[Weberp-svn] SF.net SVN: weberp:[9376] trunk

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Revision: 9376
          http://weberp.svn.sourceforge.net/weberp/?rev=9376&view=rev
Author:   tim_schofield
Date:     2012-07-05 09:22:12 +0000 (Thu, 05 Jul 2012)
Log Message:
-----------
Improvements to queries to run faster on big data sets

Modified Paths:
--------------
    trunk/InventoryPlanning.php
    trunk/Z_UpgradeDatabase.php

Added Paths:
-----------
    trunk/sql/mysql/updates/138.php

Modified: trunk/InventoryPlanning.php
===================================================================

--- trunk/InventoryPlanning.php	2012-07-04 11:35:55 UTC (rev 9375)
+++ trunk/InventoryPlanning.php	2012-07-05 09:22:12 UTC (rev 9376)
@@ -128,6 +128,86 @@
 	$Period_4 = $CurrentPeriod -4;
 	$Period_5 = $CurrentPeriod -5;
 
+	$sql="CREATE TEMPORARY TABLE stockmovesumm(
+								`stockid` varchar(20) NOT NULL DEFAULT '',
+								`loccode` varchar(5) NOT NULL DEFAULT '',
+								`prd0` double NOT NULL DEFAULT '1.0',
+								`prd1` double NOT NULL DEFAULT '1.0',
+								`prd2` double NOT NULL DEFAULT '1.0',
+								`prd3` double NOT NULL DEFAULT '1.0',
+								`prd4` double NOT NULL DEFAULT '1.0',
+								`prd5` double NOT NULL DEFAULT '1.0'
+							) ENGINE=MEMORY";
+	$result=DB_query($sql, $db);
+
+	$sql = "INSERT INTO stockmovesumm (stockid,
+										loccode,
+										prd0,
+										prd1,
+										prd2,
+										prd3,
+										prd4,
+										prd5)
+				SELECT stockid,
+					loccode,
+					SUM(CASE WHEN prd='" . $CurrentPeriod . "' THEN -qty ELSE 0 END) AS prd0,
+					SUM(CASE WHEN prd='" . $Period_1 . "' THEN -qty ELSE 0 END) AS prd1,
+					SUM(CASE WHEN prd='" . $Period_2 . "' THEN -qty ELSE 0 END) AS prd2,
+					SUM(CASE WHEN prd='" . $Period_3 . "' THEN -qty ELSE 0 END) AS prd3,
+					SUM(CASE WHEN prd='" . $Period_4 . "' THEN -qty ELSE 0 END) AS prd4,
+					SUM(CASE WHEN prd='" . $Period_5 . "' THEN -qty ELSE 0 END) AS prd5
+				FROM stockmoves
+				WHERE (type=10 OR type=11)
+					AND stockmoves.hidemovt=0";
+	$result=DB_query($sql, $db);
+
+	$sql="CREATE TEMPORARY TABLE salesordersumm(
+								`stkcode` varchar(20) NOT NULL DEFAULT '',
+								`fromstkloc` varchar(5) NOT NULL DEFAULT '',
+								`qtydemand` double NOT NULL DEFAULT '0'
+							) ENGINE=MEMORY";
+	$result=DB_query($sql, $db);
+
+	$sql = "INSERT INTO salesordersumm (stkcode,
+										fromstkloc,
+										qtydemand)
+									SELECT salesorderdetails.stkcode,
+											salesorders.fromstkloc,
+											SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qtydemand
+									FROM salesorderdetails
+									INNER JOIN salesorders
+										ON salesorderdetails.orderno=salesorders.orderno
+									WHERE salesorderdetails.completed = 0
+										AND salesorders.quotation=0";
+	$result=DB_query($sql, $db);
+
+	$sql="CREATE TEMPORARY TABLE assemblysalesordersumm(
+								`component` varchar(20) NOT NULL DEFAULT '',
+								`fromstkloc` varchar(5) NOT NULL DEFAULT '',
+								`dem` double NOT NULL DEFAULT '0'
+							) ENGINE=MEMORY";
+	$result=DB_query($sql, $db);
+
+	$sql = "INSERT INTO assemblysalesordersumm (component,
+												fromstkloc,
+												dem)
+											SELECT bom.component,
+												salesorders.fromstkloc,
+												SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
+											FROM salesorderdetails
+											INNER JOIN bom
+												ON salesorderdetails.stkcode=bom.parent
+											INNER JOIN stockmaster
+												ON stockmaster.stockid=bom.parent
+											INNER JOIN salesorders
+												ON salesorders.orderno = salesorderdetails.orderno
+											WHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
+												AND salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
+												AND stockmaster.mbflag='A'
+												AND salesorderdetails.completed=0
+												AND salesorders.quotation=0";
+	$result=DB_query($sql, $db);
+
 	while ($InventoryPlan = DB_fetch_array($InventoryResult,$db)){
 
 		if ($Category!=$InventoryPlan['categoryid']){
@@ -135,7 +215,7 @@
 			if ($Category!=''){ /*Then it's NOT the first time round */
 				/*draw a line under the CATEGORY TOTAL*/
 				$YPos -=$line_height;
-		   		$pdf->line($Left_Margin, $YPos,$Page_Width-$Right_Margin, $YPos);
+				$pdf->line($Left_Margin, $YPos,$Page_Width-$Right_Margin, $YPos);
 				$YPos -=(2*$line_height);
 			}
 
@@ -146,64 +226,53 @@
 
 		$YPos -=$line_height;
 
-
 		if ($_POST['Location']=='All'){
-   		   $SQL = "SELECT SUM(CASE WHEN prd='" . $CurrentPeriod . "' THEN -qty ELSE 0 END) AS prd0,
-		   		SUM(CASE WHEN prd='" . $Period_1 . "' THEN -qty ELSE 0 END) AS prd1,
-				SUM(CASE WHEN prd='" . $Period_2 . "' THEN -qty ELSE 0 END) AS prd2,
-				SUM(CASE WHEN prd='" . $Period_3 . "' THEN -qty ELSE 0 END) AS prd3,
-				SUM(CASE WHEN prd='" . $Period_4 . "' THEN -qty ELSE 0 END) AS prd4,
-				SUM(CASE WHEN prd='" . $Period_5 . "' THEN -qty ELSE 0 END) AS prd5
-			FROM stockmoves
-			WHERE stockid='" . $InventoryPlan['stockid'] . "'
-			AND (type=10 OR type=11)
-			AND stockmoves.hidemovt=0";
+			$SQL = "SELECT prd0,
+							prd1,
+							prd2,
+							prd3,
+							prd4,
+							prd5
+						FROM stockmovesumm
+						WHERE stockid='" . $InventoryPlan['stockid'] . "'";
 		} else {
-  		   $SQL = "SELECT SUM(CASE WHEN prd='" . $CurrentPeriod . "' THEN -qty ELSE 0 END) AS prd0,
-		   		SUM(CASE WHEN prd='" . $Period_1 . "' THEN -qty ELSE 0 END) AS prd1,
-				SUM(CASE WHEN prd='" . $Period_2 . "' THEN -qty ELSE 0 END) AS prd2,
-				SUM(CASE WHEN prd='" . $Period_3 . "' THEN -qty ELSE 0 END) AS prd3,
-				SUM(CASE WHEN prd='" . $Period_4 . "' THEN -qty ELSE 0 END) AS prd4,
-				SUM(CASE WHEN prd='" . $Period_5 . "' THEN -qty ELSE 0 END) AS prd5
-			FROM stockmoves
-			WHERE stockid='" . $InventoryPlan['stockid'] . "'
-			AND stockmoves.loccode ='" . $_POST['Location'] . "'
-			AND (stockmoves.type=10 OR stockmoves.type=11)
-			AND stockmoves.hidemovt=0";
+			$SQL = "SELECT prd0,
+							prd1,
+							prd2,
+							prd3,
+							prd4,
+							prd5
+						FROM stockmovesumm
+						WHERE stockid='" . $InventoryPlan['stockid'] . "'
+							AND stockmovesumm.loccode ='" . $_POST['Location'] . "'";
 		}
 
 		$SalesResult = DB_query($SQL,$db,'','', false, false);
 
 		if (DB_error_no($db) !=0) {
-	 		 $title = _('Inventory Planning') . ' - ' . _('Problem Report') . '....';
-	  		include('includes/header.inc');
-	   		prnMsg( _('The sales quantities could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db),'error');
-	   		echo '<br /><a href="' .$rootpath .'/index.php">' . _('Back to the menu') . '</a>';
-	   		if ($debug==1){
-	  			echo '<br />' . $SQL;
-	   		}
+			$title = _('Inventory Planning') . ' - ' . _('Problem Report') . '....';
+			include('includes/header.inc');
+			prnMsg( _('The sales quantities could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db),'error');
+			echo '<br /><a href="' .$rootpath .'/index.php">' . _('Back to the menu') . '</a>';
+			if ($debug==1){
+				echo '<br />' . $SQL;
+			}
 
-	   		include('includes/footer.inc');
-	   		exit;
+			include('includes/footer.inc');
+			exit;
 		}
 
 		$SalesRow = DB_fetch_array($SalesResult);
 
 		if ($_POST['Location']=='All'){
-			$SQL = "SELECT SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qtydemand
-				FROM salesorderdetails INNER JOIN salesorders
-				ON salesorderdetails.orderno=salesorders.orderno
-				WHERE salesorderdetails.stkcode = '" . $InventoryPlan['stockid'] . "'
-				AND salesorderdetails.completed = 0
-				AND salesorders.quotation=0";
+			$SQL = "SELECT qtydemand
+				FROM salesordersumm
+				WHERE stkcode = '" . $InventoryPlan['stockid'] . "'";
 		} else {
-			$SQL = "SELECT SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qtydemand
-				FROM salesorderdetails INNER JOIN salesorders
-				ON salesorderdetails.orderno=salesorders.orderno
-				WHERE salesorders.fromstkloc ='" . $_POST['Location'] . "'
-				AND salesorderdetails.stkcode = '" . $InventoryPlan['stockid'] . "'
-				AND salesorderdetails.completed = 0
-				AND salesorders.quotation=0";
+			$SQL = "SELECT qtydemand
+				FROM salesordersumm
+				WHERE fromstkloc ='" . $_POST['Location'] . "'
+					AND stkcode = '" . $InventoryPlan['stockid'] . "'";
 		}
 
 		$DemandResult = DB_query($SQL, $db, '', '', false , false);
@@ -224,35 +293,14 @@
 // Also need to add in the demand as a component of an assembly items if this items has any assembly parents.
 
 		if ($_POST['Location']=='All'){
-			$SQL = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
-				FROM salesorderdetails INNER JOIN bom
-					ON salesorderdetails.stkcode=bom.parent
-					INNER JOIN	stockmaster
-					ON stockmaster.stockid=bom.parent
-					INNER JOIN salesorders
-					ON salesorders.orderno = salesorderdetails.orderno
-				WHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
-				AND salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
-				AND bom.component='" . $InventoryPlan['stockid'] . "'
-				AND stockmaster.mbflag='A'
-				AND salesorderdetails.completed=0
-				AND salesorders.quotation=0";
+			$SQL = "SELECT dem
+				FROM assemblysalesordersumm
+				WHERE component='" . $InventoryPlan['stockid'] . "'";
 		} else {
-			$SQL = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
-				FROM salesorderdetails INNER JOIN bom
-					ON salesorderdetails.stkcode=bom.parent
-					INNER JOIN	stockmaster
-					ON stockmaster.stockid=bom.parent
-					INNER JOIN salesorders
-					ON salesorders.orderno = salesorderdetails.orderno
-				WHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
-				AND salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
-				AND bom.component='" . $InventoryPlan['stockid'] . "'
-				AND stockmaster.stockid=bom.parent
-				AND salesorders.fromstkloc ='" . $_POST['Location'] . "'
-				AND stockmaster.mbflag='A'
-				AND salesorderdetails.completed=0
-				AND salesorders.quotation=0";
+			$SQL = "SELECT dem
+				FROM assemblysalesordersumm
+				WHERE component='" . $InventoryPlan['stockid'] . "'
+					AND fromstkloc ='" . $_POST['Location'] . "'";
 		}
 
 		$BOMDemandResult = DB_query($SQL,$db,'','',false,false);
@@ -381,7 +429,7 @@
 
 	/*if $FromCriteria is not set then show a form to allow input	*/
 
-		echo '<form onsubmit="return SubmitForm(this)" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">';
+		echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">';
 		echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
 
 		echo '<table class="selection"><tr>

Modified: trunk/Z_UpgradeDatabase.php
===================================================================
--- trunk/Z_UpgradeDatabase.php	2012-07-04 11:35:55 UTC (rev 9375)
+++ trunk/Z_UpgradeDatabase.php	2012-07-05 09:22:12 UTC (rev 9376)
@@ -44,6 +44,7 @@
 
 	echo '<div class="page_help_text">' . _('You have database updates that are required.').'<br />'.
 		_('Please ensure that you have taken a backup of your current database before continuing.'). '</div><br />';
+	echo '<div class="centre"><button type="submit" name="continue">'. _('Continue With Updates').'</button></div>';
 	echo '</form>';
 } else {
 	$StartingUpdate=$_SESSION['DBUpdateNumber']+1;

Added: trunk/sql/mysql/updates/138.php
===================================================================
--- trunk/sql/mysql/updates/138.php	                        (rev 0)
+++ trunk/sql/mysql/updates/138.php	2012-07-05 09:22:12 UTC (rev 9376)
@@ -0,0 +1,10 @@
+<?php
+
+/* Include the new script for printing quotations in Portrait
+ */
+
+AddIndex(array('categoryid', 'stockid', 'mbflag'), 'stockmaster', 'stockmaster_ibfk_3', $db);
+
+UpdateDBNo(basename(__FILE__, '.php'), $db);
+
+?>
\ No newline at end of file

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.



