考勤消费管理系统定制
考勤消费管理系统定制
一、定制需求简述：
1、设置消费系统就餐3个时间段。
2、分考勤机与消费机，记录保存到不同表中。
3、自动同步人员信息。
4、进行定制表格统计
二、实现方法：
1、在考勤消费系统设置中设置相应参数。
2、新建表格 CheckInOut1 存放消费记录
3、默认机器号 小于 200的是考勤机，其余就是消费机
4、新建一查询窗口，提供定制查询。
三、代码Changelog：
主要代码如下：
select U.BADGENUMBER,U.NAME,U.TITLE,U.F_Company,U.F_Department,FCount1,FCount2,FCount3,FCount
from
(
select USERID, sum(FTime1) as FCount1, sum(FTime2) as FCount2, sum(FTime3) as FCount3, sum(FTime1+FTime2+FTime3) as FCount
from
(
Select USERID, COUNT(*) as FTime1, 0 as FTime2, 0 as FTime3
from CHECKINOUT
where (CONVERT(varchar(12), CHECKTIME, 108) >= '@TimeZone1Begin' and CONVERT(varchar(12), CHECKTIME, 108) <= '@TimeZone1End')
and (CHECKTIME between '@SearchBeginTime' and '@SearchEndTime')
group by USERID
union
Select USERID, 0 as FTime1, COUNT(*) as FTime2, 0 as FTime3
from CHECKINOUT
where (CONVERT(varchar(12), CHECKTIME, 108) >= '@TimeZone2Begin' and CONVERT(varchar(12), CHECKTIME, 108) <= '@TimeZone2End')
and (CHECKTIME between '@SearchBeginTime' and '@SearchEndTime')
group by USERID
union
Select USERID, 0 as FTime1, 0 as FTime2, COUNT(*) as FTime3
from CHECKINOUT
where (CONVERT(varchar(12), CHECKTIME, 108) >= '@TimeZone3Begin' @TimeZone3Condition CONVERT(varchar(12), CHECKTIME, 108) <=
'@TimeZone3End')
and (CHECKTIME between '@SearchBeginTime' and '@SearchEndTime')
group by USERID
union
Select USERID, 0 as FTime1, 0 as FTime2, 0 as FTime3
from userinfo
) T
group by USERID
) TT
right join UserInfo U
on TT.USERID=U.USERID