selector.py 11.1 KB
Newer Older
LE GAC Renaud's avatar
LE GAC Renaud committed
1
# -*- coding: utf-8 -*-
2 3 4
"""selector module

"""
5 6 7
import json


8 9 10 11 12 13
from datetime import date, timedelta
from gluon import current
from gluon.dal import Field
from plugin_dbui import Selector


14 15
class SelectorActiveItemsException(BaseException):
    pass
LE GAC Renaud's avatar
LE GAC Renaud committed
16 17


18 19
class SelectorActiveItems(Selector):
    """Selector to get records active during a given period of time.
LE GAC Renaud's avatar
LE GAC Renaud committed
20

21
    The period of time are defined by the selector fields
LE GAC Renaud's avatar
LE GAC Renaud committed
22
    ``year_start`` and ``year_end``.
LE GAC Renaud's avatar
LE GAC Renaud committed
23 24 25 26 27 28 29

    Args:
        table (gluon.dal.Table): the virtual table defining the selector.

        exclude_fields (list of string):
            name of the selector fields which are
            excluded in the query.
LE GAC Renaud's avatar
LE GAC Renaud committed
30

31
    """
LE GAC Renaud's avatar
LE GAC Renaud committed
32
    def __init__(self, table, exclude_fields=('year_end', 'year_start')):
LE GAC Renaud's avatar
LE GAC Renaud committed
33

34
        self._cache_period = None
LE GAC Renaud's avatar
LE GAC Renaud committed
35

LE GAC Renaud's avatar
LE GAC Renaud committed
36 37
        self._period_end = None
        self._period_start = None
LE GAC Renaud's avatar
LE GAC Renaud committed
38

LE GAC Renaud's avatar
LE GAC Renaud committed
39 40 41 42 43
        Selector.__init__(self, table, exclude_fields=exclude_fields)

        if self.year_start and not self.year_end:
            self._period_start = date(self.year_start, 1, 1)
            self._period_end = date(self.year_start, 12, 31)
44

LE GAC Renaud's avatar
LE GAC Renaud committed
45 46 47
        elif self.year_start and self.year_end:
            self._period_start = date(self.year_start, 1, 1)
            self._period_end = date(self.year_end, 12, 31)
LE GAC Renaud's avatar
LE GAC Renaud committed
48

LE GAC Renaud's avatar
LE GAC Renaud committed
49 50
        else:
            raise SelectorActiveItemsException("Period is not defined.")
51

52
    def get_years(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
53 54
        """The list of year between period start and end.

LE GAC Renaud's avatar
LE GAC Renaud committed
55
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
56
            list:
LE GAC Renaud's avatar
LE GAC Renaud committed
57

58 59 60
        """
        if self._cache_period:
            start, end = self._cache_period
LE GAC Renaud's avatar
LE GAC Renaud committed
61

62
        else:
LE GAC Renaud's avatar
LE GAC Renaud committed
63 64
            start = self._period_start.year
            end = self._period_end.year
LE GAC Renaud's avatar
LE GAC Renaud committed
65

66 67
        return range(start, end + 1)

68
    def query(self, table):
LE GAC Renaud's avatar
LE GAC Renaud committed
69 70 71 72
        """Build the database query for the database *table*.

        The query includes inner join for foreign keys, selector constraints
        as well as extra queries.
LE GAC Renaud's avatar
LE GAC Renaud committed
73

LE GAC Renaud's avatar
LE GAC Renaud committed
74 75 76
        Args:
            table (gluon.dal.Table):
                the database table in which records are selected.
LE GAC Renaud's avatar
LE GAC Renaud committed
77

LE GAC Renaud's avatar
LE GAC Renaud committed
78 79 80 81 82
        Returns:
            gluon.dal.Query:
                The query selects active items during the given period of
                time. It only works for table having the database fields
                ``start_date`` and ``end_date``.
LE GAC Renaud's avatar
LE GAC Renaud committed
83

84 85
        """
        query = Selector.query(self, table)
LE GAC Renaud's avatar
LE GAC Renaud committed
86

LE GAC Renaud's avatar
LE GAC Renaud committed
87
        if self._period_start and self._period_end:
88 89
            query &= table.start_date <= self._period_end
            q2 = table.end_date == None
90
            q3 = table.end_date >= self._period_start
91
            query &= (q2 | q3)
LE GAC Renaud's avatar
LE GAC Renaud committed
92

93 94
        return query

95 96
    def reset_period(self):
        """Reset the period to the initial values.
LE GAC Renaud's avatar
LE GAC Renaud committed
97

98
        """
LE GAC Renaud's avatar
LE GAC Renaud committed
99 100
        if self._cache_period:
            self._period_start, self._period_end = self._cache_period
101
            self._cache_period = None
LE GAC Renaud's avatar
LE GAC Renaud committed
102

103 104 105
    def set_year(self, year):
        """Modify the period attributes in order to query records active
        during the given year.
LE GAC Renaud's avatar
LE GAC Renaud committed
106

LE GAC Renaud's avatar
LE GAC Renaud committed
107 108
        Args:
            year (integer):
LE GAC Renaud's avatar
LE GAC Renaud committed
109

110 111
        """
        # keep original period
112
        if self._cache_period is None:
LE GAC Renaud's avatar
LE GAC Renaud committed
113
            self._cache_period = (self._period_start, self._period_end)
LE GAC Renaud's avatar
LE GAC Renaud committed
114

LE GAC Renaud's avatar
LE GAC Renaud committed
115 116
        self._period_start = date(year, 1, 1)
        self._period_end = date(year, 12, 31)
117 118


119
class MySelector(SelectorActiveItems):
LE GAC Renaud's avatar
LE GAC Renaud committed
120
    """Main selector to build list, metric or graph according to
121
    the user criteria:
LE GAC Renaud's avatar
LE GAC Renaud committed
122

123 124 125
        - focus on history record
        - Select record active during the selected period of time.
        - Selection can be performed on category defined as a string.
LE GAC Renaud's avatar
LE GAC Renaud committed
126 127
        - For each record computes virtual field: ``age``, ``coverage``,
          ``duration``, ``fte`` and ``is_over``.
LE GAC Renaud's avatar
LE GAC Renaud committed
128

LE GAC Renaud's avatar
LE GAC Renaud committed
129 130 131 132 133
    Args:
        table (gluon.dal.Table): the virtual table defining the selector.

        exclude_fields (tuple of string):
            extend the list selector fields which are
LE GAC Renaud's avatar
LE GAC Renaud committed
134 135 136
            excluded in the query. The field related to people / object
            categories, year_end, year_start and  year are systematically
            excluded.
LE GAC Renaud's avatar
LE GAC Renaud committed
137

138 139
    """
    def __init__(self, table, exclude_fields=()):
LE GAC Renaud's avatar
LE GAC Renaud committed
140

141 142
        li = ['data',
              'id_object_categories',
143 144 145 146 147
              'id_object_code',
              'id_people_categories',
              'id_people_code',
              'year_end',
              'year_start']
LE GAC Renaud's avatar
LE GAC Renaud committed
148

149
        li.extend(exclude_fields)
150 151 152 153 154
        SelectorActiveItems.__init__(self, table, exclude_fields=li)

        # add virtual fields
        db = current.globalenv['db']

155 156 157 158 159 160 161 162 163 164
        history = db.history
        virtual = Field.Virtual

        db.people.age = virtual('age', self._age, ftype='integer')
        history.coverage = virtual('coverage', self._coverage, ftype='double')
        history.duration = virtual('duration', self._duration, ftype='double')
        history.fte = virtual('fte', self._fte, ftype='double')
        history.is_end = virtual('is_end', self._is_end, ftype='boolean')
        history.is_over = virtual('is_over', self._is_over, ftype='boolean')
        history.is_start = virtual('is_start', self._is_start, ftype='boolean')
165 166

    def _age(self, row):
167
        """Compute the age of the person associated to the history record, now.
168

LE GAC Renaud's avatar
LE GAC Renaud committed
169 170
        Args:
            row (gluon.dal.Row): row of the history table.
LE GAC Renaud's avatar
LE GAC Renaud committed
171

LE GAC Renaud's avatar
LE GAC Renaud committed
172 173
        Returns:
            int or None:
LE GAC Renaud's avatar
LE GAC Renaud committed
174

175 176 177 178 179 180 181
        """
        value = None
        now = date.today()
        if row.people.birth_date:
            value = ((now - row.people.birth_date).days / 365)

        return value
LE GAC Renaud's avatar
LE GAC Renaud committed
182

183
    def _coverage(self, row):
184 185
        """Compute the time coverage of the history record with respect
        to the period range.
LE GAC Renaud's avatar
LE GAC Renaud committed
186

LE GAC Renaud's avatar
LE GAC Renaud committed
187 188
        Args:
            row (gluon.dal.Row): row of the history table.
189

LE GAC Renaud's avatar
LE GAC Renaud committed
190 191
        Returns:
            float: a value between ``0`` and ``1``.
LE GAC Renaud's avatar
LE GAC Renaud committed
192

193 194
        """
        value = 0.
195 196 197

        period_end = self._period_end
        period_start = self._period_start
LE GAC Renaud's avatar
LE GAC Renaud committed
198

199
        if period_start and period_end:
LE GAC Renaud's avatar
LE GAC Renaud committed
200

201
            start = max(period_start, row.history.start_date)
LE GAC Renaud's avatar
LE GAC Renaud committed
202

203
            end = period_end
204
            if row.history.end_date:
205
                end = min(period_end, row.history.end_date)
LE GAC Renaud's avatar
LE GAC Renaud committed
206

207
            x = (end - start).days
208
            y = (period_end - period_start).days
209
            value = float(x) / float(y)
LE GAC Renaud's avatar
LE GAC Renaud committed
210

211 212 213 214
        return value

    def _duration(self, row):
        """Compute the duration of the history record.
LE GAC Renaud's avatar
LE GAC Renaud committed
215
        The end date is ``now`` when the history record is not finished.
216

LE GAC Renaud's avatar
LE GAC Renaud committed
217 218
        Args:
            row (gluon.dal.Row): row of the history table.
LE GAC Renaud's avatar
LE GAC Renaud committed
219

LE GAC Renaud's avatar
LE GAC Renaud committed
220
        Returns:
221
            float: in seconds
LE GAC Renaud's avatar
LE GAC Renaud committed
222

223 224
        """
        value = timedelta(seconds=0)
LE GAC Renaud's avatar
LE GAC Renaud committed
225

226 227 228
        end = row.history.end_date
        now = date.today()
        start = row.history.start_date
LE GAC Renaud's avatar
LE GAC Renaud committed
229

230 231
        if start and end and start <= now <= end:
            value = now - start
LE GAC Renaud's avatar
LE GAC Renaud committed
232

233
        elif start and end is None and start <= now:
234
            value = now - start
LE GAC Renaud's avatar
LE GAC Renaud committed
235

236
        elif start and end and end < now:
LE GAC Renaud's avatar
LE GAC Renaud committed
237
            value = end - start
238

239
        return value.total_seconds()
LE GAC Renaud's avatar
LE GAC Renaud committed
240

241
    def _fte(self, row):
242 243 244
        """Compute the full time equivalent of the person associated
        to the history record. It is the product of the time coverage
        and the percentage of the person assigned to the record.
245

LE GAC Renaud's avatar
LE GAC Renaud committed
246 247
        Args:
            row (gluon.dal.Row): row of the history table.
LE GAC Renaud's avatar
LE GAC Renaud committed
248

LE GAC Renaud's avatar
LE GAC Renaud committed
249 250
        Returns:
            float: a value between 0 and 1.
LE GAC Renaud's avatar
LE GAC Renaud committed
251

252 253 254 255 256 257
        """
        value = 0.
        if row.history.percentage:
            value = self._coverage(row) * (row.history.percentage / 100.)

        return value
LE GAC Renaud's avatar
LE GAC Renaud committed
258

259 260 261
    def _is_end(self, row):
        """Return true is the history record ends during the period range.

LE GAC Renaud's avatar
LE GAC Renaud committed
262 263
        Args:
            row (gluon.dal.Row): row of the history table.
264

LE GAC Renaud's avatar
LE GAC Renaud committed
265 266
        Returns:
            bool:
LE GAC Renaud's avatar
LE GAC Renaud committed
267

268 269 270
        """
        end = row.history.end_date
        start = row.history.start_date
LE GAC Renaud's avatar
LE GAC Renaud committed
271

272 273
        period_end = self._period_end
        period_start = self._period_start
LE GAC Renaud's avatar
LE GAC Renaud committed
274

275 276
        if end and start != end:
            return period_start <= end <= period_end
LE GAC Renaud's avatar
LE GAC Renaud committed
277

278 279
        return False

280 281 282
    def _is_over(self, row):
        """Return true is the history record is over now.

LE GAC Renaud's avatar
LE GAC Renaud committed
283 284
        Args:
            row (gluon.dal.Row): row of the history table.
285

LE GAC Renaud's avatar
LE GAC Renaud committed
286 287
        Returns:
            bool:
LE GAC Renaud's avatar
LE GAC Renaud committed
288

289 290 291 292
        """
        end = row.history.end_date
        now = date.today()
        start = row.history.start_date
LE GAC Renaud's avatar
LE GAC Renaud committed
293

294 295
        if end and start != end:
            return end < now
LE GAC Renaud's avatar
LE GAC Renaud committed
296

297
        return False
298 299 300 301

    def _is_start(self, row):
        """Return true is the history record starts during the period range.

LE GAC Renaud's avatar
LE GAC Renaud committed
302 303
        Args:
            row (gluon.dal.Row): row of the history table.
304

LE GAC Renaud's avatar
LE GAC Renaud committed
305 306
        Returns:
            bool:
LE GAC Renaud's avatar
LE GAC Renaud committed
307

308 309 310
        """
        end = row.history.end_date
        start = row.history.start_date
LE GAC Renaud's avatar
LE GAC Renaud committed
311

312 313
        period_end = self._period_end
        period_start = self._period_start
LE GAC Renaud's avatar
LE GAC Renaud committed
314

315 316
        if start and start != end:
            return period_start <= start <= period_end
LE GAC Renaud's avatar
LE GAC Renaud committed
317

318
        return False
LE GAC Renaud's avatar
LE GAC Renaud committed
319

320
    def query(self, table):
LE GAC Renaud's avatar
LE GAC Renaud committed
321
        """Supersede the base class method to handle categories constraints.
322

LE GAC Renaud's avatar
LE GAC Renaud committed
323 324
        Args:
            table (gluon.dal.Table):
LE GAC Renaud's avatar
LE GAC Renaud committed
325

326 327 328
        """
        db = table._db
        query = SelectorActiveItems.query(self, table)
LE GAC Renaud's avatar
LE GAC Renaud committed
329

330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356
        # history data block
        # list of key, value pair: [[None, 'v1'], ['k2', 'v2'], [None, 'v5']]
        # apply the like operator: %bla%bla%
        data = json.loads(self.data)
        if len(data) > 0:
            conditions = []
            for key, val in data:
                if key is None and val is None:
                    pass

                elif key is None:
                    conditions.append(val)

                elif val is None:
                    conditions.append('"%s":' % key)

                elif val in ("true", "false"):
                    conditions.append('"%s": %s' % (key, val))

                elif isinstance(val, (unicode, str)):
                    conditions.append('"%s": "%s"' % (key, val))

                else:
                    conditions.append('"%s": %s' % (key, val))

            conditions = "%%%s%%" % "%".join(conditions)
            query &= db.history.data.like(conditions, case_sensitive=False)
357

358 359 360
        # object category and code
        id_object_code = self.id_object_code
        if id_object_code:
361
            query &= db.history.id_object_categories == id_object_code
362 363 364

        id_object_category = self.id_object_categories
        if id_object_category:
365
            object_categories = db.object_categories
366

367 368 369 370 371
            category = object_categories[id_object_category].category
            qcat = object_categories.category == category
            myset = {row.id for row in db(qcat).select(object_categories.id)}

            query &= db.history.id_object_categories.belongs(myset)
372

373 374 375
        # people category and code
        id_people_code = self.id_people_code
        if id_people_code:
376
            query &= db.history.id_people_categories == id_people_code
377 378 379

        id_people_category = self.id_people_categories
        if id_people_category:
380 381 382 383 384
            people_categories = db.people_categories

            category = people_categories[id_people_category].category
            qcat = people_categories.category == category
            myset = {row.id for row in db(qcat).select(people_categories.id)}
385

386
            query &= db.history.id_people_categories.belongs(myset)
LE GAC Renaud's avatar
LE GAC Renaud committed
387

388 389 390 391
        return query

    def reset_extra_queries(self):
        self._extra_queries = []