frm_rpt_kaoqinrpt.frm

考勤系统,智能判断刷卡异常,是一大型ERP系统的一个分支

        .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