function save()
{
db_connect();
$sql = "SELECT * FROM customers";
$custRslt = db_exec($sql) or errDie("Unable to view customer");
if (pg_numrows($custRslt) < 1) {
return "<li class=err>Invalid Customer Number.";
}
$Sl = "DELETE FROM ages";
$Ri = db_exec($Sl);
while ($cust = pg_fetch_array($custRslt)) {
if ($cust['location'] == 'int') {
$cust['balance'] = $cust['fbalance'];
}
$cust['balance'] = sprint($cust['balance']);
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$curr = ageage($cust['cusnum'], 0, $cust['fcid'], $cust['location']);
$age30 = ageage($cust['cusnum'], 1, $cust['fcid'], $cust['location']);
$age60 = ageage($cust['cusnum'], 2, $cust['fcid'], $cust['location']);
$age90 = ageage($cust['cusnum'], 3, $cust['fcid'], $cust['location']);
$age120 = ageage($cust['cusnum'], 4, $cust['fcid'], $cust['location']);
} else {
$curr = age($cust['cusnum'], 29, $cust['fcid'], $cust['location']);
$age30 = age($cust['cusnum'], 59, $cust['fcid'], $cust['location']);
$age60 = age($cust['cusnum'], 89, $cust['fcid'], $cust['location']);
$age90 = age($cust['cusnum'], 119, $cust['fcid'], $cust['location']);
$age120 = age($cust['cusnum'], 149, $cust['fcid'], $cust['location']);
}
$custtot = $curr + $age30 + $age60 + $age90 + $age120;
if (sprint($custtot) != sprint($cust['balance'])) {
$curr = sprint($curr + $cust['balance'] - $custtot);
$custtot = sprint($cust['balance']);
}
$Sl = "INSERT INTO ages(cust,curr,age30,age60,age90,age120) VALUES('{$cust['cusnum']}','{$curr}','{$age30}','{$age60}','{$age90}','{$age120}')";
$Ri = db_exec($Sl);
$age = "<table cellpadding='3' cellspacing='1' border=0 width=100% bordercolor='#000000'>\n\t\t\t<tr><th>Current</th><th>30 days</th><th>60 days</th><th>90 days</th><th>120 days +</th></tr>\n\t\t\t<tr><td align=right>{$cust['currency']} {$curr}</td><td align=right>{$cust['currency']} {$age30}</td><td align=right>{$cust['currency']} {$age60}</td>\n\t\t\t<td align=right>{$cust['currency']} {$age90}</td><td align=right>{$cust['currency']} {$age120}</td></tr>\n\t\t\t</table>";
}
$out = "<p><br><table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t<tr><th>Done</th></tr>\n\t<tr class='bg-odd'><td>Age analysis saved.</td></tr>\n\t</table>";
return $out;
}
function printAgeAge()
{
# Set up table to display in
$printCust = "\n <h3>Debtors Age Analysis</h3>\n <table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n <tr><th>Acc no.</th><th>Customer</th><th>Contact Name</th><th>Tel No.</th><th>Current</th><th>30 days</th><th>60 days</th><th>90 days</th><th>120 days</th><th>Total Outstanding</th></tr>";
# Connect to database
db_connect();
# Query server
$i = 0;
$sql = "SELECT * FROM customers ORDER BY accno ASC";
$custRslt = db_exec($sql) or errDie("Unable to retrieve Customers from database.");
if (pg_numrows($custRslt) < 1) {
return "<li>There are no Customers in Cubit.";
}
# Totals
$totcurr = 0;
$tot30 = 0;
$tot60 = 0;
$tot90 = 0;
$tot120 = 0;
$alltot = 0;
while ($cust = pg_fetch_array($custRslt)) {
# Get all ages
$curr = ageage($cust['cusnum'], 0);
$age30 = ageage($cust['cusnum'], 1);
$age60 = ageage($cust['cusnum'], 2);
$age90 = ageage($cust['cusnum'], 3);
$age120 = ageage($cust['cusnum'], 4);
# Customer total
$custtot = $curr + $age30 + $age60 + $age90 + $age120;
# Alternate bgcolor
$printCust .= "<tr class='" . bg_class() . "'><td>{$cust['accno']}</td><td>{$cust['surname']}</td><td>{$cust['contname']}</td><td>{$cust['tel']}</td><td>" . CUR . " {$curr}</td><td>" . CUR . " {$age30}</td><td>" . CUR . " {$age60}</td><td>" . CUR . " {$age90}</td><td>" . CUR . " {$age120}</td><td>" . CUR . " {$custtot}</td></tr>";
# Hold totals
$totcurr += $curr;
$tot30 += $age30;
$tot60 += $age60;
$tot90 += $age90;
$tot120 += $age120;
$alltot += $custtot;
$i++;
}
$printCust .= "<tr><td><br></td></tr>\n\t<tr class='bg-even'><td colspan=4><b>Totals</b></td><td><b>" . CUR . " {$totcurr}</b></td><td><b>" . CUR . " {$tot30}</b></td><td><b>" . CUR . " {$tot60}</b></td><td><b>" . CUR . " {$tot90}</b></td><td><b>" . CUR . " {$tot120}</b></td><td><b>" . CUR . " {$alltot}</b></td></tr>\n\t<tr><td><br></td></tr>\n\t<tr><td align=center colspan=10>\n\t\t<form action='../xls/debt-age-analysis-xls.php' method=post name=form>\n\t\t<input type=submit name=xls value='Export to spreadsheet'>\n\t\t</form>\n\t</td></tr>\n\t</table>\n <p>\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=15%>\n <tr><td><br></td></tr>\n <tr><th>Quick Links</th></tr>\n\t\t<tr class='bg-odd'><td><a href='../customers-new.php'>Add Customer</a></td></tr>\n\t\t<tr class='bg-odd'><td><a href='../customers-view.php'>View Customers</a></td></tr>\n\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t</table>";
return $printCust;
}
function print_stmnt()
{
extract($_REQUEST);
define("PAGE_SPLIT", 25);
$fields = array();
$fields["cusnum"] = 0;
$fields["stmnt_type"] = "detailed";
$fields["from_year"] = date("Y");
$fields["from_month"] = date("m");
$fields["from_day"] = "01";
$fields["to_year"] = date("Y");
$fields["to_month"] = date("m");
$fields["to_day"] = date("d");
$fields["comments"] = "[_BLANK_]";
extract($fields, EXTR_SKIP);
if (isset($b64_comments)) {
$comments = base64_decode($b64_comments);
}
if (!checkdate($from_month, $from_day, $from_year)) {
$from_day = date("d");
$from_month = date("m");
$from_year = date("Y");
}
if (!checkdate($to_month, $to_day, $to_year)) {
$to_day = date("d");
$to_month = date("m");
$to_year = date("Y");
}
// Date Selections Concatenated
$from_date = "{$from_year}-{$from_month}-{$from_day}";
$to_date = "{$to_year}-{$to_month}-{$to_day}";
// Border styles
$rborder = "style='border-right: 2px solid #000'";
$bborder = "style='border-bottom: 2px solid #000'";
$tborder = "style='border-top: 2px solid #000'";
$thborder = "style='border-right: 2px solid #000; border-bottom: 2px solid #000'";
$aborder = "style='border-right: 2px solid #000; border-top: 2px solid #000'";
$br = "<br style='line-height: 2px'>";
$page_break = "<br style='page-break-after:always;'>";
// Retrieve customer information
$sql = "\n\t\tSELECT cusnum, accno, surname, balance, paddr1, addr1, fcid, location, bankid \n\t\tFROM cubit.customers\n\t\tWHERE cusnum='{$cusnum}'";
$cust_rslt = db_exec($sql) or errDie("Unable to retrieve customer information.");
$cust_data = pg_fetch_array($cust_rslt);
// Retrieve company information
$sql = "\n\t\tSELECT compname, addr1, addr2, addr3, addr4, tel, fax, vatnum, regnum\n\t\tFROM cubit.compinfo";
$comp_rslt = db_exec($sql) or errDie("Unable to retrieve company information.");
$comp_data = pg_fetch_array($comp_rslt);
#handle unset bank information
if ($cust_data['bankid'] == "0") {
$get_bid = "SELECT * FROM bankacct LIMIT 1";
$run_bid = db_exec($get_bid) or errDie("Unable to get default bank information.");
if (pg_numrows($run_bid) < 1) {
#no bank accounts in cubit ????
$bank_data = array();
$bank_data['bankname'] = "";
$bank_data['branchname'] = "";
$bank_data['branchcode'] = "";
$bank_data['accnum'] = "";
} else {
$cust_data['bankid'] = pg_fetch_result($run_bid, 0, 0);
$bank_data = qryBankAcct($cust_data['bankid']);
}
} else {
$bank_data = qryBankAcct($cust_data['bankid']);
}
// Retrieve banking details
// $bank_data = qryBankAcct(getdSetting("BANK_DET"));
// Should payments or credit notes be displayed
$payment_sql = "";
if ($stmnt_type == "open") {
$payment_sql = "\n\t\tAND type NOT LIKE 'Payment for%'\n\t\tAND type NOT LIKE '%Credit Note%for invoice%'\n\t\tAND (allocation = '0' OR allocation = '')";
}
// Retrieve statement information
$sql = "\n\t\tSELECT id, date, invid, type, amount, docref, refnum FROM cubit.stmnt \n\t\tWHERE cusnum='{$cusnum}' {$payment_sql} AND date BETWEEN '{$from_date}' AND '{$to_date}' \n\t\tORDER BY date, allocation_date, invid, allocation ASC";
$stmnt_rslt = db_exec($sql) or errDie("Unable to retrieve statement.");
// Retrieve balance before the 'from date'
$sql = "\n\t\tSELECT sum(amount) \n\t\tFROM cubit.stmnt \n\t\tWHERE cusnum='{$cusnum}' AND date<'{$from_date}'";
$balance_rslt = db_exec($sql) or errDie("Unable to retrieve balance.");
$balance = pg_fetch_result($balance_rslt, 0);
$stmnt_ar = array();
$pages = 1;
$item_count = 0;
while ($stmnt_data = pg_fetch_array($stmnt_rslt)) {
// Deduct payments and credit notes from balances only
// if this is an open item statement
if ($stmnt_type == "open" && ($stmnt_data["type"] == "Invoice" || $stmnt_data["type"] == "Non-Stock Invoice")) {
$sql = "\n\t\t\t\tSELECT sum(amount) \n\t\t\t\tFROM cubit.stmnt\n\t\t\t\tWHERE \n\t\t\t\t\ttype LIKE 'Payment for % {$stmnt_data['invid']}' OR \n\t\t\t\t\ttype LIKE '%Credit Note%for invoice%{$stmnt_data['invid']}' OR \n\t\t\t\t\tallocation = '{$stmnt_data['id']}'";
$payment_rslt = db_exec($sql) or errDie("Unable to retrieve payments.");
$payment = pg_fetch_result($payment_rslt, 0);
// If the amount has been paid/credit note'ed in full
// then no need to display this line
if ($stmnt_data["amount"] == $payment * -1) {
continue;
}
$stmnt_data["amount"] += $payment;
}
// Increase the balance
$balance += $stmnt_data["amount"];
// What should we prepend the ref num with, either invoice or credit note
if (preg_match("/Payment/", $stmnt_data["type"])) {
$refnum = "";
} elseif (preg_match("/Invoice\$/", $stmnt_data["type"])) {
$refnum = "INV";
} elseif (preg_match("/Credit Note/", $stmnt_data["type"])) {
$refnum = "CR";
}
if (isset($refnum)) {
$refnum .= " " . $stmnt_data["invid"];
} else {
$refnum = "";
}
if (empty($refnum)) {
$refnum = $stmnt_data["invid"];
}
if ($stmnt_type == "open") {
$show_bal = "";
} else {
$show_bal = "<td align='right'>" . sprint($balance) . "</td>";
}
if ($stmnt_data['type'] == "Invoice") {
db_connect();
$get_invid = "SELECT invid FROM invoices WHERE invnum = '{$stmnt_data['invid']}' LIMIT 1";
$run_invid = db_exec($get_invid) or errDie("Unable to get invoice information.");
if (pg_numrows($run_invid) == 1) {
$stmnt_invid = pg_fetch_result($run_invid, 0, 0);
$showtype = "<font onClick=\"window.open('invoice-reprint.php?invid={$stmnt_invid}&type=invreprint','window1','height=600, width=900, scrollbars=yes');\">{$stmnt_data['type']}</font>";
} else {
$showtype = $stmnt_data['type'];
}
} elseif ($stmnt_data['type'] == "Non-Stock Invoice") {
db_connect();
$get_invid = "SELECT invid FROM nons_invoices WHERE invnum = '{$stmnt_data['invid']}' LIMIT 1";
$run_invid = db_exec($get_invid) or errDie("Unable to get non stock invoice information.");
if (pg_numrows($run_invid) == 1) {
$stmnt_invid = pg_fetch_result($run_invid, 0, 0);
$showtype = "<font onClick=\"window.open('nons-invoice-reprint.php?invid={$stmnt_invid}&type=nonsreprint','window1','height=600, width=900, scrollbars=yes');\">{$stmnt_data['type']}</font>";
} else {
$showtype = $stmnt_data['type'];
}
} else {
$showtype = "{$stmnt_data['type']}";
}
// Add the line to the current page
$stmnt_ar[$pages][] = "\n\t\t\t<tr>\n\t\t\t\t<td align='center' {$rborder}>\n\t\t\t\t\t" . date("d-m-Y", strtotime($stmnt_data["date"])) . " \n\t\t\t\t</td>\n\t\t\t\t<td align='center' {$rborder}>{$refnum} </td>\n\t\t\t\t<td align='center' {$rborder}>{$stmnt_data['docref']} </td>\n\t\t\t\t<td {$rborder}>{$showtype} </td>\n\t\t\t\t<td align='right' {$rborder}>" . sprint($stmnt_data["amount"]) . " </td>\n\t\t\t\t{$show_bal}\n\t\t\t</tr>";
unset($refnum);
$item_count++;
// Time for a new page
if ($item_count == PAGE_SPLIT) {
$pages++;
$item_count = 0;
}
}
if ($stmnt_type == "open") {
$show_bal_space = "";
$unmatch = "Unmatched";
} else {
$show_bal_space = "<td> </td>";
$unmatch = "";
}
// If there's wasn't one single line returned from the database
// at the very least make the user aware of this.
if (count($stmnt_ar) == 0) {
$stmnt_ar[1][] = "\n\t\t\t<tr>\n\t\t\t\t<td {$rborder}> </td>\n\t\t\t\t<td {$rborder}> </td>\n\t\t\t\t<td {$rborder}> </td>\n\t\t\t\t<td {$rborder} align='center'><b>No {$unmatch} Invoices for this date range.</b></td>\n\t\t\t\t<td {$rborder}> </td>\n\t\t\t\t{$show_bal_space}\n\t\t\t</tr>";
}
// Generate blank lines to fill the the page
foreach ($stmnt_ar as $page => $lv2) {
$blank_lines = PAGE_SPLIT - count($stmnt_ar[$page]);
for ($i = 0; $i < $blank_lines; $i++) {
$stmnt_ar[$page][] = "\n\t\t\t\t<tr>\n\t\t\t\t\t<td {$rborder}> </td>\n\t\t\t\t\t<td {$rborder}> </td>\n\t\t\t\t\t<td {$rborder}> </td>\n\t\t\t\t\t<td {$rborder}> </td>\n\t\t\t\t\t<td {$rborder}> </td>\n\t\t\t\t\t{$show_bal_space}\n\t\t\t\t</tr>";
}
}
// Decide which radio button should be selected
if ($stmnt_type == "detailed") {
$detailed_sel = "checked='checked'";
$open_sel = "";
} elseif ($stmnt_type == "open") {
$detailed_sel = "";
$open_sel = "checked='checked'";
}
// Comments
if ($comments == "[_BLANK_]") {
$sql = "\n\t\t\tSELECT value FROM cubit.settings \n\t\t\tWHERE constant='DEFAULT_STMNT_COMMENTS'";
$comment_rslt = db_exec($sql) or errDie("Unable to retrieve comments.");
$comments = base64_decode(pg_fetch_result($comment_rslt, 0));
}
// Get age analysis
if (div_isset("DEBT_AGE", "mon")) {
#shouldnt be used ...
$curr = ageage($cust_data['cusnum'], 0, $cust_data['fcid'], $cust_data['location']);
$age30 = ageage($cust_data['cusnum'], 1, $cust_data['fcid'], $cust_data['location']);
$age60 = ageage($cust_data['cusnum'], 2, $cust_data['fcid'], $cust_data['location']);
$age90 = ageage($cust_data['cusnum'], 3, $cust_data['fcid'], $cust_data['location']);
$age120 = ageage($cust_data['cusnum'], 4, $cust_data['fcid'], $cust_data['location']);
} else {
#this is the used setting ...
$curr = cust_age($cust_data['cusnum'], 29, $cust_data['fcid'], $cust_data['location'], $to_month, $to_date, $from_date);
$age30 = cust_age($cust_data['cusnum'], 59, $cust_data['fcid'], $cust_data['location'], $to_month, $to_date, $from_date);
$age60 = cust_age($cust_data['cusnum'], 89, $cust_data['fcid'], $cust_data['location'], $to_month, $to_date, $from_date);
$age90 = cust_age($cust_data['cusnum'], 119, $cust_data['fcid'], $cust_data['location'], $to_month, $to_date, $from_date);
$age120 = cust_age($cust_data['cusnum'], 149, $cust_data['fcid'], $cust_data['location'], $to_month, $to_date, $from_date);
}
$custtot = $curr + $age30 + $age60 + $age90 + $age120;
$OUTPUT = "\n\t\t<center>\n\t\t<style>\n\t\t\ttable { border: 2px solid #000 }\n\t\t\tinput, textarea { border: 1px solid #000 }\n\t\t</style>";
// Statement settings, only display when not printing
if (!isset($key) || $key != "print") {
$OUTPUT .= "\n\t\t\t<form method='post' action='" . SELF . "' name='form'>\n\t\t\t<input type='hidden' name='cusnum' value='{$cusnum}' />\n\t\t\t<table " . TMPL_tblDflts . " style='border: 1px solid #000'>\n\t\t\t\t<tr class='bg-even'>\n\t\t\t\t\t<td colspan='3' align='center'>\n\t\t\t\t\t\t<input type='radio' name='stmnt_type' value='detailed'\n\t\t\t\t\t\tonchange='javascript:document.form.submit()' {$detailed_sel}>\n\t\t\t\t\t\tDetailed\n\t\t\t\t\t\t<input type='radio' name='stmnt_type' value='open'\n\t\t\t\t\t\tonchange='javascript:document.form.submit()' {$open_sel}>\n\t\t\t\t\t\tOpen Item\n\t\t\t\t\t</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td>" . mkDateSelect("from", $from_year, $from_month, $from_day) . "</td>\n\t\t\t\t\t<td align='center'> <b>To</b> </td>\n\t\t\t\t\t<td>" . mkDateSelect("to", $to_year, $to_month, $to_day) . "</td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='3' align='center'><b>Comments</b></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td colspan='3'>\n\t\t\t\t\t\t<textarea name='comments' style='width: 100%'>{$comments}</textarea>\n\t\t\t\t\t</td>\n\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t<td align='center'>\n\t\t\t\t\t\t<input type='button' value='Print'\n\t\t\t\t\t\tonclick='javascript:popupOpen(\"" . SELF . "?" . "key=print&cusnum={$cusnum}&stmnt_type={$stmnt_type}&" . "b64_comments=" . base64_encode($comments) . "&from_year={$from_year}" . "&from_month={$from_month}&from_day={$from_day}&to_year={$to_year}&" . "to_month={$to_month}&to_day={$to_day}\");' />\n\t\t\t\t\t</td>\n\t\t\t\t\t<td><input type='submit' value='Apply' style='font-weight: bold' /></td>\n\t\t\t\t\t<td align='center'>\n\t\t\t\t\t\t<input type='button' value='View PDF'\n\t\t\t\t\t\tonclick='javascript:popupOpen(\"pdf/pdf-statement.php?" . "key=cust_statement&cusnum={$cusnum}&stmnt_type={$stmnt_type}&" . "from_year={$from_year}&from_month={$from_month}&from_day={$from_day}&" . "to_year={$to_year}&to_month={$to_month}&to_day={$to_day}&" . "b64_comments=" . base64_encode($comments) . "\");' />\n\t\t\t\t\t</td>\n\t\t\t\t</tr>\n\t\t\t</table>\n\t\t\t{$page_break}";
}
if ($stmnt_type == "open") {
$show_bal_head = "";
} else {
$show_bal_head = "<th width='15%' {$bborder}>Balance</th>";
}
// Actual Statement per page
for ($i = 1; $i <= $pages; $i++) {
$stmnt_out = "";
foreach ($stmnt_ar[$i] as $items_out) {
$stmnt_out .= $items_out;
}
$OUTPUT .= "\n\t\t</form>\n\t\t<table cellpadding='5' cellspacing='2'' width='90%'>\n\t\t\t<tr>\n\t\t\t\t<td colspan='2' align='center'><b>Page {$i}</b></td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td rowspan='2' valign='middle'>\n\t\t\t\t\t<h1>" . COMP_NAME . "</h3>\n\t\t\t\t</td>\n\t\t\t\t<td width='10%' align='center'>\n\t\t\t\t\t<h1>Statement</h3>\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td align='center' nowrap>\n\t\t\t\t\t<h3>\n\t\t\t\t\t\t" . date("d-m-Y", strtotime($from_date)) . " -\n\t\t\t\t\t\t" . date("d-m-Y", strtotime($to_date)) . "\n\t\t\t\t\t</h3>\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>\n\t\t\n\t\t{$br}\n\t\t\n\t\t<table cellpadding='0' cellspacing='0' width='90%'>\n\t\t\t<tr>\n\t\t\t\t<td valign='top' {$rborder}>\n\t\t\t\t\t{$cust_data['surname']}<br />\n\t\t\t\t\t" . nl2br($cust_data["paddr1"]) . "<br />\n\t\t\t\t\t<br />\n\t\t\t\t\t<b>Account Number:</b> {$cust_data['accno']}<br />\n\t\t\t\t</td>\n\t\t\t\t\n\t\t\t\t<td valign='top'>\n\t\t\t\t\t{$comp_data['compname']}<br />\n\t\t\t\t\t{$comp_data['addr1']}<br />\n\t\t\t\t\t{$comp_data['addr2']}<br />\n\t\t\t\t\t{$comp_data['addr3']}<br />\n\t\t\t\t\t{$comp_data['addr4']}<br />\n\t\t\t\t\t<br />\n\t\t\t\t\t<b>Tel:</b> {$comp_data['tel']}<br />\n\t\t\t\t\t<b>Fax:</b> {$comp_data['fax']}<br />\n\t\t\t\t\t<b>VAT Reg:</b> {$comp_data['vatnum']}<br />\n\t\t\t\t\t<b>Company Reg:</b> {$comp_data['regnum']}<br />\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t</table>\n\t\t{$br}\n\t\t<table cellpadding='0' cellspacing='0' width='90%'>\n\t\t\t<tr>\n\t\t\t\t<th width='10%' {$thborder}>Date</th>\n\t\t\t\t<th width='10%' {$thborder}>Ref No.</th>\n\t\t\t\t<th width='10%' {$thborder}>Customer Ref No.</th>\n\t\t\t\t<th width='40%' {$thborder}>Details</th>\n\t\t\t\t<th width='15%' {$thborder}>Amount</th>\n\t\t\t\t{$show_bal_head}\n\t\t\t</tr>\n\t\t\t{$stmnt_out}\n\t\t</table>\n\t\t{$br}\n\t\t<table cellpadding='0' cellspacing='0' width='90%'>\n\t\t\t<tr>\n\t\t\t\t<td colspan='5' align='right'>\n\t\t\t\t\t \n\t\t\t\t\t<br />\n\t\t\t\t\t<b>Total Outstanding Balance:</b> " . sprint($balance) . "\n\t\t\t\t\t<br />\n\t\t\t\t\t \n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td width='20%' {$aborder}>\n\t\t\t\t\t<b>120+ Days</b><br />\n\t\t\t\t\t{$age120}\n\t\t\t\t</td>\n\t\t\t\t<td width='20%' {$aborder}>\n\t\t\t\t\t<b>90 Days</b><br />\n\t\t\t\t\t{$age90}\n\t\t\t\t</td>\n\t\t\t\t<td width='20%' {$aborder}>\n\t\t\t\t\t<b>60 Days</b><br />\n\t\t\t\t\t{$age60}\n\t\t\t\t</td>\n\t\t\t\t<td width='20%' {$aborder}>\n\t\t\t\t\t<b>30 Days</b><br />\n\t\t\t\t\t{$age30}\n\t\t\t\t</td>\n\t\t\t\t<td width='20%' {$tborder}>\n\t\t\t\t\t<b>Current</b><br />\n\t\t\t\t\t{$curr}\n\t\t\t\t</td>\n\t\t</table>\n\n\t\t{$br}\t\t\n\n\t\t<table cellpadding='0' cellspacing='0' width=90%'>\n\t\t\t<tr>\n\t\t\t\t<td rowspan='5' {$rborder} width='50%'>" . nl2br($comments) . " </td>\n\t\t\t</tr>\n\t\t\t<tr><td>{$bank_data['bankname']} </td></tr>\n\t\t\t<tr><td><b>Branch:</b> {$bank_data['branchname']}</td></tr>\n\t\t\t<tr><td><b>Branch Code:</b> {$bank_data['branchcode']}</td></tr>\n\t\t\t<tr><td><b>Account Number:</b> {$bank_data['accnum']}</td></tr>\n\t\t</table>";
if ($i >= 1) {
$OUTPUT .= $page_break;
}
}
$OUTPUT .= "\n\t</center>";
require "tmpl-print.php";
}
function age($cusnum, $days)
{
$ldays = $days;
if ($days == 149) {
$ldays = 365 * 10;
}
if (div_isset("DEBT_AGE", "mon")) {
switch ($days) {
case 29:
return ageage($cusnum, 0);
case 59:
return ageage($cusnum, 1);
case 89:
return ageage($cusnum, 2);
case 119:
return ageage($cusnum, 3);
case 149:
return ageage($cusnum, 4);
}
}
# Get the current oustanding
$sql = "SELECT sum(balance) FROM cubit.invoices\n\t\t\tWHERE cusnum = '{$cusnum}' AND printed = 'y'\n\t\t\t\tAND odate >='" . extlib_ago($ldays) . "'\n\t\t\t\tAND odate <'" . extlib_ago($days - 30) . "'\n\t\t\t\tAND div = '" . USER_DIV . "'";
$rs = db_exec($sql) or errDie("Unable to access database");
$sum = pg_fetch_array($rs);
# Get the current oustanding on transactions
$sql = "SELECT sum(balance) FROM cubit.custran\n\t\t\tWHERE cusnum = '{$cusnum}' AND odate >='" . extlib_ago($ldays) . "'\n\t\t\t\tAND odate <'" . extlib_ago($days - 30) . "'\n\t\t\t\tAND div = '" . USER_DIV . "'";
$rs = db_exec($sql) or errDie("Unable to access database");
$sumb = pg_fetch_array($rs);
# Take care of nasty zero
return sprint($sum['sum'] + $sumb['sum']);
}
function cusDetailsAll()
{
extract($_REQUEST);
global $set_mainFont;
$fields = array();
$fields["stmnt_type"] = "detailed";
extract($fields, EXTR_SKIP);
if (!isset($report_type)) {
$report_type = "all";
}
switch ($report_type) {
case "all":
$search = "";
break;
case "bal":
$search = "balance != '0.00' AND ";
break;
default:
$search = "true";
}
$search2 = "";
if (isset($filt_class) and strlen($filt_class) > 0 and $filt_class != "0") {
$search2 .= " class = '{$filt_class}' AND ";
}
if (isset($filt_cat) and strlen($filt_cat) > 0 and $filt_cat != "0") {
$search2 .= " category = '{$filt_cat}' AND ";
}
$from_date = dateFmt($from_year, $from_month, $from_day);
$to_date = dateFmt($to_year, $to_month, $to_day);
$fdate = $from_date;
$pdf =& new Cezpdf();
$pdf->selectFont($set_mainFont);
// Heading --------------------------------------------------------------
$heading = array(array(''));
#check for sort ...
if (isset($sort) and $sort == "branch") {
$sortinga = "ORDER BY branch";
$sorting = "branch,";
} else {
$sortinga = "";
$sorting = "";
}
// Customer info ---------------------------------------------------------
db_conn("cubit");
$sql = "SELECT * FROM customers WHERE {$search} {$search2} div='" . USER_DIV . "' ORDER BY surname";
$custmnt_rslt = db_exec($sql) or errDie("Unable to retrieve customer information from Cubit.");
if (pg_numrows($custmnt_rslt) < 1) {
return "<li class='err'>No Customers Found Matching Criteria.</li>";
}
while ($cust_data = pg_fetch_array($custmnt_rslt)) {
$totout = 0;
$cusnum = $cust_data["cusnum"];
// Company info ----------------------------------------------------------
db_conn("cubit");
$sql = "SELECT * FROM compinfo WHERE div='" . USER_DIV . "'";
$ciRlst = db_exec($sql) or errDie("Unable to retrieve the company information from Cubit.");
$compinf = pg_fetch_array($ciRlst);
$compinfo = array(array(COMP_NAME), array("{$compinf['addr1']}"), array("{$compinf['addr2']}"), array("{$compinf['addr3']}"), array("{$compinf['addr4']}"), array(""), array("<b>Tel:</b> {$compinf['tel']}"), array("<b>Fax:</b> {$compinf['fax']}"), array("<b>VAT REG:</b> {$compinf['vatnum']}"), array("<b>COMPANY REG:</b> {$compinf['regnum']}"));
$info = array();
/* base for balance brought forward */
$info[0] = array("Date" => "", "Ref no" => "", "Details" => "<b>Balance Brought Forward: </b>", "Amount" => "", "Balance" => "");
#check for sort ...
if (isset($sort) and $sort == "branch") {
$sortinga = "ORDER BY branch, date";
$sorting = "branch,";
} else {
$sortinga = "ORDER BY date";
$sorting = "";
}
// Should payments or credit notes be displayed
$payment_sql = "";
if ($stmnt_type == "open") {
$payment_sql = "\n\t\t\tAND type NOT LIKE 'Payment for%'\n\t\t\tAND type NOT LIKE '%Credit Note%for invoice%'";
}
// Retrieve statement information
$sql = "\n\t\tSELECT date, invid, type, amount, docref, branch FROM cubit.stmnt\n\t\tWHERE cusnum='{$cusnum}' {$payment_sql} AND\n\t\t\tdate BETWEEN '{$from_date}' AND '{$to_date}'\n\t\tORDER BY date, id ASC";
$stmnt_rslt = db_exec($sql) or errrDie("Unable to retrieve statement.");
// Retrieve balance before the 'from date'
$sql = "\n\t\tSELECT sum(amount) FROM cubit.stmnt\n\t\tWHERE cusnum='{$cusnum}' AND date < '{$from_date}'";
$balance_rslt = db_exec($sql) or errDie("Unable to retrieve balance.");
$balance = pg_fetch_result($balance_rslt, 0);
// $oldest_date = mktime(0, 0, 0, date("m"), 1, date("Y"));
$oldest_date = mktime(0, 0, 0, $from_month, $from_day - 1, $from_year);
if (pg_numrows($stmnt_rslt) < 1) {
$info[] = array("Date" => "", "Ref no" => "", "Details" => "No invoices for this month", "Amount" => "");
// Fill the info array
} else {
while ($stmnt_data = pg_fetch_array($stmnt_rslt)) {
// Deduct payments and credit notes from balances only
// if this is an open item statement
if ($stmnt_type == "open" && ($stmnt_data["type"] == "Invoice" || $stmnt_data["type"] == "Non-Stock Invoice")) {
$sql = "\n\t\t\t\t\tSELECT sum(amount) FROM cubit.stmnt\n\t\t\t\t\tWHERE type LIKE 'Payment for % {$stmnt_data['invid']}'\n\t\t\t\t\t\tOR type LIKE '%Credit Note%for invoice%{$stmnt_data['invid']}'";
$payment_rslt = db_exec($sql) or errDie("Unable to retrieve payments.");
$payment = pg_fetch_result($payment_rslt, 0);
// If the amount has been paid/credit note'ed in full
// then no need to display this line
if ($stmnt_data["amount"] == $payment * -1) {
continue;
}
$stmnt_data["amount"] += $payment;
}
// Increase the balance
$balance += $stmnt_data["amount"];
// What should we prepend the ref num with, either invoice or credit note
if (preg_match("/Invoice/", $stmnt_data["type"])) {
$refnum = "INV";
} elseif (preg_match("/Credit Note/", $stmnt_data["type"])) {
$refnum = "CR";
} else {
$refnum = "";
}
$refnum .= " " . $stmnt_data["invid"];
$info[] = array("Date" => makewidth(&$pdf, 60, 12, $stmnt_data['date']), "Ref no" => makewidth(&$pdf, 70, 12, $refnum), "Details" => makewidth(&$pdf, 200, 12, "{$stmnt_data['type']} {$stmnt_data['branch']}"), "Amount" => makewidth(&$pdf, 75, 12, "{$cust_data['currency']}{$stmnt_data['amount']}"), "Balance" => makewidth(&$pdf, 75, 12, "{$cust_data['currency']}" . sprint($balance) . ""));
}
}
if (isset($from_date) && isset($to_date)) {
# get overlapping amount
// $sql = "
// SELECT sum(amount) as amount FROM cubit.stmnt
// WHERE cusnum='$cusnum' AND date>'$to_date'";
// $balRslt = db_exec ($sql) or errDie ("Unable to retrieve invoices statement from database.");
// $bal = pg_fetch_array ($balRslt);
$get_bal = "SELECT sum(amount) FROM stmnt WHERE cusnum = '{$cust_data['cusnum']}'";
$run_bal = db_exec($get_bal) or errDie("Unable to get customer balance.");
if (pg_numrows($run_bal) < 1) {
$cust_data['balance'] = sprint(0);
} else {
$cust_data['balance'] = sprint(pg_fetch_result($run_bal, 0, 0));
}
$get_bal = "SELECT sum(amount) FROM stmnt WHERE cusnum = '{$cust_data['cusnum']}' AND date>'{$from_date}'";
$run_bal = db_exec($get_bal) or errDie("Unable to get customer balance.");
if (pg_numrows($run_bal) < 1) {
$bal['amount'] = sprint(0);
} else {
$bal['amount'] = sprint(pg_fetch_result($run_bal, 0, 0));
}
$cust_data['balance'] = $cust_data['balance'] - $bal['amount'];
}
/* alter the balance brought forward entry's (info[0]) amount */
if ($cust_data['location'] == 'int') {
$cust_data['balance'] = $cust_data['fbalance'];
}
$balbf = $cust_data['balance'] - $totout;
$balbf = sprint($balbf);
// $balbf = "test";
$info[0]["Date"] = date("d-m-Y", $oldest_date);
// - 24*60*60);
$info[0]["Amount"] = "{$cust_data['currency']}{$balbf}";
$custinfo = array(array("{$cust_data['surname']}"));
// Add the address to the array
$custaddr_ar = explode("\n", $cust_data["paddr1"]);
foreach ($custaddr_ar as $addr) {
$custinfo[] = array(pdf_lstr("{$addr}", 70));
}
$custinfo[] = array("");
$custinfo[] = array("<b>Account Number:</b> {$cust_data['accno']}");
//$custinfo[] = array("<b>Balance Brought Forward: </b>$cust_data[currency]$balbf");
// Comments --------------------------------------------------------------
if (isset($comment)) {
db_conn("cubit");
$sql = "SELECT comment FROM saved_statement_comments WHERE id='{$comment}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve comments from Cubit.");
$default_comment = base64_decode(pg_fetch_result($rslt, 0));
} elseif (isset($b64_comments)) {
$default_comment = base64_decode($b64_comments);
} else {
db_conn("cubit");
$sql = "SELECT value FROM settings WHERE constant='DEFAULT_STMNT_COMMENTS'";
$cmntRslt = db_exec($sql) or errDie("Unable to retrieve comments from Cubit.");
$default_comment = base64_decode(pg_fetch_result($cmntRslt, 0));
}
$comments = array();
$default_comment = wordwrap($default_comment, 55, "\n");
$default_comment_ar = explode("\n", $default_comment);
$i = 1;
foreach ($default_comment_ar as $val) {
if ($i == 4) {
$comments[] = array(pdf_lstr($val, 55));
break;
} else {
$comments[] = array($val);
}
$i++;
}
#handle unset bank information
if ($cust_data['bankid'] == "0") {
$get_bid = "SELECT * FROM bankacct LIMIT 1";
$run_bid = db_exec($get_bid) or errDie("Unable to get default bank information.");
if (pg_numrows($run_bid) < 1) {
#no bank accounts in cubit ????
$bank_data = array();
$bank_data['bankname'] = "";
$bank_data['branchname'] = "";
$bank_data['branchcode'] = "";
$bank_data['accnum'] = "";
} else {
$cust_data['bankid'] = pg_fetch_result($run_bid, 0, 0);
$bank_data = qryBankAcct($cust_data['bankid']);
}
} else {
$bank_data = qryBankAcct($cust_data['bankid']);
}
$banking = array(array("{$bank_data['bankname']}"), array("<b>Branch: </b>{$bank_data['branchname']}"), array("<b>Branch Code: </b>{$bank_data['branchcode']}"), array("<b>Account Number: </b>{$bank_data['accnum']}"));
$get_bal = "SELECT sum(amount) FROM stmnt WHERE cusnum = '{$cust_data['cusnum']}'";
$run_bal = db_exec($get_bal) or errDie("Unable to get customer balance.");
if (pg_numrows($run_bal) < 1) {
$cust_data['balance'] = sprint(0);
} else {
$cust_data['balance'] = sprint(pg_fetch_result($run_bal, 0, 0));
}
// Totals ----------------------------------------------------------------
$totals = array(array(""), array("<b>Total Outstanding Balance</b> : {$cust_data['currency']} " . sprint($cust_data["balance"])));
// Age analysis ----------------------------------------------------------
if ($cust_data['location'] == 'int') {
$cust_data['balance'] = $cust_data['fbalance'];
}
$balbf = $cust_data['balance'] - $totout;
$balbf = sprint($balbf);
$cust_data['balance'] = sprint($cust_data['balance']);
// $from_date = date ("Y-m-d",mktime (0,0,0,date("m"),"01",date("Y")));
$from_date = "{$from_year}-{$from_month}-{$from_day}";
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$curr = ageage($cust_data['cusnum'], 0, $cust_data['fcid'], $cust_data['location']);
$age30 = ageage($cust_data['cusnum'], 1, $cust_data['fcid'], $cust_data['location']);
$age60 = ageage($cust_data['cusnum'], 2, $cust_data['fcid'], $cust_data['location']);
$age90 = ageage($cust_data['cusnum'], 3, $cust_data['fcid'], $cust_data['location']);
$age120 = ageage($cust_data['cusnum'], 4, $cust_data['fcid'], $cust_data['location']);
} else {
$curr = cust_age($cust_data['cusnum'], 29, $cust_data['fcid'], $cust_data['location'], $to_month, $to_date, $from_date);
$age30 = cust_age($cust_data['cusnum'], 59, $cust_data['fcid'], $cust_data['location'], $to_month, $to_date, $from_date);
$age60 = cust_age($cust_data['cusnum'], 89, $cust_data['fcid'], $cust_data['location'], $to_month, $to_date, $from_date);
$age90 = cust_age($cust_data['cusnum'], 119, $cust_data['fcid'], $cust_data['location'], $to_month, $to_date, $from_date);
$age120 = cust_age($cust_data['cusnum'], 149, $cust_data['fcid'], $cust_data['location'], $to_month, $to_date, $from_date);
}
$custtot = $curr + $age30 + $age60 + $age90 + $age120;
// if(sprint($custtot) != sprint($cust_data['balance'])) {
// $curr = sprint($curr + $cust_data['balance'] - $custtot);
// $custtot = sprint($cust_data['balance']);
// }
$age = array(array("Current" => "<b>Current</b>", "30 Days" => "<b>30 Days:</b>", "60 Days" => "<b>60 Days</b>", "90 Days" => "<b>90 Days</b>", "120 Days" => "<b>120 Days</b>"), array("Current" => $curr, "30 Days" => $age30, "60 Days" => $age60, "90 Days" => $age90, "120 Days" => $age120));
// Table layout ----------------------------------------------------------
$ic = 0;
while (++$ic * 25 < count($info)) {
}
// Draw the pages, determine by the amount of items how many pages
// if items > 25 start a new page
$info_print = array();
for ($i = 0; $i < $ic; $i++) {
if ($i) {
$pdf->ezNewPage();
}
if (isset($from_date) && isset($to_date)) {
$date = "{$from_date} to {$to_date}";
$dalign_x = 130;
} else {
$date = date("d-m-Y");
$dalign_x = 105;
}
// Heading
$heading_pos = drawTable(&$pdf, $heading, 0, 0, 520, 5);
drawText(&$pdf, "<b>Page " . ($i + 1) . "</b>", 8, $heading_pos['x'] / 2 - 8, 10);
drawText(&$pdf, "<b>{$compinf['compname']}</b>", 18, 8, $heading_pos['y'] / 2 + 6);
drawText(&$pdf, "<b>Statement</b>", 18, $heading_pos['x'] - 120, $heading_pos['y'] / 2);
drawText(&$pdf, $date, 10, $heading_pos['x'] - $dalign_x, $heading_pos['y'] / 2 + 18);
$custinfo_pos = drawTable(&$pdf, $custinfo, 0, $heading_pos['y'] + 5, 300, 10);
$compinfo_pos = drawTable(&$pdf, $compinfo, $custinfo_pos['x'], $heading_pos['y'] + 5, 220, 10);
$info_start = $i * 25;
if ($i) {
$info_start++;
}
if ($info_start >= count($info) - 25) {
$info_end = count($info) - 1;
} else {
$info_end = ($i + 1) * 25;
}
$info_print = array();
for ($j = $info_start; $j <= $info_end; $j++) {
$info_print[$j] = $info[$j];
}
// Adjust the column widths
$cols = array("Date" => array("width" => 60), "Proforma Inv no" => array("width" => 70), "Ref no" => array("width" => 80), "Amount" => array("width" => 75, "justification" => "right"), "Balance" => array("width" => 75, "justification" => "right"));
$info_pos = drawTable(&$pdf, $info_print, 0, $custinfo_pos['y'] + 5, 520, 25, $cols, 1);
$comments_pos = drawTable(&$pdf, $comments, 0, $info_pos['y'] + 5, 260, 4);
$banking_pos = drawTable(&$pdf, $banking, $comments_pos['x'], $info_pos['y'] + 5, 260, 4);
$totals_pos = drawTable(&$pdf, $totals, 0, $comments_pos['y'] + 5, 520, 3);
$age_pos = drawTable(&$pdf, $age, 0, $totals_pos['y'], 520, 2);
drawText(&$pdf, "<b>Cubit Accounting</b>", 6, 0, $age_pos['y'] + 20);
}
$pdf->ezNewPage();
}
$pdf->ezStream();
}
function printStmnt($_GET)
{
# get vars
foreach ($_GET as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cusnum, "num", 1, 20, "Invalid Customer number.");
# display errors, if any
$err = "";
if ($v->isError()) {
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class=err>{$e['msg']}</li>";
}
return $err;
}
# Get selected customer info
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to view customer");
if (pg_numrows($custRslt) < 1) {
return "<li class=err>Invalid Customer Number.</li>";
}
$cust = pg_fetch_array($custRslt);
# connect to database
db_connect();
$fdate = date("Y") . "-" . date("m") . "-" . "01";
$stmnt = "";
$totout = 0;
if (!open()) {
# Query server
$sql = "SELECT * FROM stmnt WHERE cusnum = '{$cusnum}' AND date >= '{$fdate}' AND div = '" . USER_DIV . "' ORDER BY branch,date ASC";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
} else {
while ($st = pg_fetch_array($stRslt)) {
$totout += $st['amount'];
}
}
} else {
# Query server
$sql = "SELECT * FROM open_stmnt WHERE cusnum = '{$cusnum}' AND balance != '0' AND div = '" . USER_DIV . "' ORDER BY date ASC";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
} else {
while ($st = pg_fetch_array($stRslt)) {
$totout += $st['balance'];
}
}
}
$balbf = $cust['balance'] - $totout;
$balbf = sprint($balbf);
$rbal = $balbf;
// Check if it is an open item statement
if (!open()) {
# Query server
$sql = "SELECT * FROM stmnt WHERE cusnum = '{$cusnum}' AND date >= '{$fdate}' AND div = '" . USER_DIV . "' ORDER BY branch,date ASC";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
$stmnt .= "<tr><td colspan=4>No invoices for this month.</td></tr>";
} else {
while ($st = pg_fetch_array($stRslt)) {
# Format date
$st['date'] = explode("-", $st['date']);
$st['date'] = $st['date'][2] . "-" . $st['date'][1] . "-" . $st['date'][0];
$st['amount'] = sprint($st['amount']);
if (substr($st['type'], 0, 7) == "Invoice") {
$ex = "INV";
} elseif (substr($st['type'], 0, 17) == "Non-Stock Invoice") {
$ex = "INV";
} elseif (substr($st['type'], 0, 21) == "Non Stock Credit Note") {
$ex = "CR";
} elseif (substr($st['type'], 0, 11) == "Credit Note") {
$ex = "CR";
} else {
$ex = "";
}
$rbal = sprint($rbal + $st['amount']);
$stmnt .= "<tr><td align=center>{$st['date']}</td><td align=center>{$ex} {$st['invid']}</td><td align=center>{$st['docref']}</td><td>{$st['type']} {$st['branch']}</td><td align=right>{$cust['currency']} {$st['amount']}</td><td align=right>{$cust['currency']} {$rbal}</td></tr>";
}
}
} else {
# Query server
$sql = "SELECT * FROM open_stmnt WHERE cusnum = '{$cusnum}' AND balance != '0' AND div = '" . USER_DIV . "' ORDER BY date ASC";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
$stmnt .= "<tr><td colspan=4>No invoices for this month.</td></tr>";
} else {
while ($st = pg_fetch_array($stRslt)) {
# Format date
$st['date'] = explode("-", $st['date']);
$st['date'] = $st['date'][2] . "-" . $st['date'][1] . "-" . $st['date'][0];
$st['balance'] = sprint($st['balance']);
if (substr($st['type'], 0, 7) == "Invoice") {
$ex = "INV";
} elseif (substr($st['type'], 0, 17) == "Non-Stock Invoice") {
$ex = "INV";
} elseif (substr($st['type'], 0, 21) == "Non Stock Credit Note") {
$ex = "CR";
} elseif (substr($st['type'], 0, 11) == "Credit Note") {
$ex = "CR";
} else {
$ex = "";
}
$rbal = sprint($rbal + $st['balance']);
$stmnt .= "<tr><td align=center>{$st['date']}</td><td align=center>{$ex} {$st['invid']}</td><td align=center>{$st['docref']}</td><td>{$st['type']} {$st['branch']}</td><td align=right>{$cust['currency']} {$st['balance']}</td><td align=right>{$cust['currency']} {$rbal}</td></tr>";
}
}
}
if ($cust['location'] == 'int') {
$cust['balance'] = $cust['fbalance'];
}
$balbf = $cust['balance'] - $totout;
$balbf = sprint($balbf);
$cust['balance'] = sprint($cust['balance']);
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$curr = ageage($cust['cusnum'], 0, $cust['fcid'], $cust['location']);
$age30 = ageage($cust['cusnum'], 1, $cust['fcid'], $cust['location']);
$age60 = ageage($cust['cusnum'], 2, $cust['fcid'], $cust['location']);
$age90 = ageage($cust['cusnum'], 3, $cust['fcid'], $cust['location']);
$age120 = ageage($cust['cusnum'], 4, $cust['fcid'], $cust['location']);
} else {
$curr = age($cust['cusnum'], 29, $cust['fcid'], $cust['location']);
$age30 = age($cust['cusnum'], 59, $cust['fcid'], $cust['location']);
$age60 = age($cust['cusnum'], 89, $cust['fcid'], $cust['location']);
$age90 = age($cust['cusnum'], 119, $cust['fcid'], $cust['location']);
$age120 = age($cust['cusnum'], 149, $cust['fcid'], $cust['location']);
}
$custtot = $curr + $age30 + $age60 + $age90 + $age120;
if (sprint($custtot) != sprint($cust['balance'])) {
$curr = sprint($curr + $cust['balance'] - $custtot);
$custtot = sprint($cust['balance']);
}
$age = "<table cellpadding='3' cellspacing='1' border=0 width=100% bordercolor='#000000'>\r\n\t\t<tr><th>Current</th><th>30 days</th><th>60 days</th><th>90 days</th><th>120 days +</th></tr>\r\n\t\t<tr><td align=right>{$cust['currency']} {$curr}</td><td align=right>{$cust['currency']} {$age30}</td><td align=right>{$cust['currency']} {$age60}</td><td align=right>{$cust['currency']} {$age90}</td><td align=right>{$cust['currency']} {$age120}</td></tr>\r\n\t\t</table>";
db_conn("cubit");
if (!isset($print)) {
// Retrieve the template settings
db_conn("cubit");
$sql = "SELECT filename FROM template_settings WHERE template='statements'";
$tsRslt = db_exec($sql) or errDie("Unable to retrieve template settings from Cubit.");
$template = pg_fetch_result($tsRslt, 0);
#get the default comment
$get_com = "SELECT * FROM settings WHERE constant = 'DEFAULT_STMNT_COMMENTS' LIMIT 1";
$run_com = db_exec($get_com) or errDie("Could not get default comments");
if (pg_numrows($run_com) < 1) {
$default_stmnt_comments = "";
} else {
$arr = pg_fetch_array($run_com);
$default_stmnt_comments = base64_decode($arr['value']);
}
$show_comment = "<textarea name='default_stmnt_comments' cols='40' rows='4'>{$default_stmnt_comments}</textarea>";
$buttonz = "<input type=button value='[X] Close' onClick='javascript:window.close();'> | <input type=button value='View PDF' onClick=\"javascript:document.location.href='{$template}?cusnum={$cusnum}&sort=branch'\"> | <input type=button value='View By Date Range' onClick=\"javascript:document.location.href='cust-stmnt-date.php?cusnum={$cusnum}'\"> | <input type=button value='Sort By Customer Branches' onClick=\"javascript:document.location.href='cust-stmnt-branch.php?cusnum={$cusnum}'\"> | <input type=submit value='Print'>";
} else {
$show_comment = nl2br($default_stmnt_comments);
$buttonz = "";
}
/* db_conn("cubit");
$sql = "SELECT value FROM settings WHERE constant='DEFAULT_COMMENTS'";
$rslt = db_exec($sql) or errDie("Unable to retrieve default comments.");
$DEFAULT_COMMENTS = base64_decode(pg_fetch_result($rslt, 0));
*/
// Layout
$printStmnt = "<center><h2>Monthly Statement</h2></center>\r\n\t<form action='" . SELF . "' method=GET>\r\n\t<input type=hidden name=cusnum value='{$cusnum}'>\r\n\t<input type=hidden name=print value='yes'>\r\n\t<table cellpadding='3' cellspacing='0' border=0 width=750 bordercolor='#000000'>\r\n\t\t<tr></td><td valign=top width=70%>\r\n\t\t\t<font size=5><b>" . COMP_NAME . "</b></font><br>\r\n\t\t\t" . COMP_ADDRESS . "<br>\r\n\t\t\t" . COMP_PADDR . "\r\n\t\t</td><td>\r\n\t\t\tCOMPANY REG. " . COMP_REGNO . "<br>\r\n\t\t\tTEL : " . COMP_TEL . "<br>\r\n\t\t\tFAX : " . COMP_FAX . "<br>\r\n\t\t\tVAT REG." . COMP_VATNO . "<br>\r\n\t\t</td></tr>\r\n\t</table>\r\n\t<p>\r\n\t<table cellpadding='3' cellspacing='0' border=1 width=400 bordercolor='#000000'>\r\n\t\t<tr><th width=60%><b>Account No.</b></th><td width=40%>{$cust['accno']}</th></tr>\r\n\t\t<tr><td colspan=2>\r\n\t\t\t<font size=4><b>{$cust['cusname']} {$cust['surname']}</b></font><br>\r\n\t\t\t" . nl2br($cust['addr1']) . "<br>\r\n\t\t</td></tr>\r\n\t\t<tr><td><b>Balance Brought Forward</b></td><td>{$cust['currency']} {$balbf}</td>\r\n\t</table>\r\n\t<p>\r\n\t<table cellpadding='3' cellspacing='0' border=0 width=750 bordercolor='#000000'>\r\n\t <tr>\r\n\t <td>{$show_comment}</td>\r\n\t </tr>\r\n\t</table>\r\n\t<p>\r\n\t<table cellpadding='3' cellspacing='0' border=0 width=750 bordercolor='#000000'>\r\n\t\t<tr><th>Date</th><th>Ref No.</th><th>Proforma Inv No.</th><th>Details</th><th>Amount</th><th>Balance</th></tr>\r\n\t\t{$stmnt}\r\n\t\t<tr><td><br></td></tr>\r\n\t\t<tr><td colspan=4 align=right>\r\n\t\t\t<table cellpadding='3' cellspacing='0' border=1 width=300 bordercolor='#000000'>\r\n\t\t\t\t<tr><th><b>Total Outstanding</b></th><td colspan=2>{$cust['currency']} {$cust['balance']}</td></tr>\r\n\t\t\t</table>\r\n\t\t</td></tr>\r\n\t\t<tr><td><br></td></tr>\r\n\t\t<tr><td><br></td></tr>\r\n\t\t<tr><td colspan=4>{$age}</td></tr>\r\n\t\t<tr><td><br></td></tr>\r\n\t</table>\r\n\t<p>\r\n\t{$buttonz}\r\n\t</form>";
// // Retrieve template settings from Cubit
// db_conn("cubit");
// $sql = "SELECT filename FROM template_settings WHERE template='statements'";
// $tsRslt = db_exec($sql) or errDie("Unable to retrieve the template settings from Cubit.");
// $template = pg_fetch_result($tsRslt, 0);
//
// if ($template == "stmnt-print.php") {
// $OUTPUT = $printStmnt;
// require("tmpl-print.php");
// } else {
// header ("Location: $template?cusnum=$cust[cusnum]");
// }
$OUTPUT = $printStmnt;
require "tmpl-print.php";
}
function printCust($_GET)
{
# get vars
foreach ($_GET as $key => $value) {
${$key} = $value;
}
if (isset($filter) && !isset($all)) {
$sqlfilter = " AND lower({$filter}) LIKE lower('%{$fval}%')";
$show = true;
} else {
$filter = "";
$fval = "";
$sqlfilter = "";
$show = false;
}
if (isset($all)) {
$show = true;
}
$filterarr = array("surname" => "Company/Name", "init" => "Initials", "accno" => "Account Number", "deptname" => "Department");
$filtersel = extlib_cpsel("filter", $filterarr, $filter);
# Set up table to display in
$printCust = "\r\n\t<h3>Find Customer</h3>\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\r\n\t<form action='" . SELF . "' method=get>\r\n\t<tr><th>.: Filter :.</th><th>.: Value :.</th></tr>\r\n\t<tr class='bg-odd'><td>{$filtersel}</td><td><input type=text size=20 name=fval value='{$fval}'></td></tr>\r\n\t<tr class='bg-even'><td align=center><input type=submit name=all value='View All'></td><td align=center><input type=submit value='Apply Filter'></td></tr>\r\n\t</form>\r\n\t</table>\r\n\t<p>";
if ($show) {
$printCust .= "<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\r\n\t\t<tr><th>Acc no.</th><th>Company/Name</th><th>Type</th><th>Curr</th><th>Tel</th><th>Category</th><th>Class</th><th colspan=2>Balance</th><th>Overdue</th><th colspan=8>Options</th></tr>";
# connect to database
db_connect();
# Query server
$tot = 0;
$totoverd = 0;
$i = 0;
$sql = "SELECT * FROM customers WHERE (div = '" . USER_DIV . "' OR ddiv = '" . USER_DIV . "') {$sqlfilter} ORDER BY surname ASC";
$custRslt = db_exec($sql) or errDie("Unable to retrieve Customers from database.");
if (pg_numrows($custRslt) < 1) {
$printCust .= "<tr class='bg-odd'><td colspan=20><li>There are no Customers in Cubit.</td></tr>";
} else {
while ($cust = pg_fetch_array($custRslt)) {
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$overd = ageage($cust['cusnum'], $cust['overdue'] / 30 - 1, $cust['location']);
} else {
$overd = age($cust['cusnum'], $cust['overdue'] - 1, $cust['location']);
}
$totoverd += $overd;
# Check if record can be removed
db_connect();
$sql = "SELECT * FROM cashbook WHERE banked = 'no' AND cusnum = '{$cust['cusnum']}' AND div = '" . USER_DIV . "'";
$rs = db_exec($sql) or errDie("Unable to get cashbook entries.", SELF);
if (pg_numrows($rs) < 1 && $cust['balance'] == 0) {
$rm = "<td><a href='cust-rem.php?cusnum={$cust['cusnum']}'>Remove</a></td>";
} else {
$rm = "<td></td>";
}
if (strlen(trim($cust['bustel'])) < 1) {
$cust['bustel'] = $cust['tel'];
}
$cust['balance'] = sprint($cust['balance']);
$tot = $tot + $cust['balance'];
$inv = "";
$inv = "<td><a href='pdf/invoice-pdf-cust.php?cusnum={$cust['cusnum']}' target=_blank>Print Invoices</a></td>";
# Locations drop down
$locs = array("loc" => "Local", "int" => "International", "" => "");
$loc = $locs[$cust['location']];
$sp4 = " ";
$fbal = "{$sp4}--{$sp4}";
$ocurr = CUR;
$trans = "<td><a href='core/cust-trans.php?cusnum={$cust['cusnum']}'>Transaction</a></td>";
if ($cust['location'] == 'int') {
$fbal = "{$sp4} {$cust['currency']} {$cust['fbalance']}";
$ocurr = $cust['currency'];
$trans = "<td><a href='core/intcust-trans.php?cusnum={$cust['cusnum']}'>Transaction</a></td>";
}
# alternate bgcolor
$printCust .= "<tr class='" . bg_class() . "'><td>{$cust['accno']}</td><td>{$cust['surname']}</td><td align=center>{$loc}</td><td align=center>{$cust['currency']}</td><td>{$cust['bustel']}</td><td>{$cust['catname']}</td><td>{$cust['classname']}</td><td align=right>" . CUR . " {$cust['balance']}</td><td align=right>{$fbal}</td><td align=right>{$ocurr} {$overd}</td><td><a href='cust-det.php?cusnum={$cust['cusnum']}'>Details</a></td>";
$printCust .= "<td><a href='cust-edit.php?cusnum={$cust['cusnum']}'>Edit</a></td><td><a href='#' onclick='openPrintWin(\"cust-stmnt.php?cusnum={$cust['cusnum']}\")'>Statement</a></td>{$trans} {$inv}";
if ($cust['blocked'] == 'yes') {
$printCust .= "<td><a href='cust-unblock.php?cusnum={$cust['cusnum']}'>Unblock</a></td>";
} else {
$printCust .= "<td><a href='cust-block.php?cusnum={$cust['cusnum']}'>Block</a></td>";
}
$printCust .= "{$rm} <td><a href='conper-add.php?type=cust&id={$cust['cusnum']}'>Add Contact</a></td></tr>";
$i++;
}
if ($i > 1) {
$s = "s";
} else {
$s = "";
}
$tot = sprint($tot);
$totoverd = sprint($totoverd);
$printCust .= "<tr class='" . bg_class() . "'><td colspan=7>Total Amount Outstanding, from {$i} client{$s} </td><td align=right>" . CUR . " {$tot}</td><td></td><td align=right>" . CUR . " {$totoverd}</td></tr>";
}
$printCust .= "</table>";
}
$printCust .= "\r\n <p>\r\n\t<table border=0 cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "' width=15%>\r\n <tr><td><br></td></tr>\r\n <tr><th>Quick Links</th></tr>\r\n\t\t<tr class='bg-odd'><td><a href='customers-new.php'>Add Customer</a></td></tr>\r\n\t\t<script>document.write(getQuicklinkSpecial());</script>\r\n\t\t<tr class='bg-odd'><td><a href='main.php'>Main Menu</a></td></tr>\r\n\t</table>";
return $printCust;
}
function printStmnt($_POST)
{
# get vars
foreach ($_POST as $key => $value) {
${$key} = $value;
}
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cusnum, "num", 1, 20, "Invalid Customer number.");
$v->isOk($fday, "num", 1, 2, "Invalid from Date day.");
$v->isOk($fmon, "num", 1, 2, "Invalid from Date month.");
$v->isOk($fyear, "num", 1, 4, "Invalid from Date Year.");
$v->isOk($today, "num", 1, 2, "Invalid to Date day.");
$v->isOk($tomon, "num", 1, 2, "Invalid to Date month.");
$v->isOk($toyear, "num", 1, 4, "Invalid to Date Year.");
# mix dates
$fromdate = $fyear . "-" . $fmon . "-" . $fday;
$todate = $toyear . "-" . $tomon . "-" . $today;
if (!checkdate($fmon, $fday, $fyear)) {
$v->isOk($fromdate, "num", 1, 1, "Invalid from date.");
}
if (!checkdate($tomon, $today, $toyear)) {
$v->isOk($todate, "num", 1, 1, "Invalid to date.");
}
# display errors, if any
$err = "";
if ($v->isError()) {
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class=err>" . $e["msg"];
}
return $err;
}
# Get selected customer info
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to view customer");
if (pg_numrows($custRslt) < 1) {
return "<li class=err>Invalid Customer Number.";
}
$cust = pg_fetch_array($custRslt);
if ($cust['location'] == 'int') {
$cust['balance'] = $cust['fbalance'];
}
# connect to database
db_connect();
$fdate = date("Y") . "-" . date("m") . "-" . "01";
$stmnt = "";
$totout = 0;
# Query server
$sql = "SELECT * FROM stmnt WHERE cusnum = '{$cusnum}' AND date >= '{$fromdate}' AND date <= '{$todate}' AND div = '" . USER_DIV . "' ORDER BY date ASC";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
$stmnt .= "<tr><td colspan=4>No invoices for this month.</td></tr>";
} else {
while ($st = pg_fetch_array($stRslt)) {
# format date
$st['date'] = explode("-", $st['date']);
$st['date'] = $st['date'][2] . "-" . $st['date'][1] . "-" . $st['date'][0];
$st['amount'] = sprint($st['amount']);
if (substr($st['type'], 0, 7) == "Invoice") {
$ex = "INV";
} elseif (substr($st['type'], 0, 17) == "Non-Stock Invoice") {
$ex = "INV";
} elseif (substr($st['type'], 0, 21) == "Non Stock Credit Note") {
$ex = "CR";
} elseif (substr($st['type'], 0, 11) == "Credit Note") {
$ex = "CR";
} else {
$ex = "";
}
$stmnt .= "\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td align='center'>{$st['date']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$ex} {$st['invid']}</td>\n\t\t\t\t\t\t\t<td align='center'>{$st['docref']}</td>\n\t\t\t\t\t\t\t<td>{$st['type']} {$st['branch']}</td>\n\t\t\t\t\t\t\t<td align='right'>{$cust['currency']} {$st['amount']}</td>\n\t\t\t\t\t\t</tr>";
# keep track of da totals
$totout += $st['amount'];
}
}
db_connect();
# get overlapping amount
$sql = "SELECT sum(amount) as amount FROM stmnt WHERE cusnum = '{$cusnum}' AND date > '{$todate}' AND div = '" . USER_DIV . "' ";
$balRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
$bal = pg_fetch_array($balRslt);
$cust['balance'] = $cust['balance'] - $bal['amount'];
$balbf = $cust['balance'] - $totout;
$balbf = sprint($balbf);
$cust['balance'] = sprint($cust['balance']);
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$curr = ageage($cust['cusnum'], 0);
$age30 = ageage($cust['cusnum'], 1);
$age60 = ageage($cust['cusnum'], 2);
$age90 = ageage($cust['cusnum'], 3);
$age120 = ageage($cust['cusnum'], 4);
} else {
$curr = age($cust['cusnum'], 29);
$age30 = age($cust['cusnum'], 59);
$age60 = age($cust['cusnum'], 89);
$age90 = age($cust['cusnum'], 119);
$age120 = age($cust['cusnum'], 149);
}
$Sl = "SELECT * FROM ages WHERE cust='{$cust['cusnum']}'";
$Ri = db_exec($Sl);
if (pg_num_rows($Ri) > 0) {
$ad = pg_fetch_array($Ri);
$age = "<table cellpadding='3' cellspacing='1' border=0 width=100% bordercolor='#000000'>\n\t\t<tr><th>Current</th><th>30 days</th><th>60 days</th><th>90 days</th><th>120 days +</th></tr>\n\t\t<tr><td align=right>{$cust['currency']} {$ad['curr']}</td><td align=right>{$cust['currency']} {$ad['age30']}</td>\n\t\t<td align=right>{$cust['currency']} {$ad['age60']}</td><td align=right>{$cust['currency']} {$ad['age90']}</td>\n\t\t<td align=right>{$cust['currency']} {$ad['age120']}</td></tr>\n\t\t</table>";
} else {
$age = "";
}
// Retrieve template settings from Cubit
db_conn("cubit");
$sql = "SELECT filename FROM template_settings WHERE template='statements' AND div='" . USER_DIV . "'";
$tsRslt = db_exec($sql) or errDie("Unable to retrieve template settings from Cubit.");
$template = pg_fetch_result($tsRslt, 0);
if ($template == "pdf/cust-pdf-stmnt.php") {
$template = "pdf/cust-pdf-stmnt-date.php";
}
if (!isset($print)) {
$buttonz = "<form action='{$template}' method=post name=form>\n <input type=hidden name=cusnum value='{$cusnum}'>\n <input type=hidden name=fday value='{$fday}'>\n <input type=hidden name=fmon value='{$fmon}'>\n <input type=hidden name=fyear value='{$fyear}'>\n <input type=hidden name=today value='{$today}'>\n <input type=hidden name=tomon value='{$tomon}'>\n <input type=hidden name=toyear value='{$toyear}'>\n <input type=button value='[X] Close' onClick='javascript:window.close();'> | <input type=submit value='View PDF'> | <input type=button value='View By Date Range' onClick=\"javascript:document.location.href='cust-stmnt-date.php?cusnum={$cusnum}'\">\n\t\t</form>\n\t\t<form action='cust-stmnt-date.php' method=post name=form>\n <input type=hidden name=key value=view>\n <input type=hidden name=print value=''>\n <input type=hidden name=cusnum value='{$cusnum}'>\n <input type=hidden name=fday value='{$fday}'>\n <input type=hidden name=fmon value='{$fmon}'>\n <input type=hidden name=fyear value='{$fyear}'>\n <input type=hidden name=today value='{$today}'>\n <input type=hidden name=tomon value='{$tomon}'>\n <input type=hidden name=toyear value='{$toyear}'>\n <input type=submit value='Print'></form>";
#get the default comment, from the saved table first ...
$get_saved = "SELECT * FROM saved_statement_comments WHERE cusnum = '{$cusnum}' ORDER BY id DESC LIMIT 1";
$run_saved = db_exec($get_saved) or errDie("Unable to get saved statement comment");
if (pg_numrows($run_saved) < 1) {
#no comment has been saved for this customer ... so check if there's a default now ...
$get_com = "SELECT * FROM settings WHERE constant = 'DEFAULT_STMNT_COMMENTS' LIMIT 1";
$run_com = db_exec($get_com) or errDie("Could not get default comments");
if (pg_numrows($run_com) < 1) {
#there is absolutely no comment to display ...
$default_stmnt_comments = "";
} else {
#found default comment ... use that
$arr = pg_fetch_array($run_com);
$default_stmnt_comments = base64_decode($arr['value']);
}
} else {
#found a saved comment ... use it
$sarr = pg_fetch_array($run_saved);
$default_stmnt_comments = base64_decode($sarr['comment']);
}
$show_comment = "<textarea name='default_stmnt_comments' cols='40' rows='4'>{$default_stmnt_comments}</textarea>";
} else {
$show_comment = nl2br($default_stmnt_comments);
$buttonz = "";
}
// Layout
$printStmnt = "<center><h2>Customer Statement</h2>\n\t<h3>{$fday}-{$fmon}-{$fyear} -- {$today}-{$tomon}-{$toyear}</h3></center>\n\t<table cellpadding='3' cellspacing='0' border=0 width=750 bordercolor='#000000'>\n\t\t<tr></td><td valign=top width=70%>\n\t\t\t<font size=5><b>" . COMP_NAME . "</b></font><br>\n\t\t\t" . COMP_ADDRESS . "<br>\n\t\t\t" . COMP_PADDR . "\n\t\t</td><td>\n\t\t\tCOMPANY REG. " . COMP_REGNO . "<br>\n\t\t\tTEL : " . COMP_TEL . "<br>\n\t\t\tFAX : " . COMP_FAX . "<br>\n\t\t\tVAT REG." . COMP_VATNO . "<br>\n\t\t</td></tr>\n\t</table>\n\t<p>\n\t<table cellpadding='3' cellspacing='0' border=1 width=400 bordercolor='#000000'>\n\t\t<tr><th width=60%><b>Account No.</b></th><td width=40%>{$cust['accno']}</th></tr>\n\t\t<tr><td colspan=2>\n\t\t\t<font size=4><b>{$cust['cusname']} {$cust['surname']}</b></font><br>\n\t\t\t" . nl2br($cust['addr1']) . "<br>\n\t\t</td></tr>\n\t\t<tr><td><b>Balance Brought Forward</b></td><td>{$cust['currency']} {$balbf}</td>\n\t</table>\n\t\t<p>\n\t<table cellpadding='3' cellspacing='0' border=0 width=750 bordercolor='#000000'>\n\t <tr>\n\t <td>{$show_comment}</td>\n\t </tr>\n\t</table>\n\t</p>\n\t<p>\n\t<table cellpadding='3' cellspacing='0' border=0 width=750 bordercolor='#000000'>\n\t\t<tr><th>Date</th><th>Ref No.</th><th>Proforma Inv No.</th><th>Details</th><th>Amount</th></tr>\n\t\t{$stmnt}\n\t\t<tr><td><br></td></tr>\n\t\t<tr><td colspan=4 align=right>\n\t\t\t<table cellpadding='3' cellspacing='0' border=1 width=300 bordercolor='#000000'>\n\t\t\t\t<tr><th><b>Total Outstanding</b></th><td colspan=2>{$cust['currency']} {$cust['balance']}</td></tr>\n\t\t\t</table>\n\t\t</td></tr>\n\t\t<tr><td><br></td></tr>\n\t\t<tr><td><br></td></tr>\n\t\t<tr><td colspan=4>{$age}</td></tr>\n\t\t<tr><td><br></td></tr>\n\t</table>\n\t<p>\n\t{$buttonz}";
# return $printStmnt;
$OUTPUT = $printStmnt;
require "tmpl-print.php";
}
function get_recip()
{
global $_SESSION;
extract($_REQUEST);
if (!AJAX) {
$content = $_POST["emailsavepage_content"];
}
if (!isset($action)) {
$action = "listcust";
}
/* session var prefix */
$SPRE = "custview_";
/* max number of customers in list */
if (isset($viewall_cust)) {
define("ACT_SHOW_LIMIT", 2147483647);
$offset = 0;
} else {
define("ACT_SHOW_LIMIT", SHOW_LIMIT);
}
if (!isset($fval) && isset($_SESSION["{$SPRE}fval"])) {
$fval = $_SESSION["{$SPRE}fval"];
}
if (!isset($filter) && isset($_SESSION["{$SPRE}filter"])) {
$filter = $_SESSION["{$SPRE}filter"];
}
if (!isset($all) && isset($_SESSION["{$SPRE}all"]) && !isset($filter) && !isset($fval)) {
$all = $_SESSION["{$SPRE}all"];
}
if (isset($filter) && isset($fval) && !isset($all)) {
if ($filter == "all") {
$filter = "surname";
}
if (AJAX) {
$sqlfilter = " AND lower({$filter}) LIKE lower('%{$fval}%')";
} else {
$sqlfilter = " AND FALSE";
}
if (isset($_SESSION["{$SPRE}all"])) {
unset($_SESSION["{$SPRE}all"]);
}
$_SESSION["{$SPRE}fval"] = $fval;
$_SESSION["{$SPRE}filter"] = $filter;
} else {
if (isset($_SESSION["{$SPRE}fval"])) {
unset($_SESSION["{$SPRE}fval"]);
}
if (isset($_SESSION["{$SPRE}filter"])) {
unset($_SESSION["{$SPRE}filter"]);
}
$filter = "";
$fval = "";
$_SESSION["{$SPRE}all"] = "true";
$sqlfilter = "";
}
$filterarr = array("surname" => "Company/Name", "init" => "Initials", "accno" => "Account Number", "deptname" => "Department", "category" => "Category", "class" => "Classification");
$filtersel = extlib_cpsel("filter", $filterarr, $filter, "onChange='applyFilter();'");
if (!isset($custom_address)) {
$custom_address = "";
}
# Set up table to display in
if (!AJAX) {
$printCust_begin = "\n\t\t<h3>Please select who you wish to send this page to:</h3>\n\t\t<form method='POST' action='" . SELF . "'>\n\t\t\t<input type='hidden' name='emailsavepage_key' value='gather_emails'>\n\t\t\t<input type='hidden' name='emailsavepage_action' value=''>\n\t\t\t<input type='hidden' name='emailsavepage_name' value='{$emailsavepage_name}' />\n\t\t\t<input type='hidden' name='emailsavepage_content' value='{$content}'>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th colspan='10'>Customers</th>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<th>Customer Name</th>\n\t\t\t\t<th>Email Address</th>\n\t\t\t\t<th>Select</th>\n\t\t\t</tr>\n\t\t\t<input type='hidden' name='action' value='{$action}'>\n\t\t\t<tr>\n\t\t\t\t<th>.: Filter :.</th>\n\t\t\t\t<th>.: Value :.</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$filtersel}</td>\n\t\t\t\t<td><input type='text' size='20' id='fval' value='{$fval}' onKeyUp='applyFilter();'></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td align='center'><input type='button' name='all' value='View All' onClick='viewAll();'></td>\n\t\t\t\t<td align='center'><input type='button' value='Apply Filter' onClick='applyFilter();'></td>\n\t\t\t</tr>\n\t\t\t" . TBL_BR . "\n\t\t</table>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Send To This Address</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><input type='text' size='35' name='custom_address' value='{$custom_address}'></td>\n\t\t\t</tr>\n\t\t\t" . TBL_BR . "\n\t\t</table>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Send Email Member Of This Group</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><input type='radio' name='show' onClick='applyFilter();'> Send To Customer</td>\n\t\t\t</tr>\n\t\t</table>\n\t\t<script>\n\t\t\t/* CRM CODE */\n\t\t\tfunction updateAccountInfo(id, name) {\n\t\t\t\twindow.opener.document.frm_con.accountname.value=name;\n\t\t\t\twindow.opener.document.frm_con.account_id.value=id;\n\t\t\t\twindow.opener.document.frm_con.account_type.value='Customer';\n\t\t\t\twindow.close();\n\t\t\t}\n\n\t\t\t/* AJAX filter code */\n\t\t\tfunction viewAll() {\n\t\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'emailsavepage_key=content_supplied&all=t');\n\t\t\t}\n\n\t\t\tfunction applyFilter() {\n\t\t\t\tfilter = getObject('filter').value;\n\t\t\t\tfval = getObject('fval').value;\n\n\t\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'emailsavepage_key=content_supplied&filter=' + filter + '&fval=' + fval);\n\t\t\t}\n\n\t\t\tfunction updateOffset(noffset, viewall) {\n\t\t\t\tif (viewall && !noffset) {\n\t\t\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'viewall_cust=t');\n\t\t\t\t} else {\n\t\t\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'offset=' + noffset);\n\t\t\t\t}\n\t\t\t}\n\t\t</script>\n\t\t<p>\n\t\t<div id='cust_list'>";
}
if (!isset($offset) && isset($_SESSION["{$SPRE}offset"])) {
$offset = $_SESSION["{$SPRE}offset"];
} else {
if (!isset($offset)) {
$offset = 0;
}
}
$_SESSION["{$SPRE}offset"] = $offset;
# connect to database
db_connect();
# counting the number of possible entries
$sel = grp(m("where", "(div = '" . USER_DIV . "' OR ddiv = '" . USER_DIV . "') {$sqlfilter}"));
$customers = new dbSelect("customers", "cubit", $sel);
$customers->run();
$custcount = $customers->num_rows();
/* view offsets */
if ($offset > 0) {
$poffset = $offset >= ACT_SHOW_LIMIT ? $offset - ACT_SHOW_LIMIT : 0;
$os_prev = "<a class='nav' href='javascript: updateOffset(\"{$poffset}\");'>Previous</a>";
} else {
$os_prev = " ";
}
if ($offset + ACT_SHOW_LIMIT > $custcount) {
$os_next = " ";
} else {
$noffset = $offset + ACT_SHOW_LIMIT;
$os_next = "<a class='nav' href='javascript: updateOffset(\"{$noffset}\");'>Next</a>";
}
if ($os_next != " " || $os_prev != " ") {
$os_viewall = "| <a class='nav' href='javascript: updateOffset(false, true);'>View All</a>";
} else {
$os_viewall = "";
}
if (!isset($ajaxCust)) {
$ajaxCust = "";
}
if (!isset($pure)) {
$pure = "";
}
$ajaxCust .= "\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<td colspan='9' align='right'><input type='submit' value='Send Emails'></td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<td colspan='20'>\n\t\t\t\t\t<table width='100%' border='0'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td align='right' width='50%'>{$os_prev}</td>\n\t\t\t\t\t\t\t<td align='left' width='50%'>{$os_next} {$os_viewall}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t\t<tr>\n\t\t\t\t<th>Acc no.</th>\n\t\t\t\t<th>Company/Name</th>\n\t\t\t\t<th>Tel</th>\n\t\t\t\t<th>Category</th>\n\t\t\t\t<th>Class</th>\n\t\t\t\t<th colspan='2'>Balance</th>\n\t\t\t\t<th>Overdue</th>\n\t\t\t\t" . ($pure ? "" : "<th colspan='11'>Options</th>") . "\n\t\t\t</tr>";
# Query server
$tot = 0;
$totoverd = 0;
$i = 0;
$sel = grp(m("order", "surname ASC"), m("offset", $offset), m("limit", ACT_SHOW_LIMIT), m("where", "(div = '" . USER_DIV . "' OR ddiv = '" . USER_DIV . "') {$sqlfilter}"));
$customers = new dbSelect("customers", "cubit", $sel);
$customers->run();
if ($customers->num_rows() < 1) {
$ajaxCust .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='20'><li>There are no Customers matching the criteria entered.</li></td>\n\t\t</tr>";
} else {
while ($cust = $customers->fetch_array()) {
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$overd = ageage($cust['cusnum'], $cust['overdue'] / 30 - 1, $cust['location']);
} else {
$overd = age($cust['cusnum'], $cust['overdue'] - 1, $cust['location']);
}
if ($overd < 0) {
$overd = 0;
}
if ($overd > $cust['balance']) {
$overd = $cust['balance'];
}
if ($cust["location"] == "int") {
db_conn("cubit");
$sql = "SELECT rate FROM currency WHERE fcid='{$cust['fcid']}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve currency rate from Cubit.");
$rate = pg_fetch_result($rslt, 0);
if ($rate != 0) {
$totoverd += $overd * $rate;
} else {
$totoverd += $overd;
}
} else {
$totoverd += $overd;
}
if (strlen(trim($cust['bustel'])) < 1) {
$cust['bustel'] = $cust['tel'];
}
$cust['balance'] = sprint($cust['balance']);
if ($cust["location"] == "int") {
db_conn("cubit");
$sql = "SELECT rate FROM currency WHERE fcid='{$cust['fcid']}'";
$rslt = db_exec($sql) or errDie("Unable to retrieve currency rate from Cubit.");
$rate = pg_fetch_result($rslt, 0);
if ($rate != 0.0) {
$tot = $tot + $cust['balance'] * $rate;
} else {
$tot = $tot + $cust['balance'];
}
} else {
$tot = $tot + $cust['balance'];
}
$sql = "SELECT filename FROM template_settings WHERE template='invoices'";
$ts_rslt = db_exec($sql) or errDie("Unable to retrieve the template settings from Cubit.");
$template = pg_fetch_result($ts_rslt, 0);
if ($template != "pdf/pdf-tax-invoice.php") {
$template = "pdf/invoice-pdf-cust.php";
}
$inv = "";
$inv = "\n\t\t\t<td>\n\t\t\t\t<a href='{$template}?cusnum={$cust['cusnum']}&type=cusprintinvoices' target='_blank'>Print Invoices</a>\n\t\t\t</td>";
# Locations drop down
$locs = array("loc" => "Local", "int" => "International", "" => "");
$loc = $locs[$cust['location']];
$sp4 = " ";
$fbal = "{$sp4}--{$sp4}";
$ocurr = CUR;
$ajaxCust .= "<tr class='" . bg_class() . "'>";
if ($action == "contact_acc") {
$updatelink = "javascript: updateAccountInfo(\"{$cust['cusnum']}\", \"{$cust['accno']}\");";
$ajaxCust .= "\n\t\t\t\t\t<td><a href='{$updatelink}'>{$cust['accno']}</a></td>\n\t\t\t\t\t<td><a href='{$updatelink}'>{$cust['surname']}</a></td>";
} else {
$ajaxCust .= "\n\t\t\t\t\t<td>{$cust['accno']}</td>\n\t\t\t\t\t<td>{$cust['surname']}</td>";
}
$ajaxCust .= "\n\t\t\t\t\t<td>{$cust['bustel']}</td>\n\t\t\t\t\t<td>{$cust['catname']}</td>\n\t\t\t\t\t<td>{$cust['classname']}</td>\n\t\t\t\t\t<td align='right'>{$ocurr} {$cust['balance']}</td>\n\t\t\t\t\t<td align='right'>{$fbal}</td>\n\t\t\t\t\t<td align='right'>{$ocurr} {$overd}</td>";
if ($action == "listcust") {
$ajaxCust .= "\n\t\t\t\t\t<input type='hidden' name='surnames[{$cust['cusnum']}]' value='{$cust['surname']}'>\n\t\t\t\t\t<td><input type='checkbox' name='emailcust[{$cust['cusnum']}]' value='{$cust['email']}'></td>\n\t\t\t\t\t<td><a href='delnote-report.php?cusnum={$cust['cusnum']}' target='_blank'>Outstanding Stock</a></td>\n\t\t\t\t\t<td><a href='cust-det.php?cusnum={$cust['cusnum']}' target='_blank'>Details</a></td>\n\t\t\t\t\t<td><a href='cust-edit.php?cusnum={$cust['cusnum']}' target='_blank'>Edit</a></td>\n\t\t\t\t\t<td><a href='#' onClick='openPrintWin(\"cust-stmnt.php?cusnum={$cust['cusnum']}\");'>Statement</a></td>\n\t\t\t\t\t{$inv}";
if ($cust['blocked'] == 'yes') {
$ajaxCust .= "<td><a href='cust-unblock.php?cusnum={$cust['cusnum']}' target='_blank'>Unblock</a></td>";
} else {
$ajaxCust .= "<td><a href='cust-block.php?cusnum={$cust['cusnum']}' target='_blank'>Block</a></td>";
}
$ajaxCust .= "\n\t\t\t\t</tr>";
} else {
$ajaxCust .= "\n\t\t\t\t\t<td align='center'>\n\t\t\t\t\t\t<a href='javascript: popupSized(\"cust-det.php?cusnum={$cust['cusnum']}\", \"custdetails\", 550, 400, \"\");'>Details</a>\n\t\t\t\t\t</td>";
}
$i++;
}
if ($i > 1) {
$s = "s";
} else {
$s = "";
}
$tot = sprint($tot);
$totoverd = sprint($totoverd);
$ajaxCust .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='5'>Total Amount Outstanding, from {$i} client{$s} </td>\n\t\t\t\t<td align='right' nowrap>" . CUR . " {$tot}</td>\n\t\t\t\t<td></td>\n\t\t\t\t<td align='right' nowrap>" . CUR . " {$totoverd}</td>\n\t\t\t</tr>";
$ajaxCust .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='20'>\n\t\t\t\t\t<table width='100%' border='0'>\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<td align='right' width='50%'>{$os_prev}</td>\n\t\t\t\t\t\t\t<td align='left' width='50%'>{$os_next} {$os_viewall}</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>\n\t\t\t\t</td>\n\t\t\t</tr>";
}
$ajaxCust .= "\n\t\t\t" . TBL_BR . "\n\t\t\t<tr>\n\t\t\t\t<td colspan='9' align='right'><input type='submit' value='Send Emails'></td>\n\t\t\t</tr>\n\t\t</table>\n\t\t</form>";
if (!AJAX) {
$printCust_end = "\n\t\t</div>\n\t <p>\n\t\t<table " . TMPL_tblDflts . " width='15%'>\n\t " . TBL_BR . "\n\t <tr>\n\t \t<th>Quick Links</th>\n\t </tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><a href='customers-new.php'>Add Customer</a></td>\n\t\t\t</tr>\n\t\t\t<script>document.write(getQuicklinkSpecial());</script>\n\t\t</table>";
}
if (AJAX) {
return $ajaxCust;
} else {
return "{$printCust_begin}{$ajaxCust}{$printCust_end}";
}
return $OUT;
}
function printAgeAge()
{
# Set up table to display in
$printCust = "\r\n\t\t<h3>Debtors Age Analysis</h3>\r\n\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t<tr>\r\n\t\t\t\t<th>Acc no.</th>\r\n\t\t\t\t<th>Customer</th>\r\n\t\t\t\t<th>Contact Name</th>\r\n\t\t\t\t<th>Tel No.</th>\r\n\t\t\t\t<th>Current</th>\r\n\t\t\t\t<th>30 days</th>\r\n\t\t\t\t<th>60 days</th>\r\n\t\t\t\t<th>90 days</th>\r\n\t\t\t\t<th>120 days</th>\r\n\t\t\t\t<th>Total Outstanding</th>\r\n\t\t\t</tr>";
# Connect to database
db_connect();
# Query server
$i = 0;
$sql = "SELECT * FROM customers WHERE div = '" . USER_DIV . "' ORDER BY accno ASC";
$custRslt = db_exec($sql) or errDie("Unable to retrieve Customers from database.");
if (pg_numrows($custRslt) < 1) {
return "<li>There are no Customers in Cubit.</li>";
}
# Totals
$totcurr = 0;
$tot30 = 0;
$tot60 = 0;
$tot90 = 0;
$tot120 = 0;
$alltot = 0;
while ($cust = pg_fetch_array($custRslt)) {
# Get all ages
$curr = ageage($cust['cusnum'], 0);
$age30 = ageage($cust['cusnum'], 1);
$age60 = ageage($cust['cusnum'], 2);
$age90 = ageage($cust['cusnum'], 3);
$age120 = ageage($cust['cusnum'], 4);
# Customer total
$custtot = sprint($curr + $age30 + $age60 + $age90 + $age120);
$printCust .= "\r\n\t\t\t<tr>\r\n\t\t\t\t<td>{$cust['accno']}</td>\r\n\t\t\t\t<td>{$cust['surname']}</td>\r\n\t\t\t\t<td>{$cust['contname']}</td>\r\n\t\t\t\t<td>{$cust['tel']}</td>\r\n\t\t\t\t<td>" . CUR . " {$curr}</td>\r\n\t\t\t\t<td>" . CUR . " {$age30}</td>\r\n\t\t\t\t<td>" . CUR . " {$age60}</td>\r\n\t\t\t\t<td>" . CUR . " {$age90}</td>\r\n\t\t\t\t<td>" . CUR . " {$age120}</td>\r\n\t\t\t\t<td>" . CUR . " {$custtot}</td>\r\n\t\t\t</tr>";
# Hold totals
$totcurr += $curr;
$tot30 += $age30;
$tot60 += $age60;
$tot90 += $age90;
$tot120 += $age120;
$alltot += $custtot;
$i++;
}
$totcurr = sprint($totcurr);
$tot30 = sprint($tot30);
$tot60 = sprint($tot60);
$tot90 = sprint($tot90);
$tot120 = sprint($tot120);
$alltot = sprint($alltot);
$printCust .= "\r\n\t\t\t" . TBL_BR . "\r\n\t\t\t<tr>\r\n\t\t\t\t<td colspan='4'><b>Totals</b></td>\r\n\t\t\t\t<td nowrap><b>" . CUR . " {$totcurr}</b></td>\r\n\t\t\t\t<td nowrap><b>" . CUR . " {$tot30}</b></td>\r\n\t\t\t\t<td nowrap><b>" . CUR . " {$tot60}</b></td>\r\n\t\t\t\t<td nowrap><b>" . CUR . " {$tot90}</b></td>\r\n\t\t\t\t<td nowrap><b>" . CUR . " {$tot120}</b></td>\r\n\t\t\t\t<td nowrap><b>" . CUR . " {$alltot}</b></td>\r\n\t\t\t</tr>\r\n\t\t\t" . TBL_BR . "\r\n\t\t\t<tr>\r\n\t\t\t\t<td align='center' colspan='10'>\r\n\t\t\t\t\t<form action='../xls/debt-age-analysis-xls.php' method=post name=form>\r\n\t\t\t\t\t\t<input type='submit' name='xls' value='Export to spreadsheet'>\r\n\t\t\t\t\t</form>\r\n\t\t\t\t</td>\r\n\t\t\t</tr>\r\n\t\t</table>";
include "temp.xls.php";
Stream("DebtAgeAnalisys", $printCust);
exit;
}
function printCust()
{
global $_SESSION, $_GET;
# get vars
extract($_GET);
if (!isset($action)) {
$action = "listcust";
}
/* session var prefix */
$SPRE = "custview_";
/* max number of customers in list */
if (isset($viewall_cust)) {
define("ACT_SHOW_LIMIT", 2147483647);
$offset = 0;
} else {
define("ACT_SHOW_LIMIT", SHOW_LIMIT);
}
if (!isset($fval) && isset($_SESSION["{$SPRE}fval"])) {
$fval = $_SESSION["{$SPRE}fval"];
}
if (!isset($filter) && isset($_SESSION["{$SPRE}filter"])) {
$filter = $_SESSION["{$SPRE}filter"];
}
if (!isset($all) && isset($_SESSION["{$SPRE}all"])) {
$all = $_SESSION["{$SPRE}all"];
}
if (isset($filter) && !isset($all)) {
if ($filter == "all") {
$filter = "surname";
}
$sqlfilter = " AND lower({$filter}) LIKE lower('%{$fval}%')";
if (isset($_SESSION["{$SPRE}all"])) {
unset($_SESSION["{$SPRE}all"]);
}
$_SESSION["{$SPRE}fval"] = $fval;
$_SESSION["{$SPRE}filter"] = $filter;
} else {
if (isset($_SESSION["{$SPRE}fval"])) {
unset($_SESSION["fval"]);
}
if (isset($_SESSION["{$SPRE}filter"])) {
unset($_SESSION["filter"]);
}
$filter = "";
$fval = "";
$_SESSION["{$SPRE}all"] = "true";
$sqlfilter = "";
}
$filterarr = array("surname" => "Company/Name", "init" => "Initials", "accno" => "Account Number", "deptname" => "Department", "category" => "Category", "class" => "Classification");
$filtersel = extlib_cpsel("filter", $filterarr, $filter);
# Set up table to display in
$printCust_begin = "\n <h3>Current Customers</h3>\n\t<table border='0' cellpadding='" . TMPL_tblCellPadding . "' cellspacing='" . TMPL_tblCellSpacing . "'>\n\t\t<input type='hidden' name='action' value='{$action}'>\n\t<tr><th>.: Filter :.</th><th>.: Value :.</th></tr>\n\t<tr class='bg-odd'>\n\t\t<td>{$filtersel}</td>\n\t\t<td><input type='text' size='20' id='fval' value='{$fval}' onKeyUp='applyFilter();'></td>\n\t</tr>\n\t<tr class='bg-even'>\n\t\t<td align=center><input type='button' name='all' value='View All' onClick='viewAll();'></td>\n\t\t<td align=center><input type='button' value='Apply Filter' onClick='applyFilter();'></td>\n\t</tr>\n\t</table>\n\t<script>\n\t\t/* CRM CODE */\n\t\tfunction updateAccountInfo(id, name) {\n\t\t\twindow.opener.document.frm_con.accountname.value=name;\n\t\t\twindow.opener.document.frm_con.account_id.value=id;\n\t\t\twindow.opener.document.frm_con.account_type.value='Customer';\n\t\t\twindow.close();\n\t\t}\n\n\t\t/* AJAX filter code */\n\t\tfunction viewAll() {\n\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET);\n\t\t}\n\n\t\tfunction applyFilter() {\n\t\t\tfilter = getObject('filter').value;\n\t\t\tfval = getObject('fval').value;\n\n\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'filter=' + filter + '&fval=' + fval);\n\t\t}\n\n\t\tfunction updateOffset(noffset, viewall) {\n\t\t\tif (viewall && !noffset) {\n\t\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'viewall_cust=t');\n\t\t\t} else {\n\t\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'offset=' + noffset);\n\t\t\t}\n\t\t}\n\t</script>\n\t<p>\n\t<div id='cust_list'>";
if (!isset($err)) {
$err = "";
} else {
$err = "<li class='err'>{$err}</li>";
}
$ajaxCust = "\n\t<form action='customers-email-msg.php' method='post'>\n\t{$err}";
if (!isset($offset) && isset($_SESSION["{$SPRE}offset"])) {
$offset = $_SESSION["{$SPRE}offset"];
} else {
if (!isset($offset)) {
$offset = 0;
}
}
$_SESSION["{$SPRE}offset"] = $offset;
# connect to database
db_connect();
# counting the number of possible entries
$sql = "SELECT * FROM customers\n \t\tWHERE (div = '" . USER_DIV . "' OR ddiv = '" . USER_DIV . "') AND length(email) > 5 {$sqlfilter}\n \t\tORDER BY surname ASC";
$rslt = db_exec($sql) or errDie("Error counting matching customers.");
$custcount = pg_num_rows($rslt);
/* view offsets */
if ($offset > 0) {
$poffset = $offset >= ACT_SHOW_LIMIT ? $offset - ACT_SHOW_LIMIT : 0;
$os_prev = "<a class='nav' href='javascript: updateOffset(\"{$poffset}\");'>Previous</a>";
} else {
$os_prev = " ";
}
if ($offset + ACT_SHOW_LIMIT > $custcount) {
$os_next = " ";
} else {
$noffset = $offset + ACT_SHOW_LIMIT;
$os_next = "<a class='nav' href='javascript: updateOffset(\"{$noffset}\");'>Next</a>";
}
if ($os_next != " " || $os_prev != " ") {
$os_viewall = "| <a class='nav' href='javascript: updateOffset(false, true);'>View All</a>";
} else {
$os_viewall = "";
}
$ajaxCust .= "\n\t<table " . TMPL_tblDflts . ">\n\t<tr>\n\t\t<td colspan='20'>\n\t\t<table width='100%' border='0'>\n\t\t<tr>\n\t\t\t<td align='right' width='50%'>{$os_prev}</td>\n\t\t\t<td align='left' width='50%'>{$os_next} {$os_viewall}</td>\n\t\t</tr>\n\t\t</table>\n\t\t</td>\n\t</tr>\n\t<tr>\n\t\t<th>Acc no.</th>\n\t\t<th>Company/Name</th>\n\t\t<th>Tel</th>\n\t\t<th>Category</th>\n\t\t<th>Class</th>\n\t\t<th colspan='2'>Balance</th>\n\t\t<th>Overdue</th>\n\t\t" . ($pure ? "" : "<th colspan='11'>Options</th>") . "\n\t</tr>";
# Query server
$tot = 0;
$totoverd = 0;
$i = 0;
$sql = "SELECT * FROM customers\n \t\tWHERE (div = '" . USER_DIV . "' OR ddiv = '" . USER_DIV . "') AND length(email) > 5 {$sqlfilter}\n \t\tORDER BY surname ASC\n \t\tOFFSET {$offset} LIMIT " . ACT_SHOW_LIMIT;
$custRslt = db_exec($sql) or errDie("unable to get customer list.");
if (pg_numrows($custRslt) < 1) {
$ajaxCust .= "\n\t\t<tr class='bg-odd'>\n\t\t\t<td colspan=20><li>There are no Customers matching the criteria entered.</li></td>\n\t\t</tr>";
} else {
while ($cust = pg_fetch_array($custRslt)) {
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$overd = ageage($cust['cusnum'], $cust['overdue'] / 30 - 1, $cust['location']);
} else {
$overd = age($cust['cusnum'], $cust['overdue'] - 1, $cust['location']);
}
if ($overd < 0) {
$overd = 0;
}
if ($overd > $cust['balance']) {
$overd = $cust['balance'];
}
$totoverd += $overd;
if (strlen(trim($cust['bustel'])) < 1) {
$cust['bustel'] = $cust['tel'];
}
$cust['balance'] = sprint($cust['balance']);
$tot = $tot + $cust['balance'];
$inv = "";
# Locations drop down
$locs = array("loc" => "Local", "int" => "International", "" => "");
$loc = $locs[$cust['location']];
$sp4 = " ";
$fbal = "{$sp4}--{$sp4}";
$ocurr = CUR;
$trans = "";
# alternate bgcolor
$ajaxCust .= "<tr class='" . bg_class() . "'>";
if ($action == "contact_acc") {
$updatelink = "javascript: updateAccountInfo(\"{$cust['cusnum']}\", \"{$cust['accno']}\");";
$ajaxCust .= "\n\t\t\t\t\t<td><a href='{$updatelink}'>{$cust['accno']}</a></td>\n\t\t\t\t\t<td><a href='{$updatelink}'>{$cust['surname']}</a></td>";
} else {
$ajaxCust .= "\n\t\t\t\t\t<td>{$cust['accno']}</td>\n\t\t\t\t\t<td>{$cust['surname']}</td>";
}
$ajaxCust .= "\n\t\t\t\t\t<td>{$cust['bustel']}</td>\n\t\t\t\t\t<td>{$cust['catname']}</td>\n\t\t\t\t\t<td>{$cust['classname']}</td>\n\t\t\t\t\t<td align='right'>" . CUR . " {$cust['balance']}</td>\n\t\t\t\t\t<td align='right'>{$fbal}</td>\n\t\t\t\t\t<td align='right'>{$ocurr} {$overd}</td>";
if ($action == "listcust") {
$ajaxCust .= "\n\t\t\t\t\t{$trans} {$inv}";
$ajaxCust .= "\n\t\t\t\t<td><input type='checkbox' name='emails[]' value='{$cust['email']}' checked='yes'/></td>\n\t\t\t\t</tr>";
}
$i++;
}
if ($i > 1) {
$s = "s";
} else {
$s = "";
}
$tot = sprint($tot);
$totoverd = sprint($totoverd);
$ajaxCust .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='5'>Total Amount Outstanding, from {$i} client{$s} </td>\n\t\t\t<td align='right'>" . CUR . " {$tot}</td>\n\t\t\t<td></td>\n\t\t\t<td align='right'>" . CUR . " {$totoverd}</td>\n\t\t\t<td colspan='11' align='right'><input type='submit' name='email' value='Email Customers' /></td>\n\t\t</tr>";
$ajaxCust .= "\n\t\t<tr>\n\t\t\t<td colspan='20'>\n\t\t\t<table width='100%' border='0'>\n\t\t\t<tr>\n\t\t\t\t<td align='right' width='50%'>{$os_prev}</td>\n\t\t\t\t<td align='left' width='50%'>{$os_next} {$os_viewall}</td>\n\t\t\t</tr>\n\t\t\t</table>\n\t\t\t</td>\n\t\t</tr>";
}
$ajaxCust .= "\n\t<tr><td><br /></td></tr>\n\t</table>\n\t</form>";
$printCust_end = "\n\t</div>" . mkQuickLinks(ql("customers-new.php", "Add New Customer"));
if (AJAX) {
return $ajaxCust;
} else {
return "{$printCust_begin}{$ajaxCust}{$printCust_end}";
}
}
function printCust()
{
global $_SESSION;
extract($_REQUEST);
if (!isset($action)) {
$action = "listcust";
}
/* session var prefix */
$SPRE = "custview_";
/* max number of customers in list */
if (isset($viewall_cust)) {
$offset = 0;
define("ACT_SHOW_LIMIT", 2147483647);
} else {
define("ACT_SHOW_LIMIT", SHOW_LIMIT);
}
if (!isset($fval) && isset($_SESSION["{$SPRE}fval"])) {
$fval = $_SESSION["{$SPRE}fval"];
}
if (!isset($filter) && isset($_SESSION["{$SPRE}filter"])) {
$filter = $_SESSION["{$SPRE}filter"];
}
if (!isset($all) && isset($_SESSION["{$SPRE}all"]) && !isset($filter) && !isset($fval)) {
$all = $_SESSION["{$SPRE}all"];
}
if (isset($filter) && isset($fval) && !isset($all)) {
if (strlen($filter) > 0) {
if ($filter == "all") {
$sqlfilter = " AND (lower(accno) LIKE lower('%{$fval}%') OR lower(surname) LIKE lower('%{$fval}%') OR lower(paddr1) LIKE lower('%{$fval}%') OR lower(addr1) LIKE lower('%{$fval}%') OR lower(del_addr1) LIKE lower('%{$fval}%') OR lower(bustel) LIKE lower('%{$fval}%') OR lower(email) LIKE lower('%{$fval}%') OR lower(vatnum) LIKE lower('%{$fval}%') OR lower(contname) LIKE lower('%{$fval}%') OR lower(tel) LIKE lower('%{$fval}%') OR lower(cellno) LIKE lower('%{$fval}%') OR lower(fax) LIKE lower('%{$fval}%') OR lower(url) LIKE lower('%{$fval}%') OR lower(comments) LIKE lower('%{$fval}%') OR lower(bankname) LIKE lower('%{$fval}%') OR lower(branname) LIKE lower('%{$fval}%') OR lower(brancode) LIKE lower('%{$fval}%') OR lower(bankaccno) LIKE lower('%{$fval}%') OR lower(bankaccname) LIKE lower('%{$fval}%') OR lower(bankacctype) LIKE lower('%{$fval}%'))";
} else {
$sqlfilter = " AND lower({$filter}) LIKE lower('%{$fval}%')";
}
} else {
$sqlfilter = "";
}
if (isset($_SESSION["{$SPRE}all"])) {
unset($_SESSION["{$SPRE}all"]);
}
$_SESSION["{$SPRE}fval"] = $fval;
$_SESSION["{$SPRE}filter"] = $filter;
} else {
if (isset($_SESSION["{$SPRE}fval"])) {
unset($_SESSION["{$SPRE}fval"]);
}
if (isset($_SESSION["{$SPRE}filter"])) {
unset($_SESSION["{$SPRE}filter"]);
}
$filter = "";
$fval = "";
$_SESSION["{$SPRE}all"] = "true";
$sqlfilter = "";
}
$filterarr = array("all" => "Detailed", "surname" => "Company/Name", "init" => "Initials", "accno" => "Account Number", "deptname" => "Department", "category" => "Category", "class" => "Classification");
$filtersel = extlib_cpsel("filter", $filterarr, $filter, "onChange='applyFilter();'");
if (isset($export)) {
$pure = true;
} else {
$pure = false;
}
if (!$pure) {
# Set up table to display in
$printCust_begin = "\n\t <h3>" . (isset($findcust) ? "Find" : "Current") . " Customers</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t<input type='hidden' name='action' value='{$action}' />\n\t\t<tr>\n\t\t\t<th>.: Filter :.</th>\n\t\t\t<th colspan='2'>.: Search :.</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>{$filtersel}</td>\n\t\t\t<td><input type='text' size='20' id='fval' value='{$fval}'></td>\n\t\t\t<td align='center'><input type='button' value='Search' onClick='applyFilter();' /></td>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td align='center'><input type='button' name='all' value='View All' onClick='viewAll();' /></td>\n\t\t</tr>\n\t\t</table>\n\t\t<script>\n\t\t\t/* CRM CODE */\n\t\t\tfunction updateAccountInfo(id, name) {\n\t\t\t\twindow.opener.document.frm_con.accountname.value=name;\n\t\t\t\twindow.opener.document.frm_con.account_id.value=id;\n\t\t\t\twindow.opener.document.frm_con.account_type.value='Customer';\n\t\t\t\twindow.close();\n\t\t\t}\n\n\t\t\t/* AJAX filter code */\n\t\t\tfunction viewAll() {\n\t\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'all=t');\n\t\t\t}\n\n\t\t\tfunction applyFilter() {\n\t\t\t\tfilter = getObject('filter').value;\n\t\t\t\tfval = getObject('fval').value;\n\n\t\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'filter=' + filter + '&fval=' + fval);\n\t\t\t}\n\n\t\t\tfunction updateOffset(noffset, viewall) {\n\t\t\t\tif (viewall && !noffset) {\n\t\t\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'viewall_cust=t');\n\t\t\t\t} else {\n\t\t\t\t\tajaxRequest('" . SELF . "', 'cust_list', AJAX_SET, 'offset=' + noffset);\n\t\t\t\t}\n\t\t\t}\n\t\t</script>\n\t\t<p>\n\t\t<div id='cust_list'>";
} else {
$printCust_begin = "";
}
/* FIND CUSTOMER START */
if (!isset($findcust)) {
$ajaxCust = "";
if (!$pure) {
$ajaxCust .= "\n\t\t<form action='statements-email.php' method='get'>\n\t\t<input type='hidden' name='key' value='confirm' />";
}
if (!isset($offset) && isset($_SESSION["{$SPRE}offset"])) {
$offset = $_SESSION["{$SPRE}offset"];
} else {
if (!isset($offset)) {
$offset = 0;
}
}
$_SESSION["{$SPRE}offset"] = $offset;
# connect to database
db_connect();
# counting the number of possible entries
$sql = "SELECT * FROM customers\n \t\tWHERE (div = '" . USER_DIV . "' OR ddiv = '" . USER_DIV . "') {$sqlfilter}\n \t\tORDER BY surname ASC";
$rslt = db_exec($sql) or errDie("Error counting matching customers.");
$custcount = pg_num_rows($rslt);
# Query server
$tot = 0;
$totoverd = 0;
$i = 0;
if (!isset($ajaxCust)) {
$ajaxCust = "";
}
/* view offsets */
if ($offset > 0) {
$poffset = $offset >= ACT_SHOW_LIMIT ? $offset - ACT_SHOW_LIMIT : 0;
$os_prev = "<a class='nav' href='javascript: updateOffset(\"{$poffset}\");'>Previous</a>";
} else {
$os_prev = " ";
}
if ($offset + ACT_SHOW_LIMIT > $custcount) {
$os_next = " ";
} else {
$noffset = $offset + ACT_SHOW_LIMIT;
$os_next = "<a class='nav' href='javascript: updateOffset(\"{$noffset}\");'>Next</a>";
}
if ($os_next != " " || $os_prev != " ") {
$os_viewall = "| <a class='nav' href='javascript: updateOffset(false, true);'>View All</a>";
} else {
$os_viewall = "";
}
$ajaxCust .= "\n\t<table " . TMPL_tblDflts . ">\n\t<tr>\n\t\t<td colspan='20'>\n\t\t<table width='100%' border='0'>\n\t\t<tr>\n\t\t\t<td align='right' width='50%'>{$os_prev}</td>\n\t\t\t<td align='left' width='50%'>{$os_next} {$os_viewall}</td>\n\t\t</tr>\n\t\t</table>\n\t\t</td>\n\t</tr>\n\t<tr>\n\t\t<th>Acc no.</th>\n\t\t<th>Company/Name</th>\n\t\t<th>Tel</th>\n\t\t<th>Category</th>\n\t\t<th>Class</th>\n\t\t<th colspan='2'>Balance</th>\n\t\t<th>Overdue</th>\n\t\t" . ($pure ? "" : "<th colspan='11'>Options</th>") . "\n\t</tr>";
/* query object for cashbook */
$cashbook = new dbSelect("cashbook", "cubit");
$custRslt = new dbSelect("customers", "cubit", grp(m("where", "(div ='" . USER_DIV . "' or ddiv='" . USER_DIV . "') {$sqlfilter}"), m("order", "surname ASC"), m("offset", $offset), m("limit", ACT_SHOW_LIMIT)));
$custRslt->run();
if ($custRslt->num_rows() < 1) {
$ajaxCust .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='20'><li>There are no Customers matching the criteria entered.</li></td>\n\t\t</tr>";
} else {
while ($cust = $custRslt->fetch_array()) {
if (!user_in_team($cust["team_id"], USER_ID)) {
continue;
}
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$overd = ageage($cust['cusnum'], $cust['overdue'] / 30 - 1, $cust['location']);
} else {
$overd = age($cust['cusnum'], $cust['overdue'] - 1, $cust['location']);
}
if ($overd < 0) {
$overd = 0;
}
if ($overd > $cust['balance']) {
$overd = $cust['balance'];
}
if ($cust["location"] == "int") {
$cur = qryCurrency($cust["fcid"], "rate");
$rate = $cur["rate"];
if ($rate != 0) {
$totoverd += $overd * $rate;
} else {
$totoverd += $overd;
}
} else {
$totoverd += $overd;
}
if (!$pure) {
/* check if customer may be removed */
$cashbook->setOpt(grp(m("where", "cusnum='{$cust['cusnum']}' AND banked='no' AND div='" . USER_DIV . "'")));
$cashbook->run();
if ($cashbook->num_rows() <= 0 && $cust['balance'] == 0) {
$rm = "<td><a href='cust-rem.php?cusnum={$cust['cusnum']}'>Remove</a></td>";
} else {
$rm = "<td></td>";
}
}
if (strlen(trim($cust['bustel'])) < 1) {
$cust['bustel'] = $cust['tel'];
}
$cust['balance'] = sprint($cust['balance']);
if ($cust["location"] == "int") {
if ($rate != 0.0) {
$tot = $tot + $cust['fbalance'] * $rate;
} else {
$tot = $tot + $cust['balance'];
}
} else {
$tot = $tot + $cust['balance'];
}
/* determine which template to use when printing customer invoices */
if (templateScript("invoices") != "pdf/cust-pdf-print-invoices.php") {
$template = "pdf/pdf-tax-invoice.php?type=cusprintinvoices";
} else {
$template = "pdf/pdf-tax-invoice.php?type=cusprintinvoices";
}
$inv = "";
$inv = "\n\t\t\t<td>\n\t\t\t\t<a href='{$template}&cusnum={$cust['cusnum']}' target='_blank'>Print Invoices</a>\n\t\t\t</td>";
# Locations drop down
$locs = array("loc" => "Local", "int" => "International", "" => "");
$loc = $locs[$cust['location']];
$fbal = "--";
$ocurr = CUR;
$trans = "\n\t\t\t<td>\n\t\t\t\t<a href='core/cust-trans.php?cusnum={$cust['cusnum']}'>Transaction</a>\n\t\t\t</td>";
if ($cust['location'] == 'int') {
$fbal = "{$cust['currency']} {$cust['fbalance']}";
$ocurr = CUR;
$trans = "\n\t\t\t\t<td>\n\t\t\t\t\t<a href='core/intcust-trans.php?cusnum={$cust['cusnum']}'>Transaction</a>\n\t\t\t\t</td>";
$receipt = "<a href='bank/bank-recpt-inv-int.php?cusid={$cust['cusnum']}&cash=yes'>Add Receipt</a>";
} else {
$receipt = "<a href='bank/bank-recpt-inv.php?cusnum={$cust['cusnum']}&cash=yes'>Add Receipt</a>";
}
# alternate bgcolor
$bgColor = bgcolor($i);
$ajaxCust .= "<tr class='" . bg_class() . "'>";
if ($action == "contact_acc") {
$updatelink = "javascript: updateAccountInfo(\"{$cust['cusnum']}\", \"{$cust['accno']}\");";
$ajaxCust .= "\n\t\t\t\t\t<td><a href='{$updatelink}'>{$cust['accno']}</a></td>\n\t\t\t\t\t<td><a href='{$updatelink}'>{$cust['surname']}</a></td>";
} else {
if ($action == "select") {
$ajaxCust .= "\n\t\t\t\t\t<td><a href='" . SELF . "?key=select&cusnum={$cust['cusnum']}&" . frmupdate_passon(true) . "'>{$cust['accno']}</a></td>\n\t\t\t\t\t<td><a href='" . SELF . "?key=select&cusnum={$cust['cusnum']}&" . frmupdate_passon(true) . "'>{$cust['surname']}</a></td>";
} else {
$ajaxCust .= "\n\t\t\t\t\t<td>{$cust['accno']}</td>\n\t\t\t\t\t<td>{$cust['surname']}</td>";
}
}
$ajaxCust .= "\n\t\t\t\t\t<td>{$cust['bustel']}</td>\n\t\t\t\t\t<td>{$cust['catname']}</td>\n\t\t\t\t\t<td>{$cust['classname']}</td>\n\t\t\t\t\t<td align='right' nowrap>{$ocurr} {$cust['balance']}</td>\n\t\t\t\t\t<td align='center' nowrap>{$fbal}</td>\n\t\t\t\t\t<td align='right' nowrap>{$ocurr} {$overd}</td>";
if (!$pure) {
if ($action == "listcust") {
$ajaxCust .= "\n\t\t\t\t\t\t<td>{$receipt}</td>\n\t\t\t\t\t\t<td><a href='delnote-report.php?cusnum={$cust['cusnum']}'>Outstanding Stock</a></td>\n\t\t\t\t\t\t<td><a href='cust-det.php?cusnum={$cust['cusnum']}'>Details</a></td>\n\t\t\t\t\t\t<td><a href='customers-new.php?cusnum={$cust['cusnum']}'>Edit</a></td>\n\t\t\t\t\t\t<td><a href='#' onClick='openPrintWin(\"cust-stmnt.php?cusnum={$cust['cusnum']}\");'>Statement</a></td>\n\t\t\t\t\t\t{$trans} {$inv}";
if ($cust['blocked'] == 'yes') {
$ajaxCust .= "<td><a href='cust-unblock.php?cusnum={$cust['cusnum']}'>Unblock</a></td>";
} else {
$ajaxCust .= "<td><a href='cust-block.php?cusnum={$cust['cusnum']}'>Block</a></td>";
}
$ajaxCust .= "<td><a href='transheks/pricelist_send.php?cusnum={$cust['cusnum']}'>Send Pricelist</a></td>";
$ajaxCust .= "{$rm} <td><a href='conper-add.php?type=cust&id={$cust['cusnum']}'>Add Contact</a></td>\n\t\t\t\t\t<td><input type='checkbox' name='cids[]' value='{$cust['cusnum']}' /></td>";
} else {
$ajaxCust .= "\n\t\t\t\t\t\t<td align=center>\n\t\t\t\t\t\t\t<a href='javascript: popupSized(\"cust-det.php?cusnum={$cust['cusnum']}\", \"custdetails\", 550, 400, \"\");'>Details</a>\n\t\t\t\t\t\t</td>";
}
}
$ajaxCust .= "</tr>";
}
$bgColor = bgcolor($i);
$tot = sprint($tot);
$totoverd = sprint($totoverd);
$i--;
$ajaxCust .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='5'>Total Amount Outstanding, from {$i} " . ($i > 1 ? "clients" : "client") . "</td>\n\t\t\t<td align='right' nowrap>" . CUR . " {$tot}</td>\n\t\t\t<td></td>\n\t\t\t<td align='right' nowrap>" . CUR . " {$totoverd}</td>\n\t\t\t" . ($pure ? "" : "<td colspan='11' align='right'><input type='submit' value='Email Statements' /></td>") . "\n\t\t</tr>";
if (!$pure) {
$ajaxCust .= "\n\t\t\t<tr>\n\t\t\t\t<td colspan='20'>\n\t\t\t\t<table width='100%' border='0'>\n\t\t\t\t<tr>\n\t\t\t\t\t<td align='right' width='50%'>{$os_prev}</td>\n\t\t\t\t\t<td align='left' width='50%'>{$os_next} {$os_viewall}</td>\n\t\t\t\t</tr>\n\t\t\t\t</table>\n\t\t\t\t</td>\n\t\t\t</tr>";
}
}
if ($pure) {
$ajaxCust .= "</table>";
} else {
$ajaxCust .= "\n\t\t" . TBL_BR . "\n\t\t</table>\n\t\t</form>\n\t\t<form action='" . SELF . "' method='post'>\n\t\t<table>\n\t\t\t<input type='hidden' name='export' value='yes' />\n\t\t\t<input type='hidden' name='filter' value='{$filter}' />\n\t\t\t<input type='hidden' name='fval' value='{$fval}' />\n\t\t\t<tr>\n\t\t\t\t<td colspan='3'><input type='submit' value='Export to Spreadsheet' /></td>\n\t\t\t</tr>\n\t\t</table>\n\t\t</form>";
}
/* FIND CUSTOMER END */
} else {
$ajaxCust = "";
}
$printCust_end = "\n\t</div>";
if (!$pure) {
$printCust_end .= mkQuickLinks(ql("customers-new.php", "Add New Customer"));
}
if (AJAX) {
return $ajaxCust;
} else {
return "{$printCust_begin}{$ajaxCust}{$printCust_end}";
}
}
function printStmnt($_GET)
{
# get vars
extract($_GET);
# validate input
require_lib("validate");
$v = new validate();
$v->isOk($cusnum, "num", 1, 20, "Invalid Customer number.");
if (isset($from_day)) {
$BYDATE = true;
$v->isOk($from_day, "num", 1, 2, "Invalid from Date day.");
$v->isOk($from_month, "num", 1, 2, "Invalid from Date month.");
$v->isOk($from_year, "num", 1, 4, "Invalid from Date Year.");
$v->isOk($to_day, "num", 1, 2, "Invalid to Date day.");
$v->isOk($to_month, "num", 1, 2, "Invalid to Date month.");
$v->isOk($to_year, "num", 1, 4, "Invalid to Date Year.");
# mix dates
$fromdate = $from_year . "-" . $from_month . "-" . $from_day;
$todate = $to_year . "-" . $to_month . "-" . $to_day;
if (!checkdate($from_month, $from_day, $from_year)) {
$v->isOk($fromdate, "num", 1, 1, "Invalid from date.");
}
if (!checkdate($to_month, $to_day, $to_year)) {
$v->isOk($todate, "num", 1, 1, "Invalid to date.");
}
} else {
$BYDATE = false;
}
# display errors, if any
$err = "";
if ($v->isError()) {
$errors = $v->getErrors();
foreach ($errors as $e) {
$err .= "<li class='err'>" . $e["msg"] . "</li>";
}
return $err;
}
if ($BYDATE) {
$bdfilter = "date >= '{$fromdate}' AND date <= '{$todate}'";
$heading = "Period Range Statement : {$fromdate} - {$todate}";
} else {
$fdate = date("Y") . "-" . date("m") . "-" . "01";
$bdfilter = "date >= '{$fdate}'";
$heading = "Monthly Statement";
}
# Get selected customer info
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to view customer");
if (pg_numrows($custRslt) < 1) {
return "<li class='err'>Invalid Customer Number.</li>";
}
$cust = pg_fetch_array($custRslt);
# connect to database
db_connect();
$fdate = date("Y") . "-" . date("m") . "-" . "01";
$stmnt = array();
$totout = 0;
#check for sort ...
if (isset($sort) and $sort == "branch") {
$sortinga = "ORDER BY branch";
$sorting = "branch,";
} else {
$sortinga = "";
$sorting = "";
}
if (!open()) {
# Query server
$sql = "SELECT * FROM stmnt WHERE cusnum = '{$cusnum}' AND {$bdfilter} AND div = '" . USER_DIV . "' ORDER BY {$sorting} date ASC";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
//$stmnt .= "<tr><td colspan=4>No invoices for this month.</td></tr>";
} else {
while ($st = pg_fetch_array($stRslt)) {
# keep track of da totals
$totout += $st['amount'];
}
}
} else {
# Query server
$sql = "SELECT * FROM open_stmnt WHERE cusnum = '{$cusnum}' AND {$bdfilter} AND balance != '0' AND div = '" . USER_DIV . "' ORDER BY {$sorting} date ASC";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
//$stmnt .= "<tr><td colspan=4>No invoices for this month.</td></tr>";
} else {
while ($st = pg_fetch_array($stRslt)) {
# keep track of da totals
$totout += $st['balance'];
}
}
}
// we need a way to get this for a date range selection as well ....
// balance brought forward == sum of all transactions before selected start date
// $balbf = ($cust['balance'] - $totout);
if ($BYDATE) {
$bdfilter2 = "date < '{$fromdate}'";
} else {
$fdate = date("Y") . "-" . date("m") . "-" . "01";
$bdfilter2 = "date < '{$fdate}'";
}
if (!open()) {
$sql = "SELECT sum(amount) FROM stmnt WHERE cusnum = '{$cusnum}' AND {$bdfilter2} AND div = '" . USER_DIV . "'";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
$stmnt .= "<tr><td colspan='4'>No invoices for this month.</td></tr>";
} else {
$st = pg_fetch_array($stRslt);
}
} else {
$sql = "SELECT sum(amount) FROM open_stmnt WHERE cusnum = '{$cusnum}' AND {$bdfilter2} AND balance != '0' AND div = '" . USER_DIV . "'";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
$stmnt .= "<tr><td colspan='4'>No invoices for this month.</td></tr>";
} else {
$st = pg_fetch_array($stRslt);
}
}
// $balbf = ($cust['balance'] - $totout);
$balbf = $st['sum'];
$balbf = sprint($balbf);
$rbal = $balbf;
# Query server
if (!open()) {
db_conn("cubit");
$sql = "SELECT * FROM stmnt WHERE cusnum = '{$cusnum}' AND {$bdfilter} AND div = '" . USER_DIV . "' ORDER BY {$sorting} date ASC";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
} else {
db_conn("cubit");
$sql = "SELECT * FROM open_stmnt WHERE cusnum = '{$cusnum}' AND {$bdfilter} AND balance != '0' AND div = '" . USER_DIV . "' ORDER BY {$sorting} date ASC";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
}
if (pg_numrows($stRslt) > 0) {
while ($st = pg_fetch_array($stRslt)) {
if (!open()) {
$amtbal = sprint($st['amount']);
$rbal = sprint($rbal + $st['amount']);
} else {
$amtbal = sprint($st['balance']);
$rbal = sprint($rbal + $st['balance']);
}
# format date
$st['date'] = explode("-", $st['date']);
$st['date'] = $st['date'][2] . "-" . $st['date'][1] . "-" . $st['date'][0];
$st['amount'] = sprint($st['amount']);
if (substr($st['type'], 0, 7) == "Invoice") {
$ex = "INV";
} elseif (substr($st['type'], 0, 21) == "Non Stock Credit Note") {
$ex = "CR";
} elseif (substr($st['type'], 0, 17) == "Non-Stock Invoice") {
$ex = "INV";
} elseif (substr($st['type'], 0, 11) == "Credit Note") {
$ex = "CR";
} else {
$ex = "";
}
$stmnt[] = grp(m('date', $st['date']), m('invid', $ex . " " . $st['invid']), m('type', $st['type']), m('amount', "{$cust['currency']} {$amtbal}"), m('balance', "{$cust['currency']} {$rbal}"));
# keep track of da totals
//$totout += $amtbal;
}
}
if ($cust['location'] == 'int') {
$cust['balance'] = $cust['fbalance'];
}
//$balbf = ($cust['balance'] - $totout);
if ($BYDATE) {
$bdfilter2 = "date < '{$fromdate}'";
} else {
$fdate = date("Y") . "-" . date("m") . "-" . "01";
$bdfilter2 = "date < '{$fdate}'";
}
if (!open()) {
$sql = "SELECT sum(amount) FROM stmnt WHERE cusnum = '{$cusnum}' AND {$bdfilter2} AND div = '" . USER_DIV . "'";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
$stmnt .= "<tr><td colspan='4'>No invoices for this month.</td></tr>";
} else {
$st = pg_fetch_array($stRslt);
}
} else {
$sql = "SELECT sum(amount) FROM open_stmnt WHERE cusnum = '{$cusnum}' AND {$bdfilter2} AND balance != '0' AND div = '" . USER_DIV . "'";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
if (pg_numrows($stRslt) < 1) {
$stmnt .= "<tr><td colspan='4'>No invoices for this month.</td></tr>";
} else {
$st = pg_fetch_array($stRslt);
}
}
$balbf = $st['sum'];
$balbf = sprint($balbf);
$cust['balance'] = sprint($cust['balance']);
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$curr = ageage($cust['cusnum'], 0, $cust['fcid'], $cust['location']);
$age30 = ageage($cust['cusnum'], 1, $cust['fcid'], $cust['location']);
$age60 = ageage($cust['cusnum'], 2, $cust['fcid'], $cust['location']);
$age90 = ageage($cust['cusnum'], 3, $cust['fcid'], $cust['location']);
$age120 = ageage($cust['cusnum'], 4, $cust['fcid'], $cust['location']);
} else {
$curr = age($cust['cusnum'], 29, $cust['fcid'], $cust['location']);
$age30 = age($cust['cusnum'], 59, $cust['fcid'], $cust['location']);
$age60 = age($cust['cusnum'], 89, $cust['fcid'], $cust['location']);
$age90 = age($cust['cusnum'], 119, $cust['fcid'], $cust['location']);
$age120 = age($cust['cusnum'], 149, $cust['fcid'], $cust['location']);
$custtot = $curr + $age30 + $age60 + $age90 + $age120;
if (sprint($custtot) != sprint($cust['balance'])) {
$curr = sprint($curr + $cust['balance'] - $custtot);
$custtot = sprint($cust['balance']);
}
}
$stmnthead = array('date' => "Date", 'invid' => "Ref No.", 'type' => "Details", 'amount' => "Amount", 'balance' => "Balance");
$agehead = array('cur' => "Current", '30' => "30", '60' => "60", '90' => "90", '120' => "120");
$age = array();
$age[] = array('cur' => "{$curr}", '30' => "{$age30}", '60' => "{$age60}", '90' => "{$age90}", '120' => "{$age120}");
/* Start PDF Layout */
include "../pdf-settings.php";
$pdf =& new Cezpdf();
$pdf->selectFont($set_mainFont);
# put a line top and bottom on all the pages
$all = $pdf->openObject();
$pdf->saveState();
$pdf->setStrokeColor(0, 0, 0, 1);
$pdf->line(20, 40, 578, 40);
# $pdf->line(20,822,578,822);
$pdf->addText(20, 34, 6, 'Cubit Accounting');
$pdf->restoreState();
$pdf->closeObject();
# note that object can be told to appear on just odd or even pages by changing 'all' to 'odd'
# or 'even'.
$pdf->addObject($all, 'all');
/* End PDF Layout */
/* start PDF Layout */
# Heading
$pdf->ezText("<b>Customer {$heading}</b>", $set_txtSize + 2, array('justification' => 'centre'));
# Set y so its away from the top
$pdf->ezSetY($set_tlY);
# Company details
$smTxtSz = $set_txtSize - 3;
$pdf->addText($set_tlX, $set_tlY, $smTxtSz, COMP_NAME);
$nl = pdf_addnl($pdf, $set_tlX, $set_tlY, $smTxtSz, COMP_ADDRESS);
$pdf->addText($set_tlX, $set_tlY - $smTxtSz * $nl, $smTxtSz, COMP_PADDR);
# Company details cont
$lrite = $set_pgXCenter + 60;
$pdf->addText($lrite, $set_tlY - $smTxtSz, $smTxtSz, "COMPANY REG. " . COMP_REGNO);
$pdf->addText($lrite, $set_tlY - $smTxtSz * 2, $smTxtSz, "TEL : " . COMP_TEL);
$pdf->addText($lrite, $set_tlY - $smTxtSz * 3, $smTxtSz, "FAX : " . COMP_FAX);
$pdf->addText($lrite, $set_tlY - $smTxtSz * 4, $smTxtSz, "VAT REG. " . COMP_VATNO);
# Set y so its away from the company details
$pdf->ezSetY($set_tlY - $set_txtSize * ($nl + 1));
$address_ar = explode("\n", $cust["addr1"]);
$address_out = "";
foreach ($address_ar as $addr) {
$address_out .= makewidth($pdf, 175, 12, $addr);
}
# customer details data
$cusdet[] = array('tit' => "Account number : {$cust['accno']}");
$cusdet[] = array('tit' => "{$cust['surname']}\n {$address_out}");
$cusdet[] = array('tit' => "Balance Brought Forward : {$cust['currency']} {$balbf}");
# customer details table
$pdf->ezTable($cusdet, '', "", array('shaded' => 0, 'showLines' => 2, 'showHeadings' => 0, 'xPos' => 100));
$bnkData = qryBankAcct(getdSetting("BANK_DET"));
$banking = array(array("{$bnkData['bankname']}"), array("<b>Branch: </b>{$bnkData['branchname']}"), array("<b>Branch Code: </b>{$bnkData['branchcode']}"), array("<b>Account Number: </b>{$bnkData['accnum']}"));
global $set_pgHeight;
$pdf->ezSetY($set_tlY - $set_txtSize * ($nl + 1));
$pdf->ezTable($banking, '', "", array('shaded' => 0, 'showLines' => 2, 'showHeadings' => 0, 'xPos' => 300));
# just a new line
$pdf->ezText("\n", $set_txtSize);
# Statement table
if (count($stmnt) < 1) {
$stmnt = array($stmnthead);
//$pdf->ezTable($stmnthead, "", "", array_merge($set_maxTblOptNl, grp(m("showHeadings", 0))));
$pdf->ezTable($stmnt, "", '', array_merge($set_maxTblOptNl, array("showHeadings" => 0)));
$stmnt = array(grp(m("err", "No previous invoices/transactions for this month.")));
$pdf->ezTable($stmnt, "", '', array_merge($set_maxTblOptNl, array("showHeadings" => 0)));
} else {
$pdf->ezTable($stmnt, $stmnthead, '', $set_maxTblOptNl);
}
# just a new line
$pdf->ezText("\n", $set_txtSize);
# balance table data
//$cust[balance]
$baldat[] = array('tit' => "Total Outstanding Balance : {$cust['currency']} {$rbal}");
//$pdf->ezSetY($set_tlY -200);
# balance table
$pdf->ezTable($baldat, '', "", array('showLines' => 2, 'showHeadings' => 0, 'xPos' => $set_pgWidth - 63));
$pdf->ezText("\n", $set_txtSize);
$pdf->ezTable($age, $agehead, '', $set_maxTblOptNl);
# Send stream
$pdf->ezStream();
exit;
}
function state($id, $fromdate, $todate, $type)
{
$fdate = $fromdate;
global $set_mainFont, $set_codeFont, $set_pgWidth, $set_pgHeight, $set_pgXCenter, $set_pgYCenter, $set_tlX, $set_tlY, $set_txtSize, $set_ttlY, $set_maxTblOpt, $set_maxTblOptNl, $set_repTblOpt, $set_repTblOptSm, $set_tubTblOpt, $set_tubTblOpt2, $set_tubTblOpt3;
# Get selected customer info
db_connect();
$sql = "SELECT * FROM customers WHERE cusnum = '{$id}' AND div = '" . USER_DIV . "'";
$custRslt = db_exec($sql) or errDie("Unable to view customer");
if (pg_numrows($custRslt) < 1) {
return "<li class='err'>Invalid Customer Number.</li>";
}
$cust = pg_fetch_array($custRslt);
# connect to database
db_connect();
$fdate = date("Y") . "-" . date("m") . "-" . "01";
$stmnt = array();
$totout = 0;
if ($type == "Monthly") {
$fdate = date("Y") . "-" . date("m") . "-" . "01";
$whe = "";
} else {
$whe = "AND date<='{$todate}'";
}
if (!open()) {
# Query server
$sql = "SELECT * FROM stmnt WHERE cusnum = '{$id}' AND date >= '{$fdate}' AND div = '" . USER_DIV . "' {$whe} ORDER BY date";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
} else {
# Query server
$sql = "SELECT * FROM open_stmnt WHERE cusnum = '{$id}' AND balance != '0' AND div = '" . USER_DIV . "' {$whe} ORDER BY date";
$stRslt = db_exec($sql) or errDie("Unable to retrieve invoices statement from database.");
}
if (pg_numrows($stRslt) < 1) {
$stmnt[] = array('date' => "No invoices/transactions for this month.", 'invid' => " ", 'type' => " ", 'amount' => " ");
} else {
while ($st = pg_fetch_array($stRslt)) {
# format date
$st['date'] = explode("-", $st['date']);
$st['date'] = $st['date'][2] . "-" . $st['date'][1] . "-" . $st['date'][0];
$st['amount'] = sprint($st['amount']);
if (substr($st['type'], 0, 7) == "Invoice") {
$ex = "INV";
} elseif (substr($st['type'], 0, 21) == "Non Stock Credit Note") {
$ex = "CR";
} elseif (substr($st['type'], 0, 17) == "Non-Stock Invoice") {
$ex = "INV";
} elseif (substr($st['type'], 0, 11) == "Credit Note") {
$ex = "CR";
} else {
$ex = "";
}
$stmnt[] = array('date' => $st['date'], 'invid' => $ex . " " . $st['invid'], 'type' => $st['type'], 'amount' => "{$cust['currency']} {$st['amount']}");
# keep track of da totals
$totout += $st['amount'];
}
}
if ($cust['location'] == 'int') {
$cust['balance'] = $cust['fbalance'];
}
$balbf = $cust['balance'] - $totout;
$balbf = sprint($balbf);
$cust['balance'] = sprint($cust['balance']);
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$curr = ageage($cust['cusnum'], 0, $cust['fcid'], $cust['location']);
$age30 = ageage($cust['cusnum'], 1, $cust['fcid'], $cust['location']);
$age60 = ageage($cust['cusnum'], 2, $cust['fcid'], $cust['location']);
$age90 = ageage($cust['cusnum'], 3, $cust['fcid'], $cust['location']);
$age120 = ageage($cust['cusnum'], 4, $cust['fcid'], $cust['location']);
} else {
$curr = age($cust['cusnum'], 29, $cust['fcid'], $cust['location']);
$age30 = age($cust['cusnum'], 59, $cust['fcid'], $cust['location']);
$age60 = age($cust['cusnum'], 89, $cust['fcid'], $cust['location']);
$age90 = age($cust['cusnum'], 119, $cust['fcid'], $cust['location']);
$age120 = age($cust['cusnum'], 149, $cust['fcid'], $cust['location']);
}
$stmnthead = array('date' => "Date", 'invid' => "Ref No.", 'type' => "Details", 'amount' => "Amount");
$agehead = array('cur' => "Current", '30' => "30 days", '60' => "60 days", '90' => "90 days", '120' => "120 days +");
$age = array();
$age[] = array('cur' => "{$curr}", '30' => "{$age30}", '60' => "{$age60}", '90' => "{$age90}", '120' => "{$age120}");
/* Start PDF Layout */
//include("pdf-settings.php");
$pdf =& new Cezpdf();
$pdf->selectFont($set_mainFont);
# put a line top and bottom on all the pages
$all = $pdf->openObject();
$pdf->saveState();
$pdf->setStrokeColor(0, 0, 0, 1);
$pdf->line(20, 40, 578, 40);
# $pdf->line(20,822,578,822);
$pdf->addText(20, 34, 6, 'Cubit Accounting');
$pdf->restoreState();
$pdf->closeObject();
# note that object can be told to appear on just odd or even pages by changing 'all' to 'odd'
# or 'even'.
$pdf->addObject($all, 'all');
/* End PDF Layout */
/* start PDF Layout */
# Heading
$pdf->ezText("<b>Customer Monthly Statement</b>", $set_txtSize + 2, array('justification' => 'centre'));
if ($type == "Monthly") {
$pdf->ezText(date("Y-m-") . "01 - " . date("Y-m-d", mktime(0, 0, 0, date("m") + 1, -0, date("Y"))), $set_txtSize, array('justification' => 'centre'));
} else {
$pdf->ezText("{$fromdate} - {$todate}", $set_txtSize, array('justification' => 'centre'));
}
# Set y so its away from the top
$pdf->ezSetY($set_tlY);
# Company details
$smTxtSz = $set_txtSize - 3;
$pdf->addText($set_tlX, $set_tlY, $smTxtSz, COMP_NAME);
$nl = pdf_addnl($pdf, $set_tlX, $set_tlY, $smTxtSz, COMP_ADDRESS);
$pdf->addText($set_tlX, $set_tlY - $smTxtSz * $nl, $smTxtSz, COMP_PADDR);
# Company details cont
$lrite = $set_pgXCenter + 60;
$pdf->addText($lrite, $set_tlY - $smTxtSz, $smTxtSz, "COMPANY REG. " . COMP_REGNO);
$pdf->addText($lrite, $set_tlY - $smTxtSz * 2, $smTxtSz, "TEL : " . COMP_TEL);
$pdf->addText($lrite, $set_tlY - $smTxtSz * 3, $smTxtSz, "FAX : " . COMP_FAX);
$pdf->addText($lrite, $set_tlY - $smTxtSz * 4, $smTxtSz, "VAT REG. " . COMP_VATNO);
# Set y so its away from the company details
$pdf->ezSetY($set_tlY - $set_txtSize * ($nl + 1));
# customer details data
$cusdet[] = array('tit' => "Account number : {$cust['accno']}");
$cusdet[] = array('tit' => "{$cust['surname']}\n{$cust['addr1']}");
$cusdet[] = array('tit' => "Balance Brought Forward : {$cust['currency']} {$balbf}");
# customer details table
$pdf->ezTable($cusdet, '', "", array('shaded' => 0, 'showLines' => 2, 'showHeadings' => 0, 'xPos' => 94));
# just a new line
$pdf->ezText("\n", $set_txtSize);
# Statement table
$pdf->ezTable($stmnt, $stmnthead, '', $set_maxTblOptNl);
# just a new line
$pdf->ezText("\n", $set_txtSize);
# balance table data
$baldat[] = array('tit' => "Total Outstanding Balance : {$cust['currency']} {$cust['balance']}");
//$pdf->ezSetY($set_tlY -200);
# balance table
$pdf->ezTable($baldat, '', "", array('showLines' => 2, 'showHeadings' => 0, 'xPos' => $set_pgWidth - 63));
$pdf->ezText("\n", $set_txtSize);
if ($type == "Monthly") {
$pdf->ezTable($age, $agehead, '', $set_maxTblOptNl);
}
return $pdf->output();
exit;
# Send stream
$pdf->ezStream();
exit;
}
function printCust()
{
global $_SESSION;
extract($_REQUEST);
if (!isset($action)) {
$action = "listcust";
}
$sqlfilter = "";
$printCust_begin = "<h2>View Customers</h2>";
$ajaxCust = "";
$ajaxCust .= "\n\t<form action='statements-email.php' method='get'>\n\t<input type='hidden' name='key' value='confirm' />";
if (!isset($offset) && isset($_SESSION["offset"])) {
$offset = $_SESSION["offset"];
} else {
if (!isset($offset)) {
$offset = 0;
}
}
$_SESSION["offset"] = $offset;
# connect to database
db_connect();
# counting the number of possible entries
$sql = "SELECT * FROM customers\n \t\tWHERE (div = '" . USER_DIV . "' OR ddiv = '" . USER_DIV . "') {$sqlfilter}\n \t\tORDER BY surname ASC";
$rslt = db_exec($sql) or errDie("Error counting matching customers.");
$custcount = pg_num_rows($rslt);
# Query server
$tot = 0;
$totoverd = 0;
$i = 0;
$ajaxCust .= "\n\t<table " . TMPL_tblDflts . ">\n\n\t<tr>\n\t\t<th>Acc no.</th>\n\t\t<th>Company/Name</th>\n\t\t<th>Tel</th>\n\t\t<th>Category</th>\n\t\t<th>Class</th>\n\t\t<th colspan='2'>Balance</th>\n\t\t<th>Overdue</th>\n\t</tr>";
/* query object for cashbook */
$cashbook = new dbSelect("cashbook", "cubit");
$custRslt = new dbSelect("customers", "cubit", grp(m("where", "(div ='" . USER_DIV . "' or ddiv='" . USER_DIV . "') {$sqlfilter}"), m("order", "surname ASC"), m("offset", $offset), m("limit", 100)));
$custRslt->run();
if ($custRslt->num_rows() < 1) {
$ajaxCust .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='20'><li>There are no Customers matching the criteria entered.</li></td>\n\t\t</tr>";
} else {
while ($cust = $custRslt->fetch_array()) {
if (!user_in_team($cust["team_id"], USER_ID)) {
continue;
}
# Check type of age analisys
if (div_isset("DEBT_AGE", "mon")) {
$overd = ageage($cust['cusnum'], $cust['overdue'] / 30 - 1, $cust['location']);
} else {
$overd = age($cust['cusnum'], $cust['overdue'] - 1, $cust['location']);
}
if ($overd < 0) {
$overd = 0;
}
if ($overd > $cust['balance']) {
$overd = $cust['balance'];
}
if ($cust["location"] == "int") {
$cur = qryCurrency($cust["fcid"], "rate");
$rate = $cur["rate"];
if ($rate != 0) {
$totoverd += $overd * $rate;
} else {
$totoverd += $overd;
}
} else {
$totoverd += $overd;
}
/* check if customer may be removed */
$cashbook->setOpt(grp(m("where", "cusnum='{$cust['cusnum']}' AND banked='no' AND div='" . USER_DIV . "'")));
$cashbook->run();
if (strlen(trim($cust['bustel'])) < 1) {
$cust['bustel'] = $cust['tel'];
}
$cust['balance'] = sprint($cust['balance']);
if ($cust["location"] == "int") {
if ($rate != 0.0) {
$tot = $tot + $cust['fbalance'] * $rate;
} else {
$tot = $tot + $cust['balance'];
}
} else {
$tot = $tot + $cust['balance'];
}
# Locations drop down
$locs = array("loc" => "Local", "int" => "International", "" => "");
$loc = $locs[$cust['location']];
$fbal = "--";
$ocurr = CUR;
# alternate bgcolor
$bgColor = bgcolor($i);
$ajaxCust .= "<tr class='" . bg_class() . "'>";
if ($action == "contact_acc") {
$updatelink = "javascript: updateAccountInfo(\"{$cust['cusnum']}\", \"{$cust['accno']}\");";
$ajaxCust .= "\n\t\t\t\t\t<td><a href='{$updatelink}'>{$cust['accno']}</a></td>\n\t\t\t\t\t<td><a href='{$updatelink}'>{$cust['surname']}</a></td>";
} else {
if ($action == "select") {
$ajaxCust .= "\n\t\t\t\t\t<td><a href='" . SELF . "?key=select&cusnum={$cust['cusnum']}&" . frmupdate_passon(true) . "'>{$cust['accno']}</a></td>\n\t\t\t\t\t<td><a href='" . SELF . "?key=select&cusnum={$cust['cusnum']}&" . frmupdate_passon(true) . "'>{$cust['surname']}</a></td>";
} else {
$ajaxCust .= "\n\t\t\t\t\t<td>{$cust['accno']}</td>\n\t\t\t\t\t<td>{$cust['surname']}</td>";
}
}
$ajaxCust .= "\n\t\t\t\t\t<td>{$cust['bustel']}</td>\n\t\t\t\t\t<td>{$cust['catname']}</td>\n\t\t\t\t\t<td>{$cust['classname']}</td>\n\t\t\t\t\t<td align='right' nowrap>{$ocurr} {$cust['balance']}</td>\n\t\t\t\t\t<td align='center' nowrap>{$fbal}</td>\n\t\t\t\t\t<td align='right' nowrap>{$ocurr} {$overd}</td>";
$ajaxCust .= "</tr>";
}
$bgColor = bgcolor($i);
$tot = sprint($tot);
$totoverd = sprint($totoverd);
$i--;
$ajaxCust .= "\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td colspan='5'>Total Amount Outstanding, from {$i} " . ($i > 1 ? "clients" : "client") . "</td>\n\t\t\t<td align='right' nowrap>" . CUR . " {$tot}</td>\n\t\t\t<td></td>\n\t\t\t<td align='right' nowrap>" . CUR . " {$totoverd}</td>\n\t\t</tr>";
}
$ajaxCust .= "\n\t\t" . TBL_BR . "\n\t\t</table>\n\t\t</form>";
$printCust_end = "\n\t</div>";
if (AJAX) {
return $ajaxCust;
} else {
return "{$printCust_begin}{$ajaxCust}{$printCust_end}";
}
}
