function bal_sheet()
{
# Get Owners Equity Sub Headings
$oesubRslt = get("core", "*", "bal_sheet", "type", "OESUB");
$sheet = "\n\t<table>\n\t<tr><th colspan=3><h3> Balance sheet for period : " . PRD_NAME . "</h3></th></tr>\n\t<tr><th colspan=3><h3>Date: " . date("d M Y") . "</h3></th></tr>\n\t<tr><td colspan=3></td></tr>\n\t<tr><th colspan=3><h3>Owners Equity</h3></th></tr>";
# get accounts
$oebal = 0;
// OE Balance
while ($oesub = pg_fetch_array($oesubRslt)) {
$sheet .= "<tr><td colspan=3><b><u>{$oesub['value']}</u></b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='OEACC' AND ref = {$oesub['ref']} AND div = '" . USER_DIV . "'";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
$query = "SELECT * FROM trial_bal WHERE period='" . PRD_DB . "' AND period='" . PRD_DB . "' AND accid = '{$acc['value']}' AND div = '" . USER_DIV . "'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['credit'] - $accbal['debit'];
$oebal += $balance;
$sheet .= "<tr><td>{$accbal['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
# print Net Income and OE Balance on last column
$netincome = getNetIncome();
$oebal += $netincome;
$sheet .= "<tr><td> <b>Net Income</b></td><td>" . CUR . " {$netincome}</td><td><br></td></tr>";
$sheet .= "<tr><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$oebal}</b></td></tr>\n\t<tr><td colspan=3></td></tr>";
# Get Assets Sub Headings
$abal = 0;
// Assets Balance
$asssubRslt = get("core", "*", "bal_sheet", "type", "ASSSUB");
$sheet .= "<tr><th colspan=3><h3>Assets</h3></th></tr>";
# get accounts
while ($asssub = pg_fetch_array($asssubRslt)) {
$sheet .= "<tr><td colspan=3><b><u>{$asssub['value']}</u></b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='ASSACC' AND ref = {$asssub['ref']} AND div = '" . USER_DIV . "'";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
$query = "SELECT * FROM trial_bal WHERE period='" . PRD_DB . "' AND accid = '{$acc['value']}' AND div = '" . USER_DIV . "'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['debit'] - $accbal['credit'];
// calc Balance
$abal += $balance;
$sheet .= "<tr><td>{$accbal['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
# print assets balance on last column
$sheet .= "<tr><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$abal}</b></td></tr>\n\t</table>";
# return $sheet;
include "temp.xls.php";
Stream("BalanceSheet", $sheet);
}
function sheet_save()
{
# Get Owners Equity Sub Headings
$oesubRslt = get("core", "*", "bal_sheet", "type", "OESUB");
$sheet = "<center>\n <h3> Balance sheet for period : " . PRD_NAME . "<br><br>Date: " . date("d M Y") . " </h3>\n <table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width='70%'>\n <tr><th colspan=3>Owners Equity</th></tr>";
# get accounts
$oebal = 0;
// OE Balance
while ($oesub = pg_fetch_array($oesubRslt)) {
$sheet .= "<tr class='bg-even'><td colspan=3><b>{$oesub['value']}<b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='OEACC' AND ref = {$oesub['ref']}";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
$query = "SELECT * FROM trial_bal WHERE accid = '{$acc['value']}'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['credit'] - $accbal['debit'];
$oebal += $balance;
$sheet .= "<tr class='bg-odd'><td><blockquote><li>{$accbal['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
# print Net Income and OE Balance on last column
$netincome = getNetIncome();
$oebal += $netincome;
$sheet .= "<tr class='bg-odd'><td> <b>Net Income</b></td><td>" . CUR . " {$netincome}</td><td><br></td></tr>";
$sheet .= "<tr class='bg-even'><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$oebal}</b></td></tr>";
# Get Assets Sub Headings
$abal = 0;
// Assets Balance
$asssubRslt = get("core", "*", "bal_sheet", "type", "ASSSUB");
$sheet .= "<tr><th colspan=3>Assets</th></tr>";
# get accounts
while ($asssub = pg_fetch_array($asssubRslt)) {
$sheet .= "<tr class='bg-even'><td colspan=3><b>{$asssub['value']}<b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='ASSACC' AND ref = {$asssub['ref']}";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
$query = "SELECT * FROM trial_bal WHERE accid = '{$acc['value']}'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['debit'] - $accbal['credit'];
// calc Balance
$abal += $balance;
$sheet .= "<tr class='bg-odd'><td><blockquote><li>{$accbal['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
# print assets balance on last column
$sheet .= "<tr class='bg-even'><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$abal}</b></td></tr>\n </table><br>";
$output = base64_encode($sheet);
core_connect();
$sql = "INSERT INTO save_bal_sheet(gendate, output) VALUES('" . date("Y-m-d") . "', '{$output}')";
$Rs = db_exec($sql) or errdie("Unable to save the Balance Sheet.");
return true;
}
function getReport()
{
$typ = $this->typ;
$name = $this->name;
$tot = $this->tot;
$balance = sprint(0);
$nettincome = getNetIncome();
# all connects to core
db_conn("core");
$products = "<tr class='bg-odd'><th colspan=5>{$name}</th></tr>";
$sql = "SELECT * FROM balsubs WHERE typ = '{$typ}' ORDER BY skey ASC";
$subRslt = db_exec($sql) or errDie("Unable to get sub-headings information.");
while ($sub = pg_fetch_array($subRslt)) {
$products .= "<tr class='bg-even'><td colspan=2><b><u>{$sub['subname']}</u></b></td><td> <br> </td><td> <br> </td><tr>";
$sql = "SELECT * FROM balgrps WHERE skey = '{$sub['skey']}' AND typ = '{$typ}'";
$grpRslt = db_exec($sql) or errDie("Unable to get groups information.");
while ($grp = pg_fetch_array($grpRslt)) {
$grpbal = 0;
$products .= "<tr class='bg-odd'><td colspan=2><b>{$grp['grpname']} :<b></td><td> <br> </td><td> <br> </td><tr>";
$sql = "SELECT * FROM balgrpaccids WHERE skey = '{$sub['skey']}' AND gkey = '{$grp['gkey']}' AND typ = '{$typ}'";
$gaccRslt = db_exec($sql) or errDie("Unable to get group accounts information.");
while ($gacc = pg_fetch_array($gaccRslt)) {
$sql = "SELECT * FROM accounts WHERE accid = '{$gacc['accid']}' AND div = '" . USER_DIV . "'";
$accRslt = db_exec($sql) or errDie("Unable to view account.");
$acc = pg_fetch_array($accRslt);
$sql = "SELECT ({$tot}) as bal FROM trial_bal WHERE accid = '{$gacc['accid']}' AND div = '" . USER_DIV . "'";
$balRslt = db_exec($sql) or errDie("Unable to view account.");
$bal = pg_fetch_array($balRslt);
$bal['bal'] = sprint($bal['bal']);
$grpbal = sprint($grpbal + $bal['bal']);
$products .= "<tr class='bg-odd'><td> <br> </td>\n\t\t\t\t\t<td colspan=2>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td><td align=right>{$bal['bal']}</td></tr>";
}
$products .= "<tr class='bg-odd'><td colspan=3></td><td align=right class=tot>{$grpbal}</td></tr>";
$balance = sprint($balance + $grpbal);
}
$products .= "</tr>";
}
if ($typ == 'oe') {
$products .= "<tr class='bg-odd'><td colspan=4><br></td></tr>";
$products .= "<tr class='bg-even'><td colspan=3><b><u>Nett Income</u></b></td><td align=right class=tot>{$nettincome}</td><tr>";
$balance = sprint($balance + $nettincome);
}
$products .= "<tr class='bg-odd'><td colspan=4><br></td></tr>";
$products .= "<tr class='bg-even'><td colspan=2 align=right class=tot><br></td><td class=tot><b><u>Total</u></b></td><td align=right class=tot><b>{$balance}</b></td></tr>\n\t\t";
$this->balance = $balance;
return $products . "<tr><td colspan=2></form><form action='../xls/gen-balance-sheet-xls.php' method=post name=form><input type=hidden name=key value=print><input type=hidden name=summary value='no'><input type=submit name=xls value='Export to spreadsheet'></form></td></tr>";
}
function save_bal()
{
# Get Owners Equity Sub Headings
$oesubRslt = undget("core", "*", "bal_sheet", "type", "OESUB' AND div = '11111111");
$sheet = "<center>\n\t<h3> Balance sheet for period : " . PRD_NAME . "<br><br>Date: " . date("d M Y") . " </h3>\n\t<table cellpadding='3' cellspacing='0' border=1 bordercolor='#000000' width=750>\n\t<tr><th colspan=3>Owners Equity</th></tr>";
# get accounts
$oebal = 0;
// OE Balance
while ($oesub = pg_fetch_array($oesubRslt)) {
$sheet .= "<tr><td colspan=3><b>{$oesub['value']}<b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='OEACC' AND ref = {$oesub['ref']} AND div = 11111111";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
list($topacc, $accnum) = explode("/", $acc['value']);
# Get balance
$query = "SELECT sum(credit) as credit,sum(debit) as debit FROM trial_bal WHERE topacc = '{$topacc}' AND accnum = '{$accnum}'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['credit'] - $accbal['debit'];
$oebal += $balance;
$balacc = getaccnum($acc['value']);
$sheet .= "<tr><td><blockquote><li>{$balacc['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
$oebal = sprint($oebal);
# print Net Income and OE Balance on last column
$netincome = getNetIncome();
$oebal += $netincome;
$sheet .= "<tr><td> <b>Net Income</b></td><td>" . CUR . " {$netincome}</td><td><br></td></tr>";
$sheet .= "<tr><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$oebal}</b></td></tr>";
# Get Assets Sub Headings
$abal = 0;
// Assets Balance
$asssubRslt = undget("core", "*", "bal_sheet", "type", "ASSSUB' AND div = '11111111");
$sheet .= "<tr><th colspan=3>Assets</th></tr>";
# get accounts
while ($asssub = pg_fetch_array($asssubRslt)) {
$sheet .= "<tr><td colspan=3><b>{$asssub['value']}<b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='ASSACC' AND ref = {$asssub['ref']} AND div = 11111111";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
list($topacc, $accnum) = explode("/", $acc['value']);
# Get Balance
$query = "SELECT sum(credit) as credit,sum(debit) as debit FROM trial_bal WHERE topacc = '{$topacc}' AND accnum = '{$accnum}'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['debit'] - $accbal['credit'];
// calc Balance
$abal += $balance;
$balacc = getaccnum($acc['value']);
$sheet .= "<tr><td><blockquote><li>{$acc['value']} - {$balacc['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
$abal = sprint($abal);
# print assets balance on last column
$sheet .= "<tr><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$abal}</b></td></tr>\n\t</table><br>";
$output = base64_encode($sheet);
core_connect();
$sql = "INSERT INTO save_bal_sheet(gendate, output, div) VALUES('" . date("Y-m-d") . "', '{$output}', '" . USER_DIV . "')";
$Rs = db_exec($sql) or errdie("Unable to save the Balance Sheet.");
$sheet .= "\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=25%>\n\t<tr><th>Quick Links</th></tr>\n\t<tr class=datacell><td align=center><a target=_blank href='../core/acc-new2.php'>Add account (New Window)</a></td></tr>\n\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
return $sheet;
}
function bal_sheet()
{
$HEADER = "Balance sheet for period : " . PRD_NAME . "\nDate: " . date("d M Y");
$HEAD = "Owners Equity";
$HEADINGS = array('accname' => "Account", 'balance' => "Balance", 'total' => "Total");
$DATA = array();
$HEAD2 = "Assets";
$HEADINGS2 = array('accname' => "Account", 'balance' => "Balance", 'total' => "Total");
$DATA2 = array();
# Get Owners Equity Sub Headings
$oesubRslt = get("core", "*", "bal_sheet", "type", "OESUB");
$sheet = "\n\t<table>\n\t<tr><th colspan=3><h3> Balance sheet for period : " . PRD_NAME . "</h3></th></tr>\n\t<tr><th colspan=3><h3>Date: " . date("d M Y") . "</h3></th></tr>\n\t<tr><td colspan=3></td></tr>\n\t<tr><th colspan=3><h3>Owners Equity</h3></th></tr>";
# Get accounts
$oebal = 0;
// OE Balance
$i = 0;
while ($oesub = pg_fetch_array($oesubRslt)) {
$DATA[] = array('accname' => "<b><u>{$oesub['value']}</u></b>", 'balance' => " ", 'total' => " ");
$sheet .= "<tr><td colspan=3><b><u>{$oesub['value']}</u></b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='OEACC' AND ref = {$oesub['ref']} AND div = '" . USER_DIV . "'";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
$query = "SELECT * FROM trial_bal WHERE period='" . PRD_DB . "' AND accid = '{$acc['value']}' AND div = '" . USER_DIV . "'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['credit'] - $accbal['debit'];
$oebal += $balance;
$DATA[] = array('accname' => $accbal['accname'], 'balance' => CUR . " {$balance}", 'total' => " ");
$sheet .= "<tr><td>{$accbal['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
# Print Net Income and OE Balance on last column
$netincome = getNetIncome();
$oebal += $netincome;
$DATA[] = array('accname' => "<b>Net Income</b>", 'balance' => CUR . " {$netincome}", 'total' => " ");
$DATA[] = array('accname' => " ", 'balance' => " ", 'total' => " ");
$DATA[] = array('accname' => "<b>Total</b>", 'balance' => " ", 'total' => CUR . " {$oebal}");
$sheet .= "<tr><td> <b>Net Income</b></td><td>" . CUR . " {$netincome}</td><td><br></td></tr>";
$sheet .= "<tr><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$oebal}</b></td></tr>\n\t<tr><td colspan=3></td></tr>";
# Get Assets Sub Headings
$abal = 0;
// Assets Balance
$asssubRslt = get("core", "*", "bal_sheet", "type", "ASSSUB");
$sheet .= "<tr><th colspan=3><h3>Assets</h3></th></tr>";
# Get accounts
while ($asssub = pg_fetch_array($asssubRslt)) {
$DATA2[] = array('accname' => "<b><u>{$asssub['value']}</u></b>", 'balance' => " ", 'total' => " ");
$sheet .= "<tr><td colspan=3><b><u>{$asssub['value']}</u></b></td></tr>";
$sql = "SELECT * FROM bal_sheet WHERE type ='ASSACC' AND ref = {$asssub['ref']} AND div = '" . USER_DIV . "'";
$accRslt = db_exec($sql) or errDie("Unable to retrieve balance sheet settings from the Database.", SELF);
# Get account BAlances
while ($acc = pg_fetch_array($accRslt)) {
$query = "SELECT * FROM trial_bal WHERE period='" . PRD_DB . "' AND accid = '{$acc['value']}' AND div = '" . USER_DIV . "'";
$balRslt = db_exec($query) or errDie("Unable to retrieve Account Balances from the Database.", SELF);
$accbal = pg_fetch_array($balRslt);
$balance = $accbal['debit'] - $accbal['credit'];
// calc Balance
$abal += $balance;
$DATA2[] = array('accname' => $accbal['accname'], 'balance' => CUR . " {$balance}", 'total' => " ");
$sheet .= "<tr><td>{$accbal['accname']}</td><td>" . CUR . " {$balance}</td><td><br></td></tr>";
}
}
# Print assets balance on last column
$DATA2[] = array('accname' => " ", 'balance' => " ", 'total' => " ");
$DATA2[] = array('accname' => "<b>Total</b>", 'balance' => " ", 'total' => CUR . " {$abal}");
$sheet .= "<tr><td colspan=2><b>Total</b></td><td><b>" . CUR . " {$abal}</b></td></tr>\n\t</table>";
# Stream out to browser
require "temp.pdf.php";
}
