function write($_GET)
{
# get vars
extract($_GET);
# validate input
require_lib("validate");
$v = new validate();
$prd += 0;
$v->isOk($purid, "num", 1, 20, "Invalid Invoice number.");
$sndate = $p_year . "-" . $p_month . "-" . $p_day;
if (!checkdate($p_month, $p_day, $p_year)) {
$v->addError($sdate, "Invalid Date.");
}
$td = $sndate;
foreach ($ids as $key => $id) {
$v->isOk($id, "num", 1, 20, "Invalid Item number.");
$v->isOk($qtys[$key], "num", 1, 20, "Invalid Item quantity.");
//$v->isOk ($amts[$key], "float", 1, 20, "Invalid Item amount.");
}
//$v->isOk ($subtot, "float", 1, 20, "Invalid sub-total amount.");
//$v->isOk ($vat, "float", 1, 20, "Invalid vat amount.");
//$v->isOk ($total, "float", 1, 20, "Invalid total amount.");
# display errors, if any
if ($v->isError()) {
$err = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# CHECK IF THIS DATE IS IN THE BLOCKED RANGE
$blocked_date_from = getCSetting("BLOCKED_FROM");
$blocked_date_to = getCSetting("BLOCKED_TO");
if (strtotime($sndate) >= strtotime($blocked_date_from) and strtotime($sndate) <= strtotime($blocked_date_to) and !user_is_admin(USER_ID)) {
return "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
$cvacc = gethook("accnum", "pchsacc", "name", "Cost Variance");
db_conn($prd);
# Get invoice info
$sql = "SELECT * FROM nons_purchases WHERE purid = '{$purid}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to get invoice information");
if (pg_numrows($invRslt) < 1) {
return "<i class='err'>Not Found{$sql}</i>";
}
$inv = pg_fetch_array($invRslt);
db_conn("cubit");
$sql = "SELECT * FROM nons_purchasesn";
$purRslt = db_exec($sql) or errDie("Unable to get Non-Stock Order information");
if (pg_numrows($purRslt) < 1) {
$noteid = 2;
} else {
$noteid = pglib_lastid("nons_purchasesn", "id");
$noteid++;
}
$refnum = getrefnum();
db_conn("cubit");
if ($inv['spurnum'] > 0) {
if ($inv['ctyp'] == "sup") {
$inv['ctyp'] = 's';
$inv['supid'] = $inv['typeid'];
} elseif ($inv['ctyp'] == "led") {
$inv['ctyp'] = 'c';
$inv['deptid'] = $inv['typeid'];
} else {
$inv['ctyp'] = 'p';
}
}
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
if ($inv['ctyp'] == 's') {
$sql = "SELECT * FROM suppliers WHERE supid = '{$inv['supid']}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to view customer");
$cus = pg_fetch_array($custRslt);
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cus['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql) or errDie("Unable to get details.");
if (pg_numrows($deptRslt) < 1) {
$dept['deptname'] = "<li class=err>Department not Found.";
} else {
$dept = pg_fetch_array($deptRslt);
}
} elseif ($inv['ctyp'] == 'c') {
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$inv['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql) or errDie("Unable to get details.");
if (pg_numrows($deptRslt) < 1) {
$dept['deptname'] = "<li class=err>Department not Found.";
} else {
$dept = pg_fetch_array($deptRslt);
}
$dept['credacc'] = $dept['pca'];
} elseif ($inv['ctyp'] == 'cb') {
$bankid = $inv['supid'];
$bankid += 0;
db_conn("cubit");
$sql = "SELECT * FROM bankacct WHERE bankid = '{$bankid}'";
$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'];
$dept['credacc'] = $BA;
} elseif ($inv['ctyp'] == 'p') {
core_connect();
# Get Petty cash account
$cashacc = gethook("accnum", "bankacc", "name", "Petty Cash");
# Get account name for thy lame User's Sake
$accRslt = get("core", "*", "accounts", "accid", $cashacc);
if (pg_numrows($accRslt) < 1) {
return "<li class='err'> Petty Cash Account not found.</li>";
}
$acc = pg_fetch_array($accRslt);
$dept['credacc'] = $cashacc;
//$supacc = "$acc[topacc]/$acc[accnum] - $acc[accname]";
//$hide = "<input type=hidden name=supacc value='$cashacc'><input type=hidden name=ctyp value='$ctyp'>";
}
db_conn($prd);
/* --- Start Products Display --- */
$tot_post = 0;
# Products layout
$products = "";
$resub = 0;
$revat = 0;
foreach ($ids as $key => $id) {
db_conn($prd);
$sql = "SELECT * FROM nons_pur_items WHERE purid = '{$purid}' AND id = '{$id}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
$stkd = pg_fetch_array($stkdRslt);
$stkacc = $stkd['accid'];
# keep records for transactions
$sql = "UPDATE nons_pur_items SET rqty = (rqty + '{$qtys[$key]}') WHERE id = '{$stkd['id']}'";
$sRslt = db_exec($sql);
# Calculate cost amount bought
$amt[$key] = $qtys[$key] * $unitcost[$key];
/* delivery charge */
# Calculate percentage from subtotal
$perc[$key] = $amt[$key] / $SUBTOTAL * 100;
# Get percentage from shipping charges
$shipc[$key] = $perc[$key] / 100 * $shipchrg;
# add delivery charges
$amt[$key] += $shipc[$key];
/* end delivery charge */
# the subtotal + delivery charges
$resub += $amt[$key];
# calculate vat
$svat[$key] = svat($amt[$key], $stkd['amt'], $stkd['svat']);
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatcode']}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$vd = pg_fetch_array($Ri);
vatr($vd['id'], $td, "INPUT", $vd['code'], $refnum, "VAT for Non-Stock purchase No. {$inv['purnum']} returned", $amt[$key], $svat[$key]);
# received vat
$revat += $svat[$key];
# make amount vat free
// if($inv['vatinc'] == "yes"){
// $amt[$key] = ($amt[$key] - $svat[$key]);
// }
$amts[$key] = $stkd['unitcost'] * $qtys[$key];
$nv = sprint($stkd['svat'] / $stkd['qty'] * $qtys[$key]);
if ($inv['vatinc'] != "no") {
$aev = $amts[$key] - $nv;
} else {
$aev = $amts[$key];
}
$stkd['accid'] += 0;
db_conn('cubit');
$sql = "\n\t\t\tINSERT INTO nons_pur_itemsn (\n\t\t\t\tnoteid, qty, description, amt, unitcost, svat, div, \n\t\t\t\tcod, des, ddate, accid\n\t\t\t) VALUES (\n\t\t\t\t'{$noteid}', '{$qtys[$key]}', '{$stkd['des']}', '{$amts[$key]}', '{$stkd['unitcost']}', '{$stkd['svat']}', '" . USER_DIV . "', \n\t\t\t\t'{$stkd['cod']}', '{$stkd['des']}', '{$stkd['ddate']}', '{$stkd['accid']}'\n\t\t\t)";
$stkdRslt = db_exec($sql) or errDie("Unable to insert note items.");
if ($inv['ctyp'] == 'ac') {
$dept['credacc'] = $inv['mpurid'];
}
//$sql = "INSERT INTO nons_pur_itemsn(noteid, cod, des, qty, unitcost, amt, svat, ddate, accid, div) VALUES('$nid', '$stktc[cod]', '$stktc[des]', '$qtys[$key]', '$stktc[unitcost]', '$stktc[amt]', '$stktc[svat]', '$stktc[ddate]', '$stktc[accid]', '".USER_DIV."')";
if ($inv['spurprd'] == 0) {
$tot_post += $aev;
writetrans($dept['credacc'], $stkacc, $td, $refnum, $aev, "Non-Stock purchase No. {$inv['purnum']} returned, Supplier {$inv['supplier']}.");
}
}
$supacc = $dept['credacc'];
$tot_post = 0;
$pur = $inv;
if ($inv['spurprd'] > 0) {
$retot = $TOTAL;
$vatamt = $VAT;
db_conn($pur['spurprd']);
# Get purchase info
$sql = "SELECT * FROM purchases WHERE purnum = '{$pur['spurnum']}' AND div = '" . USER_DIV . "'";
$spurRslt = db_exec($sql) or errDie("Unable to get purchase information");
if (pg_numrows($spurRslt) < 1) {
return "<li> - purchase Not Found</li>";
}
$spur = pg_fetch_array($spurRslt);
db_conn($pur['spurprd']);
# Get selected stock
$sql = "SELECT * FROM pur_items WHERE purid = '{$spur['purid']}' AND div = '" . USER_DIV . "'";
$sstkdRslt = db_exec($sql);
while ($sstk = pg_fetch_array($sstkdRslt)) {
if ($spur['vatinc'] == "yes") {
$csamt = sprint(($sstk['amt'] - $sstk['svat']) / $spur['subtot'] * ($retot - $vatamt));
} else {
$csamt = sprint($sstk['amt'] / $spur['subtot'] * ($retot - $vatamt));
}
db_connect();
# get selected stock
$sql = "SELECT * FROM stock WHERE stkid = '{$sstk['stkid']}' AND div = '" . USER_DIV . "'";
$stktRslt = db_exec($sql);
$stkt = pg_fetch_array($stktRslt);
/* Code insert */
# get warehouse name
db_conn("exten");
$sql = "SELECT * FROM warehouses WHERE whid = '{$stkt['whid']}' AND div = '" . USER_DIV . "'";
$whRslt = db_exec($sql);
$wh = pg_fetch_array($whRslt);
$tot_post += $csamt;
writetrans($supacc, $wh['stkacc'], date("d-m-Y"), $refnum, $csamt, "Non-Stock Purchase No. {$pur['purnum']} Returned.");
/* End code insert */
db_connect();
if ($stkt['units'] != 0) {
$sql = "\n\t\t\t\t\tUPDATE stock \n\t\t\t\t\tSET csamt = (csamt - '{$csamt}'), csprice = (csamt/units) \n\t\t\t\t\tWHERE stkid = '{$sstk['stkid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update stock to Cubit.", SELF);
} else {
$sql = "UPDATE stock SET csamt = (csamt - '{$csamt}') WHERE stkid = '{$sstk['stkid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update stock to Cubit.", SELF);
}
$sdate = $pur['pdate'];
stockrec($stkt['stkid'], $stkt['stkcod'], $stkt['stkdes'], 'ct', $sdate, 0, $csamt, "Cost decreased with Non Stock Purchase No. {$pur['purnum']}, returned");
# Just wanted to fix the xxx.xxxxxxe-x value
# get selected stock
db_connect();
$sql = "SELECT * FROM stock WHERE stkid = '{$sstk['stkid']}' AND div = '" . USER_DIV . "'";
$stktRslt = db_exec($sql);
$stkt = pg_fetch_array($stktRslt);
# $csprice = round(($stk['csamt']/$stk['units']), 2);
if ($stkt['units'] > 0) {
$csprice = round($stkt['csamt'] / $stkt['units'], 2);
} else {
$csprice = round($stkt['csprice'], 2);
}
# update stock(csprice = (csamt/units))
$sql = "UPDATE stock SET csprice = '{$csprice}' WHERE stkid = '{$sstk['stkid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update stock to Cubit.", SELF);
}
}
/* --- Start Some calculations --- */
# Subtotal
//$SUBTOT = sprint($subtot);
// $VAT = sprint($vat);
//$TOTAL = sprint($total);
/* --- End Some calculations --- */
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
$varacc = gethook("accnum", "salesacc", "name", "sales_variance");
/* - End Hooks - */
# todays date
$date = date("d-m-Y");
$sdate = date("Y-m-d");
// print $inv['ctyp']; exit;
//$real_noteid = divlastid('note', USER_DIV);
db_conn('cubit');
# bank % cust
if ($inv['ctyp'] == 's') {
$sql = "SELECT * FROM suppliers WHERE supid = '{$inv['supid']}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to view customer");
$cus = pg_fetch_array($custRslt);
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cus['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql) or errDie("Unable to get details.");
if (pg_numrows($deptRslt) < 1) {
$dept['deptname'] = "<li class=err>Department not Found.";
} else {
$dept = pg_fetch_array($deptRslt);
}
$tpp = 0;
# record transaction from data
//foreach($totstkamt as $stkacc => $wamt){
//}
//$tot_dif=sprint($tot_post-$TOTAL);
} elseif ($inv['ctyp'] == 'b') {
$dept['debtacc'] = getbankaccid($inv['accid']);
$amounts = "";
$accids = "";
$vats = "";
$chrgvats = "";
$gamt = 0;
# record transaction from data
foreach ($totstkamt as $stkacc => $wamt) {
# Cook vars
$amounts .= "|{$wamt}";
$accids .= "|{$stkacc}";
$vats .= "|0";
$chrgvats .= "|no";
# Debit Customer and Credit stock
//$tot_post+=$wamt;
//writetrans($stkacc, $dept['debtacc'], date("d-m-Y"), $refnum, $wamt, "Non-Stock purchase No. $inv[invnum] Credit note No.$real_noteid.");
}
# Debit bank and credit the account involved
} else {
//$cusacc = $inv['accid'];
$sdate = date("Y-m-d");
# record transaction from data
//foreach($totstkamt as $stkacc => $wamt){
# Debit Customer and Credit stock
// $tot_post+=$wamt;
//writetrans($stkacc, $cusacc, date("d-m-Y"), $refnum, $wamt, "Non-Stock Purchase No. $inv[invnum] Credit note No.$real_noteid.");
//pettyrec($cusacc, $sdate, "dt", "Non-Stock Purchase No. $inv[invnum] Credit note No.$real_noteid.", $wamt, "Account Sale Credit note");
//}
# Debit bank and credit the account involved
//$tot_post+=$VAT;
//writetrans($vatacc, $cusacc, date("d-m-Y"), $refnum, $VAT, "Non-Stock Purchase No. $inv[invnum] Credit note No.$real_noteid VAT.");
//pettyrec($cusacc, $sdate, "dt", "Non-Stock Purchase No. $inv[invnum] Credit note No.$real_noteid VAT.", $VAT, "Account Sale Credit note VAT");
}
if ($VAT != 0) {
$tot_post += $VAT;
writetrans($dept['credacc'], $vatacc, $td, $refnum, $VAT, "Non-Stock purchase No. {$inv['purnum']} Returned. Supplier {$inv['supplier']}.");
}
$sdate = date("Y-m-d");
if ($inv['spurprd'] > 0) {
$stkacc = $wh['stkacc'];
$diff = sprint($TOTAL - $tot_post);
if ($diff > 0) {
writetrans($dept['credacc'], $cvacc, $td, $refnum, $diff, "Cost Variance for Non stock Purchase No. {$pur['purnum']} Returned");
} elseif ($diff < 0) {
writetrans($cvacc, $dept['credacc'], $td, $refnum, -$diff, "Cost Variance for Non stock Purchase No. {$pur['purnum']} Returned");
}
}
/*
if($tot_dif>0) {
writetrans($stkacc, $varacc, date("d-m-Y"), $refnum, $tot_dif, "Purchase Variance on invoice $real_invid");
} elseif($tot_post<0) {
writetrans($varacc, $stkacc, date("d-m-Y"), $refnum, $tot_dif, "Purchase Variance on invoice $real_invid");
}*/
$reff = $refnum;
db_connect();
if ($inv['ctyp'] == 's') {
# Record the payment on the statement
$sql = "\n\t\t\tINSERT INTO sup_stmnt (\n\t\t\t\tsupid, ref, amount, edate, descript, div, cacc\n\t\t\t) VALUES (\n\t\t\t\t'{$inv['supid']}', '{$reff}', '-{$TOTAL}', '{$td}', 'Non Stock purchase {$inv['purnum']} returned', '" . USER_DIV . "', '{$stkacc}'\n\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the customer (make balance less)
$sql = "UPDATE suppliers SET balance = (balance - '{$TOTAL}'::numeric(13,2)) WHERE supid = '{$inv['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Make ledge record
suppledger($inv['supid'], $stkacc, $td, $reff, "Non Stock purchase {$inv['purnum']} returned", $TOTAL, "d");
$mt = $TOTAL * -1;
db_connect();
$sql = "INSERT INTO suppurch (supid, purid, pdate, balance, div) VALUES ('{$inv['supid']}', '{$inv['purnum']}', '{$td}', '{$mt}', '" . USER_DIV . "')";
$purcRslt = db_exec($sql) or errDie("Unable to update int Orders information in Cubit.{$sql}", SELF);
//custfCT($TOTAL, $inv['cusid'], $sndate);
} elseif ($inv['ctyp'] == 'cb') {
$date = date("Y-m-d");
# Record the Receipt record
db_connect();
$sql = "\n\t\t\tINSERT INTO cashbook (\n\t\t\t\tbankid, trantype, date, name, descript, cheqnum, amount, \n\t\t\t\tbanked, accids, amounts, chrgvats, vats, div, accinv\n\t\t\t) VALUES (\n\t\t\t\t'{$inv['supid']}', 'deposit', '{$td}', '{$inv['supplier']}', 'Nons Stock purchase {$inv['purnum']} returned', '0', '{$TOTAL}', \n\t\t\t\t'no', '', '{$TOTAL}', '{$inv['vatinc']}', '{$VAT}', '" . USER_DIV . "','{$stkacc}'\n\t\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank Receipt to database.", SELF);
}
db_connect();
$sql = "UPDATE \"{$prd}\".nons_purchases SET balance = (balance - '{$TOTAL}'::numeric(13,2)) WHERE purid = '{$inv['purid']}' AND div = '" . USER_DIV . "'";
$upRslt = db_exec($sql) or errDie("Unable to update invoice information");
$sql = "UPDATE cubit.suppurch SET balance=(balance - '{$TOTAL}'::numeric(13,2)) WHERE purid='{$inv['purid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice balance.");
# write note
$sql = "\n\t\tINSERT INTO cubit.nons_purchasesn (\n\t\t\tpurid, purnum, supplier, supaddr, vatinc, pdate, subtot, vat, total, \n\t\t\tprd, notenum, ctyp, remarks, div\n\t\t) VALUES (\n\t\t\t'{$inv['purid']}', '{$inv['purnum']}', '{$inv['supplier']}', '{$inv['supaddr']}', '{$inv['vatinc']}', '{$td}', '{$SUBTOTAL}', '{$VAT}', '{$TOTAL}', \n\t\t\t'" . PRD_DB . "', '{$noteid}', '{$inv['ctyp']}', '{$remarks}', '" . USER_DIV . "'\n\t\t)";
$rslt = db_exec($sql) or errDie("Unable to create template Non-Stock Invoice.{$sql}", SELF);
$nid = pglib_lastid("nons_purchasesn", "id");
$nid++;
# write note items
foreach ($ids as $key => $id) {
db_conn($prd);
$sql = "SELECT * FROM nons_pur_items WHERE purid = '{$purid}' AND id = '{$id}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql) or errDie("Unable to get data.{$sql}");
$stktc = pg_fetch_array($stkdRslt);
db_conn("cubit");
$sql = "\n\t\t\tINSERT INTO nons_pur_itemsn (\n\t\t\t\tnoteid, cod, des, qty, unitcost, amt, svat, \n\t\t\t\tddate, accid, div\n\t\t\t) VALUES (\n\t\t\t\t'{$nid}', '{$stktc['cod']}', '{$stktc['des']}', '{$qtys[$key]}', '{$stktc['unitcost']}', '{$stktc['amt']}', '{$stktc['svat']}', \n\t\t\t\t'{$stktc['ddate']}', '{$stktc['accid']}', '" . USER_DIV . "'\n\t\t\t)";
//$rslt = db_exec($sql) or errDie("Unable to insert Order items to Cubit.",SELF);
}
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
/* -- Format the remarks boxlet -- */
$inv["remarks"] = "<table border=1><tr><td>Remarks:<br>{$inv['remarks']}</td><ble>";
$cc = "<script> CostCenter('dt', 'Credit Note', '{$inv['pdate']}', 'Non Stock Credit Note No.{$noteid}', '" . ($TOTAL - $VAT) . "', ''); </script>";
/* -- Final Layout -- */
$details = "\n\t\t{$cc}\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Purchase Return</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Purchase return has been recorded.</td>\n\t\t\t</tr>\n\t\t</table>\n\t\t<p>\n\t\t<table " . TMPL_tblDflts . ">\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='" . bg_class() . "'>\n\t\t\t\t<td><a href='purchase-view.php'>View purchases</a></td>\n\t\t\t</tr>\n\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t</table>";
return $details;
}
function write($_POST)
{
extract($_POST);
if (isset($back)) {
unset($_POST["back"]);
return enter($_POST);
}
# CHECK IF THIS DATE IS IN THE BLOCKED RANGE
$blocked_date_from = getCSetting("BLOCKED_FROM");
$blocked_date_to = getCSetting("BLOCKED_TO");
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 20, "Invalid Bank ID.");
foreach ($amount as $key => $value) {
# check all vars
$v->isOk($to[$key], "string", 1, 255, "Invalid receipient/depositor.");
$v->isOk($trantype[$key], "string", 1, 20, "Invalid transaction type.");
$v->isOk($descript[$key], "string", 0, 255, "Invalid description.");
$v->isOk($ref[$key], "string", 0, 255, "Invalid reference <b>[{$key}]</b>.");
$v->isOk($cheqnum[$key], "num", 0, 20, "Invalid cheque number <b>[{$key}]</b>.");
$v->isOk($amount[$key], "float", 1, 8, "Invalid amount <b>[{$key}]</b>.");
$v->isOk($accinv[$key], "num", 1, 20, "Invalid account involved <b>[{$key}]</b>.");
$v->isOk($date[$key], "date", 1, 15, "Invalid date <b>[{$key}]</b>.");
if (strtotime($date[$key]) >= strtotime($blocked_date_from) and strtotime($date[$key]) <= strtotime($blocked_date_to) and !user_is_admin(USER_ID)) {
return "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
}
# 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;
}
# Processes
db_connect();
# Begin Transaction
pglib_transaction("BEGIN");
# Some info
$bankacc = getbankaccid($bankid);
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
foreach ($amount as $key => $amt) {
$totamt = $amount[$key];
if ($chrgvat[$key] == "exc") {
$totamt += $vat[$key];
} elseif ($chrgvat[$key] == "inc") {
$amount[$key] -= $vat[$key];
} else {
$vat[$key] = "No VAT";
}
if ($cheqnum[$key] == '') {
$cheqnum[$key] = 0;
}
if (!isset($batch)) {
$refnum = getrefnum();
if (strtolower($trantype[$key]) == 'deposit') {
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "1");
writetrans($bankacc, $accinv[$key], $date[$key], $refnum, $amount[$key], $descript[$key]);
if ($vat[$key] != 0) {
# DT(Bank), CT(VAT)
$vat[$key] += 0;
writetrans($bankacc, $vatacc, $date[$key], $refnum, $vat[$key], $descript[$key] . " VAT");
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode[$key]}'";
$Rt = db_exec($Sl) or errDie("Unable to get data.");
$vd = pg_fetch_array($Rt);
vatr($vatcode[$key], $date[$key], "OUTPUT", $vd['code'], $refnum, $descript[$key] . " VAT", $totamt, $vat[$key]);
}
} else {
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
writetrans($accinv[$key], $bankacc, $date[$key], $refnum, $amount[$key], $descript[$key]);
if ($vat[$key] != 0) {
# DT(Bank), CT(VAT)
$vat[$key] += 0;
writetrans($vatacc, $bankacc, $date[$key], $refnum, $vat[$key], $descript[$key] . " VAT");
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode[$key]}'";
$Rt = db_exec($Sl) or errDie("Unable to get data.");
$vd = pg_fetch_array($Rt);
vatr($vatcode[$key], $date[$key], "INPUT", $vd['code'], $refnum, $descript[$key] . " VAT", -$totamt, -$vat[$key]);
}
}
$vat[$key] += 0;
db_connect();
$sql = "\n\t\t\t\tINSERT INTO cashbook (\n\t\t\t\t\tbankid, trantype, date, name, descript, cheqnum, \n\t\t\t\t\tamount, banked, accinv, div,chrgvat,vat,reference\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$bankid}', lower('{$trantype[$key]}'), '{$date[$key]}', '{$to[$key]}', '{$descript[$key]}', '{$cheqnum[$key]}', \n\t\t\t\t\t'{$totamt}', 'no', '{$accinv[$key]}', '" . USER_DIV . "','{$chrgvat[$key]}', '{$vat[$key]}','{$ref[$key]}'\n\t\t\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
} else {
db_connect();
$vat[$key] += 0;
$sql = "\n\t\t\t\tINSERT INTO batch_cashbook (\n\t\t\t\t\tbankid, trantype, date, name, descript, cheqnum, \n\t\t\t\t\tamount, banked, accinv, div, chrgvat, vat, vatcode, \n\t\t\t\t\treference\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$bankid}', lower('{$trantype[$key]}'), '{$date[$key]}', '{$to[$key]}', '{$descript[$key]}', '{$cheqnum[$key]}', \n\t\t\t\t\t'{$totamt}', 'no', '{$accinv[$key]}', '" . USER_DIV . "','{$chrgvat[$key]}','{$vat[$key]}','{$vatcode[$key]}', \n\t\t\t\t\t'{$ref[$key]}'\n\t\t\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
}
}
# Get bank details
$bankAccRslt = get("cubit", "*", "bankacct", "bankid", $bankid);
$bankacc = pg_fetch_array($bankAccRslt);
pglib_transaction("COMMIT");
if (!isset($batch)) {
$write = "\n\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Statement Recorded</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td>New Statement Details for account, <b>{$bankacc['accname']} ({$bankacc['accnum']})</b><br>held at <b>{$bankacc['bankname']}</b>, was successfully added to Cubit.</td>\n\t\t\t\t</tr>\n\t\t\t</table>";
} else {
$write = "\n\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Batch entries recorded</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td>New batch items for account, <b>{$bankacc['accname']} ({$bankacc['accnum']})</b><br>held at <b>{$bankacc['bankname']}</b>, was successfully added to Cubit.</td>\n\t\t\t\t</tr>\n\t\t\t</table>";
}
# Main table (layout with menu)
$OUTPUT = "\n\t\t<center>\n\t\t<table width='90%'>\n\t\t\t<tr valign='top'>\n\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t<td align='center'>" . mkQuickLinks(ql("bank-pay-add.php", "Add Bank Payment"), ql("bank-recpt-add.php", "Add Bank Receipt"), ql("cashbook-view.php", "View Cash Book"), ql("batch-cashbook-view.php", "View Batch Cashbook")) . "\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>";
return $OUTPUT;
}
function write($_POST)
{
extract($_POST);
if (isset($back)) {
unset($_POST["back"]);
return alloc($_POST);
}
require_lib("validate");
$v = new validate();
$v->isOk($all, "num", 1, 1, "Invalid allocation.");
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
$v->isOk($date, "date", 1, 14, "Invalid Date.");
$v->isOk($out, "float", 1, 40, "Invalid out amount.");
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
$v->isOk($reference, "string", 0, 50, "Invalid Reference Name/Number.");
$v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number.");
$v->isOk($amt, "float", 1, 40, "Invalid amount.");
$v->isOk($cusid, "num", 1, 40, "Invalid customer number.");
$v->isOk($out1, "float", 0, 40, "Invalid paid amount(currant).");
$v->isOk($out2, "float", 0, 40, "Invalid paid amount(30).");
$v->isOk($out3, "float", 0, 40, "Invalid paid amount(60).");
$v->isOk($out4, "float", 0, 40, "Invalid paid amount(90).");
$v->isOk($out5, "float", 0, 40, "Invalid paid amount(120).");
if (isset($invids)) {
foreach ($invids as $key => $value) {
$v->isOk($invids[$key], "num", 1, 50, "Invalid Invoice No.");
$v->isOk($paidamt[$key], "float", 1, 40, "Invalid amount to be paid.");
}
}
if ($v->isError()) {
$confirm = $v->genErrors();
return $confirm . confirm($_POST);
}
/* get bank account id of cash on hand account IF this entry is cash */
if (($bank_acc = getbankaccid($bankid)) === false or $bankid == "0") {
//old function didnt check if cash is selected ... if(($bank_acc = getbankaccid($bankid)) === false) {
$sql = "SELECT accid FROM core.accounts WHERE accname='Cash on Hand'";
$rslt = db_exec($sql);
if (pg_num_rows($rslt) < 1) {
if ($bankid == 0) {
return "There is no 'Cash on Hand' account, there was one, but\n\t\t\t\t\t\t**s not there now, you must have deleted it, if you want\n\t\t\t\t\t\tto use cash functionality please create a 'Cash on Hand' account.";
} else {
return "Invalid bank acc.";
}
}
$bank_acc = pg_fetch_result($rslt, 0);
}
$cus = qryCustomer($cusid, "cusnum, deptid, cusname, surname");
$dept = qryDepartment($cus["deptid"], "debtacc");
$refnum = getrefnum();
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# date format
$sdate = explode("-", $date);
$sdate = $sdate[2] . "-" . $sdate[1] . "-" . $sdate[0];
$cheqnum = 0 + $cheqnum;
$pay = "";
$accdate = $sdate;
/* Paid invoices */
$invidsers = "";
$rinvids = "";
$amounts = "";
$invprds = "";
$rages = "";
/* OPTION 1 : AUTO ALLOCATE (write) */
if ($all == 0) {
# update the customer (make balance less)
$sql = "UPDATE cubit.customers SET balance = (balance - '{$amt}'::numeric(13,2))\n\t\t\t\tWHERE cusnum = '{$cus['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
$reverse_allocation_dates = "";
$reverse_allocation_amounts = "";
if (isset($invids)) {
foreach ($invids as $key => $value) {
$ii = $invids[$key];
/* OPTION 1: STOCK INVOICES */
if (!isset($itype[$ii]) && !isset($ptype[$ii])) {
$sql = "\n\t\t\t\t\t\tSELECT prd,invnum,odate \n\t\t\t\t\t\tFROM cubit.invoices\n\t\t\t\t\t\tWHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.</li>";
}
$inv = pg_fetch_array($invRslt);
$inv['invnum'] += 0;
// reduce invoice balance
$sql = "UPDATE cubit.invoices\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
# record the payment on the statement
$sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}','{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $paidamt[$key] * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t)";
if (!(isset($bulk_pay) and strlen($bulk_pay) > 0)) {
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
} else {
$reverse_allocation_dates .= "{$inv['odate']}|";
$reverse_allocation_amounts .= sprint($paidamt[$key] - $paidamt[$key] * 2) . "|";
}
custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Invoice No. {$inv['invnum']}", $paidamt[$key], "c");
$rinvids .= "|{$invids[$key]}";
$amounts .= "|{$paidamt[$key]}";
if ($inv['prd'] == "0") {
$inv['prd'] = PRD_DB;
}
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
/* OPTION 1: NONS STOCK INVOICES */
} else {
if (!isset($ptype[$ii])) {
$sql = "\n\t\t\t\t\t\tSELECT prd,invnum,descrip,age,odate \n\t\t\t\t\t\tFROM cubit.nons_invoices \n\t\t\t\t\t\tWHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.";
}
$inv = pg_fetch_array($invRslt);
$inv['invnum'] += 0;
# reduce the money that has been paid
$sql = "UPDATE cubit.nons_invoices\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
# record the payment on the statement
$sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, \n\t\t\t\t\t\t\tdiv, allocation_date\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $paidamt[$key] * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', \n\t\t\t\t\t\t\t'" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t)";
if (!(isset($bulk_pay) and strlen($bulk_pay) > 0)) {
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
} else {
$reverse_allocation_dates .= "{$inv['odate']}|";
$reverse_allocation_amounts .= sprint($paidamt[$key] - $paidamt[$key] * 2) . "|";
}
custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}", $paidamt[$key], "c");
//recordCT($paidamt[$key], $cus['cusnum'],$inv['age'],$accdate);
$rinvids .= "|{$invids[$key]}";
$amounts .= "|{$paidamt[$key]}";
$invprds .= "|0";
$rages .= "|{$inv['age']}";
$invidsers .= " - {$inv['invnum']}";
} else {
/* pos invoices */
$sqls = array();
for ($i = 1; $i <= 12; ++$i) {
$sqls[] = "\n\t\t\t\t\t\t\tSELECT '{$i}' AS prd,invid,invnum,odate \n\t\t\t\t\t\t\tFROM \"{$i}\".pinvoices \n\t\t\t\t\t\t\tWHERE invid='{$invids[$key]}' AND div='" . USER_DIV . "'";
}
$sql = implode(" UNION ", $sqls);
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.";
}
$inv = pg_fetch_array($invRslt);
// reduce the invoice balance
$sql = "UPDATE \"{$inv['prd']}\".pinvoices\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
# record the payment on the statement
$sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, div, \n\t\t\t\t\t\t\tallocation_date\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $paidamt[$key] * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment for Non Stock Invoice No. {$inv['invnum']}', '" . USER_DIV . "', \n\t\t\t\t\t\t\t'{$inv['odate']}'\n\t\t\t\t\t\t)";
if (!(isset($bulk_pay) and strlen($bulk_pay) > 0)) {
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
} else {
$reverse_allocation_dates .= "{$inv['odate']}|";
$reverse_allocation_amounts .= sprint($paidamt[$key] - $paidamt[$key] * 2) . "|";
}
custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']}", $paidamt[$key], "c");
//recordCT($paidamt[$key], $cus['cusnum'],0,$accdate);
$rinvids .= "|{$invids[$key]}";
$amounts .= "|{$paidamt[$key]}";
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
}
}
}
#record the total for the statement if bulk is selected
if (isset($bulk_pay) and strlen($bulk_pay) > 0) {
$arrtotal = sprint(array_sum($paidamt));
$sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, div, \n\t\t\t\t\t\t\tallocation_date, reverse_allocation_dates, reverse_allocation_amounts\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($arrtotal - $arrtotal * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment Received (Ref:{$reference})', '" . USER_DIV . "', \n\t\t\t\t\t\t\t'1500-01-01', '{$reverse_allocation_dates}', '{$reverse_allocation_amounts}'\n\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
}
}
$cols = grp(m("bankid", $bankid), m("trantype", "deposit"), m("date", $sdate), m("name", "{$cus['cusname']} {$cus['surname']}"), m("descript", "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"), m("cheqnum", $cheqnum), m("amount", $amt), m("banked", "no"), m("accinv", $dept["debtacc"]), m("cusnum", $cus["cusnum"]), m("rinvids", $rinvids), m("amounts", $amounts), m("invprds", $invprds), m("rages", $rages), m("reference", $reference), m("div", USER_DIV));
$dbobj = new dbUpdate("cashbook", "cubit", $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
/*
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript,
cheqnum, amount, banked, accinv, cusnum, rinvids, amounts,
invprds, rages, reference, div)
VALUES ('$bankid', 'deposit', '$sdate', '$cus[cusname] $cus[surname]',
'',
'$cheqnum', '$amt', 'no', '$dept[debtacc]', '$cus[cusnum]',
'$rinvids', '$amounts', '$invprds', '$rages', '$reference',
'".USER_DIV."')";
$Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF);
*/
writetrans($bank_acc, $dept['debtacc'], $accdate, $refnum, $amt, "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}");
db_conn('cubit');
if ($out > 0) {
/* START OPEN ITEMS */
$openstmnt = new dbSelect("open_stmnt", "cubit", grp(m("where", "balance>0 AND cusnum='{$cusid}'"), m("order", "date")));
$openstmnt->run();
$open_out = $out;
$i = 0;
$ox = "";
while ($od = $openstmnt->fetch_array()) {
if ($open_out == 0) {
continue;
}
$oid = $od['id'];
if ($open_out >= $od['balance']) {
$open_amount[$oid] = $od['balance'];
$open_out = sprint($open_out - $od['balance']);
$ox .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><input type='hidden' size='20' name='open[{$oid}]' value='{$oid}'>{$od['type']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t\t\t<td><input type='hidden' name='open_amount[{$oid}]' value='{$open_amount[$oid]}'>" . CUR . " {$open_amount[$oid]}</td>\n\t\t\t\t\t\t\t</tr>";
$Sl = "UPDATE cubit.open_stmnt SET balance=balance-'{$open_amount[$oid]}' WHERE id='{$oid}'";
$Ri = db_exec($Sl) or errDie("Unable to update statement.");
} elseif ($open_out < $od['balance']) {
$open_amount[$oid] = $open_out;
$open_out = 0;
$ox .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><input type='hidden' size='20' name='open[{$oid}]' value='{$od['id']}'>{$od['type']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t\t\t<td><input type='hidden' name='open_amount[{$oid}]' value='{$open_amount[$oid]}'>" . CUR . " {$open_amount[$oid]}</td>\n\t\t\t\t\t\t\t</tr>";
$Sl = "UPDATE cubit.open_stmnt SET balance=balance-'{$open_amount[$oid]}' WHERE id='{$oid}'";
$Ri = db_exec($Sl) or errDie("Unable to update statement.");
}
$i++;
}
if (open()) {
$bout = $out;
$out = $open_out;
if ($out > 0) {
$sql = "INSERT INTO cubit.open_stmnt(cusnum, invid, amount, balance, date, type, st, div) VALUES('{$cus['cusnum']}', '0', '-{$out}', '-{$out}', '{$sdate}', 'Payment Received', 'n', '" . USER_DIV . "')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
//$confirm .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>";
}
$out = $bout;
} else {
//$confirm .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>";}
}
}
if ($out > 0) {
recordCT($out, $cus['cusnum'], 0, $accdate);
$cols = grp(m("cusnum", $cus["cusnum"]), m("invid", 0), m("amount", -$out), m("date", $sdate), m("type", "Payment Received"), m("div", USER_DIV), m("allocation_date", $accdate));
$dbobj = new dbUpdate("stmnt", "cubit", $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
custledger($cus['cusnum'], $bank_acc, $sdate, "PAYMENT", "Payment received.", $out, "c");
}
}
/* start moving invoices */
// move invoices that are fully paid
$sql = "SELECT * FROM cubit.invoices WHERE balance=0 AND printed = 'y' AND done = 'y' AND div = '" . USER_DIV . "'";
$invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
while ($x = pg_fetch_array($invbRslt)) {
if (($prd = $x['prd']) == "0") {
$prd = PRD_DB;
}
// move invoice
$cols = grp(m("invid", $x["invid"]), m("invnum", $x["invnum"]), m("deptid", $x["deptid"]), m("cusnum", $x["cusnum"]), m("deptname", $x["deptname"]), m("cusacc", $x["cusacc"]), m("cusname", $x["cusname"]), m("surname", $x["surname"]), m("cusaddr", $x["cusaddr"]), m("cusvatno", $x["cusvatno"]), m("cordno", $x["cordno"]), m("ordno", $x["ordno"]), m("chrgvat", $x["chrgvat"]), m("terms", $x["terms"]), m("traddisc", $x["traddisc"]), m("salespn", $x["salespn"]), m("odate", $x["odate"]), m("delchrg", $x["delchrg"]), m("subtot", $x["subtot"]), m("vat", $x["vat"]), m("total", $x["total"]), m("age", $x["age"]), m("comm", $x["comm"]), m("discount", $x["discount"]), m("delivery", $x["delivery"]), m("docref", $x["docref"]), m("prd", $x["prd"]), m("delvat", $x["delvat"]), m("balance", 0), m("printed", "y"), m("done", "y"), m("username", USER_NAME), m("div", USER_DIV));
$dbobj = new dbUpdate("invoices", $prd, $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
// record movement
$cols = grp(m("invtype", "inv"), m("invnum", $x["invnum"]), m("prd", $x["prd"]), m("docref", $x["docref"]), m("div", USER_DIV));
$dbobj->setTable("movinv", "cubit");
$dbobj->setOpt($cols);
$dbobj->run();
$dbobj->free();
// move invoice items
$inv_items = new dbSelect("inv_items", "cubit", grp(m("where", wgrp(m("invid", $x["invid"]), m("div", USER_DIV)))));
$inv_items->run();
while ($xi = $inv_items->fetch_array()) {
$xi['vatcode'] += 0;
$xi['account'] += 0;
$xi['del'] += 0;
$cols = grp(m("invid", $x["invid"]), m("whid", $xi["whid"]), m("stkid", $xi["stkid"]), m("qty", $xi["qty"]), m("unitcost", $xi["unitcost"]), m("amt", $xi["amt"]), m("disc", $xi["disc"]), m("discp", $xi["discp"]), m("vatcode", $xi["vatcode"]), m("account", $xi["account"]), m("description", $xi["description"]), m("del", $xi["del"]), m("noted", $xi["noted"]), m("serno", $xi["serno"]), m("div", USER_DIV));
$dbobj->setTable("inv_items", $prd);
$dbobj->setOpt($cols);
$dbobj->run();
$dbobj->free();
}
/* remove invoice from cubit schema */
$dbobj = new dbDelete("invoices", "cubit", wgrp(m("invid", $x["invid"]), m("div", USER_DIV)));
$dbobj->run();
$dbobj->setTable("inv_items", "cubit");
$dbobj->run();
}
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
$cashbook_id = pglib_lastid("cashbook", "cashid");
if (isset($print_recpt) and $print_recpt == "yes") {
$showreceipt = "<script>printer ('bank/bank-recpt-inv-print.php?recid={$cashbook_id}');</script>";
} else {
$showreceipt = "";
}
// status report
$write = "\n\t\t{$showreceipt}\n\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t<tr>\n\t\t\t\t<th>Bank Receipt</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Bank Receipt added to cash book.</td>\n\t\t\t</tr>\n\t\t</table>";
$OUTPUT = "\n\t\t<center>\n\t\t<table width='90%'>\n\t\t\t<tr valign='top'>\n\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t<td align='center'>" . mkQuickLinks(ql("bank-pay-add.php", "Add Bank Payment"), ql("bank-recpt-add.php", "Add Bank Receipt"), ql("bank-recpt-inv.php", "Add Customer Payment"), ql("cashbook-view.php", "View Cash Book")) . "\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>";
return $OUTPUT;
}
function write($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
$v->isOk($from_day, "num", 1, 2, "Invalid Day for the 'From' date.");
$v->isOk($from_month, "num", 1, 2, "Invalid month for the 'From' date..");
$v->isOk($from_year, "num", 1, 4, "Invalid year for the 'From' date..");
$v->isOk($to_day, "num", 1, 2, "Invalid Day for the 'To' date.");
$v->isOk($to_month, "num", 1, 2, "Invalid month for the 'To' date..");
$v->isOk($to_year, "num", 1, 4, "Invalid year for the 'To' date..");
# lets mix the date
$from = $from_day . "-" . $from_month . "-" . $from_year;
$to = $to_day . "-" . $to_month . "-" . $to_year;
# 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;
}
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# Get bank details
$bankRslt = get("cubit", "accname,bankname,fcid", "bankacct", "bankid", $bankid);
$bank = pg_fetch_array($bankRslt);
$Sl = "SELECT * FROM currency WHERE fcid='{$bank['fcid']}'";
$Ry = db_exec($Sl) or errDie("Unable to get currency");
if (pg_numrows($Ry) > 0) {
$curdata = pg_fetch_array($Ry);
$fc = $curdata['symbol'];
}
$s1 = "";
$s2 = "";
$s3 = "";
$s4 = "";
$s5 = "";
$order = "order by date desc, cheqnum asc";
if (isset($order)) {
if ($order == "order by date desc, cheqnum asc") {
$s2 = "selected";
} elseif ($order == "order by date desc, cheqnum desc") {
$s3 = "selected";
} elseif ($order == "order by cheqnum asc") {
$s4 = "selected";
} elseif ($order == "order by cheqnum desc") {
$s5 = "selected";
} else {
$s1 = "selected";
}
} else {
$s1 = "selected";
}
// Set up table to display in
# Receipts
$OUTPUT = "\n\t<center>\n\t<h3>Batch Cash Book Entries<br><br>Account : {$bank['accname']} - {$bank['bankname']}<br>Period : {$from} to {$to}</h3>\n\t<table " . TMPL_tblDflts . ">\n\t<form action='" . SELF . "' method='POST' name='form'>\n\t\t<input type='hidden' name='key' value='viewcash'>\n\t\t<input type='hidden' name='bankid' value='{$bankid}'>\n\t\t<input type='hidden' name='from_day' value='{$from_day}'>\n\t\t<input type='hidden' name='from_month' value='{$from_month}'>\n\t\t<input type='hidden' name='from_year' value='{$from_year}'>\n\t\t<input type='hidden' name='to_day' value='{$to_day}'>\n\t\t<input type='hidden' name='to_month' value='{$to_month}'>\n\t\t<input type='hidden' name='to_year' value='{$to_year}'>\n\t</table>\n\t<p>\n\t<table " . TMPL_tblDflts . " width='95%'>\n\t\t<tr>\n\t\t\t<td colspan='7'><h4>Receipts</h4></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th> Date</th>\n\t\t\t<th>Bank Account Name</th>\n\t\t\t<th>Cheque Number</th>\n\t\t\t<th>Received From : </th>\n\t\t\t<th>Description</th>\n\t\t\t<th>Ledger Account</th>\n\t\t\t<th>Amount</th>\n\t\t</tr>";
$rtotal = 0;
// Received total amount
// Connect to database
db_Connect();
# date format
$from = explode("-", $from);
$from = $from[2] . "-" . $from[1] . "-" . $from[0];
$to = explode("-", $to);
$to = $to[2] . "-" . $to[1] . "-" . $to[0];
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
# Get hook account number
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
# Check if link exists
if (pg_numrows($rslt) < 1) {
return "<li class='err'> ERROR : The bank account that you selected doesn't appear to have an account linked to it.";
}
$banklnk = pg_fetch_array($rslt);
db_Connect();
$sql = "SELECT * FROM batch_cashbook WHERE date >= '{$from}' AND date <= '{$to}' AND trantype='deposit' AND bankid='{$bankid}' AND div = '" . USER_DIV . "' {$order}";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
$numrows = pg_numrows($accntRslt);
if ($numrows < 1) {
//$OUTPUT .= "<tr><td colspan='7' align='center'><li class='err'>There are no batch Payments/cheques received for the selected period.</td></tr>";
} else {
for ($i = 0; $i < $numrows; $i++) {
$accnt = pg_fetch_array($accntRslt, $i);
if (!isset($pro[$accnt['cashid']])) {
continue;
}
// if($accnt['bt']=="receipt") {
$refnum = getrefnum();
if (strlen($accnt['accids']) > 0) {
$accids = explode("|", $accnt['accids']);
$vatcodes = explode("|", $accnt['vatcodes']);
$amounts = explode("|", $accnt['amounts']);
$vats = explode("|", $accnt['vats']);
$chrgvats = explode("|", $accnt['chrgvats']);
$refnum = getrefnum();
$descript = $accnt['descript'];
//$date = date("Y-m-d");
$date = $accnt['date'];
foreach ($amounts as $key => $amount) {
# SQL Array Rule: Thou shalt skip Zero Reference
if ($key < 1) {
continue;
}
$accid = $accids[$key];
$vat = $vats[$key];
$chrgvat = $chrgvats[$key];
$amount -= $vat;
$vatcode = $vatcodes[$key];
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
if ($accnt['trantype'] != "deposit") {
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "a");
# DT(account involved), CT(bank)
// writetrans($accid, $banklnk['accnum'], $date, $refnum, ($amount-$vat), $descript);
writetrans($accid, $banklnk['accnum'], $date, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $banklnk['accnum'], $date, $refnum, $vat, $descript);
vatr($vd['id'], $accnt['date'], "INPUT", $vd['code'], $refnum, $accnt['descript'], -$amount, -$vat);
}
} else {
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
# DT(bank), CT(account invoilved)
// writetrans($banklnk['accnum'], $accid, $date, $refnum, ($amount-$vat), $descript);
writetrans($banklnk['accnum'], $accid, $date, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
vatr($vd['id'], $accnt['date'], "OUTPUT", $vd['code'], $refnum, $accnt['descript'], $amount, $vat);
writetrans($banklnk['accnum'], $vatacc, $date, $refnum, $vat, $descript);
}
}
}
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accids, amounts, chrgvats, vats, reference, div) VALUES ('{$accnt['bankid']}', 'deposit', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', 'no', '{$accnt['accids']}', '{$accnt['amounts']}', '{$accnt['chrgvats']}', '{$accnt['vats']}', '{$accnt['reference']}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
db_connect();
$Sl = "DELETE FROM batch_cashbook WHERE cashid='{$accnt['cashid']}'";
$Ri = db_exec($Sl);
} else {
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$accnt['vatcode']}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
# record the payment record
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, vat, chrgvat, banked, accinv, div, vatcode, reference) VALUES ('{$bankid}', 'deposit', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', '{$accnt['vat']}', '{$accnt['chrgvat']}', 'no', '{$accnt['accinv']}', '" . USER_DIV . "', '{$accnt['vatcode']}', '{$accnt['reference']}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
vatr($vd['id'], $accnt['date'], "OUTPUT", $vd['code'], $refnum, $accnt['descript'], $accnt['amount'], $accnt['vat']);
# DT(account involved), CT(bank)
writetrans($banklnk['accnum'], $accnt['accinv'], $accnt['date'], $refnum, $accnt['amount'] - $accnt['vat'], $accnt['descript']);
if ($accnt['vat'] != 0) {
# DT(Vat), CT(Bank)
writetrans($banklnk['accnum'], $vatacc, $accnt['date'], $refnum, $accnt['vat'], $accnt['descript']);
}
// }
db_connect();
$Sl = "DELETE FROM batch_cashbook WHERE cashid='{$accnt['cashid']}'";
$Ri = db_exec($Sl);
continue;
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "<a href=# onClick=openSmallWindow('multi-acc-popup.php?cashid={$accnt['cashid']}')>Multiple Accounts</a>";
$acc['accname'] = "";
$acc['accno'] = "";
} else {
# Get account name for the account involved
$AccRslt = get("core", "accname, topacc, accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
$acc['accno'] = "{$acc['topacc']}/{$acc['accnum']}";
}
# Get account name for bank account
db_connect();
$sql = "SELECT accname,btype FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
$rtotal += $accnt['amount'];
// add to rtotal
$accnt['amount'] = sprint($accnt['amount']);
$accnt['date'] = ext_rdate($accnt['date']);
if ($bname['btype'] != "loc") {
$ex = "/ {$fc} {$accnt['famount']}";
} else {
$ex = "";
}
$OUTPUT .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td>{$accnt['date']}</td>\n\t\t\t\t\t\t\t\t<td align='center'>{$bname['accname']}</td>\n\t\t\t\t\t\t\t\t<td align='center'>{$accnt['cheqnum']}</td>\n\t\t\t\t\t\t\t\t<td align='center'>{$accnt['name']}</td>\n\t\t\t\t\t\t\t\t<td>{$accnt['descript']}</td>\n\t\t\t\t\t\t\t\t<td>{$acc['accno']} {$acc['accname']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$accnt['amount']} {$ex}</td>";
if ($accnt['banked'] == "no" && $accnt['opt'] != 'n') {
//$OUTPUT .= "<td><a href='batch-enytry-delete.php?id=$accnt[cashid]'>Delete</td>";
$OUTPUT .= "<input type='hidden' name='pro[" . $accnt['cashid'] . "]' value='1'>";
// $OUTPUT .= "<td><a href='../bank/cheq-cancel.php?cashid=$accnt[cashid]'>Cancel</td>";
}
$OUTPUT .= "</tr>";
}
}
# print the total
$OUTPUT .= "\n\t\t\t<tr class='" . bg_class() . "''>\n\t\t\t\t<td colspan='6'><b>Total Receipts</b></td>\n\t\t\t\t<td><b>" . CUR . " " . sprintf("%01.2f", $rtotal) . "</b></td>\n\t\t\t</tr>";
}
# Seperate the tables with two rows
$OUTPUT .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='7'><br></td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td colspan='7'><br></td>\n\t\t\t\t\t</tr>";
# Payments
$OUTPUT .= "\n\t\t<tr>\n\t\t\t<td colspan='7'><h4>Payments</h4></td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Date</th>\n\t\t\t<th>Bank Account Name</th>\n\t\t\t<th>Cheque Number</th>\n\t\t\t<th>Paid to: </th>\n\t\t\t<th>Description</th>\n\t\t\t<th>Ledger Account</th>\n\t\t\t<th>Amount</th>\n\t\t</tr>";
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
# Get hook account number
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
# Check if link exists
if (pg_numrows($rslt) < 1) {
return "<li class='err'> ERROR : The bank account that you selected doesn't appear to have an account linked to it.";
}
$banklnk = pg_fetch_array($rslt);
$ptotal = 0;
// payments total
// Connect to database
db_Connect();
$sql = "SELECT * FROM batch_cashbook WHERE date >= '{$from}' AND date <= '{$to}' AND trantype='withdrawal' AND bankid='{$bankid}' AND div = '" . USER_DIV . "' {$order}";
$accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve bank deposits details from database.", SELF);
if (pg_numrows($accntRslt) < 1) {
//$OUTPUT .= "<tr><td colspan=7 align=center><li class=err>There are batch no Payments made for the selected period.</td></tr>";
} else {
# Display all bank payments
for ($i = 0; $accnt = pg_fetch_array($accntRslt); $i++) {
if (!isset($pro[$accnt['cashid']])) {
continue;
}
if ($accnt['bt'] == "payment") {
$refnum = getrefnum();
if (strlen($accnt['accids']) > 0) {
$accids = explode("|", $accnt['accids']);
$vatcodes = explode("|", $accnt['vatcodes']);
$amounts = explode("|", $accnt['amounts']);
$vats = explode("|", $accnt['vats']);
$chrgvats = explode("|", $accnt['chrgvats']);
$refnum = getrefnum();
$descript = $accnt['descript'];
//$date = date("Y-m-d");
$date = $accnt['date'];
foreach ($amounts as $key => $amount) {
# SQL Array Rule: Thou shalt skip Zero Reference
if ($key < 1) {
continue;
}
$accid = $accids[$key];
$vat = $vats[$key];
$chrgvat = $chrgvats[$key];
$amount -= $vat;
$vatcode = $vatcodes[$key];
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
if ($accnt['trantype'] != "deposit") {
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "a");
# DT(account involved), CT(bank)
// writetrans($accid, $banklnk['accnum'], $date, $refnum, $amount-$vat, $descript);
writetrans($accid, $banklnk['accnum'], $date, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $banklnk['accnum'], $date, $refnum, $vat, $descript);
vatr($vd['id'], $accnt['date'], "INPUT", $vd['code'], $refnum, $accnt['descript'], -$amount, -$vat);
}
} else {
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
# DT(bank), CT(account invoilved)
writetrans($banklnk['accnum'], $accid, $date, $refnum, $amount, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($banklnk['accnum'], $vatacc, $date, $refnum, $vat, $descript);
vatr($vd['id'], $accnt['date'], "OUTPUT", $vd['code'], $refnum, $accnt['descript'], $amount, $vat);
}
}
}
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accids, amounts, chrgvats, vats, div, vatcode, reference) VALUES ('{$accnt['bankid']}', 'withdrawal', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', 'no', '{$accnt['accids']}', '{$accnt['amounts']}', '{$accnt['chrgvats']}', '{$accnt['vats']}', '" . USER_DIV . "','{$accnt['vatcode']}', '{$accnt['reference']}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
} else {
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$accnt['vatcode']}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
# Record the payment record
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, vat, chrgvat, banked, accinv, div, vatcode, reference) VALUES ('{$bankid}', 'withdrawal', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', '{$accnt['vat']}', '{$accnt['chrgvat']}', 'no', '{$accnt['accinv']}', '" . USER_DIV . "','{$accnt['vatcode']}', '{$accnt['reference']}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
vatr($vd['id'], $accnt['date'], "INPUT", $vd['code'], $refnum, $accnt['descript'], -$accnt['amount'], -$accnt['vat']);
# DT(account involved), CT(bank)
writetrans($accnt['accinv'], $banklnk['accnum'], $accnt['date'], $refnum, $accnt['amount'] - $accnt['vat'], $accnt['descript']);
if ($accnt['vat'] != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $banklnk['accnum'], $accnt['date'], $refnum, $accnt['vat'], $accnt['descript']);
}
}
db_connect();
$Sl = "DELETE FROM batch_cashbook WHERE cashid='{$accnt['cashid']}'";
$Ri = db_exec($Sl);
continue;
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "<a href=# onClick=openSmallWindow('multi-acc-popup.php?cashid={$accnt['cashid']}')>Multiple Accounts</a>";
$acc['accno'] = "";
} else {
# get account name for the account involved
$AccRslt = get("core", "accname, topacc, accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
$acc['accno'] = "{$acc['topacc']}/{$acc['accnum']}";
}
# get account name for bank account
db_connect();
$sql = "SELECT accname,btype FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
$ptotal += $accnt['amount'];
//add to total
$accnt['amount'] = sprint($accnt['amount']);
$accnt['date'] = ext_rdate($accnt['date']);
if ($bname['btype'] != "loc") {
$ex = "/ {$fc} {$accnt['famount']}";
} else {
$ex = "";
}
$OUTPUT .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$accnt['date']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$bname['accname']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['cheqnum']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['name']}</td>\n\t\t\t\t\t\t\t<td>{$accnt['descript']}</td>\n\t\t\t\t\t\t\t<td>{$acc['accno']} {$acc['accname']}</td>\n\t\t\t\t\t\t\t<td>" . CUR . " {$accnt['amount']} {$ex}</td>";
if ($accnt['banked'] == "no" && $accnt['opt'] != 'n') {
//$OUTPUT .= "<td><a href='batch-enytry-delete.php?id=$accnt[cashid]'>Delete</td>";
$OUTPUT .= "<input type='hidden' name='pro[" . $accnt['cashid'] . "]' value='1'>";
//$OUTPUT .= "<td><a href='../bank/cheq-return.php?cashid=$accnt[cashid]'>Returned/Unpaid</td>";
// $OUTPUT .= "<td><a href='../bank/cheq-cancel.php?cashid=$accnt[cashid]'>Cancel</td>";
}
$OUTPUT .= "</tr>";
} elseif ($accnt['bt'] == "transfer") {
$refnum = getrefnum();
extract($accnt);
db_connect();
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$bankid}' AND div = '" . USER_DIV . "'";
$fbankRslt = db_exec($sql);
$fbank = pg_fetch_array($fbankRslt);
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$rid}' AND div = '" . USER_DIV . "'";
$tbankRslt = db_exec($sql);
$tbank = pg_fetch_array($tbankRslt);
$faccid = getbankaccid($bankid);
$taccid = getbankaccid($rid);
# write trans
writetrans($taccid, $faccid, $date, $refnum, $amount, $descript);
# Record the payment record
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, div, reference) VALUES ('{$bankid}', 'withdrawal', '{$date}', '{$tbank['accname']} - {$tbank['bankname']}', '{$descript}', '{$cheqnum}', '{$amount}', 'no', '{$taccid}', '" . USER_DIV . "', '{$reference}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$lcashid = pglib_lastid("cashbook", "cashid");
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, div, reference) VALUES ('{$rid}', 'deposit', '{$date}', '{$fbank['accname']} - {$fbank['bankname']}', '{$descript}', '{$cheqnum}', '{$amount}', 'no', '{$faccid}', '" . USER_DIV . "', '{$reference}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$lcashid2 = pglib_lastid("cashbook", "cashid");
# restore link
$sql = "UPDATE cashbook SET lcashid = '{$lcashid2}' WHERE cashid = '{$lcashid}'";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$sql = "UPDATE cashbook SET lcashid = '{$lcashid}' WHERE cashid = '{$lcashid2}'";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
db_connect();
$Sl = "DELETE FROM batch_cashbook WHERE cashid='{$accnt['cashid']}'";
$Ri = db_exec($Sl);
} else {
$refnum = getrefnum();
if (strlen($accnt['accids']) > 0) {
$accids = explode("|", $accnt['accids']);
$vatcodes = explode("|", $accnt['vatcodes']);
$amounts = explode("|", $accnt['amounts']);
$vats = explode("|", $accnt['vats']);
$chrgvats = explode("|", $accnt['chrgvats']);
$refnum = getrefnum();
$descript = $accnt['descript'];
$date = $accnt["date"];
foreach ($amounts as $key => $amount) {
# SQL Array Rule: Thou shalt skip Zero Reference
if ($key < 1) {
continue;
}
$accid = $accids[$key];
$vat = $vats[$key];
$chrgvat = $chrgvats[$key];
//$amount -= $vat;
$vatcode = $vatcodes[$key];
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
if ($accnt['trantype'] != "deposit") {
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "a");
# DT(account involved), CT(bank)
writetrans($accid, $banklnk['accnum'], $date, $refnum, $amount - $vat, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $banklnk['accnum'], $date, $refnum, $vat, "{$descript}");
vatr($vd['id'], $accnt['date'], "INPUT", $vd['code'], $refnum, $accnt['descript'], -$amount, -$vat);
}
} else {
$vatacc = gethook("accnum", "salesacc", "name", "VAT");
# DT(bank), CT(account invoilved)
writetrans($banklnk['accnum'], $accid, $date, $refnum, $amount - $vat, $descript);
if ($vat != 0) {
# DT(Vat), CT(Bank)
writetrans($banklnk['accnum'], $vatacc, $date, $refnum, $vat, "{$descript}");
vatr($vd['id'], $accnt['date'], "OUTPUT", $vd['code'], $refnum, $accnt['descript'], $amount, $vat);
}
}
}
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accids, amounts, chrgvats, vats, div, vatcode, reference) VALUES ('{$accnt['bankid']}', 'withdrawal', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', 'no', '{$accnt['accids']}', '{$accnt['amounts']}', '{$accnt['chrgvats']}', '{$accnt['vats']}', '" . USER_DIV . "','{$accnt['vatcode']}', '{$accnt['reference']}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
} else {
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$accnt['vatcode']}'";
$Ri = db_exec($Sl) or errDie("Unable to get vat codes");
$vd = pg_fetch_array($Ri);
# Record the payment record
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, vat, chrgvat, banked, accinv, div, vatcode, reference) VALUES ('{$bankid}', 'withdrawal', '{$accnt['date']}', '{$accnt['name']}', '{$accnt['descript']}', '{$accnt['cheqnum']}', '{$accnt['amount']}', '{$accnt['vat']}', '{$accnt['chrgvat']}', 'no', '{$accnt['accinv']}', '" . USER_DIV . "','{$accnt['vatcode']}', '{$accnt['reference']}')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
vatr($vd['id'], $accnt['date'], "INPUT", $vd['code'], $refnum, $accnt['descript'], -$accnt['amount'], -$accnt['vat']);
# DT(account involved), CT(bank)
writetrans($accnt['accinv'], $banklnk['accnum'], $accnt['date'], $refnum, $accnt['amount'] - $accnt['vat'], $accnt['descript']);
if ($accnt['vat'] != 0) {
# DT(Vat), CT(Bank)
writetrans($vatacc, $banklnk['accnum'], $accnt['date'], $refnum, $accnt['vat'], $accnt['descript']);
}
}
db_connect();
$Sl = "DELETE FROM batch_cashbook WHERE cashid='{$accnt['cashid']}'";
$Ri = db_exec($Sl);
continue;
if (strlen($accnt['accids']) > 0) {
$acc['accname'] = "<a href=# onClick=openSmallWindow('multi-acc-popup.php?cashid={$accnt['cashid']}')>Multiple Accounts</a>";
$acc['accno'] = "";
} else {
# get account name for the account involved
$AccRslt = get("core", "accname, topacc, accnum", "accounts", "accid", $accnt['accinv']);
$acc = pg_fetch_array($AccRslt);
$acc['accno'] = "{$acc['topacc']}/{$acc['accnum']}";
}
# get account name for bank account
db_connect();
$sql = "SELECT accname,btype FROM bankacct WHERE bankid= '{$accnt['bankid']}' AND div = '" . USER_DIV . "'";
$bnameRslt = db_exec($sql);
$bname = pg_fetch_array($bnameRslt);
$ptotal += $accnt['amount'];
//add to total
$accnt['amount'] = sprint($accnt['amount']);
$accnt['date'] = ext_rdate($accnt['date']);
if ($bname['btype'] != "loc") {
$ex = "/ {$fc} {$accnt['famount']}";
} else {
$ex = "";
}
$OUTPUT .= "\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>{$accnt['date']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$bname['accname']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['cheqnum']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$accnt['name']}</td>\n\t\t\t\t\t\t\t<td>{$accnt['descript']}</td>\n\t\t\t\t\t\t\t<td>{$acc['accno']} {$acc['accname']}</td>\n\t\t\t\t\t\t\t<td>" . CUR . " {$accnt['amount']} {$ex}</td>";
if ($accnt['banked'] == "no" && $accnt['opt'] != 'n') {
//$OUTPUT .= "<td><a href='batch-enytry-delete.php?id=$accnt[cashid]'>Delete</td>";
$OUTPUT .= "<input type='hidden' name='pro[" . $accnt['cashid'] . "]' value='1'>";
//$OUTPUT .= "<td><a href='../bank/cheq-return.php?cashid=$accnt[cashid]'>Returned/Unpaid</td>";
// $OUTPUT .= "<td><a href='../bank/cheq-cancel.php?cashid=$accnt[cashid]'>Cancel</td>";
}
$OUTPUT .= "</tr>";
}
}
# print the total
$OUTPUT .= "\n\t\t\t<tr class='" . bg_class() . "''>\n\t\t\t\t<td colspan='6'><b>Total Payments</b></td>\n\t\t\t\t<td><b>" . CUR . " " . sprintf("%01.2f", $ptotal) . "</b></td>\n\t\t\t</tr>";
}
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
$OUTPUT = "\n\t\t\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Done</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td>Batch entries have been processed.</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table><br>" . mkQuickLinks(ql("../core/acc-new2.php", "Add New Account"));
return $OUTPUT;
}
function write($_POST)
{
extract($_POST);
if (isset($back)) {
unset($_POST["back"]);
return alloc($_POST);
}
require_lib("validate");
$v = new validate();
$v->isOk($all, "num", 1, 1, "Invalid allocation.");
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
$v->isOk($date, "date", 1, 14, "Invalid Date.");
$v->isOk($out, "float", 1, 40, "Invalid out amount.");
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
$v->isOk($reference, "string", 0, 50, "Invalid Reference Name/Number.");
$v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number.");
$v->isOk($amt, "float", 1, 40, "Invalid amount.");
$v->isOk($setamt, "float", 1, 40, "Invalid Settlement Amount.");
$v->isOk($setvat, "string", 1, 10, "Invalid Settlement VAT Option.");
$v->isOk($setvatcode, "string", 1, 40, "Invalid Settlement VAT code");
$v->isOk($cusid, "num", 1, 40, "Invalid customer number.");
$v->isOk($out1, "float", 0, 40, "Invalid paid amount(current).");
$v->isOk($out2, "float", 0, 40, "Invalid paid amount(30).");
$v->isOk($out3, "float", 0, 40, "Invalid paid amount(60).");
$v->isOk($out4, "float", 0, 40, "Invalid paid amount(90).");
$v->isOk($out5, "float", 0, 40, "Invalid paid amount(120).");
$v->isOk($overpay, "float", 1, 20, "Invalid Overpay Amount.");
if (isset($invids)) {
foreach ($invids as $key => $value) {
$v->isOk($invids[$key], "num", 1, 50, "Invalid Invoice No.");
$v->isOk($paidamt[$key], "float", 1, 40, "Invalid amount to be paid.");
$v->isOk($stock_setamt[$key], "float", 1, 40, "Invalid Settlement Discount Amount");
}
}
if ($v->isError()) {
$confirm = $v->genErrors();
return $confirm . confirm($_POST);
}
/* get bank account id of cash on hand account IF this entry is cash */
if (($bank_acc = getbankaccid($bankid)) === false or $bankid == "0") {
//old function didnt check if cash is selected ... if(($bank_acc = getbankaccid($bankid)) === false) {
$sql = "SELECT accid FROM core.accounts WHERE accname='Cash on Hand'";
$rslt = db_exec($sql);
if (pg_num_rows($rslt) < 1) {
if ($bankid == 0) {
return "There is no 'Cash on Hand' account, there was one, but\n\t\t\t\t\t**s not there now, you must have deleted it, if you want\n\t\t\t\t\tto use cash functionality please create a 'Cash on Hand' account.";
} else {
return "Invalid bank acc.";
}
}
$bank_acc = pg_fetch_result($rslt, 0);
}
$cus = qryCustomer($cusid, "cusnum, deptid, cusname, surname");
$dept = qryDepartment($cus["deptid"], "debtacc");
$refnum = getrefnum();
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# date format
$sdate = explode("-", $date);
$_SESSION["global_day"] = $sdate[2];
$_SESSION["global_month"] = $sdate[1];
$_SESSION["global_year"] = $sdate[0];
// $sdate = $sdate[2]."-".$sdate[1]."-".$sdate[0];
$sdate = "{$date_year}-{$date_month}-{$date_day}";
$cheqnum = 0 + $cheqnum;
$pay = "";
$accdate = $sdate;
// $accdate = "$date_year-$date_month-$date_day";
/* Paid invoices */
$invidsers = "";
$rinvids = "";
$amounts = "";
$invprds = "";
$rages = "";
$setamts = "";
#get settlement accid
$get_setacc = "SELECT accid FROM accounts WHERE accname = 'Debtors Settlement Discount'";
$run_setacc = db_exec($get_setacc) or errDie("Unable to get settlement account information");
$setaccid = pg_fetch_result($run_setacc, 0, 0);
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "VAT");
$amt += $overpay;
/* OPTION 3 : ALLOCATE TO EACH INVOICE (confirm) */
if ($all == 2) {
$sql = "UPDATE cubit.customers SET balance = (balance - '{$amt}'::numeric(16,2)) WHERE cusnum = '{$cus['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
if (isset($invids)) {
foreach ($invids as $key => $value) {
$ii = $invids[$key];
# some logic ...
# because the customer account should be 0 when paid fully, we need
# to also deduct the settlement amount ...
$paidamt[$key] = $paidamt[$key] + $stock_setamt[$key];
# with the amount added to the paid amount, we tract it using a new
# seperate setamt db column
if (!isset($itype[$key]) && !isset($ptype[$key])) {
$sql = "SELECT prd,invnum,odate FROM cubit.invoices WHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.</li>";
}
$inv = pg_fetch_array($invRslt);
// reduce invoice balance
$sql = "\n\t\t\t\t\t\tUPDATE cubit.invoices\n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(16,2))\n\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "\n\t\t\t\t\t\tUPDATE cubit.open_stmnt\n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(16,2))\n\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
# record the payment on the statement
$sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, type, div, allocation_date, docref, \n\t\t\t\t\t\t\tallocation_balance\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $stock_setamt[$key] - ($paidamt[$key] - $stock_setamt[$key]) * 2) . "', \n\t\t\t\t\t\t\t'{$sdate}', 'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}', '{$reference}', \n\t\t\t\t\t\t\t'" . abs($paidamt[$key] - $stock_setamt[$key] - ($paidamt[$key] - $stock_setamt[$key]) * 2) . "'\n\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
#record the settlement discount on the statement
if ($stock_setamt[$key] > 0) {
$sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, \n\t\t\t\t\t\t\t\tdate, type, \n\t\t\t\t\t\t\t\tdiv, allocation_date, docref, allocation_balance\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', '" . ($stock_setamt[$key] - $stock_setamt[$key] * 2) . "', \n\t\t\t\t\t\t\t\t'{$sdate}', 'Settlement Discount for Invoice No.{$inv['invnum']} Ref. {$refnum}', \n\t\t\t\t\t\t\t\t'" . USER_DIV . "', '{$inv['odate']}', '{$reference}', '" . abs($stock_setamt[$key] - $stock_setamt[$key] * 2) . "'\n\t\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
}
#deduct setamt for records ...
custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Invoice No. {$inv['invnum']}", $paidamt[$key] - $stock_setamt[$key], "c");
db_connect();
$rinvids .= "|{$invids[$key]}";
$amounts .= "|{$paidamt[$key]}";
if ($inv['prd'] == "0") {
$inv['prd'] = PRD_DB;
}
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
$setamts .= "|{$stock_setamt[$key]}";
} elseif (!isset($ptype[$key])) {
$sql = "\n\t\t\t\t\t\tSELECT prd,invnum,descrip,age,odate \n\t\t\t\t\t\tFROM cubit.nons_invoices \n\t\t\t\t\t\tWHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.</li>";
}
$inv = pg_fetch_array($invRslt);
// reduce the invoice balance
$sql = "\n\t\t\t\t\t\tUPDATE cubit.nons_invoices \n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(16,2)) \n\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "\n\t\t\t\t\t\tUPDATE cubit.open_stmnt \n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(16,2)) \n\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
if (!isset($inv['odate']) or strlen($inv['odate']) < 1) {
$inv['odate'] = $sdate;
}
// add payment to statement
$sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, \n\t\t\t\t\t\t\tdate, type, \n\t\t\t\t\t\t\tdiv, allocation_date, docref, allocation_balance\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $stock_setamt[$key] - ($paidamt[$key] - $stock_setamt[$key]) * 2) . "', \n\t\t\t\t\t\t\t'{$sdate}', 'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', \n\t\t\t\t\t\t\t'" . USER_DIV . "', '{$inv['odate']}', '{$reference}', '" . abs($paidamt[$key] - $stock_setamt[$key] - ($paidamt[$key] - $stock_setamt[$key]) * 2) . "'\n\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
#record the settlement discount on the statement
if ($stock_setamt[$key] > 0) {
$sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, \n\t\t\t\t\t\t\t\tdate, type, \n\t\t\t\t\t\t\t\tdiv, allocation_date, docref, allocation_balance\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', '" . ($stock_setamt[$key] - $stock_setamt[$key] * 2) . "', \n\t\t\t\t\t\t\t\t'{$sdate}', 'Settlement Discount for Invoice No.{$inv['invnum']} Ref. {$refnum}', \n\t\t\t\t\t\t\t\t'" . USER_DIV . "', '{$inv['odate']}', '{$reference}', '" . abs($stock_setamt[$key] - $stock_setamt[$key] * 2) . "'\n\t\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
}
custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}", $paidamt[$key], "c");
db_connect();
//recordCT($paidamt[$key], $cus['cusnum'],$inv['age'],$accdate);
$rinvids .= "|{$invids[$key]}";
$amounts .= "|{$paidamt[$key]}";
$invprds .= "|0";
$rages .= "|{$inv['age']}";
$invidsers .= " - {$inv['invnum']}";
$setamts .= "|{$stock_setamt[$key]}";
} else {
/* pos invoices */
$sqls = array();
for ($i = 1; $i <= 12; ++$i) {
$sqls[] = "\n\t\t\t\t\t\t\tSELECT '{$i}' AS prd,invid,invnum,odate \n\t\t\t\t\t\t\tFROM \"{$i}\".pinvoices \n\t\t\t\t\t\t\tWHERE invid='{$invids[$key]}' AND div='" . USER_DIV . "'";
}
$sql = implode(" UNION ", $sqls);
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.</li>";
}
$inv = pg_fetch_array($invRslt);
// reduce the invoice balance
$sql = "\n\t\t\t\t\t\tUPDATE \"{$inv['prd']}\".pinvoices \n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(16,2)) \n\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "\n\t\t\t\t\t\tUPDATE cubit.open_stmnt \n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(16,2)) \n\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
// add payment to statement
$sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\t\ttype, div, \n\t\t\t\t\t\t\tallocation_date, docref, allocation_balance\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', '" . ($paidamt[$key] - $stock_setamt[$key]) * -1 . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment for POS Invoice No. {$inv['invnum']}', '" . USER_DIV . "', \n\t\t\t\t\t\t\t'{$inv['odate']}', '{$reference}', '" . abs(($paidamt[$key] - $stock_setamt[$key]) * -1) . "'\n\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
#record the settlement discount on the statement
if ($stock_setamt[$key] > 0) {
$sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\t\ttype, \n\t\t\t\t\t\t\t\tdiv, allocation_date, docref, allocation_balance\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t\t'" . ($stock_setamt[$key] - $stock_setamt[$key] * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t\t'Settlement Discount for Invoice No.{$inv['invnum']} Ref. {$refnum}', \n\t\t\t\t\t\t\t\t'" . USER_DIV . "', '{$inv['odate']}', '{$reference}', '" . abs($stock_setamt[$key] - $stock_setamt[$key] * 2) . "'\n\t\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
}
custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for POS Invoice No. {$inv['invnum']}", $paidamt[$key], "c");
//recordCT($paidamt[$key], $cus['cusnum'],"0",$accdate);
$rinvids .= "|{$invids[$key]}";
$amounts .= "|{$paidamt[$key]}";
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
$setamts .= "|{$stock_setamt[$key]}";
}
}
}
if (open()) {
db_conn('cubit');
$Sl = "SELECT * FROM cubit.open_stmnt WHERE balance>0 AND cusnum='{$cusid}' ORDER BY date";
$Ri = db_exec($Sl) or errDie("Unable to get open items.");
//$open_out=$out;
$ox = "";
$i = 0;
while ($od = pg_fetch_array($Ri)) {
$oid = $od['id'];
if (!isset($open_amount[$oid]) || $open_amount[$oid] == 0) {
continue;
}
$ox .= "\n\t\t\t\t\t<input type='hidden' size='20' name='open[{$oid}]' value='{$oid}'>\n\t\t\t\t\t<input type='hidden' name='open_amount[{$oid}]' value='{$open_amount[$oid]}'>\n\t\t\t\t\t<tr bgcolor='" . bgcolor($i) . "'>\n\t\t\t\t\t\t<td>{$od['type']}</td>\n\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t<td>" . CUR . " {$open_amount[$oid]}</td>\n\t\t\t\t\t</tr>";
$sql = "\n\t\t\t\t\tUPDATE cubit.open_stmnt \n\t\t\t\t\tSET balance = (balance - {$open_amount[$oid]} ::numeric(16,2)) \n\t\t\t\t\tWHERE id = '{$oid}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
// record the payment on the statement
$sql = "\n\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\ttype, div, allocation_date, docref, allocation_balance\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cus['cusnum']}', '0', '" . -$open_amount[$oid] . "', '{$sdate}', \n\t\t\t\t\t\t'Payment received', '" . USER_DIV . "', '{$accdate}', '{$reference}', '" . abs($open_amount[$oid]) . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank_acc, $sdate, 0, "Payment received", $open_amount[$oid], "c");
recordCT($open_amount[$oid], $cus['cusnum'], 0, $accdate);
}
}
// record the payment record
$cols = grp(m("bankid", $bankid), m("trantype", "deposit"), m("date", $sdate), m("name", "{$cus['cusname']} {$cus['surname']}"), m("descript", "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"), m("cheqnum", $cheqnum), m("amount", $amt), m("banked", "no"), m("accinv", $dept["debtacc"]), m("cusnum", $cus["cusnum"]), m("rinvids", $rinvids), m("amounts", $amounts), m("invprds", $invprds), m("rages", $rages), m("reference", $reference), m("div", USER_DIV));
$dbobj = new dbUpdate("cashbook", "cubit", $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
$cashbook_id = pglib_lastid("cashbook", "cashid");
writetrans($bank_acc, $dept['debtacc'], $accdate, $refnum, $amt, "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}");
}
/* start moving invoices */
// move invoices that are fully paid
$sql = "SELECT * FROM cubit.invoices WHERE balance='0' AND printed = 'y' AND done = 'y' AND div = '" . USER_DIV . "'";
$invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
while ($x = pg_fetch_array($invbRslt)) {
if (($prd = $x['prd']) == "0") {
$prd = PRD_DB;
}
// move invoice
$cols = grp(m("invid", $x["invid"]), m("invnum", $x["invnum"]), m("deptid", $x["deptid"]), m("cusnum", $x["cusnum"]), m("deptname", $x["deptname"]), m("cusacc", $x["cusacc"]), m("cusname", $x["cusname"]), m("surname", $x["surname"]), m("cusaddr", $x["cusaddr"]), m("cusvatno", $x["cusvatno"]), m("cordno", $x["cordno"]), m("ordno", $x["ordno"]), m("chrgvat", $x["chrgvat"]), m("terms", $x["terms"]), m("traddisc", $x["traddisc"]), m("salespn", $x["salespn"]), m("odate", $x["odate"]), m("delchrg", $x["delchrg"]), m("subtot", $x["subtot"]), m("vat", $x["vat"]), m("total", $x["total"]), m("age", $x["age"]), m("comm", $x["comm"]), m("discount", $x["discount"]), m("delivery", $x["delivery"]), m("docref", $x["docref"]), m("prd", $x["prd"]), m("delvat", $x["delvat"]), m("balance", 0), m("printed", "y"), m("done", "y"), m("username", USER_NAME), m("div", USER_DIV));
$dbobj = new dbUpdate("invoices", $prd, $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
// record movement
$cols = grp(m("invtype", "inv"), m("invnum", $x["invnum"]), m("prd", $x["prd"]), m("docref", $x["docref"]), m("div", USER_DIV));
$dbobj->setTable("movinv", "cubit");
$dbobj->setOpt($cols);
$dbobj->run();
$dbobj->free();
// move invoice items
$inv_items = new dbSelect("inv_items", "cubit", grp(m("where", wgrp(m("invid", $x["invid"]), m("div", USER_DIV)))));
$inv_items->run();
while ($xi = $inv_items->fetch_array()) {
$xi['vatcode'] += 0;
$xi['account'] += 0;
$xi['del'] += 0;
$cols = grp(m("invid", $x["invid"]), m("whid", $xi["whid"]), m("stkid", $xi["stkid"]), m("qty", $xi["qty"]), m("unitcost", $xi["unitcost"]), m("amt", $xi["amt"]), m("disc", $xi["disc"]), m("discp", $xi["discp"]), m("vatcode", $xi["vatcode"]), m("account", $xi["account"]), m("description", $xi["description"]), m("del", $xi["del"]), m("noted", $xi["noted"]), m("serno", $xi["serno"]), m("div", USER_DIV));
$dbobj->setTable("inv_items", $prd);
$dbobj->setOpt($cols);
$dbobj->run();
$dbobj->free();
}
/* remove invoice from cubit schema */
$dbobj = new dbDelete("invoices", "cubit", wgrp(m("invid", $x["invid"]), m("div", USER_DIV)));
$dbobj->run();
$dbobj->setTable("inv_items", "cubit");
$dbobj->run();
}
#do journal for the settlement discount here ... now ...
if ($setamt > 0) {
db_conn('core');
#calculate the settlement vat ... and amt
if (isset($setvat) and $setvat == 'inc') {
db_connect();
$get_vcode = "SELECT * FROM vatcodes WHERE id = '{$setvatcode}' LIMIT 1";
$run_vcode = db_exec($get_vcode) or errDie("Unable to get vatcode informtion.");
if (pg_numrows($run_vcode) < 1) {
return "<li class='err'>Settlement Discount VAT Code Not Set.</li>";
}
$vd = pg_fetch_array($run_vcode);
#vat inc ... recalculate the amts
$setvatamt = sprint($setamt * ($vd['vat_amount'] / (100 + $vd['vat_amount'])));
$setamt = sprint($setamt - $setvatamt);
#process the vat amt ...
writetrans($vatacc, $dept['debtacc'], $accdate, $refnum, $setvatamt, "VAT Received on Settlement Discount (Ref.{$refnum}) for Customer : {$cus['cusname']} {$cus['surname']}");
vatr($vd['id'], $accdate, "OUTPUT", $vd['code'], $refnum, "VAT for Settlement Discount (Ref.{$refnum}) for Customer : {$cus['cusname']} {$cus['surname']}", ($setamt + $setvatamt) * -1, $setvatamt * -1);
} else {
#no vat for set amt ... do nothing
$setvatamt = 0;
}
custledger($cus['cusnum'], $setaccid, $accdate, $refnum, "Settlement Discount (Ref.{$refnum})", $setamt + $setvatamt, "c");
writetrans($setaccid, $dept['debtacc'], $accdate, $refnum, $setamt, "Settlement Discount (Ref.{$refnum}) For {$cus['cusname']} {$cus['surname']}");
db_connect();
#record this paid settlement discount for reporting ...
$settl_sql = "\n\t\t\tINSERT INTO settlement_cus (\n\t\t\t\tcustomer, amt, setamt, setvatamt, setvat, setvatcode, tdate, sdate, refnum\n\t\t\t) VALUES (\n\t\t\t\t'{$cus['cusnum']}', '{$amt}', '{$setamt}', '{$setvatamt}', '{$setvat}', '{$setvatcode}', '{$accdate}', 'now', '{$refnum}'\n\t\t\t)";
$run_settl = db_exec($settl_sql) or errDie("Unable to get debtor settlement information.");
}
// $overpay = sprint ($amt - array_sum($paidamt));
if (!isset($overpay) or $overpay < 0) {
$overpay = 0.0;
}
if ($overpay > 0) {
recordCT($overpay, $cus['cusnum'], 0, $accdate);
$cols = grp(m("cusnum", $cus["cusnum"]), m("invid", 0), m("amount", -$overpay), m("date", $sdate), m("type", "Payment Received (Receipt " . pglib_lastid("cashbook", "cashid") . ")"), m("div", USER_DIV), m("allocation_date", $accdate), m("docref", $reference));
$dbobj = new dbUpdate("stmnt", "cubit", $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
custledger($cus['cusnum'], $bank_acc, $sdate, "PAYMENT", "Payment received.", $overpay, "c");
}
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
// status report
// $write = "
// <table ".TMPL_tblDflts." width='100%'>
// <tr>
// <th>Bank Receipt</th>
// </tr>
// <tr class='".bg_class()."'>
// <td>Bank Receipt added to cash book.</td>
// </tr>
// </table>
// ";
//
// $OUTPUT = "<center>
// <table width='90%'>
// <tr valign='top'>
// <td width='50%'>$write</td>
// <td align='center'>"
// .mkQuickLinks(
// ql("bank-pay-add.php", "Add Bank Payment"),
// ql("bank-recpt-add.php", "Add Bank Receipt"),
// ql("bank-recpt-inv.php", "Add Customer Payment"),
// ql("cashbook-view.php", "View Cash Book")
// )."
// </td>
// </tr>
// </table>";
// return $OUTPUT;
if (isset($print_recpt) and $print_recpt == "yes") {
$showreceipt = "printer ('bank/bank-recpt-inv-print.php?recid={$cashbook_id}');";
} else {
$showreceipt = "";
}
return "\n\t\t<script>\n\t\t\tmove ('../customers-view.php?offset=0&fval=&filter=surname&nozerobal=yes');\n\t\t\t{$showreceipt}\n\t\t</script>";
}
function write($_POST)
{
# Processes
db_connect();
# Get vars
extract($_POST);
if (isset($back)) {
return add($_POST);
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
$v->isOk($date, "date", 1, 10, "Invalid Date Entry.");
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
$v->isOk($reference, "string", 0, 50, "Invalid Reference Name/Number.");
$v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number.");
$v->isOk($amount, "float", 1, 10, "Invalid amount.");
$v->isOk($supid, "num", 1, 20, "Invalid Supplier account.");
# Display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# 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 "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
# Date format
$date = explode("-", $date);
$date = $date[2] . "-" . $date[1] . "-" . $date[0];
$cheqnum = 0 + $cheqnum;
# Get bank account name
db_connect();
$sql = "SELECT accname,bankname FROM bankacct WHERE bankid = '{$bankid}' AND div = '" . USER_DIV . "'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
# Get account name
$supRslt = get("cubit", "*", "suppliers", "supid", $supid);
$sup = pg_fetch_array($supRslt);
$bankaccid = getbankaccid($bankid);
db_conn("exten");
# Get debtors control account
$sql = "SELECT credacc FROM departments WHERE deptid ='{$sup['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
$dept = pg_fetch_array($deptRslt);
pglib_transaction("BEGIN");
db_connect();
$Sl = "INSERT INTO sup_stmnt(supid, amount, edate, descript,ref,cacc, div) VALUES('{$supid}','{$amount}','{$date}', '{$descript}','{$cheqnum}','{$bankaccid}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the supplier (make balance less)
$sql = "UPDATE suppliers SET balance = (balance + '{$amount}') WHERE supid = '{$supid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
suppCT($amount, $supid, $date);
# record the payment record
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, suprec, reference, div) VALUES ('{$bankid}', 'deposit', '{$date}', '{$sup['supno']} - {$sup['supname']}', '{$descript}', '{$cheqnum}', '{$amount}', 'no', '{$dept['credacc']}', '{$supid}', '{$reference}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$refnum = getrefnum();
# DT(account involved), CT(bank)
writetrans($bankaccid, $dept['credacc'], $date, $refnum, $amount, $descript);
suppledger($supid, $bankaccid, $date, 0, $descript, $amount, 'c');
pglib_transaction("COMMIT");
# Status report
$write = "\n\t\t\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Bank Receipt</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td>Bank Receipt from supplier : {$sup['supname']} added to cash book.</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>";
# main table (layout with menu)
$OUTPUT = "\n\t\t\t\t<center>\n\t\t\t\t<table width='90%'>\n\t\t\t\t\t<tr valign='top'>\n\t\t\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t\t\t<td align='center'>\n\t\t\t\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t<td><a href='bank-pay-add.php'>Add Bank Payment</a></td>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t<td><a href='bank-recpt-add.php'>Add Bank Receipt</a></td>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t<td><a href='cashbook-view.php'>View Cash Book</a></td>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t</table>\n\t\t\t\t\t\t</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>";
return $OUTPUT;
}
function write($_GET)
{
$showvat = TRUE;
# get vars
extract($_GET);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($invid, "num", 1, 20, "Invalid Invoice number.");
$sndate = $ninv_year . "-" . $ninv_month . "-" . $ninv_day;
if (!checkdate($ninv_month, $ninv_day, $ninv_year)) {
$v->addError($sdate, "Invalid Date.");
}
$td = $sndate;
foreach ($ids as $key => $id) {
$v->isOk($id, "num", 1, 20, "Invalid Item number.");
$v->isOk($qtys[$key], "float", 1, 20, "Invalid Item quantity.");
$v->isOk($amts[$key], "float", 1, 20, "Invalid Item amount.");
}
$v->isOk($subtot, "float", 1, 20, "Invalid sub-total amount.");
$v->isOk($vat, "float", 1, 20, "Invalid vat amount.");
$v->isOk($total, "float", 1, 20, "Invalid total amount.");
# display errors, if any
if ($v->isError()) {
$err = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
db_connect();
# Get invoice info
$sql = "SELECT * FROM hire.hire_nons_invoices WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$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);
db_conn("hire");
$noteid = pglib_lastid("hire_nons_inv_notes", "noteid");
$noteid++;
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
/* --- Start Products Display --- */
$refnum = getrefnum();
/*refnum*/
$real_noteid = divlastid('note', USER_DIV);
$vattot = 0;
$amttot = 0;
db_connect();
# Products layout
$products = array();
$i = 0;
$page = 0;
foreach ($ids as $key => $id) {
if ($i >= 25) {
$page++;
$i = 0;
}
$sql = "SELECT * FROM hire.hire_nons_inv_items WHERE invid = '{$invid}' AND id = '{$id}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
$stkd = pg_fetch_array($stkdRslt);
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatex']}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$vd = pg_fetch_array($Ri);
if (TAX_VAT != $vd['vat_amount'] and $vd['vat_amount'] != "0.00") {
$showvat = FALSE;
}
$temp = $stkd['vatex'];
if ($vd['zero'] == "Yes") {
$stkd['vatex'] = "y";
}
$t = $inv['chrgvat'];
// $VATP = TAX_VAT;
$VATP = $vd['vat_amount'];
$stkacc = $stkd['accid'];
# keep records for transactions
if (isset($totstkamt[$stkacc])) {
if ($stkd['vatex'] == "y") {
$totstkamt[$stkacc] += $amts[$key];
$va = 0;
$inv['chrgvat'] = "";
} else {
$totstkamt[$stkacc] += vats($amts[$key], $inv['chrgvat'], $vd['vat_amount']);
$va = sprint($stkd['amt'] - vats($amts[$key], $inv['chrgvat'], $vd['vat_amount']));
if ($inv['chrgvat'] == "no") {
$va = sprint($amts[$key] * $VATP / 100);
}
}
} else {
if ($stkd['vatex'] == "y") {
$totstkamt[$stkacc] = $amts[$key];
$va = 0;
$inv['chrgvat'] = "";
} else {
$totstkamt[$stkacc] = vats($amts[$key], $inv['chrgvat'], $vd['vat_amount']);
$va = sprint($amts[$key] - vats($amts[$key], $inv['chrgvat'], $vd['vat_amount']));
if ($inv['chrgvat'] == "no") {
$va = sprint($amts[$key] * $VATP / 100);
}
}
}
#add this entry's vat to a total
$vattot = $vattot + $va;
vatr($vd['id'], $td, "OUTPUT", $vd['code'], $refnum, "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid} Customer {$inv['cusname']}.", -vats($amts[$key], $inv['chrgvat'], $vd['vat_amount']) - $va, -$va);
$inv['chrgvat'] = $t;
$sql = "UPDATE hire.hire_nons_inv_items SET rqty = (rqty + '{$qtys[$key]}') WHERE id = '{$stkd['id']}'";
$sRslt = db_exec($sql);
if ($stkd['vatex'] == 'y') {
$ex = "#";
} else {
$ex = " ";
}
$stkd['vatex'] = $temp;
#add this entry's amt to a total
$amttot = $amttot + $amts[$key];
$sql = "INSERT INTO hire.hire_nons_note_items(noteid, qty, description, amt, unitcost, vatcode) VALUES('{$noteid}', '{$qtys[$key]}', '{$stkd['description']}', '{$amts[$key]}', '{$stkd['unitcost']}', '{$stkd['vatex']}')";
$stkdRslt = db_exec($sql);
#the credit note entry will get any remark entered here ? so we dont update the invoice entry ...
// db_conn("cubit");
// $sql = "UPDATE nons_invoices SET remarks='$remarks' WHERE invid='$invid'";
// $rslt = db_exec($sql) or errDie("Unable to save the comments to Cubit.");
$products[$page][] = "\r\n\t\t\t\t\t\t<tr valign='top'>\r\n\t\t\t\t\t\t\t<td style='border-right: 2px solid #000'>{$ex} {$stkd['description']} </td>\r\n\t\t\t\t\t\t\t<td style='border-right: 2px solid #000'>{$qtys[$key]} </td>\r\n\t\t\t\t\t\t\t<td style='border-right: 2px solid #000' align='right' nowrap>" . CUR . " {$stkd['unitcost']} </td>\r\n\t\t\t\t\t\t\t<td align='right' nowrap>" . CUR . " {$amts[$key]} </td>\r\n\t\t\t\t\t\t</tr>";
$i++;
}
$blank_lines = 25;
foreach ($products as $key => $val) {
$bl = $blank_lines - count($products[$key]);
for ($i = 0; $i <= $bl; $i++) {
$products[$key][] = "\r\n\t\t\t\t \t\t\t<tr>\r\n\t\t\t\t \t\t\t\t<td style='border-right: 2px solid #000'> </td>\r\n\t\t\t\t \t\t\t\t<td style='border-right: 2px solid #000'> </td>\r\n\t\t\t\t \t\t\t\t<td style='border-right: 2px solid #000'> </td>\r\n\t\t\t\t \t\t\t\t<td> </td>\r\n\t\t\t\t \t\t\t</tr>";
}
}
/* --- Start Some calculations --- */
# Subtotal
$SUBTOT = sprint($subtot);
$VAT = sprint($vat);
$TOTAL = sprint($total);
/* --- End Some calculations --- */
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "vat");
$varacc = gethook("accnum", "salesacc", "name", "sales_variance");
/* - End Hooks - */
# todays date
$date = date("d-m-Y");
$sdate = date("Y-m-d");
// print $inv['ctyp']; exit;
db_connect();
$tot_post = 0;
# bank % cust
if ($inv['ctyp'] == 's') {
$sql = "SELECT * FROM customers WHERE cusnum = '{$inv['cusid']}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to view customer");
$cus = pg_fetch_array($custRslt);
# 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;
# record transaction from data
foreach ($totstkamt as $stkacc => $wamt) {
$tot_post += $wamt;
writetrans($stkacc, $dept['debtacc'], $td, $refnum, $wamt, "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid} Customer {$inv['cusname']}.");
}
if ($VAT != 0) {
$tot_post += $VAT;
writetrans($vatacc, $dept['debtacc'], $td, $refnum, $VAT, "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid} VAT. Customer {$inv['cusname']}.");
}
$tot_dif = sprint($tot_post - $TOTAL);
if ($tot_dif > 0) {
writetrans($dept['debtacc'], $varacc, $td, $refnum, $tot_dif, "Sales Variance on Credit note No.{$real_noteid}");
} elseif ($tot_dif < 0) {
$tot_dif = $tot_dif * -1;
writetrans($varacc, $dept['debtacc'], $td, $refnum, $tot_dif, "Sales Variance on Credit note No.{$real_noteid}");
}
} elseif ($inv['ctyp'] == 'b') {
$dept['debtacc'] = getbankaccid($inv['accid']);
$amounts = "";
$accids = "";
$vats = "";
$chrgvats = "";
$gamt = 0;
# record transaction from data
foreach ($totstkamt as $stkacc => $wamt) {
# Cook vars
$amounts .= "|{$wamt}";
$accids .= "|{$stkacc}";
$vats .= "|0";
$chrgvats .= "|no";
# Debit Customer and Credit stock
$tot_post += $wamt;
writetrans($stkacc, $dept['debtacc'], $td, $refnum, $wamt, "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid}.");
}
# Debit bank and credit the account involved
if ($VAT != 0) {
# Cook vars
$amounts .= "|{$VAT}";
$accids .= "|{$vatacc}";
$vats .= "|0";
$chrgvats .= "|no";
$tot_post += $VAT;
writetrans($vatacc, $dept['debtacc'], $td, $refnum, $VAT, "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid} VAT.");
}
} else {
$cusacc = $inv['accid'];
$sdate = date("Y-m-d");
# record transaction from data
foreach ($totstkamt as $stkacc => $wamt) {
# Debit Customer and Credit stock
$tot_post += $wamt;
writetrans($stkacc, $cusacc, $td, $refnum, $wamt, "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid}.");
pettyrec($cusacc, $td, "dt", "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid}.", $wamt, "Account Sale Credit note");
}
# Debit bank and credit the account involved
$tot_post += $VAT;
writetrans($vatacc, $cusacc, $td, $refnum, $VAT, "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid} VAT.");
pettyrec($cusacc, $td, "dt", "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid} VAT.", $VAT, "Account Sale Credit note VAT");
$tot_dif = sprint($tot_post - $TOTAL);
if ($tot_dif > 0) {
writetrans($cusacc, $varacc, $td, $refnum, $tot_dif, "Sales Variance on Credit note No.{$real_noteid}");
} elseif ($tot_dif < 0) {
$tot_dif = $tot_dif * -1;
writetrans($varacc, $cusacc, $td, $refnum, $tot_dif, "Sales Variance on Credit note No.{$real_noteid}");
}
}
$sdate = date("Y-m-d");
db_connect();
if ($inv['ctyp'] == 's') {
# Record the payment on the statement
$sql = "\r\n\t\t\tINSERT INTO stmnt \r\n\t\t\t\t(cusnum, invid, amount, date, type, div, allocation_date) \r\n\t\t\tVALUES \r\n\t\t\t\t('{$inv['cusid']}', '{$real_noteid}', '-{$TOTAL}','{$td}', 'Non Stock Credit Note, for invoice {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the customer (make balance less)
$sql = "UPDATE customers SET balance = (balance - '{$TOTAL}'::numeric(13,2)) WHERE cusnum = '{$inv['cusid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Update the customer (make balance less)
$sql = "UPDATE open_stmnt SET balance = (balance - '{$TOTAL}'::numeric(13,2)) WHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Make ledge record
custledger($inv['cusid'], $stkacc, $td, $real_noteid, "Non Stock Credit note {$real_noteid}", $TOTAL, "c");
#record entry for age analysis ...
#this function seems a little ... broken
//custfCT($TOTAL, $inv['cusid'], $inv['age']);
#lets rather use the system wide function and send it the invoice transaction date to do the entry for that age
custCT($TOTAL, $inv['cusid'], $inv['odate']);
} elseif ($inv['ctyp'] == 'cb') {
$date = date("Y-m-d");
# Record the Receipt record
db_connect();
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accids, amounts, chrgvats, vats, div,accinv) VALUES ('{$inv['jobid']}', 'withdrawal', '{$td}', '{$inv['cusname']}', 'Nons Stock Credit note for invoice {$inv['invnum']}', '0', '{$TOTAL}', 'no', '', '0', '{$inv['chrgvat']}', '0', '" . USER_DIV . "','{$stkacc}')";
die($sql);
$Rslt = db_exec($sql) or errDie("Unable to add bank Receipt to database.", SELF);
}
db_connect();
$sql = "UPDATE hire.hire_nons_invoices SET balance = (balance - '{$TOTAL}'::numeric(13,2)) WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$upRslt = db_exec($sql) or errDie("Unable to update invoice information");
# write note
$sql = "INSERT INTO hire.hire_nons_inv_notes(invid, invnum, cusname, cusaddr, cusvatno, chrgvat, date, subtot, vat, total, username, prd, notenum, ctyp, remarks, div)";
$sql .= " VALUES('{$inv['invid']}', '{$inv['invnum']}', '{$inv['cusname']}', '{$inv['cusaddr']}', '{$inv['cusvatno']}', '{$inv['chrgvat']}', '{$td}', {$SUBTOT}, {$VAT}, {$TOTAL}, '" . USER_NAME . "', '" . PRD_DB . "', '{$real_noteid}', '{$inv['ctyp']}', '{$remarks}', '" . USER_DIV . "')";
$rslt = db_exec($sql) or errDie("Unable to create template Non-Stock Invoice.", SELF);
# write note items
foreach ($ids as $key => $id) {
$sql = "SELECT * FROM hire.hire_nons_inv_items WHERE invid = '{$invid}' AND id = '{$id}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
$nstk = pg_fetch_array($stkdRslt);
}
$sql = "INSERT INTO salesrec(edate, invid, invnum, debtacc, vat, total, typ, div)\r\n\tVALUES('{$td}', '{$noteid}', '{$real_noteid}', '0', '{$VAT}', '{$TOTAL}', 'nnon', '" . USER_DIV . "')";
$recRslt = db_exec($sql);
$Sl = "INSERT INTO sj(cid,name,des,date,exl,vat,inc,div) VALUES\r\n\t('{$inv['cusid']}','{$inv['cusname']}','Credit Note: {$real_noteid}, Invoice {$inv['invnum']}','{$td}','" . -sprint($TOTAL - $VAT) . "','-{$VAT}','" . -sprint($TOTAL) . "','" . USER_DIV . "')";
$Ri = db_exec($Sl);
com_invoice($inv['salespn'], -($TOTAL - $VAT), 0, $inv['invnum'], $td);
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
$cc = "<script> CostCenter('ct', 'Credit Note', '{$td}', 'Non Stock Credit Note No.{$real_noteid}', '" . ($TOTAL - $VAT) . "', ''); </script>";
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);
// Retrieve the banking information
db_conn("cubit");
$sql = "SELECT * FROM bankacct WHERE bankid='2' 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);
// Retrieve customer information
db_conn("cubit");
$sql = "SELECT * FROM customers WHERE cusnum='{$inv['cusid']}'";
$cust_rslt = db_exec($sql) or errDie("Unable to retrieve customer information from Cubit.");
$cust_data = pg_fetch_array($cust_rslt);
if ($inv['cusid'] == "0") {
$cust_data['surname'] = $inv['cusname'];
$cust_data['addr1'] = $inv['cusaddr'];
$cust_data['paddr1'] = $inv['cusaddr'];
}
$table_borders = "\r\n\t\tborder-top: 2px solid #000000;\r\n\t\tborder-left: 2px solid #000000;\r\n\t\tborder-right: 2px solid #000000;\r\n\t\tborder-bottom: none;\r\n\t";
// $nolr_borders = "
// border-top: 2px solid #000;
// border-left: none;
// border-right: none;
// border-bottom: none;
// ";
$details = "";
for ($i = 0; $i <= $page; $i++) {
// new page?
if ($i > 1) {
$details .= "<br style='page-break-after:always;'>";
}
$products_out = "";
foreach ($products[$i] as $string) {
$products_out .= $string;
}
$vattot = sprint($vattot);
$amttot = sprint($amttot);
$details .= "<center>\r\n\t\t<table cellpadding='0' cellspacing='0' width='85%' style='{$table_borders}'>\r\n\t\t\t<tr><td>\r\n\t\t\t<table border='0' cellpadding='2' cellspacing='2' width='100%'>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td align='left' rowspan='2'><img src='compinfo/getimg.php' width=230 height=47></td>\r\n\t\t\t\t\t<td align='left' rowspan='2'><font size='5'><b>" . COMP_NAME . "</b></font></td>\r\n\t\t\t\t\t<td align='right'><font size='5'><b>Tax Credit Note</b></font></td>\r\n\t\t\t\t</tr>\r\n\t\t\t</table>\r\n\t\t\t</td></tr>\r\n\t\t</table>\r\n\r\n\t\t<table cellpadding='0' cellspacing='0' width='85%' style='{$table_borders}'>\r\n\t\t\t<tr><td valign='top'>\r\n\t\t\t<table cellpadding='2' cellspacing='0' border='0' width='100%'>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>{$comp_data['addr1']} </td>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>{$comp_data['paddr1']} </td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>{$comp_data['addr2']} </td>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>{$comp_data['paddr2']} </td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>{$comp_data['addr3']} </td>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>{$comp_data['paddr3']} </td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>{$comp_data['addr4']} </td>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>{$comp_data['postcode']} </td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>REG:</b> {$comp_data['regnum']}</b> </td>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>{$bank_data['bankname']}</b> </td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>VAT REG:</b> {$comp_data['vatnum']} </td>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>Branch</b> {$bank_data['branchname']} </td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>Tel:</b> {$comp_data['tel']} </td>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>Branch Code:</b> {$bank_data['branchcode']} </td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>Fax:</b> {$comp_data['fax']} </td>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>Acc Num:</b> {$bank_data['accnum']} </td>\r\n\t\t\t\t</tr>\r\n\t\t\t</table>\r\n\t\t\t</td><td valign='top'>\r\n\t\t\t<table cellpadding='2' cellspacing='0' border='0' width='100%'>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>Date</b></td>\r\n\t\t\t\t\t<td><b>Page Number</b></td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>{$inv['odate']}</td>\r\n\t\t\t\t\t<td>" . ($i + 1) . "</td>\r\n\t\t\t\t</tr>\r\n\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-bottom: 2px solid #000; border-right: 2px solid #000'> </td>\r\n\t\t\t\t\t<td style='border-bottom: 2px solid #000'> </td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr><td> </td></tr>\r\n\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td colspan='2'><b>Credit Note No:</b> {$real_noteid}</td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td colspan='2'><b>Invoice No:</b> {$inv['invnum']}</td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td colspan='2'><b>Proforma Inv No:</b> {$inv['docref']}</td>\r\n\t\t\t\t</tr>\r\n\t\t\t</table>\r\n\t\t\t</td></tr>\r\n\t\t</table>\r\n\r\n\t\t<table cellpadding='0' cellspacing='0' width='85%' style='{$table_borders}'>\r\n\t\t\t<tr><td>\r\n\t\t\t<table cellpadding='2' cellspacing='0' border='0' width='100%'>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td align='center'><font size='4'><b>Credit Note To:</b></font></td>\r\n\t\t\t\t</tr>\r\n\t\t\t</table>\r\n\t\t\t</td></tr>\r\n\t\t</table>\r\n\r\n\t\t<table cellpadding='0' cellspacing='0' width='85%' style='{$table_borders}'>\r\n\t\t\t<tr><td>\r\n\t\t\t<table cellpadding='2' cellspacing='0' border='0' width='100%'>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td width='33%' style='border-right: 2px solid #000'><b>{$cust_data['surname']}</b></td>\r\n\t\t\t\t\t<td width='33%' style='border-right: 2px solid #000'><b>Postal Address</b></td>\r\n\t\t\t\t\t<td width='33%'><b>Delivery Address</td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>" . nl2br($cust_data["addr1"]) . "</td>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'>" . nl2br($cust_data["paddr1"]) . "</td>\r\n\t\t\t\t\t<td> </td>\r\n\t\t\t\t</tr>\r\n\t\t\t</table>\r\n\t\t\t</td></tr>\r\n\t\t</table>\r\n\r\n\t\t<table cellpadding='0' cellspacing='0' width='85%' style='{$table_borders}'>\r\n\t\t\t<tr><td>\r\n\t\t\t<table cellpadding='2' cellspacing='0' border='0' width='100%'>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td width='33%' style='border-right: 2px solid #000'><b>Customer VAT No:</b> {$inv['cusvatno']}</td>\r\n\t\t\t\t\t<td width='33%'><b>Customer Order No:</b> {$inv['cordno']}</td>\r\n\t\t\t\t</tr>\r\n\t\t\t</table>\r\n\t\t\t</td></tr>\r\n\t\t</table>\r\n\r\n\t\t<table cellpadding='0' cellspacing='0' width='85%' style='{$table_borders}'>\r\n\t\t\t<tr><td>\r\n\t\t\t<table cellpadding='2' cellspacing='0' border='0' width='100%'>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-bottom: 2px solid #000; border-right: 2px solid #000'><b>Description</b></td>\r\n\t\t\t\t\t<td style='border-bottom: 2px solid #000; border-right: 2px solid #000'><b>Qty</b></td>\r\n\t\t\t\t\t<td style='border-bottom: 2px solid #000; border-right: 2px solid #000' align='right'><b>Unit Price</b></td>\r\n\t\t\t\t\t<td style='border-bottom: 2px solid #000;' align='right'><b>Amount</b></td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t{$products_out}\r\n\t\t\t</table>\r\n\t\t\t</td></tr>\r\n\t\t</table>\r\n\r\n\t\t<table cellpadding='0' cellspacing='0' width='85%' style='{$table_borders}'>\r\n\t\t\t<tr><td>\r\n\t\t\t<table cellpadding='2' cellspacing='0' border='0' width='100%'>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td><i>VAT Exempt Indicator: #</i></td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td>{$remarks}</td>\r\n\t\t\t\t</tr>\r\n\t\t\t</table>\r\n\t\t</table>\r\n\r\n\t\t<table cellpadding='0' cellspacing='0' width='85%' style='border: 2px solid #000000'>\r\n\t\t\t<tr><td>\r\n\t\t\t<table cellpadding='2' cellspacing='0' border='0' width='100%'>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>Terms:</b> {$inv['terms']} days</b></td>\r\n\t\t\t\t\t<td><b>Subtotal:</b></td>\r\n\t\t\t\t\t<td nowrap><b>" . CUR . " {$SUBTOT}</b></td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'> </td>\r\n\t\t\t\t\t<td><b>VAT {$vat14}:</b></td>\r\n\t\t\t\t\t<td nowrap><b>" . CUR . " {$VAT}</b></td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>Received in good order by:</b>_____________________</td>\r\n\t\t\t\t\t<td><b>Total Incl VAT:</b></td>\r\n\t\t\t\t\t<td nowrap><b>" . CUR . " {$TOTAL}</b></td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'> </td>\r\n\t\t\t\t<tr>\r\n\t\t\t\t<tr>\r\n\t\t\t\t\t<td style='border-right: 2px solid #000'><b>Date:</b>_____________________</td>\r\n\t\t\t\t</tr>\r\n\t\t\t\t</tr>\r\n\t\t\t</table>\r\n\t\t</table>\r\n\t\t";
}
#fix teh date
$date_arr = explode("-", $date);
$cdate = "{$date_arr['2']}-{$date_arr['1']}-{$date_arr['0']}";
// Retrieve template settings from Cubit
db_conn("cubit");
$sql = "SELECT filename FROM template_settings WHERE template='invoices'";
$tsRslt = db_exec($sql) or errDie("Unable to retrieve the template settings from Cubit.");
$template = pg_fetch_result($tsRslt, 0);
if ($template == "invoice-print.php") {
$OUTPUT = "\r\n\t\t\t<script>\r\n\t\t\t\tCostCenter('ct', 'Credit Note', '{$cdate}', 'Non Stock Credit Note No.{$real_noteid}', '" . ($TOTAL - $VAT) . "', '');\r\n\t\t\t</script>\r\n\t\t\t{$details}";
require "tmpl-print.php";
} else {
$OUTPUT = "\r\n\t\t\t<script>\r\n\t\t\t\tCostCenter('ct', 'Credit Note', '{$cdate}', 'Non Stock Credit Note No.{$real_noteid}', '" . ($TOTAL - $VAT) . "', '');\r\n\t\t\t\tmove(\"{$template}?noteid={$noteid}&type=nonsnote\");\r\n\t\t\t</script>";
require "template.php";
}
}
function writeLoan($_POST)
{
# get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($empnum, "num", 1, 20, "Invalid employee number.");
$v->isOk($loanamt, "float", 1, 10, "Invalid loan amount.");
$v->isOk($loanint, "float", 1, 5, "Invalid loan interest.");
$v->isOk($loanperiod, "num", 1, 3, "Invalid payback period.");
$v->isOk($loaninstall, "float", 1, 10, "Invalid monthly installment.");
$v->isOk($fringebenefit, "float", 1, 10, "Invalid fringe benefit amount.");
$v->isOk($lday, "num", 1, 2, "Invalid day.");
$v->isOk($lmonth, "num", 1, 2, "Invalid month.");
$v->isOk($lyear, "num", 4, 4, "Invalid year.");
$v->isOk($accid, "num", 1, 9, "Invalid bank account selected.");
$v->isOk($account, "num", 1, 9, "Invalid contra account selected.");
$v->isOk($loan_account, "num", 1, 9, "Invalid loan account selected.");
if (!checkdate($lmonth, $lday, $lyear)) {
$v->addError("", "Invalid date.");
}
if ($account > 0 && isb($account) || isb($loan_account)) {
$v->addError("", "Main accounts blocked. Please select sub accounts.");
}
# display errors, if any
if ($v->isError()) {
$confirmCust = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirmCust .= "<li class=err>" . $e["msg"] . "</li>";
}
return enterLoan($confirmCust);
}
# get employee details
db_connect();
$sql = "SELECT * FROM employees WHERE empnum='{$empnum}' AND div = '" . USER_DIV . "'";
$empRslt = db_exec($sql) or errDie("Unable to select employees from database.");
if (pg_numrows($empRslt) < 1) {
return "Invalid employee ID.";
}
$myEmp = pg_fetch_array($empRslt);
# check for previous loan
$sql = "SELECT empnum FROM employees WHERE empnum='{$empnum}' AND div = '" . USER_DIV . "' AND gotloan='t'::bool";
$chkRslt = db_exec($sql) or errDie("Unable to check existing loans for employee.");
if (pg_numrows($chkRslt) > 0) {
return "Loan already exists for employee number: {$empnum}.";
}
if ($accid > 0) {
$bankacc = getbankaccid($accid);
}
# Debit salaries control acc and credit Bank acc
$date = date("Y-m-d");
$refnum = getrefnum();
if ($account > 0) {
$bankacc = $account;
}
writetrans($loan_account, $bankacc, $date, $refnum, $loanamt, "Loan granted to employee {$myEmp['fnames']} {$myEmp['sname']}.");
if ($accid > 0) {
# issue bank record
banktrans($accid, "withdrawal", date("d-m-Y"), "{$myEmp['fnames']} {$myEmp['sname']}", "Loan granted to employee {$myEmp['fnames']} {$myEmp['sname']}.", 0, $loanamt, $loan_account);
}
$totamount = sprint($loanperiod * $loaninstall);
$loanint_amt = $totamount - $loanamt;
# connect to db
db_connect();
$ldate = "{$lyear}-{$lmonth}-{$lday}";
pglib_transaction("BEGIN");
$sql = "INSERT INTO emp_loanarchive (empnum, loanamt, loaninstall, loanint, loanperiod,loandate, div)\n\t\t\tVALUES('{$empnum}', '{$totamount}', '{$loaninstall}', '{$loanint}', '{$loanperiod}', CURRENT_DATE, '" . USER_DIV . "')";
$rslt = db_exec($sql) or errDie("Unable to pre archive loan.");
$loanid = pglib_lastid('emp_loanarchive', 'id');
# write to db
$sql = "UPDATE employees\n\t\t\tSET loanamt='{$totamount}', loanint='{$loanint}', loanint_amt='{$loanint_amt}',\n\t\t\t\tloanint_unpaid='{$loanint_amt}', loanperiod='{$loanperiod}', loaninstall='{$loaninstall}',\n\t\t\t\tgotloan='t'::bool, loanpayslip='{$loanamt}', loanfringe='{$fringebenefit}', loandate='{$ldate}',\n\t\t\t\texpacc_loan='{$loan_account}', loanamt_tot='{$totamount}', loanid='{$loanid}'\n\t\t\tWHERE empnum='{$empnum}' AND div = '" . USER_DIV . "'";
$loanRslt = db_exec($sql) or errDie("Unable to add loan to system.", SELF);
if (pg_cmdtuples($loanRslt) < 1) {
return "Unable to add loan to system.";
}
pglib_transaction("COMMIT");
$writeLoan = "<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width='50%'>\n\t<tr><th>Loan granted and added to system</th></tr>\n\t<tr class=datacell><td>New loan has been successfully added to Cubit.</td></tr>\n\t</table>" . mkQuickLinks(ql("../admin-employee-add.php", "Add Employee"), ql("../admin-employee-view.php", "View Employees"));
return $writeLoan;
}
function write($_POST)
{
# Get vars
extract($_POST);
if (isset($back)) {
return add($_POST);
}
# Validate input
require_lib("validate");
$v = new validate();
$v->isOk($fbankid, "num", 1, 30, "Invalid From Bank Account.");
$v->isOk($tbankid, "num", 1, 30, "Invalid To Bank Account.");
$v->isOk($date, "date", 1, 10, "Invalid Date Entry.");
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
$v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number.");
$v->isOk($amount, "float", 1, 10, "Invalid amount.");
$v->isOk($reference, "string", 0, 50, "Invalid Reference Name/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;
}
# Get bank account name
db_connect();
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$fbankid}' AND div = '" . USER_DIV . "'";
$fbankRslt = db_exec($sql);
$fbank = pg_fetch_array($fbankRslt);
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$tbankid}' AND div = '" . USER_DIV . "'";
$tbankRslt = db_exec($sql);
$tbank = pg_fetch_array($tbankRslt);
# Date format
$date = explode("-", $date);
$date = $date[2] . "-" . $date[1] . "-" . $date[0];
# nasty zero
$cheqnum += 0;
$faccid = getbankaccid($fbankid);
$taccid = getbankaccid($tbankid);
# Some info
$refnum = getrefnum();
$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 "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
if (isset($batch)) {
# Begin Updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# Record the payment record
db_connect();
$sql = "\n\t\t\tINSERT INTO batch_cashbook (\n\t\t\t\tbankid, trantype, date, name, descript, cheqnum, amount, \n\t\t\t\tbanked, accinv, div, bt, reference, rid\n\t\t\t) VALUES (\n\t\t\t\t'{$fbankid}', 'withdrawal', '{$date}', '{$tbank['accname']} - {$tbank['bankname']}', '{$descript}', '{$cheqnum}', '{$amount}', \n\t\t\t\t'no', '{$taccid}', '" . USER_DIV . "', 'transfer', '{$reference}', '{$tbankid}'\n\t\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
// $lcashid = pglib_lastid("cashbook", "cashid");
//
// $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, div) VALUES ('$tbankid', 'deposit', '$date', '$fbank[accname] - $fbank[bankname]', '$descript', '$cheqnum', '$amount', 'no', '$faccid', '".USER_DIV."')";
// $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF);
//
// $lcashid2 = pglib_lastid("cashbook", "cashid");
//
// # restore link
// $sql = "UPDATE cashbook SET lcashid = '$lcashid2' WHERE cashid = '$lcashid'";
// $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF);
//
// $sql = "UPDATE cashbook SET lcashid = '$lcashid' WHERE cashid = '$lcashid2'";
// $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF);
//
# Commit Updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
} else {
# Begin Updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# write trans
writetrans($taccid, $faccid, $date, $refnum, $amount, $descript);
# Record the payment record
db_connect();
$sql = "\n\t\t\tINSERT INTO cashbook (\n\t\t\t\tbankid, trantype, date, name, descript, cheqnum, amount, \n\t\t\t\tbanked, accinv, reference, div\n\t\t\t) VALUES (\n\t\t\t\t'{$fbankid}', 'withdrawal', '{$date}', '{$tbank['accname']} - {$tbank['bankname']}', '{$descript}', '{$cheqnum}', '{$amount}', \n\t\t\t\t'no', '{$taccid}', '{$reference}', '" . USER_DIV . "'\n\t\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$lcashid = pglib_lastid("cashbook", "cashid");
$sql = "\n\t\t\tINSERT INTO cashbook (\n\t\t\t\tbankid, trantype, date, name, descript, cheqnum, \n\t\t\t\tamount, banked, accinv, reference, div\n\t\t\t) VALUES (\n\t\t\t\t'{$tbankid}', 'deposit', '{$date}', '{$fbank['accname']} - {$fbank['bankname']}', '{$descript}', '{$cheqnum}', \n\t\t\t\t'{$amount}', 'no', '{$faccid}', '{$reference}', '" . USER_DIV . "'\n\t\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$lcashid2 = pglib_lastid("cashbook", "cashid");
# restore link
$sql = "UPDATE cashbook SET lcashid = '{$lcashid2}' WHERE cashid = '{$lcashid}'";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$sql = "UPDATE cashbook SET lcashid = '{$lcashid}' WHERE cashid = '{$lcashid2}'";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
# Commit Updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
}
# Status report
$write = "\n\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t<tr>\n\t\t\t\t<th>Bank transfer</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Bank transfer has been added to the Cash book.</td>\n\t\t\t</tr>\n\t\t</table>";
# Main table (layout with menu)
$OUTPUT = "\n\t\t<center>\n\t\t<table width='90%'>\n\t\t\t<tr valign='top'>\n\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t<td align='center'>\n\t\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Quick Links</th>\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 align='center'><a target='_blank' href='../core/acc-new2.php'>Add account (New Window)</a></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><a href='cashbook-view.php'>View Cash Book</a></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t\t\t</table>\n\t\t\t</td>\n\t\t</tr>\n\t\t</table>";
return $OUTPUT;
}
function write($_POST)
{
# Get vars
extract($_POST);
if (isset($back)) {
return add($_POST);
}
# Validate input
require_lib("validate");
$v = new validate();
$v->isOk($fbankid, "num", 1, 30, "Invalid From Bank Account.");
$v->isOk($tbankid, "num", 1, 30, "Invalid To Bank Account.");
$v->isOk($date, "date", 1, 10, "Invalid Date Entry.");
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
$v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number.");
$v->isOk($amount, "float", 1, 10, "Invalid amount.");
# 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 bank account name
db_connect();
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$fbankid}' AND div = '" . USER_DIV . "'";
$fbankRslt = db_exec($sql);
$fbank = pg_fetch_array($fbankRslt);
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$tbankid}' AND div = '" . USER_DIV . "'";
$tbankRslt = db_exec($sql);
$tbank = pg_fetch_array($tbankRslt);
# Date format
$date = explode("-", $date);
$date = $date[2] . "-" . $date[1] . "-" . $date[0];
# nasty zero
$cheqnum += 0;
$faccid = getbankaccid($fbankid);
$taccid = getbankaccid($tbankid);
# Some info
$refnum = getrefnum();
db_conn('cubit');
$id += 0;
$Sl = "UPDATE batch_cashbook SET bankid='{$fbankid}',date='{$date}',name='{$tbank['accname']} - {$tbank['bankname']}',descript='{$descript}',cheqnum='{$cheqnum}',amount='{$amount}',accinv='{$taccid}',rid='{$tbankid}' WHERE cashid='{$id}'";
$Ri = db_exec($Sl) or errDie("unable to update cashbook.");
// # Record the payment record
// db_connect();
// $sql = "INSERT INTO batch_cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, div,bt,rid) VALUES ('$fbankid', 'withdrawal', '$date', '$tbank[accname] - $tbank[bankname]', '$descript', '$cheqnum', '$amount', 'no', '$taccid', '".USER_DIV."','transfer','$tbankid')";
// $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF);
// $lcashid = pglib_lastid("cashbook", "cashid");
//
// $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, div) VALUES ('$tbankid', 'deposit', '$date', '$fbank[accname] - $fbank[bankname]', '$descript', '$cheqnum', '$amount', 'no', '$faccid', '".USER_DIV."')";
// $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF);
//
// $lcashid2 = pglib_lastid("cashbook", "cashid");
//
// # restore link
// $sql = "UPDATE cashbook SET lcashid = '$lcashid2' WHERE cashid = '$lcashid'";
// $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF);
//
// $sql = "UPDATE cashbook SET lcashid = '$lcashid' WHERE cashid = '$lcashid2'";
// $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF);
//
# Status report
$write = "\n\t\t\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Bank transfer</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t<td>Bank transfer has been updated.</td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>";
# Main table (layout with menu)
$OUTPUT = "\n\t\t\t\t\t<center>\n\t\t\t\t\t<table width='90%'>\n\t\t\t\t\t\t<tr valign='top'>\n\t\t\t\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t\t\t\t<td align='center'>\n\t\t\t\t\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\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\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t\t<td><a href='cashbook-view.php'>View Cash Book</a></td>\n\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t\t\t\t\t\t</table>\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>";
return $OUTPUT;
}
function write($_POST)
{
# Get vars
extract($_POST);
# Validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
$v->isOk($date, "date", 1, 10, "Invalid Date Entry.");
$v->isOk($name, "string", 1, 255, "Invalid Person/Business Received from/received from.");
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
$v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number.");
$v->isOk($amount, "float", 1, 10, "Invalid amount.");
$v->isOk($accinv, "string", 1, 255, "Invalid account number (account involved).");
# 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;
}
# 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 "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
# date format
$date = explode("-", $date);
$date = $date[2] . "-" . $date[1] . "-" . $date[0];
# nasty zero
$cheqnum += 0;
db_connect();
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$bankid}' AND div = '" . USER_DIV . "'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
# Some info
$bankacc = getbankaccid($bankid);
pglib_transaction("BEGIN");
$refnum = getrefnum();
# write trans
writetrans($bankacc, $accinv, $date, $refnum, $amount, $descript);
# Record the payment record
db_connect();
$sql = "\n\t\tINSERT INTO cashbook (\n\t\t\tbankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, div\n\t\t) VALUES (\n\t\t\t'{$bankid}', 'deposit', '{$date}', '{$name}', '{$descript}', '{$cheqnum}', '{$amount}', 'no', '{$accinv}', '" . USER_DIV . "'\n\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
# Record tranfer for patty cash report
$sql = "\n\t\tINSERT INTO pettyrec (\n\t\t\tdate, type, det, amount, name, div\n\t\t) VALUES (\n\t\t\t'{$date}', 'Req', '{$descript}', '-{$amount}', 'Transfer To Bank Account : {$bank['accname']} - {$bank['bankname']}', '" . USER_DIV . "'\n\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
pglib_transaction("COMMIT");
# Status report
$write = "\n\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t<tr>\n\t\t\t\t<th>Funds transfer to Bank</th>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td>Petty Cash Funds transfer to Bank has been added to the petty cash book.</td>\n\t\t\t</tr>\n\t\t</table>";
# Main table (layout with menu)
$OUTPUT = "\n\t\t<center>\n\t\t<table width='90%'>\n\t\t\t<tr valign='top'>\n\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t<td align='center'>\n\t\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Quick Links</th>\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><a href='cashbook-view.php'>View Cash Book</a></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t\t\t</table>\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>";
return $OUTPUT;
}
function bank_xrate_update($fcid, $nrate)
{
$placc = getAccn('999', '999');
$date = date("d-m-Y");
$refnum = getrefnum();
db_connect();
$sql = "SELECT bankid, balance, fbalance, (balance/fbalance) as crate FROM bankacct WHERE fcid = '{$fcid}' AND fbalance <> 0";
$rslt = db_exec($sql) or errDie("Unable to retrieve balances from Cubit", SELF);
while ($rec = pg_fetch_array($rslt)) {
$baccid = getbankaccid($rec['bankid']);
$nbal = sprint($rec['fbalance'] * $nrate);
db_connect();
$sql = "UPDATE bankacct SET balance = '{$nbal}' WHERE bankid = '{$rec['bankid']}'";
$uRs = db_exec($sql) or errDie("Unable to retrieve update exchange rate : Bank accounts", SELF);
if ($nbal > $rec['balance']) {
$diff = sprint($nbal - $rec['balance']);
// Journal entry (? accounts)
writetrans($baccid, $placc['accid'], $date, $refnum, $diff, "Exchange rate profit.");
} else {
$diff = sprint($rec['balance'] - $nbal);
// Journal entry (? accounts)
writetrans($placc['accid'], $baccid, $date, $refnum, $diff, "Exchange rate loss.");
}
}
}
function write()
{
extract($_POST);
if (isset($cabtn_back)) {
return alloc();
}
require_lib("validate");
$v = new validate();
$v->isOk($all, "num", 1, 1, "Invalid allocation.");
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
$v->isOk($date, "date", 1, 14, "Invalid Date.");
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
$v->isOk($reference, "string", 0, 50, "Invalid Reference Name/Number.");
$v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number.");
foreach ($cusids as $k => $cusid) {
$v->isOk($out[$k], "float", 1, 40, "Invalid outstanding amount.");
$v->isOk($amts[$k], "float", 1, 40, "Invalid amount.");
$v->isOk($cusid, "num", 1, 40, "Invalid customer number.");
if (isset($out1[$k])) {
$v->isOk($out1[$k], "float", 0, 40, "Invalid paid amount(currant).");
$v->isOk($out2[$k], "float", 0, 40, "Invalid paid amount(30).");
$v->isOk($out3[$k], "float", 0, 40, "Invalid paid amount(60).");
$v->isOk($out4[$k], "float", 0, 40, "Invalid paid amount(90).");
$v->isOk($out5[$k], "float", 0, 40, "Invalid paid amount(120).");
}
if (isset($invids[$k])) {
foreach ($invids[$k] as $key => $value) {
$v->isOk($invids[$k][$key], "num", 1, 50, "Invalid Invoice No.");
$v->isOk($paidamt[$k][$key], "float", 1, 40, "Invalid amount to be paid.");
}
}
}
if ($v->isError()) {
return alloc($v->genErrors());
}
# 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 "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
/* get bank account id */
if (($bank_acc = getbankaccid($bankid)) === false) {
$sql = "SELECT accid FROM core.accounts WHERE accname='Cash on Hand'";
$rslt = db_exec($sql);
if (pg_num_rows($rslt) < 1) {
if ($bankid == 0) {
return "There is no 'Cash on Hand' account, there was one, but\n\t\t\t\t\t\t**s not there now, you must have deleted it, if you want\n\t\t\t\t\t\tto use cash functionality please create a 'Cash on Hand' account.";
} else {
return "Invalid bank acc.";
}
}
$bank_acc = pg_fetch_result($rslt, 0);
}
$date_arr = explode("-", $date);
$_SESSION["global_day"] = $date_arr[2];
$_SESSION["global_month"] = $date_arr[1];
$_SESSION["global_year"] = $date_arr[0];
pglib_transaction("BEGIN");
$cheqnum += 0;
$rinvids = "";
$amounts = "";
$invprds = "";
$rages = "";
$deptacc = array();
foreach ($cusids as $k => $cusid) {
if (!isset($invids[$k])) {
$invids[$k] = array();
}
if (!isset($paidamt[$k])) {
$paidamt[$k] = array();
}
$vars = array("bankid" => $bankid, "bank_acc" => $bank_acc, "date" => $date, "descript" => $descript, "reference" => $reference, "cheqnum" => $cheqnum, "out1" => isset($out1[$k]) ? $out1[$k] : 0, "out2" => isset($out2[$k]) ? $out2[$k] : 0, "out3" => isset($out3[$k]) ? $out3[$k] : 0, "out4" => isset($out4[$k]) ? $out4[$k] : 0, "out5" => isset($out5[$k]) ? $out5[$k] : 0, "amt" => $amts[$k], "out" => $out[$k], "cusid" => $cusid, "invids" => $invids[$k], "paidamt" => $paidamt[$k], "itype" => isset($itype[$k]) ? $itype[$k] : array(), "ptype" => isset($ptype[$k]) ? $ptype[$k] : array(), "all" => $all);
$x = write_cus($vars);
$rinvids .= $x["rinvids"];
$amounts .= $x["amounts"];
$invprds .= $x["invprds"];
$rages .= $x["rages"];
$deptacc[$x["deptacc"]] = $x["deptacc"];
}
if (count($deptacc) == 1) {
$ledgeracc_col = "accinv";
$pfxhack = "";
} else {
$ledgeracc_col = "accids";
$pfxhack = "|";
}
if (!isset($cus['cusname'])) {
$cus['cusname'] = "";
}
if (!isset($cus['surname'])) {
$cus['surname'] = "";
}
if (!isset($invidsers)) {
$invidsers = "";
}
$cols = grp(m("bankid", $bankid), m("trantype", "deposit"), m("date", $date), m("name", "{$cus['cusname']} {$cus['surname']}"), m("descript", "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"), m("cheqnum", $cheqnum), m("amount", $amt), m("banked", "no"), m($ledgeracc_col, $pfxhack . implode("|", $deptacc)), m("cusnum", "-1"), m("rinvids", $rinvids), m("amounts", $amounts), m("invprds", $invprds), m("rages", $rages), m("multicusnum", implode(",", $cusids)), m("multicusamt", implode(",", $amts)), m("reference", $reference), m("div", USER_DIV));
$dbobj = new dbUpdate("cashbook", "cubit", $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
pglib_transaction("COMMIT");
$OUT = "\n\t\t<center>\n\t <table " . TMPL_tblDflts . ">\n\t\t <tr>\n\t\t \t<th>Bank Receipt</th>\n\t\t </tr>\n\t\t <tr class='" . bg_class() . "'>\n\t\t \t<td>Bank Receipt added to cash book.</td>\n\t\t </tr>\n\t </table>\n\t <br />" . mkQuickLinks(ql("bank-pay-add.php", "Add Bank Payment"), ql("bank-recpt-add.php", "Add Bank Receipt"), ql("bank-recpt-inv.php", "Add Customer Payment"), ql("cashbook-view.php", "View Cash Book"));
return $OUT;
}
function writeLoan($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($empnum, "num", 1, 20, "Invalid employee number.");
$v->isOk($loanamt, "float", 1, 10, "Invalid loan amount.");
$v->isOk($loanint, "float", 1, 5, "Invalid loan interest.");
$v->isOk($loanperiod, "num", 1, 3, "Invalid payback period.");
$v->isOk($loaninstall, "float", 1, 10, "Invalid monthly installment.");
$v->isOk($fringebenefit, "float", 1, 10, "Invalid fringe benefit amount.");
$v->isOk($lday, "num", 1, 2, "Invalid day.");
$v->isOk($lmonth, "num", 1, 2, "Invalid month.");
$v->isOk($lyear, "num", 4, 4, "Invalid year.");
$v->isOk($accid, "num", 1, 9, "Invalid bank account selected.");
$v->isOk($account, "num", 1, 9, "Invalid contra account selected.");
$v->isOk($loan_account, "num", 1, 9, "Invalid loan account selected.");
$v->isOk($loan_type, "num", 1, 9, "Invalid loan type selected.");
if (!checkdate($lmonth, $lday, $lyear)) {
$v->addError("", "Invalid date.");
}
if ($account > 0 && isb($account) || isb($loan_account)) {
$v->addError("", "Main accounts blocked. Please select sub accounts.");
}
# display errors, if any
if ($v->isError()) {
$confirmCust = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirmCust .= "<li class='err'>" . $e["msg"] . "</li>";
}
return enterLoan($confirmCust);
}
# get employee details
db_connect();
$sql = "SELECT * FROM employees WHERE empnum='{$empnum}' AND div = '" . USER_DIV . "'";
$empRslt = db_exec($sql) or errDie("Unable to select employees from database.");
if (pg_numrows($empRslt) < 1) {
return "Invalid employee ID.";
}
$myEmp = pg_fetch_array($empRslt);
# check for previous loan
$sql = "SELECT empnum FROM employees WHERE empnum='{$empnum}' AND div = '" . USER_DIV . "' AND gotloan='t'::bool";
$chkRslt = db_exec($sql) or errDie("Unable to check existing loans for employee.");
if (pg_numrows($chkRslt) > 0) {
return "<li class='err'>Loan already exists for employee number: {$myEmp['enum']}.</li>";
}
if ($accid > 0) {
$bankacc = getbankaccid($accid);
}
# Debit salaries control acc and credit Bank acc
$date = date("Y-m-d");
$ldate = mkdate($lyear, $lmonth, $lday);
$refnum = getrefnum();
if ($account > 0) {
$bankacc = $account;
}
// writetrans($loan_account, $bankacc, $date, $refnum, $loanamt, "Loan granted to employee $myEmp[fnames] $myEmp[sname].");
if ($accid > 0) {
# issue bank record
// banktrans($accid, "withdrawal", date("d-m-Y"), "$myEmp[fnames] $myEmp[sname]", "Loan granted to employee $myEmp[fnames] $myEmp[sname].", 0, $loanamt, $loan_account);
}
$totamount = sprint($loanperiod * $loaninstall);
$loanint_amt = $totamount - $loanamt;
# connect to db
db_connect();
$ldate = "{$lyear}-{$lmonth}-{$lday}";
$insert_sql = "\n\t\tINSERT INTO loan_requests (\n\t\t\tempnum, loanamt, loaninstall, loanint, loanperiod, loandate, \n\t\t\tloan_type, div, loan_account, bankacc, date, totamount, \n\t\t\tloanint_amt, fringebenefit, ldate, account, accid\n\t\t) VALUES (\n\t\t\t'{$empnum}', '{$loanamt}', '{$loaninstall}', '{$loanint}', '{$loanperiod}', '{$ldate}', \n\t\t\t'{$loan_type}', '" . USER_DIV . "', '{$loan_account}', '{$bankacc}', '{$date}', '{$totamount}', \n\t\t\t'{$loanint_amt}', '{$fringebenefit}', '{$ldate}', '{$account}', '{$accid}'\n\t\t)";
$run_insert = db_exec($insert_sql) or errDie("Unable to add loan application request.");
// pglib_transaction("BEGIN");
//
// $sql = "INSERT INTO emp_loanarchive (empnum, loanamt, loaninstall, loanint, loanperiod,loandate, loan_type, div)
// VALUES('$empnum', '$totamount', '$loaninstall', '$loanint', '$loanperiod', CURRENT_DATE, '$loan_type', '".USER_DIV."')";
// $rslt = db_exec($sql) or errDie("Unable to pre archive loan.");
//
// $loanid = pglib_lastid('emp_loanarchive', 'id');
//
// # write to db
// $sql = "UPDATE employees
// SET loanamt='$totamount', loanint='$loanint', loanint_amt='$loanint_amt',
// loanint_unpaid='$loanint_amt', loanperiod='$loanperiod', loaninstall='$loaninstall',
// gotloan='t'::bool, loanpayslip='$loanamt', loanfringe='$fringebenefit', loandate='$ldate',
// expacc_loan='$loan_account', loanamt_tot='$totamount', loanid='$loanid'
// WHERE empnum='$empnum' AND div = '".USER_DIV."'";
// $loanRslt = db_exec ($sql) or errDie ("Unable to add loan to system.", SELF);
//
// if (pg_cmdtuples ($loanRslt) < 1) {
// return "Unable to add loan to system.";
// }
//
// pglib_transaction("COMMIT");
$writeLoan = "\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Loan granted and added to system</th>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td>New loan application has been successfully added to Cubit.\n\t\t\t\t<a href='../groupware/req_gen.php'>Send</a> an instant message.</td>\n\t\t\t</tr>\n\t\t</table><br>" . mkQuickLinks(ql("loan_apply.php", "Add Loan Application"), ql("loan_apply_view.php", "View Loan Applications"), ql("../admin-employee-add.php", "Add Employee"), ql("../admin-employee-view.php", "View Employees"));
return $writeLoan;
}
function write($_POST)
{
extract($_POST);
if (isset($back)) {
unset($_POST["back"]);
return alloc($_POST);
}
# CHECK IF THIS DATE IS IN THE BLOCKED RANGE
$blocked_date_from = getCSetting("BLOCKED_FROM");
$blocked_date_to = getCSetting("BLOCKED_TO");
require_lib("validate");
$v = new validate();
$v->isOk($all, "num", 1, 1, "Invalid allocation.");
$v->isOk($rec_amount, "num", 1, 5, "Invalid amount of entries.");
for ($t = 0; $t < $rec_amount; $t++) {
if (!isset($descript[$t]) or !isset($reference[$t]) or !isset($setamt[$t]) or empty($descript[$t]) or empty($reference[$t]) or empty($setamt[$t])) {
continue;
}
$v->isOk($bankid[$t], "num", 1, 30, "Invalid Bank Account.");
$v->isOk($date[$t], "date", 1, 14, "Invalid Date.");
$v->isOk($out[$t], "float", 1, 40, "Invalid out amount.");
$v->isOk($descript[$t], "string", 0, 255, "Invalid Description.");
$v->isOk($reference[$t], "string", 0, 50, "Invalid Reference Name/Number.");
$v->isOk($cheqnum[$t], "num", 0, 30, "Invalid Cheque number.");
$v->isOk($amt[$t], "float", 1, 40, "Invalid amount.");
$v->isOk($setamt[$t], "float", 1, 40, "Invalid Settlement amount.");
$v->isOk($setvat[$t], "string", 1, 10, "Invalid Settlement VAT Option.");
$v->isOk($setvatcode[$t], "string", 1, 40, "Invalid Settlement VAT code");
$v->isOk($cusid[$t], "num", 1, 40, "Invalid customer number.");
$v->isOk($out1[$t], "float", 0, 40, "Invalid paid amount(currant).");
$v->isOk($out2[$t], "float", 0, 40, "Invalid paid amount(30).");
$v->isOk($out3[$t], "float", 0, 40, "Invalid paid amount(60).");
$v->isOk($out4[$t], "float", 0, 40, "Invalid paid amount(90).");
$v->isOk($out5[$t], "float", 0, 40, "Invalid paid amount(120).");
if (isset($invids[$t])) {
foreach ($invids[$t] as $key => $value) {
$v->isOk($invids[$t][$key], "num", 1, 50, "Invalid Invoice No.");
$v->isOk($paidamt[$t][$key], "float", 1, 40, "Invalid amount to be paid.");
}
}
if (strtotime($date[$t]) >= strtotime($blocked_date_from) and strtotime($date[$t]) <= strtotime($blocked_date_to) and !user_is_admin(USER_ID)) {
return "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
}
if ($v->isError()) {
$confirm = $v->genErrors();
return $confirm . confirm($_POST);
}
for ($t = 0; $t < $rec_amount; $t++) {
if (!isset($descript[$t]) or !isset($reference[$t]) or !isset($setamt[$t]) or empty($descript[$t]) or empty($reference[$t]) or empty($setamt[$t])) {
continue;
}
/* get bank account id */
if (($bank_acc[$t] = getbankaccid($bankid[$t])) === false) {
$sql = "SELECT accid FROM core.accounts WHERE accname='Cash on Hand'";
$rslt = db_exec($sql);
if (pg_num_rows($rslt) < 1) {
if ($bankid[$t] == 0) {
return "There is no 'Cash on Hand' account, there was one, but\n\t\t\t\t\t\t**s not there now, you mudst have deleted it, if you want\n\t\t\t\t\t\tto use cash functionality please create a 'Cash on Hand' account.";
} else {
return "Invalid bank acc.";
}
}
$bank_acc[$t] = pg_fetch_result($rslt, 0);
}
$cus = qryCustomer($cusid[$t], "cusnum, deptid, cusname, surname");
$dept = qryDepartment($cus["deptid"], "debtacc");
$refnum = getrefnum();
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# date format
$sdate[$t] = explode("-", $date[$t]);
$sdate[$t] = $sdate[$t][2] . "-" . $sdate[$t][1] . "-" . $sdate[$t][0];
$cheqnum[$t] = 0 + $cheqnum[$t];
$pay = "";
$accdate[$t] = $sdate[$t];
/* Paid invoices */
$invidsers = "";
$rinvids = "";
$amounts = "";
$invprds = "";
$rages = "";
/* OPTION 1 : AUTO ALLOCATE (write) */
if ($all == 0) {
# update the customer (make balance less)
$sql = "UPDATE cubit.customers SET balance = (balance - '{$amt[$t]}'::numeric(13,2))\n\t\t\t\t\tWHERE cusnum = '{$cus['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
if (isset($invids[$t])) {
foreach ($invids[$t] as $key => $value) {
$ii = $invids[$t][$key];
$pp = $paidamt[$t][$key];
/* OPTION 1: STOCK INVOICES */
if (!isset($itype[$t][$ii]) && !isset($ptype[$t][$ii])) {
$sql = "SELECT prd,invnum,odate FROM cubit.invoices\n\t\t\t\t\t\t\t\tWHERE invid ='{$ii}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.</li>";
}
$inv = pg_fetch_array($invRslt);
$inv['invnum'] += 0;
// reduce invoice balance
$sql = "UPDATE cubit.invoices \n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2))\n\t\t\t\t\t\t\t\tWHERE invid = '{$ii}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "UPDATE cubit.open_stmnt \n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2))\n\t\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
# record the payment on the statement
$sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', '" . ($pp - $pp * 2) . "', '{$sdate[$t]}', \n\t\t\t\t\t\t\t\t'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank_acc[$t], $sdate[$t], $inv['invnum'], "Payment for Invoice No. {$inv['invnum']}", $paidamt[$t][$key], "c");
$rinvids .= "|{$invids[$t]}[{$key}]";
$amounts .= "|{$pp}";
if ($inv['prd'] == "0") {
$inv['prd'] = PRD_DB;
}
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
/* OPTION 1: NONS STOCK INVOICES */
} else {
if (!isset($ptype[$t][$ii])) {
$sql = "SELECT prd,invnum,descrip,age,odate FROM cubit.nons_invoices\n\t\t\t\t\t\t\t\tWHERE invid ='{$ii}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.</li>";
}
$inv = pg_fetch_array($invRslt);
$inv['invnum'] += 0;
# reduce the money that has been paid
$sql = "UPDATE cubit.nons_invoices\n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2))\n\t\t\t\t\t\t\t\tWHERE invid = '{$ii}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2))\n\t\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
# record the payment on the statement
$sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', '" . ($pp - $pp * 2) . "', '{$sdate[$t]}', \n\t\t\t\t\t\t\t\t'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank_acc[$t], $sdate[$t], $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}", $paidamt[$t][$key], "c");
recordCT($pp, $cus['cusnum'], $inv['age'], $accdate[$t]);
$rinvids .= "|{$ii}";
$amounts .= "|{$pp}";
$invprds .= "|0";
$rages .= "|{$inv['age']}";
$invidsers .= " - {$inv['invnum']}";
} else {
/* pos invoices */
$sqls = array();
for ($i = 1; $i <= 12; ++$i) {
$sqls[] = "SELECT '{$i}' AS prd,invid,invnum,odate FROM \"{$i}\".pinvoices \n\t\t\t\t\t\t\t\t\tWHERE invid='{$ii}' AND div='" . USER_DIV . "'";
}
$sql = implode(" UNION ", $sqls);
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.</li>";
}
$inv = pg_fetch_array($invRslt);
// reduce the invoice balance
$sql = "UPDATE \"{$inv['prd']}\".pinvoices \n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2)) \n\t\t\t\t\t\t\t\tWHERE invid = '{$ii}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "UPDATE cubit.open_stmnt \n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2)) \n\t\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
# record the payment on the statement
$sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, date, type, div, allocation_date\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}','{$inv['invnum']}', '" . ($pp - $pp * 2) . "','{$sdate[$t]}', 'Payment for Non Stock Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank_acc[$t], $sdate[$t], $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']}", $paidamt[$t][$key], "c");
recordCT($paidamt[$t][$key], $cus['cusnum'], 0, $accdate[$t]);
$rinvids .= "|{$invids[$t]}[{$key}]";
$amounts .= "|{$paidamt[$t]}[{$key}]";
$invprds .= "|{$inv['prd']}";
//$rages .= "|$inv[age]";
$invidsers .= " - {$inv['invnum']}";
}
}
}
}
$cols = grp(m("bankid", $bankid[$t]), m("trantype", "deposit"), m("date", $sdate[$t]), m("name", "{$cus['cusname']} {$cus['surname']}"), m("descript", "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"), m("cheqnum", $cheqnum[$t]), m("amount", $amt[$t]), m("banked", "no"), m("accinv", $dept["debtacc"]), m("cusnum", $cus["cusnum"]), m("rinvids", $rinvids), m("amounts", $amounts), m("invprds", $invprds), m("rages", $rages), m("reference", $reference[$t]), m("div", USER_DIV));
$dbobj = new dbUpdate("cashbook", "cubit", $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
/*
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript,
cheqnum, amount, banked, accinv, cusnum, rinvids, amounts,
invprds, rages, reference, div)
VALUES ('$bankid', 'deposit', '$sdate', '$cus[cusname] $cus[surname]',
'',
'$cheqnum', '$amt', 'no', '$dept[debtacc]', '$cus[cusnum]',
'$rinvids', '$amounts', '$invprds', '$rages', '$reference',
'".USER_DIV."')";
$Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF);
*/
$refnum = getrefnum($accdate[$t]);
writetrans($bank_acc[$t], $dept['debtacc'], $accdate[$t], $refnum, $amt[$t], "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}");
db_conn('cubit');
if ($out > 0) {
/* START OPEN ITEMS */
$openstmnt = new dbSelect("open_stmnt", "cubit", grp(m("where", "balance>0 AND cusnum='{$cusid[$t]}'"), m("order", "date")));
$openstmnt->run();
$open_out[$t] = $out[$t];
$i = 0;
$ox = "";
while ($od = $openstmnt->fetch_array()) {
if ($open_out[$t] == 0) {
continue;
}
$oid = $od['id'];
if ($open_out[$t] >= $od['balance']) {
$open_amount[$t][$oid] = $od['balance'];
$open_out[$t] = sprint($open_out[$t] - $od['balance']);
$ox .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><input type='hidden' size='20' name='open[{$t}][{$oid}]' value='{$oid}'>{$od['type']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t\t\t<td><input type='hidden' name='open_amount[{$t}][{$oid}]' value='{$open_amount[$t]}[{$oid}]'>" . CUR . " {$open_amount[$t]}[{$oid}]</td>\n\t\t\t\t\t\t\t</tr>";
$Sl = "UPDATE cubit.open_stmnt SET balance=balance-'" . $open_amount[$t][$oid] . "' WHERE id='{$oid}'";
$Ri = db_exec($Sl) or errDie("Unable to update statement.");
} elseif ($open_out < $od['balance']) {
$open_amount[$t][$oid] = $open_out[$t];
$open_out = 0;
$ox .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><input type='hidden' size='20' name='open[{$t}][{$oid}]' value='{$od['id']}'>{$od['type']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t\t\t<td><input type='hidden' name='open_amount[{$t}][{$oid}]' value='{$open_amount[$t]}[{$oid}]'>" . CUR . " {$open_amount[$t]}[{$oid}]</td>\n\t\t\t\t\t\t\t</tr>";
$Sl = "UPDATE cubit.open_stmnt SET balance=balance-'" . $open_amount[$t][$oid] . "' WHERE id='{$oid}'";
$Ri = db_exec($Sl) or errDie("Unable to update statement.");
}
$i++;
}
if (open()) {
$bout[$t] = $out[$t];
$out[$t] = $open_out[$t];
if ($out > 0) {
$sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.open_stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, balance, date, \n\t\t\t\t\t\t\t\ttype, st, div\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}', '0', '-{$out[$t]}', '-{$out[$t]}', '{$sdate[$t]}', \n\t\t\t\t\t\t\t\t'Payment Received', 'n', '" . USER_DIV . "'\n\t\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
//$confirm .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>";
}
$out[$t] = $bout[$t];
} else {
//$confirm .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>";}
}
}
if ($out[$t] > 0) {
recordCT($out[$t], $cus['cusnum'], 0, $accdate[$t]);
$cols = grp(m("cusnum", $cus["cusnum"]), m("invid", 0), m("amount", -$out[$t]), m("date", $sdate[$t]), m("type", "Payment Received"), m("div", USER_DIV), m("allocation_date", $accdate[$t]));
$dbobj = new dbUpdate("stmnt", "cubit", $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
custledger($cus['cusnum'], $bank_acc[$t], $sdate[$t], "PAYMENT", "Payment received.", $out[$t], "c");
}
}
/* start moving invoices */
// move invoices that are fully paid
$sql = "SELECT * FROM cubit.invoices WHERE balance=0 AND printed = 'y' AND done = 'y' AND div = '" . USER_DIV . "'";
$invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
while ($x = pg_fetch_array($invbRslt)) {
if (($prd = $x['prd']) == "0") {
$prd = PRD_DB;
}
// move invoice
$cols = grp(m("invid", $x["invid"]), m("invnum", $x["invnum"]), m("deptid", $x["deptid"]), m("cusnum", $x["cusnum"]), m("deptname", $x["deptname"]), m("cusacc", $x["cusacc"]), m("cusname", $x["cusname"]), m("surname", $x["surname"]), m("cusaddr", $x["cusaddr"]), m("cusvatno", $x["cusvatno"]), m("cordno", $x["cordno"]), m("ordno", $x["ordno"]), m("chrgvat", $x["chrgvat"]), m("terms", $x["terms"]), m("traddisc", $x["traddisc"]), m("salespn", $x["salespn"]), m("odate", $x["odate"]), m("delchrg", $x["delchrg"]), m("subtot", $x["subtot"]), m("vat", $x["vat"]), m("total", $x["total"]), m("age", $x["age"]), m("comm", $x["comm"]), m("discount", $x["discount"]), m("delivery", $x["delivery"]), m("docref", $x["docref"]), m("prd", $x["prd"]), m("delvat", $x["delvat"]), m("balance", 0), m("printed", "y"), m("done", "y"), m("username", USER_NAME), m("div", USER_DIV));
$dbobj = new dbUpdate("invoices", $prd, $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
// record movement
$cols = grp(m("invtype", "inv"), m("invnum", $x["invnum"]), m("prd", $x["prd"]), m("docref", $x["docref"]), m("div", USER_DIV));
$dbobj->setTable("movinv", "cubit");
$dbobj->setOpt($cols);
$dbobj->run();
$dbobj->free();
// move invoice items
$inv_items = new dbSelect("inv_items", "cubit", grp(m("where", wgrp(m("invid", $x["invid"]), m("div", USER_DIV)))));
$inv_items->run();
while ($xi = $inv_items->fetch_array()) {
$xi['vatcode'] += 0;
$xi['account'] += 0;
$xi['del'] += 0;
$cols = grp(m("invid", $x["invid"]), m("whid", $xi["whid"]), m("stkid", $xi["stkid"]), m("qty", $xi["qty"]), m("unitcost", $xi["unitcost"]), m("amt", $xi["amt"]), m("disc", $xi["disc"]), m("discp", $xi["discp"]), m("vatcode", $xi["vatcode"]), m("account", $xi["account"]), m("description", $xi["description"]), m("del", $xi["del"]), m("noted", $xi["noted"]), m("serno", $xi["serno"]), m("div", USER_DIV));
$dbobj->setTable("inv_items", $prd);
$dbobj->setOpt($cols);
$dbobj->run();
$dbobj->free();
}
/* remove invoice from cubit schema */
$dbobj = new dbDelete("invoices", "cubit", wgrp(m("invid", $x["invid"]), m("div", USER_DIV)));
$dbobj->run();
$dbobj->setTable("inv_items", "cubit");
$dbobj->run();
}
/* start moving invoices */
// move invoices that are fully paid
$sql = "SELECT * FROM cubit.invoices WHERE balance=0 AND printed = 'y' AND done = 'y' AND div = '" . USER_DIV . "'";
$invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
while ($x = pg_fetch_array($invbRslt)) {
if (($prd = $x['prd']) == "0") {
$prd = PRD_DB;
}
// move invoice
$cols = grp(m("invid", $x["invid"]), m("invnum", $x["invnum"]), m("deptid", $x["deptid"]), m("cusnum", $x["cusnum"]), m("deptname", $x["deptname"]), m("cusacc", $x["cusacc"]), m("cusname", $x["cusname"]), m("surname", $x["surname"]), m("cusaddr", $x["cusaddr"]), m("cusvatno", $x["cusvatno"]), m("cordno", $x["cordno"]), m("ordno", $x["ordno"]), m("chrgvat", $x["chrgvat"]), m("terms", $x["terms"]), m("traddisc", $x["traddisc"]), m("salespn", $x["salespn"]), m("odate", $x["odate"]), m("delchrg", $x["delchrg"]), m("subtot", $x["subtot"]), m("vat", $x["vat"]), m("total", $x["total"]), m("age", $x["age"]), m("comm", $x["comm"]), m("discount", $x["discount"]), m("delivery", $x["delivery"]), m("docref", $x["docref"]), m("prd", $x["prd"]), m("delvat", $x["delvat"]), m("balance", 0), m("printed", "y"), m("done", "y"), m("username", USER_NAME), m("div", USER_DIV));
$dbobj = new dbUpdate("invoices", $prd, $cols);
$dbobj->run(DB_INSERT);
$dbobj->free();
// record movement
$cols = grp(m("invtype", "inv"), m("invnum", $x["invnum"]), m("prd", $x["prd"]), m("docref", $x["docref"]), m("div", USER_DIV));
$dbobj->setTable("movinv", "cubit");
$dbobj->setOpt($cols);
$dbobj->run();
$dbobj->free();
// move invoice items
$inv_items = new dbSelect("inv_items", "cubit", grp(m("where", wgrp(m("invid", $x["invid"]), m("div", USER_DIV)))));
$inv_items->run();
while ($xi = $inv_items->fetch_array()) {
$xi['vatcode'] += 0;
$xi['account'] += 0;
$xi['del'] += 0;
$cols = grp(m("invid", $x["invid"]), m("whid", $xi["whid"]), m("stkid", $xi["stkid"]), m("qty", $xi["qty"]), m("unitcost", $xi["unitcost"]), m("amt", $xi["amt"]), m("disc", $xi["disc"]), m("discp", $xi["discp"]), m("vatcode", $xi["vatcode"]), m("account", $xi["account"]), m("description", $xi["description"]), m("del", $xi["del"]), m("noted", $xi["noted"]), m("serno", $xi["serno"]), m("div", USER_DIV));
$dbobj->setTable("inv_items", $prd);
$dbobj->setOpt($cols);
$dbobj->run();
$dbobj->free();
}
/* remove invoice from cubit schema */
$dbobj = new dbDelete("invoices", "cubit", wgrp(m("invid", $x["invid"]), m("div", USER_DIV)));
$dbobj->run();
$dbobj->setTable("inv_items", "cubit");
$dbobj->run();
}
#do journal for the settlement discount here ... now ...
if ($setamt[$t] > 0) {
db_conn('core');
#get settlement accid
$get_setacc = "SELECT accid FROM accounts WHERE accname = 'Debtors Settlement Discount'";
$run_setacc = db_exec($get_setacc) or errDie("Unable to get settlement account information");
$setaccid = pg_fetch_result($run_setacc, 0, 0);
#calculate the settlement vat ... and amt
if (isset($setvat[$t]) and $setvat[$t] == 'inc') {
db_connect();
$get_vcode = "SELECT * FROM vatcodes WHERE id = '{$setvatcode[$t]}' LIMIT 1";
$run_vcode = db_exec($get_vcode) or errDie("Unable to get vatcode informtion.");
if (pg_numrows($run_vcode) < 1) {
return "<li class='err'>Settlement Discount VAT Code Not Set.</li>";
}
$vd = pg_fetch_array($run_vcode);
#vat inc ... recalculate the amts
$setvatamt = sprint($setamt[$t] * ($vd['vat_amount'] / (100 + $vd['vat_amount'])));
$setamt[$t] = sprint($setamt[$t] - $setvatamt);
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "VAT");
$svattot = sprint($setamt[$t] + $setvatamt - ($setamt[$t] + $setvatamt) * 2);
$svatamt = sprint($setvatamt - $setvatamt * 2);
#process the vat amt ...
writetrans($vatacc, $dept['debtacc'], $accdate[$t], $refnum, $setvatamt, "VAT Received on Settlement Discount for Customer : {$cus['cusname']} {$cus['surname']}");
vatr($vd['id'], $accdate[$t], "OUTPUT", $vd['code'], $refnum, "VAT for Settlement Discount for Customer : {$cus['cusname']} {$cus['surname']}", $svattot, $svatamt);
} else {
#no vat for set amt ... do nothing
$setvatamt = 0;
$svattot = 0;
$svatamt = 0;
}
writetrans($setaccid, $dept['debtacc'], $accdate[$t], $refnum, sprint($setamt[$t]), "Settlement Discount For {$cus['cusname']} {$cus['surname']}");
custledger($cus['cusnum'], $bank_acc[$t], $sdate[$t], "{$refnum}", "Payment Settlement Discount Received.", sprint($setamt[$t] + $setvatamt), "c");
$sql = "\n\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$cus['cusnum']}', '0', '" . $svattot . "', '{$sdate[$t]}', \n\t\t\t\t\t'Settlement Discount for Payment. Ref {$refnum}', '" . USER_DIV . "', '{$accdate[$t]}'\n\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
db_connect();
#record this paid settlement discount for reporting ...
$settl_sql = "\n\t\t\t\tINSERT INTO settlement_cus (\n\t\t\t\t\tcustomer, amt, setamt, setvatamt, setvat, \n\t\t\t\t\tsetvatcode, tdate, sdate, refnum\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$cus['cusnum']}', '{$amt[$t]}', '{$setamt[$t]}', '{$setvatamt}', '{$setvat[$t]}', \n\t\t\t\t\t'{$setvatcode[$t]}', '{$accdate[$t]}', 'now', '{$refnum[$t]}'\n\t\t\t\t)";
$run_settl = db_exec($settl_sql) or errDie("Unable to get debtor settlement information.");
}
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
}
// status report
$write = "\n\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t<tr>\n\t\t\t\t<th>Bank Receipt</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Bank Receipt added to cash book.</td>\n\t\t\t</tr>\n\t\t</table>";
$OUTPUT = "\n\t\t<center>\n\t\t<table width='90%'>\n\t\t\t<tr valign='top'>\n\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t<td align='center'>" . mkQuickLinks(ql("bank-pay-add.php", "Add Bank Payment"), ql("bank-recpt-add.php", "Add Bank Receipt"), ql("bank-recpt-inv.php", "Add Customer Payment"), ql("cashbook-view.php", "View Cash Book")) . "\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>";
return $OUTPUT;
}
function writeLoan($_POST)
{
# get vars
extract($_POST);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($empnum, "num", 1, 20, "Invalid employee number.");
$v->isOk($loanid, "num", 1, 20, "Invalid loan ID.");
$v->isOk($loanamt, "float", 1, 10, "Invalid loan amount.");
$v->isOk($loanint, "float", 1, 5, "Invalid loan interest.");
$v->isOk($loanperiod, "num", 1, 3, "Invalid payback period.");
$v->isOk($loaninstall, "float", 1, 10, "Invalid monthly installment.");
$v->isOk($fringebenefit, "float", 1, 10, "Invalid fringe benefit amount.");
$v->isOk($accid, "num", 1, 9, "Invalid bank account selected.");
$v->isOk($account, "num", 1, 9, "Invalid contra account selected.");
$v->isOk($loan_account, "num", 1, 9, "Invalid loan account selected.");
$v->isOk($loan_type, "num", 1, 9, "Invalid loan type selected.");
$ldate = mkdate($lyear, $lmonth, $lday);
$v->isOk($ldate, "date", 1, 1, "Invalid loan date.");
$archdate = mkdate($arch_year, $arch_month, $arch_day);
$v->isOk($archdate, "date", 1, 1, "Invalid approval/denial date.");
if (!checkdate($lmonth, $lday, $lyear)) {
$v->addError("", "Invalid date.");
}
if ($account > 0 && isb($account) || isb($loan_account)) {
$v->addError("", "Main accounts blocked. Please select sub accounts.");
}
# display errors, if any
if ($v->isError()) {
$confirmCust = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirmCust .= "<li class='err'>" . $e["msg"] . "</li>";
}
return enterLoan($confirmCust);
}
# CHECK IF THIS DATE IS IN THE BLOCKED RANGE
$blocked_date_from = getCSetting("BLOCKED_FROM");
$blocked_date_to = getCSetting("BLOCKED_TO");
if (strtotime($ldate) >= strtotime($blocked_date_from) and strtotime($ldate) <= strtotime($blocked_date_to) and !user_is_admin(USER_ID)) {
return "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
# CHECK IF THIS DATE IS IN THE BLOCKED RANGE
$blocked_date_from = getCSetting("BLOCKED_FROM");
$blocked_date_to = getCSetting("BLOCKED_TO");
if (strtotime($archdate) >= strtotime($blocked_date_from) and strtotime($archdate) <= strtotime($blocked_date_to) and !user_is_admin(USER_ID)) {
return "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
# get employee details
db_connect();
$sql = "SELECT * FROM employees WHERE empnum='{$empnum}' AND div = '" . USER_DIV . "'";
$empRslt = db_exec($sql) or errDie("Unable to select employees from database.");
if (pg_numrows($empRslt) < 1) {
return "Invalid employee ID.";
}
$myEmp = pg_fetch_array($empRslt);
if (!isset($deny)) {
# check for previous loan
$sql = "SELECT empnum FROM employees WHERE empnum='{$empnum}' AND div = '" . USER_DIV . "' AND gotloan='t'::bool";
$chkRslt = db_exec($sql) or errDie("Unable to check existing loans for employee.");
if (pg_numrows($chkRslt) > 0) {
return "<li class='err'>Loan already exists for employee number: {$empnum}.</li>";
}
}
$date = date("Y-m-d");
pglib_transaction("BEGIN");
$totamount = sprint($loanperiod * $loaninstall);
$loanint_amt = $totamount - $loanamt;
$sql = "\n\t\tINSERT INTO emp_loanarchive (\n\t\t\tempnum, loanamt, loaninstall, loanint, loanperiod, loandate, archdate, loan_type, \n\t\t\tdiv, status\n\t\t) VALUES (\n\t\t\t'{$empnum}', '{$totamount}', '{$loaninstall}', '{$loanint}', '{$loanperiod}', '{$ldate}', '{$archdate}', '{$loan_type}', \n\t\t\t'" . USER_DIV . "', '" . (isset($deny) ? "D" : "A") . "'\n\t\t)";
$rslt = db_exec($sql) or errDie("Unable to pre archive loan.");
$loanaid = pglib_lastid('emp_loanarchive', 'id');
$rem_sql = "DELETE FROM loan_requests WHERE id = '{$loanid}'";
$run_rem = db_exec($rem_sql) or errDie("Unable to get loan requests information.");
if (!isset($deny)) {
$refnum = getrefnum();
if ($accid > 0) {
$bankacc = getbankaccid($accid);
}
if ($account > 0) {
$bankacc = $account;
}
writetrans($loan_account, $bankacc, $archdate, $refnum, $loanamt, "Loan granted to employee {$myEmp['fnames']} {$myEmp['sname']}.");
if ($accid > 0) {
banktrans($accid, "withdrawal", $archdate, "{$myEmp['fnames']} {$myEmp['sname']}", "Loan granted to employee {$myEmp['fnames']} {$myEmp['sname']}.", 0, $loanamt, $loan_account);
}
# write to db
$sql = "\n\t\t\tUPDATE cubit.employees \n\t\t\tSET loanamt = '{$totamount}', loanint = '{$loanint}', loanint_amt = '{$loanint_amt}', loanint_unpaid = '{$loanint_amt}', \n\t\t\t\tloanperiod = '{$loanperiod}', loaninstall = '{$loaninstall}', gotloan = 't'::bool, loanpayslip = '{$loanamt}', \n\t\t\t\tloanfringe = '{$fringebenefit}', loandate = '{$archdate}', expacc_loan = '{$loan_account}', \n\t\t\t\tloanamt_tot = '{$totamount}', loanid = '{$loanaid}' \n\t\t\tWHERE empnum = '{$empnum}' AND div = '" . USER_DIV . "'";
$loanRslt = db_exec($sql) or errDie("Unable to add loan to system.", SELF);
if (pg_cmdtuples($loanRslt) < 1) {
return "Unable to add loan to system.";
}
}
pglib_transaction("COMMIT");
$OUT = "<table " . TMPL_tblDflts . ">";
if (isset($deny)) {
$OUT .= "\n\t\t\t<tr>\n\t\t\t\t<th>Loan Denied And Request Archived.</th>\n\t\t\t</tr>";
} else {
$OUT .= "\n\t\t\t<tr>\n\t\t\t\t<th>Loan Granted And Added To System</th>\n\t\t\t</tr>";
}
$OUT .= "\n\t\t<tr class='datacell'>\n\t\t\t<td>Loan information successfully updated.</td>\n\t\t</tr>\n\t\t" . TBL_BR;
if (!isset($deny)) {
$OUT .= "\n\t\t\t<tr>\n\t\t\t\t<td><input type='button' onclick=\"document.location='../reporting/loan_approval.php?id={$loanaid}'\" value='Generate Approval Report'></td>\n\t\t\t</tr>";
}
$OUT .= "\n\t\t</table><br>" . mkQuickLinks(ql("loan_apply.php", "Add Loan Application"), ql("loan_apply_view.php", "View Loan Applications"), ql("../admin-employee-add.php", "Add Employee"), ql("../admin-employee-view.php", "View Employees"));
return $OUT;
}
function write($_POST)
{
# Get vars
extract($_POST);
if (isset($back)) {
return add($_POST);
}
# Validate input
require_lib("validate");
$v = new validate();
if ($frm == 'bnk') {
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
} else {
$v->isOk($laccid, "num", 1, 30, "Invalid Account.");
}
$v->isOk($date, "date", 1, 10, "Invalid Date Entry.");
$v->isOk($name, "string", 1, 255, "Invalid Person/Business paid to/received from.");
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
$v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number.");
$v->isOk($amount, "float", 1, 10, "Invalid amount.");
$v->isOk($accinv, "string", 1, 255, "Invalid account number (account involved).");
# 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;
}
# Date format
$date = explode("-", $date);
$date = $date[2] . "-" . $date[1] . "-" . $date[0];
# nasty zero
$cheqnum += 0;
if ($frm == 'bnk') {
# Get bank account name
db_connect();
$sql = "SELECT accname, bankname FROM bankacct WHERE bankid = '{$bankid}' AND div = '" . USER_DIV . "'";
$bankRslt = db_exec($sql);
$bank = pg_fetch_array($bankRslt);
$frmaccid = getbankaccid($bankid);
$details = "Transfer From Bank Account : {$bank['accname']} - {$bank['bankname']}";
} else {
# get account name
$laccRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $laccid);
$lacc = pg_fetch_array($laccRslt);
$frmaccid = $laccid;
$details = "Transfer From Account : {$lacc['topacc']}/{$lacc['accnum']} - {$lacc['accname']}";
}
pglib_transaction("BEGIN");
# Some info
$refnum = getrefnum();
# write trans
writetrans($accinv, $frmaccid, $date, $refnum, $amount, $descript);
if ($frm == 'bnk') {
# Record the payment record
db_connect();
$sql = "\n\t\t\tINSERT INTO cashbook (\n\t\t\t\tbankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, div\n\t\t\t) VALUES (\n\t\t\t\t'{$bankid}', 'withdrawal', '{$date}', '{$name}', '{$descript}', '{$cheqnum}', '{$amount}', 'no', '{$accinv}', '" . USER_DIV . "'\n\t\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
}
db_connect();
# Record tranfer for patty cash report
$sql = "\n\t\tINSERT INTO pettyrec (\n\t\t\tdate, type, det, amount, name, div\n\t\t) VALUES (\n\t\t\t'{$date}', 'Transfer', '{$descript}', '{$amount}', '{$details}', '" . USER_DIV . "'\n\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
pglib_transaction("COMMIT");
# Status report
$write = "\n\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t<tr>\n\t\t\t\t<th>Funds transfer to Petty cash</th>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td>Funds transfer to Petty cash has been added to the Petty cash book.</td>\n\t\t\t</tr>\n\t\t</table>";
# Main table (layout with menu)
$OUTPUT = "\n\t\t<center>\n\t\t<table width='90%'>\n\t\t\t<tr valign='top'>\n\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t<td align='center'>\n\t\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Quick Links</th>\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><a href='cashbook-view.php'>View Cash Book</a></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t\t\t</table>\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>";
return $OUTPUT;
}