modulewagereport.bas

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

Attribute VB_Name = "ModuleWageReport"
Public Function Getsp_emp_wage() As Boolean
    Dim i As Integer
    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_emp_wage = 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_emp_wage"
    Set m_oRs4Report = m_Command4Report.Execute()
    
    If ExportExcel(, , C_EMP_WAGE, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Getsp_emp_wage = False
        Exit Function
    End If
    
    i = 9
    Do While m_oRs4Report.EOF = False
        If m_oRs4Report.Fields(1).Value = Null Then
            m_oRs4Report.Fields(1).Value = 0
        Else
            g_oSheet4Export.Range("d" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(1).Value)
        End If
        If m_oRs4Report.Fields(2).Value = Null Then
            m_oRs4Report.Fields(2).Value = 0
        Else
            g_oSheet4Export.Range("h" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(2).Value)
        End If
        If m_oRs4Report.Fields(3).Value = Null Then
            m_oRs4Report.Fields(3).Value = 0
        Else
            g_oSheet4Export.Range("i" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(3).Value)
        End If
        If m_oRs4Report.Fields(4).Value = Null Then
            m_oRs4Report.Fields(4).Value = 0
        Else
            g_oSheet4Export.Range("j" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(4).Value)
        End If
        If m_oRs4Report.Fields(5).Value = Null Then
            m_oRs4Report.Fields(5).Value = 0
        Else
            g_oSheet4Export.Range("k" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(5).Value)
        End If
        If m_oRs4Report.Fields(6).Value = Null Then
            m_oRs4Report.Fields(6).Value = 0
        Else
            g_oSheet4Export.Range("l" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(6).Value)
        End If
        If m_oRs4Report.Fields(7).Value = Null Then
            m_oRs4Report.Fields(7).Value = 0
        Else
            g_oSheet4Export.Range("m" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(7).Value)
        End If
        If m_oRs4Report.Fields(8).Value <> Null Then
            m_oRs4Report.Fields(8).Value = 0
        Else
            g_oSheet4Export.Range("o" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(8).Value)
        End If
        i = i + 1
        m_oRs4Report.MoveNext
    Loop
    If g_oSheet4Export.Range("e" & CStr(8)) <> 0 Then
        g_oSheet4Export.Range("f" & CStr(8)) = g_oSheet4Export.Range("g" & CStr(8)) / g_oSheet4Export.Range("e" & CStr(8))
    End If
    If g_oSheet4Export.Range("e" & CStr(9)) <> 0 Then
        g_oSheet4Export.Range("f" & CStr(9)) = g_oSheet4Export.Range("g" & CStr(9)) / g_oSheet4Export.Range("e" & CStr(9))
    End If
    If g_oSheet4Export.Range("e" & CStr(10)) <> 0 Then
        g_oSheet4Export.Range("f" & CStr(10)) = g_oSheet4Export.Range("g" & CStr(10)) / g_oSheet4Export.Range("e" & CStr(10))
    End If
    If g_oSheet4Export.Range("e" & CStr(11)) <> 0 Then
        g_oSheet4Export.Range("f" & CStr(11)) = g_oSheet4Export.Range("g" & CStr(11)) / g_oSheet4Export.Range("e" & CStr(11))
    End If
    g_oSheet4Export.Range("C" & CStr(2)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("j" & CStr(2)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Range("C" & CStr(16)) = CheckVariant(g_str4OrganEmp)
    g_oSheet4Export.Range("i" & CStr(16)) = CheckVariant(g_str4CompanyEmp)
    g_oSheet4Export.Range("n" & CStr(16)) = CheckVariant(g_str4TableEmp)
    g_oSheet4Export.Range("r" & CStr(16)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Cells(16, 2).Select
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    Getsp_emp_wage = True
Err:
    Exit Function
End Function

Public Function Getsp_allowence() As Boolean
    Dim i As Integer
    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_allowence = 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_allowence"
    Set m_oRs4Report = m_Command4Report.Execute()
    
    If ExportExcel(, , C_EMP_WAGE_OTHER, frmReport.Dir4This.Path) = False Then
        Set m_Command4Report = Nothing
        Set m_Params4Report = Nothing
        Set m_oRs4Report = Nothing
        Getsp_allowence = False
        Exit Function
    End If
    i = 8
    Do While m_oRs4Report.EOF = False
        If m_oRs4Report.Fields(0).Value = Null Then
            m_oRs4Report.Fields(0).Value = 0
        Else
            g_oSheet4Export.Range("f" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(0).Value)
        End If
        If m_oRs4Report.Fields(1).Value = Null Then
            m_oRs4Report.Fields(1).Value = 0
        Else
            g_oSheet4Export.Range("g" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(1).Value)
        End If
        If m_oRs4Report.Fields(2).Value = Null Then
            m_oRs4Report.Fields(2).Value = 0
        Else
            g_oSheet4Export.Range("h" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(2).Value)
        End If
        If m_oRs4Report.Fields(3).Value = Null Then
            m_oRs4Report.Fields(3).Value = 0
        Else
            g_oSheet4Export.Range("i" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(3).Value)
        End If
        If m_oRs4Report.Fields(4).Value = Null Then
            m_oRs4Report.Fields(4).Value = 0
        Else
            g_oSheet4Export.Range("j" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(4).Value)
        End If
        If m_oRs4Report.Fields(5).Value = Null Then
            m_oRs4Report.Fields(5).Value = 0
        Else
            g_oSheet4Export.Range("k" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(5).Value)
        End If
        If m_oRs4Report.Fields(6).Value = Null Then
            m_oRs4Report.Fields(6).Value = 0
        Else
            g_oSheet4Export.Range("l" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(6).Value)
        End If
        If m_oRs4Report.Fields(7).Value = Null Then
            m_oRs4Report.Fields(7).Value = 0
        Else
            g_oSheet4Export.Range("m" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(7).Value)
        End If
        If m_oRs4Report.Fields(8).Value = Null Then
            m_oRs4Report.Fields(8).Value = 0
        Else
            g_oSheet4Export.Range("n" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(8).Value)
        End If
        If m_oRs4Report.Fields(9).Value = Null Then
            m_oRs4Report.Fields(9).Value = 0
        Else
            g_oSheet4Export.Range("o" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(9).Value)
        End If
        If m_oRs4Report.Fields(10).Value = Null Then
            m_oRs4Report.Fields(10).Value = 0
        Else
            g_oSheet4Export.Range("p" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(10).Value)
        End If
        If m_oRs4Report.Fields(11).Value = Null Then
            m_oRs4Report.Fields(11).Value = 0
        Else
            g_oSheet4Export.Range("q" & CStr(i)) = CheckVariant(m_oRs4Report.Fields(11).Value)
        End If
        i = i + 1
        m_oRs4Report.MoveNext
    Loop
    g_oSheet4Export.Range("C" & CStr(2)) = CheckVariant(g_str4ReportOrgan)
    g_oSheet4Export.Range("k" & CStr(2)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Range("C" & CStr(15)) = CheckVariant(g_str4OrganEmp)
    g_oSheet4Export.Range("h" & CStr(15)) = CheckVariant(g_str4CompanyEmp)
    g_oSheet4Export.Range("k" & CStr(15)) = CheckVariant(g_str4TableEmp)
    g_oSheet4Export.Range("q" & CStr(15)) = CheckVariant(g_str4ReportTime)
    g_oSheet4Export.Cells(16, 2).Select
    Set m_Command4Report = Nothing
    Set m_Params4Report = Nothing
    Set m_oRs4Report = Nothing
    Getsp_allowence = True
Err:
    Exit Function
End Function