1 | <?php |
---|
2 | /** |
---|
3 | * @file |
---|
4 | * SQL date functions. |
---|
5 | */ |
---|
6 | /** |
---|
7 | * A helper function to do cross-database concatation of date parts |
---|
8 | * |
---|
9 | * @param $array - an array of values to be concatonated in sql |
---|
10 | * @return - correct sql string for database type |
---|
11 | */ |
---|
12 | function date_sql_concat($array) { |
---|
13 | global $db_type; |
---|
14 | switch ($db_type) { |
---|
15 | case ('mysql'): |
---|
16 | case ('mysqli'): |
---|
17 | return "CONCAT(". implode(",", $array) .")"; |
---|
18 | case ('pgsql'): |
---|
19 | return implode(" || ", $array); |
---|
20 | } |
---|
21 | } |
---|
22 | |
---|
23 | /** |
---|
24 | * Helper function to do cross-database NULL replacements |
---|
25 | * |
---|
26 | * @param an array of values to test for NULL values |
---|
27 | * @return SQL statement to return the first non-NULL value in the list. |
---|
28 | */ |
---|
29 | function date_sql_coalesce($array) { |
---|
30 | global $db_type; |
---|
31 | switch ($db_type) { |
---|
32 | case ('mysql'): |
---|
33 | case ('mysqli'): |
---|
34 | case ('pgsql'): |
---|
35 | return "COALESCE(". implode(',', $array) .")"; |
---|
36 | } |
---|
37 | } |
---|
38 | |
---|
39 | /** |
---|
40 | * A helper function to do cross-database padding of date parts |
---|
41 | * |
---|
42 | * @param $str - a string to apply padding to |
---|
43 | * @param $size - the size the final string should be |
---|
44 | * @param $pad - the value to pad the string with |
---|
45 | * @param $side - the side of the string to pad |
---|
46 | */ |
---|
47 | function date_sql_pad($str, $size = 2, $pad = '0', $side = 'l') { |
---|
48 | switch ($side) { |
---|
49 | case ('r'): |
---|
50 | return "RPAD($str, $size, '$pad')"; |
---|
51 | default: |
---|
52 | return "LPAD($str, $size, '$pad')"; |
---|
53 | } |
---|
54 | } |
---|
55 | |
---|
56 | /** |
---|
57 | * A class to manipulate date SQL. |
---|
58 | */ |
---|
59 | class date_sql_handler { |
---|
60 | var $db_type = 'mysql'; |
---|
61 | var $date_type = DATE_DATETIME; |
---|
62 | var $db_timezone = 'UTC'; // A string timezone name. |
---|
63 | var $local_timezone = NULL; // A string timezone name. |
---|
64 | var $db_timezone_field = NULL; // Use if the db timezone is stored in a field. |
---|
65 | var $local_timezone_field = NULL; // Use if the local timezone is stored in a field. |
---|
66 | var $offset_field = NULL; // Use if the offset is stored in a field. |
---|
67 | |
---|
68 | function construct($date_type = DATE_DATETIME, $local_timezone = NULL) { |
---|
69 | $this->db_type = $GLOBALS['db_type']; |
---|
70 | $this->date_type = $date_type; |
---|
71 | $this->db_timezone = 'UTC'; |
---|
72 | $this->local_timezone = isset($local_timezone) ? $local_timezone : date_default_timezone_name(); |
---|
73 | if (isset($this->definition['content_field'])) { |
---|
74 | $this->date_handler->date_type = $this->definition['content_field']['type']; |
---|
75 | } |
---|
76 | date_api_set_db_timezone(); |
---|
77 | } |
---|
78 | |
---|
79 | /** |
---|
80 | * See if the db has timezone name support. |
---|
81 | */ |
---|
82 | function db_tz_support($reset = FALSE) { |
---|
83 | $has_support = variable_get('date_db_tz_support', -1); |
---|
84 | if ($has_support == -1 || $reset) { |
---|
85 | date_api_set_db_timezone(); |
---|
86 | $has_support = FALSE; |
---|
87 | switch ($this->db_type) { |
---|
88 | case 'mysql': |
---|
89 | case 'mysqli': |
---|
90 | if (version_compare(db_version(), '4.1.3', '>=')) { |
---|
91 | $test = db_result(db_query("SELECT CONVERT_TZ('2008-02-15 12:00:00', 'UTC', 'US/Central')")); |
---|
92 | if ($test == '2008-02-15 06:00:00') { |
---|
93 | $has_support = TRUE; |
---|
94 | } |
---|
95 | } |
---|
96 | break; |
---|
97 | case 'pgsql': |
---|
98 | $test = db_result(db_query("SELECT '2008-02-15 12:00:00 UTC' AT TIME ZONE 'US/Central'")); |
---|
99 | if ($test == '2008-02-15 06:00:00') { |
---|
100 | $has_support = TRUE; |
---|
101 | } |
---|
102 | break; |
---|
103 | } |
---|
104 | variable_set('date_db_tz_support', $has_support); |
---|
105 | } |
---|
106 | return $has_support; |
---|
107 | } |
---|
108 | |
---|
109 | /** |
---|
110 | * Set the database timzone offset. |
---|
111 | * |
---|
112 | * Setting the db timezone to UTC is done to ensure consistency in date |
---|
113 | * handling whether or not the database can do proper timezone conversion. |
---|
114 | * |
---|
115 | * Views filters that not exposed are cached and won't set the timezone |
---|
116 | * so views date filters should add 'cacheable' => 'no' to their |
---|
117 | * definitions to ensure that the database timezone gets set properly |
---|
118 | * when the query is executed. |
---|
119 | * |
---|
120 | * @param $offset |
---|
121 | * An offset value to set the database timezone to. This will only |
---|
122 | * set a fixed offset, not a timezone, so any value other than |
---|
123 | * '+00:00' should be used with caution. |
---|
124 | */ |
---|
125 | function set_db_timezone($offset = '+00:00') { |
---|
126 | static $already_set = FALSE; |
---|
127 | $type = $GLOBALS['db_type']; |
---|
128 | if (!$already_set) { |
---|
129 | if (($type == 'mysqli' || $type == 'mysql') && version_compare(db_version(), '4.1.3', '>=')) { |
---|
130 | db_query("SET @@session.time_zone = '$offset'"); |
---|
131 | } |
---|
132 | elseif ($type == 'pgsql') { |
---|
133 | db_query("SET TIME ZONE INTERVAL '$offset' HOUR TO MINUTE"); |
---|
134 | } |
---|
135 | $already_set = TRUE; |
---|
136 | } |
---|
137 | } |
---|
138 | |
---|
139 | /** |
---|
140 | * Return timezone offset for the date being processed. |
---|
141 | */ |
---|
142 | function get_offset() { |
---|
143 | if (!empty($this->db_timezone) && !empty($this->local_timezone)) { |
---|
144 | if ($this->db_timezone != $this->local_timezone) { |
---|
145 | $date = date_now($this->db_timezone); |
---|
146 | date_timezone_set($date, timezone_open($this->local_timezone)); |
---|
147 | return date_offset_get($date); |
---|
148 | } |
---|
149 | } |
---|
150 | return 0; |
---|
151 | } |
---|
152 | |
---|
153 | /** |
---|
154 | * Helper function to create cross-database SQL dates. |
---|
155 | * |
---|
156 | * @param $field |
---|
157 | * The real table and field name, like 'tablename.fieldname'. |
---|
158 | * @param $offset |
---|
159 | * The name of a field that holds the timezone offset or an |
---|
160 | * offset value. If NULL, the normal Drupal timezone handling |
---|
161 | * will be used, if $offset = 0 no adjustment will be made. |
---|
162 | * @return |
---|
163 | * An appropriate SQL string for the db type and field type. |
---|
164 | */ |
---|
165 | function sql_field($field, $offset = NULL) { |
---|
166 | if (drupal_strtoupper($field) == 'NOW') { |
---|
167 | // NOW() will be in UTC since that is what we set the db timezone to. |
---|
168 | $this->local_timezone = 'UTC'; |
---|
169 | return $this->sql_offset('NOW()', $offset); |
---|
170 | } |
---|
171 | switch ($this->db_type) { |
---|
172 | case 'mysql': |
---|
173 | case 'mysqli': |
---|
174 | switch ($this->date_type) { |
---|
175 | case DATE_UNIX: |
---|
176 | $field = "FROM_UNIXTIME($field)"; |
---|
177 | break; |
---|
178 | case DATE_ISO: |
---|
179 | if (version_compare(db_version(), '4.1.1', '>=')) { |
---|
180 | $field = "STR_TO_DATE($field, '%Y-%m-%%dT%T')"; |
---|
181 | } |
---|
182 | else { |
---|
183 | $field = "REPLACE($field, 'T', ' ')"; |
---|
184 | } |
---|
185 | break; |
---|
186 | case DATE_DATETIME: |
---|
187 | break; |
---|
188 | } |
---|
189 | break; |
---|
190 | case 'pgsql': |
---|
191 | switch ($this->date_type) { |
---|
192 | case DATE_UNIX: |
---|
193 | $field = "$field::ABSTIME"; |
---|
194 | break; |
---|
195 | case DATE_ISO: |
---|
196 | $field = "TO_DATE($field, 'FMYYYY-FMMM-FMDDTFMHH24:FMMI:FMSS')"; |
---|
197 | break; |
---|
198 | case DATE_DATETIME: |
---|
199 | break; |
---|
200 | } |
---|
201 | break; |
---|
202 | } |
---|
203 | // Adjust the resulting value to the right timezone/offset. |
---|
204 | return $this->sql_tz($field, $offset); |
---|
205 | } |
---|
206 | |
---|
207 | /** |
---|
208 | * Adjust a field value by an offset in seconds. |
---|
209 | */ |
---|
210 | function sql_offset($field, $offset = NULL) { |
---|
211 | if (!empty($offset)) { |
---|
212 | switch ($this->db_type) { |
---|
213 | case 'mysql': |
---|
214 | case 'mysqli': |
---|
215 | if (version_compare(db_version(), '4.1.1', '>=')) { |
---|
216 | return "ADDTIME($field, SEC_TO_TIME($offset))"; |
---|
217 | } |
---|
218 | else { |
---|
219 | return "DATE_ADD(CAST($field AS DATETIME), INTERVAL $offset SECOND)"; |
---|
220 | } |
---|
221 | case 'pgsql': |
---|
222 | return "($field + INTERVAL '$offset SECONDS')";; |
---|
223 | } |
---|
224 | } |
---|
225 | return $field; |
---|
226 | } |
---|
227 | |
---|
228 | /** |
---|
229 | * Adjust a field value by time interval. |
---|
230 | * |
---|
231 | * @param $field |
---|
232 | * The field to be adjusted. |
---|
233 | * @param $direction |
---|
234 | * Either ADD or SUB. |
---|
235 | * @param $count |
---|
236 | * The number of values to adjust. |
---|
237 | * @param $granularity |
---|
238 | * The granularity of the adjustment, should be singular, |
---|
239 | * like SECOND, MINUTE, DAY, HOUR. |
---|
240 | */ |
---|
241 | function sql_date_math($field, $direction, $count, $granularity) { |
---|
242 | $granularity = drupal_strtoupper($granularity); |
---|
243 | switch ($this->db_type) { |
---|
244 | case 'mysql': |
---|
245 | case 'mysqli': |
---|
246 | switch ($direction) { |
---|
247 | case 'ADD': |
---|
248 | return "DATE_ADD(CAST($field AS DATETIME), INTERVAL $count $granularity)"; |
---|
249 | case 'SUB': |
---|
250 | return "DATE_SUB(CAST($field AS DATETIME), INTERVAL $count $granularity)"; |
---|
251 | } |
---|
252 | |
---|
253 | case 'pgsql': |
---|
254 | $granularity .= 'S'; |
---|
255 | switch ($direction) { |
---|
256 | case 'ADD': |
---|
257 | return "($field + INTERVAL '$count $granularity')"; |
---|
258 | case 'SUB': |
---|
259 | return "($field - INTERVAL '$count $granularity')"; |
---|
260 | } |
---|
261 | } |
---|
262 | return $field; |
---|
263 | } |
---|
264 | |
---|
265 | /** |
---|
266 | * Select a date value from the database, adjusting the value |
---|
267 | * for the timezone. |
---|
268 | * |
---|
269 | * Check whether database timezone conversion is supported in |
---|
270 | * this system and use it if possible, otherwise use an |
---|
271 | * offset. |
---|
272 | * |
---|
273 | * @param $offset |
---|
274 | * Set a fixed offset or offset field to use for the date. |
---|
275 | * If set, no timezone conversion will be done and the |
---|
276 | * offset will be used. |
---|
277 | */ |
---|
278 | function sql_tz($field, $offset = NULL) { |
---|
279 | // If the timezones are values they need to be quoted, but |
---|
280 | // if they are field names they do not. |
---|
281 | $db_zone = $this->db_timezone_field ? $this->db_timezone_field : "'{$this->db_timezone}'"; |
---|
282 | $localzone = $this->local_timezone_field ? $this->local_timezone_field : "'{$this->local_timezone}'"; |
---|
283 | |
---|
284 | // If a fixed offset is required, use it. |
---|
285 | if ($offset !== NULL) { |
---|
286 | return $this->sql_offset($field, $offset); |
---|
287 | } |
---|
288 | // If the db and local timezones are the same, make no adjustment. |
---|
289 | elseif ($db_zone == $localzone) { |
---|
290 | return $this->sql_offset($field, 0); |
---|
291 | } |
---|
292 | // If the db has no timezone support, adjust by the offset, |
---|
293 | // could be either a field name or a value. |
---|
294 | elseif (!$this->db_tz_support()) { |
---|
295 | if (!empty($this->offset_field)) { |
---|
296 | return $this->sql_offset($field, $this->offset_field); |
---|
297 | } |
---|
298 | else { |
---|
299 | return $this->sql_offset($field, $this->get_offset()); |
---|
300 | } |
---|
301 | } |
---|
302 | // Otherwise make a database timezone adjustment to the field. |
---|
303 | else { |
---|
304 | switch ($this->db_type) { |
---|
305 | case 'mysql': |
---|
306 | case 'mysqli': |
---|
307 | return "CONVERT_TZ($field, $db_zone, $localzone)"; |
---|
308 | case 'pgsql': |
---|
309 | // WITH TIME ZONE assumes the date is using the system |
---|
310 | // timezone, which should have been set to UTC. |
---|
311 | return "$field::timestamp with time zone AT TIME ZONE $localzone"; |
---|
312 | } |
---|
313 | } |
---|
314 | } |
---|
315 | |
---|
316 | /** |
---|
317 | * Helper function to create cross-database SQL date formatting. |
---|
318 | * |
---|
319 | * @param $format |
---|
320 | * A format string for the result, like 'Y-m-d H:i:s'. |
---|
321 | * @param $field |
---|
322 | * The real table and field name, like 'tablename.fieldname'. |
---|
323 | * @return |
---|
324 | * An appropriate SQL string for the db type and field type. |
---|
325 | */ |
---|
326 | function sql_format($format, $field) { |
---|
327 | switch ($this->db_type) { |
---|
328 | case 'mysql': |
---|
329 | case 'mysqli': |
---|
330 | $replace = array( |
---|
331 | 'Y' => '%Y', 'y' => '%y', |
---|
332 | 'm' => '%m', 'n' => '%c', |
---|
333 | 'd' => '%%d', 'j' => '%e', |
---|
334 | 'H' => '%H', |
---|
335 | 'i' => '%i', |
---|
336 | 's' => '%%s', |
---|
337 | '\WW' => 'W%U', |
---|
338 | ); |
---|
339 | $format = strtr($format, $replace); |
---|
340 | return "DATE_FORMAT($field, '$format')"; |
---|
341 | case 'pgsql': |
---|
342 | $replace = array( |
---|
343 | 'Y' => 'YYYY', 'y' => 'Y', |
---|
344 | 'm' => 'MM', 'n' => 'M', |
---|
345 | 'd' => 'DD', 'j' => 'D', |
---|
346 | 'H' => 'HH24', |
---|
347 | 'i' => 'MI', |
---|
348 | 's' => 'SS', |
---|
349 | '\T' => '"T"', |
---|
350 | //'\W' => // TODO, what should this be? |
---|
351 | ); |
---|
352 | $format = strtr($format, $replace); |
---|
353 | return "TO_CHAR($field, '$format')"; |
---|
354 | } |
---|
355 | } |
---|
356 | |
---|
357 | /** |
---|
358 | * Helper function to create cross-database SQL date extraction. |
---|
359 | * |
---|
360 | * @param $extract_type |
---|
361 | * The type of value to extract from the date, like 'MONTH'. |
---|
362 | * @param $field |
---|
363 | * The real table and field name, like 'tablename.fieldname'. |
---|
364 | * @return |
---|
365 | * An appropriate SQL string for the db type and field type. |
---|
366 | */ |
---|
367 | function sql_extract($extract_type, $field) { |
---|
368 | // Note there is no space after FROM to avoid db_rewrite problems |
---|
369 | // see http://drupal.org/node/79904. |
---|
370 | switch (drupal_strtoupper($extract_type)) { |
---|
371 | case ('DATE'): |
---|
372 | return $field; |
---|
373 | case ('YEAR'): |
---|
374 | return "EXTRACT(YEAR FROM($field))"; |
---|
375 | case ('MONTH'): |
---|
376 | return "EXTRACT(MONTH FROM($field))"; |
---|
377 | case ('DAY'): |
---|
378 | return "EXTRACT(DAY FROM($field))"; |
---|
379 | case ('HOUR'): |
---|
380 | return "EXTRACT(HOUR FROM($field))"; |
---|
381 | case ('MINUTE'): |
---|
382 | return "EXTRACT(MINUTE FROM($field))"; |
---|
383 | case ('SECOND'): |
---|
384 | return "EXTRACT(SECOND FROM($field))"; |
---|
385 | case ('WEEK'): // ISO week number for date |
---|
386 | switch ($this->db_type) { |
---|
387 | case ('mysql'): |
---|
388 | case ('mysqli'): |
---|
389 | // WEEK using arg 3 in mysql should return the same value as postgres EXTRACT |
---|
390 | return "WEEK($field, 3)"; |
---|
391 | case ('pgsql'): |
---|
392 | return "EXTRACT(WEEK FROM($field))"; |
---|
393 | } |
---|
394 | case ('DOW'): |
---|
395 | switch ($this->db_type) { |
---|
396 | case ('mysql'): |
---|
397 | case ('mysqli'): |
---|
398 | // mysql returns 1 for Sunday through 7 for Saturday |
---|
399 | // php date functions and postgres use 0 for Sunday and 6 for Saturday |
---|
400 | return "INTEGER(DAYOFWEEK($field) - 1)"; |
---|
401 | case ('pgsql'): |
---|
402 | return "EXTRACT(DOW FROM($field))"; |
---|
403 | } |
---|
404 | case ('DOY'): |
---|
405 | switch ($this->db_type) { |
---|
406 | case ('mysql'): |
---|
407 | case ('mysqli'): |
---|
408 | return "DAYOFYEAR($field)"; |
---|
409 | case ('pgsql'): |
---|
410 | return "EXTRACT(DOY FROM($field))"; |
---|
411 | } |
---|
412 | } |
---|
413 | } |
---|
414 | |
---|
415 | /** |
---|
416 | * Create a where clause to compare a complete date field to a complete date value. |
---|
417 | * |
---|
418 | * @param string $type |
---|
419 | * The type of value we're comparing to, could be another field |
---|
420 | * or a date value. |
---|
421 | * @param string $field |
---|
422 | * The db table and field name, like "$table.$field". |
---|
423 | * @param string $operator |
---|
424 | * The db comparison operator to use, like '='. |
---|
425 | * @param int $value |
---|
426 | * The value to compare the extracted date part to, could be a |
---|
427 | * field name or a date string or NOW(). |
---|
428 | * @return |
---|
429 | * SQL for the where clause for this operation. |
---|
430 | */ |
---|
431 | function sql_where_date($type, $field, $operator, $value, $adjustment = NULL) { |
---|
432 | $type = drupal_strtoupper($type); |
---|
433 | if (drupal_strtoupper($value) == 'NOW') { |
---|
434 | $value = $this->sql_field('NOW', $adjustment); |
---|
435 | } |
---|
436 | elseif ($type == 'FIELD') { |
---|
437 | $value = $this->sql_field($value, $adjustment); |
---|
438 | } |
---|
439 | elseif ($type == 'DATE') { |
---|
440 | $date = date_make_date($value, date_default_timezone_name(), DATE_DATETIME); |
---|
441 | if (!empty($adjustment)) { |
---|
442 | date_modify($date, $adjustment .' seconds'); |
---|
443 | } |
---|
444 | // When comparing a field to a date we can avoid doing timezone |
---|
445 | // conversion by altering the comparison date to the db timezone. |
---|
446 | // This won't work if the timezone is a field instead of a value. |
---|
447 | if (empty($this->db_timezone_field) && empty($this->local_timezone_field) && $this->db_timezone_field != $this->local_timezone_field) { |
---|
448 | date_timezone_set($date, timezone_open($this->db_timezone)); |
---|
449 | $this->local_timezone = $this->db_timezone; |
---|
450 | } |
---|
451 | $value = "'". date_format_date($date, 'custom', DATE_FORMAT_DATETIME) ."'"; |
---|
452 | } |
---|
453 | if ($this->local_timezone != $this->db_timezone) { |
---|
454 | $field = $this->sql_field($field); |
---|
455 | } |
---|
456 | else { |
---|
457 | $field = $this->sql_field($field, 0); |
---|
458 | } |
---|
459 | return "$field $operator $value"; |
---|
460 | } |
---|
461 | |
---|
462 | /** |
---|
463 | * Create a where clause to compare an extracted part of a field to an integer value. |
---|
464 | * |
---|
465 | * @param string $part |
---|
466 | * The part to extract, YEAR, MONTH, DAY, etc. |
---|
467 | * @param string $field |
---|
468 | * The db table and field name, like "$table.$field". |
---|
469 | * @param string $operator |
---|
470 | * The db comparison operator to use, like '='. |
---|
471 | * @param int $value |
---|
472 | * The integer value to compare the extracted date part to. |
---|
473 | * @return |
---|
474 | * SQL for the where clause for this operation. |
---|
475 | */ |
---|
476 | function sql_where_extract($part, $field, $operator, $value, $adjustment = NULL) { |
---|
477 | $field = $this->sql_field($field, $adjustment); |
---|
478 | return $this->sql_extract($part, $field) ." $operator $value"; |
---|
479 | } |
---|
480 | |
---|
481 | /** |
---|
482 | * Create a where clause to compare a formated field to a formated value. |
---|
483 | * |
---|
484 | * @param string $format |
---|
485 | * The format to use on the date and the value when comparing them. |
---|
486 | * @param string $field |
---|
487 | * The db table and field name, like "$table.$field". |
---|
488 | * @param string $operator |
---|
489 | * The db comparison operator to use, like '='. |
---|
490 | * @param string $value |
---|
491 | * The value to compare the extracted date part to, could be a |
---|
492 | * field name or a date string or NOW(). |
---|
493 | * @return |
---|
494 | * SQL for the where clause for this operation. |
---|
495 | */ |
---|
496 | function sql_where_format($format, $field, $operator, $value, $adjustment = NULL) { |
---|
497 | $field = $this->sql_field($field, $adjustment); |
---|
498 | return $this->sql_format($format, $field) ." $operator '$value'"; |
---|
499 | } |
---|
500 | |
---|
501 | /** |
---|
502 | * An array of all date parts, |
---|
503 | * optionally limited to an array of allowed parts. |
---|
504 | */ |
---|
505 | function date_parts($limit = NULL) { |
---|
506 | $parts = array( |
---|
507 | 'year' => date_t('Year', 'datetime'), 'month' => date_t('Month', 'datetime'), 'day' => date_t('Day', 'datetime'), |
---|
508 | 'hour' => date_t('Hour', 'datetime'), 'minute' => date_t('Minute', 'datetime'), 'second' => date_t('Second', 'datetime'), |
---|
509 | ); |
---|
510 | if (!empty($limit)) { |
---|
511 | $last = FALSE; |
---|
512 | foreach ($parts as $key => $part) { |
---|
513 | if ($last) { |
---|
514 | unset($parts[$key]); |
---|
515 | } |
---|
516 | if ($key == $limit) { |
---|
517 | $last = TRUE; |
---|
518 | } |
---|
519 | } |
---|
520 | } |
---|
521 | return $parts; |
---|
522 | } |
---|
523 | |
---|
524 | /** |
---|
525 | * Part information. |
---|
526 | * |
---|
527 | * @param $op |
---|
528 | * 'min', 'max', 'format', 'sep', 'empty_now', 'empty_min', 'empty_max'. |
---|
529 | * Returns all info if empty. |
---|
530 | * @param $part |
---|
531 | * 'year', 'month', 'day', 'hour', 'minute', or 'second. |
---|
532 | * returns info for all parts if empty. |
---|
533 | */ |
---|
534 | function part_info($op = NULL, $part = NULL) { |
---|
535 | $info = array(); |
---|
536 | $info['min'] = array( |
---|
537 | 'year' => 100, 'month' => 1, 'day' => 1, |
---|
538 | 'hour' => 0, 'minute' => 0, 'second' => 0); |
---|
539 | $info['max'] = array( |
---|
540 | 'year' => 4000, 'month' => 12, 'day' => 31, |
---|
541 | 'hour' => 23, 'minute' => 59, 'second' => 59); |
---|
542 | $info['format'] = array( |
---|
543 | 'year' => 'Y', 'month' => 'm', 'day' => 'd', |
---|
544 | 'hour' => 'H', 'minute' => 'i', 'second' => 's'); |
---|
545 | $info['sep'] = array( |
---|
546 | 'year' => '', 'month' => '-', 'day' => '-', |
---|
547 | 'hour' => ' ', 'minute' => ':', 'second' => ':'); |
---|
548 | $info['empty_now'] = array( |
---|
549 | 'year' => date('Y'), 'month' => date('m'), 'day' => min('28', date('d')), |
---|
550 | 'hour' => date('H'), 'minute' => date('i'), 'second' => date('s')); |
---|
551 | $info['empty_min'] = array( |
---|
552 | 'year' => '1000', 'month' => '01', 'day' => '01', |
---|
553 | 'hour' => '00', 'minute' => '00', 'second' => '00'); |
---|
554 | $info['empty_max'] = array( |
---|
555 | 'year' => '9999', 'month' => '12', 'day' => '31', |
---|
556 | 'hour' => '23', 'minute' => '59', 'second' => '59'); |
---|
557 | if (!empty($op)) { |
---|
558 | if (!empty($part)) { |
---|
559 | return $info[$op][$part]; |
---|
560 | } |
---|
561 | else { |
---|
562 | return $info[$op]; |
---|
563 | } |
---|
564 | } |
---|
565 | return $info; |
---|
566 | } |
---|
567 | |
---|
568 | /** |
---|
569 | * Create a complete datetime value out of an |
---|
570 | * incomplete array of selected values. |
---|
571 | * |
---|
572 | * For example, array('year' => 2008, 'month' => 05) will fill |
---|
573 | * in the day, hour, minute and second with the earliest possible |
---|
574 | * values if type = 'min', the latest possible values if type = 'max', |
---|
575 | * and the current values if type = 'now'. |
---|
576 | */ |
---|
577 | function complete_date($selected, $type = 'now') { |
---|
578 | if (empty($selected)) { |
---|
579 | return ''; |
---|
580 | } |
---|
581 | // Special case for weeks. |
---|
582 | if (array_key_exists('week', $selected)) { |
---|
583 | $dates = date_week_range($selected['week'], $selected['year']); |
---|
584 | switch ($type) { |
---|
585 | case 'empty_now': |
---|
586 | case 'empty_min': |
---|
587 | case 'min': |
---|
588 | return date_format($dates[0], 'Y-m-d H:i:s'); |
---|
589 | case 'empty_max': |
---|
590 | case 'max': |
---|
591 | return date_format($dates[1], 'Y-m-d H:i:s'); |
---|
592 | default: |
---|
593 | return; |
---|
594 | } |
---|
595 | } |
---|
596 | |
---|
597 | $compare = array_merge($this->part_info('empty_'. $type), $selected); |
---|
598 | // If this is a max date, make sure the last day of |
---|
599 | // the month is the right one for this date. |
---|
600 | if ($type == 'max') { |
---|
601 | $compare['day'] = date_days_in_month($compare['year'], $compare['month']); |
---|
602 | } |
---|
603 | $value = ''; |
---|
604 | $separators = $this->part_info('sep'); |
---|
605 | foreach ($this->date_parts() as $key => $name) { |
---|
606 | $value .= $separators[$key] . (!empty($selected[$key]) ? $selected[$key] : $compare[$key]); |
---|
607 | } |
---|
608 | return $value; |
---|
609 | } |
---|
610 | /** |
---|
611 | * Convert a format string into help text, |
---|
612 | * i.e. 'Y-m-d' becomes 'YYYY-MM-DD'. |
---|
613 | * |
---|
614 | * @param unknown_type $format |
---|
615 | * @return unknown |
---|
616 | */ |
---|
617 | function format_help($format) { |
---|
618 | $replace = array( |
---|
619 | 'Y' => 'YYYY', 'm' => 'MM', 'd' => 'DD', |
---|
620 | 'H' => 'HH', 'i' => 'MM', 's' => 'SS', '\T' => 'T'); |
---|
621 | return strtr($format, $replace); |
---|
622 | } |
---|
623 | |
---|
624 | /** |
---|
625 | * A function to test the validity of various date parts |
---|
626 | */ |
---|
627 | function part_is_valid($value, $type) { |
---|
628 | if ( !preg_match('/^[0-9]*$/', $value) ) { |
---|
629 | return FALSE; |
---|
630 | } |
---|
631 | $value = intval($value); |
---|
632 | if ($value <= 0) return FALSE; |
---|
633 | switch ($type) { |
---|
634 | case 'year': |
---|
635 | if ($value < DATE_MIN_YEAR) return FALSE; |
---|
636 | break; |
---|
637 | case 'month': |
---|
638 | if ($value < 0 || $value > 12) return FALSE; |
---|
639 | break; |
---|
640 | case 'day': |
---|
641 | if ($value < 0 || $value > 31) return FALSE; |
---|
642 | break; |
---|
643 | case 'week': |
---|
644 | if ($value < 0 || $value > 53) return FALSE; |
---|
645 | } |
---|
646 | return TRUE; |
---|
647 | } |
---|
648 | |
---|
649 | function views_formats($granularity, $type = 'sql') { |
---|
650 | $formats = array('display', 'sql'); |
---|
651 | // Start with the site long date format and add seconds to it |
---|
652 | $long = str_replace(':i', ':i:s', variable_get('date_format_long', 'l, F j, Y - H:i')); |
---|
653 | switch ($granularity) { |
---|
654 | case ('year'): |
---|
655 | $formats['display'] = 'Y'; |
---|
656 | $formats['sql'] = 'Y'; |
---|
657 | break; |
---|
658 | case ('month'): |
---|
659 | $formats['display'] = date_limit_format($long, array('year', 'month')); |
---|
660 | $formats['sql'] = 'Y-m'; |
---|
661 | break; |
---|
662 | case ('day'): |
---|
663 | $formats['display'] = date_limit_format($long, array('year', 'month', 'day')); |
---|
664 | $formats['sql'] = 'Y-m-d'; |
---|
665 | break; |
---|
666 | case ('hour'): |
---|
667 | $formats['display'] = date_limit_format($long, array('year', 'month', 'day', 'hour')); |
---|
668 | $formats['sql'] = 'Y-m-d\TH'; |
---|
669 | break; |
---|
670 | case ('minute'): |
---|
671 | $formats['display'] = date_limit_format($long, array('year', 'month', 'day', 'hour', 'minute')); |
---|
672 | $formats['sql'] = 'Y-m-d\TH:i'; |
---|
673 | break; |
---|
674 | case ('second'): |
---|
675 | $formats['display'] = date_limit_format($long, array('year', 'month', 'day', 'hour', 'minute', 'second')); |
---|
676 | $formats['sql'] = 'Y-m-d\TH:i:s'; |
---|
677 | break; |
---|
678 | case ('week'): |
---|
679 | $formats['display'] = 'F j Y (W)'; |
---|
680 | $formats['sql'] = 'Y-\WW'; |
---|
681 | break; |
---|
682 | } |
---|
683 | return $formats[$type]; |
---|
684 | } |
---|
685 | |
---|
686 | function granularity_form($granularity) { |
---|
687 | $form = array( |
---|
688 | '#title' => t('Granularity'), |
---|
689 | '#type' => 'radios', |
---|
690 | '#default_value' => $granularity, |
---|
691 | '#options' => $this->date_parts(), |
---|
692 | ); |
---|
693 | return $form; |
---|
694 | } |
---|
695 | |
---|
696 | /** |
---|
697 | * Parse date parts from an ISO date argument. |
---|
698 | * |
---|
699 | * Based on ISO 8601 date duration and time interval standards. |
---|
700 | * |
---|
701 | * See http://en.wikipedia.org/wiki/ISO_8601#Week_dates for definitions of ISO weeks. |
---|
702 | * See http://en.wikipedia.org/wiki/ISO_8601#Duration for definitions of ISO duration and time interval. |
---|
703 | * |
---|
704 | * Parses a value like 2006-01-01--2006-01-15, or 2006-W24, or @P1W. |
---|
705 | * Separate from and to dates or date and period with a double hyphen (--). |
---|
706 | * |
---|
707 | * The 'to' portion of the argument can be eliminated if it is the same as the 'from' portion. |
---|
708 | * Use @ instead of a date to substitute in the current date and time. |
---|
709 | * |
---|
710 | * Use periods (P1H, P1D, P1W, P1M, P1Y) to get next hour/day/week/month/year from now. |
---|
711 | * Use date before P sign to get next hour/day/week/month/year from that date. |
---|
712 | * Use period then date to get a period that ends on the date. |
---|
713 | * |
---|
714 | */ |
---|
715 | function arg_parts($argument) { |
---|
716 | $values = array(); |
---|
717 | // Keep mal-formed arguments from creating errors. |
---|
718 | if (empty($argument) || is_array($argument)) { |
---|
719 | return array('date' => array(), 'period' => array()); |
---|
720 | } |
---|
721 | $fromto = explode('--', $argument); |
---|
722 | foreach ($fromto as $arg) { |
---|
723 | $parts = array(); |
---|
724 | if ($arg == '@') { |
---|
725 | $parts['date'] = date_array(date_now()); |
---|
726 | } |
---|
727 | elseif (preg_match('/(\d{4})?-?(W)?(\d{1,2})?-?(\d{1,2})?[T\s]?(\d{1,2})?:?(\d{1,2})?:?(\d{1,2})?/', $arg, $matches)) { |
---|
728 | $date = array(); |
---|
729 | if (!empty($matches[1])) $date['year'] = $matches[1]; |
---|
730 | if (!empty($matches[3])) { |
---|
731 | if (empty($matches[2])) { |
---|
732 | $date['month'] = $matches[3]; |
---|
733 | } |
---|
734 | else { |
---|
735 | $date['week'] = $matches[3]; |
---|
736 | } |
---|
737 | } |
---|
738 | if (!empty($matches[4])) $date['day'] = $matches[4]; |
---|
739 | if (!empty($matches[5])) $date['hour'] = $matches[5]; |
---|
740 | if (!empty($matches[6])) $date['minute'] = $matches[6]; |
---|
741 | if (!empty($matches[7])) $date['second'] = $matches[7]; |
---|
742 | $parts['date'] = $date; |
---|
743 | } |
---|
744 | if (preg_match('/^P(\d{1,4}[Y])?(\d{1,2}[M])?(\d{1,2}[W])?(\d{1,2}[D])?([T]{0,1})?(\d{1,2}[H])?(\d{1,2}[M])?(\d{1,2}[S])?/', $arg, $matches)) { |
---|
745 | $period = array(); |
---|
746 | if (!empty($matches[1])) $period['year'] = str_replace('Y', '', $matches[1]); |
---|
747 | if (!empty($matches[2])) $period['month'] = str_replace('M', '', $matches[2]); |
---|
748 | if (!empty($matches[3])) $period['week'] = str_replace('W', '', $matches[3]); |
---|
749 | if (!empty($matches[4])) $period['day'] = str_replace('D', '', $matches[4]); |
---|
750 | if (!empty($matches[6])) $period['hour'] = str_replace('H', '', $matches[6]); |
---|
751 | if (!empty($matches[7])) $period['minute'] = str_replace('M', '', $matches[7]); |
---|
752 | if (!empty($matches[8])) $period['second'] = str_replace('S', '', $matches[8]); |
---|
753 | $parts['period'] = $period; |
---|
754 | } |
---|
755 | $values[] = $parts; |
---|
756 | } |
---|
757 | return $values; |
---|
758 | } |
---|
759 | |
---|
760 | /** |
---|
761 | * Convert strings like '+1 day' to the ISO equivalent, like 'P1D'. |
---|
762 | */ |
---|
763 | function arg_replace($arg) { |
---|
764 | if (!preg_match('/([+|-])\s?([0-9]{1,32})\s?([day(s)?|week(s)?|month(s)?|year(s)?|hour(s)?|minute(s)?|second(s)?]{1,10})/', $arg, $results)) { |
---|
765 | return str_replace('now', '@', $arg); |
---|
766 | } |
---|
767 | $direction = $results[1]; |
---|
768 | $count = $results[2]; |
---|
769 | $item = $results[3]; |
---|
770 | |
---|
771 | $replace = array( |
---|
772 | 'now' => '@', |
---|
773 | '+' => 'P', |
---|
774 | '-' => 'P-', |
---|
775 | 'years' => 'Y', |
---|
776 | 'year' => 'Y', |
---|
777 | 'months' => 'M', |
---|
778 | 'month' => 'M', |
---|
779 | 'weeks' => 'W', |
---|
780 | 'week' => 'W', |
---|
781 | 'days' => 'D', |
---|
782 | 'day' => 'D', |
---|
783 | 'hours' => 'H', |
---|
784 | 'hour' => 'H', |
---|
785 | 'minutes' => 'M', |
---|
786 | 'minute' => 'M', |
---|
787 | 'seconds' => 'S', |
---|
788 | 'second' => 'S', |
---|
789 | ' ' => '', |
---|
790 | ' ' => '', |
---|
791 | ); |
---|
792 | $prefix = in_array($item, array('hours', 'hour', 'minutes', 'minute', 'seconds', 'second')) ? 'T' : ''; |
---|
793 | return $prefix . strtr($direction, $replace) . $count . strtr($item, $replace); |
---|
794 | } |
---|
795 | |
---|
796 | /** |
---|
797 | * Use the parsed values from the ISO argument to determine the |
---|
798 | * granularity of this period. |
---|
799 | */ |
---|
800 | function arg_granularity($arg) { |
---|
801 | $granularity = ''; |
---|
802 | $parts = $this->arg_parts($arg); |
---|
803 | $date = !empty($parts[0]['date']) ? $parts[0]['date'] : (!empty($parts[1]['date']) ? $parts[1]['date'] : array()); |
---|
804 | foreach ($date as $key => $part) { |
---|
805 | $granularity = $key; |
---|
806 | } |
---|
807 | return $granularity; |
---|
808 | } |
---|
809 | |
---|
810 | /** |
---|
811 | * Use the parsed values from the ISO argument to determine the |
---|
812 | * min and max date for this period. |
---|
813 | */ |
---|
814 | function arg_range($arg) { |
---|
815 | // Parse the argument to get its parts |
---|
816 | $parts = $this->arg_parts($arg); |
---|
817 | |
---|
818 | // Build a range from a period-only argument (assumes the min date is now.) |
---|
819 | if (empty($parts[0]['date']) && !empty($parts[0]['period']) && (empty($parts[1]))) { |
---|
820 | $min_date = date_now(); |
---|
821 | $max_date = drupal_clone($min_date); |
---|
822 | foreach ($parts[0]['period'] as $part => $value) { |
---|
823 | date_modify($max_date, "+$value $part"); |
---|
824 | } |
---|
825 | date_modify($max_date, '-1 second'); |
---|
826 | return array($min_date, $max_date); |
---|
827 | } |
---|
828 | // Build a range from a period to period argument |
---|
829 | if (empty($parts[0]['date']) && !empty($parts[0]['period']) && !empty($parts[1]['period'])) { |
---|
830 | $min_date = date_now(); |
---|
831 | $max_date = drupal_clone($min_date); |
---|
832 | foreach ($parts[0]['period'] as $part => $value) { |
---|
833 | date_modify($min_date, "+$value $part"); |
---|
834 | } |
---|
835 | date_modify($min_date, '-1 second'); |
---|
836 | foreach ($parts[1]['period'] as $part => $value) { |
---|
837 | date_modify($max_date, "+$value $part"); |
---|
838 | } |
---|
839 | date_modify($max_date, '-1 second'); |
---|
840 | return array($min_date, $max_date); |
---|
841 | } |
---|
842 | if (!empty($parts[0]['date'])) { |
---|
843 | $value = date_fuzzy_datetime($this->complete_date($parts[0]['date'], 'min')); |
---|
844 | $min_date = date_make_date($value, date_default_timezone_name(), DATE_ISO); |
---|
845 | // Build a range from a single date-only argument. |
---|
846 | if (empty($parts[1]) || (empty($parts[1]['date']) && empty($parts[1]['period']))) { |
---|
847 | $value = date_fuzzy_datetime($this->complete_date($parts[0]['date'], 'max')); |
---|
848 | $max_date = date_make_date($value, date_default_timezone_name(), DATE_ISO); |
---|
849 | return array($min_date, $max_date); |
---|
850 | } |
---|
851 | // Build a range from start date + period. |
---|
852 | elseif (!empty($parts[1]['period'])) { |
---|
853 | foreach ($parts[1]['period'] as $part => $value) { |
---|
854 | $max_date = drupal_clone($min_date); |
---|
855 | date_modify($max_date, "+$value $part"); |
---|
856 | } |
---|
857 | date_modify($max_date, '-1 second'); |
---|
858 | return array($min_date, $max_date); |
---|
859 | } |
---|
860 | } |
---|
861 | // Build a range from start date and end date. |
---|
862 | if (!empty($parts[1]['date'])) { |
---|
863 | $value = date_fuzzy_datetime($this->complete_date($parts[1]['date'], 'max')); |
---|
864 | $max_date = date_make_date($value, date_default_timezone_name(), DATE_ISO); |
---|
865 | if (isset($min_date)) { |
---|
866 | return array($min_date, $max_date); |
---|
867 | } |
---|
868 | } |
---|
869 | // Build a range from period + end date. |
---|
870 | if (!empty($parts[0]['period'])) { |
---|
871 | $min_date = date_now(); |
---|
872 | foreach ($parts[0]['period'] as $part => $value) { |
---|
873 | date_modify($min_date, "$value $part"); |
---|
874 | } |
---|
875 | return array($min_date, $max_date); |
---|
876 | } |
---|
877 | // Intercept invalid info and fall back to the current date. |
---|
878 | $now = date_now(); |
---|
879 | return array($now, $now); |
---|
880 | } |
---|
881 | } |
---|