function approve()
{
extract($_REQUEST);
if (!isset($approve) || $approve != "i" && $approve != "a") {
invalid_use("<li class='err'>Invalid action.<li>");
}
/* order info */
$qry = new dbSelect("recvpurch", "trh", grp(m("where", wgrp(m("id", $id)))));
$qry->run();
if ($qry->num_rows() <= 0) {
invalid_use("<li class='err'>Invalid Sales Order Id (TRHAPP).</li>");
}
$soi = $qry->fetch_array();
/* customer info */
$ci = qryCustomer($soi["custid"]);
/* sales person name */
if (empty($ci["sales_rep"])) {
$speoples = qrySalesPerson();
if ($speoples->num_rows() <= 0) {
$salespn = "General";
} else {
$speoples->fetch_array();
$salespn = $speoples->d["salesp"];
$speoples->free();
}
} else {
$m = qrySalesPerson($ci["sales_rep"]);
$salespn = $m["salesp"];
}
/* currency info */
$curinfo = qryCurrency($ci["fcid"]);
$cols = grp(m("deptid", "{$ci['deptid']}"), m("cusnum", "{$ci['cusnum']}"), m("cordno", ""), m("ordno", ""), m("chrgvat", $soi["vatinc"] == "yes" ? "inc" : "no"), m("terms", "{$ci['credterm']}"), m("salespn", $salespn), m("odate", "{$soi['pdate']}"), m("accepted", "n"), m("comm", ""), m("done", "y"), m("username", USER_NAME), m("deptname", "{$ci['deptname']}"), m("cusacc", "{$ci['accno']}"), m("cusname", "{$ci['cusname']}"), m("surname", "{$ci['surname']}"), m("cusaddr", "{$ci['addr1']}"), m("cusordno", "{$soi['purnum']}"), m("cusvatno", "{$ci['vatnum']}"), m("prd", "0"), m("div", USER_DIV), m("disc", "0.00"), m("discp", "0.00"), m("delchrg", "{$soi['shipchrg']}"), m("subtot", "{$soi['subtot']}"), m("traddisc", "0.00"), m("balance", "{$soi['balance']}"), m("vat", "{$soi['vat']}"), m("total", "{$soi['total']}"), m("jobid", "0"), m("jobnum", "0"), m("dir", ""), m("location", ""), m("fcid", "{$ci['fcid']}"), m("currency", "{$curinfo['symbol']}"), m("xrate", "{$curinfo['rate']}"), m("fbalance", "0.00"), m("fsubtot", "0.00"), m("discount", "0.00"), m("delivery", "{$soi['shipchrg']}"), m("delvat", "{$soi['delvat']}"), m("display_costs", "yes"), m("proforma", "no"), m("pinvnum", "0"));
$upd = new dbUpdate("sorders", "cubit", $cols);
$upd->run(DB_INSERT);
$sordid = $upd->lastvalue("sordid");
/* items */
$qry->reset();
$qry->setTable("recvpurch_items");
$qry->setOpt(grp(m("where", wgrp(m("recvpurch_id", $id)))));
$qry->run();
$upd->setTable("sorders_items");
while ($row = $qry->fetch_array()) {
if (empty($row["sup_stkcod"])) {
invalid_use("Supplier stock codes not setup for customer. Order ignored.");
}
$stkinfo = qryStockC($row["sup_stkcod"]);
$cols = grp(m("sordid", "{$sordid}"), m("whid", "{$stkinfo['whid']}"), m("stkid", "{$stkinfo['stkid']}"), m("qty", "{$row['qty']}"), m("div", USER_DIV), m("amt", "{$row['amt']}"), m("discp", "0.00"), m("disc", "0.00"), m("unitcost", "{$row['unitcost']}"), m("hidden", ""), m("funitcost", "0.00"), m("famt", "0.00"), m("pinv", "0.00"), m("vatcode", "{$stkinfo['vatcode']}"), m("description", "{$stkinfo['stkdes']}"), m("account", "0"));
$upd->setOpt($cols);
$upd->run(DB_INSERT);
}
/* set approve status */
$upd->reset();
$upd->setTable("recvpurch", "trh");
$upd->setOpt(grp(m("approved", "{$approve}")), "id='{$id}'");
$upd->run(DB_UPDATE);
/* get customer trh config */
$keyinfo = trhKeyCust($soi["custid"]);
$email = $keyinfo["email"];
/* send trh response message */
$purinfo = array("purid" => $soi["purid"], "status" => $approve);
$ret = send_trhmsg("cust", $soi["custid"], $email, "rsppur", $purinfo);
if ($approve == "i") {
header("Location: ../sorder-accept.php?sordid={$sordid}");
exit;
} else {
$OUT = listorders("<li class='err'>Successfully approved sales order.</li>");
}
return $OUT;
}
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)
{
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()
{
extract($_POST);
if (isset($back)) {
return enter();
}
if ($err = validate($_POST)) {
return enter($err);
}
/* check account number */
if (!isset($cusnum)) {
$qry = new dbSelect("customers", "cubit", grp(m("where", "accno='{$accno}' AND div='" . USER_DIV . "'")));
$qry->run();
if ($qry->num_rows() > 0) {
return enter("<li class='err'>A Customer/Client with this account number already exists.</li>");
}
}
$data = qryCategory($category);
$catname = $data["category"];
$data = qryClass($class);
$classname = $data["classname"];
$data = qryPricelist($pricelist);
$plist = $data["listname"];
$curr = getSymbol($fcid);
$currency = $curr["symbol"];
/* fix numerics */
$traddisc += 0;
$setdisc += 0;
$pricelist += 0;
$overdue += 0;
$credterm += 0;
$credlimit += 0;
pglib_transaction("BEGIN");
/* insert into database / update */
$cols = grp(m("deptid", $deptid), m("accno", $accno), m("surname", $surname), m("title", $title), m("init", $init), m("location", $location), m("fcid", $fcid), m("currency", $currency), m("category", $category), m("class", $class), m("addr1", $addr1), m("paddr1", $paddr1), m("del_addr1", $del_addr1), m("vatnum", $vatnum), m("contname", $contname), m("bustel", $bustel), m("tel", $tel), m("cellno", $cellno), m("fax", $fax), m("email", $email), m("url", $url), m("traddisc", $traddisc), m("setdisc", $setdisc), m("pricelist", $pricelist), m("chrgint", $chrgint), m("overdue", $overdue), m("intrate", $intrate), m("chrgvat", "yes"), m("credterm", $credterm), m("odate", $odate), m("credlimit", $credlimit), m("blocked", "no"), m("deptname", $deptname), m("classname", $classname), m("catname", $catname), m("lead_source", $lead_source), m("comments", $comments), m("sales_rep", $sales_rep), m("div", USER_DIV), m("bankname", $bankname), m("branname", $branname), m("brancode", $brancode), m("bankaccname", $bankaccname), m("bankaccno", $bankaccno), m("bankacctype", $bankacctype), m("team_id", $team_id), m("registration", $registration));
$where = wgrp(m("cusnum", isset($cusnum) ? $cusnum : 0));
$qryi = new dbUpdate("customers", "cubit", $cols, $where);
$qryi->run(DB_REPLACE);
/* get id */
if (!isset($cusnum)) {
$cusnum = pglib_lastid("customers", "cusnum");
$newcust = true;
// used later to check if we should create the ledgers
}
/* add to/update contact list */
$cols = grp(m("surname", $surname), m("title", $title), m("ref", "Customer"), m("tell", $tel), m("tell_office", $bustel), m("cell", $cellno), m("fax", $fax), m("email", $email), m("hadd", $addr1), m("padd", $paddr1), m("del_addr", $del_addr1), m("date", $odate), m("cust_id", $cusnum), m("con", "No"), m("lead_source", $lead_source), m("description", $comments), m("account_type", "Customer"), m("accountname", $surname), m("account_id", $cusnum), m("by", USER_NAME), m("div", USER_DIV));
$where = wgrp(m("cust_id", $cusnum));
$qryi->setTable("cons", "cubit");
$qryi->setOpt($cols, $where);
$qryi->run(DB_REPLACE);
if (PRD_STATE == "py") {
$audit_db = YR_NAME . "_audit";
$actyear = PYR_NAME;
} else {
$audit_db = "audit";
$actyear = YR_NAME;
}
if (isset($newcust)) {
/* create customer ledgers */
for ($i = 1; $i <= 12; ++$i) {
/* period customer ledger */
$cols = grp(m("cusnum", $cusnum), m("contra", 0), m("edate", $odate), m("sdate", raw("CURRENT_DATE")), m("eref", 0), m("descript", "Balance"), m("credit", 0), m("debit", 0), m("cbalance", 0), m("dbalance", 0), m("div", USER_DIV));
$qryi->setTable("custledger", "{$i}");
$qryi->setOpt($cols);
$qryi->run(DB_INSERT);
/* audit customer ledger */
$cols = grp(m("cusnum", $cusnum), m("contra", 0), m("edate", $odate), m("sdate", raw("CURRENT_DATE")), m("eref", 0), m("descript", "Balance"), m("credit", 0), m("debit", 0), m("cbalance", 0), m("dbalance", 0), m("div", USER_DIV), m("actyear", $actyear));
$qryi->setTable(getMonthName($i) . "_custledger", $audit_db);
$qryi->setOpt($cols);
$qryi->run(DB_INSERT);
}
if (isset($crm)) {
header("Location: crm/tokens-new.php?value={$surname}");
exit;
}
if ($re != "not") {
$qryi->setTable("invoices", "cubit");
$cols = grp(m("cusnum", $cusnum));
$where = wgrp(m("invid", $re));
$qryi->setOpt($cols, $where);
$qryi->run(DB_UPDATE);
header("Location: cust-credit-stockinv.php?invid={$re}&cont=1&letters=");
exit;
}
}
pglib_transaction("COMMIT") or errDie("Unable to add customer to database. (TC)");
// Update documents
$sql = "SELECT * FROM crm.ctmp_docs WHERE session='{$_REQUEST['CUBIT_SESSION']}'";
$ctdoc_rslt = db_exec($sql) or errDie("Unable to retrieve docs.");
while ($ctdoc_data = pg_fetch_array($ctdoc_rslt)) {
$sql = "INSERT INTO crm.customer_docs (cusnum, file, type, filename, size)\n\t\tVALUES ('{$cusnum}', '{$ctdoc_data['file']}', '{$ctdoc_data['type']}',\n\t\t\t\t'{$ctdoc_data['filename']}', '{$ctdoc_data['size']}')";
db_exec($sql) or errDie("Unable to save files to customer.");
$sql = "DELETE FROM crm.ctmp_docs WHERE id='{$ctdoc_data['id']}'";
db_exec($sql) or errDie("Unable to remove tmp file.");
}
$sql = "SELECT * FROM cubit.cons WHERE cust_id='{$cusnum}'";
$con_rslt = db_exec($sql) or errDie("Unable to retrieve contact.");
$con_data = pg_fetch_array($con_rslt);
if (pg_num_rows($con_rslt)) {
$con_out = "\n\t\t<a href='javascript:popupOpen(\"groupware/conper-add.php?type=conn&id={$con_data['id']}\")'>\n\t\t\tAdd Sub Contact\n\t\t</a>";
} else {
$con_out = "";
}
onthespot_declare();
$OUT = "\n\t<table " . TMPL_tblDflts . ">\n\t<tr>\n\t\t<th>Customer add/update successful.</th>\n\t</tr>\n\t<tr class='datacell'>\n\t\t<td>\n\t\t\tNew Customer <b>{$surname}</b>,\n\t\t\thas been successfully added to the system.\n\t\t\t{$con_out}</td>\n\t</tr>\n\t</table>";
$OUT .= onthespot_out(mkQuickLinks(ql("customers-new.php", "Add Customers"), ql("customers-view.php", "View Customers")));
return $OUT;
}
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 package($_POST)
{
$_POST = var_makesafe($_POST);
extract($_POST);
$week += 0;
if (isset($back)) {
return process($_POST);
}
$annual += 0;
$bonus += 0;
$paye_salary += 0;
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($empnum, "num", 1, 20, "Invalid employee number.");
$v->isOk($accid, "num", 1, 20, "Invalid bank number.");
$v->isOk($MON, "num", 1, 2, "Invalid month.");
$v->isOk($basic_sal, "float", 1, 40, "Invalid basic salary.");
$v->isOk($overamt, "float", 1, 40, "Invalid overtime amount.");
$v->isOk($income, "float", 1, 40, "Invalid income.");
$v->isOk($commission, "float", 0, 40, "Invalid commision.");
$v->isOk($abonus, "float", 0, 40, "Invalid Bonus.");
$v->isOk($loaninstall, "float", 0, 40, "Invalid loan installment.");
$v->isOk($paidamount, "float", 1, 40, "Invalid paid amount.");
if (isset($allowances)) {
foreach ($allowances as $key => $value) {
$v->isOk($allowances[$key], "float", 0, 20, "Invalid allowance amount " . ($key + 1) . ".");
}
}
if (isset($deductid)) {
foreach ($deductid as $key => $value) {
$v->isOk($deductid[$key], "num", 1, 20, "Invalid deductions ID.");
}
}
if (isset($deductions)) {
foreach ($deductions as $key => $value) {
$v->isOk($deductions[$key], "float", 0, 20, "Invalid deduction amount" . ($key + 1) . ".");
}
}
if (isset($allowid)) {
foreach ($allowid as $key => $value) {
$v->isOk($allowid[$key], "num", 1, 20, "Invalid allowance ID.");
}
}
if (isset($allowtax)) {
foreach ($allowtax as $key => $value) {
$v->isOk($allowtax[$key], "string", 2, 20, "Invalid allowance tax " . ($key + 1) . ".");
}
}
$date = mkdate($year, $mon, $day);
$pdate = mkdate($year, $MON, $pday);
if (!checkdate($mon, $day, $year)) {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
$mon = $MON;
# display errors, if any
if ($v->isError()) {
$write = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$write .= "<li class='err'>" . $e["msg"] . "</li>";
}
$write .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $write;
}
$basic_sal_save = $basic_sal;
if (isset($multi)) {
$basic_sal = sprint($basic_sal * $multi);
$tyear = $tyear / $multi;
} else {
$basic_sal = $basic_sal;
}
db_conn('cubit');
$nettpay = $income;
$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.";
}
$ecost = 0;
$myEmp = pg_fetch_array($empRslt);
// fringe benefits
$i = 0;
$fringes = "";
$fringes_desc = "";
if (isset($fringebens)) {
foreach ($fringebens as $key => $value) {
if ($fringebens[$key] > 0) {
$fringes_desc .= "\n\t\t\t\t<tr>\n\t\t\t\t\t<td>{$fringename[$key]}</td>\n\t\t\t\t\t<td>" . CUR . " {$fringebens[$key]}</td>\n\t\t\t\t</tr>";
}
}
}
if (!empty($fringes_desc)) {
$fringes_desc = "<tr><th colspan='2'>Fringe Benefits</th></tr>{$fringes_desc}";
}
$all_before = "";
$all_after = "";
$all_beforeamount = 0;
$all_afteramount = 0;
if (isset($allowtax)) {
foreach ($allowtax as $key => $perc) {
if ($perc == "Yes" and $allowances[$key] > 0) {
$all_before .= "<tr><td>{$allowname[$key]}</td><td align='right'>" . CUR . " {$allowances[$key]}</td></tr>";
$all_beforeamount = $all_beforeamount + $allowances[$key];
} elseif ($allowances[$key] > 0) {
$all_after .= "<tr><td>{$allowname[$key]}</td><td align='right'>" . CUR . " {$allowances[$key]}</td></tr>";
$all_afteramount = $all_afteramount + $allowances[$key];
}
}
}
$de_before = "\n\t\t<tr>\n\t\t\t<td colspan='2'>\n\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Details</th>\n\t\t\t\t\t\t<th>Employee aContribution</th>\n\t\t\t\t\t\t<!--<th>Employer Contribution</th>//-->\n\t\t\t\t\t</tr>";
$de_after = "\n\t\t<tr>\n\t\t\t<td colspan='2'>\n\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Details</th>\n\t\t\t\t\t\t<th>Employee Contribution</th>\n\t\t\t\t\t\t<!--<th>Employer Contribution</th>//-->\n\t\t\t\t\t</tr>";
$de_beforeamount = 0;
$de_afteramount = 0;
$de_beforeamount_emp = 0;
$de_afteramount_emp = 0;
if (isset($deducttax)) {
foreach ($deducttax as $key => $perc) {
if ($perc == "Yes" and $deductions[$key] > 0) {
$de_before .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td>{$deductname[$key]}</td>\n\t\t\t\t\t\t<td align='right'>" . CUR . " {$deductions[$key]}</td>\n<!--\t\t\t\t\t\t<td align='right'>" . CUR . " {$employer_deductions[$key]}</td> //-->\n\t\t\t\t\t</tr>";
$de_beforeamount = $de_beforeamount + $deductions[$key] + $employer_deductions[$key];
$de_beforeamount_emp += $employer_deductions[$key];
} elseif ($deductions[$key] > 0) {
$de_after .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td>{$deductname[$key]}</td>\n\t\t\t\t\t\t<td align='right'>" . CUR . " {$deductions[$key]}</td>\n<!--\t\t\t\t\t\t<td align='right'>" . CUR . " {$employer_deductions[$key]}</td> //-->\n\t\t\t\t\t</tr>";
$de_afteramount = $de_afteramount + $deductions[$key] + $employer_deductions[$key];
$de_afteramount_emp += $employer_deductions[$key];
}
}
}
$de_before .= "</table></td></tr>";
$de_after .= "</table></td></tr>";
if ($all_beforeamount > 0) {
$all_before = "<tr><td colspan='2'>Allowances</td></tr>" . $all_before;
}
if ($all_afteramount > 0) {
$all_after = "<tr><td colspan='2'>Allowances</td></tr>" . $all_after;
}
if ($de_beforeamount > 0) {
$de_before = "<tr><td colspan='2'>Deductions</td></tr>" . $de_before;
}
if ($de_afteramount > 0) {
$de_after = "<tr><td colspan='2'>Deductions</td></tr>" . $de_after;
}
$gros_sal = sprint($grossal);
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$accid}' 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.</li>";
}
$bank = pg_fetch_array($rslt);
$bankacc = $bank["accnum"];
$basic_sal = sprint($basic_sal);
$commission = sprint($commission);
$abonus = sprint($abonus);
$overamt = sprint($overamt);
$paye = sprint($paye);
$nettpay = sprint($nettpay);
$sdl = sprint($comp_sdl);
$amount = sprint($gros_sal + $comp_pension + $comp_provident + $comp_medical + $comp_other + $comp_uif + $comp_ret + $sdl);
$loaninstall = sprint($loaninstall);
//Original CC
//$cc = "<script> CostCenter('ct', 'Salaries', '$date', 'Salary Payment for employee, $myEmp[fnames] $myEmp[sname]', '$amount', '../'); </script>";
//New CC
$cc = "CostCenter('ct', 'Salaries', '{$date}', 'Salary Payment for employee, {$myEmp['fnames']} {$myEmp['sname']}', '{$amount}', '../'); ";
$ecost = $amount;
if ($commission > 0) {
$comDis = "<tr><td>Commission</td><td align='right'>" . CUR . " {$commission}</td></tr>";
} else {
$comDis = "";
}
if ($abonus > 0) {
$aboDis = "<tr><td>Annual Bonus</td><td align='right'>" . CUR . " {$abonus}</td></tr>";
} else {
$aboDis = "";
}
if ($overamt > 0) {
$oveDis = "<tr><td>Overtime</td><td align='right'>" . CUR . " {$overamt}</td></tr>";
} else {
$oveDis = "";
}
if ($loaninstall > 0) {
$loaDis = "<tr><td>Loan Instalment</td><td align='right'>" . CUR . " {$loaninstall}</td></tr>";
} else {
$loaDis = "";
}
if ($basic_sal != $gros_sal) {
$groDis = "<tr><td>Gross Salary</td><td align='right'>" . CUR . " {$gros_sal}</td></tr>";
} else {
$groDis = "";
}
if ($all_travel > 0) {
$talDis = "<tr><td>Travel Allowance</td><td align='right'>" . CUR . " {$all_travel}</td></tr>";
} else {
$talDis = "";
}
db_connect();
$Sl = "SELECT * FROM salset";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) > 0) {
$con = true;
} else {
$con = false;
}
$intrec = gethook("accnum", "salacc", "name", "interestreceived");
$uifbal = gethook("accnum", "salacc", "name", "uifbal");
$sdlbal = gethook("accnum", "salacc", "name", "sdlbal");
$pa = gethook("accnum", "salacc", "name", "pension");
$ma = gethook("accnum", "salacc", "name", "medical");
$cash_account = gethook("accnum", "salacc", "name", "cash");
$retire = gethook("accnum", "salacc", "name", "retire");
$provident = gethook("accnum", "salacc", "name", "provident");
$salconacc = gethook("accnum", "salacc", "name", "salaries control");
$commacc = gethook("accnum", "salacc", "name", "Commission");
$abonusacc = gethook("accnum", "salacc", "name", "Bonus");
$payeacc = gethook("accnum", "salacc", "name", "PAYE");
$uifacc = gethook("accnum", "salacc", "name", "UIF");
$providente = $myEmp["expacc_provident"];
$retiree = $myEmp["expacc_ret"];
$pax = $myEmp["expacc_pension"];
$uifexp = $myEmp["expacc_uif"];
$max = $myEmp["expacc_medical"];
$dedgenerale = $myEmp["expacc_other"];
$sdlexp = $myEmp["expacc_sdl"];
$salacc = $myEmp["expacc_salwages"];
$loanexp = $myEmp["expacc_loan"];
$reimbursexp = $myEmp["expacc_reimburs"];
if ($con) {
$uifexp = $salacc;
$sdlexp = $salacc;
$pax = $salacc;
$max = $salacc;
$retiree = $salacc;
}
// Get Bank account [the traditional way re: hook of hook]
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$accid}' 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.";
}
$bank = pg_fetch_array($Rslt);
$refnum = getrefnum($date);
# Debit uif acc and credit uif control acc
if ($comp_uif > 0) {
writetrans($uifexp, $uifbal, $date, $refnum, $comp_uif, "Company UIF Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if ($emp_uif > 0) {
db_conn("cubit");
$Sl = "UPDATE employees SET balance=balance-({$emp_uif}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $uifacc, $date, $refnum, "UIF", $emp_uif, "d");
writetrans($salconacc, $uifbal, $date, $refnum, $emp_uif, "Employee UIF Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
# Debit uif sdl and credit sdl control acc
writetrans($sdlexp, $sdlbal, $date, $refnum, $sdl, "SDL, {$myEmp['fnames']} {$myEmp['sname']}.");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance+({$grossal_nodedall}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $salacc, $date, $refnum, "Gross Salary", $grossal_nodedall, "c");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
# Debit salaries acc and credit salaries control acc
writetrans($salacc, $salconacc, $date, $refnum, $grossal_nodedall, "Gross Salary proccessing for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
if ($commission > 0) {
if ($con) {
$commacc = $salacc;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance+({$commission}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $commacc, $date, $refnum, "Commission", $commission, "c");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
# Debit commission acc and credit salaries control acc
writetrans($commacc, $salconacc, $date, $refnum, $commission, "Commission for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if ($abonus > 0) {
if ($con) {
$abonusacc = $salacc;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance+({$abonus}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $abonusacc, $date, $refnum, "Bonus", $abonus, "c");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
# Debit bonus acc and credit salaries control acc
writetrans($abonusacc, $salconacc, $date, $refnum, $abonus, "Bonus for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if ($paye > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$paye}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $payeacc, $date, $refnum, "PAYE", $paye, "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
# Debit salaries control acc and credit PAYE control acc
writetrans($salconacc, $payeacc, $date, $refnum, $paye, "PAYE for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
# Pay allowances accounts
if (isset($allowid)) {
foreach ($allowid as $i => $id) {
# Debit allowances acc and credit salaries control acc
if ($con) {
$allowaccs[$i] = $salacc;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance+({$allowances[$i]}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $allowaccs[$i], $date, $refnum, "Allowance", $allowances[$i], "c");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($allowaccs[$i], $salconacc, $date, $refnum, $allowances[$i], "Allowances for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
}
}
# Pay Deductions accounts
if (isset($deductid)) {
foreach ($deductid as $i => $id) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$deductions[$i]}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $dedaccs[$i], $date, $refnum, "Deduction", $deductions[$i], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
# Debit salaries control acc and credit acc
// salcon acc - ded balance acc
writetrans($salconacc, $dedaccs[$i], $date, $refnum, $deductions[$i], "Deductions for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
db_conn("cubit");
$sql = "SELECT * FROM salded WHERE id='{$id}'";
$rslt = db_exec($sql) or errDie("Error reading deduction information.");
$dedinfo = pg_fetch_array($rslt);
}
}
if ($comp_pension > 0) {
writetrans($pax, $pa, $date, $refnum, $comp_pension, "Company Pension Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if ($emp_pension > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_pension}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $pa, $date, $refnum, "Pension Contribution", $emp_pension, "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $pa, $date, $refnum, $emp_pension, "Pension Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if ($comp_medical > 0) {
writetrans($max, $ma, $date, $refnum, $comp_medical, "Company Medical Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if ($emp_medical > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_medical}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $ma, $date, $refnum, "Medical Contribution", $emp_medical, "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $ma, $date, $refnum, $emp_medical, "Employee Medical Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if ($comp_provident > 0) {
writetrans($providente, $provident, $date, $refnum, $comp_provident, "Company Provident Fund Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if ($emp_provident > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_provident}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $provident, $date, $refnum, "Provident Fund Contribution", $emp_provident, "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $provident, $date, $refnum, $emp_provident, "Provident Fund Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if (false && $comp_other > 0) {
writetrans($dedgenerale, $dedgeneral, $date, $refnum, $comp_other, "Company Contribution to Other Deductions, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if (false && $emp_other > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_other}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $dedgeneral, $date, $refnum, "Other Deductions Contribution", $emp_other, "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $dedgeneral, $date, $refnum, $emp_other, "Other Deductions Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if ($emp_ret > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_ret}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $retire, $date, $refnum, "Retirement Annuity Contribution", $emp_ret, "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $retire, $date, $refnum, $emp_ret, "Employee Retirement Annuity Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
if ($comp_ret > 0) {
writetrans($retiree, $retire, $date, $refnum, $comp_ret, "Company Retirement Annuity Contribution, {$myEmp['fnames']} {$myEmp['sname']}.");
}
db_conn('cubit');
$mons = "{$mon};";
$due = sprint($nettpay - $paidamount);
//, balance=balance+'$due
$sql = "\n\t\tUPDATE employees \n\t\tSET lastpay = '{$mons}', loanamt = (loanamt - cast(float '{$loaninstall}' as numeric)), \n\t\t\tloanfringe = (loanfringe - cast(float '{$fringe_loan}' as numeric)) \n\t\tWHERE empnum = '{$empnum}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to get employee details.");
// check if loan is 0, then unmark loan as active, and store in archive
$sql = "SELECT loanid FROM employees WHERE loanamt=0 AND empnum='{$empnum}' AND gotloan='t'::bool";
$rslt = db_exec($sql) or errDie("Error reading employee details for loan.");
if (pg_num_rows($rslt) > 0) {
$loanid = pg_fetch_result($rslt, 0, 0);
$sql = "UPDATE employees SET gotloan='f'::bool, loaninstall='0' WHERE empnum='{$empnum}'";
$rslt = db_exec($sql) or errDie("Unable to update employee loan status.");
$sql = "UPDATE emp_loanarchive SET donedata=CURRENT_DATE WHERE id='{$loanid}'";
$rslt = db_exec($sql) or errDie("Unable to archive loan.");
$sql = "SELECT loanint_unpaid FROM employees WHERE empnum='{$empnum}'";
$rslt = db_exec($sql) or errDie("Error reading loan interest for installment.");
$loanint = sprint(pg_fetch_result($rslt, 0, 0));
} else {
if ($loaninstall > 0) {
$sql = "SELECT loanamt_tot, loanint_amt FROM employees WHERE empnum='{$empnum}'";
$rslt = db_exec($sql) or errDie("Error reading loan interest for installment.");
$loan_tot = pg_fetch_result($rslt, 0, 0);
$loan_totint = pg_fetch_result($rslt, 0, 1);
$loanint = sprint($loaninstall / $loan_tot * $loan_totint);
} else {
$loanint = 0;
}
}
$sql = "\n\t\tUPDATE employees \n\t\tSET loanint_unpaid = (loanint_unpaid - cast(float '{$loanint}' as numeric)) \n\t\tWHERE empnum = '{$empnum}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update employee interest.");
if ($loaninstall > 0 && !empty($loanexp)) {
$loaninstall += 0;
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$loaninstall}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $loanexp, $date, $refnum, "Loan Instalment", $loaninstall, "d");
# Debit salaries control acc and credit loan control acc
writetrans($salconacc, $loanexp, $date, $refnum, $loaninstall - $loanint, "Loan Installment for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
writetrans($salconacc, $intrec, $date, $refnum, $loanint, "Loan Interest for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
/* record this month's loan amounts (for reversal purposes) */
// determine the salary period
switch ($myEmp["payprd"]) {
case "d":
$lprd = date_part($pdate, DP_DAY);
break;
case "m":
case "f":
case "w":
default:
$lprd = $week;
break;
}
// record it!
db_conn("cubit");
$sql = "\n\t\t\tINSERT INTO emp_loaninstallments (\n\t\t\t\tempnum, fdate, fperiod, fmonth, fyear, installment, interest, fringe\n\t\t\t) VALUES (\n\t\t\t\t'{$empnum}', '{$date}', '{$lprd}', '{$mon}', '" . EMP_YEAR . "', '{$loaninstall}', '{$loanint}', '{$fringe_loan}'\n\t\t\t)";
$rslt = db_exec($sql) or errDie("Error record loan fringe benefit.");
}
$loaninstall = $loaninstall + 0;
$totded = sprint($de_beforeamount + $de_afteramount + $emp_pension + $emp_medical + $emp_provident + $emp_ret + $emp_other);
$totded_employer = sprint($de_beforeamount_emp + $de_afteramount_emp + $comp_pension + $comp_medical + $comp_provident + $comp_ret + $comp_other);
$totall = sprint($all_beforeamount + $all_afteramount + $all_travel);
$parkage = "\n\t\t<br><br>\n\t\t<center>\n\t\t{$cc}\n\t\t<table border=2 cellpadding='4' cellspacing='0' width='750' bordercolor='#000000'>\n\t\t\t<tr>\n\t\t\t\t<td align='center'><b>Description</b></td>\n\t\t\t\t<td width='100' align='center'><b>Amount</b></td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td>Basic salary</td>\n\t\t\t\t<td align='right'>" . CUR . " {$basic_sal}</td>\n\t\t\t</tr>\n\t\t\t{$comDis}\n\t\t\t{$aboDis}\n\t\t\t{$fringes_desc}\n\t\t\t{$all_before}\n\t\t\t{$de_before}\n\t\t\t{$groDis}\n\t\t\t{$talDis}\n\t\t\t<tr>\n\t\t\t\t<td>UIF</td>\n\t\t\t\t<td align='right'>" . CUR . " {$emp_uif}</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td>PAYE</td>\n\t\t\t\t<td align='right'>" . CUR . " {$paye}</td>\n\t\t\t</tr>\n\t\t\t{$loaDis}\n\t\t\t{$all_after}\n\t\t\t{$de_after}\n\t\t\t<tr>\n\t\t\t\t<td><b>Nett Pay</b></td>\n\t\t\t\t<td align='right'><b>" . CUR . " {$nettpay}</b></td>\n\t\t\t</tr>\n\t\t</form>\n\t\t</table>\n\t\t</center>";
$parkagesave = "\n\t\t<br><br>\n\t\t<center>\n\t\t<table border='2' width='750' border=2 cellpadding='4' cellspacing='0' bordercolor='#000000'>\n\t\t\t<tr>\n\t\t\t\t<td align='center'><b>Description</b></td>\n\t\t\t\t<td width='100' align='center'><b>Amount</b></td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td>Basic salary</td>\n\t\t\t\t<td align='right'>" . CUR . " {$basic_sal}</td>\n\t\t\t</tr>\n\t\t\t{$comDis}\n\t\t\t{$aboDis}\n\t\t\t{$fringes_desc}\n\t\t\t{$all_before}\n\t\t\t{$de_before}\n\t\t\t{$groDis}\n\t\t\t{$talDis}\n\t\t\t<tr>\n\t\t\t\t<td>UIF</td>\n\t\t\t\t<td align='right'>" . CUR . " {$emp_uif}</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td>PAYE</td>\n\t\t\t\t<td align='right'>" . CUR . " {$paye}</td>\n\t\t\t</tr>\n\t\t\t{$loaDis}\n\t\t\t{$all_after}\n\t\t\t{$de_after}\n\t\t\t<tr>\n\t\t\t\t<td><b>Nett Pay</b></td>\n\t\t\t\t<td align='right'><b>" . CUR . " {$nettpay}</b></td>\n\t\t\t</tr>\n\t\t</form>\n\t\t</table>\n\t\t</center>";
$OUTPUT = $parkage;
$save = base64_encode($parkagesave);
$Date = $date;
$np = $nettpay;
if (isset($rbsa)) {
$np = sprint($np - array_sum($rbsa));
}
/* hack to store the pay date for dailies */
if ($myEmp["payprd"] == "d") {
$week = $pday;
}
if (empty($novert)) {
$novert = "0";
}
if (empty($hovert)) {
$hovert = "0";
}
db_conn("cubit");
$sql = "\n \t\tINSERT INTO cubit.salpaid (\n \t\t\tempnum, month, bankid, salary, comm, uifperc, uif, payeperc, paye, totded, totded_employer, \n\t\t\ttotallow, loanins, tot_fringe, div, display, saldate, week, cyear, novert, \n \t\t\thovert, taxed_sal, hours, salrate, bonus\n \t\t) VALUES (\n \t\t\t'{$empnum}', '{$mon}', '{$accid}', '{$np}', '{$commission}', '0', '{$emp_uif}', '0', '{$paye}', '{$totded}', '{$totded_employer}', \n\t\t\t'{$totall}', '{$loaninstall}', '{$fringe_tot}', '" . USER_DIV . "', '{$save}', '{$Date}', '{$week}', '" . EMP_YEAR . "', '{$novert}', \n \t\t\t'{$hovert}', '{$paye_salary}', '{$multi}', '{$basic_sal_save}', '{$abonus}'\n \t\t)";
$Ry = db_exec($sql) or errDie("Unable to insert record.");
$id = pglib_lastid("salpaid", "id");
$year = $year;
$payslip_id = $id;
db_conn("cubit");
$Sl = "SELECT * FROM cubit.rbs ORDER BY name";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$i = 0;
if (pg_num_rows($Ri) > 0) {
while ($td = pg_fetch_array($Ri)) {
if (!isset($rbsa[$td['id']]) || $rbsa[$td['id']] < 1) {
continue;
}
$rb = sprint($rbsa[$td['id']]);
db_conn("cubit");
$sql = "\n\t\t\t\tINSERT INTO emp_inc (\n\t\t\t\t\temp, year, period, date, week, payslip, type, code, description, \n\t\t\t\t\tqty, rate, amount, ex\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$Date}', '{$week}', '{$payslip_id}', '{$td['id']}', '', '{$td['name']}', \n\t\t\t\t\t'1', '0', '{$rb}', 'RBS'\n\t\t\t\t)";
db_exec($sql) or errDie("unable to insert data.");
$sql = "UPDATE employees SET balance=balance+({$rb}) WHERE empnum = '{$empnum}'";
db_exec($sql) or errDie("Unable to get employee details.");
empledger($empnum, $td['account'], $date, $refnum, "Reimbursement", $rb, "c");
writetrans($td['account'], $salconacc, $date, $refnum, $rb, "Reimbursement for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
}
}
if (isset($subsname)) {
foreach ($subsname as $sid => $sn) {
if (empty($subsamt[$sid]) || $subsamt[$sid] <= 0) {
continue;
}
$samt = sprint($subsamt[$sid]);
$i++;
db_conn('cubit');
$cols = grp(m("emp", $empnum), m("year", EMP_YEAR), m("period", $mon), m("week", $week), m("date", $Date), m("payslip", $payslip_id), m("type", $sid), m("code", ""), m("description", $subsname[$sid]), m("qty", 1), m("rate", 0), m("amount", $samt), m("ex", "SUBS"));
$subin = new dbUpdate("emp_inc", "cubit", $cols);
$subin->run(DB_INSERT);
$cols = grp(m("balance", raw("balance+({$samt})")));
$subin->setTable("employees");
$subin->setOpt($cols, wgrp(m("empnum", $empnum)));
$subin->run(DB_UPDATE);
empledger($empnum, $subsacc[$sid], $date, $refnum, "Subsistence Allowance: {$subsname[$sid]}", $samt, "c");
writetrans($subsacc[$sid], $salconacc, $date, $refnum, $samt, "Subsistence Allownace ({$subsname[$sid]}) for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
}
}
if ($myEmp['paytype'] == "Cash") {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$paidamount}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $cash_account, $date, $refnum, "Payment(Cash)", $paidamount, "d");
writetrans($salconacc, $cash_account, $date, $refnum, $paidamount, "Salary Payment(Cash) for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
} elseif ($myEmp['paytype'] == "Ledger Account") {
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$paidamount}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $account, $date, $refnum, "Payment(Ledger Account)", $paidamount, "d");
writetrans($salconacc, $account, $date, $refnum, $paidamount, "Salary Payment(Ledger Account) for employee, {$myEmp['fnames']} {$myEmp['sname']}.");
} else {
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$paidamount}) WHERE empnum = '{$empnum}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($empnum, $bankacc, $date, $refnum, "Payment(Bank)", $paidamount, "d");
writetrans($salconacc, $bankacc, $date, $refnum, $paidamount, "Salary Payment for employee(Bank), {$myEmp['fnames']} {$myEmp['sname']}.");
# issue bank record
banktrans($accid, "withdrawal", $date, "{$myEmp['fnames']} {$myEmp['sname']}", "Salary Payment for employee, {$myEmp['fnames']} {$myEmp['sname']}", 0, $paidamount, $salconacc, $myEmp['empnum']);
}
db_conn('cubit');
/*
writetrans($uifexp,$uifbal , $date, $refnum, $uif, "Company UIF Contribution, $myEmp[fnames] $myEmp[sname].");
*/
# Debit uif sdl and credit sdl control acc
// writetrans($sdlexp,$sdlbal , $date, $refnum, $sdl, "SDL, $myEmp[fnames] $myEmp[sname].");
db_conn("cubit");
if ($comp_uif > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_com (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'UIFC', '', 'UIF', '1', '0','{$comp_uif}')";
$Ri = db_exec($Sl) or errDie("unable to insert data1.");
}
if ($emp_uif > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'UIFE', '', 'UIF', '1', '0', '{$emp_uif}')";
$Ri = db_exec($Sl) or errDie("unable to insert data3.");
}
if ($sdl > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_com (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'SDL', '', 'SDL', '1', '0', '{$sdl}')";
$Ri = db_exec($Sl) or errDie("unable to insert data2.");
}
if ($paye > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'PAYE', '', 'PAYE', '1', '0', '{$paye}')";
$Ri = db_exec($Sl) or errDie("unable to insert data3.");
}
if ($basic_sal > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_inc (emp, year, period, week, date, payslip, type, code, description, pension, qty, rate, amount, ex) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'INC', '', 'Basic Salary', '', '1', '0', '{$basic_sal}', '')";
$Ri = db_exec($Sl) or errDie("unable to insert data4.");
}
if ($myEmp["loanpayslip"] > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_inc (emp, year, period, week, date, payslip, type, code, description, pension, qty, rate, amount, ex) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'LOAN', '', 'Employee Loan', '', '1', '0', '{$myEmp['loanpayslip']}', '')";
$Ri = db_exec($Sl) or errDie("unable to insert loan data for employee income on payslip.");
$sql = "UPDATE employees SET loanpayslip='0' WHERE empnum='{$empnum}'";
$rslt = db_exec($sql) or errDie("Error updating loan information for payslip.");
}
if ($bonus > 0 && $myEmp["payprd"] != "f" && $myEmp["payprd"] != "w") {
$Sl = "\n\t\t\tINSERT INTO emp_inc (emp, year, period, week, date, payslip, type, code, description, pension, qty, rate, amount, ex) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'INCB', '', 'Bonus', '', '1', '0', '{$bonus}', '')";
$Ri = db_exec($Sl) or errDie("unable to insert data5.");
} else {
if ($bonus > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_inc (emp, year, period, week, date, payslip, type, code, description, pension, qty, rate, amount, ex) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'INCB', '', 'Special Bonus/Additional Salary', '', '1', '0', '{$bonus}', '')";
$Ri = db_exec($Sl) or errDie("unable to insert data5.");
}
}
if ($annual > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_inc (emp, year, period, week, date, payslip, type, code, description, pension, qty, rate, amount, ex) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'INCAB', '', 'Annual Bonus', '', '1', '0', '{$annual}', '')";
$Ri = db_exec($Sl) or errDie("unable to insert data5.");
}
if ($commission > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_inc (emp, year, period, week, date, payslip, type, code, description, pension, qty, rate, amount, ex) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'INCC', '', 'Commission', '', '1', '0', '{$commission}', '')";
$Ri = db_exec($Sl) or errDie("unable to insert data6.");
}
if ($abonus > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_inc (emp, year, period, week, date, payslip, type, code, description, pension, qty, rate, amount, ex) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'INCAB', '', 'Bonus', '', '1', '0', '{$abonus}', '')";
$Ri = db_exec($Sl) or errDie("unable to insert data6.");
}
if ($all_travel > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_inc (emp, year, period, week, date, payslip, type, code, description, pension, qty, rate, amount, ex) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'INCT', '', 'Travel Allowance', '', '1', '0', '{$all_travel}', '')";
$Ri = db_exec($Sl) or errDie("unable to insert data7.");
}
if ($loaninstall > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'DEDL', '', 'Loan Repayment', '1', '0', '{$loaninstall}')";
$Ri = db_exec($Sl) or errDie("unable to insert data8.");
}
if ($comp_pension > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_com (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'COMP', '', 'Pension', '1', '0', '{$comp_pension}')";
$Ri = db_exec($Sl) or errDie("unable to insert data9.");
}
if ($emp_pension > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'DEDP', '', 'Pension', '1', '0', '{$emp_pension}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($comp_ret > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_com (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'COMR', '', 'Retirement Annuity Fund', '1', '0', '{$comp_ret}')";
$Ri = db_exec($Sl) or errDie("unable to insert data9.");
}
if ($emp_ret > 0) {
$Sl = "\n \tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n \tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'DEDR', '', 'Retirement Annuity Fund', '1', '0', '{$emp_ret}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($myEmp["fringe_car1_contrib"] > 0) {
$Sl = "\n \tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n \tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'DEDA', '', 'Motorcar 1 Contribution for Use', '1', '0', '{$myEmp['fringe_car1_contrib']}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($myEmp["fringe_car2_contrib"] > 0) {
$Sl = "\n \tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n \tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'DEDB', '', 'Motorcar 2 Contribution for Use', '1', '0', '{$myEmp['fringe_car2_contrib']}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($comp_medical > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_com (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'COMM', '', 'Medical Contribution', '1', '0', '{$comp_medical}')";
$Ri = db_exec($Sl) or errDie("unable to insert data.11");
}
if ($emp_medical > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'DEDM', '', 'Medical Contribution', '1', '0', '{$emp_medical}')";
$Ri = db_exec($Sl) or errDie("unable to insert data.12");
}
if ($comp_provident > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_com (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'COMV', '', 'Provident', '1', '0', '{$comp_provident}')";
$Ri = db_exec($Sl) or errDie("unable to insert data9.");
}
if ($emp_provident > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'DEDV', '', 'Provident', '1', '0', '{$emp_provident}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($comp_other > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_com (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'COMO', '', 'Other Deductions', '1', '0', '{$comp_other}')";
$Ri = db_exec($Sl) or errDie("unable to insert data9.");
}
if ($emp_other > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'DEDO', '', 'Other Deductions', '1', '0', '{$emp_other}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($overamt > 0) {
$Sl = "\n\t\t\tINSERT INTO emp_inc (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount, ex) \n\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$id}', 'INCO', '', 'Over Time', '1', '0', '{$overamt}', '')";
$Ri = db_exec($Sl) or errDie("unable to insert data.13");
}
$payslip_id = $id;
// fringe benefits
$frinupd = new dbUpdate("emp_frin", "cubit");
if (isset($fringebens)) {
foreach ($fringebens as $key => $value) {
$cols = grp(m("emp", $empnum), m("year", EMP_YEAR), m("period", $mon), m("week", $week), m("fdate", $Date), m("payslip", $payslip_id), m("code", $key), m("description", $fringename[$key]), m("qty", 1), m("amount", sprint($fringebens[$key])));
$frinupd->setCols($cols);
$frinupd->run(DB_INSERT);
}
}
if ($fringe_loan > 0) {
$cols = grp(m("emp", $empnum), m("year", $year), m("period", $mon), m("fdate", $Date), m("payslip", $payslip_id), m("code", "FRINLOAN"), m("description", "Loan Fringe Benefit"), m("qty", 1), m("amount", $fringe_loan));
$frinupd->setCols($cols);
$frinupd->run(DB_INSERT);
}
if ($fringe_medical > 0) {
$cols = grp(m("emp", $empnum), m("year", $year), m("period", $mon), m("fdate", $Date), m("payslip", $payslip_id), m("code", "FRINMED"), m("description", "Medical Fringe Benefit"), m("qty", 1), m("amount", $fringe_medical));
$frinupd->setCols($cols);
$frinupd->run(DB_INSERT);
}
if ($fringe_car1 > 0) {
$cols = grp(m("emp", $empnum), m("year", $year), m("period", $mon), m("fdate", $Date), m("payslip", $payslip_id), m("code", "FRINCAR1"), m("description", "Fringe Benefit: Vehicle 1"), m("qty", 1), m("amount", $fringe_car1));
$frinupd->setCols($cols);
$frinupd->run(DB_INSERT);
}
if ($fringe_car2 > 0) {
$cols = grp(m("emp", $empnum), m("year", $year), m("period", $mon), m("fdate", $Date), m("payslip", $payslip_id), m("code", "FRINCAR2"), m("description", "Fringe Benefit: Vehicle 2"), m("qty", 1), m("amount", $fringe_car2));
$frinupd->setCols($cols);
$frinupd->run(DB_INSERT);
}
if (isset($allowid)) {
$Sl = "SELECT id,allowance FROM allowances";
$Ri = db_exec($Sl) or errDie("Unable to get allowances.");
while ($data = pg_fetch_array($Ri)) {
$allname[$data['id']] = $data['allowance'];
}
foreach ($allowid as $i => $id) {
$aname = $allname[$allowid[$i]];
if (($allowances[$i] = sprint($allowances[$i])) <= 0) {
continue;
}
$Sl = "\n\t\t\t\tINSERT INTO emp_inc (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount, ex)\n\t\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$payslip_id}', '{$allowid[$i]}', '', '{$aname}', '1', '0', '{$allowances[$i]}', '')";
$Ri = db_exec($Sl) or errDie("unable to insert data.");
}
}
# Pay Deductions accounts
if (isset($deductid)) {
$Sl = "SELECT id,deduction FROM salded";
$Ri = db_exec($Sl) or errDie("Unabel to get get dat.");
while ($data = pg_fetch_array($Ri)) {
$dnames[$data['id']] = $data['deduction'];
}
foreach ($deductid as $i => $id) {
$dname = $dnames[$deductid[$i]];
# Debit salaries control acc and credit acc
if (($deductions[$i] = sprint($deductions[$i])) > 0) {
$Sl = "\n\t\t\t\t\tINSERT INTO emp_ded (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$payslip_id}', '{$deductid[$i]}', '', '{$dname}', '1', '0', '{$deductions[$i]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data.");
}
if (($employer_deductions[$i] = sprint($employer_deductions[$i])) > 0) {
$Sl = "\n\t\t\t\t\tINSERT INTO emp_com (emp, year, period, week, date, payslip, type, code, description, qty, rate, amount) \n\t\t\t\t\tVALUES ('{$empnum}', '" . EMP_YEAR . "', '{$mon}', '{$week}', '{$Date}', '{$payslip_id}', '{$deductid[$i]}', '', '{$dname}', '1', '0', '{$employer_deductions[$i]}')";
//$Ri=db_exec($Sl) or errDie("unable to insert data1.");
}
}
}
$id = $payslip_id;
$ecost += 0;
db_conn('cubit');
$Sl = "SELECT * FROM empc WHERE emp='{$empnum}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) > 0) {
while ($data = pg_fetch_array($Ri)) {
db_conn('cubit');
$sql = "SELECT * FROM costcenters WHERE ccid = '{$data['cid']}'";
$ccRslt = db_exec($sql) or errDie("Unable to retrieve Cost centers from database.");
$cc = pg_fetch_array($ccRslt);
$amount = sprint($ecost * $data['amount'] / 100);
db_conn(PRD_DB);
$sql = "\n\t\t\t\tINSERT INTO cctran (\n\t\t\t\t\tccid, trantype, typename, edate, \n\t\t\t\t\tdescription, amount, username, div\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$cc['ccid']}', 'ct', 'Salary', '{$Date}', \n\t\t\t\t\t'Salary for employee, {$myEmp['fnames']} {$myEmp['sname']}', '{$amount}', '" . USER_NAME . "', '" . USER_DIV . "'\n\t\t\t\t)";
$insRslt = db_exec($sql) or errDie("Unable to retrieve insert Cost center amounts into database.");
}
}
/* update printslip setting */
setCSetting("EMP_PRINTSLIP", $printslip);
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
if ($printslip != "n") {
$OUTPUT = "<script>printer('salwages/payslip-print.php?id={$id}');spmove('../main.php');</script>";
} else {
$OUTPUT = "\n\t\t\t<h3>Process Employee Salary</h3>\n\t\t\tSuccessfully processed salary.<br /><br />" . mkQuickLinks(ql("salaries-staff.php", "Process Employee Salary"), ql("../admin-employee-view.php", "View Employees/Process Salaries by Batch"), ql("../admin-employee-add.php", "Add New Employee"), ql("settings-acc-edit.php", "Salary Settings"));
}
//$OUTPUT .= "<script>move('../index-salaries.php');</script>";
require "../template.php";
}
function cp2($id, $amount, $description, $contra, $refnum, $date, $cheque = 0, $bankid)
{
$cheque += 0;
$sdate = date("Y-m-d");
$accdate = $date;
if ($accdate == 0) {
$accdate = date("Y-m-d");
}
$cus = qryCustomer($id, "cusnum, deptid, cusname, surname");
$dept = qryDepartment($cus["deptid"], "debtacc");
// db_connect();
//
// $Sl = "SELECT cusnum,deptid,cusname,surname FROM customers WHERE cusnum = '$id' AND div = '".USER_DIV."'";
// $Ri = db_exec($Sl) or errDie("Unable to get data.");
// $cus = pg_fetch_array($Ri);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}'";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 1) {
return "Invalid bank acc.";
}
$link = pg_fetch_array($Rx);
#######################################################################################################
########################################### COMPILE ###################################################
#######################################################################################################
$out = $amount;
$invs_arr = array();
// Connect to database
db_connect();
#####################[ GET OUTSTANDING INVOICES ]######################
$sql = "\n\t\tSELECT invnum, invid, balance, terms, odate \n\t\tFROM invoices \n\t\tWHERE cusnum = '{$id}' AND printed = 'y' AND balance>0 AND div = '" . USER_DIV . "' ORDER BY odate ASC";
$prnInvRslt = db_exec($sql);
while (($inv = pg_fetch_array($prnInvRslt)) && $out > 0) {
$invs_arr[] = array("s", $inv['odate'], "{$inv['invid']}", "{$inv['balance']}");
}
#####################[ GET OUTSTANDING NON STOCK INVOICES ]######################
$sql = "\n\t\tSELECT invnum, invid, balance, odate \n\t\tFROM nons_invoices \n\t\tWHERE cusid='{$id}' AND done='y' AND balance>0 AND div='" . USER_DIV . "' ORDER BY odate ASC";
$prnInvRslt = db_exec($sql);
while (($inv = pg_fetch_array($prnInvRslt)) && $out > 0) {
$invs_arr[] = array("n", $inv['odate'], "{$inv['invid']}", "{$inv['balance']}");
}
$out = sprint($out);
#####################[ GET OUTSTANDING POS INVOICES ]######################
$sqls = array();
for ($i = 1; $i <= 12; ++$i) {
$sqls[] = "\n\t\t\tSELECT invnum, invid, balance, odate \n\t\t\tFROM \"{$i}\".pinvoices \n\t\t\tWHERE cusnum='{$id}' AND done='y' AND balance > 0 AND div='" . USER_DIV . "'";
}
$sql = implode(" UNION ", $sqls);
$prnInvRslt = db_exec($sql);
while ($inv = pg_fetch_array($prnInvRslt)) {
$invs_arr[] = array("p", $inv['odate'], "{$inv['invid']}", "{$inv['balance']}");
}
#compile results into an array we can sort by date
$search_arr = array();
foreach ($invs_arr as $key => $array) {
$search_arr[$key] = $array[1];
}
#sort array by date
asort($search_arr);
#add sorted invoices to payment listing
foreach ($search_arr as $key => $date) {
$arr = $invs_arr[$key];
if ($arr[0] == "s") {
db_connect();
$get_sql = "\n\t\t\t\tSELECT invnum, invid, balance, terms, odate \n\t\t\t\tFROM invoices \n\t\t\t\tWHERE cusnum = '{$id}' AND printed = 'y' AND balance>0 AND div = '" . USER_DIV . "' AND invid = '{$arr['2']}' LIMIT 1";
$run_sql = db_exec($get_sql) or errDie("Unable to get stock invoice information.");
if (pg_numrows($run_sql) > 0) {
$inv = pg_fetch_array($run_sql);
$invid = $inv['invid'];
$val = allocamt($out, $inv["balance"]);
if ($val == 0.0) {
continue;
}
$inv['invnum'] += 0;
// reduce invoice balance
$sql = "\n\t\t\t\t\tUPDATE cubit.invoices \n\t\t\t\t\tSET balance = (balance - {$val}::numeric(13,2)) \n\t\t\t\t\tWHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "\n\t\t\t\t\tUPDATE cubit.open_stmnt \n\t\t\t\t\tSET balance = (balance - {$val}::numeric(13,2)) \n\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\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$id}','{$inv['invnum']}', '" . ($val - $val * 2) . "', '{$accdate}', \n\t\t\t\t\t\t'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $link['accnum'], $accdate, $inv['invnum'], "Payment for Invoice No. {$inv['invnum']}", $val, "c");
$rinvids .= "|{$invids[$key]}";
$amounts .= "|{$paidamt[$key]}";
if ($inv['prd'] == "0") {
$inv['prd'] = PRD_DB;
}
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
}
} elseif ($arr[0] == "n") {
db_connect();
$get_sql = "\n\t\t\t\tSELECT invnum, invid, balance, odate \n\t\t\t\tFROM nons_invoices \n\t\t\t\tWHERE cusid='{$id}' AND done='y' AND balance>0 AND div='" . USER_DIV . "' AND invid = '{$arr['2']}' LIMIT 1";
$run_sql = db_exec($get_sql) or errDie("Unable to get non stock information.");
if (pg_numrows($run_sql) > 0) {
$inv = pg_fetch_array($run_sql);
$invid = $inv['invid'];
$val = allocamt($out, $inv["balance"]);
if ($val == 0.0) {
continue;
}
$inv['invnum'] += 0;
# reduce the money that has been paid
$sql = "\n\t\t\t\t\tUPDATE cubit.nons_invoices \n\t\t\t\t\tSET balance = (balance - {$val}::numeric(13,2)) \n\t\t\t\t\tWHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "\n\t\t\t\t\tUPDATE cubit.open_stmnt \n\t\t\t\t\tSET balance = (balance - {$val}::numeric(13,2)) \n\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\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\ttype, \n\t\t\t\t\t\tdiv, allocation_date\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$id}', '{$inv['invnum']}', '" . ($val - $val * 2) . "', '{$accdate}', \n\t\t\t\t\t\t'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', \n\t\t\t\t\t\t'" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $link['accnum'], $accdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}", $val, "c");
$rinvids .= "|{$invids[$key]}";
$amounts .= "|{$paidamt[$key]}";
$invprds .= "|0";
$rages .= "|{$inv['age']}";
$invidsers .= " - {$inv['invnum']}";
}
} else {
db_connect();
$sqls = array();
for ($i = 1; $i <= 12; ++$i) {
$sqls[] = "\n\t\t\t\t\tSELECT invnum, invid, balance, odate, '{$i}' AS prd \n\t\t\t\t\tFROM \"{$i}\".pinvoices \n\t\t\t\t\tWHERE cusnum='{$id}' AND done='y' AND balance > 0 AND div='" . USER_DIV . "' AND invid = '{$arr['2']}'";
}
$get_sql = implode(" UNION ", $sqls);
$run_sql = db_exec($get_sql) or errDie("Unable to get pos invoice information.");
if (pg_numrows($run_sql) > 0) {
$inv = pg_fetch_array($run_sql);
$invid = $inv['invid'];
$val = allocamt($out, $inv["balance"]);
if ($val == 0.0) {
continue;
}
// reduce the invoice balance
$sql = "\n\t\t\t\t\tUPDATE \"{$inv['prd']}\".pinvoices \n\t\t\t\t\tSET balance = (balance - {$val}::numeric(13,2)) \n\t\t\t\t\tWHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$sql = "\n\t\t\t\t\tUPDATE cubit.open_stmnt \n\t\t\t\t\tSET balance = (balance - {$val}::numeric(13,2)) \n\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\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', '" . ($val - $val * 2) . "', '{$accdate}', \n\t\t\t\t\t\t'Payment for Non Stock Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $link['accnum'], $accdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']}", $val, "c");
$rinvids .= "|{$invids[$key]}";
$amounts .= "|{$paidamt[$key]}";
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
}
}
}
#if there is any amount unallocated, it goes to general transaction
$confirm .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='6'><b>A general transaction will credit the client's account with " . CUR . " {$out} </b></td>\n\t\t</tr>";
vsprint($out);
$confirm .= "<input type='hidden' name='out' value='{$out}'>";
###############################################################################################################################
###############################################################################################################################
###############################################################################################################################
#######################################################################################################
########################################### PROCESS ###################################################
#######################################################################################################
# update the customer (make balance less)
$sql = "\n\t\tUPDATE cubit.customers \n\t\tSET balance = (balance - '{$amount}'::numeric(13,2)) \n\t\tWHERE cusnum = '{$id}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
$cols = grp(m("bankid", $bankid), m("trantype", "deposit"), m("date", $accdate), m("name", "{$cus['cusname']} {$cus['surname']}"), m("descript", "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"), m("cheqnum", $cheque), m("amount", $amount), 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();
writetrans($link['accnum'], $dept['debtacc'], $accdate, $refnum, $amount, "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='{$id}'"), 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<tr class='" . bg_class() . "'>\n\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<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t<td>{$od['date']}</td>\n\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</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<tr class='" . bg_class() . "'>\n\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<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t<td>{$od['date']}</td>\n\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</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 = "\n\t\t\t\t\tINSERT INTO cubit.open_stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, balance, date, type, st, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cus['cusnum']}', '0', '-{$out}', '-{$out}', '{$accdate}', 'Payment Received', 'n', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
}
$out = $bout;
}
if ($out > 0) {
recordCT($out, $cus['cusnum'], $accdate, 0);
$cols = grp(m("cusnum", $cus["cusnum"]), m("invid", 0), m("amount", -$out), m("date", $accdate), 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'], $link['accnum'], $accdate, "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();
}
}
function write()
{
if (PRD_STATE == 'py') {
return "<center><li class='err'>Please update your transaction year before closing financial year: Click <a href='../set-period-use.php'>here</a> to do so.</li></center>";
}
// Check if last database has not been reached
if (YR_DB == "yr10") {
$OUTPUT = "<center>Warning : The System has reached its Last year of operation, Please contact the support team to reset.";
require "template.php";
}
$i = substr(YR_DB, 2) + 1;
$nextyr = "yr" . $i;
$sdate = DATE_STD;
pglib_transaction("BEGIN");
global $PRDMON;
$stmnt = financialStatements::trialbal(array("heading_3" => "Trial Balance: Year End", "month_to" => $PRDMON[12]), true);
save_statement($stmnt, "trial_bal");
$stmnt = financialStatements::incomestmnt(array("customized" => true, "this_year_year_to_date" => true, "heading_3" => "Income Statement: Year End", "budget" => true, "this_year_budget" => true, "month_to" => $PRDMON[12]), true);
save_statement($stmnt, "income_stmnt");
$stmnt = financialStatements::balsheet(array("customized" => true, "this_year_year_to_date" => true, "heading_3" => "Balance Sheet: Year End", "month_to" => $PRDMON[12]), true);
save_statement($stmnt, "bal_sheet");
# Copy balance sheet table
core_connect();
$sql = "SELECT * FROM bal_sheet";
$balSheet = db_exec($sql) or errDie("Could not copy Balances to year DB", SELF);
while ($bal = pg_fetch_array($balSheet)) {
db_conn(YR_DB);
$sql = "INSERT INTO bal_sheet (type, ref, value, div) VALUES ('{$bal['type']}', '{$bal['ref']}', '{$bal['value']}', '{$bal['div']}')";
$inRslt = db_exec($sql) or print $sql;
}
core_connect();
$sql = "SELECT * FROM core.trial_bal WHERE period='12'";
$trialBal = db_exec($sql) or errDie("Could not copy Balances to year DB", SELF);
while ($bal = pg_fetch_array($trialBal)) {
$sql = "\n\t\t\tINSERT INTO " . YR_DB . ".year_balance (\n\t\t\t\taccid, topacc, accnum, accname, debit, credit, div\n\t\t\t) VALUES (\n\t\t\t\t'{$bal['accid']}', '{$bal['topacc']}', '{$bal['accnum']}', '{$bal['accname']}', '{$bal['debit']}', '{$bal['credit']}', '{$bal['div']}'\n\t\t\t)";
$inRslt = db_exec($sql) or errDie("Failed to store year balance.");
}
//$sql = "CREATE TABLE \"".YR_DB."\".stkledger AS SELECT * FROM core.stkledger";
//$rslt = db_exec_safe($sql);
// make a copy from the trial balance
$sql = "DROP TABLE \"" . YR_DB . "\".trial_bal";
//$rslt = db_exec_safe($sql);
$sql = "CREATE TABLE \"" . YR_DB . "\".trial_bal AS SELECT * FROM core.trial_bal";
$rslt = db_exec_safe($sql);
// recreate the trial balance actual view
$sql = "\n\t\tCREATE OR REPLACE VIEW \"" . YR_DB . "\".trial_bal_actual AS\n\t\tSELECT tb.accid, tb.topacc, tb.accnum, tb.accname, tb.vat, tb.div, tb.acctype, \n\t\t\tCASE\n\t\t\t\tWHEN tb.period = 1 THEN tb.debit\n\t\t\t\tELSE tb.debit - atb.debit\n\t\t\tEND AS debit,\n\t\t\tCASE\n\t\t\t\tWHEN tb.period = 1 THEN tb.credit\n\t\t\t\tELSE tb.credit - atb.credit\n\t\t\tEND AS credit, tb.month, tb.period\n\t\tFROM \"" . YR_DB . "\".trial_bal tb LEFT JOIN \"" . YR_DB . "\".trial_bal atb\n\t\t\tON tb.period = (atb.period::int + 1) AND tb.accid = atb.accid;";
db_exec_safe($sql);
/* fetch customer balances */
$custs = qryCustomer(false, "cusnum");
$custdebit = array();
$custcredit = array();
while ($cd = $custs->fetch_array()) {
$qry = new dbSelect("custledger", $PRDMON['12'], grp(m("cols", "cbalance, dbalance"), m("where", "cusnum='{$cd['cusnum']}'"), m("order", "id DESC"), m("limit", 1)));
$qry->run();
if ($qry->num_rows() <= 0) {
$custcredit[$cd["cusnum"]] = 0;
$custdebit[$cd["cusnum"]] = 0;
} else {
$qry->fetch_array();
$custcredit[$cd["cusnum"]] = $qry->d["cbalance"];
$custdebit[$cd["cusnum"]] = $qry->d["dbalance"];
}
}
/* fetch supplier balances */
$supps = qrySupplier(false, "supid");
$suppdebit = array();
$suppcredit = array();
while ($sd = $supps->fetch_array()) {
$qry = new dbSelect("suppledger", $PRDMON['12'], grp(m("cols", "cbalance, dbalance"), m("where", "supid='{$sd['supid']}'"), m("order", "id DESC"), m("limit", 1)));
$qry->run();
if ($qry->num_rows() <= 0) {
$suppcredit[$sd["supid"]] = 0;
$suppdebit[$sd["supid"]] = 0;
} else {
$qry->fetch_array();
$suppcredit[$sd["supid"]] = $qry->d["cbalance"];
$suppdebit[$sd["supid"]] = $qry->d["dbalance"];
}
}
/* copy the inventory ledger */
for ($i = 1; $i <= 12; ++$i) {
$mname = strtolower(getMonthName($i));
$sql = "CREATE TABLE audit.{$mname}_stkledger \n\t\t\t\tAS \n\t\t\t\tSELECT * FROM \"{$i}\".stkledger";
db_exec($sql) or errDie("Error copying inventory ledger (P{$i}).");
}
// Empty All Period Databases
for ($i = 1; $i <= 14; $i++) {
db_conn($i);
$sql = "TRUNCATE TABLE transect;";
$sql .= "TRUNCATE TABLE ledger;";
$sql .= "TRUNCATE TABLE custledger;";
$sql .= "TRUNCATE TABLE suppledger;";
$sql .= "TRUNCATE TABLE empledger;";
db_exec($sql) or errDie("Unable to empty Period databases", SELF);
}
$qryi = new dbUpdate();
recreateAudit();
/* FP AUDIT FIX
for ($p = 1; $p <= 12; ++$p) {
$monnum = $PRDMON[$i];
$monname = strtolower(getMonthName($i));
}
*/
/* create the customer ledger balance entries */
$custs = qryCustomer(false, "cusnum");
while ($cd = $custs->fetch_array()) {
for ($i = 1; $i <= 12; ++$i) {
$cols = grp(m("cusnum", $cd["cusnum"]), m("contra", 0), m("edate", $sdate), m("sdate", raw("CURRENT_DATE")), m("eref", 0), m("descript", "Balance"), m("credit", 0), m("debit", 0), m("cbalance", $custcredit[$cd["cusnum"]]), m("dbalance", $custdebit[$cd["cusnum"]]), m("div", USER_DIV));
$qryi->setTable("custledger", "{$i}");
$qryi->setOpt($cols);
$qryi->run(DB_INSERT);
/* audit customer ledger */
$cols = grp(m("cusnum", $cd["cusnum"]), m("contra", 0), m("edate", $sdate), m("sdate", raw("CURRENT_DATE")), m("eref", 0), m("descript", "Balance"), m("credit", 0), m("debit", 0), m("cbalance", $custcredit[$cd["cusnum"]]), m("dbalance", $custdebit[$cd["cusnum"]]), m("div", USER_DIV), m("actyear", YR_NAME));
$qryi->setTable(getMonthName($i) . "_custledger", "audit");
$qryi->setOpt($cols);
$qryi->run(DB_INSERT);
}
}
/* create the supplier ledger balance entries */
$supps = qrySupplier(false, "supid");
while ($sd = $supps->fetch_array()) {
for ($i = 1; $i <= 12; ++$i) {
$qryi->setTable("suppledger", "{$i}");
$cols = grp(m("supid", $sd["supid"]), m("contra", "0"), m("edate", $sdate), m("sdate", raw("CURRENT_DATE")), m("eref", "0"), m("descript", "Balance"), m("credit", "0"), m("debit", "0"), m("div", USER_DIV), m("cbalance", $suppcredit[$sd["supid"]]), m("dbalance", $suppdebit[$sd["supid"]]));
$qryi->setCols($cols);
$qryi->run(DB_INSERT);
}
}
/* create the stock ledger entries */
$stock = qryStock(false, "stkid, stkcod, stkdes, units, csamt");
while ($stk = $stock->fetch_array()) {
for ($i = 1; $i <= 12; ++$i) {
$qryi->setTable("stkledger", "{$i}");
$cols = grp(m("stkid", $stk["stkid"]), m("stkcod", $stk["stkcod"]), m("stkdes", $stk["stkdes"]), m("trantype", "bal"), m("edate", $sdate), m("qty", $stk["units"]), m("csamt", $stk["csamt"]), m("balance", $stk["csamt"]), m("bqty", $stk["units"]), m("details", "Balance"), m("div", USER_DIV), m("yrdb", $nextyr));
$qryi->setCols($cols);
$qryi->run(DB_INSERT);
}
}
/* do the retained income entries for all branches */
$sql = "SELECT div FROM cubit.branches";
$branRs = db_exec($sql) or errDie("Could not access branches table.");
while ($bran = pg_fetch_array($branRs)) {
fintran($bran['div']);
}
/* create the ledger account balance entries */
for ($i = 1; $i <= 12; ++$i) {
$periodname = getMonthName($i);
$sql = "\n\t\t\tINSERT INTO " . YR_DB . ".{$periodname} (\n\t\t\t\taccid, topacc, accnum, accname, debit, credit, div\n\t\t\t) SELECT accid, topacc, accnum, accname, debit, credit, div\n\t\t\t\tFROM core.trial_bal WHERE month='1'";
db_exec($sql) or errDie("Error creating ledger balances (1)");
$sql = "\n\t\t\tINSERT INTO \"{$i}\".openbal (\n\t\t\t\taccid, accname, debit, credit, div\n\t\t\t) SELECT accid, accname, debit, credit, div\n\t\t\t\tFROM core.trial_bal WHERE month='1'";
db_exec($sql) or errDie("Error creating ledger balances (2)");
$sql = "\n\t\t\tINSERT INTO \"{$i}\".ledger (\n\t\t\t\tacc, contra, edate, eref, descript, credit, debit, div, caccname, ctopacc, caccnum, cbalance, dbalance\n\t\t\t) SELECT accid, accid, CURRENT_DATE, '0', 'Balance', '0', '0', div, accname, topacc, accnum, credit, debit\n\t\t\t\tFROM core.trial_bal WHERE month='1'";
db_exec($sql) or errDie("Error creating ledger balances (3)");
}
/* close and select new year */
selectNextYear($nextyr);
/* mark year as closed */
$sql = "UPDATE core.year SET closed = 'y' WHERE yrdb = '" . YR_DB . "'";
$rslt = db_exec($sql) or errDie("Could not Set Next Year Database and Name", SELF);
pglib_transaction("COMMIT");
// Display
$write = "<center><h3> Current Year has been closed </h3>\n\t<b>( i ) The next Year has been activated ( i )</b></center>" . mkQuickLinks();
return $write;
}
function write($_POST)
{
$_POST = var_makesafe($_POST);
extract($_POST);
$week += 0;
if (!isset($button)) {
return enter($_POST);
}
if (!isset($date_day)) {
exit;
}
require_lib("validate");
$v = new validate();
//$v->isOk ($empnum, "num", 1, 20, "Invalid employee number.");
if (!checkdate($date_month, $date_day, $date_year)) {
$v->isOk($date_day, "num", 10, 1, "Invalid date.");
}
# display errors, if any
if ($v->isError()) {
$write = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$write .= "<li class='err'>" . $e["msg"] . "</li>";
}
$write .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $write;
}
$date = $date_year . "-" . $date_month . "-" . $date_day;
$salconacc = gethook("accnum", "salacc", "name", "salaries control");
$intrec = gethook("accnum", "salacc", "name", "interestreceived");
$commacc = gethook("accnum", "salacc", "name", "Commission");
$abonusacc = gethook("accnum", "salacc", "name", "Bonus");
$payeacc = gethook("accnum", "salacc", "name", "PAYE");
$uifacc = gethook("accnum", "salacc", "name", "UIF");
$uifbal = gethook("accnum", "salacc", "name", "uifbal");
$sdlbal = gethook("accnum", "salacc", "name", "sdlbal");
$pa = gethook("accnum", "salacc", "name", "pension");
$ma = gethook("accnum", "salacc", "name", "medical");
$cash_account = gethook("accnum", "salacc", "name", "cash");
$retire = gethook("accnum", "salacc", "name", "retire");
$provident = gethook("accnum", "salacc", "name", "provident");
$refnum = getrefnum($date);
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
db_conn('cubit');
$i = 0;
$Sl = "SELECT * FROM salset";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) > 0) {
$con = true;
} else {
$con = false;
}
if ($con) {
$uifexp = $salacc;
$sdlexp = $salacc;
$pax = $salacc;
$max = $salacc;
$retiree = $salacc;
}
$Sl = "SELECT * FROM employees WHERE div='" . USER_DIV . "' ORDER BY sname,fnames";
$Rr = db_exec($Sl) or errDie("Unable to get data.");
$out = "<script>";
$batch_setting = getCSetting("PRINT_PSLIPS_BATCH");
if (isset($batch_setting) and $batch_setting == "yes") {
if ($printslip != "n") {
$out .= "nhprinter('salwages/payslip-print.php?batch=";
}
}
while ($data = pg_fetch_array($Rr)) {
if (!isset($emps[$data['empnum']]) || isset($emps_already[$data["empnum"]])) {
continue;
}
$providente = $data["expacc_provident"];
$retiree = $data["expacc_ret"];
$pax = $data["expacc_pension"];
$uifexp = $data["expacc_uif"];
$max = $data["expacc_medical"];
$dedgenerale = $data["expacc_other"];
$sdlexp = $data["expacc_sdl"];
$salacc = $data["expacc_salwages"];
$loanexp = $data["expacc_loan"];
$id = $data['empnum'];
$basic_sal_save[$id] = $basic_sal[$id];
# Multiply basic_sal add overtime
if (isset($multi[$id])) {
$basic_sal[$id] = sprint($basic_sal[$id] * $multi[$id]);
}
$basic_sal[$id] = sprint($basic_sal[$id]);
$bonus[$id] = sprint($bonus[$id]);
$annual[$id] = sprint($annual[$id]);
$abonus[$id] = sprint($abonus[$id]);
$commission[$id] = sprint($commission[$id]);
$all_travel[$id] = sprint($all_travel[$id]);
$loaninstall[$id] = sprint($loaninstall[$id]);
$comp_pension[$id] = sprint($comp_pension[$id]);
$emp_pension[$id] = sprint($emp_pension[$id]);
$comp_provident[$id] = sprint($comp_provident[$id]);
$emp_provident[$id] = sprint($emp_provident[$id]);
$comp_uif[$id] = sprint($comp_uif[$id]);
$emp_uif[$id] = sprint($emp_uif[$id]);
$comp_ret[$id] = sprint($comp_ret[$id]);
$emp_ret[$id] = sprint($emp_ret[$id]);
$comp_medical[$id] = sprint($comp_medical[$id]);
$emp_medical[$id] = sprint($emp_medical[$id]);
$comp_other[$id] = sprint($comp_other[$id]);
$emp_other[$id] = sprint($emp_other[$id]);
$novert[$id] += 0;
$hovert[$id] += 0;
$overamt[$id] = sprint($overamt[$id]);
$sdl[$id] = sprint($comp_sdl[$id]);
$ecost = $basic_sal[$id] + $overamt[$id] + $all_travel[$id] + $bonus[$id] + $annual[$id] + $sdl[$id] + $all_beforeamount[$id] + $all_afteramount[$id] + $comp_pension[$id] + $comp_uif[$id] + $comp_ret[$id] + $comp_medical[$id] + $comp_provident[$id] + $comp_other[$id] + $all_beforeamount[$id] + $all_afteramount[$id];
$ecost = sprint($ecost);
// writetrans($uifexp,$uifbal , $date, $refnum, $comp_uif[$id], "Company UIF Contribution, $data[fnames] $data[sname].");
writetrans($sdlexp, $sdlbal, $date, $refnum, $sdl[$id], "SDL, {$data['fnames']} {$data['sname']}.");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance+({$grossal_nodedall[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $salacc, $date, $refnum, "Gross Salary", $grossal_nodedall[$id], "c");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salacc, $salconacc, $date, $refnum, $grossal_nodedall[$id], "Gross Salary proccessing for employee, {$data['fnames']} {$data['sname']}.");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if ($emp_uif[$id] > 0) {
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_uif[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $uifacc, $date, $refnum, "UIF", $emp_uif[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $uifbal, $date, $refnum, $emp_uif[$id], "UIF for employee, {$data['fnames']} {$data['sname']}.");
}
if ($comp_uif[$id] > 0) {
writetrans($uifexp, $uifbal, $date, $refnum, $emp_uif[$id], "UIF for employee, {$data['fnames']} {$data['sname']}.");
}
if ($commission[$id] > 0) {
if ($con) {
$commacc = $salacc;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance+({$commission[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $commacc, $date, $refnum, "Commission", $commission[$id], "c");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($commacc, $salconacc, $date, $refnum, $commission[$id], "Commission for employee, {$data['fnames']} {$data['sname']}.");
}
if ($abonus[$id] > 0) {
if ($con) {
$abonusacc = $salacc;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance+({$abonus[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $abonusacc, $date, $refnum, "Bonus", $abonus[$id], "c");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($abonusacc, $salconacc, $date, $refnum, $abonus[$id], "Bonus for employee, {$data['fnames']} {$data['sname']}.");
}
if ($paye[$id] > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$paye[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $payeacc, $date, $refnum, "PAYE", $paye[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $payeacc, $date, $refnum, $paye[$id], "PAYE for employee, {$data['fnames']} {$data['sname']}.");
}
if ($comp_pension[$id] > 0) {
writetrans($pax, $pa, $date, $refnum, $comp_pension[$id], "Company Pension Contribution, {$data['fnames']} {$data['sname']}.");
}
if ($comp_medical[$id] > 0) {
writetrans($max, $ma, $date, $refnum, $comp_medical[$id], "Company Medical Contribution, {$data['fnames']} {$data['sname']}.");
}
if ($emp_pension[$id] > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_pension[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $pa, $date, $refnum, "Pension Contribution", $emp_pension[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $pa, $date, $refnum, $emp_pension[$id], "Employee Pension Contribution, {$data['fnames']} {$data['sname']}.");
}
if ($emp_medical[$id] > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_medical[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $ma, $date, $refnum, "Medical Contribution", $emp_medical[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $ma, $date, $refnum, $emp_medical[$id], "Employee Medical Contribution, {$data['fnames']} {$data['sname']}.");
}
if ($comp_provident[$id] > 0) {
writetrans($providente, $provident, $date, $refnum, $comp_provident[$id], "Company Provident Fund Contribution, {$data['fnames']} {$data['sname']}.");
}
if ($emp_provident[$id] > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_provident[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $provident, $date, $refnum, "Provident Fund Contribution", $emp_provident[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $provident, $date, $refnum, $emp_provident[$id], "Provident Fund Contribution, {$data['fnames']} {$data['sname']}.");
}
if (false && $comp_other[$id] > 0) {
writetrans($dedgenerale, $dedgeneral, $date, $refnum, $comp_other[$id], "Company Contribution to Other Deductions, {$data['fnames']} {$data['sname']}.");
}
if (false && $emp_other[$id] > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_other[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $dedgeneral, $date, $refnum, "Other Deductions Contribution", $emp_other[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $dedgeneral, $date, $refnum, $emp_other[$id], "Other Deductions Contribution, {$data['fnames']} {$data['sname']}.");
}
if ($emp_ret[$id] > 0) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$emp_ret[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $retire, $date, $refnum, "Retirement Annuity Contribution", $emp_ret[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $retire, $date, $refnum, $emp_ret[$id], "Employee Retirement Annuity Contribution, {$data['fnames']} {$data['sname']}.");
}
if ($comp_ret[$id] > 0) {
writetrans($retiree, $retire, $date, $refnum, $comp_ret[$id], "Company Retirement Annuity Contribution, {$data['fnames']} {$data['sname']}.");
}
$paidamount[$id] += 0;
db_conn('cubit');
$mons = "{$month};";
$due = sprint($nettpay[$id] - $paidamount[$id]);
//, balance=balance+'$due'
$sql = "\n\t\t\tUPDATE employees \n\t\t\tSET lastpay = '{$mons}', loanamt = (loanamt - cast(float '{$loaninstall[$id]}' as numeric)), \n\t\t\t\tloanfringe = (loanfringe - cast(float '{$fringe_loan[$id]}' as numeric)) \n\t\t\tWHERE empnum = '{$data['empnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to get employee details.");
// check if loan is 0, then unmark loan as active, and store in archive
$sql = "SELECT loanid FROM employees WHERE loanamt=0 AND empnum='{$data['empnum']}' AND gotloan='t'::bool";
$rslt = db_exec($sql) or errDie("Error reading employee details for loan.");
if (pg_num_rows($rslt) > 0) {
$loanid = pg_fetch_result($rslt, 0, 0);
$sql = "UPDATE employees SET gotloan='f'::bool, loaninstall='0' WHERE empnum='{$data['empnum']}'";
$rslt = db_exec($sql) or errDie("Unable to update employee loan status.");
$sql = "UPDATE emp_loanarchive SET donedata=CURRENT_DATE WHERE id='{$loanid}'";
$rslt = db_exec($sql) or errDie("Unable to archive loan.");
$sql = "SELECT loanint_unpaid FROM employees WHERE empnum='{$data['empnum']}'";
$rslt = db_exec($sql) or errDie("Error reading loan interest for installment.");
$loanint[$id] = sprint(pg_fetch_result($rslt, 0, 0));
} else {
if ($loaninstall[$id] > 0) {
$sql = "SELECT loanamt_tot, loanint_amt FROM employees WHERE empnum='{$data['empnum']}'";
$rslt = db_exec($sql) or errDie("Error reading loan interest for installment.");
$loan_tot = pg_fetch_result($rslt, 0, 0);
$loan_totint = pg_fetch_result($rslt, 0, 1);
$loanint[$id] = sprint($loaninstall[$id] / $loan_tot * $loan_totint);
} else {
$loanint[$id] = 0;
}
}
$sql = "\n\t\t\tUPDATE employees \n\t\t\tSET loanint_unpaid = (loanint_unpaid - cast(float '{$loanint[$id]}' as numeric)) \n\t\t\tWHERE empnum = '{$data['empnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update employee interest.");
if ($loaninstall[$id] > 0 && !empty($loanexp)) {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$loaninstall[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $loanexp, $date, $refnum, "Loan Instalment", $loaninstall[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $loanexp, $date, $refnum, $loaninstall[$id] - $loanint[$id], "Loan Installment for employee, {$data['fnames']} {$data['sname']}.");
writetrans($salconacc, $intrec, $date, $refnum, $loanint[$id], "Loan Interest for employee, {$data['fnames']} {$data['sname']}.");
/* record this month's loan amounts (for reversal purposes) */
// determine the salary period
switch ($data["payprd"]) {
case "d":
$lprd = date_part($date, DP_DAY);
break;
case "m":
case "f":
case "w":
default:
$lprd = $week;
break;
}
// record it!
db_conn("cubit");
$sql = "\n\t\t\t\tINSERT INTO cubit.emp_loaninstallments (\n\t\t\t\t\tempnum, fdate, fperiod, fmonth, fyear, installment, interest, \n\t\t\t\t\tfringe\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$data['empnum']}', '{$date}', '{$lprd}', '{$month}', '" . EMP_YEAR . "', '{$loaninstall[$id]}', '{$loanint[$id]}', \n\t\t\t\t\t'{$fringe_loan[$id]}'\n\t\t\t\t)";
$rslt = db_exec($sql) or errDie("Error record loan fringe benefit.");
}
if (!isset($accid[$id])) {
$accid[$id] = 0;
}
$totded[$id] = sprint($de_beforeamount[$id] + $de_afteramount[$id] + $emp_pension[$id] + $emp_medical[$id] + $emp_provident[$id] + $emp_ret[$id] + $emp_other[$id]);
$totded_employer[$id] = sprint($de_beforeamount_emp[$id] + $de_afteramount_emp[$id] + $comp_pension[$id] + $comp_medical[$id] + $comp_provident[$id] + $comp_ret[$id] + $comp_other[$id]);
$totall[$id] = sprint($totall[$id]);
$np = $nettpay[$id];
if (isset($rbsa[$id])) {
$np = sprint($np - array_sum($rbsa[$id]));
}
if ($data["payprd"] == "d") {
$week = $pday;
}
if (empty($novert[$id])) {
$novert[$id] = "0";
}
if (empty($hovert[$id])) {
$hovert[$id] = "0";
}
db_conn("cubit");
$Sl = "\n\t\t\tINSERT INTO cubit.salpaid (\n\t\t\t\tempnum, month, bankid, salary, comm, uifperc, uif, payeperc, \n\t\t\t\tpaye, totded, totded_employer, totallow, loanins, \n\t\t\t\ttot_fringe, div, display, saldate, week, cyear, novert, \n\t\t\t\thovert, taxed_sal, hours, salrate, bonus\n\t\t\t) VALUES (\n\t\t\t\t'{$data['empnum']}', '{$month}', '{$accid[$id]}', '{$np}', '{$commission[$id]}', '0', '{$emp_uif[$id]}', '0', \n\t\t\t\t'{$paye[$id]}', '{$totded[$id]}', '{$totded_employer[$id]}', '{$totall[$id]}', '{$loaninstall[$id]}', \n\t\t\t\t'{$fringe_tot[$id]}', '" . USER_DIV . "', '','{$date}','{$week}', '" . EMP_YEAR . "', '{$novert[$id]}', \n\t\t\t\t'{$hovert[$id]}', '{$paye_salary[$id]}', '{$multi[$id]}','{$basic_sal_save[$id]}', '{$abonus[$id]}'\n\t\t\t)";
$Ry = db_exec($Sl) or errDie("Unable to insert record.");
$pid = pglib_lastid("salpaid", "id");
// fringe benefits
if (isset($fringeid[$id])) {
foreach ($fringeid[$id] as $i => $fid) {
// empledger($data["empnum"], $fringeaccs[$id][$i], $date, $refnum,"Fringe Benefit, ".$fringename[$id][$i], $fringebens[$id][$i], "d");
// writetrans($salconacc, $fringeaccs[$id][$i], $date, $refnum, $fringebens[$id][$i], "Fringe Benefit for employee, $data[fnames] $data[sname].");
}
}
/* db_conn('cubit');
// allowances
$i = 0;
$sql = "SELECT * FROM allowances WHERE div = '".USER_DIV."' ORDER BY allowance";
$allowRslt = db_exec ($sql) or errDie ("Unable to select allowances from database.");
if (pg_numrows ($allowRslt) > 0) {
while ($myAllow = pg_fetch_array ($allowRslt)) {
db_conn('cubit');
# check if employee has allowance
$sql = "SELECT * FROM empallow WHERE allowid='$myAllow[id]' AND empnum='$data[empnum]' AND div = '".USER_DIV."'";
$empAllowRslt = db_exec ($sql) or errDie ("Unable to select allowance info from database.");
if (pg_numrows ($empAllowRslt) > 0) {
$dataAllow = pg_fetch_array ($empAllowRslt);
if ( ($allowances[$id][$i]=sprint($allowances[$id][$i])) <= 0 ) continue;
$Sl="INSERT INTO emp_inc(emp,year,period,date,payslip,type,code,description,qty,rate,amount,ex)
VALUES ('$data[empnum]','$year','$month','$date','$pid','$myAllow[id]','','$myAllow[allowance]','1','0','".$allowances[$id][$i]."','')";
$Ri=db_exec($Sl) or errDie("unable to insert data.1");
if($con) {
$myAllow['accid']=$salacc;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance+(".$allowances[$id][$i].") WHERE empnum = '$data[empnum]'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $allowaccs[$id][$i], $date, $refnum,"Allowance" , $allowances[$id][$i] , "c");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($allowaccs[$id][$i], $salconacc, $date, $refnum, $allowances[$id][$i], "Allowances for employee, $data[fnames] $data[sname].");
}
$i++;
}
}*/
$frinupd = new dbUpdate("emp_frin", "cubit");
if (isset($fringebens[$id])) {
foreach ($fringebens[$id] as $key => $value) {
$cols = grp(m("emp", $data["empnum"]), m("year", EMP_YEAR), m("period", $month), m("week", $week), m("fdate", $date), m("payslip", $pid), m("code", $key), m("description", sprint($fringename[$id][$key])), m("qty", 1), m("amount", $fringebens[$id][$key]));
$frinupd->setCols($cols);
$frinupd->run(DB_INSERT);
}
}
if ($fringe_loan[$id] > 0) {
$cols = grp(m("emp", $data["empnum"]), m("year", EMP_YEAR), m("period", $month), m("fdate", $date), m("payslip", $pid), m("code", "FRINLOAN"), m("description", "Loan Fringe Benefit"), m("qty", 1), m("amount", $fringe_loan[$id]));
$frinupd->setCols($cols);
$frinupd->run(DB_INSERT);
}
if ($fringe_medical[$id] > 0) {
$cols = grp(m("emp", $data["empnum"]), m("year", EMP_YEAR), m("period", $month), m("fdate", $date), m("payslip", $pid), m("code", "FRINMED"), m("description", "Medical Fringe Benefit"), m("qty", 1), m("amount", $fringe_medical[$id]));
$frinupd->setCols($cols);
$frinupd->run(DB_INSERT);
}
if ($fringe_car1[$id] > 0) {
$cols = grp(m("emp", $data["empnum"]), m("year", EMP_YEAR), m("period", $month), m("fdate", $date), m("payslip", $pid), m("code", "FRINCAR1"), m("description", "Fringe Benefit: Vehicle 1"), m("qty", 1), m("amount", $fringe_car1[$id]));
$frinupd->setCols($cols);
$frinupd->run(DB_INSERT);
}
if ($fringe_car2[$id] > 0) {
$cols = grp(m("emp", $data["empnum"]), m("year", EMP_YEAR), m("period", $month), m("fdate", $date), m("payslip", $pid), m("code", "FRINCAR2"), m("description", "Fringe Benefit: Vehicle 2"), m("qty", 1), m("amount", $fringe_car2[$id]));
$frinupd->setCols($cols);
$frinupd->run(DB_INSERT);
}
// allowances
if (isset($allowid[$id]) && is_array($allowid[$id]) && count($allowid[$id]) > 0) {
foreach ($allowid[$id] as $k => $dummy) {
if (($allowances[$id][$k] = sprint($allowances[$id][$k])) <= 0) {
continue;
}
db_conn('cubit');
$sql = "\n\t\t\t\t\tINSERT INTO emp_inc (\n\t\t\t\t\t\temp, year, period, week, date, payslip, type, code, \n\t\t\t\t\t\tdescription, qty, rate, amount, ex\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$data['empnum']}', '" . EMP_YEAR . "', '{$month}', '{$week}', '{$date}', '{$pid}', '" . $allowid[$id][$k] . "', '', \n\t\t\t\t\t\t'" . $allowname[$id][$k] . "', '1', '0', '" . $allowances[$id][$k] . "', ''\n\t\t\t\t\t)";
$rslt = db_exec($sql) or errDie("unable to insert data.1");
if ($con) {
$allowaccs[$id][$k] = $salacc;
}
$Sl = "UPDATE employees SET balance=balance+(" . $allowances[$id][$k] . ") WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $allowaccs[$id][$k], $date, $refnum, "Allowance", $allowances[$id][$i], "c");
writetrans($allowaccs[$id][$k], $salconacc, $date, $refnum, $allowances[$id][$k], "Allowances for employee, {$data['fnames']} {$data['sname']}.");
}
}
/* db_conn('cubit');
# Deductions
$i = 0;
$sql = "SELECT * FROM salded WHERE div = '".USER_DIV."' ORDER BY deduction";
$deductRslt = db_exec ($sql) or errDie ("Unable to select deductions from database.");
if (pg_numrows ($deductRslt) >0) {
while ($myDeduct = pg_fetch_array ($deductRslt)) {
db_conn('cubit');
# check if employee has deduction
$sql = "SELECT * FROM empdeduct WHERE dedid='$myDeduct[id]' AND empnum='$data[empnum]' AND div = '".USER_DIV."'";
$empDeductRslt = db_exec ($sql) or errDie ("Unable to select Deduction info from database.");
if (pg_numrows ($empDeductRslt) > 0) {
$dataDeduct = pg_fetch_array ($empDeductRslt);
if ( ($deductions[$id][$i]=sprint($deductions[$id][$i])) > 0 ) {
# Debit salaries control acc and credit acc
$Sl="INSERT INTO emp_ded(emp,year,period,date,payslip,type,code,description,qty,rate,amount) VALUES
('$data[empnum]','$year','$month','$date','$pid','$myDeduct[id]','','$myDeduct[deduction]','1','0','".$deductions[$id][$i]."')";
$Ri=db_exec($Sl) or errDie("unable to insert data.2");
}
if ( ($employer_deductions[$id][$i]=sprint($employer_deductions[$id][$i])) > 0 ) {
$Sl="INSERT INTO emp_com(emp,year,period,date,payslip,type,code,description,qty,rate,amount) VALUES
('$data[empnum]','$year','$month','$date','$pid','$myDeduct[id]','','$myDeduct[deduction]','1','0','".$employer_deductions[$id][$i]."')";
//$Ri=db_exec($Sl) or errDie("unable to insert data1.");
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-(".$deductions[$id][$i].") WHERE empnum = '$data[empnum]'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $myDeduct['accid'], $date, $refnum,"Deduction" , $deductions[$id][$i], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $dedaccs[$id][$i], $date, $refnum, $deductions[$id][$i], "Deductions for employee, $data[fnames] $data[sname].");
$i++;
}
}
}*/
# Deductions
if (isset($deductid[$id]) && is_array($deductid[$id]) && count($deductid[$id]) > 0) {
foreach ($deductid[$id] as $k => $dummy) {
if (($deductions[$id][$k] = sprint($deductions[$id][$k])) <= 0) {
continue;
}
db_conn('cubit');
# Debit salaries control acc and credit acc
$sql = "\n\t\t\t\t\tINSERT INTO emp_ded (\n\t\t\t\t\t\temp, year, period, week, date, payslip, type, code, \n\t\t\t\t\t\tdescription, qty, rate, amount\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$data['empnum']}', '" . EMP_YEAR . "', '{$month}', '{$week}', '{$date}', '{$pid}', '" . $deductid[$id][$k] . "', '', \n\t\t\t\t\t\t'" . $deductname[$id][$k] . "', '1', '0', '" . $deductions[$id][$k] . "'\n\t\t\t\t\t)";
$rslt = db_exec($sql) or errDie("unable to insert data.2");
$sql = "UPDATE employees SET balance=balance-(" . $deductions[$id][$k] . ") WHERE empnum = '{$data['empnum']}'";
$rslt = db_exec($sql) or errDie("Unable to get employee details.");
empledger($data['empnum'], $dedaccs[$id][$k], $date, $refnum, "Deduction", $deductions[$id][$k], "d");
writetrans($salconacc, $dedaccs[$id][$k], $date, $refnum, $deductions[$id][$k], "Deductions for employee, {$data['fnames']} {$data['sname']}.");
}
}
db_conn('cubit');
$Sl = "SELECT * FROM rbs ORDER BY name";
$Rl = db_exec($Sl) or errDie("Unable to get data.");
if (pg_num_rows($Rl) > 0) {
while ($td = pg_fetch_array($Rl)) {
$rbsa[$id][$td['id']] = sprint($rbsa[$id][$td['id']]);
db_conn('cubit');
$sql = "\n\t\t\t\t\tINSERT INTO emp_inc (\n\t\t\t\t\t\temp, year, period, week, date, payslip, type, code, description, \n\t\t\t\t\t\tqty, rate, amount, ex\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$data['empnum']}', '" . EMP_YEAR . "', '{$month}', '{$week}', '{$date}', '{$pid}', '{$td['id']}', '', '{$td['name']}', \n\t\t\t\t\t\t'1', '0', '" . $rbsa[$id][$td['id']] . "', 'RBS'\n\t\t\t\t\t)";
db_exec($sql) or errDie("unable to insert data.3");
$sql = "UPDATE employees SET balance=balance+(" . $rbsa[$id][$td['id']] . ") WHERE empnum = '{$data['empnum']}'";
db_exec($sql) or errDie("Unable to get employee details.");
empledger($data['empnum'], $td['account'], $date, $refnum, "Reimbursement", $rbsa[$id][$td['id']], "c");
writetrans($td['account'], $salconacc, $date, $refnum, $rbsa[$id][$td['id']], "Reimbursement for employee, {$data['fnames']} {$data['sname']}.");
}
}
if (isset($subsname)) {
foreach ($subsname[$id] as $sid => $sn) {
if (empty($subsamt[$id][$sid]) || $subsamt[$id][$sid] <= 0) {
continue;
}
$samt = sprint($subsamt[$id][$sid]);
$i++;
db_conn('cubit');
$cols = grp(m("emp", $data["empnum"]), m("year", EMP_YEAR), m("period", $month), m("week", $week), m("date", $date), m("payslip", $pid), m("type", $sid), m("code", ""), m("description", $subsname[$id][$sid]), m("qty", 1), m("rate", 0), m("amount", $samt), m("ex", "SUBS"));
$subin = new dbUpdate("emp_inc", "cubit", $cols);
$subin->run(DB_INSERT);
$cols = grp(m("balance", raw("balance+({$samt})")));
$subin->setTable("employees");
$subin->setOpt($cols, wgrp(m("empnum", $data["empnum"])));
$subin->run(DB_UPDATE);
empledger($data["empnum"], $subsacc[$id][$sid], $date, $refnum, "Subsistence Allowance: " . $subsname[$id][$sid], $samt, "c");
writetrans($subsacc[$id][$sid], $salconacc, $date, $refnum, $samt, "Subsistence Allownace (" . $subsname[$id][$sid] . ") for employee, {$data['fnames']} {$data['sname']}.");
}
}
if ($paidamount[$id] > 0) {
if ($data['paytype'] == "Cash") {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$paidamount[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $cash_account, $date, $refnum, "Payment(Cash)", $paidamount[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $cash_account, $date, $refnum, $paidamount[$id], "Salary Payment(Cash) for employee, {$data['fnames']} {$data['sname']}.");
} elseif ($data['paytype'] == "Ledger Account") {
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$paidamount[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $account[$id], $date, $refnum, "Payment(Ledger Account)", $paidamount[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
writetrans($salconacc, $account[$id], $date, $refnum, $paidamount[$id], "Salary Payment(Ledger Account) for employee, {$data['fnames']} {$data['sname']}.");
} else {
$accid[$id] += 0;
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$accid[$id]}' 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.";
}
$bank = pg_fetch_array($rslt);
$bankacc = $bank["accnum"];
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance-({$paidamount[$id]}) WHERE empnum = '{$data['empnum']}'";
$Rp = db_exec($Sl) or errDie("Unable to get employee details.");
empledger($data['empnum'], $bankacc, $date, $refnum, "Payment(Bank)", $paidamount[$id], "d");
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if ($paidamount[$id] > 0) {
writetrans($salconacc, $bankacc, $date, $refnum, $paidamount[$id], "Salary Payment for employee(Bank), {$data['fnames']} {$data['sname']}.");
banktrans($accid[$id], "withdrawal", $date, "{$data['fnames']} {$data['sname']}", "Salary Payment for employee, {$data['fnames']} {$data['sname']}", 0, $paidamount[$id], $salconacc, $data['empnum']);
}
}
}
db_conn('cubit');
if ($comp_uif[$id] > 0) {
$Sl = "INSERT INTO emp_com(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','UIFC','','UIF','1','0','{$comp_uif[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data1.");
}
if ($emp_uif[$id] > 0) {
$Sl = "INSERT INTO emp_ded(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','UIFE','','UIF','1','0','{$emp_uif[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data3.");
}
if ($sdl[$id] > 0) {
$Sl = "INSERT INTO emp_com(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','SDL','','SDL','1','0','{$sdl[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data2.");
}
if ($paye[$id] > 0) {
$Sl = "INSERT INTO emp_ded(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','PAYE','','PAYE','1','0','{$paye[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data3.");
}
if ($basic_sal[$id] > 0) {
$Sl = "INSERT INTO emp_inc(emp,year,period,week,date,payslip,type,code,description,pension,qty,rate,amount,ex) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','INC','','Basic Salary','','1','0','{$basic_sal[$id]}','')";
$Ri = db_exec($Sl) or errDie("unable to insert data4.");
}
if ($data["loanpayslip"] > 0) {
$Sl = "INSERT INTO emp_inc(emp,year,period,week,date,payslip,type,code,description,pension,qty,rate,amount,ex) VALUES\n\t\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','LOAN','','Employee Loan','','1','0','{$data['loanpayslip']}','')";
$Ri = db_exec($Sl) or errDie("unable to insert loan data for employee income on payslip.");
$sql = "UPDATE employees SET loanpayslip='0' WHERE empnum='{$data['empnum']}'";
$rslt = db_exec($sql) or errDie("Error updating loan information for payslip.");
}
if ($bonus[$id] > 0 && $data["payprd"] != "f" && $data["payprd"] != "w") {
$Sl = "INSERT INTO emp_inc(emp,year,period,week,date,payslip,type,code,description,pension,qty,rate,amount,ex) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','INCB','','Bonus','','1','0','{$bonus[$id]}','')";
$Ri = db_exec($Sl) or errDie("unable to insert data5.");
} else {
if ($bonus[$id] > 0) {
$Sl = "INSERT INTO emp_inc(emp,year,period,week,date,payslip,type,code,description,pension,qty,rate,amount,ex) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','INCB','','Special Bonus/Additional Salary','','1','0','{$bonus[$id]}','')";
$Ri = db_exec($Sl) or errDie("unable to insert data5.");
}
}
if ($annual[$id] > 0) {
$Sl = "INSERT INTO emp_inc(emp,year,period,week,date,payslip,type,code,description,pension,qty,rate,amount,ex) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','INCAB','','Annual Bonus','','1','0','{$annual[$id]}','')";
$Ri = db_exec($Sl) or errDie("unable to insert data5.");
}
if ($commission[$id] > 0) {
$Sl = "INSERT INTO emp_inc(emp,year,period,week,date,payslip,type,code,description,pension,qty,rate,amount,ex) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','INCC','','Commission','','1','0','{$commission[$id]}','')";
$Ri = db_exec($Sl) or errDie("unable to insert data6.");
}
if ($abonus[$id] > 0) {
$Sl = "INSERT INTO emp_inc(emp,year,period,week,date,payslip,type,code,description,pension,qty,rate,amount,ex) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','INCAB','','Bonus','','1','0','{$abonus[$id]}','')";
$Ri = db_exec($Sl) or errDie("unable to insert data6a.");
}
if ($all_travel[$id] > 0) {
$Sl = "INSERT INTO emp_inc(emp,year,period,week,date,payslip,type,code,description,pension,qty,rate,amount,ex) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','INCT','','Travel Allowance','','1','0','{$all_travel[$id]}','')";
$Ri = db_exec($Sl) or errDie("unable to insert data7.");
}
if ($loaninstall[$id] > 0) {
$Sl = "INSERT INTO emp_ded(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','DEDL','','Loan Repayment','1','0','{$loaninstall[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data8.");
}
if ($comp_pension[$id] > 0) {
$Sl = "INSERT INTO emp_com(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','COMP','','Pension','1','0','{$comp_pension[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data9.");
}
if ($emp_pension[$id] > 0) {
$Sl = "INSERT INTO emp_ded(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','DEDP','','Pension','1','0','{$emp_pension[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($comp_provident[$id] > 0) {
$Sl = "INSERT INTO emp_com(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','COMV','','Provident','1','0','{$comp_provident[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data9.");
}
if ($emp_provident[$id] > 0) {
$Sl = "INSERT INTO emp_ded(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','DEDV','','Provident','1','0','{$emp_provident[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($comp_other[$id] > 0) {
$Sl = "INSERT INTO emp_com(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','COMO','','Other Deductions','1','0','{$comp_other[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data9.");
}
if ($emp_other[$id] > 0) {
$Sl = "INSERT INTO emp_ded(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','DEDO','','Other Deductions','1','0','{$emp_other[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($comp_ret[$id] > 0) {
$Sl = "INSERT INTO emp_com(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','COMR','','Retirement Annuity Fund','1','0','{$comp_ret[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data9.");
}
if ($emp_ret[$id] > 0) {
$Sl = "INSERT INTO emp_ded(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','DEDR','','Retirement Annuity Fund','1','0','{$emp_ret[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($data["fringe_car1_contrib"] > 0) {
$Sl = "INSERT INTO emp_ded(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','DEDR','','Motorcar 1 Contribution for Use','1','0','{$data['fringe_car1_contrib']}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($data["fringe_car2_contrib"] > 0) {
$Sl = "INSERT INTO emp_ded(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','DEDR','','Motorcar 2 Contribution for Use','1','0','{$data['fringe_car2_contrib']}')";
$Ri = db_exec($Sl) or errDie("unable to insert data10.");
}
if ($comp_medical[$id] > 0) {
$Sl = "INSERT INTO emp_com(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','COMM','','Medical Contribution','1','0','{$comp_medical[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data.11");
}
if ($emp_medical[$id] > 0) {
$Sl = "INSERT INTO emp_ded(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','DEDM','','Medical Contribution','1','0','{$emp_medical[$id]}')";
$Ri = db_exec($Sl) or errDie("unable to insert data.12");
}
if ($overamt[$id] > 0) {
$Sl = "INSERT INTO emp_inc(emp,year,period,week,date,payslip,type,code,description,qty,rate,amount,ex) VALUES\n\t\t\t('{$data['empnum']}','" . EMP_YEAR . "','{$month}','{$week}','{$date}','{$pid}','INCO','','Over Time','1','0','{$overamt[$id]}','')";
$Ri = db_exec($Sl) or errDie("unable to insert data.13");
}
$ecost += 0;
db_conn('cubit');
$Sl = "SELECT * FROM empc WHERE emp='{$data['empnum']}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) > 0) {
while ($cdata = pg_fetch_array($Ri)) {
db_conn('cubit');
$sql = "SELECT * FROM costcenters WHERE ccid = '{$cdata['cid']}'";
$ccRslt = db_exec($sql) or errDie("Unable to retrieve Cost centers from database.");
$cc = pg_fetch_array($ccRslt);
$amount = sprint($ecost * $cdata['amount'] / 100);
db_conn(PRD_DB);
$sql = "\n\t\t\t\t\tINSERT INTO cctran (\n\t\t\t\t\t\tccid, trantype, typename, edate, description, \n\t\t\t\t\t\tamount, username, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cc['ccid']}', 'ct', 'Salary', '{$date}', 'Salary for employee, {$data['fnames']} {$data['sname']}', \n\t\t\t\t\t\t'{$amount}', '" . USER_NAME . "', '" . USER_DIV . "'\n\t\t\t\t\t)";
$insRslt = db_exec($sql) or errDie("Unable to retrieve insert Cost center amounts into database.");
}
}
$batch_setting = getCSetting("PRINT_PSLIPS_BATCH");
if (isset($batch_setting) and $batch_setting == "yes") {
if ($printslip != "n") {
$out .= "{$pid}|";
}
} else {
if ($printslip != "n") {
$out .= "nhprinter('salwages/payslip-print.php?id={$pid}',{$pid});";
}
}
}
$batch_setting = getCSetting("PRINT_PSLIPS_BATCH");
if (isset($batch_setting) and $batch_setting == "yes") {
$out .= "',{$pid});</script>";
} else {
if ($printslip != "n") {
$out .= "</script>";
//spmove('../main.php');</script>";
}
}
if ($printslip == "n") {
$out = "\n\t\t<h3>Process Employee Salaries</h3>\n\t\tSuccessfully processed salaries for selected employees.<br /><br />";
}
/* update printslip setting */
setCSetting("EMP_PRINTSLIP", $printslip);
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
//$out="";
return $out;
}
function write_cus($vars)
{
extract($vars);
$cus = qryCustomer($cusid, "cusnum, deptid, cusname, surname");
$dept = qryDepartment($cus["deptid"], "debtacc");
$refnum = getrefnum();
# date format
$sdate = $date;
$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);
if (isset($invids)) {
foreach ($invids as $key => $value) {
$ii = $invids[$key];
/* OPTION 1: STOCK INVOICES */
if (!isset($itype[$ii]) && !isset($ptype[$ii])) {
$sql = "SELECT prd,invnum,odate FROM cubit.invoices\n\t\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, amount, \n\t\t\t\t\t\t\tdate, type, div, allocation_date\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', '" . ($paidamt[$key] - $paidamt[$key] * 2) . "', \n\t\t\t\t\t\t\t'{$sdate}', 'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}'\n\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 Invoice No. {$inv['invnum']}", $paidamt[$key], "c");
//recordCT($paidamt[$key], $cus['cusnum'],0,$inv["odate"]);
$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 = "SELECT prd,invnum,descrip,odate,age FROM cubit.nons_invoices\n\t\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 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\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\t\tVALUES \n\t\t\t\t\t\t\t('{$cus['cusnum']}','{$inv['invnum']}', '" . ($paidamt[$key] - $paidamt[$key] * 2) . "','{$sdate}', 'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', '" . USER_DIV . "', '{$inv['odate']}')";
$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");
//recordCT($paidamt[$key], $cus['cusnum'],$inv['age'],$inv["odate"]);
$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[] = "SELECT '{$i}' AS prd,invid,invnum,odate FROM \"{$i}\".pinvoices\n\t\t\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 = "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\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\t\tVALUES \n\t\t\t\t\t\t\t('{$cus['cusnum']}','{$inv['invnum']}', '" . ($paidamt[$key] - $paidamt[$key] * 2) . "','{$sdate}', 'Payment for Non Stock Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}')";
$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']}", $paidamt[$key], "c");
//recordCT($paidamt[$key], $cus['cusnum'],0,$inv["odate"]);
$rinvids .= "|{$invids[$key]}";
$amounts .= "|{$paidamt[$key]}";
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
}
}
}
}
/*
$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 (sprint($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<tr class='" . bg_class() . "'>\n\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<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\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</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<tr class='" . bg_class() . "'>\n\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<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\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</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 = "\n\t\t\t\t\t\tINSERT INTO cubit.open_stmnt (\n\t\t\t\t\t\t\tcusnum, invid, amount, balance, date, type, st, div\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '0', '-{$out}', '-{$out}', '{$sdate}', 'Payment Received', 'n', '" . USER_DIV . "'\n\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
//$OUT .="<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 {
//$OUT .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>";}
}
}
if (sprint($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();
}
return array("rinvids" => $rinvids, "amounts" => $amounts, "invprds" => $invprds, "rages" => $rages, "deptacc" => $dept["debtacc"]);
}
function write($_POST)
{
# Get vars
global $_POST;
extract($_POST);
if (isset($back)) {
return enter();
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($deptid, "num", 1, 255, "Invalid Department.");
$v->isOk($supno, "string", 1, 255, "Invalid supplier number.");
$v->isOk($supname, "string", 1, 255, "Invalid supplier name.");
$v->isOk($loc, "string", 1, 3, "Invalid Type.");
$v->isOk($fcid, "num", 1, 30, "Invalid Currency.");
$v->isOk($vatnum, "string", 1, 255, "Invalid supplier vat number.");
$v->isOk($registration, "string", 1, 255, "Invalid registration/id number.");
$v->isOk($supaddr, "string", 1, 255, "Invalid supplier address.");
$v->isOk($suppostaddr, "string", 0, 255, "Invalid supplier postal address.");
$v->isOk($contname, "string", 1, 255, "Invalid contact name.");
$v->isOk($tel, "string", 1, 20, "Invalid tel no.");
$v->isOk($fax, "string", 0, 20, "Invalid fax no.");
$v->isOk($cell, "string", 0, 20, "Invalid cell no.");
$v->isOk($email, "email", 0, 255, "Invalid e-mail address.");
$v->isOk($url, "url", 0, 255, "Invalid web address.");
$v->isOk($listid, "num", 1, 20, "Invalid price list.");
$v->isOk($bankname, "string", 0, 255, "Invalid bank name.");
$v->isOk($branname, "string", 0, 255, "Invalid branch name.");
$v->isOk($brancode, "string", 0, 255, "Invalid branch code.");
$v->isOk($bankaccname, "string", 0, 255, "Invalid bank account name.");
$v->isOk($bankacctype, "string", 0, 255, "Invalid bank account type.");
$v->isOk($bankaccno, "num", 0, 255, "Invalid bank account number.");
$v->isOk($lead_source, "num", 0, 9, "Invalid lead source selected.");
$v->isOk($comments, "string", 0, 255, "Invalid characters in comment.");
$v->isOk($supbranch, "string", 0, 255, "Invalid supplier branch.");
$v->isOk($reference, "string", 0, 255, "Invalid references.");
$v->isOk($bee_status, "string", 0, 255, "Invalid BEE Status");
$v->isOk($team_id, "num", 1, 9, "Invalid team selection.");
$v->isOk($supp_grp, "num", 1, 9, "Invalid supplier group selected.");
$v->isOk($setdisc, "float", 1, 40, "Invalid Settlement Discount Amount.");
$v->isOk($setdays, "num", 1, 40, "Invalid Settlement Discount Days");
# display errors, if any
if ($v->isError()) {
$confirmCust = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirmCust .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirmCust .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirmCust;
}
db_conn('cubit');
$Sl = "SELECT * FROM suppliers WHERE supno='{$supno}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
if (pg_num_rows($Ri) > 0) {
return enter("<li class='err'>Supplier number exists.</li>");
}
# Connect to db
db_connect();
$curr = getSymbol($fcid);
if (!pglib_transaction("BEGIN")) {
return "<li class='err'>Unable to add supplier to database. (TB)</li>";
}
# Write to db
$sql = "\n\t\tINSERT INTO suppliers (\n\t\t\tdeptid, supno, supname, location, fcid, \n\t\t\tcurrency, vatnum, supaddr, suppostaddr, contname, tel, \n\t\t\tfax, cell, email, url, listid, bankname, \n\t\t\tbranname, brancode, bankaccno, balance, fbalance, \n\t\t\tdiv, lead_source, comments, branch, bee_status, \n\t\t\treference, team_id, registration, bankaccname, bankacctype, \n\t\t\tsetdisc, setdays\n\t\t) VALUES (\n\t\t\t'{$deptid}', '{$supno}', '{$supname}', '{$loc}', '{$fcid}', \n\t\t\t'{$curr['symbol']}', '{$vatnum}', '{$supaddr}', '{$suppostaddr}', '{$contname}', '{$tel}', \n\t\t\t'{$fax}', '{$cell}', '{$email}', '{$url}', '{$listid}', '{$bankname}', \n\t\t\t'{$branname}', '{$brancode}', '{$bankaccno}', 0, 0, \n\t\t\t'" . USER_DIV . "', '{$lead_source}', '{$comments}', '{$supbranch}', '{$bee_status}', \n\t\t\t'{$reference}', '{$team_id}', '{$registration}', '{$bankaccname}', '{$bankacctype}', \n\t\t\t'{$setdisc}', '{$setdays}'\n\t\t)";
$supRslt = db_exec($sql) or errDie("Unable to add supplier to the system.", SELF);
if (pg_cmdtuples($supRslt) < 1) {
return "<li class='err'>Unable to add supplier to database.</li>";
}
if (($supp_id = pglib_lastid("suppliers", "supid")) == 0) {
return "<li class='err'>Unable to add supplier to contact list.</li>";
}
#handle supplier groups
if ($supp_grp != 0) {
$insert_sql = "INSERT INTO supp_grpowners (grpid,supid) VALUES ('{$supp_grp}','{$supp_id}')";
$run_insert = db_exec($insert_sql) or errDie("Unable to add supplier group information.");
}
# Check if should be added to contact list
db_connect();
$sql = "\n\t\tINSERT INTO cons (\n\t\t\tname, surname, comp, ref, tell, cell, \n\t\t\tfax, email, hadd, padd, date, supp_id, \n\t\t\tcon, by, div\n\t\t) VALUES (\n\t\t\t'{$contname}', '{$supname}', '', 'Supplier', '{$tel}', '{$cell}', \n\t\t\t'{$fax}', '{$email}', '{$supaddr}', '{$suppostaddr}', CURRENT_DATE, '{$supp_id}', \n\t\t\t'No', '" . USER_NAME . "','" . USER_DIV . "'\n\t\t)";
$rslt = db_exec($sql) or errDie("Unable to add supplier to contact list.", SELF);
if (!pglib_transaction("COMMIT")) {
return "<li class='err'>Unable to add supplier to database. (TC)</li>";
}
$Date = date("Y-m-d");
$qryi = new dbUpdate();
for ($i = 1; $i <= 12; ++$i) {
$qryi->setTable("suppledger", "{$i}");
$cols = grp(m("supid", $supp_id), m("contra", "0"), m("edate", $Date), m("sdate", $Date), m("eref", "0"), m("descript", "Balance"), m("credit", "0"), m("debit", "0"), m("div", USER_DIV), m("dbalance", "0"), m("cbalance", "0"));
$qryi->setCols($cols);
$qryi->run(DB_INSERT);
}
if (isset($crm)) {
header("Location: crm/tokens-new.php?value={$supname}");
exit;
}
if ($re != "no") {
db_conn('cubit');
$re += 0;
$Sl = "UPDATE purchases SET supid='{$supp_id}' WHERE purid='{$re}'";
$Ri = db_exec($Sl);
//print $Sl;exit;
header("Location: purchase-new.php?purid={$re}&cont=1&letters=");
exit;
}
// Update documents
$sql = "SELECT * FROM crm.stmp_docs WHERE session='{$_REQUEST['CUBIT_SESSION']}'";
$stdoc_rslt = db_exec($sql) or errDie("Unable to retrieve docs.");
while ($stdoc_data = pg_fetch_array($stdoc_rslt)) {
$sql = "\n\t\t\tINSERT INTO crm.supplier_docs (\n\t\t\t\tsupid, file, type, filename, size, \n\t\t\t\treal_filename\n\t\t\t) VALUES (\n\t\t\t\t'{$supp_id}', '{$stdoc_data['file']}', '{$stdoc_data['type']}', '{$stdoc_data['filename']}', '{$stdoc_data['size']}', \n\t\t\t\t'{$stdoc_data['real_filename']}'\n\t\t\t)";
db_exec($sql) or errDie("Unable to save files to customer.");
$sql = "DELETE FROM crm.stmp_docs WHERE id='{$stdoc_data['id']}'";
db_exec($sql) or errDie("Unable to remove tmp file.");
}
$sql = "SELECT * FROM cubit.cons WHERE supp_id='{$supp_id}'";
$con_rslt = db_exec($sql) or errDie("Unable to retrieve contact.");
$con_data = pg_fetch_array($con_rslt);
if (pg_num_rows($con_rslt)) {
$con_out = "<a href='javascript:popupOpen(\"groupware/conper-add.php?type=conn&id={$con_data['id']}\")'>\n\t\t\tAdd Sub Contact\n\t\t</a>";
} else {
$con_out = "";
}
$write = "\n\t\t<table " . TMPL_tblDflts . " width='50%'>\n\t\t\t<tr>\n\t\t\t\t<th>Supplier added to the system</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>New Supplier <b>{$supname}</b>, has been successfully added to the system. {$con_out}</td>\n\t\t\t</tr>\n\t\t</table>\n\t\t<p>\n\t\t<table border=0 cellpadding='2' cellspacing='1'>\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='supp-new.php'>Add Supplier</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><a href='supp-view.php'>View Suppliers</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><a href='main.php'>Main Menu</a></td>\n\t\t\t</tr>\n\t\t</table>";
// return $write;
$_POST = array();
return enter("<li class='yay'>Supplier added to the system. {$con_out}</li><br>");
}
/**
* handles a order request
*
* @param string $key
* @param clsMailMsg $oMSG
* @param array $config
* @return bool
*/
function request_order($key, $oMSG, $config)
{
if (($stds = msg_std($oMSG)) === false) {
return false;
}
list($compname, $ipaddr, $bustel, $fromwho, $email) = $stds;
/* other side key */
if (($yourkey = getfrommmsg(REGEX_YOURKEY, $oMSG)) === false) {
return false;
}
/* validate keys */
if (($keyinfo = trhKeyPair($key, $yourkey)) === false) {
return false;
}
$custid = $keyinfo["custid"];
if (count($oMSG->parts) < 2) {
print "Invalid message: count(parts) < 2\n";
return false;
}
$attach = new clsMailMsg();
$attach->processMessage(implode("\r\n", $oMSG->parts[1]));
if ($attach->getAttachmentFilename() != "data.xml") {
print "Invalid message part. Disposition name != data.xml\n";
return false;
}
$XML = base64_decode(preg_replace("/[ \r\n\t]/", "", implode("", $attach->body)));
global $reqpur_activetag, $purch_info, $purch_items;
$reqpur_activetag = $purch_info = $purch_items = array();
$parser = xml_parser_create();
xml_set_element_handler($parser, "stElement", "endElement");
xml_parse($parser, $XML, true);
xml_parser_free($parser);
$i = grp(m("approved", "n"), m("custid", $custid), m(raw("trhkey"), dbrow("0.0.0.0/0", "{$keyinfo['send_key']}", "{$keyinfo['recv_key']}")));
$purch_info = array_merge($purch_info, $i);
foreach ($purch_info as $k => $v) {
if (empty($v)) {
$purch_info[$k] = raw("NULL");
}
}
$upd = new dbUpdate("recvpurch", "trh", $purch_info);
$upd->run(DB_INSERT);
$recvpurch_id = pglib_lastid("trh.recvpurch", "id");
$upd->setTable("recvpurch_items", "trh");
foreach ($purch_items as $pi_det) {
unset($pi_det["id"]);
$pi_det["recvpurch_id"] = $recvpurch_id;
$upd->setOpt($pi_det);
$upd->run(DB_INSERT);
}
print "Purchase inserted.\n";
$userinfo = qryUsers($config["MANAGEUSER"]);
msgSend($userinfo["username"], "Purchase received via Transheks. Click <a target='mainframe' href='../transheks/order_approve.php'>here</a> to view.");
}
