Mercurial > defr > drupal > views_calc

annotate views_calc.module @ 0:0651c02e6ed7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
views_calc 1.3
author Franck Deroche <franck@defr.org>
date Wed, 05 Aug 2009 18:20:29 +0200
parents
children b0a976e17cc7
rev   line source
franck@0 1 <?php
franck@0 2
franck@0 3 /**
franck@0 4 * @file
franck@0 5 * This module will allow you to add calculated fields to views tables
franck@0 6 * and compute (SUM, COUNT, AVG, etc) columns of numeric data in a views table.
franck@0 7 */
franck@0 8 function views_calc_views_api() {
franck@0 9 return array(
franck@0 10 'api' => 2,
franck@0 11 'path' => drupal_get_path('module', 'views_calc'),
franck@0 12 );
franck@0 13 }
franck@0 14
franck@0 15 function views_calc_theme() {
franck@0 16 $path = drupal_get_path('module', 'views_calc');
franck@0 17 return array(
franck@0 18 'views_calc_ui_table' => array(
franck@0 19 'arguments' => array('form' => NULL),
franck@0 20 'file' => 'theme.inc',
franck@0 21 ),
franck@0 22 );
franck@0 23 }
franck@0 24
franck@0 25 /**
franck@0 26 * Implementation of hook_help().
franck@0 27 */
franck@0 28 function views_calc_help($section, $arg) {
franck@0 29 switch ($section) {
franck@0 30 case 'admin/settings/views_calc':
franck@0 31 case 'admin/settings/views_calc/fields':
franck@0 32 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 33 case 'admin/settings/views_calc/settings':
franck@0 34 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 35 case 'admin/help#views_calc':
franck@0 36 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 37 }
franck@0 38 }
franck@0 39
franck@0 40 /**
franck@0 41 * Default SQL operator alternatives.
franck@0 42 *
franck@0 43 * The ones allowed in this system are stored in the
franck@0 44 * variable views_calc_operators, and can be changed
franck@0 45 * at admin/settings/views_calc.
franck@0 46 *
franck@0 47 */
franck@0 48 function _views_calc_operators() {
franck@0 49 $default = array('+', '-', '*', '/', '(', ')', ',', "'", 'CONCAT', 'MIN', 'MAX', 'ROUND', 'NOW()');
franck@0 50 $operators = variable_get('views_calc_operators', implode("\n", $default));
franck@0 51 return explode("\n", $operators);
franck@0 52
franck@0 53 }
franck@0 54
franck@0 55 /**
franck@0 56 * Column calculation alternatives
franck@0 57 */
franck@0 58 function _views_calc_calc_options() {
franck@0 59 return array('COUNT' => 'Count', 'SUM' => 'Sum', 'AVG' => 'Average', 'MIN' => 'Minimum', 'MAX' => 'Maximum');
franck@0 60 }
franck@0 61
franck@0 62 /**
franck@0 63 * Result format options
franck@0 64 */
franck@0 65 function _views_calc_format_options() {
franck@0 66 $options = array(
franck@0 67 'none' => '',
franck@0 68 'integer' => 'intval',
franck@0 69 'decimal (1)' => 'number_format:1',
franck@0 70 'decimal (2)' => 'number_format:2',
franck@0 71 'shortdate' => 'format_date:small',
franck@0 72 'mediumdate' => 'format_date',
franck@0 73 'longdate' => 'format_date:large',
franck@0 74 'custom' => '',
franck@0 75 );
franck@0 76 return $options;
franck@0 77 }
franck@0 78
franck@0 79 /**
franck@0 80 * Implementation of hook_perm().
franck@0 81 *
franck@0 82 * The permission 'administer views calc' has rights to alter the SQL
franck@0 83 * operators that can be used in calculations.
franck@0 84 *
franck@0 85 * The permission 'create views calc' has rights to create calculated
franck@0 86 * fields and set calculation columns on views.
franck@0 87 */
franck@0 88 function views_calc_perm() {
franck@0 89 return array('create views calc', 'administer views calc');
franck@0 90 }
franck@0 91
franck@0 92 function views_calc_menu() {
franck@0 93
franck@0 94 $items = array();
franck@0 95 $items['admin/settings/views_calc'] = array(
franck@0 96 'title' => t('Views Calc'),
franck@0 97 'description' => t('Set Views Calc fields and columns.'),
franck@0 98 'type' => MENU_NORMAL_ITEM,
franck@0 99 'weight' => 10,
franck@0 100 'priority' => 1,
franck@0 101 'page callback' => 'drupal_get_form',
franck@0 102 'page arguments' => array('views_calc_fields_form'),
franck@0 103 'access arguments' => array('create views calc'),
franck@0 104 );
franck@0 105 $items['admin/settings/views_calc/fields'] = array(
franck@0 106 'title' => t('Fields'),
franck@0 107 'type' => MENU_DEFAULT_LOCAL_TASK,
franck@0 108 'weight' => 5,
franck@0 109 'priority' => 1,
franck@0 110 'page callback' => 'drupal_get_form',
franck@0 111 'page arguments' => array('views_calc_fields_form'),
franck@0 112 'access arguments' => array('create views calc'),
franck@0 113 );
franck@0 114 $items['admin/settings/views_calc/settings'] = array(
franck@0 115 'title' => t('Settings'),
franck@0 116 'type' => MENU_LOCAL_TASK,
franck@0 117 'weight' => 10,
franck@0 118 'priority' => 1,
franck@0 119 'page callback' => 'drupal_get_form',
franck@0 120 'page arguments' => array('views_calc_settings_form'),
franck@0 121 'access arguments' => array('administer views calc'),
franck@0 122 );
franck@0 123 return $items;
franck@0 124 }
franck@0 125
franck@0 126 /**
franck@0 127 * Implementation of hook_settings()
franck@0 128 */
franck@0 129 function views_calc_settings_form() {
franck@0 130 drupal_set_title(t('Views Calc'));
franck@0 131 $operators = _views_calc_operators();
franck@0 132 $form['views_calc_operators'] = array(
franck@0 133 '#type' => 'textarea',
franck@0 134 '#default_value' => implode("\n", $operators),
franck@0 135 '#title' => t('Allowable functions and operators'),
franck@0 136 '#rows' => intval(sizeof($operators) + 2),
franck@0 137 );
franck@0 138 $form['submit'] = array(
franck@0 139 '#type' => 'submit',
franck@0 140 '#value' => t('Save'),
franck@0 141 );
franck@0 142 return $form;
franck@0 143 }
franck@0 144
franck@0 145 function views_calc_settings_form_submit($form, &$form_state) {
franck@0 146 $form_values = $form_state['values'];
franck@0 147 variable_set('views_calc_operators', $form_values['views_calc_operators']);
franck@0 148 }
franck@0 149
franck@0 150 /**
franck@0 151 * Views Calc Fields tab on views list.
franck@0 152 */
franck@0 153 function views_calc_fields_form() {
franck@0 154 $i = 0;
franck@0 155 $substitutions = _views_calc_substitutions();
franck@0 156 $reverse = array_flip($substitutions);
franck@0 157
franck@0 158 // display current views calcs fields
franck@0 159 $fields = _views_calc_fields();
franck@0 160 while ($field = db_fetch_array($fields)) {
franck@0 161 $form[] = views_calc_field_form_item($i, $field, $substitutions);
franck@0 162 $i++;
franck@0 163 }
franck@0 164 // add blank fields for more calcs
franck@0 165 for ($x = $i + 1; $x < $i + 2; $x++) {
franck@0 166 $field = array();
franck@0 167 $form[] = views_calc_field_form_item($i, $field, $substitutions);
franck@0 168 }
franck@0 169 $form['#prefix'] = '<div class="views-calc-field-settings">';
franck@0 170 $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 171 $form['submit'] = array(
franck@0 172 '#type' => 'submit',
franck@0 173 '#value' => t('Save'),
franck@0 174 );
franck@0 175 return $form;
franck@0 176 }
franck@0 177
franck@0 178 /**
franck@0 179 * A form element for an individual calculated field.
franck@0 180 */
franck@0 181 function views_calc_field_form_item($i, $field, $substitutions) {
franck@0 182 if (empty($field)) {
franck@0 183 $field = array('cid' => 0, 'label' => '', 'tablelist' => '', 'calc' => '', 'format' => '', 'custom' => '');
franck@0 184 }
franck@0 185 $form['group'][$i] = array(
franck@0 186 '#type' => 'fieldset',
franck@0 187 '#tree' => TRUE,
franck@0 188 '#title' => t('Field: ') . !empty($field['label']) ? $field['label'] : t('New'),
franck@0 189 '#collapsible' => TRUE,
franck@0 190 '#collapsed' => FALSE,
franck@0 191 );
franck@0 192 $form['group'][$i]['cid'] = array(
franck@0 193 '#type' => 'hidden',
franck@0 194 '#value' => intval($field['cid']),
franck@0 195 );
franck@0 196 $form['group'][$i]['tablelist'] = array(
franck@0 197 '#type' => 'hidden',
franck@0 198 '#value' => $field['tablelist'],
franck@0 199 );
franck@0 200 $form['group'][$i]['label'] = array(
franck@0 201 '#type' => 'textfield',
franck@0 202 '#title' => t('Label'),
franck@0 203 '#field_prefix' => 'ViewsCalc: ',
franck@0 204 '#default_value' => str_replace('ViewsCalc: ', '', $field['label']),
franck@0 205 '#description' => t('The views field name for this field (i.e. Views Calc: My Calculation).'),
franck@0 206 );
franck@0 207 $form['group'][$i]['calc'] = array(
franck@0 208 '#type' => 'textarea',
franck@0 209 '#title' => t('Calculation'),
franck@0 210 '#default_value' => strtr($field['calc'], $substitutions),
franck@0 211 '#description' => t('<p>The query operation to be performed, using numbers, field substitutions, and '. implode(' ', _views_calc_operators()) .'.</p>'),
franck@0 212 );
franck@0 213 $form['group'][$i]['format'] = array(
franck@0 214 '#type' => 'select',
franck@0 215 '#title' => t('Format'),
franck@0 216 '#default_value' => $field['format'],
franck@0 217 '#options' => drupal_map_assoc(array_keys(_views_calc_format_options())),
franck@0 218 '#description' => t('The format of the result of this calculation.'),
franck@0 219 );
franck@0 220 $form['group'][$i]['custom'] = array(
franck@0 221 '#type' => 'textfield',
franck@0 222 '#title' => t('Custom function'),
franck@0 223 '#default_value' => $field['custom'],
franck@0 224 '#description' => t('The function to call for a custom format.'),
franck@0 225 );
franck@0 226 return $form;
franck@0 227 }
franck@0 228
franck@0 229 /**
franck@0 230 * Validate the views calc settings
franck@0 231 */
franck@0 232 function views_calc_fields_form_validate($form, &$form_state) {
franck@0 233 $form_values = $form_state['values'];
franck@0 234 $edit = $form_values;
franck@0 235 foreach ($edit as $delta => $item) {
franck@0 236 if ($item['calc'] == '' || !is_numeric($delta)) {
franck@0 237 // remove blank fields, don't save them
franck@0 238 unset($form_values[$delta]);
franck@0 239 } else {
franck@0 240 // Remove all valid values from calc, if anything is left over, it is invalid.
franck@0 241
franck@0 242 // First, remove all field names.
franck@0 243 $repl = array();
franck@0 244 $patterns = array();
franck@0 245 foreach (_views_calc_substitutions() as $key => $value) {
franck@0 246 $key = trim($value);
franck@0 247 $count = strlen($value);
franck@0 248 $replace = preg_quote($value);
franck@0 249 $patterns[] = "`(^|[^\\\\\\\\])". $replace ."`";
franck@0 250 $repl[] = '${1}';
franck@0 251 }
franck@0 252 $remaining = trim(preg_replace($patterns, $repl, $item['calc']));
franck@0 253 // Next, remove functions and numbers.
franck@0 254 $repl = array();
franck@0 255 $patterns = array();
franck@0 256 foreach (_views_calc_replacements() as $value) {
franck@0 257 $patterns[] = "`(^|[^\\\\\\\\])". preg_quote(trim($value)) ."`";
franck@0 258 $repl[] = '${1}';
franck@0 259 }
franck@0 260 $remaining = trim(preg_replace($patterns, $repl, $remaining));
franck@0 261 if (!empty($remaining)) {
franck@0 262 form_set_error($form_values[$delta]['calc'], t('The values %remaining in %field are not allowed.', array('%remaining' => $remaining, '%field' => $item['label'])));
franck@0 263 }
franck@0 264 }
franck@0 265 }
franck@0 266 }
franck@0 267
franck@0 268 /**
franck@0 269 * Save the views calc field settings
franck@0 270 */
franck@0 271 function views_calc_fields_form_submit($form, &$form_state) {
franck@0 272 $form_values = $form_state['values'];
franck@0 273 $edit = $form_values;
franck@0 274 foreach ($edit as $delta => $value) {
franck@0 275 if ($value['calc'] == '' || !is_numeric($delta)) {
franck@0 276 // remove blank fields, don't save them
franck@0 277 unset($form_values[$delta]);
franck@0 278
franck@0 279 }
franck@0 280 else {
franck@0 281 $tables = array();
franck@0 282 $form_values[$delta]['label'] = $value['label'];
franck@0 283 $form_values[$delta]['format'] = $value['format'];
franck@0 284 $form_values[$delta]['custom'] = $value['custom'];
franck@0 285 $form_values[$delta]['calc'] = $value['calc'];
franck@0 286
franck@0 287 // Substitute field names back into the calculation.
franck@0 288 $matches = array();
franck@0 289 foreach (_views_calc_substitutions() as $key => $value) {
franck@0 290 $label_patterns[] = "`(^|[^\\\\\\\\])". preg_quote($value) ."`";
franck@0 291 $value_patterns[] = "`(^|[^\\\\\\\\])". preg_quote($key) ."`";
franck@0 292 $repl[] = '${1}'. $key;
franck@0 293 }
franck@0 294 $form_values[$delta]['calc'] = preg_replace($label_patterns, $repl, $form_values[$delta]['calc']);
franck@0 295
franck@0 296 // Extract the fields and table names from the calculation.
franck@0 297 $tables = array();
franck@0 298 $fields = array();
franck@0 299 foreach ($value_patterns as $pattern) {
franck@0 300 if (preg_match($pattern, $form_values[$delta]['calc'], $results)) {
franck@0 301 $fields[] = trim($results[0]);
franck@0 302 $tmp = explode('.', trim($results[0]));
franck@0 303 if (trim($tmp[0])) {
franck@0 304 $tables[trim($tmp[0])] = trim($tmp[0]);
franck@0 305 }
franck@0 306 }
franck@0 307 }
franck@0 308 $form_values[$delta]['tablelist'] = implode(',', $tables);
franck@0 309 $form_values[$delta]['fieldlist'] = implode(',', $fields);
franck@0 310 }
franck@0 311 }
franck@0 312 foreach ($form_values as $delta => $value) {
franck@0 313 if ($value['cid'] == 0) {
franck@0 314 drupal_write_record('views_calc_fields', $value);
franck@0 315 }
franck@0 316 else {
franck@0 317 drupal_write_record('views_calc_fields', $value, array('cid'));
franck@0 318 }
franck@0 319 }
franck@0 320 views_invalidate_cache();
franck@0 321 drupal_set_message(t('Views Calc fields were updated.'));
franck@0 322 }
franck@0 323
franck@0 324 /**
franck@0 325 * Wrapper function to make sure this function will always work.
franck@0 326 */
franck@0 327 function views_calc_views_fetch_fields($base, $type) {
franck@0 328 if (!module_exists('views')) {
franck@0 329 return array();
franck@0 330 }
franck@0 331 require_once('./'. drupal_get_path('module', 'views') .'/includes/admin.inc');
franck@0 332 return views_fetch_fields($base, $type);
franck@0 333 }
franck@0 334
franck@0 335 /**
franck@0 336 * Field substitutions for calculations.
franck@0 337 */
franck@0 338 function _views_calc_substitutions($base = 'node') {
franck@0 339 $fields = views_calc_views_fetch_fields($base, 'field');
franck@0 340 $substitutions['node.nid'] = '%Node.nid';
franck@0 341 $substitutions['node.uid'] = '%Node.uid';
franck@0 342 foreach ($fields as $key => $field) {
franck@0 343 // For now, omit calculated fields from available fields list.
franck@0 344 // Doing caculations on calculated fields will require some
franck@0 345 // complex additional logic, especially if they are nested
franck@0 346 // several levels deep.
franck@0 347 if (substr($key, 0, 4) != '.cid') {
franck@0 348 $substitutions[$key] = '%'. str_replace(' ', '', $key);
franck@0 349 }
franck@0 350 }
franck@0 351 return $substitutions;
franck@0 352 }
franck@0 353
franck@0 354 /**
franck@0 355 * Views calc fields result object
franck@0 356 */
franck@0 357 function _views_calc_fields() {
franck@0 358 return db_query("SELECT * FROM {views_calc_fields}");
franck@0 359 }
franck@0 360
franck@0 361 /**
franck@0 362 * An array of allowable calculation values.
franck@0 363 */
franck@0 364 function _views_calc_replacements() {
franck@0 365 $operators = array_filter(_views_calc_operators(), 'trim');
franck@0 366 $numbers = range(0, 9);
franck@0 367 return array_merge($operators, $numbers);
franck@0 368 }