11 users Sign-in to change your status

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

• Homepage
• Repository
• Download package
• Browse source

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.

Developers

Name	Status	Email
Ilyas Salikhov	lead	moc.liamg <<ta>> ffohkilas
Michael Klein	developer	ed.otnauqla <<ta>> nielk.leahcim

License

Copyright (c) 2008 Francois Zaninotto, Salikhov Ilyas

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Releases for sf 1.2

Version	License	API	Released
0.1.11beta	MIT license	0.1.1beta	01/02/2009
0.1.9beta	MIT license	0.1.1beta	23/10/2008

Releases for sf 1.1

Version	License	API	Released
0.1.11beta	MIT license	0.1.1beta	01/02/2009
0.1.9beta	MIT license	0.1.1beta	23/10/2008
0.1.7beta	MIT license	0.1.1beta	26/09/2008
0.1.6beta	MIT license	0.1.1beta	25/09/2008
0.1.5beta	MIT license	0.1.1beta	08/09/2008

Releases for sf 1.0

Version	License	API	Released
0.1.11beta	MIT license	0.1.1beta	01/02/2009
0.1.9beta	MIT license	0.1.1beta	23/10/2008
0.1.7beta	MIT license	0.1.1beta	26/09/2008
0.1.6beta	MIT license	0.1.1beta	25/09/2008
0.1.5beta	MIT license	0.1.1beta	08/09/2008

Join this plugin team

Other releases

Release 0.1.11 - 01/02/2009

Release 0.1.9 - 23/10/2008

Release 0.1.7 - 26/09/2008

Release 0.1.6 - 25/09/2008

Release 0.1.5 - 08/09/2008

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