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(expiry_date < NOW(), 1, 0), IF(priority IS NULL, CONCAT(ord_date, ' 23:59:59'), priority), hold_until_date, id
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($use_order_date=false) {
122 // Set priority to current time.
123 $sql = "UPDATE ".TB_PREF."sales_order_details";
124 if($use_order_date) {
125 $sql .= " NATURAL JOIN ".TB_PREF."sales_orders
126 SET priority = ord_date";
129 $sql .= " SET priority = now()";
131 $sql .= " WHERE priority is null";
132 $ok &= db_query($sql);
134 // Set hold_until_date to delivery_date
135 $sql = "UPDATE ".TB_PREF."sales_order_details
136 NATURAL JOIN ".TB_PREF."sales_orders
137 SET hold_until_date = delivery_date
138 WHERE hold_until_date is null";
139 $ok &= db_query($sql);
141 // Set expiry date to 6 weeks + delivery date
142 // or 2 weeks + required date. Stuff with an
143 // explicit required date should expire quickly the
144 // required date has been missed.
145 $sql = "UPDATE ".TB_PREF."sales_order_details
146 SET expiry_date = if(required_date is null, hold_until_date + INTERVAL 6 WEEK,
147 required_date + INTERVAL 2 WEEK)
148 WHERE expiry_date is null";
149 $ok &= db_query($sql, $sql);
151 // Set ref to ref order. Usefull to track detail history (if move from orders)
152 $sql = "UPDATE ".TB_PREF."sales_order_details
153 NATURAL JOIN ".TB_PREF."sales_orders
156 $ok &= db_query($sql);