考勤(Time Management)管理模块
在考勤管理系统中,从数据库的角度来看,可能会有以下表:
1.出勤表
2.请假记录
3.加班记录
4.月度汇总
出勤记录表(TimeClock)的MySQL建表语句:
CREATE TABLE `tm_attendance` (
`attendance_id` int NOT NULL AUTO_INCREMENT COMMENT '出勤id',
`employee_id` int DEFAULT NULL COMMENT '员工id',
`time_clock_date` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '打卡日期',
`first_time` datetime DEFAULT NULL COMMENT '首次打卡时间',
`last_time` datetime DEFAULT NULL COMMENT '末次打卡时间',
`state` char(1) COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '状态:0满勤,1旷工,2早退,3迟到',
`Lateness_time` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '迟到时长',
`early_leaving_duration` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '早退时长',
`description` varchar(512) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '详细描述',
`del_flag` tinyint(1) DEFAULT '0' COMMENT '删除标志:0存在,1删除',
`create_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新者',
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`remark` varchar(512) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`attendance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='出勤表';
加班记录表(OverTime)的MySQL建表语句:
CREATE TABLE tm_over_time
(
overtime_id int primary key auto_increment comment '加班记录id',
employee_id int comment '员工id',
start_time DATETIME comment '加班开始时间',
end_time datetime comment '加班结束时间',
total_time varchar(32) comment '加班总时间',
reason varchar(256) comment '加班原因',
auditor varchar(64) comment '审核人',
state char(1) DEFAULT '0' COMMENT '状态:0待审核,1批准,2拒绝',
del_flag tinyint(1) DEFAULT '0' COMMENT '删除标志:0存在,1删除',
create_by VARCHAR(64) COMMENT '创建者',
create_time DATETIME COMMENT '创建时间',
update_by VARCHAR(64) COMMENT '更新者',
update_time DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
remark VARCHAR(512) COMMENT '备注'
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='加班记录表';
请假记录表(LeaveTime)的MySQL建表语句:
CREATE TABLE tm_leave_time
(
overtime_id int primary key auto_increment comment '请假记录id',
employee_id int comment '员工id',
type char(1) comment '请假类型:0事假,1病假',
total_time varchar(32) comment '假期总天数',
reason varchar(256) comment '请假原因',
auditor varchar(64) comment '审核人',
state char(1) DEFAULT '0' COMMENT '状态:0待审核,1批准,2拒绝',
del_flag tinyint(1) DEFAULT '0' COMMENT '删除标志:0存在,1删除',
create_by VARCHAR(64) COMMENT '创建者',
create_time DATETIME COMMENT '创建时间',
update_by VARCHAR(64) COMMENT '更新者',
update_time DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
remark VARCHAR(512) COMMENT '备注'
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='请假记录表';
考勤制度表(SystemTime)的MySQL建表语句:
CREATE TABLE tm_system_time
(
overtime_id int primary key auto_increment comment '考勤制度id',
project varchar(32) comment '考勤项目',//是哪个考勤制度,比如夏季和冬季作息时间同
start_time datetime comment '上班时间',
end_time datetime comment '下班时间',
total_time varchar(32) comment '总时间',
del_flag tinyint(1) DEFAULT '0' COMMENT '删除标志:0存在,1删除',
create_by VARCHAR(64) COMMENT '创建者',
create_time DATETIME COMMENT '创建时间',
update_by VARCHAR(64) COMMENT '更新者',
update_time DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
remark VARCHAR(512) COMMENT '备注'
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='考勤制度表';
月度汇总表(Summary)的MySQL建表语句
CREATE TABLE tm_summary
(
summary_id int primary key auto_increment comment '月度汇总id',
employee_id int comment '员工id',
month varchar(16) comment '月份',
attendance_number varchar(16) comment '实际出勤天数',
lateness_number varchar(16) comment '迟到次数',
lateness_time varchar(16) comment '迟到时长',
early_leaving_number varchar(16) comment '早退次数',
early_leaving_total varchar(16) comment '早退时长',
overtime_number varchar(16) comment '加班次数',
overtime_total varchar(16) comment '加班时长',
absenteeism_number varchar(16) comment '旷工次数',
leave_time_number varchar(16) comment '请假次数',
leave_time_total varchar(16) comment '请假时长',
del_flag tinyint(1) DEFAULT '0' COMMENT '删除标志:0存在,1删除',
create_by VARCHAR(64) COMMENT '创建者',
create_time DATETIME COMMENT '创建时间',
update_by VARCHAR(64) COMMENT '更新者',
update_time DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
remark VARCHAR(512) COMMENT '备注'
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='月度汇总表';
导表:
1,文件校验
2,读取数据
3,