示例#1
0
    def query_data(cls, widget):
        # get the columns needed
        column = widget.column
        group_by = widget.group_by if widget.group_by else None

        if isinstance(widget.content_object, XForm):
            xform = widget.content_object
        elif isinstance(widget.content_object, DataView):
            xform = widget.content_object.xform

        field = get_field_from_field_xpath(column, xform)

        if isinstance(field, basestring) and field == SUBMISSION_TIME:
            field_label = 'Submission Time'
            field_xpath = '_submission_time'
            field_type = 'datetime'
            data_type = DATA_TYPE_MAP.get(field_type, 'categorized')
        else:
            field_type = field.type
            data_type = DATA_TYPE_MAP.get(field.type, 'categorized')
            field_xpath = field.get_abbreviated_xpath()
            field_label = get_field_label(field)

        columns = [
            SimpleField(field="json->>'%s'" % unicode(column),
                        alias='"{}"'.format(column)),
            CountField(field="json->>'%s'" % unicode(column), alias='"count"')
        ]
        if group_by:
            if field_type in NUMERIC_LIST:
                column_field = SimpleField(field="json->>'%s'" %
                                           unicode(column),
                                           cast="float",
                                           alias=column)
            else:
                column_field = SimpleField(field="json->>'%s'" %
                                           unicode(column),
                                           alias=column)

            # build inner query
            inner_query_columns = \
                [column_field,
                 SimpleField(field="json->>'%s'" % unicode(group_by),
                             alias=group_by),
                 SimpleField(field="xform_id"),
                 SimpleField(field="deleted_at")]
            inner_query = Query().from_table(Instance, inner_query_columns)

            # build group-by query
            if field_type in NUMERIC_LIST:
                columns = [
                    SimpleField(field=group_by, alias='"%s"' % group_by),
                    SumField(field=column, alias="sum"),
                    AvgField(field=column, alias="mean")
                ]
            elif field_type == SELECT_ONE:
                columns = [
                    SimpleField(field=column, alias='"%s"' % column),
                    SimpleField(field=group_by, alias='"%s"' % group_by),
                    CountField(field="*", alias='"count"')
                ]

            query = Query().from_table({'inner_query': inner_query}, columns).\
                where(xform_id=xform.pk, deleted_at=None)

            if field_type == SELECT_ONE:
                query.group_by(column).group_by(group_by)
            else:
                query.group_by(group_by)

        else:
            query = Query().from_table(Instance, columns).\
                where(xform_id=xform.pk, deleted_at=None)
            query.group_by("json->>'%s'" % unicode(column))

        # run query
        records = query.select()

        # flatten multiple dict if select one with group by
        if field_type == SELECT_ONE and group_by:
            records = _flatten_multiple_dict_into_one(column, group_by,
                                                      records)
        # use labels if group by
        if group_by:
            group_by_field = get_field_from_field_xpath(group_by, xform)
            choices = get_field_choices(group_by, xform)
            records = _use_labels_from_group_by_name(group_by,
                                                     group_by_field,
                                                     data_type,
                                                     records,
                                                     choices=choices)
        return {
            "field_type": field_type,
            "data_type": data_type,
            "field_xpath": field_xpath,
            "field_label": field_label,
            "grouped_by": group_by,
            "data": records
        }
示例#2
0
文件: widget.py 项目: onaio/onadata
    def query_data(cls, widget):
        # get the columns needed
        column = widget.column
        group_by = widget.group_by if widget.group_by else None

        if isinstance(widget.content_object, XForm):
            xform = widget.content_object
        elif isinstance(widget.content_object, DataView):
            xform = widget.content_object.xform

        field = get_field_from_field_xpath(column, xform)

        if isinstance(field, basestring) and field == SUBMISSION_TIME:
            field_label = 'Submission Time'
            field_xpath = '_submission_time'
            field_type = 'datetime'
            data_type = DATA_TYPE_MAP.get(field_type, 'categorized')
        else:
            field_type = field.type
            data_type = DATA_TYPE_MAP.get(field.type, 'categorized')
            field_xpath = field.get_abbreviated_xpath()
            field_label = get_field_label(field)

        columns = [
            SimpleField(
                field="json->>'%s'" % text(column),
                alias='{}'.format(column)),
            CountField(
                field="json->>'%s'" % text(column),
                alias='count')
        ]

        if group_by:
            if field_type in NUMERIC_LIST:
                column_field = SimpleField(
                    field="json->>'%s'" % text(column),
                    cast="float",
                    alias=column)
            else:
                column_field = SimpleField(
                    field="json->>'%s'" % text(column), alias=column)

            # build inner query
            inner_query_columns = \
                [column_field,
                 SimpleField(field="json->>'%s'" % text(group_by),
                             alias=group_by),
                 SimpleField(field="xform_id"),
                 SimpleField(field="deleted_at")]
            inner_query = Query().from_table(Instance, inner_query_columns)

            # build group-by query
            if field_type in NUMERIC_LIST:
                columns = [
                    SimpleField(field=group_by, alias='%s' % group_by),
                    SumField(field=column, alias="sum"),
                    AvgField(field=column, alias="mean")
                ]
            elif field_type == SELECT_ONE:
                columns = [
                    SimpleField(field=column, alias='%s' % column),
                    SimpleField(field=group_by, alias='%s' % group_by),
                    CountField(field="*", alias='count')
                ]

            query = Query().from_table({'inner_query': inner_query}, columns).\
                where(xform_id=xform.pk, deleted_at=None)

            if field_type == SELECT_ONE:
                query.group_by(column).group_by(group_by)
            else:
                query.group_by(group_by)

        else:
            query = Query().from_table(Instance, columns).\
                where(xform_id=xform.pk, deleted_at=None)
            query.group_by("json->>'%s'" % text(column))

        # run query
        records = query.select()

        # flatten multiple dict if select one with group by
        if field_type == SELECT_ONE and group_by:
            records = _flatten_multiple_dict_into_one(column, group_by,
                                                      records)
        # use labels if group by
        if group_by:
            group_by_field = get_field_from_field_xpath(group_by, xform)
            choices = get_field_choices(group_by, xform)
            records = _use_labels_from_group_by_name(
                group_by, group_by_field, data_type, records, choices=choices)

        return {
            "field_type": field_type,
            "data_type": data_type,
            "field_xpath": field_xpath,
            "field_label": field_label,
            "grouped_by": group_by,
            "data": records
        }