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 ***********************************************************************/
14 * Clear the denormalisation table for a given stock id
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\"
24 $sql = "TRUNCATE TABLE ".TB_PREF."denorm_order_details_queue";
27 return db_query($sql, 'Error when trying to clean ".TB_PREF."denorm_order_details_queue');
32 * Update the priority field of the denorm queru
33 * a given stock id. Requires that table has been cleared
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
44 (@running_quantity := @running_quantity + quantity) - quantity,
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)
59 return db_query($sql, "Problem whilst updating quantity queue for item : $stock_id");
63 * This function update the denormalisation table for
67 function update_queue_quantity_for_item($stock_id) {
69 clear_queue_quantity_for_item($stock_id);
70 insert_item_into_queue($stock_id);
75 function update_queue_quantities() {
76 $sql = "SELECT DISTINCT stk_code from ".TB_PREF."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);
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");
90 function update_qoh_for_item($stock_id=null) {
91 clear_qoh_for_item($stock_id);
92 $sql = "INSERT INTO ".TB_PREF."denorm_qoh(stock_id, loc_code, quantity)
97 FROM ".TB_PREF."stock_moves
98 WHERE tran_date <= NOW()";
99 if(isset($stock_id)) $sql.= " AND stock_id = '$stock_id'";
100 $sql .= " GROUP BY stock_id, loc_code";
103 return db_query($sql, $sql);
106 function clear_qoh_for_item($stock_id=null) {
107 if(isset($stock_id)) {
108 $sql = "DELETE FROM ".TB_PREF."denorm_qoh
109 WHERE stock_id = \"$stock_id\"
113 $sql = "TRUNCATE TABLE ".TB_PREF."denorm_qoh";
116 return db_query($sql, 'Error when trying to clean ".TB_PREF."denorm_qoh');
119 function update_order_detail_defaults() {
120 // Set priority to current time.
121 $sql = "UPDATE ".TB_PREF."sales_order_details
123 WHERE priority is null";
125 // Set hold_until_date to delivery_date
126 $sql = "UPDATE ".TB_PREF."sales_order_details
127 NATURAL JOIN 0_sales_orders
128 SET hold_until_date = delivery_date
129 WHERE hold_until_date is null";
132 // Set expiry date to 6 weeks + delivery date
133 // or 2 weeks + required date. Stuff with an
134 // explicit required date should expire quickly the
135 // required date has been missed.
136 $sql = "UPDATE ".TB_PREF."sales_order_details
137 SET expiry_date = if(required_date is null, hold_until_date + INTERVAL 6 WEEK,
138 required_date + INTERVAL 2 WEEK)
139 WHERE expiry_date is null";