function write($_POST)
{
# Get vars
extract($_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[2], $datea[0])) {
$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"] . "</li>";
}
$write .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $write;
}
# 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.</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);
}
$famt = sprint($amount);
$amount = sprint($amount * $rate);
cus_xrate_update($cust['fcid'], $rate);
xrate_update($cust['fcid'], $rate, "invoices", "invid");
xrate_update($cust['fcid'], $rate, "custran", "id");
# 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\t<td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td>\n\t\t\t\t\t<td>{$cust['accno']} - {$cust['cusname']} {$cust['surname']}</td>\n\t\t\t\t</tr>";
$samount = sprint($amount - $amount * 2);
$sfamt = sprint($famt - $famt * 2);
// recordCT($samount, $cust['cusnum']);
frecordCT($famt, $amount, $cust['cusnum'], $cust['fcid'], $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 = "\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;
$sfamt = $famt;
// recordDT($samount, $cust['cusnum']);
frecordDT($famt, $amount, $cust['cusnum'], $cust['fcid'], $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\tINSERT INTO stmnt \n\t\t\t\t(cusnum, invid, amount, date, type, st, div, allocation_date) \n\t\t\tVALUES \n\t\t\t\t('{$cust['cusnum']}', '0', '{$sfamt}', '{$date}', '{$details}', 'n', '" . USER_DIV . "', '{$date}')";
$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}'), fbalance = (fbalance + '{$sfamt}') 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 = "\n\t\t\t<h3>Journal transaction has been recorded</h3>\n\t\t\t<table " . TMPL_tblDflts . " width='500'>\n\t\t\t\t<tr>\n\t\t\t\t\t<td width='50%'><h3>Debit</h3></td>\n\t\t\t\t\t<td width='50%'><h3>Credit</h3></td>\n\t\t\t\t</tr>\n\t\t\t\t{$tran}\n\t\t\t\t" . TBL_BR . "\n\t\t\t\t<tr colspan='2'>\n\t\t\t\t\t<td><h4>Amount</h4></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='2'><b>" . CUR . " {$famt}</b></td>\n\t\t\t\t</tr>\n\t\t\t</table>\n\t\t\t<P>\n\t\t\t<table " . TMPL_tblDflts . " width='25%'>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>Quick Links</th>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td align='center'><a href='trans-new.php'>Journal Transactions</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td align='center'><a href='../customers-view.php'>View Customers</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t</table>";
return $write;
}
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($cusid, "num", 1, 10, "Invalid customer number.");
$v->isOk($out1, "float", 0, 10, "Invalid paid amount(currant).");
$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"];
}
$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) {
$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();
# Customer name
$sql = "SELECT * FROM customers WHERE cusnum = '{$cusid}' AND div = '" . USER_DIV . "'";
$cusRslt = db_exec($sql);
$cus = pg_fetch_array($cusRslt);
db_conn("exten");
# get debtors control account
$sql = "SELECT debtacc FROM departments WHERE deptid ='{$cus['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
$dept = pg_fetch_array($deptRslt);
# Update xrate
cus_xrate_update($cus['fcid'], $rate);
xrate_update($cus['fcid'], $rate, "invoices", "invid");
xrate_update($cus['fcid'], $rate, "custran", "id");
bank_xrate_update($cus['fcid'], $rate);
$lamt = sprint($amt * $rate);
# date format
$sdate = explode("-", $date);
$sdate = $sdate[2] . "-" . $sdate[1] . "-" . $sdate[0];
$cheqnum = 0 + $cheqnum;
$pay = "";
$accdate = date("Y-m-d");
# Paid invoices
$invidsers = "";
$rinvids = "";
$famounts = "";
$amounts = "";
$invprds = "";
$rages = "";
db_connect();
if ($all == 0) {
# Begin updates
# pglib_transaction ("BEGIN") or errDie("Unable to start a database transaction.",SELF);
if (isset($invids)) {
foreach ($invids as $key => $value) {
$ii = $invids[$key];
$lpaidamt[$key] = sprint($paidamt[$key] * $rate);
if (!isset($itype[$ii])) {
# Get debt invoice info
$sql = "SELECT prd,invnum,odate FROM invoices WHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.</li>";
}
$inv = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE invoices SET balance = (balance - {$lpaidamt[$key]}::numeric(13,2)),fbalance = (fbalance - {$paidamt[$key]}::numeric(13,2)) WHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$inv['invnum'] += 0;
# 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\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\t\t\tVALUES \n\t\t\t\t\t\t\t\t('{$cus['cusnum']}','{$inv['invnum']}','" . ($paidamt[$key] - $paidamt[$key] * 2) . "','{$sdate}', 'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank['accnum'], $sdate, $inv['invnum'], "Payment for Invoice No. {$inv['invnum']}", $lpaidamt[$key], "c");
db_connect();
$rinvids .= "|{$invids[$key]}";
$famounts .= "|{$paidamt[$key]}";
$amounts .= "|{$lpaidamt[$key]}";
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
} else {
# Get debt invoice info
$sql = "SELECT prd,invnum,descrip,age,odate FROM nons_invoices WHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class='err'>Invalid Invoice Number.</li>";
}
$inv = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE nons_invoices SET balance = (balance - {$lpaidamt[$key]}::numeric(13,2)), fbalance = (fbalance - {$paidamt[$key]}::numeric(13,2)) WHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$inv['invnum'] += 0;
# 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\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\t\t\tVALUES \n\t\t\t\t\t\t\t\t('{$cus['cusnum']}','{$inv['invnum']}','" . ($paidamt[$key] - $paidamt[$key] * 2) . "','{$sdate}', 'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', '" . USER_DIV . "', '{$inv['odate']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank['accnum'], $sdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}", $lpaidamt[$key], "c");
db_connect();
frecordCT($paidamt[$key], $cus['cusnum'], $rate, $cus['fcid']);
$rinvids .= "|{$invids[$key]}";
$famounts .= "|{$paidamt[$key]}";
$amounts .= "|{$lpaidamt[$key]}";
$invprds .= "|0";
$rages .= "|{$inv['age']}";
$invidsers .= " - {$inv['invnum']}";
}
}
}
# update the customer (make fbalance less)
$sql = "UPDATE customers SET balance = (balance - '{$lamt}'::numeric(13,2)), fbalance = (fbalance - '{$amt}'::numeric(13,2)) WHERE cusnum = '{$cus['cusnum']}' 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, cusnum, rinvids, amounts, invprds, rages, div) VALUES ('$bankid', 'deposit', '$sdate', '$cus[cusname] $cus[surname]', 'Payment for Invoices $invidsers from customer $cus[cusname] $cus[surname]', '$cheqnum', '$lamt', 'no', '$dept[debtacc]', '$cus[cusnum]', '$rinvids', '$amounts', '$invprds', '$rages', '".USER_DIV."')";
// 2 $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, cusnum, rinvids, amounts, invprds, rages, div) VALUES ('$bankid', 'deposit', '$sdate', '$cus[cusname] $cus[surname]', 'Payment for Invoices $invidsers from customer $cus[cusname] $cus[surname]', '$cheqnum', '$lamt', '$amt', 'no', '$dept[debtacc]', '$cus[cusnum]', '$rinvids', '$amounts', '$invprds', '$rages', '".USER_DIV."')";
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, cusnum, rinvids, amounts, famounts, invprds, rages, fcid, currency, location, div) VALUES ('{$bankid}', 'deposit', '{$sdate}', '{$cus['cusname']} {$cus['surname']}', 'Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}', '{$cheqnum}', '{$lamt}', '{$amt}', 'no', '{$dept['debtacc']}', '{$cus['cusnum']}', '{$rinvids}', '{$amounts}', '{$famounts}', '{$invprds}', '{$rages}', '{$cus['fcid']}', '{$cus['currency']}', '{$cus['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($sdate);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}' AND accid!=0";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 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, if you want to use cash functionality please create a 'Cash on Hand' account.";
} else {
return "Invalid bank acc.";
}
}
$add = pg_fetch_array($Rg);
$link['accnum'] = $add['accid'];
} else {
$link = pg_fetch_array($Rx);
}
writetrans($link['accnum'], $dept['debtacc'], $sdate, $refnum, $lamt, "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}");
db_conn('cubit');
if ($out > 0) {
frecordCT($out, $cus['cusnum'], $rate, $cus['fcid']);
$Sl = "\n\t\t\t\tINSERT INTO stmnt \n\t\t\t\t\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\tVALUES \n\t\t\t\t\t('{$cus['cusnum']}','0','" . $out * -1 . "','{$sdate}', 'Payment Received.', '" . USER_DIV . "', '{$sdate}')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank['accnum'], $sdate, "PAYMENT", "Payment received.", $out * $rate, "c");
db_connect();
}
# Commit updates
# pglib_transaction ("COMMIT") or errDie("Unable to commit a database transaction.",SELF);
}
if ($all == 1) {
# Begin updates
# pglib_transaction ("BEGIN") or errDie("Unable to start a database transaction.",SELF);
if (isset($invids)) {
foreach ($invids as $key => $value) {
$ii = $invids[$key];
$lpaidamt[$key] = sprint($paidamt[$key] * $rate);
if (!isset($itype[$ii])) {
# Get debt invoice info
$sql = "SELECT prd,invnum,odate FROM invoices WHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class=err>Invalid Invoice Number.";
}
$inv = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE invoices SET balance = (balance - '{$lpaidamt[$key]}'::numeric(13,2)), fbalance = (fbalance - {$paidamt[$key]}::numeric(13,2)) WHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$inv['invnum'] += 0;
# 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\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\t\t\tVALUES \n\t\t\t\t\t\t\t\t('{$cus['cusnum']}','{$inv['invnum']}','" . ($paidamt[$key] - $paidamt[$key] * 2) . "','{$sdate}', 'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank['accnum'], $sdate, $inv['invnum'], "Payment for Invoice No. {$inv['invnum']}", $lpaidamt[$key], "c");
db_connect();
$rinvids .= "|{$invids[$key]}";
$famounts .= "|{$paidamt[$key]}";
$amounts .= "|{$lpaidamt[$key]}";
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
} else {
# Get debt invoice info
$sql = "SELECT prd,invnum,descrip,age,odate FROM nons_invoices WHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class=err>Invalid Invoice Number.";
}
$inv = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE nons_invoices SET balance = (balance - '{$lpaidamt[$key]}'::numeric(13,2)), fbalance = (fbalance - {$paidamt[$key]}::numeric(13,2)) WHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
$inv['invnum'] += 0;
# 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\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\t\t\tVALUES \n\t\t\t\t\t\t\t\t('{$cus['cusnum']}','{$inv['invnum']}','" . ($paidamt[$key] - $paidamt[$key] * 2) . "','{$sdate}', 'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', '" . USER_DIV . "', '{$inv['odate']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank['accnum'], $sdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}", $lpaidamt[$key], "c");
db_connect();
frecordCT($paidamt[$key], $cus['cusnum'], $rate, $cus['fcid']);
$rinvids .= "|{$invids[$key]}";
$famounts .= "|{$paidamt[$key]}";
$amounts .= "|{$lpaidamt[$key]}";
$invprds .= "|0";
$rages .= "|{$inv['age']}";
$invidsers .= " - {$inv['invnum']}";
}
}
}
# update the customer (make fbalance less)
$sql = "UPDATE customers SET balance = (balance - '{$lamt}'::numeric(13,2)), fbalance = (fbalance - '{$amt}'::numeric(13,2)) WHERE cusnum = '{$cus['cusnum']}' 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, cusnum, rinvids, amounts, invprds, rages, div) VALUES ('$bankid', 'deposit', '$sdate', '$cus[cusname] $cus[surname]', 'Payment for Invoices $invidsers from customer $cus[cusname] $cus[surname]', '$cheqnum', '$lamt', 'no', '$dept[debtacc]', '$cus[cusnum]', '$rinvids', '$amounts', '$invprds', '$rages', '".USER_DIV."')";
// 2 $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, cusnum, rinvids, amounts, invprds, rages, div) VALUES ('$bankid', 'deposit', '$sdate', '$cus[cusname] $cus[surname]', 'Payment for Invoices $invidsers from customer $cus[cusname] $cus[surname]', '$cheqnum', '$lamt', '$amt', 'no', '$dept[debtacc]', '$cus[cusnum]', '$rinvids', '$amounts', '$invprds', '$rages', '".USER_DIV."')";
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, cusnum, rinvids, amounts, famounts, invprds, rages, fcid, currency, location, div) VALUES ('{$bankid}', 'deposit', '{$sdate}', '{$cus['cusname']} {$cus['surname']}', 'Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}', '{$cheqnum}', '{$lamt}', '{$amt}', 'no', '{$dept['debtacc']}', '{$cus['cusnum']}', '{$rinvids}', '{$amounts}', '{$famounts}', '{$invprds}', '{$rages}', '{$cus['fcid']}', '{$cus['currency']}', '{$cus['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($sdate);
db_conn('core');
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}' AND accid!=0";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 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, if you want to use cash functionality please create a 'Cash on Hand' account.";
} else {
return "Invalid bank acc.";
}
}
$add = pg_fetch_array($Rg);
$link['accnum'] = $add['accid'];
} else {
$link = pg_fetch_array($Rx);
}
writetrans($link['accnum'], $dept['debtacc'], $sdate, $refnum, $lamt, "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}");
db_conn('cubit');
if ($out1 + $out2 + $out3 + $out4 + $out5 > 0) {
$Sl = "\n\t\t\t\t\tINSERT INTO stmnt \n\t\t\t\t\t\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\tVALUES \n\t\t\t\t\t\t('{$cus['cusnum']}','0','" . ($out1 + $out2 + $out3 + $out4 + $out5) * -1 . "','{$sdate}', 'Payment Received.', '" . USER_DIV . "', '{$sdate}')";
$Rs = db_exec($Sl) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank['accnum'], $sdate, "PAYMENT", "Payment received.", ($out1 + $out2 + $out3 + $out4 + $out5) * $rate, "c");
db_connect();
}
if ($out1 > 0) {
frecordCT($out1, $cus['cusnum'], $rate, $cus['fcid']);
}
if ($out2 > 0) {
frecordCT($out2, $cus['cusnum'], $rate, $cus['fcid']);
}
if ($out3 > 0) {
frecordCT($out3, $cus['cusnum'], $rate, $cus['fcid']);
}
if ($out4 > 0) {
frecordCT($out4, $cus['cusnum'], $rate, $cus['fcid']);
}
if ($out5 > 0) {
frecordCT($out5, $cus['cusnum'], $rate, $cus['fcid']);
}
# Commit updates
# pglib_transaction ("COMMIT") or errDie("Unable to commit a database transaction.",SELF);
}
if ($all == 2) {
# Begin updates
//pglib_transaction ("BEGIN") or errDie("Unable to start a database transaction.",SELF);
# Debtors
foreach ($invids as $key => $value) {
$ii = $invids[$key];
$lpaidamt[$key] = sprint($paidamt[$key] * $rate);
if (!isset($itype[$ii])) {
# Get debt invoice info
$sql = "SELECT prd,invnum,odate FROM invoices WHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class=err>Invalid Invoice Number.";
}
$inv = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE invoices SET balance = (balance - '{$lpaidamt[$key]}'::numeric(13,2)), fbalance = (fbalance - {$paidamt[$key]}::numeric(13,2)) WHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
# record the payment on the statement
$sql = "\n\t\t\t\t\t\tINSERT INTO stmnt \n\t\t\t\t\t\t\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\t\tVALUES \n\t\t\t\t\t\t\t('{$cus['cusnum']}','{$inv['invnum']}','" . ($paidamt[$key] - $paidamt[$key] * 2) . "','{$sdate}', 'Payment for Invoice No. {$inv['invnum']}', '" . USER_DIV . "', '{$inv['odate']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank['accnum'], $sdate, $inv['invnum'], "Payment for Invoice No. {$inv['invnum']}", $lpaidamt[$key], "c");
db_connect();
$rinvids .= "|{$invids[$key]}";
$famounts .= "|{$paidamt[$key]}";
$amounts .= "|{$lpaidamt[$key]}";
$invprds .= "|{$inv['prd']}";
$rages .= "|0";
$invidsers .= " - {$inv['invnum']}";
} else {
# Get debt invoice info
$sql = "SELECT prd,invnum,descrip,age,odate FROM nons_invoices WHERE invid ='{$invids[$key]}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to retrieve invoice details from database.");
if (pg_numrows($invRslt) < 1) {
return "<li class=err>Invalid Invoice Number.";
}
$inv = pg_fetch_array($invRslt);
# reduce the money that has been paid
$sql = "UPDATE nons_invoices SET balance = (balance - '{$lpaidamt[$key]}'::numeric(13,2)), fbalance = (fbalance - {$paidamt[$key]}::numeric(13,2)) WHERE invid = '{$invids[$key]}' AND div = '" . USER_DIV . "'";
$payRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
# record the payment on the statement
$sql = "\n\t\t\t\t\t\tINSERT INTO stmnt \n\t\t\t\t\t\t\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\t\t\t\t\tVALUES \n\t\t\t\t\t\t\t('{$cus['cusnum']}','{$inv['invnum']}','" . ($paidamt[$key] - $paidamt[$key] * 2) . "','{$sdate}', 'Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}', '" . USER_DIV . "', '{$inv['odate']}')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
custledger($cus['cusnum'], $bank['accnum'], $sdate, $inv['invnum'], "Payment for Non Stock Invoice No. {$inv['invnum']} - {$inv['descrip']}", $lpaidamt[$key], "c");
db_connect();
frecordCT($paidamt[$key], $cus['cusnum'], $rate, $cus['fcid']);
$rinvids .= "|{$invids[$key]}";
$famounts .= "|{$paidamt[$key]}";
$amounts .= "|{$lpaidamt[$key]}";
$invprds .= "|0";
$rages .= "|{$inv['age']}";
$invidsers .= " - {$inv['invnum']}";
}
}
# update the customer (make fbalance less)
$sql = "UPDATE customers SET balance = (balance - '{$lamt}'::numeric(13,2)), fbalance = (fbalance - '{$amt}'::numeric(13,2)) WHERE cusnum = '{$cus['cusnum']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# record the payment record
//2 $sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, cusnum, rinvids, amounts, famounts, invprds, rages, div) VALUES ('$bankid', 'deposit', '$sdate', '$cus[cusname] $cus[surname]', 'Payment for Invoices $invidsers from customer $cus[cusname] $cus[surname]', '$cheqnum', '$lamt', '$amt', 'no', '$dept[debtacc]', '$cus[cusnum]', '$rinvids', '$amounts', '$famounts', '$invprds', '$rages', '".USER_DIV."')";
$sql = "INSERT INTO cashbook(bankid, trantype, date, name, descript, cheqnum, amount, famount, banked, accinv, cusnum, rinvids, amounts, famounts, invprds, rages, fcid, currency, location, div) VALUES ('{$bankid}', 'deposit', '{$sdate}', '{$cus['cusname']} {$cus['surname']}', 'Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}', '{$cheqnum}', '{$lamt}', '{$amt}', 'no', '{$dept['debtacc']}', '{$cus['cusnum']}', '{$rinvids}', '{$amounts}', '{$famounts}', '{$invprds}', '{$rages}', '{$cus['fcid']}', '{$cus['currency']}', '{$cus['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.");
$Sl = "SELECT * FROM bankacc WHERE accid='{$bankid}' AND accid!=0";
$Rx = db_exec($Sl) or errDie("Uanble to get bank acc.");
if (pg_numrows($Rx) < 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, if you want to use cash functionality please create a 'Cash on Hand' account.";
} else {
return "Invalid bank acc.";
}
}
$add = pg_fetch_array($Rg);
$link['accnum'] = $add['accid'];
} else {
$link = pg_fetch_array($Rx);
}
writetrans($link['accnum'], $dept['debtacc'], $sdate, $refnum, $lamt, "Payment for Invoices {$invidsers} from customer {$cus['cusname']} {$cus['surname']}");
# Commit updates
//pglib_transaction ("COMMIT") or errDie("Unable to commit a database transaction.",SELF);
}
db_conn('cubit');
/* start moving invoices */
# move invoices that are fully paid
$sql = "SELECT * FROM invoices WHERE fbalance = 0 AND balance = 0 AND printed = 'y' AND done = 'y' AND div = '" . USER_DIV . "'";
$invbRslt = db_exec($sql) or errDie("Unable to update Invoice information in Cubit.", SELF);
while ($invb = pg_fetch_array($invbRslt)) {
if ($invb['prd'] == 0) {
$invb['prd'] = PRD_DB;
}
db_conn($invb['prd']);
# Insert invoice to period DB
$sql = "INSERT INTO invoices(invid, invnum, deptid, cusnum, deptname, cusacc, cusname, surname, cusaddr, cusvatno, cordno, ordno, chrgvat, fcid, currency, xrate, terms, traddisc, salespn, odate, delchrg, subtot, vat, total, fbalance, location, age, comm, discount, delivery, printed, done, div)";
$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['fcid']}', '{$invb['currency']}', '{$invb['xrate']}', '{$invb['terms']}', '{$invb['traddisc']}', '{$invb['salespn']}', '{$invb['odate']}', '{$invb['delchrg']}', '{$invb['subtot']}', '{$invb['vat']}' , '{$invb['total']}', '0', '{$invb['location']}', '{$invb['age']}', '{$invb['comm']}', '{$invb['discount']}', '{$invb['delivery']}', 'y', 'y', '" . 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 inv_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
$sql = "INSERT INTO inv_items(invid, whid, stkid, qty, unitcost, amt, disc, discp, div) VALUES('{$invb['invid']}', '{$stkd['whid']}', '{$stkd['stkid']}', '{$stkd['qty']}', '{$stkd['unitcost']}', '{$stkd['amt']}', '{$stkd['disc']}', '{$stkd['discp']}', '" . USER_DIV . "')";
$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 invoices 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 inv_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 */
# status report
$write = "\n\t\t\t<table " . TMPL_tblDflts . " width='100%'>\n\t\t\t\t<tr><th>Bank Receipt</th></tr>\n\t\t\t\t<tr class='datacell'><td>Bank Receipt added to cash book.</td></tr>\n\t\t\t</table>";
# main table (layout with menu)
$OUTPUT = "\n\t\t\t<center>\n\t\t\t<table width='90%'>\n\t\t\t\t<tr valign='top'>\n\t\t\t\t\t<td width=50%>{$write}</td>\n\t\t\t\t\t<td align='center'>\n\t\t\t\t\t\t<table " . TMPL_tblDflts . " width='80%'>\n\t\t\t\t\t\t\t<tr><th>Quick Links</th></tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'><td><a href='bank-pay-add.php'>Add Bank Payment</a></td></tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'><td><a href='bank-recpt-add.php'>Add Bank Receipt</a></td></tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'><td><a href='bank-recpt-inv.php'>Add Customer Payment</a></td></tr>\n\t\t\t\t\t\t\t<tr class='" . bg_class() . "'><td><a href='cashbook-view.php'>View Cash Book</a></td></tr>\n\t\t\t\t\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t\t\t\t\t</table>\n\t\t\t\t\t</td>\n\t\t\t\t</tr>\n\t\t\t</table>";
return $OUTPUT;
}
function write($_POST)
{
# Get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($supid, "num", 1, 50, "Invalid Supplier 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[2], $datea[0])) {
$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;
}
$td = $date;
# Accounts details
$accRs = get("core", "*", "accounts", "accid", $accid);
$acc = pg_fetch_array($accRs);
# Select supplier
db_connect();
$sql = "SELECT * FROM suppliers WHERE supid = '{$supid}' AND div = '" . USER_DIV . "'";
$suppRslt = db_exec($sql) or errDie("Unable to access databse.", SELF);
if (pg_numrows($suppRslt) < 1) {
return "<li> Invalid Supplier ID.";
} else {
$supp = pg_fetch_array($suppRslt);
}
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$supp['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);
}
$famt = sprint($amount);
$amount = sprint($amount * $rate);
# update all supplies xchange rate first
xrate_update($supp['fcid'], $rate, "suppurch", "id");
sup_xrate_update($supp['fcid'], $rate);
$supp['supname'] = remval($supp['supname']);
# Probe tran type
if ($entry == "CT") {
# Write transaction (debit contra account, credit debtors control)
writetrans($accid, $dept['credacc'], $td, $refnum, $amount, $details . " - Supplier {$supp['supname']}");
$tran = "<tr class='bg-odd'><td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td><td>{$supp['supno']} - {$supp['supname']}</td></tr>";
$samount = $amount;
$sfamt = $famt;
// recordCT(-$amount, $supp['supid']);
frecordCT($famt, $amount, $supp['supid'], $supp['fcid'], $td);
$type = 'c';
} else {
# Write transaction (debit debtors control, credit contra account)
writetrans($dept['credacc'], $accid, $td, $refnum, $amount, $details . " - Supplier {$supp['supname']}");
$tran = "<tr class='bg-odd'><td>{$supp['supno']} - {$supp['supname']}</td><td>{$acc['topacc']}/{$acc['accnum']} - {$acc['accname']}</td></tr>";
$samount = sprint($amount - $amount * 2);
$sfamt = sprint($famt - $famt * 2);
// recordDT($amount, $supp['supid']);
frecordDT($famt, $amount, $supp['supid'], $supp['fcid'], $td);
$type = 'd';
}
db_connect();
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
$edate = date("Y-m-d");
# record the payment on the statement
$sql = "INSERT INTO sup_stmnt(supid, edate, ref, cacc, descript, amount, div) VALUES('{$supp['supid']}', '{$td}', '0', '{$accid}', '{$details}', '{$sfamt}', '" . 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 + '{$samount}'),fbalance = (fbalance + '{$sfamt}') WHERE supid = '{$supp['supid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update supplier in Cubit.", SELF);
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
# Ledger Records
suppledger($supp['supid'], $accid, $td, $refnum, $details, $amount, $type);
db_connect();
// Start layout
$write = "<h3>Journal transaction has been recorded</h3>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t\t<tr><td width=50%><h3>Debit</h3></td><td width=50%><h3>Credit</h3></td></tr>\n\t\t{$tran}\n\t\t<tr><td><br></td></tr>\n\t\t<tr colspan=2><td><h4>Amount</h4></td></tr>\n\t\t<tr class='bg-even'><td colspan=2><b>" . CUR . " {$famt}</b></td></tr>\n\t</table>\n\t<P>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=25%>\n\t\t<tr><th>Quick Links</th></tr>\n\t\t<tr class=datacell><td align=center><a href='trans-new.php'>Journal Transactions</td></tr>\n\t\t<tr class='bg-odd'><td align=center><a href='../supp-view.php'>View Suppliers</a></td></tr>\n\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
return $write;
}
function write($_GET)
{
# get vars
extract($_GET);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($invid, "num", 1, 20, "Invalid Invoice number.");
$sndate = mkdate($s_year, $s_month, $s_day);
if (!checkdate($s_month, $s_day, $s_year)) {
$v->addError($sdate, "Invalid Date.");
}
foreach ($ids as $key => $id) {
$v->isOk($id, "num", 1, 20, "Invalid Item number.");
// if (!is_int($qtys[$key])) {
if (!ctype_digit($qtys[$key])) {
$v->addError(0, "Invalid Item Quantity.");
}
$v->isOk($amts[$key], "float", 1, 20, "Invalid Item amount.");
}
$v->isOk($subtot, "float", 1, 20, "Invalid sub-total amount.");
$v->isOk($vat, "float", 1, 20, "Invalid vat amount.");
$v->isOk($total, "float", 1, 20, "Invalid total amount.");
# display errors, if any
if ($v->isError()) {
$err = "";
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class='err'>" . $e["msg"] . "</li>";
}
$err .= "<p><input type='button' onClick='JavaScript:history.back();' value='« Correct submission'>";
return $err;
}
db_connect();
# Get invoice info
$sql = "SELECT * FROM nons_invoices WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$invRslt = db_exec($sql) or errDie("Unable to get invoice information");
if (pg_numrows($invRslt) < 1) {
return "<i class='err'>Not Found</i>";
}
$inv = pg_fetch_array($invRslt);
# Update xrate
cus_xrate_update($inv['fcid'], $inv['xrate']);
xrate_update($inv['fcid'], $inv['xrate'], "invoices", "invid");
xrate_update($inv['fcid'], $inv['xrate'], "custran", "id");
db_connect();
# Begin updates
pglib_transaction("BEGIN") or errDie("Unable to start a database transaction.", SELF);
$refnum = getrefnum();
/*refnum*/
$real_noteid = divlastid('note', USER_DIV);
db_connect();
/* --- Start Products Display --- */
$td = $sndate;
# Products layout
$products = "";
foreach ($ids as $key => $id) {
$sql = "SELECT * FROM nons_inv_items WHERE invid = '{$invid}' AND id = '{$id}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
$stkd = pg_fetch_array($stkdRslt);
$stkacc = $stkd['accid'];
# keep records for transactions
// if(isset($totstkamt[$stkacc])){
// $totstkamt[$stkacc] += vats($amts[$key], $inv['chrgvat']);
// }else{
// $totstkamt[$stkacc] = vats($amts[$key], $inv['chrgvat']);
// }
$Sl = "SELECT * FROM vatcodes WHERE id='{$stkd['vatex']}'";
$Ri = db_exec($Sl) or errDie("Unable to get data.");
$vd = pg_fetch_array($Ri);
if ($vd['zero'] == "Yes") {
$stkd['vatex'] = "y";
}
$t = $inv['chrgvat'];
$VATP = TAX_VAT;
$stkacc = $stkd['accid'];
# keep records for transactions
if (isset($totstkamt[$stkacc])) {
if ($stkd['vatex'] == "y") {
$totstkamt[$stkacc] += $amts[$key];
$va = 0;
$inv['chrgvat'] = "";
} else {
$totstkamt[$stkacc] += vats($amts[$key], $inv['chrgvat']);
$va = sprint($stkd['amt'] - vats($amts[$key], $inv['chrgvat']));
if ($inv['chrgvat'] == "no") {
$va = sprint($amts[$key] * $VATP / 100);
}
}
} else {
if ($stkd['vatex'] == "y") {
$totstkamt[$stkacc] = $amts[$key];
$va = 0;
$inv['chrgvat'] = "";
} else {
$totstkamt[$stkacc] = vats($amts[$key], $inv['chrgvat']);
$va = sprint($amts[$key] - vats($amts[$key], $inv['chrgvat']));
if ($inv['chrgvat'] == "no") {
$va = sprint($amts[$key] * $VATP / 100);
}
}
}
$f = -vats($amts[$key], $inv['chrgvat']);
$f = $f * $inv['xrate'];
$va = $va * $inv['xrate'];
vatr($vd['id'], $td, "OUTPUT", $vd['code'], $refnum, "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid} Customer {$inv['cusname']}.", $f - $va, -$va);
$inv['chrgvat'] = $t;
$sql = "UPDATE nons_inv_items SET rqty = (rqty + '{$qtys[$key]}') WHERE id = '{$stkd['id']}'";
$sRslt = db_exec($sql);
$products .= "\n\t\t\t\t\t\t<tr valign='top'>\n\t\t\t\t\t\t\t<td>{$stkd['description']}</td>\n\t\t\t\t\t\t\t<td>{$qtys[$key]}</td>\n\t\t\t\t\t\t\t<td>{$inv['currency']} {$stkd['unitcost']}</td>\n\t\t\t\t\t\t\t<td>{$inv['currency']} {$amts[$key]}</td>\n\t\t\t\t\t\t</tr>";
}
/* --- Start Some calculations --- */
# Subtotal
$SUBTOT = sprint($subtot);
$VAT = sprint($vat);
$TOTAL = sprint($total);
$LVAT = sprint($VAT * $inv['xrate']);
$LTOTAL = sprint($TOTAL * $inv['xrate']);
/* --- End Some calculations --- */
/* - Start Hooks - */
$vatacc = gethook("accnum", "salesacc", "name", "VAT", "VAT");
/* - End Hooks - */
# todays date
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$inv['cusid']}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to view customer");
if (pg_numrows($custRslt) < 1) {
return "<li class='err'>Error : Customer not Found.</li>";
}
$cus = pg_fetch_array($custRslt);
$na = $cus['surname'];
# Get department
db_conn("exten");
$sql = "SELECT * FROM departments WHERE deptid = '{$cus['deptid']}' AND div = '" . USER_DIV . "'";
$deptRslt = db_exec($sql);
if (pg_numrows($deptRslt) < 1) {
return "<li class='err'>Department not Found.</li>";
} else {
$dept = pg_fetch_array($deptRslt);
}
$wtot = array_sum($totstkamt) + $va;
$lwtot = sprint($wtot * $inv['xrate']);
$lva = sprint($va * $inv["xrate"]);
$tpp = 0;
# record transaction from data
foreach ($totstkamt as $stkacc => $wamt) {
writetrans($stkacc, $dept['debtacc'], $sndate, $refnum, $lwtot, "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid} Customer {$inv['cusname']}.");
}
if ($lva != 0) {
writetrans($vatacc, $dept['debtacc'], $sndate, $refnum, $lva, "Non-Stock invoice No. {$inv['invnum']} Credit note No.{$real_noteid} Vat. Customer {$inv['cusname']}.");
}
db_connect();
# Record the payment on the statement
$sql = "\n\t\tINSERT INTO stmnt \n\t\t\t(cusnum, invid, amount, date, type, div, allocation_date) \n\t\tVALUES \n\t\t\t('{$inv['cusid']}', '{$real_noteid}', '-{$wtot}','{$sndate}', 'Non Stock Credit Note, for invoice {$inv['invnum']}', '" . USER_DIV . "', '{$sndate}')";
$stmntRslt = db_exec($sql) or errDie("Unable to insert statement record in Cubit.", SELF);
# Update the customer (make balance less)
$sql = "UPDATE customers \n\t\t\tSET balance = (balance - '{$lwtot}'::numeric(13,2)), \n\t\t\t\tfbalance = (fbalance - '{$wtot}'::numeric(13,2)) WHERE cusnum = '{$inv['cusid']}' AND div = '" . USER_DIV . "'";
$rslt = db_exec($sql) or errDie("Unable to update invoice in Cubit.", SELF);
# Make ledge record
custledger($inv['cusid'], $dept['incacc'], $sndate, $real_noteid, "Non Stock Credit note {$real_noteid}", $lwtot, "c");
frecordCT($wtot, $inv['cusid'], $inv['xrate'], $inv['fcid'], $sndate);
// custCT($TOTAL, $inv['cusid']);
db_connect();
$sql = "UPDATE nons_invoices \n\t\t\tSET balance = (balance - '{$lwtot}'::numeric(13,2)), \n\t\t\t\tfbalance = (fbalance - '{$wtot}'::numeric(13,2)) WHERE invid = '{$invid}' AND div = '" . USER_DIV . "'";
$upRslt = db_exec($sql) or errDie("Unable to update invoice information");
# write note
$sql = "INSERT INTO nons_inv_notes(invid, invnum, cusname, cusaddr, cusvatno, chrgvat, location, currency, date, subtot, vat, total, username, prd, notenum, ctyp, div)";
$sql .= " VALUES('{$inv['invid']}', '{$inv['invnum']}', '{$inv['cusname']}', '{$inv['cusaddr']}', '{$inv['cusvatno']}', '{$inv['chrgvat']}', 'int', '{$inv['currency']}', '{$sndate}', '" . sprint($wtot - $va) . "', {$va}, {$wtot}, '" . USER_NAME . "', '" . PRD_DB . "', '{$real_noteid}', '{$inv['ctyp']}', '" . USER_DIV . "')";
$rslt = db_exec($sql) or errDie("Unable to create template Non-Stock Invoice.", SELF);
$noteid = pglib_lastid("nons_inv_notes", "noteid");
# write note items
foreach ($ids as $key => $id) {
$sql = "SELECT * FROM nons_inv_items WHERE invid = '{$invid}' AND id = '{$id}' AND div = '" . USER_DIV . "'";
$stkdRslt = db_exec($sql);
$nstk = pg_fetch_array($stkdRslt);
$sql = "INSERT INTO nons_note_items(noteid, qty, description, amt, unitcost) \n\t\t\t\tVALUES('{$noteid}', '{$qtys[$key]}', '{$nstk['description']}', '{$amts[$key]}', \n\t\t\t\t\t'{$nstk['unitcost']}')";
$stkdRslt = db_exec($sql);
}
$sql = "INSERT INTO salesrec(edate, invid, invnum, debtacc, vat, total, typ, div)\n\tVALUES('{$sndate}', '{$noteid}', '{$real_noteid}', '0', '{$lva}', '{$lwtot}', 'nnon', '" . USER_DIV . "')";
$recRslt = db_exec($sql);
db_conn('cubit');
$Sl = "INSERT INTO sj(cid,name,des,date,exl,vat,inc,div) VALUES\n\t('{$inv['cusid']}','{$na}','Credit note: {$real_noteid}, Non-stock International Invoice {$inv['invnum']} ','{$sndate}','" . -sprint($lwtot - $lva) . "','-{$lva}','" . -sprint($lwtot) . "','" . USER_DIV . "')";
$Ri = db_exec($Sl);
# Commit updates
pglib_transaction("COMMIT") or errDie("Unable to commit a database transaction.", SELF);
/* -- Format the remarks boxlet -- */
$inv["remarks"] = "<table border=1><tr><td>Remarks:<br>{$inv['remarks']}</td></tr></table>";
$cc = "<script> CostCenter('ct', 'Credit Note', '{$sndate}', 'Non Stock Credit Note No.{$real_noteid}', '" . ($LTOTAL - $LVAT) . "', ''); </script>";
/* -- Final Layout -- */
$details = "\n\t\t\t\t\t{$cc}\n\t\t\t\t\t<center>\n\t\t\t\t\t<h2>Credit Note</h2>\n\t\t\t\t\t<table cellpadding='0' cellspacing='4' border='0' width='750'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td valign='top' width='30%'>\n\t\t\t\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t\t\t\t<td>{$inv['cusname']}</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>" . nl2br($inv['cusaddr']) . "</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>(Vat No. {$inv['cusvatno']})</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\t<td valign='top' width='30%'>\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\tVat No. " . COMP_VATNO . "\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t\t<td width='20%'><img src='compinfo/getimg.php' width=230 height=47></td>\n\t\t\t\t\t\t\t<td valign='bottom' align='right' width='20%'>\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>Credit Note No.</b></td>\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$real_noteid}</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 No.</b></td>\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$inv['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>Date</b></td>\n\t\t\t\t\t\t\t\t\t\t<td valign='center'>{$sndate}</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='4'>\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 width='65%'>DESCRIPTION</th>\n\t\t\t\t\t\t\t\t\t\t<th width='10%'>QTY</th>\n\t\t\t\t\t\t\t\t\t\t<th width='10%'>UNIT PRICE</th>\n\t\t\t\t\t\t\t\t\t\t<th width='10%'>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>{$inv['remarks']}</td>\n\t\t\t\t\t\t\t<td align='right' colspan='3'>\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'>{$inv['currency']} {$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>VAT @ " . TAX_VAT . "%</b></td>\n\t\t\t\t\t\t\t\t\t\t<td align='right'>{$inv['currency']} {$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'>{$inv['currency']} {$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</table>\n\t\t\t\t\t</center>";
$OUTPUT = $details;
require "tmpl-print.php";
}
