Monday, November 13, 2017

An AngularJS Dashboard, Part 6: Admin Tile Actions and Confirmation Dialogs

NOTE: for best results, view the http: version of this page (else you won't get syntax highlighting).

This is Part 6 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 5, we added a user interface for configuring tiles, dashboard layout persistence, and tile menu actions. We also admitted a lot of the code was new and hadn't gone through a deep test and debug cycle.

Today, we're going to:

  • Polish the existing tile actions with confirmation dialogs.
  • Add a new tile action, Make Default Layout, for setting the default dashboard (available only to administrators).
  • Pass user information and privileges from the MVC back end to the Angular code.
  • Improve the appearance of the Table tile.
  • Release updated code with bug fixes.
Here' a view of what we'll be ending up with today:


Confirmation Dialogs

Last time, we added a number of tile menu actions. Some of these, like Remove Tile or Reset Dashboard, delete parts of your saved dashboard layout. We shouldn't be taking potentially destructive actions without being sure it's what the user wants, so we're now going to add confirmation dialogs for these actions.

The approach we'll be taking to confirmation dialogs is to use leverage Bootstrap, which has been part of our soluton all along. We'll be re-using the same confirmation dialog for each confirmation, so let's add that markup to our HTML template. The dialog has id confirm-reset-modal.
<!-- Confirmation dialog -->

<div class="modal fade" tabindex="-1" role="dialog" aria-labelledby="confirm-label" aria-hidden="true" id="confirm-reset-modal">
    <div class="modal-dialog modal-md">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
                <h4 class="modal-title" id="confirm-label">Confirmation Message</h4>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-success" id="modal-btn-yes">Yes</button>
                <button type="button" class="btn btn" id="modal-btn-no">No</button>
            </div>
        </div>
    </div>
</div>
HTML template with confirmation dialog markup

Previously, the tile menu actions simply invoked functons (removeTile, etc.) that called counterpart functions in the controller, taking immediate action. Now, we'll be calling confirmation functions first that will ask the user if they are sure they want to take the selected action. Only in the case of a Yes response will the action take place. Our first step, then, is to update the tile menu markup to call confirmation functions.
<!-- Populated tile (data loaded) -->
<div id="tile-{{tile.id}}" ng-if="tile.haveData"
        class="tile" ng-class="tile.classes" ng-style="{ 'background-color': $ctrl.tileColor(tile.id), 'color': $ctrl.tileTextColor(tile.id) }"
        style="overflow: hidden"
        draggable="true" ondragstart="tile_dragstart(event);"
        ondrop="tile_drop(event);" ondragover="tile_dragover(event);">
    <div class="dropdown" style="height: 100%">
        <div class="hovermenu">
            <i class="fa fa-ellipsis-h dropdown-toggle" data-toggle="dropdown" aria-hidden="true"></i>
            <ul class="dropdown-menu" style="margin-left:-150px !important">
                <li><a id="tile-config-{{tile.id}}" href="#" onclick="configureTile(this.id);"><i class="fa fa-gear" aria-hidden="true"></i>  Configure Tile</a></li>
                <li><a href="#" onclick="configureTile('0');"><i class="fa fa-plus-square-o" aria-hidden="true"></i>  Add Tile</a></li>
                <li><a id=tile-remove-{{tile.id}}" href="#" onclick="removeTileConfirm(this.id);"><i class="fa fa-trash-o" aria-hidden="true"></i>  Remove Tile</a></li>
                <li><a id=tile-reset-{{tile.id}}" href="#" onclick="resetDashboardConfirm();"><i class="fa fa-refresh" aria-hidden="true"></i>  Reset Dashboard</a></li>
                <li ng-if="$ctrl.user.IsAdmin"><a id=tile-reset-{{tile.id}}" href="#" onclick="saveDefaultDashboardConfirm();"><i class="fa fa-check-square-o" aria-hidden="true"></i>  Make Default Layout</a></li>
            </ul>
        </div>
Tile menu updated to call confirmation functions

We've switched to calling confirmation functions for Remove Tile, Reset Default Dashboard, and our new action Make Default Layout.

Remove Tile

The confirmation function for Remove Tile is shown below. Line 4 sets the confirmation message; lines 6-15 set handlers for the Yes and No buttons; and line 17 displays the confirmation dialog. If Yes, is selected, the original removeTile function is executed to remove the tile and the modal dialog is hidden. If No is selected, no action ensues and the modal dialog is hidden.
// removeTile : Remove a tile.

function removeTileConfirm(id) {
    $('#confirm-label').html('Are you sure you want to remove this tile?');

    $("#modal-btn-yes").on("click", function () {
        $("#modal-btn-yes").off();
        $("#modal-btn-no").off();
        removeTile(id);
        $("#confirm-reset-modal").modal('hide');
    });

    $("#modal-btn-no").on("click", function () {
        $("#confirm-reset-modal").modal('hide');
    });

    $("#confirm-reset-modal").modal('show');
}

function removeTile(id) {
    var scope = angular.element('#dashboard').scope();
    var ctrl = angular.element('#dashboard').scope().$$childHead.$ctrl;

    scope.$evalAsync(function () {
        return ctrl.removeTile(id);
    });
}

Updated JavaScript code for Remove Tile

When the user selectes Remove Tile from the tile menu, they now see this confirmation dialog:

Remove Tile Confirmation Dialog

Clicking Yes proceeds to remove the tile, by calling the original removeTile function.

Reset Dashboard

Following the exact same pattern, here is the code for Reset Dashboard.
// resetDashboard : Restore default dashboard by deleting user's saved custom dashboard.

function resetDashboardConfirm() {
    $('#confirm-label').html('Are you sure you want to reset your dashboard to the default layout?');

    $("#modal-btn-yes").on("click", function () {
        $("#modal-btn-yes").off();
        $("#modal-btn-no").off();
        resetDashboard();
        $("#confirm-reset-modal").modal('hide');
    });

    $("#modal-btn-no").on("click", function () {
        $("#confirm-reset-modal").modal('hide');
    });

    $("#confirm-reset-modal").modal('show');
}

function resetDashboard() {
    var scope = angular.element('#dashboard').scope();
    var ctrl = angular.element('#dashboard').scope().$$childHead.$ctrl;

    scope.$evalAsync(function () {
        return ctrl.resetDashboard();
    });
}
When the Reset Dashboard tile action is taken, the user sees this confirmation dialog.

Reset Dashboard Confirmation Dialog

Clicking Yes resets the dashboard, by calling the original resetDashboard function.

New Tile Action: Make Default Layout

We're adding a new tile action named Make Default Layout. Up till now, we've seen that we have a default layout defined in the database; and that we can persist a customized layout for a user. What's been lacking is a way to take a customized layout and make it the new default for all users. That's what this new action will do.


// saveDefaultDashboard : Save current layout as the default layout for all users.

function saveDefaultDashboardConfirm() {
    $('#confirm-label').html('Are you sure you want to make this layout the default for all users?');

    $("#modal-btn-yes").on("click", function () {
        $("#modal-btn-yes").off();
        $("#modal-btn-no").off();
        saveDefaultDashboard();
        $("#confirm-reset-modal").modal('hide');
    });

    $("#modal-btn-no").on("click", function () {
        $("#confirm-reset-modal").modal('hide');
    });

    $("#confirm-reset-modal").modal('show');
}

function saveDefaultDashboard() {
    console.log('saveDefaultDashboard');
    var scope = angular.element('#dashboard').scope();
    var ctrl = angular.element('#dashboard').scope().$$childHead.$ctrl;

    scope.$evalAsync(function () {
        return ctrl.saveDefaultDashboard();
    });
}


Make Default Layout Confirmation Dialog

Below is the new controller function saveDefaultDashboard. This code leverages existing controller code and Data Service code for saving a dashboard, but an isDefault flag has been added to DataService.saveDashboard. When a true is passed to DataService.saveDashboard, that means we are updating the default dashboard rather than the user's customized dashboard.

// saveDefaultDashboard : make user's dashboard the default dashboard for all users

self.saveDefaultDashboard = function () {
    self.wait(true);
    DataService.saveDashboard(self.tiles, true);
    toastr.options = {
        "positionClass": "toast-top-center",
        "timeOut": "1000",
    }
    toastr.info('Default Dashboard Layout Saved');
    self.wait(false);
};
Controller saveDefaultDashboard function

In DataService.saveDashboard (SQL Server version shown below), the only change is the addition of the isDefault flag, which is passed in the structure sent to the SaveDashboard MVC action.

// -------------------- saveDashboard : updates the master layout for tiles (returns tiles array). If isDefault=true, this becomes the new default dashboard layout. ------------------

self.saveDashboard = function (newTiles, isDefault) { 

    var Dashboard = {
        DashboardName: null,
        IsAdmin: false,
        Username: null,
        Tiles: [],
        Queries: null,
        IsDefault: isDefault
    };

    var tile = null;
    var Tile = null;

    // create tile object with properties

    for (var t = 0; t < newTiles.length; t++) {
        tile = newTiles[t];
        Tile = {
            Sequence: t+1,
            Properties: [
                { PropertyName: 'color', PropertyValue: tile.color },
                { PropertyName: 'width', PropertyValue: parseInt(tile.width) },
                { PropertyName: 'height', PropertyValue: parseInt(tile.height) },
                { PropertyName: 'title', PropertyValue: tile.title },
                { PropertyName: 'type', PropertyValue: tile.type },
                { PropertyName: 'dataSource', PropertyValue: tile.dataSource },
                { PropertyName: 'columns', PropertyValue: JSON.stringify(tile.columns) },
                { PropertyName: 'value', PropertyValue: JSON.stringify(tile.value) },
                { PropertyName: 'label', PropertyValue: tile.label },
                { PropertyName: 'link', PropertyValue: tile.link },
                { PropertyName: 'format', PropertyValue: tile.format }
            ]
        };
        Dashboard.Tiles.push(Tile);
    };

    var request = $http({
        method: "POST",
        url: "/Dashboard/SaveDashboard",
        data: JSON.stringify(Dashboard),
        headers : {
            'Content-Type': 'application/json'
        }

    });

    return (request.then(handleSuccess, handleError));
};

DataService.saveDashboard

In the MVC SaveDashboard action below, the Dashboard object now has an IsDefault bool flag. When SaveDashboard is called, it's either for the traditional purpose of saving the user's custom dashboard layout (IsDefault=false), or for saving a new default dashboard that applies to everyone (IsDefault=true). Lines 6-7 customize values used in INSERT database queries depending on what the target dashboard is. In line 222, the same thing happens for DeleteDashboard.
[HttpPost]
public void SaveDashboard(Dashboard dashboard)
{
    try
    {
        int priority = dashboard.IsDefault ? 1 : 2;
        String username = dashboard.IsDefault ? "default" : CurrentUsername();

        DeleteDashboard(dashboard.IsDefault);  // Delete prior saved dashboard (if any) for user.

        // Check whether an existing dashboard is saved for this user. If so, delete it.

        int dashboardId = -1;

        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Database"]))
        {
            conn.Open();

            // Add dashboard layout root record

            String query = "INSERT INTO DashboardLayout (DashboardName, Username, Priority) VALUES (@DashboardName, @Username, @priority); SELECT SCOPE_IDENTITY();";

            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("@DashboardName", "Home");
                cmd.Parameters.AddWithValue("@Username", username);
                cmd.Parameters.AddWithValue("@Priority", priority);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        dashboardId = Convert.ToInt32(reader[0]);
                    }
                }
            }

            if (dashboardId!=-1) // If root record added and we have an id, proceed to add child records
            {
                // Add DashboardLayoutTile records.

                int sequence = 1;
                foreach (Tile tile in dashboard.Tiles)
                {
                    query = "INSERT INTO DashboardLayoutTile (DashboardId, Sequence) VALUES (@DashboardId, @Sequence)";

                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.ExecuteNonQuery();
                    }
                    sequence++;
                } // next tile

                // Add DashboardLayoutTileProperty records.

                sequence = 1;
                foreach (Tile tile in dashboard.Tiles)
                {
                    query = "INSERT INTO DashboardLayoutTileProperty (DashboardId, Sequence, PropertyName, PropertyValue) VALUES (@DashboardId, @Sequence, @Name, @Value)";

                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.Parameters.AddWithValue("@Name", "color");
                        if (tile["color"] == null)
                        {
                            cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Value", tile["color"]);
                        }
                        cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.Parameters.AddWithValue("@Name", "height");
                        if (tile["height"] == null)
                        {
                            cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Value", tile["height"]);
                        }
                        cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.Parameters.AddWithValue("@Name", "width");
                        if (tile["width"] == null)
                        {
                            cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Value", tile["width"]);
                        }
                        cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.Parameters.AddWithValue("@Name", "type");
                        if (tile["type"] == null)
                        {
                            cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Value", tile["type"]);
                        }
                        cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.Parameters.AddWithValue("@Name", "title");
                        if (tile["title"] == null)
                        {
                            cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Value", tile["title"]);
                        }
                        cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.Parameters.AddWithValue("@Name", "dataSource");
                        if (tile["dataSource"] == null)
                        {
                            cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Value", tile["dataSource"]);
                        }
                        cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.Parameters.AddWithValue("@Name", "label");
                        if (tile["label"] == null)
                        {
                            cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Value", tile["label"]);
                        }
                        cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.Parameters.AddWithValue("@Name", "columns");
                        if (tile["columns"] == null)
                        {
                            cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Value", tile["columns"]);
                        }
                        cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.Parameters.AddWithValue("@Name", "value");
                        if (tile["value"] == null)
                        {
                            cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Value", tile["value"]);
                        }
                        cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                        cmd.Parameters.AddWithValue("@Sequence", sequence);
                        cmd.Parameters.AddWithValue("@Name", "link");
                        if (tile["link"] == null)
                        {
                            cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Value", tile["link"]);
                        }
                        cmd.ExecuteNonQuery();
                    }
                    sequence++;
                } // next tile
            }

            conn.Close();
        } // end SqlConnection
    }
    catch(Exception ex)
    {
        Console.WriteLine("EXCEPTION: " + ex.Message);
    }
}

// DeleteDashboard : Delete user's saved custom dashboard. If isDefault is true, deletes the default dashboard.

private void DeleteDashboard(bool isDefault)
{
    try
    {
        String username = isDefault ? "default" : CurrentUsername();

        // Check whether an existing dashboard is saved for this user. If so, delete it.

        int dashboardId = -1;

        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Database"]))
        {
            conn.Open();

            // Load the dashboard.
            // If the user has a saved dashboard, load that. Otherwise laod the default dashboard.

            String query = "SELECT TOP 1 DashboardId FROM DashboardLayout WHERE DashboardName='Home' AND Username=@Username";

            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@Username", username);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        dashboardId = Convert.ToInt32(reader["DashboardId"]);
                    }
                }
            }

            if (dashboardId != -1) // If found a dashboard...
            {
                // Delete dashboard layout tile property records

                query = "DELETE DashboardLayoutTileProperty WHERE DashboardId=@DashboardId";

                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                    cmd.ExecuteNonQuery();
                }

                // Delete dashboard layout tile records

                query = "DELETE DashboardLayoutTile WHERE DashboardId=@DashboardId";

                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                    cmd.ExecuteNonQuery();
                }

                // Delete dashboard layout record

                query = "DELETE DashboardLayout WHERE DashboardId=@DashboardId";

                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                    cmd.ExecuteNonQuery();
                }
            }
            conn.Close();
        } // end SqlConnection
    }
    catch (Exception ex)
    {
        Console.WriteLine("EXCEPTION: " + ex.Message);
    }
}

MVC DashboardController SaveDashboard Action

This takes care of the functionality for Make Default Layout, except for one other matter: we don't want just any user to be able to set the default layout--that should be reserved for administrators.

Reserving Make Default Layout for Administrators

In order to enforce Make Default Layout only being avaiable for administrators, we need to know whether our user is an administrator. In the MVC DashboardController, our pre-existing CurrentUsername function is now accompanied with a CurrentUserIsAdmin function. In the demo project, the return values are hard-coded; in your real application, your authentication/authorization mechanism would be used to determine identity and privileges.

#region Authentication and Authorization

// Use these methods to simulate different users, by changing the username or admin privilege. In a real app, your authN/authZ system handles this.

// Return current username. Since this is just a demo project that lacks authentication, a hard-coded username is returned.

private String CurrentUsername()
{
    //return "Mike.Jones";
    //return "Karen.Carpenter";
    return "John.Smith";
}

// Return true if current user is an administrator. Since this is just a demo project that lacks authentication, a hard-coded role assignment is made.

private bool CurrentUserIsAdmin()
{
    switch (this.CurrentUsername())
    {
        case "John.Smith":
            return true;
        default:
            return false;
    }
}

#endregion
MVC Dashboard Methods for Username and Administrator Status

We need to pass the user information on to the Angular side of things. This is done with a new MVC action named GetUser. 
// /Dashboard/GetUser .... returns username and admin privilege of cucrrent user.

[HttpGet]
public JsonResult GetUser()
{
    User user = new User()
    {
        Username = CurrentUsername(),
        IsAdmin = CurrentUserIsAdmin()
    };
    return Json(user, JsonRequestBehavior.AllowGet);
}
MVC Dashboard GetUser Action

There is a matching getUser function in the DataService that the controller uses to get this information.
// -------------------- getUser : return user information.

self.getUser = function () {

    var url = '/Dashboard/GetUser';

    var request = $http({
        method: "GET",
        url: url,
    });

    return (request.then(getUser_handleSuccess, handleError));
};
DataService.getUser function

The HTML template uses ng-if to conditionally show the Make Default Layout menu option: it only appears if the current user is an administrator.
<div class="hovermenu">
    <i class="fa fa-ellipsis-h dropdown-toggle" data-toggle="dropdown" aria-hidden="true"></i>
    <ul class="dropdown-menu" style="margin-left:-150px !important">
        <li><a id="tile-config-{{tile.id}}" href="#" onclick="configureTile(this.id);"><i class="fa fa-gear" aria-hidden="true"></i>  Configure Tile</a></li>
        <li><a href="#" onclick="configureTile('0');"><i class="fa fa-plus-square-o" aria-hidden="true"></i>  Add Tile</a></li>
        <li><a id=tile-remove-{{tile.id}}" href="#" onclick="removeTileConfirm(this.id);"><i class="fa fa-trash-o" aria-hidden="true"></i>  Remove Tile</a></li>
        <li><a id=tile-reset-{{tile.id}}" href="#" onclick="resetDashboardConfirm();"><i class="fa fa-refresh" aria-hidden="true"></i>  Reset Dashboard</a></li>
        <li ng-if="$ctrl.user.IsAdmin"><a id=tile-reset-{{tile.id}}" href="#" onclick="saveDefaultDashboardConfirm();"><i class="fa fa-check-square-o" aria-hidden="true"></i>  Make Default Layout</a></li>
    </ul>
</div>
Use of ng-if to show menu option for administrator users


Improving the Table Tile

Our Table tile works well enough, but it's kind of crammed. It could do with an expanded layout, with more space padding in the cells. But doing that will make it even harder for the table content to fit in the tile space at times--it really needs a horizontal scroll bar.

In our dashboard.less file, we've updated the styles for table tbody elements to scroll horizontally when necessary; and added greated padding for table cells.
.tile tbody {
    color: black;
    background-color: white;
    height: 100%;
    overflow-y: auto;    /* vertical scroll when needed */
    overflow-x: auto;    /* horizontal scroll when needed */
    font-size: 12px;
}

.tile td, .tile th {
    padding: 8px;
}
Updated table styles

Our improved tile markup is shown below. 
<!-- TABLE tile -->
<div ng-if="tile.type=='table'"
        style="text-align: left !important; padding: 16px; height: 100%">
    <div style="height: 100%; text-align: left !important">
        <table style="padding-bottom: 28px;">
            <tbody style="max-width: {{$ctrl.tileTableWidth(tile.id); }}">
                <tr>
                    <th ng-repeat="col in tile.columns">{{col[0]}}</th>
                </tr>
                <tr ng-repeat="row in tile.value">
                    <td ng-repeat="cell in row track by $index">
                        <div ng-if="tile.columns[$index][1]=='number'" class="td-right">{{cell}}</div>
                        <div ng-if="tile.columns[$index][1]!='number'">{{cell}}</div>
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
</div>
Updated tile markup

With the above changes, our table tile is much improved: it's more readable, and the content is scrollable.

Improved Table Tile

Summary


Today we did the following to polish and refine our earlier efforts:

  • Added confirmation dialogs for several tile actions.
  • Added a new tile action, Make Default Layout, for administrators.
  • Passed user information from the back end to the front end, including admin role.
  • Improved the Table tile's appearance and made it scrollable.
  • Released updated code with bug fixes.
Download Source
Download Zip
https://drive.google.com/open?id=1913tZaEmxSFj9StyMlKCynePpLw43T0O

Saturday, November 4, 2017

An AngularJS Dashboard, Part 5: Tile Configuration UI and Dashboard Persistence

NOTE: for best results, view the http: version of this page (else you won't get syntax highlighting).

This is Part 5 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 4, we added new chart tiles and completed enough of the back end that dashboard layout and data are queried from a database. However, we haven't yet done any work to allow the user to customize their dashboard and persist those changes for the future.

Today, we're going to:

  • Add a user interface for configuring tiles.
  • Add persistence of dashboard changes for the user.
  • Add tile actions to edit/add/remove tiles, and to reset the dashboard.

Here's what we'll be ending up with:

Dashboard with Configuration Dialog Open

Tile Configuration UI

For adding new tiles or configuring existing ones, the dialog shown below is used. It appears docked at right when the Configure Tile or Add New Tile actions are selected from a tile's menu.

Tile Configuration UI

When I first started implementing the UI, I approached it jQuery-style, where I used explicit code to set the input control values based on properties of the selected tile. Likewise, when the UI was saved similar code would copy input control values back into tile properties. Although this worked, I was dissatisfied with it. For one thing, changes to tiles were only visible when you saved your changes, not as you interacted with the form.

I realized I wasn't doing this the Angular way. I re-implemented using Angular directives, and I now have a form that is automatically linked to the tile being configured. As changes are made in the dialog, they immediately show visually which is much nicer for the user. I also eliminated a lot of code.

Just how is this linkage accomplished?

  • The visibility of the configuration dialog is controlled by the ng-style directive on line 4: when the controller's configuring variable is true, the dialog is visible; and when false, the dialog is hidden. 
  • The ng-model directive is used on many of the input controls (such as lines 7, 11, and 15). It sets a 2-way binding between a tile property and an input control: when one changes, the other is updated. 
  • There are a number of labels and input controls that only apply to certain tile types; ng-if is used to control their visibility (lines 48, 51, 52).
<!-- Configure Tile Panel -->

<div id="configTilePanel" style="position: fixed; width: 400px; right: 0; top: 0; background-color: #FFFFFF; border: 1px solid black"
        ng-style="{'visibility': $ctrl.configuring?'visible':'hidden'}">
    <div style="font-size: 18px !important; margin-bottom: 8px; background-color: navy; color: white"> {{$ctrl.configureTileTitle}}<div style="float: right;" onclick="cancelConfigureTile();">X </div></div>
    <table style="width: 100%">
        <tr><td>Title:  </td><td><input id="configTileTitle" style="width: 100%" ng-model="$ctrl.tiles[$ctrl.configIndex].title"></td></tr>
        <tr>
            <td>Type:  </td>
            <td>
                <select id="configTileType" style="width: 100%" ng-model="$ctrl.tiles[$ctrl.configIndex].type" ng-change="$ctrl.TileTypeChanged($ctrl.configIndex);">
                    <option value="counter">counter</option>
                    <option value="bar">bar chart</option>
                    <option value="column">column chart</option>
                    <option value="donut">donut chart</option>
                    <option value="kpi">kpi</option>
                    <option value="pie">pie chart</option>
                    <option value="table">table</option>
                </select>
            </td>
        </tr>
        <tr>
            <td>Color:  </td>
            <td>
                <input id="configTileColor" type="color" class="configTileColor" style="width: 100%" ng-model="$ctrl.tiles[$ctrl.configIndex].color" />
            </td>
        </tr>
        <tr>
            <td>Width:  </td>
            <td>
                <select id="configTileWidth" ng-model="$ctrl.tiles[$ctrl.configIndex].width">
                    <option value="1">1</option>
                    <option value="2">2</option>
                </select>
            </td>
        </tr>
        <tr>
            <td>Height:  </td>
            <td>
                <select id="configTileHeight" ng-model="$ctrl.tiles[$ctrl.configIndex].height">
                    <option value="1">1</option>
                    <option value="2">2</option>
                </select>
            </td>
        </tr>
        <tr><td>Data Source:  </td><td>
            <select id="configTileDataSource" ng-model="$ctrl.tiles[$ctrl.configIndex].dataSource" ng-change="$ctrl.UpdateTileData($ctrl.configIndex);">
                <option ng-repeat="query in $ctrl.queries" value="{{query.QueryName}}" ng-if="query.ValueType==$ctrl.dataSourceType()">{{query.QueryName}}</option>
            </select>
        </td></tr>
        <tr ng-if="$ctrl.tiles[$ctrl.configIndex].type=='counter' || $ctrl.tiles[$ctrl.configIndex].type=='kpi'"><td>Label:  </td><td><input id="configTileLabel" style="width: 100%" ng-model="$ctrl.tiles[$ctrl.configIndex].label" /></td></tr>
        <tr ng-if="$ctrl.tiles[$ctrl.configIndex].type=='counter' && $ctrl.tiles[$ctrl.configIndex].dataSource=='inline'"><td>Value:  </td><td><input id="configTileValue" style="width: 100%" /></td></tr>
        <tr><td>Link:  </td><td><input id="configTileLink" style="width: 100%" ng-model="$ctrl.tiles[$ctrl.configIndex].link" /></td></tr>
        <tr><td colspan="2" style="text-align: right">
            <button id="cancelButton" onclick="cancelConfigureTile();" class="btn" style="background-color: black; color: white">Cancel</button>  
            <button id="saveButton" onclick="saveConfigureTile(true);" class="btn" style="background-color: green; color: white">Save</button>
        </td></tr>
    </table>
</div>
Markup for Tile Configuration Dialog

Color Selection

For color selection, the dialog makes use of the Spectrum Color Picker, which does a nice job of letting users select from the palette of colors we've chosen to make available. Speaking of which, we've expanded our available tile colors.

Color Picker with an Expanded Palette

Many of the colors in the tile palette are the same as offered by the TFS dashboard, but I've also made a few changes. Here's a sampling of how some of these colors look on tiles.

Dashboard with some new Tile Colors

One other thing to note about tile colors. Previously, when we had a smaller color palette, we used classes to set tile colors--which allowed us to also set the foreground color to white or black, whichever gave the best contrast for the tile color. We've now made that automatic, with the addition of  a new controller function, tileTextColor(id), which uses an algorithm found on the Internet for determining the best contrast color for a background color.
// Return the best text color for a tile. Pass id ('1', '2', ...).

self.tileTextColor = function (id) {
    return self.textColor(self.tiles[parseInt(id) - 1].color);
}

// textColor('#rrggbb') : Given a background color (rrggbb or #rrggbb). Return '#000000' or '#FFFFFF'.
// Courtesty of https://24ways.org/2010/calculating-color-contrast/

self.textColor = function(hexcolor) {
    var color = hexcolor;
    if (color.length === 7) color = color.substr(1);
    var r = parseInt(color.substr(0, 2), 16);
    var g = parseInt(color.substr(2, 2), 16);
    var b = parseInt(color.substr(4, 2), 16);
    var yiq = ((r * 299) + (g * 587) + (b * 114)) / 1000;
    return (yiq >= 128) ? '#000000' : '#FFFFFF';
}
textColor function in controller
With this controller action, we can now set the tile text color in the HTML template with ng-style, setting the tile's CSS color attribute to $ctrl.tileTextColor(tile.id) on line 3. Once again now that I am starting to think "the Angular way", I am eliminating a lot of discrete code.
<!-- Populated tile (data loaded) -->
<div id="tile-{{tile.id}}" ng-if="tile.haveData"
        class="tile" ng-class="tile.classes" ng-style="{ 'background-color': $ctrl.tileColor(tile.id), 'color': $ctrl.tileTextColor(tile.id) }"
        style="overflow: hidden"
        draggable="true" ondragstart="tile_dragstart(event);"
        ondrop="tile_drop(event);" ondragover="tile_dragover(event);">
    <div class="dropdown" style="height: 100%">
Setting tile background color and text color in HTML template

Tile Actions

We've had an ellipsis (...) menu at the top right of dashboard tiles all along, visible on hover. Now we'll add actions underneath that menu. Here's the markup for the tile action menu:
<div class="dropdown" style="height: 100%">
    <div class="hovermenu">
        <i class="fa fa-ellipsis-h dropdown-toggle" data-toggle="dropdown" aria-hidden="true"></i>
        <ul class="dropdown-menu" style="margin-left:-130px !important">
            <li><a id="tile-config-{{tile.id}}" href="#" onclick="configureTile(this.id);"><i class="fa fa-gear" aria-hidden="true"></i>  Configure Tile</a></li>
            <li><a href="#" onclick="configureTile('0');"><i class="fa fa-plus-square-o" aria-hidden="true"></i>  Add Tile</a></li>
            <li><a id=tile-remove-{{tile.id}}" href="#" onclick="removeTile(this.id);"><i class="fa fa-remove" aria-hidden="true"></i>  Remove Tile</a></li>
            <li><a id=tile-reset-{{tile.id}}" href="#" onclick="resetDashboard();"><i class="fa fa-refresh" aria-hidden="true"></i>  Reset Dashboard</a></li>
        </ul>
    </div>
Tile Action Menu Markup in HTML Template

This gives us the menu you see below when we click on the ellipsis.

Menu Actions

Now let's look at how each action is implemented.

Configure Tile

The Configure Tile action allows the tile's properties to be edited. When this action is selected, the confguration dialog appears, populated with the tile's properties. As you edit the dialog, the tile changes visually in real-time. Clicking Save commits the changes, saving the updated dialog. Clicking Cancel undoes any changes.

The event handler for Configure Tile is shown below, but all of the work is done in the controller's configureTile function. The page code invokes ctrl.configureTile(id) within a scope.$evalSync(...) call: without $evalSync, scope changes made in the controller's configureTile function would not be detected and the page wouldn't automatically update.
// configureTile : Configure (edit) a tile. Displays configure tile dialog. Value is tile index ('1', '2', ..) or '0' (new tile).

function configureTile(id) {
    var scope = angular.element('#dashboard').scope();
    var ctrl = angular.element('#dashboard').scope().$$childHead.$ctrl;
    scope.$evalAsync(function () {
        return ctrl.configureTile(id);
    });
}
Event hanlder for Configure Tile

Over in the controller, the configureTile function is expecting to be passed a tile Id (1, 2, etc.). If it is passed a zero, that indicates a new tile is being added. In the former case (configuring an existing tile), the 0-based index to the tile is saved in self.configIndex. The self.configuring flag is set to true, indicating tile configuration is in progress--and causing the dialog to be visible.
    // configureTile : Configure (edit) a tile. Displays configure tile dialog. Value is tile index ('1', '2', ..) or '0' (new tile).

    self.configureTile = function (id) {
        if (!id) // if no id specified, cancel configuration tile action
        {
            self.cancelConfigureTile();
        }
        else {
            self.clonedTiles = angular.copy(self.tiles);    // Make a copy of original tile configuration
            self.applied = true;
            if (id == '0') {    // add new tile
                self.configureTileTitle = 'Add New Tile';
                tileIndex = -1;
                var color = '#888888'; 
                $('#configTileColor').val(color);
                $("#configTileColor").spectrum("set", color);
                $('#configTileLink').val('');
                self.configuring = true;
                self.configIndex = self.tiles.length;
                self.tiles.push({
                    id: (self.configIndex+1).toString(),
                    title: 'title',
                    type: 'counter',
                    color: color,
                    dataSource: 'inline',
                    height: '1',
                    width: '1',
                    link: null,
                    classes: 'tile_1_1',
                    columns: null,
                    label: 'label',
                    value: 1,
                    haveData: true
                });
                self.computeLayout();
            }
            else {  // edit existing tile
                var index = parseInt(id.substring(12)) - 1;      // tile-config-1 => 0, tile-config-2 => 1, etc.
                self.configureTileTitle = 'Configure Tile ' + (index+1).toString();
                tileIndex = index;
                self.configIndex = index;
                self.configTile = self.tiles[index];
                if (self.configTile) {
                    self.configuring = true;
                    self.configTileSave = angular.copy(self.configTile);
                    var color = self.configTile.color; 
                    $('#configTileColor').val(color);
                    $("#configTileColor").spectrum("set", color);
                    $('#configTileLink').val(self.configTile.link);
                    var value = '';
                    if (self.configTile.type === 'counter' && self.configTile.dataSource === 'inline') {
                        value = self.configTile.value;
                        $('#configTileValue').val(value);
                    }
                }
            }
        }
        return false;
    };

configureTile function in Controller

In the above, there's some code used to set a color picker control but otherwise you don't see type, title, width, height, etc. being set into the configuration UI. As explained earlier, the reason for this is that the tile properties are bound to the UI automatically by angular directives in the HTML template.

Cancel

If the user clicks Cancel, we need to abandon changes--but because of the 2-way binding, any changes made on the dialog have already been applied to the tile--so how do we handle a Cancel? If you look back above at the conrtroller's configureTile function, the answer is on line 9. When we begin the configuration activity, we make a copy of the entire tile array named clonedTile. This is done using angular.copy, which makes a deep (without references) copy of the tiles array. Because this has been done, the code to cancel (shown below) merely has to copy back the saved copy of the tiles array. It also calls self.computeLayout (to calculate the arrangement of tiles to fit the current window width) and calls deferCreateCharts to have the chart library render any chart tiles.
    // cancelConfigTile : cancel a configure tile action (dismiss tile, clear configuring flags).

    self.cancelConfigureTile = function () {
        if (self.configuring) {
            self.configuring = false;
            if (self.applied) {  // roll back changes from Apply button
                self.tiles = angular.copy(self.clonedTiles);
                self.computeLayout();
                deferCreateCharts();
                self.clonedTiles = null;
                self.applied = false;
            }
        }
    };
cancelConfigureTile function in controller

Save

When Save is clicked, our tiles array is already up to date thanks to the 2-way binding that has been operative all during the editing process. We do, however, need to save the updated layout. That is done with a new call added to the DataService named saveDashboard (line 14).
// saveConfigureTile : Save tile configuration. An Apply can be reverted with a cancel.
// if tileIndex is -1, a new tile is being added.

self.saveConfigureTile = function () {
    var index = tileIndex;

    if (tileIndex == -1) { // new tile
        tileIndex = self.configIndex;
        index = tileIndex;
    }

    self.configTile = self.tiles[index];

    DataService.saveDashboard(self.tiles);

    self.applied = false;
    tileIndex = -1;

    self.updateTileProperties();
    self.computeLayout();
    deferCreateCharts();
    toastr.options = {
        "positionClass": "toast-top-center",
        "timeOut": "1000",
    }
    toastr.info('Dashboard Changes Saved')

    self.configuring = false;
};
saveConfigureTile function in controller

In the SQL Server implementation of the Data Service, saveDashboard makes an Ajax call (line 38) to a SaveDashboard action in the MVC controller. The MVC controller action writes out the dashboard layout and tile properties to the database for the current user. The demo edition of the Data Service also has a saveDashboard function, but it doesn't do anything.
// -------------------- saveDashboard : updates the master layout for tiles (returns tiles array) ------------------

this.saveDashboard = function (newTiles) { 

    var Dashboard = {
        DashboardName: null,
        Username: null,
        Tiles: [],
        Queries: null
    };

    var tile = null;
    var Tile = null;

    // create tile object with properties

    for (var t = 0; t < newTiles.length; t++) {
        tile = newTiles[t];
        Tile = {
            Sequence: t+1,
            Properties: [
                { PropertyName: 'color', PropertyValue: tile.color },
                { PropertyName: 'width', PropertyValue: parseInt(tile.width) },
                { PropertyName: 'height', PropertyValue: parseInt(tile.height) },
                { PropertyName: 'title', PropertyValue: tile.title },
                { PropertyName: 'type', PropertyValue: tile.type },
                { PropertyName: 'dataSource', PropertyValue: tile.dataSource },
                { PropertyName: 'columns', PropertyValue: JSON.stringify(tile.columns) },
                { PropertyName: 'value', PropertyValue: JSON.stringify(tile.value) },
                { PropertyName: 'label', PropertyValue: tile.label },
                { PropertyName: 'link', PropertyValue: tile.link },
                { PropertyName: 'format', PropertyValue: tile.format }
            ]
        };
        Dashboard.Tiles.push(Tile);
    };

    var request = $http({
        method: "POST",
        url: "/Dashboard/SaveDashboard",
        data: JSON.stringify(Dashboard),
        headers : {
            'Content-Type': 'application/json'
        }

    });

    return (request.then(handleSuccess, handleError));
};
saveDashboard function in SQL Server Data Service

Add New Tile

The Add New Tile action adds a new tile to the layout and brings up the configuration dialog. Clicking Save commits the new tile to the layout and saves it. Clicking Cancel abandons the new tile.

Add New Tile uses the same code describes above under Configure Tile. By passing an id of zero, the code in the controller configureTile function knows to add a new tile to the layout.

By the way, Add New Tile and Configure Tile have to deal with the fact that as the user is defining / modifying a tile, some of the tile properties may be incomplete / invalid. In particular, consider what happens when a tile type is changed in the configuration dialog: more likely than not, a valid data source is no longer set that makes sense for that tile type. To combat this, the controller code that responds to changes in type and data source calls a function named tileHasValidTypeAndDataSource(index) before trying to re-fetch data and render the tile. I'm quite sure more needs to be done along these lines--expect an update after more testing and debugging. There, you've been warned!

Remove Tile

The Remove Tile action removes the current tile and saves the updated layout. Your dashboard must have at least one tile, so this action won't do anything if there's only one tile left. 


In the controller, the removeTile function removes the tile from the tiles array using JavaScript's array.splice function. DataService.saveDashboard is called to save the updated dashboard layout. The usual call to computeLayout is then made to recompute the dashboard layout.
// removeTile : Remove a tile.

self.removeTile = function(id) {
    if (!id) return;

    if (self.tiles.length < 2) return; // can't delete all tiles, because we lose the tile menu and have no way to add new tiles

    var index = parseInt(id.substring(12)) - 1;      // tile-remove-1 => 0, tile-remove-2 => 1, etc.

    self.tiles.splice(index, 1);
    self.computeLayout();

    DataService.saveDashboard(self.tiles);

    toastr.options = {
        "positionClass": "toast-top-center",
        "timeOut": "1000",
    }

    toastr.info('Dashboard Tile Deleted');
    return false;
}
removeTile function in controller

There really should be a confirmation dialog for destructive actions such as Remove Tile. One more item to add to our To Do list.

Reset Dashboard

One last action we'll implement is the ability to reset the dashboard. This will remove the user's custom dashboard and return them back to the default layout.

Recall that our database scheme is to have a default dashboard in the database, but also the ability for saving a custom dashboard for a user: all of the cases above where we've added / configured / removed a tile causes a custom dashboard layout to be saved for the user.

When our code loads a dashboard for a user, it tries to find a custom one for the current user's username. If it can't find that, it uses the default dashboard. All it takes for a dashboard reset, then, is to delete the user's custom saved dashboard. In the Angular controller, the resetDashboard function invokes the Data Service resetDashboard function; and then re-loads the user's dashboard.
// resetDashboard : reset user's dashboard

self.resetDashboard = function () {
    Promise.resolve(DataService.resetDashboard()).then(
        function (response) {
            self.LoadDashboard();
        });
}
resetDashboard function in controller

The Data Service's resetDashboard function in turn calls an MVC action that removes the custom dashboard from the database.
// resetDashboard : reset user's dashboard

this.resetDashboard = function () {
    var request = $http({
        method: "GET",
        url: "/Dashboard/DashboardReset/",
    });
}
resetDashboard function in SQL DataService

Drag and Drop Persistence

In addition to the tile actions we've covered that persist a user's custom dashboard layout to the database, there's one other way we have to modify the layout: through drag-and-drop. Several posts back we developed the capability to re-arrange tiles through dragging. We've updated the controller's dragTile method to persist the dashboard changes after a tile has been moved. This is done by calling DataService.saveDashboard, just as with the other tile actions described earlier.

New MVC Controller Actions

We've mentioned a few new MVC actions but haven't shared the back-end C# code yet, so let's review it now.

SaveDashboard

SaveDashboard saves a dashboard layout for the current user in the SQL Server database. It first deletes any existing dashboard layout set of records for the username, then adds a new set.
        // /Dashboard/SaveDashboard (POST) tiles .... save updated dashboard for user.

        [HttpPost]
        public void SaveDashboard(Dashboard dashboard)
        {
            try
            {
                String username = CurrentUsername();

                DeleteDashboard();  // Delete prior saved dashboard (if any) for user.

                // Check whether an existing dashboard is saved for this user. If so, delete it.

                int dashboardId = -1;

                using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Database"]))
                {
                    conn.Open();

                    // Add dashboard layout root record

                    String query = "INSERT INTO DashboardLayout (DashboardName, Username, Priority) VALUES (@DashboardName, @Username, 2); SELECT SCOPE_IDENTITY();";

                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@DashboardName", "Home");
                        cmd.Parameters.AddWithValue("@Username", username);
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                dashboardId = Convert.ToInt32(reader[0]);
                            }
                        }
                    }

                    if (dashboardId!=-1) // If root record added and we have an id, proceed to add child records
                    {
                        // Add DashboardLayoutTile records.

                        int sequence = 1;
                        foreach (Tile tile in dashboard.Tiles)
                        {
                            query = "INSERT INTO DashboardLayoutTile (DashboardId, Sequence) VALUES (@DashboardId, @Sequence)";

                            using (SqlCommand cmd = new SqlCommand(query, conn))
                            {
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.ExecuteNonQuery();
                            }
                            sequence++;
                        } // next tile

                        // Add DashboardLayoutTileProperty records.

                        sequence = 1;
                        foreach (Tile tile in dashboard.Tiles)
                        {
                            query = "INSERT INTO DashboardLayoutTileProperty (DashboardId, Sequence, PropertyName, PropertyValue) VALUES (@DashboardId, @Sequence, @Name, @Value)";

                            using (SqlCommand cmd = new SqlCommand(query, conn))
                            {
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.Parameters.AddWithValue("@Name", "color");
                                if (tile["color"] == null)
                                {
                                    cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Value", tile["color"]);
                                }
                                cmd.ExecuteNonQuery();

                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.Parameters.AddWithValue("@Name", "height");
                                if (tile["height"] == null)
                                {
                                    cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Value", tile["height"]);
                                }
                                cmd.ExecuteNonQuery();

                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.Parameters.AddWithValue("@Name", "width");
                                if (tile["width"] == null)
                                {
                                    cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Value", tile["width"]);
                                }
                                cmd.ExecuteNonQuery();

                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.Parameters.AddWithValue("@Name", "type");
                                if (tile["type"] == null)
                                {
                                    cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Value", tile["type"]);
                                }
                                cmd.ExecuteNonQuery();

                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.Parameters.AddWithValue("@Name", "title");
                                if (tile["title"] == null)
                                {
                                    cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Value", tile["title"]);
                                }
                                cmd.ExecuteNonQuery();

                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.Parameters.AddWithValue("@Name", "dataSource");
                                if (tile["dataSource"] == null)
                                {
                                    cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Value", tile["dataSource"]);
                                }
                                cmd.ExecuteNonQuery();

                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.Parameters.AddWithValue("@Name", "label");
                                if (tile["label"] == null)
                                {
                                    cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Value", tile["label"]);
                                }
                                cmd.ExecuteNonQuery();

                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.Parameters.AddWithValue("@Name", "columns");
                                if (tile["columns"] == null)
                                {
                                    cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Value", tile["columns"]);
                                }
                                cmd.ExecuteNonQuery();

                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.Parameters.AddWithValue("@Name", "value");
                                if (tile["value"] == null)
                                {
                                    cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Value", tile["value"]);
                                }
                                cmd.ExecuteNonQuery();

                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                                cmd.Parameters.AddWithValue("@Sequence", sequence);
                                cmd.Parameters.AddWithValue("@Name", "link");
                                if (tile["link"] == null)
                                {
                                    cmd.Parameters.AddWithValue("@Value", DBNull.Value);
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@Value", tile["link"]);
                                }
                                cmd.ExecuteNonQuery();
                            }
                            sequence++;
                        } // next tile
                    }

                    conn.Close();
                } // end SqlConnection
            }
            catch(Exception ex)
            {
                Console.WriteLine("EXCEPTION: " + ex.Message);
            }
        }

        // DeleteDashboard : Delete user's saved custom dashboard.

        private void DeleteDashboard()
        {
            try
            {
                String username = CurrentUsername();

                // Check whether an existing dashboard is saved for this user. If so, delete it.

                int dashboardId = -1;

                using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Database"]))
                {
                    conn.Open();

                    // Load the dashboard.
                    // If the user has a saved dashboard, load that. Otherwise laod the default dashboard.

                    String query = "SELECT TOP 1 DashboardId FROM DashboardLayout WHERE DashboardName='Home' AND Username=@Username";

                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandType = System.Data.CommandType.Text;
                        cmd.Parameters.AddWithValue("@Username", CurrentUsername());
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                dashboardId = Convert.ToInt32(reader["DashboardId"]);
                            }
                        }
                    }

                    if (dashboardId != -1) // If found a dashboard...
                    {
                        // Delete dashboard kayout tile property records

                        query = "DELETE DashboardLayoutTileProperty WHERE DashboardId=@DashboardId";

                        using (SqlCommand cmd = new SqlCommand(query, conn))
                        {
                            cmd.CommandType = System.Data.CommandType.Text;
                            cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                            cmd.ExecuteNonQuery();
                        }

                        // Delete dashboard layout tile records

                        query = "DELETE DashboardLayoutTileProperty WHERE DashboardId=@DashboardId";

                        using (SqlCommand cmd = new SqlCommand(query, conn))
                        {
                            cmd.CommandType = System.Data.CommandType.Text;
                            cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                            cmd.ExecuteNonQuery();
                        }

                        // Delete dashboard layout record

                        query = "DELETE DashboardLayout WHERE DashboardId=@DashboardId";

                        using (SqlCommand cmd = new SqlCommand(query, conn))
                        {
                            cmd.CommandType = System.Data.CommandType.Text;
                            cmd.Parameters.AddWithValue("@DashboardId", dashboardId);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    conn.Close();
                } // end SqlConnection
            }
            catch (Exception ex)
            {
                Console.WriteLine("EXCEPTION: " + ex.Message);
            }
        }

SaveDashboard Action in MVC Controller

DashboardReset

The DashboardReset action action deletes the user's saved custom dashboard (if any).

        // DashboardReset ... reset user to default dashboard (by deleting their saved custom dashboard).

        [HttpGet]
        public JsonResult DashboardReset()
        {
            try
            {
                DeleteDashboard();
                return Json(true, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                Console.WriteLine("EXCEPTION: " + ex.Message);
                return Json(false, JsonRequestBehavior.AllowGet);
            }
        }
DashboardReset Action in MVC Controller

Summary

Today we achieved the following:

  • Added a tile configuration UI
  • Expanded the tile color palette
  • Used Angular directives for tile editing with real-time visual changes
  • Added dashboard layout persistence with new Data Service functions and MVC Controller actionss
  • Added tile menu actions to Configure Tile, Add New Tile, Remove Tile, and Reset Dashboard

After 5 posts, we now have a dashboard that works! However, we can't quite claim completion yet. For one thing, all of the above is brand new and it needs testing and debugging. After some of that, we'll look to polish what we have and perhaps add some more functionality.

Download Source 
Download Zip
https://drive.google.com/file/d/0B4734rvcufGGRC1ZenItNUNwNTA/view?usp=sharing

Next: Part 6 : Admin Tile Actions and Confirmation Dialog





Sunday, October 15, 2017

An AngularJS Dashboard, Part 4: Bar/Pie Charts and Back-end Data Queries

NOTE: for best results, view the http: version of this page (else you won't get syntax highlighting).

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:
  1. Add two more chart tiles (bar chart and pie chart).
  2. Build out more of our back end so that tile data is actually queried from the database. 
  3. Do some refactoring and fixing of existing code.
Here's what we'll be ending up with:


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.

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:
  1. Define a DashboardTile reference table, with one entry for each type of dashboard tile.
  2. Define some application data (tables like Employee, Store, Customer, and Order).
  3. Define a DashboardQuery table for holding available data queries for dashboard tiles.
  4. Revise MVC DashboardController.Index action to execute queries to fecth tile data. .
  5. 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.

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)

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.

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 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