inventory/includes/inventory_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 . "/includes/date_functions.inc");
  13 include_once($path_to_root . "/includes/banking.inc");
  14 include_once($path_to_root . "/includes/inventory.inc");
  15
  16 include_once($path_to_root . "/inventory/includes/db/items_category_db.inc");
  17 include_once($path_to_root . "/inventory/includes/db/items_prices_db.inc");
  18 include_once($path_to_root . "/inventory/includes/db/items_purchases_db.inc");
  19 include_once($path_to_root . "/inventory/includes/db/items_codes_db.inc");
  20 include_once($path_to_root . "/inventory/includes/db/items_db.inc");
  21 include_once($path_to_root . "/inventory/includes/db/items_locations_db.inc");
  22 include_once($path_to_root . "/inventory/includes/db/items_adjust_db.inc");
  23 include_once($path_to_root . "/inventory/includes/db/items_transfer_db.inc");
  24 include_once($path_to_root . "/inventory/includes/db/items_units_db.inc");
  25
  26 function item_img_name($stock_id)
  27 {
  28         $stock_id = strtr($stock_id, "><\\/:|*?", '________');
  29         return clean_file_name($stock_id);
  30 }
  31
  32 function get_stock_movements($stock_id, $StockLocation, $BeforeDate, $AfterDate)
  33 {
  34         $before_date = date2sql($BeforeDate);
  35         $after_date = date2sql($AfterDate);
  36         // PO Delivery and Customer Credit Notes references should be saved in stock moves reference in 2.5
  37         $sql = "SELECT move.*, IF(ISNULL(supplier.supplier_id), debtor.name, supplier.supp_name) name,
  38                 IF(move.type=".ST_SUPPRECEIVE.", grn.reference, IF(move.type=".ST_CUSTCREDIT.", cust_trans.reference, move.reference)) reference";
  39
  40         if(!$StockLocation) {
  41                  $sql .= ", move.loc_code";
  42         }
  43         $sql.=    " FROM ".TB_PREF."stock_moves move
  44                                 LEFT JOIN ".TB_PREF."supp_trans credit ON credit.trans_no=move.trans_no AND credit.type=move.type
  45                                 LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=move.trans_no AND move.type=".ST_SUPPRECEIVE."
  46                                 LEFT JOIN ".TB_PREF."suppliers supplier ON IFNULL(grn.supplier_id, credit.supplier_id)=supplier.supplier_id
  47                                 LEFT JOIN ".TB_PREF."debtor_trans cust_trans ON cust_trans.trans_no=move.trans_no AND cust_trans.type=move.type
  48                                 LEFT JOIN ".TB_PREF."debtors_master debtor ON cust_trans.debtor_no=debtor.debtor_no
  49                 WHERE";
  50
  51         if ($StockLocation) {
  52         $sql.= " move.loc_code=".db_escape($StockLocation)." AND";
  53         }
  54
  55         $sql.= " move.tran_date >= '". $after_date . "'
  56                 AND move.tran_date <= '" . $before_date . "'
  57                 AND move.stock_id = ".db_escape($stock_id) . " ORDER BY move.tran_date, move.trans_id";
  58
  59         return db_query($sql, "could not query stock moves");
  60 }
  61
  62 function calculate_reorder_level($location, $line, &$st_ids, &$st_names, &$st_num, &$st_reorder)
  63 {
  64         $sql = "SELECT stock.*, loc.location_name, loc.email
  65                 FROM ".TB_PREF."loc_stock stock,"
  66                         .TB_PREF."locations loc
  67                 WHERE stock.loc_code=loc.loc_code
  68                 AND stock.stock_id = '" . $line->stock_id . "'
  69                 AND stock.loc_code = '" . $location . "'";
  70         $res = db_query($sql,"a location could not be retreived");
  71         $loc = db_fetch($res);
  72         if ($loc['email'] != "")
  73         {
  74                 $qoh = get_qoh_on_date($line->stock_id, $location);
  75                 $qoh -= get_demand_qty($line->stock_id, $location);
  76                 $qoh -= get_demand_asm_qty($line->stock_id, $location);
  77                 $qoh -= $line->quantity;
  78                 if ($qoh < $loc['reorder_level'])
  79                 {
  80                         $st_ids[] = $line->stock_id;
  81                         $st_names[] = $line->item_description;
  82                         $st_num[] = $qoh - $loc['reorder_level'];
  83                         $st_reorder[] = $loc['reorder_level'];
  84                 }
  85         }
  86         return $loc;
  87 }
  88
  89 function send_reorder_email($loc, $st_ids, $st_names, $st_num, $st_reorder)
  90 {
  91         global $path_to_root;
  92
  93         require_once($path_to_root . "/reporting/includes/class.mail.inc");
  94         $company = get_company_prefs();
  95         $mail = new email($company['coy_name'], $company['email']);
  96         $to = $loc['location_name'] . " <" . $loc['email'] . ">";
  97         $subject = _("Stocks below Re-Order Level at " . $loc['location_name']);
  98         $msg = "\n";
  99         for ($i = 0; $i < count($st_ids); $i++)
 100                 $msg .= $st_ids[$i] . " " . $st_names[$i] . ", " . _("Re-Order Level") . ": " . $st_reorder[$i] . ", " . _("Below") . ": " . $st_num[$i] . "\n";
 101         $msg .= "\n" . _("Please reorder") . "\n\n";
 102         $msg .= $company['coy_name'];
 103         $mail->to($to);
 104         $mail->subject($subject);
 105         $mail->text($msg);
 106         return $mail->send();
 107 }
 108
 109 //----------------------------------------- Backend interfaces ----------------------------------------------------
 110
 111 function stock_cost_update($stock_id, $material_cost, $labour_cost, $overhead_cost,
 112         $last_cost, $refline, $memo_)
 113 {
 114         begin_transaction(__FUNCTION__, func_get_args());
 115
 116         $mb_flag = get_mb_flag($stock_id);
 117
 118         $update_no = -1;
 119
 120     if (is_service($mb_flag))
 121     {
 122                 $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost)."
 123                 WHERE stock_id=".db_escape($stock_id);
 124
 125                 db_query($sql,"The cost details for the inventory item could not be updated");
 126
 127                 commit_transaction();
 128                 return $update_no;
 129     }
 130
 131         $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost).",
 132                 labour_cost=".db_escape($labour_cost).",
 133                 overhead_cost=".db_escape($overhead_cost)."
 134                 WHERE stock_id=".db_escape($stock_id);
 135         db_query($sql,"The cost details for the inventory item could not be updated");
 136
 137         $qoh = get_qoh_on_date($stock_id);
 138
 139         $date_ = Today();
 140         if (!is_date_in_fiscalyear($date_))
 141                 $date_ = end_fiscalyear();
 142
 143         if ($qoh > 0)
 144         {
 145                 $new_cost = $material_cost + $labour_cost + $overhead_cost;
 146
 147                 $value_of_change = round2($qoh * ($new_cost - $last_cost), user_price_dec());
 148
 149                 if ($value_of_change != 0)
 150                 {
 151                         global $Refs;
 152                         $stock_gl_code = get_stock_gl_code($stock_id);
 153
 154                         $cart = new items_cart(ST_COSTUPDATE);
 155                         $cart->tran_date = $cart->doc_date = $cart->event_date = $date_;
 156                         if (!is_date_in_fiscalyear($cart->tran_date))
 157                                 $cart->tran_date = end_fiscalyear();
 158                         $cart->reference = $Refs->get_next(ST_COSTUPDATE, $refline, $cart->tran_date, $date_);
 159
 160                         if (empty($memo_))
 161                                 $cart->memo_ = sprintf(_("Cost was %s changed to %s x quantity on hand of %s"),
 162                                         number_format2($last_cost, 2), number_format2($new_cost), $qoh);
 163                         else
 164                                 $cart->memo_ = $memo_;
 165
 166                         $cart->add_gl_item($stock_gl_code["adjustment_account"],
 167                                 $stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], -$value_of_change);
 168                         $cart->add_gl_item($stock_gl_code["inventory_account"], 0, 0, $value_of_change);
 169
 170                         write_journal_entries($cart);
 171                 }
 172         }
 173
 174         if ($update_no != -1)
 175                 add_audit_trail(ST_COSTUPDATE, $update_no, $date_);
 176
 177         commit_transaction();
 178         return $update_no;
 179 }
 180
 181 function write_item($id, $NewStockID, $description, $long_description, $category_id, $tax_type_id, $units,
 182         $mb_flag, $sales_account, $inventory_account, $cogs_account, $adjustment_account, $wip_account,
 183         $dim1, $dim2, $no_sale, $editable, $no_purchase, $depreciation_method, $depreciation_rate, $depreciation_factor, $depreciation_start,
 184         $fa_class_id, $vat_category, $shipper, $inactive)
 185 {
 186         begin_transaction(__FUNCTION__, func_get_args());
 187         if ($id != '')
 188         {
 189                 update_item($NewStockID, $description, $long_description, $category_id, $tax_type_id, $units,
 190                         $mb_flag, $sales_account, $inventory_account, $cogs_account, $adjustment_account, $wip_account,
 191                         $dim1, $dim2, $no_sale, $editable, $no_purchase, $depreciation_method, $depreciation_rate, $depreciation_factor, $depreciation_start,
 192                         $fa_class_id, $vat_category, $shipper);
 193                 update_record_status($NewStockID, $inactive, 'stock_master', 'stock_id');
 194                 update_record_status($NewStockID, $inactive, 'item_codes', 'item_code');
 195         } else {
 196                 add_item($NewStockID, $description, $long_description, $category_id, $tax_type_id, $units,
 197                         $mb_flag, $sales_account, $inventory_account, $cogs_account, $adjustment_account, $wip_account,
 198                         $dim1, $dim2, $no_sale, $editable, $no_purchase, $depreciation_method, $depreciation_rate, $depreciation_factor, $depreciation_start,
 199                         $fa_class_id, $vat_category, $shipper);
 200         }
 201         commit_transaction();
 202 }