function print_report()
{
$OUTPUT = clean_html(financialStatements::incomestmnt($_POST));
switch ($_POST["key"]) {
case ct("Print"):
require "../tmpl-print.php";
break;
case ct("Save"):
db_conn("core");
$sql = "INSERT INTO save_income_stmnt (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 "<li class='err'>Income statement has been successfully saved to Cubit.</li>\n\t\t\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=25%>\n\t\t\t\t<tr><th>Quick Links</th></tr>\n\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<tr class=datacell><td align=center><a href='index-reports.php'>Financials</a></td></tr>\n\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<tr class=datacell><td align=center><a href='../main.php'>Main Menu</td></tr>\n\t\t\t</table>";
break;
case ct("Export to Spreadsheet"):
require_lib("xls");
StreamXLS("income_statement", $OUTPUT);
break;
}
}
function print_report()
{
$OUTPUT = clean_html(financialStatements::trialbal($_POST));
switch ($_POST["key"]) {
case ct("Print"):
require "../tmpl-print.php";
break;
case ct("Save"):
db_conn("core");
$sql = "INSERT INTO save_trial_bal (output, gendate, div) VALUES ('" . base64_encode($OUTPUT) . "', current_date, '" . USER_DIV . "')";
$svincRslt = db_exec($sql) or errDie("Unable to save the trial balance to Cubit.");
return "\n\t\t\t\t<li>Trial Balance has been successfully saved to Cubit.</li>\n\t\t\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td align='center'><a target=_blank href='../core/acc-new2.php'>Add account (New Window)</a></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td align='center'><a href='index-reports.php'>Financials</a></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td align='center'><a href='index-reports-stmnt.php'>Current Year Financial Statements</a></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>";
break;
case ct("Export to Spreadsheet"):
require_lib("xls");
StreamXLS("trial_balance", $OUTPUT);
break;
}
}
/**
* Generates a balance sheet
*
* @param array $VARS GET/POST vars
* @param bool $pure true if quicklinks/forms/stuff should be excluded
* @return string
*/
static function balsheet($VARS = false, $pure = false)
{
if ($VARS === false) {
$VARS = array();
}
extract($VARS);
// Default values
$fields = array();
$fields["heading_1"] = COMP_NAME;
$fields["heading_2"] = date("d/m/Y");
$fields["heading_3"] = "Balance Sheet";
$fields["heading_4"] = "Prepared by: " . USER_NAME;
$fields["viewyear"] = "core";
$fields["capital_employed_out"] = "Capital Employed";
$fields["employment_of_capital_out"] = "Employment of Capital";
$fields["month_from"] = (int) date("m");
$fields["month_to"] = (int) date("m");
$fields["zero_balance"] = "";
foreach ($fields as $var_name => $value) {
if (!isset(${$var_name})) {
${$var_name} = $value;
}
}
$cols["this_year_movement_to_date"] = true;
$cols["this_year_year_to_date"] = true;
$cols["budget"] = true;
$cols["this_year_budget"] = true;
$cols["last_year_same_month"] = false;
$cols["last_year_year_to_date"] = true;
foreach ($cols as $fname => $v) {
if (!isset(${$fname}) && isset($customized)) {
${$fname} = false;
} else {
if (isset($customized)) {
${$fname} = true;
} else {
if (!isset(${$fname})) {
${$fname} = $v;
}
}
}
}
// Current Profit / Loss -------------------------------------------------
// Initialize output variables
$fixed_asset = "";
$investments = "";
$other_fixed_asset = "";
$current_asset = "";
$share_capital = "";
$retained_income = "";
$shareholders_loan = "";
$non_current_liability = "";
$long_term_borrowing = "";
$other_long_term_liability = "";
$current_liability = "";
$tax = "";
// Initialize the totals
$total = array("fixed_asset" => 0.0, "investments" => 0.0, "other_fixed_asset" => 0.0, "current_asset" => 0.0, "share_capital" => 0.0, "retained_income" => 0.0, "shareholders_loan" => 0.0, "non_current_liability" => 0.0, "long_term_borrowing" => 0.0, "other_long_term_liability" => 0.0, "current_liability" => 0.0, "tax" => 0.0, "TOTAL" => array("assets", "equity"));
// For the current profit/loss at retained income ------------------------
if (empty($zero_balance)) {
$zb_sql = "(debit!=0 OR credit!=0)";
} else {
$zb_sql = "(true)";
}
$zb_sql = "(true)";
/* hard code so only one month is used */
$month_from = $month_to;
/* hard code so column isn't displayed */
$this_year_budget = false;
//$this_year_year_to_date = false;
// Retrieve the current year from Cubit
global $PRDMON, $MONPRD;
if ($viewyear == "core") {
$sql = "SELECT yrname FROM core.active";
} else {
$sql = "SELECT yrname FROM core.year WHERE yrdb='{$viewyear}'";
}
$rslt = db_exec($sql) or errDie("Unable to retrieve current year from Cubit.");
$year_out = substr(pg_fetch_result($rslt, 0), 1) - (int) ($PRDMON[1] > 1);
if ($month_to < $PRDMON[1]) {
++$year_out;
}
$month_from_out = getMonthName($month_from);
$month_to_out = getMonthName($month_to);
if ($viewyear == "core") {
$last_year_schema = "yr" . (substr(YR_DB, 2) - 1);
} else {
$last_year_schema = "yr" . (substr($viewyear, 2) - 1);
}
$month_to_name = strtolower($month_to_out);
/* create the month range sql */
if ($month_from > $month_to) {
$month_range = "(month >= '{$month_from}' OR month <= '{$month_to}')";
$budget_month_range = "(prd >= '{$month_from}' OR prd <= '{$month_to}')";
} else {
$month_range = "(month >= '{$month_from}' AND month <= '{$month_to}')";
$budget_month_range = "(prd >= '{$month_from}' AND prd <= '{$month_to}')";
}
if (!isset($key)) {
$key = "";
}
if ($pure === false) {
switch ($key) {
case ct("Print"):
case ct("Save"):
$pure = true;
break;
case ct("Export to Spreadsheet"):
define("MONEY_NUMERIC", true);
$pure = true;
break;
default:
$pure = false;
break;
}
}
if ($pure) {
// Retrieve the notes
db_conn("cubit");
$sql = "SELECT * FROM saved_bs_accounts WHERE note!=''";
$rslt = db_exec($sql) or errDie("Unable to retrieve notes from Cubit.");
$i = 0;
$notes = array();
while ($note_data = pg_fetch_array($rslt)) {
$i++;
$notes["{$note_data['accid']}"] = $i;
}
}
// -----------------------------------------------------------------------
$ar_cats = array("fixed_asset" => "Fixed Assets", "investments" => "Investments", "other_fixed_asset" => "Other Fixed Assets", "current_asset" => "Current Assets", "share_capital" => "Share Capital", "retained_income" => "Retained Income", "shareholders_loan" => "Shareholders Loan", "non_current_liability" => "Non-current Liabilities", "long_term_borrowing" => "Long Term Borrowings", "other_long_term_liability" => "Other Long Term Liabilities", "current_liability" => "Current Liabilities");
$ar_assets = array("fixed_asset", "investments", "other_fixed_asset", "current_asset");
$ar_equity = array("share_capital", "retained_income", "shareholders_loan", "non_current_liability", "long_term_borrowing", "other_long_term_liability", "current_liability");
foreach ($ar_assets as $var_name) {
${$var_name} = "";
}
foreach ($ar_equity as $var_name) {
${$var_name} = "";
}
$curr_month = date("m");
/* calculate retained income */
if ($last_year_schema == "yr0") {
$last_year_same_month = false;
$last_year_year_to_date = false;
$prevyear_profit_loss_total = 0;
$prevyear_profit_loss_total_ytd = 0;
} else {
$prevyear_profit_loss_total = financialStatements::balsheet_GetProfitLoss(false, $month_to, $last_year_schema);
$prevyear_profit_loss_total_ytd = financialStatements::balsheet_GetProfitLoss(true, $PRDMON[12], $last_year_schema);
}
$current_profit_loss_total = -financialStatements::balsheet_GetProfitLoss(false, $month_to, $viewyear);
$current_profit_loss_total_ytd = financialStatements::balsheet_GetProfitLoss(true, $month_to, $viewyear);
/* calculate movement */
$mon0_profit_loss_total = financialStatements::balsheet_GetProfitLoss(true, 0, $viewyear);
$current_profit_loss_total_mtd = 0 - ($current_profit_loss_total_ytd - $mon0_profit_loss_total);
//$totals["equity"] += $current_profit_loss_total;
//$totals["tymtd"] += $current_profit_loss_total_mtd;
//$totals["tyytd"] += $current_profit_loss_total_ytd;
//if ($last_year_same_month) $totals["lysm"] += $prevyear_profit_loss_total;
//if ($last_year_year_to_date) $totals["lyytd"] += $prevyear_profit_loss_total_ytd;
/* calculate retained income budget */
$ri_calc = new dbQuery(DB_SQL, "SELECT SUM(CASE WHEN budtype='inc' THEN (amt) ELSE (amt*-1) END) AS amt\n\t\t\t\tFROM cubit.budgets b JOIN cubit.buditems bi ON(bi.budid=b.budid)\n\t\t\t\tWHERE b.budfor='acc' AND (b.budtype='inc' OR b.budtype='exp') AND bi.prd='{$month_to}'");
$ri_calc->run();
$retained_income_budget = $ri_calc->fetch_result(0, 0);
//$totals["budget"] += $retained_income_budget;
// Retrieve saved balance sheet information
//db_conn("cubit");
//$sql = "SELECT * FROM saved_bs_accounts";
//$tb_rslt = db_exec($sql) or errDie("Unable to retrieve accounts list from Cubit.");
//if (pg_num_rows($tb_rslt) == 0 || !isset($acc_view)) {
db_conn($viewyear);
$sql = "SELECT DISTINCT accid, topacc, accnum, accname FROM trial_bal WHERE div='" . USER_DIV . "' AND {$month_range} AND {$zb_sql}";
$tb_rslt = db_exec($sql) or errDie("Unable to retrieve trial balance information from Cubit.");
//}
$assets_out = "";
$equity_out = "";
$report_out = "";
$tymtd_out = "";
$tyytd_out = "";
$budget_out = "";
$tybudget_out = "";
$lysm_out = "";
$lyytd_out = "";
while ($tb_data = pg_fetch_array($tb_rslt)) {
db_conn("core");
$sql = "SELECT * FROM accounts WHERE accid='{$tb_data['accid']}'";
$acc_rslt = db_exec($sql) or errDie("Unable to retrieve account information from Cubit.");
$acc_data = pg_fetch_array($acc_rslt);
if (isset($tb_data["toptype"])) {
$acc_data["toptype"] = $tb_data["toptype"];
}
if ($acc_data["acctype"] == "B") {
if (financialStatements::balsheet_type($acc_data["toptype"]) == "assets") {
$assets[$acc_data["toptype"]][] = $acc_data["accid"];
} elseif (financialStatements::balsheet_type($acc_data["toptype"]) == "equity") {
$equity[$acc_data["toptype"]][] = $acc_data["accid"];
}
}
}
$totals["assets"] = 0.0;
$totals["lysm"] = 0.0;
$totals["tymtd"] = 0.0;
$totals["tyytd"] = 0.0;
$totals["lyytd"] = 0.0;
$totals["budget"] = 0.0;
$totals["tybudget"] = 0.0;
/* ASSETS - CAPITAL EMPLOYED */
if (isset($assets)) {
foreach ($assets as $toptype => $arlv2) {
foreach ($assets[$toptype] as $accid) {
/* determines whether a figure in any of the accounts */
$disp_acc = false;
db_conn($viewyear);
$sql = "SELECT * FROM trial_bal_actual\n\t\t\t\t\t\t\tWHERE accid='{$accid}' AND month='{$month_to}' AND {$zb_sql}";
$tb_rslt = db_exec($sql) or errDie("Unable to retrieve account information from Cubit.");
$tb_data = pg_fetch_array($tb_rslt);
// Retrieve this year, movement to date
if ($this_year_movement_to_date) {
$tymtd = array();
$tymtd["debit"] = 0;
$tymtd["credit"] = 0;
db_conn($viewyear);
/* current year, year to date */
$sql = "SELECT debit,credit FROM trial_bal\n\t\t\t\t\t\t\t\tWHERE accid='{$accid}' AND month='{$month_to}' AND {$zb_sql} LIMIT 1";
$tymtd_rslt = db_exec($sql) or errDie("Unable to retrieve this year, year to date information from Cubit.");
while ($tymtd_data = pg_fetch_array($tymtd_rslt)) {
$tymtd["debit"] += $tymtd_data["debit"];
$tymtd["credit"] += $tymtd_data["credit"];
}
/* deduct previous year end of year amounts */
$sql = "SELECT debit,credit FROM trial_bal\n\t\t\t\t\t\t\t\tWHERE accid='{$accid}' AND month='0' AND {$zb_sql} LIMIT 1";
$tymtd_rslt = db_exec($sql) or errDie("Unable to retrieve this year, year to date information from Cubit.");
while ($tymtd_data = pg_fetch_array($tymtd_rslt)) {
$tymtd["debit"] -= $tymtd_data["debit"];
$tymtd["credit"] -= $tymtd_data["credit"];
}
$amt = financialStatements::balsheet_calculate($toptype, $tymtd["debit"], $tymtd["credit"]);
$tymtd_out = "\n\t\t\t\t\t\t\t<td align='right' width='10%'>\n\t\t\t\t\t\t\t\t" . fsmoney($amt) . "\n\t\t\t\t\t\t\t</td>";
if ($amt != 0) {
$disp_acc = true;
}
$totals["tymtd"] += $amt;
} else {
$tymtd_out = "";
}
// Retrieve this year, year to date
if ($this_year_year_to_date) {
db_conn($viewyear);
$sql = "SELECT debit,credit FROM trial_bal\n\t\t\t\t\t\t\t\tWHERE accid='{$accid}' AND month='{$month_to}' AND {$zb_sql} LIMIT 1";
$tyytd_rslt = db_exec($sql) or errDie("Unable to retrieve this year, year to date information from Cubit.");
$tyytd = array();
$tyytd["debit"] = 0;
$tyytd["credit"] = 0;
while ($tyytd_data = pg_fetch_array($tyytd_rslt)) {
$tyytd["debit"] += $tyytd_data["debit"];
$tyytd["credit"] += $tyytd_data["credit"];
}
$amt = financialStatements::balsheet_calculate($toptype, $tyytd["debit"], $tyytd["credit"]);
$tyytd_out = "\n\t\t\t\t\t\t\t<td align='right' width='10%'>\n\t\t\t\t\t\t\t\t" . fsmoney($amt) . "\n\t\t\t\t\t\t\t</td>";
if ($amt != 0) {
$disp_acc = true;
}
$totals["tyytd"] += $amt;
} else {
$tyytd_out = "";
}
// Budget values
if ($budget) {
db_conn("cubit");
$sql = "SELECT SUM(amt) AS amt FROM buditems WHERE prd='{$month_to}' AND id='{$accid}'";
$bud_rslt = db_exec($sql) or errDie("Unable to retrieve budget values from Cubit.");
$bud_amt = pg_fetch_result($bud_rslt, 0, 0);
$budget_out = "<td align='right' width='10%'>" . fsmoney($bud_amt) . "</td>";
if ($bud_amt != 0) {
$disp_acc = true;
}
$totals["budget"] += $bud_amt;
} else {
$budget_out = "";
}
if ($this_year_budget) {
if ($PRDMON[1] == 1) {
$prdwhere = "prd<='{$month_to}'";
} else {
if ($month_to < $PRDMON[1]) {
$prdwhere = "(prd>='{$PRDMON['1']}' OR prd<='{$month_to}')";
} else {
if ($month_to >= $PRDMON[1]) {
$prdwhere = "(prd>='{$PRDMON['1']}' AND prd<='{$month_to}')";
}
}
}
// Retrieve current year budget from Cubit
db_conn("cubit");
$sql = "SELECT SUM(bi.amt)\n\t \t\t\t\t\t\tFROM cubit.budgets b LEFT JOIN cubit.buditems bi\n\t \t\t\t\t\t\tON b.budid=bi.budid\n\t \t\t\t\t\t\tWHERE bi.id='{$accid}' AND {$prdwhere}";
$rslt = db_exec($sql) or errDie("Unable to retrieve this year budget items from Cubit.");
$bud_amt = pg_fetch_result($rslt, 0, 0);
$tybudget_out = "\n\t\t\t\t\t\t\t<td align='right' width='10%'>\n\t\t\t\t\t\t\t\t" . fsmoney($bud_amt) . "\n\t\t\t\t\t\t\t</td>";
if ($bud_amt != 0) {
$disp_acc = true;
}
$totals["tybudget"] += $bud_amt;
} else {
$tybudget_out = "";
}
// Retrieve last year this month trial balance data
if ($last_year_same_month) {
db_conn($last_year_schema);
$sql = "SELECT * FROM {$month_to_name} WHERE accid='{$accid}' AND {$zb_sql}";
$lytm_rslt = db_exec($sql) or errDie("Unable to retrieve last year this month information from Cubit.");
$lytm_data = pg_fetch_array($lytm_rslt);
$amt = financialStatements::balsheet_calculate($toptype, $lytm_data["debit"], $lytm_data["credit"]);
$lysm_out = "\n\t\t\t\t\t\t\t<td align='right' width='10%'>\n\t\t\t\t\t\t\t\t" . fsmoney($amt) . "\n\t\t\t\t\t\t\t</td>";
if ($amt != 0) {
$disp_acc = true;
}
$totals["lysm"] += $amt;
} else {
$lysm_out = "";
}
// Last year's values
if ($last_year_year_to_date) {
db_conn($last_year_schema);
$sql = "SELECT * FROM year_balance WHERE accid='{$accid}' AND {$zb_sql}";
$py_rslt = db_exec($sql) or errDie("Unable to retrieve previous year trial balance from Cubit.");
$py_data = pg_fetch_array($py_rslt);
$amt = financialStatements::balsheet_calculate($toptype, $py_data["debit"], $py_data["credit"]);
$lyytd_out = "\n\t\t\t\t\t\t\t<td align='right' width='10%'>\n\t\t\t\t\t\t\t\t" . fsmoney($amt) . "\n\t\t\t\t\t\t\t</td>";
if ($amt != 0) {
$disp_acc = true;
}
$totals["lyytd"] += $amt;
} else {
$lyytd_out = "";
}
if (empty($zero_balance) && !$disp_acc && $tb_data["credit"] == $tb_data["debit"]) {
continue;
}
if ($pure && isset($notes[$accid])) {
$note_out = $notes[$accid];
} else {
if (!$pure) {
$note_out = "<a href='#' onclick='openwindow(\"" . SELF . "?key=note_view&accid={$accid}\")'>Note</a>";
} else {
$note_out = " ";
}
}
$amt = financialStatements::balsheet_calculate($toptype, $tb_data["debit"], $tb_data["credit"]);
${$toptype} .= "\n\t\t\t\t\t\t<tr class='bg-odd'>\n\t\t\t\t\t\t\t<td><a onClick=\"window.open('drill-view-trans.php?accid={$accid}&month_to={$month_to}','window{$accid}','width=900, height=380, scrollbars=yes');\" href='#'>{$tb_data['accname']}</a></td>\n\t\t\t\t\t\t\t<td align='right' width='10%'><a href='#' onClick=\"window.open('../core/drill-trans-new.php?dtaccid={$accid}&ctaccid={$accid}','window{$accid}','height=420, width=900, scrollbars=yes');\">" . fsmoney($amt) . "</a></td>\n\t\t\t\t\t\t\t{$tymtd_out}\n\t\t\t\t\t\t\t{$tyytd_out}\n\t\t\t\t\t\t\t{$budget_out}\n\t\t\t\t\t\t\t{$tybudget_out}\n\t\t\t\t\t\t\t{$lysm_out}\n\t\t\t\t\t\t\t{$lyytd_out}\n\t\t\t\t\t\t\t<td align='right'>{$note_out}</td>\n\t\t\t\t\t\t</tr>";
// add up the totals
$totals["assets"] += $amt;
}
}
// Decide which categories to display
/* if (!empty($fixed_asset))
$assets_out .= "<tr><th colspan='10' class='balsheet_cats'>- Fixed Assets</th></tr>$fixed_asset";
if (!empty($investments))
$assets_out .= "<tr><th colspan='10' class='balsheet_cats'>- Investments</th></tr>$investments";
if (!empty($other_fixed_asset))
$assets_out .= "<tr><th colspan='10' class='balsheet_cats'>- Other Fixed Assets</th></tr>$other_fixed_asset";
if (!empty($current_asset))
$assets_out .= "<tr><th colspan='10' class='balsheet_cats'>- Current Assets</th></tr>$current_asset";*/
foreach ($ar_assets as $ctoptype) {
if (!empty(${$ctoptype})) {
$assets_out .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th colspan='10' class='balsheet_cats'>- {$ar_cats[$ctoptype]}</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{${$ctoptype}}";
}
}
}
// Assets total output
if ($this_year_movement_to_date) {
$tymtd_out = "<td align='right'>" . fsmoney($totals["tymtd"]) . "</td>";
}
if ($this_year_year_to_date) {
$tyytd_out = "<td align='right'>" . fsmoney($totals["tyytd"]) . "</td>";
}
if ($budget) {
$budget_out = "<td align='right'>" . fsmoney($totals["budget"]) . "</td>";
}
if ($this_year_budget) {
$tybudget_out = "<td align='right'>" . fsmoney($totals["tybudget"]) . "</td>";
}
if ($last_year_same_month) {
$lysm_out = "<td align='right'>" . fsmoney($totals["lysm"]) . "</td>";
}
if ($last_year_year_to_date) {
$lyytd_out = "<td align='right'>" . fsmoney($totals["lyytd"]) . "</td>";
}
//$balance2 = $totals["assets"];
$balance2 = $totals["tyytd"];
// $assets_out .= "
// <tr class='bg-even'>
// <td> </td>
// <td align='right'>".fsmoney($totals["assets"], 2)."</td>
// $tymtd_out
// $tyytd_out
// $budget_out
// $tybudget_out
// $lysm_out
// $lyytd_out
// <td> </td>
// </tr>";
$assets1 .= "\n\t\t\t\t<tr class='bg-even'>\n\t\t\t\t\t<td> </td>\n\t\t\t\t\t<td align='right'>" . fsmoney($totals["assets"], 2) . "</td>\n\t\t\t\t\t{$tymtd_out}\n\t\t\t\t\t{$tyytd_out}\n\t\t\t\t\t{$budget_out}\n\t\t\t\t\t{$tybudget_out}\n\t\t\t\t\t{$lysm_out}\n\t\t\t\t\t{$lyytd_out}\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
$assets2 .= "\n\t\t\t\t<tr class='bg-even'>\n\t\t\t\t\t<td> </td>\n\t\t\t\t\t<td align='right'>" . fsmoney($totals["assets"], 2) . "</td>\n\t\t\t\t\t{$tymtd_out}\n\t\t\t\t\t<td><p class='err'>ERROR: Database Corruption Detected. Please Contact Your Dealer</p></td>\n\t\t\t\t\t{$budget_out}\n\t\t\t\t\t{$tybudget_out}\n\t\t\t\t\t{$lysm_out}\n\t\t\t\t\t{$lyytd_out}\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
// print "assets:$totals[assets]<br>";
// print "lysm:$totals[lysm]<br>";
// print "tymtd:$totals[tymtd]<br>";
// print "tyytd:$totals[tyytd]<br>";
// print "lyytd:$totals[lyytd]<br>";
// print "budget:$totals[budget]<br>";
// print "tybudget:$totals[tybudget]<br><br>";
$assets_total_tyytd = $totals['tyytd'];
$totals = array();
$totals["equity"] = 0.0;
$totals["tymtd"] = 0.0;
$totals["tyytd"] = 0.0;
$totals["budget"] = 0.0;
$totals["tybudget"] = 0.0;
$totals["lysm"] = 0.0;
$totals["lyytd"] = 0.0;
/* EQUITY - LIABILITIES - EMPLOYEMENT OF CAPITAL */
if (isset($equity)) {
foreach ($equity as $toptype => $arlv2) {
foreach ($equity[$toptype] as $accid) {
$disp_acc = false;
db_conn($viewyear);
$sql = "SELECT * FROM trial_bal_actual\n\t\t\t\t\t\t\tWHERE accid='{$accid}' AND month='{$month_to}' AND {$zb_sql}";
$tb_rslt = db_exec($sql) or errDie("Unable to retrieve account information from Cubit.");
$tb_data = pg_fetch_array($tb_rslt);
if ($tb_data["topacc"] == "5200" && $tb_data["accnum"] == "000") {
$retinc_acc = true;
} else {
$retinc_acc = false;
}
// Retrieve this year, movement to date
if ($this_year_movement_to_date) {
$tymtd = array();
$tymtd["debit"] = 0;
$tymtd["credit"] = 0;
db_conn($viewyear);
/* current year, year to date */
$sql = "SELECT debit,credit FROM trial_bal\n\t\t\t\t\t\t\t\tWHERE accid='{$accid}' AND month='{$month_to}' AND {$zb_sql} LIMIT 1";
$tymtd_rslt = db_exec($sql) or errDie("Unable to retrieve this year, year to date information from Cubit.");
while ($tymtd_data = pg_fetch_array($tymtd_rslt)) {
$tymtd["debit"] += $tymtd_data["debit"];
$tymtd["credit"] += $tymtd_data["credit"];
}
/* deduct previous year end of year amounts */
$sql = "SELECT debit,credit FROM trial_bal\n\t\t\t\t\t\t\t\tWHERE accid='{$accid}' AND month='0' AND {$zb_sql} LIMIT 1";
$tymtd_rslt = db_exec($sql) or errDie("Unable to retrieve this year, year to date information from Cubit.");
while ($tymtd_data = pg_fetch_array($tymtd_rslt)) {
$tymtd["debit"] -= $tymtd_data["debit"];
$tymtd["credit"] -= $tymtd_data["credit"];
}
//$amt = financialStatements::balsheet_calculate($toptype, $tymtd["debit"], $tymtd["credit"]);
$amt = $tymtd["debit"] - $tymtd["credit"];
if ($retinc_acc) {
$amt += $current_profit_loss_total_mtd;
}
$tymtd_out = "\n\t\t\t\t\t\t\t<td align='right' width='10%'>\n\t\t\t\t\t\t\t\t" . fsmoney($amt) . "\n\t\t\t\t\t\t\t</td>";
if ($amt != 0) {
$disp_acc = true;
}
$totals["tymtd"] += $amt;
//financialStatements::balsheet_calculate($toptype, $tymtd["debit"], $tymtd["credit"]);
} else {
$tymtd_out = "";
}
// Retrieve this year, year to date
if ($this_year_year_to_date) {
db_conn($viewyear);
$sql = "SELECT debit,credit FROM trial_bal\n\t\t\t\t\t\t\t\tWHERE accid='{$accid}' AND month='{$month_to}' AND {$zb_sql} LIMIT 1";
$tyytd_rslt = db_exec($sql) or errDie("Unable to retrieve this year, year to date information from Cubit.");
$tyytd = array();
$tyytd["debit"] = 0;
$tyytd["credit"] = 0;
while ($tyytd_data = pg_fetch_array($tyytd_rslt)) {
$tyytd["debit"] += $tyytd_data["debit"];
$tyytd["credit"] += $tyytd_data["credit"];
}
$amt = financialStatements::balsheet_calculate($toptype, $tyytd["debit"], $tyytd["credit"]);
if ($retinc_acc) {
$amt += $current_profit_loss_total_ytd;
}
$tyytd_out = "\n\t\t\t\t\t\t\t<td align='right' width='10%'>\n\t\t\t\t\t\t\t\t" . fsmoney($amt) . "\n\t\t\t\t\t\t\t</td>";
if ($amt != 0) {
$disp_acc = true;
}
$totals["tyytd"] += $amt;
} else {
$tyytd_out = "";
}
// Budget values
if ($budget) {
db_conn("cubit");
$sql = "SELECT SUM(amt) FROM buditems WHERE prd='{$month_to}' AND id='{$accid}'";
$bud_rslt = db_exec($sql) or errDie("Unable to retrieve budget values from Cubit.");
$bud_amt = pg_fetch_result($bud_rslt, 0, 0);
if ($retinc_acc) {
$bud_amt += $retained_income_budget;
}
$budget_out = "<td align='right' width='10%'>" . fsmoney($bud_amt) . "</td>";
if ($bud_amt != 0) {
$disp_acc = true;
}
$totals["budget"] += $bud_amt;
} else {
$budget_out = "";
}
if ($this_year_budget) {
if ($PRDMON[1] == 1) {
$prdwhere = "prd<='{$month_to}'";
} else {
if ($month_to < $PRDMON[1]) {
$prdwhere = "(prd>='{$PRDMON['1']}' OR prd<='{$month_to}')";
} else {
if ($month_to >= $PRDMON[1]) {
$prdwhere = "(prd>='{$PRDMON['1']}' AND prd<='{$month_to}')";
}
}
}
// Retrieve current year budget from Cubit
db_conn("cubit");
$sql = "SELECT SUM(bi.amt) \n\t \t\t\t\t\t\tFROM cubit.budgets b LEFT JOIN cubit.buditems bi \n\t \t\t\t\t\t\tON b.budid=bi.budid \n\t \t\t\t\t\t\tWHERE bi.id='{$accid}' AND {$prdwhere}";
$rslt = db_exec($sql) or errDie("Unable to retrieve this year budget items from Cubit.");
$bud_amt = pg_fetch_result($rslt, 0, 0);
if ($bud_amt != 0) {
$disp_acc = true;
}
$totals["tybudget"] += $bud_amt;
$tybudget_out = "\n\t\t\t\t\t\t\t<td align='right' width='10%'>\n\t\t\t\t\t\t\t\t" . fsmoney($bud_amt) . "\n\t\t\t\t\t\t\t</td>";
} else {
$tybudget_out = "";
}
// Retrieve last year this month trial balance data
if ($last_year_same_month) {
db_conn($last_year_schema);
$sql = "SELECT * FROM {$month_to_name} WHERE accid='{$accid}' AND {$zb_sql}";
$lytm_rslt = db_exec($sql) or errDie("Unable to retrieve last year this month information from Cubit.");
$lytm_data = pg_fetch_array($lytm_rslt);
$amt = financialStatements::balsheet_calculate($toptype, $lytm_data["debit"], $lytm_data["credit"]);
if ($retinc_acc) {
$amt += $prevyear_profit_loss_total;
}
$lysm_out = "\n\t\t\t\t\t\t\t<td align='right' width='10%'>\n\t\t\t\t\t\t\t\t" . fsmoney($amt) . "\n\t\t\t\t\t\t\t</td>";
if ($amt != 0) {
$disp_acc = true;
}
$totals["lysm"] += $amt;
} else {
$lysm_out = "";
}
// Last year's values
if ($last_year_year_to_date) {
db_conn($last_year_schema);
$sql = "SELECT * FROM year_balance WHERE accid='{$accid}' AND {$zb_sql}";
$py_rslt = db_exec($sql) or errDie("Unable to retrieve previous year trial balance from Cubit.");
$py_data = pg_fetch_array($py_rslt);
$amt = financialStatements::balsheet_calculate($toptype, $py_data["debit"], $py_data["credit"]);
if ($retinc_acc) {
$amt += $prevyear_profit_loss_total_ytd;
}
$lyytd_out = "\n\t\t\t\t\t\t\t<td align='right' width='10%'>\n\t\t\t\t\t\t\t\t" . fsmoney($amt) . "\n\t\t\t\t\t\t\t</td>";
if ($amt != 0) {
$disp_acc = true;
}
$totals["lyytd"] += $amt;
} else {
$lyytd_out = "";
}
if (empty($zero_balance) && !$disp_acc && $tb_data["credit"] == $tb_data["debit"]) {
continue;
}
if ($pure && isset($notes[$accid])) {
$note_out = $notes[$accid];
} else {
if (!$pure) {
$note_out = "<a href='#' onclick='openwindow(\"" . SELF . "?key=note_view&accid={$accid}\")'>Note</a>";
} else {
$note_out = " ";
}
}
//$amt = financialStatements::balsheet_calculate($toptype, $tb_data["debit"], $tb_data["credit"]);
$amt = $tb_data["debit"] - $tb_data["credit"];
if ($retinc_acc) {
$amt += $current_profit_loss_total;
}
${$toptype} .= "\n\t\t\t\t\t\t<tr class='bg-odd'>\n\t\t\t\t\t\t\t<td><a onClick=\"window.open('drill-view-trans.php?accid={$accid}&month_to={$month_to}','window{$accid}','width=900, height=380, scrollbars=yes');\" href='#'>{$tb_data['accname']}</a></td>\n\t\t\t\t\t\t\t<td align='right' width='10%'><a href='#' onClick=\"window.open('../core/drill-trans-new.php?dtaccid={$accid}&ctaccid={$accid}','window{$accid}','height=420, width=900, scrollbars=yes');\">" . fsmoney($amt) . "</a></td>\n\t\t\t\t\t\t\t{$tymtd_out}\n\t\t\t\t\t\t\t{$tyytd_out}\n\t\t\t\t\t\t\t{$budget_out}\n\t\t\t\t\t\t\t{$tybudget_out}\n\t\t\t\t\t\t\t{$lysm_out}\n\t\t\t\t\t\t\t{$lyytd_out}\n\t\t\t\t\t\t\t<td align='right'>{$note_out}</td>\n\t\t\t\t\t\t</tr>";
$totals["equity"] += $amt;
}
}
// Decide which categories to display
/* if (!empty($share_capital))
$equity_out .= "<tr><th colspan='10' class='balsheet_cats'>- Share Capital</th></tr>$share_capital";
if (!empty($retained_income))
$equity_out .= "<tr><th colspan='10' class='balsheet_cats'>- Retained Income</th></tr>$retained_income";
if (!empty($shareholders_loan))
$equity_out .= "<tr><th colspan='10' class='balsheet_cats'>- Shareholders Loan</th></tr>$shareholders_loan";
if (!empty($long_term_borrowing))
$equity_out .= "<tr><th colspan='10' class='balsheet_cats'>- Long Term Borrowings</th></tr>$long_term_borrowing";
if (!empty($other_long_term_liability))
$equity_out .= "<tr><th colspan='10' class='balsheet_cats'>- Other Long Term Liabilities</th></tr>$other_long_term_liability";
if (!empty($current_liability))
$equity_out .= "<tr><th colspan='10' class='balsheet_cats'>- Current Liabilities</th></tr>$current_liability";*/
foreach ($ar_equity as $ctoptype) {
if (!empty(${$ctoptype})) {
$equity_out .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th colspan='10' class='balsheet_cats'>- {$ar_cats[$ctoptype]}</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{${$ctoptype}}";
}
}
}
// equity totals output
/*if ($last_year_same_month) $lysm_out = "<td align='right'>".fsmoney($prevyear_profit_loss_total)."</td>";
if ($last_year_year_to_date) $lyytd_out = "<td align='right'>".fsmoney($prevyear_profit_loss_total_ytd)."</td>";
if ($this_year_movement_to_date) $tymtd_out = "<td align='right'>".fsmoney($current_profit_loss_total_mtd)."</td>";
if ($this_year_year_to_date) $tyytd_out = "<td align='right'>".fsmoney($current_profit_loss_total_ytd)."</td>";
if ($budget) $budget_out = "<td align='right'>".fsmoney($retained_income_budget)."</td>";
if ($this_year_budget) $tybudget_out = "<td> </td>";
$equity_out .= "
<tr class='bg-even'>
<td>Retained Income</td>
<td align='right'>".fsmoney($current_profit_loss_total)."</td>
$lysm_out
$lyytd_out
$tymtd_out
$tyytd_out
$budget_out
$tybudget_out
<td> </td>
</tr>";*/
if ($this_year_movement_to_date) {
$tymtd_out = "<td align='right'>" . fsmoney($totals["tymtd"]) . "</td>";
}
if ($this_year_year_to_date) {
$tyytd_out = "<td align='right'>" . fsmoney($totals["tyytd"]) . "</td>";
}
if ($budget) {
$budget_out = "<td align='right'>" . fsmoney($totals["budget"]) . "</td>";
}
if ($this_year_budget) {
$tybudget_out = "<td align='right'>" . fsmoney($totals["tybudget"]) . "</td>";
}
if ($last_year_same_month) {
$lysm_out = "<td align='right'>" . fsmoney($totals["lysm"]) . "</td>";
}
if ($last_year_year_to_date) {
$lyytd_out = "<td align='right'>" . fsmoney($totals["lyytd"]) . "</td>";
}
//$balance1 = $totals["equity"];
$balance1 = $totals["tyytd"];
// $equity_out .= "
// <tr class='bg-even'>
// <td> </td>
// <td align='right'>".fsmoney($totals["equity"], 2)."</td>
// $tymtd_out
// $tyytd_out
// $budget_out
// $tybudget_out
// $lysm_out
// $lyytd_out
// <td> </td>
// </tr>";
$equity1 .= "\n\t\t\t\t<tr class='bg-even'>\n\t\t\t\t\t<td> </td>\n\t\t\t\t\t<td align='right'>" . fsmoney($totals["equity"], 2) . "</td>\n\t\t\t\t\t{$tymtd_out}\n\t\t\t\t\t{$tyytd_out}\n\t\t\t\t\t{$budget_out}\n\t\t\t\t\t{$tybudget_out}\n\t\t\t\t\t{$lysm_out}\n\t\t\t\t\t{$lyytd_out}\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
$equity2 .= "\n\t\t\t\t<tr class='bg-even'>\n\t\t\t\t\t<td> </td>\n\t\t\t\t\t<td align='right'>" . fsmoney($totals["equity"], 2) . "</td>\n\t\t\t\t\t{$tymtd_out}\n\t\t\t\t\t<td><p class='err'>ERROR: Database Corruption Detected. Please Contact Your Dealer</p></td>\n\t\t\t\t\t{$budget_out}\n\t\t\t\t\t{$tybudget_out}\n\t\t\t\t\t{$lysm_out}\n\t\t\t\t\t{$lyytd_out}\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
if (isset($acc_view)) {
$acc_view_hidden = "<input type='hidden' name='acc_view' value='{$acc_view}'>";
} else {
$acc_view_hidden = "";
}
if ($month_from == $month_to) {
$date_range = "{$month_from_out} {$year_out}";
} else {
$date_range = "{$month_from_out} TO {$month_to_out} {$year_out}";
}
/* headings */
$head = "\n\t\t<tr>\n\t\t\t<th align='left' class='thkborder thkborder_left'>Account</th>\n\t\t\t<th align='right' class='thkborder'>Movement during<br />{$month_to_out} {$year_out}</th>";
if ($this_year_movement_to_date) {
$head .= "<th align='right' class='thkborder'>Movement<br />To {$month_to_out} {$year_out}</th>";
}
if ($this_year_year_to_date) {
$head .= "<th align='right' class='thkborder'>This Year<br />At {$month_to_out} {$year_out}</th>";
}
if ($budget) {
$head .= "<th align='right' class='thkborder'>Budget<br />for {$month_to_out} {$year_out}</th>";
}
if ($this_year_budget) {
$head .= "<th align='right' class='thkborder'>Budget<br />To {$month_to_out} {$year_out}</th>";
}
if ($last_year_same_month) {
$head .= "<th align='right' class='thkborder'>Last Year<br />At {$month_to_out} {$year_out}</th>";
}
if ($last_year_year_to_date) {
$head .= "<th align='right' class='thkborder'>Last Year<br />Year End</th>";
}
$head .= "\n\t\t\t<th align='left' class='thkborder thkborder_right'>Note</th>\n\t\t</tr>";
/* calculate colspans to half the total column span */
$totcols = 3;
if ($this_year_movement_to_date) {
++$totcols;
}
if ($this_year_year_to_date) {
++$totcols;
}
if ($budget) {
++$totcols;
}
if ($this_year_budget) {
++$totcols;
}
if ($last_year_same_month) {
++$totcols;
}
if ($last_year_year_to_date) {
++$totcols;
}
$half_left = (int) ($totcols / 2);
$half_right = $totcols - $half_left;
$OUTPUT = "";
// print "equity:$totals[equity]<br>";
// print "tymtd:$totals[tymtd]<br>";
// print "tyytd:$totals[tyytd]<br>";
// print "budget:$totals[budget]<br>";
// print "tybudget:$totals[tybudget]<br>";
// print "lysm:$totals[lysm]<br>";
// print "lyytd:$totals[lyytd]<br>";
$equity_total_tyytd = $totals['tyytd'];
$balance1 += 0;
$balance2 += 0;
if ((string) $balance1 != (string) $balance2) {
$assets_out .= $assets2;
$equity_out .= $equity2;
} else {
$assets_out .= $assets1;
$equity_out .= $equity1;
}
// Layout
$OUTPUT .= "\n\t\t\t{$acc_view_hidden}\n\t\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='{$half_left}' align='left'><h3>{$heading_1}</h3></td>\n\t\t\t\t\t<td colspan='{$half_right}' align='right'><h3>{$heading_2}</h3></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='{$half_left}' align='left'><h3>{$heading_3}</h3></td>\n\t\t\t\t\t<td colspan='{$half_right}' align='right'><h3>{$heading_4}</h3></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='10' align='center'><h3>{$date_range}</h3></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th colspan='10' class='balsheet_cats'><h3>{$capital_employed_out}</h3></th>\n\t\t\t\t</tr>\n\t\t\t\t{$head}\n\t\t\t\t{$equity_out}\n\t\t\t\t<tr>\n\t\t\t\t\t<th colspan='10' class='balsheet_cats'><h3>{$employment_of_capital_out}</h3></th>\n\t\t\t\t</tr>\n\t\t\t\t{$head}\n\t\t\t\t{$assets_out}";
if ($pure) {
$OUTPUT .= "<tr><td> </td></tr>";
$notes_display = "";
foreach ($notes as $accid => $num) {
db_conn("cubit");
$sql = "SELECT * FROM saved_bs_accounts WHERE accid='{$accid}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve note from Cubit.");
$note_data = pg_fetch_array($rslt);
$OUTPUT .= "\n\t\t\t\t\t<tr><td></td></tr>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='10'><u>{$num}) {$note_data['accname']}</u></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='10'>" . nl2br(base64_decode($note_data["note"])) . "</u></td>\n\t\t\t\t\t</tr>";
}
} else {
$OUTPUT .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='5' align='center'>\n\t\t\t\t\t\t<form method='POST' action='" . SELF . "'>\n\t\t\t\t\t\t\t<input type='hidden' name='key' value='customize' />\n\t\t\t\t\t\t\t<input type='hidden' name='heading_1' value='{$heading_1}' />\n\t\t\t\t\t\t\t<input type='hidden' name='heading_2' value='{$heading_2}' />\n\t\t\t\t\t\t\t<input type='hidden' name='heading_3' value='{$heading_3}' />\n\t\t\t\t\t\t\t<input type='hidden' name='heading_4' value='{$heading_4}' />\n\t\t\t\t\t\t\t<input type='hidden' name='capital_employed_out' value='{$capital_employed_out}' />\n\t\t\t\t\t\t\t<input type='hidden' name='employment_of_capital_out' value='{$employment_of_capital_out}' />\n\t\t\t\t\t\t\t<input type='hidden' name='viewyear' value='{$viewyear}' />\n\t\t\t\t\t\t\t<input type='hidden' name='month_from' value='{$month_from}' />\n\t\t\t\t\t\t\t<input type='hidden' name='month_to' value='{$month_to}' />\n\t\t\t\t\t\t\t<input type='hidden' name='last_year_same_month' value='{$last_year_same_month}' />\n\t\t\t\t\t\t\t<input type='hidden' name='last_year_year_to_date' value='{$last_year_year_to_date}' />\n\t\t\t\t\t\t\t<input type='hidden' name='this_year_movement_to_date' value='{$this_year_movement_to_date}' />\n\t\t\t\t\t\t\t<input type='hidden' name='this_year_year_to_date' value='{$this_year_year_to_date}' />\n\t\t\t\t\t\t\t<input type='hidden' name='budget' value='{$budget}' />\n\t\t\t\t\t\t\t<input type='hidden' name='this_year_budget' value='{$this_year_budget}' />\n\t\t\t\t\t\t\t<input type='hidden' name='zero_balance' value='{$zero_balance}' />\n\t\t\t\t\t\t\t<input type='submit' value='Customise' />\n\t\t\t\t\t\t\t<input type='submit' name='key' value='Print' />\n\t\t\t\t\t\t\t<input type='submit' name='key' value='Save' />\n\t\t\t\t\t\t\t<input type='submit' name='key' value='Export to Spreadsheet' />\n\t\t\t\t\t\t</form>\n\t\t\t\t\t\t</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>\n\t\t\t\t<p>\n\t\t\t\t<center>\n\t\t\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td align='center'><a target='_blank' href='../core/acc-new2.php'>Add account (New Window)</a></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td align='center'><a href='index-reports.php'>Financials</a></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td align='center'><a href='index-reports-stmnt.php'>Current Year Financial Statements</a></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>\n\t\t\t\t</center>";
}
return $OUTPUT;
}
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;
}
