function display()
{
extract($_REQUEST);
$fields = array();
$fields["from_year"] = date("Y");
$fields["from_month"] = date("m");
$fields["from_day"] = "01";
$fields["to_year"] = date("Y");
$fields["to_month"] = date("m");
$fields["to_day"] = date("d");
extract($fields, EXTR_SKIP);
$from_date = "{$from_year}-{$from_month}-{$from_day}";
$to_date = "{$to_year}-{$to_month}-{$to_day}";
$sql = "SELECT stkid, stkcod, stkdes, units, minlvl FROM cubit.stock";
$stock_rslt = db_exec($sql) or errdie("Unable to retrieve stock.");
$stock_out = "";
while ($stock_data = pg_fetch_array($stock_rslt)) {
// Gather all the data we need to perform the calculations
$qty_units = $stock_data["units"];
$qty_min = $stock_data["minlvl"];
$qty_sales = averageSalesQty($stock_data["stkid"], $from_date, $to_date, "YEARLY");
$qty_ordered = qty_ordered($stock_data["stkid"], $from_date, $to_date);
$total_qty = $qty_units + $qty_ordered - $qty_sales;
if ($total_qty <= $qty_min) {
$qty_suggest = (int) ($qty_min - $total_qty);
}
// Nothing interesting to see here...
if (!isset($qty_suggest) || !$qty_suggest) {
continue;
}
$stock_out .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$stock_data['stkcod']}</td>\n\t\t\t\t<td>{$stock_data['stkdes']}</td>\n\t\t\t\t<td align='center'><b>{$qty_suggest}</b></td>\n\t\t\t\t<td>" . orderDate($stock_data["stkid"], $from_date, $to_date) . "</td>\n\t\t\t</tr>";
}
$OUTPUT = "\n\t\t<center>\n\t\t<h3>Recommended Order Date and Order Quantity Report</h3>\n\t\t<form method='post' action='" . SELF . "'>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr><th colspan='4'>Date Range</th></tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>" . mkDateSelect("from", $from_year, $from_month, $from_day) . "</td>\n\t\t\t\t<td> <b> To </b> </td>\n\t\t\t\t<td>" . mkDateSelect("to", $to_year, $to_month, $to_day) . "</td>\n\t\t\t\t<td>\n\t\t\t\t\t<input type='submit' value='Apply' style='font-weight: bold' />\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>\n\t\t</form>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Stock Code</th>\n\t\t\t\t<th>Stock Description</th>\n\t\t\t\t<th>Suggested Order Qty</th>\n\t\t\t\t<th>Suggested Order Date</th>\n\t\t\t</tr>\n\t\t\t{$stock_out}\n\t\t</table>";
return $OUTPUT;
}
function view()
{
core_connect();
$sql = "SELECT batchid FROM batch WHERE proc = 'no' AND div = '" . USER_DIV . "'";
$Rs = db_exec($sql) or errdie("Batch file unreachable.");
if (pg_numrows($Rs) > 0) {
$sum = pg_numrows($Rs);
$out = pg_fetch_array($Rs);
$note = "<tr class='bg-even'><td colspan=2 class=err><li>Note : There are {$sum} unprocessed batch entries.</td></tr><tr><td><br></td></tr>";
} else {
$note = "";
}
$view = "\n\t<h3>Trial Balance</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=350>\n\t<form action='" . SELF . "' method=post name=form>\n\t<input type=hidden name=key value=print>\n\t{$note}\n\t<tr><th>Field</th><th>Value</th></tr>\n\t<tr class='bg-odd'><td>Include Accounts with Zero balances</td><td valign=center>\n\t<input type=radio name=zero value=yes>Yes | <input type=radio name=zero value=no checked=yes>No</td></tr>\n\t<tr><td><br></td></tr>\n\t<tr class='bg-even'><td>List Debit & Credit</td><td valign=center>\n <input type=radio name=work value=no checked=yes>Yes | <input type=radio name=work value=Yes >No</td></tr>\n <tr><td><br></td></tr>\n\t<tr><td><input type=button value='< Cancel' onClick='javascript:history.back();'></td><td valign=center><input type=submit value='Continue >'></td></tr>\n\t</table>";
return $view;
}
function con_data($_POST)
{
# get vars
extract($_POST);
if (isset($back)) {
return view_data($_POST);
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($id, "num", 0, 100, "Invalid number.");
$v->isOk($depamt, "float", 0, 14, "Invalid Depreciation Amount.");
$v->isOk($depmonths, "num", 0, 3, "Invalid auto depreciation period.");
$v->isOk("{$depmonths}{$depamt}", "float", 1, 14, "Enter one of Depreciation amount or period.");
if (!empty($depamt) && $netval < $depamt) {
$v->isOk("###", "float", 1, 1, "Error : Depreciation amount must not be more than the Net Value.");
} else {
if (!empty($depmonths) && $depperc <= 0) {
$v->addError("###", "Depriaction percentage has to be more than 0 if depreciating by period.");
}
}
$v->isOk($date, "date", 1, 14, "Invalid account open date.");
# display errors, if any
if ($v->isError()) {
$confirmCust = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirmCust .= "<li class=err>" . $e["msg"];
}
$confirmCust .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirmCust;
}
db_conn('cubit');
$user = USER_NAME;
$Sql = "SELECT * FROM assets WHERE (id='{$id}' AND div = '" . USER_DIV . "')";
$Rslt = db_exec($Sql) or errDie("Unable to access database.");
if (pg_numrows($Rslt) < 1) {
return "Asset not Found";
}
$led = pg_fetch_array($Rslt);
# Get group
$sql = "SELECT * FROM assetgrp WHERE grpid = '{$led['grpid']}' AND div = '" . USER_DIV . "'";
$grpRslt = db_exec($sql);
$grp = pg_fetch_array($grpRslt);
# get last ref number
$refnum = getrefnum($date);
if ($led["dep_acc"]) {
$dep_acc = $led["dep_acc"];
} else {
// Maintain backwards compatibiltiy
$sql = "\r\n\t\tSELECT accid FROM core.accounts\r\n\t\tWHERE topacc='2200' AND accnum='000'";
$acc_rslt = db_exec($sql) or errDie("Unable to retrieve account.");
$dep_acc = pg_fetch_result($acc_rslt, 0);
}
if ($led["accdep_acc"]) {
$accdep_acc = $led["accdep_acc"];
} else {
// Maintain backwards compatibiltiy
$accdep_acc = $grp["accdacc"];
}
pglib_transaction("BEGIN");
# dt(depacc) ct(accdep)
writetrans($dep_acc, $accdep_acc, $date, $refnum, $depamt, "{$led['des']} Depreciation");
db_connect();
$sql = "UPDATE assets SET accdep = (accdep + '{$depamt}') WHERE (id='{$id}' AND div = '" . USER_DIV . "')";
$up = db_exec($sql) or errdie("Could not update assets table.");
$snetval = $netval - $depamt;
$sdate = date("Y-m-d");
$sql = "INSERT INTO assetledger(assetid, asset, date, depamt, netval, div) \r\n\t\t\tVALUES ('{$id}', '{$led['des']}', '{$date}', '{$depamt}', '{$snetval}', '" . USER_DIV . "')";
$rec = db_exec($sql) or errdie("Could not write to asset ledger.");
$cc = "<script> CostCenter('ct', 'Asset Depreciation', '{$date}', '{$led['des']} Depreciation', '{$depamt}', ''); </script>";
pglib_transaction("COMMIT");
$write = "\r\n\t\t\t\t{$cc}\r\n\t\t\t\t<table " . TMPL_tblDflts . " width='50%'>\r\n\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t<th>Asset Depreciation</th>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='datacell'>\r\n\t\t\t\t\t\t<td>Asset Depreciation has been recorded</td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t</table>\r\n\t\t\t\t<p>\r\n\t\t\t\t<table border=0 cellpadding='2' cellspacing='1'>\r\n\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t<th>Quick Links</th>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td><a href='asset-new.php'>New Asset</a></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td><a href='asset-view.php'>View Assets</a></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\r\n\t\t\t\t</table>";
return $write;
}
function sheet_save()
{
# Get Owners Equity Sub Headings
$oesubRslt = get("core", "*", "bal_sheet", "type", "OESUB");
$sheet = "<center>\n <h3> Balance sheet for period : " . PRD_NAME . "<br><br>Date: " . date("d M Y") . " </h3>\n <table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width='70%'>\n <tr><th colspan=3>Owners Equity</th></tr>";
# get accounts
$oebal = 0;
// OE Balance
while ($oesub = pg_fetch_array($oesubRslt)) {
$sheet .= "<tr class='bg-even'><td colspan=3><b>{$oesub['value']}<b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='OEACC' AND ref = {$oesub['ref']}";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
$query = "SELECT * FROM trial_bal WHERE accid = '{$acc['value']}'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['credit'] - $accbal['debit'];
$oebal += $balance;
$sheet .= "<tr class='bg-odd'><td><blockquote><li>{$accbal['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
# print Net Income and OE Balance on last column
$netincome = getNetIncome();
$oebal += $netincome;
$sheet .= "<tr class='bg-odd'><td> <b>Net Income</b></td><td>" . CUR . " {$netincome}</td><td><br></td></tr>";
$sheet .= "<tr class='bg-even'><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$oebal}</b></td></tr>";
# Get Assets Sub Headings
$abal = 0;
// Assets Balance
$asssubRslt = get("core", "*", "bal_sheet", "type", "ASSSUB");
$sheet .= "<tr><th colspan=3>Assets</th></tr>";
# get accounts
while ($asssub = pg_fetch_array($asssubRslt)) {
$sheet .= "<tr class='bg-even'><td colspan=3><b>{$asssub['value']}<b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='ASSACC' AND ref = {$asssub['ref']}";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
$query = "SELECT * FROM trial_bal WHERE accid = '{$acc['value']}'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['debit'] - $accbal['credit'];
// calc Balance
$abal += $balance;
$sheet .= "<tr class='bg-odd'><td><blockquote><li>{$accbal['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
# print assets balance on last column
$sheet .= "<tr class='bg-even'><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$abal}</b></td></tr>\n </table><br>";
$output = base64_encode($sheet);
core_connect();
$sql = "INSERT INTO save_bal_sheet(gendate, output) VALUES('" . date("Y-m-d") . "', '{$output}')";
$Rs = db_exec($sql) or errdie("Unable to save the Balance Sheet.");
return true;
}
function print_saveacc($_POST)
{
# get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
// Set up table to display in
$OUTPUT = "\n <center>\n <h3>Trial Balance as at : " . date("d M Y") . "</h3>\n\t\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=450>\n <tr><th>Account Number</th><th>Account Name</th><th>Debit</th><th>Credit</th></tr>";
// Connect to database
core_connect();
$sql = "SELECT * FROM trial_bal ORDER BY topacc, accnum ASC";
$accRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve account details from database.", SELF);
$numrows = pg_numrows($accRslt);
if ($numrows < 1) {
$OUTPUT = "There are no Accounts yet in Cubit.";
require "../template.php";
}
# display all Accounts
$i = 0;
$tldebit = 0;
$tlcredit = 0;
if ($zero == "no") {
while ($acc = pg_fetch_array($accRslt)) {
$i++;
if (intval($acc['debit']) == 0 && intval($acc['credit']) == 0) {
$i++;
continue;
}
$branname = branname($acc['div']);
$OUTPUT .= "<tr class='" . bg_class() . "'><td>{$acc['div']} - {$acc['topacc']}/{$acc['accnum']}</td><td>{$branname} - {$acc['accname']}</td>";
if (intval($acc['debit']) == 0) {
$OUTPUT .= "<td align=center> - </td>";
} else {
$OUTPUT .= "<td align=center>" . CUR . " {$acc['debit']}</td>";
}
if (intval($acc['credit']) == 0) {
$OUTPUT .= "<td align=center> - </td>";
} else {
$OUTPUT .= "<td align=center>" . CUR . " {$acc['credit']}</td>";
}
$OUTPUT .= "</tr>";
$tldebit += $acc['debit'];
$tlcredit += $acc['credit'];
}
} elseif ($zero == "yes") {
while ($acc = pg_fetch_array($accRslt)) {
$i++;
$branname = branname($acc['div']);
$OUTPUT .= "<tr class='" . bg_class() . "'><td>{$acc['div']} - {$acc['topacc']}/{$acc['accnum']}</td><td>{$branname} - {$acc['accname']}</td>";
if (intval($acc['debit']) == 0) {
$OUTPUT .= "<td align=center> - </td>";
} else {
$OUTPUT .= "<td align=center>" . CUR . " {$acc['debit']}</td>";
}
if (intval($acc['credit']) == 0) {
$OUTPUT .= "<td align=center> - </td>";
} else {
$OUTPUT .= "<td align=center>" . CUR . " {$acc['credit']}</td>";
}
$OUTPUT .= "</tr>";
$tldebit += $acc['debit'];
$tlcredit += $acc['credit'];
}
}
$OUTPUT .= "<tr class='" . bg_class() . "'><td colspan=2><b>Total</b></td><td align=center><b>" . CUR . " {$tldebit}</b></td><td align=center><b>" . CUR . " {$tlcredit}</b></td></tr>\n\t\t</table><br>";
$output = base64_encode($OUTPUT);
core_connect();
$sql = "INSERT INTO save_trial_bal(gendate, output, div) VALUES('" . date("Y-m-d") . "', '{$output}', '" . USER_DIV . "')";
$Rs = db_exec($sql) or errdie("Unable to save the Trial Balance.");
$OUTPUT .= "\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<script>document.write(getQuicklinkSpecial());</script>\n\t\t</table>";
return $OUTPUT;
}
function save_inc($_POST)
{
# get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# connect to core DB
core_connect();
# get the income statement settings
$sql = "SELECT accid FROM accounts WHERE acctype = 'I' AND div = '" . USER_DIV . "'";
$incRslt = db_exec($sql) or errDie("Unable to retrieve income statement settings from the Database", SELF);
if (pg_numrows($incRslt) < 1) {
return "<center>There are no accounts under income on the income statement Settings table.<br>Please Set the Income Statement";
}
// Set up the Table to display in
$income = "<center><h3>Income Statement as at : " . date("d M Y") . "</h3>\n\t\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=75%>\n <tr><th width=70%>Account Name</th><th>Amount</th></tr>\n <tr><td colspan=3><h3>Income</h3></td></tr>";
# get account Balances
$tlinc = 0;
// total income credit
$i = 0;
while ($inc = pg_fetch_array($incRslt)) {
# get the balances (debit nad credit) from trial Balance
$sql = "SELECT * FROM trial_bal WHERE period='" . PRD_DB . "' AND accid = '{$inc['accid']}' AND div = '" . USER_DIV . "'";
$balRslt = db_exec($sql) or errDie("Unable to retrieve Account Balance information from the Database.", SELF);
$bal = pg_fetch_array($balRslt);
$total = sprint($bal['credit'] - $bal['debit']);
if ($zero == "no") {
if (intval($total == 0)) {
$i++;
continue;
}
}
$tlinc += $total;
$income .= "<tr class='" . bg_class() . "'><td>{$bal['accname']}</td><td align=center>" . CUR . " {$total}</td></tr>";
$i++;
}
# write totals for income
$income .= "<tr class='bg-odd'><td><b>Total<b></td><td align=center><b>" . CUR . " {$tlinc}</b></td></tr>";
# get the income statement settings
$sql = "SELECT accid FROM accounts WHERE acctype='E' AND div = '" . USER_DIV . "'";
$expRslt = db_exec($sql) or errDie("Unable to retrieve income statement Settings from the Database", SELF);
if (pg_numrows($expRslt) < 1) {
return "<center>There are no accounts under Expenditures on the income statement Settings table.<br>Please Set the Income Statement";
}
$income .= "<tr><td colspan=3><h3>Expenditure</h3></td></tr>";
# get account Balances for Expenditure
$tlexp = 0;
// total expenditures
$i = 0;
while ($exp = pg_fetch_array($expRslt)) {
# get the balances (debit nad credit) from trial Balance
$sql = "SELECT * FROM trial_bal WHERE period='" . PRD_DB . "' AND accid = '{$exp['accid']}' AND div = '" . USER_DIV . "'";
$balRslt = db_exec($sql) or errDie("Unable to retrieve Account Balance information from the Database.", SELF);
$bal = pg_fetch_array($balRslt);
$total = $bal['debit'] - $bal['credit'];
if ($zero == "no") {
if (intval($total == 0)) {
$i++;
continue;
}
}
$tlexp += $total;
// And increment the balance for expenditure
$income .= "<tr class='" . bg_class() . "'><td>{$bal['accname']}</td><td align=center>" . CUR . " {$total}</td></tr>";
$i++;
}
$income .= "<tr class='bg-odd'><td><b>Total<b></td><td align=center><b>" . CUR . " {$tlexp}</b></td></tr>\n <tr><td colspan=3><br></td></tr>";
# Calculate Profit/Loss
$income .= "<tr class='bg-even'><td><b>Nett Profit Carried Forward<b></td><td align=center colspan=2><b>" . CUR . " " . ($tlinc - $tlexp) . "</b></td></tr>\n\t\t<tr><td><br></td></tr>\n\t\t</table>";
$output = base64_encode($income);
core_connect();
$sql = "INSERT INTO save_income_stmnt(gendate, output, div) VALUES('" . date("Y-m-d") . "', '{$output}', '" . USER_DIV . "')";
$Rs = db_exec($sql) or errdie("Unable to save the Income Statement.");
$income .= "\n\t\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=20%>\n\t\t<tr><th>Quick Links</th></tr>\n\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t</table>";
return $income;
}
function write($frm)
{
extract($_REQUEST);
if ($frm->validate("write")) {
return confirm($frm);
}
pglib_transaction("BEGIN");
db_conn('cubit');
$user = USER_NAME;
$Sql = "SELECT * FROM assets WHERE (dep_month='yes' AND remaction IS NULL)";
$Rslt = db_exec($Sql) or errDie("Unable to access database.");
$cc = "";
$todate = mkdate($date_year, $date_month, $date_day);
$ttime = mktimefd($todate);
$refnum = getrefnum($todate);
while ($led = pg_fetch_array($Rslt)) {
if (empty($led["autodepr_date"])) {
$led["autodepr_date"] = $led["date"];
}
explodeDate($led["autodepr_date"], $date_year, $date_month, $date_day);
$ftime = mktime(0, 0, 0, $date_month, $date_day, $date_year);
$depmonths = 0;
while ($ftime < $ttime) {
++$depmonths;
$ftime = mktime(0, 0, 0, $date_month + $depmonths, $date_day, $date_year);
}
if ($depmonths == 0) {
continue;
}
$depperc = $led["dep_perc"];
$ml_perc = $depperc * ($depmonths % 12 / 12);
$years = ($depmonths - $depmonths % 12) / 12;
$baseamt = $led["amount"] - $led["accdep"];
$depamt = 0;
/* yearly depreciations */
for ($i = 1; $i <= $years; ++$i) {
$depamt += ($baseamt - $depamt) * ($depperc / 100);
}
/* monthly depreciation */
$depamt += ($baseamt - $depamt) * ($ml_perc / 100);
$sql = "SELECT * FROM assetgrp WHERE grpid = '{$led['grpid']}' AND div = '" . USER_DIV . "'";
$grpRslt = db_exec($sql);
$grp = pg_fetch_array($grpRslt);
writetrans($grp['depacc'], $grp['accdacc'], $todate, $refnum, $depamt, "{$led['des']} Depreciation");
db_connect();
$sql = "UPDATE assets SET accdep = (accdep + '{$depamt}'), autodepr_date='{$todate}'\r\n\t\t\t\tWHERE (id='{$led['id']}' AND div = '" . USER_DIV . "')";
db_exec($sql) or errdie("Could not update assets table.");
$snetval = $baseamt - $depamt;
$sdate = date("Y-m-d");
$sql = "INSERT INTO assetledger(assetid, asset, date, depamt, netval, div) \r\n\t\t\t\tVALUES ('{$led['id']}', '{$led['des']}', '{$todate}', '{$depamt}', '{$snetval}', '" . USER_DIV . "')";
db_exec($sql) or errdie("Could not write to asset ledger.");
$cc .= "CostCenter('ct', 'Asset Depreciation', '{$todate}', '{$led['des']} Depreciation', '{$depamt}', '');";
}
pglib_transaction("COMMIT");
$write = "\r\n\t<script> \r\n\t{$cc}\r\n\t</script>\r\n\t<table " . TMPL_tblDflts . " width='50%'>\r\n\t\t<tr>\r\n\t\t\t<th>Auto Asset Depreciation</th>\r\n\t\t</tr>\r\n\t\t<tr class='datacell'>\r\n\t\t\t<td>Asset Depreciation has calculated and recorded.</td>\r\n\t\t</tr>\r\n\t</table>";
return $write;
}
function save_bal()
{
# Get Owners Equity Sub Headings
$oesubRslt = undget("core", "*", "bal_sheet", "type", "OESUB' AND div = '11111111");
$sheet = "<center>\n\t<h3> Balance sheet for period : " . PRD_NAME . "<br><br>Date: " . date("d M Y") . " </h3>\n\t<table cellpadding='3' cellspacing='0' border=1 bordercolor='#000000' width=750>\n\t<tr><th colspan=3>Owners Equity</th></tr>";
# get accounts
$oebal = 0;
// OE Balance
while ($oesub = pg_fetch_array($oesubRslt)) {
$sheet .= "<tr><td colspan=3><b>{$oesub['value']}<b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='OEACC' AND ref = {$oesub['ref']} AND div = 11111111";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
list($topacc, $accnum) = explode("/", $acc['value']);
# Get balance
$query = "SELECT sum(credit) as credit,sum(debit) as debit FROM trial_bal WHERE topacc = '{$topacc}' AND accnum = '{$accnum}'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['credit'] - $accbal['debit'];
$oebal += $balance;
$balacc = getaccnum($acc['value']);
$sheet .= "<tr><td><blockquote><li>{$balacc['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
$oebal = sprint($oebal);
# print Net Income and OE Balance on last column
$netincome = getNetIncome();
$oebal += $netincome;
$sheet .= "<tr><td> <b>Net Income</b></td><td>" . CUR . " {$netincome}</td><td><br></td></tr>";
$sheet .= "<tr><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$oebal}</b></td></tr>";
# Get Assets Sub Headings
$abal = 0;
// Assets Balance
$asssubRslt = undget("core", "*", "bal_sheet", "type", "ASSSUB' AND div = '11111111");
$sheet .= "<tr><th colspan=3>Assets</th></tr>";
# get accounts
while ($asssub = pg_fetch_array($asssubRslt)) {
$sheet .= "<tr><td colspan=3><b>{$asssub['value']}<b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='ASSACC' AND ref = {$asssub['ref']} AND div = 11111111";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
list($topacc, $accnum) = explode("/", $acc['value']);
# Get Balance
$query = "SELECT sum(credit) as credit,sum(debit) as debit FROM trial_bal WHERE topacc = '{$topacc}' AND accnum = '{$accnum}'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['debit'] - $accbal['credit'];
// calc Balance
$abal += $balance;
$balacc = getaccnum($acc['value']);
$sheet .= "<tr><td><blockquote><li>{$acc['value']} - {$balacc['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
$abal = sprint($abal);
# print assets balance on last column
$sheet .= "<tr><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$abal}</b></td></tr>\n\t</table><br>";
$output = base64_encode($sheet);
core_connect();
$sql = "INSERT INTO save_bal_sheet(gendate, output, div) VALUES('" . date("Y-m-d") . "', '{$output}', '" . USER_DIV . "')";
$Rs = db_exec($sql) or errdie("Unable to save the Balance Sheet.");
$sheet .= "\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=25%>\n\t<tr><th>Quick Links</th></tr>\n\t<tr class=datacell><td align=center><a target=_blank href='../core/acc-new2.php'>Add account (New Window)</a></td></tr>\n\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
return $sheet;
}
function con_data($_POST)
{
# get vars
extract($_POST);
if (isset($back)) {
return view_data($_POST);
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($id, "num", 0, 100, "Invalid number.");
$v->isOk($depamt, "float", 1, 14, "Invalid Depreciation Amount.");
if ($netval < $depamt) {
$v->isOk("###", "float", 1, 1, "Error : Depreciation amount must not be more than the Net Value.");
}
$v->isOk($date, "date", 1, 14, "Invalid account open date.");
# display errors, if any
if ($v->isError()) {
$confirmCust = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirmCust .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirmCust .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirmCust;
}
db_conn('cubit');
$user = USER_NAME;
$Sql = "SELECT * FROM assets WHERE (id='{$id}' AND div = '" . USER_DIV . "')";
$Rslt = db_exec($Sql) or errDie("Unable to access database.");
if (pg_numrows($Rslt) < 1) {
return "Asset not Found";
}
$led = pg_fetch_array($Rslt);
# Get group
$sql = "SELECT * FROM assetgrp WHERE grpid = '{$led['grpid']}' AND div = '" . USER_DIV . "'";
$grpRslt = db_exec($sql);
$grp = pg_fetch_array($grpRslt);
# get last ref number
$refnum = getrefnum($date);
if ($led["dep_acc"]) {
$dep_acc = $led["dep_acc"];
} else {
// Maintain backwards compatibiltiy
$dep_acc = $grp["depacc"];
}
if ($led["accdep_acc"]) {
$accdep_acc = $led["accdep_acc"];
} else {
// Maintain backwards compatibiltiy
$accdep_acc = $grp["accdacc"];
}
# dt(depacc) ct(accdep)
writetrans($accdep_acc, $dep_acc, $date, $refnum, $depamt, "{$led['des']} Appreciation");
db_connect();
$sql = "UPDATE assets SET accdep = (accdep - '{$depamt}') WHERE (id='{$id}' AND div = '" . USER_DIV . "')";
$up = db_exec($sql) or errdie("Could not update assets table.");
$snetval = $netval + $depamt;
$sdate = date("Y-m-d");
$sql = "INSERT INTO assetledger(assetid, asset, date, depamt, netval, div) VALUES ('{$id}', '{$led['des']}', '{$sdate}', '-{$depamt}', '{$snetval}', '" . USER_DIV . "')";
$rec = db_exec($sql) or errdie("Could not write to asset ledger.");
#resort date
$cdarr = explode("-", $date);
$cyear = $cdarr[2];
$cmonth = $cdarr[1];
$cday = $cdarr[0];
$cdate = "{$cyear}-{$cmonth}-{$cday}";
$cc = "<script> CostCenter('dt', 'Asset Appreciation', '{$cdate}', '{$led['des']} Appreciation', '{$depamt}', ''); </script>";
$write = "\r\n\t\t\t\t{$cc}\r\n\t\t\t\t<table " . TMPL_tblDflts . " width='50%'>\r\n\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t<th>Asset Appreciation</th>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='datacell'>\r\n\t\t\t\t\t\t<td>Asset Appreciation has been recorded</td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t</table>\r\n\t\t\t\t<p>\r\n\t\t\t\t<table border=0 cellpadding='2' cellspacing='1'>\r\n\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t<th>Quick Links</th>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td><a href='asset-new.php'>New Asset</a></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t<td><a href='asset-view.php'>View Assets</a></td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\r\n\t\t\t\t</table>";
return $write;
}
/**
* does an employee ledger transaction
*
* @param int $empid employee id seq
* @param int $contra account id seq
* @param string $date
* @param int $ref reference number
* @param string $details description of transaction
* @param float $amount
* @param char $type D/C - debit/credit employee ledger
*/
function empledger($empid, $contra, $date, $ref, $details, $amount, $type)
{
$amount = abs($amount);
if (floatval($amount) == 0) {
return;
}
$edate = date("Y-m-d");
list($PRD_DB, $PRD_NAME) = getPRD($date);
list($CUR_PRD_DB, $CUR_PRD_NAME) = getPRD($edate);
db_conn($PRD_DB);
if ($type == "d") {
$damount = $amount;
$camount = 0;
# Get balances
$idRs = get($PRD_DB, "max(id)", "empledger", "empid", $empid);
$id = pg_fetch_array($idRs);
if ($id['max'] != 0) {
$balRs = get($PRD_DB, "cbalance,dbalance", "empledger", "id", $id['max']);
$bal = pg_fetch_array($balRs);
$bal['cbalance'] += 0;
$bal['dbalance'] += $amount;
} else {
$balRs = get("cubit", "balance", "employees", "empnum", $empid);
$bal = pg_fetch_array($balRs);
$bal['balance'] += 0;
if ($bal['balance'] < 0) {
$bal['dbalance'] = $bal['balance'] * -1;
$bal['cbalance'] = 0;
} else {
$bal['cbalance'] = $bal['balance'];
$bal['dbalance'] = 0;
}
//$bal['dbalance'] += $amount;
}
# Total balance changes
if ($bal['dbalance'] >= $bal['cbalance']) {
$bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
$bal['cbalance'] = 0;
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
$bal['dbalance'] = 0;
}
db_conn($PRD_DB);
$sql = "\n\t\t\tINSERT INTO empledger (\n\t\t\t\tempid, contra, edate,sdate, ref, des, debit, dbalance, cbalance, div\n\t\t\t) VALUES (\n\t\t\t\t'{$empid}', '{$contra}', '{$date}','{$edate}', '{$ref}', '{$details}', '{$amount}', {$bal['dbalance']}, '{$bal['cbalance']}', '" . USER_DIV . "'\n\t\t\t)";
} else {
$camount = $amount;
$damount = 0;
# Get balances
$idRs = get($PRD_DB, "max(id)", "empledger", "empid", $empid);
$id = pg_fetch_array($idRs);
if ($id['max'] != 0) {
$balRs = get($PRD_DB, "cbalance,dbalance", "empledger", "id", $id['max']);
$bal = pg_fetch_array($balRs);
$bal['cbalance'] += 0;
$bal['dbalance'] += 0;
$bal['cbalance'] += $amount;
} else {
$balRs = get("cubit", "balance", "employees", "empnum", $empid);
$bal = pg_fetch_array($balRs);
$bal['balance'] += 0;
if ($bal['balance'] < 0) {
$bal['dbalance'] = $bal['balance'] * -1;
$bal['cbalance'] = 0;
} else {
$bal['cbalance'] = $bal['balance'];
$bal['dbalance'] = 0;
}
}
# 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;
}
db_conn($PRD_DB);
$sql = "INSERT INTO empledger(empid, contra, edate,sdate, ref, des, credit, dbalance, cbalance, div)\n\t\tVALUES('{$empid}', '{$contra}', '{$date}','{$edate}', '{$ref}', '{$details}', '{$amount}', '{$bal['dbalance']}', '{$bal['cbalance']}', '" . USER_DIV . "')";
}
$rs = db_exec($sql) or errdie("Unable to insert ledger entry to the Database.");
db_conn('cubit');
$Sl = "SELECT * FROM users WHERE username='******' AND div='" . USER_DIV . "' AND state='p'";
$Ri = db_exec($Sl);
//if(pg_num_rows($Ri)>0) {
$data = pg_fetch_array($Ri);
// db_conn('core');
//
// $Sl="SELECT * FROM active";
// $Ri=db_exec($Sl) or errDie("Unablet to get data.");
//
// $data=pg_fetch_array($Ri);
global $PRDMON, $MONPRD;
if (true || $MONPRD[$PRD_DB] < $MONPRD[$CUR_PRD_DB]) {
for ($iPRD = $MONPRD[$PRD_DB] + 1; $iPRD <= 12; ++$iPRD) {
if ($type == "d") {
$d_extra = $amount;
$c_extra = 0;
} else {
$d_extra = 0;
$c_extra = $amount;
}
$d_extra = $c_extra = 0;
db_conn($PRDMON[$iPRD]);
$sql = "UPDATE empledger SET dbalance=dbalance+'{$damount}',cbalance=cbalance+'{$camount}'\n\t\t\t\tWHERE empid='{$empid}'";
$Ri = db_exec($sql) or errDie("Unable to update suppledeger.");
if (pg_affected_rows($Ri) <= 0) {
$sql = "INSERT INTO empledger(empid, contra, edate,sdate, ref, des,\n\t\t\t\t\t\tcredit, dbalance, cbalance, div)\n\t\t\t\t\tVALUES('{$empid}', '0', '{$date}','{$edate}', '{$ref}', 'Balance',\n\t\t\t\t\t\t'0', '{$bal['dbalance']}', '{$bal['cbalance']}', '" . USER_DIV . "')";
db_exec($sql) or errDie("Error updating employee ledger (PINS).");
} else {
//$Sl="SELECT * FROM empledger WHERE empid='$empid'";
//$Rl=db_exec($Sl) or errDie("Unable to get ledger.");
//while($cdata=pg_fetch_array($Rl)) {
// if($cdata['dbalance']>=$cdata['cbalance']) {
$sql = "UPDATE empledger SET dbalance=dbalance-cbalance,cbalance=0\n\t\t\t\t\tWHERE empid='{$empid}' AND dbalance>=cbalance";
db_exec($sql) or errDie("Unable to update emp ledger.");
// } elseif($cdata['cbalance']>$cdata['dbalance']) {
$sql = "UPDATE empledger SET cbalance=cbalance-dbalance,dbalance=0\n\t\t\t\t\tWHERE empid='{$empid}' AND cbalance>dbalance";
db_exec($sql) or errDie("Unable to update emp ledger.");
// }
//}
}
}
}
if (PRD_STATE == "py") {
$audit_db = YR_NAME . "_audit";
$actyear = PYR_NAME;
} else {
$audit_db = "audit";
$actyear = YR_NAME;
}
db_conn($audit_db);
if ($type == "d") {
$sql = "INSERT INTO " . $PRD_NAME . "_empledger(empid, contra, edate,sdate, ref, descript, debit,\n\t\t\t\tdbalance, cbalance, div, actyear)\n\t\t\tVALUES('{$empid}', '{$contra}', '{$date}','{$edate}', '{$ref}', '{$details}', '{$amount}',\n\t\t\t\t'{$bal['dbalance']}', '{$bal['cbalance']}', '" . USER_DIV . "', '{$actyear}')";
} else {
$sql = "INSERT INTO " . $PRD_NAME . "_empledger(empid, contra, edate,sdate, ref, descript, credit,\n\t\t\t\tdbalance, cbalance, div, actyear)\n\t\t\tVALUES('{$empid}', '{$contra}', '{$date}','{$edate}', '{$ref}', '{$details}', '{$amount}',\n\t\t\t\t'{$bal['dbalance']}', '{$bal['cbalance']}', '" . USER_DIV . "', '{$actyear}')";
}
db_exec($sql) or errDie("Unable to insert aduit");
}
function trial_save()
{
// Set up table to display in
$OUTPUT = "\n <center>\n <h3>Trial Balance as at : " . date("d M Y") . "</h3>\n\t\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=450>\n <tr><th>Account Number</th><th>Account Name</th><th>Debit</th><th>Credit</th></tr>";
// Connect to database
core_connect();
$sql = "SELECT * FROM trial_bal ORDER BY topacc, accnum ASC";
$accRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve account details from database.", SELF);
$numrows = pg_numrows($accRslt);
if ($numrows < 1) {
$OUTPUT = "There are no Accounts yet in Cubit.";
require "../template.php";
}
# display all Accounts
$i = 0;
$tldebit = 0;
$tlcredit = 0;
while ($acc = pg_fetch_array($accRslt)) {
$i++;
if (intval($acc['debit']) == 0 && intval($acc['credit']) == 0) {
continue;
}
$OUTPUT .= "<tr class='" . bg_class() . "'><td>{$acc['topacc']}/{$acc['accnum']}</td><td>{$acc['accname']}</td>";
if (intval($acc['debit']) == 0) {
$OUTPUT .= "<td align=center> - </td>";
} else {
$OUTPUT .= "<td align=center>" . CUR . " {$acc['debit']}</td>";
}
if (intval($acc['credit']) == 0) {
$OUTPUT .= "<td align=center> - </td>";
} else {
$OUTPUT .= "<td align=center>" . CUR . " {$acc['credit']}</td>";
}
$OUTPUT .= "</tr>";
$tldebit += $acc['debit'];
$tlcredit += $acc['credit'];
}
$OUTPUT .= "<tr class='" . bg_class() . "'><td colspan=2><b>Total</b></td><td align=center><b>" . CUR . " {$tldebit}</b></td><td align=center><b>" . CUR . " {$tlcredit}</b></td></tr>\n\t\t</table><br>";
$output = base64_encode($OUTPUT);
core_connect();
$sql = "INSERT INTO save_trial_bal(gendate, output) VALUES('" . date("Y-m-d") . "', '{$output}')";
$Rs = db_exec($sql) or errdie("Unable to save the Trial Balance.");
return true;
}
/**
* @ignore
*/
function lbalance($id)
{
$balRs = get(PRD_DB, "cbalance, dbalance", "ledger", "id", $id);
$bal = pg_fetch_array($balRs);
# Total balance changes
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = 0;
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = 0;
} else {
$bal['cbalance'] = 0;
$bal['dbalance'] = 0;
}
$sql = "UPDATE ledger SET dbalance = '{$bal['dbalance']}', cbalance = '{$bal['cbalance']}' WHERE id = '{$id}'";
$rs = db_exec($sql) or errdie("Unable to update ledger entry on the Database.");
}
