budgetlist.frm

金算盘软件代码

                        rec3.MoveNext
                    Next
                End If
            End With
        End If
    Else
        If mintFlag = 1 Then
'            strSql = "Select BudgetQuery1.* From ((((((((BudgetQuery1 LEFT JOIN Item" _
'                & " ON BudgetQuery1.lngItemID=Item.lngItemID) LEFT JOIN ItemNature" _
'                & " ON Item.lngItemNatureID=ItemNature.lngItemNatureID) LEFT JOIN ItemType" _
'                & " ON Item.lngItemTypeID=ItemType.lngItemTypeID) LEFT JOIN Customer" _
'                & " ON BudgetQuery1.lngCustomerID=Customer.lngCustomerID) LEFT JOIN CustomerType" _
'                & " ON CustomerType.lngCustomerTypeID=Customer.lngCustomerTypeID) LEFT JOIN Employee" _
'                & " ON BudgetQuery1.lngEmployeeID=Employee.lngEmployeeID) LEFT JOIN EmployeeType" _
'                & " ON EmployeeType.lngEmployeeTypeID=Employee.lngEmployeeTypeID) LEFT JOIN Class1" _
'                & " ON BudgetQuery1.lngClassID1=Class1.lngClassID) LEFT JOIN Class2" _
'                & " ON BudgetQuery1.lngClassID2=Class2.lngClassID Where " & strWhere
'            Set qryTemp = gclsBase.BaseDB.CreateQueryDef("", strSql)
'            With qryTemp
'                If cboBudget.Text = "" Then
'                    .Parameters("Year") = 1998
'                Else
'                    .Parameters("Year") = cboBudget.Text
'                End If
'                .Parameters("BudgetID") = mlngBudgetID
'                Set dtaBudget.Recordset = .OpenRecordset
'            End With

            '原币部分
            strBudgetQuery1Sql = TransferPublic.getBudgetQuery1OraSql(1)
            strSql = "Select BudgetQuery1.* From ( " & strBudgetQuery1Sql & " ) BudgetQuery1,Item," _
                & "ItemNature,ItemType,Customer,CustomerType,Employee,EmployeeType,Class1,Class2," _
                & "Department,Job" _
                & " Where ((((((((((( BudgetQuery1.lngItemID=Item.lngItemID(+))" _
                & " AND Item.lngItemNatureID=ItemNature.lngItemNatureID(+))" _
                & " AND Item.lngItemTypeID=ItemType.lngItemTypeID(+))" _
                & " AND BudgetQuery1.lngCustomerID=Customer.lngCustomerID(+))" _
                & " AND Customer.lngCustomerTypeID=CustomerType.lngCustomerTypeID(+))" _
                & " AND BudgetQuery1.lngEmployeeID=Employee.lngEmployeeID(+))" _
                & " AND Employee.lngEmployeeTypeID=EmployeeType.lngEmployeeTypeID(+))" _
                & " AND BudgetQuery1.lngClassID1=Class1.lngClassID(+))" _
                & " AND BudgetQuery1.lngClassID2=Class2.lngClassID(+))" _
                & " AND BudgetQuery1.lngDepartmentID=Department.lngDepartmentID(+))" _
                & " AND BudgetQuery1.lngJobID=Job.lngJobID(+) )" _
                & " AND BudgetQuery1.lngAreaID=Area.lngAreaID(+) " _
                & " AND " & strWhere
            If cboBudget.Text = "" Then
                strSql = Salary.Change_Text("[Year]", "1998", strSql)
            Else
                strSql = Salary.Change_Text("[Year]", cboBudget.Text, strSql)
            End If
            strSql = Salary.Change_Text("[BudgetID]", mlngBudgetID, strSql)
            Set dtaBudget.Resultset = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
            '本币部分
            strBudgetQuery1Sql = TransferPublic.getBudgetQuery1OraSql(2)
            strSql = "Select BudgetQuery1.* From ( " & strBudgetQuery1Sql & " ) BudgetQuery1,Item," _
                & "ItemNature,ItemType,Customer,CustomerType,Employee,EmployeeType,Class1,Class2," _
                & "Department,Job" _
                & " Where ((((((((((( BudgetQuery1.lngItemID=Item.lngItemID(+))" _
                & " AND Item.lngItemNatureID=ItemNature.lngItemNatureID(+))" _
                & " AND Item.lngItemTypeID=ItemType.lngItemTypeID(+))" _
                & " AND BudgetQuery1.lngCustomerID=Customer.lngCustomerID(+))" _
                & " AND Customer.lngCustomerTypeID=CustomerType.lngCustomerTypeID(+))" _
                & " AND BudgetQuery1.lngEmployeeID=Employee.lngEmployeeID(+))" _
                & " AND Employee.lngEmployeeTypeID=EmployeeType.lngEmployeeTypeID(+))" _
                & " AND BudgetQuery1.lngClassID1=Class1.lngClassID(+))" _
                & " AND BudgetQuery1.lngClassID2=Class2.lngClassID(+))" _
                & " AND BudgetQuery1.lngDepartmentID=Department.lngDepartmentID(+))" _
                & " AND BudgetQuery1.lngJobID=Job.lngJobID(+) )" _
                & " AND BudgetQuery1.lngAreaID=Area.lngAreaID(+) " _
                & " AND " & strWhere
            If cboBudget.Text = "" Then
                strSql = Salary.Change_Text("[Year]", "1998", strSql)
            Else
                strSql = Salary.Change_Text("[Year]", cboBudget.Text, strSql)
            End If
            strSql = Salary.Change_Text("[BudgetID]", mlngBudgetID, strSql)
            Set rec2 = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
            '数量部分
            strBudgetQuery1Sql = TransferPublic.getBudgetQuery1OraSql(3)
            strSql = "Select BudgetQuery1.* From ( " & strBudgetQuery1Sql & " ) BudgetQuery1,Item," _
                & "ItemNature,ItemType,Customer,CustomerType,Employee,EmployeeType,Class1,Class2," _
                & "Department,Job" _
                & " Where ((((((((((( BudgetQuery1.lngItemID=Item.lngItemID(+))" _
                & " AND Item.lngItemNatureID=ItemNature.lngItemNatureID(+))" _
                & " AND Item.lngItemTypeID=ItemType.lngItemTypeID(+))" _
                & " AND BudgetQuery1.lngCustomerID=Customer.lngCustomerID(+))" _
                & " AND Customer.lngCustomerTypeID=CustomerType.lngCustomerTypeID(+))" _
                & " AND BudgetQuery1.lngEmployeeID=Employee.lngEmployeeID(+))" _
                & " AND Employee.lngEmployeeTypeID=EmployeeType.lngEmployeeTypeID(+))" _
                & " AND BudgetQuery1.lngClassID1=Class1.lngClassID(+))" _
                & " AND BudgetQuery1.lngClassID2=Class2.lngClassID(+))" _
                & " AND BudgetQuery1.lngDepartmentID=Department.lngDepartmentID(+))" _
                & " AND BudgetQuery1.lngJobID=Job.lngJobID(+) )" _
                & " AND BudgetQuery1.lngAreaID=Area.lngAreaID(+) " _
                & " AND " & strWhere
            If cboBudget.Text = "" Then
                strSql = Salary.Change_Text("[Year]", "1998", strSql)
            Else
                strSql = Salary.Change_Text("[Year]", cboBudget.Text, strSql)
            End If
            strSql = Salary.Change_Text("[BudgetID]", mlngBudgetID, strSql)
            Set rec3 = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
            With msgBudget(1)
                intOldCols = .Cols
                .Cols = .Cols + rec2.rdoColumns.Count - 11
                For j = intOldCols To intOldCols + rec2.rdoColumns.Count - 12
                    .TextMatrix(0, j) = rec2.rdoColumns(j - intOldCols).Name
                Next
                If Not rec2.EOF Then
                    rec2.MoveLast
                    rec2.MoveFirst
                    For i = 0 To rec2.RowCount - 1
                        For j = intOldCols To intOldCols + rec2.rdoColumns.Count - 12
                            .TextMatrix(i + 1, j) = IIf(IsNull(rec2.rdoColumns(j - intOldCols).Value), "", rec2.rdoColumns(j - intOldCols).Value)
                        Next
                        rec2.MoveNext
                    Next
                End If
                intOldCols = .Cols
                .Cols = .Cols + rec3.rdoColumns.Count - 11
                For j = intOldCols To intOldCols + rec3.rdoColumns.Count - 12
                    .TextMatrix(0, j) = rec3.rdoColumns(j - intOldCols).Name
                Next
                If Not rec3.EOF Then
                    rec3.MoveLast
                    rec3.MoveFirst
                    For i = 0 To rec3.RowCount - 1
                        For j = intOldCols To intOldCols + rec3.rdoColumns.Count - 12
                            .TextMatrix(i + 1, j) = IIf(IsNull(rec3.rdoColumns(j - intOldCols).Value), "", rec3.rdoColumns(j - intOldCols).Value)
                        Next
                        rec3.MoveNext
                    Next
                End If
            End With
        Else
'            strSql = "Select BudgetQuery.* From (((((((BudgetQuery LEFT JOIN Account" _
'                & " ON BudgetQuery.lngAccountID=Account.lngAccountID) LEFT JOIN AccountType" _
'                & " ON AccountType.lngAccountTypeID=Account.lngAccountTypeID) LEFT JOIN Customer" _
'                & " ON BudgetQuery.lngCustomerID=Customer.lngCustomerID) LEFT JOIN CustomerType" _
'                & " ON CustomerType.lngCustomerTypeID=Customer.lngCustomerTypeID) LEFT JOIN Employee" _
'                & " ON BudgetQuery.lngEmployeeID=Employee.lngEmployeeID) LEFT JOIN EmployeeType" _
'                & " ON EmployeeType.lngEmployeeTypeID=Employee.lngEmployeeTypeID) LEFT JOIN Class1" _
'                & " ON BudgetQuery.lngClassID1=Class1.lngClassID) LEFT JOIN Class2" _
'                & " ON BudgetQuery1.lngClassID2=Class2.lngClassID) LEFT JOIN Area" _
'                & " ON BudgetQuery1.lngAreaID=Area.lngAreaID Where " & strWhere
'            Set qryTemp = gclsBase.BaseDB.CreateQueryDef("", strSql)
'            With qryTemp
'                If cboBudget.Text = "" Then
'                    .Parameters("Year") = 1998
'                Else
'                    .Parameters("Year") = cboBudget.Text
'                End If
'                .Parameters("BudgetID") = mlngBudgetID
'                .Parameters("strDec") = IIf(gclsBase.GetCurrencyDec("人民币") = 0, "#,###,###,###", "999,999,999,999,999,999,999,999,990." + String(gclsBase.GetCurrencyDec("人民币"), "0"))
'                .Parameters("strQuantityDec") = IIf(gclsBase.QuantityDec = 0, "#,###,###,###", "999,999,999,999,999,999,999,999,990." + String(gclsBase.QuantityDec, "0"))
'                Set dtaBudget.Recordset = .OpenRecordset
'            End With

            'ID部分
            strBudgetQuerySql = TransferPublic.getBudgetQueryOraSql(0)
            strSql = "Select BudgetQuery.* From ( " & strBudgetQuerySql & " ) BudgetQuery," _
                & " Account,AccountType,Customer,CustomerType,Employee,EmployeeType,Class1,Class2," _
                & " Department,Currencys,Area " _
                & " WHERE (((((((((BudgetQuery.lngAccountID=Account.lngAccountID(+))" _
                & " AND Account.lngAccountTypeID=AccountType.lngAccountTypeID(+)) " _
                & " AND BudgetQuery.lngCustomerID=Customer.lngCustomerID(+)) " _
                & " AND Customer.lngCustomerTypeID=CustomerType.lngCustomerTypeID(+))" _
                & " AND BudgetQuery.lngEmployeeID=Employee.lngEmployeeID(+)) " _
                & " AND Employee.lngEmployeeTypeID=EmployeeType.lngEmployeeTypeID(+)) " _
                & " AND BudgetQuery.lngClassID1=Class1.lngClassID(+))" _
                & " AND BudgetQuery.lngClassID2=Class2.lngClassID(+) )" _
                & " AND BudgetQuery.lngDepartmentID=Department.lngDepartmentID(+))" _
                & " AND BudgetQuery.lngCurrencyID=Currencys.lngCurrencyID(+)" _
                & " AND " & strWhere
            If cboBudget.Text = "" Then
                strSql = Salary.Change_Text("[Year]", "1998", strSql)
            Else
                strSql = Salary.Change_Text("[Year]", cboBudget.Text, strSql)
            End If
            strSql = Salary.Change_Text("[BudgetID]", mlngBudgetID, strSql)
            strTmpDec = IIf(gclsBase.GetCurrencyDec("人民币") = 0, "'999,999,999,999,999,999'", "'999,999,999,999,999,990." + String(gclsBase.GetCurrencyDec("人民币"), "0")) + "'"
            strSql = Salary.Change_Text("[strDec]", strTmpDec, strSql)
            strTmpQuantityDec = IIf(gclsBase.QuantityDec = 0, "'999,999,999,999,999,999'", "'999,999,999,999,999,990." + String(gclsBase.QuantityDec, "0")) + "'"
            strSql = Salary.Change_Text("[strQuantityDec]", strTmpQuantityDec, strSql)
            
            strTmpsql = " SELECT MIN(bytCurrencyDec) AS CurrencyDec " _
                & " FROM BudgetBalance, Currencys" _
                & " WHERE BudgetBalance.lngCurrencyID=Currencys.lngCurrencyID(+)" _
                & " AND lngBudgetID = " & mlngBudgetID
            If cboBudget.Text = "" Then
                strTmpsql = strTmpsql & " And intYear = 1998 "
            Else
                strTmpsql = strTmpsql & " And intYear =" & cboBudget.Text
            End If
            Set recTmp = gclsBase.BaseDB.OpenResultset(strTmpsql, rdOpenStatic)
            If Not recTmp.EOF Then
                If IsNull(recTmp!CurrencyDec) Then
                    strTmpCurrencyDec = "'999,999,999,999,999,990.00'"
                Else
                    If recTmp!CurrencyDec = 0 Then
                        strTmpCurrencyDec = "'999,999,999,999,999,999'"
                    Else
                        strTmpCurrencyDec = "'999,999,999,999,999,990.'" & String(recTmp!CurrencyDec, "0")
                    End If
                End If
            Else
                strTmpCurrencyDec = "'999,999,999,999,999,990.00'"
            End If
            recTmp.Close
            Set recTmp = Nothing
            strSql = Salary.Change_Text("[CurrencyDec]", strTmpCurrencyDec, strSql)
            Set dtaBudget.Resultset = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
            '原币部分
            strBudgetQuerySql = TransferPublic.getBudgetQueryOraSql(1)
            strSql = "Select BudgetQuery.* From ( " & strBudgetQuerySql & " ) BudgetQuery," _
                & " Account,AccountType,Customer,CustomerType,Employee,EmployeeType,Class1,Class2," _
                & " Department,Currencys " _
                & " WHERE (((((((((BudgetQuery.lngAccountID=Account.lngAccountID(+))" _
                & " AND Account.lngAccountTypeID=AccountType.lngAccountTypeID(+)) " _
                & " AND BudgetQuery.lngCustomerID=Customer.lngCustomerID(+)) " _
                & " AND Customer.lngCustomerTypeID=CustomerType.lngCustomerTypeID(+))" _
                & " AND BudgetQuery.lngEmployeeID=Employee.lngEmployeeID(+)) " _
                & " AND Employee.lngEmployeeTypeID=EmployeeType.lngEmployeeTypeID(+)) " _
                & " AND BudgetQuery.lngClassID1=Class1.lngClassID(+))" _
                & " AND BudgetQuery.lngClassID2=Class2.lngClassID(+) )" _
                & " AND BudgetQuery.lngDepartmentID=Department.lngDepartmentID(+))" _
                & " AND BudgetQuery.lngCurrencyID=Currencys.lngCurrencyID(+)" _
                & " AND " & strWhere
            If cboBudget.Text = "" Then
                strSql = Salary.Change_Text("[Year]", "1998", strSql)
            Else
                strSql = Salary.Change_Text("[Year]", cboBudget.Text, strSql)
            End If
            strSql = Salary.Change_Text("[BudgetID]", mlngBudgetID, strSql)
            strSql = Salary.Change_Text("[strDec]", strTmpDec, strSql)
            strSql = Salary.Change_Text("[strQuantityDec]", strTmpQuantityDec, strSql)
            strSql = Salary.Change_Text("[CurrencyDec]", strTmpCurrencyDec, strSql)
            Set rec1 = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
            '本币部分
            strBudgetQuerySql = TransferPublic.getBudgetQueryOraSql(2)
            strSql = "Select BudgetQuery.* From ( " & strBudgetQuerySql & " ) BudgetQuery," _
                & " Account,AccountType,Customer,CustomerType,Employee,EmployeeType,Class1,Class2," _
                & " Department,Currencys " _
                & " WHERE (((((((((BudgetQuery.lngAccountID=Account.lngAccountID(+))" _
                & " AND Account.lngAccountTypeID=AccountType.lngAccountTypeID(+)) " _
                & " AND BudgetQuery.lngCustomerID=Customer.lngCustomerID(+)) " _
                & " AND Customer.lngCustomerTypeID=CustomerType.lngCustomerTypeID(+))" _
                & " AND BudgetQuery.lngEmployeeID=Employee.lngEmployeeID(+)) " _
                & " AND Employee.lngEmployeeTypeID=EmployeeType.lngEmployeeTypeID(+)) " _
                & " AND BudgetQuery.lngClassID1=Class1.lngClassID(+))" _
                & " AND BudgetQuery.lngClassID2=Class2.lngClassID(+) )" _
                & " AND BudgetQuery.lngDepartmentID=Department.lngDepartmentID(+))" _
                & " AND BudgetQuery.lngCurrencyID=Currencys.lngCurrencyID(+)" _
                & " AND " & strWhere
            If cboBudget.Text = "" Then
                strSql = Salary.Change_Text("[Year]", "1998", strSql)
            Else
                strSql = Salary.Change_Text("[Year]", cboBudget.Text, strSql)
            End If
            strSql = Salary.Change_Text("[BudgetID]", mlngBudgetID, strSql)
            strSql = Salary.Change_Text("[strDec]", strTmpDec, strSql)
            strSql = Salary.Change_Text("[strQuantityDec]", strTmpQuantityDec, strSql)
            strSql = Salary.Change_Text("[CurrencyDec]", strTmpCurrencyDec, strSql)
            Set rec2 = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
            '数量部分
            strBudgetQuerySql = TransferPublic.getBudgetQueryOraSql(3)
            strSql = "Select BudgetQuery.* From ( " & strBudgetQuerySql & " ) BudgetQuery," _
                & " Account,AccountType,Customer,CustomerType,Employee,EmployeeType,Class1,Class2," _
                & " Department,Currencys " _
                & " WHERE (((((((((BudgetQuery.lngAccountID=Account.lngAccountID(+))" _
                & " AND Account.lngAccountTypeID=AccountType.lngAccountTypeID(+)) " _
                & " AND BudgetQuery.lngCustomerID=Customer.lngCustomerID(+)) " _
                & " AND Customer.lngCustomerTypeID=CustomerType.lngCustomerTypeID(+))" _
                & " AND BudgetQuery.lngEmployeeID=Employee.lngEmployeeID(+)) " _
                & " AND Employee.lngEmployeeTypeID=EmployeeType.lngEmployeeTypeID(+)) " _
                & " AND BudgetQuery.lngClassID1=Class1.lngClassID(+))" _
                & " AND BudgetQuery.lngClassID2=Class2.lngClassID(+) )" _
                & " AND BudgetQuery.lngDepartmentID=Department.lngDepartmentID(+))" _
                & " AND BudgetQuery.lngCurrencyID=Currencys.lngCurrencyID(+)" _
                & " AND " & strWhere
            If cboBudget.Text = "" Then
                strSql = Salary.Change_Text("[Year]", "1998", strSql)
            Else
                strSql = Salary.Change_Text("[Year]", cboBudget.Text, strSql)