frminvoiceselect.frm

金算盘软件代码

    If lngReceiptTypeID = 8 Then
        strSql = "SELECT lngActivityDetailID,strDate AS 日期,strReceiptTypeName AS 单据类型," & _
            "Ltrim(strReceiptNo)||LPAD(lngReceiptNo,4,'0') AS 单据号,strItemCode||' '||strItemName||' '||strItemStyle AS 商品或劳务名称," & _
            "strUnitName AS 单位, ConvertQuantity(dblQuantity-dblInvoiceQuantity+NVL(dblFpQuantity,0),dblFactor) AS 未开票数量," & _
            "ConvertAmount(dblCurrAmount+dblCurrTaxAmount-dblCurrInvoiceAmount+NVL(dblFpAmount,0),bytCurrencyDec,0) AS 未开票金额," & _
            "dblFpQuantity AS 本次开票数量, dblFpAmount AS 本次开票金额, Decode(ItemActivityDetail.blnCloseInvoice,1,'√',' ') AS 关闭," & _
            "ItemUnit.dblFactor, ItemActivityDetail.lngItemID, ItemActivity.lngVoucherID, ItemActivityDetail.dblCurrAmount," & _
            "ItemActivityDetail.dblCurrTaxAmount, ItemActivityDetail.dblAmount, ItemActivityDetail.dblTaxAmount," & _
            "ItemActivityDetail.dblCurrInvoiceAmount, Tax.strTaxName AS strTax, Job.strJobCode||' '||Job.strJobName AS strJob," & _
            "Custom0.strCustomCode||' '||Custom0.strCustomName AS strCustom0," & _
            "Custom1.strCustomCode||' '||Custom1.strCustomName AS strCustom1," & _
            "Custom2.strCustomCode||' '||Custom2.strCustomName AS strCustom2," & _
            "Custom3.strCustomCode||' '||Custom3.strCustomName AS strCustom3," & _
            "Custom4.strCustomCode||' '||Custom4.strCustomName AS strCustom4," & _
            "Custom5.strCustomCode||' '||Custom5.strCustomName AS strCustom5," & _
            "ItemActivityDetail.strProduceNum, ItemActivityDetail.strProduceDate, " & _
            "ItemActivityDetail.strValidDate, ItemActivityDetail.intValidDay, ItemActivityDetail.lngUnitID," & _
            "ItemActivityDetail.lngTaxID, ItemActivityDetail.lngJobID," & _
            "ItemActivityDetail.lngCustomID0, ItemActivityDetail.lngCustomID1," & _
            "ItemActivityDetail.lngCustomID2, ItemActivityDetail.lngCustomID3," & _
            "ItemActivityDetail.lngCustomID4, ItemActivityDetail.lngCustomID5," & _
            "ItemActivityDetail.dblCurrPriceTax, Tax.dblPurchaseTaxRate, ItemActivityDetail.dblCurrPrice,"
'            "(ItemActivity.lngVoucherID<>0 AND ItemActivity.blnNoInvoice=0 AND ItemActivity.lngActivityTypeID IN (1,47)) AS blnNoChange "
        strSql = strSql & "Decode(ItemActivity.lngVoucherID,-1,0,0,0,1)*" & _
            "(1-ItemActivity.blnNoInvoice)*Decode(ItemActivity.lngActivityTypeID,1,1,47,1,0) AS blnNoChange "
            
        strSql = strSql & " FROM ItemActivityDetail,ItemActivity,ReceiptType,ItemUnit,Item,Currencys,Tax,Job," & _
            "Custom0,Custom1,Custom2,Custom3,Custom4,Custom5, "
        strSql = strSql & "(SELECT PurchaseToInvoice.lngReceiptDetailID AS lngOrderDetailID," & _
            " SUM(PurchaseToInvoice.dblQuantity) AS dblFpQuantity, SUM(PurchaseToInvoice.dblCurrAmount) AS dblFpAmount " & _
            " FROM PurchaseToInvoice,ItemActivityDetail " & _
            " WHERE PurchaseToInvoice.lngInvoiceDetailID = ItemActivityDetail.lngActivityDetailID "
        If mlngRowNO <> 0 Then
            strSql = strSql & " AND ItemActivityDetail.lngActivityDetailID = " & C2lng(frmName.TextOfGrid(mlngRowNO, 0))
        Else
            strSql = strSql & " AND ItemActivityDetail.lngActivityID = " & frmName.getID
        End If
        strSql = strSql & " GROUP BY PurchaseToInvoice.lngReceiptDetailID) QPurchaseInvoice "
        
        strSql = strSql & " WHERE 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.lngTaxID = Tax.lngTaxID(+) " & _
            " AND ItemActivityDetail.lngJobID = Job.lngJobID(+) " & _
            " 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 ItemActivityDetail.lngActivityDetailID=QPurchaseInvoice.lngOrderDetailID(+) "
        
        strSql = strSql & " AND ItemActivity.lngCustomerID=" & C2lng(frmName.lblHead(0).Tag) & _
            " AND ItemActivity.lngCurrencyID=" & frmName.getFieldID(7) & _
            " AND (Abs(dblCurrAmount+dblCurrTaxAmount-dblCurrInvoiceAmount+NVL(dblFpAmount,0))>0.000000001 " & _
            " OR Abs(dblCurrAmount+dblCurrTaxAmount-dblCurrInvoiceAmount)>0.000000001) AND blnIsVoid=0 " & _
            " AND (((ItemActivity.lngVoucherID<=0 OR ItemActivity.blnNoInvoice<>0) AND ItemActivity.lngActivityTypeID IN (1,47)) " & _
            " OR (ItemActivity.lngVoucherID>0 AND ItemActivity.blnNoInvoice=0 AND dblFPAmount<>0 AND ItemActivity.lngActivityTypeID IN (1,47)) " & _
            " OR (ItemActivity.lngActivityTypeID IN (2,4,6))) " & _
            " AND (ItemActivityDetail.blnCloseInvoice=0 OR dblCurrInvoiceAmount=0 OR dblFpAmount<>0) "
        If mlngRowNO <> 0 Then
            strSql = strSql & " AND Item.lngItemID=" & C2lng(frmName.TextOfGrid(mlngRowNO, 28))
        End If
    Else    '销售
        strSql = "SELECT lngActivityDetailID,strDate AS 日期,strReceiptTypeName AS 单据类型," & _
            "Ltrim(strReceiptNo)||LPAD(lngReceiptNo,4,'0') AS 单据号,strItemCode||' '||strItemName||' '||strItemStyle AS 商品或劳务名称," & _
            "strUnitName AS 单位, ConvertQuantity(dblQuantity-dblInvoiceQuantity+NVL(dblFpQuantity,0),dblFactor) AS 未开票数量," & _
            "ConvertAmount(dblCurrAmount+dblCurrTaxAmount-dblCurrInvoiceAmount+NVL(dblFpAmount,0),bytCurrencyDec,0) AS 未开票金额," & _
            "dblFpQuantity AS 本次开票数量, dblFpAmount AS 本次开票金额, Decode(ItemActivityDetail.blnCloseInvoice,1,'√',' ') AS 关闭," & _
            "ItemUnit.dblFactor, ItemActivityDetail.lngItemID, ItemActivity.lngVoucherID, ItemActivityDetail.dblCurrAmount," & _
            "ItemActivityDetail.dblCurrTaxAmount, ItemActivityDetail.dblAmount, ItemActivityDetail.dblTaxAmount," & _
            "ItemActivityDetail.dblCurrInvoiceAmount, Tax.strTaxName AS strTax, Job.strJobCode||' '||Job.strJobName AS strJob," & _
            "Custom0.strCustomCode||' '||Custom0.strCustomName AS strCustom0," & _
            "Custom1.strCustomCode||' '||Custom1.strCustomName AS strCustom1," & _
            "Custom2.strCustomCode||' '||Custom2.strCustomName AS strCustom2," & _
            "Custom3.strCustomCode||' '||Custom3.strCustomName AS strCustom3," & _
            "Custom4.strCustomCode||' '||Custom4.strCustomName AS strCustom4," & _
            "Custom5.strCustomCode||' '||Custom5.strCustomName AS strCustom5," & _
            "ItemActivityDetail.strProduceNum, ItemActivityDetail.strProduceDate, " & _
            "ItemActivityDetail.strValidDate, ItemActivityDetail.intValidDay, ItemActivityDetail.lngUnitID," & _
            "ItemActivityDetail.lngTaxID, ItemActivityDetail.lngJobID," & _
            "ItemActivityDetail.lngCustomID0, ItemActivityDetail.lngCustomID1," & _
            "ItemActivityDetail.lngCustomID2, ItemActivityDetail.lngCustomID3," & _
            "ItemActivityDetail.lngCustomID4, ItemActivityDetail.lngCustomID5," & _
            "ItemActivityDetail.dblCurrPriceTax, Tax.dblSaleTaxRate, ItemActivityDetail.dblCurrPrice,"
        strSql = strSql & "0 AS blnNoChange "
            
        strSql = strSql & " FROM ItemActivityDetail,ItemActivity,ReceiptType,ItemUnit,Item,Currencys,Tax,Job," & _
            "Custom0,Custom1,Custom2,Custom3,Custom4,Custom5, "
        strSql = strSql & "(SELECT SaleToInvoice.lngReceiptDetailID AS lngOrderDetailID," & _
            " SUM(SaleToInvoice.dblQuantity) AS dblFpQuantity, SUM(SaleToInvoice.dblCurrAmount) AS dblFpAmount " & _
            " FROM SaleToInvoice,ItemActivityDetail " & _
            " WHERE SaleToInvoice.lngInvoiceDetailID = ItemActivityDetail.lngActivityDetailID "
        If mlngRowNO <> 0 Then
            strSql = strSql & " AND ItemActivityDetail.lngActivityDetailID = " & C2lng(frmName.TextOfGrid(mlngRowNO, 0))
        Else
            strSql = strSql & " AND ItemActivityDetail.lngActivityID = " & frmName.getID
        End If
        strSql = strSql & " GROUP BY SaleToInvoice.lngReceiptDetailID) QSaleInvoice "
        
        strSql = strSql & " WHERE 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.lngTaxID = Tax.lngTaxID(+) " & _
            " AND ItemActivityDetail.lngJobID = Job.lngJobID(+) " & _
            " 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 ItemActivityDetail.lngActivityDetailID=QSaleInvoice.lngOrderDetailID(+) "
        
        strSql = strSql & " AND ItemActivity.lngCustomerID=" & C2lng(frmName.lblHead(0).Tag) & _
            " AND ItemActivity.lngCurrencyID=" & frmName.getFieldID(7) & _
            " AND (Abs(dblCurrAmount+dblCurrTaxAmount-dblCurrInvoiceAmount+NVL(dblFpAmount,0))>0.000000001 " & _
            " OR Abs(dblCurrAmount+dblCurrTaxAmount-dblCurrInvoiceAmount)>0.000000001) AND blnIsVoid=0 " & _
            " AND (ItemActivity.lngActivityTypeID IN (11,12,14,17)) " & _
            " AND (ItemActivityDetail.blnCloseInvoice=0 OR dblCurrInvoiceAmount=0 OR dblFpAmount<>0) "
        If mlngRowNO <> 0 Then
            strSql = strSql & " AND Item.lngItemID=" & C2lng(frmName.TextOfGrid(mlngRowNO, 28))
        End If
    End If
    
    Set Data1.Resultset = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
    
    If Not Data1.Resultset.EOF Then
        Data1.Resultset.MoveLast
    Else
        cmdOkCancel(2).Enabled = False
        cmdOkCancel(3).Enabled = False
    End If
        
    ReDim DataofGrid(GrdCol.Rows - 1)
        
    For i = 1 To GrdCol.Rows - 1
        GrdCol.TextMatrix(i, 11) = Trim(GrdCol.TextMatrix(i, 11))
        DataofGrid(i).lngVoucherID = C2lng(GrdCol.TextMatrix(i, 14))
        DataofGrid(i).lngItemID = C2lng(GrdCol.TextMatrix(i, 13))
        DataofGrid(i).dblFactor = C2Dbl(GrdCol.TextMatrix(i, 12))
        DataofGrid(i).dblCurrAmount = C2Dbl(GrdCol.TextMatrix(i, 15))
        DataofGrid(i).dblTaxCurrAmount = C2Dbl(GrdCol.TextMatrix(i, 16))
        DataofGrid(i).dblAmount = C2Dbl(GrdCol.TextMatrix(i, 17))
        DataofGrid(i).dblTaxAmount = C2Dbl(GrdCol.TextMatrix(i, 18))
        DataofGrid(i).dblCurrInvoiceAmount = C2Dbl(GrdCol.TextMatrix(i, 19))
        DataofGrid(i).strTax = Trim(GrdCol.TextMatrix(i, 20))
        DataofGrid(i).strJob = Trim(GrdCol.TextMatrix(i, 21))
        DataofGrid(i).strCustom0 = Trim(GrdCol.TextMatrix(i, 22))
        DataofGrid(i).strCustom1 = Trim(GrdCol.TextMatrix(i, 23))
        DataofGrid(i).strCustom2 = Trim(GrdCol.TextMatrix(i, 24))
        DataofGrid(i).strCustom3 = Trim(GrdCol.TextMatrix(i, 25))
        DataofGrid(i).strCustom4 = Trim(GrdCol.TextMatrix(i, 26))
        DataofGrid(i).strCustom5 = Trim(GrdCol.TextMatrix(i, 27))
        DataofGrid(i).strProduceNum = Trim(GrdCol.TextMatrix(i, 28))
        DataofGrid(i).strProduceDate = Trim(GrdCol.TextMatrix(i, 29))
        DataofGrid(i).strValidDate = Trim(GrdCol.TextMatrix(i, 30))
        DataofGrid(i).intValidDay = C2lng(GrdCol.TextMatrix(i, 31))
        DataofGrid(i).lngUnitID = C2lng(GrdCol.TextMatrix(i, 32))
        DataofGrid(i).lngTaxID = C2lng(GrdCol.TextMatrix(i, 33))
        DataofGrid(i).lngJobID = C2lng(GrdCol.TextMatrix(i, 34))
        DataofGrid(i).lngCustomID0 = C2lng(GrdCol.TextMatrix(i, 35))
        DataofGrid(i).lngCustomID1 = C2lng(GrdCol.TextMatrix(i, 36))
        DataofGrid(i).lngCustomID2 = C2lng(GrdCol.TextMatrix(i, 37))
        DataofGrid(i).lngCustomID3 = C2lng(GrdCol.TextMatrix(i, 38))
        DataofGrid(i).lngCustomID4 = C2lng(GrdCol.TextMatrix(i, 39))
        DataofGrid(i).lngCustomID5 = C2lng(GrdCol.TextMatrix(i, 40))
        DataofGrid(i).dblPriceTax = C2Dbl(GrdCol.TextMatrix(i, 41))
        DataofGrid(i).dblTaxRate = C2Dbl(GrdCol.TextMatrix(i, 42)) / 100
        DataofGrid(i).dblPrice = C2Dbl(GrdCol.TextMatrix(i, 43))
        DataofGrid(i).blnNoChange = (C2lng(GrdCol.TextMatrix(i, 44)) <> 0)
        DataofGrid(i).lngActivityDetailID = C2lng(GrdCol.TextMatrix(i, 1))
        GrdCol.RowData(i) = i
    Next
    GrdCol.Cols = 12
    
    mclsGrid.ListSet.Columns = GrdCol.Cols - 1
    
    GrdCol.TextMatrix(0, 1) = "选择"
    
    ReDim strColName(GrdCol.Cols - 1)
    ReDim xlngColNo(GrdCol.Cols - 1)
     
    For i = 0 To GrdCol.Cols - 1
        If InStr(GrdCol.TextMatrix(0, i), "数量") <> 0 Or InStr(GrdCol.TextMatrix(0, i), "金额") <> 0 Then
            GrdCol.ColAlignment(i) = flexAlignRightCenter
'            mclsGrid.ColType(i) = 1
        Else
            GrdCol.ColAlignment(i) = flexAlignLeftCenter
'            mclsGrid.ColType(i) = 0
        End If
        strColName(i) = GrdCol.TextMatrix(0, i)
        xlngColNo(i) = i
'        mclsGrid.ColSort(i) = True
    Next
    '设置初始选择
    SetSelectedBills

    CalcCanSettleAmount
    mclsGrid.ColOfs = 2
'    mclsGrid.ListSetToGrid
    mclsGrid.SetupStyle
    If mblnBillCanNotChange Then
        cmdOkCancel(2).Enabled = False
        cmdOkCancel(3).Enabled = False
    Else
        If frmName.getID() = 0 Then
            Set mclsGrid.EditText = curInput
            mclsGrid.SetEditText "本次开票数量", "", "选择", "√"
            mclsGrid.SetEditText "本次开票金额", "", "选择", "√"
        Else
            Set mclsGrid.EditText = curInput
            mclsGrid.SetEditText "本次开票数量", "", "选择", "√"
            mclsGrid.SetEditText "本次开票金额", "", "选择", "√"
        End If
    End If
    
'    If GrdCol.Rows > GrdCol.FixedRows Then
'        GrdCol.Row = GrdCol.FixedRows
'        GrdCol.RowSel = GrdCol.FixedRows
'        GrdCol.ColSel = GrdCol.Cols - 1
'    End If
    Set GrdCol.MouseIcon = Utility.GetFormResPicture(2001, 2)
    GrdCol.MousePointer = flexDefault
    LoadGrdColWidth
    If GrdCol.ColWidth(0) <> 0 Then
        GrdCol.ColWidth(0) = 0
    End If
    GrdCol.Redraw = True
    Screen.MousePointer = vbDefault
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If Me.MousePointer = vbHourglass Then
        Cancel = True
        Exit Sub
    End If
    frmName.HwndForMsg = 0
    Data1.Resultset.Close
    SaveGrdColWidth
    Utility.UnLoadFormResPicture Me
'    Utility.RemoveFormResPicture 139
'    Utility.RemoveFormResPicture 1001
'    Utility.RemoveFormResPicture 1002
'    Utility.RemoveFormResPicture 1021
    Utility.RemoveFormResPicture 2001
    Set frmName = Nothing
    Erase strColName
    Erase xlngColNo
    Erase ObtendData
    Erase DataofGrid
    Set mclsGrid = Nothing
End Sub

Private Sub GrdCol_KeyPress(KeyAscii As Integer)
    With GrdCol
        If KeyAscii = vbKeySpace Then
            If .Row >= .FixedRows Then
                Select Case .TextMatrix(0, .col)
                Case "本次开票数量"
                Case "本次开票金额"
                Case "关闭"
                    If .TextMatrix(.Row, .col) = "" Then
                        SetCloseRow .Row, True
                    Else
                        SetCloseRow .Row, False
                    End If
                Case Else
                    If .TextMatrix(.Row, xlngColNo(11)) = "" Then
                        If .TextMatrix(.Row, 1) = "" Then
                            SetSelectRow .Row, True
                        Else
                            SetSelectRow .Row, False
                        End If
                    Else
                        If .TextMatrix(.Row, 1) <> "" Then
                            SetSelectRow .Row, False
                        End If
                    End If
                End Select
            End If
        End If
    End With
End Sub

Private Sub grdCol_MouseMove(Button As Integer, Shift As Integer, x As Single, y As Single)
    Dim i As Long
    
    With GrdCol
        For i = 0 To .Cols - 1
            If .TextMatrix(0, i) Like "关闭*" Then
                Exit For
            End If