[Web-erp-svn] SF.net SVN: web-erp:[5847] trunk

[<dai...@us...>]

Revision: 5847
          http://sourceforge.net/p/web-erp/reponame/5847
Author:   daintree
Date:     2013-04-19 11:33:08 +0000 (Fri, 19 Apr 2013)
Log Message:
-----------
BOMExtendedQty missing purchase orders as reported by Bob Thoma s

Modified Paths:
--------------
    trunk/BOMExtendedQty.php
    trunk/Stocks.php
    trunk/Z_ChangeStockCode.php
    trunk/doc/Change.log

Modified: trunk/BOMExtendedQty.php
===================================================================
--- trunk/BOMExtendedQty.php	2013-04-18 06:18:30 UTC (rev 5846)
+++ trunk/BOMExtendedQty.php	2013-04-19 11:33:08 UTC (rev 5847)
@@ -53,8 +53,8 @@
 					   CONCAT(bom.parent,bom.component) AS sortpart
 					  FROM bom
 			  WHERE bom.parent ='" . $_POST['Part'] . "'
-			  AND bom.effectiveto >= NOW()
-			  AND bom.effectiveafter <= NOW()";
+			  AND bom.effectiveto >= '" . date('Y-m-d') . "'
+			  AND bom.effectiveafter <= '" . date('Y-m-d') . "'";
 	$result = DB_query($sql,$db);
 
 	$LevelCounter = 2;
@@ -80,8 +80,8 @@
 					 (" . filter_number_format($_POST['Quantity']) . " * bom.quantity) as extendedqpa
 			FROM bom
 			WHERE bom.parent ='" . $_POST['Part'] . "'
-			AND bom.effectiveto >= NOW()
-			AND bom.effectiveafter <= NOW()";
+			AND bom.effectiveto >= '" . date('Y-m-d') . "'
+			AND bom.effectiveafter <= '" . date('Y-m-d') . "'";
 	$result = DB_query($sql,$db);
 	//echo "<br />sql is $sql<br />";
 	// This while routine finds the other levels as long as $ComponentCounter - the
@@ -112,8 +112,8 @@
 					 (bom.quantity * passbom.extendedqpa)
 			 FROM bom,passbom
 			 WHERE bom.parent = passbom.part
-			  AND bom.effectiveto >= NOW()
-			  AND bom.effectiveafter <= NOW()";
+			  AND bom.effectiveto >= '" . date('Y-m-d') . "'
+			  AND bom.effectiveafter <= '" . date('Y-m-d') . "'";
 		$result = DB_query($sql,$db);
 
 		$result = DB_query("DROP TABLE IF EXISTS passbom2",$db);
@@ -134,8 +134,8 @@
 									FROM bom
 									INNER JOIN passbom2
 									ON bom.parent = passbom2.part
-									WHERE bom.effectiveto >= NOW()
-										AND bom.effectiveafter <= NOW()";
+									WHERE bom.effectiveto >= '" . date('Y-m-d') . "'
+									AND bom.effectiveafter <= '" . date('Y-m-d') . "'";
 		$result = DB_query($sql,$db);
 
 		$sql = "SELECT COUNT(bom.parent) AS components
@@ -177,9 +177,11 @@
 					  GROUP BY locstock.stockid) AS qoh,
 				   (SELECT
 					  SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) as netqty
-					  FROM purchorderdetails
+					  FROM purchorderdetails INNER JOIN purchorders
+					  ON purchorderdetails.orderno=purchorders.orderno
 					  WHERE purchorderdetails.itemcode = tempbom.component
-					  AND completed = 0
+					  AND purchorderdetails.completed = 0
+					  AND (purchorders.status = 'Authorised' OR purchorders.status='Printed')
 					  GROUP BY purchorderdetails.itemcode) AS poqty,
 				   (SELECT
 					  SUM(woitems.qtyreqd - woitems.qtyrecd) as netwoqty

Modified: trunk/Stocks.php
===================================================================
--- trunk/Stocks.php	2013-04-18 06:18:30 UTC (rev 5846)
+++ trunk/Stocks.php	2013-04-19 11:33:08 UTC (rev 5847)
@@ -719,8 +719,17 @@
 								$CancelDelete = 1;
 								prnMsg( _('Cannot delete this item because there are offers for this item'),'warn');
 								echo '<br />' . _('There are') . ' ' . $myrow[0] . ' ' . _('offers from suppliers for this part');
+							} else {
+								$sql = "SELECT COUNT(*) FROM tenderitems WHERE stockid='".$StockID."' GROUP BY stockid";
+								$result = DB_query($sql,$db);
+								$myrow = DB_fetch_row($result);
+								if ($myrow[0]!=0) {
+									$CancelDelete = 1;
+									prnMsg( _('Cannot delete this item because there are tenders for this item'),'warn');
+									echo '<br />' . _('There are') . ' ' . $myrow[0] . ' ' . _('tenders from suppliers for this part');
+								}
 							}
-						}
+						} 
 					}
 				}
 			}

Modified: trunk/Z_ChangeStockCode.php
===================================================================
--- trunk/Z_ChangeStockCode.php	2013-04-18 06:18:30 UTC (rev 5846)
+++ trunk/Z_ChangeStockCode.php	2013-04-19 11:33:08 UTC (rev 5847)
@@ -308,7 +308,14 @@
 		$ErrMsg = _('The SQL to update the offer records failed');
 		$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true);
 		echo ' ... ' . _('completed');
-		
+
+
+		echo '<br />' . _('Changing tender items table');
+		$sql = "UPDATE tenderitems SET stockid='" . $_POST['NewStockID'] . "' WHERE stockid='" . $_POST['OldStockID'] . "'";
+		$ErrMsg = _('The SQL to update the tender records failed');
+		$result = DB_query($sql,$db,$ErrMsg,$DbgMsg,true);
+		echo ' ... ' . _('completed');
+
 		DB_ReinstateForeignKeys($db);
 
 		$result = DB_Txn_Commit($db);

Modified: trunk/doc/Change.log
===================================================================
--- trunk/doc/Change.log	2013-04-18 06:18:30 UTC (rev 5846)
+++ trunk/doc/Change.log	2013-04-19 11:33:08 UTC (rev 5847)
@@ -1,5 +1,6 @@
 webERP Change Log
 
+19/4/13 Phil: Reported by Bob Thomas - BOMExtendedQty.php was missing purchase orders with status='Authorised' or Printed
 18/4/13 Tim: Credit_Invoice.php missing $identifier in link causing details of credit note to be lost
 16/4/13 Ricard: Audit trail was not being purged if DB Maintenance was turned off and it should be pruned daily.
 16/04/13 Thumb: Fixed the bug of Y-m-d date format error in MiscFunctions.js (this date type is missing) which will display wrong date in Work Order.