function load_query_results($tableKey = 'id', $tableValue = 0)
{
global $db, $report, $FieldListings;
if (!$tableValue) {
return false;
}
$sql = "select * from " . TABLE_JOURNAL_MAIN . " where id = " . $tableValue;
$result = $db->Execute($sql);
while (list($key, $value) = each($result->fields)) {
$this->{$key} = db_prepare_input($value);
}
$this->load_item_details($this->id);
$this->load_payment_details($this->id);
$this->load_account_details($this->bill_acct_id);
// convert particular values indexed by id to common name
if ($this->rep_id) {
$sql = "select short_name, contact_first, contact_last from " . TABLE_CONTACTS . " where id = " . $this->rep_id;
$result = $db->Execute($sql);
$this->rep_id = $result->fields['short_name'];
$this->rep_name = $result->fields['contact_first'] . ' ' . $result->fields['contact_last'];
} else {
$this->rep_id = '';
$this->rep_name = '';
}
$terms_date = calculate_terms_due_dates($this->post_date, $this->terms);
$this->payment_due_date = $terms_date['net_date'];
// $this->tax_authorities = 'tax_auths';
$this->balance_due = $this->total_amount - $this->total_paid;
$this->rounded_of = $this->total_amount - ($this->inv_subtotal_w_tax - $this->discount);
// sequence the results per Prefs[Seq]
$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 results
//echo 'line items = '; print_r($this->line_items); echo '<br />';
return $output;
}
<?php
if (ENABLE_MULTI_CURRENCY) {
echo '<tr><td colspan="9" class="fieldRequired"> ' . sprintf(GEN_PRICE_SHEET_CURRENCY_NOTE, $currencies->currencies[DEFAULT_CURRENCY]['title']) . '</td></tr>';
}
?>
<tr valign="top"><?php
echo $list_header;
?>
</tr>
</thead>
<tbody id="item_table" class="ui-widget-content">
<?php
// build the javascript constructor for creating each address object
$idx = 1;
while (!$query_result->EOF) {
$due_dates = calculate_terms_due_dates($query_result->fields['post_date'], $query_result->fields['terms'], 'AP');
if ($due_dates['net_date'] > $invoice_date && ($due_dates['early_date'] > $discount_date || $due_dates['discount'] == 0)) {
$query_result->MoveNext();
continue;
// skip if not within selected discount window
}
if ($invoice_date < $due_dates['net_date']) {
if ($discount_date < $due_dates['early_date']) {
$query_result->MoveNext();
continue;
// skip if not within selected discount window
}
}
$already_paid = fetch_partially_paid($query_result->fields['id']);
$amount_due = $query_result->fields['total_amount'] - $already_paid;
if ($query_result->fields['journal_id'] == 7) {
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;
}
function fill_paid_invoice_array($id, $account_id, $type = 'c')
{
// to build this data array, all current open invoices need to be gathered and then the paid part needs
// to be applied along with discounts taken by row.
global $db, $currencies;
$negate = JOURNAL_ID == 20 && $type == 'c' || JOURNAL_ID == 18 && $type == 'v' ? true : false;
// first read all currently open invoices and the payments of interest and put into an array
$sql = "select distinct so_po_item_ref_id from " . TABLE_JOURNAL_ITEM . " where ref_id = " . $id;
$result = $db->Execute($sql);
$paid_indeces = array();
while (!$result->EOF) {
if ($result->fields['so_po_item_ref_id']) {
$paid_indeces[] = $result->fields['so_po_item_ref_id'];
}
$result->MoveNext();
}
switch ($type) {
case 'c':
$search_journal = '(12, 13)';
break;
case 'v':
$search_journal = '(6, 7)';
break;
default:
return false;
}
$sql = "select id, journal_id, post_date, terms, purch_order_id, purchase_invoice_id, total_amount, gl_acct_id \r\n\t\tfrom " . TABLE_JOURNAL_MAIN . " \r\n\t\twhere (journal_id in " . $search_journal . " and closed = '0' and bill_acct_id = " . $account_id . ")";
if (sizeof($paid_indeces) > 0) {
$sql .= " or (id in (" . implode(',', $paid_indeces) . ") and closed = '0')";
}
$sql .= " order by post_date";
$result = $db->Execute($sql);
$open_invoices = array();
while (!$result->EOF) {
if ($result->fields['journal_id'] == 7 || $result->fields['journal_id'] == 13) {
$result->fields['total_amount'] = -$result->fields['total_amount'];
}
$result->fields['total_amount'] -= fetch_partially_paid($result->fields['id']);
$result->fields['description'] = $result->fields['purch_order_id'];
$result->fields['discount'] = '';
$result->fields['amount_paid'] = '';
$open_invoices[$result->fields['id']] = $result->fields;
$result->MoveNext();
}
// next read the record of interest and add/adjust open invoice array with amounts
$sql = "select id, ref_id, so_po_item_ref_id, gl_type, description, debit_amount, credit_amount, gl_account \r\n\t\tfrom " . TABLE_JOURNAL_ITEM . " where ref_id = " . $id;
$result = $db->Execute($sql);
while (!$result->EOF) {
$amount = $result->fields['debit_amount'] ? $result->fields['debit_amount'] : $result->fields['credit_amount'];
if ($negate) {
$amount = -$amount;
}
$index = $result->fields['so_po_item_ref_id'];
switch ($result->fields['gl_type']) {
case 'dsc':
// it's the discount field
$open_invoices[$index]['discount'] = $amount;
$open_invoices[$index]['amount_paid'] -= $amount;
break;
case 'chk':
case 'pmt':
// it's the payment field
$open_invoices[$index]['total_amount'] += $amount;
$open_invoices[$index]['description'] = $result->fields['description'];
$open_invoices[$index]['amount_paid'] = $amount;
break;
case 'ttl':
$payment_fields = $result->fields['description'];
// payment details
// payment details
default:
}
$result->MoveNext();
}
ksort($open_invoices);
$balance = 0;
$index = 0;
$item_list = array();
foreach ($open_invoices as $key => $line_item) {
// fetch some information about the invoice
$sql = "select id, post_date, terms, purchase_invoice_id, purch_order_id, gl_acct_id, waiting \r\n\t\t\tfrom " . TABLE_JOURNAL_MAIN . " where id = " . $key;
$result = $db->Execute($sql);
$due_dates = calculate_terms_due_dates($result->fields['post_date'], $result->fields['terms'], $type == 'v' ? 'AP' : 'AR');
if ($negate) {
$line_item['total_amount'] = -$line_item['total_amount'];
$line_item['discount'] = -$line_item['discount'];
$line_item['amount_paid'] = -$line_item['amount_paid'];
}
$balance += $line_item['total_amount'];
$item_list[] = array('id' => $result->fields['id'], 'waiting' => $result->fields['waiting'], 'purchase_invoice_id' => $result->fields['purchase_invoice_id'], 'purch_order_id' => $result->fields['purch_order_id'], 'percent' => $due_dates['discount'], 'early_date' => gen_spiffycal_db_date_short($due_dates['early_date']), 'net_date' => gen_spiffycal_db_date_short($due_dates['net_date']), 'total_amount' => $currencies->format($line_item['total_amount']), 'gl_acct_id' => $result->fields['gl_acct_id'], 'description' => $line_item['description'], 'discount' => $line_item['discount'] ? $currencies->format($line_item['discount']) : '', 'amount_paid' => $line_item['amount_paid'] ? $currencies->format($line_item['amount_paid']) : '');
$index++;
}
return array('balance' => $balance, 'payment_fields' => $payment_fields, 'invoices' => $item_list);
}
} else {
$messageStack->add(GL_ERROR_NEVER_POSTED, 'error');
}
$messageStack->add(GL_ERROR_NO_DELETE, 'error');
// if we are here, there was an error, reload page
$order = new objectInfo($_POST);
$order->post_date = gen_db_date($_POST['post_date']);
// fix the date to original format
break;
case 'pmt':
// for opening a sales/invoice directly from payment (POS like)
// fetch the journal_main information
$sql = "select id, shipper_code, bill_acct_id, bill_address_id, bill_primary_name, bill_contact, bill_address1, \n\t\tbill_address2, bill_city_town, bill_state_province, bill_postal_code, bill_country_code, bill_email, \n\t\tpost_date, terms, gl_acct_id, purchase_invoice_id, total_amount from " . TABLE_JOURNAL_MAIN . " \n\t\twhere id = " . $oID;
$result = $db->Execute($sql);
$account_id = $db->Execute("select short_name from " . TABLE_CONTACTS . " where id = " . $result->fields['bill_acct_id']);
$due_dates = calculate_terms_due_dates($result->fields['post_date'], $result->fields['terms'], 'AR');
$pre_paid = fetch_partially_paid($oID);
$order->bill_acct_id = $result->fields['bill_acct_id'];
$order->bill_primary_name = $result->fields['bill_primary_name'];
$order->bill_contact = $result->fields['bill_contact'];
$order->bill_address1 = $result->fields['bill_address1'];
$order->bill_address2 = $result->fields['bill_address2'];
$order->bill_city_town = $result->fields['bill_city_town'];
$order->bill_state_province = $result->fields['bill_state_province'];
$order->bill_postal_code = $result->fields['bill_postal_code'];
$order->bill_country_code = $result->fields['bill_country_code'];
$order->bill_email = $result->fields['bill_email'];
$order->id_1 = $result->fields['id'];
$order->inv_1 = $result->fields['purchase_invoice_id'];
$order->acct_1 = $result->fields['gl_acct_id'];
$order->early_1 = $due_dates['early_date'];
function load_query_results($tableKey = 'id', $tableValue = 0)
{
global $db, $FieldListings, $Prefs, $rw_xtra_jrnl_defs;
if (!$tableValue) {
return false;
}
$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, \r\n\t\t a.primary_name, a.contact, a.address1, a.address2, a.city_town, a.state_province, a.postal_code, \r\n\t\t\ta.country_code, a.telephone1, a.telephone2, a.telephone3, a.telephone4, a.email, a.website \r\n\t\t from " . TABLE_CONTACTS . " c inner join " . TABLE_ADDRESS_BOOK . " a on c.id = a.ref_id \r\n\t\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);
}
// calculate the starting balance
$dates = gen_build_sql_date($Prefs['datedefault'], $Prefs['datefield']);
$sql = "select sum(i.credit_amount) as credit, sum(i.debit_amount) as debit \r\n\t\t\tfrom " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n\t\t\twhere m.bill_acct_id = " . $this->bill_acct_id . " and m.post_date < '" . $dates['start_date'] . "' \r\n\t\t\t and m.journal_id in (6, 7, 12, 13) and i.gl_type = 'ttl'";
$result = $db->Execute($sql);
$this->prior_balance = $result->RecordCount() ? $result->fields['debit'] - $result->fields['credit'] : 0;
$sql = "select sum(i.credit_amount) as credit, sum(i.debit_amount) as debit \r\n\t\t\tfrom " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n\t\t\twhere m.bill_acct_id = " . $this->bill_acct_id . " and m.post_date < '" . $dates['start_date'] . "' \r\n\t\t\t and m.journal_id in (18, 20) and i.gl_type in ('pmt', 'chk')";
$result = $db->Execute($sql);
$this->prior_balance -= $result->RecordCount() ? $result->fields['credit'] - $result->fields['debit'] : 0;
// fetch journal history based on date criteria
$strDates = str_replace('[table1]', 'm', $dates['sql']);
$this->line_items = array();
$sql = "select m.post_date, m.journal_id, i.debit_amount, i.credit_amount, m.purchase_invoice_id, \r\n\t\t m.purch_order_id, i.gl_type \r\n\t\t from " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id \r\n\t\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 ('dsc', 'ttl') order by m.post_date";
$sql .= " and m.journal_id in (6, 7, 12, 13, 18, 20) and i.gl_type = 'ttl' 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 = $rw_xtra_jrnl_defs[$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'];
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' => $line_desc, 'post_date' => $result->fields['post_date'], 'debit_amount' => $debit, 'credit_amount' => $credit, 'purchase_invoice_id' => $result->fields['purchase_invoice_id'], 'purch_order_id' => $result->fields['purch_order_id'], 'due_date' => $terms_date['net_date']);
$this->statememt_total += $credit - $debit;
$result->MoveNext();
}
// convert particular values indexed by id to common name
if ($this->rep_id) {
$sql = "select short_name from " . TABLE_CONTACTS . " where id = " . $this->rep_id;
$result = $db->Execute($sql);
$this->rep_id = $result->fields['display_name'];
} else {
$this->rep_id = '';
}
$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;
}
//echo 'prior balance = ' . $this->prior_balance . ' and bal due = ' . $this->balance_due . '<br />';
$this->post_date = date(DATE_FORMAT);
// sequence the results per Prefs[Seq]
$output = array();
foreach ($FieldListings as $OneField) {
// check for a data field and build sql field list
if ($OneField['params']['index'] == 'Data') {
// then it's data field, include it
$output[] = $this->{$OneField}['params']['DataField'];
}
}
// return results
return $output;
}
private function get_all_open_invoices()
{
// to build this data array, all current open invoices need to be gathered and then the paid part needs
// to be applied along with discounts taken by row.
global $db, $currencies;
$sql = "select m.id as id, m.journal_id as journal_id, m.post_date as post_date, m.terms as terms, m.purch_order_id as purch_order_id, i.debit_amount as debit_amount, i.credit_amount as credit_amount,\n\t\t m.purchase_invoice_id as purchase_invoice_id, m.total_amount as total_amount, m.gl_acct_id as gl_acct_id, m.bill_acct_id as bill_acct_id, c.type as type, c.short_name as short_name, m.waiting as waiting \n\t\t from " . TABLE_JOURNAL_MAIN . " m join " . TABLE_CONTACTS . " c on m.bill_acct_id = c.id join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\n\t\t where journal_id in (6, 7, 12, 13) and closed = '0' and gl_type = 'ttl'";
$sql .= " order by m.post_date";
$result = $db->Execute($sql);
$open_invoices = array();
while (!$result->EOF) {
$result->fields['total_amount'] = $result->fields['debit_amount'] ? $result->fields['debit_amount'] : $result->fields['credit_amount'];
$result->fields['total_amount'] -= fetch_partially_paid($result->fields['id']);
if ($result->fields['journal_id'] == 7 || $result->fields['journal_id'] == 13) {
$result->fields['total_amount'] = -$result->fields['total_amount'];
}
$result->fields['description'] = $result->fields['purch_order_id'];
$result->fields['discount'] = '';
$result->fields['amount_paid'] = '';
$open_invoices[$result->fields['id']] = $result->fields;
$result->MoveNext();
}
ksort($open_invoices);
$balance = 0;
$index = 0;
foreach ($open_invoices as $key => $line_item) {
// fetch some information about the invoice
$due_dates = calculate_terms_due_dates($line_item['post_date'], $line_item['terms'], $line_item['type'] == 'v' ? 'AP' : 'AR');
$negate = $line_item['journal_id'] == 20 && $line_item['type'] == 'c' || $line_item['journal_id'] == 18 && $line_item['type'] == 'v' ? true : false;
if ($negate) {
$line_item['total_amount'] = -$line_item['total_amount'];
$line_item['discount'] = -$line_item['discount'];
$line_item['amount_paid'] = -$line_item['amount_paid'];
}
$balance += $line_item['total_amount'];
$this->open_inv[$line_item['bill_acct_id']][$line_item['id']] = array('id' => $line_item['id'], 'waiting' => $line_item['waiting'], 'purchase_invoice_id' => $line_item['purchase_invoice_id'], 'purch_order_id' => $line_item['purch_order_id'], 'percent' => $due_dates['discount'], 'post_date' => $line_item['post_date'], 'early_date' => $due_dates['early_date'], 'net_date' => $due_dates['net_date'], 'total_amount' => round($line_item['total_amount'], $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']), 'gl_acct_id' => $line_item['gl_acct_id'], 'description' => $line_item['description'], 'type' => $line_item['type'], 'short_name' => $line_item['short_name'], 'discount' => round($line_item['discount'], $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']), 'amount_paid' => round($line_item['amount_paid'], $currencies->currencies[DEFAULT_CURRENCY]['decimal_places']));
$index++;
}
}
