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