<?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.
 */

/**
 * Implementation of hook_help().
 */
function views_calc_help($section) {
  switch ($section) {
    case 'admin/settings/views_calc':
    case 'admin/settings/views_calc/fields':
    case 'admin/settings/views_calc/columns':
      return t('<p>Set up calculation fields and column total fields. Calculation fields will be displayed in the views fields list and can be added to any view. '.
      'Column totals can be created for any view with a page type of \'Views Calc Table\'.</p>');
    case 'admin/settings/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 only work on views which have been given a page type of \'Views Calc Table\'.</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('SUM' => 'Sum', 'COUNT' => 'Count', 'AVG' => 'Average', 'MIN' => 'Minimum', 'MAX' => '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;
}

/**
 * Implementation of hook_perm().
 *
 * 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_perm() {
  return array('create views calc', 'administer views calc');
}

function views_calc_menu($may_cache) {
  include_once(drupal_get_path('module', 'views') .'/views.module');
  include_once(drupal_get_path('module', 'views') .'/views_cache.inc');

  $items = array();
  if (!$may_cache) {
    // make sure the table gets proper spreadsheet-like themeing
    drupal_add_css(drupal_get_path('module', 'views_calc') .'/views_calc.css');
  }
  else {
    $items[] = array(
      'title' => t('Views Calc'),
      'path' => 'admin/settings/views_calc',
      'description' => t('Set Views Calc fields and columns.'),
      'type' => MENU_NORMAL_ITEM,
      'weight' => 10,
      'priority' => 1,
      'callback' => 'drupal_get_form',
      'callback arguments' => array('views_calc_fields_form'),
      'access' => user_access('create views calc') && user_access('administer views'),
      );
    $items[] = array('path' => 'admin/settings/views_calc/fields',
      'title' => t('Fields'),
      'type' => MENU_DEFAULT_LOCAL_TASK,
      'weight' => 5,
      'priority' => 1,
      'callback' => 'drupal_get_form',
      'callback arguments' => array('views_calc_fields_form'),
      'access' => user_access('create views calc') && user_access('administer views'),
      );
    $items[] = array('path' => 'admin/settings/views_calc/columns',
      'title' => t('Columns'),
      'type' => MENU_LOCAL_TASK,
      'weight' => 10,
      'priority' => 1,
      'callback' => 'drupal_get_form',
      'callback arguments' => array('views_calc_cols_form'),
      'access' => user_access('create views calc') && user_access('administer views'),
      );
    $items[] = array(
      'path' => 'admin/settings/views_calc/settings',
      'title' => t('Settings'),
      'type' => MENU_LOCAL_TASK,
      'weight' => 10,
      'priority' => 1,
      'callback' => 'drupal_get_form',
      'callback arguments' => array('views_calc_settings_form'),
      'access' => user_access('administer views calc'),
      );


  }
  return $items;
}

/**
 *  Implementation of hook_settings()
 */
function views_calc_settings_form() {
  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;
}

function views_calc_settings_form_submit($form_id, $form_values) {
  variable_set('views_calc_operators', $form_values['views_calc_operators']);
}

/**
 * Views Calc Fields tab on views list.
 */
function views_calc_fields_form() {
  $i = 0;
  $substitutions = _views_calc_substitutions();
  $reverse       = array_flip($substitutions);

  // display current views calcs fields
  $fields = _views_calc_fields();
  while ($field = db_fetch_array($fields)) {
    $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">'. theme('item_list', _views_calc_substitutions()) .'</div></div>';
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Save'),
    );
  return $form;
}

/**
 * Views Calc Columns tab on views list.
 */
function views_calc_cols_form() {
  $form = array();

  $views = _views_calc_view_views();
  while ($val = db_fetch_array($views)) {
    if ($vid = $val['vid']) {
      $view = views_get_view($vid);
      $fields = $view->field;
      $options = array('' => '');
      foreach ($fields as $key => $field) {
        $options[$field['fullname']] = $field['label'];
      }
      $calcs = _views_calc_calc_options();
      $form['views_calc'][$vid] = array(
        '#type' => 'fieldset',
        '#title' => t('View: '). $view->name,
        '#collapsible' => TRUE,
        '#collapsed' => FALSE,
        );
      $form['views_calc'][$vid]['views_calc_'.$vid.'_col'] = array(
        '#type' => 'select',
        '#title' => t('Columns for column calculation'),
        '#default_value' => variable_get('views_calc_'.$vid.'_col', ''),
        '#options' => $options,
        '#multiple' => TRUE,
        '#description' => t('Add a calculation at the foot of each these columns.'),
        );
      $form['views_calc'][$vid]['views_calc_'.$vid.'_col_calc'] = array(
        '#type' => 'select',
        '#title' => t('Calculations to perform on columns'),
        '#default_value' => variable_get('views_calc_'.$vid.'_col_calc', ''),
        '#options' => $calcs,
        '#multiple' => TRUE,
        );
    }
  }

  if (sizeof($form) == 0) {
    drupal_set_message(t('There are no views with the Views Calc page type. Create a view with that page type, then return here to set up column calculations.'), 'error');
  }
  else {
    $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) {
    $form['group'][$i] = array(
     '#type' => 'fieldset',
     '#tree' => TRUE,
     '#title' => t('Field: ') . ($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]['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()) .'.</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;
}

/**
 *  Validate the views calc settings
 */
function views_calc_fields_form_validate($form_id, $form_values) {
  $edit = $form_values;
  foreach ($edit as $delta => $item) {
    if ($item['calc'] == '' || !is_numeric($delta)) {
      // remove blank fields, don't save them
      unset($form_values[$delta]);
    } else {
      // Remove all valid values from calc, if anything is left over, it is invalid.

      // First, remove all field names.
      $repl = array();
      $patterns = array();
      foreach (_views_calc_substitutions() 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'])));
      }
    }
  }
}

/**
 *  Save the views calc field settings
 */
function views_calc_fields_form_submit($form_id, $form_values) {
  $edit = $form_values;
  foreach ($edit as $delta => $value) {
    if ($value['calc'] == '' || !is_numeric($delta)) {
      // remove blank fields, don't save them
      unset($form_values[$delta]);

    } else {
      $tables = array();
      $form_values[$delta]['label']  = $value['label'];
      $form_values[$delta]['format'] = $value['format'];
      $form_values[$delta]['custom'] = $value['custom'];
      $form_values[$delta]['calc']   = $value['calc'];

      // Substitute field names back into the calculation.
      $matches = array();
      foreach (_views_calc_substitutions() 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]);
          $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 ($form_values as $delta => $value) {
    if ($value['cid'] == 0) {
      $cid = db_next_id('views_calc_field');
      $qrytext = "INSERT INTO {views_calc_fields} SET cid = $cid, ";
      $where   = '';
    } else {
      $qrytext = "UPDATE {views_calc_fields} SET ";
      $where   = " WHERE cid = ". $value['cid'];
    }
    $qrytext = $qrytext ."label = '%s', format = '%s', custom = '%s', calc = '%s', tablelist = '%s', fieldlist = '%s' ". $where;
    db_query($qrytext, 'ViewsCalc: '. $value['label'], $value['format'], $value['custom'], $value['calc'], $value['tablelist'], $value['fieldlist']);
  }
  views_invalidate_cache();
  drupal_set_message(t('Views Calc fields were updated.'));
}

/**
 *  Save the views calc column settings.
 */
function views_calc_cols_form_submit($form_id, $form_values) {
  $edit = $form_values;
  foreach ($edit as $name => $value) {
    variable_set($name, $value);
  }
  drupal_set_message(t('Views Calc columns were updated.'));
}

/**
 *  Implementation of hook_views_tables.
 *
 *  Add calc fields to views field list.
 */
function views_calc_views_tables() {
  $tables['views_calc'] = array(
    'name' => 'node',
    'join' => array(
      'left' => array(
        'table' => 'node',
        'field' => 'nid'
      ),
      'right' => array(
        'field' => 'nid'
      ),
    ),
  );
  // insert all available views_calc fields into field list as placeholders
  // using NULL for table name so views doesn't try to prepend the calc with a tablename
  $results = _views_calc_fields();
  while($field = db_fetch_array($results)) {
    $tables[NULL]['fields']['cid'. $field['cid']] = array(
      'name'          => t($field['label']),
      'notafield'     => true,
      'sortable'      => true,
      'query_handler' => 'views_calc_query_handler',
      'handler'       => 'views_calc_field_handler',
      'cid'           => $field['cid'],
      'format'        => $field['format'],
      'custom'        => $field['custom'],
      );
  }
  return $tables;
}

/**
 *  Query handler to update query
 *  use handler insert a real field with the calc into the query
 */
function views_calc_query_handler(&$field, &$fieldinfo, &$query) {
  $results = _views_calc_fields();
  while ($calc_field = db_fetch_array($results)) {
    if ($fieldinfo['cid'] == $calc_field['cid']) {
      foreach (explode(',', $calc_field['tablelist']) as $table) {
        $query->ensure_table($table);
      }
      $query->add_field("(". $calc_field['calc'] .") AS _cid". $calc_field['cid'], NULL);
    }
  }
}

/**
 *  The field handler.
 *  Used to format the calculation results
 */
function views_calc_field_handler(&$field, &$fieldinfo, $value, $data) {
  return _views_calc_format($value, $field['format'], $field['custom']);
}

function _views_calc_format($value, $format, $custom) {
  $formats = _views_calc_format_options();
  $format = $formats[$format];
  $tmp = explode(':', $format);
  $function = trim($tmp[0]);
  $vars     = $tmp[1];
  if ($function == 'custom') {
    $tmp = explode(':', $custom);
    $function = trim($tmp[0]);
    $vars     = $tmp[1];
  }
  if (empty($function) || $function == 'none') {
    $function = 'check_plain';
  }
  return $function($value, $vars);
}

/**
 *  Implementation of hook_views_style_plugins()
 */
function views_calc_views_style_plugins() {
  return array(
    'views_calc' => array(
      'name' => t('Views Calc Table'),
      'theme' => 'views_calc_table',
      'validate' => 'views_ui_plugin_validate_table',
      'needs_fields' => TRUE,
      'needs_table_header' => TRUE,
      ),
    );
}

/**
 *  Implementation of hook_views_pre_view()
 */
function views_calc_views_pre_view(&$view, &$items) {
  if (!(_views_calc_is_calc_view($view) && _views_calc_has_items($items))) return;

  // Add summary rows to the table.
  $cols       = (array) variable_get('views_calc_'.$view->vid.'_col','');
  $col_calc   = (array) variable_get('views_calc_'.$view->vid.'_col_calc','');
  $summary_view = drupal_clone($view);
  foreach ($col_calc as $calc) {
    $summary_view->views_calc_calculation = $calc;
    $summary_view->is_cacheable = FALSE;
    $values = views_build_view('items', $summary_view, $summary_view->args);
    $items[$calc] = $values['items'][0];
  }
}

/**
 *  Views calc fields result object
 */
function _views_calc_fields() {
  return db_query("SELECT * FROM {views_calc_fields}");
}

/**
 *  Views calc views result object.
 */
function _views_calc_view_views() {
 return db_query("SELECT * FROM {view_view} WHERE page_type = 'views_calc'");
}

/**
 *  Field substitutions for calculations.
 */
function _views_calc_substitutions() {
  $fields = _views_get_fields(true);
  $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(' ', '', $field);
    }
  }
  return $substitutions;
}

/**
 *  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);
}

/**
 *  Test if this is a views_calc view.
 */
function _views_calc_is_calc_view($view) {
  if (!$view) return false;
  if ($view->vid && $view->page_type == 'views_calc') {
    return true;
  }
  return false;
}

/**
 *  Test that this is a full view, not just a summary
 */
function _views_calc_has_items($items) {
  if ($items && !$items[0]->num_nodes) {
    return true;
  } else {
    return false;
  }
}

/**
 * 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.
 */
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;
  }
}

/**
 * Views Calc summary display.
 *
 * The summary view should be the normal summary view.
 */
function theme_views_calc_table_summary(&$view, $type, $level, &$items, $args) {
  return theme('views_summary', $view, $type, $level, $items, $args);
}

/**
 *  Override the views table theme to add formatting
 *  for a more spreadsheet-like appearance.
 */
function theme_views_calc_table($view, $items, $type) {
  $fields     = _views_get_fields();
  foreach ($items as $delta => $node) {
    $row = array();
    $set_label = FALSE;
    foreach ($view->field as $delta2 => $field) {
      // Format the calculation rows.
      if (!is_numeric($delta)) {
        if (!empty($node->$field['queryname'])) {
          $cell['data'] = theme('views_handle_field', $fields, $field, $node);
        }
        elseif (!$set_label) {
          $cell['data'] = t($delta);
          $set_label = TRUE;
        }
        else {
          $cell['data'] = '&nbsp;';
        }
        // give numeric data its own class
        if (is_numeric($cell['data'])) {
          $cell['class'] = 'view-footer-number';
        } else {
          $cell['class'] = 'view-footer';
        }
        $cell['id'] = "view-field-$field[queryname]-$delta";

      // Format the main table rows.
      } else {
        // Format calculated row values.
        $cell['data'] = theme('views_handle_field', $fields, $field, $node);

        // Give numeric data its own class.
        if (is_numeric($cell['data'])) {
          if (strstr($field['queryname'], 'row_')) {
            // format calculation columns
            $cell['class'] = 'view-total-number';
          } else {
            // format main table columns
            $cell['class'] = 'view-field-number';
          }
        } else {
          if (strstr($field['queryname'], 'row_')) {
            // format calculation columns
            $cell['class'] = 'view-total';

          } else {
            // format main table columns
            $cell['class'] = 'view-field';
          }
        }
        $cell['id'] = "view-field-$field[queryname]";
      }
      $row[] = $cell;
    }
    $rows[] = $row;
  }
  return theme('table', $view->table_header, $rows);
}