Update the prices of the bill of materials

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.