Menu
▾
▴
[Weberp-cvs] SF.net SVN: web-erp:[3001] trunk
|
From: <dai...@us...> - 2009-11-15 06:31:06
|
Revision: 3001
http://web-erp.svn.sourceforge.net/web-erp/?rev=3001&view=rev
Author: daintree
Date: 2009-11-15 06:30:58 +0000 (Sun, 15 Nov 2009)
Log Message:
-----------
New GLTrialBalance_csv.php script that produces a TB for import into OpenOffice scalc that has webERP authentication from GET parameters sent with the URL
Modified Paths:
--------------
trunk/DailySalesInquiry.php
trunk/doc/Change.log.html
trunk/includes/session.inc
trunk/index.php
Added Paths:
-----------
trunk/GLTrialBalance_csv.php
Modified: trunk/DailySalesInquiry.php
===================================================================
--- trunk/DailySalesInquiry.php 2009-11-14 17:32:19 UTC (rev 3000)
+++ trunk/DailySalesInquiry.php 2009-11-15 06:30:58 UTC (rev 3001)
@@ -72,12 +72,13 @@
$StartDateSQL = date('Y-m-d', mktime(0,0,0, (int)$Date_Array[1],1,(int)$Date_Array[0]));
$sql = "SELECT trandate,
- SUM((price*(1-discountpercent) * -qty)) as salesvalue,
+ SUM(price*(1-discountpercent)* (-qty)) as salesvalue,
SUM((standardcost * -qty)) as cost
FROM stockmoves
INNER JOIN custbranch ON stockmoves.debtorno=custbranch.debtorno
AND stockmoves.branchcode=custbranch.branchcode
WHERE (stockmoves.type=10 or stockmoves.type=11)
+ AND show_on_inv_crds =1
AND trandate>='" . $StartDateSQL . "'
AND trandate<='" . $EndDateSQL . "'";
@@ -151,14 +152,14 @@
}
if ($CumulativeTotalSales !=0){
- $AverageGPPercent = ($CumulativeTotalSales - $CumulativeTotalCost)/$CumulativeTotalSales;
+ $AverageGPPercent = ($CumulativeTotalSales - $CumulativeTotalCost)*100/$CumulativeTotalSales;
$AverageDailySales = $CumulativeTotalSales/$BilledDays;
} else {
$AverageGPPercent = 0;
$AverageDailySales = 0;
}
-echo '<td colspan=7>' . _('Total Sales for month') . ': ' . number_format($CumulativeTotalSales,0) . ' ' . _('GP%') . ': ' . number_format($AverageGPPercent,1) . ' ' . _('Avg Daily Sales') . ': ' . number_format($AverageDailySales,0) . '</td></tr>';
+echo '<td colspan=7>' . _('Total Sales for month') . ': ' . number_format($CumulativeTotalSales,0) . ' ' . _('GP%') . ': ' . number_format($AverageGPPercent,1) . '% ' . _('Avg Daily Sales') . ': ' . number_format($AverageDailySales,0) . '</td></tr>';
echo '</table>';
Added: trunk/GLTrialBalance_csv.php
===================================================================
--- trunk/GLTrialBalance_csv.php (rev 0)
+++ trunk/GLTrialBalance_csv.php 2009-11-15 06:30:58 UTC (rev 3001)
@@ -0,0 +1,92 @@
+<?php
+
+/* $Id */
+
+/*Through deviousness and cunning, this system allows trial balances for any date range that recalcuates the p & l balances
+and shows the balance sheets as at the end of the period selected - so first off need to show the input of criteria screen
+while the user is selecting the criteria the system is posting any unposted transactions */
+
+/*Needs to have FromPeriod and ToPeriod sent with URL
+ * also need to work on authentication with username and password sent too*/
+
+$PageSecurity = 8;
+$AllowAnyone =true;
+//$_POST['UserNameEntryField'] = $_GET['Identifier'];
+//$_POST['Password'] = $_GET['IdentifierCheck'];
+//Page must be called with GLTrialBalance_csv.php?CompanyName=XXXXX&FromPeriod=Y&ToPeriod=Z
+$_POST['CompanyNameField'] = $_GET['CompanyName'];
+//$_SERVER['PHP_SELF'] = dirname($_SERVER['PHP_SELF']) .'/GLTrialBalance_csv.php?ToPeriod=' . $_GET['ToPeriod'] . '&FromPeriod=' . $_GET['FromPeriod'];
+
+include ('includes/session.inc');
+include('includes/SQL_CommonFunctions.inc');
+
+include ('includes/GLPostings.inc'); //do any outstanding posting
+
+$NumberOfMonths = $_GET['ToPeriod'] - $_GET['FromPeriod'] + 1;
+
+$RetainedEarningsAct = $_SESSION['CompanyRecord']['retainedearnings'];
+
+$SQL = 'SELECT accountgroups.groupname,
+ accountgroups.parentgroupname,
+ accountgroups.pandl,
+ chartdetails.accountcode ,
+ chartmaster.accountname,
+ Sum(CASE WHEN chartdetails.period=' . $_GET['FromPeriod'] . ' THEN chartdetails.bfwd ELSE 0 END) AS firstprdbfwd,
+ Sum(CASE WHEN chartdetails.period=' . $_GET['FromPeriod'] . ' THEN chartdetails.bfwdbudget ELSE 0 END) AS firstprdbudgetbfwd,
+ Sum(CASE WHEN chartdetails.period=' . $_GET['ToPeriod'] . ' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS lastprdcfwd,
+ Sum(CASE WHEN chartdetails.period=' . $_GET['ToPeriod'] . ' THEN chartdetails.actual ELSE 0 END) AS monthactual,
+ Sum(CASE WHEN chartdetails.period=' . $_GET['ToPeriod'] . ' THEN chartdetails.budget ELSE 0 END) AS monthbudget,
+ Sum(CASE WHEN chartdetails.period=' . $_GET['ToPeriod'] . ' THEN chartdetails.bfwdbudget + chartdetails.budget ELSE 0 END) AS lastprdbudgetcfwd
+ FROM chartmaster INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
+ INNER JOIN chartdetails ON chartmaster.accountcode= chartdetails.accountcode
+ GROUP BY accountgroups.groupname,
+ accountgroups.parentgroupname,
+ accountgroups.pandl,
+ accountgroups.sequenceintb,
+ chartdetails.accountcode,
+ chartmaster.accountname
+ ORDER BY accountgroups.pandl desc,
+ accountgroups.sequenceintb,
+ accountgroups.groupname,
+ chartdetails.accountcode';
+
+$AccountsResult = DB_query($SQL,$db);
+
+while ($myrow=DB_fetch_array($AccountsResult)) {
+
+ if ($myrow['pandl']==1){
+ $AccountPeriodActual = $myrow['lastprdcfwd'] - $myrow['firstprdbfwd'];
+ $AccountPeriodBudget = $myrow['lastprdbudgetcfwd'] - $myrow['firstprdbudgetbfwd'];
+ $PeriodProfitLoss += $AccountPeriodActual;
+ $PeriodBudgetProfitLoss += $AccountPeriodBudget;
+ $MonthProfitLoss += $myrow['monthactual'];
+ $MonthBudgetProfitLoss += $myrow['monthbudget'];
+ $BFwdProfitLoss += $myrow['firstprdbfwd'];
+ } else { /*PandL ==0 its a balance sheet account */
+ if ($myrow['accountcode']==$RetainedEarningsAct){
+ $AccountPeriodActual = $BFwdProfitLoss + $myrow['lastprdcfwd'];
+ $AccountPeriodBudget = $BFwdProfitLoss + $myrow['lastprdbudgetcfwd'] - $myrow['firstprdbudgetbfwd'];
+ } else {
+ $AccountPeriodActual = $myrow['lastprdcfwd'];
+ $AccountPeriodBudget = $myrow['firstprdbfwd'] + $myrow['lastprdbudgetcfwd'] - $myrow['firstprdbudgetbfwd'];
+ }
+ }
+
+ $CSV_File .= $myrow['accountcode'] . ', ' . stripcomma($myrow['accountname']) . ', ' . $AccountPeriodActual . ', ' . $AccountPeriodBudget . "\n";
+}
+
+function stripcomma($str) { //because we're using comma as a delimiter
+ return str_replace(",", "", $str);
+}
+/*
+$len = strlen($CSV_File);
+header('Content-type: application/csv');
+header('Content-Length: ' . $len );
+header('Content-Disposition: inline; filename=GLTrialBalance.csv');
+header('Expires: 0');
+header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
+header('Pragma: public');
+*/
+echo $CSV_File;
+
+?>
\ No newline at end of file
Modified: trunk/doc/Change.log.html
===================================================================
--- trunk/doc/Change.log.html 2009-11-14 17:32:19 UTC (rev 3000)
+++ trunk/doc/Change.log.html 2009-11-15 06:30:58 UTC (rev 3001)
@@ -1,5 +1,6 @@
<p><font SIZE=4 COLOR=BLUE><b>webERP Change Log</b></font></p>
<p>
+<p>15/11/09 Phil: GLTrialBalance_csv.php Creates a csv of the trial balance with parameters for PeriodFrom and PeriodTo - also with webERP username and password set as part of the URL. This script allows a csv to be retrieved into an open-office spreadsheet directly with the loadComponentFromURL statement. Also modified session.inc - hope I've not broken anything else!!
<p>14/11/09 Tim: StockLocStatus.php - Correct missing </a> tag.
<p>12/11/09 Tim: Improve direct ordering code and make the EOQ the default quantity when ordered direct
<p>12/11/09 Tim: PO_PDFPurchOrder.php - Correct syntax error
Modified: trunk/includes/session.inc
===================================================================
--- trunk/includes/session.inc 2009-11-14 17:32:19 UTC (rev 3000)
+++ trunk/includes/session.inc 2009-11-15 06:30:58 UTC (rev 3001)
@@ -155,8 +155,7 @@
$i++;
}
}
-
-
+
echo '<meta http-equiv="refresh" content="0" url="' . $_SERVER['PHP_SELF'] . '?' . SID . '">';
exit;
} else { // Incorrect password
Modified: trunk/index.php
===================================================================
--- trunk/index.php 2009-11-14 17:32:19 UTC (rev 3000)
+++ trunk/index.php 2009-11-15 06:30:58 UTC (rev 3001)
@@ -2,7 +2,6 @@
/* $Revision: 1.89 $ */
-
$PageSecurity = 1;
include('includes/session.inc');
@@ -157,9 +156,13 @@
<?php echo "<a href='" . $rootpath . '/PDFOrdersInvoiced.php?' . SID . "'><LI>" . _('Orders Invoiced Reports') . '</LI></a>'; ?>
</td>
</tr>
-
<tr>
<td class="menu_group_item">
+ <?php echo "<a href='" . $rootpath . '/DailySalesInquiry.php?' . SID . "'><LI>" . _('Daily Sales Inquiry') . '</LI></a>'; ?>
+ </td>
+ </tr>
+ <tr>
+ <td class="menu_group_item">
<?php echo "<a href='" . $rootpath . '/PDFDeliveryDifferences.php?' . SID . "'><LI>" . _('Order Delivery Differences Report') . '</LI></a>'; ?>
</td>
</tr>
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
