function import($frm)
{
/* @var $frm cForm */
if ($frm->validate("import")) {
return view($frm);
}
/* get field indexes */
$stkcod = false;
$price = false;
foreach ($_REQUEST["fld"] as $fi => $ft) {
if ($ft != "ignore") {
${$ft} = $fi;
}
}
/* import file if all field types specified */
if ($stkcod === false || $price === false) {
$frm->setmsg("<li class='err'>Not all field types satisfied</li>");
} else {
$qry = new dbSelect("spricelist", "exten", grp(m("cols", "listid"), m("where", "suppid='{$_REQUEST['supid']}'")));
$qry->run();
if ($qry->num_rows() <= 0) {
$suppinfo = qrySupplier($_REQUEST["supid"]);
$cols = grp(m("suppid", $_REQUEST["supid"]), m("listname", $suppinfo["supname"]), m("div", USER_DIV));
$upd = new dbUpdate("spricelist", "exten", $cols);
$upd->run(DB_INSERT);
$listid = $upd->lastid("listid");
} else {
$listid = $qry->fetch_result();
}
$upd = new dbDelete("splist_prices", "exten", "listid='{$listid}'");
$upd->run();
$upd = new dbUpdate("splist_prices", "exten");
$invalid_fields = array();
$nosuch_fields = array();
$file = ucfs::file("supplist");
foreach ($file as $rd) {
$ri = explode(",", $rd);
$ri[$stkcod] = trim($ri[$stkcod]);
$ri[$price] = trim($ri[$price]);
if (cForm::validateValue($ri[$stkcod], "string", 1, 250) || cForm::validateValue($ri[$price], "float", 1, 40)) {
$invalid_fields[] = $ri[$stkcod];
continue;
}
$stkid = suppStkid($_REQUEST["supid"], $ri[$stkcod]);
if ($stkid === false) {
$stkinfo = array("stkid" => "0", "catid" => "0", "prdcls" => "0");
} else {
$stkinfo = qryStock($stkid, "stkid, catid, prdcls");
}
if (!isset($_REQUEST["vatinc"])) {
$ri[$price] += $ri[$price] * TAX_VAT / 100;
}
$cols = grp(m("listid", $listid), m("stkid", $stkinfo["stkid"]), m("catid", $stkinfo["catid"]), m("clasid", $stkinfo["prdcls"]), m("price", $ri[$price]), m("div", USER_DIV), m("supstkcod", $ri[$stkcod]));
$upd->setCols($cols);
$upd->run();
}
if (count($invalid_fields) > 0) {
$msg = "<br />The following items weren't imported because they contain\n\t\t\t\tinvalid values for either the stock code or the price:<br />";
foreach ($invalid_fields as $v) {
$msg .= " - {$v}<br />";
}
} else {
$msg = "";
}
$frm->setmsg("<li class='err'>Successfully imported new pricelist.{$msg}</li>");
}
return view($frm);
}
function write()
{
if (PRD_STATE == 'py') {
return "<center><li class='err'>Please update your transaction year before closing financial year: Click <a href='../set-period-use.php'>here</a> to do so.</li></center>";
}
// Check if last database has not been reached
if (YR_DB == "yr10") {
$OUTPUT = "<center>Warning : The System has reached its Last year of operation, Please contact the support team to reset.";
require "template.php";
}
$i = substr(YR_DB, 2) + 1;
$nextyr = "yr" . $i;
$sdate = DATE_STD;
pglib_transaction("BEGIN");
global $PRDMON;
$stmnt = financialStatements::trialbal(array("heading_3" => "Trial Balance: Year End", "month_to" => $PRDMON[12]), true);
save_statement($stmnt, "trial_bal");
$stmnt = financialStatements::incomestmnt(array("customized" => true, "this_year_year_to_date" => true, "heading_3" => "Income Statement: Year End", "budget" => true, "this_year_budget" => true, "month_to" => $PRDMON[12]), true);
save_statement($stmnt, "income_stmnt");
$stmnt = financialStatements::balsheet(array("customized" => true, "this_year_year_to_date" => true, "heading_3" => "Balance Sheet: Year End", "month_to" => $PRDMON[12]), true);
save_statement($stmnt, "bal_sheet");
# Copy balance sheet table
core_connect();
$sql = "SELECT * FROM bal_sheet";
$balSheet = db_exec($sql) or errDie("Could not copy Balances to year DB", SELF);
while ($bal = pg_fetch_array($balSheet)) {
db_conn(YR_DB);
$sql = "INSERT INTO bal_sheet (type, ref, value, div) VALUES ('{$bal['type']}', '{$bal['ref']}', '{$bal['value']}', '{$bal['div']}')";
$inRslt = db_exec($sql) or print $sql;
}
core_connect();
$sql = "SELECT * FROM core.trial_bal WHERE period='12'";
$trialBal = db_exec($sql) or errDie("Could not copy Balances to year DB", SELF);
while ($bal = pg_fetch_array($trialBal)) {
$sql = "\n\t\t\tINSERT INTO " . YR_DB . ".year_balance (\n\t\t\t\taccid, topacc, accnum, accname, debit, credit, div\n\t\t\t) VALUES (\n\t\t\t\t'{$bal['accid']}', '{$bal['topacc']}', '{$bal['accnum']}', '{$bal['accname']}', '{$bal['debit']}', '{$bal['credit']}', '{$bal['div']}'\n\t\t\t)";
$inRslt = db_exec($sql) or errDie("Failed to store year balance.");
}
//$sql = "CREATE TABLE \"".YR_DB."\".stkledger AS SELECT * FROM core.stkledger";
//$rslt = db_exec_safe($sql);
// make a copy from the trial balance
$sql = "DROP TABLE \"" . YR_DB . "\".trial_bal";
//$rslt = db_exec_safe($sql);
$sql = "CREATE TABLE \"" . YR_DB . "\".trial_bal AS SELECT * FROM core.trial_bal";
$rslt = db_exec_safe($sql);
// recreate the trial balance actual view
$sql = "\n\t\tCREATE OR REPLACE VIEW \"" . YR_DB . "\".trial_bal_actual AS\n\t\tSELECT tb.accid, tb.topacc, tb.accnum, tb.accname, tb.vat, tb.div, tb.acctype, \n\t\t\tCASE\n\t\t\t\tWHEN tb.period = 1 THEN tb.debit\n\t\t\t\tELSE tb.debit - atb.debit\n\t\t\tEND AS debit,\n\t\t\tCASE\n\t\t\t\tWHEN tb.period = 1 THEN tb.credit\n\t\t\t\tELSE tb.credit - atb.credit\n\t\t\tEND AS credit, tb.month, tb.period\n\t\tFROM \"" . YR_DB . "\".trial_bal tb LEFT JOIN \"" . YR_DB . "\".trial_bal atb\n\t\t\tON tb.period = (atb.period::int + 1) AND tb.accid = atb.accid;";
db_exec_safe($sql);
/* fetch customer balances */
$custs = qryCustomer(false, "cusnum");
$custdebit = array();
$custcredit = array();
while ($cd = $custs->fetch_array()) {
$qry = new dbSelect("custledger", $PRDMON['12'], grp(m("cols", "cbalance, dbalance"), m("where", "cusnum='{$cd['cusnum']}'"), m("order", "id DESC"), m("limit", 1)));
$qry->run();
if ($qry->num_rows() <= 0) {
$custcredit[$cd["cusnum"]] = 0;
$custdebit[$cd["cusnum"]] = 0;
} else {
$qry->fetch_array();
$custcredit[$cd["cusnum"]] = $qry->d["cbalance"];
$custdebit[$cd["cusnum"]] = $qry->d["dbalance"];
}
}
/* fetch supplier balances */
$supps = qrySupplier(false, "supid");
$suppdebit = array();
$suppcredit = array();
while ($sd = $supps->fetch_array()) {
$qry = new dbSelect("suppledger", $PRDMON['12'], grp(m("cols", "cbalance, dbalance"), m("where", "supid='{$sd['supid']}'"), m("order", "id DESC"), m("limit", 1)));
$qry->run();
if ($qry->num_rows() <= 0) {
$suppcredit[$sd["supid"]] = 0;
$suppdebit[$sd["supid"]] = 0;
} else {
$qry->fetch_array();
$suppcredit[$sd["supid"]] = $qry->d["cbalance"];
$suppdebit[$sd["supid"]] = $qry->d["dbalance"];
}
}
/* copy the inventory ledger */
for ($i = 1; $i <= 12; ++$i) {
$mname = strtolower(getMonthName($i));
$sql = "CREATE TABLE audit.{$mname}_stkledger \n\t\t\t\tAS \n\t\t\t\tSELECT * FROM \"{$i}\".stkledger";
db_exec($sql) or errDie("Error copying inventory ledger (P{$i}).");
}
// Empty All Period Databases
for ($i = 1; $i <= 14; $i++) {
db_conn($i);
$sql = "TRUNCATE TABLE transect;";
$sql .= "TRUNCATE TABLE ledger;";
$sql .= "TRUNCATE TABLE custledger;";
$sql .= "TRUNCATE TABLE suppledger;";
$sql .= "TRUNCATE TABLE empledger;";
db_exec($sql) or errDie("Unable to empty Period databases", SELF);
}
$qryi = new dbUpdate();
recreateAudit();
/* FP AUDIT FIX
for ($p = 1; $p <= 12; ++$p) {
$monnum = $PRDMON[$i];
$monname = strtolower(getMonthName($i));
}
*/
/* create the customer ledger balance entries */
$custs = qryCustomer(false, "cusnum");
while ($cd = $custs->fetch_array()) {
for ($i = 1; $i <= 12; ++$i) {
$cols = grp(m("cusnum", $cd["cusnum"]), m("contra", 0), m("edate", $sdate), m("sdate", raw("CURRENT_DATE")), m("eref", 0), m("descript", "Balance"), m("credit", 0), m("debit", 0), m("cbalance", $custcredit[$cd["cusnum"]]), m("dbalance", $custdebit[$cd["cusnum"]]), m("div", USER_DIV));
$qryi->setTable("custledger", "{$i}");
$qryi->setOpt($cols);
$qryi->run(DB_INSERT);
/* audit customer ledger */
$cols = grp(m("cusnum", $cd["cusnum"]), m("contra", 0), m("edate", $sdate), m("sdate", raw("CURRENT_DATE")), m("eref", 0), m("descript", "Balance"), m("credit", 0), m("debit", 0), m("cbalance", $custcredit[$cd["cusnum"]]), m("dbalance", $custdebit[$cd["cusnum"]]), m("div", USER_DIV), m("actyear", YR_NAME));
$qryi->setTable(getMonthName($i) . "_custledger", "audit");
$qryi->setOpt($cols);
$qryi->run(DB_INSERT);
}
}
/* create the supplier ledger balance entries */
$supps = qrySupplier(false, "supid");
while ($sd = $supps->fetch_array()) {
for ($i = 1; $i <= 12; ++$i) {
$qryi->setTable("suppledger", "{$i}");
$cols = grp(m("supid", $sd["supid"]), m("contra", "0"), m("edate", $sdate), m("sdate", raw("CURRENT_DATE")), m("eref", "0"), m("descript", "Balance"), m("credit", "0"), m("debit", "0"), m("div", USER_DIV), m("cbalance", $suppcredit[$sd["supid"]]), m("dbalance", $suppdebit[$sd["supid"]]));
$qryi->setCols($cols);
$qryi->run(DB_INSERT);
}
}
/* create the stock ledger entries */
$stock = qryStock(false, "stkid, stkcod, stkdes, units, csamt");
while ($stk = $stock->fetch_array()) {
for ($i = 1; $i <= 12; ++$i) {
$qryi->setTable("stkledger", "{$i}");
$cols = grp(m("stkid", $stk["stkid"]), m("stkcod", $stk["stkcod"]), m("stkdes", $stk["stkdes"]), m("trantype", "bal"), m("edate", $sdate), m("qty", $stk["units"]), m("csamt", $stk["csamt"]), m("balance", $stk["csamt"]), m("bqty", $stk["units"]), m("details", "Balance"), m("div", USER_DIV), m("yrdb", $nextyr));
$qryi->setCols($cols);
$qryi->run(DB_INSERT);
}
}
/* do the retained income entries for all branches */
$sql = "SELECT div FROM cubit.branches";
$branRs = db_exec($sql) or errDie("Could not access branches table.");
while ($bran = pg_fetch_array($branRs)) {
fintran($bran['div']);
}
/* create the ledger account balance entries */
for ($i = 1; $i <= 12; ++$i) {
$periodname = getMonthName($i);
$sql = "\n\t\t\tINSERT INTO " . YR_DB . ".{$periodname} (\n\t\t\t\taccid, topacc, accnum, accname, debit, credit, div\n\t\t\t) SELECT accid, topacc, accnum, accname, debit, credit, div\n\t\t\t\tFROM core.trial_bal WHERE month='1'";
db_exec($sql) or errDie("Error creating ledger balances (1)");
$sql = "\n\t\t\tINSERT INTO \"{$i}\".openbal (\n\t\t\t\taccid, accname, debit, credit, div\n\t\t\t) SELECT accid, accname, debit, credit, div\n\t\t\t\tFROM core.trial_bal WHERE month='1'";
db_exec($sql) or errDie("Error creating ledger balances (2)");
$sql = "\n\t\t\tINSERT INTO \"{$i}\".ledger (\n\t\t\t\tacc, contra, edate, eref, descript, credit, debit, div, caccname, ctopacc, caccnum, cbalance, dbalance\n\t\t\t) SELECT accid, accid, CURRENT_DATE, '0', 'Balance', '0', '0', div, accname, topacc, accnum, credit, debit\n\t\t\t\tFROM core.trial_bal WHERE month='1'";
db_exec($sql) or errDie("Error creating ledger balances (3)");
}
/* close and select new year */
selectNextYear($nextyr);
/* mark year as closed */
$sql = "UPDATE core.year SET closed = 'y' WHERE yrdb = '" . YR_DB . "'";
$rslt = db_exec($sql) or errDie("Could not Set Next Year Database and Name", SELF);
pglib_transaction("COMMIT");
// Display
$write = "<center><h3> Current Year has been closed </h3>\n\t<b>( i ) The next Year has been activated ( i )</b></center>" . mkQuickLinks();
return $write;
}
