Blog: Posts from March, 2012

Code On Time web applications offer the Export to Spreadsheet action. The feature is available in all grid views of your application by default.

Select this option and you will be prompted to download a file. The following screen shot shows the prompt displayed by Internet Explorer 9 at the bottom of the web browser window.

The file has the “*.iqy” extension. This file format is recognized and supported by Microsoft Excel.

If you save the file to the hard drive and open it in the Notepad then you will see a cryptic-looking URL that points to your web application.

If you choose Open option instead then Microsoft Excel will start if you have it installed on your computer. You will see a prompt similar to the one in the next picture.

Microsoft Excel wants to let you know that it is going to download the content, which is not located on your computer. You have to authorize that by clicking on Enable button. Nothing dangerous is coming your way. The content is an XML file produced by your application in response to the URL in the “*.iqy” file.

Click Enable and you will see the prompt shown next.

This time Microsoft Excel contacts your web application. Your application needs to know the identity of the user and requests this information from Excel. That is why you are seeing the prompt.

Enter the identity of the user registered in the membership database. For example,enter admin/admin123% or user/user123% – these are two default users accounts created automatically in the baseline application.

Click OK button and you will see the data from your web application display in Microsoft Excel.

Select Insert tab on the ribbon and choose to insert the PivotTable.

Dialog Create PivotTable is displayed.

Click OK to insert a new sheet with the pivot table.

The pivot table PivotTable1 is empty. Notice the list of fields on the right-hand side of the window. All visible fields from the Orders screen are listed there.

Drag Customer Company Name field to the “Values” box and you will see the total number of orders in the database.

Drag Employee Last Name to the “Row Labels” box and you will see that the most product employee is Ms. Peacock. She has placed 156 orders.

Drag Ship Via Company Name field to “Column Labels” box and you will see the breakdown of orders by employees and shippers delivering the goods to customers.

Numbers don’t always tell the story.

Choose PivotChart item on the right-hand side of the Options ribbon. Select 3-D Cylinder chart and click OK button.

We can now clearly see that Ms. Peacock is heavily utilizing the shipping company United Package. If this is the most expensive shipping option then we should call Ms. Peacock and advise to switch to another freight operator. If this this is the least expensive option then a bonus is due in recognition of an employee thinking about the business bottom line.

You can save this spreadsheet to the hard drive.  Open the spreadsheet a week a later and choose Data | Refresh All and you will be prompted to re-enter user name and password – the new data feed will be produced by your application and the charts and cross tables will automatically refresh.

You web application created with Code On Time becomes an information hub of your company. Business users can do ad hoc data analysis with the tool they know best. The business decision makers don’t even need to sign in the web application to know what’s going on. Microsoft Excel charts and pivot table swill let them know everything they need.

The Subtotal field is now present in the application. The field does not reflect changes when new order items are entered. We will add a business rule to the Orders controller and add a code expression for Subtotal. This code expression will use the rule to calculate the subtotal.

In the Project Explorer, switch to Controllers tab. Double-click on the Orders controller. In the Handler field, type:

OrdersBusinessRules

Press OK to save the controller, exit the Designer, and Generate the project. The business rule placeholder file will be created.

When generation is complete, click on the project name in the web application generator and press Develop. Microsoft Visual Studio or Visual Web Developer will open. In the Solution Explorer on the right side of the screen, double-click on App_Code /Rules / OrdersBusinessRules.cs(vb).

Paste in the following code:

C#:

using MyCompany.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace MyCompany.Rules
{
    public partial class OrdersBusinessRules : MyCompany.Data.BusinessRules
    {
        public decimal CalculateOrderDetailsTotal(int? orderID)
        {
            using (SqlText calc = new SqlText(@"select sum(unitprice * 
                    quantity * (1 - discount)) from 
                    [Order Details] where OrderID= @OrderID"))
            {
                calc.AddParameter("@OrderID", orderID);
                object total = calc.ExecuteScalar();
                if (DBNull.Value.Equals(total))
                    return 0;
                else
                    return Convert.ToDecimal(total);
            }
        }
    }
}

VB:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Namespace MyCompany.Rules

    Partial Public Class OrdersBusinessRules
        Inherits MyCompany.Data.BusinessRules
        Public Function CalculateOrderDetailsTotal(
                            ByRef orderID As Nullable(Of Integer)) As Decimal
            Using calc As SqlText = New SqlText(
                    "select sum(unitprice * quantity * (1 - discount)) " +
                    "from [Order Details] where OrderID=@OrderID")
                calc.AddParameter("@OrderID", orderID)
                Dim total As Object = calc.ExecuteScalar()
                If DBNull.Value.Equals(total) Then
                    Return 0
                Else
                    Return Convert.ToDecimal(total)
                End If
            End Using
        End Function
    End Class
End Namespace

This function uses SqlText utility class to create an instance of a query connected to the project’s database.
This simple query selects a sum of UnitPrice multiplied by Quantity multiplied by one minus the
Discount.

Note that SqlText utility class is generated as a part of the code base of your application. It uses the
default database connection string and ADO.NET to execute the query.

Save the file, and switch back to the application generator. Select the project name, and press Design. In the Project Explorer, switch to Controllers. Double-click on Orders / Fields / Subtotal. Make the following changes:

CalculateOrderDetailsTotal(orderID)

Press OK to save the field. The Subtotal field on the Order Form will now update to reflect any changes made to the order details.