function bal($id)
{
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($id, "num", 1, 20, "Invalid Cash Flow Statement number.");
# display errors, if any
if ($v->isError()) {
$theseErrors = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$theseErrors .= "<li class=err>" . $e["msg"];
}
$theseErrors .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $theseErrors;
}
# connect to core DB
core_connect();
# get the Trial Balance
$cf = new dbSelect("save_cashflow", "core", grp(m("cols", "output, date_trunc('day', gentime) as gentime"), m("where", "id='{$id}'")));
$cf->run();
if ($cf->num_rows() < 1) {
return "<center><li> Invalid Cash Flow Statement Number.</li></center>";
}
$stmnt = $cf->fetch_array();
$OUTPUT = base64_decode($stmnt['output']);
if (isset($_GET["xls"])) {
$cftime = preg_replace("/ 00:00.*/", "", $stmnt["gentime"]);
require_lib("xls");
Stream("cashflow-{$cftime}", $OUTPUT);
} else {
require "../tmpl-print.php";
}
}
function AssetLedg()
{
# Set up table to display in
$Assets = "\r\n\t<h3>Asset Ledger</h3>\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\r\n\t<tr><th>Group</th><th>Serial</th><th>Location</th><th>Description</th><th>Date Bought</th><th>Date Added</th><th>Cost Amount</th><th>Net Value</th></tr>";
db_connect();
$i = 0;
$tot = 0;
$totnet = 0;
$Sl = "SELECT * FROM assets WHERE div = '" . USER_DIV . "' ORDER BY serial";
$Rs = db_exec($Sl) or errDie("Unable to retrieve Asset Ledger from database.");
if (pg_numrows($Rs) < 1) {
return "<li>There are no Assets recorded on Cubit.";
}
while ($Led = pg_fetch_array($Rs)) {
$netval = sprint($Led['amount'] - $Led['accdep']);
$Led['amount'] = sprint($Led['amount']);
# Get group
db_connect();
$sql = "SELECT * FROM assetgrp WHERE grpid = '{$Led['grpid']}' AND div = '" . USER_DIV . "'";
$grpRslt = db_exec($sql);
$grp = pg_fetch_array($grpRslt);
$tot = $tot + $Led['amount'];
$totnet = $totnet + $netval;
$Assets .= "<tr><td>{$grp['grpname']}</td><td>{$Led['serial']}</td><td>{$Led['locat']}</td><td>{$Led['des']}</td><td>{$Led['bdate']}</td><td>{$Led['date']}</td><td align=right>" . CUR . " {$Led['amount']}</td><td align=right>" . CUR . " {$netval}</td></tr>";
$i++;
}
$tot = sprint($tot);
$totnet = sprint($totnet);
$Assets .= "<tr><td colspan=6>Total Assets: {$i} </td><td align=right>" . CUR . " {$tot}</td><td align=right>" . CUR . " {$totnet}</td></tr>";
$Assets .= "</table>";
include "temp.xls.php";
Stream("Assets", $Assets);
return $Assets;
}
function bal($id)
{
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($id, "num", 1, 20, "Invalid Trial Balance number.");
# display errors, if any
if ($v->isError()) {
$theseErrors = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$theseErrors .= "<li class=err>" . $e["msg"];
}
$theseErrors .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $theseErrors;
}
# connect to core DB
core_connect();
# get the Trial Balance
$sql = "SELECT * FROM save_trial_bal WHERE id = '{$id}' AND div = '" . USER_DIV . "'";
$balRslt = db_exec($sql) or errDie("Unable to retrieve Trial Balance from the Database", SELF);
if (pg_numrows($balRslt) < 1) {
return "<center><li> Invalid Trial Balance Number.";
}
$bal = pg_fetch_array($balRslt);
$balance = base64_decode($bal['output']);
$OUTPUT = $balance;
include "temp.xls.php";
Stream("TB", $OUTPUT);
}
function cashbook($bankid)
{
$OUTPUT = printdep($bankid);
$OUTPUT .= "<br>";
$OUTPUT .= printrep($bankid);
# Send the stream
include "temp.xls.php";
Stream("Oustanding", $OUTPUT);
}
function edit($listid)
{
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($listid, "num", 1, 50, "Invalid Price List id.");
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>-" . $e["msg"] . "</li>";
}
return $confirm;
}
# Select Stock
db_conn("exten");
$sql = "SELECT * FROM pricelist WHERE listid = '{$listid}' AND div = '" . USER_DIV . "'";
$listRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($listRslt) < 1) {
return "<li> Invalid Price List ID.";
} else {
$list = pg_fetch_array($listRslt);
}
$vattype = getSetting("SELAMT_VAT") == 'inc' ? "Including Vat" : "Excluding Vat";
$enter = "\r\n\t\t<h3>Price List</h3>\r\n\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Field</th>\r\n\t\t\t\t<th>Value</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<td>Price list</td>\r\n\t\t\t\t<td align='center'>{$list['listname']}</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 colspan='2'><h3>Prices</h3><td>\r\n\t\t\t<tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Item</th>\r\n\t\t\t\t<th>Price Amount</th>\r\n\t\t\t</tr>";
# Query server
$i = 0;
db_conn('exten');
$sql = "SELECT * FROM plist_prices WHERE listid = '{$listid}' AND div = '" . USER_DIV . "' AND show='Yes' ORDER BY stkid ASC";
$stkpRslt = db_exec($sql) or errDie("Unable to retrieve stock items from database.");
if (pg_numrows($stkpRslt) < 1) {
return "<li class='err'> There are no stock item on the selected pricelist.</li>";
}
while ($stkp = pg_fetch_array($stkpRslt)) {
db_connect();
# get stock details
$sql = "SELECT stkid, stkcod, stkdes FROM stock WHERE stkid = '{$stkp['stkid']}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql) or errDie("Unable to retrieve stocks from database.");
$stk = pg_fetch_array($stkRslt);
$enter .= "\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td>{$stk['stkcod']} - " . extlib_rstr($stk['stkdes'], 30) . "</td>\r\n\t\t\t\t\t<td align='right'>" . CUR . " " . sprint($stkp['price']) . " {$vattype}</td>\r\n\t\t\t\t</tr>";
}
$enter .= "\r\n\t\t\t</table>";
$OUTPUT = $enter;
require "temp.xls.php";
Stream("Pricelist", $OUTPUT);
return $OUTPUT;
}
function printSupp()
{
# Set up table to display in
$printSupp = "\r\n\t\t<table>\r\n\t\t\t<tr><th colspan='3'><h3>Creditors Age Analysis</h3></th></tr>\r\n\t\t\t<tr><th></th></tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<th><u>Acc no.</u></th>\r\n\t\t\t\t<th><u>Suppliers</u></th>\r\n\t\t\t\t<th><u>Current</u></th>\r\n\t\t\t\t<th><u>30 days</u></th>\r\n\t\t\t\t<th><u>60 days</u></th>\r\n\t\t\t\t<th><u>90 days</u></th>\r\n\t\t\t\t<th><u>120 days</u></th>\r\n\t\t\t\t<th><u>Total Outstanding</u></th>\r\n\t\t\t</tr>";
# connect to database
db_connect();
# Query server
$i = 0;
$sql = "SELECT * FROM suppliers WHERE div = '" . USER_DIV . "' ORDER BY supname ASC";
$suppRslt = db_exec($sql) or errDie("Unable to retrieve Suppliers from database.");
if (pg_numrows($suppRslt) < 1) {
return "<li>There are no Suppliers in Cubit.</li>";
}
# totals
$totcurr = 0;
$tot30 = 0;
$tot60 = 0;
$tot90 = 0;
$tot120 = 0;
$alltot = 0;
while ($supp = pg_fetch_array($suppRslt)) {
# Get all ages
$curr = age($supp['supid'], 29);
$age30 = age($supp['supid'], 59);
$age60 = age($supp['supid'], 89);
$age90 = age($supp['supid'], 119);
$age120 = age($supp['supid'], 149);
# Suppliers total
$supptot = $curr + $age30 + $age60 + $age90 + $age120;
if ($supptot < $supp['balance']) {
$curr = sprint($curr + ($supp['balance'] - $supptot));
$supptot = sprint($supptot + $supp['balance'] - $supptot);
}
$printSupp .= "\r\n\t\t\t<tr>\r\n\t\t\t\t<td>{$supp['supno']}</td>\r\n\t\t\t\t<td>{$supp['supname']}</td>\r\n\t\t\t\t<td>" . CUR . " {$curr}</td>\r\n\t\t\t\t<td>" . CUR . " {$age30}</td>\r\n\t\t\t\t<td>" . CUR . " {$age60}</td>\r\n\t\t\t\t<td>" . CUR . " {$age90}</td>\r\n\t\t\t\t<td>" . CUR . " {$age120}</td>\r\n\t\t\t\t<td>" . CUR . " {$supptot}</td>\r\n\t\t\t</tr>";
# hold totals
$totcurr += $curr;
$tot30 += $age30;
$tot60 += $age60;
$tot90 += $age90;
$tot120 += $age120;
$alltot += $supptot;
$i++;
}
$printSupp .= "\r\n\t\t\t<tr><td><br></td></tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<td colspan='2'><b>Totals</b></td>\r\n\t\t\t\t<td><b>" . CUR . " {$totcurr}</b></td>\r\n\t\t\t\t<td><b>" . CUR . " {$tot30}</b></td>\r\n\t\t\t\t<td><b>" . CUR . " {$tot60}</b></td>\r\n\t\t\t\t<td><b>" . CUR . " {$tot90}</b></td>\r\n\t\t\t\t<td><b>" . CUR . " {$tot120}</b></td>\r\n\t\t\t\t<td><b>" . CUR . " {$alltot}</b></td>\r\n\t\t\t</tr>\r\n\t\t</table>";
# Send the stream
include "temp.xls.php";
Stream("CredAgeAnalysis", $printSupp);
}
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 details($_GET)
{
# Get vars
foreach ($_GET as $key => $value) {
${$key} = $value;
}
# Query server
db_connect();
$sql = "SELECT * FROM budgets WHERE budid = '{$budid}'";
$budRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
if (pg_numrows($budRslt) < 1) {
return "<li class=err> - Invalid Budget.";
}
$bud = pg_fetch_array($budRslt);
require "budget.lib.php";
$vbudtype = $TYPES[$bud['budtype']];
$vbudfor = $BUDFOR[$bud['budfor']];
$vfromyr = $YEARS[$bud['fromprd']];
$vtoyr = $YEARS[$bud['toprd']];
$bud['edate'] = ext_rdate($bud['edate']);
/* Toggle Options */
$list = "";
$totamt = 0;
db_connect();
# budget for
if ($bud['budfor'] == 'cost') {
$head = "<tr><th>Cost Centers</th>";
$sql = "SELECT DISTINCT id FROM buditems WHERE budid = '{$budid}'";
$bitRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
while ($bit = pg_fetch_array($bitRslt)) {
$ccRs = get("cubit", "*", "costcenters", "ccid", $bit['id']);
$cc = pg_fetch_array($ccRs);
$list .= "<tr><td>{$cc['centercode']} - {$cc['centername']}</td>";
db_connect();
$lstRs = db_exec("SELECT * FROM buditems WHERE id = '{$bit['id']}' AND budid = '{$budid}'");
while ($lst = pg_fetch_array($lstRs)) {
$list .= "<td align=right>" . CUR . " {$lst['amt']}</td>";
}
$list .= "</tr>";
}
} elseif ($bud['budfor'] == 'acc') {
$head = "<tr><th>Accounts</th>";
$sql = "SELECT DISTINCT id FROM buditems WHERE budid = '{$budid}'";
$bitRslt = db_exec($sql) or errDie("Unable to retrieve Budgets from database.");
while ($bit = pg_fetch_array($bitRslt)) {
$accRs = get("core", "*", "accounts", "accid", $bit['id']);
$acc = pg_fetch_array($accRs);
$list .= "<tr><td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>";
db_connect();
$lstRs = db_exec("SELECT * FROM buditems WHERE id = '{$bit['id']}' AND budid = '{$budid}'");
while ($lst = pg_fetch_array($lstRs)) {
$list .= "<td align=right>" . CUR . " {$lst['amt']}</td>";
}
$list .= "</tr>";
}
}
# Budget headings
if ($bud['fromprd'] < $bud['toprd']) {
for ($i = $bud['fromprd']; $i <= $bud['toprd']; $i++) {
$head .= "<th>{$YEARS[$i]}</th>";
}
} elseif ($bud['fromprd'] > $bud['toprd']) {
for ($i = $bud['fromprd']; $i < 10; $i++) {
$head .= "<th>{$YEARS[$i]}</th>";
}
for ($i = 0; $i <= $bud['toprd']; $i++) {
$head .= "<th>{$YEARS[$i]}</th>";
}
} else {
$i = $bud['toprd'];
$head .= "<th>{$YEARS[$i]}</th>";
}
$head .= "</tr>";
// $totamt = sprint($totamt);
// $list .= "<tr class='bg-even'><td><b>Total Budget Amount</b></td><td align=right><b>".CUR." $totamt</b></td></tr>";
/* End Toggle Options */
$details = "<center><h3> Yearly Budget Details </h3>\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\r\n\t<tr><th colspan=2>Details</th></tr>\r\n\t<tr><td>Budget Name</td><td>{$bud['budname']}</td></tr>\r\n\t<tr><td><br></td></tr>\r\n\t<tr><th colspan=2>Options</th></tr>\r\n\t<tr><td>Budget For</td><td>{$vbudfor}</td>\r\n\t<tr><td>Budget Type</td><td>{$vbudtype}</td>\r\n\t<tr><td>Budget Year</td><td>{$vfromyr} to {$vtoyr}</td>\r\n\t<tr><td><br></td></tr>\r\n\t</table>\r\n\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\r\n\t{$head}\r\n\t{$list}\r\n\t</table>";
include "../xls/temp.xls.php";
Stream("Budget.xls", $details);
}
function viewtran($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($fprd, "string", 1, 14, "Invalid Starting Period number.");
$v->isOk($tprd, "string", 1, 14, "Invalid Ending Period number.");
if (isset($details)) {
$v->isOk($accid, "string", 1, 20, "Invalid Account number.");
} else {
$v->isOk($topacc, "num", 1, 20, "Invalid Account number.");
$v->isOk($accnum, "num", 0, 20, "Invalid Account number.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# dates drop downs
$months = array("1" => "January", "2" => "February", "3" => "March", "4" => "April", "5" => "May", "6" => "June", "7" => "July", "8" => "August", "9" => "September", "10" => "October", "11" => "November", "12" => "December");
if ($tprd < $fprd) {
return "<li class='err'> Invalid Period range : {$months[$fprd]} to {$months[$tprd]}";
}
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";
}
$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";
}
$acc = pg_fetch_array($accRs);
}
}
// 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='7'><h3>Journal Entries for Account : {$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</h3></th>\n\t\t\t\t</tr>\n\t\t\t\t<tr><th colspan='7'></th></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th><u>Period</u></th>\n\t\t\t\t\t<th><u>Date</u></th>\n\t\t\t\t\t<th><u>Reference</u></th>\n\t\t\t\t\t<th><u>Contra Acc</u></th>\n\t\t\t\t\t<th><u>Description</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>User</u></th>\n\t\t\t\t</tr>";
# counts
$credtot = 0;
$debtot = 0;
# Get Transactions
for ($i = $fprd; $i <= $tprd; $i++) {
db_conn($i);
$sql = "SELECT * FROM transect WHERE debit = '{$acc['accid']}' AND div = '" . USER_DIV . "' OR credit = '{$acc['accid']}' AND div = '" . USER_DIV . "'";
$tranRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve Transaction details from database.", SELF);
if (pg_numrows($tranRslt) < 1) {
continue;
} else {
# display all transactions
while ($tran = pg_fetch_array($tranRslt)) {
#get vars from tran as the are in db
foreach ($tran as $key => $value) {
${$key} = $value;
}
if ($debit == $acc['accid']) {
$cacc = $credit;
$debitamt = "R " . $amount;
$debtot += $amount;
$creditamt = "";
} else {
$debitamt = "";
$creditamt = "R " . $amount;
$credtot += $amount;
$cacc = $debit;
}
# 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\t\t<tr>\n\t\t\t\t\t\t\t<td>{$months[$i]}</td>\n\t\t\t\t\t\t\t<td>{$date}</td>\n\t\t\t\t\t\t\t<td>{$refnum}</td>\n\t\t\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\t\t\t\t\t\t\t<td>{$details}</td>\n\t\t\t\t\t\t\t<td>{$debitamt}</td>\n\t\t\t\t\t\t\t<td>{$creditamt}</td>\n\t\t\t\t\t\t\t<td>{$author}</td>\n\t\t\t\t\t\t</tr>";
}
}
}
$OUTPUT .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='5'><b>Total</b></td>\n\t\t\t\t\t<td><b>" . CUR . " {$debtot}</b></td>\n\t\t\t\t\t<td><b>" . CUR . " {$credtot}</b></td>\n\t\t\t\t\t<td></td>\n\t\t\t\t</tr>\n\t\t\t</table>";
$acc['accname'] = str_replace(" ", "", $acc['accname']);
# Send the stream
include "temp.xls.php";
Stream("AllTrans-{$acc['accname']}", $OUTPUT);
}
function export($_POST)
{
extract($_POST);
$date = $year . "-" . $mon . "-" . $day;
$tdate = $tyear . "-" . $tmon . "-" . $tday;
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cid, "string", 1, 50, "Invalid cid.");
if (!checkdate($mon, $day, $year)) {
$v->isOk($date, "num", 1, 1, "Invalid from date.");
}
if (!checkdate($tmon, $tday, $tyear)) {
$v->isOk($tdate, "num", 1, 1, "Invalid to date.");
}
# display errors, if any
if ($v->isError()) {
$err = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
db_conn('cubit');
if ($cid != "all") {
$whe = " AND cid='{$cid}'";
} else {
$whe = "";
}
if ($cid != "all") {
if ($report == "sum") {
$Sl = "SELECT sum(exl) AS exl,sum(vat) AS vat,sum(inc) AS inc FROM sj WHERE date>='{$date}' AND date<='{$tdate}' {$whe}";
$Ry = db_exec($Sl) or errDie("Unable to get sales journal.");
$data = pg_fetch_array($Ry);
$exl = sprint($data['exl']);
$vat = sprint($data['vat']);
$inc = sprint($data['inc']);
$out = "<tr><th>Description</th><th>Amount exl VAT</th><th>VAT</th><th>Amount inc VAT</th></tr>\n\t\t\t<tr><td>Total</td><td align=right>" . CUR . " {$exl}</td><td align=right>" . CUR . " {$vat}</td><td align=right>" . CUR . " {$inc}</td></tr>";
} else {
$Sl = "SELECT * FROM sj WHERE date>='{$date}' AND date<='{$tdate}' {$whe} ORDER BY id";
$Ry = db_exec($Sl) or errDie("Unable to get sales journal.");
$out = "<tr><th>Date</th><th>Description</th><th>Amount exl VAT</th><th>VAT</th><th>Amount inc VAT</th></tr>";
$i = 0;
$totexl = 0;
$totvat = 0;
$totinc = 0;
while ($vd = pg_fetch_array($Ry)) {
$out .= "<tr><td>{$vd['date']}</td><td>{$vd['des']}</td>\n\t\t\t\t<td align=right>" . CUR . " {$vd['exl']}</td><td align=right>" . CUR . " {$vd['vat']}</td><td align=right>" . CUR . " {$vd['inc']}</td></tr>";
$i++;
$totexl += $vd['exl'];
$totvat += $vd['vat'];
$totinc += $vd['inc'];
}
$totexl = sprint($totexl);
$totvat = sprint($totvat);
$totinc = sprint($totinc);
$out .= "<tr><td colspan=2>Total</td><td align=right>" . CUR . " {$totexl}</td><td align=right>" . CUR . " {$totvat}</td><td align=right>" . CUR . " {$totinc}</td></tr>";
}
} else {
if ($report == "sum") {
$Sl = "SELECT DISTINCT cid,name FROM sj ORDER BY name";
$Ri = db_exec($Sl) or errDie("Unable to get sales journal.");
$totexl = 0;
$totvat = 0;
$totinc = 0;
$out = "<tr><th>Customer</th><th>Amount exl VAT</th><th>VAT</th><th>Amount inc VAT</th></tr>";
$i = 1;
while ($vd = pg_fetch_array($Ri)) {
$Sl = "SELECT sum(exl) AS exl,sum(vat) AS vat, sum(inc) AS inc FROM sj WHERE date>='{$date}' AND date<='{$tdate}' {$whe} AND cid='{$vd['cid']}'";
$Ry = db_exec($Sl) or errDie("Unable to get sales journal.");
$data = pg_fetch_array($Ry);
$exl = sprint($data['exl']);
$vat = sprint($data['vat']);
$inc = sprint($data['inc']);
$totexl += $exl;
$totvat += $vat;
$totinc += $inc;
$out .= "<tr><td>{$vd['name']}</td><td align=right>" . CUR . " {$exl}</td><td align=right>" . CUR . " {$vat}</td><td align=right>" . CUR . " {$inc}</td></tr>";
$i++;
}
$totexl = sprint($totexl);
$totvat = sprint($totvat);
$totinc = sprint($totinc);
$out .= "<tr><td>Total</td><td align=right>" . CUR . " {$totexl}</td><td align=right>" . CUR . " {$totvat}</td><td align=right>" . CUR . " {$totinc}</td></tr>";
} else {
$Sl = "SELECT * FROM sj WHERE date>='{$date}' AND date<='{$tdate}' {$whe} ORDER BY id";
$Ry = db_exec($Sl) or errDie("Unable to get vat rec.");
$out = "<tr><th>Date</th><th>Description</th><th>Amount exl VAT</th><th>VAT</th><th>Amount inc VAT</th></tr>";
$i = 0;
$totexl = 0;
$totvat = 0;
$totinc = 0;
while ($vd = pg_fetch_array($Ry)) {
$exl = sprint($vd['exl']);
$vat = sprint($vd['vat']);
$inc = sprint($vd['inc']);
$out .= "<tr><td>{$vd['date']}</td><td>{$vd['des']}</td>\n\t\t\t\t<td align=right>" . CUR . " {$exl}</td><td align=right>" . CUR . " {$vat}</td><td align=right>" . CUR . " {$inc}</td></tr>";
$i++;
$totexl += $vd['exl'];
$totvat += $vd['vat'];
$totinc += $vd['inc'];
}
$totexl = sprint($totexl);
$totvat = sprint($totvat);
$totinc = sprint($totinc);
$out .= "<tr><td colspan=2>Total</td><td align=right>" . CUR . " {$totexl}</td><td align=right>" . CUR . " {$totvat}</td><td align=right>" . CUR . " {$totinc}</td></tr>";
}
}
$Report = "<h3>Sales Journal: {$date} TO {$tdate}</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t{$out}\n\t</table>";
$OUTPUT = $Report;
include "xls/temp.xls.php";
Stream("Report", $OUTPUT);
}
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);
}
#
#
#
#
#
#
#
#
#
# get settings
require "../settings.php";
require "../core-settings.php";
require "../libs/ext.lib.php";
$OUTPUT = sheet();
include "temp.xls.php";
Stream("TrialBalance", $OUTPUT);
exit;
# details
function sheet()
{
$rep = new grp_report();
$report = $rep->getReport();
$selected = $rep->selected;
$gdebit = $rep->totdebit;
$gcredit = $rep->totcredit;
$totdebit = 0;
$totcredit = 0;
$sql = "SELECT * FROM trial_bal WHERE div = '" . USER_DIV . "' order by topacc,accnum ASC";
$accRslt = db_exec($sql) or errDie("Unable to get group accounts information.");
while ($acc = pg_fetch_array($accRslt)) {
if (in_array($acc['accid'], $selected)) {
function export()
{
# Set up table to display in
$printStk = "\n\t\t<center>\n\t\t<h3>Available Stock</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Store</th>\n\t\t\t\t<th>Category</th>\n\t\t\t\t<th>Stock Code</th>\n\t\t\t\t<th>Stock Description</th>\n\t\t\t\t<th>Product class</th>\n\t\t\t\t<th>Available units</th>\n\t\t\t\t<th>Cost Amount</th>\n\t\t\t\t<th>Minimun Level</th>\n\t\t\t\t<th>Maximum Level</th>\n\t\t\t\t<th>Selling Price</th>\n\t\t\t</tr>";
# connect to database
db_connect();
# Query server
$i = 0;
$tot_avail = 0;
$tot_cost = 0;
$tot_sell = 0;
$sql = "SELECT * FROM stock WHERE units > 0 AND div = '" . USER_DIV . "' ORDER BY stkdes ASC";
$stkRslt = db_exec($sql) or errDie("Unable to retrieve stocks from database.");
if (pg_numrows($stkRslt) < 1) {
return "\n\t\t\t<li>There are no available stock found in Cubit.\n\t\t\t<p>\n\t\t\t<table " . TMPL_tblDflts . " width='15%'>\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='" . bg_class() . "'>\n\t\t\t\t\t<td><a href='main.php'>Main Menu</a></td>\n\t\t\t\t</tr>\n\t\t\t</table>";
}
while ($stk = pg_fetch_array($stkRslt)) {
$stk['units'] = $stk['units'] - $stk['alloc'];
# alternate bgcolor
# get warehouse
db_conn("exten");
$sql = "SELECT whname FROM warehouses WHERE whid = '{$stk['whid']}'";
$whRslt = db_exec($sql);
$wh = pg_fetch_array($whRslt);
$printStk .= "\n\t\t\t<tr>\n\t\t\t\t<td>{$wh['whname']}</td>\n\t\t\t\t<td>{$stk['catname']}</td>\n\t\t\t\t<td>{$stk['stkcod']}</td>\n\t\t\t\t<td align='center'>{$stk['stkdes']}</td>\n\t\t\t\t<td>{$stk['prdcls']}</td>\n\t\t\t\t<td align='right'>" . sprint3($stk['units']) . "</td>\n\t\t\t\t<td align='right'>" . CUR . " " . sprint($stk['csamt']) . "</td>\n\t\t\t\t<td align='right'>{$stk['minlvl']}</td>\n\t\t\t\t<td align='right'>{$stk['maxlvl']}</td>\n\t\t\t\t<td align='left'>" . CUR . " " . sprint($stk['selamt'] * $stk['units']) . " (" . CUR . " {$stk['selamt']} each)</td>\n\t\t\t</tr>";
#handle totals
if ($stk['units'] > 0) {
$tot_avail += $stk['units'];
}
if ($stk['csamt'] > 0) {
$tot_cost += $stk['csamt'];
}
if ($stk['selamt'] > 0) {
$tot_sell += $stk['selamt'] * $stk['units'];
}
}
$printStk .= "\n\t\t<tr>\n\t\t\t<td colspan='5'>Totals:</td>\n\t\t\t<td align='right'>" . sprint3($tot_avail) . "</td>\n\t\t\t<td align='right'>" . CUR . " " . sprint($tot_cost) . "</td>\n\t\t\t<td colspan='2'></td>\n\t\t\t<td align='left'>" . CUR . " " . sprint($tot_sell) . "</td>\n\t\t</tr>\n\t\t</table>";
$OUTPUT = $printStk;
include "xls/temp.xls.php";
Stream("Report", $OUTPUT);
return $printStk;
}
function report($_POST)
{
extract($_POST);
$date = $year . "-" . $mon . "-" . $day;
$amount += 0;
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($user, "string", 1, 50, "Invalid user.");
$v->isOk($amount, "float", 1, 10, "Invalid amount.");
if (!checkdate($mon, $day, $year)) {
$v->isOk($date, "num", 1, 1, "Invalid order date.");
}
$met = remval($met);
# display errors, if any
if ($v->isError()) {
$err = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class=err>{$e['msg']}</li>";
}
return $confirm;
}
if ($user != "0") {
$whe = " AND by='{$user}'";
} else {
$whe = "";
}
if ($met != "all") {
$whe .= " AND method='{$met}'";
} else {
$whe .= "";
}
db_conn("cubit");
$sql = "SELECT * FROM payrec WHERE date='{$date}' {$whe}";
$rslt = db_exec($sql) or errDie("Unable to retrieve pos report from Cubit.");
$cash = $cheque = $credit_card = $credit = $sales = 0;
while ($rec_data = pg_fetch_array($rslt)) {
switch (strtolower($rec_data["method"])) {
case "cash":
$cash += $rec_data["amount"];
break;
case "cheque":
$cheque += $rec_data["amount"];
break;
case "credit card":
$credit_card += $rec_data["amount"];
break;
case "credit":
$credit += $rec_data["amount"];
break;
}
$sales += $rec_data["amount"];
}
db_conn('cubit');
$Sl = "SELECT sum(amount) FROM payrec WHERE date='{$date}' {$whe}";
$Ry = db_exec($Sl) or errDie("Unable to get pos rec.");
$data = pg_fetch_array($Ry);
$amount = sprint($amount);
$expected = sprint($amount + $sales);
$Report = "<h3>POS Report: {$date}</h3>\n\t<table " . TMPL_tblDflts . " style='width: 100%'>\n\t<tr>\n\t\t<th colspan=2>Report</th>\n\t</tr>\n\t<tr class='bg-odd'>\n\t\t<td>Starting Amount</td>\n\t\t<td align='right'>" . CUR . " {$amount}</td>\n\t</tr>\n\t<tr class='bg-even'>\n\t\t<td>Cash</td>\n\t\t<td align='right'>" . sprint($cash) . "</td>\n\t</tr>\n\t<tr class='bg-odd'>\n\t\t<td>Cheque</td>\n\t\t<td align='right'>" . sprint($cheque) . "</td>\n\t</tr>\n\t<tr class='bg-even'>\n\t\t<td>Credit Card</td>\n\t\t<td align='right'>" . sprint($credit_card) . "</td>\n\t</tr>\n\t<tr class='bg-odd'>\n\t\t<td>Credit</td>\n\t\t<td align='right'>" . sprint($credit) . "</td>\n\t</td>\n\t<tr class='bg-even'>\n\t\t<td>Expected Amount</td>\n\t\t<td align='right'>" . CUR . " {$expected}</td>\n\t</tr>\n\t</table>";
include "temp.xls.php";
Stream("Report", $Report);
return $Report;
}
function export($_POST)
{
# Get vars
extract($_POST);
# Validate input
require_lib("validate");
$v = new validate();
$v->isOk($fday, "num", 1, 2, "Invalid from Date day.");
$v->isOk($fmon, "num", 1, 2, "Invalid from Date month.");
$v->isOk($fyear, "num", 1, 4, "Invalid from Date Year.");
$v->isOk($today, "num", 1, 2, "Invalid to Date day.");
$v->isOk($tomon, "num", 1, 2, "Invalid to Date month.");
$v->isOk($toyear, "num", 1, 4, "Invalid to Date Year.");
# mix dates
$fromdate = $fyear . "-" . $fmon . "-" . $fday;
$todate = $toyear . "-" . $tomon . "-" . $today;
if (!checkdate($fmon, $fday, $fyear)) {
$v->isOk($fromdate, "num", 1, 1, "Invalid from date.");
}
if (!checkdate($tomon, $today, $toyear)) {
$v->isOk($todate, "num", 1, 1, "Invalid to date.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>-" . $e["msg"] . "<br>";
}
return $confirm;
}
# Set up table to display in
$printOrd = "\n\t\t\t\t\t<center>\n\t\t\t\t\t<h3>Returned International Stock Orders</h3>\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<th>Purchase No.</th>\n\t\t\t\t\t\t\t<th>Return Date</th>\n\t\t\t\t\t\t\t<th>Supplier</th>\n\t\t\t\t\t\t\t<th>Total Cost Returned</th>\n\t\t\t\t\t\t</tr>";
# Connect to database
db_conn($prd);
# Query server
$i = 0;
$tot = 0;
$sql = "SELECT * FROM purchint_ret WHERE rdate >= '{$fromdate}' AND rdate <= '{$todate}' AND div = '" . USER_DIV . "' ORDER BY rdate DESC";
$stkpRslt = db_exec($sql) or errDie("Unable to retrieve stock Order from database.");
if (pg_numrows($stkpRslt) < 1) {
return "\n\t\t\t\t\t<li> There are no Returned Stock Orders found.</li>\n\t\t\t\t\t<p>\n\t\t\t\t\t<table border='0' cellpadding='2' cellspacing='1'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Quick Links</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><a href='purchase-new.php'>New Purchase</a></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><a href='purchase-view.php'>View Purchases</a></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t\t\t</table>";
}
while ($stkp = pg_fetch_array($stkpRslt)) {
# date format
$date = explode("-", $stkp['rdate']);
$date = $date[2] . "-" . $date[1] . "-" . $date[0];
$tot += $stkp['subtot'];
$printOrd .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td>{$stkp['purnum']}</td>\n\t\t\t\t\t\t\t<td>{$date}</td>\n\t\t\t\t\t\t\t<td>{$stkp['supname']}</td>\n\t\t\t\t\t\t\t<td align='right'>" . CUR . " {$stkp['subtot']}</td>\n\t\t\t\t\t\t</tr>";
$i++;
}
$tot = sprint($tot);
$printOrd .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='3'>Totals</td>\n\t\t\t\t\t\t<td align='right'>" . CUR . " {$tot}</td>\n\t\t\t\t\t\t<td><br></td>\n\t\t\t\t\t</tr>\n\t</table>";
include "xls/temp.xls.php";
Stream("Purchases", $printOrd);
return $printOrd;
}
function export($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($fday, "num", 1, 2, "Invalid from Date day.");
$v->isOk($fmon, "num", 1, 2, "Invalid from Date month.");
$v->isOk($fyear, "num", 1, 4, "Invalid from Date Year.");
$v->isOk($today, "num", 1, 2, "Invalid to Date day.");
$v->isOk($tomon, "num", 1, 2, "Invalid to Date month.");
$v->isOk($toyear, "num", 1, 4, "Invalid to Date Year.");
# mix dates
$fromdate = $fyear . "-" . $fmon . "-" . $fday;
$todate = $toyear . "-" . $tomon . "-" . $today;
if (!checkdate($fmon, $fday, $fyear)) {
$v->isOk($fromdate, "num", 1, 1, "Invalid from date.");
}
if (!checkdate($tomon, $today, $toyear)) {
$v->isOk($todate, "num", 1, 1, "Invalid to date.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>{$e['msg']}</li>";
}
return $confirm;
}
# Set up table to display in
$printOrd = "\n\t\t<center>\n\t\t<h3>View Stock Orders</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>No.</th>\n\t\t\t\t<th>Order No.</th>\n\t\t\t\t<th>Supp Inv No.</th>\n\t\t\t\t<th>Order Date</th>\n\t\t\t\t<th>Supplier</th>\n\t\t\t\t<th>Sub Total</th>\n\t\t\t\t<th>Delivery Charges</th>\n\t\t\t\t<th>Vat</th>\n\t\t\t\t<th>Total</th>\n\t\t\t</tr>";
# connect to database
db_connect();
# Query server
$i = 0;
$tot1 = 0;
$tot2 = 0;
$tot3 = 0;
$tot4 = 0;
$sql = "SELECT * FROM purchases WHERE pdate >= '{$fromdate}' AND pdate <= '{$todate}' AND div = '" . USER_DIV . "' ORDER BY pdate DESC";
$stkpRslt = db_exec($sql) or errDie("Unable to retrieve stock purchases from database.");
if (pg_numrows($stkpRslt) < 1) {
return "\n\t\t\t<li> There are no stock Orders found.</li>" . mkQuickLinks(ql("purchase-new.php", "New Order"), ql("stock-view.php", "View Stock"));
} else {
while ($stkp = pg_fetch_array($stkpRslt)) {
# Date format
$date = explode("-", $stkp['pdate']);
$date = $date[2] . "-" . $date[1] . "-" . $date[0];
# Calculate the Sub-Total
$stkp['total'] = sprint($stkp['total']);
$stkp['shipchrg'] = sprint($stkp['shipping']);
$subtot = $stkp['subtot'];
$subtot = sprint($subtot);
$vat = sprint($stkp['vat']);
$tot1 = sprint($tot1 + $subtot);
$tot2 = sprint($tot2 + $stkp['shipchrg']);
$tot3 = sprint($tot3 + $stkp['total']);
$tot4 = sprint($tot4 + $vat);
# Get documents
$docs = "";
$printOrd .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$stkp['purnum']}</td>\n\t\t\t\t\t<td>{$stkp['ordernum']}</td>\n\t\t\t\t\t<td>{$stkp['supinv']}</td>\n\t\t\t\t\t<td>{$date}</td>\n\t\t\t\t\t<td>{$stkp['supname']}</td>\n\t\t\t\t\t<td align='right'>" . CUR . " {$subtot}</td>\n\t\t\t\t\t<td align='right'>" . CUR . " {$stkp['shipchrg']}</td>\n\t\t\t\t\t<td align='right'>" . CUR . " {$vat}</td>\n\t\t\t\t\t<td align='right'>" . CUR . " {$stkp['total']}</td>";
$edit = "purchase-new.php";
$recv = "purch-recv.php";
$complt = "<a href='purch-complete.php?purid={$stkp['purid']}'>Complete</a>";
$recinv = "<a href='purch-recinvcd.php?purid={$stkp['purid']}'>Record Invoice</a>";
if ($stkp['invcd'] == 'y') {
$recinv = "Invoice Recorded";
}
if ($stkp['cash'] == 'y') {
$edit = "purchase-new-cash.php";
$recv = "purch-recv-cash.php";
$complt = "<br>";
$recinv = "";
}
if ($stkp['received'] != "y" && $subtot == 0) {
/*
$printOrd .= "<td><a href='$edit?purid=$stkp[purid]&cont=1'>Edit</a></td>
<td><br></td>
<td><a href='purch-cancel.php?purid=$stkp[purid]'>Cancel</a></td>
</tr>";
*/
} elseif ($stkp['received'] != "y") {
if ($stkp['edit'] != 1 && $stkp['apprv'] != 'y' && $stkp['invcd'] != 'y') {
/*
$printOrd .= "<td><a href='$edit?purid=$stkp[purid]&cont=1'>Edit</a></td>
<td><a href='purch-apprv.php?purid=$stkp[purid]'>Approve</a></td>
<td><a href='purch-cancel.php?purid=$stkp[purid]'>Cancel</a></td>
</tr>";
*/
} elseif ($stkp['edit'] != 1 && $stkp['apprv'] == 'y') {
if (getSetting("PURCH_APPRV") == 'napprv' && $stkp['invcd'] != 'y') {
/*
$printOrd .= "<td><a href='$edit?purid=$stkp[purid]&cont=1'>Edit</a></td>
<td><a href='purch-cancel.php?purid=$stkp[purid]'>Cancel</a></td>";
*/
} else {
//$printOrd .= "<td><a href='$edit?purid=$stkp[purid]&cont=1'>Edit</a></td>";
}
if ($stkp['rsubtot'] > 0) {
$rec = "";
} elseif ($stkp['iamount'] > 0) {
$rec = "";
} else {
$rec = "<a href='{$recv}?purid={$stkp['purid']}&invoice=no'>Receive & Record Invoice</a>";
}
/*
$printOrd .= "<td><a href='$recv?purid=$stkp[purid]'>Received</a></td>
<td>$recinv</td>
<td>$rec</td>
</tr>";
*/
} else {
/*
$printOrd .= "<td><a href='$recv?purid=$stkp[purid]'>Received</a></td>
<td>$recinv</td>
<td>$complt</td>
</tr>";
*/
}
} else {
if ($stkp['invcd'] != 'y') {
//$printOrd .= "<td colspan=3>$recinv</td></tr>";
} else {
//$printOrd .= "<td colspan=3><br></td></tr>";
}
}
$i++;
}
}
$printOrd .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='5'>Totals</td>\n\t\t\t\t<td align='right'>" . CUR . " {$tot1}</td>\n\t\t\t\t<td align='right'>" . CUR . " {$tot2}</td>\n\t\t\t\t<td align='right'>" . CUR . " {$tot4}</td>\n\t\t\t\t<td align='right'>" . CUR . " {$tot3}</td>\n\t\t\t</tr>\n\t\t</table>";
$OUTPUT = $printOrd;
include "xls/temp.xls.php";
Stream("Purchases", $OUTPUT);
return $printOrd;
}
function report($_POST)
{
extract($_POST);
$date = $year . "-" . $mon . "-" . $day;
$tdate = $tyear . "-" . $tmon . "-" . $tday;
# validate input
require_lib("validate");
$v = new validate();
if (!checkdate($mon, $day, $year)) {
$v->isOk($date, "num", 1, 1, "Invalid order date.");
}
if (!checkdate($tmon, $tday, $tyear)) {
$v->isOk($tdate, "num", 1, 1, "Invalid order date.");
}
# display errors, if any
if ($v->isError()) {
$err = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes ORDER BY code";
$Ri = db_exec($Sl) or errDie("Unabel to get data.");
$total1 = 0;
$totvat1 = 0;
$out = "\n\t\t<tr>\n\t\t\t<th colspan='5'>OUTPUT</th>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Code</th>\n\t\t\t<th>VAT Code Name</th>\n\t\t\t<th>Tax %</th>\n\t\t\t<th>Base Amount</th>\n\t\t\t<th>Tax Amount</th>\n\t\t</tr>";
$i = 1;
while ($vd = pg_fetch_array($Ri)) {
$Sl = "SELECT sum(amount) AS amount,sum(vat) AS vat FROM vatreport WHERE date>='{$date}' AND date<='{$tdate}' AND type = 'OUTPUT' AND cid='{$vd['id']}'";
$Ry = db_exec($Sl) or errDie("Unable to get vat rec.");
$data = pg_fetch_array($Ry);
$amount = sprint($data['amount']);
$vat = sprint($data['vat']);
$total1 += $amount;
$totvat1 += $vat;
$out .= "\n\t\t\t<tr>\n\t\t\t\t<td>{$vd['code']}</td>\n\t\t\t\t<td>{$vd['description']}</td>\n\t\t\t\t<td>{$vd['vat_amount']}</td>\n\t\t\t\t<td align='right'>" . CUR . " {$amount}</td>\n\t\t\t\t<td align='right'>" . CUR . " {$vat}</td>\n\t\t\t</tr>";
$i++;
}
$total1 = sprint($total1);
$totvat1 = sprint($totvat1);
$out .= "\n\t\t<tr>\n\t\t\t<td colspan='2' align='right'>Total:</td>\n\t\t\t<td></td>\n\t\t\t<td align='right'>" . CUR . " {$total1}</td>\n\t\t\t<td align='right'>" . CUR . " {$totvat1}</td>\n\t\t</tr>";
$total2 = 0;
$totvat2 = 0;
$out .= "\n\t\t" . TBL_BR . "\n\t\t" . TBL_BR . "\n\t\t<tr>\n\t\t\t<th colspan='5'>INPUT</th>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Code</th>\n\t\t\t<th>VAT Code Name</th>\n\t\t\t<th>Tax %</th>\n\t\t\t<th>Base Amount</th>\n\t\t\t<th>Tax Amount</th>\n\t\t</tr>";
$i = 1;
$Sl = "SELECT * FROM vatcodes ORDER BY code";
$Ri = db_exec($Sl) or errDie("Unabel to get data.");
while ($vd = pg_fetch_array($Ri)) {
$Sl = "SELECT sum(amount) AS amount,sum(vat) AS vat FROM vatreport WHERE date>='{$date}' AND date<='{$tdate}' AND type = 'INPUT' AND cid='{$vd['id']}'";
$Ry = db_exec($Sl) or errDie("Unable to get vat rec.");
$data = pg_fetch_array($Ry);
$amount = sprint($data['amount']);
$vat = sprint($data['vat']);
$total2 += $amount;
$totvat2 += $vat;
$out .= "\n\t\t\t<tr>\n\t\t\t\t<td>{$vd['code']}</td>\n\t\t\t\t<td>{$vd['description']}</td>\n\t\t\t\t<td>{$vd['vat_amount']}</td>\n\t\t\t\t<td align='right'>" . CUR . " {$amount}</td>\n\t\t\t\t<td align='right'>" . CUR . " {$vat}</td>\n\t\t\t</tr>";
$i++;
}
$total2 = sprint($total2);
$totvat2 = sprint($totvat2);
$out .= "\n\t\t<tr>\n\t\t\t<td colspan='2' align='right'>Total:</td>\n\t\t\t<td></td>\n\t\t\t<td align='right'>" . CUR . " {$total2}</td>\n\t\t\t<td align='right'>" . CUR . " {$totvat2}</td>\n\t\t</tr>";
$out .= "\n\t\t<tr><td><br></td></tr>\n\t\t<tr>\n\t\t\t<td colspan='2' align='right'>Total:</td>\n\t\t\t<td></td>\n\t\t\t<td>" . CUR . " " . ($total1 - abs($total2)) . "</td>\n\t\t\t<td>" . CUR . " " . ($totvat1 - abs($totvat2)) . "</td>\n\t\t</tr>";
$Report = "\n\t\t<h3>VAT Report: {$date} TO {$tdate}</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t{$out}\n\t\t</table>";
include "temp.xls.php";
Stream("VAT_REPORT", $Report);
return $Report;
}
function report($_POST)
{
extract($_POST);
$date = $year . "-" . $mon . "-" . $day;
$tdate = $tyear . "-" . $tmon . "-" . $tday;
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cid, "num", 1, 50, "Invalid id.");
if (!checkdate($mon, $day, $year)) {
$v->isOk($date, "num", 1, 1, "Invalid order date.");
}
if (!checkdate($tmon, $tday, $tyear)) {
$v->isOk($tdate, "num", 1, 1, "Invalid order date.");
}
# display errors, if any
if ($v->isError()) {
$err = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
db_conn('cubit');
if ($cid != "0") {
$whe = " AND cid='{$cid}'";
} else {
$whe = "";
}
if ($type != "0") {
$whe = " AND type='{$type}'";
}
if ($cid != "0") {
if ($report == "sum") {
$Sl = "SELECT sum(amount) AS amount,sum(vat) AS vat FROM vatreport WHERE date>='{$date}' AND date<='{$tdate}' {$whe}";
$Ry = db_exec($Sl) or errDie("Unable to get vat rec.");
$data = pg_fetch_array($Ry);
$amount = sprint($data['amount']);
$vat = sprint($data['vat']);
$out = "<tr><th>Description</th><th>Amount inc VAT</th><th>VAT</th></tr>\n\t\t\t<tr><td>Total</td><td align=right>" . CUR . " {$amount}</td><td align=right>" . CUR . " {$vat}</td></tr>";
} else {
$Sl = "SELECT * FROM vatreport WHERE date>='{$date}' AND date<='{$tdate}' {$whe}";
$Ry = db_exec($Sl) or errDie("Unable to get vat rec.");
$out = "<tr><th>Date</th><th>Code</th><th>Ref</th><th>Description</th><th>Amount inc VAT</th><th>VAT</th></tr>";
$i = 0;
$total = 0;
$totvat = 0;
while ($vd = pg_fetch_array($Ry)) {
//$amount=sprint($data['amount']);
//$vat=sprint($data['vat']);
$out .= "<tr><td>{$vd['date']}</td><td>{$vd['code']}</td><td>{$vd['ref']}</td><td>{$vd['description']}</td>\n\t\t\t\t<td align=right>" . CUR . " {$vd['amount']}</td><td align=right>" . CUR . " {$vd['vat']}</td></tr>";
$i++;
$total += $vd['amount'];
$totvat += $vd['vat'];
}
$total = sprint($total);
$totvat = sprint($totvat);
$out .= "<tr><td colspan=4>Total</td><td align=right>" . CUR . " {$total}</td><td align=right>" . CUR . " {$totvat}</td></tr>";
}
} else {
if ($report == "sum") {
$Sl = "SELECT * FROM vatcodes ORDER BY code";
$Ri = db_exec($Sl) or errDie("Unabel to get data.");
$total = 0;
$totvat = 0;
$out = "<tr><th>Description</th><th>Amount inc VAT</th><th>VAT</th></tr>";
$i = 1;
while ($vd = pg_fetch_array($Ri)) {
$Sl = "SELECT sum(amount) AS amount,sum(vat) AS vat FROM vatreport WHERE date>='{$date}' AND date<='{$tdate}' {$whe} AND cid='{$vd['id']}'";
$Ry = db_exec($Sl) or errDie("Unable to get vat rec.");
$data = pg_fetch_array($Ry);
$amount = sprint($data['amount']);
$vat = sprint($data['vat']);
$total += $amount;
$totvat += $vat;
$out .= "<tr><td>{$vd['code']}</td><td align=right>" . CUR . " {$amount}</td><td align=right>" . CUR . " {$vat}</td></tr>";
$i++;
}
$bgcolor = $i % 2 ? TMPL_tblDataColor1 : TMPL_tblDataColor2;
$total = sprint($total);
$totvat = sprint($totvat);
$out .= "<tr><td>Total</td><td align=right>" . CUR . " {$total}</td><td align=right>" . CUR . " {$totvat}</td></tr>";
} else {
$Sl = "SELECT * FROM vatreport WHERE date>='{$date}' AND date<='{$tdate}' {$whe}";
$Ry = db_exec($Sl) or errDie("Unable to get vat rec.");
$out = "<tr><th>Date</th><th>Code</th><th>Ref</th><th>Description</th><th>Amount inc VAT</th><th>VAT</th></tr>";
$i = 0;
$total = 0;
$totvat = 0;
while ($vd = pg_fetch_array($Ry)) {
$amount = sprint($vd['amount']);
$vat = sprint($vd['vat']);
$out .= "<tr><td>{$vd['date']}</td><td>{$vd['code']}</td><td>{$vd['ref']}</td><td>{$vd['description']}</td>\n\t\t\t\t<td align=right>" . CUR . " {$amount}</td><td align=right>" . CUR . " {$vat}</td></tr>";
$i++;
$total += $vd['amount'];
$totvat += $vd['vat'];
}
$total = sprint($total);
$totvat = sprint($totvat);
$out .= "<tr><td colspan=4>Total</td><td align=right>" . CUR . " {$total}</td><td align=right>" . CUR . " {$totvat}</td></tr>";
}
}
// <form action='xls/pos-report-user-xls.php' method=post name=form>
// <input type=hidden name=key value=report>
// <input type=hidden name=cid value='$cid'>
// <input type=hidden name=day value='$day'>
// <input type=hidden name=mon value='$mon'>
// <input type=hidden name=year value='$year'>
// <input type=submit name=xls value='Export to spreadsheet'>
// </form>
$Report = "<h3>VAT Report: {$date} TO {$tdate}</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\n\t{$out}\n\t</table>";
include "temp.xls.php";
Stream("VAT_REPORT", $Report);
return $Report;
}
function all($_POST)
{
extract($_POST);
# Validate input
require_lib("validate");
$v = new validate();
$v->isOk($fday, "num", 1, 2, "Invalid from Date day.");
$v->isOk($fmon, "num", 1, 2, "Invalid from Date month.");
$v->isOk($fyear, "num", 1, 4, "Invalid from Date Year.");
$v->isOk($today, "num", 1, 2, "Invalid to Date day.");
$v->isOk($tomon, "num", 1, 2, "Invalid to Date month.");
$v->isOk($toyear, "num", 1, 4, "Invalid to Date Year.");
# Mix dates
$fromdate = $fyear . "-" . $fmon . "-" . $fday;
$todate = $toyear . "-" . $tomon . "-" . $today;
if (!checkdate($fmon, $fday, $fyear)) {
$v->isOk($fromdate, "num", 1, 1, "Invalid from date.");
}
if (!checkdate($tomon, $today, $toyear)) {
$v->isOk($todate, "num", 1, 1, "Invalid to date.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>-" . $e["msg"] . "</li>";
}
return $confirm;
}
db_conn('cubit');
$Sl = "SELECT * FROM pr WHERE pdate>='{$fromdate}' AND pdate<='{$todate}' ORDER BY cust";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$out = "\n\t\t\t\t<h3>Pos Sales Report</h3>\n\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Customer</th>\n\t\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t\t<th>Inv</th>\n\t\t\t\t\t\t<th>Amount</th>\n\t\t\t\t\t</tr>";
$i = 0;
$tot = 0;
while ($pd = pg_fetch_array($Ri)) {
$out .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td>{$pd['cust']}</td>\n\t\t\t\t\t\t<td>{$pd['pdate']}</td>\n\t\t\t\t\t\t<td>{$pd['inv']}</td>\n\t\t\t\t\t\t<td align='right'>" . CUR . " {$pd['amount']}</td>\n\t\t\t\t\t</tr>";
$i++;
$tot += $pd['amount'];
}
$tot = sprint($tot);
$out .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td>Total</td>\n\t\t\t\t\t\t<td></td>\n\t\t\t\t\t\t<td></td>\n\t\t\t\t\t\t<td align='right'>" . CUR . " {$tot}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>";
include "temp.xls.php";
Stream("Report", $out);
return $out;
}
function export($_POST)
{
# get stock vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
//$v->isOk ($stkid, "num", 1, 50, "Invalid stock number.");
$v->isOk($fday, "num", 1, 2, "Invalid from Date day.");
$v->isOk($fmon, "num", 1, 2, "Invalid from Date month.");
$v->isOk($fyear, "num", 1, 4, "Invalid from Date Year.");
$v->isOk($today, "num", 1, 2, "Invalid to Date day.");
$v->isOk($tomon, "num", 1, 2, "Invalid to Date month.");
$v->isOk($toyear, "num", 1, 4, "Invalid to Date Year.");
# mix dates
$fromdate = $fyear . "-" . $fmon . "-" . $fday;
$todate = $toyear . "-" . $tomon . "-" . $today;
if (!checkdate($fmon, $fday, $fyear)) {
$v->isOk($fromdate, "num", 1, 1, "Invalid from date.");
}
if (!checkdate($tomon, $today, $toyear)) {
$v->isOk($todate, "num", 1, 1, "Invalid to date.");
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>-" . $e["msg"] . "</li>";
}
return $confirm;
}
if (in_array('all', $stkids)) {
$stkids = $alls;
}
$report = "<h3>Stock Sales Report</h3>";
foreach ($stkids as $stkid) {
$stkid += 0;
# Select Stock
db_connect();
$sql = "SELECT * FROM stock WHERE stkid = '{$stkid}'";
$stkRslt = db_exec($sql) or errDie("Unable to access database.", SELF);
if (pg_numrows($stkRslt) < 1) {
return "<li> Invalid Stock ID.</li>";
} else {
$stk = pg_fetch_array($stkRslt);
}
db_conn("exten");
# get warehouse
$sql = "SELECT whname FROM warehouses WHERE whid = '{$stk['whid']}'";
$whRslt = db_exec($sql);
$wh = pg_fetch_array($whRslt);
# Get all relevant records
db_connect();
$sql = "SELECT * FROM stockrec WHERE edate >= '{$fromdate}' AND edate <= '{$todate}' AND stkid = '{$stkid}' AND (trantype = 'invoice' OR trantype='note') ORDER BY edate DESC";
$recRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
$records = "";
$totprof = 0;
$totprice = 0;
$totqty = 0;
while ($rec = pg_fetch_array($recRslt)) {
# format date
$rec['edate'] = explode("-", $rec['edate']);
$rec['edate'] = $rec['edate'][2] . "-" . $rec['edate'][1] . "-" . $rec['edate'][0];
if ($rec['trantype'] == "note") {
$rec['qty'] = -$rec['qty'];
$rec['csprice'] = -$rec['csprice'];
$rec['csamt'] = -$rec['csamt'];
}
# recods
$totqty += $rec['qty'];
$prof = $rec['csprice'] - $rec['csamt'];
$totprof += $prof;
$totprice += $rec['csprice'];
$records .= "\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<td>{$rec['edate']}</td>\n\t\t\t\t\t\t\t\t<td>{$rec['details']}</td>\n\t\t\t\t\t\t\t\t<td>{$rec['qty']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$rec['csprice']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$prof}</td>\n\t\t\t\t\t\t\t</tr>";
}
// Layout
$report .= "\n\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<th colspan='2'>Details</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<td>Warehouse</td>\n\t\t\t\t\t\t\t\t<td>{$wh['whname']}</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>Stock code</td>\n\t\t\t\t\t\t\t\t<td>{$stk['stkcod']}</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>Stock description</td>\n\t\t\t\t\t\t\t\t<td>" . nl2br($stk['stkdes']) . "</pre></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>Category</td>\n\t\t\t\t\t\t\t\t<td>{$stk['catname']}</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>Category</td>\n\t\t\t\t\t\t\t\t<td>{$stk['classname']}</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<p>\n\t\t\t\t\t\t<table " . TMPL_tblDflts . " width='70%'>\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>Quantity</th>\n\t\t\t\t\t\t\t\t<th>Selling Price</th>\n\t\t\t\t\t\t\t\t<th>Gross Profit</th>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t{$records}\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<td colspan='2'><b>Totals</b></td>\n\t\t\t\t\t\t\t\t<td>{$totqty}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$totprice}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$totprof}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t</table>";
$report .= "<tr><td><br></td></tr>";
}
$OUTPUT = $report;
include "xls/temp.xls.php";
Stream("Report", $OUTPUT);
return $report;
}
function export($_POST)
{
$slip = clean_html(slip($_POST, true));
include "../xls/temp.xls.php";
Stream("Report", $slip);
}
function viewtran($_POST)
{
# get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($fprd, "string", 1, 14, "Invalid Starting Period number.");
$v->isOk($tprd, "string", 1, 14, "Invalid Ending Period number.");
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class=err>" . $e["msg"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# dates drop downs
$months = array("1" => "January", "2" => "February", "3" => "March", "4" => "April", "5" => "May", "6" => "June", "7" => "July", "8" => "August", "9" => "September", "10" => "October", "11" => "November", "12" => "December");
// if($tprd < $fprd){
// $OUTPUT = "<li class=err> Invalid Period range : $months[$fprd] to $months[$tprd]";
// require("../template.php");
// }
// Set up table to display in
$OUTPUT = "\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t<tr><th colspan=7><h3>Journal Entries : {$months[$fprd]} - {$months[$tprd]}</h3></th></tr>\n\t<tr><th colspan=7></th></tr>\n\t<tr><th>Date</th><th>Debit</th><th>Credit</th><th>Ref No</th><th>Amount</th><th>Details</th><th>User</th></tr>";
$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;
}
}
# counts
$credtot = 0;
$debtot = 0;
# Get Transactions
// for($i= $fprd; $i <= $tprd; $i++){
foreach ($prds as $i) {
db_conn($i);
$sql = "SELECT * FROM transect WHERE div = '" . USER_DIV . "'";
$tranRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve Transaction details from database.", SELF);
if (pg_numrows($tranRslt) < 1) {
continue;
} else {
# display all transactions
while ($tran = pg_fetch_array($tranRslt)) {
#get vars from tran as the are in db
foreach ($tran as $key => $value) {
${$key} = $value;
}
# format date
$date = explode("-", $date);
$date = $date[2] . "-" . $date[1] . "-" . $date[0];
// get account names
$deb = get("core", "accname, topacc, accnum", "accounts", "accid", $debit);
$debacc = pg_fetch_array($deb);
$ct = get("core", "accname, topacc,accnum", "accounts", "accid", $credit);
$ctacc = pg_fetch_array($ct);
$OUTPUT .= "<tr><td>{$date}</td><td>{$debit} - {$debacc['topacc']}/{$debacc['accnum']} - {$debacc['accname']}</td><td>{$credit} - {$ctacc['topacc']}/{$ctacc['accnum']} - {$ctacc['accname']}</td><td>{$refnum}</td><td>" . CUR . " {$amount}</td><td>{$details}</td><td>{$author}</td></tr>";
}
}
}
$OUTPUT .= "</table>";
# Send the stream
include "temp.xls.php";
Stream("AllTransactionsPrd", $OUTPUT);
}
function export($_POST)
{
$OUT = printOrd($_POST, true);
$OUT = clean_html($OUT);
require_lib("xls");
Stream("Orders Received", $OUT);
}
function export($_GET)
{
# get vars
extract($_GET);
if (!isset($action)) {
$action = "listsupp";
}
if ($filter == "") {
unset($filter);
}
if (isset($filter) && !isset($all)) {
$sqlfilter = " AND lower({$filter}) LIKE lower('%{$search}%')";
} else {
$filter = "";
$search = "";
$sqlfilter = "";
}
$filterarr = array("supname" => "Supplier Name", "supno" => "Account Number");
$filtersel = extlib_cpsel("filter", $filterarr, $filter);
# Set up table to display in
$printSupp = "\n\t<h3>Current Suppliers</h3>\n\t<p>\n\t<table " . TMPL_tblDflts . ">\n\t<tr>\n\t\t<th>Department</th>\n\t\t<th>Supp No.</th>\n\t\t<th>Supplier Name</th>\n\t\t<th>Branch</th>\n\t\t<th>Contact Name</th>\n\t\t<th>Tel No.</th>\n\t\t<th>Fax No.</th>\n\t\t<th colspan='2'>Balance</th>\n\t</tr>";
# connect to database
db_connect();
# Query server
$i = 0;
$tot = 0;
$sql = "SELECT * FROM suppliers WHERE (div = '" . USER_DIV . "' OR ddiv = '" . USER_DIV . "') {$sqlfilter} ORDER BY supname ASC";
$suppRslt = db_exec($sql) or errDie("Unable to retrieve Suppliers from database.");
if (pg_numrows($suppRslt) < 1) {
$printSupp .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td colspan='20'><li>There are no Suppliers in Cubit.</td>\n\t\t\t\t\t\t</tr>";
} else {
while ($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) {
$deptname = "<li class=err>Department not Found.";
} else {
$dept = pg_fetch_array($deptRslt);
$deptname = $dept['deptname'];
}
$supp['balance'] = sprint($supp['balance']);
# Check if record can be removed
db_connect();
$sql = "SELECT * FROM cashbook WHERE banked = 'no' AND supid = '{$supp['supid']}' AND div = '" . USER_DIV . "'";
$rs = db_exec($sql) or errDie("Unable to get cashbook entries.", SELF);
if (pg_numrows($rs) < 1 && $supp['balance'] == 0) {
$rm = "<a href='supp-rem.php?supid={$supp['supid']}'>Remove</a>";
} else {
$rm = "";
}
#if($supp['balance']==0) {$rm="<a href='supp-rem.php?supid=$supp[supid]'>Remove</a>";} else {$rm="";}
// check if supplier can be added to contact list
$addcontact = "<td><a href='conper-add.php?type=supp&id={$supp['supid']}'>Add Contact</a></td>";
$tot = $tot + $supp['balance'];
# Locations drop down
$locs = array("loc" => "Local", "int" => "International", "" => "");
$loc = $locs[$supp['location']];
$sp4 = " ";
$fbal = "{$sp4}--{$sp4}";
$trans = "<a href='core/supp-trans.php?supid={$supp['supid']}'>Transaction</a>";
if ($supp['location'] == 'int') {
$fbal = "{$sp4} {$supp['currency']} {$supp['fbalance']}";
$trans = "<a href='core/intsupp-trans.php?supid={$supp['supid']}'>Transaction</a>";
}
$printSupp .= "<tr><td>{$deptname}</td>";
if ($action == "contact_acc") {
$updatelink = "javascript: updateAccountInfo(\"{$supp['supid']}\", \"{$supp['supno']}\");";
$printSupp .= "\t<td><a href='{$updatelink}'>{$supp['supno']}</a></td>\n\t\t\t\t\t\t<td align=center><a href='{$updatelink}'>{$supp['supname']}</a></td>";
} else {
$printSupp .= "<td>{$supp['supno']}</td><td align=center>{$supp['supname']}</td>";
$printSupp .= "<td align=center>{$supp['branch']}</td>";
}
$printSupp .= "\n\t\t\t<td>{$supp['contname']}</td><td>{$supp['tel']}</td>\n\t\t\t<td>{$supp['fax']}</td><td align=right>{$sp4} " . CUR . " {$supp['balance']}</td><td align=right>{$fbal}</td>";
if ($action == "listsupp") {
// Retrieve the template settings
db_conn("cubit");
$sql = "SELECT filename FROM template_settings WHERE div='" . USER_DIV . "' AND template='statements'";
$tsRslt = db_exec($sql) or errDie("Unable to retrieve template settings from Cubit.");
$template = pg_fetch_result($tsRslt, 0);
} else {
$printSupp .= "\t<td><a href='javascript: popupSized(\"supp-det.php?supid={$supp['supid']}\", \"suppdetails\", 500, 300, \"\");'>Details</a></td>";
}
$i++;
}
if ($i > 1) {
$s = "s";
} else {
$s = "";
}
$tot = sprint($tot);
$printSupp .= "<tr><td colspan=7>Total Amount Owed, to {$i} supplier{$s} </td><td align=right>" . CUR . " {$tot}</td></tr>";
}
$printSupp .= "</form></table>";
if ($action == "listsupp") {
$printSupp .= "\n\t\t";
}
$OUTPUT = $printSupp;
include "xls/temp.xls.php";
Stream("Suppliers", $OUTPUT);
return $printSupp;
}
function xls($_POST)
{
# Get vars
extract($_POST);
$OUTPUT = base64_decode($xlformat);
# Send the stream
include "../xls/temp.xls.php";
Stream("DetailedGeneralLedger", $OUTPUT);
}
function viewtran($_POST)
{
# Get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# 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"];
}
$confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# 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.";
}
}
# 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);
$prdname = strtolower($prdname);
# Get balances
$idRs = get($yd['yrname'] . "_audit", "max(id), min(id)", $prdname . "_ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 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 .= "<tr><td colspan=8><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td></tr>";
$trans .= "<tr><td colspan=2><br></td><td>Br/Forwd</td><td>Brought Forward</td><td align=right>{$bal['dbalance']}</td><td align=right>{$bal['cbalance']}</td><td align=right>{$balance} {$fl}</td><td> </td></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";
} 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 = "";
}
# 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'] = "";
$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>General Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=90%>\n\t<tr><td colspan=8 align=center><input type=submit value='Export to Spreadsheet'></td></tr>\n\t<tr><td colspan=8><br></td></tr>\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<tr><td colspan=8><br></td></tr>\n\t<table>";
//<tr><td colspan=8 align=center><input type=submit value='Export to Spreadsheet'></td></tr>
//return $view;
include "temp.xls.php";
Stream("Ledger", $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.");
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 update($_POST)
{
# Get Vars ( banked[] )
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# Check if anything is selected
if (!isset($banked)) {
$err = "<li class=err> Please Select at least one entry to update.";
return cashbook($_POST, $err);
}
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
/* - End Hooks - */
$refnum = getrefnum();
/*refnum*/
# Record all trans
$tot = 0;
$totr = 0;
$totp = 0;
$recpts = "";
$paymnts = "";
foreach ($banked as $key => $cashid) {
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve details from database.", SELF);
$cash = pg_fetch_array($cashRslt);
# Set record as banked
db_connect();
$sql = "UPDATE cashbook SET banked = 'yes' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
$Rslt = db_exec($sql) or errDie("Unable to set bank deposit as banked in Cubit.", SELF);
}
// Connect to database
db_Connect();
$sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND banked = 'no' AND div = '" . USER_DIV . "' ORDER BY date DESC";
$cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank transactions from database.", SELF);
$tot = 0;
$totr = 0;
$totp = 0;
$recpts = "";
$paymnts = "";
while ($cash = pg_fetch_array($cashRslt)) {
if ($cash['trantype'] == "deposit") {
$recpts .= "<tr><td>{$cash['date']}</td><td>{$cash['descript']}</td><td align=right>" . CUR . " {$cash['amount']}</td></tr>";
$totr += $cash['amount'];
} else {
$paymnts .= "<tr><td>{$cash['date']}</td><td>{$cash['descript']}</td><td align=right>" . CUR . " {$cash['amount']}</td></tr>";
$totp += $cash['amount'];
}
$tot += $cash['amount'];
}
$reconbal = sprint($cbal + ($totr - $totp));
# Get account name for bank account
db_connect();
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid= '{$bankid}' AND div = '" . USER_DIV . "'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
# Get hook account number
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
# check if link exists
if (pg_numrows($rslt) < 1) {
return "<li class=err> ERROR : The bank account that you selected doesn't appear to have an account linked to it.";
}
$banklnk = pg_fetch_array($rslt);
# Get bank balance
$sql = "SELECT (debit - credit) as bal FROM trial_bal WHERE period='" . PRD_DB . "' AND accid = '{$banklnk['accnum']}' AND div = '" . USER_DIV . "'";
$brslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
$bal = pg_fetch_array($brslt);
$diff = $reconbal - $bal['bal'];
$derr = "";
if ($diff != 0) {
$derr = "<tr><td colspan=2><b class=err>Bank statement and computer balance not balancing by</b></td><td align=right>" . CUR . " {$diff}</td></tr>";
}
// Layout
$update = "<table border=1>\n\t<tr><th colspan=3><h3>Bank Reconciliation Output</h3></th></tr>\n\t<tr><td></td></tr>\n\t<tr><td colspan=3>\n\t\t<table cellpadding='2' cellspacing='0' border=0 bordercolor='#000000' width=100%>\n\t\t\t<tr><td><b>Bank Account : </b>{$bank['accname']}</td><td></td><td align=right><b>Prepared By : </b>" . USER_NAME . "</td></tr>\n\t\t\t<tr><td><b>Closing Balance As per Bank Statement : </b>" . CUR . " {$cbal}</td><td></td></tr>\n\t\t</table>\n\t</td></tr>\n\t<tr><td><br><br></td></tr>\n\t<tr><td colspan=3><b>Plus Outstanding Receipts :</b></td></tr>\n\t<tr><th>Date</th><th>Reference</th><th>Amount</th></tr>\n\t{$recpts}\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2 align=right><b>Sub Total</b></td><td align=right>" . CUR . " {$totr}</td></tr>\n\t<tr><td><br><br></td></tr>\n\t<tr><td colspan=3><b>Less Outstanding Payments :</b></td></tr>\n\t<tr><th>Date</th><th>Reference</th><th>Amount</th></tr>\n\t{$paymnts}\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2 align=right><b>Sub Total</b></td><td align=right>" . CUR . " {$totp}</td></tr>\n\t<tr><td><br><td></tr>\n\t{$derr}\n\t<tr><td><br><td></tr>\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2><b>Reconciled Bank Balance</b></td><td align=right>" . CUR . " {$reconbal}</td></tr>\n\t<tr><td colspan=2><b>Computer Bank Balance</b></td><td align=right>" . CUR . " {$bal['bal']}</td></tr>\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2><b>Diff</b></td><td align=right>" . CUR . " {$diff}</td></tr>\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t</table>";
# Send the stream
include "temp.xls.php";
return Stream("BankRecon", $update);
}
function 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 display($_POST)
{
extract($_POST);
$prev_yr_schema = substr(YR_DB, 0, 2) . (substr(YR_DB, 2) - 1);
if ($prev_yr_schema < 0) {
return "<li class='err'>No prior years found.</li>";
}
/* "prior" should be from beginning of month (iow the end of the previous)
or if it is the first period the end of period 0 (end of last year) */
global $MONPRD, $PRDMON;
$month_from = $PRDMON[$MONPRD[$month_from] - 1];
switch (strtolower($key)) {
case "print":
case "save":
case "export to spreadsheet":
$print_func = true;
break;
default:
$print_func = false;
break;
}
// Retrieve the accounts list from Cubit
db_conn("cubit");
$sql = "SELECT * FROM saved_cf_accounts";
$cfacc_rslt = db_exec($sql) or errDie("Unable to retrieve accounts list from Cubit.");
$i = 0;
while ($cfacc_data = pg_fetch_array($cfacc_rslt)) {
// Multidimensional array, variable variable
${$cfacc_data["category"]}[] = $cfacc_data["accid"];
}
$cat_list = array("nciis" => "Non Cash Item in Income Statement", "ciaal" => "Change in Assets and Liabilities", "cffuif" => "Cash Flows From / Used in Financing", "cffuii" => "Cash Flows From / Used in Investing");
$cat_total = array("ciaal" => "Net cash provided by operating activities", "cffuif" => "Net cash provided by financing activities", "cffuii" => "Net cash used in investing activities");
// Net cash and cash equivalents, beginning of period
$cash_equiv_bop = array();
$cash_equiv_bop["curr_total"] = 0;
$cash_equiv_bop["prev_total"] = 0;
$cash_equiv_bop["var_total"] = 0;
$cash_equiv_bop["percvar_total"] = 0;
foreach ($cat_list as $key => $value) {
if (($key == "cffuif" || $key == "cffuii") && isset(${"acc_{$key}"})) {
foreach (${"acc_{$key}"} as $accid) {
db_conn("core");
$sql = "SELECT * FROM trial_bal_actual WHERE accid='{$accid}' AND month='{$month_from}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve trial balance data from Cubit.");
$py_data = pg_fetch_array($rslt);
$sql = "SELECT * FROM trial_bal_actual WHERE accid='{$accid}' AND month='{$month_to}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve trial balance data from Cubit.");
$tb_data = pg_fetch_array($rslt);
/*if ($prev_yr_schema) {
db_conn($prev_yr_schema);
$sql = "SELECT * FROM year_balance WHERE accid='$accid'";
$rslt = db_exec($sql) or errDie("Unable to retrieve previous year data from Cubit.");
$py_data = pg_fetch_array($rslt);
} else {
$py_data = array(
"debit" => 0,
"credit" => 0
);
}*/
db_conn("core");
$sql = "SELECT toptype FROM accounts WHERE accid='{$accid}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve accounts from Cubit.");
$toptype = pg_fetch_result($rslt, 0);
$cash_equiv_bop["curr_total"] += calculate($toptype, $tb_data["debit"], $tb_data["credit"]);
$cash_equiv_bop["prev_total"] += calculate($toptype, $py_data["debit"], $py_data["credit"]);
}
}
}
$cash_equiv_bop["var_total"] = $cash_equiv_bop["curr_total"] - $cash_equiv_bop["prev_total"];
if ($cash_equiv_bop["curr_total"] && $cash_equiv_bop["prev_total"]) {
$cash_equiv_bop["percvar_total"] = $cash_equiv_bop["curr_total"] / $cash_equiv_bop["prev_total"] * 100;
} else {
$cash_equiv_bop["percvar_total"] = 0;
}
// Net cash and cash equivalents, end of period
$cash_equiv_eop = array();
$cash_equiv_eop["curr_total"] = 0;
$cash_equiv_eop["prev_total"] = 0;
$cash_equiv_eop["var_total"] = 0;
$cash_equiv_eop["percvar_total"] = 0;
foreach ($cat_list as $key => $value) {
if (($key == "cffuif" || $key == "cffuii") && isset(${"acc_{$key}"})) {
foreach (${"acc_{$key}"} as $accid) {
db_conn("core");
$sql = "SELECT * FROM trial_bal_actual WHERE accid='{$accid}' AND month='{$month_from}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve trial balance data from Cubit.");
$py_data = pg_fetch_array($rslt);
$sql = "SELECT * FROM trial_bal_actual WHERE accid='{$accid}' AND month='{$month_to}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve trial balance data from Cubit.");
$tb_data = pg_fetch_array($rslt);
/*$py_month = strtolower(getMonthName($month_to));
db_conn($prev_yr_schema);
$sql = "SELECT * FROM $py_month WHERE accid='$accid'";
$rslt = db_exec($sql) or errDie("Unable to retrieve previous year data from Cubit.");
$py_data = pg_fetch_array($rslt);*/
db_conn("core");
$sql = "SELECT toptype FROM accounts WHERE accid='{$accid}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve accounts from Cubit.");
$toptype = pg_fetch_result($rslt, 0);
$cash_equiv_eop["curr_total"] += calculate($toptype, $tb_data["debit"], $tb_data["credit"]);
$cash_equiv_eop["prev_total"] += calculate($toptype, $py_data["debit"], $py_data["credit"]);
}
}
}
$cash_equiv_eop["var_total"] = $cash_equiv_eop["curr_total"] - $cash_equiv_eop["prev_total"];
if ($cash_equiv_eop["curr_total"] && $cash_equiv_eop["prev_total"]) {
$cash_equiv_eop["percvar_total"] = $cash_equiv_eop["curr_total"] / $cash_equiv_eop["prev_total"] * 100;
} else {
$cash_equiv_eop["percvar_total"] = 0;
}
// Output the headings and accounts
$acc_out = "";
if (!pg_num_rows($cfacc_rslt)) {
$acc_out .= "<tr class='bg-odd'><td colspan='5'>No accounts selected.</td></tr>";
}
$i = 0;
foreach ($cat_list as $key => $value) {
if (isset(${$key})) {
// Category heading
$acc_out .= "<tr><th colspan='5' class='cashflow_cats'>{$value}</th></tr>";
foreach (${"acc_{$key}"} as $accid) {
// Retrieve the account info from the trial balance
db_conn("core");
$sql = "SELECT debit, credit FROM trial_bal WHERE accid='{$accid}' AND month='{$month_from}'";
$tb_rslt = db_exec($sql) or errDie("Unable to retrieve account information from Cubit.");
$ptb_data = pg_fetch_array($tb_rslt);
$sql = "SELECT debit, credit FROM trial_bal WHERE accid='{$accid}' AND month='{$month_to}'";
$tb_rslt = db_exec($sql) or errDie("Unable to retrieve account information from Cubit.");
$tb_data = pg_fetch_array($tb_rslt);
// Account information
db_conn("core");
$sql = "SELECT * FROM accounts WHERE accid='{$accid}'";
$acc_rslt = db_exec($sql) or errDie("Unable to retrieve account information from Cubit.");
$acc_data = pg_fetch_array($acc_rslt);
// Retrieve previous year trial balance
/*db_conn($prev_yr_schema);
$sql = "SELECT * FROM year_balance WHERE accid='$accid'";
$ptb_rslt = db_exec($sql) or errDie("Unable to retrieve previous year account information from Cubit.");
$ptb_data = pg_fetch_array($ptb_rslt);
// Previous year account information
db_conn("core");
$sql = "SELECT * FROM accounts WHERE accid='$accid'";
$pacc_rslt = db_exec($sql) or errDie("Unable to retrieve previous year account information from Cubit.");
$pacc_data = pg_fetch_array($pacc_rslt);*/
// Make sure we've got a toptype
if (empty($acc_data["toptype"])) {
if ($acc_data["acctype"] == "I") {
$acc_data["toptype"] = "other_income";
}
if ($acc_data["acctype"] == "E") {
$acc_data["toptype"] = "expenses";
}
}
/*if (empty($pacc_data["toptype"])) {
if ($pacc_data["acctype"] == "I") $pacc_data["toptype"] = "other_income";
if ($pacc_data["acctype"] == "E") $pacc_data["toptype"] = "expenses";
}*/
// Do the calculations
$current = calculate($acc_data["toptype"], $tb_data["debit"], $tb_data["credit"]);
$prior = calculate($acc_data["toptype"], $ptb_data["debit"], $ptb_data["credit"]);
$variance = $current - $prior;
// We don't want a division by zero...
if ($current && $prior) {
$percvar = $current / $prior * 100;
} else {
$percvar = "0.00";
}
// How should the current category be displayed
switch ($key) {
case "ciaal":
if ($current <= $prior) {
$decrease = "(decrease)";
$increase = "Increase";
} else {
$decrease = "decrease";
$increase = "(Increase)";
}
$acc_out .= "<tr class='bg-even'>\n\t\t\t\t\t\t\t<td>{$increase} {$decrease} in {$acc_data['accname']}</td>\n\t\t\t\t\t\t\t<td align='right'><b>" . sprint($current) . "</b></td>\n\t\t\t\t\t\t\t<td align='right'>" . sprint($prior) . "</td>\n\t\t\t\t\t\t\t<td align='right'>" . sprint($variance) . "</td>\n\t\t\t\t\t\t\t<td align='right'><b>" . sprint($percvar) . "</td>\n\t\t\t\t\t\t</tr>";
break;
case "nciis":
case "cffuif":
case "cffuii":
$acc_out .= "<tr class='bg-even'>\n\t\t\t\t\t\t\t<td>{$acc_data['accname']}</td>\n\t\t\t\t\t\t\t<td align='right'><b>" . sprint($current) . "</b></td>\n\t\t\t\t\t\t\t<td align='right'>" . sprint($prior) . "</td>\n\t\t\t\t\t\t\t<td align='right'>" . sprint($variance) . "</td>\n\t\t\t\t\t\t\t<td align='right'><b>" . sprint($percvar) . "</td>\n\t\t\t\t\t\t</tr>";
}
// Create the total variables, unless they already exist
if (!isset(${$key}["curr_total"])) {
${$key}["curr_total"] = 0.0;
}
if (!isset(${$key}["prev_total"])) {
${$key}["prev_total"] = 0.0;
}
if (!isset(${$key}["var_total"])) {
${$key}["var_total"] = 0.0;
}
if (!isset(${$key}["percvar_total"])) {
${$key}["percvar_total"] = 0.0;
}
// Assign values to the totals
if (isset(${$key})) {
${$key}["curr_total"] += $current;
${$key}["prev_total"] += $prior;
${$key}["var_total"] += $variance;
${$key}["percvar_total"] += $percvar;
}
}
if ($key == "ciaal") {
$acc_out .= "<tr class='bg-odd'>\n\t\t\t\t\t<td>Total adjustments</td>\n\t\t\t\t\t<td align='right'><b>" . sprint(${$key}["curr_total"]) . "</b></td>\n\t\t\t\t\t<td align='right'>" . sprint(${$key}["prev_total"]) . "</td>\n\t\t\t\t\t<td align='right'>" . sprint(${$key}["var_total"]) . "</td>\n\t\t\t\t\t<td align='right'><b>" . sprint(${$key}["percvar_total"]) . "</b></td>\n\t\t\t\t</tr>";
}
// nciis and ciaal are both in the same category, dont assign a total to both
// instead just use nciis
if ($key != "nciis") {
if (isset($nciis) && isset($ciaal)) {
$ciaal["curr_total"] += $nciis["curr_total"];
$ciaal["prev_total"] += $nciis["prev_total"];
$ciaal["var_total"] += $nciis["var_total"];
$ciaal["percvar_total"] += $nciis["percvar_total"];
}
// Totals output
$acc_out .= "<tr class='bg-odd'>\n\t\t\t\t\t<td>{$cat_total[$key]}</td>\n\t\t\t\t\t<td align='right'><b>" . sprint(${$key}["curr_total"]) . "</b></td>\n\t\t\t\t\t<td align='right'>" . sprint(${$key}["prev_total"]) . "</td>\n\t\t\t\t\t<td align='right'>" . sprint(${$key}["var_total"]) . "</td>\n\t\t\t\t\t<td align='right'><b>" . sprint(${$key}["percvar_total"]) . "</b></td>\n\t\t\t\t</tr>";
} else {
// Totals output
$acc_out .= "<tr class='bg-odd'>\n\t\t\t\t\t<td> </td>\n\t\t\t\t\t<td align='right'><b>" . sprint(${$key}["curr_total"]) . "</b></td>\n\t\t\t\t\t<td align='right'>" . sprint(${$key}["prev_total"]) . "</td>\n\t\t\t\t\t<td align='right'>" . sprint(${$key}["var_total"]) . "</td>\n\t\t\t\t\t<td align='right'><b>" . sprint(${$key}["percvar_total"]) . "</b></td>\n\t\t\t\t</tr>";
}
}
}
// Net cash and cash equivalents
if (!isset($cffuif)) {
$cffuif = array();
$cffuif["curr_total"] = 0.0;
$cffuif["prev_total"] = 0.0;
$cffuif["var_total"] = 0.0;
$cffuif["percvar_total"] = 0.0;
}
if (!isset($cffuii)) {
$cffuii = array();
$cffuii["curr_total"] = 0.0;
$cffuii["prev_total"] = 0.0;
$cffuii["var_total"] = 0.0;
$cffuii["percvar_total"] = 0.0;
}
$cash_equiv = array();
$cash_equiv["curr_total"] = $cffuif["curr_total"] + $cffuii["curr_total"] + $nciis["curr_total"];
$cash_equiv["prev_total"] = $cffuif["prev_total"] + $cffuii["prev_total"] + $nciis["prev_total"];
$cash_equiv["var_total"] = $cffuif["var_total"] + $cffuii["var_total"] + $nciis["var_total"];
$cash_equiv["percvar_total"] = $cffuif["percvar_total"] + $cffuii["percvar_total"] + $nciis["percvar_total"];
if ($cash_equiv["curr_total"] < $cash_equiv["prev_total"]) {
$cash_equiv["inc_dec"] = "(increase) decrease";
} else {
$cash_equiv["inc_dec"] = "increase (decrease)";
}
// Date range
// Retrieve the current year from Cubit
db_conn("core");
$sql = "SELECT yrname FROM active";
$rslt = db_exec($sql) or errDie("Unable to retrieve current year from Cubit.");
$year_out = substr(pg_fetch_result($rslt, 0), 1);
$month_from_out = getMonthName($month_from);
$month_to_out = getMonthName($month_to);
if ($month_from == $month_to) {
$date_range = "{$month_from_out} {$year_out}";
} else {
$date_range = "{$month_from_out} TO {$month_to_out} {$year_out}";
}
$OUTPUT = "";
if (!$print_func) {
$OUTPUT .= "\n\t\t<form method='post' action='" . SELF . "'>\n\t\t<input type='hidden' name='key' value='slct'>\n\t\t<input type='hidden' name='month_from' value='{$month_from}'>\n\t\t<input type='hidden' name='month_to' value='{$month_to}'>";
}
$OUTPUT .= "\n\t<table border='0' cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width='100%'>\n\t\t<tr>\n\t\t\t<td><h3>Statement of Cash Flow</h3></td>\n\t\t\t<td align='right'><h3>{$date_range}</h3></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<td colspan='2' class='err'>\n\t\t\tThe non-cash items have been adjusted to operating profit. The other\n\t\t\tleg must still be debited or credited to an asset/liability account.\n\t\t\t</td>\n\t\t</tr>\n\t</table>\n\t<table border='0' cellpadding='0' cellspacing='0' width='100%'>\n\t\t<tr>\n\t\t\t<th> </td>\n\t\t\t<th width='10%' class='thkborder'>Current</th>\n\t\t\t<th width='10%' class='thkborder'>Prior</th>\n\t\t\t<th width='10%' class='thkborder'>Variance</th>\n\t\t\t<th width='10%' class='thkborder thkborder_right'>%Var</th>\n\t\t</tr>\n\t\t{$acc_out}\n\t\t<tr class='bg-odd'>\n\t\t\t<td>Net cash {$cash_equiv['inc_dec']} in cash and cash equivalents</td>\n\t\t\t<td align='right'><b>" . sprint($cash_equiv["curr_total"]) . "</b></td>\n\t\t\t<td align='right'>" . sprint($cash_equiv["prev_total"]) . "</td>\n\t\t\t<td align='right'>" . sprint($cash_equiv["var_total"]) . "</td>\n\t\t\t<td align='right'><b>" . sprint($cash_equiv["percvar_total"]) . "</b></td>\n\t\t</tr>\n\t\t<tr class='bg-odd'>\n\t\t\t<td>Net cash equivalents beginning of period</td>\n\t\t\t<td align='right'><b>" . sprint($cash_equiv_bop["curr_total"]) . "</b></td>\n\t\t\t<td align='right'>" . sprint($cash_equiv_bop["prev_total"]) . "</td>\n\t\t\t<td align='right'>" . sprint($cash_equiv_bop["var_total"]) . "</td>\n\t\t\t<td align='right'>" . sprint($cash_equiv_bop["percvar_total"]) . "</td>\n\t\t</tr>\n\t\t<tr class='bg-odd'>\n\t\t\t<td>Net cash equivalents end of period</td>\n\t\t\t<td align='right'><b>" . sprint($cash_equiv_eop["curr_total"]) . "</b></td>\n\t\t\t<td align='right'>" . sprint($cash_equiv_eop["prev_total"]) . "</td>\n\t\t\t<td align='right'>" . sprint($cash_equiv_eop["var_total"]) . "</td>\n\t\t\t<td align='right'>" . sprint($cash_equiv_eop["percvar_total"]) . "</td>\n\t\t</tr>";
if (!$print_func) {
$pf = "";
foreach ($cat_list as $key => $value) {
if (!isset(${"acc_{$key}"})) {
continue;
}
foreach (${"acc_{$key}"} as $k => $v) {
$pf .= "<input type='hidden' name='acc_{$key}" . "[{$k}]' value='{$v}' />";
}
}
$OUTPUT .= "\n\t\t{$pf}\n\t\t<tr>\n\t\t\t<td colspan='5' align='center'>\n\t\t\t\t<input type='submit' value='Accounts'>\n\t\t\t\t<input type='submit' name='key' value='Save'>\n\t\t\t\t<input type='submit' name='key' value='Print'>\n\t\t\t\t<input type='submit' name='key' value='Export to Spreadsheet'>\n\t\t\t</td>\n\t\t</tr>\n\t\t</table>\n\t\t</form>";
} else {
$OUTPUT .= "\n\t\t</table>";
}
if ($print_func) {
$OUTPUT = clean_html($OUTPUT);
switch (strtolower($_POST["key"])) {
case "print":
require "../tmpl-print.php";
break;
case "save":
db_conn("core");
$sql = "INSERT INTO save_cashflow (output, div) VALUES ('" . base64_encode($OUTPUT) . "', '" . USER_DIV . "')";
$svincRslt = db_exec($sql) or errDie("Unable to save the balance sheet to Cubit.");
return "<li>Cash Flow Statement has been successfully saved to Cubit.</li>";
break;
case "export to spreadsheet":
require "../xls/temp.xls.php";
Stream("cashflow", $OUTPUT);
break;
}
} else {
$OUTPUT .= "\n\t\t<p>\n\t\t<center>\n\t\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=25%>\n\t\t\t<tr><th>Quick Links</th></tr>\n\t\t\t<tr class='datacell'><td align='center'><a target=_blank href='../core/acc-new2.php'>Add account (New Window)</a></td></tr>\n\t\t\t<tr class='datacell'><td align='center'><a href='index-reports.php'>Financials</a></td></tr>\n\t\t\t<tr class='datacell'><td align='center'><a href='index-reports-stmnt.php'>Current Year Financial Statements</a></td></tr>\n\t\t\t<tr class='datacell'><td align='center'><a href='../main.php'>Main Menu</td></tr>\n\t\t</table>\n\t\t</center>";
}
return $OUTPUT;
}
