selector.py 12.7 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
from datetime import date, timedelta
from gluon import current
from gluon.dal import Field
11 12 13 14 15 16 17 18 19 20
from plugin_dbui import get_id, Selector


RAWSQL_ACTIVITY = \
"""
    SELECT start_date, end_date
    FROM history
    WHERE id_events=%i AND id_people=%i AND id_domains=%i AND id_teams=%i
    ORDER BY start_date, end_date
"""
21 22


23 24
class SelectorActiveItemsException(BaseException):
    pass
LE GAC Renaud's avatar
LE GAC Renaud committed
25 26


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

30
    The period of time are defined by the selector fields
LE GAC Renaud's avatar
LE GAC Renaud committed
31
    ``year_start`` and ``year_end``.
LE GAC Renaud's avatar
LE GAC Renaud committed
32 33 34 35 36 37 38

    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
39

40
    """
41
    def __init__(self, table, exclude_fields=("year_end", "year_start")):
LE GAC Renaud's avatar
LE GAC Renaud committed
42

43
        self._cache_period = None
LE GAC Renaud's avatar
LE GAC Renaud committed
44

LE GAC Renaud's avatar
LE GAC Renaud committed
45 46
        self._period_end = None
        self._period_start = None
LE GAC Renaud's avatar
LE GAC Renaud committed
47

LE GAC Renaud's avatar
LE GAC Renaud committed
48 49 50 51 52
        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)
53

LE GAC Renaud's avatar
LE GAC Renaud committed
54 55 56
        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
57

LE GAC Renaud's avatar
LE GAC Renaud committed
58 59
        else:
            raise SelectorActiveItemsException("Period is not defined.")
60

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

LE GAC Renaud's avatar
LE GAC Renaud committed
64
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
65
            list:
LE GAC Renaud's avatar
LE GAC Renaud committed
66

67 68 69
        """
        if self._cache_period:
            start, end = self._cache_period
LE GAC Renaud's avatar
LE GAC Renaud committed
70

71
        else:
LE GAC Renaud's avatar
LE GAC Renaud committed
72 73
            start = self._period_start.year
            end = self._period_end.year
LE GAC Renaud's avatar
LE GAC Renaud committed
74

75 76
        return range(start, end + 1)

77
    def query(self, table):
LE GAC Renaud's avatar
LE GAC Renaud committed
78 79 80 81
        """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
82

LE GAC Renaud's avatar
LE GAC Renaud committed
83 84 85
        Args:
            table (gluon.dal.Table):
                the database table in which records are selected.
LE GAC Renaud's avatar
LE GAC Renaud committed
86

LE GAC Renaud's avatar
LE GAC Renaud committed
87 88 89 90 91
        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
92

93 94
        """
        query = Selector.query(self, table)
LE GAC Renaud's avatar
LE GAC Renaud committed
95

LE GAC Renaud's avatar
LE GAC Renaud committed
96
        if self._period_start and self._period_end:
97 98
            query &= table.start_date <= self._period_end
            q2 = table.end_date == None
99
            q3 = table.end_date >= self._period_start
100
            query &= (q2 | q3)
LE GAC Renaud's avatar
LE GAC Renaud committed
101

102 103
        return query

104 105
    def reset_period(self):
        """Reset the period to the initial values.
LE GAC Renaud's avatar
LE GAC Renaud committed
106

107
        """
LE GAC Renaud's avatar
LE GAC Renaud committed
108 109
        if self._cache_period:
            self._period_start, self._period_end = self._cache_period
110
            self._cache_period = None
LE GAC Renaud's avatar
LE GAC Renaud committed
111

112 113 114
    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
115

LE GAC Renaud's avatar
LE GAC Renaud committed
116 117
        Args:
            year (integer):
LE GAC Renaud's avatar
LE GAC Renaud committed
118

119 120
        """
        # keep original period
121
        if self._cache_period is None:
LE GAC Renaud's avatar
LE GAC Renaud committed
122
            self._cache_period = (self._period_start, self._period_end)
LE GAC Renaud's avatar
LE GAC Renaud committed
123

LE GAC Renaud's avatar
LE GAC Renaud committed
124 125
        self._period_start = date(year, 1, 1)
        self._period_end = date(year, 12, 31)
126 127


128
class EvtSelector(SelectorActiveItems):
LE GAC Renaud's avatar
LE GAC Renaud committed
129
    """Main selector to build list, metric or graph according to
130
    the user criteria:
LE GAC Renaud's avatar
LE GAC Renaud committed
131

132
        - focus on history record and on a given event.
133 134
        - 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
135 136
        - For each record computes virtual field: ``age``, ``coverage``,
          ``duration``, ``fte`` and ``is_over``.
LE GAC Renaud's avatar
LE GAC Renaud committed
137

LE GAC Renaud's avatar
LE GAC Renaud committed
138 139 140 141 142
    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
143 144 145
            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
146

147 148
        id_event (int): the event identifier

149
    """
150
    def __init__(self, table, id_event=0, exclude_fields=()):
LE GAC Renaud's avatar
LE GAC Renaud committed
151

152 153 154 155 156 157 158
        li = ["data",
              "id_object_categories",
              "id_object_code",
              "id_people_categories",
              "id_people_code",
              "year_end",
              "year_start"]
LE GAC Renaud's avatar
LE GAC Renaud committed
159

160
        li.extend(exclude_fields)
161 162
        SelectorActiveItems.__init__(self, table, exclude_fields=li)

163
        self._db = db = current.globalenv["db"]
164
        self._id_event = id_event
165

166
        # add virtual fields
167 168 169
        history = db.history
        virtual = Field.Virtual

170 171 172 173 174 175 176
        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")
177

178 179
    def _active_period(self, id_people, id_domain, id_team):
        """Determine the period when a person is active in a given
180
        domain and team and for a given event
181 182 183 184 185 186 187 188 189 190 191 192

        Args:
            id_people (int):
            id_domain (int):
            id_team (int):

        Returns
            tuple:
                start (datetime or None)
                stop (datetime or None)

        """
193
        rawsql = RAWSQL_ACTIVITY % (self._id_event,
194 195 196 197 198 199 200 201 202 203 204 205 206 207
                                    id_people,
                                    id_domain,
                                    id_team)

        rows =  self._db.executesql(rawsql)
        nrows = len(rows)

        if nrows == 1:
            return (rows[0][0], rows[0][1])

        elif nrows > 1:
            return (rows[0][0], rows[-1][1])

        else:
208
            return (None, None)
209

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

LE GAC Renaud's avatar
LE GAC Renaud committed
213 214
        Args:
            row (gluon.dal.Row): row of the history table.
LE GAC Renaud's avatar
LE GAC Renaud committed
215

LE GAC Renaud's avatar
LE GAC Renaud committed
216 217
        Returns:
            int or None:
LE GAC Renaud's avatar
LE GAC Renaud committed
218

219 220 221 222 223 224 225
        """
        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
226

227
    def _coverage(self, row):
228 229
        """Compute the time coverage of the history record with respect
        to the period range.
LE GAC Renaud's avatar
LE GAC Renaud committed
230

LE GAC Renaud's avatar
LE GAC Renaud committed
231 232
        Args:
            row (gluon.dal.Row): row of the history table.
233

LE GAC Renaud's avatar
LE GAC Renaud committed
234 235
        Returns:
            float: a value between ``0`` and ``1``.
LE GAC Renaud's avatar
LE GAC Renaud committed
236

237 238
        """
        value = 0.
239 240 241

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

243
        if period_start and period_end:
LE GAC Renaud's avatar
LE GAC Renaud committed
244

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

247
            end = period_end
248
            if row.history.end_date:
249
                end = min(period_end, row.history.end_date)
LE GAC Renaud's avatar
LE GAC Renaud committed
250

251
            x = (end - start).days
252
            y = (period_end - period_start).days
253
            value = float(x) / float(y)
LE GAC Renaud's avatar
LE GAC Renaud committed
254

255 256 257 258
        return value

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

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

LE GAC Renaud's avatar
LE GAC Renaud committed
264
        Returns:
265
            float: in seconds
LE GAC Renaud's avatar
LE GAC Renaud committed
266

267 268
        """
        value = timedelta(seconds=0)
LE GAC Renaud's avatar
LE GAC Renaud committed
269

270 271 272
        end = row.history.end_date
        now = date.today()
        start = row.history.start_date
LE GAC Renaud's avatar
LE GAC Renaud committed
273

274 275
        if start and end and start <= now <= end:
            value = now - start
LE GAC Renaud's avatar
LE GAC Renaud committed
276

277
        elif start and end is None and start <= now:
278
            value = now - start
LE GAC Renaud's avatar
LE GAC Renaud committed
279

280
        elif start and end and end < now:
LE GAC Renaud's avatar
LE GAC Renaud committed
281
            value = end - start
282

283
        return value.total_seconds()
LE GAC Renaud's avatar
LE GAC Renaud committed
284

285
    def _fte(self, row):
286 287 288
        """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.
289

LE GAC Renaud's avatar
LE GAC Renaud committed
290 291
        Args:
            row (gluon.dal.Row): row of the history table.
LE GAC Renaud's avatar
LE GAC Renaud committed
292

LE GAC Renaud's avatar
LE GAC Renaud committed
293 294
        Returns:
            float: a value between 0 and 1.
LE GAC Renaud's avatar
LE GAC Renaud committed
295

296 297 298 299 300 301
        """
        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
302

303
    def _is_end(self, row):
304 305
        """Return true is the active period (for a given person, domain and
        team) ends during the period range.
306

LE GAC Renaud's avatar
LE GAC Renaud committed
307 308
        Args:
            row (gluon.dal.Row): row of the history table.
309

LE GAC Renaud's avatar
LE GAC Renaud committed
310 311
        Returns:
            bool:
LE GAC Renaud's avatar
LE GAC Renaud committed
312

313
        """
314 315 316 317
        history = row.history
        start, end = self._active_period(history.id_people,
                                         history.id_domains,
                                         history.id_teams)
LE GAC Renaud's avatar
LE GAC Renaud committed
318

319 320
        period_end = self._period_end
        period_start = self._period_start
LE GAC Renaud's avatar
LE GAC Renaud committed
321

322 323
        if end and start != end:
            return period_start <= end <= period_end
LE GAC Renaud's avatar
LE GAC Renaud committed
324

325 326
        return False

327
    def _is_over(self, row):
328 329
        """Return true is the active period (for a given person, domain and
        team) is over now.
330

LE GAC Renaud's avatar
LE GAC Renaud committed
331 332
        Args:
            row (gluon.dal.Row): row of the history table.
333

LE GAC Renaud's avatar
LE GAC Renaud committed
334 335
        Returns:
            bool:
LE GAC Renaud's avatar
LE GAC Renaud committed
336

337
        """
338 339 340 341
        history = row.history
        start, end = self._active_period(history.id_people,
                                         history.id_domains,
                                         history.id_teams)
LE GAC Renaud's avatar
LE GAC Renaud committed
342

343
        if end and start != end:
344
            return end < date.today()
LE GAC Renaud's avatar
LE GAC Renaud committed
345

346
        return False
347 348

    def _is_start(self, row):
349 350
        """Return true is the active period (for a given person, domain and
        team) starts during the period range.
351

LE GAC Renaud's avatar
LE GAC Renaud committed
352 353
        Args:
            row (gluon.dal.Row): row of the history table.
354

LE GAC Renaud's avatar
LE GAC Renaud committed
355 356
        Returns:
            bool:
LE GAC Renaud's avatar
LE GAC Renaud committed
357

358
        """
359 360 361 362
        history = row.history
        start, end = self._active_period(history.id_people,
                                         history.id_domains,
                                         history.id_teams)
LE GAC Renaud's avatar
LE GAC Renaud committed
363

364 365
        period_end = self._period_end
        period_start = self._period_start
LE GAC Renaud's avatar
LE GAC Renaud committed
366

367 368
        if start and start != end:
            return period_start <= start <= period_end
LE GAC Renaud's avatar
LE GAC Renaud committed
369

370
        return False
LE GAC Renaud's avatar
LE GAC Renaud committed
371

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

LE GAC Renaud's avatar
LE GAC Renaud committed
375 376
        Args:
            table (gluon.dal.Table):
LE GAC Renaud's avatar
LE GAC Renaud committed
377

378 379 380
        """
        db = table._db
        query = SelectorActiveItems.query(self, table)
LE GAC Renaud's avatar
LE GAC Renaud committed
381

382
        # history data block
383
        # list of key, value pair: [[None, "v1"], ["k2", "v2"], [None, "v5"]]
384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408
        # 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)
409

410 411 412
        # object category and code
        id_object_code = self.id_object_code
        if id_object_code:
413
            query &= db.history.id_object_categories == id_object_code
414 415 416

        id_object_category = self.id_object_categories
        if id_object_category:
417
            object_categories = db.object_categories
418

419 420 421 422 423
            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)
424

425 426 427
        # people category and code
        id_people_code = self.id_people_code
        if id_people_code:
428
            query &= db.history.id_people_categories == id_people_code
429 430 431

        id_people_category = self.id_people_categories
        if id_people_category:
432 433 434 435 436
            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)}
437

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

440 441 442 443
        return query

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