📄 kq_sckqtj_do.asp
字号:
<!--#include file="../inc/Secure.asp"-->
<!--#include file="../inc/conn.asp"-->
<!--#include file="../inc/Const.asp"-->
<%Response.Buffer = true %>
<%server.ScriptTimeout = 60000 %>
<%
dim stjny
stjny=Request.form("tjny")
if stjny="" then
Response.End
end if
%>
<!-- #include file="../inc/pubfun_a.inc" -->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link rel="stylesheet" href="../css/main.css" type=text/css>
<title>生成月考勤统计数据</title>
</head>
<body>
<div class="TitleBar">生成月考勤统计数据</div>
<%
Dim rs,rskq,rssave
set rs=server.CreateObject("ADODB.recordset")
set rskq=server.CreateObject("ADODB.recordset")
set rssave=server.CreateObject("ADODB.recordset")
dim sygbh
sygbh=session("LoginID")
dim skqyqsrq,skqyqsbz,scdsj1,scdsj2,scdsj3,ssbsj,sxbsj
rs.Open "select * from kq_csb",oConn,1,1
if rs.EOf then
rs.Close
set rs=nothing
oConn.close
set oConn=nothing
Response.Write "未定义考勤参数"
Response.End
end if
skqyqsrq=rs("kqyqsrq")
skqyqsbz=rs("kqyqsbz")
scdsj1=rs("cdsj1")
scdsj2=rs("cdsj2")
scdsj3=rs("cdsj3")
ssbsj=rs("sbsj")
sxbsj=rs("xbsj")
rs.Close
'计算统计起止日期
dim stjksrq,stjzzrq
if cstr(skqyqsbz)="0" then
if isdate(stjny & "-" & skqyqsrq) then
stjzzrq=formatdatetime_activenew(stjny & "-" & skqyqsrq,"yyyy-mm-dd")
stjzzrq=formatdatetime_activenew(dateadd("d",-1,cdate(stjzzrq)),"yyyy-mm-dd")
else '为统计月最后一天
stjzzrq=formatdatetime_activenew(dateadd("m",1,cdate(stjny)),"yyyy-mm-01")
stjzzrq=formatdatetime_activenew(dateadd("d",-1,cdate(stjzzrq)),"yyyy-mm-dd")
end if
stjksrq=formatdatetime_activenew(dateadd("m",-1,cdate(stjzzrq)),"yyyy-mm-dd")
stjksrq=formatdatetime_activenew(dateadd("d",1,cdate(stjksrq)),"yyyy-mm-dd")
else
if isdate(stjny & "-" & skqyqsrq) then
stjksrq=formatdatetime_activenew(stjny & "-" & skqyqsrq,"yyyy-mm-dd")
else '为统计月最后一天
stjksrq=formatdatetime_activenew(dateadd("m",1,cdate(stjny)),"yyyy-mm-01")
stjksrq=formatdatetime_activenew(dateadd("d",-1,cdate(stjksrq)),"yyyy-mm-dd")
end if
stjzzrq=formatdatetime_activenew(dateadd("m",1,cdate(stjksrq)),"yyyy-mm-dd")
stjzzrq=formatdatetime_activenew(dateadd("d",-1,cdate(stjzzrq)),"yyyy-mm-dd")
end if
'Response.Write stjksrq & "<br>"
'Response.Write stjzzrq & "<br>"
Response.flush
dim sxxrlb
'取得休息日列表
sxxrlb=","
rs.Open "select xxr from kq_xxrb where CAST(xxr AS DATETIME)>='" & stjksrq & "' and CAST(xxr AS DATETIME)<='" & stjzzrq & "'",oConn,1,1
do while not rs.EOf
sxxrlb=sxxrlb & formatdatetime_activenew(rs("xxr"),"yyyy-mm-dd") & ","
rs.MoveNext
loop
rs.Close
'Response.Write sxxrlb & "<br>"
%>
<%
dim ajb(),i
redim ajb(1,0) '存放假别字典，ajb(0,0)存放假别代码,ajb(1,0)存放请假次数
rs.Open "select * from KQ_JBZD",oConn,1,1
redim ajb(2,rs.RecordCount)
Response.Write " 开始统计"& stjny & "的考勤数据，请不要中断执行....<br>"
Response.flush
for i=1 to rs.RecordCount
ajb(0,i)=rs("dm") & ""
ajb(1,i)=0
rs.MoveNext
next
rs.Close
oConn.begintrans
'删除已有数据
oConn.execute "delete from kq_ytjb where ny='" & stjny & "'"
rssave.Open "select * from kq_ytjb where 1>2",oConn,3,2
'对所有员工统计
rs.Open "select id from tbioaUser",oConn,1,1
for i=1 to rs.RecordCount
Response.Write " 共" & rs.RecordCount & "人,统计第" & i & "人......"
Response.flush
'统计当前员工的月考勤统计数据
'考勤数据
dim scd1cs,scd2cs,scd3cs,szt1cs,szt2cs,szt3cs,swqdcs,swqtcs,scqts,sqqts
dim ssc '时间差
dim bqdyx,bqtyx
dim sloopdate
scd1cs=0
scd2cs=0
scd3cs=0
szt1cs=0
szt2cs=0
szt3cs=0
swqdcs=0
swqtcs=0
scqts=0
sqqts=0
sloopdate=stjksrq
do while sloopdate<=stjzzrq
'休息日不统计
if instr(sxxrlb,"," & sloopdate & ",")=0 then
rskq.open "select * from kq_kqb where kqrq='" & sloopdate & "' and ygbh=" & rs("id"),oConn,1,1
if rskq.EOf then
'没有当日考勤则缺勤、未签到、未签退各＋1
sqqts=sqqts+1
else
bqdyx=false
bqtyx=false
'有签到时间则判是否有效
if rskq("qdsj") & ""<>"" then
ssc=datediff("n",cdate(sloopdate & " " & ssbsj),cdate(sloopdate & " " & rskq("qdsj")))
if cdbl(ssc)>cdbl(scdsj3) then '此次签到无效
else '有效
bqdyx=true
if cdbl(ssc)>cdbl(scdsj2) then
scd3cs=scd3cs+1
elseif cdbl(ssc)>cdbl(scdsj1) then
scd2cs=scd2cs+1
elseif cdbl(ssc)>0 and cdbl(ssc)<=cdbl(scdsj1) then
scd1cs=scd1cs+1
end if
end if
end if
'有签退时间则判是否有效
if rskq("qtsj") & ""<>"" then
ssc=datediff("n",cdate(sloopdate & " " & rskq("qtsj")),cdate(sloopdate & " " & sxbsj))
if cdbl(ssc)>cdbl(scdsj3) then '此次签退无效
else '有效
bqtyx=true
if cdbl(ssc)>cdbl(scdsj2) then
szt3cs=szt3cs+1
elseif cdbl(ssc)>cdbl(scdsj1) then
szt2cs=szt2cs+1
elseif cdbl(ssc)>0 and cdbl(ssc)<=cdbl(scdsj1) then
szt1cs=szt1cs+1
end if
end if
end if
'有有效签到签退则记出勤
if bqtyx and bqdyx then
scqts=scqts+1
elseif bqtyx and not bqdyx then
swqdcs=swqdcs+1
elseif not bqtyx and bqdyx then
swqtcs=swqtcs+1
end if
if not bqdyx and not bqtyx then
sqqts=sqqts+1
end if
end if 'end无当日考勤记录
rskq.Close
end if 'end休息日不统计
sloopdate=formatdatetime_activenew(dateadd("d",1,cdate(sloopdate)),"yyyy-mm-dd")
loop
'保存
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-1
rssave("sj")=scqts
rssave.Update
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-2
rssave("sj")=sqqts
rssave.Update
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-3
rssave("sj")=scd1cs
rssave.Update
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-4
rssave("sj")=scd2cs
rssave.Update
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-5
rssave("sj")=scd3cs
rssave.Update
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-6
rssave("sj")=szt1cs
rssave.Update
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-7
rssave("sj")=szt2cs
rssave.Update
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-8
rssave("sj")=szt3cs
rssave.Update
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-10
rssave("sj")=swqdcs
rssave.Update
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-11
rssave("sj")=swqtcs
rssave.Update
'Response.Write "<br>" & rs("id") & " 出勤" & scqts & ",缺勤" & sqqts & ",未签到" & swqdcs & ",未签退" & swqtcs & ",迟到1 " & scd1cs & ",迟到2 " & scd2cs & ",迟到3 " & scd3cs & ",早退1 " & szt1cs & ",早退2 " & szt2cs & ",早退3 " & szt3cs & "<br>"
'统计加班时间
dim sjbsj
sjbsj=0
rskq.Open "select sum(sjgs) from oa_jbmx a, oa_sqjbxx b " _
& " where b.sqr=" & rs("id") & " " _
& "and substring(sjkssj,1,10)>='" & stjksrq & "' " _
& "and substring(sjkssj,1,10)<='" & stjzzrq & "' " _
& "and a.sqxh=b.sqxh " _
& "and b.spbz='1' and a.hzqk='3'" _
,oConn,1,1
if not rskq.EOf then
if rskq(0) & ""<>"" then
sjbsj=rskq(0)
end if
end if
rskq.Close
'保存
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=-9
rssave("sj")=sjbsj
rssave.Update
'Response.Write "<br>加班" & sjbsj & "<br>"
'统计各种请假次数
dim j
for j= 1 to ubound(ajb,2)
ajb(1,j)=0
rskq.Open "select count(a.sqxh) from kq_qjd a, oa_sqjbxx b " _
& " where b.sqr=" & rs("id") & " " _
& "and substring(sjkssj,1,10)>='" & stjksrq & "' " _
& "and substring(sjkssj,1,10)<='" & stjzzrq & "' " _
& "and a.sqxh=b.sqxh " _
& "and a.jb=" & ajb(0,j) & " " _
& "and b.spbz='1' and a.xjqk='3'" _
,oConn,1,1
if not rskq.EOf then
if rskq(0) & ""<>"" then
ajb(1,j)=rskq(0)
end if
end if
rskq.Close
rssave.AddNew
rssave("ny")=stjny
rssave("ygbh")=rs("id")
rssave("jb")=ajb(0,j)
rssave("cs")=ajb(1,j)
rssave.Update
'Response.Write "假别" & ajb(0,j) & "-" & ajb(1,j) & ""
next
Response.Write "完成<br>"
Response.flush
rs.MoveNext
next
rs.Close
rssave.Close
oConn.committrans
%>
<%
set rs=nothing
oConn.close
set oConn=nothing
%>
<script language=vbscript>
<!--
msgbox "成功生成月考勤统计数据",vbinformation,"生成月考勤统计数据"
location.href="kq_sckqtj.asp"
//-->
</script>
</body>
</html> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -