function export()
{
$oc = array();
/* add (if any) last closed year */
$qry = new dbSelect("year", "core", grp(m("cols", "yrdb"), m("where", "closed='y'"), m("order", "yrname DESC"), m("limit", "1")));
$qry->run();
if ($qry->num_rows() > 0) {
$qry->fetch_array();
add_tbdata($oc, $qry->d["yrdb"], 1);
}
/* add current year */
add_tbdata($oc, "core");
/* generate output information */
$headings = "<td rowspan='1'></td>";
$subhead = "";
$accinfo = array();
/* go through each period */
foreach ($oc as $name => $data) {
$headings .= "<td colspan='1'>{$name}</td>";
//$subhead .= "<th>Debit</th><th>Credit</th>";
/* go through each account for current period */
foreach ($data as $accname => $accdata) {
if (!isset($accinfo[$accname])) {
$accinfo[$accname] = "<td>{$accname}</td>";
}
$accinfo[$accname] .= "<td align='right'>" . fsmoney($accdata["debit"] - $accdata["credit"]) . "</td>";
}
}
/* build output information */
$OUT = "<table>";
$OUT .= "<tr>{$headings}</tr>\n";
//$OUT .= "<tr>$subhead</tr>\n";
$OUT .= "<tr>" . implode("</tr>\n<tr>", $accinfo) . "</tr>";
$OUT .= "</table>";
/* stream information */
$months = count($oc);
// number of months of data we gathered
StreamXLS("GeneralLedger{$months}", $OUT);
return $OUT;
}
/**
* 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;
}
