As I'm fond of mentioning, CGI applications are the ultimate gateway between a Web server and any other system to which the Web server machine has access. This point cannot be overstated to the programmer who is trying to hang on to legacy data or existing systems, yet wishes to integrate the Web into the IS environment. This chapter serves as one more basic example of connecting an existing system, Microsoft's Schedule Plus, to the Web.
The application in this chapter requires that Microsoft Schedule Plus Version 7 be installed on the machine running the Web server. Also, a valid Schedule Plus schedule must be accessible, whether it is for a Microsoft Exchange Server mailbox or simply a schedule file residing on the Web server machine. The example provided in this chapter assumes you have Microsoft Exchange Server installed. It uses an Exchange mailbox as the resource for the Schedule Plus schedule it accesses. The code can be modified, however, to open a local schedule file.
The purpose of Schedule Plus, as you can probably guess, is to manage a schedule or a group of schedules. In the typical LAN environment, an e-mail system such as Microsoft Exchange is already in place. Adding Microsoft Schedule Plus enables the enterprise to extend its e-mail system into the realm of time and contact management. The example presented in this chapter demonstrates the setting of a doctor's appointment using a CGI application. The CGI application connects an HTML form to a Schedule Plus database. The application examines a predetermined Schedule Plus schedule for available appointment times on a date that the Web page user enters. The user is then able to pick one of the available times and book an appointment. The CGI application then creates the new appointment on the Schedule Plus database. Now, anyone in the office that has access to that schedule by way of the local LAN can see that an appointment has been created, who created it, and the reason for the appointment.
Schedule Plus (hereafter called "SPlus") operates along side Microsoft Exchange Server and uses the same mailboxes and logins as Exchange. Although it has just recently been released, the two applications developed in this chapter work only with the released versions of Exchange Server and SPlus version 7. The reason for this is quite simple: SPlus version 7 can operate as an OLE automation server. Previous versions of SPlus require calls to the SPlus DLL and are cumbersome, at best, to program. Also, version 7 introduces a small contact-management module into the product. The example in this chapter assumes all the patients have been entered into the Contacts table. This enables the application to work off a single key field, which the user enters (the Patient ID), and to set up the appointment with information taken from the patient's contact record. This saves users from having to enter personal information every time they wish to make an appointment.
The SPlus OLE interface, documented in Microsoft's BackOffice Software Developer's Kit (SDK), is slick and easy to use. It provides access to all the details within an SPlus database, including the appointments, contacts, tasks, and projects. There is a small section in this chapter, "The Basics of the SPlus OLE Model," which briefly describes the object model for SPlus. Microsoft has published several fair pieces on controlling the SPlus OLE automation server with VB. These are listed in the bibliography (Appendix F).
The appointment setting site and accompanying applications are very basic. The main purpose of this chapter is to illustrate how to connect a Web site to an OLE automation server that is accessible by the server machine. The schedule that is used in this chapter's examples is for a resource. A resource is a special type of SPlus schedule that represents any entity shared among the users of the system. Resources can include conference rooms, audio-visual equipment, computers, and any other item (or even a person such as an audio/visual technician) that can be shared. Resources can be invited to meetings and have appointments scheduled for them, which is how the applications in this chapter operate.
The resource used in this chapter is named "Exam Room A" and represents an examination room in a doctor's office. In this simple example, the doctor has a small practice with only one examination room. The application could easily be expanded to handle multiple examination rooms if necessary. The applications use the exam room schedule instead of the doctor's personal schedule because the exam room is the actual resource being reserved. For example, the doctor may be out sick that day and another doctor may be covering the patients.
When patients wish to set up an appointment, they point their Web browser to the Appointment Request page shown in Figure 9.1. The user then enters the desired date, their Patient ID, and the reason they are requesting the appointment. When the Submit button is clicked, the first of the Win/CGI applications developed in this chapter is implemented.
Figure 9.1. The Appointment Request page.
This application, checkscd.exe, first verifies the date entered. The date must be a valid date, must not be before the current date, and must not fall on a Saturday or Sunday. The other fields are checked to make sure something has been entered. The application then opens the schedule for Exam Room A and verifies that the supplied Patient ID identifies an existing contact. The Contacts table in SPlus has a number of fields labeled Userx. The application uses User1 to store the Patient ID data.
If a valid Patient ID has been entered, the application next checks the Free/Busy indicators for the date requested, and, if there are any available times on the day selected, it outputs an HTML form that has a radio button for each available time slot on the date requested. All appointments are 30 minutes long and begin on the half-hour. If there are no available times, the HTML page returned notifies the user of that fact. An example HTML output page is shown in Figure 9.2.
Figure 9.2. The Available Times page.
The form has a button labeled Book Appointment, which the user clicks after selecting an appointment time. This launches the CGI application named makeappt.exe. This application creates the new appointment on Exam Room A's schedule and outputs a verification page to the user, confirming the appointment as well as the user's phone number. The phone number is retrieved from the SPlus Contacts table.
At this point, a new appointment has been entered into the SPlus database for Exam Room A. Anyone in the doctor's office who can connect to the Exchange Server can open Exam Room A's schedule and see the appointments that have been booked. The makeappt application places the patient's name and the reason for the appointment in the SPlus appointment description. A sentence indicating that the appointment was booked using the Web is placed in the SPlus appointment's Notes field. The appointment record is linked to the contact record using the SPlus internal ItemID for the patient's contact record.
Anyone using the SPlus front end can then print a daily, weekly, or monthly view for the exam room. The daily printout can be placed at the receptionist's desk so patients can be checked off as they show up for their appointments. The doctor could use the weekly printout to schedule his next golf outing. The monthly printout could be used to see if additional staff needs to be brought on for the month in the case of a run on the office. The possibilities are endless!
The first step in this chapter's journey is to create the schedule for Exam Room A. As mentioned in the introduction, SPlus schedules that are used in group scheduling settings are always tied to an Exchange mailbox. This section covers creating the mailbox and setting the schedule for the mailbox to represent a resource.
Create the Exam Room A mailbox by using the following steps:
Figure 9.3. The Microsoft Exchange Administrator.
Figure 9.4. The Exam Room A Exchange mailbox property sheet.
Now that the mailbox has been created, it's time to open it with SPlus. In SPlus, you'll specify that the schedule is for a resource, set some options, add some dummy appointments, and create a few patients in the SPlus Contacts table.
I use the Windows 95 Exchange client applications so these instructions are specific to Windows 95. The NT Exchange clients may require different steps to accomplish the same tasks.
The first step is to create an Exchange profile that opens the Exam Room A mailbox as follows:
Figure 9.5. The Inbox Setup Wizard.
The next step is to open the Exam Room A schedule in SPlus. An intermediate step may be necessary, however. Your Exchange settings must enable you to select a profile to be used when you log on using an Exchange Server client application. If you don't see the Choose Profile dialog (Figure 9.6) when you start Exchange or SPlus, you need to follow these steps to enable this feature:
Figure 9.6. The Exchange Choose Profile dialog.
Now that you can select the profile that will be used, continue with these steps:
Figure 9.7. SPlus with Exam Room A loaded.
Figure 9.8. The SPlus Options dialog for Exam Room A.
Now that the Exam Room A resource has been created and loaded with some data, you can move on to the next section. It provides a very basic discussion of SPlus OLE automation programming. If you are already familiar with controlling OLE automation servers in general or SPlus in particular, feel free to skip the next section and proceed to the section titled "Designing the Input Forms."
First the bad newsthe SPlus OLE automation object model is by far the most complicated OLE model I've seen. Now, for the good newsto effectively control the SPlus OLE automation server from Visual Basic you only need to learn a few basic concepts.
SPlus uses a concept known as object overloading to expose most of the data contained in a schedule. This means that one class is used for many named objects. The SPlus type library exposes only five objects: Application, Schedule, Table, Item, and Property. The objects are hierarchical in nature, with the Application object being topmost and the Property object at the bottom. In this section I'll take you through to the Item level. A serious discussion of using the Property object is beyond the scope and needs of this chapter. See the Bibliography in Appendix D for further resources covering the Property object.
The Application object is the only one that can be created at run time. All the other objects are derived from the Application object. Although I've seen the code to create a SPlus application object written many ways, the only code I've been able to make work is
Dim objSPlusApplication as Object Set SPlusApplication = CreateObject("SchedulePlus.Application.7")
so that's the code I've used in this chapter's examples.
The Application object has many useful properties and three very useful methods. The more interesting and useful properties are presented in Table 9.1. The only property of the Application object used in this chapter is ScheduleLogged.
Property |
Data Type |
Description |
ScheduleLogged
|
Object
|
Returns a schedule object for the currently logged in mailbox.
|
IsMailEnabled
|
Long
|
Boolean that indicates whether mail is enabled.
|
UserName
|
String
|
The display name of the logged in mailbox.
|
UserAddress
|
String
|
The e-mail address for the logged in mailbox.
|
LoggedOn
|
Long
|
Boolean that indicates whether or not the Application object is logged on to the mail system. |
The three methods are Logon, ScheduleForUser, and ScheduleForFile. The examples in this chapter use the Logon method, but not the other two. The Logon method is used to log on to the mail system. The method is defined by
object.Logon [profile name], [profile password], [show dialog], [parent window]
where profile name is the name of an Exchange profile to be used, such as Exam Room A, for the profile created when the Exam Room A resource was created. The profile password is the password for the named profile. The show dialog flag is used to instruct SPlus to display the Choose Profile dialog. The parent window parameter specifies the handle to the window that will be the parent window for the dialog box, if displayed.
Two examples should clarify the Logon method. The first,
objSPlusApplication.Logon "", "", 1
causes the SPlus OLE automation server to display the Choose Profile dialog. The method does not return until the user either selects a profile to use or clicks the dialog's Cancel button. The Cancel button causes a trappable run-time error to occur.
The second example,
objSPlusApplication.Logon "Exam Room A"
is the code used in the applications in this chapter. Because the profile was created on the Web server machine, there is no problem with hard-coding the profile name. Also, because the applications run as Win/CGI applications, using the first example would be futile because there would likely be no one seated at the machine to select the proper Exchange profile. The application would be stuck on that line of code, probably until the server was rebooted.
The other two methods, ScheduleForUser and ScheduleForFile are used to open other schedules. The ScheduleForUser method is incorrectly documented in the most recent version of the SPlus Programmer's Reference. The only way I have been able to open another user's schedule with this method is
Set objSched = objSPlusApplication.ScheduleForUser(tEMailAddress$, "", 1, 1)
where the tEMailAddress$ is the fully-qualified Exchange e-mail address for the mailbox of interest and the 1 1 has something to do with the mode in which the schedule is to be opened. Unfortunately, I am unable to explain why this code works, but it does for now!
The Schedule object provides a gateway to all the Table objects that actually contain the schedule's information. The Schedule object also provides properties to access the user options for the schedule, such as Start of Day and End of Day. Table 9.2 lists the more interesting properties of the Schedule object.
Property |
Data Type |
Description |
DayEndsAt
|
Long
|
Number of half hours from midnight until the end of the day.
|
DayStartsAt
|
Long
|
Number of half hours from midnight until the start of the day.
|
DayOfWeekStart
|
Long
|
Indicates the day the week starts on.
|
UserName
|
String
|
The display name of the logged in mailbox.
|
UserAddress
|
String
|
The e-mail address for the logged in mailbox.
|
DisallowAppointmentOverlap
|
Long
|
Boolean value that indicates whether or not to allow overlapping appointments to be scheduled, used mostly for resources.
|
DisallowRecurringItems
|
Long
|
Boolean value that indicates whether or not to allow recurring items to be scheduled. |
All the Table objects listed in the following section are accessed using the Schedule object. For example,
set objContacts = objCurrSched.Contacts
sets the object objContacts to the Contacts table contained within the schedule represented by objCurrSched.
There are two methods that are worth mentioning here. The first, Activate is used to activate the SPlus application and load the schedule referenced by the Schedule object just as though you had launched SPlus from Windows 95. This method isn't used in this chapter, but I use it a lot when debugging SPlus controllers.
The second method, FreeBusy, is one of the keys to making the applications in this chapter work. It takes a date as one of its parameters and returns the free/busy information for the month that the supplied date falls in. The return value is a string of zeros and ones, where a zero means that there are no appointments scheduled during that interval, and a one means that there are. The second parameter is optional. If specified, it sets the interval in minutes that each zero or one represents. If not specified, the interval defaults to 30 minutes. The return string contains free/busy information for each interval from midnight (AM) on the first of the month, up until midnight (PM) on the last day of the month.
The Table object is the first of the overloaded objects. Therefore, you probably will never use the word "Table" anywhere in your SPlus code. Instead, you will refer to one of the many data tables within the Schedule object (Appointments, Contacts, Tasks, Events, and so forth).
Each Table object has the same properties and methods, which are listed in Table 9.3 and Table 9.4.
Property |
Data Type |
Description |
IsEndOfTable
|
Long
|
Indicates whether the cursor position is at the end of the table.
|
Position
|
Long
|
The current position of the cursor in the table.
|
Rows
|
Long
|
The number of items currently in the table. |
Methods |
Description |
New
|
Creates a new object in the table.
|
Item
|
Returns an Item object for the table.
|
DeleteItem
|
Deletes an Item object in the table.
|
Skip
|
Skips a specified number of rows forward or back in the table.
|
Reset
|
Sets the current item to the first item in the table.
|
SetRange
|
Restricts the table to a specified date range.
|
SetRestriction
|
Sets a simple restriction on a simple table. |
You operate on a Table object in a manner similar to using the Data Access Objects. Think of the table as a linked list of items (which is what it really is). When a table is first opened, the record pointer (if you will) is pointing at the first item in the list. By using the Skip method, you move the record pointer to the next item in the list. This continues until you reach the last item in the list. At this time, the IsEndOfTable property for the table is set to True.
To access a particular property of the current item, you must use the Item method. For example,
Debug.Print objContacts.Item.FirstName
prints the first name of the current contact (if there is a current contact) to the Debug window. The Item method also accepts a string parameter that specifies the ItemID of an item in the referenced table. This is useful for pinpointing an item in a table if you know its ItemID.
The New method creates a new item in the table, and the DeleteItem method deletes either the current item or the item matching the method's ItemID parameter if specified. The Reset method moves the record pointer to the first item in the table, and the Skip method skips to the next record. You can also skip over multiple records or skip backward by specifying a number as a parameter to the Skip method.
Finally, the SetRange method is used to limit the records returned by the Table object to the ones that fall within the date range specified. This is useful for Appointments, for example. The more powerful SetRestriction method enables you to restrict the items available to those matching the criteria specified by the method's parameters. This method is used in the applications in this chapter to pinpoint the Contacts table item for the patient who is setting the appointment.
The Item object is used to access the properties of a given Table object. The Table object may be an appointment, task, alarm, attendee, event, project, or contact. Each Table object's Item object has its own specific set of properties, but any property can be set on any item. SPlus simply ignores you when you attempt to set a property that is not valid for the Item object referenced. For example, you can set the FirstName property on an Appointment item, but Splus will simply ignore you.
The only property or method that I'll discuss here is the Flush method. This method is invoked whenever you want to save changes you've made to an Item's properties or when you have created a new item by using the Table object's New method. The makeappt application demonstrates the use of New and Flush when it adds the patient's appointment to the SPlus Appointments table.
I realize that the material I've covered may seem either overwhelming or may be completely inadequate. This section has only covered the basics of the SPlus OLE model. A more complete accounting can be found in the Schedule Plus Programmer's Guide and Reference available on the Microsoft Developer's Network CD or through the BackOffice SDK, also available from Microsoft. I have tried to cover, though, the solutions to all the problems I encountered in attempting to control the SPlus OLE automation server.
The method I finally resorted to when learning the programming model was to declare a bunch of global object variables, start an empty application, break execution, and then write code in the Debug window. I hope the preceding information, the examples to follow, and the reference material referred to in the Bibliography provides you with enough information to effectively utilize the SPlus OLE automation server. If all else fails, simply copy the code I've provided and use it anywhere you need to.
The Web site for this chapter consists of three HTML pages. The initial page (the Appointment Request page) is a static page that you should create in a suitable document directory on your Web server. The other two pages are created by the two CGI applications developed in this chapter. This section presents the full HTML of the Appointment Request page and simply discusses the other two pages. The HTML for the other two pages is generated by the applications developed in the next section.
The Appointment Request page (see Listing 9.1) is a simple HTML form page. The user enters the date the preferred appointment, the Patient ID, and the reason for requesting an appointment. The names for the fields are Date, PatientID, and Reason, respectively. The first two fields are single-line textboxes. The reason field is a multiline textbox in case the user wishes to provide details of the problem. Because the Patient ID is the patient's Social Security number, the default text for that field is "###-##-####" to help remind the user to enter an SSN.
The FORM tag defines how the form operates when its Submit button is clicked. For the Appointment Request form, the browser issues a POST request to a resource whose URI is /cgi-win/checkscd.exe. The POST request message passes the data entered on the form to the first application developed in this chapter (checkscd). The data is passed using the three CGI parameters (Date, PatientID, and Reason) defined within the form. The form also has a Reset button, which returns the fields to their default state.
Listing 9.1. The Appointment Request page.
<html><head><title>Appointment Request</title></head> <body> <h1 align=left>Appointment Request Form</h1> <blockquote> <h2 align=left>Use this form to request an appointment<br> (all fields are required!)</h2> </blockquote> <form action="/cgi-win/checkscd.exe" method="POST"> <blockquote> <p> Enter the desired appointment date:<br> <input type=text size=20 maxlength=256 name="Date"> </p> </blockquote> <blockquote> <p>Enter your Patient ID (Social Security #):<br> <input type=text size=20 maxlength=256 name="PatientID" value="###-##-####"></p> </blockquote> <blockquote> <p>Enter the reason for your visit: <br> <textarea name="Reason" rows=3 cols=30></textarea></p> </blockquote> <blockquote> <p><input type=submit value="Submit"> <input type=reset value="Reset"></p> </blockquote> </form> </body></html>
The HTML form produced by the checkscd application is the Available Times page. It contains a radio button for each available appointment slot, a Submit button (labeled Book Appointment), and a Reset button. The HTML definition for a radio button is
<input type=radio name=group_name value=individual_value [checked] >
The type element defines the input as a radio button. The name element defines the group to which the radio button belongs. This element must be the same for each radio button in the group. The value element defines the value that will be passed to the server by the Web browser application if this particular radio button is selected when the Submit button is clicked. The optional checked element specifies that this particular radio button should be selected by default. Note that the text that appears to the right of the radio button is not specified within the <INPUT> tag. Instead, you can place any valid HTML elements after the radio button. The Web browser renders the radio button similar to a VB radio button that has an empty string as its Caption property.
The Available Times form also has two hidden fields. These fields contain the Patient ID field and the Reason field that the user entered on the Appointment Request form. These fields are needed to complete the appointment setup and must be passed to the next application when the form's Book Appointment button is clicked. The form's action specifies a POST request to a resource located at /cgi-win/makeappt.exe. This is the makeappt application developed later in the chapter.
The final page is generated by makeappt. It provides a verification notice to the user that the appointment has indeed been created. It also informs the user that someone from the office will call 24 hours in advance of the appointment to follow up. The patient's phone number is also listed so the user can verify that it is correct.
This page is a simple HTML output page. No form is present and nothing fancy takes place.
All the HTML for these last two pages is generated by the two applications that are discussed in the next section. This section was intended to explain the design behind the HTML generated by the applications.
Two applications are used in the Web site described at the beginning of this chapter. The first, checkscd, takes the data the user has entered on the Appointment Request form and creates a page with some header information and an HTML form. If valid data has been entered on the Appointment Request form, the HTML form contains a radio button for each appointment time available on the date the user entered.
Clicking the Book Appointment button on this form causes the other application for this chapter, makeappt, to execute. This application takes the data entered on the previous two forms and creates an appointment on Exam Room A's SPlus schedule. The application then creates a confirmation page to display to the user.
This section discusses the code for these two applications. As you are aware if you've read the other chapters discussing Win/CGI applications, both projects contain a module named CGI32.BAS. This module is where the functions, user-defined data types, and global variables that I use in CGI applications are stored. The module FUNCS.BAS is also required in the project because CGI32.BAS calls some of its procedures. The code for these two modules is in Chapter 7, "Creating CGI Applications in Visual Basic." It is also available in the same directory on the CD-ROM as the code for this chapter.
The two applications have a function named IsTimeAvailable() in common. Rather than creating a separate module just to store this function, I simply copied the code for it into the main module for both projects. The code for IsTimeAvailable() is discussed in the following section.
To create the checkscd application, start a new Visual Basic project. Add the CGI32.BAS and FUNCS.BAS modules to the project. Insert a new module and save it as CHECKSCD.BAS. This module will contain two procedures: Main and IsTimeAvailable(). The code for both procedures appears in full in Listing 9.2, which is at the end of this section. You must also add a reference to the SPlus object library using Visual Basic's Tools | References menu. On the References dialog, click the Browse button and locate the file SP7EN32.OLB. It is most likely located in the directory where SPlus has been installed.
The IsTimeAvailable function, defined by this line
Public Function IsTimeAvailable(ptFreeBusy As String, _ pvStart As Variant, pvEnd As Variant) As Integer
takes a string and two date/times as parameters and returns a Boolean result. The string parameter is a free/busy string which should be obtained by using the SPlus Schedule object's FreeBusy method. The date/times specify the start and end of the time period of interest.
The function examines the substring of the free/busy string that corresponds to the time period of interest. If that substring contains at least one "1", the function returns False because this indicates that the schedule is busy at some point during the interval. The schedule must be free for the entire interval in order for this function to return True.
The function starts by first checking to see if there are any ones at all in the string. If there aren't, then the schedule is free for the entire month represented by the free/busy string, and the function returns True without any further processing required.
If there is at least one "1" in the string, the function must determine which characters in the string represent the free/busy information for the time period provided by the two date/time parameters. The first step is to determine the number of intervals that have occurred between midnight (AM) on the first day of the month and the times specified for the pvStart and the pvEnd parameters. The function then gets the substring from the string parameter that represents this interval. If there are any ones in this substring, the schedule is considered busy for the time period specified by the pvStart and the pvEnd parameters, and the function returns False. Otherwise, the function returns True.
As in the other Win/CGI applications presented in this book, the Main procedure is where all the execution takes place. When the procedure ends, so does the CGI application.
The first section of code contains the typical Win/CGI startup code. This code has been discussed in previous chapters (Chapter 7 and Chapter 8, "Database Connectivity: The WebGuest Application") and won't be rehashed here.
After the startup code, the application validates the data entered on the Appointment Request form. The following validation rules are applied:
If the data fails any of the validation tests, the application returns a page to the user indicating the error.
After the basic validation has been passed, the application moves on to some SPlus code. The first step is to dimension object variables for each SPlus object that will be accessed. This application uses the Application object (objSplusApp), a Schedule object (objCurrScd), and the Contacts Table object (objContacts).
Next, the Application object is created with this line:
Set objSPlusApp = CreateObject("SchedulePlus.Application.7")
The code assumes that the shell the application is currently running under does not have a logged on mail session, so the LoggedIn property is not checked. Instead, a login to SPlus is accomplished using
objSPlusApp.Logon "Exam Room A"
This line uses the Exam Room A profile to log on to the SPlus and Exchange systems. After the successful logon, a reference to Exam Room A's schedule is created by executing
Set objCurrScd = objSPlusApp.ScheduleLogged
which returns the schedule for the logged in mailbox. Next,
Set objContacts = objCurrScd.Contacts
grabs a reference to the Contacts table for the schedule. The Contacts table is where all the office's patients are stored. The Contacts table is used to validate the Patient ID entered on the Appointment Request form and to provide some user-friendly text (in this application, the patient's name).
The Patient ID is stored as a Social Security number and is, therefore, formatted as "###-##-####". The form field is retrieved from the CGI data, formatted as a SSN, and stored in a local variable by executing
ltFormattedID$ = Format$(GetFieldValue("PatientID"), "###-##-####")
The next step is to validate that the ID entered exists in the Contacts table. The Patient ID is stored in the Contacts table's User1 field. The SetRestriction method of the objContacts Table object is invoked to restrict the table to only those records that have a User1 field equal to the formatted Patient ID with this line:
objContacts.SetRestriction "User1", ltFormattedID$, "=="
The application assumes that a given Patient ID/SSN is used only once in the Contacts table. If the value of the objContacts.IsEndOftable property is True, then the Contacts table does not contain any records having the Patient ID entered on the Appointment Request form. In this case, the application outputs an HTML page to the user informing him or her of the fact that the ID entered is invalid. The program then logs off the SPlus Application object (using the Logoff method) and sets the object to Nothing.
Whenever you close an application that has logged on using an SPlus Application object, you must perform the Logoff and set the Application object to Nothing or you will leave the SPlus OLE automation server running. When this has happened, I have been unable to perform a Logon method without first manually shutting down the SPlus server.
After the Patient ID is validated, the application retrieves the patient's name using the Item method and the FirstName and LastName properties of the Contacts table:
ltFirstName$ = objContacts.Item.FirstName ltLastName$ = objContacts.Item.LastName
Recall that the SetRestriction method has placed the Contacts table record pointer at the record for the contact whose User1 field equals the Patient ID entered on the Appointment Request form.
Now that the patient information is out of the way, the application concentrates on determining which appointment times are available on the date requested. The start and end times of the office's hours are retrieved from the Schedule object with
liDayStart = objCurrScd.DayStartsAt liDayEnd = objCurrScd.DayEndsAt
The DayStartsAt and DayEndsAt properties return an integer representing the number of half hours that occur between midnight (AM) and the start and end times specified in the schedule's preferences. These integers are converted to actual time values using
lvStartTime = TimeValue(DateAdd("n", liDayStart * 30, "12:00:00 AM")) lvEndTime = TimeValue(DateAdd("n", liDayEnd * 30, "12:00:00 AM"))
Next, the FreeBusy method is invoked to obtain the free/busy string for the month in which the requested date falls:
ltFreeBusy$ = objCurrScd.FreeBusy(ltDate$)
And, finally, the SPlus object is logged off and closed down using
objSPlusApp.Logoff Set objSPlusApp = Nothing
The logoff is done before the actual end of the code in order to free the memory and resources used by the SPlus server for any other concurrent connections to use. Because the objects won't be used again by the application, there's no need to keep them around.
After the SPlus code, the application begins to build the HTML output page. The OutputString procedure is called several times to produce HTML that appears at the top of the page. Then, a variable named ltForm$ is created to store the HTML for the form, which contains the radio buttons for the available appointment times. The HTML is stored to a variable instead of being placed directly into the output file because of the chance that no times may be available on the date requested. In this case, the HTML form will not be created on the output page.
The code that creates the form starts with the form's <FORM> tag and specifies the HTTP method to be used (POST) as well as the action to be taken (/cgi-win/makeappt.exe). Then, several hidden fields are placed on the form. These fields contain the Patient ID and the text of the Reason field. This data is needed by makeappt in order to properly create the SPlus appointment for the patient.
A flag named liTimeAvailable% is used to keep track of whether or not any available times have been found. The code loops from the beginning to the end of office hours, checking each half-hour interval for an available time. If an interval is available, a radio button is created on the HTML form to represent that time period and the liTimeAvailable% flag is set to True. If this is the first available time that has been identified, the radio button's checked element is used to select the time by default on the form.
The form ends with the Submit button (labeled Book Appointment) and the Reset button.
If the liTimeAvailable% flag is set to True, the contents of ltForm$ are written to the output page. Otherwise, a message informing the user that no times are available is output.
The application ends by finishing off the HTML and closing the output file.
Take note, also, of the code in Listing 9.2 that occurs after the FormError label. As I stated earlier, you must close any logged-on SPlus Application objects. This is true even in an error condition, so the Logoff and Nothing code are copied here as well.
Listing 9.2. The checkscd module.
Public Sub Main() '================================ ' Typical Win/CGI startup code ' On Error GoTo FormError If InStr(Command$, " ") Then guCGIData.ProfileFile = Left$(Command$, InStr(Command$, " ") - 1) Else guCGIData.ProfileFile = Command$ End If If LoadCGIData() = 0 Then 'if we couldn't load all the CGI data, 'could we at least open the Output File? If guCGIData.OutputFileHandle <> 0 Then 'yes - call ErrorHandler to handle this Call ErrorHandler(-1, "Error loading CGI Data File") Else 'no - forget about anything else! End End If End If OutputString "Content-Type: text/html" OutputString "" ' ' End of Win/CGI startup code '================================ '================================ ' Validate form data ' 'validate the date entered on the form: ltDate$ = Trim$(GetFieldValue("Date")) If Not (IsDate(ltDate$)) Then 'an invalid date was entered OutputString "<h2>You entered an invalid date</h2>" OutputString "The date you entered was: " & ltDate$ OutputString "</body></html>" Close #guCGIData.OutputFileHandle End End If If DateValue(ltDate$) < DateValue(Now) Then 'the date was prior to today OutputString "<h2>You entered a date that has passed</h2>" OutputString "The date you entered was: " & ltDate$ OutputString "</body></html>" Close #guCGIData.OutputFileHandle End End If If WeekDay(ltDate$) = vbSunday Or WeekDay(ltDate$) = vbSaturday Then 'the date was for a Saturday or Sunday OutputString "<h2>You entered a date that is a Saturday or Sunday</h2>" OutputString "Our offices are opened Monday thru Friday.<p>" OutputString "The date you entered was: " & ltDate$ OutputString "</body></html>" Close #guCGIData.OutputFileHandle End End If 'make sure the user entered a reason If Len(Trim$(GetFieldValue("Reason"))) = 0 Then OutputString "<h2>You Forgot to Enter a Reason</h2>" OutputString "You must enter a reason for the appointment!" OutputString "</body></html>" Close #guCGIData.OutputFileHandle End End If 'make sure the user entered a patient ID If Len(Trim$(GetFieldValue("PatientID"))) = 0 Then OutputString "<h2>There is no Patient ID</h2>" OutputString "</body></html>" Close #guCGIData.OutputFileHandle End End If ' ' End of form data validation '=================================== '=================================== ' Schedule+ Code ' 'Dimension some objects Dim objSPlusApp As Object Dim objCurrScd As Object Dim objContacts As Object 'create the Schedule Plus (S+) object Set objSPlusApp = CreateObject("SchedulePlus.Application.7") 'log on using the "Exam Room A" Exchange profile ' this profile logs on to the Exam Room A mailbox & schedule objSPlusApp.Logon "Exam Room A" 'get the schedule for the logged-on user (Exam Room A) Set objCurrScd = objSPlusApp.ScheduleLogged 'validate the Patient ID entered 'Patients are stored in the schedule's Contacts table Set objContacts = objCurrScd.Contacts 'User1 is the Patient ID, set a restriction ltFormattedID$ = Format$(GetFieldValue("PatientID"), "###-##-####") objContacts.SetRestriction "User1", ltFormattedID$, "==" 'if we're at the end of the Contacts table, no such Patient ID If objContacts.IsEndOfTable Then OutputString "<h2>Your Patient ID Is Invalid!</h2>" OutputString "The ID you entered was: " & ltFormattedID$ OutputString "<p>Your Patient ID is your Social Security Number" OutputString "</body></html>" Close #guCGIData.OutputFileHandle 'close the S+ objects (VERY IMPORTANT!!!!) objSPlusApp.Logoff Set objSPlusApp = Nothing End End If 'get the patient's first name from the Contacts table: ltFirstName$ = objContacts.Item.FirstName ltLastName$ = objContacts.Item.LastName 'get the day starting and ending times for the schedule ' these values are the number of 1/2 hours between midnight ' and the Day Starts and Day Ends settings for the schedule liDayStart = objCurrScd.DayStartsAt liDayEnd = objCurrScd.DayEndsAt lvStartTime = TimeValue(DateAdd("n", liDayStart * 30, "12:00:00 AM")) lvEndTime = TimeValue(DateAdd("n", liDayEnd * 30, "12:00:00 AM")) 'get the FreeBusy string for the date entered ' NOTE: S+ returns a string for the ENTIRE month starting at day 1 ltFreeBusy$ = objCurrScd.FreeBusy(ltDate$) 'close the S+ objects (VERY IMPORTANT!!!!) objSPlusApp.Logoff Set objSPlusApp = Nothing ' ' End of Schedule+ Code '===================================== OutputString "<html><head><title>Available Times</title></head>" OutputString "<center><h1>Available Times for " & ltDate$ & "</h1>" OutputString "<h2>Hello " & ltFirstName$ & "! <br>" OutputString "Welcome to the Appointment Setter</h2></center>" OutputString "If you are not " & ltFirstName$ & " " & ltLastName$ OutputString " please return to the Appointment Request form and check " OutputString "the Patient ID you entered.<p>" OutputString "Our Office Hours are " & Format$(lvStartTime, "H:NN AM/PM") OutputString " to " & Format$(lvEndTime, "H:NN AM/PM") 'create a string to hold the form definition ' (if there are no available times for the chosen date, the ' form won't be output to the return page) ltForm$ = "<form method=""post"" action=""/cgi-win/makeappt.exe"">" 'hidden field to pass the Patient ID to makeappt ltForm$ = ltForm$ & "<input type=hidden name=""PatientID""" ltForm$ = ltForm$ & " value=""" & ltFormattedID$ & """>" 'hidden field to pass the reason to makeappt ltForm$ = ltForm$ & "<input type=hidden name=""Reason""" ltForm$ = ltForm$ & " value=""" & GetFieldValue("Reason") & """>" ltForm$ = ltForm$ & "<pre>Select a time:" & Chr$(13) liTimesAvailable% = False 'flag for existence of avail. times For i% = 1 To liDayEnd - liDayStart 'appts are every half hour on the half hour ltLoopStart$ = DateAdd("n", 30 * (i% - 1), ltDate$ & " " & lvStartTime) ltLoopEnd$ = DateAdd("n", 30, ltLoopStart$) 'check the FreeBusy string to see if this time is avail. If IsTimeAvailable(ltFreeBusy$, Trim$(ltLoopStart$), _ Trim$(ltLoopEnd$)) Then 'this time is available, output to the form ltForm$ = ltForm$ & " <input type=radio name=""Time"" " 'If liTimesAvailable% is False, this is the first available ' time, so make this radio button selected If Not (liTimesAvailable%) Then ltForm$ = ltForm$ & "checked " End If 'finish creating the radio button, ' the value is the start date/time of the appt. ltForm$ = ltForm$ & "value=""" & ltLoopStart$ & """>" & _ Format$(TimeValue(ltLoopStart$), "H:NN AM/PM") & Chr$(13) 'turn the flag to True liTimesAvailable% = True End If Next 'finish up the form ltForm$ = ltForm$ & Chr$(13) ltForm$ = ltForm$ & "<input type=submit value=""Book Appointment"">" ltForm$ = ltForm$ & " <input type=reset></pre></form>" 'if there are available times, output the form variable If liTimesAvailable% Then OutputString ltForm$ 'otherwise output an apology Else OutputString "<h2>Sorry, nothing available on " OutputString "the date you selected.</h2>" End If 'finish up OutputString "</body></html>" Close #guCGIData.OutputFileHandle End '===================================== ' Handler for all run-time errors ' FormError: 'close the S+ objects (VERY IMPORTANT!!!!) If Not (objSPlusApp Is Nothing) Then objSPlusApp.Logoff Set objSPlusApp = Nothing End If 'call the standard Win/CGI error handler: Call ErrorHandler(Err, Error$) End Sub Public Function IsTimeAvailable(ptFreeBusy As String, _ pvStart As Variant, pvEnd As Variant) As Integer 'given a FreeBusy string from a Schedule+ schedule, ' and start and end times, determine the FreeBusy status ' for the given time period (a status of busy for any part ' of the interval returns False) 'some constants used Const FREE_BUSY_INTERVAL = 30 Const MINUTES_PER_DAY = 24 * 60 Dim lvStartOfMonth As Variant Dim llMinutes As Long Dim liStart As Integer Dim liEnd As Integer Dim ltFreeBusyOfInterest$ IsTimeAvailable = True 'if the FreeBusy string contains no "1"s, all times are available If InStr(ptFreeBusy, "1") = 0 Then Exit Function 'get the start of the month lvStartOfMonth = DateValue(DateAdd("d", 1 - Day(pvStart), (pvStart))) 'get the number of intervals between the start of the month ' and pvStart llMinutes = DateDiff("n", lvStartOfMonth, pvStart) ' use the DIV operator to return an integer liStart = (llMinutes \ FREE_BUSY_INTERVAL) + 1 ' if there was a remainder, add 1 to the interval number If llMinutes Mod FREE_BUSY_INTERVAL > 0 Then liStart = liStart + 1 'get the number of intervals between the start of the month ' and pvEnd llMinutes = DateDiff("n", lvStartOfMonth, pvEnd) ' use the DIV operator to return an integer liEnd = (llMinutes \ FREE_BUSY_INTERVAL) + 1 ' if there was a remainder, add 1 to the interval number If llMinutes Mod FREE_BUSY_INTERVAL > 0 Then liEnd = liEnd + 1 'get the free/busy string for the time interval we're interested in ltFreeBusyOfInterest$ = Mid$(ptFreeBusy, liStart, liEnd - liStart) 'return the result based on whether or not a "1" is in the interval If InStr(ltFreeBusyOfInterest$, "1") Then IsTimeAvailable = False Else IsTimeAvailable = True End If End Function
The makeappt application takes the selected appointment time and the hidden fields from the HTML form generated by checkscd and creates a new SPlus appointment item on the Exam Room A schedule. To create the application, start a new project. Add the CGI32.BAS and FUNCS.BAS modules and insert a new module. Copy the IsTimeAvailable() function from CHECKSCD.BAS into the new module and save the new module as MAKEAPPT.BAS. Insert a new procedure named Main and copy the code from Listing 9.3 into this new procedure.
The code at the beginning of Sub Main is virtually identical to the Sub Main code of CHECKSCD.BAS, so I won't bother repeating the details. The discussion in this section starts at the line of code following the comment Start of new code!! about three-quarters down Listing 9.3.
The first line,
Set objNewAppointment = objCurrScd.Appointments.New
creates a new Item object in the schedule's Appointments table. This is filled with the properties that are necessary to define the appointment that the patient has requested. To simplify the typing necessary,
With objNewAppointment
causes all property assignments to refer to the new appointment item. The lines of code following the With are where the appointment's properties are specified. These properties are the starting date/time, the ending date/time, the tentative flag (BusyType), the Notes, the alarm flag (Ring), and the actual text of the appointment (Text). Note also that the appointment's ContactItemID is set by
.ContactItemId = objContacts.Item.ItemID
This assigns the ContactItemID to the ItemID for the current contact referred to by objContacts.Item. This, because of code executed earlier, points to the Contacts table record for the Patient ID specified in the CGI data.
After all the properties are assigned, the new appointment item's Flush method is invoked, and the Item object is set to Nothing in order to actually save the property assignments:
objNewAppointment.Flush Set objNewAppointment = Nothing
The remainder of the code simply outputs an HTML verification page to the user and closes down using the standard CGI close code.
Listing 9.3. Sub Main of the makeappt module.
Public Sub Main() '================================ ' Typical Win/CGI startup code ' On Error GoTo FormError If InStr(Command$, " ") Then guCGIData.ProfileFile = Left$(Command$, InStr(Command$, " ") - 1) Else guCGIData.ProfileFile = Command$ End If If LoadCGIData() = 0 Then 'if we couldn't load all the CGI data, 'could we at least open the Output File? If guCGIData.OutputFileHandle <> 0 Then 'yes - call ErrorHandler to handle this Call ErrorHandler(-1, "Error loading CGI Data File") Else 'no - forget about anything else! End End If End If OutputString "Content-Type: text/html" OutputString "" ' ' End of Win/CGI startup code '================================ '================================ ' Validate form data ' 'check the date/time selected on the form: ltDate$ = Trim$(GetFieldValue("Time")) If Not (IsDate(ltDate$)) Then OutputString "<h2>An invalid date was sent to MakeAppt.exe</h2>" OutputString "The date sent was: " & ltDate$ OutputString "</body></html>" Close #guCGIData.OutputFileHandle End End If If Len(Trim$(GetFieldValue("Reason"))) = 0 Then OutputString "<h2>You Forgot to Enter a Reason</h2>" OutputString "You must enter a reason for the appointment!" OutputString "</body></html>" Close #guCGIData.OutputFileHandle End End If If Len(Trim$(GetFieldValue("PatientID"))) = 0 Then OutputString "<h2>There is no Patient ID</h2>" OutputString "</body></html>" Close #guCGIData.OutputFileHandle End End If ' ' End of form data validation '=================================== '=================================== ' Schedule+ Code ' Dim objSPlusApp As Object Dim objCurrScd As Object Dim objContacts As Object Dim objAppointments As Object 'create the Schedule Plus (S+) object Set objSPlusApp = CreateObject("SchedulePlus.Application.7") 'log on using the "Exam Room A" Exchange profile ' this profile logs on to the Exam Room A mailbox & schedule objSPlusApp.Logon "Exam Room A" 'get the schedule for the logged-on user (Exam Room A) Set objCurrScd = objSPlusApp.ScheduleLogged 'validate the Patient ID entered 'Patients are stored in the Exam Room A contact list Set objContacts = objCurrScd.Contacts 'User1 is the Patient ID, set a restriction ltFormattedID$ = Format$(GetFieldValue("PatientID"), "###-##-####") objContacts.SetRestriction "User1", ltFormattedID$, "==" 'if we're at the end of the Contacts table, no such Patient ID If objContacts.IsEndOfTable Then OutputString "<h2>Your Patient ID Is Invalid!</h2>" OutputString "The ID you entered was: " & ltFormattedID$ OutputString "<p>Your Patient ID is your Social Security Number" OutputString "</body></html>" Close #guCGIData.OutputFileHandle 'close the S+ objects (VERY IMPORTANT!!!!) objSPlusApp.Logoff Set objSPlusApp = Nothing End End If 'get the patient's first name from the Contacts table: ltFirstName$ = objContacts.Item.FirstName ltLastName$ = objContacts.Item.LastName ltPhone$ = objContacts.Item.PhoneHome 'get the FreeBusy string for the date entered ' NOTE: S+ returns a string for the ENTIRE month starting at day 1 ltFreeBusy$ = objCurrScd.FreeBusy(ltDate$) 'the end date/time will be 30 minutes after the start date/time ltEnd$ = DateAdd("n", 30, ltDate$) 'check the FreeBusy string to see if this time is avail. If IsTimeAvailable(ltFreeBusy$, Trim$(ltDate$), Trim$(ltEnd$)) = 0 Then 'time is not available OutputString "<head><title>Conflicting Appointment</title></head>" OutputString "<h2>The Selected Time Is Unavailable!</h2>" OutputString "The time you selected has likely been taken " OutputString "after you loaded the previous form. Please " OutputString "use your browser's Back feature to return to " OutputString "the Appointment Request page and click Submit" OutputString "again." OutputString "</body></html>" Close #guCGIData.OutputFileHandle 'close the S+ objects (VERY IMPORTANT!!!!) objSPlusApp.Logoff Set objSPlusApp = Nothing End End If '================================ ' Start of new code!! '================================ 'everything is a GO, create the new appointment Set objNewAppointment = objCurrScd.Appointments.New With objNewAppointment .Start = ltDate$ .End = ltEnd$ .BusyType = 1 'Non-tentative .Notes = "Requested via the Web on " & Format$(Now, "short date") .Ring = 0 'No alarm .ContactItemId = objContacts.Item.ItemID ltText$ = ltFirstName$ & " " & ltLastName$ & Chr$(13) & Chr$(10) ltText$ = ltText$ & Trim$(GetFieldValue("Reason")) .Text = ltText$ End With 'save the appointment objNewAppointment.Flush 'close the S+ objects (VERY IMPORTANT!!!!) Set objNewAppointment = Nothing objSPlusApp.Logoff Set objSPlusApp = Nothing ' ' End of Schedule+ Code '===================================== OutputString "<html><head><title>Appointment Booked</title></head>" OutputString "<center><h1>Appointment Booked</h1></center>" OutputString "Your appointment time, " & Format$(ltDate$, "H:NN AM/PM") OutputString " on " & Format$(ltDate$, "short date") OutputString ", has been verified. <br>Our office will call within " OutputString "24 hours of the appointment to verify. <br>" OutputString "We will call you at " OutputString ltPhone$ & ". If this number is not correct, please contact " OutputString "our office right away!" 'finish up OutputString "</body></html>" Close #guCGIData.OutputFileHandle End FormError: 'close the S+ objects (VERY IMPORTANT!!!!) If Not (objSPlusApp Is Nothing) Then objSPlusApp.Logoff Set objSPlusApp = Nothing End If Call ErrorHandler(Err, Error$) End Sub
Now that you've entered the code, it's time to install and test the applications. Fortunately, this is a simple Web site to test. There are only two forms requiring user input and only two applications.
Possibly the trickiest part is getting the path to the SPlus server working properly. If your Web server is running on a machine that can run Microsoft Exchange or Schedule Plus and successfully connect to an Exchange server, then you should have no problems with the code. If the Web server is not running on such a machine, you need to make it do so. Install the Microsoft Exchange client on the Web server machine. Then follow the instructions in this chapter's section titled "Setting Up SPlus Resources" to set up the Exam Room A mailbox and schedule.
If you haven't yet created any contacts or appointments on the Exam Room A schedule, do so now. Follow the instructions given in the "Setting Up SPlus Resources" section.
If all is a go on connecting to the Exchange server from your Web server machine, it's time to install the applications. Compile the applications into executable form, using the names checkscd.exe and makeappt.exe. Copy these files into your Web server's Win/CGI application directory.
Create the Appointment Request page given in Listing 9.1. You can give it any name you wish, but it must be saved into a directory that is a valid document directory for your Web server. Otherwise, it won't be accessible to a Web browser.
Start your Web server, if it's not already running. Load the Appointment Request page. Enter a date that corresponds to a day on which you created an appointment on the schedule. Enter the Patient ID for one of the contacts you entered. Enter some text in the Reason field, and click the Submit button.
After the CGI application churns for a while (the SPlus OLE automation server can be very slow at creating the Application object and at performing the Logon method), you will see a page that contains radio buttons for each of the available times on the date specified. The times for which you have already created appointments on the schedule should not listed. If some of these times are listed, check the code in the IsTimeAvailable() function. The code for this function is in Listing 9.2.
If the page has loaded successfully, view the HTML source for this document. You should see the HTML for the form, and it should include hidden fields containing the data you entered for Patient ID and Reason on the Request Appointment form. If not, check and correct the code that creates ltForm$ in Sub Main of CHECKSCD.BAS.
If the page displays some available times, select a time using the radio buttons provided. For this test, don't click the Book Appointment button yet. Instead, launch SPlus and open the schedule for Exam Room A. Move to the date you specified for the appointment and create an appointment at the same time as the radio button you selected. Close SPlus. Now click the Book Appointment button. This action should obviously create a conflicting appointment on the schedule. The makeappt application should return a page resembling Figure 9.9. If the Appointment Booked page is returned and you're sure your dates and times should cause a conflict, check the code in MAKEAPPT.BAS that appears immediately before the new code comment label. This is how the makeappt application ensures that user A's time has not been chosen simultaneously by another user during the lapse between creating the page, selecting a time, and clicking the Book Appointment button.
Figure 9.9. The Conflicting Appointment page.
Use the Back command in your Web browser to return to the Available Times page. Choose a time that will not cause a conflict and click the Book Appointment button. You could also use Back one more time to return to the Request Appointment page and click the Submit button there. This would create a new Available Times page that no longer includes the conflicting times. Either way, clicking the Book Appointment button should now produce the Appointment Booked page shown in Figure 9.10.
Figure 9.10. The Appointment Booked page.
To verify that the appointment has indeed been created, launch SPlus and open the schedule for Exam Room A. Open the date for which you created the appointment from the Web page. The screen should look similar to Figure 9.11. Don't be alarmed that only the patient name is shown on the line for the appointment. The Text property of the appointment is assigned with a carriage-return/line-feed character separating the patient's name from the reason for the appointment.
Figure 9.11. SPlus displaying the appointment just created.
If you use the Edit | Edit Item menu while the appointment is selected, you can see where the properties assigned in the makeappt code are displayed within SPlus, including the full text of the Text property. For example, the Notes tab shown in Figure 9.12 illustrates how SPlus displays an appointment's Notes property.
Figure 9.12. The Notes tab for the appointment just created.
There are many ways you can extend this application to provide greater functionality. I'll leave the coding up to you, but here are a few possible ideas:
This chapter demonstrated how to connect your Web pages to any OLE automation server accessible to the machine running your Web server. This topic will become increasingly more important as VB4 advances the creation of OLE servers. VB4 has made it possible and in fact, simple to create an OLE automation server using Visual Basic. This means that any existing application, or at least some pieces of it, can easily be converted to server as an OLE automation server. I hope this chapter has shown you how easy it is to use the Win/CGI interface to access such servers.
The chapter also should have whetted your appetite to explore the SPlus OLE automation server in more detail. Although the SPlus OLE object is complicated and can be cumbersome to use it is a fun server to control, once you get past the few pitfalls. I'm sure any programmer who is responsible for applications used in a workgroup or enterprise environment can think of myriad ways to use the SPlus OLE server to their benefit.