/**
* This is where an icon report's data should be calculated and set in $this->data
*
* $this->data should contain an array for each icon, containing the display name,
* the numerical value, and the path to the icon file
*/
function calculate_data()
{
$this->data = $this->get_default_data();
// Get extra icons... send list of filters but don't include exceptions - as we do need the filter list here
$exceptions = $this->get_filter_exceptions();
$rest_of_filters = array();
foreach ($this->filter->_fields as $key => $value) {
if (!in_array($key, $exceptions)) {
$rest_of_filters[] = $key;
}
}
// Pass the rest_of_filters array, but then also let the method know this is a special config filter
$extra_icons = $this->filter->get_sql_filter('', $rest_of_filters, $this->allow_interactive_filters(), $this->allow_configured_filters(), '', true);
foreach ($this->data as $key => $value) {
//If key is in choices array and not in extra_icons
if (array_key_exists($key, $this->checkboxes_filter->options['choices']) && !strpos($extra_icons, $key)) {
// then we need to remove it from $this->data in fact...
unset($this->data[$key]);
continue;
}
//first step - try getting the item value directly
$data_item = $this->get_data_item($key);
if ($data_item !== FALSE) {
$this->data[$key]->value = $data_item;
} else {
//backup plan - use SQL query to get the item value
$use_filters = true;
$sql = $this->get_data_item_sql($key, $use_filters);
if ($sql !== FALSE) {
//parse SQL for a WHERE clause
$has_where_clause = php_report::sql_has_where_clause($sql);
$conditional_symbol = 'WHERE';
if ($has_where_clause) {
$conditional_symbol = 'AND';
}
// apply filters if applicable
if (!empty($this->filter) && !empty($use_filters)) {
// Include filter_exceptions here so that our config type filter is not included in the filters added to the final sql
$sql_filter = $this->filter->get_sql_filter('', $this->get_filter_exceptions($key), $this->allow_interactive_filters(), $this->allow_configured_filters());
if (!empty($sql_filter)) {
$sql .= " {$conditional_symbol} ({$sql_filter})";
}
}
//obtain field value
if ($field_data = get_field_sql($sql)) {
$this->data[$key]->value = $field_data;
}
}
}
}
}
/**
* Calculates the data for a specified data series
*
* @param unknown $key A key that uniquely identifies the series, as
* specified by calculate_series_list
*
* @return array The numerical data for the specified series
*/
function calculate_series($series_key)
{
$data = array();
$points = $this->calculate_series_points($series_key);
foreach ($points as $point) {
$sql = $this->get_series_point_data_sql($series_key, $point);
$has_where_clause = php_report::sql_has_where_clause($sql);
$conditional_symbol = 'WHERE';
if ($has_where_clause) {
$conditional_symbol = 'AND';
}
if (!empty($this->filter)) {
$sql_filter = $this->filter->get_sql_filter('', array(), $this->allow_interactive_filters(), $this->allow_configured_filters());
if (!empty($sql_filter)) {
$sql .= " {$conditional_symbol} ({$sql_filter})";
}
}
if ($point_data = get_field_sql($sql)) {
$data[] = $point_data;
} else {
$data[] = 0;
}
}
return $data;
}
/**
* Calculates the entire SQL query, including changes made
* by the report engine
*
* @param boolean $use_limit true if the paging-based limit clause should be included, otherwise false
* @param string $sql Fixed sql to replace parameters with - if null, obtain from report definition
* @param array $params the SQL query parameters
* @return array The SQL query, and the appropriate sql filter information
*/
function get_complete_sql_query($use_limit = true, $sql = null, $params = array()) {
$columns = $this->get_select_columns();
//used to track whether we're in the main report flow or not
$in_main_report_flow = false;
//query from the report implementation
if ($sql === null) {
list($sql, $params) = $this->get_report_sql($columns);
$in_main_report_flow = true;
}
//determine if the special wildcard for adding filter sql is included
$parameter_token_pos = strpos($sql, table_report::PARAMETER_TOKEN);
if ($parameter_token_pos === false) {
//no wildcard, so add filter sql to the end
//determine if we need an add or where clause
$has_where_clause = php_report::sql_has_where_clause($sql);
$conditional_symbol = 'WHERE';
if ($has_where_clause) {
$conditional_symbol = 'AND';
}
//add filter sql
list($additional_sql, $additional_params) = $this->get_filter_condition($conditional_symbol);
$sql .= $additional_sql;
$params = array_merge($params, $additional_params);
} else {
//wildcard, so do a find and replace
//get the filter clause without adding WHERE or AND - it's up to the
//report to include those in this case because parsing pieces of queries
//is complex and error-prone
list($filter_clause, $filter_params) = $this->get_filter_condition('');
if (empty($filter_clause)) {
$filter_clause = 'TRUE';
}
//replace the wildcard with the filter clause
$sql = str_replace(table_report::PARAMETER_TOKEN, $filter_clause, $sql);
// Check for duplicate named parameters
foreach ($filter_params as $key => $value) {
if (substr_count($sql, ":{$key}") > 1) {
$cnt = 0;
$sql_parts = explode(":{$key}", $sql);
foreach($sql_parts as $sql_part) {
if ($cnt++) {
$newkey = ($cnt == 1) ? $key : "{$key}_{$cnt}";
$new_sql .= ":{$newkey}{$sql_part}";
$filter_params[$newkey] = $value;
} else {
$new_sql = $sql_part;
}
}
$sql = $new_sql;
}
}
$params = array_merge($params, $filter_params);
}
if ($in_main_report_flow) {
//grouping
$groups = $this->get_report_sql_groups();
if (!empty($groups)) {
$sql .= " GROUP BY {$groups}";
}
$this->set_num_recs($sql, $params);
//ordering
$sql .= $this->get_order_by_clause();
}
//error_log("table_report::get_complete_sql_query(): sql = {$sql}");
return array($sql, $params);
}
/**
* This is where an icon report's data should be calculated and set in $this->data
*
* $this->data should contain an array for each icon, containing the display name,
* the numerical value, and the path to the icon file
*/
function calculate_data() {
global $DB;
$this->data = $this->get_default_data();
foreach ($this->data as $key => $value) {
//first step - try getting the item value directly
$data_item = $this->get_data_item($key);
if ($data_item !== FALSE) {
$this->data[$key]->value = $data_item;
} else {
//backup plan - use SQL query to get the item value
$use_filters = true;
list($sql, $params) = $this->get_data_item_sql($key, $use_filters);
if ($sql !== FALSE) {
//parse SQL for a WHERE clause
$has_where_clause = php_report::sql_has_where_clause($sql);
$conditional_symbol = 'WHERE';
if($has_where_clause) {
$conditional_symbol = 'AND';
}
//apply filters if applicable
if(!empty($this->filter) && !empty($use_filters)) {
list($additional_sql, $additional_params) = $this->filter->get_sql_filter('', $this->get_filter_exceptions($key), $this->allow_interactive_filters(), $this->allow_configured_filters());
if(!empty($additional_sql)) {
$sql .= " {$conditional_symbol} ({$additional_sql})";
$params = array_merge($params, $additional_params);
}
}
//obtain field value
if ($field_data = $DB->get_field_sql($sql, $params)) {
$this->data[$key]->value = $field_data;
}
}
}
}
}
/**
* Specifies a WHERE / AND clause that acts on the supplied secondary filter
*
* @param string $sql The SQL statement to be filtered
* @param string $filter_key The key that references the filter being applied
*
* @return array The WHERE / AND clause performing the filtering, or
* an empty string if none, as well as the applicable filter values
*/
private function get_secondary_filter_clause($sql, $filter_key) {
//calculate our conditional symbol / operator based on the existing query
$has_where_clause = php_report::sql_has_where_clause($sql);
$conditional_symbol = 'WHERE';
if ($has_where_clause) {
$conditional_symbol = 'AND';
}
//filtering
if (!empty($this->filter)) {
//try to return the filter clause and parameter values
list($sql_filter, $params) = $this->filter->get_sql_filter('', array(), $this->allow_interactive_filters(), $this->allow_configured_filters(), $filter_key);
if(!empty($sql_filter)) {
return array(" {$conditional_symbol} ({$sql_filter})", $params);
}
}
return array(null, array());
}
/**
* Calculates the entire SQL query, including changes made
* by the report engine
*
* @param boolean $use_limit true if the paging-based limit clause should be included, otherwise false
* @param string $sql Fixed sql to replace parameters with - if NULL, obtain from report definition
*
* @return string The SQL query
*/
function get_complete_sql_query($use_limit = true, $sql = NULL)
{
$columns = $this->get_select_columns();
//used to track whether we're in the main report flow or not
$in_main_report_flow = false;
//query from the report implementation
if ($sql === NULL) {
$sql = $this->get_report_sql($columns);
$in_main_report_flow = true;
}
//determine if the special wildcard for adding filter sql is included
$parameter_token_pos = strpos($sql, table_report::PARAMETER_TOKEN);
if ($parameter_token_pos === false) {
//no wildcard, so add filter sql to the end
//determine if we need an add or where clause
$has_where_clause = php_report::sql_has_where_clause($sql);
$conditional_symbol = 'WHERE';
if ($has_where_clause) {
$conditional_symbol = 'AND';
}
//add filter sql
$sql .= $this->get_filter_condition($conditional_symbol);
} else {
//wildcard, so do a find and replace
//get the filter clause without adding WHERE or AND - it's up to the
//report to include those in this case because parsing pieces of queries
//is complex and error-prone
$filter_clause = $this->get_filter_condition('');
//replace the wildcard with the filter clause
$sql = str_replace(table_report::PARAMETER_TOKEN, $filter_clause, $sql);
}
if ($in_main_report_flow) {
//grouping
$groups = $this->get_report_sql_groups();
if (!empty($groups)) {
$sql .= " GROUP BY {$groups}";
}
$this->set_num_recs($sql);
//ordering
$sql .= $this->get_order_by_clause();
}
return $sql;
}
