/** * @param boolean $check_restart defaults to true in which case if the results are paginated and the offeset is more than the number of results, we restart it setting the page to 1 * @returns mixed false on failure on succes an array. at index 'results' and MDB2 buffered result object at index 'num_results' the * number of results that would be found without the limit */ protected function getResults($check_restart = true) { if (array_key_exists('limit_paginated', $this->defaultOptions) && $this->defaultOptions['limit_paginated']) { if (!array_key_exists('limit_page', $this->defaultOptions) || !(is_integer($this->defaultOptions['limit_page']) || ctype_digit($this->defaultOptions['limit_page'])) || (int) $this->defaultOptions['limit_page'] < 1) { $this->defaultOptions['limit_page'] = 1; } if (!array_key_exists('limit_per_page', $this->defaultOptions) || !(is_integer($this->defaultOptions['limit_per_page']) || ctype_digit($this->defaultOptions['limit_per_page'])) || (int) $this->defaultOptions['limit_per_page'] < 1) { //we don't have a valid 'limit_per_page' $this->defaultOptions['limit_per_page'] = 100; //default to 100 } $this->defaultOptions['limit_page'] = (int) $this->defaultOptions['limit_page']; $this->defaultOptions['limit_per_page'] = (int) $this->defaultOptions['limit_per_page']; $limit_offset = ($this->defaultOptions['limit_page'] - 1) * $this->defaultOptions['limit_per_page']; $limit_amount = $this->defaultOptions['limit_per_page']; } else { $limit_offset = $this->defaultOptions['limit_offset']; //$limit_amount = $this->defaultOptions['limit_per_page']; $limit_amount = false; if ($limit_offset !== false || $limit_offset !== 'false') { if (!(is_integer($limit_offset) || ctype_digit($limit_offset)) || $limit_offset < 0) { $limit_offset = 0; } } if ($limit_amount === null || empty($limit_amount) || $limit_amount === 'false') { $limit_amount = false; } if ($limit_amount !== false) { if (!(is_integer($limit_amount) || ctype_digit($limit_amount)) || $limit_amount < 1) { $limit_amount = 100; } } } if ($this->defaultOptions['sort_order'] == 'none') { $sort_order = array(); } else { $sort_order = explode(',', $this->defaultOptions['sort_order'] . ''); foreach ($sort_order as $i => $field) { if ($field == 'none') { unset($sort_order[$i]); } } } $fieldData = $this->getDisplayFieldsData(); $sort_order = $this->validateSortFields($sort_order, array_keys($fieldData)); $fields = array(); $groups = array(); $aggregates = array(); $aggregate_fields = array(); $has_total = false; $cols_in_report = array('' => I2CE_CustomReport::getColumnsInReportTable($this->config->report)); $referenced_form_ids = array(); $no_display = array(); foreach ($fieldData as $field => $data) { if (!is_array($data)) { $no_display[] = $field; } if ($field == 'total') { $has_total = true; continue; } list($merge_form_form, $form_field, $aggregate) = array_pad(explode('+', $field), 3, ''); //function fields have blank $form_form list($mergekey, $mergereport, $form_form) = array_pad(explode(':', $merge_form_form), -3, ''); //pad to the left. note there is no mergekey/mergereprot on primary table if ($mergereport == 'primary_table') { $mergereport = ''; } if (!array_key_exists($mergereport, $cols_in_report)) { $cols_in_report[$mergereport] = I2CE_CustomReport::getColumnsInReportTable($mergereport); } if ($form_form != $field ? !in_array($form_form . '+' . $form_field, $cols_in_report[$mergereport]) : !in_array($form_form, $cols_in_report[$mergereport])) { I2CE::raiseError("Skipping field {$field} ({$form_form})({$form_field}) b/c it is not in report {$mergereport}:\n\t" . implode(',', $cols_in_report[$mergereport])); continue; } if ($mergekey) { $merge = $mergekey . ':' . $mergereport . ':'; } else { $merge = ''; } if ($form_form && $form_form != $field) { if ($merge) { $referenced_form_ids[] = "`{$mergekey}:{$mergereport}`.`{$form_form}+id` AS `{$merge}{$form_form}+id`"; } else { $referenced_form_ids[] = "`primary_table`.`{$form_form}+id` AS `{$form_form}+id`"; } } if ($form_form == $field) { $fields[] = "{$merge}{$field}"; } else { $fields[] = "{$merge}{$form_form}+{$form_field}"; } if ($form_form && $form_form != $field) { //it is not a function field. check if there is a link if (is_array($data) && $data['link'] && $data['link_append']) { if ($merge) { $groups[$data['link_append']] = "`{$mergekey}:{$mergereport}`.`{$data['link_append']}` AS `{$merge}{$data['link_append']}`"; } else { $groups[$data['link_append']] = '`' . $data['link_append'] . '`'; } } } if ($form_form == $field) { $reportfield = $field; } else { $reportfield = "{$form_form}+{$form_field}"; } switch ($aggregate) { case 'sum': if ($merge) { $aggregates[$reportfield] = " SUM(`{$mergekey}:{$mergereport}`.`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } else { $aggregates[$reportfield] = " SUM(`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } break; case 'maximum': if ($merge) { $aggregates[$reportfield] = " MAX(`{$mergekey}:{$mergereport}`.`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } else { $aggregates[$reportfield] = " MAX(`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } break; case 'minmimum': if ($merge) { $aggregates[$reportfield] = " MIN(`{$mergekey}:{$mergereport}`.`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } else { $aggregates[$reportfield] = " MIN(`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } break; case 'average': if ($merge) { $aggregates[$reportfield] = " AVG(`{$mergekey}:{$mergereport}`.`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } else { $aggregates[$reportfield] = " AVG(`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } break; case 'count': if ($merge) { $aggregates[$reportfield] = " COUNT(`{$mergekey}:{$mergereport}`.`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } else { $aggregates[$reportfield] = " COUNT(`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } break; case 'count_distinct': if ($merge) { $aggregates[$reportfield] = " COUNT( DISTINCT `{$mergekey}:{$mergereport}`.`{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } else { $aggregates[$reportfield] = " COUNT(DISTINCT `{$reportfield}`) AS `{$field}`"; $aggregate_fields[$reportfield] = "{$field}"; } break; default: //'none' if ($merge) { $groups[$reportfield] = "`{$mergekey}:{$mergereport}`.`{$reportfield}` AS `{$field}`"; } else { $groups[$reportfield] = "`primary_table`.`{$reportfield}` AS `{$reportfield}`"; } } } if (count($fields) == 0 && !$has_total) { I2CE::raiseError("No fields to get results from"); return false; } $sorts = array(); foreach ($sort_order as $formfield) { if (strlen($formfield) == 0) { continue; } if ($formfield[0] == '-') { $sort_field = substr($formfield, 1); $sort_postfix = ' DESC'; } else { $sort_field = $formfield; $sort_postfix = ''; } if ($sort_field != 'total' && !in_array($sort_field, $fields)) { //make sure we don't have junk continue; } if (array_key_exists($sort_field, $aggregate_fields)) { $sort_field = $aggregate_fields[$sort_field]; } $sorts[$sort_field] = '`' . $sort_field . '`' . $sort_postfix; } $order_by = ''; if (count($sorts) > 0) { $lsorts = array(); foreach ($sorts as $s) { $lsorts[] = strtolower($s); } $order_by = ' ORDER BY ' . implode(',', $lsorts); } $group_by = ''; $group_bys = array(); if (count($aggregates) == 0) { //no aggregate fields if ($has_total) { $group_bys = $groups; foreach ($group_bys as $field => $info) { if (in_array($field, $no_display)) { unset($group_bys[$field]); } } $select_fields = $group_bys; $select_fields[] = 'COUNT(*) AS `total`'; } else { if (count($groups) == 0) { I2CE::raiseError("Report view {$this->view} has no viewable fields"); return false; } $select_fields = $groups; } } else { $select_fields = $aggregates; $group_bys = $groups; $select_fields = array_merge($aggregates, $groups); if ($has_total) { $group_bys = array_diff($select_fields, $no_display); $select_fields[] = 'COUNT(*) AS `total`'; } foreach (array_keys($group_bys) as $gb) { if (in_array($gb, $no_display)) { unset($group_bys[$gb]); } } } $group_bys = array_map(create_function('$n', 'return ( ($i = stripos( $n, " as" ) ) === false ? $n : substr( $n, 0, $i ) );'), $group_bys); if (count($group_bys) > 0) { $lgroup_bys = array(); foreach ($group_bys as $g) { $lgroup_bys[] = strtolower($g); } $group_by = ' GROUP BY ' . implode(',', $lgroup_bys); } if ($limit_offset !== false && $limit_amount !== false) { $limit = " LIMIT {$limit_amount} OFFSET {$limit_offset} "; $this->row_start = $limit_offset; $this->row_amount = $limit_offset; } else { $limit = ''; $this->row_start = false; $this->row_amount = false; } if (array_key_exists('nested_limits', $this->defaultOptions)) { $where = $this->processWhere($this->defaultOptions['nested_limits']); } else { $where = ''; } $merge_reports = $this->getMergedReportJoins(); $select_fields = array_unique(array_merge($referenced_form_ids, $select_fields)); $lselect_fields = array(); foreach ($select_fields as $s) { $lselect_fields[] = strtolower($s); } $qry = "SELECT SQL_CALC_FOUND_ROWS " . implode(',', $lselect_fields) . " FROM " . I2CE_CustomReport::getCachedTableName($this->config->report) . ' AS primary_table ' . implode(" ", $merge_reports) . ' ' . $where . ' ' . $group_by . ' ' . $order_by . ' ' . $limit; I2CE::raiseError("Doing {$qry}"); $db = MDB2::singleton(); $res = $db->query($qry); I2CE::raiseMessage($qry); if (I2CE::pearError($res, "Could not get results")) { return false; } $num_rows = $db->queryRow("SELECT FOUND_ROWS() AS num_rows"); if (I2CE::pearError($num_rows, "Could not get total number of results")) { $num_rows = false; } else { $num_rows = (int) $num_rows->num_rows; } if ($check_restart && $num_rows > 0 && $num_rows < $limit_offset) { //set the limit offset to be 0 and restart the query.. if (array_key_exists('limit_paginated', $this->defaultOptions) && $this->defaultOptions['limit_paginated']) { if (array_key_exists('limit_page', $this->defaultOptions) && (is_integer($this->defaultOptions['limit_page']) || ctype_digit($this->defaultOptions['limit_page'])) && (int) $this->defaultOptions['limit_page'] > 1) { $this->defaultOptions['limit_page'] = 1; return $this->getResults(false); } } } return array('results' => $res, 'num_results' => $num_rows, 'has_total' => $has_total); }