通过sql和数据列表的方式快速生成考勤统计报表 - 功能扩展

场景：通过数据列表实现每月考勤数据的统计生成报表展示。

需求如图：

实现步骤：

首先需建立对应的表字段，字段如下：姓名、岗位、打卡日期、上班打卡时间、下班打卡时间，日期字段类型需要选择日期类型，如图：

保存好后，可以点击一键生成表单，然后设置表单样式就可以了：

具体设置表单请参考表单链接：

http://www.hotent.org:8880/chanzhi/index.php/book/EIPSAAS7.2/34.html

编写视图sql，如图参考：

SELECT a.F_yd AS 月度 ,a.F_xm AS 姓名,a.F_gw AS 岗位,
		/*列转行语法*/
    MAX(CASE a.F_r WHEN '01' THEN a.F_kqsjtj ELSE '' END ) '一',
    MAX(CASE a.F_r WHEN '02' THEN a.F_kqsjtj ELSE '' END ) '二',
    MAX(CASE a.F_r WHEN '03' THEN a.F_kqsjtj ELSE '' END ) '三',
  MAX(CASE a.F_r WHEN '04' THEN a.F_kqsjtj ELSE '' END ) '四',
  MAX(CASE a.F_r WHEN '05' THEN a.F_kqsjtj ELSE '' END ) '五',
    MAX(CASE a.F_r WHEN '06' THEN a.F_kqsjtj ELSE '' END ) '六',
    MAX(CASE a.F_r WHEN '07' THEN a.F_kqsjtj ELSE '' END ) '七',
  MAX(CASE a.F_r WHEN '08' THEN a.F_kqsjtj ELSE '' END ) '八',
    MAX(CASE a.F_r WHEN '09' THEN a.F_kqsjtj ELSE '' END ) '九',
    MAX(CASE a.F_r WHEN '10' THEN a.F_kqsjtj ELSE '' END ) '十',
  MAX(CASE a.F_r WHEN '11' THEN a.F_kqsjtj ELSE '' END ) '十一',
    MAX(CASE a.F_r WHEN '12' THEN a.F_kqsjtj ELSE '' END ) '十二',
    MAX(CASE a.F_r WHEN '13' THEN a.F_kqsjtj ELSE '' END ) '十三',
  MAX(CASE a.F_r WHEN '14' THEN a.F_kqsjtj ELSE '' END ) '十四',
    MAX(CASE a.F_r WHEN '15' THEN a.F_kqsjtj ELSE '' END ) '十五',
    MAX(CASE a.F_r WHEN '16' THEN a.F_kqsjtj ELSE '' END ) '十六',
  MAX(CASE a.F_r WHEN '17' THEN a.F_kqsjtj ELSE '' END ) '十七',
    MAX(CASE a.F_r WHEN '18' THEN a.F_kqsjtj ELSE '' END ) '十八',
    MAX(CASE a.F_r WHEN '19' THEN a.F_kqsjtj ELSE '' END ) '十九',
  MAX(CASE a.F_r WHEN '20' THEN a.F_kqsjtj ELSE '' END ) '二十',
    MAX(CASE a.F_r WHEN '21' THEN a.F_kqsjtj ELSE '' END ) '二十一',
    MAX(CASE a.F_r WHEN '22' THEN a.F_kqsjtj ELSE '' END ) '二十二',
  MAX(CASE a.F_r WHEN '23' THEN a.F_kqsjtj ELSE '' END ) '二十三',
    MAX(CASE a.F_r WHEN '24' THEN a.F_kqsjtj ELSE '' END ) '二十四',
  MAX(CASE a.F_r WHEN '25' THEN a.F_kqsjtj ELSE '' END ) '二十五',
    MAX(CASE a.F_r WHEN '26' THEN a.F_kqsjtj ELSE '' END ) '二十六',
  MAX(CASE a.F_r WHEN '27' THEN a.F_kqsjtj ELSE '' END ) '二十七',
    MAX(CASE a.F_r WHEN '28' THEN a.F_kqsjtj ELSE '' END ) '二十八',
  MAX(CASE a.F_r WHEN '29' THEN a.F_kqsjtj ELSE '' END ) '二十九',
    MAX(CASE a.F_r WHEN '30' THEN a.F_kqsjtj ELSE '' END ) '三十',
  MAX(CASE a.F_r WHEN '31' THEN a.F_kqsjtj ELSE '' END ) '三十一'
 FROM (select F_xm,F_gw,DATE_FORMAT(f_dkrq,'%Y%m') as F_yd, 
-- 使用case 语法判断日期是否为周末，打卡时间是否为迟到、早退
 case
 when WEEKDAY(F_dkrq) = 6 
 then "周日" 
 when WEEKDAY(F_dkrq) = 5 
 then "周六"  
 when (DATE_FORMAT(F_sbdksjaa,'%H:%i') > DATE_FORMAT( CURDATE( ) ,'09:00'))&&(DATE_FORMAT(f_xbdksjaa,'%H:%i') >= DATE_FORMAT( CURDATE( ) ,'18:00'))
 then concat( DATE_FORMAT( F_sbdksjaa, '%H:%i' ), '-', DATE_FORMAT( f_xbdksjaa, '%H:%i' ), '迟到' )
 WHEN (DATE_FORMAT(F_sbdksjaa,'%H:%i') <= DATE_FORMAT( CURDATE( ) ,'09:00'))&&(DATE_FORMAT(f_xbdksjaa,'%H:%i') < DATE_FORMAT( CURDATE( ) ,'18:00'))
 then concat( DATE_FORMAT( F_sbdksjaa, '%H:%i' ), '-', DATE_FORMAT( f_xbdksjaa, '%H:%i' ), '早退' )
 WHEN (DATE_FORMAT(F_sbdksjaa,'%H:%i') > DATE_FORMAT( CURDATE( ) ,'09:00'))&&(DATE_FORMAT(f_xbdksjaa,'%H:%i') < DATE_FORMAT( CURDATE( ) ,'18:00'))
 then concat( DATE_FORMAT( F_sbdksjaa, '%H:%i' ), '-', DATE_FORMAT( f_xbdksjaa, '%H:%i' ), '迟到早退')
 else concat( DATE_FORMAT( F_sbdksjaa, '%H:%i' ), '-', DATE_FORMAT( f_xbdksjaa, '%H:%i' ))   
 end as F_kqsjtj ,day(f_dkrq) as F_r FROM w_kqjcxxb)a
GROUP BY a.F_yd,a.F_xm,a.F_gw;

效果：