function views_crosstab_table::query in Views Crosstab 6

Same name and namespace in other branches

1. 7 plugins/views_crosstab_table.inc \views_crosstab_table::query()

File

./views_crosstab_table.inc, line 109

Class

views_crosstab_table

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

Code

function query() {
  parent::query();
  $options = $this->view->style_plugin->options;
  $columns = $this
    ->sanitize_columns($this->options['columns']);

  // Clone the query, and modify the clone to determine the crosstab columns.
  $crosstab_columns_query = drupal_clone($this->view->query);
  foreach ($crosstab_columns_query->fields as $key => $field) {

    // We set aggregate to TRUE for each field, to ensure ours is the only
    // groupby.
    // http://drupal.org/node/385158
    $crosstab_columns_query->fields[$key]['aggregate'] = TRUE;
    $this->view->query->fields[$key]['aggregate'] = TRUE;

    // Locate the crosstab rows, column and data fields.
    if ($field['field'] == $options['crosstab_rows']) {
      $crosstab_rows = $field;
    }
    if ($field['field'] == $options['crosstab_columns']) {
      $crosstab_columns = $field;
    }
    if ($field['field'] == $options['crosstab_data']) {
      $crosstab_data = $field;
    }
  }

  // Basic sanity checking.
  $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;
  }

  // Group by the header, and remove any existing orders.
  $crosstab_columns_query
    ->add_groupby($crosstab_columns['alias']);
  $crosstab_columns_query->orderby = array();
  $query = $crosstab_columns_query
    ->query();
  $args = $crosstab_columns_query
    ->get_where_args();

  // Run the query and collate the results.
  $results = db_query($query, $args);

  // Crosstab COUNT is really a SUM of 1s.
  if ($options['crosstab_operation'] == 'COUNT') {
    $data = 1;
    $options['crosstab_operation'] = 'SUM';
    $options['crosstab_operation_total'] = 'COUNT';
  }
  else {
    $data = $crosstab_data['table'] . '.' . $crosstab_data['field'];
    $options['crosstab_operation_total'] = $options['crosstab_operation'];
  }

  // Loop through the columns, adding to the query and the view.
  while ($result = db_fetch_object($results)) {
    $crosstab_column = $result->{$crosstab_columns}['alias'];
    $alias = $crosstab_columns['alias'] . '_' . strtolower(preg_replace('/[^A-Za-z_]+/', '_', $crosstab_column));

    // Add the new field to the query, basing it on the original data query field.
    $this->view->query
      ->add_field(NULL, 'ROUND(' . $options['crosstab_operation'] . '(IF(' . $crosstab_columns['table'] . '.' . $crosstab_columns['field'] . ' = "' . db_escape_string($crosstab_column) . '", ' . $data . ', 0)),' . $options['crosstab_decimals'] . ')', $alias);
    $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] = drupal_clone($this->view->field[$crosstab_data['field']]);

    //Set the alias, label and remove the table association (since it is 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;
  }

  // Add a "Total" column.
  // TODO: Add an option for this.
  $alias = $crosstab_columns['alias'] . '_total';
  $this->view->query
    ->add_field(NULL, 'ROUND(' . $options['crosstab_operation_total'] . '(' . $crosstab_data['table'] . '.' . $crosstab_data['field'] . '),' . $options['crosstab_decimals'] . ')', $alias);
  $this->view->query->fields[$alias]['aggregate'] = TRUE;
  $this->view->field[$alias] = drupal_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'] = t('Total');
  $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']);
}