source: tailbone/tailbone/grids/filters.py @ 944e896

Last change on this file since 944e896 was 944e896, checked in by Lance Edgar <lance@…>, 2 years ago

Consider any integer greater than PG allows, to be invalid grid filter value

this feels pretty hacky...would be nice to come up with a better way

  • Property mode set to 100644
File size: 25.6 KB
Line 
1# -*- coding: utf-8; -*-
2################################################################################
3#
4#  Rattail -- Retail Software Framework
5#  Copyright © 2010-2018 Lance Edgar
6#
7#  This file is part of Rattail.
8#
9#  Rattail is free software: you can redistribute it and/or modify it under the
10#  terms of the GNU General Public License as published by the Free Software
11#  Foundation, either version 3 of the License, or (at your option) any later
12#  version.
13#
14#  Rattail is distributed in the hope that it will be useful, but WITHOUT ANY
15#  WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16#  FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
17#  details.
18#
19#  You should have received a copy of the GNU General Public License along with
20#  Rattail.  If not, see <http://www.gnu.org/licenses/>.
21#
22################################################################################
23"""
24Grid Filters
25"""
26
27from __future__ import unicode_literals, absolute_import
28
29import datetime
30import logging
31
32import six
33import sqlalchemy as sa
34
35from rattail.gpc import GPC
36from rattail.util import OrderedDict
37from rattail.core import UNSPECIFIED
38from rattail.time import localtime, make_utc
39from rattail.util import prettify
40
41import colander
42from webhelpers2.html import HTML, tags
43
44from tailbone import forms
45
46
47log = logging.getLogger(__name__)
48
49
50class FilterValueRenderer(object):
51    """
52    Base class for all filter renderers.
53    """
54
55    @property
56    def name(self):
57        return self.filter.key
58
59    def render(self, value=None, **kwargs):
60        """
61        Render the filter input element(s) as HTML.  Default implementation
62        uses a simple text input.
63        """
64        return tags.text(self.name, value=value, **kwargs)
65
66
67class DefaultValueRenderer(FilterValueRenderer):
68    """
69    Default / fallback renderer.
70    """
71
72
73class NumericValueRenderer(FilterValueRenderer):
74    """
75    Input renderer for numeric values.
76    """
77
78    def render(self, value=None, **kwargs):
79        kwargs.setdefault('step', '0.001')
80        return tags.text(self.name, value=value, type='number', **kwargs)
81
82
83class DateValueRenderer(FilterValueRenderer):
84    """
85    Input renderer for date values.
86    """
87
88    def render(self, value=None, **kwargs):
89        kwargs['data-datepicker'] = 'true'
90        return tags.text(self.name, value=value, **kwargs)
91
92
93class ChoiceValueRenderer(FilterValueRenderer):
94    """
95    Renders value input as a dropdown/selectmenu of available choices.
96    """
97
98    def __init__(self, options):
99        self.options = options
100
101    def render(self, value=None, **kwargs):
102        return tags.select(self.name, [value], self.options, **kwargs)
103
104
105class EnumValueRenderer(ChoiceValueRenderer):
106    """
107    Renders value input as a dropdown/selectmenu of available choices.
108    """
109
110    def __init__(self, enum):
111        sorted_keys = sorted(enum, key=lambda k: enum[k].lower())
112        self.options = [tags.Option(enum[k], six.text_type(k)) for k in sorted_keys]
113
114
115class GridFilter(object):
116    """
117    Represents a filter available to a grid.  This is used to construct the
118    'filters' section when rendering the index page template.
119    """
120    verb_labels = {
121        'is_any':               "is any",
122        'equal':                "equal to",
123        'not_equal':            "not equal to",
124        'greater_than':         "greater than",
125        'greater_equal':        "greater than or equal to",
126        'less_than':            "less than",
127        'less_equal':           "less than or equal to",
128        'is_null':              "is null",
129        'is_not_null':          "is not null",
130        'is_true':              "is true",
131        'is_false':             "is false",
132        'contains':             "contains",
133        'does_not_contain':     "does not contain",
134        'is_me':                "is me",
135        'is_not_me':            "is not me",
136    }
137
138    valueless_verbs = ['is_any', 'is_null', 'is_not_null', 'is_true', 'is_false',
139                       'is_me', 'is_not_me']
140
141    value_renderer_factory = DefaultValueRenderer
142
143    def __init__(self, key, label=None, verbs=None, value_enum=None, value_renderer=None,
144                 default_active=False, default_verb=None, default_value=None,
145                 encode_values=False, value_encoding='utf-8', **kwargs):
146        self.key = key
147        self.label = label or prettify(key)
148        self.verbs = verbs or self.get_default_verbs()
149        if value_renderer:
150            self.set_value_renderer(value_renderer)
151        elif value_enum:
152            self.set_value_renderer(EnumValueRenderer(value_enum))
153        else:
154            self.set_value_renderer(self.value_renderer_factory)
155        self.default_active = default_active
156        self.default_verb = default_verb
157        self.default_value = default_value
158
159        self.encode_values = encode_values
160        self.value_encoding = value_encoding
161
162        for key, value in kwargs.items():
163            setattr(self, key, value)
164
165    def __repr__(self):
166        return "{}({})".format(self.__class__.__name__, repr(self.key))
167
168    def get_default_verbs(self):
169        """
170        Returns the set of verbs which will be used by default, i.e.  unless
171        overridden by constructor args etc.
172        """
173        verbs = getattr(self, 'default_verbs', None)
174        if verbs:
175            if callable(verbs):
176                return verbs()
177            return verbs
178        return ['equal', 'not_equal', 'is_null', 'is_not_null', 'is_any']
179
180    def set_value_renderer(self, renderer):
181        """
182        Set the value renderer for the filter, post-construction.
183        """
184        if not isinstance(renderer, FilterValueRenderer):
185            renderer = renderer()
186        renderer.filter = self
187        self.value_renderer = renderer
188
189    def filter(self, data, verb=None, value=UNSPECIFIED):
190        """
191        Filter the given data set according to a verb/value pair.  If no verb
192        and/or value is specified by the caller, the filter will use its own
193        current verb/value by default.
194        """
195        verb = verb or self.verb
196        value = self.get_value(value)
197        filtr = getattr(self, 'filter_{0}'.format(verb), None)
198        if not filtr:
199            raise ValueError("Unknown filter verb: {0}".format(repr(verb)))
200        return filtr(data, value)
201
202    def get_value(self, value=UNSPECIFIED):
203        return value if value is not UNSPECIFIED else self.value
204
205    def encode_value(self, value):
206        if self.encode_values and isinstance(value, six.string_types):
207            return value.encode('utf-8')
208        return value
209
210    def filter_is_any(self, data, value):
211        """
212        Special no-op filter which does no actual filtering.  Useful in some
213        cases to add an "ineffective" option to the verb list for a given grid
214        filter.
215        """
216        return data
217
218    def render_value(self, value=UNSPECIFIED, **kwargs):
219        """
220        Render the HTML needed to expose the filter's value for user input.
221        """
222        if value is UNSPECIFIED:
223            value = self.value
224        kwargs['filtr'] = self
225        return self.value_renderer.render(value=value, **kwargs)
226
227
228class MobileFilter(GridFilter):
229    """
230    Base class for mobile grid filters.
231    """
232    default_verbs = ['equal']
233
234    def __init__(self, key, **kwargs):
235        kwargs.setdefault('default_active', True)
236        kwargs.setdefault('default_verb', 'equal')
237        super(MobileFilter, self).__init__(key, **kwargs)
238
239
240class AlchemyGridFilter(GridFilter):
241    """
242    Base class for SQLAlchemy grid filters.
243    """
244
245    def __init__(self, *args, **kwargs):
246        self.column = kwargs.pop('column')
247        super(AlchemyGridFilter, self).__init__(*args, **kwargs)
248
249    def filter_equal(self, query, value):
250        """
251        Filter data with an equal ('=') query.
252        """
253        if value is None or value == '':
254            return query
255        return query.filter(self.column == self.encode_value(value))
256
257    def filter_not_equal(self, query, value):
258        """
259        Filter data with a not eqaul ('!=') query.
260        """
261        if value is None or value == '':
262            return query
263
264        # When saying something is 'not equal' to something else, we must also
265        # include things which are nothing at all, in our result set.
266        return query.filter(sa.or_(
267            self.column == None,
268            self.column != self.encode_value(value),
269        ))
270
271    def filter_is_null(self, query, value):
272        """
273        Filter data with an 'IS NULL' query.  Note that this filter does not
274        use the value for anything.
275        """
276        return query.filter(self.column == None)
277
278    def filter_is_not_null(self, query, value):
279        """
280        Filter data with an 'IS NOT NULL' query.  Note that this filter does
281        not use the value for anything.
282        """
283        return query.filter(self.column != None)
284
285    def filter_greater_than(self, query, value):
286        """
287        Filter data with a greater than ('>') query.
288        """
289        if value is None or value == '':
290            return query
291        return query.filter(self.column > self.encode_value(value))
292
293    def filter_greater_equal(self, query, value):
294        """
295        Filter data with a greater than or equal ('>=') query.
296        """
297        if value is None or value == '':
298            return query
299        return query.filter(self.column >= self.encode_value(value))
300
301    def filter_less_than(self, query, value):
302        """
303        Filter data with a less than ('<') query.
304        """
305        if value is None or value == '':
306            return query
307        return query.filter(self.column < self.encode_value(value))
308
309    def filter_less_equal(self, query, value):
310        """
311        Filter data with a less than or equal ('<=') query.
312        """
313        if value is None or value == '':
314            return query
315        return query.filter(self.column <= self.encode_value(value))
316
317
318class AlchemyStringFilter(AlchemyGridFilter):
319    """
320    String filter for SQLAlchemy.
321    """
322
323    def default_verbs(self):
324        """
325        Expose contains / does-not-contain verbs in addition to core.
326        """
327        return ['contains', 'does_not_contain',
328                'equal', 'not_equal', 'is_null', 'is_not_null', 'is_any']
329
330    def filter_contains(self, query, value):
331        """
332        Filter data with a full 'ILIKE' query.
333        """
334        if value is None or value == '':
335            return query
336        return query.filter(sa.and_(
337            *[self.column.ilike(self.encode_value('%{}%'.format(v)))
338              for v in value.split()]))
339
340    def filter_does_not_contain(self, query, value):
341        """
342        Filter data with a full 'NOT ILIKE' query.
343        """
344        if value is None or value == '':
345            return query
346
347        # When saying something is 'not like' something else, we must also
348        # include things which are nothing at all, in our result set.
349        return query.filter(sa.or_(
350            self.column == None,
351            sa.and_(
352                *[~self.column.ilike(self.encode_value('%{}%'.format(v)))
353                  for v in value.split()]),
354        ))
355
356
357class AlchemyEmptyStringFilter(AlchemyStringFilter):
358    """
359    String filter with special logic to treat empty string values as NULL
360    """
361
362    def filter_is_null(self, query, value):
363        return query.filter(
364            sa.or_(
365                self.column == None,
366                sa.func.trim(self.column) == self.encode_value('')))
367
368    def filter_is_not_null(self, query, value):
369        return query.filter(
370            sa.and_(
371                self.column != None,
372                sa.func.trim(self.column) != self.encode_value('')))
373
374
375class AlchemyByteStringFilter(AlchemyStringFilter):
376    """
377    String filter for SQLAlchemy, which encodes value as bytestring before
378    passing it along to the query.  Useful when querying certain databases
379    (esp. via FreeTDS) which like to throw the occasional segfault...
380    """
381    value_encoding = 'utf-8'
382
383    def get_value(self, value=UNSPECIFIED):
384        value = super(AlchemyByteStringFilter, self).get_value(value)
385        if isinstance(value, six.text_type):
386            value = value.encode(self.value_encoding)
387        return value
388
389    def filter_contains(self, query, value):
390        """
391        Filter data with a full 'ILIKE' query.
392        """
393        if value is None or value == '':
394            return query
395        return query.filter(sa.and_(
396            *[self.column.ilike(b'%{}%'.format(v)) for v in value.split()]))
397
398    def filter_does_not_contain(self, query, value):
399        """
400        Filter data with a full 'NOT ILIKE' query.
401        """
402        if value is None or value == '':
403            return query
404
405        # When saying something is 'not like' something else, we must also
406        # include things which are nothing at all, in our result set.
407        return query.filter(sa.or_(
408            self.column == None,
409            sa.and_(
410                *[~self.column.ilike(b'%{}%'.format(v)) for v in value.split()]),
411        ))
412
413
414class AlchemyNumericFilter(AlchemyGridFilter):
415    """
416    Numeric filter for SQLAlchemy.
417    """
418    value_renderer_factory = NumericValueRenderer
419
420    # expose greater-than / less-than verbs in addition to core
421    default_verbs = ['equal', 'not_equal', 'greater_than', 'greater_equal',
422                     'less_than', 'less_equal', 'is_null', 'is_not_null', 'is_any']
423
424    # TODO: what follows "works" in that it prevents an error...but from the
425    # user's perspective it still fails silently...need to improve on front-end
426
427    # try to detect (and ignore) common mistake where user enters UPC as search
428    # term for integer field...
429
430    def value_invalid(self, value):
431        return bool(value and len(six.text_type(value)) > 8)
432
433    def filter_equal(self, query, value):
434        if self.value_invalid(value):
435            return query
436        return super(AlchemyNumericFilter, self).filter_equal(query, value)
437
438    def filter_not_equal(self, query, value):
439        if self.value_invalid(value):
440            return query
441        return super(AlchemyNumericFilter, self).filter_not_equal(query, value)
442
443    def filter_greater_than(self, query, value):
444        if self.value_invalid(value):
445            return query
446        return super(AlchemyNumericFilter, self).filter_greater_than(query, value)
447
448    def filter_greater_equal(self, query, value):
449        if self.value_invalid(value):
450            return query
451        return super(AlchemyNumericFilter, self).filter_greater_equal(query, value)
452
453    def filter_less_than(self, query, value):
454        if self.value_invalid(value):
455            return query
456        return super(AlchemyNumericFilter, self).filter_less_than(query, value)
457
458    def filter_less_equal(self, query, value):
459        if self.value_invalid(value):
460            return query
461        return super(AlchemyNumericFilter, self).filter_less_equal(query, value)
462
463
464class AlchemyIntegerFilter(AlchemyNumericFilter):
465    """
466    Integer filter for SQLAlchemy.
467    """
468
469    def value_invalid(self, value):
470        if value:
471            if isinstance(value, int):
472                return True
473            if not value.isdigit():
474                return True
475            # TODO: this one is to avoid DataError from PG, but perhaps that
476            # isn't a good enough reason to make this global logic?
477            if int(value) > 2147483647:
478                return True
479        return False
480
481
482class AlchemyBooleanFilter(AlchemyGridFilter):
483    """
484    Boolean filter for SQLAlchemy.
485    """
486    default_verbs = ['is_true', 'is_false', 'is_any']
487
488    def filter_is_true(self, query, value):
489        """
490        Filter data with an "is true" query.  Note that this filter does not
491        use the value for anything.
492        """
493        return query.filter(self.column == True)
494
495    def filter_is_false(self, query, value):
496        """
497        Filter data with an "is false" query.  Note that this filter does not
498        use the value for anything.
499        """
500        return query.filter(self.column == False)
501
502
503class AlchemyNullableBooleanFilter(AlchemyBooleanFilter):
504    """
505    Boolean filter for SQLAlchemy which is NULL-aware.
506    """
507    default_verbs = ['is_true', 'is_false', 'is_null', 'is_not_null', 'is_any']
508
509
510class AlchemyDateFilter(AlchemyGridFilter):
511    """
512    Date filter for SQLAlchemy.
513    """
514    value_renderer_factory = DateValueRenderer
515
516    verb_labels = {
517        'equal':                "on",
518        'not_equal':            "not on",
519        'greater_than':         "after",
520        'greater_equal':        "on or after",
521        'less_than':            "before",
522        'less_equal':           "on or before",
523        'is_null':              "is null",
524        'is_not_null':          "is not null",
525        'is_any':               "is any",
526    }
527
528    def default_verbs(self):
529        """
530        Expose greater-than / less-than verbs in addition to core.
531        """
532        return ['equal', 'not_equal', 'greater_than', 'greater_equal',
533                'less_than', 'less_equal', 'is_null', 'is_not_null', 'is_any']
534
535    def make_date(self, value):
536        """
537        Convert user input to a proper ``datetime.date`` object.
538        """
539        if value:
540            try:
541                dt = datetime.datetime.strptime(value, '%Y-%m-%d')
542            except ValueError:
543                log.warning("invalid date value: {}".format(value))
544            else:
545                return dt.date()
546
547
548class AlchemyDateTimeFilter(AlchemyDateFilter):
549    """
550    SQLAlchemy filter for datetime values.
551    """
552
553    def filter_equal(self, query, value):
554        """
555        Find all dateimes which fall on the given date.
556        """
557        date = self.make_date(value)
558        if not date:
559            return query
560
561        start = datetime.datetime.combine(date, datetime.time(0))
562        start = make_utc(localtime(self.config, start))
563
564        stop = datetime.datetime.combine(date + datetime.timedelta(days=1), datetime.time(0))
565        stop = make_utc(localtime(self.config, stop))
566
567        return query.filter(self.column >= start)\
568                    .filter(self.column < stop)
569
570    def filter_not_equal(self, query, value):
571        """
572        Find all dateimes which do *not* fall on the given date.
573        """
574        date = self.make_date(value)
575        if not date:
576            return query
577
578        start = datetime.datetime.combine(date, datetime.time(0))
579        start = make_utc(localtime(self.config, start))
580
581        stop = datetime.datetime.combine(date + datetime.timedelta(days=1), datetime.time(0))
582        stop = make_utc(localtime(self.config, stop))
583
584        return query.filter(sa.or_(
585            self.column < start,
586            self.column <= stop))
587
588    def filter_greater_than(self, query, value):
589        """
590        Find all datetimes which fall after the given date.
591        """
592        date = self.make_date(value)
593        if not date:
594            return query
595
596        time = datetime.datetime.combine(date + datetime.timedelta(days=1), datetime.time(0))
597        time = make_utc(localtime(self.config, time))
598        return query.filter(self.column >= time)
599
600    def filter_greater_equal(self, query, value):
601        """
602        Find all datetimes which fall on or after the given date.
603        """
604        date = self.make_date(value)
605        if not date:
606            return query
607
608        time = datetime.datetime.combine(date, datetime.time(0))
609        time = make_utc(localtime(self.config, time))
610        return query.filter(self.column >= time)
611
612    def filter_less_than(self, query, value):
613        """
614        Find all datetimes which fall before the given date.
615        """
616        date = self.make_date(value)
617        if not date:
618            return query
619
620        time = datetime.datetime.combine(date, datetime.time(0))
621        time = make_utc(localtime(self.config, time))
622        return query.filter(self.column < time)
623
624    def filter_less_equal(self, query, value):
625        """
626        Find all datetimes which fall on or before the given date.
627        """
628        date = self.make_date(value)
629        if not date:
630            return query
631
632        time = datetime.datetime.combine(date + datetime.timedelta(days=1), datetime.time(0))
633        time = make_utc(localtime(self.config, time))
634        return query.filter(self.column < time)
635
636
637class AlchemyLocalDateTimeFilter(AlchemyDateTimeFilter):
638    """
639    SQLAlchemy filter for *local* datetime values.  This assumes datetime
640    values in the database are for local timezone, as opposed to UTC.
641    """
642
643    def filter_equal(self, query, value):
644        """
645        Find all dateimes which fall on the given date.
646        """
647        date = self.make_date(value)
648        if not date:
649            return query
650
651        start = datetime.datetime.combine(date, datetime.time(0))
652        start = localtime(self.config, start, tzinfo=False)
653
654        stop = datetime.datetime.combine(date + datetime.timedelta(days=1), datetime.time(0))
655        stop = localtime(self.config, stop, tzinfo=False)
656
657        return query.filter(self.column >= start)\
658                    .filter(self.column < stop)
659
660    def filter_not_equal(self, query, value):
661        """
662        Find all dateimes which do *not* fall on the given date.
663        """
664        date = self.make_date(value)
665        if not date:
666            return query
667
668        start = datetime.datetime.combine(date, datetime.time(0))
669        start = localtime(self.config, start, tzinfo=False)
670
671        stop = datetime.datetime.combine(date + datetime.timedelta(days=1), datetime.time(0))
672        stop = localtime(self.config, stop, tzinfo=False)
673
674        return query.filter(sa.or_(
675            self.column < start,
676            self.column <= stop))
677
678    def filter_greater_than(self, query, value):
679        """
680        Find all datetimes which fall after the given date.
681        """
682        date = self.make_date(value)
683        if not date:
684            return query
685
686        time = datetime.datetime.combine(date + datetime.timedelta(days=1), datetime.time(0))
687        time = localtime(self.config, time, tzinfo=False)
688        return query.filter(self.column >= time)
689
690    def filter_greater_equal(self, query, value):
691        """
692        Find all datetimes which fall on or after the given date.
693        """
694        date = self.make_date(value)
695        if not date:
696            return query
697
698        time = datetime.datetime.combine(date, datetime.time(0))
699        time = localtime(self.config, time, tzinfo=False)
700        return query.filter(self.column >= time)
701
702    def filter_less_than(self, query, value):
703        """
704        Find all datetimes which fall before the given date.
705        """
706        date = self.make_date(value)
707        if not date:
708            return query
709
710        time = datetime.datetime.combine(date, datetime.time(0))
711        time = localtime(self.config, time, tzinfo=False)
712        return query.filter(self.column < time)
713
714    def filter_less_equal(self, query, value):
715        """
716        Find all datetimes which fall on or before the given date.
717        """
718        date = self.make_date(value)
719        if not date:
720            return query
721
722        time = datetime.datetime.combine(date + datetime.timedelta(days=1), datetime.time(0))
723        time = localtime(self.config, time, tzinfo=False)
724        return query.filter(self.column < time)
725
726
727class AlchemyGPCFilter(AlchemyGridFilter):
728    """
729    GPC filter for SQLAlchemy.
730    """
731    default_verbs = ['equal', 'not_equal']
732
733    def filter_equal(self, query, value):
734        """
735        Filter data with an equal ('=') query.
736        """
737        if value is None or value == '':
738            return query
739        try:
740            return query.filter(self.column.in_((
741                GPC(value),
742                GPC(value, calc_check_digit='upc'))))
743        except ValueError:
744            return query
745
746    def filter_not_equal(self, query, value):
747        """
748        Filter data with a not eqaul ('!=') query.
749        """
750        if value is None or value == '':
751            return query
752
753        # When saying something is 'not equal' to something else, we must also
754        # include things which are nothing at all, in our result set.
755        try:
756            return query.filter(sa.or_(
757                ~self.column.in_((
758                    GPC(value),
759                    GPC(value, calc_check_digit='upc'))),
760                self.column == None))
761        except ValueError:
762            return query
763
764
765class GridFilterSet(OrderedDict):
766    """
767    Collection class for :class:`GridFilter` instances.
768    """
769
770
771class GridFiltersForm(forms.Form):
772    """
773    Form for grid filters.
774    """
775
776    def __init__(self, filters, **kwargs):
777        self.filters = filters
778        if 'schema' not in kwargs:
779            schema = colander.Schema()
780            for key, filtr in self.filters.items():
781                node = colander.SchemaNode(colander.String(), name=key)
782                schema.add(node)
783            kwargs['schema'] = schema
784        super(GridFiltersForm, self).__init__(**kwargs)
785
786    def iter_filters(self):
787        return self.filters.values()
788
789    def filter_verb(self, filtr):
790        """
791        Render the verb selection dropdown for the given filter.
792        """
793        options = [tags.Option(filtr.verb_labels.get(v, "unknown verb '{}'".format(v)), v)
794                   for v in filtr.verbs]
795        hide_values = [v for v in filtr.valueless_verbs
796                       if v in filtr.verbs]
797        return tags.select('{}.verb'.format(filtr.key), filtr.verb, options, **{
798            'class_': 'verb',
799            'data-hide-value-for': ' '.join(hide_values)})
800
801    def filter_value(self, filtr, **kwargs):
802        """
803        Render the value input element(s) for the filter.
804        """
805        style = 'display: none;' if filtr.verb in filtr.valueless_verbs else None
806        return HTML.tag('div', class_='value', style=style,
807                        c=[filtr.render_value(**kwargs)])
Note: See TracBrowser for help on using the repository browser.