budgetcard.frm

来自「金算盘软件代码」· FRM 代码 · 共 1,590 行 · 第 1/5 页

    strFrom = ""
    strWhere = ""
    
    '是否余额数
    blnBalance(0) = False
    blnBalance(1) = False
    blnBalance(2) = False
    '数据来源(1=业务单据,2=发生额表,3=采购订单,4=销售订单)
    bytSource(0) = 1
    bytSource(1) = 1
    bytSource(2) = 1
    
    If recBudget!bytType = 0 Then
        strFrom = " AccountDaily DataDaily"
    Else
        'strFrom = "(ItemActivity DataDaily INNER JOIN ItemActivityDetail ON DataDaily.lngActivityID=ItemActivityDetail.lngActivityID)"
        strFrom = " ItemActivity DataDaily,ItemActivityDetail"
        strWhere = "(DataDaily.lngActivityID=ItemActivityDetail.lngActivityID)"
        'strFromCM = "(ItemActivity DataDaily INNER JOIN ItemActivityDetail ON DataDaily.lngActivityID=ItemActivityDetail.lngActivityID)"
        strFromCM = " ItemActivity DataDaily,ItemActivityDetail"
        strWhereCM = "(DataDaily.lngActivityID=ItemActivityDetail.lngActivityID)"
        'strFromPO = "(PurchaseOrder DataDaily INNER JOIN PurchaseOrderDetail ON DataDaily.lngPurchaseOrderID=PurchaseOrderDetail.lngPurchaseOrderID)"
        strFromPO = " PurchaseOrder DataDaily,PurchaseOrderDetail"
        strWherePO = "(DataDaily.lngPurchaseOrderID=PurchaseOrderDetail.lngPurchaseOrderID)"
        'strFromSO = "(SaleOrder DataDaily INNER JOIN SaleOrderDetail ON DataDaily.lngSaleOrderID=SaleOrderDetail.lngSaleOrderID)"
        strFromSO = " SaleOrder DataDaily,SaleOrderDetail"
        strWhereSO = "(DataDaily.lngSaleOrderID=SaleOrderDetail.lngSaleOrderID)"
    End If
    
    '科目
    If lstBudget(0).ID > 0 Then
        strSql = "SELECT strAccountCode,intDirection,blnIsDetail FROM Account WHERE lngAccountID=" & lstBudget(0).ID
        Set recCode = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
        If Not recCode.EOF Then
            intDirection = recCode!intDirection
            'If recCode!blnIsDetail Then
            If recCode!blnIsDetail = 1 Then
                strCode = recCode!strAccountCode
                If strWhere = "" Then
                    'strWhere = "Account.blnIsDetail AND Account.strAccountCode='" & strCode & "'"
                    strWhere = "Account.blnIsDetail=1 AND Account.strAccountCode='" & strCode & "'"
                Else
                    'strWhere = strWhere & " AND Account.blnIsDetail AND Account.strAccountCode='" & strCode & "'"
                    strWhere = strWhere & " AND Account.blnIsDetail=1 AND Account.strAccountCode='" & strCode & "'"
                End If
            Else
                strCode = recCode!strAccountCode & "-%"
                If strWhere = "" Then
                    strWhere = "Account.blnIsDetail=1 AND Account.strAccountCode Like '" & strCode & "'"
                Else
                    strWhere = strWhere & " AND Account.blnIsDetail=1 AND Account.strAccountCode Like '" & strCode & "'"
                End If
            End If
            'strFrom = "(" & strFrom & " INNER JOIN Account ON DataDaily.lngAccountID=Account.lngAccountID" & ")"
            strFrom = strFrom & ",Account "
            strWhere = strWhere & " AND (DataDaily.lngAccountID=Account.lngAccountID) "
        End If
        recCode.Close
    End If
    
    '商品
    If lstBudget(1).ID > 0 Then
        If strWhere = "" Then
            strWhere = "ItemActivityDetail.lngItemID=" & lstBudget(1).ID
        Else
            strWhere = strWhere & " AND ItemActivityDetail.lngItemID=" & lstBudget(1).ID
        End If
    End If
    
    '商品类型
    If lstBudget(10).ID > 0 Then
        strSql = "SELECT strItemTypeCode,blnIsDetail FROM ItemType WHERE lngItemTypeID=" & lstBudget(10).ID
        Set recCode = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
        If Not recCode.EOF Then
            If recCode!blnIsDetail = 1 Then
                strCode = recCode!strItemTypeCode
                If strWhere = "" Then
                    strWhere = "ItemType.blnIsDetail=1 AND ItemType.strItemTypeCode='" & strCode & "'"
                Else
                    strWhere = strWhere & " AND ItemType.blnIsDetail=1 AND ItemType.strItemTypeCode='" & strCode & "'"
                End If
            Else
                strCode = recCode!strItemTypeCode & "-%"
                If strWhere = "" Then
                    strWhere = "ItemType.blnIsDetail=1 AND ItemType.strItemTypeCode Like '" & strCode & "'"
                Else
                    strWhere = strWhere & " AND ItemType.blnIsDetail=1 AND ItemType.strItemTypeCode Like '" & strCode & "'"
                End If
            End If
        End If
        recCode.Close
        strFrom = strFrom & " ,Item,ItemType"
    End If
    
    '单位
    If lstBudget(2).ID > 0 Then
        If strWhere = "" Then
            strWhere = "DataDaily.lngCustomerID=" & lstBudget(2).ID
        Else
            strWhere = strWhere & " AND DataDaily.lngCustomerID=" & lstBudget(2).ID
        End If
    End If
    
    '单位类型
    If lstBudget(11).ID > 0 Then
        'strFrom = "(" & strFrom & " INNER JOIN (Customer INNER JOIN CustomerType ON Customer.lngCustomerTypeID=CustomerType.lngCustomerTypeID) ON DataDaily.lngCustomerID=Customer.lngCustomerID" & ")"
        strFrom = strFrom & ",Customer,CustomerType"
        If strWhere = "" Then
            strWhere = "Customer.lngCustomerTypeID=" & lstBudget(11).ID
        Else
            strWhere = strWhere & " AND Customer.lngCustomerTypeID=" & lstBudget(11).ID
        End If
        strWhere = strWhere & " AND Customer.lngCustomerTypeID=CustomerType.lngCustomerTypeID AND DataDaily.lngCustomerID=Customer.lngCustomerID"
    End If
    
    '部门
    If lstBudget(3).ID > 0 Then
        strSql = "SELECT strDepartmentCode,blnIsDetail FROM Department WHERE lngDepartmentID=" & lstBudget(3).ID
        Set recCode = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
        If Not recCode.EOF Then
            If recCode!blnIsDetail = 1 Then
                strCode = recCode!strDepartmentCode
                If strWhere = "" Then
                    strWhere = "Department.strDepartmentCode='" & strCode & "'"
                Else
                    strWhere = strWhere & " AND Department.strDepartmentCode='" & strCode & "'"
                End If
            Else
                strCode = recCode!strDepartmentCode & "-%"
                If strWhere = "" Then
                    strWhere = "Department.strDepartmentCode Like '" & strCode & "'"
                Else
                    strWhere = strWhere & " AND Department.strDepartmentCode Like '" & strCode & "'"
                End If
            End If
        End If
        'strFrom = "(" & strFrom & " INNER JOIN Department ON DataDaily.lngDepartmentID=Department.lngDepartmentID" & ")"
        strFrom = strFrom & ",Department"
        strWhere = strWhere & " AND DataDaily.lngDepartmentID=Department.lngDepartmentID"
    End If
        
    '职员
    If lstBudget(4).ID > 0 Then
        If strWhere = "" Then
            strWhere = "DataDaily.lngEmployeeID=" & lstBudget(4).ID
        Else
            strWhere = strWhere & " AND DataDaily.lngEmployeeID=" & lstBudget(4).ID
        End If
    End If
        
    '工程
    If lstBudget(5).ID > 0 Then
        If strWhere = "" Then
            strWhere = "ItemActivityDetail.lngJobID=" & lstBudget(5).ID
        Else
            strWhere = strWhere & " AND ItemActivityDetail.lngJobID=" & lstBudget(5).ID
        End If
        'strFrom = "(" & strFrom & " INNER JOIN Job ON ItemActivityDetail.lngJobID=Job.lngJobID" & ")"
        strFrom = strFrom & ",Job "
        strWhere = strWhere & " AND ItemActivityDetail.lngJobID=Job.lngJobID"
    End If
        
    '统计
    If lstBudget(6).ID > 0 Then
        strSql = "SELECT strClassCode,blnIsDetail FROM Class1 WHERE lngClassID=" & lstBudget(6).ID
        Set recCode = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
        If Not recCode.EOF Then
            'If recCode!blnIsDetail Then
            If recCode!blnIsDetail = 1 Then
                strCode = recCode!strClassCode
                If strWhere = "" Then
                    strWhere = "Class1.strClassCode='" & strCode & "'"
                Else
                    strWhere = strWhere & " AND Class1.strClassCode='" & strCode & "'"
                End If
            Else
                strCode = recCode!strClassCode & "-%"
                If strWhere = "" Then
                    strWhere = "Class1.strClassCode Like '" & strCode & "'"
                Else
                    strWhere = strWhere & " AND Class1.strClassCode Like '" & strCode & "'"
                End If
            End If
        End If
        'strFrom = "(" & strFrom & " INNER JOIN Class1 ON DataDaily.lngClassID1=Class1.lngClassID" & ")"
        strFrom = strFrom & ",Class1"
        strWhere = strWhere & " AND DataDaily.lngClassID1=Class1.lngClassID"
    End If
        
    '项目
    If lstBudget(7).ID > 0 Then
        strSql = "SELECT strClassCode,blnIsDetail FROM Class2 WHERE lngClassID=" & lstBudget(7).ID
        Set recCode = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
        If Not recCode.EOF Then
            'If recCode!blnIsDetail Then
            If recCode!blnIsDetail = 1 Then
                If strWhere = "" Then
                    strWhere = "Class2.strClassCode='" & strCode & "'"
                Else
                    strWhere = strWhere & " AND Class2.strClassCode='" & strCode & "'"
                End If
            Else
                strCode = recCode!strClassCode & "-%"
                If strWhere = "" Then
                    strWhere = "Class2.strClassCode Like '" & strCode & "'"
                Else
                    strWhere = strWhere & " AND Class2.strClassCode Like '" & strCode & "'"
                End If
            End If
        End If
        'strFrom = "(" & strFrom & " INNER JOIN Class2 ON DataDaily.lngClassID2=Class2.lngClassID" & ")"
        strFrom = strFrom & ",Class2"
        strWhere = strWhere & " AND DataDaily.lngClassID2=Class2.lngClassID"
    End If
        
    '币种
    If lstBudget(8).ID > 0 Then
        If strWhere = "" Then
            strWhere = "DataDaily.lngCurrencyID=" & lstBudget(8).ID
        Else
            strWhere = strWhere & " AND DataDaily.lngCurrencyID=" & lstBudget(8).ID
        End If
    End If
    
    '地区
    If lstBudget(12).ID > 0 Then
        If InStr(UCase(strFrom), UCase("Customer")) > 0 Then
            strFrom = strFrom & ",Area"
        Else
            strFrom = strFrom & ",Customer,Area"
        End If
        If strWhere = "" Then
            strWhere = "Customer.lngAreaID=" & lstBudget(12).ID
        Else
            strWhere = strWhere & " AND Customer.lngAreaID=" & lstBudget(12).ID
        End If
        strWhere = strWhere & " AND Customer.lngAreaID=Area.lngAreaID AND DataDaily.lngCustomerID=Customer.lngCustomerID"
    End If
    If strWhere = "" Then
        Exit Sub
    End If
    
    If mintFlag = 1 Then
        strSelect = "AccountPeriod.bytPeriod"
        strSelectPO = "AccountPeriod.bytPeriod"
        strSelectSO = "AccountPeriod.bytPeriod"
        'strWhere = strWhere & " AND (isNull(AccountPeriod.bytPeriod) OR AccountPeriod.intYear=" & cboYear.Text & ")"
        strWhere = strWhere & " AND (NVL(AccountPeriod.bytPeriod,-1)=-1 OR AccountPeriod.intYear=" & cboYear.Text & ")"
        '经营预算
        For intCount = 0 To 2
            Select Case mstrObjectName(intCount)
            Case "采购数量"
                bytSource(intCount) = 1
                'strSelect = strSelect & ",SUM(IIF(lngActivityTypeID In (1,2),ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
                strSelect = strSelect & ",SUM(DECODE(lngActivityTypeID,1,ItemActivityDetail.dblQuantity,2,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
            Case "销售数量"
                bytSource(intCount) = 1
                'strSelect = strSelect & ",SUM(IIF(lngActivityTypeID IN (11,12,14,17),ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
                strSelect = strSelect & ",SUM(DECODE(lngActivityTypeID,11,ItemActivityDetail.dblQuantity,12,ItemActivityDetail.dblQuantity,14,ItemActivityDetail.dblQuantity,17,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
            Case "销售成本"
                bytSource(intCount) = 1
                'strSelect = strSelect & ",SUM(IIF(lngActivityTypeID IN (11,12,14,17),dblCostAmount,0)) As " & mstrObjectName(intCount)
                strSelect = strSelect & ",SUM(DECODE(lngActivityTypeID,11,dblCostAmount,12,dblCostAmount,14,dblCostAmount,17,dblCostAmount,0)) As " & mstrObjectName(intCount)
            Case "库存数量"
                bytSource(intCount) = 1
                blnBalance(intCount) = True
                'strSelect = strSelect & ",SUM(IIF(lngActivityTypeID IN (1,3,5,8,9,10,30,32,41),ItemActivityDetail.dblQuantity,0)-IIF(lngActivityTypeID IN (11,14,15,16,19,21,22,31,33),ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
                strSelect = strSelect & ",SUM(DECODE(lngActivityTypeID,1,ItemActivityDetail.dblQuantity,3,ItemActivityDetail.dblQuantity,5,ItemActivityDetail.dblQuantity,8,ItemActivityDetail.dblQuantity,9,ItemActivityDetail.dblQuantity,10,ItemActivityDetail.dblQuantity,30,ItemActivityDetail.dblQuantity,32,ItemActivityDetail.dblQuantity,41,ItemActivityDetail.dblQuantity,0)- " _
                    & " DECODE(lngActivityTypeID,11,ItemActivityDetail.dblQuantity,14,ItemActivityDetail.dblQuantity,15,ItemActivityDetail.dblQuantity,16,ItemActivityDetail.dblQuantity,19,ItemActivityDetail.dblQuantity,21,ItemActivityDetail.dblQuantity,22,ItemActivityDetail.dblQuantity,31,ItemActivityDetail.dblQuantity,33,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
            Case "受托数量"
                bytSource(intCount) = 1
                blnBalance(intCount) = True
                'strSelect = strSelect & ",SUM(IIF(lngActivityTypeID IN (3,42),ItemActivityDetail.dblQuantity,0)-IIF(lngActivityTypeID=4,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
                strSelect = strSelect & ",SUM(DECODE(lngActivityTypeID,3,ItemActivityDetail.dblQuantity,42,ItemActivityDetail.dblQuantity,0)-DECODE(lngActivityTypeID,4,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
            Case "委托数量"
                bytSource(intCount) = 1
                blnBalance(intCount) = True
                'strSelect = strSelect & ",SUM(IIF(lngActivitytypeID IN (13,43),ItemActivityDetail.dblQuantity,0)-IIF(lngActivitytypeID=14,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
                strSelect = strSelect & ",SUM(DECODE(lngActivitytypeID,13,ItemActivityDetail.dblQuantity,43,ItemActivityDetail.dblQuantity,0)-DECODE(lngActivitytypeID,14,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
            Case "分期数量"
                bytSource(intCount) = 1
                blnBalance(intCount) = True
                'strSelect = strSelect & ",SUM(IIF(lngActivitytypeID IN (16,44),ItemActivityDetail.dblQuantity,0)-IIF(lngActivitytypeID=17,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
                strSelect = strSelect & ",SUM(DECODE(lngActivitytypeID,16,ItemActivityDetail.dblQuantity,44,ItemActivityDetail.dblQuantity,0)-DECODE(lngActivitytypeID,17,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
            Case "加工数量"
                bytSource(intCount) = 1
                blnBalance(intCount) = True
                'strSelect = strSelect & ",SUM(IIF(lngActivitytypeID IN (15,46),ItemActivityDetail.dblQuantity,0)-IIF(IsNull(EntrustInToOut.dblQuantity),0,EntrustInToOut.dblQuantity)) As " & mstrObjectName(intCount)
                strSelect = strSelect & ",SUM(DECODE(lngActivitytypeID,15,ItemActivityDetail.dblQuantity,46,ItemActivityDetail.dblQuantity,0)-NVL(EntrustInToOut.dblQuantity,0)) As " & mstrObjectName(intCount)
                'If InStr(strFrom, "LEFT JOIN EntrustInToOut") = 0 Then
                If InStr(UCase(strFrom), UCase("EntrustInToOut")) = 0 Then
                    'strFrom = "(" & strFrom & " LEFT JOIN EntrustInToOut ON ItemActivityDetail.lngActivityDetailID=EntrustInToOut.lngOutActivityDetailID" & ")"
                    strFrom = strFrom & ",EntrustInToOut"
                    strWhere = strWhere & " AND ItemActivityDetail.lngActivityDetailID=EntrustInToOut.lngOutActivityDetailID(+)"
                End If
            Case "领用数量"
                bytSource(intCount) = 1
                'strSelect = strSelect & ",SUM(IIF(lngActivityTypeID=19,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
                strSelect = strSelect & ",SUM(DECODE(lngActivityTypeID,19,ItemActivityDetail.dblQuantity,0)) As " & mstrObjectName(intCount)
            Case "采购金额"
                bytSource(intCount) = 1
                'If recBudget!blnIsTax Then
                If recBudget!blnIsTax = 1 Then
                    'strSelect = strSelect & ",SUM(IIF(lngActivityTypeID IN (1,2),ItemActivityDetail.dblAmount+dblTaxAmount,0)) As " & mstrObjectName(intCount)
                    strSelect = strSelect & ",SUM(DECODE(lngActivityTypeID,1,ItemActivityDetail.dblAmount+dblTaxAmount,2,ItemActivityDetail.dblAmount+dblTaxAmount,0)) As " & mstrObjectName(intCount)
                Else
                    'strSelect = strSelect & ",SUM(IIF(lngActivityTypeID IN (1,2),ItemActivityDetail.dblAmount,0)) As " & mstrObjectName(intCount)
                    strSelect = strSelect & ",SUM(DECODE(lngActivityTypeID,1,ItemActivityDetail.dblAmount,2,ItemActivityDetail.dblAmount,0)) As " & mstrObjectName(intCount)
                End If
            Case "销售收入"
                bytSource(intCount) = 1
                'If recBudget!blnIsTax Then
                If recBudget!blnIsTax = 1 Then
                    'strSelect = strSelect & ",SUM(IIF(lngActivityTypeID IN (11,12,14,17),ItemActivityDetail.dblAmount+dblTaxAmount,0)) As " & mstrObjectName(intCount)
                    strSelect = strSelect & ",SUM(DECODE(lngActivityTypeID,11,ItemActivityDetail.dblAmount+dblTaxAmount,12,ItemActivityDetail.dblAmount+dblTaxAmount,14,ItemActivityDetail.dblAmount+dblTaxAmount,17,ItemActivityDetail.dblAmount+dblTaxAmount,0)) As " & mstrObjectName(intCount)
                Else
                    'strSelect = strSelect & ",SUM(IIF(lngActivityTypeID IN (11,12,14,17),ItemActivityDetail.dblAmount,0)) As " & mstrObjectName(intCount)
                    strSele