item.bas

金算盘软件代码

Public Function SelectReceiptSQL(ByVal ReceiptTypeID As Integer) As String
'作者：蔡奇科
'参数ReceiptTypeID：使用单据类型
'                    =26 代销调拨单,选择代销出库单据
'                    =26 代销调拨单,选择代销出库单据
    Dim SqlStr As String
    SqlStr = ""
    Select Case ReceiptTypeID
        Case 26 '代销调拨单
            SqlStr = "SELECT lngActivityDetailID, strDate AS 日期, strReceiptTypeName AS 单据类型," & _
                 "strReceiptNo || TO_CHAR(lngReceiptNo,'0000') AS 单据号," & _
                 "strItemCode || ' ' || strItemName || ' ' || strItemStyle AS 商品名称," & _
                 "strUnitName AS 单位," & _
                 "DECODE(SIGN(dblQuantity-dblSettlementQuantity+dblOutQuantity),-1,'-','') " & _
                 "|| TO_CHAR(FLOOR(ABS(dblQuantity-dblSettlementQuantity+dblOutQuantity)/dblFactor)) " & _
                 "|| DECODE(MOD(dblQuantity-dblSettlementQuantity+dblOutQuantity, dblFactor),0,'','.') " & _
                 "|| SUBSTR('00000000000000000000'|| TO_CHAR(ABS(MOD(dblQuantity-dblSettlementQuantity+dblOutQuantity, dblFactor))),-LENGTH(to_char(dblFactor-1))) AS 发出数量," & _
                 "ROUND(dblCurrAmount+dblCurrTaxAmount-dblCurrSettlementAmount+dblOutAmount,bytCurrencyDec) AS 发出金额 "
            SqlStr = SqlStr & _
                "FROM ItemActivityDetail,ItemActivity , ReceiptType,ItemUnit,Item ,Currencys ," & _
                "(SELECT lngOrderDetailID, dblQuantity AS dblOutQuantity, " & _
                "(dblCurrAmount+dblCurrTaxAmount) AS dblOutAmount, dblQuantity AS dblFpQuantity," & _
                "(dblCurrAmount+dblCurrTaxAmount) AS dblFpAmount, dblQuantity AS dblSingleQuantity," & _
                "dblSettlementQuantity AS dblSetQuantity, dblCurrSettlementAmount AS dblSetAmount, " & _
                "dblQuantity AS dblOrderQuantity " & _
                "FROM ItemActivityDetail " & _
                "Where ItemActivityDetail.lngActivityDetailID = [DetailID]) QItemActivityDetail "
            SqlStr = SqlStr & _
                "Where ItemActivity.blnIsVoid=0 AND ItemActivityDetail.lngActivityID=ItemActivity.lngActivityID AND " & _
                "ItemActivity.lngReceiptTypeID=ReceiptType.lngReceiptTypeID AND " & _
                "ItemActivityDetail.lngUnitID=ItemUnit.lngUnitID AND " & _
                "ItemActivityDetail.lngItemID=Item.lngItemID AND " & _
                "ItemActivity.lngCurrencyID=Currencys.lngCurrencyID AND " & _
                "ItemActivityDetail.lngActivityDetailID=QItemActivityDetail.lngOrderDetailID(+) AND " & _
                "ItemActivity.lngActivityTypeID IN (13,24,43) AND "
            SqlStr = SqlStr & "(Abs(dblCurrAmount + dblCurrTaxAmount - dblCurrSettlementAmount) > 0.00001 Or dblOutAmount <> 0) "
            SqlStr = SqlStr & "ItemActivity.lngCustomerID=[CustomerID] AND "
            SqlStr = SqlStr & "ItemActivity.lngCurrencyID=[CurrencyID] AND "
            SqlStr = SqlStr & "([ItemID]=0 OR ItemActivityDetail.lngItemID=[ItemID]) AND "
            
        Case Else
    End Select
End Function
Public Function ActivityExport1(ByVal strPath As String, intIndex As Integer, _
    ByVal intYear As Integer, ByVal intStartPeriod As Integer, ByVal intEndPeriod As Integer, Optional ByVal strWhere As String = "") As Boolean
    Dim strTemp As String
    Dim lngResult As Long
    Dim strSQL As String
    Dim recTemp As rdoResultset
    Dim intFileNum As Integer
    Dim intCount As Integer
    
    On Error GoTo errhandel
    Select Case intIndex
        Case 26
        '代销调拨单
            strTemp = "文件名=ItemActivity_" & intIndex & ".dat" & Chr(0) & "字段数=39"
            strTemp = strTemp & Chr(0) & "字段1=单据类型,1,1"
            strTemp = strTemp & Chr(0) & "字段2=业务类型,2,1"
            strTemp = strTemp & Chr(0) & "字段3=会计年度,3,1"
            strTemp = strTemp & Chr(0) & "字段4=会计期间,4,1"
            strTemp = strTemp & Chr(0) & "字段5=单据号,5,2"
            strTemp = strTemp & Chr(0) & "字段6=往来单位,6,2"
            strTemp = strTemp & Chr(0) & "字段7=制单日期,7,2"
            strTemp = strTemp & Chr(0) & "字段8=职员,8,2"
            strTemp = strTemp & Chr(0) & "字段9=部门,9,2"
            strTemp = strTemp & Chr(0) & "字段10=科目,10,2"
            strTemp = strTemp & Chr(0) & "字段11=汇率,11,5"
            strTemp = strTemp & Chr(0) & "字段12=币种,12,2"
            strTemp = strTemp & Chr(0) & "字段13=项目,13,2"
            strTemp = strTemp & Chr(0) & "字段14=统计,14,2"
            strTemp = strTemp & Chr(0) & "字段15=作废,15,2"
            strTemp = strTemp & Chr(0) & "字段16=备注,16,2"
            strTemp = strTemp & Chr(0) & "字段17=行号,17,4"
            strTemp = strTemp & Chr(0) & "字段18=商品,18,2"
            strTemp = strTemp & Chr(0) & "字段19=选择单据,19,2"
            strTemp = strTemp & Chr(0) & "字段20=计量单位,20,2"
            strTemp = strTemp & Chr(0) & "字段21=数量,21,5"
            strTemp = strTemp & Chr(0) & "字段22=原币单价,22,5"
            strTemp = strTemp & Chr(0) & "字段23=原币金额,23,5"
            strTemp = strTemp & Chr(0) & "字段24=本币金额,24,5"
            strTemp = strTemp & Chr(0) & "字段25=税率,25,2"
            strTemp = strTemp & Chr(0) & "字段26=原币税额,26,5"
            strTemp = strTemp & Chr(0) & "字段27=本币税额,27,5"
            strTemp = strTemp & Chr(0) & "字段28=工程,28,2"
            strTemp = strTemp & Chr(0) & "字段29=自定义项目0,29,2"
            strTemp = strTemp & Chr(0) & "字段30=自定义项目1,30,2"
            strTemp = strTemp & Chr(0) & "字段31=自定义项目2,31,2"
            strTemp = strTemp & Chr(0) & "字段32=自定义项目3,32,2"
            strTemp = strTemp & Chr(0) & "字段33=自定义项目4,33,2"
            strTemp = strTemp & Chr(0) & "字段34=自定义项目5,34,2"
            strTemp = strTemp & Chr(0) & "字段35=成本差异,35,5"
            strTemp = strTemp & Chr(0) & "字段36=成本金额,36,5"
            strTemp = strTemp & Chr(0) & "字段37=制单人,37,2"
            strTemp = strTemp & Chr(0) & "字段38=模板,38,2"
            strTemp = strTemp & Chr(0) & "字段39=待打印标志,39,3"
            strTemp = strTemp & Chr(0) & "导出日期=" & Format(Date, "yyyy-mm-dd")
            
            lngResult = WritePrivateProfileSection(ReceiptType(intIndex).strReceiptTypeName, strTemp, strPath)
            strSQL = "SELECT ItemActivity.lngReceiptTypeID, ItemActivity.lngActivityTypeID, ItemActivity.intYear, " & _
            "ItemActivity.bytPeriod, ltrim(ItemActivity.strReceiptNO || ltrim(to_char(ItemActivity.lngReceiptNO,'0000'))) strReceiptNO1, " & _
            "Customer.strCustomerCode, ItemActivity.strDate, Employee.strEmployeeCode, " & _
            "Department.strDepartmentCode, Account.strAccountCode, ItemActivity.dblRate, " & _
            "Currencys.strCurrencyCode, Class2.strClassCode, Class1.strClassCode, " & _
            "ItemActivity.blnIsVoid,ItemActivity.strNote, " & _
            "ItemActivityDetail.lngRowID, Item.strItemCode, "
            strSQL = strSQL & _
            "table_2.lngReceiptTypeID || ',' || table_2.lngActivityTypeID ||  ',' || Table_2.intYear || ',' ||  " & _
            "Table_2.bytPeriod || ',' || Table_2.strReceiptNO || ','||  " & _
            "Table_2.lngReceiptNO || ',' || Table_1.lngRowID XXXXX, "
            strSQL = strSQL & _
            "ItemUnit.strUnitName, " & _
            "ItemActivityDetail.dblQuantity, ItemActivityDetail.dblCurrPrice," & _
            "ItemActivityDetail.dblCurrAmount, " & _
            "ItemActivityDetail.dblAmount, Tax.strTaxName, ItemActivityDetail.dblCurrTaxAmount, " & _
            "ItemActivityDetail.dblTaxAmount," & _
            "JOb.strJobCode, Custom0.strCustomCode, Custom1.strCustomCode, " & _
            "Custom2.strCustomCode, Custom3.strCustomCode, Custom4.strCustomCode, Custom5.strCustomCode, " & _
            "ItemActivityDetail.dblCostDiff, " & _
            "ItemActivityDetail.dblCostAmount,Operator.strOperatorName,Template.strTemplateName,ItemActivity.blnIsPrint "
            strSQL = strSQL & "FROM ItemActivity,ItemActivityDetail,Customer,Employee,Department,Account,Currencys," & _
            "Class1,Class2,ItemActivity ItemActivity_1,Item,ItemActivityDetail Table_1,ItemActivity Table_2,ItemUnit," & _
            "Tax,JOb,Operator,Template,Custom0,Custom1,Custom2,Custom3,Custom4,Custom5 " & _
            "WHERE ItemActivity.lngActivityID = ItemActivityDetail.lngActivityID AND  " & _
            "ItemActivity.lngCustomerID = Customer.lngCustomerID AND  " & _
            "ItemActivity.lngEmployeeID = Employee.lngEmployeeID(+) AND  " & _
            "ItemActivity.lngDepartmentID = Department.lngDepartmentID(+) AND " & _
            "ItemActivity.lngAccountID = Account.lngAccountID(+) AND " & _
            "ItemActivity.lngCurrencyID = Currencys.lngCurrencyID AND " & _
            "ItemActivity.lngClassID2 = Class2.lngClassID(+) AND " & _
            "ItemActivity.lngClassID1 = Class1.lngClassID(+) AND " & _
            "ItemActivity.lngSourceActivityID = ItemActivity_1.lngActivityID(+) AND " & _
            "ItemActivityDetail.lngItemID = Item.lngItemID AND "
            strSQL = strSQL & _
            "ItemActivityDetail.lngOrderDetailID = Table_1.lngActivityDetailID(+) AND " & _
            "Table_1.lngActivityID = Table_2.lngActivityID(+) AND "
            strSQL = strSQL & _
            "" & _
            "ItemActivityDetail.lngUnitID = ItemUnit.lngUnitID(+) AND " & _
            "ItemActivityDetail.lngTaxID = Tax.lngTaxID(+) AND " & _
            "ItemActivityDetail.lngJobID = JOb.lngJobID(+) AND " & _
            "ItemActivity.lngOperatorID = Operator.lngOperatorID(+) AND " & _
            "ItemActivity.lngTemplateID = Template.lngTemplateID(+) AND " & _
            "ItemActivityDetail.lngCustomID0 = Custom0.lngCustomID(+) AND " & _
            "ItemActivityDetail.lngCustomID1 = Custom1.lngCustomID(+) AND " & _
            "ItemActivityDetail.lngCustomID2 = Custom2.lngCustomID(+) AND " & _
            "ItemActivityDetail.lngCustomID3 = Custom3.lngCustomID(+) AND " & _
            "ItemActivityDetail.lngCustomID4 = Custom4.lngCustomID(+) AND " & _
            "ItemActivityDetail.lngCustomID5 = Custom5.lngCustomID(+) AND "
            strSQL = strSQL & "(ItemActivity.lngReceiptTypeID)=" & intIndex
            If Trim(strWhere) <> "" Then
                strSQL = strSQL & " AND to_char(ItemActivity.lngReceiptTypeID) || to_char(ItemActivity.intYear) || to_Char(ItemActivity.bytPeriod) || to_char(ItemActivity.strReceiptNO) || to_char(ItemActivity.lngReceiptNO) IN " & strWhere
            End If
            strSQL = strSQL & " ORDER BY ItemActivity.lngActivityID,ItemActivityDetail.lngRowID"
            Set recTemp = gclsBase.BaseDB.OpenResultset(strSQL, rdOpenStatic)
            With recTemp
                intFileNum = FreeFile
                If Dir(GetFilePath(strPath) & "ItemActivity_" & intIndex & ".Dat") <> "" Then
                    Kill GetFilePath(strPath) & "ItemActivity_" & intIndex & ".Dat"
                End If
                Open GetFilePath(strPath) & "ItemActivity_" & intIndex & ".Dat" For Binary As #intFileNum
                Do While Not .EOF
                    strTemp = ""
                    For intCount = 0 To .rdoColumns.Count - 1
                        strTemp = strTemp & IIf(IsNull(.rdoColumns(intCount)), "" & Chr(9), .rdoColumns(intCount) & Chr(9))
                    Next
                    strTemp = strTemp & Chr(13) & Chr(10)
                    Put #intFileNum, , strTemp
                    .MoveNext
                Loop
                Close #intFileNum
            End With
        Case 28, 30, 31
        '商品调拨、车卸组装单
            strTemp = "文件名=ItemActivity_" & intIndex & ".dat" & Chr(0) & "字段数=35"
            strTemp = strTemp & Chr(0) & "字段1=单据类型,1,1"
            strTemp = strTemp & Chr(0) & "字段2=业务类型,2,1"
            strTemp = strTemp & Chr(0) & "字段3=会计年度,3,1"
            strTemp = strTemp & Chr(0) & "字段4=会计期间,4,1"
            strTemp = strTemp & Chr(0) & "字段5=单据号,5,2"
            strTemp = strTemp & Chr(0) & "字段6=制单日期,6,2"
            strTemp = strTemp & Chr(0) & "字段7=职员,7,2"
            strTemp = strTemp & Chr(0) & "字段8=部门,8,2"
            strTemp = strTemp & Chr(0) & "字段9=汇率,9,5"
            strTemp = strTemp & Chr(0) & "字段10=币种,10,2"
            strTemp = strTemp & Chr(0) & "字段11=项目,11,2"
            strTemp = strTemp & Chr(0) & "字段12=统计,12,2"
            strTemp = strTemp & Chr(0) & "字段13=作废,13,2"
            strTemp = strTemp & Chr(0) & "字段14=备注,14,2"
            strTemp = strTemp & Chr(0) & "字段15=行号,15,4"
            strTemp = strTemp & Chr(0) & "字段16=商品,16,2"
            strTemp = strTemp & Chr(0) & "字段17=货位,17,2"
            strTemp = strTemp & Chr(0) & "字段18=计量单位,18,2"
            strTemp = strTemp & Chr(0) & "字段19=数量,19,5"
            strTemp = strTemp & Chr(0) & "字段20=生产批号,20,5"
            strTemp = strTemp & Chr(0) & "字段21=生产日期,21,5"
            strTemp = strTemp & Chr(0) & "字段22=到期日期,22,5"
            strTemp = strTemp & Chr(0) & "字段23=保质期,23,2"
            strTemp = strTemp & Chr(0) & "字段24=自定义项目0,24,2"
            strTemp = strTemp & Chr(0) & "字段25=自定义项目1,25,2"
            strTemp = strTemp & Chr(0) & "字段26=自定义项目2,26,2"
            strTemp = strTemp & Chr(0) & "字段27=自定义项目3,27,2"
            strTemp = strTemp & Chr(0) & "字段28=自定义项目4,28,2"
            strTemp = strTemp & Chr(0) & "字段29=自定义项目5,29,2"
            strTemp = strTemp & Chr(0) & "字段30=批次商品INID5,30,2"
            strTemp = strTemp & Chr(0) & "字段31=成本差异,31,5"
            strTemp = strTemp & Chr(0) & "字段32=成本金额,32,5"
            strTemp = strTemp & Chr(0) & "字段33=制单人,33,2"
            strTemp = strTemp & Chr(0) & "字段34=模板,34,2"
            strTemp = strTemp & Chr(0) & "字段35=待打印标志,35,3"
            strTemp = strTemp & Chr(0) & "导出日期=" & Format(Date, "yyyy-mm-dd")
            
            lngResult = WritePrivateProfileSection(ReceiptType(intIndex).strReceiptTypeName, strTemp, strPath)
            strSQL = "SELECT ItemActivity.lngReceiptTypeID, ItemActivity.lngActivityTypeID, ItemActivity.intYear, " & _
            "ItemActivity.bytPeriod, LTRIM(ItemActivity.strReceiptNO || LTRIM(TO_CHAR(ItemActivity.lngReceiptNO,'0000'))), " & _
            "ItemActivity.strDate, Employee.strEmployeeCode, " & _
            "Department.strDepartmentCode, ItemActivity.dblRate, " & _
            "Currencys.strCurrencyCode, Class2.strClassCode, Class1.strClassCode, " & _
            "ItemActivity.blnIsVoid, ItemActivity.strNote, " & _
            "ItemActivityDetail.lngRowID, Item.strItemCode, "
            strSQL = strSQL & _
            "Position.strPositionCode, ItemUnit.strUnitName, " & _
            "ItemActivityDetail.dblQuantity, ItemActivityDetail.strProduceNum, " & _
            "ItemActivityDetail.strProduceDate, " & _
            "ItemActivityDetail.strValidDate,ItemActivityDetail.intValidDay," & _
            "Custom0.strCustomCode, Custom1.strCustomCode, " & _
            "Custom2.strCustomCode, Custom3.strCustomCode, Custom4.strCustomCode, Custom5.strCustomCode, " & _
            "ItemActivityDetail.lngActivityDetailID, ItemActivityDetail.dblCostDiff, " & _
            "ItemActivityDetail.dblCostAmount,Operator.strOperatorName,Template.strTemplateName,ItemActivity.blnIsPrint "
            strSQL = strSQL & " FROM ItemActivity,ItemActivityDetail,Employee,Department,Currencys,Class2,Class1,"
            strSQL = strSQL & "Item,Position,ItemUnit,Operator,Template,Custom0,Custom1,Custom2,Custom3,Custom4,Custom5 "
            
            strSQL = strSQL & " WHERE ((((((((((((((((" & _
            "ItemActivity.lngActivityID = ItemActivityDetail.lngActivityID) AND " & _
            "ItemActivity.lngEmployeeID = Employee.lngEmployeeID(+)) AND " & _
            "ItemActivity.lngDepartmentID = Department.lngDepartmentID(+)) AND " & _
            "ItemActivity.lngCurrencyID = Currencys.lngCurrencyID) AND " & _
            "ItemActivity.lngClassID2 = Class2.lngClassID(+)) AND " & _
            "ItemActivity.lngClassID1 = Class1.lngClassID(+)) AND "
            strSQL = strSQL & "ItemActivityDetail.lngItemID = Item.lngItemID) AND " & _
            "ItemActivityDetail.lngPositionID = Position.lngPositionID(+)) AND " & _
            "ItemActivityDetail.lngUnitID = ItemUnit.lngUnitID(+)) AND " & _
            "ItemActivity.lngOperatorID = Operator.lngOperatorID(+)) AND " & _
            "ItemActivity.lngTemplateID = Template.lngTemplateID(+)) AND " & _
            "ItemActivityDetail.lngCustomID0 = Custom0.lngCustomID(+)) AND " & _
            "ItemActivityDetail.lngCustomID1 = Custom1.lngCustomID(+)) AND " & _
            "ItemActivityDetail.lngCustomID2 = Custom2.lngCustomID(+)) AND " & _
            "ItemActivityDetail.lngCustomID3 = Custom3.lngCustomID(+)) AND " & _
            "ItemActivityDetail.lngCustomID4 = Custom4.lngCustomID(+)) AND " & _
            "ItemActivityDetail.lngCustomID5 = Custom5.lngCustomID(+) AND "

            strSQL = strSQL & "(ItemActivity.lngReceiptTypeID)=" & intIndex
            If Trim(strWhere) <> "" Then
'                If intIndex = 28 Then
                    strSQL = strSQL & " AND to_char(ItemActivity.lngReceiptTypeID) || to_char(ItemActivity.intYear) || to_Char(ItemActivity.bytPeriod) || to_char(ItemActivity.strReceiptNO) || to_char(ItemActivity.lngReceiptNO) IN " & strWhere
'                Else
'                    strSql = strSql & " AND ItemActivity.lngActivityID IN " & strWhere