okvpn/graph-widget-bundle

Build graph bases on database query

GraphWidgetBundle for OroPlatform

This OroPlatform bundle provider functionality to build line graph based on custom native sql query., _(*1)

Install

Install using composer following the official Composer [documentation][2]:, _(*2)

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., _(*3)

, _(*4)

1. Add widget to dashboard.

, _(*5)

1. Update widget configuration

, _(*6)

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., _(*7)

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, _(*8)

, _(*9)

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, _(*10)

, _(*11)

Permissions

You can disable update sql query using ACL permission okvpn_sql_query., _(*12)

, _(*13)

License

MIT License. See LICENSE., _(*14)