Projet

Général

Profil

Paste
Télécharger (36,6 ko) Statistiques
| Branche: | Révision:

root / drupal7 / sites / all / modules / date / date_api / date_api_sql.inc @ 599a39cd

1
<?php
2

    
3
/**
4
 * @file
5
 * SQL helper for Date API.
6
 *
7
 * @todo
8
 * Add experimental support for sqlite: http://www.sqlite.org/lang_datefunc.html
9
 * and Oracle (http://psoug.org/reference/date_func.html and
10
 * http://psoug.org/reference/datatypes.html) date/time functions.
11
 */
12

    
13
/**
14
 * A helper function to do cross-database concatation of date parts.
15
 *
16
 * @param array $array
17
 *   An array of values to be concatonated in sql.
18
 *
19
 * @return string
20
 *   Correct sql string for database type.
21
 */
22
function date_sql_concat(array $array) {
23
  switch (Database::getConnection()->databaseType()) {
24
    case 'mysql':
25
      return "CONCAT(" . implode(",", $array) . ")";
26

    
27
    case 'pgsql':
28
      return implode(" || ", $array);
29
  }
30
}
31

    
32
/**
33
 * Helper function to do cross-database NULL replacements.
34
 *
35
 * @param array $array
36
 *   An array of values to test for NULL values.
37
 *
38
 * @return string
39
 *   SQL statement to return the first non-NULL value in the list.
40
 */
41
function date_sql_coalesce(array $array) {
42
  switch (Database::getConnection()->databaseType()) {
43
    case 'mysql':
44
    case 'pgsql':
45
      return "COALESCE(" . implode(',', $array) . ")";
46
  }
47
}
48

    
49
/**
50
 * A helper function to do cross-database padding of date parts.
51
 *
52
 * @param string $str
53
 *   A string to apply padding to.
54
 * @param int $size
55
 *   The size the final string should be.
56
 * @param string $pad
57
 *   The value to pad the string with.
58
 * @param string $side
59
 *   The side of the string to pad.
60
 */
61
function date_sql_pad($str, $size = 2, $pad = '0', $side = 'l') {
62
  switch ($side) {
63
    case 'r':
64
      return "RPAD($str, $size, '$pad')";
65

    
66
    default:
67
      return "LPAD($str, $size, '$pad')";
68
  }
69
}
70

    
71
/**
72
 * A class to manipulate date SQL.
73
 */
74
class date_sql_handler {
75
  var $db_type = NULL;
76
  var $date_type = DATE_DATETIME;
77
  // A string timezone name.
78
  var $db_timezone = 'UTC';
79
  // A string timezone name.
80
  var $local_timezone = NULL;
81
  // Use if the db timezone is stored in a field.
82
  var $db_timezone_field = NULL;
83
  // Use if the local timezone is stored in a field.
84
  var $local_timezone_field = NULL;
85
  // Use if the offset is stored in a field.
86
  var $offset_field = NULL;
87

    
88
  /**
89
   * The object constuctor.
90
   */
91
  public function __construct($date_type = DATE_DATETIME, $local_timezone = NULL, $offset = '+00:00') {
92
    $this->db_type = Database::getConnection()->databaseType();
93
    $this->date_type = $date_type;
94
    $this->db_timezone = 'UTC';
95
    $this->local_timezone = isset($local_timezone) ? $local_timezone : date_default_timezone();
96
    $this->set_db_timezone($offset);
97
  }
98

    
99
  /**
100
   * See if the db has timezone name support.
101
   */
102
  public function db_tz_support($reset = FALSE) {
103
    $has_support = variable_get('date_db_tz_support', -1);
104
    if ($has_support == -1 || $reset) {
105
      $has_support = FALSE;
106
      switch ($this->db_type) {
107
        case 'mysql':
108
          $test = db_query("SELECT CONVERT_TZ('2008-02-15 12:00:00', 'UTC', 'US/Central')")->fetchField();
109
          if ($test == '2008-02-15 06:00:00') {
110
            $has_support = TRUE;
111
          }
112
          break;
113

    
114
        case 'pgsql':
115
          // PostgreSQL doesn't always have timezone support enabled, so catch
116
          // exceptions so they don't break the site. This is safe to do as it
117
          // is only checking to see if timezones are actually supported.
118
          try {
119
            $test = db_query("SELECT '2008-02-15 12:00:00 UTC' AT TIME ZONE 'US/Central'")->fetchField();
120
            if ($test == '2008-02-15 06:00:00') {
121
              $has_support = TRUE;
122
            }
123
          }
124
          catch (PDOException $e) {
125
            // No support.
126
          }
127
          break;
128
      }
129
      variable_set('date_db_tz_support', $has_support);
130
    }
131
    return $has_support;
132
  }
133

    
134
  /**
135
   * Set the database timzone offset.
136
   *
137
   * Setting the db timezone to UTC is done to ensure consistency in date
138
   * handling whether or not the database can do proper timezone conversion.
139
   *
140
   * Views filters that not exposed are cached and won't set the timezone
141
   * so views date filters should add 'cacheable' => 'no' to their
142
   * definitions to ensure that the database timezone gets set properly
143
   * when the query is executed.
144
   *
145
   * @param string $offset
146
   *   An offset value to set the database timezone to. This will only
147
   *   set a fixed offset, not a timezone, so any value other than
148
   *   '+00:00' should be used with caution.
149
   */
150
  public function set_db_timezone($offset = '+00:00') {
151
    static $already_set = FALSE;
152
    $type = Database::getConnection()->databaseType();
153
    if (!$already_set) {
154
      switch ($type) {
155
        case 'mysql':
156
          db_query("SET @@session.time_zone = '$offset'");
157
          break;
158

    
159
        case 'pgsql':
160
          db_query("SET TIME ZONE INTERVAL '$offset' HOUR TO MINUTE");
161
          break;
162

    
163
        case 'sqlsrv':
164
          // Issue #1201342, This is the wrong way to set the timezone, this
165
          // still needs to be fixed. In the meantime, commenting this out makes
166
          // SQLSRV functional.
167
          // db_query('TimeZone.setDefault(TimeZone.getTimeZone("GMT"))');
168
          break;
169
      }
170
      $already_set = TRUE;
171
    }
172
  }
173

    
174
  /**
175
   * Return timezone offset for the date being processed.
176
   */
177
  public function get_offset($comp_date = NULL) {
178
    if (!empty($this->db_timezone) && !empty($this->local_timezone)) {
179
      if ($this->db_timezone != $this->local_timezone) {
180
        if (empty($comp_date)) {
181
          $comp_date = date_now($this->db_timezone);
182
        }
183
        $comp_date->setTimezone(timezone_open($this->local_timezone));
184
        return date_offset_get($comp_date);
185
      }
186
    }
187
    return 0;
188
  }
189

    
190
  /**
191
   * Helper function to create cross-database SQL dates.
192
   *
193
   * @param string $field
194
   *   The real table and field name, like 'tablename.fieldname' .
195
   * @param string $offset
196
   *   The name of a field that holds the timezone offset or an
197
   *   offset value. If NULL, the normal Drupal timezone handling
198
   *   will be used, if $offset = 0 no adjustment will be made.
199
   *
200
   * @return string
201
   *   An appropriate SQL string for the db type and field type.
202
   */
203
  function sql_field($field, $offset = NULL, $comp_date = NULL) {
204
    if (strtoupper($field) == 'NOW') {
205
      // NOW() will be in UTC since that is what we set the db timezone to.
206
      $this->local_timezone = 'UTC';
207
      return $this->sql_offset('NOW()', $offset);
208
    }
209
    switch ($this->db_type) {
210
      case 'mysql':
211
        switch ($this->date_type) {
212
          case DATE_UNIX:
213
            $field = "FROM_UNIXTIME($field)";
214
            break;
215

    
216
          case DATE_ISO:
217
            $field = "STR_TO_DATE($field, '%Y-%m-%dT%T')";
218
            break;
219

    
220
          case DATE_DATETIME:
221
            break;
222
        }
223
        break;
224

    
225
      case 'pgsql':
226
        switch ($this->date_type) {
227
          case DATE_UNIX:
228
            $field = "TO_TIMESTAMP($field)";
229
            break;
230

    
231
          case DATE_ISO:
232
            $field = "TO_DATE($field, 'FMYYYY-FMMM-FMDDTFMHH24:FMMI:FMSS')";
233
            break;
234

    
235
          case DATE_DATETIME:
236
            break;
237
        }
238
        break;
239

    
240
      case 'sqlite':
241
        switch ($this->date_type) {
242
          case DATE_UNIX:
243
            $field = "datetime($field, 'unixepoch')";
244
            break;
245

    
246
          case DATE_ISO:
247
          case DATE_DATETIME:
248
            $field = "datetime($field)";
249
            break;
250
        }
251
        break;
252

    
253
      case 'sqlsrv':
254
        switch ($this->date_type) {
255
          case DATE_UNIX:
256
            $field = "DATEADD(s, $field, '19700101 00:00:00:000')";
257
            break;
258

    
259
          case DATE_ISO:
260
          case DATE_DATETIME:
261
            $field = "CAST($field as smalldatetime)";
262
            break;
263
        }
264
        break;
265

    
266
      break;
267
    }
268
    // Adjust the resulting value to the right timezone/offset.
269
    return $this->sql_tz($field, $offset, $comp_date);
270
  }
271

    
272
  /**
273
   * Adjust a field value by an offset in seconds.
274
   */
275
  function sql_offset($field, $offset = NULL) {
276
    if (!empty($offset)) {
277
      switch ($this->db_type) {
278
        case 'mysql':
279
          return "ADDTIME($field, SEC_TO_TIME($offset))";
280

    
281
        case 'pgsql':
282
          return "($field + INTERVAL '$offset SECONDS')";
283

    
284
        case 'sqlite':
285
          return "datetime($field, '$offset seconds')";
286

    
287
        case 'sqlsrv':
288
          return "DATEADD(second, $offset, $field)";
289
      }
290
    }
291
    return $field;
292
  }
293

    
294
  /**
295
   * Adjusts a field value by time interval.
296
   *
297
   * @param string $field
298
   *   The field to be adjusted.
299
   * @param string $direction
300
   *   Either ADD or SUB.
301
   * @param int $count
302
   *   The number of values to adjust.
303
   * @param string $granularity
304
   *   The granularity of the adjustment, should be singular, like SECOND,
305
   *   MINUTE, DAY, HOUR.
306
   */
307
  function sql_date_math($field, $direction, $count, $granularity) {
308
    $granularity = strtoupper($granularity);
309
    switch ($this->db_type) {
310
      case 'mysql':
311
        switch ($direction) {
312
          case 'ADD':
313
            return "DATE_ADD(CAST($field AS DATETIME), INTERVAL $count $granularity)";
314

    
315
          case 'SUB':
316
            return "DATE_SUB(CAST($field AS DATETIME), INTERVAL $count $granularity)";
317
        }
318

    
319
      case 'pgsql':
320
        $granularity .= 'S';
321
        switch ($direction) {
322
          case 'ADD':
323
            return "($field + INTERVAL '$count $granularity')";
324

    
325
          case 'SUB':
326
            return "($field - INTERVAL '$count $granularity')";
327
        }
328
      case 'sqlite':
329
        $granularity .= 'S';
330
        switch ($direction) {
331
          case 'ADD':
332
            return "datetime($field, '+$count $granularity')";
333

    
334
          case 'SUB':
335
            return "datetime($field, '-$count $granularity')";
336
        }
337
    }
338
    return $field;
339
  }
340

    
341
  /**
342
   * Select a date value from the database, adjusting for the timezone.
343
   *
344
   * Check whether database timezone conversion is supported in this system and
345
   * use it if possible, otherwise use an offset.
346
   *
347
   * @param string $field
348
   *   The field to be adjusted.
349
   * @param bool $offset
350
   *   Set a fixed offset or offset field to use for the date. If set, no
351
   *   timezone conversion will be done and the offset will be used.
352
   */
353
  function sql_tz($field, $offset = NULL, $comp_date = NULL) {
354
    // If the timezones are values they need to be quoted, but if they are
355
    // field names they do not.
356
    $db_zone   = !empty($this->db_timezone_field) ? $this->db_timezone_field : "'{$this->db_timezone}'";
357
    $localzone = !empty($this->local_timezone_field) ? $this->local_timezone_field : "'{$this->local_timezone}'";
358
    // If a fixed offset is required, use it.
359
    if ($offset !== NULL) {
360
      return $this->sql_offset($field, $offset);
361
    }
362
    // If the db and local timezones are the same, make no adjustment.
363
    elseif ($db_zone == $localzone) {
364
      return $this->sql_offset($field, 0);
365
    }
366
    // If the db has no timezone support, adjust by the offset, could be either
367
    // a field name or a value.
368
    elseif (!$this->db_tz_support() || empty($localzone)) {
369
      if (!empty($this->offset_field)) {
370
        return $this->sql_offset($field, $this->offset_field);
371
      }
372
      else {
373
        return $this->sql_offset($field, $this->get_offset($comp_date));
374
      }
375
    }
376
    // Otherwise make a database timezone adjustment to the field.
377
    else {
378
      switch ($this->db_type) {
379
        case 'mysql':
380
          return "CONVERT_TZ($field, $db_zone, $localzone)";
381

    
382
        case 'pgsql':
383
          // WITH TIME ZONE assumes the date is using the system
384
          // timezone, which should have been set to UTC.
385
          return "$field::timestamp with time zone AT TIME ZONE $localzone";
386
      }
387
    }
388
  }
389

    
390
  /**
391
   * Helper function to create cross-database SQL date formatting.
392
   *
393
   * @param string $format
394
   *   A format string for the result, like 'Y-m-d H:i:s' .
395
   * @param string $field
396
   *   The real table and field name, like 'tablename.fieldname' .
397
   *
398
   * @return string
399
   *   An appropriate SQL string for the db type and field type.
400
   */
401
  function sql_format($format, $field) {
402
    switch ($this->db_type) {
403
      case 'mysql':
404
        $replace = array(
405
          'Y' => '%Y',
406
          'y' => '%y',
407
          'M' => '%b',
408
          'm' => '%m',
409
          'n' => '%c',
410
          'F' => '%M',
411
          'D' => '%a',
412
          'd' => '%d',
413
          'l' => '%W',
414
          'j' => '%e',
415
          'W' => '%v',
416
          'H' => '%H',
417
          'h' => '%h',
418
          'i' => '%i',
419
          's' => '%s',
420
          'A' => '%p',
421
          '\WW' => 'W%U',
422
        );
423
        $format = strtr($format, $replace);
424
        return "DATE_FORMAT($field, '$format')";
425

    
426
      case 'pgsql':
427
        $replace = array(
428
          'Y' => 'YYYY',
429
          'y' => 'YY',
430
          'M' => 'Mon',
431
          'm' => 'MM',
432
          // No format for Numeric representation of a month, without leading
433
          // zeros.
434
          'n' => 'MM',
435
          'F' => 'Month',
436
          'D' => 'Dy',
437
          'd' => 'DD',
438
          'l' => 'Day',
439
          // No format for Day of the month without leading zeros.
440
          'j' => 'DD',
441
          'W' => 'WW',
442
          'H' => 'HH24',
443
          'h' => 'HH12',
444
          'i' => 'MI',
445
          's' => 'SS',
446
          'A' => 'AM',
447
          '\T' => '"T"',
448
          // '\W' => // @todo what should this be?
449
        );
450
        $format = strtr($format, $replace);
451
        return "TO_CHAR($field, '$format')";
452

    
453
      case 'sqlite':
454
        $replace = array(
455
          // 4 digit year number.
456
          'Y' => '%Y',
457
          // No format for 2 digit year number.
458
          'y' => '%Y',
459
          // No format for 3 letter month name.
460
          'M' => '%m',
461
          // Month number with leading zeros.
462
          'm' => '%m',
463
          // No format for month number without leading zeros.
464
          'n' => '%m',
465
          // No format for full month name.
466
          'F' => '%m',
467
          // No format for 3 letter day name.
468
          'D' => '%d',
469
          // Day of month number with leading zeros.
470
          'd' => '%d',
471
          // No format for full day name.
472
          'l' => '%d',
473
          // No format for day of month number without leading zeros.
474
          'j' => '%d',
475
          // ISO week number.
476
          'W' => '%W',
477
          // 24 hour hour with leading zeros.
478
          'H' => '%H',
479
          // No format for 12 hour hour with leading zeros.
480
          'h' => '%H',
481
          // Minutes with leading zeros.
482
          'i' => '%M',
483
          // Seconds with leading zeros.
484
          's' => '%S',
485
          // No format for AM/PM.
486
          'A' => '',
487
          // Week number.
488
          '\WW' => '',
489
        );
490
        $format = strtr($format, $replace);
491
        return "strftime('$format', $field)";
492

    
493
      case 'sqlsrv':
494
        $replace = array(
495
          // 4 digit year number.
496
          'Y' => "' + CAST(DATEPART(year, $field) AS nvarchar) + '",
497
          // 2 digit year number.
498
          'y' => "' + RIGHT(DATEPART(year, $field), 2) + '",
499
          // 3 letter month name.
500
          'M' => "' + LEFT(DATENAME(month, $field), 3) + '",
501
          // Month number with leading zeros.
502
          'm' => "' + RIGHT('0' + CAST(DATEPART(month, $field) AS nvarchar), 2) + '",
503
          // Month number without leading zeros.
504
          'n' => "' + CAST(DATEPART(month, $field) AS nvarchar) + '",
505
          // Full month name.
506
          'F' => "' + DATENAME(month, $field) + '",
507
          // 3 letter day name.
508
          'D' => "' + LEFT(DATENAME(day, $field), 3) + '",
509
          // Day of month number with leading zeros.
510
          'd' => "' + RIGHT('0' + CAST(DATEPART(day, $field) AS nvarchar), 2) + '",
511
          // Full day name.
512
          'l' => "' + DATENAME(day, $field) + '",
513
          // Day of month number without leading zeros.
514
          'j' => "' + CAST(DATEPART(day, $field) AS nvarchar) + '",
515
          // ISO week number.
516
          'W' => "' + CAST(DATEPART(iso_week, $field) AS nvarchar) + '",
517
          // 24 hour with leading zeros.
518
          'H' => "' + RIGHT('0' + CAST(DATEPART(hour, $field) AS nvarchar), 2) + '",
519
          // 12 hour with leading zeros.
520
          // Conversion to 'mon dd yyyy hh:miAM/PM' format (corresponds to style
521
          // 100 in MSSQL).
522
          // Hour position is fixed, so we use SUBSTRING to extract it.
523
          'h' => "' + RIGHT('0' + LTRIM(SUBSTRING(CONVERT(nvarchar, $field, 100), 13, 2)), 2) + '",
524
          // Minutes with leading zeros.
525
          'i' => "' + RIGHT('0' + CAST(DATEPART(minute, $field) AS nvarchar), 2) + '",
526
          // Seconds with leading zeros.
527
          's' => "' + RIGHT('0' + CAST(DATEPART(second, $field) AS nvarchar), 2) + '",
528
          // AM/PM.
529
          // Conversion to 'mon dd yyyy hh:miAM/PM' format (corresponds to style
530
          // 100 in MSSQL).
531
          'A' => "' + RIGHT(CONVERT(nvarchar, $field, 100), 2) + '",
532
          // Week number.
533
          '\WW' => "' + CAST(DATEPART(week, $field) AS nvarchar) + '",
534
          '\T' => 'T',
535
          // MS SQL uses single quote as escape symbol.
536
          '\'' => '\'\'',
537
        );
538
        $format = strtr($format, $replace);
539
        $format = "'$format'";
540
        return $format;
541
    }
542
  }
543

    
544
  /**
545
   * Helper function to create cross-database SQL date extraction.
546
   *
547
   * @param string $extract_type
548
   *   The type of value to extract from the date, like 'MONTH'.
549
   * @param string $field
550
   *   The real table and field name, like 'tablename.fieldname'.
551
   *
552
   * @return string
553
   *   An appropriate SQL string for the db type and field type.
554
   */
555
  function sql_extract($extract_type, $field) {
556
    // Note there is no space after FROM to avoid db_rewrite problems
557
    // see http://drupal.org/node/79904.
558
    switch (strtoupper($extract_type)) {
559
      case 'DATE':
560
        return $field;
561

    
562
      case 'YEAR':
563
        return "EXTRACT(YEAR FROM($field))";
564

    
565
      case 'MONTH':
566
        return "EXTRACT(MONTH FROM($field))";
567

    
568
      case 'DAY':
569
        return "EXTRACT(DAY FROM($field))";
570

    
571
      case 'HOUR':
572
        return "EXTRACT(HOUR FROM($field))";
573

    
574
      case 'MINUTE':
575
        return "EXTRACT(MINUTE FROM($field))";
576

    
577
      case 'SECOND':
578
        return "EXTRACT(SECOND FROM($field))";
579

    
580
      // ISO week number for date.
581
      case 'WEEK':
582
        switch ($this->db_type) {
583
          case 'mysql':
584
            // WEEK using arg 3 in MySQl should return the same value as
585
            // Postgres EXTRACT.
586
            return "WEEK($field, 3)";
587

    
588
          case 'pgsql':
589
            return "EXTRACT(WEEK FROM($field))";
590
        }
591

    
592
      case 'DOW':
593
        switch ($this->db_type) {
594
          case 'mysql':
595
            // MySQL returns 1 for Sunday through 7 for Saturday, PHP date
596
            // functions and Postgres use 0 for Sunday and 6 for Saturday.
597
            return "INTEGER(DAYOFWEEK($field) - 1)";
598

    
599
          case 'pgsql':
600
            return "EXTRACT(DOW FROM($field))";
601
        }
602
  
603
      case 'DOY':
604
        switch ($this->db_type) {
605
          case 'mysql':
606
            return "DAYOFYEAR($field)";
607

    
608
          case 'pgsql':
609
            return "EXTRACT(DOY FROM($field))";
610
        }
611
    }
612
  }
613

    
614
  /**
615
   * Creates a where clause to compare a complete date field to a date value.
616
   *
617
   * @param string $type
618
   *   The type of value we're comparing to, could be another field
619
   *   or a date value.
620
   * @param string $field
621
   *   The db table and field name, like "$table.$field".
622
   * @param string $operator
623
   *   The db comparison operator to use, like '='.
624
   * @param int $value
625
   *   The value to compare the extracted date part to, could be a field name or
626
   *   a date string or NOW().
627
   *
628
   * @return string
629
   *   SQL for the where clause for this operation.
630
   */
631
  function sql_where_date($type, $field, $operator, $value, $adjustment = NULL) {
632
    $type = strtoupper($type);
633
    if (strtoupper($value) == 'NOW') {
634
      $value = $this->sql_field('NOW', $adjustment);
635
    }
636
    elseif ($type == 'FIELD') {
637
      $value = $this->sql_field($value, $adjustment);
638
    }
639
    elseif ($type == 'DATE') {
640
      $date = new DateObject($value, date_default_timezone(), DATE_FORMAT_DATETIME);
641
      if (!empty($adjustment)) {
642
        date_modify($date, $adjustment . ' seconds');
643
      }
644
      // When comparing a field to a date we can avoid doing timezone
645
      // conversion by altering the comparison date to the db timezone. This
646
      // won't work if the timezone is a field instead of a value.
647
      if (empty($this->db_timezone_field) && empty($this->local_timezone_field) && $this->db_timezone_field != $this->local_timezone_field) {
648
        $date->setTimezone(timezone_open($this->db_timezone));
649
        $this->local_timezone = $this->db_timezone;
650
      }
651
      $value = "'" . $date->format(DATE_FORMAT_DATETIME, TRUE) . "'";
652
    }
653
    if ($this->local_timezone != $this->db_timezone) {
654
      $field = $this->sql_field($field);
655
    }
656
    else {
657
      $field = $this->sql_field($field, 0);
658
    }
659
    return "$field $operator $value";
660
  }
661

    
662
  /**
663
   * Creates a where clause comparing an extracted date part to an integer.
664
   *
665
   * @param string $part
666
   *   The part to extract, YEAR, MONTH, DAY, etc.
667
   * @param string $field
668
   *   The db table and field name, like "$table.$field".
669
   * @param string $operator
670
   *   The db comparison operator to use, like '=' .
671
   * @param int $value
672
   *   The integer value to compare the extracted date part to.
673
   *
674
   * @return string
675
   *   SQL for the where clause for this operation.
676
   */
677
  function sql_where_extract($part, $field, $operator, $value, $adjustment = NULL) {
678
    if (empty($adjustment) && $this->local_timezone != $this->db_timezone) {
679
      $field = $this->sql_field($field);
680
    }
681
    else {
682
      $field = $this->sql_field($field, $adjustment);
683
    }
684
    return $this->sql_extract($part, $field) . " $operator $value";
685
  }
686

    
687
  /**
688
   * Create a where clause to compare a formated field to a formated value.
689
   *
690
   * @param string $format
691
   *   The format to use on the date and the value when comparing them.
692
   * @param string $field
693
   *   The db table and field name, like "$table.$field".
694
   * @param string $operator
695
   *   The db comparison operator to use, like '=' .
696
   * @param string $value
697
   *   The value to compare the extracted date part to, could be a
698
   *   field name or a date string or NOW().
699
   *
700
   * @return string
701
   *   SQL for the where clause for this operation.
702
   */
703
  function sql_where_format($format, $field, $operator, $value, $adjustment = NULL) {
704
    if (empty($adjustment) && $this->local_timezone != $this->db_timezone) {
705
      $field = $this->sql_field($field);
706
    }
707
    else {
708
      $field = $this->sql_field($field, $adjustment);
709
    }
710
    return $this->sql_format($format, $field) . " $operator '$value'";
711
  }
712

    
713
  /**
714
   * An array of all date parts,
715
   *
716
   * @param bool $limit
717
   *   Limit to an array of allowed parts.
718
   */
719
  function date_parts($limit = NULL) {
720
    $parts = array(
721
      'year' => t('Year', array(), array('context' => 'datetime')),
722
      'month' => t('Month', array(), array('context' => 'datetime')),
723
      'day' => t('Day', array(), array('context' => 'datetime')),
724
      'hour' => t('Hour', array(), array('context' => 'datetime')),
725
      'minute' => t('Minute', array(), array('context' => 'datetime')),
726
      'second' => t('Second', array(), array('context' => 'datetime')),
727
    );
728
    if (!empty($limit)) {
729
      $last = FALSE;
730
      foreach ($parts as $key => $part) {
731
        if ($last) {
732
          unset($parts[$key]);
733
        }
734
        if ($key == $limit) {
735
          $last = TRUE;
736
        }
737
      }
738
    }
739
    return $parts;
740
  }
741

    
742
  /**
743
   * Part information.
744
   *
745
   * @param string $op
746
   *   'min', 'max', 'format', 'sep', 'empty_now', 'empty_min', 'empty_max' .
747
   *   Returns all info if empty.
748
   * @param string $part
749
   *   'year', 'month', 'day', 'hour', 'minute', or 'second. Returns info for
750
   *   all parts if empty.
751
   */
752
  function part_info($op = NULL, $part = NULL) {
753
    $info = array();
754
    $info['min'] = array(
755
      'year' => 100,
756
      'month' => 1,
757
      'day' => 1,
758
      'hour' => 0,
759
      'minute' => 0,
760
      'second' => 0,
761
    );
762
    $info['max'] = array(
763
      'year' => 4000,
764
      'month' => 12,
765
      'day' => 31,
766
      'hour' => 23,
767
      'minute' => 59,
768
      'second' => 59,
769
    );
770
    $info['format'] = array(
771
      'year' => 'Y',
772
      'month' => 'm',
773
      'day' => 'd',
774
      'hour' => 'H',
775
      'minute' => 'i',
776
      'second' => 's',
777
    );
778
    $info['sep'] = array(
779
      'year' => '',
780
      'month' => '-',
781
      'day' => '-',
782
      'hour' => ' ',
783
      'minute' => ':',
784
      'second' => ':',
785
    );
786
    $info['empty_now'] = array(
787
      'year' => date('Y'),
788
      'month' => date('m'),
789
      'day' => min('28', date('d')),
790
      'hour' => date('H'),
791
      'minute' => date('i'),
792
      'second' => date('s'),
793
    );
794
    $info['empty_min'] = array(
795
      'year' => '1000',
796
      'month' => '01',
797
      'day' => '01',
798
      'hour' => '00',
799
      'minute' => '00',
800
      'second' => '00',
801
    );
802
    $info['empty_max'] = array(
803
      'year' => '9999',
804
      'month' => '12',
805
      'day' => '31',
806
      'hour' => '23',
807
      'minute' => '59',
808
      'second' => '59',
809
    );
810
    if (!empty($op)) {
811
      if (!empty($part)) {
812
        return $info[$op][$part];
813
      }
814
      else {
815
        return $info[$op];
816
      }
817
    }
818
    return $info;
819
  }
820

    
821
  /**
822
   * Create a complete date/time value out of an incomplete array of values.
823
   *
824
   * For example, array('year' => 2008, 'month' => 05) will fill in the day,
825
   * hour, minute and second with the earliest possible values if type = 'min',
826
   * the latest possible values if type = 'max', and the current values if type
827
   * = 'now'.
828
   */
829
  function complete_date($selected, $type = 'now') {
830
    if (empty($selected)) {
831
      return '';
832
    }
833
    // Special case for weeks.
834
    if (array_key_exists('week', $selected)) {
835
      $dates = date_week_range($selected['week'], $selected['year']);
836
      switch ($type) {
837
        case 'empty_now':
838
        case 'empty_min':
839
        case 'min':
840
          return date_format($dates[0], 'Y-m-d H:i:s');
841

    
842
        case 'empty_max':
843
        case 'max':
844
          return date_format($dates[1], 'Y-m-d H:i:s');
845

    
846
        default:
847
          return;
848
      }
849
    }
850

    
851
    $compare = array_merge($this->part_info('empty_' . $type), $selected);
852
    // If this is a max date, make sure the last day of
853
    // the month is the right one for this date.
854
    if ($type == 'max') {
855
      $compare['day'] = date_days_in_month($compare['year'], $compare['month']);
856
    }
857
    $value = '';
858
    $separators = $this->part_info('sep');
859
    foreach ($this->date_parts() as $key => $name) {
860
      $value .= $separators[$key] . (!empty($selected[$key]) ? $selected[$key] : $compare[$key]);
861
    }
862
    return $value;
863
  }
864

    
865
  /**
866
   * Converts a format string into help text, i.e. 'Y-m-d' becomes 'YYYY-MM-DD'.
867
   *
868
   * @param string $format
869
   *   A date format string.
870
   *
871
   * @return string
872
   *   The conveted help text.
873
   */
874
  function format_help($format) {
875
    $replace = array(
876
      'Y' => 'YYYY',
877
      'm' => 'MM',
878
      'd' => 'DD',
879
      'H' => 'HH',
880
      'i' => 'MM',
881
      's' => 'SS',
882
      '\T' => 'T',
883
    );
884
    return strtr($format, $replace);
885
  }
886

    
887
  /**
888
   * A function to test the validity of various date parts.
889
   */
890
  function part_is_valid($value, $type) {
891
    if (!preg_match('/^[0-9]*$/', $value)) {
892
      return FALSE;
893
    }
894
    $value = intval($value);
895
    if ($value <= 0) {
896
      return FALSE;
897
    }
898
    switch ($type) {
899
      case 'year':
900
        if ($value < DATE_MIN_YEAR) {
901
          return FALSE;
902
        }
903
        break;
904

    
905
      case 'month':
906
        if ($value < 0 || $value > 12) {
907
          return FALSE;
908
        }
909
        break;
910

    
911
      case 'day':
912
        if ($value < 0 || $value > 31) {
913
          return FALSE;
914
        }
915
        break;
916

    
917
      case 'week':
918
        if ($value < 0 || $value > 53) {
919
          return FALSE;
920
        }
921
        break;
922
    }
923
    return TRUE;
924
  }
925

    
926
  /**
927
   * @todo.
928
   */
929
  function views_formats($granularity, $type = 'sql') {
930
    if (empty($granularity)) {
931
      return DATE_FORMAT_ISO;
932
    }
933
    $formats = array('display', 'sql');
934
    // Start with the site long date format and add seconds to it.
935
    $short = str_replace(':i', ':i:s', variable_get('date_format_short', 'l, F j, Y - H:i'));
936
    switch ($granularity) {
937
      case 'year':
938
        $formats['display'] = 'Y';
939
        $formats['sql'] = 'Y';
940
        break;
941

    
942
      case 'month':
943
        $formats['display'] = date_limit_format($short, array('year', 'month'));
944
        $formats['sql'] = 'Y-m';
945
        break;
946

    
947
      case 'day':
948
        $args = array('year', 'month', 'day');
949
        $formats['display'] = date_limit_format($short, $args);
950
        $formats['sql'] = 'Y-m-d';
951
        break;
952

    
953
      case 'hour':
954
        $args = array('year', 'month', 'day', 'hour');
955
        $formats['display'] = date_limit_format($short, $args);
956
        $formats['sql'] = 'Y-m-d\TH';
957
        break;
958

    
959
      case 'minute':
960
        $args = array('year', 'month', 'day', 'hour', 'minute');
961
        $formats['display'] = date_limit_format($short, $args);
962
        $formats['sql'] = 'Y-m-d\TH:i';
963
        break;
964

    
965
      case 'second':
966
        $args = array('year', 'month', 'day', 'hour', 'minute', 'second');
967
        $formats['display'] = date_limit_format($short, $args);
968
        $formats['sql'] = 'Y-m-d\TH:i:s';
969
        break;
970

    
971
      case 'week':
972
        $formats['display'] = 'F j Y (W)';
973
        $formats['sql'] = 'Y-\WW';
974
        break;
975
    }
976
    return $formats[$type];
977
  }
978

    
979
  /**
980
   * @todo.
981
   */
982
  function granularity_form($granularity) {
983
    $form = array(
984
      '#title' => t('Granularity'),
985
      '#type' => 'radios',
986
      '#default_value' => $granularity,
987
      '#options' => $this->date_parts(),
988
    );
989
    return $form;
990
  }
991

    
992
  /**
993
   * Parse date parts from an ISO date argument.
994
   *
995
   * Based on ISO 8601 date duration and time interval standards.
996
   *
997
   * Parses a value like 2006-01-01--2006-01-15, or 2006-W24, or @P1W.
998
   * Separate start and end dates or date and period with a double hyphen (--).
999
   *
1000
   * The 'end' portion of the argument can be eliminated if it is the same as
1001
   * the 'start' portion. Use @ instead of a date to substitute in the current
1002
   * date and time.
1003
   *
1004
   * Use periods (P1H, P1D, P1W, P1M, P1Y) to get next hour/day/week/month/year
1005
   * from now. Use date before P sign to get next hour/day/week/month/year from
1006
   * that date. Use period then date to get a period that ends on the date.
1007
   *
1008
   * @see http://en.wikipedia.org/wiki/ISO_8601#Week_dates
1009
   * @see http://en.wikipedia.org/wiki/ISO_8601#Duration
1010
   */
1011
  function arg_parts($argument) {
1012
    $values = array();
1013
    // Keep mal-formed arguments from creating errors.
1014
    if (empty($argument) || is_array($argument)) {
1015
      return array('date' => array(), 'period' => array());
1016
    }
1017
    $fromto = explode('--', $argument);
1018
    foreach ($fromto as $arg) {
1019
      $parts = array();
1020
      if ($arg == '@') {
1021
        $date = date_now();
1022
        $parts['date'] = $date->toArray();
1023
      }
1024
      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)) {
1025
        $date = array();
1026
        if (!empty($matches[1])) {
1027
          $date['year'] = $matches[1];
1028
        }
1029
        if (!empty($matches[3])) {
1030
          if (empty($matches[2])) {
1031
            $date['month'] = $matches[3];
1032
          }
1033
          else {
1034
            $date['week'] = $matches[3];
1035
          }
1036
        }
1037
        if (!empty($matches[4])) {
1038
          $date['day'] = $matches[4];
1039
        }
1040
        if (!empty($matches[5])) {
1041
          $date['hour'] = $matches[5];
1042
        }
1043
        if (!empty($matches[6])) {
1044
          $date['minute'] = $matches[6];
1045
        }
1046
        if (!empty($matches[7])) {
1047
          $date['second'] = $matches[7];
1048
        }
1049
        $parts['date'] = $date;
1050
      }
1051
      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)) {
1052
        $period = array();
1053
        if (!empty($matches[1])) {
1054
          $period['year'] = str_replace('Y', '', $matches[1]);
1055
        }
1056
        if (!empty($matches[2])) {
1057
          $period['month'] = str_replace('M', '', $matches[2]);
1058
        }
1059
        if (!empty($matches[3])) {
1060
          $period['week'] = str_replace('W', '', $matches[3]);
1061
        }
1062
        if (!empty($matches[4])) {
1063
          $period['day'] = str_replace('D', '', $matches[4]);
1064
        }
1065
        if (!empty($matches[6])) {
1066
          $period['hour'] = str_replace('H', '', $matches[6]);
1067
        }
1068
        if (!empty($matches[7])) {
1069
          $period['minute'] = str_replace('M', '', $matches[7]);
1070
        }
1071
        if (!empty($matches[8])) {
1072
          $period['second'] = str_replace('S', '', $matches[8]);
1073
        }
1074
        $parts['period'] = $period;
1075
      }
1076
      $values[] = $parts;
1077
    }
1078
    return $values;
1079
  }
1080

    
1081
  /**
1082
   * Convert strings like '+1 day' to the ISO equivalent, like 'P1D' .
1083
   */
1084
  function arg_replace($arg) {
1085
    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)) {
1086
      return str_replace('now', '@', $arg);
1087
    }
1088
    $direction = $results[1];
1089
    $count = $results[2];
1090
    $item = $results[3];
1091
    $replace = array(
1092
      'now' => '@',
1093
      '+' => 'P',
1094
      '-' => 'P-',
1095
      'years' => 'Y',
1096
      'year' => 'Y',
1097
      'months' => 'M',
1098
      'month' => 'M',
1099
      'weeks' => 'W',
1100
      'week' => 'W',
1101
      'days' => 'D',
1102
      'day' => 'D',
1103
      'hours' => 'H',
1104
      'hour' => 'H',
1105
      'minutes' => 'M',
1106
      'minute' => 'M',
1107
      'seconds' => 'S',
1108
      'second' => 'S',
1109
      '  ' => '',
1110
      ' ' => '',
1111
    );
1112
    $args = array('hours', 'hour', 'minutes', 'minute', 'seconds', 'second');
1113
    if (in_array($item, $args)) {
1114
      $prefix = 'T';
1115
    }
1116
    else {
1117
      $prefix = '';
1118
    }
1119
    $return = $prefix;
1120
    $return .= strtr($direction, $replace);
1121
    $return .= $count;
1122
    $return .= strtr($item, $replace);
1123

    
1124
    return $return;
1125
  }
1126

    
1127
  /**
1128
   * Granularity arguments handler.
1129
   *
1130
   * Use the parsed values from the ISO argument to determine the granularity
1131
   * of this period.
1132
   */
1133
  function arg_granularity($arg) {
1134
    $granularity = '';
1135
    $parts = $this->arg_parts($arg);
1136
    $date = !empty($parts[0]['date']) ? $parts[0]['date'] : (!empty($parts[1]['date']) ? $parts[1]['date'] : array());
1137
    foreach ($date as $key => $part) {
1138
      $granularity = $key;
1139
    }
1140
    return $granularity;
1141
  }
1142

    
1143
  /**
1144
   * Determine the min and max date for this period.
1145
   *
1146
   * Uses the parsed values from the ISO argument.
1147
   */
1148
  function arg_range($arg) {
1149
    // Parse the argument to get its parts.
1150
    $parts = $this->arg_parts($arg);
1151

    
1152
    // Build a range from a period-only argument (assumes the min date is now.)
1153
    if (empty($parts[0]['date']) && !empty($parts[0]['period']) && (empty($parts[1]))) {
1154
      $min_date = date_now();
1155
      $max_date = clone $min_date;
1156
      foreach ($parts[0]['period'] as $part => $value) {
1157
        date_modify($max_date, "+$value $part");
1158
      }
1159
      date_modify($max_date, '-1 second');
1160
      return array($min_date, $max_date);
1161
    }
1162
    // Build a range from a period to period argument.
1163
    if (empty($parts[0]['date']) && !empty($parts[0]['period']) && !empty($parts[1]['period'])) {
1164
      $min_date = date_now();
1165
      $max_date = clone $min_date;
1166
      foreach ($parts[0]['period'] as $part => $value) {
1167
        date_modify($min_date, "+$value $part");
1168
      }
1169
      date_modify($min_date, '-1 second');
1170
      foreach ($parts[1]['period'] as $part => $value) {
1171
        date_modify($max_date, "+$value $part");
1172
      }
1173
      date_modify($max_date, '-1 second');
1174
      return array($min_date, $max_date);
1175
    }
1176
    if (!empty($parts[0]['date'])) {
1177
      $value = $this->complete_date($parts[0]['date'], 'min');
1178
      $min_date = new DateObject($value, date_default_timezone(), DATE_FORMAT_DATETIME);
1179
      // Build a range from a single date-only argument.
1180
      if (empty($parts[1]) || (empty($parts[1]['date']) && empty($parts[1]['period']))) {
1181
        $value = $this->complete_date($parts[0]['date'], 'max');
1182
        $max_date = new DateObject($value, date_default_timezone(), DATE_FORMAT_DATETIME);
1183
        return array($min_date, $max_date);
1184
      }
1185
      // Build a range from start date + period.
1186
      elseif (!empty($parts[1]['period'])) {
1187
        foreach ($parts[1]['period'] as $part => $value) {
1188
          $max_date = clone $min_date;
1189
          date_modify($max_date, "+$value $part");
1190
        }
1191
        date_modify($max_date, '-1 second');
1192
        return array($min_date, $max_date);
1193
      }
1194
    }
1195
    // Build a range from start date and end date.
1196
    if (!empty($parts[1]['date'])) {
1197
      $value = $this->complete_date($parts[1]['date'], 'max');
1198
      $max_date = new DateObject($value, date_default_timezone(), DATE_FORMAT_DATETIME);
1199
      if (isset($min_date)) {
1200
        return array($min_date, $max_date);
1201
      }
1202
    }
1203
    // Build a range from period + end date.
1204
    if (!empty($parts[0]['period'])) {
1205
      $min_date = date_now();
1206
      foreach ($parts[0]['period'] as $part => $value) {
1207
        date_modify($min_date, "$value $part");
1208
      }
1209
      return array($min_date, $max_date);
1210
    }
1211
    // Intercept invalid info and fall back to the current date.
1212
    $now = date_now();
1213
    return array($now, $now);
1214
  }
1215
}