* This function update the denormalisation table for
* a given stock id.
*/
+
function update_queue_quantity_for_item($stock_id) {
begin_transaction();
clear_queue_quantity_for_item($stock_id);
insert_item_into_queue($stock_id);
commit_transaction();
+ return true;
}
function update_queue_quantities() {
- $sql = "SELECT DISTINCT stk_code from 0_sales_order_details WHERE quantity > qty_sent";
+ $sql = "SELECT DISTINCT stk_code from ".TB_PREF."sales_order_details WHERE quantity > qty_sent";
$result = db_query($sql);
while($row=db_fetch($result)) {
$stock_id = $row['stk_code'];
update_queue_quantity_for_item($stock_id);
}
+ return true;
+}
+
+function update_order_detail_priority($detail_id, $priority) {
+ $sql = "UPDATE ".TB_PREF."sales_order_details SET priority='".$priority."' WHERE id = $detail_id ";
+ db_query($sql, "can't set priorti to order details $detail_id");
}
+function update_qoh_for_item($stock_id=null) {
+ clear_qoh_for_item($stock_id);
+ $sql = "INSERT INTO ".TB_PREF."denorm_qoh(stock_id, loc_code, quantity)
+ SELECT
+ stock_id
+ ,loc_code
+ ,sum(qty) as quantity
+ FROM ".TB_PREF."stock_moves
+ WHERE tran_date <= NOW()";
+ if(isset($stock_id)) $sql.= " AND stock_id = '$stock_id'";
+ $sql .= " GROUP BY stock_id, loc_code";
+
+
+ return db_query($sql, $sql);
+
+}
+function clear_qoh_for_item($stock_id=null) {
+ if(isset($stock_id)) {
+ $sql = "DELETE FROM ".TB_PREF."denorm_qoh
+ WHERE stock_id = \"$stock_id\"
+";
+ }
+ else {
+ $sql = "TRUNCATE TABLE ".TB_PREF."denorm_qoh";
+ }
+
+ return db_query($sql, 'Error when trying to clean ".TB_PREF."denorm_qoh');
+}
+
+function update_order_detail_defaults() {
+ // Set priority to current time.
+ $sql = "UPDATE ".TB_PREF."sales_order_details
+ SET priority = now()
+ WHERE priority is null";
+ db_query($sql);
+ // Set hold_until_date to delivery_date
+ $sql = "UPDATE ".TB_PREF."sales_order_details
+ NATURAL JOIN 0_sales_orders
+ SET hold_until_date = delivery_date
+ WHERE hold_until_date is null";
+ db_query($sql);
+
+ // Set expiry date to 6 weeks + delivery date
+ // or 2 weeks + required date. Stuff with an
+ // explicit required date should expire quickly the
+ // required date has been missed.
+ $sql = "UPDATE ".TB_PREF."sales_order_details
+ SET expiry_date = if(required_date is null, hold_until_date + INTERVAL 6 WEEK,
+ required_date + INTERVAL 2 WEEK)
+ WHERE expiry_date is null";
+ db_query($sql);
+}
?>