The Workings of the Sales Data Analyst Tool | ColdFusion MX Professional Projects

Sales Data Analyst is a data analysis tool that works on the existing sales data and presents it in the form of charts to users in the Marketing department. The data that's displayed is comparative, easy to understand, and can be displayed to the next level of detail. Sales Data Analyst works with the SalesAnalysis database.

Before the marketing personnel can start to use the tool, the Database Administrator will need to move the data for the current month and year from the SalesData database to the SalesAnalysis database. The System Administrator, who handles the mail server, needs to provide information about the POP server, the username, and the password for the e-mail account commonly used by the Marketing department. You need to set the SMTP server by using ColdFusion Administrator. You also need to specify the settings for charting by using ColdFusion MX Administrator.

Setting Up the Mail Server

Perform the following steps to set up the mail server:

Start ColdFusion MX Administrator.
Under the Server Settings section on the main page, click the Mail Server subheading, as shown in Figure 29.5.

Figure 29.5: The main ColdFusion MX Administrator page.
The Mail Server Settings page is displayed. On this page, the information to be provided is divided into two sections, Mail Connection Settings and Mail Logging Settings. Figure 29.6 shows the Mail Connection Settings section.

Figure 29.6: The Mail Connection Settings section.
In this section, enter the IP address of the mail server. Accept default values for Server Port, Connection Timeout, and Spool Interval. Check the Verify Mail Server Connection option to verify the connection when the information on this form is submitted. Figure 29.7 shows the Mail Logging Settings section.

Figure 29.7: The Mail Logging Settings section.
In this section, select Warning under the Error Log Severity option. Check the Log All E-mail Messages Sent by ColdFusion option to enable the recording of messages in the log file.
Click the Submit Changes button. If the server is updated successfully, it displays the Successful Update Message page, as shown in Figure 29.8.

Figure 29.8: The Successful Update Message page.

Specifying the Chart Settings

Perform the following steps to specify the chart settings in the ColdFusion MX Administrator:

Under the Server Settings section on the main ColdFusion MX Administrator page, click the Charting subheading to display the Charting Settings page, as shown in Figure 29.9.

Figure 29.9: The Charting Settings page.
On this page, set Cache Type to Disk Cache if RAM on the Web server is already being used for other processes. If you have free RAM on the server, select Memory Cache. It's faster, although it consumes memory.
Accept the default value under Maximum Number of Images in the Cache. Set Maximum Number of Charting Threads to 4. Accept the default value of Disk Cache Location.
Click the Submit Changes button. If the server is updated successfully, it shows the Successful Update Message page, as displayed in Figure 29.10.

Figure 29.10: The Successful Update Message page.

Before using the Sales Data Analyst tool, you need to create the SalesAnalysis data source using the ColdFusion MX Administrator page. The steps for creating data sources were discussed in detail in Chapter 13, "Creating the Knowledge Bank Application." The database name is SalesAnalysis. The username and server details are the same as in Chapter 13.

Viewing the Sales Data for Item Categories

Start.cfm is the start file for the Sales Data Analyst tool. It loads the Category Wise Sales Figures page. The data is fetched from the SalesAnalysis database using queries. This page displays two charts. The code for the Start.cfm file shows how these charts are displayed.

The following is the first section of the code from the Start.cfm file:

 <CFQUERY Name="getYearMonth" DATASOURCE = "SalesAnalysis">     SELECT CurrentYear, CurrentMonth from tblParameters </CFQUERY> <cfoutput query ="getYearMonth">     <CFIF #CurrentYear# EQ 0 or Trim(#CurrentMonth#) EQ "">         <P >The Current Year or Current Month data is missing in                              the tblParameters table in the database.                              The chart cannot be displayed. Please contact system                              administrator.         </P>        </body>        </html>        <cfabort>     <CFELSE>         <cfset SESSION.CYear = #CurrentYear#>         <cfset SESSION.CMonth = Trim(#CurrentMonth#)>     </CFIF> </cfoutput>

You need to store the current year and month in the tblParameters table in the SalesAnalysis database. In this code snippet, note that these two fields are retrieved from the tblParameters table. This table has only one row. If these fields are found to be empty, the error message is displayed to the user. Otherwise, the CurrentYear and CurrentMonth values are stored in the SESSION.CYear and SESSION.CMonth variables. You need to use these variables in the later code sections and other Web pages.

The following code snippet shows the queries used for the Category Wise Sales Figures chart:

 <CFQUERY Name="CategoryWiseSales" DATASOURCE = "SalesAnalysis">     SELECT Category, Sum(Amount) AS CategorySales     from tblSalesData     Group by category,PurchaseYear     HAVING PurchaseYear = #SESSION.CYear# </CFQUERY> <CFIF #CategoryWiseSales.RECORDCOUNT# EQ 0>     <cfoutput>       <P >No Sales Data Available for the Current Year :         #SESSION.CYear#.         Please Contact System Administrator.       </P>     </cfoutput>     </body>     </html>     <cfabort> </CFIF> <CFQUERY Name="CategoryWiseSalesMonth" DATASOURCE = "SalesAnalysis">     SELECT Category, Sum(Amount) AS CategorySales     from tblSalesData     Group by category,PurchaseMonth,PurchaseYear     HAVING PurchaseMonth = '#SESSION.CMonth#'     AND PurchaseYear = #SESSION.CYear# </CFQUERY> <CFQUERY Name="TopTenItemsForTheMonth" DATASOURCE = "SalesAnalysis">     SELECT Top 10 ItemDesc, Sum(Amount) AS CategorySales     from tblSalesData     Group by ItemDesc,PurchaseMonth,PurchaseYear     HAVING PurchaseMonth = '#SESSION.CMonth#'     AND PurchaseYear = #SESSION.CYear#     Order by Sum(Amount) DESC </CFQUERY>

The CategoryWiseSales query retrieves the sales data for the current year. The query totals the sales figures, category-wise. The CategoryWiseSalesMonth query fetches the sales data for the current year and the current month. The TopTenItemsForTheMonth query retrieves the total sales figures for the top ten items sold in the current year. You need to use these queries in the query attributes of the <cfchartseries> tags for the two charts.

The following code shows the use of the <cfchart> and <cfchartseries> tags to display the two charts:

 <cfchart          xAxisTitle="Category"          yAxisTitle="Sales (in dollars)"          format="jpg"          chartwidth = "350"          chartheight="300"          show3D="no"          tipStyle="mouseover"          backgroundColor="99CC33"          seriesPlacement="cluster"          url="ViewItemForCategory.cfm?Category=$ITEMLABEL$" >            <cfchartseries              type="bar"              query="CategoryWiseSales"              valueColumn="CategorySales"              itemColumn="Category"              seriesLabel="Current Year's Sales"              />              <cfchartseries              type="bar"              query="CategoryWiseSalesMonth"              valueColumn="CategorySales"              itemColumn="Category"              seriesLabel="Current Month's Sales"              seriesColor="blue"              /> </cfchart> <cfchart          xAxisTitle="Item Description"          yAxisTitle="Sales (in dollars)"          format="jpg"          chartwidth = "390"          chartheight="300"          show3D="no"          tipStyle="mouseover"          pieSliceStyle="solid"          backgroundColor="99CC33"          seriesPlacement="cluster" >            <cfchartseries              type="pie"              query="TopTenItemsForTheMonth"              valueColumn="CategorySales"              itemColumn="ItemDesc"            /> </cfchart>

The various attributes of the <cfchart> and <cfchartseries> tags enable you to control the display of the charts.

For the first chart, the query attribute of the <cfchart> tag enables you to specify the name of the query that will feed the data to the chart. You need to set the format attribute to jpg because not all the client machines may have the Flash plug-in loaded. Set the type attribute to bar. Because two chart series are merged in the first chart, set the seriesPlacement attribute to cluster to show the two series in a bar format, one beside the other. Set the url attribute to ViewItemForCategory.cfm and pass the category name as the parameter to the ViewItemForCategory.cfm file. When the user clicks any bar, this file is loaded.

The second chart displays the name and sales figures for the top ten items sold in the current year in a pie chart format. Set the pieSliceStyle attribute to solid so that all the slices are put together in a circular format.

This Category Wise Sales Figures page has a link to view all the e-mails sent by the customers.

Viewing the Sales Data of Items of the Selected Category

The user can see the sales data for the items in a selected category by clicking its bar in the bar chart on the Category Wise Sales Figures page. The ViewItemForCategory.cfm file is loaded in the browser with the category name as a URL parameter passed to it.

The following code snippet from ViewItemForCategory.cfm shows the query to retrieve the data for the bar chart that will be displayed on this page:

 <CFQUERY Name="ItemsForTheCategory" DATASOURCE = "SalesAnalysis">     SELECT ItemDesc, Sum(Amount) AS ItemSales from tblSalesData     Group by ItemDesc,PurchaseMonth,PurchaseYear,Category     HAVING PurchaseMonth = '#SESSION.CMonth#'     AND PurchaseYear = #SESSION.CYear#     AND Category = '#URL.Category#'     Order by Sum(Amount) DESC </CFQUERY>

The data is fetched from the tblSalesData table in the SalesAnalysis database and is grouped according to the ItemDesc, PurchaseMonth, PurchaseYear, and Category fields. It's filtered based on the current year, the current month, and the category passed in the URL parameter, and it's sorted by the sum of the Amount field in descending order. This data will be fed to the chart.

The following code snippet shows how the chart is created in the View Item Wise Sales Figures page:

 <cfchart           xAxisTitle="Item Desc"           yAxisTitle="Sales (in dollars)"           format="jpg"           chartwidth = "700"           chartheight="300"           show3D="no"           tipStyle="mouseover"           backgroundColor="FFFF99"           url="ViewListOfCustomers.cfm?ItemDesc=$ITEMLABEL$"           rotated="yes"           showborder="yes" >             <cfchartseries               type="bar"               query="ItemsForTheCategory"               valueColumn="ItemSales"               itemColumn="ItemDesc"             /> </cfchart>

The chart will be displayed in the JPEG format. The url attribute of the <cfchart> tag is set to ViewListOfCustomers.cfm. This file will be loaded when the user clicks an individual bar on the chart. You can display this as a horizontal bar chart by setting the rotated attribute of the <cfchart> tag to yes. This chart will also have a border if you set the showborder attribute to yes. The attributes in the <cfchartseries> tag enable you to set the type of the chart, the query name, and the fields to be displayed on the X and Y axes.

Viewing the Customer List and Sending E-Mails

When the user clicks an item bar on the Item Wise Sales Figures page, the View Customer List page appears. The View Customer List page displays the list of customers who bought the selected item from the SellingOnTheWeb online store. Users can send e-mails to all the customers on this page. The list of customers is displayed in a tabular form. A Text Area control enables the user to enter the content of the e-mail. After entering the e-mail message, the user needs to click the Send E-Mails button to send e-mails to all the customers of the selected item. This feature lets the user send targeted e-mails to market various offers on a given item.

The following code is from the ViewListOfCustomers.cfm file:

 <html> <head> <title>SellingOnTheWeb The Online Store Sales Data Analysis Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project7.css"> </head> <body> <DIV align="center" >SellingOnTheWeb <I>The Online Store</I><BR>      Sales Data Analyst </DIV> <CFQUERY Name="CustomerList" DATASOURCE = "SalesAnalysis"> SELECT Distinct tblSalesData.CustomerID, Name, Email, Age, City, State, Gender FROM tblSalesData INNER JOIN tblCustomers ON tblSalesData.CustomerID = tblCustomers.CustomerID WHERE ItemDesc='#URL.ItemDesc#' AND PurchaseMonth = '#SESSION.CMonth#' AND PurchaseYear = #SESSION.CYear# </CFQUERY> <cfoutput> <P  >&nbsp;List of Customer for Item: #URL.ItemDesc#</P> </cfoutput> <Table cellpadding="4" cellspacing="4">     <TR>     <TD valign="top">         <Table cellpadding="1" cellspacing="1">             <TH>Customer's Name</TH>             <TH>Age</TH>             <TH>Gender</TH>             <TH>City</TH>             <TH>State</TH>             <cfoutput query = "CustomerList">             <TR>                 <TD>#Name#</TD>                 <TD>#Age#</TD>                 <TD>#Gender#</TD>                 <TD>#City#</TD>                 <TD>#State#</TD>             </TR>             </cfoutput>         </TABLE>     </TD>     <TD valign="top">     <form name ="e-mails" action = "sendmail.cfm" method="post">         <Textarea name="Content" cols="50" rows="12" wrap="soft">Please Enter         the Offer Content to be E-mailed to the Customers.</Textarea>         <cfoutput>         <input type="hidden" name = "ItemDesc" value="#URL.ItemDesc#">         </cfoutput>         <BR><BR>         <input type="submit" name="sendmail" value="Send E-mails">     </form>     </TD> </TABLE> <br> <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR> <a href ="readmail.cfm"><B>Check For E-mails from the Customers</B></a> </body> </html>

The program calls the sendmail.cfm file when the user clicks the Send E-mails button. This file will retrieve the FromEmailAddress field from the tblParameters table. If the field contains valid data, the program sends e-mails. Otherwise, it terminates the process after giving a warning. The code in sendmail.cfm uses the <cfmail> tag to send e-mails. The query attribute of the <cfmail> tag enables the user to specify the name of the query that will fetch the customer name and e-mails. The from attribute enables the user to specify the e-mail address fetched from the tblParameters table. The to attribute contains the e-mail address of the customer fetched in the CustomerList query. The subject attribute is set to the varSubject variable. The <CFTRY> and <CFCATCH> tags enable you to implement error trapping in the code.

The following code is from the sendmail.cfm file:

 <html> <head> <title>SellingOnTheWeb The Online Store Sales Data Analysis Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project7.css"> </head> <body> <DIV align="center" >SellingOnTheWeb <I>The Online Store</I><BR>      Sales Data Analyst </DIV> <CFQUERY Name="getParameters" DATASOURCE = "SalesAnalysis">     SELECT FromEmailAddress from tblParameters </CFQUERY> <cfoutput query ="getParameters">     <CFIF Trim(#FromEmailAddress#) EQ "">         <P >The FromEmailAddress parameter is not specified in                              the tblParameters table in the database.                              The e-mails cannot be sent at the moment.                              Please contact the system administrator.         </P>         <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR>         </body>         </html>         <cfabort>     <CFELSE>         <cfset varFromEmail = #FromEmailAddress#>     </CFIF> </cfoutput> <CFQUERY Name="CustomerList" DATASOURCE = "SalesAnalysis">     SELECT Distinct Name, Email     FROM tblSalesData INNER JOIN     tblCustomers ON tblSalesData.CustomerID = tblCustomers.CustomerID     WHERE ItemDesc='#Form.ItemDesc#'     AND PurchaseMonth = '#SESSION.CMonth#'     AND PurchaseYear = #SESSION.CYear# </CFQUERY> <cfset varSubject = "Great Offer Again on : " & #Form.ItemDesc#> <CFTRY> <cfmail     query="CustomerList"     from="#varFromEmail#"     to="#CustomerList.Email#"     subject="#varSubject#" >     Dear #CustomerList.Name#,     #Form.Content#     Looking forward to seeing you more often on www.SellingOnTheWeb.com     Have fun shopping!!!     With best wishes,     The Marketing Team at SellingOnTheWeb </cfmail> <CFCATCH type="any"> <DIV ALIGN ="center">     <FONT FACE = "Arial">       <H3> An error occurred in the Sales Data Analyst.</H3>       <HR>     </FONT> </DIV> <FONT FACE = "Arial"> The detailed error information is displayed below: <p> <CFOUTPUT>     <B>TYPE</B>: #CFCATCH.TYPE#<BR>     <B>MESSAGE</B>: #CFCATCH.MESSAGE#<BR>     <B>DETAILS</B>:     <FORM>         <TEXTAREA COLS="80" ROWS = "4" NAME =         "error_details">#HTMLEDITFORMAT(CFCATCH.DETAIL)#</TEXTAREA>     </FORM>     <CFSET varError = 1> </CFOUTPUT> </FONT> </CFCATCH> </CFTRY> <P  >&nbsp;The e-mails were sent.                         Please check the e-mail log file at                         C:\CFusionMX\logs\mailsent.txt</P><BR> <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR> </body> </html>

Viewing the E-Mails Sent by Customers

All the pages of the Sales Data Analyst tool contain a Check for E-mails from the Customers link. The readmail.cfm file contains the code for reading the e-mails from the POP server specified in the tblParameters table in the database. The POPServer, UserName, and Password fields are retrieved from the tblParameters table. The code verifies that all these fields return a value before proceeding further. If these fields are empty, a message is displayed to the user.

Using the <CFTRY> and <CFCATCH> tags, you can protect the code in this file from exceptions. The <cfpop> tag enables you to read e-mails from the POP server, and the server attribute enables you to specify the IP address of the POP server to be used. The username and password attributes verify the e-mail account on the specified POP server. The action attribute enables you to indicate whether only the headers or the complete e-mail along with the content should be read.

Another possible action is to delete e-mails from the POP server. The System Administrator, outside the scope of Sales Data Analyst, handles the deletion of e-mails.

The following code shows how these features are implemented:

 <html> <head> <title>SellingOnTheWeb The Online Store Sales Data Analysis Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project7.css"> </head> <body> <DIV align="center" >SellingOnTheWeb <I>The Online Store</I><BR>     Sales Data Analyst </DIV> <P  >&nbsp;E-mails Received from the Customers:</P> <CFQUERY Name="getParameters" DATASOURCE = "SalesAnalysis">     SELECT POPServer,UserName,Password from tblParameters </CFQUERY> <cfoutput query ="getParameters">     <CFIF Trim(#POPServer#) EQ "" or Trim(#UserName#) EQ ""                                     or Trim(#Password#) EQ "" >         <P >The POPServer address, e-mail account user name, and                              password are required parameters for reading the                              e-mails. One or more of these parameters have not                              been specified in the tblParameters table in the                              database.                              The e-mails cannot be read at the moment. Please                              contact the system administrator.         </P>         <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR>         </body>         </html>         <cfabort>     <CFELSE>         <cfset varPOPServer = #POPServer#>         <cfset varUserName = #UserName#>         <cfset varPassword = #Password#>     </CFIF>  </cfoutput> <CFTRY> <cfpop server="#varPOPServer#"     username="#varUserName#"     password="#varPassword#"     action="getall"     name="EmailsFromCustomers" > <cfoutput query="EmailsFromCustomers">     <P >       MessageNumber: #HTMLEditFormat(EmailsFromCustomers.messageNumber)# <br>       To: #EmailsFromCustomers.to# <br>       From: #HTMLEditFormat(EmailsFromCustomers.from)# <br>       Subject: #HTMLEditFormat(EmailsFromCustomers.subject)# <br>       Date: #HTMLEditFormat(EmailsFromCustomers.date)#<br>       Cc: #HTMLEditFormat(EmailsFromCustomers.cc)# <br>       ReplyTo: #HTMLEditFormat(EmailsFromCustomers.replyTo)# <br><br>       Email Content:<br>       #EmailsFromCustomers.body#<br>     </P> </cfoutput> <CFCATCH type="any"> <DIV ALIGN ="center">     <FONT FACE = "Arial">       <H3> An error occurred in the Sales Data Analyst.</H3>       <HR>     </FONT> </DIV> <FONT FACE = "Arial"> The detailed error information is displayed below: <p> <CFOUTPUT>     <B>TYPE</B>: #CFCATCH.TYPE#<BR>     <B>MESSAGE</B>: #CFCATCH.MESSAGE#<BR>     <B>DETAILS</B>:     <FORM>         <TEXTAREA COLS="80" ROWS = "4" NAME =         "error_details">#HTMLEDITFORMAT(CFCATCH.DETAIL)#</TEXTAREA>     </FORM>     <CFSET varError = 1> </CFOUTPUT> </FONT> </CFCATCH> </CFTRY> <BR> <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR> </body> </html>

Here are the complete code listings from all the files in the Sales Data Analyst application. Listing 29.1 provides the complete code for the Start.cfm page.

Listing 29.1: Start.cfm

 <html> <head> <title>SellingOnTheWeb The Online Store Sales Data Analysis Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project7.css"> </head> <body> <DIV align="center" >SellingOnTheWeb <I>The Online Store</I><BR>     Sales Data Analyst </DIV> <CFQUERY Name="getYearMonth" DATASOURCE = "SalesAnalysis">     SELECT CurrentYear, CurrentMonth from tblParameters </CFQUERY> <cfoutput query ="getYearMonth">     <CFIF #CurrentYear# EQ 0 or Trim(#CurrentMonth#) EQ "">         <P >The current year or current month data is missing in                              the tblParameters table in the database. The chart                              cannot be displayed. Please the contact system                              administrator.         </P>         </body>         </html>         <cfabort>     <CFELSE>         <cfset SESSION.CYear = #CurrentYear#>         <cfset SESSION.CMonth = Trim(#CurrentMonth#)>     </CFIF> </cfoutput> <CFQUERY Name="CategoryWiseSales" DATASOURCE = "SalesAnalysis">     SELECT Category, Sum(Amount) AS CategorySales     from tblSalesData     Group by category,PurchaseYear     HAVING PurchaseYear = #SESSION.CYear# </CFQUERY> <CFIF #CategoryWiseSales.RECORDCOUNT# EQ 0>     <cfoutput>       <P >No sales data available for the current year :                            #SESSION.CYear#.                            Please contact the system administrator.       </P>     </cfoutput>     </body>     </html>     <cfabort> </CFIF> <CFQUERY Name="CategoryWiseSalesMonth" DATASOURCE = "SalesAnalysis">     SELECT Category, Sum(Amount) AS CategorySales     from tblSalesData     Group by category,PurchaseMonth,PurchaseYear     HAVING PurchaseMonth = '#SESSION.CMonth#'     AND PurchaseYear = #SESSION.CYear# </CFQUERY> <CFQUERY Name="TopTenItemsForTheMonth" DATASOURCE = "SalesAnalysis">     SELECT Top 10 ItemDesc, Sum(Amount) AS CategorySales from tblSalesData     Group by ItemDesc,PurchaseMonth,PurchaseYear     HAVING PurchaseMonth = '#SESSION.CMonth#'     AND PurchaseYear = #SESSION.CYear#     Order by Sum(Amount) DESC </CFQUERY> <cfoutput> <P  >&nbsp;Sales Figures Category Wise for Year:                         #SESSION.Cyear# and Month:                         #SESSION.CMonth#&nbsp;&nbsp;&nbsp;&nbsp;                         &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;                         &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Top Sold Items                         for This Period </P> </cfoutput> Click on the category bars to see item-level details: <Table cellpadding="4" cellspacing="4">     <TR>       <TD>         <cfchart         xAxisTitle="Category"         yAxisTitle="Sales (in dollars)"         format="jpg"         chartwidth = "350"         chartheight="300"         show3D="no"         tipStyle="mouseover"         backgroundColor="99CC33"         seriesPlacement="cluster"         url="ViewItemForCategory.cfm?Category=$ITEMLABEL$"         >           <cfchartseries             type="bar"             query="CategoryWiseSales"             valueColumn="CategorySales"             itemColumn="Category"             seriesLabel="Current Year's Sales"         />           <cfchartseries             type="bar"             query="CategoryWiseSalesMonth"             valueColumn="CategorySales"             itemColumn="Category"             seriesLabel="Current Month's Sales"             seriesColor="blue"           />         </cfchart>       </TD>       <TD valign="top">         <cfchart         xAxisTitle="Item Description"         yAxisTitle="Sales (in dollars)"         format="jpg"         chartwidth = "390"         chartheight="300"         show3D="no"         tipStyle="mouseover"         pieSliceStyle="solid"         backgroundColor="99CC33"         seriesPlacement="cluster"         >           <cfchartseries             type="pie"             query="TopTenItemsForTheMonth"             valueColumn="CategorySales"             itemColumn="ItemDesc"           />         </cfchart>       </TD>     </TR> </TABLE> <a href ="readmail.cfm"><B>Check For E-mails from the Customers</B></a><BR> </body> </html>

Listing 29.2 provides the complete code for the ViewItemForCategory.cfm page.

Listing 29.2: ViewItemForCategory.cfm

 <html> <head> <title>SellingOnTheWeb The Online Store Sales Data Analysis                     Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project7.css"> </head> <body> <DIV align="center" >     SellingOnTheWeb <I>The Online Store</I><BR>     Sales Data Analyst </DIV> <cfoutput> <P  >&nbsp;Sales Figures Item Wise for the Category:                         #URL.Category#</P> </cfoutput> Click on the item bars to see the customer list for the item: <CFQUERY Name="ItemsForTheCategory" DATASOURCE = "SalesAnalysis">     SELECT ItemDesc, Sum(Amount) AS ItemSales from tblSalesData     Group by ItemDesc,PurchaseMonth,PurchaseYear,Category     HAVING PurchaseMonth = '#SESSION.CMonth#'     AND PurchaseYear = #SESSION.CYear#     AND Category = '#URL.Category#'     Order by Sum(Amount) DESC </CFQUERY> <cfchart          xAxisTitle="Item Desc"          yAxisTitle="Sales (in dollars)"          format="jpg"          chartwidth = "700"          chartheight="300"          show3D="no"          tipStyle="mouseover"          backgroundColor="FFFF99"          url="ViewListOfCustomers.cfm?ItemDesc=$ITEMLABEL$"          rotated="yes"          showborder="yes" >            <cfchartseries              type="bar"              query="ItemsForTheCategory"              valueColumn="ItemSales"              itemColumn="ItemDesc"            /> </cfchart> <BR> <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR> <a href ="readmail.cfm"><B>Check for E-mails from the Customers</B></a> </body> </html>

Listing 29.3 provides the complete code for the ViewListOfCustomers.cfm page.

Listing 29.3: ViewListOfCustomers.cfm

 <head> <title>SellingOnTheWeb The Online Store Sales Data Analysis Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project7.css"> </head> <body> <DIV align="center" >     SellingOnTheWeb <I>The Online Store</I><BR>      Sales Data Analyst </DIV> <CFQUERY Name="CustomerList" DATASOURCE = "SalesAnalysis">     SELECT Distinct tblSalesData.CustomerID, Name, Email,                       Age, City, State, Gender     FROM tblSalesData INNER JOIN     tblCustomers ON tblSalesData.CustomerID = tblCustomers.CustomerID     WHERE ItemDesc='#URL.ItemDesc#'     AND PurchaseMonth = '#SESSION.CMonth#'     AND PurchaseYear = #SESSION.CYear# </CFQUERY> <cfoutput> <P  >&nbsp;List of Customer for Item: #URL.ItemDesc#</P> </cfoutput> <Table cellpadding="4" cellspacing="4">     <TR>       <TD valign="top">         <Table cellpadding="1" cellspacing="1">             <TH>Customer's Name</TH>             <TH>Age</TH>             <TH>Gender</TH>             <TH>City</TH>             <TH>State</TH>             <cfoutput query = "CustomerList">             <TR>                 <TD>#Name#</TD>                 <TD>#Age#</TD>                 <TD>#Gender#</TD>                 <TD>#City#</TD>                 <TD>#State#</TD>             </TR>             </cfoutput>         </TABLE>       </TD>       <TD valign="top">         <form name ="e-mails" action = "sendmail.cfm" method="post">         <Textarea name="Content" cols="50" rows="12" wrap="soft">Please enter          the offer content to be e-mailed to the customers.</Textarea>         <cfoutput>         <input type="hidden" name = "ItemDesc" value="#URL.ItemDesc#">         </cfoutput>         <BR><BR>         <input type="submit" name="sendmail" value="Send E-mails">         </form>       </TD> </TABLE> <br> <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR> <a href ="readmail.cfm"><B>Check For E-mails from the Customers</B></a> </body> </html>

Listing 29.4 provides the complete code for the sendmail.cfm page.

Listing 29.4: sendmail.cfm

 <html> <head> <title>SellingOnTheWeb The Online Store Sales Data Analysis          Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project7.css"> </head> <body> <DIV align="center" >     SellingOnTheWeb <I>The Online Store</I><BR>     Sales Data Analyst </DIV> <CFQUERY Name="getParameters" DATASOURCE = "SalesAnalysis">     SELECT FromEmailAddress from tblParameters </CFQUERY> <cfoutput query ="getParameters">     <CFIF Trim(#FromEmailAddress#) EQ "">         <P >The FromEmailAddress parameter is not                              specified in the tblParameters table in the                              database. The e-mails cannot be sent at the                              moment. Please contact the system                              administrator.</P>         <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR>         </body>         </html>         <cfabort>     <CFELSE>         <cfset varFromEmail = #FromEmailAddress#>     </CFIF> </cfoutput> <CFQUERY Name="CustomerList" DATASOURCE = "SalesAnalysis">     SELECT Distinct Name, Email     FROM tblSalesData INNER JOIN     tblCustomers ON tblSalesData.CustomerID = tblCustomers.CustomerID     WHERE ItemDesc='#Form.ItemDesc#'     AND PurchaseMonth = '#SESSION.CMonth#'     AND PurchaseYear = #SESSION.CYear# </CFQUERY> <cfset varSubject = "Great Offer Again on : " & #Form.ItemDesc#> <CFTRY> <cfmail     query="CustomerList"     from="#varFromEmail#"     to="#CustomerList.Email#"     subject="#varSubject#" >     Dear #CustomerList.Name#,     #Form.Content#     Looking forward to seeing you more often on www.SellingOnTheWeb.com     Have fun shopping!!!     With Best Wishes,     The Marketing Team at SellingOnTheWeb </cfmail> <CFCATCH type="any"> <DIV ALIGN ="center">     <FONT FACE = "Arial">     <H3> An error occurred in the Sales Data Analyst.</H3>     <HR>     </FONT> </DIV> <FONT FACE = "Arial"> The detailed error information is displayed below: <p> <CFOUTPUT>     <B>TYPE</B>: #CFCATCH.TYPE#<BR>     <B>MESSAGE</B>: #CFCATCH.MESSAGE#<BR>     <B>DETAILS</B>:     <FORM>         <TEXTAREA COLS="80" ROWS = "4" NAME =         "error_details">#HTMLEDITFORMAT(CFCATCH.DETAIL)#</TEXTAREA>     </FORM>     <CFSET varError = 1> </CFOUTPUT> </FONT> </CFCATCH> </CFTRY> <P  >&nbsp;The e-mails were sent.                         Please check the e-mail                         log file at C:\CFusionMX\logs\mailsent.txt</P><BR> <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR> </body> </html>

Listing 29.5 provides the complete code for the readmail.cfm page.

Listing 29.5: readmail.cfm

 <html> <head> <title>SellingOnTheWeb The Online Store Sales Data Analysis Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project7.css"> </head> <body> <DIV align="center" >     SellingOnTheWeb <I>The Online Store</I><BR>     Sales Data Analyst </DIV> <P  >&nbsp;E-mails Received from the Customers:</P> <CFQUERY Name="getParameters" DATASOURCE = "SalesAnalysis">     SELECT POPServer,UserName,Password from tblParameters </CFQUERY> <cfoutput query ="getParameters">     <CFIF Trim(#POPServer#) EQ "" or Trim(#UserName#) EQ ""                               or Trim(#Password#) EQ"" >         <P >           The POPServer address, E-mail account user name, and           password are required           parameters for reading the e-mails. One or more of these parameters           have not been specified in the tblParameters table in the database.           The e-mails cannot be read at the moment. Please contact the system           administrator.</P>         <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR>         </body>         </html>         <cfabort>     <CFELSE>         <cfset varPOPServer = #POPServer#>         <cfset varUserName = #UserName#>         <cfset varPassword = #Password#>     </CFIF> </cfoutput> <CFTRY> <cfpop server="#varPOPServer#"     username="#varUserName#"     password="#varPassword#"     action="getall"     name="EmailsFromCustomers" > <cfoutput query="EmailsFromCustomers">     <P >       MessageNumber: #HTMLEditFormat(EmailsFromCustomers.messageNumber)# <br>       To: #EmailsFromCustomers.to# <br>       From: #HTMLEditFormat(EmailsFromCustomers.from)# <br>       Subject: #HTMLEditFormat(EmailsFromCustomers.subject)# <br>       Date: #HTMLEditFormat(EmailsFromCustomers.date)#<br>       Cc: #HTMLEditFormat(EmailsFromCustomers.cc)# <br>       ReplyTo: #HTMLEditFormat(EmailsFromCustomers.replyTo)# <br><br>       Email Content:<br>       #EmailsFromCustomers.body#<br>     </P> </cfoutput> <CFCATCH type="any"> <DIV ALIGN ="center">     <FONT FACE = "Arial">     <H3> An error occurred in the Sales Data Analyst.</H3>     <HR>     </FONT> </DIV> <FONT FACE = "Arial"> The detailed error information is displayed below: <p> <CFOUTPUT>     <B>TYPE</B>: #CFCATCH.TYPE#<BR>     <B>MESSAGE</B>: #CFCATCH.MESSAGE#<BR>     <B>DETAILS</B>:     <FORM>         <TEXTAREA COLS="80" ROWS = "4" NAME =         "error_details">#HTMLEDITFORMAT(CFCATCH.DETAIL)#</TEXTAREA>     </FORM>     <CFSET varError = 1> </CFOUTPUT> </FONT> </CFCATCH> </CFTRY> <BR> <a href ="Start.cfm"><B>Back to the Start Page</B></a><BR> </body> </html>

Listing 29.6 provides the complete code for the Application.cfm page.

Listing 29.6: Application.cfm

 <cfapplication     name = "SalesDataAnalyst"     SESSIONMANAGEMENT="Yes"     setClientCookies = "Yes"     SESSIONTIMEOUT="#CreateTimeSpan(0,0,30,0)#">

Listing 29.7 provides the complete code for the Project7.css file.

Listing 29.7: Project7.css

 body {     background-color: #FF9900;     font-family: Tahoma, Arial, Helvetica, sans-serif;     font-size: 12px;     line-height: 24px;     color: #000000; } td {     font-family: Tahoma, Arial, Helvetica, sans-serif;     font-size: 9px;     line-height: 24px;     color: #000000;     font-weight: bold; } th {     font-family: Tahoma, Arial, Helvetica, sans-serif;     font-size: 9px;     line-height: 24px;     color: #FFFFFF;     font-weight: bold;     background-color: #CC6600; } a {     color: #000000;     font-weight: bold; } form {     background-color: #FF9900;     font-family: Tahoma, Arial, Helvetica, sans-serif; } div {     background-color: #FF9900;     color: #000000;     font-family: Tahoma; } .title {     font-family: Tahoma;     font-size: 18px;     line-height: 30px;     background-color: #FF6633;     color: #000000;     font-weight: bold;     text-decoration: underline; } a:hover {     color: #000000;     background-color: #FFFF99; } .message {     font-family: Tahoma, Arial, Helvetica, sans-serif;     font-size: 12px;     font-weight: bold;     line-height: 30px;     color: #990000;     background-color: #FFFF99; } .summary {     font-family: Tahoma, Arial, Helvetica, sans-serif;     font-size: 10px;     line-height: 30px;     background-color: #CCCC99; } .InfoLabel {     font-family: Tahoma, Arial, Helvetica, sans-serif;     font-size: 11px;     line-height: 30px;     background-color: #FFFF99;     color: #000000;     font-weight: bold; } cfform {     background-color: #CC6600;     color: #99CCFF; } .DivText {     color: #FFFFFF;     background-color: #666666; } .divwithform {     background-color: #999999;     font-family: Tahoma, Arial, Helvetica, sans-serif; } .EmailContent {     font-family: Tahoma, Arial, Helvetica, sans-serif;     font-size: 11px;     line-height: 30px;     background-color: #CC6600;     color: #FFFF99;     font-weight: bold; }