S3XRC Mini-Tutorial

• S3XRC Recipes and Tutorials

A report function for your resource

Use-case

You have a module xxx and within it a resource yyy, which has a number of components. You're providing CRUD functions for the resource and its components using s3_rest_controller().

Now you want to provide a reporting function for this resource, where the user can specify report parameters in a form and then get a summary report auto-generated:

Our example: Your resource contains a "timestmp" field (type datetime) and a field "cost" (type double), and in your reporting function the user shall be able to select a time interval, and get a report of the sum of all "cost"s within the selected time interval auto-generated.

This tutorial shows you how this can be integrated into the REST interface of your resource.

Techniques

Create a custom method handler

Reporting is a method of your resource, the reporting function will therefore be implemented as a custom REST method handler function.

To achieve this, add the reporting function to your model file, and specify it as a method to your resource using s3xrc.model.set_method, like:

def s3_xxx_yyy_report(r, **attr):

    # Report generating code goes here

s3xrc.model.set_method("xxx", "yyy", method="report", action=s3_xxx_yyy_report)

A custom method handler has to take two arguments:

• r is the respective S3Request object, which represents the current request
• attr is the same dict of named arguments that have been passed to shn_rest_controller

Once you have done this, you can invoke your method handler from the URL:

http://localhost:8000/eden/xxx/yyy/report

But it even understands URLs like:

http://localhost:8000/eden/xxx/yyy/1/report
http://localhost:8000/eden/xxx/yyy/report?yyy.id=1,2,3
http://localhost:8000/eden/xxx/yyy/report?yyy.field1__like=abc

meaning: it already implements a RESTful API for your reporting function, e.g. does the parsing/validating of the URL for you, implements the full range of URL queries for your resource and so forth. Nothing you need to care about.

Provide different report formats

In a later improvement, you want perhaps to provide the report in various formats, hence you need to know what format the user has requested. The best way is to check for the format that has been specified in the URL.

To know which format has been specified, simply use r.representation inside the method handler:

def s3_xxx_yyy_report(r, **attr):

    if r.representation == "html":
        # HTML report generating code goes here:
        # "output" gets a dict of variables to be passed to the view

        response.view = "xxx/yyy_report.html" # <== choose the view template

        return output # <== return the output to the view

    elif r.representation == "xls":
        # XLS report generating code goes here
        # "output" gets the XLS contents

        return output # <== return the output to the view

    elif r.representation == "pdf":
        # PDF report generating code goes here
        # "output" takes the PDF contents

        return output # <== return the output to the view

    elif r.representation == "svg":
        # SVG report generating code goes here
        # "output" takes the SVG as a string

        return output # <== return the output to the view

    else:
        # Unsupported format
        raise HTTP(501, body=s3xrc.ERROR.BAD_FORMAT)

s3xrc.model.set_method("xxx", "yyy", method="report", action=s3_xxx_yyy_report)

Now your method handler recognises the requested format as specified in the URL by either a filename extension or the format variable:

http://localhost:8000/eden/xxx/yyy/4/report.xls
http://localhost:8000/eden/xxx/yyy.pdf/6
http://localhost:8000/eden/xxx/yyy/report?format=svg

It is important to raise an HTTP error status in case of an unsupported format rather than providing a nicely formatted error message: while an interactive user who just clicks on links and buttons would rarely request something else than the interactive formats we expose to him, it would rather be a non-interactive client which requests other formats - and that would hardly understand a nicely formatted error message but expect a proper HTTP status in the response header.

Get at the data

How can you find out which data have to be processed by your reporting function, and yet more important: how can you get at them?

r (the S3Request object) contains an interface to your resource as S3Resource object in: r.resource

And this is what you can use to easily access the resource data. Some examples:

def s3_xxx_yyy_report(r, **attr):

    resource = r.resource # <== Get the resource

    if r.representation == "html":

        resource.load()
        rows = resource.records() # <== get all records

    elif r.representation == "xls":

        for record in resource: # <== interate through the records
            ...

    elif r.representation == "pdf":

        # Access components record-wise:
        for record in resource:
            component_set = resource(record, component="component_name")
            for component_record in component_set:
                ...

    elif r.representation == "svg":

        # Modifying the resource query before accessing records
        filter = resource.get_query()
        filter = filter & (db.xxx_yyy.field5 == "value")
        resource.build_query(filter = filter)

        # ...and then iterate through the records:
        for record in resource:
            ...

    else:
        # Unsupported format
        raise HTTP(501, body=s3xrc.ERROR.BAD_FORMAT)

s3xrc.model.set_method("xxx", "yyy", method="report", action=s3_xxx_yyy_report)

Step by step: total costs within a time interval

Your resource contains a "timestmp" field (type datetime) and a field "cost" (type double), and now the user shall select a time interval in aform, and your report function shall provide the sum of all "cost" for those records with a timestmp within the selected time interval.

Let's go:

1. Create a method handler

First of all, we implement our method handler as mentioned before:

def s3_xxx_yyy_report(r, **attr):

    # Report generating code goes here

s3xrc.model.set_method("xxx", "yyy", method="report", action=s3_xxx_yyy_report)

2. Add representations

Then we add the HTML representation:

def s3_xxx_yyy_report(r, **attr):

    if r.representation == "html":
        output = dict()
        return output

s3xrc.model.set_method("xxx", "yyy", method="report", action=s3_xxx_yyy_report)

3. Add the form

The next step would be to provide a form to select the first and the last date of the interval. This would look like that:

    if r.representation == "html":

        # Filter form
        form = FORM(
                 TABLE(
                   TR(
                     T("Date from:"),
                     INPUT(_type="text", _name="date_from", _class="date", _value=datetime.now().date(), requires=IS_DATE()),
                     T("until:"),
                     INPUT(_type="text", _name="date_until", _class="date", _value=datetime.now().date(), requires=IS_DATE())
                   )
                 )
               )

        output = dict(form=form)

        if form.accepts(request.vars, session, keepvalues=True):
            # Processing of the form data goes here

        return output

4. Process the form data

From this, we need to process the form data into two dates. We use datetime for that:

        if form.accepts(request.vars, session, keepvalues=True):

            from datetime import date, datetime, time

            # Date from
            if form.vars.date_from:
                from = datetime.combine(form.vars.date_from, time(0, 0, 0))

            # Date until
            if form.vars.date_until:
                until = datetime.combine(form.vars.date_until + timedelta(days=1), time(0, 0, 0))

5. Extend the resource query

To select the corresponding records, we need to extend our query:

        if form.accepts(request.vars, session, keepvalues=True):

            # Get the initial query from the resource
            # => this implements authorisation as well as URL queries
            resource = r.resource
            table = resource.table
            query = resource.get_query()

            from datetime import date, datetime, time

            # Date from
            if form.vars.date_from:
                from = datetime.combine(form.vars.date_from, time(0, 0, 0))
                date_query = (table.timestmp >= from)

            # Date until
            if form.vars.date_until:
                until = datetime.combine(form.vars.date_until + timedelta(days=1), time(0, 0, 0))
                q = (table.timetmp <= until)
                if date_query:
                    date_query = date_query & q
                else:
                    date_query = q

            # Add our filter
            query = query & date_query

6. Retrieve the sum of costs

From there, it is easy to select the sum of the "cost" field:

        if form.accepts(request.vars, session, keepvalues=True):

            # Get the initial query from the resource
            # => this implements authorisation as well as URL queries
            resource = r.resource
            table = resource.table
            query = resource.get_query()

            from datetime import date, datetime, time

            # Date from
            if form.vars.date_from:
                from = datetime.combine(form.vars.date_from, time(0, 0, 0))
                date_query = (table.timestmp >= from)

            # Date until
            if form.vars.date_until:
                until = datetime.combine(form.vars.date_until + timedelta(days=1), time(0, 0, 0))
                q = (table.timetmp <= until)
                if date_query:
                    date_query = date_query & q
                else:
                    date_query = q

            # Add our filter
            query = query & date_query

            # Get the total costs
            costs = table.costs.sum()
            total = db(query).select(costs)

            # Build the result string
            result = "%s: %s" % (T("The total costs of the selected projects are"), total)

            output.update(result=result)

7. Add a view

Done with the method handler! Here at a glance:

def s3_xxx_yyy_report(r, **attr):

    if r.representation == "html":

        # Filter form
        form = FORM(
                 TABLE(
                   TR(
                     T("Date from:"),
                     INPUT(_type="text", _name="date_from", _class="date", _value=datetime.now().date(), requires=IS_DATE()),
                     T("until:"),
                     INPUT(_type="text", _name="date_until", _class="date", _value=datetime.now().date(), requires=IS_DATE())
                   )
                 )
               )

        output = dict(form=form)

        if form.accepts(request.vars, session, keepvalues=True):

            # Get the initial query from the resource
            # => this implements authorisation as well as URL queries
            resource = r.resource
            table = resource.table
            query = resource.get_query()

            from datetime import date, datetime, time

            # Date from
            if form.vars.date_from:
                from = datetime.combine(form.vars.date_from, time(0, 0, 0))
                date_query = (table.timestmp >= from)

            # Date until
            if form.vars.date_until:
                until = datetime.combine(form.vars.date_until + timedelta(days=1), time(0, 0, 0))
                q = (table.timetmp <= until)
                if date_query:
                    date_query = date_query & q
                else:
                    date_query = q

            # Add our filter
            query = query & date_query

            # Get the total costs
            costs = table.costs.sum()
            total = db(query).select(costs)

            # Build the result string
            result = "%s: %s" % (T("The total costs of the selected projects are"), total)

            output.update(result=result)

            # Select the corresponding view:
            response.view = "xxx/report.html"

    else:
        raise HTTP(501, body=s3xrc.ERROR.BAD_FORMAT)

    return output

s3xrc.model.set_method("xxx", "yyy", method="report", action=s3_xxx_yyy_report)

We have added a line to raise an HTTP error in case an unsupported format gets requested, and added a line to select a proper view template "xxx/report.html", which would look like that:

{{extend "layout.html"}}
<div class='form-container'>
{{try:}}{{=form}}{{except:}}{{pass}}
</div>
<div id='table-container'>
{{try:}}{{=result}}{{except:}}{{pass}}
</div>

...and that's it.