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

[<te...@us...>]

Revision: 7319
          http://sourceforge.net/p/web-erp/reponame/7319
Author:   tehonu
Date:     2015-06-11 09:49:54 +0000 (Thu, 11 Jun 2015)
Log Message:
-----------
Creation of table loctransfercancellations to keep track of cancelled quantities of items in transfers and simplication of code on StocLocTransferReceive.php. More reports on loctransfercancellations will come on the next days.

Modified Paths:
--------------
    trunk/StockLocTransferReceive.php
    trunk/sql/mysql/upgrade4.12.3-4.13.sql

Modified: trunk/StockLocTransferReceive.php
===================================================================
--- trunk/StockLocTransferReceive.php	2015-06-11 08:13:31 UTC (rev 7318)
+++ trunk/StockLocTransferReceive.php	2015-06-11 09:49:54 UTC (rev 7319)
@@ -67,7 +67,7 @@
 		$Result = DB_Txn_Begin(); // The Txn should affect the full transfer
 
 		foreach ($_SESSION['Transfer']->TransferItem AS $TrfLine) {
-			if($TrfLine->Quantity >0) {
+			if($TrfLine->Quantity >= 0) {
 
 				/* Need to get the current location quantity will need it later for the stock movement */
 				$SQL="SELECT locstock.quantity
@@ -393,6 +393,7 @@
 				prnMsg(_('A stock transfer for item code'). ' - '  . $TrfLine->StockID . ' ' . $TrfLine->ItemDescription . ' '. _('has been created from').' ' . $_SESSION['Transfer']->StockLocationFromName . ' '. _('to'). ' ' . $_SESSION['Transfer']->StockLocationToName . ' ' . _('for a quantity of'). ' '. $TrfLine->Quantity,'success');
 
 				if($TrfLine->CancelBalance==1) {
+					RecordItemCancelledInTransfer($_SESSION['Transfer']->TrfID, $TrfLine->StockID, $TrfLine->Quantity);
 					$sql = "UPDATE loctransfers SET recqty = recqty + '". round($TrfLine->Quantity, $TrfLine->DecimalPlaces) . "',
 						shipqty = recqty + '". round($TrfLine->Quantity, $TrfLine->DecimalPlaces) . "',
 								recdate = '".Date('Y-m-d H:i:s'). "'
@@ -408,13 +409,8 @@
 				$Result = DB_query($sql, $ErrMsg, $DbgMsg, true);
 				unset ($_SESSION['Transfer']->LineItem[$i]);
 				unset ($_POST['Qty' . $i]);
-			} /*end if Quantity > 0 */
+			} /*end if Quantity >= 0 */
 			if($TrfLine->CancelBalance==1) {
-				$sql = "UPDATE loctransfers SET shipqty = recqty
-						WHERE reference = '". $_SESSION['Transfer']->TrfID . "'
-						AND stockid = '".  $TrfLine->StockID."'";
-				$ErrMsg =  _('CRITICAL ERROR') . '! ' . _('Unable to set the quantity received to the quantity shipped to cancel the balance on this transfer line');
-				$Result = DB_query($sql, $ErrMsg, $DbgMsg, true);
 				// send an email to the inventory manager about this cancellation (as can lead to employee fraud)
 				if($_SESSION['InventoryManagerEmail']!='') {
 					$ConfirmationText = _('Cancelled balance of transfer'). ': ' . $_SESSION['Transfer']->TrfID .
@@ -686,4 +682,25 @@
           </form>';
 }
 include('includes/footer.inc');
+
+function RecordItemCancelledInTransfer($TransferReference, $StockID, $CancelQty){
+	$SQL = "INSERT INTO loctransfercancellations (
+			reference,
+			stockid,
+			cancelqty,
+			canceldate,
+			canceluserid)
+		VALUES ('" . $TransferReference . "',
+			'" . $StockID . "',
+			(SELECT (l2.shipqty-l2.recqty)
+				FROM loctransfers AS l2
+				WHERE l2.reference = '" . $TransferReference . "'
+					AND l2.stockid ='" . $StockID . "') - " . $CancelQty . ",
+			'" . Date('Y-m-d H:i:s') . "',
+			'" . $_SESSION['UserID'] . "')";
+	$ErrMsg =  _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The transfer cancellation record could not be inserted because');
+	$DbgMsg =  _('The following SQL to insert records was used');
+	$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
+
+}
 ?>
\ No newline at end of file

Modified: trunk/sql/mysql/upgrade4.12.3-4.13.sql
===================================================================
--- trunk/sql/mysql/upgrade4.12.3-4.13.sql	2015-06-11 08:13:31 UTC (rev 7318)
+++ trunk/sql/mysql/upgrade4.12.3-4.13.sql	2015-06-11 09:49:54 UTC (rev 7319)
@@ -1,4 +1,11 @@
+CREATE TABLE `loctransfercancellations` ( 
+			`reference` INT(11) NOT NULL , 
+			`stockid` VARCHAR(20) NOT NULL , 
+			`cancelqty` DOUBLE NOT NULL , 
+			`canceldate` DATETIME NOT NULL , 
+			`canceluserid` VARCHAR(20) NOT NULL ) ENGINE = InnoDB;
+ALTER TABLE `loctransfercancellations` ADD INDEX `Index1` (`reference`, `stockid`) COMMENT '';
+ALTER TABLE `loctransfercancellations` ADD INDEX `Index2` (`canceldate`, `reference`, `stockid`) COMMENT '';
 
-
 -- Update version number:
 UPDATE config SET confvalue='4.13' WHERE confname='VersionNumber';