code suggestions manufacturing in oscommerce

Question

​I am a newbie to SQL and PHP Looking for some suggestions for OsCommerce as how to code a manufacturing costing system . I think it might be able to be done is SQL statements alone but not sure. Basically I have costs and data on the raw materials but I need that to flow through to the costings weights and volumes for pricing and shipping etc. The complexity arises for me that the products table so that some how I have to SELF JOIN ? to the products table for ever Kit component is the same as the raw materials table Can someone suggest some code examples please ?

---

I have a file products (p)

---

in products
p.products_id, p.products_model, p.products_RRP, p.products_height, p.products_width, p.products_depth, p.products_volume, p.products_cost, p.products_margin
Firstly I wish to update the info in products file

set p.products_volume = p.products_height x p.products_weight x p.products_depth
set p.products_RRP = p.products_cost x p.products_margin

for every record

---

I have a file kits (k)

---

in kits
k.product_id, k.subproduct_id, k.qty
Sub product_id(s) costs are the costs from p.products_cost etc
so that the data in products file p.products_RRP, p.products_cost p.products_weight, p.products_volume
are calculated from adding all sub products together
after multiply each one as below then saved to the product_id of the kit file in products file
Calcs for kits

p.products_weight += (p.products_weight [k.subproduct_id[N]] x k.qty[N])
p.products_cost += (p.products_cost [k.subproduct_id[N]] x k.qty[N])  
p.products_volume += (p.products_volume [k.subproduct_id[N]] x k.qty[N])  

I am not sure if this can be done with an SQL statement or I need to write some kind of PHP to do it
I think from memory there are procedures in MYSQL (MariaDb 10)
that can do calcs automatically ?

---

logically I can see I need to :
but SYNTAX is my downfall right now
I know this probably has self join and INNER JOIN some place ??

Anys uggestions appreciated:

---

open kits file and get the k.products_id (one line at a time ? not sure how to do this<br>
find number of subproducts count(k.subproduct_id) WHERE k.product_id = kproduct_id ?<br>

// something like

   SELECT k.product_id, k.subproduct_id, k.qty FROM kits , k WHERE ALL ??

    for ($i= 1 to count(k.subproduct_id )) {<
      if (count(k.subproduct_id) = 0 ) { next record } ?? <
   $kid[$i]=k.product_id<br>
    $kmodel[$i] = p.product_model<br>
    $kcost[$i] += query select from products as p p.products_cost where products_id = k.subproduct_id x k.qty<br>
    $kvolume += k.qty x p.products_depth x p.products_height x p.products_depth<br>
    $kcost[$i] += $cost[$i] x k.sub <br>
    $kweight[$i] += p.products_weight x k,qty<br>
    $kheight[$i] += p.products_height x k,qty<br>
    if (p.products_margin = 0) {$kmargin[$i] = 1.5 } else {$kmargin[$i] = p.products_margin}< krrp[$i] += p.products_cost x $kmargin[$i]<
  }<br>
    for ($i = 1 to count($kid())) {<br>
    if (p.products_price != sum($krrp())) { echo $kmodel[$i] " , " . $kid[$i] ." , "  . $kid[$i]/ p.products_price}; 
    UPDATE products ,p
    SET  = p.products_cost = sum($kcost()),
           p.products_volume = sum($kvolume()),
           p.products_RRP = sum($krrp()),
           p.products_height = sum($kheight()),
           p.products_weight = sum($kweight()),
           p.products_volume = sum($kvolume())
    WHERE products_id = $kid[$i];

This is what I am trying to do

---

update products p p.products_cost = sum($kcost()) where products_id = $kid[$i]<br>
update products p p.products_volume = sum($kvolume()) where products_id = $kid[$i]<br>
update products p p.products_RRP = sum($krrp()) where products_id = $kid[$i]<br>
update products p p.products_height = sum($kheight()) where products_id = $kid[$i]<br>
update products p p.products_weight = sum($kweight()) where products_id = $kid[$i]<br>
<br>  
}<br>

OK so I have tried some SQL and I'm getting there JJJust not sure how to process the data from the query.

SELECT p.products_model AS Model, 
pb.subproduct_id AS Id, 
pb.subproduct_qty AS Qty,
p.raw_material AS Raw,
p.sold_in_bundle_only AS kit ,
pd.products_name AS Name, 
p.buy_in_cost AS Cost,
p.buy_in_markup as Markup,
CAST(p.buy_in_cost * p.buy_in_markup AS DECIMAL(7,3)) AS Mprice, 
p.products_price AS Price, 
CAST(p.products_RRP AS DECIMAL(7,3)) as RRP,
p.products_weight as Kgs , 

CAST(p.products_height AS DECIMAL(7,2)) AS Height,
CAST(p.products_width AS DECIMAL(7,2)) AS Width,
CAST(p.products_depth AS DECIMAL(7,1)) AS Depth, 

CAST(p.products_volume  AS DECIMAL(7,1)) AS Vol,     
CAST(p.products_height * p.products_width * p.products_depth AS DECIMAL(7,1)) AS CalcVol
     

FROM products AS p  
INNER JOIN products_bundles AS pb 
ON pb.subproduct_id=p.products_id 
INNER JOIN products_description AS pd 
ON pb.subproduct_id=pd.products_id 
WHERE pd.language_id="1"
GROUP BY pb.subproduct_id
ORDER BY raw DESC