[Weberp-cvs] SF.net SVN: web-erp:[4159] trunk

[<tim...@us...>]

Revision: 4159
          http://web-erp.svn.sourceforge.net/web-erp/?rev=4159&view=rev
Author:   tim_schofield
Date:     2010-11-18 10:00:57 +0000 (Thu, 18 Nov 2010)

Log Message:
-----------
Fix sql syntax errors brought in on recent changes.

Modified Paths:
--------------
    trunk/StockDispatch.php
    trunk/doc/Change.log.html
    trunk/sql/mysql/upgrade3.11.1-4.00.sql

Modified: trunk/StockDispatch.php
===================================================================
--- trunk/StockDispatch.php	2010-11-16 08:57:29 UTC (rev 4158)
+++ trunk/StockDispatch.php	2010-11-18 10:00:57 UTC (rev 4159)
@@ -55,7 +55,7 @@
 	}
 
 
-	$sql = 'SELECT locstock.stockid,
+	$sql = "SELECT locstock.stockid,
 				stockmaster.description,
 				locstock.loccode,
 				locstock.quantity,
@@ -64,22 +64,25 @@
 				stockmaster.serialised,
 				stockmaster.controlled,
 				ROUND((locstock.reorderlevel - locstock.quantity) *
-				   (1 + (' . $_POST['Percent'] . '/100)))
+				   (1 + (" . $_POST['Percent'] . "/100)))
 				as neededqty,
 			   (fromlocstock.quantity - fromlocstock.reorderlevel)  as available,
 			   fromlocstock.reorderlevel as fromreorderlevel,
 			   fromlocstock.quantity as fromquantity
-			FROM stockmaster,
-				locstock
+			FROM stockmaster
+			LEFT JOIN stockcategory
+				ON stockmaster.categoryid=stockcategory.categoryid,
+			locstock
 			LEFT JOIN locstock AS fromlocstock ON
 			  locstock.stockid = fromlocstock.stockid
-			  AND fromlocstock.loccode = "' . $_POST['FromLocation'] . '"
+			  AND fromlocstock.loccode = '" . $_POST['FromLocation'] . "'
 			WHERE locstock.stockid=stockmaster.stockid
-			AND locstock.loccode ="' . $_POST['ToLocation'] . '"
+			AND locstock.loccode ='" . $_POST['ToLocation'] . "'
 			AND locstock.reorderlevel > locstock.quantity
 			AND (fromlocstock.quantity - fromlocstock.reorderlevel) > 0
-			AND (stockmaster.mbflag="B" OR stockmaster.mbflag="M") ' .
-			$wherecategory . ' ORDER BY locstock.loccode,locstock.stockid';
+			AND stockcategory.stocktype<>'A'
+			AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M') " .
+			$wherecategory . " ORDER BY locstock.loccode,locstock.stockid";
 
 	$result = DB_query($sql,$db,'','',false,true);
 
@@ -257,7 +260,7 @@
 	}
 	echo '</select></td></tr>';
 
-	$SQL='SELECT categoryid, categorydescription FROM stockcategory ORDER BY categorydescription';
+	$SQL='SELECT categoryid, categorydescription FROM stockcategory WHERE stocktype<>"A" ORDER BY categorydescription';
 	$result1 = DB_query($SQL,$db);
 	if (DB_num_rows($result1)==0){
 		echo '</table></td></tr>

Modified: trunk/doc/Change.log.html
===================================================================
--- trunk/doc/Change.log.html	2010-11-16 08:57:29 UTC (rev 4158)
+++ trunk/doc/Change.log.html	2010-11-18 10:00:57 UTC (rev 4159)
@@ -1,5 +1,6 @@
 <p><font SIZE=4 COLOR=BLUE><b>webERP Change Log</b></font></p>
 <p></p>
+<p>18/11/10 Tim: upgrade3.11.1-4.00.sql - Fix sql syntax errors brought in on recent changes.</p>
 <p>14/11/10 Phil: SelectAsset.php script reworked SelectAssetType.php script now deleted</p>
 <p>08/11/10 Exson: Z_ImportStocks.php - Bug fixes.</p>
 <p>06/11/10 Phil: ShiptChgs.php - made a check to ensure a shipment reference entered manully actually exists before it is added - otherwise a nasty error occurs on commital of the invoice</p>

Modified: trunk/sql/mysql/upgrade3.11.1-4.00.sql
===================================================================
--- trunk/sql/mysql/upgrade3.11.1-4.00.sql	2010-11-16 08:57:29 UTC (rev 4158)
+++ trunk/sql/mysql/upgrade3.11.1-4.00.sql	2010-11-18 10:00:57 UTC (rev 4159)
@@ -147,7 +147,7 @@
 ALTER TABLE `purchorders` ADD COLUMN `tel` varchar(15) NOT NULL DEFAULT '' AFTER deladd6;
 ALTER TABLE `purchorders` ADD COLUMN `port` varchar(40) NOT NULL DEFAULT '' ;
 
-ALTER TABLE `suppliers` DROP KEY `suppliers_ibfk_4`;
+ALTER TABLE `suppliers` DROP FOREIGN KEY `suppliers_ibfk_4`;
 UPDATE `suppliers` SET `factorcompanyid`=0 WHERE `factorcompanyid`=1;
 DELETE FROM `factorcompanies` WHERE `coyname`='None';
 
@@ -436,7 +436,7 @@
 
 ALTER TABLE `stockcatproperties` ADD `maximumvalue` DOUBLE NOT NULL DEFAULT 999999999 AFTER `defaultvalue` ,
 ADD `minimumvalue` DOUBLE NOT NULL DEFAULT -999999999,
-ADD `numericvalue` TINYINT NOT NULL DEFAULT 0 
+ADD `numericvalue` TINYINT NOT NULL DEFAULT 0 ;
 
 RENAME TABLE assetmanager to fixedassets;
 ALTER TABLE fixedassets ADD COLUMN `assetcategoryid` varchar(6) NOT NULL DEFAULT '';
@@ -455,11 +455,11 @@
   `disposalact` int(11) NOT NULL DEFAULT '80000',
   `accumdepnact` int(11) NOT NULL DEFAULT '0',
   defaultdepnrate double NOT NULL DEFAULT '.2',
-  defaultdepntype int NOT NULL DEFAULT '1'
+  defaultdepntype int NOT NULL DEFAULT '1',
   PRIMARY KEY (`categoryid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-INSERT INTO fixedassetcategories SELECT categoryid, categorydescription, stockact, adjglact, materialuseagevarac, wipact FROM stockcategory WHERE stocktype='A';
+INSERT INTO fixedassetcategories (categoryid, categorydescription, costact, depnact, disposalact, accumdepnact) SELECT categoryid, categorydescription, stockact, adjglact, materialuseagevarac, wipact FROM stockcategory WHERE stocktype='A';
 
 DELETE locstock.* FROM locstock INNER JOIN stockmaster ON locstock.stockid=stockmaster.stockid INNER JOIN stockcategory ON stockmaster.categoryid=stockcategory.categoryid WHERE stockcategory.stocktype='A';
 


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