report_objects.py

# -*- coding: utf-8 -*-
"""report_objects module

"""
import json
import matplotlib
import re


from gluon import current
from gluon.storage import Storage
from pandas import DataFrame, MultiIndex, to_datetime
from plugin_dbui import get_id, Store
from pydal.helpers.methods import smart_query
from pydal.objects import FieldVirtual
from StringIO import StringIO


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

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


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

    Args:
        config (gluon.dal.Row): the list configuration parameter.
        selector (EvtSelector): selector handling period of time.

    Returns:
        str:

    """
    db = current.globalenv["db"]
    T = current.T

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

    # add meta data
    metadata = []
    if selector.id_teams:
        metadata.append(db.teams[selector.id_teams].team)

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

    if selector.category:
        metadata.append(selector.category)

    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))

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

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


def get_value(row, tablename, fieldname, keyname="", **kwargs):
    """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``.

    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.

    Returns:
        * ``row[tablename][fieldname]`` or ``row[fieldname]``
          when tablename and fieldname are defined
        * ``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

    """
    undefined = None

    # force value
    if tablename and (not fieldname) and (tablename in kwargs):
        return kwargs[tablename]

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

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

    else:
        return undefined

    # deal with the keyname
    # it has been design for JSON-type field containing a dictionary
    if not keyname:
        return value

    elif keyname and keyname in value:
        return value[keyname]

    return undefined


def split_dbfield(value):
    """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``).

    Args:
        value (string):
            the name of the database field, either
            ``tablename.fieldname`` or ``tablename.fieldname.key``

    Returns:
        tuple: ``(tablename, fieldname, keyname)`` where the ``keyname``
            is either a string or an empty string.

    """
    li = value.split(".")
    if len(li) == 1:
        li.extend(["", ""])
    elif len(li) == 2:
        li.append("")
    return tuple(li)


class ReportException(BaseException):
    pass


class BaseReport(object):
    """Base class to build list, metric or graph reports.

    Args:
        config (gluon.dal.Row): the configuration parameter for the report.
        selector (EvtSelector): the selector handling user criteria.

    """
    def __init__(self, config, selector):

        db = current.globalenv["db"]

        self.db = db
        self.df = None
        self.config = config
        self.rows = None
        self.selector = selector

        # apply the condition criteria used to filter the history records
        # condition can be written as a smart query: history.id_events == 7
        # or like a python query: db.events.event == "People"
        if "conditions" in config:
            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)

            selector.append_query(q_conditions)

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

        Args:
            maps (list):
                the database field map (tablename, fieldname, keyname).
                One per series.

        Returns:
            list:

        """
        data = []

        db = self.db
        selector = self.selector

        query = selector.query

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

        dbfields.extend([db.history.end_date,
                         db.history.id_domains,
                         db.history.id_people,
                         db.history.id_teams,
                         db.history.percentage,
                         db.history.start_date,
                         db.people.birth_date])

        # the year axis is on
        # scan the database and compute virtual field on the year basis
        if self._is_year(maps):

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

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

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

        return data

    def _is_year(self, maps):
        """
        Args:
            maps (list):
                the database field map (tablename, fieldname, keyname).
                One per series.

        Returns:
            bool: ``True`` is the pseudo field ``year`` is in maps

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

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

        Returns:
            pandas.DataFrame:

        """
        return self.df


class Graph(BaseReport):
    """Build a report as a graph.

    Any data encapsulated in list, 1-dim or 2-dim metrics
    can be displayed as a graph. The rendering is performed by
    the matplotlib library. Therefore, many representations of
    the data are possible: plot, histogram, bar charts, error charts,
    scater plots, *etc*.

    Args:
        config (gluon.dal.Row):
            the configuration parameter for the graph.

        selector (EvtSelector):
            selector handling period of time.

        backend (str):
            the name of the matplotlib backend uses to produce figure.

    """
    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
        # is generated.
        # 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.
        #
        matplotlib.use(backend)

        # 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()

        for k in ("index", "transpose", "xlabel", "ylabel"):
            v = config.plot.pop(k, None)
            config.steer[k] = v

        # 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]

        if report_type == "lists":
            report = List(report_config, selector)

        elif report_type == "metrics1d":
            report = Metric1D(report_config, selector)

        elif report_type == "metrics2d":
            report = Metric2D(report_config, selector)

        self.df = report.to_df()

        # build the graph from the DataFrame
        self._do_graph()
        self._do_labels()
        self._do_legend()
        self._do_tick()

    def _do_graph(self):
        """Build the graph from the ``DataFrame`` structure.

        """
        config = self.config
        df = self.df
        plot, steer = config.plot, config.steer

        # transpose
        if steer.transpose:
            df = df.T

        # generate the plot using a specific set of columns
        if steer.index and len(steer.index) <= len(df.columns):
            ax = df.ix[:, steer.index].plot(**plot)

        # generate the plot using all columns
        else:
            ax = df.ix[:, :].plot(**plot)

        # persistence
        self.ax = ax

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

        """
        ax = self.ax
        steer = self.config.steer

        if steer.xlabel:
            ax.set_xlabel(steer.xlabel, x=1, horizontalalignment="right")

        if steer.ylabel:
            ax.set_ylabel(steer.ylabel, y=1, horizontalalignment="right")

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

        """
        ax = self.ax

        if ax.get_legend():
            box = ax.get_position()
            ax.set_position([box.x0, box.y0, box.width, box.height * 0.9])
            ax.legend(loc="lower right",
                      bbox_to_anchor=(1.01, 1.),
                      fontsize=10,
                      ncol=3)

    def _do_tick(self):
        """Polish the tick mark

        """
        ax = self.ax

        ax.minorticks_on()
        ax.tick_params(which="major", length=8)
        ax.tick_params(which="minor", length=4)

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

        Args:
            fmt (str): possible values are pdf, png and svg.

        """
        fig = self.ax.get_figure()

        fi = StringIO()
        fig.savefig(fi, format=fmt)
        data = fi.getvalue()
        fi.close()

        fig.clear()
        matplotlib.pyplot.close(fig)

        return data

    def to_pdf(self):
        """Encode the graph using the PDF format

        Returns:
            str:

        """
        return self._savefig("pdf")

    def to_png(self):
        """Encode the graph using the PNG format.

        Returns:
            str:

        """
        return self._savefig("png")

    def to_svg(self):
        """Encode the graph using the SVG format.

        Returns:
            str:

        """
        return self._savefig("svg")


class List(BaseReport):
    """Build a report as a list.

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

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

    More technically, this class interfaces the database and the
    ``Dbui.grid.Panel`` thought the underlying ``Ext.data.Store``.
    Its configuration is returned by the method *to_store*.

    Args:
        config (gluon.dal.Row): the configuration parameter for the list.
        selector (EvtSelector): selector handling period of time.

    """
    def __init__(self, config, selector):

        BaseReport.__init__(self, config, selector)

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

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

        # columns are persistent
        self._columns = columns

        # instantiate and fill the DataFrame
        self._do_metric()

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

        The type of the column determine by the pandas might be wrong.
        This append when events are merged with different user block.

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

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

        Args:
            column (str):
                the index of the column in the DataFrame

            dbfield (tuple):
                address of the database field encoded as
                (tablename, fieldname, keyname).

            xtype (str):
                the xtype of the grid column.
                Possible values are ``booleancolumn``, ``datecolumn``,
                ``gridcolumn`` and ``numbercolumn``.

        """
        df = self.df
        tablename, fieldname = dbfield[0:2]

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

        dbtype = self.db[tablename][fieldname].type

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

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

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

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

        # 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.
        elif dbtype == "json":

            if xtype == "gridcolumn":
                pass

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

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

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

    def _check_column(self, column):
        """Check column configuration:

            - Raise an exception if xtype is not defined
            - Raise an exception when eval is defined but not the dataIndex
            - Add the database field map
            - Add the dataIndex if not defined

        Args:
            column (gluon.storage.Storage):

        """
        T = current.T

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

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

        dbfield = column.dbfield
        if dbfield:
            column.map = split_dbfield(dbfield)

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

    def _do_metric(self):
        """Interface the database with the DataFrame structure.
        This method handle the ``year`` database field.


        """
        columns = self._columns

        # 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)

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

        # protection
        if not data:
            self.df = DataFrame(columns=index)
            return

        # fill the DataFrame
        df = DataFrame(data, columns=index)

        # make the data frame persistent
        self.df = df

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

        # add computed columns
        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]

    def _set_store_data(self):
        """Generate the ``Ext.data.Store.data`` property.
        It is a list of dictionaries. Each of them contains the data
        for one row. One key, value pair for each ``Ext.data.Field`` where
        the key is the name of the ``Ext.data.Field``.

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

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

    def _set_store_fields(self):
        """Generate the ``Ext.data.Store.fields`` property.
        It is a list of ``Ext.data.Field`` configuration.

        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.

        """
        db = self.db
        columns = self._columns
        store = self._store

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

            # protection against rownumberer column
            if not el.dataIndex:
                continue

            tablename, fieldname, keyname = el.map

            cfg = Storage(name=el.dataIndex)

            # the pseudo field year
            if el.dbfield == "year":
                cfg.type = "int"

            # the computed column
            elif el.eval:
                cfg.type = "float"

            # json type database field
            elif keyname:
                xtype = el.xtype

                if xtype == "gridcolumn":
                    cfg.type = "string"

                elif xtype == "booleancolumn":
                    cfg.type = "boolean"

                elif xtype == "datecolumn":
                    cfg.type = "date"

                elif xtype == "numbercolumn":
                    cfg.type = "float"

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

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

                elif dbfield.type == "boolean":
                    cfg.type = "boolean"

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

                elif dbfield.type == "double":
                    cfg.type = "float"

                elif dbfield.type == "integer":
                    cfg.type = "int"

            store.fields.append(cfg)

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

        Returns:
            gluon.storage.Storage:
                the keys are ``columns`` and ``features`` and the
                corresponding values are list of dictionary.

        """
        config = self.config
        grid = Storage(columns=[], features=[])

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