okvpn/graph-widget-bundle

Build graph bases on database query

dev-master 2018-07-06 16:31 UTC

Requires

• php: >=7.0
• oro/platform: ^2.3|^3.0

Requires (Dev)

• phpunit/phpunit: ^5.7

MIT

• Tsykun Vladimir

• OroCrm
• Oro Platform
• Graph widget

README

This OroPlatform bundle provider functionality to build line graph based on custom native sql query.

Install

Install using composer following the official Composer documentation:

1. Install via composer:

composer require okvpn/graph-widget-bundle

1. Run oro platform update

# symfony 3
rm -r var/cache/*
php bin/console oro:platform:update --force

# symfony 2
rm -r app/cache/*
php app/console oro:platform:update --force

Configure

1. Create database integration.

Note: it's better to create a read-only database user. Dashboard widget use separate connection for database.

1. Add widget to dashboard.

1. Update widget configuration

SQL Format

You can build line or multi-line plot. The data will be fetch from the columns: "x", "y", "line". Where: "x" - x-axis, "y" - y-axis and "line" - grouping column to build multi-line plot.

Example of line plot.

SELECT avg_status as y, created_at as x
FROM okvpn_radar
WHERE time > extract(epoch from now()) - 86400
ORDER BY id DESC LIMIT 1000;

Results of execute sql query

Example of multi-line plot.

SELECT tmp.x, tmp.y, tmp.line FROM (
  SELECT ROUND(AVG(avg_status), 3) AS y, created_at::date AS x, 'AVG' AS line
  FROM okvpn_radar
  WHERE created_at > now() - INTERVAL '50 day' AND type = 'UMMN' GROUP BY x
  UNION ALL (
    SELECT ROUND(MAX(avg_status), 3) AS y, created_at::date AS x, 'MAX' AS line
    FROM okvpn_radar
    WHERE created_at > now() - INTERVAL '50 day' AND type = 'UMMN' GROUP BY x
  )
) tmp ORDER BY tmp.x DESC;

Results of execute sql query

Permissions

You can disable update sql query using ACL permission okvpn_sql_query.

License

MIT License. See LICENSE.