selector.py 12.8 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 133 134
        - 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
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
    """
    def __init__(self, table, exclude_fields=()):
LE GAC Renaud's avatar
LE GAC Renaud committed
149

150 151 152 153 154 155 156
        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
157

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

        # add virtual fields
162
        self._db = db = current.globalenv["db"]
163

164 165 166
        history = db.history
        virtual = Field.Virtual

167 168 169 170 171 172 173
        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")
174

175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208
        # keep track of the identifier of the people event
        # since it is used by period_activity
        self._id_event_people = get_id(db.events, event="People")

    def _active_period(self, id_people, id_domain, id_team):
        """Determine the period when a person is active in a given
        domain and team.

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

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

        """
        rawsql = RAWSQL_ACTIVITY % (self._id_event_people,
                                    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:
209
            return (None, None)
210

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

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

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

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

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

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

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

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

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

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

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

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

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

256 257 258 259
        return value

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

314
        """
315 316 317 318
        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
319

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

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

326 327
        return False

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

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

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

338
        """
339 340 341 342
        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
343

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

347
        return False
348 349

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

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

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

359
        """
360 361 362 363
        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
364

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

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

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

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

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

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

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

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

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

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

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

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

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

441 442 443 444
        return query

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