Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[6886] trunk
|
From: <dai...@us...> - 2014-09-14 02:52:04
|
Revision: 6886
http://sourceforge.net/p/web-erp/reponame/6886
Author: daintree
Date: 2014-09-14 02:51:55 +0000 (Sun, 14 Sep 2014)
Log Message:
-----------
Import Bank transactions modifications for SCB MT940 imports
Modified Paths:
--------------
trunk/BankAccounts.php
trunk/ImportBankTrans.php
trunk/ImportBankTransAnalysis.php
trunk/doc/Change.log
trunk/doc/Manual/ManualTax.html
trunk/sql/mysql/upgrade4.11-4.12.sql
Added Paths:
-----------
trunk/includes/ImportBankTrans_MT940_ING.php
trunk/includes/ImportBankTrans_MT940_SCB.php
Modified: trunk/BankAccounts.php
===================================================================
--- trunk/BankAccounts.php 2014-09-14 00:39:55 UTC (rev 6885)
+++ trunk/BankAccounts.php 2014-09-14 02:51:55 UTC (rev 6886)
@@ -88,7 +88,8 @@
bankaccountcode='" . $_POST['BankAccountCode'] . "',
bankaccountnumber='" . $_POST['BankAccountNumber'] . "',
bankaddress='" . $_POST['BankAddress'] . "',
- invoice ='" . $_POST['DefAccount'] . "'
+ invoice ='" . $_POST['DefAccount'] . "',
+ importformat='" . $_POST['ImportFormat'] . "'
WHERE accountcode = '" . $SelectedBankAccount . "'";
prnMsg(_('Note that it is not possible to change the currency of the account once there are transactions against it'),'warn');
echo '<br />';
@@ -98,7 +99,8 @@
bankaccountnumber='" . $_POST['BankAccountNumber'] . "',
bankaddress='" . $_POST['BankAddress'] . "',
currcode ='" . $_POST['CurrCode'] . "',
- invoice ='" . $_POST['DefAccount'] . "'
+ invoice ='" . $_POST['DefAccount'] . "',
+ importformat='" . $_POST['ImportFormat'] . "'
WHERE accountcode = '" . $SelectedBankAccount . "'";
}
@@ -113,14 +115,16 @@
bankaccountnumber,
bankaddress,
currcode,
- invoice
+ invoice,
+ importformat
) VALUES ('" . $_POST['AccountCode'] . "',
'" . $_POST['BankAccountName'] . "',
'" . $_POST['BankAccountCode'] . "',
'" . $_POST['BankAccountNumber'] . "',
'" . $_POST['BankAddress'] . "',
'" . $_POST['CurrCode'] . "',
- '" . $_POST['DefAccount'] . "' )";
+ '" . $_POST['DefAccount'] . "',
+ '" . $_POST['ImportFormat'] . "' )";
$msg = _('The new bank account has been entered');
}
@@ -178,7 +182,8 @@
bankaccountnumber,
bankaddress,
currcode,
- invoice
+ invoice,
+ importformat
FROM bankaccounts INNER JOIN chartmaster
ON bankaccounts.accountcode = chartmaster.accountcode";
@@ -193,6 +198,7 @@
<th>' . _('Bank Account Code') . '</th>
<th>' . _('Bank Account Number') . '</th>
<th>' . _('Bank Address') . '</th>
+ <th>' . _('Import Format') . '</th>
<th>' . _('Currency') . '</th>
<th>' . _('Default for Invoices') . '</th>
</tr>';
@@ -213,6 +219,17 @@
} elseif ($myrow['invoice']==2) {
$DefaultBankAccount=_('Currency Default');
}
+ switch ($myrow['importformat']) {
+ case 'MT940-ING':
+ $ImportFormat = 'ING MT940';
+ break;
+ case 'MT940-SCB':
+ $ImportFormat = 'SCB MT940';
+ break;
+ default:
+ $ImportFormat ='';
+ }
+
printf('<td>%s<br />%s</td>
<td>%s</td>
<td>%s</td>
@@ -220,6 +237,7 @@
<td>%s</td>
<td>%s</td>
<td>%s</td>
+ <td>%s</td>
<td><a href="%s?SelectedBankAccount=%s">' . _('Edit') . '</a></td>
<td><a href="%s?SelectedBankAccount=%s&delete=1" onclick="return confirm(\'' . _('Are you sure you wish to delete this bank account?') . '\');">' . _('Delete') . '</a></td>
</tr>',
@@ -229,6 +247,7 @@
$myrow['bankaccountcode'],
$myrow['bankaccountnumber'],
$myrow['bankaddress'],
+ $ImportFormat,
$myrow['currcode'],
$DefaultBankAccount,
htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8'),
@@ -323,7 +342,9 @@
if (!isset($_POST['BankAddress'])) {
$_POST['BankAddress']='';
}
-
+if (!isset($_POST['ImportFormat'])) {
+ $_POST['ImportFormat']='';
+}
echo '<tr>
<td>' . _('Bank Account Name') . ': </td>
<td><input tabindex="2" ' . (in_array('AccountName',$Errors) ? 'class="inputerror"' : '' ) .' type="text" required="required" name="BankAccountName" value="' . $_POST['BankAccountName'] . '" size="40" maxlength="50" /></td>
@@ -341,8 +362,17 @@
<td><input tabindex="4" ' . (in_array('BankAddress',$Errors) ? 'class="inputerror"' : '' ) .' type="text" name="BankAddress" value="' . $_POST['BankAddress'] . '" size="40" maxlength="50" /></td>
</tr>
<tr>
+ <td>' . _('Transaction Import File Format') . ': </td>
+ <td><select tabindex="5" name="ImportFormat">
+ <option ' . ($_POST['ImportFormat']=='' ? 'selected="selected"' : '') . ' value="">' . _('N/A') . '</option>
+ <option ' . ($_POST['ImportFormat']=='MT940-SCB' ? 'selected="selected"' : '') . ' value="MT940-SCB">' . _('MT940 - Siam Comercial Bank Thailand') . '</option>
+ <option ' . ($_POST['ImportFormat']=='MT940-ING' ? 'selected="selected"' : '') . ' value="MT940-ING">' . _('MT940 - ING Bank Netherlands') . '</option>
+ </select>
+ </td>
+ </tr>
+ <tr>
<td>' . _('Currency Of Account') . ': </td>
- <td><select tabindex="5" name="CurrCode">';
+ <td><select tabindex="6" name="CurrCode">';
if (!isset($_POST['CurrCode']) or $_POST['CurrCode']==''){
$_POST['CurrCode'] = $_SESSION['CompanyRecord']['currencydefault'];
@@ -364,7 +394,7 @@
echo '<tr>
<td>' . _('Default for Invoices') . ': </td>
- <td><select tabindex="6" name="DefAccount">';
+ <td><select tabindex="8" name="DefAccount">';
if (!isset($_POST['DefAccount']) OR $_POST['DefAccount']==''){
$_POST['DefAccount'] = $_SESSION['CompanyRecord']['currencydefault'];
@@ -393,11 +423,12 @@
<option value="0">' . _('No') . '</option>';
}
-echo '</select></td>';
-
-echo '</tr></table><br />
- <div class="centre"><input tabindex="7" type="submit" name="submit" value="'. _('Enter Information') .'" /></div>';
-echo '</div>';
-echo '</form>';
+echo '</select></td>
+ </tr>
+ </table>
+ <br />
+ <div class="centre"><input tabindex="9" type="submit" name="submit" value="'. _('Enter Information') .'" /></div>
+ </div>
+ </form>';
include('includes/footer.inc');
?>
\ No newline at end of file
Modified: trunk/ImportBankTrans.php
===================================================================
--- trunk/ImportBankTrans.php 2014-09-14 00:39:55 UTC (rev 6885)
+++ trunk/ImportBankTrans.php 2014-09-14 02:51:55 UTC (rev 6886)
@@ -8,6 +8,7 @@
include ('includes/header.inc');
include('includes/SQL_CommonFunctions.inc');
+include('includes/CurrenciesArray.php');
/*
Read in the flat file one line at a time
@@ -15,9 +16,35 @@
*/
if (!isset($_FILES['ImportFile']) AND !isset($_SESSION['Statement'])) {
- echo '<form name="ImportForm" enctype="multipart/form-data" method="post" action="' . $_SERVER['PHP_SELF'] . '">';
- echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
+
+ $sql = "SELECT bankaccountname,
+ bankaccountnumber,
+ currcode,
+ importformat
+ FROM bankaccounts WHERE importformat <>''";
+
+ $ErrMsg = _('The bank accounts set up could not be retrieved because');
+ $DbgMsg = _('The SQL used to retrieve the bank accounts was') . '<br />' . $sql;
+ $result = DB_query($sql,$db,$ErrMsg,$DbgMsg);
+ if (DB_num_rows($result) ==0){
+ prnMsg(_('There are no bank accounts defined that are set up to allow importation of bank statement transactions. First define the file format used by your bank for statement exports.'),'error');
+ echo '<br /><a href="BankAccounts.php>' . _('Setup Import Format for Bank Accounts') . '</a>';
+ include('includes/footer.inc');
+ exit;
+ }
+ echo '<form name="ImportForm" enctype="multipart/form-data" method="post" action="' . $_SERVER['PHP_SELF'] . '">
+ <input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
<table>
+ <tr>
+ <td>' . _('Bank Account to Import Transaction For') . '</td>
+ <td><select name="ImportFormat">';
+
+ while ($myrow = DB_fetch_array($result)) {
+ echo '<option value="' . $myrow['importformat'] . '">' . $myrow['bankaccountname'] . '</option>';
+ }
+
+ echo '</td>
+ </tr>
<tr>
<td>' . _('MT940 format Bank Statement File to import') . '</td>
<td><input type="file" id="ImportFile" autofocus="autofocus" required="required" title="' . _('Select the file that contains the bank transactions in MT940 format') . '" name="ImportFile"></td>
@@ -33,7 +60,7 @@
//But check for the worst
if ($_FILES['ImportFile']['size'] > (1024*1024)) { //File Size Check
- prnMsg(_('The file size is over the maximum allowed. The maximum size allowed is 1 megabyte'),'warn');
+ prnMsg(_('The file size is over the maximum allowed. The maximum size allowed is 1 megabyte. This file size is (bytes)') . ' ' . $_FILES['ImportFile']['size'],'warn');
prnMsg(_('The MT940 bank statement file cannot be imported and processed'),'error');
include('includes/footer.inc');
exit;
@@ -55,9 +82,15 @@
$_SESSION['Statement']->FileName = $_FILES['ImportFile']['tmp_name'];
while ($LineText = fgets($fp)){ /* get each line of the order file */
- include('includes/ImportBankTrans_MT940_SCB.php'); //for Siam Commercial Bank Thailand
- //for ING Bank Netherlands include('includes/ImportBankTrans_MT940_ING.php');
+ switch ($_POST['ImportFormat']) {
+ case 'MT940-SCB':
+ include('includes/ImportBankTrans_MT940_SCB.php'); //for Siam Commercial Bank Thailand
+ break;
+ case 'MT940-ING': //for ING Bank Netherlands
+ include('includes/ImportBankTrans_MT940_ING.php');
+ }
+
} /*end while get next line of message */
/* Look to match up the account for which transactions are being imported with a bank account in webERP */
Modified: trunk/ImportBankTransAnalysis.php
===================================================================
--- trunk/ImportBankTransAnalysis.php 2014-09-14 00:39:55 UTC (rev 6885)
+++ trunk/ImportBankTransAnalysis.php 2014-09-14 02:51:55 UTC (rev 6886)
@@ -20,7 +20,7 @@
echo '<br /><a href="' . $RootPath . '/ImportBankTrans.php">' . _('Import Bank Transactions') . '</a>';
include('includes/footer.inc');
exit;
- /*It all stops here if there aint no bank transactions being imported i.e. $_SESSION['Trans'] has not been initiated
+ /*It all stops here if there aint no bank transactions being imported i.e. $_SESSION['Trans'] has not been initiated
* */
}
@@ -76,7 +76,7 @@
if (!is_numeric($_POST['Amount'])){
prnMsg( _('The amount entered is not numeric') . '. ' . _('This line cannot be added to the transaction'),'error');
$InputError = True;
- }
+ }
}
if ($InputError == False){
@@ -132,11 +132,11 @@
echo '<tr>
<td>' . _('Payment to Supplier Account') . ':</td>
<td><select name="SupplierID" onChange="ReloadForm(form1.Update)">';
-
- $result = DB_query("SELECT supplierid,
- suppname
- FROM suppliers
- WHERE currcode='" . $_SESSION['Statement']->CurrCode . "'
+
+ $result = DB_query("SELECT supplierid,
+ suppname
+ FROM suppliers
+ WHERE currcode='" . $_SESSION['Statement']->CurrCode . "'
ORDER BY suppname",$db);
if ($_SESSION['Trans'][$TransID]->SupplierID ==''){
echo '<option selected value="">' . _('GL Payment') . '</option>';
@@ -157,16 +157,16 @@
$AllowGLAnalysis = true;
} else {
$AllowGLAnalysis = false;
- }
+ }
echo '</table>';
} else { //its a receipt
echo '<tr>
<td>' . _('Receipt to Customer Account') . ':</td>
<td><select name="DebtorNo" onChange="ReloadForm(form1.Update)">';
-
- $result = DB_query("SELECT debtorno,
- name
- FROM debtorsmaster
+
+ $result = DB_query("SELECT debtorno,
+ name
+ FROM debtorsmaster
WHERE currcode='" . $_SESSION['Statement']->CurrCode . "'
ORDER BY name",$db);
if ($_SESSION['Trans'][$TransID]->DebtorNo ==''){
@@ -188,7 +188,7 @@
$AllowGLAnalysis = true;
} else {
$AllowGLAnalysis = false;
- }
+ }
echo '</table>';
}
@@ -198,23 +198,22 @@
$_SESSION['Trans'][$TransID]->Remove_GLEntry($GLAnalysisLine->ID);
}
} else { /*Allow GL Analysis == true */
- echo '</p><table cellpadding="2" class="selection">';
- $TableHeader = '<tr>
- <th colspan="5">' . _('General ledger Analysis') . '</th>
- </tr>
- <tr>
- <th class="ascending">' . _('Account') . '</th>
- <th class="ascending">' . _('Name') . '</th>
- <th class="ascending">' . _('Amount') . '<br />' . _('in') . ' ' . $_SESSION['Statement']->CurrCode . '</th>
- <th>' . _('Narrative') . '</th>
- <th class="ascending">' . _('Tag') . '</th>
- </tr>';
- echo $TableHeader;
+ echo '</p><table cellpadding="2" class="selection">
+ <tr>
+ <th colspan="5">' . _('General ledger Analysis') . '</th>
+ </tr>
+ <tr>
+ <th class="ascending">' . _('Account') . '</th>
+ <th class="ascending">' . _('Name') . '</th>
+ <th class="ascending">' . _('Amount') . '<br />' . _('in') . ' ' . $_SESSION['Statement']->CurrCode . '</th>
+ <th>' . _('Narrative') . '</th>
+ <th class="ascending">' . _('Tag') . '</th>
+ </tr>';
$TotalGLValue=0;
$i=0;
-
+
foreach ( $_SESSION['Trans'][$TransID]->GLEntries AS $EnteredGLCode){
-
+
echo '<tr>
<td>' . $EnteredGLCode->GLCode . '</td>
<td>' . $EnteredGLCode->GLAccountName . '</td>
@@ -224,16 +223,10 @@
<td><a href="' . $_SERVER['PHP_SELF'] . '?Edit=' . $EnteredGLCode->ID . '&TransID=' . $TransID . '">' . _('Edit') . '</a></td>
<td><a href="' . $_SERVER['PHP_SELF'] . '?Delete=' . $EnteredGLCode->ID . '&TransID=' . $TransID . '">' . _('Delete') . '</a></td>
</tr>';
-
+
$TotalGLValue += $EnteredGLCode->Amount;
-
- $i++;
- if ($i>15){
- $i = 0;
- echo $TableHeader;
- }
}
-
+
echo '<tr>
<td colspan="2" class="number">' . _('Total of GL Entries') . ':</td>
<td class="number">' . locale_number_format($TotalGLValue,$_SESSION['Statement']->CurrDecimalPlaces) . '</td>
@@ -243,7 +236,7 @@
<td class="number">' . locale_number_format($_SESSION['Trans'][$TransID]->Amount,$_SESSION['Statement']->CurrDecimalPlaces) . '</td>
</tr>
<tr>';
-
+
if (($_SESSION['Trans'][$TransID]->Amount - $TotalGLValue)!=0) {
echo '<td colspan="2" class="number">' . _('Yet To Enter') . ':</font></td>
<td class="number"><font size="4" color="red">' . locale_number_format($_SESSION['Trans'][$TransID]->Amount-$TotalGLValue,$_SESSION['Statement']->CurrDecimalPlaces) . '</td>';
@@ -261,12 +254,12 @@
}
echo '<tr>
<td>' . _('Account Code') . ':</td>
- <td><input type="text" name="GLCode" size="12" required="required" maxlength="11" value="' . $_POST['GLCode'] . '"></td>
+ <td><input type="text" name="GLCode" size="12" maxlength="11" value="' . $_POST['GLCode'] . '"></td>
</tr>';
echo '<tr>
<td>' . _('Account Selection') . ':<br />(' . _('If you know the code enter it above') . '<br />' . _('otherwise select the account from the list') . ')</td>
<td><select name="AcctSelection">';
-
+
$result = DB_query("SELECT accountcode, accountname FROM chartmaster ORDER BY accountcode", $db);
echo '<option value=""></option>';
while ($myrow = DB_fetch_array($result)) {
@@ -277,7 +270,7 @@
}
echo $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' - ' . $myrow['accountname'] . '</option>';
}
-
+
echo '</select>
</td>
</tr>';
@@ -288,7 +281,7 @@
<td>' . _('Amount') . ':</td>
<td><input type="text" class="number" name="Amount" required="required" size="12" maxlength="11" value="' . locale_number_format($_POST['Amount'],$_SESSION['Statement']->CurrDecimalPlaces) . '"></td>
</tr>';
-
+
if (!isset($_POST['Narrative'])) {
$_POST['Narrative']='';
}
@@ -296,16 +289,16 @@
<td>' . _('Narrative') . ':</td>
<td><textarea name="Narrative" cols=40 rows=2>' . $_POST['Narrative'] . '</textarea></td>
</tr>';
-
+
//Select the tag
echo '<tr><td>' . _('Tag') . '</td>
<td><select name="GLTag">';
-
+
$SQL = "SELECT tagref,
tagdescription
FROM tags
ORDER BY tagref";
-
+
$result=DB_query($SQL,$db);
echo '<option value="0">0 - ' . _('None') . '</option>';
while ($myrow=DB_fetch_array($result)){
Modified: trunk/doc/Change.log
===================================================================
--- trunk/doc/Change.log 2014-09-14 00:39:55 UTC (rev 6885)
+++ trunk/doc/Change.log 2014-09-14 02:51:55 UTC (rev 6886)
@@ -1,7 +1,8 @@
webERP Change Log
+13/9/14 Phil: Added new MT940 - SCB - Siam Comercial Bank Thailand style - a bit different to ING's style. Modifications to BankAccounts.php to allow the transaction file format for imports. ImportBankTrans.php must now select the bank account to determine the file import format to use. It now uses an include for the file parsing so other formats can easily be accomodated with a new include.
11/09/14 RChacon: Adds gettext() to line 402 of CustomerAllocations.php. Reported by Harald.
11/09/14 Exson: Code tidy up to make it meet coding guidance in CustomerInquiry.php.
-11/09/14: Exson: Fixed the prnMsg bug in CustomerReceipt.php and add an inquiry link in it and make a status selectable in Customer inquiry and removed those rowstring definition which caused printf parameters missing.
+11/09/14: Exson: Fixed the prnMsg bug in CustomerReceipt.php and add an inquiry link in it and make a status selectable in Customer inquiry and removed those rowstring definition which caused printf parameters missing.
31/8/14 RChacon: In general: Replaces code to determine background colour with TableRows(). Adds comments to ManualNewScripts.html. Fixes manual.css format. In GLTransInquiry.php: Adds $ViewTopic and $BookMark and sorts columns, and minor improvements. Minor translation improvements.
31/8/14 RChacon: Standardizes page_title_text to main-menu-option, standardizes bottom tax-menu, adds $ViewTopic and $BookMark, completes table header columns, formats data columns in Tax* scripts.
30/8/14 RChacon: In TaxAuthorities.php, adds $ViewTopic and $BookMark, completes table headings, formats number columns to fix sort order, and minor improvements.
Modified: trunk/doc/Manual/ManualTax.html
===================================================================
--- trunk/doc/Manual/ManualTax.html 2014-09-14 00:39:55 UTC (rev 6885)
+++ trunk/doc/Manual/ManualTax.html 2014-09-14 02:51:55 UTC (rev 6886)
@@ -63,15 +63,15 @@
<h2><a id="TaxCategories">Tax Category Maintenance</a></h2>
-<p>In this script, you can enter, edit or delete a tax category name.</p>
+<p>In this script, you can enter, edit or delete a tax category name. A tax category is required for each category of items that attracts different rates of tax because in some authorities childrens clothing and basic food items are not subject to tax. Similarly luxury items might attract higher rates of tax.</p>
<p>You can enter any tax category name. But there are three special tax category names:</p>
<ul>
-<li><b>Exempt</b>. "<i>Exempt</i>" written in British English (en-GB). This tax category name is translated to other languages, if translated term is available. You can edit and you can delete this category.</li>
+<li><b>Exempt</b>. "<i>Exempt</i>" written in British English (en-GB). This tax category name is translated to other languages, if translated term is available. You can edit and you can delete this category. Items that are defined with this tax category would normally attract no tax - so the rates for the authority/tax category combination would normally be zero.</li>
-<li><b>Freight</b>. "<i>Freight</i>" written in British English (en-GB). This tax category name is translated to other languages, if translated term is available. You can NOT edit nor you can NOT delete this category, because it is used in several scripts.</li>
+<li><b>Freight</b>. "<i>Freight</i>" written in British English (en-GB). This tax category name is translated to other languages, if translated term is available. You can NOT edit nor you can NOT delete this category, because it is used in several scripts. The Freight tax category specifies the tax rate from the tax authorities/tax category combination for the order freight value. This is hard coded in webERP so it cannot be deleted and must be present</li>
<li><b>Handling</b>. "<i>Handling</i>" written in British English (en-GB). This tax category name is translated to other languages, if translated term is available. You can edit and you can delete this category.</li>
Added: trunk/includes/ImportBankTrans_MT940_ING.php
===================================================================
--- trunk/includes/ImportBankTrans_MT940_ING.php (rev 0)
+++ trunk/includes/ImportBankTrans_MT940_ING.php 2014-09-14 02:51:55 UTC (rev 6886)
@@ -0,0 +1,89 @@
+<?php
+/* MT940 per ING Bank Netherlands */
+
+if (substr($LineText,0,4)==':20:'){ //Timestamp of report MT940 generation
+ $_SESSION['Statement']->ReportCreated = substr($LineText,4); //in format DDDHHMM where DDD is the number of day in year and HHMM is the time
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,4)==':25:'){//The account number in IBAN format
+ $_SESSION['Statement']->AccountNumber = trim(substr($LineText,4));
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,5)==':28C:'){//The statement number
+ $_SESSION['Statement']->StatementNumber = trim(substr($LineText,5));
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,6)==':NS:22'){//The account owner name
+ $_SESSION['Statement']->AccountOwner = trim(substr($LineText,6));
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,6)==':NS:23'){//The account name
+ $_SESSION['Statement']->AccountName = trim(substr($LineText,6));
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,5)==':60F:'){//The account opening balance
+ $DebitOrCredit = substr($LineText,5,1); //D or C
+ $_SESSION['Statement']->OpeningDate = ConvertSQLDate('20' . substr($LineText,6,2) . '-' . substr($LineText,8,2) . '-' . substr($LineText,10,2));
+ $_SESSION['Statement']->CurrCode = substr($LineText,12,3);
+ if ($DebitOrCredit =='D'){
+ $_SESSION['Statement']->OpeningBalance = doubleval(str_replace(',','.',substr($LineText,15)));
+ } else {
+ $_SESSION['Statement']->OpeningBalance = doubleval('-' . str_replace(',','.',substr($LineText,15)));
+ }
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,4)==':61:'){//It's a transaction line
+ $TransactionLine = true;
+ $TransDate = ConvertSQLDate('20' . substr($LineText,4,2) . '-' . substr($LineText,6,2) . '-' . substr($LineText,8,2));
+ $DebitOrCredit = substr($LineText,10,1); //D or C or R
+ if ($DebitOrCredit =='R'){ //then it is a 2 character reversal
+ if (substr($LineText,10,2)=='RC'){
+ $DebitOrCredit ='D';
+ } else {
+ $DebitOrCredit ='C';
+ }
+ if ($DebitOrCredit =='D'){
+ $TransAmount = doubleval(str_replace(',','.',substr($LineText,12,-10)));
+ } else {
+ $TransAmount = doubleval('-' . str_replace(',','.',substr($LineText,12,-10)));
+ }
+ } else { // it will be either D or C
+ //check for a B - no idea why but can be a B after the D or C for some reason in which case the amount starts a character after
+ if (substr($LineText,11,1)=='B'){
+ $ValueStart = 12;
+ } else {
+ $ValueStart = 11;
+ }
+ if ($DebitOrCredit =='D'){
+ $TransAmount = doubleval(str_replace(',','.',substr($LineText,$ValueStart,-10)));
+ } else {
+ $TransAmount = doubleval('-' . str_replace(',','.',substr($LineText,$ValueStart,-10)));
+ }
+ }
+ $i++;
+ $_SESSION['Trans'][$i] = new BankTrans($TransDate,$TransAmount) ;
+ }
+ if (substr($LineText,0,4)==':86:'){
+ if ($TransactionLine) {
+ $_SESSION['Trans'][$i]->Code = substr($LineText,4,3);
+ $_SESSION['Trans'][$i]->Description = substr($LineText,7);
+ }
+ }
+
+ if (substr($LineText,0,1)!=':' AND $TransactionLine){
+ //then it is the continuation of an :86: line
+ $_SESSION['Trans'][$i]->Description .= $LineText;
+ }
+
+ if (substr($LineText,0,5)==':62F:'){
+ $DebitOrCredit = substr($LineText,5,1); //D or C
+ $_SESSION['Statement']->ClosingDate = ConvertSQLDate('20' . substr($LineText,6,2) . '-' . substr($LineText,8,2) . '-' . substr($LineText,10,2));
+ $CurrCode = substr($LineText,12,3);
+ if ($DebitOrCredit =='D'){
+ $_SESSION['Statement']->ClosingBalance = doubleval(str_replace(',','.',substr($LineText,15)));
+ } else {
+ $_SESSION['Statement']->ClosingBalance = doubleval('-' . str_replace(',','.',substr($LineText,15)));
+ }
+ $TransactionLine = false;
+ }
+?>
\ No newline at end of file
Added: trunk/includes/ImportBankTrans_MT940_SCB.php
===================================================================
--- trunk/includes/ImportBankTrans_MT940_SCB.php (rev 0)
+++ trunk/includes/ImportBankTrans_MT940_SCB.php 2014-09-14 02:51:55 UTC (rev 6886)
@@ -0,0 +1,102 @@
+<?php
+/* MT940 per SCB - Siam Comercial Bank Thailand */
+
+if (substr($LineText,0,4)==':20:'){ //Timestamp of report MT940 generation
+ $_SESSION['Statement']->ReportCreated = substr($LineText,4); //in format DDDHHMM where DDD is the number of day in year and HHMM is the time
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,4)==':25:'){//The account number in IBAN format
+ $_SESSION['Statement']->AccountNumber = trim(substr($LineText,4));
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,5)==':28C:'){//The statement number
+ $_SESSION['Statement']->StatementNumber = trim(substr($LineText,5));
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,6)==':NS:22'){//The account owner name
+ $_SESSION['Statement']->AccountOwner = trim(substr($LineText,6));
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,6)==':NS:23'){//The account name
+ $_SESSION['Statement']->AccountName = trim(substr($LineText,6));
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,5)==':60F:'){//The account opening balance
+ $DebitOrCredit = substr($LineText,5,1); //D or C
+ $_SESSION['Statement']->OpeningDate = ConvertSQLDate('20' . substr($LineText,6,2) . '-' . substr($LineText,8,2) . '-' . substr($LineText,10,2));
+ $_SESSION['Statement']->CurrCode = substr($LineText,12,3);
+ if (!array_key_exists($_SESSION['Statement']->CurrCode,$CurrencyName)){
+ prnMsg(_('The bank statement currency is a currency not defined in the system. Please see you system administrator'),'warn');
+ prnMsg(_('The MT940 bank statement file cannot be imported and processed'),'error');
+ include('includes/footer.inc');
+ exit;
+ $ReadTheFile ='No';
+ }
+ if ($DebitOrCredit =='D'){
+ $_SESSION['Statement']->OpeningBalance = doubleval(str_replace(',','.',substr($LineText,15)));
+ } else {
+ $_SESSION['Statement']->OpeningBalance = doubleval('-' . str_replace(',','.',substr($LineText,15)));
+ }
+ $TransactionLine = false;
+ }
+ if (substr($LineText,0,4)==':61:'){//It's a transaction line
+ $TransactionLine = true;
+ $TransDate = ConvertSQLDate('20' . substr($LineText,4,2) . '-' . substr($LineText,6,2) . '-' . substr($LineText,8,2));
+ //this format repeats the date from characters 10-14
+ $DebitOrCredit = substr($LineText,14,1); //D or C or R
+ if ($DebitOrCredit =='R'){ //then it is a 2 character reversal
+ if (substr($LineText,14,2)=='RC'){
+ $DebitOrCredit ='D';
+ } else {
+ $DebitOrCredit ='C';
+ }
+ //Need to find end of value amount - find the , decimal point + 2 characters
+ $ValueEnd = strpos($LineText, ',', 12)+2;
+ if ($DebitOrCredit =='D'){
+ $TransAmount = doubleval(str_replace(',','.',substr($LineText,12,$ValueEnd)));
+ } else {
+ $TransAmount = doubleval('-' . str_replace(',','.',substr($LineText,12,$ValueEnd)));
+ }
+ } else { // it will be either D or C
+ if (!is_numeric(substr($LineText,15,1)) ){
+ //check for a B - no idea why but can be a B after the D or C for some reason in which case the amount starts a character after
+ $ValueStart = 16;
+ } else {
+ $ValueStart = 15;
+ }
+ //Need to find end of value amount - find the , decimal point + 2 characters
+ $ValueEnd = strpos($LineText, ',', $ValueStart)+2;
+ if ($DebitOrCredit =='D'){
+ $TransAmount = doubleval(str_replace(',','.',substr($LineText,$ValueStart,$ValueEnd)));
+ } else {
+ $TransAmount = doubleval('-' . str_replace(',','.',substr($LineText,$ValueStart,$ValueEnd)));
+ }
+ }
+
+ $i++;
+ $_SESSION['Trans'][$i] = new BankTrans($TransDate,$TransAmount) ;
+ $_SESSION['Trans'][$i]->Description = substr($LineText,$ValueEnd+1);
+ }
+ if (substr($LineText,0,4)==':86:'){
+ if ($TransactionLine) {
+ $_SESSION['Trans'][$i]->Description .= ' ' . substr($LineText,4);
+ }
+ }
+
+ if (substr($LineText,0,1)!=':' AND $TransactionLine){
+ //then it is the continuation of an :86: line
+ $_SESSION['Trans'][$i]->Description .= $LineText;
+ }
+
+ if (substr($LineText,0,5)==':62F:'){
+ $DebitOrCredit = substr($LineText,5,1); //D or C
+ $_SESSION['Statement']->ClosingDate = ConvertSQLDate('20' . substr($LineText,6,2) . '-' . substr($LineText,8,2) . '-' . substr($LineText,10,2));
+ $CurrCode = substr($LineText,12,3);
+ if ($DebitOrCredit =='D'){
+ $_SESSION['Statement']->ClosingBalance = doubleval(str_replace(',','.',substr($LineText,15)));
+ } else {
+ $_SESSION['Statement']->ClosingBalance = doubleval('-' . str_replace(',','.',substr($LineText,15)));
+ }
+ $TransactionLine = false;
+ }
+?>
\ No newline at end of file
Modified: trunk/sql/mysql/upgrade4.11-4.12.sql
===================================================================
--- trunk/sql/mysql/upgrade4.11-4.12.sql 2014-09-14 00:39:55 UTC (rev 6885)
+++ trunk/sql/mysql/upgrade4.11-4.12.sql 2014-09-14 02:51:55 UTC (rev 6886)
@@ -81,6 +81,7 @@
INSERT INTO `scripts` ( `script` , `pagesecurity` , `description` ) VALUES ('PrintWOItemSlip.php', '4', 'PDF WO Item production Slip ');
ALTER TABLE `locations` ADD `usedforwo` TINYINT( 4 ) NOT NULL DEFAULT '1' AFTER `internalrequest`;
+ALTER TABLE `bankaccounts` ADD `importformat` VARCHAR( 10 ) NOT NULL DEFAULT '';
UPDATE config SET confvalue='4.12' WHERE confname='VersionNumber';
|
