From 83e65c768351c5c909e1909276579ada77037703 Mon Sep 17 00:00:00 2001
From: Maxime Bourget <bmx007@gmail.com>
Date: Mon, 3 Jun 2013 15:27:11 +0100
Subject: [PATCH] Add denorm_qoh

---
 hooks.php                         |  7 +++++--
 includes/db_order_lines.inc       | 31 ++++++++++++++++++++++++++++++-
 sql/clean_sales_order_details.sql |  1 +
 sql/create_denorm_qoh.sql         |  9 +++++++++
 4 files changed, 45 insertions(+), 3 deletions(-)
 create mode 100644 sql/create_denorm_qoh.sql

diff --git a/hooks.php b/hooks.php
index 778ee8e..8a177c4 100644
--- a/hooks.php
+++ b/hooks.php
@@ -44,11 +44,14 @@ class hooks_order_line_extra extends hooks {
         global $db_connections;
 
         $updates = array(
-						'alter_sales_order_details.sql' => array('sales_order_details'),// ,'required_date'),
+						'alter_sales_order_details.sql' => array('sales_order_details','required_date'),
 						'create_denorm_order_details_queue.sql' => array('denorm_order_details_queue'),
+						'create_denorm_qoh.sql' => array('denorm_qoh'),
         );
 
-        return $this->update_databases($company, $updates, $check_only) && update_queue_quantities();
+				return $this->update_databases($company, $updates, $check_only)
+								&& update_queue_quantities()
+								&& update_qoh_for_item();;
     }
 
     function deactivate_extension($company, $check_only=true)
diff --git a/includes/db_order_lines.inc b/includes/db_order_lines.inc
index e6a6824..f4913bf 100644
--- a/includes/db_order_lines.inc
+++ b/includes/db_order_lines.inc
@@ -73,7 +73,7 @@ function update_queue_quantity_for_item($stock_id) {
 }
 
 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'];
@@ -87,4 +87,33 @@ function update_order_detail_priority($detail_id, $priority) {
 		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');
+}
+
 ?>
diff --git a/sql/clean_sales_order_details.sql b/sql/clean_sales_order_details.sql
index d8ebd99..9636d28 100644
--- a/sql/clean_sales_order_details.sql
+++ b/sql/clean_sales_order_details.sql
@@ -4,3 +4,4 @@ ALTER TABlE 0_sales_order_details
 # DROP COLUMN priority;
 
 DROP TABLE IF EXISTS 0_denorm_order_details_queue;
+DROP TABLE IF EXISTS 0_denorm_qoh;
diff --git a/sql/create_denorm_qoh.sql b/sql/create_denorm_qoh.sql
new file mode 100644
index 0000000..431a05b
--- /dev/null
+++ b/sql/create_denorm_qoh.sql
@@ -0,0 +1,9 @@
+CREATE TABLE 0_denorm_qoh (
+	id INT AUTO_INCREMENT,
+	stock_id VARCHAR(20),
+	loc_code VARCHAR(20),
+	quantity DOUBLE, -- quantity in the order
+	primary key (id),
+	key stock_id (stock_id, loc_code)
+)
+;
-- 
2.30.2