Expense Report Application

[Previous] [Next]

Although the Event Service could be used for non-Exchange Server-related applications, most developers use the Event Service to automate administrative tasks such as modifying user directory information and replicating data from Microsoft SQL Server into Exchange or vice versa, or to write workflow applications. In this section, we will look at a simple workflow application—an Expense Report application—that uses the Event Service and some custom code written using VBScript.

When most developers think of building a workflow application, the first type that pops into their minds is an expense reporting application, because most expense reporting applications require some type of report status tracking and approval routing, as well as a system of escalation for nonapproved reports. To help you better understand how to write applications using the Exchange Event Service, let's look at a sample expense report application using the Event Service technology.

Setting Up the Expense Report Application

Before you can install the application, you must have a Windows NT 4.0 Server and a client with certain software installed. Table 13-1 describes the installation requirements for the application.

Table 13-1. Installation requirements for the Expense Report application.

Minimum Software Requirements	Installation Notes
Exchange Server 5.5 Service Pack1 or higher with Outlook Web Access	Service Pack 3 is recommended.
Internet Information Server 3.0 or higher with Active Server Pages	Internet Information Services 4.0 is recommended.
CDO library (cdo.dll) and CDO Rendering library (cdohtml.dll)	Exchange Server 5.5 SP1 installs CDO library 1.21 and CDO Rendering library 1.21. Outlook installs CDO library 1.21.
For the client:
A Web browser and Outlook 98	For the Web browser, Microsoft Internet Explorer is recommended. You can run the client software on the same machine or on a separate machine.

To install the Expense Report application, copy the Expense Report folder from the companion CD to the Web server where you want to run the application. Start the IIS administration program. Create a virtual directory that points to the location where you copied the expense report files, and name the virtual directory expense. Enable the Execute permissions option for the virtual directory. You will be able to use the following URL to access your Expense Report application: http://yourservername/expense.

Open the Exchange Administrator program. Open the Properties dialog box for the Folders\System Folders\Events Root\EventConfig_servername folder. Click the Client Permissions button, add a user who will administer the Expense Reports folder, and grant the user Author permissions. Click OK twice. Start the Registry Editor on your server, and then open the key named HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSExchangeES\Parameters. Set the Logging Level DWORD to 5 to log the maximum amount of information.

NOTE

---

Be sure to set Logging Level to 0 when you are finished testing the Expense Report application. If you do not, your Application log will be quickly filled up with MSExchangeES logging entries.

Launch Outlook using the user you selected earlier to administer the Expense Reports folder. Create a new public folder named Expense Reports under All Public Folders. Right-click on the Expense Reports folder, and select Properties. On the Agents tab, click the New button. Type Expense Agent as the Agent Name. Check the A Scheduled Event Occurs check box and the A New Item Is Posted In This Folder check box. Click the Schedule button, set a 15-minute interval, and click OK.

In the Expense Agent dialog box, click Edit Script to display the event scripting starter code in Notepad. On the companion CD, locate the file named ExpenseAgentScript.txt in the expense report files. Open ExpenseAgentScript.txt, copy all of the code, and paste it into the starter code in Notepad, replacing the existing code. Perform a search in the code, and replace the three instances of the text localhost with the name of your Web server. Save and close Notepad. At this point, the Expense Agent dialog box should look like Figure 13-12. Click OK twice to return to Outlook.

Figure 13-12. The configured Expense Agent dialog box.

Open the Exchange Administrator program, and open the Properties dialog box for the Expense Reports public folder. Click on the Advanced tab, and uncheck the Hide From Address Book check box. Click OK. You can now access the Expense Report application using the URL http://yourservername/expense.

NOTE

---

Included with the Expense Report files on the companion CD is a .pst file named Expense Reports.pst. This file shows some sample expense reports. To see these samples, clear the read-only flag on Expense Reports.pst and open it in Outlook.

Functionality of the Expense Report Application

After entering a valid mailbox, the main page of the Expense Report application is displayed, as shown in Figure 13-13. From the main page, the user can click the Submit A New Expense Report link to enter and submit an expense report, as shown in Figure 13-14. As you can see, users submit expense reports in this application by using a simple Web page, but you can easily modify an Outlook form or a Microsoft Excel document to implement the same functionality as the Web page.

Figure 13-13. The main page of the Expense Report application.

Figure 13-14. The page used to enter and submit expense reports.

After the user submits an expense report from the Web page, the report is emailed to the Expense Reports public folder that contains the agent, named Expense Agent. This agent fires on two of the four supported events. In the Expense Report application, I assume that expense reports are not normally changed while in process and are not deleted once submitted. Thus, the agent fires for these two events: when a new expense item is created in the folder and when every 15 minutes pass (this is a timer event).

The Expense Agent receives the new expense report and calls the Folder_OnMessageCreated subroutine in its associated VBScript file. This subroutine checks the amount of the expense report, and if the amount is over a specific limit—in this case, $5,000—the agent looks up the manager of the user in the directory and sends an e-mail to the manager with a link to the expense report, as shown in Figure 13-15. If the amount is under the limit, the agent automatically approves the expense report and routes it for payment.

Figure 13-15. E-mail, with a link to an expense report sent to a manager by the Expense Agent, requesting approval of the expense.

Now we all know that people sometimes get bogged down in their e-mail and do not always quickly respond to requests for expense report approvals. To help facilitate the responsiveness of managers who need to approve expense reports, the agent fires on a 15-minute timer event. Every 15 minutes, the agent calls the Folder_OnTimer subroutine, which checks the current status of all expense reports in the folder. If the subroutine finds an expense report that has not been approved yet and that has been sitting for more than 15 minutes, the agent automatically looks up the manager of the current person who is supposed to approve the expense report and routes the report to that person for approval. This process will continue every 15 minutes until either the expense report is approved or until the agent runs out of managers to reroute the report to. Each rerouted report sends a polite message to the manager who was supposed to approve the report and updates the user on the status of the routing.

If a report is rerouted to other managers, any manager in the route—from the first manager to the top person in the organization—can approve or reject the expense report at any time. This flexibility allows anyone with authority that sees the report to approve it, not just the current manager the report is routed to.

Throughout the entire application, users can go to a Web page to track the status of their expense reports. As you can see in Figure 13-16, I have used familiar traffic icons for expense report status. A stop sign means the expense report was rejected, a yellow light means that it is currently waiting for approval, and a green light means that the expense report was approved. Also included is text that describes the current report status, such as whether the report is waiting approval, the name of the person in the management chain currently reviewing the report, whether the report was rejected or approved, and who rejected or approved it.

Figure 13-16. The Expense Report Status Page. From this page, users can check the status of their expense reports as well as find out who is currently reviewing the report.

Managers see a slightly different view of the information in the application's main screen. By using a CDO MessageFilter object, the Web page figures out whether managers have any reports waiting for approval in the Expense Reports public folder. If there are reports awaiting approval, the page indicates how many, as shown in Figure 13-17.

Figure 13-17. The Web page for managers who have expense reports pending approval. This Web page uses a CDO MessageFilter object to quickly find pending expense reports.

Expense report status information such as the current approver's name; expense amounts for items such as travel, hotel, and rental car; and which stage of approval the report is in (1 for Submitted, 2 for Awaiting Approval, 3 for Rejected, 4 for Approved) are all stored with the individual message as custom properties. This means that the agent can update the status of the expense report using only CDO methods, which you will see when we examine the Expense Agent script.

Expense Agent Script

Now that you understand some of the functionality of the Expense Report application, let's look at the code that implements it. The two main pieces of the application are the Web pages that constitute part of the interface and the agent that implements the business logic. We will look at some of the CDO code behind the Web pages for the application, because these pages show you how to use some of the CDO objects in a way that was not demonstrated in Chapter 12.

The Expense Agent, as mentioned earlier, fires on only two events. The script for the agent includes two helper functions, GetEventDetails and WriteToLog.

GetEventDetails Function

The first helper function is the GetEventDetails function, shown in the following code:

 'DESCRIPTION: Get the details of the event that fired Private Sub GetEventDetails On Error Resume Next Dim oStores Dim Temp Dim idTargetFolder Dim idTargetMessage idTargetFolder = EventDetails.FolderID idTargetMessage = EventDetails.MessageID 'Some of the above might not exist Err.Clear Set AMSession = EventDetails.Session If Err.Number = 0 Then 'We're going to send a message, so let's get the 'Outbox here Set fldrOutbox = AMSession.Outbox If Err.Number = 0 Then Set oStores = AMSession.InfoStores If Err.Number = 0 Then Set Temp = oStores.Item(1).RootFolder Set Temp = oStores.Item(2).RootFolder Set fldrTarget = AMSession.GetFolder( _ idTargetFolder, Null ) If Err.Number = 0 Then Set msgTarget = AMSession.GetMessage( _ idTargetMessage, Null ) If Not Err.Number = 0 Then WriteToLog 0,"Session.GetMessage Failed: " & _ Err.Description End If Else WriteToLog 0,"Session.GetFolder Failed: " & _ Err.Description End If Else WriteToLog 0,"Session.InfoStores Failed: " & _ Err.Description End If Else WriteToLog 0,"Outbox.Messages Failed: " & _ Err.Description End If Else WriteToLog 0,"EventDetails.Session Failed: " & Err.Description End If End Sub 

The GetEventDetails function pulls the intrinsic objects and variables passed to the script and assigns them to other variables. The script then proceeds to get the Outbox of the pre-logged-on CDO user and retrieve both the folder and the message corresponding to the event. If any of these calls fail, the GetEventsDetails function calls the second helper function, WriteToLog.

WriteToLog Function

The WriteToLog function allows you to record custom messages in the agent log file. Earlier in this chapter, we discussed the Script.Response method in the context of helping you debug your agents, and you learned that if you make multiple calls to this method, you have to keep building your string by passing it previous strings. The WriteToLog function implements this functionality, and it takes two parameters that allow you to customize how events are logged. The first parameter, when set to 1, records the name of the message when recording your event in the log. The second parameter is the string you want to place in the log. As you will see with the Expense Agent, the WriteToLog function is used heavily to insert status messages for the agent in the log. Here is the code for the WriteToLog function:

 Private Sub WriteToLog(boolRecordName,strMessage) Dim strResponse strResponse = Now & vbTab & strMessage & ":" if boolRecordName = 1 then strResponse = strResponse & " " & msgTarget.Subject else strResponse = strResponse & " " end if Script.Response = Script.Response & vbNewLine & strResponse End Sub 

Folder_OnMessageCreated Function

The Folder_OnMessageCreated function is called when a new expense report is placed in the folder. When this function is called, it checks the expense total of the new expense reports in the folder by using the Fields collection on the item and then looking up the Total field.

If the expense total is greater than a certain amount, the script looks up the manager of the user issuing the report by using the CDO AddressEntry object. The script sends this manager a message containing a hyperlink to the current expense report. Then the script updates the message's status fields to reflect that the report has been routed to a new person. Finally, the agent e-mails a status update to the user and indicates to whom the report was routed.

If the expense total is less than $5,000, the agent automatically approves the expense report and updates its status. Although the application does not perform any tasks beyond sending an e-mail and updating the status, you could, in your agent, change this function to send an e-mail to the accounting department or update a database to transfer the funds into the user's expense account. The Folder_OnMessageCreated code is shown here:

 'DESCRIPTION: This event is fired when a new message is added to 'the folder Public Sub Folder_OnMessageCreated On Error Resume Next GetEventDetails If Err.Number = 0 Then WriteToLog 1,"Get Events Succeeded" WriteToLog 1,"Message Created: Checking Total. . ." CheckTotal Else WriteToLog 0,"GetEventDetails Failed" End If End Sub 'DESCRIPTION: Check the total of the expense report, and if it is 'less than a specific amount, automatically approve the expense 'report Private Sub CheckTotal Dim msgResponse Dim iMsgCount Dim msgManager Dim UsersManager Dim currentuser Dim currentapprover On Error Resume Next iMsgCount = fldrTarget.Messages.Count If Err.Number = 0 Then WriteToLog 1,"Message Count Succeeded" set ExpTotal = msgTarget.Fields.Item("Total") Set msgResponse = fldrOutbox.Messages.Add 'Modify this line to change Expense Amount If ExpTotal > 5000 then WriteToLog 1,"Greater than max expense amount" msgResponse.Subject = "The Total was " & ExpTotal 'Message to Manager set msgManager = fldrOutbox.Messages.Add set currentuser = msgTarget.Sender set UsersManager = currentuser.Manager currentapprover = UsersManager.Name msgResponse.Text = "This Expense Report has been " & _ "routed to your Manager: " & currentapprover 'Get the spaces out currentapprover = Replace(currentapprover," ","+") msgManager.Subject = "Approval Required for " & _ "Expense Report!" msgManager.Text = currentuser.name & _ " has submitted an expense report for " & ExpTotal & _ ". Please review it at http://localhost/expense/" & _ "approve.asp?entryid=" & msgTarget.ID & "&Approver=" & _ CurrentApprover msgManager.Recipients.Add "","",1,UsersManager.ID msgManager.Recipients.Resolve(False) msgManager.Send msgTarget.Fields("Status") = _ "Awaiting Approval from " & UsersManager.Name msgTarget.Fields("StatusInt") = 2 msgTarget.Fields.Add "Approver",8,UsersManager.Name msgTarget.Update Else 'Expense Report <= Max Amount WriteToLog 1,"Less than max expense amount" msgResponse.Subject = _ "This Expense Report has been Approved" msgResponse.Text = "Your expense report for " & _ ExpTotal & " has been automatically approved. " & _ "Funds are being transferred!" msgTarget.Fields("Status") = _ "Approved automatically and routed for payment" msgTarget.Fields("StatusInt") = 3 msgTarget.Update End If If Err.Number = 0 Then msgResponse.Recipients.Add "", "", 1, _ msgTarget.Sender.ID If Err.Number = 0 Then msgResponse.Recipients.Resolve(False) If msgResponse.Recipients.Resolved = True Then msgResponse.Send If Not Err.Number = 0 Then WriteToLog 0,"Message.Send Failed: " & _ Err.Description End If Else WriteToLog 0,"Recipients.Resolve Failed: " & _ Err.Description End If Else WriteToLog 0,"Recipients.Add Failed: " & _ Err.Description End If Else WriteToLog 0,"Messages.Add Failed: " & _ Err.Description End If Else WriteToLog 0,"Messages.Count Failed: " & Err.Description End If End Sub 

Folder_OnTimer Function

After 15 minutes, the Folder_OnTimer function is called by the agent to check the status of folder items. If any have the value 2, which indicates that the item is waiting for approval, the script checks the time the item was sent into the folder (as opposed to the current time) by using the VBScript DateDiff function. The DateDiff function returns the difference between the two dates in numbers of seconds. Once this value is returned, the script checks to see whether it is greater than 400 seconds. (I picked an arbitrary number which is less than 900 seconds, or 15 minutes. In a completed application, you will probably want to give managers more than 15 minutes to approve expense reports before escalating them.)

If the report has been sitting for more than the specified interval, the script looks up the manager of the current approver by using the AddressEntry object in CDO. If this manager has no manager above her, the script sends a friendly reminder to the current approver explaining that an expense report is awaiting approval. The script also informs the user that there are no other managers to route the report to.

If there is a manager above the current approver, the script forwards the report to this manager and informs the user and the current approver that the report has been forwarded to a new manager. The script then updates the report status to reflect the change in state.

Notice in the following code that the script does not try to retrieve the EventDetails.MessageID variable because the variable does not exist for timer events. You will receive an error if you attempt to retrieve this variable in your implementation for a timer event.

 'DESCRIPTION: This event is fired when the timer on the folder 'expires Public Sub Folder_OnTimer Dim oMessages Dim oMessage Dim Status Dim currentdate Dim elapsed Dim timesent Dim CurrentApprover Dim NextApprover Dim msgResponse Dim objonerecip Dim myaddentry Dim currentuser Dim msgNewApprover Dim DestFolder Dim idTargetFolder Dim oStores Dim Temp Dim i Dim StatusInt 'Since timer events do not return a specific message, all the calls to 'WriteToLog must not try to record the message name unless 'the variable msgTarget is explicitly set On Error Resume Next WriteToLog 0,"Timer Event Fired." 'Set variables using event details idTargetFolder = EventDetails.FolderID 'Clear errors Err.Clear Set AMSession = EventDetails.Session fldrOutbox = AMSession.Outbox If Err.Number = 0 Then Set oStores = AMSession.InfoStores If Err.Number = 0 Then Set Temp = oStores.Item(1).RootFolder Set Temp = oStores.Item(2).RootFolder Set fldrTarget = AMSession.GetFolder( idTargetFolder, _ Null ) end if end if 'Need to check all the messages in the folder to see if they 'are over the 15-minute limit and are awaiting approval set oMessages = fldrTarget.Messages WriteToLog 0,"There are " & oMessages.Count & _ " messages in the folder." for i = 1 to oMessages.Count 'Retrieve the message set oMessage = oMessages.Item(i) 'Check the time and status StatusInt = oMessage.Fields("StatusInt") if StatusInt = 2 then 'Got a live one 'Figure out how long it has been sitting timesent = oMessage.TimeSent currentdate = now() elapsed = datediff("s", timesent,currentdate) if elapsed > 400 then 'been sitting for a while 'Set another variable to the current message set msgTarget = oMessage WriteToLog 1,"Rerouting beginning" set ExpTotal = oMessage.Fields("Total") 'Reroute the message set CurrentApprover = oMessage.Fields("Approver") set msgResponse = AMSession.Outbox.Messages.Add 'Create the recipient Set objonerecip = msgResponse.Recipients.Add objonerecip.Name = CurrentApprover 'Resolve the name against the Exchange directory objonerecip.Resolve 'Get the address entry so we can pull out 'template information Set myaddentry = objonerecip.AddressEntry 'Get the manager from the address entry set NextApprover = myaddentry.Manager if NextApprover = Empty then 'We don't have a manager! 'Send a message to the current user set currentuser = oMessage.Sender msgResponse.Subject = _ "No more manager to route to" msgResponse.Text = currentuser.name & _ " has submitted an expense report for " & _ ExpTotal & _ ". There are no other managers to route to!" msgResponse.Recipients.Add "", "", 1, _ oMessage.Sender.ID msgResponse.Send 'Resend a message to the current approver Set msgResendtoApprover = _ AMSession.Outbox.Messages.Add CurrentApproverName = Replace( _ CurrentApprover," ","+") msgResendtoApprover.Subject = _ "Repeat notice for Approval of an " & _ "Expense Report!" 'Change the following location to be 'your Web location msgResendtoApprover.Text = currentuser.name & " has submitted an " & _ "expense report for " & ExpTotal & _ ". Please review it at http://localhost/" & _ "expense/approve.asp?entryid=" & msgTarget.ID & _ "&Approver=" & CurrentApproverName 'Create the recipient set oRecip = msgResendtoApprover.Recipients.Add oRecip.Name = CurrentApprover oRecip.Resolve msgResendtoApprover.Send WriteToLog 1,"No More Managers beyond " & _ CurrentApprover & " for this user." else NextApproverName = NextApprover.Name 'Got the next approver. Send a message to 'previous approver and user and reroute. set currentuser = oMessage.Sender msgResponse.Subject = "An Expense Report" & _ " has been rerouted" msgResponse.Text = currentuser.name & _ " has submitted an expense report for " & _ ExpTotal & ". It was rerouted because the " & _ "15 minute approval time limit has expired. " & _ " It is now routed to " & NextApproverName msgResponse.Recipients.Add "", "", 1, _ oMessage.Sender.ID msgResponse.Send if err.number = 0 then WriteToLog 1,"Successfully rerouted" end if 'Now change the status and reroute to 'new person oMessage.Fields("Status") = _ "Rerouted and awaiting Approval from " & _ NextApproverName oMessage.Fields("Approver") = NextApproverName oMessage.Update 'Now send a message Set msgNewApprover = _ AMSession.Outbox.Messages.Add 'Create the recipient. 'Get the spaces out. NextApproverName = Replace( _ NextApproverName," ","+") msgNewApprover.Subject = _ "Approval Required for Rerouted Expense Report!" msgNewApprover.Text = currentuser.name & _ " has submitted an expense report for " & _ ExpTotal & ". Please review it at http://" & _ localhost/expense/approve.asp?entryid=" & _ msgTarget.ID & "&Approver=" & NextApproverName msgNewApprover.Recipients.Add "","",1, _ NextApprover.ID msgNewApprover.Recipients.Resolve(False) msgNewApprover.Send end if 'Manager! end if 'Elapsed end if 'Status next WriteToLog 0,"Timer Event Ended" End Sub 

CDO Code in the Application

The Expense Report application contains sections of CDO code that show how to use CDO objects not discussed in detail in Chapter 12, which covers CDO development. The most interesting section of the code is found in the file Logon.asp. The code in this script uses custom properties in a MessageFilter object to filter out all expense reports that have the current user as the current approver, as well as filter out only those expense reports with a status of 2, which means that the expense report is waiting for approval. As you can see in the following code, to filter custom properties on an item, you must use the Add method of the Fields collection for the MessageFilter object. In the Add method, you need to specify the name of the custom property; the type, by using a Long constant; and the value that the property should use for the filter. Once you set these properties, the messages collection will contain only those items that meet your specified criteria.

 <% 'Check to see whether any reports are waiting for this approver set oMessages = objFolder.Messages set oMsgFilter = oMessages.Filter set oApprover = oMsgFilter.Fields.Add( _ "Approver",8,AMSession.CurrentUser.Name) set oStatus = oMsgFilter.Fields.Add("StatusInt",8,"2") iMsgCount = oMessages.Count if iMsgCount > 0 then response.write "<P>There are <B>" & iMsgCount & _ "</B> Expense Reports awaiting your approval." end if %>