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

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

Revision: 5795
          http://sourceforge.net/p/web-erp/reponame/5795
Author:   tehonu
Date:     2013-01-18 05:46:22 +0000 (Fri, 18 Jan 2013)
Log Message:
-----------
Pak Ricard: New script showing raw materials not used in any BOM

Modified Paths:
--------------
    trunk/includes/MainMenuLinksArray.php
    trunk/sql/mysql/upgrade4.09-4.10.sql

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

Added: trunk/MaterialsNotUsed.php
===================================================================
--- trunk/MaterialsNotUsed.php	                        (rev 0)
+++ trunk/MaterialsNotUsed.php	2013-01-18 05:46:22 UTC (rev 5795)
@@ -0,0 +1,86 @@
+<?php
+
+/* Session started in session.inc for password checking and authorisation level check
+config.php is in turn included in session.inc*/
+include ('includes/session.inc');
+$Title = _('Raw Materials Not Used Anywhere');
+include ('includes/header.inc');
+
+$SQL = "SELECT stockmaster.stockid,
+				stockmaster.description,
+				stockmaster.decimalplaces,
+				(stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) AS stdcost,
+				(SELECT SUM(quantity)
+				FROM locstock
+				WHERE locstock.stockid = stockmaster.stockid) AS qoh
+		FROM stockmaster,
+			stockcategory
+		WHERE stockmaster.categoryid = stockcategory.categoryid
+			AND stockcategory.stocktype = 'M'
+			AND stockmaster.discontinued = 0
+			AND NOT EXISTS(
+				SELECT *
+				FROM bom
+				WHERE bom.component = stockmaster.stockid )
+		ORDER BY stockmaster.stockid";
+$result = DB_query($SQL, $db);
+if (DB_num_rows($result) != 0){
+	$TotalValue = 0;
+	echo '<p class="page_title_text" align="center"><strong>' . _('Raw Materials Not Used in any BOM') . '</strong></p>';
+	echo '<div>';
+	echo '<table class="selection">';
+	$TableHeader = '<tr>
+						<th>' . _('#') . '</th>
+						<th>' . _('Code') . '</th>
+						<th>' . _('Description') . '</th>
+						<th>' . _('QOH') . '</th>
+						<th>' . _('Std Cost') . '</th>
+						<th>' . _('Value') . '</th>
+					</tr>';
+	echo $TableHeader;
+	$k = 0; //row colour counter
+	$i = 1;
+	while ($myrow = DB_fetch_array($result)) {
+		if ($k == 1) {
+			echo '<tr class="EvenTableRows">';
+			$k = 0;
+		} else {
+			echo '<tr class="OddTableRows">';
+			$k = 1;
+		}
+		$CodeLink = '<a href="' . $RootPath . '/SelectProduct.php?StockID=' . $myrow['stockid'] . '">' . $myrow['stockid'] . '</a>';
+		$LineValue = $myrow['qoh'] * $myrow['stdcost'];
+		$TotalValue = $TotalValue + $LineValue;
+		
+		printf('<td class="number">%s</td>
+				<td>%s</td>
+				<td>%s</td>
+				<td class="number">%s</td>
+				<td class="number">%s</td>
+				<td class="number">%s</td>
+				</tr>', 
+				$i, 
+				$CodeLink, 
+				$myrow['description'],
+				locale_number_format($myrow['qoh'],$myrow['decimalplaces']),
+				locale_number_format($myrow['stdcost'],$_SESSION['CompanyRecord']['decimalplaces']),
+				locale_number_format($LineValue,$_SESSION['CompanyRecord']['decimalplaces'])
+				);
+		$i++;
+	}
+
+	printf('<td colspan="4">%s</td>
+			<td>%s</td>
+			<td class="number">%s</td>
+			</tr>', 
+			'',
+			_('Total').':', 
+			locale_number_format($TotalValue,$_SESSION['CompanyRecord']['decimalplaces']));
+
+	echo '</table>
+			</div>
+			</form>';
+}
+
+include ('includes/footer.inc');
+?>
\ No newline at end of file

Modified: trunk/includes/MainMenuLinksArray.php
===================================================================
--- trunk/includes/MainMenuLinksArray.php	2013-01-17 02:11:24 UTC (rev 5794)
+++ trunk/includes/MainMenuLinksArray.php	2013-01-18 05:46:22 UTC (rev 5795)
@@ -294,6 +294,7 @@
 													_('Bill Of Material Listing'),
 													_('Indented Bill Of Material Listing'),
 													_('List Components Required'),
+													_('List Materials Not Used Anywhere'),
 													_('Indented Where Used Listing'),
 													_('MRP'),
 													_('MRP Shortages'),
@@ -308,6 +309,7 @@
 												'/BOMListing.php',
 												'/BOMIndented.php',
 												'/BOMExtendedQty.php',
+												'/MaterialsNotUsed.php',
 												'/BOMIndentedReverse.php',
 												'/MRPReport.php',
 												'/MRPShortages.php',

Modified: trunk/sql/mysql/upgrade4.09-4.10.sql
===================================================================
--- trunk/sql/mysql/upgrade4.09-4.10.sql	2013-01-17 02:11:24 UTC (rev 5794)
+++ trunk/sql/mysql/upgrade4.09-4.10.sql	2013-01-18 05:46:22 UTC (rev 5795)
@@ -12,4 +12,5 @@
 INSERT INTO scripts VALUES ('CustomerPurchases.php','5','Shows the purchases a customer has made.');
 INSERT INTO scripts VALUES ('GoodsReceivedNotInvoiced.php','2','Shows the list of goods received but not yet invoiced, both in supplier currency and home currency. Total in home curency should match the GL Account for Goods received not invoiced. Any discrepancy is due to multicurrency errors.');
 INSERT INTO scripts VALUES ('Z_ItemsWithoutPicture.php','15','Shows the list of curent items without picture in webERP');
+INSERT INTO scripts VALUES ('MaterialsNotUsed.php',  '4',  'Lists the items from Raw Material Categories not used in any BOM (thus, not used at all)');
 UPDATE config SET confvalue='4.10.0' WHERE confname='VersionNumber';