protected function ReportQueryBuilder::getDateFormat in Commerce Reporting 8

Creates cross-database date formatting.

@note This has only been tested on MySQL + SQLite in this module.

Parameters

string $field: An appropriate query expression pointing to the date field.

string $format: A format string for the result, like 'Y-m-d H:i:s'.

bool $string_date: For certain databases, date format functions vary depending on string or numeric storage.

Return value

string A string representing the field formatted as a date in the format specified by $format.

See also

\Drupal\views\Plugin\views\query\Sql::getDateFormat

1 call to ReportQueryBuilder::getDateFormat()

ReportQueryBuilder::alterQuery in src/ReportQueryBuilder.php

Alters the report query.

File

src/ReportQueryBuilder.php, line 110

Class

ReportQueryBuilder

Provides an aggregate query builder for order reports.

Namespace

Drupal\commerce_reports

Code

protected function getDateFormat($field, $format, $string_date = FALSE) {
  $db_type = Database::getConnection()
    ->databaseType();
  switch ($db_type) {
    case 'mysql':
      $replace = [
        'Y' => '%Y',
        'y' => '%y',
        'M' => '%b',
        'm' => '%m',
        'n' => '%c',
        'F' => '%M',
        'D' => '%a',
        'd' => '%d',
        'l' => '%W',
        'j' => '%e',
        'W' => '%v',
        'H' => '%H',
        'h' => '%h',
        'i' => '%i',
        's' => '%s',
        'A' => '%p',
      ];
      $format = strtr($format, $replace);
      return "DATE_FORMAT({$field}, '{$format}')";
    case 'pgsql':
      $replace = [
        'Y' => 'YYYY',
        'y' => 'YY',
        'M' => 'Mon',
        'm' => 'MM',
        // No format for Numeric representation of a month, without leading
        // zeros.
        'n' => 'MM',
        'F' => 'Month',
        'D' => 'Dy',
        'd' => 'DD',
        'l' => 'Day',
        // No format for Day of the month without leading zeros.
        'j' => 'DD',
        'W' => 'IW',
        'H' => 'HH24',
        'h' => 'HH12',
        'i' => 'MI',
        's' => 'SS',
        'A' => 'AM',
      ];
      $format = strtr($format, $replace);
      if (!$string_date) {
        return "TO_CHAR({$field}, '{$format}')";
      }

      // In order to allow for partials (eg, only the year), transform to a
      // date, back to a string again.
      return "TO_CHAR(TO_TIMESTAMP({$field}, 'YYYY-MM-DD HH24:MI:SS'), '{$format}')";
    case 'sqlite':
      $replace = [
        'Y' => '%Y',
        // No format for 2 digit year number.
        'y' => '%Y',
        // No format for 3 letter month name.
        'M' => '%m',
        'm' => '%m',
        // No format for month number without leading zeros.
        'n' => '%m',
        // No format for full month name.
        'F' => '%m',
        // No format for 3 letter day name.
        'D' => '%d',
        'd' => '%d',
        // No format for full day name.
        'l' => '%d',
        // No format for day of month number without leading zeros.
        'j' => '%d',
        'W' => '%W',
        'H' => '%H',
        // No format for 12 hour hour with leading zeros.
        'h' => '%H',
        'i' => '%M',
        's' => '%S',
        // No format for AM/PM.
        'A' => '',
      ];
      $format = strtr($format, $replace);

      // Don't use the 'unixepoch' flag for string date comparisons.
      $unixepoch = $string_date ? '' : ", 'unixepoch'";

      // SQLite does not have a ISO week substitution string, so it needs
      // special handling.
      // @see http://wikipedia.org/wiki/ISO_week_date#Calculation
      // @see http://stackoverflow.com/a/15511864/1499564
      if ($format === '%W') {
        $expression = "((strftime('%j', date(strftime('%Y-%m-%d', {$field}" . $unixepoch . "), '-3 days', 'weekday 4')) - 1) / 7 + 1)";
      }
      else {
        $expression = "strftime('{$format}', {$field}" . $unixepoch . ")";
      }

      // The expression yields a string, but the comparison value is an
      // integer in case the comparison value is a float, integer, or numeric.
      // All of the above SQLite format tokens only produce integers. However,
      // the given $format may contain 'Y-m-d', which results in a string.
      // @see \Drupal\Core\Database\Driver\sqlite\Connection::expandArguments()
      // @see http://www.sqlite.org/lang_datefunc.html
      // @see http://www.sqlite.org/lang_expr.html#castexpr
      if (preg_match('/^(?:%\\w)+$/', $format)) {
        $expression = "CAST({$expression} AS NUMERIC)";
      }
      return $expression;
  }
}