Group customer orders on an invoice

In this example we will create a small orders grouper inside the invoice. In this way, with a single script we can recover what our costumer owes us and create a single document.

Script Group

Form

Event

Action

if dataview.getvalue("gguid_customer") == "" then

dataview.showerror("Select a customer!")

do return end

end

First we check that we have selected the customer in the new invoice. Obviously if the choice has not been made we cannot continue.

table_invoice = dataview.getatable("invoice_rows")

table_rows= database.getsql("SELECT * FROM orders WHERE gguid_customer='" .. dataview.getvalue("gguid_customer") .. "' AND eli=0 AND billed=0")

rows= table_rows.getrows()

for i=1,table_rows.countrows() do

date = rows[i].getvalue("date")

number = rows[i].getvalue("number)

description = "Order N." .. number .. " of " .. date

-- ENTER THE DESCRIPTIVE LINE OF THE ORDER

rowinvoice = table_invoice.adddatarow()

rowinvoice.setvalue("description",description)

tabler= database.getsql("SELECT * FROM order_rows WHERE gguidp='" .. rows[i].getvalue("gguid") .. "' AND eli=0")

rowsorders= tabler.getrows()

for i=1,tabler.countrows() do

-- ENTER ALL THE LINES OF THE ORDER

rowinvoice2= table_invoice.adddatarow()

rowinvoice2.setvalue("code", rowsorders[i].getvalue("code"))

rowinvoice2.setvalue("description", rowsorders[i].getvalue("description"))

rowitem2.setvalue("qty",rowsorders[i].getvalue("qty"))

rowitem2.setvalue("price", rowsorders[i].getvalue("price"))

rowitem2.setvalue("totalprice",rowsorders[i].getvalue("totalprice"))

end

--INSERT AN EMPTY LINE TO REMOVE THE ORDERS

rowitem= table_invoice.adddatarow()

rowitem.setvalue("description","")

--CHANGE THE STATE ON THE ORDER TO AVOID BILLING IT TWICE

database.setsql("UPDATE orders SET tid=" .. tostring(utility.tid()) .. ",billed=1 WHERE gguid='" .. rows[i].getvalue("gguid") .. "'")

database.addsyncbox("orders",rows[i].getvalue("gguid"))

end

--FORCE THE INVOICE UPDATE

dataview.update()

The procedure consists of two cycles. The first one extracts the orders by selecting them based on the customer and on the basis of the billed value = 0. This is used to prevent creating two invoices that report the same order.

Once the orders have been recovered, we insert a first row in the invoice where we will report the order's number and its date.

We will then extract the rows of the current order and begin the second cycle to insert all the rows that make it up on the invoice.

We will then insert an empty row between one order and another to better display them within the body of the invoice. In the meantime, we will change the status of the order by setting the value of billed to 1, so that the order is not called up by another invoice. After the two cycles, we will force the update of the invoice calculations.