Creating_Printable_Reports

It is often useful to provide your users with a printable report that is generated from your database. Although Xataface doesn't include an explicit reporting module to allow the end users to create their own reports, you (the developer) can still quite easily produce a report by creating a custom action.

This report will be subject to the user's sorting and searching preferences. E.g. if the user searches for only books about "frogs", then when he clicks on your printable report it will only display those records that match the query (i.e. only books about frogs).

Requirements for our report

Report will be run against the "products" table (using the WebAuction application).
Report should be accessible by clicking an icon in the top right of the list view (i.e. the resultlist actions).
Report should display the product ID, product name, photo, and description. One product per page.

Adding the Icon to our Application

We'll start out by finding an appropriate icon to use for our action. There are loads of free icons that you can download (please observe and respect the license agreement of any icon library that you use). Two good free icon libraries include:

Once you have found the icon you want to use, just upload it somewhere inside your application's directory. It might be a good idea to create a directory to store your images if you haven't already. An appropriate name might by images. For this example, we'll use the icon from the FamFamFam icon library. So we upload this icon to

 %APPLICATION_PATH%/images/printer.png

Next we need to create an entry in our application's actions.ini file so that Xataface knows about our action, and where we want its icon to be displayed. We'll start out with a basic definition that just specifies the icon for the action, and the category of the action.

[printable_report]
    icon="{$site_url}/images/printer.png"
    category=result_list_actions
    description="See this product list in a printable format"

The result_list_actions category setting means that the icon for our action will be included along with the result list actions, which are located in the top right corner in the list tab. Now if we reload our application and look at the "list" tab, you'll see that the icon group in the top right now includes our icon:

If you don't see this icon, but see the text "printable_report" instead, then you have entered an incorrect path to the icon in the icon directive. If you don't see an icon at all or any text, then your category directive may be incorrect. Check that it is exactly "result_list_actions".

If you mouse over your icon you'll see the text that you specified as your description directive for the action:

You'll notice, if you try to click on your icon, that nothing happens. This is because we haven't yet specified a URL for your action. We'll wait to specify our URL, until we've built the back-end of our action (i.e. the actual report).

Creating the Actual Report

Most reports involve the following pieces:

Fetch the found set of records from the database.
Loop through the found set and output some information about each record.
Optionally use a template to integrate the report into your application's look and feel.

All reports will be placed in the framework of a custom Xataface action. In our case we add a file to our application actions directory named after our action:

 %APPLICATION_PATH%/actions/printable_report.php

with the following contents:

<?php
class actions_printable_report {
    function handle(&$params){
        echo "Hello world!!!";
    }
}

Please note the following about this snippet:

The action was placed in a file actions/printable_report.php because the action is named printable_report (referring to our definition in the actions.ini file. If the action was named foo, then we would place our action in file actions/foo.php.
The printable_report.php file contains a single class named actions_printable_report after the name of the action.
The action class contains a single method handle which handles the request for the action (i.e. outputs the report the way we like). This method must exist and me named exactly handle.
The handle method takes a single &$params parameter which contains some parameters that may be passed to the action. We won't be dealing with these in this example.

Before proceeding, let's try accessing our action just to make sure that we're on the right track. Point your browser to

 http://example.com/yourapplication/index.php?-action=printable_report

Note that you don't point our web browser directly to your action php file (in the actions directory. Rather you point it to your application's entry point (index.php file), and specify the action via the -action GET parameter. Your web browser should display something like:

If you get a blank white screen, then you should check your error log to see where the error is occuring. See Troubleshooting for general Xataface troubleshooting strategies in this case.

Now that we have all of the formalities out of the way, we can proceed to meat of our report.

Retrieving the Found Set

Let's build onto our action now. First we will load the found set of records as follows:

<?php
class actions_printable_report {
    function handle(&$params){
        $app =& Dataface_Application::getInstance();
        $query =& $app->getQuery();
        
        if ( $query['-table'] != 'products' ){
            return PEAR::raiseError('This action can only be called on the Products table.');
        }
        
        $products = df_get_records_array('products', $query);
        
    }
}

Things to note in this snippet:

We start be loading a reference to the Dataface_Application object.
We then use the Dataface_Application object to load the current query. This is essentially an associative array of all of the GET parameters, but with some guaranteed attributes such as -table and -action.
In our particular action we are designing it to only work for the products table so we do a check on the query parameters to make sure that this is the case. If someone tries to run this action from outside the products table (e.g. if -action=foo) then an error will be displayed.
We use the df_get_records_array() function to return all matching records on the products table. It returns an array of Dataface_Record? objects.

Overriding -skip and -limit

Xataface allows the user to specify the number of records to display and the position in the found set to start from by adding the -skip and -limit GET parameters to a request. If these are omitted, then default values of 0 and 30 are used respectively. You may notice that if you click "Next" in list view, you see '-skip' and '-limit' parameters automatically added to the URL.

df_get_records_array respects the -limit and -skip parameters that are specified in the query. I.e. if -skip and -limit are omitted it will return only the first 30 records from the found set. If -skip=1 and -limit=10 then it will return 10 records starting from the 2nd record (2nd becuase -skip=0 would point to the first record). This may be desired behavior for your report, but in some reports you may want to print off the entire found set. If this is the case, you will want to explicitly set the -skip and -limit parameters in the $query array before passing it to df_get_records_array. E.g.:

$query =& $app->getQuery();
$query['-skip'] = 0;
$query['-limit'] = 10000;
$products = df_get_records_array('products', $query);

Looping through and Printing Product Info

Now comes the fun part. We're just going to loop through our found set and print off the product information:


foreach ($products as $p){
    

    echo '<table>'
        .'<tr><th>Product ID</th><td>'.$p->htmlValue('product_id').'</td></tr>'
        .'<tr><th>Product Name</th><td>'.$p->htmlValue('product_name').'</td></tr>'
        .'<tr><th>Description</th><td>'.$p->htmlValue('product_description').'</td></tr>'
        .'<tr><th>Photo</th><td>'.$p->htmlValue('product_image').'</td></tr>'
        .'</table>';
}

The entire action at this point will look like:


<?php
class actions_printable_report {
    function handle(&$params){
        $app =& Dataface_Application::getInstance();
        $query =& $app->getQuery();
        $query['-skip'] = 0;
        $query['-limit'] = 10000;
        
        if ( $query['-table'] != 'products' ){
            return PEAR::raiseError('This action can only be called on the Products table.');
        }
        
        $products = df_get_records_array('products', $query);
        foreach ($products as $p){
    

            echo '<table>'
                .'<tr><th>Product ID</th><td>'.$p->htmlValue('product_id').'</td></tr>'
                .'<tr><th>Product Name</th><td>'.$p->htmlValue('product_name').'</td></tr>'
                .'<tr><th>Description</th><td>'.$p->htmlValue('product_description').'</td></tr>'
                .'<tr><th>Photo</th><td>'.$p->htmlValue('product_image').'</td></tr>'
                .'</table>';
        }
        
    }
}

Now we refresh our action in the web browser, or point the browser again to:

 http://example.com/yourapplication/index.php?-action=printable_report&-table=products

It should display something like:

If you get a blank white screen, please check out the Troubleshooting section for general Xataface troubleshooting strategies.

Adding a Little Style

It is a good idea to at least provide the proper HTML HEAD and BODY tags for your report. And to help make things a little nicer looking we're going to add some CSS styles to:

Make the table field labels vertically aligned to the top.
Change the font to helvetica.

This is easy to do with simple echo statements:

echo '<html><head>'
    .'<title>Printable Report</title>'
    .'<style type="text/css">'
    .'    th { vertical-align: top}'
    .'</style>'
    .'</head>'
    .'<body>';
    
    //...

So our finished action looks like:

<?php
class actions_printable_report {
    function handle(&$params){
        $app =& Dataface_Application::getInstance();
        $query =& $app->getQuery();
        $query['-skip'] = 0;
        $query['-limit'] = 10000;
        
        if ( $query['-table'] != 'products' ){
            return PEAR::raiseError('This action can only be called on the Products table.');
        }
        
        $products = df_get_records_array('products', $query);
        
        
        
        echo '<html><head>'
            .'<title>Printable Report</title>'
            .'<style type="text/css">'
            .'    th { vertical-align: top}'
            .'</style>'
            .'</head>'
            .'<body>';
        foreach ($products as $p){
    

            echo '<table>'
                .'<tr><th>Product ID</th><td>'.$p->htmlValue('product_id').'</td></tr>'
                .'<tr><th>Product Name</th><td>'.$p->htmlValue('product_name').'</td></tr>'
                .'<tr><th>Description</th><td>'.$p->htmlValue('product_description').'</td></tr>'
                .'<tr><th>Photo</th><td>'.$p->htmlValue('product_image').'</td></tr>'
                .'</table>';
        }
        
        echo '</body></html>';
        
    }
}

Connecting the Icon to the Action

FInally it is time to connect our Icon to our Action. We do this by adding a url directive for the action in the actions.ini file:

[printable_report]
    icon="{$site_url}/images/printer.png"
    category=result_list_actions
    description="See this product list in a printable format"
    url="{$app->url('-action=printable_report')}"

Explanation of the url directive in this snippet:

The url method of the Dataface_Application? object is used to generate a URL with the user's current query settings, but with the -action parameter set to printable_report.

Now if we reload our application, go to the list tab of the products table and click on our icon, it should take us to our action:

Trying out the action on different found sets with different sort orders

One of the cool things about this action is that it is tied directly into the Xataface find settings so that th user is able to search for a subset of products and run our report on only those products that were found. The user can also perform a sort on any column and this sort will be respected by our report.

Hiding Icon from Other Tables

Since our action is only intended to operate on the products table it probably isn't a good idea to make the icon visible for every other table. For example, if you go to the list view of the users table, you'll see the printer icon in the top right just like it appears for the products table. Clicking on it should display our error:

We will use a condition directive for our action to hide it from tables other than the products table as follows:

 condition="$query['-table'] == 'products'"

So our action will now look like:

[printable_report]
    icon="{$site_url}/images/printer.png"
    category=result_list_actions
    description="See this product list in a printable format"
    url="{$app->url('-action=printable_report')}"
    condition="$query['-table'] == 'products'"

Now if you reload the list for the users table you'll notice that the printer icon is now gone. But returning to the products table shows our action still alive and well.

Locking Down our Action with Permissions

In our case we don't want our action to be accessible to all users. Only administrators. Xataface permissions and all its possibilities are beyond the scope of this tutorial, but we still want to demonstrate how to lock down this action. The WebAuction application into which this action is being installed defines a permission called reports which only administrators have. We will use this permission to limit access to this action as follows in the actions.ini file:

 permission=reports

So the actions.ini file will now look like:

[printable_report]
    icon="{$site_url}/images/printer.png"
    category=result_list_actions
    description="See this product list in a printable format"
    url="{$app->url('-action=printable_report')}"
    condition="$query['-table'] == 'products'"
    permission=reports

Now only administrators will see our icon, and if non-administrators attempt to access out action by typing in its URL directly, they will receive an "Access Denied" message.

Language en

Creating_Printable_Reports[ Search for ""]