From d03c215ae25f4595d5730cd9996967f1898b2309 Mon Sep 17 00:00:00 2001
From: Maxime Bourget <bmx007@gmail.com>
Date: Sun, 26 May 2013 23:38:36 +0100
Subject: [PATCH] denorm queue works (with running quantity)

---
 hooks.php                                 |  5 +-
 includes/db_order_lines.inc               | 83 +++++++++++++++++++++++
 sql/alter_sales_order_details.sql         |  2 +-
 sql/create_denorm_order_details_queue.sql | 11 +++
 4 files changed, 99 insertions(+), 2 deletions(-)
 create mode 100644 includes/db_order_lines.inc
 create mode 100644 sql/create_denorm_order_details_queue.sql

diff --git a/hooks.php b/hooks.php
index dfac389..fc1943f 100644
--- a/hooks.php
+++ b/hooks.php
@@ -8,6 +8,8 @@
 // ----------------------------------------------------------------
 define ('SS_ORDERLINEX', 100<<8);
 
+include_once('includes/db_order_lines.inc');
+
 class hooks_order_line_extra extends hooks {
 	var $module_name = 'order_line_extra';
 
@@ -47,7 +49,8 @@ class hooks_order_line_extra extends hooks {
 
         );
 
-        return $this->update_databases($company, $updates, $check_only);
+        //return $this->update_databases($company, $updates, $check_only);
+        return $this->update_databases($company, $updates, $check_only) && update_queue_quantities();
     }
 
     function deactivate_extension($company, $check_only=true)
diff --git a/includes/db_order_lines.inc b/includes/db_order_lines.inc
new file mode 100644
index 0000000..6947df1
--- /dev/null
+++ b/includes/db_order_lines.inc
@@ -0,0 +1,83 @@
+<?php
+/**********************************************************************
+    Copyright (C) FrontAccounting, LLC.
+	Released under the terms of the GNU General Public License, GPL, 
+	as published by the Free Software Foundation, either version 3 
+	of the License, or (at your option) any later version.
+    This program is distributed in the hope that it will be useful,
+    but WITHOUT ANY WARRANTY; without even the implied warranty of
+    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
+    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
+***********************************************************************/
+
+/*
+ * Clear the denormalisation table for a given stock id
+ */
+
+function clear_queue_quantity_for_item($stock_id) {
+	if(isset($stock_id)) {
+	$sql = "DELETE FROM ".TB_PREF."denorm_order_details_queue
+					WHERE stock_id = \"$stock_id\"
+";
+	}
+	else {
+	$sql = "TRUNCATE TABLE ".TB_PREF."denorm_order_details_queue";
+	}
+
+	return db_query($sql, 'Error when trying to clean ".TB_PREF."denorm_order_details_queue');
+}
+
+
+/*
+ * Update the priority field of the denorm queru
+ * a given stock id. Requires that table has been cleared
+ * for the given item.
+ */
+function insert_item_into_queue($stock_id) { 
+	db_query("SELECT @running_quantity := 0");
+	$sql = " INSERT INTO ".TB_PREF."denorm_order_details_queue
+					SELECT 
+						id,
+						stk_code,
+						order_no, 
+						quantity ,
+						(@running_quantity := @running_quantity + quantity) - quantity,
+						priority
+					FROM (SELECT id,
+												stk_code,
+												sd.order_no,
+												quantity - qty_sent AS quantity,
+												IF(priority IS NULL, CONCAT(ord_date, ' 23:59:59'), priority) AS priority
+								FROM ".TB_PREF."sales_order_details sd
+								NATURAL JOIN ".TB_PREF."sales_orders
+								WHERE stk_code = \"$stock_id\"
+								AND quantity > qty_sent
+								ORDER BY IF(priority IS NULL, CONCAT(ord_date, ' 23:59:59'), priority)
+								) AS d
+	";
+
+	return db_query($sql, "Problem whilst updating quantity queue for item : $stock_id");
+}
+
+/*
+ * 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();
+}
+
+function update_queue_quantities() {
+	$sql = "SELECT DISTINCT stk_code from 0_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);
+	}
+}
+
+
+?>
diff --git a/sql/alter_sales_order_details.sql b/sql/alter_sales_order_details.sql
index ee5a5b3..2d204eb 100644
--- a/sql/alter_sales_order_details.sql
+++ b/sql/alter_sales_order_details.sql
@@ -2,4 +2,4 @@ ALTER TABlE 0_sales_order_details
 ADD COLUMN required_date DATE,
 ADD COLUMN comment TEXT,
 ADD COLUMN ref VARCHAR(50),
-ADD COLUMN priority INTEGER;
+ADD COLUMN priority DATETIME;
diff --git a/sql/create_denorm_order_details_queue.sql b/sql/create_denorm_order_details_queue.sql
new file mode 100644
index 0000000..8f685dd
--- /dev/null
+++ b/sql/create_denorm_order_details_queue.sql
@@ -0,0 +1,11 @@
+CREATE TABLE 0_denorm_order_details_queue (
+	id INT,
+	stock_id VARCHAR(20),
+	order_id  INT,
+	quantity DOUBLE, -- quantity in the order
+	quantity_before DOUBLE, -- sum  of quantity of other orders which have priority
+	priority DATETIME,
+	primary key (id),
+	key stock_id (stock_id, priority)
+)
+;
-- 
2.30.2