function buildlist(&$frm) { if (!isset($_GET["cusnum"])) { invalid_use(); } $cust = qryCustomer($_GET["cusnum"]); $qry = new dbSelect("plist_prices", "exten", grp(m("where", "listid='{$cust['pricelist']}' AND div='" . USER_DIV . "'"))); $qry->run(); $pli = array(); $pli_noshow = array(); while ($row = $qry->fetch_array()) { if ($row["show"] == "Yes") { $pli[$row["stkid"]] = $row["price"]; } else { $pli_noshow[$row["stkid"]] = $row["price"]; } } $qry = new dbSelect("stock", "cubit", grp(m("where", "div='" . USER_DIV . "'"))); $qry->run(); $plist = ""; while ($row = $qry->fetch_array()) { if (isset($pli_noshow[$row["stkid"]])) { continue; } if (isset($pli[$row["stkid"]])) { $price = $pli[$row["stkid"]]; } else { $price = $row["selamt"]; } $desc = preg_replace("/,/", "", $row["stkdes"]); $plist .= "{$row['stkcod']},{$price},{$desc}\n"; } /* @var $frm cForm */ $frm->setFormParm("post", "../emailsave_page.php"); $frm->setkey("sendmails"); $frm->add_hidden("emailsavepage_key", "sendmails", "string"); $frm->add_hidden("emailsavepage_action", "email", "string"); $frm->add_hidden("emailsavepage_content", base64_encode($plist), "string"); $frm->add_hidden("emailsavepage_mime", "text/plain", "string"); $frm->add_hidden("emailsavepage_subject", "Pricelist from " . COMP_NAME, "string"); $frm->add_hidden("emailsavepage_name", "pricelist.csv", "string"); $frm->add_heading("Sending Pricelist to Customer"); $frm->add_layout("\n\t\t<tr %bgc>\n\t\t\t<td>Customer:</td>\n\t\t\t<td>{$cust['surname']}</td>\n\t\t</tr>\n\t\t<tr %bgc>\n\t\t\t<td>%disp[0]</td>\n\t\t\t<td>%fld[0]</td>\n\t\t</tr>"); $frm->add_hidden("surnames[{$cust['cusnum']}]", $cust["surname"], "string"); $frm->add_text("E-mail", "emailcust[{$cust['cusnum']}]", $cust["email"], "string", "1:255"); return $frm->getfrm_input(); }
function confirm($cashid) { global $_GET; # validate input require_lib("validate"); $v = new validate(); $v->isOk($cashid, "num", 1, 20, "Invalid Reference number."); # display errors, if any if ($v->isError()) { $confirm = ""; $errors = $v->getErrors(); foreach ($errors as $e) { $confirm .= "<li class=err>" . $e["msg"]; } $confirm .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>"; return $confirm; } if (isset($_GET["type"]) and $_GET["type"] == "cash") { $bou = "cashbook"; } else { $bou = "batch_cashbook"; } // Connect to database Db_Connect(); $sql = "SELECT * FROM {$bou} WHERE cashid = '{$cashid}' AND div = '" . USER_DIV . "'"; $accntRslt = db_exec($sql) or errDie("ERROR: Unable to retrieve cashbook entry details from database.", SELF); $accnt = pg_fetch_array($accntRslt); $confirm = "\n\t<center>\n\t<table " . TMPL_tblDflts . " width='300'>\n\t\t<tr>\n\t\t\t<td colspan='2'><h3>Mutliple Debtors</h3></td>\n\t\t</tr>\n\t\t" . TBL_BR . "\n\t\t<tr>\n\t\t\t<th>Customer Name</th>\n\t\t\t<th>Amount</th>\n\t\t</tr>"; $accids = explode(",", $accnt['multicusnum']); $amounts = explode(",", $accnt['multicusamt']); foreach ($amounts as $key => $amt) { $ci = qryCustomer($accids[$key]); $amt = sprint($amt); $confirm .= "\n\t\t<tr class='bg-even''>\n\t\t\t<td>{$ci['surname']}, {$ci['cusname']}</td>\n\t\t\t<td>" . CUR . " {$amt}</td>\n\t\t</tr>"; } $confirm .= "<tr><td><br></td></tr>\n\t<tr><td colspan=2 align=center><input type=button value=' [X] Close ' onClick='javascript:window.close();'></td></tr>\n\t</table>"; return $confirm; }
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 recvpayment_write() { if (isset($_POST["btn_back"])) { return details($_POST); } extract($_POST); $v = new validate(); $v->isOk($cusnum, "num", 1, 10, "Invalid customer id."); $v->isOk($bank_acc, "num", 1, 10, "Invalid cash account selected."); $v->isOk($pcc, "float", 1, 40, "Invalid credit card amount."); $v->isOk($pcash, "float", 1, 40, "Invalid cash amount."); $v->isOk($pcheque, "float", 1, 40, "Invalid cheque amount."); $v->isOk($amt, "float", 1, 40, "Invalid total received amount."); $v->isOk($out, "float", 1, 40, "Invalid unallocated amount."); $v->isOk($descript, "string", 1, 255, "Invalid description."); $v->isOk($date, "date", 1, 1, "Invalid invoice date."); if ($v->isError()) { return details($_POST, $v->genErrors()); } $sdate = $date; $cus = qryCustomer($cusnum); $dept = qryDepartment($cus["deptid"], "debtacc"); $refnum = getrefnum(); pglib_transaction("BEGIN"); /* do the calculations/recordings */ # update the customer (make balance less) $sql = "UPDATE cubit.customers SET balance = (balance - '{$amt}'::numeric(13,2))\n\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\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 invoice balance $sql = "UPDATE cubit.invoices\n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,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 = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,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\tINSERT INTO cubit.stmnt \n\t\t\t\t\t\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\tVALUES \n\t\t\t\t\t\t('{$cus['cusnum']}','{$inv['invnum']}', '" . ($paidamt[$key] - $paidamt[$key] * 2) . "','{$sdate}', 'Payment for 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 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 = "SELECT prd,invnum,descrip,age,odate FROM 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\tSET balance = (balance - {$paidamt[$key]}::numeric(13,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 = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,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\tINSERT INTO cubit.stmnt \n\t\t\t\t\t\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\tVALUES \n\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'], $sdate); $rinvids .= "|{$invids[$key]}"; $amounts .= "|{$paidamt[$key]}"; $invprds .= "|0"; $rages .= "|{$inv['age']}"; $invidsers .= " - {$inv['invnum']}"; } else { /* pos invoices */ $sql = "SELECT * FROM cubit.prd_pinvoices\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 = "UPDATE \"{$inv['iprd']}\".pinvoices\n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,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 = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,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\tINSERT INTO cubit.stmnt\n\t\t\t\t\t\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\tVALUES \n\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, $sdate); $rinvids .= "|{$invids[$key]}"; $amounts .= "|{$paidamt[$key]}"; $invprds .= "|{$inv['prd']}"; //$rages .= "|$inv[age]"; $invidsers .= " - {$inv['invnum']}"; } } } } writetrans($bank_acc, $dept['debtacc'], $sdate, $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='{$cusnum}'"), 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 .= "<tr class='" . bg_class() . "'><td><input type=hidden size=20 name=open[{$oid}] value='{$oid}'>{$od['type']}</td>\n\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td><td>{$od['date']}</td><td><input type=hidden name='open_amount[{$oid}]' value='{$open_amount[$oid]}'>\n\t\t\t\t\t" . CUR . " {$open_amount[$oid]}</td></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 .= "<tr class='" . bg_class() . "'><td><input type=hidden size=20 name=open[{$oid}] value='{$od['id']}'>{$od['type']}</td>\n\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td><td>{$od['date']}</td><td><input type=hidden name='open_amount[{$oid}]' value='{$open_amount[$oid]}'>\n\t\t\t\t\t" . CUR . " {$open_amount[$oid]}</td></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, $sdate); $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", $sdate)); $dbobj = new dbUpdate("stmnt", "cubit", $cols); $dbobj->run(DB_INSERT); $dbobj->free(); custledger($cus['cusnum'], $bank_acc, $sdate, "PAYMENT", "Payment received.", $out, "c"); } $sql = "INSERT INTO cubit.payrec(date,by,multiinv,amount,method,prd,note)\n\t\t\tVALUES('{$sdate}','" . USER_NAME . "', '{$invidsers}', '{$pcash}','Cash','" . PRD_DB . "','0')"; db_exec($sql) or errDie("Unable to insert data."); $sql = "INSERT INTO cubit.payrec(date,by,multiinv,amount,method,prd,note)\n\t\t\tVALUES('{$sdate}','" . USER_NAME . "', '{$invidsers}', '{$pcc}','Credit Card','" . PRD_DB . "','0')"; db_exec($sql) or errDie("Unable to insert data."); $sql = "INSERT INTO cubit.payrec(date,by,multiinv,amount,method,prd,note)\n\t\t\tVALUES('{$sdate}','" . USER_NAME . "', '{$invidsers}', '{$pcheque}','Cheque','" . PRD_DB . "','0')"; db_exec($sql) or errDie("Unable to insert data."); pglib_transaction("COMMIT"); $_POST["pcc"] = $_POST["pcheque"] = $_POST["pcash"] = "0.00"; return details($_POST, "<li class='err'>Payment received successfully</li>"); }
function write($_POST) { extract($_POST); if (isset($back)) { unset($_POST["back"]); return alloc($_POST); } require_lib("validate"); $v = new validate(); $v->isOk($all, "num", 1, 1, "Invalid allocation."); $v->isOk($bankid, "num", 1, 30, "Invalid Bank Account."); $v->isOk($date, "date", 1, 14, "Invalid Date."); $v->isOk($out, "float", 1, 40, "Invalid out amount."); $v->isOk($descript, "string", 0, 255, "Invalid Description."); $v->isOk($reference, "string", 0, 50, "Invalid Reference Name/Number."); $v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number."); $v->isOk($amt, "float", 1, 40, "Invalid amount."); $v->isOk($cusid, "num", 1, 40, "Invalid customer number."); $v->isOk($out1, "float", 0, 40, "Invalid paid amount(currant)."); $v->isOk($out2, "float", 0, 40, "Invalid paid amount(30)."); $v->isOk($out3, "float", 0, 40, "Invalid paid amount(60)."); $v->isOk($out4, "float", 0, 40, "Invalid paid amount(90)."); $v->isOk($out5, "float", 0, 40, "Invalid paid amount(120)."); if (isset($invids)) { foreach ($invids as $key => $value) { $v->isOk($invids[$key], "num", 1, 50, "Invalid Invoice No."); $v->isOk($paidamt[$key], "float", 1, 40, "Invalid amount to be paid."); } } if ($v->isError()) { $confirm = $v->genErrors(); return $confirm . confirm($_POST); } /* get bank account id of cash on hand account IF this entry is cash */ if (($bank_acc = getbankaccid($bankid)) === false or $bankid == "0") { //old function didnt check if cash is selected ... if(($bank_acc = getbankaccid($bankid)) === false) { $sql = "SELECT accid FROM core.accounts WHERE accname='Cash on Hand'"; $rslt = db_exec($sql); if (pg_num_rows($rslt) < 1) { if ($bankid == 0) { return "There is no 'Cash on Hand' account, there was one, but\n\t\t\t\t\t\t**s not there now, you must have deleted it, if you want\n\t\t\t\t\t\tto use cash functionality please create a 'Cash on Hand' account."; } else { return "Invalid bank acc."; } } $bank_acc = pg_fetch_result($rslt, 0); } $cus = qryCustomer($cusid, "cusnum, deptid, cusname, surname"); $dept = qryDepartment($cus["deptid"], "debtacc"); $refnum = getrefnum(); pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF); # date format $sdate = explode("-", $date); $sdate = $sdate[2] . "-" . $sdate[1] . "-" . $sdate[0]; $cheqnum = 0 + $cheqnum; $pay = ""; $accdate = $sdate; /* Paid invoices */ $invidsers = ""; $rinvids = ""; $amounts = ""; $invprds = ""; $rages = ""; /* OPTION 1 : AUTO ALLOCATE (write) */ if ($all == 0) { # update the customer (make balance less) $sql = "UPDATE cubit.customers SET balance = (balance - '{$amt}'::numeric(13,2))\n\t\t\t\tWHERE cusnum = '{$cus['cusnum']}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF); $reverse_allocation_dates = ""; $reverse_allocation_amounts = ""; if (isset($invids)) { foreach ($invids as $key => $value) { $ii = $invids[$key]; /* OPTION 1: STOCK INVOICES */ if (!isset($itype[$ii]) && !isset($ptype[$ii])) { $sql = "\n\t\t\t\t\t\tSELECT prd,invnum,odate \n\t\t\t\t\t\tFROM cubit.invoices\n\t\t\t\t\t\tWHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'"; $invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database."); if (pg_numrows($invRslt) < 1) { return "<li class='err'>Invalid Invoice Number.</li>"; } $inv = pg_fetch_array($invRslt); $inv['invnum'] += 0; // reduce invoice balance $sql = "UPDATE cubit.invoices\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); $sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); # record the payment on the statement $sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}','{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $paidamt[$key] * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t)"; if (!(isset($bulk_pay) and strlen($bulk_pay) > 0)) { $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); } else { $reverse_allocation_dates .= "{$inv['odate']}|"; $reverse_allocation_amounts .= sprint($paidamt[$key] - $paidamt[$key] * 2) . "|"; } custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Invoice No. {$inv['invnum']}", $paidamt[$key], "c"); $rinvids .= "|{$invids[$key]}"; $amounts .= "|{$paidamt[$key]}"; if ($inv['prd'] == "0") { $inv['prd'] = PRD_DB; } $invprds .= "|{$inv['prd']}"; $rages .= "|0"; $invidsers .= " - {$inv['invnum']}"; /* OPTION 1: NONS STOCK INVOICES */ } else { if (!isset($ptype[$ii])) { $sql = "\n\t\t\t\t\t\tSELECT prd,invnum,descrip,age,odate \n\t\t\t\t\t\tFROM cubit.nons_invoices \n\t\t\t\t\t\tWHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'"; $invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database."); if (pg_numrows($invRslt) < 1) { return "<li class='err'>Invalid Invoice Number."; } $inv = pg_fetch_array($invRslt); $inv['invnum'] += 0; # reduce the money that has been paid $sql = "UPDATE cubit.nons_invoices\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); $sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); # record the payment on the statement $sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, \n\t\t\t\t\t\t\tdiv, allocation_date\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $paidamt[$key] * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', \n\t\t\t\t\t\t\t'" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t)"; if (!(isset($bulk_pay) and strlen($bulk_pay) > 0)) { $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); } else { $reverse_allocation_dates .= "{$inv['odate']}|"; $reverse_allocation_amounts .= sprint($paidamt[$key] - $paidamt[$key] * 2) . "|"; } custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}", $paidamt[$key], "c"); //recordCT($paidamt[$key], $cus['cusnum'],$inv['age'],$accdate); $rinvids .= "|{$invids[$key]}"; $amounts .= "|{$paidamt[$key]}"; $invprds .= "|0"; $rages .= "|{$inv['age']}"; $invidsers .= " - {$inv['invnum']}"; } else { /* pos invoices */ $sqls = array(); for ($i = 1; $i <= 12; ++$i) { $sqls[] = "\n\t\t\t\t\t\t\tSELECT '{$i}' AS prd,invid,invnum,odate \n\t\t\t\t\t\t\tFROM \"{$i}\".pinvoices \n\t\t\t\t\t\t\tWHERE invid='{$invids[$key]}' AND div='" . USER_DIV . "'"; } $sql = implode(" UNION ", $sqls); $invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database."); if (pg_numrows($invRslt) < 1) { return "<li class='err'>Invalid Invoice Number."; } $inv = pg_fetch_array($invRslt); // reduce the invoice balance $sql = "UPDATE \"{$inv['prd']}\".pinvoices\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); $sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); # record the payment on the statement $sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, div, \n\t\t\t\t\t\t\tallocation_date\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $paidamt[$key] * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment for Non Stock Invoice No. {$inv['invnum']}', '" . USER_DIV . "', \n\t\t\t\t\t\t\t'{$inv['odate']}'\n\t\t\t\t\t\t)"; if (!(isset($bulk_pay) and strlen($bulk_pay) > 0)) { $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); } else { $reverse_allocation_dates .= "{$inv['odate']}|"; $reverse_allocation_amounts .= sprint($paidamt[$key] - $paidamt[$key] * 2) . "|"; } custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']}", $paidamt[$key], "c"); //recordCT($paidamt[$key], $cus['cusnum'],0,$accdate); $rinvids .= "|{$invids[$key]}"; $amounts .= "|{$paidamt[$key]}"; $invprds .= "|{$inv['prd']}"; $rages .= "|0"; $invidsers .= " - {$inv['invnum']}"; } } } #record the total for the statement if bulk is selected if (isset($bulk_pay) and strlen($bulk_pay) > 0) { $arrtotal = sprint(array_sum($paidamt)); $sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, div, \n\t\t\t\t\t\t\tallocation_date, reverse_allocation_dates, reverse_allocation_amounts\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($arrtotal - $arrtotal * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment Received (Ref:{$reference})', '" . USER_DIV . "', \n\t\t\t\t\t\t\t'1500-01-01', '{$reverse_allocation_dates}', '{$reverse_allocation_amounts}'\n\t\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); } } $cols = grp(m("bankid", $bankid), m("trantype", "deposit"), m("date", $sdate), m("name", "{$cus['cusname']} {$cus['surname']}"), m("descript", "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"), m("cheqnum", $cheqnum), m("amount", $amt), m("banked", "no"), m("accinv", $dept["debtacc"]), m("cusnum", $cus["cusnum"]), m("rinvids", $rinvids), m("amounts", $amounts), m("invprds", $invprds), m("rages", $rages), m("reference", $reference), m("div", USER_DIV)); $dbobj = new dbUpdate("cashbook", "cubit", $cols); $dbobj->run(DB_INSERT); $dbobj->free(); /* $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, cusnum, rinvids, amounts, invprds, rages, reference, div) VALUES ('$bankid', 'deposit', '$sdate', '$cus[cusname] $cus[surname]', '', '$cheqnum', '$amt', 'no', '$dept[debtacc]', '$cus[cusnum]', '$rinvids', '$amounts', '$invprds', '$rages', '$reference', '".USER_DIV."')"; $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF); */ writetrans($bank_acc, $dept['debtacc'], $accdate, $refnum, $amt, "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"); db_conn('cubit'); if ($out > 0) { /* START OPEN ITEMS */ $openstmnt = new dbSelect("open_stmnt", "cubit", grp(m("where", "balance>0 AND cusnum='{$cusid}'"), m("order", "date"))); $openstmnt->run(); $open_out = $out; $i = 0; $ox = ""; while ($od = $openstmnt->fetch_array()) { if ($open_out == 0) { continue; } $oid = $od['id']; if ($open_out >= $od['balance']) { $open_amount[$oid] = $od['balance']; $open_out = sprint($open_out - $od['balance']); $ox .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><input type='hidden' size='20' name='open[{$oid}]' value='{$oid}'>{$od['type']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t\t\t<td><input type='hidden' name='open_amount[{$oid}]' value='{$open_amount[$oid]}'>" . CUR . " {$open_amount[$oid]}</td>\n\t\t\t\t\t\t\t</tr>"; $Sl = "UPDATE cubit.open_stmnt SET balance=balance-'{$open_amount[$oid]}' WHERE id='{$oid}'"; $Ri = db_exec($Sl) or errDie("Unable to update statement."); } elseif ($open_out < $od['balance']) { $open_amount[$oid] = $open_out; $open_out = 0; $ox .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><input type='hidden' size='20' name='open[{$oid}]' value='{$od['id']}'>{$od['type']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t\t\t<td><input type='hidden' name='open_amount[{$oid}]' value='{$open_amount[$oid]}'>" . CUR . " {$open_amount[$oid]}</td>\n\t\t\t\t\t\t\t</tr>"; $Sl = "UPDATE cubit.open_stmnt SET balance=balance-'{$open_amount[$oid]}' WHERE id='{$oid}'"; $Ri = db_exec($Sl) or errDie("Unable to update statement."); } $i++; } if (open()) { $bout = $out; $out = $open_out; if ($out > 0) { $sql = "INSERT INTO cubit.open_stmnt(cusnum, invid, amount, balance, date, type, st, div) VALUES('{$cus['cusnum']}', '0', '-{$out}', '-{$out}', '{$sdate}', 'Payment Received', 'n', '" . USER_DIV . "')"; $stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF); //$confirm .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>"; } $out = $bout; } else { //$confirm .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>";} } } if ($out > 0) { recordCT($out, $cus['cusnum'], 0, $accdate); $cols = grp(m("cusnum", $cus["cusnum"]), m("invid", 0), m("amount", -$out), m("date", $sdate), m("type", "Payment Received"), m("div", USER_DIV), m("allocation_date", $accdate)); $dbobj = new dbUpdate("stmnt", "cubit", $cols); $dbobj->run(DB_INSERT); $dbobj->free(); custledger($cus['cusnum'], $bank_acc, $sdate, "PAYMENT", "Payment received.", $out, "c"); } } /* start moving invoices */ // move invoices that are fully paid $sql = "SELECT * FROM cubit.invoices WHERE balance=0 AND printed = 'y' AND done = 'y' AND div = '" . USER_DIV . "'"; $invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); while ($x = pg_fetch_array($invbRslt)) { if (($prd = $x['prd']) == "0") { $prd = PRD_DB; } // move invoice $cols = grp(m("invid", $x["invid"]), m("invnum", $x["invnum"]), m("deptid", $x["deptid"]), m("cusnum", $x["cusnum"]), m("deptname", $x["deptname"]), m("cusacc", $x["cusacc"]), m("cusname", $x["cusname"]), m("surname", $x["surname"]), m("cusaddr", $x["cusaddr"]), m("cusvatno", $x["cusvatno"]), m("cordno", $x["cordno"]), m("ordno", $x["ordno"]), m("chrgvat", $x["chrgvat"]), m("terms", $x["terms"]), m("traddisc", $x["traddisc"]), m("salespn", $x["salespn"]), m("odate", $x["odate"]), m("delchrg", $x["delchrg"]), m("subtot", $x["subtot"]), m("vat", $x["vat"]), m("total", $x["total"]), m("age", $x["age"]), m("comm", $x["comm"]), m("discount", $x["discount"]), m("delivery", $x["delivery"]), m("docref", $x["docref"]), m("prd", $x["prd"]), m("delvat", $x["delvat"]), m("balance", 0), m("printed", "y"), m("done", "y"), m("username", USER_NAME), m("div", USER_DIV)); $dbobj = new dbUpdate("invoices", $prd, $cols); $dbobj->run(DB_INSERT); $dbobj->free(); // record movement $cols = grp(m("invtype", "inv"), m("invnum", $x["invnum"]), m("prd", $x["prd"]), m("docref", $x["docref"]), m("div", USER_DIV)); $dbobj->setTable("movinv", "cubit"); $dbobj->setOpt($cols); $dbobj->run(); $dbobj->free(); // move invoice items $inv_items = new dbSelect("inv_items", "cubit", grp(m("where", wgrp(m("invid", $x["invid"]), m("div", USER_DIV))))); $inv_items->run(); while ($xi = $inv_items->fetch_array()) { $xi['vatcode'] += 0; $xi['account'] += 0; $xi['del'] += 0; $cols = grp(m("invid", $x["invid"]), m("whid", $xi["whid"]), m("stkid", $xi["stkid"]), m("qty", $xi["qty"]), m("unitcost", $xi["unitcost"]), m("amt", $xi["amt"]), m("disc", $xi["disc"]), m("discp", $xi["discp"]), m("vatcode", $xi["vatcode"]), m("account", $xi["account"]), m("description", $xi["description"]), m("del", $xi["del"]), m("noted", $xi["noted"]), m("serno", $xi["serno"]), m("div", USER_DIV)); $dbobj->setTable("inv_items", $prd); $dbobj->setOpt($cols); $dbobj->run(); $dbobj->free(); } /* remove invoice from cubit schema */ $dbobj = new dbDelete("invoices", "cubit", wgrp(m("invid", $x["invid"]), m("div", USER_DIV))); $dbobj->run(); $dbobj->setTable("inv_items", "cubit"); $dbobj->run(); } pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF); $cashbook_id = pglib_lastid("cashbook", "cashid"); if (isset($print_recpt) and $print_recpt == "yes") { $showreceipt = "<script>printer ('bank/bank-recpt-inv-print.php?recid={$cashbook_id}');</script>"; } else { $showreceipt = ""; } // status report $write = "\n\t\t{$showreceipt}\n\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t<tr>\n\t\t\t\t<th>Bank Receipt</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Bank Receipt added to cash book.</td>\n\t\t\t</tr>\n\t\t</table>"; $OUTPUT = "\n\t\t<center>\n\t\t<table width='90%'>\n\t\t\t<tr valign='top'>\n\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t<td align='center'>" . mkQuickLinks(ql("bank-pay-add.php", "Add Bank Payment"), ql("bank-recpt-add.php", "Add Bank Receipt"), ql("bank-recpt-inv.php", "Add Customer Payment"), ql("cashbook-view.php", "View Cash Book")) . "\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>"; return $OUTPUT; }
function write($_POST) { 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); } # 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 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 cconfirm($_POST) { # Get vars extract($_POST); # validate input require_lib("validate"); $v = new validate(); $v->isOk($invid, "num", 1, 20, "Invalid Invoice number."); if (isset($ctyp) && $ctyp == 's') { $v->isOk($cusnum, "num", 1, 20, "Invalid customer number."); } elseif (isset($ctyp) && $ctyp == 'c') { $v->isOk($deptid, "num", 1, 20, "Invalid Department."); } if (isset($stkaccs)) { foreach ($stkaccs as $key => $accid) { $v->isOk($accid, "num", 1, 20, "Invalid Item Account number."); } } else { $v->isOk($invid, "num", 0, 0, "Invalid Item Account number."); } # display errors, if any if ($v->isError()) { $err = ""; $errors = $v->getErrors(); foreach ($errors as $e) { $err .= "<li class=err>" . $e["msg"]; } $confirm = "{$err}<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>"; return $confirm; } # Get Invoice info db_connect(); $sql = "SELECT * FROM nons_invoices WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'"; $invRslt = db_exec($sql) or errDie("Unable to get invoices information"); if (pg_numrows($invRslt) < 1) { return "<i class=err>Not Found</i>"; } $inv = pg_fetch_array($invRslt); /* --- Start Products Display --- */ # Products layout $products = "\r\n\t<table " . TMPL_tblDflts . " width='100%'>\r\n\t<tr>\r\n\t\t<th width='5%'>#</th>\r\n\t\t<th width='40%'>DESCRIPTION</th>\r\n\t\t<th width='10%'>QTY</th>\r\n\t\t<th width='10%'>UNIT PRICE</th>\r\n\t\t<th width='10%'>AMOUNT</th>\r\n\t\t<th width='25%'>ACCOUNT</th>\r\n\t<tr>"; # get selected stock in this Invoice db_connect(); $sql = "SELECT * FROM nons_inv_items WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'"; $stkdRslt = db_exec($sql); $i = 0; while ($stkd = pg_fetch_array($stkdRslt)) { $stkacc = $stkaccs[$stkd['id']]; $accRs = get("core", "accname,topacc,accnum", "accounts", "accid", $stkacc); $acc = pg_fetch_array($accRs); db_conn('cubit'); $Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatex']}'"; $Ri = db_exec($Sl); $vd = pg_fetch_array($Ri); if (TAX_VAT != $vd['vat_amount'] and $vd['vat_amount'] != "0.00") { $showvat = FALSE; } $i++; # put in product $products .= "<tr class='bg-odd'>\r\n\t\t\t<td align=center>{$i}</td>\r\n\t\t\t<td>{$stkd['description']}</td>\r\n\t\t\t<td>{$stkd['qty']}</td>\r\n\t\t\t<td>{$stkd['unitcost']}</td>\r\n\t\t\t<td>" . CUR . " {$stkd['amt']}</td>\r\n\t\t\t<td><input type='hidden' name='stkaccs[{$stkd['id']}]' value='{$stkacc}'>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\r\n\t\t</tr>"; } $products .= "</table>"; /* --- Start Some calculations --- */ # Get subtotal $SUBTOT = sprint($inv['subtot']); # Get Total $TOTAL = sprint($inv['total']); # Get vat $VAT = sprint($inv['vat']); /* --- End Some calculations --- */ # format date list($syear, $smon, $sday) = explode("-", $inv['odate']); db_connect(); # cust % bank if ($ctyp == 's') { $cust = qryCustomer($cusnum); $details = "\r\n\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<th colspan='2'> Customer Details </th>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<input type='hidden' name='cusnum' value='{$cusnum}'>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'>{$cust['cusname']} {$cust['surname']}</td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer Address</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'><pre>{$cust['addr1']}</pre></td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer VAT Number</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'>{$cust['vatnum']}</td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t</table>"; } elseif ($ctyp == 'c') { $dept = qryDepartment($deptid); $details = "\r\n\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<th colspan='2'> Customer Details </th>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<input type='hidden' name='deptid' value='{$deptid}'>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'>{$inv['cusname']} </td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer Address</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'><pre>{$inv['cusaddr']}</pre></td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer VAT Number</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'>{$inv['cusvatno']}</td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t</table>"; } elseif ($ctyp == 'cb') { db_conn("cubit"); $sql = "SELECT * FROM bankacct WHERE bankid = '{$inv['accid']}'"; $deptRslt = db_exec($sql) or errDie("Unable to view customers"); if (pg_numrows($deptRslt) < 1) { $error = "<li class='err'> Bank not Found.</li>"; $confirm .= "{$error}<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>"; return $confirm; } else { $dept = pg_fetch_array($deptRslt); $supacc = "{$dept['bankname']} - {$dept['accname']}({$dept['acctype']})"; } $details = "\r\n\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<th colspan='2'> Customer Details </th>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<input type='hidden' name='bankid' value='{$inv['accid']}'>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'>{$inv['cusname']} </td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer Address</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'><pre>{$inv['cusaddr']}</pre></td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer VAT Number</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'>{$inv['cusvatno']}</td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Account</td>\r\n\t\t\t\t\t\t\t\t<td>{$supacc}</td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t</table>"; } elseif ($ctyp == 'ac') { $accountc += 0; $accRs = get("core", "accname,topacc,accnum", "accounts", "accid", $accountc); $accd = pg_fetch_array($accRs); $details = "\r\n\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<th colspan='2'>Customer Details </th>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<input type='hidden' name='accountc' value='{$accountc}'>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'>{$inv['cusname']} </td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer Address</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'><pre>{$inv['cusaddr']}</pre></td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Customer VAT Number</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'>{$inv['cusvatno']}</td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t<td>Account</td>\r\n\t\t\t\t\t\t\t\t<td valign='center'>{$accd['accname']}</td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t</table>"; } if (!isset($showvat)) { $showvat = TRUE; } if ($showvat == TRUE) { $vat14 = AT14; } else { $vat14 = ""; } /* -- Final Layout -- */ $details = "\r\n\t\t\t\t\t<center>\r\n\t\t\t\t\t<h3>Non-Stock Invoice Details</h3>\r\n\t\t\t\t\t<form action='" . SELF . "' method='POST' name='form'>\r\n\t\t\t\t\t\t<input type='hidden' name='key' value='cwrite'>\r\n\t\t\t\t\t\t<input type='hidden' name='invid' value='{$invid}'>\r\n\t\t\t\t\t\t<input type='hidden' name='ctyp' value='{$ctyp}'>\r\n\t\t\t\t\t<table " . TMPL_tblDflts . " width='95%'>\r\n\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t<td valign='top'>{$details}</td>\r\n\t\t\t\t\t\t\t<td valign='top' align='right'>\r\n\t\t\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t\t\t<th colspan='2'> Non-Stock Invoice Details </th>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t\t\t<td>Non-Stock Invoice No.</td>\r\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>T {$inv['invid']}</td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t\t\t<td>Proforma Inv No.</td>\r\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$inv['docref']}</td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t\t\t<td>Date</td>\r\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$sday}-{$smon}-{$syear}</td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t\t\t<td>VAT Inclusive</td>\r\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$inv['chrgvat']}</td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t\t\t<td>Terms</td>\r\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$inv['terms']} Days</td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t</table>\r\n\t\t\t\t\t\t\t</td>\r\n\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t<tr><td><br></td></tr>\r\n\t\t\t\t\t\t<tr><td colspan='2'>{$products}</td></tr>\r\n\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t<td>\r\n\t\t\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t\t\t<th width='40%'>Quick Links</th>\r\n\t\t\t\t\t\t\t\t\t\t<th width='45%'>Remarks</th>\r\n\t\t\t\t\t\t\t\t\t\t<td rowspan='5' valign='top' width='15%'><br></td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t\t\t<td align='center'><a href='nons-invoice-new.php'>New Non-Stock Invoices</a></td>\r\n\t\t\t\t\t\t\t\t\t\t<td class='" . bg_class() . "' rowspan='4' align='center' valign='top'>" . nl2br($inv['remarks']) . "</td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t\t\t<td align='center'><a href='nons-invoice-view.php'>View Non-Stock Invoices</a></td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\r\n\t\t\t\t\t\t\t\t</table>\r\n\t\t\t\t\t\t\t</td>\r\n\t\t\t\t\t\t\t<td align='right'>\r\n\t\t\t\t\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\r\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t\t\t<td>SUBTOTAL</td>\r\n\t\t\t\t\t\t\t\t\t\t<td align='right'>" . CUR . " {$inv['subtot']}</td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t\t\t<td>VAT {$vat14}</td>\r\n\t\t\t\t\t\t\t\t\t\t<td align='right'>" . CUR . " {$inv['vat']}</td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t\t\t\t\t\t\t<th>GRAND TOTAL</th>\r\n\t\t\t\t\t\t\t\t\t\t<td align='right'>" . CUR . " {$inv['total']}</td>\r\n\t\t\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t\t</table>\r\n\t\t\t\t\t\t\t</td>\r\n\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t<td align='right'><input type='submit' value='Write »'></td>\r\n\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t</table>\r\n\t\t\t\t\t</form>\r\n\t\t\t\t\t</center>"; return $details; }
function recvpayment_write() { if (isset($_POST["btn_back"])) { return details($_POST); } extract($_POST); $bank_acc = qryAccountsName("Cash on Hand"); $bank_acc = $bank_acc["accid"]; $cred_acc = qryAccountsName("POS Credit Card Control"); $cred_acc = $cred_acc["accid"]; $v = new validate(); $v->isOk($cusnum, "num", 1, 10, "Invalid customer id."); $v->isOk($bank_acc, "num", 1, 10, "Invalid cash account selected."); $v->isOk($pcc, "float", 1, 40, "Invalid credit card amount."); $v->isOk($pcash, "float", 1, 40, "Invalid cash amount."); $v->isOk($pcheque, "float", 1, 40, "Invalid cheque amount."); $v->isOk($amt, "float", 1, 40, "Invalid total received amount."); $v->isOk($date, "date", 1, 1, "Invalid invoice date."); if ($v->isError()) { return details($_POST, $v->genErrors()); } $sdate = $date; $cus = qryCustomer($cusnum); $dept = qryDepartment($cus["deptid"], "debtacc"); $refnum = getrefnum(); pglib_transaction("BEGIN"); /* do the calculations/recordings */ # update the customer (make balance less) $sql = "UPDATE cubit.customers SET balance = (balance - '{$amt}'::numeric(13,2))\r\n\t\t\tWHERE cusnum = '{$cus['cusnum']}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF); $sql = "SELECT prd,invnum,descrip,age FROM cubit.nons_invoices\r\n\t\t\tWHERE invid ='{$invid}' 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 if ($amt) { $sql = "UPDATE cubit.nons_invoices\r\n\t\t\t\tSET balance = (balance - {$amt}::numeric(13,2))\r\n\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 = "UPDATE cubit.open_stmnt\r\n\t\t\t\tSET balance = (balance - {$amt}::numeric(13,2))\r\n\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 = "INSERT INTO cubit.stmnt(cusnum, invid, amount, date, type, div)\r\n\t\t\t\tVALUES('{$cus['cusnum']}','{$inv['invnum']}',\r\n\t\t\t\t'" . ($amt - $amt * 2) . "','{$sdate}',\r\n\t\t\t\t'Payment for Hire Invoice No. {$inv['invnum']}',\r\n\t\t\t\t'" . USER_DIV . "')"; $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); $cash_amt = $pcash + $pcheque; $cred_amt = $pcc; custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Hire Invoice No. {$inv['invnum']}", $cash_amt, "c"); custledger($cus["cusnum"], $cred_acc, $sdate, $inv["invnum"], "Payment for Hire Invoice No. {$inv['invnum']}", $cred_amt, "c"); custCT($amt, $cus["cusnum"], $sdate); //recordCT($amt, $cus['cusnum'],$inv['age'],$sdate); } if (!isset($invids[$key])) { $invids[$key] = 0; } if (!isset($rinvids)) { $rinvids = 0; } if (!isset($amounts)) { $amounts = 0; } if (!isset($invprds)) { $invprds = 0; } if (!isset($rages)) { $rages = 0; } if (!isset($invidsers)) { $invidsers = 0; } $rinvids .= "|{$invids[$key]}"; $amounts .= "|{$amt}"; $invprds .= "|0"; $rages .= "|{$inv['age']}"; $invidsers .= " - {$inv['invnum']}"; $sql = "SELECT * FROM core.accounts WHERE topacc='6400' AND accnum='000'"; $acc_rslt = db_exec($sql); $deptacc = pg_fetch_result($acc_rslt, 0); if ((double) $pcash) { writetrans($bank_acc, $deptacc, $sdate, $refnum, $pcash, "Payment for Invoice {$inv['invnum']} from customer {$cus['cusname']} {$cus['surname']}"); } if ((double) $pcc) { $sql = "SELECT accid FROM core.accounts WHERE topacc='7300' AND accnum='000'"; $acc_rslt = db_exec($sql); $accid = pg_fetch_result($acc_rslt, 0); writetrans($accid, $deptacc, $sdate, $refnum, $pcc, "Payment for Invoice {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"); } if ((double) $pcheque) { $sql = "SELECT accid FROM core.accounts WHERE topacc='7200' AND accnum='000'"; $acc_rslt = db_exec($sql); $accid = pg_fetch_result($acc_rslt, 0); writetrans($accid, $deptacc, $sdate, $refnum, $pcheque, "Payment for Invoice {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"); } db_conn('cubit'); pglib_transaction("COMMIT"); $_POST["pcc"] = $_POST["pcheque"] = $_POST["pcash"] = "0.00"; return cdetails($_POST, "<li class='err'>Payment received successfully</li>"); }
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_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"]); }