Data Aquarium Framework

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(8) OAuth Scopes(1) OAuth2(11) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(10) PKCE(2) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(180) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(80) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Data Aquarium Framework
Saturday, January 24, 2015PrintSubscribe
Charts Everywhere

Every IT project involves collection of data. Development teams put a lot of thought into database design, middleware programming, business rules. Analysis of data is always somewhere a few milestones away on the project timeline. Sure the charts can make your app look good, but one needs data to feed the charts. Therefore the data analysis and charts will only become a feature of a project if it survives the initial developments stages.

Users Love Charts

Why do users love charts? The charts can tell the story that the raw data cannot.

For example, a typical list of orders hides a treasure trove of information that can be unlocked if data is pivoted, sorted, counted, and summed.  Consider the collection of charts in the screenshot below.

Charts view style in a Touch UI app created with Code On Time app generator.

These charts are derived from the orders stored in the Northwind database. The master-detail form with order data is shown next.

Master-detail form view in a Touch UI app created with Code On Time app generator.

Data fields Order Date, Employee Last Name, Ship Via Company Name, and Ship Country can truly illuminate the business processes within the Northwind mail order company.

For example, we can answer the following questions:

  • Which countries are the main contributors to the sales?
  • Which employee is the most productive?
  • How much are shippers utilized to fulfill orders?
  • Are sales getting better over time?
  • Which employee is contributing the most to the expense of fulfilling orders?
  • How do sales compare year over year?
  • Which month can be expected to be the busiest?

The charts in the screenshot answer the questions.

Charts in the app with Touch UI created with Code On Time.

Charts Are Created Automatically

Application framework has a built-in ability to recognize what data can be analyzed. “Lookup” and “date” fields are tagged to produce various charts without any need for programming.

If the compatible fields are detected than the “Charts” view style becomes available to end users. Charts are literally everywhere!

Sample chart displayed in the user interface of an app with Touch UI.

Developers can tag the fields for charting by default.  A typical “chart” tag in a data controller XML file will cause the chart above to become available when “Charts” view style is activated.

<dataField fieldName="ShipVia" aliasFieldName="ShipViaCompanyName" 
tag="pivot1-row-pie3d"/>

The server-side code of application will pivot the data and package it in the fashion suitable for chart presentation. Application produces multiple pivots while reading the same set of records from the database.  A single request to the server will retrieve all pivots for the charts displayed when “Charts” view style is activated.

The built-in ability to pivot data can be utilized to create custom charts and data presenters.

Responsive Presentation of Charts

Charts are responsive. Users will be able to interact with charts on any device. The small form factor will cause a simple list of charts to be displayed. The height of the charts is computed based on the available width and the height of the display.

Responsive charts are displayed in a window with small form factor in a Touch UI app.

Application will try to fit as many charts as possible on screen without requiring user to scroll horizontally to see the entire set of charts.

Wide displays will allow Touch UI to render multiple charts without the need for scrolling.

Activating “Charts” View Style

The option to activate “Charts” view style is visible on the sidebar as shown in the illustration above. The sidebar may not be visible on all screen sizes.

Context menu provides “Charts” option in the list of data presentation styles. A single touch or click will show the available charts.

Context menu includes an option to activate Charts view style in an app with Touch UI.   Charts view style in an app with Touch UI created with Code On Time app generator.

Shaping The data In The Charts

Users shape the data set rendered in the “Charts” style with the help of Quick Find, Adaptive Filters, and Advanced Search.

For example, a user can specify a criteria for “deep search” that requires data to match the ship country to France and Italy and have associated line items with products in categories that include “Confections” and “Seafood”.

Advanced Search screen in an app with Touch UI.

Here is the result set rendered in the “List” style. User can access “Charts” view style by touching or clicking the context menu button on the right side of the toolbar.

Activating context menu in a Touch UI application.

Here is the set of charts reflecting the “deep search” criteria that required searching in orders and related order details.

Charts view style shows data produced by deep search of orders and linked order details in Touch UI application.

This screenshot shows charts for orders shipped to Canada, USA, and UK and placed by employees Buchanan and Fuller.

Compact set of charts rendered in Charts view style in an app with Touch UI.

User may activate responsive grid view style to see the data behind the charts.

Responsive grid view style in a Touch UI app created with Code On Time.

Wider window will show charts distributed in three columns.

Responsive Charts view style displayed three charts side-by-side in an app with Touch UI.

Monday, October 27, 2014PrintSubscribe
Handling Login and Logout

In order to log into a web app generated with Code On Time, the user must first activate the login modal window. When using Touch UI, click on the Menu button in the top left corner.

image

Then, select the Login button in the menu.

image

This will open the modal login window. Enter the username and password in the fields provided, and press Login to initiate the login process.

Default login modal form.

The Desktop UI uses a flyover login dialog. Mouse over the top right corner of the screen, next to the words “Login to this website”, and enter the user credentials when the dialog appears.

image

If a standalone login page has been generated, then the username and password fields will be visible in the top right corner of the Login page. Enter the user credentials and press Login.

image

Once the user clicks the Login button, the $app.login JavaScript method will be called on the client. The login() meth0d invokes the web service on the server and executes DataControllerService.Login method, seen below.

public bool Login(string username, string password, bool createPersistentCookie)
{
    return ApplicationServices.Login(username, password, createPersistentCookie);
}

The DataControllerService.Login method then calls ApplicationServices.Login method, which creates an instance of ApplicationServices and calls the virtual method UserLogin.

public static bool Login(string username, string password, bool createPersistentCookie)
{
    ApplicationServices services = new ApplicationServices();
    return services.UserLogin(username, password, createPersistentCookie);
}

The UserLogin method’s default implementation will validate the user using the application’s Membership class. If successfully validated, it will set the authentication cookie and return true. Otherwise, it will return false.

public virtual bool UserLogin(string username, string password, bool createPersistentCookie)
{
    if (Membership.ValidateUser(username, password))
    {
        FormsAuthentication.SetAuthCookie(username, createPersistentCookie);
        return true;
    }
    else
        return false;
}

Any custom user control can call the $app.login method in order to log in the user – an example of this would be the standalone login page.

If it is necessary to the project requirements of your application, the UserLogin method can be overridden to extend the functionality. This allows setting of session variables, executing custom scripts on the server, logging user access, to name a few examples.

Logging In From JavaScript

Suppose that we want to add a button to the home page of the app that allows the user to log in with “user” account without having to use the standard login form.

The first step will be to add a page and a custom user control to the page. Start the Project Designer. In the Project Explorer window, click on the New Page button.

Adding a new page to the project.

Specify these properties:

Property Value
Name LoginPage
Roles ?

Press OK to save the page. In the Project Explorer, drag and drop the new page to right of Home page to place it second in the site menu.

Dragging a page onto the right side of Home page node.     Login Page has been placed after Home in the site menu.

Right-click on the new page and press New Container.

Adding a new container to the 'Login Page' page.

Preserve the default settings and press OK to save. Right-click the new container and press New Control.

Adding a new control to the 'Login Page' page.

Next to the User Control field, click on the New User Control icon.

Creating a new user control.

Enter a name of “CustomLoginButton” and press OK to save the user control. Press OK again to bind the control to the page.

On the toolbar, press Browse to generate the web app and the new user control file. When complete, right-click on the control and press Edit in Visual Studio.

Editing the user control in Visual Studio.

The file will open in Visual Studio. Replace the contents after the <%@ Control%> element with the following:

<div id="CustomLoginButton" data-app-role="page" data-activator="Button|CustomLoginButton">
    <div data-role="content">
        <p>
            <button id="login-admin-button">Login As Administrator</button>
        </p>
    </div>
</div>

<script type="text/javascript">
    (function () {
        $(document)
            // attach event to button
            .on('click', '#login-admin-button', function () {
                // call login method
                $app.login('admin', 'admin123%', true, function () {
                    // on success, navigate to Home
                    window.location.replace('/Pages/Home.aspx');
                }, function () {
                    // on failure, show an alert
                    alert('Login failed!');
                });
                return false;
            });
    })();
</script>

Run the project by pressing F5, and navigate to the Login Page. The page will have a single button present.

A single button is present on the 'Login Page'.

Click on the button. The page will successfully log in the user with “admin” account and redirect to the Home page.

The user has been logged in and redirected to the Home page.

Extending Login

The Login authentication method can also be overridden to implement custom functionality.

For example, suppose that we need to allow anyone to take the name of any user if they provide a secret key. Let’s override the Login method to check for presence of the secret key in the password. If the password is the key, then the user will be authenticated. Otherwise, the base method will be called to check for the user’s actual password.

Start the app generator. Click on the project name, and press Develop to open the project in Visual Studio.

In the Solution Explorer of Visual Studio, right-click on ~/App_Code folder and press Add | Class.

Adding a class to the application using Visual Studio.

Assign a name to the class file.

Assigning a name to the class file.

Replace the contents of the file with the following:

C#:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;

namespace MyCompany.Services
{
    public partial class ApplicationServices
    {
        public override bool UserLogin(string username, string password, bool createPersistentCookie)
        {
            if (password == "secret")
            {
                FormsAuthentication.SetAuthCookie(username, createPersistentCookie);
                return true;
            }
            else
                return base.UserLogin(username, password, createPersistentCookie);
        }
    }
}

Visual Basic:

Imports Microsoft.VisualBasic

Namespace MyCompany.Services
    Partial Public Class ApplicationServices
        Public Overrides Function UserLogin(username As String, password As String, createPersistentCookie As Boolean) As Boolean
            If password.Equals("secret") Then
                FormsAuthentication.SetAuthCookie(username, createPersistentCookie)
                Return True
            End If
            Return MyBase.UserLogin(username, password, createPersistentCookie)
        End Function
    End Class
End Namespace

Save the file, and press F5 on your keyboard to start the application. Login to the application with the username “admin” and the password “secret”.

Logging into admin account with the secret password.

The application will log you in successfully and grant you access to the user’s pages.

Access has been granted to the user with admin priveledges.

Logging Out

The logout procedure is very similar to login. To log out from a Touch UI web app, click on the Menu button in the top right corner of the page, and click Logout from the menu panel.

image

When a user clicks on the Logout button, the JavaScript method $app.logout is called. The method will invoke the web service to trigger the DataControllerServices.Logout method.

public void Logout()
{
    ApplicationServices.Logout();
}

The Logout() web method will invoke ApplicationServices.Logout().

public static void Logout()
{
    ApplicationServices services = new ApplicationServices();
    services.UserLogout();
}
The ApplicationServices.Logout static method will create an instance of ApplicationServices and invoke UserLogout virtual method.
public virtual void UserLogout()
{
    FormsAuthentication.SignOut();
}
The UserLogout method will trigger the SignOut method of forms authentication, which will remove the authentication cookie.

Logging Out From JavaScript

Let’s add a logout button to the user control that was created previously. Switch back to the user control file open in Visual Studio, and replace the contents after the <% Control %> element with the following:

<div id="CustomLoginButton" data-app-role="page" data-activator="Button|CustomLoginButton">
    <div data-role="content">
        <p>
            <button id="login-admin-button">Login As Administrator</button>
            <button id="logout-button">Logout</button>
        </p>
    </div>
</div>

<script type="text/javascript">
    (function () {
        $(document)
            // attach login event to button
            .on('click', '#login-admin-button', function () {
                // call login method
                $app.login('admin', 'admin123%', true, function () {
                    // on success, navigate to Home
                    window.location.replace('/Pages/Home.aspx');
                }, function () {
                    // on failure, show an alert
                    alert('Login failed!');
                });
                return false;
            }).on('click', '#logout-button', function () {
                $app.logout(function () {
                    // refresh the page
                    window.location.reload();
                })
            });
    })();
</script>

Save the file, and open the page in your browser. Note that there are now two buttons.

Login and Logout custom buttons are present on the page.

Click on the first one and you will be logged into the app as “admin”. Click on the second one and it will log the user out, and refresh the page.

Extending Logout

The UserLogout method can also be overridden to add custom functionality. For example, suppose that we need to record when a user logs out. In the class file created in the previous section, add another overridden class after UserLogin method:

C#:

public override void UserLogout()
{
    Trace.WriteLine(String.Format(
                        "User {0} has logged out.", 
                        HttpContext.Current.User.Identity.Name));
    base.UserLogout();
}

Visual Basic:

Public Overrides Sub UserLogout()
    System.Diagnostics.Trace.WriteLine(String.Format(
                        "User {0} has logged out.",
                        HttpContext.Current.User.Identity.Name))
    MyBase.UserLogout()
End Sub

Press F5 to run the app in debug mode. Log in to the application, and then log out. Switch back to Visual Studio and you will notice that the line has been printed to the Output window.

The trace line has been printed to the Output window.

Note that the line may not print in Web Site Factory apps.

Saturday, August 16, 2014PrintSubscribe
Sending Emails with SQL Business Rules

Email business rules offer a simple and effective mechanism of creating email notifications for various workflow tasks. For example, a simple notification can be sent out when a new customer account is created. Another example is a notification with attached PDF reports triggered by an update of a product category record.

An email business rule is a static text-based template with placeholders matched to a data controller action command name and execution phase. The template may also include XML-based attachment instructions. A single email notification is generated by application framework when an email business rule is matched to a command activated by application user. Template placeholders are replaced with the field values of the affected data row. If the multiple selection mode is enabled, then a separate notification is generated for each selected data row. Here is an email business rule that produces a notification with two attachments for Categories controller.

An email business rule selected in Project Designer of Code OnTime app generator.

The text of the email business rule script template is shown next. The data value placeholders are highlighted.

Host: smtp.gmail.com
Port: 587
UserName: YOUR_EMAIL_ADDRESS@gmail.com
Password: PASSWORD
EnableSSL: true

From: "Sales Admin" <your_email_address@gmail.com>
To: RECIPIENT@northwind.com
Subject: Category "{CategoryName}" has changed!

Dear Admin,

This product category has been changed.
See attachment for category product details.

System Monitor

<attachment type="report">
    <name>{CategoryName}</name>
    <controller>Categories</controller>
    <view>editForm1</view>
    <filter>
        <item>
            <field>CategoryID</field>
            <operator>=</operator>
            <value>{CategoryID}</value>
        </item>
    </filter>
</attachment>
<attachment type="report">
    <name>{CategoryName} Products</name>
    <controller>Products</controller>
    <sortExpression>UnitPrice desc</sortExpression>
    <filter>
        <item>
            <field>CategoryID</field>
            <operator>=</operator>
            <value type="Int32">{CategoryID}</value>
        </item>
    </filter>
</attachment>

If a specific notification cannot be expressed with a static text template, then consider using SQL business rules to compose an email by writing notification text in the programming language of the database engine.  Database programming languages, such as T-SQL of Microsoft SQL Server, offer enough flexibility to compose a notification of any complexity.

SQL business rules are text-based scripts executed by the database engine. The scripts may include references to data controller fields, properties of BusinessRules class associated with the data controller, URL arguments, and session variables. Developers reference any of these resources as parameters that do not have to be explicitly declared in the scripts.

Application framework binds these “pre-defined” parameters to the corresponding resources, executes the script, collects the output parameter values, and ignores any datasets that may have been produced when the script was executed.

A special mechanism exists in the application framework to force it to pay attention to the output of the SQL business rule script. The developer must supply two business rules that are matched to the same command. The first business rule triggers the “awareness” mode of the application framework. The second business rule produces a dataset that will be captured by the framework. Application framework copies the output data to an instance of System.Data.DataTable class and assigns it to BusinessRules.EmailMessages property. The property implementation iterates over the data table rows and treats each row as a notification that must be sent out. The column names of the table, such as “Port”, “To”, “From”, “Subject”, and “Body” allow the framework to compose correct email messages. The message “awareness” mode is automatically turned off when the last email has been sent.

Here is an example of data controller with two SQL business rules that will generate an email with report attachments when a product category has been updated.

A data controller with two SQL business rules that will generate email notifcations in Touch UI application created with Code On Time.

The first SQL business rule is very simple

Property Value
Type SQL
Command Name Update
Phase After
Script
set @BusinessRules_EnableEmailMessages = 1

The script of the this rule assigns value True to the BusinessRules.EnableEmailMessages property of Boolean type and does nothing else. The implementation of the script may do any other type of processing if needed.

The mode of email message awareness is activated now!

The second SQL business rule will produce a singe data row. The script has a “debug” section that declares parameter values used for testing. Application framework will remove this section at runtime  before asking the database server to execute the script. The highlighted fragments are the output column names.

Property Value
Type SQL
Command Name Update
Phase After
Script
-- debug
declare @CategoryID int
declare @CategoryName nvarchar(5)
select @CategoryName = 'Confections', @CategoryID = 1
-- end debug

declare @CrLf as varchar(2)
select @CrLf = char(13)+ char(10)

select 
    -- from 
    'YOUR_EMAIL_ADDRESS@gmail.com' "From",
    -- to
    'RECIPIENT@nothwind.com' "To",
    -- subject
    'Category "' + @CategoryName + '" has changed' "Subject",
    -- body
    'Dear Admin' + @CrLf + @CrLf +
    'This product category has been changed.' + @CrLf +
    'See attachment for category product details.' + @CrLf + @CrLf +
    'System Monitor' + @CrLf  +
    -- attachment 1
    '<attachment type="report">' + 
    '<name>' + @CategoryName + '</name>' +
    '<controller>Categories</controller>' + 
    '<view>editForm1</view>' +
    '<filter>' + 
        '<item>' +
              '<field>CategoryID</field>' +
              '<operator>=</operator>' + 
              '<value>' + cast(@CategoryID as varchar) + '</value>' +
        '</item>' +
    '</filter>' +
    '</attachment>' +    
    -- attachment 2
    '<attachment type="report">' + 
    '<name>Products in ' + @CategoryName + '</name>' +
    '<controller>Products</controller>' + 
    '<sortExpression>UnitPrice desc</sortExpression>' +
    '<filter>' + 
        '<item>' +
              '<field>CategoryID</field>' +
              '<operator>=</operator>' + 
              '<value type="Int32">' + 
cast(@CategoryID as varchar) +
'</value>' + '</item>' + '</filter>' + '</attachment>' "Body"

Note that STMP parameters, such as Port and Password are omitted from the script. Application framework will use default SMTP settings from the application configuration file. If any of these parameters are needed to be in the message definition, then make sure that the values are included as the corresponding column names (for example, Host, UserName, etc.)

This particular implementation uses @CrLf parameter to inject end-of-line characters in the message. The entire message body is a single concatenated string of text. A real-world implementation may compose the text by iterating through cursors and executing multiple select statements.

If an HTML output is desired then the corresponding tags should be included in the “Body”.

Developers can return any number of “messages” in the output when needed. This may be accomplished by creating a temporary database table and populating it with the messages. The contents of the temporary table are selected when the job is done. Another alternative is to use UNION of several SELECT statements to send a fixed number of messages at once.

This is a sample email notification produced by SQL business rules from the example above.

An email notification with two report attachments produced by Touch UI app created with Code On Time.