frmreport.frm

考勤机管理软件,用于统计某段时间某个部门或者某个员工在某段内迟到与早退次数.

                
                rstGrid.Fields("员工卡号") = EmployeeInfo(lEmpRow).CardID
                rstGrid.Fields("员工名称") = EmployeeInfo(lEmpRow).EmployeeName
                
                
                
                
                '===========获得班次同时获得加班次数================
                rstGrid.Fields("加班次数") = g_AddClass
    
                '==============================================================================
                Do While Not rstExec.EOF
                
                    If (EmployeeInfo(lEmpRow).CardID <> rstExec.Fields("InCode") _
                       Or bTime <> rstExec.Fields("InDate")) Then Exit Do
                        
                    If iCardCount = 0 Or iCardCount = -1 Then
                        GoTo NextRow:
                    End If
                    
                    
                    
                    iTimePos = GetTimePos(rstExec.Fields("InTime"))
                    
                    '==========返回零是班次时间出错
                    If iTimePos = -1 Then
                        rstGrid.Fields("备注") = "时间段设置有冲突!"
                    
                    '==========返回奇数是上班时间
                    ElseIf iTimePos Mod 2 = 1 Then
                        
                        
                         
                            '===========如果该位置为空，则将时间直接放入
                            If IsNull(rstGrid.Fields("时间" & CStr(iTimePos))) Or _
                                Trim(rstGrid.Fields("时间" & CStr(iTimePos))) = "" Then
                                
                                rstGrid.Fields("时间" & CStr(iTimePos)) = rstExec.Fields("InTime")
                                
                            Else
                            
                                lTimeState = GetTimeState(rstExec.Fields("InTime"), iTimePos, 0, 0)
                                If lTimeState = 1 Or lTimeState = 2 Then
                                
                                '===========如果前则为满，后则为空，则放入后则。
                                    If IsNull(rstGrid.Fields("时间" & CStr(iTimePos) + 1)) Or _
                                        Trim(rstGrid.Fields("时间" & CStr(iTimePos) + 1)) = "" Then
                                        
                                        rstGrid.Fields("时间" & CStr(iTimePos) + 1) = rstExec.Fields("InTime")
                                    Else
                                        '=======如果前，后则都为满，把后则移置前则，把刷卡时间放入后则。
                                        rstGrid.Fields("时间" & CStr(iTimePos)) = rstGrid.Fields("时间" & CStr(iTimePos) + 1)
                                        rstGrid.Fields("时间" & CStr(iTimePos) + 1) = rstExec.Fields("InTime")
                                    End If
                                
                                Else
                                    rstGrid.Fields("时间" & CStr(iTimePos)) = rstExec.Fields("InTime")
                                End If
                                
                            End If
    
            '
            
                    '==========返回偶数是下班时间
                    ElseIf iTimePos Mod 2 = 0 Then
                        '===========如果前则为空，则放入前则。
                        
                        
                        
                        
                            If IsNull(rstGrid.Fields("时间" & CStr(iTimePos) - 1)) Or _
                                Trim(rstGrid.Fields("时间" & CStr(iTimePos) - 1)) = "" Then
                                
                                If IsNull(rstGrid.Fields("时间" & CStr(iTimePos))) Or _
                                    Trim(rstGrid.Fields("时间" & CStr(iTimePos))) = "" Then
                                
                                    rstGrid.Fields("时间" & CStr(iTimePos)) = rstExec.Fields("InTime")
                                Else
                                
                                    If GetTimeState(rstGrid.Fields("时间" & CStr(iTimePos)), iTimePos, 0, 0) <> 0 Then
                                    
                                        rstGrid.Fields("时间" & CStr(iTimePos) - 1) = rstGrid.Fields("时间" & CStr(iTimePos))
                                        rstGrid.Fields("时间" & CStr(iTimePos)) = rstExec.Fields("InTime")
                                        
    '                                Else
    '                                     rstGrid.Fields("时间" & CStr(iTimePos)) = rstExec.Fields("InTime")
                                    End If
                                
                                End If
                                
                            Else
                                '========如果当前为满，判断当前格内的值是否原本属于前则，如果是，那把该前移。
                                If Trim(rstGrid.Fields("时间" & CStr(iTimePos))) <> "" Then
                                    iTimePos2 = GetTimePos(rstGrid.Fields("时间" & CStr(iTimePos)))
                                    If iTimePos2 = iTimePos - 1 Then
                                        rstGrid.Fields("时间" & CStr(iTimePos) - 1) = rstGrid.Fields("时间" & CStr(iTimePos))
                                        rstGrid.Fields("时间" & CStr(iTimePos)) = rstExec.Fields("InTime")
                                    End If
                                Else
                                    rstGrid.Fields("时间" & CStr(iTimePos)) = rstExec.Fields("InTime")
                                End If
                                
                            End If
                        
    
                        
                    End If
                    
NextRow:
                    rstExec.MoveNext
                Loop
                '==============================================================================
                If iCardCount = 0 Or iCardCount = -1 Then
                    GoTo NextDo
                End If
            
                
                '======循环至刷卡次数
                Dim intField As Integer
                For intField = 1 To iCardCount Step 2
                
                    '=========在一个时间段内，有个时间为空，查寻是否为休假或请假。
                    If IsNull(rstGrid.Fields("时间" & intField)) Or IsNull(rstGrid.Fields("时间" & intField + 1)) _
                        Or Trim(rstGrid.Fields("时间" & intField)) = "" Or Trim(rstGrid.Fields("时间" & intField + 1)) = "" Then
                        
                        
                        
                        '=====格内为满，将值改为时间格式。
                        If Trim(rstGrid.Fields("时间" & intField)) <> "" Then
                            sTime = Left(rstGrid.Fields("时间" & intField), 2) & ":" & Right(rstGrid.Fields("时间" & intField), 2)
                            rstGrid.Fields("时间" & intField) = sTime
                        End If
                        
                        If Trim(rstGrid.Fields("时间" & intField + 1)) <> "" Then
                            sTime = Left(rstGrid.Fields("时间" & intField + 1), 2) & ":" & Right(rstGrid.Fields("时间" & intField + 1), 2)
                            rstGrid.Fields("时间" & intField + 1) = sTime
                        End If
                        
                        
                        Dim lTimePos As Long
            
                        If intField Mod 2 = 1 Then
                            lTimePos = (intField + 1) / 2
                        ElseIf intField Mod 2 = 0 Then
                            lTimePos = intField / 2
                        End If
                        
                        bln(lTimePos) = True
                        
                        If InStr(rstGrid.Fields("备注"), "时间段" & lTimePos) <> 0 Then
                            If GetYesNoLeave(EmployeeInfo(lEmpRow).EmployeeID, intField, rstGrid.Fields("刷卡日期")) = True Then
                                rstGrid.Fields("时间" & intField) = "请"
                                rstGrid.Fields("时间" & intField + 1) = "假"
                                rstGrid.Fields("加班次数") = rstGrid.Fields("加班次数") - 1
                            Else
                                rstGrid.Fields("旷工次数") = rstGrid.Fields("旷工次数") + 1
                            End If
                        Else
                        
                            '=====格内为空,先判断是否为休假。
                            If GetYesNoVac(EmployeeInfo(lEmpRow).EmployeeID, intField, EmployeeInfo(lEmpRow).VacID, rstGrid.Fields("刷卡日期")) = True Then
                                
                                rstGrid.Fields("时间" & intField) = "休"
                                rstGrid.Fields("时间" & intField + 1) = "假"
                                  
                            Else
                                '====不为休假，判断是否为请假。
                                If GetYesNoLeave(EmployeeInfo(lEmpRow).EmployeeID, intField, rstGrid.Fields("刷卡日期")) = True Then
                                    rstGrid.Fields("时间" & intField) = "请"
                                    rstGrid.Fields("时间" & intField + 1) = "假"
                                Else
                                    '=====否则视为旷工
                                    rstGrid.Fields("旷工次数") = rstGrid.Fields("旷工次数") + 1
                                End If
                            End If
                        
                        End If
                   '=========================================================================================
                        
                    Else
                        '==========判断时间是否迟到或旷工。返回1迟到，返回2旷工。
                        
                        Dim lTimePos2 As Long
            
                        If intField Mod 2 = 1 Then
                            lTimePos2 = (intField + 1) / 2
                        ElseIf intField Mod 2 = 0 Then
                            lTimePos2 = intField / 2
                        End If
                        
                        bln(lTimePos2) = True
                        
                        lTimeState = GetTimeState(rstGrid.Fields("时间" & intField), intField, Val(txtLate.Text), Val(txtAbsent.Text))
                        lTimeState2 = GetTimeState(rstGrid.Fields("时间" & intField + 1), intField + 1, Val(txtLate.Text), Val(txtAbsent.Text))
                        
                        If lTimeState = 2 Or lTimeState2 = 2 Then
                            rstGrid.Fields("旷工次数") = rstGrid.Fields("旷工次数") + 1
                            
                        Else
                        
                            If lTimeState = 1 Then
                                rstGrid.Fields("迟到早退次数") = rstGrid.Fields("迟到早退次数") + 1
                            End If
                            
                            If lTimeState2 = 1 Then
                                rstGrid.Fields("迟到早退次数") = rstGrid.Fields("迟到早退次数") + 1
                            End If
                            
                        End If
                        
                        '==========将值改为时间格式。
                        sTime = Left(rstGrid.Fields("时间" & intField), 2) & ":" & Right(rstGrid.Fields("时间" & intField), 2)
                        rstGrid.Fields("时间" & intField) = sTime
                        sTime = Left(rstGrid.Fields("时间" & intField + 1), 2) & ":" & Right(rstGrid.Fields("时间" & intField + 1), 2)
                        rstGrid.Fields("时间" & intField + 1) = sTime
                        
                    End If
                    
                Next
                
                rstGrid.Update
        
                
NextDo:
                dBeginTime = dBeginTime + 1
            Loop
        End If
    Next
    
    Grid.DefSortCol = "Date"
'    Grid.Col("Date").SortDirection = sASC
'    MsgBox rstGrid.RecordCount

    For i = 1 To UBound(bln)
        Grid.Col("时间" & Val(i * 2 - 1)).Visible = bln(i)
        Grid.Col("时间" & Val(i * 2)).Visible = bln(i)
    Next


    Set Grid.DataSource = rstGrid
'    Grid.ReFetch
    
    Dim cur As Long
    cur = GetTickCount
    Dim s As String
    s = Str((cur - Pre) / 1000)
    MsgBox s
End Sub


Private Sub SetGrid(ByVal TimeCount As Long)

    If rstGrid.State = adStateOpen Then rstGrid.Close
    Set rstGrid = Nothing
    Dim i As Long
    For i = 1 To rstGrid.Fields.Count
        rstGrid.Fields.Delete i
    Next
    
    rstGrid.Fields.Append "员工名称", adVarChar, 20
    rstGrid.Fields.Append "员工卡号", adVarChar, 20
    rstGrid.Fields.Append "刷卡日期", adVarChar, 20
    rstGrid.Fields.Append "星期", adVarChar, 20
    rstGrid.Fields.Append "刷卡次数", adInteger

    Grid.ClearHeadData
    Grid.AddHeader "序号", "Serial", 40, -1, "Serial", False, sSerial
    Grid.AddHeader "刷卡日期", "Date", 90, -1, "刷卡日期", False, sDefault
    Grid.AddHeader "星期", "Weekday", 20, -1, "星期", False, sDefault
    Grid.AddHeader "员工卡号", "Card", 60, -1, "员工卡号", False, sDefault
    Grid.AddHeader "员工名称", "Name", 60, -1, "员工名称", False, sDefault
    Grid.AddHeader "刷卡次数", "CardCount", 60, -1, "刷卡次数", False, sDefault
    
    Dim intField As Integer
    
    For intField = 1 To TimeCount Step 2
    
        rstGrid.Fields.Append "时间" & intField, adVarChar, 20
        rstGrid.Fields.Append "时间" & intField + 1, adVarChar, 20
        
        Grid.AddHeader "上班时间", "时间" & intField, 80, -1, "时间" & intField, False, sDefault
        Grid.AddHeader "下班时间", "时间" & intField + 1, 80, -1, "时间" & intField + 1, False, sDefault


        Grid.AddCollect "时间段" & (intField + 1) / 2, "KeyOne" & intField
        Grid.Col("时间" & intField).CollectKey = "KeyOne" & intField
        Grid.Col("时间" & intField + 1).CollectKey = "KeyOne" & intField
        
     '---------休假，请假以绿色显示。
        Grid.Col("时间" & intField).FilterColor.Filter.SetCondition "in(请,休,假)"
        Grid.Col("时间" & intField).FilterColor.FrColor = RGB(0, 128, 64)
        Grid.Col("时间" & intField + 1).FilterColor.Filter.SetCondition "in(请,休,假)"
        Grid.Col("时间" & intField + 1).FilterColor.FrColor = RGB(0, 128, 64)
        
    Next
    
    rstGrid.Fields.Append "迟到早退次数", adInteger
    Grid.AddHeader "迟到早退次数", "Late", 100, -1, "迟到早退次数", True, sDefault
    
   '---------设置迟到以红色显示
    Grid.Col("Late").FilterColor.Filter.SetCondition "> 0"
    Grid.Col("Late").FilterColor.FrColor = vbRed
    
    rstGrid.Fields.Append "旷工次数", adInteger
    Grid.AddHeader "旷工次数", "Absent", 60, -1, "旷工次数", True, sDefault
    
   '---------设置旷工以红色显示
    Grid.Col("Absent").FilterColor.Filter.SetCondition "> 0"
    Grid.Col("Absent").FilterColor.FrColor = vbRed
    
    rstGrid.Fields.Append "加班次数", adInteger
    Grid.AddHeader "加班次数", "AddClass", 60, -1, "加班次数", True, sDefault
    
    rstGrid.Fields.Append "备注", adVarChar, 255
    Grid.AddHeader "备注", "Memo", 200, -1, "备注", True, sDefault
    
    
    rstGrid.Open "", , adOpenStatic, adLockBatchOptimistic
    
    Grid.HeadHeight = 40
    Grid.AllowAddNew = False
    
    Grid.GroupCol = 4
    
    Grid.Col("Serial").SumText = "合计"
    Grid.SmallSumText = "小计"

End Sub










Private Sub Form_Resize()
On Error Resume Next

    Frame.Move 60, 0, Me.ScaleWidth - 120, 800
    lblEmployee.Move 60, 300
    cmbEmployee.Move lblEmployee.Left + lblEmployee.Width + 60, lblEmployee.Top - 60
    
    Label1.Move cmbEmployee.Left + cmbEmployee.Width + 60, lblEmployee.Top
    BeginDate.Move Label1.Left + Label1.Width + 60, Label1.Top - 60
    Label2.Move BeginDate.Left + BeginDate.Width + 60, Label1.Top
    EndDate.Move Label2.Left + Label2.Width + 60, Label1.Top - 60
    Label3.Move EndDate.Left + EndDate.Width + 60, Label1.Top
    txtLate.Move Label3.Left + Label3.Width + 60, Label1.Top - 60
    Label4.Move txtLate.Left + txtLate.Width + 60, Label1.Top
    txtAbsent.Move Label4.Left + Label4.Width + 60, Label1.Top - 60
    
'    cmdQuery.Move txtAbsent.Left + txtAbsent.Width + 60, txtAbsent.Top - 60
'    cmdExpor.Move cmdQuery.Left + cmdQuery.Width + 60, cmdQuery.Top
'    cmdPrint.Move cmdExpor.Left + cmdExpor.Width + 60, cmdExpor.Top

    cmdPrint.Move Frame.Width - cmdQuery.Width - 100, txtAbsent.Top - 60
    cmdExpor.Move cmdPrint.Left - cmdExpor.Width - 60, txtAbsent.Top - 60
    cmdQuery.Move cmdExpor.Left - cmdQuery.Width - 60, txtAbsent.Top - 60
    'Frame.Height - cmdPrint.Height - 100
    Grid.Move 0, Frame.Top + Frame.Height, Me.ScaleWidth, Me.ScaleHeight - Frame.Height
    
End Sub


Private Sub cmdExpor_Click()
On Error GoTo IsErr:

    ComDlg.CancelError = True
    ComDlg.Filter = "ExcelFile(*.xls)|*.xls"
    ComDlg.Flags = cdlOFNHideReadOnly
    ComDlg.InitDir = App.Path
    ComDlg.DialogTitle = "导出"
    ComDlg.ShowSave
   
    If Trim(ComDlg.FileName) = "" Then Exit Sub
    ExporToExcel rstGrid, "报表", ComDlg.FileName
    Exit Sub
    
IsErr:
   
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If rstGrid.State = 1 Then rstGrid.Close
    Set rstGrid = Nothing
    If rstExec.State = 1 Then rstExec.Close
    Set rstExec = Nothing
End Sub



Private Sub Grid_DblClick()
    If rstGrid.RecordCount = 0 Then Exit Sub
    
    Load frmReport_D
    frmReport_D.ShowBill rstGrid.Fields("刷卡日期"), rstGrid.Fields("星期"), rstGrid.Fields("员工卡号"), rstGrid.Fields("员工名称"), txtLate.Text, txtAbsent.Text
    frmReport_D.Show vbModal
    

End Sub