hr-saas/doc/operation.sql

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

-- 2018-08-20 laiyinghe  DB:hr_saas
-- 新增表.
CREATE TABLE `t_wxyj_interaction` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `summary_id` int(11) NOT NULL,
  `eid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'wxyj_employee表的ID',
  `com_userid` int(11) NOT NULL,
  `edec` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否减员数据的交互',
  `wxyj` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '五险一金类别',
  `wxyj_month` mediumint(9) NOT NULL DEFAULT '0' COMMENT '月份',
  `jh_opt` varchar(16) NOT NULL DEFAULT '',
  `email` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否邮件通知',
  `kefu_uid` int(11) NOT NULL DEFAULT '0' COMMENT '客服ID',
  `prof_uid` int(11) NOT NULL DEFAULT '0' COMMENT '专员ID',
  `kefu_status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '处理状态',
  `prof_status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '处理状态',
  `kefu_content` varchar(127) NOT NULL DEFAULT '' COMMENT '内容',
  `prof_content` varchar(127) NOT NULL DEFAULT '' COMMENT '内容',
  `kefu_time` datetime DEFAULT NULL,
  `prof_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `wxyj_interaction_kefu_idx` (`kefu_uid`),
  KEY `wxyj_interaction_prof_idx` (`prof_uid`),
  KEY `wxyj_interaction_sumid_idx` (`summary_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='专员客服交互表';


-- 2018-08-08 laiyinghe  DB:hr_saas
-- 新增表.
CREATE TABLE `t_wxyj_summary_kefu` (
  `summary_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '汇总数据ID',
  `kefu_uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '客服ID',
  `opt_month` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '核算操作月份',
  KEY `wxyj_summary_id_idx` (`summary_id`),
  KEY `wxyj_summary_kefuid_idx` (`kefu_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='五险一金汇总数据与客服标识的关联表';

-- 新增字段，索引
ALTER TABLE `t_wxyj_employee`
ADD COLUMN `kefu_uid`  int UNSIGNED NOT NULL DEFAULT 0 COMMENT '客服的ID' AFTER `summary_id`,
ADD INDEX `wxyj_e_summayid_idx` (`summary_id`) ,
ADD INDEX `wxyj_e_opt_month_idx` (`opt_month`) ,
ADD INDEX `wxyj_e_city_idx` (`city_code`) ,
ADD INDEX `wxyj_e_kefuid_idx` (`kefu_uid`) ;

-- 新增字段
ALTER TABLE `t_wxyj_employee_dec`
ADD COLUMN `kefu_uid`  int UNSIGNED NOT NULL DEFAULT 0 COMMENT '客服用户ID' AFTER `summary_id`;


-- 2018-07-31 laiyinghe  DB:hr_saas
-- 新增企业联系人表.
CREATE TABLE `t_company_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '企业ID',
  `uname` varchar(16) NOT NULL DEFAULT '' COMMENT '联系人姓名',
  `uphone` varchar(16) NOT NULL DEFAULT '' COMMENT '联系人手机号码',
  `uemail` varchar(32) NOT NULL DEFAULT '' COMMENT '联系人邮箱',
  `gen_user` varchar(255) NOT NULL DEFAULT '' COMMENT '操作用户',
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `com_contact_cid_idx` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='企业联系人表';

-- 新增企业邮递信息表.
CREATE TABLE `t_company_mail` (
  `company_id` int(10) unsigned NOT NULL,
  `muser` varchar(16) NOT NULL DEFAULT '' COMMENT '收件人姓名',
  `mphone` varchar(16) NOT NULL DEFAULT '' COMMENT '收件人电话',
  `maddress` varchar(64) NOT NULL DEFAULT '' COMMENT '收件地址',
  `zip_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '收件邮编',
  `opt_user` varchar(16) NOT NULL DEFAULT '' COMMENT '更新人',
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='企业邮递信息';

-- 2018-07-27 laiyinghe  DB:hr_saas
-- 新增专员报送日 配置表.
CREATE TABLE `t_base_submit_day` (
  `city_code` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '城市代码',
  `use_submit` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '使用的报送日',
  `submit1` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '报送日',
  `submit2` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '报送日',
  `submit3` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '报送日',
  `opt_user` varchar(16) NOT NULL DEFAULT '' COMMENT '配置人',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`city_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='专员报送日';

-- 2018-07-19 laiyinghe  DB:hr_saas
-- 新增字段.
ALTER TABLE `t_base_shebao`
ADD COLUMN `app_use`  tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否适用于APP使用' AFTER `p_id`;



-- 2018-07-09 laiyinghe  DB:hr_saas
-- 新增 增加专员ID字段, 增加客服ID字段.
ALTER TABLE `t_wxyj_employee_summary`
ADD COLUMN `prof_uid`  int UNSIGNED NOT NULL DEFAULT 0 COMMENT '专员的用户ID' AFTER `ent_id`,
COMMENT='五险一金参缴主表';

-- 2018-07-04 laiyinghe  DB:hr_saas
-- 新增 提交的导入增员的员工数据表.
CREATE TABLE `t_wxyj_import_inc_member` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `inc_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '主表ID',
  `com_userid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '员工ID',
  `shebao_month` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '社保增员月份',
  `shebao_added_months` varchar(72) NOT NULL DEFAULT '' COMMENT '社保补缴月份',
  `fund_month` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '公积金月份',
  `fund_added_months` varchar(72) NOT NULL DEFAULT '' COMMENT '公积金补缴月份',
  `calced` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否已计算',
  `e_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 't_wxyj_employee表的ID',
  PRIMARY KEY (`id`),
  KEY `wxyj_import_incm_iid_idx` (`inc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='提交的导入增员的员工数据表.';

-- 2018-07-04 laiyinghe  DB:hr_saas
-- 新增 提交导入的增员数据主表.
CREATE TABLE `t_wxyj_import_inc` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `temp_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '导入数据的主ID',
  `proxy_no` varchar(32) NOT NULL DEFAULT '' COMMENT '代缴ID',
  `city_code` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '城市ID',
  `opt_month` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '操作月份',
  `ent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '企业ID',
  `prof_uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '专员ID',
  `kefu_uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '客服ID',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
  `gen_time` datetime NOT NULL COMMENT '生成时间',
  `summary_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '计算生成的数据主ID',
  `calc_time` datetime NOT NULL COMMENT '完成计算时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `wxyj_import_inc_proxyno_idx` (`proxy_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='提交导入的增员数据主表';

-- 2018-06-27 laiyinghe  DB:hr_saas
-- 新增增减员导入主表.
CREATE TABLE `t_wxyj_temp_inc_dec` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上传人',
  `file_path_name` varchar(128) NOT NULL DEFAULT '' COMMENT '文件',
  `gen_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='增减员导入主表';

-- 新增增员导入的数据表.
CREATE TABLE `t_wxyj_temp_inc_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `temp_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '主表ID',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '企业ID',
  `city_code` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '城市代码',
  `param_id` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '社保方案ID',
  `conflicts` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '数据冲突数',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '员工标识',
  `pcity` varchar(16) NOT NULL DEFAULT '' COMMENT '省份',
  `city` varchar(16) NOT NULL DEFAULT '' COMMENT '城市',
  `prof_uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '专员的用户ID',
  `prof_user` varchar(16) NOT NULL DEFAULT '' COMMENT '社保专员',
  `company` varchar(32) NOT NULL DEFAULT '' COMMENT '客户名称',
  `username` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `idcard` varchar(18) NOT NULL DEFAULT '' COMMENT '身份证号码',
  `gender` varchar(4) NOT NULL DEFAULT '' COMMENT '性别',
  `nation` varchar(16) NOT NULL DEFAULT '' COMMENT '民族',
  `hukou` varchar(4) NOT NULL DEFAULT '' COMMENT '户藉类型',
  `sbopt_month` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '社保操作月份',
  `sb_month` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '社保月份',
  `sbadded_month` varchar(32) NOT NULL DEFAULT '' COMMENT '社保补缴月份',
  `sb_radix` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '社保缴费基数',
  `sb_name` varchar(16) NOT NULL DEFAULT '' COMMENT '社保方案',
  `fund_optmonth` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '公积金操作月份',
  `fund_month` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '公积金月份',
  `fundadded_month` varchar(64) NOT NULL DEFAULT '' COMMENT '公积金补缴月份',
  `fund_radix` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '公积金缴费基数',
  `ent_ratio` decimal(6,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '单位比例',
  `p_ratio` decimal(6,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '个人比例',
  `note1` varchar(128) NOT NULL DEFAULT '' COMMENT '补充信息1',
  `note2` varchar(128) NOT NULL DEFAULT '' COMMENT '补充信息2',
  `note3` varchar(128) NOT NULL DEFAULT '' COMMENT '补充信息3',
  PRIMARY KEY (`id`),
  KEY `temp_inc_dec_id_idx` (`temp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8 COMMENT='增员导入的数据表';


-- 新增 导入数据冲突的 数据表.
CREATE TABLE `t_wxyj_temp_inc_dec_error` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `temp_id` int(11) NOT NULL DEFAULT '0' COMMENT '临时数据ID',
  `data_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '临时数据项ID',
  `err_code` varchar(16) NOT NULL DEFAULT '' COMMENT '冲突类型',
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '与冲突的用户ID',
  `user_value` varchar(64) NOT NULL DEFAULT '' COMMENT '冲突值',
  `opt_value` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '冲突解决代码值',
  `notes` varchar(256) NOT NULL DEFAULT '' COMMENT '冲突描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 新增 减员 导入的数据表.
CREATE TABLE `t_wxyj_temp_dec_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `temp_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '主表ID',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '企业ID',
  `city_code` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '城市代码',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '员工ID',
  `pcity` varchar(16) NOT NULL DEFAULT '' COMMENT '省份',
  `city` varchar(16) NOT NULL DEFAULT '' COMMENT '城市',
  `prof_uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '专员的用户ID',
  `prof_user` varchar(16) NOT NULL DEFAULT '' COMMENT '社保专员',
  `company` varchar(32) NOT NULL DEFAULT '' COMMENT '客户名称',
  `username` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `idcard` varchar(18) NOT NULL DEFAULT '' COMMENT '身份证号码',
  `gender` varchar(4) NOT NULL DEFAULT '' COMMENT '性别',
  `sbopt_month` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '社保操作月份',
  `sb_month` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '社保月份',
  `fund_optmonth` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '公积金操作月份',
  `fund_month` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '公积金月份',
  `note1` varchar(128) NOT NULL DEFAULT '' COMMENT '补充信息1',
  `note2` varchar(128) NOT NULL DEFAULT '' COMMENT '补充信息2',
  `note3` varchar(128) NOT NULL DEFAULT '' COMMENT '补充信息3',
  `remark` varchar(128) NOT NULL DEFAULT '' COMMENT '数据说明',
  PRIMARY KEY (`id`),
  KEY `temp_dec_id_idx` (`temp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='减员导入的数据表';








-- 2018-06-26 laiyinghe  DB:hr_saas
-- 新增专员负责的城市表.
CREATE TABLE `t_base_user_prof` (
  `puid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '专员ID',
  `setting_uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '设置人ID',
  `pcity_code` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '省份代码',
  `city_codes` varchar(128) NOT NULL DEFAULT '' COMMENT '负责城市列表',
  KEY `prof_uid_idx` (`puid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='专员负责城市表';

-- 新增客服业务表.
CREATE TABLE `t_base_user_kefu` (
  `kefu_id` int(10) unsigned NOT NULL DEFAULT '0',
  `setting_uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '配置人ID',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '企业标识',
  `business` varchar(32) NOT NULL DEFAULT '' COMMENT '业务',
  KEY `kefu_busi_uid_idx` (`kefu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客服业务表';

-- 2018-06-25 laiyinghe  DB:hr_saas
-- 用户表增加字段
ALTER TABLE `t_base_user`
ADD COLUMN `u_role`  tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户工作角色(0企业用户 1客服 2专员)' AFTER `enabled`;

ALTER TABLE `t_base_user`
MODIFY COLUMN `email`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '邮箱' AFTER `password`,
MODIFY COLUMN `nickname`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '姓名或昵称' AFTER `email_verify`;





-- 2018-06-11 laiyinghe  DB:hr_saas
-- 补缴的结果表.
CREATE TABLE `t_wxyj_added_result` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `wxyj` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '社保OR公积金',
  `opt_month` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '核算月份',
  `added_month` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '补缴月份',
  `added_status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '补缴结果',
  `gen_time` datetime NOT NULL COMMENT '生成时间',
  PRIMARY KEY (`id`),
  KEY `added_result_uid_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='补缴的结果表';

-- 2018-04-11 laiyinghe  DB:hr_saas
-- 增加计算任务时,有批次号,因为前端支持多个城市一起生成.
ALTER TABLE `t_wxyj_task`
ADD COLUMN `batch_no`  varchar(12) NOT NULL DEFAULT '' COMMENT '批次' AFTER `ask_user`;


-- 2018-04-09 laiyinghe  DB:hr_saas
-- 增加计算任务生成时,顺便生成每项头.
ALTER TABLE `t_wxyj_employee_summary`
DROP COLUMN `shebao_head`,
MODIFY COLUMN `remark`  varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '说明' AFTER `update_time`,
ADD COLUMN `shebao_head`  varchar(255) NOT NULL DEFAULT '' COMMENT '社保表头项' AFTER `update_time`;

-- 2018-03-28 laiyinghe  DB:hr_saas
-- 增加补缴的兼容配置字段,补缴与正常交是一样的数据,则用这些字段.
ALTER TABLE `t_base_shebao`
MODIFY COLUMN `shebao_desc`  varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT ' 社保参数描述' AFTER `dec_day`,
ADD COLUMN `repay_charge`  decimal(6,2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '补缴服务费' AFTER `repay_num`,
ADD COLUMN `repay_desc`  varchar(256) NOT NULL DEFAULT '' COMMENT '补缴描述' AFTER `repay_charge`;


-- 2018-03-19 laiyinghe  DB:hr_saas
-- 修改员工方案表, 参保状态一个分两个.
ALTER TABLE `t_wxyj_employee_plan`
CHANGE COLUMN `fund_buy` `shebao_buy`  tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否参保社保' AFTER `shebao_uptime`,
CHANGE COLUMN `status` `fund_buy`  tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否参缴公积金' AFTER `fund_pratio`;


ALTER TABLE `t_base_user` ADD COLUMN `email` VARCHAR(64) DEFAULT NULL COMMENT '邮箱' AFTER `password`;
ALTER TABLE `t_base_user` ADD COLUMN `email_verify` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '邮箱是否已验证 0-未验证  1-已验证'  AFTER `email`;


-- 2017-07-11 laiyinghe  DB:hr_saas
-- 新建代发工资导入临时表。
CREATE TABLE `t_temp_payroll` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `for_month` int(10) unsigned NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `service_item` tinyint(3) unsigned NOT NULL,
  `user_name` varchar(16) NOT NULL,
  `bank_card` varchar(20) NOT NULL,
  `bank_name` varchar(32) NOT NULL,
  `wage` decimal(8,2) unsigned NOT NULL,
  `remark` varchar(64) DEFAULT NULL,
  `gen_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `temp_payroll_comid_idx` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='导入临时表';

-- 2017-07-11 laiyinghe  DB:hr_saas
-- 新建代发工资主表。
CREATE TABLE `t_payroll_list` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `payroll_id` int(10) unsigned NOT NULL COMMENT '清单标识',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户/员工标识',
  `bank_card` varchar(32) NOT NULL COMMENT '银行卡号',
  `bank_code` varchar(32) NOT NULL DEFAULT '' COMMENT '银行清算代码',
  `bank_name` varchar(32) NOT NULL COMMENT '银行中文名称',
  `wage` decimal(8,2) unsigned NOT NULL COMMENT '工资',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '工资发放状态',
  `remark` varchar(64) DEFAULT NULL COMMENT '备注',
  `gen_time` datetime DEFAULT NULL COMMENT '生成时间',
  `pay_times` tinyint(3) unsigned DEFAULT NULL COMMENT '尝试交易次数',
  `pay_no` varchar(64) DEFAULT NULL COMMENT '交易流水号',
  `pay_remark` varchar(256) DEFAULT NULL COMMENT '交易描述',
  `pay_time` datetime DEFAULT NULL COMMENT '交易时间',
  PRIMARY KEY (`id`),
  KEY `payroll_list_id_idx` (`payroll_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3508 DEFAULT CHARSET=utf8 COMMENT='代发工资清单';


-- 2017-07-11 laiyinghe  DB:hr_saas
-- 新建代发工资主表。CREATE TABLE `t_payroll` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL COMMENT '企业标识',
  `service_item` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '用途、项目',
  `for_month` int(10) unsigned NOT NULL COMMENT '月份',
  `total_fee` decimal(10,2) unsigned NOT NULL COMMENT '总金额',
  `members` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '员工数',
  `pay_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '工资发放方式',
  `pay_result` varchar(32) DEFAULT NULL COMMENT '发薪结果描述',
  `status` tinyint(3) unsigned DEFAULT '0' COMMENT '状态',
  `remark` varchar(128) DEFAULT NULL COMMENT '备注',
  `gen_user` int(10) unsigned NOT NULL COMMENT '生成此数据的用户',
  `gen_time` datetime DEFAULT NULL COMMENT '生成时间',
  `verify_user` int(10) unsigned DEFAULT '0' COMMENT '审核人员',
  `verify_remark` varchar(64) DEFAULT '' COMMENT '审核意见描述',
  `verify_time` datetime DEFAULT NULL COMMENT '审核时间',
  PRIMARY KEY (`id`),
  KEY `payroll_companyid_idx` (`company_id`),
  KEY `payroll_month_idx` (`for_month`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='代发工资主表';


-- 2017-07-11 laiyinghe  DB:hr_saas
-- 新建企业员工表。
CREATE TABLE `t_company_user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL COMMENT '公司标识',
  `user_name` varchar(16) NOT NULL COMMENT '员工姓名',
  `bank_card` varchar(20) NOT NULL COMMENT '银行卡号',
  `bank_name` varchar(32) NOT NULL,
  `status` tinyint(3) unsigned DEFAULT '0' COMMENT '员工状态(试用、正式、外包等)',
  `gen_time` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `company_user_comId_idx` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='企业的员工';


-- 2017-04-20 laiyinghe  DB:hr_saas
-- 新建企业数据表。
CREATE TABLE `t_company` (
  `company_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `full_name` varchar(64) NOT NULL COMMENT '企业全称',
  `address` varchar(64) DEFAULT NULL COMMENT '公司地址',
  `business_license_img` varchar(128) DEFAULT NULL COMMENT '营业执照',
  `legal_person` varchar(16) DEFAULT NULL COMMENT '法人',
  `legal_idcard_img` varchar(128) DEFAULT NULL COMMENT '法人身份证图片',
  `create_uid` int(10) unsigned DEFAULT '0' COMMENT '创建此记录用户标识',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态，见ComUserStatus',
  `gen_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 2017-04-20 laiyinghe  DB:hr_saas
-- 新建用户的权限表、用户角色的权限表。
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:hr_saas
-- 新建角色的权限表。
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-04-18 laiyinghe  DB:hr_saas
-- 新建基础用户表。
CREATE TABLE `t_base_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '帐号',
  `password` varchar(32) NOT NULL,
  `nickname` varchar(32) DEFAULT NULL COMMENT '姓名或昵称',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '所属公司标识',
  `create_uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建者标识',
  `enabled` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '是否可用',
  `reg_time` datetime DEFAULT NULL COMMENT '注册时间',
  `login_time` datetime DEFAULT NULL COMMENT '最新登录时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_name_indx` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;