moduleempreport.bas

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

    End If
    
    i4Organ = m_oRs4Report.Fields(0)
    Set m_oRs4Report = m_oRs4Report.NextRecordset
    If m_oRs4Report.EOF = False Then g_oSheet4Export.Range("a16:a" & i4Organ).CopyFromRecordset m_oRs4Report
    
    Set m_oRs4Report = m_oRs4Report.NextRecordset
    If m_oRs4Report.EOF = False Then g_oSheet4Export.Range("b15:g" & i4Organ).CopyFromRecordset m_oRs4Report
    
    Set m_oRs4Report = m_oRs4Report.NextRecordset
    If m_oRs4Report.EOF = False Then g_oSheet4Export.Range("h15:m" & i4Organ).CopyFromRecordset m_oRs4Report
    
    Set m_oRs4Report = m_oRs4Report.NextRecordset
    If m_oRs4Report.EOF = False Then g_oSheet4Export.Range("n15:s" & i4Organ).CopyFromRecordset m_oRs4Report
    
    Set m_oRs4Report = m_oRs4Report.NextRecordset
    If m_oRs4Report.EOF = False Then g_oSheet4Export.Range("t15:y" & i4Organ).CopyFromRecordset m_oRs4Report
    
    Set m_oRs4Report = m_oRs4Report.NextRecordset
    If m_oRs4Report.EOF = False Then g_oSheet4Export.Range("z15:ae" & i4Organ).CopyFromRecordset m_oRs4Report
    g_oSheet4Export.Range("c" & CStr(4)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("o" & CStr(4)) = CheckVariant(g_str4ReportTime)
'    g_oSheet4Export.Range("e" & CStr(int4TempCount)) = CheckVariant(g_str4OrganEmp)
'    g_oSheet4Export.Range("k" & CStr(int4TempCount)) = CheckVariant(g_str4CompanyEmp)
'    g_oSheet4Export.Range("u" & CStr(int4TempCount)) = CheckVariant(g_str4TableEmp)
'    g_oSheet4Export.Range("ac" & CStr(int4TempCount)) = CheckVariant(g_str4ReportTime)
'    g_oSheet4Export.Cells(16, 2).Select
   
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    Getsp_bureau_self_emp = True
Err:
    Exit Function
End Function

' 内部退养人员情况
Public Function Getinner() 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:
    Getinner = 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_inner"
    Set m_oRs4Report = m_Command4Report.Execute()
    
    If ExportExcel(, , C_LEVEL_EMP, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Getinner = False
        Exit Function
    End If
    
    If m_oRs4Report.EOF = False Then g_oSheet4Export.Cells(9, 8).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("h" & CStr(11)) = CheckVariant(g_str4CompanyEmp)
    g_oSheet4Export.Range("l" & CStr(11)) = CheckVariant(g_str4TableEmp)
    g_oSheet4Export.Range("o" & CStr(11)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Cells(16, 2).Select
    
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    Getinner = True
Err:
    Exit Function
End Function

'石油专业队数及人数
Public Function Getsp_brigade_speciality() 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_brigade_speciality = 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_brigade_speciality"
    Set m_oRs4Report = m_Command4Report.Execute()
    
    If ExportExcel(, , C_EMP_SEPR, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Getsp_brigade_speciality = False
        Exit Function
    End If
    
    i = 15
    Do While m_oRs4Report.EOF = False
        If i <= 30 Then
            If i = 28 Then i = i + 1
            g_oSheet4Export.Range("d" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(1).Value)
            g_oSheet4Export.Range("e" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(2).Value)
            g_oSheet4Export.Range("f" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(3).Value)
            g_oSheet4Export.Range("g" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(4).Value)
            g_oSheet4Export.Range("h" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(5).Value)
            g_oSheet4Export.Range("i" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(6).Value)
        Else
            g_oSheet4Export.Range("n" & CStr(i - 16)) = CheckVariant(m_oRs4Report.Fields(1).Value)
            g_oSheet4Export.Range("o" & CStr(i - 16)) = CheckVariant(m_oRs4Report.Fields(2).Value)
            g_oSheet4Export.Range("p" & CStr(i - 16)) = CheckVariant(m_oRs4Report.Fields(3).Value)
            g_oSheet4Export.Range("q" & CStr(i - 16)) = CheckVariant(m_oRs4Report.Fields(4).Value)
            g_oSheet4Export.Range("r" & CStr(i - 16)) = CheckVariant(m_oRs4Report.Fields(5).Value)
            g_oSheet4Export.Range("s" & CStr(i - 16)) = CheckVariant(m_oRs4Report.Fields(6).Value)
        End If
        i = i + 1
        m_oRs4Report.MoveNext
    Loop
    g_oSheet4Export.Range("b" & CStr(4)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("j" & CStr(4)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Range("b" & CStr(31)) = CheckVariant(g_str4OrganEmp)
    g_oSheet4Export.Range("h" & CStr(31)) = CheckVariant(g_str4CompanyEmp)
    g_oSheet4Export.Range("n" & CStr(31)) = CheckVariant(g_str4TableEmp)
    g_oSheet4Export.Range("s" & CStr(31)) = CheckVariant(g_str4ReportTime)
    'g_oSheet4Export.Cells(16, 2).Select
    
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    Getsp_brigade_speciality = True
Err:
    Exit Function
End Function

'职工队伍人数分类
Public Function Getsp_brigade_and_emp() 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_brigade_and_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_brigade_and_emp"
    Set m_oRs4Report = m_Command4Report.Execute()
    
    If ExportExcel(, , C_BRIGE_EMP, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Getsp_brigade_and_emp = False
        Exit Function
    End If
    j = 0
    i = 12
    Do While m_oRs4Report.EOF = False
        If j = 36 Or j = 37 Then
            m_oRs4Report.MoveNext
            j = j + 1
        Else
            If i >= 12 And i <= 26 And i <> 22 Then
                g_oSheet4Export.Range("c" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(1).Value)
                g_oSheet4Export.Range("d" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(2).Value)
                m_oRs4Report.MoveNext
                j = j + 1
            ElseIf i >= 27 And i <= 43 And i <> 29 And i <> 32 And i <> 35 Then
                g_oSheet4Export.Range("g" & CStr(i - 17)) = CheckVariant(m_oRs4Report.Fields(1).Value)
                g_oSheet4Export.Range("h" & CStr(i - 17)) = CheckVariant(m_oRs4Report.Fields(2).Value)
                m_oRs4Report.MoveNext
                j = j + 1
            ElseIf i >= 44 And i <> 53 And i <> 48 Then
                g_oSheet4Export.Range("k" & CStr(i - 34)) = CheckVariant(m_oRs4Report.Fields(1).Value)
                g_oSheet4Export.Range("l" & CStr(i - 34)) = CheckVariant(m_oRs4Report.Fields(2).Value)
                m_oRs4Report.MoveNext
                j = j + 1
            End If
            i = i + 1
        End If
    Loop
    g_oSheet4Export.Range("b" & CStr(4)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("f" & CStr(4)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Range("b" & CStr(27)) = CheckVariant(g_str4OrganEmp)
    g_oSheet4Export.Range("e" & CStr(27)) = CheckVariant(g_str4CompanyEmp)
    g_oSheet4Export.Range("h" & CStr(27)) = CheckVariant(g_str4TableEmp)
    g_oSheet4Export.Range("j" & CStr(27)) = CheckVariant(g_str4ReportTime)
    'g_oSheet4Export.Cells(16, 2).Select
    
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    Getsp_brigade_and_emp = True
Err:
    Exit Function
End Function
                   
'基层组织机构及人员情况统计表
Public Function Getsp_basic_organ_and_emp(ByVal organNo As String) As Boolean
    Dim i As Long
    Dim int4TempCount As Long
    Dim m_Command4Report As New ADODB.Command
    Dim m_Params4Report As ADODB.Parameters
    Dim m_oRs4Report As New ADODB.Recordset
    Dim ors4temp As New ADODB.Recordset
    Dim AryNum()
    Dim strSQL As String
    Dim ifor As Integer
'On Error GoTo Err:
    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)
    
    If Len(organNo) = C_FACTORY_LENGTH Then
        m_Command4Report.CommandText = "sp_basic_organ_and_emp"
    Else
        m_Command4Report.CommandText = "sp_bureau_basic_organ_and_emp"
    End If
    
    Set m_oRs4Report = m_Command4Report.Execute()
    Set m_oRs4Report = m_oRs4Report.NextRecordset
    
    If ExportExcel(, , C_EMP_ORGAN, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Getsp_basic_organ_and_emp = False
        Exit Function
    End If
    
    Dim j As Long, k As Long, l As Long, m As Long
    If m_oRs4Report.EOF = False Then
        int4TempCount = 44
        Array4RecordCount = m_oRs4Report.GetRows
        int4RecordCount = UBound(Array4RecordCount, 2) + 1
        int4FieldCount = UBound(Array4RecordCount, 1) + 1
        
        For i = 1 To int4RecordCount \ 32
            g_oSheet4Export.Range("a12:ao43").Insert Shift:=xlShiftDown
            g_oSheet4Export.Range("a12:ao20").Insert Shift:=xlShiftDown
            int4TempCount = int4TempCount + 41
        Next
        strSQL = "select organ_no from t_organ where "
        strSQL = strSQL & " left(organ_no," & Len(organNo) & ")='" & organNo & "'" & HaveOrganMark
        If ors4temp.State = adStateOpen Then ors4temp.Close