source: tailbone/tailbone/grids/filters.py @ f755aef

Last change on this file since f755aef was f755aef, checked in by Lance Edgar <ledgar@…>, 17 months ago

Honor enum sort order where possible, for grid filter values

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