#270588: Have summaries respect the DISTINCT flag when doing their counting
[project/views.git] / includes / handlers.inc
CommitLineData
013538bb
EM
1<?php
2// $Id$
3/**
4 * @file handlers.inc
5 * Defines the various handler objects to help build and display views.
6 */
7
8/**
021885c3 9 * Instantiate and construct a new handler
021885c3
EM
10 */
11function _views_create_handler($definition) {
238f7347 12// vpr('Instantiating handler ' . $definition['handler']);
39829940 13 if (empty($definition['handler']) || !class_exists($definition['handler'])) {
99c8e8b1
EM
14 return;
15 }
16
021885c3 17 $handler = new $definition['handler'];
c60d618c 18 $handler->set_definition($definition);
725bd2c9 19 // let the handler have something like a constructor.
cb2cdc11 20 // @todo: deprecated. Remove.
021885c3 21 if (isset($definition['arguments'])) {
b207ba28 22 call_user_func_array(array(&$handler, 'construct'), $definition['arguments']);
021885c3 23 }
725bd2c9
EM
24 else {
25 $handler->construct();
26 }
021885c3 27
021885c3
EM
28 return $handler;
29}
30
31/**
32 * Prepare a handler's data by checking defaults and such.
021885c3
EM
33 */
34function _views_prepare_handler($definition, $data, $field) {
38b3122d 35 foreach (array('group', 'title', 'help', 'real field') as $key) {
021885c3
EM
36 // First check the field level
37 if (!isset($definition[$key]) && !empty($data[$field][$key])) {
38 $definition[$key] = $data[$field][$key];
39 }
40 // Then if that doesn't work, check the table level
fe99091c 41 else if (!isset($definition['table'][$key]) && !empty($data['table'][$key])) {
021885c3
EM
42 $definition[$key] = $data['table'][$key];
43 }
44 }
45
46 return _views_create_handler($definition);
47}
48
49/**
50 * Fetch a handler to join one table to a primary table from the data cache
021885c3 51 */
ee23afa2 52function views_get_table_join($table, $base_table) {
021885c3 53 $data = views_fetch_data($table);
ee23afa2
EM
54 if (isset($data['table']['join'][$base_table])) {
55 $h = $data['table']['join'][$base_table];
2d3898d7
EM
56 if (!empty($h['handler']) && class_exists($h['handler'])) {
57 $handler = new $h['handler'];
58 }
59 else {
60 $handler = new views_join();
61 }
62
63 // Fill in some easy defaults
64 $handler->definition = $h;
65 if (empty($handler->definition['table'])) {
66 $handler->definition['table'] = $table;
67 }
68 // If this is empty, it's a direct link.
69 if (empty($handler->definition['left_table'])) {
ee23afa2 70 $handler->definition['left_table'] = $base_table;
2d3898d7
EM
71 }
72
021885c3 73 if (isset($h['arguments'])) {
b207ba28 74 call_user_func_array(array(&$handler, 'construct'), $h['arguments']);
021885c3 75 }
2d3898d7
EM
76 else {
77 $handler->construct();
78 }
79
021885c3
EM
80 return $handler;
81 }
82 // DEBUG -- identify missing handlers
ee23afa2 83 vpr("Missing join: $table $base_table");
021885c3
EM
84}
85
86/**
86ed07d8 87 * Base handler, from which all the other handlers are derived.
013538bb
EM
88 * It creates a common interface to create consistency amongst
89 * handlers and data.
90 *
013538bb 91 * This class would be abstract in PHP5, but PHP4 doesn't understand that.
86ed07d8 92 *
013538bb 93 */
cffc1056 94class views_handler extends views_object {
013538bb 95 /**
86ed07d8 96 * init the handler with necessary data.
013538bb
EM
97 * @param $view
98 * The $view object this handler is attached to.
5ad6fb04 99 * @param $options
013538bb
EM
100 * The item from the database; the actual contents of this will vary
101 * based upon the type of handler.
102 */
1008b7ff 103 function init(&$view, $options) {
013538bb 104 $this->view = &$view;
cb2cdc11 105 $this->unpack_options($this->options, $options);
013538bb 106
013538bb 107 // This exist on most handlers, but not all. So they are still optional.
1008b7ff
EM
108 if (isset($options['table'])) {
109 $this->table = $options['table'];
013538bb
EM
110 }
111
38b3122d
EM
112 if (isset($this->definition['real field'])) {
113 $this->real_field = $this->definition['real field'];
114 }
115
116 if (isset($this->definition['field'])) {
117 $this->real_field = $this->definition['field'];
118 }
119
1008b7ff
EM
120 if (isset($options['field'])) {
121 $this->field = $options['field'];
cffc1056 122 if (!isset($this->real_field)) {
1008b7ff 123 $this->real_field = $options['field'];
cffc1056 124 }
013538bb
EM
125 }
126
013538bb
EM
127 if (!empty($view->query)) {
128 $this->query = &$view->query;
129 }
130 }
131
132 /**
4191b1df
EM
133 * Return a string representing this handler's name in the UI.
134 */
135 function ui_name() {
4693b8a1 136 return t('!group: !title', array('!group' => $this->definition['group'], '!title' => $this->definition['title']));
4191b1df
EM
137 }
138
139 /**
013538bb
EM
140 * Provide a form for setting options.
141 */
3a69a04c 142 function options_form(&$form, &$form_state) { }
86ed07d8 143
013538bb
EM
144 /**
145 * Validate the options form.
146 */
147 function options_validate($form, &$form_state) { }
148
149 /**
150 * Perform any necessary changes to the form values prior to storage.
151 * There is no need for this function to actually store the data.
152 */
153 function options_submit($form, &$form_state) { }
154
155 /**
8881fd4d
EM
156 * If a handler has 'extra options' it will get a little settings widget and
157 * another form called extra_options.
158 */
159 function has_extra_options() { return FALSE; }
160
161 /**
162 * Provide defaults for the handler.
163 */
164 function extra_options(&$option) { }
165
166 /**
167 * Provide a form for setting options.
168 */
169 function extra_options_form(&$form, &$form_state) { }
170
171 /**
172 * Validate the options form.
173 */
174 function extra_options_validate($form, &$form_state) { }
175
176 /**
177 * Perform any necessary changes to the form values prior to storage.
178 * There is no need for this function to actually store the data.
179 */
180 function extra_options_submit($form, &$form_state) { }
181
182 /**
d3887131
EM
183 * Set new exposed option defaults when exposed setting is flipped
184 * on.
185 */
186 function expose_options() { }
187 /**
188 * Render our chunk of the exposed filter form when selecting
70b45fc4
EM
189 */
190 function exposed_form(&$form, &$form_state) { }
191
192 /**
193 * Validate the exposed filter form
194 */
195 function exposed_validate(&$form, &$form_state) { }
196
197 /**
198 * Submit the exposed filter form
199 */
200 function exposed_submit(&$form, &$form_state) { }
201
202 /**
d3887131
EM
203 * Get information about the exposed form for the form renderer.
204 *
205 * @return
206 * An array with the following keys:
207 * - operator: The $form key of the operator. Set to NULL if no operator.
208 * - value: The $form key of the value. Set to NULL if no value.
209 * - label: The label to use for this piece.
210 */
211 function exposed_info() { }
4191b1df 212
7ae4dc41
EM
213 /**
214 * Check whether current user has access to this handler.
215 *
216 * @return boolean
4191b1df 217 */
7ae4dc41 218 function access() {
fa774b18
EM
219 if (isset($this->definition['access callback']) && function_exists($this->definition['access callback'])) {
220 if (isset($this->definition['access arguments']) && is_array($this->definition['access arguments'])) {
221 return call_user_func_array($this->definition['access callback'], $this->definition['access arguments']);
222 }
223 return $this->definition['access callback']();
224 }
225
7ae4dc41
EM
226 return TRUE;
227 }
d3887131
EM
228
229 /**
0a5243db
EM
230 * Run before the view is built.
231 *
232 * This gives all the handlers some time to set up before any handler has
233 * been fully run.
234 */
235 function pre_query() { }
236
237 /**
4191b1df
EM
238 * Called just prior to query(), this lets a handler set up any relationship
239 * it needs.
240 */
241 function set_relationship() {
242 // Ensure this gets set to something.
243 $this->relationship = NULL;
244
245 // Don't process non-existant relationships.
246 if (empty($this->options['relationship']) || $this->options['relationship'] == 'none') {
247 return;
248 }
249
250 $relationship = $this->options['relationship'];
251
252 // Ignore missing/broken relationships.
0d0ed51a 253 if (empty($this->view->relationship[$relationship])) {
4191b1df
EM
254 return;
255 }
256
257 // Check to see if the relationship has already processed. If not, then we
258 // cannot process it.
0d0ed51a 259 if (empty($this->view->relationship[$relationship]->alias)) {
4191b1df
EM
260 return;
261 }
262
263 // Finally!
0d0ed51a 264 $this->relationship = $this->view->relationship[$relationship]->alias;
4191b1df
EM
265 }
266
267 /**
013538bb
EM
268 * Add this handler into the query.
269 *
270 * If we were using PHP5, this would be abstract.
271 */
272 function query() { }
cffc1056
EM
273
274 /**
275 * Ensure the main table for this handler is in the query. This is used
276 * a lot.
277 */
278 function ensure_my_table() {
1146ee98 279 if (!isset($this->table_alias)) {
06f6fd84 280 $this->table_alias = $this->query->ensure_table($this->table, $this->relationship);
cffc1056
EM
281 }
282 return $this->table_alias;
283 }
3a69a04c
EM
284
285 /**
286 * Provide text for the administrative summary
287 */
288 function admin_summary() { }
289
290 /**
291 * Determine if the argument needs a style plugin.
292 *
293 * @return TRUE/FALSE
294 */
295 function needs_style_plugin() { return FALSE; }
bb770a1b
EM
296
297 /**
298 * Determine if this item is 'exposed', meaning it provides form elements
299 * to let users modify the view.
300 *
301 * @return TRUE/FALSE
302 */
303 function is_exposed() {
304 return !empty($this->options['exposed']);
305 }
306
307 /**
308 * Take input from exposed filters and assign to this handler, if necessary.
309 */
618449fb 310 function accept_exposed_input($input) { return TRUE; }
f4a62619
EM
311
312 /**
313 * Get the join object that should be used for this handler.
314 *
315 * This method isn't used a great deal, but it's very handy for easily
316 * getting the join if it is necessary to make some changes to it, such
317 * as adding an 'extra'.
318 */
319 function get_join() {
320 // get the join from this table that links back to the base table.
321 return drupal_clone(views_get_table_join($this->table, $this->query->base_table));
322 }
0d0ed51a
EM
323
324 /**
325 * Determine if the handler is considered 'broken', meaning it's a
326 * a placeholder used when a handler can't be found.
327 */
328 function broken() { }
cb2cdc11
EM
329}
330
331/**
332 * A special handler to take the place of missing or broken handlers.
333 */
334class views_handler_broken extends views_handler {
335 function ui_name() {
336 return t('Broken/missing handler');
337 }
338
339 function ensure_my_table() { /* No table to ensure! */ }
f4a62619 340
cb2cdc11
EM
341 function options_form(&$form, &$form_state) {
342 $form['markup'] = array(
343 '#prefix' => '<div class="form-item description">',
344 '#value' => t('The handler for this item is broken or missing and cannot be used. If a module provided the handler and was disabled, re-enabling the module may restore it. Otherwise, you should probably delete this item.'),
345 );
346 }
215c9151
EM
347
348 function label() {
349 return $this->ui_name();
350 }
351
352 function pre_render() { }
0d0ed51a
EM
353
354 /**
355 * Determine if the handler is considered 'broken'
356 */
357 function broken() { }
013538bb
EM
358}
359
013538bb 360/**
ca78ec49 361 * This many to one helper object is used on both arguments and filters.
538c7cf5
EM
362 *
363 * @todo This requires extensive documentation on how this class is to
364 * be used. For now, look at the arguments and filters that use it. Lots
365 * of stuff is just pass-through but there are definitely some interesting
366 * areas where they interact.
013538bb 367 */
ca78ec49
EM
368class views_many_to_one_helper {
369 function views_many_to_one_helper(&$handler) {
370 $this->handler = &$handler;
371 }
013538bb 372
cb2cdc11
EM
373 function option_definition(&$options) {
374 $options['reduce_duplicates'] = array('default' => FALSE);
375 }
376
ca78ec49
EM
377 function options_form(&$form, &$form_state) {
378 $form['reduce_duplicates'] = array(
379 '#type' => 'checkbox',
380 '#title' => t('Reduce duplicates'),
381 '#description' => t('This filter can cause items that have more than one of the selected options to appear as duplicate results. If this filter causes duplicate results to occur, this checkbox can reduce those duplicates; however, the more terms it has to search for, the less performant the query will be, so use this with caution.'),
382 '#default_value' => !empty($this->handler->options['reduce_duplicates']),
383 );
384 }
0a5243db
EM
385
386 /**
ca78ec49
EM
387 * Sometimes the handler might want us to use some kind of formula, so give
388 * it that option. If it wants us to do this, it must set $helper->formula = TRUE
389 * and implement handler->get_formula();
013538bb 390 */
ca78ec49
EM
391 function get_field() {
392 if (!empty($this->formula)) {
393 return $this->handler->get_formula();
394 }
395 else {
396 return $this->handler->table_alias . '.' . $this->handler->real_field;
8a718296 397 }
013538bb 398 }
013538bb 399
013538bb 400 /**
50e8b0ab
EM
401 * Add a table to the query.
402 *
403 * This is an advanced concept; not only does it add a new instance of the table,
404 * but it follows the relationship path all the way down to the relationship
405 * link point and adds *that* as a new relationship and then adds the table to
406 * the relationship, if necessary.
407 */
408 function add_table($join = NULL, $alias = NULL) {
409 // This is used for lookups in the many_to_one table.
410 $field = $this->handler->table . '.' . $this->handler->field;
411
412 if (empty($join)) {
413 $join = $this->get_join();
414 }
415
416 // See if there's a chain between us and the base relationship. If so, we need
417 // to create a new relationship to use.
418 $relationship = $this->handler->relationship;
419
420 // Determine the primary table to seek
421 if (empty($this->handler->query->relationships[$relationship])) {
ee23afa2 422 $base_table = $this->handler->query->base_table;
50e8b0ab
EM
423 }
424 else {
ee23afa2 425 $base_table = $this->handler->query->relationships[$relationship]['base'];
50e8b0ab
EM
426 }
427
428 // Cycle through the joins. This isn't as error-safe as the normal
429 // ensure_path logic. Perhaps it should be.
430 $r_join = drupal_clone($join);
ee23afa2
EM
431 while ($r_join->left_table != $base_table) {
432 $r_join = views_get_table_join($r_join->left_table, $base_table);
50e8b0ab
EM
433 }
434
435 // If we found that there are tables in between, add the relationship.
436 if ($r_join->table != $join->table) {
437 $relationship = $this->handler->query->add_relationship(NULL, $r_join, $r_join->table, $this->handler->relationship);
438 }
439
440 // And now add our table, using the new relationship if one was used.
441 $alias = $this->handler->query->add_table($this->handler->table, $relationship, $join, $alias);
442
443 // Store what values are used by this table chain so that other chains can
444 // automatically discard those values.
83076935
EM
445 if (empty($this->handler->view->many_to_one_tables[$field])) {
446 $this->handler->view->many_to_one_tables[$field] = $this->handler->value;
447 }
448 else {
449 $this->handler->view->many_to_one_tables[$field] = array_merge($this->handler->view->many_to_one_tables[$field], $this->handler->value);
450 }
50e8b0ab
EM
451
452 return $alias;
453 }
454
455 function get_join() {
f4a62619 456 return $this->handler->get_join();
50e8b0ab
EM
457 }
458
459 /**
460 * Provide the proper join for summary queries. This is important in part because
461 * it will cooperate with other arguments if possible.
462 */
463 function summary_join() {
464 $field = $this->handler->table . '.' . $this->handler->field;
465 $join = $this->get_join();
466
467 // shortcuts
468 $options = $this->handler->options;
469 $view = &$this->handler->view;
470 $query = &$this->handler->query;
471
472 if (!empty($options['require_value'])) {
473 $join->type = 'INNER';
474 }
475
476 if (empty($options['add_table']) || empty($view->many_to_one_tables[$field])) {
477 return $query->ensure_table($this->handler->table, $this->handler->relationship, $join);
478 }
479 else {
480 if (!empty($view->many_to_one_tables[$field])) {
481 foreach ($view->many_to_one_tables[$field] as $value) {
482 $join->extra = array(
483 array(
484 'field' => $this->handler->real_field,
485 'operator' => '!=',
486 'value' => $value,
487 'numeric' => !empty($this->definition['numeric']),
488 ),
489 );
490 }
491 }
492 return $this->add_table($join);
493 }
494 }
495
496 /**
ca78ec49
EM
497 * Override ensure_my_table so we can control how this joins in.
498 * The operator actually has influence over joining.
013538bb 499 */
ca78ec49
EM
500 function ensure_my_table() {
501 if (!isset($this->handler->table_alias)) {
502 // For 'or' if we're not reducing duplicates, we get the absolute simplest:
503 $field = $this->handler->table . '.' . $this->handler->field;
ca78ec49 504 if ($this->handler->operator == 'or' && empty($this->handler->options['reduce_duplicates'])) {
2440bfa7 505 if (empty($this->handler->options['add_table']) && empty($this->handler->view->many_to_one_tables[$field])) {
1f4cbd48
EM
506 // query optimization, INNER joins are slightly faster, so use them
507 // when we know we can.
508 $join = $this->get_join();
509 $join->type = 'INNER';
510 $this->handler->table_alias = $this->handler->query->ensure_table($this->handler->table, $this->handler->relationship, $join);
ca78ec49
EM
511 $this->handler->view->many_to_one_tables[$field] = $this->handler->value;
512 }
513 else {
50e8b0ab 514 $join = $this->get_join();
1f4cbd48 515 $join->type = 'LEFT';
ca78ec49
EM
516 if (!empty($this->handler->view->many_to_one_tables[$field])) {
517 foreach ($this->handler->view->many_to_one_tables[$field] as $value) {
518 $join->extra = array(
519 array(
520 'field' => $this->handler->real_field,
521 'operator' => '!=',
522 'value' => $value,
523 'numeric' => !empty($this->handler->definition['numeric']),
524 ),
525 );
526 }
527 }
013538bb 528
50e8b0ab 529 $this->handler->table_alias = $this->add_table($join);
ca78ec49 530 }
50e8b0ab 531
ca78ec49
EM
532 return $this->handler->table_alias;
533 }
0225248a 534
ca78ec49
EM
535 if ($this->handler->operator != 'not') {
536 // If it's an and or an or, we do one join per selected value.
537 // Clone the join for each table:
538 $this->handler->table_aliases = array();
539 foreach ($this->handler->value as $value) {
50e8b0ab 540 $join = $this->get_join();
1f4cbd48
EM
541 if ($this->handler->operator == 'and') {
542 $join->type = 'INNER';
543 }
ca78ec49
EM
544 $join->extra = array(
545 array(
546 'field' => $this->handler->real_field,
547 'value' => $value,
548 'numeric' => !empty($this->handler->definition['numeric']),
549 ),
550 );
50e8b0ab 551 $alias = $this->handler->table_aliases[$value] = $this->add_table($join, $this->handler->table . '_' . $value);
ca78ec49
EM
552
553 // and set table_alias to the first of these.
554 if (empty($this->handler->table_alias)) {
555 $this->handler->table_alias = $alias;
556 }
557 }
558 }
559 else {
560 // For not, we just do one join. We'll add a where clause during
561 // the query phase to ensure that $table.$field IS NULL.
50e8b0ab 562 $join = $this->get_join();
ca78ec49
EM
563 $join->type = 'LEFT';
564 $join->extra = array();
565 $join->extra_type = 'OR';
566 foreach ($this->handler->value as $value) {
567 $join->extra[] = array(
568 'field' => $this->handler->real_field,
569 'value' => $value,
570 'numeric' => !empty($this->handler->definition['numeric']),
571 );
572 }
0225248a 573
50e8b0ab 574 $this->handler->table_alias = $this->add_table($join);
ca78ec49 575 }
0225248a 576 }
ca78ec49 577 return $this->handler->table_alias;
0225248a 578 }
0225248a 579
ca78ec49
EM
580 function add_filter() {
581 if (empty($this->handler->value)) {
8a718296 582 return;
0a5243db 583 }
ca78ec49
EM
584 $this->handler->ensure_my_table();
585
586 // Shorten some variables:
587 $field = $this->get_field();
588 $options = $this->handler->options;
589 $operator = $this->handler->operator;
590 if (empty($options['group'])) {
591 $options['group'] = 0;
9e948ec4
EM
592 }
593
ca78ec49 594 $placeholder = !empty($this->handler->definition['numeric']) ? '%d' : "'%s'";
b57c309a 595
ca78ec49
EM
596 if ($operator == 'not') {
597 $this->handler->query->add_where($options['group'], "$field IS NULL");
9e948ec4 598 }
ca78ec49 599 else if ($operator == 'or' && empty($options['reduce_duplicates'])) {
50e8b0ab
EM
600 if (count($this->handler->value) > 1) {
601 $replace = array_fill(0, sizeof($this->handler->value), $placeholder);
602 $in = '(' . implode(", ", $replace) . ')';
603 $this->handler->query->add_where($options['group'], "$field IN $in", $this->handler->value);
604 }
605 else {
606 $this->handler->query->add_where($options['group'], "$field = $placeholder", $this->handler->value);
607 }
013538bb 608 }
ca78ec49
EM
609 else {
610 $field = $this->handler->real_field;
611 $clauses = array();
612 foreach ($this->handler->table_aliases as $value => $alias) {
613 $clauses[] = "$alias.$field = $placeholder";
614 }
01c6ed1e 615
ca78ec49
EM
616 $group = empty($options['group']) ? 0 : $options['group'];
617
618 // implode on either AND or OR.
619 $this->handler->query->add_where($group, implode(' ' . strtoupper($operator) . ' ', $clauses), $this->handler->value);
01c6ed1e 620 }
01c6ed1e
EM
621 }
622}
623
8a718296
EM
624/*
625 * Break x,y,z and x+y+z into an array. Numeric only.
626 *
627 * @param $str
628 * The string to parse.
629 * @param $filter
630 * The filter object to use as a base. If not specified one will
631 * be created.
632 *
633 * @return $filter
634 * The new filter object.
013538bb 635 */
8a718296
EM
636function views_break_phrase($str, $filter = NULL) {
637 if (!$filter) {
ca78ec49 638 $filter = new stdClass();
3a69a04c 639 }
8a718296
EM
640 if (preg_match('/^([0-9]+[+ ])+[0-9]+$/', $str)) {
641 // The '+' character in a query string may be parsed as ' '.
642 $filter->operator = 'or';
ca78ec49 643 $filter->value = preg_split('/[+ ]/', $str);
bb770a1b 644 }
8a718296
EM
645 else if (preg_match('/^([0-9]+,)*[0-9]+$/', $str)) {
646 $filter->operator = 'and';
ca78ec49 647 $filter->value = explode(',', $str);
3a69a04c
EM
648 }
649
eca7ad62
EM
650 // Keep an 'error' value if invalid strings were given.
651 if (!empty($str) && empty($filter->value)) {
652 $filter->value = array(-1);
653 }
654
83076935
EM
655 // Doubly ensure that all values are numeric only.
656 foreach ($filter->value as $id => $value) {
657 $filter->value[$id] = intval($value);
658 }
9d99573a 659
8a718296
EM
660 return $filter;
661}
52208794 662
80f01edd
EM
663// --------------------------------------------------------------------------
664// Date helper functions
665
666/**
667 * Figure out what timezone we're in; needed for some date manipulations.
668 */
669function views_get_timezone() {
670 global $user;
671 if (variable_get('configurable_timezones', 1) && $user->uid && strlen($user->timezone)) {
672 $timezone = $user->timezone;
673 }
674 else {
675 $timezone = variable_get('date_default_timezone', 0);
676 }
677
678 // set up the database timezone
679 if (in_array($GLOBALS['db_type'], array('mysql', 'mysqli'))) {
680 static $already_set = false;
681 if (!$already_set) {
682 if ($GLOBALS['db_type'] == 'mysqli' || version_compare(mysql_get_server_info(), '4.1.3', '>=')) {
683 db_query("SET @@session.time_zone = '+00:00'");
684 }
685 $already_set = true;
686 }
687 }
688
689 return $timezone;
690}
691
692/**
693 * Helper function to create cross-database SQL dates.
694 *
695 * @param $field
696 * The real table and field name, like 'tablename.fieldname'.
697 * @param $field_type
698 * The type of date field, 'int' or 'datetime'.
699 * @param $set_offset
700 * The name of a field that holds the timezone offset or a fixed timezone
701 * offset value. If not provided, the normal Drupal timezone handling
702 * will be used, i.e. $set_offset = 0 will make no timezone adjustment.
703 * @return
704 * An appropriate SQL string for the db type and field type.
705 */
706function views_date_sql_field($field, $field_type = 'int', $set_offset = NULL) {
707 $db_type = $GLOBALS['db_type'];
708 $offset = $set_offset !== NULL ? $set_offset : views_get_timezone();
709 switch ($db_type) {
710 case 'mysql':
711 case 'mysqli':
712 switch ($field_type) {
713 case 'int':
714 $field = "FROM_UNIXTIME($field)";
715 break;
716 case 'datetime':
717 break;
718 }
719 if (!empty($offset)) {
720 $field = "($field + INTERVAL $offset SECOND)";
721 }
722 return $field;
723 case 'pgsql':
724 switch ($field_type) {
725 case 'int':
726 $field = "$field::ABSTIME";
727 break;
728 case 'datetime':
729 break;
730 }
731 if (!empty($offset)) {
732 $field = "($field + 'INTERVAL $offset SECONDS')";
733 }
734 return $field;
735 }
736}
737
738/**
739 * Helper function to create cross-database SQL date formatting.
740 *
741 * @param $format
742 * A format string for the result, like 'Y-m-d H:i:s'.
743 * @param $field
744 * The real table and field name, like 'tablename.fieldname'.
745 * @param $field_type
746 * The type of date field, 'int' or 'datetime'.
747 * @param $set_offset
748 * The name of a field that holds the timezone offset or a fixed timezone
749 * offset value. If not provided, the normal Drupal timezone handling
750 * will be used, i.e. $set_offset = 0 will make no timezone adjustment.
751 * @return
752 * An appropriate SQL string for the db type and field type.
753 */
754function views_date_sql_format($format, $field, $field_type = 'int', $set_offset = NULL) {
755 $db_type = $GLOBALS['db_type'];
756 $field = views_date_sql_field($field, $field_type, $set_offset);
757 switch ($db_type) {
758 case 'mysql':
759 case 'mysqli':
760 $replace = array(
761 'Y' => '%Y',
762 'm' => '%m',
763 'd' => '%%d',
764 'H' => '%H',
765 'i' => '%i',
766 's' => '%s',
767 );
768 $format = strtr($format, $replace);
769 return "DATE_FORMAT($field, '$format')";
770 case 'pgsql':
771 $replace = array(
772 'Y' => 'YY',
773 'm' => 'MM',
774 'd' => 'DD',
775 'H' => 'HH24',
776 'i' => 'MI',
777 's' => 'SS',
778 );
779 $format = strtr($format, $replace);
780 return "TO_CHAR($field, '$format')";
781 }
782}
783
784/**
785 * Helper function to create cross-database SQL date extraction.
786 *
787 * @param $extract_type
788 * The type of value to extract from the date, like 'MONTH'.
789 * @param $field
790 * The real table and field name, like 'tablename.fieldname'.
791 * @param $field_type
792 * The type of date field, 'int' or 'datetime'.
793 * @param $set_offset
794 * The name of a field that holds the timezone offset or a fixed timezone
795 * offset value. If not provided, the normal Drupal timezone handling
796 * will be used, i.e. $set_offset = 0 will make no timezone adjustment.
797 * @return
798 * An appropriate SQL string for the db type and field type.
799 */
800function views_date_sql_extract($extract_type, $field, $field_type = 'int', $set_offset = NULL) {
801 $db_type = $GLOBALS['db_type'];
802 $field = views_date_sql_field($field, $field_type, $set_offset);
803
804 // Note there is no space after FROM to avoid db_rewrite problems
805 // see http://drupal.org/node/79904.
806 switch ($extract_type) {
807 case('DATE'):
808 return $field;
809 case('YEAR'):
810 return "EXTRACT(YEAR FROM($field))";
811 case('MONTH'):
812 return "EXTRACT(MONTH FROM($field))";
813 case('DAY'):
814 return "EXTRACT(DAY FROM($field))";
815 case('HOUR'):
816 return "EXTRACT(HOUR FROM($field))";
817 case('MINUTE'):
818 return "EXTRACT(MINUTE FROM($field))";
819 case('SECOND'):
820 return "EXTRACT(SECOND FROM($field))";
821 case('WEEK'): // ISO week number for date
822 switch ($db_type) {
823 case('mysql'):
824 case('mysqli'):
825 // WEEK using arg 3 in mysql should return the same value as postgres EXTRACT
826 return "WEEK($field, 3)";
827 case('pgsql'):
828 return "EXTRACT(WEEK FROM($field))";
829 }
830 case('DOW'):
831 switch ($db_type) {
832 case('mysql'):
833 case('mysqli'):
834 // mysql returns 1 for Sunday through 7 for Saturday
835 // php date functions and postgres use 0 for Sunday and 6 for Saturday
836 return "INTEGER(DAYOFWEEK($field) - 1)";
837 case('pgsql'):
838 return "EXTRACT(DOW FROM($field))";
839 }
840 case('DOY'):
841 switch ($db_type) {
842 case('mysql'):
843 case('mysqli'):
844 return "DAYOFYEAR($field)";
845 case('pgsql'):
846 return "EXTRACT(DOY FROM($field))";
847 }
848 }
849
850}