This is Part 4 in a series on creating a dashboard in AngularJS. I'm blogging as I progressively create the dashboard, refining my Angular experience along the way.
Previously in Part 3, we added a KPI tile, improved on our tile rendering algorithm, and implemented a data service. The data service communicates with a partially-done back end to retrieve dashboard layout.
Today, we're going to:
- Add two more chart tiles (bar chart and pie chart).
- Build out more of our back end so that tile data is actually queried from the database.
- Do some refactoring and fixing of existing code.
But First, Some Refactoring
We've been moving pretty rapidly as we protoype more and more of our dashboard, and whenever that's the case it's important to step back from time to time and take stock. There are a few things in our code we'd like to fix or refactor.Fixing Drag-and-Drop
One of those areas is drag-and-drop. We implemented that right out of the box in Part 1, before we had even implemented any of our tiles. Since then, however, we've made all sorts of changes to our template and our controller; as well as changing our tile rendering approach to use an HTML table. Just where does this leave drag-and-drop? Having tested it out after Part 3, it's only partially working. Sometimes it's not re-rendering the dashboard after a drop. Clearly some refinement to our drag-and-drop code is needed.// Handler for start of tile drag.
function tile_dragstart(ev) {
ev.dataTransfer.dropEffect = "move";
var sourceId = getParentTileId(ev.target);
if (sourceId != null) {
ev.dataTransfer.setData("text/plain", sourceId);
}
else {
ev.stopPropagation(); // tile not found - stop drag
}
}
// Handler for tile dragover.
function tile_dragover(ev) {
ev.preventDefault();
ev.dataTransfer.dropEffect = "move";
}
// Handler for tile drop.
function tile_drop(ev, tiles) {
ev.preventDefault();
var destId = getParentTileId(ev.target);
if (destId != null) {
var sourceTileIndex = parseInt(ev.dataTransfer.getData("text").substring(5)) - 1;
var destTileIndex = parseInt(destId.substring(5)) - 1;
var ctrl = angular.element('#dashboard').scope().$$childHead.$ctrl;
ctrl.dragTile(sourceTileIndex, destTileIndex); // make the drag happen in the tile layout, and re-rended the dashboard
deferCreateCharts(); // re-render the charts, too.
}
else {
ev.stopPropagation(); // tile not found, cancel drop
}
}
// Given a drag element (which may or may not have an Id, and which may or may not be in a tile),
// return the tile Id that contains it. Or null, if not in a tile.
function getParentTileId(element) {
var ctrl = angular.element('#dashboard').scope().$$childHead.$ctrl;
for (var t = 1; t <= ctrl.tiles.length; t++) {
if (document.getElementById('tile-' + t.toString()).contains(element)) {
return 'tile-' + t.toString();
} // end if
} // next t
return null;
}
Recalling that we added the computeLayout function to the controller in Part 3, the controller's dragTile function now also invokes computeLayout. That ensures the updated dashboard HTML now always gets rendered. In the drop event handler we also call deferCreateCharts, which has the controller invoke the ChartService to run the JavaScript code needed to render the chart tiles. With these changes, drag-and-drop is happy once again. We can freely drag and drop tiles and now we're getting the expected experience. We'll need to check how well this is working on mobile devices, but for the desktop we're happy.Result: drag-and-drop is now working beautifully
Tile Rendering
Although we improved tile rendering in Part 3 (through the use of dynamically-computed tiles-across and use of an HTML table), the use of colspan didn't quite give us the economy of layout we expected. It turns out the HTML table will do a smarter job of rendering table cells and colspan attributes if we tell it how many columns there are going to be, using <colgroup> and <col> elements (line 6).<div class="dashboard-controller" window-size>
<div>
<div>{{$ctrl.title}} (chart provider: {{$ctrl.chartProvider}} | data provider: {{$ctrl.dataProvider}})</div>
<div class="dashboard-panel">
<table>
<colgroup><col width="200px" ng-repeat="unit in $ctrl.tileunits"></colgroup>
<tr ng-repeat="row in $ctrl.layoutView track by $index">
<td ng-repeat="tile in row" colspan="{{tile.width}}">
<div id="tile-{{tile.id}}"
class="tile" ng-class="tile.classes"
style="overflow: hidden"
draggable="true" ondragstart="tile_dragstart(event);"
ondrop="tile_drop(event);" ondragover="tile_dragover(event);">
As powerful as ng-repeat is, it can't execute a simple for loop. This posed a problem in getting the right number of <col> elements emitted. Ultimately, I had to add a tileunits array to the controller, updated by configLayout (lines 30-33).angular.module('dashboard').component('dashboard', {
templateUrl: '/components/dashboard/dashboard.template.html',
controller: [
'$scope', '$window', '$http', 'ChartService', 'DataService', function DashboardController($scope, $window, $http, ChartService, DataService) {
var self = this;
var ctrl = angular.element('#dashboard').scope().$$childHead.$ctrl;
self.chartProvider = ChartService.chartProvider();
self.dataProvider = DataService.dataProvider();
self.tilesacross = 2; // number of tile units across - set dynamically as window size chnges
self.tileunits = [1, 2]; // used by template to render table <col> elements.
self.tablecolumns = function () {
return $scope.tablecolumns;
}
...
// Compute layout based on current screen dimensions (self.tablecolumns()) and generate updated tile layout
// Inputs: self.tablecolumns() ........... number of tiles across.
// Ouptuts: self.layoutView .............. updated array of tile rows, each element an array of tiles for a row [[ tile1, ... tileN ], ... [ tileX, ... tileZ ] ]
self.computeLayout = function () {
if (self.tiles == null) return;
var numcols = self.tablecolumns();
if (numcols < 1) numcols = 1;
// This is used in template to render things like table <col> elements.
self.tilesacross = numcols;
self.tileunits = [];
for (var u = 0; u < numcols; u++) {
self.tileunits.push(u);
}
var newlayout = [];
...
Controller with tileunits property added
By adding <col> elements to our HTML template, we're already getting a smarter rendering by the browser--and we haven't even added any smart fill logic yet.
Result: dashboard layout with a mixture of tile widths is now rendering much better.
Formatted Tile Values
For some tiles, like charts, we need to provide numeric values--but often we want those values formatted, for example with currency formatting such as $150.00. We're adding a new optional property to tile named format. If format is present, it is expected to contain a string with an embedded {0}. The {0} will be replaced with the data value. This gives us a simple way to have format masks, as in '${0}'. We'll see if we can make that more sophisticated over time.We've updated the GoogleChartProvider class to take tile.format into consideration for column charts. Now, we see money represented properly on the bars. If we find other opportunities for formatted values, we now have the format property available in the tile.
Result: GoogleChartProvider column chart tiles now support formatted values
Tile Links
Early on in this series, we made the point that the intent of the dashboard was informational, and that the place to go and do work (advanced sorting/searching, interaction, data changes) was in the application pages that these tiles would link to. We haven't done anything to make tiles linkable yet, so we'll do that now. All it takes are two easy changes: 1) adding a new link property to our tile definitions, and 2) including an anchor tag in our tile rendering. The link property contains a URL, and the template changes are below (lines 26 and 30).
<div class="dashboard-controller" window-size>
<div>
<div>{{$ctrl.title}} (chart provider: {{$ctrl.chartProvider}} | data provider: {{$ctrl.dataProvider}})</div>
<!--<span after-render="deferCreateCharts"></span>-->
<div class="dashboard-panel">
<table>
<colgroup><col width="200px" ng-repeat="unit in $ctrl.tileunits"></colgroup>
<tr ng-repeat="row in $ctrl.layoutView track by $index">
<td ng-repeat="tile in row" colspan="{{tile.width}}">
<!-- Placeholder Tile (data not loaded) -->
<div id="tile-{{tile.id}}" ng-if="!tile.haveData"
class="tile" ng-class="tile.classes"
style="overflow: hidden"
draggable="true" ondragstart="tile_dragstart(event);"
ondrop="tile_drop(event);" ondragover="tile_dragover(event);">
<div class="hovermenu"><i class="fa fa-ellipsis-h" aria-hidden="true"></i></div>
<div style="overflow: hidden; white-space: nowrap">{{tile.title}}</div>
<div style="position: relative; height: 100%"></div></div>
<!-- Populated tile (data loaded) -->
<div id="tile-{{tile.id}}" ng-if="tile.haveData"
class="tile" ng-class="tile.classes"
style="overflow: hidden"
draggable="true" ondragstart="tile_dragstart(event);"
ondrop="tile_drop(event);" ondragover="tile_dragover(event);">
<div class="hovermenu"><i class="fa fa-ellipsis-h" aria-hidden="true"></i></div>
<a ng-href="{{tile.link}}" style="color: inherit; text-decoration: inherit;">
<div style="overflow: hidden; white-space: nowrap">{{tile.title}}</div>
<div style="position: relative; height: 100%">
...tile markup...
</a>
</div> <!-- end tile -->
</td>
</tr>
</table>
</div>
</div>
dashboard.template.html Changes to Support Tile Linking
Our template HTML has been updated to output an a tag with ng-href. The effect of this is a tile will transfer to the defined application page when clicked, and we'll get an automatic hand cursor over the tile on hover. Tiles without a link property won't go anywhere when clicked.
Our sample application is not a real application, but we'll add some mock application pages under /App/Customers, /App/Orders, etc. so we can see the tile link feature in action when we run the code. For this reason, you'll see an MVC AppController.cs and a /Views/App folder in the solution with dummy pages /App/Customers, /App/Stores, /App/Orders, and /App/Employees.
Our sample application is not a real application, but we'll add some mock application pages under /App/Customers, /App/Orders, etc. so we can see the tile link feature in action when we run the code. For this reason, you'll see an MVC AppController.cs and a /Views/App folder in the solution with dummy pages /App/Customers, /App/Stores, /App/Orders, and /App/Employees.
Result: GoogleChartProvider column chart tiles now support formatted values
This is probably a good time to review the properties that can now exist in a tile:
| color | background color (red, orange, yellow, green, cyan, blue, purple, gray) | |
| columns | An array of column information for the tile | |
| dataSource | The name of the query for retrieving tile data | |
| height | tile height (1 or 2) | |
| label | A display value used by some tile types | |
| link | The URL the tile links to when clicked | |
| title | the tile's display title | |
| type | the type of the tile (counter, bar, column, donut, kpi pie, table) | |
| width | tile width (1 or 2) | |
| value | Value(s) to show in the tile |
New Bar Chart Tile
Two posts ago, we added some chart tiles--a donut chart and a column (vertical bar) chart--implemented with a choice of two different chart libraries (Google Visualization and Chart.js). These libraries can generate many kinds of charts, so we want to regularly add additional chart tiles as we move forward. Today we'll add a bar chart, a horizontal edition of the column chart we already have.
These are the steps needed to implement the bar chart tile:
1. Define a new tile type of 'bar'. Aside from the type, this tile structure will be identical to the 'column' tile created in Part 2.
2. In the template, add markup with ng-if conditions of chartProvider equals Google' and type equals 'bar'. This will otherwise be identical markup to the column tile, except that the class will be 'barchart'.
3. Add code to the controller's createCharts function to scan the DOM for elements in class 'barchart' and invoke ChartService.drawBarChart() to render the charts.
4. In the Google Chart Service (google.chart.service.js), add new function drawBarChart(). The implementation is practically identical to drawColumnChart(), except the call is to google.visualization.BarChart(...) instead of .ColumnChart(...).
5. Repeat steps 2-4 for the Chart.js chart provider. The new drawBarChart() function is practically identical to drawColumnChart(), but we tell Chart.js the chart type is 'horizontalBar' instead of 'bar'.
All of this is very simple because we're simply following the same pattern already established for the column chart tile. With this work done, we'll update our demo.data.service.js definition of the Precious Metals tile so its type is now 'bar' instead of 'column'. After clearing cache and running the project with the Google chart provider enabled, here's what we get. This looks pretty good.
Bar Chart rendered by Google Visualization API
Now, we switch to the Chart.js chart provider (by commenting out the Google script elements and uncommenting the Chart.js script elements in index.html). This time, we get a bar chart from Chart.js that looks very good. To make the bar charts more similar, we'd like to find a way to show bar values on the bars in the Chart.js version--but we're still working on that.
Bar Chart rendered by Chart.js
All in all, that was pretty easy. Let's do one more chart tile.
New Pie Chart Tile
In the same way, we can easily create a pie chart tile since we already have a donut tile in place. THe only difference between a pie chart and a donut chart is the hole taken out of the center of the donut chart.
These are the steps needed to implement the pie chart tile:
1. Define a new tile type of 'pie'. Aside from the type, this tile structure will be identical to the 'donut' tile created in Part 2.
2. In the template, add markup with ng-if conditions of chartProvider equals 'Google' and type equals 'pie'. This will otherwise be identical markup to the donut tile except that the class will be 'piechart'.
3. Add code to the controller's createCharts function to scan the DOM for elements in class 'piechart' and invoke ChartService.drawPieChart() to render the charts.
4. In the Google Chart Service (google.chart.service.js), add new function drawPieChart(). The implementation is practically identical to drawDonutChart(), except there is no donut center section (pieHole: 0.4) property defined in the structure handed to the API.
5. Repeat steps 2-4 for the Chart.js chart provider. We tell chart.js the chart type is 'pie' rather than 'donut'.
Again, very straightfoward because we're following an established pattern. With this work done, we'll update our demo.data.service.js definition of the Time Managements tile so its type is now 'pie' instead of 'donut'. After clearing cache and running the project with the Google chart provider enabled, here's what we get.
Pie Chart rendered by Google Visualization
Pie Chart rendered by Chart.js
Now we have 4 kinds of chart tiles available in the dashboard (donut, pie, column, bar), each with a dual implementation based on which ChartService you enable. We'll continue to add more chart types over time.
Expanding the Back End for Live Application Data
In part 3, we started our back end: we created a Dashboard database with dashboard layout tables, created a sister SqlServerDataService alternatuve to the DemoDataService, and an ASP.NET MVC controller for database actions. All of that got us database-based dashboard layout. However, the data on the tiles is still just sitting in the tile definitions (properties like value, columns, and label).
Our next step, then, will be to retrieve tile data from application tables. To make this happen, we'll be doing the following:
- Define a DashboardTile reference table, with one entry for each type of dashboard tile.
- Define some application data (tables like Employee, Store, Customer, and Order).
- Define a DashboardQuery table for holding available data queries for dashboard tiles.
- Revise MVC DashboardController.Index action to execute queries to fecth tile data. .
- Update the SqlServerDataService's getTileLayout function to call the back-end Query action to get data for each tile.
New DashboardTile Table
Although we already have a DashboardLayoutTile table that lists the tile in a particular dashboard layout, we don't have a reference table of the available tile types--which will be crucial later on when we add UI for adding new tiles. Each tile definition will include:
- TileType : this value is the same as tile.type we've had in our tile structure all along. So far, it can have these possible values: counter, donut, pie, bar, column, table, kpi.
- TileName : the name for the type of tile, such as "Pie Chart".
- PropertyNames : a string containing a list of properties the tile requires, separated by a vertical bar. For example: type|title|width|height|color|dataSource|columns|value
- ValueType : a string explaining the type of value the tile needs.
ValueType needs some explanation. We've already seen that different tiles take different kinds of values--all the way from a counter tile that expects a simple number like 15, all the way to a table tile that requires an array of arrays. In addition, for some tile types the data source query also needs to set additional tile values such as label or columns. In order to safeguard against bad combinations of data source query and tile type, we need to know what kind of query (or queries) each tile expects, and that's what ValueType is. Possible values right now are:
- number: a single number. Example: value: 5
- number-array: an array of numbers. Example: value: 1, 6, 7, 10, -4.3
- table: an array of rows, where each row is an array of column values. Example: value: [ [ 'Mike', 10000, true ], ['Jim,' 8000, false ] ... ] | The back end will also set the tile's columns property, base on the column names and types in thre result set. Example: columns: [['Name], 'string], [Salary', 'number:'] ... ]
- kpi-set: a single numeric value; a label for the value; and an array of four column names. Example: value: 50 | label: $7,500 | columns: $0, $5K, $10K, $15K
DashboardTile table defining Available Tile Types
With these definitions, we'll be able to implement a safe configuration UI for the tiles. We won't be creating that UI today, but we've laid the foundation for it.
Defining Application Data
To simulate some actual application data in our sample project, we'll add these new tables to our sample database and populate them:
- Employee : table of employees (EmployeeId, Lastname, Firstname, Title, Startdate IsActive, ManagerEmployeeId)
- Store: table of store locations (StoreId, StoreName, ManagerEmployeeId, Street, City, State, PostalCode, Country, Phone)
- Customer : table of customers (CustomerId, Street, City, State, Country, PostalCode, Country, Phone)
- Order : table of orders (OrderId, StoreId, CutomerId, OrderDate, Subtotal, Tax, Total, IsOpen, ShipDate)
The T-SQL to create these tables and populate them is included in the same project script file dashboard_database.sql. Here's our mock application data. It's not much, but it's enough for us to build out running actual data queries to populate our tiles.
Mock Application Data Added to Database
New DashboardQuery Table
Next, we'll add a table of dashboard queries. Each record in DashboardQuery will contain:
- Name : A friendly name. When we later implement the UI for tile configuration, we'll need to present the user with a friendly list of available data.
- ValueType : A value type, indicating what kind of value the query brings back. These are the same ValueType values described earlier above for the DashboardTile table (number, number-array, table, etc).
- Query : The T-SQL query to run.
Note that the query in Query must return data in the form expected by ValueType. That is, if ValueType is 'number', the query should return a single numeric value; if ValueType is 'number-array', the query should return a sequence of numbers; and so on.
Here is our initial DashboardQuery data--with more to come.
Now that we have some queries defined, we're going to update some of our tile definitions in the DashboardLayoutTileProperty table. Previously, the dataSource property for each tile was 'Inline'. Now, we're going to update our tile definitions to use some of the above queries. Below is our updated llst of tiles in the DashboardLayout tables.
Dashboard Layout (DashboardLayoutTileProperties)
Notice we no longer have value or columns properties defined for our tiles--these will come from querying the data sources when the dashboard is loaded.
DashboardQiuery Table
Now that we have some queries defined, we're going to update some of our tile definitions in the DashboardLayoutTileProperty table. Previously, the dataSource property for each tile was 'Inline'. Now, we're going to update our tile definitions to use some of the above queries. Below is our updated llst of tiles in the DashboardLayout tables.
Dashboard Layout (DashboardLayoutTileProperties)
Tile Validation and Data Separation
Up until now, a tile definition also included its data--they were provided in value and other properties right when the rest of the tile was defined. Our Angular DashboardController would call the DataService.getTileLayout() function to retrieve an array of tile definitions, including the data.
We now want to separate the tile definition from the tile data, and have the data loaded from elsewhere (such as the Order table). For now, we'll still make the single call to DataService.getTileLayout(), but we're going to change how that function works. In the DataSevice, we won't be changing anything--but in the MVC back-end that returns the collection of tiles, we'll now be querying the database to get the tile data.
To start on that path, we'll first be adding a new property to each tile named haveData. This is an internal property that can be true or false. If false, the tile does not yet have data. We've modified the template to show a ghost tile that is empty and in gray (regardless of its assigned color) when there's no data. When tile.haveData is true, the tile will render as usual.
We now want to separate the tile definition from the tile data, and have the data loaded from elsewhere (such as the Order table). For now, we'll still make the single call to DataService.getTileLayout(), but we're going to change how that function works. In the DataSevice, we won't be changing anything--but in the MVC back-end that returns the collection of tiles, we'll now be querying the database to get the tile data.
To start on that path, we'll first be adding a new property to each tile named haveData. This is an internal property that can be true or false. If false, the tile does not yet have data. We've modified the template to show a ghost tile that is empty and in gray (regardless of its assigned color) when there's no data. When tile.haveData is true, the tile will render as usual.
We have a second use for haveData: we'll set it to false if we don't think a tile has a correct and complete structure. Each of our tiles has a slightly different mix of properties, and some of those (like value) have expected structures that vary from one tile type to another. What we don't want is for one incorrect tile to kill rendering of the entire dashboard. So, we're going to add some correctness checks in our controller's initialization code, right after it retrieves the tile definitions from the Data Service. If a tile doesn't look right, we'll set its haveData property to false and the tempalte will render it as a gray ghost tile--avoiding the JavaScript errors that would surely result if the tile were attempted to be rendered. Our validation code is in a new function, validateTile(tile).
// Validate a tile. If not valid, tile.haveData is set to false so it will not attempt to be rendered.
self.validateTile = function (tile) {
if (tile.hasOwnProperty('value')) { // if no value property is present in the tile object, invalidate the tile
tile.haveData = true;
}
else {
tile.haveData = false; // tile is missing a value property, so invalidate it.
}
if (!tile.hasOwnProperty('type')) {
tile.haveData = false; // tile is missing a type property, so invalidate it.
}
else {
console.log('validateTile 01 tile:' + tile.id + ', type:' + tile.type);
switch (tile.type) {
case 'bar':
case 'column':
case 'counter':
case 'donut':
case 'kpi':
case 'pie':
case 'table':
break;
default:
console.log('validateTile 02 invalidating tile');
tile.haveData = false; // tile type is unrecognized, so invalidate it.
break;
} // end switch
} // end else
if (!tile.haveData) {
tile.classes = tile.sizeClass + ' tile-gray';
}
}
validateTile controller method
MVC Controller Changes to Query for Tile Data
We're now in a position to modify the MVC DashboardController. It still has just one action method, Index, which returns a collection of tiles. We're now going to modify the controller to find the query for each tile and fetch the data.We've made a number of changes to the controller since Part 3:
- Added code to Index() to fetch data for each tile, first by looking up the DashboardQuery record associated with the tile's query name (in its dataSource property).
- A new method LoadTileData does the actual work of performing a query and then setting tile.value in the format the tile needs.
- Updated the Tile class to have an indexer. This lets us get or set a tile property using the syntaxt tile["propertyName"].
Let's take a look at some of the new code. In Index(), lines 92-106 ensure the data for each tile is loaded, unless the tile's dataSource is 'inline'. The method LoadTileData (lines 119-306) does the work of looking up the tile's dataSource query from the DashboardQuery table, executing the query, and setting tile properties such as value from the results.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace Dashboard.controllers
{
public class DashboardController : Controller
{
// /Dashboard .... return dashboard layout as JSON
public JsonResult Index()
{
int dashboardId = -1;
Dashboard dashboard = new Dashboard()
{
DashboardName = "Home",
Username = "default", // TODO: check for username, if not found fall back to default
Tiles = new List<Tile>()
};
try
{
using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Database"]))
{
conn.Open();
// Load the default home dashboard.
String query = "SELECT DashboardId FROM DashboardLayout WHERE DashboardName='Home' AND Username='default'";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
dashboardId = Convert.ToInt32(reader["DashboardId"]);
}
}
}
if (dashboardId != -1) // If found a dashboard...
{
// Load dashboard layout
query = @"SELECT prop.* FROM DashboardLayoutTile tile
INNER JOIN DashboardLayoutTileProperty prop
ON tile.DashboardId=prop.DashboardId AND prop.Sequence=tile.Sequence
WHERE tile.DashboardId=@DashboardId
ORDER BY prop.Sequence,prop.PropertyName";
Tile tile = null;
int lastSeq = 0;
int seq = 0;
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
seq = Convert.ToInt32(reader["Sequence"]);
if (seq != lastSeq) // starting a new sequence (tile)
{
lastSeq = seq;
if (tile != null)
{
dashboard.Tiles.Add(tile);
}
tile = new Tile()
{
Sequence = seq,
Properties = new List<TileProperty>()
};
}
tile.Properties.Add(new TileProperty(Convert.ToString(reader["PropertyName"]), Convert.ToString(reader["PropertyValue"])));
} // end while have tile property
dashboard.Tiles.Add(tile); // add final tile to tiles collection
}
}
}
// Get the data for each tile. If dataQuery is 'inline', tile already has data.
if (dashboard != null && dashboard.Tiles != null)
{
foreach (Tile tile in dashboard.Tiles)
{
if (tile.Properties != null)
{
if (tile["dataSource"] != "inline")
{
LoadTileData(conn, tile, tile["dataSource"]);
}
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION: " + ex.Message);
}
return Json(dashboard, JsonRequestBehavior.AllowGet);
}
// Lookup and execute the query for a tile, and set its value.
private void LoadTileData(SqlConnection conn, Tile tile, String queryName)
{
String dataQuery = null;
String valueType = null;
String query = "SELECT Query,ValueType FROM DashboardQuery WHERE Name=@Name";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@Name", queryName);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
dataQuery = Convert.ToString(reader["Query"]);
valueType = Convert.ToString(reader["ValueType"]);
}
}
}
// If a data query was found, execute it.
if (dataQuery != null)
{
try
{
using (SqlCommand cmd = new SqlCommand(dataQuery, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
switch(valueType)
{
// number: expect query to return just one numeric value, set into tile.value.
case "number":
if (reader.Read())
{
tile["value"] = Convert.ToString(reader[0]);
}
break;
// number-array: expect query to return one bar name and one bar value per row.
// sets tile.value to [ value-1, ... valueN ] array of numbers. sets tile.column to array of column names.
case "number-array":
{
String columnValue = "[";
String value = "["; // [ (open outer array)
int rows = 0;
List<String> columns = new List<String>();
List<String> types = new List<String>();
// Read through the query results, and build up columns property and value proprty for bar/column tile.
// Expected: two columns per row: <column-name> <value>
while (reader.Read())
{
if (rows>0)
{
value = value + ",";
columnValue = columnValue + ",";
}
columnValue = columnValue + "'" + Convert.ToString(reader[0]) + "'"; // Get the bar column
value = value + Convert.ToString(reader[1]); // Get the bar value
rows++;
} // end while reader.Read()
value = value + "]";
columnValue = columnValue + "]";
tile["columns"] = columnValue;
tile["value"] = value;
}
break;
// kpi-set: query should return <numeric-value>, <label>, <low-label>, <1/3-label>, <2/3-label>, <high-label>
// tile properties set: value, label, columns (4 values).
case "kpi-set":
{
String value = "";
String labelValue = "";
String columnValue = "[";
if (reader.Read())
{
value = Convert.ToString(reader[0]);
labelValue = Convert.ToString(reader[1]);
columnValue = "[" + "'" + Convert.ToString(reader[2]) + "',";
columnValue = columnValue + "'" + Convert.ToString(reader[3]) + "',";
columnValue = columnValue + "'" + Convert.ToString(reader[4]) + "',";
columnValue = columnValue + "'" + Convert.ToString(reader[5]) + "']";
tile["value"] = value;
tile["label"] = labelValue;
tile["columns"] = columnValue;
} // end while reader.Read()
}
break;
// table: query should returns rows of column values, which will coped into tile.value as [ [row-1-array], ... [row-N-array] ]
// column names and types will be detected from the query results and set into tile.columns as [ [ col-1-name, col-1-type ], ... [col-N-name, col-N-type] ]
case "table":
{
String value = "["; // [ (open outer array)
int rows = 0;
List<String> columns = new List<String>();
List<String> types = new List<String>();
while (reader.Read())
{
if (rows==0)
{
// Load columns
String columnValue = "[";
for (int c = 0; c < reader.FieldCount; c++)
{
columns.Add(reader.GetName(c));
types.Add(reader.GetDataTypeName(c));
if (c>0)
{
columnValue = columnValue + ",";
}
String type = "string";
switch(reader.GetDataTypeName(c))
{
case "int":
case "decimal":
case "float":
type = "number";
break;
case "bit":
case "boolean":
type = "boolean";
break;
case "date":
case "datetime":
type = "string";
break;
default:
type = "string";
break;
}
columnValue = columnValue + "['" + reader.GetName(c) + "','" + type + "']";
}
columnValue = columnValue + "]";
tile["columns"] = columnValue;
}
else
{
value = value + ",";
}
value = value + "["; // start inner array
int colindex = 0;
foreach (String column in columns)
{
if (colindex!=0)
{
value = value + ",";
}
if (reader[column] == DBNull.Value)
{
value = value + "null";
}
else
{
switch (types[colindex])
{
case "date":
value = value + "'" + Convert.ToDateTime(reader[column]).ToString("MM-dd-yyyy") + "'";
break;
default:
value = value + "'" + Convert.ToString(reader[column]) + "'";
break;
}
}
colindex++;
}
value = value + "]"; // end inner array
rows++;
} // end while reader.Read()
value = value + "]"; // end outer array
tile["value"] = value;
}
break;
default:
// set tile to have no data
break;
}
}
}
}
catch (SqlException ex)
{
String msg = ex.Message;
Console.WriteLine(msg);
}
}
}
private String TileProperty(Tile tile, String propName)
{
String value = null;
if (tile != null && tile.Properties != null)
{
foreach (TileProperty prop in tile.Properties)
{
if (prop.PropertyName == propName)
{
value = prop.PropertyValue;
break;
}
}
}
return value;
}
}
#region Dashboard Layout Objects
public class Dashboard
{
public String DashboardName { get; set; }
public String Username { get; set; }
public List<Tile> Tiles { get; set; }
public Dashboard()
{
DashboardName = "Home";
Tiles = new List<Tile>();
}
public Dashboard(String name)
{
DashboardName = name;
Tiles = new List<Tile>();
}
}
public class Tile
{
public int Sequence { get; set; }
//public String Username { get; set; }
public List<TileProperty> Properties { get; set; }
public String this[String index]
{
get
{
if (this.Properties != null)
{
foreach(TileProperty prop in this.Properties)
{
if (prop.PropertyName == index)
{
return prop.PropertyValue;
}
}
}
return null;
}
set
{
if (this.Properties== null)
{
this.Properties = new List<TileProperty>();
}
foreach (TileProperty prop in this.Properties)
{
if (prop.PropertyName == index)
{
prop.PropertyValue = value;
return;
}
}
this.Properties.Add(new TileProperty(index, value));
}
}
}
public class TileProperty
{
public String PropertyName { get; set; }
public String PropertyValue { get; set; }
public TileProperty() { }
public TileProperty(String name, String value = null)
{
PropertyName = name;
PropertyValue = value;
}
}
#endregion
}
In LoadTileData (lines 119-306), the processing of query results and setting of tile values is fairly involved. A switch case statement runs separate logic for each query ValueType, as follows:- number : the query is expected to return a single numeric value, which is set into tile.value.
- number-array : the query is expected to return one bar name and one bar value per row. This ends up in the tile as a tile.columns array of column names, and a tile.value array of numbers.
- kpi-set : the query is expected to return one numeric value, one label for the value, and four labels for the KPI range. These are set into tile.value, tile.label, and tile.columns.
- table : the query is expected to return multiple rows, each of which contains multiple column values. Tile.value is set to an array of row items, where each row item is an array of column values. Tile.columns is set to tn array of column names and types, taken from the query results.
Seeing the Dashboard Run with Application Data
We're finally ready to see our dashboard run without hard-coded data. Making sure index.html is set to use the SqlServerDataProvider, we run the project and our dashboard appears. We can see that the tile definitions are coming from the database DashboardLayout tables. We can also see that the data is coming from the database, using the queries in the DashboardQuery table. This is a big milestone for us! While there's plenty more we can and will do to refine and improve our Angular dashboard, we can now view actual application data with it.
Dashboard rendered with Application Data
Where will we go from here? It seems some UI for configuration tiles can now be implemented, allowing users to add / configure / remove tile to personalize their dashboard.
Summary
Today we did all of the following:- Refactoring and Tile Improvements
- Fixed drag-and-drop to work better by refining the drag and drop event handler code.
- Improved tile rendering by adding a colgroup element to the template's HTML table.
- Added a format property to tiles, and implemented value formatting for GoogleChartProvider bar and column charts.
- Added a link property to tiles, and implemented tile click-through.
- New Chart Tiles
- Added two new chart tiles (bar chart and pie chart), each a variation on the existing column chart and pie chart tiles. We implemented these tiles in both of our chart services.
- Back End
- Added sample application data to the database so we would have data sources to query.
- Added tile validation to the Angular controller
- Added MVC controller code to run queries to get tile values when the data service requests the tile layout.
We're making good progress, but how are we feeling about Angular?
- I like it. Much of Angular is useful and well thought-out, and is becoming intuitive.
- I am having to clear cache on web pages A LOT when trying out changes. I guess that's a trade-off so that Angular performs well.
- I still run into gotchas. In today's work, after setting up the back-end to really query application data for tiles, I found the table tile wasn't rendering 3 of its rows. There was an Angular complaint about duplicate ng-repeat indexes (3 of them) in the web console. I got around this by adding 'track by $index' to the end of the ng-repeat condition for table cells.
Download Source
https://drive.google.com/open?id=0B4734rvcufGGWTRqX1B4cjVEWkk
1 comment:
I have pleased to read about Bar/Pie Charts and Back-end Data Queries! Great article thanks and keep it up!
Post a Comment