function slctacc($_POST) { extract($_POST); # validate input require_lib("validate"); $v = new validate(); $v->isOk($year, "string", 3, 4, "Invalid year."); # display errors, if any if ($v->isError()) { $confirm = $v->genErrors(); $confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>"; return $confirm; } db_conn('core'); $Sl = "SELECT * FROM year WHERE yrdb='{$year}'"; $Ri = db_exec($Sl) or errDie("Unable to get data."); $yd = pg_fetch_array($Ri); global $PRDMON; $fprds = finMonList("fprd", $PRDMON[1], false, $yd["yrname"]); $tprds = finMonList("tprd", PRD_DB, false, $yd["yrname"]); db_connect(); $sql = "SELECT * FROM stock WHERE div = '" . USER_DIV . "' ORDER BY stkdes ASC"; $stkRslt = db_exec($sql) or errDie("Could not retrieve Stock Information from the Database.", SELF); if (pg_numrows($stkRslt) < 1) { return "<li class='err'> There are no Stock Items in Cubit.</li>"; } $stks = "<select name=stkids[] multiple size=10>"; while ($stk = pg_fetch_array($stkRslt)) { $stks .= "<option value='{$stk['stkid']}'>{$stk['stkcod']} {$stk['stkdes']}</option>"; } $stks .= "</select>"; $slctacc = "\n\t\t<p>\n\t\t<h3>Inventory Ledger</h3>\n\t\t<h4>Select Options</h4>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t<input type='hidden' name='key' value='viewtran'>\n\t\t\t<input type='hidden' name='year' value='{$year}'>\n\t\t\t<input type='hidden' name='fprd' value='{$fprd}'>\n\t\t\t<input type='hidden' name='tprd' value='{$tprd}'>\n\t\t\t<tr>\n\t\t\t\t<th>Field</th>\n\t\t\t\t<th>Value</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td valign='top'>Stock Items</td>\n\t\t\t\t<td><input type='radio' name='accnt' value='slct' checked='yes'>Selected Items | <input type='radio' name='accnt' value='all'>All Items</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td valign='top'>Select Stock Item(s)</td>\n\t\t\t\t<td>{$stks}</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Select period</td>\n\t\t\t\t<td>{$fprds} to {$tprds}</td>\n\t\t\t</tr>\n\t\t\t<tr><td><br></td></tr>\n\t\t\t<tr>\n\t\t\t\t<td align='center'></td>\n\t\t\t\t<td align='right'><input type='submit' value='Continue »'></td>\n\t\t\t</tr>\n\t\t</table>"; return $slctacc; }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($invid, "num", 1, 20, "Invalid invoice selection."); if ($v->isError()) { return edit($v->genErrors()); } pglib_transaction("BEGIN"); $subtotal = 0; if (isset($unitprices) && is_array($unitprices)) { foreach ($unitprices as $id => $unitprice) { $sql = "SELECT qty FROM cubit.inv_items WHERE id='{$id}'"; $qty_rslt = db_exec($sql) or errDie("Unable to retrieve qty."); $qty = pg_fetch_result($qty_rslt, 0); $sql = "\n\t\t\tUPDATE cubit.inv_items SET unitcost='{$unitprice}'\n\t\t\tWHERE id='{$id}'"; db_exec($sql) or errDie("Unable to update unit cost."); $subtotal += $unitprice * $qty; } } $vat = $subtotal / 100 * 14; $total = $subtotal + $vat; $sql = "\n\tUPDATE cubit.invoices SET total='{$total}', subtot='{$subtotal}', vat='{$vat}'\n\tWHERE invid='{$invid}'"; db_exec($sql) or errDie("Unable to update invoice totals."); pglib_transaction("COMMIT"); return edit(); }
function view() { extract($_GET); require_lib("validate"); $v = new validate(); $v->isOk($empnum, "num", 1, 20, "Invalid employee number."); $v->isOk($id, "num", 1, 20, "Invalid payslip number."); if ($v->isError()) { $confirmCust = $v->genErrors() . "<br><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>"; return $confirmCust; } if (($emp = qryEmployee($empnum, "*")) === false) { $emp = qryLEmployee($empnum, "*"); } if (isset($rev)) { $tbl = "salr"; } else { $tbl = "salpaid"; } $sql = "SELECT * FROM cubit.{$tbl} WHERE empnum='{$empnum}' AND id = '{$id}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to read employee salary details from Cubit."); if (pg_numrows($rslt) < 1) { return "<li>Employee payment not found.</li>"; } $pay = pg_fetch_array($rslt); # Calculate gross salary from nettpay $gross = $pay['salary'] - $pay['totallow'] - $pay['comm'] + $pay['totded'] + $pay['uif'] + $pay['paye'] + $pay['loanins']; vsprint($gross); # Layout $slip = "\n\t<table " . TMPL_tblDflts . ">\n\t<tr>\n\t\t<td align='right'><font size='3' color='white'><b>Employee: </b></font></td>\n\t\t<td align='left'><b>{$emp['empnum']}</b></td>\n\t\t<td align='right'><font size='3' color='white'><b>Name: </b></font></td>\n\t\t<td align='left'><b>{$emp['fnames']}</b></td>\n\t\t<td align='right'><font size='3' color='white'><b>Surname: </b></font></td>\n\t\t<td align='left'><b>{$emp['sname']}</b></td>\n\t</tr>\n\t" . TBL_BR . "\n\t</table>\n\t<table " . TMPL_tblDflts . ">\n\t<tr>\n\t\t<th>Description</th>\n\t\t<th>Amount</th>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Gross Basic salary</td>\n\t\t<td align='center'>" . CUR . " {$gross}</td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Commission</td>\n\t\t<td align='center'>" . CUR . " {$pay['comm']}</td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Low or interest-free loan</td>\n\t\t<td align='center'>" . CUR . " {$pay['loanins']}</td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Interest rate charged by company</td>\n\t\t<td align='center'>{$emp['loanint']} %</td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Deductions</td>\n\t\t<td align='center'>" . CUR . " {$pay['totded']}</td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>UIF</td>\n\t\t<td align='center'>" . CUR . " {$pay['uif']}</td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>PAYE</td>\n\t\t<td align='center'>" . CUR . " {$pay['paye']}</td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Allowances</td>\n\t\t<td align='center'>" . CUR . " {$pay['totallow']}</td>\n\t</tr>\n\t" . TBL_BR . "\n\t<tr class='" . bg_class() . "'>\n\t\t<td><h3>Nett Income</h3></td>\n\t\t<td align='center'><b>" . CUR . " {$pay['salary']}</b></td>\n\t</tr>\n\t</table>" . mkQuickLinks(ql("../admin-employee-add.php", "Add Employee")); return $slip; }
function printLea($_POST) { # get vars extract($_POST); # validate input require_lib("validate"); $v = new validate(); $v->isOk($f_day, "num", 1, 2, "Invalid from Date day."); $v->isOk($f_month, "num", 1, 2, "Invalid from Date month."); $v->isOk($f_year, "num", 1, 4, "Invalid from Date Year."); $v->isOk($to_day, "num", 1, 2, "Invalid to Date day."); $v->isOk($to_month, "num", 1, 2, "Invalid to Date month."); $v->isOk($to_year, "num", 1, 4, "Invalid to Date Year."); # mix dates $fromdate = $f_year . "-" . $f_month . "-" . $f_day; $todate = $to_year . "-" . $to_month . "-" . $to_day; $v->isOk($fromdate, "date", 1, 1, "Invalid from date."); $v->isOk($todate, "date", 1, 1, "Invalid to date."); if ($v->isError()) { $err = $v->genErrors(); return $err; } if (isset($empnum)) { $OUTPUT = genslip($empnum, $fromdate, $todate); } else { $OUTPUT = ""; $qry = new dbSelect("employees", "cubit", grp(m("cols", "empnum"), m("where", "div='" . USER_DIV . "'"))); $qry->run(); while ($row = $qry->fetch_array()) { $OUTPUT .= paged(genslip($row["empnum"], $fromdate, $todate)); } } require "../tmpl-print.php"; }
function settings() { extract($_POST); db_conn('cubit'); $err = ""; $save = false; if (isset($from)) { $save = true; require_lib("validate"); $v = new validate(); $v->isOk($sig, "string", 0, 255, "Invalid signature."); $v->isOk($from, "email", 1, 255, "Invalid from e-mail address."); $v->isOk($reply, "email", 0, 255, "Invalid reply e-mail address."); $v->isOk($host, "string", 1, 255, "Invalid smtp server. You need to fill in the SMTP HOST field, you can get this from your ISP.<br>\n\t\t\t\tExamples: smtp.saix.net OR smtp.mweb.co.za"); if ($v->isError()) { $err = $v->genErrors(); } else { $sig = remval($sig); $from = remval($from); $reply = remval($reply); $host = remval($host); $Sl = "SELECT * FROM esettings"; $Ri = db_exec($Sl); if (pg_num_rows($Ri) < 1) { $Sl = "INSERT INTO esettings(sig,fromname,reply,smtp_host,smtp_auth,smtp_user,smtp_pass) VALUES\n\t\t\t\t('{$sig}','{$from}','{$reply}','{$host}','0','','')"; $Ri = db_exec($Sl); } else { $Sl = "UPDATE esettings SET sig='{$sig}',fromname='{$from}',reply='{$reply}',smtp_host='{$host}'"; $Ri = db_exec($Sl); } r2sListRestore("emailsettings"); } } $Sl = "SELECT * FROM esettings"; $Ri = db_exec($Sl); $sd = pg_fetch_array($Ri); if (!$save) { $ex = "<li class=err>Please set your email settings & then click 'Update'</li>"; } else { $ex = "<li class=err>Email settings saved</li>"; } if (pg_num_rows($Ri) < 1) { $sd['sig'] = ""; $sd['fromname'] = ""; $sd['reply'] = ""; $sd['smtp_host'] = "smtp.saix.net"; $exx = "<li class=err>These are default settings. If these settings do not work, contact your ISP for correct details.</li>"; } else { $exx = ""; } $sd = array_merge($sd, $_POST); if (!isset($retdata)) { $retdata = ""; } $out = "<h3>Email Settings</h3>\n\t{$exx}\n\t{$ex}\n\t<br />\n\t{$err}\n\t<table " . TMPL_tblDflts . ">\n\t<form action='" . SELF . "' method=post>\n\t{$retdata}\n\t<tr>\n\t\t<th colspan='2'>Settings</th>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td align='center' colspan='2'><b>An asterisk (" . REQ . ") symbol marks required fields.</b></td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Signature</td>\n\t\t<td><input type='text' size='25' name='sig' value='{$sd['sig']}'></td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>" . REQ . " From E-mail Address</td>\n\t\t<td><input type='text' size='25' name='from' value='{$sd['fromname']}'></td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Reply To E-mail Address</td>\n\t\t<td><input type='text' size='25' name='reply' value='{$sd['reply']}'></td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>" . REQ . " SMTP Server</td>\n\t\t<td><input type='text' size='25' name='host' value='{$sd['smtp_host']}'></td>\n\t</tr>\n\t<tr>\n\t\t<td colspan=2 align=right><input type=submit value='Update »'></td>\n\t</tr>\n\t</form>\n\t</table>\n\t<p>\n\t<table boder=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t<tr><th>Quick Links</th></tr>\n\t<tr class='bg-odd'><td><a href='main.php'>Main Menu</a></td></tr>\n\t</table>"; return $out; }
function confirm() { extract($_POST); require_lib("validate"); $v = new validate(); $v->isOk($bankid, "num", 1, 30, "Invalid Bank Account."); if ($v->isError()) { $err = $v->genErrors(); return add($err); } $bank = qryBankAcct($bankid); $OUT = "\n\t<h3>Banking Details Account</h3>\n\t<h4>Confirm entry (Please check the details)</h4>\n\t<form action='" . SELF . "' method='post'>\n\t<table " . TMPL_tblDflts . ">\n\t<input type='hidden' name='key' value='write'>\n\t<input type='hidden' name='bankid' value='{$bankid}'>\n\t<tr>\n\t\t<th>Field</th>\n\t\t<th>Value</th>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Bank</td>\n\t\t<td>{$bank['bankname']}</td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Branch</td>\n\t\t<td>{$bank['branchname']} ({$bank['branchcode']})</td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Account Name</td>\n\t\t<td>{$bank['accname']}</td>\n\t</tr>\n\t<tr class='" . bg_class() . "'>\n\t\t<td>Account Number</td>\n\t\t<td>{$bank['accnum']}</td>\n\t</tr>\n\t" . TBL_BR . "\n\t<tr>\n\t\t<td><input type='submit' name='back' value='« Correction' /></td>\n\t\t<td align='right'><input type='submit' value='Write »' /></td>\n\t</tr>\n\t</table>\n\t</form>" . mkQuickLinks(); return $OUT; }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($id, "num", 1, 9, "Invalid booking selection."); if ($v->isError()) { return enter($v->genErrors()); } $sql = "DELETE FROM hire.bookings WHERE id='{$id}'"; db_exec($sql) or errDie("Unable to retrieve bookings."); $OUTPUT = "<h3>Remove Booking</h3>\r\n\t<table " . TMPL_tblDflts . ">\r\n\t\t<tr>\r\n\t\t\t<th>Remove</th>\r\n\t\t</tr>\r\n\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t<td><li>Booking successfully removed</li></td>\r\n\t\t</tr>\r\n\t</table>"; return $OUTPUT; }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($wh_id, "num", 1, 9, "Invalid store id."); if ($v->isError()) { return enter($v->genErrors()); } $sql = "UPDATE hire.hire_settings SET value='{$wh_id}' WHERE field='wh_id'"; db_exec($sql) or errDie("Unable to save store id."); $OUTPUT = "<h3>Hire Settings</h3>\r\n\t<table " . TMPL_tblDflts . ">\r\n\t\t<tr><th colspan='2'>Write</th></tr>\r\n\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t<td><li>Successfully saved settings.</li></td>\r\n\t\t</tr>\r\n\t</table>"; return $OUTPUT; }
function validate() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($type_name, "string", 1, 255, "Invalid type name."); if ($v->isError()) { return enter($v->genErrors()); } // Make sure we don't have another entry with the same name $sql = "SELECT * FROM cubit.document_types WHERE type_name='{$type_name}'"; $dt_rslt = db_exec($sql) or errDie("Unable to retrieve document types."); if (pg_num_rows($dt_rslt)) { $errmsg = "<li class='err'>A document type with the same name exists.</li>"; return enter($errmsg); } }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($name, "string", 1, 80, "Invalid type name."); $v->isOk($descr, "string", 0, 255, "Invalid description."); if ($v->isError()) { return enter($v->genErrors()); } if ($page_option == "Edit") { $sql = "UPDATE cubit.asset_types SET name='{$name}', description='{$descr}' WHERE id='{$id}'"; } else { $sql = "INSERT INTO cubit.asset_types (name, description) VALUES ('{$name}', '{$descr}')"; } db_exec($sql) or errDie("Unable to save asset type."); $OUTPUT = "\r\n\t\t<h3>{$page_option} Asset Type</h3>\r\n\t\t<form method='post' action='" . SELF . "'>\r\n\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Write</th>\r\n\t\t\t</tr>\r\n\t\t\t<tr class='" . bg_class() . "'>\r\n\t\t\t\t<td>Successfully Saved the Asset Type.</td>\r\n\t\t\t</tr>\r\n\t\t</table>\r\n\t\t</form>"; return $OUTPUT; }
function write() { extract($_POST); require_lib("validate"); $v = new validate(); $v->isOk($salespno, "num", 1, 10, "Invalid Sales Person number."); $v->isOk($salesp, "string", 1, 255, "Invalid Sales Person name."); if ($v->isError()) { return $v->genErrors(); } $cols = grp(m("salespno", $salespno), m("salesp", $salesp), m("com", $com), m("div", USER_DIV)); $qry = new dbUpdate("salespeople", "exten", $cols); $qry->run(DB_INSERT); if ($qry->affected() < 1) { return "<li class=err>Unable to add sales person to Cubit."; } $write = "\n\t<table " . TMPL_tblDflts . ">\n\t<tr>\n\t\t<th>Sales Person added to system</th>\n\t</tr>\n\t<tr class='text'><td>New Sales Person <b>{$salesp}</b>, has been\n\t\tsuccessfully added to the system.</td>\n\t</tr>\n\t</table>"; return $write; }
function confirm() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($title, "string", 1, 255, "Invalid title."); $v->isOk($team_id, "num", 1, 9, "Invalid team selection."); if ($v->isError()) { return enter($v->genErrors()); } // Retrieve team name $sql = "SELECT name FROM crm.teams WHERE id='{$team_id}'"; $team_rslt = db_exec($sql) or errDie("Unable to retrieve team."); $team_name = pg_fetch_result($team_rslt, 0); if (empty($team_name)) { $team_name = "[None]"; } $OUTPUT = "<h3>{$page_option} Main Todo</h3>\n\t<form method='post' action='" . SELF . "'>\n\t" . frmupdate_passon() . "\n\t<input type='hidden' name='key' value='write' />\n\t<input type='hidden' name='page_option' value='{$page_option}' />\n\t<input type='hidden' name='id' value='{$id}' />\n\t<input type='hidden' name='title' value='{$title}' />\n\t<input type='hidden' name='team_id' value='{$team_id}' />\n\t<table cellpadding='2' cellspacing='0' class='shtable'>\n\t\t<tr>\n\t\t\t<th colspan='2'>Confirm</th>\n\t\t</tr>\n\t\t<tr class='odd'>\n\t\t\t<td>Title</td>\n\t\t\t<td>{$title}</td>\n\t\t</tr>\n\t\t<tr class='even'>\n\t\t\t<td>Team Permissions</td>\n\t\t\t<td>{$team_name}</td>\n\t\t</tr>\n\t</table>\n\t<input type='submit' value='Write »' />\n\t</form>"; return $OUTPUT; }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($user_id, "num", 1, 20, "Invalid cubit user selection."); $v->isOk($description, "string", 0, 255, "Invalid person description."); if ($v->isError()) { return enter($v->genErrors()); } if (strtolower($page_option) == "edit") { $sql = "\n\t\tUPDATE project.people SET user_id='{$user_id}', description='{$description}'\n\t\tWHERE id='{$id}'"; db_exec($sql) or errDie("Unable to add person"); } else { $sql = "\n\t\tINSERT INTO project.people (user_id, description)\n\t\tVALUES ('{$user_id}', '{$description}')"; db_exec($sql) or errDie("Unable to add person."); } $OUTPUT = "<h3>{$page_option} Person</h3>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th colspan='2'>Write</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td><li>Successfully saved the person to cubit.</li></td>\n\t\t</tr>\n\t</table>"; return $OUTPUT; }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($name, "string", 1, 255, "Invalid team name."); $v->isOk($description, "string", 1, 255, "Invalid team description."); if ($v->isError()) { return enter($v->genErrors()); } if (strtolower($page_option) == "edit") { $sql = "\n\t\tUPDATE project.teams SET name='{$name}', description='{$description}'\n\t\tWHERE id='{$id}'"; db_exec($sql) or errDie("Unable to update team."); } else { $sql = "\n\t\tINSERT INTO project.teams (name, description)\n\t\tVALUES ('{$name}', '{$description}')"; db_exec($sql) or errDie("Unable to add team."); } $OUTPUT = "\n\t\t\t\t<h3>{$page_option} Project Management Team</h3>\n\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Write</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td><li>Successfully saved the team.</li></td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>"; return $OUTPUT; }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($hd_perc, "num", 1, 9, "Invalid half day rate."); if ($v->isError()) { return enter($v->genErrors()); } $sql = "SELECT value FROM cubit.settings WHERE constant='HD_PERC'"; $rslt = db_exec($sql) or errDie("Unable to retrieve percentage."); if (pg_num_rows($rslt)) { $sql = "\n\t\tUPDATE cubit.settings SET value='{$hd_perc}'\n\t\tWHERE constant='HD_PERC'"; } else { $sql = "\n\t\tINSERT INTO cubit.settings (constant, value)\n\t\t\tVALUES ('HD_PERC', '{$hd_perc}')"; } db_exec($sql) or errDie("Unable to update half day rate."); $msg = "<li class='yay'>Successfully saved half day rate.</li>"; return enter($msg); }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($name, "string", 1, 255, "Invalid name."); $v->isOk($description, "string", 0, 255, "Invalid description."); $v->isOk($extension, "string", 0, 65, "Invalid extension."); if ($v->isError()) { return enter($v->genErrors()); } if (strtolower($page_option) == "edit") { $sql = "UPDATE project.doc_types SET name='{$name}', description='{$description}', extension='{$extension}' WHERE id='{$id}'"; db_exec($sql) or errDie("Unable to update document type."); } else { $sql = "INSERT INTO project.doc_types (name, description, extension) VALUES ('{$name}', '{$description}', '{$extension}')"; db_exec($sql) or errDie("Unable to add document type."); } $OUTPUT = "\n\t\t<h3>{$page_option} Project Document Type</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Write</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><li>Successfully added the document type.</li></td>\n\t\t\t</tr>\n\t\t</table>"; return $OUTPUT; }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($cost_acc, "num", 1, 9, "Invalid cost account."); $v->isOk($accdep_acc, "num", 1, 9, "Invalid accumulated depreciation account."); $v->isOk($dep_acc, "num", 1, 9, "Invalid depreciation account."); if ($v->isError()) { return enter($v->genErrors()); } $sql = "\n\tSELECT value FROM cubit.settings\n\tWHERE constant='ASSET_COST_ACCOUNT'"; $cost_rslt = db_exec($sql) or errDie("Unable to retrieve cost setting."); if (pg_num_rows($cost_rslt)) { $sql = "\n\t\tUPDATE cubit.settings SET value='{$cost_acc}'\n\t\tWHERE constant='ASSET_COST_ACCOUNT'"; } else { $sql = "\n\t\tINSERT INTO cubit.settings (constant, value)\n\t\t\tVALUES ('ASSET_COST_ACCOUNT', '{$cost_acc}')"; } db_exec($sql) or errDie("Unable to update cost setting."); $sql = "\n\tSELECT value FROM cubit.settings\n\tWHERE constant='ASSET_ACCDEP_ACCOUNT'"; $accdep_rslt = db_exec($sql) or errDie("Unable to retrieve accumulated depreciation setting."); if (pg_num_rows($accdep_rslt)) { $sql = "\n\t\tUPDATE cubit.settings SET value='{$accdep_acc}'\n\t\tWHERE constant='ASSET_ACCDEP_ACCOUNT'"; } else { $sql = "\n\t\tINSERT INTO cubit.settings (constant, value)\n\t\t\tVALUES ('ASSET_ACCDEP_ACCOUNT', '{$accdep_acc}')"; } db_exec($sql) or errDie("Unable to update accumulated depreciation setting."); $sql = "\n\tSELECT value FROM cubit.settings\n\tWHERE constant='ASSET_DEP_ACCOUNT'"; $dep_rslt = db_exec($sql) or errDie("Unable to retrieve depreciation setting."); if (pg_num_rows($dep_rslt)) { $sql = "\n\t\tUPDATE cubit.settings SET value='{$dep_acc}'\n\t\tWHERE constant='ASSET_DEP_ACCOUNT'"; } else { $sql = "\n\t\tINSERT INTO cubit.settings (constant, value)\n\t\t\tVALUES ('ASSET_DEP_ACCOUNT', '{$dep_acc}')"; } db_exec($sql) or errDie("Unable to update depreciation setting."); $msg = "<li class='err'>Successfully updated asset settings.</li>"; return enter($msg); }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); $v->isOk($how, "string", 0, 255, "Invalid how."); if ($v->isError()) { return display($v->genErrors()); } $msg = ""; if (isset($remove)) { foreach ($remove as $id => $value) { $sql = "DELETE FROM cubit.dispatch_how WHERE id='{$value}'"; db_exec($sql) or errDie("Unable to remove how."); } } if (!empty($how)) { $sql = "INSERT INTO cubit.dispatch_how (name) VALUES ('{$how}')"; db_exec($sql) or errDie("Unable add how."); $msg .= "<li class='yay'>Successfully added {$how}</li>"; } return display($msg); }
function write($_POST) { extract($_POST); require_lib("validate"); $v = new validate(); $v->isOk($id, "num", 1, 255, "Invalid group id."); if ($v->isError()) { return enter($_POST, $v->genErrors()); } $get_grp = "SELECT grouptitle FROM egroups WHERE id = '{$id}' LIMIT 1"; $run_grp = db_exec($get_grp) or errDie("Unable to get email group information (0)"); if (pg_numrows($run_grp) < 1) { #no group found ??? return confirm($_POST, "<li class='err'>Email group not found.</li>"); } $gtitle = pg_fetch_result($run_grp, 0, 0); $write_sql = "DELETE FROM egroups WHERE id = '{$id}'"; $run_write = db_exec($write_sql) or errDie("Unable to remove group information."); $write_sql2 = "DELETE FROM email_groups WHERE email_group = '{$gtitle}'"; $run_write2 = db_exec($write_sql2) or errDie("Unable to remove email group email addresses."); $OUTPUT = "<h3>Write Group</h3>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th>Write</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td><li>Successfully removed the group.</li></td>\n\t\t</tr>\n\t</table>"; return $OUTPUT; }
function details($_GET) { extract($_GET); $ids = explode(",", $ids); # validate input require_lib("validate"); $v = new validate(); foreach ($ids as $invid) { $v->isOk($invid, "num", 1, 20, "Invalid recuring invoice number."); } # display errors, if any if ($v->isError()) { $err = $v->genErrors(); $confirm = "{$err}<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>"; return $confirm; } /* --- Start Display --- */ $printInv = "\n\t<h3>Confirm Invoice Printing Process</h3>\n\t<form action='" . SELF . "' method='POST'>\n\t\t<input type='hidden' name='key' value='process'>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<td colspan='6' class='err'>Please Note : This process might take long depending on the number of invoices. It is best to run it overnight.</td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Invoice No.</th>\n\t\t\t<th>Invoice Date</th>\n\t\t\t<th>Customer Name</th>\n\t\t\t<th>Grand Total</th>\n\t\t</tr>"; $i = 0; foreach ($ids as $key => $invid) { # Get recuring invoice info db_connect(); $sql = "SELECT * FROM nons_invoices WHERE invid = '{$invid}' AND div = '" . USER_DIV . "' AND done!='y'"; $invRslt = db_exec($sql) or errDie("Unable to get recuring invoice information"); if (pg_numrows($invRslt) < 1) { return "<i class='err'>Invoice Not Found, Please make sure you have selected a unprinted invoice.</i>"; } $inv = pg_fetch_array($invRslt); $bgColor = bgcolor($i); $inv['total'] = sprint($inv['total']); $inv['balance'] = sprint($inv['balance']); $printInv .= "\n\t\t<input type=hidden name='invids[]' value='{$inv['invid']}'>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>T {$inv['invid']}</td>\n\t\t\t<td valign='center'>{$inv['odate']}</td>\n\t\t\t<td>{$inv['cusname']}</td>\n\t\t\t<td align=right>" . CUR . " {$inv['total']}</td>\n\t\t</tr>"; } $bgColor = bgcolor($i); $printInv .= "<tr class='" . bg_class() . "'><td colspan=6 align=right>Totals Invoices : {$i}</td></tr>\n\t<tr><td><br></td></tr>\n\t<tr><td colspan=6 align=right><input type=submit value='Process >>'></td></tr>\n\t</form></table>\n\t<p>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t<tr><th>Quick Links</th></tr>\n\t<tr class='bg-odd'><td><a href='rec-nons-invoice-new.php'>New Recurring Non-stock Invoice</a></td></tr>\n\t<tr class='bg-odd'><td><a href='rec-nons-invoice-view.php'>View Recurring Non-stock Invoices</a></td></tr>\n\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>"; return $printInv; }
function write($_POST) { extract($_POST); require_lib("validate"); $v = new validate(); $v->isOk($group, "string", 1, 255, "Invalid group name."); if ($v->isError()) { return enter($_POST, $v->genErrors()); } #clean and prepare vars $grouptitle = strtolower($group); $grouptitle = str_replace("'", "", $grouptitle); $grouptitle = str_replace("\\", "", $grouptitle); $grouptitle = str_replace("|", "", $grouptitle); $grouptitle = str_replace("@", "", $grouptitle); $grouptitle = str_replace("!", "", $grouptitle); $grouptitle = str_replace("?", "", $grouptitle); $grouptitle = str_replace("%", "", $grouptitle); $grouptitle = str_replace(" ", "", $grouptitle); $write_sql = "INSERT INTO egroups (grouptitle,groupname) VALUES ('{$grouptitle}','{$group}')"; $run_write = db_exec($write_sql) or errDie("Unable to add group information."); $OUTPUT = "<h3>Write Group</h3>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th>Write</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td><li>Successfully saved the group.</li></td>\n\t\t</tr>\n\t</table>"; return $OUTPUT; }
function validate($data) { extract($data); require_lib("validate"); $v = new validate(); $v->isOk($pricelist, "num", 1, 20, "Invalid pricelist selection."); $v->isOk($category, "num", 1, 20, "Invalid category selection."); $v->isOk($classification, "num", 1, 20, "Invalid classification selection."); $v->isOk($increase, "float", 1, 20, "Invalid increase percentage."); $v->isOk($decrease, "float", 1, 20, "Invalid decrease percentage."); if (is_numeric($pricelist) && $pricelist) { $sql = "SELECT listid FROM exten.pricelist WHERE listid='{$pricelist}'"; $pricelist_rslt = db_exec($sql) or errDie("Unable to retrieve pricelist."); if (!pg_num_rows($pricelist_rslt)) { $v->addError("", "Selected pricelist does not exist."); } } if (is_numeric($category) && $category) { $sql = "SELECT catid FROM cubit.stockcat WHERE catid='{$category}'"; $category_rslt = db_exec($sql) or errDie("Unable to retrieve category."); if (!pg_num_rows($category_rslt)) { $v->addError("", "Selected category does not exist."); } } if (is_numeric($classification) && $classification) { $sql = "SELECT clasid FROM cubit.stockclass WHERE clasid='{$classification}'"; $classification_rslt = db_exec($sql) or errDie("Unable to retrieve classification."); if (!pg_num_rows($classification_rslt)) { $v->addError("", "Selected classification does not exist."); } } if ($v->isError()) { return enter($v->genErrors()); } return true; }
function confirm($_POST) { # Get vars extract($_POST); if (isset($back)) { header("Location: cashbook-entry.php"); exit; } # validate input require_lib("validate"); $v = new validate(); $v->isOk($bankid, "num", 1, 30, "Invalid Bank Account."); $v->isOk($o_day, "num", 1, 2, "Invalid Date day."); $v->isOk($o_month, "num", 1, 2, "Invalid Date month."); $v->isOk($o_year, "num", 1, 4, "Invalid Date Year."); $v->isOk($name, "string", 1, 255, "Invalid Person/Business paid to/received from."); $v->isOk($descript, "string", 0, 255, "Invalid Description."); $v->isOk($reference, "string", 0, 255, "Invalid Description."); $v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number."); $v->isOk($amount, "float", 1, 10, "Invalid amount."); $v->isOk($chrgvat, "string", 1, 4, "Invalid vat option."); $v->isOk($accinv, "num", 1, 20, "Invalid Account involved."); $date = mkdate($o_year, $o_month, $o_day); $v->isOk($date, "date", 1, 1, "Invalid date."); if ($v->isError()) { $err = $v->genErrors(); return $err . add($_POST); } # Start rattling vat $totamt = $amount; vsprint($vat); if ($vatcode != $orig_vatcode || $amount != $orig_amount || $chrgvat != $orig_chrgvat) { db_conn('cubit'); $Sl = "SELECT * FROM vatcodes WHERE id='{$vatcode}'"; $Ri = db_exec($Sl) or errDie("Unable to get vat codes"); $vd = pg_fetch_array($Ri); $vatp = $vd['vat_amount']; if ($chrgvat == "exc") { $vat = sprint($vatp / 100 * $amount); } else { if ($chrgvat == "inc") { $vat = sprint($amount * $vatp / ($vatp + 100)); } else { $vat = 0; } } } if ($chrgvat == "exc") { $totamt += $vat; $vatin = CUR . "<input type='text' name='vat' value='{$vat}' />"; } else { if ($chrgvat == "inc") { $vatin = CUR . "<input type='text' name='vat' value='{$vat}' />"; } else { $vatin = "No VAT"; } } $OUT = "\n\t\t\t<center>\n\t\t\t<h3>Edit Bank Receipt</h3>\n\t\t\t<h4>Confirm entry (Please check the details)</h4>\n\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<form action='" . SELF . "' method='POST'>\n\t\t\t\t<input type='hidden' name='key' value='write' />\n\t\t\t\t<input type='hidden' name='id' value='{$id}' />\n\t\t\t\t<input type='hidden' name='bankid' value='{$bankid}' />\n\t\t\t\t<input type='hidden' name='date' value='{$date}' />\n\t\t\t\t<input type='hidden' name='name' value='{$name}' />\n\t\t\t\t<input type='hidden' name='descript' value='{$descript}' />\n\t\t\t\t<input type='hidden' name='reference' value='{$reference}' />\n\t\t\t\t<input type='hidden' name='cheqnum' value='{$cheqnum}' />\n\t\t\t\t<input type='hidden' name='amount' value='{$amount}' />\n\t\t\t\t<input type='hidden' name='chrgvat' value='{$chrgvat}' />\n\t\t\t\t<input type='hidden' name='accinv' value='{$accinv}' />\n\t\t\t\t<input type='hidden' name='vatcode' value='{$vatcode}' />"; # Get bank account name db_connect(); $sql = "SELECT accname,bankname FROM bankacct WHERE bankid = '{$bankid}' AND div = '" . USER_DIV . "'"; $bankRslt = db_exec($sql); $bank = pg_fetch_array($bankRslt); # get hook account number core_connect(); $sql = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF); # check if link exists if (pg_numrows($rslt) < 1) { return "<li class=err> ERROR : The bank account that you selected doesn't appear to have an account linked to it."; } $banklnk = pg_fetch_array($rslt); # Get bank balance $sql = "SELECT (debit - credit) as bal FROM trial_bal WHERE period='" . getPRDDB($date) . "' AND accid = '{$banklnk['accnum']}' AND div = '" . USER_DIV . "'"; $brslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF); $bal = pg_fetch_array($brslt); $accRslt = get("core", "accname,topacc,accnum", "accounts", "accid", $accinv); $accnt = pg_fetch_array($accRslt); $OUT .= "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Field</th>\n\t\t\t\t\t\t<th>Value</th>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Account</td>\n\t\t\t\t\t\t<td>{$bank['accname']} - {$bank['bankname']}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Account Balance</td>\n\t\t\t\t\t\t<td>" . CUR . " {$bal['bal']}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Date</td>\n\t\t\t\t\t\t<td valign='center'>{$date}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Received from</td>\n\t\t\t\t\t\t<td valign='center'>{$name}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Description</td>\n\t\t\t\t\t\t<td valign='center'>{$descript}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Reference</td>\n\t\t\t\t\t\t<td valign='center'>{$reference}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Cheque Number</td>\n\t\t\t\t\t\t<td valign='center'>{$cheqnum}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t<td>Amount</td>\n\t\t\t\t\t\t<td valign='center'>" . CUR . " " . sprint($totamt) . "</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='bg-odd'>\n\t\t\t\t\t\t<td>VAT </td>\n\t\t\t\t\t\t<td>{$vatin}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t\t<tr class='bg-even'>\n\t\t\t\t\t\t<td>Ledger Account Received from</td>\n\t\t\t\t\t\t<td valign='center'>{$accnt['topacc']}/{$accnt['accnum']} - {$accnt['accname']}</td>\n\t\t\t\t\t</tr>\n\t\t\t\t \t" . TBL_BR . "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td> </td>\n\t\t\t\t\t\t<td align='right'><input type='submit' value='Write »'></td>\n\t\t\t\t\t</tr>\n\t\t\t\t</form>\n\t\t\t\t</table>" . mkQuickLinks(); return $OUT; }
function write($_POST) { extract($_POST); if (isset($back)) { unset($_POST["back"]); return alloc($_POST); } # CHECK IF THIS DATE IS IN THE BLOCKED RANGE $blocked_date_from = getCSetting("BLOCKED_FROM"); $blocked_date_to = getCSetting("BLOCKED_TO"); require_lib("validate"); $v = new validate(); $v->isOk($all, "num", 1, 1, "Invalid allocation."); $v->isOk($rec_amount, "num", 1, 5, "Invalid amount of entries."); for ($t = 0; $t < $rec_amount; $t++) { if (!isset($descript[$t]) or !isset($reference[$t]) or !isset($setamt[$t]) or empty($descript[$t]) or empty($reference[$t]) or empty($setamt[$t])) { continue; } $v->isOk($bankid[$t], "num", 1, 30, "Invalid Bank Account."); $v->isOk($date[$t], "date", 1, 14, "Invalid Date."); $v->isOk($out[$t], "float", 1, 40, "Invalid out amount."); $v->isOk($descript[$t], "string", 0, 255, "Invalid Description."); $v->isOk($reference[$t], "string", 0, 50, "Invalid Reference Name/Number."); $v->isOk($cheqnum[$t], "num", 0, 30, "Invalid Cheque number."); $v->isOk($amt[$t], "float", 1, 40, "Invalid amount."); $v->isOk($setamt[$t], "float", 1, 40, "Invalid Settlement amount."); $v->isOk($setvat[$t], "string", 1, 10, "Invalid Settlement VAT Option."); $v->isOk($setvatcode[$t], "string", 1, 40, "Invalid Settlement VAT code"); $v->isOk($cusid[$t], "num", 1, 40, "Invalid customer number."); $v->isOk($out1[$t], "float", 0, 40, "Invalid paid amount(currant)."); $v->isOk($out2[$t], "float", 0, 40, "Invalid paid amount(30)."); $v->isOk($out3[$t], "float", 0, 40, "Invalid paid amount(60)."); $v->isOk($out4[$t], "float", 0, 40, "Invalid paid amount(90)."); $v->isOk($out5[$t], "float", 0, 40, "Invalid paid amount(120)."); if (isset($invids[$t])) { foreach ($invids[$t] as $key => $value) { $v->isOk($invids[$t][$key], "num", 1, 50, "Invalid Invoice No."); $v->isOk($paidamt[$t][$key], "float", 1, 40, "Invalid amount to be paid."); } } if (strtotime($date[$t]) >= strtotime($blocked_date_from) and strtotime($date[$t]) <= strtotime($blocked_date_to) and !user_is_admin(USER_ID)) { return "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>"; } } if ($v->isError()) { $confirm = $v->genErrors(); return $confirm . confirm($_POST); } for ($t = 0; $t < $rec_amount; $t++) { if (!isset($descript[$t]) or !isset($reference[$t]) or !isset($setamt[$t]) or empty($descript[$t]) or empty($reference[$t]) or empty($setamt[$t])) { continue; } /* get bank account id */ if (($bank_acc[$t] = getbankaccid($bankid[$t])) === false) { $sql = "SELECT accid FROM core.accounts WHERE accname='Cash on Hand'"; $rslt = db_exec($sql); if (pg_num_rows($rslt) < 1) { if ($bankid[$t] == 0) { return "There is no 'Cash on Hand' account, there was one, but\n\t\t\t\t\t\t**s not there now, you mudst have deleted it, if you want\n\t\t\t\t\t\tto use cash functionality please create a 'Cash on Hand' account."; } else { return "Invalid bank acc."; } } $bank_acc[$t] = pg_fetch_result($rslt, 0); } $cus = qryCustomer($cusid[$t], "cusnum, deptid, cusname, surname"); $dept = qryDepartment($cus["deptid"], "debtacc"); $refnum = getrefnum(); pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF); # date format $sdate[$t] = explode("-", $date[$t]); $sdate[$t] = $sdate[$t][2] . "-" . $sdate[$t][1] . "-" . $sdate[$t][0]; $cheqnum[$t] = 0 + $cheqnum[$t]; $pay = ""; $accdate[$t] = $sdate[$t]; /* Paid invoices */ $invidsers = ""; $rinvids = ""; $amounts = ""; $invprds = ""; $rages = ""; /* OPTION 1 : AUTO ALLOCATE (write) */ if ($all == 0) { # update the customer (make balance less) $sql = "UPDATE cubit.customers SET balance = (balance - '{$amt[$t]}'::numeric(13,2))\n\t\t\t\t\tWHERE cusnum = '{$cus['cusnum']}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF); if (isset($invids[$t])) { foreach ($invids[$t] as $key => $value) { $ii = $invids[$t][$key]; $pp = $paidamt[$t][$key]; /* OPTION 1: STOCK INVOICES */ if (!isset($itype[$t][$ii]) && !isset($ptype[$t][$ii])) { $sql = "SELECT prd,invnum,odate FROM cubit.invoices\n\t\t\t\t\t\t\t\tWHERE invid ='{$ii}' AND div = '" . USER_DIV . "'"; $invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database."); if (pg_numrows($invRslt) < 1) { return "<li class='err'>Invalid Invoice Number.</li>"; } $inv = pg_fetch_array($invRslt); $inv['invnum'] += 0; // reduce invoice balance $sql = "UPDATE cubit.invoices \n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2))\n\t\t\t\t\t\t\t\tWHERE invid = '{$ii}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); $sql = "UPDATE cubit.open_stmnt \n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2))\n\t\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); # record the payment on the statement $sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', '" . ($pp - $pp * 2) . "', '{$sdate[$t]}', \n\t\t\t\t\t\t\t\t'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); custledger($cus['cusnum'], $bank_acc[$t], $sdate[$t], $inv['invnum'], "Payment for Invoice No. {$inv['invnum']}", $paidamt[$t][$key], "c"); $rinvids .= "|{$invids[$t]}[{$key}]"; $amounts .= "|{$pp}"; if ($inv['prd'] == "0") { $inv['prd'] = PRD_DB; } $invprds .= "|{$inv['prd']}"; $rages .= "|0"; $invidsers .= " - {$inv['invnum']}"; /* OPTION 1: NONS STOCK INVOICES */ } else { if (!isset($ptype[$t][$ii])) { $sql = "SELECT prd,invnum,descrip,age,odate FROM cubit.nons_invoices\n\t\t\t\t\t\t\t\tWHERE invid ='{$ii}' AND div = '" . USER_DIV . "'"; $invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database."); if (pg_numrows($invRslt) < 1) { return "<li class='err'>Invalid Invoice Number.</li>"; } $inv = pg_fetch_array($invRslt); $inv['invnum'] += 0; # reduce the money that has been paid $sql = "UPDATE cubit.nons_invoices\n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2))\n\t\t\t\t\t\t\t\tWHERE invid = '{$ii}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); $sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2))\n\t\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); # record the payment on the statement $sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', '" . ($pp - $pp * 2) . "', '{$sdate[$t]}', \n\t\t\t\t\t\t\t\t'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); custledger($cus['cusnum'], $bank_acc[$t], $sdate[$t], $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}", $paidamt[$t][$key], "c"); recordCT($pp, $cus['cusnum'], $inv['age'], $accdate[$t]); $rinvids .= "|{$ii}"; $amounts .= "|{$pp}"; $invprds .= "|0"; $rages .= "|{$inv['age']}"; $invidsers .= " - {$inv['invnum']}"; } else { /* pos invoices */ $sqls = array(); for ($i = 1; $i <= 12; ++$i) { $sqls[] = "SELECT '{$i}' AS prd,invid,invnum,odate FROM \"{$i}\".pinvoices \n\t\t\t\t\t\t\t\t\tWHERE invid='{$ii}' AND div='" . USER_DIV . "'"; } $sql = implode(" UNION ", $sqls); $invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database."); if (pg_numrows($invRslt) < 1) { return "<li class='err'>Invalid Invoice Number.</li>"; } $inv = pg_fetch_array($invRslt); // reduce the invoice balance $sql = "UPDATE \"{$inv['prd']}\".pinvoices \n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2)) \n\t\t\t\t\t\t\t\tWHERE invid = '{$ii}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); $sql = "UPDATE cubit.open_stmnt \n\t\t\t\t\t\t\t\tSET balance = (balance - {$pp}::numeric(13,2)) \n\t\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); # record the payment on the statement $sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, date, type, div, allocation_date\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}','{$inv['invnum']}', '" . ($pp - $pp * 2) . "','{$sdate[$t]}', 'Payment for Non Stock Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); custledger($cus['cusnum'], $bank_acc[$t], $sdate[$t], $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']}", $paidamt[$t][$key], "c"); recordCT($paidamt[$t][$key], $cus['cusnum'], 0, $accdate[$t]); $rinvids .= "|{$invids[$t]}[{$key}]"; $amounts .= "|{$paidamt[$t]}[{$key}]"; $invprds .= "|{$inv['prd']}"; //$rages .= "|$inv[age]"; $invidsers .= " - {$inv['invnum']}"; } } } } $cols = grp(m("bankid", $bankid[$t]), m("trantype", "deposit"), m("date", $sdate[$t]), m("name", "{$cus['cusname']} {$cus['surname']}"), m("descript", "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"), m("cheqnum", $cheqnum[$t]), m("amount", $amt[$t]), m("banked", "no"), m("accinv", $dept["debtacc"]), m("cusnum", $cus["cusnum"]), m("rinvids", $rinvids), m("amounts", $amounts), m("invprds", $invprds), m("rages", $rages), m("reference", $reference[$t]), m("div", USER_DIV)); $dbobj = new dbUpdate("cashbook", "cubit", $cols); $dbobj->run(DB_INSERT); $dbobj->free(); /* $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, cusnum, rinvids, amounts, invprds, rages, reference, div) VALUES ('$bankid', 'deposit', '$sdate', '$cus[cusname] $cus[surname]', '', '$cheqnum', '$amt', 'no', '$dept[debtacc]', '$cus[cusnum]', '$rinvids', '$amounts', '$invprds', '$rages', '$reference', '".USER_DIV."')"; $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF); */ $refnum = getrefnum($accdate[$t]); writetrans($bank_acc[$t], $dept['debtacc'], $accdate[$t], $refnum, $amt[$t], "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"); db_conn('cubit'); if ($out > 0) { /* START OPEN ITEMS */ $openstmnt = new dbSelect("open_stmnt", "cubit", grp(m("where", "balance>0 AND cusnum='{$cusid[$t]}'"), m("order", "date"))); $openstmnt->run(); $open_out[$t] = $out[$t]; $i = 0; $ox = ""; while ($od = $openstmnt->fetch_array()) { if ($open_out[$t] == 0) { continue; } $oid = $od['id']; if ($open_out[$t] >= $od['balance']) { $open_amount[$t][$oid] = $od['balance']; $open_out[$t] = sprint($open_out[$t] - $od['balance']); $ox .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><input type='hidden' size='20' name='open[{$t}][{$oid}]' value='{$oid}'>{$od['type']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t\t\t<td><input type='hidden' name='open_amount[{$t}][{$oid}]' value='{$open_amount[$t]}[{$oid}]'>" . CUR . " {$open_amount[$t]}[{$oid}]</td>\n\t\t\t\t\t\t\t</tr>"; $Sl = "UPDATE cubit.open_stmnt SET balance=balance-'" . $open_amount[$t][$oid] . "' WHERE id='{$oid}'"; $Ri = db_exec($Sl) or errDie("Unable to update statement."); } elseif ($open_out < $od['balance']) { $open_amount[$t][$oid] = $open_out[$t]; $open_out = 0; $ox .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><input type='hidden' size='20' name='open[{$t}][{$oid}]' value='{$od['id']}'>{$od['type']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t\t\t<td><input type='hidden' name='open_amount[{$t}][{$oid}]' value='{$open_amount[$t]}[{$oid}]'>" . CUR . " {$open_amount[$t]}[{$oid}]</td>\n\t\t\t\t\t\t\t</tr>"; $Sl = "UPDATE cubit.open_stmnt SET balance=balance-'" . $open_amount[$t][$oid] . "' WHERE id='{$oid}'"; $Ri = db_exec($Sl) or errDie("Unable to update statement."); } $i++; } if (open()) { $bout[$t] = $out[$t]; $out[$t] = $open_out[$t]; if ($out > 0) { $sql = "\n\t\t\t\t\t\t\tINSERT INTO cubit.open_stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, balance, date, \n\t\t\t\t\t\t\t\ttype, st, div\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$cus['cusnum']}', '0', '-{$out[$t]}', '-{$out[$t]}', '{$sdate[$t]}', \n\t\t\t\t\t\t\t\t'Payment Received', 'n', '" . USER_DIV . "'\n\t\t\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF); //$confirm .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>"; } $out[$t] = $bout[$t]; } else { //$confirm .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>";} } } if ($out[$t] > 0) { recordCT($out[$t], $cus['cusnum'], 0, $accdate[$t]); $cols = grp(m("cusnum", $cus["cusnum"]), m("invid", 0), m("amount", -$out[$t]), m("date", $sdate[$t]), m("type", "Payment Received"), m("div", USER_DIV), m("allocation_date", $accdate[$t])); $dbobj = new dbUpdate("stmnt", "cubit", $cols); $dbobj->run(DB_INSERT); $dbobj->free(); custledger($cus['cusnum'], $bank_acc[$t], $sdate[$t], "PAYMENT", "Payment received.", $out[$t], "c"); } } /* start moving invoices */ // move invoices that are fully paid $sql = "SELECT * FROM cubit.invoices WHERE balance=0 AND printed = 'y' AND done = 'y' AND div = '" . USER_DIV . "'"; $invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); while ($x = pg_fetch_array($invbRslt)) { if (($prd = $x['prd']) == "0") { $prd = PRD_DB; } // move invoice $cols = grp(m("invid", $x["invid"]), m("invnum", $x["invnum"]), m("deptid", $x["deptid"]), m("cusnum", $x["cusnum"]), m("deptname", $x["deptname"]), m("cusacc", $x["cusacc"]), m("cusname", $x["cusname"]), m("surname", $x["surname"]), m("cusaddr", $x["cusaddr"]), m("cusvatno", $x["cusvatno"]), m("cordno", $x["cordno"]), m("ordno", $x["ordno"]), m("chrgvat", $x["chrgvat"]), m("terms", $x["terms"]), m("traddisc", $x["traddisc"]), m("salespn", $x["salespn"]), m("odate", $x["odate"]), m("delchrg", $x["delchrg"]), m("subtot", $x["subtot"]), m("vat", $x["vat"]), m("total", $x["total"]), m("age", $x["age"]), m("comm", $x["comm"]), m("discount", $x["discount"]), m("delivery", $x["delivery"]), m("docref", $x["docref"]), m("prd", $x["prd"]), m("delvat", $x["delvat"]), m("balance", 0), m("printed", "y"), m("done", "y"), m("username", USER_NAME), m("div", USER_DIV)); $dbobj = new dbUpdate("invoices", $prd, $cols); $dbobj->run(DB_INSERT); $dbobj->free(); // record movement $cols = grp(m("invtype", "inv"), m("invnum", $x["invnum"]), m("prd", $x["prd"]), m("docref", $x["docref"]), m("div", USER_DIV)); $dbobj->setTable("movinv", "cubit"); $dbobj->setOpt($cols); $dbobj->run(); $dbobj->free(); // move invoice items $inv_items = new dbSelect("inv_items", "cubit", grp(m("where", wgrp(m("invid", $x["invid"]), m("div", USER_DIV))))); $inv_items->run(); while ($xi = $inv_items->fetch_array()) { $xi['vatcode'] += 0; $xi['account'] += 0; $xi['del'] += 0; $cols = grp(m("invid", $x["invid"]), m("whid", $xi["whid"]), m("stkid", $xi["stkid"]), m("qty", $xi["qty"]), m("unitcost", $xi["unitcost"]), m("amt", $xi["amt"]), m("disc", $xi["disc"]), m("discp", $xi["discp"]), m("vatcode", $xi["vatcode"]), m("account", $xi["account"]), m("description", $xi["description"]), m("del", $xi["del"]), m("noted", $xi["noted"]), m("serno", $xi["serno"]), m("div", USER_DIV)); $dbobj->setTable("inv_items", $prd); $dbobj->setOpt($cols); $dbobj->run(); $dbobj->free(); } /* remove invoice from cubit schema */ $dbobj = new dbDelete("invoices", "cubit", wgrp(m("invid", $x["invid"]), m("div", USER_DIV))); $dbobj->run(); $dbobj->setTable("inv_items", "cubit"); $dbobj->run(); } /* start moving invoices */ // move invoices that are fully paid $sql = "SELECT * FROM cubit.invoices WHERE balance=0 AND printed = 'y' AND done = 'y' AND div = '" . USER_DIV . "'"; $invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); while ($x = pg_fetch_array($invbRslt)) { if (($prd = $x['prd']) == "0") { $prd = PRD_DB; } // move invoice $cols = grp(m("invid", $x["invid"]), m("invnum", $x["invnum"]), m("deptid", $x["deptid"]), m("cusnum", $x["cusnum"]), m("deptname", $x["deptname"]), m("cusacc", $x["cusacc"]), m("cusname", $x["cusname"]), m("surname", $x["surname"]), m("cusaddr", $x["cusaddr"]), m("cusvatno", $x["cusvatno"]), m("cordno", $x["cordno"]), m("ordno", $x["ordno"]), m("chrgvat", $x["chrgvat"]), m("terms", $x["terms"]), m("traddisc", $x["traddisc"]), m("salespn", $x["salespn"]), m("odate", $x["odate"]), m("delchrg", $x["delchrg"]), m("subtot", $x["subtot"]), m("vat", $x["vat"]), m("total", $x["total"]), m("age", $x["age"]), m("comm", $x["comm"]), m("discount", $x["discount"]), m("delivery", $x["delivery"]), m("docref", $x["docref"]), m("prd", $x["prd"]), m("delvat", $x["delvat"]), m("balance", 0), m("printed", "y"), m("done", "y"), m("username", USER_NAME), m("div", USER_DIV)); $dbobj = new dbUpdate("invoices", $prd, $cols); $dbobj->run(DB_INSERT); $dbobj->free(); // record movement $cols = grp(m("invtype", "inv"), m("invnum", $x["invnum"]), m("prd", $x["prd"]), m("docref", $x["docref"]), m("div", USER_DIV)); $dbobj->setTable("movinv", "cubit"); $dbobj->setOpt($cols); $dbobj->run(); $dbobj->free(); // move invoice items $inv_items = new dbSelect("inv_items", "cubit", grp(m("where", wgrp(m("invid", $x["invid"]), m("div", USER_DIV))))); $inv_items->run(); while ($xi = $inv_items->fetch_array()) { $xi['vatcode'] += 0; $xi['account'] += 0; $xi['del'] += 0; $cols = grp(m("invid", $x["invid"]), m("whid", $xi["whid"]), m("stkid", $xi["stkid"]), m("qty", $xi["qty"]), m("unitcost", $xi["unitcost"]), m("amt", $xi["amt"]), m("disc", $xi["disc"]), m("discp", $xi["discp"]), m("vatcode", $xi["vatcode"]), m("account", $xi["account"]), m("description", $xi["description"]), m("del", $xi["del"]), m("noted", $xi["noted"]), m("serno", $xi["serno"]), m("div", USER_DIV)); $dbobj->setTable("inv_items", $prd); $dbobj->setOpt($cols); $dbobj->run(); $dbobj->free(); } /* remove invoice from cubit schema */ $dbobj = new dbDelete("invoices", "cubit", wgrp(m("invid", $x["invid"]), m("div", USER_DIV))); $dbobj->run(); $dbobj->setTable("inv_items", "cubit"); $dbobj->run(); } #do journal for the settlement discount here ... now ... if ($setamt[$t] > 0) { db_conn('core'); #get settlement accid $get_setacc = "SELECT accid FROM accounts WHERE accname = 'Debtors Settlement Discount'"; $run_setacc = db_exec($get_setacc) or errDie("Unable to get settlement account information"); $setaccid = pg_fetch_result($run_setacc, 0, 0); #calculate the settlement vat ... and amt if (isset($setvat[$t]) and $setvat[$t] == 'inc') { db_connect(); $get_vcode = "SELECT * FROM vatcodes WHERE id = '{$setvatcode[$t]}' LIMIT 1"; $run_vcode = db_exec($get_vcode) or errDie("Unable to get vatcode informtion."); if (pg_numrows($run_vcode) < 1) { return "<li class='err'>Settlement Discount VAT Code Not Set.</li>"; } $vd = pg_fetch_array($run_vcode); #vat inc ... recalculate the amts $setvatamt = sprint($setamt[$t] * ($vd['vat_amount'] / (100 + $vd['vat_amount']))); $setamt[$t] = sprint($setamt[$t] - $setvatamt); $vatacc = gethook("accnum", "salesacc", "name", "VAT", "VAT"); $svattot = sprint($setamt[$t] + $setvatamt - ($setamt[$t] + $setvatamt) * 2); $svatamt = sprint($setvatamt - $setvatamt * 2); #process the vat amt ... writetrans($vatacc, $dept['debtacc'], $accdate[$t], $refnum, $setvatamt, "VAT Received on Settlement Discount for Customer : {$cus['cusname']} {$cus['surname']}"); vatr($vd['id'], $accdate[$t], "OUTPUT", $vd['code'], $refnum, "VAT for Settlement Discount for Customer : {$cus['cusname']} {$cus['surname']}", $svattot, $svatamt); } else { #no vat for set amt ... do nothing $setvatamt = 0; $svattot = 0; $svatamt = 0; } writetrans($setaccid, $dept['debtacc'], $accdate[$t], $refnum, sprint($setamt[$t]), "Settlement Discount For {$cus['cusname']} {$cus['surname']}"); custledger($cus['cusnum'], $bank_acc[$t], $sdate[$t], "{$refnum}", "Payment Settlement Discount Received.", sprint($setamt[$t] + $setvatamt), "c"); $sql = "\n\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$cus['cusnum']}', '0', '" . $svattot . "', '{$sdate[$t]}', \n\t\t\t\t\t'Settlement Discount for Payment. Ref {$refnum}', '" . USER_DIV . "', '{$accdate[$t]}'\n\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); db_connect(); #record this paid settlement discount for reporting ... $settl_sql = "\n\t\t\t\tINSERT INTO settlement_cus (\n\t\t\t\t\tcustomer, amt, setamt, setvatamt, setvat, \n\t\t\t\t\tsetvatcode, tdate, sdate, refnum\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$cus['cusnum']}', '{$amt[$t]}', '{$setamt[$t]}', '{$setvatamt}', '{$setvat[$t]}', \n\t\t\t\t\t'{$setvatcode[$t]}', '{$accdate[$t]}', 'now', '{$refnum[$t]}'\n\t\t\t\t)"; $run_settl = db_exec($settl_sql) or errDie("Unable to get debtor settlement information."); } pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF); } // status report $write = "\n\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t<tr>\n\t\t\t\t<th>Bank Receipt</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Bank Receipt added to cash book.</td>\n\t\t\t</tr>\n\t\t</table>"; $OUTPUT = "\n\t\t<center>\n\t\t<table width='90%'>\n\t\t\t<tr valign='top'>\n\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t<td align='center'>" . mkQuickLinks(ql("bank-pay-add.php", "Add Bank Payment"), ql("bank-recpt-add.php", "Add Bank Receipt"), ql("bank-recpt-inv.php", "Add Customer Payment"), ql("cashbook-view.php", "View Cash Book")) . "\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>"; return $OUTPUT; }
function 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($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 slctCat($_POST); } require_lib("validate"); $v = new validate(); $v->isOk($type, "string", 1, 2, "Invalid category type."); $v->isOk($tab, "string", 1, 14, "Invalid category type."); $v->isOk($accname, "string", 1, 50, "Invalid account name."); $v->isOk($catid, "string", 1, 50, "Invalid category Id/name."); $v->isOk($topacc, "num", 4, 4, "Invalid account number."); $v->isOk($accnum, "num", 1, 3, "Invalid account number."); $v->isOk($toptype, "string", 1, 255, "Invalid category type."); if ($v->isError()) { $err = $v->genErrors(); return $err; } core_connect(); $sql = "SELECT * FROM accounts WHERE accname = '{$accname}' AND div = '" . USER_DIV . "'"; $checkRslt = db_exec($sql) or errDie("Unable to retrieve Account details from database."); if (pg_numrows($checkRslt) > 0) { $confirm = "\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' class='err'>Account name already exist.</td>\n\t\t\t</tr>"; return slctCaterr($type, $tab, $accname, $catid, $topacc, $accnum, $confirm); exit; } # Check Account Number core_connect(); $sql = "SELECT * FROM accounts WHERE topacc = '{$topacc}' AND accnum = '{$accnum}' AND div = '" . USER_DIV . "'"; $checkRslt = db_exec($sql) or errDie("Unable to retrieve Account details from database."); $check = pg_numrows($checkRslt); if (pg_numrows($checkRslt) > 0) { $confirm = "\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' class='err'>The Account number is already in use.</td>\n\t\t\t</tr>"; return slctCaterr($type, $tab, $accname, $catid, $topacc, $accnum, $confirm); exit; } # Begin sql transaction pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF); // Get the db value of toptype $toptype_db = explode(":", $toptype); $toptype_db = $toptype_db[0]; # write to DB $sql = "\n\t\tINSERT INTO accounts (\n\t\t\ttopacc, accnum, accname, acctype, catid, div, toptype\n\t\t) VALUES (\n\t\t\t'{$topacc}', '{$accnum}', '{$accname}','{$type}', '{$catid}', '" . USER_DIV . "', '{$toptype_db}'\n\t\t)"; $catRslt = db_exec($sql) or errDie("Unable to add Account to Database.", SELF); # get last inserted id for new acc $accid = pglib_lastid("accounts", "accid"); global $MONPRD; $month_names = array(0, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"); # insert account into trial Balance insert_trialbal($accid, $topacc, $accnum, $accname, $type, 'f', USER_DIV); 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 FROM core.trial_bal WHERE month='{$i}' AND accid='{$accid}'"; db_exec($sql) or die($sql); $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 FROM core.trial_bal WHERE month='{$i}' AND accid='{$accid}'"; db_exec($sql) or die($sql); $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\tFROM core.trial_bal \n\t\t\tWHERE month='{$i}' AND accid='{$accid}'"; db_exec($sql) or die($sql); } pglib_transaction("COMMIT") or errDie("Unable to start a database transaction.", SELF); block(); // print " // <script> // window.opener.location.reload(); // window.close(); // </script>"; if (isset($update_parent) and $update_parent == "yes") { #do something to reload the parent window ... print "\n\t\t\t<script>\n\t\t\t\twindow.opener.document.form.key.value='{$set_key}';\n\t\t\t\twindow.opener.document.form.submit ();\n\t\t\t\twindow.close();\n\t\t\t</script>"; } else { #do normal return # status report $write = "\n\t\t\t<table " . TMPL_tblDflts . " width='50%'>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>New Account</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t<td>New Account, <b>({$topacc}/{$accnum}) - {$accname}</b> was successfully added to Cubit.</td>\n\t\t\t\t</tr>\n\t\t\t</table><br>" . mkQuickLinks(ql("../reporting/allcat.php", "List All Accounts (New Window)", true), ql("acc-view.php", "View Accounts"), ql("acc-new2.php", "Add Account")); return $write; } }
function write() { extract($_REQUEST); require_lib("validate"); $v = new validate(); // Validate Categories if (isset($cat_start_id) && is_numeric($cat_start_id)) { $sql = "SELECT catid FROM cubit.stockcat WHERE catid='{$cat_start_id}'"; $cat_rslt = db_exec($sql) or errDie("Unable to retrieve categories."); if (!pg_num_rows($cat_rslt)) { $v->addError(0, "Invalid starting category"); } } if (isset($cat_end_id) && is_numeric($cat_end_id) && $cat_end_id > 0) { $sql = "SELECT catid FROM cubit.stockcat WHERE catid='{$cat_end_id}'"; $cat_rslt = db_exec($sql) or errDie("Unable to retrieve categories."); if (!pg_num_rows($cat_rslt)) { $v->addError(0, "Invalid ending category"); } } // Validate price options $price_options = array("price_all", "price_recent", "price_multi"); if (!isset($price_rad)) { $v->addError(0, "Please select a price option."); } elseif (!in_array($price_rad, $price_options)) { $v->addError(0, "Invalid price option selected."); } else { switch ($price_rad) { case "price_all": $v->isOk($price_one, "float", 1, 20, "Invalid price factor for existing selling prices."); break; case "price_recent": $v->isOk($price_two, "float", 1, 20, "Invalid price factor for most recent prices."); break; case "price_multi": // A if (!isset($price_a) || !isset($factor_a)) { $v->addError(0, "No cost or factor specified for price A"); } else { $v->isOk($price_a, "float", 1, 20, "Invalid cost (A)"); $v->isOk($factor_a, "float", 1, 20, "Invalid factor (A)"); } // B if (!isset($price_b) || empty($price_b)) { break; } else { $v->isOk($price_b, "float", 1, 20, "Invalid cost (B)"); $v->isOk($factor_b, "float", 1, 20, "Invalid factor (B)"); } // C if (!isset($price_c) || empty($price_c)) { break; } else { $v->isOk($price_c, "float", 1, 20, "Invalid cost (C)"); $v->isOk($factor_c, "float", 1, 20, "Invalid factor (C)"); } // D if (!isset($price_d) || empty($price_d)) { break; } else { $v->isOk($price_d, "float", 1, 20, "Invalid cost (D)"); $v->isOk($factor_d, "float", 1, 20, "Invalid factor (D)"); } // E if (!isset($price_e) || empty($price_e)) { break; } else { $v->isOk($price_e, "float", 1, 20, "Invalid cost (E)"); $v->isOk($factor_e, "float", 1, 20, "Invalid factor (E)"); } break; } } if ($v->isError()) { return enter($v->genErrors()); } $affected_rows = 0; if (isset($new_price)) { foreach ($new_price as $stkid => $value) { $sql = "\n\t\t\tUPDATE exten.plist_prices SET price='{$value}'\n\t\t\tWHERE stkid='{$stkid}'"; db_exec($sql); $affected_rows++; } } /* // Retrieve starting category name $sql = "SELECT cat FROM cubit.stockcat WHERE catid='$cat_start_id'"; $cat_rslt = db_exec($sql) or errDie("Unable to retrieve start category."); $cat_start = pg_fetch_result($cat_rslt, 0); // Retrieve ending category name if ($cat_end_id) { $sql = "SELECT cat FROM cubit.stockcat WHERE catid='$cat_end_id'"; } else { $sql = "SELECT max(cat) FROM cubit.stockcat"; } $cat_rslt = db_exec($sql) or errDie("Unable to retrieve end category."); $cat_end = pg_fetch_result($cat_rslt, 0); // Keep track of the amount of pricelist items updated $affected_rows = 0; pglib_transaction("BEGIN"); switch ($price_rad) { case "price_all": // Retrieve items to update $sql = " SELECT id FROM exten.plist_prices LEFT JOIN cubit.stockcat ON plist_prices.catid=stockcat.catid WHERE cat BETWEEN '$cat_start' AND '$cat_end'"; $items_rslt = db_exec($sql) or errDie("Unable to retrieve items to update."); while (list($id) = pg_fetch_array($items_rslt)) { $sql = " UPDATE exten.plist_prices SET price=(price * '$price_one') WHERE id='$id'"; $rslt = db_exec($sql) or errDie("Unable to update pricelist."); $affected_rows += pg_affected_rows($rslt); } break; case "price_recent": // Retrieve items to update $sql = " SELECT id, lcsprice FROM exten.plist_prices LEFT JOIN cubit.stockcat ON plist_prices.catid=stockcat.catid LEFT JOIN cubit.stock ON plist_prices.stkid=stock.stkid WHERE cat BETWEEN '$cat_start' AND '$cat_end'"; $items_rslt = db_exec($sql) or errDie("Unable to retrieve items to update."); while (list($id, $lcsprice) = pg_fetch_array($items_rslt)) { $sql = " UPDATE exten.plist_prices SET price=('$lcsprice' * '$price_two') WHERE id='$id'"; $rslt = db_exec($sql) or errDie("Unable to update pricelist."); $affected_rows += $pg_affected_rows($rslt); } break; case "price_multi": // Query A ----------------------------------------------------------- $sql = " SELECT id FROM exten.plist_prices LEFT JOIN cubit.stockcat ON plist_prices.catid=stockcat.catid LEFT JOIN cubit.stock ON plist_prices.stkid=stock.stkid WHERE cat BETWEEN '$cat_start' AND '$cat_end' AND price BETWEEN 0 AND '$price_a'"; $items_rslt = db_exec($sql) or errDie("Unable to retrieve prices."); while (list($id) = pg_fetch_array($items_rslt)) { $sql = " UPDATE exten.plist_prices SET price=(price * '$factor_a') WHERE id='$id'"; $rslt = db_exec($sql) or errDie("Unable to update pricelist."); $affected_rows += pg_affected_rows($rslt); } // Query B ----------------------------------------------------------- if (!isset($price_b) || empty($price_b)) break; $sql = " SELECT id FROM exten.plist_prices LEFT JOIN cubit.stockcat ON plist_prices.catid=stockcat.catid LEFT JOIN cubit.stock ON plist_prices.stkid=stock.stkid WHERE cat BETWEEN '$cat_start' AND '$cat_end' AND price BETWEEN '$price_a' AND '$price_b'"; $items_rslt = db_exec($sql) or errDie("Unable to retrieve prices."); while (list($id) = pg_fetch_array($items_rslt)) { $sql = " UPDATE exten.plist_prices SET price=(price * '$factor_b') WHERE id='$id'"; $rslt = db_exec($sql) or errDie("Unable to update pricelist."); $affected_rows += pg_affected_rows($rslt); } // Query C ----------------------------------------------------------- if (!isset($price_c) || empty($price_c)) break; $sql = " SELECT id FROM exten.plist_prices LEFT JOIN cubit.stockcat ON plist_prices.catid=stockcat.catid LEFT JOIN cubit.stock ON plist_prices.stkid=stock.stkid WHERE cat BETWEEN '$cat_start' AND '$cat_end' AND price BETWEEN '$price_b' AND '$price_c'"; $items_rslt = db_exec($sql) or errDie("Unable to retrieve prices."); while (list($id) = pg_fetch_array($items_rslt)) { $sql = " UPDATE exten.plist_prices SET price=(price * '$factor_c') WHERE id='$id'"; $rslt = db_exec($sql) or errDie("Unable to update pricelist."); $affected_rows += pg_affected_rows($rslt); } // Query D ----------------------------------------------------------- if (!isset($price_d) || empty($price_d)) break; $sql = " SELECT id FROM exten.plist_prices LEFT JOIN cubit.stockcat ON plist_prices.catid=stockcat.catid LEFT JOIN cubit.stock ON plist_prices.stkid=stock.stkid WHERE cat BETWEEN '$cat_start' AND '$cat_end' AND price BETWEEN '$price_c' AND '$price_d'"; $items_rslt = db_exec($sql) or errDie("Unable to retrieve prices."); while (list($id) = pg_fetch_array($items_rslt)) { $sql = " UPDATE exten.plist_prices SET price=(price * '$factor_d') WHERE id='$id'"; $rslt = db_exec($sql) or errDie("Unable to update pricelist."); $affected_rows += pg_affected_rows($rslt); } // Query E ----------------------------------------------------------- if (!isset($price_e) || empty($price_e)) break; $sql = " SELECT id FROM exten.plist_prices LEFT JOIN cubit.stockcat ON plist_prices.catid=stockcat.catid LEFT JOIN cubit.stock ON plist_prices.stkid=stock.stkid WHERE cat BETWEEN '$cat_start' AND '$cat_end' AND price BETWEEN '$price_d' AND '$price_e'"; $items_rslt = db_exec($sql) or errDie("Unable to retrieve prices."); while (list($id) = pg_fetch_array($items_rslt)) { $sql = " UPDATE exten.plist_prices SET price=(price * '$factor_e') WHERE id='$id'"; $rslt = db_exec($sql) or errDie("Unable to update pricelist."); $affected_rows += pg_affected_rows($rslt); } } */ pglib_transaction("COMMIT"); $msg = "\n\t\t<li class='yay' style='font-size: 1.2em'>\n\t\t\t<strong>{$affected_rows}</strong> prices, updated successfully!\n\t\t\t<a href='pricelist-xls.php?listid=2'>Export</a>\n\t\t</li>"; return enter($msg); }
function write($_POST) { extract($_POST); require_lib("validate"); $v = new validate(); $v->isOk($locale, "string", 1, 80, "Invalid language selection."); $v->isOk($timezone, "string", 1, 80, "Invalid timezone selection."); if ($v->isError()) { $confirm = $v->genErrors(); return enter($confirm); } // Retrieve user admin info db_conn("cubit"); $sql = "SELECT admin FROM users WHERE username='******'"; $admRslt = db_exec($sql) or errDie("Unable to retrieve user admin information from Cubit."); $adm = pg_fetch_result($admRslt, 0); if (!isset($enable)) { $enable = "disabled"; } if ($adm == 1) { // Username db_conn("cubit"); $sql = "UPDATE users SET locale='{$locale}', locale_enable='{$enable}' WHERE userid='{$username}'"; $usrRslt = db_exec($sql) or errDie("Unable to update user locale settings to Cubit."); if (pg_affected_rows($usrRslt) > 0) { $msg = "<tr class='bg-odd'>\n\t\t\t <td><li>Successfully updated user locale settings</li></td>\n\t\t\t</tr>"; } else { $msg = "<tr><td><li class=err>Failed to update user locale settings.</li></td></tr>"; } // Default db_conn("cubit"); $sql = "UPDATE cubit.settings SET value='{$locale}' WHERE constant='LOCALE_DEFAULT'"; $defRslt = db_exec($sql) or errDie("Unable to update the default locale setting to Cubit."); if (pg_affected_rows($defRslt) > 0) { $msg .= "<tr class='bg-odd'>\n\t\t\t <td><li>Successfully updated the default locale setting.</li></td>\n\t\t\t</tr>"; } else { $msg .= "<tr class='bg-odd'>\n\t\t\t <td><li class=err>Failed to update the default locale setting</li></td>\n\t\t\t</tr>"; } /* timezone */ db_conn("cubit"); $sql = "UPDATE cubit.settings SET value='{$timezone}' WHERE constant='LOCALE_TIMEZONE'"; $defRslt = db_exec($sql) or errDie("Unable to update the timezone setting to Cubit."); if (pg_affected_rows($defRslt) > 0) { $msg .= "<tr class='bg-odd'>\n\t\t\t <td><li>Successfully updated the timezone selecion.</li></td>\n\t\t\t</tr>"; } else { $msg .= "<tr class='bg-odd'>\n\t\t\t <td><li class=err>Failed to update the timezone selecion</li></td>\n\t\t\t</tr>"; } } else { db_conn("cubit"); $sql = "UPDATE users SET locale='{$locale}' WHERE username='******'"; $localeRslt = db_exec($sql) or errDie("Unable to update user locale settings to Cubit."); $msg = "<tr class='bg-odd'>\n\t\t <td><li>Successfully updated user locale settings</li></td>\n\t\t</tr>"; } $OUTPUT = "<h3>Locale Settings</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t\t{$msg}\n\t</table>"; return $OUTPUT; }
function write($_POST) { extract($_POST); if (isset($back)) { unset($_POST["back"]); return alloc($_POST); } require_lib("validate"); $v = new validate(); $v->isOk($all, "num", 1, 1, "Invalid allocation."); $v->isOk($bankid, "num", 1, 30, "Invalid Bank Account."); $v->isOk($date, "date", 1, 14, "Invalid Date."); $v->isOk($out, "float", 1, 40, "Invalid out amount."); $v->isOk($descript, "string", 0, 255, "Invalid Description."); $v->isOk($reference, "string", 0, 50, "Invalid Reference Name/Number."); $v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number."); $v->isOk($amt, "float", 1, 40, "Invalid amount."); $v->isOk($cusid, "num", 1, 40, "Invalid customer number."); $v->isOk($out1, "float", 0, 40, "Invalid paid amount(currant)."); $v->isOk($out2, "float", 0, 40, "Invalid paid amount(30)."); $v->isOk($out3, "float", 0, 40, "Invalid paid amount(60)."); $v->isOk($out4, "float", 0, 40, "Invalid paid amount(90)."); $v->isOk($out5, "float", 0, 40, "Invalid paid amount(120)."); if (isset($invids)) { foreach ($invids as $key => $value) { $v->isOk($invids[$key], "num", 1, 50, "Invalid Invoice No."); $v->isOk($paidamt[$key], "float", 1, 40, "Invalid amount to be paid."); } } if ($v->isError()) { $confirm = $v->genErrors(); return $confirm . confirm($_POST); } /* get bank account id of cash on hand account IF this entry is cash */ if (($bank_acc = getbankaccid($bankid)) === false or $bankid == "0") { //old function didnt check if cash is selected ... if(($bank_acc = getbankaccid($bankid)) === false) { $sql = "SELECT accid FROM core.accounts WHERE accname='Cash on Hand'"; $rslt = db_exec($sql); if (pg_num_rows($rslt) < 1) { if ($bankid == 0) { return "There is no 'Cash on Hand' account, there was one, but\n\t\t\t\t\t\t**s not there now, you must have deleted it, if you want\n\t\t\t\t\t\tto use cash functionality please create a 'Cash on Hand' account."; } else { return "Invalid bank acc."; } } $bank_acc = pg_fetch_result($rslt, 0); } $cus = qryCustomer($cusid, "cusnum, deptid, cusname, surname"); $dept = qryDepartment($cus["deptid"], "debtacc"); $refnum = getrefnum(); pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF); # date format $sdate = explode("-", $date); $sdate = $sdate[2] . "-" . $sdate[1] . "-" . $sdate[0]; $cheqnum = 0 + $cheqnum; $pay = ""; $accdate = $sdate; /* Paid invoices */ $invidsers = ""; $rinvids = ""; $amounts = ""; $invprds = ""; $rages = ""; /* OPTION 1 : AUTO ALLOCATE (write) */ if ($all == 0) { # update the customer (make balance less) $sql = "UPDATE cubit.customers SET balance = (balance - '{$amt}'::numeric(13,2))\n\t\t\t\tWHERE cusnum = '{$cus['cusnum']}' AND div = '" . USER_DIV . "'"; $rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF); $reverse_allocation_dates = ""; $reverse_allocation_amounts = ""; if (isset($invids)) { foreach ($invids as $key => $value) { $ii = $invids[$key]; /* OPTION 1: STOCK INVOICES */ if (!isset($itype[$ii]) && !isset($ptype[$ii])) { $sql = "\n\t\t\t\t\t\tSELECT prd,invnum,odate \n\t\t\t\t\t\tFROM cubit.invoices\n\t\t\t\t\t\tWHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'"; $invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database."); if (pg_numrows($invRslt) < 1) { return "<li class='err'>Invalid Invoice Number.</li>"; } $inv = pg_fetch_array($invRslt); $inv['invnum'] += 0; // reduce invoice balance $sql = "UPDATE cubit.invoices\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); $sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); # record the payment on the statement $sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}','{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $paidamt[$key] * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t)"; if (!(isset($bulk_pay) and strlen($bulk_pay) > 0)) { $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); } else { $reverse_allocation_dates .= "{$inv['odate']}|"; $reverse_allocation_amounts .= sprint($paidamt[$key] - $paidamt[$key] * 2) . "|"; } custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Invoice No. {$inv['invnum']}", $paidamt[$key], "c"); $rinvids .= "|{$invids[$key]}"; $amounts .= "|{$paidamt[$key]}"; if ($inv['prd'] == "0") { $inv['prd'] = PRD_DB; } $invprds .= "|{$inv['prd']}"; $rages .= "|0"; $invidsers .= " - {$inv['invnum']}"; /* OPTION 1: NONS STOCK INVOICES */ } else { if (!isset($ptype[$ii])) { $sql = "\n\t\t\t\t\t\tSELECT prd,invnum,descrip,age,odate \n\t\t\t\t\t\tFROM cubit.nons_invoices \n\t\t\t\t\t\tWHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'"; $invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database."); if (pg_numrows($invRslt) < 1) { return "<li class='err'>Invalid Invoice Number."; } $inv = pg_fetch_array($invRslt); $inv['invnum'] += 0; # reduce the money that has been paid $sql = "UPDATE cubit.nons_invoices\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); $sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); # record the payment on the statement $sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, \n\t\t\t\t\t\t\tdiv, allocation_date\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $paidamt[$key] * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', \n\t\t\t\t\t\t\t'" . USER_DIV . "', '{$inv['odate']}'\n\t\t\t\t\t\t)"; if (!(isset($bulk_pay) and strlen($bulk_pay) > 0)) { $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); } else { $reverse_allocation_dates .= "{$inv['odate']}|"; $reverse_allocation_amounts .= sprint($paidamt[$key] - $paidamt[$key] * 2) . "|"; } custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}", $paidamt[$key], "c"); //recordCT($paidamt[$key], $cus['cusnum'],$inv['age'],$accdate); $rinvids .= "|{$invids[$key]}"; $amounts .= "|{$paidamt[$key]}"; $invprds .= "|0"; $rages .= "|{$inv['age']}"; $invidsers .= " - {$inv['invnum']}"; } else { /* pos invoices */ $sqls = array(); for ($i = 1; $i <= 12; ++$i) { $sqls[] = "\n\t\t\t\t\t\t\tSELECT '{$i}' AS prd,invid,invnum,odate \n\t\t\t\t\t\t\tFROM \"{$i}\".pinvoices \n\t\t\t\t\t\t\tWHERE invid='{$invids[$key]}' AND div='" . USER_DIV . "'"; } $sql = implode(" UNION ", $sqls); $invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database."); if (pg_numrows($invRslt) < 1) { return "<li class='err'>Invalid Invoice Number."; } $inv = pg_fetch_array($invRslt); // reduce the invoice balance $sql = "UPDATE \"{$inv['prd']}\".pinvoices\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); $sql = "UPDATE cubit.open_stmnt\n\t\t\t\t\t\t\tSET balance = (balance - {$paidamt[$key]}::numeric(13,2))\n\t\t\t\t\t\t\tWHERE invid = '{$inv['invnum']}' AND div = '" . USER_DIV . "'"; $payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); # record the payment on the statement $sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, div, \n\t\t\t\t\t\t\tallocation_date\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($paidamt[$key] - $paidamt[$key] * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment for Non Stock Invoice No. {$inv['invnum']}', '" . USER_DIV . "', \n\t\t\t\t\t\t\t'{$inv['odate']}'\n\t\t\t\t\t\t)"; if (!(isset($bulk_pay) and strlen($bulk_pay) > 0)) { $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); } else { $reverse_allocation_dates .= "{$inv['odate']}|"; $reverse_allocation_amounts .= sprint($paidamt[$key] - $paidamt[$key] * 2) . "|"; } custledger($cus['cusnum'], $bank_acc, $sdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']}", $paidamt[$key], "c"); //recordCT($paidamt[$key], $cus['cusnum'],0,$accdate); $rinvids .= "|{$invids[$key]}"; $amounts .= "|{$paidamt[$key]}"; $invprds .= "|{$inv['prd']}"; $rages .= "|0"; $invidsers .= " - {$inv['invnum']}"; } } } #record the total for the statement if bulk is selected if (isset($bulk_pay) and strlen($bulk_pay) > 0) { $arrtotal = sprint(array_sum($paidamt)); $sql = "\n\t\t\t\t\t\tINSERT INTO cubit.stmnt (\n\t\t\t\t\t\t\tcusnum, invid, \n\t\t\t\t\t\t\tamount, date, \n\t\t\t\t\t\t\ttype, div, \n\t\t\t\t\t\t\tallocation_date, reverse_allocation_dates, reverse_allocation_amounts\n\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t'{$cus['cusnum']}', '{$inv['invnum']}', \n\t\t\t\t\t\t\t'" . ($arrtotal - $arrtotal * 2) . "', '{$sdate}', \n\t\t\t\t\t\t\t'Payment Received (Ref:{$reference})', '" . USER_DIV . "', \n\t\t\t\t\t\t\t'1500-01-01', '{$reverse_allocation_dates}', '{$reverse_allocation_amounts}'\n\t\t\t\t\t\t)"; $stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF); } } $cols = grp(m("bankid", $bankid), m("trantype", "deposit"), m("date", $sdate), m("name", "{$cus['cusname']} {$cus['surname']}"), m("descript", "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"), m("cheqnum", $cheqnum), m("amount", $amt), m("banked", "no"), m("accinv", $dept["debtacc"]), m("cusnum", $cus["cusnum"]), m("rinvids", $rinvids), m("amounts", $amounts), m("invprds", $invprds), m("rages", $rages), m("reference", $reference), m("div", USER_DIV)); $dbobj = new dbUpdate("cashbook", "cubit", $cols); $dbobj->run(DB_INSERT); $dbobj->free(); /* $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, cusnum, rinvids, amounts, invprds, rages, reference, div) VALUES ('$bankid', 'deposit', '$sdate', '$cus[cusname] $cus[surname]', '', '$cheqnum', '$amt', 'no', '$dept[debtacc]', '$cus[cusnum]', '$rinvids', '$amounts', '$invprds', '$rages', '$reference', '".USER_DIV."')"; $Rslt = db_exec ($sql) or errDie ("Unable to add bank payment to database.",SELF); */ writetrans($bank_acc, $dept['debtacc'], $accdate, $refnum, $amt, "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}"); db_conn('cubit'); if ($out > 0) { /* START OPEN ITEMS */ $openstmnt = new dbSelect("open_stmnt", "cubit", grp(m("where", "balance>0 AND cusnum='{$cusid}'"), m("order", "date"))); $openstmnt->run(); $open_out = $out; $i = 0; $ox = ""; while ($od = $openstmnt->fetch_array()) { if ($open_out == 0) { continue; } $oid = $od['id']; if ($open_out >= $od['balance']) { $open_amount[$oid] = $od['balance']; $open_out = sprint($open_out - $od['balance']); $ox .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><input type='hidden' size='20' name='open[{$oid}]' value='{$oid}'>{$od['type']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t\t\t<td><input type='hidden' name='open_amount[{$oid}]' value='{$open_amount[$oid]}'>" . CUR . " {$open_amount[$oid]}</td>\n\t\t\t\t\t\t\t</tr>"; $Sl = "UPDATE cubit.open_stmnt SET balance=balance-'{$open_amount[$oid]}' WHERE id='{$oid}'"; $Ri = db_exec($Sl) or errDie("Unable to update statement."); } elseif ($open_out < $od['balance']) { $open_amount[$oid] = $open_out; $open_out = 0; $ox .= "\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t\t<td><input type='hidden' size='20' name='open[{$oid}]' value='{$od['id']}'>{$od['type']}</td>\n\t\t\t\t\t\t\t\t<td>" . CUR . " {$od['balance']}</td>\n\t\t\t\t\t\t\t\t<td>{$od['date']}</td>\n\t\t\t\t\t\t\t\t<td><input type='hidden' name='open_amount[{$oid}]' value='{$open_amount[$oid]}'>" . CUR . " {$open_amount[$oid]}</td>\n\t\t\t\t\t\t\t</tr>"; $Sl = "UPDATE cubit.open_stmnt SET balance=balance-'{$open_amount[$oid]}' WHERE id='{$oid}'"; $Ri = db_exec($Sl) or errDie("Unable to update statement."); } $i++; } if (open()) { $bout = $out; $out = $open_out; if ($out > 0) { $sql = "INSERT INTO cubit.open_stmnt(cusnum, invid, amount, balance, date, type, st, div) VALUES('{$cus['cusnum']}', '0', '-{$out}', '-{$out}', '{$sdate}', 'Payment Received', 'n', '" . USER_DIV . "')"; $stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF); //$confirm .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>"; } $out = $bout; } else { //$confirm .="<tr class='bg-even'><td colspan=4><b>A general transaction will credit the client's account with ".CUR." $out </b></td></tr>";} } } if ($out > 0) { recordCT($out, $cus['cusnum'], 0, $accdate); $cols = grp(m("cusnum", $cus["cusnum"]), m("invid", 0), m("amount", -$out), m("date", $sdate), m("type", "Payment Received"), m("div", USER_DIV), m("allocation_date", $accdate)); $dbobj = new dbUpdate("stmnt", "cubit", $cols); $dbobj->run(DB_INSERT); $dbobj->free(); custledger($cus['cusnum'], $bank_acc, $sdate, "PAYMENT", "Payment received.", $out, "c"); } } /* start moving invoices */ // move invoices that are fully paid $sql = "SELECT * FROM cubit.invoices WHERE balance=0 AND printed = 'y' AND done = 'y' AND div = '" . USER_DIV . "'"; $invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF); while ($x = pg_fetch_array($invbRslt)) { if (($prd = $x['prd']) == "0") { $prd = PRD_DB; } // move invoice $cols = grp(m("invid", $x["invid"]), m("invnum", $x["invnum"]), m("deptid", $x["deptid"]), m("cusnum", $x["cusnum"]), m("deptname", $x["deptname"]), m("cusacc", $x["cusacc"]), m("cusname", $x["cusname"]), m("surname", $x["surname"]), m("cusaddr", $x["cusaddr"]), m("cusvatno", $x["cusvatno"]), m("cordno", $x["cordno"]), m("ordno", $x["ordno"]), m("chrgvat", $x["chrgvat"]), m("terms", $x["terms"]), m("traddisc", $x["traddisc"]), m("salespn", $x["salespn"]), m("odate", $x["odate"]), m("delchrg", $x["delchrg"]), m("subtot", $x["subtot"]), m("vat", $x["vat"]), m("total", $x["total"]), m("age", $x["age"]), m("comm", $x["comm"]), m("discount", $x["discount"]), m("delivery", $x["delivery"]), m("docref", $x["docref"]), m("prd", $x["prd"]), m("delvat", $x["delvat"]), m("balance", 0), m("printed", "y"), m("done", "y"), m("username", USER_NAME), m("div", USER_DIV)); $dbobj = new dbUpdate("invoices", $prd, $cols); $dbobj->run(DB_INSERT); $dbobj->free(); // record movement $cols = grp(m("invtype", "inv"), m("invnum", $x["invnum"]), m("prd", $x["prd"]), m("docref", $x["docref"]), m("div", USER_DIV)); $dbobj->setTable("movinv", "cubit"); $dbobj->setOpt($cols); $dbobj->run(); $dbobj->free(); // move invoice items $inv_items = new dbSelect("inv_items", "cubit", grp(m("where", wgrp(m("invid", $x["invid"]), m("div", USER_DIV))))); $inv_items->run(); while ($xi = $inv_items->fetch_array()) { $xi['vatcode'] += 0; $xi['account'] += 0; $xi['del'] += 0; $cols = grp(m("invid", $x["invid"]), m("whid", $xi["whid"]), m("stkid", $xi["stkid"]), m("qty", $xi["qty"]), m("unitcost", $xi["unitcost"]), m("amt", $xi["amt"]), m("disc", $xi["disc"]), m("discp", $xi["discp"]), m("vatcode", $xi["vatcode"]), m("account", $xi["account"]), m("description", $xi["description"]), m("del", $xi["del"]), m("noted", $xi["noted"]), m("serno", $xi["serno"]), m("div", USER_DIV)); $dbobj->setTable("inv_items", $prd); $dbobj->setOpt($cols); $dbobj->run(); $dbobj->free(); } /* remove invoice from cubit schema */ $dbobj = new dbDelete("invoices", "cubit", wgrp(m("invid", $x["invid"]), m("div", USER_DIV))); $dbobj->run(); $dbobj->setTable("inv_items", "cubit"); $dbobj->run(); } pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF); $cashbook_id = pglib_lastid("cashbook", "cashid"); if (isset($print_recpt) and $print_recpt == "yes") { $showreceipt = "<script>printer ('bank/bank-recpt-inv-print.php?recid={$cashbook_id}');</script>"; } else { $showreceipt = ""; } // status report $write = "\n\t\t{$showreceipt}\n\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t<tr>\n\t\t\t\t<th>Bank Receipt</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>Bank Receipt added to cash book.</td>\n\t\t\t</tr>\n\t\t</table>"; $OUTPUT = "\n\t\t<center>\n\t\t<table width='90%'>\n\t\t\t<tr valign='top'>\n\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t<td align='center'>" . mkQuickLinks(ql("bank-pay-add.php", "Add Bank Payment"), ql("bank-recpt-add.php", "Add Bank Receipt"), ql("bank-recpt-inv.php", "Add Customer Payment"), ql("cashbook-view.php", "View Cash Book")) . "\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>"; return $OUTPUT; }