function view($frm)
{
extract($_REQUEST);
if ($msg = cForm::validateValue($supid, "num", 1, 10)) {
return "<li class='err'>The supplier ID is invalid. {$msg}</li>\n\t\t\t<input type='button' onclick='window.history.back();' value='« Correction' />";
}
/* @var frm cForm */
$frm->settitle("Supplier Pricelist");
$frm->setkey("import");
$frm->add_heading("Import New Pricelist");
$frm->add_message("\n\t\t\t<li class='err'>Pricelists has to be in CSV format and to import them\n\t\t\t\tyou have to specify the order and format of the fields<br />\n\t\t\t\tby selecting what each of them are in the same order as they appear\n\t\t\t\tin the file. Only the stock code<br />\n\t\t\t\tand the price is needed, so for the other fields you simply\n\t\t\t\tselect the '-' options.\n\t\t\t</li>", "inst");
$frm->add_message("\n\t\t\t<li class='err'>Note that the stock codes of the supplier should be added\n\t\t\t\tby editing the stock item and selecting the<br />\n\t\t\t\t'Add/Edit/Remove' button below the stock code input field.\n\t\t\t</li>", "suppstkcod");
$frm->add_hidden("supid", $supid, "num");
$frm->add_file("Pricelist", "supplist");
$frm->add_checkbox("VAT Inclusive", "vatinc", true, true);
$frm->add_heading("Comma Seperated Volume (CSV) Fields");
$frm->add_layout("\n\t<tr %bgc>\n\t\t<td colspan='2' nowrap='t'>\n\t\t\t<strong>\n\t\t\t\t%fldonly ,\n\t\t\t\t%fldonly ,\n\t\t\t\t%fldonly ,\n\t\t\t\t%fldonly ,\n\t\t\t\t%fldonly ,\n\t\t\t\t%fldonly\n\t\t\t</strong>\n\t\t</td>\n\t</tr>");
// field types
$ft = array("ignore" => "-", "stkcod" => "Stock Code", "price" => "Price");
$frm->add_select("", "fld[0]", "stkcod", $ft, "string", "5:6");
$frm->add_select("", "fld[1]", "price", $ft, "string", "5:6");
$frm->add_select("", "fld[2]", "ignore", $ft, "string", "5:6");
$frm->add_select("", "fld[3]", "ignore", $ft, "string", "5:6");
$frm->add_select("", "fld[4]", "ignore", $ft, "string", "5:6");
$frm->add_select("", "fld[5]", "ignore", $ft, "string", "5:6");
$frm->add_ctrlbtn("Import", "submit", "btn_import");
$OUT = $frm->getfrm_input();
/* supplier info */
$suppinfo = qrySupplier($supid);
$supcur = qryCurrency($suppinfo["fcid"]);
$supcur = $supcur["symbol"];
/* list current pricelist */
$OUT .= "\n\t<table " . TMPL_tblDflts . ">\n\t<tr>\n\t\t<th>Stock Code</td>\n\t\t<th>Supplier Stock Code</th>\n\t\t<th>Current Supplier Price</th>\n\t\t<th>Current Selling Price</th>\n\t</tr>";
$sql = "SELECT pli.*\n\t\t\tFROM exten.spricelist pl INNER JOIN exten.splist_prices pli\n\t\t\t\tON pl.listid=pli.listid\n\t\t\tWHERE pl.suppid='{$supid}'";
$qry = new dbSql($sql);
$qry->run();
if ($qry->num_rows() <= 0) {
$OUT .= "\n\t\t<tr bgcolor='" . bgcolorc(0) . "'>\n\t\t\t<td colspan='4'>No pricelist.</td>\n\t\t</tr>";
}
$i = 0;
while ($row = $qry->fetch_array()) {
if (empty($row["supstkcod"])) {
$our_stkcod = "No supplier stock code";
$our_price = "";
} else {
if (($stkid = suppStkid($supid, $row["supstkcod"])) === false) {
$our_stkcod = "<li class='err'>No such stock item.\n\t\t\t\t<a href='stock-add.php?stkcod={$row['supstkcod']}&supid={$supid}&supstkcod={$row['supstkcod']}'>Add Stock</a> /\n\t\t\t\t<a href='stock-view.php'>Edit Stock</li>";
$our_price = "";
} else {
$stkrow = qryStock($stkid, "stkcod, selamt");
$our_stkcod = "<strong>{$stkrow['stkcod']}</strong>";
$our_price = CUR . " " . sprint($stkrow["selamt"]);
}
}
$OUT .= "\n\t\t<tr bgcolor='" . bgcolor($i) . "'>\n\t\t\t<td>{$our_stkcod}</td>\n\t\t\t<td>{$row['supstkcod']}</td>\n\t\t\t<td align='right'>{$supcur} " . sprint($row["price"]) . "</td>\n\t\t\t<td align='right'>{$our_price}</td>\n\t\t</tr>";
}
$OUT .= "\n\t</table>";
return $OUT;
}
function edit($accid, $err = "")
{
require_lib("validate");
$v = new validate();
$v->isOk($accid, "num", 1, 50, "Invalid Account number.");
if ($v->isError()) {
return $v->genErrors();
}
$acc = qryAccounts($accid);
$acctypes = array("I" => "Income", "E" => "Expense", "B" => "Balance");
$size = strlen($acc['accname']);
if ($size < 20) {
$size = 20;
}
$OUT = "\n\t<h3>Edit Account</h3>\n\t{$err}\n\t<form action='" . SELF . "' method=post>\n\t<table " . TMPL_tblDflts . ">\n\t\t<input type='hidden' name='key' value='confirm' />\n\t\t<input type='hidden' name='accid' value='{$acc['accid']}' />\n\t\t<input type='hidden' name='fcatid' value='{$acc['catid']}' />\n\t\t<input type='hidden' name='acctype' value='{$acc['acctype']}' />\n\t\t<tr>\n\t\t\t<th>Field</th>\n\t\t\t<th>Value</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Account Name</td>\n\t\t\t<td>{$acc['accname']}</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Account Type</td>\n\t\t\t<td>" . $acctypes[$acc["acctype"]] . "</td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>To Category</td>\n\t\t\t<td>\n\t\t\t\t<select name=catid>";
$sql = "SELECT 'I' AS acctype, * FROM core.income WHERE div = '" . USER_DIV . "'\n\t\t\t\tUNION\n\t\t\t\tSELECT 'E' AS acctype, * FROM core.expenditure WHERE div='" . USER_DIV . "'\n\t\t\t\tUNION\n\t\t\t\tSELECT 'B' AS acctype, * FROM core.balance WHERE div='" . USER_DIV . "'";
$cats = new dbSql($sql);
$cats->run();
if ($cats->num_rows() < 1) {
return "There are no Account Categories in Cubit.";
}
$pgroup = false;
while ($cat = $cats->fetch_array()) {
if ($pgroup != $cat["acctype"]) {
if ($pgroup) {
$OUT .= "</optgroup>";
}
$OUT .= "<optgroup label='" . $acctypes[$cat["acctype"]] . "'>";
}
if ($cat["catid"] == $acc["catid"]) {
$sel = "selected";
} else {
$sel = "";
}
$OUT .= "<option {$sel} value='{$cat['catid']}'>{$cat['catname']}</option>";
}
$OUT .= "\n\t\t\t\t</optgroup>\n\t\t\t</select>\n\t\t</td></tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>Account Number</td>\n\t\t\t<td valign='center'><input type='text' name='topacc' size='4' maxlength='4' value='{$acc['topacc']}' /> / <input type='text' name='accnum' size='3' maxlength='3' value='{$acc['accnum']}' /></td>\n\t\t</tr>\n\t\t" . TBL_BR . "\n\t\t<tr>\n\t\t\t<td colspan='2' align='right'><input type='submit' value='Confirm »' /></td>\n\t\t</tr>\n\t</table>\n\t</form>" . mkQuickLinks(ql("acc-view.php", "View Accounts"));
return $OUT;
}
function add_tbdata(&$oc, $schema, $yearsback = 0)
{
global $MONPRD, $PRDMON;
/* fetch prev year trial bal data */
$tb = new dbSelect("trial_bal_actual", $schema, grp(m("where", "period!='0'"), m("order", "period, acctype, topacc, accnum")));
$tb->run();
$cprd = false;
$cprd_name = false;
while ($row = $tb->fetch_array()) {
/* the period in the table data changed */
if ($cprd != $row["period"]) {
$cprd = $row["period"];
$year = getYearOfFinMon($PRDMON[$cprd]) - $yearsback;
$mon = getMonthNameS($PRDMON[$cprd]);
$cprd_name = "{$mon} {$year}";
$oc[$cprd_name] = array();
}
if ($row["period"] == 1 && $row["acctype"] != "B") {
$hcode = new dbSelect("trial_bal", $schema, grp(m("where", "period='1' AND accid='{$row['accid']}'"), m("limit", 1)));
$hcode->run();
$row = $hcode->fetch_array();
} else {
if ($row["period"] == 1 && $row["topacc"] == "5200" && $row["accnum"] == "000") {
/* calculate previous year profit/loss */
$sql = "SELECT SUM(tb.credit) AS credit, SUM(tb.debit) AS debit\n\t\t\t\t\tFROM core.accounts acc LEFT JOIN {$schema}.trial_bal tb\n\t\t\t\t\t\tON acc.accid=tb.accid AND acc.div=tb.div\n\t\t\t\t\tWHERE (acc.acctype='I' OR acc.acctype='E') AND acc.div='" . USER_DIV . "'\n\t\t\t\t\t\tAND tb.period='0'";
$qry = new dbSql($sql);
$qry->run();
/* then deduct from debit/credit of retained income/accumulated loss */
$qry->fetch_array();
$row["debit"] -= $qry->d["debit"];
$row["credit"] -= $qry->d["credit"];
}
}
/* store data */
$oc[$cprd_name]["{$row['topacc']}/{$row['accnum']} {$row['accname']}"] = array("debit" => $row["debit"], "credit" => $row["credit"]);
}
#sort array to make some sense
$oc = natksort($oc);
}
function viewtran($_POST)
{
extract($_POST);
global $MONPRD, $PRDMON;
require_lib("validate");
$v = new validate();
$v->isOk($accnt, "string", 1, 10, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($accids)) {
foreach ($accids as $accid) {
$v->isOk($accid, "num", 1, 20, "Invalid Account number.");
}
} else {
return "<li class='err'>Please select at least one account.</li>" . slctacc();
}
}
# display errors, if any
if ($v->isError()) {
$err = $v->genErrors();
return slct($err);
}
if ($accnt == 'all') {
$accids = array();
core_connect();
$sql = "SELECT accid FROM accounts WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
while ($ac = pg_fetch_array($rs)) {
$accids[] = $ac['accid'];
}
} else {
if ($accnt == "allactive") {
$accids = array();
$sql = "SELECT accid FROM core.trial_bal\n\t\t\t\tWHERE (debit!=0 OR credit!=0) AND div='" . USER_DIV . "'\n\t\t\t\t\tAND period>='" . $MONPRD[$fprd] . "' AND period<='" . $MONPRD[$tprd] . "'\n\t\t\t\tGROUP BY accid";
$qry = new dbSql($sql);
$qry->run();
while ($macc_data = $qry->fetch_array()) {
$accids[] = $macc_data["accid"];
}
}
}
if ($key == "spreadsheet") {
$pure = true;
} else {
$pure = false;
}
# Get all Closed Periods
db_conn("audit");
// $sql = "SELECT * FROM closedprd";
// $clsRs = db_exec($sql) or errDie("Could not get closed periods from audit DB",SELF);
$trans = "";
$hide = "";
//while($cls = pg_fetch_array($clsRs)){
foreach ($accids as $key => $accid) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
$sql = "SELECT debit,credit FROM core.trial_bal WHERE accid='{$accid}' AND month='{$tprd}'";
$qry = new dbSql($sql);
$qry->run();
$tb = $qry->fetch_array();
$tbbal = $tb["debit"] - $tb["credit"];
$hide .= "<input type='hidden' name='accids[]' value='{$acc['accid']}'>";
$trans .= "\n\t\t\t<tr>\n\t\t\t\t<th> </th>\n\t\t\t\t<th>Date</th>\n\t\t\t\t<th>Reference</th>\n\t\t\t\t<th>Description</th>\n\t\t\t\t<th>Debit</th>\n\t\t\t\t<th>Credit</th>\n\t\t\t\t<th>Balance</th>\n\t\t\t\t<th>Contra Acc</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='4' align='right'><b>Balance at end of " . getMonthName($tprd) . "</b></td>\n\t\t\t\t<td align='right'><b>" . money($tb["debit"]) . "</b></td>\n\t\t\t\t<td align='right'><b>" . money($tb["credit"]) . "</b></td>\n\t\t\t\t<td align='right' nowrap='t'><b>" . ($tbbal > 0 ? money($tbbal) . " DT" : money(-$tbbal) . " CT") . "</b></td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>";
$cp = $fprd;
$fs = 0;
if ($fprd == $tprd + 1) {
$f = true;
} else {
$f = false;
}
while ($cp != $tprd + 1 || $f) {
$prd = $cp;
$cp++;
if ($cp == 13) {
$cp = 1;
}
$fs++;
if ($fs > 13) {
break;
}
$f = false;
# Period name
$prdname = prdname($prd);
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='8' align='center'><h3>{$prdname}</h3></td>\n\t\t\t\t</tr>";
if (isset($t)) {
unset($t);
}
# Get balances
$idRs = get($prd, "max(id), min(id)", "ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", "ledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "cbalance,dbalance", "ledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
} else {
if (!isset($t)) {
$trans .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td colspan='8' align='center'><li> There are no transactions in this period.</td>\n\t\t\t\t\t\t</tr>";
}
continue;
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
$cbal['cbalance'] = 0;
$cbal['dbalance'] = 0;
}
$t = "lemme ci";
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DT";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CT";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
$balance = sprint($balance);
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
if ($prd < $PRDMON[1]) {
++$prd_y;
}
// make the date of the last day of the previous prd
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, $prd_y));
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
# --> transactio reding comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
$tranRs = get($prd, "*", "ledger", "acc", $accid);
while ($tran = pg_fetch_array($tranRs)) {
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
# Current(Running) balance
if ($tran['dbalance'] > $tran['cbalance']) {
$tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
$tran['cbalance'] = "";
$cbalance = $tran['dbalance'];
$cfl = "DT";
} elseif ($tran['cbalance'] > $tran['dbalance']) {
$tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
$tran['dbalance'] = "";
$cbalance = $tran['cbalance'];
$cfl = "CT";
} else {
$tran['cbalance'] = "";
$tran['dbalance'] = "";
$cbalance = "0.00";
$cfl = "";
}
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$trans .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td colspan='2'>{$tran['edate']}</td>\n\t\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t\t<td align='right'>{$cbalance} {$cfl}</td>\n\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t</tr>";
}
# Total balance changes
if ($dbal['debit'] > $dbal['credit']) {
$dbal['debit'] = sprint($dbal['debit'] - $dbal['credit']);
$dbal['credit'] = "";
} elseif ($dbal['credit'] > $dbal['debit']) {
$dbal['credit'] = sprint($dbal['credit'] - $dbal['debit']);
$dbal['debit'] = "";
} else {
$dbal['credit'] = "";
$dbal['debit'] = "0.00";
}
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t\t<td>A/C Total</td>\n\t\t\t\t\t<td>Total for period {$prdname}:</td>\n\t\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t<td></td>\n\t\t\t\t</tr>";
}
$trans .= "<tr><td colspan='8'><br></td></tr>";
}
$fprdname = prdname($fprd);
$tprdname = prdname($tprd);
$sp = " ";
$view = "";
if (!$pure) {
$view .= "\n\t\t\t<center>\n\t\t\t<h3>Period Range General Ledger</h3>\n\t\t\t<h4>{$fprdname} - {$tprdname}</h4>";
}
$view .= "<table " . TMPL_tblDflts . " width='90%'>";
if (!$pure) {
$view .= "\n\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t<input type='hidden' name='key' value='spreadsheet'>\n\t\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t\t{$hide}\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t</tr>\n\t\t\t\t" . TBL_BR;
}
$view .= $trans;
if (!$pure) {
$view .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8'> </td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t</tr>\n\t\t\t<table>\n\t\t\t</form>" . mkQuickLinks(ql("index-reports.php", "Financials"), ql("index-reports-journal.php", "Current Year Details General Ledger Reports"), ql("../core/acc-new2.php", "Add New Account"));
}
return $view;
}
function viewtran($_POST)
{
global $MONPRD, $PRDMON;
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($accnt, "string", 1, 10, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($accids)) {
foreach ($accids as $key => $accid) {
$v->isOk($accid, "num", 1, 20, "Invalid Account number.");
}
} else {
return "<li class='err'>Please select at least one account.</li>" . slctacc();
}
}
if ($v->isError()) {
$err = $v->genErrors();
return $confirm;
}
if ($_POST["key"] == "export") {
$pure = true;
} else {
$pure = false;
}
#get list of which accounts to show
if ($accnt == 'all') {
$accids = array();
core_connect();
$sql = "SELECT accid FROM accounts WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$accids[] = $ac['accid'];
}
} else {
return "<li calss='err'> There are no accounts yet in Cubit.</li>";
}
} else {
if ($accnt == "allactive") {
$accids = array();
//print "->$fin_year<-";
// if ($fin_year != "0"){
// $accsql = array ();
// for ($x=1;$x<13;$x++){
// $month = date ("F",mktime (0,0,0,$x,1,substr($fin_year,1)));
// $accsql[] = "SELECT debit,credit FROM $month";
// }
// $sql = implode (" UNION ",$accsql);
// db_conn($fin_year."_audit");
// $run_sql = db_exec($sql) or errDie ("Unable to get previous year information.");
// while ($rarr = pg_fetch_array ($run_sql)){
// $accids[] = $rarr['debit'];
// $accids[] = $rarr['credit'];
// $accids = array_unique($accids);
// }
//
// }else {
$sql = "SELECT accid FROM core.trial_bal\n\t\t\t\t\tWHERE (debit!=0 OR credit!=0) AND div='" . USER_DIV . "'\n\t\t\t\t\t\tAND period<='" . $MONPRD[PRD_DB] . "'\n\t\t\t\t\tGROUP BY accid";
$qry = new dbSql($sql);
$qry->run();
while ($macc_data = $qry->fetch_array()) {
$accids[] = $macc_data["accid"];
}
// }
}
}
$hide = "";
$trans = "";
foreach ($accids as $key => $accid) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
$tran_flag = FALSE;
$the_trans = "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t</tr>";
db_conn("audit");
#go through SELECTED periods ...
$cp = $fprd;
$fs = 0;
if ($fprd == $tprd + 1) {
$f = true;
} else {
$f = false;
}
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DT";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CT";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
// if ($prd < $PRDMON[1]) {
// ++$prd_y;
// }
//"t-M-Y"
// make the date of the last day of the previous prd
$bbf_date = date("t F Y", mktime(0, 0, 0, $PRDMON[1] - 1, 1, $prd_y));
if ($openbal == "show" and ($bal['dbalance'] > 0 or $bal['cbalance'] > 0)) {
$tran_flag = TRUE;
$the_trans .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td nowrap colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t\t\t<td>Br/Forwd</td><td>Brought Forward</td>\n\t\t\t\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t</tr>";
}
while ($cp != $tprd + 1 || $f) {
$prd = $cp;
$cp++;
$fs++;
if ($cp == 13) {
$cp = 1;
}
if ($fs > 13) {
break;
}
$f = false;
# Period name
$prdname = prdname($prd);
$hide = "";
if (isset($t)) {
unset($t);
}
# Get balances
$idRs = get($prd, "max(id), min(id)", "ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
#at least 1 entry found for this period ...
$balRs = get($prd, "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", "ledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "cbalance,dbalance", "ledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
} else {
if (!isset($t)) {
// $the_trans .= "
// <tr class='".bg_class()."'>
// <td colspan='8' align='center'><li> There are no transactions in this period.</td>
// </tr>";
}
continue;
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
$cbal['cbalance'] = 0;
$cbal['dbalance'] = 0;
}
$t = "lemme ci";
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DT";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CT";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
$balance = sprint($balance);
$bal['cbalance'] = sprint($bal['cbalance']);
$bal['dbalance'] = sprint($bal['dbalance']);
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
if ($prd < $PRDMON[1]) {
++$prd_y;
}
# --> Transaction reding comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
#go through all the transactions
$tranRs = get($prd, "*", "ledger", "acc", $accid, "ORDER BY id");
while ($tran = pg_fetch_array($tranRs)) {
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
# Current(Running) balance
if ($tran['dbalance'] > $tran['cbalance']) {
$tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
$tran['cbalance'] = "";
$cbalance = $tran['dbalance'];
$cfl = "DT";
} elseif ($tran['cbalance'] > $tran['dbalance']) {
$tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
$tran['dbalance'] = "";
$cbalance = $tran['cbalance'];
$cfl = "CT";
} else {
$tran['cbalance'] = "";
$tran['dbalance'] = "";
$cbalance = "0.00";
$cfl = "";
}
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
#only show the transaction if it actually DOES something ...
if ($tran['debit'] != 0 or $tran['credit'] != 0) {
$tran_flag = TRUE;
if (strlen($tran['edate']) == 10) {
$darr = explode("-", $tran['edate']);
$tran['edate'] = date("t F Y", mktime(0, 0, 0, $darr[1], $darr[0], $darr[2]));
}
$the_trans .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td><br></td>\n\t\t\t\t\t\t\t<td nowrap>{$tran['edate']}</td>\n\t\t\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t\t<td nowrap align='right'>{$tran['debit']}</td>\n\t\t\t\t\t\t\t<td nowrap align='right'>{$tran['credit']}</td>\n\t\t\t\t\t\t\t<td nowrap align='right'>{$cbalance} {$cfl}</td>\n\t\t\t\t\t\t\t<td nowrap>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t\t</tr>";
}
}
# Total balance changes
if ($dbal['debit'] > $dbal['credit']) {
$dbal['debit'] = sprint($dbal['debit'] - $dbal['credit']);
$dbal['credit'] = "";
} elseif ($dbal['credit'] > $dbal['debit']) {
$dbal['credit'] = sprint($dbal['credit'] - $dbal['debit']);
$dbal['debit'] = "";
} else {
$dbal['credit'] = "0.00";
$dbal['debit'] = "0.00";
}
// $trans .= "
// <tr class='".bg_class()."'>
// <td colspan='2'><br></td>
// <td>A/C Total</td>
// <td>Total for period $prdname to Date :</td>
// <td align='right'>$dbal[debit]</td>
// <td align='right'>$dbal[credit]</td>
// <td align='right'></td>
// <td> </td>
// </tr>";
if ($tran_flag) {
$trans .= $the_trans;
$the_trans = "";
}
}
if ($tran_flag) {
$trans .= "<tr><td colspan='8'><br></td></tr>";
}
}
$OUT = "";
if (!$pure) {
$OUT .= "<center>";
}
$OUT .= "\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>General Ledger Report</h3></td>\n\t\t\t</tr>";
if (!$pure) {
$OUT .= "\n\t\t<tr>\n\t\t\t<form action='" . SELF . "' method='post'>\n\t\t\t\t<input type='hidden' name='key' value='export' />\n\t\t\t\t<input type='hidden' name='prd' value='{$prd}' />\n\t\t\t\t<input type='hidden' name='accnt' value='{$accnt}' />\n\t\t\t\t<input type='hidden' name='fprd' value='{$fprd}' />\n\t\t\t\t<input type='hidden' name='tprd' value='{$tprd}' />\n\t\t\t\t<input type='hidden' name='openbal' value='{$openbal}' />\n\t\t\t\t" . array2form($accids, "accids") . "\n\t\t\t\t<td colspan='8' align='center'>\n\t\t\t\t\t<input type='submit' value='Export to Spreadsheet'>\n\t\t\t\t</td>\n\t\t\t</form>\n\t\t</tr>\n\t\t" . TBL_BR;
}
$OUT .= "\n\t<tr>\n\t\t<th> </th>\n\t\t<th>Date</th>\n\t\t<th>Reference</th>\n\t\t<th>Description</th>\n\t\t<th>Debit</th>\n\t\t<th>Credit</th>\n\t\t<th>Balance</th>\n\t\t<th>Contra Acc</th>\n\t</tr>\n\t{$trans}\n\t</table>";
if (!$pure) {
$OUT .= mkQuickLinks(ql("index-reports.php", "Financials"), ql("index-reports-journal.php", "Current Year Details General Ledger Reports"), ql("../core/acc-new2.php", "Add New Account"));
$OUT .= "\n\t\t</center>";
}
return $OUT;
}
function confirm_data($_POST)
{
extract($_POST);
if (isset($back)) {
return enter_data($_POST);
}
/* do account number changes */
if (isset($topacc) && is_array($topacc)) {
$qry = new dbSql();
foreach ($topacc as $fid => $v) {
if (isset($accnum[$fid])) {
$sql = "UPDATE cubit.import_data\n\t\t\t\t\t\tSET des1='{$topacc[$fid]}/{$accnum[$fid]}'\n\t\t\t\t\t\tWHERE id='{$fid}'";
$qry->setSql($sql);
$qry->run();
}
}
}
$qry = new dbSelect("import_data", "cubit");
$qry->run();
$check_num = new dbSelect("accounts", "core");
$check_dup = new dbSelect("import_data", "cubit");
while ($fd = $qry->fetch_array()) {
$fid = $fd['id'];
$accnum_parts = explode("/", $fd["des1"]);
if (isset($topacc[$fid]) && isset($accnum[$fid])) {
$check_num->setOpt(grp(m("where", wgrp(m("topacc", $accnum_parts[0]), m("accnum", $accnum_parts[1])))));
$check_num->run();
$check_dup->setOpt(grp(m("where", "des1='{$fd['des1']}' AND id!='{$fd['id']}'")));
$check_dup->run();
if ($check_num->num_rows() > 0 || $check_dup->num_rows() > 0) {
return enter_data2($_POST);
}
}
}
global $_SESSION;
$out = "\n\t\t<h3>Trial Balance Import</h3>\n\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t<input type='hidden' name='key' value='write' />\n\t\t\t<input type='hidden' name='login' value='1' />\n\t\t\t<input type='hidden' name='div' value='{$_SESSION['USER_DIV']}' />\n\t\t\t<input type='hidden' name='login_user' value='{$_SESSION['USER_NAME']}' />\n\t\t\t<input type='hidden' name='login_pass' value='{$_SESSION['USER_PASS']}' />\n\t\t\t<input type='hidden' name='code' value='{$_SESSION['code']}' />\n\t\t\t<input type='hidden' name='comp' value='{$_SESSION['comp']}' />\n\t\t\t<input type='hidden' name='noroute' value='1' />\n\t\t\t<input type='hidden' name='prd' value='{$prd}' />\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th colspan='2'>Importing into " . getMonthName($prd) . " " . getYearOfFinMon($prd) . "</th>\n\t\t\t</tr>\n\t\t\t" . TBL_BR . "\n\t\t\t<tr>\n\t\t\t\t<th>Acc No</th>\n\t\t\t\t<th>Account Name</th>\n\t\t\t\t<th>Debit</th>\n\t\t\t\t<th>Credit</th>\n\t\t\t\t<th>Select Account to link to</th>\n\t\t\t</tr>";
db_conn('cubit');
$sql = "SELECT * FROM import_data ORDER BY des1";
$rslt = db_exec($sql);
$i = 0;
$tot_debit = 0;
$tot_credit = 0;
db_conn('core');
while ($fd = pg_fetch_array($rslt)) {
$fid = $fd['id'];
if ($accounts[$fid] == 0) {
$catss = explode(":", $cat[$fid]);
if ($catss[0] == "0") {
return enter_data2($_POST) . "<li class=err>You need to select a category for the new account</li>";
}
$add = "<input type='hidden' name='cat[{$fid}]' value='{$cat[$fid]}'>\n\t\t\t(New Account) {$catss['1']}</td>";
} else {
$Sl = "SELECT accid,accname FROM accounts WHERE accid='{$accounts[$fid]}'";
$Rx = db_exec($Sl);
$ad = pg_fetch_array($Rx);
$add = "{$ad['accname']}</td>";
}
$out .= "\n\t\t\t<input type='hidden' name='accounts[{$fid}]' value='{$accounts[$fid]}' />\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$fd['des1']}</td>\n\t\t\t\t<td>{$fd['des2']}</td>\n\t\t\t\t<td>{$fd['des3']}</td>\n\t\t\t\t<td>{$fd['des4']}</td>\n\t\t\t\t<td>{$add}</td>\n\t\t\t</tr>";
$i++;
$tot_debit += $fd['des3'];
$tot_credit += $fd['des4'];
}
$tot_debit = sprint($tot_debit);
$tot_credit = sprint($tot_credit);
$out .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='2'>Total</td>\n\t\t\t<td align='right'>{$tot_debit}</td>\n\t\t\t<td align='right'>{$tot_credit}</td>\n\t\t</tr>";
if ($cc_tot > 0) {
db_conn('cubit');
$Sl = "SELECT cusnum,accno,surname FROM customers ORDER BY surname";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>If you want to import your customer control account you need to add customers first</li>";
}
$out .= "\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='10'>\n\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Acc No</th>\n\t\t\t\t\t\t\t<th>Customer</th>\n\t\t\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t\t</tr>";
$tot = 0;
while ($cd = pg_fetch_array($Ri)) {
$cid = $cd['cusnum'];
$cbalance[$cid] = sprint($cbalance[$cid]);
$out .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$cd['accno']}</td>\n\t\t\t\t<td>{$cd['surname']}</td>\n\t\t\t\t<td align='right'><input type='hidden' size='12' name='cbalance[{$cid}]' value='{$cbalance[$cid]}' />{$cbalance[$cid]}</td>\n\t\t\t</tr>";
$tot += $cbalance[$cid];
}
$out .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2'><b>Total</b></td>\n\t\t\t\t\t<td align='right'><b>" . CUR . " {$cc_tot}</b></td>\n\t\t\t\t</tr>\n\t\t\t</td>\n\t\t</tr>";
$out .= TBL_BR;
if (sprint($cc_tot) != sprint($tot)) {
return enter_data2($_POST) . "<li class='err'>The total amount for balances for customers you entered is: " . CUR . " {$tot}, the\n\t\t\ttotal for the control account is: " . sprint($cc_tot) . ". These need to be the same.</li>";
}
}
if ($sc_tot > 0) {
db_conn('cubit');
$Sl = "SELECT supid,supno,supname FROM suppliers ORDER BY supname";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>If you want to import your supplier control account you need to add suppliers first</li>";
}
$out .= "\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='10'>\n\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Supplier No</th>\n\t\t\t\t\t\t\t<th>Supplier</th>\n\t\t\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t\t</tr>";
$tot = 0;
while ($cd = pg_fetch_array($Ri)) {
$sid = $cd['supid'];
$sbalance[$sid] = sprint($sbalance[$sid]);
$out .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>{$cd['supno']}</td>\n\t\t\t\t\t<td>{$cd['supname']}</td>\n\t\t\t\t\t<td align='right'><input type='hidden' size='12' name='sbalance[{$sid}]' value='{$sbalance[$sid]}'>" . CUR . " {$sbalance[$sid]}</td>\n\t\t\t\t</tr>";
$i++;
$tot += $sbalance[$sid];
}
$out .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2'><b>Total</b></td>\n\t\t\t\t\t<td align='right'><b>" . CUR . " {$sc_tot}</b></td>\n\t\t\t\t</tr>\n\t\t\t</td>\n\t\t</tr>";
$out .= TBL_BR;
if (sprint($sc_tot) != sprint($tot)) {
return enter_data2($_POST) . "<li class='err'>The total amount for balances for suppliers you entered is: " . CUR . " {$tot}, the\n\t\t\ttotal for the control account is: " . sprint($sc_tot) . ". These need to be the same.</li>";
}
}
if ($sal_tot > 0) {
db_conn('cubit');
$Sl = "SELECT empnum,enum,sname,fnames FROM employees ORDER BY sname,fnames";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>If you want to import your employee control account you need to add employees first</li>";
}
$out .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='4'><li class='err'>Please enter the employee balances to link up with 'Employees Control Account'</li></td>\n\t\t\t</tr>\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='10'>\n\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Employee Number</th>\n\t\t\t\t\t\t\t<th>Employee</th>\n\t\t\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t\t</tr>";
$tot = 0;
while ($cd = pg_fetch_array($Ri)) {
$eid = $cd['empnum'];
if (!isset($ebalance[$eid])) {
$ebalance[$eid] = "";
}
$ebalance[$eid] = sprint($ebalance[$eid]);
$out .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>{$cd['enum']}</td>\n\t\t\t\t\t<td>{$cd['sname']}, {$cd['fnames']}</td>\n\t\t\t\t\t<td align=right><input type='hidden' size='12' name='ebalance[{$eid}]' value='{$ebalance[$eid]}'>" . CUR . " {$ebalance[$eid]}</td>\n\t\t\t\t</tr>";
$i++;
$tot += $ebalance[$eid];
}
$out .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td colspan='2'><b>Total</b></td>\n\t\t\t\t\t\t<td align='right'><b>" . CUR . " {$sal_tot}</b></td>\n\t\t\t\t\t</tr>\n\t\t\t\t</td>\n\t\t\t</tr>";
$out .= "<tr><td><br></td></tr>";
if (sprint($sal_tot) != sprint($tot)) {
return enter_data2($_POST) . "<li class=err>The total amount for balances for employees you entered is: " . CUR . " {$tot}, the\n\t\t\ttotal for the control account is: " . sprint($sal_tot) . ". These need to be the same.</li>";
}
}
if ($i_tot > 0) {
db_conn('cubit');
$Sl = "SELECT stkid,stkcod,stkdes FROM stock ORDER BY stkcod";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>If you want to import your inventory control account you need to add stock first</li>";
}
$out .= "\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='10'>\n\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Stock Code</th>\n\t\t\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t\t\t<th>Units</th>\n\t\t\t\t\t\t</tr>";
$tot = 0;
while ($cd = pg_fetch_array($Ri)) {
$iid = $cd['stkid'];
if (!isset($ibalance[$iid])) {
$ibalance[$iid] = "";
}
$tot += $ibalance[$iid];
$units[$iid] += 0;
if (sprint($ibalance[$iid]) > 0 && $units[$iid] <= 0) {
return enter_data2($_POST) . "<li class='err'>You specified {$units[$iid]} units for {$cd['stkcod']}, but " . CUR . " {$ibalance[$iid]}.\n\t\t\t\tIf you want to enter an amount you need to give the qty.</li>";
}
$out .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>{$cd['stkcod']}</td>\n\t\t\t\t\t<td>{$cd['stkdes']}</td>\n\t\t\t\t\t<td><input type='hidden' size='12' name='ibalance[{$iid}]' value='{$ibalance[$iid]}'>{$ibalance[$iid]}</td>\n\t\t\t\t\t<td><input type='hidden' name='units[{$iid}]' value='{$units[$iid]}'>{$units[$iid]}</td>\n\t\t\t\t</tr>";
$i++;
}
$out .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td colspan='2'><b>Total</b></td>\n\t\t\t\t\t\t<td align='right'><b>" . CUR . " {$i_tot}</b></td>\n\t\t\t\t\t\t<td></td>\n\t\t\t\t\t</tr>\n\t\t\t\t</td>\n\t\t\t</tr>";
$out .= "<tr><td><br></td></tr>";
if (sprint($i_tot) != sprint($tot)) {
return enter_data2($_POST) . "<li class='err'>The total amount for balances for inventory you entered is: " . CUR . " {$tot}, the\n\t\t\ttotal for the control account is: " . sprint($i_tot) . ". These need to be the same.</li>";
}
}
$out .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><input type='submit' name='back' value='« Correction'></td>\n\t\t\t\t<td colspan='1' align='right'><input type='submit' value='Write »'></td>\n\t\t\t</tr>\n\t\t\t<input type='hidden' name='cc_tot' value='{$cc_tot}'>\n\t\t\t<input type='hidden' name='sal_tot' value='{$sal_tot}'>\n\t\t\t<input type='hidden' name='sc_tot' value='{$sc_tot}'>\n\t\t\t<input type='hidden' name='i_tot' value='{$i_tot}'>\n\t\t</form>\n\t\t</table>";
return $out;
}
function viewtran($_POST)
{
global $MONPRD, $PRDMON;
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($accnt, "string", 1, 10, "Invalid Accounts Selection.");
if ($accnt == 'slct') {
if (isset($accids)) {
foreach ($accids as $key => $accid) {
$v->isOk($accid, "num", 1, 20, "Invalid Account number.");
}
} else {
return "<li class='err'>Please select at least one account.</li>" . slctacc();
}
}
if ($v->isError()) {
$err = $v->genErrors();
return $confirm;
}
if ($_POST["key"] == "export") {
$pure = true;
} else {
$pure = false;
}
if ($accnt == 'all') {
$accids = array();
core_connect();
$sql = "SELECT accid FROM accounts WHERE div = '" . USER_DIV . "'";
$rs = db_exec($sql);
if (pg_num_rows($rs) > 0) {
while ($ac = pg_fetch_array($rs)) {
$accids[] = $ac['accid'];
}
} else {
return "<li calss='err'> There are no accounts yet in Cubit.</li>";
}
} else {
if ($accnt == "allactive") {
$accids = array();
$sql = "SELECT accid FROM core.trial_bal\n\t\t\t\tWHERE (debit!=0 OR credit!=0) AND div='" . USER_DIV . "'\n\t\t\t\t\tAND period<='" . $MONPRD[PRD_DB] . "'\n\t\t\t\tGROUP BY accid";
$qry = new dbSql($sql);
$qry->run();
while ($macc_data = $qry->fetch_array()) {
$accids[] = $macc_data["accid"];
}
}
}
$hide = "";
$trans = "";
foreach ($accids as $key => $accid) {
$accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
$trans .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t</tr>";
db_conn("audit");
$sql = "SELECT prd.*, map.period FROM audit.closedprd prd, core.prdmap map\n\t\t\t\tWHERE prd.prdnum=map.month\n\t\t\t\tORDER BY map.period::integer";
$clsRs = db_exec($sql) or errDie("Could not get closed periods from audit DB", SELF);
while ($cls = pg_fetch_array($clsRs)) {
$prd = $cls['prdnum'];
# Period name
$prdname = prdname($prd);
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='8' align='center'><h3>{$prdname}</h3></td>\n\t\t\t\t</tr>";
$hide = "";
if (isset($t)) {
unset($t);
}
# Get balances
$idRs = get($prd, "max(id), min(id)", "ledger", "acc", $accid);
$id = pg_fetch_array($idRs);
if ($id['min'] != 0) {
$balRs = get($prd, "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", "ledger", "id", $id['min']);
$bal = pg_fetch_array($balRs);
$cbalRs = get($prd, "cbalance,dbalance", "ledger", "id", $id['max']);
$cbal = pg_fetch_array($cbalRs);
} else {
if (!isset($t)) {
$trans .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td colspan='8' align='center'><li> There are no transactions in this period.</td>\n\t\t\t\t\t\t</tr>";
}
continue;
$balRs = get("core", "credit as cbalance, debit as dbalance", "trial_bal", "accid", $accid);
$bal = pg_fetch_array($balRs);
$cbal['cbalance'] = 0;
$cbal['dbalance'] = 0;
}
$t = "lemme ci";
if ($bal['dbalance'] > $bal['cbalance']) {
$bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
$bal['cbalance'] = "";
$balance = $bal['dbalance'];
$fl = "DT";
} elseif ($bal['cbalance'] > $bal['dbalance']) {
$bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
$bal['dbalance'] = "";
$balance = $bal['cbalance'];
$fl = "CT";
} else {
$bal['cbalance'] = "";
$bal['dbalance'] = "";
$balance = "0.00";
$fl = "";
}
$balance = sprint($balance);
$bal['cbalance'] = sprint($bal['cbalance']);
$bal['dbalance'] = sprint($bal['dbalance']);
// calculate which year the current period is in
$prd_y = getFinYear() - 1;
if ($prd < $PRDMON[1]) {
++$prd_y;
}
// make the date of the last day of the previous prd
$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, $prd_y));
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t\t<td>Br/Forwd</td><td>Brought Forward</td>\n\t\t\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
# --> Transaction reding comes here <--- #
$dbal['debit'] = 0;
$dbal['credit'] = 0;
$tranRs = get($prd, "*", "ledger", "acc", $accid);
while ($tran = pg_fetch_array($tranRs)) {
$dbal['debit'] += $tran['debit'];
$dbal['credit'] += $tran['credit'];
# Current(Running) balance
if ($tran['dbalance'] > $tran['cbalance']) {
$tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
$tran['cbalance'] = "";
$cbalance = $tran['dbalance'];
$cfl = "DT";
} elseif ($tran['cbalance'] > $tran['dbalance']) {
$tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
$tran['dbalance'] = "";
$cbalance = $tran['cbalance'];
$cfl = "CT";
} else {
$tran['cbalance'] = "";
$tran['dbalance'] = "";
$cbalance = "0.00";
$cfl = "";
}
# Format date
$tran['edate'] = explode("-", $tran['edate']);
$tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
$tran['debit'] = sprint($tran['debit']);
$tran['credit'] = sprint($tran['credit']);
$trans .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td><br></td>\n\t\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t\t<td align='right'>{$cbalance} {$cfl}</td>\n\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t</tr>";
}
# Total balance changes
if ($dbal['debit'] > $dbal['credit']) {
$dbal['debit'] = sprint($dbal['debit'] - $dbal['credit']);
$dbal['credit'] = "";
} elseif ($dbal['credit'] > $dbal['debit']) {
$dbal['credit'] = sprint($dbal['credit'] - $dbal['debit']);
$dbal['debit'] = "";
} else {
$dbal['credit'] = "0.00";
$dbal['debit'] = "0.00";
}
$trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t\t<td>A/C Total</td>\n\t\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
}
$trans .= "<tr><td colspan='8'><br></td></tr>";
}
$OUT = "";
if (!$pure) {
$OUT .= "<center>";
}
$OUT .= "\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>Year Review General Ledger</h3></td>\n\t\t\t</tr>";
if (!$pure) {
$OUT .= "\n\t\t<tr>\n\t\t\t<form action='" . SELF . "' method='post'>\n\t\t\t<input type='hidden' name='key' value='export' />\n\t\t\t<input type='hidden' name='prd' value='{$prd}' />\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}' />\n\t\t\t" . array2form($accids, "accids") . "\n\t\t\t<td colspan='8' align='center'>\n\t\t\t\t<input type='submit' value='Export to Spreadsheet'>\n\t\t\t</td>\n\t\t\t</form>\n\t\t</tr>\n\t\t" . TBL_BR;
}
$OUT .= "\n\t<tr>\n\t\t<th> </th>\n\t\t<th>Date</th>\n\t\t<th>Reference</th>\n\t\t<th>Description</th>\n\t\t<th>Debit</th>\n\t\t<th>Credit</th>\n\t\t<th>Balance</th>\n\t\t<th>Contra Acc</th>\n\t</tr>\n\t{$trans}\n\t</table>";
if (!$pure) {
$OUT .= mkQuickLinks(ql("index-reports.php", "Financials"), ql("index-reports-journal.php", "Current Year Details General Ledger Reports"), ql("../core/acc-new2.php", "Add New Account"));
$OUT .= "\n\t\t</center>";
}
return $OUT;
}
/**
* Calculate PAYE on salary
*
* @param array &$emp employee db row
* @param int $prd month/week for which salary is calculated
* @param float $paye_salary taxable salary
* @param int $tyear periods in year (weekly emp: 52, monthly: 12, etc...)
* @param int $age employee age
* @return float
*/
function calculate_paye_old(&$emp, $prd, $paye_salary, $tyear, $age)
{
$empnum = $emp["empnum"];
//define("ACTUAL_EMP_YEAR",getYearOfEmpMon($prd));
payedbg("fromsal: {$paye_salary}<br />");
/* query expression for previous payments/paye */
if ($emp["payprd"] == "w" || $emp["payprd"] == "f" || $emp["payprd"] == "d") {
list($month, $week) = explode(":", $prd);
if ($emp["payprd"] == "d") {
$day = $week;
} else {
$day = 1;
}
$mw_b = "((month::int>='3' AND month::int<'{$month}')\n\t\t\t\tOR ('{$month}' < '3' AND (month::int<'{$month}' OR month::int>='3')))\n\t\t\t\tOR (month::int='{$month}' AND week<'{$week}')";
} else {
$month = $prd;
$day = 1;
$mw_b = "(month::int>='3' AND month::int<'{$month}')\n\t\t\t\tOR ('{$month}' < '3' AND (month::int<'{$month}' OR month::int>='3'))";
}
/* determine previously paid amounts */
$sql = "SELECT 1 AS m, * FROM cubit.salamt_pay\n\t\t\tWHERE empnum='{$empnum}' AND cyear='" . EMP_YEAR . "' AND ({$mw_b})\n\t\t\tUNION\n\t\t\tSELECT -1 AS m, * FROM cubit.salamt_rev\n\t\t\tWHERE empnum='{$empnum}' AND cyear='" . EMP_YEAR . "' AND ({$mw_b})";
$qry = new dbSql($sql);
$qry->run();
$prev_emp = $emp["cyear"] == EMP_YEAR && ($emp["prevemp_tax"] > 0 && $emp["prevemp_remun"] > 0);
payedbg("prevemp: " . ($prev_emp ? "true" : "false") . "<br />");
if ($prev_emp) {
$totpaye = $emp["prevemp_tax"];
$totgross = $paye_salary + $emp["prevemp_remun"];
} else {
$totpaye = 0;
$totgross = $paye_salary;
}
$totnetgross = 0;
while ($row = $qry->fetch_array()) {
$totpaye += $row["paye"] * $row["m"];
$totgross += $row["payegross"] * $row["m"];
$totnetgross += $row["netgross"] * $row["m"];
}
/* calculate current year fraction */
$fday = 1;
$year_fmon = getYearOfEmpMon(3);
$year_month = getYearOfEmpMon($month);
$fmstart = mktime(0, 0, 0, 3, $fday, $year_fmon);
$tmstart = mktime(0, 0, 0, $month, $day, $year_month);
/* if weekly/fortnightly, find the first friday (effective first week)
and last day of selected weeks */
if ($emp["payprd"] == "w" || $emp["payprd"] == "f") {
payedbg("week: {$week}<br />");
/* effective first week */
while (date("w", $fmstart) != 5) {
$fmstart = mktime(0, 0, 0, 3, ++$fday, $year_fmon);
}
/* end of week, find first week first */
while (date("w", $tmstart) != 5) {
$tmstart = mktime(0, 0, 0, $month, ++$day, $year_month);
}
/* move the day by "week" number of weeks */
$day += ($week - 1) * ($emp["payprd"] == "w" ? 1 : 2) * 7;
$tmstart = mktime(0, 0, 0, $month, $day, $year_month);
// hack(fortnightly): increase to end of 1st week
if ($emp["payprd"] == "f") {
$fmstart += 7 * 24 * 60 * 60;
//$tmstart += 7 * 24 * 60 * 60;
}
}
/* count the periods in the past */
$curprd = prdage($fmstart, $tmstart, $emp["payprd"]);
payedbg("curprd calc: " . mkdatet($fmstart) . " - " . mkdatet($tmstart) . BR);
payedbg("curprd: {$curprd}<br />");
/* determine start period from the $curprd */
$finYearStart = getYearOfEmpMon(3);
$styear = extractYear($emp["hiredate"]);
$stmon = extractMonth($emp["hiredate"]);
if ($styear < $finYearStart || $styear == $finYearStart && $stmon < 3) {
$stprd = 1;
} else {
//$stprd = prdage(mktimefd($emp["hiredate"]), $tmstart, $emp["payprd"]);
payedbg("stprd calc: " . date("Y-m-d", mktimefd(getYearOfEmpMon(3) . "-03-01")));
payedbg(" - " . date("Y-m-d", mktimefd($emp["hiredate"])) . "<br />");
$stprd = prdage(mktimefd(getYearOfEmpMon(3) . "-03-01"), mktimefd($emp["hiredate"]), $emp["payprd"]);
}
if ($prev_emp) {
$working_prd = $curprd;
$totprd = $tyear;
//$totprd = $tyear - ($stprd - 0);
} else {
// num of prds working
$working_prd = $curprd - ($stprd - 1);
// total periods should be working this year
$totprd = $tyear - ($stprd - 1);
}
payedbg("startprd: {$stprd}<br />");
payedbg("totprd: {$totprd}<br />");
payedbg("totgross: {$totgross}<br />");
payedbg("totnetgross: {$totnetgross}<br />");
payedbg("workprd: {$working_prd}<br />");
//$working_prd = 2;
//$totprd = 7;
$paye_salary = $totgross / $working_prd;
payedbg("payesal1: {$paye_salary}<br />");
/* scale paye to amount of periods should be working */
$paye_salary *= $totprd / $tyear;
//$paye_salary *= $tyear / $curprd;
payedbg("payesal2: {$paye_salary}<br />");
$paye = bracket_calcpaye_old($age, $paye_salary, $tyear);
if (($amt = sprint($paye * ($working_prd / $totprd) - $totpaye)) < 0) {
$amt = 0;
}
return $amt;
}
}
/* create objects */
$oPOP = new clsPOPMail();
$oMSG = new clsMailMsg();
//db_conn("cubit_aaae");
//send_trhmsg("cust", "-1", "*****@*****.**", "rspkey", "99887766554433221100");
//exit(1);
$smallest_wait = -1;
$ctr = 0;
while (true) {
$companies = array();
/* fetch the company list */
if ($ctr == 0) {
db_con("cubit");
$sql = "SELECT c.code FROM companies c WHERE (true) {$wh}";
$comps = new dbSql($sql);
$comps->run();
$companies = array();
while ($row = $comps->fetch_array()) {
/* read the companies configuration */
print "Company Read: {$row['code']}\n";
db_con("cubit_{$row['code']}");
$cfg = getTrhConfig();
/* not enabled */
if ($cfg["MANAGEUSER"] <= 0 || empty($cfg["POP3_SERVER"]) || empty($cfg["SMTP_SERVER"])) {
continue;
}
$companies[$row["code"]] = $cfg;
}
$comps->free();
}
