Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[7137] trunk
|
From: <dai...@us...> - 2015-02-08 02:15:35
|
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;
}
|
