Thread: [Web-erp-svn] SF.net SVN: web-erp:[7137] trunk (Page 56)

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Revision: 7137
          http://sourceforge.net/p/web-erp/reponame/7137
Author:   daintree
Date:     2015-02-08 02:15:28 +0000 (Sun, 08 Feb 2015)
Log Message:
-----------
New option to export 24 months sales net of credits to CSV

Modified Paths:
--------------
    trunk/InventoryPlanning.php
    trunk/doc/Change.log
    trunk/includes/DateFunctions.inc

Modified: trunk/InventoryPlanning.php
===================================================================

--- trunk/InventoryPlanning.php	2015-02-07 21:50:57 UTC (rev 7136)
+++ trunk/InventoryPlanning.php	2015-02-08 02:15:28 UTC (rev 7137)
@@ -345,6 +345,111 @@
 		$pdf-> __destruct();
 	}
 
+} elseif (isset($_POST['ExportToCSV'])){ //send the data to a CSV
+
+	function stripcomma($str) { //because we're using comma as a delimiter
+		return str_replace(',', '', str_replace(';','', $str));
+	}
+ /*Now figure out the inventory data to report for the category range under review
+   need QOH, QOO, QDem, Sales Mth -1, Sales Mth -2, Sales Mth -3, Sales Mth -4*/
+	if ($_POST['Location']=='All'){
+		$SQL = "SELECT stockmaster.categoryid,
+						stockmaster.description,
+						stockcategory.categorydescription,
+						locstock.stockid,
+						SUM(locstock.quantity) AS qoh
+					FROM locstock
+					INNER JOIN locationusers ON locationusers.loccode=locstock.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1,
+						stockmaster,
+						stockcategory
+					WHERE locstock.stockid=stockmaster.stockid
+					AND stockmaster.discontinued = 0
+					AND stockmaster.categoryid=stockcategory.categoryid
+					AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M')
+					AND stockmaster.categoryid IN ('". implode("','",$_POST['Categories'])."')
+					GROUP BY stockmaster.categoryid,
+						stockmaster.description,
+						stockcategory.categorydescription,
+						locstock.stockid,
+						stockmaster.stockid
+					ORDER BY stockmaster.categoryid,
+						stockmaster.stockid";
+	} else {
+		$SQL = "SELECT stockmaster.categoryid,
+					locstock.stockid,
+					stockmaster.description,
+					stockcategory.categorydescription,
+					locstock.quantity  AS qoh
+				FROM locstock
+				INNER JOIN locationusers ON locationusers.loccode=locstock.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1,
+					stockmaster,
+					stockcategory
+				WHERE locstock.stockid=stockmaster.stockid
+				AND stockmaster.discontinued = 0
+				AND stockmaster.categoryid IN ('". implode("','",$_POST['Categories'])."')
+				AND stockmaster.categoryid=stockcategory.categoryid
+				AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M')
+				AND locstock.loccode = '" . $_POST['Location'] . "'
+				ORDER BY stockmaster.categoryid,
+					stockmaster.stockid";
+	}
+	$InventoryResult = DB_query($SQL);
+	$CurrentPeriod = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
+	$Periods = array();
+	for ($i=0;$i<24;$i++) {
+		$Periods[$i]['Period'] = $CurrentPeriod - $i;
+		$Periods[$i]['Month'] = GetMonthText(Date('m',mktime(0,0,0,Date('m') - $i,Date('d'),Date('Y')))) .  ' ' . Date('Y',mktime(0,0,0,Date('m') - $i,Date('d'),Date('Y')));
+	}
+	$SQLStarter = "SELECT stockmoves.stockid,";
+	for ($i=0;$i<24;$i++) {
+		$SQLStarter .= "SUM(CASE WHEN prd='" . $Periods[$i]['Period'] . "' THEN -qty ELSE 0 END) AS prd" . $i . ' ';
+		if ($i<23) {
+			$SQLStarter .= ', ';
+		}
+	}
+	$SQLStarter .= "FROM stockmoves
+					INNER JOIN locationusers ON locationusers.loccode=stockmoves.loccode AND locationusers.userid='" .  $_SESSION['UserID'] . "' AND locationusers.canview=1
+					WHERE (type=10 OR type=11)
+					AND stockmoves.hidemovt=0";
+	if ($_POST['Location']!='All'){
+		$SQLStarter .= " AND stockmoves.loccode ='" . $_POST['Location'] . "'";
+	}
+
+
+	$CSVListing = _('Category ID') .','. _('Category Description') .','. _('Stock ID') .','. _('Description') .',' . _('QOH') . ',';
+	for ($i=0;$i<24;$i++) {
+		$CSVListing .= $Periods[$i]['Month'] . ',';
+	}
+	$CSVListing .= "\r\n";
+
+	$Category ='';
+
+	while ($InventoryPlan = DB_fetch_array($InventoryResult)){
+
+		$SQL = $SQLStarter . " AND stockid='" . $InventoryPlan['stockid'] . "' GROUP BY stockmoves.stockid";
+		$SalesResult = DB_query($SQL,_('The stock usage of this item could not be retrieved because'));
+
+		if (DB_num_rows($SalesResult)==0) {
+			$CSVListing .= stripcomma($InventoryPlan['categoryid']) . ',' . stripcomma($InventoryPlan['categorydescription']) . ',' .stripcomma($InventoryPlan['stockid']) . ',' . stripcomma($InventoryPlan['description']) . ',' . stripcomma($InventoryPlan['qoh']) . "\r\n";
+		} else {
+			$SalesRow = DB_fetch_array($SalesResult);
+			$CSVListing .= stripcomma($InventoryPlan['categoryid']) . ',' . stripcomma($InventoryPlan['categorydescription']) . ',' .stripcomma($InventoryPlan['stockid']) . ',' . stripcomma($InventoryPlan['description']) . ',' . stripcomma($InventoryPlan['qoh']);
+			for ($i=0;$i<24;$i++) {
+				$CSVListing .= ',' . $SalesRow['prd' .$i];
+			}
+			$CSVListing .= "\r\n";
+		}
+
+	}
+	header('Content-Encoding: UTF-8');
+    header('Content-type: text/csv; charset=UTF-8');
+    header("Content-disposition: attachment; filename=InventoryPlanning_" .  Date('Y-m-d:h:m:s')  .'.csv');
+    header("Pragma: public");
+    header("Expires: 0");
+    echo "\xEF\xBB\xBF"; // UTF-8
+	echo $CSVListing;
+	exit;
+
 } else { /*The option to print PDF was not hit */
 
 	$Title=_('Inventory Planning Reporting');
@@ -361,8 +466,8 @@
 			<tr>
 				<td>' . _('Select Inventory Categories') . ':</td>
 				<td><select autofocus="autofocus" required="required" minlength="1" size="12" name="Categories[]"multiple="multiple">';
-	$SQL = 'SELECT categoryid, categorydescription 
-			FROM stockcategory 
+	$SQL = 'SELECT categoryid, categorydescription
+			FROM stockcategory
 			ORDER BY categorydescription';
 	$CatResult = DB_query($SQL);
 	while ($MyRow = DB_fetch_array($CatResult)) {
@@ -375,7 +480,7 @@
 	echo '</select>
 			</td>
 		</tr>';
-	
+
 	echo '<tr>
 			<td>' . _('For Inventory in Location') . ':</td>
 			<td><select name="Location">';
@@ -414,6 +519,7 @@
 	<br />
 	<div class="centre">
 		<input type="submit" name="PrintPDF" value="' . _('Print PDF') . '" />
+		<input type="submit" name="ExportToCSV" value="' . _('Export 24 months to CSV') . '" />
 	</div>
 	</div>
 	</form>';

Modified: trunk/doc/Change.log
===================================================================
--- trunk/doc/Change.log	2015-02-07 21:50:57 UTC (rev 7136)
+++ trunk/doc/Change.log	2015-02-08 02:15:28 UTC (rev 7137)
@@ -1,5 +1,6 @@
 webERP Change Log
 
+8/2/15 InventoryPlanning.php now has an option to export the last 24 months usage to CSV
 7/2/15 New script CustomerAccount.php - on screen statement similar to CustomerInquiry.php
 
 6/2/15 Version 4.12.2

Modified: trunk/includes/DateFunctions.inc
===================================================================
--- trunk/includes/DateFunctions.inc	2015-02-07 21:50:57 UTC (rev 7136)
+++ trunk/includes/DateFunctions.inc	2015-02-08 02:15:28 UTC (rev 7137)
@@ -139,6 +139,9 @@
 		case 12:
 			$Month = _('December');
 			break;
+		default:
+			$Month = _('error');
+			break;
 	}
 	return $Month;
 }





Thread: [Web-erp-svn] SF.net SVN: web-erp:[7137] trunk (Page 56)

Accounting & Best Practice Business Administration System

web-erp-svn