function confirm($_POST)
{
# Get vars
extract($_POST);
if (isset($back)) {
return slctacc($_POST);
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($supid, "num", 1, 50, "Invalid Supplier number.");
$v->isOk($accid, "num", 1, 50, "Invalid Contra Account.");
$v->isOk($refnum, "num", 1, 10, "Invalid Reference number.");
$v->isOk($amount, "float", 1, 20, "Invalid Amount.");
$v->isOk($details, "string", 0, 255, "Invalid Details.");
$v->isOk($author, "string", 1, 30, "Invalid Authorising person name.");
$datea = explode("-", $date);
if (count($datea) == 3) {
if (!checkdate($datea[1], $datea[0], $datea[2])) {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
} else {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get contra account details
$accRs = get("core", "*", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
# Select supplier
db_connect();
$sql = "SELECT * FROM suppliers WHERE supid = '{$supid}' AND div = '" . USER_DIV . "'";
$suppRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($suppRslt) < 1) {
return "<li class='err'>Invalid supplier ID, or supplier has been blocked</li>";
} else {
$supp = pg_fetch_array($suppRslt);
}
# Probe tran type
if ($entry == "CT") {
$tran = "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t\t\t<td>{$supp['supno']} - {$supp['supname']}</td>\n\t\t\t\t\t\t</tr>";
} else {
$tran = "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$supp['supno']} - {$supp['supname']}</td>\n\t\t\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t\t</tr>";
}
// Layout
$confirm = "\n\t\t\t<h3>Record Journal transaction</h3>\n\t\t\t<h4>Confirm entry</h4>\n\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t<input type='hidden' name='key' value='write'>\n\t\t\t\t<input type='hidden' name='supid' value='{$supid}'>\n\t\t\t\t<input type='hidden' name='accid' value='{$accid}'>\n\t\t\t\t<input type='hidden' name='accname' value='{$acc['accname']}'>\n\t\t\t\t<input type='hidden' name='date' value='{$date}'>\n\t\t\t\t<input type='hidden' name='refnum' value='{$refnum}'>\n\t\t\t\t<input type='hidden' name='entry' value='{$entry}'>\n\t\t\t\t<input type='hidden' name='amount' value='{$amount}'>\n\t\t\t\t<input type='hidden' name='details' value='{$details}'>\n\t\t\t\t<input type='hidden' name='author' value='{$author}'>\n\t\t\t\t<input type='hidden' name='date_day' value='{$date_day}'>\n\t\t\t\t<input type='hidden' name='date_month' value='{$date_month}'>\n\t\t\t\t<input type='hidden' name='date_year' value='{$date_year}'>\n\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t<tr>\n\t\t\t\t\t<td width='50%'><h3>Debit</h3></td>\n\t\t\t\t\t<td width='50%'><h3>Credit</h3></td>\n\t\t\t\t</tr>\n\t\t\t\t{$tran}\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>Date</td>\n\t\t\t\t\t<td>{$date}</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>Referance number</td>\n\t\t\t\t\t<td>{$refnum}</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>Amount</td>\n\t\t\t\t\t<td>" . CUR . " {$amount}</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>Details</td>\n\t\t\t\t\t<td>{$details}</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>Authorising Person</td>\n\t\t\t\t\t<td>{$author}</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td><input type='submit' name='back' value='« Correction'></td>\n\t\t\t\t\t<td align='right'><input type='submit' value='Write »'></td>\n\t\t\t\t</tr>\n\t\t\t</table>\n\t\t\t</form>\n\t\t\t<p>\n\t\t\t<table border='0' cellpadding='2' cellspacing='1' width='15%'>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td align='center'><a href='../core/trans-new.php'>Journal Transactions</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td align='center'><a href='../supp-view.php'>View Suppliers</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t</table>";
return $confirm;
}
function write($_POST)
{
# Get vars
extract($_POST);
if (isset($back)) {
return slctacc($_POST);
}
# validate input
require_lib("validate");
$v = new validate();
$date = $date_year . "-" . $date_month . "-" . $date_day;
if (!checkdate($date_month, $date_day, $date_year)) {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
foreach ($accid as $key => $value) {
$dtamount[$key] += 0;
$ctamount[$key] += 0;
if ($dtamount[$key] > 0 || $ctamount[$key] > 0) {
$v->isOk($accid[$key], "num", 1, 50, "Invalid Account to be Debited.[{$key}]");
$v->isOk($dtamount[$key], "float", 1, 20, "Invalid Debit Amount.[{$key}]");
$v->isOk($ctamount[$key], "float", 1, 20, "Invalid Credit Amount.[{$key}]");
$v->isOk($descript[$key], "string", 0, 255, "Invalid Details.[{$key}]");
}
}
# display errors, if any
if ($v->isError()) {
$write = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$write .= "<li class='err'>" . $e["msg"] . "</li>";
}
$write .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $write;
}
foreach ($accid as $key => $value) {
# begin sql transaction
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
if ($dtamount[$key] > 0) {
writetrans($accid[$key], $account, $date, $refnum, $dtamount[$key], $descript[$key]);
} elseif ($ctamount[$key] > 0) {
writetrans($account, $accid[$key], $date, $refnum, $ctamount[$key], $descript[$key]);
}
pglib_transaction("COMMIT") or errDie("Unable to finish a database transaction.", SELF);
}
// Start layout
$write = "\n\t\t<center>\n\t\t<h3>Journal transactions have been recorded.</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Account</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</tr>";
foreach ($accid as $key => $value) {
if ($dtamount[$key] > 0 || $ctamount[$key] > 0) {
core_connect();
$accRss = get("core", "*", "accounts", "accid", $accid[$key]);
if (pg_numrows($accRss) < 1) {
return "<li> Accounts to be debited does not exist.</li>";
}
$acc_each[$key] = pg_fetch_array($accRss);
$write .= "\n\t\t\t\t<tr bgcolor=" . bgcolorg() . ">\n\t\t\t\t\t<td valign='center'>" . $acc_each[$key]['accname'] . "</td>\n\t\t\t\t\t<td>{$descript[$key]}</td>\n\t\t\t\t\t<td nowrap>" . CUR . " " . sprint($dtamount[$key]) . "</td>\n\t\t\t\t\t<td nowrap>" . CUR . " " . sprint($ctamount[$key]) . "</td>\n\t\t\t\t</tr>";
}
}
$write .= "\n\t\t</table>\n\t\t<br>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\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='datacell'>\n\t\t\t\t<td align='center'><a href='../reporting/index-reports.php'>Financials</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='trans-new.php'>Journal Transaction</td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='batch-view.php'>View batch file</td>\n\t\t\t</tr>\n\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t</table>";
return $write;
}
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.");
if (isset($accnt)) {
if ($accnt == 'slct') {
if (isset($cusnums)) {
foreach ($cusnums as $key => $cusnum) {
$v->isOk($cusnum, "num", 1, 20, "Invalid Customer number.");
}
} else {
return "<li class=err>Please select at least one Debtor.</li>" . slctacc();
}
}
} else {
$v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
}
# 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') {
$cusnums = array();
db_connect();
$sql = "SELECT cusnum FROM customers WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$cusnums[] = $ac['cusnum'];
}
} else {
return "<li calss=err> There are no customers yet in Cubit.";
}
}
# Period name
$prdname = prdname($prd);
$hide = "";
$trans = "";
foreach ($cusnums as $key => $cusnum) {
$cusRs = get("cubit", "cusname, surname, accno, balance", "customers", "cusnum", $cusnum);
$cus = pg_fetch_array($cusRs);
$idRs = get($prd, "min(id)", "custledger", "cusnum", $cusnum);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "custledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$bal['cbalance'] += 0;
$bal['dbalance'] += 0;
} else {
$balRs = get("cubit", "balance", "customers", "cusnum", $cusnum);
$bal = pg_fetch_array($balRs);
$bal['balance'] += 0;
if ($bal['balance'] > 0) {
$bal['dbalance'] = $bal['balance'];
$bal['cbalance'] = 0;
} else {
$bal['cbalance'] = $bal['balance'] * -1;
$bal['dbalance'] = 0;
}
//$bal['dbalance'] += $amount;
}
# Total balance changes
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
$bal['cbalance'] = 0;
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
$bal['dbalance'] = 0;
} else {
$bal['cbalance'] = 0;
$bal['dbalance'] = 0;
}
$bal['credit'] = sprint($bal['cbalance']);
$bal['debit'] = sprint($bal['dbalance']);
$balance = sprint($bal['dbalance'] - $bal['cbalance']);
$hide .= "<input type=hidden name=cusnums[] value='{$cusnum}'>";
$trans .= "<tr><td colspan=8><b>{$cus['accno']} - {$cus['cusname']} {$cus['surname']}</b></td></tr>";
$trans .= "<tr><td colspan=2><br></td><td>Br/Forwd</td><td>Brought Forward</td><td align=right>{$bal['debit']}</td><td align=right>{$bal['credit']}</td><td align=right>{$balance}</td><td> </td></tr>";
# --> Transaction reading comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
if ($t == "s") {
$tranRs = get($prd, "*", "custledger", "cusnum", $cusnum, "ORDER BY id");
} else {
$tranRs = get($prd, "*", "custledger", "cusnum", $cusnum, "ORDER BY edate,id");
}
while ($tran = pg_fetch_array($tranRs)) {
$caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
$cacc = pg_fetch_array($caccRs);
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
if ($t == "s") {
$balance = sprint($tran['dbalance'] - $tran['cbalance']);
} else {
$balance = sprint($dbal['debit'] + $bal['debit'] - ($dbal['credit'] + $bal['credit']));
}
if ($t == "t") {
$tran['sdate'] = $tran['edate'];
}
# Format date
$tran['sdate'] = explode("-", $tran['sdate']);
$tran['sdate'] = $tran['sdate'][2] . "-" . $tran['sdate'][1] . "-" . $tran['sdate'][0];
$trans .= "<tr><td><br></td><td>{$tran['sdate']}</td><td>{$tran['eref']}</td><td>{$tran['descript']}</td><td align=right>" . sprint($tran['debit']) . "</td><td align=right>" . sprint($tran['credit']) . "</td><td align=right>{$balance}</td><td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</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'] = "";
$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>";
}
$sp = " ";
$view = "\n\t<center>\n\t<h3>Debtors Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=75%>\n\t<tr><td>{$sp}</td><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</table>";
include "temp.xls.php";
Stream("Ledger", $view);
}
function import_csv_file($_POST)
{
extract($_POST);
$file = file($_FILES['import_file']['tmp_name']);
if (!is_array($file) or count($file) < 1) {
return slctacc(array(), "<li class='err'>Please Ensure File Format Is Correct.</li><br>");
}
$refnum = getrefnum();
foreach ($file as $line) {
$cleanline = trim($line);
if (strlen($cleanline) < 8) {
continue;
}
$line_arr = explode(",", $cleanline);
db_connect();
$get_cusnum = "SELECT cusnum FROM customers WHERE accno = '{$line_arr['0']}' LIMIT 1";
$run_cusnum = db_exec($get_cusnum) or errDie("Unable to get customer information.");
$cusnum = pg_fetch_result($run_cusnum, 0, 0);
// $refnum = $line_arr[3];
// $cusnum = $line_arr[0];
$darr = explode("/", $line_arr[7]);
$date_day = $darr[1];
$date_month = $darr[0];
$date_year = "20" . $darr[2];
$date = "{$date_year}-{$date_month}-{$date_day}";
$aarr = explode("/", $line_arr[2]);
$acc_topacc = $aarr[0];
$acc_accnum = $aarr[1];
$get_vatcode = "SELECT id FROM vatcodes WHERE code = '{$line_arr['4']}' LIMIT 1";
$run_vatcode = db_exec($get_vatcode) or errDie("Unable to get vat code information.");
$vatcode = pg_fetch_result($run_vatcode, 0, 0);
core_connect();
$get_acc = "SELECT accid FROM accounts WHERE topacc = '{$acc_topacc}' AND accnum = '{$acc_accnum}' LIMIT 1";
$run_acc = db_exec($get_acc) or errDie("Unable to get contra account information.");
$accid = pg_fetch_result($run_acc, 0, 0);
db_connect();
$ins_sql = "\n\t\t\tINSERT INTO cust_trans_batch (\n\t\t\t\tcusnum, proc_date, ref_num, entry_type, contra_account, chrg_vat, vatcode, amount, description\n\t\t\t) VALUES (\n\t\t\t\t'{$cusnum}', '{$date}', '{$refnum}', '{$line_arr['5']}', '{$accid}', '{$line_arr['6']}', '{$vatcode}','{$line_arr['9']}', '{$line_arr['8']}'\n\t\t\t)";
$run_sql = db_exec($ins_sql) or errDie("Unable to record customer transaction.");
$refnum++;
}
return slctacc(array(), "<li class='yay'>{$numtrans} Transaction(s) Has Been Imported.</li><br>");
}
function viewtran($_POST)
{
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
$v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
$v->isOk($year, "string", 1, 10, "Invalid year.");
if ($accnt == 'slct') {
if (isset($stkids)) {
foreach ($stkids as $key => $stkid) {
$v->isOk($stkid, "num", 1, 20, "Invalid Stock code.");
}
} else {
return "<li class='err'>ERROR : Please select at least one Stock Item.</li>" . slctacc();
}
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get the ids
if ($accnt == 'all') {
$stkids = array();
db_connect();
$sql = "SELECT stkid FROM stock WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$stkids[] = $ac['stkid'];
}
} else {
return "<li calss='err'> There are no Stock Items yet in Cubit.</li>";
}
}
$hide = "";
# Period name
$prds = array();
if ($tprd < $fprd) {
for ($i = $fprd; $i <= 12; ++$i) {
$prds[] = $i;
}
for ($i = 1; $i <= $tprd; ++$i) {
$prds[] = $i;
}
} else {
for ($i = $fprd; $i <= $tprd; ++$i) {
$prds[] = $i;
}
}
$trans = "";
foreach ($stkids as $key => $stkid) {
$stkRs = get("cubit", "*", "stock", "stkid", $stkid);
$stk = pg_fetch_array($stkRs);
$trans .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>{$stk['stkcod']} - {$stk['stkdes']}</h3></td>\n\t\t\t</tr>";
$hide .= "<input type='hidden' name='stkids[]' value='{$stkid}'>";
foreach ($prds as $prd) {
# Period name
$prdname = prdname($prd);
# Get balances
$idRs = get($prd, "max(id), min(id)", "stkledger", "yrdb='{$year}' AND stkid", $stkid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "qty, (bqty - qty) as bqty, trantype, (balance - csamt) as balance", "stkledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "balance", "stkledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
/*
if($bal['trantype'] == 'dt'){
$bal['bqty'] = ($bal['bqty'] + $bal['qty']);
}else{
$bal['bqty'] = ($bal['bqty'] - $bal['qty']);
}
*/
} else {
if ($prd != PRD_DB) {
continue;
}
$balRs = get("cubit", "csamt as balance, units as bqty", "stock", "stkid", $stkid);
$bal = pg_fetch_array($balRs);
$cbal['balance'] = 0;
$cbal['bqty'] = 0;
}
$balance = sprint($bal['balance']);
$get_yr = "SELECT yrname FROM core.year WHERE yrdb = '{$year}' LIMIT 1";
$run_yr = db_exec($get_yr) or errDie("Unable to get year information.");
if (pg_numrows($run_yr) > 0) {
$yarr = pg_fetch_array($run_yr);
$tmpyear = substr($yarr['yrname'], 1);
} else {
$tmpyear = date("Y") - 1;
}
$get_last_prdmap = "SELECT period FROM core.prdmap WHERE month = '12'";
$run_last_prdmap = db_exec($get_last_prdmap) or errDie("Unable to get last month period");
if (pg_numrows($run_last_prdmap) > 0) {
$lastperiod = pg_fetch_result($run_last_prdmap, 0, 0);
}
$get_current_prd = "SELECT period FROM core.prdmap WHERE month = '{$prd}' LIMIT 1";
$run_current_prd = db_exec($get_current_prd) or errDie("Unable to get period information.");
if (pg_fetch_array($run_current_prd) > 0) {
$currentperiod = pg_fetch_result($run_current_prd, 0, 0);
if ((int) $lastperiod >= (int) $currentperiod) {
$show_year = $tmpyear - 1;
} else {
$show_year = $tmpyear;
}
}
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='5'><b>({$stk['stkcod']}) {$stk['stkdes']}</b></td>\n\t\t\t\t</tr>";
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$show_year}-{$prd}-01<br></td>\n\t\t\t\t\t<td>Balance Brought Forward</td>\n\t\t\t\t\t<td align='right'>{$bal['bqty']}</td>\n\t\t\t\t\t<td align='right'>{$balance} </td>\n\t\t\t\t\t<td align='right'>{$balance} </td>\n\t\t\t\t</tr>";
# --> transactio reding comes here <--- #
$dbal['balance'] = 0;
$dbal['bqty'] = 0;
$tranRs = nget($prd, "*", "stkledger", "yrdb='{$year}' AND stkid", $stkid . " ORDER BY id ASC");
while ($tran = pg_fetch_array($tranRs)) {
$dbal['balance'] += $tran['csamt'];
$dbal['bqty'] += $tran['qty'];
# sprinting
$tran['csamt'] = sprint($tran['csamt']);
$tran['balance'] = sprint($tran['balance']);
# 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>\n\t\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t\t<td>{$tran['details']}</td>\n\t\t\t\t\t\t<td>{$tran['qty']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['csamt']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['balance']}</td>\n\t\t\t\t\t</tr>";
}
$dbal['balance'] = sprint($dbal['balance']);
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td><br></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['bqty']}</td>\n\t\t\t\t\t<td align='right'>{$dbal['balance']} </td>\n\t\t\t\t\t<td align='right'>{$dbal['balance']} </td>\n\t\t\t\t</tr>";
$trans .= "<tr><td colspan='5'><br></td></tr>";
}
}
$sp = " ";
$view = "\n\t\t<center>\n\t\t<h3>Inventory Ledger</h3>\n\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t<tr>\n\t\t\t\t<th>DATE</th>\n\t\t\t\t<th>DETAILS</th>\n\t\t\t\t<th>QTY</th>\n\t\t\t\t<th>COST AMOUNT</th>\n\t\t\t\t<th>BALANCE</th>\n\t\t\t</tr>\n\t\t\t{$trans}\n\t\t</table>";
include "temp.xls.php";
Stream("Ledger", $view);
}
function viewtran($_POST)
{
# Get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($fprd, "string", 1, 14, "Invalid from period number.");
$v->isOk($tprd, "string", 1, 14, "Invalid to period number.");
$v->isOk($year, "string", 3, 4, "Invalid year.");
if (isset($accnt)) {
if ($accnt == 'slct') {
if (isset($supids)) {
foreach ($supids as $key => $supid) {
$v->isOk($supid, "num", 1, 20, "Invalid Supplier number.");
}
} else {
return "<li class='err'>Please select at least one Creditor.</li>" . slctacc();
}
}
} else {
$v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get the ids
if ($accnt == 'all') {
$supids = array();
db_connect();
$sql = "SELECT supid FROM suppliers WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$supids[] = $ac['supid'];
}
} else {
return "<li calss='err'> There are no suppliers yet in Cubit.</li>";
}
}
$prds = array();
if ($tprd < $fprd) {
for ($i = $fprd; $i <= 12; ++$i) {
$prds[] = $i;
}
for ($i = 1; $i <= $tprd; ++$i) {
$prds[] = $i;
}
} else {
for ($i = $fprd; $i <= $tprd; ++$i) {
$prds[] = $i;
}
}
db_conn('core');
$Sl = "SELECT yrname FROM year WHERE yrdb='{$year}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$yrname = pg_fetch_result($Ri, 0);
$auditdb = "{$yrname}_audit";
# Period name
$hide = "";
global $MONPRD, $PRDMON;
$sp = " ";
$trans = "";
foreach ($supids as $key => $supid) {
$supRs = get("cubit", "supname, supno, balance", "suppliers", "supid", $supid);
$sup = pg_fetch_array($supRs);
$trans .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>{$sup['supname']}</h3></td>\n\t\t\t</tr>";
$hide .= "<input type='hidden' name='supids[]' value='{$supid}'>";
foreach ($prds as $prd) {
$prdname = prdname($prd);
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<th colspan='8'>{$prdname}</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>{$sp}</th>\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>";
$idRs = get($auditdb, "min(id)", "{$prdname}_suppledger", "supid", $supid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($auditdb, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "{$prdname}_suppledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$bal['cbalance'] += 0;
$bal['dbalance'] += 0;
} else {
$sql = array();
for ($i = $MONPRD[$prd] - 1; $i >= 1; --$i) {
$pprdname = getMonthName($PRDMON[$i]);
$sql[] = "SELECT id,cbalance,dbalance FROM {$auditdb}.{$pprdname}_suppledger WHERE supid='{$supid}'";
}
if (count($sql) > 0) {
$sql = "SELECT * FROM (" . implode(" UNION ", $sql) . ") AS sl ORDER BY id DESC LIMIT 1";
$balRs = db_exec($sql);
$bal = pg_fetch_array($balRs);
}
}
if (!isset($bal['dbalance'])) {
$bal['dbalance'] = "";
}
if (!isset($bal['cbalance'])) {
$bal['cbalance'] = "";
}
# Total balance changes
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
$bal['cbalance'] = 0;
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
$bal['dbalance'] = 0;
} else {
$bal['cbalance'] = 0;
$bal['dbalance'] = 0;
}
$bal['credit'] = sprint($bal['cbalance']);
$bal['debit'] = sprint($bal['dbalance']);
$balance = sprint($bal['cbalance'] - $bal['dbalance']);
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='8'><b>{$sup['supno']} - {$sup['supname']} </b></td>\n\t\t\t\t</tr>";
// // $bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, getYearOfFinMon($prd)));
// $bbf_date
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2' align='right'> </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'>" . sprint($bal['debit']) . "</td>\n\t\t\t\t\t<td align='right'>" . sprint($bal['credit']) . "</td>\n\t\t\t\t\t<td align='right'>" . sprint($balance) . "</td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
# --> Transaction reading comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
if ($t == "s") {
$tranRs = get($auditdb, "*", "{$prdname}_suppledger", "supid", $supid, "ORDER BY id");
} else {
$tranRs = get($auditdb, "*", "{$prdname}_suppledger", "supid", $supid, "ORDER BY edate,id");
}
while ($tran = pg_fetch_array($tranRs)) {
$caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
$cacc = pg_fetch_array($caccRs);
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
if ($t == "s") {
$cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
} else {
$cbalance = sprint($dbal['credit'] + $bal['credit'] - ($dbal['debit'] + $bal['debit']));
}
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];
$trans .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td> </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'>" . sprint($tran['debit']) . "</td>\n\t\t\t\t\t\t<td align='right'>" . sprint($tran['credit']) . "</td>\n\t\t\t\t\t\t<td align='right'>" . sprint($cbalance) . "</td>\n\t\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</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'> </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>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8'> </td>\n\t\t\t\t</tr>";
}
}
$view = "\n\t\t<center>\n\t\t<form action='../xls/supp-ledger-xls.php' method='POST'>\n\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t<input type='hidden' name='year' value='{$year}'>\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\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<input type='hidden' name='t' value='{$t}'>\n\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t{$hide}\n\t\t<h3>Creditors Ledger</h3>\n\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t{$trans}\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t</tr>\n\t\t</from>\n\t\t</table>\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t<tr><td><br></td></tr>\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='datacell'>\n\t\t\t\t<td align='center'><a target=_blank href='../core/acc-new2.php'>Add account (New Window)</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports.php'>Financials</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports-debtcred.php'>Debtors & Creditors Reports</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\n\t\t\t</tr>\n\t\t</table>";
return $view;
}
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($year, "string", 1, 10, "Invalid year.");
if (isset($accnt)) {
if ($accnt == 'slct') {
if (isset($supids)) {
foreach ($supids as $key => $supid) {
$v->isOk($supid, "num", 1, 20, "Invalid Supplier number.");
}
} else {
return "<li class=err>Please select at least one Creditor.</li>" . slctacc();
}
}
} else {
$v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
}
# 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') {
$supids = array();
db_connect();
$sql = "SELECT supid FROM suppliers WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$supids[] = $ac['supid'];
}
} else {
return "<li calss=err> There are no suppliers yet in Cubit.";
}
}
# Period name
$prdname = prdname($prd);
db_conn('core');
$Sl = "SELECT * FROM year WHERE yrdb='{$year}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$yd = pg_fetch_array($Ri);
$trans = "";
foreach ($supids as $key => $supid) {
$supRs = get("cubit", "supname, supno, balance", "suppliers", "supid", $supid);
$sup = pg_fetch_array($supRs);
$idRs = get($yd['yrname'] . "_audit", "min(id)", $prdname . "_suppledger", "supid", $supid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($yd['yrname'] . "_audit", "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", $prdname . "_suppledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$bal['cbalance'] += 0;
$bal['dbalance'] += 0;
} else {
$balRs = get("cubit", "balance", "suppliers", "supid", $supid);
$bal = pg_fetch_array($balRs);
$bal['balance'] += 0;
if ($bal['balance'] < 0) {
$bal['dbalance'] = $bal['balance'] * -1;
$bal['cbalance'] = 0;
} else {
$bal['cbalance'] = $bal['balance'];
$bal['dbalance'] = 0;
}
//$bal['dbalance'] += $amount;
}
# Total balance changes
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
$bal['cbalance'] = 0;
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
$bal['dbalance'] = 0;
} else {
$bal['cbalance'] = 0;
$bal['dbalance'] = 0;
}
$bal['credit'] = $bal['cbalance'];
$bal['debit'] = $bal['dbalance'];
$balance = sprint($bal['cbalance'] - $bal['dbalance']);
$trans .= "<tr class='bg-even'><td colspan=8><b>{$sup['supno']} - {$sup['supname']} </b></td></tr>";
$trans .= "<tr class='bg-even'><td colspan=2><br></td><td>Br/Forwd</td><td>Brought Forward</td><td align=right>{$bal['debit']}</td><td align=right>{$bal['credit']}</td><td align=right>{$balance}</td><td> </td></tr>";
# --> Transaction reading comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
$tranRs = get($yd['yrname'] . "_audit", "*", $prdname . "_suppledger", "supid", $supid);
while ($tran = pg_fetch_array($tranRs)) {
$caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
$cacc = pg_fetch_array($caccRs);
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
$cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$trans .= "<tr class='bg-odd'><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}</td><td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</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'] = "";
$dbal['debit'] = "0.00";
}
$trans .= "<tr class='bg-even'><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>";
}
$sp = " ";
$view = "\n\t<center>\n\t<h3>Creditors Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=75%>\n\t<tr><td>{$sp}</td><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<table>\n\t<p>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=25%>\n\t\t<tr><td><br></td></tr>\n\t\t<tr><th>Quick Links</th></tr>\n\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
return $view;
}
function confirm($_POST)
{
# Get vars
extract($_POST);
# Redirect if must chrgvat
if ($chrgvat == 'yes' && !isset($vataccid)) {
return slctVatAcc($_POST);
}
if (isset($vatcode)) {
$vatcode += 0;
} else {
$vatcode = 0;
}
if (isb($dtaccid)) {
return "<li class='err'>You selected a main account.</li>" . slctacc($_POST);
}
if (isb($ctaccid)) {
return "<li class='err'>You selected a main account.</li>" . slctacc($_POST);
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($ctaccid, "num", 1, 50, "Invalid Account to be Credited.");
$v->isOk($dtaccid, "num", 1, 50, "Invalid Account to be Debited.");
$v->isOk($refnum, "num", 1, 10, "Invalid Reference number.");
$v->isOk($amount, "float", 1, 20, "Invalid Amount.");
$v->isOk($details, "string", 0, 255, "Invalid Details.");
$v->isOk($author, "string", 1, 30, "Invalid Authorising person name.");
$datea = explode("-", $date);
if (count($datea) == 3) {
if (!checkdate($datea[1], $datea[0], $datea[2])) {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
} else {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
if ($chrgvat == 'yes') {
$v->isOk($vataccid, "num", 1, 50, "Invalid VAT Account number.");
$v->isOk($vatdedacc, "num", 1, 50, "Invalid VAT Deductable Account number.");
$v->isOk($vatinc, "string", 1, 3, "Invalid vat inclusive selection.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
return details($_POST, $confirm);
}
if ($amount <= 0) {
return details($_POST, "<li class='err'>Invalid Amount To Process.</li>");
}
$dtaccRs = get("core", "*", "accounts", "accid", $dtaccid);
$dtacc = pg_fetch_array($dtaccRs);
$ctaccRs = get("core", "*", "accounts", "accid", $ctaccid);
$ctacc = pg_fetch_array($ctaccRs);
# show vat account if vat is charged
if ($chrgvat == 'yes') {
$vataccRs = get("core", "*", "accounts", "accid", $vataccid);
$vatacc = pg_fetch_array($vataccRs);
$vatin = ucwords($vatinc);
$vataccnum = "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>VAT Account</td>\n\t\t\t\t<td><input type='hidden' name='vataccid' value='{$vataccid}'><input type='hidden' name='vatdedacc' value='{$vatdedacc}'>{$vatacc['topacc']}/{$vatacc['accnum']} - {$vatacc['accname']}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>VAT Inclusive</td>\n\t\t\t\t<td><input type='hidden' name='vatinc' value='{$vatinc}'>{$vatin}</td>\n\t\t\t</tr>";
} else {
$vataccnum = "";
}
$vat = ucwords($chrgvat);
if ($vatcode > 0) {
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl) or errDie("unable to get data.");
$va = pg_fetch_array($Ri);
$vd = "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>VAT Code</td>\n\t\t\t\t<td>{$va['code']}</td>\n\t\t\t</tr>";
} else {
$vd = "";
}
$confirm = "\n\t\t<h3>Record Recurring Transaction</h3>\n\t\t<h4>Confirm entry</h4>\n\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t<input type='hidden' name='key' value='write'>\n\t\t\t<input type='hidden' name='vatcode' value='{$vatcode}'>\n\t\t\t<input type='hidden' name='dtaccid' value='{$dtaccid}'>\n\t\t\t<input type='hidden' name='ctaccid' value='{$ctaccid}'>\n\t\t\t<input type='hidden' name='dtaccname' value='{$dtacc['accname']}'>\n\t\t\t<input type='hidden' name='ctaccname' value='{$ctacc['accname']}'>\n\t\t\t<input type='hidden' name='date' value='{$date}'>\n\t\t\t<input type='hidden' name='date_day' value='{$date_day}'>\n\t\t\t<input type='hidden' name='date_month' value='{$date_month}'>\n\t\t\t<input type='hidden' name='date_year' value='{$date_year}'>\n\t\t\t<input type='hidden' name='refnum' value='{$refnum}'>\n\t\t\t<input type='hidden' name='amount' value='{$amount}'>\n\t\t\t<input type='hidden' name='chrgvat' value='{$chrgvat}'>\n\t\t\t<input type='hidden' name='details' value='{$details}'>\n\t\t\t<input type='hidden' name='author' value='{$author}'>\n\t\t<table " . TMPL_tblDflts . " width='500'>\n\t\t\t<tr>\n\t\t\t\t<td width='50%'><h3>Debit</h3></td>\n\t\t\t\t<td width='50%'><h3>Credit</h3></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$dtacc['topacc']}/{$dtacc['accnum']} - {$dtacc['accname']}</td>\n\t\t\t\t<td>{$ctacc['topacc']}/{$ctacc['accnum']} - {$ctacc['accname']}</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Date</td>\n\t\t\t\t<td>{$date}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Reference number</td>\n\t\t\t\t<td>{$refnum}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Amount</td>\n\t\t\t\t<td>" . CUR . " {$amount}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Charge VAT </td>\n\t\t\t\t<td>{$vat}</td>\n\t\t\t</tr>\n\t\t\t{$vataccnum}\n\t\t\t{$vd}\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Details</td>\n\t\t\t\t<td>{$details}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Authorising Person</td>\n\t\t\t\t<td>{$author}</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td align='right'><input type='button' value='« Back' onClick='javascript:history.back()'></td>\n\t\t\t\t<td align='right'><input type='submit' value='Confirm Transaction »'></td>\n\t\t\t</tr>\n\t\t</form>\n\t\t</table>\n\t\t<p>\n\t\t<table border=0 cellpadding='2' cellspacing='1' width=15%>\n\t\t\t<tr>\n\t\t\t\t<th>Quick Links</th>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='../reporting/index-reports.php'>Financials</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='rectrans-view.php'>View Recurring Transactions</td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='trans-new.php'>Journal Transactions</td>\n\t\t\t</tr>\n\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t</table>";
return $confirm;
}
function viewtran($_POST)
{
extract($_POST);
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
$v->isOk($accnt, "string", 1, 10, "Invalid Accounts Selection.");
$v->isOk($acc_first, "string", 1, 20, "Invalid accounts display 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"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
// Was the 'All Accounts' option selected?
if ($accnt == 'all') {
$accids = array();
// Retrieve the main accounts
db_conn("core");
$sql = "SELECT * FROM accounts WHERE div='" . USER_DIV . "' AND accnum='000' ORDER BY topacc ASC";
$macc_rslt = db_exec($sql) or errDie("Unable to retrieve main accounts from Cubit.");
while ($macc_data = pg_fetch_array($macc_rslt)) {
// Retrieve sub accounts from Cubit
$sql = "SELECT * FROM accounts WHERE div='" . USER_DIV . "' AND topacc='{$macc_data['topacc']}' AND accnum!='000' ORDER BY topacc ASC";
$sacc_rslt = db_exec($sql) or errDie("Unable to retrieve sub accounts from Cubit.");
// List the main accounts without any sub accounts
if (!pg_num_rows($sacc_rslt)) {
$accids[] = $macc_data["accid"];
// List the sub accounts
} else {
while ($sacc_data = pg_fetch_array($sacc_rslt)) {
$accids[] = $sacc_data["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 . "' AND month='{$prd}'";
$rslt = db_exec($sql) or errDie("Error fetching active account list.");
while ($macc_data = pg_fetch_array($rslt)) {
$accids[] = $macc_data["accid"];
}
}
}
# Period name
$prdname = prdname($prd);
$trans = "";
$hide = "";
$i = 0;
$curr_topacc = "";
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 {
db_conn("core");
$balSql = "SELECT credit as cbalance, debit as dbalance FROM trial_bal WHERE accid='{$acc['accid']}' AND period='{$prd}'";
$balRs = db_exec($balSql) or errDie("Error reading trial balance.");
$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']);
if ($acc_first == "accnum") {
$account_name = "{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}";
} else {
$account_name = "{$acc['accname']} - {$acc['topacc']}/{$acc['accnum']}";
}
$hide .= "<input type='hidden' name='accids[]' value='{$acc['accid']}'>";
$heading = "";
if ($acc["accnum"] != "000") {
if (!$i && $acc["topacc"] != $curr_topacc) {
db_conn("core");
$sql = "SELECT * FROM accounts WHERE div='" . USER_DIV . "' AND topacc='{$acc['topacc']}' AND accnum='000'";
$hacc_rslt = db_exec($sql) or errDie("Unable to retrieve main account from Cubit.");
$hacc_data = pg_fetch_array($hacc_rslt);
if ($acc_first == "accnum") {
$heading_name = "{$hacc_data['topacc']}/{$hacc_data['accnum']} - {$hacc_data['accname']}";
} else {
$heading_name = "{$hacc_data['accname']} - {$hacc_data['topacc']}/{$hacc_data['accnum']}";
}
$heading = "<tr><th colspan='8' align='left'>{$heading_name}</th></tr>";
$curr_topacc = $acc["topacc"];
}
} elseif ($acc["topacc"] == $curr_topacc) {
++$i;
}
$trans .= "{$heading}\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='8'><b>{$account_name}</b></td>\n\t\t</tr>";
// make the date of the last day of the previous prd
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, getYearOfFinMon($prd)));
$trans .= "\n\t\t<tr class='" . bg_class() . "'>\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']);
if ($tran["debit"] != 0) {
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2' nowrap>{$tran['edate']}</td>\n\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t<td align='right' nowrap>{$tran['debit']}</td>\n\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t<td align='right' nowrap>{$cbalance} {$cfl}</td>\n\t\t\t\t\t<td align='right'>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t</tr>";
} elseif ($tran["credit"] != 0) {
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2' nowrap>{$tran['edate']}</td>\n\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t<td align='right' nowrap>{$tran['credit']}</td>\n\t\t\t\t\t<td align='right' nowrap>{$cbalance} {$cfl}</td>\n\t\t\t\t\t<td align='right'>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\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<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='2'><br></td>\n\t\t\t<td>A/C Total</td>\n\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t<td align='right' nowrap>{$dbal['debit']}</td>\n\t\t\t<td align='right' nowrap>{$dbal['credit']}</td>\n\t\t\t<td align='right' nowrap></td>\n\t\t\t<td> </td>\n\t\t</tr>";
$trans .= "<tr><td colspan='8'><br></td></tr>";
}
$sp = " ";
$view = "\n\t\t\t\t<center>\n\t\t\t\t<h3>General Ledger</h3>\n\t\t\t\t<form action='../xls/ledger-xls.php' method='POST'>\n\t\t\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t\t\t<input type='hidden' name='t' value='{$t}'>\n\t\t\t\t\t<input type='hidden' name=accnt value='{$accnt}'>\n\t\t\t\t\t{$hide}\n\t\t\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th colspan='2'>Date</th>\n\t\t\t\t\t\t<th>Reference</th>\n\t\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t\t<th width='10%'>Debit</th>\n\t\t\t\t\t\t<th width='10%'>Credit</th>\n\t\t\t\t\t\t<th width='10%'>Balance</th>\n\t\t\t\t\t\t<th>Contra Acc</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t{$trans}\n\t\t\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t\t</tr>\n\t\t\t\t<table>\n\t\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;
}
function viewtran($_POST)
{
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
if (isset($accnt)) {
if ($accnt == 'slct') {
if (isset($supids)) {
foreach ($supids as $key => $supid) {
$v->isOk($supid, "num", 1, 20, "Invalid Supplier number.");
}
} else {
return "<li class='err'>Please select at least one Creditor.</li>" . slctacc();
}
}
} else {
$v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get the ids
if ($accnt == 'all') {
$supids = array();
db_connect();
$sql = "SELECT supid FROM suppliers WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$supids[] = $ac['supid'];
}
} else {
return "<li calss='err'> There are no suppliers yet in Cubit.</li>";
}
}
db_conn('core');
$Sl = "SELECT yrname FROM year WHERE yrdb='{$year}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$yrname = pg_fetch_result($Ri, 0);
$auditdb = "{$yrname}_audit";
$prds = array();
if ($tprd < $fprd) {
for ($i = $fprd; $i <= 12; ++$i) {
$prds[] = $i;
}
for ($i = 1; $i <= $tprd; ++$i) {
$prds[] = $i;
}
} else {
for ($i = $fprd; $i <= $tprd; ++$i) {
$prds[] = $i;
}
}
# Period name
$prdname = prdname($prd);
$trans = "";
foreach ($supids as $key => $supid) {
$supRs = get("cubit", "supname, supno, balance", "suppliers", "supid", $supid);
$sup = pg_fetch_array($supRs);
$trans .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>{$sup['supname']}</h3></td>\n\t\t\t</tr>";
foreach ($prds as $prd) {
$prdname = prdname($prd);
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<th colspan='8'>{$prdname}</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>{$sp}</th>\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>";
// if($id['min'] <> 0){
// $balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "suppledger", "id", $id['min']);
// $bal = pg_fetch_array($balRs);
// $bal['cbalance'] += 0;
// $bal['dbalance'] += 0;
// }else{
// $balRs = get("cubit", "balance", "suppliers", "supid", $supid);
// $bal = pg_fetch_array($balRs);
// $bal['balance']+=0;
//
// if($bal['balance']<0) {
// $bal['dbalance'] = $bal['balance'];
// $bal['cbalance'] = 0;
// } else {
// $bal['cbalance'] = $bal['balance'];
// $bal['dbalance'] = 0;
// }
// //$bal['dbalance'] += $amount;
// }
// $idRs = get($prd, "min(id)", "suppledger", "supid", $supid);
// $id = pg_fetch_array($idRs);
$idRs = get($auditdb, "min(id)", "{$prdname}_suppledger", "supid", $supid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($auditdb, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "{$prdname}_suppledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$bal['cbalance'] += 0;
$bal['dbalance'] += 0;
} else {
$sql = array();
for ($i = $MONPRD[$prd] - 1; $i >= 1; --$i) {
$pprdname = getMonthName($PRDMON[$i]);
$sql[] = "SELECT id,cbalance,dbalance FROM {$auditdb}.{$pprdname}_suppledger WHERE supid='{$supid}'";
}
if (count($sql) > 0) {
$sql = "SELECT * FROM (" . implode(" UNION ", $sql) . ") AS sl ORDER BY id DESC LIMIT 1";
$balRs = db_exec($sql);
$bal = pg_fetch_array($balRs);
}
}
if (!isset($bal['dbalance'])) {
$bal['dbalance'] = "";
}
if (!isset($bal['cbalance'])) {
$bal['cbalance'] = "";
}
# Total balance changes
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
$bal['cbalance'] = 0;
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
$bal['dbalance'] = 0;
} else {
$bal['cbalance'] = 0;
$bal['dbalance'] = 0;
}
$bal['credit'] = sprint($bal['cbalance']);
$bal['debit'] = sprint($bal['dbalance']);
$balance = sprint($bal['cbalance'] - $bal['dbalance']);
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8'><b>{$sup['supno']} - {$sup['supname']} </b></td>\n\t\t\t\t</tr>";
$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>Br/Forwd</td>\n\t\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t\t<td align='right'>{$bal['debit']}</td>\n\t\t\t\t\t<td align='right'>{$bal['credit']}</td>\n\t\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
# --> Transaction reading comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
// if($t == "s") {
// $tranRs = get($prd, "*", "suppledger", "supid", $supid,"ORDER BY id");
// } else {
// $tranRs = get($prd, "*", "suppledger", "supid", $supid,"ORDER BY edate,id");
// }
if ($t == "s") {
$tranRs = get($auditdb, "*", "{$prdname}_suppledger", "supid", $supid, "ORDER BY id");
} else {
$tranRs = get($auditdb, "*", "{$prdname}_suppledger", "supid", $supid, "ORDER BY edate,id");
}
while ($tran = pg_fetch_array($tranRs)) {
$caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
$cacc = pg_fetch_array($caccRs);
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
if ($t == "s") {
$cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
} else {
$cbalance = sprint($dbal['credit'] + $bal['credit'] - ($dbal['debit'] + $bal['debit']));
}
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];
$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}</td>\n\t\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</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 = " ";
$view = "\n\t\t<center>\n\t\t<h3>Creditors Ledger</h3>\n\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t{$trans}\n\t\t</table>";
include "temp.xls.php";
Stream("Ledger", $view);
}
function details($_POST)
{
# Get vars
extract($_POST);
#redirect if must chrgvat
if ($chrgvat == 'yes' && !isset($vataccid)) {
return slctacc($_POST);
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($lname, "string", 1, 255, "Invalid Ledger Name.");
$v->isOk($dtaccid, "num", 1, 50, "Invalid Account to be Debited.");
$v->isOk($ctaccid, "num", 1, 50, "Invalid Account to be Credited.");
$v->isOk($chrgvat, "string", 1, 4, "Invalid charge vat option.");
$v->isOk($numtran, "num", 1, 20, "Invalid Number on entries.");
$v->isOk($dateopt, "string", 1, 10, "Invalid date entry option.");
$v->isOk($desopt, "string", 1, 5, "Invalid Description option.");
if ($desopt == 'once') {
$v->isOk($descript, "string", 1, 255, "Invalid Description.");
} else {
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
}
$v->isOk($refopt, "string", 1, 5, "Invalid Reference number option.");
if ($refopt == 'once') {
$v->isOk($refnum, "string", 1, 255, "Invalid Reference number.");
} else {
$v->isOk($refnum, "string", 0, 255, "Invalid Reference number.");
}
if ($chrgvat == 'yes') {
$v->isOk($vataccid, "num", 1, 50, "Invalid Vat Account number.");
$v->isOk($vatdedacc, "num", 1, 50, "Invalid Vat Deductable Account number.");
$v->isOk($vatinc, "string", 1, 3, "Invalid vat inclusive selection.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"] . "</li>";
}
return error($_POST, $confirm);
# $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
# return $confirm;
}
# Account numbers
$dtaccRs = get("core", "*", "accounts", "accid", $dtaccid);
$dtacc = pg_fetch_array($dtaccRs);
$ctaccRs = get("core", "*", "accounts", "accid", $ctaccid);
$ctacc = pg_fetch_array($ctaccRs);
if ($chrgvat == 'yes') {
$vataccRs = get("core", "*", "accounts", "accid", $vataccid);
$vatacc = pg_fetch_array($vataccRs);
$vatin = ucwords($vatinc);
$vataccnum = "\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Vat Account</td>\r\n\t\t\t\t<td><input type='hidden' name='vataccid' value='{$vataccid}'><input type='hidden' name='vatdedacc' value='{$vatdedacc}'>{$vatacc['topacc']}/{$vatacc['accnum']} - {$vatacc['accname']}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Vat Inclusive</td>\r\n\t\t\t\t<td><input type='hidden' name='vatinc' value='{$vatinc}'>{$vatin}</td>\r\n\t\t\t</tr>";
} else {
$vataccnum = "";
}
/* Toggle Options */
# Charge Vat Option
$vat = ucwords($chrgvat);
# Date Option
if ($dateopt == 'system') {
$date = 'System Date';
} elseif ($dateopt == 'user') {
$date = 'User Input Date';
}
# Description and Refnum Option
$options = array("num" => "Auto Number", "emp" => "Empty Input Box", "once" => "Once Only Setting", "edit" => "Default Editable Input");
$descriptopt = $options[$desopt];
$refnumopt = $options[$refopt];
# put auto number if its auto number
if ($refopt == 'num') {
$refnums = $options[$refopt];
} else {
$refnums = $refnum;
}
/* End Toggle Options */
# uppercase first letter of name
$lname = ucfirst($lname);
// Details
$details = "\r\n\t\t<center>\r\n\t\t<h3> New High Speed Input Ledger </h3>\r\n\t\t<form action='" . SELF . "' method='POST' name='form'>\r\n\t\t\t<input type='hidden' name='key' value='write'>\r\n\t\t\t<input type='hidden' name='lname' value='{$lname}'>\r\n\t\t\t<input type='hidden' name='dtaccid' value='{$dtaccid}'>\r\n\t\t\t<input type='hidden' name='ctaccid' value='{$ctaccid}'>\r\n\t\t\t<input type='hidden' name='chrgvat' value='{$chrgvat}'>\r\n\t\t\t<input type='hidden' name='numtran' value='{$numtran}'>\r\n\t\t\t<input type='hidden' name='dateopt' value='{$dateopt}'>\r\n\t\t\t<input type='hidden' name='descript' value='{$descript}'>\r\n\t\t\t<input type='hidden' name='desopt' value='{$desopt}'>\r\n\t\t\t<input type='hidden' name='refnum' value='{$refnum}'>\r\n\t\t\t<input type='hidden' name='refopt' value='{$refopt}'>\r\n\t\t<table " . TMPL_tblDflts . " align='center'>\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Option</th>\r\n\t\t\t\t<th>Value</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Ledger Name</td>\r\n\t\t\t\t<td>{$lname}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<th><h4>Debit</h4></th>\r\n\t\t\t\t<th><h4>Credit</h4></th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td align='center'>{$dtacc['topacc']}/{$dtacc['accnum']} - {$dtacc['accname']}</td>\r\n\t\t\t\t<td align='center'>{$ctacc['topacc']}/{$ctacc['accnum']} - {$ctacc['accname']}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr><td><br></td></tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<th colspan='3'>Options</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Number of Entries</td>\r\n\t\t\t\t<td>{$numtran}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Date Entry</td>\r\n\t\t\t\t<td>{$date}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Charge Vat </td>\r\n\t\t\t\t<td>{$vat}</td>\r\n\t\t\t</tr>\r\n\t\t\t{$vataccnum}\r\n\t\t\t<tr>\r\n\t\t\t\t<th colspan='3'>Description</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Description</td>\r\n\t\t\t\t<td>{$descript}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Option</td>\r\n\t\t\t\t<td>{$descriptopt}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<th colspan='3'>Reference Number</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Reference Number</td>\r\n\t\t\t\t<td>{$refnums}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Option</td>\r\n\t\t\t\t<td>{$refnumopt}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr><td><br></td></tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<td align='right'><input type='button' value='« Back' onClick='javascript:history.back()'></td>\r\n\t\t\t\t<td align='right'><input type='submit' value='Continue »'></td>\r\n\t\t\t</tr>\r\n\t\t</table>\r\n\t\t</form>\r\n\t\t<p>\r\n\t\t<table border=0 cellpadding='2' cellspacing='1' width=15%>\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Quick Links</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td align='center'><a href='ledger-new.php'>New High Speed Input Ledger</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td align='center'><a href='ledger-view.php'>View High Speed Input Ledgers</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\r\n\t\t\t</tr>\r\n\t\t</table>";
return $details;
}
function viewtran($_POST, $pure = false)
{
# Get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($fprd, "string", 1, 14, "Invalid from period number.");
$v->isOk($tprd, "string", 1, 14, "Invalid to period number.");
if (isset($accnt)) {
if ($accnt == 'slct') {
if (isset($cusnums)) {
foreach ($cusnums as $key => $cusnum) {
$v->isOk($cusnum, "num", 1, 20, "Invalid Customer number.");
}
} else {
return "<li class='err'>Please select at least one Debtor.</li>" . slctacc();
}
}
} else {
$v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get the ids
if ($accnt == 'all') {
$cusnums = array();
db_connect();
$sql = "SELECT cusnum FROM customers WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$cusnums[] = $ac['cusnum'];
}
} else {
return "<li calss='err'> There are no customers yet in Cubit.</li>";
}
}
$prds = array();
if ($tprd < $fprd) {
for ($i = $fprd; $i <= 12; ++$i) {
$prds[] = $i;
}
for ($i = 1; $i <= $tprd; ++$i) {
$prds[] = $i;
}
} else {
for ($i = $fprd; $i <= $tprd; ++$i) {
$prds[] = $i;
}
}
# Period name
$hide = "";
$sp = " ";
$trans = "";
foreach ($cusnums as $key => $cusnum) {
$cusRs = get("cubit", "cusname, surname, accno, balance", "customers", "cusnum", $cusnum);
$cus = pg_fetch_array($cusRs);
$trans .= "\n\t\t<tr>\n\t\t\t<td colspan='8' align='center'><h3>{$cus['surname']}</h3></td>\n\t\t</tr>";
$hide .= "<input type='hidden' name='cusnums[]' value='{$cusnum}'>";
foreach ($prds as $prd) {
$prdname = prdname($prd);
$trans .= "\n\t\t\t<tr>\n\t\t\t\t<th colspan='8'>{$prdname}</th>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<th>{$sp}</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>";
$idRs = get($prd, "min(id)", "custledger", "cusnum", $cusnum);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "custledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$bal['cbalance'] += 0;
$bal['dbalance'] += 0;
} else {
$balRs = get("cubit", "balance", "customers", "cusnum", $cusnum);
$bal = pg_fetch_array($balRs);
$bal['balance'] += 0;
if ($bal['balance'] > 0) {
$bal['dbalance'] = $bal['balance'];
$bal['cbalance'] = 0;
} else {
$bal['cbalance'] = $bal['balance'] * -1;
$bal['dbalance'] = 0;
}
//$bal['dbalance'] += $amount;
}
# Total balance changes
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
$bal['cbalance'] = 0;
} else {
if ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
$bal['dbalance'] = 0;
} else {
$bal['cbalance'] = 0;
$bal['dbalance'] = 0;
}
}
$bal['credit'] = sprint($bal['cbalance']);
$bal['debit'] = sprint($bal['dbalance']);
$balance = sprint($bal['dbalance'] - $bal['cbalance']);
$trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='8'><b>{$cus['accno']} - {$cus['cusname']} {$cus['surname']}</b></td>\n\t\t\t\t\t\t\t</tr>";
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, getYearOfFinMon($prd)));
$trans .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t\t\t\t<td align='right'>{$bal['debit']}</td>\n\t\t\t\t\t\t\t<td align='right'>{$bal['credit']}</td>\n\t\t\t\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t</tr>";
# --> Transaction reading comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
if ($t == "s") {
$tranRs = get($prd, "*", "custledger", "cusnum", $cusnum, "ORDER BY id");
} else {
$tranRs = get($prd, "*", "custledger", "cusnum", $cusnum, "ORDER BY edate,id");
}
while ($tran = pg_fetch_array($tranRs)) {
if ($tran["descript"] == "BBF" && $tran["debit"] == "0" && $tran["credit"] == "0") {
continue;
}
$caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
$cacc = pg_fetch_array($caccRs);
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
if ($t == "s") {
$balance = sprint($tran['dbalance'] - $tran['cbalance']);
} else {
$balance = sprint($dbal['debit'] + $bal['debit'] - ($dbal['credit'] + $bal['credit']));
}
if ($t == "t") {
$tran['sdate'] = $tran['edate'];
}
# Format date
$tran['sdate'] = explode("-", $tran['sdate']);
$tran['sdate'] = $tran['sdate'][2] . "-" . $tran['sdate'][1] . "-" . $tran['sdate'][0];
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td> </td>\n\t\t\t\t\t<td>{$tran['sdate']}</td>\n\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t<td align='right'>" . sprint($tran['debit']) . "</td>\n\t\t\t\t\t<td align='right'>" . sprint($tran['credit']) . "</td>\n\t\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\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<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='2'> </td>\n\t\t\t\t<td>A/C Total</td>\n\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t<td align='right'> </td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='8'> </td>\n\t\t\t</tr>";
}
}
$view = "";
if (!$pure) {
$view .= "\n\t\t<center>\n\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t<input type='hidden' name='key' value='excel'>\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t<input type='hidden' name='t' value='{$t}'>\n\t\t\t{$hide}\n\t\t\t<h3>Debtors Ledger</h3>";
}
$view .= "\n\t<table " . TMPL_tblDflts . " width='75%'>\n\t{$trans}";
if (!$pure) {
$view .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</from>";
}
$view .= "\n\t</table>";
if (!$pure) {
$view .= "\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t" . TBL_BR . "\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='datacell'>\n\t\t\t\t<td align='center'><a target=_blank href='../core/acc-new2.php'>Add account (New Window)</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports.php'>Financials</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports-debtcred.php'>Debtors & Creditors Reports</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\n\t\t\t</tr>\n\t\t</table>";
}
return $view;
}
function details($_POST, $err = "")
{
# Get vars
extract($_POST);
if (isset($back) and isStock($accid)) {
return get_stock_items($_POST);
} elseif (isset($back)) {
return slctacc($_POST);
}
$accid += 0;
$amount = $amount + 0;
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($refnum, "num", 1, 10, "Invalid Reference number.");
$v->isOk($ct_day, "num", 1, 2, "Invalid to Date ct_day.");
$v->isOk($ct_month, "num", 1, 2, "Invalid to Date ct_monthth.");
$v->isOk($ct_year, "num", 1, 4, "Invalid to Date Year.");
$date = $ct_day . "-" . $ct_month . "-" . $ct_year;
if (!checkdate($ct_month, $ct_day, $ct_year)) {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
$v->isOk($accid, "num", 1, 50, "Invalid Contra Account.");
$v->isOk($cusnum, "num", 1, 50, "Invalid Customer number.");
$v->isOk($vatinc, "string", 1, 6, "Invalid Transaction VAT Option.");
if (sprint($amount) <= 0) {
$v->addError($amount, "Invalid Or Too Small Amount Entered.");
}
if ($accid == "0") {
$v->addError($accid, "No Allowed Accounts Found. <a href='settings/credit-note-accounts.php'>Please Add One First</a>");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
return slctacc($_POST, $confirm . "<br>");
}
# CHECK IF THIS DATE IS IN THE BLOCKED RANGE
$blocked_date_from = getCSetting("BLOCKED_FROM");
$blocked_date_to = getCSetting("BLOCKED_TO");
if (strtotime($date) >= strtotime($blocked_date_from) and strtotime($date) <= strtotime($blocked_date_to) and !user_is_admin(USER_ID)) {
return slctacc($_POST, "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li><br>");
}
#if stock returned is selected ... override the setting
if (isset($gotstock) and strlen($gotstock) > 0) {
#get a stock id
db_conn('exten');
$get_stkid = "SELECT stkacc FROM warehouses ORDER BY whid ASC LIMIT 1";
$run_stkid = db_exec($get_stkid) or errDie("Unable to get inventory account information.");
if (pg_numrows($run_stkid) < 1) {
return "<li class='err'>No Inventory Account Found.</li>";
} else {
$accid = pg_fetch_result($run_stkid, 0, 0);
}
}
# get contra account details
$accRs = get("core", "*", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
#### handle the stock we selected
if ((isStock($accid) or isset($gotstock)) and !isset($stockcontinue)) {
#for whatever reason ... we need to get stock ...
return get_stock_items($_POST);
}
db_connect();
# Select customer
$sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($custRslt) < 1) {
return "<li class='err'>Invalid customer ID, or customer has been blocked.</li>";
} else {
$cust = pg_fetch_array($custRslt);
}
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cust['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
return "<i class='err'>Customer Department Not Found</i>";
} else {
$dept = pg_fetch_array($deptRslt);
}
#get a stock total
$stock_total = 0;
if (isset($stock_items) and is_array($stock_items)) {
foreach ($stock_items as $each => $own) {
$stock_total = $stock_total + $stock_cost[$each] * $own;
}
}
$stkamount = 0;
$send_stock = "";
$show_stock = "";
if (isset($stock_items) and is_array($stock_items)) {
$unit_total = array_sum($stock_items);
if ($unit_total == 0) {
$stock_unit_avg_cost = 0;
} else {
$stock_unit_avg_cost = sprint(($amount - $stock_total) / $unit_total);
}
foreach ($stock_items as $each => $own) {
if ($own > 0) {
if (!isset($own) or strlen($own) < 1) {
$own = 1;
}
if (!isset($stock_cost[$each]) or strlen($stock_cost[$each]) < 1) {
$stock_cost[$each] = 0;
}
$send_stock .= "<input type='hidden' name='stock_items[{$each}]' value='{$own}'>\n";
$send_stock .= "<input type='hidden' name='stock_cost[{$each}]' value='{$stock_cost[$each]}'>\n";
db_connect();
$get_stk = "SELECT stkdes,whid FROM stock WHERE stkid = '{$each}' LIMIT 1";
$run_stk = db_exec($get_stk) or errDie("Unable to get stock information.");
if (pg_numrows($run_stk) < 1) {
$stock_name = "Unknown";
} else {
$stock_name = pg_fetch_result($run_stk, 0, 0);
$whid = pg_fetch_result($run_stk, 0, 1);
db_conn('exten');
$get_cos = "SELECT cosacc FROM warehouses WHERE whid = '{$whid}' LIMIT 1";
$run_cos = db_exec($get_cos) or errDie("Unable to get cost of sale information. (1)");
if (pg_numrows($run_cos) < 1) {
$show_cos = "";
} else {
$cos_id = pg_fetch_result($run_cos, 0, 0);
db_conn('core');
$get_acc = "SELECT topacc,accnum,accname FROM accounts WHERE accid = '{$cos_id}' LIMIT 1";
$run_acc = db_exec($get_acc) or errDie("Unable to get cost of sale information. (2)");
if (pg_numrows($run_acc) < 1) {
$show_cos = "";
} else {
$aarr = pg_fetch_array($run_acc);
$show_cos = "{$aarr['topacc']}/{$aarr['accnum']} - {$aarr['accname']}";
}
}
}
if (!isset($stock_prof[$each])) {
$stock_prof[$each] = sprint($stock_unit_avg_cost * $own);
}
$show_stock .= "\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t\t<td colspan='2'>{$stock_name}</td>\n\t\t\t\t\t\t\t\t\t\t<td>{$own}</td>\n\t\t\t\t\t\t\t\t\t\t<td nowrap>" . CUR . " " . sprint($stock_cost[$each]) . "</td>\n\t\t\t\t\t\t\t\t\t\t<td nowrap>" . CUR . " " . sprint($stock_cost[$each] * $own) . "</td>\n\t\t\t\t\t\t\t\t\t\t<td><input type='text' size='7' name='stock_prof[{$each}]' value='{$stock_prof[$each]}'></td>\n\t\t\t\t\t\t\t\t\t\t<td nowrap>{$show_cos}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t";
$stkamount = $stkamount + $stock_cost[$each] * $own;
}
}
}
if (strlen($send_stock) == 0) {
$send_stock = "<input type='hidden' name='stock_items' value='0'>";
$send_stock .= "<input type='hidden' name='stock_cost' value='0'>";
$send_stock .= "<input type='hidden' name='stock_prof' value='0'>";
$get_gds_note = "";
} else {
$send_stock .= "<input type='hidden' name='stockcontinue' value='0'>";
$send_stock .= "<input type='hidden' name='gotstock' value='1'>";
if (isset($gds_note) and strlen($gds_note) > 0) {
$gds_note_sel = "checked='yes'";
} else {
$gds_note_sel = "";
}
$get_gds_note = "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Print Goods Received Note</td>\n\t\t\t\t\t\t\t\t<td><input type='checkbox' name='gds_note' value='yes' {$gds_note_sel}></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t";
}
db_conn('core');
$get_sales_acc = "SELECT topacc,accnum,accname FROM accounts WHERE accid = '{$dept['incacc']}' LIMIT 1";
$run_sales_acc = db_exec($get_sales_acc) or errDie("Unable to get sales account information. (1)");
if (pg_numrows($run_sales_acc) < 1) {
$show_sales = "";
} else {
$sarr = pg_fetch_array($run_sales_acc);
$show_sales = "{$sarr['topacc']}/{$sarr['accnum']} - {$sarr['accname']}";
}
if (strlen($show_stock) > 0) {
$show_stock = "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th colspan='2'>Stock Description</th>\n\t\t\t\t\t\t\t<th>Number Of Units Returned</th>\n\t\t\t\t\t\t\t<th>Unit Cost</th>\n\t\t\t\t\t\t\t<th>Total</th>\n\t\t\t\t\t\t\t<th>Profit/Loss</th>\n\t\t\t\t\t\t\t<th>Cost Of Sale Account</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{$show_stock}\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td colspan='4' align='right'><b>Total:</b></td>\n\t\t\t\t\t\t\t<td nowrap>" . CUR . " " . sprint($stock_total) . "</td>\n\t\t\t\t\t\t\t<td colspan='2' nowrap><li class='err'>Difference ({$amount} - " . sprint($stock_total) . "): " . CUR . " " . sprint($amount - $stock_total) . "</li></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 colspan='7'><li class='err'>Sales Account To Be Used: {$show_sales}</li></td>\n\t\t\t\t\t\t</tr>";
}
# Probe tran type
if ($entry == "CT") {
$tran = "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td colspan='3'>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t\t<td colspan='2'>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t\t</tr>";
} else {
$tran = "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td colspan='3'>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t\t\t<td colspan='2'>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t</tr>";
}
if (!isset($amount)) {
$amount = $stkamount;
$details = "";
}
if (!isset($vataccid)) {
$vataccid = 0;
}
if (isset($vatinc) and $vatinc != "novat") {
db_connect();
$get_vatcodes = "SELECT * FROM vatcodes ORDER BY code";
$run_vatcodes = db_exec($get_vatcodes) or errDie("Unable to get vat code inoformation.");
if (pg_numrows($run_vatcodes) < 1) {
return "No Vatcodes Found. Please Add One First.";
} else {
$vatcode_drop = "<select name='vatcode'>";
while ($varr = pg_fetch_array($run_vatcodes)) {
$vatcode_drop .= "<option value='{$varr['id']}'>({$varr['code']}) {$varr['description']}</option>";
}
$vatcode_drop .= "</select>";
}
db_conn('core');
$vatacc = "<select name='vataccid'>";
$sql = "SELECT * FROM accounts WHERE div = '" . USER_DIV . "' ORDER BY accname ASC";
$accRslt = db_exec($sql);
if (pg_numrows($accRslt) < 1) {
return "<li>There are No accounts in Cubit.</li>";
}
$vatacc_newid = gethook("accnum", "salesacc", "name", "VAT", "VAT");
$vataccid = getCSetting("CRED_NOTE_VAT_ACC");
if (!isset($vataccid) or strlen($vataccid) < 1 or $vataccid == "0") {
$vataccid = $vatacc_newid;
}
// if (!isset($vataccid) OR $vataccid == "0")
// $vataccid = $vatacc_newid;
// while($acc_arr = pg_fetch_array($accRslt)){
// # Check Disable
// if(isDisabled($acc_arr['accid']))
// continue;
// if($vataccid == $acc_arr['accid']) {
// $sel = "selected";
// } else {
// $sel = "";
// }
// $vatacc .= "<option value='$acc_arr[accid]' $sel>$acc_arr[topacc]/$acc_arr[accnum] - $acc_arr[accname]</option>";
// }
// $vatacc .= "</select>";
if (isset($vatdedacc) and $vatdedacc == "dt") {
$dsel1 = "checked='yes'";
} else {
$dsel1 = "";
}
if (isset($vatdedacc) and $vatdedacc == "ct") {
$dsel2 = "checked='yes'";
} else {
$dsel2 = "";
}
if ($dsel1 == "" and $dsel2 == "") {
$dsel1 = "checked='yes'";
}
if (isStock($accid) and isset($gotstock)) {
$showgetvatacc = "<input type='hidden' name='vatdedacc' value='dt'>";
} else {
$showgetvatacc = "\n\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t<td colspan='2'valign='top'>VAT Deductable Account</td>\n\t\t\t\t\t\t\t\t\t<td colspan='2'>\n\t\t\t\t\t\t\t\t\t\t<input type='radio' name='vatdedacc' value='dt' {$dsel1} />{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}<br />\n\t\t\t\t\t\t\t\t\t\t<input type='radio' name='vatdedacc' value='ct' {$dsel2} />{$cust['accno']} - {$cust['surname']}\n\t\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t";
}
db_conn('core');
$get_vatacc = "SELECT accname FROM accounts WHERE accid = '{$vataccid}' LIMIT 1";
$run_vatacc = db_exec($get_vatacc) or errDie("Unable to get vat account details.");
if (pg_numrows($run_vatacc) < 1) {
$showvatacc = "";
} else {
$vatacc_id = pg_fetch_result($run_vatacc, 0, 0);
$showvatacc = "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='2'>VAT Account</td>\n\t\t\t\t\t\t\t\t<td colspan='3'>{$vatacc_id} <a target='_blank' href='settings/credit-note-accounts.php'>Change Account</a></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t";
}
// <tr class='".bg_class()."'>
// <td colspan='2'>VAT Account</td>
// <td colspan='3'>$vatacc</td>
// </tr>
$get_vats = "\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t<input type='hidden' name='vataccid' value='{$vataccid}'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th colspan='5'>VAT Detail</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{$showgetvatacc}\n\t\t\t\t\t\t{$showvatacc}\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td colspan='2'>VAT Code</td>\n\t\t\t\t\t\t\t<td colspan='3'>{$vatcode_drop}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t";
} else {
$get_vats = "\n\t\t\t\t\t\t<input type='hidden' name='vatinc' value='novat'>\n\t\t\t\t\t\t<input type='hidden' name='vatcode' value='0'>\n\t\t\t\t\t";
}
// Layout Details
$details = "\n \t\t\t\t<h3>Confirm Credit Note Details</h3>\n \t\t\t\t{$err}\n \t\t\t\t<form action='" . SELF . "' method='POST' name='form'>\n\t\t\t\t <input type='hidden' name='key' value='write'>\n\t\t\t\t\t\t<input type='hidden' name='type' value='1'>\n\t\t\t\t\t\t<input type='hidden' name='date' value='{$date}'>\n\t\t\t\t\t\t<input type='hidden' name='cusnum' value='{$cusnum}'>\n\t\t\t\t <input type='hidden' name='accid' value='{$accid}'>\n\t\t\t\t <input type='hidden' name='accname' value='{$acc['accname']}'>\n\t\t\t\t\t\t<input type='hidden' name='entry' value='{$entry}'>\n\t\t\t\t\t\t<input type='hidden' name='ct_day' value='{$ct_day}'>\n\t\t\t\t <input type='hidden' name='ct_month' value='{$ct_month}'>\n\t\t\t\t <input type='hidden' name='ct_year' value='{$ct_year}'>\n\t\t\t\t <input type='hidden' name='vatinc' value='{$vatinc}'>\n\t\t\t\t <input type='hidden' name='ac' value=''>\n\t\t\t\t <input type='hidden' name='amount' value='{$amount}'>\n\t\t\t\t <input type='hidden' name='refnum' value='{$refnum}'>\n\t\t\t\t <input type='hidden' name='difference' value='" . sprint($amount - $stock_total) . "'>\n\t\t\t\t {$send_stock}\n\t\t\t <table " . TMPL_tblDflts . " width='500'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th colspan='3'>Debit</th>\n\t\t\t\t\t\t\t<th colspan='2'>Credit</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{$tran}\n\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t{$show_stock}\n \t\t\t\t\t{$get_vats}\n \t\t\t\t</table>\n \t\t\t\t<table " . TMPL_tblDflts . " width='500'>\n \t\t\t\t\t" . TBL_BR . "\n \t\t\t\t\t<tr>\n \t\t\t\t\t\t<th colspan='2'>Transaction Details</th>\n \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>Transaction Details</td>\n\t\t\t\t\t\t\t<td valign='center'><textarea cols='30' rows='5' name='details'>{$details}</textarea></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{$get_gds_note}\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Person Authorising</td>\n\t\t\t\t\t\t\t<td valign='center'><input type='hidden' size='20' name='author' value=" . USER_NAME . ">" . USER_NAME . "</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td><input type='submit' name='back' value='« Correction'></td>\n\t\t\t\t\t\t\t<td valign='center' align='right'><input type='submit' value='Confirm »'></td>\n\t\t\t\t\t\t</tr>\n\t\t\t </table>\n\t\t\t </form>";
return $details;
}
function viewtran($_POST, $pure = false)
{
extract($_POST);
require_lib("validate");
$v = new validate();
$v->isOk($fprd, "string", 1, 14, "Invalid from period number.");
$v->isOk($tprd, "string", 1, 14, "Invalid to period number.");
$v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($stkids)) {
foreach ($stkids as $key => $stkid) {
$v->isOk($stkid, "num", 1, 20, "Invalid Stock code.");
}
} else {
return "<li class='err'>ERROR : Please select at least one Stock Item.</li>" . slctacc($_POST);
}
}
if ($v->isError()) {
return $v->genErrors();
}
if ($accnt == 'all') {
$stkids = array();
db_connect();
$sql = "SELECT stkid FROM stock WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$stkids[] = $ac['stkid'];
}
} else {
return "<li calss='err'> There are no Stock Items yet in Cubit.</li>";
}
}
# Period name
$prds = array();
if ($tprd < $fprd) {
for ($i = $fprd; $i <= 12; ++$i) {
$prds[] = $i;
}
for ($i = 1; $i <= $tprd; ++$i) {
$prds[] = $i;
}
} else {
for ($i = $fprd; $i <= $tprd; ++$i) {
$prds[] = $i;
}
}
$hide = "";
$sp = " ";
$trans = "";
foreach ($stkids as $key => $stkid) {
$stkRs = get("cubit", "*", "stock", "stkid", $stkid);
$stk = pg_fetch_array($stkRs);
$trans .= "\n\t\t<tr>\n\t\t\t<td colspan='8' align='center'><h3>{$stk['stkcod']} - {$stk['stkdes']}</h3></td>\n\t\t</tr>";
$hide .= "<input type='hidden' name='stkids[]' value='{$stkid}'>";
foreach ($prds as $prd) {
$prdname = getMonthName($prd);
$trans .= "\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<th colspan='8'>{$prdname}</th>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<th>DATE</th>\n\t\t\t\t\t\t\t\t<th>DETAILS</th>\n\t\t\t\t\t\t\t\t<th>QTY</th>\n\t\t\t\t\t\t\t\t<th>COST AMOUNT</th>\n\t\t\t\t\t\t\t\t<th>BALANCE</th>\n\t\t\t\t\t\t\t</tr>";
$idRs = get($prd, "max(id), min(id)", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "qty, (bqty - qty) as bqty, trantype, (balance - csamt) as balance", "stkledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "balance", "stkledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
/*
if($bal['trantype'] == 'dt'){
$bal['bqty'] = ($bal['bqty'] + $bal['qty']);
}else{
$bal['bqty'] = ($bal['bqty'] - $bal['qty']);
}
*/
} else {
$balRs = get("cubit", "csamt as balance, units as bqty", "stock", "stkid", $stkid);
$bal = pg_fetch_array($balRs);
$cbal['balance'] = 0;
$cbal['bqty'] = 0;
}
$balance = sprint($bal['balance']);
$trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='5'><b>({$stk['stkcod']}) {$stk['stkdes']}</b></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t\t<td>Balance Brought Forward</td>\n\t\t\t\t\t\t\t\t<td align='right'>" . sprint3($bal['bqty']) . "</td>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t\t<td align='right'>" . sprint($balance) . "</td>\n\t\t\t\t\t\t\t</tr>";
$dbal['balance'] = 0;
$dbal['bqty'] = 0;
$qtytotal = 0;
$tranRs = nget($prd, "*", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid . " ORDER BY edate,id ASC");
while ($tran = pg_fetch_array($tranRs)) {
$dbal['balance'] += $tran['csamt'];
$dbal['bqty'] = $tran['bqty'];
$qtytotal += $tran['qty'];
# sprinting
$tran['csamt'] = sprint($tran['csamt']);
$tran['balance'] = sprint($tran['balance']);
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$balance += $tran["csamt"];
$trans .= "\n\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t\t\t\t\t<td>{$tran['details']}</td>\n\t\t\t\t\t\t\t\t\t<td align='right'>" . sprint3($tran['qty']) . "</td>\n\t\t\t\t\t\t\t\t\t<td align='right'>{$tran['csamt']}</td>\n\t\t\t\t\t\t\t\t\t<td align='right'>" . sprint($balance) . "</td>\n\t\t\t\t\t\t\t\t</tr>";
}
$dbal['balance'] = sprint($dbal['balance']);
//$qtytotal was $dbal[bqty]
$trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t\t\t\t\t<td align='right'>" . sprint3($qtytotal) . "</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$dbal['balance']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$dbal['balance']}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t" . TBL_BR;
}
}
$view = "";
if (!$pure) {
$view .= "\n\t\t\t\t\t<center>\n\t\t\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t\t\t<input type='hidden' name='key' value='excel'>\n\t\t\t\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t\t\t\t<input type='hidden' name='continue' value='yes'>\n\t\t\t\t\t\t{$hide}\n\t\t\t\t\t\t<h3>Inventory Ledger</h3>";
}
$view .= "\n\t<table " . TMPL_tblDflts . " width=75%>\n\t{$trans}";
if (!$pure) {
$view .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</form>";
}
$view .= "\n\t</table>";
if (!$pure) {
$view .= "\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t<tr><td><br></td></tr>\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='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports.php'>Financials</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports-other.php'>Other Reports</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\n\t\t\t</tr>\n\t\t</table>";
}
return $view;
}
function viewtran($_POST)
{
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
$v->isOk($year, "string", 1, 10, "Invalid year.");
if (isset($accnt)) {
if ($accnt == 'slct') {
if (isset($emps)) {
foreach ($emps as $key => $emp) {
$v->isOk($emp, "num", 1, 20, "Invalid employee number.");
}
} else {
return "<li class='err'>Please select at least one Emloyee.</li>" . slctacc($_POST);
}
}
} else {
$v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get the ids
if ($accnt == 'all') {
$supids = array();
db_connect();
$sql = "SELECT empnum FROM employees WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$emps[] = $ac['empnum'];
}
} else {
return "<li calss='err'> There are no employees yet in Cubit.";
}
}
# Period name
$prdname = prdname($prd);
$hide = "";
db_conn('core');
$Sl = "SELECT * FROM year WHERE yrdb='{$year}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$yd = pg_fetch_array($Ri);
$trans = "";
foreach ($emps as $key => $emp) {
$supRs = get("cubit", "sname,fnames, balance", "employees", "empnum", $emp);
$sup = pg_fetch_array($supRs);
$idRs = get($yd['yrname'] . "_audit", "min(id)", $prdname . "_empledger", "empid", $emp);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($yd['yrname'] . "_audit", "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", $prdname . "_empledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$bal['cbalance'] += 0;
$bal['dbalance'] += 0;
} else {
$balRs = get("cubit", "balance", "employees", "empnum", $emp);
$bal = pg_fetch_array($balRs);
$bal['balance'] += 0;
if ($bal['balance'] < 0) {
$bal['dbalance'] = $bal['balance'] * -1;
$bal['cbalance'] = 0;
} else {
$bal['cbalance'] = $bal['balance'];
$bal['dbalance'] = 0;
}
//$bal['dbalance'] += $amount;
}
# Total balance changes
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
$bal['cbalance'] = 0;
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
$bal['dbalance'] = 0;
} else {
$bal['cbalance'] = 0;
$bal['dbalance'] = 0;
}
$bal['credit'] = $bal['cbalance'];
$bal['debit'] = $bal['dbalance'];
$balance = sprint($bal['cbalance'] - $bal['dbalance']);
$hide .= "<input type='hidden' name='emps[]' value='{$emp}'>";
$trans .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='8'><b>{$sup['sname']}, {$sup['fnames']} </b></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='2'><br></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['debit']}</td>\n\t\t\t\t<td align='right'>{$bal['credit']}</td>\n\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>";
# --> Transaction reading comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
$tranRs = get($yd['yrname'] . "_audit", "*", $prdname . "_empledger", "empid", $emp, "ORDER BY id");
while ($tran = pg_fetch_array($tranRs)) {
$caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
$cacc = pg_fetch_array($caccRs);
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
$cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td><br></td>\n\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t<td>{$tran['ref']}</td>\n\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t<td align='right'>{$cbalance}</td>\n\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\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<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td>A/C Total</td>\n\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t<td align='right'></td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>\n\t\t\t<tr><td colspan='8'><br></td></tr>";
}
//<tr><td colspan=8 align=center><input type=submit value='Export to Spreadsheet'></td></tr>
$sp = " ";
$view = "\n\t\t<center>\n\t\t<form action='../xls/supp-ledger-audit-xls.php' method='POST'>\n\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t<input type='hidden' name='year' value='{$year}'>\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{$hide}\n\t\t<h3>Employee Ledger</h3>\n\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t<tr>\n\t\t\t\t<td>{$sp}</td>\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{$trans}\n\t\t</form>\n\t\t</table>";
return $view;
}
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;
}
function viewtran($_POST)
{
# Get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($year, "string", 1, 10, "Invalid year.");
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
$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 {
$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"] . "<li>";
}
return $confirm . slctacc($_POST);
}
# Get the ids
if ($accnt == 'all') {
$accids = array();
db_conn($year);
$sql = "SELECT accid FROM year_balance 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>";
}
}
# Period name
$prdname = prdname($prd);
db_conn('core');
$Sl = "SELECT * FROM year WHERE yrdb='{$year}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$yd = pg_fetch_array($Ri);
$hide = "";
$trans = "";
foreach ($accids as $key => $accid) {
$accRs = get($year, "accname,accid,topacc,accnum", "year_balance", "accid", $accid);
$acc = pg_fetch_array($accRs);
# Get balances
$idRs = get($yd['yrname'] . "_audit", "max(id), min(id)", $prdname . "_ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$id['min'] += 0;
$id['max'] += 0;
$balRs = get($yd['yrname'] . "_audit", "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", $prdname . "_ledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($yd['yrname'] . "_audit", "cbalance,dbalance", $prdname . "_ledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
} else {
// //if($prd != PRD_DB){
// // continue;
// //}
$balRs = get($year, "credit as cbalance, debit as dbalance", $prdname, "accid", $accid);
$bal = pg_fetch_array($balRs);
$cbal['cbalance'] = 0;
$cbal['dbalance'] = 0;
// return "There are no transactions in this period.<p>";
}
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']);
$hide .= "<input type='hidden' name='accids[]' value='{$acc['accid']}'>";
$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>";
$trans .= "\n\t\t\t<tr class='bg-even'>\n\t\t\t\t<td colspan=2> </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>";
# --> transactio reding comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
$tranRs = get($yd['yrname'] . "_audit", "*", $prdname . "_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 = "DR";
} else {
if ($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 = "";
}
}
# 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<tr class='bg-odd'>\n\t\t\t\t\t<td><br></td>\n\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t<td align='right'>{$cbalance} {$cfl}</td>\n\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\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<tr class='bg-even'>\n\t\t\t\t<td colspan=2><br></td>\n\t\t\t\t<td>A/C Total</td>\n\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t<td align='right'></td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>\n\t\t\t<tr><td colspan='8'><br></td></tr>";
}
$sp = " ";
$view = "\n\t\t<center>\n\t\t<h3>General Ledger</h3>\n\t\t<form action='../xls/ledger-audit-xls.php' method='POST'>\n\t\t\t<input type='hidden' name='key' value='viewtran'>\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<input type='hidden' name='year' value='{$year}'>\n\t\t\t{$hide}\n\t\t<table " . TMPL_tblDflts . " width='90%'>\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t</tr>\n\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td>{$sp}</td>\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{$trans}\n\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t</table>\n\t\t</form>";
return $view;
}
function write($_POST)
{
# Get vars
extract($_POST);
if (isset($back)) {
unset($_POST["back"]);
return slctacc($_POST);
}
if (isset($cback)) {
return custconfirm($_POST);
}
# validate input
require_lib("validate");
$v = new validate();
foreach ($amount as $key => $value) {
if ($value > 0) {
continue;
}
$v->isOk($ctaccid[$key], "num", 1, 50, "Invalid Account to be Credited.[{$key}]");
$v->isOk($dtaccid[$key], "num", 1, 50, "Invalid Account to be Debited.[{$key}]");
$v->isOk($refnum[$key], "num", 1, 10, "Invalid Reference number.[{$key}]");
$v->isOk($amount[$key], "float", 1, 20, "Invalid Amount.[{$key}]");
$v->isOk($descript[$key], "string", 0, 255, "Invalid Details.[{$key}]");
$datea = explode("-", $date[$key]);
if (count($datea) == 3) {
if (!checkdate($datea[1], $datea[0], $datea[2])) {
$v->isOk("dadasdas", "num", 1, 1, "Invalid date.");
}
} else {
$v->isOk("asdasd", "num", 1, 1, "Invalid date.");
}
$date[$key] = $datea[2] . "-" . $datea[1] . "-" . $datea[0];
}
# display errors, if any
if ($v->isError()) {
$write = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$write .= "<li class='err'>" . $e["msg"] . "</li>";
}
$write .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $write;
}
foreach ($amount as $key => $value) {
if ($value <= 0) {
continue;
}
// Accounts details
$dtaccRs = get("core", "accname, topacc, accnum", "accounts", "accid", $dtaccid[$key]);
$dtacc[$key] = pg_fetch_array($dtaccRs);
$ctaccRs = get("core", "accname, topacc, accnum", "accounts", "accid", $ctaccid[$key]);
$ctacc[$key] = pg_fetch_array($ctaccRs);
$td = $date[$key];
if (isDebtors($dtaccid[$key])) {
# Select customer
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$dcusnum[$key]}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($custRslt) < 1) {
return "<li> Invalid Customer ID.</li>";
} else {
$cust = pg_fetch_array($custRslt);
}
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cust['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);
}
db_connect();
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
$sdate = date("Y-m-d");
# record the payment on the statement
$sql = "\n\t\t\t\t\tINSERT INTO stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, type, st, div, allocation_date\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cust['cusnum']}', '0', '{$amount[$key]}', '{$td}', '{$descript[$key]}', 'n', '" . USER_DIV . "', '{$td}'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
$sql = "\n\t\t\t\t\tINSERT INTO open_stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, balance, date, \n\t\t\t\t\t\ttype, st, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cust['cusnum']}', '0', '{$amount[$key]}', '{$amount[$key]}', '{$td}', \n\t\t\t\t\t\t'{$descript[$key]}', 'n', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$amount[$key]}') WHERE cusnum = '{$cust['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF);
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
# Make ledge record
custledger($cust['cusnum'], $ctaccid[$key], $td, $refnum[$key], $descript[$key], $amount[$key], "d");
custDT($amount[$key], $cust['cusnum'], $td);
$dtaccid[$key] = $dept['debtacc'];
$descript[$key] = $descript[$key] . " - Customer {$cust['surname']}";
} elseif (isCreditors($dtaccid[$key])) {
# Select supplier
db_connect();
$sql = "SELECT * FROM suppliers WHERE supid = '{$dsupid[$key]}' AND div = '" . USER_DIV . "'";
$suppRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($suppRslt) < 1) {
return "<li> Invalid Supplier ID.</li>";
} else {
$supp = pg_fetch_array($suppRslt);
}
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$supp['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);
}
db_connect();
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
$edate = date("Y-m-d");
# record the payment on the statement
$sql = "\n\t\t\t\t\tINSERT INTO sup_stmnt (\n\t\t\t\t\t\tsupid, edate, ref, cacc, descript, amount, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$supp['supid']}', '{$td}', '0', '{$ctaccid[$key]}', '{$descript[$key]}', '-{$amount[$key]}', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the supplier (make balance more)
$sql = "UPDATE suppliers SET balance = (balance - '{$amount[$key]}') WHERE supid = '{$supp['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update supplier in Cubit.", SELF);
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
# Ledger Records
suppledger($supp['supid'], $ctaccid[$key], $td, $refnum[$key], $descript[$key], $amount[$key], 'd');
suppDT($amount[$key], $supp['supid'], $td);
$dtaccid[$key] = $dept['credacc'];
$descript[$key] = $descript[$key] . " - Supplier {$supp['supname']}";
} elseif (isStock($dtaccid[$key])) {
# Select Stock
db_connect();
$sql = "SELECT * FROM stock WHERE stkid = '{$dstkids[$key]}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($stkRslt) < 1) {
return "<li> Invalid Stock ID.</li>";
} else {
$stk = pg_fetch_array($stkRslt);
}
# Get warehouse name
db_conn("exten");
$sql = "SELECT * FROM warehouses WHERE whid = '{$stk['whid']}' AND div = '" . USER_DIV . "'";
$whRslt = db_exec($sql);
$wh = pg_fetch_array($whRslt);
# Update Stock
db_connect();
$sql = "UPDATE stock SET csamt = (csamt + '{$amount[$key]}') WHERE stkid = '{$stk['stkid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
$sdate = date("Y-m-d");
# stkid, stkcod, stkdes, trantype, edate, qty, csamt, details
stockrec($stk['stkid'], $stk['stkcod'], $stk['stkdes'], 'dt', $td, 0, $amount[$key], "Stock Debit Transaction");
db_connect();
$dtaccid[$key] = $wh['stkacc'];
}
if (isDebtors($ctaccid[$key])) {
# Select customer
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$ccusnum[$key]}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($custRslt) < 1) {
return "<li> Invalid Customer ID.</li>";
} else {
$cust = pg_fetch_array($custRslt);
}
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cust['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);
}
db_connect();
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
$sdate = date("Y-m-d");
# record the payment on the statement
$sql = "\n\t\t\t\t\tINSERT INTO stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, type, st, div, allocation_date\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cust['cusnum']}', '0', '-{$amount[$key]}', '{$td}', '{$descript[$key]}', 'n', '" . USER_DIV . "', '{$td}'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
$sql = "\n\t\t\t\t\tINSERT INTO open_stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, balance, date, \n\t\t\t\t\t\ttype, st, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cust['cusnum']}', '0', '-{$amount[$key]}', '-{$amount[$key]}', '{$td}', \n\t\t\t\t\t\t'{$descript[$key]}', 'n', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance - '{$amount[$key]}') WHERE cusnum = '{$cust['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF);
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
# Make ledge record
custledger($cust['cusnum'], $dtaccid[$key], $sdate, $refnum[$key], $descript[$key], $amount[$key], "c");
custCT($amount[$key], $cust['cusnum'], $td);
$ctaccid[$key] = $dept['debtacc'];
$descript[$key] = $descript[$key] . " - Customer {$cust['surname']}";
} elseif (isCreditors($ctaccid[$key])) {
# Select supplier
db_connect();
$sql = "SELECT * FROM suppliers WHERE supid = '{$csupid[$key]}' AND div = '" . USER_DIV . "'";
$suppRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($suppRslt) < 1) {
return "<li> Invalid Supplier ID.</li>";
} else {
$supp = pg_fetch_array($suppRslt);
}
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$supp['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);
}
db_connect();
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
$edate = date("Y-m-d");
# record the payment on the statement
$sql = "\n\t\t\t\t\tINSERT INTO sup_stmnt (\n\t\t\t\t\t\tsupid, edate, ref, cacc, descript, amount, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$supp['supid']}', '{$td}', '0', '{$dtaccid[$key]}', '{$descript[$key]}', '{$amount[$key]}', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the supplier (make balance more)
$sql = "UPDATE suppliers SET balance = (balance + '{$amount[$key]}') WHERE supid = '{$supp['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update supplier in Cubit.", SELF);
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
# Ledger Records
suppledger($supp['supid'], $dtaccid[$key], $edate, $refnum[$key], $descript[$key], $amount[$key], 'c');
suppCT($amount[$key], $supp['supid'], $td);
$ctaccid[$key] = $dept['credacc'];
$descript[$key] = $descript[$key] . " - Supplier {$supp['supname']}";
} elseif (isStock($ctaccid[$key])) {
# Select Stock
db_connect();
$sql = "SELECT * FROM stock WHERE stkid = '{$cstkids[$key]}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($stkRslt) < 1) {
return "<li> Invalid Stock ID.</li>";
} else {
$stk = pg_fetch_array($stkRslt);
}
# Get warehouse name
db_conn("exten");
$sql = "SELECT * FROM warehouses WHERE whid = '{$stk['whid']}' AND div = '" . USER_DIV . "'";
$whRslt = db_exec($sql);
$wh = pg_fetch_array($whRslt);
# Update Stock
db_connect();
$sql = "UPDATE stock SET csamt = (csamt + '{$amount[$key]}') WHERE stkid = '{$stk['stkid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
$sdate = date("Y-m-d");
# stkid, stkcod, stkdes, trantype, edate, qty, csamt, details
stockrec($stk['stkid'], $stk['stkcod'], $stk['stkdes'], 'ct', $td, 0, $amount[$key], "Stock Credit Transaction");
db_connect();
$ctaccid[$key] = $wh['stkacc'];
}
# write transaction
writetrans($dtaccid[$key], $ctaccid[$key], $date[$key], $refnum[$key], $amount[$key], $descript[$key]);
}
// Layout
$write = "\n\t\t<center>\n\t\t<h3>Journal transactions have been recorded</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Date</th>\n\t\t\t\t<th>Ref num</th>\n\t\t\t\t<th>Debit</th>\n\t\t\t\t<th>Credit</th>\n\t\t\t\t<th>Amount</th>\n\t\t\t\t<th>Description</th>\n\t\t\t</tr>";
foreach ($amount as $key => $value) {
if ($value <= 0) {
continue;
}
if (isDebtors($dtaccid[$key])) {
$dcusRs = get("cubit", "*", "customers", "cusnum", $dcusnum[$key]);
$dcus = pg_fetch_array($dcusRs);
$dt = "<td valign='center'>{$dcus['accno']} - {$dcus['cusname']} {$dcus['surname']}</td>";
} elseif (isCreditors($dtaccid[$key])) {
$dsupRs = get("cubit", "*", "suppliers", "supid", $dsupid[$key]);
$dsup = pg_fetch_array($dsupRs);
$dt = "<td valign='center'>{$dsup['supno']} - {$dsup['supname']}</td>";
} elseif (isStock($dtaccid[$key])) {
$dstkRs = get("cubit", "*", "stock", "stkid", $dstkids[$key]);
$dstk = pg_fetch_array($dstkRs);
$dt = "<td valign='center'>{$dstk['stkcod']} - {$dstk['stkdes']}</td>";
} else {
$dt = "<td valign='center'>" . $dtacc[$key]['accname'] . "</td>";
}
if (isDebtors($ctaccid[$key])) {
$ccusRs = get("cubit", "*", "customers", "cusnum", $ccusnum[$key]);
$ccus = pg_fetch_array($ccusRs);
$ct = "<td valign='center'>{$ccus['accno']} - {$ccus['cusname']} {$ccus['surname']}</td>";
} elseif (isCreditors($ctaccid[$key])) {
$csupRs = get("cubit", "*", "suppliers", "supid", $csupid[$key]);
$csup = pg_fetch_array($csupRs);
$ct = "<td valign='center'>{$csup['supno']} - {$csup['supname']}</td>";
} elseif (isStock($ctaccid[$key])) {
$cstkRs = get("cubit", "*", "stock", "stkid", $cstkids[$key]);
$cstk = pg_fetch_array($cstkRs);
$ct = "<td valign='center'>{$cstk['stkcod']} - {$cstk['stkdes']}</td>";
} else {
$ct = "<td valign='center'>" . $ctacc[$key]['accname'] . "</td>";
}
$write .= "\n\t\t\t\t<tr bgcolor=" . bgcolorg() . ">\n\t\t\t\t\t<td>{$date[$key]}</td>\n\t\t\t\t\t<td>{$refnum[$key]}</td>\n\t\t\t\t\t{$dt}\n\t\t\t\t\t{$ct}\n\t\t\t\t\t<td>" . CUR . " {$amount[$key]}</td>\n\t\t\t\t\t<td>{$descript[$key]}</td>\n\t\t\t\t</tr>";
}
$write .= "\n\t\t</table>\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\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='datacell'>\n\t\t\t\t<td align='center'><a href='../reporting/index-reports.php'>Financials</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='multi-trans.php'>Journal Transactions</td>\n\t\t\t</tr>\n\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t</table>";
return $write;
}
#
#
#
#
#
#
#
#
#
# Get settings
require "../settings.php";
require "../core-settings.php";
if (isset($_POST["key"])) {
switch ($_POST["key"]) {
case "slctacc":
$OUTPUT = slctacc($_POST);
break;
case "viewtran":
$OUTPUT = viewtran($_POST);
break;
default:
$OUTPUT = "Invalid.";
}
} else {
$OUTPUT = select_year();
}
$OUTPUT .= "\n\t<p>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr><td><br></td></tr>\n\t\t<tr>\n\t\t\t<th>Quick Links</th>\n\t\t</tr>\n\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
# Get templete
require "../template.php";
function select_year()
{
function viewtran($_POST)
{
# Get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($fprd, "string", 1, 14, "Invalid from period number.");
$v->isOk($tprd, "string", 1, 14, "Invalid to period number.");
if (isset($accnt)) {
if ($accnt == 'slct') {
if (isset($supids)) {
foreach ($supids as $key => $supid) {
$v->isOk($supid, "num", 1, 20, "Invalid Supplier number.");
}
} else {
return "<li class='err'>Please select at least one Creditor.</li>" . slctacc();
}
}
} else {
$v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get the ids
if ($accnt == 'all') {
$supids = array();
db_connect();
$sql = "SELECT supid FROM suppliers WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$supids[] = $ac['supid'];
}
} else {
return "<li calss='err'> There are no suppliers yet in Cubit.</li>";
}
}
$prds = array();
if ($tprd < $fprd) {
for ($i = $fprd; $i <= 12; ++$i) {
$prds[] = $i;
}
for ($i = 1; $i <= $tprd; ++$i) {
$prds[] = $i;
}
} else {
for ($i = $fprd; $i <= $tprd; ++$i) {
$prds[] = $i;
}
}
$hide = "";
$sp = " ";
$trans = "";
foreach ($supids as $key => $supid) {
$supRs = get("cubit", "supname, supno, balance", "suppliers", "supid", $supid);
$sup = pg_fetch_array($supRs);
$hide .= "<input type='hidden' name='supids[]' value='{$supid}'>";
$trans .= "\n\t\t<tr>\n\t\t\t<td colspan='8' align='center'><h3>{$sup['supname']}</h3></td>\n\t\t</tr>";
foreach ($prds as $prd) {
$prdname = prdname($prd);
$trans .= "\n\t\t\t<tr>\n\t\t\t\t<th colspan='8'>{$prdname}</th>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<th>{$sp}</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>";
$idRs = get($prd, "min(id)", "suppledger", "supid", $supid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "suppledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$bal['cbalance'] += 0;
$bal['dbalance'] += 0;
} else {
$balRs = get("cubit", "balance", "suppliers", "supid", $supid);
$bal = pg_fetch_array($balRs);
$bal['balance'] += 0;
if ($bal['balance'] < 0) {
$bal['dbalance'] = $bal['balance'];
$bal['cbalance'] = 0;
} else {
$bal['cbalance'] = $bal['balance'];
$bal['dbalance'] = 0;
}
//$bal['dbalance'] += $amount;
}
/* show the balance as a debit/credit, and not the individual balances of both */
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
$bal['cbalance'] = 0;
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
$bal['dbalance'] = 0;
} else {
$bal['cbalance'] = 0;
$bal['dbalance'] = 0;
}
$bal['credit'] = sprint($bal['cbalance']);
$bal['debit'] = sprint($bal['dbalance']);
$balance = sprint($bal['cbalance'] - $bal['dbalance']);
$trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='8'><b>{$sup['supno']} - {$sup['supname']} </b></td>\n\t\t\t\t\t\t\t</tr>";
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, getYearOfFinMon($prd)));
$trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$bal['debit']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$bal['credit']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t</tr>";
# --> Transaction reading comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
if ($t == "s") {
$tranRs = get($prd, "*", "suppledger", "supid", $supid, "ORDER BY id");
} else {
$tranRs = get($prd, "*", "suppledger", "supid", $supid, "ORDER BY edate,id");
}
while ($tran = pg_fetch_array($tranRs)) {
$caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
$cacc = pg_fetch_array($caccRs);
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
if ($t == "s") {
$cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
} else {
$cbalance = sprint($dbal['credit'] + $bal['credit'] - ($dbal['debit'] + $bal['debit']));
}
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];
$trans .= "<tr class='bg-odd'><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}</td><td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</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'] = "";
$dbal['debit'] = "0.00";
}
$trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t\t\t\t\t<td>A/C Total</td>\n\t\t\t\t\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<td colspan='8'><br></td>\n\t\t\t\t\t\t\t</tr>";
}
}
$view = "\n\t\t\t\t<center>\n\t\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t\t\t<input type='hidden' name='t' value='{$t}'>\n\t\t\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t\t\t{$hide}\n\t\t\t\t\t<h3>Creditors Ledger</h3>\n\t\t\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t\t\t{$trans}\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='8' align='center'><input type='submit' name='export' value='Export to Spreadsheet'></td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>\n\t\t\t\t</from>";
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;
}
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 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, 5, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($stkids)) {
foreach ($stkids as $key => $stkid) {
$v->isOk($stkid, "num", 1, 20, "Invalid Stock code.");
}
} else {
return "<li class=err>ERROR : Please select at least one Stock Item.</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') {
$stkids = array();
db_connect();
$sql = "SELECT stkid FROM stock WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$stkids[] = $ac['stkid'];
}
} else {
return "<li calss=err> There are no Stock Items yet in Cubit.";
}
}
# Period name
$prdname = prdname($prd);
$hide = "";
$trans = "";
foreach ($stkids as $key => $stkid) {
$stkRs = get("cubit", "*", "stock", "stkid", $stkid);
$stk = pg_fetch_array($stkRs);
# Get balances
$idRs = get($prd, "max(id), min(id)", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "qty, (bqty - qty) as bqty, trantype, (balance - csamt) as balance", "stkledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "balance", "stkledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
/*
if($bal['trantype'] == 'dt'){
$bal['bqty'] = ($bal['bqty'] + $bal['qty']);
}else{
$bal['bqty'] = ($bal['bqty'] - $bal['qty']);
}
*/
} else {
if ($prd != PRD_DB) {
continue;
}
$balRs = get("cubit", "csamt as balance, units as bqty", "stock", "stkid", $stkid);
$bal = pg_fetch_array($balRs);
$cbal['balance'] = 0;
$cbal['bqty'] = 0;
}
$balance = sprint($bal['balance']);
$hide .= "<input type=hidden name=stkids[] value='{$stkid}'>";
$trans .= "<tr><td colspan=5><b>({$stk['stkcod']}) {$stk['stkdes']}</b></td></tr>";
$trans .= "<tr><td><br></td><td>Balance Brought Forward</td><td align=right>{$bal['bqty']}</td><td align=right>{$balance} </td><td align=right>{$balance} </td></tr>";
# --> transactio reding comes here <--- #
$dbal['balance'] = 0;
$dbal['bqty'] = 0;
$tranRs = nget($prd, "*", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid . " ORDER BY id ASC");
while ($tran = pg_fetch_array($tranRs)) {
$dbal['balance'] += $tran['csamt'];
$dbal['bqty'] += $tran['qty'];
# sprinting
$tran['csamt'] = sprint($tran['csamt']);
$tran['balance'] = sprint($tran['balance']);
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$trans .= "<tr><td>{$tran['edate']}</td><td>{$tran['details']}</td><td>{$tran['qty']}</td><td align=right>{$tran['csamt']}</td><td align=right>{$tran['balance']}</td></tr>";
}
$dbal['balance'] = sprint($dbal['balance']);
$trans .= "<tr><td><br></td><td>Total for period {$prdname} to Date :</td><td align=right>{$dbal['bqty']}</td><td align=right>{$dbal['balance']} </td><td align=right>{$dbal['balance']} </td></tr>";
$trans .= "<tr><td colspan=5><br></td></tr>";
}
$sp = " ";
$view = "\n\t<center>\n\t<h3>Inventory Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=75%>\n\t<tr><th>DATE</th><th>DETAILS</th><th>QTY</th><th>COST AMOUNT</th><th>BALANCE</th></tr>\n\t{$trans}\n\t</table>\n\t<p>";
include "temp.xls.php";
Stream("Ledger", $view);
}
function confirm($_POST, $err = "")
{
extract($_POST);
if (isset($back)) {
if (isset($vat)) {
return details($_POST);
} else {
return slctacc();
}
}
# Redirect if must chrgvat
if ($chrgvat == 'yes' && !isset($vataccid)) {
return slctVatacc($_POST);
}
if (isset($vatcode)) {
$vatcode += 0;
} else {
$vatcode = 0;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($ctaccid, "num", 1, 50, "Invalid Account to be Credited.");
$v->isOk($dtaccid, "num", 1, 50, "Invalid Account to be Debited.");
$v->isOk($refnum, "num", 1, 10, "Invalid Reference number.");
$v->isOk($amount, "float", 1, 20, "Invalid Amount.");
$v->isOk($details, "string", 0, 255, "Invalid Details.");
$v->isOk($author, "string", 1, 30, "Invalid Authorising person name.");
$v->isOk($chrgvat, "string", 1, 4, "Invalid charge vat option.");
if ($chrgvat == 'yes') {
$v->isOk($vataccid, "num", 1, 50, "Invalid VAT Account number.");
$v->isOk($vatdedacc, "num", 1, 50, "Invalid VAT Deductable Account number.");
$v->isOk($vatinc, "string", 1, 3, "Invalid vat inclusive selection.");
}
$v->isOk($date, "date", 1, 1, "Invalid date.");
if ($v->isError()) {
$err = $v->genErrors();
if (isset($details)) {
return details($_POST, $err);
} else {
return details2($_POST, $err);
}
}
$amount = sprint($amount);
if ($chrgvat == 'yes') {
$vataccRs = get("core", "*", "accounts", "accid", $vataccid);
$vatacc = pg_fetch_array($vataccRs);
$vatin = ucwords($vatinc);
//$VATP = TAX_VAT;
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl);
$vd = pg_fetch_array($Ri);
$VATP = $vd['vat_amount'];
# if vat must be charged
if ($vatinc == "no") {
$vatamt = sprint($VATP / 100 * $amount);
$totamt = sprint($amount + $vatamt);
} else {
$vatamt = sprint($amount / ($VATP + 100) * $VATP);
$totamt = sprint($amount);
}
$vataccnum = "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>VAT Amount</td>\n\t\t\t\t\t\t\t<td><input type='hidden' name='vatinc' value='{$vatinc}'><input type='hidden' name='vatamt' value='{$vatamt}'>{$vatamt}</td>\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>Total Transaction Amount</td>\n\t\t\t\t\t\t\t<td>{$totamt}</td>\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>VAT Account</td>\n\t\t\t\t\t\t\t<td><input type='hidden' name='vataccid' value='{$vataccid}'><input type='hidden' name='vatdedacc' value='{$vatdedacc}'>{$vatacc['topacc']}/{$vatacc['accnum']} - {$vatacc['accname']}</td>\n\t\t\t\t\t\t</tr>";
} else {
$vataccnum = "";
}
$dtaccRs = get("core", "*", "accounts", "accid", $dtaccid);
$dtacc = pg_fetch_array($dtaccRs);
$ctaccRs = get("core", "*", "accounts", "accid", $ctaccid);
$ctacc = pg_fetch_array($ctaccRs);
if (isb($dtaccid)) {
return slctacc("<li class='err'>You selected a main account for debit account.</li>");
}
if (isb($ctaccid)) {
return slctacc("<li class='err'>You selected a main account for credit account.</li>");
}
if ($vatcode > 0) {
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl) or errDie("unable to get data.");
$va = pg_fetch_array($Ri);
$vd = "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>VAT Code</td>\n\t\t\t\t\t<td>{$va['code']}</td>\n\t\t\t\t</tr>";
} else {
$vd = "";
}
$confirm = "\n\t\t\t\t\t<h3>Record Journal transaction</h3>\n\t\t\t\t\t{$err}\n\t\t\t\t\t<h4>Confirm entry</h4>\n\t\t\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t\t\t<input type='hidden' name='key' value='write' />\n\t\t\t\t\t\t<input type='hidden' name='vatcode' value='{$vatcode}' />\n\t\t\t\t\t\t<input type='hidden' name='dtaccid' value='{$dtaccid}' />\n\t\t\t\t\t\t<input type='hidden' name='ctaccid' value='{$ctaccid}' />\n\t\t\t\t\t\t<input type='hidden' name='dtaccname' value='{$dtacc['accname']}' />\n\t\t\t\t\t\t<input type='hidden' name='ctaccname' value='{$ctacc['accname']}' />\n\t\t\t\t\t\t<input type='hidden' name='date' value='{$date}' />\n\t\t\t\t\t\t<input type='hidden' name='refnum' value='{$refnum}' />\n\t\t\t\t\t\t<input type='hidden' name='amount' value='{$amount}' />\n\t\t\t\t\t\t<input type='hidden' name='chrgvat' value='{$chrgvat}' />\n\t\t\t\t\t\t<input type='hidden' name='details' value='{$details}' />\n\t\t\t\t\t\t<input type='hidden' name='author' value='{$author}' />\n\t\t\t\t\t\t<input type='hidden' name='jr_day' value='{$jr_day}' />\n\t\t\t\t\t\t<input type='hidden' name='jr_month' value='{$jr_month}' />\n\t\t\t\t\t\t<input type='hidden' name='jr_year' value='{$jr_year}' />\n\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td width='50%'><h3>Debit</h3></td>\n\t\t\t\t\t\t\t<td width='50%'><h3>Credit</h3></td>\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>{$dtacc['topacc']}/{$dtacc['accnum']} - {$dtacc['accname']}</td>\n\t\t\t\t\t\t\t<td>{$ctacc['topacc']}/{$ctacc['accnum']} - {$ctacc['accname']}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Date</td>\n\t\t\t\t\t\t\t<td>{$date}</td>\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>Reference number</td>\n\t\t\t\t\t\t\t<td>{$refnum}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Amount</td>\n\t\t\t\t\t\t\t<td>{$amount}</td>\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>Charge VAT </td>\n\t\t\t\t\t\t\t<td>{$chrgvat}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{$vataccnum}\n\t\t\t\t\t\t{$vd}\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Details</td>\n\t\t\t\t\t\t\t<td>{$details}</td>\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>Authorising Person</td>\n\t\t\t\t\t\t\t<td>{$author}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td><input type='submit' name='back' value='« Correction'></td>\n\t\t\t\t\t\t\t<td align='right'><input type='submit' value='Write »'></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>\n\t\t\t\t\t</form>" . mkQuickLinks(ql("../reporting/index-reports.php", "Financials"), ql("../core/trans-new.php", "Journal Transactions"));
return $confirm;
}
case "write":
$OUTPUT = write($_POST);
break;
case "details":
if (isset($_POST['details'])) {
$OUTPUT = details($_POST);
} else {
$OUTPUT = details2($_POST);
}
break;
default:
$OUTPUT = slctacc();
}
} else {
# Display default output
$OUTPUT = slctacc();
}
# get templete
require "../template.php";
# Select Accounts
function slctacc()
{
# get last ref number
$refnum = getrefnum();
/*refnum*/
// Accounts (debit)
$view = "<center>\n <h3> Journal transaction On Previous Periods </h3>\n <br><br>\n <form action='" . SELF . "' method=post name=form>\n <input type=hidden name=key value=details>\n <table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\n <tr><th>Field</th><th>Value</th></tr>\n <tr class='bg-odd'><td>Date</td><td><input type=text size=2 name=day maxlength=2 value='" . date("d") . "'>-<input type=text size=2 name=mon maxlength=2 value='" . date("m") . "'>-<input type=text size=4 name=year maxlength=4 value='" . date("Y") . "'></td></tr>\n <tr class='bg-odd'><td>Reference Number</td><td><input type=text size=10 name=refnum value='" . $refnum++ . "'></td></tr>\n\t\t<tr class='bg-even'><td>Select Period </td><td><select name=prd>";
db_conn(YR_DB);
$sql = "SELECT * FROM info WHERE prdname !='' AND prddb < '" . PRD_DB . "'";
$prdRslt = db_exec($sql);
$rows = pg_numrows($prdRslt);
function confirm($_POST)
{
# Get vars
extract($_POST);
if (isset($back)) {
return slctacc($_POST);
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cusnum, "num", 1, 50, "Invalid Customer number.");
$v->isOk($accid, "num", 1, 50, "Invalid Contra Account.");
$v->isOk($refnum, "num", 1, 10, "Invalid Reference number.");
$v->isOk($amount, "float", 1, 20, "Invalid Amount.");
$v->isOk($details, "string", 0, 255, "Invalid Details.");
$v->isOk($author, "string", 1, 30, "Invalid Authorising person name.");
$datea = explode("-", $date);
if (count($datea) == 3) {
if (!checkdate($datea[1], $datea[0], $datea[2])) {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
} else {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
if ($type == 1) {
return $confirm . details($_POST);
} else {
$_POST["accnum"] = $ac;
return $confirm . details2($_POST);
}
}
# Get contra account details
$accRs = get("core", "*", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
# Select customer
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($custRslt) < 1) {
return "<li class='err'>Invalid customer ID, or customer has been blocked.</li>";
} else {
$cust = pg_fetch_array($custRslt);
}
# Probe tran type
if ($entry == "CT") {
$tran = "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t\t\t<td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t\t\t</tr>";
} else {
$tran = "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t\t</tr>";
}
if (!isset($ac)) {
$ac = "";
}
// Layout
$confirm = "\n \t\t\t\t<h3>Record Journal transaction</h3>\n\t\t\t\t\t\t<h4>Confirm entry</h4>\n\t\t\t\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t\t\t\t<input type='hidden' name='key' value='write'>\n\t\t\t\t\t\t\t<input type='hidden' name='cusnum' value='{$cusnum}'>\n\t\t\t\t\t\t\t<input type='hidden' name='accid' value='{$accid}'>\n\t\t\t\t\t\t\t<input type='hidden' name='accname' value='{$acc['accname']}'>\n\t\t\t\t\t\t\t<input type='hidden' name='date' value='{$date}'>\n\t\t\t\t\t\t\t<input type='hidden' name='refnum' value='{$refnum}'>\n\t\t\t\t\t\t\t<input type='hidden' name='entry' value='{$entry}'>\n\t\t\t\t\t\t\t<input type='hidden' name='amount' value='{$amount}'>\n\t\t\t\t\t\t\t<input type='hidden' name='details' value='{$details}'>\n\t\t\t\t\t\t\t<input type='hidden' name='author' value='{$author}'>\n\t\t\t\t\t\t\t<input type='hidden' name='type' value='{$type}'>\n\t\t\t\t\t\t\t<input type='hidden' name='ct_day' value='{$ct_day}'>\n\t\t\t\t\t\t\t<input type='hidden' name='ct_month' value='{$ct_month}'>\n\t\t\t\t\t\t\t<input type='hidden' name='ct_year' value='{$ct_year}'>\n\t\t\t\t\t\t\t<input type='hidden' name='ac' value='{$ac}'>\n\t\t\t\t\t\t<table " . TMPL_tblDflts . " width='500'>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<td width='50%'><h3>Debit</h3></td>\n\t\t\t\t\t\t\t\t<td width='50%'><h3>Credit</h3></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t{$tran}\n\t\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Date</td>\n\t\t\t\t\t\t\t\t<td>{$date}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Reference number</td>\n\t\t\t\t\t\t\t\t<td>{$refnum}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Amount</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$amount}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Details</td>\n\t\t\t\t\t\t\t\t<td>{$details}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Authorising Person</td>\n\t\t\t\t\t\t\t\t<td>{$author}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<td><input type='submit' name='back' value='« Correction'></td>\n\t\t\t\t\t\t\t\t<td align='right'><input type='submit' value='Write »'></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t</table>\n\t\t\t\t\t\t</form>" . mkQuickLinks(ql("../core/trans-new.php", "Journal Transactions"), ql("../customers-view.php", "View Customers"));
return $confirm;
}
if (isset($_POST['details'])) {
$OUTPUT = details($_POST);
} else {
$OUTPUT = details2($_POST);
}
break;
default:
if (isset($_GET['cusnum'])) {
$OUTPUT = slctacc($_GET);
} else {
$OUTPUT = "<li> - Invalid use of module";
}
}
} else {
if (isset($_GET['cusnum'])) {
$OUTPUT = slctacc($_GET);
} else {
$OUTPUT = "<li> - Invalid use of module";
}
}
# Get templete
require "template.php";
# Select Accounts
function slctacc($_GET)
{
extract($_GET);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cusnum, "num", 1, 50, "Invalid customer id.");
# display errors, if any
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, 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"] . "</li>";
}
$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.</li>";
}
}
# Period name
$prdname = prdname($prd);
$hide = "";
$trans = "";
foreach ($accids as $key => $accid) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
$hide .= "<input type=hidden name=accids[] value='{$accid}'>";
db_conn($prd);
$sql = "SELECT DISTINCT eref FROM ledger WHERE acc = '{$accid}' AND debit > 0";
$disRs = db_exec($sql);
if (pg_numrows($disRs) > 0) {
while ($dis = pg_fetch_array($disRs)) {
$sql = "SELECT sum(debit) as debit,edate FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}' GROUP BY edate";
$sumRs = db_exec($sql);
$sum = pg_fetch_array($sumRs);
$trans .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$sum['edate']}</td>\n\t\t\t\t\t\t\t<td><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t\t\t\t\t<td>{$dis['eref']}</td>\n\t\t\t\t\t\t\t<td><br descript><td align='right'>{$sum['debit']}</td>\n\t\t\t\t\t\t\t<td><br credit></td>\n\t\t\t\t\t\t\t<td><br cacc></td>\n\t\t\t\t\t\t</tr>";
$sql = "SELECT * FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}'";
$tranRs = db_exec($sql);
while ($tran = pg_fetch_array($tranRs)) {
$trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><br date></td>\n\t\t\t\t\t\t\t\t<td><br account></td>\n\t\t\t\t\t\t\t\t<td><br ref></td>\n\t\t\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t\t\t<td><br debit></td>\n\t\t\t\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t\t\t</tr>";
}
$trans .= "<tr><td><br></td></tr>";
}
}
db_conn($prd);
$sql = "SELECT DISTINCT eref FROM ledger WHERE acc = '{$accid}' AND credit > 0";
$disRs = db_exec($sql);
if (pg_numrows($disRs) > 0) {
while ($dis = pg_fetch_array($disRs)) {
$sql = "SELECT sum(credit) as credit,edate FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}' GROUP BY edate";
$sumRs = db_exec($sql);
$sum = pg_fetch_array($sumRs);
$trans .= "\n\t\t\t\t\t\t<tr class='bg-even'>\n\t\t\t\t\t\t\t<td>{$sum['edate']}</td>\n\t\t\t\t\t\t\t\t<td><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t\t\t\t\t\t<td>{$dis['eref']}</td>\n\t\t\t\t\t\t\t\t<td><br descript></td>\n\t\t\t\t\t\t\t\t<td><br debit></td>\n\t\t\t\t\t\t\t\t<td>{$sum['credit']}</td>\n\t\t\t\t\t\t\t\t<td><br></td>\n\t\t\t\t\t\t\t</tr>";
$sql = "SELECT * FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}'";
$tranRs = db_exec($sql);
while ($tran = pg_fetch_array($tranRs)) {
$trans .= "\n\t\t\t\t\t\t\t<tr class='bg-odd'>\n\t\t\t\t\t\t\t\t<td><br date></td>\n\t\t\t\t\t\t\t\t<td><br account></td>\n\t\t\t\t\t\t\t\t<td><br ref></td>\n\t\t\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t\t\t\t<td><br credit></td>\n\t\t\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t\t\t</tr>";
}
$trans .= "<tr><td><br></td></tr>";
}
}
}
$sp = " ";
$view = "\n\t\t\t<center>\n\t\t\t<form action='../xls/trans-amt-xls.php' method='POST'>\n\t\t\t\t<input type='hidden' name='key' value='viewtran'>\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<h3>Journal Entries By Ref no.</h3>\n\t\t\t<table " . TMPL_tblDflts . " width='95%'>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Account</th>\n\t\t\t\t\t<th>Ref No.</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>Contra Acc</th>\n\t\t\t\t</tr>\n\t\t\t\t{$trans}\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></tr>\n\t\t\t</from>\n\t\t\t</table>\n\t\t\t<p>\n\t\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td align='center'><a href='index-reports.php'>Financials</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td align='center'><a href='index-reports-journal.php'>Current Year Details General Ledger Reports</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\n\t\t\t\t</tr>\n\t\t\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)
{
# Get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
$v->isOk($prd_f, "num", 1, 2, "Invalid From Period.");
$v->isOk($prd_t, "num", 1, 2, "Invalid To Period.");
if (isset($accnt)) {
if ($accnt == 'slct') {
if (isset($supids)) {
foreach ($supids as $key => $supid) {
$v->isOk($supid, "num", 1, 20, "Invalid Supplier number.");
}
} else {
return "<li class=err>Please select at least one Creditor.</li>" . slctacc();
}
}
} else {
$v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
}
# 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') {
$supids = array();
db_connect();
$sql = "SELECT empnum FROM employees WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$supids[] = $ac['empnum'];
}
} else {
return "<li calss=err> There are no employees yet in Cubit.";
}
}
// # Period name
// $prdname = prdname($prd);
//
// $trans = "";
// foreach($supids as $key => $supid){
// $supRs = get("cubit", "empnum,sname,fnames, balance", "employees", "empnum", $supid);
// $sup = pg_fetch_array($supRs);
//
// $idRs = get($prd, "min(id)", "empledger", "empid", $supid);
// $id = pg_fetch_array($idRs);
//
// if($id['min'] <> 0){
// $balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "empledger", "id", $id['min']);
// $bal = pg_fetch_array($balRs);
// $bal['cbalance'] += 0;
// $bal['dbalance'] += 0;
// }else{
// $balRs = get("cubit", "balance", "employees", "empnum", $supid);
// $bal = pg_fetch_array($balRs);
// $bal['balance']+=0;
//
// if($bal['balance']<0) {
// $bal['dbalance'] = $bal['balance'];
// $bal['cbalance'] = 0;
// } else {
// $bal['cbalance'] = $bal['balance'];
// $bal['dbalance'] = 0;
// }
// //$bal['dbalance'] += $amount;
// }
//
// # Total balance changes
// if($bal['dbalance'] > $bal['cbalance']){
// $bal['dbalance'] = ($bal['dbalance'] - $bal['cbalance']);
// $bal['cbalance'] = 0;
// }elseif($bal['cbalance'] > $bal['dbalance']){
// $bal['cbalance'] = ($bal['cbalance'] - $bal['dbalance']);
// $bal['dbalance'] = 0;
// }else{
// $bal['cbalance'] = 0;
// $bal['dbalance'] = 0;
// }
//
// $bal['credit'] = sprint($bal['cbalance']);
// $bal['debit'] = sprint($bal['dbalance']);
//
// $balance=sprint($bal['cbalance']-$bal['dbalance']);
//
// $trans .= "<tr><td colspan=8><b>$sup[sname], $sup[fnames]</b></td></tr>";
// $trans .= "<tr><td colspan=2><br></td><td>Br/Forwd</td><td>Brought Forward</td><td align=right>$bal[debit]</td><td align=right>$bal[credit]</td><td align=right>$balance</td><td> </td></tr>";
//
// # --> Transaction reading comes here <--- #
// $dbal['debit'] = 0;
// $dbal['credit'] = 0;
//
// if($t=="s") {
// $tranRs = get($prd, "*", "empledger", "empid", $supid,"ORDER BY id");
// } else {
// $tranRs = get($prd, "*", "empledger", "empid", $supid,"ORDER BY edate,id");
// }
// while($tran = pg_fetch_array($tranRs)){
// $caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
// $cacc = pg_fetch_array($caccRs);
//
// $tran['debit']=sprint($tran['debit']);
// $tran['credit']=sprint($tran['credit']);
//
// $dbal['debit'] += $tran['debit'];
// $dbal['credit'] += $tran['credit'];
//
// if($t=="s") {
//
// $cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
//
// } else {
//
// $cbalance = sprint(($dbal['credit']+$bal['credit']) - ($dbal['debit']+$bal['debit']));
//
// }
//
// 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];
//
// $trans .= "<tr><td><br></td><td>$tran[edate]</td><td>$tran[ref]</td><td>$tran[des]</td><td align=right>$tran[debit]</td><td align=right>$tran[credit]</td><td align=right>$cbalance</td><td>$cacc[topacc]/$cacc[accnum] - $cacc[accname]</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'] = "";
// $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>";
// }
$prds = array();
if ($prd_f > $prd_t) {
for ($i = $prd_f; $i <= 12; ++$i) {
$prds[] = $i;
}
for ($i = 1; $i <= $prd_t; ++$i) {
$prds[] = $i;
}
} else {
for ($i = $prd_f; $i <= $prd_t; ++$i) {
$prds[] = $i;
}
}
# Period name
$hide = "";
$trans = "";
foreach ($supids as $key => $supid) {
$supRs = get("cubit", "empnum,sname,fnames, balance", "employees", "empnum", $supid);
$sup = pg_fetch_array($supRs);
$trans .= "\n\t\t<tr>\n\t\t\t<td align='center' colspan='8'><h2>{$sup['sname']}, {$sup['fnames']}</h2></td>\n\t\t</tr>";
foreach ($prds as $prd) {
$idRs = get($prd, "min(id)", "empledger", "empid", $supid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "empledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$bal['cbalance'] += 0;
$bal['dbalance'] += 0;
} else {
$sql = array();
for ($i = $MONPRD[$prd] - 1; $i >= 1; --$i) {
$pprdname = getMonthName($PRDMON[$i]);
$sql[] = "SELECT id,cbalance,dbalance\n\t\t\t\t\t\t\tFROM \"{$i}\".empledger\n\t\t\t\t\t\t\tWHERE empid='{$supid}'";
}
if (count($sql) > 0) {
$sql = "SELECT * FROM (" . implode(" UNION ", $sql) . ") AS sl\n\t\t\t\t\t\t\tORDER BY id DESC\n\t\t\t\t\t\t\tLIMIT 1";
$balRs = db_exec($sql);
$bal = pg_fetch_array($balRs);
}
}
# Total balance changes
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
$bal['cbalance'] = 0;
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
$bal['dbalance'] = 0;
} else {
$bal['cbalance'] = 0;
$bal['dbalance'] = 0;
}
$bal['credit'] = sprint($bal['cbalance']);
$bal['debit'] = sprint($bal['dbalance']);
$balance = sprint($bal['cbalance'] - $bal['dbalance']);
// make the date of the last day of the previous prd
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, getYearOfEmpMon($prd - 1)));
if (!isset($sp)) {
$sp = "";
}
$c = 0;
$hide .= "<input type=hidden name=supids[] value='{$supid}'>";
$trans .= "\n\t\t\t<tr>\n\t\t\t\t<th colspan='8'>" . getMonthName($prd) . " " . getYearOfEmpMon($prd) . "</th>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<th>{$sp}</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>\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['debit']}</td>\n\t\t\t\t<td align=right>{$bal['credit']}</td>\n\t\t\t\t<td align=right>{$balance}</td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>";
# --> Transaction reading comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
if ($t == "s") {
$tranRs = get($prd, "*", "empledger", "empid", $supid, "ORDER BY id");
} else {
$tranRs = get($prd, "*", "empledger", "empid", $supid, "ORDER BY edate,id");
}
while ($tran = pg_fetch_array($tranRs)) {
$caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
$cacc = pg_fetch_array($caccRs);
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
if ($t == "s") {
$cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
} else {
$cbalance = sprint($dbal['credit'] + $bal['credit'] - ($dbal['debit'] + $bal['debit']));
}
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];
$trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td> </td>\n\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t<td>{$tran['ref']}</td>\n\t\t\t\t\t<td>{$tran['des']}</td>\n\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t<td align='right'>{$cbalance}</td>\n\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\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<tr>\n\t\t\t\t<td colspan='2'> </td>\n\t\t\t\t<td>A/C Total</td>\n\t\t\t\t<td>Total for period " . prdname($prd) . ":</td>\n\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t<td align='right'> </td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>\n\t\t\t" . TBL_BR;
}
}
$sp = " ";
$view = "\n\t\t\t\t<center>\n\t\t\t\t<h3>Employee Ledger</h3>\n\t\t\t\t<table " . TMPL_tblDflts . " width=75%>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td>{$sp}</td>\n\t\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t\t<th>Reference</th>\n\t\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t\t<th>Debit</th>\n\t\t\t\t\t\t<th>Credit</th>\n\t\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t\t<th>Contra Acc</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t{$trans}\n\t\t\t\t</table>";
include "temp.xls.php";
Stream("Ledger", $view);
}
