?? frm_rpt_kaoqinrpt.frm
字號:
.Refresh
End With
Me.Move 0, 0, width00 - 80, height00 - 80
'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
'Toolbar1_ButtonClick Toolbar1.Buttons(3)
Option1(0).Value = True
sFldString = GetSqlString()
DTPicker1(0).Value = DateSerial(Year(Date), Month(Date), 1)
DTPicker1(1).Value = Date
End Sub
Private Sub Form_Resize()
If Me.height < 2000 Then Exit Sub
Frame1.width = Me.width - 300
DataGrid1.Move 100, 1440, Frame1.width, Me.height - 1880
End Sub
' =====================================================
' =====================================================
Private Sub Form_Unload(Cancel As Integer)
mDB.KillDB
Set mDB = Nothing
End Sub
Private Sub Text1_DblClick()
If Text1.Text <> "" Then
strSQL = "select a.dptname as 部門,b.emplyid as 工號,b.emplyname as 姓名 from depart a,emply b where a.dptid=b.dptid and (a.dptname like '%" & Text1.Text & "%' or b.emplyid like '%" & Text1.Text & "%' or b.emplyname like '%" & Text1.Text & "%')"
Else
strSQL = "select dptname as 部門,emplyid as 工號,emplyname as 姓名 from depart a,emply b where a.dptid=b.dptid" '''' and dptname like %'" & Text1.Text & "'% or emplyid like %'" & Text1.Text & "'% or emplyname like %'" & Text1.Text & "'%"
End If
Set adoprimaryRS = mDB.adoprimaryRS(strSQL)
Set frmX = New NM_Bas
With frmX
.reload_datagrid adoprimaryRS
.Show vbModal
End With
Set frmX = Nothing
End Sub
Private Sub frmX_GG()
With frmX.DataGrid1
' Dim varBmk As Variant
' For Each varBmk In .SelBookmarks
' .Row = varBmk - 1
'' .Fields(2) = .Fields(2) + 23
' strSQL2 = "insert into NM_ZaGong(ID,iYear,iMonth,emplyid,emplyname) values(" & .Columns(0).Text & "," & Combo1(0).Text & "," & Combo1(1).Text & ",'" & .Columns(1).Text & "','" & .Columns(2).Text & "')"
''Debug.Print strSQL2
'
' mDB.ExecuteSQL (strSQL2)
' Next
Text1.Text = .Columns(1).Text
End With
Toolbar1_ButtonClick Toolbar1.Buttons(2)
Unload frmX
'Reload_DataGrid
End Sub
Private Sub Text1_GotFocus()
If Option1(1).Value = False Then
Option1(1).Value = True
End If
End Sub
Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
On Error GoTo Err1
Select Case Button.Index
Case 1
'
'If DataList1.BoundText = "" Then
'MsgBox "請選擇班組。"
'Exit Sub
'End If
'
'Dim sTmp As String
''Dim iPrice As Single
'With sForm
' .GetFindV "請錄入員工工號：", "員工工號："
' sTmp = .GetFindVar1
'' iPrice = .GetFindVar2
'End With
'
'If sTmp <> "" Then
'strSQL = "select emplyname from lz_emply2 where emplyid='" & sTmp & "'"
'Set adoprimaryRS = mDB.adoprimaryRS(strSQL)
'If adoprimaryRS.RecordCount Then
'Dim stmpE As String
' stmpE = adoprimaryRS.Fields("emplyname").Value
'
'strSQL = "insert into NM_ZaGong(ID,iYear,iMonth,emplyid,emplyname) values(" & DataList1.BoundText & "," & Combo1(0).Text & "," & Combo1(1).Text & ",'" & sTmp & "','" & stmpE & "')"
'mDB.ExecuteSQL strSQL
'DataList1_Click
'
'End If
'End If
Case 2
Dim strWhere As String
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 & "'"
strWhere = " 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
strWhere = " 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 & "'"
strWhere = " 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
'Else
' 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"
' strWhere = " where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' and wktmflag<>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"
' strWhere = " where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' and dptid=" & DataCombo1.BoundText & " and wktmflag<>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"
' strWhere = " where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' and emplyid='" & Text1.Text & "' and wktmflag<>0"
' ElseIf Option1(1).Value And Text1.Text = "" Then
' MsgBox "個人資料不能為空。"
' Exit Sub
' End If
'
'End If
'strSQL4 = "select emplyid,emplyname,sum(case when wktmrslt_flag=7 then 1 else 0 end) as count_work,sum(case when wktmrslt_flag=1 then 1 else 0 end) as count_notwork," & _
' "sum(case when wktmrslt_flag=4 or wktmrslt_flag=5 then 1 else 0 end) as count_leave,sum(case when wktmrslt_flag=3 then 1 else 0 end) as count_envection,sum(case when wktmrslt_flag=2 then 1 else 0 end) as count_different," & _
' "sum(case when latertime1+latertime2+latertime3+latertime4<>0 then 1 else 0 end) as count_later,sum(latertime1+latertime2+latertime3+latertime4) as count_latertime,sum(case when earlytime1+earlytime2+earlytime3+earlytime4<>0 then 1 else 0 end) as count_early,sum(earlytime1+earlytime2+earlytime3+earlytime4) as count_earlytime," & _
' "sum(datwktm) as count_dayhours,sum(standwktm) as count_standhours,sum(workwktm) as count_workwktm,sum(overwktm) as count_overrest from wktmrslt " & _
' " where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "' group by emplyid,emplyname"
'Debug.Print strSQL4
'strSQL4 = "select emplyid as 工號,emplyname as 姓名,sum(case when wktmrslt_flag=7 then 1 else 0 end) as 正常天,sum(case when wktmrslt_flag=1 then 1 else 0 end) as 異常天," & _
' "sum(case when wktmrslt_flag=4 or wktmrslt_flag=5 then 1 else 0 end) as 休息天,sum(case when wktmrslt_flag=3 then 1 else 0 end) as 出差天,sum(case when wktmrslt_flag=2 then 1 else 0 end) as 請假天," & _
' "sum(case when latertime1+latertime2+latertime3+latertime4>0 then 1 else 0 end) as 遲到次,sum(latertime1+latertime2+latertime3+latertime4) as 遲到分,sum(case when earlytime1+earlytime2+earlytime3+earlytime4>0 then 1 else 0 end) as 早退次,sum(earlytime1+earlytime2+earlytime3+earlytime4) as 早退分," & _
' "sum(datwktm) as 實際工時,sum(standwktm) as 標準工時,sum(workwktm) as 在崗工時,sum(overwktm) as 平時加班工時 from wktmrslt " & strWhere & " group by emplyid,emplyname"
strSQL4 = "select dptname as 部門,emplyid as 工號,emplyname as 姓名,sum(case when wktmrslt_flag=7 then 1 else 0 end) as 正常天,sum(case when wktmrslt_flag=1 then 1 else 0 end) as 異常天," & _
"sum(case when wktmrslt_flag=4 or wktmrslt_flag=5 then 1 else 0 end) as 休息天,sum(case when wktmrslt_flag=9 then 1 else 0 end) as 休日加班天,sum(case when wktmrslt_flag=3 then 1 else 0 end) as 出差天,sum(case when wktmrslt_flag=2 then 1 else 0 end) as 請假天," & _
"sum(case when latertime1+latertime2>0 then 1 else 0 end) as 遲到次,sum(latertime1+latertime2) as 遲到分,sum(case when earlytime1+earlytime2>0 then 1 else 0 end) as 早退次,sum(earlytime1+earlytime2) as 早退分," & _
"sum(datwktm) as 實際工時,sum(standwktm) as 標準工時,sum(workwktm) as 在崗工時,sum(overwktm) as 平時加班工時,sum(sunwktm) as 休日加班工時,sum(holidaysum) as 請假工時 from wktmrslt " & strWhere & " group by dptname,emplyid,emplyname"
'Debug.Print strSQL4
Set adoprimaryRS4 = mDB.adoprimaryRS(strSQL4)
With DataGrid1
.ClearFields
.DefColWidth = 900
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(3).width = 1000
'' .Columns(3).width = 0
' .Columns(3).Caption = "工號"
' .Columns(4).Caption = "姓名"
' .Columns(5).Caption = "評分"
' .Columns(6).Caption = "工作天數"
.Refresh
End With
Case 3
Screen.MousePointer = 11
'''ToExcel.ToExcel adoprimaryRS4
strWhere = " where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "'"
strSQL2 = "select dptname as 部門,emplyid as 工號,emplyname as 姓名,sum(case when wktmrslt_flag=7 then 1 else 0 end) as 正常天,sum(case when wktmrslt_flag=1 then 1 else 0 end) as 異常天," & _
"sum(case when wktmrslt_flag=4 or wktmrslt_flag=5 then 1 else 0 end) as 休息天,sum(case when wktmrslt_flag=9 then 1 else 0 end) as 休日加班天,sum(case when wktmrslt_flag=3 then 1 else 0 end) as 出差天,sum(case when wktmrslt_flag=2 then 1 else 0 end) as 請假天," & _
"sum(case when latertime1+latertime2>0 then 1 else 0 end) as 遲到次,sum(latertime1+latertime2) as 遲到分,sum(case when earlytime1+earlytime2>0 then 1 else 0 end) as 早退次,sum(earlytime1+earlytime2) as 早退分," & _
"sum(datwktm) as 實際工時,sum(overwktm) as 平時加班工時,sum(sunwktm) as 休日加班工時,sum(holidaysum) as 請假工時 from wktmrslt " & strWhere & " group by dptname,emplyid,emplyname order by dptname,emplyid"
Set adoPrimaryRS2 = mDB.adoprimaryRS(strSQL2)
Dim o As New SysForm.cFile
If o.FileExists(App.Path & "\kaoQin.xls") Then
ToExcel.ToExcel_noFld2 adoPrimaryRS2, App.Path & "\kaoQin.xls", "A3"
Else
ToExcel.ToExcel adoPrimaryRS2 ''', App.Path & "\kaoQin.xls", "A3"
End If
Screen.MousePointer = 0
Case 4
strWhere = " where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "'"
strSQL4 = "select emplyid as 工號,emplyname as 姓名," & _
"sum(case when latertime1+latertime2>0 then 1 else 0 end) as 遲到次,sum(case when earlytime1+earlytime2>0 then 1 else 0 end) as 早退次" & _
" from wktmrslt " & strWhere & " group by emplyid,emplyname having (sum(case when latertime1+latertime2>0 then 1 else 0 end)>0 or sum(case when earlytime1+earlytime2>0 then 1 else 0 end)>0)"
'Debug.Print strSQL4
Set adoprimaryRS4 = mDB.adoprimaryRS(strSQL4)
With DataGrid1
.ClearFields
.DefColWidth = 900
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 = 1000
'' .Columns(3).width = 0
' .Columns(3).Caption = "工號"
' .Columns(4).Caption = "姓名"
' .Columns(5).Caption = "評分"
' .Columns(6).Caption = "工作天數"
.Refresh
End With
Case 5
strWhere = " where caldate between '" & DTPicker1(0).Value & "' and '" & DTPicker1(1).Value & "'"
strSQL4 = "select emplyid as 工號,emplyname as 姓名," & _
"sum(case when nCount % 2<>0 then 1 else 0 end) as 刷卡次數異常" & _
" from wktmrslt " & strWhere & " group by emplyid,emplyname having sum(case when nCount % 2<>0 then 1 else 0 end)>0"
'Debug.Print strSQL4
Set adoprimaryRS4 = mDB.adoprimaryRS(strSQL4)
With DataGrid1
.ClearFields
.DefColWidth = 900
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 = 1000
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -