Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[6998] trunk
|
From: <dai...@us...> - 2014-11-22 02:28:59
|
Revision: 6998
http://sourceforge.net/p/web-erp/reponame/6998
Author: daintree
Date: 2014-11-22 02:28:56 +0000 (Sat, 22 Nov 2014)
Log Message:
-----------
Couple of new scripts/fixes
Modified Paths:
--------------
trunk/SalesTypes.php
trunk/Z_DeleteOldPrices.php
trunk/doc/Change.log
trunk/includes/MainMenuLinksArray.php
trunk/sql/mysql/upgrade4.11-4.12.sql
Added Paths:
-----------
trunk/CustomerBalancesMovement.php
trunk/Z_AutoCustomerAllocations.php
trunk/Z_RebuildSalesAnalysis.php
trunk/Z_UpdateItemCosts.php
Added: trunk/CustomerBalancesMovement.php
===================================================================
--- trunk/CustomerBalancesMovement.php (rev 0)
+++ trunk/CustomerBalancesMovement.php 2014-11-22 02:28:56 UTC (rev 6998)
@@ -0,0 +1,179 @@
+<?php
+/* $Id: Z_CustomerBalancesMovements.php 6941 2014-10-26 23:18:08Z daintree $*/
+$PageSecurity = 1;
+include('includes/session.inc');
+$Title=_('Customer Balances Movements');
+include('includes/header.inc');
+
+echo '<p class="page_title_text">
+ <img src="'.$RootPath.'/css/'.$Theme.'/images/transactions.png" title="' . _('Customer Balances Movements') . '" alt="" />' . ' ' . _('Customer Balances Movements') . '
+ </p>';
+
+if (!isset($_POST['RunReport'])){
+
+ $SalesAreasResult = DB_query("SELECT areacode, areadescription FROM areas");
+ $CustomersResult = DB_query("SELECT debtorno, name FROM debtorsmaster ORDER BY name");
+
+ echo '<form id="Form1" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">
+
+ <div>
+ <input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
+
+ <table cellpadding="2" class="selection">
+ <tr>
+ <td>' . _('Customer') . '</td>
+ <td><select name="Customer">
+ <option selected="selected" value="">' . _('All') . '</option>';
+ while ($CustomerRow = DB_fetch_array($CustomersResult)) {
+ echo '<option value="' . $CustomerRow['debtorno'] . '">' . $CustomerRow['name'] . '</option>';
+ }
+ echo '</select>
+ </td>
+ </tr>
+ <tr>
+ <td>' . _('Sales Area') . '</td>
+ <td><select name="SalesArea">
+ <option selected="selected" value="">' . _('All') . '</option>';
+ while ($AreaRow = DB_fetch_array($SalesAreasResult)) {
+ echo '<option value="' . $AreaRow['areacode'] . '">' . $AreaRow['areadescription'] . '</option>';
+ }
+ echo '</select>
+ </td>
+ </tr>
+ <tr>
+ <td>' . _('Date From') . ':</td>
+ <td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="FromDate" maxlength="10" size="11" value="' . Date($_SESSION['DefaultDateFormat'], Mktime(0, 0, 0, Date('m') - $_SESSION['NumberOfMonthMustBeShown'], Date('d'), Date('Y'))) . '" /></td>
+ </tr>
+ <tr>
+ <td>' . _('Date To') . ':</td>
+ <td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="ToDate" maxlength="10" size="11" value="' . Date($_SESSION['DefaultDateFormat']) . '" /></td>
+ </tr>
+
+ </table>
+ <br />
+ <div class="centre">
+ <input tabindex="4" type="submit" name="RunReport" value="' . _('Show Customer Balance Movements') . '" />
+ </div>
+ </div>
+ </form>
+ <br />';
+ include('includes/footer.inc');
+ exit;
+}
+
+if ($_POST['Customer']!='') {
+ $WhereClause = "debtorsmaster.debtorno='" . $_POST['Customer'] . "'";
+} elseif ($_POST['SalesArea']!='') {
+ $WhereClause = "custbranch.area='" . $_POST['SalesArea'] . "'";
+} else {
+ $WhereClause ='';
+}
+
+$sql = "SELECT SUM(ovamount+ovgst+ovdiscount+ovfreight-alloc) AS currencybalance,
+ debtorsmaster.debtorno,
+ debtorsmaster.name,
+ decimalplaces AS currdecimalplaces,
+ SUM((ovamount+ovgst+ovdiscount+ovfreight-alloc)/debtortrans.rate) AS localbalance
+ FROM debtortrans INNER JOIN debtorsmaster
+ ON debtortrans.debtorno=debtorsmaster.debtorno
+ INNER JOIN currencies
+ ON debtorsmaster.currcode=currencies.currabrev
+ INNER JOIN custbranch
+ ON debtorsmaster.debtorno=custbranch.debtorno";
+
+if (mb_strlen($WhereClause)>0){
+ $sql .= " WHERE " . $WhereClause . " ";
+}
+$sql .= " GROUP BY debtorsmaster.debtorno";
+
+$result = DB_query($sql);
+
+$LocalTotal =0;
+
+echo '<table>
+ <tr>
+ <th class="ascending">' . _('Customer') . ' </th>
+ <th class="ascending">' . _('Opening Balance') . '</th>
+ <th class="ascending">' . _('Debits') . '</th>
+ <th class="ascending">' . _('Credits') . '</th>
+ <th class="ascending">' . _('Balance') . '</th>
+ </tr>';
+
+
+$OpeningBalances =0;
+$Debits =0;
+$Credits =0;
+$ClosingBalances =0;
+
+while ($myrow=DB_fetch_array($result)){
+
+/*Get the sum of all transactions after the ending date -
+ * we need to take off the sum of all movements after the ending date from the current balance calculated above
+ * to get the balance as at the end of the period
+ */
+ $sql = "SELECT SUM(ovamount+ovgst+ovdiscount+ovfreight) AS currencytotalpost,
+ debtorsmaster.debtorno,
+ SUM((ovamount+ovgst+ovdiscount+ovfreight)/debtortrans.rate) AS localtotalpost
+ FROM debtortrans INNER JOIN debtorsmaster
+ ON debtortrans.debtorno=debtorsmaster.debtorno
+ WHERE trandate > '" . FormatDateForSQL($_POST['ToDate']) . "'
+ AND debtorsmaster.debtorno = '" . $myrow['debtorno'] . "'
+ GROUP BY debtorsmaster.debtorno";
+
+ $TransPostResult = DB_query($sql);
+ $TransPostRow = DB_fetch_array($TransPostResult);
+/* Now we need to get the debits and credits during the period under review
+ */
+ $sql = "SELECT SUM(CASE WHEN debtortrans.type=10 THEN ovamount+ovgst+ovdiscount+ovfreight ELSE 0 END) AS currencydebits,
+ SUM(CASE WHEN debtortrans.type<>10 THEN ovamount+ovgst+ovdiscount+ovfreight ELSE 0 END) AS currencycredits,
+ debtorsmaster.debtorno,
+ SUM(CASE WHEN debtortrans.type=10 THEN (ovamount+ovgst+ovdiscount+ovfreight-alloc)/debtortrans.rate ELSE 0 END) AS localdebits,
+ SUM(CASE WHEN debtortrans.type<>10 THEN (ovamount+ovgst+ovdiscount+ovfreight-alloc)/debtortrans.rate ELSE 0 END) AS localcredits
+ FROM debtortrans INNER JOIN debtorsmaster
+ ON debtortrans.debtorno=debtorsmaster.debtorno
+ WHERE trandate>='" . FormatDateForSQL($_POST['FromDate']) . "' AND trandate <= '" . FormatDateForSQL($_POST['ToDate']) . "'
+ AND debtorsmaster.debtorno = '" . $myrow['debtorno'] . "'
+ GROUP BY debtorsmaster.debtorno";
+
+ $TransResult = DB_query($sql);
+ $TransRow = DB_fetch_array($TransResult);
+
+ $OpeningBal = $myrow['localbalance']-$TransPostRow['localtotalpost']-$TransRow['localdebits']+$TransRow['localcredits'];
+ $ClosingBal = $myrow['localbalance']-$TransPostRow['localtotalpost'];
+
+ echo '<tr>
+ <td>' . $myrow['name'] . ' </td>
+ <td class="number">' . locale_number_format($OpeningBal,$_SESSION['CompanyRecord']['decimalplaces']) . '</td>
+ <td class="number">' . locale_number_format($TransRow['localdebits'],$_SESSION['CompanyRecord']['decimalplaces']) . '</td>
+ <td class="number">' . locale_number_format($TransRow['localcredits'],$_SESSION['CompanyRecord']['decimalplaces']) . '</td>
+ <td class="number">' . locale_number_format($ClosingBal,$_SESSION['CompanyRecord']['decimalplaces']) . '</td>
+ </tr>';
+ $OpeningBalances += $OpeningBal;
+ $Debits += $TransRow['localdebits'];
+ $Credits += $TransRow['localcredits'];
+ $ClosingBalances += $ClosingBal;
+}
+
+echo '</table>';
+
+if ($_POST['Customer']==''){ //if there could be several customers being reported
+ echo '<table>
+ <tr>
+ <th></th>
+ <th>' . _('Opening Balance') . '</th>
+ <th>' . _('Debits') . '</th>
+ <th>' . _('Credits') . '</th>
+ <th>' . _('Balance') . '</th>
+ </tr>
+ <tr>
+ <td>' . _('TOTALS') . '</td>
+ <td class="number">' . locale_number_format($OpeningBalances,$_SESSION['CompanyRecord']['decimalplaces']) . '</td>
+ <td class="number">' . locale_number_format($Debits,$_SESSION['CompanyRecord']['decimalplaces']) . '</td>
+ <td class="number">' . locale_number_format($Credits,$_SESSION['CompanyRecord']['decimalplaces']) . '</td>
+ <td class="number">' . locale_number_format($ClosingBalances,$_SESSION['CompanyRecord']['decimalplaces']) . '</td>
+ </tr>
+ </table>';
+}
+
+include('includes/footer.inc');
+?>
\ No newline at end of file
Modified: trunk/SalesTypes.php
===================================================================
--- trunk/SalesTypes.php 2014-11-20 12:37:23 UTC (rev 6997)
+++ trunk/SalesTypes.php 2014-11-22 02:28:56 UTC (rev 6998)
@@ -184,10 +184,10 @@
$sql = "SELECT typeabbrev,sales_type FROM salestypes ORDER BY typeabbrev";
$result = DB_query($sql);
- echo '<table class="selection">';
- echo '<tr>
- <th class="ascending">' . _('Type Code') . '</th>
- <th class="ascending">' . _('Type Name') . '</th>
+ echo '<table class="selection">
+ <tr>
+ <th class="ascending">' . _('Type Code') . '</th>
+ <th class="ascending">' . _('Type Name') . '</th>
</tr>';
$k=0; //row colour counter
@@ -218,19 +218,21 @@
//end of ifs and buts!
if (isset($SelectedType)) {
- echo '<br /><div class="centre"><a href="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') .'">' . _('Show All Sales Types Defined') . '</a></div>';
+ echo '<br />
+ <div class="centre">
+ <a href="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') .'">' . _('Show All Sales Types Defined') . '</a>
+ </div>';
}
if (! isset($_GET['delete'])) {
- echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" >';
- echo '<div>';
- echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
- echo '<br />';
+ echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" >
+ <div>
+ <input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
+ <br />';
// The user wish to EDIT an existing type
- if ( isset($SelectedType) AND $SelectedType!='' )
- {
+ if ( isset($SelectedType) AND $SelectedType!='' ) {
$sql = "SELECT typeabbrev,
sales_type
@@ -243,40 +245,45 @@
$_POST['TypeAbbrev'] = $myrow['typeabbrev'];
$_POST['Sales_Type'] = $myrow['sales_type'];
- echo '<input type="hidden" name="SelectedType" value="' . $SelectedType . '" />';
- echo '<input type="hidden" name="TypeAbbrev" value="' . $_POST['TypeAbbrev'] . '" />';
- echo '<table class="selection">';
- echo '<tr><th colspan="4"><b>' . _('Sales Type/Price List Setup') . '</b></th></tr>';
- echo '<tr><td>' . _('Type Code') . ':</td><td>';
+ echo '<input type="hidden" name="SelectedType" value="' . $SelectedType . '" />
+ <input type="hidden" name="TypeAbbrev" value="' . $_POST['TypeAbbrev'] . '" />
+ <table class="selection">
+ <tr>
+ <th colspan="4"><b>' . _('Sales Type/Price List Setup') . '</b></th>
+ </tr>
+ <tr>
+ <td>' . _('Type Code') . ':</td>
+ <td>' . $_POST['TypeAbbrev'] . '</td>
+ </tr>';
- // We dont allow the user to change an existing type code
-
- echo $_POST['TypeAbbrev'] . '</td></tr>';
-
} else {
// This is a new type so the user may volunteer a type code
- echo '<table class="selection">';
- echo '<tr><th colspan="4"><b>' . _('Sales Type/Price List Setup') . '</b></th></tr>';
- echo '<tr><td>' . _('Type Code') . ':</td>
- <td><input type="text" ' . (in_array('SalesType',$Errors) ? 'class="inputerror"' : '' ) .' size="3" maxlength="2" name="TypeAbbrev" /></td></tr>';
+ echo '<table class="selection">
+ <tr>
+ <th colspan="4"><b>' . _('Sales Type/Price List Setup') . '</b></th>
+ </tr>
+ <tr>
+ <td>' . _('Type Code') . ':</td>
+ <td><input type="text" ' . (in_array('SalesType',$Errors) ? 'class="inputerror"' : '' ) .' size="3" maxlength="2" name="TypeAbbrev" /></td>
+ </tr>';
}
if (!isset($_POST['Sales_Type'])) {
$_POST['Sales_Type']='';
}
- echo '<tr><td>' . _('Sales Type Name') . ':</td>
- <td><input type="text" name="Sales_Type" value="' . $_POST['Sales_Type'] . '" /></td></tr>';
+ echo '<tr>
+ <td>' . _('Sales Type Name') . ':</td>
+ <td><input type="text" name="Sales_Type" value="' . $_POST['Sales_Type'] . '" /></td>
+ </tr>
+ </table>'; // close main table
- echo '</table>'; // close main table
-
- echo '<br /><div class="centre"><input type="submit" name="submit" value="' . _('Accept') . '" /><input type="submit" name="Cancel" value="' . _('Cancel') . '" /></div>';
-
- echo '</div>
+ echo '<br /><div class="centre"><input type="submit" name="submit" value="' . _('Accept') . '" /><input type="submit" name="Cancel" value="' . _('Cancel') . '" /></div>
+ </div>
</form>';
} // end if user wish to delete
include('includes/footer.inc');
-?>
+?>
\ No newline at end of file
Added: trunk/Z_AutoCustomerAllocations.php
===================================================================
--- trunk/Z_AutoCustomerAllocations.php (rev 0)
+++ trunk/Z_AutoCustomerAllocations.php 2014-11-22 02:28:56 UTC (rev 6998)
@@ -0,0 +1,251 @@
+<?php
+
+/* $Id: CustomerAllocations.php 6596 2014-03-03 02:32:31Z exsonqu $*/
+
+/*
+Call this page with:
+ 1. A DebtorNo to show all outstanding receipts or credits yet to be allocated.
+*/
+
+include('includes/DefineCustAllocsClass.php');
+include('includes/session.inc');
+$Title = _('Automatic Customer Receipt') . '/' . _('Credit Note Allocations');
+
+$ViewTopic= 'ARTransactions';
+$BookMark = 'CustomerAllocations';
+
+include('includes/header.inc');
+include('includes/SQL_CommonFunctions.inc');
+
+if (isset($_GET['DebtorNo'])) {
+ // Page called with customer code
+
+ $SQL = "SELECT debtortrans.id,
+ debtortrans.transno,
+ systypes.typename,
+ debtortrans.type,
+ debtortrans.debtorno,
+ debtorsmaster.name,
+ debtortrans.trandate,
+ debtortrans.reference,
+ debtortrans.rate,
+ debtortrans.ovamount+debtortrans.ovgst+debtortrans.ovdiscount+debtortrans.ovfreight as total,
+ debtortrans.alloc,
+ currencies.decimalplaces AS currdecimalplaces,
+ debtorsmaster.currcode
+ FROM debtortrans INNER JOIN debtorsmaster
+ ON debtortrans.debtorno=debtorsmaster.debtorno
+ INNER JOIN systypes
+ ON debtortrans.type=systypes.typeid
+ INNER JOIN currencies
+ ON debtorsmaster.currcode=currencies.currabrev
+ WHERE debtortrans.debtorno='" . $_GET['DebtorNo'] . "'
+ AND ( (debtortrans.type=12 AND debtortrans.ovamount<0) OR debtortrans.type=11)
+ AND debtortrans.settled=0
+ ORDER BY debtortrans.id";
+
+ $result = DB_query($SQL);
+
+ if (DB_num_rows($result)==0) {
+ prnMsg(_('No outstanding receipts or credits to be allocated for this customer'),'info');
+ include('includes/footer.inc');
+ exit;
+ }
+ echo '<table class="selection">';
+ echo $TableHeader;
+
+ while ($myrow = DB_fetch_array($result)) {
+ unset($_SESSION['Alloc']->Allocs);
+ unset($_SESSION['Alloc']);
+ $_SESSION['Alloc'] = new Allocation;
+ $_SESSION['Alloc']->AllocTrans = $myrow['id'];
+ $_SESSION['Alloc']->DebtorNo = $myrow['debtorno'];
+ $_SESSION['Alloc']->CustomerName = $myrow['name'];
+ $_SESSION['Alloc']->TransType = $myrow['type'];
+ $_SESSION['Alloc']->TransTypeName = $myrow['typename'];
+ $_SESSION['Alloc']->TransNo = $myrow['transno'];
+ $_SESSION['Alloc']->TransExRate = $myrow['rate'];
+ $_SESSION['Alloc']->TransAmt = $myrow['total'];
+ $_SESSION['Alloc']->PrevDiffOnExch = $myrow['diffonexch'];
+ $_SESSION['Alloc']->TransDate = ConvertSQLDate($myrow['trandate']);
+ $_SESSION['Alloc']->CurrDecimalPlaces = $myrow['decimalplaces'];
+
+ // Now get invoices or neg receipts that have outstanding balances
+ $SQL = "SELECT debtortrans.id,
+ typename,
+ transno,
+ trandate,
+ rate,
+ ovamount+ovgst+ovfreight+ovdiscount as total,
+ diffonexch,
+ alloc
+ FROM debtortrans INNER JOIN systypes
+ ON debtortrans.type = systypes.typeid
+ WHERE debtortrans.settled=0
+ AND (systypes.typeid=10 OR (systypes.typeid=12 AND ovamount>0)
+ AND debtorno='" . $_SESSION['Alloc']->DebtorNo . "'
+ ORDER BY debtortransid DESC";
+ $TransResult = DB_query($SQL);
+ $BalToAllocate = $_SESSION['Alloc']->TransAmt - $myrow['alloc'];
+ while ($myalloc=DB_fetch_array($TransResult) AND $BalToAllocate > 0) {
+ if ($myalloc['total']-$myalloc['alloc']< $BalToAllocate) {
+ $ThisAllocation = $myalloc['total']-$myalloc['alloc'];
+ } else {
+ $ThisAllocation = $BalToAllocate;
+ }
+ $_SESSION['Alloc']->add_to_AllocsAllocn ($myalloc['id'],
+ $myalloc['typename'],
+ $myalloc['transno'],
+ ConvertSQLDate($myalloc['trandate']),
+ $ThisAllocation,
+ $myalloc['total'],
+ $myalloc['rate'],
+ $myalloc['diffonexch'],
+ $myalloc['diffonexch'],
+ $myalloc['alloc'],
+ 'NA');
+ $BalToAllocate -= $ThisAllocation;
+ }
+ DB_free_result($TransResult);
+ ProcessAllocation();
+ }
+ echo '</table>';
+}
+
+include('includes/footer.inc');
+
+function ProcessAllocation() {
+ global $db;
+ if ($InputError==0) {
+ //
+ //========[ START TRANSACTION ]===========
+ //
+ $Error = '';
+ $Result= DB_Txn_Begin();
+ $AllAllocations = 0;
+ foreach ($_SESSION['Alloc']->Allocs as $AllocnItem) {
+
+ if ($AllocnItem->AllocAmt > 0) {
+ $SQL = "INSERT INTO
+ custallocns (
+ datealloc,
+ amt,
+ transid_allocfrom,
+ transid_allocto
+ ) VALUES (
+ '" . date('Y-m-d') . "',
+ '" . $AllocnItem->AllocAmt . "',
+ '" . $_SESSION['Alloc']->AllocTrans . "',
+ '" . $AllocnItem->ID . "'
+ )";
+ if( !$Result = DB_query($SQL) ) {
+ $Error = _('Could not insert allocation record');
+ }
+ }
+ $NewAllocTotal = $AllocnItem->PrevAlloc + $AllocnItem->AllocAmt;
+ $AllAllocations = $AllAllocations + $AllocnItem->AllocAmt;
+ $Settled = (abs($NewAllocTotal-$AllocnItem->TransAmount) < 0.005) ? 1 : 0;
+
+ $SQL = "UPDATE debtortrans
+ SET diffonexch='" . $AllocnItem->DiffOnExch . "',
+ alloc = '" . $NewAllocTotal . "',
+ settled = '" . $Settled . "'
+ WHERE id = '" . $AllocnItem->ID."'";
+ if( !$Result = DB_query($SQL) ) {
+ $Error = _('Could not update difference on exchange');
+ }
+ }
+ if (abs($TotalAllocated + $_SESSION['Alloc']->TransAmt) < 0.01) {
+ $Settled = 1;
+ } else {
+ $Settled = 0;
+ }
+ // Update the receipt or credit note
+ $SQL = "UPDATE debtortrans
+ SET alloc = '" . -$AllAllocations . "',
+ diffonexch = '" . -$TotalDiffOnExch . "',
+ settled='" . $Settled . "'
+ WHERE id = '" . $_SESSION['Alloc']->AllocTrans . "'";
+
+ if( !$Result = DB_query($SQL) ) {
+ $Error = _('Could not update receipt or credit note');
+ }
+
+ // If GLLink to debtors active post diff on exchange to GL
+ $MovtInDiffOnExch = -$_SESSION['Alloc']->PrevDiffOnExch - $TotalDiffOnExch;
+
+ if ($MovtInDiffOnExch !=0) {
+ if ($_SESSION['CompanyRecord']['gllink_debtors'] == 1) {
+ $PeriodNo = GetPeriod($_SESSION['Alloc']->TransDate,);
+ $_SESSION['Alloc']->TransDate = FormatDateForSQL($_SESSION['Alloc']->TransDate);
+
+ $SQL = "INSERT INTO gltrans (
+ type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount
+ ) VALUES (
+ '" . $_SESSION['Alloc']->TransType . "',
+ '" . $_SESSION['Alloc']->TransNo . "',
+ '" . $_SESSION['Alloc']->TransDate . "',
+ '" . $PeriodNo . "',
+ '" . $_SESSION['CompanyRecord']['exchangediffact'] . "',
+ '',
+ '" . $MovtInDiffOnExch . "'
+ )";
+ if( !$Result = DB_query($SQL) ) {
+ $Error = _('Could not update exchange difference in General Ledger');
+ }
+
+ $SQL = "INSERT INTO gltrans (
+ type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount
+ ) VALUES ('" . $_SESSION['Alloc']->TransType . "',
+ '" . $_SESSION['Alloc']->TransNo . "',
+ '" . $_SESSION['Alloc']->TransDate . "',
+ '" . $PeriodNo . "',
+ '" . $_SESSION['CompanyRecord']['debtorsact'] . "',
+ '',
+ '" . -$MovtInDiffOnExch . "')";
+
+ if( !$Result = DB_query($SQL) ) {
+ $Error = _('Could not update debtors control in General Ledger');
+ }
+ }
+
+ }
+
+ //
+ //========[ COMMIT TRANSACTION ]===========
+ //
+ if (empty($Error) ) {
+ $Result = DB_Txn_Commit();
+ } else {
+ $Result = DB_Txn_Rollback();
+ prnMsg($Error,'error');
+ }
+ unset($_SESSION['Alloc']);
+ unset($_POST['AllocTrans']);
+ }
+}
+
+?>
+ocTrans']);
+ }
+}
+
+?>
+
+}
+
+?>
+
+?>
Property changes on: trunk/Z_AutoCustomerAllocations.php
___________________________________________________________________
Added: svn:executable
## -0,0 +1 ##
+*
\ No newline at end of property
Modified: trunk/Z_DeleteOldPrices.php
===================================================================
--- trunk/Z_DeleteOldPrices.php 2014-11-20 12:37:23 UTC (rev 6997)
+++ trunk/Z_DeleteOldPrices.php 2014-11-22 02:28:56 UTC (rev 6998)
@@ -6,18 +6,42 @@
include('includes/header.inc');
if (isset($_POST['DeleteOldPrices'])){
- $result=DB_query("DELETE FROM prices WHERE enddate<'" . Date('Y-m-d') . "' AND enddate <>'0000-00-00'");
+ DB_Txn_Begin();
+ $result=DB_query("DELETE FROM prices WHERE enddate<'" . Date('Y-m-d') . "' AND enddate <>'0000-00-00'",'','',true);
+ $result=DB_query("SELECT stockid,
+ typeabbrev,
+ currabrev,
+ debtorno,
+ branchcode,
+ MAX(startdate) as lateststart
+ FROM prices
+ WHERE startdate<='" . Date('Y-m-d') . "'
+ AND enddate ='0000-00-00'
+ GROUP BY stockid,
+ typeabbrev,
+ currabrev,
+ debtorno,
+ branchcode");
+
+ while ($myrow = DB_fetch_array($result)){
+ $DelResult = DB_query("DELETE FROM prices WHERE stockid='" . $myrow['stockid'] . "'
+ AND debtorno='" . $myrow['debtorno'] . "'
+ AND branchcode='" . $myrow['branchcode'] . "'
+ AND currabrev='" . $myrow['currabrev'] . "'
+ AND typeabbrev='" . $myrow['typeabbrev'] . "'
+ AND enddate='0000-00-00'
+ AND startdate<'" . $myrow['lateststart'] . "'",'','',true);
+ }
prnMsg(_('All old prices have been deleted'),'success');
+ DB_Txn_Commit();
}
-echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">';
-echo '<div class="centre">';
-echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
-
-echo '<br />
- <input type="submit" name="DeleteOldPrices" value="' . _('Purge Old Prices') . '" onclick="return confirm(\'' . _('Are You Sure you wish to delete all old prices?') . '\');" />';
-
-echo '</div>
+echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">
+ <div class="centre">
+ <input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
+ <br />
+ <input type="submit" name="DeleteOldPrices" value="' . _('Purge Old Prices') . '" onclick="return confirm(\'' . _('Are You Sure you wish to delete all old prices?') . '\');" />
+ </div>
</form>';
include('includes/footer.inc');
Added: trunk/Z_RebuildSalesAnalysis.php
===================================================================
--- trunk/Z_RebuildSalesAnalysis.php (rev 0)
+++ trunk/Z_RebuildSalesAnalysis.php 2014-11-22 02:28:56 UTC (rev 6998)
@@ -0,0 +1,69 @@
+<?php
+/* $Id: Z_RebuildSalesAnalysis.php 5784 2012-12-29 04:00:43Z daintree $*/
+/* Script to rebuild sales analysis records from stock movements*/
+$PageSecurity = 15;
+include ('includes/session.inc');
+$Title = _('Rebuild sales analysis Records');
+include('includes/header.inc');
+
+echo '<br /><br />' . _('This script rebuilds sales analysis records. NB: all sales budget figures will be lost!');
+
+$result = DB_query("TRUNCATE TABLE salesanalysis");
+
+$sql = "INSERT INTO salesanalysis (typeabbrev,
+ periodno,
+ amt,
+ cost,
+ cust,
+ custbranch,
+ qty,
+ disc,
+ stockid,
+ area,
+ budgetoractual,
+ salesperson,
+ stkcategory)
+ SELECT salestype,
+ (SELECT periodno FROM periods WHERE MONTH(lastdate_in_period)=MONTH(trandate) AND YEAR(lastdate_in_period)=YEAR(trandate)) as prd,
+ SUM(price*-qty) as salesvalue,
+ SUM(standardcost*-qty) as cost,
+ stockmoves.debtorno,
+ stockmoves.branchcode,
+ SUM(-qty),
+ SUM(-qty*price*discountpercent) AS discountvalue,
+ stockmoves.stockid,
+ custbranch.area,
+ 1,
+ custbranch.salesman,
+ stockmaster.categoryid
+ FROM stockmoves
+ INNER JOIN debtorsmaster
+ ON stockmoves.debtorno=debtorsmaster.debtorno
+ INNER JOIN custbranch
+ ON stockmoves.debtorno=custbranch.debtorno
+ AND stockmoves.branchcode=custbranch.branchcode
+ INNER JOIN stockmaster
+ ON stockmoves.stockid=stockmaster.stockid
+ WHERE show_on_inv_crds=1
+ GROUP BY salestype,
+ debtorno,
+ prd,
+ branchcode,
+ stockid,
+ area,
+ salesman,
+ categoryid
+ ORDER BY prd";
+
+$ErrMsg = _('The sales analysis data could not be recreated because');
+$Result = DB_query($sql,$ErrMsg);
+
+echo '<p />';
+prnMsg(_('The sales analsysis data has been recreated based on current stock master and customer master information'),'info');
+
+include('includes/footer.inc');
+?>
+
+?>
+?>
+
Property changes on: trunk/Z_RebuildSalesAnalysis.php
___________________________________________________________________
Added: svn:executable
## -0,0 +1 ##
+*
\ No newline at end of property
Added: trunk/Z_UpdateItemCosts.php
===================================================================
--- trunk/Z_UpdateItemCosts.php (rev 0)
+++ trunk/Z_UpdateItemCosts.php 2014-11-22 02:28:56 UTC (rev 6998)
@@ -0,0 +1,124 @@
+<?php
+include('includes/session.inc');
+$Title = _('Update Item Costs From CSV');
+include('includes/header.inc');
+include('includes/SQL_CommonFunctions.inc');
+echo '<p class="page_title_text"><img alt="" src="' . $RootPath . '/css/' . $Theme .
+ '/images/maintenance.png" title="' .
+ _('Update Item Costs from CSV file') . '" />' . ' ' .
+ _('Update Item Costs from CSV file') . '</p>';
+
+$FieldHeadings = array('StockID',
+ 'Material Cost',
+ 'Labour Cost',
+ 'Overhead Cost');
+
+if (isset($_FILES['CostUpdateFile']) and $_FILES['CostUpdateFile']['name']) { //start file processing
+ //check file info
+ $FileName = $_FILES['CostUpdateFile']['name'];
+ $TempName = $_FILES['CostUpdateFile']['tmp_name'];
+ $FileSize = $_FILES['CostUpdateFile']['size'];
+ $InputError = 0;
+
+ //get file handle
+ $FileHandle = fopen($TempName, 'r');
+
+ //get the header row
+ $HeadRow = fgetcsv($FileHandle, 10000, ',');
+
+ //check for correct number of fields
+ if ( count($HeadRow) != count($FieldHeadings) ) {
+ prnMsg (_('File contains') . ' '. count($HeadRow). ' ' . _('columns, expected') . ' '. count($FieldHeadings) ,'error');
+ fclose($FileHandle);
+ include('includes/footer.inc');
+ exit;
+ }
+
+ //test header row field name and sequence
+ $HeadingColumnNumber = 0;
+ foreach ($HeadRow as $HeadField) {
+ if ( trim(mb_strtoupper($HeadField)) != trim(mb_strtoupper($FieldHeadings[$HeadingColumnNumber]))) {
+ prnMsg (_('The file to import the item cost updates from contains incorrect column headings') . ' '. mb_strtoupper($HeadField). ' != '. mb_strtoupper($FieldHeadings[$HeadingColumnNumber]). '<br />' . _('The column headings must be') . ' StockID, Material Cost, Labour Cost, Overhead Cost','error');
+ fclose($FileHandle);
+ include('includes/footer.inc');
+ exit;
+ }
+ $HeadingColumnNumber++;
+ }
+ //start database transaction
+ DB_Txn_Begin();
+
+ //loop through file rows
+ $LineNumber = 1;
+ while ( ($myrow = fgetcsv($FileHandle, 10000, ',')) !== FALSE ) {
+
+ $StockID = mb_strtoupper($myrow[0]);
+
+ $NewCost = (double)$myrow[1]+(double)$myrow[2]+(double)$myrow[3];
+
+ $sql = "SELECT mbflag,
+ materialcost,
+ labourcost,
+ overheadcost,
+ sum(quantity) as totalqoh
+ FROM stockmaster INNER JOIN locstock
+ ON stockmaster.stockid=locstock.stockid
+ WHERE stockmaster.stockid='" . $StockID . "'
+ GROUP BY materialcost,
+ labourcost,
+ overheadcost";
+
+ $ErrMsg = _('The selected item code does not exist');
+ $OldResult = DB_query($sql,$ErrMsg);
+ $OldRow = DB_fetch_array($OldResult);
+ $QOH = $OldRow['totalqoh'];
+
+ $OldCost = $OldRow['materialcost'] + $OldRow['labourcost'] + $OldRow['overheadcost'];
+ //dont update costs for assembly or kit-sets or ghost items!!
+ if ($OldCost != $NewCost AND $OldRow['mbflag']!='K' AND $OldRow['mbflag']!='A' AND $OldRow['mbflag']!='G'){
+
+ ItemCostUpdateGL($db, $StockID, $NewCost, $OldCost, $QOH);
+
+ $SQL = "UPDATE stockmaster SET materialcost='" . (double) $myrow[1] . "',
+ labourcost='" . (double) $myrow[2] . "',
+ overheadcost='" . (double) $myrow[3] . "',
+ lastcost='" . $OldCost . "',
+ lastcostupdate ='" . Date('Y-m-d')."'
+ WHERE stockid='" . $StockID . "'";
+
+ $ErrMsg = _('The cost details for the stock item could not be updated because');
+ $DbgMsg = _('The SQL that failed was');
+ $Result = DB_query($SQL,$ErrMsg,$DbgMsg,true);
+
+ UpdateCost($db, $StockID); //Update any affected BOMs
+
+ }
+
+ $LineNumber++;
+ }
+
+ DB_Txn_Commit();
+ prnMsg( _('Batch Update of costs') .' ' . $FileName . ' '. _('has been completed. All transactions committed to the database.'),'success');
+
+ fclose($FileHandle);
+
+} else { //show file upload form
+
+ echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" enctype="multipart/form-data">';
+ echo '<div class="centre">';
+ echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
+ echo '<div class="page_help_text">' .
+ _('This function updates the costs of all items from a comma separated variable (csv) file.') . '<br />' .
+ _('The file must contain two columns, and the first row should be the following headers:') . '<br /><i>StockID, Material Cost, Labour Cost, Overhead Cost</i><br />' .
+ _('followed by rows containing these four fields for each cost to be updated.') . '<br />' .
+ _('The StockID field must have a corresponding entry in the stockmaster table.') . '</div>';
+
+ echo '<br /><input type="hidden" name="MAX_FILE_SIZE" value="1000000" />' ._('Upload file') . ': <input name="CostUpdateFile" type="file" />
+ <input type="submit" name="submit" value="' . _('Send File') . '" />
+ </div>
+ </form>';
+}
+
+include('includes/footer.inc');
+
+?>
\ No newline at end of file
Modified: trunk/doc/Change.log
===================================================================
--- trunk/doc/Change.log 2014-11-20 12:37:23 UTC (rev 6997)
+++ trunk/doc/Change.log 2014-11-22 02:28:56 UTC (rev 6998)
@@ -1,12 +1,16 @@
webERP Change Log
+
+22/11/14 Phil: Z_DeleteOldPrices.php now removes all old prices where there is a later start for a price with no end date
+22/11/14 Phil: Added new script CustomerBalancesMovement.php to show customer activity debits and credits and the movement of their balances over a specified date range
+22/11/14 Phil: Added new script Z_UpdateItemCosts.php that allows a csv import of items and costs and updates the standard cost based on imported data
20/11/14 Exson: Add GRN numbers to select grns screen in SuppInvGRNs.php.
-20/11/14 Exson: Fixed the pop up error when input account no in Payments.php by align the js function with applying it. The fixes are mismatched before. Hope it's the last time.
-17/11/14 Thumb: Move EnsureGLBalance() to the right place in ConfirmDispatch_Invoice.php to ensure that the whole transaction are checked instead of only one type of it checked.
+20/11/14 Exson: Fixed the pop up error when input account no in Payments.php by align the js function with applying it. The fixes are mismatched before. Hope it's the last time.
+17/11/14 Thumb: Move EnsureGLBalance() to the right place in ConfirmDispatch_Invoice.php to ensure that the whole transaction are checked instead of only one type of it checked.
17/11/14 Exson: Fixed InvoiceQuantityDefault parameters failed to save in SystemParameters.php. Reported by Richard.
15/11/14: Exson: Remove qtyrecd in work orders requirements calculation from group by in MRP.php.
15/11/14 Exson: Fixed the NULL bug for no issued materials for WO and make multiple times material issues correctly in MRP.php.
14/11/14 Exson: Fixed the foreign key constrained failure bug in Z_DeleteCreditNote.php.
-14/11/14 Exson: Fixed the bug in MRP.php that wo requirement not counting demand for work orders without issuing items. Thanks for Tim's reminder.
+14/11/14 Exson: Fixed the bug in MRP.php that wo requirement not counting demand for work orders without issuing items. Thanks for Tim's reminder.
14/11/2014 Tim: The systypes should be 28 instead of 38 for work order issued in MRP.php.
13/11/Exson: Fixed bugs that issued materials not be calculated in demand for work orders and over received are ignored in level netting and negative inventory are not considered in REORDER level management in MRP.php which leads to wrong MRP results.
9/11/14: Tim: fix to javascript function to sort numbers including formatted numbers with commas.
Modified: trunk/includes/MainMenuLinksArray.php
===================================================================
--- trunk/includes/MainMenuLinksArray.php 2014-11-20 12:37:23 UTC (rev 6997)
+++ trunk/includes/MainMenuLinksArray.php 2014-11-22 02:28:56 UTC (rev 6998)
@@ -110,7 +110,8 @@
_('Customer Listing By Area/Salesperson'),
_('Sales Graphs'),
_('List Daily Transactions'),
- _('Customer Transaction Inquiries') );
+ _('Customer Transaction Inquiries'),
+ _('Customer Activity and Balances'));
if ($_SESSION['InvoicePortraitFormat']==0){
$PrintInvoicesOrCreditNotesScript = '/PrintCustTrans.php';
@@ -128,7 +129,8 @@
'/PDFCustomerList.php',
'/SalesGraph.php',
'/PDFCustTransListing.php',
- '/CustomerTransInquiry.php');
+ '/CustomerTransInquiry.php',
+ '/CustomerBalancesMovement.php' );
$MenuItems['AR']['Maintenance']['Caption'] = array( _('Add Customer'),
_('Select Customer'));
Modified: trunk/sql/mysql/upgrade4.11-4.12.sql
===================================================================
--- trunk/sql/mysql/upgrade4.11-4.12.sql 2014-11-20 12:37:23 UTC (rev 6997)
+++ trunk/sql/mysql/upgrade4.11-4.12.sql 2014-11-22 02:28:56 UTC (rev 6998)
@@ -1,6 +1,7 @@
INSERT INTO `config` VALUES ('InvoiceQuantityDefault','1');
ALTER TABLE `www_users` ADD `dashboard` TINYINT NOT NULL DEFAULT '0';
-
+INSERT INTO `scripts` (`script` ,`pagesecurity` ,`description` ) VALUES ('Z_UpdateItemCosts.php', '15', 'Use CSV of item codes and costs to update webERP item costs');
+INSERT INTO scripts (`script` ,`pagesecurity` ,`description` ) VALUES ('CustomerBalancesMovement.php', '3', 'Allow customers to be listed in local currency with balances and activity over a date range');
INSERT INTO `scripts` VALUES ('UserLocations.php', '15', 'Location User Maintenance');
-UPDATE config SET confvalue='4.12' WHERE confname='VersionNumber';
\ No newline at end of file
+UPDATE config SET confvalue='4.12' WHERE confname='VersionNumber';
|
