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 Group

Program

Event

Program 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.