includes/db_order_lines.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
  13 /*
  14  * Clear the denormalisation table for a given stock id
  15  */
  16
  17 function clear_queue_quantity_for_item($stock_id) {
  18         if(isset($stock_id)) {
  19         $sql = "DELETE FROM ".TB_PREF."denorm_order_details_queue
  20                                         WHERE stock_id = \"$stock_id\"
  21 ";
  22         }
  23         else {
  24         $sql = "TRUNCATE TABLE ".TB_PREF."denorm_order_details_queue";
  25         }
  26
  27         return db_query($sql, 'Error when trying to clean ".TB_PREF."denorm_order_details_queue');
  28 }
  29
  30
  31 /*
  32  * Update the priority field of the denorm queru
  33  * a given stock id. Requires that table has been cleared
  34  * for the given item.
  35  */
  36 function insert_item_into_queue($stock_id) {
  37         db_query("SELECT @running_quantity := 0");
  38         $sql = " INSERT INTO ".TB_PREF."denorm_order_details_queue
  39                                         SELECT
  40                                                 id,
  41                                                 stk_code,
  42                                                 order_no,
  43                                                 quantity ,
  44                                                 (@running_quantity := @running_quantity + quantity) - quantity,
  45                                                 priority
  46                                         FROM (SELECT id,
  47                                                                                                 stk_code,
  48                                                                                                 sd.order_no,
  49                                                                                                 quantity - qty_sent AS quantity,
  50                                                                                                 IF(priority IS NULL, CONCAT(ord_date, ' 23:59:59'), priority) AS priority
  51                                                                 FROM ".TB_PREF."sales_order_details sd
  52                                                                 NATURAL JOIN ".TB_PREF."sales_orders
  53                                                                 WHERE stk_code = \"$stock_id\"
  54                                                                 AND quantity > qty_sent
  55                                                                 ORDER BY IF(priority IS NULL, CONCAT(ord_date, ' 23:59:59'), priority)
  56                                                                 ) AS d
  57         ";
  58
  59         return db_query($sql, "Problem whilst updating quantity queue for item : $stock_id");
  60 }
  61
  62 /*
  63  * This function update the denormalisation table for
  64  * a given stock id.
  65  */
  66
  67 function update_queue_quantity_for_item($stock_id) {
  68                 begin_transaction();
  69                         clear_queue_quantity_for_item($stock_id);
  70                         insert_item_into_queue($stock_id);
  71                 commit_transaction();
  72                 return true;
  73 }
  74
  75 function update_queue_quantities() {
  76         $sql = "SELECT DISTINCT stk_code from 0_sales_order_details WHERE quantity > qty_sent";
  77         $result = db_query($sql);
  78         while($row=db_fetch($result)) {
  79                 $stock_id = $row['stk_code'];
  80                 update_queue_quantity_for_item($stock_id);
  81         }
  82                 return true;
  83 }
  84
  85 function update_order_detail_priority($detail_id, $priority) {
  86                 $sql = "UPDATE ".TB_PREF."sales_order_details  SET priority='".$priority."' WHERE id = $detail_id ";
  87                 db_query($sql, "can't set priorti to order details $detail_id");
  88 }
  89
  90 ?>