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 BuildForm($report, $delivery_method = 'D')
{
// for forms only
global $db, $messageStack, $FieldValues;
require_once DIR_FS_MODULES . 'phreeform/classes/form_generator.php';
$output = array();
// check for at least one field selected to show
if (!$report->fieldlist) {
// No fields are checked to show, that's bad
return $messageStack->add(PHREEFORM_NOROWS, 'caution');
}
// Let's build the sql field list for the general data fields (not totals, blocks or tables)
$strField = array();
foreach ($report->fieldlist as $key => $field) {
// check for a data field and build sql field list
if (in_array($field->type, array('Data', 'BarCode', 'ImgLink'))) {
// then it's data field make sure it's not empty
if ($field->boxfield[0]->fieldname) {
$strField[] = prefixTables($field->boxfield[0]->fieldname) . ' as d' . $key;
} else {
// the field is empty, bad news, error and exit
return $messageStack->add(PHREEFORM_EMPTYFIELD . $key, 'error');
}
}
}
$report->sqlField = implode(', ', $strField);
// fetch the sort order and add to group by string to finish ORDER BY string
$strSort = array();
if (is_array($report->sortlist)) {
foreach ($report->sortlist as $sortline) {
if ($sortline->default == '1') {
$strSort[] = prefixTables($sortline->fieldname);
}
}
}
$sqlSort = implode(', ', $strSort);
// fetch criteria and date filter info
$strCrit = array();
if ($report->datedefault) {
$dates = gen_build_sql_date($report->datedefault, prefixTables($report->datefield));
if ($dates['sql']) {
$strCrit[] = $dates['sql'];
}
}
//echo 'report = '; print_r($report); echo '<br />';
$criteria = build_criteria($report);
if ($criteria['sql']) {
$strCrit[] = $criteria['sql'];
}
$report->sqlCrit = implode(' and ', $strCrit);
// fetch the tables to query
$report->sqlTable = '';
foreach ($report->tables as $table) {
if (isset($table->relationship)) {
if (!$table->joinopt) {
$table->joinopt = ' JOIN ';
}
$report->sqlTable .= ' ' . $table->joinopt . ' ' . DB_PREFIX . $table->tablename . ' ON ' . prefixTables($table->relationship);
} else {
$report->sqlTable .= DB_PREFIX . $table->tablename;
}
}
// We now have the sql, find out how many groups in the query (to determine the number of forms)
$form_field_list = $report->filenamefield == '' ? prefixTables($report->formbreakfield) : prefixTables($report->formbreakfield) . ', ' . prefixTables($report->filenamefield);
$sql = 'select ' . $form_field_list . ' from ' . $report->sqlTable;
if ($report->sqlCrit) {
$sql .= ' where ' . $report->sqlCrit;
}
$sql .= ' group by ' . prefixTables($report->formbreakfield);
if ($strSort) {
$sql .= ' order by ' . $sqlSort;
}
// execute sql to see if we have data
//echo 'sql = ' . $sql . '<br />'; exit();
$result = $db->Execute($sql);
if (!$result->RecordCount()) {
return $messageStack->add(PHREEFORM_NOROWS, 'caution');
}
// set the filename for download or email
if ($report->filenameprefix || $report->filenamefield) {
$report->filename = $report->filenameprefix . $result->fields[strip_tablename($report->filenamefield)] . '.pdf';
} else {
$report->filename = ReplaceNonAllowedCharacters($report->title) . '.pdf';
}
// create an array for each form
$report->recordID = array();
while (!$result->EOF) {
$report->recordID[] = $result->fields[strip_tablename($report->formbreakfield)];
$result->MoveNext();
}
// retrieve the company information
for ($i = 0; $i < sizeof($report->fieldlist); $i++) {
if ($report->fieldlist[$i]->type == 'CDta') {
$report->fieldlist[$i]->text = constant($report->fieldlist[$i]->boxfield[0]->fieldname);
} else {
if ($report->fieldlist[$i]->type == 'CBlk') {
if (!$report->fieldlist[$i]->boxfield) {
return $messageStack->add(PHREEFORM_EMPTYFIELD . $report->fieldlist[$i]->description, 'error');
}
$TextField = '';
foreach ($report->fieldlist[$i]->boxfield as $entry) {
$temp = $entry->formatting ? ProcessData(constant($entry->fieldname), $entry->formatting) : constant($entry->fieldname);
$TextField .= AddSep($temp, $entry->processing);
}
$report->fieldlist[$i]->text = $TextField;
}
}
}
// patch for special_reports (forms) where the data file is generated externally from the standard class
if ($report->special_class) {
if (!($path = find_special_class($report->special_class))) {
return false;
}
load_special_language($path, $report->special_class);
require_once $path . '/classes/' . $report->special_class . '.php';
}
if ($report->serialform) {
$output = BuildSeq($report, $delivery_method);
// build sequential form (receipt style)
} else {
$output = BuildPDF($report, $delivery_method);
// build standard PDF form, doesn't return if download
}
return $output;
//if we are here, return the contents for an email attachment
}