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 BuildSeq($ReportID, $Prefs, $delivery_method = 'D')
{
// for forms only - Sequential mode
global $db, $messageStack;
global $FieldListings, $FieldValues, $posted_currencies;
$output = array();
// first fetch all the fields we need to display
$FieldListings = '';
$sql = "select seqnum, params from " . TABLE_REPORT_FIELDS . " \r\n\t\twhere reportid = " . $ReportID . " and entrytype = 'fieldlist' and visible = 1\r\n\t\torder by seqnum";
$result = $db->Execute($sql);
while (!$result->EOF) {
$result->fields['params'] = unserialize($result->fields['params']);
$FieldListings[] = $result->fields;
$result->MoveNext();
}
// check for at least one field selected to show
if (!$FieldListings) {
// No fields are checked to show, that's bad
$messageStack->add(RW_RPT_NOROWS, 'caution');
return;
}
// Let's build the sql field list for the general data fields (not totals, blocks or tables)
$strField = '';
$index = 0;
// index each field to allow one field to be used multiple times since $db->Execute returns assoc array
foreach ($FieldListings as $OneField) {
// check for a data field and build sql field list
if ($OneField['params']['index'] == 'Data' || $OneField['params']['index'] == 'BarCode') {
// then it's data field make sure it's not empty
if ($OneField['params']['DataField'] != '') {
$strField .= $OneField['params']['DataField'] . ' as d' . $index . ', ';
$index++;
} else {
// the field is empty, bad news, error and exit
$messageStack->add(RW_RPT_EMPTYFIELD . $OneField['seqnum'], 'error');
return;
}
}
}
$strField = substr($strField, 0, -2);
// strip the extra comma, space and continue
// fetch the sort order and add to group by string to finish ORDER BY string
$strSort = $strGroup;
if (is_array($Prefs['SortListings'])) {
while ($FieldValues = array_shift($Prefs['SortListings'])) {
if ($FieldValues['params']['default'] == '1') {
// then it's the sort by field match
if ($strSort == '') {
$strSort .= $FieldValues['fieldname'];
} else {
$strSort .= ', ' . $FieldValues['fieldname'];
}
$Prefs['filterdesc'] .= RW_RPT_SORTBY . ' ' . $FieldValues['displaydesc'] . '; ';
break;
}
}
}
// fetch date filter info (skip if criteria was passed to generate function)
$strDate = '';
if (!isset($Prefs['PassedCrit'])) {
$dates = gen_build_sql_date($Prefs['datedefault'], $Prefs['datefield']);
$strDate = $dates['sql'];
}
// Fetch the Criteria
$criteria = build_criteria($Prefs['CritListings']);
$strCrit = $criteria['sql'];
if (isset($Prefs['PassedCrit'])) {
// add the passed criteria to the default criteria
for ($i = 0; $i < count($Prefs['PassedCrit']); $i++) {
$temp = explode(':', $Prefs['PassedCrit'][$i]);
switch (count($temp)) {
case 2:
// single value passed (assume equal to)
if ($strCrit) {
$strCrit .= ' and ';
}
$strCrit .= $temp[0] . " = '" . $temp[1] . "'";
break;
case 3:
// range passed (assume between inclusive)
if ($strCrit) {
$strCrit .= ' and ';
}
$strCrit .= $temp[0] . " >= '" . $temp[1] . "' and " . $temp[0] . " <= '" . $temp[2] . "'";
break;
default:
// error in passed parameters
}
}
}
// fetch the tables to query
$tables = array($Prefs['table1'], $Prefs['table2'], $Prefs['table3'], $Prefs['table4'], $Prefs['table5'], $Prefs['table6']);
$sqlTable = DB_PREFIX . $Prefs['table1'];
if ($Prefs['table2']) {
$sqlTable .= ' inner join ' . DB_PREFIX . $Prefs['table2'] . ' on ' . $Prefs['table2criteria'];
}
if ($Prefs['table3']) {
$sqlTable .= ' inner join ' . DB_PREFIX . $Prefs['table3'] . ' on ' . $Prefs['table3criteria'];
}
if ($Prefs['table4']) {
$sqlTable .= ' inner join ' . DB_PREFIX . $Prefs['table4'] . ' on ' . $Prefs['table4criteria'];
}
if ($Prefs['table5']) {
$sqlTable .= ' inner join ' . DB_PREFIX . $Prefs['table5'] . ' on ' . $Prefs['table5criteria'];
}
if ($Prefs['table6']) {
$sqlTable .= ' inner join ' . DB_PREFIX . $Prefs['table6'] . ' on ' . $Prefs['table6criteria'];
}
// Build query string and execute
$sqlCrit = '';
if ($strCrit && $strDate) {
$sqlCrit .= $strDate . ' and ' . $strCrit;
}
if (!$strCrit && $strDate) {
$sqlCrit .= $strDate;
}
if ($strCrit && !$strDate) {
$sqlCrit .= $strCrit;
}
// We now have the sql, find out how many groups in the query (to determine the number of forms)
$PageBreakField = $Prefs['formbreakfield'];
$form_field_list = $Prefs['filenamesource'] == '' ? $PageBreakField : $PageBreakField . ', ' . $Prefs['filenamesource'];
$sql = 'select ' . $form_field_list . ' from ' . $sqlTable;
if ($sqlCrit) {
$sql .= ' where ' . $sqlCrit;
}
$sql .= ' group by ' . $PageBreakField;
if ($strSort) {
$sql .= ' order by ' . $strSort;
}
// replace table aliases (needed for DB_PREFIX values <> '')
$sql = GenReplaceTables($sql, $tables);
// execute sql to see if we have data
//echo 'sql = ' . $sql . '<br />';
$result = $db->Execute($sql);
if (!$result->RecordCount()) {
$messageStack->add(RW_RPT_NOROWS, 'caution');
return;
}
// set the filename for download or email
if ($Prefs['filenameprefix'] || $Prefs['filenamesource']) {
$output['filename'] = $Prefs['filenameprefix'] . $result->fields[strip_tablename($Prefs['filenamesource'])] . '.pdf';
} else {
$output['filename'] = ReplaceNonAllowedCharacters($Prefs['description']) . '.pdf';
}
// create an array for each form
while (!$result->EOF) {
$FormPageID[] = $result->fields[strip_tablename($PageBreakField)];
$result->MoveNext();
}
// retrieve the company information
foreach ($FieldListings as $key => $SingleObject) {
if ($SingleObject['params']['index'] == 'CDta') {
$FieldListings[$key]['params']['TextField'] = constant($SingleObject['params']['DataField']);
}
if ($SingleObject['params']['index'] == 'CBlk') {
if (!$SingleObject['params']['Seq']) {
$messageStack->add(RW_RPT_EMPTYFIELD . $SingleObject['seqnum'], 'error');
return;
}
$TextField = '';
foreach ($SingleObject['params']['Seq'] as $OneField) {
$TextField .= AddSep(constant($OneField['TblField']), $OneField['Processing']);
}
$FieldListings[$key]['params']['TextField'] = $TextField;
}
}
// patch for special_reports (forms) where the data file is generated externally from the standard class
if ($Prefs['special_report']) {
$temp = explode(':', $Prefs['special_report']);
$form_class = $temp[1];
if (file_exists(DIR_FS_MY_FILES . 'custom/reportwriter/classes/' . $form_class . '.php')) {
$success = (include DIR_FS_MY_FILES . 'custom/reportwriter/classes/' . $form_class . '.php');
} elseif (file_exists(DIR_FS_MODULES . 'reportwriter/classes/' . $form_class . '.php')) {
$success = (include DIR_FS_MODULES . 'reportwriter/classes/' . $form_class . '.php');
} else {
$success = false;
}
if (!$success) {
$messageStack->add('Special form class: ' . $form_class . ' was called but could not be found!', 'error');
return;
}
}
// Generate a form for each group element
$output = NULL;
foreach ($FormPageID as $formNum => $Fvalue) {
// find the single line data from the query for the current form page
$TrailingSQL = " from " . $sqlTable . " where " . ($sqlCrit ? $sqlCrit . " AND " : '') . $PageBreakField . " = '" . $Fvalue . "'";
if ($Prefs['special_report']) {
$special_form = new $form_class();
$FieldValues = $special_form->load_query_results($PageBreakField, $Fvalue);
} else {
$sql = "select " . $strField . $TrailingSQL;
$sql = GenReplaceTables($sql, $tables);
// replace table aliases (needed for DB_PREFIX values <> '')
$result = $db->Execute($sql);
$FieldValues = $result->fields;
}
// load the posted currency values
$posted_currencies = array();
if (ENABLE_MULTI_CURRENCY && strpos($sqlTable, TABLE_JOURNAL_MAIN) !== false) {
$sql = "select currencies_code, currencies_value " . $TrailingSQL;
$sql = GenReplaceTables($sql, $tables);
$result = $db->Execute($sql);
$posted_currencies = array('currencies_code' => $result->fields['currencies_code'], 'currencies_value' => $result->fields['currencies_value']);
} else {
$posted_currencies = array('currencies_code' => DEFAULT_CURRENCY, 'currencies_value' => 1);
}
foreach ($FieldListings as $key => $SingleObject) {
switch ($SingleObject['params']['index']) {
default:
$value = ProcessData($SingleObject['params']['TextField'], $SingleObject['params']['Processing']);
$output .= formatReceipt($value, $SingleObject['params']['BoxWidth'], $SingleObject['params']['FontAlign']);
break;
case 'Data':
$value = ProcessData(array_shift($FieldValues), $SingleObject['params']['Processing']);
$output .= formatReceipt($value, $SingleObject['params']['BoxWidth'], $SingleObject['params']['FontAlign']);
break;
case 'TBlk':
if (!$SingleObject['params']['Seq']) {
$messageStack->add(RW_RPT_EMPTYFIELD . $SingleObject['seqnum'], 'error');
return;
}
if ($Prefs['special_report']) {
$TextField = $special_form->load_text_block_data($SingleObject['params']['Seq']);
} else {
$strTxtBlk = '';
// Build the fieldlist
foreach ($SingleObject['params']['Seq'] as $OneField) {
$strTxtBlk .= $OneField['TblField'] . ', ';
}
$strTxtBlk = substr($strTxtBlk, 0, -2);
$sql = "select " . $strTxtBlk . $TrailingSQL;
$sql = GenReplaceTables($sql, $tables);
$result = $db->Execute($sql);
$TxtBlkValues = $result->fields;
$TextField = '';
$t = 0;
foreach ($TxtBlkValues as $Temp) {
$TextField .= AddSep($Temp, $SingleObject['params']['Seq'][$t]['Processing']);
$t++;
// mapping counter to separator directive
}
}
$FieldListings[$key]['params']['TextField'] = $TextField;
$output .= $FieldListings[$key]['params']['TextField'] . "\n";
break;
case 'Tbl':
if (!$SingleObject['params']['Seq']) {
$messageStack->add(RW_RPT_EMPTYFIELD . $SingleObject['seqnum'], 'error');
return;
}
// Build the sql
$tblField = '';
$tblHeading = array();
foreach ($SingleObject['params']['Seq'] as $TableField) {
$tblHeading[] = $TableField['TblDesc'];
}
if ($Prefs['special_report']) {
$SingleObject['params']['Data'] = $special_form->load_table_data($SingleObject['params']['Seq']);
} else {
foreach ($SingleObject['params']['Seq'] as $TableField) {
$tblField .= $TableField['TblField'] . ', ';
}
$tblField = substr($tblField, 0, -2);
// remove the last two chars (comma and space)
$sql = "select " . $tblField . $TrailingSQL;
$sql = GenReplaceTables($sql, $tables);
$result = $db->Execute($sql);
while (!$result->EOF) {
$SingleObject['params']['Data'][] = $result->fields;
$result->MoveNext();
}
}
// array_unshift($SingleObject['params']['Data'], $tblHeading); // set the first data element to the headings
$StoredTable = $SingleObject['params'];
foreach ($SingleObject['params']['Data'] as $key => $value) {
$temp = array();
$Col = 0;
foreach ($value as $data_key => $data_element) {
// if ($Params['Seq'][$Col]['TblShow']) {
$value = ProcessData($data_element, $SingleObject['params']['Seq'][$data_key]['Processing']);
$temp[] .= formatReceipt($value, $SingleObject['params']['Seq'][$data_key]['TblColWidth'], $SingleObject['params']['Seq'][$data_key]['FontAlign'], true);
// }
$Col++;
}
$output .= implode("", $temp) . "\n";
}
break;
case 'TDup':
if (!$StoredTable) {
$messageStack->add(RW_RPT_EMPTYTABLE . $SingleObject['seqnum'], 'error');
return;
}
// TBD NEED TO FINISH THIS
break;
case 'Ttl':
if (!$SingleObject['params']['Seq']) {
$messageStack->add(RW_RPT_EMPTYFIELD . $SingleObject['seqnum'], 'error');
return;
}
if ($Prefs['special_report']) {
$FieldValues = $special_form->load_total_results($SingleObject['params']);
} else {
$ttlField = '';
foreach ($SingleObject['params']['Seq'] as $Temp) {
$ttlField .= $Temp . '+';
}
$sql = "select sum(" . substr($ttlField, 0, -1) . ") as form_total" . $TrailingSQL;
$sql = GenReplaceTables($sql, $tables);
$result = $db->Execute($sql);
$FieldValues = $result->fields['form_total'];
}
$value = ProcessData($FieldValues, $SingleObject['params']['Processing']);
$output .= formatReceipt($value, $SingleObject['params']['BoxWidth'], $SingleObject['params']['FontAlign']);
break;
}
}
// set the printed flag field if provided
if ($Prefs['setprintedflag']) {
$tmp = GenReplaceTables($PageBreakField, $tables);
$id_field = $tmp;
$tmp = GenReplaceTables($Prefs['setprintedflag'], $tables);
$temp = explode('.', $tmp);
if (sizeof($temp) == 2) {
// need the table name and field name
$sql = "update " . $temp[0] . " set " . $temp[1] . " = " . $temp[1] . " + 1 where " . $id_field . " = '" . $Fvalue . "'";
$db->Execute($sql);
}
}
$output .= "\n" . "\n";
// page break
}
$FileSize = strlen($output);
header("Content-type: application/text");
header("Content-disposition: attachment; filename=" . $Prefs['description'] . ".txt; size=" . $FileSize);
header('Pragma: cache');
header('Cache-Control: public, must-revalidate, max-age=0');
header('Connection: close');
header('Expires: ' . date('r', time() + 60 * 60));
header('Last-Modified: ' . date('r', time()));
print $output;
exit;
}
function BuildSQL($report)
{
// for reports only
$strField = array();
$index = 0;
for ($i = 0; $i < sizeof($report->fieldlist); $i++) {
if ($report->fieldlist[$i]->visible) {
$strField[] = prefixTables($report->fieldlist[$i]->fieldname) . " AS c" . $index;
$index++;
}
}
if (!$strField) {
return array('level' => 'error', 'message' => PHREEFORM_NOROWS);
}
$strField = implode(', ', $strField);
$filterdesc = PHREEFORM_RPTFILTER;
// Initialize the filter display string
//fetch the groupings and build first level of SORT BY string (for sub totals)
$strGroup = NULL;
for ($i = 0; $i < sizeof($report->grouplist); $i++) {
if ($report->grouplist[$i]->default) {
$strGroup .= prefixTables($report->grouplist[$i]->fieldname);
$filterdesc .= PHREEFORM_GROUPBY . ' ' . $report->grouplist[$i]->description . '; ';
break;
}
}
// fetch the sort order and add to group by string to finish ORDER BY string
$strSort = $strGroup;
for ($i = 0; $i < sizeof($report->sortlist); $i++) {
if ($report->sortlist[$i]->default) {
$strSort .= ($strSort != '' ? ', ' : '') . prefixTables($report->sortlist[$i]->fieldname);
$filterdesc .= PHREEFORM_SORTBY . ' ' . $report->sortlist[$i]->description . '; ';
break;
}
}
// fetch date filter info
$dates = gen_build_sql_date($report->datedefault, prefixTables($report->datefield));
$strDate = $dates['sql'];
if ($dates['description']) {
$filterdesc .= $dates['description'];
}
// update the filter description string
// Fetch the Criteria
$criteria = build_criteria($report);
$strCrit = $criteria['sql'];
if ($criteria['description']) {
$filterdesc .= PHREEFORM_CRITBY . ' ' . $criteria['description'] . '; ';
}
// fetch the tables to query
$sqlTable = '';
foreach ($report->tables as $table) {
if (isset($table->relationship)) {
if (!$table->joinopt) {
$table->joinopt = 'JOIN';
}
$sqlTable .= ' ' . $table->joinopt . ' ' . DB_PREFIX . $table->tablename . ' ON ' . prefixTables($table->relationship);
} else {
$sqlTable .= DB_PREFIX . $table->tablename;
}
}
// Build query string
$sql = 'SELECT ' . $strField . ' FROM ' . $sqlTable;
if ($strCrit && $strDate) {
$sql .= ' WHERE ' . $strDate . ' AND ' . $strCrit;
}
if (!$strCrit && $strDate) {
$sql .= ' WHERE ' . $strDate;
}
if ($strCrit && !$strDate) {
$sql .= ' WHERE ' . $strCrit;
}
if ($strSort) {
$sql .= ' ORDER BY ' . $strSort;
}
//echo 'sql = '; print_r($sql); echo '<br><br>'; exit();
//echo 'period = '; print_r($report->period); echo '<br><br>';
return array('level' => 'success', 'data' => $sql, 'description' => $filterdesc);
}
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;
}
function load_report_data($Prefs, $Seq, $sql = '', $GrpField = '')
{
global $db;
// prepare the sql by temporarily replacing calculated fields with real fields
$sql_fields = substr($sql, strpos($sql, 'select ') + 7, strpos($sql, ' from ') - 7);
$this->sql_field_array = explode(', ', $sql_fields);
for ($i = 0; $i < count($this->sql_field_array); $i++) {
$this->sql_field_karray['c' . $i] = substr($this->sql_field_array[$i], 0, strpos($this->sql_field_array[$i], ' '));
}
$sql = $this->replace_special_fields($sql);
//echo 'sql = ' . $sql . '<br />'; exit;
//echo 'Prefs = '; print_r($Prefs); echo '<br />';
$result = $db->Execute($sql);
if ($result->RecordCount() == 0) {
return false;
}
// No data so bail now
// Generate the output data array
/*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/
$this->gl_account = $Prefs['fromvalue1'];
// assumes that the gl account is the first criteria, equal to
if (!$this->gl_account) {
return false;
}
// No gl account so bail now
$dates = gen_build_sql_date($Prefs['datedefault'], $Prefs['datefield']);
$post_date = $dates['start_date'];
$period = gen_calculate_period($post_date, $hide_error = true);
if (!$period) {
$period = 1;
}
$temp = $db->Execute("select beginning_balance from " . TABLE_CHART_OF_ACCOUNTS_HISTORY . " \r\n\t\twhere account_id = '" . $this->gl_account . "' and period = " . $period);
$beginning_balance = $temp->fields['beginning_balance'];
// load the payments and deposits for the current period
$sql = "select sum(i.debit_amount) as deposit, sum(i.credit_amount) as withdrawal\r\n\t\tfrom " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i on m.id = i.ref_id\r\n\t\twhere m.period = " . $period . " and m.post_date < '" . $post_date . "' and i.gl_account = '" . $this->gl_account . "'";
$temp = $db->Execute($sql);
$this->beginning_balance = $beginning_balance + $temp->fields['deposit'] - $temp->fields['withdrawal'];
$this->current_balance = $this->beginning_balance;
/*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/
$RowCnt = 0;
// Row counter for output data
$ColCnt = 1;
$GrpWorking = false;
while (!$result->EOF) {
$myrow = $result->fields;
// Check to see if a total row needs to be displayed
if (isset($GrpField)) {
// we're checking for group totals, see if this group is complete
if ($myrow[$GrpField] != $GrpWorking && $GrpWorking !== false) {
// it's a new group so print totals
$OutputArray[$RowCnt][0] = 'g:' . $GrpWorking;
foreach ($Seq as $offset => $TotalCtl) {
$OutputArray[$RowCnt][$offset + 1] = ProcessData($TotalCtl['grptotal'], $TotalCtl['processing']);
$Seq[$offset]['grptotal'] = '';
// reset the total
}
$RowCnt++;
// go to next row
}
$GrpWorking = $myrow[$GrpField];
// set to new grouping value
}
$OutputArray[$RowCnt][0] = 'd';
// let the display class know its a data element
//echo 'orig myrow = '; print_r($myrow); echo '<br /><br />';
$myrow = $this->replace_data_fields($myrow, $Seq);
//echo 'new myrow = '; print_r($myrow); echo '<br /><br />';
// build the new balance
foreach ($Seq as $key => $TableCtl) {
// calculate the new balance
//echo 'key = ' . $key . ' and fieldname = '; print_r($Prefs['FieldListings'][$key+1]['fieldname']); echo '<br />';
if ($Prefs['FieldListings'][$key + 1]['fieldname'] == '[table2].debit_amount') {
$this->beginning_balance += $myrow[$TableCtl['fieldname']];
}
if ($Prefs['FieldListings'][$key + 1]['fieldname'] == '[table2].credit_amount') {
$this->beginning_balance -= $myrow[$TableCtl['fieldname']];
}
}
foreach ($Seq as $key => $TableCtl) {
//
// insert data into output array and set to next column
$OutputArray[$RowCnt][$ColCnt] = ProcessData($myrow[$TableCtl['fieldname']], $TableCtl['processing']);
$ColCnt++;
if ($TableCtl['total']) {
// add to the running total if need be
$Seq[$key]['grptotal'] += $myrow[$TableCtl['fieldname']];
$Seq[$key]['rpttotal'] += $myrow[$TableCtl['fieldname']];
}
}
$RowCnt++;
$ColCnt = 1;
$result->MoveNext();
}
// add an extra line at the bottom with the newest balance
$OutputArray[$RowCnt][0] = 'd';
// let the display class know its a data element
foreach ($Seq as $key => $TableCtl) {
// calculate the new balance
if ($Prefs['FieldListings'][$key + 1]['fieldname'] == 'beg_balance') {
$OutputArray[$RowCnt][$ColCnt] = ProcessData($this->beginning_balance, $TableCtl['processing']);
} else {
$OutputArray[$RowCnt][$ColCnt] = '';
}
$ColCnt++;
}
$RowCnt++;
$ColCnt = 1;
if ($GrpWorking !== false) {
// if we collected group data show the final group total
$OutputArray[$RowCnt][0] = 'g:' . $GrpWorking;
foreach ($Seq as $TotalCtl) {
$OutputArray[$RowCnt][$ColCnt] = $TotalCtl['total'] == '1' ? ProcessData($TotalCtl['grptotal'], $TotalCtl['processing']) : ' ';
$ColCnt++;
}
$RowCnt++;
$ColCnt = 1;
}
// see if we have a total to send
$ShowTotals = false;
foreach ($Seq as $TotalCtl) {
if ($TotalCtl['total'] == '1') {
$ShowTotals = true;
}
}
if ($ShowTotals) {
$OutputArray[$RowCnt][0] = 'r:' . $Prefs['description'];
foreach ($Seq as $TotalCtl) {
if ($TotalCtl['total']) {
$OutputArray[$RowCnt][$ColCnt] = ProcessData($TotalCtl['rpttotal'], $TotalCtl['processing']);
} else {
$OutputArray[$RowCnt][$ColCnt] = ' ';
}
$ColCnt++;
}
}
// echo 'output array = '; print_r($OutputArray); echo '<br />'; exit();
return $OutputArray;
}
if ($result) {
$criteria[] = $result;
}
$amt_fields = array('m.total_amount', 'i.debit_amount', 'i.credit_amount');
$result = build_search_sql($amt_fields, $_GET['amount_id'], $_GET['amount_id_from'], $_GET['amount_id_to']);
if ($result) {
$criteria[] = $result;
}
$gl_acct_fields = array('m.gl_acct_id', 'i.gl_account');
$result = build_search_sql($gl_acct_fields, $_GET['gl_acct_id'], $_GET['gl_acct_id_from'], $_GET['gl_acct_id_to']);
if ($result) {
$criteria[] = $result;
}
$main_fields = array('m.id');
$result = build_search_sql($main_fields, $_GET['main_id'], $_GET['main_id_from'], $_GET['main_id_to']);
if ($result) {
$criteria[] = $result;
}
$date_prefs = array('fieldname' => 'm.post_date', 'params' => $_GET['date_id'] . ':' . $_GET['date_from'] . ':' . $_GET['date_to']);
$temp = gen_build_sql_date($date_prefs['params'], $date_prefs['fieldname']);
if ($temp['sql']) {
$criteria[] = '(' . $temp['sql'] . ')';
}
$crit = $criteria ? " where " . implode(' and ', $criteria) : '';
$query_raw = "select distinct m.id, m.journal_id, m.post_date, m.description, m.total_amount, \r\n\tm.purchase_invoice_id, m.bill_primary_name, m.bill_acct_id \r\n\tfrom " . TABLE_JOURNAL_MAIN . " m inner join " . TABLE_JOURNAL_ITEM . " i \r\n\ton m.id = i.ref_id " . $crit . " order by {$disp_order}";
$query_result = $db->Execute($query_raw);
$query_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS, $query_raw, $query_numrows);
$query_result = $db->Execute($query_raw);
$include_template = 'template_main.php';
// include display template (required)
define('PAGE_TITLE', HEADING_TITLE_SEARCH_INFORMATION);
function ie_export_data($prefs, $params, $criteria, $options)
{
global $db, $messageStack;
global $qualifiers, $delimiters, $address_tables;
if ($prefs['table_name'] == TABLE_CONTACTS) {
$use_address_book = true;
switch ($prefs['group_id']) {
case 'ar':
$account_type = 'c';
break;
// customers
// customers
case 'ap':
$account_type = 'v';
break;
// vendors
// vendors
case 'hr':
$account_type = 'e';
// employees
}
} else {
$use_address_book = false;
}
// build the mapping arrays to point field names to proper position in export order
$data = array();
$element_processing = array();
if ($use_address_book) {
$mail[0] = array();
for ($i = 0; $i < MAX_NUM_ADDRESSES; $i++) {
$ship[$i] = array();
$bill[$i] = array();
}
}
$index = 0;
foreach ($params as $field) {
if ($field['show'] && ($field['mode'] == 'e' || $field['mode'] == 'b')) {
if ($use_address_book && substr($field['field'], 0, 5) == 'mail ') {
$temp = explode(' ', $field['field']);
$mail[0][$temp[1]] = $index;
} elseif ($use_address_book && substr($field['field'], 0, 5) == 'ship ') {
$temp = explode(' ', $field['field']);
$ship[$temp[2] - 1][$temp[1]] = $index;
} elseif ($use_address_book && substr($field['field'], 0, 5) == 'bill ') {
$temp = explode(' ', $field['field']);
$bill[$temp[2] - 1][$temp[1]] = $index;
} else {
$data[$field['field']] = $index;
}
$element_processing[$index] = $field['proc'];
// needed for processing of each ouput value
$index++;
}
}
// fetch the delimiters and text qualifiers
$delimiter = ie_fetch_delimiter($options['delimiter']);
$qualifier = ie_fetch_qualifier($options['qualifier']);
// ready to process the export file
$output = '';
if ($options['exp_headings']) {
$output_line = array();
foreach ($params as $field) {
if ($field['show']) {
$output_line[] = $field['name'];
}
}
$output .= ie_implode($output_line, $delimiter, $qualifier) . chr(10);
}
// build export criteria
$criteria_list = array();
$description_list = array();
if (is_array($criteria)) {
foreach ($criteria as $filter) {
$filter['name'] = ie_find_field_name($filter['cfield'], $params);
$address_test = substr($filter['cfield'], 0, 5);
if ($address_test == 'mail ' || $address_test == 'ship ' || $address_test == 'bill ') {
$temp = explode(' ', $filter['cfield']);
$filter['cfield'] = 'a.' . $temp[1];
} else {
$filter['cfield'] = 'd.' . $filter['cfield'];
}
$crit_data = '';
$crit_desc = '';
switch ($filter['crit']) {
// based on the date choice selected
default:
case "all":
case "stock":
// TBD field to compare so default to nothing
// TBD field to compare so default to nothing
case "assembly":
// TBD field to compare so default to nothing
break;
case "date_range":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('b', $filter['from'], $filter['to']));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "date_today":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('c', '', ''));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "date_week":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('d', '', ''));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "date_wtd":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('e', '', ''));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "date_month":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('f', '', ''));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "date_mtd":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('g', '', ''));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "date_qtr":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('h', '', ''));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "date_qtd":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('i', '', ''));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "date_year":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('j', '', ''));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "date_ytd":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('k', '', ''));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "period":
$arrData = array('fieldname' => $filter['cfield']);
$arrData['params'] = implode(':', array('l', '', ''));
$temp = gen_build_sql_date($arrData['params'], $arrData['fieldname']);
$crit_data = $temp['sql'];
$crit_desc = $temp['description'];
break;
case "range":
if ($filter['from'] != '') {
$crit_data .= $filter['cfield'] . ">='" . $filter['from'] . "'";
$crit_desc .= ' ' . TEXT_FROM . ' ' . $filter['from'];
}
if ($filter['to'] != '') {
if (strlen($crit_data) > 0) {
$crit_data .= ' and ';
}
$crit_data .= $filter['cfield'] . "<='" . $filter['to'] . "'";
$crit_desc .= ' ' . TEXT_TO . ' ' . $filter['to'];
}
if ($crit_desc != '') {
$crit_desc = $filter['name'] . ': ' . $crit_desc;
}
break;
case "yes":
case "true":
case "active":
case "printed":
$crit_data .= $value['cfield'] . '=1';
$crit_desc .= $filter['name'] . '=' . $value['crit'];
break;
case "no":
case "false":
case "inactive":
case "unprinted":
$crit_data .= $value['cfield'] . '=0';
$crit_desc .= $filter['name'] . '=' . $value['crit'];
}
if ($crit_data != '') {
$criteria_list[] = $crit_data;
}
if ($crit_desc != '') {
$description_list[] = $crit_desc;
}
}
}
$crit_string = '';
if (count($criteria_list) > 0) {
$crit_string = ' where ' . implode(' and ', $criteria_list);
}
// build query
$fields = array_keys($data);
if (!in_array('id', $fields)) {
$fields[] = 'id';
}
$field_list = 'd.' . implode(', d.', $fields);
$sql = "select distinct " . $field_list . " from " . $prefs['table_name'] . " d ";
if ($use_address_book) {
$sql .= "left join " . TABLE_ADDRESS_BOOK . " a on d.id = a.ref_id ";
}
$sql .= $crit_string;
$export_rows = $db->Execute($sql);
if ($export_rows->RecordCount() == 0) {
$messageStack->add(TEXT_IMP_ERMSG12, 'success');
return;
}
// export data
while (!$export_rows->EOF) {
$output_line = array();
foreach ($data as $field_name => $index) {
$output_line[$index] = ie_process_the_data($export_rows->fields[$field_name], $element_processing[$index]);
}
if ($use_address_book) {
$address_type = array($account_type . 'm' => $mail, $account_type . 's' => $ship, $account_type . 'b' => $bill);
foreach ($address_type as $type => $array_name) {
// fetch the id to use to link addresses to the correct main record
$sql = "select * from " . TABLE_ADDRESS_BOOK . " \r\n\t\t\t\t\twhere type = '" . $type . "' \r\n\t\t\t\t\tand ref_id = '" . $export_rows->fields['id'] . "' limit " . MAX_NUM_ADDRESSES;
$addresses = $db->Execute($sql);
for ($i = 0; $i < count($array_name); $i++) {
// each address of type
foreach ($array_name[$i] as $field_name => $index) {
// each field in address
$output_line[$index] = ie_process_the_data($addresses->fields[$field_name], $element_processing[$index]);
}
$addresses->MoveNext();
}
}
}
ksort($output_line);
$output .= ie_implode($output_line, $delimiter, $qualifier) . chr(10);
$export_rows->MoveNext();
}
$FileSize = strlen($output);
if (substr($options['export_file_name'], strrpos($options['export_file_name'], '.')) == '.csv') {
header("Content-type: application/csv");
} else {
header("Content-type: plain/txt");
}
header("Content-disposition: attachment; filename=" . $options['export_file_name'] . "; size=" . $FileSize);
header('Pragma: cache');
header('Cache-Control: public, must-revalidate, max-age=0');
header('Connection: close');
header('Expires: ' . date('r', time() + 60 * 60));
header('Last-Modified: ' . date('r', time()));
print $output;
exit;
}
