# 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