clssalaryrptset.cls

金算盘软件代码

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsSalaryRptSet"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'  工资条设置类
'  作者: 邹俊
'  日期 ： 1998. 11. 12

Option Explicit
Private Const mlngViewID = 63         '工资视图ID
Private mlngSalaryBillID As Long      '工资条来源工资表ID
Private mstrSalaryBillName As String   '工资条名称
Private mstrSalaryBillWhere As String  '工资条条件
Private mstrSalaryBillSQL As String    '工资条SQl

'工资条来源工资表ID
Public Property Get SalaryBillID() As Long
    SalaryBillID = mlngSalaryBillID
End Property
Public Property Let SalaryBillID(ByVal lngID As Long)
    mlngSalaryBillID = lngID
End Property
'工资条名称
Public Property Get SalaryBillName() As String
    SalaryBillName = mstrSalaryBillName
End Property
Public Property Let SalaryBillName(ByVal strName As String)
    mstrSalaryBillName = strName
End Property
'工资条条件
Public Property Get SalaryBillWhere() As String
    SalaryBillWhere = mstrSalaryBillWhere
End Property
Public Property Let SalaryBillWhere(ByVal strWhere As String)
    mstrSalaryBillWhere = strWhere
End Property
'工资条SQl
Public Property Get SalaryBillSQL() As String
    SalaryBillSQL = mstrSalaryBillSQL
End Property
Public Property Let SalaryBillSQL(ByVal strSql As String)
    mstrSalaryBillSQL = strSql
End Property
'初始化工资条默认条件SQL(直接调用工资条)
Public Function InitSalaryBillSql(ByVal lngSalaryID As Long) As String
    Dim strSql As String
    Dim recItem As rdoResultset
    Dim strSET As String
    Dim recSet As rdoResultset
    Dim i As Integer
    Dim strDate As String
    Dim strBillSql As String
    Dim strTmp As String
    Dim strFrom As String
    Dim strWhere As String
    Dim strFieldName As String
    Dim strViewFieldDesc As String
    Dim blnIsOK As Boolean
    Dim strFormat As String
    
    strBillSql = "SELECT "
    strSET = "SELECT Setting.lngModuleID, Setting.strSection, Setting.strKey, " & _
             " Setting.strSetting  FROM Setting " & _
             " WHERE Setting.lngModuleID=11 AND Setting.strSection='工资条固定项目' " & _
             " AND Setting.strKey= '员工编号'"
    Set recSet = gclsBase.BaseDB.OpenResultset(strSET, rdOpenStatic)
    If Not recSet.EOF Then
        strTmp = Trim(recSet!strSetting)
        If UCase(strTmp) = "TRUE" Then
            strBillSql = strBillSql & " Employee.strEmployeeCode AS 员工编号,"
        End If
    End If
    strSET = "SELECT Setting.lngModuleID, Setting.strSection, Setting.strKey, " & _
             " Setting.strSetting  FROM Setting " & _
             " WHERE Setting.lngModuleID=11 AND Setting.strSection='工资条固定项目' " & _
             " AND Setting.strKey= '员工姓名'"
    Set recSet = gclsBase.BaseDB.OpenResultset(strSET, rdOpenStatic)
    If Not recSet.EOF Then
        strTmp = Trim(recSet!strSetting)
        If UCase(strTmp) = "TRUE" Then
            strBillSql = strBillSql & " Employee.strEmployeeName AS 员工姓名,"
        End If
    End If
    strSET = "SELECT Setting.lngModuleID, Setting.strSection, Setting.strKey, " & _
             " Setting.strSetting  FROM Setting " & _
             " WHERE Setting.lngModuleID=11 AND Setting.strSection='工资条固定项目' " & _
             " AND Setting.strKey= '部门编号'"
    Set recSet = gclsBase.BaseDB.OpenResultset(strSET, rdOpenStatic)
    If Not recSet.EOF Then
        strTmp = Trim(recSet!strSetting)
        If UCase(strTmp) = "TRUE" Then
            strBillSql = strBillSql & " Department.strDepartmentCode AS 部门编号,"
        End If
    End If
    strSET = "SELECT Setting.lngModuleID, Setting.strSection, Setting.strKey, " & _
             " Setting.strSetting  FROM Setting " & _
             " WHERE Setting.lngModuleID=11 AND Setting.strSection='工资条固定项目' " & _
             " AND Setting.strKey= '部门名称'"
    Set recSet = gclsBase.BaseDB.OpenResultset(strSET, rdOpenStatic)
    If Not recSet.EOF Then
        strTmp = Trim(recSet!strSetting)
        If UCase(strTmp) = "TRUE" Then
            strBillSql = strBillSql & " Department.strDepartmentName AS 部门名称,"
        End If
    End If
    strSET = "SELECT Setting.lngModuleID, Setting.strSection, Setting.strKey, " & _
             " Setting.strSetting  FROM Setting " & _
             " WHERE Setting.lngModuleID=11 AND Setting.strSection='工资条固定项目' " & _
             " AND Setting.strKey= '职员类型'"
    Set recSet = gclsBase.BaseDB.OpenResultset(strSET, rdOpenStatic)
    If Not recSet.EOF Then
        strTmp = Trim(recSet!strSetting)
        If UCase(strTmp) = "TRUE" Then
            strBillSql = strBillSql & " EmployeeType.strEmployeeTypeName AS 职员类型,"
        End If
    End If
    recSet.Close
    Set recSet = Nothing
    blnIsOK = False
    'strSql = "SELECT ViewField.lngViewFieldID, ViewField.strViewFieldDesc, ViewField.strTableName, " & _
             " ViewField.strFieldName,ViewField.strFieldType,ViewField.bytFieldDec," & _
             " SalaryField.lngSalaryListID FROM ViewField INNER JOIN SalaryField ON " & _
             " ViewField.lngViewFieldID = SalaryField.lngViewFieldID " & _
             " Where ViewField.lngViewId =  " & mlngViewID & _
             " And SalaryField.lngSalaryListID = " & lngSalaryID & " AND SalaryField.blnIsBillPrint=true " & _
             " ORDER BY SalaryField.lngSalaryFieldNO "
    strSql = "SELECT ViewField.lngViewFieldID, ViewField.strViewFieldDesc, ViewField.strTableName, " & _
             " ViewField.strFieldName,ViewField.strFieldType,ViewField.bytFieldDec," & _
             " SalaryField.lngSalaryListID FROM ViewField,SalaryField " & _
             " Where ViewField.lngViewFieldID = SalaryField.lngViewFieldID " & _
             " AND ViewField.lngViewId =  " & mlngViewID & _
             " And SalaryField.lngSalaryListID = " & lngSalaryID & " AND SalaryField.blnIsBillPrint=1 " & _
             " ORDER BY SalaryField.lngSalaryFieldNO "
    Set recItem = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
    strTmp = ""
    With recItem
        If Not .EOF Then
            .MoveLast
            .MoveFirst
            For i = 0 To .RowCount - 1
            strFieldName = !strFieldName
                strViewFieldDesc = !strViewFieldDesc
                '工资发放项目中是否有发放日期
                If Trim(strViewFieldDesc) = "发放日期" Then
                    blnIsOK = True
                End If
                If UCase(Trim(!strFieldType)) = "DOUBLE" Then
                    If !bytFieldDec > 0 Then
                        strFormat = "'99999999999999999999999999990." & String(!bytFieldDec, "0") & "'"
                    Else
                        strFormat = "'99999999999999999999999999990'"
                    End If
                    strTmp = strTmp & "Decode( " & strFieldName & ",0,'', Ltrim(Rtrim(TO_CHAR(" & strFieldName & "," & strFormat & " )))) AS " & strViewFieldDesc & " ,"
                Else
                    strTmp = strTmp & strFieldName & " AS " & strViewFieldDesc & " ,"
                End If
                .MoveNext
            Next
        End If
    End With
    recItem.Close
    Set recItem = Nothing
    strBillSql = strBillSql & " " & strTmp
    strTmp = ""
    strSET = "SELECT Setting.lngModuleID, Setting.strSection, Setting.strKey, " & _
             " Setting.strSetting  FROM Setting " & _
             " WHERE Setting.lngModuleID=11 AND Setting.strSection='工资条固定项目' " & _
             " AND Setting.strKey= '发放日期'"
    Set recSet = gclsBase.BaseDB.OpenResultset(strSET, rdOpenStatic)
    If Not recSet.EOF Then
        strTmp = Trim(recSet!strSetting)
        If UCase(strTmp) = "TRUE" And blnIsOK = False Then
            strBillSql = strBillSql & " SalaryList.strDate AS  发放日期 "
        End If
    End If
    recSet.Close
    Set recSet = Nothing
    strBillSql = Trim(strBillSql)
    If Right(strBillSql, 1) = "," Then
        strBillSql = Left(strBillSql, Len(strBillSql) - 1)
    End If
    'strFrom = " FROM (((((((Employee INNER JOIN Salary ON Employee.lngEmployeeID = Salary.lngEmployeeID) " & _
              " LEFT JOIN Department ON Salary.lngDepartmentID = Department.lngDepartmentID) " & _
              " LEFT JOIN Education ON Employee.lngEducationID = Education.lngEducationID) " & _
              " LEFT JOIN PersonTaxType ON Employee.lngPersonTaxTypeID = PersonTaxType.lngPersonTaxTypeID) " & _
              " LEFT JOIN Title ON Employee.lngTitleID = Title.lngTitleID) " & _
              " INNER JOIN EmployeeType ON Salary.lngEmployeeTypeID = EmployeeType.lngEmployeeTypeID) " & _
              " LEFT JOIN Bank ON Salary.lngBankID = Bank.lngBankID) " & _
              " INNER JOIN SalaryList ON Salary.lngSalaryListID = SalaryList.lngSalaryListID"
    strFrom = " FROM Employee,Salary,Department,Education,PersonTaxType," & _
              " Title,EmployeeType,Bank,SalaryList " & _
              " WHERE  ((((((( Employee.lngEmployeeID = Salary.lngEmployeeID) " & _
              " AND Salary.lngDepartmentID = Department.lngDepartmentID(+)) " & _
              " AND Employee.lngEducationID = Education.lngEducationID(+)) " & _
              " AND Employee.lngPersonTaxTypeID = PersonTaxType.lngPersonTaxTypeID(+)) " & _
              " AND Employee.lngTitleID = Title.lngTitleID(+)) " & _
              " AND Salary.lngEmployeeTypeID = EmployeeType.lngEmployeeTypeID) " & _
              " AND Salary.lngBankID = Bank.lngBankID(+)) " & _
              " AND Salary.lngSalaryListID = SalaryList.lngSalaryListID"
    strBillSql = strBillSql & " " & strFrom
    strWhere = " AND Salary.lngSalaryListID= " & lngSalaryID
    strBillSql = strBillSql & strWhere
    strBillSql = strBillSql & " Order by Employee.strEmployeeCode "
    InitSalaryBillSql = strBillSql
End Function