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

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

Revision: 6754
          http://sourceforge.net/p/web-erp/reponame/6754
Author:   tehonu
Date:     2014-06-16 06:01:12 +0000 (Mon, 16 Jun 2014)
Log Message:
-----------
New function Related Items. Useful for online shops and other internal business needs

Modified Paths:
--------------
    trunk/SelectProduct.php
    trunk/sql/mysql/upgrade4.11-4.12.sql

Added Paths:
-----------
    trunk/RelatedItemsUpdate.php

Added: trunk/RelatedItemsUpdate.php
===================================================================

--- trunk/RelatedItemsUpdate.php	                        (rev 0)
+++ trunk/RelatedItemsUpdate.php	2014-06-16 06:01:12 UTC (rev 6754)
@@ -0,0 +1,208 @@
+<?php
+
+include('includes/session.inc');
+
+$Title = _('Update Related Items');
+
+include('includes/header.inc');
+include('includes/SQL_CommonFunctions.inc');
+
+//initialise no input errors assumed initially before we test
+$InputError = 0;
+
+
+if (isset($_GET['Item'])){
+	$Item = trim(mb_strtoupper($_GET['Item']));
+}elseif (isset($_POST['Item'])){
+	$Item = trim(mb_strtoupper($_POST['Item']));
+}
+if (isset($_GET['Related'])){
+	$Related = trim(mb_strtoupper($_GET['Related']));
+}elseif (isset($_POST['Related'])){
+	$Related = trim(mb_strtoupper($_POST['Related']));
+}
+echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/inventory.png" title="' . _('Search') . '" alt="" />' . $Title . '</p>';
+
+echo '<a href="' . $RootPath . '/SelectProduct.php">' . _('Back to Items') . '</a><br />';
+
+
+$result = DB_query("SELECT stockmaster.description
+					FROM stockmaster
+					WHERE stockmaster.stockid='".$Item."'",$db);
+$myrow = DB_fetch_row($result);
+
+if (DB_num_rows($result)==0){
+	prnMsg( _('The part code entered does not exist in the database') . ': ' . $Item . _('Only valid parts can have related items entered against them'),'error');
+	$InputError=1;
+}
+
+
+if (!isset($Item)){
+	echo '<p>';
+	prnMsg (_('An item must first be selected before this page is called') . '. ' . _('The product selection page should call this page with a valid product code'),'error');
+	include('includes/footer.inc');
+	exit;
+}
+
+$PartDescription = $myrow[0];
+
+if (isset($_POST['submit'])) {
+
+	/* actions to take once the user has clicked the submit button
+	ie the page has called itself with some user input */
+
+	//first off validate inputs sensible
+	// This gives some date in 1999?? $ZeroDate = Date($_SESSION['DefaultDateFormat'],Mktime(0,0,0,0,0,0));
+
+	$result_related = DB_query("SELECT stockmaster.description,
+							stockmaster.mbflag
+					FROM stockmaster
+					WHERE stockmaster.stockid='".$_POST['Related']."'",$db);
+	$myrow_related = DB_fetch_row($result_related);
+	
+	if (DB_num_rows($result_related)==0){
+		prnMsg( _('The part code entered as related item does not exist in the database') . ': ' . $_POST['Related'] .  _('Only valid parts can be related items'),'error');
+		$InputError=1;
+	}
+
+	$sql = "SELECT related
+				FROM relateditems
+			WHERE stockid='".$Item."'
+				AND related = '" . $_POST['Related'] . "'";
+	$result = DB_query($sql, $db);
+	$myrow = DB_fetch_row($result);
+
+	if (DB_num_rows($result)!=0){
+		prnMsg( _('This related item has already been entered.') , 'warn');
+		$InputError =1;
+	}
+
+	if ($InputError !=1) {
+		$sql = "INSERT INTO relateditems (stockid,
+									related)
+							VALUES ('" . $Item . "',
+								'" . $_POST['Related'] . "')";
+		$ErrMsg = _('The new related item could not be added');
+		$result = DB_query($sql,$db,$ErrMsg);
+
+		prnMsg($_POST['Related'] . ' ' . _('is now related to') . ' ' . $Item,'success');
+
+		/* It is safe to assume that, if A is related to B, B is related to A */
+		$sql_reverse = "SELECT related
+					FROM relateditems
+				WHERE stockid='".$_POST['Related']."'
+					AND related = '" . $Item . "'";
+		$result_reverse = DB_query($sql_reverse, $db);
+		$myrow_reverse = DB_fetch_row($result_reverse);
+
+		if (DB_num_rows($myrow_reverse)==0){
+			$sql = "INSERT INTO relateditems (stockid,
+										related)
+								VALUES ('" . $_POST['Related'] . "',
+									'" . $Item . "')";
+			$ErrMsg = _('The new related item could not be added');
+			$result = DB_query($sql,$db,$ErrMsg);
+			prnMsg($Item . ' ' . _('is now related to') . ' ' . $_POST['Related'],'success');
+		}
+	}
+
+	unset($_POST['Related']);
+
+} elseif (isset($_GET['delete'])) {
+//the link to delete a selected record was clicked instead of the submit button
+
+	/* Again it is safe to assume that we have to delete both relations A to B and B to A */
+
+	$sql="DELETE FROM relateditems
+			WHERE (stockid = '". $Item ."' AND related ='". $_GET['Related'] ."')
+			OR (stockid = '". $_GET['Related'] ."' AND related ='". $Item ."')";
+	$ErrMsg = _('Could not delete this relationshop');
+	$result = DB_query($sql,$db,$ErrMsg);
+	prnMsg( _('This relationship has been deleted'),'success');
+
+}
+
+//Always do this stuff
+
+$sql = "SELECT stockmaster.stockid, 
+			stockmaster.description
+		FROM stockmaster, relateditems
+		WHERE stockmaster.stockid = relateditems.related
+			AND relateditems.stockid='".$Item."'";
+
+$result = DB_query($sql,$db);
+
+if (DB_num_rows($result) > 0) {
+	echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">';
+	echo '<div>';
+	echo '<table class="selection">
+			<tr>
+				<th colspan="3">
+				<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />' .
+				_('Related Items To') . ':
+				<input type="text" required="required" autofocus="autofocus" name="Item" size="22" value="' . $Item . '" maxlength="20" />
+				<input type="submit" name="NewPart" value="' . _('List Related Items') . '" /></th>
+			</tr>';
+
+	echo '<tbody>
+			<tr>
+				<th class="ascending">' . _('Code') . '</th>
+				<th class="ascending">' . _('Description') . '</th>
+				<th>' . _('Delete') . '</th>
+			</tr>';
+
+	$k=0; //row colour counter
+	while ($myrow = DB_fetch_array($result)) {
+		if ($k==1){
+			echo '<tr class="EvenTableRows">';
+			$k=0;
+		} else {
+			echo '<tr class="OddTableRows">';
+			$k=1;
+		}
+
+		echo   '<td>' . $myrow['stockid'] . '</td>
+				<td>' .  $myrow['description'] . '</td>';
+		echo '<td><a href="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?Item=' . $Item . '&amp;Related=' . $myrow['stockid'] . '&amp;delete=yes" onclick="return confirm(\'' . _('Are you sure you wish to delete this relationship?') . '\');">' . _('Delete') . '</a></td>';
+		echo '</tr>';
+
+	}
+	//END WHILE LIST LOOP
+	echo '</tbody></table><br />';
+	echo '</div>
+		  </form>';
+} else {
+	prnMsg(_('There are no items related set up for this part'),'warn');
+}
+
+echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '?Item=' . $Item . '">';
+echo '<div>';
+echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
+if (isset($_GET['Edit'])){
+	/*the price sent with the get is sql format price so no need to filter */
+	$_POST['Related'] = $_GET['Related'];
+}
+
+echo '<br /><table class="selection">';
+
+echo '<tr><th colspan="5"><h3>' . $Item . ' - ' . $PartDescription . '</h3></th></tr>';
+
+echo '<tr><td>' . _('Related Item Code') . ':</td>
+          <td>
+          <input type="text" class="text" required="required" name="Related" size="21" maxlength="20" value="';
+          if (isset($_POST['Related'])) {
+	         echo $_POST['Related'];
+          }
+          echo '" />
+     </td></tr>
+</table>
+<br /><div class="centre">
+<input type="submit" name="submit" value="' . _('Enter') . '/' . _('Amend Relation') . '" />
+</div>';
+
+
+echo '</div>
+      </form>';
+include('includes/footer.inc');
+
+?>
\ No newline at end of file

Modified: trunk/SelectProduct.php
===================================================================
--- trunk/SelectProduct.php	2014-06-14 16:08:55 UTC (rev 6753)
+++ trunk/SelectProduct.php	2014-06-16 06:01:12 UTC (rev 6754)
@@ -501,6 +501,7 @@
 	}
 	echo '<a href="' . $RootPath . '/DiscountCategories.php?StockID=' . $StockID . '">' . _('Maintain Discount Category') . '</a><br />';
     echo '<a href="' . $RootPath . '/StockClone.php?OldStockID=' . $StockID . '">' . _('Clone This Item') . '</a><br />';
+	echo '<a href="' . $RootPath . '/RelatedItemsUpdate.php?Item=' . $StockID . '">' . _('Maintain Related Items') . '</a><br />';
 	echo '<a href="' . $RootPath . '/PriceMatrix.php?StockID=' . $StockID . '">' . _('Maintain Price Matrix') . '</a><br />';
 }
 echo '</td></tr></table>';

Modified: trunk/sql/mysql/upgrade4.11-4.12.sql
===================================================================
--- trunk/sql/mysql/upgrade4.11-4.12.sql	2014-06-14 16:08:55 UTC (rev 6753)
+++ trunk/sql/mysql/upgrade4.11-4.12.sql	2014-06-16 06:01:12 UTC (rev 6754)
@@ -32,6 +32,16 @@
 ALTER TABLE  `debtortrans` CHANGE  `consignment`  `consignment` VARCHAR( 20 ) NOT NULL DEFAULT  '';
 ALTER TABLE `workorders` ADD `closecomments` LONGBLOB NULL DEFAULT NULL ;
 
+CREATE TABLE IF NOT EXISTS `relateditems` (
+  `stockid` varchar(20) CHARACTER SET utf8 NOT NULL,
+  `related` varchar(20) CHARACTER SET utf8 NOT NULL,
+  PRIMARY KEY (`stockid`,`related`),
+  UNIQUE KEY `Related` (`related`,`stockid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+INSERT INTO  `scripts` (`script` ,`pagesecurity` ,`description`) VALUES ('RelatedItemsUpdate.php',  '2',  'Maintains Related Items');
+
 UPDATE config SET confvalue='4.12' WHERE confname='VersionNumber';
 
 
+





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

Accounting & Best Practice Business Administration System

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