model_report.py 12.3 KB
Newer Older
1 2 3 4
# -*- coding: utf-8 -*-
"""Definitions of the report tables

"""
5
import numpy as np
6 7 8
import plugin_dbui as dbui


9 10 11
from callbacks import (INHIBIT_CASCADE_DELETE,
                       ON_CREATE_LISTS2,
                       ON_UPDATE_LISTS2)
12 13
from gluon import current, IS_IN_DB, IS_IN_SET
from gluon.tools import PluginManager
14
from pydal import Field
15 16


17 18 19 20 21 22
AGGREGATE_FUNCS = {
    "max": np.max,
    "mean": np.min,
    "median": np.median,
    "min": np.min,
    "sem": np.max,
23
    "size": np.size,
24 25 26
    "sum": np.sum,
    "std": np.std,
    "var": np.var}
27

28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93
DEF_COLUMNS_LISTS = \
"""[{
    "xtype": "rownumberer"
}, {
    "dbfield": "?.?",
    "flex": 1,
    "text": "?",
    "xtype": "gridcolumn"
}]"""

DEF_COLUMNS_METRIC1D = \
"""[{
    "aggregate": "?",
    "align": "right",
    "dbfield": "?.?",
    "format": "0.0",
    "text": "?",
    "xtype": "numbercolumn"
}]"""

DEF_FEATURES = \
"""[{
    "ftype": "groupingsummary",
    "groupHeaderTpl": "{name}",
    "startCollapsed": false
}, {
    "ftype": "summary"
}]"""

DEF_PLOT = \
"""{
    "kind": "?",
    "stacked": false
}"""

TP_AGG = \
    "The aggregation function applies on the metric field. " \
    "It is computed on the subset of row having a given values for " \
    "the group_field x and y."

TP_COLUMNS = "Configure the column of the grid displayed in the view."

TP_CONDITIONS = \
    "Database query to select history records:<br> " \
    "(db.events.event == 'People') & (db.history.data.like('%cdd%'))<br>"

TP_GROUP_FIELD_LISTS = \
    "Row are grouped according to the value of that field. " \
    "It can be any field of the history table including those of " \
    "the foreign tables, the individual property of the history.data " \
    "dictionary, or 'year'."

TP_GROUP_FIELD_METRICS = \
    "Metric are computed for each value of that field. " \
    "It can be any field of the history table including those of the " \
    "foreign tables, the individual property of the history.data dictionary " \
    "or 'year'"

TP_FEATURES = "Summary value can be computed for columns."

TP_SORTERS = \
    "Entries are sorted according to the value of these fields. " \
    "It can be any field of the history table including those of the " \
    "foreign tables, the individual property of the history.data dictionary, " \
    "or 'year'. Descending order is obtained by using the '~field' construct."

94 95 96
TP_SUMMARY_X = "To sum, for example, the content of each row."
TP_SUMMARY_Y = "To sum, for example, the content of each column."

97

98
class Report(object):
99 100
    """Create the tables for the reporting: lists, metrics1d, metrics2d,
    and graphs.
101

102
    """
103 104 105 106 107 108 109 110 111
    @staticmethod
    def define_tables(db, T):
        """Define tables for the reporting.

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        """
112

113 114
        Report.graphs(db, T)
        Report.lists(db, T)
LE GAC Renaud's avatar
LE GAC Renaud committed
115
        Report.lists2(db, T)
116 117
        Report.metrics1d(db, T)
        Report.metrics2d(db, T)
118
        Report.metrics2d2(db, T)
119 120 121 122 123 124 125 126 127 128 129 130 131

    @staticmethod
    def graphs(db, T):
        """graphs table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """
132
        def_plot = (None if db._migrate else DEF_PLOT)
133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157

        table = db.define_table(
            "graphs",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("report_type", "string", length=255, notnull=True),
            Field("report_name", "string", length=255, notnull=True),
            Field("plot", "text", notnull=True, default=def_plot),
            Field("definition", "text"),
            migrate="graphs.table")

        return table

    @staticmethod
    def lists(db, T):
        """lists table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """
158 159
        def_columns = (None if db._migrate else DEF_COLUMNS_LISTS)
        def_features = (None if db._migrate else DEF_FEATURES)
160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189

        table = db.define_table(
            "lists",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("conditions", "text", comment=T(TP_CONDITIONS)),

            Field("group_field",
                  "string",
                  length=255,
                  comment=T(TP_GROUP_FIELD_LISTS)),

            Field("sorters", "list:string", comment=TP_SORTERS),

            Field("columns",
                  "text",
                  default=def_columns,
                  comment=T(TP_COLUMNS),
                  notnull=True),

            Field("features",
                  "text",
                  default=def_features,
                  comment=T(TP_FEATURES)),

            Field("definition", "text"),
            migrate="lists.table")

        return table

LE GAC Renaud's avatar
LE GAC Renaud committed
190 191 192 193 194 195 196 197 198 199 200 201
    @staticmethod
    def lists2(db, T):
        """lists2 table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """
202 203
        migrate = current.globalenv["MIGRATE"]
        def_features = (None if migrate else DEF_FEATURES)
LE GAC Renaud's avatar
LE GAC Renaud committed
204 205 206 207 208 209 210

        table = db.define_table(
            "lists2",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("definition", "text"),
            Field("source", "string", length=255, notnull=True),
211 212 213

            Field("id_events",
                  "reference events",
214
                  notnull=True,
215 216
                  label="Event"),

217 218
            Field("eval", "text"),
            Field("query", "text"),
LE GAC Renaud's avatar
LE GAC Renaud committed
219 220 221 222 223 224 225 226

            Field("group_field",
                  "string",
                  length=255,
                  comment=T(TP_GROUP_FIELD_LISTS)),

            Field("sorters", "list:string", comment=TP_SORTERS),

227 228 229
            # NOTE
            #    - notnull when creating the database
            #    - null later on in order to run callback ON_CREATE_LISTS, ...
230
            Field("columns", "text",
231
                  notnull=migrate,
232
                  comment=T(TP_COLUMNS)),
LE GAC Renaud's avatar
LE GAC Renaud committed
233

234 235 236 237 238 239 240 241 242
            Field("summary_group",
                  "boolean",
                  default=True,
                  label=T("Summary row per group")),

            Field("summary_all",
                  "boolean",
                  default=True,
                  label=T("Summary row")),
LE GAC Renaud's avatar
LE GAC Renaud committed
243 244 245

            migrate="lists2.table")

246 247 248 249
        sources = PluginManager("event").event.sources.keys()
        sources.sort()
        db.lists2.source.requires = IS_IN_SET(sources)

250 251 252
        db.lists2._before_insert.append(ON_CREATE_LISTS2)
        db.lists2._before_update.append(ON_UPDATE_LISTS2)

LE GAC Renaud's avatar
LE GAC Renaud committed
253 254
        return table

255 256 257 258 259 260 261 262 263 264 265 266
    @staticmethod
    def metrics1d(db, T):
        """metrics1d table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """
267
        def_columns = (None if db._migrate else DEF_COLUMNS_METRIC1D)
268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337

        table = db.define_table(
            "metrics1d",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("conditions", "text", comment=T(TP_CONDITIONS)),

            Field("group_field",
                  "string",
                  length=255,
                  comment=T(TP_GROUP_FIELD_METRICS),
                  notnull=True),

            Field("columns",
                  "text",
                  default=def_columns,
                  comment=T(TP_COLUMNS),
                  notnull=True),

            Field("definition", "text"),
            migrate="metrics1d.table")

    @staticmethod
    def metrics2d(db, T):
        """metrics2d table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """

        table = db.define_table(
            "metrics2d",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("conditions", "text", comment=T(TP_CONDITIONS)),

            Field("group_field_x",
                  "string",
                  length=255,
                  notnull=True,
                  comment=T(TP_GROUP_FIELD_METRICS)),

            Field("group_field_y",
                  "string",
                  length=255,
                  notnull=True,
                  comment=T(TP_GROUP_FIELD_METRICS)),

            Field("metric_field_z",
                  "string",
                  length=255,
                  notnull=True,
                  comment=T(TP_GROUP_FIELD_METRICS)),

            Field("aggregation_z",
                  "string",
                  length=255,
                  notnull=True,
                  comment=T(TP_AGG)),

            Field("definition", "text"),
            migrate="metrics2d.table")

        return table

338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359
    @staticmethod
    def metrics2d2(db, T):
        """metrics2d2 table

        Args:
            db (pyDAL.DAL): database connection
            T (gluon.languages.translator): language translator

        Returns:
            pyDAL.Table

        """

        table = db.define_table(
            "metrics2d2",
            Field("name", "string", length=255, notnull=True, unique=True),
            Field("title", "string", length=255),
            Field("definition", "text"),
            Field("source", "string", length=255, notnull=True),

            Field("id_events",
                  "reference events",
360 361
                  label="Event",
                  notnull=True),
362 363 364 365 366 367

            Field("eval", "text"),
            Field("query", "text"),

            Field("group_field_x",
                  "string",
368
                  comment=T(TP_GROUP_FIELD_METRICS),
369 370 371 372 373 374
                  length=255,
                  notnull=True,
                  label="Group (x)"),

            Field("group_field_y",
                  "string",
375
                  comment=T(TP_GROUP_FIELD_METRICS),
376 377 378 379 380 381 382
                  length=255,
                  notnull=True,
                  label="Group (y)"),

            Field("metric_field_z",
                  "string",
                  comment=T(TP_GROUP_FIELD_METRICS),
383 384 385
                  label="Metric (z)",
                  length=255,
                  notnull=True),
386 387 388

            Field("aggregate_func_z",
                  "string",
389
                  comment=T(TP_AGG),
390
                  default="sum",
391
                  label="Aggregate (z)",
392
                  length=255,
393
                  notnull=True),
394

395 396
            Field("summary_x", "boolean", default=False, label="Per row"),

397 398
            Field("summary_func_x",
                  "string",
399
                  comment=T(TP_SUMMARY_X),
400 401 402 403 404 405 406 407 408 409 410
                  default="sum",
                  label="aggregate",
                  length=255),

            Field("summary_label_x",
                  "string",
                  default=T("Sum"),
                  label="label",
                  length=255),

            Field("summary_y", "boolean", default=False, label="Per columns"),
411 412 413

            Field("summary_func_y",
                  "string",
414
                  comment=T(TP_SUMMARY_Y),
415
                  default="sum",
416 417 418 419 420 421 422
                  label="aggregate",
                  length=255),

            Field("summary_label_y",
                  "string",
                  label="label",
                  length=255),
423 424 425 426 427

            migrate="metrics2d2.table")

        sources = PluginManager("event").event.sources.keys()
        sources.sort()
428

429 430
        db.metrics2d2.source.requires = IS_IN_SET(sources)

431 432 433 434
        funcs = AGGREGATE_FUNCS.keys()
        funcs.sort()

        db.metrics2d2.aggregate_func_z.requires = IS_IN_SET(funcs)
435 436
        db.metrics2d2.summary_func_x.requires = IS_IN_SET(funcs)
        db.metrics2d2.summary_func_y.requires = IS_IN_SET(funcs)
437 438 439

        return table

440 441 442 443 444 445 446 447 448 449
    @staticmethod
    def tables():
        """Tool returning the list of table names.

        Returns:
            list

        """
        lst = ["graphs",
               "lists",
450
               "lists2",
451
               "metrics1d",
452 453
               "metrics2d"
               "metrics2d2"]
454 455

        return lst