class views_crosstab_table in Views Crosstab 7
Same name and namespace in other branches
- 6 views_crosstab_table.inc \views_crosstab_table
Style plugin to transform a linear query into a crosstab table.
Hierarchy
- class \views_object
- class \views_plugin
- class \views_plugin_style
- class \views_plugin_style_table
- class \views_crosstab_table
- class \views_plugin_style_table
- class \views_plugin_style
- class \views_plugin
Expanded class hierarchy of views_crosstab_table
2 string references to 'views_crosstab_table'
- views_crosstab_views_default_views in ./
views_crosstab.views_default.inc - Implements hook_views_default_views().
- views_crosstab_views_plugins in ./
views_crosstab.views.inc - Implements hook_views_plugins().
File
- plugins/
views_crosstab_table.inc, line 13 - Plugin functions
View source
class views_crosstab_table extends views_plugin_style_table {
/**
* Define plugin options.
*
*/
function option_definition() {
$options = parent::option_definition();
$options['crosstab_rows'] = array(
'default' => NULL,
);
$options['crosstab_columns'] = array(
'default' => NULL,
);
$options['crosstab_data'] = array(
'default' => NULL,
);
$options['crosstab_operation'] = array(
'default' => 'COUNT',
);
$options['include_crosstab_operation_on_row'] = array(
'default' => TRUE,
'bool' => TRUE,
);
$options['include_crosstab_operation_on_column'] = array(
'default' => TRUE,
'bool' => TRUE,
);
$options['crosstab_decimals'] = array(
'default' => '0',
);
return $options;
}
/**
* Render the options form.
*
* @param array $form
* Reference to the form array we're building.
* @param array $form_state
* Reference to the form state.
*/
function options_form(&$form, &$form_state) {
parent::options_form($form, $form_state);
$handlers = $this->display->handler
->get_handlers('field');
$columns = $this
->sanitize_columns($this->options['columns']);
$defaults_select = array_keys($columns);
if (count($defaults_select) < 3) {
drupal_set_message(t('At least 3 fields need to be added to your view to create a crosstab.'), 'error');
return;
}
$crosstab_rows_default = array_shift($defaults_select);
$crosstab_columns_default = array_shift($defaults_select);
$crosstab_data_default = array_shift($defaults_select);
// Define theme function to handling options format format.
$form['#theme'] = 'views_crosstab_ui_table';
foreach ($columns as $field => $column) {
$safe = str_replace(array(
'][',
'_',
' ',
), '-', $field);
$id = 'edit-style-options-columns-' . $safe;
// Collect the row, column and data fields.
// TODO: JS helper.
$radio_id = drupal_html_id('edit-crosstab-rows-' . $field);
$form['crosstab_rows'][$field] = array(
'#type' => 'radio',
'#return_value' => $field,
'#parents' => array(
'style_options',
'crosstab_rows',
),
'#id' => $radio_id,
'#attributes' => array(
'id' => $radio_id,
),
'#default_value' => isset($this->options['crosstab_rows']) ? $this->options['crosstab_rows'] : $crosstab_rows_default,
'#dependency' => array(
$id => array(
$field,
),
),
);
$radio_id = drupal_html_id('edit-crosstab-columns-' . $field);
$form['crosstab_columns'][$field] = array(
'#type' => 'radio',
'#return_value' => $field,
'#parents' => array(
'style_options',
'crosstab_columns',
),
'#id' => $radio_id,
'#attributes' => array(
'id' => $radio_id,
),
'#default_value' => isset($this->options['crosstab_columns']) ? $this->options['crosstab_columns'] : $crosstab_columns_default,
'#dependency' => array(
$id => array(
$field,
),
),
);
$radio_id = drupal_html_id('edit-crosstab-data-' . $field);
$form['crosstab_data'][$field] = array(
'#type' => 'radio',
'#return_value' => $field,
'#parents' => array(
'style_options',
'crosstab_data',
),
'#id' => $radio_id,
'#attributes' => array(
'id' => $radio_id,
),
'#default_value' => isset($this->options['crosstab_data']) ? $this->options['crosstab_data'] : $crosstab_data_default,
'#dependency' => array(
$id => array(
$field,
),
),
);
}
// Aggregation operators to use on the data.
$form['crosstab_operation'] = array(
'#type' => 'select',
'#title' => 'Crosstab operation',
'#options' => array(
'COUNT' => 'Count',
'SUM' => 'Sum',
'AVG' => 'Average',
'MIN' => 'Minimum',
'MAX' => 'Maximum',
),
'#default_value' => $this->options['crosstab_operation'],
'#weight' => -5,
);
$form['include_crosstab_operation_on_row'] = array(
'#type' => 'checkbox',
'#title' => t('Add an extra column for aggregating entire row'),
'#default_value' => $this->options['include_crosstab_operation_on_row'],
'#weight' => -4,
);
$form['include_crosstab_operation_on_column'] = array(
'#type' => 'checkbox',
'#title' => t('Add an extra row for aggregating each column'),
'#default_value' => $this->options['include_crosstab_operation_on_column'],
'#weight' => -4,
);
// Decimals for the functions.
$form['crosstab_decimals'] = array(
'#type' => 'select',
'#title' => 'Round to N number of decimals.',
'#options' => array(
0,
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
),
'#default_value' => $this->options['crosstab_decimals'],
'#weight' => -3,
);
}
/**
* Returns the header name for the given aggretation operation.
*
*
* @param string $op
* The capitalized name of the aggregation operation.
* @return string
* The header name for this operation.
*/
static function crosstab_operation_header($op) {
$ops = array(
'COUNT' => t('Total'),
'SUM' => t('Total'),
'AVG' => t('Average'),
'MIN' => t('Minimum'),
'MAX' => t('Maximum'),
);
return $ops[$op];
}
/**
* Validate the options form.
*
* @param array $form
* Reference to the form array we're building.
* @param array $form_state
* Reference to the form state.
*/
function options_validate(&$form, &$form_state) {
parent::options_validate($form, $form_state);
// Get machine_names (keys) of crosstab field options.
$crosstab_options = array_keys(views_crosstab_field_options());
// Ensure that the user has not picked the same field for more than
// crosstab option. Do this by looping over the three crosstab options
// (rows, columns, data) twice.
foreach ($crosstab_options as $crosstab_option) {
// Perform nested loop over same crosstab options array.
foreach ($crosstab_options as $crosstab_option_compare) {
// Only compare different options.
if ($crosstab_option != $crosstab_option_compare) {
// Get field name used in each of the two options being compared.
$crosstab_option_field_name = $form_state['values']['style_options'][$crosstab_option];
$crosstab_option_compare_field_name = $form_state['values']['style_options'][$crosstab_option_compare];
// If the field names are the same, then the user has picked the same
// field for two different crosstab options and the query will likely
// not work or produce unintended results.
// TODO: check that this test doesn't return false positive errors
// due to same field name from two different sources.
if ($crosstab_option_field_name == $crosstab_option_compare_field_name) {
// Define placeholders for error message.
$error_items = array(
'%field_display_value' => $form['info'][$crosstab_option_field_name]['name']['#markup'],
'%crosstab_option' => $crosstab_option,
'%crosstab_option_compare' => $crosstab_option_compare,
);
// Report the error.
$error_message = 'You cannot set the same field (%field_display_value) ';
$error_message .= 'to be a source for <strong>both</strong> ';
$error_message .= '%crosstab_option and %crosstab_option_compare.';
form_error($form[$crosstab_option][$crosstab_option_field_name], t($error_message, $error_items));
// Break out of nested loop and outer loop.
break 2;
}
}
}
}
// Ensure that the field type selected for aggregation options is valid for
// the chosen aggregation operation.
// Get the aggregation operation.
$crosstab_operation = $form_state['values']['style_options']['crosstab_operation'];
// Define field types that can be aggregated.
$field_types_that_can_be_aggregated = array(
'SUM' => array(
'serial',
'int',
'float',
'numeric',
),
'AVG' => array(
'serial',
'int',
'float',
'numeric',
),
'MIN' => array(
'serial',
'int',
'float',
'numeric',
'varchar',
'char',
'text',
),
'MAX' => array(
'serial',
'int',
'float',
'numeric',
'varchar',
'char',
'text',
),
);
// Assume all field types can be COUNTed and do not need to be included
// in this validation check.
if ($crosstab_operation != "COUNT") {
// Get field handlers for the current display.
$field_handlers = $this->display->handler->handlers['field'];
if ($field_handlers) {
// Loop over the field handlers.
foreach ($field_handlers as $handler) {
// Get the table name and field associated with
// the handler of the field.
$table_name = $handler->table;
$field_name = $handler->field;
// Get crosstab option field name.
$crosstab_data_field_name = $form_state['values']['style_options']['crosstab_data'];
// Check if the crosstab data field name matches the handler field.
if ($crosstab_data_field_name == $field_name) {
// Check the field exists - this should be a redundant check.
$field_exists = db_field_exists($table_name, $field_name);
if ($field_exists) {
// Get the database schema for the field's table.
$schema = drupal_get_schema($table_name);
// Get the drupal database field type for the field.
$field_type = $schema['fields'][$field_name]['type'];
// Look for a matching field type for the aggregation operation.
if (!in_array($field_type, $field_types_that_can_be_aggregated[$crosstab_operation])) {
// Define placeholders for error message.
$error_items = array(
'%crosstab_data_field_name' => $crosstab_data_field_name,
'%field_type' => $field_type,
'%operation' => $crosstab_operation,
);
// Report the error.
form_error($form['crosstab_data'][$crosstab_data_field_name], t('The field selected for the crosstab data (%crosstab_data_field_name)
has a %field_type, which cannot be used in a %operation operation.', $error_items));
}
}
}
}
}
}
}
/**
* Add cross table column fields and alter the query.
*/
function query() {
parent::query();
// Get crosstab style options.
$options = $this->view->style_plugin->options;
// Check the crosstab options are set - when creating a new query
// (with Preview on), the first time the Crosstab Table display is chosen
// Views calls query() before the options have been set.
if (isset($options['crosstab_rows']) && isset($options['crosstab_columns']) && isset($options['crosstab_data'])) {
// Clone the existing query. This cloned query will be used to determine
// the columns for the crosstab table.
$crosstab_columns_view_query = clone $this->view->query;
$crosstab_columns_view_query->view = clone $this->view;
// Loop over cloned query fields.
foreach ($crosstab_columns_view_query->fields as $key => $field) {
// Set remove function attribute if it exists.
if (isset($crosstab_columns_view_query->fields[$key]['function'])) {
unset($crosstab_columns_view_query->fields[$key]['function']);
}
// Set aggregate to TRUE for each non-crosstab columns field,
// to ensure only grouping by crosstab columns in the query that
// fetches column names.
// see http://drupal.org/node/385158#comment-2871182
// Locate the crosstab rows, column and data fields.
if ($this
->crosstab_match_field($field['field'], $options['crosstab_rows'])) {
$crosstab_rows = $field;
$crosstab_columns_view_query->fields[$key]['aggregate'] = TRUE;
}
elseif ($this
->crosstab_match_field($field['field'], $options['crosstab_columns'])) {
$crosstab_columns = $field;
$this->view->query->fields[$key]['aggregate'] = TRUE;
}
elseif ($this
->crosstab_match_field($field['field'], $options['crosstab_data'])) {
$crosstab_data = $field;
$this->view->query->fields[$key]['aggregate'] = TRUE;
$crosstab_columns_view_query->fields[$key]['aggregate'] = TRUE;
}
else {
$this->view->query->fields[$key]['aggregate'] = TRUE;
$crosstab_columns_view_query->fields[$key]['aggregate'] = TRUE;
}
}
// Basic sanity checking.
// Get column names.
$columns = $this
->sanitize_columns($this->options['columns']);
$defaults_select = array_keys($columns);
if (count($defaults_select) < 3) {
// This is only a warning at this stage (unlike on the options screen,
// where we set an error, to further ensure we have the users attention.
drupal_set_message(t('At least 3 fields need to be added to your view to create a crosstab.'), 'warning');
$error = TRUE;
}
// The options do not appear to be set, or we haven't located each field
// in the query. This can happen on fresh views, or on views where a field
// used for a previously configured crosstab has been removed.
if (empty($options['crosstab_rows']) || empty($options['crosstab_columns']) || empty($options['crosstab_data']) || empty($options['crosstab_operation']) || !isset($crosstab_rows['field']) || !isset($crosstab_columns['field']) || !isset($crosstab_data['field'])) {
drupal_set_message(t('The Crosstab Table views style does not appear to be configured.'), 'warning');
$error = TRUE;
}
if (isset($error)) {
// This falls back to the parent table handler.
drupal_set_message(t('The crosstab could not be displayed, showing a linear table instead.'), 'message');
return;
}
// Remove existing ORDER BY clause.
$crosstab_columns_view_query->orderby = array();
// Remove existing GROUP BY clause.
$crosstab_columns_view_query->groupby = array();
// Convert query to a SelectQuery object.
$crosstab_columns_query = $crosstab_columns_view_query
->query();
// Add GROUP BY clause.
$crosstab_columns_query
->groupBy($crosstab_columns['alias']);
// Run the query and collate the results.
$results = $crosstab_columns_query
->execute();
// Crosstab COUNT is really a SUM of 1s.
if ($options['crosstab_operation'] == 'COUNT') {
$data = '1';
$data_else = '0';
$options['crosstab_operation'] = 'SUM';
$options['crosstab_operation_on_row'] = 'COUNT';
}
else {
$data = $crosstab_data['table'] . '.' . $crosstab_data['field'];
$data_else = 'NULL';
$options['crosstab_operation_on_row'] = $options['crosstab_operation'];
}
// Loop through the columns, adding to the query and the view.
foreach ($results as $i => $result) {
$crosstab_column = $result->{$crosstab_columns['alias']};
// Add the new field to the query, basing it on the original data
// query field.
$placeholder = ':' . $crosstab_column . '_' . $i . '_placeholder';
$alias = $this->view->query
->add_field(NULL, "CASE WHEN {$crosstab_columns['table']}.{$crosstab_columns['field']} = {$placeholder} THEN {$data} ELSE {$data_else} END", $crosstab_columns['alias'] . '_' . strtolower(preg_replace('/[^A-Za-z0-9_]+/', '_', $crosstab_column)), array(
'function' => strtolower($options['crosstab_operation']),
'placeholders' => array(
$placeholder => $crosstab_column,
),
));
$this->view->query->fields[$alias]['aggregate'] = TRUE;
// Add the field to the view, basing it on the data view field.
// TODO: linea problematica.
$this->view->field[$alias] = clone $this->view->field[$crosstab_data['field']];
// Set the alias, label and remove the table association
// (since it is an aggregate).
$this->view->field[$alias]->field_alias = $alias;
$this->view->field[$alias]->options['label'] = $crosstab_column;
$this->view->field[$alias]->options['id'] = $alias;
unset($this->view->field[$alias]->table_alias);
// Add the field to the current style_plugin, so we can sort with it.
$this->view->style_plugin->options['info'][$alias] = $this->view->style_options['info'][$crosstab_data['field']];
$this->view->style_plugin->options['columns'][$alias] = $alias;
}
// If selected by user, add an additional column for aggregating
// on entire row.
if ($options['include_crosstab_operation_on_row'] == TRUE) {
$alias = $this->view->query
->add_field(NULL, $crosstab_data['table'] . '.' . $crosstab_data['field'], $crosstab_columns['alias'] . '_total', array(
'function' => strtolower($options['crosstab_operation_on_row']),
));
$this->view->query->fields[$alias]['aggregate'] = TRUE;
$this->view->field[$alias] = clone $this->view->field[$crosstab_data['field']];
$this->view->field[$alias]->field_alias = $alias;
$this->view->field[$alias]->options['id'] = $alias;
$this->view->field[$alias]->options['label'] = self::crosstab_operation_header($this->view->style_plugin->options['crosstab_operation']);
$this->view->style_plugin->options['info'][$alias] = $this->view->style_options['info'][$crosstab_data['field']];
$this->view->style_plugin->options['columns'][$alias] = $alias;
}
// Remove the source header and data source fields, since they have now
// been replaced by the crosstab.
unset($this->view->field[$crosstab_data['field']]);
unset($this->view->field[$crosstab_columns['field']]);
unset($this->view->style_plugin->options['info'][$crosstab_data['field']]);
unset($this->view->style_plugin->options['info'][$crosstab_columns['field']]);
unset($this->view->style_plugin->options['columns'][$crosstab_data['field']]);
unset($this->view->style_plugin->options['columns'][$crosstab_columns['field']]);
unset($this->view->display['default']->display_options['fields'][$crosstab_data['field']]);
unset($this->view->display['default']->display_options['style_options']['columns'][$crosstab_data['field']]);
unset($this->view->display['default']->display_options['style_options']['info'][$crosstab_data['field']]);
unset($this->view->display['default']->handler->options['fields'][$crosstab_data['field']]);
// Rebuild the sort, so that column sorts will be added by the new fields.
$this->view->style_plugin
->build_sort();
// Add a group by for the crosstab row grouping field.
// This has to come after the build_sort, or it tries to add groups.
$this->view->query
->add_groupby($crosstab_rows['alias']);
}
}
/**
* Determines if a field defined in the options matches a field or real field
* in the view's query.
*
* @param $string $query_field
* The name of the field (or real field) from the view's query
* @param $string $option_field
* The name of the field from the view's definition
* @return boolean
* TRUE iff they match
*/
function crosstab_match_field($query_field, $option_field) {
// First check for a field defined using the field's name
// Then check for a field defined with a different 'real field'
return $option_field == $query_field || isset($this->view->field[$option_field]->definition['real field']) && $this->view->field[$option_field]->definition['real field'] == $query_field;
}
function pre_render($values) {
// This method should properly have the argument passed by referenced. An
// attempt to have the views API corrected to do so was unsuccessful.
// Declaring the argument by reference generates a "Declaration of X should
// be compatible with Y" warning.
//
// It would be possible to suppress errors for this function, but that might
// suppress actual errors.
// See: https://stackoverflow.com/questions/36079651/silence-declaration-should-be-compatible-warnings-in-php-7
// And in specific the solution by Andrea.
//
// The solution taken here is to modify the result in the passed view
// object. This will not work if pre_render is called with something other
// than ($view->result), which it currently is not.
$result =& $this->view->result;
// Get options array.
$options = $this->view->style_plugin->options;
// Generate summary row
if (count($result) && $options['include_crosstab_operation_on_column']) {
$summary_query = clone $this->view->build_info['query'];
// Remove groupBy to summarize all rows
$group_by =& $summary_query
->getGroupBy();
$group_by = array();
$summary_results = $summary_query
->execute()
->fetchObject();
$result[] = $summary_results;
}
// Get attribute that defines numerical precision.
$rounding = $options['crosstab_decimals'];
// Loop over rows in $values array.
foreach ($result as &$result_row) {
// Loop over fields in current row.
foreach ($result_row as &$field_value) {
// Check if value is numeric.
if (is_numeric($field_value)) {
// Round result to appropriate precision.
$field_value = number_format($field_value, $rounding, '.', '');
}
}
}
}
/**
* Render all of the fields for a given style and store them on the object.
*
* @param $result
* The result array from $view->result
*/
function render_fields($result) {
$rendered_fields = parent::render_fields($result);
if ($this->view->style_plugin->options['include_crosstab_operation_on_column']) {
// Create proper heading in last summary row, first column. This cannot be done earlier
// because it might be subjected to numeric or other formatting.
$row_field = $this->view->style_options['crosstab_rows'];
end($rendered_fields);
$rendered_fields[key($rendered_fields)][$row_field] = self::crosstab_operation_header($this->view->style_plugin->options['crosstab_operation']);
}
return $rendered_fields;
}
}Members
Name |
Modifiers | Type | Description | Overrides |
|---|---|---|---|---|
|
views_crosstab_table:: |
function | Determines if a field defined in the options matches a field or real field in the view's query. | ||
|
views_crosstab_table:: |
static | function | Returns the header name for the given aggretation operation. | |
|
views_crosstab_table:: |
function |
Render the options form. Overrides views_plugin_style_table:: |
||
|
views_crosstab_table:: |
function |
Validate the options form. Overrides views_plugin_style:: |
||
|
views_crosstab_table:: |
function |
Define plugin options. Overrides views_plugin_style_table:: |
||
|
views_crosstab_table:: |
function |
Allow the style to do stuff before each row is rendered. Overrides views_plugin_style:: |
||
|
views_crosstab_table:: |
function |
Add cross table column fields and alter the query. Overrides views_plugin_style:: |
||
|
views_crosstab_table:: |
function |
Render all of the fields for a given style and store them on the object. Overrides views_plugin_style:: |
||
|
views_object:: |
public | property | Handler's definition. | |
|
views_object:: |
public | property | Except for displays, options for the object will be held here. | 1 |
|
views_object:: |
function | Collect this handler's option definition and alter them, ready for use. | ||
|
views_object:: |
public | function | Views handlers use a special construct function. | 4 |
|
views_object:: |
public | function | 1 | |
|
views_object:: |
public | function | ||
|
views_object:: |
public | function | Always exports the option, regardless of the default value. | |
|
views_object:: |
public | function | Set default options on this object. | 1 |
|
views_object:: |
public | function | Set default options. | |
|
views_object:: |
public | function | Let the handler know what its full definition is. | |
|
views_object:: |
public | function | Unpack options over our existing defaults, drilling down into arrays so that defaults don't get totally blown away. | |
|
views_object:: |
public | function | Unpack a single option definition. | |
|
views_object:: |
public | function | Unpacks each handler to store translatable texts. | |
|
views_object:: |
public | function | ||
|
views_plugin:: |
public | property | The current used views display. | |
|
views_plugin:: |
public | property | The plugin name of this plugin, for example table or full. | |
|
views_plugin:: |
public | property | The plugin type of this plugin, for example style or query. | |
|
views_plugin:: |
public | property |
The top object of a view. Overrides views_object:: |
1 |
|
views_plugin:: |
public | function | Provide a list of additional theme functions for the theme info page. | |
|
views_plugin:: |
public | function | Handle any special handling on the validate form. | 9 |
|
views_plugin:: |
public | function | Return the human readable name of the display. | |
|
views_plugin:: |
public | function | Returns the summary of the settings in the display. | 8 |
|
views_plugin:: |
public | function | Provide a full list of possible theme templates used by this style. | |
|
views_plugin_style:: |
public | property | The row plugin, if it's initialized and the style itself supports it. | |
|
views_plugin_style:: |
public | property | Store all available tokens row rows. | |
|
views_plugin_style:: |
public | function |
Destructor. Overrides views_object:: |
|
|
views_plugin_style:: |
public | function | Get a rendered field. | |
|
views_plugin_style:: |
public | function | Get the raw field value. | |
|
views_plugin_style:: |
public | function | Return the token replaced row class for the specified row. | |
|
views_plugin_style:: |
public | function | Initialize a style plugin. | |
|
views_plugin_style:: |
public | function | Render the display in this style. | 5 |
|
views_plugin_style:: |
public | function | Group records as needed for rendering. | |
|
views_plugin_style:: |
public | function | Render the grouping sets. | |
|
views_plugin_style:: |
public | function | Take a value and apply token replacement logic to it. | |
|
views_plugin_style:: |
public | function | Return TRUE if this style also uses fields. | |
|
views_plugin_style:: |
public | function | Return TRUE if this style also uses a row plugin. | |
|
views_plugin_style:: |
public | function | Return TRUE if this style also uses a row plugin. | |
|
views_plugin_style:: |
public | function | Return TRUE if this style uses tokens. | |
|
views_plugin_style:: |
public | function |
Validate that the plugin is correct and can be saved. Overrides views_plugin:: |
|
|
views_plugin_style_table:: |
public | property | Contains the current active sort column. | |
|
views_plugin_style_table:: |
public | property | Contains the current active sort order, either desc or asc. | |
|
views_plugin_style_table:: |
public | function |
Determine if we should provide sorting based upon $_GET inputs Overrides views_plugin_style:: |
|
|
views_plugin_style_table:: |
public | function |
Add our actual sort criteria Overrides views_plugin_style:: |
|
|
views_plugin_style_table:: |
public | function |
Should the output of the style plugin be rendered even if it's empty. Overrides views_plugin_style:: |
|
|
views_plugin_style_table:: |
public | function | Normalize a list of columns based upon the fields that are available. This compares the fields stored in the style handler to the list of fields actually in the view, removing fields that have been removed and adding new fields in their own column. |