frm_rpt_kaoqinrpt.frm

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

If adoPrimaryRS2.RecordCount Then GoTo Err2

   
End With


Set o = Nothing

Screen.MousePointer = 0


'cE.SetFooter "第&P页，共&N页"
With cE.ex
'''*****打印预揽****
'        .ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
'        .ActiveSheet.PageSetup.PrintTitleColumns = ""
'
''     .Ex.ActiveWorkbook.SaveAs localdrive & Date & "_" & ht_dataCombo(0).Text & ".xls"
   .Rows("1:29").Delete Shift:=-4162        ''''xlUp
     .Visible = True
     .Application.ScreenUpdating = True
     .ActiveWindow.SelectedSheets.PrintPreview
'
''''关闭EXCEL
'     .Visible = False
''     .Worksheets("sheet1").ChartObjects.Delete
'     .ActiveSheet.ChartObjects.Delete
'     .ActiveSheet.OLEObjects.Delete
''     .ActiveWorkbook.Close savechanges:=False
'     .ActiveWorkbook.SaveCopyAs sFileName
'     .ActiveWorkbook.Close savechanges:=True
End With
'''''''''''***********表头************
''ex.Worksheets("sheet1").PageSetup.PrintTitleRows = Rows("1:2").Address          ''这里"1:6"是根据表头所在的行来自行设定的。
''ex.Worksheets("sheet1").PageSetup.PrintTitleColumns = Columns("A:D").Address    ''这里"A:P"是根据表头所在的列来设定的。
'''''''''''***********************
'cE.Ex.Quit
'cE.Ex.Application.Quit
 Set cE = Nothing
 
 
' Call frmMainunloadSQL
 End
 
Exit Sub
Err1:
MsgBox Err.Description
cE.ex.Quit
Set cE = Nothing
Screen.MousePointer = 0


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("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