BankMatching.php | searchcode

/chronique/BankMatching.php

http://chronique.googlecode.com/
PHP | 347 lines | 295 code | 44 blank | 8 comment | 30 complexity | 28f442eea5190d80b2a1a8453acd6e73 MD5 | raw file
Possible License(s): AGPL-1.0, GPL-2.0, LGPL-2.1, MPL-2.0-no-copyleft-exception

<?php

/* $Id: BankMatching.php 4735 2011-10-29 05:59:53Z daintree $*/

include('includes/session.inc');
$title = _('Bank Account Matching');
include('includes/header.inc');

if ((isset($_GET['Type']) 
		AND $_GET['Type']=='Receipts') 
	OR	(isset($_POST['Type']) 
		AND $_POST['Type']=='Receipts')){

	$Type = 'Receipts';
	$TypeName =_('Receipts');
	echo '<p class="page_title_text">
			<img src="'.$rootpath.'/css/'.$theme.'/images/money_add.png" title="' . _('Bank Matching') . '" alt="" />' . ' ' . _('Bank Account Matching - Receipts') . '
		</p>';

} elseif ((isset($_GET['Type']) AND $_GET['Type']=='Payments') OR
			(isset($_POST['Type']) and $_POST['Type']=='Payments')) {

	$Type = 'Payments';
	$TypeName =_('Payments');
	echo '<p class="page_title_text"><img src="'.$rootpath.'/css/'.$theme.'/images/money_delete.png" title="' . _('Bank Matching') . '" alt="" />' . ' ' . _('Bank Account Matching - Payments') . '</p>';

} else {

	prnMsg(_('This page must be called with a bank transaction type') . '. ' . _('It should not be called directly'),'error');
	include ('includes/footer.inc');
	exit;
}

if (isset($_GET['Account'])) {
	$_POST['BankAccount']=$_GET['Account'];
	$_POST['ShowTransactions']=true;
	$_POST['Ostg_or_All']='Ostg';
	$_POST['First20_or_All']='All';
}

if (isset($_POST['Update']) AND $_POST['RowCounter']>1){
	for ($Counter=1;$Counter <= $_POST['RowCounter']; $Counter++){
		if (isset($_POST['Clear_' . $Counter]) AND $_POST['Clear_' . $Counter]==True){
			/*Get amount to be cleared */
			$sql = "SELECT amount,
						exrate
					FROM banktrans
					WHERE banktransid='" . $_POST['BankTrans_' . $Counter]."'";
			$ErrMsg =  _('Could not retrieve transaction information');
			$result = DB_query($sql,$db,$ErrMsg);
			$myrow=DB_fetch_array($result);
			$AmountCleared = round($myrow[0] / $myrow[1],2);
			/*Update the banktrans recoord to match it off */
			$sql = "UPDATE banktrans SET amountcleared= ". $AmountCleared .
					" WHERE banktransid='" . $_POST['BankTrans_' . $Counter] . "'";
			$ErrMsg =  _('Could not match off this payment because');
			$result = DB_query($sql,$db,$ErrMsg);

		} elseif ((isset($_POST['AmtClear_' . $Counter]) 
					AND filter_number_format($_POST['AmtClear_' . $Counter])<0 AND $Type=='Payments') 
				OR	($Type=='Receipts' AND isset($_POST['AmtClear_' . $Counter]) AND filter_number_format($_POST['AmtClear_' . $Counter])>0)){
			/*if the amount entered was numeric and negative for a payment or positive for a receipt */
			
			$sql = "UPDATE banktrans SET amountcleared=" .  filter_number_format($_POST['AmtClear_' . $Counter]) . "
					 WHERE banktransid='" . $_POST['BankTrans_' . $Counter]."'";

			$ErrMsg = _('Could not update the amount matched off this bank transaction because');
			$result = DB_query($sql,$db,$ErrMsg);

		} elseif (isset($_POST['Unclear_' . $Counter]) 
					AND $_POST['Unclear_' . $Counter]==True){
						
			$sql = "UPDATE banktrans SET amountcleared = 0
					 WHERE banktransid='" . $_POST['BankTrans_' . $Counter]."'";
			$ErrMsg =  _('Could not unclear this bank transaction because');
			$result = DB_query($sql,$db,$ErrMsg);
		}
	}
	/*Show the updated position with the same criteria as previously entered*/
	$_POST['ShowTransactions'] = True;
}

echo '<div class="page_help_text">' . _('Use this screen to match webERP Receipts and Payments to your Bank Statement.  Check your bank statement and click the check-box when you find the matching transaction.') . '</div><br />';

echo '<form action="'. htmlspecialchars($_SERVER['PHP_SELF']) . '" method=post>';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';

echo '<input type="hidden" name="Type" value="' . $Type . '" />';

echo '<table class="selection">
		<tr>
			<td align="left">' . _('Bank Account') . ':</td>
			<td colspan=3><select tabindex="1" name="BankAccount">';

$sql = "SELECT accountcode, bankaccountname FROM bankaccounts";
$resultBankActs = DB_query($sql,$db);
while ($myrow=DB_fetch_array($resultBankActs)){
	if (isset($_POST['BankAccount']) and $myrow['accountcode']==$_POST['BankAccount']){
		echo '<option selected value="' . $myrow['accountcode'] . '">' . $myrow['bankaccountname'] . '</option>';
	} else {
		echo '<option value="' . $myrow['accountcode'] . '">' . $myrow['bankaccountname'] . '</option>';
	}
}

echo '</select></td>
	</tr>';

if (!isset($_POST['BeforeDate']) OR !Is_Date($_POST['BeforeDate'])){
	$_POST['BeforeDate'] = Date($_SESSION['DefaultDateFormat']);
}
if (!isset($_POST['AfterDate']) OR !Is_Date($_POST['AfterDate'])){
	$_POST['AfterDate'] = Date($_SESSION['DefaultDateFormat'], Mktime(0,0,0,Date('m')-3,Date('d'),Date('y')));
}

// Change to allow input of FROM DATE and then TO DATE, instead of previous back-to-front method, add datepicker
echo '<tr>
		<td>' . _('Show') . ' ' . $TypeName . ' ' . _('from') . ':</td>
		<td><input tabindex="3" type="text" name="AfterDate" class="date" alt="'.$_SESSION['DefaultDateFormat'].'" size="12" maxlength="10" onChange="isDate(this, this.value, '."'".$_SESSION['DefaultDateFormat']."'".')" value="' . $_POST['AfterDate'] . '" /></td></tr>';

echo '<td>' . _('to') . ':</td>
	<td><input tabindex="2" type="text" name="BeforeDate" class="date" alt="'.$_SESSION['DefaultDateFormat'].'" size="12" maxlength="10" onChange="isDate(this, this.value, '."'".$_SESSION['DefaultDateFormat']."'".')" value="' . $_POST['BeforeDate'] . '" /></td>';
echo '<tr>
		<td colspan="3">' . _('Choose outstanding') . ' ' . $TypeName . ' ' . _('only or all') . ' ' . $TypeName . ' ' . _('in the date range') . ':</td>
		<td><select tabindex="4" name="Ostg_or_All">';

if ($_POST['Ostg_or_All']=='All'){
	echo '<option selected value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range') . '</option>';
	echo '<option value="Ostdg">' . _('Show unmatched') . ' ' . $TypeName . ' ' . _('only') . '</option>';
} else {
	echo '<option value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range') . '</option>';
	echo '<option selected value="Ostdg">' . _('Show unmatched') . ' ' . $TypeName . ' ' . _('only') . '</option>';
}
echo '</select></td>
	</tr>';

echo '<tr>
	<td colspan="3">' . _('Choose to display only the first 20 matching') . ' ' . $TypeName . ' ' .
	_('or all') . ' ' . $TypeName . ' ' . _('meeting the criteria') . ':</td>
	<td><select tabindex="5" name="First20_or_All">';
if ($_POST['First20_or_All']=='All'){
	echo '<option selected value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range') . '</option>';
	echo '<option value="First20">' . _('Show only the first 20') . ' ' . $TypeName . '</option>';
} else {
	echo '<option value="All">' . _('Show all') . ' ' . $TypeName . ' ' . _('in the date range') . '</option>';
	echo '<option selected value="First20">' . _('Show only the first 20') . ' ' . $TypeName . '</option>';
}
echo '</select></td>
	</tr>';


echo '</table>
	<br />
	<div class="centre">
		<input tabindex="6" type="submit" name="ShowTransactions" value="' . _('Show selected') . ' ' . $TypeName . '" />
		<p>
		<a href="' . $rootpath . '/BankReconciliation.php">' . _('Show reconciliation') . '</a>
		</p>
	</div>
	<hr />';

$InputError=0;
if (!Is_Date($_POST['BeforeDate'])){
	$InputError =1;
	prnMsg(_('The date entered for the field to show') . ' ' . $TypeName . ' ' . _('before') . ', ' .
		_('is not entered in a recognised date format') . '. ' . _('Entry is expected in the format') . ' ' .
		$_SESSION['DefaultDateFormat'],'error');
}
if (!Is_Date($_POST['AfterDate'])){
	$InputError =1;
	prnMsg( _('The date entered for the field to show') . ' ' . $Type . ' ' . _('after') . ', ' .
		_('is not entered in a recognised date format') . '. ' . _('Entry is expected in the format') . ' ' .
		$_SESSION['DefaultDateFormat'],'error');
}

if ($InputError !=1 
	AND isset($_POST['BankAccount']) 
	AND $_POST['BankAccount']!='' 
	AND isset($_POST['ShowTransactions'])){

	$SQLBeforeDate = FormatDateForSQL($_POST['BeforeDate']);
	$SQLAfterDate = FormatDateForSQL($_POST['AfterDate']);

	$BankResult = DB_query("SELECT decimalplaces,
									currcode
							FROM bankaccounts INNER JOIN currencies
							ON bankaccounts.currcode=currencies.currabrev
							WHERE accountcode='" . $_POST['BankAccount'] . "'",
							$db);
	$BankRow = DB_fetch_array($BankResult);
	$CurrDecimalPlaces = $BankRow['decimalplaces'];
	$CurrCode = $BankRow['currcode'];

	if ($_POST['Ostg_or_All']=='All'){
		if ($Type=='Payments'){
			$sql = "SELECT banktransid,
							ref,
							amountcleared,
							transdate,
							amount/exrate as amt,
							banktranstype
					FROM banktrans
					WHERE amount < 0
					AND transdate >= '". $SQLAfterDate . "'
					AND transdate <= '" . $SQLBeforeDate . "'
					AND bankact='" .$_POST['BankAccount'] . "'
					ORDER BY transdate";

		} else { /* Type must == Receipts */
			$sql = "SELECT banktransid,
							ref,
							amountcleared,
							transdate,
							amount/exrate as amt,
							banktranstype
						FROM banktrans
						WHERE amount >0
						AND transdate >= '". $SQLAfterDate . "'
						AND transdate <= '" . $SQLBeforeDate . "'
						AND bankact='" .$_POST['BankAccount'] . "'
						ORDER BY transdate";
		}
	} else { /*it must be only the outstanding bank trans required */
		if ($Type=='Payments'){
			$sql = "SELECT banktransid,
							ref,
							amountcleared,
							transdate,
							amount/exrate as amt,
							banktranstype
						FROM banktrans
						WHERE amount <0
						AND transdate >= '". $SQLAfterDate . "'
						AND transdate <= '" . $SQLBeforeDate . "'
						AND bankact=" .$_POST['BankAccount'] . "
						AND  ABS(amountcleared - (amount / exrate)) > 0.009
						ORDER BY transdate";
		} else { /* Type must == Receipts */
			$sql = "SELECT banktransid,
							ref,
							amountcleared,
							transdate,
							amount/exrate as amt,
							banktranstype
						FROM banktrans
						WHERE amount >0
						AND transdate >= '". $SQLAfterDate . "'
						AND transdate <= '" . $SQLBeforeDate . "'
						AND bankact='" .$_POST['BankAccount'] . "'
						AND  ABS(amountcleared - (amount / exrate)) > 0.009
						ORDER BY transdate";
		}
	}
	if ($_POST['First20_or_All']!='All'){
		$sql = $sql . " LIMIT 20";
	}

	$ErrMsg = _('The payments with the selected criteria could not be retrieved because');
	$PaymentsResult = DB_query($sql, $db, $ErrMsg);

	$TableHeader = '<tr>
						<th>'. _('Ref'). '</th>
						<th>' . $TypeName . '</th>
						<th>' . _('Date') . '</th>
						<th>' . _('Amount') . ' ' . $CurrCode .'</th>
						<th>' . _('Outstanding') . ' ' . $CurrCode . '</th>
						<th colspan="3">' . _('Clear') . ' / ' . _('Unclear') . '</th>
					</tr>';
	echo '<table class="selection">' . $TableHeader;


	$j = 1;  //page length counter
	$k=0; //row colour counter
	$i = 1; //no of rows counter

	while ($myrow=DB_fetch_array($PaymentsResult)) {

		$DisplayTranDate = ConvertSQLDate($myrow['transdate']);
		$Outstanding = $myrow['amt']- $myrow['amountcleared'];
		if (ABS($Outstanding)<0.009){ /*the payment is cleared dont show the check box*/

			printf('<tr bgcolor="#CCCEEE">
					<td>%s</td>
					<td>%s</td>
					<td>%s</td>
					<td class="number">%s</td>
					<td class="number">%s</td>
					<td colspan="2">%s</td>
					<td><input type="checkbox" name="Unclear_%s" /><input type="hidden" name="BankTrans_%s" value="%s" /></td>
					</tr>',
					$myrow['ref'],
					$myrow['banktranstype'],
					$DisplayTranDate,
					locale_number_format($myrow['amt'],$CurrDecimalPlaces),
					locale_number_format($Outstanding,$CurrDecimalPlaces),
					_('Unclear'),
					$i,
					$i,
					$myrow['banktransid']);

		} else{
			if ($k==1){
				echo '<tr class="EvenTableRows">';
				$k=0;
			} else {
				echo '<tr class="OddTableRows">';
				$k=1;
			}
		printf('<td>%s</td>
				<td>%s</td>
				<td>%s</td>
				<td class="number">%s</td>
				<td class="number">%s</td>
				<td><input type="checkbox" name="Clear_%s" /><input type="hidden" name="BankTrans_%s" value="%s" /></td>
				<td colspan="2"><input type="text" maxlength="15" size="15" class="number" name="AmtClear_%s" /></td>
				</tr>',
				$myrow['ref'],
				$myrow['banktranstype'],
				$DisplayTranDate,
				locale_number_format($myrow['amt'],$CurrDecimalPlaces),
				locale_number_format($Outstanding,$CurrDecimalPlaces),
				$i,
				$i,
				$myrow['banktransid'],
				$i
			);
		}

		$j++;
		If ($j == 12){
			$j=1;
			echo $TableHeader;
		}
	//end of page full new headings if
		$i++;
	}
	//end of while loop

	echo '</table>
			<br />
			<div class="centre">
				<input type="hidden" name="RowCounter" value="' . $i . '" />
				<input type="submit" name="Update" value="' . _('Update Matching') . '" />
			</div>';
}
echo '</form>';
include('includes/footer.inc');
?>