function viewtrans($_POST)
{
# Get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period name.");
$v->isOk($type, "string", 1, 3, "Invalid Account type.");
$v->isOk($tab, "string", 1, 50, "Invalid Account type.");
$v->isOk($catid, "string", 1, 20, "Invalid Category.");
# 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 Category Name
$cats = get("core", "catname", $tab, "catid", $catid);
$cat = pg_fetch_array($cats);
// Set up table to display in
$OUTPUT = "\n\t\t\t<center>\n\t\t\t<h3>Journal Entries for Category : {$cat['catname']}</h3>\n\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t<tr>\n\t\t\t\t<th>Date</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>Person Who Authorized</th>\n\t\t\t</tr>";
core_connect();
# Get accounts
$type = strtoupper($type);
$sql = "SELECT * FROM accounts WHERE catid='{$catid}' AND div = '" . USER_DIV . "'";
$accRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve account details from database.", SELF);
$numrows = pg_numrows($accRslt);
while ($acc = pg_fetch_array($accRslt)) {
$OUTPUT .= viewtran($_POST, $acc['accid']);
}
$OUTPUT .= "\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td align='center' colspan='10'>\n\t\t\t\t\t\t<form action='../xls/cat-trans-xls.php' method='POST' name='form'>\n\t\t\t\t\t\t\t<input type='hidden' name='key' value='viewtrans'>\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='tab' value='{$tab}'>\n\t\t\t\t\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t\t\t\t\t<input type='hidden' name='catid' value='{$catid}'>\n\t\t\t\t\t\t\t<input type='submit' name='xls' value='Export to spreadsheet'>\n\t\t\t\t\t\t</form>\n\t\t\t\t\t</td>\n\t\t\t\t</tr>\n\t\t\t</table>\n\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><th>Quick Links</th></tr>\n\t\t\t\t<tr class='datacell'>\n\t\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\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 $OUTPUT;
}
#
#
#
#
#
#
#
#
##
# Get settings
require "../settings.php";
require "../core-settings.php";
if (isset($_POST["key"])) {
switch ($_POST["key"]) {
case "viewtran":
$OUTPUT = viewtran($_POST);
break;
default:
$OUTPUT = slctacc();
}
} else {
$OUTPUT = slctacc();
}
# Get templete
require "../template.php";
function slctacc()
{
core_connect();
$sql = "SELECT * FROM accounts WHERE div = '" . USER_DIV . "' ORDER BY accname ASC";
$accRslt = db_exec($sql) or errDie("Could not retrieve Categories Information from the Database.", SELF);
if (pg_numrows($accRslt) < 1) {
function viewtran($_POST)
{
# get vars
extract($_POST);
if (!isset($from_year)) {
$from_year = date("Y");
}
if (!isset($from_month)) {
$from_month = date("m");
}
if (!isset($from_day)) {
$from_day = "01";
}
if (!isset($to_year)) {
$to_year = date("Y");
}
if (!isset($to_month)) {
$to_month = date("m");
}
if (!isset($to_day)) {
$to_day = date("d");
}
$from_date = "{$from_year}-{$from_month}-{$from_day}";
$to_date = "{$to_year}-{$to_month}-{$to_day}";
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
if (isset($details)) {
$v->isOk($accid, "string", 1, 20, "Invalid Account number.");
$hide = "\n\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t<input type='hidden' name='details' value='{$details}'>\n\t\t\t<input type='hidden' name='accid' value='{$accid}'>";
} else {
$v->isOk($topacc, "num", 1, 4, "Invalid Account number.");
$v->isOk($accnum, "num", 0, 3, "Invalid Account number.");
$hide = "\n\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t<input type='hidden' name='topacc' value='{$topacc}'>\n\t\t\t<input type='hidden' name='accnum' value='{$accnum}'>";
}
$v->isOk($from_day, "num", 1, 2, "Invalid from Date day.");
$v->isOk($from_month, "num", 1, 2, "Invalid from Date month.");
$v->isOk($from_year, "num", 1, 4, "Invalid from Date Year.");
$v->isOk($to_day, "num", 1, 2, "Invalid to Date day.");
$v->isOk($to_month, "num", 1, 2, "Invalid to Date month.");
$v->isOk($to_year, "num", 1, 4, "Invalid to Date Year.");
if (!checkdate($from_month, $from_day, $from_year)) {
$v->isOk($from_date, "num", 1, 1, "Invalid from date.");
$_POST["from_year"] = date("Y");
$_POST["from_month"] = date("m");
$_POST["from_day"] = date("d");
}
if (!checkdate($to_month, $to_day, $to_year)) {
$v->isOk($to_date, "num", 1, 1, "Invalid to date.");
$_POST["to_year"] = date("Y");
$_POST["to_month"] = date("m");
$_POST["to_day"] = date("d");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
return $confirm . viewtran($_POST);
}
if (isset($details)) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
} else {
if (strlen($accnum) < 2) {
// account numbers
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "topacc", "{$topacc}' AND accnum = '000");
if (pg_numrows($accRs) < 1) {
return "<li> Accounts number : {$accnum} does not exist.</li>";
}
$acc = pg_fetch_array($accRs);
} else {
// account numbers
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "topacc", "{$topacc}' AND accnum = '{$accnum}");
if (pg_numrows($accRs) < 1) {
return "<li> Accounts number : {$topacc}/{$accnum} does not exist.</li>";
}
$acc = pg_fetch_array($accRs);
}
}
// Set up table to display in
$OUTPUT = "\n\t\t<center>\n\t\t<h3>Journal Entries for Account : {$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</h3>\n\t\t<form action='" . SELF . "' method='POST'>\n\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t<tr>\n\t\t\t\t<td width='15%' align='center'>\n\t\t\t\t\t<table " . TMPL_tblDflts . " width='55%'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th colspan='2' align='center'>Date Range</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>From Date:</th>\n\t\t\t\t\t\t\t<th>To Date:</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td nowrap>" . mkDateSelect("from", $from_year, $from_month, $from_day) . "</td>\n\t\t\t\t\t\t\t<td nowrap>" . mkDateSelect("to", $to_year, $to_month, $to_day) . "</td>\n\t\t\t\t\t\t\t<td><input type='submit' value='View'></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>\n\t\t\t\t</tr>\n\t\t\t</tr>\n\t\t</table>\n\t\t<br>\n\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t<tr>\n\t\t\t\t<th>Date</th>\n\t\t\t\t<th>Reference</th>\n\t\t\t\t<th>Contra Acc</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>User</th>\n\t\t\t</tr>";
db_connect();
# Get Transactions
$sql = "\n\t\tSELECT * FROM \"1\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"2\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"3\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"4\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"5\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"6\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"7\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"8\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"9\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"10\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"11\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"12\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"13\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}') UNION \n\t\tSELECT * FROM \"14\".transect WHERE ((debit = '{$acc['accid']}' AND div = '" . USER_DIV . "') OR (credit = '{$acc['accid']}' AND div = '" . USER_DIV . "')) AND (date >= '{$from_date}' AND date <= '{$to_date}')";
$tranRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve Transaction details from database.", SELF);
if (pg_numrows($tranRslt) < 1) {
$OUTPUT .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='10'>No Transactions found</td>\n\t\t\t</tr>";
# counts
$credtot = 0;
$debtot = 0;
} else {
# counts
$credtot = 0;
$debtot = 0;
# display all transactions
while ($tran = pg_fetch_array($tranRslt)) {
#get vars from tran as the are in db
extract($tran);
$amount = sprint($amount);
if ($debit == $acc['accid']) {
$cacc['accname'] = $caccname;
$cacc['accnum'] = $caccnum;
$cacc['topacc'] = $ctopacc;
$debitamt = "R " . $amount;
$debtot += $amount;
$creditamt = "";
} else {
$debitamt = "";
$creditamt = "R " . $amount;
$credtot += $amount;
$cacc['accname'] = $daccname;
$cacc['accnum'] = $daccnum;
$cacc['topacc'] = $dtopacc;
}
# format date
$date = explode("-", $date);
$date = $date[2] . "-" . $date[1] . "-" . $date[0];
/*
get contra account name
$caccRs = get("core","accname,topacc,accnum","accounts","accid",$cacc);
$cacc = pg_fetch_array($caccRs);
*/
$OUTPUT .= "\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>{$custom_refnum}</td>\n\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\t\t\t\t\t<td>{$details}</td>\n\t\t\t\t\t<td align='right'>{$debitamt}</td>\n\t\t\t\t\t<td align='right'>{$creditamt}</td>\n\t\t\t\t\t<td>{$author}</td>\n\t\t\t\t</tr>";
}
}
$debtot = sprint($debtot);
$credtot = sprint($credtot);
$OUTPUT .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='4'><b>Total</b></td>\n\t\t\t<td nowrap><b>" . CUR . " {$debtot}</b></td>\n\t\t\t<td nowrap><b>" . CUR . " {$credtot}</b></td>\n\t\t\t<td> </td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<td> </td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<td align='center' colspan='10'>\n\t\t\t\t<form action='../xls/acc-trans-xls.php' method='POST' name='form'>\n\t\t\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t\t\t{$hide}\n\t\t\t\t\t<input type='submit' name='xls' value='Export to spreadsheet'>\n\t\t\t\t</form>\n\t\t\t</td>\n\t\t</tr>\n\t\t</table>\n\t\t</form>\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t<tr>\n\t\t\t\t<td> </td>\n\t\t\t</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-journal.php'>Current Year Details General Ledger 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 $OUTPUT;
}
function export()
{
$OUT = clean_html(viewtran($_POST));
require_lib("xls");
StreamXLS("year_review_ledger", $OUT);
}
function export_data($_POST)
{
extract($_POST);
require_lib("xls");
$data = clean_html(viewtran($_POST));
StreamXLS("suppledger", "{$data}");
}
function viewtrans($_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 name.");
$v->isOk($type, "string", 1, 3, "Invalid Account type.");
$v->isOk($tab, "string", 1, 50, "Invalid Account type.");
$v->isOk($catid, "string", 1, 20, "Invalid Category.");
# 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 Category Name
$cats = get("core", "catname", $tab, "catid", $catid);
$cat = pg_fetch_array($cats);
// Set up table to display in
$OUTPUT = "\n\t\t\t<table>\n\t\t\t\t<tr>\n\t\t\t\t\t<th colspan='5'><h3>Journal Entries for Category : {$cat['catname']}</h3></th>\n\t\t\t\t</tr>\n\t\t\t\t<tr><th colspan='5'></th></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='4' align='left'>" . COMP_NAME . "</td>\n\t\t\t\t\t<td colspan='2' align='right'>" . date("Y-m-d") . "</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th><u>Date</u></th>\n\t\t\t\t\t<th><u>Debit</u></th>\n\t\t\t\t\t<th><u>Credit</u></th>\n\t\t\t\t\t<th><u>Amount</u></th>\n\t\t\t\t\t<th><u>Person Who Authorized</u></th>\n\t\t\t\t</tr>";
# get accounts
core_connect();
$type = strtoupper($type);
$sql = "SELECT * FROM accounts WHERE catid='{$catid}' AND div = '" . USER_DIV . "'";
$accRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve account details from database.", SELF);
$numrows = pg_numrows($accRslt);
while ($acc = pg_fetch_array($accRslt)) {
$OUTPUT .= viewtran($_POST, $acc['accid']);
}
$OUTPUT .= "</table>";
# Send the stream
include "temp.xls.php";
Stream("AllTrans-{$cat['catname']}", $OUTPUT);
}
function excel()
{
$OUTPUT = clean_html(viewtran($_POST, true));
require_lib("xls");
StreamXLS("Debtors Ledger", $OUTPUT);
}
