📄 frm_rpt_kaoqinyc2.frm
字号:
'
'
'
'Else
'
' Check1(0).Value = 0
' Label3(0).Caption = "待审批"
' Check1(1).Value = 0
' Label3(3).Caption = "未结算"
' Toolbar1.Buttons(1).Enabled = True
' Toolbar1.Buttons(2).Enabled = True
' Toolbar1.Buttons(4).Enabled = True
'
' SpFlg = True
'
'
'End If
'
'strSQL4 = "select ID,iYear,iMonth,emplyid,emplyname,iFen,iDay from NM_ZaGong where iYear=" & Combo1(0).Text & " and iMonth=" & Combo1(1).Text
'Set adoprimaryRS4 = mDB.adoprimaryRS(strSQL4)
'
'
'Reload_DataGrid
'If Option2(0).Value Then
'strSQL4 = "select emplyid as 工号,emplyname as 姓名,caldate as 日期," & sFldString & "DesCrip as 出勤情况 from wktmrslt left join wkrslt on wktmrslt.wktmrslt_flag=wkrslt.ID where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' and QueRen<1"
' If Option1(0).Value And DataCombo1.Text = "" Then
' '''strSQL4 = "select emplyid as 工号,emplyname as 姓名,caldate as 日期," & sFldString & "wktmrslt_flag as 出勤情况 from wktmrslt where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "'"
' strSQL4 = "select emplyid as 工号,emplyname as 姓名,caldate as 日期," & sFldString & "DesCrip as 出勤情况 from wktmrslt left join wkrslt on wktmrslt.wktmrslt_flag=wkrslt.ID where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "'"
' ElseIf Option1(0).Value And DataCombo1.Text <> "" Then
' strSQL4 = "select emplyid as 工号,emplyname as 姓名,caldate as 日期," & sFldString & "DesCrip as 出勤情况 from wktmrslt left join wkrslt on wktmrslt.wktmrslt_flag=wkrslt.ID where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' and dptid=" & DataCombo1.BoundText
' ElseIf Option1(1).Value And Text1.Text <> "" Then
' strSQL4 = "select emplyid as 工号,emplyname as 姓名,caldate as 日期," & sFldString & "DesCrip as 出勤情况 from wktmrslt left join wkrslt on wktmrslt.wktmrslt_flag=wkrslt.ID where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' and emplyid='" & Text1.Text & "'"
' ElseIf Option1(1).Value And Text1.Text = "" Then
' MsgBox "个人资料不能为空。"
' Exit Sub
' End If
'Debug.Print strSQL4
'Else
strSQL4 = "select emplyid as 工号,emplyname as 姓名,caldate as 日期,QueRen," & sFldString & "DesCrip as 出勤情况 from wktmrslt left join wkrslt on wktmrslt.wktmrslt_flag=wkrslt.ID where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' and (wktmflag=0 or latertime1>0 or earlytime1>0 or latertime2>0 or earlytime2>0) and QueRen>0" ''' or isnull(nCount,0)%2<>0) "
' If Option1(0).Value And DataCombo1.Text = "" Then
' strSQL4 = "select emplyid as 工号,emplyname as 姓名,caldate as 日期," & sFldString & "DesCrip as 出勤情况 from wktmrslt left join wkrslt on wktmrslt.wktmrslt_flag=wkrslt.ID where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' and (wktmflag=0 or latertime1>0 or earlytime1>0 or latertime2>0 or earlytime2>0)" ''' or isnull(nCount,0)%2<>0) "
' ElseIf Option1(0).Value And DataCombo1.Text <> "" Then
' strSQL4 = "select emplyid as 工号,emplyname as 姓名,caldate as 日期," & sFldString & "DesCrip as 出勤情况 from wktmrslt left join wkrslt on wktmrslt.wktmrslt_flag=wkrslt.ID where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' and dptid=" & DataCombo1.BoundText & " and (wktmflag=0 or latertime1>0 or earlytime1>0 or latertime2>0 or earlytime2>0)" ''' or isnull(nCount,0)%2<>0) "
' ElseIf Option1(1).Value And Text1.Text <> "" Then
' strSQL4 = "select emplyid as 工号,emplyname as 姓名,caldate as 日期," & sFldString & "DesCrip as 出勤情况 from wktmrslt left join wkrslt on wktmrslt.wktmrslt_flag=wkrslt.ID where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' and emplyid='" & Text1.Text & "' and (wktmflag=0 or latertime1>0 or earlytime1>0 or latertime2>0 or earlytime2>0)" ''' or isnull(nCount,0)%2<>0) "
' ElseIf Option1(1).Value And Text1.Text = "" Then
' MsgBox "个人资料不能为空。"
' Exit Sub
' End If
'End If
'Debug.Print strSQL4
Set adoprimaryRS4 = mDB.adoprimaryRS(strSQL4)
With DataGrid1
.ClearFields
.DefColWidth = 900
Set .DataSource = adoprimaryRS4
' .Columns(0).width = 0
' .Columns(1).width = 0
.Columns(2).width = 1000
.Columns(3).width = 0
.Refresh
End With
Case 4
Screen.MousePointer = 11
ToExcel.ToExcel adoprimaryRS4
Screen.MousePointer = 0
Case 5
Unload Me
End Select
Exit Sub
Err1:
MsgBox Err.Number & "," & Err.Description
End Sub
Private Sub reload_datagrid()
'strSQL4 = "select ID,iYear,iMonth,emplyid,emplyname,iFen,iDay from NM_ZaGong where iYear=" & Combo1(0).Text & " and iMonth=" & Combo1(1).Text & " and ID=" & DataList1.BoundText
'Set adoprimaryRS4 = mDB.adoprimaryRS(strSQL4)
'Debug.Print strSQL4
With DataGrid1
.ClearFields
Set .DataSource = adoprimaryRS4
.Columns(0).DataField = "ID"
.Columns(1).DataField = "iYear"
.Columns(2).DataField = "iMonth"
.Columns(3).DataField = "emplyID"
.Columns(4).DataField = "emplyName"
.Columns(5).DataField = "iFen"
.Columns(6).DataField = "iDay"
.Columns(3).Locked = True
.Columns(4).Locked = True
.Columns(0).width = 0
.Columns(1).width = 0
.Columns(2).width = 0
' .Columns(3).width = 0
.Columns(3).Caption = "工号"
.Columns(4).Caption = "姓名"
.Columns(5).Caption = "评分"
.Columns(6).Caption = "工作天数"
.Refresh
End With
If SpFlg Then
DataGrid1.AllowUpdate = True
Else
DataGrid1.AllowUpdate = False
End If
End Sub
Private Function GetSqlString() As String
Dim mDB As mDB
Dim strSQL As String
Dim adoprimaryRS As ADODB.Recordset
Dim strSQL2 As String
Dim adoPrimaryRS2 As ADODB.Recordset
Set mDB = New mDB
mDB.InitDB_RY strconnDR
Dim sTmp As String
strSQL = "select * from wktmrslt_report"
Set adoprimaryRS = mDB.adoprimaryRS(strSQL)
If adoprimaryRS.RecordCount Then
With adoprimaryRS
If .Fields("wktmbg1").Value Then
'sTmp = sTmp & "wktmbg1 as 上班时间1,"
sTmp = sTmp & "(CASE WHEN wktmbg1 < 1 THEN '' ELSE substring(cast(wktmbg1 as char(22)),11,6) end) as 上班时间1,"
End If
If .Fields("drawtmbg1").Value Then
'sTmp = sTmp & "drawtmbg1 as 上班刷卡1,"
sTmp = sTmp & "(case when drawtmbg1<1 then '' else substring(cast(drawtmbg1 as char(22)),11,6) end) as 上班刷卡1,"
End If
If .Fields("latertime1").Value Then
sTmp = sTmp & "latertime1 as 迟到时间1,"
End If
If .Fields("wktmbg1dec").Value Then
sTmp = sTmp & "wktmbg1dec as 上班描述1,"
End If
If .Fields("wktmend1").Value Then
'sTmp = sTmp & "wktmend1 as 下班时间1,"
sTmp = sTmp & "(CASE WHEN wktmend1 < 1 THEN '' ELSE substring(cast(wktmend1 as char(22)),11,6) end) as 下班时间1,"
End If
If .Fields("drawtmend1").Value Then
'sTmp = sTmp & "drawtmend1 as 下班刷卡1,"
sTmp = sTmp & "(CASE WHEN drawtmend1 < 1 THEN '' ELSE substring(cast(drawtmend1 as char(22)),11,6) end) as 下班刷卡1,"
End If
If .Fields("earlytime1").Value Then
sTmp = sTmp & "earlytime1 as 早退时间1,"
End If
If .Fields("wktmend1dec").Value Then
sTmp = sTmp & "wktmend1dec as 下班描述1,"
End If
If .Fields("wktmbg2").Value Then
'sTmp = sTmp & "wktmbg2 as 上班时间2,"
sTmp = sTmp & "(CASE WHEN wktmbg2 < 1 THEN '' ELSE substring(cast(wktmbg2 as char(22)),11,6) end) as 上班时间2,"
End If
If .Fields("drawtmbg2").Value Then
'sTmp = sTmp & "drawtmbg2 as 上班刷卡2,"
sTmp = sTmp & "(CASE WHEN drawtmbg2 < 1 THEN '' ELSE substring(cast(drawtmbg2 as char(22)),11,6) end) as 上班刷卡2,"
End If
If .Fields("latertime2").Value Then
sTmp = sTmp & "latertime2 as 迟到时间2,"
End If
If .Fields("wktmbg2dec").Value Then
sTmp = sTmp & "wktmbg2dec as 上班描述2,"
End If
If .Fields("wktmend2").Value Then
'sTmp = sTmp & "wktmend2 as 下班时间2,"
sTmp = sTmp & "(CASE WHEN wktmend2 < 1 THEN '' ELSE substring(cast(wktmend2 as char(22)),11,6) end) as 下班时间2,"
End If
If .Fields("drawtmend2").Value Then
'sTmp = sTmp & "drawtmend2 as 下班刷卡2,"
sTmp = sTmp & "(CASE WHEN drawtmend2 < 1 THEN '' ELSE substring(cast(drawtmend2 as char(22)),11,6) end) as 下班刷卡2,"
End If
If .Fields("earlytime2").Value Then
sTmp = sTmp & "earlytime2 as 早退时间2,"
End If
If .Fields("wktmend2dec").Value Then
sTmp = sTmp & "wktmend2dec as 下班描述2,"
End If
If .Fields("wktmbg3").Value Then
'sTmp = sTmp & "wktmbg3 as 上班时间3,"
sTmp = sTmp & "(CASE WHEN wktmbg3 < 1 THEN '' ELSE substring(cast(wktmbg3 as char(22)),11,6) end) as 上班时间3,"
End If
If .Fields("drawtmbg3").Value Then
'sTmp = sTmp & "drawtmbg3 as 上班刷卡3,"
sTmp = sTmp & "(CASE WHEN drawtmbg3 < 1 THEN '' ELSE substring(cast(drawtmbg3 as char(22)),11,6) end) as 上班刷卡3,"
End If
If .Fields("latertime3").Value Then
sTmp = sTmp & "latertime3 as 迟到时间3,"
End If
If .Fields("wktmbg3dec").Value Then
sTmp = sTmp & "wktmbg3dec as 上班描述3,"
End If
If .Fields("wktmend3").Value Then
'sTmp = sTmp & "wktmend3 as 下班时间3,"
sTmp = sTmp & "(CASE WHEN wktmend3 < 1 THEN '' ELSE substring(cast(wktmend3 as char(22)),11,6) end) as 下班时间3,"
End If
If .Fields("drawtmend3").Value Then
'sTmp = sTmp & "drawtmend3 as 下班刷卡3,"
sTmp = sTmp & "(CASE WHEN drawtmend3 < 1 THEN '' ELSE substring(cast(drawtmend3 as char(22)),11,6) end) as 下班刷卡3,"
End If
If .Fields("earlytime3").Value Then
sTmp = sTmp & "earlytime3 as 早退时间3,"
End If
If .Fields("wktmend3dec").Value Then
sTmp = sTmp & "wktmend3dec as 下班描述3,"
End If
If .Fields("wktmbg4").Value Then
'sTmp = sTmp & "wktmbg4 as 上班时间4,"
sTmp = sTmp & "(CASE WHEN wktmbg4 < 1 THEN '' ELSE substring(cast(wktmbg4 as char(22)),11,6) end) as 上班时间4,"
End If
If .Fields("drawtmbg4").Value Then
'sTmp = sTmp & "drawtmbg4 as 上班刷卡4,"
sTmp = sTmp & "(CASE WHEN drawtmbg4 < 1 THEN '' ELSE substring(cast(drawtmbg4 as char(22)),11,6) end) as 上班刷卡4,"
End If
If .Fields("latertime4").Value Then
sTmp = sTmp & "latertime4 as 迟到时间4,"
End If
If .Fields("wktmbg4dec").Value Then
sTmp = sTmp & "wktmbg4dec as 上班描述4,"
End If
If .Fields("wktmend4").Value Then
'sTmp = sTmp & "wktmend4 as 下班时间4,"
sTmp = sTmp & "(CASE WHEN wktmend4 < 1 THEN '' ELSE substring(cast(wktmend4 as char(22)),11,6) end) as 下班时间4,"
End If
If .Fields("drawtmend4").Value Then
'sTmp = sTmp & "drawtmend4 as 下班刷卡4,"
sTmp = sTmp & "(CASE WHEN drawtmend4 < 1 THEN '' ELSE substring(cast(drawtmend4 as char(22)),11,6) end) as 下班刷卡4,"
End If
If .Fields("earlytime4").Value Then
sTmp = sTmp & "earlytime4 as 早退时间4,"
End If
If .Fields("wktmend4dec").Value Then
sTmp = sTmp & "wktmend4dec as 下班描述4,"
End If
If .Fields("bgnovertime").Value Then
'sTmp = sTmp & "bgnovertime as 开始加班,"
sTmp = sTmp & "(CASE WHEN bgnovertime < 1 THEN '' ELSE substring(cast(bgnovertime as char(22)),11,6) end) as 开始加班,"
End If
If .Fields("endovertime").Value Then
'sTmp = sTmp & "endovertime as 结束加班,"
sTmp = sTmp & "(CASE WHEN endovertime < 1 THEN '' ELSE substring(cast(endovertime as char(22)),11,6) end) as 结束加班,"
End If
If .Fields("nCount").Value Then
sTmp = sTmp & "nCount as 刷卡次数,"
End If
If .Fields("overwktm").Value Then
sTmp = sTmp & "overwktm as 平时加班工时,"
End If
If .Fields("sunwktm").Value Then
sTmp = sTmp & "sunwktm as 休日加班工时,"
End If
If .Fields("holidaywktm").Value Then
sTmp = sTmp & "holidaywktm as 假日加班工时,"
End If
If .Fields("wktmname").Value Then
sTmp = sTmp & "wktmname as 班次名称,"
End If
If .Fields("memo").Value Then
sTmp = sTmp & "memo as 备注,"
End If
If .Fields("standwktm").Value Then
sTmp = sTmp & "standwktm as 标准工时,"
End If
If .Fields("datwktm").Value Then
sTmp = sTmp & "datwktm as 实际工时,"
End If
If .Fields("workwktm").Value Then
sTmp = sTmp & "workwktm as 在岗工时,"
End If
If .Fields("evectionsum").Value Then
sTmp = sTmp & "evectionsum as 出差工时,"
End If
If .Fields("wktmflag").Value Then
'sTmp = sTmp & "wktmflag as 是否异常,"
sTmp = sTmp & "case when wktmflag<>0 then '正常' else '异常' end as 是否异常,"
End If
If .Fields("holidaysum").Value Then
sTmp = sTmp & "holidaysum as 请假工时,"
End If
End With
'If sTmp <> "" Then
'If Right(sTmp, 1) = "," Then
'sTmp = Left(sTmp, Len(sTmp) - 1)
'End If
'End If
GetSqlString = sTmp
Else
GetSqlString = ""
End If
mDB.KillDB
Set mDB = Nothing
'strSQL = "select emplyid as ,emplyname as ,caldate as ," & sTmp & "wktmrslt_flag as from wktmrslt where "
End Function
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -