shebao-server/docs/sql/change.sql

---- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓此位置加入最新修改的脚本↓↓↓↓↓↓↓↓↓↓↓↓↓↓


-- 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`;