帆软10.0基于Mysql考勤模块开发

最新推荐文章于 2022-08-26 14:39:05 发布
最新推荐文章于 2022-08-26 14:39:05 发布
阅读量671 收藏 4
点赞数
分类专栏： FineReport
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Jmayday/article/details/119821810
版权

需求:1、个人考勤明细表

        2、考勤汇总表

筛选条件:可以根据时间和部门进行考勤筛选

前提:1、新建一张日历表关联，可标注节假日

         2、将请假、外出、出差日期格式化为每天一条记录及请假天数

开发环境:mysql8.6  帆软10.0

可能会用到的与日期相关的函数:

date(字段) 
year(字段) 
cast(字段 as time)
date_format(字段,'YYYY-MM-DD %H:i')
注:后面的H大写，用24小时制

第一步 创建日期表

1、如果日期比较短的话可以自己写

2、百度sql已经写好了节假日的sql,建表插入记录,下面列一个21年的简单版

2021年所有日期

insert into day (day,code) values("2021-01-01","1");
insert into day (day,code) values("2021-01-02","1");
insert into day (day,code) values("2021-01-03","1");
insert into day (day,code) values("2021-01-04","0");
insert into day (day,code) values("2021-01-05","0");
insert into day (day,code) values("2021-01-06","0");
insert into day (day,code) values("2021-01-07","0");
insert into day (day,code) values("2021-01-08","0");
insert into day (day,code) values("2021-01-09","3");
insert into day (day,code) values("2021-01-10","3");
insert into day (day,code) values("2021-01-11","0");
insert into day (day,code) values("2021-01-12","0");
insert into day (day,code) values("2021-01-13","0");
insert into day (day,code) values("2021-01-14","0");
insert into day (day,code) values("2021-01-15","0");
insert into day (day,code) values("2021-01-16","3");
insert into day (day,code) values("2021-01-17","3");
insert into day (day,code) values("2021-01-18","0");
insert into day (day,code) values("2021-01-19","0");
insert into day (day,code) values("2021-01-20","0");
insert into day (day,code) values("2021-01-21","0");
insert into day (day,code) values("2021-01-22","0");
insert into day (day,code) values("2021-01-23","3");
insert into day (day,code) values("2021-01-24","3");
insert into day (day,code) values("2021-01-25",“0”);
insert into day (day,code) values(“2021-01-26",“0”);
insert into day (day,code) values(“2021-01-27",“0”);
insert into day (day,code) values(“2021-01-28",“0”);
insert into day (day,code) values(“2021-01-29",“0”);
insert into day (day,code) values(“2021-01-30",“3”);
insert into day (day,code) values(“2021-01-31",“3”);
insert into day (day,code) values(“2021-02-01",“0”);
insert into day (day,code) values(“2021-02-02”,“0”);
insert into day (day,code) values(“2021-02-03”,“0”);
insert into day (day,code) values(“2021-02-04”,“0”);
insert into day (day,code) values(“2021-02-05”,“0”);
insert into day (day,code) values(“2021-02-06”,“3”);
insert into day (day,code) values(“2021-02-07”,“2”);
insert into day (day,code) values(“2021-02-08”,“0”);
insert into day (day,code) values(“2021-02-09”,“0”);
insert into day (day,code) values(“2021-02-10”,“0”);
insert into day (day,code) values(“2021-02-11”,“0”);
insert into day (day,code) values(“2021-02-12”,“0”);
insert into day (day,code) values(“2021-02-13”,“3”);
insert into day (day,code) values(“2021-02-14”,“3”);
insert into day (day,code) values(“2021-02-15”,“0”);
insert into day (day,code) values(“2021-02-16”,“0”);
insert into day (day,code) values(“2021-02-17”,“0”);
insert into day (day,code) values(“2021-02-18”,“0”);
insert into day (day,code) values(“2021-02-19”,“0”);
insert into day (day,code) values(“2021-02-20”,“2”);
insert into day (day,code) values(“2021-02-21”,“3”);
insert into day (day,code) values(“2021-02-22”,“0”);
insert into day (day,code) values(“2021-02-23”,“0”);
insert into day (day,code) values(“2021-02-24”,“0”);
insert into day (day,code) values(“2021-02-25”,“0”);
insert into day (day,code) values(“2021-02-26”,“0”);
insert into day (day,code) values(“2021-02-27”,“3”);
insert into day (day,code) values(“2021-02-28”,“3”);
insert into day (day,code) values(“2021-03-01”,“0”);
insert into day (day,code) values(“2021-03-02”,“0”);
insert into day (day,code) values(“2021-03-03”,“0”);
insert into day (day,code) values(“2021-03-04”,“0”);
insert into day (day,code) values(“2021-03-05”,“0”);
insert into day (day,code) values(“2021-03-06”,“3”);
insert into day (day,code) values(“2021-03-07”,“3”);
insert into day (day,code) values(“2021-03-08”,“0”);
insert into day (day,code) values(“2021-03-09”,“0”);
insert into day (day,code) values(“2021-03-10”,“0”);
insert into day (day,code) values(“2021-03-11”,“0”);
insert into day (day,code) values(“2021-03-12”,“0”);
insert into day (day,code) values(“2021-03-13”,“3”);
insert into day (day,code) values(“2021-03-14”,“3”);
insert into day (day,code) values(“2021-03-15”,“0”);
insert into day (day,code) values(“2021-03-16”,“0”);
insert into day (day,code) values(“2021-03-17”,“0”);
insert into day (day,code) values(“2021-03-18”,“0”);
insert into day (day,code) values(“2021-03-19”,“0”);
insert into day (day,code) values(“2021-03-20”,“3”);
insert into day (day,code) values(“2021-03-21”,“3”);
insert into day (day,code) values(“2021-03-22”,“0”);
insert into day (day,code) values(“2021-03-23”,“0”);
insert into day (day,code) values(“2021-03-24”,“0”);
insert into day (day,code) values(“2021-03-25”,“0”);
insert into day (day,code) values(“2021-03-26”,“0”);
insert into day (day,code) values(“2021-03-27”,“3”);
insert into day (day,code) values(“2021-03-28”,“3”);
insert into day (day,code) values(“2021-03-29”,“0”);
insert into day (day,code) values(“2021-03-30”,“0”);
insert into day (day,code) values(“2021-03-31”,“0”);
insert into day (day,code) values(“2021-04-01”,“0”);
insert into day (day,code) values(“2021-04-02”,“0”);
insert into day (day,code) values(“2021-04-03”,“1”);
insert into day (day,code) values(“2021-04-04”,“1”);
insert into day (day,code) values(“2021-04-05”,“1”);
insert into day (day,code) values(“2021-04-06”,“0”);
insert into day (day,code) values(“2021-04-07”,“0”);
insert into day (day,code) values(“2021-04-08”,“0”);
insert into day (day,code) values(“2021-04-09”,“0”);
insert into day (day,code) values(“2021-04-10”,“3”);
insert into day (day,code) values(“2021-04-11”,“3”);
insert into day (day,code) values(“2021-04-12”,“0”);
insert into day (day,code) values(“2021-04-13”,“0”);
insert into day (day,code) values(“2021-04-14”,“0”);
insert into day (day,code) values(“2021-04-15”,“0”);
insert into day (day,code) values(“2021-04-16”,“0”);
insert into day (day,code) values(“2021-04-17”,“3”);
insert into day (day,code) values(“2021-04-18”,“3”);
insert into day (day,code) values(“2021-04-19”,“0”);
insert into day (day,code) values(“2021-04-20”,“0”);
insert into day (day,code) values(“2021-04-21”,“0”);
insert into day (day,code) values(“2021-04-22”,“0”);
insert into day (day,code) values(“2021-04-23”,“0”);
insert into day (day,code) values(“2021-04-24”,“3”);
insert into day (day,code) values(“2021-04-25”,“2”);
insert into day (day,code) values(“2021-04-26”,“0”);
insert into day (day,code) values(“2021-04-27”,“0”);
insert into day (day,code) values(“2021-04-28”,“0”);
insert into day (day,code) values(“2021-04-29”,“0”);
insert into day (day,code) values(“2021-04-30”,“0”);
insert into day (day,code) values(“2021-05-01”,“1”);
insert into day (day,code) values(“2021-05-02”,“1”);
insert into day (day,code) values(“2021-05-03”,“1”);
insert into day (day,code) values(“2021-05-04”,“1”);
insert into day (day,code) values(“2021-05-05”,“1”);
insert into day (day,code) values(“2021-05-06”,“0”);
insert into day (day,code) values(“2021-05-07”,“0”);
insert into day (day,code) values(“2021-05-08”,“2”);
insert into day (day,code) values(“2021-05-09”,“3”);
insert into day (day,code) values(“2021-05-10”,“0”);
insert into day (day,code) values(“2021-05-11”,“0”);
insert into day (day,code) values(“2021-05-12”,“0”);
insert into day (day,code) values(“2021-05-13”,“0”);
insert into day (day,code) values(“2021-05-14”,“0”);
insert into day (day,code) values(“2021-05-15”,“3”);
insert into day (day,code) values(“2021-05-16”,“3”);
insert into day (day,code) values(“2021-05-17”,“0”);
insert into day (day,code) values(“2021-05-18”,“0”);
insert into day (day,code) values(“2021-05-19”,“0”);
insert into day (day,code) values(“2021-05-20”,“0”);
insert into day (day,code) values(“2021-05-21”,“0”);
insert into day (day,code) values(“2021-05-22”,“3”);
insert into day (day,code) values(“2021-05-23”,“3”);
insert into day (day,code) values(“2021-05-24”,“0”);
insert into day (day,code) values(“2021-05-25”,“0”);
insert into day (day,code) values(“2021-05-26”,“0”);
insert into day (day,code) values(“2021-05-27”,“0”);
insert into day (day,code) values(“2021-05-28”,“0”);
insert into day (day,code) values(“2021-05-29”,“3”);
insert into day (day,code) values(“2021-05-30”,“3”);
insert into day (day,code) values(“2021-05-31”,“0”);
insert into day (day,code) values(“2021-06-01”,“0”);
insert into day (day,code) values(“2021-06-02”,“0”);
insert into day (day,code) values(“2021-06-03”,“0”);
insert into day (day,code) values(“2021-06-04”,“0”);
insert into day (day,code) values(“2021-06-05”,“3”);
insert into day (day,code) values(“2021-06-06”,“3”);
insert into day (day,code) values(“2021-06-07”,“0”);
insert into day (day,code) values(“2021-06-08”,“0”);
insert into day (day,code) values(“2021-06-09”,“0”);
insert into day (day,code) values(“2021-06-10”,“0”);
insert into day (day,code) values(“2021-06-11”,“0”);
insert into day (day,code) values(“2021-06-12”,“1”);
insert into day (day,code) values(“2021-06-13”,“1”);
insert into day (day,code) values(“2021-06-14”,“1”);
insert into day (day,code) values(“2021-06-15”,“0”);
insert into day (day,code) values(“2021-06-16”,“0”);
insert into day (day,code) values(“2021-06-17”,“0”);
insert into day (day,code) values(“2021-06-18”,“0”);
insert into day (day,code) values(“2021-06-19”,“3”);
insert into day (day,code) values(“2021-06-20”,“3”);
insert into day (day,code) values(“2021-06-21”,“0”);
insert into day (day,code) values(“2021-06-22”,“0”);
insert into day (day,code) values(“2021-06-23”,“0”);
insert into day (day,code) values(“2021-06-24”,“0”);
insert into day (day,code) values(“2021-06-25”,“0”);
insert into day (day,code) values(“2021-06-26”,“3”);
insert into day (day,code) values(“2021-06-27”,“3”);
insert into day (day,code) values(“2021-06-28”,“0”);
insert into day (day,code) values(“2021-06-29”,“0”);
insert into day (day,code) values(“2021-06-30”,“0”);
insert into day (day,code) values(“2021-07-01”,“0”);
insert into day (day,code) values(“2021-07-02”,“0”);
insert into day (day,code) values(“2021-07-03”,“3”);
insert into day (day,code) values(“2021-07-04”,“3”);
insert into day (day,code) values(“2021-07-05”,“0”);
insert into day (day,code) values(“2021-07-06”,“0”);
insert into day (day,code) values(“2021-07-07”,“0”);
insert into day (day,code) values(“2021-07-08”,“0”);
insert into day (day,code) values(“2021-07-09”,“0”);
insert into day (day,code) values(“2021-07-10”,“3”);
insert into day (day,code) values(“2021-07-11”,“3”);
insert into day (day,code) values(“2021-07-12”,“0”);
insert into day (day,code) values(“2021-07-13”,“0”);
insert into day (day,code) values(“2021-07-14”,“0”);
insert into day (day,code) values(“2021-07-15”,“0”);
insert into day (day,code) values(“2021-07-16”,“0”);
insert into day (day,code) values(“2021-07-17”,“3”);
insert into day (day,code) values(“2021-07-18”,“3”);
insert into day (day,code) values(“2021-07-19”,“0”);
insert into day (day,code) values(“2021-07-20”,“0”);
insert into day (day,code) values(“2021-07-21”,“0”);
insert into day (day,code) values(“2021-07-22”,“0”);
insert into day (day,code) values(“2021-07-23”,“0”);
insert into day (day,code) values(“2021-07-24”,“3”);
insert into day (day,code) values(“2021-07-25”,“3”);
insert into day (day,code) values(“2021-07-26”,“0”);
insert into day (day,code) values(“2021-07-27”,“0”);
insert into day (day,code) values(“2021-07-28”,“0”);
insert into day (day,code) values(“2021-07-29”,“0”);
insert into day (day,code) values(“2021-07-30”,“0”);
insert into day (day,code) values(“2021-07-31”,“3”);
insert into day (day,code) values(“2021-08-01”,“3”);
insert into day (day,code) values(“2021-08-02”,“0”);
insert into day (day,code) values(“2021-08-03”,“0”);
insert into day (day,code) values(“2021-08-04”,“0”);
insert into day (day,code) values(“2021-08-05”,“0”);
insert into day (day,code) values(“2021-08-06”,“0”);
insert into day (day,code) values(“2021-08-07”,“3”);
insert into day (day,code) values(“2021-08-08”,“3”);
insert into day (day,code) values(“2021-08-09”,“0”);
insert into day (day,code) values(“2021-08-10”,“0”);
insert into day (day,code) values(“2021-08-11”,“0”);
insert into day (day,code) values(“2021-08-12”,“0”);
insert into day (day,code) values(“2021-08-13”,“0”);
insert into day (day,code) values(“2021-08-14”,“3”);
insert into day (day,code) values(“2021-08-15”,“3”);
insert into day (day,code) values(“2021-08-16”,“0”);
insert into day (day,code) values(“2021-08-17”,“0”);
insert into day (day,code) values(“2021-08-18”,“0”);
insert into day (day,code) values(“2021-08-19”,“0”);
insert into day (day,code) values(“2021-08-20”,“0”);
insert into day (day,code) values(“2021-08-21”,“3”);
insert into day (day,code) values(“2021-08-22”,“3”);
insert into day (day,code) values(“2021-08-23”,“0”);
insert into day (day,code) values(“2021-08-24”,“0”);
insert into day (day,code) values(“2021-08-25”,“0”);
insert into day (day,code) values(“2021-08-26”,“0”);
insert into day (day,code) values(“2021-08-27”,“0”);
insert into day (day,code) values(“2021-08-28”,“3”);
insert into day (day,code) values(“2021-08-29”,“3”);
insert into day (day,code) values(“2021-08-30”,“0”);
insert into day (day,code) values(“2021-08-31”,“0”);
insert into day (day,code) values(“2021-09-01”,“0”);
insert into day (day,code) values(“2021-09-02”,“0”);
insert into day (day,code) values(“2021-09-03”,“0”);
insert into day (day,code) values(“2021-09-04”,“3”);
insert into day (day,code) values(“2021-09-05”,“3”);
insert into day (day,code) values(“2021-09-06”,“0”);
insert into day (day,code) values(“2021-09-07”,“0”);
insert into day (day,code) values(“2021-09-08”,“0”);
insert into day (day,code) values(“2021-09-09”,“0”);
insert into day (day,code) values(“2021-09-10”,“0”);
insert into day (day,code) values(“2021-09-11”,“3”);
insert into day (day,code) values(“2021-09-12”,“3”);
insert into day (day,code) values(“2021-09-13”,“0”);
insert into day (day,code) values(“2021-09-14”,“0”);
insert into day (day,code) values(“2021-09-15”,“0”);
insert into day (day,code) values(“2021-09-16”,“0”);
insert into day (day,code) values(“2021-09-17”,“0”);
insert into day (day,code) values(“2021-09-18”,“2”);
insert into day (day,code) values(“2021-09-19”,“1”);
insert into day (day,code) values(“2021-09-20”,“1”);
insert into day (day,code) values(“2021-09-21”,“1”);
insert into day (day,code) values(“2021-09-22”,“0”);
insert into day (day,code) values(“2021-09-23”,“0”);
insert into day (day,code) values(“2021-09-24”,“0”);
insert into day (day,code) values(“2021-09-25”,“3”);
insert into day (day,code) values(“2021-09-26”,“2”);
insert into day (day,code) values(“2021-09-27”,“0”);
insert into day (day,code) values(“2021-09-28”,“0”);
insert into day (day,code) values(“2021-09-29”,“0”);
insert into day (day,code) values(“2021-09-30”,“0”);
insert into day (day,code) values(“2021-10-01”,“1”);
insert into day (day,code) values(“2021-10-02”,“1”);
insert into day (day,code) values(“2021-10-03”,“1”);
insert into day (day,code) values(“2021-10-04”,“1”);
insert into day (day,code) values(“2021-10-05”,“1”);
insert into day (day,code) values(“2021-10-06”,“1”);
insert into day (day,code) values(“2021-10-07”,“1”);
insert into day (day,code) values(“2021-10-08”,“0”);
insert into day (day,code) values(“2021-10-09”,“2”);
insert into day (day,code) values(“2021-10-10”,“3”);
insert into day (day,code) values(“2021-10-11”,“0”);
insert into day (day,code) values(“2021-10-12”,“0”);
insert into day (day,code) values(“2021-10-13”,“0”);
insert into day (day,code) values(“2021-10-14”,“0”);
insert into day (day,code) values(“2021-10-15”,“0”);
insert into day (day,code) values(“2021-10-16”,“3”);
insert into day (day,code) values(“2021-10-17”,“3”);
insert into day (day,code) values(“2021-10-18”,“0”);
insert into day (day,code) values(“2021-10-19”,“0”);
insert into day (day,code) values(“2021-10-20”,“0”);
insert into day (day,code) values(“2021-10-21”,“0”);
insert into day (day,code) values(“2021-10-22”,“0”);
insert into day (day,code) values(“2021-10-23”,“3”);
insert into day (day,code) values(“2021-10-24”,“3”);
insert into day (day,code) values(“2021-10-25”,“0”);
insert into day (day,code) values(“2021-10-26”,“0”);
insert into day (day,code) values(“2021-10-27”,“0”);
insert into day (day,code) values(“2021-10-28”,“0”);
insert into day (day,code) values(“2021-10-29”,“0”);
insert into day (day,code) values(“2021-10-30”,“3”);
insert into day (day,code) values(“2021-10-31”,“3”);
insert into day (day,code) values(“2021-11-01”,“0”);
insert into day (day,code) values(“2021-11-02”,“0”);
insert into day (day,code) values(“2021-11-03”,“0”);
insert into day (day,code) values(“2021-11-04”,“0”);
insert into day (day,code) values(“2021-11-05”,“0”);
insert into day (day,code) values(“2021-11-06”,“3”);
insert into day (day,code) values(“2021-11-07”,“3”);
insert into day (day,code) values(“2021-11-08”,“0”);
insert into day (day,code) values(“2021-11-09”,“0”);
insert into day (day,code) values(“2021-11-10”,“0”);
insert into day (day,code) values(“2021-11-11”,“0”);
insert into day (day,code) values(“2021-11-12”,“0”);
insert into day (day,code) values(“2021-11-13”,“3”);
insert into day (day,code) values(“2021-11-14”,“3”);
insert into day (day,code) values(“2021-11-15”,“0”);
insert into day (day,code) values(“2021-11-16”,“0”);
insert into day (day,code) values(“2021-11-17”,“0”);
insert into day (day,code) values(“2021-11-18”,“0”);
insert into day (day,code) values(“2021-11-19”,“0”);
insert into day (day,code) values(“2021-11-20”,“3”);
insert into day (day,code) values(“2021-11-21”,“3”);
insert into day (day,code) values(“2021-11-22”,“0”);
insert into day (day,code) values(“2021-11-23”,“0”);
insert into day (day,code) values(“2021-11-24”,“0”);
insert into day (day,code) values(“2021-11-25”,“0”);
insert into day (day,code) values(“2021-11-26”,“0”);
insert into day (day,code) values(“2021-11-27”,“3”);
insert into day (day,code) values(“2021-11-28”,“3”);
insert into day (day,code) values(“2021-11-29”,“0”);
insert into day (day,code) values(“2021-11-30”,“0”);
insert into day (day,code) values(“2021-12-01”,“0”);
insert into day (day,code) values(“2021-12-02”,“0”);
insert into day (day,code) values(“2021-12-03”,“0”);
insert into day (day,code) values(“2021-12-04”,“3”);
insert into day (day,code) values(“2021-12-05”,“3”);
insert into day (day,code) values(“2021-12-06”,“0”);
insert into day (day,code) values(“2021-12-07”,“0”);
insert into day (day,code) values(“2021-12-08”,“0”);
insert into day (day,code) values(“2021-12-09”,“0”);
insert into day (day,code) values(“2021-12-10”,“0”);
insert into day (day,code) values(“2021-12-11”,“3”);
insert into day (day,code) values(“2021-12-12”,“3”);
insert into day (day,code) values(“2021-12-13”,“0”);
insert into day (day,code) values(“2021-12-14”,“0”);
insert into day (day,code) values(“2021-12-15”,“0”);
insert into day (day,code) values(“2021-12-16”,“0”);
insert into day (day,code) values(“2021-12-17”,“0”);
insert into day (day,code) values(“2021-12-18”,“3”);
insert into day (day,code) values(“2021-12-19”,“3”);
insert into day (day,code) values(“2021-12-20”,“0”);
insert into day (day,code) values(“2021-12-21”,“0”);
insert into day (day,code) values(“2021-12-22”,“0”);
insert into day (day,code) values(“2021-12-23”,“0”);
insert into day (day,code) values(“2021-12-24”,“0”);
insert into day (day,code) values(“2021-12-25”,“3”);
insert into day (day,code) values(“2021-12-26”,“3”);
insert into day (day,code) values(“2021-12-27”,“0”);
insert into day (day,code) values(“2021-12-28”,“0”);
insert into day (day,code) values(“2021-12-29”,“0”);
insert into day (day,code) values(“2021-12-30”,“0”);
insert into day (day,code) values(“2021-12-31”,“0”);

3、写存储过程，自己创建表格

代码如下:

--创建表结构
CREATE TABLE `m_dim_day` (  
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DAY_ID` varchar(10) DEFAULT NULL,     
  `DAY_SHORT_DESC` varchar(10) DEFAULT NULL,  
  `DAY_LONG_DESC` varchar(50) DEFAULT NULL,  
  `WEEK_DESC` varchar(20) DEFAULT NULL,  
  `WEEK_ID` varchar(20) DEFAULT NULL,  
  `WEEK_LONG_DESC` varchar(50) DEFAULT NULL,  
  `MONTH_ID` varchar(20) DEFAULT NULL,  
  `MONTH_LONG_DESC` varchar(50) DEFAULT NULL,  
  `QUARTER_ID` varchar(20) DEFAULT NULL,  
  `QUARTER_LONG_DESC` varchar(20) DEFAULT NULL,  
  `YEAR_ID` varchar(20) DEFAULT NULL,  
  `YEAR_LONG_DESC` varchar(50) DEFAULT NULL,  
  PRIMARY KEY (`ID`)  
) ENGINE=InnoDB AUTO_INCREMENT=731 DEFAULT CHARSET=utf8;

写存储过程,代码如下:

CREATE DEFINER=`root`@`localhost` PROCEDURE `f_m_dim_day`(in yr VARCHAR(20))
begin  
declare i int;
declare start_date varchar(20);
declare end_date varchar(20);
declare date_count int;

    set i=0;  
        set start_date= concat(yr, '-01-01');
        set end_date = concat(yr+1,'-01-01');
    DELETE from m_dim_day where year_id = yr;
        set date_count = datediff(end_date, start_date);
        
    while i < date_count DO  
        INSERT into m_dim_day (DAY_ID,DAY_SHORT_DESC,DAY_LONG_DESC,WEEK_DESC,WEEK_ID,WEEK_LONG_DESC,MONTH_ID,MONTH_LONG_DESC,QUARTER_ID,QUARTER_LONG_DESC,YEAR_ID,YEAR_LONG_DESC)  
SELECT  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m%d') DAY_ID,  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') DAY_SHORT_DESC,  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年%m月%d日') DAY_LONG_DESC,  
case DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'))  
when 1 then '星期日' 
when 2 then '星期一' 
when 3 then '星期二' 
when 4 then '星期三' 
when 5 then '星期四' 
when 6 then '星期五' when 7 then '星期六' end WEEK_DESC,  
                
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%u') WEEK_ID,  
                
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%u周') WEEK_LONG_DESC,  
                
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m') MONTH_ID,  
                
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%m月') MONTH_LONG_DESC,  
                
CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),quarter(STR_TO_DATE( start_date,'%Y-%m-%d %H:%i:%s'))) QUARTER_ID,  
                
CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),'年第',quarter(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')),'季度') QUARTER_LONG_DESC,  
                
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y') YEAR_ID,  
               
 DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年') YEAR_LONG_DESC  
        
from dual;  
        
set i=i+1;  
        
set start_date = DATE_FORMAT(date_add(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),interval 1 day),'%Y-%m-%d');  
    end while;  
end

调用:

call f_m_dim_day('2018')

 结果如下:

第二部分 格式化日期

1、取一段开始时间和结束时间，按日期列出所有记录

select * from (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date 
from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 
 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 
 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 
 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 
 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 
 7 union select 8 union select 9) t4) v where selected_date between '2020-02-10' and '2020-03-15'

 第三部分  考勤明细表sql

代码如下:

select 部门,姓名,日期,签到时间,
签退时间,迟到时间,早退时间,
date_format(cast('2021-08-08 09:11:00'as time),'%H:%i') as sbz,
date_format(cast('2021-08-08 18:01:00'as time),'%H:%i') as xbz,
case  when qj_lb <>'' then concat(qj_lb,'(',qj_start,'~',qj_end,')')
else (case when wc_date is not null  then concat('外出','(',wc_start,'~',wc_end,')') 
else (case when cc_date is not null  then concat('出差','(',cc_date,')') 
else (case when 公休日 <> '' then 公休日 else '' 
end)
end)
end) 
end 考勤异常原因说明
from (
SELECT p.*, ods_kq_jiejiari_t.note AS 公休日 
FROM 
(SELECT n.*, o.出差日期 as cc_date 
FROM
(SELECT l.*, m.FD_WAICHUDATE AS  wc_date, date_format(m.FD_KAISHITIME,'%Y-%m-%d %H:%i') AS wc_start, date_format(m.FD_JIESHUTIME,'%Y-%m-%d %H:%i') AS wc_end 
FROM
(SELECT y.FD_ID, y.部门, y.账户名, y.姓名, y.备注, y.日期, 
y.`考勤日期`, y.`签到时间`, y.`签退时间`, y.迟到时间, y.早退时间, 
z.FD_QINGJIADATE AS 请假日期, z.FD_QINGJIALEIBIE AS 请假类别,date_format(z.FD_KAISHITIME,'%Y-%m-%d %H:%i')  AS qj_start, date_format(z.FD_JIESHUTIME,'%Y-%m-%d %H:%i')  AS qj_end,
case z.FD_QINGJIALEIBIE when '1' then '事假' 
                      when '2' then '年假'
					  when '3' then '病假'
					  when '5' then '婚假'
					  when '6' then '产假'
					  when '7' then '陪产假'
					  when '8' then '工伤假'
					  when '9' then '其他'
else '' end qj_lb 
FROM 
(SELECT b.*, c.考勤日期, c.签到时间, c.签退时间, c.迟到时间, c.早退时间 FROM
(SELECT a.FD_ID, a.`备注`, a.`部门`, a.`账户名`, a.`姓名`, date AS 日期 
FROM ods_kq_calendar
CROSS JOIN 
(SELECT FD_ID, FD_NOTE AS 备注, FD_LOGIN_NAME AS 账户名, FD_NAME AS 姓名, FD_SUOZAIBUMEN AS 部门 FROM ods_kq_renyuan_t) a
ORDER BY a.`姓名`) b
LEFT JOIN 
(SELECT g.SSN, x.考勤日期, x.签到时间, x.签退时间, 
CASE
WHEN (x.签到时间 > '09:10:00' AND x.签到时间 <= '10:45:00') THEN (HOUR(x.签到时间) - 9) *60 + (MINUTE(x.签到时间) - 10)
ELSE ''
END 迟到时间,
CASE
WHEN (x.签退时间 < '18:00:00' AND x.签退时间 >= '17:30:00') THEN (60 - MINUTE(x.签退时间))
ELSE ''
END 早退时间
FROM 
(SELECT t.USERID, t.考勤日期, 
CASE
WHEN ((HOUR(MIN(t.签到时间)) >= 11) OR ((HOUR(MIN(t.签到时间)) = 10) AND (MINUTE(MIN(t.签到时间)) >= 45))) THEN ''
ELSE MIN(t.签到时间)
END 签到时间, 
CASE
WHEN ((HOUR(MAX(t.签退时间)) >= 18) OR ((HOUR(MAX(t.签退时间)) = 17) AND (MINUTE(MAX(t.签退时间)) >= 30))) THEN MAX(t.签退时间)
ELSE ''
END 签退时间 
FROM
(SELECT USERID, DATE(CHECKTIME) AS 考勤日期, TIME(CHECKTIME) AS 签到时间, TIME(CHECKTIME) AS 签退时间 
FROM checkinout WHERE (DATE(CHECKTIME))) t
GROUP BY t.USERID, t.考勤日期) x
LEFT JOIN
(SELECT USERID, SSN FROM userinfo) g
ON x.USERID = g.USERID) c
ON b.账户名 = c.SSN AND b.日期 = c.考勤日期) y
LEFT JOIN
(SELECT FD_SHENQINGREN, FD_QINGJIALEIBIE, FD_QINGJIADATE, FD_KAISHITIME, FD_JIESHUTIME FROM ods_kq_qjsq_t) z
ON y.FD_ID = z.FD_SHENQINGREN AND y.日期 = z.FD_QINGJIADATE) l
LEFT JOIN 
(SELECT FD_SHENQINGREN, FD_WAICHUDATE, FD_KAISHITIME, FD_JIESHUTIME FROM ods_kq_wcsq_t) m
ON l.FD_ID = m.FD_SHENQINGREN AND l.日期 = m.FD_WAICHUDATE) n
LEFT JOIN
(SELECT FD_SHENQINGREN, FD_DATE AS 出差日期 FROM ods_kq_ccsq_t) o
ON n.FD_ID = o.FD_SHENQINGREN AND n.日期 = o.出差日期) p
LEFT JOIN ods_kq_jiejiari_t
ON p.日期 = ods_kq_jiejiari_t.jiejiaridate
ORDER BY p.部门, p.账户名, p.日期 ASC
)tmp
where 1=1
and 日期 between '${开始日期}'  and  '${结束日期}'
${if(len(部门) == 0,"","and 部门 = '" + 部门+ "'")}

sql解析: 

select 部门,姓名,日期,签到时间,
签退时间,迟到时间,早退时间,

--上下班标准时间
date_format(cast('2021-08-08 09:11:00'as time),'%H:%i') as sbz,
date_format(cast('2021-08-08 18:01:00'as time),'%H:%i') as xbz,
case  when qj_lb <>'' then concat(qj_lb,'(',qj_start,'~',qj_end,')')
else (case when wc_date is not null  
then concat('外出','(',wc_start,'~',wc_end,')') 
else (case when cc_date is not null  then concat('出差','(',cc_date,')') 
else (case when 公休日 <> '' then 公休日 else '' 
end)
end)
end) 
end 考勤异常原因说明
from (

--考勤明细内层综合宽表
SELECT p.*, ods_kq_jiejiari_t.note AS 公休日 
FROM 
(SELECT n.*, o.出差日期 as cc_date 
FROM
(SELECT l.*, m.FD_WAICHUDATE AS  wc_date, 
date_format(m.FD_KAISHITIME,'%Y-%m-%d %H:%i') AS wc_start, date_format(m.FD_JIESHUTIME,'%Y-%m-%d %H:%i') AS wc_end 
FROM
(SELECT y.FD_ID, y.部门, y.账户名, y.姓名, y.备注, y.日期, 
y.`考勤日期`, y.`签到时间`, y.`签退时间`, y.迟到时间, y.早退时间, 
z.FD_QINGJIADATE AS 请假日期, z.FD_QINGJIALEIBIE AS 请假类别,date_format(z.FD_KAISHITIME,'%Y-%m-%d %H:%i')  AS qj_start, date_format(z.FD_JIESHUTIME,'%Y-%m-%d %H:%i')  AS qj_end,
case z.FD_QINGJIALEIBIE when '1' then '事假' 
                      when '2' then '年假'
					  when '3' then '病假'
					  when '5' then '婚假'
					  when '6' then '产假'
					  when '7' then '陪产假'
					  when '8' then '工伤假'
					  when '9' then '其他'
else '' end qj_lb 
FROM 
(SELECT b.*, c.考勤日期, c.签到时间, c.签退时间, c.迟到时间, c.早退时间 FROM
(SELECT a.FD_ID, a.`备注`, a.`部门`, a.`账户名`, a.`姓名`, date AS 日期 
FROM ods_kq_calendar

--日期表与人员表关联
CROSS JOIN 
(SELECT FD_ID, FD_NOTE AS 备注, FD_LOGIN_NAME AS 账户名, 
FD_NAME AS 姓名, FD_SUOZAIBUMEN AS 部门 FROM ods_kq_renyuan_t) a
ORDER BY a.`姓名`) b
LEFT JOIN 
(SELECT g.SSN, x.考勤日期, x.签到时间, x.签退时间, 

--打卡时间判断
CASE
WHEN (x.签到时间 > '09:10:00' AND x.签到时间 <= '10:45:00') 
THEN (HOUR(x.签到时间) - 9) *60 + (MINUTE(x.签到时间) - 10)
ELSE ''
END 迟到时间,
CASE
WHEN (x.签退时间 < '18:00:00' AND x.签退时间 >= '17:30:00') 
THEN (60 - MINUTE(x.签退时间))
ELSE ''
END 早退时间
FROM 
(SELECT t.USERID, t.考勤日期, 
CASE
WHEN ((HOUR(MIN(t.签到时间)) >= 11) 
OR ((HOUR(MIN(t.签到时间)) = 10) AND (MINUTE(MIN(t.签到时间)) >= 45))) THEN ''
ELSE MIN(t.签到时间)
END 签到时间, 
CASE
WHEN ((HOUR(MAX(t.签退时间)) >= 18) 
OR ((HOUR(MAX(t.签退时间)) = 17) AND (MINUTE(MAX(t.签退时间)) >= 30))) 
THEN MAX(t.签退时间)
ELSE ''
END 签退时间 
FROM
(SELECT USERID, DATE(CHECKTIME) AS 考勤日期, TIME(CHECKTIME) AS 签到时间, 
TIME(CHECKTIME) AS 签退时间 
FROM checkinout WHERE (DATE(CHECKTIME))) t
GROUP BY t.USERID, t.考勤日期) x
LEFT JOIN
(SELECT USERID, SSN FROM userinfo) g
ON x.USERID = g.USERID) c
ON b.账户名 = c.SSN AND b.日期 = c.考勤日期) y

--请假申请表
LEFT JOIN
(SELECT FD_SHENQINGREN, FD_QINGJIALEIBIE, FD_QINGJIADATE, 
FD_KAISHITIME, FD_JIESHUTIME FROM ods_kq_qjsq_t) z
ON y.FD_ID = z.FD_SHENQINGREN AND y.日期 = z.FD_QINGJIADATE) l

--外出申请表
LEFT JOIN 
(SELECT FD_SHENQINGREN, FD_WAICHUDATE, FD_KAISHITIME, FD_JIESHUTIME 
FROM ods_kq_wcsq_t) m
ON l.FD_ID = m.FD_SHENQINGREN AND l.日期 = m.FD_WAICHUDATE) n

--出差申请表
LEFT JOIN
(SELECT FD_SHENQINGREN, FD_DATE AS 出差日期 FROM ods_kq_ccsq_t) o
ON n.FD_ID = o.FD_SHENQINGREN AND n.日期 = o.出差日期) p
LEFT JOIN ods_kq_jiejiari_t
ON p.日期 = ods_kq_jiejiari_t.jiejiaridate
ORDER BY p.部门, p.账户名, p.日期 ASC
)tmp
where 1=1
and 日期 between '${开始日期}'  and  '${结束日期}'
${if(len(部门) == 0,"","and 部门 = '" + 部门+ "'")}

总结需要考虑的几点特殊情况:

1、请假但仍旧打卡

2、上午上班时间之后的最晚打卡记录和下班前的最早打卡记录

3、实际出勤天数的计算规则

4、以及事后补请假出差单等等情况 

Jmayday
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
帆软制作简单请假系统
m0_52925522的博客
03-27 1000
一 思路 表结构 1.请假表的储存需要一个唯一的ID - - UUID 2.状态码的改变来定义表的流程 基本准备 二，决策系统同步用户 查询数据后使用树数据集方便查看 三，填报表制作 请假人使用函数$fine_username获取登录者的名字 组长审批表 其中是否同意选项中 使用数据字典 同意则修改状态码为 “1” 否则“9” 根据组长修改的请假状态码 同意状态 是 1 查询部长需要处理的 请假单 部长同意则 修改状态 2 否则 状态 9 四，决策系统权限设置 挂载报表 设置
考勤系统完整源码（包含多个功能模块）
05-18
这个考勤系统是一个完整的考勤系统，其中有签到，查询，记录等多个功能模块。对于初级程序员来说是一个很好的学习程序，其中的思想值得借鉴
今天开始开发考勤管理模块
ttlzdy的专栏
11-12 201
考勤管理模块进行的操作与工资管理相当类似，业务上的出入需要引用一些新操作： “根据‘在线考勤’的报警及班前会或工作中发现的考勤情况，实时对考勤情况进行记录。”，这就需要一些实时操作，可以考虑用ＡＪＡＸ技术实现。 权限控制要更复杂一些了，“班长、主管、话务部管理人员可录入、修改、删除；员工只开放查询权限。班长只能操作本班组，主管只能操作本区，话务部部分管理人员可操作话务部所有人”...
福利模块功能实现及考勤模块准备
weixin_33881050的博客
05-15 116
为什么80%的码农都做不了架构师？>>> ...
MySQL 时间函数（答学生问:关于员工考勤查询）
老焦沉淀淀
07-17 4569
今天有学生问个关于MySQL时间操作的问题，直接看图：  查询某日人员的出勤时间问题。 我们都知道，企业员工考勤使用打卡方式，每天会记录每个员工的多次打卡记录，最后取每个员工的每天： 上班时间：当日最早时间； 下班时间：当日最晚时间。 所以分析查询每个员工每日考勤记录，需要根据员工分组，当天时间、时间最小和最大，时间格式化等操作，下面举例模拟该问题： 学生表student  ...
帆软日期格式转换_Web报表工具FineReport定时器使用说明
weixin_36106506的博客
01-13 1593
本书介绍了报表工具FineReportV6.5定时器功能的相关参数及配置说明，本书最后详细列举了几个使用定时器功能的模板制作示例。6.5定时器配置简要说明………………………………………………26.5定时器参数专题……………………………………………………106.5定时器填报…………………………………………………………156.5定时器日报专题……………………………………………………196.5定时器月报...
springboot2.1.6集成帆软10.0报表
07-25
使用springboot2.1.6集成帆软10.0报表，以及页面集成，启动需要外置tomcat，配置看我博文 https://blog.csdn.net/dylanwang_cn/article/details/97247090
帆软10.0版本继承到DEA的SSM项目的lib配置所需的library库的jar包
04-23
帆软10.0版本继承到DEA的SSM项目的lib配置所需的library库的jar包； 包含： fine-accumulator-10.0.jar fine-activator-10.0.jar fine-core-10.0.jar fine-datasource-10.0.jar fine-decision-10.0.jar fine-...
帆软FineReport(V10.0)产品介绍
12-24
帆软FineReport(V10.0)产品介绍 为大家带来更详实、更简明的FineReport基础培训课程，为FineReport新手上路保驾护航。介绍报表的相关知识和基本概念，以及报表工具FineReport初级报表的制作，分组报表、自由报表、...
report10.0帮助文档.zip
06-17
10.0版本是该软件的一个重要更新，它带来了许多新特性和改进，旨在提高用户体验和报表开发效率。本帮助文档详细介绍了FineReport 10.0的功能、操作指南以及常见问题解答，旨在帮助用户快速上手并充分利用其功能。 *...
帆软10版设计器，2019年03月JAR包
最新发布
01-11
标签中包含的关键词有“java”，这意味着帆软设计器是基于Java语言开发的，具有跨平台的特性。"报表设计器"表明这是一个用于创建和编辑报表的工具，"旧版本"暗示了这个JAR包可能包含了对旧版本系统的兼容性处理，...
Creo10.0二次开发 C++接口开发使用说明书
09-08
Creo 10.0 二次开发 C++ 接口开发使用说明书 Creo 10.0 是一个功能强大的三维 CAD 软件，它提供了强大的二次开发接口，allowing developers to create custom applications and plugins to extend the ...
iOS 10.0 真机开发包
05-19
iOS 10.0 真机开发包 正式版. 使用方法： 将下载好的调试包解压，快捷键command+shift+g前往文件夹： /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneOS.platform/DeviceSupport 把解压后的文件放入到...
BI产品帆软FineReport真分页显示模板+JS分页脚本
05-10
帆软分页显示的样例模板和分页脚本代码，适用于帆软报表开发人员，当使用帆软连接大数据量表进行显示时，可能会碰到页面刷新加载很慢的问题，通过此样例可以实现真分页的显示方式，大大的提高了页面刷新加载速度，...
FineReport帮助文档Word版
12-02
《FineReport帮助文档Word版》是为了解决官方FineReport帮助文档在查看时可能存在不便的问题而制作的。原版文档的左侧导航树未完全展开，用户需要逐个点击才能查看具体内容，这无疑增加了查找和理解信息的时间成本。...
帆软填报报表实现日期自动生成总结
一份耕耘，一分收获
08-26 6538
使用帆软报表 FineReport 的填报报表实现插入数据时自动生成日期，修改数据时，自动生成修改时间
日期格式转换，24时制和12小时制
weixin_43735471的博客
07-28 2648
以LocalDateTime举例，将时间日期转化成需要的格式，
帆软报表调用mysql存储过程_finereport报表，使用带参数的sql存储过程，报没有返回数据集的错...
weixin_39724469的博客
02-05 601
原因：在SqlServer数据库中，若定义的存储过程中包含有insert/update等更新操作时，在设计器中对存储过程进行调用时，会提示没有返回数据集的错误。解决方法：修改存储过程，在begin 后面添加 SET NOCOUNT ON。不管多复杂的sql存储过程 只需要在第一个大的begin后面添加 如下：ALTER proc [dbo].[proc_enterylist] @qybm varc...
帆软10.0如何实现countifs
08-12
帆软10.0是一款功能强大的数据分析与报表工具，可以通过使用公式函数来实现countifs功能。 在帆软10.0中，要实现countifs函数，可以按照以下步骤进行操作： 1. 打开或新建一个报表，在需要进行countifs计算的单元格中输入公式。 2. 在输入公式的单元格中，输入函数名countifs，接下来用小括号括起来的参数。 3. 在countifs函数的参数中，首先输入需要进行判断的数据范围（区域）。可以是一列、一行或一个区域。 4. 在参数中，接下来使用半角分号分隔，输入第一个条件的范围，即要判断的数据范围。 5. 之后使用半角分号分隔，输入第一个条件的判断条件。可以是数字、文本、日期等。 6. 如果要输入多个条件，依然使用半角分号分隔。接着输入第二个条件的范围，以及判断条件。 7. 按照需要添加更多的条件范围和判断条件，每组条件范围和判断条件之间使用半角分号分隔。 8. 在最后一个条件之后，使用右括号结束函数。 9. 按下回车键，即可得到countifs函数的计算结果。 通过以上步骤，就可以在帆软10.0中实现countifs函数的功能。在公式中添加多个条件范围和判断条件，可以根据实际需要进行灵活设置，实现更加复杂的条件统计。 帆软10.0还提供了丰富的其他公式函数，可以满足不同类型的数据分析和统计需求。帆软10.0的使用手册中有详细的公式函数说明和示例，可以根据具体情况进行参考和操作。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值