Reporting Application Summary Data via the Web

Highlights

This chapter defines a new project case study. Once again, this project centers around the continued efforts to improve the order/inventory process at Intuit Mechanical Tools. It has been a number of months since Molly completed her work on developing the summary report consolidation project. Things have gone very smoothly, and Molly has moved on to work on the development of other projects. In the meantime, several members of the operations management staff have been asking the company's IT staff to once again improve the order/inventory reporting process by providing browser-based reporting. This way, anyone within the company who has access to the corporate Intranet will be able to access the consolidated summary reports directly from a desktop.

Two new programmers have recently been hired at Intuit. They are Alexander Banks and William Carter. They both have a Visual Basic programming background and some experience with HTML. IT management thinks that assigning them to work on this project will help both of them to better understand the order/inventory system while also getting them some exposure and interaction with the operations staff.

As you work your way through this project, you will learn how to create interactive HTML pages by embedding VBScripts that leverage the power of Internet Explorer object models. You will also learn how to use VBScript and the WSH to create HTML files and provide dynamic content. In addition, you will learn how to automate a number of other tasks, including:

• How to use the Folder object to administer the contents of directories
• How to use VBScript to create cookies that store configuration settings on client computers
• How to enhance Web pages using VBScript to create graphic effects

Project Overview

Operations management would like to augment the reporting process by making consolidated summary reports available on the company's intranet so that they are readily accessible to everybody. This not only will make things convenient for many people within the company, but also will offload the responsibility now assigned to operations staff for collecting and distributing order/inventory consolidated summary reports.

Collecting Project Requirements

Alexander and William begin their work on this project by first meeting with Molly to learn about the work that she did on her two previous order/inventory reporting projects. Once they felt like they had a good understanding of how things worked, they went to talk with the company's Web master, Michael Barns, to discuss how to best go about the development of the project's Web site. After describing their assignment and talking it over with Michael, it was agreed that a new directory would be set up on the company Windows 2000 Web server called d:IntuitOrderInventoryReporting and that Alexander and William would be granted full control over the folder and its contents. In addition, Michael instructed them to name their main HTML page Default.html so that he could set them up with their own URL, which he told them would be http://Intuit.com/OrdInv/Default.html. Using this model, Alexander and William can then create whatever file and folder structure they wish within the d:IntuitOrderInventoryReporting directory in order to support the storage of the HTML and reporting files that will make up the order/inventory Reporting Web site.

Once they understood the existing reporting infrastructure as well as how they would organize the HTML files and report files on the company's Web server, Alexander and William's next step is to sit down with the operations management to collect detailed project requirements. During this meeting, they learn that the operations department is pleased with the current format of the consolidated summary report and no additional content is required. In addition, operations wants to continue to receive network notifications when the consolidated summary reports are created on the Windows 2000 Professional workstation. Therefore, no changes are required to the scripts that were written by Molly.

What the operations staff wants is to be able to access the daily consolidated summary report via Internet Explorer. After talking for a while, the operations management added another requirement. They want to be able to access a history of up to 90 days' worth of consolidated summary report files. Operations also wants to know if the report data could be presented in a tabular spreadsheet format instead of as a text file. They thought that such a format would make the report easier to review and analyze.

Finally, Alexander and William are asked if it would be possible to store the Microsoft Word versions of the consolidated archive reports and make them available for download. This way, individuals who still need a hard copy of the original report could download it instead of having to request one from operations.

Documenting Project Requirements

After meeting with the operations management staff, Alexander and William got together the next day to go over the information that they collected. They also asked Molly to sit in on this meeting in order to solicit her input. Together they assembled the following list of requirements from their meeting with operations management staff.

• Automate the distribution of consolidated summary reports to the corporate Web server
• Read and process each day's consolidated summary report and create an HTML version of the report in a table-based format
• Maintain a three-month archive of HTML consolidated summary reports on the corporate Web server and make those reports accessible online
• Make the Word versions of the consolidated summary reports available for download
• Complete the development and testing of the project within 30 days

Alexander, William, and Molly then discussed each of these requests to determine whether they were something that could be accomplished. They came to the conclusion that they could provide scripted solutions that would meet the requirements of each of these requests. Next, at Molly's suggestion, Alexander and William typed up this list of requirements and distributed them to both their manager and the operations management staff for approval.

Performing a High Level Design

With an overall understanding of the current scripting environment and an approved requirements document, Alexander and William sit down and begin work on a preliminary high-level design. They decide upon a solution that would involve the use of VBScript in two different ways. First, using the WSH, they will develop a collection of VBScripts that would execute on the Windows 2000 Professional workstation, as depicted in Figure 26.1.

Figure 26.1: VBScript and the WSH will be used behind the scenes to create HTML pages that display the consolidated summary reports

The following collection of scripts will be created and executed on the Windows 2000 Professional workstation:

• Scheduler. This is a scheduling script that manages the sequential execution of the other three scripts that run on the Windows 2000 Professional workstation.
• Report Deployment. This script will create a network connection to the d:IntuitOrderInventoryReporting folder on the company's Web server and copy over the HTML and Word report files.
• HTML Conversion. This script will create an HTML page that displays the current day's consolidated summary report in a table format.
• Archive Link Maintenance. This script will create an HTML page that lists links to each consolidated summary report stored in the summary report archive on the Web server.
• Remote Archive Management. This script will remotely administer the management of a three-month HTML page and Word report archive on the company's Web server.

In addition to the previous list of scripts, VBScripts will be embedded within the HTML pages that will make up the order/inventory Reporting Web site. These embedded VBScripts will control frame navigation and form validation and will provide enhanced visual effects such as link rollovers and the display of messages on the Internet Explorer status bar. VBScript will also be used to create and manage cookies that will be used to store customized configuration settings for users who visit the Web site.

Alexander and William plan on using HTML frames to present consolidated summary report files and to use VBScript to control the loading of HTML consolidated summary reports and the archive management page. They plan on creating a main page from which visitors can navigate to three lower-level pages as depicted in Figure 26.2. The first page will display the current day's report, the second page will display a collection of links to archived reports, and the third page will allow visitors to specify personalized configuration settings.

Figure 26.2: VBScripts embedded within HTML pages will be used to control the navigation and presentation of information

Alexander and William decide that the best way to complete this project is to divide the work up, so each person will be responsible for completing specific tasks. Table 26.1 outlines the task assignments that they divided between themselves.

Accomplishing Prerequisite Tasks

Alexander is responsible for performing the project's preliminary tasks, which include automating the scheduled execution of the WSH VBScripts that are part of this project and the creation of new registry entries. To facilitate the execution of the project's WSH-run VBScripts, Alexander has decided to modify the scheduler script developed by Molly for execution on the Windows 2000 Professional workstation (from Chapter 19, "Scheduling Script Execution") as shown below.

'************************************************************************* 'Script Name: Script 26.1.vbs 'Author: Jerry Ford 'Created: 04/25/03 'Description: This script runs scripts associated with the order/inventory 'reporting system '************************************************************************* 'Initialization Section Option Explicit On Error Resume Next Dim WshShl, intRcChk Set WshShl = WScript.CreateObject("WScript.Shell") intRcChk = 0 'Main Processing Section RunScript("ErrorAnalyzer.vbs") RunScript("SalesAnalyzer.vbs") RunScript("ReturnsAnalyzer.vbs") RunScript("ProductionAnalyzer.vbs") 'Three new sets of statements added to support web based reporting intRcChk = RunScript("HTMLConvert.vbs") If intRcChk > 0 Then NotifyOperationsStaff("HTMLConvert.vbs") Else intRcChk = RunScript("ArchiveLinkMgr.vbs") If intRcChk > 0 Then NotifyOperationsStaff("ArchiveLinkMgr.vbs") Else intRcChk = RunScript("WebArchiveMgr.vbs") If intRcChk > 0 Then NotifyOperationsStaff("WebArchiveMgr.vbs") End If End If End If If Day(date()) = 1 Then RunScript("ArchiveManager.vbs") End If 'Terminate script execution TerminateScript() 'Procedure Section Function RunScript(strScriptName) RunScript = WshShl.Run(ScriptName, 1, True) End Function Sub WriteToEventLog() WshShl.LogEvent 4, "Report and Log Analyzer Scheduler Script executing." End Sub Sub NotifyOperationsStaff(strFailedScript) Dim strUserName, strNtkNotifiyList Dim astrNotifyArray strNtkNotifiyList = _ WshShl.RegRead("HKLMSoftwareIntuitVBScriptsMstSumRptsNtkNotifiyList") astrNotifyArray = Split(strNtkNotifiyList) For Each strUserName In astrNotifyArray WshShl.Run "Net Send " & strUserName & " " & "Script " & _ strFailedScript & " failed. " &_ "Please notify The IT Dept." Next End Sub Sub TerminateScript() WScript.Quit() End Sub

Alexander plans to add logic to each of the WSH-executed VBScripts that he is responsible for developing in order to return an error code indicating whether or not they ran successfully. In doing so, he provides the scheduling script with the ability to determine whether a problem occurred.

Alexander chooses for the moment not to modify the manner in which the scheduler script's existing execution calls are made. Instead, he will explain to Molly what he has done and suggest that she retrofit her VBScripts to support the same functionality. However, he puts script return code checking in place for the scripts that he is developing. He does this by defining a variable called intRcChk and using it to store the value returned by the RunScript function. If a value greater than zero is returned, then an error has occurred within the called script, and the NotifyOperationsStaff() subroutine is called and passed the name of the script that failed.

To accommodate this new functionality, Alexander has to modify the RunScript() procedure by changing it from a subroutine to a function. He also modifies the procedure to return the return code supplied by each script that it execute.

Alexander also modifies the scheduling script by moving the WScript.Quit() statement into its own subroutine in order to improve the overall organization of the scheduling script. Finally, he adds the NotifyOperationsStaff() subroutine to the script, which he copies and pastes out of the VBScript that creates the consolidated summary report (from Chapter 24, "Processing and Consolidating Report Data"). Using this subroutine, the scheduling script can notify the operations staff of any errors that occur when processing his scripts. This will give the IT programming staff a chance to fix things before everyone comes in to work looking for the online copy of the consolidated summary reports.

Rather than develop a script to create a registry key and values for this new script, Alexander decides that since he only needs to create seven new registry values, it would be faster to create them manually using the Regedt32 utility. Alexander creates a new registry subkey under HKLMIntuitVBScript called WebRpting to store each value for his new scripts, which are briefly explained below.

• HKLMIntuitVBScriptWebRptingDebug. Specifies whether the script should display intermediate results in pop-up dialog boxes when executing (for example, when manually executed for troubleshooting purposes)
• HKLMIntuitVBScriptWebRptingEventLogging. Specifies whether the script should write informational messages to the Windows application event log
• HKLMIntuitVBScriptWebRptingConSolRptLoc. Specifies the location of the folder on the Windows 2000 Professional workstation where copies of the consolidated summary reports are to be stored
• HKLMIntuitVBScriptWebRptingHTMLFolder. Specifies the folder on the Windows 2000 Professional workstation where the HTML versions of the consolidated summary reports are to be stored
• HKLMIntuitVBScriptWebRptingWebServer. Specifies the network name assigned to the company's Web server
• HKLMIntuitVBScriptWebRptingShare_Rpts. Specifies the name of the shared folder on the company's Web server where copies of the Word versions of the consolidated summary reports are to be stored
• HKLMIntuitVBScriptWebRptingShare_HTML. Specifies the name of the shared folder on the company's Web server where copies of the HTML versions of the consolidated summary reports are to be stored

Designing the Web Site

William is responsible for developing the HTML portion of this project and for its embedded VBScripts. As shown earlier in Figure 26.2, William plans to create a main page and to use HTML frames to control the presentation of data. Developing this Web site will require a basic understanding of HTML syntax as well as a working knowledge of frame design and implementation. An understanding of how to create and use HTML links to tie together HTML pages is also required.

Developing a Home Page

The main page or home page is the default page that all visitors will see when they open their Internet Explorer browser and type in http://Intuit.com/OrdInv/Default.html. In order to develop this HTML page, William will need to use a number of different development techniques, including:

• Embedding VBScripts. To provide graphic effects and process configuration settings stored in client-side cookies
• Creating a Links page (left pane). To create a Web page that defines links to the other HTML pages that will make up the Web site
• Creating a Welcome page (right pane). To provide visitors with a customized welcome message as well as instructions for using the Web site
• Using redirection. To redirect new visitors to the Registration and Configuration page before allowing them to access the rest of the Web site
• Implementing browser detection. To ensure that visitors are using a supported version of Internet Explorer
• Reading cookies. To retrieve user preferences and configuration settings established on the Web site's Registration and Configuration page

Creating the Registration and Configuration Page

The Registration and Configuration page will be used to collect the name of each visitor to the order/inventory Reporting Web site. In addition, it will allow visitors to specify personal preferences for things such as the background color of the links page and their preferred default page. In order to complete the development of this page, William will have to make use of:

• HTML forms
• VBScript form validation capabilities
• VBScript's ability to create and store cookies
• VBScript's ability to take control of the browser's status bar
• VBScript's ability to interact with visitors using pop-up dialog boxes

Converting Consolidated Summary Reports to HTML Pages

Alexander wants to develop the VBScript that creates an HTML version of the consolidated summary report based on the contents of the text version of the report. To do so, he will need to use a number of FileSystemObject object methods, including:

• FileExists(). Used to avoid errors by first validating that a file exists before trying to open it
• OpenTextFile(). Opens the specified file, allowing it to be further manipulated by other methods
• ReadLine(). Provides the ability to read a line of text in a file
• Close(). Closes a previously opened file
• WriteLine(). Provides the ability to write a line of text to the specified file
• WriteBlankLines(). Provides the ability to write a blank line in the specified file

In addition to these methods, Alexander will need a solid understanding of HTML syntax and will have to use this knowledge to insert HTML formatting tags within the HTML file generated by this script. Alexander will also need to use the WshShell object's RegRead() method to retrieve the script's configuration settings from the Windows registry.

Building the Report Archive Page

In addition to setting up the Archive Link Management script to retrieve its configuration settings from the Windows registry, Alexander will need to familiarize himself with the following objects, properties, and methods:

• GetFolder(). A FileSystemObject method that provides the ability to retrieve a reference to a specified folder
• Folder Object. Provides access to all the properties associated with a folder
• File Object. Provides access to all the properties associated with a file
• Files Collection. Provides access to all the files that reside in a specified folder
• Files Property. A property belonging to the File object that retrieves a reference to a Files Collection

In addition to these new objects, properties, and methods, Alexander will have to use the following FileSystemObject methods in order to generate the Archive Link Management HTML page:

• OpenTextFile()
• Close()
• WriteLine()
• WriteBlankLines()

Distributing HTML and Word Files and Performing Archive Management

In the final script of the project, Alexander will need to learn how to work with the File object's Copy() method in order to copy over the HTML and Word files from the Windows 2000 Professional workstation to the company's Web server. In addition, he will need to use the following WshNetwork methods to establish a network connection to the Web server and to break that connection when the script is done using it.

• MapNetworkDrive()
• RemoveNetworkDrive()

Specifically, Alexander will need to copy the HTML pages and Word files created on the Windows 2000 Professional workstation to the folders listed below on the Web server.

• D:Order_InventoryHTML. Stores a minimum of 90 days' worth of HTML reports containing Web-based versions of the consolidated summary reports
• D:Order_InventoryRpts. Stores a minimum of 90 days' worth of Word reports containing the consolidated summary reports

Finally, to automate the execution of the archive management process, Alexander will have to use the following FileSystemObject object methods:

• FileExists()
• DeleteFile()

Summary

This chapter introduced you to the final case study in this book. An overview of the project assigned to Alexander and William was provided. This included the development of a list of project requirements. In addition, a high-level design was presented that outlined the overall plan for providing a Web-based reporting solution. This included the identification of each script that is to be developed, as well as the major VBScript language constructs that will be used in order to create each of these scripts. In the six chapters that follow, you will get the opportunity to see how Alexander and William tackle each of the tasks involved in completing this project.