In this example we will see how to recalculate the prices of the items starting from their bill of materials In practice, by changing the price of an items we will be able to see in which bill of materials it is located and from there we will recalculate the price of the item father.
Script Group | Event |
Form | Post saving |
So let's see how to use a recursive function, that is, a function that calls itself to be able to calculate all the levels of the bill of materials of each single items passed.
The script will be linked to the post saving of the items.
function calculate(gfather) - extract the bill of materials and recover the prices and the costs tfather = database.getsql("SELECT * FROM billmaterials_dn WHERE gguidp='" .. gfather .."'") nrowsp = tfather.countrows() rowsp = tfather.getrows() finalprice = 0 finalcost = 0 for i = 1,nrowsp do finalprice = finalprice + rowsp[i].getvalue("total_price") finalcost = finalcost + rowsp[i].getvalue("total_cost") end database.setsql("UPDATE items SET update=1,cost=" .. utility.formatnum(finalcost,2) ..",price=" .. utility.formatnum(finalprice,2) .. " WHERE gguid='" .. gfather .. "'") database.setsql("UPDATE items SET tid=" .. tostring(utility.tid()) .. " WHERE gguid='" .. gfather .. "'") database.addsyncbox("items",gfather) - check if this is not a child of someone else, if you then start at calculate the other fathers tfathers = database.getsql("SELECT * FROM billmaterials_dn WHERE gguid_code_dn='" .. gfather .. "'") nrowsfathers = tfathers.countrows() rowsfathers = tfathers.getrows() - Update the prices and costs if the object is used as a son on other lists sprice = utility.formatnum(finalprice,2) scost = utility.formatnum(finalcost,2) gguidfathers = {} for i = 1,nrowsfathers do database.setsql("UPDATE billmaterials_dn SET cost=" .. scost .. ",price=" .. sprice .. " WHERE gguid='" .. rowsfathers[i].getvalue("gguid") .. "'") database.setsql("UPDATE billmaterials_dn SET total_cost=cost * qty_dn,total_price=price * qty_dn WHERE gguid='" .. rowsfathers[i].getvalue("gguid") .. "'") database.setsql("UPDATE billmaterials_dn SET tid=" .. tostring(utility.tid()) .. " WHERE gguid='" .. rowsfathers[i].getvalue("gguid") .. "'") database.addsyncbox("billmaterials_dn", rowsfathers[i].getvalue("gguid")) -- extract the fathers' gguids table.insert(gguidfathers, rowsfathers[i].getvalue("gguidp")) end -- I proceed to recursively process the items for i,gp in pairs(gguidfathers) do calculate(gp) end end
First we write the recursive function that considers the gguid of the item to be updated as an input parameter. In this way the table of the bill of materials is retrieved, its price recalculated and updated. During the update we retrieve the gguids of the item that take it back into their bill of materials and, as you can see, we call the same function thus creating recursion.
In practice, the system updates the item from the lower and lower level (always given by the item that we modified at the start) and then backwards recalculates all its fathers.
price = dataview.getvalue("price") cost = dataview.getvalue("cost") gguid = dataview.getvalue("gguid") sprice = utility.formatnum(price,2) scost = utility.formatnum(cost,2) - extrapol where the article is used and update the price and the cost tlist = database.getsql("SELECT * FROM billmaterials_dn WHERE gguid_code_dn='" .. gguid .."'") nrows = tlist.countrows() rows = tlist.getrows() gguidfathers = {} for i = 1,nrows do database.setsql("UPDATE billmaterials_dn SET cost=" .. scost .. ",price=" .. sprice .. " WHERE gguid='" .. rows[i].getvalue("gguid") .. "'") database.setsql("UPDATE billmaterials_dn SET total_cost=cost * qty_dn,total_price=price * qty_dn WHERE gguid='" .. rows[i].getvalue("gguid") .. "'") database.setsql("UPDATE billmaterials_dn SET tid=" .. tostring(utility.tid()) .. " WHERE gguid='" .. rows[i].getvalue("gguid") .. "'") database.addsyncbox("billmaterials_dn", rows[i].getvalue("gguid")) -- extract the fathers' gguids table.insert(gguidfathers,rows[i].getvalue("gguidp")) end -- I proceed to recursively process the itemsfor i,gp in pairs(gguidfathers) do calcolate(gp) end program.refreshsection("items")
In this part of the script, the one actually launched by the program, we retrieve the item information and update the price and cost where the latter is called up within the bill of materials. In this way it will be possible to retrieve the fathers' gguids which will then be passed to the Calcolate function. From there, for each father, the function will go up the chain of item ensuring that the modification made by the initial item is implemented by all the bill of materials and therefore by the price and cost of the items that directly or indirectly recall it.