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

[<ex...@us...>]

Revision: 7522
          http://sourceforge.net/p/web-erp/reponame/7522
Author:   exsonqu
Date:     2016-05-15 09:46:15 +0000 (Sun, 15 May 2016)
Log Message:
-----------
15/05/16 Exson: Add sequence digitals to make BOM sequences can be adjusted flexible and avoid any uncertainty of the number stored in SQL. Thanks Tim's suggestion.

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

Modified: trunk/BOMs.php
===================================================================
--- trunk/BOMs.php	2016-05-15 05:17:01 UTC (rev 7521)
+++ trunk/BOMs.php	2016-05-15 09:46:15 UTC (rev 7522)
@@ -16,7 +16,9 @@
 
 	// retrive all children of parent
 	$c_result = DB_query("SELECT parent,
-								component
+						component,
+						sequence/pow(10,digitals) 
+							AS sequence
 						FROM bom
 						WHERE parent='" . $Parent. "'
 						ORDER BY sequence ASC");
@@ -72,6 +74,7 @@
 
 		global $ParentMBflag;
 		$sql = "SELECT bom.sequence,
+						bom.digitals,
 						bom.component,
 						stockcategory.categorydescription,
 						stockmaster.description as itemdescription,
@@ -193,7 +196,7 @@
 					 </tr><tr><td colspan="11" style="text-indent:' . $TextIndent . ';">%s</td><td>%s</td>
 					 </tr>',
 					$Level1,
-					$myrow['sequence'],
+					locale_number_format($myrow['sequence']/pow(10,$myrow['digitals']),'Variable'),
 					$myrow['categorydescription'],
 					$myrow['component'],
 					$myrow['itemdescription'],
@@ -335,9 +338,11 @@
 		}
 
 		if (isset($SelectedParent) AND isset($SelectedComponent) AND $InputError != 1) {
-
-
-			$sql = "UPDATE bom SET sequence='" . $_POST['Sequence'] . "',
+			$Sequence = filter_number_format($_POST['Sequence']);
+			$Digitals = GetDigitals($_POST['Sequence']);
+			$Sequence = $Sequence * pow(10,$Digitals);
+			$sql = "UPDATE bom SET sequence='" . $Sequence . "',
+						digitals = '" . $Digitals . "',
 						workcentreadded='" . $_POST['WorkCentreAdded'] . "',
 						loccode='" . $_POST['LocCode'] . "',
 						effectiveafter='" . $EffectiveAfterSQL . "',
@@ -377,8 +382,12 @@
 				$result = DB_query($sql,$ErrMsg,$DbgMsg);
 
 				if (DB_num_rows($result)==0) {
+					$Sequence = filter_number_format($_POST['Sequence']);
+					$Digitals = GetDigitals($_POST['Sequence']);
+					$Sequence = $Sequence * pow(10,$Digitals);
 
 					$sql = "INSERT INTO bom (sequence,
+									digitals,
 											parent,
 											component,
 											workcentreadded,
@@ -388,7 +397,8 @@
 											effectiveto,
 											autoissue,
 											remark)
-							VALUES ('" . $_POST['Sequence'] . "',
+							VALUES ('" . $Sequence . "',
+								'" . $Digitals . "',
 								'".$SelectedParent."',
 								'" . $_POST['Component'] . "',
 								'" . $_POST['WorkCentreAdded'] . "',
@@ -681,6 +691,7 @@
 		//editing a selected component from the link to the line item
 
 			$sql = "SELECT sequence,
+						digitals,
 						bom.loccode,
 						effectiveafter,
 						effectiveto,
@@ -696,7 +707,7 @@
 			$result = DB_query($sql);
 			$myrow = DB_fetch_array($result);
 
-			$_POST['Sequence'] = $myrow['sequence'];
+			$_POST['Sequence'] = locale_number_format($myrow['sequence']/pow(10,$myrow['digitals']),'Variable');
 			$_POST['LocCode'] = $myrow['loccode'];
 			$_POST['EffectiveAfter'] = ConvertSQLDate($myrow['effectiveafter']);
 			$_POST['EffectiveTo'] = ConvertSQLDate($myrow['effectiveto']);
@@ -774,7 +785,7 @@
 		}
 		echo '<tr>
                 <td>' . _('Sequence in BOM') . ':</td>
-                <td><input type="text" required="required" size="5" name="Sequence" value="' . $_POST['Sequence'] . '" /></td>
+                <td><input type="text" required="required" size="5" name="Sequence" value="' . $_POST['Sequence'] . '" /> ' . _('Number with decimal places is acceptable') . '</td>
             </tr>';
 		echo '<tr>
 				<td>' . _('Location') . ': </td>
@@ -1065,4 +1076,9 @@
 }
 
 include('includes/footer.inc');
+function GetDigitals($Sequence) {
+	$SQLNumber = filter_number_format($Sequence);
+	return strlen(substr(strrchr($SQLNumber, "."),1));
+}
+
 ?>

Modified: trunk/sql/mysql/upgrade4.12.3-4.13.sql
===================================================================
--- trunk/sql/mysql/upgrade4.12.3-4.13.sql	2016-05-15 05:17:01 UTC (rev 7521)
+++ trunk/sql/mysql/upgrade4.12.3-4.13.sql	2016-05-15 09:46:15 UTC (rev 7522)
@@ -55,7 +55,26 @@
 ALTER table pctabs CHANGE authorizer authorizer varchar(100);
 ALTER table pctabs CHANGE assigner assigner varchar(100);
 INSERT INTO securitytokens VALUES(18,'Cost authority');
-ALTER table bom change sequence sequence double not null default 0;
+ALTER table BOM ADD digitals int(11) NOT NULL DEFAULT 0;
+INSERT INTO scripts VALUES('StockIntransitStatus.php',1,'Inventory transaction status');
+INSERT INTO scripts VALUES ('StockTransferControlledDispatched.php',11,'Inventory controlled input');
+ALTER table loctransfers ADD closed tinyint(1) not null default '0';
+CREATE table trfserialno (trfno int(11) NOT NULL AUTO_INCREMENT,
+			trfref int(11) NOT NUll DEFAULT '0',
+			stkcode varchar(20) NOT NULL DEFAULT '0',
+			serialno varchar(20) NOT NULL DEFAULT '0',
+			trfqty double NOT NULL DEFAULT '0',
+			loccode varchar(5) NOT NULL DEFAULT '',
+			recqty double NOT NULL DEFAULT '0',
+			PRIMARY KEY (`trfno`),
+			KEY (`trfref`),
+			KEY (`stkcode`,`serialno`),
+			KEY (`serialno`),
+			KEY (`stkcode`),
+			KEY (`stkcode`,`serialno`,`loccode`),
+			CONSTRAINT FOREIGN KEY (`trfref`) REFERENCES `loctransfers`(`reference`),
+			CONSTRAINT FOREIGN KEY (`stkcode`,`serialno`,`loccode`) REFERENCES `stockserialitems`(`stockid`,`serialno`,`loccode`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
+			
 
 -- Update version number:
 UPDATE config SET confvalue='4.13' WHERE confname='VersionNumber';