有四张表,分别是部门,员工,签到,时间(是否工作日),表结构如下:
部门department:
员工employee:
签到sign_record:
时间deal_calendar:
表结构和测试数据sql脚本:
/*
Navicat MySQL Data Transfer
Source Server : guangda
Source Server Version : 80013
Source Host : 127.0.0.1:3306
Source Database : guangda
Target Server Type : MYSQL
Target Server Version : 80013
File Encoding : 65001
Date: 2019-01-25 09:08:09
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for deal_calendar
-- ----------------------------
DROP TABLE IF EXISTS `deal_calendar`;
CREATE TABLE `deal_calendar` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL COMMENT '交易日历表',
`isDealDay` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'N' COMMENT '是否交易日,Y是,N不是',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of deal_calendar
-- ----------------------------
INSERT INTO `deal_calendar` VALUES ('1', '2018-09-01', 'N');
INSERT INTO `deal_calendar` VALUES ('2', '2018-09-02', 'N');
INSERT INTO `deal_calendar` VALUES ('3', '2018-09-03', 'Y');
INSERT INTO `deal_calendar` VALUES ('4', '2018-09-04', 'Y');
INSERT INTO `deal_calendar` VALUES ('5', '2018-09-05', 'Y');
INSERT INTO `deal_calendar` VALUES ('6', '2018-09-06', 'Y');
INSERT INTO `deal_calendar` VALUES ('7', '2018-09-07', 'Y');
INSERT INTO `deal_calendar` VALUES ('8', '2018-09-08', 'N');
INSERT INTO `deal_calendar` VALUES ('9', '2018-09-09', 'N');
INSERT INTO `deal_calendar` VALUES ('10', '2018-09-10', 'Y');
INSERT INTO `deal_calendar` VALUES ('11', '2018-09-11', 'Y');
INSERT INTO `deal_calendar` VALUES ('12', '2018-09-12', 'Y');
INSERT INTO `deal_calendar` VALUES ('13', '2018-09-13', 'Y');
INSERT INTO `deal_calendar` VALUES ('14', '2018-09-14', 'Y');
INSERT INTO `deal_calendar` VALUES ('15', '2018-09-15', 'N');
INSERT INTO `deal_calendar` VALUES ('16', '2018-09-16', 'N');
INSERT INTO `deal_calendar` VALUES ('17', '2018-09-17', 'Y');
INSERT INTO `deal_calendar` VALUES ('18', '2018-09-18', 'Y');
INSERT INTO `deal_calendar` VALUES ('19', '2018-09-19', 'Y');
INSERT INTO `deal_calendar` VALUES ('20', '2018-09-20', 'Y');
INSERT INTO `deal_calendar` VALUES ('21', '2018-09-21', 'Y');
INSERT INTO `deal_calendar` VALUES ('22', '2018-09-22', 'N');
INSERT INTO `deal_calendar` VALUES ('23', '2018-09-23', 'N');
INSERT INTO `deal_calendar` VALUES ('24', '2018-09-24', 'Y');
INSERT INTO `deal_calendar` VALUES ('25', '2018-09-25', 'Y');
INSERT INTO `deal_calendar` VALUES ('26', '2018-09-26', 'Y');
INSERT INTO `deal_calendar` VALUES ('27', '2018-09-27', 'Y');
INSERT INTO `deal_calendar` VALUES ('28', '2018-09-28', 'Y');
INSERT INTO `deal_calendar` VALUES ('29', '2018-09-29', 'N');
INSERT INTO `deal_calendar` VALUES ('30', '2018-09-30', 'N');
-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序列号',
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门名字',
`status` int(11) DEFAULT NULL COMMENT '部门状态 0不可用,1可用',
`no_permission_floors` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '无权限进入的门',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('1', '固定收益部', '1', null);
INSERT INTO `department` VALUES ('2', '资本市场部', '1', null);
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序列号',
`faceId` varchar(255) DEFAULT NULL COMMENT '注册人脸库人脸id',
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工姓名',
`empNO` varchar(64) NOT NULL COMMENT '员工编号',
`cardNO` int(32) DEFAULT NULL,
`postId` int(11) DEFAULT NULL COMMENT '关联岗位表id',
`dept` int(11) DEFAULT NULL COMMENT '关联部门表id',
`password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工密码',
`entryTime` date DEFAULT NULL COMMENT '入职时间',
`birthday` date DEFAULT NULL COMMENT '员工生日',
`sex` char(1) DEFAULT NULL COMMENT '性别,M男,F女',
`isblacklist` int(2) DEFAULT '0' COMMENT '是否黑名单 0不是,1是',
`vip` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'N' COMMENT '是否vip,N不是,Y是',
`tel` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '电话号码',
`img` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '头像地址',
`status` int(2) DEFAULT '1' COMMENT '0无效,1有效',
`faceToken` varchar(255) DEFAULT NULL COMMENT '人脸token',
`updateTime` datetime DEFAULT NULL COMMENT '更新时间',
`remark` varchar(255) DEFAULT NULL COMMENT '备注,VIP客户企业',
UNIQUE KEY `主索引` (`id`),
UNIQUE KEY `uk_empNo` (`empNO`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', null, '张三', 'zhangsan', '1', null, '1', null, null, '1990-10-12', 'F', '0', 'N', null, null, '1', 'zhangsan', null, null);
INSERT INTO `employee` VALUES ('2', null, '李四', 'lisi', '3', null, '1', null, null, '1982-12-16', 'M', '0', 'N', null, null, '1', 'lisi', null, null);
INSERT INTO `employee` VALUES ('3', null, '王五', 'wangwu', '2', null, '2', null, null, '1990-02-01', 'M', '0', 'N', null, null, '1', 'wangwu', null, null);
-- ----------------------------
-- Table structure for sign_record
-- ----------------------------
DROP TABLE IF EXISTS `sign_record`;
CREATE TABLE `sign_record` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序列号',
`empNO` varchar(32) DEFAULT NULL COMMENT '员工号',
`confidence` float DEFAULT NULL COMMENT '比对相似度',
`cardNo` varchar(32) DEFAULT NULL COMMENT '门禁卡号',
`signTime` datetime DEFAULT NULL COMMENT '签到时间',
`deviceNo` int(11) DEFAULT NULL COMMENT '设备号',
`imagePath` varchar(255) DEFAULT NULL COMMENT '头像路径',
`type` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '类型,0普通,1生日,2入职100天,3入职1000天,4,最早到,5本月全勤,6陌生人,7黑名单,8领导层',
`IO` char(1) DEFAULT NULL COMMENT '进出标识',
`source` int(1) DEFAULT NULL COMMENT '打卡数据来源,0人',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
UNIQUE KEY `主索引` (`id`),
KEY `index_sign_time` (`signTime`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sign_record
-- ----------------------------
INSERT INTO `sign_record` VALUES ('8', 'zhangsan', null, '1', '2018-09-24 19:59:33', '512', null, '4', null, '0', '');
INSERT INTO `sign_record` VALUES ('9', 'wangwu', null, '2', '2018-09-24 20:00:26', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('24', 'lisi', null, '3', '2018-09-24 07:32:53', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('26', 'lisi', null, '3', '2018-09-24 18:53:42', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('59', 'lisi', null, '3', '2018-09-30 09:08:37', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('60', 'lisi', null, '3', '2018-09-30 18:09:16', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('61', 'kesc', null, '2', '2018-09-29 07:20:58', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('62', 'zhangsan', null, '1', '2018-09-26 12:22:01', '512', null, '0', null, '0', null);
完整需求:
1.员工号,姓名,月份,工作日天数,是否全勤,正常天数,迟到天数,早退天数,迟到加早退天数,全天缺席天数
2.员工号,姓名,日期,考勤类别(正常,迟到,早退,迟到加早退,全天缺席)
补充:早上8点后迟到,下午5点半前早退,非工作日加班不用计算迟到或早退