Create an invoice from appointments

In this example we will see how to create an invoice by recovering the appointments made with a customer, which can be seen how work reports.

Script Group

Form

Event

Value is being modified

The script will be an action associeted with the invoice form, consequently the process will consist of creating a new invoice, choosing the customer and launching the script to extract the data.

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

dataview.showerror("Select the

nominative!")

do return end

end

First we check that we have choosen a nominative, since without this data it is impossible to find his appointments. If the special field associated with the Nominative subtable is empty, we give the error message and exit the script.

dataform = program.newdataform()

dataform.addfielddate("Start Date","DI",null)

dataform.addfielddate("End Date","DF",null)

dataform.show()

if dataform.closewithx == true then

do return end

end

startdate = dataform.getvalue("DI")

enddate = datafor.getvalue("DF")

We create a dataform to ask the user to identify which dates of appointment we have to include in the invoice.

Note that if the user closes the dataform with the x of the window, then the script will be blocked.

table_rows = database.getsql("SELECT * FROM agenda WHERE gguid_name_customer='" .. dataview.getvalue("gguid_name") .. "' AND eli=0")

We now begin to extrapolate the data from the agenda by filtering it by name. I remind you that this is only an example. It would have been possible to include the dates in the sql string to already have a filtered table, but in this case we prefer to scroll through them and retrieve only the appointments that interest us.

totalhours =0

for i=1,table_rows.countrows() do

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

if date >= startdate and date <= enddate then

totalhours = totalhours +

rows[i].getvalue("workhours")

end

end

Now we turn all the rows and check that the date is included in the search ones. It is necessary to pay attention to the date because written in this way it is very easy that it does not extract any row because the dataform makes us choose a date without hours, minutes and seconds.

This values are set to 0 so if the stardate and the enddate are the same, then only the appointments made exactly in midnight will be recovered. In this case, within the appointments there is a field where the hours have already been calculated by another script. The way to calculate the total time is at your discretion.

If totalhours == 0 then

dataview.showerror("No working hours

found in the requested date range")

do return end

end

If it has not found anything, we inform the user and exit from the script.

gridview = dataview.getatable("invoices_ca_rows")

row = gridview.adddatarow()

row.setvalue("description","Labor since " .. utility.datetostring(startdate) .. " to " .. utility.datetostring(enddate))

row.setvalue("qty",totalhours)

gridview.update()

dataview.update()

Finally, we create the row inside the invoice grid, we enter the description and the quantity. For the price, however, it depends on how it is manged: it could written in the cliente form, directly in the appointment, etc.