function printCenter($_GET)
{
# Get vars
extract($_GET);
# Query server
db_connect();
$sql = "SELECT * FROM costcenters WHERE ccid = '{$ccid}'";
$ccRslt = db_exec($sql) or errDie("Unable to retrieve Cost centers from database.");
if (pg_numrows($ccRslt) < 1) {
return "<li class='err'> - Invalid Cost Center.";
}
$cc = pg_fetch_array($ccRslt);
$sp4 = " ";
# get income
$income = "";
db_conn($prd);
$sql = "SELECT * FROM cctran WHERE ccid = '{$cc['ccid']}' AND trantype = 'dt'";
$recRslt = db_exec($sql) or errDie("Unable to retrieve Cost Centers from database.");
if (pg_numrows($recRslt) > 0) {
$income = "\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td colspan='4'><h3>Income</h3></td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<th>Type</th>\r\n\t\t\t\t\t<th>Date</th>\r\n\t\t\t\t\t<th>Description</th>\r\n\t\t\t\t\t<th>Amount</th>\r\n\t\t\t\t\t<th>Posted By</th>\r\n\t\t\t\t</tr>";
$totinc = 0;
for ($i = 0; $rec = pg_fetch_array($recRslt); $i++) {
$totinc += $rec['amount'];
$rec['edate'] = ext_rdate($rec['edate']);
$income .= "\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td>{$rec['typename']}</td>\r\n\t\t\t\t\t\t<td>{$sp4} {$rec['edate']} {$sp4}</td>\r\n\t\t\t\t\t\t<td>{$rec['description']}</td>\r\n\t\t\t\t\t\t<td align='right'>{$sp4}" . CUR . " {$rec['amount']}</td>\r\n\t\t\t\t\t\t<td>{$rec['username']}</td>\r\n\t\t\t\t\t</tr>";
}
$totinc = sprint($totinc);
$income .= "\r\n\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t<td colspan='3' align='right'><b>Total</b></td>\r\n\t\t\t\t\t<td align='right'><b>" . CUR . " {$totinc}</b></td>\r\n\t\t\t\t\t<td><br></td>\r\n\t\t\t\t</tr>";
}
#get expenses
$expense = "";
db_conn($prd);
$sql = "SELECT * FROM cctran WHERE ccid = '{$cc['ccid']}' AND trantype = 'ct'";
$recRslt = db_exec($sql) or errDie("Unable to retrieve Cost Centers from database.");
if (pg_numrows($recRslt) > 0) {
$expense = "\r\n\t\t\t\t<tr><td><br></td></tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td colspan='4'><h3>Expenses</h3></td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<th>Type</th>\r\n\t\t\t\t\t<th>Date</th>\r\n\t\t\t\t\t<th>Description</th>\r\n\t\t\t\t\t<th>Amount</th>\r\n\t\t\t\t\t<th>Posted By</th>\r\n\t\t\t\t</tr>";
$totexp = 0;
for ($i = 0; $rec = pg_fetch_array($recRslt); $i++) {
$totexp += $rec['amount'];
$rec['edate'] = ext_rdate($rec['edate']);
$expense .= "\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td>{$rec['typename']}</td>\r\n\t\t\t\t\t\t<td>{$sp4} {$rec['edate']} {$sp4}</td>\r\n\t\t\t\t\t\t<td>{$rec['description']}</td>\r\n\t\t\t\t\t\t<td align='right'>{$sp4}" . CUR . " {$rec['amount']}</td>\r\n\t\t\t\t\t\t<td>{$rec['username']}</td>\r\n\t\t\t\t\t</tr>";
}
$totexp = sprint($totexp);
$expense .= "\r\n\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t<td colspan='3' align='right'><b>Total</b></td>\r\n\t\t\t\t\t<td align='right'><b>" . CUR . " {$totexp}</b></td>\r\n\t\t\t\t</tr>";
}
$printCenter = "\r\n\t\t\t\t<center>\r\n\t\t\t\t<h3>Cost Centers Detailed Report</h3>\r\n\t\t\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t<th>Cost Center</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>{$sp4} {$cc['centername']} ({$cc['centercode']}) {$sp4}</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 . ">\r\n\t\t\t\t\t{$income}\r\n\t\t\t\t\t{$expense}\r\n\t\t\t\t</table>\r\n\t\t\t\t<p>\r\n\t\t\t\t<form action='" . SELF . "' method='POST'>\r\n\t\t\t\t\t<input type='hidden' name='key' value='export'>\r\n\t\t\t\t\t<input type='hidden' name='ccid' value='{$ccid}'>\r\n\t\t\t\t\t<input type='hidden' name='prd' value='{$prd}'>\r\n\t\t\t\t\t<input type='submit' value='Export To Spreadsheet'>\r\n\t\t\t\t</form>\r\n\t\t\t\t<p>\r\n\t\t\t\t<p>\r\n\t\t\t\t<input type='button' value='[X] Close' onClick='javascript:window.close();'>";
return $printCenter;
}
function printBudget()
{
require "budget.lib.php";
# Set up table to display in
$printBudget = "\r\n\t\t\t\t\t\t<h3>Current Budgets</h3>\r\n\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<th>Budget Name</th>\r\n\t\t\t\t\t\t\t\t<th>Entry Date</th>\r\n\t\t\t\t\t\t\t\t<th>Type</th>\r\n\t\t\t\t\t\t\t\t<th>Budget For</th>\r\n\t\t\t\t\t\t\t\t<th>Period/Year</th>\r\n\t\t\t\t\t\t\t\t<th colspan='5'>Options</th>\r\n\t\t\t\t\t\t\t</tr>";
# connect to database
db_connect();
# Query server
$i = 0;
$sql = "SELECT * FROM budgets WHERE div = '" . USER_DIV . "' ORDER BY budid ASC";
$budgRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
if (pg_numrows($budgRslt) < 1) {
return "\r\n\t\t\t\t\t<li class='err'>There are no Budgets in Cubit.</li>\r\n\t\t\t\t\t<p>\r\n\t\t\t\t\t<table " . TMPL_tblDflts . " width='15%'>\r\n\t\t\t\t <tr><td><br></td></tr>\r\n\t\t\t\t <tr>\r\n\t\t\t\t \t<th>Quick Links</th>\r\n\t\t\t\t </tr>\r\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t<td><a href='budget-new.php'>New Monthly Budget</a></td>\r\n\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t<td><a href='budget-yr-new.php'>New Yearly Budget</a></td>\r\n\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t<td><a href='main.php'>Main Menu</a></td>\r\n\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t</table>\r\n\t\t\t\t";
}
while ($budg = pg_fetch_array($budgRslt)) {
$vbudtype = $TYPES[$budg['budtype']];
$vbudfor = $BUDFOR[$budg['budfor']];
$budg['edate'] = ext_rdate($budg['edate']);
if ($budg['prdtyp'] == "yr") {
$vfromprd = $YEARS[$budg['fromprd']];
$vtoprd = $YEARS[$budg['toprd']];
$det = "budget-yr-details.php";
$rem = "budget-yr-rem.php";
$edit = "budget-yr-edit.php";
$rep = "budget/budget-yr-report-print.php";
$exp = "budget-yr-export.php";
} else {
$vfromprd = $PERIODS[$budg['fromprd']];
$vtoprd = $PERIODS[$budg['toprd']];
$det = "budget-details.php";
$rem = "budget-rem.php";
$edit = "budget-edit.php";
$rep = "budget/budget-report-print.php";
$exp = "budget-export.php";
}
$printBudget .= "\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>{$budg['budname']}</td>\r\n\t\t\t\t\t\t\t\t<td>{$budg['edate']}</td>\r\n\t\t\t\t\t\t\t\t<td>{$vbudtype}</td>\r\n\t\t\t\t\t\t\t\t<td>{$vbudfor}</td>\r\n\t\t\t\t\t\t\t\t<td>{$vfromprd} to {$vtoprd}</td>\r\n\t\t\t\t\t\t\t\t<td><a href='{$det}?budid={$budg['budid']}'>Details</a></td>";
$printBudget .= "\r\n\t\t\t\t\t\t\t\t<td><a href='{$edit}?budid={$budg['budid']}'>Edit</a></td>\r\n\t\t\t\t\t\t\t\t<td><a href='{$rem}?budid={$budg['budid']}'>Remove</a></td>\r\n\t\t\t\t\t\t\t\t<td><a href=# onClick=printer2('{$rep}?budid={$budg['budid']}')>Report</a></td>\r\n\t\t\t\t\t\t\t\t<td><a target='_blank' href='{$exp}?budid={$budg['budid']}'>Export</a></td>\r\n\t\t\t\t\t\t\t</tr>";
$i++;
}
$printBudget .= "\r\n\t\t\t\t\t\t</table>\r\n\t\t\t\t\t <p>\r\n\t\t\t\t\t\t<table " . TMPL_tblDflts . " width='15%'>\r\n\t\t\t\t\t <tr><td><br></td></tr>\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\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td><a href='budget-new.php'>New Monthly Budget</a></td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td><a href='budget-yr-new.php'>New Yearly Budget</a></td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td><a href='main.php'>Main Menu</a></td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t</table>";
return $printBudget;
}
function details($_GET)
{
# Get vars
foreach ($_GET as $key => $value) {
${$key} = $value;
}
# Query server
db_connect();
$sql = "SELECT * FROM budgets WHERE budid = '{$budid}'";
$budRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
if (pg_numrows($budRslt) < 1) {
return "<li class=err> - Invalid Budget.";
}
$bud = pg_fetch_array($budRslt);
require "budget.lib.php";
$vbudtype = $TYPES[$bud['budtype']];
$vbudfor = $BUDFOR[$bud['budfor']];
$vfromprd = $PERIODS[$bud['fromprd']];
$vtoprd = $PERIODS[$bud['toprd']];
$bud['edate'] = ext_rdate($bud['edate']);
/* Toggle Options */
$list = "";
$totamt = 0;
db_connect();
# budget for
if ($bud['budfor'] == 'cost') {
$sql = "SELECT DISTINCT prd FROM buditems WHERE budid = '{$budid}'";
$bitRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
while ($bit = pg_fetch_array($bitRslt)) {
$prd = $bit['prd'];
$list .= "\r\n\t\t\t<tr><td colspan=5><h3>{$PERIODS[$prd]}</h3></td></tr>\r\n\t\t\t<tr><td><b>Cost Centers</b></td><td><b>Budget Amount " . CUR . "</b></td><td><b>Actual Amount " . CUR . "</b></td><td><b>Difference " . CUR . "</b></td><td><b>Difference %</b></td></tr>";
db_connect();
$cclRs = db_exec("SELECT * FROM buditems WHERE prd = '{$bit['prd']}' AND budid = '{$budid}'");
while ($ccl = pg_fetch_array($cclRs)) {
$ccRs = get("cubit", "*", "costcenters", "ccid", $ccl['id']);
$cc = pg_fetch_array($ccRs);
$list .= "<tr><td>{$cc['centercode']} - {$cc['centername']}</td>";
db_connect();
$lstRs = db_exec("SELECT * FROM buditems WHERE prd = '{$bit['prd']}' AND id = '{$ccl['id']}' AND budid = '{$budid}'");
while ($lst = pg_fetch_array($lstRs)) {
db_conn($bit['prd']);
$dbalRs = db_exec("SELECT sum(amount) FROM cctran WHERE ccid = '{$ccl['id']}' AND trantype = 'dt'");
$dbal = pg_fetch_array($dbalRs);
$cbalRs = db_exec("SELECT sum(amount) FROM cctran WHERE ccid = '{$ccl['id']}' AND trantype = 'ct'");
$cbal = pg_fetch_array($cbalRs);
// if($bud['budtype'] == "inc"){
// $bal = sprint($dbal['sum'] - $cbal['sum']);
// $diff = sprint($bal - $lst['amt']);
// }else{
// $bal = sprint($dbal['sum'] - $cbal['sum']);
// $diff = sprint($lst['amt'] - $bal);
// }
if ($bud['budtype'] == "inc") {
$bal = sprint($dbal['sum']);
$diff = sprint($bal - $lst['amt']);
} else {
$bal = sprint($cbal['sum']);
$diff = sprint($lst['amt'] - $bal);
}
if ($lst['amt'] != 0) {
$perc = sprint($diff / $lst['amt'] * 100);
} else {
$perc = sprint(0);
}
$list .= "<td align=right>{$lst['amt']}</td><td align=right>{$bal}</td><td align=right>{$diff}</td><td align=right>{$perc}</td>";
}
$list .= "</tr>";
}
$list .= "<tr><td colspan=5><br></td></tr>";
}
} elseif ($bud['budfor'] == 'acc') {
$sql = "SELECT DISTINCT prd FROM buditems WHERE budid = '{$budid}'";
$bitRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
while ($bit = pg_fetch_array($bitRslt)) {
$prd = $bit['prd'];
$list .= "\r\n\t\t\t<tr><td colspan=5><h3>{$PERIODS[$prd]}</h3></td></tr>\r\n\t\t\t<tr><td><b>Cost Centers</b></td><td><b>Budget Amount " . CUR . "</b></td><td><b>Actual Amount " . CUR . "</b></td><td><b>Difference " . CUR . "</b></td><td><b>Difference %</b></td></tr>";
db_connect();
$cclRs = db_exec("SELECT * FROM buditems WHERE prd = '{$bit['prd']}' AND budid = '{$budid}'");
while ($ccl = pg_fetch_array($cclRs)) {
$ccRs = get("core", "*", "accounts", "accid", $ccl['id']);
$cc = pg_fetch_array($ccRs);
$list .= "<tr><td>{$cc['topacc']}/{$cc['accnum']} - {$cc['accname']}</td>";
db_connect();
$lstRs = db_exec("SELECT * FROM buditems WHERE prd = '{$bit['prd']}' AND id = '{$ccl['id']}' AND budid = '{$budid}'");
while ($lst = pg_fetch_array($lstRs)) {
db_conn($bit['prd']);
$dbalRs = db_exec("SELECT sum(amount) FROM transect WHERE debit = '{$ccl['id']}'");
$dbal = pg_fetch_array($dbalRs);
$cbalRs = db_exec("SELECT sum(amount) FROM transect WHERE credit = '{$ccl['id']}'");
$cbal = pg_fetch_array($cbalRs);
if ($bud['budtype'] == "inc") {
$bal = sprint($cbal['sum'] - $dbal['sum']);
$diff = sprint($bal - $lst['amt']);
} else {
$bal = sprint($dbal['sum'] - $cbal['sum']);
$diff = sprint($lst['amt'] - $bal);
}
if ($lst['amt'] != 0) {
$perc = sprint($diff / $lst['amt'] * 100);
} else {
$perc = sprint(0);
}
$list .= "<td align=right>{$lst['amt']}</td><td align=right>{$bal}</td><td align=right>{$diff}</td><td align=right>{$perc}</td>";
}
$list .= "</tr>";
}
$list .= "<tr><td colspan=5><br></td></tr>";
}
}
$details = "<center><h3> Budget Report </h3></center>\r\n\t<table cellpadding='5' cellspacing='0' border=1 width=340 bordercolor='#000000'>\r\n\t<tr><td colspan=2 align='center'><b>Details</b></th></tr>\r\n\t<tr><td>Budget Name</td><td>{$bud['budname']}</td></tr>\r\n\t<tr><td>Budget Type</td><td>{$vbudtype}</td>\r\n\t<tr><td>Budget Period</td><td>{$vfromprd} to {$vtoprd}</td>\r\n\t</table>\r\n\t<p>\r\n\t<table cellpadding='5' cellspacing='0' border=1 width=680 bordercolor='#000000'>\r\n\t{$list}\r\n\t</table>";
$OUTPUT = $details;
require "../tmpl-print.php";
}
function write($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($type, "string", 1, 255, "Invalid Transaction type switch.");
$v->isOk($typename, "string", 1, 255, "Invalid Transaction type.");
$edate = $v->chkrDate($edate, "Invalid date.");
$v->isOk($amount, "float", 1, 13, "Invalid Amount.");
$v->isOk($descrip, "string", 0, 255, "Invalid description.");
if (isset($ccids)) {
foreach ($ccids as $key => $value) {
$v->isOk($ccperc[$key], "float", 1, 20, "Invalid Cost center percentage.");
}
} else {
return enter($_POST, "<li class=err> There are no Cost centers found.");
}
# display errors, if any
if ($v->isError()) {
$confirmCust = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirmCust .= "<li class='err'>" . $e["msg"] . "</li>";
}
return enter($_POST, $confirm);
$confirmCust .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirmCust;
}
$type = strtolower($type);
$edate = ext_rdate($edate);
$edarr = explode("-", $edate);
$prd = $edarr[1];
## start transaction
pglib_transaction("BEGIN") or errDie("Unable to start transaction.");
$ccenters = "";
foreach ($ccids as $key => $value) {
db_connect();
$sql = "SELECT * FROM costcenters_links WHERE id = '{$ccids[$key]}'";
$ccRslt = db_exec($sql) or errDie("Unable to retrieve Cost centers from database.");
$cc = pg_fetch_array($ccRslt);
$ccamts[$key] = sprint($amount * ($ccperc[$key] / 100));
#we need to connect to the actual period db
db_conn($prd);
if ($type == "dtct") {
$sql = "\n\t\t\t\t\tINSERT INTO cctran \n\t\t\t\t\t\t(ccid, trantype, typename, edate, description, amount, username, div, project) \n\t\t\t\t\tVALUES \n\t\t\t\t\t\t('{$ccids[$key]}', 'dt', '{$typename}', '{$edate}', '{$descrip}', '{$ccamts[$key]}', '" . USER_NAME . "', '" . USER_DIV . "', '{$ccidpro[$key]}')";
$insRslt = db_exec($sql) or errDie("Unable to retrieve insert Cost center amounts into database.");
$sql = "\n\t\t\t\t\tINSERT INTO cctran \n\t\t\t\t\t\t(ccid, trantype, typename, edate, description, amount, username, div, project) \n\t\t\t\t\tVALUES \n\t\t\t\t\t\t('{$ccids[$key]}', 'ct', '{$typename}', '{$edate}', '{$descrip}', '{$ccamts[$key]}', '" . USER_NAME . "', '" . USER_DIV . "', '{$ccidpro[$key]}')";
$insRslt = db_exec($sql) or errDie("Unable to retrieve insert Cost center amounts into database.");
} else {
$sql = "\n\t\t\t\t\tINSERT INTO cctran \n\t\t\t\t\t\t(ccid, trantype, typename, edate, description, amount, username, div, project) \n\t\t\t\t\tVALUES \n\t\t\t\t\t\t('{$ccids[$key]}', '{$type}', '{$typename}', '{$edate}', '{$descrip}', '{$ccamts[$key]}', '" . USER_NAME . "', '" . USER_DIV . "', '{$ccidpro[$key]}')";
$insRslt = db_exec($sql) or errDie("Unable to retrieve insert Cost center amounts into database.");
}
}
db_connect();
#now remove the temp entry
$rem_sql = "DELETE FROM cc_popup_data WHERE id = '{$writeid}'";
$run_rem = db_exec($rem_sql) or errDie("Unable to remove temporary cost center information.");
pglib_transaction("COMMIT") or errDie("Unable to complete transaction.");
// Layout
$write = "\n\t\t<center>\n\t\t<table " . TMPL_tblDflts . " width='300'>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td align='center'><b>( i )</b> Amount has been allocated to Cost Centers. <b>( i )</b></td>\n\t\t\t</tr>\n\t\t</table>\n\t\t<p>\n\t\t<input type=button value=' [X] Close ' onClick='javascript:window.close();'>\n\t\t</center>";
return $write;
}
function details($_GET)
{
# Get vars
foreach ($_GET as $key => $value) {
${$key} = $value;
}
# Query server
db_connect();
$sql = "SELECT * FROM budgets WHERE budid = '{$budid}'";
$budRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
if (pg_numrows($budRslt) < 1) {
return "<li class=err> - Invalid Budget.";
}
$bud = pg_fetch_array($budRslt);
require "budget.lib.php";
$vbudtype = $TYPES[$bud['budtype']];
$vbudfor = $BUDFOR[$bud['budfor']];
$vfromprd = $PERIODS[$bud['fromprd']];
$vtoprd = $PERIODS[$bud['toprd']];
$bud['edate'] = ext_rdate($bud['edate']);
/* Toggle Options */
$list = "";
$totamt = 0;
db_connect();
# budget for
if ($bud['budfor'] == 'cost') {
$sql = "SELECT DISTINCT prd FROM buditems WHERE budid = '{$budid}'";
$bitRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
while ($bit = pg_fetch_array($bitRslt)) {
$prd = $bit['prd'];
$list .= "<tr><td><br></td></tr>\r\n\t\t\t<tr><td colspan=5><h3>{$PERIODS[$prd]}</h3></td></tr>\r\n\t\t\t<tr><th>Cost Centers</th><th>Budget Amount " . CUR . "</th><th>Actual Amount " . CUR . "</th><th>Difference " . CUR . "</th><th>Difference %</th></tr>";
db_connect();
$cclRs = db_exec("SELECT * FROM buditems WHERE prd = '{$bit['prd']}' AND budid = '{$budid}'");
while ($ccl = pg_fetch_array($cclRs)) {
$ccRs = get("cubit", "*", "costcenters", "ccid", $ccl['id']);
$cc = pg_fetch_array($ccRs);
$list .= "<tr class='bg-odd'><td>{$cc['centercode']} - {$cc['centername']}</td>";
db_connect();
$lstRs = db_exec("SELECT * FROM buditems WHERE prd = '{$bit['prd']}' AND id = '{$ccl['id']}' AND budid = '{$budid}'");
while ($lst = pg_fetch_array($lstRs)) {
db_conn($bit['prd']);
$dbalRs = db_exec("SELECT sum(amount) FROM cctran WHERE ccid = '{$ccl['id']}' AND trantype = 'dt'");
$dbal = pg_fetch_array($dbalRs);
$cbalRs = db_exec("SELECT sum(amount) FROM cctran WHERE ccid = '{$ccl['id']}' AND trantype = 'ct'");
$cbal = pg_fetch_array($cbalRs);
if ($bud['budtype'] == "inc") {
$bal = sprint($dbal['sum'] - $cbal['sum']);
$diff = sprint($bal - $lst['amt']);
} else {
$bal = sprint($dbal['sum'] - $cbal['sum']);
$diff = sprint($lst['amt'] - $bal);
}
if ($lst['amt'] != 0) {
$perc = sprint($diff / $lst['amt'] * 100);
} else {
$perc = sprint(0);
}
$list .= "<td align=right>{$lst['amt']}</td><td align=right>{$bal}</td><td align=right>{$diff}</td><td align=right>{$perc}</td>";
}
$list .= "</tr>";
}
}
} elseif ($bud['budfor'] == 'acc') {
$sql = "SELECT DISTINCT prd FROM buditems WHERE budid = '{$budid}'";
$bitRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
while ($bit = pg_fetch_array($bitRslt)) {
$prd = $bit['prd'];
$list .= "<tr><td><br></td></tr>\r\n\t\t\t<tr><td colspan=5><h3>{$PERIODS[$prd]}</h3></td></tr>\r\n\t\t\t<tr><th>Cost Centers</th><th>Budget Amount " . CUR . "</th><th>Actual Amount " . CUR . "</th><th>Difference " . CUR . "</th><th>Difference %</th></tr>";
db_connect();
$cclRs = db_exec("SELECT * FROM buditems WHERE prd = '{$bit['prd']}' AND budid = '{$budid}'");
while ($ccl = pg_fetch_array($cclRs)) {
$ccRs = get("core", "*", "accounts", "accid", $ccl['id']);
$cc = pg_fetch_array($ccRs);
$list .= "<tr class='bg-odd'><td>{$cc['topacc']}/{$cc['accnum']} - {$cc['accname']}</td>";
db_connect();
$lstRs = db_exec("SELECT * FROM buditems WHERE prd = '{$bit['prd']}' AND id = '{$ccl['id']}' AND budid = '{$budid}'");
while ($lst = pg_fetch_array($lstRs)) {
db_conn($bit['prd']);
$dbalRs = db_exec("SELECT sum(amount) FROM transect WHERE debit = '{$ccl['id']}'");
$dbal = pg_fetch_array($dbalRs);
$cbalRs = db_exec("SELECT sum(amount) FROM transect WHERE credit = '{$ccl['id']}'");
$cbal = pg_fetch_array($cbalRs);
if ($bud['budtype'] == "inc") {
$bal = sprint($cbal['sum'] - $dbal['sum']);
$diff = sprint($bal - $lst['amt']);
} else {
$bal = sprint($dbal['sum'] - $cbal['sum']);
$diff = sprint($lst['amt'] - $bal);
}
if ($lst['amt'] != 0) {
$perc = sprint($diff / $lst['amt'] * 100);
} else {
$perc = sprint(0);
}
$list .= "<td align=right>{$lst['amt']}</td><td align=right>{$bal}</td><td align=right>{$diff}</td><td align=right>{$perc}</td>";
}
$list .= "</tr>";
}
}
}
// $totamt = sprint($totamt);
// $list .= "<tr class='bg-even'><td><b>Total Budget Amount</b></td><td align=right><b>".CUR." $totamt</b></td></tr>";
/* End Toggle Options */
$details = "<center><h3> Budget Report </h3>\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\r\n\t<tr><th colspan=2>Details</th></tr>\r\n\t<tr class='bg-odd'><td>Budget Name</td><td>{$bud['budname']}</td></tr>\r\n\t<tr class='bg-even'><td>Budget Type</td><td>{$vbudtype}</td>\r\n\t<tr class='bg-odd'><td>Budget Period</td><td>{$vfromprd} to {$vtoprd}</td>\r\n\t<tr><td><br></td></tr>\r\n\t</table>\r\n\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\r\n\t{$list}\r\n\t</table>\r\n\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\r\n\t<tr><td><br></td></tr>\r\n\t</table></form>\r\n\t<p>\r\n\t<table border=0 cellpadding='2' cellspacing='1' width=15%>\r\n\t\t<tr><th>Quick Links</th></tr>\r\n\t\t<tr class='bg-odd'><td align=center><a href='budget-view.php'>View Budgets</td></tr>\r\n\t\t<tr class='bg-odd'><td align=center><a href='budget-new.php'>New Budget</td></tr>\r\n\t\t<tr class='bg-odd'><td align=center><a href='../main.php'>Main Menu</td></tr>\r\n\t</table>";
return $details;
}
function details($_GET)
{
# Get vars
foreach ($_GET as $key => $value) {
${$key} = $value;
}
# Query server
db_connect();
$sql = "SELECT * FROM budgets WHERE budid = '{$budid}'";
$budRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
if (pg_numrows($budRslt) < 1) {
return "<li class=err> - Invalid Budget.";
}
$bud = pg_fetch_array($budRslt);
require "budget.lib.php";
$vbudtype = $TYPES[$bud['budtype']];
$vbudfor = $BUDFOR[$bud['budfor']];
$vfromyr = $YEARS[$bud['fromprd']];
$vtoyr = $YEARS[$bud['toprd']];
$bud['edate'] = ext_rdate($bud['edate']);
/* Toggle Options */
$list = "";
$totamt = 0;
db_connect();
# budget for
if ($bud['budfor'] == 'cost') {
$head = "<tr><th>Cost Centers</th>";
$sql = "SELECT DISTINCT id FROM buditems WHERE budid = '{$budid}'";
$bitRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
while ($bit = pg_fetch_array($bitRslt)) {
$ccRs = get("cubit", "*", "costcenters", "ccid", $bit['id']);
$cc = pg_fetch_array($ccRs);
$list .= "<tr><td>{$cc['centercode']} - {$cc['centername']}</td>";
db_connect();
$lstRs = db_exec("SELECT * FROM buditems WHERE id = '{$bit['id']}' AND budid = '{$budid}'");
while ($lst = pg_fetch_array($lstRs)) {
$list .= "<td align=right>" . CUR . " {$lst['amt']}</td>";
}
$list .= "</tr>";
}
} elseif ($bud['budfor'] == 'acc') {
$head = "<tr><th>Accounts</th>";
$sql = "SELECT DISTINCT id FROM buditems WHERE budid = '{$budid}'";
$bitRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
while ($bit = pg_fetch_array($bitRslt)) {
$accRs = get("core", "*", "accounts", "accid", $bit['id']);
$acc = pg_fetch_array($accRs);
$list .= "<tr><td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>";
db_connect();
$lstRs = db_exec("SELECT * FROM buditems WHERE id = '{$bit['id']}' AND budid = '{$budid}'");
while ($lst = pg_fetch_array($lstRs)) {
$list .= "<td align=right>" . CUR . " {$lst['amt']}</td>";
}
$list .= "</tr>";
}
}
# Budget headings
if ($bud['fromprd'] < $bud['toprd']) {
for ($i = $bud['fromprd']; $i <= $bud['toprd']; $i++) {
$head .= "<th>{$YEARS[$i]}</th>";
}
} elseif ($bud['fromprd'] > $bud['toprd']) {
for ($i = $bud['fromprd']; $i < 10; $i++) {
$head .= "<th>{$YEARS[$i]}</th>";
}
for ($i = 0; $i <= $bud['toprd']; $i++) {
$head .= "<th>{$YEARS[$i]}</th>";
}
} else {
$i = $bud['toprd'];
$head .= "<th>{$YEARS[$i]}</th>";
}
$head .= "</tr>";
// $totamt = sprint($totamt);
// $list .= "<tr class='bg-even'><td><b>Total Budget Amount</b></td><td align=right><b>".CUR." $totamt</b></td></tr>";
/* End Toggle Options */
$details = "<center><h3> Yearly Budget Details </h3>\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\r\n\t<tr><th colspan=2>Details</th></tr>\r\n\t<tr><td>Budget Name</td><td>{$bud['budname']}</td></tr>\r\n\t<tr><td><br></td></tr>\r\n\t<tr><th colspan=2>Options</th></tr>\r\n\t<tr><td>Budget For</td><td>{$vbudfor}</td>\r\n\t<tr><td>Budget Type</td><td>{$vbudtype}</td>\r\n\t<tr><td>Budget Year</td><td>{$vfromyr} to {$vtoyr}</td>\r\n\t<tr><td><br></td></tr>\r\n\t</table>\r\n\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\r\n\t{$head}\r\n\t{$list}\r\n\t</table>";
include "../xls/temp.xls.php";
Stream("Budget.xls", $details);
}
function printCenter($_GET)
{
# Get vars
extract($_GET);
if (!isset($ccid) or strlen($ccid) < 1) {
return "<li class='err'>Invalid Cost Center. Cost Center Not Found.</li>";
}
# Query server
db_connect();
$sql = "SELECT * FROM costcenters WHERE ccid = '{$ccid}'";
$ccRslt = db_exec($sql) or errDie("Unable to retrieve Cost centers from database.");
if (pg_numrows($ccRslt) < 1) {
return "<li class='err'> - Invalid Cost Center.</li>";
}
$cc = pg_fetch_array($ccRslt);
$sp4 = " ";
# get income
$income = "";
$income = "\n\t\t<tr>\n\t\t\t<td colspan='4'><h3>Income</h3></td>\n\t\t</tr>";
#create selection date range
$from_finyear = getYearOfFinMon($from_prd);
$to_finyear = getYearOfFinMon($to_prd);
//print "$from_prd -> $to_prd<br>";
$from_month = date("m", mktime(0, 0, 0, $from_prd, 1, $from_finyear));
$to_month = date("m", mktime(0, 0, 0, $to_prd, 1, $to_finyear));
$search = "edate >= '{$from_finyear}-{$from_month}-01' AND edate <= '{$to_finyear}-{$to_month}-" . date("d", mktime(0, 0, 0, $to_prd + 1, -1, $to_finyear)) . "' AND ";
// $search = "edate >= '$from_finyear-$from_prd-01' AND edate <= '$to_finyear-$to_prd-".date("d",mktime(0,0,0,$to_prd,-1,$to_finyear))."' AND ";
$flag = TRUE;
$x = $from_prd;
while ($flag) {
// for($x=$from_prd;$x<=$to_prd;$x++){
if ($x == 13) {
$x = 1;
}
db_conn($x);
$sql = "SELECT * FROM cctran WHERE {$search} ccid = '{$cc['ccid']}' AND trantype = 'dt'";
$recRslt = db_exec($sql) or errDie("Unable to retrieve Cost Centers from database.");
$tottotinc = 0;
if (pg_numrows($recRslt) > 0) {
$income .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<th colspan='5'>" . date("F", mktime(0, 0, 0, $x, 1, date("Y"))) . "</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Type</th>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t<th>Amount</th>\n\t\t\t\t\t<th>Posted By</th>\n\t\t\t\t</tr>";
$totinc = 0;
for ($i = 0; $rec = pg_fetch_array($recRslt); $i++) {
$totinc += $rec['amount'];
$rec['edate'] = ext_rdate($rec['edate']);
$income .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>{$rec['typename']}</td>\n\t\t\t\t\t\t<td>{$sp4} {$rec['edate']} {$sp4}</td>\n\t\t\t\t\t\t<td>{$rec['description']}</td>\n\t\t\t\t\t\t<td align='right'>{$sp4}" . CUR . " {$rec['amount']}</td>\n\t\t\t\t\t\t<td>{$rec['username']}</td>\n\t\t\t\t\t</tr>";
}
$totinc = sprint($totinc);
$income .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='3' align='right'><b>Total</b></td>\n\t\t\t\t\t<td align='right'><b>" . CUR . " {$totinc}</b></td>\n\t\t\t\t\t<td><br></td>\n\t\t\t\t</tr>";
}
if ($x == $to_prd) {
$flag = FALSE;
}
$x++;
}
#get expenses
$expense = "";
$expense = "\n\t\t<tr><td><br></td></tr>\n\t\t<tr>\n\t\t\t<td colspan='4'><h3>Expenses</h3></td>\n\t\t</tr>";
$flag = TRUE;
$x = $from_prd;
while ($flag) {
// for($x=$from_prd;$x<=$to_prd;$x++){
if ($x == 13) {
$x = 1;
}
db_conn($x);
$sql = "SELECT * FROM cctran WHERE {$search} ccid = '{$cc['ccid']}' AND trantype = 'ct'";
$recRslt = db_exec($sql) or errDie("Unable to retrieve Cost Centers from database.");
if (pg_numrows($recRslt) > 0) {
$expense .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<th colspan='5'>" . date("F", mktime(0, 0, 0, $x, 1, date("Y"))) . "</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Type</th>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t<th>Amount</th>\n\t\t\t\t\t<th>Posted By</th>\n\t\t\t\t</tr>";
$totexp = 0;
for ($i = 0; $rec = pg_fetch_array($recRslt); $i++) {
$totexp += $rec['amount'];
$rec['edate'] = ext_rdate($rec['edate']);
$expense .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>{$rec['typename']}</td>\n\t\t\t\t\t\t<td>{$sp4} {$rec['edate']} {$sp4}</td>\n\t\t\t\t\t\t<td>{$rec['description']}</td>\n\t\t\t\t\t\t<td align='right'>{$sp4}" . CUR . " {$rec['amount']}</td>\n\t\t\t\t\t\t<td>{$rec['username']}</td>\n\t\t\t\t\t</tr>";
}
$totexp = sprint($totexp);
$expense .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='3' align='right'><b>Total</b></td>\n\t\t\t\t\t<td align='right'><b>" . CUR . " {$totexp}</b></td>\n\t\t\t\t</tr>";
}
if ($x == $to_prd) {
$flag = FALSE;
}
$x++;
}
$printCenter = "\n\t\t<center>\n\t\t<h3>Cost Centers Detailed Period Review</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Cost Center</th>\n\t\t\t<tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$sp4} {$cc['centername']} ({$cc['centercode']}) {$sp4}</td>\n\t\t\t</tr>\n\t\t</table>\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t{$income}\n\t\t\t{$expense}\n\t\t</table>\n\t\t<p>\n\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t<input type='hidden' name='key' value='export'>\n\t\t\t<input type='hidden' name='ccid' value='{$ccid}'>\n\t\t\t<input type='hidden' name='from_prd' value='{$from_prd}'>\n\t\t\t<input type='hidden' name='to_prd' value='{$to_prd}'>\n\t\t\t<input type='submit' value='Export To Spreadsheet'>\n\t\t</form>\n\t\t<p>\n\t\t<p>\n\t\t<input type='button' value='[X] Close' onClick='javascript:window.close();'>";
return $printCenter;
}
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 printInv($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($from_day, "num", 1, 2, "Invalid from Date day.");
$v->isOk($from_month, "num", 1, 2, "Invalid from Date month.");
$v->isOk($from_year, "num", 1, 4, "Invalid from Date Year.");
$v->isOk($to_day, "num", 1, 2, "Invalid to Date day.");
$v->isOk($to_month, "num", 1, 2, "Invalid to Date month.");
$v->isOk($to_year, "num", 1, 4, "Invalid to Date Year.");
# mix dates
$fromdate = $from_year . "-" . $from_month . "-" . $from_day;
$todate = $to_year . "-" . $to_month . "-" . $to_day;
if (!checkdate($from_month, $from_day, $from_year)) {
$v->isOk($fromdate, "num", 1, 1, "Invalid from date.");
}
if (!checkdate($to_month, $to_day, $to_year)) {
$v->isOk($todate, "num", 1, 1, "Invalid to date.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>{$e['msg']}</li>";
}
return $confirm;
}
$accnum = remval($accnum);
if (strlen($accnum) > 0) {
db_conn('cubit');
$Sl = "SELECT * FROM customers WHERE lower(accno)=lower('{$accnum}')";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>Invalid account number</li>" . slct();
}
$cd = pg_fetch_array($Ri);
$cusnum = $cd['cusnum'];
}
$rfromdate = ext_rdate($fromdate);
$rtodate = ext_rdate($todate);
# Set up table to display in
$printInv = "\n\t\t<h3>View Recurring invoices. Date Range {$rfromdate} to {$rtodate}</h3>\n\t\t<form action='rec-invoice-proc.php' method='GET'>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Department</th>\n\t\t\t\t<th>Sales Person</th>\n\t\t\t\t<th>Invoice No.</th>\n\t\t\t\t<th>Invoice Date</th>\n\t\t\t\t<th>Customer Name</th>\n\t\t\t\t<th>Order No</th>\n\t\t\t\t<th>Grand Total</th>\n\t\t\t\t<th colspan='5'>Options</th>\n\t\t\t</tr>";
# connect to database
db_connect();
# Query server
$i = 0;
$tot1 = 0;
if (isset($all)) {
$sql = "\n\t\t\tSELECT * FROM rec_invoices \n\t\t\tWHERE odate >= '{$fromdate}' AND odate <= '{$todate}' AND div = '" . USER_DIV . "' ORDER BY surname";
} else {
$sql = "\n\t\t\tSELECT * FROM rec_invoices \n\t\t\tWHERE odate >= '{$fromdate}' AND odate <= '{$todate}' AND cusnum = {$cusnum} AND div = '" . USER_DIV . "' ORDER BY surname";
}
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoices from database.");
if (pg_numrows($invRslt) < 1) {
$printInv = "<li class='err'>No Recurring Invoices found for the selected customer.</li><br>";
} else {
$counter = 0;
while ($inv = pg_fetch_array($invRslt)) {
$inv['total'] = sprint($inv['total']);
$inv['balance'] = sprint($inv['balance']);
$tot1 = $tot1 + $inv['total'];
# Format date
$inv['odate'] = explode("-", $inv['odate']);
$inv['odate'] = $inv['odate'][2] . "-" . $inv['odate'][1] . "-" . $inv['odate'][0];
if (isset($selnum) and $counter < 1000) {
$ch = "checked";
} else {
if (isset($f)) {
$ch = "checked";
} else {
$ch = "";
}
}
$printInv .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>{$inv['deptname']}</td>\n\t\t\t\t\t<td>{$inv['salespn']}</td>\n\t\t\t\t\t<td>RI {$inv['invid']}</td>\n\t\t\t\t\t<td align='center'>{$inv['odate']}</td>\n\t\t\t\t\t<td>{$inv['cusname']} {$inv['surname']}</td>\n\t\t\t\t\t<td align='right'>{$inv['ordno']}</td>\n\t\t\t\t\t<td align='right'>" . CUR . " {$inv['total']}</td>\n\t\t\t\t\t<td><input type='checkbox' name='invids[]' value='{$inv['invid']}' {$ch}></td>\n\t\t\t\t\t<td><a href='rec-invoice-details.php?invid={$inv['invid']}'>Details</a></td>\n\t\t\t\t\t<td><a href='rec-invoice-new.php?invid={$inv['invid']}&cont=1&letters='>Edit</a></td>\n\t\t\t\t\t<td><a href='rec-invoice-run.php?invid={$inv['invid']}'>Invoice</a></td>\n\t\t\t\t\t<td><a href='rec-invoice-rem.php?invid={$inv['invid']}'>Remove</a></td>\n\t\t\t\t</tr>";
$i++;
$counter++;
}
}
if ($i > 0) {
$tot1 = sprint($tot1);
$printInv .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='6'>Totals:{$i}</td>\n\t\t\t\t<td align='right'>" . CUR . " {$tot1}</td>\n\t\t\t\t<td><br></td>\n\t\t\t\t<td colspan='10'><input type='submit' name='edit' value='Edit Item Prices On Selected'> <input type='submit' value='Process Selected' name='proc'></td>\n\t\t\t</tr>";
$printInv .= "\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t</form>\n\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t<input type='hidden' name='key' value='view'>\n\t\t\t\t<input type='hidden' name='from_day' value='{$from_day}'>\n\t\t\t\t<input type='hidden' name='from_month' value='{$from_month}'>\n\t\t\t\t<input type='hidden' name='from_year' value='{$from_year}'>\n\t\t\t\t<input type='hidden' name='to_day' value='{$to_day}'>\n\t\t\t\t<input type='hidden' name='to_month' value='{$to_month}'>\n\t\t\t\t<input type='hidden' name='to_year' value='{$to_year}'>\n\t\t\t\t<input type='hidden' name='accnum' value='{$accnum}'>\n\t\t\t\t<input type='hidden' name='cusnum' value='{$cusnum}'>\n\t\t\t\t<input type='hidden' name='all' value=''>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='6'></td>\n\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t<td><br></td>\n\t\t\t\t\t<td colspan='10'><input type='submit' value='Select All' name='f'> <input type='submit' value='Select 1000' name='selnum'></td>\n\t\t\t\t</tr>\n\t\t\t</form>";
}
$printInv .= "</table>" . mkQuickLinks(ql("rec-invoice-new.php", "New Recurring Invoice"), ql("customers-new.php", "New Customer"));
return $printInv;
}
function printSerial($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($serno, "string", 1, 10, "Invalid Serial number.");
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>{$e['msg']}</li>";
}
return slct($serno, $confirm);
}
$serlist = "";
$details = "no details";
db_connect();
$sql = "SELECT * FROM serialrec WHERE serno LIKE '%{$serno}%' AND div = '" . USER_DIV . "' ORDER BY recid ASC";
$serRslt = db_exec($sql) or errDie("Unable to retrieve purchases from database.");
if (pg_numrows($serRslt) > 0) {
while ($ser = pg_fetch_array($serRslt)) {
$stk = qryStock($ser["stkid"]);
switch ($ser['typ']) {
case "inv":
$details = "Invoiced to {$ser['cusname']} Invoice No. {$ser['invnum']}";
break;
case "note":
$details = "Received from {$ser['cusname']} Credit Note No. {$ser['invnum']}";
break;
case "pur":
$details = "Purchased from Supplier {$ser['cusname']} Purchase No. {$ser['invnum']}";
break;
case "ret":
$details = "Returned to Supplier {$ser['cusname']} Purchase No. {$ser['invnum']}";
break;
case "tran":
$details = "Stock Decrease Transaction : {$ser['cusname']}";
break;
}
$ser['edate'] = ext_rdate($ser['edate']);
$serlist .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>{$ser['serno']}</td>\n\t\t\t\t\t<td>{$stk['stkcod']}</td>\n\t\t\t\t\t<td>{$stk['stkdes']}</td>\n\t\t\t\t\t<td>{$details}</td>\n\t\t\t\t\t<td align='center'> {$ser['tdate']} </td>\n\t\t\t\t\t<td>{$stk['warranty']}</td>\n\t\t\t\t</tr>";
}
} elseif (ext_findSer($serno)) {
$sers = ext_findSer($serno);
foreach ($sers as $key => $ser) {
# Get selected stock
db_connect();
$sql = "SELECT stkdes,stkcod FROM stock WHERE stkid = '{$ser['stkid']}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql);
$stk = pg_fetch_array($stkRslt);
$serlist .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>{$ser['serno']}</td>\n\t\t\t\t\t<td>{$stk['stkcod']}</td>\n\t\t\t\t\t<td>{$stk['stkdes']}</td>\n\t\t\t\t\t<td align='center'>Available</td>\n\t\t\t\t\t<td align='center'>---</td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
}
} else {
$err = "<li class='err'>Serial Number <b>{$serno}</b> not found.</li>";
return slct($serno, $err);
}
$serials = "\n\t\t<center>\n\t\t<h3>Stock Serial Numbers</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Serial No.</th>\n\t\t\t\t<th>Stock Code</th>\n\t\t\t\t<th>Stock Description</th>\n\t\t\t\t<th>Details</th>\n\t\t\t\t<th>Date</th>\n\t\t\t\t<th>Warranty</th>\n\t\t\t</tr>\n\t\t\t{$serlist}\n\t\t\t<tr><td><br></td></tr>\n\t\t</table>\n\t\t<p>\n\t\t<table border='0' cellpadding='2' cellspacing='1'>\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='" . SELF . "'>Find Another</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 $serials;
}
function process_entries($_POST)
{
extract($_POST);
# CHECK IF THIS DATE IS IN THE BLOCKED RANGE
$blocked_date_from = getCSetting("BLOCKED_FROM");
$blocked_date_to = getCSetting("BLOCKED_TO");
db_connect();
pglib_transaction("BEGIN") or errDie("Unable to start transaction.");
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
$vatinc = "yes";
#remove any selected entries
foreach ($ids as $keys => $value) {
if (isset($remids[$keys]) and $remids[$keys] == $value) {
db_connect();
#now remove this entry
$rem_sql = "DELETE FROM purch_batch_entries WHERE id = '{$ids[$keys]}'";
$run_rem = db_exec($rem_sql) or errDie("Unable to remove processed batch entry.");
continue;
}
}
db_connect();
#first we get the suppliers involved
$get_sup = "SELECT distinct(supplier) FROM purch_batch_entries";
$run_sup = db_exec($get_sup) or errDie("Unable to get supplier information.");
if (pg_numrows($run_sup) < 1) {
return get_items($_POST, "<li class='err'>Please Add At Least One Item.</li>");
} else {
while ($sarr0 = pg_fetch_array($run_sup)) {
#get this supplier's name
$get_supp = "SELECT supname,supaddr FROM suppliers WHERE supid = '{$sarr0['supplier']}' LIMIT 1";
$run_supp = db_exec($get_supp) or errDie("Unable to get supplier information.");
if (pg_numrows($run_supp) < 1) {
$supname = "";
$supaddr = "";
} else {
$sarr = pg_fetch_array($run_supp);
$supname = $sarr['supname'];
$supaddr = $sarr['supaddr'];
}
#get distinct invs
$get_inv = "SELECT distinct (supinv) FROM purch_batch_entries WHERE supplier = '{$sarr0['supplier']}'";
$run_inv = db_exec($get_inv) or errDie("Unable to get batch entries.");
if (pg_numrows($run_inv) < 1) {
return get_items($_POST, "<li class='err'>Please Add At Least One Item.</li>");
} else {
while ($earr = pg_fetch_array($run_inv)) {
#get the info + each entry = new line item
$get_items = "SELECT * FROM purch_batch_entries WHERE supplier = '{$sarr0['supplier']}' AND supinv = '{$earr['supinv']}'";
$run_items = db_exec($get_items) or errDie("Unable to get purchase information.");
if (pg_numrows($run_items) < 1) {
return get_items($_POST, "<li class='err'>Please Add At Least One Item.</li>");
} else {
$total = 0;
while ($arr1 = pg_fetch_array($run_items)) {
if (strtotime($arr1['pdate']) >= strtotime($blocked_date_from) and strtotime($arr1['pdate']) <= 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>";
}
#calculate the total
$total = $total + $arr1['qty'] * $arr1['price'];
$pdate = $arr1['pdate'];
$ddate = $arr1['pdate'];
}
}
#get the info + each entry = new line item
$get_items = "SELECT * FROM purch_batch_entries WHERE supplier = '{$sarr0['supplier']}' AND supinv = '{$earr['supinv']}'";
$run_items = db_exec($get_items) or errDie("Unable to get purchase information.");
if (pg_numrows($run_items) < 1) {
return get_items($_POST, "<li class='err'>Please Add At Least One Item.</li>");
} else {
#################[ write the non stock purchase ]################
$remarks = "";
$supaddr = "";
$terms = "0";
$total = 0;
$subtot = 0;
// $pdate = "$dates_year[$keys]-$dates_month[$keys]-$dates_day[$keys]";
// $ddate = $arr['sdate'];
$shipchrg = "0.00";
$purnum = divlastid("pur", USER_DIV);
$typeid = 0;
if (!isset($ctyp)) {
$ctyp = "s";
}
//old ...
//'$sarr0[supplier]','2','$supname','$supaddr','$terms',
# Insert Order to DB
$sql = "\n\t\t\t\t\t\t\t\tINSERT INTO nons_purchases (\n\t\t\t\t\t\t\t\t\tsupid, deptid, supplier, supaddr, terms, pdate, ddate, \n\t\t\t\t\t\t\t\t\tshipchrg, subtot, total, balance, vatinc, vat, remarks, received, done, \n\t\t\t\t\t\t\t\t\tprd, div, purnum, ctyp, typeid\n\t\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t\t'{$sarr0['supplier']}', '2', '{$supname}', '{$supaddr}', '{$terms}', '{$pdate}', '{$ddate}', \n\t\t\t\t\t\t\t\t\t'{$shipchrg}', '{$subtot}', '{$total}', '{$total}', 'yes', '0', '{$remarks}', 'n', 'n', \n\t\t\t\t\t\t\t\t\t'" . PRD_DB . "', '" . USER_DIV . "', '{$purnum}', '{$ctyp}', '{$typeid}'\n\t\t\t\t\t\t\t\t)";
$rslt = db_exec($sql) or errDie("Unable to insert Non-Stock Order to Cubit.", SELF);
#################################################################
$refnum = getrefnum();
$resub = 0;
$totstkamt = array();
$revat = 0;
#######[ NOW WRITE THE ITEMS ]########
while ($arr1 = pg_fetch_array($run_items)) {
if (!in_array("{$arr1['id']}", $ids)) {
}
$darr = explode("-", $arr1['pdate']);
$suppliers[$keys] = $arr1['supplier'];
$accounts[$keys] = $arr1['account'];
$dates_year[$keys] = $darr[0];
$dates_month[$keys] = $darr[1];
$dates_day[$keys] = $darr[2];
$vatcodes[$keys] = $arr1['vatcode'];
$supinvs[$keys] = $arr1['supinv'];
$descriptions[$keys] = $arr1['description'];
$qtys[$keys] = $arr1['qty'];
$prices[$keys] = $arr1['price'];
# Get next ordnum
$purid = lastpurid();
$novat[$keys] = "1";
# Calculate amount
$amt[$keys] = $qtys[$keys] * $prices[$keys];
$tv = $vatinc;
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcodes[$keys]}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return get_items($_POST, "<li class='err'>Please select the vatcode for all your items.</li>");
}
$vd = pg_fetch_array($Ri);
$VATP = $vd['vat_amount'];
if (TAX_VAT != $vd['vat_amount'] and $vd['vat_amount'] != "0.00") {
$showvat = FALSE;
}
# Check Tax Excempt
if ($vd['zero'] == "Yes") {
$vat[$keys] = 0;
$vatinc = "novat";
}
if (isset($novat[$keys]) || strlen($vat[$keys]) < 1) {
# If vat is not included
if ($vatinc == "no") {
$vat[$keys] = sprint($VATP / 100 * $amt[$keys]);
} elseif ($vatinc == "yes") {
$vat[$keys] = sprint($amt[$keys] / (100 + $VATP) * $VATP);
} else {
$vat[$keys] = 0;
}
}
if ($vatinc == "novat") {
$vat[$keys] = 0;
}
if ($vatinc != "novat") {
# If vat is not included
if ($vatinc == "no") {
$vat[$keys] = sprintf("%01.2f", $VATP / 100 * $amt[$keys]);
} else {
$vat[$keys] = sprintf("%01.2f", $amt[$keys] / ($VATP + 100) * $VATP);
}
}
$vatinc = $tv;
# insert Order items
$sql = "\n\t\t\t\t\t\t\t\t\tINSERT INTO nons_pur_items (\n\t\t\t\t\t\t\t\t\t\tpurid, cod, des, \n\t\t\t\t\t\t\t\t\t\tqty, unitcost, amt, \n\t\t\t\t\t\t\t\t\t\tsvat, ddate, div, vatcode\n\t\t\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t\t\t'{$purid}', '{$supinvs[$keys]}', '{$descriptions[$keys]}', \n\t\t\t\t\t\t\t\t\t\t'{$qtys[$keys]}', '{$prices[$keys]}', '{$amt[$keys]}', \n\t\t\t\t\t\t\t\t\t\t'{$vat[$keys]}', '{$ddate}', '" . USER_DIV . "','{$vatcodes[$keys]}'\n\t\t\t\t\t\t\t\t\t)";
$rslt = db_exec($sql) or errDie("Unable to insert Order items to Cubit.", SELF);
//FP
$itemid = pglib_lastid("nons_pur_items", "id");
$SUBTOT = "";
$TOTAL = "";
# calculate subtot
if (isset($amt[$keys])) {
$SUBTOT = $amt[$keys];
} else {
$SUBTOT = 0.0;
}
# If there vatable items
if (isset($vat[$keys])) {
$VAT = $vat[$keys];
} else {
$VAT = 0;
}
# Total
$TOTAL = $SUBTOT;
# If vat is not included
if ($vatinc == "no") {
$TOTAL = $TOTAL + $VAT;
} else {
$TOTAL = $TOTAL;
$SUBTOT -= $VAT;
}
# insert Order to DB
$sql = "\n\t\t\t\t\t\t\t\t\tUPDATE nons_purchases \n\t\t\t\t\t\t\t\t\tSET terms = '{$terms}', \n\t\t\t\t\t\t\t\t\t\tsubtot = subtot + '{$SUBTOT}', total = total + '{$TOTAL}',\n\t\t\t\t\t\t\t\t\t\tbalance = balance + '{$TOTAL}', vatinc = 'yes', vat = vat + '{$VAT}',\n\t\t\t\t\t\t\t\t\t\tsupinv='{$supinvs[$keys]}'\n\t\t\t\t\t\t\t\t\tWHERE purid = '{$purid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update Order in Cubit.", SELF);
#####################[ BEGIN PROCESSING ]#######################
db_connect();
$td = $pdate;
# amount of stock in
$amt[$keys] = $qtys[$keys] * $prices[$keys];
$SUBTOTAL = $amt[$keys];
# Get selected stock line
$sql = "SELECT * FROM nons_pur_items WHERE purid = '{$purid}' AND id = '{$itemid}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
$stkd = pg_fetch_array($stkdRslt);
# Calculate cost amount bought
$amt[$keys] = $qtys[$keys] * $prices[$keys];
/* delivery charge */
# Calculate percentage from subtotal
$perc[$keys] = $amt[$keys] / $SUBTOTAL * 100;
/* end delivery charge */
# the subtotal + delivery charges
$resub += $amt[$keys];
# calculate vat
$svat[$keys] = svat($amt[$keys], $stkd['amt'], $stkd['svat']);
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatcode']}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$vd = pg_fetch_array($Ri);
vatr($vd['id'], $pdate, "INPUT", $vd['code'], $refnum, "VAT for Non-Stock Purchase No. {$purnum}", -$amt[$keys], -$svat[$keys]);
# received vat
$revat += $svat[$keys];
# make amount vat free
$amt[$keys] = $amt[$keys] - $svat[$keys];
# keep records for transactions
if (isset($totstkamt[$accounts[$keys]])) {
$totstkamt[$accounts[$keys]] += $amt[$keys];
} else {
$totstkamt[$accounts[$keys]] = $amt[$keys];
}
# check if there are any outstanding items
$sql = "SELECT * FROM nons_pur_items WHERE purid = '{$purid}' AND div = '" . USER_DIV . "'";
// the old method check for this .. we receive everything NOW so we dont need this AND (qty - rqty) > '0' AND div = '".USER_DIV."'";
$stkdRslt = db_exec($sql);
# if none the set to received
if (pg_numrows($stkdRslt) < 1) {
//NO items were found ... problem somewhere
} else {
# update surch_int(received = 'y')
$sql = "UPDATE nons_purchases SET received = 'y' WHERE purid = '{$purid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update international Orders in Cubit.", SELF);
// while ($uniarr1 = pg_fetch_array($stkdRslt)){
# Update Order items
$sql = "UPDATE nons_pur_items SET rqty = (rqty + '{$qtys[$keys]}'), accid = '{$accounts[$keys]}' WHERE purid='{$purid}' AND div = '" . USER_DIV . "' AND id = '{$itemid}'";
$rslt = db_exec($sql) or errDie("Unable to insert Order items to Cubit.", SELF);
// }
}
# Update Order on the DB
$sql = "UPDATE nons_purchases SET typeid = '2', refno = '', remarks = '{$remarks}' WHERE purid = '{$purid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update Order in Cubit.", SELF);
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
if (isset($suppliers[$keys])) {
$typeid = $suppliers[$keys];
db_connect();
$sql = "SELECT * FROM suppliers WHERE supid = '{$suppliers[$keys]}' AND div = '" . USER_DIV . "'";
$supRslt = db_exec($sql) or errDie("Unable to get supplier");
if (pg_numrows($supRslt) < 1) {
$error = "<li class='err'> Supplier not Found.</li>";
$confirm .= "{$error}<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
} else {
$sup = pg_fetch_array($supRslt);
$pur['supplier'] = $sup['supname'];
$pur['supaddr'] = $sup['supaddr'];
# Get department info
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$sup['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
return "<i class='err'>Department Not Found</i>";
} else {
$dept = pg_fetch_array($deptRslt);
}
$supacc = $dept['credacc'];
}
}
/* - End Hooks - */
### DATA SET 2 WAS HERE
#####################################################################################
//pglib_transaction("COMMIT") or errDie("Unable to complete transaction.");
#received so now move
//pglib_transaction("BEGIN") or errDie("Unable to start transaction.");
## move stuff went here
#####################################################################################
db_connect();
// $sql = "SELECT * FROM bankacct WHERE btype != 'int' AND div = '".USER_DIV."' LIMIT 1";
// $banks = db_exec($sql);
// if(pg_numrows($banks) < 1){
// return "<li class='err'> There are no accounts held at the selected Bank.
// <p><input type='button' onClick='JavaScript:history.back();' value='« Correct Selection'>";
// }
// $barr = pg_fetch_array($banks);
// $bankid = $barr['bankid'];
// 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);
// $cc_trantype = cc_TranTypeAcc($arr1['account'], $banklnk['accnum']);
//
// if(($arr1['project_id'] == 0) OR (strlen($arr1['project_code']) < 1) OR (strlen($arr1['center_code']) < 1) OR (strlen($arr1['center_perc']) < 1)){
// $cc = "
// <script>
// CostCenter('$cc_trantype', 'Non Stock Purchase', '$arr1[pdate]', '$arr1[description]', '$arr1[price]', '');
// </script>
// ";
// }else {
// $cc = "";
// }
// $get_data = "SELECT * FROM purch_batch_entries WHERE id = '$ids[$keys]' LIMIT 1";
$get_data = "SELECT * FROM purch_batch_entries WHERE id = '{$arr1['id']}' LIMIT 1";
$run_data = db_exec($get_data) or errDie("Unable to get cost ledger information.");
if (pg_numrows($run_data) < 1) {
return "\n\t\t\t\t\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t\t\t<td><li class='err'>Unable to get batch entry information.</li></td>\n\t\t\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t\t</table>\n\t\t\t\t\t\t\t\t\t\t<br>" . mkQuickLinks(ql("nons-purch-batch-add.php", "Batch Creditors Non-Stock Invoices Add"), ql("purchase-new.php", "Add Purchase"), ql("purchase-view.php", "View Purchases"), ql("nons-purchase-new.php", "Add Non-Stock Purchase"), ql("nons-purchase-view.php", "View Non-Stock Purchases"));
}
$data_arr = pg_fetch_array($run_data);
$csetting = getsetting("CC_USE");
if ($csetting == "use") {
# we want to store all the relevant information in a neat and orderly manner ...
$get_info = "SELECT * FROM purch_batch_entries_costcenters WHERE batch_entry = '{$arr1['id']}'";
//$ids[$keys]'";
$run_info = db_exec($get_info) or errDie("Unable to get batch entry cost ledger information.");
if (pg_numrows($run_info) < 1) {
#no cost entries ????
} else {
$ccenters = "";
while ($arr = pg_fetch_array($run_info)) {
$amt_vat = $arr1['price'] / (100 + $vd['vat_amount']) * $vd['vat_amount'];
$amount = $arr1['qty'] * ($arr1['price'] - $amt_vat);
db_connect();
#get project id
$get_pro = "SELECT * FROM projects WHERE code = '{$arr['project']}' LIMIT 1";
$run_pro = db_exec($get_pro) or errDie("Unable to get project information.");
if (pg_numrows($run_pro) < 1) {
$project = 0;
} else {
$pro_arr = pg_fetch_array($run_pro);
$project = $pro_arr['id'];
}
#get costcenter id
$get_cst = "SELECT * FROM costcenters WHERE centercode = '{$arr['costcenter']}' LIMIT 1";
$run_cst = db_exec($get_cst) or errDie("Unable to get cost center information.");
if (pg_numrows($run_cst) < 1) {
$costcenterlink = 0;
} else {
$cst_arr = pg_fetch_array($run_cst);
$costcenterlink = $cst_arr['ccid'];
}
#get costcenter/project link id
$get_link = "SELECT * FROM costcenters_links WHERE ccid = '{$costcenterlink}' AND project1 = '{$project}' LIMIT 1";
$run_link = db_exec($get_link) or errDie("Unable to get cost center link information.");
if (pg_numrows($run_link) < 1) {
return "<li class='err'>Unable to get cost ledger link information.</li>";
}
$cc = pg_fetch_array($run_link);
$edate = ext_rdate($data_arr['pdate']);
$edarr = explode("-", $edate);
$prd = $edarr[1];
$ccamts = sprint($amount * ($arr['costperc'] / 100));
#we need to connect to the actual period db
db_conn($prd);
$sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT INTO cctran (\n\t\t\t\t\t\t\t\t\t\t\t\t\tccid, trantype, typename, edate, description, \n\t\t\t\t\t\t\t\t\t\t\t\t\tamount, username, div, project\n\t\t\t\t\t\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t\t\t\t\t\t'{$cc['ccid']}', 'ct', 'Non Stock Purchase', '{$data_arr['pdate']}', '{$data_arr['description']}', \n\t\t\t\t\t\t\t\t\t\t\t\t\t'{$ccamts}', '" . USER_NAME . "', '" . USER_DIV . "', '{$project}'\n\t\t\t\t\t\t\t\t\t\t\t\t)";
$insRslt = db_exec($sql) or errDie("Unable to retrieve insert Cost center amounts into database.");
}
}
}
db_connect();
#now remove this entry
$rem_sql = "DELETE FROM purch_batch_entries WHERE id = '{$arr1['id']}'";
$run_rem = db_exec($rem_sql) or errDie("Unable to remove processed batch entry.");
#remove cost center
$rem_sql2 = "DELETE FROM purch_batch_entries_costcenters WHERE batch_entry = '{$arr1['id']}'";
$run_rem2 = db_exec($rem_sql2) or errDie("Unable to remove cost center information.");
}
# Get Order info
db_connect();
$sql = "SELECT * FROM nons_purchases WHERE purid = '{$purid}' AND div = '" . USER_DIV . "'";
$purRslt = db_exec($sql) or errDie("Unable to get Order information");
if (pg_numrows($purRslt) < 1) {
return "<li>- Order Not Found</li>";
}
$pur = pg_fetch_array($purRslt);
$sid = $pur['supid'];
$purnum = $pur['purnum'];
if ($pur['received'] == "y") {
if (isset($bankid)) {
$sid = $bankid;
$sid += 0;
}
### DATA SET 2 ...
$detadd = "";
if (isset($sid)) {
$detadd = " from Supplier {$pur['supplier']}";
}
$sdate = $pdate;
$tpp = 0;
$ccamt = 0;
# record transaction from data
if (isset($BA)) {
$supacc = $BA;
}
foreach ($totstkamt as $stkacc => $wamt) {
writetrans($stkacc, $supacc, $td, $refnum, $wamt, "Non-Stock Purchase No. {$purnum} Received {$detadd}.");
pettyrec($supacc, $sdate, "ct", "Non-Stock Purchase No. {$purnum} Received {$detadd}.", $wamt, "Cash Order");
}
# vat
$vatamt = $revat;
# Add vat if not included
$retot = $resub;
if (isset($sid)) {
db_connect();
# update the supplier (make balance more)
$sql = "UPDATE suppliers SET balance = (balance + '{$retot}') WHERE supid = '{$pur['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
}
if (isset($sid)) {
# Ledger Records
$DAte = $pur['pdate'];
suppledger($sup['supid'], $stkacc, $DAte, $purid, "Non-Stock Purchase No. {$purnum} received.", $retot, 'c');
}
if ($vatamt != 0) {
# Debit bank and credit the account involved
writetrans($vatacc, $supacc, $td, $refnum, $vatamt, "Non-Stock Purchase VAT paid on Non-Stock Order No. {$purnum} {$detadd}.");
pettyrec($supacc, $sdate, "ct", "Non-Stock Purchase No. {$purnum} Received {$detadd}.", $vatamt, "Cash Order VAT");
# Record the payment on the statement
db_connect();
$sdate = $pdate;
}
// if(isset($bankid)) {
// db_connect();
// $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, vat, chrgvat, banked, accinv, div) VALUES ('$bankid', 'withdrawal', '$sdate', '$pur[supplier]', 'Non-Stock Purchase No. $pur[purnum] received', '0', '$retot', '$vatamt', '$pur[vatinc]', 'no', '$stkacc', '".USER_DIV."')";
// $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF);
// }
if (isset($sid)) {
db_connect();
$DAte = $pdate;
$sql = "\n\t\t\t\t\t\t\t\t\t\tINSERT INTO sup_stmnt (\n\t\t\t\t\t\t\t\t\t\t\tsupid, edate, cacc, amount, \n\t\t\t\t\t\t\t\t\t\t\tdescript, ref, ex, div\n\t\t\t\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t\t\t\t'{$pur['supid']}', '{$pur['pdate']}', '{$stkacc}', '{$retot}', \n\t\t\t\t\t\t\t\t\t\t\t'Non Stock Purchase No. {$purnum} Received', '{$refnum}', '{$purnum}', '" . USER_DIV . "'\n\t\t\t\t\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
db_connect();
# update the supplier age analysis (make balance less)
/* Make transaction record for age analysis */
$sql = "\n\t\t\t\t\t\t\t\t\t\tINSERT INTO suppurch (\n\t\t\t\t\t\t\t\t\t\t\tsupid, purid, pdate, balance, div\n\t\t\t\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t\t\t\t'{$pur['supid']}', '{$purnum}', '{$pur['pdate']}', '{$retot}', '" . USER_DIV . "'\n\t\t\t\t\t\t\t\t\t\t)";
$purcRslt = db_exec($sql) or errDie("Unable to update int Orders information in Cubit.", SELF);
}
###
# copy Order
db_conn(PRD_DB);
$sql = "\n\t\t\t\t\t\t\t\t\tINSERT INTO nons_purchases (\n\t\t\t\t\t\t\t\t\t\tpurid, deptid, supplier, supaddr, terms, \n\t\t\t\t\t\t\t\t\t\tpdate, ddate, shipchrg, shipping, subtot, \n\t\t\t\t\t\t\t\t\t\ttotal, balance, vatinc, vat, remarks, \n\t\t\t\t\t\t\t\t\t\trefno, received, done, ctyp, typeid, \n\t\t\t\t\t\t\t\t\t\tdiv, purnum, supid, mpurid, is_asset, supinv\n\t\t\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t\t\t'{$purid}', '{$pur['deptid']}', '{$pur['supplier']}', '{$pur['supaddr']}', '{$pur['terms']}', \n\t\t\t\t\t\t\t\t\t\t'{$pur['pdate']}', '{$pur['ddate']}', '{$pur['shipchrg']}', '{$pur['shipping']}', '{$pur['subtot']}', \n\t\t\t\t\t\t\t\t\t\t'{$pur['total']}', '0', '{$pur['vatinc']}', '{$pur['vat']}', '{$pur['remarks']}', \n\t\t\t\t\t\t\t\t\t\t'{$pur['refno']}', 'y', 'y', '{$pur['ctyp']}', '{$pur['typeid']}', \n\t\t\t\t\t\t\t\t\t\t'" . USER_DIV . "', '{$pur['purnum']}','{$sid}', '{$supacc}', '{$pur['is_asset']}', '{$pur['supinv']}'\n\t\t\t\t\t\t\t\t\t)";
$rslt = db_exec($sql) or errDie("Unable to insert Non-Stock Order to Cubit.", SELF);
db_connect();
# Get selected stock
$sql = "SELECT * FROM nons_pur_items WHERE purid = '{$purid}' AND div = '" . USER_DIV . "'";
$stktcRslt = db_exec($sql);
while ($stktc = pg_fetch_array($stktcRslt)) {
# Insert Order items
db_conn($pur['prd']);
$sql = "\n\t\t\t\t\t\t\t\t\t\tINSERT INTO nons_pur_items (\n\t\t\t\t\t\t\t\t\t\t\tpurid, cod, des, qty, unitcost, \n\t\t\t\t\t\t\t\t\t\t\tamt, svat, ddate, accid, \n\t\t\t\t\t\t\t\t\t\t\tdiv, vatcode\n\t\t\t\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t\t\t\t'{$purid}', '{$stktc['cod']}', '{$stktc['des']}', '{$stktc['qty']}', '{$stktc['unitcost']}', \n\t\t\t\t\t\t\t\t\t\t\t'{$stktc['amt']}', '{$stktc['svat']}', '{$stktc['ddate']}', '{$stktc['accid']}', \n\t\t\t\t\t\t\t\t\t\t\t'" . USER_DIV . "', '{$stktc['vatcode']}'\n\t\t\t\t\t\t\t\t\t\t)";
$rslt = db_exec($sql) or errDie("Unable to insert Order items to Cubit.", SELF);
}
db_connect();
# Remove the Order from running DB
$sql = "DELETE FROM nons_purchases WHERE purid = '{$purid}' AND div = '" . USER_DIV . "'";
$delRslt = db_exec($sql) or errDie("Unable to update int Orders information in Cubit.", SELF);
# Remove those Order items from running DB
$sql = "DELETE FROM nons_pur_items WHERE purid = '{$purid}' AND div = '" . USER_DIV . "'";
$delRslt = db_exec($sql) or errDie("Unable to update int Orders information in Cubit.", SELF);
}
}
}
}
}
}
pglib_transaction("COMMIT") or errDie("Unable to complete transaction.");
return "\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Processing Completed</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>All Selected Transactions Completed</td>\n\t\t\t</tr>\n\t\t</table>\n\t\t<br>\n\t\t" . mkQuickLinks(ql("nons-purch-batch-add.php", "Batch Creditors Non-Stock Invoices Add"), ql("purchase-new.php", "Add Purchase"), ql("purchase-view.php", "View Purchases"), ql("nons-purchase-new.php", "Add Non-Stock Purchase"), ql("nons-purchase-view.php", "View Non-Stock Purchases"));
}
function details($_GET)
{
# Get vars
foreach ($_GET as $key => $value) {
${$key} = $value;
}
# Query server
db_connect();
$sql = "SELECT * FROM budgets WHERE budid = '{$budid}'";
$budRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
if (pg_numrows($budRslt) < 1) {
return "<li class=err> - Invalid Budget.";
}
$bud = pg_fetch_array($budRslt);
require "budget.lib.php";
$vbudtype = $TYPES[$bud['budtype']];
$vbudfor = $BUDFOR[$bud['budfor']];
$vfromprd = $PERIODS[$bud['fromprd']];
$vtoprd = $PERIODS[$bud['toprd']];
$bud['edate'] = ext_rdate($bud['edate']);
/* Toggle Options */
$list = "";
$totamt = 0;
db_connect();
# budget for
if ($bud['budfor'] == 'cost') {
$head = "<tr><th>Cost Centers</th>";
$sql = "SELECT DISTINCT id FROM buditems WHERE budid = '{$budid}'";
$bitRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
while ($bit = pg_fetch_array($bitRslt)) {
$ccRs = get("cubit", "*", "costcenters", "ccid", $bit['id']);
$cc = pg_fetch_array($ccRs);
$list .= "<tr class='bg-odd'><td>{$cc['centercode']} - {$cc['centername']}</td>";
db_connect();
$lstRs = db_exec("SELECT * FROM buditems WHERE id = '{$bit['id']}' AND budid = '{$budid}'");
$tot_annual = 0;
while ($lst = pg_fetch_array($lstRs)) {
$list .= "<td nowrap align='right'>" . CUR . " {$lst['amt']}</td>";
$tot_annual += $lst["amt"];
}
$tot_annual = sprint($tot_annual);
$list .= "\n\t\t\t\t<td nowrap align='right'>" . CUR . " {$tot_annual}</td>\n\t\t\t</tr>";
}
} elseif ($bud['budfor'] == 'acc') {
$head = "<tr><th>Accounts</th>";
$sql = "SELECT DISTINCT id FROM buditems WHERE budid = '{$budid}'";
$bitRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
while ($bit = pg_fetch_array($bitRslt)) {
$accRs = get("core", "*", "accounts", "accid", $bit['id']);
$acc = pg_fetch_array($accRs);
$list .= "\n\t\t\t<tr class='bg-odd'>\n\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>";
db_connect();
$lstRs = db_exec("SELECT * FROM buditems WHERE id = '{$bit['id']}' AND budid = '{$budid}'");
$tot_annual = 0;
while ($lst = pg_fetch_array($lstRs)) {
$list .= "<td nowrap align='right'>" . CUR . " {$lst['amt']}</td>";
$tot_annual += $lst["amt"];
}
$tot_annual = sprint($tot_annual);
$list .= "\n\t\t\t\t<td nowrap align='right'>" . CUR . " {$tot_annual}</td>\n\t\t\t</tr>";
}
}
# Budget headings
if ($bud['fromprd'] < $bud['toprd']) {
for ($i = $bud['fromprd']; $i <= $bud['toprd']; $i++) {
$head .= "<th>{$PERIODS[$i]}</th>";
}
} elseif ($bud['fromprd'] > $bud['toprd']) {
for ($i = $bud['fromprd']; $i <= 12; $i++) {
$head .= "<th>{$PERIODS[$i]}</th>";
}
for ($i = 1; $i <= $bud['toprd']; $i++) {
$head .= "<th>{$PERIODS[$i]}</th>";
}
} else {
$i = $bud['toprd'];
$head .= "<th>{$PERIODS[$i]}</th>";
}
$head .= "\n\t\t<th>Annual Total</th>\n\t</tr>";
// $totamt = sprint($totamt);
// $list .= "<tr class='bg-even'><td><b>Total Budget Amount</b></td><td align=right><b>".CUR." $totamt</b></td></tr>";
/* End Toggle Options */
$details = "<center><h3> Budget Details </h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\n\t<tr><th colspan=2>Details</th></tr>\n\t<tr class='bg-odd'><td>Budget Name</td><td>{$bud['budname']}</td></tr>\n\t<tr><td><br></td></tr>\n\t<tr><th colspan=2>Options</th></tr>\n\t<tr class='bg-odd'><td>Budget For</td><td>{$vbudfor}</td>\n\t<tr class='bg-even'><td>Budget Type</td><td>{$vbudtype}</td>\n\t<tr class='bg-odd'><td>Budget Period</td><td>{$vfromprd} to {$vtoprd}</td>\n\t<tr><td><br></td></tr>\n\t</table>\n\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\n\t{$head}\n\t{$list}\n\t</table>\n\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\n\t<tr><td><br></td></tr>\n\t</table></form>\n\t<p>\n\t<table border=0 cellpadding='2' cellspacing='1' width=15%>\n\t\t<tr><th>Quick Links</th></tr>\n\t\t<tr class='bg-odd'><td align=center><a href='budget-view.php'>View Budgets</td></tr>\n\t\t<tr class='bg-odd'><td align=center><a href='budget-new.php'>New Budget</td></tr>\n\t\t<tr class='bg-odd'><td align=center><a href='../main.php'>Main Menu</td></tr>\n\t</table>";
return $details;
}
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;
}
