Menu
▾
▴
[Web-erp-svn] SF.net SVN: web-erp:[6036] trunk
|
From: <dai...@us...> - 2013-06-25 08:53:06
|
Revision: 6036
http://sourceforge.net/p/web-erp/reponame/6036
Author: daintree
Date: 2013-06-25 08:53:03 +0000 (Tue, 25 Jun 2013)
Log Message:
-----------
New maintenance module and bank transactions importation
Added Paths:
-----------
trunk/ImportBankTrans.php
trunk/ImportBankTransAnalysis.php
trunk/MaintenanceReminders.php
trunk/MaintenanceTasks.php
trunk/MaintenanceUserSchedule.php
trunk/includes/DefineImportBankTransClass.php
Added: trunk/ImportBankTrans.php
===================================================================
--- trunk/ImportBankTrans.php (rev 0)
+++ trunk/ImportBankTrans.php 2013-06-25 08:53:03 UTC (rev 6036)
@@ -0,0 +1,611 @@
+<?php
+
+/* $Id: ImportBankTrans.php 4213 2010-12-22 14:33:20Z tim_schofield $*/
+include('includes/DefineImportBankTransClass.php');
+include ('includes/session.inc');
+
+$Title = _('Import Bank Transactions');
+
+include ('includes/header.inc');
+include('includes/SQL_CommonFunctions.inc');
+
+/*
+Read in the flat file one line at a time
+parse the data in the line of text from the flat file to read the bank transaction into an SESSION array of banktransactions objects
+*/
+
+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'] . '" />';
+ echo '<table>
+ <tr>
+ <td>'. _('MT940 format Bank Statement File to import').'</td>
+ <td><input type="file" id="ImportFile" name="ImportFile"></td>
+ </tr>
+ </table>';
+ echo '<div class="centre"><input type="submit" name="Import" value="Process"></div>';
+ echo '</form>';
+
+} elseif (isset($_POST['Import'])){
+
+ $result = $_FILES['ImportFile']['error'];
+ $ReadTheFile = 'Yes'; //Assume all is well to start off with
+
+ //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');
+ $ReadTheFile ='No';
+ }
+
+ /*elseif ( $_FILES['ImportFile']['type'] != 'text/plain' ) { //File Type Check
+ prnMsg( _('A plain text file is expected, this file is a') . ' ' . $_FILES['ImportFile']['type'],'warn');
+ $ReadTheFile ='No';
+ } */
+
+ if ($ReadTheFile=='No'){
+ prnMsg(_('The MT940 bank statement file cannot be imported and processed'),'error');
+ include('includes/footer.inc');
+ exit;
+ }
+ $fp = fopen($_FILES['ImportFile']['tmp_name'], 'r');
+
+ $TransactionLine = false;
+ $i=0;
+ $_SESSION['Statement'] = new BankStatement;
+ $_SESSION['Trans'] = array();
+
+ $_SESSION['Statement']->FileName = $_FILES['ImportFile']['tmp_name'];
+
+ while ($LineText = fgets($fp)){ /* get each line of the order file */
+ /*
+ * This block of code could be in an include and different includes included depending on which type of transaction file is being imported
+ * */
+
+ 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
+ if ($DebitOrCredit =='D'){
+ $TransAmount = doubleval(str_replace(',','.',substr($LineText,11,-10)));
+ } else {
+ $TransAmount = doubleval('-' . str_replace(',','.',substr($LineText,11,-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;
+ }
+ /* end of MT940 specific import code - that could be in an include if we get other file formats
+ * */
+ } /*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 */
+ $sql = "SELECT accountcode,
+ bankaccountname,
+ decimalplaces,
+ rate
+ FROM bankaccounts INNER JOIN currencies
+ ON bankaccounts.currcode=currencies.currabrev
+ WHERE bankaccountnumber " . LIKE . " '" . $_SESSION['Statement']->AccountNumber ."'
+ AND currcode = '" . $_SESSION['Statement']->CurrCode . "'";
+
+ $ErrMsg = _('Could not retrieve bank accounts that match with the statement being imported');
+
+ $result = DB_query($sql,$db,$ErrMsg);
+ if (DB_num_rows($result)==0){ //but check for the worst!
+ //there is no bank account set up for the bank account being imported
+ prnMsg(_('The account') . ' ' . $_SESSION['Statement']->AccountNumber . ' ' . _('is not defined as a bank account of the business. No imports can be processed'), 'warn');
+ } else {
+ $BankAccountRow = DB_fetch_array($result);
+ $_SESSION['Statement']->BankGLAccount = $BankAccountRow['accountcode'];
+ $_SESSION['Statement']->BankAccountName = $BankAccountRow['bankaccountname'];
+ $_SESSION['Statement']->CurrDecimalPlaces = $BankAccountRow['decimalplaces'];
+ $_SESSION['Statement']->ExchangeRate = $BankAccountRow['rate'];
+
+ /* Now check to see if each transaction has already been entered */
+ for($i=1;$i<=count($_SESSION['Trans']);$i++){
+
+ $SQL = "SELECT banktransid FROM banktrans
+ WHERE transdate='" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "'
+ AND amount='" . $_SESSION['Trans'][$i]->Amount . "'
+ AND bankact='" . $_SESSION['Statement']->BankGLAccount . "'";
+ $result = DB_query($SQL,$db,_('There was a problem identifying a matching bank transaction'));
+ if (DB_num_rows($result)>0){
+ $myrow = DB_fetch_array($result);
+ $_SESSION['Trans'][$i]->BankTransID = $myrow['banktransid'];
+ }
+ }
+ } //end if there is a matching bank account in the system
+} //end if read in transaction/statement
+
+if (isset($_POST['ProcessBankTrans'])){
+ $InputError = false; //assume the best
+ if ($_SESSION['Statement']->CurrCode != $_SESSION['CompanyRecord']['currencydefault']
+ AND $_POST['ExchangeRate']==1){
+ prnMsg(_('It is necessary to enter the exchange rate to convert the bank receipts and payments into local currency for the purposes of calculating the general ledger entries necessary. The currency of this bank account is not the same as the company functional currency so an exchange rate of 1 is inappropriate'),'error');
+ $InputError = true;
+ }
+ if (!is_numeric($_POST['ExchangeRate'])){
+ prnMsg(_('The exchange rate is expected to be the number of the bank account currency that would purchase one unit of the company functional currency. A number is expected'),'error');
+ $InputError = true;
+ }
+ if ($InputError == false){
+ /*This is it - process the data into the DB
+ * First check to see if the item is flagged as matching an existing bank transaction - if it does and there is no analysis of the transaction then we need to flag the existing bank transaction as matched off the bank statement for reconciliation purposes.
+ * Then, if the transaction is analysed:
+ * 1. create the bank transaction
+ * 2. if it is a debtor receipt create a debtortrans systype 12 against the selected customer
+ * 3. if it is a supplier payment create a supptrans systype 22 against the selected supplier
+ * 4. create the gltrans for either the gl analysis or the debtor/supplier receipt/payment created
+ */
+
+ for($i=1;$i<=count($_SESSION['Trans']);$i++){
+ DB_Txn_Begin($db);
+ if ($_SESSION['Trans'][$i]->DebtorNo!='' OR
+ $_SESSION['Trans'][$i]->SupplierID!='' OR
+ $_SESSION['Trans'][$i]->GLTotal == $_SESSION['Trans'][$i]->Amount){
+ /*A Debtor or Supplier is entered or there is GL analysis for the bank trans
+ */
+ $PeriodNo = GetPeriod($_SESSION['Trans'][$i]->ValueDate,$db);
+ $InsertBankTrans = true;
+ } elseif ($_SESSION['Trans'][$i]->BankTransID!=0) {
+ //Update the banktrans to show it has cleared the bank
+ $result = DB_query("UPDATE banktrans SET amountcleared=amount
+ WHERE banktransid = '" . $_SESSION['Trans'][$i]->BankTransID . "'",
+ $db,
+ _('Could not update the bank transaction as cleared'),
+ _('The SQL that failed to update the bank transaction as cleared was'),
+ true);
+ $InsertBankTrans = false;
+ } else {
+ $InsertBankTrans = false;
+ }
+
+ if ($_SESSION['Trans'][$i]->Amount >0){ //its a receipt
+
+ if ($_SESSION['Trans'][$i]->DebtorNo!='') {
+ $TransType = 12;
+ $TransNo = GetNextTransNo(12,$db); //debtors receipt
+ /* First insert the debtortrans record */
+ $result = DB_query("INSERT INTO debtortrans (transno,
+ type,
+ debtorno,
+ trandate,
+ inputdate,
+ prd,
+ rate,
+ reference,
+ invtext,
+ ovamount)
+ VALUES ('" . $TransNo . "',
+ '" . $TransType . "',
+ '" . $_SESSION['Trans'][$i]->DebtorNo . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . Date('Y-m-d h:m:s') . "',
+ '" . $PeriodNo . "',
+ '" . $_POST['ExchangeRate'] . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '" . -$_SESSION['Trans'][$i]->Amount . "')",
+ $db,
+ _('Could not insert the customer transaction'),
+ _('The SQL used to insert the debtortrans was'),
+ true);
+ /*Now update the debtors master for the last payment date */
+ $result = DB_query("UPDATE debtorsmaster
+ SET lastpaiddate = '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ lastpaid='" . $_SESSION['Trans'][$i]->Amount ."'
+ WHERE debtorno='" . $_SESSION['Trans'][$i]->DebtorNo . "'",
+ $db,
+ _('Could not update the last payment date and amount paid'),
+ _('The SQL that failed to update the debtorsmaster was'),
+ true);
+
+ /* Now insert the gl trans to credit debtors control and debit bank account */
+ /*First credit debtors control from CompanyRecord */
+ $result = DB_query("INSERT INTO gltrans (type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES (12,
+ '" . $TransNo . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . $PeriodNo . "',
+ '" . $_SESSION['CompanyRecord']['debtorsact'] . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '" . -round($_SESSION['Trans'][$i]->Amount/$_POST['ExchangeRate'],$_SESSION['CompanyRecord']['decimalplaces']+1) . "')",
+ $db,
+ _('Cannot insert a GL entry for the receipt because'),
+ _('The SQL that failed to insert the receipt GL entry was'),
+ true);
+ /*Now debit the bank account from $_SESSION['Statement']->BankGLAccount */
+ $result = DB_query("INSERT INTO gltrans (type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES (12,
+ '" . $TransNo . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . $PeriodNo . "',
+ '" . $_SESSION['Statement']->BankGLAccount . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '" . round($_SESSION['Trans'][$i]->Amount/$_POST['ExchangeRate'],$_SESSION['CompanyRecord']['decimalplaces']+1) . "')",
+ $db,
+ _('Cannot insert a GL entry for the receipt because'),
+ _('The SQL that failed to insert the receipt GL entry was'),
+ true);
+
+ } elseif ($_SESSION['Trans'][$i]->GLTotal == $_SESSION['Trans'][$i]->Amount){
+ $TransType=2; //gl receipt
+ $TransNo = GetNextTransNo(2,$db);
+ foreach ($_SESSION['Trans'][$i]->GLEntries as $GLAnalysis){
+ /*Credit each analysis account */
+ $result = DB_query("INSERT INTO gltrans (type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES (2,
+ '" . $TransNo . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . $PeriodNo . "',
+ '" . $GLAnalysis->GLCode . "',
+ '" . DB_escape_string($GLAnalysis->Narrative . ' ' . $_SESSION['Trans'][$i]->Description) . "',
+ '" . -round($GLAnalysis->Amount/$_POST['ExchangeRate'],$_SESSION['CompanyRecord']['decimalplaces']+1) . "')",
+ $db,
+ _('Cannot insert a GL entry for the receipt gl analysis because'),
+ _('The SQL that failed to insert the gl analysis of this receipt was'),
+ true);
+
+ } //end loop around GLAnalysis
+ /*Now debit the bank account from $_SESSION['Statement']->BankGLAccount */
+ $result = DB_query("INSERT INTO gltrans (type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES (2,
+ '" . $TransNo . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . $PeriodNo . "',
+ '" . $_SESSION['Statement']->BankGLAccount . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '" . round($_SESSION['Trans'][$i]->Amount/$_POST['ExchangeRate'],$_SESSION['CompanyRecord']['decimalplaces']+1) . "')",
+ $db,
+ _('Cannot insert a GL entry for the receipt because'),
+ _('The SQL that failed to insert the receipt GL entry was'),
+ true);
+ }
+ } else { //its a payment
+ if ($_SESSION['Trans'][$i]->SupplierID!='') { //its a supplier payment
+ $TransType = 22;
+ $TransNo = GetNextTransNo(22,$db);
+ $result = DB_query("INSERT INTO supptrans (transno,
+ type,
+ supplierno,
+ trandate,
+ inputdate,
+ duedate,
+ rate,
+ suppreference,
+ transtext,
+ ovamount)
+ VALUES ('" . $TransNo . "',
+ '" . $TransType . "',
+ '" . $_SESSION['Trans'][$i]->SupplierID . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . Date('Y-m-d h:m:s') . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . $_POST['ExchangeRate'] . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '" . $_SESSION['Trans'][$i]->Amount . "')",
+ $db,
+ _('Could not insert the supplier transaction'),
+ _('The SQL used to insert the supptrans was'),
+ true);
+ /*Now update the suppliers master for the last payment date */
+ $result = DB_query("UPDATE suppliers
+ SET lastpaiddate = '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ lastpaid='" . $_SESSION['Trans'][$i]->Amount ."'
+ WHERE supplierid='" . $_SESSION['Trans'][$i]->SupplierID . "'",
+ $db,
+ _('Could not update the supplier last payment date and amount paid'),
+ _('The SQL that failed to update the supplier with the last payment amount and date was'),
+ true);
+ /* Now insert the gl trans to debit creditors control and credit bank account */
+ /*First debit creditors control from CompanyRecord */
+ $result = DB_query("INSERT INTO gltrans (type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES (22,
+ '" . $TransNo . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . $PeriodNo . "',
+ '" . $_SESSION['CompanyRecord']['creditorsact'] . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '" . round(-$_SESSION['Trans'][$i]->Amount/$_POST['ExchangeRate'],$_SESSION['CompanyRecord']['decimalplaces']+1) . "')",
+ $db,
+ _('Cannot insert a GL entry for the supplier payment to creditors control because'),
+ _('The SQL that failed to insert the creditors control GL entry was'),
+ true);
+ /*Now credit the bank account from $_SESSION['Statement']->BankGLAccount
+ * note payments are recorded as negatives in the import */
+ $result = DB_query("INSERT INTO gltrans (type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES (22,
+ '" . $TransNo . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . $PeriodNo . "',
+ '" . $_SESSION['Statement']->BankGLAccount . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '" . round($_SESSION['Trans'][$i]->Amount/$_POST['ExchangeRate'],$_SESSION['CompanyRecord']['decimalplaces']+1) . "')",
+ $db,
+ _('Cannot insert a GL entry for the supplier payment because'),
+ _('The SQL that failed to insert the supplier payment GL entry to the bank account was'),
+ true);
+
+ } elseif($_SESSION['Trans'][$i]->GLTotal == $_SESSION['Trans'][$i]->Amount){
+ //its a GL payment
+ $TransType = 1; //gl payment
+ $TransNo = GetNextTransNo(1,$db);
+ foreach ($_SESSION['Trans'][$i]->GLEntries as $GLAnalysis){
+ /*Debit each analysis account note payments are recorded as negative so need negative negative to make a debit (positive)*/
+ $result = DB_query("INSERT INTO gltrans (type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES (1,
+ '" . $TransNo . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . $PeriodNo . "',
+ '" . $GLAnalysis->GLCode . "',
+ '" . DB_escape_string($GLAnalysis->Narrative . ' ' . $_SESSION['Trans'][$i]->Description) . "',
+ '" . -round($GLAnalysis->Amount/$_POST['ExchangeRate'],$_SESSION['CompanyRecord']['decimalplaces']+1) . "')",
+ $db,
+ _('Cannot insert a GL entry for the payment gl analysis because'),
+ _('The SQL that failed to insert the gl analysis of this payment was'),
+ true);
+
+ } //end loop around GLAnalysis
+ /*Now credit the gl account from $_SESSION['Statement']->BankGLAccount
+ * Note payments are negatives*/
+ $result = DB_query("INSERT INTO gltrans (type,
+ typeno,
+ trandate,
+ periodno,
+ account,
+ narrative,
+ amount)
+ VALUES (1,
+ '" . $TransNo . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . $PeriodNo . "',
+ '" . $_SESSION['Statement']->BankGLAccount . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '" . round($_SESSION['Trans'][$i]->Amount/$_POST['ExchangeRate'],$_SESSION['CompanyRecord']['decimalplaces']+1) . "')",
+ $db,
+ _('Cannot insert a GL entry for the payment because'),
+ _('The SQL that failed to insert the payment GL entry was'),
+ true);
+ }
+
+ } //end if its a payment
+ if ($InsertBankTrans==true){
+ /* Now insert the bank transaction if necessary */
+ /* it is not possible to import transaction that were originally in another currency converted to the currency of the bank account by the bank - these entries would need to be done through the usual method */
+
+ $result=DB_query("INSERT INTO banktrans (transno,
+ type,
+ bankact,
+ ref,
+ exrate,
+ functionalexrate,
+ transdate,
+ banktranstype,
+ amount,
+ currcode,
+ amountcleared)
+ VALUES (
+ '" . $TransNo . "',
+ '" . $TransType . "',
+ '" . $_SESSION['Statement']->BankGLAccount . "',
+ '" . DB_escape_string($_SESSION['Trans'][$i]->Description) . "',
+ '1',
+ '" . $_POST['ExchangeRate'] . "',
+ '" . FormatDateForSQL($_SESSION['Trans'][$i]->ValueDate) . "',
+ '" . _('Imported') . "',
+ '" . $_SESSION['Trans'][$i]->Amount . "',
+ '" . $_SESSION['Statement']->CurrCode . "',
+ '" . $_SESSION['Trans'][$i]->Amount . "')",
+ $db,
+ _('Could not insert the bank transaction'),
+ _('The SQL that failed to insert the bank transaction was'),
+ true);
+ }
+ DB_Txn_Commit($db); // complete this bank transactions posting
+ } //end loop around the transactions
+ echo '<p />';
+ prnMsg(_('Completed the importing of analysed bank transactions'),'info');
+ unset($_SESSION['Trans']->GLEntries);
+ unset($_SESSION['Trans']);
+ unset($_SESSION['Statement']);
+ } // there were no input errors - the exchange rate was entered
+}
+
+
+
+if (isset($_SESSION['Statement'])){
+
+ echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="post" >';
+ echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
+ if (!isset($_SESSION['Statement']->BankGLAccount)){
+ $AllowImport = false;
+ } else {
+ $AllowImport = true;
+ }
+ /* show the statement in any event - just don't have links to process transactions if NOT $AllowImport
+ */
+ echo '<table class="selection">
+ <tr>
+ <th colspan="5">' . _('Bank Statement No') . ' ' . $_SESSION['Statement']->StatementNumber . ' ' . _('for') . ' ' . $_SESSION['Statement']->BankAccountName . ' ' . _('Number') . ' ' . $_SESSION['Statement']->AccountNumber . '</th>
+ </tr>
+ <tr>
+ <th colspan ="3">' . _('Opening Balance as at') . ' ' . $_SESSION['Statement']->OpeningDate . ' ' . _('in') . ' ' .$_SESSION['Statement']->CurrCode . '</th>';
+ if ($_SESSION['Statement']->OpeningBalance >=0){
+ echo '<th class="number">' . number_format($_SESSION['Statement']->OpeningBalance,$_SESSION['Statement']->CurrDecimalPlaces) . '</th><th></th></tr>';
+ } else {
+ echo '<th></th><th class="number">' . number_format($_SESSION['Statement']->OpeningBalance,$_SESSION['Statement']->CurrDecimalPlaces) . '</th></tr>';
+ }
+ for ($i=1; $i<=count($_SESSION['Trans']); $i++){
+
+ if ($_SESSION['Trans'][$i]->Amount >0){
+ if ($_SESSION['Trans'][$i]->DebtorNo!=''
+ OR $_SESSION['Trans'][$i]->GLTotal == $_SESSION['Trans'][$i]->Amount){
+ echo '<tr style="background-color: #FFFCCC;">';
+ } elseif ($_SESSION['Trans'][$i]->BankTransID!=0) {
+ echo '<tr style="background-color: #FFF222;">';
+ } else {
+ echo '<tr>';
+ }
+ } else { //its a payment
+ if ($_SESSION['Trans'][$i]->SupplierID!=''
+ OR $_SESSION['Trans'][$i]->GLTotal == $_SESSION['Trans'][$i]->Amount){
+ echo '<tr style="background-color: #FFFCCC;">';
+ } elseif ($_SESSION['Trans'][$i]->BankTransID!=0) {
+ echo '<tr style="background-color: #FFF222;">';
+ } else {
+ echo '<tr>';
+ }
+ }
+ echo '<td>' . $_SESSION['Trans'][$i]->Code . '</td>
+ <td>' . $_SESSION['Trans'][$i]->ValueDate . '</td>
+ <td>' . $_SESSION['Trans'][$i]->Description . '</td>';
+
+ if ($_SESSION['Trans'][$i]->Amount>=0){
+ echo '<td class="number">' . number_format($_SESSION['Trans'][$i]->Amount,$_SESSION['Statement']->CurrDecimalPlaces) . '</td><td></td>';
+ } else {
+ echo '<td></td><td class="number">' . number_format($_SESSION['Trans'][$i]->Amount,$_SESSION['Statement']->CurrDecimalPlaces) . '</td>';
+ }
+ if ($AllowImport==true) {
+ echo '<td><a href="' . $RootPath . '/ImportBankTransAnalysis.php?TransID=' . $i .'">' . _('Analysis') .'</a></td>';
+ }
+ echo '</tr>';
+ }
+ echo '<tr>
+ <th colspan="3">' . _('Closing Balance as at') . ' ' . $_SESSION['Statement']->ClosingDate . ' ' . _('in') . ' ' .$_SESSION['Statement']->CurrCode . '</th>';
+ if ($_SESSION['Statement']->ClosingBalance>=0){
+ echo '<th class="number">' . number_format($_SESSION['Statement']->ClosingBalance,$_SESSION['Statement']->CurrDecimalPlaces) . '</th><th></th>
+ </tr>';
+ } else {
+ echo '<th></th><th class="number">' . number_format($_SESSION['Statement']->ClosingBalance,$_SESSION['Statement']->CurrDecimalPlaces) . '</th>
+ </tr>';
+ }
+ echo '</table>';
+ echo '<br />
+ <table class="selection">';
+ if ($_SESSION['Statement']->CurrCode!=$_SESSION['CompanyRecord']['currencydefault']){
+
+ echo '<tr>
+ <td>' . _('Exchange Rate to Use When Processing Transactions') . '</td>
+ <td><input type="text" class="number" name="ExchangeRate" value="' . $_SESSION['Statement']->ExchangeRate . '" /></td>
+ </tr>';
+ } else {
+ echo '<input type="hidden" name="ExchangeRate" value="1" />';
+ }
+ echo '<tr>
+ <th colspan="2"><input type="submit" name="ProcessBankTrans" value="' . _('Process Bank Transactions') . '" onclick="return confirm(\'' . _('This process will create bank transactions for ONLY THE ANALYSED transactions shown in yellow above together with the necessary general ledger journals and customer or supplier transactions. Are You Sure?') . '\');" /></th>
+ </tr>
+ </table>';
+}
+
+
+include ('includes/footer.inc');
+?>
\ No newline at end of file
Added: trunk/ImportBankTransAnalysis.php
===================================================================
--- trunk/ImportBankTransAnalysis.php (rev 0)
+++ trunk/ImportBankTransAnalysis.php 2013-06-25 08:53:03 UTC (rev 6036)
@@ -0,0 +1,326 @@
+<?php
+
+/* $Id: SuppTransGLAnalysis.php 4578 2011-05-28 11:01:00Z daintree $*/
+
+/*The ImportBankTransClass contains the structure ofinformation about the transactions
+An array of class BankTrans objects - containing details of the bank transactions has an array of
+GLEntries objects to hold the GL analysis for each transaction */
+
+include('includes/DefineImportBankTransClass.php');
+
+/* Session started in header.inc for password checking and authorisation level check */
+include('includes/session.inc');
+
+$Title = _('Imported Bank Transaction General Ledger Analysis');
+
+include('includes/header.inc');
+
+if (!isset($_SESSION['Trans'])){
+ prnMsg(_('This page can only be called from the importation of bank transactions page which sets up the data to receive the analysed general ledger entries'),'info');
+ 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
+ * */
+}
+
+if (isset($_GET['TransID'])){
+ $TransID = $_GET['TransID'];
+} else {
+ $TransID = $_POST['TransID'];
+}
+if (!isset($TransID)){
+ prnMsg(_('This page can only be called from the importation of bank transactions page which sets up the data to receive the analysed general ledger entries'),'info');
+ echo '<br /><a href="' . $RootPath . '/ImportBankTrans.php">' . _('Import Bank Transactions') . '</a>';
+ include('includes/footer.inc');
+ exit;
+}
+
+if ($_SESSION['Trans'][$TransID]->BankTransID != 0) {
+ prnMsg(_('This transaction appears to be already entered against this bank account. By entering values in this analysis form the transaction will be entered again. Only proceed to analyse this transaction if you are sure it has not already been processed'),'warn');
+ echo '<br /><div class="centre"><a href="' . $RootPath . '/ImportBankTrans.php">' . _('Back to Main Import Screen - Recommended') . '</a></div>';
+
+}
+
+if (isset($_POST['DebtorNo'])){
+ $_SESSION['Trans'][$TransID]->DebtorNo = $_POST['DebtorNo'];
+}
+if (isset($_POST['SupplierID'])){
+ $_SESSION['Trans'][$TransID]->SupplierID = $_POST['SupplierID'];
+}
+/*If the user hit the Add to transaction button then process this first before showing all GL codes on the transaction otherwise it wouldnt show the latest addition*/
+
+if (isset($_POST['AddGLCodeToTrans']) AND $_POST['AddGLCodeToTrans'] == _('Enter GL Line')){
+
+ $InputError = False;
+ if ($_POST['GLCode'] == ''){
+ $_POST['GLCode'] = $_POST['AcctSelection'];
+ }
+
+ if ($_POST['GLCode'] == ''){
+ prnMsg( _('You must select a general ledger code from the list below') ,'warn');
+ $InputError = True;
+ }
+
+ $sql = "SELECT accountcode,
+ accountname
+ FROM chartmaster
+ WHERE accountcode='" . $_POST['GLCode'] . "'";
+ $result = DB_query($sql, $db);
+ if (DB_num_rows($result) == 0 AND $_POST['GLCode'] != ''){
+ prnMsg(_('The account code entered is not a valid code') . '. ' . _('This line cannot be added to the transaction') . '.<br />' . _('You can use the selection box to select the account you want'),'error');
+ $InputError = True;
+ } else if ($_POST['GLCode'] != '') {
+ $myrow = DB_fetch_row($result);
+ $GLActName = $myrow[1];
+ 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){
+
+ $_SESSION['Trans'][$TransID]->Add_To_GLAnalysis($_POST['Amount'],
+ $_POST['Narrative'],
+ $_POST['GLCode'],
+ $GLActName,
+ $_POST['GLTag'] );
+ unset($_POST['GLCode']);
+ unset($_POST['Amount']);
+ unset($_POST['Narrative']);
+ unset($_POST['AcctSelection']);
+ unset($_POST['GLTag']);
+ }
+}
+
+if (isset($_GET['Delete'])){
+ $_SESSION['Trans'][$TransID]->Remove_GLEntry($_GET['Delete']);
+}
+
+if (isset($_GET['Edit'])){
+ $_POST['GLCode'] = $_SESSION['Trans'][$TransID]->GLEntries[$_GET['Edit']]->GLCode;
+ $_POST['AcctSelection']= $_SESSION['Trans'][$TransID]->GLEntries[$_GET['Edit']]->GLCode;
+ $_POST['Amount'] = $_SESSION['Trans'][$TransID]->GLEntries[$_GET['Edit']]->Amount;
+ $_POST['GLTag'] = $_SESSION['Trans'][$TransID]->GLEntries[$_GET['Edit']]->Tag;
+ $_POST['Narrative'] = $_SESSION['Trans'][$TransID]->GLEntries[$_GET['Edit']]->Narrative;
+ $_SESSION['Trans'][$TransID]->Remove_GLEntry($_GET['Edit']);
+}
+
+/*Show all the selected GLEntries so far from the $_SESSION['Trans'][$TransID]->GLEntries array */
+if ($_SESSION['Trans'][$TransID]->Amount >= 0){ //its a receipt
+ echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/transactions.png" title="' . _('Bank Account Transaction Analysis') . '" alt="" />' . ' '
+ . _('Imported Bank Receipt of') . ' ' . $_SESSION['Trans'][$TransID]->Amount . ' ' . $_SESSION['Statement']->CurrCode . ' ' . _('dated:') . ' ' . $_SESSION['Trans'][$TransID]->ValueDate . '<br /> ' . $_SESSION['Trans'][$TransID]->Description;
+} else {
+ echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/transactions.png" title="' . _('Bank Account Transaction Analysis') . '" alt="" />' . ' '
+ . _('Imported Bank Payment of') . ' ' . $_SESSION['Trans'][$TransID]->Amount . ' ' . $_SESSION['Statement']->CurrCode . ' ' ._('dated:') . ' ' . $_SESSION['Trans'][$TransID]->ValueDate . '<br /> ' . $_SESSION['Trans'][$TransID]->Description;
+}
+
+/*Set up a form to allow input of new GL entries */
+echo '</p><form name="form1" action="' . $_SERVER['PHP_SELF'] . '" method="post">';
+echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
+
+echo '<input type="hidden" name="TransID" value=' . $TransID . ' />';
+
+echo '<div class="centre"><a href="' . $RootPath . '/ImportBankTrans.php" onclick="return confirm(\'' . _('If you have entered a GL analysis check that the sum of GL Entries agrees to the total bank transaction. If it does not then the bank transaction import will not be processed.') . '\');">' . _('Back to Main Import Screen') . '</a></div>';
+
+echo '<br /><table cellpadding="2" class="selection">';
+
+$AllowGLAnalysis = true;
+
+if ($_SESSION['Trans'][$TransID]->Amount<0){ //its a payment
+ 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 . "'
+ ORDER BY suppname",$db);
+ if ($_SESSION['Trans'][$TransID]->SupplierID ==''){
+ echo '<option selected value="">' . _('GL Payment') . '</option>';
+ } else {
+ echo '<option value="">' . _('GL Payment') . '</option>';
+ }
+ while ($myrow = DB_fetch_array($result)){
+ if ($myrow['supplierid']==$_SESSION['Trans'][$TransID]->SupplierID){
+ echo '<option selected value="' . $myrow['supplierid'] . '">' . $myrow['supplierid'] . ' - ' . $myrow['suppname'] . '</option>';
+ } else {
+ echo '<option value="' . $myrow['supplierid'] . '">' . $myrow['supplierid'] .' - ' . $myrow['suppname'] . '</option>';
+ }
+ }
+ echo '</select></td>
+ <td><input type="submit" name="Update" value="' . _('Update') . '" /></td>
+ </tr>';
+ if ($_SESSION['Trans'][$TransID]->SupplierID==''){
+ $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
+ WHERE currcode='" . $_SESSION['Statement']->CurrCode . "'
+ ORDER BY name",$db);
+ if ($_SESSION['Trans'][$TransID]->DebtorNo ==''){
+ echo '<option selected value="">' . _('GL Receipt') . '</option>';
+ } else {
+ echo '<option value="">' . _('GL Receipt') . '</option>';
+ }
+ while ($myrow = DB_fetch_array($result)){
+ if ($myrow['debtorno']==$_SESSION['Trans'][$TransID]->DebtorNo){
+ echo '<option selected value="' . $myrow['debtorno'] . '">' . $myrow['debtorno'] . ' - ' . $myrow['name'] . '</option>';
+ } else {
+ echo '<option value="' . $myrow['debtorno'] . '">' . $myrow['debtorno'] . ' - ' . $myrow['name'] . '</option>';
+ }
+ }
+ echo '</select></td>
+ <td><input type="submit" name="Update" value="' . _('Update') . '" /></td>
+ </tr>';
+ if ($_SESSION['Trans'][$TransID]->DebtorNo==''){
+ $AllowGLAnalysis = true;
+ } else {
+ $AllowGLAnalysis = false;
+ }
+ echo '</table>';
+}
+
+if ($AllowGLAnalysis==false){
+ /*clear any existing GLEntries */
+ foreach ($_SESSION['Trans'][$TransID]->GLEntries AS $GLAnalysisLine) {
+ $_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>' . _('Account') . '</th>
+ <th>' . _('Name') . '</th>
+ <th>' . _('Amount') . '<br />' . _('in') . ' ' . $_SESSION['Statement']->CurrCode . '</th>
+ <th>' . _('Narrative') . '</th>
+ <th>' . _('Tag') . '</th>
+ </tr>';
+ echo $TableHeader;
+ $TotalGLValue=0;
+ $i=0;
+
+ foreach ( $_SESSION['Trans'][$TransID]->GLEntries AS $EnteredGLCode){
+
+ echo '<tr>
+ <td>' . $EnteredGLCode->GLCode . '</td>
+ <td>' . $EnteredGLCode->GLAccountName . '</td>
+ <td class=number>' . locale_number_format($EnteredGLCode->Amount,$_SESSION['Statement']->CurrDecimalPlaces) . '</td>
+ <td>' . $EnteredGLCode->Narrative . '</td>
+ <td>' . $EnteredGLCode->Tag . '</td>
+ <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"><font size=4 color=blue>' . _('Total of GL Entries') . ':</font></td>
+ <td class="number"><font size=2 color=navy>' . locale_number_format($TotalGLValue,$_SESSION['Statement']->CurrDecimalPlaces) . '</font></td>
+ </tr>
+ <tr>
+ <td colspan="2" class="number"><font size=4 color=blue>' . _('Total Bank Transaction') . ':</font></td>
+ <td class="number">' . locale_number_format($_SESSION['Trans'][$TransID]->Amount,$_SESSION['Statement']->CurrDecimalPlaces) . '</font></td>
+ </tr>
+ <tr>';
+
+ if (($_SESSION['Trans'][$TransID]->Amount - $TotalGLValue)!=0) {
+ echo '<td colspan="2" class="number"><font size=4 color=blue>' . _('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>';
+ } else {
+ echo '<th colspan="5"><font size="4" color="green">' . _('Reconciled') . '</th>';
+ }
+ echo '</tr>
+ </table>';
+
+
+ echo '<br />
+ <table class="selection">';
+ if (!isset($_POST['GLCode'])) {
+ $_POST['GLCode']='';
+ }
+ echo '<tr>
+ <td>' . _('Account Code') . ':</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)) {
+ if ($myrow['accountcode'] == $_POST['AcctSelection']) {
+ echo '<option selected value="';
+ } else {
+ echo '<option value="';
+ }
+ echo $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' - ' . $myrow['accountname'] . '</option>';
+ }
+
+ echo '</select>
+ </td>
+ </tr>';
+ if (!isset($_POST['Amount'])) {
+ $_POST['Amount']=0;
+ }
+ echo '<tr>
+ <td>' . _('Amount') . ':</td>
+ <td><input type="text" class="number" name="Amount" size="12" maxlength="11" value="' . $_POST['Amount'] . '"></td>
+ </tr>';
+
+ if (!isset($_POST['Narrative'])) {
+ $_POST['Narrative']='';
+ }
+ echo '<tr>
+ <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)){
+ if (isset($_POST['tag']) and $_POST['tag']==$myrow['tagref']){
+ echo '<option selected value="' . $myrow['tagref'] . '">' . $myrow['tagref'].' - ' .$myrow['tagdescription'].'</option>';
+ } else {
+ echo '<option value="' . $myrow['tagref'] . '">' . $myrow['tagref'].' - ' .$myrow['tagdescription'] . '</option>';
+ }
+ }
+ echo '</select></td>
+ </tr>
+ </table><br />';
+
+ echo '<div class="centre"><input type="submit" name="AddGLCodeToTrans" value="' . _('Enter GL Line') . '"></div>';
+}
+echo '</form>';
+include('includes/footer.inc');
+?>
\ No newline at end of file
Added: trunk/MaintenanceReminders.php
===================================================================
--- trunk/MaintenanceReminders.php (rev 0)
+++ trunk/MaintenanceReminders.php 2013-06-25 08:53:03 UTC (rev 6036)
@@ -0,0 +1,84 @@
+<?php
+/* $Id: MaintenaceReminders.php 4551 2011-04-16 06:20:56Z daintree $*/
+//this script can be set to run from cron
+$AllowAnyone = true;
+include('includes/session.inc');
+include('includes/htmlMimeMail.php');
+
+$sql="SELECT description,
+ taskdescription,
+ ADDDATE(lastcompleted,frequencydays) AS duedate,
+ userresponsible,
+ email
+ FROM fixedassettasks
+ INNER JOIN fixedassets
+ ON fixedassettasks.assetid=fixedassets.assetid
+ INNER JOIN www_users
+ ON fixedassettasks.personresponsible=www_users.userid
+ WHERE ADDDATE(lastcompleted,frequencydays-10)> CURDATE()
+ ORDER BY userresponsible";
+
+$result = DB_query($sql,$db);
+$LastUserResponsible = '';
+while ($myrow = DB_fetch_array($result)){
+ if (!isset($('Mail' . $myrow['userresponsible'])) AND IsEmailAddress($myrow['email'])){
+ if ($LastUserResponsible!=''){
+ $('Mail' . $myrow['userresponsible'])->setText($MailText);
+ $SendResult = $('Mail' . $myrow['userresponsible'])->send(array($LastUserEmail));
+ $MailText = "You have the following maintenance task(s) falling due or over-due:\n";
+ }
+ $LastUserResponsible = $myrow['userresponsible'];
+ $LastUserEmail = $myrow['email'];
+ $('Mail' . $myrow['userresponsible']) = new htmlMimeMail();
+ $('Mail' . $myrow['userresponsible'])->setSubject('Maintenance Tasks Reminder');
+ $('Mail' . $myrow['userresponsible'])->setFrom('Do_not_reply <>');
+ }
+ $MailText .= "Asset: " . $myrow['description'] . "\nTask: " . $myrow['taskdescription'] . "\nDue: " . ConvertSQLDate($myrow['duedate']);
+ if (Date1GreaterThanDate2(ConvertSQLDate($myrow['duedate']),Date($_SESSION['DefaultDateFormat'])){
+ $MailText .= _('NB: THIS JOB IS OVERDUE');
+ }
+ $MailText . "\n\n";
+}
+if (DB_num_rows($result)>0){
+ $('Mail' . $LastUserResponsible)->setText($MailText);
+ $SendResult = $('Mail' . $LastUserResponsible)->send(array($('Mail' . $LastUserResponsible)));
+}
+
+/* Now do manager emails for overdue jobs */
+$sql="SELECT description,
+ taskdescription,
+ ADDDATE(lastcompleted,frequencydays) AS duedate,
+ realname,
+ manager
+ FROM fixedassettasks
+ INNER JOIN fixedassets
+ ON fixedassettasks.assetid=fixedassets.assetid
+ INNER JOIN www_users
+ ON fixedassettasks.personresponsible=www_users.userid
+ WHERE ADDDATE(lastcompleted,frequencydays)> CURDATE()
+ ORDER BY manager";
+
+$result = DB_query($sql,$db);
+$LastManager = '';
+while ($myrow = DB_fetch_array($result)){
+ if (!isset($('Mail' . $myrow['userresponsible'])){
+ if ($LastUserResponsible!=''){
+ $('Mail' . $myrow['userresponsible'])->setText($MailText);
+ $SendResult = $('Mail' . $myrow['manager'])->send(array($LastManagerEmail));
+ $MailText = "Your staff have failed to complete the following tasks by the due date:\n";
+ }
+ $LastManager = $myrow['manager'];
+ $LastManagerEmail = $myrow['email'];
+ $('Mail' . $myrow['manager']) = new htmlMimeMail();
+ $('Mail' . $myrow['manager'])->setSubject('Overdue Maintenance Tasks Reminder');
+ $('Mail' . $myrow['manager'])->setFrom('Do_not_reply <>');
+ }
+ $MailText .= "Asset: " . $myrow['description'] . "\nTask: " . $myrow['taskdescription'] . "\nDue: " . ConvertSQLDate($myrow['duedate']);
+ $MailText . "\n\n";
+}
+if (DB_num_rows($result)>0){
+ $('Mail' . $LastManager)->setText($MailText);
+ $SendResult = $('Mail' . $LastManager)->send(array($LastManagerEmail));
+}
+
+?>
\ No newline at end of file
Added: trunk/MaintenanceTasks.php
===================================================================
--- trunk/MaintenanceTasks.php (rev 0)
+++ trunk/MaintenanceTasks.php 2013-06-25 08:53:03 UTC (rev 6036)
@@ -0,0 +1,241 @@
+<?php
+
+/* $Id: MaintenanceTasks.php 5231 2012-04-07 18:10:09Z daitnree $*/
+
+include('includes/session.inc');
+
+$Title = _('Maintenance Tasks');
+include('includes/header.inc');
+
+echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/group_add.png" title="' . _('Search') . '" alt="" />' . ' ' . $Title.'</p><br />';
+
+
+if (isset($_POST['Submit'])) {
+ if (!is_numeric(filter_number_format($_POST['FrequencyDays'])) OR filter_number_format($_POST['FrequencyDays']) < 0){
+ prnMsg(_('The days before a task falls due is expected to be a postive'),'error');
+ } else {
+ $sql="INSERT INTO fixedassettasks (assetid,
+ taskdescription,
+ frequencydays,
+ userresponsible,
+ manager,
+ lastcompleted)
+ VALUES( '" . $_POST['AssetID'] . "',
+ '" . $_POST['TaskDescription'] . "',
+ '" . filter_number_format($_POST['FrequencyDays']) . "',
+ '" . $_POST['UserResponsible'] . "',
+ '" . $_POST['Manager'] . "',
+ '" . Date('Y-m-d') . "' )";
+ $ErrMsg = _('The authentication details cannot be inserted because');
+ $Result=DB_query($sql,$db,$ErrMsg);
+ unset($_POST['AssetID']);
+ unset($_POST['TaskDescription']);
+ unset($_POST['FrequencyDays']);
+ unset($_POST['Manager']);
+ unset($_POST['UserResponsible']);
+ }
+}
+
+if (isset($_POST['Update'])) {
+ if (!is_numeric(filter_number_format($_POST['FrequencyDays'])) OR filter_number_format($_POST['FrequencyDays']) < 0){
+ prnMsg(_('The days before a task falls due is expected to be a postive'),'error');
+ } else {
+ $sql="UPDATE fixedassettasks SET
+ assetid = '" . $_POST['AssetID'] . "',
+ taskdescription='".$_POST['TaskDescription'] ."',
+ frequencydays='" . filter_number_format($_POST['FrequencyDays'])."',
+ userresponsible='" . $_POST['UserResponsible'] . "',
+ manager='" . $_POST['Manager'] . "'
+ WHERE taskid='".$_POST['TaskID']."'";
+
+ $ErrMsg = _('The task details cannot be updated because');
+ $Result=DB_query($sql,$db,$ErrMsg);
+ unset($_POST['AssetID']);
+ unset($_POST['TaskDescription']);
+ unset($_POST['FrequencyDays']);
+ unset($_POST['Manager']);
+ unset($_POST['UserResponsible']);
+ }
+}
+
+if (isset($_GET['Delete'])) {
+ $sql="DELETE FROM fixedassettasks
+ WHERE taskid='".$_GET['TaskID']."'";
+
+ $ErrMsg = _('The maintenance task cannot be deleted because');
+ $Result=DB_query($sql,$db,$ErrMsg);
+}
+
+$sql="SELECT taskid,
+ fixedassettasks.assetid,
+ description,
+ taskdescription,
+ frequencydays,
+ lastcompleted,
+ userresponsible,
+ realname,
+ manager
+ FROM fixedassettasks
+ INNER JOIN fixedassets
+ ON fixedassettasks.assetid=fixedassets.assetid
+ INNER JOIN www_users
+ ON fixedassettasks.userresponsible=www_users.userid";
+
+$ErrMsg = _('The maintenance task details cannot be retrieved because');
+$Result=DB_query($sql,$db,$ErrMsg);
+
+echo '<table class="selection">
+ <tr>
+ <th>' . _('Task ID') . '</th>
+ <th>' . _('Asset') . '</th>
+ <th>' . _('Description') . '</th>
+ <th>' . _('Last Completed') . '</th>
+ <th>' . _('Person') . '</th>
+ <th>' . _('Manager') . '</th>
+ </tr>';
+
+while ($myrow=DB_fetch_array($Result)) {
+
+ if ($myrow['manager']!=''){
+ $ManagerResult = DB_query("SELECT realname FROM www_users WHERE userid='" . $myrow['manager'] . "'",$db);
+ $ManagerRow = DB_fetch_array($ManagerResult);
+ $ManagerName = $ManagerRow['realname'];
+ } else {
+ $ManagerName = _('No Manager Set');
+ }
+
+ echo '<tr>
+ <td>' . $myrow['taskid'] . '</td>
+ <td>' . $myrow['description'] . '</td>
+ <td>' . $myrow['taskdescription'] . '</td>
+ <td>' . ConvertSQLDate($myrow['lastcompleted']) . '</td>
+ <td>' . $myrow['realname'] . '</td>
+ <td>' . $ManagerName . '</td>
+ <td><a href="'.$RootPath.'/MaintenanceTasks.php?Edit=Yes&TaskID=' . $myrow['taskid'] .'">'._('Edit').'</a></td>
+ <td><a href="'.$RootPath.'/MaintenanceTasks.php?Delete=Yes&TaskID=' . $myrow['taskid'] .'" onclick="return confirm(\'' . _('Are you sure you wish to delete this maintenance task?') . '\');">'._('Delete').'</a></td>
+ </tr>';
+}
+
+echo '</table><br /><br />';
+
+
+echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post" id="form1">';
+echo '<div>';
+echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
+echo '<table class="selection">';
+
+if (isset($_GET['Edit'])) {
+ echo '<tr>
+ <td>'._('Task ID').'</td><td>'.$_GET['TaskID'].'</td>
+ </tr>';
+ echo '<input type="hidden" name="TaskID" value="'.$_GET['TaskID'].'" />';
+ $sql="SELECT assetid,
+ taskdescription,
+ frequencydays,
+ lastcompleted,
+ userresponsible,
+ manager
+ FROM fixedassettasks
+ WHERE taskid='".$_GET['TaskID']."'";
+ $ErrMsg = _('The maintenance task details cannot be retrieved because');
+ $result=DB_query($sql,$db,$ErrMsg);
+ $myrow=DB_fetch_array($result);
+ $_POST['TaskDescription'] = $myrow['taskdescription'];
+ $_POST['FrequencyDays'] = $myrow['frequencydays'];
+ $_POST['UserResponsible'] = $myrow['userresponsible'];
+ $_POST['Manager'] = $myrow['manager'];
+ $_POST['AssetID'] = $myrow['assetid'];
+}
+
+if (!isset($_POST['TaskDescription'])){
+ $_POST['TaskDescription']='';
+}
+if (!isset($_POST['FrequencyDays'])){
+ $_POST['FrequencyDays']='';
+}
+if (!isset($_POST['UserResponsible'])){
+ $_POST['UserResponsible']= '';
+}
+if (!isset($_POST['Manager'])){
+ $_POST['Manager']='';
+}
+if (!isset($_POST['AssetID'])){
+ $_POST['AssetID']='';
+}
+
+echo '<tr>
+ <td>'._('Asset to Maintain').':</td>
+ <td><select name="AssetID">';
+$AssetSQL="SELECT assetid, description FROM fixedassets";
+$AssetResult=DB_query($AssetSQL,$db);
+while ($myrow=DB_fetch_array($AssetResult)) {
+ if ($myrow['assetid']==$_POST['AssetID']) {
+ echo '<option selected="selected" value="'.$myrow['assetid'].'">'.$myrow['assetid'] . ' - ' . $myrow['description'] .'</option>';
+ } else {
+ echo '<option value="'.$myrow['assetid'].'">'.$myrow['assetid'] . ' - ' . $myrow['description'] .'</option>';
+ }
+}
+echo '</select></td>
+ </tr>';
+
+echo '<tr>
+ <td>'._('Task Description').':</td>
+ <td><textarea name="TaskDescription" cols="40" rows="3">' . $_POST['TaskDescription'] . '</textarea></td>
+ </tr>';
+
+echo '<tr>
+ <td>'._('Days Before Task Due').':</td>
+ <td><input type="text" class="number" name="FrequencyDays" size="5" maxlength="5" value="' . $_POST['FrequencyDays'] . '" /></td>
+ </tr>';
+
+echo '<tr>
+ <td>'._('Responsible').':</td>
+ <td><select name="UserResponsible">';
+$UserSQL="SELECT userid FROM www_users";
+$UserResult=DB_query($UserSQL,$db);
+while ($myrow=DB_fetch_array($UserResult)) {
+ if ($myrow['userid']==$_POST['UserResponsible']) {
+ echo '<option selected="selected" value="'.$myrow['userid'].'">'.$myrow['userid'].'</option>';
+ } else {
+ echo '<option value="'.$myrow['userid'].'">'.$myrow['userid'].'</option>';
+ }
+}
+echo '</select></td>
+ </tr>';
+
+echo '<tr>
+ <td>'._('Manager').':</td>
+ <td><select name="Manager">';
+if ($_POST['Manager']==''){
+ echo '<option selected="selected" value="">' . _('No Manager') . '</option>';
+} else {
+ echo '<option value="">' . _('No Manager') . '</option>';
+}
+$ManagerSQL="SELECT userid FROM www_users";
+$ManagerResult=DB_query($UserSQL,$db);
+while ($myrow=DB_fetch_array($ManagerResult)) {
+ if ($myrow['userid']==$_POST['Manager']) {
+ echo '<option selected="selected" value="'.$myrow['userid'].'">'.$myrow['userid'].'</option>';
+ } else {
+ echo '<option value="'.$myrow['userid'].'">'.$myrow['userid'].'</option>';
+ }
+}
+echo '</select></td>
+ </tr>
+ </table>';
+
+if (isset($_GET['Edit'])) {
+ echo '<br />
+ <div class="centre">
+ <input type="submit" name="Update" value="'._('Update Task').'" />
+ </div>';
+} else {
+ echo '<br />
+ <div class="centre">
+ <input type="submit" name="Submit" value="'._('Enter New Task').'" />
+ </div>';
+}
+echo '</div>
+ </form>';
+include('includes/footer.inc');
+?>
\ No newline at end of file
Added: trunk/MaintenanceUserSchedule.php
===================================================================
--- trunk/MaintenanceUserSchedule.php (rev 0)
+++ trunk/MaintenanceUserSchedule.php 2013-06-25 08:53:03 UTC (rev 6036)
@@ -0,0 +1,77 @@
+<?php
+
+/* $Id: MaintenanceTasks.php 5231 2012-04-07 18:10:09Z daitnree $*/
+
+include('includes/session.inc');
+
+$Title = _('My Maintenance Jobs');
+include('includes/header.inc');
+
+echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/group_add.png" title="' . _('Search') . '" alt="" />' . ' ' . $Title.'</p><br />';
+
+
+if (isset($_GET['Complete'])) {
+ $result = DB_query("UPDATE fixedassettasks SET lastcompleted='" . Date('Y-m-d') . "' WHERE taskid='" . $_GET['TaskID'] . "'",$db);
+}
+
+
+$sql="SELECT taskid,
+ fixedassettasks.assetid,
+ description,
+ taskdescription,
+ frequencydays,
+ lastcompleted,
+ ADDDATE(lastcompleted,frequencydays) AS duedate,
+ userresponsible,
+ realname,
+ manager
+ FROM fixedassettasks
+ INNER JOIN fixedassets
+ ON fixedassettasks.assetid=fixedassets.assetid
+ INNER JOIN www_users
+ ON fixedassettasks.userresponsible=www_users.userid
+ WHERE userresponsible='" . $_SESSION['UserID'] . "'
+ OR manager = '" . $_SESSION['UserID'] . "'
+ ORDER BY ADDDATE(lastcompleted,frequencydays) DESC";
+
+$ErrMsg = _('The maintenance schedule cannot be retrieved because');
+$Result=DB_query($sql,$db,$ErrMsg);
+
+echo '<table class="selection">
+ <tr>
+ <th>' . _('Task ID') . '</th>
+ <th>' . _('Asset') . '</th>
+ <th>' . _('Description') . '</th>
+ <th>' . _('Last Completed') . '</th>
+ <th>' . _('Due By') . '</td>
+ <th>' . _('Person') . '</th>
+ <th>' . _('Manager') . '</th>
+ <th>' . _('Now Complete') . '</th>
+ </tr>';
+
+while ($myrow=DB_fetch_array($Result)) {
+
+ if ($myrow['manager']!=''){
+ $ManagerResult = DB_query("SELECT realname FROM www_users WHERE userid='" . $myrow['manager'] . "'",$db);
+ $ManagerRow = DB_fetch_array($ManagerResult);
+ $ManagerName = $ManagerRow['realname'];
+ } else {
+ $ManagerName = _('No Manager Set');
+ }
+
+ echo '<tr>
+ <td>' . $myrow['taskid'] . '</td>
+ <td>' . $myrow['description'] . '</td>
+ <td>' . $myrow['taskdescription'] . '</td>
+ <td>' . ConvertSQLDate($myrow['lastcompleted']) . '</td>
+ <td>' . ConvertSQLDate($myrow['duedate']) . '</td>
+ <td>' . $myrow['realname'] . '</td>
+ <td>' . $ManagerName . '</td>
+ <td><a href="'.$RootPath.'/MaintenanceUserSchedule.php?Complete=Yes&TaskID=' . $myrow['taskid'] .'" onclick="return confirm(\'' . _('Are you sure you wish to mark this maintenance task as completed?') . '\');">'._('Mark Completed').'</a></td>
+ </tr>';
+}
+
+echo '</table><br /><br />';
+
+include('includes/footer.inc');
+?>
\ No newline at end of file
Added: trunk/includes/DefineImportBankTransClass.php
===================================================================
--- trunk/includes/DefineImportBankTransClass.php (rev 0)
+++ trunk/includes/DefineImportBankTransClass.php 2013-06-25 08:53:03 UTC (rev 6036)
@@ -0,0 +1,100 @@
+<?php
+/* $Id: DefineImportBankTransClass.php 3242 2009-12-16 22:06:53Z tim_schofield $*/
+
+class BankStatement {
+ var $ReportCreated;
+ var $AccountNumber;
+ var $AccountName;
+ var $StatementNumber;
+ var $AccountOwner;
+ var $CurrCode;
+ var $OpeningDate;
+ var $OpeningBalance;
+ var $ClosingDate;
+ var $ClosingBalance;
+ var $BankGLAccount;
+ var $BankAccountName;
+ var $CurrDecimalPlaces;
+ var $ExchangeRate;
+ var $Trans;
+
+ function BankStatement () {
+ $this->ReportCreated = '';
+ $this->AccountNumber = '';
+ $this->AccountName = '';
+ $this->StatementNumber = '';
+ $this->AccountOwner = '';
+ $this->CurrCode = '';
+ $this->ClosingBalance = 0;
+ $this->OpeningBalance = 0;
+ $this->BankGLAccount = '';
+ $this->BankAccountName = '';
+ $this->CurrDecimalPlaces = 2;
+ $this->ExchangeRate = 1;
+ }
+}
+
+class BankTrans {
+ var $ValueDate;
+ var $Amount;
+ var $Code;
+ var $Description;
+ var $BankTransID;
+ var $GLEntries;
+ var $DebtorNo;
+ var $SupplierID;
+ var $GLItemID;
+ var $GLTotal;
+
+ function BankTrans ($ValueDate, $Amount) {
+ $this->ValueDate = $ValueDate;
+ $this->Amount = $Amount;
+ $this->GLEntries = array();
+ $this->DebtorNo = '';
+ $this->SupplierID = '';
+ $this->GLItemID = 0;
+ $this->GLTotal = 0;
+ $this->BankTransID = 0;
+ }
+
+ function Add_To_GLAnalysis($Amount, $Narrative, $GLCode, $GLAccountName, $Tag){
+ if (isset($GLCode) AND $Amount!=0){
+ $this->GLEntries[$this->GLItemID] = new GLAnalysis($Amount, $Narrative, $this->GLItemID, $GLCode, $GLAccountName, $Tag);
+ $this->GLItemID++;
+ $this->GLTotal += $Amount;
+
+ Return 1;
+ }
+ Return 0;
+ }
+
+ function Remove_GLEntry($GL_ID){
+ $this->GLTotal -= $this->GLEntries[$GL_ID]->Amount;
+ unset($this->GLEntries[$GL_ID]);
+ $this->GLItemCounter--;
+ }
+}
+
+
+Class GLAnalysis {
+
+ Var $Amount;
+ Var $Narrative;
+ Var $GLCode;
+ var $GLAccountName;
+ Var $ID;
+ var $Tag;
+
+ function GLAnalysis ($Amount, $Narrative, $ID, $GLCode, $GLAccountName, $Tag){
+
+/* Constructor function to add a new JournalGLAnalysis object with passed params */
+ $this->Amount =$Amount;
+ $this->Narrative = $Narrative;
+ $this->GLCode = $GLCode;
+ $this->GLAccountName = $GLAccountName;
+ $this->ID = $ID;
+ $this->Tag = $Tag;...
[truncated message content] |
