PHP prdname Beispiele

Programmiersprache: PHP

Methode / Funktion: prdname

Beispiele auf hotexamples.com: 23

PHP prdname - 23 Beispiele gefunden. Dies sind die am besten bewerteten PHP Beispiele für die prdname, die aus Open Source-Projekten extrahiert wurden. Sie können Beispiele bewerten, um die Qualität der Beispiele zu verbessern.

Beispiel #1

Datei anzeigen

Datei: ledger-audit.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($year, "string", 1, 10, "Invalid year.");
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
    if ($accnt == 'slct') {
        if (isset($accids)) {
            foreach ($accids as $key => $accid) {
                $v->isOk($accid, "num", 1, 20, "Invalid Account number.");
            }
        } else {
            $v->isOk("###", "num", 0, 0, "ERROR : Please select at least one account.");
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "<li>";
        }
        return $confirm . slctacc($_POST);
    }
    # Get the ids
    if ($accnt == 'all') {
        $accids = array();
        db_conn($year);
        $sql = "SELECT accid FROM year_balance 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>";
        }
    }
    # Period name
    $prdname = prdname($prd);
    db_conn('core');
    $Sl = "SELECT * FROM year WHERE yrdb='{$year}'";
    $Ri = db_exec($Sl) or errDie("Unable to get data.");
    $yd = pg_fetch_array($Ri);
    $hide = "";
    $trans = "";
    foreach ($accids as $key => $accid) {
        $accRs = get($year, "accname,accid,topacc,accnum", "year_balance", "accid", $accid);
        $acc = pg_fetch_array($accRs);
        # Get balances
        $idRs = get($yd['yrname'] . "_audit", "max(id), min(id)", $prdname . "_ledger", "acc", $accid);
        $id = pg_fetch_array($idRs);
        if ($id['min'] != 0) {
            $id['min'] += 0;
            $id['max'] += 0;
            $balRs = get($yd['yrname'] . "_audit", "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", $prdname . "_ledger", "id", $id['min']);
            $bal = pg_fetch_array($balRs);
            $cbalRs = get($yd['yrname'] . "_audit", "cbalance,dbalance", $prdname . "_ledger", "id", $id['max']);
            $cbal = pg_fetch_array($cbalRs);
        } else {
            // 			//if($prd != PRD_DB){
            // 			//	continue;
            // 			//}
            $balRs = get($year, "credit as cbalance, debit as dbalance", $prdname, "accid", $accid);
            $bal = pg_fetch_array($balRs);
            $cbal['cbalance'] = 0;
            $cbal['dbalance'] = 0;
            //			return "There are no transactions in this period.<p>";
        }
        if ($bal['dbalance'] > $bal['cbalance']) {
            $bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
            $bal['cbalance'] = "";
            $balance = $bal['dbalance'];
            $fl = "DR";
        } elseif ($bal['cbalance'] > $bal['dbalance']) {
            $bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
            $bal['dbalance'] = "";
            $balance = $bal['cbalance'];
            $fl = "CR";
        } else {
            $bal['cbalance'] = "";
            $bal['dbalance'] = "";
            $balance = "0.00";
            $fl = "";
        }
        $balance = sprint($balance);
        $bal['cbalance'] = sprint($bal['cbalance']);
        $bal['dbalance'] = sprint($bal['dbalance']);
        $hide .= "<input type='hidden' name='accids[]' value='{$acc['accid']}'>";
        $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>";
        $trans .= "\n\t\t\t<tr class='bg-even'>\n\t\t\t\t<td colspan=2>&nbsp;</td>\n\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t\t<td>&nbsp;</td>\n\t\t\t</tr>";
        # --> transactio reding comes here <--- #
        $dbal['debit'] = 0;
        $dbal['credit'] = 0;
        $tranRs = get($yd['yrname'] . "_audit", "*", $prdname . "_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 = "DR";
            } else {
                if ($tran['cbalance'] > $tran['dbalance']) {
                    $tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
                    $tran['dbalance'] = "";
                    $cbalance = $tran['cbalance'];
                    $cfl = "CR";
                } 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<tr class='bg-odd'>\n\t\t\t\t\t<td><br></td>\n\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t<td align='right'>{$cbalance} {$cfl}</td>\n\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\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<tr class='bg-even'>\n\t\t\t\t<td colspan=2><br></td>\n\t\t\t\t<td>A/C Total</td>\n\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t<td align='right'></td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>\n\t\t\t<tr><td colspan='8'><br></td></tr>";
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t\t<center>\n\t\t<h3>General Ledger</h3>\n\t\t<form action='../xls/ledger-audit-xls.php' method='POST'>\n\t\t\t<input type='hidden' name='key' value='viewtran'>\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<input type='hidden' name='year' value='{$year}'>\n\t\t\t{$hide}\n\t\t<table " . TMPL_tblDflts . " width='90%'>\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t</tr>\n\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td>{$sp}</td>\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{$trans}\n\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t</table>\n\t\t</form>";
    return $view;
}

Beispiel #2

Datei anzeigen

Datei: supp-ledger-audit.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($fprd, "string", 1, 14, "Invalid from period number.");
    $v->isOk($tprd, "string", 1, 14, "Invalid to period number.");
    $v->isOk($year, "string", 3, 4, "Invalid year.");
    if (isset($accnt)) {
        if ($accnt == 'slct') {
            if (isset($supids)) {
                foreach ($supids as $key => $supid) {
                    $v->isOk($supid, "num", 1, 20, "Invalid Supplier number.");
                }
            } else {
                return "<li class='err'>Please select at least one Creditor.</li>" . slctacc();
            }
        }
    } else {
        $v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $supids = array();
        db_connect();
        $sql = "SELECT supid FROM suppliers WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $supids[] = $ac['supid'];
            }
        } else {
            return "<li calss='err'> There are no suppliers yet in Cubit.</li>";
        }
    }
    $prds = array();
    if ($tprd < $fprd) {
        for ($i = $fprd; $i <= 12; ++$i) {
            $prds[] = $i;
        }
        for ($i = 1; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    } else {
        for ($i = $fprd; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    }
    db_conn('core');
    $Sl = "SELECT yrname FROM year WHERE yrdb='{$year}'";
    $Ri = db_exec($Sl) or errDie("Unable to get data.");
    $yrname = pg_fetch_result($Ri, 0);
    $auditdb = "{$yrname}_audit";
    # Period name
    $hide = "";
    global $MONPRD, $PRDMON;
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $trans = "";
    foreach ($supids as $key => $supid) {
        $supRs = get("cubit", "supname, supno, balance", "suppliers", "supid", $supid);
        $sup = pg_fetch_array($supRs);
        $trans .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>{$sup['supname']}</h3></td>\n\t\t\t</tr>";
        $hide .= "<input type='hidden' name='supids[]' value='{$supid}'>";
        foreach ($prds as $prd) {
            $prdname = prdname($prd);
            $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<th colspan='8'>{$prdname}</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>{$sp}</th>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Reference</th>\n\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t<th>Debit</th>\n\t\t\t\t\t<th>Credit</th>\n\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t<th>Contra Acc</th>\n\t\t\t\t</tr>";
            $idRs = get($auditdb, "min(id)", "{$prdname}_suppledger", "supid", $supid);
            $id = pg_fetch_array($idRs);
            if ($id['min'] != 0) {
                $balRs = get($auditdb, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "{$prdname}_suppledger", "id", $id['min']);
                $bal = pg_fetch_array($balRs);
                $bal['cbalance'] += 0;
                $bal['dbalance'] += 0;
            } else {
                $sql = array();
                for ($i = $MONPRD[$prd] - 1; $i >= 1; --$i) {
                    $pprdname = getMonthName($PRDMON[$i]);
                    $sql[] = "SELECT id,cbalance,dbalance FROM {$auditdb}.{$pprdname}_suppledger WHERE supid='{$supid}'";
                }
                if (count($sql) > 0) {
                    $sql = "SELECT * FROM (" . implode(" UNION ", $sql) . ") AS sl ORDER BY id DESC LIMIT 1";
                    $balRs = db_exec($sql);
                    $bal = pg_fetch_array($balRs);
                }
            }
            if (!isset($bal['dbalance'])) {
                $bal['dbalance'] = "";
            }
            if (!isset($bal['cbalance'])) {
                $bal['cbalance'] = "";
            }
            # Total balance changes
            if ($bal['dbalance'] > $bal['cbalance']) {
                $bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
                $bal['cbalance'] = 0;
            } elseif ($bal['cbalance'] > $bal['dbalance']) {
                $bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
                $bal['dbalance'] = 0;
            } else {
                $bal['cbalance'] = 0;
                $bal['dbalance'] = 0;
            }
            $bal['credit'] = sprint($bal['cbalance']);
            $bal['debit'] = sprint($bal['dbalance']);
            $balance = sprint($bal['cbalance'] - $bal['dbalance']);
            $trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='8'><b>{$sup['supno']} - {$sup['supname']} </b></td>\n\t\t\t\t</tr>";
            // // 			$bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, getYearOfFinMon($prd)));
            // $bbf_date
            $trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2' align='right'>&nbsp;</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'>" . sprint($bal['debit']) . "</td>\n\t\t\t\t\t<td align='right'>" . sprint($bal['credit']) . "</td>\n\t\t\t\t\t<td align='right'>" . sprint($balance) . "</td>\n\t\t\t\t\t<td>&nbsp;</td>\n\t\t\t\t</tr>";
            # --> Transaction reading comes here <--- #
            $dbal['debit'] = 0;
            $dbal['credit'] = 0;
            if ($t == "s") {
                $tranRs = get($auditdb, "*", "{$prdname}_suppledger", "supid", $supid, "ORDER BY id");
            } else {
                $tranRs = get($auditdb, "*", "{$prdname}_suppledger", "supid", $supid, "ORDER BY edate,id");
            }
            while ($tran = pg_fetch_array($tranRs)) {
                $caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
                $cacc = pg_fetch_array($caccRs);
                $tran['debit'] = sprint($tran['debit']);
                $tran['credit'] = sprint($tran['credit']);
                $dbal['debit'] += $tran['debit'];
                $dbal['credit'] += $tran['credit'];
                if ($t == "s") {
                    $cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
                } else {
                    $cbalance = sprint($dbal['credit'] + $bal['credit'] - ($dbal['debit'] + $bal['debit']));
                }
                if ($t == "s") {
                    $tran['edate'] = $tran['sdate'];
                }
                # 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>&nbsp;</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'>" . sprint($tran['debit']) . "</td>\n\t\t\t\t\t\t<td align='right'>" . sprint($tran['credit']) . "</td>\n\t\t\t\t\t\t<td align='right'>" . sprint($cbalance) . "</td>\n\t\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</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'>&nbsp;</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'>&nbsp;</td>\n\t\t\t\t\t<td>&nbsp;</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8'>&nbsp;</td>\n\t\t\t\t</tr>";
        }
    }
    $view = "\n\t\t<center>\n\t\t<form action='../xls/supp-ledger-xls.php' method='POST'>\n\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t<input type='hidden' name='year' value='{$year}'>\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\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<input type='hidden' name='t' value='{$t}'>\n\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t{$hide}\n\t\t<h3>Creditors Ledger</h3>\n\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t{$trans}\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t</tr>\n\t\t</from>\n\t\t</table>\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<th>Quick Links</th>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a target=_blank href='../core/acc-new2.php'>Add account (New Window)</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports.php'>Financials</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports-debtcred.php'>Debtors & Creditors Reports</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\n\t\t\t</tr>\n\t\t</table>";
    return $view;
}

Beispiel #3

Datei anzeigen

Datei: cust-ledger-audit-xls.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($year, "string", 1, 10, "Invalid year.");
    if (isset($accnt)) {
        if ($accnt == 'slct') {
            if (isset($cusnums)) {
                foreach ($cusnums as $key => $cusnum) {
                    $v->isOk($cusnum, "num", 1, 20, "Invalid Customer number.");
                }
            } else {
                return "<li class='err'>Please select at least one Debtor.</li>" . slctacc();
            }
        }
    } else {
        $v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $cusnums = array();
        db_connect();
        $sql = "SELECT cusnum FROM customers WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $cusnums[] = $ac['cusnum'];
            }
        } else {
            return "<li calss='err'> There are no customers yet in Cubit.</li>";
        }
    }
    $prds = array();
    if ($tprd < $fprd) {
        for ($i = $fprd; $i <= 12; ++$i) {
            $prds[] = $i;
        }
        for ($i = 1; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    } else {
        for ($i = $fprd; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    }
    db_conn('core');
    $Sl = "SELECT * FROM year WHERE yrdb='{$year}'";
    $Ri = db_exec($Sl) or errDie("Unable to get data.");
    $yd = pg_fetch_array($Ri);
    $trans = "";
    foreach ($cusnums as $key => $cusnum) {
        $cusRs = get("cubit", "cusname, surname, accno, balance", "customers", "cusnum", $cusnum);
        $cus = pg_fetch_array($cusRs);
        $trans .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>{$cus['surname']}</h3></td>\n\t\t\t</tr>";
        foreach ($prds as $prd) {
            # Period name
            $prdname = prdname($prd);
            $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$sp}</td>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Reference</th>\n\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t<th>Debit</th>\n\t\t\t\t\t<th>Credit</th>\n\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t<th>Contra Acc</th>\n\t\t\t\t</tr>";
            $idRs = get($yd['yrname'] . "_audit", "min(id)", $prdname . "_custledger", "cusnum", $cusnum);
            $id = pg_fetch_array($idRs);
            if ($id['min'] != 0) {
                $balRs = get($yd['yrname'] . "_audit", "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", $prdname . "_custledger", "id", $id['min']);
                $bal = pg_fetch_array($balRs);
                $bal['cbalance'] += 0;
                $bal['dbalance'] += 0;
            } else {
                $balRs = get("cubit", "balance", "customers", "cusnum", $cusnum);
                $bal = pg_fetch_array($balRs);
                $bal['balance'] += 0;
                if ($bal['balance'] > 0) {
                    $bal['dbalance'] = $bal['balance'];
                    $bal['cbalance'] = 0;
                } else {
                    $bal['cbalance'] = $bal['balance'] * -1;
                    $bal['dbalance'] = 0;
                }
                //$bal['dbalance'] += $amount;
            }
            # Total balance changes
            if ($bal['dbalance'] > $bal['cbalance']) {
                $bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
                $bal['cbalance'] = 0;
            } elseif ($bal['cbalance'] > $bal['dbalance']) {
                $bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
                $bal['dbalance'] = 0;
            } else {
                $bal['cbalance'] = 0;
                $bal['dbalance'] = 0;
            }
            $bal['credit'] = sprint($bal['cbalance']);
            $bal['debit'] = sprint($bal['dbalance']);
            $balance = sprint($bal['dbalance'] - $bal['cbalance']);
            $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8'><b>{$cus['accno']} - {$cus['cusname']} {$cus['surname']}</b></td>\n\t\t\t\t</tr>";
            $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='2'><br></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['debit']}</td>\n\t\t\t\t\t<td align='right'>{$bal['credit']}</td>\n\t\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
            # --> Transaction reading comes here <--- #
            $dbal['debit'] = 0;
            $dbal['credit'] = 0;
            $tranRs = get($yd['yrname'] . "_audit", "*", $prdname . "_custledger", "cusnum", $cusnum, "ORDER BY id");
            while ($tran = pg_fetch_array($tranRs)) {
                $caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
                $cacc = pg_fetch_array($caccRs);
                $dbal['debit'] += $tran['debit'];
                $dbal['credit'] += $tran['credit'];
                $balance = sprint($tran['dbalance'] - $tran['cbalance']);
                # 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>\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'>{$balance}</td>\n\t\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</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>\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>";
        }
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t\t<center>\n\t\t<h3>Debtors Ledger</h3>\n\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t{$trans}\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t</tr>\n\t\t</table>";
    include "temp.xls.php";
    Stream("Ledger", $view);
}

Beispiel #4

Datei anzeigen

Datei: emp-ledger-audit.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($year, "string", 1, 10, "Invalid year.");
    if (isset($accnt)) {
        if ($accnt == 'slct') {
            if (isset($emps)) {
                foreach ($emps as $key => $emp) {
                    $v->isOk($emp, "num", 1, 20, "Invalid employee number.");
                }
            } else {
                return "<li class='err'>Please select at least one Emloyee.</li>" . slctacc($_POST);
            }
        }
    } else {
        $v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $supids = array();
        db_connect();
        $sql = "SELECT empnum FROM employees WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $emps[] = $ac['empnum'];
            }
        } else {
            return "<li calss='err'> There are no employees yet in Cubit.";
        }
    }
    # Period name
    $prdname = prdname($prd);
    $hide = "";
    db_conn('core');
    $Sl = "SELECT * FROM year WHERE yrdb='{$year}'";
    $Ri = db_exec($Sl) or errDie("Unable to get data.");
    $yd = pg_fetch_array($Ri);
    $trans = "";
    foreach ($emps as $key => $emp) {
        $supRs = get("cubit", "sname,fnames, balance", "employees", "empnum", $emp);
        $sup = pg_fetch_array($supRs);
        $idRs = get($yd['yrname'] . "_audit", "min(id)", $prdname . "_empledger", "empid", $emp);
        $id = pg_fetch_array($idRs);
        if ($id['min'] != 0) {
            $balRs = get($yd['yrname'] . "_audit", "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", $prdname . "_empledger", "id", $id['min']);
            $bal = pg_fetch_array($balRs);
            $bal['cbalance'] += 0;
            $bal['dbalance'] += 0;
        } else {
            $balRs = get("cubit", "balance", "employees", "empnum", $emp);
            $bal = pg_fetch_array($balRs);
            $bal['balance'] += 0;
            if ($bal['balance'] < 0) {
                $bal['dbalance'] = $bal['balance'] * -1;
                $bal['cbalance'] = 0;
            } else {
                $bal['cbalance'] = $bal['balance'];
                $bal['dbalance'] = 0;
            }
            //$bal['dbalance'] += $amount;
        }
        # Total balance changes
        if ($bal['dbalance'] > $bal['cbalance']) {
            $bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
            $bal['cbalance'] = 0;
        } elseif ($bal['cbalance'] > $bal['dbalance']) {
            $bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
            $bal['dbalance'] = 0;
        } else {
            $bal['cbalance'] = 0;
            $bal['dbalance'] = 0;
        }
        $bal['credit'] = $bal['cbalance'];
        $bal['debit'] = $bal['dbalance'];
        $balance = sprint($bal['cbalance'] - $bal['dbalance']);
        $hide .= "<input type='hidden' name='emps[]' value='{$emp}'>";
        $trans .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='8'><b>{$sup['sname']}, {$sup['fnames']} </b></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t<td align='right'>{$bal['debit']}</td>\n\t\t\t\t<td align='right'>{$bal['credit']}</td>\n\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>";
        # --> Transaction reading comes here <--- #
        $dbal['debit'] = 0;
        $dbal['credit'] = 0;
        $tranRs = get($yd['yrname'] . "_audit", "*", $prdname . "_empledger", "empid", $emp, "ORDER BY id");
        while ($tran = pg_fetch_array($tranRs)) {
            $caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
            $cacc = pg_fetch_array($caccRs);
            $dbal['debit'] += $tran['debit'];
            $dbal['credit'] += $tran['credit'];
            $cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
            # Format date
            $tran['edate'] = explode("-", $tran['edate']);
            $tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
            $trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td><br></td>\n\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t<td>{$tran['ref']}</td>\n\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t<td align='right'>{$cbalance}</td>\n\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\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<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t<td>A/C Total</td>\n\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t<td align='right'></td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>\n\t\t\t<tr><td colspan='8'><br></td></tr>";
    }
    //<tr><td colspan=8 align=center><input type=submit value='Export to Spreadsheet'></td></tr>
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t\t<center>\n\t\t<form action='../xls/supp-ledger-audit-xls.php' method='POST'>\n\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t<input type='hidden' name='year' value='{$year}'>\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{$hide}\n\t\t<h3>Employee Ledger</h3>\n\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t<tr>\n\t\t\t\t<td>{$sp}</td>\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{$trans}\n\t\t</form>\n\t\t</table>";
    return $view;
}

Beispiel #5

Datei anzeigen

Datei: stock-ledger-xls.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    foreach ($_POST as $key => $value) {
        ${$key} = $value;
    }
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
    if ($accnt == 'slct') {
        if (isset($stkids)) {
            foreach ($stkids as $key => $stkid) {
                $v->isOk($stkid, "num", 1, 20, "Invalid Stock code.");
            }
        } else {
            return "<li class=err>ERROR : Please select at least one Stock Item.</li>" . slctacc();
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class=err>" . $e["msg"];
        }
        $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $stkids = array();
        db_connect();
        $sql = "SELECT stkid FROM stock WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $stkids[] = $ac['stkid'];
            }
        } else {
            return "<li calss=err> There are no Stock Items yet in Cubit.";
        }
    }
    # Period name
    $prdname = prdname($prd);
    $hide = "";
    $trans = "";
    foreach ($stkids as $key => $stkid) {
        $stkRs = get("cubit", "*", "stock", "stkid", $stkid);
        $stk = pg_fetch_array($stkRs);
        # Get balances
        $idRs = get($prd, "max(id), min(id)", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid);
        $id = pg_fetch_array($idRs);
        if ($id['min'] != 0) {
            $balRs = get($prd, "qty, (bqty - qty) as bqty, trantype, (balance - csamt) as balance", "stkledger", "id", $id['min']);
            $bal = pg_fetch_array($balRs);
            $cbalRs = get($prd, "balance", "stkledger", "id", $id['max']);
            $cbal = pg_fetch_array($cbalRs);
            /*
            if($bal['trantype'] == 'dt'){
            	$bal['bqty'] =  ($bal['bqty'] + $bal['qty']);
            }else{
            	$bal['bqty'] =  ($bal['bqty'] - $bal['qty']);
            }
            */
        } else {
            if ($prd != PRD_DB) {
                continue;
            }
            $balRs = get("cubit", "csamt as balance, units as bqty", "stock", "stkid", $stkid);
            $bal = pg_fetch_array($balRs);
            $cbal['balance'] = 0;
            $cbal['bqty'] = 0;
        }
        $balance = sprint($bal['balance']);
        $hide .= "<input type=hidden name=stkids[] value='{$stkid}'>";
        $trans .= "<tr><td colspan=5><b>({$stk['stkcod']}) {$stk['stkdes']}</b></td></tr>";
        $trans .= "<tr><td><br></td><td>Balance Brought Forward</td><td align=right>{$bal['bqty']}</td><td align=right>{$balance} </td><td align=right>{$balance} </td></tr>";
        # --> transactio reding comes here <--- #
        $dbal['balance'] = 0;
        $dbal['bqty'] = 0;
        $tranRs = nget($prd, "*", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid . " ORDER BY id ASC");
        while ($tran = pg_fetch_array($tranRs)) {
            $dbal['balance'] += $tran['csamt'];
            $dbal['bqty'] += $tran['qty'];
            # sprinting
            $tran['csamt'] = sprint($tran['csamt']);
            $tran['balance'] = sprint($tran['balance']);
            # Format date
            $tran['edate'] = explode("-", $tran['edate']);
            $tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
            $trans .= "<tr><td>{$tran['edate']}</td><td>{$tran['details']}</td><td>{$tran['qty']}</td><td align=right>{$tran['csamt']}</td><td align=right>{$tran['balance']}</td></tr>";
        }
        $dbal['balance'] = sprint($dbal['balance']);
        $trans .= "<tr><td><br></td><td>Total for period {$prdname} to Date :</td><td align=right>{$dbal['bqty']}</td><td align=right>{$dbal['balance']} </td><td align=right>{$dbal['balance']} </td></tr>";
        $trans .= "<tr><td colspan=5><br></td></tr>";
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t<center>\n\t<h3>Inventory Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=75%>\n\t<tr><th>DATE</th><th>DETAILS</th><th>QTY</th><th>COST AMOUNT</th><th>BALANCE</th></tr>\n\t{$trans}\n\t</table>\n\t<p>";
    include "temp.xls.php";
    Stream("Ledger", $view);
}

Beispiel #6

Datei anzeigen

Datei: cust-ledger-xls.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    foreach ($_POST as $key => $value) {
        ${$key} = $value;
    }
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    if (isset($accnt)) {
        if ($accnt == 'slct') {
            if (isset($cusnums)) {
                foreach ($cusnums as $key => $cusnum) {
                    $v->isOk($cusnum, "num", 1, 20, "Invalid Customer number.");
                }
            } else {
                return "<li class=err>Please select at least one Debtor.</li>" . slctacc();
            }
        }
    } else {
        $v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class=err>" . $e["msg"];
        }
        $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $cusnums = array();
        db_connect();
        $sql = "SELECT cusnum FROM customers WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $cusnums[] = $ac['cusnum'];
            }
        } else {
            return "<li calss=err> There are no customers yet in Cubit.";
        }
    }
    # Period name
    $prdname = prdname($prd);
    $hide = "";
    $trans = "";
    foreach ($cusnums as $key => $cusnum) {
        $cusRs = get("cubit", "cusname, surname, accno, balance", "customers", "cusnum", $cusnum);
        $cus = pg_fetch_array($cusRs);
        $idRs = get($prd, "min(id)", "custledger", "cusnum", $cusnum);
        $id = pg_fetch_array($idRs);
        if ($id['min'] != 0) {
            $balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "custledger", "id", $id['min']);
            $bal = pg_fetch_array($balRs);
            $bal['cbalance'] += 0;
            $bal['dbalance'] += 0;
        } else {
            $balRs = get("cubit", "balance", "customers", "cusnum", $cusnum);
            $bal = pg_fetch_array($balRs);
            $bal['balance'] += 0;
            if ($bal['balance'] > 0) {
                $bal['dbalance'] = $bal['balance'];
                $bal['cbalance'] = 0;
            } else {
                $bal['cbalance'] = $bal['balance'] * -1;
                $bal['dbalance'] = 0;
            }
            //$bal['dbalance'] += $amount;
        }
        # Total balance changes
        if ($bal['dbalance'] > $bal['cbalance']) {
            $bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
            $bal['cbalance'] = 0;
        } elseif ($bal['cbalance'] > $bal['dbalance']) {
            $bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
            $bal['dbalance'] = 0;
        } else {
            $bal['cbalance'] = 0;
            $bal['dbalance'] = 0;
        }
        $bal['credit'] = sprint($bal['cbalance']);
        $bal['debit'] = sprint($bal['dbalance']);
        $balance = sprint($bal['dbalance'] - $bal['cbalance']);
        $hide .= "<input type=hidden name=cusnums[] value='{$cusnum}'>";
        $trans .= "<tr><td colspan=8><b>{$cus['accno']} - {$cus['cusname']} {$cus['surname']}</b></td></tr>";
        $trans .= "<tr><td colspan=2><br></td><td>Br/Forwd</td><td>Brought Forward</td><td align=right>{$bal['debit']}</td><td align=right>{$bal['credit']}</td><td align=right>{$balance}</td><td> </td></tr>";
        # --> Transaction reading comes here <--- #
        $dbal['debit'] = 0;
        $dbal['credit'] = 0;
        if ($t == "s") {
            $tranRs = get($prd, "*", "custledger", "cusnum", $cusnum, "ORDER BY id");
        } else {
            $tranRs = get($prd, "*", "custledger", "cusnum", $cusnum, "ORDER BY edate,id");
        }
        while ($tran = pg_fetch_array($tranRs)) {
            $caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
            $cacc = pg_fetch_array($caccRs);
            $dbal['debit'] += $tran['debit'];
            $dbal['credit'] += $tran['credit'];
            if ($t == "s") {
                $balance = sprint($tran['dbalance'] - $tran['cbalance']);
            } else {
                $balance = sprint($dbal['debit'] + $bal['debit'] - ($dbal['credit'] + $bal['credit']));
            }
            if ($t == "t") {
                $tran['sdate'] = $tran['edate'];
            }
            # Format date
            $tran['sdate'] = explode("-", $tran['sdate']);
            $tran['sdate'] = $tran['sdate'][2] . "-" . $tran['sdate'][1] . "-" . $tran['sdate'][0];
            $trans .= "<tr><td><br></td><td>{$tran['sdate']}</td><td>{$tran['eref']}</td><td>{$tran['descript']}</td><td align=right>" . sprint($tran['debit']) . "</td><td align=right>" . sprint($tran['credit']) . "</td><td align=right>{$balance}</td><td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td></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 .= "<tr><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>";
        $trans .= "<tr><td colspan=8><br></td></tr>";
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t<center>\n\t<h3>Debtors Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=75%>\n\t<tr><td>{$sp}</td><th>Date</th><th>Reference</th><th>Description</th><th>Debit</th><th>Credit</th><th>Balance</th><th>Contra Acc</th></tr>\n\t{$trans}\n\t</table>";
    include "temp.xls.php";
    Stream("Ledger", $view);
}

Beispiel #7

Datei anzeigen

Datei: ledger-ytd.php Projekt: andrecoetzee/accounting-123.com

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>&nbsp;</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>&nbsp;</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;
}

Beispiel #8

Datei anzeigen

Datei: ledger-prd.php Projekt: andrecoetzee/accounting-123.com

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>&nbsp;</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>&nbsp;</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>&nbsp;</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 = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $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'>&nbsp;</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;
}

Beispiel #9

Datei anzeigen

Datei: supp-ledger-xls.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    if (isset($accnt)) {
        if ($accnt == 'slct') {
            if (isset($supids)) {
                foreach ($supids as $key => $supid) {
                    $v->isOk($supid, "num", 1, 20, "Invalid Supplier number.");
                }
            } else {
                return "<li class='err'>Please select at least one Creditor.</li>" . slctacc();
            }
        }
    } else {
        $v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $supids = array();
        db_connect();
        $sql = "SELECT supid FROM suppliers WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $supids[] = $ac['supid'];
            }
        } else {
            return "<li calss='err'> There are no suppliers yet in Cubit.</li>";
        }
    }
    db_conn('core');
    $Sl = "SELECT yrname FROM year WHERE yrdb='{$year}'";
    $Ri = db_exec($Sl) or errDie("Unable to get data.");
    $yrname = pg_fetch_result($Ri, 0);
    $auditdb = "{$yrname}_audit";
    $prds = array();
    if ($tprd < $fprd) {
        for ($i = $fprd; $i <= 12; ++$i) {
            $prds[] = $i;
        }
        for ($i = 1; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    } else {
        for ($i = $fprd; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    }
    # Period name
    $prdname = prdname($prd);
    $trans = "";
    foreach ($supids as $key => $supid) {
        $supRs = get("cubit", "supname, supno, balance", "suppliers", "supid", $supid);
        $sup = pg_fetch_array($supRs);
        $trans .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>{$sup['supname']}</h3></td>\n\t\t\t</tr>";
        foreach ($prds as $prd) {
            $prdname = prdname($prd);
            $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<th colspan='8'>{$prdname}</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>{$sp}</th>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Reference</th>\n\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t<th>Debit</th>\n\t\t\t\t\t<th>Credit</th>\n\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t<th>Contra Acc</th>\n\t\t\t\t</tr>";
            // 			if($id['min'] <> 0){
            // 				$balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "suppledger", "id", $id['min']);
            // 				$bal = pg_fetch_array($balRs);
            // 				$bal['cbalance'] += 0;
            // 				$bal['dbalance'] += 0;
            // 			}else{
            // 				$balRs = get("cubit", "balance", "suppliers", "supid", $supid);
            // 				$bal = pg_fetch_array($balRs);
            // 				$bal['balance']+=0;
            //
            // 				if($bal['balance']<0) {
            // 					$bal['dbalance'] = $bal['balance'];
            // 					$bal['cbalance'] = 0;
            // 				} else {
            // 					$bal['cbalance'] = $bal['balance'];
            // 					$bal['dbalance'] = 0;
            // 				}
            // 				//$bal['dbalance'] += $amount;
            // 			}
            // 			$idRs = get($prd, "min(id)", "suppledger", "supid", $supid);
            // 			$id = pg_fetch_array($idRs);
            $idRs = get($auditdb, "min(id)", "{$prdname}_suppledger", "supid", $supid);
            $id = pg_fetch_array($idRs);
            if ($id['min'] != 0) {
                $balRs = get($auditdb, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "{$prdname}_suppledger", "id", $id['min']);
                $bal = pg_fetch_array($balRs);
                $bal['cbalance'] += 0;
                $bal['dbalance'] += 0;
            } else {
                $sql = array();
                for ($i = $MONPRD[$prd] - 1; $i >= 1; --$i) {
                    $pprdname = getMonthName($PRDMON[$i]);
                    $sql[] = "SELECT id,cbalance,dbalance FROM {$auditdb}.{$pprdname}_suppledger WHERE supid='{$supid}'";
                }
                if (count($sql) > 0) {
                    $sql = "SELECT * FROM (" . implode(" UNION ", $sql) . ") AS sl ORDER BY id DESC LIMIT 1";
                    $balRs = db_exec($sql);
                    $bal = pg_fetch_array($balRs);
                }
            }
            if (!isset($bal['dbalance'])) {
                $bal['dbalance'] = "";
            }
            if (!isset($bal['cbalance'])) {
                $bal['cbalance'] = "";
            }
            # Total balance changes
            if ($bal['dbalance'] > $bal['cbalance']) {
                $bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
                $bal['cbalance'] = 0;
            } elseif ($bal['cbalance'] > $bal['dbalance']) {
                $bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
                $bal['dbalance'] = 0;
            } else {
                $bal['cbalance'] = 0;
                $bal['dbalance'] = 0;
            }
            $bal['credit'] = sprint($bal['cbalance']);
            $bal['debit'] = sprint($bal['dbalance']);
            $balance = sprint($bal['cbalance'] - $bal['dbalance']);
            $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8'><b>{$sup['supno']} - {$sup['supname']} </b></td>\n\t\t\t\t</tr>";
            $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan=2><br></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['debit']}</td>\n\t\t\t\t\t<td align='right'>{$bal['credit']}</td>\n\t\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t\t<td> </td>\n\t\t\t\t</tr>";
            # --> Transaction reading comes here <--- #
            $dbal['debit'] = 0;
            $dbal['credit'] = 0;
            // 			if($t == "s") {
            // 				$tranRs = get($prd, "*", "suppledger", "supid", $supid,"ORDER BY id");
            // 			} else  {
            // 				$tranRs = get($prd, "*", "suppledger", "supid", $supid,"ORDER BY edate,id");
            // 			}
            if ($t == "s") {
                $tranRs = get($auditdb, "*", "{$prdname}_suppledger", "supid", $supid, "ORDER BY id");
            } else {
                $tranRs = get($auditdb, "*", "{$prdname}_suppledger", "supid", $supid, "ORDER BY edate,id");
            }
            while ($tran = pg_fetch_array($tranRs)) {
                $caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
                $cacc = pg_fetch_array($caccRs);
                $tran['debit'] = sprint($tran['debit']);
                $tran['credit'] = sprint($tran['credit']);
                $dbal['debit'] += $tran['debit'];
                $dbal['credit'] += $tran['credit'];
                if ($t == "s") {
                    $cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
                } else {
                    $cbalance = sprint($dbal['credit'] + $bal['credit'] - ($dbal['debit'] + $bal['debit']));
                }
                if ($t == "s") {
                    $tran['edate'] = $tran['sdate'];
                }
                # 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>\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}</td>\n\t\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</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>\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>";
        }
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t\t<center>\n\t\t<h3>Creditors Ledger</h3>\n\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t{$trans}\n\t\t</table>";
    include "temp.xls.php";
    Stream("Ledger", $view);
}

Beispiel #10

Datei anzeigen

Datei: ledger.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    extract($_POST);
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($accnt, "string", 1, 10, "Invalid Accounts Selection.");
    $v->isOk($acc_first, "string", 1, 20, "Invalid accounts display 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();
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    // Was the 'All Accounts' option selected?
    if ($accnt == 'all') {
        $accids = array();
        // Retrieve the main accounts
        db_conn("core");
        $sql = "SELECT * FROM accounts WHERE div='" . USER_DIV . "' AND accnum='000' ORDER BY topacc ASC";
        $macc_rslt = db_exec($sql) or errDie("Unable to retrieve main accounts from Cubit.");
        while ($macc_data = pg_fetch_array($macc_rslt)) {
            // Retrieve sub accounts from Cubit
            $sql = "SELECT * FROM accounts WHERE div='" . USER_DIV . "' AND topacc='{$macc_data['topacc']}' AND accnum!='000' ORDER BY topacc ASC";
            $sacc_rslt = db_exec($sql) or errDie("Unable to retrieve sub accounts from Cubit.");
            // List the main accounts without any sub accounts
            if (!pg_num_rows($sacc_rslt)) {
                $accids[] = $macc_data["accid"];
                // List the sub accounts
            } else {
                while ($sacc_data = pg_fetch_array($sacc_rslt)) {
                    $accids[] = $sacc_data["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 . "' AND month='{$prd}'";
            $rslt = db_exec($sql) or errDie("Error fetching active account list.");
            while ($macc_data = pg_fetch_array($rslt)) {
                $accids[] = $macc_data["accid"];
            }
        }
    }
    # Period name
    $prdname = prdname($prd);
    $trans = "";
    $hide = "";
    $i = 0;
    $curr_topacc = "";
    foreach ($accids as $key => $accid) {
        $accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
        $acc = pg_fetch_array($accRs);
        # 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 {
            db_conn("core");
            $balSql = "SELECT credit as cbalance, debit as dbalance FROM trial_bal WHERE accid='{$acc['accid']}' AND period='{$prd}'";
            $balRs = db_exec($balSql) or errDie("Error reading trial balance.");
            $bal = pg_fetch_array($balRs);
            $cbal['cbalance'] = 0;
            $cbal['dbalance'] = 0;
        }
        if ($bal['dbalance'] > $bal['cbalance']) {
            $bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
            $bal['cbalance'] = "";
            $balance = $bal['dbalance'];
            $fl = "DR";
        } elseif ($bal['cbalance'] > $bal['dbalance']) {
            $bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
            $bal['dbalance'] = "";
            $balance = $bal['cbalance'];
            $fl = "CR";
        } else {
            $bal['cbalance'] = "";
            $bal['dbalance'] = "";
            $balance = "0.00";
            $fl = "";
        }
        $balance = sprint($balance);
        $bal['cbalance'] = sprint($bal['cbalance']);
        $bal['dbalance'] = sprint($bal['dbalance']);
        if ($acc_first == "accnum") {
            $account_name = "{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}";
        } else {
            $account_name = "{$acc['accname']} - {$acc['topacc']}/{$acc['accnum']}";
        }
        $hide .= "<input type='hidden' name='accids[]' value='{$acc['accid']}'>";
        $heading = "";
        if ($acc["accnum"] != "000") {
            if (!$i && $acc["topacc"] != $curr_topacc) {
                db_conn("core");
                $sql = "SELECT * FROM accounts WHERE div='" . USER_DIV . "' AND topacc='{$acc['topacc']}' AND accnum='000'";
                $hacc_rslt = db_exec($sql) or errDie("Unable to retrieve main account from Cubit.");
                $hacc_data = pg_fetch_array($hacc_rslt);
                if ($acc_first == "accnum") {
                    $heading_name = "{$hacc_data['topacc']}/{$hacc_data['accnum']} - {$hacc_data['accname']}";
                } else {
                    $heading_name = "{$hacc_data['accname']} - {$hacc_data['topacc']}/{$hacc_data['accnum']}";
                }
                $heading = "<tr><th colspan='8' align='left'>{$heading_name}</th></tr>";
                $curr_topacc = $acc["topacc"];
            }
        } elseif ($acc["topacc"] == $curr_topacc) {
            ++$i;
        }
        $trans .= "{$heading}\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='8'><b>{$account_name}</b></td>\n\t\t</tr>";
        // make the date of the last day of the previous prd
        $bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, getYearOfFinMon($prd)));
        $trans .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t<td>Br/Forwd</td>\n\t\t\t<td>Brought Forward</td>\n\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t<td> </td>\n\t\t</tr>";
        # --> transactio reding comes here <--- #
        $dbal['debit'] = 0;
        $dbal['credit'] = 0;
        if ($t == "s") {
            $tranRs = get($prd, "*", "ledger", "acc", $accid);
        } else {
            $tranRs = get($prd, "*", "ledger", "acc", $accid, "ORDER BY edate,id");
        }
        while ($tran = pg_fetch_array($tranRs)) {
            $dbal['debit'] += $tran['debit'];
            $dbal['credit'] += $tran['credit'];
            if ($t == "t") {
                $tran['dbalance'] = $dbal['debit'] + $bal['dbalance'];
                $tran['cbalance'] = $dbal['credit'] + $bal['cbalance'];
            }
            # Current(Running) balance
            if ($tran['dbalance'] > $tran['cbalance']) {
                $tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
                $tran['cbalance'] = "";
                $cbalance = $tran['dbalance'];
                $cfl = "DR";
            } elseif ($tran['cbalance'] > $tran['dbalance']) {
                $tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
                $tran['dbalance'] = "";
                $cbalance = $tran['cbalance'];
                $cfl = "CR";
            } else {
                $tran['cbalance'] = "";
                $tran['dbalance'] = "";
                $cbalance = "0.00";
                $cfl = "";
            }
            if ($t == "s") {
                $tran['edate'] = $tran['sdate'];
            }
            # 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']);
            if ($tran["debit"] != 0) {
                $trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2' nowrap>{$tran['edate']}</td>\n\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t<td align='right' nowrap>{$tran['debit']}</td>\n\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t<td align='right' nowrap>{$cbalance} {$cfl}</td>\n\t\t\t\t\t<td align='right'>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t</tr>";
            } elseif ($tran["credit"] != 0) {
                $trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2' nowrap>{$tran['edate']}</td>\n\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t<td align='right' nowrap>{$tran['credit']}</td>\n\t\t\t\t\t<td align='right' nowrap>{$cbalance} {$cfl}</td>\n\t\t\t\t\t<td align='right'>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\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<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='2'><br></td>\n\t\t\t<td>A/C Total</td>\n\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t<td align='right' nowrap>{$dbal['debit']}</td>\n\t\t\t<td align='right' nowrap>{$dbal['credit']}</td>\n\t\t\t<td align='right' nowrap></td>\n\t\t\t<td> </td>\n\t\t</tr>";
        $trans .= "<tr><td colspan='8'><br></td></tr>";
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t\t\t\t<center>\n\t\t\t\t<h3>General Ledger</h3>\n\t\t\t\t<form action='../xls/ledger-xls.php' method='POST'>\n\t\t\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t\t\t<input type='hidden' name='t' value='{$t}'>\n\t\t\t\t\t<input type='hidden' name=accnt value='{$accnt}'>\n\t\t\t\t\t{$hide}\n\t\t\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th colspan='2'>Date</th>\n\t\t\t\t\t\t<th>Reference</th>\n\t\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t\t<th width='10%'>Debit</th>\n\t\t\t\t\t\t<th width='10%'>Credit</th>\n\t\t\t\t\t\t<th width='10%'>Balance</th>\n\t\t\t\t\t\t<th>Contra Acc</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t{$trans}\n\t\t\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t\t</tr>\n\t\t\t\t<table>\n\t\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;
}

Beispiel #11

Datei anzeigen

Datei: cust-ledger.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST, $pure = false)
{
    # Get vars
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($fprd, "string", 1, 14, "Invalid from period number.");
    $v->isOk($tprd, "string", 1, 14, "Invalid to period number.");
    if (isset($accnt)) {
        if ($accnt == 'slct') {
            if (isset($cusnums)) {
                foreach ($cusnums as $key => $cusnum) {
                    $v->isOk($cusnum, "num", 1, 20, "Invalid Customer number.");
                }
            } else {
                return "<li class='err'>Please select at least one Debtor.</li>" . slctacc();
            }
        }
    } else {
        $v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $cusnums = array();
        db_connect();
        $sql = "SELECT cusnum FROM customers WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $cusnums[] = $ac['cusnum'];
            }
        } else {
            return "<li calss='err'> There are no customers yet in Cubit.</li>";
        }
    }
    $prds = array();
    if ($tprd < $fprd) {
        for ($i = $fprd; $i <= 12; ++$i) {
            $prds[] = $i;
        }
        for ($i = 1; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    } else {
        for ($i = $fprd; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    }
    # Period name
    $hide = "";
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $trans = "";
    foreach ($cusnums as $key => $cusnum) {
        $cusRs = get("cubit", "cusname, surname, accno, balance", "customers", "cusnum", $cusnum);
        $cus = pg_fetch_array($cusRs);
        $trans .= "\n\t\t<tr>\n\t\t\t<td colspan='8' align='center'><h3>{$cus['surname']}</h3></td>\n\t\t</tr>";
        $hide .= "<input type='hidden' name='cusnums[]' value='{$cusnum}'>";
        foreach ($prds as $prd) {
            $prdname = prdname($prd);
            $trans .= "\n\t\t\t<tr>\n\t\t\t\t<th colspan='8'>{$prdname}</th>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<th>{$sp}</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>";
            $idRs = get($prd, "min(id)", "custledger", "cusnum", $cusnum);
            $id = pg_fetch_array($idRs);
            if ($id['min'] != 0) {
                $balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "custledger", "id", $id['min']);
                $bal = pg_fetch_array($balRs);
                $bal['cbalance'] += 0;
                $bal['dbalance'] += 0;
            } else {
                $balRs = get("cubit", "balance", "customers", "cusnum", $cusnum);
                $bal = pg_fetch_array($balRs);
                $bal['balance'] += 0;
                if ($bal['balance'] > 0) {
                    $bal['dbalance'] = $bal['balance'];
                    $bal['cbalance'] = 0;
                } else {
                    $bal['cbalance'] = $bal['balance'] * -1;
                    $bal['dbalance'] = 0;
                }
                //$bal['dbalance'] += $amount;
            }
            # Total balance changes
            if ($bal['dbalance'] > $bal['cbalance']) {
                $bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
                $bal['cbalance'] = 0;
            } else {
                if ($bal['cbalance'] > $bal['dbalance']) {
                    $bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
                    $bal['dbalance'] = 0;
                } else {
                    $bal['cbalance'] = 0;
                    $bal['dbalance'] = 0;
                }
            }
            $bal['credit'] = sprint($bal['cbalance']);
            $bal['debit'] = sprint($bal['dbalance']);
            $balance = sprint($bal['dbalance'] - $bal['cbalance']);
            $trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='8'><b>{$cus['accno']} - {$cus['cusname']} {$cus['surname']}</b></td>\n\t\t\t\t\t\t\t</tr>";
            $bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, getYearOfFinMon($prd)));
            $trans .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t\t\t\t<td align='right'>{$bal['debit']}</td>\n\t\t\t\t\t\t\t<td align='right'>{$bal['credit']}</td>\n\t\t\t\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t\t\t\t<td>&nbsp;</td>\n\t\t\t\t\t\t</tr>";
            # --> Transaction reading comes here <--- #
            $dbal['debit'] = 0;
            $dbal['credit'] = 0;
            if ($t == "s") {
                $tranRs = get($prd, "*", "custledger", "cusnum", $cusnum, "ORDER BY id");
            } else {
                $tranRs = get($prd, "*", "custledger", "cusnum", $cusnum, "ORDER BY edate,id");
            }
            while ($tran = pg_fetch_array($tranRs)) {
                if ($tran["descript"] == "BBF" && $tran["debit"] == "0" && $tran["credit"] == "0") {
                    continue;
                }
                $caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
                $cacc = pg_fetch_array($caccRs);
                $dbal['debit'] += $tran['debit'];
                $dbal['credit'] += $tran['credit'];
                if ($t == "s") {
                    $balance = sprint($tran['dbalance'] - $tran['cbalance']);
                } else {
                    $balance = sprint($dbal['debit'] + $bal['debit'] - ($dbal['credit'] + $bal['credit']));
                }
                if ($t == "t") {
                    $tran['sdate'] = $tran['edate'];
                }
                # Format date
                $tran['sdate'] = explode("-", $tran['sdate']);
                $tran['sdate'] = $tran['sdate'][2] . "-" . $tran['sdate'][1] . "-" . $tran['sdate'][0];
                $trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>&nbsp;</td>\n\t\t\t\t\t<td>{$tran['sdate']}</td>\n\t\t\t\t\t<td>{$tran['eref']}</td>\n\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t<td align='right'>" . sprint($tran['debit']) . "</td>\n\t\t\t\t\t<td align='right'>" . sprint($tran['credit']) . "</td>\n\t\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\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<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='2'>&nbsp;</td>\n\t\t\t\t<td>A/C Total</td>\n\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t<td align='right'>&nbsp;</td>\n\t\t\t\t<td>&nbsp;</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='8'>&nbsp;</td>\n\t\t\t</tr>";
        }
    }
    $view = "";
    if (!$pure) {
        $view .= "\n\t\t<center>\n\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t<input type='hidden' name='key' value='excel'>\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t<input type='hidden' name='t' value='{$t}'>\n\t\t\t{$hide}\n\t\t\t<h3>Debtors Ledger</h3>";
    }
    $view .= "\n\t<table " . TMPL_tblDflts . " width='75%'>\n\t{$trans}";
    if (!$pure) {
        $view .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</from>";
    }
    $view .= "\n\t</table>";
    if (!$pure) {
        $view .= "\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t" . TBL_BR . "\n\t\t\t<tr>\n\t\t\t\t<th>Quick Links</th>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a target=_blank href='../core/acc-new2.php'>Add account (New Window)</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports.php'>Financials</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='index-reports-debtcred.php'>Debtors & Creditors Reports</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\n\t\t\t</tr>\n\t\t</table>";
    }
    return $view;
}

Beispiel #12

Datei anzeigen

Datei: ledger-xls.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    foreach ($_POST as $key => $value) {
        ${$key} = $value;
    }
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($accnt, "string", 1, 9, "Invalid Accounts Selection.");
    if ($accnt == 'slct') {
        if (isset($accids)) {
            foreach ($accids as $key => $accid) {
                $v->isOk($accid, "num", 1, 20, "Invalid Account number.");
            }
        } else {
            $v->isOk("###", "num", 0, 0, "ERROR : Please select at least one account.");
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class=err>" . $e["msg"];
        }
        $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    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.";
        }
    }
    # Period name
    $prdname = prdname($prd);
    $trans = "";
    foreach ($accids as $key => $accid) {
        $accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
        $acc = pg_fetch_array($accRs);
        # 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 ($prd != PRD_DB) {
                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;
        }
        if ($bal['dbalance'] > $bal['cbalance']) {
            $bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
            $bal['cbalance'] = "";
            $balance = $bal['dbalance'];
            $fl = "DR";
        } elseif ($bal['cbalance'] > $bal['dbalance']) {
            $bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
            $bal['dbalance'] = "";
            $balance = $bal['cbalance'];
            $fl = "CR";
        } else {
            $bal['cbalance'] = "";
            $bal['dbalance'] = "";
            $balance = "0.00";
            $fl = "";
        }
        $balance = sprint($balance);
        $bal['cbalance'] = sprint($bal['cbalance']);
        $bal['dbalance'] = sprint($bal['dbalance']);
        global $MONPRD, $PRDMON;
        // 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<tr>\n\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t<td>Br/Forwd</td>\n\t\t\t<td>Brought Forward</td>\n\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t<td>&nbsp;</td>\n\t\t</tr>";
        # --> transactio reding comes here <--- #
        $dbal['debit'] = 0;
        $dbal['credit'] = 0;
        if ($t == "s") {
            $tranRs = get($prd, "*", "ledger", "acc", $accid);
        } else {
            $tranRs = get($prd, "*", "ledger", "acc", $accid, "ORDER BY edate,id");
        }
        while ($tran = pg_fetch_array($tranRs)) {
            $dbal['debit'] += $tran['debit'];
            $dbal['credit'] += $tran['credit'];
            if ($t == "t") {
                $tran['dbalance'] = $dbal['debit'] + $bal['dbalance'];
                $tran['cbalance'] = $dbal['credit'] + $bal['cbalance'];
            }
            # Current(Running) balance
            if ($tran['dbalance'] > $tran['cbalance']) {
                $tran['dbalance'] = sprint($tran['dbalance'] - $tran['cbalance']);
                $tran['cbalance'] = "";
                $cbalance = $tran['dbalance'];
                $cfl = "DR";
            } elseif ($tran['cbalance'] > $tran['dbalance']) {
                $tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
                $tran['dbalance'] = "";
                $cbalance = $tran['cbalance'];
                $cfl = "CR";
            } else {
                $tran['cbalance'] = "";
                $tran['dbalance'] = "";
                $cbalance = "0.00";
                $cfl = "";
            }
            if ($t == "s") {
                $tran['edate'] = $tran['sdate'];
            }
            # 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>\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'] = "";
            $dbal['debit'] = "0.00";
        }
        $trans .= "\n\t\t\t\t<tr>\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>";
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $OUTPUT = "\n\t\t\t<center>\n\t\t\t<h3>General Ledger</h3>\n\t\t\t<table " . TMPL_tblDflts . " width='90%'>\n\t\t\t\t<tr>\n\t\t\t\t\t<td width='50%' align='left' colspan='4'>" . COMP_NAME . "</td>\n\t\t\t\t\t<td width='50%' align='right' colspan='4'>" . date("Y-m-d") . "</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$sp}</td>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Reference</th>\n\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t<th>Debit</th>\n\t\t\t\t\t<th>Credit</th>\n\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t<th>Contra Acc</th>\n\t\t\t\t</tr>\n\t\t\t\t{$trans}\n\t\t\t<table>";
    require_lib("xls");
    StreamXLS("GeneralLedger" . date("dmY"), $OUTPUT);
    # return $view;
}

Beispiel #13

Datei anzeigen

Datei: emp-ledger-xls.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($prd_f, "num", 1, 2, "Invalid From Period.");
    $v->isOk($prd_t, "num", 1, 2, "Invalid To Period.");
    if (isset($accnt)) {
        if ($accnt == 'slct') {
            if (isset($supids)) {
                foreach ($supids as $key => $supid) {
                    $v->isOk($supid, "num", 1, 20, "Invalid Supplier number.");
                }
            } else {
                return "<li class=err>Please select at least one Creditor.</li>" . slctacc();
            }
        }
    } else {
        $v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class=err>" . $e["msg"];
        }
        $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $supids = array();
        db_connect();
        $sql = "SELECT empnum FROM employees WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $supids[] = $ac['empnum'];
            }
        } else {
            return "<li calss=err> There are no employees yet in Cubit.";
        }
    }
    //	# Period name
    //	$prdname = prdname($prd);
    //
    //	$trans = "";
    //	foreach($supids as $key => $supid){
    //		$supRs = get("cubit", "empnum,sname,fnames, balance", "employees", "empnum", $supid);
    //		$sup = pg_fetch_array($supRs);
    //
    //		$idRs = get($prd, "min(id)", "empledger", "empid", $supid);
    //		$id = pg_fetch_array($idRs);
    //
    //		if($id['min'] <> 0){
    //			$balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "empledger", "id", $id['min']);
    //			$bal = pg_fetch_array($balRs);
    //			$bal['cbalance'] += 0;
    //			$bal['dbalance'] += 0;
    //		}else{
    //			$balRs = get("cubit", "balance", "employees", "empnum", $supid);
    //			$bal = pg_fetch_array($balRs);
    //			$bal['balance']+=0;
    //
    //			if($bal['balance']<0) {
    //				$bal['dbalance'] = $bal['balance'];
    //				$bal['cbalance'] = 0;
    //			} else {
    //				$bal['cbalance'] = $bal['balance'];
    //				$bal['dbalance'] = 0;
    //			}
    //			//$bal['dbalance'] += $amount;
    //		}
    //
    //		# Total balance changes
    //		if($bal['dbalance'] > $bal['cbalance']){
    //			$bal['dbalance'] = ($bal['dbalance'] - $bal['cbalance']);
    //			$bal['cbalance'] = 0;
    //		}elseif($bal['cbalance'] > $bal['dbalance']){
    //			$bal['cbalance'] = ($bal['cbalance'] - $bal['dbalance']);
    //			$bal['dbalance'] = 0;
    //		}else{
    //			$bal['cbalance'] = 0;
    //			$bal['dbalance'] = 0;
    //		}
    //
    //		$bal['credit'] = sprint($bal['cbalance']);
    //		$bal['debit'] = sprint($bal['dbalance']);
    //
    //		$balance=sprint($bal['cbalance']-$bal['dbalance']);
    //
    //		$trans .= "<tr><td colspan=8><b>$sup[sname], $sup[fnames]</b></td></tr>";
    //		$trans .= "<tr><td colspan=2><br></td><td>Br/Forwd</td><td>Brought Forward</td><td align=right>$bal[debit]</td><td align=right>$bal[credit]</td><td align=right>$balance</td><td> </td></tr>";
    //
    //		# --> Transaction reading comes here <--- #
    //		$dbal['debit'] = 0;
    //		$dbal['credit'] = 0;
    //
    //		if($t=="s") {
    //			$tranRs = get($prd, "*", "empledger", "empid", $supid,"ORDER BY id");
    //		} else  {
    //			$tranRs = get($prd, "*", "empledger", "empid", $supid,"ORDER BY edate,id");
    //		}
    //		while($tran = pg_fetch_array($tranRs)){
    //			$caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
    //			$cacc = pg_fetch_array($caccRs);
    //
    //			$tran['debit']=sprint($tran['debit']);
    //			$tran['credit']=sprint($tran['credit']);
    //
    //			$dbal['debit'] += $tran['debit'];
    //			$dbal['credit'] += $tran['credit'];
    //
    //			if($t=="s") {
    //
    //				$cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
    //
    //			} else {
    //
    //				$cbalance = sprint(($dbal['credit']+$bal['credit']) - ($dbal['debit']+$bal['debit']));
    //
    //			}
    //
    //			if($t=="s") {
    //				$tran['edate']=$tran['sdate'];
    //			}
    //
    //			# Format date
    //			$tran['edate'] = explode("-", $tran['edate']);
    //			$tran['edate'] = $tran['edate'][2]."-".$tran['edate'][1]."-".$tran['edate'][0];
    //
    //			$trans .= "<tr><td><br></td><td>$tran[edate]</td><td>$tran[ref]</td><td>$tran[des]</td><td align=right>$tran[debit]</td><td align=right>$tran[credit]</td><td align=right>$cbalance</td><td>$cacc[topacc]/$cacc[accnum] - $cacc[accname]</td></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 .= "<tr><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>";
    //		$trans .= "<tr><td colspan=8><br></td></tr>";
    //	}
    $prds = array();
    if ($prd_f > $prd_t) {
        for ($i = $prd_f; $i <= 12; ++$i) {
            $prds[] = $i;
        }
        for ($i = 1; $i <= $prd_t; ++$i) {
            $prds[] = $i;
        }
    } else {
        for ($i = $prd_f; $i <= $prd_t; ++$i) {
            $prds[] = $i;
        }
    }
    # Period name
    $hide = "";
    $trans = "";
    foreach ($supids as $key => $supid) {
        $supRs = get("cubit", "empnum,sname,fnames, balance", "employees", "empnum", $supid);
        $sup = pg_fetch_array($supRs);
        $trans .= "\n\t\t<tr>\n\t\t\t<td align='center' colspan='8'><h2>{$sup['sname']}, {$sup['fnames']}</h2></td>\n\t\t</tr>";
        foreach ($prds as $prd) {
            $idRs = get($prd, "min(id)", "empledger", "empid", $supid);
            $id = pg_fetch_array($idRs);
            if ($id['min'] != 0) {
                $balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "empledger", "id", $id['min']);
                $bal = pg_fetch_array($balRs);
                $bal['cbalance'] += 0;
                $bal['dbalance'] += 0;
            } else {
                $sql = array();
                for ($i = $MONPRD[$prd] - 1; $i >= 1; --$i) {
                    $pprdname = getMonthName($PRDMON[$i]);
                    $sql[] = "SELECT id,cbalance,dbalance\n\t\t\t\t\t\t\tFROM \"{$i}\".empledger\n\t\t\t\t\t\t\tWHERE empid='{$supid}'";
                }
                if (count($sql) > 0) {
                    $sql = "SELECT * FROM (" . implode(" UNION ", $sql) . ") AS sl\n\t\t\t\t\t\t\tORDER BY id DESC\n\t\t\t\t\t\t\tLIMIT 1";
                    $balRs = db_exec($sql);
                    $bal = pg_fetch_array($balRs);
                }
            }
            # Total balance changes
            if ($bal['dbalance'] > $bal['cbalance']) {
                $bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
                $bal['cbalance'] = 0;
            } elseif ($bal['cbalance'] > $bal['dbalance']) {
                $bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
                $bal['dbalance'] = 0;
            } else {
                $bal['cbalance'] = 0;
                $bal['dbalance'] = 0;
            }
            $bal['credit'] = sprint($bal['cbalance']);
            $bal['debit'] = sprint($bal['dbalance']);
            $balance = sprint($bal['cbalance'] - $bal['dbalance']);
            // make the date of the last day of the previous prd
            $bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, getYearOfEmpMon($prd - 1)));
            if (!isset($sp)) {
                $sp = "";
            }
            $c = 0;
            $hide .= "<input type=hidden name=supids[] value='{$supid}'>";
            $trans .= "\n\t\t\t<tr>\n\t\t\t\t<th colspan='8'>" . getMonthName($prd) . " " . getYearOfEmpMon($prd) . "</th>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<th>{$sp}</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>\n\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t<td align=right>{$bal['debit']}</td>\n\t\t\t\t<td align=right>{$bal['credit']}</td>\n\t\t\t\t<td align=right>{$balance}</td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>";
            # --> Transaction reading comes here <--- #
            $dbal['debit'] = 0;
            $dbal['credit'] = 0;
            if ($t == "s") {
                $tranRs = get($prd, "*", "empledger", "empid", $supid, "ORDER BY id");
            } else {
                $tranRs = get($prd, "*", "empledger", "empid", $supid, "ORDER BY edate,id");
            }
            while ($tran = pg_fetch_array($tranRs)) {
                $caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
                $cacc = pg_fetch_array($caccRs);
                $tran['debit'] = sprint($tran['debit']);
                $tran['credit'] = sprint($tran['credit']);
                $dbal['debit'] += $tran['debit'];
                $dbal['credit'] += $tran['credit'];
                if ($t == "s") {
                    $cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
                } else {
                    $cbalance = sprint($dbal['credit'] + $bal['credit'] - ($dbal['debit'] + $bal['debit']));
                }
                if ($t == "s") {
                    $tran['edate'] = $tran['sdate'];
                }
                # Format date
                $tran['edate'] = explode("-", $tran['edate']);
                $tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
                $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>&nbsp;</td>\n\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t<td>{$tran['ref']}</td>\n\t\t\t\t\t<td>{$tran['des']}</td>\n\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t<td align='right'>{$cbalance}</td>\n\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\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<tr>\n\t\t\t\t<td colspan='2'>&nbsp;</td>\n\t\t\t\t<td>A/C Total</td>\n\t\t\t\t<td>Total for period " . prdname($prd) . ":</td>\n\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t<td align='right'>&nbsp;</td>\n\t\t\t\t<td>&nbsp;</td>\n\t\t\t</tr>\n\t\t\t" . TBL_BR;
        }
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t\t\t\t<center>\n\t\t\t\t<h3>Employee Ledger</h3>\n\t\t\t\t<table " . TMPL_tblDflts . " width=75%>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td>{$sp}</td>\n\t\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t\t<th>Reference</th>\n\t\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t\t<th>Debit</th>\n\t\t\t\t\t\t<th>Credit</th>\n\t\t\t\t\t\t<th>Balance</th>\n\t\t\t\t\t\t<th>Contra Acc</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t{$trans}\n\t\t\t\t</table>";
    include "temp.xls.php";
    Stream("Ledger", $view);
}

Beispiel #14

Datei anzeigen

Datei: ledger-audit-prd.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
    $v->isOk($year, "string", 1, 10, "Invalid year.");
    if ($accnt == 'slct') {
        if (isset($accids)) {
            foreach ($accids as $key => $accid) {
                $v->isOk($accid, "num", 1, 20, "Invalid Account number.");
            }
        } else {
            $v->isOk("###", "num", 0, 0, "ERROR : Please select at least one account.");
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $accids = array();
        db_conn($year);
        $sql = "SELECT accid FROM year_balance 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 class='err'> There are no accounts yet in Cubit.</li>";
        }
    }
    $cp = $fprd;
    $fs = 0;
    if ($cp == $tprd + 1) {
        $f = true;
    } else {
        $f = false;
    }
    db_conn('core');
    $Sl = "SELECT * FROM year WHERE yrdb='{$year}'";
    $Ri = db_exec($Sl) or errDie("Unable to get data.");
    $yd = pg_fetch_array($Ri);
    # 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 = "";
    //while($cls = pg_fetch_array($clsRs)){
    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<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='8' align='center'><h3>{$prdname}</h3></td>\n\t\t\t</tr>";
        $hide = "";
        if (isset($t)) {
            unset($t);
        }
        foreach ($accids as $key => $accid) {
            $accRs = get($year, "accname, accid, topacc, accnum", "year_balance", "accid", $accid);
            $acc = pg_fetch_array($accRs);
            $test = $prdname . "_ledger";
            db_conn($yd['yrname'] . "_audit");
            $Sl = "SELECT * FROM {$test}";
            if (!($Ri = @db_exec($Sl))) {
                continue;
            }
            # Get balances
            $idRs = get($yd['yrname'] . "_audit", "max(id), min(id)", $prdname . "_ledger", "acc", $accid);
            $id = pg_fetch_array($idRs);
            if ($id['min'] != 0) {
                $balRs = get($yd['yrname'] . "_audit", "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", $prdname . "_ledger", "id", $id['min']);
                $bal = pg_fetch_array($balRs);
                $cbalRs = get($yd['yrname'] . "_audit", "cbalance,dbalance", $prdname . "_ledger", "id", $id['max']);
                $cbal = pg_fetch_array($cbalRs);
            } else {
                $balRs = get($year, "credit as cbalance, debit as dbalance", $prdname, "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);
            $hide .= "<input type='hidden' name='accids[]' value='{$acc['accid']}'>";
            $trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t\t</tr>\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>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($yd['yrname'] . "_audit", "*", $prdname . "_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><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'] = "";
                $dbal['debit'] = "0.00";
            }
            $trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2'>&nbsp;</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>&nbsp;</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr><td colspan='8'><br></td></tr>";
        }
        if (!isset($t)) {
            $trans .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='8' align='center'><li> There are no transactions in this period.</td>\n\t\t\t\t</tr>";
        }
    }
    $fprdname = prdname($fprd);
    $tprdname = prdname($tprd);
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t\t<center>\n\t\t<form action='../xls/ledger-audit-prd-xls.php' method='POST'>\n\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t<input type='hidden' name='year' value='{$year}'>\n\t\t\t{$hide}\n\t\t\t<h3>Period Range General Ledger</h3>\n\t\t\t<h4>{$fprdname} - {$tprdname}</h4>\n\t\t<table " . TMPL_tblDflts . " width='90%'>\n\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<th>{$sp}</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{$trans}\n\t\t\t<tr><td colspan='8'><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><input type='submit' value='Export to Spreadsheet'></td>\n\t\t\t</tr>\n\t\t<table>\n\t\t</form>";
    return $view;
}

Beispiel #15

Datei anzeigen

Datei: ledger-audit-xls.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    foreach ($_POST as $key => $value) {
        ${$key} = $value;
    }
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($year, "string", 1, 10, "Invalid year.");
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
    if ($accnt == 'slct') {
        if (isset($accids)) {
            foreach ($accids as $key => $accid) {
                $v->isOk($accid, "num", 1, 20, "Invalid Account number.");
            }
        } else {
            $v->isOk("###", "num", 0, 0, "ERROR : Please select at least one account.");
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class=err>" . $e["msg"];
        }
        $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $accids = array();
        db_conn($year);
        $sql = "SELECT accid FROM year_balance 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.";
        }
    }
    # Period name
    $prdname = prdname($prd);
    db_conn('core');
    $Sl = "SELECT * FROM year WHERE yrdb='{$year}'";
    $Ri = db_exec($Sl) or errDie("Unable to get data.");
    $yd = pg_fetch_array($Ri);
    $hide = "";
    $trans = "";
    foreach ($accids as $key => $accid) {
        $accRs = get($year, "accname,accid,topacc,accnum", "year_balance", "accid", $accid);
        $acc = pg_fetch_array($accRs);
        $prdname = strtolower($prdname);
        # Get balances
        $idRs = get($yd['yrname'] . "_audit", "max(id), min(id)", $prdname . "_ledger", "acc", $accid);
        $id = pg_fetch_array($idRs);
        if ($id['min'] != 0) {
            $balRs = get($yd['yrname'] . "_audit", "(cbalance-credit) as cbalance,(dbalance-debit) as dbalance", $prdname . "_ledger", "id", $id['min']);
            $bal = pg_fetch_array($balRs);
            $cbalRs = get($yd['yrname'] . "_audit", "cbalance,dbalance", $prdname . "_ledger", "id", $id['max']);
            $cbal = pg_fetch_array($cbalRs);
        } else {
            //if($prd != PRD_DB){
            //	continue;
            //}
            $balRs = get($year, "credit as cbalance, debit as dbalance", $prdname, "accid", $accid);
            $bal = pg_fetch_array($balRs);
            $cbal['cbalance'] = 0;
            $cbal['dbalance'] = 0;
            //			return "There are no transactions in this period.<p>";
        }
        if ($bal['dbalance'] > $bal['cbalance']) {
            $bal['dbalance'] = sprint($bal['dbalance'] - $bal['cbalance']);
            $bal['cbalance'] = "";
            $balance = $bal['dbalance'];
            $fl = "DR";
        } elseif ($bal['cbalance'] > $bal['dbalance']) {
            $bal['cbalance'] = sprint($bal['cbalance'] - $bal['dbalance']);
            $bal['dbalance'] = "";
            $balance = $bal['cbalance'];
            $fl = "CR";
        } else {
            $bal['cbalance'] = "";
            $bal['dbalance'] = "";
            $balance = "0.00";
            $fl = "";
        }
        $balance = sprint($balance);
        $bal['cbalance'] = sprint($bal['cbalance']);
        $bal['dbalance'] = sprint($bal['dbalance']);
        $hide .= "<input type=hidden name=accids[] value='{$acc['accid']}'>";
        $trans .= "<tr><td colspan=8><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td></tr>";
        $trans .= "<tr><td colspan=2><br></td><td>Br/Forwd</td><td>Brought Forward</td><td align=right>{$bal['dbalance']}</td><td align=right>{$bal['cbalance']}</td><td align=right>{$balance} {$fl}</td><td> </td></tr>";
        # --> transactio reding comes here <--- #
        $dbal['debit'] = 0;
        $dbal['credit'] = 0;
        $tranRs = get($yd['yrname'] . "_audit", "*", $prdname . "_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 = "DR";
            } elseif ($tran['cbalance'] > $tran['dbalance']) {
                $tran['cbalance'] = sprint($tran['cbalance'] - $tran['dbalance']);
                $tran['dbalance'] = "";
                $cbalance = $tran['cbalance'];
                $cfl = "CR";
            } 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 .= "<tr><td><br></td><td>{$tran['edate']}</td><td>{$tran['eref']}</td><td>{$tran['descript']}</td><td align=right>{$tran['debit']}</td><td align=right>{$tran['credit']}</td><td align=right>{$cbalance} {$cfl}</td><td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td></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 .= "<tr><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>";
        $trans .= "<tr><td colspan=8><br></td></tr>";
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t<center>\n\t<h3>General Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=90%>\n\t<tr><td colspan=8 align=center><input type=submit value='Export to Spreadsheet'></td></tr>\n\t<tr><td colspan=8><br></td></tr>\n\t<tr><td>{$sp}</td><th>Date</th><th>Reference</th><th>Description</th><th>Debit</th><th>Credit</th><th>Balance</th><th>Contra Acc</th></tr>\n\t{$trans}\n\t<tr><td colspan=8><br></td></tr>\n\t<table>";
    //<tr><td colspan=8 align=center><input type=submit value='Export to Spreadsheet'></td></tr>
    //return $view;
    include "temp.xls.php";
    Stream("Ledger", $view);
}

Beispiel #16

Datei anzeigen

Datei: supp-ledger-prd.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    foreach ($_POST as $key => $value) {
        ${$key} = $value;
    }
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($year, "string", 1, 10, "Invalid year.");
    if (isset($accnt)) {
        if ($accnt == 'slct') {
            if (isset($supids)) {
                foreach ($supids as $key => $supid) {
                    $v->isOk($supid, "num", 1, 20, "Invalid Supplier number.");
                }
            } else {
                return "<li class=err>Please select at least one Creditor.</li>" . slctacc();
            }
        }
    } else {
        $v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class=err>" . $e["msg"];
        }
        $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $supids = array();
        db_connect();
        $sql = "SELECT supid FROM suppliers WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $supids[] = $ac['supid'];
            }
        } else {
            return "<li calss=err> There are no suppliers yet in Cubit.";
        }
    }
    # Period name
    $prdname = prdname($prd);
    db_conn('core');
    $Sl = "SELECT * FROM year WHERE yrdb='{$year}'";
    $Ri = db_exec($Sl) or errDie("Unable to get data.");
    $yd = pg_fetch_array($Ri);
    $trans = "";
    foreach ($supids as $key => $supid) {
        $supRs = get("cubit", "supname, supno, balance", "suppliers", "supid", $supid);
        $sup = pg_fetch_array($supRs);
        $idRs = get($yd['yrname'] . "_audit", "min(id)", $prdname . "_suppledger", "supid", $supid);
        $id = pg_fetch_array($idRs);
        if ($id['min'] != 0) {
            $balRs = get($yd['yrname'] . "_audit", "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", $prdname . "_suppledger", "id", $id['min']);
            $bal = pg_fetch_array($balRs);
            $bal['cbalance'] += 0;
            $bal['dbalance'] += 0;
        } else {
            $balRs = get("cubit", "balance", "suppliers", "supid", $supid);
            $bal = pg_fetch_array($balRs);
            $bal['balance'] += 0;
            if ($bal['balance'] < 0) {
                $bal['dbalance'] = $bal['balance'] * -1;
                $bal['cbalance'] = 0;
            } else {
                $bal['cbalance'] = $bal['balance'];
                $bal['dbalance'] = 0;
            }
            //$bal['dbalance'] += $amount;
        }
        # Total balance changes
        if ($bal['dbalance'] > $bal['cbalance']) {
            $bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
            $bal['cbalance'] = 0;
        } elseif ($bal['cbalance'] > $bal['dbalance']) {
            $bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
            $bal['dbalance'] = 0;
        } else {
            $bal['cbalance'] = 0;
            $bal['dbalance'] = 0;
        }
        $bal['credit'] = $bal['cbalance'];
        $bal['debit'] = $bal['dbalance'];
        $balance = sprint($bal['cbalance'] - $bal['dbalance']);
        $trans .= "<tr class='bg-even'><td colspan=8><b>{$sup['supno']} - {$sup['supname']} </b></td></tr>";
        $trans .= "<tr class='bg-even'><td colspan=2><br></td><td>Br/Forwd</td><td>Brought Forward</td><td align=right>{$bal['debit']}</td><td align=right>{$bal['credit']}</td><td align=right>{$balance}</td><td> </td></tr>";
        # --> Transaction reading comes here <--- #
        $dbal['debit'] = 0;
        $dbal['credit'] = 0;
        $tranRs = get($yd['yrname'] . "_audit", "*", $prdname . "_suppledger", "supid", $supid);
        while ($tran = pg_fetch_array($tranRs)) {
            $caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
            $cacc = pg_fetch_array($caccRs);
            $dbal['debit'] += $tran['debit'];
            $dbal['credit'] += $tran['credit'];
            $cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
            # Format date
            $tran['edate'] = explode("-", $tran['edate']);
            $tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
            $trans .= "<tr class='bg-odd'><td><br></td><td>{$tran['edate']}</td><td>{$tran['eref']}</td><td>{$tran['descript']}</td><td align=right>{$tran['debit']}</td><td align=right>{$tran['credit']}</td><td align=right>{$cbalance}</td><td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td></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 .= "<tr class='bg-even'><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>";
        $trans .= "<tr><td colspan=8><br></td></tr>";
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t<center>\n\t<h3>Creditors Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=75%>\n\t<tr><td>{$sp}</td><th>Date</th><th>Reference</th><th>Description</th><th>Debit</th><th>Credit</th><th>Balance</th><th>Contra Acc</th></tr>\n\t{$trans}\n\t<table>\n\t<p>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=25%>\n\t\t<tr><td><br></td></tr>\n\t\t<tr><th>Quick Links</th></tr>\n\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
    return $view;
}

Beispiel #17

Datei anzeigen

Datei: supp-ledger.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($fprd, "string", 1, 14, "Invalid from period number.");
    $v->isOk($tprd, "string", 1, 14, "Invalid to period number.");
    if (isset($accnt)) {
        if ($accnt == 'slct') {
            if (isset($supids)) {
                foreach ($supids as $key => $supid) {
                    $v->isOk($supid, "num", 1, 20, "Invalid Supplier number.");
                }
            } else {
                return "<li class='err'>Please select at least one Creditor.</li>" . slctacc();
            }
        }
    } else {
        $v->isOk("###", "num", 0, 0, "ERROR : Invalid Accounts Selection.");
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $supids = array();
        db_connect();
        $sql = "SELECT supid FROM suppliers WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $supids[] = $ac['supid'];
            }
        } else {
            return "<li calss='err'> There are no suppliers yet in Cubit.</li>";
        }
    }
    $prds = array();
    if ($tprd < $fprd) {
        for ($i = $fprd; $i <= 12; ++$i) {
            $prds[] = $i;
        }
        for ($i = 1; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    } else {
        for ($i = $fprd; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    }
    $hide = "";
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $trans = "";
    foreach ($supids as $key => $supid) {
        $supRs = get("cubit", "supname, supno, balance", "suppliers", "supid", $supid);
        $sup = pg_fetch_array($supRs);
        $hide .= "<input type='hidden' name='supids[]' value='{$supid}'>";
        $trans .= "\n\t\t<tr>\n\t\t\t<td colspan='8' align='center'><h3>{$sup['supname']}</h3></td>\n\t\t</tr>";
        foreach ($prds as $prd) {
            $prdname = prdname($prd);
            $trans .= "\n\t\t\t<tr>\n\t\t\t\t<th colspan='8'>{$prdname}</th>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<th>{$sp}</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>";
            $idRs = get($prd, "min(id)", "suppledger", "supid", $supid);
            $id = pg_fetch_array($idRs);
            if ($id['min'] != 0) {
                $balRs = get($prd, "(cbalance-credit) AS cbalance,(dbalance-debit) AS dbalance", "suppledger", "id", $id['min']);
                $bal = pg_fetch_array($balRs);
                $bal['cbalance'] += 0;
                $bal['dbalance'] += 0;
            } else {
                $balRs = get("cubit", "balance", "suppliers", "supid", $supid);
                $bal = pg_fetch_array($balRs);
                $bal['balance'] += 0;
                if ($bal['balance'] < 0) {
                    $bal['dbalance'] = $bal['balance'];
                    $bal['cbalance'] = 0;
                } else {
                    $bal['cbalance'] = $bal['balance'];
                    $bal['dbalance'] = 0;
                }
                //$bal['dbalance'] += $amount;
            }
            /* show the balance as a debit/credit, and not the individual balances of both */
            if ($bal['dbalance'] > $bal['cbalance']) {
                $bal['dbalance'] = $bal['dbalance'] - $bal['cbalance'];
                $bal['cbalance'] = 0;
            } elseif ($bal['cbalance'] > $bal['dbalance']) {
                $bal['cbalance'] = $bal['cbalance'] - $bal['dbalance'];
                $bal['dbalance'] = 0;
            } else {
                $bal['cbalance'] = 0;
                $bal['dbalance'] = 0;
            }
            $bal['credit'] = sprint($bal['cbalance']);
            $bal['debit'] = sprint($bal['dbalance']);
            $balance = sprint($bal['cbalance'] - $bal['dbalance']);
            $trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='8'><b>{$sup['supno']} - {$sup['supname']} </b></td>\n\t\t\t\t\t\t\t</tr>";
            $bbf_date = date("t-M-Y", mktime(0, 0, 0, $prd - 1, 1, getYearOfFinMon($prd)));
            $trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$bal['debit']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$bal['credit']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$balance}</td>\n\t\t\t\t\t\t\t\t<td>&nbsp;</td>\n\t\t\t\t\t\t\t</tr>";
            # --> Transaction reading comes here <--- #
            $dbal['debit'] = 0;
            $dbal['credit'] = 0;
            if ($t == "s") {
                $tranRs = get($prd, "*", "suppledger", "supid", $supid, "ORDER BY id");
            } else {
                $tranRs = get($prd, "*", "suppledger", "supid", $supid, "ORDER BY edate,id");
            }
            while ($tran = pg_fetch_array($tranRs)) {
                $caccRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $tran['contra']);
                $cacc = pg_fetch_array($caccRs);
                $tran['debit'] = sprint($tran['debit']);
                $tran['credit'] = sprint($tran['credit']);
                $dbal['debit'] += $tran['debit'];
                $dbal['credit'] += $tran['credit'];
                if ($t == "s") {
                    $cbalance = sprint($tran['cbalance'] - $tran['dbalance']);
                } else {
                    $cbalance = sprint($dbal['credit'] + $bal['credit'] - ($dbal['debit'] + $bal['debit']));
                }
                if ($t == "s") {
                    $tran['edate'] = $tran['sdate'];
                }
                # Format date
                $tran['edate'] = explode("-", $tran['edate']);
                $tran['edate'] = $tran['edate'][2] . "-" . $tran['edate'][1] . "-" . $tran['edate'][0];
                $trans .= "<tr class='bg-odd'><td><br></td><td>{$tran['edate']}</td><td>{$tran['eref']}</td><td>{$tran['descript']}</td><td align=right>{$tran['debit']}</td><td align=right>{$tran['credit']}</td><td align=right>{$cbalance}</td><td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td></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\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='2'><br></td>\n\t\t\t\t\t\t\t\t<td>A/C Total</td>\n\t\t\t\t\t\t\t\t<td>Total for period {$prdname} to Date :</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$dbal['debit']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$dbal['credit']}</td>\n\t\t\t\t\t\t\t\t<td align='right'></td>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<td colspan='8'><br></td>\n\t\t\t\t\t\t\t</tr>";
        }
    }
    $view = "\n\t\t\t\t<center>\n\t\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t\t\t<input type='hidden' name='t' value='{$t}'>\n\t\t\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t\t\t{$hide}\n\t\t\t\t\t<h3>Creditors Ledger</h3>\n\t\t\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t\t\t{$trans}\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='8' align='center'><input type='submit' name='export' value='Export to Spreadsheet'></td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>\n\t\t\t\t</from>";
    return $view;
}

Beispiel #18

Datei anzeigen

Datei: old-ledger.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    foreach ($_POST as $key => $value) {
        ${$key} = $value;
    }
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
    if ($accnt == 'slct') {
        if (isset($accids)) {
            foreach ($accids as $key => $accid) {
                $v->isOk($accid, "num", 1, 20, "Invalid Account number.");
            }
        } else {
            $v->isOk("###", "num", 0, 0, "ERROR : Please select at least one account.");
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class=err>" . $e["msg"];
        }
        $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    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.";
        }
    }
    # Period name
    $prdname = prdname($prd);
    $trans = "";
    foreach ($accids as $key => $accid) {
        $accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
        $acc = pg_fetch_array($accRs);
        # 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 ($prd != PRD_DB) {
                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;
        }
        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);
        $trans .= "<tr class='bg-even'><td colspan=8><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td></tr>";
        $trans .= "<tr class='bg-even'><td colspan=2><br></td><td>Br/Forwd</td><td>Brought Forward</td><td align=right>{$bal['dbalance']}</td><td align=right>{$bal['cbalance']}</td><td align=right>{$balance} {$fl}</td><td> </td></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 .= "<tr class='bg-odd'><td><br></td><td>{$tran['edate']}</td><td>{$tran['eref']}</td><td>{$tran['descript']}</td><td align=right>{$tran['debit']}</td><td align=right>{$tran['credit']}</td><td align=right>{$cbalance} {$cfl}</td><td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td></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 .= "<tr class='bg-even'><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>";
        $trans .= "<tr><td colspan=8><br></td></tr>";
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t<center>\n\t<h3>General Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=75%>\n\t<tr><td>{$sp}</td><th>Date</th><th>Reference</th><th>Description</th><th>Debit</th><th>Credit</th><th>Balance</th><th>Contra Acc</th></tr>\n\t{$trans}\n\t<table>\n\t<p>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=25%>\n\t\t<tr><td><br></td></tr>\n\t\t<tr><th>Quick Links</th></tr>\n\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<script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
    return $view;
}

Beispiel #19

Datei anzeigen

Datei: ledger-summary.php Projekt: andrecoetzee/accounting-123.com

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>&nbsp;</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>&nbsp;</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;
}

Beispiel #20

Datei anzeigen

Datei: stock-ledger-audit-xls.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($accnt, "string", 1, 5, "Invalid Accounts Selection.");
    $v->isOk($year, "string", 1, 10, "Invalid year.");
    if ($accnt == 'slct') {
        if (isset($stkids)) {
            foreach ($stkids as $key => $stkid) {
                $v->isOk($stkid, "num", 1, 20, "Invalid Stock code.");
            }
        } else {
            return "<li class='err'>ERROR : Please select at least one Stock Item.</li>" . slctacc();
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    if ($accnt == 'all') {
        $stkids = array();
        db_connect();
        $sql = "SELECT stkid FROM stock WHERE div = '" . USER_DIV . "'";
        $rs = db_exec($sql);
        if (pg_num_rows($rs) > 0) {
            while ($ac = pg_fetch_array($rs)) {
                $stkids[] = $ac['stkid'];
            }
        } else {
            return "<li calss='err'> There are no Stock Items yet in Cubit.</li>";
        }
    }
    $hide = "";
    # Period name
    $prds = array();
    if ($tprd < $fprd) {
        for ($i = $fprd; $i <= 12; ++$i) {
            $prds[] = $i;
        }
        for ($i = 1; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    } else {
        for ($i = $fprd; $i <= $tprd; ++$i) {
            $prds[] = $i;
        }
    }
    $trans = "";
    foreach ($stkids as $key => $stkid) {
        $stkRs = get("cubit", "*", "stock", "stkid", $stkid);
        $stk = pg_fetch_array($stkRs);
        $trans .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='8' align='center'><h3>{$stk['stkcod']} - {$stk['stkdes']}</h3></td>\n\t\t\t</tr>";
        $hide .= "<input type='hidden' name='stkids[]' value='{$stkid}'>";
        foreach ($prds as $prd) {
            # Period name
            $prdname = prdname($prd);
            # Get balances
            $idRs = get($prd, "max(id), min(id)", "stkledger", "yrdb='{$year}' AND stkid", $stkid);
            $id = pg_fetch_array($idRs);
            if ($id['min'] != 0) {
                $balRs = get($prd, "qty, (bqty - qty) as bqty, trantype, (balance - csamt) as balance", "stkledger", "id", $id['min']);
                $bal = pg_fetch_array($balRs);
                $cbalRs = get($prd, "balance", "stkledger", "id", $id['max']);
                $cbal = pg_fetch_array($cbalRs);
                /*
                if($bal['trantype'] == 'dt'){
                	$bal['bqty'] =  ($bal['bqty'] + $bal['qty']);
                }else{
                	$bal['bqty'] =  ($bal['bqty'] - $bal['qty']);
                }
                */
            } else {
                if ($prd != PRD_DB) {
                    continue;
                }
                $balRs = get("cubit", "csamt as balance, units as bqty", "stock", "stkid", $stkid);
                $bal = pg_fetch_array($balRs);
                $cbal['balance'] = 0;
                $cbal['bqty'] = 0;
            }
            $balance = sprint($bal['balance']);
            $get_yr = "SELECT yrname FROM core.year WHERE yrdb = '{$year}' LIMIT 1";
            $run_yr = db_exec($get_yr) or errDie("Unable to get year information.");
            if (pg_numrows($run_yr) > 0) {
                $yarr = pg_fetch_array($run_yr);
                $tmpyear = substr($yarr['yrname'], 1);
            } else {
                $tmpyear = date("Y") - 1;
            }
            $get_last_prdmap = "SELECT period FROM core.prdmap WHERE month = '12'";
            $run_last_prdmap = db_exec($get_last_prdmap) or errDie("Unable to get last month period");
            if (pg_numrows($run_last_prdmap) > 0) {
                $lastperiod = pg_fetch_result($run_last_prdmap, 0, 0);
            }
            $get_current_prd = "SELECT period FROM core.prdmap WHERE month = '{$prd}' LIMIT 1";
            $run_current_prd = db_exec($get_current_prd) or errDie("Unable to get period information.");
            if (pg_fetch_array($run_current_prd) > 0) {
                $currentperiod = pg_fetch_result($run_current_prd, 0, 0);
                if ((int) $lastperiod >= (int) $currentperiod) {
                    $show_year = $tmpyear - 1;
                } else {
                    $show_year = $tmpyear;
                }
            }
            $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='5'><b>({$stk['stkcod']}) {$stk['stkdes']}</b></td>\n\t\t\t\t</tr>";
            $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$show_year}-{$prd}-01<br></td>\n\t\t\t\t\t<td>Balance Brought Forward</td>\n\t\t\t\t\t<td align='right'>{$bal['bqty']}</td>\n\t\t\t\t\t<td align='right'>{$balance} </td>\n\t\t\t\t\t<td align='right'>{$balance} </td>\n\t\t\t\t</tr>";
            # --> transactio reding comes here <--- #
            $dbal['balance'] = 0;
            $dbal['bqty'] = 0;
            $tranRs = nget($prd, "*", "stkledger", "yrdb='{$year}' AND stkid", $stkid . " ORDER BY id ASC");
            while ($tran = pg_fetch_array($tranRs)) {
                $dbal['balance'] += $tran['csamt'];
                $dbal['bqty'] += $tran['qty'];
                # sprinting
                $tran['csamt'] = sprint($tran['csamt']);
                $tran['balance'] = sprint($tran['balance']);
                # 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>\n\t\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t\t<td>{$tran['details']}</td>\n\t\t\t\t\t\t<td>{$tran['qty']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['csamt']}</td>\n\t\t\t\t\t\t<td align='right'>{$tran['balance']}</td>\n\t\t\t\t\t</tr>";
            }
            $dbal['balance'] = sprint($dbal['balance']);
            $trans .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td><br></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['bqty']}</td>\n\t\t\t\t\t<td align='right'>{$dbal['balance']} </td>\n\t\t\t\t\t<td align='right'>{$dbal['balance']} </td>\n\t\t\t\t</tr>";
            $trans .= "<tr><td colspan='5'><br></td></tr>";
        }
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t\t<center>\n\t\t<h3>Inventory Ledger</h3>\n\t\t<table " . TMPL_tblDflts . " width='75%'>\n\t\t\t<tr>\n\t\t\t\t<th>DATE</th>\n\t\t\t\t<th>DETAILS</th>\n\t\t\t\t<th>QTY</th>\n\t\t\t\t<th>COST AMOUNT</th>\n\t\t\t\t<th>BALANCE</th>\n\t\t\t</tr>\n\t\t\t{$trans}\n\t\t</table>";
    include "temp.xls.php";
    Stream("Ledger", $view);
}

Beispiel #21

Datei anzeigen

Datei: trans-amt.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    foreach ($_POST as $key => $value) {
        ${$key} = $value;
    }
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($accnt, "string", 1, 5, "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();
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    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>";
        }
    }
    # Period name
    $prdname = prdname($prd);
    $hide = "";
    $trans = "";
    foreach ($accids as $key => $accid) {
        $accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
        $acc = pg_fetch_array($accRs);
        $hide .= "<input type=hidden name=accids[] value='{$accid}'>";
        db_conn($prd);
        $sql = "SELECT DISTINCT eref FROM ledger WHERE acc = '{$accid}' AND debit > 0";
        $disRs = db_exec($sql);
        if (pg_numrows($disRs) > 0) {
            while ($dis = pg_fetch_array($disRs)) {
                $sql = "SELECT sum(debit) as debit,edate FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}' GROUP BY edate";
                $sumRs = db_exec($sql);
                $sum = pg_fetch_array($sumRs);
                $trans .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$sum['edate']}</td>\n\t\t\t\t\t\t\t<td><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t\t\t\t\t<td>{$dis['eref']}</td>\n\t\t\t\t\t\t\t<td><br descript><td align='right'>{$sum['debit']}</td>\n\t\t\t\t\t\t\t<td><br credit></td>\n\t\t\t\t\t\t\t<td><br cacc></td>\n\t\t\t\t\t\t</tr>";
                $sql = "SELECT * FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}'";
                $tranRs = db_exec($sql);
                while ($tran = pg_fetch_array($tranRs)) {
                    $trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><br date></td>\n\t\t\t\t\t\t\t\t<td><br account></td>\n\t\t\t\t\t\t\t\t<td><br ref></td>\n\t\t\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t\t\t<td><br debit></td>\n\t\t\t\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t\t\t</tr>";
                }
                $trans .= "<tr><td><br></td></tr>";
            }
        }
        db_conn($prd);
        $sql = "SELECT DISTINCT eref FROM ledger WHERE acc = '{$accid}' AND credit > 0";
        $disRs = db_exec($sql);
        if (pg_numrows($disRs) > 0) {
            while ($dis = pg_fetch_array($disRs)) {
                $sql = "SELECT sum(credit) as credit,edate FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}' GROUP BY edate";
                $sumRs = db_exec($sql);
                $sum = pg_fetch_array($sumRs);
                $trans .= "\n\t\t\t\t\t\t<tr class='bg-even'>\n\t\t\t\t\t\t\t<td>{$sum['edate']}</td>\n\t\t\t\t\t\t\t\t<td><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t\t\t\t\t\t<td>{$dis['eref']}</td>\n\t\t\t\t\t\t\t\t<td><br descript></td>\n\t\t\t\t\t\t\t\t<td><br debit></td>\n\t\t\t\t\t\t\t\t<td>{$sum['credit']}</td>\n\t\t\t\t\t\t\t\t<td><br></td>\n\t\t\t\t\t\t\t</tr>";
                $sql = "SELECT * FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}'";
                $tranRs = db_exec($sql);
                while ($tran = pg_fetch_array($tranRs)) {
                    $trans .= "\n\t\t\t\t\t\t\t<tr class='bg-odd'>\n\t\t\t\t\t\t\t\t<td><br date></td>\n\t\t\t\t\t\t\t\t<td><br account></td>\n\t\t\t\t\t\t\t\t<td><br ref></td>\n\t\t\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t\t\t\t<td><br credit></td>\n\t\t\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t\t\t</tr>";
                }
                $trans .= "<tr><td><br></td></tr>";
            }
        }
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t\t\t<center>\n\t\t\t<form action='../xls/trans-amt-xls.php' method='POST'>\n\t\t\t\t<input type='hidden' name='key' value='viewtran'>\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<h3>Journal Entries By Ref no.</h3>\n\t\t\t<table " . TMPL_tblDflts . " width='95%'>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Date</th>\n\t\t\t\t\t<th>Account</th>\n\t\t\t\t\t<th>Ref No.</th>\n\t\t\t\t\t<th>Description</th>\n\t\t\t\t\t<th>Debit</th>\n\t\t\t\t\t<th>Credit</th>\n\t\t\t\t\t<th>Contra Acc</th>\n\t\t\t\t</tr>\n\t\t\t\t{$trans}\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></tr>\n\t\t\t</from>\n\t\t\t</table>\n\t\t\t<p>\n\t\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td align='center'><a href='index-reports.php'>Financials</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td align='center'><a href='index-reports-journal.php'>Current Year Details General Ledger Reports</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\n\t\t\t\t</tr>\n\t\t\t</table>";
    return $view;
}

Beispiel #22

Datei anzeigen

Datei: ledger-ytd-xls.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    global $MONPRD, $PRDMON;
    # Get vars
    foreach ($_POST as $key => $value) {
        ${$key} = $value;
    }
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($accnt, "string", 1, 5, "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();
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class=err>" . $e["msg"];
        }
        $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    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.";
        }
    }
    $hide = "";
    # Get all previous Periods
    db_conn("audit");
    $sql = "SELECT prd.*, map.period FROM audit.closedprd prd, core.prdmap map\n\t\t\tWHERE prd.prdnum=map.month AND map.period::integer<'" . $MONPRD[PRD_DB] . "'\n\t\t\tORDER BY map.period::integer";
    $clsRs = db_exec($sql) or errDie("Could not get closed periods from audit DB", SELF);
    $trans = "";
    if (pg_numrows($clsRs) > 0) {
        while ($cls = pg_fetch_array($clsRs)) {
            $prd = $cls['prdnum'];
            # Period name
            $prdname = prdname($prd);
            $trans .= "<tr><td colspan=8 align=center><h3>{$prdname}</h3></td></tr>";
            $hide = "";
            if (isset($t)) {
                unset($t);
            }
            foreach ($accids as $key => $accid) {
                $accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
                $acc = pg_fetch_array($accRs);
                # 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 {
                    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));
                $hide .= "<input type=hidden name=accids[] value='{$acc['accid']}'>";
                $trans .= "\n\t\t\t<tr>\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>\n\t\t\t\t<td colspan='2' align='right'>{$bbf_date}</td>\n\t\t\t\t<td>Br/Forwd</td>\n\t\t\t\t<td>Brought Forward</td>\n\t\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t\t<td>&nbsp;</td>\n\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 .= "<tr><td><br></td><td>{$tran['edate']}</td><td>{$tran['eref']}</td><td>{$tran['descript']}</td><td align=right>{$tran['debit']}</td><td align=right>{$tran['credit']}</td><td align=right>{$cbalance} {$cfl}</td><td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td></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><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>";
                $trans .= "<tr><td colspan=8><br></td></tr>";
            }
            if (!isset($t)) {
                $trans .= "<tr><td colspan=8 align=center><li> There are no transactions in this period.</td></tr>";
            }
        }
    } else {
        $trans .= "<tr><td colspan=8 align=center><li> There are no closed periods this year.</td></tr>";
    }
    # Period name
    $prdname = prdname(PRD_DB);
    $prd = PRD_DB;
    $trans .= "<tr><td><br></td></tr>";
    $trans .= "<tr><td colspan=8 align=center><h3>{$prdname}</h3></td></tr>";
    $hide = "";
    if (isset($t)) {
        unset($t);
    }
    foreach ($accids as $key => $accid) {
        $accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
        $acc = pg_fetch_array($accRs);
        # 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 {
            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));
        $hide .= "<input type=hidden name=accids[] value='{$acc['accid']}'>";
        $trans .= "\n\t\t<tr>\n\t\t\t<td colspan='8'><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<td colspan='2'>{$bbf_date}</td>\n\t\t\t<td>Br/Forwd</td>\n\t\t\t<td>Brought Forward</td>\n\t\t\t<td align='right'>{$bal['dbalance']}</td>\n\t\t\t<td align='right'>{$bal['cbalance']}</td>\n\t\t\t<td align='right'>{$balance} {$fl}</td>\n\t\t\t<td>&nbsp;</td>\n\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];
            $tran['debit'] = sprint($tran['debit']);
            $tran['credit'] = sprint($tran['credit']);
            $trans .= "<tr><td><br></td><td>{$tran['edate']}</td><td>{$tran['eref']}</td><td>{$tran['descript']}</td><td align=right>{$tran['debit']}</td><td align=right>{$tran['credit']}</td><td align=right>{$cbalance} {$cfl}</td><td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td></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><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>";
        $trans .= "<tr><td colspan=8><br></td></tr>";
    }
    if (!isset($t)) {
        $trans .= "<tr><td colspan=8 align=center><li> There are no transactions in this period.</td></tr>";
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t<center>\n\t<h3>Year Review General Ledger</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=90%>\n\t<tr><td colspan=8><br></td></tr>\n\t<tr><th>{$sp}</th><th>Date</th><th>Reference</th><th>Description</th><th>Debit</th><th>Credit</th><th>Balance</th><th>Contra Acc</th></tr>\n\t{$trans}\n\t<tr><td colspan=8><br></td></tr>\n\t</table>";
    include "temp.xls.php";
    Stream("Ledger", $view);
    return $view;
}

Beispiel #23

Datei anzeigen

Datei: trans-amt-xls.php Projekt: andrecoetzee/accounting-123.com

function viewtran($_POST)
{
    # Get vars
    foreach ($_POST as $key => $value) {
        ${$key} = $value;
    }
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($prd, "string", 1, 14, "Invalid Period number.");
    $v->isOk($accnt, "string", 1, 5, "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();
        }
    }
    # display errors, if any
    if ($v->isError()) {
        $confirm = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirm .= "<li class=err>" . $e["msg"];
        }
        $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='&laquo; Correct submission'>";
        return $confirm;
    }
    # Get the ids
    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.";
        }
    }
    # Period name
    $prdname = prdname($prd);
    $hide = "";
    $trans = "";
    foreach ($accids as $key => $accid) {
        $accRs = get("core", "accname, accid, topacc, accnum", "accounts", "accid", $accid);
        $acc = pg_fetch_array($accRs);
        $hide .= "<input type=hidden name=accids[] value='{$accid}'>";
        db_conn($prd);
        $sql = "SELECT DISTINCT eref FROM ledger WHERE acc = '{$accid}' AND debit > 0";
        $disRs = db_exec($sql);
        if (pg_numrows($disRs) > 0) {
            while ($dis = pg_fetch_array($disRs)) {
                $sql = "SELECT sum(debit) as debit,edate FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}' GROUP BY edate";
                $sumRs = db_exec($sql);
                $sum = pg_fetch_array($sumRs);
                $trans .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td>{$sum['edate']}</td>\n\t\t\t\t\t\t\t<td><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t\t\t\t\t<td>{$dis['eref']}</td>\n\t\t\t\t\t\t\t<td><br descript><td align='right'>{$sum['debit']}</td>\n\t\t\t\t\t\t\t<td><br credit></td>\n\t\t\t\t\t\t\t<td><br cacc></td>\n\t\t\t\t\t\t</tr>";
                $sql = "SELECT * FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}'";
                $tranRs = db_exec($sql);
                while ($tran = pg_fetch_array($tranRs)) {
                    $trans .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td><br date></td>\n\t\t\t\t\t\t\t<td><br account></td>\n\t\t\t\t\t\t\t<td><br ref></td>\n\t\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t\t<td><br debit></td>\n\t\t\t\t\t\t\t<td align='right'>{$tran['debit']}</td>\n\t\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t\t</tr>";
                }
                $trans .= "<tr><td><br></td></tr>";
            }
        }
        db_conn($prd);
        $sql = "SELECT DISTINCT eref FROM ledger WHERE acc = '{$accid}' AND credit > 0";
        $disRs = db_exec($sql);
        if (pg_numrows($disRs) > 0) {
            while ($dis = pg_fetch_array($disRs)) {
                $sql = "SELECT sum(credit) as credit,edate FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}' GROUP BY edate";
                $sumRs = db_exec($sql);
                $sum = pg_fetch_array($sumRs);
                $trans .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td>{$sum['edate']}</td>\n\t\t\t\t\t\t\t<td><b>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</b></td>\n\t\t\t\t\t\t\t<td>{$dis['eref']}</td>\n\t\t\t\t\t\t\t<td><br descript></td>\n\t\t\t\t\t\t\t<td><br debit></td>\n\t\t\t\t\t\t\t<td>{$sum['credit']}</td>\n\t\t\t\t\t\t\t<td><br></td>\n\t\t\t\t\t\t</tr>";
                $sql = "SELECT * FROM ledger WHERE acc = '{$accid}' AND eref = '{$dis['eref']}'";
                $tranRs = db_exec($sql);
                while ($tran = pg_fetch_array($tranRs)) {
                    $trans .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td><br date></td>\n\t\t\t\t\t\t\t<td><br account></td>\n\t\t\t\t\t\t\t<td><br ref></td>\n\t\t\t\t\t\t\t<td>{$tran['descript']}</td>\n\t\t\t\t\t\t\t<td align='right'>{$tran['credit']}</td>\n\t\t\t\t\t\t\t<td><br credit></td>\n\t\t\t\t\t\t\t<td>{$tran['ctopacc']}/{$tran['caccnum']} - {$tran['caccname']}</td>\n\t\t\t\t\t\t</tr>";
                }
                $trans .= "<tr><td><br></td></tr>";
            }
        }
    }
    $sp = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    $view = "\n\t\t\t<center>\n\t\t\t<h3>Journal Entries By Ref no.</h3>\n\t\t<table " . TMPL_tblDflts . " width='95%'>\n\t\t\t<tr>\n\t\t\t\t<td colspan='3' align='left'>" . COMP_NAME . "</td>\n\t\t\t\t<td colspan='4' align='right'>" . date("Y-m-d") . "</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<th>Date</th>\n\t\t\t\t<th>Account</th>\n\t\t\t\t<th>Ref No.</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>Contra Acc</th>\n\t\t\t</tr>\n\t\t\t{$trans}\n\t\t</table>";
    include "temp.xls.php";
    Stream("Transactions", $view);
    return $view;
}