--------------------------------------------------------------------------------------------------
Public Function ConvertOrder(OrdNo As Long) As Boolean
Dim IsStartTrans as boolean
IsStartTrans = False
ConvertOrder = False
Dim RSOrd As New ADODB.Recordset
SqlStr = "Select * from orders where firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo & " ;"
RSOrd.Open SqlStr, dbx
If RSOrd.EOF Then
MsgBox "Order No. " & OrdNo & " Not Available for Conversion", vbInformation
Exit Function
End If
RSOrd.MoveFirst
If RSOrd!closed = 1 Then
'Closed Order
Exit Function
End If
Dim SaleNo As Long
SaleNo = GetNewSaleNumber ' THIS IS A FUNCTION TO GET THE NEXT SALE BILL NUMBER
IsStartTrans = True
dbx.BeginTrans
'SQL No.1
SqlStr = "INSERT INTO sale( " & _
"firm_id, invnumber, invdate, salesmancode, cust_number, cust_name, " & _
"routecode, pricegroup, totalamount, totaldiff, discount, paid," & _
"journalrefno, isrm, orderno, memo, lastbalance) " & _
"SELECT firm_id, " & SaleNo & " as invnumber,'" & Format(Now(), "yyyy-mm-dd hh:mm:ss") & "', salesmancode, cust_number, " & _
"cust_name, routecode, pricegroup, totalamount, totaldiff, discount, " & _
"paid , 0, IsRM, ordnumber, Memo, get_head_current_balance(" & PubFirmId & ", cust_number) " & _
"FROM orders where firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo & " ; "
dbexecute SqlStr 'dbexecute IS A FUNCTION TO EXECUTE THE QUERRY SqlStr
'SQL No.2
SqlStr = "INSERT INTO saleit(" & _
"firm_id, invnumber, item_code, qty, rate, cost, slno) " & _
"SELECT firm_id, " & SaleNo & " as invnumber, item_code, qty, rate, cost, slno " & _
"FROM orderit where firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo & " ;"
dbexecute SqlStr
'SQL No.3
SqlStr = "select add_item_stock(" & PubFirmId & ",item_code, (0 - qty)::real ) FROM orderit where firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo & " ;"
dbexecute SqlStr
'add_item_stock is a function to modify the stock in the items table
'SQL No.4
SqlStr = "UPDATE orderit SET SoldQty = Qty WHERE firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo & " ;"
dbexecute SqlStr
SQL No.5
SqlStr = "update orders set Closed=true where firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo
dbexecute SqlStr
--------------------------------------------------------------------------------------------------------------------------------------------------
Query No:1 and 2 are INSERT INTO - SELECT combination queries for taking some data (one row ) from order table and inserting to Sale table & Orderit to SaleIt Table
Query No.3 is for updating the stock. if the stock goes below zero, the function triggers an error and the error handler is executed and the transaction is rolled back. When I used ODBC
09.01.0100 the transaction is not rolled back completely. The SQL No.1 &2 are held until any other program segment tries to commit some other transaction outside this function and new rows are found added in Sale and SaleIt tables.
---------------------------------------------------------------------------------------------------------------------------------------
If a man is called to be a
streetsweeper, he should sweep streets even as Michelangelo painted,
or Beethoven composed music, or Shakespeare wrote poetry. He should
sweep streets so well that all the hosts of heaven and earth will pause
to say, here lived a great streetsweeper who did his job well.
RSS Feed