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

"""
5
import json
LE GAC Renaud's avatar
LE GAC Renaud committed
6
import matplotlib
7 8 9 10 11
import re


from gluon import current
from gluon.storage import Storage
12
from pandas import DataFrame, MultiIndex, to_datetime
LE GAC Renaud's avatar
LE GAC Renaud committed
13
from plugin_dbui import get_id, Store
14 15
from pydal.helpers.methods import smart_query
from pydal.objects import FieldVirtual
LE GAC Renaud's avatar
LE GAC Renaud committed
16
from StringIO import StringIO
17

18

19 20
MSG_NO_DATAINDEX = "The property dataIndex is required when eval is used."
MSG_NO_XTYPE = "The property xtype is missing."
21

22
REG_DBFIELD = re.compile("\w+\.\w+(?:\.\w+)?", re.UNICODE)
23
REG_PYQUERY = re.compile("[\( ]*\w+\.\w+\.\w+")
24 25 26
REG_SINGLE_DBFIELD = re.compile("^ *\w+\.\w+(\.\w+)? *$", re.UNICODE)


27 28
def do_title(config, selector):
    """Build the report title.
29

LE GAC Renaud's avatar
LE GAC Renaud committed
30 31
    Args:
        config (gluon.dal.Row): the list configuration parameter.
32
        selector (EvtSelector): selector handling period of time.
LE GAC Renaud's avatar
LE GAC Renaud committed
33 34 35 36

    Returns:
        str:

37
    """
38
    db = current.globalenv["db"]
39
    T = current.T
40

41 42
    # from the configuration
    title = (config.title if config.title else config.name)
43

44 45 46 47
    # add meta data
    metadata = []
    if selector.id_teams:
        metadata.append(db.teams[selector.id_teams].team)
48

49 50
    if selector.id_projects:
        metadata.append(db.projects[selector.id_projects].project)
51

52 53
    if selector.category:
        metadata.append(selector.category)
54

55 56 57 58 59 60 61
    if selector.id_people_categories:
        code = db.people_categories[selector.id_people_categories].code
        metadata.append(code)

    # add period
    if selector.year_start and not selector.year_end:
        metadata.append(str(selector.year_start))
62

63 64 65
    elif selector.year_start and selector.year_end:
        years = (str(selector.year_start), str(selector.year_end))
        metadata.append(T("from %s to %s") % years)
66

67
    return "%s: %s" % (title, " / ".join(metadata))
68 69


70
def get_value(row, tablename, fieldname, keyname="", **kwargs):
LE GAC Renaud's avatar
LE GAC Renaud committed
71 72 73 74 75
    """Helper function returning the value of a database field.

    The method is designed to handle standard and JSON-type database field.
    The field is identified by its ``tablename``, ``fieldname`` and
    ``keyname``.
76

LE GAC Renaud's avatar
LE GAC Renaud committed
77 78 79 80 81
    Args:
        row (gluon.dal.Row): one row of the tablename table.
        tablename (str): name of a database table.
        fieldname (str): name of database field.
        keyname (str): key for the JSON type field.
82

LE GAC Renaud's avatar
LE GAC Renaud committed
83 84
    Returns:
        * ``row[tablename][fieldname]`` or ``row[fieldname]``
85
          when tablename and fieldname are defined
LE GAC Renaud's avatar
LE GAC Renaud committed
86 87 88
        * ``row[tablename][fieldname][keyname]`` for JSON type field
        * ``kwargs[tablename]`` when fieldname and keyname are not defined
        * ``None`` when the field address does not exist in the row
89

90
    """
LE GAC Renaud's avatar
LE GAC Renaud committed
91
    undefined = None
92

93 94
    # force value
    if tablename and (not fieldname) and (tablename in kwargs):
95
        return kwargs[tablename]
96 97

    # field is addressed in the row by tablename and by fieldname
98
    if tablename in row:
99
        value = row[tablename][fieldname]
100

101 102 103
    # field is addressed in the row by its fieldname only
    elif fieldname in row:
        value = row[fieldname]
104

105 106 107
    else:
        return undefined

LE GAC Renaud's avatar
LE GAC Renaud committed
108 109
    # deal with the keyname
    # it has been design for JSON-type field containing a dictionary
110 111
    if not keyname:
        return value
112

113 114
    elif keyname and keyname in value:
        return value[keyname]
115

116
    return undefined
117 118 119


def split_dbfield(value):
LE GAC Renaud's avatar
LE GAC Renaud committed
120 121 122 123 124 125
    """Helper function to decode database field name as 3-elements tuple.

    The name of a database field is encoded as ``table.field`` or
    ``table.field.key``. The latter syntax is used for JSON type field.
    The function decodes as a 3-elements tuple (``tablename``,
    ``fieldname``, ``keyname``).
126

LE GAC Renaud's avatar
LE GAC Renaud committed
127 128
    Args:
        value (string):
LE GAC Renaud's avatar
LE GAC Renaud committed
129 130
            the name of the database field, either
            ``tablename.fieldname`` or ``tablename.fieldname.key``
131

LE GAC Renaud's avatar
LE GAC Renaud committed
132 133 134
    Returns:
        tuple: ``(tablename, fieldname, keyname)`` where the ``keyname``
            is either a string or an empty string.
135

136
    """
137
    li = value.split(".")
138
    if len(li) == 1:
139
        li.extend(["", ""])
140
    elif len(li) == 2:
141
        li.append("")
142 143 144
    return tuple(li)


145 146
class ReportException(BaseException):
    pass
147 148 149


class BaseReport(object):
LE GAC Renaud's avatar
LE GAC Renaud committed
150
    """Base class to build list, metric or graph reports.
151

LE GAC Renaud's avatar
LE GAC Renaud committed
152
    Args:
LE GAC Renaud's avatar
LE GAC Renaud committed
153
        config (gluon.dal.Row): the configuration parameter for the report.
154
        selector (EvtSelector): the selector handling user criteria.
155

156
    """
157
    def __init__(self, config, selector):
158

159
        db = current.globalenv["db"]
160

161 162 163 164 165
        self.db = db
        self.df = None
        self.config = config
        self.rows = None
        self.selector = selector
166

167
        # apply the condition criteria used to filter the history records
168 169
        # condition can be written as a smart query: history.id_events == 7
        # or like a python query: db.events.event == "People"
LE GAC Renaud's avatar
LE GAC Renaud committed
170
        if "conditions" in config:
171 172 173 174 175 176 177 178 179 180 181 182 183 184 185
            condition = config.conditions

            # minimal protection to avoid injection flow
            # the beginning of the python query should be like:
            #    db.table.field
            #    (db.table.field
            #    ((db.table.field
            #    ( ( db.table.field
            #
            if REG_PYQUERY.match(condition):
                q_conditions = eval(condition, None, {"db": db})

            else:
                q_conditions = smart_query(db.history, config.conditions)

186
            selector.append_query(q_conditions)
187

188 189 190 191
    def _do_data(self, maps):
        """Build a temporarily list with the raw data for each series.
        This method handle the "year" database field.

LE GAC Renaud's avatar
LE GAC Renaud committed
192 193 194 195
        Args:
            maps (list):
                the database field map (tablename, fieldname, keyname).
                One per series.
196

LE GAC Renaud's avatar
LE GAC Renaud committed
197 198
        Returns:
            list:
199

200 201
        """
        data = []
202

203 204 205
        db = self.db
        selector = self.selector

206 207 208 209 210
        query = selector.query

        # limit the list of database fields to speed up processing:
        # - keep those required by the user
        # - remove virtual field
211
        # - add standard fields require to compute virtual fields
212 213
        dbfields = [db[el[0]][el[1]] for el in maps if el[1]]
        dbfields = [el for el in dbfields if not isinstance(el, FieldVirtual)]
214 215

        dbfields.extend([db.history.end_date,
216
                         db.history.percentage,
217
                         db.history.start_date,
218
                         db.people.birth_date])
219

220
        # the year axis is on
221
        # scan the database and compute virtual field on the year basis
222 223 224 225 226
        if self._is_year(maps):

            # get the year range
            for year in selector.get_years():
                selector.set_year(year)
227

228
                for row in db(query(db.history)).select(*dbfields):
229
                    values = [get_value(row, *elt, year=year) for elt in maps]
230
                    data.append(values)
231

232 233
        # standard scan
        else:
234
            for row in db(query(db.history)).select(*dbfields):
235
                values = [get_value(row, *elt) for elt in maps]
236
                data.append(values)
237

238
        return data
239

240 241
    def _is_year(self, maps):
        """
LE GAC Renaud's avatar
LE GAC Renaud committed
242 243 244 245
        Args:
            maps (list):
                the database field map (tablename, fieldname, keyname).
                One per series.
246

LE GAC Renaud's avatar
LE GAC Renaud committed
247 248
        Returns:
            bool: ``True`` is the pseudo field ``year`` is in maps
249

250
        """
251
        li = [True for el in maps if el[0] == "year"]
252 253
        return (True if li else False)

254
    def to_df(self):
255
        """Return the pandas DataFrame.
256

LE GAC Renaud's avatar
LE GAC Renaud committed
257 258
        Returns:
            pandas.DataFrame:
259

260 261
        """
        return self.df
262 263


LE GAC Renaud's avatar
LE GAC Renaud committed
264
class Graph(BaseReport):
LE GAC Renaud's avatar
LE GAC Renaud committed
265 266 267
    """Build a report as a graph.

    Any data encapsulated in list, 1-dim or 2-dim metrics
268 269
    can be displayed as a graph. The rendering is performed by
    the matplotlib library. Therefore, many representations of
LE GAC Renaud's avatar
LE GAC Renaud committed
270 271
    the data are possible: plot, histogram, bar charts, error charts,
    scater plots, *etc*.
LE GAC Renaud's avatar
LE GAC Renaud committed
272

LE GAC Renaud's avatar
LE GAC Renaud committed
273
    Args:
LE GAC Renaud's avatar
LE GAC Renaud committed
274 275 276
        config (gluon.dal.Row):
            the configuration parameter for the graph.

277
        selector (EvtSelector):
LE GAC Renaud's avatar
LE GAC Renaud committed
278 279
            selector handling period of time.

LE GAC Renaud's avatar
LE GAC Renaud committed
280 281 282
        backend (str):
            the name of the matplotlib backend uses to produce figure.

LE GAC Renaud's avatar
LE GAC Renaud committed
283 284 285 286 287 288 289 290 291
    """
    def __init__(self, config, selector, backend="Agg"):

        BaseReport.__init__(self, config, selector)

        # set the matplotlib back end
        #
        # NOTE: the X11 back end is not needed on the server side. In addition
        # Tkinter crash with the message "out of stack space" when the 2nd plot
292
        # is generated.
LE GAC Renaud's avatar
LE GAC Renaud committed
293 294 295
        # The documentation recommend to limit the matplotlib back end to Agg
        # which is tuned to render high quality PNG image. But, it is also
        # design to render PDF and SVG image without the X11 interface.
296
        #
LE GAC Renaud's avatar
LE GAC Renaud committed
297
        matplotlib.use(backend)
298

LE GAC Renaud's avatar
LE GAC Renaud committed
299 300 301 302 303
        # split the plot configuration in two parts:
        # 1) keywords for the DataFrame.plot method
        # 2) steering parameter for this class
        config.plot = json.loads(config.plot)
        config.steer = Storage()
304

305
        for k in ("index", "transpose", "xlabel", "ylabel"):
LE GAC Renaud's avatar
LE GAC Renaud committed
306 307
            v = config.plot.pop(k, None)
            config.steer[k] = v
308

LE GAC Renaud's avatar
LE GAC Renaud committed
309 310 311 312 313 314 315 316
        # instantiate the DataFrame for the report
        db = self.db

        report_type = config.report_type
        report_name = config.report_name
        report_id = get_id(db[report_type], name=report_name)
        report_config = db[report_type][report_id]

317
        if report_type == "lists":
LE GAC Renaud's avatar
LE GAC Renaud committed
318
            report = List(report_config, selector)
319

LE GAC Renaud's avatar
LE GAC Renaud committed
320 321
        elif report_type == "metrics1d":
            report = Metric1D(report_config, selector)
322

LE GAC Renaud's avatar
LE GAC Renaud committed
323 324 325 326
        elif report_type == "metrics2d":
            report = Metric2D(report_config, selector)

        self.df = report.to_df()
327

LE GAC Renaud's avatar
LE GAC Renaud committed
328 329 330 331 332
        # build the graph from the DataFrame
        self._do_graph()
        self._do_labels()
        self._do_legend()
        self._do_tick()
333

LE GAC Renaud's avatar
LE GAC Renaud committed
334
    def _do_graph(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
335
        """Build the graph from the ``DataFrame`` structure.
336

LE GAC Renaud's avatar
LE GAC Renaud committed
337 338 339 340
        """
        config = self.config
        df = self.df
        plot, steer = config.plot, config.steer
341

LE GAC Renaud's avatar
LE GAC Renaud committed
342 343 344
        # transpose
        if steer.transpose:
            df = df.T
345

LE GAC Renaud's avatar
LE GAC Renaud committed
346 347
        # generate the plot using a specific set of columns
        if steer.index and len(steer.index) <= len(df.columns):
348
            ax = df.ix[:, steer.index].plot(**plot)
349

LE GAC Renaud's avatar
LE GAC Renaud committed
350 351
        # generate the plot using all columns
        else:
352
            ax = df.ix[:, :].plot(**plot)
353

LE GAC Renaud's avatar
LE GAC Renaud committed
354 355 356 357 358
        # persistence
        self.ax = ax

    def _do_labels(self):
        """Deal with axes label.
359

LE GAC Renaud's avatar
LE GAC Renaud committed
360 361 362
        """
        ax = self.ax
        steer = self.config.steer
363

LE GAC Renaud's avatar
LE GAC Renaud committed
364
        if steer.xlabel:
365
            ax.set_xlabel(steer.xlabel, x=1, horizontalalignment="right")
366

LE GAC Renaud's avatar
LE GAC Renaud committed
367
        if steer.ylabel:
368
            ax.set_ylabel(steer.ylabel, y=1, horizontalalignment="right")
LE GAC Renaud's avatar
LE GAC Renaud committed
369 370 371

    def _do_legend(self):
        """Deal with legend.
372

LE GAC Renaud's avatar
LE GAC Renaud committed
373 374 375 376 377 378
        """
        ax = self.ax

        if ax.get_legend():
            box = ax.get_position()
            ax.set_position([box.x0, box.y0, box.width, box.height * 0.9])
379
            ax.legend(loc="lower right",
380
                      bbox_to_anchor=(1.01, 1.),
LE GAC Renaud's avatar
LE GAC Renaud committed
381 382
                      fontsize=10,
                      ncol=3)
383

LE GAC Renaud's avatar
LE GAC Renaud committed
384 385
    def _do_tick(self):
        """Polish the tick mark
386

LE GAC Renaud's avatar
LE GAC Renaud committed
387 388
        """
        ax = self.ax
389

LE GAC Renaud's avatar
LE GAC Renaud committed
390
        ax.minorticks_on()
391 392
        ax.tick_params(which="major", length=8)
        ax.tick_params(which="minor", length=4)
LE GAC Renaud's avatar
LE GAC Renaud committed
393

394
    def _savefig(self, fmt):
395
        """Save the figure as a string.
396

LE GAC Renaud's avatar
LE GAC Renaud committed
397 398
        Args:
            fmt (str): possible values are pdf, png and svg.
399

400 401
        """
        fig = self.ax.get_figure()
402

403
        fi = StringIO()
404
        fig.savefig(fi, format=fmt)
405 406
        data = fi.getvalue()
        fi.close()
407

408 409
        fig.clear()
        matplotlib.pyplot.close(fig)
410

411
        return data
412

LE GAC Renaud's avatar
LE GAC Renaud committed
413
    def to_pdf(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
414 415
        """Encode the graph using the PDF format

LE GAC Renaud's avatar
LE GAC Renaud committed
416
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
417
            str:
418

LE GAC Renaud's avatar
LE GAC Renaud committed
419
        """
420
        return self._savefig("pdf")
421

LE GAC Renaud's avatar
LE GAC Renaud committed
422
    def to_png(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
423 424
        """Encode the graph using the PNG format.

LE GAC Renaud's avatar
LE GAC Renaud committed
425
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
426
            str:
427

LE GAC Renaud's avatar
LE GAC Renaud committed
428
        """
429
        return self._savefig("png")
430

LE GAC Renaud's avatar
LE GAC Renaud committed
431
    def to_svg(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
432 433
        """Encode the graph using the SVG format.

LE GAC Renaud's avatar
LE GAC Renaud committed
434
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
435
            str:
436

LE GAC Renaud's avatar
LE GAC Renaud committed
437
        """
438
        return self._savefig("svg")
LE GAC Renaud's avatar
LE GAC Renaud committed
439 440


441
class List(BaseReport):
LE GAC Renaud's avatar
LE GAC Renaud committed
442 443 444
    """Build a report as a list.

    A list is a table in which each column contains the values of
445 446 447 448
    one database field. The rows can be grouped per value of a given column.
    Summary information can be computed for each group as well as for
    the whole table.

449
    The list is displayed as the ``Dbui.grid.Panel`` widget.
450
    The configuration of the list columns is the configuration of
451
    the ``Dbui.grid.Panel`` object.
452

453
    More technically, this class interfaces the database and the
454
    ``Dbui.grid.Panel`` thought the underlying ``Ext.data.Store``.
LE GAC Renaud's avatar
LE GAC Renaud committed
455
    Its configuration is returned by the method *to_store*.
LE GAC Renaud's avatar
LE GAC Renaud committed
456 457

    Args:
LE GAC Renaud's avatar
LE GAC Renaud committed
458
        config (gluon.dal.Row): the configuration parameter for the list.
459
        selector (EvtSelector): selector handling period of time.
460

461
    """
462 463 464
    def __init__(self, config, selector):

        BaseReport.__init__(self, config, selector)
465

466
        # decode column configuration
467
        columns = [Storage(el) for el in json.loads(config.columns)]
468

469
        # check column configuration
470
        # add database field map (tablename, fieldname, keyname)
471
        # add the dataIndex (DataFrame, Ext.data.Store, Ext.grid.Panel)
472
        map(self._check_column, columns)
473

474
        # columns are persistent
475
        self._columns = columns
476

477 478
        # instantiate and fill the DataFrame
        self._do_metric()
479

480
    def _cast_type(self, column, dbfield, xtype):
481 482
        """Cast the type of a dataframe column to the database field type
        or to the grid column xtype.
483

LE GAC Renaud's avatar
LE GAC Renaud committed
484
        The type of the column determine by the pandas might be wrong.
485
        This append when events are merged with different user block.
486

487 488
        This is fine in most of the case but not with computed column.
        In that case the eval computation crashed.
489 490

        This method avoid this problem. It also convert properly
491
        datetime column allowing computation with them.
492

LE GAC Renaud's avatar
LE GAC Renaud committed
493 494 495
        Args:
            column (str):
                the index of the column in the DataFrame
496

LE GAC Renaud's avatar
LE GAC Renaud committed
497 498 499
            dbfield (tuple):
                address of the database field encoded as
                (tablename, fieldname, keyname).
500

LE GAC Renaud's avatar
LE GAC Renaud committed
501 502 503 504
            xtype (str):
                the xtype of the grid column.
                Possible values are ``booleancolumn``, ``datecolumn``,
                ``gridcolumn`` and ``numbercolumn``.
505

LE GAC Renaud's avatar
LE GAC Renaud committed
506 507
        """
        df = self.df
508
        tablename, fieldname = dbfield[0:2]
LE GAC Renaud's avatar
LE GAC Renaud committed
509

510
        # the dtype of column containing a mixture of type is object.
511
        if (tablename == "year") or (df[column].dtype != "object"):
512
            return
513

514 515 516
        dbtype = self.db[tablename][fieldname].type

        # the dtype for column containing string is also object
517
        if dbtype in ("string", "text"):
518
            return
519

520 521
        elif dbtype == "boolean":
            df[column] = df[column].astype("bool")
522

523
        elif dbtype in ("date", "datetime", "time"):
524
            df[column] = to_datetime(df[column])
525

526 527
        elif dbtype in ("double", "integer"):
            df[column] = df[column].astype("float64")
528

529 530 531
        # database field containing JSON-type dictionary
        # The type of the key is defined in the event model but it is
        # not accessible at this stage. Instead we use the grid column xtype.
532
        elif dbtype == "json":
533

534
            if xtype == "gridcolumn":
535
                pass
LE GAC Renaud's avatar
LE GAC Renaud committed
536

537 538
            elif xtype == "booleancolumn":
                df[column] = df[column].astype("bool")
539

540
            elif xtype == "datecolumn":
541
                df[column] = to_datetime(df[column])
542

543 544
            elif xtype == "numbercolumn":
                df[column] = df[column].astype("float64")
545

546 547
    def _check_column(self, column):
        """Check column configuration:
548

549 550
            - Raise an exception if xtype is not defined
            - Raise an exception when eval is defined but not the dataIndex
551
            - Add the database field map
552
            - Add the dataIndex if not defined
553

LE GAC Renaud's avatar
LE GAC Renaud committed
554 555
        Args:
            column (gluon.storage.Storage):
556

557 558 559 560 561 562
        """
        T = current.T

        xtype = column.xtype
        if not xtype:
            raise ReportException(T(MSG_NO_XTYPE))
563

564 565 566
        if column.eval and not column.dataIndex:
            raise ReportException(T(MSG_NO_DATAINDEX))

567
        dbfield = column.dbfield
568 569 570 571
        if dbfield:
            column.map = split_dbfield(dbfield)

        if not (column.dataIndex or xtype == "rownumberer"):
572
            column.dataIndex = column.dbfield.replace(".", "")
573

574 575
    def _do_metric(self):
        """Interface the database with the DataFrame structure.
LE GAC Renaud's avatar
LE GAC Renaud committed
576
        This method handle the ``year`` database field.
577

578 579

        """
580
        columns = self._columns
581

LE GAC Renaud's avatar
LE GAC Renaud committed
582 583 584 585 586 587 588
        # extract columns associated to database fields
        maps, index, xtypes = [], [], []
        for column in columns:
            if column.dbfield:
                maps.append(column.map)
                index.append(column.dataIndex)
                xtypes.append(column.xtype)
589

590 591
        # extract data from the database
        data = self._do_data(maps)
592

593
        # protection
594
        if not data:
595
            self.df = DataFrame(columns=index)
596
            return
597

598
        # fill the DataFrame
599
        df = DataFrame(data, columns=index)
600 601

        # make the data frame persistent
LE GAC Renaud's avatar
LE GAC Renaud committed
602 603
        self.df = df

604 605
        # cast dataframe column type to database type or grid column xtype
        map(self._cast_type, index, maps, xtypes)
606

LE GAC Renaud's avatar
LE GAC Renaud committed
607
        # add computed columns
608 609 610 611 612 613 614 615
        for el in columns:
            if el.eval:
                df[el.dataIndex] = df.eval(el.eval)

        # re-order the column to follow user requirement
        # skip rownumberer column (None index)
        index = [el.dataIndex for el in columns if el.dataIndex]
        df = df[index]
616

617
    def _set_store_data(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
618
        """Generate the ``Ext.data.Store.data`` property.
619
        It is a list of dictionaries. Each of them contains the data
LE GAC Renaud's avatar
LE GAC Renaud committed
620 621
        for one row. One key, value pair for each ``Ext.data.Field`` where
        the key is the name of the ``Ext.data.Field``.
622 623

        """
624 625
        # extract the list of records as a JSON-string
        # at this stage date/time are converted as an ISO8601 string
626
        data = self.df.to_json(orient="records", date_format="iso")
627

628 629
        # convert the JSON-string into a list
        self._store.data = json.loads(data)
630

631
    def _set_store_fields(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
632 633
        """Generate the ``Ext.data.Store.fields`` property.
        It is a list of ``Ext.data.Field`` configuration.
634

LE GAC Renaud's avatar
LE GAC Renaud committed
635 636 637 638 639
        Note:
            The name of the ``Ext.data.Field`` is derived from the
            address of the database field. The former can not contains dot.
            Therefore, it is obtained by removing dot in the database
            field address.
640

641 642
        """
        db = self.db
643
        columns = self._columns
644
        store = self._store
645

646 647
        # convert the columns into the configuration of an Ext.data.Field
        for el in columns:
648

649 650 651
            # protection against rownumberer column
            if not el.dataIndex:
                continue
652

653 654
            tablename, fieldname, keyname = el.map

655
            cfg = Storage(name=el.dataIndex)
656

657
            # the pseudo field year
658 659
            if el.dbfield == "year":
                cfg.type = "int"
660

661 662
            # the computed column
            elif el.eval:
663
                cfg.type = "float"
664

665 666 667 668
            # json type database field
            elif keyname:
                xtype = el.xtype

669 670
                if xtype == "gridcolumn":
                    cfg.type = "string"
671

672 673
                elif xtype == "booleancolumn":
                    cfg.type = "boolean"
674

675 676
                elif xtype == "datecolumn":
                    cfg.type = "date"
677

678 679
                elif xtype == "numbercolumn":
                    cfg.type = "float"
680

681 682 683
            # standard database field, extract the type from the database field
            else:
                dbfield = db[tablename][fieldname]
684

685
                cfg.type = dbfield.type
686 687
                if dbfield.type in ("blob", "string", "text", "json"):
                    cfg.type = "string"
688

689 690
                elif dbfield.type == "boolean":
                    cfg.type = "boolean"
691

692 693 694
                elif dbfield.type in ("date", "datetime", "time"):
                    cfg.type = "date"
                    cfg.dateFormat = "c"
695

696 697
                elif dbfield.type == "double":
                    cfg.type = "float"
698

699 700
                elif dbfield.type == "integer":
                    cfg.type = "int"
701

702
            store.fields.append(cfg)
703

704
    def to_grid(self):
705
        """Build the configuration for the ``Dbui.grid.Panel``.
706

LE GAC Renaud's avatar
LE GAC Renaud committed
707
        Returns:
708 709 710
            gluon.storage.Storage:
                the keys are ``columns`` and ``features`` and the
                corresponding values are list of dictionary.
711

712 713
        """
        config = self.config
714
        grid = Storage(columns=[], features=[])
715 716

        # column from the configuration
717
        # remove non Ext JS property
718
        for cfg in self._columns:
719

720
            for key in ("dbfield", "eval", "map"):
721 722
                if key in cfg:
                    del cfg[key]