moduleempreport.bas

本公司开发得大请油田人事管理系统c/s结构

Attribute VB_Name = "ModuleEmpReport"
Option Explicit
Private i As Integer
Private j As Integer
Private int4organ_no As Integer
Private int4temp As Integer
Private str4temp As String
Private str4organ_no As String
Private int4RecordCount As Long
Private int4FieldCount As Long
Private Array4RecordCount()

'职工人数增减花名册
Public Function EmpRemoveReport() As Boolean
    Dim m_Command4Report As New ADODB.Command
    Dim m_Params4Report As ADODB.Parameters
    Dim m_oRs4Report As New ADODB.Recordset
    Dim int4TempCount As Long
    Dim AryNum() As Long
'On Error GoTo Err:
    EmpRemoveReport = False
    With m_Command4Report
        Set .ActiveConnection = g_oConnection4This
        .CommandType = adCmdStoredProc
        Set m_Params4Report = .Parameters
        m_Params4Report.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
        m_Params4Report.Append .CreateParameter("@Organ_no", adVarChar, adParamInput, 50)
        m_Params4Report.Append .CreateParameter("@Time4Report", adDBTimeStamp, adParamInput, 0)
    End With
    
    m_Params4Report("@Organ_no") = frmReport.SSComboBoxEx4Organ.ItemData(frmReport.SSComboBoxEx4Organ.ListIndex)
    m_Params4Report("@Time4Report") = CDate(g_str4ReportTime)
    
    m_Command4Report.CommandText = "sp_emp_Add_Subtract"
    Set m_oRs4Report = m_Command4Report.Execute()
    
    If ExportExcel(, , C_EMP_REMOVE, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        EmpRemoveReport = False
        Exit Function
    End If

    int4TempCount = 47
    If m_oRs4Report.EOF = False Then
        Array4RecordCount = m_oRs4Report.GetRows
        int4RecordCount = UBound(Array4RecordCount, 2) + 1
        int4FieldCount = UBound(Array4RecordCount, 1) + 1

        For i = 1 To int4RecordCount \ 43
            g_oSheet4Export.Range("b4:v46").Insert Shift:=xlShiftDown
            g_oSheet4Export.Range("b4:v5").Insert Shift:=xlShiftDown
            int4TempCount = int4TempCount + 47
        Next
        ReDim AryNum(int4RecordCount - 1, 0)
        For i = 1 To int4RecordCount
            AryNum(i - 1, 0) = i
        Next
        g_oSheet4Export.Cells(4, 1).Resize(int4RecordCount, 1).Value = AryNum
        g_oSheet4Export.Cells(4, 2).Resize(int4RecordCount, int4FieldCount).Value = TransposeDim(Array4RecordCount)
        
    End If
    g_oSheet4Export.Range("C" & CStr(2)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("p" & CStr(2)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Range("C" & CStr(int4TempCount)) = CheckVariant(g_str4OrganEmp)
    g_oSheet4Export.Range("H" & CStr(int4TempCount)) = CheckVariant(g_str4CompanyEmp)
    g_oSheet4Export.Range("n" & CStr(int4TempCount)) = CheckVariant(g_str4TableEmp)
    g_oSheet4Export.Range("u" & CStr(int4TempCount)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Cells(4, 2).Select
    
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    EmpRemoveReport = True
'Err:
    Exit Function
End Function

' 新增职工情况
Public Function Getnewadd_worker() As Boolean
    Dim m_Command4Report As New ADODB.Command
    Dim m_Params4Report As ADODB.Parameters
    Dim m_oRs4Report As New ADODB.Recordset
On Error GoTo Err:
    Getnewadd_worker = False
    With m_Command4Report
        Set .ActiveConnection = g_oConnection4This
        .CommandType = adCmdStoredProc
        Set m_Params4Report = .Parameters
        m_Params4Report.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
        m_Params4Report.Append .CreateParameter("@Organ_no", adVarChar, adParamInput, 50)
    End With
    
    m_Params4Report("@Organ_no") = frmReport.SSComboBoxEx4Organ.ItemData(frmReport.SSComboBoxEx4Organ.ListIndex)
    
    m_Command4Report.CommandText = "sp_newadd_worker"
    Set m_oRs4Report = m_Command4Report.Execute()
    
    If ExportExcel(, , C_NEW_EMP, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Getnewadd_worker = False
        Exit Function
    End If
    
    If m_oRs4Report.EOF = False Then g_oSheet4Export.Cells(9, 4).CopyFromRecordset m_oRs4Report
'    If m_oRs4Report.EOF Then g_oSheet4Export.Cells(12, 2).CopyFromRecordset m_oRs4Report
    g_oSheet4Export.Range("b" & CStr(4)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("i" & CStr(4)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Range("b" & CStr(11)) = CheckVariant(g_str4OrganEmp)
    g_oSheet4Export.Range("g" & CStr(11)) = CheckVariant(g_str4CompanyEmp)
    g_oSheet4Export.Range("m" & CStr(11)) = CheckVariant(g_str4TableEmp)
    g_oSheet4Export.Range("q" & CStr(11)) = CheckVariant(g_str4ReportTime)
    
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    Getnewadd_worker = True
Err:
    Exit Function
End Function

' 职工违纪处理情况统计表
Public Function Getpunish() As Boolean
    Dim i As Long
    Dim int4organ_no As Integer
    Dim int4TempCount As Long
    Dim m_Command4Report As New ADODB.Command
    Dim m_Params4Report As ADODB.Parameters
    Dim m_oRs4Report As New ADODB.Recordset
On Error GoTo Err:
    Getpunish = False
    With m_Command4Report
        Set .ActiveConnection = g_oConnection4This
        .CommandType = adCmdStoredProc
        Set m_Params4Report = .Parameters
        m_Params4Report.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
        m_Params4Report.Append .CreateParameter("@Organ_no", adVarChar, adParamInput, 50)
    End With
    
    m_Params4Report("@Organ_no") = frmReport.SSComboBoxEx4Organ.ItemData(frmReport.SSComboBoxEx4Organ.ListIndex)
    
    m_Command4Report.CommandText = "sp_punish"
    Set m_oRs4Report = m_Command4Report.Execute()
    
    If ExportExcel(, , C_EMP_PUNISH, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Getpunish = False
        Exit Function
    End If
    
    int4TempCount = 48
    If m_oRs4Report.EOF = False Then
        Array4RecordCount = m_oRs4Report.GetRows
        int4RecordCount = UBound(Array4RecordCount, 2) + 1
        int4FieldCount = UBound(Array4RecordCount, 1) + 1
        If int4RecordCount > 42 Then
            g_oSheet4Export.Range("a6:l47").Insert Shift:=xlShiftDown
            g_oSheet4Export.Range("a6:l7").Insert Shift:=xlShiftDown
            int4TempCount = int4TempCount + 44
        End If
        If int4RecordCount > 79 Then
            g_oSheet4Export.Range("a6:l47").Insert Shift:=xlShiftDown
            g_oSheet4Export.Range("a6:l7").Insert Shift:=xlShiftDown
            int4TempCount = int4TempCount + 44
        End If
        If int4RecordCount > 118 Then
            g_oSheet4Export.Range("a6:l47").Insert Shift:=xlShiftDown
            g_oSheet4Export.Range("a6:l7").Insert Shift:=xlShiftDown
            int4TempCount = int4TempCount + 44
        End If
        
'        g_oSheet4Export.Cells(12, 2).CopyFromRecordset m_oRs4Report
        g_oSheet4Export.Cells(6, 1).Resize(int4RecordCount, int4FieldCount).Value = TransposeDim(Array4RecordCount)
    End If
    g_oSheet4Export.Range("b" & CStr(4)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("g" & CStr(4)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Range("b" & CStr(int4TempCount)) = CheckVariant(g_str4OrganEmp)
    g_oSheet4Export.Range("e" & CStr(int4TempCount)) = CheckVariant(g_str4CompanyEmp)
    g_oSheet4Export.Range("g" & CStr(int4TempCount)) = CheckVariant(g_str4TableEmp)
    g_oSheet4Export.Range("j" & CStr(int4TempCount)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Cells(16, 2).Select
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    Getpunish = True
Err:
    Exit Function
End Function

'  工人技术结构情况
Public Function Getsp_worker_technical_and_structure() As Boolean
    Dim m_Command4Report As New ADODB.Command
    Dim m_Params4Report As ADODB.Parameters
    Dim m_oRs4Report As New ADODB.Recordset
On Error GoTo Err:
    Getsp_worker_technical_and_structure = False
    With m_Command4Report
        Set .ActiveConnection = g_oConnection4This
        .CommandType = adCmdStoredProc
        Set m_Params4Report = .Parameters
        m_Params4Report.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
        m_Params4Report.Append .CreateParameter("@Organ_no", adVarChar, adParamInput, 50)
    End With
    
    m_Params4Report("@Organ_no") = frmReport.SSComboBoxEx4Organ.ItemData(frmReport.SSComboBoxEx4Organ.ListIndex)
    
    m_Command4Report.CommandText = "sp_worker_technical_and_structure"
    Set m_oRs4Report = m_Command4Report.Execute()
    
    If ExportExcel(, , C_WORKER_TECHNIC_FRAME, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Getsp_worker_technical_and_structure = False
        Exit Function
    End If
    
    If m_oRs4Report.EOF = False Then g_oSheet4Export.Cells(8, 4).CopyFromRecordset m_oRs4Report
    g_oSheet4Export.Range("b" & CStr(4)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("m" & CStr(4)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Range("c" & CStr(9)) = CheckVariant(g_str4OrganEmp)
    g_oSheet4Export.Range("i" & CStr(9)) = CheckVariant(g_str4CompanyEmp)
    g_oSheet4Export.Range("q" & CStr(9)) = CheckVariant(g_str4TableEmp)
    g_oSheet4Export.Range("w" & CStr(9)) = CheckVariant(g_str4ReportTime)
    'g_oSheet4Export.Cells(16, 2).Select
    
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    Getsp_worker_technical_and_structure = True
Err:
    Exit Function
End Function

' 长庆石油勘探局自行用工统计表
Public Function Getsp_bureau_self_emp() As Boolean
    Dim i As Long
    Dim i4Organ As Integer
    Dim int4organ_no As Integer
    Dim int4TempCount As Long
    Dim m_Command4Report As New ADODB.Command
    Dim m_Params4Report As ADODB.Parameters
    Dim m_oRs4Report As New ADODB.Recordset
On Error GoTo Err:
    Getsp_bureau_self_emp = False
    With m_Command4Report
        Set .ActiveConnection = g_oConnection4This
        .CommandType = adCmdStoredProc
        Set m_Params4Report = .Parameters
        m_Params4Report.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
        m_Params4Report.Append .CreateParameter("@Organ_no", adVarChar, adParamInput, 50)
    End With
    
    m_Params4Report("@Organ_no") = frmReport.SSComboBoxEx4Organ.ItemData(frmReport.SSComboBoxEx4Organ.ListIndex)
    
    m_Command4Report.CommandText = "sp_bureau_self_emp"
    Set m_oRs4Report = m_Command4Report.Execute()
    
    If ExportExcel(, , C_USER_WORK, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Getsp_bureau_self_emp = False
        Exit Function