<?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(
      'arguments' => array('form' => NULL),
      'file' => 'theme.inc',
    ),
  );
}

/**
 * Implements hook_help().
 */
function views_calc_help($section, $arg) {
  switch ($section) {
    case 'admin/settings/views_calc':
    case 'admin/settings/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/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 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_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'
  );
}

/**
 * Implements hook_menu().
 */
function views_calc_menu() {
  $items = array();

  $items['admin/settings/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/settings/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/settings/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/settings/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/settings/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() {
  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() {
  $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_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', $base_subs),
      '#collapsible' => TRUE,
      '#collapsed' => TRUE,
      );
    $help .= theme('fieldset', $fieldset);
  }

  // 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">'. $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 = array('cid' => 0, 'label' => '', 'tablelist' => '', 'calc' => '', 'format' => '', 'custom' => '');
  }
  require_once(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_query("DELETE FROM {views_calc_fields} WHERE cid=%d", $value['cid']);
      }
    } 
    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_get_path('module', 'views') .'/includes/admin.inc');
  return views_fetch_fields($base, $type);
}

/**
 *  Field substitutions for calculations.
 */
function _views_calc_substitutions($base = 'node') {
  $fields = views_calc_views_fetch_fields($base, 'field');
  $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() {
  return db_query("SELECT * FROM {views_calc_fields}");
}

/**
 * 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() {
  
  $fields = _views_calc_fields();
  $string = '';
  while ($field = db_fetch_array($fields)) {
    $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_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['#redirect'] = 'admin/settings/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_execute('views_calc_fields_form', $form_state);

}