Changed Journal Inquiries so it shows the Bank Deposit/Payment amount instead of...

[fa-stable.git] / purchasing / includes / db / invoice_db.inc

diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc
index 0fbb05099d9f0aee8bbc18e1a3a0208af914a2ce..ffdb8c17316ffebd60e78392c1eb71331be69ea0 100644 (file)
--- a/purchasing/includes/db/invoice_db.inc
+++ b/purchasing/includes/db/invoice_db.inc
@@ -15,13 +15,14 @@ include_once($path_to_root . "/purchasing/includes/db/invoice_items_db.inc");
 
 function read_supplier_details_to_trans(&$supp_trans, $supplier_id)
 {
-       $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."payment_terms.terms, ".TB_PREF."payment_terms.days_before_due,
+       $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."payment_terms.terms, "
+               .TB_PREF."payment_terms.days_before_due,
                ".TB_PREF."payment_terms.day_in_following_month,
                ".TB_PREF."suppliers.tax_group_id, ".TB_PREF."tax_groups.name As tax_group_name
                From ".TB_PREF."suppliers, ".TB_PREF."payment_terms, ".TB_PREF."tax_groups
                WHERE ".TB_PREF."suppliers.tax_group_id = ".TB_PREF."tax_groups.id
                AND ".TB_PREF."suppliers.payment_terms=".TB_PREF."payment_terms.terms_indicator
-               AND ".TB_PREF."suppliers.supplier_id = '" . $supplier_id . "'";
+               AND ".TB_PREF."suppliers.supplier_id = ".db_escape($supplier_id);
 
        $result = db_query($sql, "The supplier record selected: " . $supplier_id . " cannot be retrieved");
 
@@ -61,7 +62,7 @@ function update_supp_received_items_for_invoice($id, $po_detail_item, $qty_invoi
        if ($chg_price != null)
        {
                $sql = "SELECT act_price, unit_price FROM ".TB_PREF."purch_order_details WHERE
-                       po_detail_item = $po_detail_item";
+                       po_detail_item = ".db_escape($po_detail_item);
                $result = db_query($sql, "The old actual price of the purchase order line could not be retrieved");
                $row = db_fetch_row($result);
                $ret = $row[0];
@@ -69,7 +70,8 @@ function update_supp_received_items_for_invoice($id, $po_detail_item, $qty_invoi
                $unit_price = $row[1]; //Added by Rasmus
 
                $sql = "SELECT delivery_date FROM ".TB_PREF."grn_batch,".TB_PREF."grn_items WHERE
-                       ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id AND ".TB_PREF."grn_items.id=$id";
+                       ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id AND "
+                       .TB_PREF."grn_items.id=".db_escape($id);
                $result = db_query($sql, "The old delivery date from the received record cout not be retrieved");
                $row = db_fetch_row($result);
                $date = $row[0];
@@ -81,17 +83,17 @@ function update_supp_received_items_for_invoice($id, $po_detail_item, $qty_invoi
                $unit_price = 0; // Added by Rasmus
        }
     $sql = "UPDATE ".TB_PREF."purch_order_details
-               SET qty_invoiced = qty_invoiced + $qty_invoiced ";
+               SET qty_invoiced = qty_invoiced + ".db_escape($qty_invoiced);
 
        if ($chg_price != null)
-               $sql .= " , act_price = $chg_price ";
+               $sql .= " , act_price = ".db_escape($chg_price);
 
-       $sql .= " WHERE po_detail_item = $po_detail_item";
+       $sql .= " WHERE po_detail_item = ".db_escape($po_detail_item);
     db_query($sql, "The quantity invoiced of the purchase order line could not be updated");
 
     $sql = "UPDATE ".TB_PREF."grn_items
-        SET quantity_inv = quantity_inv + $qty_invoiced
-        WHERE id = $id";
+        SET quantity_inv = quantity_inv + ".db_escape($qty_invoiced)."
+        WHERE id = ".db_escape($id);
        db_query($sql, "The quantity invoiced off the items received record could not be updated");
        return array($ret, $date, $unit_price);
 }
@@ -101,8 +103,9 @@ function get_deliveries_between($stock_id, $from, $to)
        $from = date2sql($from);
        $to = date2sql($to);
        $sql = "SELECT SUM(-qty), SUM(-qty*standard_cost) FROM ".TB_PREF."stock_moves
-               WHERE type=".ST_CUSTDELIVERY." AND stock_id='$stock_id' AND
+               WHERE type=".ST_CUSTDELIVERY." AND stock_id=".db_escape($stock_id)." AND
                        tran_date>='$from' AND tran_date<='$to' GROUP BY stock_id";
+
        $result = db_query($sql, "The deliveries could not be updated");
        return db_fetch_row($result);
 }
@@ -208,8 +211,8 @@ function add_supp_invoice($supp_trans, $invoice_no=0) // do not receive as ref b
                $line_taxfree = $entered_grn->taxfree_charge_price($supp_trans->tax_group_id);
                $line_tax = $entered_grn->full_charge_price($supp_trans->tax_group_id) - $line_taxfree;
                $stock_gl_code = get_stock_gl_code($entered_grn->item_code);
-
-               $total += add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_gl_code["inventory_account"],
+               $iv_act = (is_inventory_item($entered_grn->item_code) ? $stock_gl_code["inventory_account"] : $stock_gl_code["cogs_account"]);
+               $total += add_gl_trans_supplier($trans_type, $invoice_id, $date_, $iv_act,
                        $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'],
                        $entered_grn->this_quantity_inv * $line_taxfree, $supp_trans->supplier_id);
        // -------------- if price changed since po received. 16 Aug 2008 Joe Hunt
@@ -250,7 +253,7 @@ function add_supp_invoice($supp_trans, $invoice_no=0) // do not receive as ref b
                                                $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], _("Cost diff."),
                                                $amt, null, null, null,
                                                "The general ledger transaction could not be added for the price variance of the inventory item");
-                                       add_gl_trans($trans_type, $invoice_id, $date_,  $stock_gl_code["inventory_account"],
+                                       add_gl_trans($trans_type, $invoice_id, $date_,  $iv_act,
                                                0, 0, _("Cost diff."), -$amt, null, null, null,
                                                "The general ledger transaction could not be added for the price variance of the inventory item");
                                }               
@@ -340,11 +343,12 @@ function get_po_invoices_credits($po_number)
        $sql = "SELECT DISTINCT ".TB_PREF."supp_trans.trans_no, ".TB_PREF."supp_trans.type,
                ov_amount+ov_discount+ov_gst AS Total,
                ".TB_PREF."supp_trans.tran_date
-               FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, ".TB_PREF."purch_order_details, ".TB_PREF."purch_orders
+               FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, "
+               .TB_PREF."purch_order_details, ".TB_PREF."purch_orders
                WHERE ".TB_PREF."supp_invoice_items.supp_trans_no = ".TB_PREF."supp_trans.trans_no
                AND ".TB_PREF."supp_invoice_items.po_detail_item_id = ".TB_PREF."purch_order_details.po_detail_item
                AND ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."supp_trans.supplier_id
-               AND ".TB_PREF."purch_order_details.order_no = $po_number";
+               AND ".TB_PREF."purch_order_details.order_no = ".db_escape($po_number);
 
        return db_query($sql, "The invoices/credits for the po $po_number could not be retreived");
 }
@@ -354,7 +358,7 @@ function get_po_invoices_credits($po_number)
 function read_supp_invoice($trans_no, $trans_type, &$supp_trans)
 {
        $sql = "SELECT ".TB_PREF."supp_trans.*, supp_name FROM ".TB_PREF."supp_trans,".TB_PREF."suppliers
-               WHERE trans_no = $trans_no AND type = $trans_type
+               WHERE trans_no = ".db_escape($trans_no)." AND type = ".db_escape($trans_type)."
                AND ".TB_PREF."suppliers.supplier_id=".TB_PREF."supp_trans.supplier_id";
        $result = db_query($sql, "Cannot retreive a supplier transaction");
 
@@ -414,7 +418,8 @@ function read_supp_invoice($trans_no, $trans_type, &$supp_trans)
 function get_matching_invoice_item($stock_id, $po_item_id)
 {
        $sql = "SELECT *, tran_date FROM ".TB_PREF."supp_invoice_items, ".TB_PREF."supp_trans
-               WHERE supp_trans_type = ".ST_SUPPINVOICE." AND stock_id = '$stock_id' AND po_detail_item_id = $po_item_id
+               WHERE supp_trans_type = ".ST_SUPPINVOICE." AND stock_id = "
+               .db_escape($stock_id)." AND po_detail_item_id = ".db_escape($po_item_id)."
                AND supp_trans_no = trans_no";
        $result = db_query($sql, "Cannot retreive supplier transaction detail records");
        return db_fetch($result);