Exports

Export is an important issue for the Chill software : users should be able to :

  • compute statistics about their activity ;
  • list “things” which make part of their activities.

The main bundle provides a powerful framework to build custom queries with re-usable parts across differents bundles.

See also

The issue where this framework was discussed
Provides some information about the pursued features and architecture.

Concepts

Some vocabulary: 3 “Export elements”

Four terms are used for this framework :

exports

provides some basic operation on the date. Two kind of exports are available :

  • computed data : it may be “the number of people”, “the number of activities”, “the duration of activities”, ...
  • list data : it may be “the list of people”, “the list of activity”, ...
filters

The filters make a filter on the date: it removes some information the user doesn’t want to introduce in the computation done by export. In other word, filters make a filter...

Example of filter: “people under 18 years olds”, “activities between the 1st of June and the 31st December”, ...

aggregators

The aggregator aggregates the data into some group (some software use the term ‘bucket’).

Example of aggregator : “group people by gender”, “group people by nationality”, “group activity by type”, ...

formatters

The formatters format the data into a SymfonyComponentHttpFoundationResponse, which will be returned “as is” by the controller to the web client.

Example of formatter: “format data as CSV”, “format data as ods spreadsheet”, ...

Anatomy of an export

An export may be explained as a sentence, where each part of this sentence refers to one or multiple exports element. Examples :

Example 1: Count the number of people having at least one activity in the last 12 month, and group them by nationality and gender, and format them in a CSV spreadsheet.

Here :

  • count the number of people is the export part
  • having at least one activity is the filter part
  • group them by nationality is the aggregator part
  • group them by gender is a second aggregator part
  • format the date in a CSV spreadsheet is the formatter part

Note that :

  • aggregators, filters, exports and aggregators are cross-bundle. Here the bundle activity provides a filter which apply on an export provided by the person bundle ;
  • there may exists multiple aggregator or filter for one export. Currently, only one export is allowed.

The result might be :

Nationality Gender Number of people
Russian Male 12
Russian Female 24
France Male 110
France Female 150

Example 2: Count the average duration of an activity with type “meeting”, which occurs between the 1st of June and the 31st of December, group them by week, and format the data in a OpenDocument spreadsheet.

Here :

  • count the average duration of an activity is the export part
  • activity with type meeting is a filter part
  • activity which occurs between the 1st of June and the 31st of December is a filter
  • group them by week is the aggregator part
  • format the date in an OpenDocument spreadsheet is the formatter part

The result might be :

Week Number of activities
2015-10 10
2015-11 12
2015-12 10
2015-13 9

Authorization and exports

Exports, filters and aggregators should not make see data the user is not allowed to see.

In other words, developers are required to take care of user authorization for each export.

It should exists a special role that should be granted to users which are allowed to build exports. For more simplicity, this role should apply on center, and should not requires special circles.

How does the magic works ?

To build an export, we rely on the capacity of the database to execute queries with aggregate (i.e. GROUP BY) and filter (i.e. WHERE) instructions.

An export is an SQL query which is initiated by an export, and modified by aggregators and filters.

Note

Example: Count the number of people having at least one activity in the last 12 month, and group them by nationality and gender

  1. The report initiate the query
SELECT count(people.*) FROM people
  1. The filter add a where and join clause :
SELECT count(people.*) FROM people
   RIGHT JOIN activity
   WHERE activity.date IS BETWEEN now AND 6 month ago
  1. The aggregator “nationality” add a GROUP BY clause and a column in the SELECT statement:
SELECT people.nationality, count(people.*) FROM people
   RIGHT JOIN activity
   WHERE activity.date IS BETWEEN now AND 6 month ago
   GROUP BY nationality
  1. The aggregator “gender” do the same job as the nationality aggregator : it adds a GROUP BY clause and a column in the SELECT statement :
SELECT people.nationality, people.gender, count(people.*)
   FROM people RIGHT JOIN activity
   WHERE activity.date IS BETWEEN now AND 6 month ago
   GROUP BY nationality, gender

Each filter, aggregator and filter may collect parameters from the user by providing a form. This form is appended to the export form. Here is an example.

../_images/export_form-fullpage.png

The screenshot show the export form for CountPeople (Nombre de personnes). The filter by date of birth is checked (Filtrer par date de naissance de la personne), which allow to show a subform, which is provided by the ChillPersonBundleExportFilterBirthdateFilter. The other filter, which are unchecked, does not show the subform.

Two aggregators are also checked : by Country of birth (Aggréger les personnes par pays de naissance, corresponding class is ChillPersonBundleExportAggregatorCountryOfBirthAggregator, which also open a subform. The aggregator by gender (Aggréger les personnes par genre) is also checked, but there is no corresponding subform.

The Export Manager

The Export manager (ChillMainBundleExportExportManager is the central class which register all exports, aggregators, filters and formatters.

The export manager is also responsible for orchestrating the whole export process, producing a SymfonyFrameworkBundleHttpFoundationRequest to each export request.

The export form step

The form step allow to build a form, aggregating different parts of the module.

The building of forms is separated between different subform, which are responsible for rendering their part of the form (aggregators, filters, and export).

../_images/form_steps.png

The formatter form step

The formatter form is processed after the user filled the export form. It is built the same way, but receive in parameters the data entered by the user on the previous step (i.e. export form). It may then adapt it accordingly (example: show a list of columns selected in aggregators).

Processing the export

The export process may be explained by this schema :

../_images/processing_export.png

(Click to enlarge)

Export, formatters and filters explained

Exports

This is an example of the CountPerson export :

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
<?php


namespace Chill\PersonBundle\Export\Export;

use Chill\MainBundle\Export\ExportInterface;
use Doctrine\ORM\QueryBuilder;
use Symfony\Component\Form\FormBuilderInterface;
use Doctrine\ORM\Query;
use Chill\PersonBundle\Security\Authorization\PersonVoter;
use Symfony\Component\Security\Core\Role\Role;
use Chill\PersonBundle\Export\Declarations;
use Chill\MainBundle\Export\FormatterInterface;
use Doctrine\ORM\EntityManagerInterface;

/**
 * 
 *
 * @author Julien Fastré <julien.fastre@champs-libres.coop>
 */
class CountPerson implements ExportInterface
{
    /**
     *
     * @var EntityManagerInterface
     */
    protected $entityManager;
    
    public function __construct(
            EntityManagerInterface $em
            )
    {
        $this->entityManager = $em;
    }
    
    public function getType()
    {
        return Declarations::PERSON_TYPE;
    }
    
    public function getDescription()
    {
        return "Count peoples by various parameters.";
    }
    
    public function getTitle()
    {
        return "Count peoples";
    }
    
    public function requiredRole()
    {
        return new Role(PersonVoter::STATS);
    }
    
    public function initiateQuery(array $requiredModifiers,  array $acl, array $data = array())
    {
        // we gather all center the user choose.
        $centers = array_map(function($el) { return $el['center']; }, $acl);
        
        $qb = $this->entityManager->createQueryBuilder();
        
        $qb->select('COUNT(person.id) AS export_result')
                ->from('ChillPersonBundle:Person', 'person')
                ->join('person.center', 'center')
                ->andWhere('center IN (:authorized_centers)')
                ->setParameter('authorized_centers', $centers);
                ;
        
        
        return $qb;
    }
    
    public function getResult($qb, $data)
    {
        return $qb->getQuery()->getResult(Query::HYDRATE_SCALAR);
    }
    
    public function getQueryKeys($data)
    {
        // this array match the result keys in the query. We have only
        // one column.
        return array('export_result');
    }
    
    public function getLabels($key, array $values, $data)
    {
        
        // the Closure which will be executed by the formatter. 
        return function($value) {
            switch($value) {
                case '_header':
                  // we have to process specifically the '_header' string, 
                  // which will be used by the formatter to show a column title
                  return $this->getTitle();
                default:
                  // for all value, we do not process them and return them
                  // immediatly
                  return $value;
        };
    }
    
    public function getAllowedFormattersTypes()
    {
        return array(FormatterInterface::TYPE_TABULAR);
    }
    
    public function buildForm(FormBuilderInterface $builder) {
        // this export does not add any form
    }
    
    public function supportsModifiers()
    {
        // explain the export manager which formatters and filters are allowed
        return array(Declarations::PERSON_TYPE, Declarations::PERSON_IMPLIED_IN);
    }
    
}
  • Line 36: the getType function return a string. This string will be used to find the aggregtors and filters which will apply to this export.
  • Line 41: a simple description to help user to understand what your export does.
  • Line 46: The title of the export. A summary of what your export does.
  • Line 51: The list of roles requires to execute this export.
  • Line 56: We initiate the query here...
  • Line 59: We have to filter the query with centers the users checked in the form. We process the $acl variable to get all Center object in one array
  • Line 63: We create the query, with a query builder.
  • Line 74: We simply returns the result, but take care of hydrating the results as an array.
  • Line 103: return the list of formatters types which are allowed to apply on this filter

Filters

This is an example of the filter by birthdate. This filter ask some information in a form (buildForm is not empty), and this form must be validated. To performs this validations, we implement a new Interface: ChillMainBundleExportExportElementValidatedInterface:

<?php

namespace Chill\PersonBundle\Export\Filter;

use Chill\MainBundle\Export\FilterInterface;
use Symfony\Component\Form\Extension\Core\Type\DateType;
use Symfony\Component\Validator\Context\ExecutionContextInterface;
use Symfony\Component\Validator\Constraints;
use Symfony\Component\Form\FormEvent;
use Symfony\Component\Form\FormEvents;
use Doctrine\ORM\Query\Expr;
use Chill\MainBundle\Form\Type\Export\FilterType;
use Symfony\Component\Form\FormError;
use Chill\MainBundle\Export\ExportElementValidatedInterface;

class BirthdateFilter implements FilterInterface, ExportElementValidatedInterface
{
    // add specific role for this filter    
    public function addRole()
    {
        // we do not need any new role for this filter, so we return null
        return null;
    }

    // we give information on which type of export this filter applies
    public function applyOn()
    {
        return 'person';
    }

    public function getTitle()
    {
        return 'Filter by person\'s birthdate';
    }

    // we build a form to collect some parameters from the users
    public function buildForm(\Symfony\Component\Form\FormBuilderInterface $builder)
    {
        $builder->add('date_from', DateType::class, array(
            'label' => "Born after this date",
            'data'  => new \DateTime(),
            'attr'  => array('class' => 'datepicker'),
            'widget'=> 'single_text',
            'format' => 'dd-MM-yyyy',
        ));
        
        $builder->add('date_to', DateType::class, array(
            'label' => "Born before this date",
            'data'  => new \DateTime(),
            'attr'  => array('class' => 'datepicker'),
            'widget'=> 'single_text',
            'format' => 'dd-MM-yyyy',
        ));
        
    }
   
    // the form created above must be validated. The process of validation
    // is executed here. This function is added by the interface 
    // `ExportElementValidatedInterface`, and can be ignore if there is 
    // no need for a validation
    public function validateForm($data, ExecutionContextInterface $context)
    { 
        $date_from = $data['date_from'];
        $date_to   = $data['date_to'];
        
        if ($date_from === null) {
            $context->buildViolation('The "date from" should not be empty')
                //->atPath('date_from')
                ->addViolation();
        }
        
        if ($date_to === null) {
            $context->buildViolation('The "date to" should not be empty')
                //->atPath('date_to')
                ->addViolation();
        }
        
        if (
            ($date_from !== null && $date_to !== null)
            &&
            $date_from >= $date_to
        ) {
            $context->buildViolation('The date "date to" should be after the '
                . 'date given in "date from" field')
                ->addViolation();
        }
    }


    // here, we alter the query created by Export
    public function alterQuery(\Doctrine\ORM\QueryBuilder $qb, $data)
    {
        $where = $qb->getDQLPart('where');
        // we create the clause here
        $clause = $qb->expr()->between('person.birthdate', ':date_from', 
            ':date_to');

        // we have to take care **not to** remove previous clauses...
        if ($where instanceof Expr\Andx) {
            $where->add($clause);
        } else {
            $where = $qb->expr()->andX($clause);
        }
        
        $qb->add('where', $where);
        // we add parameters from $data. $data contains the parameters from the form
        $qb->setParameter('date_from', $data['date_from']);
        $qb->setParameter('date_to', $data['date_to']);
    }

    // here, we create a simple string which will describe the action of
    // the filter in the Response
    public function describeAction($data, $format = 'string')
    {
        return array('Filtered by person\'s birtdate: '
            . 'between %date_from% and %date_to%', array(
                '%date_from%' => $data['date_from']->format('d-m-Y'),
                '%date_to%'   => $data['date_to']->format('d-m-Y')
            ));
    }


}

Todo

Continue to explain the export framework