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