hr-saas/doc/operation.sql

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

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;