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;
}
Example #2
0
function GenerateXMLFile($Data, $report, $delivery_method = 'D')
{
    // for csv reports only
    global $Heading, $posted_currencies;
    // Now write each data line and totals
    print_r($Data);
    foreach ($Data as $myrow) {
        $xml .= '<Row>' . chr(10);
        $Action = array_shift($myrow);
        $todo = explode(':', $Action);
        // contains a letter of the date type and title/groupname
        switch ($todo[0]) {
            case "r":
                // Report Total
            // Report Total
            case "g":
                // Group Total
                $Desc = $todo[0] == 'g' ? TEXT_GROUP_TOTAL_FOR : TEXT_REPORT_TOTAL_FOR;
                $xml .= '<' . $Desc . '>' . $todo[1] . '</' . $Desc . '>' . chr(10);
                // Now write the total data like any other data row
            // Now write the total data like any other data row
            case "d":
                // Data
            // Data
            default:
                $i = 0;
                foreach ($Heading as $title) {
                    //foreach ($myrow as $mycolumn) { // check for embedded commas and enclose in quotes
                    $xml .= '<' . $title . '>' . $myrow[$i] . '</' . $title . '>' . chr(10);
                    $i++;
                }
        }
        $xml .= '</Row>' . chr(10);
    }
    $ReportName = ReplaceNonAllowedCharacters($report->title) . '.csv';
    if ($delivery_method == 'S') {
        return array('filename' => $ReportName, 'pdf' => $CSVOutput);
    }
    global $db, $messageStack;
    $error = false;
    $output = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . chr(10);
    $output .= '<PhreeformReport>' . chr(10);
    $output .= $xml;
    $output .= '</PhreeformReport>' . chr(10);
    print $output;
    exit;
    echo createXmlHeader() . $xml . createXmlFooter();
    die;
}