DateTimeKey.py 7.75 KB
Newer Older
Ivan Tyagov's avatar
Ivan Tyagov committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
##############################################################################
#
# Copyright (c) 2005 Nexedi SARL and Contributors. All Rights Reserved.
#                     Ivan Tyagov <ivan@nexedi.com>
#
# WARNING: This program as such is intended to be used by professional
# programmers who take the whole responsability of assessing all potential
# consequences resulting from its eventual inadequacies and bugs
# End users who are looking for a ready-to-use solution with commercial
# garantees and support are strongly adviced to contract a Free Software
# Service Company
#
# This program is Free Software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
#
##############################################################################

from Products.ZSQLCatalog.Query.SimpleQuery import SimpleQuery as Query
from Products.ZSQLCatalog.Query.ComplexQuery import ComplexQuery
from Products.ZSQLCatalog.SQLCatalog import getSearchKeyInstance
from DateTime import DateTime
33
from SearchKey import SearchKey
Ivan Tyagov's avatar
Ivan Tyagov committed
34 35
from pprint import pprint

Jean-Paul Smets's avatar
Jean-Paul Smets committed
36

37
class DateTimeKey(SearchKey):
Ivan Tyagov's avatar
Ivan Tyagov committed
38 39 40 41
  """ DateTimeKey key is an ERP5 portal_catalog search key which is used to render
      SQL expression that will try to match values in DateTime MySQL columns.
      It supports following special operator ['=', '%', '>' , '>=', '<', '<='] in
      addition to main logical operators like ['OR', 'or', 'AND', 'and'].
42

Jean-Paul Smets's avatar
Jean-Paul Smets committed
43
      Note: because all ERP5 datetime values are indexed in MySQL in 'UTC'
Ivan Tyagov's avatar
Ivan Tyagov committed
44 45
      the respective passed date will be first converted to 'UTC' before inserted into
      respective SQL query!
46

Ivan Tyagov's avatar
Ivan Tyagov committed
47
      Examples (GMT+02, Bulgaria/Sofia for 'delivery.start_date'):
48

Ivan Tyagov's avatar
Ivan Tyagov committed
49
        * '15/01/2008' --> "delivery.start_date = '2008-01-14 22:00'"
50

Jean-Paul Smets's avatar
Jean-Paul Smets committed
51
        * '>=15/01/2008' --> "delivery.start_date >= '2008-01-14 22:00'"     
52

Jean-Paul Smets's avatar
Jean-Paul Smets committed
53
        * '>=15/01/2008 or <=20/01/2008'
Ivan Tyagov's avatar
Ivan Tyagov committed
54
          --> "delivery.start_date >= '2008-01-14 22:00' or delivery.start_date<='2008-01-19 22:00'"
55

Ivan Tyagov's avatar
Ivan Tyagov committed
56 57
        * '>=15/01/2008 10:00 GMT+02 OR <=20/01/2008 05:12 Universal'
          -->
Jean-Paul Smets's avatar
Jean-Paul Smets committed
58 59
          "delivery.start_date >= '2008-01-15 08:00 Universal'
            OR
Ivan Tyagov's avatar
Ivan Tyagov committed
60 61 62
          delivery.start_date <= '2008-01-20 05:12 Universal'
          "
  """
63

Ivan Tyagov's avatar
Ivan Tyagov committed
64 65 66
  tokens =  ('DATE', 'OR', 'AND', 'NOT', 'EQUAL',
             'GREATERTHAN', 'GREATERTHANEQUAL',
             'LESSTHAN', 'LESSTHANEQUAL')
67

Jean-Paul Smets's avatar
Jean-Paul Smets committed
68
  sub_operators =  ('GREATERTHAN', 'GREATERTHANEQUAL',
Ivan Tyagov's avatar
Ivan Tyagov committed
69
                    'LESSTHAN', 'LESSTHANEQUAL', 'NOT', 'EQUAL',)
70

Ivan Tyagov's avatar
Ivan Tyagov committed
71
  def t_OR(self, t):
72
    r'\s+(OR|or)\s+'
Ivan Tyagov's avatar
Ivan Tyagov committed
73 74 75 76 77
    # operator has leading and trailing ONLY one white space character
    t.value = 'OR'
    return t

  def t_AND(self, t):
78
    r'\s+(AND|and)\s+'
Ivan Tyagov's avatar
Ivan Tyagov committed
79 80
    # operator has leading and trailing ONLY one white space character
    t.value = 'AND'
81 82
    return t

Ivan Tyagov's avatar
Ivan Tyagov committed
83
  def t_NOT(self, t):
84
    r'(\s+(NOT|not)\s+|!=)'
Ivan Tyagov's avatar
Ivan Tyagov committed
85 86
    # operator has leading and trailing ONLY one white space character
    t.value = t.value.upper().strip()
87
    return t
Ivan Tyagov's avatar
Ivan Tyagov committed
88

Jean-Paul Smets's avatar
Jean-Paul Smets committed
89 90
  t_GREATERTHANEQUAL = r'>=' 
  t_LESSTHANEQUAL = r'<=' 
Ivan Tyagov's avatar
Ivan Tyagov committed
91 92
  t_GREATERTHAN = r'>'
  t_LESSTHAN = r'<'
93
  t_EQUAL = r'='
Ivan Tyagov's avatar
Ivan Tyagov committed
94
  t_DATE = r'\d{1,4}[(/|\.|\-) /.]\d{1,4}[(/|\.|\-) /.]\d{1,4}((\s.)*\d{0,2}:\d{0,2}(:\d{0,2})?)?(\sUniversal|\sGMT\+\d\d)?|\d\d\d\d%?'
95

Ivan Tyagov's avatar
Ivan Tyagov committed
96
  def quoteSQLString(self, value, format):
Jean-Paul Smets's avatar
Jean-Paul Smets committed
97
    """ Return a quoted string of the value.
Ivan Tyagov's avatar
Ivan Tyagov committed
98 99 100 101 102 103
        Make sure to convert it to UTC first."""
    if getattr(value, 'ISO', None) is not None:
      value = "'%s'" % value.toZone('UTC').ISO()
    else:
      value = "'%s'" %DateTime(value).toZone('UTC').ISO()
    return value
104

Ivan Tyagov's avatar
Ivan Tyagov committed
105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124
  def buildQueryForTokenList(self, tokens, key, value, format):
    """ Build a ComplexQuery for a token list """
    query_list = []
    for group_tokens in self.groupByLogicalOperator(tokens, 'AND'):
      token_values = [x.value for x in group_tokens]
      sub_operator, sub_tokens = self.getOperatorForTokenList(group_tokens)
      date_value = sub_tokens[0].value
      days_offset = 0
      # some format require special handling
      if format != '%Y':
        # full format (Year/Month/Day)
        if sub_operator in ('=',):
          # 2007/01/01 00:00 <= date < 2007/01/02
          days_offset = 1
      elif format == '%Y':
        # incomplete format only Year because DateTime can not handle
        # extend format and value by assumption that start of year is ment
        # add days ofset accordingly
        format = '%%%s/%%m/%%d' %format
        date_value = '%s/01/01' %date_value
Jean-Paul Smets's avatar
Jean-Paul Smets committed
125
        days_offset_map = {'=' : 366, '>' : 366,
Ivan Tyagov's avatar
Ivan Tyagov committed
126 127
                           '>=' : 366, '<': -366, '<=':-366}
        days_offset = days_offset_map[sub_operator]
128

Ivan Tyagov's avatar
Ivan Tyagov committed
129 130 131 132 133 134 135 136 137 138 139
      # convert to UTC in given format
      is_valid_date = 1
      try:
        if format != '%m/%d/%Y':
          # treat ambigious dates as "days before month before year"
          date_value = DateTime(date_value, datefmt="international").toZone('UTC')
        else:
          # US style "month before day before year"
          date_value = DateTime(date_value).toZone('UTC')
      except:
        is_valid_date = 0
140 141

      query_kw = None
Ivan Tyagov's avatar
Ivan Tyagov committed
142 143 144 145
      if is_valid_date:
        if sub_operator == '=':
          # transform to range 'key >= date  AND  date < key'
          query_kw = {key: (date_value, date_value + days_offset,),
Jean-Paul Smets's avatar
Jean-Paul Smets committed
146
                      'range': 'minmax'}
Ivan Tyagov's avatar
Ivan Tyagov committed
147 148
        else:
          query_kw = {key: date_value + days_offset,
Jean-Paul Smets's avatar
Jean-Paul Smets committed
149
                      'range': sub_operator}  
150
        query_kw['type'] = 'date'
Ivan Tyagov's avatar
Ivan Tyagov committed
151 152 153 154 155 156 157 158 159 160 161
      else:
        # not a valid date, try to get an year range
        is_year = 1
        date_value = date_value.replace('%', '')
        try: date_value = int(date_value)
        except: is_year = 0
        if is_year:
          date_value = '%s/01/01' % date_value
          date_value = DateTime(date_value).toZone('UTC')
          query_kw = {key: (date_value, date_value + 366,),
                      'type': 'date',
Jean-Paul Smets's avatar
Jean-Paul Smets committed
162
                      'range': 'minmax'}
163

Ivan Tyagov's avatar
Ivan Tyagov committed
164 165
      # append only if it was possible to generate query
      if query_kw is not None:
166 167
        query_list.append(Query(**query_kw))

Ivan Tyagov's avatar
Ivan Tyagov committed
168 169
    # join query list in one really big ComplexQuery
    if len(query_list):
Jean-Paul Smets's avatar
Jean-Paul Smets committed
170
      complex_query = ComplexQuery(*query_list,
Ivan Tyagov's avatar
Ivan Tyagov committed
171
                                   **{'operator': 'AND'})
172 173
      return complex_query

Ivan Tyagov's avatar
Ivan Tyagov committed
174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190
##  def buildSQLExpressionFromSearchString(self, key, value, format, mode, range_value, stat__):
##    """ Tokenize/analyze passed string value and generate SQL query expressions. """
##    where_expression = ''
##    key = self.quoteSQLKey(key, format)
##    tokens = self.tokenize(value)
##    operators_mapping_list = self.groupByOperator(tokens)
##    # new one
##    for item in operators_mapping_list:
##      row_tokens_values = []
##      tokens = item['tokens']
##      operator = item['operator']
##      operator_value = None
##      if operator is not None:
##        # operator is standalone expression
##        operator_value = operator.value
##        where_expressions.append('%s' %operator_value)
##      if len(tokens):
Jean-Paul Smets's avatar
Jean-Paul Smets committed
191
##        # no it's not a stand alone expression,
Ivan Tyagov's avatar
Ivan Tyagov committed
192 193 194 195 196
##        # determine it from list of tokens
##        operator_value, sub_tokens = self.getOperatorForTokenList(tokens)
##        row_tokens_values = [self.quoteSQLString(x.value, format) for x in sub_tokens]
##        where_expression = "%s %s %s" %(key, operator_value, ' '.join(row_tokens_values))
##    return where_expression, []