franck@0: <?php
franck@0: 
franck@0: /**
franck@0:  * @file
franck@0:  * This module will allow you to add calculated fields to views tables
franck@0:  * and compute (SUM, COUNT, AVG, etc) columns of numeric data in a views table.
franck@0:  */
franck@0: function views_calc_views_api() {
franck@0:   return array(
franck@0:     'api' => 2,
franck@0:     'path' => drupal_get_path('module', 'views_calc'),
franck@0:   );
franck@0: }
franck@0: 
franck@0: function views_calc_theme() {
franck@0:   $path = drupal_get_path('module', 'views_calc');
franck@0:   return array(
franck@0:     'views_calc_ui_table' => array(
franck@0:       'arguments' => array('form' => NULL),
franck@0:       'file' => 'theme.inc',
franck@0:     ),
franck@0:   );
franck@0: }
franck@0: 
franck@0: /**
franck@0:  * Implementation of hook_help().
franck@0:  */
franck@0: function views_calc_help($section, $arg) {
franck@0:   switch ($section) {
franck@0:     case 'admin/settings/views_calc':
franck@0:     case 'admin/settings/views_calc/fields':
franck@0:       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>');
franck@0:     case 'admin/settings/views_calc/settings':
franck@0:       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.');
franck@0:     case 'admin/help#views_calc':
franck@0:       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>');
franck@0:   }
franck@0: }
franck@0: 
franck@0: /**
franck@0:  * Default SQL operator alternatives.
franck@0:  *
franck@0:  * The ones allowed in this system are stored in the
franck@0:  * variable views_calc_operators, and can be changed
franck@0:  * at admin/settings/views_calc.
franck@0:  *
franck@0:  */
franck@0: function _views_calc_operators() {
franck@0:   $default = array('+', '-', '*', '/', '(', ')', ',', "'", 'CONCAT', 'MIN', 'MAX', 'ROUND', 'NOW()');
franck@0:   $operators = variable_get('views_calc_operators', implode("\n", $default));
franck@0:   return explode("\n", $operators);
franck@0: 
franck@0: }
franck@0: 
franck@0: /**
franck@0:  *  Column calculation alternatives
franck@0:  */
franck@0: function _views_calc_calc_options() {
franck@0:   return array('COUNT' => 'Count', 'SUM' => 'Sum', 'AVG' => 'Average', 'MIN' => 'Minimum', 'MAX' => 'Maximum');
franck@0: }
franck@0: 
franck@0: /**
franck@0:  *  Result format options
franck@0:  */
franck@0: function _views_calc_format_options() {
franck@0:   $options = array(
franck@0:     'none' => '',
franck@0:     'integer' => 'intval',
franck@0:     'decimal (1)' => 'number_format:1',
franck@0:     'decimal (2)' => 'number_format:2',
franck@0:     'shortdate' => 'format_date:small',
franck@0:     'mediumdate' => 'format_date',
franck@0:     'longdate' => 'format_date:large',
franck@0:     'custom' => '',
franck@0:     );
franck@0:   return $options;
franck@0: }
franck@0: 
franck@0: /**
franck@0:  * Implementation of hook_perm().
franck@0:  *
franck@0:  * The permission 'administer views calc' has rights to alter the SQL
franck@0:  * operators that can be used in calculations.
franck@0:  *
franck@0:  * The permission 'create views calc' has rights to create calculated
franck@0:  * fields and set calculation columns on views.
franck@0:  */
franck@0: function views_calc_perm() {
franck@0:   return array('create views calc', 'administer views calc');
franck@0: }
franck@0: 
franck@0: function views_calc_menu() {
franck@0: 
franck@0:   $items = array();
franck@0:   $items['admin/settings/views_calc'] = array(
franck@0:     'title' => t('Views Calc'),
franck@0:     'description' => t('Set Views Calc fields and columns.'),
franck@0:     'type' => MENU_NORMAL_ITEM,
franck@0:     'weight' => 10,
franck@0:     'priority' => 1,
franck@0:     'page callback' => 'drupal_get_form',
franck@0:     'page arguments' => array('views_calc_fields_form'),
franck@0:     'access arguments' => array('create views calc'),
franck@0:     );
franck@0:   $items['admin/settings/views_calc/fields'] = array(
franck@0:     'title' => t('Fields'),
franck@0:     'type' => MENU_DEFAULT_LOCAL_TASK,
franck@0:     'weight' => 5,
franck@0:     'priority' => 1,
franck@0:     'page callback' => 'drupal_get_form',
franck@0:     'page arguments' => array('views_calc_fields_form'),
franck@0:     'access arguments' => array('create views calc'),
franck@0:     );
franck@0:   $items['admin/settings/views_calc/settings'] = array(
franck@0:     'title' => t('Settings'),
franck@0:     'type' => MENU_LOCAL_TASK,
franck@0:     'weight' => 10,
franck@0:     'priority' => 1,
franck@0:     'page callback' => 'drupal_get_form',
franck@0:     'page arguments' => array('views_calc_settings_form'),
franck@0:     'access arguments' => array('administer views calc'),
franck@0:     );
franck@0:   return $items;
franck@0: }
franck@0: 
franck@0: /**
franck@0:  *  Implementation of hook_settings()
franck@0:  */
franck@0: function views_calc_settings_form() {
franck@0:   drupal_set_title(t('Views Calc'));
franck@0:   $operators = _views_calc_operators();
franck@0:   $form['views_calc_operators'] = array(
franck@0:     '#type' => 'textarea',
franck@0:     '#default_value' => implode("\n", $operators),
franck@0:     '#title' => t('Allowable functions and operators'),
franck@0:     '#rows' => intval(sizeof($operators) + 2),
franck@0:     );
franck@0:   $form['submit'] = array(
franck@0:     '#type' => 'submit',
franck@0:     '#value' => t('Save'),
franck@0:     );
franck@0:   return $form;
franck@0: }
franck@0: 
franck@0: function views_calc_settings_form_submit($form, &$form_state) {
franck@0:   $form_values = $form_state['values'];
franck@0:   variable_set('views_calc_operators', $form_values['views_calc_operators']);
franck@0: }
franck@0: 
franck@0: /**
franck@0:  * Views Calc Fields tab on views list.
franck@0:  */
franck@0: function views_calc_fields_form() {
franck@0:   $i = 0;
franck@0:   $substitutions = _views_calc_substitutions();
franck@0:   $reverse       = array_flip($substitutions);
franck@0: 
franck@0:   // display current views calcs fields
franck@0:   $fields = _views_calc_fields();
franck@0:   while ($field = db_fetch_array($fields)) {
franck@0:     $form[] = views_calc_field_form_item($i, $field, $substitutions);
franck@0:     $i++;
franck@0:   }
franck@0:   // add blank fields for more calcs
franck@0:   for ($x = $i + 1; $x < $i + 2; $x++) {
franck@0:     $field = array();
franck@0:     $form[] = views_calc_field_form_item($i, $field, $substitutions);
franck@0:   }
franck@0:   $form['#prefix'] = '<div class="views-calc-field-settings">';
franck@0:   $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>';
franck@0:   $form['submit'] = array(
franck@0:     '#type' => 'submit',
franck@0:     '#value' => t('Save'),
franck@0:     );
franck@0:   return $form;
franck@0: }
franck@0: 
franck@0: /**
franck@0:  * A form element for an individual calculated field.
franck@0:  */
franck@0: function views_calc_field_form_item($i, $field, $substitutions) {
franck@0:   if (empty($field)) {
franck@0:     $field = array('cid' => 0, 'label' => '', 'tablelist' => '', 'calc' => '', 'format' => '', 'custom' => '');
franck@0:   }
franck@0:   $form['group'][$i] = array(
franck@0:     '#type' => 'fieldset',
franck@0:     '#tree' => TRUE,
franck@0:     '#title' => t('Field: ') . !empty($field['label']) ? $field['label'] : t('New'),
franck@0:     '#collapsible' => TRUE,
franck@0:     '#collapsed'   => FALSE,
franck@0:     );
franck@0:   $form['group'][$i]['cid'] = array(
franck@0:     '#type' => 'hidden',
franck@0:     '#value' => intval($field['cid']),
franck@0:     );
franck@0:   $form['group'][$i]['tablelist'] = array(
franck@0:     '#type' => 'hidden',
franck@0:     '#value' => $field['tablelist'],
franck@0:     );
franck@0:   $form['group'][$i]['label'] = array(
franck@0:     '#type' => 'textfield',
franck@0:     '#title' => t('Label'),
franck@0:     '#field_prefix' => 'ViewsCalc: ',
franck@0:     '#default_value' => str_replace('ViewsCalc: ', '', $field['label']),
franck@0:     '#description' => t('The views field name for this field (i.e. Views Calc: My Calculation).'),
franck@0:     );
franck@0:   $form['group'][$i]['calc'] = array(
franck@0:     '#type' => 'textarea',
franck@0:     '#title' => t('Calculation'),
franck@0:     '#default_value' => strtr($field['calc'], $substitutions),
franck@0:     '#description' => t('<p>The query operation to be performed, using numbers, field substitutions, and '. implode(' ', _views_calc_operators()) .'.</p>'),
franck@0:     );
franck@0:   $form['group'][$i]['format'] = array(
franck@0:     '#type' => 'select',
franck@0:     '#title' => t('Format'),
franck@0:     '#default_value' => $field['format'],
franck@0:     '#options' => drupal_map_assoc(array_keys(_views_calc_format_options())),
franck@0:     '#description' => t('The format of the result of this calculation.'),
franck@0:     );
franck@0:   $form['group'][$i]['custom'] = array(
franck@0:     '#type' => 'textfield',
franck@0:     '#title' => t('Custom function'),
franck@0:     '#default_value' => $field['custom'],
franck@0:     '#description' => t('The function to call for a custom format.'),
franck@0:     );
franck@0:   return $form;
franck@0: }
franck@0: 
franck@0: /**
franck@0:  *  Validate the views calc settings
franck@0:  */
franck@0: function views_calc_fields_form_validate($form, &$form_state) {
franck@0:   $form_values = $form_state['values'];
franck@0:   $edit = $form_values;
franck@0:   foreach ($edit as $delta => $item) {
franck@0:     if ($item['calc'] == '' || !is_numeric($delta)) {
franck@0:       // remove blank fields, don't save them
franck@0:       unset($form_values[$delta]);
franck@0:     } else {
franck@0:       // Remove all valid values from calc, if anything is left over, it is invalid.
franck@0: 
franck@0:       // First, remove all field names.
franck@0:       $repl = array();
franck@0:       $patterns = array();
franck@0:       foreach (_views_calc_substitutions() as $key => $value) {
franck@0:         $key = trim($value);
franck@0:         $count = strlen($value);
franck@0:         $replace = preg_quote($value);
franck@0:         $patterns[] = "`(^|[^\\\\\\\\])". $replace ."`";
franck@0:         $repl[] = '${1}';
franck@0:       }
franck@0:       $remaining = trim(preg_replace($patterns, $repl, $item['calc']));
franck@0:       // Next, remove functions and numbers.
franck@0:       $repl = array();
franck@0:       $patterns = array();
franck@0:       foreach (_views_calc_replacements() as $value) {
franck@0:         $patterns[] = "`(^|[^\\\\\\\\])". preg_quote(trim($value)) ."`";
franck@0:         $repl[] = '${1}';
franck@0:       }
franck@0:       $remaining = trim(preg_replace($patterns, $repl, $remaining));
franck@0:       if (!empty($remaining)) {
franck@0:         form_set_error($form_values[$delta]['calc'], t('The values %remaining in %field are not allowed.', array('%remaining' => $remaining, '%field' => $item['label'])));
franck@0:       }
franck@0:     }
franck@0:   }
franck@0: }
franck@0: 
franck@0: /**
franck@0:  *  Save the views calc field settings
franck@0:  */
franck@0: function views_calc_fields_form_submit($form, &$form_state) {
franck@0:   $form_values = $form_state['values'];
franck@0:   $edit = $form_values;
franck@0:   foreach ($edit as $delta => $value) {
franck@0:     if ($value['calc'] == '' || !is_numeric($delta)) {
franck@0:       // remove blank fields, don't save them
franck@0:       unset($form_values[$delta]);
franck@0: 
franck@0:     } 
franck@0:     else {
franck@0:       $tables = array();
franck@0:       $form_values[$delta]['label']  = $value['label'];
franck@0:       $form_values[$delta]['format'] = $value['format'];
franck@0:       $form_values[$delta]['custom'] = $value['custom'];
franck@0:       $form_values[$delta]['calc']   = $value['calc'];
franck@0: 
franck@0:       // Substitute field names back into the calculation.
franck@0:       $matches = array();
franck@0:       foreach (_views_calc_substitutions() as $key => $value) {
franck@0:         $label_patterns[] = "`(^|[^\\\\\\\\])". preg_quote($value) ."`";
franck@0:         $value_patterns[] = "`(^|[^\\\\\\\\])". preg_quote($key) ."`";
franck@0:         $repl[] = '${1}'. $key;
franck@0:       }
franck@0:       $form_values[$delta]['calc'] = preg_replace($label_patterns, $repl, $form_values[$delta]['calc']);
franck@0: 
franck@0:       // Extract the fields and table names from the calculation.
franck@0:       $tables = array();
franck@0:       $fields = array();
franck@0:       foreach ($value_patterns as $pattern) {
franck@0:         if (preg_match($pattern, $form_values[$delta]['calc'], $results)) {
franck@0:           $fields[] = trim($results[0]);
franck@0:           $tmp = explode('.', trim($results[0]));
franck@0:           if (trim($tmp[0])) {
franck@0:             $tables[trim($tmp[0])] = trim($tmp[0]);
franck@0:           }
franck@0:         }
franck@0:       }
franck@0:       $form_values[$delta]['tablelist'] = implode(',', $tables);
franck@0:       $form_values[$delta]['fieldlist'] = implode(',', $fields);
franck@0:     }
franck@0:   }
franck@0:   foreach ($form_values as $delta => $value) {
franck@0:     if ($value['cid'] == 0) {
franck@0:       drupal_write_record('views_calc_fields', $value);
franck@0:     } 
franck@0:     else {
franck@0:       drupal_write_record('views_calc_fields', $value, array('cid'));
franck@0:     }
franck@0:   }
franck@0:   views_invalidate_cache();
franck@0:   drupal_set_message(t('Views Calc fields were updated.'));
franck@0: }
franck@0: 
franck@0: /**
franck@0:  * Wrapper function to make sure this function will always work.
franck@0:  */
franck@0: function views_calc_views_fetch_fields($base, $type) {
franck@0:   if (!module_exists('views')) {
franck@0:     return array();
franck@0:   }
franck@0:   require_once('./'. drupal_get_path('module', 'views') .'/includes/admin.inc');
franck@0:   return views_fetch_fields($base, $type);
franck@0: }
franck@0: 
franck@0: /**
franck@0:  *  Field substitutions for calculations.
franck@0:  */
franck@0: function _views_calc_substitutions($base = 'node') {
franck@0:   $fields = views_calc_views_fetch_fields($base, 'field');
franck@0:   $substitutions['node.nid'] = '%Node.nid';
franck@0:   $substitutions['node.uid'] = '%Node.uid';
franck@0:   foreach ($fields as $key => $field) {
franck@0:     // For now, omit calculated fields from available fields list.
franck@0:     // Doing caculations on calculated fields will require some
franck@0:     // complex additional logic, especially if they are nested
franck@0:     // several levels deep.
franck@0:     if (substr($key, 0, 4) != '.cid') {
franck@0:       $substitutions[$key] = '%'. str_replace(' ', '', $key);
franck@0:     }
franck@0:   }
franck@0:   return $substitutions;
franck@0: }
franck@0: 
franck@0: /**
franck@0:  *  Views calc fields result object
franck@0:  */
franck@0: function _views_calc_fields() {
franck@0:   return db_query("SELECT * FROM {views_calc_fields}");
franck@0: }
franck@0: 
franck@0: /**
franck@0:  *  An array of allowable calculation values.
franck@0:  */
franck@0: function _views_calc_replacements() {
franck@0:   $operators     = array_filter(_views_calc_operators(), 'trim');
franck@0:   $numbers       = range(0, 9);
franck@0:   return array_merge($operators, $numbers);
franck@0: }