コード例 #1
0
function print_sheet()
{
    $OUTPUT = clean_html(financialStatements::balsheet($_POST));
    switch ($_POST["key"]) {
        case ct("Print"):
            require "../tmpl-print.php";
            break;
        case ct("Save"):
            db_conn("core");
            $sql = "INSERT INTO save_bal_sheet (output, gendate, div) VALUES ('" . base64_encode($OUTPUT) . "', current_date, '" . USER_DIV . "')";
            $svincRslt = db_exec($sql) or errDie("Unable to save the balance sheet to Cubit.");
            return "\n\t\t\t\t<li>Balance Sheet has been successfully saved to Cubit.</li>\n\t\t\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t\t\t<tr><th>Quick Links</th></tr>\n\t\t\t\t\t<tr class='datacell'><td align='center'><a target='_blank' href='../core/acc-new2.php'>Add account (New Window)</a></td></tr>\n\t\t\t\t\t<tr class='datacell'><td align='center'><a href='index-reports.php'>Financials</a></td></tr>\n\t\t\t\t\t<tr class='datacell'><td align='center'><a href='index-reports-stmnt.php'>Current Year Financial Statements</a></td></tr>\n\t\t\t\t\t<tr class='datacell'><td align='center'><a href='../main.php'>Main Menu</td></tr>\n\t\t\t\t</table>";
            break;
        case ct("Export to Spreadsheet"):
            require_lib("xls");
            StreamXLS("balance_sheet", $OUTPUT);
            break;
    }
}
コード例 #2
0
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;
}