function printyr()
{
// Set up table to display in
// Connect to database
core_Connect();
$sql = "SELECT * FROM year ORDER BY yrname";
$Rslt = db_exec($sql) or errDie("ERROR: Unable to get Financial year details from database.", SELF);
$numrows = pg_numrows($Rslt);
if ($numrows < 1) {
$OUTPUT = "There are no Financial years defined in Cubit.";
require "template.php";
}
# display all Accounts
$yrs = "";
for ($i = 0; $i < $numrows; $i++) {
$yr = pg_fetch_array($Rslt, $i);
if (!isset($thisyear) && $yr["closed"] == "n") {
$thisyear = $yr["yrname"];
}
# alternate bgcolor
$bgColor = bgcolorc($i);
if (PRD_STATE != "py" && $yr["closed"] == "y") {
$status = "Closed";
} else {
if (PRD_STATE == "py" && $yr["yrname"] == YR_NAME) {
$status = "Closed (In Use)";
} else {
if (PRD_STATE != "py" && $yr["yrname"] == YR_NAME || PRD_STATE == "py" && $yr["yrname"] == PYR_NAME) {
$status = "Current";
} else {
$status = "";
}
}
}
$yrs .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>{$yr['yrname']}</td>\n\t\t\t<td align='right'>{$yr['yrdb']}</td>\n\t\t\t<td align='center'>{$status}</td>\n\t\t</tr>\n";
}
if (!isset($thisyear)) {
$thisyear = "All financial years have been closed.";
} else {
$thisyear = substr($thisyear, 1);
}
global $PRDMON, $MONPRD;
$pmon = 0;
$fyear = getFinYear() - (int) ($PRDMON[1] > 1);
$prddesc = array();
for ($i = 1; $i <= 12; $i++) {
$mon = $PRDMON[$i];
if ($mon < $pmon) {
++$fyear;
}
$pmon = $mon;
if ($i == 1 || $i == 12) {
$prddesc[] = getMonthName($mon) . " {$fyear}";
}
}
$prddesc = implode(" to ", $prddesc);
$OUTPUT = "\n\t<h3>Financial Years</h3>\n\t<table " . TMPL_tblDflts . ">\n\t<tr>\n\t\t<th colspan='3'>Current Financial Year: {$thisyear}</th>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<th colspan='3'>Period Range: {$prddesc}</td>\n\t</tr>\n\t<tr>\n\t\t<th>Year Name</th>\n\t\t<th>Year Database</th>\n\t\t<th>Status</th>\n\t</tr>\n\t{$yrs}\n\t</table>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=15%>\n\t\t<tr><td><br></td></tr>\n\t\t<tr><th>Quick Links</th></tr>\n\t\t<tr class=datacell><td align=center><a href='finyearnames-new.php'>Set Financial Year</td></tr>\n\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
// all template to display the info and die
require "template.php";
}
function view()
{
# Just months
$months = array("1", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
# Check if year has been opened
core_connect();
$sql = "SELECT * FROM active";
$cRs = db_exec($sql) or errDie("Database Access Failed - check year open.", SELF);
if (pg_numrows($cRs) > 0) {
# Get the range
core_connect();
$sql = "SELECT * FROM range";
$Rslt = db_exec($sql);
if (pg_numrows($Rslt) < 1) {
$OUTPUT = "<center><li class=err>ERROR : The Financial year Period range was not found on Database, Please make sure that everything is set during instalation.";
require "template.php";
}
$range = Pg_fetch_array($Rslt);
global $PRDMON, $MONPRD;
$pmon = 0;
$fyear = getFinYear() - (int) ($PRDMON[1] > 1);
$prddesc = array();
for ($i = 1; $i <= 12; $i++) {
$mon = $PRDMON[$i];
if ($mon < $pmon) {
++$fyear;
}
$pmon = $mon;
if ($i == 1) {
$smonth = getMonthName($mon) . " {$fyear}";
} else {
if ($i == 12) {
$endmon = getMonthName($mon) . " {$fyear}";
}
}
}
$prddesc = implode(" to ", $prddesc);
$ret = "<p><p>\n\t\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t\t\t<tr><td colspan=2><li class=err>Financial year period range has already been set.</td></tr>\n\t\t\t<tr><th>Field</th><th>Value</th></tr>\n\t\t\t<tr class='bg-odd'><td>Financial Year Starts in</td><td align=center>{$smonth}</td></tr>\n\t\t\t<tr class='bg-even'><td>Financial Year Ends in</td><td align=center>{$endmon}</td></tr>\n\t\t</table>\n\t\t<p>\n\t\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=100>\n\t\t\t<tr><th>Quick Links</th></tr>\n\t\t\t<tr bgcolor='#88BBFF'><td><a href='../main.php'>Main Menu</a></td></tr>\n\t\t</table>";
return $ret;
}
$month = 1;
$smonth = "<select name=smonth>";
while ($month <= 12) {
$smonth .= "<option value='{$month}'>{$months[$month]}</option>";
$month++;
}
$smonth .= "</select>";
//layout
$view = "\n\t<h3>Set Financial Year Period Range</h3>\n\t<form action='" . SELF . "' method=post name=form>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=300>\n\t\t<input type=hidden name=key value=confirm>\n\t\t<tr><th>Field</th><th>Value</th></tr>\n\t\t<tr class='bg-odd'><td>Financial Years Start in</td><td valign=center>{$smonth}</td></tr>\n\t\t<tr><td><br></td></tr>\n\t\t<tr><td><input type=button value='« Back' onClick='javascript:history.back()'></td><td align=right><input type=submit value='Continue »'></td></tr>\n\t</table>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=100>\n\t\t<tr><th>Quick Links</th></tr>\n\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t</form>\n\t</table>";
return $view;
}
function viewtran($_POST)
{
global $MONPRD, $PRDMON;
# Get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($accids)) {
foreach ($accids as $key => $accid) {
$v->isOk($accid, "num", 1, 20, "Invalid Account number.");
}
} else {
return "<li class=err>Please select at least one account.</li>" . slctacc();
}
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get the ids
if ($accnt == 'all') {
$accids = array();
core_connect();
$sql = "SELECT accid FROM accounts WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$accids[] = $ac['accid'];
}
} else {
return "<li calss=err> There are no accounts yet in Cubit.";
}
}
$hide = "";
# Get all previous Periods
db_conn("audit");
$sql = "SELECT prd.*, map.period FROM audit.closedprd prd, core.prdmap map\n\t\t\tWHERE prd.prdnum=map.month AND map.period::integer<'" . $MONPRD[PRD_DB] . "'\n\t\t\tORDER BY map.period::integer";
$clsRs = db_exec($sql) or errDie("Could not get closed periods from audit DB", SELF);
$trans = "";
if (pg_numrows($clsRs) > 0) {
while ($cls = pg_fetch_array($clsRs)) {
$prd = $cls['prdnum'];
# Period name
$prdname = prdname($prd);
$trans .= "<tr><td colspan=8 align=center><h3>{$prdname}</h3></td></tr>";
$hide = "";
if (isset($t)) {
unset($t);
}
foreach ($accids as $key => $accid) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
# Get balances
$idRs = get($prd, "max(id), min(id)", "ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", "ledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "cbalance,dbalance", "ledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
} else {
continue;
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
$cbal['cbalance'] = 0;
$cbal['dbalance'] = 0;
}
$t = "lemme ci";
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DT";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CT";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
$balance = sprint($balance);
$bal['cbalance'] = sprint($bal['cbalance']);
$bal['dbalance'] = sprint($bal['dbalance']);
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
if ($prd < $PRDMON[1]) {
++$prd_y;
}
// make the date of the last day of the previous prd
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, $prd_y));
$hide .= "<input type=hidden name=accids[] value='{$acc['accid']}'>";
$trans .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>";
# --> Transaction reding comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
$tranRs = get($prd, "*", "ledger", "acc", $accid);
while ($tran = pg_fetch_array($tranRs)) {
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
# Current(Running) balance
if ($tran['dbalance'] > $tran['cbalance']) {
$tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
$tran['cbalance'] = "";
$cbalance = $tran['dbalance'];
$cfl = "DT";
} elseif ($tran['cbalance'] > $tran['dbalance']) {
$tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
$tran['dbalance'] = "";
$cbalance = $tran['cbalance'];
$cfl = "CT";
} else {
$tran['cbalance'] = "";
$tran['dbalance'] = "";
$cbalance = "0.00";
$cfl = "";
}
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
$trans .= "<tr><td><br></td><td>{$tran['edate']}</td><td>{$tran['eref']}</td><td>{$tran['descript']}</td><td align=right>{$tran['debit']}</td><td align=right>{$tran['credit']}</td><td align=right>{$cbalance} {$cfl}</td><td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td></tr>";
}
# Total balance changes
if ($dbal['debit'] > $dbal['credit']) {
$dbal['debit'] = sprint($dbal['debit'] - $dbal['credit']);
$dbal['credit'] = "";
} elseif ($dbal['credit'] > $dbal['debit']) {
$dbal['credit'] = sprint($dbal['credit'] - $dbal['debit']);
$dbal['debit'] = "";
} else {
$dbal['credit'] = "0.00";
$dbal['debit'] = "0.00";
}
$trans .= "<tr><td colspan=2><br></td><td>A/C Total</td><td>Total for period {$prdname} to Date :</td><td align=right>{$dbal['debit']}</td><td align=right>{$dbal['credit']}</td><td align=right></td><td> </td></tr>";
$trans .= "<tr><td colspan=8><br></td></tr>";
}
if (!isset($t)) {
$trans .= "<tr><td colspan=8 align=center><li> There are no transactions in this period.</td></tr>";
}
}
} else {
$trans .= "<tr><td colspan=8 align=center><li> There are no closed periods this year.</td></tr>";
}
# Period name
$prdname = prdname(PRD_DB);
$prd = PRD_DB;
$trans .= "<tr><td><br></td></tr>";
$trans .= "<tr><td colspan=8 align=center><h3>{$prdname}</h3></td></tr>";
$hide = "";
if (isset($t)) {
unset($t);
}
foreach ($accids as $key => $accid) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
# Get balances
$idRs = get($prd, "max(id), min(id)", "ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", "ledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "cbalance,dbalance", "ledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
} else {
continue;
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
$cbal['cbalance'] = 0;
$cbal['dbalance'] = 0;
}
$t = "lemme ci";
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DT";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CT";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
$balance = sprint($balance);
$bal['cbalance'] = sprint($bal['cbalance']);
$bal['dbalance'] = sprint($bal['dbalance']);
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
if ($prd < $PRDMON[1]) {
++$prd_y;
}
// make the date of the last day of the previous prd
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, $prd_y));
$hide .= "<input type=hidden name=accids[] value='{$acc['accid']}'>";
$trans .= "\n\t\t<tr>\n\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<td colspan='2'>{$bbf_date}</td>\n\t\t\t<td>Br/Forwd</td>\n\t\t\t<td>Brought Forward</td>\n\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t<td> </td>\n\t\t</tr>";
# --> transactio reding comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
$tranRs = get($prd, "*", "ledger", "acc", $accid);
while ($tran = pg_fetch_array($tranRs)) {
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
# Current(Running) balance
if ($tran['dbalance'] > $tran['cbalance']) {
$tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
$tran['cbalance'] = "";
$cbalance = $tran['dbalance'];
$cfl = "DT";
} elseif ($tran['cbalance'] > $tran['dbalance']) {
$tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
$tran['dbalance'] = "";
$cbalance = $tran['cbalance'];
$cfl = "CT";
} else {
$tran['cbalance'] = "";
$tran['dbalance'] = "";
$cbalance = "0.00";
$cfl = "";
}
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
$trans .= "<tr><td><br></td><td>{$tran['edate']}</td><td>{$tran['eref']}</td><td>{$tran['descript']}</td><td align=right>{$tran['debit']}</td><td align=right>{$tran['credit']}</td><td align=right>{$cbalance} {$cfl}</td><td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td></tr>";
}
# Total balance changes
if ($dbal['debit'] > $dbal['credit']) {
$dbal['debit'] = sprint($dbal['debit'] - $dbal['credit']);
$dbal['credit'] = "";
} elseif ($dbal['credit'] > $dbal['debit']) {
$dbal['credit'] = sprint($dbal['credit'] - $dbal['debit']);
$dbal['debit'] = "";
} else {
$dbal['credit'] = "0.00";
$dbal['debit'] = "0.00";
}
$trans .= "<tr><td colspan=2><br></td><td>A/C Total</td><td>Total for period {$prdname} to Date :</td><td align=right>{$dbal['debit']}</td><td align=right>{$dbal['credit']}</td><td align=right></td><td> </td></tr>";
$trans .= "<tr><td colspan=8><br></td></tr>";
}
if (!isset($t)) {
$trans .= "<tr><td colspan=8 align=center><li> There are no transactions in this period.</td></tr>";
}
$sp = " ";
$view = "\n\t<center>\n\t<h3>Year Review General Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=90%>\n\t<tr><td colspan=8><br></td></tr>\n\t<tr><th>{$sp}</th><th>Date</th><th>Reference</th><th>Description</th><th>Debit</th><th>Credit</th><th>Balance</th><th>Contra Acc</th></tr>\n\t{$trans}\n\t<tr><td colspan=8><br></td></tr>\n\t</table>";
include "temp.xls.php";
Stream("Ledger", $view);
return $view;
}
function viewtran($_POST)
{
extract($_POST);
global $MONPRD, $PRDMON;
require_lib("validate");
$v = new validate();
$v->isOk($accnt, "string", 1, 10, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($accids)) {
foreach ($accids as $accid) {
$v->isOk($accid, "num", 1, 20, "Invalid Account number.");
}
} else {
return "<li class='err'>Please select at least one account.</li>" . slctacc();
}
}
# display errors, if any
if ($v->isError()) {
$err = $v->genErrors();
return slct($err);
}
if ($accnt == 'all') {
$accids = array();
core_connect();
$sql = "SELECT accid FROM accounts WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
while ($ac = pg_fetch_array($rs)) {
$accids[] = $ac['accid'];
}
} else {
if ($accnt == "allactive") {
$accids = array();
$sql = "SELECT accid FROM core.trial_bal\n\t\t\t\tWHERE (debit!=0 OR credit!=0) AND div='" . USER_DIV . "'\n\t\t\t\t\tAND period>='" . $MONPRD[$fprd] . "' AND period<='" . $MONPRD[$tprd] . "'\n\t\t\t\tGROUP BY accid";
$qry = new dbSql($sql);
$qry->run();
while ($macc_data = $qry->fetch_array()) {
$accids[] = $macc_data["accid"];
}
}
}
if ($key == "spreadsheet") {
$pure = true;
} else {
$pure = false;
}
# Get all Closed Periods
db_conn("audit");
// $sql = "SELECT * FROM closedprd";
// $clsRs = db_exec($sql) or errDie("Could not get closed periods from audit DB",SELF);
$trans = "";
$hide = "";
//while($cls = pg_fetch_array($clsRs)){
foreach ($accids as $key => $accid) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
$sql = "SELECT debit,credit FROM core.trial_bal WHERE accid='{$accid}' AND month='{$tprd}'";
$qry = new dbSql($sql);
$qry->run();
$tb = $qry->fetch_array();
$tbbal = $tb["debit"] - $tb["credit"];
$hide .= "<input type='hidden' name='accids[]' value='{$acc['accid']}'>";
$trans .= "\n\t\t\t<tr>\n\t\t\t\t<th> </th>\n\t\t\t\t<th>Date</th>\n\t\t\t\t<th>Reference</th>\n\t\t\t\t<th>Description</th>\n\t\t\t\t<th>Debit</th>\n\t\t\t\t<th>Credit</th>\n\t\t\t\t<th>Balance</th>\n\t\t\t\t<th>Contra Acc</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='4' align='right'><b>Balance at end of " . getMonthName($tprd) . "</b></td>\n\t\t\t\t<td align='right'><b>" . money($tb["debit"]) . "</b></td>\n\t\t\t\t<td align='right'><b>" . money($tb["credit"]) . "</b></td>\n\t\t\t\t<td align='right' nowrap='t'><b>" . ($tbbal > 0 ? money($tbbal) . " DT" : money(-$tbbal) . " CT") . "</b></td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>";
$cp = $fprd;
$fs = 0;
if ($fprd == $tprd + 1) {
$f = true;
} else {
$f = false;
}
while ($cp != $tprd + 1 || $f) {
$prd = $cp;
$cp++;
if ($cp == 13) {
$cp = 1;
}
$fs++;
if ($fs > 13) {
break;
}
$f = false;
# Period name
$prdname = prdname($prd);
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='8' align='center'><h3>{$prdname}</h3></td>\n\t\t\t\t</tr>";
if (isset($t)) {
unset($t);
}
# Get balances
$idRs = get($prd, "max(id), min(id)", "ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", "ledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "cbalance,dbalance", "ledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
} else {
if (!isset($t)) {
$trans .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td colspan='8' align='center'><li> There are no transactions in this period.</td>\n\t\t\t\t\t\t</tr>";
}
continue;
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
$cbal['cbalance'] = 0;
$cbal['dbalance'] = 0;
}
$t = "lemme ci";
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DT";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CT";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
$balance = sprint($balance);
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
if ($prd < $PRDMON[1]) {
++$prd_y;
}
// make the date of the last day of the previous prd
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, $prd_y));
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
# --> transactio reding comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
$tranRs = get($prd, "*", "ledger", "acc", $accid);
while ($tran = pg_fetch_array($tranRs)) {
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
# Current(Running) balance
if ($tran['dbalance'] > $tran['cbalance']) {
$tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
$tran['cbalance'] = "";
$cbalance = $tran['dbalance'];
$cfl = "DT";
} elseif ($tran['cbalance'] > $tran['dbalance']) {
$tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
$tran['dbalance'] = "";
$cbalance = $tran['cbalance'];
$cfl = "CT";
} else {
$tran['cbalance'] = "";
$tran['dbalance'] = "";
$cbalance = "0.00";
$cfl = "";
}
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$trans .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td colspan='2'>{$tran['edate']}</td>\n\t\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t\t<td align='right'>{$cbalance} {$cfl}</td>\n\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t</tr>";
}
# Total balance changes
if ($dbal['debit'] > $dbal['credit']) {
$dbal['debit'] = sprint($dbal['debit'] - $dbal['credit']);
$dbal['credit'] = "";
} elseif ($dbal['credit'] > $dbal['debit']) {
$dbal['credit'] = sprint($dbal['credit'] - $dbal['debit']);
$dbal['debit'] = "";
} else {
$dbal['credit'] = "";
$dbal['debit'] = "0.00";
}
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t\t<td>A/C Total</td>\n\t\t\t\t\t<td>Total for period {$prdname}:</td>\n\t\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t<td></td>\n\t\t\t\t</tr>";
}
$trans .= "<tr><td colspan='8'><br></td></tr>";
}
$fprdname = prdname($fprd);
$tprdname = prdname($tprd);
$sp = " ";
$view = "";
if (!$pure) {
$view .= "\n\t\t\t<center>\n\t\t\t<h3>Period Range General Ledger</h3>\n\t\t\t<h4>{$fprdname} - {$tprdname}</h4>";
}
$view .= "<table " . TMPL_tblDflts . " width='90%'>";
if (!$pure) {
$view .= "\n\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t<input type='hidden' name='key' value='spreadsheet'>\n\t\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t\t{$hide}\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t</tr>\n\t\t\t\t" . TBL_BR;
}
$view .= $trans;
if (!$pure) {
$view .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8'> </td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t</tr>\n\t\t\t<table>\n\t\t\t</form>" . mkQuickLinks(ql("index-reports.php", "Financials"), ql("index-reports-journal.php", "Current Year Details General Ledger Reports"), ql("../core/acc-new2.php", "Add New Account"));
}
return $view;
}
#
#
#
#
#
#
#
# If this script is called by itself, abort
if (basename(getenv("SCRIPT_NAME")) == "budget.lib.php") {
exit;
}
# get array from all periods
$PERIODS = array();
global $PRDMON, $MONPRD;
$pmon = 0;
$fyear = getFinYear() - (int) ($PRDMON[1] > 1);
for ($i = 1; $i <= 12; $i++) {
$mon = $PRDMON[$i];
if ($mon < $pmon) {
++$fyear;
}
$pmon = $mon;
$PERIODS[$mon] = getMonthName($mon) . " {$fyear}";
}
# get array from all years
$YEARS = array();
db_conn("core");
$sql = "SELECT * FROM year";
$yrRslt = db_exec($sql);
$i = 0;
while ($yr = pg_fetch_array($yrRslt)) {
function viewtran($_POST)
{
global $MONPRD, $PRDMON;
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($accnt, "string", 1, 10, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($accids)) {
foreach ($accids as $key => $accid) {
$v->isOk($accid, "num", 1, 20, "Invalid Account number.");
}
} else {
return "<li class='err'>Please select at least one account.</li>" . slctacc();
}
}
if ($v->isError()) {
$err = $v->genErrors();
return $confirm;
}
if ($_POST["key"] == "export") {
$pure = true;
} else {
$pure = false;
}
#get list of which accounts to show
if ($accnt == 'all') {
$accids = array();
core_connect();
$sql = "SELECT accid FROM accounts WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$accids[] = $ac['accid'];
}
} else {
return "<li calss='err'> There are no accounts yet in Cubit.</li>";
}
} else {
if ($accnt == "allactive") {
$accids = array();
//print "->$fin_year<-";
// if ($fin_year != "0"){
// $accsql = array ();
// for ($x=1;$x<13;$x++){
// $month = date ("F",mktime (0,0,0,$x,1,substr($fin_year,1)));
// $accsql[] = "SELECT debit,credit FROM $month";
// }
// $sql = implode (" UNION ",$accsql);
// db_conn($fin_year."_audit");
// $run_sql = db_exec($sql) or errDie ("Unable to get previous year information.");
// while ($rarr = pg_fetch_array ($run_sql)){
// $accids[] = $rarr['debit'];
// $accids[] = $rarr['credit'];
// $accids = array_unique($accids);
// }
//
// }else {
$sql = "SELECT accid FROM core.trial_bal\n\t\t\t\t\tWHERE (debit!=0 OR credit!=0) AND div='" . USER_DIV . "'\n\t\t\t\t\t\tAND period<='" . $MONPRD[PRD_DB] . "'\n\t\t\t\t\tGROUP BY accid";
$qry = new dbSql($sql);
$qry->run();
while ($macc_data = $qry->fetch_array()) {
$accids[] = $macc_data["accid"];
}
// }
}
}
$hide = "";
$trans = "";
foreach ($accids as $key => $accid) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
$tran_flag = FALSE;
$the_trans = "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t</tr>";
db_conn("audit");
#go through SELECTED periods ...
$cp = $fprd;
$fs = 0;
if ($fprd == $tprd + 1) {
$f = true;
} else {
$f = false;
}
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DT";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CT";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
// if ($prd < $PRDMON[1]) {
// ++$prd_y;
// }
//"t-M-Y"
// make the date of the last day of the previous prd
$bbf_date = date("t F Y", mktime(0, 0, 0, $PRDMON[1] - 1, 1, $prd_y));
if ($openbal == "show" and ($bal['dbalance'] > 0 or $bal['cbalance'] > 0)) {
$tran_flag = TRUE;
$the_trans .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td nowrap colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t\t\t<td>Br/Forwd</td><td>Brought Forward</td>\n\t\t\t\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t</tr>";
}
while ($cp != $tprd + 1 || $f) {
$prd = $cp;
$cp++;
$fs++;
if ($cp == 13) {
$cp = 1;
}
if ($fs > 13) {
break;
}
$f = false;
# Period name
$prdname = prdname($prd);
$hide = "";
if (isset($t)) {
unset($t);
}
# Get balances
$idRs = get($prd, "max(id), min(id)", "ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
#at least 1 entry found for this period ...
$balRs = get($prd, "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", "ledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "cbalance,dbalance", "ledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
} else {
if (!isset($t)) {
// $the_trans .= "
// <tr class='".bg_class()."'>
// <td colspan='8' align='center'><li> There are no transactions in this period.</td>
// </tr>";
}
continue;
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
$cbal['cbalance'] = 0;
$cbal['dbalance'] = 0;
}
$t = "lemme ci";
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DT";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CT";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
$balance = sprint($balance);
$bal['cbalance'] = sprint($bal['cbalance']);
$bal['dbalance'] = sprint($bal['dbalance']);
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
if ($prd < $PRDMON[1]) {
++$prd_y;
}
# --> Transaction reding comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
#go through all the transactions
$tranRs = get($prd, "*", "ledger", "acc", $accid, "ORDER BY id");
while ($tran = pg_fetch_array($tranRs)) {
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
# Current(Running) balance
if ($tran['dbalance'] > $tran['cbalance']) {
$tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
$tran['cbalance'] = "";
$cbalance = $tran['dbalance'];
$cfl = "DT";
} elseif ($tran['cbalance'] > $tran['dbalance']) {
$tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
$tran['dbalance'] = "";
$cbalance = $tran['cbalance'];
$cfl = "CT";
} else {
$tran['cbalance'] = "";
$tran['dbalance'] = "";
$cbalance = "0.00";
$cfl = "";
}
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
#only show the transaction if it actually DOES something ...
if ($tran['debit'] != 0 or $tran['credit'] != 0) {
$tran_flag = TRUE;
if (strlen($tran['edate']) == 10) {
$darr = explode("-", $tran['edate']);
$tran['edate'] = date("t F Y", mktime(0, 0, 0, $darr[1], $darr[0], $darr[2]));
}
$the_trans .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td><br></td>\n\t\t\t\t\t\t\t<td nowrap>{$tran['edate']}</td>\n\t\t\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t\t<td nowrap align='right'>{$tran['debit']}</td>\n\t\t\t\t\t\t\t<td nowrap align='right'>{$tran['credit']}</td>\n\t\t\t\t\t\t\t<td nowrap align='right'>{$cbalance} {$cfl}</td>\n\t\t\t\t\t\t\t<td nowrap>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t\t</tr>";
}
}
# Total balance changes
if ($dbal['debit'] > $dbal['credit']) {
$dbal['debit'] = sprint($dbal['debit'] - $dbal['credit']);
$dbal['credit'] = "";
} elseif ($dbal['credit'] > $dbal['debit']) {
$dbal['credit'] = sprint($dbal['credit'] - $dbal['debit']);
$dbal['debit'] = "";
} else {
$dbal['credit'] = "0.00";
$dbal['debit'] = "0.00";
}
// $trans .= "
// <tr class='".bg_class()."'>
// <td colspan='2'><br></td>
// <td>A/C Total</td>
// <td>Total for period $prdname to Date :</td>
// <td align='right'>$dbal[debit]</td>
// <td align='right'>$dbal[credit]</td>
// <td align='right'></td>
// <td> </td>
// </tr>";
if ($tran_flag) {
$trans .= $the_trans;
$the_trans = "";
}
}
if ($tran_flag) {
$trans .= "<tr><td colspan='8'><br></td></tr>";
}
}
$OUT = "";
if (!$pure) {
$OUT .= "<center>";
}
$OUT .= "\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>General Ledger Report</h3></td>\n\t\t\t</tr>";
if (!$pure) {
$OUT .= "\n\t\t<tr>\n\t\t\t<form action='" . SELF . "' method='post'>\n\t\t\t\t<input type='hidden' name='key' value='export' />\n\t\t\t\t<input type='hidden' name='prd' value='{$prd}' />\n\t\t\t\t<input type='hidden' name='accnt' value='{$accnt}' />\n\t\t\t\t<input type='hidden' name='fprd' value='{$fprd}' />\n\t\t\t\t<input type='hidden' name='tprd' value='{$tprd}' />\n\t\t\t\t<input type='hidden' name='openbal' value='{$openbal}' />\n\t\t\t\t" . array2form($accids, "accids") . "\n\t\t\t\t<td colspan='8' align='center'>\n\t\t\t\t\t<input type='submit' value='Export to Spreadsheet'>\n\t\t\t\t</td>\n\t\t\t</form>\n\t\t</tr>\n\t\t" . TBL_BR;
}
$OUT .= "\n\t<tr>\n\t\t<th> </th>\n\t\t<th>Date</th>\n\t\t<th>Reference</th>\n\t\t<th>Description</th>\n\t\t<th>Debit</th>\n\t\t<th>Credit</th>\n\t\t<th>Balance</th>\n\t\t<th>Contra Acc</th>\n\t</tr>\n\t{$trans}\n\t</table>";
if (!$pure) {
$OUT .= mkQuickLinks(ql("index-reports.php", "Financials"), ql("index-reports-journal.php", "Current Year Details General Ledger Reports"), ql("../core/acc-new2.php", "Add New Account"));
$OUT .= "\n\t\t</center>";
}
return $OUT;
}
function display($_POST)
{
extract($_POST);
global $PRDMON;
#determine the date range based on period selection
if (isset($year_to_process)) {
switch ($year_to_process) {
case "active":
$fdate_year = getYearOfFinPrd(1);
$fdate_month = $PRDMON[1];
$fdate_day = "1";
$tdate_year = getYearOfFinPrd(12);
$tdate_month = $PRDMON[12];
$tdate_day = date("d", mktime(0, 0, 0, $PRDMON[12] + 1, 0, $tdate_year));
break;
case "previous":
$fdate_year = getYearOfFinPrd(1) - 1;
$fdate_month = $PRDMON[1];
$fdate_day = "1";
$tdate_year = getYearOfFinPrd(12) - 1;
$tdate_month = $PRDMON[12];
$tdate_day = date("d", mktime(0, 0, 0, $PRDMON[12] + 1, 0, $tdate_year));
break;
default:
$fdate_year = getYearOfFinPrd(1);
$fdate_month = "03";
$fdate_day = "01";
$tdate_year = getYearOfFinPrd(12);
$tdate_month = "02";
$tdate_day = date("d", mktime(0, 0, 0, 3, 0, $tdate_year));
}
}
// -----------------------------------------------------------------------
// Sanity checks
// -----------------------------------------------------------------------
require_lib("validate");
$v = new validate();
// Does this employee number actually exist
db_conn("cubit");
$sql = "SELECT * FROM employees WHERE empnum='" . (int) $empnum . "' AND div='" . USER_DIV . "'";
$empinf_rslt = db_exec($sql) or errDie("Unable to retrieve employee number from Cubit.");
if (pg_num_rows($empinf_rslt) == 0) {
$v->addError(0, "Employee number not found in Cubit.");
}
$v->isOk($fdate_month, "num", 1, 2, "Invalid from date (month)");
$v->isOk($fdate_year, "num", 4, 4, "Invalid from date (year)");
$v->isOk($tdate_month, "num", 1, 2, "Invalid to date (month)");
$v->isOk($tdate_year, "num", 4, 4, "Invalid to date (year)");
if ($fdate_month > 12) {
$v->addError(0, "Invalid from date (month)");
}
if ($fdate_year < 1970 || $fdate_year > 2050) {
$v->addError(0, "Invalid from date (year)");
}
if ($tdate_month > 12) {
$v->addError(0, "Invalid to date (month)");
}
if ($tdate_year < 1970 || $tdate_year > 2050) {
$v->addError(0, "Invalid to date (year)");
}
if ($fdate_day > getDaysInMonth((int) $fdate_month, $fdate_year)) {
$v->addError(0, "Invalid from date (day)");
}
if ($tdate_day > getDaysInMonth((int) $tdate_month, $tdate_year)) {
$v->addError(0, "Invalid to date (day)");
}
$from_time = mktime(0, 0, 0, $fdate_day, $fdate_month, $fdate_year);
$to_time = mktime(0, 0, 0, $tdate_day, $tdate_month, $tdate_year);
if ($from_time > $to_time) {
$v->addError(0, "Invalid date range specified.");
}
// Return the errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>{$e['msg']}</li>";
}
return slct($confirm);
}
$from_date = "{$fdate_year}-{$fdate_month}-{$fdate_day}";
$to_date = "{$tdate_year}-{$tdate_month}-{$tdate_day}";
$gross_taxable_annual_payments = 0.0;
$gross_non_taxable_income = 0.0;
$gross_retirement_funding_income = 0.0;
$gross_non_retirement_funding_income = 0.0;
$gross_remuneration = 0.0;
db_conn("cubit");
$sql = "SELECT * FROM compinfo";
$compinfo_rslt = db_exec($sql) or errDie("Unable to retrieve company information from Cubit.");
$compinfo = pg_fetch_array($compinfo_rslt);
db_conn("cubit");
$sql = "SELECT * FROM employees WHERE empnum='{$empnum}'";
$empinfo_rslt = db_exec($sql) or errDie("Unable to retrieve employee information from Cubit.");
$empinfo = pg_fetch_array($empinfo_rslt);
$header_out = "<b>Employees tax certificate</b>";
$employer_trading_name_out = array(array("<b>Trading or other name:</b> {$compinfo['compname']}"));
$employer_irp5_number_out = array(array("<b>IRP 5 number:</b> {$irp5_number}"));
$employer_reference_number_out = array(array("<b>Reference number:</b> {$empinfo['taxref']}"));
$employer_tax_year_out = array(array("<b>Tax year:</b> " . ($fdate_year + 1)));
$employer_diplomatic_indemnity_out = array(array("<b>Diplomatic indemnity:</b> {$compinfo['diplomatic_indemnity']}"));
$employer_business_address_out = array(array("<b>Employer business address:</b>"), array("{$compinfo['addr1']}"), array("{$compinfo['addr2']}"), array("{$compinfo['addr3']}"));
$employer_postal_code_out = array(array("col1" => "<b>Postal Code:</b>", "col2" => "{$compinfo['addr4']}"));
$employer_postal_code_cols = array("col1" => array("width" => 200, "justification" => "right"), "col2" => array("width" => 40, "justification" => "right"));
// Extract the employee's birth date from her id number
$bd_year = 1900 + substr($empinfo["idnum"], 0, 2);
$bd_month = substr($empinfo["idnum"], 2, 2);
$bd_day = substr($empinfo["idnum"], 4, 2);
// Period employed from
// strip out the dashes from the date
$prd_employed_frm = explode("-", $empinfo["hiredate"]);
$prd_employed_frm = implode($prd_employed_frm);
// Period employed to
if (empty($empinfo["firedate"])) {
$prd_employed_to = getFinYear() . "0228";
} else {
$prd_employed_to = explode("-", $firedate);
$prd_employed_to = implode("", $prd_employed_to);
}
$employee_nature_out = array(array("<b>Nature of Person:</b> {$empinfo['nature']}"));
$employee_surname_out = array(array("<b>Employee surname or trading name:</b> {$empinfo['sname']}"));
$employee_first_names_out = array(array("<b>First two names:</b> {$empinfo['fnames']}"));
$fnames = explode(" ", $empinfo["fnames"]);
$initials = "";
foreach ($fnames as $name) {
$initials .= strtoupper($name[0]);
}
$employee_initials_out = array(array("<b>Initials:</b> {$initials}"));
$employee_identity_number_out = array(array("<b>Identity number:</b> {$empinfo['idnum']}"));
$employee_passport_number_out = array(array("<b>Passport number:</b> {$empinfo['passport_number']}"));
$employee_date_of_birth_out = array(array("<b>Date of birth:</b> {$bd_year}-{$bd_month}-{$bd_day}"));
$employee_cc_number_out = array(array("<b>Company/CC/Trust number:</b> {$empinfo['cc_number']}"));
$employee_tax_number_out = array(array("<b>Income Tax number:</b> {$empinfo['tax_number']}"));
$employee_residential_out = array(array("<b>Employees residential address:</b>"), array("{$empinfo['res1']}"), array("{$empinfo['res2']}"), array("{$empinfo['res3']}"));
$employee_postal_code_out = array(array("col1" => "<b>Postal Code:</b>", "col2" => "{$empinfo['res4']}"));
$employee_postal_code_cols = array("col1" => array("width" => 200, "justification" => "right"), "col2" => array("width" => 40, "justification" => "right"));
$employee_number_out = array(array("<b>Employee Number:</b> {$empinfo['empnum']}"));
$tax_prd_employed_frm_out = array(array("<b>Period employed from:</b> {$prd_employed_frm}"));
$tax_prd_employed_to_out = array(array("<b>Period employed to:</b> {$prd_employed_to}"));
$tax_over_deduction_out = array(array("<b>Voluntary over-deduction:</b> {$over_deduction}"));
switch ($empinfo["payprd"]) {
case "m":
$pay_periods = 12;
break;
case "f":
$pay_periods = 24;
break;
case "w":
$pay_periods = 48;
break;
}
$tax_prd_in_year_out = array(array("<b>Pay periods in year:</b> {$pay_periods}"));
if ($year_to_process == "active") {
$cyear = EMP_YEAR;
} else {
$cyear = EMP_YEAR - 1;
}
db_conn("cubit");
$sql = "SELECT * FROM emp_inc WHERE emp='{$empinfo['empnum']}' AND description='Basic Salary' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve the number of periods worked from Cubit.");
$pay_periods_worked = pg_num_rows($rslt);
$tax_prd_worked_out = array(array("<b>Number of periods worked:</b> {$pay_periods_worked}"));
$tax_fixed_rate_income_out = array(array("<b>Fixed rate income:</b> {$empinfo['fixed_rate']}"));
$tax_directive_number_out = array(array("<b>Directive number:</b> {$directive_number}"));
// Income sources --------------------------------------------------------
$income_sources_out = array();
$income_taxable_total = 0;
$income_commission_total = 0;
$income_bonus_total = 0;
$income_reimburse_total = 0;
$income_travelallowance_total = 0;
$income_subsis_total = 0;
$income_otherallowance_total = 0;
$deduction_motorcar_total = 0;
$deduction_medical_total = 0;
// $deduction_medicalaid_total = 0;
db_conn("cubit");
$sql = "SELECT amount FROM emp_inc WHERE emp='{$empnum}' AND description='Basic Salary' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
while ($empinc_data = pg_fetch_array($rslt)) {
$income_taxable_total += $empinc_data["amount"];
}
$income_sources_out[] = array("<b>CODE</b>" => "3601", "<b>DESCRIPTION</b>" => "Income Taxable", "<b>RF IND</b>" => "N", "<b>AMOUNT</b>" => (int) $income_taxable_total);
$sql = "SELECT amount FROM emp_inc WHERE emp='{$empnum}' AND description='Bonus' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
while ($empinc_data = pg_fetch_array($rslt)) {
$income_bonus_total += $empinc_data["amount"];
}
$income_sources_out[] = array("<b>CODE</b>" => "3605", "<b>DESCRIPTION</b>" => "Bonus", "<b>RF IND</b>" => "N", "<b>AMOUNT</b>" => (int) $income_bonus_total);
$sql = "SELECT amount FROM emp_inc WHERE emp='{$empnum}' AND description='Commission' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
while ($empinc_data = pg_fetch_array($rslt)) {
$income_commission_total += $empinc_data["amount"];
}
$income_sources_out[] = array("<b>CODE</b>" => "3606", "<b>DESCRIPTION</b>" => "Commission", "<b>RF IND</b>" => "N", "<b>AMOUNT</b>" => (int) $income_commission_total);
#handle travel allowances ....
$sql = "SELECT amount FROM emp_inc WHERE emp='{$empnum}' AND description = 'Travel Allowance' AND type = 'INCT' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
while ($empinc_data = pg_fetch_array($rslt)) {
$income_travelallowance_total += $empinc_data['amount'];
}
$income_sources_out[] = array("<b>CODE</b>" => "3701", "<b>DESCRIPTION</b>" => "Travel Allowance", "<b>RF IND</b>" => "", "<b>AMOUNT</b>" => (int) $income_travelallowance_total);
#handle reimbursements ....
$sql = "SELECT amount FROM emp_inc WHERE emp='{$empnum}' AND description!='Basic Salary' AND ex = 'RBS' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
while ($empinc_data = pg_fetch_array($rslt)) {
$income_reimburse_total += $empinc_data['amount'];
}
$income_sources_out[] = array("<b>CODE</b>" => "3702", "<b>DESCRIPTION</b>" => "Reimbursements", "<b>RF IND</b>" => "", "<b>AMOUNT</b>" => (int) $income_reimburse_total);
#handle subsistance allowances ....
$sql = "SELECT amount FROM emp_inc WHERE emp='{$empnum}' AND description != 'Basic Salary' AND ex = 'SUBS' AND type = '2' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
while ($empinc_data = pg_fetch_array($rslt)) {
$income_subsis_total += $empinc_data['amount'];
}
$income_sources_out[] = array("<b>CODE</b>" => "3704", "<b>DESCRIPTION</b>" => "Subsistance Allowance", "<b>RF IND</b>" => "", "<b>AMOUNT</b>" => (int) $income_subsis_total);
#handle other allowances ....
$sql = "SELECT amount FROM emp_inc WHERE emp='{$empnum}' AND description != 'Basic Salary' AND description != 'Travel Allowance' AND ex != 'SUBS' AND ex != 'RBS' AND type = '2' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
while ($empinc_data = pg_fetch_array($rslt)) {
$income_otherallowance_total += $empinc_data['amount'];
}
$income_sources_out[] = array("<b>CODE</b>" => "3713", "<b>DESCRIPTION</b>" => "Other Allowances", "<b>RF IND</b>" => "", "<b>AMOUNT</b>" => (int) $income_otherallowance_total);
#handle motorcar DEDUCTIONS in income table ...
$sql = "SELECT amount FROM emp_ded WHERE emp='{$empnum}' AND (description = 'Motorcar 1 Contribution for Use' OR description = 'Motorcar 2 Contribution for Use') AND (type = 'DEDA' OR type = 'DEDB') AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
while ($empinc_data = pg_fetch_array($rslt)) {
$deduction_motorcar_total += $empinc_data['amount'];
}
$income_sources_out[] = array("<b>CODE</b>" => "3802", "<b>DESCRIPTION</b>" => "Use Of Motor Vehicle", "<b>RF IND</b>" => "", "<b>AMOUNT</b>" => (int) $deduction_motorcar_total);
#handle medical DEDUCTIONS in income table ...
//$sql = "SELECT amount FROM emp_com WHERE emp='$empnum' AND description = 'Medical Contribution' AND type = 'DEDM'";
$sql = "SELECT amount FROM emp_com WHERE emp='{$empnum}' AND description = 'Medical Contribution' AND type = 'COMM' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
while ($empinc_data = pg_fetch_array($rslt)) {
$deduction_medical_total += $empinc_data['amount'];
}
//"<b>CODE</b>" => "3816",
$income_sources_out[] = array("<b>CODE</b>" => "3810", "<b>DESCRIPTION</b>" => "Medical Contribution", "<b>RF IND</b>" => "", "<b>AMOUNT</b>" => (int) $deduction_medical_total);
#handle medical aid DEDUCTIONS in income table ...
// $sql = "SELECT amount FROM emp_ded WHERE emp='$empnum' AND description = 'Medical Contribution' AND type = 'DEDM'";
// $rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
// while ($empinc_data = pg_fetch_array($rslt)) {
// $deduction_medicalaid_total += $empinc_data['amount'];
// }
// $income_sources_out[] = array (
// "<b>CODE</b>" => "3802",
// "<b>DESCRIPTION</b>" => "Medical aid contributions",
// "<b>RF IND</b>" => "",
// "<b>AMOUNT</b>" => (int)$deduction_medicalaid_total
// );
// old
// $sql = "SELECT * FROM emp_inc WHERE emp='$empnum' AND description!='Basic Salary'";
// $rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
// while ($empinc_data = pg_fetch_array($rslt)) {
// $income_sources_out[] = array (
// "<b>CODE</b>"=>"$empinc_data[code]",
// "<b>DESCRIPTION</b>"=>"$empinc_data[description]",
// "<b>RF IND</b>"=>"",
// "<b>AMOUNT</b>"=>(int)$empinc_data["amount"]
// );
// }
if (!empty($nincome_description) || !empty($nincome_amount) && $nincome_code != 0) {
$income_sources_out[] = array("<b>CODE</b>" => "{$nincome_code}", "<b>DESCRIPTION</b>" => "{$nincome_description}", "<b>RF IND</b>" => "{$nincome_rfind}", "<b>AMOUNT</b>" => (int) $nincome_amount);
}
if (isset($income_code)) {
foreach ($income_code as $id => $value) {
if ($income_code[$id] != 0) {
$income_sources_out[] = array("<b>CODE</b>" => "{$income_code[$id]}", "<b>DESCRIPTION</b>" => "{$income_description[$id]}", "<b>RF IND</b>" => "{$income_rfind[$id]}", "<b>AMOUNT</b>" => (int) $income_amount[$id]);
} else {
$income_sources_out[] = array("<b>CODE</b>" => "", "<b>DESCRIPTION</b>" => "", "<b>RF IND</b>" => "", "<b>AMOUNT</b>" => "");
}
}
}
$income_sources_cols = array("<b>CODE</b>" => array("width" => 40), "<b>DESCRIPTION</b>" => array("width" => 340), "<b>RF IND</b>" => array("width" => 70), "<b>AMOUNT</b>" => array("width" => 70));
// Gross renumeration ----------------------------------------------------
// Taxable annual payments
db_conn("cubit");
$sql = "SELECT * FROM emp_inc WHERE (code='3695' OR code='3601' OR description='Bonus') AND emp = '{$empnum}' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve taxable annual payments from Cubit.");
while ($emp_inc_data = pg_fetch_array($rslt)) {
$gross_taxable_annual_payments += $emp_inc_data["amount"];
}
db_conn("cubit");
$sql = "SELECT * FROM emp_income_sources WHERE (code='3695' OR code='3601') AND empnum = '{$empnum}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve taxable annual payments from Cubit.");
while ($emp_income_sources_data = pg_fetch_array($rslt)) {
$gross_taxable_annual_payments += $emp_income_sources_data["amount"];
}
// Non taxable annual payments
db_conn("cubit");
$sql = "SELECT * FROM emp_inc WHERE (code='3602' OR code='3604' OR code='3612' OR code='3703' OR code='3705' OR code='3709' OR code='3714') AND emp = '{$empnum}' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve non taxable payments from Cubit.");
while ($emp_inc_data = pg_fetch_array($rslt)) {
$gross_non_taxable_income += $emp_inc_data["amount"];
}
db_conn("cubit");
$sql = "SELECT * FROM emp_income_sources WHERE (code='3602' OR code='3604' OR code='3612' OR code='3703' OR code='3705' OR code='3709' OR code='3714') AND empnum = '{$empnum}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve non taxable payments from Cubit.");
while ($emp_income_sources_data = pg_fetch_array($rslt)) {
if ($emp_income_sources_data["rf_ind"] == "Y") {
$gross_non_taxable_income += $emp_income_sources_data["amount"];
}
}
// Gross retirement funding income
db_conn("cubit");
$sql = "SELECT emp_pension, emp_ret FROM employees WHERE empnum='{$empnum}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve gross retrirement funding from Cubit.");
$emp_data = pg_fetch_array($rslt);
// RF IND
$rfind_y = 0;
if (isset($income_rfind)) {
foreach ($income_rfind as $id => $value) {
if ($income_rfind[$id] == "Y") {
$rfind_y += $income_amount[$id];
}
}
}
//wth emp_pension is a PERCENTAGE, not some amount to add
// $gross_retirement_funding_income += $emp_data["emp_pension"] + $emp_data["emp_ret"] + $rfind_y;
$gross_retirement_funding_income += $gross_taxable_annual_payments / 100 * $emp_data["emp_pension"] + $emp_data["emp_ret"] + $rfind_y;
// Gross non retirement funding income
db_conn("cubit");
$sql = "SELECT * FROM emp_inc WHERE ((code != '3601' AND code != '3603' AND code != '3604' AND code != '3610' AND code != '3615') OR description = 'Basic Salary') AND emp = '{$empnum}' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve gross non retirement funding from Cubit.");
// RF IND
$rfind_n = 0;
if (isset($income_rfind)) {
foreach ($income_rfind as $id => $value) {
if ($income_rfind[$id] == "N") {
$rfind_n += $income_amount[$id];
}
}
}
while ($emp_inc_data = pg_fetch_array($rslt)) {
$gross_non_retirement_funding_income += $emp_inc_data["amount"] + $rfind_n;
}
db_conn("cubit");
$sql = "SELECT * FROM emp_income_sources WHERE (code != '3601' AND code != '3603' AND code != '3604' AND code != '3610' AND code!='3615') AND empnum = '{$empnum}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve gross non retirement funding from Cubit.");
while ($emp_income_sources_data = pg_fetch_array($rslt)) {
if ($emp_income_sources_data["rf_ind"] == "N") {
$gross_non_retirement_funding_income += $emp_income_sources_data["amount"];
}
}
// Gross Remuneration
$gross_remuneration = $gross_retirement_funding_income + $gross_non_retirement_funding_income;
$gross_remuneration_out = array(array("<b>CODE</b>" => "3695", "<b>DESCRIPTION</b>" => "GROSS TAXABLE ANNUAL PAYMENTS", "<b>AMOUNT</b>" => (int) $gross_taxable_annual_payments), array("<b>CODE</b>" => "3696", "<b>DESCRIPTION</b>" => "GROSS NON-TAXABLE INCOME", "<b>AMOUNT</b>" => (int) $gross_non_taxable_income), array("<b>CODE</b>" => "3697", "<b>DESCRIPTION</b>" => "GROSS RETIREMENT FUNDING INCOME", "<b>AMOUNT</b>" => (int) $gross_retirement_funding_income), array("<b>CODE</b>" => "3698", "<b>DESCRIPTION</b>" => "GROSS NON-RETIREMENT FUNDING INCOME", "<b>AMOUNT</b>" => (int) $gross_non_retirement_funding_income), array("<b>CODE</b>" => "3699", "<b>DESCRIPTION</b>" => "GROSS REMUNERATION", "<b>AMOUNT</b>" => (int) $gross_remuneration));
$gross_remuneration_cols = array("<b>CODE</b>" => array("width" => 40), "<b>DESCRIPTION</b>" => array("width" => 410), "<b>AMOUNT</b>" => array("width" => 70));
// Deductions ------------------------------------------------------------
$deductions_out = array();
$deduction_pension_total = 0;
$deduction_provident_total = 0;
$deduction_medicalaid_total = 0;
$deduction_medical_total2 = 0;
$deduction_retirementann_total = 0;
$deduction_premiumpol_total = 0;
db_conn("cubit");
$sql = "SELECT * FROM emp_deductions WHERE empnum='{$empnum}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee deductions from Cubit.");
while ($ded_data = pg_fetch_array($rslt)) {
$deductions_out[] = array("<b>CODE</b>" => "{$ded_data['code']}", "<b>DESCRIPTION</b>" => "{$ded_data['description']}", "<b>CLEARANCE NO</b>" => "{$ded_data['clearance_no']}", "<b>AMOUNT</b>" => (int) $ded_data["amount"]);
}
#handle pension deduction ...
$sql = "SELECT amount FROM emp_ded WHERE emp='{$empnum}' AND description = 'Pension' AND type = 'DEDP' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee deductions from Cubit.");
while ($empded_data = pg_fetch_array($rslt)) {
$deduction_pension_total += $empded_data['amount'];
}
$deductions_out[] = array("<b>CODE</b>" => "4001", "<b>DESCRIPTION</b>" => "Current pension fund contributions", "<b>CLEARANCE NO</b>" => "", "<b>AMOUNT</b>" => (int) $deduction_pension_total);
#handle provident deduction ...
$sql = "SELECT amount FROM emp_ded WHERE emp='{$empnum}' AND description = 'Provident' AND type = 'DEDV' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee deductions from Cubit.");
while ($empded_data = pg_fetch_array($rslt)) {
$deduction_provident_total += $empded_data['amount'];
}
$deductions_out[] = array("<b>CODE</b>" => "4003", "<b>DESCRIPTION</b>" => "Current provident fund contributions", "<b>CLEARANCE NO</b>" => "", "<b>AMOUNT</b>" => (int) $deduction_provident_total);
#handle medical DEDUCTIONS in income table ...
$sql = "SELECT amount FROM emp_ded WHERE emp='{$empnum}' AND description = 'Medical Contribution' AND type = 'DEDM' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee income sources from Cubit.");
while ($empinc_data = pg_fetch_array($rslt)) {
$deduction_medical_total2 += $empinc_data['amount'];
}
$deductions_out[] = array("<b>CODE</b>" => "4005", "<b>DESCRIPTION</b>" => "Medical Contribution", "<b>RF IND</b>" => "", "<b>AMOUNT</b>" => (int) $deduction_medical_total2);
#handle retirement annuity deduction ...
$sql = "SELECT amount FROM emp_ded WHERE emp='{$empnum}' AND description = 'Retirement Annuity Fund' AND type = 'DEDR' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee deductions from Cubit.");
while ($empded_data = pg_fetch_array($rslt)) {
$deduction_retirementann_total += $empded_data['amount'];
}
$deductions_out[] = array("<b>CODE</b>" => "4006", "<b>DESCRIPTION</b>" => "Current retirement annuity fund contributions", "<b>CLEARANCE NO</b>" => "", "<b>AMOUNT</b>" => (int) $deduction_retirementann_total);
$sql = "SELECT * FROM emp_ded WHERE emp='{$empnum}' AND description!='UIF' AND description!='SDL' AND description!='PAYE' AND description!='Motorcar 1 Contribution for Use' AND description!='Motorcar 2 Contribution for Use' AND description!='Medical Contribution' AND description!='Pension' AND description!='Provident' AND description!='Retirement Annuity Fund' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve employee deductions from Cubit.");
while ($empded_data = pg_fetch_array($rslt)) {
$deduction_premiumpol_total += $empded_data['amount'];
}
$deductions_out[] = array("<b>CODE</b>" => "4018", "<b>DESCRIPTION</b>" => "Premiums paid on loss of income policies", "<b>CLEARANCE NO</b>" => "", "<b>AMOUNT</b>" => (int) $deduction_premiumpol_total);
//old all encapsulating search ... only first to description checks were orginal ...
// $sql = "SELECT * FROM emp_ded WHERE emp='$empnum' AND description!='UIF' AND description!='SDL' AND description!='Motorcar 1 Contribution for Use' AND description!='Motorcar 2 Contribution for Use' AND description!='Medical Contribution' AND description!='Pension' AND description!='Provident' AND description!='Retirement Annuity Fund'";
// $rslt = db_exec($sql) or errDie("Unable to retrieve employee deductions from Cubit.");
// while ($empded_data = pg_fetch_array($rslt)) {
// if ($empded_data["type"] != "PAYE") {
// $deductions_out[] = array (
// "<b>CODE</b>"=>"$empded_data[code]",
// "<b>DESCRIPTION</b>"=>"$empded_data[description]",
// "<b>CLEARANCE NO</b>"=>"",
// "<b>AMOUNT</b>"=>(int)$empded_data["amount"]
// );
// }
// }
// cant just remove whatever deductions is on system if they havent actually been applied to salary (ie. salary processed)
// this value will come from same place as the rest of the income/deductions ... wth
// db_conn("cubit");
// $sql = "SELECT * FROM empdeduct WHERE empnum='$empnum'";
// $empded_rslt = db_exec($sql) or errDie("Unable to retrieve employee deductions from Cubit.'");
//
// while ($empded_data = pg_fetch_array($empded_rslt)) {
// db_conn("cubit");
// $sql = "SELECT deduction, code FROM salded WHERE id='$empded_data[dedid]' AND deduction!='UIF'";
// $rslt = db_exec($sql) or errDie("Unable to retrieve deduction information from Cubit.");
// $ded_data = pg_fetch_array($rslt);
//
// $deductions_out[] = array (
// "<b>CODE</b>"=>"$ded_data[code]",
// "<b>DESCRIPTION</b>"=>"$ded_data[deduction]",
// "<b>CLEARANCE NO</b>"=>"$empded_data[clearance_no]",
// "<b>AMOUNT</b>"=>(int)$empded_data["amount"], 2
// );
// }
if (!isset($deductions_out[0])) {
$deductions_out = array(array("<b>CODE</b>" => "", "<b>DESCRIPTION</b>" => "", "<b>CLEARANCE NO</b>" => "", "<b>AMOUNT</b>" => ""));
}
$deductions_cols = array("<b>CODE</b>" => array("width" => 40), "<b>DESCRIPTION</b>" => array("width" => 340), "<b>CLEARANCE NO</b>" => array("width" => 70), "<b>AMOUNT</b>" => array("width" => 70));
// Employees Tax deductions-----------------------------------------------
$tax_amt = 0;
// Retrieve PAYE amount from Cubit
db_conn("cubit");
$sql = "SELECT * FROM emp_ded WHERE type='PAYE' AND emp = '{$empnum}' AND year = '{$cyear}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve PAYE deductions from Cubit");
$paye_amount = 0;
while ($empded_data = pg_fetch_array($rslt)) {
// add 375 to site if salary, -375 if reverse salary
$tax_amt += 375 * ($empded_data["amount"] > 0 ? 1 : -1);
$paye_amount += $empded_data["amount"];
}
if ($tax_amt > $paye_amount) {
$tax_amt = $paye_amount;
$paye_amount = 0;
} else {
$paye_amount -= $tax_amt;
}
$tax_deductions_amount = $paye_amount + $tax_amt;
$employees_tax_deductions_out = array(array("<b>CODE</b>" => "4101", "<b>DESCRIPTION</b>" => "STANDARD INCOME TAX ON EMPLOYEES-SITE", "<b>AMOUNT</b>" => sprint($tax_amt, 2)), array("<b>CODE</b>" => "4102", "<b>DESCRIPTION</b>" => "PAY AS YOU EARN - PAYE", "<b>AMOUNT</b>" => sprint($paye_amount, 2)), array("<b>CODE</b>" => "4103", "<b>DESCRIPTION</b>" => "TOTAL EMPLOYEES TAX", "<b>AMOUNT</b>" => sprint($tax_deductions_amount, 2)));
$employees_tax_deductions_cols = array("<b>CODE</b>" => array("width" => 40), "<b>DESCRIPTION</b>" => array("width" => 410), "<b>AMOUNT</b>" => array("width" => 70));
// -----------------------------------------------------------------------
// Do the actual rendering of the pdf
// -----------------------------------------------------------------------
$pdf =& new Cezpdf();
global $set_mainFont;
$pdf->selectFont($set_mainFont);
$pdf->addInfo("Title", "IRP 5 for {$empinfo['fnames']} {$empinfo['sname']}");
$pdf->addInfo("Author", USER_NAME);
$irp5_pos = drawText(&$pdf, "<b>IRP 5</b>", 14, 520 - $pdf->getTextWidth(14, "<b>IRP 5</b>"), 0);
$header_pos = drawText(&$pdf, $header_out, 12, 520 - $pdf->getTextWidth(12, $header_out), $irp5_pos['y'] + 14);
// Employer information --------------------------------------------------
$employer_information_head = drawText(&$pdf, "<b>EMPLOYER INFORMATION</b>", 8, 0, $irp5_pos['y'] + 14);
$employer_trading_name_pos = drawTable2(&$pdf, $employer_trading_name_out, 0, $employer_information_head['y'] + 2, 520, 1);
$employer_irp5_number_pos = drawTable2(&$pdf, $employer_irp5_number_out, 0, $employer_trading_name_pos['y'], 260, 1);
$employer_reference_number_pos = drawTable2(&$pdf, $employer_reference_number_out, 0, $employer_irp5_number_pos['y'], 260, 1);
$employer_tax_year_pos = drawTable2(&$pdf, $employer_tax_year_out, 0, $employer_reference_number_pos['y'], 260, 1);
$employer_diplomatic_indemnity_pos = drawTable2(&$pdf, $employer_diplomatic_indemnity_out, 0, $employer_tax_year_pos['y'], 260, 1);
$employer_business_address_pos = drawTable2(&$pdf, $employer_business_address_out, $employer_irp5_number_pos['x'] + 20, $employer_trading_name_pos['y'], 240, 4);
$employer_postal_code_pos = drawTable2(&$pdf, $employer_postal_code_out, $employer_irp5_number_pos['x'] + 20, $employer_business_address_pos['y'], 240, 1, $employer_postal_code_cols);
// Employee information --------------------------------------------------
$employee_information_head = drawText(&$pdf, "<b>EMPLOYEE INFORMATION</b>", 8, 0, $employer_postal_code_pos['y'] + 15);
$employee_nature_pos = drawTable2(&$pdf, $employee_nature_out, 0, $employee_information_head['y'], 100, 1);
$employee_surname_pos = drawTable2(&$pdf, $employee_surname_out, $employee_nature_pos['x'] + 20, $employee_information_head['y'], 400, 1);
$employee_first_names_pos = drawTable2(&$pdf, $employee_first_names_out, 0, $employee_nature_pos['y'], 400, 1);
$employee_initials_pos = drawTable2(&$pdf, $employee_initials_out, $employee_first_names_pos['x'] + 20, $employee_nature_pos['y'], 100, 1);
$employee_identity_number_pos = drawTable2(&$pdf, $employee_identity_number_out, 0, $employee_first_names_pos['y'], 260, 1);
$employee_residential_pos = drawTable2(&$pdf, $employee_residential_out, $employee_identity_number_pos['x'] + 20, $employee_first_names_pos['y'], 240, 4);
$employee_postal_code_pos = drawTable2(&$pdf, $employee_postal_code_out, $employee_identity_number_pos['x'] + 20, $employee_residential_pos['y'], 240, 1, $employee_postal_code_cols);
$employee_number_pos = drawTable2(&$pdf, $employee_number_out, $employee_identity_number_pos['x'] + 20, $employee_postal_code_pos['y'], 240, 1);
$employee_passport_number_pos = drawTable2(&$pdf, $employee_passport_number_out, 0, $employee_identity_number_pos['y'], 260, 1);
$employee_date_of_birth_pos = drawTable2(&$pdf, $employee_date_of_birth_out, 0, $employee_passport_number_pos['y'], 260, 1);
$employee_cc_number_pos = drawTable2(&$pdf, $employee_cc_number_out, 0, $employee_date_of_birth_pos['y'], 260, 1);
$employee_tax_number_pos = drawTable2(&$pdf, $employee_tax_number_out, 0, $employee_cc_number_pos['y'], 260, 1);
// Tax calculation information -------------------------------------------
$tax_calculation_head = drawText(&$pdf, "<b>TAX CALCULATION INFORMATION</b>", 8, 0, $employee_number_pos['y'] + 15);
$tax_prd_employed_frm_pos = drawTable2(&$pdf, $tax_prd_employed_frm_out, 0, $tax_calculation_head['y'], 160, 1);
$tax_prd_employed_to_pos = drawTable2(&$pdf, $tax_prd_employed_to_out, $tax_prd_employed_frm_pos['x'] + 20, $tax_calculation_head['y'], 160, 1);
$tax_over_deduction_pos = drawTable2(&$pdf, $tax_over_deduction_out, $tax_prd_employed_to_pos['x'] + 20, $tax_calculation_head['y'], 160, 1);
$tax_prd_in_year_pos = drawTable2(&$pdf, $tax_prd_in_year_out, 0, $tax_prd_employed_frm_pos['y'], 115, 1);
$tax_prd_worked_pos = drawTable2(&$pdf, $tax_prd_worked_out, $tax_prd_in_year_pos['x'] + 20, $tax_prd_employed_frm_pos['y'], 115, 1);
$tax_fixed_rate_income_pos = drawTable2(&$pdf, $tax_fixed_rate_income_out, $tax_prd_worked_pos['x'] + 20, $tax_prd_employed_frm_pos['y'], 115, 1);
$tax_directive_number_pos = drawTable2(&$pdf, $tax_directive_number_out, $tax_fixed_rate_income_pos['x'] + 20, $tax_prd_employed_frm_pos['y'], 115, 1);
// Income sources --------------------------------------------------------
$income_sources_head = drawText(&$pdf, "<b>INCOME SOURCE</b>", 8, 0, $tax_prd_in_year_pos['y'] + 15);
$income_sources_pos = drawTable2(&$pdf, $income_sources_out, 0, $income_sources_head['y'] + 2, 520, 20, $income_sources_cols, 1);
$gross_remuneration_head = drawText(&$pdf, "<b>GROSS REMUNERATION</b>", 8, 0, $income_sources_pos['y'] + 15);
$gross_remuneration_pos = drawTable2(&$pdf, $gross_remuneration_out, 0, $gross_remuneration_head['y'] + 2, 520, 5, $gross_remuneration_cols, 1);
$deductions_head = drawText(&$pdf, "<b>DEDUCTIONS</b>", 8, 0, $gross_remuneration_pos['y'] + 15);
$deductions_pos = drawTable2(&$pdf, $deductions_out, 0, $deductions_head['y'] + 2, 520, 15, $deductions_cols, 1);
$employees_tax_deductions_head = drawText(&$pdf, "<b>EMPLOYEES TAX DEDUCTIONS</b>", 8, 0, $deductions_pos['y'] + 15);
$employees_tax_deductions_pos = drawTable2(&$pdf, $employees_tax_deductions_out, 0, $employees_tax_deductions_head['y'] + 2, 520, 3, $employees_tax_deductions_cols, 1);
// Footer note -----------------------------------------------------------
$certificate_attatch = drawText(&$pdf, "Attach this certificate to your income tax return or retain it. / ", 6, 0, $employees_tax_deductions_pos['y'] + 10);
$pdf->ezStream();
}
function viewtran($_POST)
{
global $MONPRD, $PRDMON;
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($accnt, "string", 1, 10, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($accids)) {
foreach ($accids as $key => $accid) {
$v->isOk($accid, "num", 1, 20, "Invalid Account number.");
}
} else {
return "<li class='err'>Please select at least one account.</li>" . slctacc();
}
}
if ($v->isError()) {
$err = $v->genErrors();
return $confirm;
}
if ($_POST["key"] == "export") {
$pure = true;
} else {
$pure = false;
}
if ($accnt == 'all') {
$accids = array();
core_connect();
$sql = "SELECT accid FROM accounts WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$accids[] = $ac['accid'];
}
} else {
return "<li calss='err'> There are no accounts yet in Cubit.</li>";
}
} else {
if ($accnt == "allactive") {
$accids = array();
$sql = "SELECT accid FROM core.trial_bal\n\t\t\t\tWHERE (debit!=0 OR credit!=0) AND div='" . USER_DIV . "'\n\t\t\t\t\tAND period<='" . $MONPRD[PRD_DB] . "'\n\t\t\t\tGROUP BY accid";
$qry = new dbSql($sql);
$qry->run();
while ($macc_data = $qry->fetch_array()) {
$accids[] = $macc_data["accid"];
}
}
}
$hide = "";
$trans = "";
foreach ($accids as $key => $accid) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
$trans .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t</tr>";
db_conn("audit");
$sql = "SELECT prd.*, map.period FROM audit.closedprd prd, core.prdmap map\n\t\t\t\tWHERE prd.prdnum=map.month\n\t\t\t\tORDER BY map.period::integer";
$clsRs = db_exec($sql) or errDie("Could not get closed periods from audit DB", SELF);
while ($cls = pg_fetch_array($clsRs)) {
$prd = $cls['prdnum'];
# Period name
$prdname = prdname($prd);
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='8' align='center'><h3>{$prdname}</h3></td>\n\t\t\t\t</tr>";
$hide = "";
if (isset($t)) {
unset($t);
}
# Get balances
$idRs = get($prd, "max(id), min(id)", "ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", "ledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "cbalance,dbalance", "ledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
} else {
if (!isset($t)) {
$trans .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td colspan='8' align='center'><li> There are no transactions in this period.</td>\n\t\t\t\t\t\t</tr>";
}
continue;
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
$cbal['cbalance'] = 0;
$cbal['dbalance'] = 0;
}
$t = "lemme ci";
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DT";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CT";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
$balance = sprint($balance);
$bal['cbalance'] = sprint($bal['cbalance']);
$bal['dbalance'] = sprint($bal['dbalance']);
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
if ($prd < $PRDMON[1]) {
++$prd_y;
}
// make the date of the last day of the previous prd
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, $prd_y));
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t\t<td>Br/Forwd</td><td>Brought Forward</td>\n\t\t\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
# --> Transaction reding comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
$tranRs = get($prd, "*", "ledger", "acc", $accid);
while ($tran = pg_fetch_array($tranRs)) {
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
# Current(Running) balance
if ($tran['dbalance'] > $tran['cbalance']) {
$tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
$tran['cbalance'] = "";
$cbalance = $tran['dbalance'];
$cfl = "DT";
} elseif ($tran['cbalance'] > $tran['dbalance']) {
$tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
$tran['dbalance'] = "";
$cbalance = $tran['cbalance'];
$cfl = "CT";
} else {
$tran['cbalance'] = "";
$tran['dbalance'] = "";
$cbalance = "0.00";
$cfl = "";
}
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
$trans .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td><br></td>\n\t\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t\t<td align='right'>{$cbalance} {$cfl}</td>\n\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t</tr>";
}
# Total balance changes
if ($dbal['debit'] > $dbal['credit']) {
$dbal['debit'] = sprint($dbal['debit'] - $dbal['credit']);
$dbal['credit'] = "";
} elseif ($dbal['credit'] > $dbal['debit']) {
$dbal['credit'] = sprint($dbal['credit'] - $dbal['debit']);
$dbal['debit'] = "";
} else {
$dbal['credit'] = "0.00";
$dbal['debit'] = "0.00";
}
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t\t<td>A/C Total</td>\n\t\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
}
$trans .= "<tr><td colspan='8'><br></td></tr>";
}
$OUT = "";
if (!$pure) {
$OUT .= "<center>";
}
$OUT .= "\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>Year Review General Ledger</h3></td>\n\t\t\t</tr>";
if (!$pure) {
$OUT .= "\n\t\t<tr>\n\t\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='prd' value='{$prd}' />\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}' />\n\t\t\t" . array2form($accids, "accids") . "\n\t\t\t<td colspan='8' align='center'>\n\t\t\t\t<input type='submit' value='Export to Spreadsheet'>\n\t\t\t</td>\n\t\t\t</form>\n\t\t</tr>\n\t\t" . TBL_BR;
}
$OUT .= "\n\t<tr>\n\t\t<th> </th>\n\t\t<th>Date</th>\n\t\t<th>Reference</th>\n\t\t<th>Description</th>\n\t\t<th>Debit</th>\n\t\t<th>Credit</th>\n\t\t<th>Balance</th>\n\t\t<th>Contra Acc</th>\n\t</tr>\n\t{$trans}\n\t</table>";
if (!$pure) {
$OUT .= mkQuickLinks(ql("index-reports.php", "Financials"), ql("index-reports-journal.php", "Current Year Details General Ledger Reports"), ql("../core/acc-new2.php", "Add New Account"));
$OUT .= "\n\t\t</center>";
}
return $OUT;
}
/**
* gets the real year of a period in the active financial year
*
* @param int $mon month for which you wish to find out the year
* @return int
*/
function getYearOfFinPrd($prd)
{
global $PRDMON, $MONPRD;
$mon = $PRDMON[$prd];
$fyear = getFinYear();
if ($PRDMON[1] == 1) {
return $fyear;
}
if ($mon >= $PRDMON[1]) {
--$fyear;
}
return (int) $fyear;
}
function viewtran($_POST)
{
# Get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
$v->isOk($accnt, "string", 1, 9, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($accids)) {
foreach ($accids as $key => $accid) {
$v->isOk($accid, "num", 1, 20, "Invalid Account number.");
}
} else {
$v->isOk("###", "num", 0, 0, "ERROR : Please select at least one account.");
}
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get the ids
if ($accnt == 'all') {
$accids = array();
core_connect();
$sql = "SELECT accid FROM accounts WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$accids[] = $ac['accid'];
}
} else {
return "<li calss=err> There are no accounts yet in Cubit.";
}
}
# Period name
$prdname = prdname($prd);
$trans = "";
foreach ($accids as $key => $accid) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
# Get balances
$idRs = get($prd, "max(id), min(id)", "ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", "ledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "cbalance,dbalance", "ledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
} else {
if ($prd != PRD_DB) {
continue;
}
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
$cbal['cbalance'] = 0;
$cbal['dbalance'] = 0;
}
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DR";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CR";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
$balance = sprint($balance);
$bal['cbalance'] = sprint($bal['cbalance']);
$bal['dbalance'] = sprint($bal['dbalance']);
global $MONPRD, $PRDMON;
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
if ($prd < $PRDMON[1]) {
++$prd_y;
}
// make the date of the last day of the previous prd
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, $prd_y));
$trans .= "\n\t\t<tr>\n\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t<td>Br/Forwd</td>\n\t\t\t<td>Brought Forward</td>\n\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t<td> </td>\n\t\t</tr>";
# --> transactio reding comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
if ($t == "s") {
$tranRs = get($prd, "*", "ledger", "acc", $accid);
} else {
$tranRs = get($prd, "*", "ledger", "acc", $accid, "ORDER BY edate,id");
}
while ($tran = pg_fetch_array($tranRs)) {
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
if ($t == "t") {
$tran['dbalance'] = $dbal['debit'] + $bal['dbalance'];
$tran['cbalance'] = $dbal['credit'] + $bal['cbalance'];
}
# Current(Running) balance
if ($tran['dbalance'] > $tran['cbalance']) {
$tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
$tran['cbalance'] = "";
$cbalance = $tran['dbalance'];
$cfl = "DR";
} elseif ($tran['cbalance'] > $tran['dbalance']) {
$tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
$tran['dbalance'] = "";
$cbalance = $tran['cbalance'];
$cfl = "CR";
} else {
$tran['cbalance'] = "";
$tran['dbalance'] = "";
$cbalance = "0.00";
$cfl = "";
}
if ($t == "s") {
$tran['edate'] = $tran['sdate'];
}
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
$trans .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td><br></td>\n\t\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t\t<td align='right'>{$cbalance} {$cfl}</td>\n\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t</tr>";
}
# Total balance changes
if ($dbal['debit'] > $dbal['credit']) {
$dbal['debit'] = sprint($dbal['debit'] - $dbal['credit']);
$dbal['credit'] = "";
} elseif ($dbal['credit'] > $dbal['debit']) {
$dbal['credit'] = sprint($dbal['credit'] - $dbal['debit']);
$dbal['debit'] = "";
} else {
$dbal['credit'] = "";
$dbal['debit'] = "0.00";
}
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t\t<td>A/C Total</td>\n\t\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
$trans .= "<tr><td colspan=8><br></td></tr>";
}
$sp = " ";
$OUTPUT = "\n\t\t\t<center>\n\t\t\t<h3>General Ledger</h3>\n\t\t\t<table " . TMPL_tblDflts . " width='90%'>\n\t\t\t\t<tr>\n\t\t\t\t\t<td width='50%' align='left' colspan='4'>" . COMP_NAME . "</td>\n\t\t\t\t\t<td width='50%' align='right' colspan='4'>" . date("Y-m-d") . "</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$sp}</td>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Reference</th>\n\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t<th>Debit</th>\n\t\t\t\t\t<th>Credit</th>\n\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t<th>Contra Acc</th>\n\t\t\t\t</tr>\n\t\t\t\t{$trans}\n\t\t\t<table>";
require_lib("xls");
StreamXLS("GeneralLedger" . date("dmY"), $OUTPUT);
# return $view;
}
function save_statement($stmnt, $tbl_pfx)
{
$stmnt = clean_html($stmnt);
$cols = grp(m("gendate", raw("CURRENT_DATE")), m("output", base64_encode($stmnt)), m("div", USER_DIV), m("des", "Saved by " . USER_NAME . " at Year End: " . getFinYear()));
$stmnt = new dbUpdate("save_{$tbl_pfx}", "core", $cols);
$stmnt->run(DB_INSERT);
}
