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";
}
function printcheq()
{
// Set up table to display in
$OUTPUT = "<h3>View Cheque Records</h3>\r\n <table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\r\n <form action='../bank/bank-bankall.php' method=post>\r\n <tr><th>Bank Name</th><th>Account Name</th><th>Date</th><th>Paid to/Received from</th><th>Description</th><th>Transaction Type</th><th>Amount</th><th>Account paid<br>/received from</th></tr>";
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cheqnum > 0 and banked='no' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank cheqque transaction details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
$OUTPUT = "<li class=err> There are no outstanding bank cheque Records yet in Cubit.";
require "../template.php";
}
# display all bank cheques
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
# get account name for account involved
$accRslt = get("core", "accname", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($accRslt);
# get account name for bank account
db_connect();
$sql = "SELECT accname,bankname FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
$OUTPUT .= "<tr class='" . bg_class() . "'><td>{$bank['bankname']}</td><td align=center>{$bank['accname']}</td><td align=center>{$accnt['date']}</td><td align=center>{$accnt['name']}</td><td>{$accnt['descript']}</td><td align=center>{$accnt['trantype']}</td><td align=center>" . CUR . " {$accnt['amount']}<td align=center>{$acc['accname']}</td></td>";
if ($accnt['banked'] == "no") {
$OUTPUT .= "<td><input type=checkbox name='bank[]' value='{$accnt['cashid']}'> <a href='../bank/bank-bank.php?cashid={$accnt['cashid']}'>Bank</td><td><a href='../bank/cheq-cancel.php?cashid={$accnt['cashid']}'>Cancel</td></tr>";
} else {
$OUTPUT .= "</tr>";
}
}
$OUTPUT .= "<tr><td colspan=8><br></td><td colspan=2><input type=submit value='Bank all selected'></td></tr></form></table>";
// all template to display the info and die
require "../template.php";
}
function theme_AddMyIdeaWithLimit($idea, $node, $user, $limit)
{
db_Connect();
// Only because we're doing native DB ops, call connect //
global $db;
$db->begin_transaction();
$count = 0;
try {
$count = theme_CountMyIdeas($node, $user, " FOR UPDATE");
if ($count === false) {
throw new Exception();
}
if ($count >= $limit) {
throw new Exception();
}
$result = db_DoInsert("INSERT INTO " . CMW_TABLE_THEME_IDEA . " (\n\t\t\t\ttheme, node, user, `timestamp`\n\t\t\t)\n\t\t\tVALUES ( \n\t\t\t\t?, ?, ?, NOW()\n\t\t\t)", $idea, $node, $user);
if (empty($result)) {
throw new Exception();
}
$count = theme_CountMyIdeas($node, $user);
if ($count === false) {
throw new Exception();
}
if ($count > $limit) {
throw new Exception();
}
// We're good! Commit! We're finished //
$db->commit();
return ["id" => $result, "count" => $count];
} catch (Exception $ex) {
// Bad! Do a rollback! //
$db->rollback();
return ["id" => 0, "count" => $count];
}
}
function confirm($cashid)
{
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cashid, "num", 1, 20, "Invalid Reference number.");
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Connect to database
db_Connect();
$sql = "SELECT * FROM pettycashbook WHERE cashid = '{$cashid}' AND div = '" . USER_DIV . "'";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database.", SELF);
if (pg_numrows($cashRslt) < 1) {
$OUTPUT = "<li clss=err>Requisistion not found in Cubit.";
return $OUTPUT;
}
$cash = pg_fetch_array($cashRslt);
# Get account name for the account involved
$accRslt = get("core", "accname,accnum,topacc", "accounts", "accid", $cash['accid']);
$acc = pg_fetch_array($accRslt);
// Layout
$confirm = "<h3>Cancel Requisistion</h3>\r\n\t<h4>Confirm entry</h4>\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\r\n\t<form action='" . SELF . "' method=post>\r\n\t<input type=hidden name=key value=cancel>\r\n\t<input type=hidden name=cashid value='{$cash['cashid']}'>\r\n\t<tr><th>Field</th><th>Value</th></tr>\r\n\t<tr class='bg-odd'><td>Date</td><td>{$cash['date']}</td></tr>\r\n\t<tr class='bg-even'><td>Paid to</td><td>{$cash['name']}</td></tr>\r\n\t<tr class='bg-odd'><td>Details</td><td><pre>{$cash['det']}</pre></td></tr>\r\n\t<tr class='bg-even'><td>Amount</td><td>" . CUR . " {$cash['amount']}</td></tr>\r\n\t<tr class='bg-odd'><td>Account</td><td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td></tr>\r\n\t<tr><td><br></td></tr>\r\n\t<tr><td\t><input type=button value='« Back' onClick='javascript:history.back()'></td><td align=right><input type=submit value='Cancel »'></td></tr>\r\n\t</table></form>\r\n\t<p>\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=15%>\r\n\t\t<tr><th>Quick Links</th></tr>\r\n\t\t<tr class='bg-odd'><td><a href='pettycashbook-view.php'>View Petty Cash Requisitions</a></td></tr>\r\n\t\t<tr class='bg-odd'><td><a href='../main.php'>Main Menu</a></td></tr>\r\n\t</table>";
return $confirm;
}
function _config_Load()
{
$ret = cache_Fetch(_CONFIG_CACHE_KEY);
if ($ret === null) {
db_Connect();
$ret = db_DoFetchPair("SELECT `key`,`value` FROM " . CMW_TABLE_CONFIG . "\n\t\t\tWHERE id IN (\n\t\t\t\tSELECT MAX(id) FROM " . CMW_TABLE_CONFIG . " GROUP BY `key`\n\t\t\t);");
cache_Store(_CONFIG_CACHE_KEY, $ret, _CONFIG_CACHE_TTL);
}
return $ret;
}
function confirm($_POST)
{
# Get Vars ( banked[] )
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# check if anything is selected
if (!isset($bank)) {
return "<li class=err> Please Select at least one entry to proccess.";
}
$OUTPUT = "<h3>Process Multiple Entries</h3>\r\n <h4>Confirm Selection</h4>\r\n <table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\r\n <form action='" . SELF . "' method=post>\r\n <input type=hidden name=key value=bank>\r\n <tr><th>Bank Name</th><th>Account Name</th><th>Date</th><th>Reference No</th><th>Paid to/Received from<th>Description</th><th>Transaction Type</th><th>Amount</th><th>Account paid/received from</th></tr>";
$refnum = getrefnum();
/*refnum*/
# get ifo for each deposit
$tot = 0;
foreach ($bank as $key => $cashid) {
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid='{$cashid}'";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve details from database.", SELF);
$numrows = pg_numrows($cashRslt);
# display all bank Deposits
for ($i = 0; $i < $numrows; $i++) {
$cash = pg_fetch_array($cashRslt, $i);
# Get account name for account involved
$accRslt = get("core", "accname", "accounts", "accid", $cash['accinv']);
$acc = pg_fetch_array($accRslt);
# get account name for bank account
db_connect();
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$cash['bankid']}'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
$OUTPUT .= "<input type=hidden name=bank[] value='{$cashid}'><tr class='" . bg_class() . "'><td>{$bank['bankname']}</td><td align=center>{$bank['accname']}</td>\r\n <td align=center><input type=text size=2 name=day[] maxlength=2 value='" . date("d") . "'>-<input type=text size=2 name=mon[] maxlength=2 value='" . date("m") . "'>-<input type=text size=4 name=year[] maxlength=4 value='" . date("Y") . "'></td>\r\n <td align=center><input type=text size=7 name=refnum[] value='" . $refnum++ . "'></td><td align=center>{$cash['name']}</td><td align=center>{$cash['descript']}</td><td align=center>{$cash['trantype']}</td>\r\n <td align=right>" . CUR . " {$cash['amount']}</td><td align=center>{$acc['accname']}</td></tr>";
$tot = $cash['amount'];
}
}
$OUTPUT .= "<tr class='bg-even'><td colspan=7><b>Total Outstanding</b></td><td colspan=2><b>" . CUR . " {$tot}</b></td></tr>\r\n \t\t\t<tr><td colspan=8><br></td><td><input type=submit value='Confirm'></td></tr></form></table>";
return $OUTPUT;
}
function bank($_POST)
{
# Get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cashid, "num", 1, 4, "Invalid Reference number.");
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get cash book record
Db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid = '{$cashid}' AND div = '" . USER_DIV . "'";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database11.", SELF);
if (pg_numrows($accntRslt) < 1) {
$OUTPUT = "<li clss='err'>The entry with reference number, <b>{$cashid}</b> was not found in Cubit.</li>";
return $OUTPUT;
}
$accnt = pg_fetch_array($accntRslt);
# get hook account number
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$accnt['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.</li>";
}
$bank = pg_fetch_array($rslt);
# Date
$sdate = date("Y-m-d");
# If tis customer payment
if ($accnt['cusnum'] > 0) {
db_connect();
# Get invoice Ids and Amounts
$invids = explode("|", $accnt['rinvids']);
$amounts = explode("|", $accnt['amounts']);
$invprds = explode("|", $accnt['invprds']);
$rages = explode("|", $accnt['rages']);
# Return the amount that was surppose to be paid to invoices
foreach ($invids as $key => $invid) {
db_connect();
# Skip all nulls and check existance
if ($invids[$key] > 0 && ext_ex("invoices", "invid", $invids[$key]) && $invprds[$key] != 0) {
db_connect();
$sql = "UPDATE invoices SET balance = (balance + '{$amounts[$key]}'::numeric(13,2)) WHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
} elseif ($invids[$key] > 0 && ext_ex("nons_invoices", "invid", $invids[$key]) && $invprds[$key] == 0) {
db_connect();
$sql = "UPDATE nons_invoices SET balance = (balance + '{$amounts[$key]}'::numeric(13,2)) WHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
custDTA($amounts[$key], $accnt['cusnum'], $rages[$key]);
} elseif ($invids[$key] > 0) {
db_conn($invprds[$key]);
# check if invoice exitsts on prd
if (ext_ex("invoices", "invid", $invids[$key])) {
# if found, Move the invoice back
if (moveback($invids[$key], $invprds[$key], $amounts[$key])) {
}
}
}
}
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
db_connect();
# Update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$accnt['amount']}'::numeric(13,2)) WHERE cusnum = '{$accnt['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit1.", SELF);
# Record the transaction on the statement
$sql = "\n\t\t\t\tINSERT INTO stmnt \n\t\t\t\t\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\tVALUES('{$accnt['cusnum']}', '0', '{$accnt['amount']}','{$sdate}', 'Cheque/Payment for Invoices Cancelled.', '" . USER_DIV . "', '{$accnt['date']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
if ($accnt['lcashid'] > 0) {
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$laccntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database.2", SELF);
$laccnt = pg_fetch_array($laccntRslt);
$sql = "UPDATE bankacct SET fbalance = (fbalance + '{$laccnt['famount']}'::numeric(13,2)), balance = (balance + '{$laccnt['amount']}'::numeric(13,2)) WHERE bankid = '{$laccnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit2.", SELF);
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
}
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
# Make ledge record
custledger($accnt['cusnum'], $bank['accnum'], $sdate, "cancel", "Payment for Invoices Cancelled.", $accnt['amount'], "d");
$descript = $accnt['descript'] . " Cancelled";
$refnum = getrefnum();
$date = date("Y-m-d");
# debit customer account, credit bank account (customer takes money back)
writetrans($accnt['accinv'], $bank['accnum'], $date, $refnum, $accnt['amount'], $descript);
} elseif ($accnt['supid'] > 0) {
db_connect();
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
$ids = explode("|", $accnt['ids']);
$purids = explode("|", $accnt['purids']);
$pamounts = explode("|", $accnt['pamounts']);
$pdates = explode("|", $accnt['pdates']);
if (count($ids) > 0) {
foreach ($ids as $key => $vale) {
if ($ids[$key] > 0) {
rerecord($ids[$key], $accnt['supid'], $purids[$key], $pamounts[$key], $pdates[$key]);
}
}
}
# if the amount was overpaid
if (array_sum($pamounts) < $accnt['amount']) {
# get and record amount that was overpaid to balance the equation
$rem = $accnt['amount'] - array_sum($pamounts);
rerecord('0', $accnt['supid'], '0', $rem, $accnt['date']);
}
# Update the supplier (make balance more)
$sql = "UPDATE suppliers SET balance = (balance + '{$accnt['amount']}'::numeric(13,2)) WHERE supid = '{$accnt['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit3.", SELF);
# Record the payment on the statement
$sql = "INSERT INTO sup_stmnt(supid, edate, cacc, ref, descript, amount, div) VALUES('{$accnt['supid']}', '{$sdate}', '{$bank['accnum']}', '{$accnt['cheqnum']}', 'Cheque/Payment to Supplier Cancelled.', '{$accnt['amount']}', '" . USER_DIV . "')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
if ($accnt['lcashid'] > 0) {
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$laccntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database3.", SELF);
$laccnt = pg_fetch_array($laccntRslt);
$sql = "UPDATE bankacct SET fbalance = (fbalance + '{$laccnt['famount']}'::numeric(13,2)), balance = (balance + '{$laccnt['amount']}'::numeric(13,2)) WHERE bankid = '{$laccnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.4", SELF);
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
}
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
suppledger($accnt['supid'], $bank['accnum'], $sdate, $accnt['cheqnum'], "Payment to Supplier Cancelled", $accnt['amount'], "c");
db_connect();
$descript = $accnt['descript'] . " Cancelled";
$refnum = getrefnum();
$date = date("Y-m-d");
# debit bank, credit supplier account
writetrans($bank['accnum'], $accnt['accinv'], $date, $refnum, $accnt['amount'], $descript);
} elseif ($accnt['suprec'] > 0) {
db_connect();
$Sl = "INSERT INTO sup_stmnt(supid, amount, edate, descript,ref,cacc, div) VALUES('{$accnt['suprec']}','-{$accnt['amount']}','{$accnt['date']}', 'Receipt Returned','{$accnt['cheqnum']}','0', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the supplier (make balance less)
$sql = "UPDATE suppliers SET balance = (balance - '{$accnt['amount']}'::numeric(13,2)) WHERE supid = '{$accnt['suprec']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.5", SELF);
suppDT($accnt['amount'], $accnt['suprec']);
db_connect();
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
if ($accnt['lcashid'] > 0) {
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
}
$descript = $accnt['descript'] . " Cancelled";
$refnum = getrefnum();
$date = date("Y-m-d");
# debit bank, credit supplier account
writetrans($bank['accnum'], $accnt['accinv'], $date, $refnum, $accnt['amount'], $descript);
} elseif (strlen($accnt['accids']) > 0) {
/* -- Start Hooks -- */
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
/* -- End Hooks -- */
multican($accnt, $bank, $vatacc);
} else {
$amount = $accnt['amount'];
$vat = $accnt['vat'];
$chrgvat = $accnt['chrgvat'];
$amount -= $vat;
/* -- Start Hooks -- */
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
/* -- End Hooks -- */
db_connect();
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid = '{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
if ($accnt['trantype'] == "deposit") {
$sql = "UPDATE bankacct SET fbalance = (fbalance - '{$accnt['famount']}'::numeric(13,2)), balance = (balance - '{$accnt['amount']}'::numeric(13,2)) WHERE bankid = '{$accnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.5", SELF);
} else {
$sql = "UPDATE bankacct SET fbalance = (fbalance + '{$accnt['famount']}'::numeric(13,2)), balance = (balance + '{$accnt['amount']}'::numeric(13,2)) WHERE bankid = '{$accnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.6", SELF);
}
/* ---- the Others ---- */
if ($accnt['lcashid'] > 0) {
//Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$laccntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database.4", SELF);
$laccnt = pg_fetch_array($laccntRslt);
if ($laccnt['trantype'] == "deposit") {
$sql = "UPDATE bankacct SET fbalance = (fbalance - '{$laccnt['famount']}'::numeric(13,2)), balance = (balance - '{$laccnt['amount']}'::numeric(13,2)) WHERE bankid = '{$laccnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.7", SELF);
} else {
$sql = "UPDATE bankacct SET fbalance = (fbalance + '{$laccnt['famount']}'::numeric(13,2)), balance = (balance + '{$laccnt['amount']}'::numeric(13,2)) WHERE bankid = '{$laccnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.8", SELF);
}
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
/* ---- End the Others ---- */
}
$descript = $accnt['descript'] . " Cancelled";
$refnum = getrefnum();
$date = date("Y-m-d");
if ($accnt['trantype'] == "deposit") {
# DT(account involved), CT(bank)
writetrans($accnt['accinv'], $bank['accnum'], $date, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $bank['accnum'], $date, $refnum, $vat, $descript);
}
$cc_trantype = cc_TranTypeAcc($accnt['accinv'], $bank['accnum']);
} else {
# DT(bank), CT(account invoilved)
writetrans($bank['accnum'], $accnt['accinv'], $date, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($bank['accnum'], $vatacc, $date, $refnum, $vat, $descript);
}
$cc_trantype = cc_TranTypeAcc($bank['accnum'], $accnt['accinv']);
}
}
if (isset($cc_trantype) && $cc_trantype != false) {
$cc = "<script> CostCenter('{$cc_trantype}', 'Cancelled Bank Transaction', '{$date}', '{$descript}', '" . ($accnt['amount'] - $accnt['vat']) . "', '../'); </script>";
} else {
$cc = "";
}
# Status report
$bank = "\n\t\t\t\t{$cc}\n\t\t\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Cash Book</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td>Cash Book Entry was successfully canceled .</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>";
# Main table (layout with menu)
$OUTPUT = "\n\t\t\t\t<center>\n\t\t\t\t<table width='90%'>\n\t\t\t\t\t<tr valign='top'>\n\t\t\t\t\t\t<td width='60%'>{$bank}</td>\n\t\t\t\t\t\t<td align='center'>\n\t\t\t\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t<th>Quick Navigation</th>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t\t\t\t<td align='center'><a href='cashbook-view.php'>View Cash Book</td>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t\t\t\t<td align='center'><a href='../reporting/not-banked.php'>View Outstanding Cash Book Entries</td>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t\t\t\t<td align='center'><a href='bank-pay-add.php'>Add bank Payment</td>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t\t\t\t<td align='center'><a href='bank-recpt-add.php'>Add Bank Receipt</td>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t</table>\n\t\t\t\t\t\t</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>";
return $OUTPUT;
}
function printaccnt()
{
// Set up table to display in
$OUTPUT = "\n\t\t<center>\n\t\t<h3>View Bank Accounts</h3></td>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Account Type</th>\n\t\t\t\t<th>Bank Name</th>\n\t\t\t\t<th>Type</th>\n\t\t\t\t<th>Currency</th>\n\t\t\t\t<th>Branch Name</th>\n\t\t\t\t<th>Branch Code</th>\n\t\t\t\t<th>Account Name</th>\n\t\t\t\t<th>Account Number</th>\n\t\t\t\t<th>Foreign Balance</th>\n\t\t\t\t<th>Local Currency</th>\n\t\t\t\t<th>Details</th>\n\t\t\t\t<th colspan='2'>Options</th>\n\t\t\t</tr>";
# Connect to database
db_Connect();
$sql = "SELECT * FROM bankacct WHERE div = '" . USER_DIV . "' ORDER BY bankname,branchname";
$bankRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank account details from database.", SELF);
$numrows = pg_numrows($bankRslt);
if ($numrows < 1) {
$OUTPUT = "No Bank Accounts.";
require "../template.php";
}
# Locations drop down
$locs = array("loc" => "Local", "int" => "International");
# display all orders
for ($i = 0; $i < $numrows; $i++) {
$bankacc = pg_fetch_array($bankRslt, $i);
if ($bankacc['fcid'] != 0) {
$curr = getSymbol($bankacc['fcid']);
} else {
$curr = 0;
$locs[$bankacc['btype']] = "Local";
}
$type = $locs[$bankacc['btype']];
# Get hook account number
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$bankacc['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 core.trial_bal\n\t\t\t\tWHERE accid = '{$banklnk['accnum']}' AND period='12' AND div = '" . USER_DIV . "'";
$brslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
$bal = pg_fetch_array($brslt);
$fbal = $bankacc['btype'] == 'int' ? "{$curr['symbol']} {$bankacc['fbalance']}" : "<center> - </center>";
$lbal = $bankacc['btype'] == 'int' ? CUR . " {$bankacc['balance']}" : CUR . " " . sprint($bal['bal']);
# alternate bgcolor
$bgColor = bgcolorc($i);
$OUTPUT .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$bankacc['acctype']}</td>\n\t\t\t\t<td>{$bankacc['bankname']}</td>\n\t\t\t\t<td>{$type}</td>\n\t\t\t\t<td>{$curr['symbol']} - {$curr['name']}</td>\n\t\t\t\t<td>{$bankacc['branchname']}</td>\n\t\t\t\t<td>{$bankacc['branchcode']}</td>\n\t\t\t\t<td>{$bankacc['accname']}</td>\n\t\t\t\t<td align='right'>{$bankacc['accnum']}</td>\n\t\t\t\t<td align='right'>{$fbal}</td>\n\t\t\t\t<td align='right'>{$lbal}</td>\n\t\t\t\t<td>{$bankacc['details']}</td>";
if ($bankacc['type'] == 'cr') {
$OUTPUT .= "<td><a href='creditcard-edit.php?bankid={$bankacc['bankid']}'>Edit</a></td>";
} elseif ($bankacc['type'] == 'ptrl') {
$OUTPUT .= "<td><a href='petrolcard-edit.php?bankid={$bankacc['bankid']}'>Edit</a></td>";
} else {
$OUTPUT .= "<td><a href='bankacct-edit.php?bankid={$bankacc['bankid']}'>Edit</a></td>";
}
db_connect();
# Check if record can be removed
$sql = "SELECT * FROM cashbook WHERE banked = 'no' AND bankid='{$bankacc['bankid']}' AND div = '" . USER_DIV . "'";
$rs = db_exec($sql) or errDie("Unable to get cashbook entries.", SELF);
if (pg_numrows($rs) > 0) {
$OUTPUT .= "<td><br></td></tr>";
} else {
$OUTPUT .= "<td><a href='bankacct-rem.php?bankid={$bankacc['bankid']}'>Delete</a></td></tr>";
}
}
$OUTPUT .= "\n\t\t</table>\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Quick Links</th>\n\t\t\t</tr>\n\t <tr class='" . bg_class() . "'>\n\t \t<td><a href='bank-pay-add.php'>Add Bank Payment</a></td>\n\t </tr>\n\t <tr class='" . bg_class() . "'>\n\t \t<td><a href='bank-recpt-add.php'>Add Bank Receipt</a></td>\n\t </tr>\n\t <tr class='" . bg_class() . "'>\n\t \t<td><a href='cashbook-view.php'>View Cash Book</a></td>\n\t </tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><a href='../main.php'>Main Menu</a></td>\n\t\t\t</tr>\n\t\t</table>";
require "../template.php";
}
function write($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
$v->isOk($from_day, "num", 1, 2, "Invalid Day for the 'From' date.");
$v->isOk($from_month, "num", 1, 2, "Invalid month for the 'From' date..");
$v->isOk($from_year, "num", 1, 4, "Invalid year for the 'From' date..");
$v->isOk($to_day, "num", 1, 2, "Invalid Day for the 'To' date.");
$v->isOk($to_month, "num", 1, 2, "Invalid month for the 'To' date..");
$v->isOk($to_year, "num", 1, 4, "Invalid year for the 'To' date..");
# lets mix the date
$from = $from_day . "-" . $from_month . "-" . $from_year;
$to = $to_day . "-" . $to_month . "-" . $to_year;
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# Get bank details
$bankRslt = get("cubit", "accname,bankname,fcid", "bankacct", "bankid", $bankid);
$bank = pg_fetch_array($bankRslt);
$Sl = "SELECT * FROM currency WHERE fcid='{$bank['fcid']}'";
$Ry = db_exec($Sl) or errDie("Unable to get currency");
if (pg_numrows($Ry) > 0) {
$curdata = pg_fetch_array($Ry);
$fc = $curdata['symbol'];
}
$s1 = "";
$s2 = "";
$s3 = "";
$s4 = "";
$s5 = "";
$order = "order by date desc, cheqnum asc";
if (isset($order)) {
if ($order == "order by date desc, cheqnum asc") {
$s2 = "selected";
} elseif ($order == "order by date desc, cheqnum desc") {
$s3 = "selected";
} elseif ($order == "order by cheqnum asc") {
$s4 = "selected";
} elseif ($order == "order by cheqnum desc") {
$s5 = "selected";
} else {
$s1 = "selected";
}
} else {
$s1 = "selected";
}
// Set up table to display in
# Receipts
$OUTPUT = "\n\t<center>\n\t<h3>Batch Cash Book Entries<br><br>Account : {$bank['accname']} - {$bank['bankname']}<br>Period : {$from} to {$to}</h3>\n\t<table " . TMPL_tblDflts . ">\n\t<form action='" . SELF . "' method='POST' name='form'>\n\t\t<input type='hidden' name='key' value='viewcash'>\n\t\t<input type='hidden' name='bankid' value='{$bankid}'>\n\t\t<input type='hidden' name='from_day' value='{$from_day}'>\n\t\t<input type='hidden' name='from_month' value='{$from_month}'>\n\t\t<input type='hidden' name='from_year' value='{$from_year}'>\n\t\t<input type='hidden' name='to_day' value='{$to_day}'>\n\t\t<input type='hidden' name='to_month' value='{$to_month}'>\n\t\t<input type='hidden' name='to_year' value='{$to_year}'>\n\t</table>\n\t<p>\n\t<table " . TMPL_tblDflts . " width='95%'>\n\t\t<tr>\n\t\t\t<td colspan='7'><h4>Receipts</h4></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th> Date</th>\n\t\t\t<th>Bank Account Name</th>\n\t\t\t<th>Cheque Number</th>\n\t\t\t<th>Received From : </th>\n\t\t\t<th>Description</th>\n\t\t\t<th>Ledger Account</th>\n\t\t\t<th>Amount</th>\n\t\t</tr>";
$rtotal = 0;
// Received total amount
// Connect to database
db_Connect();
# date format
$from = explode("-", $from);
$from = $from[2] . "-" . $from[1] . "-" . $from[0];
$to = explode("-", $to);
$to = $to[2] . "-" . $to[1] . "-" . $to[0];
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
# 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);
db_Connect();
$sql = "SELECT * FROM batch_cashbook WHERE date >= '{$from}' AND date <= '{$to}' AND trantype='deposit' AND bankid='{$bankid}' AND div = '" . USER_DIV . "' {$order}";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
//$OUTPUT .= "<tr><td colspan='7' align='center'><li class='err'>There are no batch Payments/cheques received for the selected period.</td></tr>";
} else {
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
if (!isset($pro[$accnt['cashid']])) {
continue;
}
// if($accnt['bt']=="receipt") {
$refnum = getrefnum();
if (strlen($accnt['accids']) > 0) {
$accids = explode("|", $accnt['accids']);
$vatcodes = explode("|", $accnt['vatcodes']);
$amounts = explode("|", $accnt['amounts']);
$vats = explode("|", $accnt['vats']);
$chrgvats = explode("|", $accnt['chrgvats']);
$refnum = getrefnum();
$descript = $accnt['descript'];
//$date = date("Y-m-d");
$date = $accnt['date'];
foreach ($amounts as $key => $amount) {
# SQL Array Rule: Thou shalt skip Zero Reference
if ($key < 1) {
continue;
}
$accid = $accids[$key];
$vat = $vats[$key];
$chrgvat = $chrgvats[$key];
$amount -= $vat;
$vatcode = $vatcodes[$key];
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
if ($accnt['trantype'] != "deposit") {
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "a");
# DT(account involved), CT(bank)
// writetrans($accid, $banklnk['accnum'], $date, $refnum, ($amount-$vat), $descript);
writetrans($accid, $banklnk['accnum'], $date, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $banklnk['accnum'], $date, $refnum, $vat, $descript);
vatr($vd['id'], $accnt['date'], "INPUT", $vd['code'], $refnum, $accnt['descript'], -$amount, -$vat);
}
} else {
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
# DT(bank), CT(account invoilved)
// writetrans($banklnk['accnum'], $accid, $date, $refnum, ($amount-$vat), $descript);
writetrans($banklnk['accnum'], $accid, $date, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
vatr($vd['id'], $accnt['date'], "OUTPUT", $vd['code'], $refnum, $accnt['descript'], $amount, $vat);
writetrans($banklnk['accnum'], $vatacc, $date, $refnum, $vat, $descript);
}
}
}
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accids, amounts, chrgvats, vats, reference, div) VALUES ('{$accnt['bankid']}', 'deposit', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', 'no', '{$accnt['accids']}', '{$accnt['amounts']}', '{$accnt['chrgvats']}', '{$accnt['vats']}', '{$accnt['reference']}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
db_connect();
$Sl = "DELETE FROM batch_cashbook WHERE cashid='{$accnt['cashid']}'";
$Ri = db_exec($Sl);
} else {
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$accnt['vatcode']}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
# record the payment record
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, vat, chrgvat, banked, accinv, div, vatcode, reference) VALUES ('{$bankid}', 'deposit', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', '{$accnt['vat']}', '{$accnt['chrgvat']}', 'no', '{$accnt['accinv']}', '" . USER_DIV . "', '{$accnt['vatcode']}', '{$accnt['reference']}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
vatr($vd['id'], $accnt['date'], "OUTPUT", $vd['code'], $refnum, $accnt['descript'], $accnt['amount'], $accnt['vat']);
# DT(account involved), CT(bank)
writetrans($banklnk['accnum'], $accnt['accinv'], $accnt['date'], $refnum, $accnt['amount'] - $accnt['vat'], $accnt['descript']);
if ($accnt['vat'] != 0) {
# DT(Vat), CT(Bank)
writetrans($banklnk['accnum'], $vatacc, $accnt['date'], $refnum, $accnt['vat'], $accnt['descript']);
}
// }
db_connect();
$Sl = "DELETE FROM batch_cashbook WHERE cashid='{$accnt['cashid']}'";
$Ri = db_exec($Sl);
continue;
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "<a href=# onClick=openSmallWindow('multi-acc-popup.php?cashid={$accnt['cashid']}')>Multiple Accounts</a>";
$acc['accname'] = "";
$acc['accno'] = "";
} else {
# Get account name for the account involved
$AccRslt = get("core", "accname, topacc, accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
$acc['accno'] = "{$acc['topacc']}/{$acc['accnum']}";
}
# Get account name for bank account
db_connect();
$sql = "SELECT accname,btype FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
$rtotal += $accnt['amount'];
// add to rtotal
$accnt['amount'] = sprint($accnt['amount']);
$accnt['date'] = ext_rdate($accnt['date']);
if ($bname['btype'] != "loc") {
$ex = "/ {$fc} {$accnt['famount']}";
} else {
$ex = "";
}
$OUTPUT .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>{$accnt['date']}</td>\n\t\t\t\t\t\t\t\t<td align='center'>{$bname['accname']}</td>\n\t\t\t\t\t\t\t\t<td align='center'>{$accnt['cheqnum']}</td>\n\t\t\t\t\t\t\t\t<td align='center'>{$accnt['name']}</td>\n\t\t\t\t\t\t\t\t<td>{$accnt['descript']}</td>\n\t\t\t\t\t\t\t\t<td>{$acc['accno']} {$acc['accname']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$accnt['amount']} {$ex}</td>";
if ($accnt['banked'] == "no" && $accnt['opt'] != 'n') {
//$OUTPUT .= "<td><a href='batch-enytry-delete.php?id=$accnt[cashid]'>Delete</td>";
$OUTPUT .= "<input type='hidden' name='pro[" . $accnt['cashid'] . "]' value='1'>";
// $OUTPUT .= "<td><a href='../bank/cheq-cancel.php?cashid=$accnt[cashid]'>Cancel</td>";
}
$OUTPUT .= "</tr>";
}
}
# print the total
$OUTPUT .= "\n\t\t\t<tr class='" . bg_class() . "''>\n\t\t\t\t<td colspan='6'><b>Total Receipts</b></td>\n\t\t\t\t<td><b>" . CUR . " " . sprintf("%01.2f", $rtotal) . "</b></td>\n\t\t\t</tr>";
}
# Seperate the tables with two rows
$OUTPUT .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='7'><br></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='7'><br></td>\n\t\t\t\t\t</tr>";
# Payments
$OUTPUT .= "\n\t\t<tr>\n\t\t\t<td colspan='7'><h4>Payments</h4></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Date</th>\n\t\t\t<th>Bank Account Name</th>\n\t\t\t<th>Cheque Number</th>\n\t\t\t<th>Paid to: </th>\n\t\t\t<th>Description</th>\n\t\t\t<th>Ledger Account</th>\n\t\t\t<th>Amount</th>\n\t\t</tr>";
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
# 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);
$ptotal = 0;
// payments total
// Connect to database
db_Connect();
$sql = "SELECT * FROM batch_cashbook WHERE date >= '{$from}' AND date <= '{$to}' AND trantype='withdrawal' AND bankid='{$bankid}' AND div = '" . USER_DIV . "' {$order}";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
if (pg_numrows($accntRslt) < 1) {
//$OUTPUT .= "<tr><td colspan=7 align=center><li class=err>There are batch no Payments made for the selected period.</td></tr>";
} else {
# Display all bank payments
for ($i = 0; $accnt = pg_fetch_array($accntRslt); $i++) {
if (!isset($pro[$accnt['cashid']])) {
continue;
}
if ($accnt['bt'] == "payment") {
$refnum = getrefnum();
if (strlen($accnt['accids']) > 0) {
$accids = explode("|", $accnt['accids']);
$vatcodes = explode("|", $accnt['vatcodes']);
$amounts = explode("|", $accnt['amounts']);
$vats = explode("|", $accnt['vats']);
$chrgvats = explode("|", $accnt['chrgvats']);
$refnum = getrefnum();
$descript = $accnt['descript'];
//$date = date("Y-m-d");
$date = $accnt['date'];
foreach ($amounts as $key => $amount) {
# SQL Array Rule: Thou shalt skip Zero Reference
if ($key < 1) {
continue;
}
$accid = $accids[$key];
$vat = $vats[$key];
$chrgvat = $chrgvats[$key];
$amount -= $vat;
$vatcode = $vatcodes[$key];
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
if ($accnt['trantype'] != "deposit") {
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "a");
# DT(account involved), CT(bank)
// writetrans($accid, $banklnk['accnum'], $date, $refnum, $amount-$vat, $descript);
writetrans($accid, $banklnk['accnum'], $date, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $banklnk['accnum'], $date, $refnum, $vat, $descript);
vatr($vd['id'], $accnt['date'], "INPUT", $vd['code'], $refnum, $accnt['descript'], -$amount, -$vat);
}
} else {
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
# DT(bank), CT(account invoilved)
writetrans($banklnk['accnum'], $accid, $date, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($banklnk['accnum'], $vatacc, $date, $refnum, $vat, $descript);
vatr($vd['id'], $accnt['date'], "OUTPUT", $vd['code'], $refnum, $accnt['descript'], $amount, $vat);
}
}
}
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accids, amounts, chrgvats, vats, div, vatcode, reference) VALUES ('{$accnt['bankid']}', 'withdrawal', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', 'no', '{$accnt['accids']}', '{$accnt['amounts']}', '{$accnt['chrgvats']}', '{$accnt['vats']}', '" . USER_DIV . "','{$accnt['vatcode']}', '{$accnt['reference']}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
} else {
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$accnt['vatcode']}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
# Record the payment record
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, vat, chrgvat, banked, accinv, div, vatcode, reference) VALUES ('{$bankid}', 'withdrawal', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', '{$accnt['vat']}', '{$accnt['chrgvat']}', 'no', '{$accnt['accinv']}', '" . USER_DIV . "','{$accnt['vatcode']}', '{$accnt['reference']}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
vatr($vd['id'], $accnt['date'], "INPUT", $vd['code'], $refnum, $accnt['descript'], -$accnt['amount'], -$accnt['vat']);
# DT(account involved), CT(bank)
writetrans($accnt['accinv'], $banklnk['accnum'], $accnt['date'], $refnum, $accnt['amount'] - $accnt['vat'], $accnt['descript']);
if ($accnt['vat'] != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $banklnk['accnum'], $accnt['date'], $refnum, $accnt['vat'], $accnt['descript']);
}
}
db_connect();
$Sl = "DELETE FROM batch_cashbook WHERE cashid='{$accnt['cashid']}'";
$Ri = db_exec($Sl);
continue;
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "<a href=# onClick=openSmallWindow('multi-acc-popup.php?cashid={$accnt['cashid']}')>Multiple Accounts</a>";
$acc['accno'] = "";
} else {
# get account name for the account involved
$AccRslt = get("core", "accname, topacc, accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
$acc['accno'] = "{$acc['topacc']}/{$acc['accnum']}";
}
# get account name for bank account
db_connect();
$sql = "SELECT accname,btype FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
$ptotal += $accnt['amount'];
//add to total
$accnt['amount'] = sprint($accnt['amount']);
$accnt['date'] = ext_rdate($accnt['date']);
if ($bname['btype'] != "loc") {
$ex = "/ {$fc} {$accnt['famount']}";
} else {
$ex = "";
}
$OUTPUT .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$accnt['date']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$bname['accname']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['cheqnum']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['name']}</td>\n\t\t\t\t\t\t\t<td>{$accnt['descript']}</td>\n\t\t\t\t\t\t\t<td>{$acc['accno']} {$acc['accname']}</td>\n\t\t\t\t\t\t\t<td>" . CUR . " {$accnt['amount']} {$ex}</td>";
if ($accnt['banked'] == "no" && $accnt['opt'] != 'n') {
//$OUTPUT .= "<td><a href='batch-enytry-delete.php?id=$accnt[cashid]'>Delete</td>";
$OUTPUT .= "<input type='hidden' name='pro[" . $accnt['cashid'] . "]' value='1'>";
//$OUTPUT .= "<td><a href='../bank/cheq-return.php?cashid=$accnt[cashid]'>Returned/Unpaid</td>";
// $OUTPUT .= "<td><a href='../bank/cheq-cancel.php?cashid=$accnt[cashid]'>Cancel</td>";
}
$OUTPUT .= "</tr>";
} elseif ($accnt['bt'] == "transfer") {
$refnum = getrefnum();
extract($accnt);
db_connect();
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$bankid}' AND div = '" . USER_DIV . "'";
$fbankRslt = db_exec($sql);
$fbank = pg_fetch_array($fbankRslt);
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$rid}' AND div = '" . USER_DIV . "'";
$tbankRslt = db_exec($sql);
$tbank = pg_fetch_array($tbankRslt);
$faccid = getbankaccid($bankid);
$taccid = getbankaccid($rid);
# write trans
writetrans($taccid, $faccid, $date, $refnum, $amount, $descript);
# Record the payment record
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, div, reference) VALUES ('{$bankid}', 'withdrawal', '{$date}', '{$tbank['accname']} - {$tbank['bankname']}', '{$descript}', '{$cheqnum}', '{$amount}', 'no', '{$taccid}', '" . USER_DIV . "', '{$reference}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$lcashid = pglib_lastid("cashbook", "cashid");
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, div, reference) VALUES ('{$rid}', 'deposit', '{$date}', '{$fbank['accname']} - {$fbank['bankname']}', '{$descript}', '{$cheqnum}', '{$amount}', 'no', '{$faccid}', '" . USER_DIV . "', '{$reference}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$lcashid2 = pglib_lastid("cashbook", "cashid");
# restore link
$sql = "UPDATE cashbook SET lcashid = '{$lcashid2}' WHERE cashid = '{$lcashid}'";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$sql = "UPDATE cashbook SET lcashid = '{$lcashid}' WHERE cashid = '{$lcashid2}'";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
db_connect();
$Sl = "DELETE FROM batch_cashbook WHERE cashid='{$accnt['cashid']}'";
$Ri = db_exec($Sl);
} else {
$refnum = getrefnum();
if (strlen($accnt['accids']) > 0) {
$accids = explode("|", $accnt['accids']);
$vatcodes = explode("|", $accnt['vatcodes']);
$amounts = explode("|", $accnt['amounts']);
$vats = explode("|", $accnt['vats']);
$chrgvats = explode("|", $accnt['chrgvats']);
$refnum = getrefnum();
$descript = $accnt['descript'];
$date = $accnt["date"];
foreach ($amounts as $key => $amount) {
# SQL Array Rule: Thou shalt skip Zero Reference
if ($key < 1) {
continue;
}
$accid = $accids[$key];
$vat = $vats[$key];
$chrgvat = $chrgvats[$key];
//$amount -= $vat;
$vatcode = $vatcodes[$key];
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
if ($accnt['trantype'] != "deposit") {
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "a");
# DT(account involved), CT(bank)
writetrans($accid, $banklnk['accnum'], $date, $refnum, $amount - $vat, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $banklnk['accnum'], $date, $refnum, $vat, "{$descript}");
vatr($vd['id'], $accnt['date'], "INPUT", $vd['code'], $refnum, $accnt['descript'], -$amount, -$vat);
}
} else {
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
# DT(bank), CT(account invoilved)
writetrans($banklnk['accnum'], $accid, $date, $refnum, $amount - $vat, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($banklnk['accnum'], $vatacc, $date, $refnum, $vat, "{$descript}");
vatr($vd['id'], $accnt['date'], "OUTPUT", $vd['code'], $refnum, $accnt['descript'], $amount, $vat);
}
}
}
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accids, amounts, chrgvats, vats, div, vatcode, reference) VALUES ('{$accnt['bankid']}', 'withdrawal', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', 'no', '{$accnt['accids']}', '{$accnt['amounts']}', '{$accnt['chrgvats']}', '{$accnt['vats']}', '" . USER_DIV . "','{$accnt['vatcode']}', '{$accnt['reference']}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
} else {
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$accnt['vatcode']}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
# Record the payment record
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, vat, chrgvat, banked, accinv, div, vatcode, reference) VALUES ('{$bankid}', 'withdrawal', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', '{$accnt['vat']}', '{$accnt['chrgvat']}', 'no', '{$accnt['accinv']}', '" . USER_DIV . "','{$accnt['vatcode']}', '{$accnt['reference']}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
vatr($vd['id'], $accnt['date'], "INPUT", $vd['code'], $refnum, $accnt['descript'], -$accnt['amount'], -$accnt['vat']);
# DT(account involved), CT(bank)
writetrans($accnt['accinv'], $banklnk['accnum'], $accnt['date'], $refnum, $accnt['amount'] - $accnt['vat'], $accnt['descript']);
if ($accnt['vat'] != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $banklnk['accnum'], $accnt['date'], $refnum, $accnt['vat'], $accnt['descript']);
}
}
db_connect();
$Sl = "DELETE FROM batch_cashbook WHERE cashid='{$accnt['cashid']}'";
$Ri = db_exec($Sl);
continue;
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "<a href=# onClick=openSmallWindow('multi-acc-popup.php?cashid={$accnt['cashid']}')>Multiple Accounts</a>";
$acc['accno'] = "";
} else {
# get account name for the account involved
$AccRslt = get("core", "accname, topacc, accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
$acc['accno'] = "{$acc['topacc']}/{$acc['accnum']}";
}
# get account name for bank account
db_connect();
$sql = "SELECT accname,btype FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
$ptotal += $accnt['amount'];
//add to total
$accnt['amount'] = sprint($accnt['amount']);
$accnt['date'] = ext_rdate($accnt['date']);
if ($bname['btype'] != "loc") {
$ex = "/ {$fc} {$accnt['famount']}";
} else {
$ex = "";
}
$OUTPUT .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$accnt['date']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$bname['accname']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['cheqnum']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['name']}</td>\n\t\t\t\t\t\t\t<td>{$accnt['descript']}</td>\n\t\t\t\t\t\t\t<td>{$acc['accno']} {$acc['accname']}</td>\n\t\t\t\t\t\t\t<td>" . CUR . " {$accnt['amount']} {$ex}</td>";
if ($accnt['banked'] == "no" && $accnt['opt'] != 'n') {
//$OUTPUT .= "<td><a href='batch-enytry-delete.php?id=$accnt[cashid]'>Delete</td>";
$OUTPUT .= "<input type='hidden' name='pro[" . $accnt['cashid'] . "]' value='1'>";
//$OUTPUT .= "<td><a href='../bank/cheq-return.php?cashid=$accnt[cashid]'>Returned/Unpaid</td>";
// $OUTPUT .= "<td><a href='../bank/cheq-cancel.php?cashid=$accnt[cashid]'>Cancel</td>";
}
$OUTPUT .= "</tr>";
}
}
# print the total
$OUTPUT .= "\n\t\t\t<tr class='" . bg_class() . "''>\n\t\t\t\t<td colspan='6'><b>Total Payments</b></td>\n\t\t\t\t<td><b>" . CUR . " " . sprintf("%01.2f", $ptotal) . "</b></td>\n\t\t\t</tr>";
}
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
$OUTPUT = "\n\t\t\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Done</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td>Batch entries have been processed.</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table><br>" . mkQuickLinks(ql("../core/acc-new2.php", "Add New Account"));
return $OUTPUT;
}
function printdep($bankid)
{
// Set up table to display in
$OUTPUT = "\n\t\t\t<h3>Outstanding Payments</h3>\n\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<form action='../bank/bank-bankall.php' method='POST'>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Bank Name</th>\n\t\t\t\t\t<th>Account Name</th>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Paid to</th>\n\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t<th>Transaction Type</th>\n\t\t\t\t\t<th>Amount</th>\n\t\t\t\t\t<th>Account</th>\n\t\t\t\t</tr>";
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND trantype = 'withdrawal' AND banked='no' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
$OUTPUT = "<li class='err'> There are no outstanding Bank Payment entries.</li>";
return $OUTPUT;
}
# display all bank Deposits
$tot = 0;
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "Multiple Accounts";
$acc['accnum'] = "";
$acc['topacc'] = "";
} else {
# get account name for the account involved
$AccRslt = get("core", "accname", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
}
/*
# get account name for account involved
$accRslt = get("core", "accname", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($accRslt);
*/
# get account name for bank account
db_connect();
$sql = "SELECT accname,bankname FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
$accnt['amount'] = sprint($accnt['amount']);
$OUTPUT .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>{$bank['bankname']}</td>\n\t\t\t\t\t\t<td align='center'>{$bank['accname']}</td>\n\t\t\t\t\t\t<td align='center'>{$accnt['date']}</td>\n\t\t\t\t\t\t<td align='center'>{$accnt['name']}</td>\n\t\t\t\t\t\t<td align='center'>{$accnt['descript']}</td>\n\t\t\t\t\t\t<td align='center'>{$accnt['trantype']}</td>\n\t\t\t\t\t\t<td align='right'>" . CUR . " {$accnt['amount']}</td>\n\t\t\t\t\t\t<td align='center'>{$acc['accname']}</td>";
if ($accnt['banked'] == "no" && $accnt['opt'] != 'n') {
$OUTPUT .= "\n\t\t\t\t\t\t<td><a href='../bank/cheq-return.php?cashid={$accnt['cashid']}'>Returned/Unpaid</td>";
// $OUTPUT .= "<td><a href='../bank/cheq-cancel.php?cashid=$accnt[cashid]'>Cancel</td>";
}
$OUTPUT .= "</tr>";
$tot += $accnt['amount'];
}
$tot = sprint($tot);
$OUTPUT .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='6'><b>Total Outstanding</b></td>\n\t\t\t\t\t<td colspan='3'><b>" . CUR . " {$tot}</b></td>\n\t\t\t\t</tr>\n\t\t\t</form>\n\t\t\t</table>";
// return OUTPUT
return $OUTPUT;
}
<?php
include_once './lib/db/db_connect.php.inc';
if (!isset($_GET['validacion'])) {
echo "Error en el enlace de validación. Inténtelo de nuevo. Disculpe las molestias.";
} else {
$db = db_Connect();
$md5 = $_GET['validacion'];
//Busca el md5 recibido en la base de datos
$md5_valido = mysqli_query($db, "select * from ValidaReserva where MD5 like '" . $md5 . "'");
//Recoge el resultado de la búsqueda y lo guarda en una array
if (!($resultado = mysqli_fetch_array($md5_valido))) {
echo "No se ha encontrado la reserva en la base de datos";
} else {
//Almacena los campos IDVisitante e IDEvento de la tabla ListaVisitantes en sendas variables
$visitanteID = $resultado[0];
$eventoID = $resultado[1];
//Busca el ID en la tabla ListaVisitantes que coincide con el IDVisitante y el IDEvento de la tabla ListaVisitantes
$savedID = mysqli_query($db, "select IDVisitante,IDEvento from ListaVisitantes where IDVisitante='" . $visitanteID . "' and IDEvento='" . $eventoID . "'");
if (!($resultado = mysqli_fetch_array($savedID))) {
echo "No se encuentra la reserva registrada en esta base de datos";
} else {
//Elimina el registro de la tabla ValidarReserva, ya que se ha encontrado y se procede la validación
if (mysqli_query($db, "delete from ValidaReserva where MD5 like '" . $md5 . "';")) {
if (mysqli_query($db, "update ListaVisitantes set Activo='1' where IDVisitante='" . $visitanteID . "' and IDEvento='" . $eventoID . "'")) {
echo "¡Reserva validada correctamente!";
} else {
echo "No se pudo activar la reserva";
}
} else {
echo "Ya se realizó la reserva con este código de activación: " . $md5;
function editAccnt($bankid)
{
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 4, "Invalid Bank Account ID.");
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
// Connect to database
db_Connect();
$sql = "SELECT * FROM bankacct WHERE bankid='{$bankid}' AND div = '" . USER_DIV . "'";
$bankRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank account details from database.", SELF);
$numrows = pg_numrows($bankRslt);
if ($numrows < 1) {
$OUTPUT = "<li> - Invalid Bank account ID.";
return $OUTPUT;
}
$accnt = pg_fetch_array($bankRslt);
if (strlen($accnt['accname']) < 20) {
$size = 20;
} else {
$size = strlen($accnt['accname']);
}
// Get Bank account [the traditional way re: hook of hook]
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.";
}
$bank = pg_fetch_array($Rslt);
$bankaccid = $bank["accnum"];
# Check account balance
$sql = "SELECT * FROM trial_bal WHERE period='" . PRD_DB . "' AND accid = '{$bankaccid}' AND debit > 0 OR accid = '{$bankaccid}' AND credit > 0";
$accRslt = db_exec($sql);
if (pg_numrows($accRslt) > 0) {
$acc = pg_fetch_array($accRslt);
$account = "<input type=hidden name='glacc' value='{$acc['accid']}'>{$acc['accname']}";
} else {
core_connect();
# income accounts ($inc)
$account = "<select name='glacc'>";
$sql = "SELECT * FROM accounts WHERE acctype ='B'";
$accRslt = db_exec($sql);
$numrows = pg_numrows($accRslt);
if (empty($numrows)) {
return "<li> - There are no accounts yet in Cubit. Please set up accounts first.";
}
while ($acc = pg_fetch_array($accRslt)) {
if ($acc['accid'] == $bankaccid) {
$sal = "selected";
} else {
$sal = "";
}
$account .= "<option value='{$acc['accid']}' {$sal}>{$acc['accname']}</option>";
}
$account .= "</select>";
}
$cardname = "";
$chm = "";
$chv = "";
$cho = "";
if ($accnt['cardtype'] == 'Visa') {
$chv = "checked=yes";
} elseif ($accnt['cardtype'] == 'Mastercard') {
$chm = "checked=yes";
} else {
$cho = "checked=yes";
$cardname = $accnt['cardtype'];
}
db_connect();
# Locations drop down
$locs = array("loc" => "Local", "int" => "International");
$locsel = extlib_cpsel("loc", $locs, $accnt['btype']);
# currency drop down
$currsel = ext_unddbsel("fcid", "currency", "fcid", "descrip", "There are is no currency found in Cubit, please add currency first.", $accnt['fcid']);
// Set up table to display in
$OUTPUT = "\n\t<h3>Edit Bank Account</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t<form action='" . SELF . "' method=post name=form>\n\t<input type=hidden name=key value=confirm>\n\t<input type=hidden name=bankid value={$bankid}>\n\t<tr><th>Field</th><th>Value</th></tr>\n\t<tr class='bg-odd'><td>Type of Account</td><td valign=center><input type=hidden size=20 name=acctype value='{$accnt['acctype']}'>{$accnt['acctype']}</td></tr>\n\t<tr class='bg-even'><td>Bank Name</td><td valign=center><input type=text size=20 name=bankname value='{$accnt['bankname']}'></td></tr>\n\t<tr class='bg-odd'><td>Type</td><td>{$locsel}</td></tr>\n\t<tr class='bg-even'><td>Currency</td><td>{$currsel}</td></tr>\n\t<tr class='bg-odd'><td>Branch Name</td><td valign=center><input type=text size=20 name=branchname value='{$accnt['branchname']}'></td></tr>\n\t<tr class='bg-even'><td>Branch Code</td><td valign=center><input type=text size=20 name=branchcode value='{$accnt['branchcode']}'></td></tr>\n\t<tr class='bg-odd'><td>Account Name</td><td valign=center><input type=text size='{$size}' name=accname value='{$accnt['accname']}'></td></tr>\n\t<tr class='bg-even'><td>Account Number</td><td valign=center><input type=text name=accnum value='{$accnt['accnum']}'></td></tr>\n\t<tr class='bg-odd'><td>Ledger Account</td><td valign=center>{$account}</td></tr>\n\t<tr class='bg-even'><td>Card Number</td><td ><input type=text size=25 name=cardnum maxlength=16 value='{$accnt['cardnum']}'></td></tr>\n\t<tr class='bg-odd'><td>Expiry Date</td><td><input type=text size=2 name=mon maxlength=2 value='{$accnt['mon']}'>-<input type=text size=4 name=year maxlength=4 value='{$accnt['year']}'>MM-YYYY</td></tr>\n\t<tr class='bg-even'><td>Last 3 Digits at back of Card</td><td ><input type=text size=3 maxlength=3 name=lastdigits value='{$accnt['digits']}'></td></tr>\n\t<tr class='bg-odd'><td>Card Type</td><td><input type=radio name=cardtyp value=Visa {$chv}>Visa <input type=radio name=cardtyp value='Mastercard' {$chm}> Mastercard <input type=radio name=cardtyp value='other' {$cho}>Other: <input type=text name=cardname value='{$cardname}'></td>\n\t<tr class='bg-even'><td>Details</td><td valign=center><textarea cols=20 rows=5 name=details>{$accnt['details']}</textarea></td></tr>\n\t<tr><td><input type=button value='< Cancel' onClick='javascript:history.back();'></td><td valign=center><input type=submit value='Confirm >'></td></tr>\n\t</form>\n\t</table>\n\t<p>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t\t\t<tr><th>Quick Links</th></tr>\n\t\t<tr class='bg-odd'><td><a href='bankacct-view.php'>View Bank Accounts</a></td></tr>\n\t\t\t<tr class='bg-odd'><td><a href='../main.php'>Main Menu</a></td></tr>\n\t</table>";
// all template to display the info and die
require "../template.php";
}
function _db_DoQuery($query, $args)
{
db_Connect();
$st = _db_Prepare($query);
if ($st && _db_BindExecute($st, $args)) {
return $st;
}
db_LogError();
return false;
}
function edit($cashid)
{
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cashid, "num", 1, 4, "Invalid Petty Cash Requisition ID.");
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>{$e['msg']}</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Connect to database
db_Connect();
$sql = "SELECT * FROM pettycashbook WHERE cashid = '{$cashid}' AND div = '" . USER_DIV . "'";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database.", SELF);
if (pg_numrows($cashRslt) < 1) {
$OUTPUT = "<li class='err'>Requisistion not found in Cubit.</li>";
return $OUTPUT;
}
$cash = pg_fetch_array($cashRslt);
$accnts = mkAccSelect("accid", $cash['accid']);
// core_connect();
// $sql = "SELECT * FROM accounts WHERE div = '".USER_DIV."' ORDER BY accname ASC";
// $accRslt = db_exec($sql);
// if(pg_numrows($accRslt) < 1){
// return "<li> ERROR : There are no accounts in the category selected.</li>";
// }
// $accnts = "<select name='accid'>";
// while($acc = pg_fetch_array($accRslt)){
// # Check Disable
// if(isDisabled($acc['accid']))
// continue;
//
// $sel = "";
// if($acc['accid'] == $cash['accid'])
// $sel = "selected";
//
// $accnts .= "<option value='$acc[accid]' $sel>$acc[accname]</option>";
// }
// $accnts .= "</select>";
# check available funds
$cashacc = gethook("accnum", "bankacc", "name", "Petty Cash");
core_connect();
$sql = "SELECT (debit - credit) as bal FROM trial_bal WHERE accid = '{$cashacc}' AND month='" . (int) date("m") . "' AND div = '" . USER_DIV . "'";
$accbRslt = db_exec($sql);
if (pg_numrows($accbRslt) < 1) {
return "<li class='err'> Petty Cash Account not found.</li>";
}
$accb = pg_fetch_array($accbRslt);
$accb['bal'] = sprint($accb['bal']);
# mourn if the is no money
if ($accb['bal'] < 1) {
return "<li> There are no Petty Cash funds available.</li>";
}
list($date_year, $date_month, $date_day) = explode("-", $cash['date']);
# Keep the charge vat option stable
if ($cash['chrgvat'] == "inc") {
$chin = "checked=yes";
$chex = "";
$chno = "";
} elseif ($cash['chrgvat'] == "exc") {
$chin = "";
$chex = "checked=yes";
$chno = "";
} else {
$chin = "";
$chex = "";
$chno = "checked=yes";
}
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes ORDER BY code";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$Vatcodes = "<select name='vatcode'>\r\n\t<option value='0'>Select</option>";
while ($vd = pg_fetch_array($Ri)) {
if ($vd['del'] == "Yes" || $cash["vatcode"] == $vd["id"]) {
$sel = "selected";
} else {
$sel = "";
}
$Vatcodes .= "<option value='{$vd['id']}' {$sel}>{$vd['code']}</option>";
}
$Vatcodes .= "</select>";
// Layout
$add = "\r\n\t\t\t\t<h3>Edit Petty Cash Requisistion</h3>\r\n\t\t\t\t<table " . TMPL_tblDflts . " width='300'>\r\n\t\t\t\t<form action='" . SELF . "' method='POST' name='form'>\r\n\t\t\t\t\t<input type='hidden' name='key' value='confirm'>\r\n\t\t\t\t\t<input type='hidden' name='cashid' value='{$cashid}'>\r\n\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t<th>Field</th>\r\n\t\t\t\t\t\t<th>Value</th>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td>Date</td>\r\n\t\t\t\t\t\t<td>" . mkDateSelect("date", $date_year, $date_month, $date_day) . "</td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td>Paid to</td>\r\n\t\t\t\t\t\t<td valign='center'><input size='20' name='name' value='{$cash['name']}'></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td>Details</td>\r\n\t\t\t\t\t\t<td valign='center'><textarea cols='18' rows='2' name='det'>{$cash['det']}</textarea></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td>Available Funds</td>\r\n\t\t\t\t\t\t<td><input type='hidden' name='bal' value='{$accb['bal']}'>" . CUR . " {$accb['bal']}</td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td>Amount</td>\r\n\t\t\t\t\t\t<td valign='center'>" . CUR . " <input type='text' size='10' name='amount' value='{$cash['amount']}'></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td>VAT Inclusive</td>\r\n\t\t\t\t\t\t<td valign='center'>\r\n\t\t\t\t\t\t\tYes <input type='radio' size='7' name='chrgvat' value='inc' {$chin}>\r\n\t\t\t\t\t\t\tNo<input type='radio' size='7' name='chrgvat' value='exc' {$chex}>\r\n\t\t\t\t\t\t\tNo VAT<input type='radio' size='7' name='chrgvat' value='nov' {$chno}>\r\n\t\t\t\t\t\t</td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td>VAT Code</td>\r\n\t\t\t\t\t\t<td>{$Vatcodes}</td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td>Account Paid to</td>\r\n\t\t\t\t\t\t<td>{$accnts}</td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t<td><br></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t<td valign='center' align='right' colspan='2'><input type='submit' value='Confirm »'></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t</table>\r\n\t\t\t\t<p>\r\n\t\t\t\t<table " . TMPL_tblDflts . " width='15%'>\r\n\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t<th>Quick Links</th>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td><a href='pettycashbook-view.php'>View Petty Cash Requisitions</a></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td><a href='../main.php'>Main Menu</a></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t</table>";
return $add;
}
function love_Fetch(&$user = 0, &$ip = '0.0.0.0', $offset = null, $limit = null)
{
db_Connect();
return db_FetchSingle("SELECT `node` FROM `" . CMW_TABLE_NODE_LOVE . "` WHERE " . "`user`=" . $user . " AND " . "`ip`=INET_ATON('" . $ip . "')" . (is_null($limit) ? "" : " LIMIT " . $limit) . (is_null($offset) ? "" : " OFFSET " . $offset) . ";");
}
function legacy_SetExtraInfo($id, $result)
{
db_Connect();
return db_DoDelete("UPDATE " . CMW_TABLE_LEGACY_USER . "\n\t\tSET num_events=?,gravatar=?\n\t\tWHERE id=?\n\t\t;", intval($result['num_events']), strval($result['gravatar']), intval($id));
}
function node_GetPosts($limit = 10, $offset = 0)
{
global $NODE_SCHEMA;
db_Connect();
$items = db_Fetch("SELECT * FROM `" . CMW_TABLE_NODE . "` WHERE " . "`time_published` != " . "'0000-00-00 00:00:00'" . " AND " . "`type`=" . "\"post\"" . " ORDER BY `time_published` DESC" . " LIMIT " . $limit . " OFFSET " . $offset . ";", $NODE_SCHEMA);
return $items;
}
function write($_POST)
{
# Get vars
global $_FILES;
foreach ($_POST as $key => $value) {
${$key} = $value;
}
$vatcode += 0;
# Validate input
require_lib("validate");
$v = new validate();
$v->isOk($cashid, "num", 1, 4, "Invalid Reference number.");
$v->isOk($refno, "string", 0, 255, "Invalid Receipt/Ref No.");
$v->isOk($ret, "float", 0, 10, "Invalid Returned/Change amount.");
$vatret += 0;
# Display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
$date = DATE_STD;
if (is_uploaded_file($_FILES["doc"]["tmp_name"])) {
$doctyp = $_FILES["doc"]["type"];
$filename = $_FILES["doc"]["name"];
# Open file in "read, binary" mode
$docu = "";
$file = fopen($_FILES['doc']['tmp_name'], "rb");
while (!feof($file)) {
# fread is binary safe
$docu .= fread($file, 1024);
}
fclose($file);
# Compress and encode the file
$docu = doclib_encode($docu, 9);
}
# Connect to database
db_Connect();
$sql = "SELECT * FROM pettycashbook WHERE cashid = '{$cashid}' AND div = '" . USER_DIV . "'";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database.", SELF);
$cash = pg_fetch_array($cashRslt);
# Mourn if the is not sufficient money
if ($ret > $cash['amount']) {
return "<li class=err>Error : Returned/Change amount is more than the requisistion amount.</li>\n\t\t<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
}
# Ya Nasty zero
$ret += 0;
$refnum = getrefnum($date);
# Check available funds
$cashacc = gethook("accnum", "bankacc", "name", "Petty Cash");
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}' AND zero='Yes'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
if (pg_num_rows($Ri) > 0) {
$cash['chrgvat'] = "exc";
}
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
# if amount is not equal to zero, write tran
if ($ret != 0) {
# If subtract vat
if ($cash['chrgvat'] == "inc") {
# Get vat account
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
$VATP = TAX_VAT;
//$vatret = sprint(($VATP/($VATP + 100)) * $ret);
$sret = $ret - $vatret;
# Write returning transaction
writetrans($cashacc, $cash['accid'], date("Y-m-d"), $refnum, $sret, "Petty Cash Change");
# Write VAT returning transaction
writetrans($cashacc, $vatacc, date("Y-m-d"), $refnum, $vatret, "VAT return, " . $cash['det']);
vatr($vd['id'], date("Y-m-d"), "INPUT", $vd['code'], $refnum, "VAT return, " . $cash['det'], $sret + $vatret, $vatret);
/*
# Record vat statement
db_connect();
$sql = "INSERT INTO svatrec(edate, ref, amount, descript, div) VALUES('".date("Y-m-d")."', '$refnum', '$vatret', 'VAT returned on Petty Cash Change.', '".USER_DIV."')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.",SELF);
*/
} elseif ($cash['chrgvat'] == "exc") {
# Get vat account
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
$VATP = TAX_VAT;
//$vatret = sprint(($VATP/100) * $ret);
$sret = $ret;
$ret += $vatret;
# Write returning transaction
writetrans($cashacc, $cash['accid'], date("Y-m-d"), $refnum, $sret, "Petty Cash Change");
# Write VAT returning transaction
writetrans($cashacc, $vatacc, date("Y-m-d"), $refnum, $vatret, "VAT return, " . $cash['det']);
vatr($vd['id'], date("Y-m-d"), "INPUT", $vd['code'], $refnum, "VAT return, " . $cash['det'], $sret + $vatret, $vatret);
/*
# Record vat statement
db_connect();
$sql = "INSERT INTO svatrec(edate, ref, amount, descript, div) VALUES('".date("Y-m-d")."', '$refnum', '$vatret', 'VAT returned on Petty Cash Change.', '".USER_DIV."')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.",SELF);
*/
} else {
# Write returning transaction
writetrans($cashacc, $cash['accid'], date("Y-m-d"), $refnum, $ret, "Petty Cash Change");
$sret = $ret;
}
# Record tranfer for patty cash report
db_connect();
$date = date("Y-m-d");
$sql = "INSERT INTO pettyrec(date, type, det, amount, name, div) VALUES ('{$date}', 'Change', 'Petty Cash Change', '{$ret}', 'Cash Received From : {$cash['name']}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
}
if (!isset($sret)) {
$sret = 0;
}
# Update
db_connect();
$sql = "UPDATE pettycashbook SET refno = '{$refno}', amount = (amount - '{$ret}'), reced = 'yes' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
if (isset($docu)) {
db_conn(YR_DB);
$sql = "INSERT INTO documents(typeid, typename, xin, docref, docdate, docname, filename, mimetype, descrip, docu, div) VALUES ('prec', 'Petty Cash Receipt', '{$refno}', '{$refno}', '{$date}', '{$filename}', '{$filename}', '{$doctyp}', 'Receipt from {$cash['name']}', '{$docu}', '" . USER_DIV . "')";
$docRslt = db_exec($sql) or errDie("Unable to add {$docname} to system.", SELF);
}
if (cc_TranTypeAcc($cashacc, $cash['accid']) != false) {
$cc_trantype = cc_TranTypeAcc($cashacc, $cash['accid']);
$cc = "<script> CostCenter('{$cc_trantype}', 'Petty Cash Receipt', '{$date}', '{$cash['det']}', '{$sret}', '../'); </script>";
} else {
$cc = "";
}
# Status report
$write = "{$cc}\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width='30%'>\n\t\t<tr><th>Petty Cash Requisition Approved</th></tr>\n\t\t<tr class=datacell><td>Petty Cash Requisition has been approved .</td></tr>\n\t</table>\n\t<p>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=15%>\n\t\t<tr><th>Quick Links</th></tr>\n\t\t<tr class='bg-odd'><td><a href='petty-req-add.php'>Add Petty Cash Requisition</a></td></tr>\n\t\t<tr class='bg-odd'><td><a href='pettycashbook-view.php'>View Petty Cash Requisitions</a></td></tr>\n\t\t<tr class='bg-odd'><td><a href='../main.php'>Main Menu</a></td></tr>\n\t</table>";
return $write;
}
function user_GetIdAndHashByMail($mail)
{
db_Connect();
// TODO: Use time-attack safe fetch function
$data = db_Fetch("SELECT `node` AS `id`,`hash` FROM `" . CMW_TABLE_USER . "` WHERE " . "`mail`=\"" . $mail . "\"" . " LIMIT 1" . ";");
if (count($data)) {
return $data[0];
}
return null;
}
function viewcash($_POST)
{
# get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 20, "Invalid Bank Account Number.");
$v->isOk($fday, "num", 1, 2, "Invalid Day for the 'From' date.");
$v->isOk($fmonth, "num", 1, 2, "Invalid month for the 'From' date..");
$v->isOk($fyear, "num", 1, 4, "Invalid year for the 'From' date..");
$v->isOk($lday, "num", 1, 2, "Invalid Day for the 'To' date.");
$v->isOk($lmonth, "num", 1, 2, "Invalid month for the 'To' date..");
$v->isOk($lyear, "num", 1, 4, "Invalid year for the 'To' date..");
# lets mix the date
$from = sprintf("%02.2d", $fday) . "-" . sprintf("%02.2d", $fmonth) . "-" . $fyear;
$to = sprintf("%02.2d", $lday) . "-" . sprintf("%02.2d", $lmonth) . "-" . $lyear;
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# 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);
// Receipts
$OUTPUT = "<table border=1>\n\t\t<tr><th colspan=7><h3>Cash Book : {$bank['accname']}</h3></th></tr>\n\t\t<tr><th colspan=7><h3>{$from} to {$to}</h3></th></tr>\n\t\t<tr><td></td></tr>\n <tr><td colspan=7><h3>Analysis of Receipts</h3></td></tr>\n <tr><th><u>Date</u></th><th><u>Bank Account Name</u></th><th><u>Cheque Number</u></th><th><u>Received From :</u></th><th><u>Description</u></th><th><u>Ledger Account</u></th><th><u>Amount</u></th></tr>";
# date format
$from = explode("-", $from);
$from = $from[2] . "-" . $from[1] . "-" . $from[0];
$to = explode("-", $to);
$to = $to[2] . "-" . $to[1] . "-" . $to[0];
$rtotal = 0;
# Received total amount
# Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND date >= '{$from}' AND date <= '{$to}' AND trantype='deposit' AND banked='yes' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
$OUTPUT .= "<tr><td colspan=7 align=center><li class=err>There are no Payments/cheques received on the selected period.</td></tr>";
} else {
# display all bank Deposits
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "Multiple Accounts";
$acc['accnum'] = "000";
$acc['topacc'] = "000";
} else {
# get account name for the account involved
$AccRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
}
/*
# get account name for account involved
$accRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($accRslt);
*/
# get account name for bank account
db_connect();
$sql = "SELECT accname FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
# format date
$accnt['date'] = explode("-", $accnt['date']);
$accnt['date'] = $accnt['date'][2] . "-" . $accnt['date'][1] . "-" . $accnt['date'][0];
$rtotal += $accnt['amount'];
// add to rtotal
$OUTPUT .= "<tr><td>{$accnt['date']}</td><td>{$bname['accname']}</td><td align=center>{$accnt['cheqnum']}</td><td align=center>{$accnt['name']}</td><td>{$accnt['descript']}</td><td>{$acc['topacc']}/{$acc['accnum']} {$acc['accname']}</td><td>" . CUR . " {$accnt['amount']}</td></tr>";
}
# print the total
$OUTPUT .= "<tr><td></td></tr>\n\t\t\t<tr><td colspan=6><b>Total Receipts</b></td><td><b>" . CUR . " " . sprintf("%01.2f", $rtotal) . "</b></td></tr>";
}
# Seperate the tables with two rows
$OUTPUT .= "<tr><td colspan=7><br></td></tr><tr><td colspan=7><br></td></tr>";
# Payments
$OUTPUT .= "<tr><td colspan=7><h3>Analysis of Payments</h3></td></tr>\n <tr><th><u>Date</u></th><th><u>Bank Account Name</u></th><th><u>Cheque Number</u></th><th><u>Paid to:</u></th><th><u>Description</u></th><th><u>Ledger Account</u></th><th><u>Amount</u></th></tr>";
$ptotal = 0;
# payments total
# Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE date >= '{$from}' AND date <= '{$to}' AND trantype='withdrawal' AND banked='yes' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
$OUTPUT .= "<tr><td colspan=7 align=center><li class=err>There are no Payments made on the selected period.</td></tr>";
} else {
# Display all bank Deposits
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "Multiple Accounts";
$acc['accnum'] = "000";
$acc['topacc'] = "000";
} else {
# get account name for the account involved
$AccRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
}
/*
# get account name for account involved
$accRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($accRslt);
*/
# get account name for bank account
db_connect();
$sql = "SELECT accname FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
# format date
$accnt['date'] = explode("-", $accnt['date']);
$accnt['date'] = $accnt['date'][2] . "-" . $accnt['date'][1] . "-" . $accnt['date'][0];
$ptotal += $accnt['amount'];
# add to total
$OUTPUT .= "<tr><td>{$accnt['date']}</td><td>{$bname['accname']}</td><td align=center>{$accnt['cheqnum']}</td><td align=center>{$accnt['name']}</td><td>{$accnt['descript']}</td><td>{$acc['topacc']}/{$acc['accnum']} {$acc['accname']}</td><td>" . CUR . " {$accnt['amount']}</td></tr>";
}
# print the total
$OUTPUT .= "<tr><td></td></tr>\n\t\t\t<tr><td colspan=6><b>Total Payments</b></td><td><b>" . CUR . " " . sprintf("%01.2f", $ptotal) . "</b></td></tr>";
}
$OUTPUT .= "</table>";
# Send the stream
include "temp.xls.php";
Stream("CashBook", $OUTPUT);
}
function viewcash($_POST)
{
# get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 20, "Invalid Bank Account Number.");
$v->isOk($fday, "num", 1, 2, "Invalid Day for the 'From' date.");
$v->isOk($fmonth, "num", 1, 2, "Invalid month for the 'From' date..");
$v->isOk($fyear, "num", 1, 4, "Invalid year for the 'From' date..");
$v->isOk($lday, "num", 1, 2, "Invalid Day for the 'To' date.");
$v->isOk($lmonth, "num", 1, 2, "Invalid month for the 'To' date..");
$v->isOk($lyear, "num", 1, 4, "Invalid year for the 'To' date..");
# lets mix the date
$from = sprintf("%02.2d", $fday) . "-" . sprintf("%02.2d", $fmonth) . "-" . $fyear;
$to = sprintf("%02.2d", $lday) . "-" . sprintf("%02.2d", $lmonth) . "-" . $lyear;
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get account name for bank account
db_connect();
$sql = "SELECT accname,bankname FROM bankacct WHERE bankid= '{$bankid}'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
// Receipts
$OUTPUT = "<center><h3>Cash Book : {$bank['accname']}<br><br>{$from} to {$to}</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t<tr><td colspan=7><h4>Analysis of Receipts</h4></td></tr>\n\t<tr><th>Date</th><th>Bank Account Name</th><th>Cheque Number</th><th>Received From : </th><th>Description</th><th>Ledger Account</th><th>Amount</th></tr>";
# date format
$from = explode("-", $from);
$from = $from[2] . "-" . $from[1] . "-" . $from[0];
$to = explode("-", $to);
$to = $to[2] . "-" . $to[1] . "-" . $to[0];
$rtotal = 0;
# Received total amount
# Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND date >= '{$from}' AND date <= '{$to}' AND trantype='deposit' AND banked='yes' ORDER BY date DESC";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
$OUTPUT .= "<tr><td colspan=7 align=center><li class=err>There are no Payments/cheques received on the selected period.</td></tr>";
} else {
# display all bank Deposits
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "Multiple Accounts";
$acc['accnum'] = "000";
$acc['topacc'] = "000";
} else {
# get account name for the account involved
$AccRslt = undget("core", "accname", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
}
/*
# get account name for account involved
$accRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($accRslt);
*/
# get account name for bank account
db_connect();
$sql = "SELECT accname FROM bankacct WHERE bankid= '{$accnt['bankid']}'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
# format date
$accnt['date'] = explode("-", $accnt['date']);
$accnt['date'] = $accnt['date'][2] . "-" . $accnt['date'][1] . "-" . $accnt['date'][0];
$rtotal += $accnt['amount'];
// add to rtotal
$OUTPUT .= "<tr class='" . bg_class() . "'><td>{$accnt['date']}</td><td>{$bname['accname']}</td><td align=center>{$accnt['cheqnum']}</td><td align=center>{$accnt['name']}</td><td>{$accnt['descript']}</td><td>{$acc['topacc']}/{$acc['accnum']} {$acc['accname']}</td><td>" . CUR . " {$accnt['amount']}</td></tr>";
}
# print the total
$OUTPUT .= "<tr class='bg-even''><td colspan=6><b>Total Receipts</b></td><td><b>" . CUR . " " . sprintf("%01.2f", $rtotal) . "</b></td></tr>";
}
# Seperate the tables with two rows
$OUTPUT .= "<tr><td colspan=7><br></td></tr><tr><td colspan=7><br></td></tr>";
# Payments
$OUTPUT .= "<tr><td colspan=7><h4>Analysis of Payments</h4></td></tr>\n\t<tr><th>Date</th><th>Bank Account Name</th><th>Cheque Number</th><th>Paid to: </th><th>Description</th><th>Ledger Account</th><th>Amount</th></tr>";
$ptotal = 0;
# payments total
# Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND date >= '{$from}' AND date <= '{$to}' AND trantype='withdrawal' AND banked='yes' ORDER BY date DESC";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
$OUTPUT .= "<tr><td colspan=7 align=center><li class=err>There are no Payments made on the selected period.</td></tr>";
} else {
# Display all bank Deposits
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "Multiple Accounts";
$acc['accnum'] = "000";
$acc['topacc'] = "000";
} else {
# get account name for the account involved
$AccRslt = undget("core", "accname", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
}
/*
# get account name for account involved
$accRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($accRslt);
*/
# get account name for bank account
db_connect();
$sql = "SELECT accname FROM bankacct WHERE bankid= '{$accnt['bankid']}'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
# format date
$accnt['date'] = explode("-", $accnt['date']);
$accnt['date'] = $accnt['date'][2] . "-" . $accnt['date'][1] . "-" . $accnt['date'][0];
$ptotal += $accnt['amount'];
# add to total
$OUTPUT .= "<tr class='" . bg_class() . "'><td>{$accnt['date']}</td><td>{$bname['accname']}</td><td align=center>{$accnt['cheqnum']}</td><td align=center>{$accnt['name']}</td><td>{$accnt['descript']}</td><td>{$acc['topacc']}/{$acc['accnum']} {$acc['accname']}</td><td>" . CUR . " {$accnt['amount']}</td></tr>";
}
# print the total
$OUTPUT .= "<tr class='bg-even''><td colspan=6><b>Total Payments</b></td><td><b>" . CUR . " " . sprintf("%01.2f", $ptotal) . "</b></td></tr>";
}
$OUTPUT .= "<tr><td colspan=7><br></td></tr>\n\n\t<!--\n\t<tr><td align=center colspan=10>\n\t\t<form action='../xls/banked-xls.php' method=post name=form>\n\t\t<input type=hidden name=key value=viewcash>\n\t\t<input type=hidden name=bankid value='{$bankid}'>\n\t\t<input type=hidden name=fday value='{$fday}'>\n\t\t<input type=hidden name=fmonth value='{$fmonth}'>\n\t\t<input type=hidden name=fyear value='{$fyear}'>\n\t\t<input type=hidden name=lday value='{$lday}'>\n\t\t<input type=hidden name=lmonth value='{$lmonth}'>\n\t\t<input type=hidden name=lyear value='{$lyear}'>\n\t\t<input type=submit name=xls value='Export to spreadsheet'>\n\t\t</form>\n\t</td></tr>\n\t-->\n\n\t</table>\n\t<p>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t\t<tr><th>Quick Links</th></tr>\n\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
return $OUTPUT;
}
function printdep($bankid)
{
// Set up table to display in
$OUTPUT = "<table>\n\t<tr><th colspan=3><h3>Outstanding Payments</h3></th></tr>\n\t<tr><th>Bank Name</th><th>Account Name</th><th>Date</th><th>Paid to</th><th>Description</th><th>Transaction Type</th><th>Amount</th><th>Account</th></tr>";
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND trantype = 'withdrawal' AND banked='no' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
$OUTPUT = "<tr><td></td></tr>\n\t\t<tr><th colspan=5><li class=err> There are no outstanding Bank Payment entries.</li></th></tr>";
return $OUTPUT;
}
# display all bank Deposits
$tot = 0;
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
# get account name for account involved
$accRslt = get("core", "accname", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($accRslt);
# get account name for bank account
db_connect();
$sql = "SELECT accname,bankname FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
$OUTPUT .= "<tr><td>{$bank['bankname']}</td><td align=center>{$bank['accname']}</td><td align=center>{$accnt['date']}</td><td align=center>{$accnt['name']}</td><td align=center>{$accnt['descript']}</td><td align=center>{$accnt['trantype']}</td><td align=right>" . CUR . " {$accnt['amount']}</td><td align=center>{$acc['accname']}</td></tr>";
$tot += $accnt['amount'];
}
$OUTPUT .= "<tr><td colspan=6><b>Total Outstanding</b></td><td colspan=3><b>" . CUR . " {$tot}</b></td></tr>";
# return OUTPUT
return $OUTPUT;
}
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);
}
function viewallcash($_POST)
{
# get vars
extract($_POST);
db_Connect();
// Layout
$OUTPUT = "\r\n\t\t<center>\r\n\t\t<h3>Petty Cash Book</h3>\r\n\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Date</th>\r\n\t\t\t\t<th>Paid to</th>\r\n\t\t\t\t<th>Details</th>\r\n\t\t\t\t<th>Amount</th>\r\n\t\t\t\t<th>VAT Amount</th>\r\n\t\t\t\t<th>Receipt/Ref No.</th>\r\n\t\t\t\t<th>Account Paid to</th>\r\n\t\t\t\t<th colspan='3'>Options</th>\r\n\t\t\t</tr>";
$rtotal = 0;
$totout = 0;
$sql = "SELECT * FROM pettycashbook WHERE div = '" . USER_DIV . "' ORDER BY date DESC";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve petty cash entrie from database.", SELF);
if (pg_numrows($cashRslt) < 1) {
$OUTPUT .= "\r\n\t\t\t<tr>\r\n\t\t\t\t<td colspan='7' align='center'><li class='err'>There are no enties found on the selected date range.</td>\r\n\t\t\t</tr>";
} else {
# display all bank Deposits
for ($i = 0; $cash = pg_fetch_array($cashRslt); $i++) {
# get account name for account involved
$accRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $cash['accid']);
$acc = pg_fetch_array($accRslt);
$cash['date'] = explode("-", $cash['date']);
$cash['date'] = $cash['date'][2] . "-" . $cash['date'][1] . "-" . $cash['date'][0];
$rtotal += $cash['amount'];
// add to rtotal
if ($cash['chrgvat'] == "exc") {
$cash['amount'] = $cash['amount'] + $cash['vat_paid'];
}
$cash['amount'] = sprint($cash['amount']);
$get_vcod = "SELECT vat_amount FROM vatcodes WHERE id = '{$cash['vatcode']}' LIMIT 1";
$run_vcod = db_exec($get_vcod) or errDie("Unable to get vat code information.");
if (pg_numrows($run_vcod) < 1) {
$varr['vat_amount'] = 0;
} else {
$varr = pg_fetch_array($run_vcod);
}
if ($cash['chrgvat'] == "inc") {
#calculate vat amt inc ...
$vatamt = $cash['amount'] - $cash['amount'] / (1 + $varr['vat_amount'] / 100);
} else {
#calculate vat amt excl ...
$vatamt = $cash['amount'] / 100 * $varr['vat_amount'];
}
$vatamt = sprint($vatamt);
$OUTPUT .= "\r\n\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t<td>{$cash['date']}</td>\r\n\t\t\t\t\t<td>{$cash['name']}</td>\r\n\t\t\t\t\t<td>{$cash['det']}</td>\r\n\t\t\t\t\t<td>" . CUR . " {$cash['amount']}</td>\r\n\t\t\t\t\t<td>" . CUR . " {$vatamt}</td>\r\n\t\t\t\t\t<td>{$cash['refno']}</td>\r\n\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} {$acc['accname']}</td>";
if ($cash['approved'] == "n") {
$totout += $cash['amount'];
$OUTPUT .= "\r\n\t\t\t\t\t\t<td><a href='petty-req-edit.php?cashid={$cash['cashid']}'>Edit</td>\r\n\t\t\t\t\t\t<td><a href='petty-req-can.php?cashid={$cash['cashid']}'>Cancel</td>\r\n\t\t\t\t\t\t<td><a href='petty-req-app.php?cashid={$cash['cashid']}'>Approve</td>\r\n\t\t\t\t\t</tr>";
} else {
$OUTPUT .= "<td colspan='3'><a href='petty-req-recpt.php?cashid={$cash['cashid']}'>Record Receipt</td></tr>";
}
}
# print the total
$OUTPUT .= "\r\n\t\t\t<tr><td><br></td></tr>\r\n\t\t\t<tr class='" . bg_class() . "''>\r\n\t\t\t\t<td colspan='3'><b>Total Requisitions</b></td>\r\n\t\t\t\t<td><b>" . CUR . " " . sprint($rtotal) . "</b></td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "''>\r\n\t\t\t\t<td colspan='3'><b>Total Outstanding Requisitions</b></td>\r\n\t\t\t\t<td><b>" . CUR . " " . sprint($totout) . "</b></td>\r\n\t\t\t</tr>";
}
$OUTPUT .= "\r\n </table>\r\n <p>\r\n\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Quick Links</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td><a href='petty-req-add.php'>Add Petty Cash Requisition</a></td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td><a href='pettycashbook-view.php'>View Petty Cash Requisitions</a></td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td><a href='../main.php'>Main Menu</a></td>\r\n\t\t\t</tr>\r\n\t\t</table>";
return $OUTPUT;
}
function viewrep($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($from_day, "num", 1, 2, "Invalid Day for the 'From' date.");
$v->isOk($from_month, "num", 1, 2, "Invalid month for the 'From' date..");
$v->isOk($from_year, "num", 1, 4, "Invalid year for the 'From' date..");
$v->isOk($to_day, "num", 1, 2, "Invalid Day for the 'To' date.");
$v->isOk($to_month, "num", 1, 2, "Invalid month for the 'To' date..");
$v->isOk($to_year, "num", 1, 4, "Invalid year for the 'To' date..");
# lets mix the date
$from = $from_day . "-" . $from_month . "-" . $from_year;
$to = $to_day . "-" . $to_month . "-" . $to_year;
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
db_Connect();
# date format
$sfrom = explode("-", $from);
$sfrom = $sfrom[2] . "-" . $sfrom[1] . "-" . $sfrom[0];
$sto = explode("-", $to);
$sto = $sto[2] . "-" . $sto[1] . "-" . $sto[0];
// Layout
$OUTPUT = "\r\n\t\t<center>\r\n\t\t<h3>Petty Cash Book Report<br><br>From : {$from} To : {$to}</h3>\r\n\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Date</th>\r\n\t\t\t\t<th>Type</th>\r\n\t\t\t\t<th>Details</th>\r\n\t\t\t\t<th>Amount</th>\r\n\t\t\t</tr>";
$rtotal = 0;
$qtotal = 0;
$ttotal = 0;
$sql = "SELECT * FROM pettyrec WHERE date >= '{$sfrom}' AND date <= '{$sto}' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve petty cash entrie from database.", SELF);
if (pg_numrows($cashRslt) < 1) {
$OUTPUT .= "\r\n\t\t\t<tr>\r\n\t\t\t\t<td colspan='4' align='center'><li class='err'>There are no enties found on the selected date range.</td>\r\n\t\t\t</tr>";
} else {
# display all bank Deposits
for ($i = 0; $cash = pg_fetch_array($cashRslt); $i++) {
$cash['date'] = explode("-", $cash['date']);
$cash['date'] = $cash['date'][2] . "-" . $cash['date'][1] . "-" . $cash['date'][0];
$OUTPUT .= "\r\n\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t<td>{$cash['date']}</td>\r\n\t\t\t\t\t<td>{$cash['name']}</td>\r\n\t\t\t\t\t<td>{$cash['det']}</td>\r\n\t\t\t\t\t<td>" . CUR . " " . sprint($cash['amount']) . "</td>\r\n\t\t\t\t</tr>";
if ($cash['type'] == "Change") {
$rtotal += $cash['amount'];
} elseif ($cash['type'] == "Req") {
$qtotal += $cash['amount'];
} elseif ($cash['type'] == "Transfer") {
$ttotal += $cash['amount'];
}
}
# requisition total must be positive
$qtotal = $qtotal * -1;
# Get available funds
$cashacc = gethook("accnum", "bankacc", "name", "Petty Cash");
core_connect();
$sql = "SELECT (debit - credit) as bal FROM trial_bal WHERE accid = '{$cashacc}' AND div = '" . USER_DIV . "' AND month = '" . PRD_DB . "'";
$accbRslt = db_exec($sql);
if (pg_numrows($accbRslt) < 1) {
return "<li class='err'> Petty Cash Account not found.</li>";
}
$accb = pg_fetch_array($accbRslt);
$balance = sprint($accb['bal']);
# print the total
$OUTPUT .= "\r\n\t\t\t" . TBL_BR . "\r\n\t\t\t<tr class='" . bg_class() . "''>\r\n\t\t\t\t<td colspan='3'><b>Total Transfer</b></td>\r\n\t\t\t\t<td><b>" . CUR . " " . sprint($ttotal) . "</b></td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "''>\r\n\t\t\t\t<td colspan='3'><b>Total Requisitions</b></td>\r\n\t\t\t\t<td><b>" . CUR . " " . sprint($qtotal) . "</b></td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "''>\r\n\t\t\t\t<td colspan='3'><b>Total Returned</b></td>\r\n\t\t\t\t<td><b>" . CUR . " " . sprint($rtotal) . "</b></td>\r\n\t\t\t</tr>\r\n\t\t\t" . TBL_BR . "\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td colspan='3'><b>Balance</b></td>\r\n\t\t\t\t<td><b>" . CUR . " " . sprint($balance) . "</b></td>\r\n\t\t\t</tr>";
}
$OUTPUT .= "\r\n\t\t\t" . TBL_BR . "\r\n\t\t\t<tr>\r\n\t\t\t\t<td colspan='4' align='center'><input type=button value='Select Date Range' onClick=\"javascript:document.location.href='pettycash-rep.php'\"></td>\r\n\t\t\t</tr>\r\n\t\t</table>\r\n\t\t<p>\r\n\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Quick Links</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td><a href='petty-req-add.php'>Add Petty Cash Requisition</a></td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td><a href='pettycashbook-view.php'>View Petty Cash Requisitions</a></td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td><a href='../main.php'>Main Menu</a></td>\r\n\t\t\t</tr>\r\n\t\t</table>";
return $OUTPUT;
}
function bank($_POST)
{
# Get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cashid, "num", 1, 20, "Invalid Reference number.");
$v->isOk("{$date_day}{$date_month}{$date_year}", "num", 6, 8, "Invalid date selected.");
if (!checkdate($date_month, $date_day, $date_year)) {
$v->addError("", "Invalid date selected. No such date possible.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
$seldate = "{$date_year}-{$date_month}-{$date_day}";
$salconacc = gethook("accnum", "salacc", "name", "salaries control");
# Get cash book record
Db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid = '{$cashid}' AND div = '" . USER_DIV . "'";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database11.", SELF);
if (pg_numrows($accntRslt) < 1) {
$OUTPUT = "<li clss='err'>The entry with reference number, <b>{$cashid}</b> was not found in Cubit.</li>";
return $OUTPUT;
}
$accnt = pg_fetch_array($accntRslt);
# get hook account number
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$accnt['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.</li>";
}
$bank = pg_fetch_array($rslt);
# Date
$sdate = date("Y-m-d");
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# If tis customer payment
if (($accnt['cusnum'] > 0 || $accnt["multicusnum"] != "") && strlen($accnt['rinvids']) > 0) {
db_connect();
# Get invoice Ids and Amounts
$invids = explode("|", $accnt['rinvids']);
$amounts = explode("|", $accnt['amounts']);
$invprds = explode("|", $accnt['invprds']);
$rages = explode("|", $accnt['rages']);
if ($accnt["multicusnum"] != "") {
$cusnums = explode(",", $accnt["multicusnum"]);
$cusamts = explode(",", $accnt["multicusamt"]);
} else {
$cusnums = array($accnt["cusnum"]);
$cusamts = array($accnt["amount"]);
}
$oa = 0;
# Return the amount that was surppose to be paid to invoices
foreach ($invids as $key => $invid) {
if ($invids[$key] <= 0) {
continue;
}
db_connect();
if (ext_ex("invoices", "invid", $invids[$key]) && $invprds[$key] != 0) {
db_connect();
$sql = "\n\t\t\t\t\tUPDATE invoices \n\t\t\t\t\tSET balance = (balance + '{$amounts[$key]}'::numeric(13,2)) \n\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
if (open()) {
$sql = "SELECT invnum FROM invoices WHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$idata = pg_fetch_array($payRslt);
$sql = "\n\t\t\t\t\t\tUPDATE open_stmnt \n\t\t\t\t\t\tSET balance = (balance + '{$amounts[$key]}'::numeric(13,2)) \n\t\t\t\t\t\tWHERE invid = '{$idata['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$oa = $oa - $amounts[$key];
}
} else {
if (ext_ex("nons_invoices", "invid", $invids[$key]) && $invprds[$key] == 0) {
db_connect();
$sql = "\n\t\t\t\t\tUPDATE nons_invoices \n\t\t\t\t\tSET balance = (balance + '{$amounts[$key]}'::numeric(13,2)) \n\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
/*$Sll="SELECT sdate FROM nons_invoices WHERE invid = '$invids[$key]' AND div = '".USER_DIV."'";
$Rii=db_exec($Sll) or errDie("Unable to get invoice data.");
$dii=pg_fetch_array($Rii);*/
$cnsql = "SELECT cusid FROM cubit.nons_invoices WHERE invid='{$invids[$key]}'";
$cnrslt = db_exec($cnsql) or errDie("Error reading customer info from nonstock invoice.");
$invcusid = pg_fetch_result($cnrslt, 0, 0);
custDTA($amounts[$key], $invcusid, $rages[$key], $seldate);
} else {
if ($invprds[$key] != 0 && ext_ex("pinvoices", "invid", $invids[$key], $invprds[$key])) {
$sql = "\n\t\t\t\t\tUPDATE \"{$invprds[$key]}\".pinvoices \n\t\t\t\t\tSET balance = (balance + '{$amounts[$key]}'::numeric(13,2)) \n\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "SELECT cusnum, balance FROM \"{$invprds[$key]}\".pinvoices WHERE invid='{$invids[$key]}'";
$rslt = db_exec($sql) or errDie("Error reading customer info from nonstock invoice.");
$invcusid = pg_fetch_result($rslt, 0, 0);
custDTA($amounts[$key], $invcusid, $rages[$key], $seldate);
} else {
if ($invprds[$key] > 0) {
if (open()) {
db_conn($invprds[$key]);
$sql = "SELECT invnum FROM invoices WHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$idata = pg_fetch_array($payRslt);
db_conn('cubit');
$sql = "\n\t\t\t\t\t\tUPDATE open_stmnt \n\t\t\t\t\t\tSET balance = (balance + '{$amounts[$key]}'::numeric(13,2)) \n\t\t\t\t\t\tWHERE invid = '{$idata['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$oa = $oa - $amounts[$key];
}
db_conn($invprds[$key]);
# check if invoice exitsts on prd
if (ext_ex("invoices", "invid", $invids[$key])) {
# if found, Move the invoice back
if (moveback($invids[$key], $invprds[$key], $amounts[$key])) {
}
}
}
}
}
}
}
foreach ($cusnums as $cuskey => $cusnum) {
$accnt["cusnum"] = $cusnum;
$cusamt = $cusamts[$cuskey];
db_connect();
# Update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$cusamt}'::numeric(13,2)) \n\t\t\t\t\tWHERE cusnum = '{$accnt['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit1.", SELF);
# Record the transaction on the statement
$sql = "\n\t\t\t\tINSERT INTO stmnt (\n\t\t\t\t\tcusnum, invid, amount, date, type, \n\t\t\t\t\tdiv, allocation_date\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$accnt['cusnum']}', '0', '{$cusamt}', '{$seldate}', 'Cheque/Payment for Invoices Returned.', \n\t\t\t\t\t'" . USER_DIV . "', '{$accnt['date']}'\n\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
if (sprint($accnt['amount'] + $oa) > 0) {
# Record the transaction on the statement
$sql = "\n\t\t\t\t\tINSERT INTO open_stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\ttype, div, balance\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$accnt['cusnum']}', '0', '" . sprint($accnt['amount'] + $oa) . "', '{$seldate}', \n\t\t\t\t\t\t'Cheque/Payment for Invoices Returned.', '" . USER_DIV . "', '{$cusamt}'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
}
}
# Delete cashbook ID
$sql = "UPDATE cashbook SET opt = 'n' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
copyEntry($cashid);
if ($accnt['lcashid'] > 0) {
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$laccntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database.2", SELF);
$laccnt = pg_fetch_array($laccntRslt);
$sql = "\n\t\t\t\tUPDATE bankacct \n\t\t\t\tSET fbalance = (fbalance + '{$laccnt['famount']}'::numeric(13,2)), balance = (balance + '{$laccnt['amount']}'::numeric(13,2)) \n\t\t\t\tWHERE bankid = '{$laccnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit2.", SELF);
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
}
# Make ledge record
// custledger($accnt['cusnum'], $bank['accnum'], $accnt['date'], "cancel", "Payment Returned.", $accnt['amount'], "d");
foreach ($cusnums as $cuskey => $cusnum) {
$cusamt = $cusamts[$cuskey];
custledger($cusnum, $bank['accnum'], $seldate, "cancel", "Payment Returned.", $cusamt, "d");
}
$descript = $accnt['descript'] . " Returned, Unpaid";
$refnum = getrefnum();
$date = date("Y-m-d");
# debit customer account, credit bank account (customer takes money back)
// writetrans($accnt['accinv'], $bank['accnum'], $accnt['date'], $refnum, $accnt['amount'], $descript);
writetrans($accnt['accinv'], $bank['accnum'], $seldate, $refnum, $accnt['amount'], $descript);
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
if ($accnt['vat'] != 0) {
# DT(VAT), CT(Bank)
writetrans($vatacc, $bank['accnum'], $accnt['date'], $accnt['reference'], $vat, $accnt['descript']);
}
} else {
if (($accnt['cusnum'] > 0 || $accnt["multicusnum"] != "") && $accnt['trantype'] != "withdrawal") {
$refnum = getrefnum();
$date = date("Y-m-d");
// recordDT($accnt['amount'], $accnt['cusnum']);
recordCT($accnt['amount'], $accnt['cusnum']);
if ($accnt["multicusnum"] != "") {
$cusnums = explode(",", $accnt["multicusnum"]);
$cusamts = explode(",", $accnt["multicusamt"]);
} else {
$cusnums = array($accnt["cusnum"]);
$cusamts = array($accnt["amount"]);
}
db_connect();
foreach ($cusnums as $cuskey => $cusnum) {
$accnt["cusnum"] = $cusnum;
$cusamt = $cusamts[$cuskey];
# receipt from customer returned
$sql = "\n\t\t\t\t\tINSERT INTO stmnt \n\t\t\t\t\t\t(cusnum, invid, amount, date, type, st, div, allocation_date) \n\t\t\t\t\tVALUES \n\t\t\t\t\t\t('{$accnt['cusnum']}', '0', '{$cusamt}', '{$seldate}', 'Cheque/Payment returned', 'n', '" . USER_DIV . "', '{$accnt['date']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
$sql = "INSERT INTO open_stmnt(cusnum, invid, amount, date, type, st, div,balance) VALUES('{$accnt['cusnum']}', '0', '{$cusamt}', '{$seldate}', '{$accnt['descript']}, Cheque/Payment returned', 'n', '" . USER_DIV . "','{$cusamt}')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$cusamt}') WHERE cusnum = '{$accnt['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF);
}
copyEntry($cashid);
foreach ($cusnums as $cuskey => $cusnum) {
$cusamt = $cusamts[$cuskey];
# Make ledge record
// custledger($accnt['cusnum'], $bank['accnum'], $accnt['date'], $refnum, "Cheque/Payment returned.", $accnt['amount'], "c");
custledger($cusnum, $bank['accnum'], $seldate, $refnum, "Cheque/Payment returned.", $cusamt, "d");
}
db_conn('cubit');
$sql = "UPDATE cashbook SET opt = 'n' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
// writetrans ($accnt['accinv'],$bank['accnum'], $accnt['date'], $refnum, $accnt['amount'], "Cheque/Payment returned.$accnt[descript]");
writetrans($accnt['accinv'], $bank['accnum'], $seldate, $refnum, $accnt['amount'], "Cheque/Payment returned.{$accnt['descript']}");
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
if ($accnt['vat'] != 0) {
# DT(VAT), CT(Bank)
writetrans($vatacc, $bank['accnum'], $accnt['date'], $accnt['reference'], $vat, $accnt['descript']);
}
} elseif ($accnt['cusnum'] > 0) {
$refnum = getrefnum();
$date = date("Y-m-d");
recordCT($accnt['amount'], $accnt['cusnum']);
db_connect();
# receipt from customer returned
$sql = "\n\t\t\t\tINSERT INTO stmnt \n\t\t\t\t\t(cusnum, invid, amount, date, type, st, div, allocation_date) \n\t\t\t\tVALUES \n\t\t\t\t\t('{$accnt['cusnum']}', '0', '-{$accnt['amount']}', '{$seldate}', 'Cheque/Payment returned', 'n', '" . USER_DIV . "', '{$accnt['date']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
$sql = "INSERT INTO open_stmnt(cusnum, invid, amount, date, type, st, div,balance) VALUES('{$accnt['cusnum']}', '0', '-{$accnt['amount']}', '{$seldate}', '{$accnt['descript']}, Cheque/Payment returned', 'n', '" . USER_DIV . "','-{$accnt['amount']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance - '{$accnt['amount']}') WHERE cusnum = '{$accnt['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF);
copyEntry($cashid);
# Make ledge record
custledger($accnt['cusnum'], $bank['accnum'], $accnt['date'], $refnum, "Cheque/Payment returned.", $accnt['amount'], "c");
db_conn('cubit');
$sql = "UPDATE cashbook SET opt = 'n' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
// writetrans ($bank['accnum'],$accnt['accinv'], $date, $refnum, $accnt['amount'], "Cheque/Payment returned.$accnt[descript]");
writetrans($bank['accnum'], $accnt['accinv'], $accnt['date'], $refnum, $accnt['amount'], "Cheque/Payment returned.{$accnt['descript']}");
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
if ($accnt['vat'] != 0) {
# DT(VAT), CT(Bank)
writetrans($vatacc, $bank['accnum'], $accnt['date'], $accnt['reference'], $vat, $accnt['descript']);
}
} elseif ($accnt['supid'] > 0) {
db_connect();
$ids = explode("|", $accnt['ids']);
$purids = explode("|", $accnt['purids']);
$pamounts = explode("|", $accnt['pamounts']);
$pdates = explode("|", $accnt['pdates']);
if (count($ids) > 0) {
foreach ($ids as $key => $vale) {
if ($ids[$key] > 0) {
rerecord($ids[$key], $accnt['supid'], $purids[$key], $pamounts[$key], $pdates[$key]);
}
}
}
# if the amount was overpaid
if (array_sum($pamounts) < $accnt['amount']) {
# get and record amount that was overpaid to balance the equation
$rem = $accnt['amount'] - array_sum($pamounts);
rerecord('0', $accnt['supid'], '0', $rem, $accnt['date']);
}
# Update the supplier (make balance more)
$sql = "UPDATE suppliers SET balance = (balance + '{$accnt['amount']}'::numeric(13,2)) WHERE supid = '{$accnt['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit3.", SELF);
# Record the payment on the statement
$sql = "INSERT INTO sup_stmnt(supid, edate, cacc, ref, descript, amount, div) VALUES('{$accnt['supid']}', '{$seldate}', '{$bank['accnum']}', '{$accnt['cheqnum']}', 'Cheque/Payment to Supplier Returned.', '{$accnt['amount']}', '" . USER_DIV . "')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# Delete cashbook ID
$sql = "UPDATE cashbook SET opt = 'n' WHERE cashid = '{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
copyEntry($cashid);
if ($accnt['lcashid'] > 0) {
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$laccntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database3.", SELF);
$laccnt = pg_fetch_array($laccntRslt);
$sql = "UPDATE bankacct SET fbalance = (fbalance + '{$laccnt['famount']}'::numeric(13,2)), balance = (balance + '{$laccnt['amount']}'::numeric(13,2)) WHERE bankid = '{$laccnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.4", SELF);
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
}
suppledger($accnt['supid'], $bank['accnum'], $accnt['date'], $accnt['cheqnum'], "Payment to Supplier Returned", $accnt['amount'], "c");
db_connect();
$descript = $accnt['descript'] . " Returned, Unpaid";
$refnum = getrefnum();
$date = date("Y-m-d");
# debit bank, credit supplier account
writetrans($bank['accnum'], $accnt['accinv'], $accnt['date'], $refnum, $accnt['amount'], $descript);
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
if ($accnt['vat'] != 0) {
# DT(VAT), CT(Bank)
writetrans($vatacc, $bank['accnum'], $accnt['date'], $accnt['reference'], $vat, $accnt['descript']);
}
} elseif ($accnt['suprec'] > 0) {
db_connect();
$Sl = "INSERT INTO sup_stmnt(supid, amount, edate, descript,ref,cacc, div) VALUES('{$accnt['suprec']}','-{$accnt['amount']}','{$accnt['date']}', 'Receipt Returned','{$accnt['cheqnum']}','0', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the supplier (make balance less)
$sql = "UPDATE suppliers SET balance = (balance - '{$accnt['amount']}'::numeric(13,2)) WHERE supid = '{$accnt['suprec']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.5", SELF);
suppDT($accnt['amount'], $accnt['suprec']);
suppledger($accnt['suprec'], $bank['accnum'], $accnt['date'], $accnt['cheqnum'], "Receipt from Supplier Returned", $accnt['amount'], "d");
db_connect();
# Delete cashbook ID
$sql = "UPDATE cashbook SET opt = 'n' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
copyEntry($cashid);
if ($accnt['lcashid'] > 0) {
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
}
$descript = $accnt['descript'] . " Returned, Unpaid";
$refnum = getrefnum();
$date = date("Y-m-d");
# debit bank, credit supplier account
writetrans($accnt['accinv'], $bank['accnum'], $accnt['date'], $refnum, $accnt['amount'], $descript);
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
if ($accnt['vat'] != 0) {
# DT(VAT), CT(Bank)
writetrans($vatacc, $bank['accnum'], $accnt['date'], $accnt['reference'], $vat, $accnt['descript']);
}
} else {
if ($accnt["empnum"] != "0" && strlen($accnt["empnum"]) > 0 && $accnt['trantype'] == "withdrawal") {
$refnum = getrefnum();
$date = date("Y-m-d");
$sql = "UPDATE cubit.employees SET balance = balance + '{$accnt['amount']}' \n\t\t\t\tWHERE empnum='{$accnt['empnum']}' AND div = '" . USER_DIV . "'";
db_exec($sql) or errDie("Unable to get employee details.");
$sql = "SELECT fnames,sname FROM cubit.employees WHERE empnum='{$accnt['empnum']}'";
$rslt = db_exec($sql);
$empinfo = pg_fetch_array($rslt);
$empname = "{$empinfo['fnames']} {$empinfo['sname']}";
copyEntry($cashid);
empledger($accnt["empnum"], $bank['accnum'], $accnt["date"], $refnum, "Cheque/Payment Returned", $accnt['amount'], "c");
db_conn('cubit');
$sql = "UPDATE cashbook SET opt = 'n' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
writetrans($bank['accnum'], $accnt['accinv'], $accnt['date'], $refnum, $accnt['amount'], "Cheque/Payment returned for {$empname}");
} elseif (strlen($accnt['accids']) > 0) {
/* -- Start Hooks -- */
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
/* -- End Hooks -- */
multican($accnt, $bank, $vatacc, $accnt['vatcode']);
} else {
$amount = $accnt['amount'];
$vat = $accnt['vat'];
$chrgvat = $accnt['chrgvat'];
$amount -= $vat;
/* -- Start Hooks -- */
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
/* -- End Hooks -- */
db_connect();
# Delete cashbook ID
$sql = "UPDATE cashbook SET opt = 'n' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
copyEntry($cashid);
if ($accnt['trantype'] == "deposit") {
$sql = "UPDATE bankacct SET fbalance = (fbalance - '{$accnt['famount']}'::numeric(13,2)), balance = (balance - '{$accnt['amount']}'::numeric(13,2)) WHERE bankid = '{$accnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.5", SELF);
} else {
$sql = "UPDATE bankacct SET fbalance = (fbalance + '{$accnt['famount']}'::numeric(13,2)), balance = (balance + '{$accnt['amount']}'::numeric(13,2)) WHERE bankid = '{$accnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.6", SELF);
}
/* ---- the Others ---- */
if ($accnt['lcashid'] > 0) {
//Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$laccntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database.4", SELF);
$laccnt = pg_fetch_array($laccntRslt);
if ($laccnt['trantype'] == "deposit") {
$sql = "UPDATE bankacct SET fbalance = (fbalance - '{$laccnt['famount']}'::numeric(13,2)), balance = (balance - '{$laccnt['amount']}'::numeric(13,2)) WHERE bankid = '{$laccnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.7", SELF);
} else {
$sql = "UPDATE bankacct SET fbalance = (fbalance + '{$laccnt['famount']}'::numeric(13,2)), balance = (balance + '{$laccnt['amount']}'::numeric(13,2)) WHERE bankid = '{$laccnt['bankid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.8", SELF);
}
# Delete cashbook ID
$sql = "DELETE FROM cashbook WHERE cashid = '{$accnt['lcashid']}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
/* ---- End the Others ---- */
}
$descript = $accnt['descript'] . " Returned, Unpaid";
$refnum = getrefnum();
$date = date("Y-m-d");
if ($accnt['trantype'] == "deposit") {
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "a");
# DT(account involved), CT(bank)
// writetrans($accnt['accinv'], $bank['accnum'], $accnt['date'], $refnum, $amount, $descript);
writetrans($accnt['accinv'], $bank['accnum'], $seldate, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$accnt['vatcode']}'";
$Ri = db_exec($Sl);
$vd = pg_fetch_array($Ri);
// vatr($vd['id'],$date,"OUTPUT",$vd['code'],$refnum,$descript,-($amount+$vat),-$vat);
vatr($vd['id'], $seldate, "OUTPUT", $vd['code'], $refnum, $descript, -($amount + $vat), -$vat);
// writetrans($vatacc, $bank['accnum'], $accnt['date'], $refnum, $vat, $descript);
writetrans($vatacc, $bank['accnum'], $seldate, $refnum, $vat, $descript);
}
$cc_trantype = cc_TranTypeAcc($accnt['accinv'], $bank['accnum']);
} else {
# DT(bank), CT(account invoilved)
// writetrans($bank['accnum'], $accnt['accinv'], $accnt['date'], $refnum, $amount, $descript);
writetrans($bank['accnum'], $accnt['accinv'], $seldate, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$accnt['vatcode']}'";
$Ri = db_exec($Sl);
$vd = pg_fetch_array($Ri);
// vatr($vd['id'],$date,"INPUT",$vd['code'],$refnum,$descript,($amount+$vat),$vat);
vatr($vd['id'], $seldate, "INPUT", $vd['code'], $refnum, $descript, $amount + $vat, $vat);
// writetrans($bank['accnum'], $vatacc, $accnt['date'], $refnum, $vat, $descript);
writetrans($bank['accnum'], $vatacc, $seldate, $refnum, $vat, $descript);
}
$cc_trantype = cc_TranTypeAcc($bank['accnum'], $accnt['accinv']);
}
/* stock purchase/sale */
if (!empty($accnt["stkinfo"])) {
list($si_stkid, $si_unitnum, $si_cost, $si_vat) = explode("|", $accnt["stkinfo"]);
db_connect();
$sql = "SELECT * FROM stock WHERE stkid = '{$si_stkid}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
$stk = pg_fetch_array($stkRslt);
if ($accnt['trantype'] == "deposit") {
db_connect();
$sql = "UPDATE stock SET csamt = (csamt + '{$si_cost}'), \n\t\t\t\t\t\t\tunits = (units + '{$si_unitnum}') \n\t\t\t\t\t\tWHERE stkid = '{$si_stkid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
stockrec($stk['stkid'], $stk['stkcod'], $stk['stkdes'], 'dt', $seldate, $si_unitnum, $si_cost, "Returned receipt for: {$accnt['descript']}");
db_connect();
$cspric = sprint($si_cost / $si_unitnum);
$sql = "INSERT INTO stockrec(edate, stkid, stkcod, stkdes, trantype, qty, csprice, csamt, details, div)\n\t\t\t\t\t\tVALUES('{$seldate}', '{$stk['stkid']}', '{$stk['stkcod']}', '{$stk['stkdes']}', 'inc', '{$si_unitnum}', '{$si_cost}', '{$cspric}', 'Returned receipt for: {$accnt['descript']}', '" . USER_DIV . "')";
$recRslt = db_exec($sql);
db_connect();
$sql = "SELECT * FROM stock WHERE stkid = '{$si_stkid}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
$stk = pg_fetch_array($stkRslt);
if ($stk['units'] != 0) {
$sql = "UPDATE stock SET csprice = (csamt/units) WHERE stkid = '{$si_stkid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
} else {
$csprice = sprint($si_cost / $si_unitnum);
$sql = "UPDATE stock SET csprice = '{$csprice}' WHERE stkid = '{$si_stkid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
}
} else {
db_connect();
$sql = "UPDATE stock SET csamt = (csamt - {$si_cost}), \n\t\t\t\t\t\t\tunits = (units - '{$si_unitnum}') \n\t\t\t\t\t\tWHERE stkid = '{$si_stkid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
stockrec($stk['stkid'], $stk['stkcod'], $stk['stkdes'], 'ct', $seldate, $si_unitnum, $si_cost, "Returned payment for: {$accnt['descript']}");
db_connect();
$cspric = sprint($si_cost / $si_unitnum);
$sql = "INSERT INTO stockrec(edate, stkid, stkcod, stkdes, trantype, qty, csprice, csamt, details, div)\n\t\t\t\t\t\tVALUES('{$seldate}', '{$stk['stkid']}', '{$stk['stkcod']}', '{$stk['stkdes']}', 'dec', '-{$si_unitnum}', '{$si_cost}', '{$cspric}', 'Returned payment for: {$accnt['descript']}', '" . USER_DIV . "')";
$recRslt = db_exec($sql);
db_connect();
$sql = "SELECT * FROM stock WHERE stkid = '{$si_stkid}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
$stk = pg_fetch_array($stkRslt);
if ($stk['units'] != 0) {
$sql = "UPDATE stock SET csprice = (csamt/units) WHERE stkid = '{$si_stkid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
} else {
$csprice = sprint($si_cost / $si_unitnum);
$sql = "UPDATE stock SET csprice = '{$csprice}' WHERE stkid = '{$si_stkid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
}
}
}
}
}
}
if (isset($cc_trantype) && $cc_trantype != false) {
$cc = "<script> CostCenter('{$cc_trantype}', 'Returned, Unpaid Bank Transaction', '{$seldate}', '{$descript}', '" . ($accnt['amount'] - $accnt['vat']) . "', '../'); </script>";
} else {
$cc = "";
}
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
# Status report
$bank = "\n\t\t\t{$cc}\n\t\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Cash Book</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td>Cash Book Entry was successfully canceled .</td>\n\t\t\t\t</tr>\n\t\t\t</table>";
# Main table (layout with menu)
$OUTPUT = "\n\t\t\t<center>\n\t\t\t<table width='90%'>\n\t\t\t\t<tr valign='top'>\n\t\t\t\t\t<td width='60%'>{$bank}</td>\n\t\t\t\t\t<td align='center'>\n\t\t\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td align='center'><a href='cashbook-view.php'>View Cash Book</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td align='center'><a href='../reporting/not-banked.php'>View Outstanding Cash Book Entries</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td align='center'><a href='bank-pay-add.php'>Add Bank Payment</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td align='center'><a href='bank-recpt-add.php'>Add Bank Receipt</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t\t\t\t</table>\n\t\t\t\t\t</td>\n\t\t\t\t</tr>\n\t\t\t</table>";
return $OUTPUT;
}
function viewcash($_POST)
{
# get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
$v->isOk($fday, "num", 1, 2, "Invalid Day for the 'From' date.");
$v->isOk($fmonth, "num", 1, 2, "Invalid month for the 'From' date..");
$v->isOk($fyear, "num", 1, 4, "Invalid year for the 'From' date..");
$v->isOk($lday, "num", 1, 2, "Invalid Day for the 'To' date.");
$v->isOk($lmonth, "num", 1, 2, "Invalid month for the 'To' date..");
$v->isOk($lyear, "num", 1, 4, "Invalid year for the 'To' date..");
# lets mix the date
$from = sprintf("%02.2d", $fday) . "-" . sprintf("%02.2d", $fmonth) . "-" . $fyear;
$to = sprintf("%02.2d", $lday) . "-" . sprintf("%02.2d", $lmonth) . "-" . $lyear;
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# get bank details
$bankRslt = get("cubit", "accname,bankname", "bankacct", "bankid", $bankid);
$bank = pg_fetch_array($bankRslt);
// Set up table to display in
# Receipts
$OUTPUT = "<center><h3>Cash Book<br><br>Account : {$bank['accname']} - {$bank['bankname']}<br>Period : {$from} to {$to}</h3>\n <table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n <tr><th>Date</th><th>Received From/Paid to : </th><th>Description</th><th>Deposits</th><th>Withdrawals</th><th>Ledger Account</th></tr>";
// Connect to database
db_Connect();
# date format
$from = explode("-", $from);
$from = $from[2] . "-" . $from[1] . "-" . $from[0];
$to = explode("-", $to);
$to = $to[2] . "-" . $to[1] . "-" . $to[0];
$sql = "SELECT * FROM cashbook WHERE date >= '{$from}' AND date <= '{$to}' AND bankid='{$bankid}' ORDER BY date DESC";
$tranRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
if (pg_numrows($tranRslt) < 1) {
$OUTPUT .= "<tr><td colspan=7 align=center><li class=err>Bank Transactions on the selected period.</td></tr>";
} else {
# display all bank Deposits
for ($i = 0; $tran = pg_fetch_array($tranRslt); $i++) {
# get account name for account involved
$accRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $tran['accinv']);
$acc = pg_fetch_array($accRslt);
# format date
$tran['date'] = explode("-", $tran['date']);
$tran['date'] = $tran['date'][2] . "-" . $tran['date'][1] . "-" . $tran['date'][0];
# $rtotal += $accnt['amount']; // add to rtotal
$OUTPUT .= "<tr class='" . bg_class() . "'><td>{$tran['date']}</td><td>{$tran['name']}</td><td>{$tran['descript']}</td>";
if ($tran['trantype'] == "deposit") {
$OUTPUT .= "<td>" . CUR . " {$tran['amount']}</td><td></td>";
} elseif ($tran['trantype'] == "withdrawal") {
$OUTPUT .= "<td></td><td>" . CUR . " {$tran['amount']}</td>";
}
$OUTPUT .= "<td>{$acc['topacc']}/{$acc['accnum']} {$acc['accname']}</td></tr>";
}
}
# Seperate the tables with two rows
$OUTPUT .= "<tr><td colspan=7><br></td></tr><tr><td colspan=7><br></td></tr>\n </table>\n <p>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t <tr><th>Quick Links</th></tr>\n \t <script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
return $OUTPUT;
}
function viewcash($_POST)
{
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
$v->isOk($f_day, "num", 1, 2, "Invalid Day for the 'From' date.");
$v->isOk($f_month, "num", 1, 2, "Invalid month for the 'From' date..");
$v->isOk($f_year, "num", 1, 4, "Invalid year for the 'From' date..");
$v->isOk($l_day, "num", 1, 2, "Invalid Day for the 'To' date.");
$v->isOk($l_month, "num", 1, 2, "Invalid month for the 'To' date..");
$v->isOk($l_year, "num", 1, 4, "Invalid year for the 'To' date..");
# lets mix the date
$from = mkdate($f_year, $f_month, $f_day);
$to = mkdate($l_year, $l_month, $l_day);
if ($v->isError()) {
$err = $v->genErrors();
return $err;
}
if (isset($export)) {
$pure = true;
} else {
$pure = false;
}
$bank = qryBankAcct($bankid);
$curdata = qryCurrency($bank["fcid"]);
$fc = $curdata['symbol'];
$s1 = "";
$s2 = "";
$s3 = "";
$s4 = "";
$s5 = "";
if (isset($order)) {
if ($order == "ORDER BY date ASC, cheqnum ASC") {
$s2 = "selected";
} elseif ($order == "ORDER BY date DESC, cheqnum DESC") {
$s3 = "selected";
} elseif ($order == "ORDER BY cheqnum ASC") {
$s4 = "selected";
} elseif ($order == "ORDER BY cheqnum DESC") {
$s5 = "selected";
} else {
$s1 = "selected";
}
} else {
$order = "ORDER BY date DESC, cheqnum ASC";
$s1 = "selected";
}
// Set up table to display in
# Receipts
$OUTPUT = "\n\t\t<center>\n\t\t<table " . TMPL_tblDflts . " width='95%'>\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>Cash Book<br><br>Account : {$bank['accname']} - {$bank['bankname']}<br>Period : {$from} to {$to}</h3></td>\n\t\t\t</tr>";
if (!$pure) {
$OUTPUT .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'>\n\t\t\t\t\t<form action='" . SELF . "' method='POST' name='form'>\n\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t<input type='hidden' name='key' value='viewcash'>\n\t\t\t\t\t\t<input type='hidden' name='bankid' value='{$bankid}'>\n\t\t\t\t\t\t<input type='hidden' name='f_day' value='{$f_day}'>\n\t\t\t\t\t\t<input type='hidden' name='f_month' value='{$f_month}'>\n\t\t\t\t\t\t<input type='hidden' name='f_year' value='{$f_year}'>\n\t\t\t\t\t\t<input type='hidden' name='l_day' value='{$l_day}'>\n\t\t\t\t\t\t<input type='hidden' name='l_month' value='{$l_month}'>\n\t\t\t\t\t\t<input type='hidden' name='l_year' value='{$l_year}'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Order By</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>\n\t\t\t\t\t\t\t\t<select name='order' onChange='javascript:document.form.submit();'>\n\t\t\t\t\t\t\t\t\t<option value='' disabled {$s1} >Select</option>\n\t\t\t\t\t\t\t\t\t<option value='ORDER BY date ASC, cheqnum ASC' {$s2}>Date, Cheque No. Ascending</option>\n\t\t\t\t\t\t\t\t\t<option value='ORDER BY date DESC, cheqnum DESC' {$s3}>Date, Cheque No. Descending</option>\n\t\t\t\t\t\t\t\t\t<option value='ORDER BY cheqnum ASC' {$s4}>Cheque No. Ascending</option>\n\t\t\t\t\t\t\t\t\t<option value='ORDER BY cheqnum DESC' {$s5}>Cheque No. Descending</option>\n\t\t\t\t\t\t\t\t</select>\n\t\t\t\t\t\t\t</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 align='center'><input type='submit' name='export' value='Export to Spreadsheet'></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</form>\n\t\t\t\t\t</table>\n\t\t\t\t</td>\n\t\t\t</tr>";
}
$OUTPUT .= "\n\t\t<tr>\n\t\t\t<td colspan='7'><h4>Receipts</h4></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Date</th>\n\t\t\t<th width='20%'>Bank Account Name</th>\n\t\t\t<th width='5%'>Cheque Number</th>\n\t\t\t<th width='15%'>Received From : </th>\n\t\t\t<th width='20%'>Description</th>\n\t\t\t<th>Reference</th>\n\t\t\t<th width='21%'>Ledger Account</th>\n\t\t\t<th width='23%'>Amount</th>\n\t\t</tr>";
$rtotal = 0;
// Received total amount
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE date >= '{$from}' AND date <= '{$to}' AND trantype='deposit' AND bankid='{$bankid}' AND div = '" . USER_DIV . "' {$order}";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
$OUTPUT .= "<tr><td colspan='7' align='center'><li class='err'>There are no Payments/cheques received on the selected period.</td></tr>";
} else {
# display all bank Deposits
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "<a href=\"javascript: openSmallWindow('multi-acc-popup.php?cashid={$accnt['cashid']}&type=cash')\">Multiple Accounts</a>";
$acc['accno'] = "";
} else {
# Get account name for the account involved
$AccRslt = get("core", "accname, topacc, accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
$acc['accno'] = "{$acc['topacc']}/{$acc['accnum']}";
}
# Get account name for bank account
db_connect();
$sql = "SELECT accname,btype FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
$rtotal += $accnt['amount'];
// add to rtotal
$accnt['amount'] = sprint($accnt['amount']);
$accnt['date'] = ext_rdate($accnt['date']);
if ($bname['btype'] != "loc") {
$ex = "/ {$fc} {$accnt['famount']}";
} else {
$ex = "";
}
if (empty($accnt["multicusnum"])) {
$from_disp = "{$accnt['name']}";
} else {
$from_disp = "<a href=\"javascript: openSmallWindow('multi-debtor-popup.php?cashid={$accnt['cashid']}&type=cash')\">Multiple Debtors</a>";
}
$OUTPUT .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>{$accnt['date']}</td>\n\t\t\t\t\t<td align='center'>{$bname['accname']}</td>\n\t\t\t\t\t<td align='center'>{$accnt['cheqnum']}</td>\n\t\t\t\t\t<td align='center'>{$from_disp}</td>\n\t\t\t\t\t<td>{$accnt['descript']}</td>\n\t\t\t\t\t<td>{$accnt['reference']}</td>\n\t\t\t\t\t<td>{$acc['accno']} {$acc['accname']}</td>\n\t\t\t\t\t<td>" . CUR . " {$accnt['amount']} {$ex}</td>\n\t\t\t\t\t<td><a href='#' onClick=\"printer ('bank/bank-recpt-inv-print.php?recid={$accnt['cashid']}');\">Print</a></td>";
if (!$pure && $accnt['banked'] == "no" && $accnt['opt'] != 'n') {
$OUTPUT .= "<td><a href='../bank/cheq-return.php?cashid={$accnt['cashid']}'>Returned/Unpaid</td>";
// $OUTPUT .= "<td><a href='../bank/cheq-cancel.php?cashid=$accnt[cashid]'>Cancel</td>";
}
$OUTPUT .= "</tr>";
}
# print the total
$OUTPUT .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='6'><b>Total Receipts</b></td>\n\t\t\t\t<td><b>" . CUR . " " . sprintf("%01.2f", $rtotal) . "</b></td>\n\t\t\t</tr>";
}
# Seperate the tables with two rows
$OUTPUT .= "<tr><td colspan='7'><br></td></tr><tr><td colspan='7'><br></td></tr>";
# Payments
$OUTPUT .= "\n\t\t<tr>\n\t\t\t<td colspan='7'><h4>Payments</h4></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Date</th>\n\t\t\t<th>Bank Account Name</th>\n\t\t\t<th>Cheque Number</th>\n\t\t\t<th>Paid to: </th>\n\t\t\t<th>Description</th>\n\t\t\t<th>Reference</th>\n\t\t\t<th>Ledger Account</th>\n\t\t\t<th>Amount</th>\n\t\t</tr>";
$ptotal = 0;
// payments total
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE date >= '{$from}' AND date <= '{$to}' AND trantype='withdrawal' AND bankid='{$bankid}' AND div = '" . USER_DIV . "' {$order}";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
if (pg_numrows($accntRslt) < 1) {
$OUTPUT .= "<tr><td colspan='7' align='center'><li class='err'>There are no Payments made on the selected period.</td></tr>";
} else {
# Display all bank Deposits
for ($i = 0; $accnt = pg_fetch_array($accntRslt); $i++) {
# alternate bgcolor
$bgColor = bgcolorc($i);
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "<a href=\"javascript: openSmallWindow('multi-acc-popup.php?cashid={$accnt['cashid']}&type=cash');\">Multiple Accounts</a>";
$acc['accno'] = "";
} else {
# get account name for the account involved
$AccRslt = get("core", "accname, topacc, accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
$acc['accno'] = "{$acc['topacc']}/{$acc['accnum']}";
}
# get account name for bank account
db_connect();
$sql = "SELECT accname,btype FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
$ptotal += $accnt['amount'];
//add to total
$accnt['amount'] = sprint($accnt['amount']);
$accnt['date'] = ext_rdate($accnt['date']);
if ($bname['btype'] != "loc") {
$ex = "/ {$fc} {$accnt['famount']}";
} else {
$ex = "";
}
$OUTPUT .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$accnt['date']}</td>\n\t\t\t\t<td align='center'>{$bname['accname']}</td>\n\t\t\t\t<td align='center'>{$accnt['cheqnum']}</td>\n\t\t\t\t<td align='center'>{$accnt['name']}</td>\n\t\t\t\t<td>{$accnt['descript']}</td>\n\t\t\t\t<td>{$accnt['reference']}</td>\n\t\t\t\t<td>{$acc['accno']} {$acc['accname']}</td>\n\t\t\t\t<td>" . CUR . " {$accnt['amount']} {$ex}</td>";
if (!$pure && $accnt['banked'] == "no" && $accnt['opt'] != 'n') {
$OUTPUT .= "<td><a href='../bank/cheq-return.php?cashid={$accnt['cashid']}'>Returned/Unpaid</td>";
// $OUTPUT .= "<td><a href='../bank/cheq-cancel.php?cashid=$accnt[cashid]'>Cancel</td>";
}
$OUTPUT .= "</tr>";
}
# print the total
$OUTPUT .= "\n\t\t<tr class='" . bg_class() . "''>\n\t\t\t<td colspan='6'><b>Total Payments</b></td>\n\t\t\t<td><b>" . CUR . " " . sprintf("%01.2f", $ptotal) . "</b></td>\n\t\t</tr>";
}
if (!$pure) {
$OUTPUT .= mkQuickLinks(ql("../core/acc-new2.php", "Add New Account"), ql("../core/acc-new2.php", "Add New Account (New Window)", true));
}
if (isset($export)) {
$OUTPUT = clean_html($OUTPUT);
require_lib("xls");
StreamXLS("Cashbook", $OUTPUT);
}
return $OUTPUT;
}
function write($_POST)
{
# get vars
extract($_POST);
$vatcode += 0;
$vat += 0;
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cashid, "num", 1, 4, "Invalid Reference number.");
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Connect to database
db_Connect();
$sql = "SELECT * FROM pettycashbook WHERE cashid = '{$cashid}' AND div = '" . USER_DIV . "'";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database.", SELF);
$cash = pg_fetch_array($cashRslt);
# CHECK IF THIS DATE IS IN THE BLOCKED RANGE
$blocked_date_from = getCSetting("BLOCKED_FROM");
$blocked_date_to = getCSetting("BLOCKED_TO");
if (strtotime($cash['date']) >= strtotime($blocked_date_from) and strtotime($cash['date']) <= strtotime($blocked_date_to) and !user_is_admin(USER_ID)) {
return "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
$refnum = getrefnum($cash['date']);
# Check available funds
$cashacc = gethook("accnum", "bankacc", "name", "Petty Cash");
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}' AND zero='Yes'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
if (pg_num_rows($Ri) > 0) {
$cash['chrgvat'] = "exc";
}
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
$VATP = $vd['vat_amount'];
# If subtract vat
if ($cash['chrgvat'] == "inc") {
# get vat account
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
// $VATP = TAX_VAT;
//$VAT = sprint(($VATP/($VATP + 100)) * $cash['amount']);
$VAT = $vat;
$samount = $cash['amount'] - $VAT;
//date("Y-m-d")
# Write transaction
writetrans($cash['accid'], $cashacc, $cash['date'], $refnum, $samount, $cash['det']);
# Write VAT transaction
writetrans($vatacc, $cashacc, $cash['date'], $refnum, $VAT, "VAT, " . $cash['det']);
vatr($vd['id'], $cash['date'], "INPUT", $vd['code'], $refnum, "VAT, " . $cash['det'], -($samount + $VAT), -$VAT);
# record vat statement
/*
db_connect();
$sql = "INSERT INTO svatrec(edate, ref, amount, descript, div) VALUES('".date("Y-m-d")."', '$refnum', '-$VAT', 'VAT paid on Petty Cash requisition.', '".USER_DIV."')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.",SELF);
*/
} elseif ($cash['chrgvat'] == "exc") {
# get vat account
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
// $VATP = TAX_VAT;
// $VAT = sprint(($VATP/100) * $cash['amount']);
$VAT = $vat;
$samount = $cash['amount'];
$cash['amount'] += $VAT;
# Write transaction
$VAT = $vat;
writetrans($cash['accid'], $cashacc, $cash['date'], $refnum, $samount, $cash['det']);
# Write VAT transaction
writetrans($vatacc, $cashacc, $cash['date'], $refnum, $VAT, "VAT, " . $cash['det']);
vatr($vd['id'], $cash['date'], "INPUT", $vd['code'], $refnum, "VAT, " . $cash['det'], -($samount + $VAT), -$VAT);
# record vat statement
/*
db_connect();
$sql = "INSERT INTO svatrec(edate, ref, amount, descript, div) VALUES('".date("Y-m-d")."', '$refnum', '-$VAT', 'VAT paid on Petty Cash requisition.', '".USER_DIV."')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.",SELF);
*/
} else {
$samount = $cash['amount'];
# Write transaction
writetrans($cash['accid'], $cashacc, $cash['date'], $refnum, $samount, $cash['det']);
}
# Update
db_connect();
// $date = date("Y-m-d");
$date = $cash['date'];
$sql = "\n\t\tINSERT INTO pettyrec (\n\t\t\tdate, type, det, amount, name, div\n\t\t) VALUES (\n\t\t\t'{$date}', 'Req', '{$cash['det']}', '-{$cash['amount']}', 'Cash Paid to : {$cash['name']}', '" . USER_DIV . "'\n\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
# Update
$sql = "UPDATE pettycashbook SET approved = 'y',vatcode='{$vatcode}', vat_paid = '{$vat}',reced = 'no' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to cancel cheque.", SELF);
if (cc_TranTypeAcc($cash['accid'], $cashacc) != false) {
$cc_trantype = cc_TranTypeAcc($cash['accid'], $cashacc);
$cc = "<script> CostCenter('{$cc_trantype}', 'Petty Cash Requisition', '{$date}', '{$cash['det']}', '{$samount}', '../'); </script>";
} else {
$cc = "";
}
# status report
$write = "\n\t\t{$cc}\n\t\t<table " . TMPL_tblDflts . " width='30%'>\n\t\t\t<tr>\n\t\t\t\t<th>Petty Cash Requisition Approved</th>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td>Petty Cash Requisition has been approved .</td>\n\t\t\t</tr>\n\t\t</table>\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . " width='15%'>\n\t\t\t<tr>\n\t\t\t\t<th>Quick Links</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><a href='petty-req-add.php'>Add Petty Cash Requisition</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><a href='pettycashbook-view.php'>View Petty Cash Requisitions</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><a href='../main.php'>Main Menu</a></td>\n\t\t\t</tr>\n\t\t</table>";
return $write;
}
function viewcash($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 20, "Invalid Bank Account Number.");
$v->isOk($from_day, "num", 1, 2, "Invalid Day for the 'From' date.");
$v->isOk($from_month, "num", 1, 2, "Invalid month for the 'From' date..");
$v->isOk($from_year, "num", 1, 4, "Invalid year for the 'From' date..");
$v->isOk($to_day, "num", 1, 2, "Invalid Day for the 'To' date.");
$v->isOk($to_month, "num", 1, 2, "Invalid month for the 'To' date..");
$v->isOk($to_year, "num", 1, 4, "Invalid year for the 'To' date..");
# lets mix the date
$from = sprintf("%02.2d", $from_day) . "-" . sprintf("%02.2d", $from_month) . "-" . $from_year;
$to = sprintf("%02.2d", $to_day) . "-" . sprintf("%02.2d", $to_month) . "-" . $to_year;
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# 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);
// Receipts
$OUTPUT = "\n\t\t\t<center>\n\t\t\t<h3>Cash Book : {$bank['accname']}<br><br>{$from} to {$to}</h3>\n\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='7'><h4>Analysis of Receipts</h4></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Bank Account Name</th>\n\t\t\t\t\t<th>Cheque Number</th>\n\t\t\t\t\t<th>Received From : </th>\n\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t<th>Ledger Account</th>\n\t\t\t\t\t<th>Amount</th>\n\t\t\t\t</tr>";
# date format
$from = explode("-", $from);
$from = $from[2] . "-" . $from[1] . "-" . $from[0];
$to = explode("-", $to);
$to = $to[2] . "-" . $to[1] . "-" . $to[0];
$rtotal = 0;
# Received total amount
# Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND date >= '{$from}' AND date <= '{$to}' AND trantype='deposit' AND banked='yes' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
$OUTPUT .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='7' align='center'><li class='err'>There are no Payments/cheques received on the selected period.</td>\n\t\t\t\t\t</tr>";
} else {
# display all bank Deposits
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "Multiple Accounts";
$acc['accnum'] = "";
$acc['topacc'] = "";
} else {
# get account name for the account involved
$AccRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
}
/*
# get account name for account involved
$accRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($accRslt);
*/
# get account name for bank account
db_connect();
$sql = "SELECT accname FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
# format date
$accnt['date'] = explode("-", $accnt['date']);
$accnt['date'] = $accnt['date'][2] . "-" . $accnt['date'][1] . "-" . $accnt['date'][0];
$rtotal += $accnt['amount'];
// add to rtotal
$accnt['amount'] = sprint($accnt['amount']);
$OUTPUT .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$accnt['date']}</td>\n\t\t\t\t\t\t\t<td>{$bname['accname']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['cheqnum']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['name']}</td>\n\t\t\t\t\t\t\t<td>{$accnt['descript']}</td>\n\t\t\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} {$acc['accname']}</td>\n\t\t\t\t\t\t\t<td>" . CUR . " {$accnt['amount']}</td>\n\t\t\t\t\t\t</tr>";
}
# print the total
$OUTPUT .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td colspan='6'><b>Total Receipts</b></td>\n\t\t\t\t\t\t<td><b>" . CUR . " " . sprintf("%01.2f", $rtotal) . "</b></td>\n\t\t\t\t\t</tr>";
}
# Seperate the tables with two rows
$OUTPUT .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='7'><br></td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='7'><br></td>\n\t\t\t</tr>";
# Payments
$OUTPUT .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='7'><h4>Analysis of Payments</h4></td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<th>Date</th>\n\t\t\t\t<th>Bank Account Name</th>\n\t\t\t\t<th>Cheque Number</th>\n\t\t\t\t<th>Paid to: </th>\n\t\t\t\t<th>Description</th>\n\t\t\t\t<th>Ledger Account</th>\n\t\t\t\t<th>Amount</th>\n\t\t\t</tr>";
$ptotal = 0;
# payments total
# Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE date >= '{$from}' AND date <= '{$to}' AND trantype='withdrawal' AND banked='yes' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
$OUTPUT .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='7' align='center'><li class='err'>There are no Payments made on the selected period.</td>\n\t\t\t\t</tr>";
} else {
# Display all bank Deposits
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "Multiple Accounts";
$acc['accnum'] = "";
$acc['topacc'] = "";
} else {
# get account name for the account involved
$AccRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
}
/*
# get account name for account involved
$accRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($accRslt);
*/
# get account name for bank account
db_connect();
$sql = "SELECT accname FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
# format date
$accnt['date'] = explode("-", $accnt['date']);
$accnt['date'] = $accnt['date'][2] . "-" . $accnt['date'][1] . "-" . $accnt['date'][0];
$ptotal += $accnt['amount'];
# add to total
$accnt['amount'] = sprint($accnt['amount']);
$OUTPUT .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$accnt['date']}</td>\n\t\t\t\t\t\t\t<td>{$bname['accname']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['cheqnum']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['name']}</td>\n\t\t\t\t\t\t\t<td>{$accnt['descript']}</td>\n\t\t\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} {$acc['accname']}</td>\n\t\t\t\t\t\t\t<td>" . CUR . " {$accnt['amount']}</td>\n\t\t\t\t\t\t</tr>";
}
# print the total
$OUTPUT .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td colspan='6'><b>Total Payments</b></td>\n\t\t\t\t\t\t<td><b>" . CUR . " " . sprintf("%01.2f", $ptotal) . "</b></td>\n\t\t\t\t\t</tr>";
}
$OUTPUT .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='7'><br></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td align='center' colspan='10'>\n\t\t\t\t\t\t<form action='../xls/banked-xls.php' method='POST' name='form'>\n\t\t\t\t\t\t\t<input type='hidden' name='key' value='viewcash'>\n\t\t\t\t\t\t\t<input type='hidden' name='bankid' value='{$bankid}'>\n\t\t\t\t\t\t\t<input type='hidden' name='fday' value='{$from_day}'>\n\t\t\t\t\t\t\t<input type='hidden' name='fmonth' value='{$from_month}'>\n\t\t\t\t\t\t\t<input type='hidden' name='fyear' value='{$from_year}'>\n\t\t\t\t\t\t\t<input type='hidden' name='lday' value='{$to_day}'>\n\t\t\t\t\t\t\t<input type='hidden' name='lmonth' value='{$to_month}'>\n\t\t\t\t\t\t\t<input type='hidden' name='lyear' value='{$to_year}'>\n\t\t\t\t\t\t\t<input type='submit' name='xls' value='Export to spreadsheet'>\n\t\t\t\t\t\t</form>\n\t\t\t\t\t</td>\n\t\t\t\t</tr>\n\t\t\t</table>\n\t\t\t<p>\n\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td><a href='index-reports.php'>Financials</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td><a href='index-reports-banking.php'>Banking Reports</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td><a href='../main.php'>Main Menu</a></td>\n\t\t\t\t</tr>\n\t\t\t</table>";
return $OUTPUT;
}
