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.
if dataview.getvalue("gguid_customer") == "" then
dataview.showerror("Select a customer!")
do return 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")
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()
tabler= database.getsql("SELECT * FROM order_rows WHERE gguidp='" .. rows[i].getvalue("gguid") .. "' AND eli=0")
for i=1,tabler.countrows() do
-- ENTER ALL THE LINES OF THE ORDER
--INSERT AN EMPTY LINE TO REMOVE THE ORDERS
--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") .. "'")
--FORCE THE INVOICE 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.