function confirm($_POST) { # Get vars extract($_POST); if (isset($back)) { return slctacc($_POST); } # validate input require_lib("validate"); $v = new validate(); $v->isOk($supid, "num", 1, 50, "Invalid Supplier number."); $v->isOk($accid, "num", 1, 50, "Invalid Contra Account."); $v->isOk($refnum, "num", 1, 10, "Invalid Reference number."); $v->isOk($amount, "float", 1, 20, "Invalid Amount."); $v->isOk($details, "string", 0, 255, "Invalid Details."); $v->isOk($author, "string", 1, 30, "Invalid Authorising person name."); $datea = explode("-", $date); if (count($datea) == 3) { if (!checkdate($datea[1], $datea[0], $datea[2])) { $v->isOk($date, "num", 1, 1, "Invalid date."); } } else { $v->isOk($date, "num", 1, 1, "Invalid date."); } # 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='« Correct submission'>"; return $confirm; } # Get contra account details $accRs = get("core", "*", "accounts", "accid", $accid); $acc = pg_fetch_array($accRs); # Select supplier db_connect(); $sql = "SELECT * FROM suppliers WHERE supid = '{$supid}' AND div = '" . USER_DIV . "'"; $suppRslt = db_exec($sql) or errDie("Unable to access databse.", SELF); if (pg_numrows($suppRslt) < 1) { return "<li class='err'>Invalid supplier ID, or supplier has been blocked</li>"; } else { $supp = pg_fetch_array($suppRslt); } # Probe tran type if ($entry == "CT") { $tran = "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t\t\t<td>{$supp['supno']} - {$supp['supname']}</td>\n\t\t\t\t\t\t</tr>"; } else { $tran = "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$supp['supno']} - {$supp['supname']}</td>\n\t\t\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t\t</tr>"; } // Layout $confirm = "\n\t\t\t<h3>Record Journal transaction</h3>\n\t\t\t<h4>Confirm entry</h4>\n\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t<input type='hidden' name='key' value='write'>\n\t\t\t\t<input type='hidden' name='supid' value='{$supid}'>\n\t\t\t\t<input type='hidden' name='accid' value='{$accid}'>\n\t\t\t\t<input type='hidden' name='accname' value='{$acc['accname']}'>\n\t\t\t\t<input type='hidden' name='date' value='{$date}'>\n\t\t\t\t<input type='hidden' name='refnum' value='{$refnum}'>\n\t\t\t\t<input type='hidden' name='entry' value='{$entry}'>\n\t\t\t\t<input type='hidden' name='amount' value='{$amount}'>\n\t\t\t\t<input type='hidden' name='details' value='{$details}'>\n\t\t\t\t<input type='hidden' name='author' value='{$author}'>\n\t\t\t\t<input type='hidden' name='date_day' value='{$date_day}'>\n\t\t\t\t<input type='hidden' name='date_month' value='{$date_month}'>\n\t\t\t\t<input type='hidden' name='date_year' value='{$date_year}'>\n\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t<tr>\n\t\t\t\t\t<td width='50%'><h3>Debit</h3></td>\n\t\t\t\t\t<td width='50%'><h3>Credit</h3></td>\n\t\t\t\t</tr>\n\t\t\t\t{$tran}\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>Date</td>\n\t\t\t\t\t<td>{$date}</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>Referance number</td>\n\t\t\t\t\t<td>{$refnum}</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>Amount</td>\n\t\t\t\t\t<td>" . CUR . " {$amount}</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>Details</td>\n\t\t\t\t\t<td>{$details}</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>Authorising Person</td>\n\t\t\t\t\t<td>{$author}</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td><input type='submit' name='back' value='« Correction'></td>\n\t\t\t\t\t<td align='right'><input type='submit' value='Write »'></td>\n\t\t\t\t</tr>\n\t\t\t</table>\n\t\t\t</form>\n\t\t\t<p>\n\t\t\t<table border='0' cellpadding='2' cellspacing='1' width='15%'>\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='../core/trans-new.php'>Journal Transactions</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td align='center'><a href='../supp-view.php'>View Suppliers</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t</table>"; return $confirm; }
function write($_POST) { # Get vars extract($_POST); if (isset($back)) { return slctacc($_POST); } # validate input require_lib("validate"); $v = new validate(); $date = $date_year . "-" . $date_month . "-" . $date_day; if (!checkdate($date_month, $date_day, $date_year)) { $v->isOk($date, "num", 1, 1, "Invalid date."); } foreach ($accid as $key => $value) { $dtamount[$key] += 0; $ctamount[$key] += 0; if ($dtamount[$key] > 0 || $ctamount[$key] > 0) { $v->isOk($accid[$key], "num", 1, 50, "Invalid Account to be Debited.[{$key}]"); $v->isOk($dtamount[$key], "float", 1, 20, "Invalid Debit Amount.[{$key}]"); $v->isOk($ctamount[$key], "float", 1, 20, "Invalid Credit Amount.[{$key}]"); $v->isOk($descript[$key], "string", 0, 255, "Invalid Details.[{$key}]"); } } # display errors, if any if ($v->isError()) { $write = ""; $errors = $v->getErrors(); foreach ($errors as $e) { $write .= "<li class='err'>" . $e["msg"] . "</li>"; } $write .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>"; return $write; } foreach ($accid as $key => $value) { # begin sql transaction pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF); if ($dtamount[$key] > 0) { writetrans($accid[$key], $account, $date, $refnum, $dtamount[$key], $descript[$key]); } elseif ($ctamount[$key] > 0) { writetrans($account, $accid[$key], $date, $refnum, $ctamount[$key], $descript[$key]); } pglib_transaction("COMMIT") or errDie("Unable to finish a database transaction.", SELF); } // Start layout $write = "\n\t\t<center>\n\t\t<h3>Journal transactions have been recorded.</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Account</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</tr>"; foreach ($accid as $key => $value) { if ($dtamount[$key] > 0 || $ctamount[$key] > 0) { core_connect(); $accRss = get("core", "*", "accounts", "accid", $accid[$key]); if (pg_numrows($accRss) < 1) { return "<li> Accounts to be debited does not exist.</li>"; } $acc_each[$key] = pg_fetch_array($accRss); $write .= "\n\t\t\t\t<tr bgcolor=" . bgcolorg() . ">\n\t\t\t\t\t<td valign='center'>" . $acc_each[$key]['accname'] . "</td>\n\t\t\t\t\t<td>{$descript[$key]}</td>\n\t\t\t\t\t<td nowrap>" . CUR . " " . sprint($dtamount[$key]) . "</td>\n\t\t\t\t\t<td nowrap>" . CUR . " " . sprint($ctamount[$key]) . "</td>\n\t\t\t\t</tr>"; } } $write .= "\n\t\t</table>\n\t\t<br>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\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 href='../reporting/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='trans-new.php'>Journal Transaction</td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='batch-view.php'>View batch file</td>\n\t\t\t</tr>\n\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t</table>"; return $write; }
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='« 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 = " "; $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); }
function import_csv_file($_POST) { extract($_POST); $file = file($_FILES['import_file']['tmp_name']); if (!is_array($file) or count($file) < 1) { return slctacc(array(), "<li class='err'>Please Ensure File Format Is Correct.</li><br>"); } $refnum = getrefnum(); foreach ($file as $line) { $cleanline = trim($line); if (strlen($cleanline) < 8) { continue; } $line_arr = explode(",", $cleanline); db_connect(); $get_cusnum = "SELECT cusnum FROM customers WHERE accno = '{$line_arr['0']}' LIMIT 1"; $run_cusnum = db_exec($get_cusnum) or errDie("Unable to get customer information."); $cusnum = pg_fetch_result($run_cusnum, 0, 0); // $refnum = $line_arr[3]; // $cusnum = $line_arr[0]; $darr = explode("/", $line_arr[7]); $date_day = $darr[1]; $date_month = $darr[0]; $date_year = "20" . $darr[2]; $date = "{$date_year}-{$date_month}-{$date_day}"; $aarr = explode("/", $line_arr[2]); $acc_topacc = $aarr[0]; $acc_accnum = $aarr[1]; $get_vatcode = "SELECT id FROM vatcodes WHERE code = '{$line_arr['4']}' LIMIT 1"; $run_vatcode = db_exec($get_vatcode) or errDie("Unable to get vat code information."); $vatcode = pg_fetch_result($run_vatcode, 0, 0); core_connect(); $get_acc = "SELECT accid FROM accounts WHERE topacc = '{$acc_topacc}' AND accnum = '{$acc_accnum}' LIMIT 1"; $run_acc = db_exec($get_acc) or errDie("Unable to get contra account information."); $accid = pg_fetch_result($run_acc, 0, 0); db_connect(); $ins_sql = "\n\t\t\tINSERT INTO cust_trans_batch (\n\t\t\t\tcusnum, proc_date, ref_num, entry_type, contra_account, chrg_vat, vatcode, amount, description\n\t\t\t) VALUES (\n\t\t\t\t'{$cusnum}', '{$date}', '{$refnum}', '{$line_arr['5']}', '{$accid}', '{$line_arr['6']}', '{$vatcode}','{$line_arr['9']}', '{$line_arr['8']}'\n\t\t\t)"; $run_sql = db_exec($ins_sql) or errDie("Unable to record customer transaction."); $refnum++; } return slctacc(array(), "<li class='yay'>{$numtrans} Transaction(s) Has Been Imported.</li><br>"); }
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='« 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 = " "; $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); }
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='« 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 = " "; $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'> </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> </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> </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'> </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>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='8'> </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; }
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='« 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 = " "; $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; }
function confirm($_POST) { # Get vars extract($_POST); # Redirect if must chrgvat if ($chrgvat == 'yes' && !isset($vataccid)) { return slctVatAcc($_POST); } if (isset($vatcode)) { $vatcode += 0; } else { $vatcode = 0; } if (isb($dtaccid)) { return "<li class='err'>You selected a main account.</li>" . slctacc($_POST); } if (isb($ctaccid)) { return "<li class='err'>You selected a main account.</li>" . slctacc($_POST); } # validate input require_lib("validate"); $v = new validate(); $v->isOk($ctaccid, "num", 1, 50, "Invalid Account to be Credited."); $v->isOk($dtaccid, "num", 1, 50, "Invalid Account to be Debited."); $v->isOk($refnum, "num", 1, 10, "Invalid Reference number."); $v->isOk($amount, "float", 1, 20, "Invalid Amount."); $v->isOk($details, "string", 0, 255, "Invalid Details."); $v->isOk($author, "string", 1, 30, "Invalid Authorising person name."); $datea = explode("-", $date); if (count($datea) == 3) { if (!checkdate($datea[1], $datea[0], $datea[2])) { $v->isOk($date, "num", 1, 1, "Invalid date."); } } else { $v->isOk($date, "num", 1, 1, "Invalid date."); } if ($chrgvat == 'yes') { $v->isOk($vataccid, "num", 1, 50, "Invalid VAT Account number."); $v->isOk($vatdedacc, "num", 1, 50, "Invalid VAT Deductable Account number."); $v->isOk($vatinc, "string", 1, 3, "Invalid vat inclusive selection."); } # display errors, if any if ($v->isError()) { $confirm = ""; $errors = $v->getErrors(); foreach ($errors as $e) { $confirm .= "<li class='err'>" . $e["msg"] . "</li>"; } return details($_POST, $confirm); } if ($amount <= 0) { return details($_POST, "<li class='err'>Invalid Amount To Process.</li>"); } $dtaccRs = get("core", "*", "accounts", "accid", $dtaccid); $dtacc = pg_fetch_array($dtaccRs); $ctaccRs = get("core", "*", "accounts", "accid", $ctaccid); $ctacc = pg_fetch_array($ctaccRs); # show vat account if vat is charged if ($chrgvat == 'yes') { $vataccRs = get("core", "*", "accounts", "accid", $vataccid); $vatacc = pg_fetch_array($vataccRs); $vatin = ucwords($vatinc); $vataccnum = "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>VAT Account</td>\n\t\t\t\t<td><input type='hidden' name='vataccid' value='{$vataccid}'><input type='hidden' name='vatdedacc' value='{$vatdedacc}'>{$vatacc['topacc']}/{$vatacc['accnum']} - {$vatacc['accname']}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>VAT Inclusive</td>\n\t\t\t\t<td><input type='hidden' name='vatinc' value='{$vatinc}'>{$vatin}</td>\n\t\t\t</tr>"; } else { $vataccnum = ""; } $vat = ucwords($chrgvat); if ($vatcode > 0) { db_conn('cubit'); $Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'"; $Ri = db_exec($Sl) or errDie("unable to get data."); $va = pg_fetch_array($Ri); $vd = "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>VAT Code</td>\n\t\t\t\t<td>{$va['code']}</td>\n\t\t\t</tr>"; } else { $vd = ""; } $confirm = "\n\t\t<h3>Record Recurring Transaction</h3>\n\t\t<h4>Confirm entry</h4>\n\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t<input type='hidden' name='key' value='write'>\n\t\t\t<input type='hidden' name='vatcode' value='{$vatcode}'>\n\t\t\t<input type='hidden' name='dtaccid' value='{$dtaccid}'>\n\t\t\t<input type='hidden' name='ctaccid' value='{$ctaccid}'>\n\t\t\t<input type='hidden' name='dtaccname' value='{$dtacc['accname']}'>\n\t\t\t<input type='hidden' name='ctaccname' value='{$ctacc['accname']}'>\n\t\t\t<input type='hidden' name='date' value='{$date}'>\n\t\t\t<input type='hidden' name='date_day' value='{$date_day}'>\n\t\t\t<input type='hidden' name='date_month' value='{$date_month}'>\n\t\t\t<input type='hidden' name='date_year' value='{$date_year}'>\n\t\t\t<input type='hidden' name='refnum' value='{$refnum}'>\n\t\t\t<input type='hidden' name='amount' value='{$amount}'>\n\t\t\t<input type='hidden' name='chrgvat' value='{$chrgvat}'>\n\t\t\t<input type='hidden' name='details' value='{$details}'>\n\t\t\t<input type='hidden' name='author' value='{$author}'>\n\t\t<table " . TMPL_tblDflts . " width='500'>\n\t\t\t<tr>\n\t\t\t\t<td width='50%'><h3>Debit</h3></td>\n\t\t\t\t<td width='50%'><h3>Credit</h3></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$dtacc['topacc']}/{$dtacc['accnum']} - {$dtacc['accname']}</td>\n\t\t\t\t<td>{$ctacc['topacc']}/{$ctacc['accnum']} - {$ctacc['accname']}</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Date</td>\n\t\t\t\t<td>{$date}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Reference number</td>\n\t\t\t\t<td>{$refnum}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Amount</td>\n\t\t\t\t<td>" . CUR . " {$amount}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Charge VAT </td>\n\t\t\t\t<td>{$vat}</td>\n\t\t\t</tr>\n\t\t\t{$vataccnum}\n\t\t\t{$vd}\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Details</td>\n\t\t\t\t<td>{$details}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Authorising Person</td>\n\t\t\t\t<td>{$author}</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td align='right'><input type='button' value='« Back' onClick='javascript:history.back()'></td>\n\t\t\t\t<td align='right'><input type='submit' value='Confirm Transaction »'></td>\n\t\t\t</tr>\n\t\t</form>\n\t\t</table>\n\t\t<p>\n\t\t<table border=0 cellpadding='2' cellspacing='1' width=15%>\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 href='../reporting/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='rectrans-view.php'>View Recurring Transactions</td>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td align='center'><a href='trans-new.php'>Journal Transactions</td>\n\t\t\t</tr>\n\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t</table>"; return $confirm; }
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='« 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 = " "; $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; }
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='« 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 = " "; $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); }
function details($_POST) { # Get vars extract($_POST); #redirect if must chrgvat if ($chrgvat == 'yes' && !isset($vataccid)) { return slctacc($_POST); } # validate input require_lib("validate"); $v = new validate(); $v->isOk($lname, "string", 1, 255, "Invalid Ledger Name."); $v->isOk($dtaccid, "num", 1, 50, "Invalid Account to be Debited."); $v->isOk($ctaccid, "num", 1, 50, "Invalid Account to be Credited."); $v->isOk($chrgvat, "string", 1, 4, "Invalid charge vat option."); $v->isOk($numtran, "num", 1, 20, "Invalid Number on entries."); $v->isOk($dateopt, "string", 1, 10, "Invalid date entry option."); $v->isOk($desopt, "string", 1, 5, "Invalid Description option."); if ($desopt == 'once') { $v->isOk($descript, "string", 1, 255, "Invalid Description."); } else { $v->isOk($descript, "string", 0, 255, "Invalid Description."); } $v->isOk($refopt, "string", 1, 5, "Invalid Reference number option."); if ($refopt == 'once') { $v->isOk($refnum, "string", 1, 255, "Invalid Reference number."); } else { $v->isOk($refnum, "string", 0, 255, "Invalid Reference number."); } if ($chrgvat == 'yes') { $v->isOk($vataccid, "num", 1, 50, "Invalid Vat Account number."); $v->isOk($vatdedacc, "num", 1, 50, "Invalid Vat Deductable Account number."); $v->isOk($vatinc, "string", 1, 3, "Invalid vat inclusive selection."); } # display errors, if any if ($v->isError()) { $confirm = ""; $errors = $v->getErrors(); foreach ($errors as $e) { $confirm .= "<li class=err>" . $e["msg"] . "</li>"; } return error($_POST, $confirm); # $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>"; # return $confirm; } # Account numbers $dtaccRs = get("core", "*", "accounts", "accid", $dtaccid); $dtacc = pg_fetch_array($dtaccRs); $ctaccRs = get("core", "*", "accounts", "accid", $ctaccid); $ctacc = pg_fetch_array($ctaccRs); if ($chrgvat == 'yes') { $vataccRs = get("core", "*", "accounts", "accid", $vataccid); $vatacc = pg_fetch_array($vataccRs); $vatin = ucwords($vatinc); $vataccnum = "\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Vat Account</td>\r\n\t\t\t\t<td><input type='hidden' name='vataccid' value='{$vataccid}'><input type='hidden' name='vatdedacc' value='{$vatdedacc}'>{$vatacc['topacc']}/{$vatacc['accnum']} - {$vatacc['accname']}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Vat Inclusive</td>\r\n\t\t\t\t<td><input type='hidden' name='vatinc' value='{$vatinc}'>{$vatin}</td>\r\n\t\t\t</tr>"; } else { $vataccnum = ""; } /* Toggle Options */ # Charge Vat Option $vat = ucwords($chrgvat); # Date Option if ($dateopt == 'system') { $date = 'System Date'; } elseif ($dateopt == 'user') { $date = 'User Input Date'; } # Description and Refnum Option $options = array("num" => "Auto Number", "emp" => "Empty Input Box", "once" => "Once Only Setting", "edit" => "Default Editable Input"); $descriptopt = $options[$desopt]; $refnumopt = $options[$refopt]; # put auto number if its auto number if ($refopt == 'num') { $refnums = $options[$refopt]; } else { $refnums = $refnum; } /* End Toggle Options */ # uppercase first letter of name $lname = ucfirst($lname); // Details $details = "\r\n\t\t<center>\r\n\t\t<h3> New High Speed Input Ledger </h3>\r\n\t\t<form action='" . SELF . "' method='POST' name='form'>\r\n\t\t\t<input type='hidden' name='key' value='write'>\r\n\t\t\t<input type='hidden' name='lname' value='{$lname}'>\r\n\t\t\t<input type='hidden' name='dtaccid' value='{$dtaccid}'>\r\n\t\t\t<input type='hidden' name='ctaccid' value='{$ctaccid}'>\r\n\t\t\t<input type='hidden' name='chrgvat' value='{$chrgvat}'>\r\n\t\t\t<input type='hidden' name='numtran' value='{$numtran}'>\r\n\t\t\t<input type='hidden' name='dateopt' value='{$dateopt}'>\r\n\t\t\t<input type='hidden' name='descript' value='{$descript}'>\r\n\t\t\t<input type='hidden' name='desopt' value='{$desopt}'>\r\n\t\t\t<input type='hidden' name='refnum' value='{$refnum}'>\r\n\t\t\t<input type='hidden' name='refopt' value='{$refopt}'>\r\n\t\t<table " . TMPL_tblDflts . " align='center'>\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Option</th>\r\n\t\t\t\t<th>Value</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Ledger Name</td>\r\n\t\t\t\t<td>{$lname}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<th><h4>Debit</h4></th>\r\n\t\t\t\t<th><h4>Credit</h4></th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td align='center'>{$dtacc['topacc']}/{$dtacc['accnum']} - {$dtacc['accname']}</td>\r\n\t\t\t\t<td align='center'>{$ctacc['topacc']}/{$ctacc['accnum']} - {$ctacc['accname']}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr><td><br></td></tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<th colspan='3'>Options</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Number of Entries</td>\r\n\t\t\t\t<td>{$numtran}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Date Entry</td>\r\n\t\t\t\t<td>{$date}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Charge Vat </td>\r\n\t\t\t\t<td>{$vat}</td>\r\n\t\t\t</tr>\r\n\t\t\t{$vataccnum}\r\n\t\t\t<tr>\r\n\t\t\t\t<th colspan='3'>Description</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Description</td>\r\n\t\t\t\t<td>{$descript}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Option</td>\r\n\t\t\t\t<td>{$descriptopt}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<th colspan='3'>Reference Number</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Reference Number</td>\r\n\t\t\t\t<td>{$refnums}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Option</td>\r\n\t\t\t\t<td>{$refnumopt}</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr><td><br></td></tr>\r\n\t\t\t<tr>\r\n\t\t\t\t<td align='right'><input type='button' value='« Back' onClick='javascript:history.back()'></td>\r\n\t\t\t\t<td align='right'><input type='submit' value='Continue »'></td>\r\n\t\t\t</tr>\r\n\t\t</table>\r\n\t\t</form>\r\n\t\t<p>\r\n\t\t<table border=0 cellpadding='2' cellspacing='1' width=15%>\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Quick Links</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td align='center'><a href='ledger-new.php'>New High Speed Input Ledger</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td align='center'><a href='ledger-view.php'>View High Speed Input Ledgers</td>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td align='center'><a href='../main.php'>Main Menu</td>\r\n\t\t\t</tr>\r\n\t\t</table>"; return $details; }
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='« 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 = " "; $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> </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> </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'> </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>\n\t\t\t\t<td colspan='8'> </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; }
function details($_POST, $err = "") { # Get vars extract($_POST); if (isset($back) and isStock($accid)) { return get_stock_items($_POST); } elseif (isset($back)) { return slctacc($_POST); } $accid += 0; $amount = $amount + 0; # validate input require_lib("validate"); $v = new validate(); $v->isOk($refnum, "num", 1, 10, "Invalid Reference number."); $v->isOk($ct_day, "num", 1, 2, "Invalid to Date ct_day."); $v->isOk($ct_month, "num", 1, 2, "Invalid to Date ct_monthth."); $v->isOk($ct_year, "num", 1, 4, "Invalid to Date Year."); $date = $ct_day . "-" . $ct_month . "-" . $ct_year; if (!checkdate($ct_month, $ct_day, $ct_year)) { $v->isOk($date, "num", 1, 1, "Invalid date."); } $v->isOk($accid, "num", 1, 50, "Invalid Contra Account."); $v->isOk($cusnum, "num", 1, 50, "Invalid Customer number."); $v->isOk($vatinc, "string", 1, 6, "Invalid Transaction VAT Option."); if (sprint($amount) <= 0) { $v->addError($amount, "Invalid Or Too Small Amount Entered."); } if ($accid == "0") { $v->addError($accid, "No Allowed Accounts Found. <a href='settings/credit-note-accounts.php'>Please Add One First</a>"); } # display errors, if any if ($v->isError()) { $confirm = ""; $errors = $v->getErrors(); foreach ($errors as $e) { $confirm .= "<li class='err'>" . $e["msg"] . "</li>"; } return slctacc($_POST, $confirm . "<br>"); } # CHECK IF THIS DATE IS IN THE BLOCKED RANGE $blocked_date_from = getCSetting("BLOCKED_FROM"); $blocked_date_to = getCSetting("BLOCKED_TO"); if (strtotime($date) >= strtotime($blocked_date_from) and strtotime($date) <= strtotime($blocked_date_to) and !user_is_admin(USER_ID)) { return slctacc($_POST, "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li><br>"); } #if stock returned is selected ... override the setting if (isset($gotstock) and strlen($gotstock) > 0) { #get a stock id db_conn('exten'); $get_stkid = "SELECT stkacc FROM warehouses ORDER BY whid ASC LIMIT 1"; $run_stkid = db_exec($get_stkid) or errDie("Unable to get inventory account information."); if (pg_numrows($run_stkid) < 1) { return "<li class='err'>No Inventory Account Found.</li>"; } else { $accid = pg_fetch_result($run_stkid, 0, 0); } } # get contra account details $accRs = get("core", "*", "accounts", "accid", $accid); $acc = pg_fetch_array($accRs); #### handle the stock we selected if ((isStock($accid) or isset($gotstock)) and !isset($stockcontinue)) { #for whatever reason ... we need to get stock ... return get_stock_items($_POST); } db_connect(); # Select customer $sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'"; $custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF); if (pg_numrows($custRslt) < 1) { return "<li class='err'>Invalid customer ID, or customer has been blocked.</li>"; } else { $cust = pg_fetch_array($custRslt); } db_conn("exten"); $sql = "SELECT * FROM departments WHERE deptid = '{$cust['deptid']}' AND div = '" . USER_DIV . "'"; $deptRslt = db_exec($sql); if (pg_numrows($deptRslt) < 1) { return "<i class='err'>Customer Department Not Found</i>"; } else { $dept = pg_fetch_array($deptRslt); } #get a stock total $stock_total = 0; if (isset($stock_items) and is_array($stock_items)) { foreach ($stock_items as $each => $own) { $stock_total = $stock_total + $stock_cost[$each] * $own; } } $stkamount = 0; $send_stock = ""; $show_stock = ""; if (isset($stock_items) and is_array($stock_items)) { $unit_total = array_sum($stock_items); if ($unit_total == 0) { $stock_unit_avg_cost = 0; } else { $stock_unit_avg_cost = sprint(($amount - $stock_total) / $unit_total); } foreach ($stock_items as $each => $own) { if ($own > 0) { if (!isset($own) or strlen($own) < 1) { $own = 1; } if (!isset($stock_cost[$each]) or strlen($stock_cost[$each]) < 1) { $stock_cost[$each] = 0; } $send_stock .= "<input type='hidden' name='stock_items[{$each}]' value='{$own}'>\n"; $send_stock .= "<input type='hidden' name='stock_cost[{$each}]' value='{$stock_cost[$each]}'>\n"; db_connect(); $get_stk = "SELECT stkdes,whid FROM stock WHERE stkid = '{$each}' LIMIT 1"; $run_stk = db_exec($get_stk) or errDie("Unable to get stock information."); if (pg_numrows($run_stk) < 1) { $stock_name = "Unknown"; } else { $stock_name = pg_fetch_result($run_stk, 0, 0); $whid = pg_fetch_result($run_stk, 0, 1); db_conn('exten'); $get_cos = "SELECT cosacc FROM warehouses WHERE whid = '{$whid}' LIMIT 1"; $run_cos = db_exec($get_cos) or errDie("Unable to get cost of sale information. (1)"); if (pg_numrows($run_cos) < 1) { $show_cos = ""; } else { $cos_id = pg_fetch_result($run_cos, 0, 0); db_conn('core'); $get_acc = "SELECT topacc,accnum,accname FROM accounts WHERE accid = '{$cos_id}' LIMIT 1"; $run_acc = db_exec($get_acc) or errDie("Unable to get cost of sale information. (2)"); if (pg_numrows($run_acc) < 1) { $show_cos = ""; } else { $aarr = pg_fetch_array($run_acc); $show_cos = "{$aarr['topacc']}/{$aarr['accnum']} - {$aarr['accname']}"; } } } if (!isset($stock_prof[$each])) { $stock_prof[$each] = sprint($stock_unit_avg_cost * $own); } $show_stock .= "\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t\t<td colspan='2'>{$stock_name}</td>\n\t\t\t\t\t\t\t\t\t\t<td>{$own}</td>\n\t\t\t\t\t\t\t\t\t\t<td nowrap>" . CUR . " " . sprint($stock_cost[$each]) . "</td>\n\t\t\t\t\t\t\t\t\t\t<td nowrap>" . CUR . " " . sprint($stock_cost[$each] * $own) . "</td>\n\t\t\t\t\t\t\t\t\t\t<td><input type='text' size='7' name='stock_prof[{$each}]' value='{$stock_prof[$each]}'></td>\n\t\t\t\t\t\t\t\t\t\t<td nowrap>{$show_cos}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t"; $stkamount = $stkamount + $stock_cost[$each] * $own; } } } if (strlen($send_stock) == 0) { $send_stock = "<input type='hidden' name='stock_items' value='0'>"; $send_stock .= "<input type='hidden' name='stock_cost' value='0'>"; $send_stock .= "<input type='hidden' name='stock_prof' value='0'>"; $get_gds_note = ""; } else { $send_stock .= "<input type='hidden' name='stockcontinue' value='0'>"; $send_stock .= "<input type='hidden' name='gotstock' value='1'>"; if (isset($gds_note) and strlen($gds_note) > 0) { $gds_note_sel = "checked='yes'"; } else { $gds_note_sel = ""; } $get_gds_note = "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Print Goods Received Note</td>\n\t\t\t\t\t\t\t\t<td><input type='checkbox' name='gds_note' value='yes' {$gds_note_sel}></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t"; } db_conn('core'); $get_sales_acc = "SELECT topacc,accnum,accname FROM accounts WHERE accid = '{$dept['incacc']}' LIMIT 1"; $run_sales_acc = db_exec($get_sales_acc) or errDie("Unable to get sales account information. (1)"); if (pg_numrows($run_sales_acc) < 1) { $show_sales = ""; } else { $sarr = pg_fetch_array($run_sales_acc); $show_sales = "{$sarr['topacc']}/{$sarr['accnum']} - {$sarr['accname']}"; } if (strlen($show_stock) > 0) { $show_stock = "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th colspan='2'>Stock Description</th>\n\t\t\t\t\t\t\t<th>Number Of Units Returned</th>\n\t\t\t\t\t\t\t<th>Unit Cost</th>\n\t\t\t\t\t\t\t<th>Total</th>\n\t\t\t\t\t\t\t<th>Profit/Loss</th>\n\t\t\t\t\t\t\t<th>Cost Of Sale Account</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{$show_stock}\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td colspan='4' align='right'><b>Total:</b></td>\n\t\t\t\t\t\t\t<td nowrap>" . CUR . " " . sprint($stock_total) . "</td>\n\t\t\t\t\t\t\t<td colspan='2' nowrap><li class='err'>Difference ({$amount} - " . sprint($stock_total) . "): " . CUR . " " . sprint($amount - $stock_total) . "</li></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td colspan='7'><li class='err'>Sales Account To Be Used: {$show_sales}</li></td>\n\t\t\t\t\t\t</tr>"; } # Probe tran type if ($entry == "CT") { $tran = "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td colspan='3'>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t\t<td colspan='2'>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t\t</tr>"; } else { $tran = "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td colspan='3'>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t\t\t<td colspan='2'>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t</tr>"; } if (!isset($amount)) { $amount = $stkamount; $details = ""; } if (!isset($vataccid)) { $vataccid = 0; } if (isset($vatinc) and $vatinc != "novat") { db_connect(); $get_vatcodes = "SELECT * FROM vatcodes ORDER BY code"; $run_vatcodes = db_exec($get_vatcodes) or errDie("Unable to get vat code inoformation."); if (pg_numrows($run_vatcodes) < 1) { return "No Vatcodes Found. Please Add One First."; } else { $vatcode_drop = "<select name='vatcode'>"; while ($varr = pg_fetch_array($run_vatcodes)) { $vatcode_drop .= "<option value='{$varr['id']}'>({$varr['code']}) {$varr['description']}</option>"; } $vatcode_drop .= "</select>"; } db_conn('core'); $vatacc = "<select name='vataccid'>"; $sql = "SELECT * FROM accounts WHERE div = '" . USER_DIV . "' ORDER BY accname ASC"; $accRslt = db_exec($sql); if (pg_numrows($accRslt) < 1) { return "<li>There are No accounts in Cubit.</li>"; } $vatacc_newid = gethook("accnum", "salesacc", "name", "VAT", "VAT"); $vataccid = getCSetting("CRED_NOTE_VAT_ACC"); if (!isset($vataccid) or strlen($vataccid) < 1 or $vataccid == "0") { $vataccid = $vatacc_newid; } // if (!isset($vataccid) OR $vataccid == "0") // $vataccid = $vatacc_newid; // while($acc_arr = pg_fetch_array($accRslt)){ // # Check Disable // if(isDisabled($acc_arr['accid'])) // continue; // if($vataccid == $acc_arr['accid']) { // $sel = "selected"; // } else { // $sel = ""; // } // $vatacc .= "<option value='$acc_arr[accid]' $sel>$acc_arr[topacc]/$acc_arr[accnum] - $acc_arr[accname]</option>"; // } // $vatacc .= "</select>"; if (isset($vatdedacc) and $vatdedacc == "dt") { $dsel1 = "checked='yes'"; } else { $dsel1 = ""; } if (isset($vatdedacc) and $vatdedacc == "ct") { $dsel2 = "checked='yes'"; } else { $dsel2 = ""; } if ($dsel1 == "" and $dsel2 == "") { $dsel1 = "checked='yes'"; } if (isStock($accid) and isset($gotstock)) { $showgetvatacc = "<input type='hidden' name='vatdedacc' value='dt'>"; } else { $showgetvatacc = "\n\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t<td colspan='2'valign='top'>VAT Deductable Account</td>\n\t\t\t\t\t\t\t\t\t<td colspan='2'>\n\t\t\t\t\t\t\t\t\t\t<input type='radio' name='vatdedacc' value='dt' {$dsel1} />{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}<br />\n\t\t\t\t\t\t\t\t\t\t<input type='radio' name='vatdedacc' value='ct' {$dsel2} />{$cust['accno']} - {$cust['surname']}\n\t\t\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t"; } db_conn('core'); $get_vatacc = "SELECT accname FROM accounts WHERE accid = '{$vataccid}' LIMIT 1"; $run_vatacc = db_exec($get_vatacc) or errDie("Unable to get vat account details."); if (pg_numrows($run_vatacc) < 1) { $showvatacc = ""; } else { $vatacc_id = pg_fetch_result($run_vatacc, 0, 0); $showvatacc = "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='2'>VAT Account</td>\n\t\t\t\t\t\t\t\t<td colspan='3'>{$vatacc_id} <a target='_blank' href='settings/credit-note-accounts.php'>Change Account</a></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t"; } // <tr class='".bg_class()."'> // <td colspan='2'>VAT Account</td> // <td colspan='3'>$vatacc</td> // </tr> $get_vats = "\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t<input type='hidden' name='vataccid' value='{$vataccid}'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th colspan='5'>VAT Detail</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{$showgetvatacc}\n\t\t\t\t\t\t{$showvatacc}\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td colspan='2'>VAT Code</td>\n\t\t\t\t\t\t\t<td colspan='3'>{$vatcode_drop}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t"; } else { $get_vats = "\n\t\t\t\t\t\t<input type='hidden' name='vatinc' value='novat'>\n\t\t\t\t\t\t<input type='hidden' name='vatcode' value='0'>\n\t\t\t\t\t"; } // Layout Details $details = "\n \t\t\t\t<h3>Confirm Credit Note Details</h3>\n \t\t\t\t{$err}\n \t\t\t\t<form action='" . SELF . "' method='POST' name='form'>\n\t\t\t\t <input type='hidden' name='key' value='write'>\n\t\t\t\t\t\t<input type='hidden' name='type' value='1'>\n\t\t\t\t\t\t<input type='hidden' name='date' value='{$date}'>\n\t\t\t\t\t\t<input type='hidden' name='cusnum' value='{$cusnum}'>\n\t\t\t\t <input type='hidden' name='accid' value='{$accid}'>\n\t\t\t\t <input type='hidden' name='accname' value='{$acc['accname']}'>\n\t\t\t\t\t\t<input type='hidden' name='entry' value='{$entry}'>\n\t\t\t\t\t\t<input type='hidden' name='ct_day' value='{$ct_day}'>\n\t\t\t\t <input type='hidden' name='ct_month' value='{$ct_month}'>\n\t\t\t\t <input type='hidden' name='ct_year' value='{$ct_year}'>\n\t\t\t\t <input type='hidden' name='vatinc' value='{$vatinc}'>\n\t\t\t\t <input type='hidden' name='ac' value=''>\n\t\t\t\t <input type='hidden' name='amount' value='{$amount}'>\n\t\t\t\t <input type='hidden' name='refnum' value='{$refnum}'>\n\t\t\t\t <input type='hidden' name='difference' value='" . sprint($amount - $stock_total) . "'>\n\t\t\t\t {$send_stock}\n\t\t\t <table " . TMPL_tblDflts . " width='500'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th colspan='3'>Debit</th>\n\t\t\t\t\t\t\t<th colspan='2'>Credit</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{$tran}\n\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t{$show_stock}\n \t\t\t\t\t{$get_vats}\n \t\t\t\t</table>\n \t\t\t\t<table " . TMPL_tblDflts . " width='500'>\n \t\t\t\t\t" . TBL_BR . "\n \t\t\t\t\t<tr>\n \t\t\t\t\t\t<th colspan='2'>Transaction Details</th>\n \t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Transaction Details</td>\n\t\t\t\t\t\t\t<td valign='center'><textarea cols='30' rows='5' name='details'>{$details}</textarea></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{$get_gds_note}\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Person Authorising</td>\n\t\t\t\t\t\t\t<td valign='center'><input type='hidden' size='20' name='author' value=" . USER_NAME . ">" . USER_NAME . "</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td><input type='submit' name='back' value='« Correction'></td>\n\t\t\t\t\t\t\t<td valign='center' align='right'><input type='submit' value='Confirm »'></td>\n\t\t\t\t\t\t</tr>\n\t\t\t </table>\n\t\t\t </form>"; return $details; }
function viewtran($_POST, $pure = false) { extract($_POST); 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($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($_POST); } } if ($v->isError()) { return $v->genErrors(); } 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>"; } } # 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; } } $hide = ""; $sp = " "; $trans = ""; foreach ($stkids as $key => $stkid) { $stkRs = get("cubit", "*", "stock", "stkid", $stkid); $stk = pg_fetch_array($stkRs); $trans .= "\n\t\t<tr>\n\t\t\t<td colspan='8' align='center'><h3>{$stk['stkcod']} - {$stk['stkdes']}</h3></td>\n\t\t</tr>"; $hide .= "<input type='hidden' name='stkids[]' value='{$stkid}'>"; foreach ($prds as $prd) { $prdname = getMonthName($prd); $trans .= "\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<th colspan='8'>{$prdname}</th>\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<th>DATE</th>\n\t\t\t\t\t\t\t\t<th>DETAILS</th>\n\t\t\t\t\t\t\t\t<th>QTY</th>\n\t\t\t\t\t\t\t\t<th>COST AMOUNT</th>\n\t\t\t\t\t\t\t\t<th>BALANCE</th>\n\t\t\t\t\t\t\t</tr>"; $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 { $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']); $trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td colspan='5'><b>({$stk['stkcod']}) {$stk['stkdes']}</b></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t\t<td>Balance Brought Forward</td>\n\t\t\t\t\t\t\t\t<td align='right'>" . sprint3($bal['bqty']) . "</td>\n\t\t\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t\t\t<td align='right'>" . sprint($balance) . "</td>\n\t\t\t\t\t\t\t</tr>"; $dbal['balance'] = 0; $dbal['bqty'] = 0; $qtytotal = 0; $tranRs = nget($prd, "*", "stkledger", "yrdb='" . YR_DB . "' AND stkid", $stkid . " ORDER BY edate,id ASC"); while ($tran = pg_fetch_array($tranRs)) { $dbal['balance'] += $tran['csamt']; $dbal['bqty'] = $tran['bqty']; $qtytotal += $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]; $balance += $tran["csamt"]; $trans .= "\n\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t<td>{$tran['edate']}</td>\n\t\t\t\t\t\t\t\t\t<td>{$tran['details']}</td>\n\t\t\t\t\t\t\t\t\t<td align='right'>" . sprint3($tran['qty']) . "</td>\n\t\t\t\t\t\t\t\t\t<td align='right'>{$tran['csamt']}</td>\n\t\t\t\t\t\t\t\t\t<td align='right'>" . sprint($balance) . "</td>\n\t\t\t\t\t\t\t\t</tr>"; } $dbal['balance'] = sprint($dbal['balance']); //$qtytotal was $dbal[bqty] $trans .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td> </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'>" . sprint3($qtytotal) . "</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$dbal['balance']}</td>\n\t\t\t\t\t\t\t\t<td align='right'>{$dbal['balance']}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t" . TBL_BR; } } $view = ""; if (!$pure) { $view .= "\n\t\t\t\t\t<center>\n\t\t\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t\t\t<input type='hidden' name='key' value='excel'>\n\t\t\t\t\t\t<input type='hidden' name='accnt' value='{$accnt}'>\n\t\t\t\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t\t\t\t<input type='hidden' name='continue' value='yes'>\n\t\t\t\t\t\t{$hide}\n\t\t\t\t\t\t<h3>Inventory 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</form>"; } $view .= "\n\t</table>"; if (!$pure) { $view .= "\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 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-other.php'>Other 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; }
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='« 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 = " "; $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; }
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> </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> </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; }
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> </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> </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 = " "; $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; }
function write($_POST) { # Get vars extract($_POST); if (isset($back)) { unset($_POST["back"]); return slctacc($_POST); } if (isset($cback)) { return custconfirm($_POST); } # validate input require_lib("validate"); $v = new validate(); foreach ($amount as $key => $value) { if ($value > 0) { continue; } $v->isOk($ctaccid[$key], "num", 1, 50, "Invalid Account to be Credited.[{$key}]"); $v->isOk($dtaccid[$key], "num", 1, 50, "Invalid Account to be Debited.[{$key}]"); $v->isOk($refnum[$key], "num", 1, 10, "Invalid Reference number.[{$key}]"); $v->isOk($amount[$key], "float", 1, 20, "Invalid Amount.[{$key}]"); $v->isOk($descript[$key], "string", 0, 255, "Invalid Details.[{$key}]"); $datea = explode("-", $date[$key]); if (count($datea) == 3) { if (!checkdate($datea[1], $datea[0], $datea[2])) { $v->isOk("dadasdas", "num", 1, 1, "Invalid date."); } } else { $v->isOk("asdasd", "num", 1, 1, "Invalid date."); } $date[$key] = $datea[2] . "-" . $datea[1] . "-" . $datea[0]; } # display errors, if any if ($v->isError()) { $write = ""; $errors = $v->getErrors(); foreach ($errors as $e) { $write .= "<li class='err'>" . $e["msg"] . "</li>"; } $write .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>"; return $write; } foreach ($amount as $key => $value) { if ($value <= 0) { continue; } // Accounts details $dtaccRs = get("core", "accname, topacc, accnum", "accounts", "accid", $dtaccid[$key]); $dtacc[$key] = pg_fetch_array($dtaccRs); $ctaccRs = get("core", "accname, topacc, accnum", "accounts", "accid", $ctaccid[$key]); $ctacc[$key] = pg_fetch_array($ctaccRs); $td = $date[$key]; if (isDebtors($dtaccid[$key])) { # Select customer db_connect(); $sql = "SELECT * FROM customers WHERE cusnum = '{$dcusnum[$key]}' AND div = '" . USER_DIV . "'"; $custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF); if (pg_numrows($custRslt) < 1) { return "<li> Invalid Customer ID.</li>"; } else { $cust = pg_fetch_array($custRslt); } # Get department db_conn("exten"); $sql = "SELECT * FROM departments WHERE deptid = '{$cust['deptid']}' AND div = '" . USER_DIV . "'"; $deptRslt = db_exec($sql); if (pg_numrows($deptRslt) < 1) { return "<i class='err'>Department Not Found</i>"; } else { $dept = pg_fetch_array($deptRslt); } db_connect(); # Begin updates pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF); $sdate = date("Y-m-d"); # record the payment on the statement $sql = "\n\t\t\t\t\tINSERT INTO stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, type, st, div, allocation_date\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cust['cusnum']}', '0', '{$amount[$key]}', '{$td}', '{$descript[$key]}', 'n', '" . USER_DIV . "', '{$td}'\n\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF); $sql = "\n\t\t\t\t\tINSERT INTO open_stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, balance, date, \n\t\t\t\t\t\ttype, st, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cust['cusnum']}', '0', '{$amount[$key]}', '{$amount[$key]}', '{$td}', \n\t\t\t\t\t\t'{$descript[$key]}', 'n', '" . USER_DIV . "'\n\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF); # update the customer (make balance more) $sql = "UPDATE customers SET balance = (balance + '{$amount[$key]}') WHERE cusnum = '{$cust['cusnum']}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF); # Commit updates pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF); # Make ledge record custledger($cust['cusnum'], $ctaccid[$key], $td, $refnum[$key], $descript[$key], $amount[$key], "d"); custDT($amount[$key], $cust['cusnum'], $td); $dtaccid[$key] = $dept['debtacc']; $descript[$key] = $descript[$key] . " - Customer {$cust['surname']}"; } elseif (isCreditors($dtaccid[$key])) { # Select supplier db_connect(); $sql = "SELECT * FROM suppliers WHERE supid = '{$dsupid[$key]}' AND div = '" . USER_DIV . "'"; $suppRslt = db_exec($sql) or errDie("Unable to access databse.", SELF); if (pg_numrows($suppRslt) < 1) { return "<li> Invalid Supplier ID.</li>"; } else { $supp = pg_fetch_array($suppRslt); } # Get department db_conn("exten"); $sql = "SELECT * FROM departments WHERE deptid = '{$supp['deptid']}' AND div = '" . USER_DIV . "'"; $deptRslt = db_exec($sql); if (pg_numrows($deptRslt) < 1) { return "<i class='err'>Department Not Found</i>"; } else { $dept = pg_fetch_array($deptRslt); } db_connect(); # Begin updates pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF); $edate = date("Y-m-d"); # record the payment on the statement $sql = "\n\t\t\t\t\tINSERT INTO sup_stmnt (\n\t\t\t\t\t\tsupid, edate, ref, cacc, descript, amount, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$supp['supid']}', '{$td}', '0', '{$ctaccid[$key]}', '{$descript[$key]}', '-{$amount[$key]}', '" . USER_DIV . "'\n\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF); # update the supplier (make balance more) $sql = "UPDATE suppliers SET balance = (balance - '{$amount[$key]}') WHERE supid = '{$supp['supid']}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to update supplier in Cubit.", SELF); # Commit updates pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF); # Ledger Records suppledger($supp['supid'], $ctaccid[$key], $td, $refnum[$key], $descript[$key], $amount[$key], 'd'); suppDT($amount[$key], $supp['supid'], $td); $dtaccid[$key] = $dept['credacc']; $descript[$key] = $descript[$key] . " - Supplier {$supp['supname']}"; } elseif (isStock($dtaccid[$key])) { # Select Stock db_connect(); $sql = "SELECT * FROM stock WHERE stkid = '{$dstkids[$key]}' AND div = '" . USER_DIV . "'"; $stkRslt = db_exec($sql) or errDie("Unable to access databse.", SELF); if (pg_numrows($stkRslt) < 1) { return "<li> Invalid Stock ID.</li>"; } else { $stk = pg_fetch_array($stkRslt); } # Get warehouse name db_conn("exten"); $sql = "SELECT * FROM warehouses WHERE whid = '{$stk['whid']}' AND div = '" . USER_DIV . "'"; $whRslt = db_exec($sql); $wh = pg_fetch_array($whRslt); # Update Stock db_connect(); $sql = "UPDATE stock SET csamt = (csamt + '{$amount[$key]}') WHERE stkid = '{$stk['stkid']}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF); $sdate = date("Y-m-d"); # stkid, stkcod, stkdes, trantype, edate, qty, csamt, details stockrec($stk['stkid'], $stk['stkcod'], $stk['stkdes'], 'dt', $td, 0, $amount[$key], "Stock Debit Transaction"); db_connect(); $dtaccid[$key] = $wh['stkacc']; } if (isDebtors($ctaccid[$key])) { # Select customer db_connect(); $sql = "SELECT * FROM customers WHERE cusnum = '{$ccusnum[$key]}' AND div = '" . USER_DIV . "'"; $custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF); if (pg_numrows($custRslt) < 1) { return "<li> Invalid Customer ID.</li>"; } else { $cust = pg_fetch_array($custRslt); } # Get department db_conn("exten"); $sql = "SELECT * FROM departments WHERE deptid = '{$cust['deptid']}' AND div = '" . USER_DIV . "'"; $deptRslt = db_exec($sql); if (pg_numrows($deptRslt) < 1) { return "<i class='err'>Department Not Found</i>"; } else { $dept = pg_fetch_array($deptRslt); } db_connect(); # Begin updates pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF); $sdate = date("Y-m-d"); # record the payment on the statement $sql = "\n\t\t\t\t\tINSERT INTO stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, type, st, div, allocation_date\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cust['cusnum']}', '0', '-{$amount[$key]}', '{$td}', '{$descript[$key]}', 'n', '" . USER_DIV . "', '{$td}'\n\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF); $sql = "\n\t\t\t\t\tINSERT INTO open_stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, balance, date, \n\t\t\t\t\t\ttype, st, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cust['cusnum']}', '0', '-{$amount[$key]}', '-{$amount[$key]}', '{$td}', \n\t\t\t\t\t\t'{$descript[$key]}', 'n', '" . USER_DIV . "'\n\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF); # update the customer (make balance more) $sql = "UPDATE customers SET balance = (balance - '{$amount[$key]}') WHERE cusnum = '{$cust['cusnum']}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF); # Commit updates pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF); # Make ledge record custledger($cust['cusnum'], $dtaccid[$key], $sdate, $refnum[$key], $descript[$key], $amount[$key], "c"); custCT($amount[$key], $cust['cusnum'], $td); $ctaccid[$key] = $dept['debtacc']; $descript[$key] = $descript[$key] . " - Customer {$cust['surname']}"; } elseif (isCreditors($ctaccid[$key])) { # Select supplier db_connect(); $sql = "SELECT * FROM suppliers WHERE supid = '{$csupid[$key]}' AND div = '" . USER_DIV . "'"; $suppRslt = db_exec($sql) or errDie("Unable to access databse.", SELF); if (pg_numrows($suppRslt) < 1) { return "<li> Invalid Supplier ID.</li>"; } else { $supp = pg_fetch_array($suppRslt); } # Get department db_conn("exten"); $sql = "SELECT * FROM departments WHERE deptid = '{$supp['deptid']}' AND div = '" . USER_DIV . "'"; $deptRslt = db_exec($sql); if (pg_numrows($deptRslt) < 1) { return "<i class='err'>Department Not Found</i>"; } else { $dept = pg_fetch_array($deptRslt); } db_connect(); # Begin updates pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF); $edate = date("Y-m-d"); # record the payment on the statement $sql = "\n\t\t\t\t\tINSERT INTO sup_stmnt (\n\t\t\t\t\t\tsupid, edate, ref, cacc, descript, amount, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$supp['supid']}', '{$td}', '0', '{$dtaccid[$key]}', '{$descript[$key]}', '{$amount[$key]}', '" . USER_DIV . "'\n\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF); # update the supplier (make balance more) $sql = "UPDATE suppliers SET balance = (balance + '{$amount[$key]}') WHERE supid = '{$supp['supid']}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to update supplier in Cubit.", SELF); # Commit updates pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF); # Ledger Records suppledger($supp['supid'], $dtaccid[$key], $edate, $refnum[$key], $descript[$key], $amount[$key], 'c'); suppCT($amount[$key], $supp['supid'], $td); $ctaccid[$key] = $dept['credacc']; $descript[$key] = $descript[$key] . " - Supplier {$supp['supname']}"; } elseif (isStock($ctaccid[$key])) { # Select Stock db_connect(); $sql = "SELECT * FROM stock WHERE stkid = '{$cstkids[$key]}' AND div = '" . USER_DIV . "'"; $stkRslt = db_exec($sql) or errDie("Unable to access databse.", SELF); if (pg_numrows($stkRslt) < 1) { return "<li> Invalid Stock ID.</li>"; } else { $stk = pg_fetch_array($stkRslt); } # Get warehouse name db_conn("exten"); $sql = "SELECT * FROM warehouses WHERE whid = '{$stk['whid']}' AND div = '" . USER_DIV . "'"; $whRslt = db_exec($sql); $wh = pg_fetch_array($whRslt); # Update Stock db_connect(); $sql = "UPDATE stock SET csamt = (csamt + '{$amount[$key]}') WHERE stkid = '{$stk['stkid']}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF); $sdate = date("Y-m-d"); # stkid, stkcod, stkdes, trantype, edate, qty, csamt, details stockrec($stk['stkid'], $stk['stkcod'], $stk['stkdes'], 'ct', $td, 0, $amount[$key], "Stock Credit Transaction"); db_connect(); $ctaccid[$key] = $wh['stkacc']; } # write transaction writetrans($dtaccid[$key], $ctaccid[$key], $date[$key], $refnum[$key], $amount[$key], $descript[$key]); } // Layout $write = "\n\t\t<center>\n\t\t<h3>Journal transactions have been recorded</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Date</th>\n\t\t\t\t<th>Ref num</th>\n\t\t\t\t<th>Debit</th>\n\t\t\t\t<th>Credit</th>\n\t\t\t\t<th>Amount</th>\n\t\t\t\t<th>Description</th>\n\t\t\t</tr>"; foreach ($amount as $key => $value) { if ($value <= 0) { continue; } if (isDebtors($dtaccid[$key])) { $dcusRs = get("cubit", "*", "customers", "cusnum", $dcusnum[$key]); $dcus = pg_fetch_array($dcusRs); $dt = "<td valign='center'>{$dcus['accno']} - {$dcus['cusname']} {$dcus['surname']}</td>"; } elseif (isCreditors($dtaccid[$key])) { $dsupRs = get("cubit", "*", "suppliers", "supid", $dsupid[$key]); $dsup = pg_fetch_array($dsupRs); $dt = "<td valign='center'>{$dsup['supno']} - {$dsup['supname']}</td>"; } elseif (isStock($dtaccid[$key])) { $dstkRs = get("cubit", "*", "stock", "stkid", $dstkids[$key]); $dstk = pg_fetch_array($dstkRs); $dt = "<td valign='center'>{$dstk['stkcod']} - {$dstk['stkdes']}</td>"; } else { $dt = "<td valign='center'>" . $dtacc[$key]['accname'] . "</td>"; } if (isDebtors($ctaccid[$key])) { $ccusRs = get("cubit", "*", "customers", "cusnum", $ccusnum[$key]); $ccus = pg_fetch_array($ccusRs); $ct = "<td valign='center'>{$ccus['accno']} - {$ccus['cusname']} {$ccus['surname']}</td>"; } elseif (isCreditors($ctaccid[$key])) { $csupRs = get("cubit", "*", "suppliers", "supid", $csupid[$key]); $csup = pg_fetch_array($csupRs); $ct = "<td valign='center'>{$csup['supno']} - {$csup['supname']}</td>"; } elseif (isStock($ctaccid[$key])) { $cstkRs = get("cubit", "*", "stock", "stkid", $cstkids[$key]); $cstk = pg_fetch_array($cstkRs); $ct = "<td valign='center'>{$cstk['stkcod']} - {$cstk['stkdes']}</td>"; } else { $ct = "<td valign='center'>" . $ctacc[$key]['accname'] . "</td>"; } $write .= "\n\t\t\t\t<tr bgcolor=" . bgcolorg() . ">\n\t\t\t\t\t<td>{$date[$key]}</td>\n\t\t\t\t\t<td>{$refnum[$key]}</td>\n\t\t\t\t\t{$dt}\n\t\t\t\t\t{$ct}\n\t\t\t\t\t<td>" . CUR . " {$amount[$key]}</td>\n\t\t\t\t\t<td>{$descript[$key]}</td>\n\t\t\t\t</tr>"; } $write .= "\n\t\t</table>\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . " width='25%'>\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 href='../reporting/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='multi-trans.php'>Journal Transactions</td>\n\t\t\t</tr>\n\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t</table>"; return $write; }
# # # # # # # # # # Get settings require "../settings.php"; require "../core-settings.php"; if (isset($_POST["key"])) { switch ($_POST["key"]) { case "slctacc": $OUTPUT = slctacc($_POST); break; case "viewtran": $OUTPUT = viewtran($_POST); break; default: $OUTPUT = "Invalid."; } } else { $OUTPUT = select_year(); } $OUTPUT .= "\n\t<p>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr><td><br></td></tr>\n\t\t<tr>\n\t\t\t<th>Quick Links</th>\n\t\t</tr>\n\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>"; # Get templete require "../template.php"; function select_year() {
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='« 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 = " "; $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> </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; }
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> </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> </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> </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 = " "; $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'> </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; }
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> </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> </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; }
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='« 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 = " "; $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); }
function confirm($_POST, $err = "") { extract($_POST); if (isset($back)) { if (isset($vat)) { return details($_POST); } else { return slctacc(); } } # Redirect if must chrgvat if ($chrgvat == 'yes' && !isset($vataccid)) { return slctVatacc($_POST); } if (isset($vatcode)) { $vatcode += 0; } else { $vatcode = 0; } # validate input require_lib("validate"); $v = new validate(); $v->isOk($ctaccid, "num", 1, 50, "Invalid Account to be Credited."); $v->isOk($dtaccid, "num", 1, 50, "Invalid Account to be Debited."); $v->isOk($refnum, "num", 1, 10, "Invalid Reference number."); $v->isOk($amount, "float", 1, 20, "Invalid Amount."); $v->isOk($details, "string", 0, 255, "Invalid Details."); $v->isOk($author, "string", 1, 30, "Invalid Authorising person name."); $v->isOk($chrgvat, "string", 1, 4, "Invalid charge vat option."); if ($chrgvat == 'yes') { $v->isOk($vataccid, "num", 1, 50, "Invalid VAT Account number."); $v->isOk($vatdedacc, "num", 1, 50, "Invalid VAT Deductable Account number."); $v->isOk($vatinc, "string", 1, 3, "Invalid vat inclusive selection."); } $v->isOk($date, "date", 1, 1, "Invalid date."); if ($v->isError()) { $err = $v->genErrors(); if (isset($details)) { return details($_POST, $err); } else { return details2($_POST, $err); } } $amount = sprint($amount); if ($chrgvat == 'yes') { $vataccRs = get("core", "*", "accounts", "accid", $vataccid); $vatacc = pg_fetch_array($vataccRs); $vatin = ucwords($vatinc); //$VATP = TAX_VAT; db_conn('cubit'); $Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'"; $Ri = db_exec($Sl); $vd = pg_fetch_array($Ri); $VATP = $vd['vat_amount']; # if vat must be charged if ($vatinc == "no") { $vatamt = sprint($VATP / 100 * $amount); $totamt = sprint($amount + $vatamt); } else { $vatamt = sprint($amount / ($VATP + 100) * $VATP); $totamt = sprint($amount); } $vataccnum = "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>VAT Amount</td>\n\t\t\t\t\t\t\t<td><input type='hidden' name='vatinc' value='{$vatinc}'><input type='hidden' name='vatamt' value='{$vatamt}'>{$vatamt}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Total Transaction Amount</td>\n\t\t\t\t\t\t\t<td>{$totamt}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>VAT Account</td>\n\t\t\t\t\t\t\t<td><input type='hidden' name='vataccid' value='{$vataccid}'><input type='hidden' name='vatdedacc' value='{$vatdedacc}'>{$vatacc['topacc']}/{$vatacc['accnum']} - {$vatacc['accname']}</td>\n\t\t\t\t\t\t</tr>"; } else { $vataccnum = ""; } $dtaccRs = get("core", "*", "accounts", "accid", $dtaccid); $dtacc = pg_fetch_array($dtaccRs); $ctaccRs = get("core", "*", "accounts", "accid", $ctaccid); $ctacc = pg_fetch_array($ctaccRs); if (isb($dtaccid)) { return slctacc("<li class='err'>You selected a main account for debit account.</li>"); } if (isb($ctaccid)) { return slctacc("<li class='err'>You selected a main account for credit account.</li>"); } if ($vatcode > 0) { db_conn('cubit'); $Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'"; $Ri = db_exec($Sl) or errDie("unable to get data."); $va = pg_fetch_array($Ri); $vd = "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>VAT Code</td>\n\t\t\t\t\t<td>{$va['code']}</td>\n\t\t\t\t</tr>"; } else { $vd = ""; } $confirm = "\n\t\t\t\t\t<h3>Record Journal transaction</h3>\n\t\t\t\t\t{$err}\n\t\t\t\t\t<h4>Confirm entry</h4>\n\t\t\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t\t\t<input type='hidden' name='key' value='write' />\n\t\t\t\t\t\t<input type='hidden' name='vatcode' value='{$vatcode}' />\n\t\t\t\t\t\t<input type='hidden' name='dtaccid' value='{$dtaccid}' />\n\t\t\t\t\t\t<input type='hidden' name='ctaccid' value='{$ctaccid}' />\n\t\t\t\t\t\t<input type='hidden' name='dtaccname' value='{$dtacc['accname']}' />\n\t\t\t\t\t\t<input type='hidden' name='ctaccname' value='{$ctacc['accname']}' />\n\t\t\t\t\t\t<input type='hidden' name='date' value='{$date}' />\n\t\t\t\t\t\t<input type='hidden' name='refnum' value='{$refnum}' />\n\t\t\t\t\t\t<input type='hidden' name='amount' value='{$amount}' />\n\t\t\t\t\t\t<input type='hidden' name='chrgvat' value='{$chrgvat}' />\n\t\t\t\t\t\t<input type='hidden' name='details' value='{$details}' />\n\t\t\t\t\t\t<input type='hidden' name='author' value='{$author}' />\n\t\t\t\t\t\t<input type='hidden' name='jr_day' value='{$jr_day}' />\n\t\t\t\t\t\t<input type='hidden' name='jr_month' value='{$jr_month}' />\n\t\t\t\t\t\t<input type='hidden' name='jr_year' value='{$jr_year}' />\n\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td width='50%'><h3>Debit</h3></td>\n\t\t\t\t\t\t\t<td width='50%'><h3>Credit</h3></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$dtacc['topacc']}/{$dtacc['accnum']} - {$dtacc['accname']}</td>\n\t\t\t\t\t\t\t<td>{$ctacc['topacc']}/{$ctacc['accnum']} - {$ctacc['accname']}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Date</td>\n\t\t\t\t\t\t\t<td>{$date}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Reference number</td>\n\t\t\t\t\t\t\t<td>{$refnum}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Amount</td>\n\t\t\t\t\t\t\t<td>{$amount}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Charge VAT </td>\n\t\t\t\t\t\t\t<td>{$chrgvat}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t{$vataccnum}\n\t\t\t\t\t\t{$vd}\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Details</td>\n\t\t\t\t\t\t\t<td>{$details}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Authorising Person</td>\n\t\t\t\t\t\t\t<td>{$author}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td><input type='submit' name='back' value='« Correction'></td>\n\t\t\t\t\t\t\t<td align='right'><input type='submit' value='Write »'></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>\n\t\t\t\t\t</form>" . mkQuickLinks(ql("../reporting/index-reports.php", "Financials"), ql("../core/trans-new.php", "Journal Transactions")); return $confirm; }
case "write": $OUTPUT = write($_POST); break; case "details": if (isset($_POST['details'])) { $OUTPUT = details($_POST); } else { $OUTPUT = details2($_POST); } break; default: $OUTPUT = slctacc(); } } else { # Display default output $OUTPUT = slctacc(); } # get templete require "../template.php"; # Select Accounts function slctacc() { # get last ref number $refnum = getrefnum(); /*refnum*/ // Accounts (debit) $view = "<center>\n <h3> Journal transaction On Previous Periods </h3>\n <br><br>\n <form action='" . SELF . "' method=post name=form>\n <input type=hidden name=key value=details>\n <table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' align=center>\n <tr><th>Field</th><th>Value</th></tr>\n <tr class='bg-odd'><td>Date</td><td><input type=text size=2 name=day maxlength=2 value='" . date("d") . "'>-<input type=text size=2 name=mon maxlength=2 value='" . date("m") . "'>-<input type=text size=4 name=year maxlength=4 value='" . date("Y") . "'></td></tr>\n <tr class='bg-odd'><td>Reference Number</td><td><input type=text size=10 name=refnum value='" . $refnum++ . "'></td></tr>\n\t\t<tr class='bg-even'><td>Select Period </td><td><select name=prd>"; db_conn(YR_DB); $sql = "SELECT * FROM info WHERE prdname !='' AND prddb < '" . PRD_DB . "'"; $prdRslt = db_exec($sql); $rows = pg_numrows($prdRslt);
function confirm($_POST) { # Get vars extract($_POST); if (isset($back)) { return slctacc($_POST); } # validate input require_lib("validate"); $v = new validate(); $v->isOk($cusnum, "num", 1, 50, "Invalid Customer number."); $v->isOk($accid, "num", 1, 50, "Invalid Contra Account."); $v->isOk($refnum, "num", 1, 10, "Invalid Reference number."); $v->isOk($amount, "float", 1, 20, "Invalid Amount."); $v->isOk($details, "string", 0, 255, "Invalid Details."); $v->isOk($author, "string", 1, 30, "Invalid Authorising person name."); $datea = explode("-", $date); if (count($datea) == 3) { if (!checkdate($datea[1], $datea[0], $datea[2])) { $v->isOk($date, "num", 1, 1, "Invalid date."); } } else { $v->isOk($date, "num", 1, 1, "Invalid date."); } # display errors, if any if ($v->isError()) { $confirm = ""; $errors = $v->getErrors(); foreach ($errors as $e) { $confirm .= "<li class='err'>" . $e["msg"] . "</li>"; } if ($type == 1) { return $confirm . details($_POST); } else { $_POST["accnum"] = $ac; return $confirm . details2($_POST); } } # Get contra account details $accRs = get("core", "*", "accounts", "accid", $accid); $acc = pg_fetch_array($accRs); # Select customer db_connect(); $sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'"; $custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF); if (pg_numrows($custRslt) < 1) { return "<li class='err'>Invalid customer ID, or customer has been blocked.</li>"; } else { $cust = pg_fetch_array($custRslt); } # Probe tran type if ($entry == "CT") { $tran = "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t\t\t<td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t\t\t</tr>"; } else { $tran = "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t\t</tr>"; } if (!isset($ac)) { $ac = ""; } // Layout $confirm = "\n \t\t\t\t<h3>Record Journal transaction</h3>\n\t\t\t\t\t\t<h4>Confirm entry</h4>\n\t\t\t\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t\t\t\t<input type='hidden' name='key' value='write'>\n\t\t\t\t\t\t\t<input type='hidden' name='cusnum' value='{$cusnum}'>\n\t\t\t\t\t\t\t<input type='hidden' name='accid' value='{$accid}'>\n\t\t\t\t\t\t\t<input type='hidden' name='accname' value='{$acc['accname']}'>\n\t\t\t\t\t\t\t<input type='hidden' name='date' value='{$date}'>\n\t\t\t\t\t\t\t<input type='hidden' name='refnum' value='{$refnum}'>\n\t\t\t\t\t\t\t<input type='hidden' name='entry' value='{$entry}'>\n\t\t\t\t\t\t\t<input type='hidden' name='amount' value='{$amount}'>\n\t\t\t\t\t\t\t<input type='hidden' name='details' value='{$details}'>\n\t\t\t\t\t\t\t<input type='hidden' name='author' value='{$author}'>\n\t\t\t\t\t\t\t<input type='hidden' name='type' value='{$type}'>\n\t\t\t\t\t\t\t<input type='hidden' name='ct_day' value='{$ct_day}'>\n\t\t\t\t\t\t\t<input type='hidden' name='ct_month' value='{$ct_month}'>\n\t\t\t\t\t\t\t<input type='hidden' name='ct_year' value='{$ct_year}'>\n\t\t\t\t\t\t\t<input type='hidden' name='ac' value='{$ac}'>\n\t\t\t\t\t\t<table " . TMPL_tblDflts . " width='500'>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<td width='50%'><h3>Debit</h3></td>\n\t\t\t\t\t\t\t\t<td width='50%'><h3>Credit</h3></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t{$tran}\n\t\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Date</td>\n\t\t\t\t\t\t\t\t<td>{$date}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Reference number</td>\n\t\t\t\t\t\t\t\t<td>{$refnum}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Amount</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$amount}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Details</td>\n\t\t\t\t\t\t\t\t<td>{$details}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>Authorising Person</td>\n\t\t\t\t\t\t\t\t<td>{$author}</td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t<td><input type='submit' name='back' value='« Correction'></td>\n\t\t\t\t\t\t\t\t<td align='right'><input type='submit' value='Write »'></td>\n\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t</table>\n\t\t\t\t\t\t</form>" . mkQuickLinks(ql("../core/trans-new.php", "Journal Transactions"), ql("../customers-view.php", "View Customers")); return $confirm; }
if (isset($_POST['details'])) { $OUTPUT = details($_POST); } else { $OUTPUT = details2($_POST); } break; default: if (isset($_GET['cusnum'])) { $OUTPUT = slctacc($_GET); } else { $OUTPUT = "<li> - Invalid use of module"; } } } else { if (isset($_GET['cusnum'])) { $OUTPUT = slctacc($_GET); } else { $OUTPUT = "<li> - Invalid use of module"; } } # Get templete require "template.php"; # Select Accounts function slctacc($_GET) { extract($_GET); # validate input require_lib("validate"); $v = new validate(); $v->isOk($cusnum, "num", 1, 50, "Invalid customer id."); # display errors, if any
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='« 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 = " "; $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; }
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='« 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> </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> </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 = " "; $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; }
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='« 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> </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'> </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'> </td>\n\t\t\t\t<td> </td>\n\t\t\t</tr>\n\t\t\t" . TBL_BR; } } $sp = " "; $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); }