/** * 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; }