// | GNU General Public License for more details. |
// +-----------------------------------------------------------------+
// Path: /modules/phreebooks/pages/popup_status/pre_process.php
//
$security_level = validate_user(0, true);
/************** include page specific files *********************/
gen_pull_language('contacts');
require_once DIR_FS_WORKING . 'functions/phreebooks.php';
/************** page specific initialization *************************/
$id = (int) $_GET['id'];
/*************** Act on the action request *************************/
// Load the customer status
$customer = $db->Execute("select c.type, c.inactive, c.special_terms, a.notes \n from " . TABLE_CONTACTS . " c inner join " . TABLE_ADDRESS_BOOK . " a on c.id = a.ref_id \n where c.id = " . $id . " and a.type like '%m'");
$notes = $customer->fields['notes'] ? str_replace(chr(10), "<br />", $customer->fields['notes']) : ' ';
$type = $customer->fields['type'] == 'v' ? 'AP' : 'AR';
$new_data = calculate_aging($id, $customer->fields['type'], $customer->fields['special_terms']);
// set the customer/vendor status in order of importance
if ($customer->fields['inactive']) {
$inactive_flag = 'class="ui-state-error"';
$status_text = TEXT_INACTIVE;
} elseif ($new_data['past_due'] > 0) {
$inactive_flag = 'class="ui-state-highlight"';
$status_text = ACT_HAS_PAST_DUE_AMOUNT;
} elseif ($new_data['total'] > $new_data['credit_limit']) {
$inactive_flag = 'class="ui-state-highlight"';
$status_text = ACT_OVER_CREDIT_LIMIT;
} else {
$inactive_flag = 'class="ui-state-active"';
$status_text = ACT_GOOD_STANDING;
}
/***************** prepare to display templates *************************/
function load_query_results($tableKey = 'id', $tableValue = 0)
{
global $db, $FieldListings, $report;
if (!$tableValue) {
return false;
}
$today = date('Y-m-d');
$this->bill_acct_id = $tableValue;
// fetch the main contact information, only one record
$sql = "select c.id, c.type, c.short_name, c.special_terms, \n\t\ta.primary_name, a.contact, a.address1, a.address2, a.city_town, a.state_province, a.postal_code, \n\t\ta.country_code, a.telephone1, a.telephone2, a.telephone3, a.telephone4, a.email, a.website \n\t from " . TABLE_CONTACTS . " c inner join " . TABLE_ADDRESS_BOOK . " a on c.id = a.ref_id \n\t where c.id = " . $this->bill_acct_id . " and a.type like '%m'";
$result = $db->Execute($sql);
while (list($key, $value) = each($result->fields)) {
$this->{$key} = db_prepare_input($value);
}
// Load the prior balance and aging, first aging
$result = calculate_aging($this->bill_acct_id, $result->fields['type'], $result->fields['special_terms']);
$this->balance_0 = $result['balance_0'];
$this->balance_30 = $result['balance_30'];
$this->balance_60 = $result['balance_60'];
$this->balance_90 = $result['balance_90'];
// $this->total = $result['total'];
// $this->past_due = $result['past_due'];
// $this->credit_limit = $result['credit_limit'];
// $this->terms_lang = $result['terms_lang'];
// now prior balance
$dates = gen_build_sql_date($report->datedefault, $report->datefield);
$sql = "select m.id, (i.debit_amount - i.credit_amount) as balance \n\t\tfrom " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id \n\t\twhere m.bill_acct_id = " . $this->bill_acct_id . " \n\t\tand m.post_date < '" . $dates['start_date'] . "' \n\t\tand (m.closed_date >= '" . $dates['start_date'] . "' or m.closed = '0') \n\t\tand m.journal_id in (6, 7, 12, 13) and i.gl_type in ('ttl', 'dsc')";
$result = $db->Execute($sql);
$prior_balance = 0;
$partials = array();
while (!$result->EOF) {
$prior_balance += $result->fields['balance'];
$partials[] = $result->fields['id'];
$result->MoveNext();
}
if (sizeof($partials) > 0) {
$sql = "select sum(i.debit_amount - i.credit_amount) as payment\n\t\tfrom " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id \n\t\twhere i.so_po_item_ref_id in (" . implode(',', $partials) . ") \n\t\tand m.post_date < '" . $dates['start_date'] . "' \n\t\tand m.journal_id in (18, 20) and i.gl_type = 'pmt'";
$result = $db->Execute($sql);
$this->prior_balance = $prior_balance + $result->fields['payment'];
} else {
$this->prior_balance = $prior_balance;
}
$strDates = str_replace('post_date', 'm.post_date', $dates['sql']);
$this->line_items = array();
$sql = "select m.post_date, m.journal_id, m.terms, i.debit_amount, i.credit_amount, m.purchase_invoice_id, \n\t m.purch_order_id, i.gl_type \n\t from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id \n\t where m.bill_acct_id = " . $this->bill_acct_id;
if ($strDates) {
$sql .= " and " . $strDates;
}
$sql .= " and m.journal_id in (6, 7, 12, 13, 18, 20) and i.gl_type in ('ttl', 'dsc') order by m.post_date";
$result = $db->Execute($sql);
$this->statememt_total = 0;
while (!$result->EOF) {
$reverse = in_array($result->fields['journal_id'], array(6, 7, 12, 13)) ? true : false;
$line_desc = defined('GEN_ADM_TOOLS_J' . str_pad($result->fields['journal_id'], 2, '0', STR_PAD_LEFT)) ? constant('GEN_ADM_TOOLS_J' . str_pad($result->fields['journal_id'], 2, '0', STR_PAD_LEFT)) : $result->fields['journal_id'];
$terms_date = calculate_terms_due_dates($result->fields['post_date'], $this->special_terms);
$credit = $reverse ? $result->fields['debit_amount'] : $result->fields['credit_amount'];
$debit = $reverse ? $result->fields['credit_amount'] : $result->fields['debit_amount'];
$ref_only = $result->fields['gl_type'] == 'dsc' && $reverse ? true : false;
// show discount as reference only
if (in_array($result->fields['journal_id'], array(7, 13)) || $result->fields['gl_type'] == 'dsc') {
// special case for credit memos and discounts
$temp = $debit;
$debit = -$credit;
$credit = -$temp;
if ($result->fields['gl_type'] == 'dsc') {
$line_desc = TEXT_DISCOUNT;
}
}
$this->line_items[] = array('journal_desc' => $ref_only ? 'Order Discount (Ref Only)' : $line_desc, 'purchase_invoice_id' => $result->fields['purchase_invoice_id'], 'purch_order_id' => $result->fields['purch_order_id'], 'post_date' => $result->fields['post_date'], 'due_date' => $terms_date['net_date'], 'credit_amount' => $ref_only ? -$credit : $credit, 'debit_amount' => $ref_only ? -$debit : $debit);
if (!$ref_only) {
$this->statememt_total += $credit - $debit;
}
$result->MoveNext();
}
$this->balance_due = $this->prior_balance + $this->statememt_total;
if ($this->type == 'v') {
// invert amount for vendors for display purposes
$this->prior_balance = -$this->prior_balance;
$this->balance_due = -$this->balance_due;
}
$this->post_date = date(DATE_FORMAT);
// sequence the results
$output = array();
foreach ($report->fieldlist as $OneField) {
// check for a data field and build sql field list
if ($OneField->type == 'Data') {
// then it's data field, include it
$field = $OneField->boxfield[0]->fieldname;
$output[] = $this->{$field};
}
}
return $output;
}