modulecountryreport.bas

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

    Dim aRecordArry()       As Variant
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)
    
    
    m_Command4Report.CommandText = "sp_DifferentDepartmentStat"
    Set m_oRs4Report = m_Command4Report.Execute
    
    If ExportExcel(, , C_DifferentDepartmentStat_ONE, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Exit Function
    End If
        g_oSheet4Export.Range("c" & CStr(3)) = g_str4ReportOrgan
        g_oSheet4Export.Range("l" & CStr(3)) = CheckVariant(g_str4ReportTime)
        g_oSheet4Export.Range("d" & 8).CopyFromRecordset m_oRs4Report
        g_oSheet4Export.Range("d" & 17) = g_oSheet4Export.Range("D" & 8)
        g_oSheet4Export.Range("d" & 18) = g_oSheet4Export.Range("D" & 8)
        
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    g_oApp4Export.Visible = True
    g_bHaveExcel = True
Err:
    Exit Function
End Function



'分部门情况统计表(3)

Public Function DifferentDepartmentStat3() 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
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)
    
    
    m_Command4Report.CommandText = "sp_DifferentDepartmentStat"
    Set m_oRs4Report = m_Command4Report.Execute
    
    If ExportExcel(, , C_DifferentDepartmentStat_THREE, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Exit Function
    End If
        g_oSheet4Export.Range("c" & CStr(3)) = CheckVariant(g_str4ReportOrgan)
        g_oSheet4Export.Range("n" & CStr(3)) = CheckVariant(g_str4ReportTime)
        g_oSheet4Export.Range("o" & 8).CopyFromRecordset m_oRs4Report
        g_oSheet4Export.Range("o" & 18) = g_oSheet4Export.Range("o" & 8)
        g_oSheet4Export.Range("o" & 19) = g_oSheet4Export.Range("o" & 8)
        
  
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    g_oApp4Export.Visible = True
    g_bHaveExcel = True
Err:
    Exit Function
End Function

'两年数字变化情况表
Public Function Getsp_TwoYearsChangge() As Boolean
    Dim Command4Report      As New ADODB.Command
    Dim Params4Report       As ADODB.Parameters
    Dim oRs4Report          As New ADODB.Recordset
    Dim iCountResult        As Integer
   
On Error GoTo Err:
    Getsp_TwoYearsChangge = False
    With Command4Report
        Set .ActiveConnection = g_oConnection4This
        .CommandType = adCmdStoredProc
        Set Params4Report = .Parameters
        Params4Report.Append .CreateParameter("RETURN_VALUE", adInteger, adParamOutput)
        Params4Report.Append .CreateParameter("@Organ_no", adVarChar, adParamInput, 50)
    End With
    Params4Report("@Organ_no") = frmReport.SSComboBoxEx4Organ.ItemData(frmReport.SSComboBoxEx4Organ.ListIndex)
    Command4Report.CommandText = "sp_TwoYearsChangge "
    Command4Report.Execute
    iCountResult = Params4Report("RETURN_VALUE").Value
    
    If ExportExcel(, , C_TwoYearsChangge, frmReport.Dir4This.Path) = False Then
        Set Command4Report = Nothing
        Set Params4Report = Nothing
        Set oRs4Report = Nothing
        Exit Function
    End If
    g_oSheet4Export.Range("D" & CStr(11)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("D" & CStr(13)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("D" & CStr(14)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("c" & CStr(3)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("j" & CStr(3)) = CheckVariant(g_str4ReportTime)
    Set Command4Report = Nothing
    Set Params4Report = Nothing
    Set oRs4Report = Nothing
    Getsp_TwoYearsChangge = True
Err:
    Exit Function
End Function
'企业单位管理人员、专业技术人员分行业统计报表 第八表*/
Public Function Getsp_cExpertDifferentCallingBasic() As Boolean
    Dim Command4Report As New ADODB.Command
    Dim Params4Report  As ADODB.Parameters
    Dim oRs4Report     As New ADODB.Recordset
    Dim iCountResult     As Integer
   
'On Error GoTo Err:
    Getsp_cExpertDifferentCallingBasic = False
    With Command4Report
        Set .ActiveConnection = g_oConnection4This
        .CommandType = adCmdStoredProc
        Set Params4Report = .Parameters
        Params4Report.Append .CreateParameter("@Organ_no", adVarChar, adParamInput, 50)
    End With
    Params4Report("@Organ_no") = frmReport.SSComboBoxEx4Organ.ItemData(frmReport.SSComboBoxEx4Organ.ListIndex)
    Command4Report.CommandText = "sp_cExpertDifferentCallingBasic"
    Set oRs4Report = Command4Report.Execute()
    'iCountResult = Params4Report("RETURN_VALUE").Value
    
   If ExportExcel(, , C_cExpertDifferentCallingBasic_ONE, frmReport.Dir4This.Path) = False Then
        Set Command4Report = Nothing
        Set Params4Report = Nothing
        Set oRs4Report = Nothing
        Exit Function
    End If
    'Set oRs4Report = oRs4Report.NextRecordset
    Set g_oSheet4Export = g_oBook4Export.Worksheets("sheet1")
    If oRs4Report.EOF = False Then g_oSheet4Export.Range("k21, k26").CopyFromRecordset oRs4Report
    
    g_oSheet4Export.Range("D" & CStr(11)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("D" & CStr(13)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("D" & CStr(14)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("c" & CStr(3)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("j" & CStr(3)) = CheckVariant(g_str4ReportTime)
    Set oRs4Report = oRs4Report.NextRecordset
    Set g_oSheet4Export = g_oBook4Export.Worksheets("sheet2")
    If oRs4Report.EOF = False Then g_oSheet4Export.Range("k12, k26").CopyFromRecordset oRs4Report
    
    g_oSheet4Export.Range("D" & CStr(11)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("D" & CStr(13)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("D" & CStr(14)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("c" & CStr(3)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("j" & CStr(3)) = CheckVariant(g_str4ReportTime)
    
    Set Command4Report = Nothing
    Set Params4Report = Nothing
    Set oRs4Report = Nothing
    Getsp_cExpertDifferentCallingBasic = True
Err:
    Exit Function
End Function
'集团公司各类专业技术人员分类分级情况统计表
Public Function GetbEveryExpertSortLevel() As Boolean
    Dim Command4Report      As New ADODB.Command
    Dim Params4Report       As ADODB.Parameters
    Dim oRs4Report          As New ADODB.Recordset
    Dim iCountResult        As Integer
   
On Error GoTo Err:
    GetbEveryExpertSortLevel = False
    With Command4Report
        Set .ActiveConnection = g_oConnection4This
        .CommandType = adCmdStoredProc
        Set Params4Report = .Parameters
        Params4Report.Append .CreateParameter("@Organ_no", adVarChar, adParamInput, 50)
    End With
    Params4Report("@Organ_no") = frmReport.SSComboBoxEx4Organ.ItemData(frmReport.SSComboBoxEx4Organ.ListIndex)
    Command4Report.CommandText = "sp_bEveryExpertSortLevel"
    Set oRs4Report = Command4Report.Execute()
    'iCountResult = Params4Report("RETURN_VALUE").Value
    
   If ExportExcel(, , C_bEveryExpertSortLevel, frmReport.Dir4This.Path) = False Then
        Set Command4Report = Nothing
        Set Params4Report = Nothing
        Set oRs4Report = Nothing
        Exit Function
    End If
    If oRs4Report.EOF = False Then g_oSheet4Export.Range("e10, p15").CopyFromRecordset oRs4Report
    g_oSheet4Export.Range("D" & CStr(11)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("D" & CStr(13)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("D" & CStr(14)) = CheckVariant(iCountResult)
    g_oSheet4Export.Range("c" & CStr(3)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("j" & CStr(3)) = CheckVariant(g_str4ReportTime)
    Set Command4Report = Nothing
    Set Params4Report = Nothing
    Set oRs4Report = Nothing
    GetbEveryExpertSortLevel = True
Err:
    Exit Function
End Function
'中央分地区统计表
Public Function Getsp_DifferentAreaStat() As Boolean
    Dim Command4Report      As New ADODB.Command
    Dim Params4Report       As ADODB.Parameters
    Dim oRs4Report          As New ADODB.Recordset
    Dim iStartRowOne        As Integer
    Dim iStartRowSec        As Integer
On Error GoTo Err:
    Getsp_DifferentAreaStat = False
    With Command4Report
        Set .ActiveConnection = g_oConnection4This
        .CommandType = adCmdStoredProc
        Set Params4Report = .Parameters
        Params4Report.Append .CreateParameter("@Organ_no", adVarChar, adParamInput, 50)
    End With
    Params4Report("@Organ_no") = frmReport.SSComboBoxEx4Organ.ItemData(frmReport.SSComboBoxEx4Organ.ListIndex)
    Command4Report.CommandText = "sp_DifferentAreaStat"
    
    Set oRs4Report = Command4Report.Execute
    
    If ExportExcel(, , C_DifferentAreaStat, frmReport.Dir4This.Path) = False Then
        Set Command4Report = Nothing
        Set oRs4Report = Nothing
        Exit Function