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;
}
Beispiel #2
0
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
}