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($details)) {
$v->isOk($accid, "string", 1, 20, "Invalid Account number.");
} else {
$v->isOk($topacc, "num", 1, 20, "Invalid Account number.");
$v->isOk($accnum, "num", 0, 20, "Invalid Account number.");
}
# 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;
}
if (isset($details)) {
$acc = qryAccounts($accid);
} else {
if (strlen($accnum) < 2) {
$acc = qryAccountsNum($topacc, "000");
if ($acc === false) {
return "<li> Accounts number {$topacc}/000 does not exist";
}
} else {
$acc = qryAccountsNum($topacc, $accnum);
if ($acc === false) {
return "<li> Accounts number {$topacc}/{$accnum} does not exist";
}
$acc = pg_fetch_array($accRs);
}
}
db_conn($prd);
// Set up table to display in
$OUTPUT = "<center>\n\t<h3>Journal Entries for Account : {$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</h3>\n\t<table " . TMPL_tblDflts . ">\n\t<tr>\n\t\t<th>Date</th>\n\t\t<th>Reference</th>\n\t\t<th>Contra Acc</th>\n\t\t<th>Description</th>\n\t\t<th>Debit</th>\n\t\t<th>Credit</th>\n\t\t<th>User</th>\n\t</tr>";
$sql = "SELECT * FROM transect WHERE debit = '{$acc['accid']}' OR credit = '{$acc['accid']}'";
$tranRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve Transaction details from database.", SELF);
if (pg_numrows($tranRslt) < 1) {
$OUTPUT .= "<tr class='bg-odd'><td colspan=10>No Transactions found</td></tr>";
} else {
$credtot = 0;
$debtot = 0;
# display all transactions
while ($tran = pg_fetch_array($tranRslt)) {
extract($tran);
if ($debit == $accid) {
$cacc = $credit;
$debitamt = CUR . " {$amount}";
$debtot += $amount;
$creditamt = "";
} else {
$debitamt = "";
$creditamt = CUR . " {$amount}";
$credtot += $amount;
$cacc = $debit;
}
# get contra account name
$caccRs = get("core", "accname,topacc,accnum", "accounts", "accid", $cacc);
$cacc = pg_fetch_array($caccRs);
$OUTPUT .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$date}</td>\n\t\t\t\t<td>{$refnum}</td>\n\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\t\t\t\t<td>{$details}</td>\n\t\t\t\t<td>{$debitamt}</td>\n\t\t\t\t<td>{$creditamt}</td>\n\t\t\t\t<td>{$author}</td>\n\t\t\t</tr>";
}
}
if ($acc["acctype"] == "I") {
$accbal = $credtot - $debtot;
} else {
if ($acc["acctype"] == "E") {
$accbal = $debtot - $credtot;
} else {
if ($acc["acctype"] == "B") {
$accbal = $debtot - $credtot;
} else {
return "<li class=err>Account number is beyond limits.";
}
}
}
$OUTPUT .= "\n\t<tr class='" . bg_class() . "'>\n\t\t<td colspan='4'><b>Total</b></td>\n\t\t<td><b>" . CUR . " {$debtot}</b></td>\n\t\t<td><b>" . CUR . " {$credtot}</b></td>\n\t\t<td> </td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td colspan='4'><b>Balance</b></td>\n\t\t<td colspan='2'><b>" . CUR . " {$accbal}</b></td>\n\t\t<td> </td>\n\t</tr>\n\t</table>" . mkQuickLinks();
return $OUTPUT;
}
function details2($_POST, $err = "")
{
extract($_POST);
require_lib("validate");
$v = new validate();
$v->isOk($refnum, "num", 1, 10, "Invalid Reference number.");
$v->isOk($jr_day, "num", 1, 2, "Invalid to Date day.");
$v->isOk($jr_month, "num", 1, 2, "Invalid to Date month.");
$v->isOk($jr_year, "num", 1, 4, "Invalid to Date Year.");
$date = mkdate($jr_year, $jr_month, $jr_day);
$v->isOk($date, "date", 1, 1, "Invalid date.");
$v->isOk($dtaccnum, "string", 1, 50, "Invalid Account number to be Debited.");
$v->isOk($ctaccnum, "string", 1, 50, "Invalid Account number to be Credited.");
if ($v->isError()) {
$err = $v->genErrors();
return slctacc($err);
}
$dtaccnum = explode("/", rtrim($dtaccnum));
$ctaccnum = explode("/", rtrim($ctaccnum));
if (count($dtaccnum) < 2) {
$dtacc = qryAccountsNum($dtaccnum[0], "000");
} else {
$dtacc = qryAccountsNum($dtaccnum[0], $dtaccnum[1]);
}
if (count($ctaccnum) < 2) {
$ctacc = qryAccountsNum($ctaccnum[0], "000");
} else {
$ctacc = qryAccountsNum($ctaccnum[0], $ctaccnum[1]);
}
if (Control($dtacc['accid'], $ctacc['accid'], $refnum, $jr_day, $jr_month, $jr_year)) {
return Control($dtacc['accid'], $ctacc['accid'], $refnum, $jr_day, $jr_month, $jr_year);
}
if (!isset($amount)) {
$amount = "";
$details = "";
$chrgvat = "";
}
if ($chrgvat == "yes") {
$c1 = "checked=yes";
$c2 = "";
} else {
$c1 = "";
$c2 = "checked=yes";
}
$OUT = "\n\t\t\t\t<h3>Journal transaction details</h3>\n\t\t\t\t<form action='" . SELF . "' method='POST' name='form'>\n\t\t\t\t\t<input type='hidden' name='key' value='confirm' />\n\t\t\t\t\t<input type='hidden' name='date' value='{$date}' />\n\t\t\t\t\t<input type='hidden' name='ctaccid' value='{$ctacc['accid']}' />\n\t\t\t\t\t<input type='hidden' name='dtaccid' value='{$dtacc['accid']}' />\n\t\t\t\t\t<input type='hidden' name='jr_day' value='{$jr_day}' />\n\t\t\t\t\t<input type='hidden' name='jr_month' value='{$jr_month}' />\n\t\t\t\t\t<input type='hidden' name='jr_year' value='{$jr_year}' />\n\t\t\t\t<table " . TMPL_tblDflts . " width='500'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td width='50%'><h3>Debit</h3></td>\n\t\t\t\t\t\t<td width='50%'><h3>Credit</h3></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>{$dtacc['topacc']}/{$dtacc['accnum']} - {$dtacc['accname']}</td>\n\t\t\t\t\t\t<td>{$ctacc['topacc']}/{$ctacc['accnum']} - {$ctacc['accname']}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Date</td>\n\t\t\t\t\t\t<td valign='center'>{$date}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Reference No.</td>\n\t\t\t\t\t\t<td valign='center'><input type='text' size='20' name='refnum' value='{$refnum}'></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Amount</td>\n\t\t\t\t\t\t<td valign='center'>" . CUR . "<input type='text' size='20' name='amount' value='{$amount}'></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Charge VAT </td>\n\t\t\t\t\t\t<td><input type='radio' name='chrgvat' value='yes' {$c1}>Yes <input type='radio' name='chrgvat' value='no' {$c2}>No</td>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Transaction Details</td>\n\t\t\t\t\t\t<td valign='center'><textarea cols='20' rows='5' name='details'>{$details}</textarea></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Person Authorising</td>\n\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</tr>\n\t\t\t\t\t" . TBL_BR . "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td><input type='submit' name='back' value='« Correction'></td>\n\t\t\t\t\t\t<td valign='center'><input type='submit' value='Confirm »'></td>\n\t\t\t\t\t</tr>\n\t\t\t\t</form>\n\t\t\t\t</table>" . mkQuickLinks(ql("../reporting/index-reports.php", "Financials"), ql("../core/trans-new.php", "Journal Transactions"));
return $OUT;
}
function cwrite($_GET)
{
$showvat = TRUE;
extract($_GET);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($invid, "num", 1, 20, "Invalid invoice number.");
if (isset($ctyp) && $ctyp == 's') {
$v->isOk($cusnum, "num", 1, 20, "Invalid customer number.");
} elseif (isset($ctyp) && $ctyp == 'c') {
$v->isOk($deptid, "num", 1, 20, "Invalid Department.");
}
if (isset($stkaccs)) {
foreach ($stkaccs as $key => $accid) {
$v->isOk($accid, "num", 1, 20, "Invalid Item Account number.");
}
} else {
$v->isOk($invid, "num", 0, 0, "Invalid Item Account number.");
}
# display errors, if any
if ($v->isError()) {
$err = $v->genErrors();
$err .= "<input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $err;
}
db_connect();
# Get invoice info
$sql = "SELECT * FROM nons_invoices WHERE invid = '{$invid}' AND div = '" . USER_DIV . "' and done='n'";
$invRslt = db_exec($sql) or errDie("Unable to get invoice information");
if (pg_numrows($invRslt) < 1) {
return "<i class='err'>Not Found</i>";
}
$inv = pg_fetch_array($invRslt);
$td = $inv['odate'];
db_connect();
# cust % bank
if ($ctyp == 's') {
$sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to view customer");
$cus = pg_fetch_array($custRslt);
$details = "\r\n\t\t<tr><td>{$cus['surname']}</td></tr>\r\n\t\t<tr><td>" . nl2br($cus['addr1']) . "</td></tr>\r\n\t\t<tr><td>VAT No. {$cus['vatnum']}</td></tr>\r\n\t\t<tr><td>Customer Order Number: {$inv['cordno']}</td></tr>";
$na = $cus['surname'];
} elseif ($ctyp == 'c') {
$cus['surname'] = $inv['cusname'];
$cus['addr1'] = $inv['cusaddr'];
$cus["del_addr1"] = "";
$cus["paddr1"] = "";
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$deptid}'";
$deptRslt = db_exec($sql) or errDie("Unable to view customers");
$dept = pg_fetch_array($deptRslt);
$details = "\r\n\t\t<tr><td>{$inv['cusname']}</td></tr>\r\n\t\t<tr><td>" . nl2br($inv['cusaddr']) . "</td></tr>\r\n\t\t<tr><td>VAT No. {$inv['cusvatno']}</td></tr>\r\n\t\t<tr><td>Customer Order Number: {$inv['cordno']}</td></tr>";
$na = $inv['cusname'];
} else {
$cus["del_addr1"] = "";
$cus["paddr1"] = "";
$cus['surname'] = $inv['cusname'];
$cus['addr1'] = $inv['cusaddr'];
$details = "\r\n\t\t<tr><td>{$inv['cusname']}</td></tr>\r\n\t\t<tr><td>" . nl2br($inv['cusaddr']) . "</td></tr>\r\n\t\t<tr><td>VAT No. {$inv['cusvatno']}</td></tr>\r\n\t\t<tr><td>Customer Order Number: {$inv['cordno']}</td></tr>";
$na = $inv['cusname'];
}
# Begin updates
$refnum = getrefnum();
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "NO VAT");
$varacc = gethook("accnum", "salesacc", "name", "sales_variance");
/* - End Hooks - */
//lock(2);
$real_invid = divlastid('inv', USER_DIV);
//unlock(2);
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
/* --- Start Products Display --- */
# Products layout
$products = "";
$disc = 0;
# get selected stock in this invoice
db_connect();
$sql = "SELECT * FROM nons_inv_items WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
# Put in product
$i = 0;
$page = 0;
while ($stk = pg_fetch_array($stkdRslt)) {
if ($i >= 25) {
$page++;
$i = 0;
}
$stkacc = $stk["accid"];
$Sl = "SELECT * FROM vatcodes WHERE id='{$stk['vatex']}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$vd = pg_fetch_array($Ri);
if ($vd['zero'] == "Yes") {
$stk['vatex'] = "y";
}
//print $inv['chrgvat'];exit;
if (TAX_VAT != $vd['vat_amount'] and $vd['vat_amount'] != "0.00") {
$showvat = FALSE;
}
$t = $inv['chrgvat'];
$VATP = TAX_VAT;
$hs_acc = qryAccountsNum(1050, 00);
$hs_acc = $hs_acc["accid"];
$inv_acc = qryAccountsNum(6350, 00);
$inv_acc = $inv_acc["accid"];
# keep records for transactions
if ($stkacc == $inv_acc) {
if (!isset($totstkamt[$stkacc])) {
$totstkamt[$stkacc] = 0;
}
$totstkamt[$stkacc] += $cost_prices[$stk["id"]];
$va = sprint($stk['amt'] - vats($stk['amt'], $inv['chrgvat'], $vd['vat_amount']));
if ($inv['chrgvat'] == "no") {
$va = sprint($stk['amt'] * $vd['vat_amount'] / 100);
}
$totstkamt[$hs_acc] += vats($stk['amt'], $inv['chrgvat'], $vd['vat_amount']);
} else {
if (isset($totstkamt[$stkacc])) {
# Is it stock sold?
if ($stk['vatex'] == "y") {
$totstkamt[$stkacc] += vats($stk['amt'], 'novat', $vd['vat_amount']);
$va = 0;
$inv['chrgvat'] = "";
} else {
$totstkamt[$stkacc] += vats($stk['amt'], $inv['chrgvat'], $vd['vat_amount']);
$va = sprint($stk['amt'] - vats($stk['amt'], $inv['chrgvat'], $vd['vat_amount']));
if ($inv['chrgvat'] == "no") {
$va = sprint($stk['amt'] * $vd['vat_amount'] / 100);
}
}
} else {
if ($stk['vatex'] == "y") {
$totstkamt[$stkacc] = $stk['amt'];
$inv['chrgvat'] = "";
$va = 0;
} else {
$totstkamt[$stkacc] = vats($stk['amt'], $inv['chrgvat'], $vd['vat_amount']);
$va = sprint($stk['amt'] - vats($stk['amt'], $inv['chrgvat'], $vd['vat_amount']));
if ($inv['chrgvat'] == "no") {
$va = sprint($stk['amt'] * $vd['vat_amount'] / 100);
}
}
}
}
vatr($vd['id'], $td, "OUTPUT", $vd['code'], $refnum, "Non-Stock Sales, invoice No.{$real_invid}", vats($stk['amt'], $inv['chrgvat'], $vd['vat_amount']) + $va, $va);
$inv['chrgvat'] = $t;
// if(isset($totstkamt[$stkacc])){
// $totstkamt[$stkacc] += vats($stk['amt'], $inv['chrgvat']);
// }else{
// $totstkamt[$stkacc] = vats($stk['amt'], $inv['chrgvat']);
// }
$sql = "UPDATE nons_inv_items SET accid = '{$stkacc}' WHERE id = '{$stk['id']}'";
$sRslt = db_exec($sql);
if ($stk['vatex'] == 'y') {
$ex = "#";
} else {
// $ex = " ";
$ex = "";
}
$i++;
}
/* --- Start Some calculations --- */
# Subtotal
$SUBTOT = sprint($inv['subtot']);
$VAT = sprint($inv['vat']);
$TOTAL = sprint($inv['total']);
/* --- End Some calculations --- */
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "novat");
/* - End Hooks - */
# todays date
$date = date("d-m-Y");
$sdate = date("Y-m-d");
if (isset($bankid)) {
$bankid += 0;
db_conn("cubit");
$sql = "SELECT * FROM bankacct WHERE bankid = '{$inv['accid']}'";
$deptRslt = db_exec($sql) or errDie("Unable to view customers");
if (pg_numrows($deptRslt) < 1) {
$error = "<li class='err'> Bank not Found.";
$confirm .= "{$error}<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
} else {
$deptd = pg_fetch_array($deptRslt);
}
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}'";
$rd = db_exec($Sl) or errDie("Unable to get data.");
$data = pg_fetch_array($rd);
$BA = $data['accnum'];
}
$tot_post = 0;
# bank % cust
if ($ctyp == 's') {
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cus['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
$dept['deptname'] = "<li class=err>Department not Found.";
} else {
$dept = pg_fetch_array($deptRslt);
}
$tpp = 0;
$hs_acc = qryAccountsNum(1050, 00);
$hs_acc = $hs_acc["accid"];
$inv_acc = qryAccountsNum(6350, 00);
$inv_acc = $inv_acc["accid"];
$coh_acc = qryAccountsNum(7200, 00);
$coh_acc = $coh_acc["accid"];
$cos_acc = qryAccountsNum(2150, 00);
$cos_acc = $cos_acc["accid"];
# record transaction from data
foreach ($totstkamt as $stkacc => $wamt) {
$use_acc = $coh_acc;
if ($stkacc == $hs_acc) {
$use_acc = $coh_acc;
} else {
if ($stkacc == $inv_acc) {
$use_acc = $cos_acc;
}
}
$tot_post += $wamt;
writetrans($use_acc, $stkacc, $td, $refnum, $wamt, "Non-Stock Sales on invoice No.{$real_invid} customer {$cus['surname']}.");
}
# Debit bank and credit the account involved
if ($VAT != 0) {
$tot_post += $VAT;
writetrans($coh_acc, $vatacc, $td, $refnum, $VAT, "Non-Stock Sales VAT received on invoice No.{$real_invid} customer {$cus['surname']}.");
}
$sdate = date("Y-m-d");
} else {
if (!isset($accountc)) {
$accountc = 0;
}
if (!isset($dept['pca'])) {
$accountc += 0;
$dept['pca'] = $accountc;
$dept['debtacc'] = $accountc;
}
if (isset($bankid)) {
$dept['pca'] = $BA;
}
$tpp = 0;
# record transaction from data
foreach ($totstkamt as $stkacc => $wamt) {
if (!isset($cust['surname'])) {
$cust['surname'] = $inv['cusname'];
$cust['addr1'] = $inv['cusaddr'];
}
# Debit Customer and Credit stock
$tot_post += $wamt;
writetrans($dept['pca'], $stkacc, $td, $refnum, $wamt, "Non-Stock Sales on invoice No.{$real_invid} customer {$cust['surname']}.");
}
if (isset($bankid)) {
db_connect();
$bankid += 0;
$sql = "\r\n\t\t\tINSERT INTO cashbook (bankid, trantype, date, name, descript, cheqnum, \r\n\t\t\t\tamount, vat, chrgvat, banked, accinv, div)\r\n\t\t\tVALUES (\r\n\t\t\t\t\t'{$bankid}', 'deposit', '{$td}', '{$inv['cusname']}', \r\n\t\t\t\t\t'Non-Stock Sales on invoice No.{$real_invid} customer {$inv['cusname']}', '0', \r\n\t\t\t\t\t'{$TOTAL}', '{$VAT}', '{$inv['chrgvat']}', 'no', '{$stkacc}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$sql = "UPDATE nons_invoices SET jobid='{$bankid}' WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$upRslt = db_exec($sql) or errDie("Unable to update invoice information");
}
# Debit bank and credit the account involved
if ($VAT != 0) {
$tot_post += $VAT;
writetrans($dept['pca'], $vatacc, $td, $refnum, $VAT, "Non-Stock Sales VAT received on invoice No.{$real_invid} customer {$cust['surname']}.");
}
$sdate = date("Y-m-d");
}
$tot_post = sprint($tot_post);
db_connect();
if ($ctyp == 's') {
$sql = "\r\n\t\tUPDATE nons_invoices SET balance=total, cusid='{$cusnum}', ctyp='{$ctyp}',\r\n\t\t\tcusname='{$cus['surname']}', cusaddr='{$cus['addr1']}', cusvatno='{$cus['vatnum']}',\r\n\t\t\tdone='y', invnum='{$real_invid}'\r\n\t\tWHERE invid='{$invid}' AND div='" . USER_DIV . "'";
$upRslt = db_exec($sql) or errDie("Unable to update invoice information");
/*
# Record the payment on the statement
$sql = "
INSERT INTO stmnt (
cusnum, invid, docref, amount, date,
type, div, allocation_date
) VALUES (
'$cusnum', '$real_invid', '$inv[docref]', '$TOTAL', '$inv[odate]',
'Non-Stock Invoice', '".USER_DIV."', '$inv[odate]'
)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.",SELF);
# Record the payment on the statement
$sql = "
INSERT INTO open_stmnt (
cusnum, invid, docref, amount, balance,
date, type, div
) VALUES (
'$cusnum', '$real_invid', '$inv[docref]', '$TOTAL', '$TOTAL',
'$inv[sdate]', 'Non-Stock Invoice', '".USER_DIV."'
)";
$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 + '$TOTAL'::numeric(13,2)) WHERE cusnum = '$cusnum' AND div = '".USER_DIV."'";
# $rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.",SELF);
# Make ledge record
custledger($cusnum,$stkacc , $td, $real_invid, "Non Stock Invoice No. $real_invid", $TOTAL, "d");
custDT($TOTAL, $cusnum, $td, $invid, "nons");
*/
$tot_dif = sprint($tot_post - $TOTAL);
# if($tot_dif > 0) {
# writetrans($varacc,$dept['debtacc'], $td, $refnum, $tot_dif, "Sales Variance on invoice $real_invid");
# } elseif($tot_dif < 0) {
# $tot_dif = $tot_dif * -1;
# writetrans($dept['debtacc'],$varacc, $td, $refnum, $tot_dif, "Sales Variance on invoice $real_invid");
# }
} else {
$date = date("Y-m-d");
$sql = "\r\n\t\tUPDATE nons_invoices SET balance=total, cusname = '{$cust['surname']}',\r\n\t\t\taccid = '{$dept['pca']}', ctyp = '{$ctyp}', cusaddr = '{$cust['addr1']}',\r\n\t\t\tdone = 'y', invnum = '{$real_invid}'\r\n\t\tWHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$upRslt = db_exec($sql) or errDie("Unable to update invoice information");
# $tot_dif = sprint($tot_post - $TOTAL);
# if($tot_dif > 0) {
# writetrans($varacc,$dept['pca'], $td, $refnum, $tot_dif, "Sales Variance on invoice $real_invid");
# } elseif($tot_dif < 0) {
# $tot_dif = $tot_dif * -1;
# writetrans($dept['pca'],$varacc, $td, $refnum, $tot_dif, "Sales Variance on invoice $real_invid");
# }
}
db_connect();
$sql = "\r\n\tINSERT INTO salesrec (edate, invid, invnum, debtacc, vat, total, typ, div)\r\n\tVALUES ('{$inv['odate']}', '{$invid}', '{$real_invid}', '{$dept['debtacc']}', '{$VAT}', \r\n\t\t'{$TOTAL}', 'non', '" . USER_DIV . "')";
$recRslt = db_exec($sql);
com_invoice($inv['salespn'], $TOTAL - $VAT, 0, $real_invid, $inv["odate"]);
db_conn('cubit');
if (!isset($cusnum)) {
$cusnum = 0;
}
$Sl = "\r\n\tINSERT INTO sj (cid, name, des, date, exl, vat, inc, div)\r\n\tVALUES ('{$cusnum}', '{$na}', 'Non stock Invoice {$real_invid}', '{$inv['sdate']}',\r\n\t\t'" . sprint($TOTAL - $VAT) . "','{$VAT}','" . sprint($TOTAL) . "','" . USER_DIV . "')";
$Ri = db_exec($Sl);
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
# Get selected stock in this invoice
$sql = "SELECT * FROM nons_inv_items WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
# $stkdRslt = db_exec($sql);
$cc = "<script> CostCenter('dt', 'Sales', '{$inv['odate']}', 'Non Stock Invoice No.{$real_invid}', '" . ($TOTAL - $VAT) . "', ''); </script>";
db_conn('cubit');
$Sl = "SELECT * FROM settings WHERE constant='SALES'";
$Ri = db_exec($Sl) or errDie("Unable to get settings.");
$data = pg_fetch_array($Ri);
if ($data['value'] == "Yes") {
$sp = "<tr><td><b>Sales Person:</b> {$inv['salespn']}</td></tr>";
} else {
$sp = "";
}
if ($inv['chrgvat'] == "yes") {
$inv['chrgvat'] = "Inclusive";
} elseif ($inv['chrgvat'] == "no") {
$inv['chrgvat'] = "Exclusive";
} else {
$inv['chrgvat'] = "No vat";
}
if ($inv["remarks"] == "") {
db_conn("cubit");
$sql = "SELECT value FROM settings WHERE constant='DEFAULT_COMMENTS'";
$commRslt = db_exec($sql) or errDie("Unable to retrieve the default comments from Cubit.");
$inv["remarks"] = pg_fetch_result($commRslt, 0);
}
if (!isset($showvat)) {
$showvat = TRUE;
}
if ($showvat == TRUE) {
$vat14 = AT14;
} else {
$vat14 = "";
}
// Retrieve the company information
db_conn("cubit");
$sql = "SELECT * FROM compinfo";
$comp_rslt = db_exec($sql) or errDie("Unable to retrieve company information from Cubit.");
$comp_data = pg_fetch_array($comp_rslt);
#make sure we have a valid bank id for customer
if (!isset($inv['bankid']) or strlen($inv['bankid']) < 1) {
$inv['bankid'] = '2';
}
// Retrieve the banking information
db_conn("cubit");
$sql = "SELECT * FROM bankacct WHERE bankid='{$inv['bankid']}' AND div='" . USER_DIV . "'";
$bank_rslt = db_exec($sql) or errDie("Unable to retrieve bank information from Cubit.");
$bank_data = pg_fetch_array($bank_rslt);
}
function run()
{
extract($_REQUEST);
pglib_transaction("BEGIN");
// Retrieve outstanding rentals
$sql = "\n\tSELECT id, user_id, username, order_num, subtotal, vat, total, discount,\n\t\tdelivery, customers.cusnum, surname, addr1, addr2, addr3, accno,\n\t\tvatnum, tel, discount_perc, timestamp, deposit\n\tFROM cubit.hire_trans\n\t\tLEFT JOIN cubit.customers ON hire_trans.cusnum=customers.cusnum\n\t\tLEFT JOIN cubit.users ON hire_trans.user_id=users.userid\n\tWHERE user_id='{$user_id}' AND done='y'";
$rental_rslt = db_exec($sql) or errDie("Unable to retrieve rentals.");
$hire_nums = array();
while ($rental_data = pg_fetch_array($rental_rslt)) {
$deptid = 2;
$time = strtotime($rental_data["timestamp"]);
$sql = "SELECT deptname FROM exten.departments WHERE deptid='{$deptid}'";
$deptname_rslt = db_exec($sql) or errDie("Unable to retrieve department.");
$deptname = pg_fetch_result($deptname_rslt, 0);
// Create hire note
$sql = "\n\t\tINSERT INTO hire.hire_invoices (deptid, cusnum, cordno, ordno,\n\t\t\tchrgvat, terms, salespn, odate, printed, comm, done, username,\n\t\t\tdeptname, cusacc, cusname, surname, cusaddr, cusordno, cusvatno,\n\t\t\tprd, invnum, div, prints, disc, discp, delchrg, subtot, traddisc,\n\t\t\tbalance, vat, total, discount, delivery, nbal, rdelchrg, serd,\n\t\t\tpcash, pcheque, pcc, rounding, pchange, delvat, pcredit, vatnum,\n\t\t\ttelno, systime, deposit_type, deposit_amt, custom_txt, collection,\n\t\t\tbranch_addr, timestamp, hire_invid, revision)\n\t\tVALUES ('{$deptid}', '{$rental_data['cusnum']}', '', '', 'inc', '0', '2',\n\t\t\t'" . date("Y-m-d", $time) . "', 'y', '', 'y', '{$rental_data['username']}',\n\t\t\t'{$deptname}', '{$rental_data['accno']}', '', '{$rental_data['surname']}',\n\t\t\t'{$rental_data['addr1']}', '{$rental_data['order_num']}',\n\t\t\t'{$rental_data['vatnum']}', '" . PRD_DB . "', '{$rental_data['id']}',\n\t\t\t'" . USER_DIV . "', '0', '{$rental_data['discount']}',\n\t\t\t'{$rental_data['discount_perc']}', '{$rental_data['delivery']}',\n\t\t\t'{$rental_data['subtotal']}', '{$rental_data['discount']}', '0.00',\n\t\t\t'{$rental_data['vat']}', '{$rental_data['total']}',\n\t\t\t'{$rental_data['discount']}', '{$rental_data['delivery']}', '0.00', '0.00',\n\t\t\t'', '100', '100', '100', '100', '100', '0', '0.00',\n\t\t\t'{$rental_data['vatnum']}', '{$rental_data['tel']}',\n\t\t\t'{$rental_data['timestamp']}', 'CSH', '{$rental_data['deposit']}', '',\n\t\t\t'Client Collect', '0', current_timestamp, '0', '0')";
db_exec($sql) or errDie("Unable to create hire note.");
$invid = pglib_lastid("hire.hire_invoices", "invid");
$hire_nums[$rental_data["id"]] = $invid;
// Do deposit transaction if required
if ($rental_data["deposit"] > 0) {
$cash_on_hand = qryAccountsNum("7200", "000");
$cash_on_hand = $cash_on_hand["accid"];
$cust_control = qryAccountsNum("6400", "000");
$cust_control = $cust_control["accid"];
$refnum = getRefnum();
writetrans($cash_on_hand, $cust_control, date("Y-m-d", $time), $refnum, $rental_data["deposit"], "Cash Receipt for " . CUR . "{$rental_data['deposit']} from " . "{$rental_data['surname']} for Deposit on Hire Note {$rental_data['id']}");
$sql = "\n\t\t\tINSERT INTO hire.cash (invid, cash)\n\t\t\tVALUES ('{$invid}', '{$rental_data['deposit']}')";
db_exec($sql) or errDie("Unable to add cash to hire.");
// Make ledger record
custledger($rental_data["cusnum"], $cust_control, date("Y-m-d", $time), $invid, "Cash Receipt for " . CUR . "{$rental_data['deposit']} from " . "{$rental_data['surname']} for Deposit on Hire Note {$rental_data['id']}", $rental_data["deposit"], "c");
custCT($rental_data["deposit"], $rental_data["cusnum"], date("Y-m-d", $time));
// Turn the amount around to a negative
$stmnt_amt = $rental_data["deposit"] - $rental_data["deposit"] * 2;
// Record the payment on the statement
$sql = "\n\t\t\tINSERT INTO cubit.stmnt(cusnum, invid, docref, amount, date, type,\n\t\t\t\tdiv)\n\t\t\tVALUES('{$rental_data['cusnum']}', '{$invid}', '{$rental_data['id']}',\n\t\t\t\t'{$stmnt_amt}', '" . date("Y-m-d", $time) . "',\n\t\t\t\t'Cash Receipt for " . CUR . "{$rental_data['deposit']} from " . "{$rental_data['surname']} for Deposit on Hire Note {$rental_data['id']}',\n\t\t\t\t'" . USER_DIV . "')";
$stmntRslt = db_exec($sql) or errDie("Unable to add deposit to statement");
// Update customer balance
$sql = "\n\t\t\tUPDATE cubit.customers SET balance=balance-'{$rental_data['deposit']}'\n\t\t\tWHERE cusnum='{$rental_data['cusnum']}'";
db_exec($sql) or errDie("Unable to update customer balance.");
$sql = "\n\t\t\tUPDATE hire.hire_invoices SET deposit_amt='0'\n\t\t\tWHERE invid='{$invid}'";
db_exec($sql) or errDie("Unable to retrieve hire invoices.");
}
// Retrieve items on this invoice
$sql = "\n\t\tSELECT asset_id, basis, from_date, to_date, half_day, qty,\n\t\t\tweekends, total_days, total\n\t\tFROM cubit.hire_trans_items\n\t\tWHERE hire_id='{$rental_data['id']}'";
$item_rslt = db_exec($sql) or errDie("Unable to retrieve items.");
while ($item_data = pg_fetch_array($item_rslt)) {
$unitcost = $item_data["total"] / $item_data["qty"];
// Decide which basis to use
$hours = 0;
$weeks = 0;
$days = 0;
$months = 0;
$total_days = 0;
switch ($item_data["basis"]) {
case "per_hour":
$hours = $item_data["total_days"];
break;
case "per_day":
$days = $item_data["total_days"];
$total_days = $item_data["total_days"];
break;
case "per_week":
$week = $item_data["total_days"];
break;
case "per_month":
$months = $item_data["total_days"];
break;
}
// Convert booleans into something we can use
$half_day = $item_data["half_day"] == "t" ? 1 : 0;
$weekends = $item_data["weekends"] == "t" ? 1 : 0;
$sql = "\n\t\t\tINSERT INTO hire.hire_invitems (invid, qty, amt, unitcost,\n\t\t\t\tfrom_date, to_date, asset_id, basis, hours, weeks, days,\n\t\t\t\tmonths, half_day, weekends, total_days)\n\t\t\tVALUES ('{$invid}', '{$item_data['qty']}', '{$item_data['total']}',\n\t\t\t\t'{$unitcost}', '{$item_data['from_date']}', '{$item_data['to_date']}',\n\t\t\t\t'{$item_data['asset_id']}', '{$item_data['basis']}', '{$hours}',\n\t\t\t\t'{$weeks}', '{$days}', '{$months}', '{$half_day}', '{$weekends}',\n\t\t\t\t'{$total_days}')";
db_exec($sql) or errDie("Unable to create rental items.");
$item_id = pglib_lastid("hire.hire_invitems", "id");
$sql = "\n\t\t\tINSERT INTO hire.assets_hired (invid, asset_id, qty, hired_time,\n\t\t\t\tcust_id, item_id, invnum, value, basis, discount, weekends)\n\t\t\tVALUES ('{$invid}', '{$item_data['asset_id']}', '{$item_data['qty']}',\n\t\t\t\t'{$rental_data['timestamp']}', '{$rental_data['cusnum']}', '{$item_id}',\n\t\t\t\t'{$rental_data['id']}', '{$item_data['total']}', '{$item_data['basis']}',\n\t\t\t\t'0.00', '{$weekends}')";
db_exec($sql) or errDie("Unable to add to assets hired.");
}
}
// Run invoices ----------------------------------------------------------
$sql = "\n\tSELECT id, hire_id, customers.cusnum, order_num, discount_perc, discount,\n\t\tsubtotal, total, vat, timestamp, user_id, surname, addr1, vatnum,\n\t\tusername, delivery\n\tFROM cubit.hire_invoice_trans\n\t\tLEFT JOIN cubit.customers ON hire_invoice_trans.cusnum=customers.cusnum\n\t\tLEFT JOIN cubit.users ON hire_invoice_trans.user_id=users.userid\n\tWHERE done='y' AND user_id='{$user_id}' AND hire_id > 0";
$inv_rslt = db_exec($sql) or errDie("Unable to retrieve invoices.");
while ($inv_data = pg_fetch_array($inv_rslt)) {
$hire_sales = qryAccountsNum("1050", "000");
$cust_control = qryAccountsNum("6400", "000");
$cash_on_hand = qryAccountsNum("7200", "000");
$hire_sales = $hire_sales["accid"];
$cust_control = $cust_control["accid"];
$cash_on_hand = $cash_on_hand["accid"];
$time = strtotime($inv_data["timestamp"]);
$sql = "\n\t\tINSERT INTO cubit.nons_invoices (cusname, cusaddr, cusvatno,\n\t\t\tchrgvat, sdate, done, username, prd, invnum, div, remarks, cusid,\n\t\t\tage, typ, subtot, balance, vat, total, descrip, ctyp, accid,\n\t\t\tfbalance, fsubtot, cordno, terms, odate, systime, bankid,\n\t\t\tcusordno, ncdate, cusnum, discount, delivery, hire_invid,\n\t\t\tcash, cheque, credit)\n\t\tVALUES ('{$inv_data['surname']}', '{$inv_data['addr1']}', '{$inv_data['vatnum']}',\n\t\t\t'yes', '" . date("Y-m-d", $time) . "', 'y', '{$inv_data['username']}',\n\t\t\t'" . PRD_DB . "', '{$inv_data['id']}', '" . USER_DIV . "', '',\n\t\t\t'{$inv_data['cusnum']}', '0', 'inv', '{$inv_data['subtotal']}',\n\t\t\t'{$inv_data['total']}', '{$inv_data['vat']}', '{$inv_data['total']}', '', 's',\n\t\t\t'{$hire_sales}', '0.00', '0.00', '{$inv_data['order_num']}', '0',\n\t\t\t'" . date("Y-m-d", $time) . "', current_timestamp,\n\t\t\t'" . cust_bank_id($inv_data["cusnum"]) . "', '{$inv_data['order_num']}',\n\t\t\t'" . date("Y-m-d", $time) . "', '{$inv_data['cusnum']}',\n\t\t\t'{$inv_data['discount']}', '{$inv_data['delivery']}',\n\t\t\t'" . $hire_nums[$inv_data["hire_id"]] . "', '{$inv_data['total']}', '0', '0')";
db_exec($sql) or errDie("Unable to create non stock invoice.");
$invid = lastinvid();
$sql = "\n\t\tSELECT hire_invoice_items_trans.id, asset_id, basis, from_date,\n\t\t\tto_date, half_day, qty, weekends, total_days, total,\n\t\t\tserial, des, grpid\n\t\tFROM cubit.hire_invoice_items_trans\n\t\t\tLEFT JOIN cubit.assets\n\t\t\t\tON hire_invoice_items_trans.asset_id=assets.id\n\t\tWHERE trans_id='{$inv_data['id']}'";
$item_rslt = db_exec($sql) or errDie("Unable to retrieve items.");
while ($item_data = pg_fetch_array($item_rslt)) {
$unitcost = $item_data["total"] / $item_data["qty"];
$item_id = 0;
$sql = "\n\t\t\tSELECT {$item_data['basis']} FROM hire.basis_prices\n\t\t\tWHERE assetid='{$item_data['asset_id']}'";
$rate_rslt = db_exec($sql) or errDie("Unable to retrieve rate.");
$rate = pg_fetch_result($rate_rslt, 0);
$rate = empty($rate) ? 0.0 : $rate;
$sql = "\n\t\t\tSELECT serial, des FROM cubit.assets\n\t\t\tWHERE id='{$item_data['asset_id']}'";
$asset_rslt = db_exec($sql) or errDie("Unable to retrieve assets.");
$asset_data = pg_fetch_array($asset_rslt);
$sql = "\n\t\t\tINSERT INTO hire.hire_nons_inv_items (invid, qty, description, div,\n\t\t\t\tamt, unitcost, accid, vatex, cunitcost, asset_id, item_id,\n\t\t\t\thired_days, rate)\n\t\t\tVALUES ('{$invid}', '{$item_data['qty']}', '({$asset_data['serial']}) " . "{$asset_data['des']} hired from {$item_data['from_date']} to " . "{$item_data['to_date']}.', '" . USER_DIV . "', '{$item_data['total']}',\n\t\t\t\t'{$unitcost}', '{$hire_sales}', '2', '{$unitcost}',\n\t\t\t\t'{$item_data['asset_id']}', '{$item_id}', '{$item_data['total_days']}',\n\t\t\t\t'{$rate}')";
db_exec($sql) or errDie("Unable to create invoice item.");
// Add up revenue
$sql = "\n\t\t\tINSERT INTO hire.revenue (group_id, asset_id, total, discount,\n\t\t\t\thire_invnum, inv_invnum, cusname)\n\t\t\tVALUES ('{$item_data['grpid']}', '{$item_data['asset_id']}',\n\t\t\t\t'{$item_data['total']}', '0', '0',\n\t\t\t\t'0', '{$inv_data['surname']}')";
db_exec($sql) or errDie("Unable to update revenue");
$sql = "\n\t\t\tUPDATE hire.assets_hired SET return_time=CURRENT_TIMESTAMP\n\t\t\t\tWHERE item_id='{$item_data['id']}'";
db_exec($sql) or errDie("Unable to update hired assets.");
$sql = "\n\t\t\tSELECT serial2 FROM cubit.assets\n\t\t\tWHERE id='{$item_data['asset_id']}'";
$asset_rslt = db_exec($sql) or errDie("Unable to retrieve asset");
$asset_data = pg_fetch_array($asset_rslt);
if (!isSerialized($item_data["asset_id"])) {
$new_qty = $asset_data["serial2"] + $item_data["qty"];
$sql = "\n\t\t\t\tUPDATE cubit.assets SET serial2=(serial2::numeric + '{$item_data['qty']}')\n\t\t\t\tWHERE id='{$item_data['asset_id']}'";
db_exec($sql) or errDie("Unable to update asset qty.");
}
}
$refnum = getRefnum();
writetrans($cust_control, $hire_sales, date("Y-m-d", $time), $refnum, $inv_data["total"], "Non Stock Sales on invoice No. {$inv_data['id']} " . "customer {$inv_data['surname']}");
// Sales record
$sql = "\n\t\tINSERT INTO cubit.salesrec(edate, invid, invnum, debtacc, vat, total,\n\t\t\ttyp, div)\n\t\tVALUES('" . date("Y-m-d", $time) . "', '{$invid}', '{$inv_data['id']}',\n\t\t\t'{$cust_control}', '{$inv_data['vat']}', '{$inv_data['total']}', 'non',\n\t\t\t'" . USER_DIV . "')";
db_exec($sql) or errDie("Unable to create sales record.");
// Vat record
vatr(2, date("Y-m-d", $time), "OUTPUT", '01', $refnum, "Non-Stock Sales, invoice No.{$inv_data['id']}", $inv_data["total"], $inv_data["vat"]);
// Add to statement
$sql = "\n\t\tINSERT INTO cubit.stmnt (cusnum, invid, docref, amount, date, type, div)\n\t\tVALUES ('{$inv_data['cusnum']}', '{$invid}', '{$inv_data['order_num']}',\n\t\t\t'{$inv_data['total']}', '" . date("Y-m-d", $time) . "',\n\t\t\t'Hire Invoice {$inv_data['id']}', '" . USER_DIV . "')";
db_exec($sql) or errDie("Unable to add to statement.");
// Update customer balance
$sql = "\n\t\tUPDATE customers SET balance = (balance + '{$inv_data['total']}')\n\t\tWHERE cusnum='{$inv_data['cusnum']}' AND div='" . USER_DIV . "'";
db_exec($sql) or errDie("Unable to update customer balance.");
custledger($inv_data["cusnum"], $hire_sales, date("Y-m-d", $time), $invid, "Hire Invoice No. {$inv_data['id']}", $inv_data["total"], "d");
custDT($inv_data["total"], $inv_data["cusnum"], date("Y-m-d", $time));
}
// Clear outstanding tables
$sql = "DELETE FROM cubit.hire_trans";
db_exec($sql) or errDie("Unable to remove outstanding (1)");
$sql = "DELETE FROM cubit.hire_trans_items";
db_exec($sql) or errDie("Unable to remove outstanding (2)");
$sql = "DELETE FROM cubit.hire_invoice_trans";
db_exec($sql) or errDie("Unable to remove outstanding (3)");
$sql = "DELETE FROM cubit.hire_invoice_items_trans";
db_exec($sql) or errDie("Unable to remove outstanding (4)");
pglib_transaction("COMMIT");
return enter();
}
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($details)) {
$v->isOk($accid, "string", 1, 20, "Invalid Account number.");
} else {
$v->isOk($topacc, "num", 1, 20, "Invalid Account number.");
}
# 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;
}
if (isset($details)) {
$acc = qryAccounts($accid);
} else {
if (strlen($topacc) > 2) {
$acc = qryAccountsNum($topacc, "000");
} else {
return "<li> Accounts number : {$topacc}/000 does not exist";
}
}
db_conn($prd);
// Set up table to display in
$OUTPUT = "\n\t\t\t<table>\n\t\t\t\t<tr>\n\t\t\t\t\t<th colspan='7'><h3>Journal Entries for Account : {$acc['topacc']}/{$acc['accnum']} - {$acc['accname']} and Sub Accounts</h3></th>\n\t\t\t\t</tr>\n\t\t\t\t<tr><th colspan='7'></th></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='3' align='left'>" . COMP_NAME . "</td>\n\t\t\t\t\t<td colspan='4' align='right'>" . date("Y-m-d") . "</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\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>Contra Acc</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>User</th>\n\t\t\t\t</tr>";
# get all sub accounts
core_connect();
$sql = "SELECT * FROM accounts WHERE topacc = '{$acc['topacc']}' AND div = '" . USER_DIV . "' ORDER BY accnum ASC";
$subRs = db_exec($sql);
# all totals
$allcredtot = 0;
$alldebtot = 0;
while ($subacc = pg_fetch_array($subRs)) {
$OUTPUT .= "\n\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='10'><h4>Account : {$subacc['topacc']}/{$subacc['accnum']} - {$subacc['accname']}</h4></td>\n\t\t\t\t</tr>";
# get Transactions
db_conn($prd);
$sql = "SELECT * FROM transect WHERE debit = '{$subacc['accid']}' AND div = '" . USER_DIV . "' OR credit = '{$subacc['accid']}' AND div = '" . USER_DIV . "'";
$tranRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve Transaction details from database.", SELF);
if (pg_numrows($tranRslt) < 1) {
# counts
$credtot = 0;
$debtot = 0;
$OUTPUT .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='10'>No Transactions found</td>\n\t\t\t\t\t</tr>";
$OUTPUT .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='4'><b>Total</b></td>\n\t\t\t\t\t\t\t<td><b>" . CUR . " {$debtot}</b></td>\n\t\t\t\t\t\t\t<td><b>" . CUR . " {$credtot}</b></td>\n\t\t\t\t\t\t\t<td></td>\n\t\t\t\t\t\t</tr>";
} else {
# counts
$credtot = 0;
$debtot = 0;
# display all transactions
while ($tran = pg_fetch_array($tranRslt)) {
extract($tran);
if ($debit == $subacc['accid']) {
$cacc = $credit;
$debitamt = "R " . $amount;
$debtot += $amount;
$alldebtot += $amount;
$creditamt = "";
} else {
$debitamt = "";
$creditamt = "R " . $amount;
$credtot += $amount;
$allcredtot += $amount;
$cacc = $debit;
}
# get contra account name
$caccRs = get("core", "accname,topacc,accnum", "accounts", "accid", $cacc);
$cacc = pg_fetch_array($caccRs);
$OUTPUT .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td>{$date}</td>\n\t\t\t\t\t\t\t<td>{$refnum}</td>\n\t\t\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\t\t\t\t\t\t\t<td>{$details}</td>\n\t\t\t\t\t\t\t<td>{$debitamt}</td>\n\t\t\t\t\t\t\t<td>{$creditamt}</td>\n\t\t\t\t\t\t\t<td>{$author}</td>\n\t\t\t\t\t\t</tr>";
}
if ($acc["acctype"] == "I") {
$accbal = $credtot - $debtot;
} else {
if ($acc["acctype"] == "E") {
$accbal = $debtot - $credtot;
} else {
if ($acc["acctype"] == "B") {
$accbal = $debtot - $credtot;
} else {
return "<li class='err'>Account number is beyond limits.</li>";
}
}
}
$OUTPUT .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='4'><b>Total</b></td>\n\t\t\t\t\t\t<td><b>" . CUR . " {$debtot}</b></td>\n\t\t\t\t\t\t<td><b>" . CUR . " {$credtot}</b></td>\n\t\t\t\t\t\t<td></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='4'><b>Balance</b></td>\n\t\t\t\t\t\t<td colspan='2'><b>" . CUR . " {$accbal}</b></td>\n\t\t\t\t\t\t<td></td>\n\t\t\t\t\t</tr>";
}
}
$OUTPUT .= "\n\t\t\t\t<tr><td colspan='7'></td></tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td colspan='4'><b>Total</b></td>\n\t\t\t\t\t<td><b>" . CUR . " {$alldebtot}</b></td>\n\t\t\t\t\t<td><b>" . CUR . " {$allcredtot}</b></td>\n\t\t\t\t\t<td></td>\n\t\t\t\t</tr>\n\t\t\t</table>";
$acc['accname'] = str_replace(" ", "_", $acc['accname']);
# Send the stream
include "temp.xls.php";
Stream("AllTrans-{$acc['accname']}_And_Subacc", $OUTPUT);
}
function viewtran($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($prd, "string", 1, 14, "Invalid Period number.");
if (isset($details)) {
$v->isOk($accid, "string", 1, 20, "Invalid Account number.");
$hide = "\n\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t<input type='hidden' name='details' value='{$details}'>\n\t\t\t<input type='hidden' name='accid' value='{$accid}'>";
} else {
$v->isOk($topacc, "num", 1, 4, "Invalid Account number.");
$hide = "\n\t\t\t<input type='hidden' name='prd' value='{$prd}'>\n\t\t\t<input type='hidden' name='topacc' value='{$topacc}'>";
}
# 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();
}
if (isset($details)) {
$acc = qryAccounts($accid);
} else {
if (strlen($topacc) > 2) {
$acc = qryAccountsNum($topacc, "000");
} else {
return "<li> Accounts number : {$topacc}/000 does not exist.</li>";
}
}
db_conn($prd);
// Set up table to display in
$OUTPUT = "\n\t\t<center>\n\t\t<h3>Journal Entries for Account : {$acc['topacc']}/{$acc['accnum']} - {$acc['accname']} and Sub Accounts</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>Reference</th>\n\t\t\t\t<th>Contra Acc</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>User</th>\n\t\t\t</tr>";
# get all sub accounts
core_connect();
$sql = "SELECT * FROM accounts WHERE topacc = '{$acc['topacc']}' AND div = '" . USER_DIV . "' ORDER BY accnum ASC";
$subRs = db_exec($sql);
# all totals
$allcredtot = 0;
$alldebtot = 0;
while ($subacc = pg_fetch_array($subRs)) {
$OUTPUT .= "\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='10'><h2>Account : {$subacc['topacc']}/{$subacc['accnum']} - {$subacc['accname']}</h2></td>\n\t\t\t</tr>";
# get Transactions
db_conn($prd);
$sql = "SELECT * FROM transect WHERE debit = '{$subacc['accid']}' AND div = '" . USER_DIV . "' OR credit = '{$subacc['accid']}' AND div = '" . USER_DIV . "'";
$tranRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve Transaction details from database.", SELF);
if (pg_numrows($tranRslt) < 1) {
# counts
$credtot = 0;
$debtot = 0;
$OUTPUT .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='10'>No Transactions found</td>\n\t\t\t\t</tr>";
$debtot = sprint($debtot);
$credtot = sprint($credtot);
$OUTPUT .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='4'><b>Total</b></td>\n\t\t\t\t\t<td><b>" . CUR . " {$debtot}</b></td>\n\t\t\t\t\t<td><b>" . CUR . " {$credtot}</b></td>\n\t\t\t\t\t<td></td>\n\t\t\t\t</tr>";
} else {
# counts
$credtot = 0;
$debtot = 0;
# display all transactions
while ($tran = pg_fetch_array($tranRslt)) {
extract($tran);
$amount = sprint($amount);
if ($debit == $subacc['accid']) {
$cacc['accname'] = $caccname;
$cacc['accnum'] = $caccnum;
$cacc['topacc'] = $ctopacc;
$debitamt = "R " . $amount;
$debtot += $amount;
$alldebtot += $amount;
$creditamt = "";
} else {
$debitamt = "";
$creditamt = "R " . $amount;
$credtot += $amount;
$allcredtot += $amount;
$cacc['accname'] = $daccname;
$cacc['accnum'] = $daccnum;
$cacc['topacc'] = $dtopacc;
}
/*
# get contra account name
$caccRs = get("core","accname,topacc,accnum","accounts","accid",$cacc);
$cacc = pg_fetch_array($caccRs);
*/
$OUTPUT .= "\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>{$date}</td>\n\t\t\t\t\t\t<td>{$custom_refnum}</td>\n\t\t\t\t\t\t<td>{$cacc['topacc']}/{$cacc['accnum']} - {$cacc['accname']}</td>\n\t\t\t\t\t\t<td>{$details}</td>\n\t\t\t\t\t\t<td align='right'>{$debitamt}</td>\n\t\t\t\t\t\t<td align='right'>{$creditamt}</td>\n\t\t\t\t\t\t<td>{$author}</td>\n\t\t\t\t\t</tr>";
}
if ($acc["acctype"] == "I") {
$accbal = $credtot - $debtot;
} else {
if ($acc["acctype"] == "E") {
$accbal = $debtot - $credtot;
} else {
if ($acc["acctype"] == "B") {
$accbal = $debtot - $credtot;
} else {
return "<li class='err'>Account number is beyond limits.</li>";
}
}
}
$debtot = sprint($debtot);
$credtot = sprint($credtot);
$accbal = sprint($accbal);
$OUTPUT .= "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='4'><b>Total</b></td>\n\t\t\t\t\t<td><b>" . CUR . " {$debtot}</b></td>\n\t\t\t\t\t<td><b>" . CUR . " {$credtot}</b></td>\n\t\t\t\t\t<td></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='4'><b>Balance</b></td>\n\t\t\t\t\t<td colspan='2'><b>" . CUR . " {$accbal}</b></td>\n\t\t\t\t\t<td></td>\n\t\t\t\t</tr>";
}
}
$alldebtot = sprint($alldebtot);
$allcredtot = sprint($allcredtot);
$OUTPUT .= "\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='4'><b>Total</b></td>\n\t\t\t\t<td><b>" . CUR . " {$alldebtot}</b></td>\n\t\t\t\t<td><b>" . CUR . " {$allcredtot}</b></td>\n\t\t\t\t<td></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='center' colspan='10'>\n\t\t\t\t\t<form action='../xls/accsub-trans-xls.php' method='POST' name='form'>\n\t\t\t\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t\t\t\t{$hide}\n\t\t\t\t\t\t<input type='submit' name='xls' value='Export to spreadsheet'>\n\t\t\t\t\t</form>\n\t\t\t\t</td>\n\t\t\t</tr>\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'><td align='center'><a target=_blank href='../core/acc-new2.php'>Add account (New Window)</a></td></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-journal.php'>Current Year Details General Ledger 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 $OUTPUT;
}