Create the monthly from the workers' hour markers

In this example we will build a document directly into the database. We will create one for each of our worker and insert the worked hours for a given month. Given the operation to be performed, this script is an action associated directly with the program.

Script GroupEvent
ProgramProgram action
output.clear()
dataform = program.newdataform()
dataform.settitle("Select month")
months = {"January","February","March","April","May","June","July","August","September","October","November","December"}
actualmonth = utility.datemonth(utility.getnow())
actualyear = utility.dateyear(utility.getnow())
actualmonths = null

if actualmonth == 1 then actualmonths = "January" end
if actualmonth == 2 then actualmonths = "February" end
if actualmonth == 3 then actualmonths = "March" end
if actualmonth == 4 then actualmonths = "April" end
if actualmonth == 5 then actualmonths = "May" end
if actualmonth == 6 then actualmonths = "June" end
if actualmonth == 7 then actualmonths = "July" end
if actualmonth == 8 then actualmonths = "August" end
if actualmonth == 9 then actualmonths = "September" end
if actualmonth == 10 then actualmonths = "October" end
if actualmonth == 11 then actualmonths = "November" end
if actualmonth == 12 then actualmonths = "December" end

dataform.addfieldlist("Month","MONTH",months,actualmonths)
dataform.addfieldinteger("Year","YEAR",actualyear)
dataform.show()

if dataform.closewithx == true then
        do return end
end

First we create a dataform to ask the user which month and year we should analyze. For convenience, we will set the default values for the current month and year.

In the event that the user closes the dataform with the x, a good rule of thumb will be to block the execution of the script.

table_rows = database.getsql("SELECT * FROM work_hour WHERE eli=0")
rows = table_rows.getrows()
for i = 1,table_rows.countrows() do
        m = utility.datemonth(rows[i].getvalue("date"))
        y = utility.dateyear(rows[i].getvalue("date"))
        wrk = rows[i].getvalue("gguid_worker")
        if m == actualmonth and y == actualyear then
               vote = false
               for i,worker in pairs(workers) do
                       if worker == wrk then
                               vote = true
                       end
               end
               if vote == false then
                       table.insert(worker,wrk)
               end
        end
end

Having recovered the month and year to be analyzed, we begin to turn the work_hour table to intercept which worker have worked in that time range. In this phase, we only save the worker's gguid, since it will be used to perform searchs on the tables. From here we will also be able to know how many documents we will create.

for i,worker in pairs(workers) do
        workername = ""
        hourlyrate = 0
        tworker    = database.getsql("SELECT * FROM workers WHERE gguid='" .. worker .."' AND eli=0")
        if tworker.countrows() > 0 then
               rowso = tworker.getrows()
               workername = rowso[1].getvalue("name")
               hourlyrate = rowso[1].getvalue("hourly_rate")
        end

We start to turn all the workers and, first of all, we recover the name and its hourly rate for each one.

Warning: the for loop is not closed because subsequent parts of the script must also be added.

if workername ~= "" then
        -- start to see if I find a monthly with the required characteristics
        gguidmonth = ""
        account   = 0
        tme = database.getsql("SELECT * FROM monthly
        WHERE eli=0 and gguid_worker='" ..worker .. "' and month='" .. actualmonths .. "' and year=" .. tostring(actualyear) .."")
        if tme.countrows() == 0 then
               --I have to create a new card
               gguidmonth = utility.gguid()
               sql   = "INSERT INTO monthly(gguid,tid,eli,arc,ut, uta,exp,gguidp,ind,tap,dsp,dsc,dsq1,dsq2,utc,tidc,gguid_worker,workername,[month],[year], hourly_rate,date) VALUES ("sql   = sql .. "'" ..  gguidmonth .. "'," .. tostring(utility.tid()) .. ",0,0,'','','',''," ..tostring(database.getind("monthly")) .. ",'','','',0,0,'',0,'" ..worker .. "','" .. utility.convap(workername) .. "','" .. actualmonths .."'," ..tostring(actualyear) .. "," ..tostring(hourlyrate) .. ",#" .. utility.formatdate(utility.getnow(),"yyyy-MM-dd") .. "#)"
               database.setsql(sql)
        else
               rows = tme.getrows()
               gguidmonth = rows[1].getvalue("gguid")
              total_account = rows[1].getvalue("total_account")
               --I eliminate the detail lines
               database.setsql("DELETE * FROM monthly_rows WHERE gguidp='"  .. gguidmonth .. "'")
        end

We take into consideration only workers who have a valid name and then see if there is a monthly for this worker with the requested month and year. If it doesn't exist we create it, otherwise we recover the previous one by eliminating the rows inside it since the we will recreate them.

Note the creation string which contains all the Nios4 management fields and the fields proper to the table. We also remind you that in order to avoid malfunctions, all fields must always be given a value since the null condition is not managed.

Warning: even in this case the initial if is not closed.

--I begin to read the reports
table_rows = database.getsql("SELECT * FROM work_hour WHERE eli=0 and gguid_worker='" .. worker .. "'")
totalhour = 0
rows = table_rows.getrows()
for i = 1,table_rows.countrows() do
        m = utility.datemonth(rows[i].getvalue("date"))
        y = utility.dateyear(rows[i].getvalue("date"))
        wrk = rows[i].getvalue("gguid_worker")
        if m == actualmonth and y == actualyear then
               - I create the row and save it
               gguidr  = utility.gguid()
               sql = "INSERT INTO monthly_rows(gguid,tid,eli,arc,ut,uta,exp,gguidp,ind,tap,dsp,dsc,dsq1,dsq2,utc,tidc)  VALUES ("sql = sql .. "'" .. gguidr .. "'," ..  tostring(utility.tid()) .. ",0,0,'','','','" .. gguidmonth .. "'," .. tostring(database.getind("monthly_rows")) .. ",'monthly','','',0,0,'',0)"
               database.setsql(sql)
               - add the missing values
               sql = "UPDATE monthly_rows SET date=#" .. utility.formatdate(rows[i].getvalue("date"),"yyyy-MM-dd") .. "#,"
               sql = sql .. "gguid_customer='" .. rows[i].getvalue("gguid_customer") .. "', customer='" .. utility.convap(rows[i].getvalue("customer")) .. "',"
               sql = sql .. "total_hour=" .. utility.convi(rows[i].getvalue("total_hour")) .. ","
               sql = sql .. "work_done=" .. utility.convap(rows[i].getvalue("work_done")) .. "',"
               sql = sql .. "site='" .. utility.convap(rows[i].getvalue("site")) .. "'"
               sql = sql .. " WHERE gguid='" .. gguidr .. "'"
               database.setsql(sql)
               totalhour = totalhour +                rows[i].getvalue("total_hour")
        end
end

Now that we have created or recovered the head of our monthly document, let's start extrapolating the rows from the work hour based on the worker. In this case, two write queries are performed for convenience: the first sets the new row by inserting the values for the program, while the second inserts our data.

               -- update the head of the monthly
               total_month = totalhour * hourlyrate
              balance = total_month– total_account
               sql = "UPDATE monthly SET total_month=" ..  utility.convi(total_month) ..  ",total_hour=" .. utility.convi(totalhour) .. ",balance=".. utility.convi(balance)  .. " WHERE gguid='" .. gguidmonth .."'" database.setsql(sql)
        end
end
program.refreshsection("monthly")

After turning all the rows, we update the head of the monthly by entering the total price and what we have already paid to the worker.

We close the if consition regarding the worker name and the for loop.

Lastly, we force the program to update the sections that use rhe monthly table to show the documents created.