Dashboard
The purpose of a dashboard within an ERP is to provide each user with key data (KPIs) referring to their job. This way, each user can quickly see how they are performing and take appropriate action when necessary. In order to achieve this the dashboard needs to be easily configurable, and extendable. The proposed webERP dashboard fulfils all these criteria.
The main dashboard screen itself is found by clicking on the dashboard icon in the header of each webERP screen. The screen consists of two rows of the mini reports, with a maximum of 6 mini reports allowed for each user. Reports can be removed by clicking on the “X” on the right hand side of the header row for the mini report in question. New reports can be added by using the drop down list at the bottom of the screen. Each report is designed to represent one KPI.
Each report is stored in the dashboard folder.
The reports are updated dynamically, so if another uses enters a transaction on another computer, even in a different country, it would immediately show in your dashboard.
CREATE TABLE dashboard_scripts (
id int(12) NOT NULL AUTO_INCREMENT,
scripts varchar(78) NOT NULL,
pagesecurity int(11) NOT NULL DEFAULT 1,
description text NOT NULL,
PRIMARY KEY (id)
)`
This table stores the details of each of the mini reports that are available. As can be seen below people are encouraged to add their own reports. Each report has a description, and a page security token which governs whether the user can have that report on their dashboard.
CREATE TABLE dashboard_users (
id int(10) NOT NULL AUTO_INCREMENT,
userid varchar(20) NOT NULL,
scripts varchar(255) NOT NULL,
PRIMARY KEY (id)
)`
This table stores which dashboard reports each user will see on their dashboard. The users can configure their own dashboards, but they can only see preports that their security profile allows them to.
A few sample reports are included with webERP, but it is envisaged that companies will wish to extend the dashboard by writing their own mini reports. This only requires a small amount of knowledge regarding of SQL and HTML, and can easily be picked up.
To facilitate this webERP comes with a template designed to be used for creating new mini reports. This template can be found at dashboard/template.php. The first stem is to copy this file and rename the copy for our new report. It must be stored in the dashboard/ sub folder, and have an extension of .php. For this example we will create a report showing the most recent goods received. Let us call this report latest_grns.php, and store it in the dashboard sub folder.
The first section of the template file is as follows:
<?php
/**********************************************************/
$PathPrefix = '../';
if (basename($_SERVER['SCRIPT_NAME']) != 'index.php') {
require_once ($PathPrefix . 'includes/session.php');
$DashBoardURL = $RootPath . '/index.php';
}
$ScriptTitle = _('Enter the script title here');
$SQL = "SELECT id FROM dashboard_scripts WHERE scripts='" . basename(basename(__FILE__)) . "'";
$DashboardResult = DB_query($SQL);
$DashboardRow = DB_fetch_array($DashboardResult);
echo '<table class="DashboardTable">
<thead>
<tr>
<th colspan="5">
<div class="CanvasTitle">', $ScriptTitle, '
<a class="CloseButton" href="', $DashBoardURL, '?Remove=', urlencode($DashboardRow['id']), '" target="_parent" title="', _('Remove this applet from dashboard'), '" id="CloseButton" href="#">X</a>
</div>
</th>
</tr>';
All we need to do here is to change the title of the script, so change the value of $ScriptTitle to _(‘Latest goods received notes’). The next section of the template is as follows:
/**********************************************************************/
$SQL = "";
$DashboardResult = DB_query($SQL);
/* Create an SQL SELECT query to produce the data you want to show
* and store the result in $DashboardResult
*/
This is where some basic knowledge of SQL is needed. We need to create a simple SELECT statement that retrieves the columns that we require. Looking at the grns table in the database we need the columns grnno, deliverydate, itemcode, itemdescription, and qtyrecd. The SQL statement then becomes
“SELECT grnno,deliverydate,itemcode,itemdescription,qtyrecd FROM grns ORDER BY deliverydate DESC LIMIT 15”
In other words we pull the 5 fields from table grns, sequence them by descending order of delivery date, and just retrieve the most recent 15 GRNs. This is then assigned to the $SQL variable in the script.
The next part of the template is for the column headings:
/**********************************************************************/
/* Create the table/column headings for the output that you want to show */
Within each
pair we need to enter a header for every column. This will look something like this:echo '<tr>
`<th>Column 1</th>`
`<th>Column 2</th>`
`.................`
`.................`
`<th>Final Column</th>`
`</tr>`
</thead>';
The next part of the template displays the individual lines of the report:
while ($MyRow = DB_fetch_array($DashboardResult)) {
echo '<tr class="striped_row">
`<td>Text Data 1</td>`
`<td class="number">Numerical Data 2</td>`
`....................`
`....................`
`<td>Final Data</td>`
`</tr>';`
}
/* Iterate through the rows of data returned by our SQL and create table
* rows for each record
*/
Within each <td></td> pair we need to enter a variable containing the data for every column. This will look something like this:
The final section of the template just ensures that the table gets closed properly:
/**********************************************************************/
echo '</tbody>
`</table>';`
/* Don't forget to close off the table */
?>
The final stage, is to tell webERP about the new mini report, and to configure it’s security settings, and description. This is done via the ‘Configure the Dashboard’ option on the ‘Setup’ menu.
On this screen any scripts that are not currently installed are listed in the drop down section in the input section:
As can be seen only our new script is waiting to be installed so we select that.
Next we choose what security token to allocate to this script. This decides who will be able to have it on their dashboard
Finally we input a description for this mini report. This description will appear in the drop down box on the actual Dashboard screen.