function printAgeInv()
{
# 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
$to_month = date("m");
$to_date = "now";
$from_date = date("Y-m-d", mktime(0, 0, 0, date("m"), "01", date("Y")));
$curr = cust_age($cust['cusnum'], 29, $cust['fcid'], $cust['location'], $to_month, $to_date, $from_date);
$age30 = cust_age($cust['cusnum'], 59, $cust['fcid'], $cust['location'], $to_month, $to_date, $from_date);
$age60 = cust_age($cust['cusnum'], 89, $cust['fcid'], $cust['location'], $to_month, $to_date, $from_date);
$age90 = cust_age($cust['cusnum'], 119, $cust['fcid'], $cust['location'], $to_month, $to_date, $from_date);
$age120 = cust_age($cust['cusnum'], 149, $cust['fcid'], $cust['location'], $to_month, $to_date, $from_date);
# 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['bustel']}</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</table>";
include "temp.xls.php";
Stream("DebtAgeAnalisys", $printCust);
exit;
}
function printAgeAge()
{
# Set up table to display in
$printCust = "\n\t\t<h3>Debtors Age Analysis</h3>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th>Acc no.</th>\n\t\t\t\t<th>Customer</th>\n\t\t\t\t<th>Sales Rep</th>\n\t\t\t\t<th>Contact Name</th>\n\t\t\t\t<th>Tel No.</th>\n\t\t\t\t<th>Current</th>\n\t\t\t\t<th>30 days</th>\n\t\t\t\t<th>60 days</th>\n\t\t\t\t<th>90 days</th>\n\t\t\t\t<th>120 days</th>\n\t\t\t\t<th>Total Outstanding</th>\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
$to_month = date("m");
$to_date = "now";
$from_date = date("Y-m-d", mktime(0, 0, 0, date("m"), "01", date("Y")));
$curr = cust_age($cust['cusnum'], 29, $cust['fcid'], $cust['location'], $to_month, $to_date, $from_date);
$age30 = cust_age($cust['cusnum'], 59, $cust['fcid'], $cust['location'], $to_month, $to_date, $from_date);
$age60 = cust_age($cust['cusnum'], 89, $cust['fcid'], $cust['location'], $to_month, $to_date, $from_date);
$age90 = cust_age($cust['cusnum'], 119, $cust['fcid'], $cust['location'], $to_month, $to_date, $from_date);
$age120 = cust_age($cust['cusnum'], 149, $cust['fcid'], $cust['location'], $to_month, $to_date, $from_date);
# Customer total
$custtot = sprint($curr + $age30 + $age60 + $age90 + $age120);
db_con("exten");
# sales rep
$get_salsp = "SELECT salesp FROM salespeople WHERE salespid = '{$cust['sales_rep']}' LIMIT 1";
$run_salsp = db_exec($get_salsp) or errDie("Unable to get sales person information.");
if (pg_numrows($run_salsp) > 0) {
$sarr = pg_fetch_array($run_salsp);
$salesperson = $sarr['salesp'];
} else {
$salesperson = "";
}
$printCust .= "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td>{$cust['accno']}</td>\n\t\t\t\t<td>{$cust['surname']}</td>\n\t\t\t\t<td>{$salesperson}</td>\n\t\t\t\t<td>{$cust['contname']}</td>\n\t\t\t\t<td>{$cust['tel']}</td>\n\t\t\t\t<td>" . CUR . " {$curr}</td>\n\t\t\t\t<td>" . CUR . " {$age30}</td>\n\t\t\t\t<td>" . CUR . " {$age60}</td>\n\t\t\t\t<td>" . CUR . " {$age90}</td>\n\t\t\t\t<td>" . CUR . " {$age120}</td>\n\t\t\t\t<td>" . CUR . " {$custtot}</td>\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 .= "\n\t\t\t" . TBL_BR . "\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='4'><b>Totals</b></td>\n\t\t\t\t<td nowrap><b>" . CUR . " {$totcurr}</b></td>\n\t\t\t\t<td nowrap><b>" . CUR . " {$tot30}</b></td>\n\t\t\t\t<td nowrap><b>" . CUR . " {$tot60}</b></td>\n\t\t\t\t<td nowrap><b>" . CUR . " {$tot90}</b></td>\n\t\t\t\t<td nowrap><b>" . CUR . " {$tot120}</b></td>\n\t\t\t\t<td nowrap><b>" . CUR . " {$alltot}</b></td>\n\t\t\t</tr>\n\t\t\t" . TBL_BR . "\n\t\t\t<tr>\n\t\t\t\t<td align='center' colspan='10'><input type='submit' name='xls' value='Export to spreadsheet'></td>\n\t\t\t</tr>\n\t\t</table>\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='index-reports.php'>Financials</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><a href='index-reports-debtcred.php'>Debtors & Creditors Reports</a></td>\n\t\t\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<tr class='" . bg_class() . "'>\n\t\t\t\t<td><a href='../customers-view.php'>View Customers</a></td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td><a href='../main.php'>Main Menu</a></td>\n\t\t\t</tr>\n\t\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 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 getOverdue($cusnum)
{
if (!isset($cusnum) or strlen($cusnum) < 1) {
return 0;
}
db_connect();
#get customer info ...
$get_cust = "SELECT * FROM customers WHERE cusnum = '{$cusnum}' LIMIT 1";
$run_cust = db_exec($get_cust) or errDie("Unable to get customer information.");
if (pg_numrows($run_cust) > 0) {
$cust_data = pg_fetch_array($run_cust);
} else {
return 0;
}
$to_month = date("m");
$to_date = date("Y-m-d");
$from_date = date("Y-m-d", mktime(0, 0, 0, date("m"), "01", date("Y")));
$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);
return $age120 + $age30 + $age60 + $age90;
// # Get the client's overdue period
// $sql = "SELECT cusnum,overdue,location FROM customers WHERE cusnum = '$cusnum' AND div = '".USER_DIV."'";
// $clntRs = db_exec($sql) or errDie("Unable to get clients from Cubit.");
// $cust = pg_fetch_array($clntRs);
//
// # Check type of age analisys
// if(div_isset("DEBT_AGE", "mon")){
// $overd = oageage($cust['cusnum'], ($cust['overdue']/30) - 1, $cust['location']);
// }else{
// $overd = oage($cust['cusnum'], ($cust['overdue'])- 1, $cust['location']);
// }
// return $overd;
}
