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(); }