function print_sheet()
{
$OUTPUT = clean_html(financialStatements::balsheet($_POST));
switch ($_POST["key"]) {
case ct("Print"):
require "../tmpl-print.php";
break;
case ct("Save"):
db_conn("core");
$sql = "INSERT INTO save_bal_sheet (output, gendate, div) VALUES ('" . base64_encode($OUTPUT) . "', current_date, '" . USER_DIV . "')";
$svincRslt = db_exec($sql) or errDie("Unable to save the balance sheet to Cubit.");
return "\n\t\t\t\t<li>Balance Sheet has been successfully saved to Cubit.</li>\n\t\t\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t\t\t<tr><th>Quick Links</th></tr>\n\t\t\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\t\t<tr class='datacell'><td align='center'><a href='index-reports.php'>Financials</a></td></tr>\n\t\t\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\t\t<tr class='datacell'><td align='center'><a href='../main.php'>Main Menu</td></tr>\n\t\t\t\t</table>";
break;
case ct("Export to Spreadsheet"):
require_lib("xls");
StreamXLS("balance_sheet", $OUTPUT);
break;
}
}
function write()
{
if (PRD_STATE == 'py') {
return "<center><li class='err'>Please update your transaction year before closing financial year: Click <a href='../set-period-use.php'>here</a> to do so.</li></center>";
}
// Check if last database has not been reached
if (YR_DB == "yr10") {
$OUTPUT = "<center>Warning : The System has reached its Last year of operation, Please contact the support team to reset.";
require "template.php";
}
$i = substr(YR_DB, 2) + 1;
$nextyr = "yr" . $i;
$sdate = DATE_STD;
pglib_transaction("BEGIN");
global $PRDMON;
$stmnt = financialStatements::trialbal(array("heading_3" => "Trial Balance: Year End", "month_to" => $PRDMON[12]), true);
save_statement($stmnt, "trial_bal");
$stmnt = financialStatements::incomestmnt(array("customized" => true, "this_year_year_to_date" => true, "heading_3" => "Income Statement: Year End", "budget" => true, "this_year_budget" => true, "month_to" => $PRDMON[12]), true);
save_statement($stmnt, "income_stmnt");
$stmnt = financialStatements::balsheet(array("customized" => true, "this_year_year_to_date" => true, "heading_3" => "Balance Sheet: Year End", "month_to" => $PRDMON[12]), true);
save_statement($stmnt, "bal_sheet");
# Copy balance sheet table
core_connect();
$sql = "SELECT * FROM bal_sheet";
$balSheet = db_exec($sql) or errDie("Could not copy Balances to year DB", SELF);
while ($bal = pg_fetch_array($balSheet)) {
db_conn(YR_DB);
$sql = "INSERT INTO bal_sheet (type, ref, value, div) VALUES ('{$bal['type']}', '{$bal['ref']}', '{$bal['value']}', '{$bal['div']}')";
$inRslt = db_exec($sql) or print $sql;
}
core_connect();
$sql = "SELECT * FROM core.trial_bal WHERE period='12'";
$trialBal = db_exec($sql) or errDie("Could not copy Balances to year DB", SELF);
while ($bal = pg_fetch_array($trialBal)) {
$sql = "\n\t\t\tINSERT INTO " . YR_DB . ".year_balance (\n\t\t\t\taccid, topacc, accnum, accname, debit, credit, div\n\t\t\t) VALUES (\n\t\t\t\t'{$bal['accid']}', '{$bal['topacc']}', '{$bal['accnum']}', '{$bal['accname']}', '{$bal['debit']}', '{$bal['credit']}', '{$bal['div']}'\n\t\t\t)";
$inRslt = db_exec($sql) or errDie("Failed to store year balance.");
}
//$sql = "CREATE TABLE \"".YR_DB."\".stkledger AS SELECT * FROM core.stkledger";
//$rslt = db_exec_safe($sql);
// make a copy from the trial balance
$sql = "DROP TABLE \"" . YR_DB . "\".trial_bal";
//$rslt = db_exec_safe($sql);
$sql = "CREATE TABLE \"" . YR_DB . "\".trial_bal AS SELECT * FROM core.trial_bal";
$rslt = db_exec_safe($sql);
// recreate the trial balance actual view
$sql = "\n\t\tCREATE OR REPLACE VIEW \"" . YR_DB . "\".trial_bal_actual AS\n\t\tSELECT tb.accid, tb.topacc, tb.accnum, tb.accname, tb.vat, tb.div, tb.acctype, \n\t\t\tCASE\n\t\t\t\tWHEN tb.period = 1 THEN tb.debit\n\t\t\t\tELSE tb.debit - atb.debit\n\t\t\tEND AS debit,\n\t\t\tCASE\n\t\t\t\tWHEN tb.period = 1 THEN tb.credit\n\t\t\t\tELSE tb.credit - atb.credit\n\t\t\tEND AS credit, tb.month, tb.period\n\t\tFROM \"" . YR_DB . "\".trial_bal tb LEFT JOIN \"" . YR_DB . "\".trial_bal atb\n\t\t\tON tb.period = (atb.period::int + 1) AND tb.accid = atb.accid;";
db_exec_safe($sql);
/* fetch customer balances */
$custs = qryCustomer(false, "cusnum");
$custdebit = array();
$custcredit = array();
while ($cd = $custs->fetch_array()) {
$qry = new dbSelect("custledger", $PRDMON['12'], grp(m("cols", "cbalance, dbalance"), m("where", "cusnum='{$cd['cusnum']}'"), m("order", "id DESC"), m("limit", 1)));
$qry->run();
if ($qry->num_rows() <= 0) {
$custcredit[$cd["cusnum"]] = 0;
$custdebit[$cd["cusnum"]] = 0;
} else {
$qry->fetch_array();
$custcredit[$cd["cusnum"]] = $qry->d["cbalance"];
$custdebit[$cd["cusnum"]] = $qry->d["dbalance"];
}
}
/* fetch supplier balances */
$supps = qrySupplier(false, "supid");
$suppdebit = array();
$suppcredit = array();
while ($sd = $supps->fetch_array()) {
$qry = new dbSelect("suppledger", $PRDMON['12'], grp(m("cols", "cbalance, dbalance"), m("where", "supid='{$sd['supid']}'"), m("order", "id DESC"), m("limit", 1)));
$qry->run();
if ($qry->num_rows() <= 0) {
$suppcredit[$sd["supid"]] = 0;
$suppdebit[$sd["supid"]] = 0;
} else {
$qry->fetch_array();
$suppcredit[$sd["supid"]] = $qry->d["cbalance"];
$suppdebit[$sd["supid"]] = $qry->d["dbalance"];
}
}
/* copy the inventory ledger */
for ($i = 1; $i <= 12; ++$i) {
$mname = strtolower(getMonthName($i));
$sql = "CREATE TABLE audit.{$mname}_stkledger \n\t\t\t\tAS \n\t\t\t\tSELECT * FROM \"{$i}\".stkledger";
db_exec($sql) or errDie("Error copying inventory ledger (P{$i}).");
}
// Empty All Period Databases
for ($i = 1; $i <= 14; $i++) {
db_conn($i);
$sql = "TRUNCATE TABLE transect;";
$sql .= "TRUNCATE TABLE ledger;";
$sql .= "TRUNCATE TABLE custledger;";
$sql .= "TRUNCATE TABLE suppledger;";
$sql .= "TRUNCATE TABLE empledger;";
db_exec($sql) or errDie("Unable to empty Period databases", SELF);
}
$qryi = new dbUpdate();
recreateAudit();
/* FP AUDIT FIX
for ($p = 1; $p <= 12; ++$p) {
$monnum = $PRDMON[$i];
$monname = strtolower(getMonthName($i));
}
*/
/* create the customer ledger balance entries */
$custs = qryCustomer(false, "cusnum");
while ($cd = $custs->fetch_array()) {
for ($i = 1; $i <= 12; ++$i) {
$cols = grp(m("cusnum", $cd["cusnum"]), m("contra", 0), m("edate", $sdate), m("sdate", raw("CURRENT_DATE")), m("eref", 0), m("descript", "Balance"), m("credit", 0), m("debit", 0), m("cbalance", $custcredit[$cd["cusnum"]]), m("dbalance", $custdebit[$cd["cusnum"]]), m("div", USER_DIV));
$qryi->setTable("custledger", "{$i}");
$qryi->setOpt($cols);
$qryi->run(DB_INSERT);
/* audit customer ledger */
$cols = grp(m("cusnum", $cd["cusnum"]), m("contra", 0), m("edate", $sdate), m("sdate", raw("CURRENT_DATE")), m("eref", 0), m("descript", "Balance"), m("credit", 0), m("debit", 0), m("cbalance", $custcredit[$cd["cusnum"]]), m("dbalance", $custdebit[$cd["cusnum"]]), m("div", USER_DIV), m("actyear", YR_NAME));
$qryi->setTable(getMonthName($i) . "_custledger", "audit");
$qryi->setOpt($cols);
$qryi->run(DB_INSERT);
}
}
/* create the supplier ledger balance entries */
$supps = qrySupplier(false, "supid");
while ($sd = $supps->fetch_array()) {
for ($i = 1; $i <= 12; ++$i) {
$qryi->setTable("suppledger", "{$i}");
$cols = grp(m("supid", $sd["supid"]), m("contra", "0"), m("edate", $sdate), m("sdate", raw("CURRENT_DATE")), m("eref", "0"), m("descript", "Balance"), m("credit", "0"), m("debit", "0"), m("div", USER_DIV), m("cbalance", $suppcredit[$sd["supid"]]), m("dbalance", $suppdebit[$sd["supid"]]));
$qryi->setCols($cols);
$qryi->run(DB_INSERT);
}
}
/* create the stock ledger entries */
$stock = qryStock(false, "stkid, stkcod, stkdes, units, csamt");
while ($stk = $stock->fetch_array()) {
for ($i = 1; $i <= 12; ++$i) {
$qryi->setTable("stkledger", "{$i}");
$cols = grp(m("stkid", $stk["stkid"]), m("stkcod", $stk["stkcod"]), m("stkdes", $stk["stkdes"]), m("trantype", "bal"), m("edate", $sdate), m("qty", $stk["units"]), m("csamt", $stk["csamt"]), m("balance", $stk["csamt"]), m("bqty", $stk["units"]), m("details", "Balance"), m("div", USER_DIV), m("yrdb", $nextyr));
$qryi->setCols($cols);
$qryi->run(DB_INSERT);
}
}
/* do the retained income entries for all branches */
$sql = "SELECT div FROM cubit.branches";
$branRs = db_exec($sql) or errDie("Could not access branches table.");
while ($bran = pg_fetch_array($branRs)) {
fintran($bran['div']);
}
/* create the ledger account balance entries */
for ($i = 1; $i <= 12; ++$i) {
$periodname = getMonthName($i);
$sql = "\n\t\t\tINSERT INTO " . YR_DB . ".{$periodname} (\n\t\t\t\taccid, topacc, accnum, accname, debit, credit, div\n\t\t\t) SELECT accid, topacc, accnum, accname, debit, credit, div\n\t\t\t\tFROM core.trial_bal WHERE month='1'";
db_exec($sql) or errDie("Error creating ledger balances (1)");
$sql = "\n\t\t\tINSERT INTO \"{$i}\".openbal (\n\t\t\t\taccid, accname, debit, credit, div\n\t\t\t) SELECT accid, accname, debit, credit, div\n\t\t\t\tFROM core.trial_bal WHERE month='1'";
db_exec($sql) or errDie("Error creating ledger balances (2)");
$sql = "\n\t\t\tINSERT INTO \"{$i}\".ledger (\n\t\t\t\tacc, contra, edate, eref, descript, credit, debit, div, caccname, ctopacc, caccnum, cbalance, dbalance\n\t\t\t) SELECT accid, accid, CURRENT_DATE, '0', 'Balance', '0', '0', div, accname, topacc, accnum, credit, debit\n\t\t\t\tFROM core.trial_bal WHERE month='1'";
db_exec($sql) or errDie("Error creating ledger balances (3)");
}
/* close and select new year */
selectNextYear($nextyr);
/* mark year as closed */
$sql = "UPDATE core.year SET closed = 'y' WHERE yrdb = '" . YR_DB . "'";
$rslt = db_exec($sql) or errDie("Could not Set Next Year Database and Name", SELF);
pglib_transaction("COMMIT");
// Display
$write = "<center><h3> Current Year has been closed </h3>\n\t<b>( i ) The next Year has been activated ( i )</b></center>" . mkQuickLinks();
return $write;
}