function views_calc_views_query_alter in Views Calc 5

Implementation of hook_views_query_alter().

Add additional row calculations to the query as new columns.

If $view->views_calc_calculation is set to 'SUM', 'COUNT', or 'AVG', produce a single query result row with that value, otherwise return a normal query.

File

./views_calc.module, line 574

This module will allow you to add calculated fields to views tables and compute (SUM, COUNT, AVG, etc) columns of numeric data in a views table.

Code

function views_calc_views_query_alter(&$query, &$view) {

  // When not doing a column calculation, do nothing.
  if (!$view->views_calc_calculation) {
    return;
  }
  else {
    $cols = (array) variable_get('views_calc_' . $view->vid . '_col', '');
    $col_calc = (array) variable_get('views_calc_' . $view->vid . '_col_calc', '');
    $calc = $view->views_calc_calculation;
    $query->distinct = FALSE;
    $query->orderby = array();
    $query->groupby = array();

    // The query fields do not necessarily match the view fields,
    // so create a way to find the position of each view field in the query.
    $query_fields = array();
    foreach ($query->fields as $field) {
      $value = explode(' AS ', $field);
      $query_fields[$value[1]] = $value[0];
    }

    // Go through the view fields, check for columns with
    // calculations, and alter the query to do a groupby
    // for each calculated value.
    foreach ($view->field as $delta => $field) {
      $query_field = $query_fields[$field['queryname']];
      if (in_array($field['fullname'], array_keys($cols))) {
        $calc_field = $calc . '(' . $query_field . ') AS ' . $field['queryname'];
        $group_field = '(' . $query_field . ') AS ' . $field['queryname'];

        // Add calc values to the query if not already there,
        // otherwise alter the existing field value.
        if (isset($query->fields[$query_delta])) {
          $query->fields[$query_delta] = $calc_field;
        }
        else {
          $query
            ->add_field($calc_field, NULL);
          $query
            ->ensure_table($field['tablename']);
        }
      }
      else {

        // Empty fields that have no calculations.
        if (isset($query->fields[$query_delta])) {
          $query->fields[$query_delta] = "'' AS " . $field['queryname'];
        }
        else {
          $query
            ->add_field("'' AS " . $field['queryname'], NULL);
          $query
            ->ensure_table($field['tablename']);
        }
      }
      $query
        ->add_field("'" . $calc . "' AS TOTAL_" . $calc, NULL);
      $query
        ->add_groupby("TOTAL_" . $calc);
    }
    return;
  }
}