frmreport.frm

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

'======员工
    cmbEmployee.ShowHeadScale = "0,20,20,20"
    cmbEmployee.ShowHeadValue = "EmployeeID,编号,名称,卡号"
    cmbEmployee.ShowIndex = 2
    cmbEmployee.Type = tStatic
    cmbEmployee.SetBtns "刷新,清空"
    cmbEmployee.ButtonHeight = 20
'    cmbEmployee.DropWidth = cmbEmployee.Width \ 15
    cmbEmployee.DataSource = bufEmployee

    
    txtLate.Text = "0"
    txtAbsent.Text = "60"
    
    BeginDate.Value = Date - 30
    EndDate.Value = Date
    
'    Set Grid.DataSource = rstGrid
    
End Sub

'Private Sub FillInfo()
'    Dim i  As Integer
'    Dim lCount As Long
'    Dim tmpRst As New Recordset
'    Dim strSQL As String
'    Dim tmps As String
'    Dim tmps1 As String
'    Dim iValue As Integer
'
'
'    Dim sBeginTime As String
'    Dim sEndTime As String
'
'    dBeginTime = BeginDate.Value
'    dEndTime = EndDate.Value
'
'    sBeginTime = Format(dBeginTime, "yyyy-mm-dd")
'    sEndTime = Format(dEndTime, "yyyy-mm-dd")
'
''=================ClassInfo======================================
'    tmpRst.Open "select * from class_d", con, adOpenStatic, adLockReadOnly
'    lCount = tmpRst.RecordCount
'    If lCount > 0 Then
'
'        ReDim ClassInfo(1 To lCount) As ClassType
'        tmpRst.MoveFirst
'        For i = 1 To lCount
'            ClassInfo(i).ClassID = tmpRst!ClassID
'
'            tmps = tmpRst!OnDutyTime
'            tmps1 = Split(tmps, ":")(0)
'            iValue = Val(tmps1)
'            tmps1 = Split(tmps, ":")(1)
'            iValue = Val(tmps1) + iValue * 60
'            ClassInfo(i).InTime = iValue
'
'            tmps = tmpRst!OffDutyTime
'            tmps1 = Split(tmps, ":")(0)
'            iValue = Val(tmps1)
'            tmps1 = Split(tmps, ":")(1)
'            iValue = Val(tmps1) + iValue * 60
'            ClassInfo(i).OutTime = iValue
'
'            tmpRst.MoveNext
'        Next
'
'    End If
''================EmployeeInfo============================
'    tmpRst.Close
'    Set tmpRst = Nothing
'    tmpRst.Open "select * from employee", con, adOpenStatic, adLockReadOnly
'    lCount = tmpRst.RecordCount
'
'    If lCount > 0 Then
'
'        ReDim EmployeeInfo(1 To lCount) As EmployeeType
'        tmpRst.MoveFirst
'        For i = 1 To lCount
'            EmployeeInfo(i).EmployeeName = tmpRst!Name
'            EmployeeInfo(i).ClassID = tmpRst!ClassID
'            EmployeeInfo(i).VacID = tmpRst!VacID
'            EmployeeInfo(i).CardID = tmpRst!Code
'            EmployeeInfo(i).EmployeeID = tmpRst!EmployeeID
'            tmpRst.MoveNext
'        Next
'
'    End If
''==============VacInfo=================================
'    tmpRst.Close
'    Set tmpRst = Nothing
'    tmpRst.Open "select * from VacInfo_D order by VacID,TimePos,TimeMode", con, adOpenStatic, adLockReadOnly
'    lCount = tmpRst.RecordCount
'
'    If lCount > 0 Then
'
'        ReDim VacTypeInfo(1 To lCount) As VacType
'        blnVacTypeInfo = True
'
'        tmpRst.MoveFirst
'        For i = 1 To lCount
'            VacTypeInfo(i).VacID = tmpRst!VacID
'
'            If Trim(tmpRst!TimeMode) = "每天" Then
'                VacTypeInfo(i).TimeMode = 0
'            ElseIf Trim(tmpRst!TimeMode) = "每周" Then
'                VacTypeInfo(i).TimeMode = 1
'            ElseIf Trim(tmpRst!TimeMode) = "每月" Then
'                VacTypeInfo(i).TimeMode = 2
'            End If
'
'
'            If tmpRst!TimePos = "全天" Then
'                VacTypeInfo(i).TimePos = 0
'            Else
'                VacTypeInfo(i).TimePos = Val(Mid(tmpRst!TimePos, 4, Len(tmpRst!TimePos) - 3))
'            End If
'
'            VacTypeInfo(i).BeginDate = tmpRst!BeginDate
'            VacTypeInfo(i).EndDate = tmpRst!EndDate
'            VacTypeInfo(i).BeginTime = tmpRst!BeginTime
'            VacTypeInfo(i).EndTime = tmpRst!EndTime
'            tmpRst.MoveNext
'        Next
'
'    End If
'
'
''==============SetVacInfo=================================
'    tmpRst.Close
'    Set tmpRst = Nothing
'    tmpRst.Open "select * from SetVac order by EmployeeID,TimePos,TimeMode", con, adOpenStatic, adLockReadOnly
'    lCount = tmpRst.RecordCount
'
'    If lCount > 0 Then
'
'        ReDim SetVacTypeInfo(1 To lCount) As SetVacType
'        blnSetVacTypeInfo = True
'
'        tmpRst.MoveFirst
'        For i = 1 To lCount
'            SetVacTypeInfo(i).EmployeeID = tmpRst!EmployeeID
'
'            If Trim(tmpRst!TimeMode) = "每天" Then
'                SetVacTypeInfo(i).TimeMode = 0
'            ElseIf Trim(tmpRst!TimeMode) = "每周" Then
'                SetVacTypeInfo(i).TimeMode = 1
'            ElseIf Trim(tmpRst!TimeMode) = "每月" Then
'                SetVacTypeInfo(i).TimeMode = 2
'            End If
'
'
'            If tmpRst!TimePos = "全天" Then
'                SetVacTypeInfo(i).TimePos = 0
'            Else
'                SetVacTypeInfo(i).TimePos = Val(Mid(tmpRst!TimePos, 4, Len(tmpRst!TimePos) - 3))
'            End If
'
'            SetVacTypeInfo(i).BeginDate = tmpRst!BeginDate
'            SetVacTypeInfo(i).EndDate = tmpRst!EndDate
'            SetVacTypeInfo(i).BeginTime = tmpRst!BeginTime
'            SetVacTypeInfo(i).EndTime = tmpRst!EndTime
'            tmpRst.MoveNext
'        Next
'
'    End If
' '============SetClassInfo==================================
'
'    strSQL = " select * from setclass" _
'         & " where  EndDate>= '" & sBeginTime & "' and BeginDate<='" & sEndTime & "'" _
'         & " order by AddClass desc"
'
'    tmpRst.Close
'    Set tmpRst = Nothing
'    tmpRst.Open strSQL, con, adOpenStatic, adLockReadOnly
'    lCount = tmpRst.RecordCount
'
'    If lCount > 0 Then
'
'        ReDim SetClassInfo(1 To lCount) As SetClassType
'        blnSetClassInfo = True
'
'        tmpRst.MoveFirst
'        For i = 1 To lCount
'
'            SetClassInfo(i).EmployeeID = tmpRst!EmployeeID
'            SetClassInfo(i).AddClass = tmpRst!AddClass
'            SetClassInfo(i).BeginDate = tmpRst!BeginDate
'            SetClassInfo(i).EndDate = tmpRst!EndDate
'
'
'            If Trim(tmpRst!TimeMode) = "每天" Then
'                SetClassInfo(i).TimeMode = 0
'            ElseIf Trim(tmpRst!TimeMode) = "每周" Then
'                SetClassInfo(i).TimeMode = 1
'            ElseIf Trim(tmpRst!TimeMode) = "每月" Then
'                SetClassInfo(i).TimeMode = 2
'            End If
'
'            tmps = tmpRst!BeginDate
'            SetClassInfo(i).BeginDate = Val(Mid(tmps, 1, 4)) * 600 + Val(Mid(tmps, 6, 2)) * 50 + Val(Mid(tmps, 9, 2))
'            tmps = tmpRst!EndDate
'            SetClassInfo(i).EndDate = Val(Mid(tmps, 1, 4)) * 600 + Val(Mid(tmps, 6, 2)) * 50 + Val(Mid(tmps, 9, 2))
'
'            If SetClassInfo(i).TimeMode <> 0 Then
'
'                If IsNull(tmpRst!BeginTime) Or Trim(tmpRst!BeginTime) = "" Then SetClassInfo(i).BeginTime = 0 Else SetClassInfo(i).BeginTime = tmpRst!BeginTime
'                If IsNull(tmpRst!EndTime) Or Trim(tmpRst!EndTime) = "" Then SetClassInfo(i).EndTime = 0 Else SetClassInfo(i).EndTime = tmpRst!EndTime
'
'            End If
'
'            SetClassInfo(i).ClassID = tmpRst!ClassID
'            FillCardID SetClassInfo(i).EmployeeID, SetClassInfo(i).CardID
'            If SetClassInfo(i).CardID = Space(8) Then
'                MsgBox "cardID =    !!!"
'            End If
'            tmpRst.MoveNext
'        Next
'    End If
'
''=============================LeaveInfo=======================================
'
'
'
'    strSQL = " select a.EmployeeID,b.BeginTime,b.EndTime,b.TimePos from LeaveInfo a " _
'            & " left outer join LeaveInfo_D b on a.LeaveID=b.LeaveID where" _
'            & " EndTime>= '" & sBeginTime & "' and BeginTime<='" & sEndTime & "' order by EmployeeID,TimePos"
'
'    tmpRst.Close
'    Set tmpRst = Nothing
'    tmpRst.Open strSQL, con, adOpenStatic, adLockBatchOptimistic
'    lCount = tmpRst.RecordCount
'
'    If lCount > 0 Then
'
'        ReDim LeaveTypeInfo(lCount) As LeaveType
'        blnLeaveTypeInfo = True
'
'        tmpRst.MoveFirst
'        For i = 1 To lCount
'
'            LeaveTypeInfo(i).EmployeeID = tmpRst.Fields("EmployeeID")
'
'            If tmpRst!TimePos = "全天" Then
'                LeaveTypeInfo(i).TimePos = 0
'            Else
'                LeaveTypeInfo(i).TimePos = Val(Mid(tmpRst!TimePos, 4, Len(tmpRst!TimePos) - 3))
'            End If
'
'            LeaveTypeInfo(i).BeginTime = tmpRst.Fields("BeginTime")
'            LeaveTypeInfo(i).EndTime = tmpRst.Fields("EndTime")
'
'            tmpRst.MoveNext
'        Next
'
'    End If
'
''=============================InDataInfo=======================================
'
'    sBeginTime = Mid(sBeginTime, 1, 4) & Mid(sBeginTime, 6, 2) & Mid(sBeginTime, 9, 2)
'    sEndTime = Mid(sEndTime, 1, 4) & Mid(sEndTime, 6, 2) & Mid(sEndTime, 9, 2)
'
'    strSQL = "select InDate,InCode,InTime from indata where InDate Between '" _
'             & sBeginTime & "' and '" & sEndTime & " 'order by indate,incode,InTime asc"
'
'    tmpRst.Close
'    Set tmpRst = Nothing
'    tmpRst.Open strSQL, con, adOpenStatic, adLockBatchOptimistic
'    lCount = tmpRst.RecordCount
'
'    If lCount > 0 Then
'
'        ReDim InDataInfo(lCount) As InDataType
'        blnInDataInfo = True
'
'        tmpRst.MoveFirst
'        For i = 1 To lCount
'
'            InDataInfo(i).InDate = tmpRst.Fields("InDate")
'            InDataInfo(i).InCode = tmpRst.Fields("InCode")
'            InDataInfo(i).InTime = tmpRst.Fields("InTime")
'
'            tmpRst.MoveNext
'        Next
'
'    End If
'
'    tmpRst.Close
'    Set tmpRst = Nothing
'
'End Sub

Private Sub GetWeekday()
    Select Case Weekday(dBeginTime)
    
        Case 1
            rstGrid.Fields("星期") = "日"
        Case 2
            rstGrid.Fields("星期") = "一"
        Case 3
            rstGrid.Fields("星期") = "二"
        Case 4
            rstGrid.Fields("星期") = "三"
        Case 5
            rstGrid.Fields("星期") = "四"
        Case 6
            rstGrid.Fields("星期") = "五"
        Case 7
            rstGrid.Fields("星期") = "六"
            
    End Select
End Sub

Private Sub cmdQuery_Click()

    Dim Pre As Long
    Pre = GetTickCount()
    
    Dim iClassID As Integer
    Dim iCardCount As Integer
    Dim iTimePos As Integer
    Dim iTimePos2 As Integer
    Dim strSQL As String
    Dim bTime As String
    Dim sTime As String
    Dim sBeginTime As String
    Dim sEndTime As String
    
    Dim lTimeState2 As Long
    Dim lTimeState As Long
    Dim TimeIndex As Long

    Dim i As Long
    
    dBeginTime = BeginDate.Value
    dEndTime = EndDate.Value
    
    
    FillInfo dBeginTime, dEndTime
'    MaxTimeCount = GetMaxTimeCount(dBeginTime, dEndTime)

    sBeginTime = Format(dBeginTime, "yyyy-mm-dd")
    sEndTime = Format(dEndTime, "yyyy-mm-dd")

    sBeginTime = Mid(sBeginTime, 1, 4) & Mid(sBeginTime, 6, 2) & Mid(sBeginTime, 9, 2)
    sEndTime = Mid(sEndTime, 1, 4) & Mid(sEndTime, 6, 2) & Mid(sEndTime, 9, 2)

    strSQL = "select distinct InDate,InCode,InTime from indata where InDate Between '" _
             & sBeginTime & "' and '" & sEndTime & " 'order by incode,indate,InTime asc"
    
    Debug.Print strSQL
    If rstExec.State = 1 Then rstExec.Close
    Set rstExec = Nothing
    rstExec.CursorLocation = adUseClient
    rstExec.Open strSQL, con, adOpenStatic, adLockBatchOptimistic
    
    i = GetTimePosCount
    ReDim bln(i)
    MaxTimeCount = i * 2
    Call SetGrid(MaxTimeCount)
    
    
    Set Grid.DataSource = Nothing
    rstGrid.CancelBatch
    
    
    Dim lEmpRow As Long
    Dim lEmpCount As Long
    
    If blnEmployeeInfo = False Then Exit Sub
    lEmpCount = UBound(EmployeeInfo)
    
    If rstExec.RecordCount > 0 Then rstExec.MoveFirst
    For lEmpRow = 1 To lEmpCount
    
    
            If (Trim(cmbEmployee.Text) = "") Or _
            (Trim(cmbEmployee.Text) <> "" And EmployeeInfo(lEmpRow).EmployeeName = Trim(cmbEmployee.Text)) Then
        
        
        
            dBeginTime = BeginDate.Value
            Do While dBeginTime <= dEndTime
        
                rstGrid.AddNew
                
                '===========始初化===========
                rstGrid.Fields("迟到早退次数") = 0
                rstGrid.Fields("旷工次数") = 0
                rstGrid.Fields("加班次数") = 0
                
                '===========刷卡日期=========
                bTime = Format(dBeginTime, "yyyy-mm-dd")
                rstGrid.Fields("刷卡日期") = bTime
                
                '===========星期============
                GetWeekday
                
                '===========获得该刷卡日期所拥有班次===========
                bTime = Mid(bTime, 1, 4) & Mid(bTime, 6, 2) & Mid(bTime, 9, 2)
                GetSetClassID EmployeeInfo(lEmpRow).CardID, bTime
                
                '===========获得该刷卡日期刷卡次数=================
                iCardCount = GetClassCount()
                
                rstGrid.Fields("刷卡次数") = iCardCount
                rstGrid.Fields("备注") = g_Memo