purchasing/includes/purchasing_db.inc

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 include_once($path_to_root . "/purchasing/includes/supp_trans_class.inc");
  13
  14 include_once($path_to_root . "/includes/banking.inc");
  15
  16 include_once($path_to_root . "/includes/date_functions.inc");
  17
  18 include_once($path_to_root . "/includes/db/inventory_db.inc");
  19
  20 include_once($path_to_root . "/purchasing/includes/db/supp_trans_db.inc");
  21 include_once($path_to_root . "/purchasing/includes/db/po_db.inc");
  22 include_once($path_to_root . "/purchasing/includes/db/grn_db.inc");
  23 include_once($path_to_root . "/purchasing/includes/db/invoice_db.inc");
  24 include_once($path_to_root . "/purchasing/includes/db/suppalloc_db.inc");
  25 include_once($path_to_root . "/purchasing/includes/db/supp_payment_db.inc");
  26 include_once($path_to_root . "/purchasing/includes/db/suppliers_db.inc");
  27
  28 //-------------------------------------------------------------------------------------------------------------
  29
  30 // add a supplier-related gl transaction
  31 // $date_ is display date (non-sql)
  32 // $amount is in SUPPLIERS'S currency
  33
  34 function add_gl_trans_supplier($type, $type_no, $date_, $account, $dimension, $dimension2,
  35         $amount, $supplier_id, $err_msg="", $rate=0, $memo="")
  36 {
  37         if ($err_msg == "")
  38                 $err_msg = "The supplier GL transaction could not be inserted";
  39
  40         return add_gl_trans($type, $type_no, $date_, $account, $dimension, $dimension2, $memo,
  41                 $amount, get_supplier_currency($supplier_id),
  42                 payment_person_types::supplier(), $supplier_id, $err_msg, $rate);
  43 }
  44
  45 //----------------------------------------------------------------------------------------
  46
  47 function get_purchase_price($supplier_id, $stock_id)
  48 {
  49         $sql = "SELECT price, conversion_factor FROM ".TB_PREF."purch_data
  50                 WHERE supplier_id = '" . $supplier_id . "'
  51                 AND stock_id = '". $stock_id . "'";
  52         $result = db_query($sql, "The supplier pricing details for " . $stock_id . " could not be retrieved");
  53
  54         if (db_num_rows($result) == 1)
  55         {
  56                 $myrow = db_fetch($result);
  57                 return $myrow["price"] / $myrow['conversion_factor'];
  58         }
  59         else
  60         {
  61                 return 0;
  62         }
  63 }
  64
  65 function get_purchase_conversion_factor($supplier_id, $stock_id)
  66 {
  67         $sql = "SELECT conversion_factor FROM ".TB_PREF."purch_data
  68                 WHERE supplier_id = '" . $supplier_id . "'
  69                 AND stock_id = '". $stock_id . "'";
  70         $result = db_query($sql, "The supplier pricing details for " . $stock_id . " could not be retrieved");
  71
  72         if (db_num_rows($result) == 1)
  73         {
  74                 $myrow = db_fetch($result);
  75                 return $myrow['conversion_factor'];
  76         }
  77         else
  78         {
  79                 return 1;
  80         }
  81 }
  82 //----------------------------------------------------------------------------------------
  83
  84 function get_purchase_data($supplier_id, $stock_id)
  85 {
  86         $sql = "SELECT * FROM ".TB_PREF."purch_data
  87                 WHERE supplier_id = '" . $supplier_id . "'
  88                 AND stock_id = '". $stock_id . "'";
  89         $result = db_query($sql, "The supplier pricing details for " . $stock_id . " could not be retrieved");
  90
  91         return db_fetch($result);
  92 }
  93
  94 function add_or_update_purchase_data($supplier_id, $stock_id, $price, $description="", $uom="")
  95 {
  96         $data = get_purchase_data($supplier_id, $stock_id);
  97         if ($data === false)
  98         {
  99                 $sql = "INSERT INTO ".TB_PREF."purch_data (supplier_id, stock_id, price, suppliers_uom,
 100                         conversion_factor, supplier_description) VALUES ('$supplier_id', '$stock_id',
 101                         $price, '$uom', 1, ".db_escape($description).")";
 102                 db_query($sql,"The supplier purchasing details could not be added");
 103                 return;
 104         }
 105         $price = round($price * $data['conversion_factor'], user_price_dec());
 106         $sql = "UPDATE ".TB_PREF."purch_data SET price=$price";
 107         if ($uom != "")
 108                 $sql .= ",suppliers_uom='$uom'";
 109         if ($description != "")
 110                 $sql .= ",supplier_description=".db_escape($description);
 111         $sql .= " WHERE stock_id='$stock_id' AND supplier_id='$supplier_id'";
 112         db_query($sql,"The supplier purchasing details could not be updated");
 113         return true;
 114 }
 115
 116 ?>