コード例 #1
2
function update($_POST)
{
    # Get Vars ( banked[] )
    extract($_POST);
    # check if anything is selected
    if (!isset($banked)) {
        $err = "<li class='err'> Please Select at least one entry to update.";
        return cashbook($_POST, $err);
    }
    /* - Start Hooks - */
    $vatacc = gethook("accnum", "salesacc", "name", "VAT");
    /* - End Hooks - */
    $refnum = getrefnum();
    /*refnum*/
    db_conn('core');
    $rid = pglib_lastid("save_bank_recon", "id");
    $rid++;
    # Record all trans
    foreach ($banked as $key => $cashid) {
        # Connect to database
        db_Connect();
        $sql = "SELECT * FROM cashbook WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
        $cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve details from database.", SELF);
        $cash = pg_fetch_array($cashRslt);
        # Set record as banked
        db_connect();
        $sql = "UPDATE cashbook SET banked = 'yes',rid='{$rid}' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
        $Rslt = db_exec($sql) or errDie("Unable to set bank deposit as banked in Cubit.", SELF);
    }
    // Connect to database
    db_connect();
    $sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND banked = 'no' AND div = '" . USER_DIV . "' ORDER BY date DESC";
    $cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank transactions from database.", SELF);
    $sql = "SELECT * FROM bankacct WHERE bankid= '{$bankid}' AND div = '" . USER_DIV . "'";
    $bankRslt = db_exec($sql);
    $bank = pg_fetch_array($bankRslt);
    $cur = CUR;
    $amtd = "amount";
    if ($bank['btype'] == 'int') {
        $currs = getSymbol($bank['fcid']);
        $cur = $currs['symbol'];
        $amtd = "famount";
    }
    $tot = 0;
    $totr = 0;
    $totp = 0;
    $recpts = "";
    $paymnts = "";
    while ($cash = pg_fetch_array($cashRslt)) {
        if ($cash['trantype'] == "deposit") {
            $recpts .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$cash['date']}</td>\n\t\t\t\t\t<td>{$cash['descript']}</td>\n\t\t\t\t\t<td align='right'>{$cur} {$cash[$amtd]}</td>\n\t\t\t\t</tr>";
            $totr += $cash[$amtd];
        } else {
            $paymnts .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$cash['date']}</td>\n\t\t\t\t\t<td>{$cash['descript']}</td>\n\t\t\t\t\t<td align='right'>{$cur} {$cash[$amtd]}</td>\n\t\t\t\t</tr>";
            $totp += $cash[$amtd];
        }
        $tot += $cash[$amtd];
    }
    $reconbal = sprint($cbal + ($totr - $totp));
    # get hook account number
    core_connect();
    $sql = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "'";
    $rslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
    # check if link exists
    if (pg_numrows($rslt) < 1) {
        return "<li class='err'> ERROR : The bank account that you selected doesn't appear to have an account linked to it.";
    }
    $banklnk = pg_fetch_array($rslt);
    # Get bank balance
    $sql = "SELECT (debit - credit) as bal FROM trial_bal WHERE period='12' AND accid = '{$banklnk['accnum']}' AND div = '" . USER_DIV . "'";
    $brslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
    $bal = pg_fetch_array($brslt);
    if ($bank['btype'] == 'int') {
        $bal['bal'] = $bank['fbalance'];
    }
    $diff = sprint($reconbal - $bal['bal']);
    $derr = "";
    if ($diff != 0) {
        $derr = "\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><b class='err'>Bank statement and computer balance not balancing by</b></td>\n\t\t\t\t<td align='right'>{$cur} {$diff}</td>\n\t\t\t</tr>";
    }
    $totp = sprint($totp);
    // Layout
    $update = "\n\t\t<center>\n\t\t<h3>Bank Reconciliation Output</h3>\n\t\t<form action='" . SELF . "' method='POST' name='form1'>\n\t\t\t<input type='hidden' name='key' value='save'>\n\t\t\t<input type='hidden' name='bankid' value='{$bankid}'>\n\t\t<table cellpadding='2' cellspacing='0' border=0 bordercolor='#000000' width='80%'>\n\t\t\t<tr>\n\t\t\t\t<td colspan='10'>\n\t\t\t\t\t<table cellpadding='2' cellspacing='0' border=0 bordercolor='#000000' width=100%>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td><b>Bank Account : </b>{$bank['accname']}</td>\n\t\t\t\t\t\t\t<td></td>\n\t\t\t\t\t\t\t<td align='right'><b>Prepared By : </b>" . USER_NAME . "</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td><b>Closing Balance As per Bank Statement : </b>{$cur} {$cbal}</td>\n\t\t\t\t\t\t\t<td></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td><b>Plus Outstanding Receipts :</b></td>\n\t\t\t</tr>\n\t\t\t<!--<tr><th>Date</th><th>Reference</th><th>Amount</th></tr>-->\n\t\t\t{$recpts}\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td align='right'>____________</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' align='right'><b>Sub Total</b></td>\n\t\t\t\t<td align='right'>{$cur} {$totr}</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td><b>Less Outstanding Payments :</b></td>\n\t\t\t</tr>\n\t\t\t<!--<tr><th>Date</th><th>Reference</th><th>Amount</th></tr>-->\n\t\t\t{$paymnts}\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td align='right'>____________</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' align='right'><b>Sub Total</b></td>\n\t\t\t\t<td align='right'>{$cur} {$totp}</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br><td></tr>\n\t\t\t{$derr}\n\t\t\t<tr><td><br><td></tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td align='right'>____________</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><b>Reconciled Bank Balance</b></td>\n\t\t\t\t<td align='right'>{$cur} {$reconbal}</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><b>Computer Bank Balance</b></td>\n\t\t\t\t<td align='right'>{$cur} {$bal['bal']}</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td align='right'>____________</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><b>Diff</b></td>\n\t\t\t\t<td align='right'>{$cur} {$diff}</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td align='right'>____________</td>\n\t\t\t</tr>";
    $upcode = base64_encode($update);
    $button = "</table></form>";
    core_connect();
    $gendate = date("Y-m-d");
    $sql = "\n\t\tINSERT INTO save_bank_recon (\n\t\t\tbankid, gendate, recon, div\n\t\t) VALUES (\n\t\t\t'{$bankid}', '{$gendate}', '{$upcode}', '" . USER_DIV . "'\n\t\t)";
    $saveRslt = db_exec($sql) or errDie("Unable to save bank recon to database", SELF);
    $update .= $button;
    $OUTPUT = $update;
    require "../tmpl-print.php";
}
コード例 #2
0
#
#
#
#
#
#
#
#
#
require "../settings.php";
// Get global variables & functions
# decide what to do
if (isset($_POST["key"])) {
    switch ($_POST["key"]) {
        case "out":
            $OUTPUT = cashbook($_POST['bankid']);
            break;
        default:
            $OUTPUT = view();
    }
} else {
    # Display default output
    $OUTPUT = view();
}
# get template
require "../template.php";
# Default view
function view()
{
    // main layout
    $view = "\n\t<h3>Outstanding Bank Account Entries</h3>\n\t<table " . TMPL_tblDflts . " width='350'>\n\t<form action='" . SELF . "' method='POST' name='form'>\n\t\t<input type='hidden' name='key' value='out'>\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>Bank Account</td>\n\t\t\t<td valign='center'>\n\t\t\t\t<select name=bankid>";
コード例 #3
0
function update($_POST)
{
    # Get Vars ( banked[] )
    foreach ($_POST as $key => $value) {
        ${$key} = $value;
    }
    # Check if anything is selected
    if (!isset($banked)) {
        $err = "<li class=err> Please Select at least one entry to update.";
        return cashbook($_POST, $err);
    }
    /* - Start Hooks - */
    $vatacc = gethook("accnum", "salesacc", "name", "VAT");
    /* - End Hooks - */
    $refnum = getrefnum();
    /*refnum*/
    # Record all trans
    $tot = 0;
    $totr = 0;
    $totp = 0;
    $recpts = "";
    $paymnts = "";
    foreach ($banked as $key => $cashid) {
        // Connect to database
        db_Connect();
        $sql = "SELECT * FROM cashbook WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
        $cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve details from database.", SELF);
        $cash = pg_fetch_array($cashRslt);
        # Set record as banked
        db_connect();
        $sql = "UPDATE cashbook SET banked = 'yes' WHERE cashid='{$cashid}' AND div = '" . USER_DIV . "'";
        $Rslt = db_exec($sql) or errDie("Unable to set bank deposit as banked in Cubit.", SELF);
    }
    // Connect to database
    db_Connect();
    $sql = "SELECT * FROM cashbook WHERE bankid = '{$bankid}' AND banked = 'no' AND div = '" . USER_DIV . "' ORDER BY date DESC";
    $cashRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank transactions from database.", SELF);
    $tot = 0;
    $totr = 0;
    $totp = 0;
    $recpts = "";
    $paymnts = "";
    while ($cash = pg_fetch_array($cashRslt)) {
        if ($cash['trantype'] == "deposit") {
            $recpts .= "<tr><td>{$cash['date']}</td><td>{$cash['descript']}</td><td align=right>" . CUR . " {$cash['amount']}</td></tr>";
            $totr += $cash['amount'];
        } else {
            $paymnts .= "<tr><td>{$cash['date']}</td><td>{$cash['descript']}</td><td align=right>" . CUR . " {$cash['amount']}</td></tr>";
            $totp += $cash['amount'];
        }
        $tot += $cash['amount'];
    }
    $reconbal = sprint($cbal + ($totr - $totp));
    # Get account name for bank account
    db_connect();
    $sql = "SELECT accname, bankname FROM bankacct WHERE bankid= '{$bankid}' AND div = '" . USER_DIV . "'";
    $bankRslt = db_exec($sql);
    $bank = pg_fetch_array($bankRslt);
    # Get hook account number
    core_connect();
    $sql = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "'";
    $rslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
    # check if link exists
    if (pg_numrows($rslt) < 1) {
        return "<li class=err> ERROR : The bank account that you selected doesn't appear to have an account linked to it.";
    }
    $banklnk = pg_fetch_array($rslt);
    # Get bank balance
    $sql = "SELECT (debit - credit) as bal FROM trial_bal WHERE period='" . PRD_DB . "' AND accid = '{$banklnk['accnum']}' AND div = '" . USER_DIV . "'";
    $brslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
    $bal = pg_fetch_array($brslt);
    $diff = $reconbal - $bal['bal'];
    $derr = "";
    if ($diff != 0) {
        $derr = "<tr><td colspan=2><b class=err>Bank statement and computer balance not balancing by</b></td><td align=right>" . CUR . " {$diff}</td></tr>";
    }
    // Layout
    $update = "<table border=1>\n\t<tr><th colspan=3><h3>Bank Reconciliation Output</h3></th></tr>\n\t<tr><td></td></tr>\n\t<tr><td colspan=3>\n\t\t<table cellpadding='2' cellspacing='0' border=0 bordercolor='#000000' width=100%>\n\t\t\t<tr><td><b>Bank Account : </b>{$bank['accname']}</td><td></td><td align=right><b>Prepared By : </b>" . USER_NAME . "</td></tr>\n\t\t\t<tr><td><b>Closing Balance As per Bank Statement : </b>" . CUR . " {$cbal}</td><td></td></tr>\n\t\t</table>\n\t</td></tr>\n\t<tr><td><br><br></td></tr>\n\t<tr><td colspan=3><b>Plus Outstanding Receipts :</b></td></tr>\n\t<tr><th>Date</th><th>Reference</th><th>Amount</th></tr>\n\t{$recpts}\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2 align=right><b>Sub Total</b></td><td align=right>" . CUR . " {$totr}</td></tr>\n\t<tr><td><br><br></td></tr>\n\t<tr><td colspan=3><b>Less Outstanding Payments :</b></td></tr>\n\t<tr><th>Date</th><th>Reference</th><th>Amount</th></tr>\n\t{$paymnts}\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2 align=right><b>Sub Total</b></td><td align=right>" . CUR . " {$totp}</td></tr>\n\t<tr><td><br><td></tr>\n\t{$derr}\n\t<tr><td><br><td></tr>\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2><b>Reconciled Bank Balance</b></td><td align=right>" . CUR . " {$reconbal}</td></tr>\n\t<tr><td colspan=2><b>Computer Bank Balance</b></td><td align=right>" . CUR . " {$bal['bal']}</td></tr>\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t<tr><td colspan=2><b>Diff</b></td><td align=right>" . CUR . " {$diff}</td></tr>\n\t<tr><td colspan=2><br></td><td align=right>____________</td></tr>\n\t</table>";
    # Send the stream
    include "temp.xls.php";
    return Stream("BankRecon", $update);
}