You are here

Home » API reference » Views Calc 7

views_calc.module in Views Calc 7

Same filename and directory in other branches
  1. 5 views_calc.module
  2. 6.3 views_calc.module
  3. 6 views_calc.module

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.

File

views_calc.module
View source 
<?php

/**
 * @file
 * 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.
 */

/**
 * Implements hook_views_api().
 */
function views_calc_views_api() {
  return array(
    'api' => 2,
    'path' => drupal_get_path('module', 'views_calc'),
  );
}

/**
 * Implements hook_theme().
 */
function views_calc_theme() {
  $path = drupal_get_path('module', 'views_calc');
  return array(
    'views_calc_ui_table' => array(
      'render element' => 'form',
      'file' => 'theme.inc',
    ),
  );
}

/**
 * Implements hook_help().
 */
function views_calc_help($section, $arg) {
  switch ($section) {
    case 'admin/config/views_calc':
    case 'admin/config/views_calc/fields':
      return t('<p>Set up calculation fields. Calculation fields will be displayed in the views fields list and can be added to any view.</p>');
    case 'admin/config/views_calc/settings':
      return t('Put one operator on each line. To avoid the possibility of SQL injection, calculation text will only allow these values, numbers, and field names. Make sure this list includes any text other than field names that should be allowed in the calculation fields.');
    case 'admin/help#views_calc':
      return t('<ul> <li>Go to admin/settings/views_calc to create calculations.</li> <li>The \'Fields\' tab will allow you to create calculated fields that can be inserted into any view. The calculations can include the value of any Views field, combined with numbers, arithmatic operations, and common SQL functions like ROUND() or MIN(). Each available field has a shortcut name like %Node:Title. Create SQL snippets like (%Node:Field1 + 84600) or ROUND(%Node:Field2 / 3). </li> <li>The \'Columns\' tab allows you to set column calculations. The column totals are added in the view itself by using the style \'Views Calc Table\' and setting the fields in the table that should have column totals.</li> <li>The \'Settings\' tab allows you to add new functions to the list of allowable functions. </ul>');
  }
}

/**
 * Default SQL operator alternatives.
 *
 * The ones allowed in this system are stored in the
 * variable views_calc_operators, and can be changed
 * at admin/settings/views_calc.
 *
 */
function _views_calc_operators() {
  $default = array(
    '+',
    '-',
    '*',
    '/',
    '(',
    ')',
    ',',
    "'",
    'CONCAT',
    'MIN',
    'MAX',
    'ROUND',
    'NOW()',
  );
  $operators = variable_get('views_calc_operators', implode("\n", $default));
  return explode("\n", $operators);
}

/**
 *  Column calculation alternatives.
 */
function _views_calc_calc_options() {
  return array(
    'COUNT' => t('Count'),
    'SUM' => t('Sum'),
    'AVG' => t('Average'),
    'MIN' => t('Minimum'),
    'MAX' => t('Maximum'),
  );
}

/**
 *  Result format options.
 */
function _views_calc_format_options() {
  $options = array(
    'none' => '',
    'integer' => 'intval',
    'decimal (1)' => 'number_format:1',
    'decimal (2)' => 'number_format:2',
    'shortdate' => 'format_date:small',
    'mediumdate' => 'format_date',
    'longdate' => 'format_date:large',
    'custom' => '',
  );
  return $options;
}

/**
 * Implements hook_permission().
 *
 * The permission 'administer views calc' has rights to alter the SQL
 * operators that can be used in calculations.
 *
 * The permission 'create views calc' has rights to create calculated
 * fields and set calculation columns on views.
 */
function views_calc_permission() {
  return array(
    'create views calc' => array(
      'title' => t('create views calc'),
      'description' => t('TODO Add a description for \'create views calc\''),
    ),
    'administer views calc' => array(
      'title' => t('administer views calc'),
      'description' => t('TODO Add a description for \'administer views calc\''),
    ),
  );
}

/**
 * Implements hook_menu().
 */
function views_calc_menu() {
  $items = array();
  $items['admin/structure/views_calc'] = array(
    'title' => t('Views Calc'),
    'description' => t('Set Views Calc fields and columns.'),
    'type' => MENU_NORMAL_ITEM,
    'weight' => 10,
    'priority' => 1,
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'views_calc_fields_form',
    ),
    'access arguments' => array(
      'create views calc',
    ),
  );
  $items['admin/structure/views_calc/fields'] = array(
    'title' => t('Fields'),
    'type' => MENU_DEFAULT_LOCAL_TASK,
    'weight' => 5,
    'priority' => 1,
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'views_calc_fields_form',
    ),
    'access arguments' => array(
      'create views calc',
    ),
  );
  $items['admin/structure/views_calc/settings'] = array(
    'title' => t('Settings'),
    'type' => MENU_LOCAL_TASK,
    'weight' => 6,
    'priority' => 1,
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'views_calc_settings_form',
    ),
    'access arguments' => array(
      'administer views calc',
    ),
  );
  $items['admin/structure/views_calc/export'] = array(
    'title' => 'Export fields',
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'views_calc_export_form',
    ),
    'access arguments' => array(
      'create views calc',
    ),
    'type' => MENU_LOCAL_TASK,
    'weight' => 7,
  );
  $items['admin/structure/views_calc/import'] = array(
    'title' => 'Import fields',
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'views_calc_import_form',
    ),
    'access arguments' => array(
      'create views calc',
    ),
    'type' => MENU_LOCAL_TASK,
    'weight' => 8,
  );
  return $items;
}

/**
 * FAPI settings_form.
 */
function views_calc_settings_form($form, &$form_state) {
  drupal_set_title(t('Views Calc'));
  $operators = _views_calc_operators();
  $form['views_calc_operators'] = array(
    '#type' => 'textarea',
    '#default_value' => implode("\n", $operators),
    '#title' => t('Allowable functions and operators'),
    '#rows' => intval(sizeof($operators) + 2),
  );
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Save'),
  );
  return $form;
}

/**
 * FAPI settings_form submit.
 */
function views_calc_settings_form_submit($form, &$form_state) {
  $form_values = $form_state['values'];
  variable_set('views_calc_operators', $form_values['views_calc_operators']);
}

/**
 * FAPI fields_form.
 *
 * Views Calc Fields tab on views list.
 */
function views_calc_fields_form($form, &$form_state) {
  $i = 0;
  $substitutions = array();
  $help = t('<p>The specific fields that are available in any view depend on the base table used for that view.</p>');
  require_once DRUPAL_ROOT . '/' . drupal_get_path('module', 'views') . '/includes/admin.inc';
  $base_tables = views_fetch_base_tables();
  foreach ($base_tables as $base => $data) {
    $base_subs = _views_calc_substitutions($base);
    $substitutions += $base_subs;
    $fieldset = array(
      '#title' => t('Base table: !name', array(
        '!name' => t($data['title']),
      )),
      '#value' => theme('item_list', array(
        'items' => $base_subs,
      )),
      '#collapsible' => TRUE,
      '#collapsed' => TRUE,
      '#attributes' => array(),
      '#children' => '',
    );
    $help .= theme('fieldset', array(
      'element' => $fieldset,
    ));
  }

  // display current views calcs fields
  $fields = _views_calc_fields();
  foreach ($fields as $field) {
    $form[] = views_calc_field_form_item($i, $field, $substitutions);
    $i++;
  }

  // add blank fields for more calcs
  for ($x = $i + 1; $x < $i + 2; $x++) {
    $field = array();
    $form[] = views_calc_field_form_item($i, $field, $substitutions);
  }
  $form['#prefix'] = '<div class="views-calc-field-settings">';
  $form['#suffix'] = '</div><div class="views-calc-field-names"><strong>Field Substitutions</strong><div class="form-item">' . $help . '</div></div>';
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Save'),
  );
  return $form;
}

/**
 * A form element for an individual calculated field.
 */
function views_calc_field_form_item($i, $field, $substitutions) {
  if (empty($field)) {
    $field = new stdClass();
    $field->cid = 0;
    $field->label = '';
    $field->tablelist = '';
    $field->fieldlist = '';
    $field->calc = '';
    $field->format = '';
    $field->custom = '';
    $field->base = '';
  }
  require_once DRUPAL_ROOT . '/' . drupal_get_path('module', 'views') . '/includes/admin.inc';
  $options = array();
  $base_tables = views_fetch_base_tables();
  foreach ($base_tables as $base => $data) {
    $options[$base] = t($data['title']);
  }
  $form['group'][$i] = array(
    '#type' => 'fieldset',
    '#tree' => TRUE,
    '#title' => t('Field: ') . !empty($field->label) ? $field->label : t('New'),
    '#collapsible' => TRUE,
    '#collapsed' => FALSE,
  );
  $form['group'][$i]['cid'] = array(
    '#type' => 'hidden',
    '#value' => intval($field->cid),
  );
  $form['group'][$i]['tablelist'] = array(
    '#type' => 'hidden',
    '#value' => $field->tablelist,
  );
  $form['group'][$i]['base'] = array(
    '#type' => 'select',
    '#title' => t('Base table'),
    '#options' => $options,
    '#default_value' => !empty($field->base) && array_key_exists($field->base, $options) ? $field->base : 'node',
    '#description' => t('The base table for this field.'),
  );
  $form['group'][$i]['label'] = array(
    '#type' => 'textfield',
    '#title' => t('Label'),
    '#field_prefix' => 'ViewsCalc: ',
    '#default_value' => str_replace('ViewsCalc: ', '', $field->label),
    '#description' => t('The views field name for this field (i.e. Views Calc: My Calculation).'),
  );
  $form['group'][$i]['calc'] = array(
    '#type' => 'textarea',
    '#title' => t('Calculation'),
    '#default_value' => strtr($field->calc, $substitutions),
    '#description' => t("<p>The query operation to be performed, using numbers, field substitutions, and " . implode(' ', _views_calc_operators()) . ". Leave spaces between parentheses and field names, i.e. 'CONCAT( %field1, ' ', %field2 )'. <strong>" . t('Note that all fields must be from the base table selected above! You cannot combine fields from different base tables.') . "</strong></p>"),
  );
  $form['group'][$i]['format'] = array(
    '#type' => 'select',
    '#title' => t('Format'),
    '#default_value' => $field->format,
    '#options' => drupal_map_assoc(array_keys(_views_calc_format_options())),
    '#description' => t('The format of the result of this calculation.'),
  );
  $form['group'][$i]['custom'] = array(
    '#type' => 'textfield',
    '#title' => t('Custom function'),
    '#default_value' => $field->custom,
    '#description' => t('The function to call for a custom format.'),
  );
  return $form;
}

/**
 * FAPI fields_form validate.
 *
 * Validate the views calc settings
 */
function views_calc_fields_form_validate($form, &$form_state) {
  $form_values = $form_state['values'];
  $edit = $form_values;
  foreach ($edit as $delta => $item) {
    if ($item['calc'] == '' || !is_numeric($delta)) {

      // remove blank fields, don't save them
      continue;
    }
    else {

      // Remove all valid values from calc, if anything is left over, it is invalid.
      // First, remove all field names.
      $repl = array();
      $patterns = array();
      $base = $item['base'];
      foreach (_views_calc_substitutions($base) as $key => $value) {
        $key = trim($value);
        $count = strlen($value);
        $replace = preg_quote($value);
        $patterns[] = "`(^|[^\\\\\\\\])" . $replace . "`";
        $repl[] = '${1}';
      }
      $remaining = trim(preg_replace($patterns, $repl, $item['calc']));

      // Next, remove functions and numbers.
      $repl = array();
      $patterns = array();
      foreach (_views_calc_replacements() as $value) {
        $patterns[] = "`(^|[^\\\\\\\\])" . preg_quote(trim($value)) . "`";
        $repl[] = '${1}';
      }
      $remaining = trim(preg_replace($patterns, $repl, $remaining));
      if (!empty($remaining)) {
        form_set_error($form_values[$delta]['calc'], t('The values %remaining in %field are not allowed.', array(
          '%remaining' => $remaining,
          '%field' => $item['label'],
        )));
      }
    }
  }
}

/**
 * FAPI fields_form submit.
 *
 * Save the views calc field settings
 */
function views_calc_fields_form_submit($form, &$form_state) {
  $edit = $form_state['values'];
  $form_values = array();
  foreach ($edit as $delta => $value) {

    // If this is some form item we don't care about, skip it.
    if (!is_array($value) || !is_numeric($delta)) {
      continue;
    }
    $value['calc'] = trim($value['calc']);
    if (empty($value['calc'])) {

      // remove blank fields, don't save them
      if (!empty($value['cid'])) {
        db_delete('views_calc_fields')
          ->condition('cid', $value['cid'])
          ->execute();
      }
    }
    else {
      $tables = array();
      $form_values[$delta]['cid'] = $value['cid'];
      $form_values[$delta]['label'] = $value['label'];
      $form_values[$delta]['format'] = $value['format'];
      $form_values[$delta]['custom'] = $value['custom'];
      $form_values[$delta]['calc'] = $value['calc'];
      $form_values[$delta]['base'] = $value['base'];

      // Substitute field names back into the calculation.
      $matches = array();
      $base = $value['base'];
      foreach (_views_calc_substitutions($base) as $key => $value) {
        $label_patterns[] = "`(^|[^\\\\\\\\])" . preg_quote($value) . "`";
        $value_patterns[] = "`(^|[^\\\\\\\\])" . preg_quote($key) . "`";
        $repl[] = '${1}' . $key;
      }
      $form_values[$delta]['calc'] = preg_replace($label_patterns, $repl, $form_values[$delta]['calc']);

      // Extract the fields and table names from the calculation.
      $tables = array();
      $fields = array();
      foreach ($value_patterns as $pattern) {
        if (preg_match($pattern, $form_values[$delta]['calc'], $results)) {
          $fields[trim($results[0])] = trim($results[0]);
          $tmp = explode('.', trim($results[0]));
          if (trim($tmp[0])) {
            $tables[trim($tmp[0])] = trim($tmp[0]);
          }
        }
      }
      $form_values[$delta]['tablelist'] = implode(',', $tables);
      $form_values[$delta]['fieldlist'] = implode(',', $fields);
    }
  }
  foreach ((array) $form_values as $delta => $value) {
    if (empty($value['cid'])) {
      drupal_write_record('views_calc_fields', $value);
    }
    else {
      drupal_write_record('views_calc_fields', $value, array(
        'cid',
      ));
    }
  }
  views_invalidate_cache();
  drupal_set_message(t('Views Calc fields were updated.'));
}

/**
 * Wrapper function to make sure this function will always work.
 */
function views_calc_views_fetch_fields($base, $type) {
  if (!module_exists('views')) {
    return array();
  }
  require_once DRUPAL_ROOT . '/' . drupal_get_path('module', 'views') . '/includes/admin.inc';
  return views_fetch_fields($base, $type);
}

/**
 *  Field substitutions for calculations.
 */
function _views_calc_substitutions($base = 'node') {

  // We retrieve fields of type 'filter' instead of 'field'
  // because filters have the actual table columns we might
  // do calculations on, while fields may have only a nid.
  $fields = views_calc_views_fetch_fields($base, 'filter');
  $substitutions['node.nid'] = '%Node.nid';
  $substitutions['node.uid'] = '%Node.uid';
  foreach ($fields as $key => $field) {

    // For now, omit calculated fields from available fields list.
    // Doing caculations on calculated fields will require some
    // complex additional logic, especially if they are nested
    // several levels deep.
    if (substr($key, 0, 4) != '.cid') {
      $substitutions[$key] = '%' . str_replace(' ', '', $key);
    }
  }
  return $substitutions;
}

/**
 * Views calc fields result object.
 */
function _views_calc_fields() {
  $query = db_select('views_calc_fields', 'vcf');
  $query
    ->fields('vcf', array(
    'cid',
    'label',
    'calc',
    'tablelist',
    'fieldlist',
    'format',
    'custom',
    'base',
  ));
  $result = $query
    ->execute();
  return $result;
}

/**
 * An array of allowable calculation values.
 */
function _views_calc_replacements() {
  $operators = array_filter(_views_calc_operators(), 'trim');
  $numbers = range(0, 9);
  return array_merge($operators, $numbers);
}

/**
 * FAPI export_form.
 *
 * Field export form.
 */
function views_calc_export_form($form, &$form_state) {
  $fields = _views_calc_fields();
  $string = '';
  foreach ($fields as $field) {
    $base = $field->base;
    $substitutions = _views_calc_substitutions($base);
    $field->calc = strtr($field->calc, $substitutions);
    $string .= "\$fields[] = " . var_export((array) $field, TRUE) . ";\n";
  }
  $form['#prefix'] = t('This form will export Views Calc custom fields.');
  $form['macro'] = array(
    '#type' => 'textarea',
    '#rows' => 20,
    '#title' => t('Export data'),
    '#default_value' => $string,
    '#description' => t('This is an export of the custom Views Calc fields. Paste this text into a Views Calc import box to import these fields into another installation. This will only work if the other installation uses the same base tables required by these fields.'),
  );
  return $form;
}

/**
 * FAPI import_form.
 *
 * Field import form.
 */
function views_calc_import_form($form, &$form_state, $type_name = '') {
  $form['#prefix'] = t('This form will import Views Calc custom fields.');
  $form['macro'] = array(
    '#type' => 'textarea',
    '#rows' => 20,
    '#title' => t('Import data'),
    '#required' => TRUE,
    '#description' => t('Paste the text created by a Views Calc export into this field.'),
  );
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Import'),
  );

  // Read in a file if there is one and set it as the default macro value.
  if (isset($_REQUEST['macro_file']) && ($file = file_get_contents($_REQUEST['macro_file']))) {
    $form['macro']['#default_value'] = $file;
    if (isset($_REQUEST['type_name'])) {
      $form['type_name']['#default_value'] = $_REQUEST['type_name'];
    }
    $form['#prefix'] .= '<p class="error">' . t('A file has been pre-loaded for import.') . '</p>';
  }
  $form_state['#redirect'] = 'admin/config/views_calc';
  return $form;
}

/**
 * FAPI import_form submit.
 *
 * Submit handler for import form.
 */
function views_calc_import_form_submit($form, &$form_state) {
  $form_values = $form_state['values'];
  $fields = NULL;

  // Use '@' to suppress errors about undefined constants in the macro.
  @eval($form_values['macro']);
  if (empty($fields) || !is_array($fields)) {
    return;
  }
  foreach ($fields as $delta => $field) {

    // Don't over-write existing fields, create new ones.
    $fields[$delta]['cid'] = NULL;
  }

  // Run the values thru drupal_execute() so they are properly validated.
  $form_state = array(
    'values' => $fields,
  );
  drupal_form_submit('views_calc_fields_form', $form_state);
}
function views_process_dependency($element, $edit, &$form_state, &$form) {
  static $dependencies;
  if (isset($element['#dependency']) && !isset($dependencies[$element['#id']])) {
    if (!isset($element['#dependency_count'])) {
      $element['#dependency_count'] = 1;
    }
    if (!empty($form_state['ajax'])) {
      $form_state['js settings']['viewsAjax']['formRelationships'][$element['#id']] = array(
        'num' => $element['#dependency_count'],
        'values' => $element['#dependency'],
      );
    }
    else {
      views_add_js('dependent');
      $options['viewsAjax']['formRelationships'][$element['#id']] = array(
        'num' => $element['#dependency_count'],
        'values' => $element['#dependency'],
      );
      drupal_add_js($options, 'setting');
    }
    $dependencies[$element['#id']] = TRUE;
  }
  return $element;
}

/**
 * Edge case if the same field has been used more than once,
 * most likely with relationships since you could show title
 * for each relationship. Results in a field like title becoming
 * title_1.
 */
function views_calc_adj_full_alias($alias, $field) {
  if ($field->field != $field->options['id']) {
    $addition = str_replace($field->field, '', $field->options['id']) . '_';
    $ext_alias = $field->relationship . $addition . $field->real_field;
    $ext_alias = views_calc_shorten($ext_alias);
    return $ext_alias;
  }
  return $alias;
}

/**
 * Make sure our reconstructed aliases don't get too long
 */
function views_calc_shorten($alias) {
  return strtolower(substr($alias, 0, 60));
}

Functions

Namesort descending Description
views_calc_adj_full_alias Edge case if the same field has been used more than once, most likely with relationships since you could show title for each relationship. Results in a field like title becoming title_1.
views_calc_export_form FAPI export_form.
views_calc_fields_form FAPI fields_form.
views_calc_fields_form_submit FAPI fields_form submit.
views_calc_fields_form_validate FAPI fields_form validate.
views_calc_field_form_item A form element for an individual calculated field.
views_calc_help Implements hook_help().
views_calc_import_form FAPI import_form.
views_calc_import_form_submit FAPI import_form submit.
views_calc_menu Implements hook_menu().
views_calc_permission Implements hook_permission().
views_calc_settings_form FAPI settings_form.
views_calc_settings_form_submit FAPI settings_form submit.
views_calc_shorten Make sure our reconstructed aliases don't get too long
views_calc_theme Implements hook_theme().
views_calc_views_api Implements hook_views_api().
views_calc_views_fetch_fields Wrapper function to make sure this function will always work.
views_process_dependency
_views_calc_calc_options Column calculation alternatives.
_views_calc_fields Views calc fields result object.
_views_calc_format_options Result format options.
_views_calc_operators Default SQL operator alternatives.
_views_calc_replacements An array of allowable calculation values.
_views_calc_substitutions Field substitutions for calculations.