---- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓此位置加入最新修改的脚本↓↓↓↓↓↓↓↓↓↓↓↓↓↓ -- 2020-10-10 laiyinghe DB：shebao_admin -- 待结算的金额汇总表 CREATE TABLE `t_report_todofee` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `city_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '城市', `opt_month` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '核算月份', `order_month` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单月份', `shebao_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '社保有效单数', `shebao_fee` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '社保费', `sbcharge_fee` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '社保服务费', `sbcard_fee` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '社保制卡服务费', `pre_fee` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '社保预收费', `fund_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '公积金有效单数', `fund_fee` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '公积金费', `fund_charge_fee` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '公积金服务费', `update_time` datetime DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`), KEY `todoFee_optmonth_idx` (`opt_month`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='待结算的金额汇总'; ----------------------------------- 已同步线上 @ 2020-08-06 -- 2020-08-04 laiyinghe DB：shebao_admin -- 对帐单表增加充值和转帐或退款金额两字段 ALTER TABLE `t_report_bill` ADD COLUMN `recharge_fee` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '充值或收入金额' AFTER `k6`, ADD COLUMN `trun_refund_fee` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '转帐或退款金额' AFTER `recharge_fee`; ----------------------------------- 已同步线上 @ 2020-07-31 -- 2020-07-27 laiyinghe DB：shebao -- 加字段。 ALTER TABLE `t_sb_city` ADD COLUMN `sb_alone` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否允许单买' AFTER `update_time`; -- 2020-04-07 laiyinghe DB：shebao -- 新表。 CREATE TABLE `t_sb_wage_flow_setting` ( `city_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '城市代码', `flow_flag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否需要流水', `flow_order` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '已有单是否可以支付', `flow_start` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '流水超始金额', `flow_end` int(11) NOT NULL DEFAULT '0' COMMENT '流水最高金额', `flow_charge` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '服务费', `remark` varchar(32) NOT NULL DEFAULT '' COMMENT '说明', `update_time` datetime DEFAULT NULL COMMENT '最新更新时间', PRIMARY KEY (`city_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='流水配置表'; ----------------------------------- 已同步线上 @ 2020-05-24 -- 2020-04-07 laiyinghe DB：shebao -- 增加新字段 支付时间 ALTER TABLE `t_sb_buy_order_added` ADD COLUMN `pay_time` datetime NULL COMMENT '支付时间'; -- 初始化新加的字段 UPDATE t_sb_buy_order_added SET pay_time=gen_time, gen_time=gen_time WHERE `status`>1; ----------------------------------- 已同步线上 @ 2020-04-26 -- 2020-04-07 laiyinghe DB：shebao -- 增加字段长度 ALTER TABLE `t_sb_advertiser` MODIFY COLUMN `advertiser` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '广告商' AFTER `appid`; ----------------------------------- 已同步线上 @ 2020-04-09 -- 2020-04-07 laiyinghe DB：shebao -- 优化券表，增加索引。 ALTER TABLE `t_sb_coupon_user` ADD INDEX `couponuser_userid_idx` (`user_id`) ; -- 2020-04-07 laiyinghe DB：shebao_admin -- 增加支付渠道字段 ALTER TABLE `t_report_user_order` ADD COLUMN `payChannel` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '合作渠道/支付平台' AFTER `userRegDate`; -- 2020-03-30 laiyinghe DB：shebao_admin -- 增加新表 CREATE TABLE `t_report_bill` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `mine_flag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '支付平台:0; 本地数据:1', `bill_date` date NOT NULL COMMENT '帐单日期', `platform_code` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '支付平台代码', `trade_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '交易订单数', `trade_fee` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '交易金额', `refund_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '退款单数', `refund_fee` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '退款金额', `k6` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '交易手续费金额', `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), KEY `report_bill_date_idx` (`bill_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='帐单金额表'; ----------------------------------- 已同步线上 @ 2020-03-17 -- 2020-03-03 部署时需要考虑到微信支付退款的配置 -- 1. configure.propertis文件配置回调地址 -- 2. 同步证书文件到线上的etc目录 -- 2020-03-09 laiyinghe DB：shebao -- 流水表增加字段 ALTER TABLE `t_sb_buy_order_wage` ADD COLUMN `pay_platform` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '支付平台标识' AFTER `fail_reason`; -- 2020-03-09 laiyinghe DB：shebao -- 补差额表增加字段 ALTER TABLE `t_sb_buy_order_added` ADD COLUMN `pay_platform` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '支付平台标识' AFTER `gen_time`; -- 2020-02-20 laiyinghe DB：shebao -- 新建 接口退款请求响应日志表 CREATE TABLE `t_log_refund_api` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `refund_no` varchar(36) NOT NULL DEFAULT '' COMMENT '退款单号', `invoke` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '调用的内容', `content` text NOT NULL, `gen_time` datetime DEFAULT NULL COMMENT '创建时间', PRIMARY KEY (`id`), KEY `logrefundapi_order_idx` (`refund_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='接口退款请求响应日志'; -- 2019-12-03 laiyinghe DB:shebao -- 增加调用接口退款用到的数据字段 ALTER TABLE `shebao`.`t_sb_buy_order_opt` ADD COLUMN `opt_api` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '退款方式' AFTER `added_no`, ADD COLUMN `charge_k6` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '千分之六的手续费(分)' AFTER `opt_api`, ADD COLUMN `opt_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '退款状态，同订单' AFTER `charge_k6`, ADD COLUMN `opt_time` DATETIME NULL COMMENT '退款操作时间' AFTER `opt_status`, ADD COLUMN `opt_remark` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '退款操作备注' AFTER `opt_time`, ADD COLUMN `batchNo` varchar(20) NOT NULL DEFAULT '' COMMENT '批次号' AFTER `opt_remark`, ADD COLUMN `refundNo` varchar(32) NOT NULL DEFAULT '' COMMENT '支付平台的本次退款标识' AFTER `batchNo`, ADD INDEX `buy_order_opt_status_idx` (`opt_status` ASC); -- 设置历史数据的状态为退款成功 update t_sb_buy_order_opt set opt_status=4; ----------------------------------- 已同步线上 @ 2019-11-25 -- 2019-11-25 laiyinghe DB:shebao -- 为订单表的时间字段加上索引（避免全表扫描）。 create index buy_order_time_idx on shebao.t_sb_buy_order(gen_time); ----------------------------------- 已同步线上 @ 2019-11-18 -- 2019-11-09 laiyinghe DB:shebao -- 给表 t_user_wx 增加数据 （从t_user表来）2820719 insert into t_user_wx (u_id, wx_open_id) SELECT u_id, user_id FROM shebao.t_user where user_id !=''; -- 新增用户表 CREATE TABLE `t_user_wx` ( `u_id` INT UNSIGNED NOT NULL, `wx_open_id` VARCHAR(32) NOT NULL, PRIMARY KEY (`u_id`), UNIQUE INDEX `wx_open_id_UNIQUE` (`wx_open_id`)) COMMENT = '用户id与微信openId的关联表'; ----------------------------------- 已同步线上 @ 2019-11-08 -- 2019-10-18 laiyinghe DB:shebao -- 黑名单表增加手机号 ALTER TABLE `t_sb_user_black` ADD COLUMN `phone` varchar(16) NOT NULL DEFAULT '' COMMENT '手机号' AFTER `idcard`; ----------------------------------- 已同步线上 @ 2019-10-31 -- 2019-09-17 laiyinghe DB:shebao_admin -- 减员表增加专员的用户ID ALTER TABLE `t_sb_stop` ADD COLUMN `proUserId` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '专员用户ID' AFTER `any_reason`; ----------------------------------- 已同步线上 @ 2019-10-18 -- 2019-10-18 laiyinghe DB:shebao -- ALTER TABLE `t_zwcx_type` ADD UNIQUE INDEX `t_zwcx_tyname_idx` (`typeName`) ; -- 2019-09-17 laiyinghe DB:shebao_admin -- 新加表 CREATE TABLE `t_report_market_user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `genDate` date DEFAULT NULL, `user0` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '游客数', `user1` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册用户数', `market` varchar(24) NOT NULL DEFAULT '' COMMENT '市场', `updateTime` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `marketuser_gen_idx` (`genDate`), KEY `marketuser_market_idx` (`market`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='市场的日用户汇总'; -- 2019-09-17 laiyinghe DB:shebao_admin -- 更改表的索引 ALTER TABLE `t_user_device_flag` DROP INDEX `userFlag_update_idx` , ADD INDEX `userFlag_gen_idx` (`gen_time`) USING BTREE ; -- 2019-09-12 laiyinghe DB:shebao_admin -- 新加表 CREATE TABLE `t_report_user_order` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `payDate` date DEFAULT NULL, `userId` int(10) unsigned NOT NULL, `orderCount` smallint(6) NOT NULL, `charge` smallint(6) NOT NULL, `phone` varchar(12) NOT NULL, `deviceId` varchar(36) NOT NULL, `market` varchar(24) NOT NULL, `userRegDate` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `userorder_date_idx` (`payDate`), KEY `userorder_market_idx` (`market`), KEY `user_order_phone_idx` (`phone`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户每天订单信息汇总表，供运营查阅'; ----------------------------------- 已同步线上 @ 2019-09-18 -- 2019-09-11 laiyinghe DB:shebao -- 新加表 CREATE TABLE `t_sb_buy_tmp` ( `trade_no` varchar(32) NOT NULL DEFAULT '', `buy_no` varchar(32) NOT NULL DEFAULT '', `added` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否是补缴', `buy_param` varchar(512) NOT NULL DEFAULT '', `buy_bean` varchar(512) NOT NULL DEFAULT '', `gen_time` datetime DEFAULT NULL, PRIMARY KEY (`trade_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='买单临时表'; -- 2019-09-09 laiyinghe DB:shebao -- 政务纠错表 CREATE TABLE `t_zwcx_error` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `tid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '话题ID', `userId` int(10) unsigned NOT NULL DEFAULT '0', `pos` varchar(16) NOT NULL DEFAULT '' COMMENT '出错项目', `content` varchar(255) NOT NULL DEFAULT '' COMMENT '出错描述', `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态', `genTime` datetime DEFAULT NULL, `updateTime` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='纠错收集'; -- 2019-09-02 laiyinghe DB:shebao -- 政务用户表 CREATE TABLE `t_zwcx_user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `openid` varchar(32) NOT NULL DEFAULT '', `userPhone` varchar(16) NOT NULL DEFAULT '' COMMENT '手机号', `nickName` varchar(32) NOT NULL DEFAULT '' COMMENT '昵称', `genTime` datetime DEFAULT NULL COMMENT '生成时间', `loginTime` datetime DEFAULT NULL COMMENT '最近登录时间', PRIMARY KEY (`id`), UNIQUE KEY `zwcx_user_openid_idx` (`openid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'; -- 2019-08-14 laiyinghe DB:shebao -- 广告表新建两字段 ALTER TABLE `t_sb_ad` ADD COLUMN `adType` varchar(16) NOT NULL DEFAULT '' COMMENT '广告分类' AFTER `enabled`, ADD COLUMN `adSys` varchar(16) NOT NULL DEFAULT '' COMMENT '广告属于产品' AFTER `adType`; -- 修改历史数据的上面两字段 UPDATE t_sb_ad SET adSys='ShebaoApp'; UPDATE t_sb_ad SET adType='Banner' WHERE position<11; UPDATE t_sb_ad SET adType='Service' WHERE position>10; -- 2019-08-14 laiyinghe DB:shebao -- 新建内容主表 CREATE TABLE `t_zwcx_title` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `typeId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '所属类型', `city` varchar(8) NOT NULL DEFAULT '' COMMENT '城市', `title` varchar(32) NOT NULL DEFAULT '' COMMENT '标题', `hot` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否常见', `hotSeq` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '常见的顺序', `batchNo` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '批次号(辅助编辑)', `disabled` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否禁用', PRIMARY KEY (`id`), KEY `zwcx_title_city_idx` (`city`), KEY `zwcx_title_type_idx` (`typeId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='内容主表'; -- 新建内容表 CREATE TABLE `t_zwcx_content` ( `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '主表ID', `content` mediumtext NOT NULL COMMENT '内容', `updateTime` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 新建政务类型表 CREATE TABLE `t_zwcx_type` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `displayPos` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '显示顺序', `typeName` varchar(16) NOT NULL DEFAULT '' COMMENT '类型名称', `img_path` varchar(32) NOT NULL DEFAULT '' COMMENT '图片地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='政务类型表'; ----------------------------------- 已同步线上 @ 2019-08-29 -- 2019-08-27 laiyinghe DB:shebao -- 增加字段。此字段值来自 表 t_sb_advertiser ALTER TABLE `t_user_device_flag` ADD COLUMN `advertiser` varchar(16) NOT NULL DEFAULT '' COMMENT '广告商' AFTER `appMarket`; -- 2019-08-27 laiyinghe DB:shebao -- 新建表 CREATE TABLE `t_sb_advertiser` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `idfa` varchar(64) NOT NULL DEFAULT '' COMMENT '手机标识', `appid` varchar(16) NOT NULL DEFAULT '', `advertiser` varchar(16) NOT NULL DEFAULT '' COMMENT '广告商', `confirm2` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否有二次确认', `callbackUrl` varchar(255) NOT NULL DEFAULT '' COMMENT '回调地址', `gen_time` datetime NOT NULL, `update_time` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tsbadvertiser_idfa_idx` (`idfa`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='广告商记录表'; ----------------------------------- 已同步线上 @ 2019-07-26 -- 2019-08-06 laiyinghe DB:shebao -- 用户表增加应用市场字段。注册时写值后不变 ALTER TABLE `t_user` ADD COLUMN `appMarket` varchar(32) NOT NULL DEFAULT '' COMMENT '应用市场名称'; -- 2019-08-05 laiyinghe DB:shebao -- 新建表，设备标识表 CREATE TABLE `t_user_device_flag` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `userId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID,游客是0', `deviceId` varchar(36) NOT NULL DEFAULT '' COMMENT '设备识别ID', `appMarket` varchar(16) NOT NULL DEFAULT '' COMMENT 'App市场', `gen_time` date NOT NULL, `update_time` date NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `userFlag_deviceId_uq` (`deviceId`), KEY `userFlag_appMarket_idx` (`appMarket`), KEY `userFlag_update_idx` (`update_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 2019-07-30 laiyinghe DB:shebao -- 下单时增加提交字段--标识支付平台。 ALTER TABLE `t_sb_buy` ADD COLUMN `pay_platform` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '第三方支付平台'; -- 2019-07-29 laiyinghe DB:shebao -- 退款操作表增加补差额退款标识字段 ALTER TABLE `t_sb_buy_order_opt` ADD COLUMN `added_no` varchar(32) NOT NULL DEFAULT '' COMMENT '补差额退款时需提供'; ----------------------------------- 已同步线上 @ 2019-07-26 -- 2019-07-26 chensenlai DB:shebao alter table t_sb_info_other add address varchar(128) DEFAULT NULL COMMENT '现居住地址' AFTER `hk_address`; ----------------------------------- 已同步线上 @ 2018-07-25 -- 2019-07-24 laiyinghe DB:shebao_admin -- 增加专员用户ID字段，用于限制专员查看的数据 ALTER TABLE `t_sb_employee` ADD COLUMN `proUserId` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '专员用户ID' AFTER `id_card_desc`; -- 2019-07-24 laiyinghe DB:shebao_admin -- 增加用户类型的字段，区分专员 ALTER TABLE `sys_user` ADD COLUMN `user_type` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '2专员' AFTER `pay_channel`; ----------------------------------- 已同步线上 @ 2018-07-11 -- 2019-07-10 laiyinghe DB:shebao_admin -- 增加是否适用于财务的流水字段 ALTER TABLE `t_report_month` ADD COLUMN `finance` tinyint(1) UNSIGNED NULL DEFAULT 0 COMMENT '是否是财务数据' AFTER `update_time`; ----------------------------------- 已同步线上 @ 2018-06-11 ======= -- 2019-07-26 chensenlai DB:shebao alter table t_sb_info_other add address varchar(128) DEFAULT NULL COMMENT '现居住地址' AFTER `hk_address`; >>>>>>> 49fbf15d98ac0d2d75cf4cc72d647aff42bc2b0c -- 2019-06-03 laiyinghe DB:shebao_admin -- 增加制卡费用的退款 ALTER TABLE `t_sb_buy_order_ext` ADD COLUMN `card_charge` decimal(6,2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '退款制止服务费' AFTER `refund_charge`; ----------------------------------- 已同步线上 @ 2018-05-31 -- 2019-05-31 laiyinghe DB:shebao -- 增加字段 ALTER TABLE `t_channel` ADD COLUMN `key_channel` varchar(16) NOT NULL DEFAULT '' COMMENT '校验key' AFTER `pay_channel`; ----------------------------------- 已同步线上 @ 2018-05-21 -- 2019-03-12 laiyinghe DB:shebao_admin -- 增加用户报表表格 CREATE TABLE `t_report_user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `gen_date` date NOT NULL COMMENT '日期', `reg_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '当天注册数', `login_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '当天登录数(含注册)', `sbusers` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '总参保人数', PRIMARY KEY (`id`), KEY `report_user_date_idx` (`gen_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户每天的变动情况'; -- 2019-04-25 laiyinghe DB:shebao -- 增加是否开放公积金购买字段 ALTER TABLE `t_sb_city` ADD COLUMN `fund_buy2` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '开放购买公积金' AFTER `fund_buy`; ----------------------------------- 已同步线上 @ 2018-05-10 -- 2019-05-08 laiyinghe DB:shebao -- 增加用户手机码字段 ALTER TABLE `t_user` ADD COLUMN `regIMEI` varchar(36) NOT NULL DEFAULT '' COMMENT '手机的IMEI'; -- 2019-05-08 laiyinghe DB:shebao -- 增加用户帐号注销表 CREATE TABLE `t_user_logout` ( `u_id` int(10) unsigned NOT NULL, `phone` varchar(12) NOT NULL DEFAULT '' COMMENT '注销账号', `reason` varchar(126) NOT NULL DEFAULT '' COMMENT '原因', `gen_time` datetime NOT NULL COMMENT '注销时间', PRIMARY KEY (`u_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户帐号注销'; ----------------------------------- 已同步线上 @ 2018-04-13 -- 2019-04-30 chensenlai DB:shebao_admin -- 增加增员员工身份证校验字段 ALTER TABLE `t_sb_employee` ADD COLUMN `id_card_status` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '身份证校验状态'; ALTER TABLE `t_sb_employee` ADD COLUMN `id_card_desc` varchar(100) DEFAULT NULL COMMENT '身份证校验描述' AFTER `id_card_status`; UPDATE `t_sb_employee` SET `id_card_status`=5 WHERE `pay_type`<>0 OR `for_month`<201904; -- 2019-03-12 laiyinghe DB:shebao -- 增加数据收集表 CREATE TABLE `t_sb_collect_question` ( `u_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户标识', `answer1` varchar(64) NOT NULL DEFAULT '' COMMENT '第一题答案', `answer2` varchar(64) NOT NULL DEFAULT '' COMMENT '第二题答案', `answer3` varchar(64) NOT NULL DEFAULT '' COMMENT '第三题答案', `gen_time` datetime DEFAULT NULL COMMENT ' 创建时间', PRIMARY KEY (`u_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据收集表'; -- 2019-03-05 laiyinghe DB:shebao -- 增加字段 ALTER TABLE `t_sb_buy` ADD COLUMN `working` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '在职有工资' AFTER `sb_wage`; ----------------------------------- 已同步线上 @ 2018-03-18 -- 2019-03-12 laiyinghe DB:shebao_admin -- 增加上传文件的临时表 CREATE TABLE `t_tmp_file` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `u_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '操作人id', `pmodule` varchar(16) NOT NULL DEFAULT '' COMMENT '模块', `filepath` varchar(64) NOT NULL DEFAULT '' COMMENT '文件路径', `gen_time` datetime NOT NULL COMMENT '上传时间', PRIMARY KEY (`id`), KEY `temp_file_uid_idx` (`u_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文件上传保存表'; ----------------------------------- 已同步线上 @ 2018-01-23 -- 2019-01-21 laiyinghe DB:shebao -- 增加不可下单原因字段 ALTER TABLE `t_sb_city_date` ADD COLUMN `reason` varchar(20) NOT NULL DEFAULT '' COMMENT '不可下单原因' AFTER `end_date`; -- 2019-01-21 laiyinghe DB:shebao_admin -- 新增流水周期数据情况表 CREATE TABLE `t_report_wage_cycle` ( `begin_date` date NOT NULL COMMENT '周期开始日期', `end_date` date NOT NULL COMMENT '周期结束日期', `totals` varchar(80) NOT NULL DEFAULT '' COMMENT '所有已支付和之后状态的数据情况', `payed` varchar(80) NOT NULL DEFAULT '' COMMENT '当前处于已支付状态数据的情况', `hfed` varchar(80) NOT NULL DEFAULT '' COMMENT '当前处于已返还状态数据的情况', `fail` varchar(80) NOT NULL DEFAULT '' COMMENT '当前处于返还失败状态数据的情况', `gen_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`begin_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='流水周期数据情况'; -- 2019-01-21 laiyinghe DB:shebao_admin -- 新增流水月汇总表 CREATE TABLE `t_report_wage_month` ( `opt_month` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '操作月份', `totals` varchar(80) NOT NULL DEFAULT '' COMMENT '所有已支付和之后状态的数据情况', `payed` varchar(80) NOT NULL DEFAULT '' COMMENT '当前处于已支付状态数据的情况', `hfed` varchar(80) NOT NULL DEFAULT '' COMMENT '当前处于已返还状态数据的情况', `fail` varchar(80) NOT NULL DEFAULT '' COMMENT '当前处于返还失败状态数据的情况', `gen_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`opt_month`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='流水月汇总'; ----------------------------------- 已同步线上 @ 2018-01-15 -- 2019-01-10 laiyinghe DB:shebao -- 流水表增加返还失败的原因 ALTER TABLE `t_sb_buy_order_wage` ADD COLUMN `fail_reason` varchar(128) NOT NULL DEFAULT '' COMMENT '返还失败原因' AFTER `refund_time`; ----------------------------------- 已同步线上 @ 2018-01-10 -- 2019-01-10 laiyinghe DB:shebao -- 新增减员月份字段 ALTER TABLE `t_sb_stop_hand` ADD COLUMN `via_month` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '对应减员月份' AFTER `order_no`; ----------------------------------- 已同步线上 @ 2018-01-05 -- 2018-12-20 laiyinghe DB:shebao -- 新增手动减员表 CREATE TABLE `t_sb_stop_hand` ( `order_no` varchar(32) NOT NULL, `opt_month` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '减员核算月', `urgent` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否加急', `status` tinyint(4) NOT NULL DEFAULT '0', `gen_time` datetime NOT NULL, `opt_user` varchar(16) NOT NULL DEFAULT '' COMMENT '操作人', `update_time` datetime DEFAULT NULL COMMENT '最新更新时间', PRIMARY KEY (`order_no`), KEY `hand_stop_month_idx` (`opt_month`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='手动减员表'; -- 2018-12-6 laiyinghe DB:shebao -- 买单表增加字段 ALTER TABLE `t_sb_buy` ADD COLUMN `sb_wage` decimal(8,2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '社保流水金额'; -- 2018-12-6 laiyinghe DB:shebao -- 新增表：社保流水表 CREATE TABLE `t_sb_buy_order_wage` ( `order_no` varchar(32) NOT NULL DEFAULT '' COMMENT '单号', `trade_no` varchar(32) NOT NULL DEFAULT '' COMMENT '支付单号', `pay_uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '帐号ID', `opt_month` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '核算(操作)月份', `charge` decimal(6,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '服务费', `wage` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '流水金额', `total_fee` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '支付金额', `pay_date` date NOT NULL COMMENT '最晚支付日期', `f_channel` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '来自渠道', `gen_time` datetime NOT NULL, `pay_channel` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '支付渠道', `pay_no` varchar(32) NOT NULL DEFAULT '' COMMENT '支付流水号', `pay_status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '支付状态', `pay_time` datetime DEFAULT NULL COMMENT '支付时间', `refund_time` datetime DEFAULT NULL COMMENT '返回时间', PRIMARY KEY (`order_no`), UNIQUE KEY `order_wage_tradeno_idx` (`trade_no`), KEY `order_wage_month_idx` (`opt_month`), KEY `order_wage_uid_idx` (`pay_uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='社保流水表'; -- 2018-12-08 laiyinghe DB:shebao -- 不可下单日期的配置表 CREATE TABLE `t_sb_city_date` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `city_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '城市代码', `begin_date` date NOT NULL COMMENT '开始不可下单日期', `end_date` date NOT NULL COMMENT '结束日期', `opt_user` varchar(20) NOT NULL COMMENT '生成人', `gen_time` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='不可下单日期的配置'; ----------------------------------- 已同步线上 @ 2018-12-04 -- 2018-12-4 laiyinghe DB:shebao -- 主配置 表增加字段 ALTER TABLE `t_sb_city` ADD COLUMN `wage_flow` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否需要流水' AFTER `inc_enabled`; -- 2018-09-27 laiyinghe DB:shebao -- 参保人黑名单表 CREATE TABLE `t_sb_user_black` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `uname` varchar(20) NOT NULL DEFAULT '' COMMENT '身份证对应姓名', `idcard` varchar(20) NOT NULL DEFAULT '' COMMENT '身份证号码', `reason` varchar(64) NOT NULL DEFAULT '' COMMENT '原因', `opt_user` varchar(20) NOT NULL DEFAULT '' COMMENT '操作人', `enable` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '有效吗', `gen_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `sbuser_black_idcard_idx` (`idcard`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='参保人黑名单表'; -- 2018-11-10 laiyinghe DB:shebao -- 新增加销量表 CREATE TABLE `t_sb_buy_volume` ( `city_code` mediumint(8) unsigned NOT NULL, `sb_vol` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '社保销量', `fund_vol` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '公积金销量', `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '数据更新时间', `gen_time` datetime DEFAULT NULL COMMENT '重置时间', PRIMARY KEY (`city_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='销量表'; -- 2018-11-10 laiyinghe DB:shebao -- 增加参保人的回访字段 ALTER TABLE `t_sb_user` ADD COLUMN `education` varchar(32) NOT NULL DEFAULT '' COMMENT '学历' AFTER `update_time`, ADD COLUMN `need_pay` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否需要发薪' AFTER `education`, ADD COLUMN `salary` decimal(8,2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '薪资金额' AFTER `need_pay`; -- 2018-11-24 laiyinghe DB:shebao -- 参保人增加银行相关字段。 ALTER TABLE `t_sb_user` ADD COLUMN `bank` varchar(32) NOT NULL DEFAULT '' COMMENT '银行' AFTER `salary`, ADD COLUMN `bank_branch` varchar(64) NOT NULL DEFAULT '' COMMENT '银行支行' AFTER `bank`, ADD COLUMN `bank_code` varchar(32) NOT NULL DEFAULT '' COMMENT '银行卡号码' AFTER `bank_branch`; -- 2018-11-01 laiyinghe DB:shebao -- 增加广告表 CREATE TABLE `t_sb_ad` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(32) NOT NULL DEFAULT '' COMMENT '广告主题', `notes` varchar(64) NOT NULL DEFAULT '' COMMENT '说明', `img_path` varchar(64) NOT NULL DEFAULT '' COMMENT '图片链接', `img_link` varchar(128) NOT NULL DEFAULT '' COMMENT '图片跳转链接', `position` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '位置', `enabled` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否启用', `gen_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `ad_enabled_idx` (`enabled`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='APP首页图片广告'; ----------------------------------- 已同步线上 @ 2018-10-24 -- 2018-10-22 laiyinghe DB:shebao -- 社保参数数据配置增加字段 ALTER TABLE `t_sb_city` ADD COLUMN `inc_enabled` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否启用' AFTER `first_months`; -- 2018-09-14 laiyinghe DB:shebao -- 城市参数数据配置增加两个字段 ALTER TABLE `t_sb_city` ADD COLUMN `hukou_address` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否需要填写户藉地址' AFTER `fund_buy`, ADD COLUMN `first_months` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '首次购买的月份数' AFTER `hukou_address`; -- 2018-09-14 laiyinghe DB:shebao -- 参保人员表增加户藉地址字段 ALTER TABLE `t_sb_user` ADD COLUMN `hk_address` varchar(126) NOT NULL DEFAULT '' COMMENT '户籍地址' AFTER `reg_channel`; ----------------------------------- 已同步线上 @ 2018-07-24 -- 2018-07-24 chensenlai DB:shebao CREATE TABLE `t_tianji_data` ( `tid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键编号', `user_id` INT(11) UNSIGNED NOT NULL COMMENT '用户编码', `id_card` VARCHAR(20) DEFAULT NULL COMMENT '身份证', `city` VARCHAR(32) DEFAULT NULL COMMENT '城市', `name` VARCHAR(20) DEFAULT NULL COMMENT '姓名', `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号码', `fund_com_name` VARCHAR(64) DEFAULT NULL COMMENT '公积金单位名', `fund_com_code` VARCHAR(32) DEFAULT NULL COMMENT '公积金单位机构码', `fund_base_money` INT(11) NOT NULL DEFAULT 0 COMMENT '公积金月缴基数', `fund_month_money` INT(11) NOT NULL DEFAULT 0 COMMENT '公积金月缴金额', `fund_per` INT(11) NOT NULL DEFAULT 0 COMMENT '公积金个人', `fund_com` INT(11) NOT NULL DEFAULT 0 COMMENT '公积金单位', `fund_begin_month` VARCHAR(8) DEFAULT NULL COMMENT '公积金开始月份', `fund_end_month` VARCHAR(8) DEFAULT NULL COMMENT '公积金截止月份', `fund_lack_month` VARCHAR(2048) DEFAULT NULL COMMENT '公积金断缴月份', `fund_query_time` DATETIME DEFAULT NULL COMMENT '公积金查询时间', `insure_com_name` VARCHAR(64) DEFAULT NULL COMMENT '社保单位名', `insure_com_code` VARCHAR(32) DEFAULT NULL COMMENT '社保单位机构码', `insure_per` INT(11) NOT NULL DEFAULT 0 COMMENT '社保个人', `insure_com` INT(11) NOT NULL DEFAULT 0 COMMENT '社保单位', `insure_begin_month` VARCHAR(8) DEFAULT NULL COMMENT '社保开始月份', `insure_end_month` VARCHAR(8) DEFAULT NULL COMMENT '社保截止月份', `insure_pension_lack_month` VARCHAR(2048) DEFAULT NULL COMMENT '社保养老断缴月份', `insure_medical_lack_month` VARCHAR(2048) DEFAULT NULL COMMENT '社保医疗断缴月份', `insure_query_time` DATETIME DEFAULT NULL COMMENT '社保查询时间', PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '融360天机数据查询表'; ----------------------------------- 已同步线上 @ 2018-06-08 -- 2018-06-08 chensenlai DB:shebao -- 客服保存常见回复 ALTER TABLE `t_sb_hot_topic` ADD COLUMN `type` INT(11) UNSIGNED NOT NULL DEFAULT 1 COMMENT '类型 1-热门题目 2-客服常见问题' AFTER `id`; ALTER TABLE `t_sb_hot_topic` ADD COLUMN `opt_user` VARCHAR(64) DEFAULT NULL COMMENT '后台用户(客服)' AFTER `sort`; ----------------------------------- 已同步线上 @ 2018-06-04 -- 2018-06-01 chensenlai DB:shebao -- H5购买渠道支持可配置 CREATE TABLE `t_channel` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '渠道编号', `code` VARCHAR(64) NOT NULL COMMENT '外部编码', `name` VARCHAR(32) NOT NULL COMMENT '名称', `domain` VARCHAR(64) DEFAULT NULL COMMENT '绑定域名', `reg_channel` VARCHAR(32) NOT NULL COMMENT '注册渠道标识', `pay_channel` INT(11) UNSIGNED NOT NULL COMMENT '支付渠道标识', `disabled` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否禁用 0-非禁用 1-禁用', `defaulted` TINYINT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否默认 0-非默认 1-默认', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '渠道配置表'; INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('095061cb906341adb88bae70eb5eb4f9', 'APP微信', NULL, '0', 1, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('29e8dd32d8ed4d77896cff017bc757ac', '五险一金APP', NULL, '0', 2, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('647b5a3879e84439a8f3b6266f1b99b5', 'APP支付宝', NULL, '0', 11, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('00d14db2101640e98a051e20be513a5e', '支付生活号(社保公积金待缴)', 'alipay.shanp.com', 'AN_JU_BAO', 31, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('088663c17e7b47769a56728459cec584', 'E家帮', 'ejbang.shanp.com', 'EJ_BANG', 33, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('14436027b0aa4877b9038ffeee249fda', '我是车主', 'woshichezhu.shanp.com', 'WS_CHEZHU', 35, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('0f3060efe4e64d2294146098b829b9df', '小雨伞', 'xiaoyusan.shanp.com', 'X_YUSAN', 37, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('faf62d2368d24b7a951fe440f6802e30', '银承派', 'yinchengpai.shanp.com', 'Y_CHENGPAI', 39, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('497cc12254fd4b24b3650812875246e9', '师傅帮', '51szg.shanp.com', 'SHI_FU', 41, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('ea0a71a565db4661b88b1a7c85f361bb', '兼职地带', 'jzdd.shanp.com', 'JIAN_ZHI', 43, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('64ca08db0d29429c9d52c017d57e8b60', '掉钱眼儿', 'diaoqianyaner.shanp.com', 'QIAN_YAN', 45, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('43f92e6cd3884fa580b52a0ba0a9233c', '五险一金微信公共号', 'wxyj.shanp.com', 'WECHAT_WXYJ', 47, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('e44db289f8e24cd2a4d8104f697c03de', 'DATALINK', 'datalink.shanp.com', 'DATALINK', 49, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('bf0bc584b3d84abaa0fa131897376312', 'DOUMI', 'doumi.shanp.com', 'DOUMI', 50, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('b7fae004b81445f6bb930c4789ccb1fe', '58', '58.shanp.com', '58', 58, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('2b0c33bea9d54988bd9b7940fd1c8055', '兼职猫', 'jianzhimao.shanp.com', 'JIAN_ZHI_MAO', 60, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('24bd6635085946f6b284d71b69256adf', '公积金生活号', 'gjjshenghuohao.shanp.com', 'GJJ_SHENG_HUO_HAO', 61, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('df7192a91bf243c28e715ec90e53c287', '51社保管家', '51sbgj.shanp.com', '51SBGJ', 62, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('da79b440c5934217b0d29990313e6a60', '中燃气', 'zhongranqi.shanp.com', 'ZHONG_RAN_QI', 63, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('a410e38c4bf74b0899b044abb1a73ec3', '深圳代缴', 'szdaijiao.shanp.com', 'SZ_DAI_JIAO', 64, 0, 0); INSERT INTO `shebao`.`t_channel` (`code`, `name`, `domain`, `reg_channel`, `pay_channel`, `disabled`, `defaulted`) VALUES ('22ad685307f544bf8e5fe97923f2bad8', '我的社保默认', NULL, 'DEFAULT', 999, 0, 1); ----------------------------------- 已同步线上 @ 2018-05-17 -- 2018-05-10 laiyinghe DB:shebao -- 月份单需要记录支付渠道和支付流水号 ALTER TABLE `t_sb_buy_order` ADD COLUMN `pay_channel` smallint UNSIGNED NULL DEFAULT 0 COMMENT '支付渠道号' AFTER `manual_type`, ADD COLUMN `pay_no` varchar(64) NULL DEFAULT '' COMMENT '第三方支付号' AFTER `pay_channel`; ----------------------------------- 已同步线上 @ 2018-04-19 -- 2018-03-27 laiyinghe DB:shebao -- 参保用户生成渠道 ALTER TABLE `t_sb_user` ADD COLUMN `reg_channel` varchar(24) NOT NULL DEFAULT '' COMMENT '来自渠道'; ALTER TABLE `t_sb_user` ADD COLUMN `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; ----------------------------------- 已同步线上 @ 2018-04-12 -- 2018-04-02 laiyinghe DB:shebao_admin -- 运营后台的用户增加渠道标识字段 ALTER TABLE `sys_user` ADD COLUMN `pay_channel` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '渠道标识' AFTER `enabled`; -- 2018-03-27 chensenlai DB:shebao -- 资料流程H5资料收集新增字段（测试环境已更新）。 ALTER TABLE `t_sb_info_other` ADD COLUMN `hk_address` VARCHAR(128) DEFAULT NULL COMMENT '户籍地址' AFTER `marriage_status`; ALTER TABLE `t_sb_info_other` ADD COLUMN `graduate_time` VARCHAR(16) DEFAULT NULL COMMENT '毕业时间' AFTER `hk_address`; ALTER TABLE `t_sb_info_other` ADD COLUMN `work_time` VARCHAR(16) DEFAULT NULL COMMENT '第一次工作时间' AFTER `graduate_time`; -- 2018-03-12 laiyinghe DB:shebao -- 增加热线电话字段. ALTER TABLE `t_sb_city_site` ADD COLUMN `shebao_tel` varchar(16) NULL DEFAULT '' COMMENT '社保局热线' AFTER `shebao_wx`, ADD COLUMN `fund_tel` varchar(16) NULL DEFAULT '' COMMENT '公积金中心热线' AFTER `fund_wx`; ----------------------------------- 已同步线上 @ 2018-03-22 -- 2018-03-12 laiyinghe DB:shebao -- 定单的服务费退款。 ALTER TABLE `t_sb_buy_order_ext` ADD COLUMN `refund_charge` decimal(6,2) NOT NULL DEFAULT 0 COMMENT '服务费退款' AFTER `refund_time`; -- 2018-03-12 laiyinghe DB:shebao -- 定单的操作列表。 CREATE TABLE `t_sb_buy_order_opt` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `order_no` varchar(32) NOT NULL DEFAULT '', `opt_type` varchar(16) NOT NULL DEFAULT '' COMMENT '操作类型', `amount` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '涉及金额', `opt_user` varchar(32) NOT NULL DEFAULT '' COMMENT '操作人', `gen_time` datetime NOT NULL COMMENT '生成时间', `remark` varchar(255) NOT NULL DEFAULT '' COMMENT '说明', PRIMARY KEY (`id`), KEY `buy_order_opt_no_idx` (`order_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='定单的操作列表,包含补缴,退款等信息'; ----------------------------------- 已同步线上 @ 2017-08-29 -- 2017-04-20 laiyinghe DB:shebao -- 重要的异常通知。 CREATE TABLE `t_log_important_error` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `desc` varchar(64) NOT NULL, `content` varchar(512) NOT NULL, `gen_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ----------------------------------- 已同步线上 @ 2017-08-29 -- 2017-08-29 chensenlai DB:shebao -- H5其它资料收集表。 CREATE TABLE `t_sb_info_other` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `u_id` int(10) NOT NULL COMMENT '系统用户标识', `id_card` varchar(20) NOT NULL COMMENT '参保人身份证号码', `political_status` varchar(8) DEFAULT NULL COMMENT '政治面貌', `education_level` varchar(16) DEFAULT NULL COMMENT '受教育程度', `other_phone` varchar(16) DEFAULT NULL COMMENT '其他联系方式', `marriage_status` varchar(8) DEFAULT NULL COMMENT '婚姻情况', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ----------------------------------- 已同步线上 @ 2017-05-24 -- 2017-04-20 laiyinghe DB:shebao_admin -- 新建用户的权限表、用户角色的权限表。 CREATE TABLE `t_privilege_user` ( `user_id` int UNSIGNED NOT NULL COMMENT '用户ID' , `role_id` int UNSIGNED NOT NULL COMMENT '角色ID' , `create_uid` int UNSIGNED NOT NULL COMMENT '生成此权限的用户ID' , `module_name` varchar(32) NULL DEFAULT '' COMMENT '模块名称PModule.pname' , `privilege` varchar(64) NULL DEFAULT '' COMMENT '权限信息' , `update_time` datetime NULL COMMENT '更新时间' , INDEX `privilege_userid_idx` (`user_id`) ) ; -- 2017-04-18 laiyinghe DB:shebao_admin -- 新建角色的权限表。 CREATE TABLE `t_privilege_role` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `role_id` int(10) unsigned NOT NULL COMMENT '角色ID', `module_name` varchar(32) NOT NULL COMMENT '模块名称PModule.pname', `privilege` varchar(64) NOT NULL COMMENT '权限信息', `create_uid` int(10) unsigned NOT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `privilege_roleid_idx` (`role_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t_base_role` ( `role_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `role_name` varchar(32) NOT NULL COMMENT '角色名称', `role_desc` varchar(255) NOT NULL COMMENT '角色描述', `create_uid` int(10) unsigned NOT NULL COMMENT '创建用户的id', `enabled` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '是否可用', `create_time` datetime DEFAULT NULL, PRIMARY KEY (`role_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ----------------------------------- 已同步线上 @ 2017-05-02 -- 2017-05-02 laiyinghe DB:shebao_admin -- 增加字段退款、补缴的时间字段 ALTER TABLE `t_sb_buy_order_ext` ADD COLUMN `refund_time` datetime NULL COMMENT '最新退款时间' AFTER `refund_reason`, ADD COLUMN `repay_time` datetime NULL COMMENT '最近补缴时间' AFTER `repay_reason`; ----------------------------------- 已同步线上 @ 2017-04-21 -- 2017-04-18 laiyinghe DB:shebao_admin -- 增加字段gen_desc，方便查看生成增、代理员的依据 ALTER TABLE `t_sb_employee` ADD COLUMN `gen_desc` varchar(64) NULL DEFAULT '' COMMENT '生成依据的描述' AFTER `new_card`; ----------------------------------- 已同步线上 @ 2017-04-19 -- 2017-04-18 chensenlai DB:shebao_admin -- sys_user增加昵称字段。 ALTER TABLE `sys_user` ADD COLUMN `nick_name` varchar(32) DEFAULT NULL COMMENT '昵称' AFTER `name`; -- 2017-04-12 laiyinghe DB:shebao -- t_sb_buy_order增加字段，保存金额值。 ALTER TABLE `t_sb_buy_order` ADD COLUMN `disabled_price` decimal(8,2) NULL DEFAULT 0 COMMENT '残保金' AFTER `pre_charge`; -- 2017-04-12 laiyinghe DB:shebao -- 字段由32改为64的长度 ALTER TABLE `t_ent_sign` MODIFY COLUMN `demand` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '业务'; ----------------------------------- 已同步线上 @ 2017-04-06 -- 2017-04-06 laiyinghe DB:shebao -- 增加字段合同附件数量 ALTER TABLE `t_ent_sign` ADD COLUMN `contract_num` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '合同附件数量' AFTER `email`; ----------------------------------- 已同步线上 @ 2017-04-06 -- 2017-04-06 laiyinghe DB:shebao_admin -- 企业用户管理的合同附件管理 CREATE TABLE `t_ent_contract` ( `id` int UNSIGNED NOT NULL AUTO_INCREMENT , `entId` int UNSIGNED NOT NULL , `fileName` varchar(32) NOT NULL , `uploadTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , `wantDelete` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 , PRIMARY KEY (`id`), INDEX `t_ent_contract_entid_idx` (`entId`) ) COMMENT='合同附件表'; ALTER TABLE `t_ent_contract` MODIFY COLUMN `fileName` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `entId`, ADD COLUMN `fileType` varchar(32) NULL AFTER `fileName`; ----------------------------------- 已同步线上 @ 2017-03-27 ALTER TABLE `t_ent_sign` ADD COLUMN `wechat` varchar(32) DEFAULT NULL, ADD COLUMN `qq` varchar(16) DEFAULT NULL, ADD COLUMN `email` varchar(32) DEFAULT NULL; ----------------------------------- 已同步线上 @ 2017-04-05 -- 2017-04-05 chensenlai DB:shebao -- 修改企业登记联系号码长度。 ALTER TABLE `t_ent_sign` CHANGE COLUMN `contact_phone` `contact_phone` VARCHAR(32) NOT NULL COMMENT '联系电话' ; ----------------------------------- 已同步线上 @ 2017-01-10 -- 2016-11-02 laiyinghe DB:shebao -- 增加业务员备注的内容表。 CREATE TABLE `t_ent_sign_notes` ( `entId` int(11) NOT NULL, `notes` varchar(255) NOT NULL COMMENT '说明文本', `opt_user` varchar(32) DEFAULT NULL COMMENT '操作人', `gen_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '生成时间', KEY `ent_notes_entId_idx` (`entId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 2016-11-02 laiyinghe DB:shebao_admin -- 增加业务员负责区域表。 CREATE TABLE `t_ent_user` ( `uname` varchar(32) NOT NULL COMMENT 'sys_user的name字段值', `areas` varchar(512) NOT NULL COMMENT '负责区域列表', `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`uname`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 2016-11-02 laiyinghe DB:shebao -- 增加一个业务员登记字段。 ALTER TABLE `t_ent_sign` ADD COLUMN `opt_sign` tinyint(1) NULL DEFAULT 0 COMMENT '1:业务员登记' AFTER `opt_user`; ----------------------------------- 已同步线上 @ 2017-01-05 -- 2016-11-02 laiyinghe DB:shebao -- 增加一个业务员字段。 ALTER TABLE `t_ent_sign` ADD COLUMN `opt_user` varchar(16) NULL COMMENT '业务员' AFTER `phone_time`; ALTER TABLE `t_ent_sign` MODIFY COLUMN `phone_result` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '电话回访结果'; ----------------------------------- 已同步线上 @ 2016-12-29 -- 2016-11-02 laiyinghe DB:shebao -- 增加一个顺序字段。 ALTER TABLE `t_sb_keyvalue` ADD COLUMN `order_id` smallint NULL DEFAULT 0 FIRST ; ----------------------------------- 已同步线上 @ 2016-12-15 -- 2016-11-02 chensenlai DB:shebao -- 热点问题。 CREATE TABLE `t_sb_hot_topic` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `topic` varchar(512) NOT NULL COMMENT '问题', `answer` varchar(1024) NOT NULL COMMENT '答案', `sort` int(10) NOT NULL DEFAULT 1 COMMENT '排序(降序)', `create_time` datetime NOT NULL COMMENT '创建时间', `update_time` datetime NOT NULL COMMENT '更新时间', `disabled` int(4) NOT NULL DEFAULT 2 COMMENT '禁用 1-启用 2-禁用', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='热点问题表'; -- 2016-11-02 chensenlai DB:shebao -- 用户留言。 CREATE TABLE `t_sb_leave_message` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `phone` varchar(13) NOT NULL COMMENT '用户手机号', `status` int(4) NOT NULL DEFAULT 1 COMMENT '状态 1-待回复 2-已回复', `title` varchar(128) DEFAULT NULL COMMENT '留言标题', `leave` varchar(512) NOT NULL COMMENT '留言内容', `leave_time` datetime NOT NULL COMMENT '留言时间', `feeback` varchar(1024) DEFAULT NULL COMMENT '反馈内容', `feeback_time` datetime DEFAULT NULL COMMENT '反馈时间', `feeback_kefu` int(10) DEFAULT NULL COMMENT '反馈时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户留言表'; -- 2016-12-13 laiyinghe DB:shebao -- 增加字段 ALTER TABLE `t_sb_buy_order` ADD COLUMN `pre_charge` decimal(8,2) UNSIGNED NULL DEFAULT 0 COMMENT '预收费用' AFTER `new_card`; ALTER TABLE `t_sb_buy` ADD COLUMN `pre_charge` decimal(8,2) UNSIGNED NULL DEFAULT 0 COMMENT '预收费用' AFTER `new_card`; ----------------------------------- 已同步线上 @ 2016-12-06 -- 2016-11-15 laiyinghe DB:shebao -- 增加手动增员描述的字段 ALTER TABLE `t_sb_buy_order` ADD COLUMN `manual_type` tinyint(1) UNSIGNED NULL DEFAULT 0 COMMENT '手动类型'; -- 2016-10-31 laiyinghe DB:shebao -- 增加个预收费用的字段 ALTER TABLE `t_sb_city_data` ADD COLUMN `pre_charge` decimal(8,2) UNSIGNED NULL DEFAULT 0 COMMENT '预收费用' AFTER `disabled`; ----------------------------------- 已同步线上 @ 2016-11-02 -- 2016-10-13 laiyinghe DB:shebao -- 补差额表增加一个支付渠道字段。 ALTER TABLE `t_sb_buy_order_added` ADD COLUMN `pay_channel` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '支付渠道' AFTER `months`; -- 2016-10-13 laiyinghe DB:shebao -- 日志增加一个类别字段，增加订单号为索引。 ALTER TABLE `t_log_order` ADD COLUMN `opt_type` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '操作类别' AFTER `order_no`; -- 2016-09-16 laiyinghe DB:shebao -- 增加一个补缴方案与正常方案关联的字段。 ALTER TABLE `t_sb_city` MODIFY COLUMN `proxy_type` tinyint(3) UNSIGNED NULL DEFAULT NULL COMMENT '社保的操作规则' AFTER `shebao_type`, ADD COLUMN `added_cid` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '补缴配置关联的正常配置id' AFTER `city_code`; ----------------------------------- 已同步线上 @ 2016-10-13 -- 2016-09-21 chensenlai DB:shebao -- 社保资讯文章表。 CREATE TABLE `t_sb_article` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(256) NOT NULL COMMENT '标题', `title_img` varchar(128) DEFAULT NULL COMMENT '标题图片', `cont` text NOT NULL COMMENT '内容', `cont_img` varchar(128) DEFAULT NULL COMMENT '正文图片', `hot` int(4) NOT NULL DEFAULT 3 COMMENT '热门标记 1-推广(APP首页单独) 2-热门 3-普通', `sort` int(10) NOT NULL DEFAULT 1 COMMENT '排序(降序)', `read_count` int(10) NOT NULL DEFAULT 0 COMMENT '阅读次数', `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `create_by` varchar(64) DEFAULT NULL COMMENT '来源', `disabled` int(4) NOT NULL DEFAULT 1 COMMENT '禁用 1-启用 2-禁用', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='社保资讯文章表'; ----------------------------------- 已同步线上 @ 2016-09-22 -- 2016-09-18 chensenlai DB:shebao -- 流程新增运营备注字段。 ALTER TABLE `t_sb_material_flow` ADD COLUMN `memo_op` varchar(1024) DEFAULT NULL COMMENT '运营备注(显示在后台管理系统,用于运营人员查看)' AFTER `memo`; ----------------------------------- 已同步线上 @ 2016-09-10 -- 2016-09-09 chensenlai DB:shebao -- 河北保定资料收集表。 CREATE TABLE `t_sb_info_baoding` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `u_id` int(10) NOT NULL COMMENT '系统用户标识', `id_card` varchar(20) NOT NULL COMMENT '参保人身份证号码', `bank_number` varchar(32) DEFAULT NULL COMMENT '银行号码', `bank_name` varchar(64) DEFAULT NULL COMMENT '银行名称', `bank_filiale` varchar(64) DEFAULT NULL COMMENT '开户支行', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ----------------------------------- 已同步线上 @ 2016-09-08 -- 2016-08-29 laiyinghe DB:shebao_admin -- 统计城市每天产生的金额数 CREATE TABLE `t_report_date_city` ( `id` int(11) NOT NULL AUTO_INCREMENT, `gen_date` date NOT NULL COMMENT '生成日期', `city_code` mediumint(9) NOT NULL, `order_num` mediumint(9) DEFAULT '0' COMMENT '社保公积金支付成功的订单数', `refund_order_num` mediumint(9) DEFAULT '0' COMMENT '社保公积金退款的订单数', `shebao_order_num` mediumint(9) DEFAULT NULL COMMENT '社保支付成功订单数', `shebao_amount` decimal(10,2) DEFAULT '0.00' COMMENT '社保日总额(不含卡,手续)', `shebao_charge_amount` decimal(8,2) DEFAULT '0.00' COMMENT '社保服务费总额', `shebao_card_amount` decimal(8,2) DEFAULT '0.00' COMMENT '社保卡服务费总额', `fund_order_num` mediumint(9) DEFAULT '0' COMMENT '公积金支付成功订单数', `fund_amount` decimal(10,2) DEFAULT '0.00' COMMENT '公积金总额(不含服务费)', `fund_charge_amount` decimal(8,2) DEFAULT '0.00' COMMENT '公积金服务费总额', `move_shebao_order_num` mediumint(9) DEFAULT '0' COMMENT '转移社保支付成功订单数', `move_fund_order_num` mediumint(9) DEFAULT '0' COMMENT '转移公积金支付成功的订单数', `move_refund_order_num` mediumint(9) DEFAULT '0' COMMENT '转移全退款订单数', `move_charge_amount` decimal(8,2) DEFAULT '0.00' COMMENT '转移服务费总额', `total_amount` decimal(10,2) DEFAULT '0.00' COMMENT '发生的总金额', `total_actual_amount` decimal(10,2) DEFAULT '0.00' COMMENT '实际有效金额数', `total_repay_amount` decimal(10,2) DEFAULT '0.00' COMMENT '补差额金额数', `total_refund_amount` decimal(10,2) DEFAULT '0.00' COMMENT '退款金额数', `total_discount_amount` decimal(8,2) DEFAULT '0.00' COMMENT '优惠券金额数', `total_need_repay_amount` decimal(8,2) DEFAULT '0.00' COMMENT '需要用户补差额的总额(应收)', PRIMARY KEY (`id`), KEY `report_city_date_idx` (`gen_date`), KEY `report_city_citycode_idx` (`city_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='统计城市每天产生的金额数'; -- 2016-08-29 laiyinghe DB:shebao_admin -- 统计每天产生的金额数 CREATE TABLE `t_report_month` ( `id` int(11) NOT NULL AUTO_INCREMENT, `for_month` int(6) unsigned NOT NULL COMMENT '月份', `city_code` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '城市代码', `order_num` mediumint(9) DEFAULT '0' COMMENT '社保公积金支付成功的订单数', `refund_order_num` mediumint(9) DEFAULT '0' COMMENT '社保公积金退款的订单数', `shebao_order_num` mediumint(9) DEFAULT NULL COMMENT '社保支付成功订单数', `shebao_amount` decimal(10,2) DEFAULT '0.00' COMMENT '社保有效总额(不含卡,手续)', `shebao_charge_amount` decimal(8,2) DEFAULT '0.00' COMMENT '社保服务费总额', `shebao_card_amount` decimal(8,2) DEFAULT '0.00' COMMENT '社保卡服务费总额', `fund_order_num` mediumint(9) DEFAULT '0' COMMENT '公积金支付成功订单数', `fund_amount` decimal(10,2) DEFAULT '0.00' COMMENT '公积金总额(不含服务费)', `fund_charge_amount` decimal(8,2) DEFAULT '0.00' COMMENT '公积金服务费总额', `move_shebao_order_num` mediumint(9) DEFAULT '0' COMMENT '转移社保支付成功订单数', `move_fund_order_num` mediumint(9) DEFAULT '0' COMMENT '转移公积金支付成功的订单数', `move_refund_order_num` mediumint(9) DEFAULT '0' COMMENT '转移全退款订单数', `move_charge_amount` decimal(8,2) DEFAULT '0.00' COMMENT '转移服务费总额', `total_amount` decimal(10,2) DEFAULT '0.00' COMMENT '发生的有效总金额', `total_actual_amount` decimal(10,2) DEFAULT '0.00' COMMENT '实际有效金额数', `total_repay_amount` decimal(10,2) DEFAULT '0.00' COMMENT '补差额金额数', `total_refund_amount` decimal(10,2) DEFAULT '0.00' COMMENT '退款金额数', `total_discount_amount` decimal(8,2) DEFAULT '0.00' COMMENT '优惠券金额数', `total_need_repay_amount` decimal(8,2) DEFAULT '0.00' COMMENT '需要用户补差额的总额(应收)', PRIMARY KEY (`id`), KEY `report_month_city_idx` (`city_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='统计每月产生的金额数'; ----------------------------------- 已同步线上 @ 2016-08-18 -- 2016-08-19 laiyinghe DB:shebao -- 参数配置增加两个描述字段 ALTER TABLE `t_sb_city` ADD COLUMN `shebao_desc` varchar(128) NULL DEFAULT '' COMMENT '社保描述' AFTER `fund_buy`, ADD COLUMN `fund_desc` varchar(128) NULL DEFAULT '' COMMENT '公积金描述' AFTER `shebao_desc`; ----------------------------------- 已同步线上 @ 2016-08-08 -- 2016-08-03 laiyinghe DB:shebao -- 用户申请退款原因。 ALTER TABLE `t_sb_buy_order` ADD COLUMN `refund_reason` varchar(128) NULL COMMENT '用户申请退款原因'; ----------------------------------- 已同步线上 @ 2016-07-29 -- 2016-07-29 chensenlai DB:shebao -- 天津\上海\北京资料收集表。 CREATE TABLE `t_sb_info_tianjin` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `u_id` int(10) NOT NULL COMMENT '系统用户标识', `id_card` varchar(20) NOT NULL COMMENT '参保人身份证号码', `user_name` varchar(12) NOT NULL COMMENT '参保人姓名', `gender` varchar(2) DEFAULT NULL COMMENT '性别(男/女)', `nation` varchar(16) DEFAULT NULL COMMENT '民族', `birthcity` varchar(32) DEFAULT NULL COMMENT '出生城市', `birthday` varchar(12) DEFAULT NULL COMMENT '出生年月', `political_status` varchar(8) DEFAULT NULL COMMENT '政治面貌', `phone` varchar(16) DEFAULT NULL COMMENT '手机号', `education_level` varchar(8) DEFAULT NULL COMMENT '文化程度', `graduate_school` varchar(32) DEFAULT NULL COMMENT '毕业学校', `graduate_time` varchar(12) DEFAULT NULL COMMENT '毕业时间', `major` varchar(32) DEFAULT NULL COMMENT '所学专业', `hk_type` varchar(8) DEFAULT NULL COMMENT '户口类型', `hk_street` varchar(128) DEFAULT NULL COMMENT '户口所在街区', `address` varchar(128) DEFAULT NULL COMMENT '常住地址', `postcode` varchar(8) DEFAULT NULL COMMENT '邮政编码', `hk_address` varchar(128) DEFAULT NULL COMMENT '户口所在地址', `hk_time` varchar(12) DEFAULT NULL COMMENT '户口登记日期', `work_time` varchar(12) DEFAULT NULL COMMENT '工作时间', `professional_post` varchar(32) DEFAULT NULL COMMENT '专业技术职务', `qualification_level` varchar(32) DEFAULT NULL COMMENT '职业资格证等级', `qualification_Time` varchar(12) DEFAULT NULL COMMENT '取得资格证日期', `country_qualification_level` varchar(32) DEFAULT NULL COMMENT '国家职业资格等级', `email` varchar(32) DEFAULT NULL COMMENT '邮件地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t_sb_info_shanghai` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `u_id` int(10) NOT NULL COMMENT '系统用户标识', `id_card` varchar(20) NOT NULL COMMENT '参保人身份证号码', `user_name` varchar(12) NOT NULL COMMENT '参保人姓名', `gender` varchar(2) DEFAULT NULL COMMENT '性别(男/女)', `nation` varchar(16) DEFAULT NULL COMMENT '民族', `phone` varchar(16) DEFAULT NULL COMMENT '手机号', `used_name` varchar(12) DEFAULT NULL COMMENT '曾用名', `political_status` varchar(8) DEFAULT NULL COMMENT '政治面貌', `education_level` varchar(16) DEFAULT NULL COMMENT '受教育程度', `other_phone` varchar(16) DEFAULT NULL COMMENT '其他联系方式', `marriage_status` varchar(8) DEFAULT NULL COMMENT '婚姻情况', `spause_name` varchar(12) DEFAULT NULL COMMENT '配偶姓名', `spause_id_card` varchar(20) DEFAULT NULL COMMENT '配偶身份证', `is_spause_local_hk` varchar(2) DEFAULT NULL COMMENT '配偶是否本地户籍', `children_num` varchar(2) DEFAULT NULL COMMENT '子女数目', `health_status` varchar(32) DEFAULT NULL COMMENT '个人健康状况', `hk_type` varchar(8) DEFAULT NULL COMMENT '户籍类别', `hk_address` varchar(128) DEFAULT NULL COMMENT '户籍地址', `address` varchar(128) DEFAULT NULL COMMENT '居住地址', `address_street` varchar(128) DEFAULT NULL COMMENT '居住地街道', `postcode` varchar(8) DEFAULT NULL COMMENT '邮政编码', `is_have_residence` varchar(8) DEFAULT NULL COMMENT '是否领居住证', `residence_code` varchar(32) DEFAULT NULL COMMENT '居住证号码', `residence_type` varchar(8) DEFAULT NULL COMMENT '居住证类型', `email` varchar(32) DEFAULT NULL COMMENT '电子邮箱', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t_sb_info_beijing` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `u_id` int(10) NOT NULL COMMENT '系统用户标识', `id_card` varchar(20) NOT NULL COMMENT '参保人身份证号码', `img_path` varchar(64) NOT NULL COMMENT '上传图片路径', `bank_number` varchar(32) DEFAULT NULL COMMENT '银行号码', `bank_name` varchar(64) DEFAULT NULL COMMENT '银行名称', `bank_filiale` varchar(64) DEFAULT NULL COMMENT '开户支行', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ----------------------------------- 已同步线上 @ 2016-07-18 -- 2016-07-12 laiyinghe DB:shebao -- 转移表增加运营备注的字段。 ALTER TABLE `t_sb_move` ADD COLUMN `notes` varchar(128) NULL COMMENT '运营备注'; -- 2016-07-05 chensenlai DB:shebao -- 流程新增备注字段，显示在app上。 ALTER TABLE `t_sb_material_flow` ADD COLUMN `memo` varchar(1024) DEFAULT NULL COMMENT '备注信息' AFTER `todo_flag`; ----------------------------------- 已同步线上 @ 2016-06-27 -- 2016-06-23 laiyinghe DB:shebao -- 修改备注字段长度到128，增加标记的状态字段。 ALTER TABLE `t_sb_buy_order` MODIFY COLUMN `app_remark` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机端补缴退款提示' AFTER `gen_time`, ADD COLUMN `sign_status` smallint UNSIGNED NULL DEFAULT 0 COMMENT '标记的状态' AFTER `first_buy`; ----------------------------------- 已同步线上 @ 2016-06-20 -- 2016-06-02 laiyinghe DB:shebao -- 已购买记录表增加参数配置的标识字段(保存购买过的档位) ALTER TABLE `t_sb_buyed` ADD COLUMN `cid` mediumint UNSIGNED NULL COMMENT '参数配置的标识' AFTER `city_code`; UPDATE t_sb_buyed a SET a.cid=( SELECT b.id FROM t_sb_city b WHERE b.city_code=a.city_code ); -- 2016-05-31 laiyinghe DB:shebao -- 收件地址表设计 CREATE TABLE `t_user_address` ( `u_id` int(10) unsigned NOT NULL, `u_name` varchar(20) DEFAULT '姓名', `phone` varchar(16) DEFAULT '' COMMENT '电话', `area` varchar(64) DEFAULT NULL COMMENT '区域', `address` varchar(256) DEFAULT NULL COMMENT '地址', `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', KEY `user_address_uid_idx` (`u_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ----------------------------------- 已同步线上 @ 2016-05-26 -- 2016-05-26 chensenlai DB:shebao -- 修改短信长度 alter table t_sb_city_ext modify column shebao_inc_sms varchar(512) DEFAULT NULL COMMENT '短信内容'; alter table t_sb_city_ext modify column shebao_inc_sms_next varchar(512) DEFAULT NULL COMMENT '短信内容'; alter table t_sb_city_ext modify column fund_inc_sms varchar(512) DEFAULT NULL COMMENT '短信内容'; alter table t_sb_city_ext modify column fund_inc_sms_next varchar(512) DEFAULT NULL COMMENT '短信内容'; alter table t_sb_city_ext modify column shebao_card_sms varchar(512) DEFAULT NULL COMMENT '短信内容'; ----------------------------------- 已同步线上 @ 2016-05-18 -- 2016-05-05 chensenlai DB:shebao -- 城市资料跟踪配置表,增加续保配置（原配置当成首次配置）。 -- 修改原来备注信息 ALTER TABLE `t_sb_city_ext` MODIFY COLUMN `shebao_inc_info` tinyint(4) unsigned NOT NULL COMMENT '首次增员资料提交方式'; ALTER TABLE `t_sb_city_ext` MODIFY COLUMN `fund_inc_info` tinyint(3) unsigned DEFAULT NULL COMMENT '首次公积金增员资料'; ALTER TABLE `t_sb_city_ext` MODIFY COLUMN `fund_inc_sms` varchar(128) DEFAULT NULL COMMENT '短信内容'; -- 新增续保配置信息 ALTER TABLE `t_sb_city_ext` ADD COLUMN `shebao_inc_info_next` tinyint(4) unsigned NOT NULL COMMENT '续保增员资料提交方式' AFTER `shebao_inc_sms`; ALTER TABLE `t_sb_city_ext` ADD COLUMN `shebao_inc_sms_next` varchar(128) DEFAULT NULL COMMENT '短信内容' AFTER `shebao_inc_info_next`; ALTER TABLE `t_sb_city_ext` ADD COLUMN `fund_inc_info_next` tinyint(3) unsigned DEFAULT NULL COMMENT '续保公积金增员资料' AFTER `fund_inc_sms`; ALTER TABLE `t_sb_city_ext` ADD COLUMN `fund_inc_sms_next` varchar(128) DEFAULT NULL COMMENT '短信内容' AFTER `fund_inc_info_next`; -- 更新数据 -- 运营人员自己配置 by chensenlai 2016-05-20 -- UPDATE t_sb_city_ext SET shebao_inc_info_next = shebao_inc_info, shebao_inc_sms_next = shebao_inc_sms, fund_inc_info_next = fund_inc_info, fund_inc_sms_next = fund_inc_sms; -- 2016-04-27 lyh DB:shebao -- 首次购买表字字段 ALTER TABLE `t_sb_buy` ADD COLUMN `first_sb` tinyint UNSIGNED DEFAULT '0' COMMENT '此城市首次参保', ADD COLUMN `first_fund` tinyint UNSIGNED DEFAULT '0' COMMENT '此城市首次缴公积金'; ALTER TABLE `t_sb_buy_order` ADD COLUMN `first_buy` tinyint UNSIGNED DEFAULT '0' COMMENT '此城市首次购买'; -- 2016-04-21 lyh DB:shebao -- 增加转移的配置表 CREATE TABLE `t_sb_city_move` ( `city_code` int(10) unsigned NOT NULL COMMENT '城市代码', `is_out` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '能否转出', `shebao_out_sms` varchar(128) DEFAULT NULL COMMENT '社保转出配置短信内容', `fund_out_sms` varchar(128) DEFAULT NULL COMMENT '公积金转出配置短信内容', `is_in` tinyint(3) unsigned NOT NULL COMMENT '能否转入', `shebao_in_sms` varchar(128) DEFAULT NULL COMMENT '社保转入配置短信内容', `fund_in_sms` varchar(128) DEFAULT NULL COMMENT '公积金转入配置短信内容', `charge` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '手续费', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`city_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='转入转出配置'; -- 2016-04-21 lyh DB:shebao -- 增加转移表 CREATE TABLE `t_sb_move` ( `move_no` varchar(32) NOT NULL COMMENT '转移订单号', `pay_uid` int(10) unsigned NOT NULL COMMENT '转移帐号标识', `id_card` varchar(20) DEFAULT NULL COMMENT '身份证', `user_name` varchar(20) NOT NULL COMMENT '转移人姓名', `move_type` tinyint(3) unsigned NOT NULL COMMENT '转移类型：1社保 2公积金', `out_city` mediumint(8) unsigned DEFAULT NULL COMMENT '转出城市', `into_city` mediumint(8) unsigned DEFAULT NULL COMMENT '转入城市', `couponuser_id` varchar(20) DEFAULT NULL COMMENT '用户现金券标识', `couponuser_price` decimal(6,2) unsigned DEFAULT NULL COMMENT '用户折扣掉金额', `total_charge` decimal(8,2) unsigned NOT NULL COMMENT '总金额', `actual_charge` decimal(8,2) unsigned NOT NULL COMMENT '优惠后金额', `status` tinyint(3) unsigned NOT NULL COMMENT '状态', `gen_time` datetime NOT NULL, `pay_channel` varchar(16) NOT NULL COMMENT '支付渠道标识', `pay_no` varchar(128) DEFAULT NULL COMMENT '第三方支付流水号', `remark` varchar(128) DEFAULT NULL, PRIMARY KEY (`move_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='转移'; ALTER TABLE `t_sb_move` ADD INDEX `move_uid_idx` (`pay_uid`) ; -- 2016-04-21 lyh DB:shebao -- 城市配置表增加转移标志字段。 ALTER TABLE `t_city` ADD COLUMN `move_disabled` tinyint(1) UNSIGNED NULL DEFAULT 0 COMMENT '转移禁用' AFTER `disabled`; -- 2016-04-21 lyh DB:shebao -- 已支付成功时，也保存户籍 ALTER TABLE `t_sb_buyed` ADD COLUMN `hukou` tinyint UNSIGNED NULL COMMENT '购买的户籍'; -- 2016-04-20 lyh DB:shebao -- 补缴增加字段 ALTER TABLE `t_sb_city_added` ADD COLUMN `added_charge` decimal(6,2) UNSIGNED NULL COMMENT '补缴服务费', ADD COLUMN `shebao_num` tinyint UNSIGNED NULL COMMENT '社保可补缴月数', ADD COLUMN `fund_num` tinyint UNSIGNED NULL COMMENT '公积金补缴月份数'; ----------------------------------- 已同步线上 @ 2016-04-20 -- 2016-04-12 lyh DB:shebao -- 企业用户备注字段更改类型。 ALTER TABLE `t_ent_sign` MODIFY COLUMN `phone_result` varchar(64) NULL DEFAULT '' COMMENT '电话回访结果'; ----------------------------------- 已同步线上 @ 2016-04-18 -- 2016-04-12 lyh DB:shebao -- 增加订单变动日志表。 CREATE TABLE `t_log_order` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `order_no` varchar(32) NOT NULL, `opt_user` varchar(16) NOT NULL, `opt_content` varchar(256) NOT NULL, `gen_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='订单变动日志'; -- 2016-04-12 lyh DB:shebao -- 增加登录日志表。 CREATE TABLE `t_log_login` ( `u_id` int(10) unsigned NOT NULL, `app_version` varchar(12) DEFAULT NULL, `login_ip` varchar(32) DEFAULT NULL, `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 2016-04-14 chensenlai DB:shebao_admin -- 新增临时卡登记表，登记社保号和公积金号 CREATE TABLE `t_tmp_card` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `batch_no` varchar(32) COMMENT '导入批次号', `id_card` varchar(20) COMMENT '参保人身份证', `shebao_card` varchar(32) COMMENT '社保号', `fund_card` varchar(32) COMMENT '公积金号', `status` int(4) unsigned NOT NULL DEFAULT '1' COMMENT '处理状态标识：1-未处理/2-处理成功/3-处理失败', `memo` varchar(255) COMMENT '备注', `create_time` datetime NOT NULL COMMENT '导入时间', PRIMARY KEY (`id`), KEY `uid_idx` (`batch_no`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='参保人员社保号公积金号批量导入临时登记表'; -- 2016-04-14 chensenlai DB:shebao -- 参保人表新增公积金号 ALTER TABLE `t_sb_user` ADD COLUMN `fund_card` varchar(32) NULL COMMENT '公积金号' AFTER `shebao_card`; -- 2016-04-14 lyh DB:shebao-admin -- 减员表增加一个参考月份字段，减员就是对这个月份检查有无购买。 ALTER TABLE `t_sb_stop` ADD COLUMN `via_month` mediumint UNSIGNED NULL DEFAULT 0 COMMENT '参考月份' AFTER `stop_month`; -- 2016-04-13 lyh DB:shebao-admin -- 原因字段改成20个长度。 ALTER TABLE `t_sb_employee` MODIFY COLUMN `any_reason` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '失败原因'; -- 2016-04-12 lyh DB:shebao -- 消息表增加手机字段，用于支持参保人联系的手机号收短信。 ALTER TABLE `t_sb_msg` ADD COLUMN `phone` varchar(16) NULL COMMENT '手机号' AFTER `channel`; ----------------------------------- 已同步线上 @ 2016-04-07 -- 2016-04-07 lyh DB:shebao -- 企业登记表--增加回访结果 ALTER TABLE `t_ent_sign` ADD COLUMN `phone_result` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '电话回访结果', ADD COLUMN `phone_time` datetime NULL COMMENT '回访时间'; -- 2016-04-01 lyh DB:shebao -- 增加表--补缴配置 CREATE TABLE `t_sb_city_added` ( `cid` int(10) unsigned NOT NULL, `shebao_note` varchar(252) DEFAULT NULL COMMENT '社保补缴说明', `fund_note` varchar(252) DEFAULT NULL COMMENT '公积金补缴说明', PRIMARY KEY (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 2016-03-31 lyh DB:shebao -- 原手动标识改为手动增员月份 ALTER TABLE `t_sb_buy_order` MODIFY COLUMN `manual` mediumint UNSIGNED NOT NULL DEFAULT 0 COMMENT '手动增员月份' AFTER `added`; -- ----begin 资料跟踪需求 -- 20160322 chensenlai DB:shebao -- 资料跟踪流程登记表 CREATE TABLE `t_sb_material_flow` ( `f_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `source` int(4) unsigned NOT NULL COMMENT '1-社保/2-公积金/3-社保卡', `source_op` int(4) unsigned NOT NULL COMMENT '0-无/1-电子/2-邮寄', `source_type` int(4) unsigned NOT NULL DEFAULT '1' COMMENT '1-系统/2-人工', `u_id` int(10) NOT NULL COMMENT '系统用户标识(流程所属用户)', `city_code` int(10) COMMENT '参保城市', `user_name` varchar(12) COMMENT '参保人姓名', `id_card` varchar(20) COMMENT '参保人身份证', `buy_no` varchar(32) COMMENT '资料关联买单编号', `flow_status` int(4) unsigned NOT NULL DEFAULT '1' COMMENT '跟踪流程状态标识：1-办理中/2-已完成/3-已关闭/4-APP隐藏(超过30天隐藏)/5-已删除', `cur_node_type` int(4) unsigned NOT NULL DEFAULT '1000' COMMENT '跟踪流程当前节点类型，默认开始节点', `todo_flag` int(4) unsigned NOT NULL DEFAULT '2' COMMENT '标识当前流程应该由谁处理(当前节点配置决定)。1-APP标识红点 2-APP不标识红点', `create_time` datetime NOT NULL COMMENT '资料流程创建时间', `update_time` datetime NOT NULL COMMENT '资料流程更新时间', PRIMARY KEY (`f_id`), KEY `uid_idx` (`u_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='资料跟踪流程登记表'; -- 资料跟踪流程节点登记表 CREATE TABLE `t_sb_material_node` ( `n_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `f_id` int(10) unsigned NOT NULL COMMENT '节点关联流程id', `node_type` int(4) unsigned NOT NULL COMMENT '节点类型，见枚举值定义', `memo` varchar(1024) COMMENT '备注信息', `create_time` datetime NOT NULL COMMENT '节点创建时间', `update_time` datetime NOT NULL COMMENT '节点更新时间', PRIMARY KEY (`n_id`), KEY `fid_idx` (`f_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='资料跟踪流程节点登记表'; -- 资料信息登记表 CREATE TABLE `t_sb_material` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `f_id` int(10) unsigned NOT NULL COMMENT '资料关联流程id', `n_id` int(10) unsigned NOT NULL COMMENT '资料关联节点id', `file_index` int(10) unsigned NOT NULL COMMENT '资料文件索引下标', `file_path` varchar(512) NOT NULL COMMENT '资料上传路径', `material_status` int(4) unsigned NOT NULL DEFAULT '1' COMMENT '材料状态标识：1-有效/2-已删除', `create_time` datetime NOT NULL COMMENT '资料创建时间', PRIMARY KEY (`id`), KEY `fid_idx` (`f_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='资料信息登记表'; -- 资料消息提醒登记表 CREATE TABLE `t_sb_material_msg` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `f_id` int(10) unsigned NOT NULL COMMENT '资料关联流程id', `n_id` int(10) unsigned NOT NULL COMMENT '资料关联节点id', `msg_type` int(10) unsigned COMMENT 'msg_type异步发送消息', `title` varchar(255) NOT NULL COMMENT '消息标题', `content` varchar(1024) NOT NULL COMMENT '消息内容', `create_time` datetime NOT NULL COMMENT '资料创建时间', PRIMARY KEY (`id`), KEY `fid_idx` (`f_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='资料消息提醒登记表'; -- ---- end 资料跟踪需求 ----------------------------------- 已同步线上 @ 2016-03-25 --2016-03-23 lyh DB:shebao --- 记录公积金购买的金额、月份 ALTER TABLE `t_sb_buyed` ADD COLUMN `fund_price` double(8,2) UNSIGNED NOT NULL DEFAULT 0.0 COMMENT '公积金购买金额', ADD COLUMN `mfund` mediumint UNSIGNED NOT NULL DEFAULT 0 COMMENT '公积金购买月份' AFTER `fund_price`; ----------------------------------- 已同步线上 @ 2016-03-23 --2016-03-23 lyh DB:shebao --- 增加短信发送标注字段、增加消息发送方式字段 ALTER TABLE `t_sb_msg` ADD COLUMN `channel` varchar(4) DEFAULT 'p' COMMENT 'p推送、s短信、ps推送+短信' AFTER `content`; ALTER TABLE `t_sb_msg` ADD COLUMN `smsed` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '短信是否发送' AFTER `sended`; --2016-03-21 lyh DB:shebao --- 城市配置扩展表 CREATE TABLE `t_sb_city_ext` ( `cid` int(10) unsigned NOT NULL, `shebao_inc_info` tinyint(4) unsigned NOT NULL COMMENT '增员资料提交方式', `shebao_inc_sms` varchar(128) DEFAULT NULL COMMENT '短信内容', `fund_inc_info` tinyint(3) unsigned DEFAULT NULL COMMENT '公积金增员资料', `fund_inc_sms` varchar(128) DEFAULT NULL, `shebao_card_info` tinyint(4) unsigned NOT NULL COMMENT '新办卡资料投递方式', `shebao_card_sms` varchar(128) DEFAULT NULL COMMENT '短信内容', PRIMARY KEY (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='城市资料、补缴等配置'; ----------------------------------- 已同步线上 @ 2016-03-18 --2016-03-15 lyh DB:shebao --- 消息按类型发送 CREATE TABLE `t_sb_msg_type` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `channel` varchar(12) NOT NULL COMMENT '消息发送方式', `receive` varchar(12) NOT NULL COMMENT '接收人类型', `conditions` text COMMENT '接收人条件', `title` varchar(128) NOT NULL COMMENT '消息标题', `content` varchar(256) NOT NULL DEFAULT '内容', `sizes` int(10) unsigned NOT NULL COMMENT '人数', `counts` int(10) unsigned DEFAULT '0' COMMENT '已发送数', `gen_sec` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间-秒', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --2016-03-15 lyh DB:shebao --- 消息增加注解，用于显示消息来源等 ALTER TABLE `t_sb_msg` ADD COLUMN `type_msg_id` int(10) NULL; ALTER TABLE `t_sb_msg` ADD INDEX `msg_uid_idx` (`u_id`) , ADD INDEX `msg_tid_idx` (`type_msg_id`) ; --------------------------- 已同步线上@2016-03-10 --20160309 lyh DB:shebao --- 公积金是否能够单独购买 ALTER TABLE `t_sb_city` ADD COLUMN `fund_buy` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '单独购买' AFTER `fund_opt`; --------------------------- 以下是 V1.2.3 已同步线上@2016-02-24 --20160218 lyh DB:shebao_admin ---是否进行了开卡标志 ALTER TABLE `t_sb_employee` ADD COLUMN `new_card` tinyint(1) UNSIGNED NULL DEFAULT 0 COMMENT '是否进行了开卡' AFTER `gen_time`; --20160218 lyh DB:shebao_admin ---是否进行了开卡标志 ALTER TABLE `t_sb_buy_order_ext` ADD COLUMN `new_card` tinyint(1) UNSIGNED NULL DEFAULT 0 COMMENT '是否进行了开卡' AFTER `repay_reason`; --20160126 侯庆营 DB:shebao ---订单表添加字段是否已手动增员 ALTER TABLE `shebao`.`t_sb_buy_order` ADD COLUMN `manual` TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL COMMENT '是否已手动增员' AFTER `added`; --20160118 赖应和 DB:shebao ---城市显示顺序 ALTER TABLE `t_city` ADD COLUMN `seque` tinyint UNSIGNED NULL COMMENT '显示顺序'; --20160118 侯庆营 DB:shebao ---增员表添加补缴增员月份 ALTER TABLE `t_sb_employee` ADD COLUMN `added_month` mediumint(8) UNSIGNED NULL COMMENT '补缴增员月份'; --20160118 侯庆营 DB:shebao ---订单是否补缴单的标识 ALTER TABLE `t_sb_buy_order` ADD COLUMN `added` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否补缴单'; --20160118 赖应和 DB:shebao ---购买表增是否补缴单的标识 ALTER TABLE `t_sb_buy` ADD COLUMN `added` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否补缴单' AFTER `audit`; --------------------------- 以下是 V1.2.2 已同步线上@2016-01-13 --20160113 侯庆营 DB:shebao ---更改数据类型。 ALTER TABLE `shebao`.`t_sb_coupon_user` CHANGE `phone` `phone` VARCHAR(11) NULL COMMENT '绑定手机号'; --20160113 侯庆营 DB:shebao ---添加分享使用上限。 ALTER TABLE `shebao`.`t_sb_link` ADD COLUMN `share_limit` INT(10) NULL COMMENT '分享使用上限'; --20160113 侯庆营 DB:shebao ---添加买单审核状态。 ALTER TABLE `shebao`.`t_sb_buy` ADD COLUMN `audit` TINYINT(1) DEFAULT 0 NULL COMMENT '审核状态（0，未审核；1，已审核）'; --20160112 侯庆营 DB:shebao ---添加链接点击次数。 ALTER TABLE `shebao`.`t_sb_link` ADD COLUMN `count` INT(10) DEFAULT 0 NULL COMMENT '点击次数' AFTER `comment`; --20160108 侯庆营 DB:shebao ---添加链接备注。 ALTER TABLE `shebao`.`t_sb_link` ADD COLUMN `comment` VARCHAR(200) NULL COMMENT '备注'; --20160107 侯庆营 DB:shebao ---本单实际支付金额。 ALTER TABLE `shebao`.`t_sb_buy` ADD COLUMN `total_fee_actual` DECIMAL(8,2) DEFAULT 0.00 NULL COMMENT '本单实际支付金额'; --20160106 侯庆营 DB:shebao ---添加字段链接分享来源标识。 ALTER TABLE `shebao`.`t_sb_link` ADD COLUMN `share_id` VARCHAR(11) NULL COMMENT '分享来源标识'; --20160105 侯庆营 DB:shebao ---添加字段链接代码。 ALTER TABLE `shebao`.`t_sb_coupon_user` ADD COLUMN `recommend_code` VARCHAR(50) NULL COMMENT '链接代码'; --20160104 侯庆营 DB:shebao ---现金券相关。 CREATE TABLE `shebao`.`t_sb_coupon`( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID标识', `name` VARCHAR(255) NOT NULL COMMENT '现金券名称', `status` TINYINT(1) NOT NULL COMMENT '现金券状态', `start_date` DATETIME COMMENT '开始时间', `end_date` DATETIME COMMENT '结束时间', `type` TINYINT(2) COMMENT '现金券类型', `create_time` DATETIME COMMENT '创建时间', `description` VARCHAR(255) COMMENT '描述（使用规则等）', `price` DOUBLE(10,2) COMMENT '现金券金额', `init_number` INT(11) COMMENT '初始化数量（运营操作）', `bind_number` INT(11) COMMENT '已绑定数量（手机绑定）', `used_number` INT(11) COMMENT '已消费数量（订单使用）', `send_number` INT(11) COMMENT '已发放数量（预留占用）', `person_limit` TINYINT(2) COMMENT '单个用户领取限额', `month_limit` TINYINT(2) DEFAULT 0 NULL COMMENT '有效月数', PRIMARY KEY (`id`) ) COMMENT='现金券表'; CREATE TABLE `shebao`.`t_sb_coupon_user`( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID标识', `coupon_id` INT(11) COMMENT '现金券ID', `phone` INT(11) COMMENT '绑定手机号', `coupon_code` VARCHAR(50) COMMENT '现金券编号', `coupon_price` DOUBLE(10,2) COMMENT '现金券金额', `user_id` INT(10) COMMENT '绑定用户ID', `user_name` VARCHAR(32) COMMENT '绑定用户名', `create_time` DATETIME COMMENT '创建时间', `end_time` DATETIME NULL COMMENT '截止时间', `consume_time` DATETIME COMMENT '消费时间', `order_no` VARCHAR(32) COMMENT '订单号', `order_price` DOUBLE(15,2) COMMENT '订单总金额', `create_from` INT(2) NULL COMMENT '领券来源（1，后台运营绑定；2，注册用户分享；3，广告投放）', `status` INT(2) COMMENT '使用状态（1，未使用；2，已使用；3，已过期）', PRIMARY KEY (`id`) ) COMMENT='现金券用户关联表'; ALTER TABLE `shebao`.`t_sb_coupon_user` ADD COLUMN `recommend_phone` VARCHAR(11) NULL COMMENT '推荐人手机号' AFTER `status`, ADD COLUMN `recommend_code` VARCHAR(50) NULL COMMENT '链接代码' AFTER `recommend_phone`; ALTER TABLE `shebao`.`t_sb_coupon` ADD COLUMN `channels` VARCHAR(255) NULL COMMENT '领券渠道限制' AFTER `month_limit`; CREATE TABLE `shebao`.`t_sb_coupon_channel`( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '领券来源ID标识', `name` VARCHAR(100) COMMENT '领券来源名称', PRIMARY KEY (`id`) ) COMMENT='领券来源定义'; ALTER TABLE `shebao`.`t_sb_buy` ADD COLUMN `couponuser_id` VARCHAR(255) NULL COMMENT '用户现金券逗号分ID' AFTER `gen_time`; ALTER TABLE `shebao`.`t_sb_buy` ADD COLUMN `couponuser_price` INT(10) NULL COMMENT '用户现金券金额' AFTER `couponuser_id`; CREATE TABLE `shebao`.`t_sb_link`( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '标识ID', `url` VARCHAR(200) NOT NULL COMMENT '链接URL', `code` VARCHAR(50) NOT NULL COMMENT '链接码', `end_date` DATETIME COMMENT '失效时间', `type` TINYINT(2) NOT NULL COMMENT '链接类型', `create_time` DATETIME NOT NULL COMMENT '创建时间', `purpose` TINYINT(2) NOT NULL COMMENT '链接用途', `month_limit` TINYINT(2) unsigned DEFAULT '0' COMMENT '有效月数', PRIMARY KEY (`id`) ); --20151216 赖应和 DB:shebao ---企业资料登记。 CREATE TABLE `t_ent_sign` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `enName` varchar(64) NOT NULL COMMENT '企业名称', `contact_name` varchar(32) NOT NULL COMMENT '联系人', `contact_phone` varchar(16) NOT NULL COMMENT '联系电话', `demand` varchar(32) NOT NULL COMMENT '业务', `employees` varchar(12) NOT NULL COMMENT '员工数', `city` varchar(32) NOT NULL, `remark` varchar(256) DEFAULT NULL COMMENT '备注', `gen_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8; --20151216 赖应和 DB:shebao_admin --- 记录参数配置改动 CREATE TABLE `t_log_city_param` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `cid` int(10) unsigned NOT NULL COMMENT '参数标识', `city_name` varchar(32) DEFAULT NULL, `modify_type` tinyint(3) unsigned NOT NULL COMMENT '0主数据，1~4对应户籍', `pre_content` mediumtext COMMENT '修改前内容', `curr_content` mediumtext NOT NULL COMMENT '当前内容', `admin_user` varchar(32) NOT NULL COMMENT '修改人', `modify_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; --20151216 赖应和 DB:shebao ---用户查询社保、公积金的依赖数据。 CREATE TABLE `t_sb_city_site` ( `cid` int(10) unsigned NOT NULL AUTO_INCREMENT, `city_name` varchar(32) NOT NULL COMMENT '城市名称', `shebao_site` varchar(256) DEFAULT NULL COMMENT '社保网站', `shebao_wx` varchar(256) DEFAULT NULL, `fund_site` varchar(256) DEFAULT NULL COMMENT '公积金网站', `fund_wx` varchar(256) DEFAULT NULL COMMENT '微信公众号', `notice` varchar(256) DEFAULT NULL COMMENT '提示', `hot` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否热闹城市', PRIMARY KEY (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='城市社保、公积金供查询信息'; --------------------------- 以下是 V1.1.2 已同步线上@20151215 --20151109 赖应和 DB:shebao_admin ---日志记录。 CREATE TABLE `t_log_sys` ( `task_id` varchar(16) NOT NULL COMMENT '日志标识', `task_type` varchar(16) DEFAULT NULL, `content` mediumtext COMMENT '内容', `exec_result` varchar(16) DEFAULT NULL COMMENT '任务结果', `u_id` int(10) unsigned DEFAULT NULL, `exec_time` datetime DEFAULT NULL COMMENT '执行时间', PRIMARY KEY (`task_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='运行日志'; --20151109 赖应和 DB:shebao ---增加用于支付的订单号字段，为支持同一buy_no，不同金额。唯一约束。 ALTER TABLE `t_sb_buy` ADD COLUMN `trade_no` varchar(64) NULL COMMENT '用于支付的单号' AFTER `buy_no`, ADD UNIQUE INDEX `buy_trade_no` (`trade_no`) ; --20151109 赖应和 DB:shebao ---补缴表格 CREATE TABLE `t_sb_buy_order_added` ( `added_no` varchar(32) NOT NULL COMMENT '补缴订单号(BJ开头)', `title` varchar(64) NOT NULL COMMENT '标题', `pay_uid` int(10) unsigned NOT NULL, `id_card` varchar(20) NOT NULL, `city_code` int(10) unsigned NOT NULL, `reason` varchar(32) NOT NULL COMMENT '原因', `amount` decimal(8,2) unsigned NOT NULL COMMENT '金额', `orders` varchar(300) NOT NULL COMMENT '订单号用英文逗号间隔', `months` varchar(128) NOT NULL COMMENT '月份用英文逗号间隔', PRIMARY KEY (`added_no`), KEY `t_order_added_uid_idx` (`pay_uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `t_sb_buy_order_added` ADD COLUMN `pay_no` varchar(128) NULL DEFAULT '' COMMENT '支付流水号' AFTER `months`, ADD COLUMN `status` tinyint(1) UNSIGNED NULL DEFAULT 1 COMMENT '状态1待支付 2已支付' AFTER `pay_no`; ALTER TABLE `t_sb_buy_order_added` ADD COLUMN `gen_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `status`; --------------------------- 以下是 V1.1.1 已同步线上@20151120 --20151109 赖应和 DB:shebao ---记录社保的增、减员截止日期 ALTER TABLE `t_sb_city` ADD COLUMN `fund_opt` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '公积金操作规则' AFTER `dec_date`; ALTER TABLE `t_sb_city` CHANGE COLUMN `end_date` `dec_date` tinyint UNSIGNED NULL DEFAULT 15 COMMENT '减员截止日期' AFTER `package_charge`, ADD COLUMN `inc_date` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '增员截止日期' AFTER `package_charge`; --20151107 赖应和 DB:shebao ---创建表记录参保人购买过的城市的社保年或月份 CREATE TABLE `t_sb_buyed` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `sbu_id` int(10) unsigned NOT NULL COMMENT '参保人标识', `city_code` mediumint(8) unsigned NOT NULL COMMENT '城市代码', `mshebao` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '购买年或月份', `update_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `t_sb_u_buyed_uid` (`sbu_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --20151030 侯庆营 DB:shebao-admin -- 添加退款理由字段： ALTER TABLE `shebao_admin`.`t_sb_buy_order_ext` ADD COLUMN `refund_reason` VARCHAR(200) NULL COMMENT '退款理由' AFTER `refund_amount`; --20151026 赖应和 DB:shebao --- 增加配置字段 ALTER TABLE `t_sb_city_data` MODIFY COLUMN `supplement` varchar(64) NULL DEFAULT NULL COMMENT '补充医疗' AFTER `village`, ADD COLUMN `large_major` varchar(64) NULL COMMENT '大病(额)医疗' AFTER `major`, ADD COLUMN `education` varchar(64) NULL COMMENT '教育统筹经费' AFTER `village`, ADD COLUMN `warm` varchar(64) NULL COMMENT '采暖费' AFTER `education`, ADD COLUMN `archives` varchar(64) NULL COMMENT '档案费' AFTER `warm`, ADD COLUMN `big_major` varchar(64) NULL COMMENT '大病(额)医疗' AFTER `supplement`; ALTER TABLE `t_sb_city_data` ADD COLUMN `hospital` varchar(64) NULL DEFAULT '' COMMENT '住院医疗' AFTER `injury`, ADD COLUMN `added` varchar(64) NULL DEFAULT '' COMMENT '补充医疗' AFTER `archives`; --- 增加索引 ALTER TABLE `t_sb_city_data` ADD INDEX `t_cityd_cid_idx` (`cid`) ; ------------------ 以下脚本已线上处理！ --20151020 侯庆营 DB:shebao-admin -- 添加退款字段： ALTER TABLE `shebao_admin`.`t_sb_buy_order_ext` ADD COLUMN `refund_type` TINYINT(3) NULL COMMENT '退款类型' AFTER `note`, ADD COLUMN `refund_amount` DECIMAL(8,2) NULL COMMENT '退款金额' AFTER `refund_type`; --20151020 侯庆营 DB:shebao-admin -- 添加补缴字段： ALTER TABLE `shebao_admin`.`t_sb_buy_order_ext` ADD COLUMN `repay_type` TINYINT(3) NULL COMMENT '补缴类型' AFTER `refund_amount`, ADD COLUMN `repay_amount` DECIMAL(8,2) NULL COMMENT '补缴金额' AFTER `repay_type`, ADD COLUMN `repay_reason` VARCHAR(200) NULL COMMENT '补缴理由' AFTER `repay_amount`; --20151020 侯庆营 DB:shebao -- 添加手机端补缴退款提示字段： ALTER TABLE `shebao`.`t_sb_buy_order` ADD COLUMN `app_remark` VARCHAR(20) NULL COMMENT '手机端补缴退款提示' AFTER `gen_time`; --20151022 赖应和 DB:shebao CREATE TABLE `t_city` ( `city_code` int(10) unsigned NOT NULL, `parent_code` int(10) unsigned NOT NULL DEFAULT '0', `city_name` varchar(64) NOT NULL, `is_city` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否城市', `disabled` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0有效、1无效', `is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`city_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --^^^^^^^^^^^^^^^^^^^^^^^^^^^ done at 2015-10-15 --20151013 赖应和 DB:shebao -- 添加登录ip字段 ALTER TABLE `t_user_flag` ADD COLUMN `login_ip` varchar(16) NULL COMMENT '登录ip' AFTER `login_time`; --20151012 侯庆营 DB:shebao-admin -- 添加字段核对结果 ALTER TABLE `t_sb_employee` ADD COLUMN `recon_result` TINYINT(4) DEFAULT 0 NULL COMMENT '核对结果'; ----------------------------------------------------------- TEST ----------------------------------- -- 增当月减当月(当月可买当月) ------ 增员：1月买1月份，1月（依据1月份订单、上一月份是否购买）生成增员在1月 ------ 代理员工：1月份有增员在，1月（依据1月份订单）生成代理员工不产生数据记录; 2月接着买2月份，2月（依据2月份订单）生成代理员工在2月 ------ 减员：3月份没买，3月（依据2月份增~代理员工名单、3月份没买）生成减员在3月 -- 增下月减下月 (当月只能买下月或下下月) ------ 增员：在1月买2月份，1月（依据2月份订单、上一月份是否购买）生成增员在1月 ------ 代理员工：1月份有增员在，1月（依据2月份订单）生成代理员工不产生数据记录; 在1或2月段内买了3月份，2月（依据3月份订单）生成代理员工在2月。 ------ 减员：4月份没买，3月（依据2月份增~代理员工名单、4月份没买）生成减员在3月 -- 增当月减下月(当月可买当月、一次需要买2个月或以上) ------ 增员：1月买了1、2月份，1月（依据1月份订单、上一月份是否购买）生成增员在1月 ------ 代理员工：1月份有增员在，1月（依据1月份订单）生成代理员工不产生数据记录; 2月（依据2月份订单）生成代理员工于2月 ------ 减员：3月份没买，2月（依据1月份增~代理员工名单、3月份没买）生成减员在2月 (生成前需要检查是否已存在减员) --------------------------------- 440682198503083561 --- 广州(1001) 社保 6月 440682198503083561 insert into t_sb_buy set buy_no='B0110010000000134201606', pay_for=1, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083561', cid=1, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=0,fund_charge=0,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-01 12:15:12'; --- 广州(1001) 公积金 8 440682198503083561 insert into t_sb_buy set buy_no='B0210010000000134201608', pay_for=2, start_month=201608,month_num=1, pay_uid=1000004,pay_channel=1,id_card='440682198503083561', cid=1,hukou=1, total_fee=0.01,shebaos=0,shebao_charge=0,funds=500,fund_charge=59,pay_no='',status=1,gen_date=20160601,gen_time='2016-08-01 12:15:12'; --- 阳江(1010) 社保 6月 440682198503083561 insert into t_sb_buy set buy_no='B0110100000000134201606', pay_for=1, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083561', cid=13, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=0,fund_charge=0,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-10 12:15:12'; --- 阳江(1010) 公积金 8 440682198503083561 insert into t_sb_buy set buy_no='B0210100000000134201608', pay_for=2, start_month=201608,month_num=1, pay_uid=1000004,pay_channel=1,id_card='440682198503083561', cid=13,hukou=1, total_fee=0.01,shebaos=0,shebao_charge=0,funds=600,fund_charge=59,pay_no='',status=1,gen_date=20160601,gen_time='2016-08-10 12:15:12'; --- 江门(1005) 社保 6月 440682198503083561 insert into t_sb_buy set buy_no='B0110050000000134201606', pay_for=1, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083561', cid=9, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=0,fund_charge=0,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-05 12:15:12'; --- 江门(1005) 公积金 8 440682198503083561 insert into t_sb_buy set buy_no='B0210050000000134201608', pay_for=2, start_month=201608,month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083561', cid=9,hukou=1, total_fee=0.01,shebaos=0,shebao_charge=0,funds=600,fund_charge=59,pay_no='',status=1,gen_date=20160601,gen_time='2016-08-05 12:15:12'; ------------------------------------------- 440682198503083561 end. --------------------------------- 440682198503083522 --- 广州(1001) 社保+公积金 6月 440682198503083522 insert into t_sb_buy set buy_no='B0310010000000135201606', pay_for=3, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083522', cid=1, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=500,fund_charge=40,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-11 12:15:12'; --- 阳江(1010) 社保+公积金 6月 440682198503083522 insert into t_sb_buy set buy_no='B0310100000000135201606', pay_for=3, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083522', cid=13, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=600,fund_charge=40,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-11 12:15:12'; --- 江门(1005) 社保+公积金 6月 440682198503083522 insert into t_sb_buy set buy_no='B0310050000000135201606', pay_for=3, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083522', cid=9, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=700,fund_charge=40,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-11 12:15:12'; ------------------------------------------- 440682198503083522 end. ----------------------------------------------------------------- --- 广州(1001) 社保+公积金 6月 440682198503083555 insert into t_sb_buy set buy_no='B0310010000000136201606', pay_for=3, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083555', cid=1, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=500,fund_charge=40,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-12 12:15:12'; --- 广州(1001) 公积金 8 440682198503083555 insert into t_sb_buy set buy_no='B0210010000000136201608', pay_for=2, start_month=201608,month_num=1, pay_uid=1000004,pay_channel=1,id_card='440682198503083555', cid=1,hukou=1, total_fee=0.01,shebaos=0,shebao_charge=0,funds=500,fund_charge=59,pay_no='',status=1,gen_date=20160601,gen_time='2016-08-12 12:15:12'; --- 阳江(1010) 社保+公积金 6月 440682198503083555 insert into t_sb_buy set buy_no='B0310100000000136201606', pay_for=3, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083555', cid=13, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=600,fund_charge=40,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-12 12:15:12'; --- 阳江(1010) 公积金 8 440682198503083555 insert into t_sb_buy set buy_no='B0210100000000136201608', pay_for=2, start_month=201608,month_num=1, pay_uid=1000004,pay_channel=1,id_card='440682198503083555', cid=13,hukou=1, total_fee=0.01,shebaos=0,shebao_charge=0,funds=600,fund_charge=59,pay_no='',status=1,gen_date=20160601,gen_time='2016-08-12 12:15:12'; --- 江门(1005) 社保+公积金 6月 440682198503083555 insert into t_sb_buy set buy_no='B0310050000000136201606', pay_for=3, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083555', cid=9, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=700,fund_charge=40,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-12 12:15:12'; --- 江门(1005) 公积金 8 440682198503083555 insert into t_sb_buy set buy_no='B0210050000000136201608', pay_for=2, start_month=201608,month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083555', cid=9,hukou=1, total_fee=0.01,shebaos=0,shebao_charge=0,funds=600,fund_charge=59,pay_no='',status=1,gen_date=20160601,gen_time='2016-08-12 12:15:12'; --------------------------------------------------------------- --- 广州(1001) 社保+公积金 6月 440682198503083566 insert into t_sb_buy set buy_no='B0310010000000137201606', pay_for=3, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083566', cid=1, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=40,funds=500,fund_charge=59,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-11 12:15:12'; --- 广州(1001) 社保 8月 440682198503083566 insert into t_sb_buy set buy_no='B0110010000000134201608', pay_for=1, start_month=201608, month_num=1, pay_uid=1000004,pay_channel=1,id_card='440682198503083566', cid=1, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=0,fund_charge=0,pay_no='',status=1,gen_date=20160601,gen_time='2016-08-06 12:15:12'; --- 阳江(1010) 社保+公积金 6月 440682198503083566 insert into t_sb_buy set buy_no='B0310100000000137201606', pay_for=3, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083566', cid=13, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=40,funds=600,fund_charge=59,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-06 12:15:12'; --- 阳江(1010) 社保 8月 440682198503083566 insert into t_sb_buy set buy_no='B0110100000000137201608', pay_for=1, start_month=201608, month_num=1, pay_uid=1000004,pay_channel=1,id_card='440682198503083566', cid=13, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=0,fund_charge=0,pay_no='',status=1,gen_date=20160601,gen_time='2016-08-06 12:15:12'; --- 江门(1005) 社保+公积金 6月 440682198503083566 insert into t_sb_buy set buy_no='B0310050000000137201606', pay_for=3, start_month=201606, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083566', cid=9, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=40,funds=700,fund_charge=59,pay_no='',status=1,gen_date=20160601,gen_time='2016-06-06 12:15:12'; --- 江门(1005) 社保 8月 440682198503083566 insert into t_sb_buy set buy_no='B0110050000000137201608', pay_for=1, start_month=201608, month_num=2, pay_uid=1000004,pay_channel=1,id_card='440682198503083566', cid=9, hukou=1, total_fee=0.01,shebaos=0,shebao_charge=59,funds=0,fund_charge=0,pay_no='',status=1,gen_date=20160601,gen_time='2016-08-06 12:15:12'; --- 2021年3月18日 已同步线上 ALTER TABLE `shebao`.`t_sb_buy_order_added` MODIFY COLUMN `reason` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '原因' AFTER `city_code`; ------------------------------------------------------------------------------- --- 2021年3月25日 用户回访表 CREATE TABLE `shebao_admin`.`pay_a_return_visit` ( `id` int(10) NOT NULL AUTO_INCREMENT, `id_card` varchar(20) NOT NULL COMMENT '身份证', `phone` varchar(20) NOT NULL COMMENT '手机号', `user_name` varchar(12) NOT NULL COMMENT '用户姓名', `city_code` smallint(6) NOT NULL COMMENT '城市', `pay_a_return_visit_time` timestamp(0) NULL COMMENT '回访时间', `pay_a_return_visit_emp` varchar(20) NULL COMMENT '回访人', `pay_a_return_visit_record` varchar(10) NULL COMMENT '回访记录', `pay_a_return_visit_situation` varchar(55) NULL COMMENT '回访情况', `gen_time` timestamp(0) NULL COMMENT '创建时间', PRIMARY KEY (`id`) ) COMMENT = '用户回访表'; --- 2021年3月30日 t_sb_user增加开户城市cid字段 ALTER TABLE `shebao`.`t_sb_user` ADD COLUMN `bank_cid` int(10) NULL COMMENT '开户城市cid' AFTER `bank_code`; ---------------------------------------------------------------------- ALTER TABLE `shebao_admin`.`t_report_month` ADD COLUMN `shebao_repay_amount` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '社保已补差额' AFTER `total_need_repay_amount`, ADD COLUMN `fund_repay_amount` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '公积金已补差额' AFTER `shebao_repay_amount`, ADD COLUMN `shebao_need_repay_amount` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '社保需补差额' AFTER `fund_repay_amount`, ADD COLUMN `fund_need_repay_amount` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '公积金需补差额' AFTER `shebao_need_repay_amount`; ALTER TABLE `shebao_admin`.`t_report_month` ADD COLUMN `shebao_partial_refund` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '社保部分退款' AFTER `shebao_need_repay_amount`, MODIFY COLUMN `fund_repay_amount` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '公积金已补差额' AFTER `shebao_partial_refund`, ADD COLUMN `fund_partial_refund` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '公积金部分退款' AFTER `fund_need_repay_amount`; ALTER TABLE `shebao_admin`.`t_report_month` ADD COLUMN `shebao_disabled_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '社保残保金总额' AFTER `total_need_repay_amount`; CREATE TABLE `shebao`.`t_sb_info_hangzhou` ( `id` int(10) NOT NULL AUTO_INCREMENT, `u_id` int(10) NOT NULL COMMENT '系统用户标识', `id_card` varchar(20) NOT NULL COMMENT '身份证号', `user_name` varchar(20) NOT NULL COMMENT '用户姓名', `political_status` varchar(8) NULL COMMENT '政治面貌', `education_level` varchar(16) NULL COMMENT '受教育程度', `other_phone` varchar(16) NULL COMMENT '其他联系方式', `marriage_status` varchar(8) NULL COMMENT '婚姻情况', `hk_address` varchar(128) NULL COMMENT '户籍地址', `address` varchar(128) NULL COMMENT '现居住地址', `graduate_time` varchar(16) NULL COMMENT '毕业时间', `graduate_school` varchar(32) NULL COMMENT '毕业学校', `major` varchar(32) NULL COMMENT '专业', `work_time` varchar(16) NULL COMMENT '第一次工作时间', `profession` varchar(32) NULL COMMENT '职业', PRIMARY KEY (`id`) ); ALTER TABLE `shebao_admin`.`t_report_month` ADD COLUMN `shebao_pre_charge` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '社保预收费总额' AFTER `shebao_disabled_price`; ---------------------------------------------------------------------------------------------- ALTER TABLE `shebao_admin`.`t_report_month` ADD COLUMN `shebao_charge_refund` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '社保服务费退费' AFTER `shebao_partial_refund`, ADD COLUMN `shebao_card_charge` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '社保制卡费退费' AFTER `shebao_charge_refund`, ADD COLUMN `fund_charge_refund` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '公积金服务费退费' AFTER `fund_partial_refund`; ---------------------------------------------------------------------------------------------- CREATE TABLE `shebao`.`t_sb_buy_order_wage_opt` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `order_no` varchar(32) NOT NULL DEFAULT '', `opt_type` varchar(16) NOT NULL DEFAULT '' COMMENT '操作类型', `amount` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '涉及金额', `opt_user` varchar(32) NOT NULL DEFAULT '' COMMENT '操作人', `gen_time` datetime NOT NULL COMMENT '生成时间', `remark` varchar(255) NOT NULL DEFAULT '' COMMENT '说明', PRIMARY KEY (`id`), KEY `buy_order_opt_no_idx` (`order_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单流水退款操作列表'; ALTER TABLE `shebao`.`t_sb_buy_order_wage_opt` ADD COLUMN `opt_api` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '退款方式' AFTER `remark`, ADD COLUMN `charge_k6` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '千分之六的手续费(分)' AFTER `opt_api`, ADD COLUMN `opt_status` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '退款状态，同订单' AFTER `charge_k6`, ADD COLUMN `opt_time` datetime(0) NULL DEFAULT NULL COMMENT '退款操作时间' AFTER `opt_status`, ADD COLUMN `opt_remark` varchar(255) NOT NULL DEFAULT '' COMMENT '退款操作备注' AFTER `opt_time`, ADD COLUMN `batchNo` varchar(20) NOT NULL DEFAULT '' COMMENT '批次号' AFTER `opt_remark`, ADD COLUMN `refundNo` varchar(32) NOT NULL DEFAULT '' COMMENT '支付平台的本次退款标识' AFTER `batchNo`; --------------------------------------------------------------------------------------------------------- ALTER TABLE `shebao`.`t_sb_buy_order_wage_opt` ADD COLUMN `trade_no` varchar(32) NOT NULL DEFAULT 0 COMMENT '支付单号' AFTER `order_no`; ALTER TABLE `shebao`.`t_sb_buy_order_wage_opt` ADD COLUMN `return_type` tinyint(4) NULL COMMENT '流水退款类型 11:全额退款 12:只退回流水金额' AFTER `opt_status`; ---------------------------------------------------------------------------------------------------------- CREATE TABLE `shebao`.`return_visit_sms` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `send_status` tinyint(3) NULL DEFAULT 0 COMMENT '发送状态', `send_uid` int(0) NULL COMMENT '短信目标用户id', `phone` varchar(20) NULL COMMENT '手机号', `t_msg_type_id` int(20) NULL COMMENT 't_msg_type_id', `create_time` timestamp(0) NULL COMMENT '创建时间', `update_time` timestamp(0) NULL COMMENT '更新时间', PRIMARY KEY (`id`) ) COMMENT = '增员回访短信状态表'; ---------------------------------------------------------------------------------------------------------- ALTER TABLE `shebao`.`t_sb_wage_flow_setting` ADD COLUMN `flow_force` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否强制缴纳流水0否，1是' AFTER `flow_flag`; ALTER TABLE `shebao`.`t_sb_buy` ADD COLUMN `flow_force` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否强制缴纳流水0:否,1:是' AFTER `working`, ADD COLUMN `flow_charge` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '社保流水服务费' AFTER `flow_force`; ALTER TABLE `shebao`.`t_sb_buy_order_wage` DROP INDEX `order_wage_tradeno_idx`, ADD INDEX `order_wage_tradeno_idx`(`trade_no`) USING BTREE; ---------------------------------------------------------------------------------------------------------- CREATE TABLE `shebao_oa`.`roster` ( `roster_id` bigint(20) NOT NULL AUTO_INCREMENT, `serial_number` int(10) NULL COMMENT '序号', `cooperatively` varchar(128) NULL COMMENT '合作地', `employing_unit` varchar(128) NOT NULL COMMENT '用人单位', `contract_subject` varchar(128) NULL COMMENT '合同主体', `contract_no` varchar(64) NULL COMMENT '合同编号', `insured_region` varchar(64) NULL COMMENT '参保地', `insured_name` varchar(32) NOT NULL COMMENT '参保人姓名', `insured_idcard` varchar(20) NOT NULL COMMENT '参保人身份证', `insured_gender` tinyint(3) NULL COMMENT '性别', `insured_age` int(10) NULL COMMENT '年龄', `insured_ethnicity` varchar(20) NULL COMMENT '民族', `insured_education` varchar(20) NULL COMMENT '学历', `insured_phone` varchar(20) NULL COMMENT '联系电话', `insured_hukou` varchar(128) NULL COMMENT '户籍地址', `insured_hukou_type` tinyint(3) NULL COMMENT '户籍性质', `insured_jobs` varchar(64) NULL COMMENT ' 工作岗位', `insured_idcard_expiration_time` timestamp(0) NULL COMMENT '身份证过期时间', `employee_type` tinyint(3) NULL COMMENT '员工类型', `contract_start_time` timestamp(0) NULL COMMENT '合同开始时间', `expiration_of_contract` timestamp(0) NULL COMMENT '合同过期时间', `probation_period` varchar(10) NULL COMMENT '试用期', `departure_time` timestamp(0) NULL COMMENT '离职时间', `status` tinyint(3) NULL DEFAULT NULL COMMENT '状态0初始状态；1:在保，2不在保', `labor_contract` tinyint(3) NULL DEFAULT NULL COMMENT '劳动合同1:有；2:无', `entry_requirements` tinyint(3) NULL COMMENT '入职需知1:有；2:无', `employee_info` tinyint(3) NULL COMMENT '员工信息表1:有；2无', `copy_of_idcard` tinyint(3) NULL COMMENT '身份证复印件1:有2:无', `certificate_of_dissolution` tinyint(3) NULL COMMENT '劳动关系解除证明书1:有，2无', `bank_card_number` varchar(64) NULL COMMENT '银行卡号', `bank` varchar(64) NULL COMMENT '开户行', `kefu_user` varchar(32) NULL COMMENT '操作客服', `create_time` timestamp(0) NULL COMMENT '创建时间', `update_time` timestamp(0) NULL COMMENT '更新时间', PRIMARY KEY (`roster_id`), UNIQUE INDEX `unit_name_idcard_index`(`employing_unit`, `insured_name`, `insured_idcard`) COMMENT '用工单位，身份证号码，姓名组成的唯一索引' ) COMMENT = '员工花名册'; ALTER TABLE `shebao_oa`.`roster` ADD COLUMN `type` int(10) NULL COMMENT '合同类型 ContractTypeEnum' AFTER `roster_id`; ALTER TABLE `shebao_oa`.`roster` MODIFY COLUMN `kefu_user` int(10) NULL DEFAULT NULL COMMENT '操作客服' AFTER `bank`; ALTER TABLE `shebao_oa`.`roster` DROP INDEX `unit_name_idcard_index`, ADD UNIQUE INDEX `unit_name_idcard_index`(`employing_unit`, `insured_name`, `insured_idcard`, `type`) USING BTREE COMMENT '用工单位，姓名，身份证号码，,合同类型组成的唯一索引'; ALTER TABLE `shebao_oa`.`roster` DROP COLUMN `serial_number`; ------------------------------------------------------------------------------------------------------------------------------------------------------ ALTER TABLE `shebao_oa`.`roster` MODIFY COLUMN `insured_hukou_type` varchar(32) NULL DEFAULT NULL COMMENT '户籍性质' AFTER `insured_hukou`; ------------------------------------------------------------------------------------------------------------------------------------------------------ CREATE TABLE `shebao_oa`.`talent_pool` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `company_name` varchar(128) NOT NULL COMMENT '需求企业名称', `job` varchar(32) NULL COMMENT '岗位', `job_type` varchar(32) NULL COMMENT '岗位类型', `job_hunter` varchar(32) NOT NULL COMMENT '求职者', `qualification` varchar(32) NOT NULL COMMENT '学历', `phone` varchar(32) NOT NULL COMMENT '电话', `gender` varchar(10) NULL COMMENT '性别', `age` int(10) NULL COMMENT '年龄', `intention_job` varchar(32) NULL COMMENT '意向岗位', `job_search_area` varchar(32) NULL COMMENT '求职区域', `status` varchar(32) NULL COMMENT '求职状态', `kefu_user` int(10) NULL COMMENT '操作客服', `create_time` timestamp(0) NULL COMMENT '创建时间', `update_time` timestamp(0) NULL COMMENT '修改时间', PRIMARY KEY (`id`) ); ALTER TABLE `shebao_oa`.`talent_pool` ADD UNIQUE INDEX `job_hunter_phone_index`(`job_hunter`, `phone`) USING BTREE COMMENT '求职者 电话 唯一索引'; ------------------------------------------------- ALTER TABLE `shebao_oa`.`roster` ADD COLUMN `remark` varchar(255) NULL COMMENT '备注' AFTER `bank`; ----------------------------------------------------------------------------------------------------------------------- CREATE TABLE `t_sb_buy_channel_notify` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `buy_no` varchar(32) NOT NULL COMMENT '买单号', `notify_url` varchar(512) DEFAULT NULL COMMENT '渠道第三方回调地址', `channel_out_trade_no` varchar(255) DEFAULT NULL COMMENT '渠道外部订单号', `status` tinyint(3) DEFAULT NULL COMMENT '状态0未调用，1成功，2失败', `create_time` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `shebao`.`t_sb_buy_channel_notify` ADD UNIQUE INDEX `buy_no_index`(`buy_no`) COMMENT 'buy_no唯一索引'; ----------------------------------------------------------------------------------------------------------------------- ALTER TABLE `shebao`.`t_sb_city` ADD COLUMN `max_age` int(5) NOT NULL DEFAULT 48 COMMENT '可参保的最大年龄' AFTER `sb_alone`, ADD COLUMN `min_age` int(5) NOT NULL DEFAULT 18 COMMENT '可参保的最小年龄' AFTER `max_age`; ALTER TABLE `shebao`.`t_sb_city` CHANGE COLUMN `max_age` `girl_max_age` int(5) NOT NULL DEFAULT 48 COMMENT '女性可参保的最大年龄' AFTER `sb_alone`, CHANGE COLUMN `min_age` `girl_min_age` int(5) NOT NULL DEFAULT 18 COMMENT '女性可参保的最小年龄' AFTER `girl_max_age`, ADD COLUMN `man_max_age` int(5) NOT NULL DEFAULT 58 COMMENT '男性可参保的最大年龄' AFTER `girl_min_age`, ADD COLUMN `man_min_age` int(5) NOT NULL DEFAULT 18 COMMENT '男性可参保的最大年龄' AFTER `man_max_age`;