function update($_POST)
{
# Get Vars ( banked[] )
extract($_POST);
# check if anything is selected
if (!isset($banked)) {
$err = "<li class='err'> Please Select at least one entry to update.";
return cashbook($_POST, $err);
}
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
/* - End Hooks - */
$refnum = getrefnum();
/*refnum*/
db_conn('core');
$rid = pglib_lastid("save_bank_recon", "id");
$rid++;
# Record all trans
foreach ($banked as $key => $cashid) {
# Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve details from database.", SELF);
$cash = pg_fetch_array($cashRslt);
# Set record as banked
db_connect();
$sql = "UPDATE cashbook SET banked = 'yes',rid='{$rid}' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to set bank deposit as banked in Cubit.", SELF);
}
// Connect to database
db_connect();
$sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND banked = 'no' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank transactions from database.", SELF);
$sql = "SELECT * FROM bankacct WHERE bankid= '{$bankid}' AND div = '" . USER_DIV . "'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
$cur = CUR;
$amtd = "amount";
if ($bank['btype'] == 'int') {
$currs = getSymbol($bank['fcid']);
$cur = $currs['symbol'];
$amtd = "famount";
}
$tot = 0;
$totr = 0;
$totp = 0;
$recpts = "";
$paymnts = "";
while ($cash = pg_fetch_array($cashRslt)) {
if ($cash['trantype'] == "deposit") {
$recpts .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$cash['date']}</td>\n\t\t\t\t\t<td>{$cash['descript']}</td>\n\t\t\t\t\t<td align='right'>{$cur} {$cash[$amtd]}</td>\n\t\t\t\t</tr>";
$totr += $cash[$amtd];
} else {
$paymnts .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$cash['date']}</td>\n\t\t\t\t\t<td>{$cash['descript']}</td>\n\t\t\t\t\t<td align='right'>{$cur} {$cash[$amtd]}</td>\n\t\t\t\t</tr>";
$totp += $cash[$amtd];
}
$tot += $cash[$amtd];
}
$reconbal = sprint($cbal + ($totr - $totp));
# get hook account number
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
# check if link exists
if (pg_numrows($rslt) < 1) {
return "<li class='err'> ERROR : The bank account that you selected doesn't appear to have an account linked to it.";
}
$banklnk = pg_fetch_array($rslt);
# Get bank balance
$sql = "SELECT (debit - credit) as bal FROM trial_bal WHERE period='12' AND accid = '{$banklnk['accnum']}' AND div = '" . USER_DIV . "'";
$brslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
$bal = pg_fetch_array($brslt);
if ($bank['btype'] == 'int') {
$bal['bal'] = $bank['fbalance'];
}
$diff = sprint($reconbal - $bal['bal']);
$derr = "";
if ($diff != 0) {
$derr = "\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><b class='err'>Bank statement and computer balance not balancing by</b></td>\n\t\t\t\t<td align='right'>{$cur} {$diff}</td>\n\t\t\t</tr>";
}
$totp = sprint($totp);
// Layout
$update = "\n\t\t<center>\n\t\t<h3>Bank Reconciliation Output</h3>\n\t\t<form action='" . SELF . "' method='POST' name='form1'>\n\t\t\t<input type='hidden' name='key' value='save'>\n\t\t\t<input type='hidden' name='bankid' value='{$bankid}'>\n\t\t<table cellpadding='2' cellspacing='0' border=0 bordercolor='#000000' width='80%'>\n\t\t\t<tr>\n\t\t\t\t<td colspan='10'>\n\t\t\t\t\t<table cellpadding='2' cellspacing='0' border=0 bordercolor='#000000' width=100%>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td><b>Bank Account : </b>{$bank['accname']}</td>\n\t\t\t\t\t\t\t<td></td>\n\t\t\t\t\t\t\t<td align='right'><b>Prepared By : </b>" . USER_NAME . "</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td><b>Closing Balance As per Bank Statement : </b>{$cur} {$cbal}</td>\n\t\t\t\t\t\t\t<td></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td><b>Plus Outstanding Receipts :</b></td>\n\t\t\t</tr>\n\t\t\t<!--<tr><th>Date</th><th>Reference</th><th>Amount</th></tr>-->\n\t\t\t{$recpts}\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td align='right'>____________</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' align='right'><b>Sub Total</b></td>\n\t\t\t\t<td align='right'>{$cur} {$totr}</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td><b>Less Outstanding Payments :</b></td>\n\t\t\t</tr>\n\t\t\t<!--<tr><th>Date</th><th>Reference</th><th>Amount</th></tr>-->\n\t\t\t{$paymnts}\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td align='right'>____________</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' align='right'><b>Sub Total</b></td>\n\t\t\t\t<td align='right'>{$cur} {$totp}</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br><td></tr>\n\t\t\t{$derr}\n\t\t\t<tr><td><br><td></tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td align='right'>____________</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><b>Reconciled Bank Balance</b></td>\n\t\t\t\t<td align='right'>{$cur} {$reconbal}</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><b>Computer Bank Balance</b></td>\n\t\t\t\t<td align='right'>{$cur} {$bal['bal']}</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td align='right'>____________</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><b>Diff</b></td>\n\t\t\t\t<td align='right'>{$cur} {$diff}</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td align='right'>____________</td>\n\t\t\t</tr>";
$upcode = base64_encode($update);
$button = "</table></form>";
core_connect();
$gendate = date("Y-m-d");
$sql = "\n\t\tINSERT INTO save_bank_recon (\n\t\t\tbankid, gendate, recon, div\n\t\t) VALUES (\n\t\t\t'{$bankid}', '{$gendate}', '{$upcode}', '" . USER_DIV . "'\n\t\t)";
$saveRslt = db_exec($sql) or errDie("Unable to save bank recon to database", SELF);
$update .= $button;
$OUTPUT = $update;
require "../tmpl-print.php";
}
#
#
#
#
#
#
#
#
#
require "../settings.php";
// Get global variables & functions
# decide what to do
if (isset($_POST["key"])) {
switch ($_POST["key"]) {
case "out":
$OUTPUT = cashbook($_POST['bankid']);
break;
default:
$OUTPUT = view();
}
} else {
# Display default output
$OUTPUT = view();
}
# get template
require "../template.php";
# Default view
function view()
{
// main layout
$view = "\n\t<h3>Outstanding Bank Account Entries</h3>\n\t<table " . TMPL_tblDflts . " width='350'>\n\t<form action='" . SELF . "' method='POST' name='form'>\n\t\t<input type='hidden' name='key' value='out'>\n\t\t<tr>\n\t\t\t<th>Field</th>\n\t\t\t<th>Value</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Bank Account</td>\n\t\t\t<td valign='center'>\n\t\t\t\t<select name=bankid>";
function update($_POST)
{
# Get Vars ( banked[] )
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# Check if anything is selected
if (!isset($banked)) {
$err = "<li class=err> Please Select at least one entry to update.";
return cashbook($_POST, $err);
}
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
/* - End Hooks - */
$refnum = getrefnum();
/*refnum*/
# Record all trans
$tot = 0;
$totr = 0;
$totp = 0;
$recpts = "";
$paymnts = "";
foreach ($banked as $key => $cashid) {
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve details from database.", SELF);
$cash = pg_fetch_array($cashRslt);
# Set record as banked
db_connect();
$sql = "UPDATE cashbook SET banked = 'yes' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to set bank deposit as banked in Cubit.", SELF);
}
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND banked = 'no' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank transactions from database.", SELF);
$tot = 0;
$totr = 0;
$totp = 0;
$recpts = "";
$paymnts = "";
while ($cash = pg_fetch_array($cashRslt)) {
if ($cash['trantype'] == "deposit") {
$recpts .= "<tr><td>{$cash['date']}</td><td>{$cash['descript']}</td><td align=right>" . CUR . " {$cash['amount']}</td></tr>";
$totr += $cash['amount'];
} else {
$paymnts .= "<tr><td>{$cash['date']}</td><td>{$cash['descript']}</td><td align=right>" . CUR . " {$cash['amount']}</td></tr>";
$totp += $cash['amount'];
}
$tot += $cash['amount'];
}
$reconbal = sprint($cbal + ($totr - $totp));
# Get account name for bank account
db_connect();
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid= '{$bankid}' AND div = '" . USER_DIV . "'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
# Get hook account number
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
# check if link exists
if (pg_numrows($rslt) < 1) {
return "<li class=err> ERROR : The bank account that you selected doesn't appear to have an account linked to it.";
}
$banklnk = pg_fetch_array($rslt);
# Get bank balance
$sql = "SELECT (debit - credit) as bal FROM trial_bal WHERE period='" . PRD_DB . "' AND accid = '{$banklnk['accnum']}' AND div = '" . USER_DIV . "'";
$brslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
$bal = pg_fetch_array($brslt);
$diff = $reconbal - $bal['bal'];
$derr = "";
if ($diff != 0) {
$derr = "<tr><td colspan=2><b class=err>Bank statement and computer balance not balancing by</b></td><td align=right>" . CUR . " {$diff}</td></tr>";
}
// Layout
$update = "<table border=1>\n\t<tr><th colspan=3><h3>Bank Reconciliation Output</h3></th></tr>\n\t<tr><td></td></tr>\n\t<tr><td colspan=3>\n\t\t<table cellpadding='2' cellspacing='0' border=0 bordercolor='#000000' width=100%>\n\t\t\t<tr><td><b>Bank Account : </b>{$bank['accname']}</td><td></td><td align=right><b>Prepared By : </b>" . USER_NAME . "</td></tr>\n\t\t\t<tr><td><b>Closing Balance As per Bank Statement : </b>" . CUR . " {$cbal}</td><td></td></tr>\n\t\t</table>\n\t</td></tr>\n\t<tr><td><br><br></td></tr>\n\t<tr><td colspan=3><b>Plus Outstanding Receipts :</b></td></tr>\n\t<tr><th>Date</th><th>Reference</th><th>Amount</th></tr>\n\t{$recpts}\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2 align=right><b>Sub Total</b></td><td align=right>" . CUR . " {$totr}</td></tr>\n\t<tr><td><br><br></td></tr>\n\t<tr><td colspan=3><b>Less Outstanding Payments :</b></td></tr>\n\t<tr><th>Date</th><th>Reference</th><th>Amount</th></tr>\n\t{$paymnts}\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2 align=right><b>Sub Total</b></td><td align=right>" . CUR . " {$totp}</td></tr>\n\t<tr><td><br><td></tr>\n\t{$derr}\n\t<tr><td><br><td></tr>\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2><b>Reconciled Bank Balance</b></td><td align=right>" . CUR . " {$reconbal}</td></tr>\n\t<tr><td colspan=2><b>Computer Bank Balance</b></td><td align=right>" . CUR . " {$bal['bal']}</td></tr>\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2><b>Diff</b></td><td align=right>" . CUR . " {$diff}</td></tr>\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t</table>";
# Send the stream
include "temp.xls.php";
return Stream("BankRecon", $update);
}