DROP PROCEDURE IF EXISTS a11; CREATE PROCEDURE a11() BEGIN IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'order_rem') THEN ALTER TABLE luxclub_order ADD order_rem VARCHAR(1000) COMMENT '订单备注(会员不可见)'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'cut_money_time') THEN ALTER TABLE luxclub_order ADD cut_money_time DATETIME comment '财务扣款时间'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'cut_money_man') THEN ALTER TABLE luxclub_order ADD cut_money_man VARCHAR(50) comment '财务扣款人'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'order_confirm_man') THEN ALTER TABLE luxclub_order ADD order_confirm_man VARCHAR(26) comment '订单确认人'; END IF; IF NOT EXISTS (select * from information_schema.`TABLES` WHERE TABLE_NAME = 'luxclub_mobile_token') THEN create table luxclub_mobile_token(loginName varchar(25) comment '登录名',token varchar(50) comment 'token验证标记'); END IF; IF NOT EXISTS (select * from information_schema.STATISTICS WHERE TABLE_NAME = 'luxclub_mobile_token' and COLUMN_NAME='loginName' AND index_name='PRIMARY') THEN ALTER TABLE luxclub_mobile_token ADD PRIMARY KEY ( loginName ); END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_mobile_steward_summary' and COLUMN_NAME = 'huo_dong_hua_ti') THEN ALTER TABLE luxclub_mobile_steward_summary ADD huo_dong_hua_ti VARCHAR(500) comment '活动话题'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_mobile_steward_summary' and COLUMN_NAME = 'xi_hao') THEN ALTER TABLE luxclub_mobile_steward_summary ADD xi_hao VARCHAR(500) comment '喜好'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_mobile_steward_summary' and COLUMN_NAME = 'guan_xi') THEN ALTER TABLE luxclub_mobile_steward_summary ADD guan_xi VARCHAR(500) comment '关系'; END IF; alter table luxclub_member_info modify column interest varchar(2000); IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'mobile_steward_receive_order_time') THEN alter table luxclub_order add mobile_steward_receive_order_time datetime comment '移动管家已经接单时间'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'mobile_steward_complete_order_time') THEN alter table luxclub_order add mobile_steward_complete_order_time datetime comment '移动管家已完成时间'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'cheng_ben') THEN alter table luxclub_order add cheng_ben varchar(1000) comment '消费成本'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'dan_ju_shang_chuan_ren') THEN alter table luxclub_order add dan_ju_shang_chuan_ren varchar(55) comment '单据上传人(成本和消费是同时上传的)'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'dan_ju_shang_chuan_time') THEN alter table luxclub_order add dan_ju_shang_chuan_time datetime comment '单据上传时间(成本和消费是同时上传的)'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'cheng_ben_url') THEN alter table luxclub_order add cheng_ben_url varchar(1000) comment '成本图片上传后储存的url'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'fang_wan') THEN alter table luxclub_order add fang_wan varchar(16) comment '添加订单的时候如果是酒店出行的需要添加房晚整型字段'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_order' and COLUMN_NAME = 'golf_chang_ci') THEN alter table luxclub_order add golf_chang_ci varchar(10) comment '添加订单为高尔夫的时候必须添加场次字段,整数必填'; END IF; -- 每月余额统计表 IF NOT EXISTS (select * from information_schema.`TABLES` WHERE TABLE_NAME = 'luxclub_month_balance') THEN CREATE TABLE luxclub_month_balance( id VARCHAR(64) COMMENT '唯一标识', member_cardno VARCHAR(25) COMMENT '会员卡号', balance DECIMAL(22,3) COMMENT '该月此时余额', update_date DATETIME COMMENT '这个我要直接使用,插入或者更新时间,每次更新都是该月最后一次的', update_by VARCHAR(25) COMMENT '创建人', create_date DATETIME COMMENT '创建时间', create_by Varchar(50) COMMENT '创建人', remarks NVARCHAR(266) COMMENT '备注', del_flag CHAR(1) COMMENT '删除标记,1代表已经删除' )DEFAULT CHARSET=UTF8,COMMENT 'jeesite4父子表测试的子表'; END IF; -- luxclub_member_card_rule 会员卡规则表添加字段 赠送金额 gift_amount IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_card_rule' and COLUMN_NAME = 'gift_amount') THEN alter table luxclub_member_card_rule add gift_amount decimal(12,2) comment '赠送金额'; END IF; -- luxclub_card_order 表增加6个字段: 总金额(卡内余额)(amount)=赠送金额(gift_amount)+实际到账金额(actual_amount) 实际未到账金额 充值人, 审核人,实际到账时间 IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_card_order' and COLUMN_NAME = 'charge_amount') THEN alter table luxclub_card_order add charge_amount decimal(12,2) comment '充值金额'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_card_order' and COLUMN_NAME = 'gift_amount') THEN alter table luxclub_card_order add gift_amount decimal(12,2) comment '赠送金额'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_card_order' and COLUMN_NAME = 'out_amount') THEN alter table luxclub_card_order add out_amount decimal(12,2) comment '未到账金额'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_card_order' and COLUMN_NAME = 'charge_man') THEN alter table luxclub_card_order add charge_man VARCHAR(50) comment '充值人'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_card_order' and COLUMN_NAME = 'check_man') THEN alter table luxclub_card_order add check_man VARCHAR(50) comment '审核人'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_card_order' and COLUMN_NAME = 'actual_get_amount_date') THEN alter table luxclub_card_order add actual_get_amount_date DATETIME comment '实际到账时间'; END IF; -- 2018_9_19 weekday(3) 10:47:01 增加佳明要的字段 IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_name_en') THEN alter table luxclub_member_info add member_name_en VARCHAR(50) comment '客户英文名称'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_email') THEN alter table luxclub_member_info add member_email VARCHAR(100) comment '客户email'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_wechatno') THEN alter table luxclub_member_info add member_wechatno VARCHAR(100) comment '客户微信号'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_shu_xiang') THEN alter table luxclub_member_info add member_shu_xiang VARCHAR(20) comment '客户属相'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_xing_zuo') THEN alter table luxclub_member_info add member_xing_zuo VARCHAR(25) comment '客户星座'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_height') THEN alter table luxclub_member_info add member_height decimal(12,3) comment '客户身高cm'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_weight') THEN alter table luxclub_member_info add member_weight decimal(12,3) comment '客户体重kg'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_nationality') THEN alter table luxclub_member_info add member_nationality varchar(200) comment '国籍'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_native_place') THEN alter table luxclub_member_info add member_native_place varchar(250) comment '籍贯'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_faith') THEN alter table luxclub_member_info add member_faith varchar(100) comment '会员宗教信仰'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_character') THEN alter table luxclub_member_info add member_character varchar(350) comment '会员性格'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_aways_say') THEN alter table luxclub_member_info add member_aways_say varchar(300) comment '口头禅'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_addr') THEN alter table luxclub_member_info add member_addr varchar(350) comment '家庭住址'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_addr_other') THEN alter table luxclub_member_info add member_addr_other varchar(350) comment '其他住址'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_marriage_status') THEN alter table luxclub_member_info add member_marriage_status varchar(10) comment '婚姻状况'; END IF; IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_member_info' and COLUMN_NAME = 'member_photo_url') THEN alter table luxclub_member_info add member_photo_url varchar(1000) comment '会员照片url'; END IF; -- 增加一个充值/开卡审核统计表 IF NOT EXISTS (select * from information_schema.`TABLES` WHERE TABLE_NAME = 'luxclub_card_order_auditing') THEN CREATE TABLE luxclub_card_order_auditing( id VARCHAR(64) COMMENT '唯一标识', code VARCHAR(25) COMMENT '开卡充值订单编号', actual_amount DECIMAL(22,3) COMMENT '该次该单号审核实际到账金额', update_date DATETIME COMMENT '这个我要直接使用,插入或者更新时间,每次更新都是该月最后一次的', update_by VARCHAR(25) COMMENT '创建人', create_date DATETIME COMMENT '创建时间', create_by varchar(50) COMMENT '创建人', remarks NVARCHAR(266) COMMENT '备注', del_flag CHAR(1) COMMENT '删除标记,1代表已经删除' ); END IF; -- 2018_9_24 weekday(1) 12:31:47 IF NOT EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'sys_user' and COLUMN_NAME = 'freeze') THEN alter table sys_user add freeze varchar(2) comment '账户冻结, 0代表没有冻结, 1代表 已经冻结'; END IF; IF EXISTS (select column_name from information_schema.columns where TABLE_NAME = 'luxclub_card_order' and COLUMN_NAME = 'vouchers') THEN alter table luxclub_card_order modify column vouchers varchar(3000); END IF; END; CALL a11(); DROP PROCEDURE IF EXISTS a11;