Flot chart using AngularJS, Web API and Json

Introduction

I was evaluating javascript charting frameworks for my projects. I came across jQuery Flot library which supports a variety of graphs. In this article I have tried to plot a multi time axis graph using jQuery Flot library. It uses ASP.NET Web API to get data from database. The problem I faced was getting the data in a format that can be binded to the Flot graph. I had to play around a lot with Json data to finally get it working with Flot.

Using the code

Let me explain the scenario for this. The requirement was to show ticket data - total tickets received in a month, total tickets resolved in the month, total tickets pending for the month. This data was to be plot on a single graph with multiple axis. The code has three parts:

1. Database

2. WebAPI

3. Angular JS client application for displaying the Graph

Web API and Angular JS client application are separate Visual Studio Solutions. I have done this to understand how to enable CORS. 

First we will go through the database objects. The data is stored in a table and a view is created on top of it to get the data.

Table Script

CREATE TABLE [dbo].[Tickets1](
[TicketId] [int] NOT NULL, 
[CreationDate] [datetime] NOT NULL,
[TicketStatus] [smallint] NOT NULL,
[ResolvedDate] [datetime] NULL,
CONSTRAINT [PK_Tickets1] PRIMARY KEY CLUSTERED
( [TicketId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]
GO

View Script

CREATE VIEW [dbo].[MonthWiseTickets]
AS
SELECT        newid() AS Id, MonthYear, 
[1] AS ResolvedTickets, [0] AS OpenTickets, [1] + [0] AS TotalTickets
FROM         (SELECT     CAST(YEAR(creationdate) AS VARCHAR(4)) + '-' + CONVERT(varchar(2), 
creationdate, 101) + '-'+
'01'  AS MonthYear, creationdate, TicketStatus
FROM            Tickets1) up PIVOT (Count(CreationDate) FOR TicketStatus IN ([1], [0])) AS pvt

The above view gives the output in the below format

Our database is now set, next we create a Web API to get the data from the database. So we just go ahead and create a Web API project using Visual Studio.

Web API

The baisc structure of the project will be as below. First we will create the model class, then enable CORS and then create the controller to get data from database.

We create a Model Tickets.cs inside the Models folder, it will respresent the fileds of the SQL view.

public class Ticket

{

public string Id { get; set; }

public string MonthYear { get; set; }

public int ResolvedTickets { get; set; }

public int OpenTickets { get; set; }

public int TotalTickets { get; set; }

}

Then we need to Enable CORS so that the client applciation can call the Web API. For this we need to install the CORS package from NuGet. Once done open WebApiConfig.cs and add the below line of code.

// Web API configuration and services


config.EnableCors();

Now we need to create a controller to call the view from database. We add a new Controller file FlotChartController. Below is code for the controller, notice the first line where we are enabling the CORS for the client application.

Then we use ADO.NET to call the SQL view using the command object.

[EnableCors(origins: "http://localhost:52306", headers: "*", methods: "*")]

public class FlotChartController : ApiController

{

public dynamic Get()

{

List<Ticket> Tickets = new List<Ticket>();

ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["charts"];

using (SqlConnection con =

new SqlConnection(settings.ConnectionString.ToString()))

{

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "Select * From MonthWiseTickets order by CONVERT(datetime,monthyear)";

cmd.CommandTimeout = 120;

cmd.CommandType = CommandType.Text;

con.Open();

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

Ticket ticket = new Ticket();

ticket.Id = reader.GetGuid(0).ToString();

ticket.MonthYear = reader.GetString(1);

ticket.ResolvedTickets = reader.GetInt32(2);

ticket.OpenTickets = reader.GetInt32(3);

ticket.TotalTickets = reader.GetInt32(4);

Tickets.Add(ticket);

}

}

var ret = new[] {

new { label="Open Tickets", data = Tickets.Select(x=>new string[]{ x.MonthYear, x.OpenTickets.ToString() })},

new { label="Resolved Tickets", data = Tickets.Select(x=>new string[]{ x.MonthYear, x.ResolvedTickets.ToString() })},

new { label="Total Tickets", data = Tickets.Select(x=>new string[]{ x.MonthYear, x.TotalTickets.ToString() })},

};

return ret;

}

}

Now our server components are ready, we now need to create the client application to consume the Web API. In the next section we will see how to set up the AngularJS client applciation.

AngularJS

Below are the steps to set up the libraries needed for the client application:

1. Create an empty ASP.NET project in Visual Studio

2. Add Nuget packages for jQuery, AngularJS, and Flot

3. Add the module for Angular Flot

This is how your soulution will look after adding these libraries.

Now add a folder named App in the project and add two sub folders Controllers and Services.

Now let us start adding some code, create a app.js file and add below code. This will instantiate our app when the index page is loaded.

var app = angular.module('MorrisApp', ['ngRoute', 'angular-flot']);

var serviceBase = 'http://localhost:51419/';

app.constant('ngAuthSettings', {

apiServiceBaseUri: serviceBase,

});

Under services folder add flotService.js file, this will call the Web API we created earlier.

'use strict';

app.factory('flotService', ['$http', 'ngAuthSettings', function ($http, ngAuthSettings) {

var serviceBase = ngAuthSettings.apiServiceBaseUri;

var flotServiceFactory = {};

var _getMonthlyTicketsSummary = function () {

return $http.get(serviceBase + 'api/FlotChart').then(function (results) {

return results;

});

};

flotServiceFactory.getMonthlyTicketsSummary = _getMonthlyTicketsSummary;

return flotServiceFactory;

}]);

Now the main part writing the controller to call the service and plot the graph.

app.controller('FlotCtrlJson', ['$scope', 'flotService', function ($scope, flotService) {

//declare an array for month names to be dispaled on the x axis

var monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]

// generate the tool tip

function getTooltip(label, x, y) {

var date = new Date(x)

return label + " for " + monthNames[date.getMonth()] + " " + date.getFullYear() + " are " + y;

}

// set options for the flot graph

$scope.options = {

legend: {

container: "#legendjson",

show: true,

noColumns: 3,


},

axisLabels: {

show: true

},

xaxis: {

mode: "time", minTickSize: [1, "month"], timeformat: " %b %y",

axisLabel: "Month",

monthNames: monthNames,

//ticks: [[1,"Jan"],[2,"Feb"]]

},

yaxes: [{

position: "left",

axisLabel: "Total Tickets",

axisLabelUseCanvas: true,

axisLabelColour: "rgb(2,198,137)"

},

{

position: "right",

axisLabel: "Overdue Tickets",

axisLabelUseCanvas: true,

axisLabelColour: "#FF0000"

},

{

position: "right",

axisLabel: "Resolved Tickets",

axisLabelUseCanvas: true,

axisLabelColour: "#0062FF"

},

],

grid: {

hoverable: true //IMPORTANT! this is needed for tooltip to work

},

tooltip: true,

tooltipOpts: {

content: getTooltip

}

};

//var data3 = [[1420050600000, 6], [1422729000000, 5]];

// call the service to get the data in JSON format

flotService.getMonthlyTicketsSummary().then(function (results) {

// debugger;

var monthlyTicketsSummary = results.data;

var totalTickets = monthlyTicketsSummary[2].data;

var totalOverdueTickets = monthlyTicketsSummary[0].data;

var totalCompletedTickets = monthlyTicketsSummary[1].data;

var data6 = [];

var data7 = [];

var data8 = [];

// alert(new Date("2015-01-01").getTime())

for (j = 0; j < totalTickets.length; j++) {

var i = totalTickets[j].toString();

data5 = i.split(",");

data6 = data6 + "[" + new Date(data5[0]).getTime() + "," + data5[1] + "]" + ","

//data6[j] = "[5,10]"

}

data6 = JSON.parse("[" + data6.substring(0, data6.length - 1) + "]");

for (j = 0; j < totalOverdueTickets.length; j++) {

var i = totalOverdueTickets[j].toString();

data5 = i.split(",");

data7 = data7 + "[" + new Date(data5[0]).getTime() + "," + data5[1] + "]" + ","

//data6[j] = "[5,10]"

}

data7 = JSON.parse("[" + data7.substring(0, data7.length - 1) + "]");

for (j = 0; j < totalCompletedTickets.length; j++) {

var i = totalCompletedTickets[j].toString();

data5 = i.split(",");

data8 = data8 + "[" + new Date(data5[0]).getTime() + "," + data5[1] + "]" + ","

//data6[j] = "[5,10]"

}

data8 = JSON.parse("[" + data8.substring(0, data8.length - 1) + "]");

// set the data to be plotted on different y axes

$scope.dataset = [{

data: data6, label: 'Total Tickets', color: "rgb(2,198,137)", bars: {

show: true,

barWidth: 24 * 60 * 60 * 6000,

lineWidth: 1,

align: "center",

}

},

{

data: data7, label: ['Total Overdue Tickets'], yaxis: 2, color: "#FF0000", lines: {

show: true,

},

points: { fillColor: "#FF0000", symbol: "triangle", show: true, }

},

{

data: data8, label: ['Total Resolved Tickets'], yaxis: 3, color: "#0062FF", lines: {

show: true,

},

points: { fillColor: "#0062FF", symbol: "diamond", show: true, }

},

];

}

, function (error) {

alert(error.data);

}

);

}]);

All set now, let just run our API project and then run the Angular project from Visual Studio.

Points of Interest

I really had to spend time in figuring out how to convert the date I am getting in JSON to the format understood by Flot library.