function views_crosstab_table::query in Views Crosstab 7

Same name and namespace in other branches

1. 6 views_crosstab_table.inc \views_crosstab_table::query()

Add cross table column fields and alter the query.

Overrides views_plugin_style::query

File

plugins/views_crosstab_table.inc, line 270

Plugin functions

Class

views_crosstab_table

Style plugin to transform a linear query into a crosstab table.

Code

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']);
  }
}