shebao-server/docs/sql/shebao-oa/shebao_oa.sql

-----------------以下已上线------------------------>

-- 社保黑名单表
CREATE TABLE `biz_emp_blacklist` (
  `blacklist_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '黑名单编号',
  `name` VARCHAR(64)  NOT NULL COMMENT  '姓名',
  `idcard` VARCHAR(18) NOT NULL COMMENT '身份证',
  `memo` VARCHAR(128) NOT NULL COMMENT '备注信息',
	PRIMARY KEY (`blacklist_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '社保黑名单表';

-- 2018-07-17 chensenlai  DB:shebao_oa
-- 后台人员添加邮箱通知
ALTER TABLE `sys_user` ADD COLUMN `email` VARCHAR(64) DEFAULT NULL COMMENT '电子邮箱' AFTER `real_name`;

INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) 
VALUES ('22', 'MENU_ACCOUNT_MANAGE', '帐务管理', '1', 'javascript:void(0);', '-1', '26');

INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) 
VALUES ('24', 'MENU_ACCOUNT_INVOICE', '开票管理', '1', '/sbo/account/invoice/list', '22', '28');

INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) 
VALUES ('25', 'MENU_ACCOUNT_PAYMENT', '打款管理', '1', '/sbo/account/payment/list', '22', '29');

INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) 
VALUES ('26', 'MENU_ACCOUNT_DEALINGS_KEFU', '客服明细', '1', '/sbo/account/dealings/kefu/list', '22', '27');

INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) 
VALUES ('27', 'MENU_ACCOUNT_DEALINGS_SHEBAO', '专员明细', '1', '/sbo/account/dealings/shebao/list', '22', '27');

INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) 
VALUES ('28', 'MENU_ACCOUNT_DEALINGS_DIFF', '核对明细', '1', '/sbo/account/dealings/diff/list', '22', '27');

INSERT INTO `shebao_oa`.`sys_role` (`rid`, `code`, `name`) VALUES ('4', 'FINANCE', '财务人员');

-- 参数配置
CREATE TABLE `cfg_param` (
  `param_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '参数编号',
  `gcode` VARCHAR(64)  NOT NULL COMMENT  '参数一级编码',
  `mcode` VARCHAR(64)  NOT NULL COMMENT  '参数二级编码',
  `mname` VARCHAR(64)  NOT NULL COMMENT  '参数名称',
  `data` VARCHAR(1024) DEFAULT NULL COMMENT '数据',
  `sort` INT(11) DEFAULT 1 COMMENT '排序',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	PRIMARY KEY (`param_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '参数配置';

INSERT INTO `cfg_param` (`gcode`, `mcode`, `mname`, `data`) VALUES ('AUDIT', 'INVOICE', '开票审核流程', '');
INSERT INTO `cfg_param` (`gcode`, `mcode`, `mname`, `data`) VALUES ('AUDIT', 'PAYMENT', '打款审核流程', '');

-- 客服帐务往来记录表
CREATE TABLE `account_dealings_kefu` (
  `dealings_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '客服往来交易编号',
  `op_user_id` INT(11) UNSIGNED NOT NULL COMMENT '操作员编号',
  `city_id` INT(11) UNSIGNED NOT NULL COMMENT '城市编号',
  `com_name` VARCHAR(64)  NOT NULL COMMENT  '公司名称',
  `op_month` VARCHAR(8) NOT NULL COMMENT '操作月份',
  `dealings_type` TINYINT(4) UNSIGNED NOT NULL COMMENT '交易类型',
  `memo` VARCHAR(1024) DEFAULT NULL COMMENT '备注信息',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `invoice_status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '开票状态',
  `invoice_no` VARCHAR(32) DEFAULT NULL COMMENT '开票流水号',
  `payment_status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '收款状态',
	PRIMARY KEY (`dealings_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 COMMENT '客服帐务往来记录表';

-- 客服社保公积金往来记录表
CREATE TABLE `account_dealings_kefu_shebao_fund` (
  `dealings_id` INT(11) UNSIGNED NOT NULL COMMENT '客服往来交易编号',
  `kefu_user_id` INT(11) UNSIGNED DEFAULT NULL COMMENT '客服编号',
  `shebao_user_id` INT(11) UNSIGNED DEFAULT NULL COMMENT '社保专员编号',
  `shebao_month` VARCHAR(8) DEFAULT NULL COMMENT '社保月份',
  `fund_month` VARCHAR(8) DEFAULT NULL COMMENT '公积金月份',
  `employ_num` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '员工数量',
  `shebao_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '社保费',
  `fund_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '公积金费',
  `residual_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '残保金',
  `detention_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '违约金',
  `service_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '服务费',
	PRIMARY KEY (`dealings_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '客服社保公积金';

-- 客服商保往来记录表
CREATE TABLE `account_dealings_kefu_insurance` (
  `dealings_id` INT(11) UNSIGNED NOT NULL COMMENT '客服往来交易编号',
  `insurance_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '商保费',
	PRIMARY KEY (`dealings_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '客服商保费';

-- 客服工资往来记录表
CREATE TABLE `account_dealings_kefu_salary` (
  `dealings_id` INT(11) UNSIGNED NOT NULL COMMENT '客服往来交易编号',
  `salary_month` VARCHAR(8) NOT NULL COMMENT '工资月份',
  `salary_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '实发工资',
  `salary_tax_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '个税',
  `tax_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '税费',
  `other_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '其他费',
  `service_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '服务费',
	PRIMARY KEY (`dealings_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '客服工资';


-- 社保专员帐务往来记录表
CREATE TABLE `account_dealings_shebao` (
  `dealings_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '社保专员往来交易编号',
  `op_user_id` INT(11) UNSIGNED NOT NULL COMMENT '操作员编号',
  `city_id` INT(11) UNSIGNED NOT NULL COMMENT '城市编号',
  `com_name` VARCHAR(64)  NOT NULL COMMENT  '公司名称',
  `op_month` VARCHAR(8) NOT NULL COMMENT '操作月份',
  `dealings_type` TINYINT(4) UNSIGNED NOT NULL COMMENT '交易类型',
  `memo` VARCHAR(1024) DEFAULT NULL COMMENT '备注信息',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `payment_status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '打款状态',
  `payment_no` VARCHAR(1024) DEFAULT NULL COMMENT '打款流水号',
  `invoice_status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '收票状态',
	PRIMARY KEY (`dealings_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 COMMENT '社保专员帐务往来记录表';

-- 社保专员社保公积金往来记录表
CREATE TABLE `account_dealings_shebao_shebao_fund` (
  `dealings_id` INT(11) UNSIGNED NOT NULL COMMENT '社保专员往来交易编号',
  `kefu_user_id` INT(11) UNSIGNED DEFAULT NULL COMMENT '客服编号',
  `shebao_user_id` INT(11) UNSIGNED DEFAULT NULL COMMENT '社保专员编号',
  `shebao_month` VARCHAR(8) DEFAULT NULL COMMENT '社保月份',
  `fund_month` VARCHAR(8) DEFAULT NULL COMMENT '公积金月份',
  `supplier_name` VARCHAR(64) DEFAULT NULL COMMENT '供应商',
  `employ_num` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '员工数量',
  `shebao_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '社保费',
  `fund_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '公积金费',
  `residual_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '残保金',
  `detention_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '违约金',
  `service_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '服务费',
	PRIMARY KEY (`dealings_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '社保专员社保公积金';

-- 帐务核对记录表
CREATE TABLE `account_dealings_diff` (
  `diff_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '核对编号',
  `city_id` INT(11) UNSIGNED NOT NULL COMMENT '城市编号',
  `com_name` VARCHAR(64)  NOT NULL COMMENT  '公司名称',
  `month` VARCHAR(8) NOT NULL COMMENT '月份',
  `kefu_user_id` INT(11) UNSIGNED NOT NULL COMMENT '客服编号',
  `shebao_user_id` INT(11) UNSIGNED NOT NULL COMMENT '社保专员编号',
  `kefu_shebao_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '客服社保费用',
  `kefu_fund_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '客服公积金费用',
  `kefu_residual_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '客服残保金',
  `shebao_shebao_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '专员社保费用',
  `shebao_fund_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '专员公积金费用',
  `shebao_residual_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '专员残保金',
  `diff_status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '核对状态',
  `force_diff_status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '强制通过',
  `memo` VARCHAR(1024) DEFAULT NULL COMMENT '备注信息',
   `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	PRIMARY KEY (`diff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '帐务核对记录表';


-- 客服开票表
CREATE TABLE `account_invoice` (
  `invoice_no` VARCHAR(32) NOT NULL COMMENT '开票流水号',
  `month` VARCHAR(8) NOT NULL COMMENT '月份',
  `shebao_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '社保费用',
  `fund_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '公积金费用',
  `residual_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '残保金',
  `detention_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '滞纳金',
  `service_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '服务费',
  `insurance_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '商保费',
  `salary_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '实发工资',
  `salary_tax_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '个税',
  `tax_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '税费',
  `other_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '其他费用',
  `salary_service_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '工资服务费',
  `kefu_user_id` INT(11) UNSIGNED NOT NULL COMMENT '客服编号',
  `invoice_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '开票状态',
  `payment_status` TINYINT(4) UNSIGNED DEFAULT NULL COMMENT '打款状态',
  `memo` VARCHAR(1024) DEFAULT NULL COMMENT '备注信息',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	PRIMARY KEY (`invoice_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '客服开票表';

-- 客服开票项表
CREATE TABLE `account_invoice_item` (
  `invoice_item_no` VARCHAR(32) NOT NULL COMMENT '客服开票项号',
  `invoice_no` VARCHAR(32)  NOT NULL COMMENT  '开票编号',
  `com_name` VARCHAR(64)  NOT NULL COMMENT  '公司名称',
  `tax_type` TINYINT(4) UNSIGNED NOT NULL COMMENT '纳税人类型',
  `tax_no` VARCHAR(64) DEFAULT NULL COMMENT '纳税编号',
  `bank_name` VARCHAR(64) NOT NULL COMMENT '银行名称',
  `bank_no` VARCHAR(64) NOT NULL COMMENT '银行卡号',
  `purpose` VARCHAR(1024) NOT NULL COMMENT '用途',
  `inv_no` VARCHAR(64) DEFAULT NULL COMMENT '开票号',
  `inv_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '开票金额',
  `memo` VARCHAR(1024) DEFAULT NULL COMMENT '开票备注',
  `finance_user_id` INT(11) UNSIGNED DEFAULT NULL COMMENT '财务编号',
  `invoice_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '开票状态',
  `audit_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '审核状态',
  `audit_user_ids` VARCHAR(11) DEFAULT NULL COMMENT '审核人员',
  `audit_user_id` INT(11) UNSIGNED DEFAULT NULL COMMENT '当前审核人员',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	PRIMARY KEY (`invoice_item_no`),
	KEY(`invoice_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '客服开票项表';


-- 客服开票收款表
CREATE TABLE `account_invoice_payment` (
  `invoice_payment_no` VARCHAR(32) NOT NULL COMMENT '客服开票收款号',
  `invoice_no` VARCHAR(32)  NOT NULL COMMENT  '开票编号',
  `payment_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT  '收款金额',
  `finance_user_id` INT(11) UNSIGNED DEFAULT NULL COMMENT '财务编号',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	PRIMARY KEY (`invoice_payment_no`),
	KEY(`invoice_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '客服开票收款表';


-- 客服开票客户表
CREATE TABLE `account_invoice_cust` (
  `cust_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '客户编号',
  `com_name` VARCHAR(64)  NOT NULL COMMENT  '公司名称',
  `tax_type` TINYINT(4) UNSIGNED NOT NULL COMMENT '纳税人类型',
  `tax_no` VARCHAR(64) DEFAULT NULL COMMENT '纳税编号',
  `bank_name` VARCHAR(64) NOT NULL COMMENT '银行名称',
  `bank_no` VARCHAR(64) NOT NULL COMMENT '银行卡号',
	PRIMARY KEY (`cust_id`),
	UNIQUE(`com_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '客服开票客户表';

-- 帐务打款表
CREATE TABLE `account_payment` (
  `payment_no` VARCHAR(32) NOT NULL COMMENT '打款流水号',
  `shebao_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '社保费',
  `fund_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '公积金费',
  `residual_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '残保金',
  `detention_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '滞纳金',
  `service_fee` INT(11) NOT NULL DEFAULT 0 COMMENT '服务费',
  `shebao_user_id` INT(11) UNSIGNED NOT NULL COMMENT '社保专员编号',
  `payment_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '打款状态',
  `invoice_status` TINYINT(4) UNSIGNED DEFAULT NULL COMMENT '收票状态',
  `memo` VARCHAR(1024) DEFAULT NULL COMMENT '备注信息',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	PRIMARY KEY (`payment_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '打款表';


-- 打款项表
CREATE TABLE `account_payment_item` (
  `payment_item_no` VARCHAR(32) NOT NULL COMMENT '打款项编号',
  `payment_no` VARCHAR(32)  NOT NULL COMMENT  '打款流水号',
  `account_name` VARCHAR(64) DEFAULT NULL COMMENT '账户名',
  `account` VARCHAR(64) DEFAULT NULL COMMENT '账户',
  `purpose` VARCHAR(64) DEFAULT NULL COMMENT '打款用途',
  `apply_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '申请金额',
  `payment_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '打款金额',
  `memo` VARCHAR(1024) DEFAULT NULL COMMENT '备注信息',
  `finance_user_id` INT(11) UNSIGNED DEFAULT NULL COMMENT '财务编号',
  `payment_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '打款状态',
  `audit_status` TINYINT(4) UNSIGNED NOT NULL COMMENT '审核状态',
  `audit_user_ids` VARCHAR(11) DEFAULT NULL COMMENT '审核人员',
  `audit_user_id` INT(11) UNSIGNED DEFAULT NULL COMMENT '当前审核人员',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	KEY(`payment_no`),
	PRIMARY KEY (`payment_item_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '打款明细表';


-- 打款收票表
CREATE TABLE `account_payment_invoice` (
  `payment_invoice_no` VARCHAR(32) NOT NULL COMMENT '打款收票编号',
  `payment_no` VARCHAR(32)  NOT NULL COMMENT  '打款流水号',
  `inv_no` VARCHAR(64) DEFAULT NULL COMMENT '发票号',
  `finance_user_id` INT(11) UNSIGNED DEFAULT NULL COMMENT '财务编号',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	KEY(`payment_no`),
	PRIMARY KEY (`payment_invoice_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '打款收票表';

-- 2018-06-21 chensenlai  DB:shebao_oa
-- 城市配置表增加城市待遇配置
ALTER TABLE `sys_city` ADD COLUMN `treatment_path` VARCHAR(64) DEFAULT NULL COMMENT '城市待遇' AFTER `proxy_memo`;


-- 2018-06-19 chensenlai  DB:shebao_oa
-- 供应商信息表
CREATE TABLE `sys_supplier` (
  `sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '供应商编号',
  `supplier_name` VARCHAR(64) NOT NULL COMMENT '供应商名称',
  `company_name` VARCHAR(64)  DEFAULT NULL COMMENT  '公司名称',
  `cid` INT(11) UNSIGNED NOT NULL COMMENT '城市编号',
  `area` VARCHAR(16) DEFAULT NULL COMMENT '所属区域',
  `proxy_price` INT(11) DEFAULT NULL COMMENT '代理价格',
  `proxy_sort`	TINYINT(4) DEFAULT 1 COMMENT '代理选择顺序',
  `op_sb` INT(11) NOT NULL COMMENT '社保专员',
  `shebao_deadline_op` VARCHAR(16) DEFAULT NULL COMMENT '社保专员截止时间',
  `shebao_deadline_sys` VARCHAR(16) DEFAULT NULL COMMENT '社保系统截止时间',
  `shebao_rule` VARCHAR(64) DEFAULT NULL COMMENT '社保操作规则',
  `shebao_material_first` VARCHAR(2048) DEFAULT NULL COMMENT '社保首参资料',
  `shebao_material_notfirst` VARCHAR(2048) DEFAULT NULL COMMENT '社保非首参资料',
  `fund_deadline_op` VARCHAR(16) DEFAULT NULL COMMENT '公积金专员截止时间',
  `fund_deadline_sys` VARCHAR(16) DEFAULT NULL COMMENT '公积金系统截止时间',
  `fund_rule` VARCHAR(64) DEFAULT NULL COMMENT '公积金操作规则',
  `fund_material_first` VARCHAR(2048) DEFAULT NULL COMMENT '公积金首参资料',
  `fund_material_notfirst` VARCHAR(2048) DEFAULT NULL COMMENT '公积金非首参资料',
  `fund_buy_only` VARCHAR(64) DEFAULT NULL COMMENT '公积金单独购买',
  `fund_rate` VARCHAR(64) DEFAULT NULL COMMENT '公积金比例',
	PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '供应商资料表(主要提供客服查看，表字段没有标准化)';



ALTER TABLE `biz_data_desc` ADD COLUMN `ext1` varchar(128) DEFAULT NULL COMMENT '补充信息1' AFTER `fund_month`;
ALTER TABLE `biz_data_desc` ADD COLUMN `ext2` varchar(128) DEFAULT NULL COMMENT '补充信息2' AFTER `ext1`;
ALTER TABLE `biz_data_desc` ADD COLUMN `ext3` varchar(128) DEFAULT NULL COMMENT '补充信息3' AFTER `ext2`;
ALTER TABLE `biz_data_desc` ADD COLUMN `ext4` varchar(128) DEFAULT NULL COMMENT '补充信息4' AFTER `ext3`;
ALTER TABLE `biz_data_desc` ADD COLUMN `ext5` varchar(128) DEFAULT NULL COMMENT '补充信息5' AFTER `ext4`;

ALTER TABLE `biz_emp_desc` ADD COLUMN `ext1` varchar(128) DEFAULT NULL COMMENT '补充信息1' AFTER `fund_month`;
ALTER TABLE `biz_emp_desc` ADD COLUMN `ext2` varchar(128) DEFAULT NULL COMMENT '补充信息2' AFTER `ext1`;
ALTER TABLE `biz_emp_desc` ADD COLUMN `ext3` varchar(128) DEFAULT NULL COMMENT '补充信息3' AFTER `ext2`;
ALTER TABLE `biz_emp_desc` ADD COLUMN `ext4` varchar(128) DEFAULT NULL COMMENT '补充信息4' AFTER `ext3`;
ALTER TABLE `biz_emp_desc` ADD COLUMN `ext5` varchar(128) DEFAULT NULL COMMENT '补充信息5' AFTER `ext4`;


ALTER TABLE `sys_city` ADD COLUMN `proxy_memo` varchar(256) DEFAULT NULL COMMENT '代理备注' AFTER `p_cid`;
ALTER TABLE `sys_city` ADD COLUMN `proxy_name` varchar(64) DEFAULT NULL COMMENT '代理名称' AFTER `p_cid`;
ALTER TABLE `sys_city` ADD COLUMN `proxy_type` INT (4) DEFAULT NULL COMMENT '代理类型 1-直营 2-代理' AFTER `p_cid`;


alter table cfg_shebao_param modify column hukou_type varchar(12) NOT NULL COMMENT '户口类型';
update cfg_shebao_param set hukou_type='1,2,3,4' where hukou_type <= 0;

ALTER TABLE `sys_city` ADD COLUMN `sb_deadline` INT(4) DEFAULT 0 COMMENT '社保截止时间' AFTER `p_cid`;


INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) VALUES ('20', 'MENU_SYS_CITY', '城市管理', '1', '/sbo/city/prov/list', '18', '32');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '20');


INSERT INTO `shebao_oa`.`sys_role` (`rid`, `code`, `name`) VALUES ('3', 'ADMIN', '管理员');

INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '5');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '6');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '7');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '8');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '9');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '10');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '11');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '12');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '13');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '14');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '15');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '16');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '18');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('3', '19');

INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) VALUES ('18', 'MENU_SYS_MANAGE', '后台管理', '1', 'javascript:void(0);', '-1', '30');
INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) VALUES ('19', 'MENU_SYS_USER', '用户管理', '1', '/sbo/user/list', '18', '31');



ALTER TABLE `sys_user` ADD COLUMN `disabled` INT(4) DEFAULT 1 COMMENT '1-启用，2-禁用' AFTER `password`;


ALTER TABLE `cfg_shebao_param` ADD COLUMN `shebao_inc_date` INT(4) DEFAULT NULL COMMENT '社保增员截止日期' AFTER `shebao_inc_rule`;
ALTER TABLE `cfg_shebao_param` ADD COLUMN `shebao_desc_date` INT(4) DEFAULT NULL COMMENT '社保减员截止日期' AFTER `shebao_desc_rule`;
ALTER TABLE `cfg_shebao_param` ADD COLUMN `fund_inc_date` INT(4) DEFAULT NULL COMMENT '公积金增员截止日期' AFTER `fund_inc_rule`;
ALTER TABLE `cfg_shebao_param` ADD COLUMN `fund_desc_date` INT(4) DEFAULT NULL COMMENT '公积金减员截止日期' AFTER `fund_desc_rule`;

alter table `cfg_shebao_param` add column `fund_scale` int(4) DEFAULT 2 COMMENT '公积金金额精算精度(默认保留小数点后两位)' AFTER `scale` ;

alter table `cfg_shebao_param` add column `shebao_card_material` VARCHAR(128) DEFAULT NULL COMMENT '社保卡提交材料' AFTER `shebao_material` ; 
 

-----------------以下已上线------------------------>

alter table `cfg_shebao_param` add column `scale` int(4) DEFAULT 2 COMMENT '金额精算精度(默认保留小数点后两位)' AFTER `fund_memo` ; 

alter table `cfg_shebao_param` add column `shebao_inc_rule` int(4) DEFAULT 1 COMMENT '社保增员 1-当月 2-下月' AFTER `shebao_execute_time` ; 
alter table `cfg_shebao_param` add column `shebao_desc_rule` int(4) DEFAULT 1 COMMENT '社保减员 1-当月 2-下月' AFTER `shebao_inc_rule` ; 
alter table `cfg_shebao_param` add column `shebao_material` VARCHAR(128) DEFAULT NULL COMMENT '社保提交材料' AFTER `shebao_desc_rule` ; 


alter table `cfg_shebao_param` add column `fund_inc_rule` int(4) DEFAULT 1 COMMENT '公积金增员 1-当月 2-下月' AFTER `fund_execute_time` ; 
alter table `cfg_shebao_param` add column `fund_desc_rule` int(4) DEFAULT 1 COMMENT '公积金减员 1-当月 2-下月' AFTER `fund_inc_rule` ; 
alter table `cfg_shebao_param` add column `fund_material` VARCHAR(128) DEFAULT NULL COMMENT '公积金提交材料' AFTER `fund_desc_rule` ; 



CREATE TABLE `cfg_shebao_param` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `city_id` int(11) NOT NULL COMMENT '城市编号',
  `proj_name` varchar(32) NOT NULL COMMENT '社保方案',
  `hukou_type` int(4) NOT NULL COMMENT '户口类型',
  `pension` varchar(128) DEFAULT NULL COMMENT '养老保险',
  `injury` varchar(128) DEFAULT NULL COMMENT '工伤保险',
  `lose_job` varchar(128) DEFAULT NULL COMMENT '失业保险',
  `birth` varchar(128) DEFAULT NULL COMMENT '生育保险',
  `medical` varchar(128) DEFAULT NULL COMMENT '基本医疗保险',
  `disabled` varchar(128) DEFAULT NULL COMMENT '残保金-定额',
  `hospital` varchar(128) DEFAULT NULL COMMENT '住院医疗',
  `major` varchar(128) DEFAULT NULL COMMENT '重大疾病',
  `large_major` varchar(128) DEFAULT NULL COMMENT '大病(额)医疗',
  `village` varchar(128) DEFAULT NULL COMMENT '农民工医疗',
  `education` varchar(128) DEFAULT NULL COMMENT '教育统筹经费',
  `warm` varchar(128) DEFAULT NULL COMMENT '采暖费',
  `archives` varchar(128) DEFAULT NULL COMMENT '档案费',
  `added` varchar(128) DEFAULT NULL COMMENT '补充医疗',
  `shebao_execute_time` varchar(16) DEFAULT NULL COMMENT '执行时间',
  `shebao_memo` varchar(128) DEFAULT NULL COMMENT '备注信息',
  `fund` varchar(128) DEFAULT NULL COMMENT '公积金',
  `fund_execute_time` varchar(16) DEFAULT NULL COMMENT '公积金执行时间',
  `fund_memo` varchar(128) DEFAULT NULL COMMENT '公积金备注信息',
  `update_by` int(11) NOT NULL COMMENT '更新者',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` int(11) NOT NULL COMMENT '创建者',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_city_id` (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='社保数据配置表';

INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) VALUES ('15', 'MENU_CFG_SHEBAO', '参数配置', '1', 'javascript:void(0);', '-1', '25');
INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) VALUES ('16', 'MENU_CFG_SHEBAO_PARAM', '社保参数', '1', '/sbo/cfg/shebaoParam/list', '15', '26');
INSERT INTO `shebao_oa`.`sys_privilege` (`pid`, `code`, `name`, `type`, `url`, `p_pid`, `sort`) VALUES ('17', 'MENU_CFG_SHEBAO_PARAM_LOG', '修改日志', '2', NULL, '15', '27');


INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('1', '15');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('1', '16');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('2', '15');
INSERT INTO `shebao_oa`.`sys_role_privilege` (`rid`, `pid`) VALUES ('2', '16');


CREATE TABLE `biz_data_batch` (
  `batch_no` varchar(32) NOT NULL COMMENT '批次号',
  `type` int(4) NOT NULL COMMENT '操作类型',
  `file` varchar(128) DEFAULT NULL COMMENT '上传文件',
  `path` varchar(128) DEFAULT NULL COMMENT '上传路径',
  `total` int(11) DEFAULT NULL COMMENT '总记录数',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `opid` int(11) NOT NULL COMMENT '操作员编号',
  PRIMARY KEY (`batch_no`),
  KEY `idx_opid` (`opid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据操作批次表';

CREATE TABLE `biz_data_desc` (
  `descid` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `batch_no` varchar(32) NOT NULL COMMENT '批次号',
  `prov` varchar(32) DEFAULT NULL COMMENT '省份',
  `city` varchar(32) DEFAULT NULL COMMENT '城市',
  `op_sb` varchar(11) DEFAULT NULL COMMENT '社保专员',
  `op_kefu` varchar(11) DEFAULT NULL COMMENT '客服',
  `emp_company` varchar(128) DEFAULT NULL COMMENT '客户名称',
  `emp_name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `emp_idcard` varchar(32) DEFAULT NULL COMMENT '身份证',
  `emp_sex` varchar(4) DEFAULT NULL COMMENT '性别',
  `sb_month_op` varchar(8) DEFAULT NULL COMMENT '社保操作月份',
  `sb_month` varchar(8) DEFAULT NULL COMMENT '社保月份',
  `fund_month_op` varchar(8) DEFAULT NULL COMMENT '公积金操作月份',
  `fund_month` varchar(8) DEFAULT NULL COMMENT '公积金月份',
  `state` int(4) NOT NULL COMMENT '状态',
  `tip` varchar(512) DEFAULT NULL COMMENT '提示',
  `opid` int(11) NOT NULL COMMENT '操作员编号',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`descid`),
  KEY `idx_opid` (`opid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='数据操作减员表';

CREATE TABLE `biz_data_inc` (
  `incid` int(4) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `batch_no` varchar(32) NOT NULL COMMENT '批次号',
  `prov` varchar(32) DEFAULT NULL COMMENT '省份',
  `city` varchar(32) DEFAULT NULL COMMENT '城市',
  `op_kefu` varchar(32) DEFAULT NULL COMMENT '客服',
  `op_sb` varchar(32) DEFAULT NULL COMMENT '社保专员',
  `emp_company` varchar(128) DEFAULT NULL COMMENT '客户名称',
  `emp_name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `emp_idcard` varchar(32) DEFAULT NULL COMMENT '身份证',
  `emp_sex` varchar(8) DEFAULT NULL COMMENT '性别',
  `emp_nation` varchar(32) DEFAULT NULL COMMENT '民族',
  `emp_reged` varchar(32) DEFAULT NULL COMMENT '户籍',
  `sb_month_op` varchar(8) DEFAULT NULL COMMENT '社保操作月份',
  `sb_month` varchar(8) DEFAULT NULL COMMENT '社保月份',
  `sb_month_added` varchar(128) DEFAULT NULL COMMENT '社保补缴月份',
  `sb_base` varchar(16) DEFAULT NULL COMMENT '社保补缴月份',
  `sb_proj` varchar(128) DEFAULT NULL COMMENT '社保项目',
  `fund_month_op` varchar(8) DEFAULT NULL COMMENT '公积金操作月份',
  `fund_month` varchar(8) DEFAULT NULL COMMENT '公积金月份',
  `fund_month_added` varchar(128) DEFAULT NULL COMMENT '公积金补缴月份',
  `fund_base` varchar(16) DEFAULT NULL COMMENT '公积金基数',
  `fund_company_rate` varchar(16) DEFAULT NULL COMMENT '单位比例',
  `fund_emp_rate` varchar(16) DEFAULT NULL COMMENT '个人比例',
  `ext1` varchar(128) DEFAULT NULL COMMENT '补充信息1',
  `ext2` varchar(128) DEFAULT NULL COMMENT '补充信息2',
  `ext3` varchar(128) DEFAULT NULL COMMENT '补充信息3',
  `ext4` varchar(128) DEFAULT NULL COMMENT '补充信息4',
  `ext5` varchar(128) DEFAULT NULL COMMENT '补充信息5',
  `state` int(4) NOT NULL COMMENT '处理状态',
  `tip` varchar(512) DEFAULT NULL COMMENT '提示信息',
  `opid` int(11) NOT NULL COMMENT '操作员编号',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`incid`),
  KEY `idx_opid` (`opid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据操作增员表';

CREATE TABLE `biz_emp_desc` (
  `descid` int(11) NOT NULL COMMENT '主键',
  `batch_no` varchar(32) NOT NULL COMMENT '批次号',
  `prov_id` int(11) NOT NULL COMMENT '省份编号',
  `city_id` int(11) NOT NULL COMMENT '城市编号',
  `op_sb` int(11) NOT NULL COMMENT '社保专员',
  `op_kefu` int(11) NOT NULL COMMENT '客服',
  `emp_company` varchar(32) NOT NULL COMMENT '客户名称',
  `emp_name` varchar(32) NOT NULL COMMENT '姓名',
  `emp_idcard` varchar(32) NOT NULL COMMENT '身份证',
  `emp_sex` int(4) NOT NULL COMMENT '性别',
  `sb_month_op` int(11) DEFAULT NULL COMMENT '社保操作月份',
  `sb_month` int(11) DEFAULT NULL COMMENT '社保月份',
  `fund_month_op` int(11) DEFAULT NULL COMMENT '公积金操作月份',
  `fund_month` int(11) DEFAULT NULL COMMENT '公积金月份',
  `reply_sb` int(11) DEFAULT NULL COMMENT '社保处理状态',
  `reply_fund` int(11) DEFAULT NULL COMMENT '公积金处理状态',
  `reply_memo` varchar(255) DEFAULT NULL COMMENT '回复备注',
  `state` int(11) NOT NULL COMMENT '处理状态',
  `opid_tosb` int(11) DEFAULT NULL COMMENT '提交给社保专员的操作员编号',
  `time_tosb` datetime DEFAULT NULL COMMENT '提交给社保专员的时间',
  `opid_reply` int(11) DEFAULT NULL COMMENT '回复处理状态的操作员编号',
  `time_reply` datetime DEFAULT NULL COMMENT '回复处理状态的时间',
  PRIMARY KEY (`descid`),
  KEY `idx_op_sb` (`op_sb`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工减员表';

CREATE TABLE `biz_emp_inc` (
  `incid` int(11) NOT NULL COMMENT '主键',
  `batch_no` varchar(32) NOT NULL COMMENT '批次号',
  `prov_id` int(11) NOT NULL COMMENT '省份编号',
  `city_id` int(11) NOT NULL COMMENT '城市编号',
  `op_kefu` int(11) NOT NULL COMMENT '客服',
  `op_sb` int(11) NOT NULL COMMENT '社保专员',
  `emp_company` varchar(32) NOT NULL COMMENT '客户名称',
  `emp_name` varchar(32) NOT NULL COMMENT '姓名',
  `emp_idcard` varchar(32) NOT NULL COMMENT '身份证',
  `emp_sex` int(4) NOT NULL COMMENT '性别',
  `emp_nation` varchar(32) NOT NULL COMMENT '民族',
  `emp_reged` int(4) NOT NULL COMMENT '户籍',
  `sb_month_op` int(8) DEFAULT NULL COMMENT '社保操作月份',
  `sb_month` int(8) DEFAULT NULL COMMENT '社保月份',
  `sb_month_added` varchar(128) DEFAULT NULL COMMENT '社保补缴月份',
  `sb_base` varchar(32) DEFAULT NULL COMMENT '社保基数',
  `sb_proj` varchar(128) DEFAULT NULL COMMENT '社保项目',
  `fund_month_op` int(8) DEFAULT NULL COMMENT '公积金操作月份',
  `fund_month` int(8) DEFAULT NULL COMMENT '公积金月份',
  `fund_month_added` varchar(128) DEFAULT NULL COMMENT '公积金补缴月份',
  `fund_base` varchar(32) DEFAULT NULL COMMENT '公积金基数',
  `fund_company_rate` double(8,4) DEFAULT NULL COMMENT '单位比例',
  `fund_emp_rate` double(8,4) DEFAULT NULL COMMENT '个人比例',
  `ext1` varchar(128) DEFAULT NULL COMMENT '补充信息1',
  `ext2` varchar(128) DEFAULT NULL COMMENT '补充信息2',
  `ext3` varchar(128) DEFAULT NULL COMMENT '补充信息3',
  `ext4` varchar(128) DEFAULT NULL COMMENT '补充信息4',
  `ext5` varchar(128) DEFAULT NULL COMMENT '补充信息5',
  `reply_sb` int(11) DEFAULT NULL COMMENT '社保处理状态',
  `reply_fund` int(11) DEFAULT NULL COMMENT '公积金处理状态',
  `reply_memo` varchar(255) DEFAULT NULL COMMENT '回复备注',
  `state` int(4) NOT NULL COMMENT '处理状态',
  `opid_tosb` int(11) DEFAULT NULL COMMENT '提交给社保专员的操作员编号',
  `time_tosb` datetime DEFAULT NULL COMMENT '提交给社保专员的时间',
  `opid_reply` varchar(255) DEFAULT NULL COMMENT '回复处理状态的操作员编号',
  `time_reply` datetime DEFAULT NULL COMMENT '回复处理状态的时间',
  PRIMARY KEY (`incid`),
  KEY `idx_op_sb` (`op_sb`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sys_city` (
  `cid` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `level` int(4) NOT NULL,
  `p_cid` int(11) NOT NULL,
  `sort` int(11) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sys_city` VALUES ('1000', '广东省', '1', '-1', '1');
INSERT INTO `sys_city` VALUES ('1001', '东莞市', '2', '1000', '2');
INSERT INTO `sys_city` VALUES ('1002', '汕头市', '2', '1000', '3');
INSERT INTO `sys_city` VALUES ('1003', '韶关市', '2', '1000', '4');
INSERT INTO `sys_city` VALUES ('1004', '深圳市', '2', '1000', '5');
INSERT INTO `sys_city` VALUES ('1005', '江门市', '2', '1000', '6');
INSERT INTO `sys_city` VALUES ('1006', '阳江市', '2', '1000', '7');
INSERT INTO `sys_city` VALUES ('1007', '云浮市', '2', '1000', '8');
INSERT INTO `sys_city` VALUES ('1008', '湛江市', '2', '1000', '9');
INSERT INTO `sys_city` VALUES ('1009', '肇庆市', '2', '1000', '10');
INSERT INTO `sys_city` VALUES ('1010', '中山市', '2', '1000', '11');
INSERT INTO `sys_city` VALUES ('1011', '珠海市', '2', '1000', '12');
INSERT INTO `sys_city` VALUES ('1012', '惠州市', '2', '1000', '13');
INSERT INTO `sys_city` VALUES ('1013', '茂名市', '2', '1000', '14');
INSERT INTO `sys_city` VALUES ('1014', '清远市', '2', '1000', '15');
INSERT INTO `sys_city` VALUES ('1015', '佛山市南海区', '2', '1000', '16');
INSERT INTO `sys_city` VALUES ('1016', '佛山市顺德区', '2', '1000', '17');
INSERT INTO `sys_city` VALUES ('1017', '佛山市禅城区', '2', '1000', '18');
INSERT INTO `sys_city` VALUES ('1018', '广州市', '2', '1000', '19');
INSERT INTO `sys_city` VALUES ('2000', '安徽省', '1', '-1', '20');
INSERT INTO `sys_city` VALUES ('2001', '合肥市', '2', '2000', '21');
INSERT INTO `sys_city` VALUES ('2002', '马鞍山', '2', '2000', '22');
INSERT INTO `sys_city` VALUES ('2003', '芜湖市', '2', '2000', '23');
INSERT INTO `sys_city` VALUES ('3000', '福建省', '1', '-1', '24');
INSERT INTO `sys_city` VALUES ('3001', '福州市', '2', '3000', '25');
INSERT INTO `sys_city` VALUES ('3002', '泉州市', '2', '3000', '26');
INSERT INTO `sys_city` VALUES ('4000', '甘肃省', '1', '-1', '27');
INSERT INTO `sys_city` VALUES ('4001', '兰州市', '2', '4000', '28');
INSERT INTO `sys_city` VALUES ('5000', '广西省', '1', '-1', '29');
INSERT INTO `sys_city` VALUES ('5001', '南宁市', '2', '5000', '30');
INSERT INTO `sys_city` VALUES ('6000', '贵州省', '1', '-1', '31');
INSERT INTO `sys_city` VALUES ('6001', '贵阳市', '2', '6000', '32');
INSERT INTO `sys_city` VALUES ('7000', '河南省', '1', '-1', '33');
INSERT INTO `sys_city` VALUES ('7001', '商丘市', '2', '7000', '34');
INSERT INTO `sys_city` VALUES ('7002', '郑州市', '2', '7000', '35');
INSERT INTO `sys_city` VALUES ('8000', '黑龙江省', '1', '-1', '36');
INSERT INTO `sys_city` VALUES ('8001', '哈尔滨市', '2', '8000', '37');
INSERT INTO `sys_city` VALUES ('9000', '湖北省', '1', '-1', '38');
INSERT INTO `sys_city` VALUES ('9001', '武汉市', '2', '9000', '39');
INSERT INTO `sys_city` VALUES ('10000', '湖南省', '1', '-1', '40');
INSERT INTO `sys_city` VALUES ('10001', '长沙市', '2', '10000', '41');
INSERT INTO `sys_city` VALUES ('10002', '邵阳市', '2', '10000', '42');
INSERT INTO `sys_city` VALUES ('10003', '岳阳市', '2', '10000', '43');
INSERT INTO `sys_city` VALUES ('11000', '江苏省', '1', '-1', '44');
INSERT INTO `sys_city` VALUES ('11001', '昆山市', '2', '11000', '45');
INSERT INTO `sys_city` VALUES ('11002', '南京市', '2', '11000', '46');
INSERT INTO `sys_city` VALUES ('11003', '南通市', '2', '11000', '47');
INSERT INTO `sys_city` VALUES ('11004', '苏州市新区', '2', '11000', '48');
INSERT INTO `sys_city` VALUES ('11005', '苏州市园区', '2', '11000', '49');
INSERT INTO `sys_city` VALUES ('11006', '泰州市', '2', '11000', '50');
INSERT INTO `sys_city` VALUES ('11007', '无锡市', '2', '11000', '51');
INSERT INTO `sys_city` VALUES ('11008', '徐州市', '2', '11000', '52');
INSERT INTO `sys_city` VALUES ('11009', '张家港市', '2', '11000', '53');
INSERT INTO `sys_city` VALUES ('11010', '镇江市', '2', '11000', '54');
INSERT INTO `sys_city` VALUES ('11011', '宜兴市', '2', '11000', '55');
INSERT INTO `sys_city` VALUES ('12000', '江西省', '1', '-1', '56');
INSERT INTO `sys_city` VALUES ('12001', '九江市', '2', '12000', '57');
INSERT INTO `sys_city` VALUES ('12002', '南昌市', '2', '12000', '58');
INSERT INTO `sys_city` VALUES ('13000', '宁夏省', '1', '-1', '59');
INSERT INTO `sys_city` VALUES ('13001', '银川市', '2', '13000', '60');
INSERT INTO `sys_city` VALUES ('14000', '四川省', '1', '-1', '61');
INSERT INTO `sys_city` VALUES ('14001', '巴中市', '2', '14000', '62');
INSERT INTO `sys_city` VALUES ('14002', '成都市', '2', '14000', '63');
INSERT INTO `sys_city` VALUES ('14003', '南充市', '2', '14000', '64');
INSERT INTO `sys_city` VALUES ('15000', '新疆维吾尔自治区', '1', '-1', '65');
INSERT INTO `sys_city` VALUES ('15001', '乌鲁木齐市', '2', '15000', '66');
INSERT INTO `sys_city` VALUES ('16000', '浙江省', '1', '-1', '67');
INSERT INTO `sys_city` VALUES ('16001', '杭州市开发区', '2', '16000', '69');
INSERT INTO `sys_city` VALUES ('16002', '杭州市滨江区', '2', '16000', '70');
INSERT INTO `sys_city` VALUES ('16003', '嘉兴市', '2', '16000', '71');
INSERT INTO `sys_city` VALUES ('16004', '金华市', '2', '16000', '72');
INSERT INTO `sys_city` VALUES ('16005', '宁波市鄞州区', '2', '16000', '73');
INSERT INTO `sys_city` VALUES ('16006', '绍兴市', '2', '16000', '74');
INSERT INTO `sys_city` VALUES ('16007', '台州市', '2', '16000', '75');
INSERT INTO `sys_city` VALUES ('16008', '温州市', '2', '16000', '76');
INSERT INTO `sys_city` VALUES ('16009', '萧山市', '2', '16000', '77');
INSERT INTO `sys_city` VALUES ('16010', '扬州市', '2', '16000', '78');
INSERT INTO `sys_city` VALUES ('99000', '直辖市', '1', '-1', '9999');
INSERT INTO `sys_city` VALUES ('99001', '北京市', '2', '99000', '9999');
INSERT INTO `sys_city` VALUES ('99002', '上海市', '2', '99000', '9999');
INSERT INTO `sys_city` VALUES ('99003', '天津市', '2', '99000', '9999');
INSERT INTO `sys_city` VALUES ('99004', '重庆市', '2', '99000', '9999');

CREATE TABLE `sys_privilege` (
  `pid` int(11) NOT NULL,
  `code` varchar(32) NOT NULL,
  `name` varchar(32) NOT NULL,
  `type` int(4) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `p_pid` int(11) NOT NULL,
  `sort` int(11) NOT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sys_privilege` VALUES ('5', 'MENU_DATA_IMPORT_MANAGE', '增减员导入', '1', 'javascript:void(0);', '-1', '5');
INSERT INTO `sys_privilege` VALUES ('6', 'MENU_DATE_IMPORT_BATCH', '批次管理', '1', '/sbo/data/batch/list', '5', '6');
INSERT INTO `sys_privilege` VALUES ('7', 'MENU_DATA_IMPORT_INC', '增员导入', '1', '/sbo/data/inc/list', '5', '7');
INSERT INTO `sys_privilege` VALUES ('8', 'MENU_DATA_IMPORT_DESC', '减员导入', '1', '/sbo/data/desc/list', '5', '8');
INSERT INTO `sys_privilege` VALUES ('9', 'MENU_EMP_REPLY_MANAGE', '增减员反馈', '1', 'javascript:void(0);', '-1', '19');
INSERT INTO `sys_privilege` VALUES ('10', 'MENU_EMP_REPLY_INC', '增员反馈', '1', '/sbo/emp/inc/list', '9', '20');
INSERT INTO `sys_privilege` VALUES ('11', 'MENU_EMP_REPLY_DESC', '减员反馈', '1', '/sbo/emp/desc/list', '9', '21');
INSERT INTO `sys_privilege` VALUES ('12', 'MENU_EMP_ON_MANAGE', '增减员在册', '1', 'javascript:void(0);', '-1', '12');
INSERT INTO `sys_privilege` VALUES ('13', 'MENU_EMP_ON_INC', '增员在册', '1', '/sbo/emp/inc/kefu/list', '12', '13');
INSERT INTO `sys_privilege` VALUES ('14', 'MENU_EMP_ON_DESC', '减员在册', '1', '/sbo/emp/desc/kefu/list', '12', '14');

CREATE TABLE `sys_role` (
  `rid` int(11) NOT NULL,
  `code` varchar(32) NOT NULL,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sys_role` VALUES ('1', 'KEFU', '客服');
INSERT INTO `sys_role` VALUES ('2', 'SHEBAO', '社保专员');

CREATE TABLE `sys_role_privilege` (
  `rid` int(11) NOT NULL,
  `pid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sys_role_privilege` VALUES ('1', '5');
INSERT INTO `sys_role_privilege` VALUES ('1', '6');
INSERT INTO `sys_role_privilege` VALUES ('1', '7');
INSERT INTO `sys_role_privilege` VALUES ('1', '8');
INSERT INTO `sys_role_privilege` VALUES ('2', '9');
INSERT INTO `sys_role_privilege` VALUES ('2', '10');
INSERT INTO `sys_role_privilege` VALUES ('2', '11');
INSERT INTO `sys_role_privilege` VALUES ('1', '12');
INSERT INTO `sys_role_privilege` VALUES ('1', '13');
INSERT INTO `sys_role_privilege` VALUES ('1', '14');

CREATE TABLE `sys_user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `login_name` varchar(32) NOT NULL,
  `real_name` varchar(32) NOT NULL,
  `password` varchar(128) NOT NULL,
  `reg_time` datetime NOT NULL,
  `login_time` datetime NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sys_user` VALUES ('1', 'admin', '管理员', '123456shanp', '2016-06-19 14:33:30', '2016-06-19 14:33:36');
INSERT INTO `sys_user` VALUES ('4', 'zhanglisan', '张丽三', '123456', '2016-07-06 10:55:19', '2016-07-06 10:55:22');
INSERT INTO `sys_user` VALUES ('5', 'zhonglizhuan', '钟丽转', '123456', '2016-07-06 10:55:54', '2016-07-06 10:55:57');
INSERT INTO `sys_user` VALUES ('6', 'lilihua', '黎丽华', '123456', '2016-07-06 10:56:29', '2016-07-06 10:56:34');
INSERT INTO `sys_user` VALUES ('7', 'konghuanxia', '孔焕霞', '123456', '2016-07-06 10:57:30', '2016-07-06 10:57:33');
INSERT INTO `sys_user` VALUES ('8', 'panqiurong', '潘秋荣', '123456', '2016-07-06 10:59:56', '2016-07-06 10:59:59');
INSERT INTO `sys_user` VALUES ('9', 'zhonglishan', '钟丽珊', '123456', '2016-07-06 11:00:33', '2016-07-06 11:00:38');
INSERT INTO `sys_user` VALUES ('10', 'jinlan', '金兰', '123456', '2016-07-06 11:01:08', '2016-07-06 11:01:12');
INSERT INTO `sys_user` VALUES ('11', 'zhouhui', '周慧', '123456', '2016-07-06 11:01:31', '2016-07-06 11:01:34');
INSERT INTO `sys_user` VALUES ('12', 'chenziyun', '陈紫韵', '123456', '2016-07-06 11:01:56', '2016-07-06 11:01:58');
INSERT INTO `sys_user` VALUES ('13', 'chenjia', '陈嘉', '123456', '2016-07-06 11:02:19', '2016-07-06 11:02:21');
INSERT INTO `sys_user` VALUES ('14', 'wuqinyun', '吴青云', '123456', '2016-07-06 11:02:47', '2016-07-06 11:02:49');

DROP TABLE IF EXISTS `sys_user_city`;
CREATE TABLE `sys_user_city` (
  `uid` int(11) NOT NULL,
  `cid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `sys_user_role` (
  `uid` int(11) NOT NULL,
  `rid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('4', '2');
INSERT INTO `sys_user_role` VALUES ('5', '2');
INSERT INTO `sys_user_role` VALUES ('6', '2');
INSERT INTO `sys_user_role` VALUES ('7', '2');
INSERT INTO `sys_user_role` VALUES ('6', '1');
INSERT INTO `sys_user_role` VALUES ('8', '1');
INSERT INTO `sys_user_role` VALUES ('9', '1');
INSERT INTO `sys_user_role` VALUES ('10', '1');
INSERT INTO `sys_user_role` VALUES ('11', '1');
INSERT INTO `sys_user_role` VALUES ('12', '1');
INSERT INTO `sys_user_role` VALUES ('13', '1');
INSERT INTO `sys_user_role` VALUES ('14', '1');


ALTER TABLE `biz_data_desc`
ADD COLUMN `force_pass`  INT(2) NOT NULL DEFAULT 1 COMMENT '强制通过记录 1-否 2-是' AFTER `fund_month`;

ALTER TABLE `biz_data_inc`
ADD COLUMN `force_pass`  INT(2) NOT NULL DEFAULT 1 COMMENT '强制通过记录 1-否 2-是' AFTER `ext5`;