sfStatisticsPlugin - 0.1.11

Symfony plugin for reports creating with charts and data tables through Google Chart API

You are currently browsing
the website for symfony 1

Visit the Symfony2 website


« Back to the Plugins Home

Signin


Forgot your password?
Create an account

Tools

Stats

advanced search
Information Readme Releases Changelog Contribute
Show source | Show as Markdown

sfStatistics plugin

The sfStatisticsPlugin is an easy-to-use tool for reports (chart and data table) creating based on the Propel Model through Google Chart API.

Plugin works in Symfony 1.0, Symfony 1.1 and Symfony 1.2 with Propel 1.2 or Propel 1.3.

This plug-in consists of:

  • one module

  • one toolkit library

Limitations

Now plugin works only with MySQL when data grouping type is 'date' because it uses mysql built-in date functions.

Later plugin will work with all other databases.

Installation

Installation for sf 1.1 and sf 1.2

$ php symfony plugin:install sfStatisticsPlugin --release=0.1.11

Installation for sf 1.0

$ php symfony plugin-install http://plugins.symfony-project.com/sfStatisticsPlugin

Alternatively, if you don't have PEAR installed, you can download the latest package attached to this plugin's wiki page and extract it under your project's plugins/ directory.

Enable the new module in your application, via the settings.yml file.

// в myproject/apps/frontend/config/settings.yml
all:
  .settings:
    enabled_modules: [sfStatistics](default,)

Clear the cache to enable the autoloading to find the new classes:

$ php symfony cc

Start using the new module by making a request to:

http://myproject/frontend_dev.php/sfStatistics

Interface with the sfStatisticsPlugin

Immediately after installing module sfStatistics dosn't show any reports. You must configure your project for reports' generating through your Propel Model.

Plug-in allows to group data by date and by value.

Below there is example propel model for displaying plug-in abilities which contains information about users and cars from different cities:

propel:
  city:
    id:
    title:        varchar(255)

  user:
    id:
    name:         varchar(255)
    age:          integer
    city_id:      { type: integer, foreignTable: city, foreignReference: id, onDelete: setnull }
    created_at:

  car:
    id:
    model:        varchar(255)

  car_city:
    car_id:       { type: integer, foreignTable: car, foreignReference: id, onDelete: cascade, primaryKey: true }
    city_id:      { type: integer, foreignTable: city, foreignReference: id, onDelete: cascade, primaryKey: true }
    created_at:

Data grouping by date

Insert into app.yml some configuration data:

all:
  sfStatistics: 
    categories: 
      users: 
        name: Users
        items: 
          users_registration_by_day: 
            name: User registration statistic by day
            model_parameters: 
              class_y: user
              column_y: id
              column_x: created_at
              type: date
              increment: day
              aggregation_method: count
              period_column: created_at
            chart_parameters:
              type: line
              size: 600x300
              bg: ffffff
              fg: ff9900
              label_length: 20

This configuration creates report group with name "Users" which contains one report "User registration statistic by day".

Model_parameters section contains parameters for data extraction. Parameter class_y specifies database table whiсh contains data, column_y specifies table selected field, column_x – grouping field with increment step. Parameter aggregation_method specifies grouping function. It can be any MySQL grouping function (count, sum, max, min, avg and other). Parameter period_column specifies table field which is created_at field.

Such report configuration is interpreted in query which is below:

SELECT count(user.id), LEFT(user.created_at, 10) as dte from user group by dte;

Increment parameter can be the day, weekday (from monday to sunday), week, month, year.

Chart_parameters section contains chart building parameters. Type field specifies chart form: line, pie or bar.

Size parameter specifies chart size. Optimal ratio is 2:1. But for pie chart optimal ratio is 3:1.

Bg parameter specifies background color, fg — foreground color, label_length — maximum label length, if label length is longer than label_length, it cuts and joins with dots at the end.

Data grouping by value

For report creating with data grouping by value parameter type in model_parameters section must take up value.

Add information about reports with data grouping by value in file app.yml:

all:
  sfStatistics: 
    categories: 
      users: 
        name: Users
        items: 
          users_registration_by_day: 
            name: User registration statistic by day
            model_parameters: 
              class_y: user
              column_y: id
              column_x: created_at
              type: date
              increment: day
              aggregation_method: count
              period_column: created_at
            chart_parameters:
              type: line
              size: 600x300
              bg: ffffff
              fg: ff9900
              label_length: 20
          users_age_by_city: 
            name: Average user age by city
            model_parameters: 
              class_y: user
              column_y: age
              column_y_measure: years
              class_x: city
              column_x: city_id
              column_x_foreign: id
              column_x_title: title
              type: value
              aggregation_method: avg
            chart_parameters:
              type: bar
              size: 600x300
              bg: ffffff
              fg: ff9900
              label_length: 20
              approximation_factor: 0.1
          cars_by_city: 
            name: Car statistic by city
            model_parameters: 
              class_y: car_city
              column_y: car_id
              class_x: city
              column_x: city_id
              column_x_foreign: id
              column_x_title: title
              type: value
              aggregation_method: count
            chart_parameters:
              type: pie
              size: 600x250
              bg: ffffff
              fg: ff9900
              label_length: 20
              approximation_factor: 0.1

Configuration of reports users_age_by_city and cars_by_city shows one-to-many relationship using, parameter class_x contains joined table name, parameter column_x_foreign — associated field in joined table, parameter column_x_title - field in joined table which values is used as labels in chart.

Parameter column_y_measure contains unit of measure.

All value which is smaller approximation_factor * maximum value is sumed up and displayed with label "Other".

Such report configuration is interpreted in query which is below:

SELECT avg(user.age), user.city_id as dte, city.title from user, city where user.city_id = city.id group by dte;
SELECT count(car_city.car_id), car_city.city_id as dte, city.title from car_city, city where car_city.city_id = city.id group by dte;

Parameter criteria allows to create own Criteria object with additional parameters. You must specify class method which returns Criteria object:

all:
  sfStatistics: 
    categories: 
      users: 
        name: Users
        items: 
          users_registration_by_day: 
            name: User registration statistic by day
            model_parameters: 
              class_y: user
              column_y: id
              column_x: created_at
              type: date
              increment: day
              aggregation_method: count
              criteria: UserPeer::getCriteriaForRegistrationChart
              period_column: created_at
            chart_parameters:
              type: line
              size: 600x300
              bg: ffffff
              fg: ff9900
              label_length: 20
          ...

Section default contains global default values:

all:
  sfStatistics: 
    default: 
      item: users_registration_by_day
      chart_parameters: 
        type: line
        bg: ffffff
        fg: ff9900
        label_length: 20
        size: 600x300
        approximation_factor: 0.1
    categories: 
      users: 
        name: Users
        items: 
          users_registration_by_day: 
            name: User registration statistic by day
            model_parameters: 
              class_y: user
              column_y: id
              column_x: created_at
              type: date
              increment: day
              aggregation_method: count
              period_column: created_at
              criteria: UserPeer::getCriteriaForRegistrationChart
          users_age_by_city: 
            name: Average user age by city
            model_parameters: 
              class_y: user
              column_y: age
              column_y_measure: years
              class_x: city
              column_x: city_id
              column_x_foreign: id
              column_x_title: title
              type: value
              aggregation_method: avg
            chart_parameters:
              type: bar
          cars_by_city: 
            name: Car statistic by city
            model_parameters: 
              class_y: car_city
              column_y: car_id
              class_x: city
              column_x: city_id
              column_x_foreign: id
              column_x_title: title
              type: value
              aggregation_method: count
            chart_parameters:
              type: pie
              size: 600x250

Some parameters have default values:

Section `chart_parameters`: 
  type: line
  bg: ffffff
  fg: ff9900
  label_length: 26
  size: line - 700x350, pie - 700x230, bar - 700x350
  approximation_factor: 0.1

Section `model_parameters`:
  column_y: id
  column_x: <<class_x>>_id #if `class_x` parameter exists
  column_x_foreign: id     #if `class_x` parameter exists
  column_x_title: title    
  type: value
  aggregation_method: count
  increment: day
  period_column: created_at

Final configuration in app.yml is such:

all:
  sfStatistics: 
    default: 
      item: users_registration_by_day
      chart_parameters: 
        label_length: 20
        size: 600x300
    categories: 
      users: 
        name: Users
        items: 
          users_registration_by_day: 
            name: User registration statistic by day
            model_parameters: 
              class_y: user
              column_x: created_at
              type: date
              criteria: UserPeer::getCriteriaForRegistrationChart
          users_age_by_city: 
            name: Average user age by city
            model_parameters: 
              class_y: user
              column_y: age
              column_y_measure: years
              class_x: city
              aggregation_method: avg
            chart_parameters:
              type: bar
          cars_by_city: 
            name: Car statistic by city
            model_parameters: 
              class_y: car_city
              column_y: car_id
              class_x: city
              type: value
            chart_parameters:
              type: pie
              size: 600x250

!! Usefull: parameters column_y, column_x, column_x_title, column_x_foreign can contain schema field name (created_at, id), model class constants (UserPeer::CREATED_AT, CarCityPeer::CAR_ID) and strings ('user.id', 'now() - user.age'). This possibility allows put in this parameters fields of other tables, which you can join in class method from criteria parameter.

The module is fully i18n'ed, and the plugin comes with English, Russian and German translations.

TODO

  • Three chart types: line, pie and bar
  • 2 grouping types: by date and by value
  • Reports grouping
  • Configuration of approximation factor
  • Configuration chart form
  • Fully i18n'ed