/**
* @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);
}
