report_objects.py 36.4 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
16
from selector import EvtSelector
LE GAC Renaud's avatar
LE GAC Renaud committed
17
from StringIO import StringIO
18

19

20
MSG_NO_DATAINDEX = "The property dataIndex is required when eval is used."
21
MSG_NO_EVT_ID = "Identifier of the event is not defined."
22
MSG_NO_XTYPE = "The property xtype is missing."
23

24
REG_DBFIELD = re.compile("\w+\.\w+(?:\.\w+)?", re.UNICODE)
25
REG_EVT_ID = re.compile("history\.id_events *={1,2} *(\d+)")
26
REG_PYQUERY = re.compile("[\( ]*\w+\.\w+\.\w+")
27 28 29
REG_SINGLE_DBFIELD = re.compile("^ *\w+\.\w+(\.\w+)? *$", re.UNICODE)


30
def do_title(report):
31
    """Build the report title.
32

LE GAC Renaud's avatar
LE GAC Renaud committed
33
    Args:
34
        report (BaseReport): the report
LE GAC Renaud's avatar
LE GAC Renaud committed
35 36 37 38

    Returns:
        str:

39
    """
40 41 42 43
    db = report.db

    config = report.config
    selector = report.selector
44
    T = current.T
45

46 47
    # from the configuration
    title = (config.title if config.title else config.name)
48

49 50 51 52
    # add meta data
    metadata = []
    if selector.id_teams:
        metadata.append(db.teams[selector.id_teams].team)
53

54 55
    if selector.id_projects:
        metadata.append(db.projects[selector.id_projects].project)
56

57 58
    if selector.category:
        metadata.append(selector.category)
59

60 61 62 63 64 65 66
    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))
67

68 69
    elif selector.year_start and selector.year_end:
        years = (str(selector.year_start), str(selector.year_end))
70
        metadata.append(T("from %s to %s", lazy=False) % years)
71

72
    return "%s: %s" % (title, " / ".join(metadata))
73 74


75
def get_value(row, tablename, fieldname, keyname="", **kwargs):
LE GAC Renaud's avatar
LE GAC Renaud committed
76 77 78 79 80
    """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``.
81

LE GAC Renaud's avatar
LE GAC Renaud committed
82 83 84 85 86
    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.
87

LE GAC Renaud's avatar
LE GAC Renaud committed
88 89
    Returns:
        * ``row[tablename][fieldname]`` or ``row[fieldname]``
90
          when tablename and fieldname are defined
LE GAC Renaud's avatar
LE GAC Renaud committed
91 92 93
        * ``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
94

95
    """
LE GAC Renaud's avatar
LE GAC Renaud committed
96
    undefined = None
97

98 99
    # force value
    if tablename and (not fieldname) and (tablename in kwargs):
100
        return kwargs[tablename]
101 102

    # field is addressed in the row by tablename and by fieldname
103
    if tablename in row:
104
        value = row[tablename][fieldname]
105

106 107 108
    # field is addressed in the row by its fieldname only
    elif fieldname in row:
        value = row[fieldname]
109

110 111 112
    else:
        return undefined

LE GAC Renaud's avatar
LE GAC Renaud committed
113 114
    # deal with the keyname
    # it has been design for JSON-type field containing a dictionary
115 116
    if not keyname:
        return value
117

118 119
    elif keyname and keyname in value:
        return value[keyname]
120

121
    return undefined
122 123 124


def split_dbfield(value):
LE GAC Renaud's avatar
LE GAC Renaud committed
125 126 127 128 129 130
    """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``).
131

LE GAC Renaud's avatar
LE GAC Renaud committed
132 133
    Args:
        value (string):
LE GAC Renaud's avatar
LE GAC Renaud committed
134 135
            the name of the database field, either
            ``tablename.fieldname`` or ``tablename.fieldname.key``
136

LE GAC Renaud's avatar
LE GAC Renaud committed
137 138 139
    Returns:
        tuple: ``(tablename, fieldname, keyname)`` where the ``keyname``
            is either a string or an empty string.
140

141
    """
142
    li = value.split(".")
143
    if len(li) == 1:
144
        li.extend(["", ""])
145
    elif len(li) == 2:
146
        li.append("")
147 148 149
    return tuple(li)


150 151
class ReportException(BaseException):
    pass
152 153 154


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

LE GAC Renaud's avatar
LE GAC Renaud committed
157
    Args:
158 159
        table (gluon.dal.Table): table containing configurations for reports.
        id_report (int): identifier of the report in the table.
160

161
    """
162
    def __init__(self, table, id_report):
163

164
        db = table._db
165

166 167 168
        self.db = db
        self.df = None
        self.rows = None
169 170 171 172 173 174 175 176 177 178 179 180 181 182 183

        # Extract the configuration for the report configuration
        config = table[id_report]

        # Extract the event identifier located in the condition field
        conditions = config.conditions
        mtch = REG_EVT_ID.search(conditions)
        if mtch is None:
            raise ReportException(current.T(MSG_NO_EVT_ID))

        id_event = int(mtch.group(1))

        # Instantiate the selector
        virtdb = current.globalenv["virtdb"]
        selector = EvtSelector(virtdb.selector, id_event)
184

185
        # apply the condition criteria used to filter the history records
186 187 188
        # condition can be written as a smart query: history.id_events == 7
        # or like a python query: db.events.event == "People"

189 190 191 192 193 194 195 196 197
        # 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(conditions):
            q_conditions = eval(conditions, None, {"db": db})
198

199 200 201 202 203 204 205 206
        else:
            q_conditions = smart_query(db.history, conditions)

        selector.append_query(q_conditions)

        # keep track of configuration and selector
        self.config = config
        self.selector = selector
207

208 209 210 211
    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
212 213 214 215
        Args:
            maps (list):
                the database field map (tablename, fieldname, keyname).
                One per series.
216

LE GAC Renaud's avatar
LE GAC Renaud committed
217 218
        Returns:
            list:
219

220 221
        """
        data = []
222

223 224 225
        db = self.db
        selector = self.selector

226 227 228 229 230
        query = selector.query

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

        dbfields.extend([db.history.end_date,
236 237 238
                         db.history.id_domains,
                         db.history.id_people,
                         db.history.id_teams,
239
                         db.history.percentage,
240
                         db.history.start_date,
241
                         db.people.birth_date])
242

243
        # the year axis is on
244
        # scan the database and compute virtual field on the year basis
245 246 247 248 249
        if self._is_year(maps):

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

251
                for row in db(query(db.history)).select(*dbfields):
252
                    values = [get_value(row, *elt, year=year) for elt in maps]
253
                    data.append(values)
254

255 256
        # standard scan
        else:
257
            for row in db(query(db.history)).select(*dbfields):
258
                values = [get_value(row, *elt) for elt in maps]
259
                data.append(values)
260

261
        return data
262

263 264
    def _is_year(self, maps):
        """
LE GAC Renaud's avatar
LE GAC Renaud committed
265 266 267 268
        Args:
            maps (list):
                the database field map (tablename, fieldname, keyname).
                One per series.
269

LE GAC Renaud's avatar
LE GAC Renaud committed
270 271
        Returns:
            bool: ``True`` is the pseudo field ``year`` is in maps
272

273
        """
274
        li = [True for el in maps if el[0] == "year"]
275 276
        return (True if li else False)

277
    def to_df(self):
278
        """Return the pandas DataFrame.
279

LE GAC Renaud's avatar
LE GAC Renaud committed
280 281
        Returns:
            pandas.DataFrame:
282

283 284
        """
        return self.df
285 286


LE GAC Renaud's avatar
LE GAC Renaud committed
287
class Graph(BaseReport):
LE GAC Renaud's avatar
LE GAC Renaud committed
288 289 290
    """Build a report as a graph.

    Any data encapsulated in list, 1-dim or 2-dim metrics
291 292
    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
293 294
    the data are possible: plot, histogram, bar charts, error charts,
    scater plots, *etc*.
LE GAC Renaud's avatar
LE GAC Renaud committed
295

LE GAC Renaud's avatar
LE GAC Renaud committed
296
    Args:
297 298
        table (gluon.dal.Table):
            table containing configurations for reports.
LE GAC Renaud's avatar
LE GAC Renaud committed
299

300 301
        id_report (int):
            identifier of the report in the table.
LE GAC Renaud's avatar
LE GAC Renaud committed
302

LE GAC Renaud's avatar
LE GAC Renaud committed
303 304 305
        backend (str):
            the name of the matplotlib backend uses to produce figure.

LE GAC Renaud's avatar
LE GAC Renaud committed
306
    """
307
    def __init__(self, table, id_report, backend="Agg"):
LE GAC Renaud's avatar
LE GAC Renaud committed
308

309 310 311 312 313
        self.db = table._db

        self.config = config = table[id_report]
        self.df = None
        self.rows = None
LE GAC Renaud's avatar
LE GAC Renaud committed
314 315 316 317 318

        # 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
319
        # is generated.
LE GAC Renaud's avatar
LE GAC Renaud committed
320 321 322
        # 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.
323
        #
LE GAC Renaud's avatar
LE GAC Renaud committed
324
        matplotlib.use(backend)
325

LE GAC Renaud's avatar
LE GAC Renaud committed
326 327 328 329 330
        # 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()
331

332
        for k in ("index", "transpose", "xlabel", "ylabel"):
LE GAC Renaud's avatar
LE GAC Renaud committed
333 334
            v = config.plot.pop(k, None)
            config.steer[k] = v
335

LE GAC Renaud's avatar
LE GAC Renaud committed
336 337 338 339 340 341 342
        # 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)

343
        if report_type == "lists":
344
            report = List(db.lists, report_id)
345

LE GAC Renaud's avatar
LE GAC Renaud committed
346
        elif report_type == "metrics1d":
347
            report = Metric1D(db.metrics1d, report_id)
348

LE GAC Renaud's avatar
LE GAC Renaud committed
349
        elif report_type == "metrics2d":
350
            report = Metric2D(db.metrics2d, report_id)
LE GAC Renaud's avatar
LE GAC Renaud committed
351 352

        self.df = report.to_df()
353
        self.selector = report.selector
354

LE GAC Renaud's avatar
LE GAC Renaud committed
355 356 357 358 359
        # build the graph from the DataFrame
        self._do_graph()
        self._do_labels()
        self._do_legend()
        self._do_tick()
360

LE GAC Renaud's avatar
LE GAC Renaud committed
361
    def _do_graph(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
362
        """Build the graph from the ``DataFrame`` structure.
363

LE GAC Renaud's avatar
LE GAC Renaud committed
364 365 366 367
        """
        config = self.config
        df = self.df
        plot, steer = config.plot, config.steer
368

LE GAC Renaud's avatar
LE GAC Renaud committed
369 370 371
        # transpose
        if steer.transpose:
            df = df.T
372

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

LE GAC Renaud's avatar
LE GAC Renaud committed
377 378
        # generate the plot using all columns
        else:
379
            ax = df.ix[:, :].plot(**plot)
380

LE GAC Renaud's avatar
LE GAC Renaud committed
381 382 383 384 385
        # persistence
        self.ax = ax

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

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

LE GAC Renaud's avatar
LE GAC Renaud committed
391
        if steer.xlabel:
392
            ax.set_xlabel(steer.xlabel, x=1, horizontalalignment="right")
393

LE GAC Renaud's avatar
LE GAC Renaud committed
394
        if steer.ylabel:
395
            ax.set_ylabel(steer.ylabel, y=1, horizontalalignment="right")
LE GAC Renaud's avatar
LE GAC Renaud committed
396 397 398

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

LE GAC Renaud's avatar
LE GAC Renaud committed
400 401 402 403 404 405
        """
        ax = self.ax

        if ax.get_legend():
            box = ax.get_position()
            ax.set_position([box.x0, box.y0, box.width, box.height * 0.9])
406
            ax.legend(loc="lower right",
407
                      bbox_to_anchor=(1.01, 1.),
LE GAC Renaud's avatar
LE GAC Renaud committed
408 409
                      fontsize=10,
                      ncol=3)
410

LE GAC Renaud's avatar
LE GAC Renaud committed
411 412
    def _do_tick(self):
        """Polish the tick mark
413

LE GAC Renaud's avatar
LE GAC Renaud committed
414 415
        """
        ax = self.ax
416

LE GAC Renaud's avatar
LE GAC Renaud committed
417
        ax.minorticks_on()
418 419
        ax.tick_params(which="major", length=8)
        ax.tick_params(which="minor", length=4)
LE GAC Renaud's avatar
LE GAC Renaud committed
420

421
    def _savefig(self, fmt):
422
        """Save the figure as a string.
423

LE GAC Renaud's avatar
LE GAC Renaud committed
424 425
        Args:
            fmt (str): possible values are pdf, png and svg.
426

427 428
        """
        fig = self.ax.get_figure()
429

430
        fi = StringIO()
431
        fig.savefig(fi, format=fmt)
432 433
        data = fi.getvalue()
        fi.close()
434

435 436
        fig.clear()
        matplotlib.pyplot.close(fig)
437

438
        return data
439

LE GAC Renaud's avatar
LE GAC Renaud committed
440
    def to_pdf(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
441 442
        """Encode the graph using the PDF format

LE GAC Renaud's avatar
LE GAC Renaud committed
443
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
444
            str:
445

LE GAC Renaud's avatar
LE GAC Renaud committed
446
        """
447
        return self._savefig("pdf")
448

LE GAC Renaud's avatar
LE GAC Renaud committed
449
    def to_png(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
450 451
        """Encode the graph using the PNG format.

LE GAC Renaud's avatar
LE GAC Renaud committed
452
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
453
            str:
454

LE GAC Renaud's avatar
LE GAC Renaud committed
455
        """
456
        return self._savefig("png")
457

LE GAC Renaud's avatar
LE GAC Renaud committed
458
    def to_svg(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
459 460
        """Encode the graph using the SVG format.

LE GAC Renaud's avatar
LE GAC Renaud committed
461
        Returns:
LE GAC Renaud's avatar
LE GAC Renaud committed
462
            str:
463

LE GAC Renaud's avatar
LE GAC Renaud committed
464
        """
465
        return self._savefig("svg")
LE GAC Renaud's avatar
LE GAC Renaud committed
466 467


468
class List(BaseReport):
LE GAC Renaud's avatar
LE GAC Renaud committed
469 470 471
    """Build a report as a list.

    A list is a table in which each column contains the values of
472 473 474 475
    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.

476
    The list is displayed as the ``Dbui.grid.Panel`` widget.
477
    The configuration of the list columns is the configuration of
478
    the ``Dbui.grid.Panel`` object.
479

480
    More technically, this class interfaces the database and the
481
    ``Dbui.grid.Panel`` thought the underlying ``Ext.data.Store``.
LE GAC Renaud's avatar
LE GAC Renaud committed
482
    Its configuration is returned by the method *to_store*.
LE GAC Renaud's avatar
LE GAC Renaud committed
483 484

    Args:
485 486
        table (gluon.dal.Table): table containing configurations for reports.
        id_report (int): identifier of the report in the table.
487

488
    """
489
    def __init__(self, table, id_report):
490

491
        BaseReport.__init__(self, table, id_report)
492

493
        # decode column configuration
494
        columns = [Storage(el) for el in json.loads(self.config.columns)]
495

496
        # check column configuration
497
        # add database field map (tablename, fieldname, keyname)
498
        # add the dataIndex (DataFrame, Ext.data.Store, Ext.grid.Panel)
499
        map(self._check_column, columns)
500

501
        # columns are persistent
502
        self._columns = columns
503

504 505
        # instantiate and fill the DataFrame
        self._do_metric()
506

507
    def _cast_type(self, column, dbfield, xtype):
508 509
        """Cast the type of a dataframe column to the database field type
        or to the grid column xtype.
510

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

514 515
        This is fine in most of the case but not with computed column.
        In that case the eval computation crashed.
516 517

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

LE GAC Renaud's avatar
LE GAC Renaud committed
520 521 522
        Args:
            column (str):
                the index of the column in the DataFrame
523

LE GAC Renaud's avatar
LE GAC Renaud committed
524 525 526
            dbfield (tuple):
                address of the database field encoded as
                (tablename, fieldname, keyname).
527

LE GAC Renaud's avatar
LE GAC Renaud committed
528 529 530 531
            xtype (str):
                the xtype of the grid column.
                Possible values are ``booleancolumn``, ``datecolumn``,
                ``gridcolumn`` and ``numbercolumn``.
532

LE GAC Renaud's avatar
LE GAC Renaud committed
533 534
        """
        df = self.df
535
        tablename, fieldname = dbfield[0:2]
LE GAC Renaud's avatar
LE GAC Renaud committed
536

537
        # the dtype of column containing a mixture of type is object.
538
        if (tablename == "year") or (df[column].dtype != "object"):
539
            return
540

541 542 543
        dbtype = self.db[tablename][fieldname].type

        # the dtype for column containing string is also object
544
        if dbtype in ("string", "text"):
545
            return
546

547 548
        elif dbtype == "boolean":
            df[column] = df[column].astype("bool")
549

550
        elif dbtype in ("date", "datetime", "time"):
551
            df[column] = to_datetime(df[column])
552

553 554
        elif dbtype in ("double", "integer"):
            df[column] = df[column].astype("float64")
555

556 557 558
        # 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.
559
        elif dbtype == "json":
560

561
            if xtype == "gridcolumn":
562
                pass
LE GAC Renaud's avatar
LE GAC Renaud committed
563

564 565
            elif xtype == "booleancolumn":
                df[column] = df[column].astype("bool")
566

567
            elif xtype == "datecolumn":
568
                df[column] = to_datetime(df[column])
569

570 571
            elif xtype == "numbercolumn":
                df[column] = df[column].astype("float64")
572

573 574
    def _check_column(self, column):
        """Check column configuration:
575

576 577
            - Raise an exception if xtype is not defined
            - Raise an exception when eval is defined but not the dataIndex
578
            - Add the database field map
579
            - Add the dataIndex if not defined
580

LE GAC Renaud's avatar
LE GAC Renaud committed
581 582
        Args:
            column (gluon.storage.Storage):
583

584 585 586 587 588 589
        """
        T = current.T

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

591 592 593
        if column.eval and not column.dataIndex:
            raise ReportException(T(MSG_NO_DATAINDEX))

594
        dbfield = column.dbfield
595 596 597 598
        if dbfield:
            column.map = split_dbfield(dbfield)

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

601 602
    def _do_metric(self):
        """Interface the database with the DataFrame structure.
LE GAC Renaud's avatar
LE GAC Renaud committed
603
        This method handle the ``year`` database field.
604

605 606

        """
607
        columns = self._columns
608

LE GAC Renaud's avatar
LE GAC Renaud committed
609 610 611 612 613 614 615
        # 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)
616

617 618
        # extract data from the database
        data = self._do_data(maps)
619

620
        # protection
621
        if not data:
622
            self.df = DataFrame(columns=index)
623
            return
624

625
        # fill the DataFrame
626
        df = DataFrame(data, columns=index)
627 628

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

631 632
        # cast dataframe column type to database type or grid column xtype
        map(self._cast_type, index, maps, xtypes)
633

LE GAC Renaud's avatar
LE GAC Renaud committed
634
        # add computed columns
635 636 637 638 639 640 641 642
        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]
643

644
    def _set_store_data(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
645
        """Generate the ``Ext.data.Store.data`` property.
646
        It is a list of dictionaries. Each of them contains the data
LE GAC Renaud's avatar
LE GAC Renaud committed
647 648
        for one row. One key, value pair for each ``Ext.data.Field`` where
        the key is the name of the ``Ext.data.Field``.
649 650

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

655 656
        # convert the JSON-string into a list
        self._store.data = json.loads(data)
657

658
    def _set_store_fields(self):
LE GAC Renaud's avatar
LE GAC Renaud committed
659 660
        """Generate the ``Ext.data.Store.fields`` property.
        It is a list of ``Ext.data.Field`` configuration.
661

LE GAC Renaud's avatar
LE GAC Renaud committed
662 663 664 665 666
        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.
667

668 669
        """
        db = self.db
670
        columns = self._columns
671
        store = self._store
672

673 674
        # convert the columns into the configuration of an Ext.data.Field
        for el in columns:
675

676 677 678
            # protection against rownumberer column
            if not el.dataIndex:
                continue
679

680 681
            tablename, fieldname, keyname = el.map

682
            cfg = Storage(name=el.dataIndex)
683

684
            # the pseudo field year
685 686
            if el.dbfield == "year":
                cfg.type = "int"
687

688 689
            # the computed column
            elif el.eval:
690
                cfg.type = "float"
691

692 693 694 695
            # json type database field
            elif keyname:
                xtype = el.xtype

696 697
                if xtype == "gridcolumn":
                    cfg.type = "string"
698

699 700
                elif xtype == "booleancolumn":
                    cfg.type = "boolean"
701

702 703
                elif xtype == "datecolumn":
                    cfg.type = "date"
704

705 706
                elif xtype == "numbercolumn":
                    cfg.type = "float"
707

708 709 710
            # standard database field, extract the type from the database field
            else:
                dbfield = db[tablename][fieldname]
711

712
                cfg.type = dbfield.type
713 714
                if dbfield.type in ("blob", "string", "text", "json"):
                    cfg.type = "string"
715

716 717
                elif dbfield.type == "boolean":
                    cfg.type = "boolean"
718

719 720 721
                elif dbfield.type in ("date", "datetime", "time"):
                    cfg.type = "date"
                    cfg.dateFormat = "c"
722

723 724
                elif dbfield.type == "double":
                    cfg.type = "float"
725