企业微信打卡统计员工考勤

【第一步】调用企业微信的api接口获取当天的打卡记录

（获取的打卡记录是根据自己提交的时间戳来获取的，我只获取当天的打卡记录，是因为设置了定时器，每天11点自动调用接口插入到数据库）

企业微信api地址

获取数据并分析

分析每一条打卡记录对应什么状态，迟到，早退，未打卡，未连接wifi打卡，外出打卡等。我这里获取了用户id，打卡类型，异常类型，打卡时间，打卡地点，打卡地点详情，打卡wifi名称，打卡规则名称，并且做了判断数据库只存在一个员工当天2条打卡记录，取最早打卡和最晚打卡。

最后呈现的效果如下

由于数据量大，计算有点复杂，导致加载时间需要15s左右，数据由sql存储过程计算获得。

sql代码如下

USE [easyOA]
GO
/****** Object:  StoredProcedure [dbo].[_rqv_p_get_attabel_by_page]    Script Date: 2020/8/13 10:33:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER proc [dbo].[_rqv_p_get_attabel_by_page]
	@at_date datetime,
	@page int,
	@rows int,
	@rowcount int output	

as
begin
	if object_id('tempdb..#tmp_lv_re') is not null
begin
    drop table #tmp_lv_re
end
create table #tmp_lv_re(
	at_userid nvarchar(100),
	zc_day int,
	at_leavetime float,
	at_leavedata nvarchar(max),
	w_sdk    int,
	w_xdk    int,
	w_sxdk   int,
	at_cdx	 int,
	at_cdd	 int,
	at_ztx	 int,
	at_ztd	 int,
	a1_1 datetime,
	s1_1 int, 
	a1_2 datetime,
	s1_2 int,
	a2_1 datetime,
	s2_1 int,
	a2_2 datetime,
	s2_2 int,
	a3_1 datetime,
	s3_1 int,
	a3_2 datetime,
	s3_2 int,
	a4_1 datetime,
	s4_1 int,
	a4_2 datetime,
	s4_2 int,
	a5_1 datetime,
	s5_1 int,
	a5_2 datetime,
	s5_2 int,
	a6_1 datetime,
	s6_1 int,
	a6_2 datetime,
	s6_2 int,
	a7_1 datetime,
	s7_1 int,
	a7_2 datetime,
	s7_2 int,
	a8_1 datetime,
	s8_1 int,
	a8_2 datetime,
	s8_2 int,
	a9_1 datetime,
	s9_1 int,
	a9_2 datetime,
	s9_2 int,
	a10_1 datetime,
	s10_1 int,
	a10_2 datetime,
	s10_2 int,
	a11_1 datetime,
	s11_1 int,
	a11_2 datetime,
	s11_2 int,
	a12_1 datetime,
	s12_1 int,
	a12_2 datetime,
	s12_2 int,
	a13_1 datetime,
	s13_1 int,
	a13_2 datetime,
	s13_2 int,
	a14_1 datetime,
	s14_1 int,
	a14_2 datetime,
	s14_2 int,
	a15_1 datetime,
	s15_1 int,
	a15_2 datetime,
	s15_2 int,
	a16_1 datetime,
		s16_1 int,
	a16_2 datetime,
		s16_2 int,
	a17_1 datetime,
		s17_1 int,
	a17_2 datetime,
		s17_2 int,
	a18_1 datetime,
	s18_1 int,
	a18_2 datetime,
	s18_2 int,
	a19_1 datetime,
	s19_1 int,
	a19_2 datetime,
	s19_2 int,
	a20_1 datetime,
	s20_1 int,
	a20_2 datetime,
	s20_2 int,
	a21_1 datetime,
	s21_1 int,
	a21_2 datetime,
	s21_2 int,
	a22_1 datetime,
	s22_1 int,
	a22_2 datetime,
	s22_2 int,
	a23_1 datetime,
	s23_1 int,
	a23_2 datetime,
	s23_2 int,
	a24_1 datetime,
	s24_1 int,
	a24_2 datetime