Create a script report

In this example we will create a small script type report to see the characteristics of the system.

First add a new report from the options, select the script type and press set to view the editor. Remember when you add the new report to set all the parameters in order to view correctly.

--create filter interface
filtertable.clear()

filtertable.addcategoryrow("Date")
filtertable.adddaterow("START","Start")
filtertable.adddaterow("END","End")
filtertable.addsubtablerow("CUSTOMER","Customers","customers","")

--create report grid
reporttable.clearcolumns()
reporttable.clearrows()
reporttable.adddatecolumn("date","Date",150,true)
reporttable.addtextcolumn("customer","Customer",200,true)

First of all, inside the interface script we insert all the commands to create the search filters and the columns that will be used to display the data.

The following rows, on the other hand, will be inserted into the processing script. While the interface script is launched only once, the processing script is performed every time we press the process button.

startdate = filtertable.getvalue("START")
enddate   = filtertable.getvalue("END")

-- I do a basic search on a range of dates
iday = tostring(utility.dateday(startdate))
imonth   = tostring(utility.datemonth(startdate))
iyear   = tostring(utility.dateyear(startdate))

fday = utility.dateday(enddate)
fmonth   = utility.datemonth(enddate)
fyear   = utility.dateyear(endadate)

First, we recover the data values from the filtered table and prepare them for use in the search.

sql = "SELECT * FROM invoices WHERE eli=0 AND year(date) >=" .. iyear .. " AND month(date) >=" .. imonth .. " AND day(date) >=" .. iday
sql = sql .. " AND year(date) <=" .. fyear .. " AND month(date) <=" .. fmonth .. " AND day(date) <=" .. fday 

-- I recover the remaining filters
rowcustomer = filtertable.getvalue("CUSTOMER")
if rowcustomer ~= nil then 
    sql = sql .. " AND gguid_name='" .. rowcustomer.getvalue("gguid") .. "'"
end

We prepare the sql string to perform the search by checking if a customer has been selected from the filter table. If yes we add it to the sql string for each search.

reporttable.clearrows()

table_rows= database.getsql(sql)
rows   = table_rows.getrows()
for i   = 1,table_rows.countrows() do
    --output.print(rows[i].getvalue("date"))
    row = reporttable.adddatarow()
    row.setvalue("date",rows[i].getvalue("date"))
    row.setvalue("customer",rows[i].getvalue("name"))
end

We perform the search and begin to compile the report table with the values found.