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 BuildSeq($report, $delivery_method = 'D') { // for forms only - Sequential mode global $db, $messageStack, $FieldValues, $posted_currencies; // Generate a form for each group element $output = NULL; foreach ($report->recordID as $formNum => $Fvalue) { // find the single line data from the query for the current form page $TrailingSQL = " from " . $report->sqlTable . " where " . ($report->sqlCrit ? $report->sqlCrit . " AND " : '') . prefixTables($report->formbreakfield) . " = '" . $Fvalue . "'"; if ($report->special_class) { $form_class = $report->special_class; $special_form = new $form_class(); $FieldValues = $special_form->load_query_results($report->formbreakfield, $Fvalue); } else { $result = $db->Execute("select " . $report->sqlField . $TrailingSQL); $FieldValues = $result->fields; } // load the posted currency values $posted_currencies = array(); if (ENABLE_MULTI_CURRENCY && strpos($report->sqlTable, TABLE_JOURNAL_MAIN) !== false) { $sql = "select currencies_code, currencies_value " . $TrailingSQL; $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 ($report->fieldlist as $key => $field) { switch ($field->type) { default: $oneline = formatReceipt($field->text, $field->width, $field->align, $oneline); break; case 'Data': $value = ProcessData(array_shift($FieldValues), $field->boxfield[0]->processing); $oneline = formatReceipt($value, $field->width, $field->align, $oneline); break; case 'TBlk': if (!$field->boxfield[0]->fieldname) { return $messageStack->add(PHREEFORM_EMPTYFIELD . $field->fieldname, 'error'); } if ($report->special_class) { $TextField = $special_form->load_text_block_data($field->boxfield); } else { $arrTxtBlk = array(); // Build the fieldlist foreach ($field->boxfield as $idx => $entry) { $arrTxtBlk[] = prefixTables($entry->fieldname) . ' as r' . $idx; } $strTxtBlk = implode(', ', $arrTxtBlk); $result = $db->Execute("select " . $strTxtBlk . $TrailingSQL); $TextField = ''; for ($i = 0; $i < sizeof($field->boxfield); $i++) { $temp = $field->boxfield[$i]->formatting ? ProcessData($result->fields['r' . $i], $field->boxfield[$i]->formatting) : $result->fields['r' . $i]; $TextField .= AddSep($temp, $field->boxfield[$i]->processing); } } $report->fieldlist[$key]->text = $TextField; $oneline = $report->fieldlist[$key]->text; break; case 'Tbl': if (!$field->boxfield) { return $messageStack->add(PHREEFORM_EMPTYFIELD . $field->description, 'error'); } // $tblHeading = array(); // foreach ($field->boxfield as $TableField) $tblHeading[] = $TableField->description; $data = array(); if ($report->special_class) { $data = $special_form->load_table_data($field->boxfield); } else { $tblField = array(); foreach ($field->boxfield as $key => $TableField) { $tblField[] = prefixTables($TableField->fieldname) . ' as r' . $key; } $tblField = implode(', ', $tblField); $result = $db->Execute("select " . $tblField . $TrailingSQL); while (!$result->EOF) { $data[] = $result->fields; $result->MoveNext(); } } $field->data = $data; $StoredTable = $field; foreach ($data as $key => $value) { $temp = array(); foreach ($value as $data_key => $data_element) { $offset = substr($data_key, 1); $value = ProcessData($data_element, $field->boxfield[$offset]->processing); $temp[] .= formatReceipt($value, $field->boxfield[$offset]->width, $field->boxfield[$offset]->align); } $oneline .= implode("", $temp) . "\n"; } $oneline = substr($oneline, 0, -2); $field->rowbreak = 1; break; case 'TDup': if (!$StoredTable) { return $messageStack->add(PHREEFORM_EMPTYTABLE . $field->description, 'error'); } // insert new coordinates into existing table $StoredTable->abscissa = $field->abscissa; $StoredTable->ordinate = $field->ordinate; foreach ($StoredTable->data as $key => $value) { $temp = array(); foreach ($value as $data_key => $data_element) { $value = ProcessData($data_element, $report->boxfield[$data_key]->processing); $temp[] .= formatReceipt($value, $field->width, $field->align); } $oneline = implode("", $temp); } $field->rowbreak = 1; break; case 'Ttl': if (!$field->boxfield) { return $messageStack->add(PHREEFORM_EMPTYFIELD . $field->description, 'error'); } if ($report->special_class) { $FieldValues = $special_form->load_total_results($field); } else { $ttlField = ''; foreach ($field->boxfield as $TotalField) { $ttlField[] = prefixTables($TotalField->fieldname); } $sql = "select sum(" . implode(' + ', $ttlField) . ") as form_total" . $TrailingSQL; $result = $db->Execute($sql); $FieldValues = $result->fields['form_total']; } $value = ProcessData($FieldValues, $report->boxfield[0]->processing); $oneline = formatReceipt($value, $field->width, $field->align, $oneline); break; } if ($field->rowbreak) { $output .= $oneline . "\n"; $oneline = ''; } } // set the printed flag field if provided if ($report->setprintedflag) { $id_field = $report->formbreakfield; $temp = explode('.', $report->setprintedflag); if (sizeof($temp) == 2) { // need the table name and field name $sql = "update " . $temp[0] . " set " . $temp[1] . " = " . $temp[1] . " + 1 where " . $report->formbreakfield . " = '" . $Fvalue . "'"; $db->Execute($sql); } } $output .= "\n\n\n\n"; // page break } if ($delivery_method == 'S') { return $output; } $FileSize = strlen($output); header("Content-type: application/text"); header("Content-disposition: attachment; filename=" . $report->filenameprefix . ".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; }