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); }