function write($_POST)
{
# get vars
extract($_POST);
# validate input
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, 10, "Invalid out amount.");
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
$v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number.");
$v->isOk($amt, "float", 1, 10, "Invalid amount.");
$v->isOk($rate, "float", 1, 10, "Invalid exchange rate.");
$v->isOk($supid, "num", 1, 10, "Invalid supplier number.");
$v->isOk($out1, "float", 0, 10, "Invalid paid amount(current).");
$v->isOk($out2, "float", 0, 10, "Invalid paid amount(30).");
$v->isOk($out3, "float", 0, 10, "Invalid paid amount(60).");
$v->isOk($out4, "float", 0, 10, "Invalid paid amount(90).");
$v->isOk($out5, "float", 0, 10, "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, 20, "Invalid amount to be paid.");
}
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# get 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.</li>";
}
$bank = pg_fetch_array($rslt);
db_connect();
# Supplier name
$sql = "SELECT * FROM suppliers WHERE supid = '{$supid}' AND div = '" . USER_DIV . "'";
$supRslt = db_exec($sql);
$sup = pg_fetch_array($supRslt);
db_conn("exten");
# get debtors control account
$sql = "SELECT credacc FROM departments WHERE deptid ='{$sup['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
$dept = pg_fetch_array($deptRslt);
# Update xrate
xrate_update($sup['fcid'], $rate, "suppurch", "id");
sup_xrate_update($sup['fcid'], $rate);
bank_xrate_update($sup['fcid'], $rate);
$lamt = sprint($amt * $rate);
# date format
$sdate = explode("-", $date);
$sdate = $sdate[2] . "-" . $sdate[1] . "-" . $sdate[0];
$cheqnum = 0 + $cheqnum;
$pay = "";
$accdate = $sdate;
# Paid invoices
$invidsers = "";
$rinvids = "";
$amounts = "";
$invprds = "";
db_connect();
if ($all == 0) {
$ids = "";
$purids = "";
$fpamounts = "";
$pamounts = "";
$pdates = "";
# Begin updates
# pglib_transaction ("BEGIN") or errDie("Unable to start a database transaction.",SELF);
if (isset($invids)) {
foreach ($invids as $key => $value) {
$lpaidamt[$key] = sprint($paidamt[$key] * $rate);
#debt invoice info
$sql = "SELECT id,pdate FROM suppurch WHERE purid ='{$invids[$key]}' AND div = '" . USER_DIV . "' ORDER BY fbalance LIMIT 1";
$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>";
}
$pur = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE suppurch SET balance = (balance - '{$lpaidamt[$key]}'::numeric(13,2)), fbalance = (fbalance - '{$paidamt[$key]}'::numeric(13,2)) WHERE purid = '{$invids[$key]}' AND div = '" . USER_DIV . "' AND id='{$pur['id']}'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$ids .= "|{$pur['id']}";
$purids .= "|{$invids[$key]}";
$fpamounts .= "|{$paidamt[$key]}";
$pamounts .= "|{$lpaidamt[$key]}";
$pdates .= "|{$pur['pdate']}";
}
}
$samount = $amt - $amt * 2;
if ($out > 0) {
recordDT($out, $sup['supid']);
}
$Sl = "INSERT INTO sup_stmnt(supid, amount, edate, descript,ref,cacc, div) VALUES('{$sup['supid']}','{$samount}','{$sdate}', 'Payment','{$cheqnum}','{$bank['accnum']}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
suppledger($sup['supid'], $bank['accnum'], $sdate, $cheqnum, "Payment for purchases", $lamt, "d");
db_connect();
# Update the supplier (make fbalance less)
$sql = "UPDATE suppliers SET balance = (balance - '{$lamt}'::numeric(13,2)), fbalance = (fbalance - '{$amt}'::numeric(13,2)) WHERE supid = '{$sup['supid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Record the payment record
// $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, supid, ids, purids, pamounts, pdates, div) VALUES ('$bankid', 'withdrawal', '$sdate', '$sup[supno] - $sup[supname]', 'Supplier Payment to $sup[supname]', '$cheqnum', '$lamt', 'no', '$dept[credacc]', '$sup[supid]', '$ids', '$purids', '$pamounts', '$pdates', '".USER_DIV."')";
//2 $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, supid, ids, purids, pamounts, pdates, div) VALUES ('$bankid', 'withdrawal', '$sdate', '$sup[supno] - $sup[supname]', 'Supplier Payment to $sup[supname]', '$cheqnum', '$lamt', '$amt', 'no', '$dept[credacc]', '$sup[supid]', '$ids', '$purids', '$pamounts', '$pdates', '".USER_DIV."')";
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, supid, ids, purids, pamounts, fpamounts, pdates, fcid, currency, location, div) VALUES ('{$bankid}', 'withdrawal', '{$sdate}', '{$sup['supno']} - {$sup['supname']}', 'Supplier Payment to {$sup['supname']}', '{$cheqnum}', '{$lamt}', '{$amt}', 'no', '{$dept['credacc']}', '{$sup['supid']}', '{$ids}', '{$purids}', '{$pamounts}', '{$fpamounts}', '{$pdates}', '{$sup['fcid']}', '{$sup['currency']}', '{$sup['location']}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
# Update the bankacct table (make fbalance less) [used for cashbook fc value]
$sql = "UPDATE bankacct SET balance = (balance - '{$lamt}'::numeric(13,2)), fbalance = (fbalance - '{$amt}'::numeric(13,2)) WHERE bankid = '{$bankid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
$refnum = getrefnum($accdate);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}'";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 1) {
return "Invalid bank acc.";
}
$link = pg_fetch_array($Rx);
writetrans($dept['credacc'], $link['accnum'], $accdate, $refnum, $lamt, "Supplier Payment to {$sup['supname']}");
db_conn('cubit');
# Commit updates
# pglib_transaction ("COMMIT") or errDie("Unable to commit a database transaction.",SELF);
}
if ($all == 1) {
$ids = "";
$purids = "";
$fpamounts = "";
$pamounts = "";
$pdates = "";
# Begin updates
//pglib_transaction ("BEGIN") or errDie("Unable to start a database transaction.",SELF);
if (isset($invids)) {
foreach ($invids as $key => $value) {
$lpaidamt[$key] = sprint($paidamt[$key] * $rate);
# Get debt invoice info
$sql = "SELECT id,pdate FROM suppurch WHERE purid ='{$invids[$key]}' AND div = '" . USER_DIV . "' ORDER BY fbalance LIMIT 1";
$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>";
}
$pur = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE suppurch SET balance = (balance - '{$lpaidamt[$key]}'::numeric(13,2)), fbalance = (fbalance - {$paidamt[$key]}::numeric(13,2)) WHERE purid = '{$invids[$key]}' AND div = '" . USER_DIV . "' AND id='{$pur['id']}'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$ids .= "|{$pur['id']}";
$purids .= "|{$invids[$key]}";
$fpamounts .= "|{$paidamt[$key]}";
$pamounts .= "|{$lpaidamt[$key]}";
$pdates .= "|{$pur['pdate']}";
}
}
$samount = $amt - $amt * 2;
if ($out1 > 0) {
recordDT($out1, $sup['supid']);
}
if ($out2 > 0) {
recordDT($out2, $sup['supid']);
}
if ($out3 > 0) {
recordDT($out3, $sup['supid']);
}
if ($out4 > 0) {
recordDT($out4, $sup['supid']);
}
if ($out5 > 0) {
recordDT($out5, $sup['supid']);
}
$Sl = "INSERT INTO sup_stmnt(supid, amount, edate, descript,ref,cacc, div) VALUES('{$sup['supid']}','{$samount}','{$sdate}', 'Payment','{$cheqnum}','{$bank['accnum']}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the supplier (make fbalance less)
$sql = "UPDATE suppliers SET balance = (balance - '{$lamt}'::numeric(13,2)), fbalance = (fbalance - '{$amt}'::numeric(13,2)) WHERE supid = '{$sup['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Record the payment record
// $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, supid, ids, purids, pamounts, pdates, div) VALUES ('$bankid', 'withdrawal', '$sdate', '$sup[supno] - $sup[supname]', 'Supplier Payment to $sup[supname]', '$cheqnum', '$lamt', 'no', '$dept[credacc]', '$sup[supid]', '$ids', '$purids', '$pamounts', '$pdates', '".USER_DIV."')";
//2 $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, supid, ids, purids, pamounts, pdates, div) VALUES ('$bankid', 'withdrawal', '$sdate', '$sup[supno] - $sup[supname]', 'Supplier Payment to $sup[supname]', '$cheqnum', '$lamt', '$amt', 'no', '$dept[credacc]', '$sup[supid]', '$ids', '$purids', '$pamounts', '$pdates', '".USER_DIV."')";
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, supid, ids, purids, pamounts, fpamounts, pdates, fcid, currency, location, div) VALUES ('{$bankid}', 'withdrawal', '{$sdate}', '{$sup['supno']} - {$sup['supname']}', 'Supplier Payment to {$sup['supname']}', '{$cheqnum}', '{$lamt}', '{$amt}', 'no', '{$dept['credacc']}', '{$sup['supid']}', '{$ids}', '{$purids}', '{$pamounts}', '{$fpamounts}', '{$pdates}', '{$sup['fcid']}', '{$sup['currency']}', '{$sup['location']}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
# Update the bankacct table (make fbalance less) [used for cashbook fc value]
$sql = "UPDATE bankacct SET balance = (balance - '{$lamt}'::numeric(13,2)), fbalance = (fbalance - '{$amt}'::numeric(13,2)) WHERE bankid = '{$bankid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
$refnum = getrefnum($accdate);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}'";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 1) {
return "Invalid bank acc.";
}
$link = pg_fetch_array($Rx);
writetrans($dept['credacc'], $link['accnum'], $accdate, $refnum, $lamt, "Supplier Payment to {$sup['supname']}");
db_conn('cubit');
# Commit updates
//pglib_transaction ("COMMIT") or errDie("Unable to commit a database transaction.",SELF);
suppledger($sup['supid'], $bank['accnum'], $sdate, $cheqnum, "Payment to Supplier", $lamt, "d");
db_connect();
}
if ($all == 2) {
$ids = "";
$purids = "";
$fpamounts = "";
$pamounts = "";
$pdates = "";
# Begin updates
#pglib_transaction ("BEGIN") or errDie("Unable to start a database transaction.",SELF);
if (isset($invids)) {
foreach ($invids as $key => $value) {
$lpaidamt[$key] = sprint($paidamt[$key] * $rate);
# Get debt invoice info
$sql = "SELECT id,pdate FROM suppurch WHERE purid ='{$invids[$key]}' AND div = '" . USER_DIV . "' ORDER BY fbalance LIMIT 1";
$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.";
}
$pur = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE suppurch SET balance = (balance - '{$lpaidamt[$key]}'::numeric(13,2)), fbalance = (fbalance - '{$paidamt[$key]}'::numeric(13,2)) WHERE purid = '{$invids[$key]}' AND div = '" . USER_DIV . "' AND id='{$pur['id']}'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$samount = $paidamt[$key] - $paidamt[$key] * 2;
$Sl = "INSERT INTO sup_stmnt(supid, amount, edate, descript,ref,cacc,div) VALUES('{$sup['supid']}','{$samount}','{$sdate}', 'Payment - Purchase: {$invids[$key]}','{$cheqnum}','{$bank['accnum']}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
suppledger($sup['supid'], $bank['accnum'], $sdate, $invids[$key], "Payment for Purchase No. {$invids[$key]}", $paidamt[$key], "d");
db_connect();
# record the payment on the statement
$ids .= "|{$pur['id']}";
$purids .= "|{$invids[$key]}";
$fpamounts .= "|{$paidamt[$key]}";
$pamounts .= "|{$lpaidamt[$key]}";
$pdates .= "|{$pur['pdate']}";
}
}
$samount = $amt - $amt * 2;
# Update the supplier (make fbalance less)
$sql = "UPDATE suppliers SET balance = (balance - '{$lamt}'::numeric(13,2)), fbalance = (fbalance - '{$amt}'::numeric(13,2)) WHERE supid = '{$sup['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Record the payment record
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, supid, ids, purids, pamounts, fpamounts, pdates, fcid, currency, location, div) VALUES ('{$bankid}', 'withdrawal', '{$sdate}', '{$sup['supno']} - {$sup['supname']}', 'Supplier Payment to {$sup['supname']}', '{$cheqnum}', '{$lamt}', '{$amt}', 'no', '{$dept['credacc']}', '{$sup['supid']}', '{$ids}', '{$purids}', '{$pamounts}', '{$fpamounts}', '{$pdates}', '{$sup['fcid']}', '{$sup['currency']}', '{$sup['location']}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
# Update the bankacct table (make fbalance less) [used for cashbook fc value]
$sql = "UPDATE bankacct SET balance = (balance - '{$lamt}'::numeric(13,2)), fbalance = (fbalance - '{$amt}'::numeric(13,2)) WHERE bankid = '{$bankid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
$refnum = getrefnum($accdate);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}'";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 1) {
return "Invalid bank acc.";
}
$link = pg_fetch_array($Rx);
writetrans($dept['credacc'], $link['accnum'], $accdate, $refnum, $lamt, "Supplier Payment to {$sup['supname']}");
db_conn('cubit');
# Commit updates
#pglib_transaction ("COMMIT") or errDie("Unable to commit a database transaction.",SELF);
}
db_conn('cubit');
$Sl = "DELETE FROM suppurch WHERE fbalance = 0::numeric(13,2) AND balance = 0::numeric(13,2)";
$Rx = db_exec($Sl);
# status report
$write = "\n\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t<tr>\n\t\t\t<th>International Bank Payment</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>International Bank Payment added to cash book.</td>\n\t\t</tr>\n\t</table>";
# main table (layout with menu)
$OUTPUT = "<center>\n\t<table width='90%'>\n\t\t<tr valign='top'>\n\t\t\t<td width='50%'>{$write}</td>\n\t\t\t<td align='center'>\n\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Quick Links</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><a href='bank-pay-supp.php'>Add supplier payment</a></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><a href='bank-pay-add.php'>Add Bank Payment</a></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><a href='bank-recpt-add.php'>Add Bank Receipt</a></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><a href='cashbook-view.php'>View Cash Book</a></td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>\n\t\t\t</td>\n\t\t</tr>\n\t</table>";
return $OUTPUT;
}
function details($_GET)
{
extract($_GET);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($invid, "num", 1, 20, "Invalid invoice number.");
# display errors, if any
if ($v->isError()) {
$err = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class=err>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get invoice info
db_connect();
$sql = "SELECT * FROM hire.hire_invoices WHERE invid = '{$invid}'";
$invRslt = db_exec($sql) or errDie("Unable to get invoice information");
if (pg_numrows($invRslt) < 1) {
return "<i class='err'>Not Found[1]</i>";
}
$inv = pg_fetch_array($invRslt);
// Cash deposit
if ($inv["deposit_type"] == "CSH" && $inv["deposit_amt"] > 0) {
$get_ar = array();
foreach ($_GET as $key => $value) {
if ($key != "key") {
$get_ar[] = "{$key}={$value}";
}
}
$get_vars = implode("&", $get_ar);
$deposit_receipt = "<script>\r\n\t\t\t\t\t\t\t\tprinter(\"" . SELF . "?key=deposit{$get_vars}\")\r\n\t\t\t\t\t\t\t</script>";
} else {
$deposit_receipt = "";
}
if ($inv['rounding'] > 0) {
db_conn('core');
$Sl = "SELECT * FROM salesacc WHERE name='rounding'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please set the rounding account, under sales settings.";
}
$ad = pg_fetch_array($Ri);
$rac = $ad['accnum'];
}
if ($inv['cusnum'] != "0") {
#then get the actual customer
db_connect();
$get_cus = "SELECT * FROM customers WHERE cusnum = '{$inv['cusnum']}' LIMIT 1";
$run_cus = db_exec($get_cus) or errDie("Unable to get customer information");
if (pg_numrows($run_cus) < 1) {
#do nothing
} else {
$carr = pg_fetch_array($run_cus);
$inv['cusname'] = "{$carr['cusname']}";
$inv['surname'] = "{$carr['surname']}";
}
}
$td = $inv['odate'];
db_conn('cubit');
$sql = "SELECT asset_id FROM hire.hire_invitems WHERE invid = '{$inv['invid']}'";
$crslt = db_exec($sql);
if ($inv['terms'] == 1) {
db_conn('core');
$Sl = "SELECT * FROM salacc WHERE name='cc'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please set a link for the POS credit card control account";
}
$cd = pg_fetch_array($Ri);
$cc = $cd['accnum'];
}
$change = sprint(sprint($inv['pcash'] + $inv['pcheque'] + $inv['pcc'] + $inv['pcredit']) - sprint($inv['total'] - $inv['rounding']));
$inv['pcash'] = sprint($inv['pcash'] - $change);
if ($inv['pcash'] < 0) {
$inv['pcash'] = 0;
}
if (sprint($inv['pcash'] + $inv['pcheque'] + $inv['pcc'] + $inv['pcredit']) != sprint($inv['total'] - $inv['rounding'])) {
return "<li class=err>The total of all the payments is not equal to the invoice total.<br>\r\n\t\tPlease edit the invoice and try again(You can only overpay with cash)</li>";
}
db_connect();
pglib_transaction("BEGIN");
$invnum = getHirenum($invid, 1);
$sql = "UPDATE hire.reprint_invoices SET invnum='{$invnum}' WHERE invid='{$invid}'";
db_exec($sql) or errDie("Unable to assign hire invoice number.");
$Sl = "INSERT INTO ncsrec (oldnum,newnum, div) VALUES ('{$invid}','{$invnum}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert into db");
//unlock(2);
# get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$inv['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
$dept['deptname'] = "<i class=err>Not Found[2]</i>";
} else {
$dept = pg_fetch_array($deptRslt);
}
/* --- Start Products Display --- */
# Products layout
$products = "";
$disc = 0;
# get selected stock in this invoice
db_connect();
$sql = "SELECT * FROM hire.hire_invitems WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
$taxex = 0;
$commision = 0;
while ($stkd = pg_fetch_array($stkdRslt)) {
$stkd['account'] += 0;
if ($stkd['account'] == 0) {
# get warehouse name
db_conn("exten");
$sql = "SELECT whname FROM warehouses WHERE whid = '{$stkd['whid']}' AND div = '" . USER_DIV . "'";
$whRslt = db_exec($sql);
$wh = pg_fetch_array($whRslt);
# get selected stock in this warehouse
db_connect();
$sql = "SELECT * FROM assets WHERE id = '{$stkd['asset_id']}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql);
$stk = pg_fetch_array($stkRslt);
db_connect();
//this was set to the stock vatcode ??? must be the pur_item code ...
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatcode']}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>Please select the vatcode for all your stock.</li>";
}
$vd = pg_fetch_array($Ri);
$sp = " ";
# Check Tax Excempt
if ($stk['exvat'] == 'yes' || $vd['zero'] == "Yes") {
$taxex += $stkd['amt'];
$ex = "#";
} else {
$ex = " ";
}
# Keep track of discounts
$disc += $stkd['disc'] * $stkd['qty'];
# Insert stock record
$sdate = date("Y-m-d");
$csprice = sprint($stk['csprice'] * $stkd['qty']);
# put in product
$products .= "<tr valign=top>\r\n\t\t\t\t<td>{$stk['stkcod']}</td>\r\n\t\t\t\t<td>{$ex} {$sp} {$stk['stkdes']}</td>\r\n\t\t\t\t<td>{$stkd['qty']}</td>\r\n\t\t\t\t<td>" . sprint($stk["selamt"]) . "</td>\r\n\t\t\t\t<td>" . CUR . sprint($stkd["amt"]) . "</td>\r\n\t\t\t</tr>";
# Get amount exluding vat if including and not exempted
$VATP = TAX_VAT;
$amtexvat = sprint($stkd['amt']);
if ($inv['chrgvat'] == "inc" && $stk['exvat'] != 'yes') {
$amtexvat = sprint($stkd['amt'] * 100 / (100 + $VATP));
}
$commision = $commision + coms($inv['salespn'], $stkd['amt'], $stk['com']);
} else {
db_conn('core');
$Sl = "SELECT * FROM accounts WHERE accid='{$stkd['account']}'";
$Ri = db_exec($Sl) or errDie("Unable to get account data.");
$ad = pg_fetch_array($Ri);
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatcode']}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please select the vatcode for all your stock.";
}
$vd = pg_fetch_array($Ri);
$sp = "";
# Check Tax Excempt
if ($vd['zero'] == "Yes") {
$taxex += $stkd['amt'];
$ex = "#";
} else {
$ex = "";
}
# all must be excempted
if ($inv['chrgvat'] == 'nov') {
$ex = "#";
}
//$commision=$commision+coms($inv['salespn'], $stkd['amt'], $stk['com']);
# Put in product
$products .= "<tr valign=top>\r\n\t\t\t\t<td></td>\r\n\t\t\t\t<td>{$ex} {$sp} {$stkd['description']}</td>\r\n\t\t\t\t<td>{$stkd['qty']}</td>\r\n\t\t\t\t<td>" . sprint($stkd["unitcost"]) . "</td>\r\n\t\t\t\t<td>{$stkd['disc']}</td>\r\n\t\t\t\t<td>" . CUR . sprint($stkd["amt"]) . "</td>\r\n\t\t\t</tr>";
}
}
/* --- Start Some calculations --- */
# subtotal
$SUBTOT = sprint($inv['subtot']);
# Calculate subtotal
$VATP = TAX_VAT;
$SUBTOTAL = sprint($inv['subtot']);
$VAT = sprint($inv['vat']);
$TOTAL = sprint($inv['total']);
$av = $VAT;
$at = $TOTAL - $VAT;
$nt = sprint($inv['pcredit']);
$sd = date("Y-m-d");
$ro = $inv['rounding'];
$ro += 0;
com_invoice($inv['salespn'], $TOTAL - $VAT, $commision, $invnum);
/* --- End Some calculations --- */
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "novat");
/* - End Hooks - */
$nsp = 0;
# todays date
$date = date("d-m-Y");
$sdate = date("Y-m-d");
db_conn('cubit');
if ($inv['cusnum'] > 0 && $nt > 0) {
# Record the payment on the statement
$sql = "INSERT INTO stmnt(cusnum, invid, docref, amount, date, type, div) VALUES('{$inv['cusnum']}', '{$invnum}', '0', '{$nt}', '{$inv['odate']}', 'Invoice', '" . USER_DIV . "')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# Record the payment on the statement
$sql = "INSERT INTO open_stmnt(cusnum, invid, docref, amount, balance, date, type, div) VALUES('{$inv['cusnum']}', '{$invnum}', '0', '{$nt}', '{$nt}', '{$inv['odate']}', 'Invoice', '" . USER_DIV . "')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$nt}') WHERE cusnum = '{$inv['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
custledger($inv['cusnum'], $dept['incacc'], $inv['odate'], $invnum, "Invoice No. {$invnum}", $nt, "d");
recordDT($nt, $inv['cusnum'], $inv['odate']);
db_conn('cubit');
$Sl = "INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('{$sd}','" . USER_NAME . "','{$invnum}','{$nt}','Credit','" . PRD_DB . "','0')";
$Ri = db_exec($Sl) or errDie("Unable to insert data.");
}
db_conn('cubit');
if ($inv['terms'] == 1) {
$Sl = "INSERT INTO crec(userid,username,amount,pdate,inv) VALUES ('" . USER_ID . "','" . USER_NAME . "','{$TOTAL}','{$td}','{$invnum}')";
$Ry = db_exec($Sl) or errDie("Unable to insert pos record.");
} else {
$Sl = "INSERT INTO posrec(userid,username,amount,pdate,inv) VALUES ('" . USER_ID . "','" . USER_NAME . "','{$TOTAL}','{$td}','{$invnum}')";
$Ry = db_exec($Sl) or errDie("Unable to insert pos record.");
}
$Sl = "INSERT INTO pr(userid,username,amount,pdate,inv,cust,t) VALUES ('" . USER_ID . "','" . USER_NAME . "','{$TOTAL}','{$td}','{$invnum}','{$inv['cusname']}','{$inv['terms']}')";
$Ry = db_exec($Sl) or errDie("Unable to insert pos record.");
$refnum = getrefnum();
$fcash = $inv['pcash'];
$fccp = $inv['pcc'];
$fcheque = $inv['pcheque'];
$fcredit = $inv['pcredit'];
/* --- Updates ---- */
db_connect();
$Sql = "UPDATE hire.hire_invoices SET pchange='{$change}',printed = 'y', done ='y',invnum='{$invnum}' WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$upRslt = db_exec($Sql) or errDie("Unable to update invoice information");
# save invoice discount
$sql = "INSERT INTO inv_discs(cusnum, invid, traddisc, itemdisc, inv_date, delchrg, div,total) VALUES('0','{$invnum}','{$inv['delivery']}','{$disc}', '{$inv['odate']}', '{$inv['delivery']}', '" . USER_DIV . "',({$SUBTOT}+{$inv['delivery']}))";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# get selected stock in this invoice
$sql = "SELECT * FROM hire.hire_invitems WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
$tcosamt = 0;
if (strlen($inv['comm']) > 0) {
$Com = "<table><tr><td>" . nl2br($inv['comm']) . "</td></tr></table>";
} else {
$Com = "";
}
$cc = "<script> sCostCenter('dt', 'Sales', '{$date}', 'POS Invoice No.{$invnum}', '" . ($TOTAL - $VAT) . "', 'Cost Of Sales for Invoice No.{$invnum}', '{$tcosamt}', ''); </script>";
if ($inv['chrgvat'] == "inc") {
$inv['chrgvat'] = "Inclusive";
} elseif ($inv['chrgvat'] == "exc") {
$inv['chrgvat'] = "Exclusive";
} else {
$inv['chrgvat'] = "No vat";
}
/* - End Transactoins - */
/* -- Final Layout -- */
$details = "<center>\r\n\t{$deposit_receipt} {$cc}\r\n\t<h2>Tax Invoice</h2>\r\n\t<table cellpadding='0' cellspacing='1' border=0 width=750>\r\n\t<tr><td valign=top width=40%>\r\n\t\t<table cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' border=0>\r\n\t\t\t<tr><td>{$inv['surname']}</td></tr>\r\n\t\t</table>\r\n\t</td><td valign=top width=35%>\r\n\t\t" . COMP_NAME . "<br>\r\n\t\t" . COMP_ADDRESS . "<br>\r\n\t\t" . COMP_TEL . "<br>\r\n\t\t" . COMP_FAX . "<br>\r\n\t\tReg No. " . COMP_REGNO . "<br>\r\n\t</td><td valign=bottom align=right width=25%>\r\n\t\t<table cellpadding='2' cellspacing='0' border=1 bordercolor='#000000'>\r\n\t\t\t<tr><td><b>Hire No.</b></td><td valign=center>H" . getHirenum($inv["invid"], 1) . "</td></tr>\r\n\t\t\t<tr><td><b>Order No.</b></td><td valign=center>{$inv['ordno']}</td></tr>\r\n\t\t\t<tr><td><b>Terms</b></td><td valign=center>Cash</td></tr>\r\n\t\t\t<tr><td><b>Invoice Date</b></td><td valign=center>{$inv['odate']}</td></tr>\r\n\t\t\t<tr><td><b>VAT</b></td><td valign=center>{$inv['chrgvat']}</td></tr>\r\n\t\t</table>\r\n\t</td></tr>\r\n\t<tr><td><br></td></tr>\r\n\t<tr><td colspan=3>\r\n\t<table cellpadding='5' cellspacing='0' border=1 width=100% bordercolor='#000000'>\r\n\t\t<tr><th>ITEM NUMBER</th><th width=45%>DESCRIPTION</th><th>QTY</th><th>UNIT PRICE</th><th>AMOUNT</th><tr>\r\n\t\t{$products}\r\n\t</table>\r\n\t</td></tr>\r\n\t<tr><td>\r\n\t\t{$inv['custom_txt']}\r\n\t\t{$Com}\r\n\t</td><td align=right colspan=2>\r\n\t\t<table cellpadding='5' cellspacing='0' border=1 width=50% bordercolor='#000000'>\r\n\t\t\t<tr><td><b>SUBTOTAL</b></td><td align=right>" . CUR . " {$SUBTOT}</td></tr>\r\n\t\t\t<tr><td><b>Trade Discount</b></td><td align=right>" . CUR . " {$inv['discount']}</td></tr>\r\n\t\t\t<tr><td><b>Delivery Charge</b></td><td align=right>" . CUR . " {$inv['delivery']}</td></tr>\r\n\t\t\t<tr><td><b>VAT @ {$VATP}%</b></td><td align=right>" . CUR . " {$VAT}</td></tr>\r\n\t\t\t<tr><th><b>GRAND TOTAL<b></th><td align=right>" . CUR . " {$TOTAL}</td></tr>\r\n\t\t</table>\r\n\t</td></tr>\r\n\t<tr><td><br></td></tr>\r\n\t<tr><td>\r\n\t\t<table cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' border=1>\r\n\t\t\t<tr><td colspan=2>VAT Exempt indicator = #</td></tr>\r\n\t\t\t<tr><th>VAT No.</th><td align=center>" . COMP_VATNO . "</td></tr>\r\n </table>\r\n\t</td><td><br></td></tr>\r\n\t</table></center>";
/* Start moving invoices */
db_connect();
# Move invoices that are fully paid
$sql = "SELECT * FROM hire.hire_invoices WHERE invid='{$invid}'";
$invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$time2 = time();
while ($invb = pg_fetch_array($invbRslt)) {
$invb['invnum'] += 0;
# Insert invoice to period DB
$sql = "INSERT INTO hire.hire_invoices(invid,invnum, deptid, cusnum, deptname, cusacc, cusname, surname, cusaddr, cusvatno, cordno, ordno, chrgvat, terms, traddisc, salespn, odate, delchrg, subtot, vat, total, balance, comm, printed, done, div, username,rounding,delvat,vatnum,pcash,pcheque,pcc,pcredit)";
$sql .= " VALUES('{$invb['invid']}','{$invb['invnum']}', '{$invb['deptid']}', '{$invb['cusnum']}', '{$invb['deptname']}', '{$invb['cusacc']}', '{$invb['cusname']}', '{$invb['surname']}', '{$invb['cusaddr']}', '{$invb['cusvatno']}', '{$invb['cordno']}', '{$invb['ordno']}', '{$invb['chrgvat']}', '{$invb['terms']}', '{$invb['traddisc']}', '{$invb['salespn']}', '{$invb['odate']}', '{$invb['delchrg']}', '{$invb['subtot']}', '{$invb['vat']}' , '{$invb['total']}', '{$invb['balance']}', '{$invb['comm']}', 'y', 'y', '" . USER_DIV . "','" . USER_NAME . "','{$invb['rounding']}','{$invb['delvat']}','{$invb['vatnum']}','{$invb['pcash']}','{$invb['pcheque']}','{$invb['pcc']}','{$invb['pcredit']}')";
//$rslt = db_exec($sql) or errDie("Unable to insert invoice to the period database.",SELF);
$sql = "SELECT * FROM hire.monthly_invoices WHERE invid='{$invb['invid']}'";
$hi_rslt = db_exec($sql) or errDie("Unable to retrieve hire invoice.");
if (pg_num_rows($hi_rslt)) {
$sql = "UPDATE hire.monthly_invoices SET invnum='{$invb['invnum']}',\r\n\t\t\t\t\t\tdeptid='{$invb['deptid']}', cusnum='{$invb['cusnum']}',\r\n\t\t\t\t\t\tdeptname='{$invb['deptname']}', cusacc='{$invb['cusacc']}',\r\n\t\t\t\t\t\tcusname='{$invb['cusname']}', surname='{$invb['surname']}',\r\n\t\t\t\t\t\tcusaddr='{$invb['cusaddr']}', cusvatno='{$invb['cusvatno']}',\r\n\t\t\t\t\t\tcordno='{$invb['cordno']}', ordno='{$invb['ordno']}',\r\n\t\t\t\t\t\tchrgvat='{$invb['chrgvat']}', terms='{$invb['terms']}',\r\n\t\t\t\t\t\ttraddisc='{$invb['traddisc']}', salespn='{$invb['salespn']}',\r\n\t\t\t\t\t\todate='{$invb['odate']}', delchrg='{$invb['delchrg']}',\r\n\t\t\t\t\t\tsubtot='{$invb['subtot']}', vat='{$invb['vat']}',\r\n\t\t\t\t\t\ttotal='{$invb['total']}', balance='{$invb['balance']}',\r\n\t\t\t\t\t\tcomm='{$invb['comm']}', printed='{$invb['printed']}',\r\n\t\t\t\t\t\tdone='{$invb['done']}', div='{$invb['div']}',\r\n\t\t\t\t\t\tusername='******'username']}', rounding='{$invb['rounding']}',\r\n\t\t\t\t\t\tdelvat='{$invb['delvat']}', vatnum='{$invb['vatnum']}',\r\n\t\t\t\t\t\tpcash='{$invb['pcash']}', pcheque='{$invb['pcheque']}',\r\n\t\t\t\t\t\tpcc='{$invb['pcc']}', pcredit='{$invb['pcredit']}'\r\n\t\t\t\t\tWHERE invid='{$invb['invid']}'";
db_exec($sql) or errDie("Unable to store monthly invoice.");
$mi_invid = $invb["invid"];
} else {
$sql = "INSERT INTO hire.monthly_invoices(invid, invnum, deptid, cusnum, deptname, cusacc, cusname, surname, cusaddr, cusvatno, cordno, ordno, chrgvat, terms, traddisc, salespn, odate, delchrg, subtot, vat, total, balance, comm, printed, done, div, username,rounding,delvat,vatnum,pcash,pcheque,pcc,pcredit, invoiced_month)";
$sql .= " VALUES('{$invb['invid']}', '{$invb['invnum']}', '{$invb['deptid']}', '{$invb['cusnum']}', '{$invb['deptname']}', '{$invb['cusacc']}', '{$invb['cusname']}', '{$invb['surname']}', '{$invb['cusaddr']}', '{$invb['cusvatno']}', '{$invb['cordno']}', '{$invb['ordno']}', '{$invb['chrgvat']}', '{$invb['terms']}', '{$invb['traddisc']}', '{$invb['salespn']}', '{$invb['odate']}', '{$invb['delchrg']}', '{$invb['subtot']}', '{$invb['vat']}' , '{$invb['total']}', '{$invb['balance']}', '{$invb['comm']}', 'y', 'y', '" . USER_DIV . "','" . USER_NAME . "','{$invb['rounding']}','{$invb['delvat']}','{$invb['vatnum']}','{$invb['pcash']}','{$invb['pcheque']}','{$invb['pcc']}','{$invb['pcredit']}', '" . date("m") . "')";
db_exec($sql) or errDie("Unable to store monthly invoice.");
db_conn("hire");
$mi_invid = pglib_lastid("monthly_invoices", "invid");
}
$sql = "SELECT * FROM hire.hire_invitems WHERE invid='{$invb['invid']}'";
$invi_rslt = db_exec($sql) or errDie("Unable to retrieve note items.");
// while ($invi = pg_fetch_array($invi_rslt)) {
// if (isset($monthly) && $monthly) {
// $sql = "DELETE FROM hire.monthly_invitems WHERE invid='$mi_invid'";
// db_exec($sql) or errDie("Unable to remove items.");
//
// $sql = "INSERT INTO hire.monthly_invitems (invid, asset_id, qty,
// unitcost, amt, disc, discp, serno, div, vatcode, account,
// description, basis, from_date, to_date, hours, weeks,
// collection)
// VALUES ('$mi_invid', '$invi[asset_id]',
// '$invi[qty]', '$invi[unitcost]', '$invi[amt]',
// '$invi[disc]', '$invi[discp]', '$invi[serno]',
// '".USER_DIV."', '$invi[vatcode]', '$invi[account]',
// '$invi[description]', '$invi[basis]', '$invi[from_date]',
// '$invi[to_date]', '$invi[hours]', '$invi[weeks]',
// '$invi[collection]')";
// db_exec($sql) or errDie("Unable to create montly item.");
// }
// }
db_connect();
$sql = "INSERT INTO movinv(invtype, invnum, prd, docref, div) VALUES('pos', '{$invb['invnum']}', '{$invb['prd']}', '', '" . USER_DIV . "')";
$rslt = db_exec($sql) or errDie("Unable to insert invoice to the period database.", SELF);
# get selected stock in this invoice
db_connect();
$sql = "SELECT * FROM hire.hire_invitems WHERE invid = '{$invb['invid']}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
while ($stkd = pg_fetch_array($stkdRslt)) {
# insert invoice items
$stkd['vatcode'] += 0;
$stkd['account'] += 0;
$sql = "INSERT INTO hire.hire_invitems(invid, whid, asset_id, qty,\r\n\t\t\t\t\t\tunitcost, amt, disc, discp, serno, div, vatcode, account,\r\n\t\t\t\t\t\tdescription)\r\n\t\t\t\t\tVALUES ('{$invb['invid']}', '{$stkd['whid']}',\r\n\t\t\t\t\t\t'{$stkd['asset_id']}', '{$stkd['qty']}', '{$stkd['unitcost']}',\r\n\t\t\t\t\t\t'{$stkd['amt']}', '{$stkd['disc']}', '{$stkd['discp']}',\r\n\t\t\t\t\t\t'{$stkd['serno']}', '" . USER_DIV . "', '{$stkd['vatcode']}',\r\n\t\t\t\t\t\t'{$stkd['account']}', '{$stkd['description']}')";
$sql = "INSERT INTO hire.monthly_items (invid, whid, asset_id, qty,\r\n\t\t\t\t\t\tunitcost, amt, disc, discp, serno, div, vatcode, account,\r\n\t\t\t\t\t\tdescription)\r\n\t\t\t\t\tVALUES ('{$invb['invid']}', '{$stkd['whid']}', '{$stkd['asset_id']}',\r\n\t\t\t\t\t\t'{$stkd['qty']}', '{$stkd['unitcost']}', '{$stkd['amt']}',\r\n\t\t\t\t\t\t'{$stkd['disc']}', '{$stkd['discp']}',\t'{$stkd['serno']}',\r\n\t\t\t\t\t\t'" . USER_DIV . "',\t'{$stkd['vatcode']}', '{$stkd['account']}',\r\n\t\t\t\t\t\t '{$stkd['desciption']}')";
$rslt = db_exec($sql) or errDie("Unable to insert invoice items to Cubit.", SELF);
}
}
// Update assets
$sql = "SELECT * FROM hire.hire_invitems WHERE invid='{$inv['invid']}'";
$item_rslt = db_exec($sql) or errDie("Unable to update items.");
while ($item_data = pg_fetch_array($item_rslt)) {
if (!isSerialized($item_data["asset_id"])) {
$sql = "SELECT serial2 FROM cubit.assets\r\n\t\t\t\t\t\tWHERE id='{$item_data['asset_id']}'";
$qty_rslt = db_exec($sql) or errDie("Unable to retrieve qty.");
$qty = pg_fetch_result($qty_rslt, 0);
$qty = $qty - $item_data["qty"];
$sql = "UPDATE cubit.assets SET serial2='{$qty}'\r\n\t\t\t\t\t\tWHERE id='{$item_data['asset_id']}'";
db_exec($sql) or errDie("Unable to update assets.");
$sql = "SELECT id, units FROM hire.bookings\r\n\t\t\t\t\t\tWHERE cust_id='{$inv['cusnum']}' AND\r\n\t\t\t\t\t\t\tasset_id='{$item_data['asset_id']}'";
$bk_rslt = db_exec($sql) or errDie("Unable to retrieve booking.");
$bk_data = pg_fetch_array($bk_rslt);
// Update booking information.
if (!empty($bk_data["id"])) {
if ($bk_data["units"] - $item_data["qty"] <= 0) {
$sql = "DELETE FROM hire.bookings WHERE id='{$bk_data['id']}'";
} else {
$new_qty = $bk_data["units"] - $item_data["qty"];
$sql = "UPDATE hire.bookings SET units=(units-'{$new_qty}')\r\n\t\t\t\t\t\t\t\tWHERE id='{$bk_data['id']}'";
}
db_exec($sql) or errDie("Unable to update bookings.");
}
$item_qty = $item_data["qty"];
} else {
$sql = "DELETE FROM hire.bookings WHERE cust_id='{$inv['cusnum']}'\r\n\t\t\t\t\t\tAND asset_id='{$item_data['asset_id']}'";
db_exec($sql) or errDie("Unable to remove booking.");
$item_qty = 1;
}
$discount = $item_data["amt"] / 100 * $inv["traddisc"];
$sql = "INSERT INTO hire.assets_hired (invid, asset_id, hired_time, qty,\r\n\t\t\t\t\t item_id, cust_id, invnum, basis, value, discount, weekends)\r\n\t\t\t\tVALUES ('{$invid}', '{$item_data['asset_id']}', CURRENT_TIMESTAMP,\r\n\t\t\t\t\t'{$item_qty}', '{$item_data['id']}', '{$inv['cusnum']}',\r\n\t\t\t\t\t'{$inv['invnum']}', '{$item_data['basis']}', '{$item_data['amt']}',\r\n\t\t\t\t\t'{$discount}', '{$item_data['weekends']}')";
db_exec($sql) or errDie("Unable to hire out item.");
}
# Commit updates
pglib_transaction("COMMIT");
header("Location: hire-slip.php?invid={$inv['invid']}&prd={$inv['prd']}&cccc=yes");
exit;
}
function confirm_list($_POST)
{
extract($_POST);
if (!isset($ids) or !is_array($ids) or count($ids) < 1) {
return get_list($_POST);
}
//print "<pre>";
//var_dump ($_POST);
//print "</pre>";
foreach ($ids as $id) {
db_conn('contract');
$get_info = "SELECT * from supp_creditor_run_cheques WHERE id = '{$id}' LIMIT 1";
$run_info = db_exec($get_info) or errDie("Unable to get sub contractor information.");
if (pg_numrows($run_info) < 1) {
return "Could not get cheque information.";
}
$suparr = pg_fetch_array($run_info);
$bankid = $suparr['bankid'];
$supid = $suparr['supid'];
$date = $suparr['proc_date'];
$cheqnum = $suparr['cheq_num'];
//$date = "$date_year-$date_month-$date_day";
$amt = $suparr['amount'];
$upd_sql = "UPDATE supp_creditor_run_cheques SET handed_over = 'yes' WHERE id = '{$id}'";
$run_upd = db_exec($upd_sql) or errDie("Unable to update cheque run information.");
#now we need to process these cheques
# get hook account number
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "' AND accid!=0";
$rslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
# check if link exists
if (pg_numrows($rslt) < 1) {
$Sl = "SELECT * FROM accounts WHERE accname='Cash on Hand'";
$Rg = db_exec($Sl);
if (pg_num_rows($Rg) < 1) {
if ($bankid == 0) {
return "There is no 'Cash on Hand' account, there was one, but its not there now, you must have deleted it, if you want to use cash functionality please create a 'Cash on Hand' account.";
} else {
return "Invalid bank acc.";
}
}
$add = pg_fetch_array($Rg);
$bank['accnum'] = $add['accid'];
} else {
$bank = pg_fetch_array($rslt);
}
db_connect();
# Supplier name
$sql = "SELECT supid,supno,supname,deptid FROM suppliers WHERE supid = '{$supid}' AND div = '" . USER_DIV . "'";
$supRslt = db_exec($sql);
$sup = pg_fetch_array($supRslt);
db_conn("exten");
# get debtors control account
$sql = "SELECT credacc FROM departments WHERE deptid ='{$sup['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
$dept = pg_fetch_array($deptRslt);
# date format
$sdate = $date;
$cheqnum = 0 + $cheqnum;
$pay = "";
$accdate = $sdate;
# Paid invoices
$invidsers = "";
$rinvids = "";
$amounts = "";
$invprds = "";
$out = "";
$reference = "";
db_conn("cubit");
pglib_transaction("BEGIN");
$all = 0;
if ($all == 0) {
$ids2 = "";
$purids = "";
$pamounts = "";
$pdates = "";
if (isset($invids)) {
foreach ($invids as $key => $value) {
#debt invoice info
$sql = "SELECT id,pdate FROM suppurch WHERE purid ='{$invids[$key]}' AND div = '" . USER_DIV . "' ORDER BY balance LIMIT 1";
$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.";
}
$pur = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE suppurch SET balance = (balance - '{$paidamt[$key]}'::numeric(13,2)) WHERE purid = '{$invids[$key]}' AND div = '" . USER_DIV . "' AND id='{$pur['id']}'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$ids2 .= "|{$pur['id']}";
$purids .= "|{$invids[$key]}";
$pamounts .= "|{$paidamt[$key]}";
$pdates .= "|{$pur['pdate']}";
}
}
$samount = $amt - $amt * 2;
if ($out > 0) {
recordDT($out, $sup['supid'], $sdate);
}
$Sl = "INSERT INTO sup_stmnt(supid, amount, edate, descript,ref,cacc, div) VALUES('{$sup['supid']}','{$samount}','{$sdate}', 'Payment','{$cheqnum}','{$bank['accnum']}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
db_connect();
# Update the supplier (make balance less)
$sql = "UPDATE suppliers SET balance = (balance - '{$amt}'::numeric(13,2)) WHERE supid = '{$sup['supid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
suppledger($sup['supid'], $bank['accnum'], $sdate, $cheqnum, "Payment for purchases", $amt, "d");
db_connect();
# Record the payment record
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, supid, ids, purids, pamounts, pdates, reference, div) VALUES ('{$bankid}', 'withdrawal', '{$sdate}', '{$sup['supno']} - {$sup['supname']}', 'Supplier Payment to {$sup['supname']}', '{$cheqnum}', '{$amt}', 'no', '{$dept['credacc']}', '{$sup['supid']}', '{$ids2}', '{$purids}', '{$pamounts}', '{$pdates}', '{$reference}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$refnum = getrefnum($accdate);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}'";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 1) {
return "Invalid bank acc.";
}
$link = pg_fetch_array($Rx);
$link['accnum'] = $bank['accnum'];
writetrans($dept['credacc'], $link['accnum'], $accdate, $refnum, $amt, "Supplier Payment to {$sup['supname']}");
db_conn('cubit');
}
$Sl = "DELETE FROM suppurch WHERE balance=0::numeric(13,2)";
$Rx = db_exec($Sl);
#add the supplier purchase entry ?
$purch_sql = "\n\t\t\t\t\t\tINSERT INTO suppurch \n\t\t\t\t\t\t\t(supid,purid,intpurid,pdate,div,npurid,balance,fcid,fbalance) \n\t\t\t\t\t\tVALUES \n\t\t\t\t\t\t\t('{$sup['supid']}','0','0','{$date}','" . USER_DIV . "','0','-{$amt}','0','0.00')";
$run_purch = db_exec($purch_sql) or errDie("Unable to update supplier purchase information.");
db_conn('contract');
$upd_sql = "UPDATE supp_creditor_run_cheques SET remarks = '{$remark[$id]}' WHERE id = '{$id}'";
$run_upd = db_exec($upd_sql) or errDie("Unable to update contract remittance.");
#also update the run listing
$upd2_sql = "UPDATE credit_runs SET remarks = '{$remark[$id]}' WHERE entry_id = '{$id}'";
$run_upd2 = db_exec($upd2_sql) or errDie("Unable to update creditor run information.");
pglib_transaction("COMMIT");
}
##PTH/CUBIT
// db_conn ("contract");
// #process the remarks
// foreach ($conid AS $each => $own){
// $upd_sql = "UPDATE contract_recs SET remarks = '$remark[$each]' WHERE conid = '$own' AND remit = '$remit[$each]'";
// $run_upd = db_exec($upd_sql) or errDie("Unable to update contract remittance.");
// }
return get_list($_POST, "<li class='err'>Cheques Have been recorded.</li>");
}
function write($_POST)
{
# Get vars
extract($_POST);
if (!isset($proc_trans) or !is_array($proc_trans)) {
return slctacc($_POST, "<li class='err'>Please Select Transaction(s) To Process</li>");
}
db_connect();
# validate input
require_lib("validate");
$v = new validate();
foreach ($proc_trans as $procid => $value) {
$get_trans = "SELECT * FROM cust_trans_batch WHERE id = '{$procid}' LIMIT 1";
$run_trans = db_exec($get_trans) or errDie("Unable to get transaction information.");
if (pg_numrows($run_trans) < 1) {
return slctacc($_POST, "<li class='err'>Transaction Not Found: (ID:{$procid})</li>");
}
$parr = pg_fetch_array($run_trans);
$v->isOk($parr['cusnum'], "num", 1, 50, "Invalid Customer number.");
$v->isOk($parr['contra_account'], "num", 1, 50, "Invalid Contra Account.");
$v->isOk($parr['ref_num'], "num", 1, 10, "Invalid Reference number.");
$v->isOk($parr['amount'], "float", 1, 20, "Invalid Amount.");
$v->isOk($parr['description'], "string", 0, 255, "Invalid Details.");
// $v->isOk ($author, "string", 1, 30, "Invalid Authorising person name.");
$datea = explode("-", $parr['proc_date']);
if (count($datea) == 3) {
if (!checkdate($datea[1], $datea[2], $datea[0])) {
$v->isOk($parr['proc_date'], "num", 1, 1, "Invalid date. (1)");
}
} else {
$v->isOk($parr['proc_date'], "num", 1, 1, "Invalid date. (2)");
}
$v->isOk($parr['chrg_vat'], "string", 1, 10, "Invalid Charge VAT Option.");
// $v->isOk ($vatinc, "string", 1, 10, "Invalid VAT Inclusive Exclusive Option.");
$v->isOk($parr['vatcode'], "num", 1, 5, "Invalid Vat Code Option.");
}
# display errors, if any
if ($v->isError()) {
$write = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$write .= "<li class='err'>" . $e["msg"] . "</li>";
}
$write .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $write;
}
foreach ($proc_trans as $procid => $value) {
db_connect();
$get_trans = "SELECT * FROM cust_trans_batch WHERE id = '{$procid}' LIMIT 1";
$run_trans = db_exec($get_trans) or errDie("Unable to get transaction information.");
if (pg_numrows($run_trans) < 1) {
return slctacc($_POST, "<li class='err'>Transaction Not Found: (ID:{$procid})</li>");
}
$parr = pg_fetch_array($run_trans);
$cusnum = $parr['cusnum'];
$vatinc = $parr['chrg_vat'];
if (isset($vatinc) and $vatinc != "0") {
$chrgvat = "yes";
} else {
$chrgvat = "no";
}
// $chrgvat = $parr['chrg_vat'];
$vatcode = $parr['vatcode'];
$amount = $parr['amount'];
$type = 1;
$entry = $parr['entry_type'];
$date = $parr['proc_date'];
$datea = explode("-", $parr['proc_date']);
$accid = $parr['contra_account'];
$refnum = $parr['ref_num'];
$details = $parr['description'];
if (isset($chrgvat) and $chrgvat == "yes") {
db_connect();
#get selected vatcode
$get_vatcode = "SELECT * FROM vatcodes WHERE id = '{$vatcode}' LIMIT 1";
$run_vatcode = db_exec($get_vatcode) or errDie("Unable to get vat code information.");
if (pg_numrows($run_vatcode) < 1) {
#vatcode not found ....
return "<li class='err'>Unable to get vat code information.</li>";
}
$vd = pg_fetch_array($run_vatcode);
if ($vatinc == "inc") {
#vat inc ... recalc value
$vatamt = sprint($amount * ($vd['vat_amount'] / (100 + $vd['vat_amount'])));
$amount = sprint($amount - $vatamt);
} else {
#vat excl
$amount = sprint($amount);
$vatamt = sprint($amount / 100 * $vd['vat_amount']);
}
} else {
#vat not set
$amount = sprint($amount);
$vatamt = sprint(0);
}
// $date = "$datea[2]-$datea[1]-$datea[0]";
# Accounts details
$accRs = get("core", "*", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
# Select customer
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($custRslt) < 1) {
return slctacc($_POST, "<li class='err'>Invalid customer ID, or customer has been blocked.</li>");
} else {
$cust = pg_fetch_array($custRslt);
}
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cust['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
return slctacc($_POST, "<i class='err'>Department Not Found</i>");
} else {
$dept = pg_fetch_array($deptRslt);
}
#get vat acc ...
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "VAT");
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# Probe tran type
if ($entry == "CT") {
# Write transaction (debit contra account, credit debtors control)
writetrans($accid, $dept['debtacc'], $date, $refnum, $amount, $details . " - Customer {$cust['cusname']} {$cust['surname']}");
$tran = "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t <td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t <td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t </tr>";
$samount = $amount - $amount * 2;
$svatamt = $vatamt - $vatamt * 2;
recordCT($samount, $cust['cusnum'], $date);
$type = 'c';
if (isset($chrgvat) and $chrgvat == "yes") {
writetrans($vatacc, $dept['debtacc'], $date, $refnum, $vatamt, "VAT for Transaction: {$refnum} for Customer : {$cust['cusname']} {$cust['surname']}");
vatr($vd['id'], $date, "OUTPUT", $vd['code'], $refnum, "VAT for Transaction: {$refnum} for Customer : {$cust['cusname']} {$cust['surname']}", $samount + $svatamt, $svatamt);
}
} else {
# Write transaction (debit debtors control, credit contra account)
writetrans($dept['debtacc'], $accid, $date, $refnum, $amount, $details . " - Customer {$cust['cusname']} {$cust['surname']}");
$tran = "\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t</tr>";
$samount = $amount;
$svatamt = $vatamt;
recordDT($samount, $cust['cusnum'], $date);
$type = 'd';
if (isset($chrgvat) and $chrgvat == "yes") {
writetrans($dept['debtacc'], $vatacc, $date, $refnum, $vatamt, "VAT for Transaction: {$refnum} for Customer : {$cust['cusname']} {$cust['surname']}");
vatr($vd['id'], $date, "OUTPUT", $vd['code'], $refnum, "VAT for Transaction: {$refnum} for Customer : {$cust['cusname']} {$cust['surname']}", $amount + $vatamt, $vatamt);
}
}
db_connect();
$sdate = date("Y-m-d");
# record the payment on the statement
$sql = "\n\t\t\tINSERT INTO stmnt (\n\t\t\t\tcusnum, invid, amount, date, type, st, div, allocation_date\n\t\t\t) VALUES (\n\t\t\t\t'{$cust['cusnum']}', '0', '" . sprint($samount + $svatamt) . "', '{$date}', '{$details}', 'n', '" . USER_DIV . "', '{$date}'\n\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
$sql = "\n\t\t\tINSERT INTO open_stmnt (\n\t\t\t\tcusnum, invid, amount, balance, date, type, st, div\n\t\t\t) VALUES (\n\t\t\t\t'{$cust['cusnum']}', '0', '" . sprint($samount + $svatamt) . "', '" . sprint($samount + $svatamt) . "', '{$date}', '{$details}', 'n', '" . USER_DIV . "'\n\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$samount}') WHERE cusnum = '{$cust['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF);
# Make ledge record
// custledger($cust['cusnum'], $accid, $date, $refnum, $details, $amount, $type);
custledger($cust['cusnum'], $accid, $date, $refnum, $details, sprint($amount + $vatamt), $type);
db_connect();
$rem_batch = "DELETE FROM cust_trans_batch WHERE id = '{$procid}'";
$run_batch = db_exec($rem_batch) or errDie("Unable to remove customer batch transaction information.");
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
}
return slctacc($_POST, "<li class='yay'>Transaction(s) Have Been Processed.</li><br>");
}
function sp($id, $amount, $description, $contra, $refnum, $date, $cheque, $bankid)
{
$id += 0;
$amount += 0;
$cheque += 0;
db_connect();
$Sl = "SELECT supid,supno,supname,deptid FROM suppliers WHERE supid = '{$id}' AND div = '" . USER_DIV . "'";
$Ri = db_exec($Sl) or errDie("Unable to get supplier data.");
$sup = pg_fetch_array($Ri);
core_connect();
$Sl = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "'";
$Ri = db_exec($Sl) or errDie("Unable to retrieve bank account link from Cubit", SELF);
if (pg_numrows($Ri) < 1) {
return "<li class='err'> ERROR : The bank account that you selected doesn't appear to have an account linked to it.";
}
$bank = pg_fetch_array($Ri);
db_conn("exten");
$Sl = "SELECT credacc FROM departments WHERE deptid ='{$sup['deptid']}' AND div = '" . USER_DIV . "'";
$Ri = db_exec($Sl) or errDie("Unable to get department data.");
$dept = pg_fetch_array($Ri);
$out = $amount;
$confirm = "";
$ids = "";
$purids = "";
$pamounts = "";
$pdates = "";
db_connect();
$Sl = "SELECT id,purid AS invid,intpurid AS invid2,balance,pdate FROM suppurch WHERE supid='{$id}' AND balance > 0 AND div='" . USER_DIV . "' ORDER BY pdate ASC";
$Ri = db_exec($Sl) or errDie("unable to get invoices.");
$i = 0;
while ($inv = pg_fetch_array($Ri) and $out > 0) {
if ($inv['invid2'] > 0) {
$inv['invid'] = $inv['invid2'];
}
$invid = $inv['invid'];
if ($out >= $inv['balance']) {
$val = $inv['balance'];
$out = $out - $inv['balance'];
} else {
$val = $out;
$out = 0;
}
$Sl = "UPDATE suppurch SET balance = (balance - '{$val}'::numeric(13,2)) WHERE id='{$inv['id']}'";
$Rl = db_exec($Sl) or errDie("Unable to update Invoice information in Cubit.", SELF);
$ids .= "|{$inv['id']}";
$purids .= "|{$invid}";
$pamounts .= "|{$val}";
$pdates .= "|{$inv['pdate']}";
}
$samount = $amount - $amount * 2;
if ($out > 0) {
recordDT($out, $sup['supid'], $date);
}
$Sl = "INSERT INTO sup_stmnt (supid, amount, edate, descript,ref,cacc, div) VALUES ('{$sup['supid']}','{$samount}','{$date}', 'Payment','{$cheque}','{$bank['accnum']}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
db_connect();
$Sl = "UPDATE suppliers SET balance = (balance - '{$amount}'::numeric(13,2)) WHERE supid = '{$sup['supid']}'";
$Ri = db_exec($Sl) or errDie("Unable to update invoice in Cubit.", SELF);
suppledger($sup['supid'], $bank['accnum'], $date, $cheque, "Payment for purchases", $amount, "d");
db_conn('cubit');
$Sl = "\n\t\tINSERT INTO cashbook (\n\t\t\tbankid, trantype, date, name, descript, \n\t\t\tcheqnum, amount, banked, accinv, supid, ids, \n\t\t\tpurids, pamounts, pdates, div\n\t\t) VALUES (\n\t\t\t'{$bankid}', 'withdrawal', '{$date}', '{$sup['supno']} - {$sup['supname']}', 'Supplier Payment to {$sup['supname']}', \n\t\t\t'{$cheque}', '{$amount}', 'no', '{$dept['credacc']}', '{$sup['supid']}', '{$ids}', \n\t\t\t'{$purids}', '{$pamounts}', '{$pdates}', '" . USER_DIV . "'\n\t\t)";
$Ri = db_exec($Sl) or errDie("Unable to add bank payment to database.", SELF);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}'";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 1) {
return "Invalid bank acc.";
}
$link = pg_fetch_array($Rx);
writetrans($dept['credacc'], $bank['accnum'], $date, $refnum, $amount, "Supplier Payment to {$sup['supname']}");
}
function calcPerc($type)
{
core_connect();
$intacc = gethook("accnum", "salesacc", "name", "SalesInt");
db_connect();
# Get all client that must be charged interest
$sql = "SELECT surname, cusnum, balance, fbalance, deptid, intrate, location, fcid FROM customers WHERE chrgint = 'yes' AND (balance > 0 OR fbalance > 0) AND div = '" . USER_DIV . "'";
$rs = db_exec($sql) or errDie("Unable to get clients from Cubit.");
if (pg_numrows($rs) < 1) {
# Return if they are not charging any interest
return;
}
#go through matching customers ...
while ($clnt = pg_fetch_array($rs)) {
if ($clnt['location'] != 'int') {
#local customer ...
#figure out the percentage ... based on setting ...
if ($type == 'brac') {
# Get from bracket
$sql = "SELECT percentage as perc FROM intbracs WHERE min <= '{$clnt['balance']}' AND max >= '{$clnt['balance']}'";
$bRs = db_exec($sql);
if (pg_numrows($bRs) > 0) {
$brac = pg_fetch_array($bRs);
$perc = $brac['perc'];
} else {
$perc = 0;
}
} elseif ($type[0] == 'r') {
$perc = $clnt['intrate'];
} else {
$perc = $type;
}
//calculate the amount to charge interest on ...
$overdue = getOverdue($clnt['cusnum']);
# Get Perc of overdue
$pamt = sprint($perc / 12 / 100 * $overdue);
$totamt = $pamt + $clnt['balance'];
$ret[$clnt['cusnum']] = $totamt;
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$clnt['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
return "<i class='err'>Department Not Found</i>";
} else {
$dept = pg_fetch_array($deptRslt);
}
if ($pamt > 0) {
$sdate = date("Y-m-d");
$invnum = intInvoice($clnt['cusnum'], $pamt, $intacc);
db_connect();
# Record the payment on the statement
$sql = "\n\t\t\t\t\tINSERT INTO stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$clnt['cusnum']}', '{$invnum}', '{$pamt}', '{$sdate}', \n\t\t\t\t\t\t'Interest on Outstanding balance', '" . USER_DIV . "', '{$sdate}'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
$sql = "\n\t\t\t\t\tINSERT INTO open_stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, balance, date, \n\t\t\t\t\t\ttype, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$clnt['cusnum']}', '{$invnum}', '{$pamt}', '{$pamt}','{$sdate}', \n\t\t\t\t\t\t'Interest on Outstanding balance', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$pamt}'::numeric(13,2)) WHERE cusnum = '{$clnt['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Make ledge record
custledger($clnt['cusnum'], $intacc, $sdate, 0, "Interest on Outstanding balance", $pamt, "d");
# Write transaction (debit debtors control, credit contra account)
$refnum = getrefnum();
writetrans($dept['debtacc'], $intacc, date("d-m-Y"), $refnum, $pamt, "Interest Received from customer : {$clnt['surname']}.");
recordDT($pamt, $clnt['cusnum']);
}
} else {
if ($type == 'brac') {
# Get from bracket
$sql = "SELECT percentage as perc FROM intbracs WHERE min <= '{$clnt['balance']}' AND max >= '{$clnt['balance']}'";
$bRs = db_exec($sql);
if (pg_numrows($bRs) > 0) {
$brac = pg_fetch_array($bRs);
$perc = $brac['perc'];
} else {
$perc = 0;
}
} elseif ($type[0] == 'r') {
$perc = $clnt['intrate'];
} else {
$perc = $type;
}
// $overdue = getfOverdue($clnt['cusnum']);
$overdue = getOverdue($clnt['cusnum']);
$rate = sprint($clnt['balance'] / $clnt['fbalance']);
# Get Perc of overdue
$pamt = sprint($perc / 12 / 100 * $overdue);
$totamt = $pamt + $clnt['fbalance'];
$ret[$clnt['cusnum']] = $totamt;
if ($rate == 0) {
$rate = 1;
}
$ltotamt = sprint($totamt * $rate);
$lpamt = sprint($ltotamt - $clnt['balance']);
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$clnt['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
return "<i class='err'>Department Not Found</i>";
} else {
$dept = pg_fetch_array($deptRslt);
}
if ($pamt > 0) {
$sdate = date("Y-m-d");
$invnum = fintInvoice($clnt['cusnum'], $pamt, $rate);
db_connect();
# Record the payment on the statement
$sql = "\n\t\t\t\t\tINSERT INTO stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, \n\t\t\t\t\t\ttype, div, allocation_date\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$clnt['cusnum']}', '{$invnum}', '{$pamt}','{$sdate}', \n\t\t\t\t\t\t'Interest on Outstanding balance', '" . USER_DIV . "', '{$sdate}'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the customer (make balance more)
$sql = "UPDATE customers SET fbalance = (fbalance + '{$pamt}'::numeric(13,2)), balance = '{$ltotamt}'::numeric(13,2) WHERE cusnum = '{$clnt['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Make ledge record
custledger($clnt['cusnum'], $intacc, $sdate, 0, "Interest on Outstanding balance", $lpamt, "d");
# Write transaction (debit debtors control, credit contra account)
$refnum = getrefnum();
writetrans($dept['debtacc'], $intacc, date("d-m-Y"), $refnum, $lpamt, "Interest Received from customer : {$clnt['surname']}.");
frecordDT($pamt, $lpamt, $clnt['cusnum'], $clnt['fcid']);
}
}
}
return $ret;
}
function do_trans($_POST)
{
extract($_POST);
global $complete;
#use the perm file we saved in the previous function ...
$filename_path = $filename_path . "1";
#now parse it into $complete
parseXML($filename_path);
db_connect();
//print "<pre>";
//var_dump($complete);
//print "</pre>";
pglib_transaction("BEGIN") or errDie("Unable to start transaction.");
if (isset($complete["DEBTOR"]) and is_array($complete["DEBTOR"])) {
foreach ($complete["DEBTOR"] as $jobjs) {
$parms = $jobjs->cols;
$debtor = $complete["DEBTOR"][$parms["iid"]]->cols;
if (!isset($debtadd[$parms["accno"]]) or strlen($debtadd[$parms["accno"]]) < 1) {
continue;
}
#this debtor is checked for adding ... so add it
$ins_sql = "\n\t\t\t\tINSERT INTO customers (\n\t\t\t\t\taccno,surname,title,init,category,class,paddr1,addr1,contname,bustel,tel,cellno,fax,email,saleterm,traddisc,setdisc,pricelist,chrgint,overdue,chrgvat,credterm,odate,credlimit,blocked,deptid,vatnum,div,url,ddiv,intrate,balance,day30,day60,day90,day120,classname,catname,deptname,fbalance,fcid,location,currency,lead_source,comments,del_addr1,sales_rep,bankname,branname,brancode,bankaccno,bankaccname,team_id,registration,bankacctype\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$parms['accno']}','{$parms['surname']}','{$parms['title']}','{$parms['init']}','{$parms['category']}','{$parms['class']}','{$parms['paddr1']}','{$parms['addr1']}','{$parms['contname']}','{$parms['bustel']}','{$parms['tel']}','{$parms['cellno']}','{$parms['fax']}','{$parms['email']}','{$parms['saleterm']}','{$parms['traddisc']}','{$parms['setdisc']}','{$parms['pricelist']}','{$parms['chrgint']}','{$parms['overdue']}','{$parms['chrgvat']}','{$parms['credterm']}','{$parms['odate']}','{$parms['credlimit']}','{$parms['blocked']}','{$parms['deptid']}','{$parms['vatnum']}','{$parms['div']}','{$parms['url']}','{$parms['ddiv']}','{$parms['intrate']}','{$parms['balance']}','{$parms['day30']}','{$parms['day60']}','{$parms['day90']}','{$parms['day120']}','{$parms['classname']}','{$parms['catname']}','{$parms['deptname']}','{$parms['fbalance']}','{$parms['fcid']}','{$parms['location']}','{$parms['currency']}','{$parms['lead_source']}','{$parms['comments']}','{$parms['del_addr1']}','{$parms['sales_rep']}','{$parms['bankname']}','{$parms['branname']}','{$parms['brancode']}','{$parms['bankaccno']}','{$parms['bankaccname']}','{$parms['team_id']}','{$parms['registration']}','{$parms['bankacctype']}'\n\t\t\t\t)";
$run_ins = db_exec($ins_sql) or errDie("Unable to add debtor information.");
}
}
if (isset($complete["CREDITOR"]) and is_array($complete["CREDITOR"])) {
foreach ($complete["CREDITOR"] as $jobjs) {
$parms = $jobjs->cols;
$creditor = $complete["CREDITOR"][$parms["iid"]]->cols;
if (!isset($suppadd[$parms["supno"]]) or strlen($suppadd[$parms["supno"]]) < 1) {
continue;
}
#this creditor is checked for adding ... so add it
$ins_sql = "\n\t\t\t\tINSERT INTO suppliers (\n\t\t\t\t\tsupno,supname,supaddr,contname,tel,fax,email,bankname,branname,brancode,bankaccno,deptid,vatnum,div,url,ddiv,balance,listid,fbalance,fcid,location,currency,lead_source,comments,branch,groupid,reference,bee_status,team_id,registration,bankaccname,bankacctype\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$parms['supno']}','{$parms['supname']}','{$parms['supaddr']}','{$parms['contname']}','{$parms['tel']}','{$parms['fax']}','{$parms['email']}','{$parms['bankname']}','{$parms['branname']}','{$parms['brancode']}','{$parms['bankaccno']}','{$parms['deptid']}','{$parms['vatnum']}','{$parms['div']}','{$parms['url']}','{$parms['ddiv']}','{$parms['balance']}','{$parms['listid']}','{$parms['fbalance']}','{$parms['fcid']}','{$parms['location']}','{$parms['currency']}','{$parms['lead_source']}','{$parms['comments']}','{$parms['branch']}','{$parms['groupid']}','{$parms['reference']}','{$parms['bee_status']}','{$parms['team_id']}','{$parms['registration']}','{$parms['bankaccname']}','{$parms['bankacctype']}'\n\t\t\t\t)";
$run_ins = db_exec($ins_sql) or errDie("Unable to add supplier information.");
}
}
$sdate = date("Y-m-d");
if (isset($complete["JOURNAL"]) and is_array($complete["JOURNAL"])) {
foreach ($complete["JOURNAL"] as $jobjs) {
$parms = $jobjs->cols;
$doid = $jobjs->id;
#check if we should run this transaction
if (!isset($replay[$doid]) or strlen($replay[$doid]) < 1) {
continue;
}
if (!isset($parms["debitacc"])) {
$parms["debitacc"] = "0";
}
db_connect();
switch ($jobjs->type) {
case "DEBTOR":
$debtor = $complete["DEBTOR"][$parms["iid"]]->cols;
if (!isset($parms['creditacc'])) {
$parms['creditacc'] = "0";
}
if ($parms['debitacc'] == '0' and $parms['creditacc'] == '0') {
#its not 1 of the custom saves ... so do generic ...
# record the payment on the statement
$sql = "\n\t\t\t\t\t\t\tINSERT INTO stmnt (\n\t\t\t\t\t\t\t\tcusnum, invid, amount, date, type, st, div, allocation_date\n\t\t\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t\t\t'{$parms['iid']}', '0', '{$parms['amount']}', '{$parms['date']}', '{$parms['details']}', 'n', '" . USER_DIV . "', '{$parms['date']}'\n\t\t\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
$sql = "INSERT INTO open_stmnt(cusnum, invid, amount, balance, date, type, st, div) VALUES('{$parms['iid']}', '0', '{$parms['amount']}', '{$parms['amount']}', '{$parms['date']}', '{$parms['details']}', 'n', '" . USER_DIV . "')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$parms['amount']}') WHERE cusnum = '{$parms['iid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF);
} else {
if ($parms['debitacc'] == '0') {
if ($parms['creditacc'] == '1') {
recordCT($parms['amount'], $parms['iid'], $parms['date']);
} else {
custledger($parms['iid'], $parms['creditacc'], $parms['date'], $parms['refno'], $parms['details'], $parms['amount'], 'c');
}
} elseif ($parms['creditacc'] == '0') {
if ($parms['debitacc'] == '1') {
recordDT($parms['amount'], $parms['iid'], $parms['date']);
} else {
custledger($parms['iid'], $parms['creditacc'], $parms['date'], $parms['refno'], $parms['details'], $parms['amount'], 'd');
}
}
}
break;
case "CREDITOR":
$creditor = $complete["CREDITOR"][$parms["iid"]]->cols;
if ($parms['debitacc'] == '0') {
if ($parms['creditacc'] == '1') {
recordCT(-$parms['amount'], $parms['iid'], $parms['date']);
} else {
suppledger($parms['iid'], $parms['creditacc'], $parms['date'], $parms['refno'], $parms['details'], $parms['amount'], 'c');
}
} elseif ($parms['creditacc'] == '0') {
if ($parms['debitacc'] == '1') {
recordDT($parms['amount'], $parms['iid'], $parms['date']);
} else {
suppledger($parms['iid'], $parms['debitacc'], $parms['date'], $parms['refno'], $parms['details'], $parms['amount'], 'd');
}
} elseif ($parms['debitacc'] == "9999" or $parms['creditacc'] == "9999") {
# record the payment on the statement
$sql = "INSERT INTO sup_stmnt(supid, edate, ref, cacc, descript, amount, div) VALUES('{$parms['iid']}', '{$parms['date']}', '0', '{$parms['refno']}', '{$parms['details']}', '{$parms['amount']}', '" . USER_DIV . "')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the supplier (make balance more)
$sql = "UPDATE suppliers SET balance = (balance + '{$parms['amount']}') WHERE supid = '{$parms['iid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update supplier in Cubit.", SELF);
} else {
#do nothing ?
#its not 1 of the custom saves ... so do generic ...
// db_connect();
// $sql = "INSERT INTO sup_stmnt(supid, edate, ref, cacc, descript, amount, div) VALUES('$parms[iid]', '$parms[date]', '0', '$parms[vat]', '$parms[details]', '$parms[amount]', '".USER_DIV."')";
// $stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.",SELF);
// # update the supplier (make balance more)
// $sql = "UPDATE suppliers SET balance = (balance + '$parms[amount]') WHERE supid = '$parms[iid]' AND div = '".USER_DIV."'";
// $rslt = db_exec($sql) or errDie("Unable to update supplier in Cubit.",SELF);
}
break;
case "STOCK":
$stock = $complete["STOCK"][$parms["iid"]]->cols;
if ($parms['debitacc'] == "0" and $parms['creditacc'] == "0") {
$sql = "UPDATE stock SET csamt = (csamt - {$parms['amount']}), units = (units - '{$parms['refno']}') WHERE stkid = '{$parms['iid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
} elseif ($parms['debitacc'] == "1" and $parms['creditacc'] == "1") {
$sql = "UPDATE stock\n\t\t\t\t\t\t\t\tSET units = (units + '{$parms['refno']}'),\n\t\t\t\t\t\t\t\t\tlcsprice = '{$parms['vat']}',\n\t\t\t\t\t\t\t\t\tcsamt = (csamt + {$parms['amount']}),\n\t\t\t\t\t\t\t\t\tcsprice = (\n\t\t\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t\tCASE WHEN (units != -{$parms['refno']}) THEN (csamt+{$parms['amount']})/(units+{$parms['refno']})\n\t\t\t\t\t\t\t\t\t\t\tELSE 0\n\t\t\t\t\t\t\t\t\t\tEND\n\t\t\t\t\t\t\t\t\t\tFROM cubit.stock\n\t\t\t\t\t\t\t\t\t\tWHERE stkid='{$parms['iid']}' AND div='" . USER_DIV . "'\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\tWHERE stkid = '{$parms['iid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
} else {
if ($parms['debitacc'] == "1" and $parms['creditacc'] == "0") {
#first get the stock information
$sql = "SELECT * FROM stock WHERE stkid = '{$parms['iid']}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($stkRslt) < 1) {
return "<li> Invalid Stock ID.</li>";
} else {
$stk = pg_fetch_array($stkRslt);
}
stockrec($stk['stkid'], $stk['stkcod'], $stk['stkdes'], 'dt', $parms['date'], $parms['refno'], $parms['amount'], $parms['details']);
$sql = "INSERT INTO stockrec (edate, stkid, stkcod, stkdes, trantype, qty, csprice, csamt, details, div)\n\t\t\t\t\t\t\t\t\t\t\t\tVALUES ('{$parms['date']}', '{$stk['stkid']}', '{$stk['stkcod']}', '{$stk['stkdes']}', 'inc', '{$parms['refno']}', '{$parms['amount']}', '{$parms['vat']}', '{$parms['details']}', '" . USER_DIV . "')";
$recRslt = db_exec($sql);
} elseif ($parms['debitacc'] == "0" and $parms['creditacc'] == "1") {
$sql = "SELECT * FROM stock WHERE stkid = '{$parms['iid']}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($stkRslt) < 1) {
return "<li> Invalid Stock ID.</li>";
} else {
$stk = pg_fetch_array($stkRslt);
}
stockrec($stk['stkid'], $stk['stkcod'], $stk['stkdes'], 'ct', $parms['date'], $parms['refno'], $parms['amount'], $parms['details']);
$parms[vat] += 0;
db_connect();
$sql = "INSERT INTO stockrec(edate, stkid, stkcod, stkdes, trantype, qty, csprice, csamt, details, div)\n\t\t\t\t\t\t\t\t\t\t\t\tVALUES('{$parms['date']}', '{$stk['stkid']}', '{$stk['stkcod']}', '{$stk['stkdes']}', 'dec', '-{$parms['refno']}', '{$parms['amount']}', '{$parms['vat']}', '{$parms['details']}', '" . USER_DIV . "')";
$recRslt = db_exec($sql);
# Units
if ($stk['units'] != 0) {
$sql = "UPDATE stock SET csprice = (csamt/units) WHERE stkid = '{$parms['iid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
} else {
$sql = "UPDATE stock SET csprice = '{$parms['vat']}' WHERE stkid = '{$parms['iid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
}
} else {
#nothing to do ...
}
}
break;
case "JOURNAL":
#process the writetrans
if ($parms['debitacc'] == "0" and $parms['creditacc'] == "0") {
print "WROTE JOURNAL: VAT<br>";
#we are dealing with vatr .. proceed
#get the compressed vars
$arrs = explode("|", $parms['details']);
vatr($arrs[1], $parms['date'], $arrs[2], $arrs[3], $parms['refno'], $arrs[0], $parms['amount'], $parms['vat']);
} else {
print "WROTE JOURNAL: TRANSACTION<br>";
writetrans($parms['debitacc'], $parms['creditacc'], $parms['date'], $parms['refno'], $parms['amount'], $parms['details']);
}
break;
}
}
}
pglib_transaction("COMMIT") or errDie("Unable to commit transactions.");
$display = "\n\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Transactions Completed</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>All Selected Replay Transactions Completed.</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>\n\t\t\t\t";
return $display;
}
function write_data($_POST)
{
extract($_POST);
if (isset($back)) {
return enter_data2($_POST);
}
db_conn('core');
$Sl = "SELECT accnum FROM salesacc WHERE name='VATIN'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) > 0) {
$vd = pg_fetch_array($Ri);
$vatin = $vd['accnum'];
} else {
$vatin = 0;
}
$Sl = "SELECT accnum FROM salesacc WHERE name='VATOUT'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) > 0) {
$vd = pg_fetch_array($Ri);
$vatout = $vd['accnum'];
} else {
$vatout = 0;
}
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE del='Yes'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
$Sl = "SELECT * FROM vatcodes WHERE zero='Yes'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please set up your vatcodes first.";
}
}
$vcd = pg_fetch_array($Ri);
db_conn('exten');
$Sl = "SELECT debtacc FROM departments";
$Ri = db_exec($Sl);
$dd = pg_fetch_array($Ri);
$cc = $dd['debtacc'];
db_conn('core');
$Sl = "SELECT * FROM accounts WHERE accname='Opening Balances / Suspense Account'";
$Ri = db_exec($Sl) or errDie("Unable to get account.");
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>There is no account called 'Opening Balances / Suspense Account'. <br>\n\t\tI Need that account.<br>\n\t\tPlease create it. <br><br>\n\t\tThank you.</li>";
}
$ad = pg_fetch_array($Ri);
$bala = $ad['accid'];
db_conn(PRD_DB);
# get last ref number
pglib_transaction("BEGIN");
$refnum = getrefnum();
/* check for main accounts whose sub accounts add up to same total,
then clear main account
check for main accounts whose sub accounts dont add up, then unblock
main accounts
*/
$sql = "SELECT * FROM cubit.import_data";
$rslt = db_exec($sql) or errDie("Error validating data.");
$acsub = $acmain = array();
while ($fd = pg_fetch_array($rslt)) {
$n = explode("/", $fd["des1"]);
if (!isset($n[1]) || $n[1] == "000") {
$n[1] = "000";
}
$a = array("num" => $fd["des1"], "name" => $fd["des2"], "dt" => $fd["des3"], "ct" => $fd["des4"]);
if ($n[1] == "000") {
$acmain["{$n['0']}"] = $a;
} else {
if (!isset($acsub[$n[0]])) {
$acsub[$n[0]] = array();
}
$acsub[$n[0]][] = $a;
}
}
/* match subs with mains */
$unblock_main = false;
foreach ($acmain as $k => $v) {
$totdt = 0;
$totct = 0;
if (isset($acsub[$k])) {
foreach ($acsub[$k] as $sk => $sv) {
$totdt += $sv["dt"];
$totct += $sv["ct"];
}
if ($totdt - $totct != $v["dt"] - $v["ct"]) {
$unblock_main = true;
} else {
$sql = "UPDATE cubit.import_data SET des3='0', des4='0'\n\t\t\t\t\t\tWHERE des1='{$v['num']}' AND des2='{$v['name']}'";
$rslt = db_exec($sql) or errDie("Error balancing main account with sub accounts: {$v['num']} - {$v['name']} with {$sv['num']} - {$sv['name']}.");
}
}
}
if ($unblock_main) {
$sql = "UPDATE cubit.set SET value = 'nuse', descript = 'Dont block main accounts'\n\t\t\t\tWHERE label = 'BLOCK' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Error unblocking main accounts.");
}
/* continue importing (validated/fixed) data */
db_conn('cubit');
$Sl = "SELECT * FROM import_data";
$Ri = db_exec($Sl);
$i = 0;
$tot_debit = 0;
$tot_credit = 0;
$date = mkdate(getYearOfFinMon($prd), $prd, 1);
db_conn('core');
while ($fd = pg_fetch_array($Ri)) {
$fid = $fd['id'];
$accs = explode('/', $fd['des1']);
$topacc = $accs[0];
$topacc = str_pad($topacc, 4, "0");
if (isset($accs[1])) {
$accnum = $accs[1];
} else {
$accnum = "000";
}
db_conn('core');
if ($accounts[$fid] == 0) {
$catss = explode(":", $cat[$fid]);
if ($catss[0] == "other_income" || $catss[0] == "sales") {
$catT = "I10";
$type = "I";
} elseif ($catss[0] == "expenses" || $catss[0] == "cost_of_sales") {
$catT = "E10";
$type = "E";
} else {
$catT = "B10";
$type = "B";
}
$Sl = "\n\t\t\t\tINSERT INTO accounts (\n\t\t\t\t\ttopacc, accnum, catid, accname, vat, \n\t\t\t\t\tdiv, toptype, acctype\n\t\t\t\t) VALUES (\n\t\t\t\t\t'{$topacc}', '{$accnum}', '{$catT}', '{$fd['des2']}', 'f', \n\t\t\t\t\t'" . USER_DIV . "', '{$catss['0']}', '{$type}'\n\t\t\t\t)";
$Rl = db_exec($Sl);
$accname = $fd['des2'];
$accid = pglib_lastid("accounts", "accid");
// $query = "INSERT INTO trial_bal(accid, topacc, accnum, accname, div) VALUES('$accid', '$topacc', '$accnum', '$fd[des2]', '".USER_DIV."')";
// $trialRslt = db_exec($query);
global $MONPRD;
insert_trialbal($accid, $topacc, $accnum, $accname, $type, 'f', USER_DIV);
for ($i = 1; $i <= 12; $i++) {
$periodname = getMonthName($i);
$sql = "INSERT INTO " . YR_DB . ".{$periodname} (accid, topacc, accnum, accname,\n\t\t\t\t\t\t\tdebit, credit, div)\n\t\t\t\t\t\tSELECT accid, topacc, accnum, accname, debit, credit, div\n\t\t\t\t\t\tFROM core.trial_bal WHERE month='{$i}' AND accid='{$accid}'";
db_exec($sql) or die($sql);
$sql = "INSERT INTO \"{$i}\".openbal (accid, accname, debit, credit, div)\n\t\t\t\t\t\tSELECT accid, accname, debit, credit, div\n\t\t\t\t\t\tFROM core.trial_bal WHERE month='{$i}' AND accid='{$accid}'";
db_exec($sql) or die($sql);
$sql = "INSERT INTO \"{$i}\".ledger (acc, contra, edate, eref, descript,\n\t\t\t\t\t\t\tcredit, debit, div, caccname, ctopacc, caccnum, cbalance, dbalance)\n\t\t\t\t\t\tSELECT accid, accid, CURRENT_DATE, '0', 'Balance', '0', '0', div,\n\t\t\t\t\t\t\taccname, topacc, accnum, credit, debit\n\t\t\t\t\t\tFROM core.trial_bal WHERE month='{$i}' AND accid='{$accid}'";
db_exec($sql) or die($sql);
}
$accounts[$fid] = $accid;
} else {
$Sl = "UPDATE accounts SET topacc='{$topacc}',accnum='{$accnum}',accname='{$fd['des2']}' WHERE accid='{$accounts[$fid]}'";
$Rl = db_exec($Sl);
$Sl = "UPDATE trial_bal SET topacc='{$topacc}',accnum='{$accnum}',accname='{$fd['des2']}' WHERE accid='{$accounts[$fid]}'";
$Rl = db_exec($Sl);
}
$Sl = "SELECT accid,accname FROM accounts WHERE accid='{$accounts[$fid]}'";
$Rx = db_exec($Sl);
$ad = pg_fetch_array($Rx);
$i++;
$debit = $fd['des3'];
$credit = $fd['des4'];
if ($debit > 0) {
writetrans($ad['accid'], $bala, $date, $refnum, sprint($debit), "Opening balance imported");
}
if ($credit > 0) {
writetrans($bala, $ad['accid'], $date, $refnum, sprint($credit), "Opening balance imported");
}
$tot_debit += $fd['des3'];
$tot_credit += $fd['des4'];
if ($ad['accid'] == $vatin) {
vatr($vcd['id'], $date, "INPUT", $vcd['code'], $refnum, "Opening balance VAT imported", sprint($credit - $debit), sprint($credit - $debit));
}
if ($ad['accid'] == $vatout) {
vatr($vcd['id'], $date, "OUTPUT", $vcd['code'], $refnum, "Opening balance VAT imported", sprint($credit - $debit), sprint($credit - $debit));
}
}
$tot_debit = sprint($tot_debit);
$tot_credit = sprint($tot_credit);
if ($cc_tot > 0) {
$tot = array_sum($cbalance);
if (sprint($cc_tot) != sprint($tot)) {
return enter_data2($_POST) . "<li class='err'>The total amount for balances for customers you entered is: " . CUR . " {$tot}, the\n\t\t\ttotal for the control account is: " . sprint($cc_tot) . ". These need to be the same.</li>";
}
db_conn('cubit');
$Sl = "SELECT cusnum,accno,surname FROM customers ORDER BY surname";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>If you want to import your customer control account you need to add customers first</li>";
}
$tot = 0;
while ($cd = pg_fetch_array($Ri)) {
$cid = $cd['cusnum'];
$cbalance[$cid] = sprint($cbalance[$cid]);
if ($cbalance[$cid] > 0) {
db_conn('cubit');
# Update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$cbalance[$cid]}') WHERE cusnum = '{$cid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
$sql = "\n\t\t\t\t\tINSERT INTO stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, type, \n\t\t\t\t\t\tst, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cid}', '0', '{$cbalance[$cid]}', '{$date}', 'Opening Balance Imported', \n\t\t\t\t\t\t'n', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
$sql = "\n\t\t\t\t\tINSERT INTO open_stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, balance, date, \n\t\t\t\t\t\ttype, st, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cid}', '0', '{$cbalance[$cid]}', '{$cbalance[$cid]}', '{$date}', \n\t\t\t\t\t\t'Opening Balance Imported', 'n', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
crecordDT($cbalance[$cid], $cid, $date);
custledger($cid, $bala, $date, 0, "Opening Balance Imported", $cbalance[$cid], "d");
} elseif ($cbalance[$cid] < 0) {
db_conn('cubit');
# Update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$cbalance[$cid]}') WHERE cusnum = '{$cid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
$sql = "\n\t\t\t\t\tINSERT INTO stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, date, type, \n\t\t\t\t\t\tst, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cid}', '0', '{$cbalance[$cid]}', '{$date}', 'Opening Balance Imported', \n\t\t\t\t\t\t'n', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
$sql = "\n\t\t\t\t\tINSERT INTO open_stmnt (\n\t\t\t\t\t\tcusnum, invid, amount, balance, date, \n\t\t\t\t\t\ttype, st, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$cid}', '0', '{$cbalance[$cid]}', '{$cbalance[$cid]}', '{$date}', \n\t\t\t\t\t\t'Opening Balance Imported', 'n', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
crecordCT(-$cbalance[$cid], $cid, $date);
custledger($cid, $bala, $date, 0, "Opening Balance Imported", -$cbalance[$cid], "c");
}
$i++;
$tot += $cbalance[$cid];
}
}
if ($sc_tot > 0) {
db_conn('cubit');
$Sl = "SELECT supid,supno,supname FROM suppliers ORDER BY supname";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>If you want to import your supplier control account you need to add suppliers first</li>";
}
$tot = 0;
while ($cd = pg_fetch_array($Ri)) {
$sid = $cd['supid'];
$sbalance[$sid] += 0;
if ($sbalance[$sid] > 0) {
db_conn('cubit');
$sql = "UPDATE suppliers SET balance = (balance + '{$sbalance[$sid]}') WHERE supid = '{$sid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update supplier in Cubit.", SELF);
$sql = "\n\t\t\t\t\tINSERT INTO sup_stmnt (\n\t\t\t\t\t\tsupid, edate, ref, cacc, descript, \n\t\t\t\t\t\tamount, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$sid}', '{$date}', '0', '{$bala}', 'Opening balance imported', \n\t\t\t\t\t\t'{$sbalance[$sid]}', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
recordCT(-$sbalance[$sid], $sid, $date);
suppledger($sid, $bala, $date, $refnum, "Opening balance imported", $sbalance[$sid], "c");
} elseif ($sbalance[$sid] < 0) {
db_conn('cubit');
$sql = "UPDATE suppliers SET balance = (balance + '{$sbalance[$sid]}') WHERE supid = '{$sid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update supplier in Cubit.", SELF);
$sql = "\n\t\t\t\t\tINSERT INTO sup_stmnt (\n\t\t\t\t\t\tsupid, edate, ref, cacc, descript, amount, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$sid}', '{$date}', '0', '{$bala}', 'Opening balance imported', \n\t\t\t\t\t\t'{$sbalance[$sid]}', '" . USER_DIV . "'\n\t\t\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
recordDT(-$sbalance[$sid], $sid, $date);
suppledger($sid, $bala, $date, $refnum, "Opening balance imported", $sbalance[$sid], "d");
}
$i++;
$tot += $sbalance[$sid];
}
if (sprint($sc_tot) != sprint($tot)) {
return enter_data2($_POST) . "<li class='err'>The total amount for balances for suppliers you entered is: " . CUR . " {$tot}, the\n\t\t\ttotal for the control account is: " . sprint($sc_tot) . ". These need to be the same.</li>";
}
}
if ($sal_tot > 0) {
db_conn('cubit');
$Sl = "SELECT empnum,enum,sname,fnames FROM employees ORDER BY sname,fnames";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>If you want to import your employee control account you need to add employees first</li>";
}
$tot = 0;
while ($cd = pg_fetch_array($Ri)) {
$eid = $cd['empnum'];
if (!isset($ebalance[$eid])) {
$ebalance[$eid] = "";
}
$ebalance[$eid] = sprint($ebalance[$eid]);
db_conn('cubit');
$Sl = "UPDATE employees SET balance=balance+'{$ebalance[$eid]}' WHERE empnum = '{$eid}' AND div = '" . USER_DIV . "'";
$Rt = db_exec($Sl) or errDie("Unable to get employee details.");
if ($ebalance[$eid] > 0) {
empledger($eid, $bala, $date, $refnum, "Opening balance imported", $ebalance[$eid], "c");
} else {
empledger($eid, $bala, $date, $refnum, "Opening balance imported", abs($ebalance[$eid]), "d");
}
$i++;
$tot += $ebalance[$eid];
}
if (sprint($sal_tot) != sprint($tot)) {
return enter_data2($_POST) . "<li class='err'>The total amount for balances for employees you entered is: " . CUR . " {$tot}, the\n\t\t\ttotal for the control account is: " . sprint($sal_tot) . ". These need to be the same.</li>";
}
}
if ($i_tot > 0) {
db_conn('cubit');
$Sl = "SELECT stkid,stkcod,stkdes FROM stock ORDER BY stkcod";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>If you want to import your inventory control account you need to add stock first</li>";
}
$tot = 0;
while ($cd = pg_fetch_array($Ri)) {
$iid = $cd['stkid'];
if (!isset($ibalance[$iid])) {
$ibalance[$iid] = "";
}
if ($ibalance[$iid] > 0) {
$unitnum = $units[$iid];
db_connect();
$sql = "UPDATE stock SET csamt = (csamt + '{$ibalance[$iid]}'), units = (units + '{$unitnum}') WHERE stkid = '{$iid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
stockrec($cd['stkid'], $cd['stkcod'], $cd['stkdes'], 'dt', $date, $unitnum, $ibalance[$iid], "Inventory balance imported");
db_connect();
$cspric = sprint($ibalance[$iid] / $unitnum);
//$cspric = sprint(0);
$sql = "\n\t\t\t\t\tINSERT INTO stockrec (\n\t\t\t\t\t\tedate, stkid, stkcod, stkdes, trantype, qty, \n\t\t\t\t\t\tcsprice, csamt, details, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$date}', '{$cd['stkid']}', '{$cd['stkcod']}', '{$cd['stkdes']}', 'inc', '{$unitnum}', \n\t\t\t\t\t\t'{$ibalance[$iid]}', '{$cspric}', 'Inventory balance imported', '" . USER_DIV . "'\n\t\t\t\t\t)";
$recRslt = db_exec($sql);
db_connect();
$sql = "SELECT * FROM stock WHERE stkid = '{$iid}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($stkRslt) < 1) {
return "<li> Invalid Stock ID.</li>";
} else {
$stk = pg_fetch_array($stkRslt);
}
if ($stk['units'] != 0) {
$sql = "UPDATE stock SET csprice = (csamt/units) WHERE stkid = '{$iid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
} else {
$sql = "UPDATE stock SET csprice = '{$csprice}' WHERE stkid = '{$iid}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to insert stock to Cubit.", SELF);
}
}
$tot += $ibalance[$iid];
$i++;
}
if (sprint($i_tot) != sprint($tot)) {
return enter_data2($_POST) . "<li class='err'>The total amount for balances for inventory you entered is: " . CUR . " {$tot}, the\n\t\t\ttotal for the control account is: " . sprint($sal_tot) . ". These need to be the same.</li>";
}
}
$out = "\n\t\t<table " . TMPL_tblDflts . " width='50%'>\n\t\t\t<tr>\n\t\t\t\t<th>Data Imported</th>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td>Trial balance, has been successfully imported.</td>\n\t\t\t</tr>\n\t\t</table>";
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
block();
return $out;
}
function write($_POST)
{
# processes
db_connect();
# Get vars
extract($_POST);
if (isset($back)) {
return add($_POST);
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($bankid, "num", 1, 30, "Invalid Bank Account.");
$v->isOk($date, "date", 1, 10, "Invalid Date Entry.");
$v->isOk($cusnum, "num", 1, 20, "Invalid Customer account.");
$v->isOk($descript, "string", 0, 255, "Invalid Description.");
$v->isOk($reference, "string", 0, 50, "Invalid Reference Name/Number.");
$v->isOk($cheqnum, "num", 0, 30, "Invalid Cheque number.");
$v->isOk($amount, "float", 1, 10, "Invalid amount.");
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# date format
$date = explode("-", $date);
$date = $date[2] . "-" . $date[1] . "-" . $date[0];
# refnum
$refnum = getrefnum();
# cheq number
$cheqnum = 0 + $cheqnum;
# Get customer
$custRslt = get("cubit", "*", "customers", "cusnum", $cusnum);
$cust = pg_fetch_array($custRslt);
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cust['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
return "<i class='err'>Department Not Found</i>";
} else {
$dept = pg_fetch_array($deptRslt);
}
# 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.</li>";
}
$banklnk = pg_fetch_array($rslt);
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# DT(customer control), CT(bank)
writetrans($dept['debtacc'], $banklnk['accnum'], $date, $refnum, $amount, $descript);
recordDT($amount, $cust['cusnum'], $date);
# Record the payment record
db_connect();
$sql = "\n\t\t\tINSERT INTO cashbook (\n\t\t\t\tbankid, trantype, date, cusnum, name, descript, \n\t\t\t\tcheqnum, amount, vat, chrgvat, banked, accinv, reference, div\n\t\t\t) VALUES (\n\t\t\t\t'{$bankid}', 'withdrawal', '{$date}', '{$cusnum}', '({$cust['accno']}) {$cust['cusname']} {$cust['surname']}', '{$descript}', \n\t\t\t\t'{$cheqnum}', '{$amount}', '0', 'no', 'no', '{$dept['debtacc']}', '{$reference}', '" . USER_DIV . "'\n\t\t\t)";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
# record the payment on the statement
$sql = "\n\t\t\tINSERT INTO stmnt (\n\t\t\t\tcusnum, invid, amount, date, type, st, div, allocation_date\n\t\t\t) VALUES (\n\t\t\t\t'{$cust['cusnum']}', '0', '{$amount}', '{$date}', '{$descript}', 'n', '" . USER_DIV . "', '{$date}'\n\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# record the payment on the statement
$sql = "\n\t\t\tINSERT INTO open_stmnt (\n\t\t\t\tcusnum, invid, amount, date, type, st, div, balance\n\t\t\t) VALUES (\n\t\t\t\t'{$cust['cusnum']}', '0', '{$amount}', '{$date}', '{$descript}', 'n', '" . USER_DIV . "', '{$amount}'\n\t\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$amount}') WHERE cusnum = '{$cust['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF);
# Make ledge record
custledger($cust['cusnum'], $banklnk['accnum'], $date, $refnum, $descript, $amount, "d");
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
# Status report
$write = "\n\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t<tr>\n\t\t\t\t<th>Bank Payment</th>\n\t\t\t</tr>\n\t\t\t<tr class='datacell'>\n\t\t\t\t<td>Bank Payment added to cash book.</td>\n\t\t\t</tr>\n\t\t</table>";
# Main table (layout with menu)
$OUTPUT = "\n\t\t<center>\n\t\t<table width='90%'>\n\t\t\t<tr valign='top'>\n\t\t\t\t<td width='50%'>{$write}</td>\n\t\t\t\t<td align='center'>\n\t\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='datacell'>\n\t\t\t\t\t\t\t<td align='center'><a target=_blank href='../core/acc-new2.php'>Add account (New Window)</a></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td><a href='bank-pay-add.php'>Add Bank Payment</a></td></tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td><a href='bank-recpt-add.php'>Add Bank Receipt</a></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td><a href='cashbook-view.php'>View Cash Book</a></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t\t\t</table>\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>";
return $OUTPUT;
}
function details($_GET)
{
# get vars
extract($_GET);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($invid, "num", 1, 20, "Invalid invoice number.");
# display errors, if any
if ($v->isError()) {
$err = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# Get invoice info
db_connect();
$sql = "SELECT * FROM pinvoices WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to get invoice information");
if (pg_numrows($invRslt) < 1) {
return "<i class='err'>Not Found</i>";
}
$inv = pg_fetch_array($invRslt);
# CHECK IF THIS DATE IS IN THE BLOCKED RANGE
$blocked_date_from = getCSetting("BLOCKED_FROM");
$blocked_date_to = getCSetting("BLOCKED_TO");
if (strtotime($inv['odate']) >= strtotime($blocked_date_from) and strtotime($inv['odate']) <= 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 ($inv['rounding'] > 0) {
db_conn('core');
$Sl = "SELECT * FROM salesacc WHERE name='rounding'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please set the rounding account, under sales settings.";
}
$ad = pg_fetch_array($Ri);
$rac = $ad['accnum'];
}
if ($inv['cusnum'] != "0") {
#then get the actual customer
db_connect();
$get_cus = "SELECT * FROM customers WHERE cusnum = '{$inv['cusnum']}' LIMIT 1";
$run_cus = db_exec($get_cus) or errDie("Unable to get customer information");
if (pg_numrows($run_cus) < 1) {
#do nothing
} else {
$carr = pg_fetch_array($run_cus);
$inv['cusname'] = "{$carr['cusname']}";
$inv['surname'] = "{$carr['surname']}";
}
}
$td = $inv['odate'];
# check if invoice has been printed
if ($inv['printed'] == "y") {
$error = "<li class='err'> Error : Invoice number <b>{$invid}</b> has already been printed.";
$error .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $error;
}
db_conn('cubit');
$sql = "SELECT stkid FROM pinv_items WHERE invid = '{$inv['invid']}' AND div = '" . USER_DIV . "'";
$crslt = db_exec($sql);
if (pg_numrows($crslt) < 1) {
$error = "<li class='err'> Error : Invoice number <b>{$invid}</b> has no items.</li>";
$error .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $error;
}
if ($inv['terms'] == 1) {
db_conn('core');
$Sl = "SELECT * FROM salacc WHERE name='cc'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please set a link for the POS credit card control account";
}
$cd = pg_fetch_array($Ri);
$cc = $cd['accnum'];
}
$change = sprint(sprint($inv['pcash'] + $inv['pcheque'] + $inv['pcc'] + $inv['pcredit']) - sprint($inv['total'] - $inv['rounding']));
$inv['pcash'] = sprint($inv['pcash'] - $change);
if ($inv['pcash'] < 0) {
$inv['pcash'] = 0;
}
if (sprint($inv['pcash'] + $inv['pcheque'] + $inv['pcc'] + $inv['pcredit']) != sprint($inv['total'] - $inv['rounding'])) {
return "<li class='err'>The total of all the payments is not equal to the invoice total.<br>\n\t\tPlease edit the invoice and try again(You can only overpay with cash)</li>";
}
db_connect();
# Begin updates
#
//lock(2);
$invnum = divlastid('inv', USER_DIV);
$Sl = "INSERT INTO ncsrec (oldnum,newnum, div) VALUES ('{$invid}','{$invnum}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert into db");
//unlock(2);
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$inv['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
$dept['deptname'] = "<i class=err>Not Found</i>";
} else {
$dept = pg_fetch_array($deptRslt);
}
/* --- Start Products Display --- */
# Products layout
$products = "";
$disc = 0;
# get selected stock in this invoice
db_connect();
$sql = "SELECT * FROM pinv_items WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
$taxex = 0;
$commision = 0;
$salesp = qrySalesPersonN($inv["salespn"]);
while ($stkd = pg_fetch_array($stkdRslt)) {
$stkd['account'] += 0;
if ($stkd['account'] == 0) {
# get warehouse name
db_conn("exten");
$sql = "SELECT whname FROM warehouses WHERE whid = '{$stkd['whid']}' AND div = '" . USER_DIV . "'";
$whRslt = db_exec($sql);
$wh = pg_fetch_array($whRslt);
# get selected stock in this warehouse
db_connect();
$sql = "SELECT * FROM stock WHERE stkid = '{$stkd['stkid']}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql);
$stk = pg_fetch_array($stkRslt);
db_connect();
//this was set to the stock vatcode ??? must be the pur_item code ...
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatcode']}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "<li class='err'>Please select the vatcode for all your stock.</li>";
}
$vd = pg_fetch_array($Ri);
$sp = " ";
# Check Tax Excempt
if ($stk['exvat'] == 'yes' || $vd['zero'] == "Yes") {
$taxex += $stkd['amt'];
$ex = "#";
} else {
$ex = " ";
}
# Keep track of discounts
$disc += $stkd['disc'] * $stkd['qty'];
# Insert stock record
$sdate = date("Y-m-d");
$csprice = sprint($stk['csprice'] * $stkd['qty']);
# put in product
$products .= "\n\t\t\t\t<tr valign='top'>\n\t\t\t\t\t<td>{$stk['stkcod']}</td>\n\t\t\t\t\t<td>{$ex} {$sp} {$stk['stkdes']}</td>\n\t\t\t\t\t<td>{$stkd['qty']}</td>\n\t\t\t\t\t<td>" . sprint($stk["selamt"]) . "</td>\n\t\t\t\t\t<td>" . CUR . sprint($stkd["amt"]) . "</td>\n\t\t\t\t</tr>";
# Get amount exluding vat if including and not exempted
$VATP = TAX_VAT;
$amtexvat = sprint($stkd['amt']);
if ($inv['chrgvat'] == "inc" && $stk['exvat'] != 'yes') {
$amtexvat = sprint($stkd['amt'] * 100 / (100 + $VATP));
}
$sql = "\n\t\t\t\t\tINSERT INTO stockrec (\n\t\t\t\t\t\tedate, stkid, stkcod, stkdes, trantype, qty, csprice, \n\t\t\t\t\t\tcsamt, details, div\n\t\t\t\t\t) VALUES (\n\t\t\t\t\t\t'{$td}', '{$stkd['stkid']}', '{$stk['stkcod']}', '{$stk['stkdes']}', 'invoice', '{$stkd['qty']}', '{$stkd['amt']}', \n\t\t\t\t\t\t'{$csprice}', 'Stock sold - Invoice No. {$invnum}', '" . USER_DIV . "'\n\t\t\t\t\t)";
$recRslt = db_exec($sql);
if ($salesp["com"] > 0) {
$itemcommission = $salesp['com'];
} else {
$itemcommission = $stk["com"];
}
$commision = $commision + coms($inv['salespn'], $amtexvat, $itemcommission);
} else {
db_conn('core');
$Sl = "SELECT * FROM accounts WHERE accid='{$stkd['account']}'";
$Ri = db_exec($Sl) or errDie("Unable to get account data.");
$ad = pg_fetch_array($Ri);
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatcode']}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please select the vatcode for all your stock.";
}
$vd = pg_fetch_array($Ri);
$sp = "";
# Check Tax Excempt
if ($vd['zero'] == "Yes") {
$taxex += $stkd['amt'];
$ex = "#";
} else {
$ex = "";
}
# all must be excempted
if ($inv['chrgvat'] == 'nov') {
$ex = "#";
}
$VATP = TAX_VAT;
$amtexvat = sprint($stkd['amt']);
if ($inv['chrgvat'] == "inc") {
$amtexvat = sprint($stkd['amt'] * 100 / (100 + $VATP));
}
if ($salesp["com"] > 0) {
$itemcommission = $salesp['com'];
} else {
$itemcommission = 0;
}
$commision = $commision + coms($inv['salespn'], $amtexvat, $itemcommission);
# Put in product
$products .= "\n\t\t\t\t<tr valign='top'>\n\t\t\t\t\t<td></td>\n\t\t\t\t\t<td>{$ex} {$sp} {$stkd['description']}</td>\n\t\t\t\t\t<td>{$stkd['qty']}</td>\n\t\t\t\t\t<td>" . sprint($stkd["unitcost"]) . "</td>\n\t\t\t\t\t<td>{$stkd['disc']}</td>\n\t\t\t\t\t<td>" . CUR . sprint($stkd["amt"]) . "</td>\n\t\t\t\t</tr>";
}
}
/* --- Start Some calculations --- */
# subtotal
$SUBTOT = sprint($inv['subtot']);
# Calculate subtotal
$VATP = TAX_VAT;
$SUBTOTAL = sprint($inv['subtot']);
$VAT = sprint($inv['vat']);
$TOTAL = sprint($inv['total']);
$av = $VAT;
$at = $TOTAL - $VAT;
$nt = sprint($inv['pcredit']);
$sd = date("Y-m-d");
$ro = $inv['rounding'];
$ro += 0;
com_invoice($inv['salespn'], $TOTAL - $VAT, $commision, $invnum, $td, true);
/* --- End Some calculations --- */
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "novat");
/* - End Hooks - */
$nsp = 0;
# todays date
$date = date("d-m-Y");
$sdate = date("Y-m-d");
db_conn('cubit');
if ($inv['cusnum'] > 0 && $nt > 0) {
# Record the payment on the statement
$sql = "\n\t\t\t\tINSERT INTO stmnt \n\t\t\t\t\t(cusnum, invid, docref, amount, date, type, div, allocation_date) \n\t\t\t\tVALUES \n\t\t\t\t\t('{$inv['cusnum']}', '{$invnum}', '0', '{$nt}', '{$inv['odate']}', 'Invoice', '" . USER_DIV . "', '{$inv['odate']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# Record the payment on the statement
$sql = "INSERT INTO open_stmnt(cusnum, invid, docref, amount, balance, date, type, div) VALUES('{$inv['cusnum']}', '{$invnum}', '0', '{$nt}', '{$nt}', '{$inv['odate']}', 'Invoice', '" . USER_DIV . "')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$nt}') WHERE cusnum = '{$inv['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
custledger($inv['cusnum'], $dept['incacc'], $inv['odate'], $invnum, "Invoice No. {$invnum}", $nt, "d");
recordDT($nt, $inv['cusnum'], $inv['odate']);
db_conn('cubit');
$Sl = "INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('{$td}','" . USER_NAME . "','{$invnum}','{$nt}','Credit','" . PRD_DB . "','0')";
$Ri = db_exec($Sl) or errDie("Unable to insert data.");
}
db_conn('cubit');
if ($inv['terms'] == 1) {
$Sl = "INSERT INTO crec(userid,username,amount,pdate,inv) VALUES ('" . USER_ID . "','" . USER_NAME . "','{$TOTAL}','{$td}','{$invnum}')";
$Ry = db_exec($Sl) or errDie("Unable to insert pos record.");
} else {
$Sl = "INSERT INTO posrec(userid,username,amount,pdate,inv) VALUES ('" . USER_ID . "','" . USER_NAME . "','{$TOTAL}','{$td}','{$invnum}')";
$Ry = db_exec($Sl) or errDie("Unable to insert pos record.");
}
if (!isset($inv['cusname']) or strlen($inv['cusname']) < 1) {
$custname = $inv['surname'];
} else {
$custname = $inv['cusname'];
}
$Sl = "INSERT INTO pr(userid,username,amount,pdate,inv,cust,t) VALUES ('" . USER_ID . "','" . USER_NAME . "','{$TOTAL}','{$td}','{$invnum}','{$custname}','{$inv['terms']}')";
$Ry = db_exec($Sl) or errDie("Unable to insert pos record.");
$refnum = getrefnum();
/*refnum*/
$fcash = $inv['pcash'];
$fccp = $inv['pcc'];
$fcheque = $inv['pcheque'];
$fcredit = $inv['pcredit'];
/* --- Updates ---- */
db_connect();
$Sql = "UPDATE pinvoices SET pchange='{$change}',printed ='y', done ='y',invnum='{$invnum}' WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$upRslt = db_exec($Sql) or errDie("Unable to update invoice information");
# save invoice discount
$sql = "INSERT INTO inv_discs(cusnum, invid, traddisc, itemdisc, inv_date, delchrg, div,total) VALUES('0','{$invnum}','{$inv['delivery']}','{$disc}', '{$inv['odate']}', '{$inv['delivery']}', '" . USER_DIV . "',({$SUBTOT}+{$inv['delivery']}))";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# get selected stock in this invoice
$sql = "SELECT * FROM pinv_items WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
$tcosamt = 0;
while ($stkd = pg_fetch_array($stkdRslt)) {
$stkd['account'] += 0;
if ($stkd['account'] == 0) {
db_connect();
# get selamt from selected stock
$sql = "SELECT * FROM stock WHERE stkid = '{$stkd['stkid']}' AND div = '" . USER_DIV . "'";
$stkRslt = db_exec($sql);
$stk = pg_fetch_array($stkRslt);
if ($stk['units'] - $stkd['qty'] < 0) {
if ($stk['units'] < 0) {
$stk['units'] = 0;
}
$cosamt = round($stk['units'] * $stk['csprice'], 2);
} else {
$cosamt = round($stkd['qty'] * $stk['csprice'], 2);
}
# cost amount
//$cosamt = round(($stkd['qty'] * $stk['csprice']), 2);
if ($stk['csprice'] > 0) {
$Sl = "INSERT INTO scr(inv,stkid,amount) VALUES ('{$invnum}','{$stkd['stkid']}',' {$stk['csprice']}')";
$Rg = db_exec($Sl);
}
# update stock(alloc - qty)
$sql = "UPDATE stock SET csamt = (csamt - '{$cosamt}'),units = (units - '{$stkd['qty']}'),alloc = (alloc - '{$stkd['qty']}') WHERE stkid = '{$stkd['stkid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update stock to Cubit.", SELF);
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatcode']}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please select the vat code for all your stock.";
}
$VATP = TAX_VAT;
$amtexvat = sprint($stkd['amt']);
###################VAT CALCS#######################
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatcode']}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please select the vatcode for all your stock.";
}
$vd = pg_fetch_array($Ri);
if ($stk['exvat'] == 'yes' || $vd['zero'] == "Yes") {
$excluding = "y";
} else {
$excluding = "";
}
$vr = vatcalc($stkd['amt'], $inv['chrgvat'], $excluding, $inv['traddisc'], $vd['vat_amount']);
$vrs = explode("|", $vr);
$ivat = $vrs[0];
$iamount = $vrs[1];
vatr($vd['id'], $inv['odate'], "OUTPUT", $vd['code'], $refnum, "VAT for Invoice No.{$invnum} for Customer : {$inv['cusname']} {$inv['surname']}", $iamount, $ivat);
####################################################
if ($stk['serd'] == 'yes') {
ext_invSer($stkd['serno'], $stkd['stkid'], "POS Cash", $invnum);
}
# stkid, stkcod, stkdes, trantype, edate, qty, csamt, details
$sdate = date("Y-m-d");
stockrec($stk['stkid'], $stk['stkcod'], $stk['stkdes'], 'ct', $td, $stkd['qty'], $cosamt, "POS Sales - Invoice No. {$invnum}");
# get accounts
db_conn("exten");
$sql = "SELECT stkacc,cosacc FROM warehouses WHERE whid = '{$stkd['whid']}' AND div = '" . USER_DIV . "'";
$whRslt = db_exec($sql);
$wh = pg_fetch_array($whRslt);
$stockacc = $wh['stkacc'];
$cosacc = $wh['cosacc'];
# dt(cos) ct(stock)
writetrans($cosacc, $stockacc, $td, $refnum, $cosamt, "Cost Of Sales POS Cash on POS Invoice No.{$invnum}.");
$tcosamt += $cosamt;
db_connect();
$sql = "INSERT INTO salesrec(edate, invid, invnum, debtacc, vat, total, typ, div)\n\t\t\t\tVALUES('{$inv['odate']}', '{$invid}', '{$invnum}', '{$dept['debtacc']}', '{$ivat}', '{$iamount}', 'stk', '" . USER_DIV . "')";
$recRslt = db_exec($sql);
} else {
db_connect();
###################VAT CALCS#######################
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatcode']}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please select the vatcode for all your stock.";
}
$vd = pg_fetch_array($Ri);
if ($vd['zero'] == "Yes") {
$excluding = "y";
} else {
$excluding = "";
}
$vr = vatcalc($stkd['amt'], $inv['chrgvat'], $excluding, $inv['traddisc'], $vd['vat_amount']);
$vrs = explode("|", $vr);
$ivat = $vrs[0];
$iamount = $vrs[1];
$av -= $ivat;
$at -= $iamount;
vatr($vd['id'], $inv['odate'], "OUTPUT", $vd['code'], $refnum, "VAT for Invoice No.{$invnum} for Customer : {$inv['cusname']} {$inv['surname']}", $iamount, $ivat);
db_connect();
$sql = "INSERT INTO salesrec(edate, invid, invnum, debtacc, vat, total, typ, div)\n\t\t\t\tVALUES('{$inv['odate']}', '{$invid}', '{$invnum}', '{$dept['debtacc']}', '{$ivat}', '{$iamount}', 'non', '" . USER_DIV . "')";
$recRslt = db_exec($sql);
####################################################
$amtexvat = sprint($stkd['amt']);
db_connect();
$sdate = date("Y-m-d");
$nsp += sprint($iamount - $ivat);
// //writetrans($cosacc, $stockacc,$inv['odate'] , $refnum, $cosamt, "Cost Of Sales for Invoice No.$invnum for Customer : $inv[cusname] $inv[surname]");
// writetrans($dept['debtacc'], $stkd['account'],$inv['odate'], $refnum, ($iamount-$ivat), "Debtors Control for Invoice No.$invnum for Customer : $inv[cusname] $inv[surname]");
if ($inv['pcash'] > 0) {
$min = $ro;
$inv['pcash'] += $ro;
$ro = 0;
//$amount=$inv['pcash'];
if ($inv['pcash'] >= $ivat) {
writetrans($dept['pca'], $vatacc, $td, $refnum, $ivat, "VAT Received for POS Invoice No.{$invnum}.");
$inv['pcash'] = sprint($inv['pcash'] - $ivat);
$ivat = 0;
if ($inv['pcash'] > 0) {
if ($inv['pcash'] >= $iamount) {
writetrans($dept['pca'], $stkd['account'], $td, $refnum, $iamount, "Sales for POS Invoice No.{$invnum}.");
$inv['pcash'] = sprint($inv['pcash'] - $iamount);
$iamount = 0;
} elseif ($inv['pcash'] < $iamount) {
writetrans($dept['pca'], $stkd['account'], $td, $refnum, $inv['pcash'], "Sales for POS Invoice No.{$invnum}.");
$iamount = sprint($iamount - $inv['pcash']);
$inv['pcash'] = 0;
}
}
} else {
writetrans($dept['pca'], $vatacc, $td, $refnum, $inv['pcash'], "VAT Received for POS Invoice No.{$invnum}.");
$ivat = sprint($ivat - $inv['pcash']);
$inv['pcash'] = 0;
}
// db_conn('cubit');
//
// $inv['pcash']-=$min;
//
// $Sl="INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('$sd','".USER_NAME."','$invnum','$inv[pcash]','Cash','".PRD_DB."','0')";
// $Ri=db_exec($Sl) or errDie("Unable to insert data.");
}
if ($inv['pcheque'] > 0) {
$min = $ro;
$inv['pcheque'] += $ro;
$ro = 0;
//$amount=$inv['pcash'];
if ($inv['pcheque'] >= $ivat) {
writetrans($dept['pca'], $vatacc, $td, $refnum, $ivat, "VAT Received for POS Invoice No.{$invnum}.");
$inv['pcheque'] = sprint($inv['pcheque'] - $ivat);
$ivat = 0;
if ($inv['pcheque'] > 0) {
if ($inv['pcheque'] >= $iamount) {
writetrans($dept['pca'], $stkd['account'], $td, $refnum, $iamount, "Sales for POS Invoice No.{$invnum}.");
$inv['pcheque'] = sprint($inv['pcheque'] - $iamount);
$iamount = 0;
} elseif ($inv['pcheque'] < $iamount) {
writetrans($dept['pca'], $stkd['account'], $td, $refnum, $inv['pcheque'], "Sales for POS Invoice No.{$invnum}.");
$iamount = sprint($iamount - $inv['pcheque']);
$inv['pcheque'] = 0;
}
}
} else {
writetrans($dept['pca'], $vatacc, $td, $refnum, $inv['pcheque'], "VAT Received for POS Invoice No.{$invnum}.");
$ivat = sprint($ivat - $inv['pcheque']);
$inv['pcheque'] = 0;
}
// db_conn('cubit');
//
// $inv['pcash']-=$min;
//
// $Sl="INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('$sd','".USER_NAME."','$invnum','$inv[pcash]','Cash','".PRD_DB."','0')";
// $Ri=db_exec($Sl) or errDie("Unable to insert data.");
}
if ($inv['pcc'] > 0) {
db_conn('core');
$Sl = "SELECT * FROM salacc WHERE name='cc'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please set a link for the POS credit card control account";
}
$cd = pg_fetch_array($Ri);
$cc = $cd['accnum'];
$min = $ro;
$inv['pcc'] += $ro;
$ro = 0;
//$amount=$inv['pcash'];
if ($inv['pcc'] >= $ivat) {
writetrans($cc, $vatacc, $td, $refnum, $ivat, "VAT Received for POS Invoice No.{$invnum}.");
$inv['pcc'] = sprint($inv['pcc'] - $ivat);
$ivat = 0;
if ($inv['pcc'] > 0) {
if ($inv['pcc'] >= $iamount) {
writetrans($cc, $stkd['account'], $td, $refnum, $iamount, "Sales for POS Invoice No.{$invnum}.");
$inv['pcc'] = sprint($inv['pcc'] - $iamount);
$iamount = 0;
} elseif ($inv['pcc'] < $iamount) {
writetrans($cc, $stkd['account'], $td, $refnum, $inv['pcc'], "Sales for POS Invoice No.{$invnum}.");
$iamount = sprint($iamount - $inv['pcc']);
$inv['pcc'] = 0;
}
}
} else {
writetrans($cc, $vatacc, $td, $refnum, $inv['pcc'], "VAT Received for POS Invoice No.{$invnum}.");
$ivat = sprint($ivat - $inv['pcc']);
$inv['pcc'] = 0;
}
// db_conn('cubit');
//
// $inv['pcash']-=$min;
//
// $Sl="INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('$sd','".USER_NAME."','$invnum','$inv[pcash]','Cash','".PRD_DB."','0')";
// $Ri=db_exec($Sl) or errDie("Unable to insert data.");
}
if ($inv['pcredit'] > 0) {
db_conn('core');
$min = $ro;
$inv['pcredit'] += $ro;
$ro = 0;
//$amount=$inv['pcash'];
if ($inv['pcredit'] >= $ivat) {
writetrans($dept['debtacc'], $vatacc, $td, $refnum, $ivat, "VAT Received for POS Invoice No.{$invnum}.");
$inv['pcredit'] = sprint($inv['pcredit'] - $ivat);
$ivat = 0;
if ($inv['pcredit'] > 0) {
if ($inv['pcredit'] >= $iamount) {
writetrans($dept['debtacc'], $stkd['account'], $td, $refnum, $iamount, "Sales for POS Invoice No.{$invnum}.");
$inv['pcredit'] = sprint($inv['pcredit'] - $iamount);
$iamount = 0;
} elseif ($inv['pcredit'] < $iamount) {
writetrans($dept['debtacc'], $stkd['account'], $td, $refnum, $inv['pcredit'], "Sales for POS Invoice No.{$invnum}.");
$iamount = sprint($iamount - $inv['pcredit']);
$inv['pcredit'] = 0;
}
}
} else {
writetrans($dept['debtacc'], $vatacc, $td, $refnum, $inv['pcredit'], "VAT Received for POS Invoice No.{$invnum}.");
$ivat = sprint($ivat - $inv['pcredit']);
$inv['pcredit'] = 0;
}
// db_conn('cubit');
//
// $inv['pcash']-=$min;
//
// $Sl="INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('$sd','".USER_NAME."','$invnum','$inv[pcash]','Cash','".PRD_DB."','0')";
// $Ri=db_exec($Sl) or errDie("Unable to insert data.");
}
}
}
/* - Start Transactoins - */
###################VAT CALCS#######################
db_conn('cubit');
$Sl = "SELECT * FROM vatcodes WHERE del='Yes'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
$Sl = "SELECT * FROM vatcodes";
$Ri = db_exec($Sl);
}
$vd = pg_fetch_array($Ri);
$excluding = "";
$vr = vatcalc($inv['delchrg'], $inv['chrgvat'], $excluding, $inv['traddisc'], $vd['vat_amount']);
$vrs = explode("|", $vr);
$ivat = $vrs[0];
$iamount = $vrs[1];
vatr($vd['id'], $inv['odate'], "OUTPUT", $vd['code'], $refnum, "VAT for Invoice No.{$invnum} for Customer : {$inv['cusname']} {$inv['surname']}", $iamount, $ivat);
####################################################
//print $inv['pcash'];exit;
if ($inv['pcash'] > 0) {
$min = $ro;
$inv['pcash'] += $ro;
$ro = 0;
$amount = $inv['pcash'];
if ($amount >= $av) {
writetrans($dept['pca'], $vatacc, $td, $refnum, $av, "VAT Received for POS Invoice No.{$invnum}.");
// PROBLEM HERE?
$amount = sprint($amount - $av);
$av = 0;
if ($amount > 0) {
writetrans($dept['pca'], $dept['pia'], $td, $refnum, $amount, "Sales for POS Invoice No.{$invnum}.");
$at = $at - $amount;
}
} else {
writetrans($dept['pca'], $vatacc, $td, $refnum, $amount, "VAT Received for POS Invoice No.{$invnum}.");
$av = $av - $amount;
$amount = 0;
}
db_conn('cubit');
$inv['pcash'] -= $min;
$Sl = "INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('{$td}','" . USER_NAME . "','{$invnum}','{$fcash}','Cash','" . PRD_DB . "','0')";
$Ri = db_exec($Sl) or errDie("Unable to insert data.");
$fcash = 0;
}
db_conn('cubit');
if ($fcash > 0) {
$Sl = "INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('{$td}','" . USER_NAME . "','{$invnum}','{$fcash}','Cash','" . PRD_DB . "','0')";
$Ri = db_exec($Sl) or errDie("Unable to insert data.");
}
if ($inv['pcheque'] > 0) {
$min = $ro;
$inv['pcheque'] += $ro;
$ro = 0;
$amount = $inv['pcheque'];
if ($amount >= $av) {
writetrans($dept['pca'], $vatacc, $td, $refnum, $av, "VAT Received for POS Invoice No.{$invnum}.");
$amount = sprint($amount - $av);
$av = 0;
if ($amount > 0) {
writetrans($dept['pca'], $dept['pia'], $td, $refnum, $amount, "Sales for POS Invoice No.{$invnum}.");
$at = $at - $amount;
}
} else {
writetrans($dept['pca'], $vatacc, $td, $refnum, $amount, "VAT Received for POS Invoice No.{$invnum}.");
$av = $av - $amount;
$amount = 0;
}
db_conn('cubit');
$inv['pcheque'] -= $min;
$Sl = "INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('{$td}','" . USER_NAME . "','{$invnum}','{$fcheque}','Cheque','" . PRD_DB . "','0')";
$Ri = db_exec($Sl) or errDie("Unable to insert data.");
$fcheque = 0;
}
db_conn('cubit');
if ($fcheque > 0) {
$Sl = "INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('{$td}','" . USER_NAME . "','{$invnum}','{$fcheque}','Cheque','" . PRD_DB . "','0')";
$Ri = db_exec($Sl) or errDie("Unable to insert data.");
}
if ($inv['pcc'] > 0) {
db_conn('core');
$Sl = "SELECT * FROM salacc WHERE name='cc'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) < 1) {
return "Please set a link for the POS credit card control account";
}
$cd = pg_fetch_array($Ri);
$cc = $cd['accnum'];
$min = $ro;
$inv['pcc'] += $ro;
$ro = 0;
$amount = $inv['pcc'];
if ($amount >= $av) {
writetrans($cc, $vatacc, $td, $refnum, $av, "VAT Received for POS Invoice No.{$invnum}.");
$amount = sprint($amount - $av);
$av = 0;
if ($amount > 0) {
writetrans($cc, $dept['pia'], $td, $refnum, $amount, "Sales for POS Invoice No.{$invnum}.");
$at = $at - $amount;
}
} else {
writetrans($cc, $vatacc, $td, $refnum, $amount, "VAT Received for POS Invoice No.{$invnum}.");
$av = $av - $amount;
$amount = 0;
}
db_conn('cubit');
$inv['pcc'] -= $min;
$Sl = "INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('{$td}','" . USER_NAME . "','{$invnum}','{$fccp}','Credit Card','" . PRD_DB . "','0')";
$Ri = db_exec($Sl) or errDie("Unable to insert data.");
$fccp = 0;
}
db_conn('cubit');
if ($fccp > 0) {
$Sl = "INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('{$td}','" . USER_NAME . "','{$invnum}','{$fccp}','Credit Card','" . PRD_DB . "','0')";
$Ri = db_exec($Sl) or errDie("Unable to insert data.");
}
if ($inv['pcredit'] > 0) {
db_conn('core');
$min = $ro;
$inv['pcredit'] += $ro;
$ro = 0;
$amount = $inv['pcredit'];
if ($amount >= $av) {
writetrans($dept['debtacc'], $vatacc, $td, $refnum, $av, "VAT Received for POS Invoice No.{$invnum}.");
$amount = sprint($amount - $av);
$av = 0;
if ($amount > 0) {
writetrans($dept['debtacc'], $dept['pia'], $td, $refnum, $amount, "Sales for POS Invoice No.{$invnum}.");
$at = $at - $amount;
}
} else {
writetrans($dept['debtacc'], $vatacc, $td, $refnum, $amount, "VAT Received for POS Invoice No.{$invnum}.");
$av = $av - $amount;
$amount = 0;
}
db_conn('cubit');
$inv['pcc'] -= $min;
$Sl = "INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('{$td}','" . USER_NAME . "','{$invnum}','{$fcredit}','Credit','" . PRD_DB . "','0')";
//$Ri=db_exec($Sl) or errDie("Unable to insert data.");
$fcredit = 0;
}
db_conn('cubit');
if ($fcredit > 0) {
$Sl = "INSERT INTO payrec(date,by,inv,amount,method,prd,note) VALUES ('{$td}','" . USER_NAME . "','{$invnum}','{$fcredit}','Credit','" . PRD_DB . "','0')";
$Ri = db_exec($Sl) or errDie("Unable to insert data.");
}
if ($inv['rounding'] > 0) {
if ($inv['pcash'] > 0) {
writetrans($rac, $dept['pca'], $td, $refnum, $inv['rounding'], "Rounding on Invoice No.{$invnum}.");
} elseif ($inv['pcheque'] > 0) {
writetrans($rac, $dept['pca'], $td, $refnum, $inv['rounding'], "Rounding on Invoice No.{$invnum}.");
} elseif ($inv['pcc'] > 0) {
writetrans($rac, $cc, $td, $refnum, $inv['rounding'], "Rounding on Invoice No.{$invnum}.");
} elseif ($inv['pcredit'] > 0) {
writetrans($rac, $dept['debtacc'], $td, $refnum, $inv['rounding'], "Rounding on Invoice No.{$invnum}.");
}
}
// if($inv['terms']==1) {
// $dept['pca']=$cc;
// }
//
// # dt(debtors) ct(income/sales)
// writetrans($dept['pca'], $dept['pia'], $td, $refnum, ($TOTAL-$VAT), "Sales for POS Invoice No.$invnum.");
//
// # dt(debtors) ct(vat account)
// writetrans($dept['pca'], $vatacc, $td, $refnum, $VAT, "VAT Received for POS Invoice No.$invnum.");
// db_connect();
// $sql = "INSERT INTO salesrec(edate, invid, invnum, debtacc, vat, total, typ, div)
// VALUES('$inv[odate]', '$invid', '$invnum', '$dept[debtacc]', '$VAT', '$TOTAL', 'stk', '".USER_DIV."')";
// $recRslt = db_exec($sql);
db_conn('cubit');
if ($inv['cusnum'] > 0) {
db_conn('cubit');
$Sl = "SELECT * FROM customers WHERE cusnum='{$inv['cusnum']}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$cd = pg_fetch_array($Ri);
$inv['cusname'] = $cd['surname'];
}
$Sl = "INSERT INTO sj(cid,name,des,date,exl,vat,inc,div) VALUES\n\t('{$inv['cusnum']}','{$inv['cusname']}','POS Invoice {$invnum}','{$inv['odate']}','" . sprint($TOTAL - $VAT) . "','{$VAT}','" . sprint($TOTAL) . "','" . USER_DIV . "')";
$Ri = db_exec($Sl);
if ($change > 0) {
$Sl = "INSERT INTO pc(date,by,inv,amount) VALUES ('{$sd}','" . USER_NAME . "','{$invnum}','{$change}')";
$Ri = db_exec($Sl) or errDie("Unable to insert data.");
}
db_conn('cubit');
if ($inv['rounding'] > 0) {
$Sl = "INSERT INTO varrec(inv,date,amount) VALUES('{$invnum}','" . date("Y-m-d") . "','{$inv['rounding']}')";
$Ri = db_exec($Sl);
}
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
if (strlen($inv['comm']) > 0) {
$Com = "<table><tr><td>" . nl2br($inv['comm']) . "</td></tr></table>";
} else {
$Com = "";
}
$cc = "<script> sCostCenter('dt', 'Sales', '{$inv['odate']}', 'POS Invoice No.{$invnum}', '" . ($TOTAL - $VAT) . "', 'Cost Of Sales for Invoice No.{$invnum}', '{$tcosamt}', ''); </script>";
if ($inv['chrgvat'] == "inc") {
$inv['chrgvat'] = "Inclusive";
} elseif ($inv['chrgvat'] == "exc") {
$inv['chrgvat'] = "Exclusive";
} else {
$inv['chrgvat'] = "No vat";
}
/* - End Transactoins - */
/* -- Final Layout -- */
$details = "\n\t\t\t\t\t<center>\n\t\t\t\t\t{$cc}\n\t\t\t\t\t<h2>Tax Invoice</h2>\n\t\t\t\t\t<table cellpadding='0' cellspacing='1' border=0 width=750>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td valign='top' width='40%'>\n\t\t\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t\t\t\t<tr><td>{$inv['surname']}</td></tr>\n\t\t\t\t\t\t\t\t</table>\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t<td valign='top' width='35%'>\n\t\t\t\t\t\t\t\t" . COMP_NAME . "<br>\n\t\t\t\t\t\t\t\t" . COMP_ADDRESS . "<br>\n\t\t\t\t\t\t\t\t" . COMP_TEL . "<br>\n\t\t\t\t\t\t\t\t" . COMP_FAX . "<br>\n\t\t\t\t\t\t\t\tReg No. " . COMP_REGNO . "<br>\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t<td valign='bottom' align='right' width='25%'>\n\t\t\t\t\t\t\t\t<table cellpadding='2' cellspacing='0' border='1' bordercolor='#000000'>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td><b>Invoice No.</b></td>\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$invnum}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td><b>Order No.</b></td>\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$inv['ordno']}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td><b>Terms</b></td>\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>Cash</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td><b>Invoice Date</b></td>\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$inv['odate']}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td><b>VAT</b></td>\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$inv['chrgvat']}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t</table>\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td colspan='3'>\n\t\t\t\t\t\t\t\t<table cellpadding='5' cellspacing='0' border='1' width=100% bordercolor='#000000'>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<th>ITEM NUMBER</th>\n\t\t\t\t\t\t\t\t\t\t<th width='45%'>DESCRIPTION</th>\n\t\t\t\t\t\t\t\t\t\t<th>QTY</th>\n\t\t\t\t\t\t\t\t\t\t<th>UNIT PRICE</th>\n\t\t\t\t\t\t\t\t\t\t<th>AMOUNT</th>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t{$products}\n\t\t\t\t\t\t\t\t</table>\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td>{$Com}</td>\n\t\t\t\t\t\t\t<td align='right' colspan='2'>\n\t\t\t\t\t\t\t\t<table cellpadding='5' cellspacing='0' border='1' width='50%' bordercolor='#000000'>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td><b>SUBTOTAL</b></td>\n\t\t\t\t\t\t\t\t\t\t<td align='right'>" . CUR . " {$SUBTOT}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td><b>Trade Discount</b></td>\n\t\t\t\t\t\t\t\t\t\t<td align='right'>" . CUR . " {$inv['discount']}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td><b>Delivery Charge</b></td>\n\t\t\t\t\t\t\t\t\t\t<td align='right'>" . CUR . " {$inv['delivery']}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td><b>VAT @ {$VATP}%</b></td>\n\t\t\t\t\t\t\t\t\t\t<td align='right'>" . CUR . " {$VAT}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<th><b>GRAND TOTAL<b></th>\n\t\t\t\t\t\t\t\t\t\t<td align='right'>" . CUR . " {$TOTAL}</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t</table>\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr><td><br></td></tr>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td>\n\t\t\t\t\t\t\t\t<table " . TMPL_tblDflts . " border='1'>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td colspan='2'>VAT Exempt indicator = #</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<th>VAT No.</th>\n\t\t\t\t\t\t\t\t\t\t<td align='center'>" . COMP_VATNO . "</td>\n\t\t\t\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t </table>\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t<td><br></td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>\n\t\t\t\t\t</center>";
/* Start moving invoices */
db_connect();
# Move invoices that are fully paid
$sql = "SELECT * FROM pinvoices WHERE printed = 'y' AND done = 'y' AND div = '" . USER_DIV . "'";
$invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
while ($invb = pg_fetch_array($invbRslt)) {
db_conn($invb['prd']);
$invb['invnum'] += 0;
# Insert invoice to period DB
$sql = "INSERT INTO pinvoices(invid,invnum, deptid, cusnum, deptname, cusacc, cusname, telno,\n\t\t\t\t\tsurname, cusaddr, cusvatno, cordno, ordno, chrgvat, terms, traddisc, salespn,\n\t\t\t\t\todate, delchrg, subtot, vat, total, balance, comm, printed, done, div, username,\n\t\t\t\t\trounding,delvat,vatnum,pcash,pcheque,pcc,pcredit, pslip_sordid)\n\t\t\t\tVALUES('{$invb['invid']}','{$invb['invnum']}', '{$invb['deptid']}', '{$invb['cusnum']}',\n\t\t\t\t\t'{$invb['deptname']}', '{$invb['cusacc']}', '{$invb['cusname']}', '{$invb['telno']}', '{$invb['surname']}',\n\t\t\t\t\t'{$invb['cusaddr']}', '{$invb['cusvatno']}', '{$invb['cordno']}', '{$invb['ordno']}',\n\t\t\t\t\t'{$invb['chrgvat']}', '{$invb['terms']}', '{$invb['traddisc']}', '{$invb['salespn']}',\n\t\t\t\t\t'{$invb['odate']}', '{$invb['delchrg']}', '{$invb['subtot']}', '{$invb['vat']}' ,\n\t\t\t\t\t'{$invb['total']}', '{$invb['balance']}', '{$invb['comm']}', 'y', 'y', '" . USER_DIV . "',\n\t\t\t\t\t'" . USER_NAME . "','{$invb['rounding']}','{$invb['delvat']}','{$invb['vatnum']}',\n\t\t\t\t\t'{$invb['pcash']}','{$invb['pcheque']}','{$invb['pcc']}','{$invb['pcredit']}', '{$invb['pslip_sordid']}')";
$rslt = db_exec($sql) or errDie("Unable to insert invoice to the period database.", SELF);
db_connect();
$sql = "INSERT INTO movinv(invtype, invnum, prd, docref, div) VALUES('pos', '{$invb['invnum']}', '{$invb['prd']}', '', '" . USER_DIV . "')";
$rslt = db_exec($sql) or errDie("Unable to insert invoice to the period database.", SELF);
# get selected stock in this invoice
db_connect();
$sql = "SELECT * FROM pinv_items WHERE invid = '{$invb['invid']}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
while ($stkd = pg_fetch_array($stkdRslt)) {
db_conn($invb['prd']);
# insert invoice items
$stkd['vatcode'] += 0;
$stkd['account'] += 0;
$sql = "INSERT INTO pinv_items(invid, whid, stkid, qty, unitcost, amt, disc, discp, serno, div,vatcode,account,description) VALUES('{$invb['invid']}', '{$stkd['whid']}', '{$stkd['stkid']}', '{$stkd['qty']}', '{$stkd['unitcost']}', '{$stkd['amt']}', '{$stkd['disc']}', '{$stkd['discp']}', '{$stkd['serno']}', '" . USER_DIV . "','{$stkd['vatcode']}','{$stkd['account']}','{$stkd['description']}')";
$rslt = db_exec($sql) or errDie("Unable to insert invoice items to Cubit.", SELF);
}
db_connect();
# Remove those invoices from running DB
$sql = "DELETE FROM pinvoices WHERE invid = '{$invb['invid']}' AND div = '" . USER_DIV . "'";
$delRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
# Remove those invoice items from running DB
$sql = "DELETE FROM pinv_items WHERE invid = '{$invb['invid']}' AND div = '" . USER_DIV . "'";
$delRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
}
/* end moving invoices */
/* OLD
$OUTPUT = $details;
require("tmpl-print.php");*/
header("Location: pos-slip.php?invid={$inv['invid']}&prd={$inv['prd']}&cccc=yes");
exit;
}
function write($_POST)
{
# Get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
if (isset($back)) {
if ($type == 1) {
return details($_POST);
} else {
$_POST["accnum"] = $ac;
return details2($_POST);
}
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cusnum, "num", 1, 50, "Invalid Customer number.");
$v->isOk($accid, "num", 1, 50, "Invalid Contra Account.");
$v->isOk($refnum, "num", 1, 10, "Invalid Reference number.");
$v->isOk($amount, "float", 1, 20, "Invalid Amount.");
$v->isOk($details, "string", 0, 255, "Invalid Details.");
$v->isOk($author, "string", 1, 30, "Invalid Authorising person name.");
$datea = explode("-", $date);
if (count($datea) == 3) {
if (!checkdate($datea[1], $datea[0], $datea[2])) {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
} else {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
# display errors, if any
if ($v->isError()) {
$write = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$write .= "<li class=err>" . $e["msg"];
}
$write .= "<p><input type=button onClick='JavaScript:history.back();' value='« Correct submission'>";
return $write;
}
$date = "{$datea['2']}-{$datea['1']}-{$datea['0']}";
# Accounts details
$accRs = get("core", "*", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
# Select customer
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($custRslt) < 1) {
return "<li> Invalid Customer ID.";
} else {
$cust = pg_fetch_array($custRslt);
}
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cust['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
return "<i class=err>Department Not Found</i>";
} else {
$dept = pg_fetch_array($deptRslt);
}
// $cust['cusname'] = remval($cust['cusname']);
// $cust['surname'] = remval($cust['surname']);
# Probe tran type
if ($entry == "CT") {
# Write transaction (debit contra account, credit debtors control)
writetrans($accid, $dept['debtacc'], $date, $refnum, $amount, $details . " - Customer {$cust['cusname']} {$cust['surname']}");
$tran = "<tr class='bg-odd'><td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td><td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td></tr>";
$samount = $amount - $amount * 2;
recordCT($samount, $cust['cusnum'], $date);
$type = 'c';
} else {
# Write transaction (debit debtors control, credit contra account)
writetrans($dept['debtacc'], $accid, $date, $refnum, $amount, $details . " - Customer {$cust['cusname']} {$cust['surname']}");
$tran = "<tr class='bg-odd'><td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td><td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td></tr>";
$samount = $amount;
recordDT($samount, $cust['cusnum'], $date);
$type = 'd';
}
db_connect();
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
$sdate = date("Y-m-d");
# record the payment on the statement
$sql = "\n\t\t\t\tINSERT INTO stmnt \n\t\t\t\t\t(cusnum, invid, amount, date, type, st, div, allocation_date) \n\t\t\t\tVALUES \n\t\t\t\t\t('{$cust['cusnum']}', '0', '{$samount}', '{$date}', '{$details}', 'n', '" . USER_DIV . "', '{$date}')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
$sql = "INSERT INTO open_stmnt(cusnum, invid, amount, balance, date, type, st, div) VALUES('{$cust['cusnum']}', '0', '{$samount}', '{$samount}', '{$date}', '{$details}', 'n', '" . USER_DIV . "')";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$samount}') WHERE cusnum = '{$cust['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF);
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
# Make ledge record
custledger($cust['cusnum'], $accid, $date, $refnum, $details, $amount, $type);
// Start layout
$write = "<h3>Journal transaction has been recorded</h3>\n <table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=500>\n \t<tr><td width=50%><h3>Debit</h3></td><td width=50%><h3>Credit</h3></td></tr>\n \t{$tran}\n \t<tr><td><br></td></tr>\n \t<tr colspan=2><td><h4>Amount</h4></td></tr>\n \t<tr class='bg-even'><td colspan=2><b>" . CUR . " {$amount}</b></td></tr>\n </table>" . mkQuickLinks(ql("trans-new.php", "Journal Transactions"), ql("../customers-view.php", "View Customers"));
return $write;
}
function write($_POST)
{
# Get vars
extract($_POST);
if (isset($back)) {
if ($type == 1) {
return details($_POST);
} else {
$_POST["accnum"] = $ac;
return details2($_POST);
}
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cusnum, "num", 1, 50, "Invalid Customer number.");
$v->isOk($accid, "num", 1, 50, "Invalid Contra Account.");
$v->isOk($refnum, "num", 1, 10, "Invalid Reference number.");
$v->isOk($amount, "float", 1, 20, "Invalid Amount.");
$v->isOk($details, "string", 0, 255, "Invalid Details.");
$v->isOk($author, "string", 1, 30, "Invalid Authorising person name.");
$datea = explode("-", $date);
if (count($datea) == 3) {
if (!checkdate($datea[1], $datea[0], $datea[2])) {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
} else {
$v->isOk($date, "num", 1, 1, "Invalid date.");
}
$v->isOk($chrgvat, "string", 1, 10, "Invalid Charge VAT Option.");
$v->isOk($vatinc, "string", 1, 10, "Invalid VAT Inclusive Exclusive Option.");
$v->isOk($vatcode, "num", 1, 5, "Invalid Vat Code Option.");
# display errors, if any
if ($v->isError()) {
$write = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$write .= "<li class='err'>" . $e["msg"] . "</li>";
}
$write .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $write;
}
# CHECK IF THIS DATE IS IN THE BLOCKED RANGE
$blocked_date_from = getCSetting("BLOCKED_FROM");
$blocked_date_to = getCSetting("BLOCKED_TO");
if (strtotime($date) >= strtotime($blocked_date_from) and strtotime($date) <= strtotime($blocked_date_to) and !user_is_admin(USER_ID)) {
return "<li class='err'>Period Range Is Blocked. Only an administrator can process entries within this period.</li>";
}
if (isset($chrgvat) and $chrgvat == "yes") {
db_connect();
#get selected vatcode
$get_vatcode = "SELECT * FROM vatcodes WHERE id = '{$vatcode}' LIMIT 1";
$run_vatcode = db_exec($get_vatcode) or errDie("Unable to get vat code information.");
if (pg_numrows($run_vatcode) < 1) {
#vatcode not found ....
return "<li class='err'>Unable to get vat code information.</li>";
}
$vd = pg_fetch_array($run_vatcode);
if ($vatinc == "inc") {
#vat inc ... recalc value
$vatamt = sprint($amount * ($vd['vat_amount'] / (100 + $vd['vat_amount'])));
$amount = sprint($amount - $vatamt);
} else {
#vat excl
$amount = sprint($amount);
$vatamt = sprint($amount / 100 * $vd['vat_amount']);
}
} else {
#vat not set
$amount = sprint($amount);
$vatamt = sprint(0);
}
$date = "{$datea['2']}-{$datea['1']}-{$datea['0']}";
# Accounts details
$accRs = get("core", "*", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
# Select customer
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($custRslt) < 1) {
return "<li class='err'>Invalid customer ID, or customer has been blocked.</li>";
} else {
$cust = pg_fetch_array($custRslt);
}
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cust['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
return "<i class='err'>Department Not Found</i>";
} else {
$dept = pg_fetch_array($deptRslt);
}
#get vat acc ...
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "VAT");
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
# Probe tran type
if ($entry == "CT") {
# Write transaction (debit contra account, credit debtors control)
writetrans($accid, $dept['debtacc'], $date, $refnum, $amount, $details . " - Customer {$cust['cusname']} {$cust['surname']}");
$tran = "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t<td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t</tr>";
$samount = $amount - $amount * 2;
$svatamt = $vatamt - $vatamt * 2;
recordCT($samount, $cust['cusnum'], $date);
$type = 'c';
if (isset($chrgvat) and $chrgvat == "yes") {
writetrans($vatacc, $dept['debtacc'], $date, $refnum, $vatamt, "VAT for Transaction: {$refnum} for Customer : {$cust['cusname']} {$cust['surname']}");
vatr($vd['id'], $date, "OUTPUT", $vd['code'], $refnum, "VAT for Transaction: {$refnum} for Customer : {$cust['cusname']} {$cust['surname']}", $samount + $svatamt, $svatamt);
}
} else {
# Write transaction (debit debtors control, credit contra account)
writetrans($dept['debtacc'], $accid, $date, $refnum, $amount, $details . " - Customer {$cust['cusname']} {$cust['surname']}");
$tran = "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t</tr>";
$samount = $amount;
$svatamt = $vatamt;
recordDT($samount, $cust['cusnum'], $date);
$type = 'd';
if (isset($chrgvat) and $chrgvat == "yes") {
writetrans($dept['debtacc'], $vatacc, $date, $refnum, $vatamt, "VAT for Transaction: {$refnum} for Customer : {$cust['cusname']} {$cust['surname']}");
vatr($vd['id'], $date, "OUTPUT", $vd['code'], $refnum, "VAT for Transaction: {$refnum} for Customer : {$cust['cusname']} {$cust['surname']}", $amount + $vatamt, $vatamt);
}
}
db_connect();
$sdate = date("Y-m-d");
# record the payment on the statement
$sql = "\n\t\tINSERT INTO stmnt (\n\t\t\tcusnum, invid, amount, date, type, st, div, allocation_date\n\t\t) VALUES (\n\t\t\t'{$cust['cusnum']}', '0', '" . sprint($samount + $svatamt) . "', '{$date}', '{$details}', 'n', '" . USER_DIV . "', '{$date}'\n\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
$sql = "\n\t\tINSERT INTO open_stmnt (\n\t\t\tcusnum, invid, amount, balance, date, type, st, div\n\t\t) VALUES (\n\t\t\t'{$cust['cusnum']}', '0', '" . sprint($samount + $svatamt) . "', '" . sprint($samount + $svatamt) . "', '{$date}', '{$details}', 'n', '" . USER_DIV . "'\n\t\t)";
$stmntRslt = db_exec($sql) or errDie("Unable to Insert statement record in Cubit.", SELF);
# update the customer (make balance more)
$sql = "UPDATE customers SET balance = (balance + '{$samount}') WHERE cusnum = '{$cust['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update customer in Cubit.", SELF);
# Make ledge record
// custledger($cust['cusnum'], $accid, $date, $refnum, $details, $amount, $type);
custledger($cust['cusnum'], $accid, $date, $refnum, $details, sprint($amount + $vatamt), $type);
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
// Start layout
$write = "\n\t\t<h3>Customer Journal transaction has been recorded</h3>\n <table " . TMPL_tblDflts . " width='500'>\n \t<tr>\n \t\t<td width='50%'><h3>Debit</h3></td>\n \t\t<td width='50%'><h3>Credit</h3></td>\n \t</tr>\n \t{$tran}\n \t<tr><td><br></td></tr>\n \t<tr colspan='2'>\n \t\t<td><h4>Amount</h4></td>\n \t</tr>\n \t<tr class='" . bg_class() . "'>\n \t\t<td colspan='2'><b>" . CUR . " {$amount}</b></td>\n \t</tr>\n </table>" . mkQuickLinks(ql("trans-new.php", "Journal Transactions"), ql("../customers-view.php", "View Customers"));
return $write;
}
function write($_POST)
{
# get vars
extract($_POST);
if (isset($back)) {
unset($_POST["back"]);
return alloc($_POST);
}
# validate input
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, 10, "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, 10, "Invalid amount.");
$v->isOk($supid, "num", 1, 10, "Invalid supplier number.");
$v->isOk($out1, "float", 0, 10, "Invalid paid amount(current).");
$v->isOk($out2, "float", 0, 10, "Invalid paid amount(30).");
$v->isOk($out3, "float", 0, 10, "Invalid paid amount(60).");
$v->isOk($out4, "float", 0, 10, "Invalid paid amount(90).");
$v->isOk($out5, "float", 0, 10, "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, 20, "Invalid amount to be paid.");
}
}
# display errors, if any
if ($v->isError()) {
$confirm = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$confirm .= "<li class='err'>" . $e["msg"] . "</li>";
}
$confirm .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $confirm;
}
# get hook account number
core_connect();
$sql = "SELECT * FROM bankacc WHERE accid = '{$bankid}' AND div = '" . USER_DIV . "' AND accid!=0";
$rslt = db_exec($sql) or errDie("Unable to retrieve bank account link from Cubit", SELF);
# check if link exists
if (pg_numrows($rslt) < 1) {
$Sl = "SELECT * FROM accounts WHERE accname='Cash on Hand'";
$Rg = db_exec($Sl);
if (pg_num_rows($Rg) < 1) {
if ($bankid == 0) {
return "There is no 'Cash on Hand' account, there was one, but its not there now, you must have deleted it, if you want to use cash functionality please create a 'Cash on Hand' account.";
} else {
return "Invalid bank acc.";
}
}
$add = pg_fetch_array($Rg);
$bank['accnum'] = $add['accid'];
} else {
$bank = pg_fetch_array($rslt);
}
db_connect();
# Supplier name
$sql = "SELECT supid,supno,supname,deptid FROM suppliers WHERE supid = '{$supid}' AND div = '" . USER_DIV . "'";
$supRslt = db_exec($sql);
$sup = pg_fetch_array($supRslt);
db_conn("exten");
# get debtors control account
$sql = "SELECT credacc FROM departments WHERE deptid ='{$sup['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
$dept = pg_fetch_array($deptRslt);
# date format
$sdate = $date;
$cheqnum = 0 + $cheqnum;
$pay = "";
$accdate = $sdate;
# Paid invoices
$invidsers = "";
$rinvids = "";
$amounts = "";
$invprds = "";
db_conn("cubit");
pglib_transaction("BEGIN");
if ($all == 0) {
$ids = "";
$purids = "";
$pamounts = "";
$pdates = "";
if (isset($invids)) {
foreach ($invids as $key => $value) {
#debt invoice info
$sql = "SELECT id,pdate FROM suppurch WHERE purid ='{$invids[$key]}' AND div = '" . USER_DIV . "' ORDER BY balance LIMIT 1";
$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>";
}
$pur = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE suppurch SET balance = (balance - '{$paidamt[$key]}'::numeric(13,2)) WHERE purid = '{$invids[$key]}' AND div = '" . USER_DIV . "' AND id='{$pur['id']}'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$ids .= "|{$pur['id']}";
$purids .= "|{$invids[$key]}";
$pamounts .= "|{$paidamt[$key]}";
$pdates .= "|{$pur['pdate']}";
}
}
$samount = $amt - $amt * 2;
if ($out > 0) {
recordDT($out, $sup['supid'], $sdate);
}
$Sl = "INSERT INTO sup_stmnt(supid, amount, edate, descript,ref,cacc, div) VALUES('{$sup['supid']}','{$samount}','{$sdate}', 'Payment','{$cheqnum}','{$bank['accnum']}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
db_connect();
# Update the supplier (make balance less)
$sql = "UPDATE suppliers SET balance = (balance - '{$amt}'::numeric(13,2)) WHERE supid = '{$sup['supid']}'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
suppledger($sup['supid'], $bank['accnum'], $sdate, $cheqnum, "Payment for purchases", $amt, "d");
db_connect();
# Record the payment record
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, supid, ids, purids, pamounts, pdates, reference, div) VALUES ('{$bankid}', 'withdrawal', '{$sdate}', '{$sup['supno']} - {$sup['supname']}', 'Supplier Payment to {$sup['supname']}', '{$cheqnum}', '{$amt}', 'no', '{$dept['credacc']}', '{$sup['supid']}', '{$ids}', '{$purids}', '{$pamounts}', '{$pdates}', '{$reference}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$refnum = getrefnum($accdate);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}'";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 1) {
return "Invalid bank acc.";
}
$link = pg_fetch_array($Rx);
$link['accnum'] = $bank['accnum'];
writetrans($dept['credacc'], $link['accnum'], $accdate, $refnum, $amt, "Supplier Payment to {$sup['supname']}");
db_conn('cubit');
}
if ($all == 1) {
$ids = "";
$purids = "";
$pamounts = "";
$pdates = "";
if (isset($invids)) {
foreach ($invids as $key => $value) {
# Get debt invoice info
$sql = "SELECT id,pdate FROM suppurch WHERE purid ='{$invids[$key]}' AND div = '" . USER_DIV . "' ORDER BY balance LIMIT 1";
$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>";
}
$pur = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE suppurch SET balance = (balance - {$paidamt[$key]}::numeric(13,2)) WHERE purid = '{$invids[$key]}' AND div = '" . USER_DIV . "' AND id='{$pur['id']}'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$ids .= "|{$pur['id']}";
$purids .= "|{$invids[$key]}";
$pamounts .= "|{$paidamt[$key]}";
$pdates .= "|{$pur['pdate']}";
}
}
$samount = $amt - $amt * 2;
if ($out1 > 0) {
recordDT($out1, $sup['supid'], $sdate);
}
if ($out2 > 0) {
recordDT($out2, $sup['supid'], $sdate, "1");
}
if ($out3 > 0) {
recordDT($out3, $sup['supid'], $sdate, "2");
}
if ($out4 > 0) {
recordDT($out4, $sup['supid'], $sdate, "3");
}
if ($out5 > 0) {
recordDT($out5, $sup['supid'], $sdate, "4");
}
$Sl = "INSERT INTO sup_stmnt(supid, amount, edate, descript,ref,cacc, div) VALUES('{$sup['supid']}','{$samount}','{$sdate}', 'Payment','{$cheqnum}','{$bank['accnum']}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the supplier (make balance less)
$sql = "UPDATE suppliers SET balance = (balance - '{$amt}'::numeric(13,2)) WHERE supid = '{$sup['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Record the payment record
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, supid, ids, purids, pamounts, pdates, reference, div) VALUES ('{$bankid}', 'withdrawal', '{$sdate}', '{$sup['supno']} - {$sup['supname']}', 'Supplier Payment to {$sup['supname']}', '{$cheqnum}', '{$amt}', 'no', '{$dept['credacc']}', '{$sup['supid']}', '{$ids}', '{$purids}', '{$pamounts}', '{$pdates}', '{$reference}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$refnum = getrefnum($accdate);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}'";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 1) {
return "Invalid bank acc.";
}
$link = pg_fetch_array($Rx);
$link['accnum'] = $bank['accnum'];
writetrans($dept['credacc'], $link['accnum'], $accdate, $refnum, $amt, "Supplier Payment to {$sup['supname']}");
db_conn('cubit');
suppledger($sup['supid'], $bank['accnum'], $sdate, $cheqnum, "Payment to Supplier", $amt, "d");
db_connect();
}
if ($all == 2) {
$ids = "";
$purids = "";
$pamounts = "";
$pdates = "";
db_conn('cubit');
# Update the supplier (make balance less)
$sql = "UPDATE suppliers SET balance = (balance - '{$amt}'::numeric(13,2)) WHERE supid = '{$sup['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Begin updates
if (isset($invids)) {
foreach ($invids as $key => $value) {
# Get debt invoice info
$sql = "SELECT id,pdate FROM suppurch WHERE purid ='{$invids[$key]}' AND div = '" . USER_DIV . "' ORDER BY balance LIMIT 1";
$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>";
}
$pur = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE suppurch SET balance = (balance - '{$paidamt[$key]}'::numeric(13,2)) WHERE purid = '{$invids[$key]}' AND div = '" . USER_DIV . "' AND id='{$pur['id']}'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$samount = $paidamt[$key] - $paidamt[$key] * 2;
$Sl = "INSERT INTO sup_stmnt(supid, amount, edate, descript,ref,cacc,div) VALUES('{$sup['supid']}','{$samount}','{$sdate}', 'Payment - Purchase: {$invids[$key]}','{$cheqnum}','{$bank['accnum']}', '" . USER_DIV . "')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
suppledger($sup['supid'], $bank['accnum'], $sdate, $invids[$key], "Payment for Purchase No. {$invids[$key]}", $paidamt[$key], "d");
db_connect();
# record the payment on the statement
$ids .= "|{$pur['id']}";
$purids .= "|{$invids[$key]}";
$pamounts .= "|{$paidamt[$key]}";
$pdates .= "|{$pur['pdate']}";
}
}
$samount = $amt - $amt * 2;
db_conn('cubit');
# Record the payment record
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, banked, accinv, supid, ids, purids, pamounts, pdates, reference, div) VALUES ('{$bankid}', 'withdrawal', '{$sdate}', '{$sup['supno']} - {$sup['supname']}', 'Supplier Payment to {$sup['supname']}', '{$cheqnum}', '{$amt}', 'no', '{$dept['credacc']}', '{$sup['supid']}', '{$ids}', '{$purids}', '{$pamounts}', '{$pdates}', '{$reference}', '" . USER_DIV . "')";
$Rslt = db_exec($sql) or errDie("Unable to add bank payment to database.", SELF);
$refnum = getrefnum($accdate);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}'";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 1) {
return "Invalid bank acc.";
}
$link = pg_fetch_array($Rx);
$link['accnum'] = $bank['accnum'];
writetrans($dept['credacc'], $link['accnum'], $accdate, $refnum, $amt, "Supplier Payment to {$sup['supname']}");
db_conn('cubit');
}
db_conn('cubit');
$Sl = "DELETE FROM suppurch WHERE balance=0::numeric(13,2)";
$Rx = db_exec($Sl);
pglib_transaction("COMMIT");
# status report
$write = "\n\t\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Bank Payment</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='datacell'><td>Bank Payment added to cash book.</td></tr>\n\t\t\t</table>";
# main table (layout with menu)
$OUTPUT = "<center>\n\t<table width='90%'>\n\t\t<tr valign='top'>\n\t\t\t<td width='50%'>{$write}</td>\n\t\t\t<td align='center'>\n\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<th>Quick Links</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><a href='bank-pay-supp.php'>Add supplier payment</a></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><a href='bank-pay-add.php'>Add Bank Payment</a></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><a href='bank-recpt-add.php'>Add Bank Receipt</a></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><a href='cashbook-view.php'>View Cash Book</a></td>\n\t\t\t\t\t</tr>\n\t\t\t\t</table>\n\t\t\t</td>\n\t\t</tr>\n\t</table>";
return $OUTPUT;
}
