Further changes in manufacturing, added unit_cost in issues and requirements

[fa-stable.git] / reporting / rep308.php

diff --git a/reporting/rep308.php b/reporting/rep308.php
index 0631e46dba74f0a1535d9a7e45dfa31718831eac..08b8e89806914046e52c25bdb642b90cd1bac28d 100644 (file)
--- a/reporting/rep308.php
+++ b/reporting/rep308.php
@@ -67,7 +67,7 @@ function fetch_items($category=0)
                                stock.category_id,units,
                                cat.description
                        FROM ".TB_PREF."stock_master stock LEFT JOIN ".TB_PREF."stock_category cat ON stock.category_id=cat.category_id
-                               WHERE mb_flag <> 'D'";
+                               WHERE mb_flag <> 'D' AND mb_flag <> 'F'";
                if ($category != 0)
                        $sql .= " AND cat.category_id = ".db_escape($category);
                $sql .= " ORDER BY stock.category_id, stock_id";
@@ -115,12 +115,18 @@ function avg_unit_cost($stock_id, $location=null, $to_date)
 
        $to_date = date2sql($to_date);
 
-       $sql = "SELECT standard_cost, price, tran_date, type, trans_no, qty, person_id  FROM ".TB_PREF."stock_moves
-               WHERE stock_id=".db_escape($stock_id)."
-               AND tran_date < '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND type <> ".ST_LOCTRANSFER;
+       $sql = "SELECT move.*, IF(ISNULL(supplier.supplier_id), debtor.debtor_no, supplier.supplier_id) person_id
+               FROM ".TB_PREF."stock_moves move
+                               LEFT JOIN ".TB_PREF."supp_trans credit ON credit.trans_no=move.trans_no AND credit.type=move.type
+                               LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=move.trans_no AND 25=move.type
+                               LEFT JOIN ".TB_PREF."suppliers supplier ON IFNULL(grn.supplier_id, credit.supplier_id)=supplier.supplier_id
+                               LEFT JOIN ".TB_PREF."debtor_trans cust_trans ON cust_trans.trans_no=move.trans_no AND cust_trans.type=move.type
+                               LEFT JOIN ".TB_PREF."debtors_master debtor ON cust_trans.debtor_no=debtor.debtor_no
+                       WHERE stock_id=".db_escape($stock_id)."
+                       AND move.tran_date < '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND move.type <> ".ST_LOCTRANSFER;
 
        if ($location != '')
-               $sql .= " AND loc_code = ".db_escape($location);
+               $sql .= " AND move.loc_code = ".db_escape($location);
        $sql .= " ORDER BY tran_date";  
 
        $result = db_query($sql, "No standard cost transactions were returned");
@@ -157,12 +163,18 @@ function trans_qty_unit_cost($stock_id, $location=null, $from_date, $to_date, $i
 
        $to_date = date2sql($to_date);
 
-       $sql = "SELECT standard_cost, price, tran_date, type, trans_no, qty, person_id FROM ".TB_PREF."stock_moves
+       $sql = "SELECT move.*, IF(ISNULL(supplier.supplier_id), debtor.debtor_no, supplier.supplier_id) person_id
+               FROM ".TB_PREF."stock_moves move
+                               LEFT JOIN ".TB_PREF."supp_trans credit ON credit.trans_no=move.trans_no AND credit.type=move.type
+                               LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=move.trans_no AND 25=move.type
+                               LEFT JOIN ".TB_PREF."suppliers supplier ON IFNULL(grn.supplier_id, credit.supplier_id)=supplier.supplier_id
+                               LEFT JOIN ".TB_PREF."debtor_trans cust_trans ON cust_trans.trans_no=move.trans_no AND cust_trans.type=move.type
+                               LEFT JOIN ".TB_PREF."debtors_master debtor ON cust_trans.debtor_no=debtor.debtor_no
                WHERE stock_id=".db_escape($stock_id)."
-               AND tran_date <= '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND type <> ".ST_LOCTRANSFER;
+               AND move.tran_date <= '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND move.type <> ".ST_LOCTRANSFER;
 
        if ($location != '')
-               $sql .= " AND loc_code = ".db_escape($location);
+               $sql .= " AND move.loc_code = ".db_escape($location);
 
        if ($inward)
                $sql .= " AND qty > 0 ";
@@ -318,4 +330,3 @@ function inventory_movements()
     $rep->End();
 }
 
-?>
\ No newline at end of file