The first of three procedures that we'll combine into a Control procedure manages how travel reservations are entered into the Club ParaDEASE application. Before we actually save a reservation record, however, we need to ensure that there are adequate vacancies at the requested club on the specified date. The INPUT RESERVATIONS procedure uses a Data-entry form to let us check on room availability. Based on the results of our Data-entry inquiry, we can tell DataEase to enter the reservation record or display a warning if there aren't enough accommodations at the destination club.
As shown below, the INPUT RESERVATIONS Data-entry form contains four fields: CLUB ID, CLUB NAME, DEPARTURE DATE, and ROOMS REQUIRED. The CLUB ID and DEPARTURE DATE fields are used to specify the date and destination for the reservation. The ROOMS REQUIRED field is used to indicate how many rooms the caller wants to reserve. The CLUB NAME field contains a Derivation formula that looks up the name of the club.
The Data-entry form also contains two buttons. The OK button invokes the Save Record action, which causes DataEase to begin processing the script. The Cancel button invokes the Close Document action, which tells DataEase to close the INPUT RESERVATIONS procedure.
When the reservation clerk enters the CLUB ID and DEPARTURE DATE in the Data-entry form, DataEase uses an ad hoc relationship to find the matching record in the CLUB ROOMS table. The matching record tells DataEase how many rooms are available at the destination club on the requested date.
In addition to checking room availability at the destination club, the INPUT RESERVATIONS procedure posts new member information from the MEMBERS table. This information is entered into two related tables: the appropriate member information is posted into the RESERVATIONS table (using the data entered into the Data-entry form) and the number of vacancies is updated in the CLUB ROOMS table.
The complete script for the INPUT RESERVATIONS procedure is shown below:
if data-entry ROOMS REQUIRED > any CLUB ROOMS with
(CLUB ID = data-entry CLUB ID and DEPARTURE DATE =
data-entry DEPARTURE DATE) VACANCIES then
message "Not enough rooms are available. |
Please choose another club or date." window.
while current status not = 1 do
input using MEMBERS into "TEMPMEM" .
modify records in CLUB ROOMS with
(CLUB ID = data-entry CLUB ID and
DEPARTURE DATE = data-entry DEPARTURE DATE)
VACANCIES:= VACANCIES- data-entry ROOMSREQUIRED.
enter a record in RESERVATIONS
copy all from TEMPMEM ;
CLUB ID:= data-entry CLUB ID;
DEPARTURE DATE := data-entry DEPARTURE DATE ;
ROOMSREQUIRED := data-entry ROOMSREQUIRED .
record entry RESERVATIONS .
message "You are not authorized to |
modify or delete records " window .
Now, let's examine this script one statement at a time:
if data-entry ROOMS REQUIRED > any CLUB ROOMS with
(CLUB ID = data-entry CLUB ID and DEPARTURE DATE
= data-entry DEPARTURE DATE) VACANCIESthen
The script begins with an if Command. The if Command tells DataEase to execute one action (the action specified after the keyword then) if the statement that follows the if command is true, and a different action (the action specified after the keyword else) if the statement is false.
In our example, the if command tells DataEase to check the number of rooms requested in the Data-entry form against the number in the VACANCIES field in the matching record in the CLUB ROOMS table. If the number in the VACANCIES field in the CLUB ROOMS table is greater than the number in the ROOMS REQUESTED field on the Data-entry form, DataEase processes the reservation. Otherwise, DataEase displays the message:
Not enough rooms are available.
Please choose another club or date.
In order to check the VACANCIES field in the matching record in the CLUB ROOMS table, DataEase needs to know how the record in the Data-entry form is related to the record(s) in the CLUB ROOMS table. The script tells DataEase how to find the matching record in the CLUB ROOMS table by specifying an ad hoc relationship:
(CLUB ID = data-entry CLUB ID and DEPARTURE DATE = data-entry
DEPARTURE DATE)
See DG 8 for more information on DataEase relationships.
An ad hoc relationship is a DQL expression that tells DataEase how the records in two different forms are related when no permanent relationship has been defined in the DataEase Relationships form. The ad hoc relationship expressed above tells DataEase to find the record in the CLUB ROOMS table that has the same CLUB ID and DEPARTURE DATE as the values entered in the Data-entry form.
For all purposes, an ad hoc relationship is similar to a relationship stored in the Relationships form, except an ad hoc relationship is only valid while the procedure is in progress (the relationship is discarded when the procedure completes execution).
The next section of the INPUT RESERVATIONS script (below) uses two Procedural commands, while and case, and the input using Processing command to tell DataEase what to do if there are sufficient vacancies at the destination club on the date requested in the Data-entry form.
while current status not = 1 do
input using MEMBERS into "TEMPMEM" .
The next section of the script tells DataEase to modify the value stored in the VACANCIES field in the matching record in the CLUB ROOMS table by subtracting the number of rooms requested on the Data-entry form.
modify records in CLUB ROOMS with
(CLUB ID = data-entry CLUB ID and
DEPARTURE DATE = data-entry DEPARTURE DATE)
VACANCIES: = VACANCIES-data-entry ROOMSREQUIRED.
The last section of the script tells DataEase to enter a new record in the RESERVATIONS form detailing all the required personal and family information, destination club, reservation date, etc.
enter a record in RESERVATIONS
copy all from TEMPMEM ;
CLUB ID:= data-entry CLUB ID;
DEPARTURE DATE := data-entry DEPARTURE DATE ;
ROOMSREQUIRED := data-entry ROOMSREQUIRED .
record entry RESERVATIONS .
Finally, if the caller decides to cancel at any point, we want DataEase to discard all the data entered by the reservations clerk. Therefore, we need a way to gather information from the caller and then tell DataEase how to process it.
The DQL case and input using commands can be used together to gather information during a DQL Procedure and then process the information in several different ways. Similar to the if command, the DQL case command tells DataEase to perform one of a number of possible actions based on which value is stored in the current status variable. The current status variable is a special DQL system variable that tells DataEase which menu command or keystroke the user executes after entering data as shown below.
|
When the user finishes entering data in the temporary input form and makes this menu selection... |
...DataEase stores this value in |
|
File>>Close |
1 |
|
File>>Save As New Record |
2 |
|
File>>Save |
3 |
|
File>>Delete |
4 |
Since much of the information required in the MEMBERS form must also be entered into the RESERVATIONS form, we only want to input this information once and then let DataEase automatically copy it into the appropriate database tables. The DQL input using command tells DataEase to display a database form as a temporary form that is used to gather the information which will later be posted into the appropriate database tables.
The while command sets up a processing loop. In our example, the while command tells DataEase to continue processing each new reservation in the manner described above as long as the value in the current status variable does not equal one. When current status equals one, DataEase exits the while loop and resumes processing the remainder of the script.
The diagram below shows the flow of information from the TEMPMEM form, depending on which processing key is used.
Now that you have an overview, let's examine the remainder of the INPUT RESERVATION script introduced earlier in a little more detail.
The first line of the script reads:
while current status not = 1 do
This line tells DataEase to continually loop through and process each of the statements that follow until the current status variable indicates that the user chose File>>Close to close the input form.
The next line in the script uses the input using command. The input using command provides all the facilities of record entry under the control of the DataEase Query Language. As each record is entered into the TEMPMEM form, the record is held in memory until a processing action is invoked. When an action is initiated, the script takes control and processes the current record according to the instructions in the script:
input using MEMBERS into "TEMPMEM" .
This line tells DataEase to display the TEMPMEM form. In our example, we'll copy member information to the RESERVATIONS form and update CLUB ROOMS data.
The next line tells DataEase to check the value in the current status variable:
The case command tells DataEase to compare an expression (e.g., current status) to a series of values and execute a different action based on which comparison is true.
The next two lines tell DataEase what to do if the value of the current status variable is "1":
Remember that the value in the current status variable is set to one when the user invokes a close document action. The exit command tells DataEase to stop processing the script. When the user chooses File>>Close, DataEase terminates all script processing.
The next line tells DataEase to check the current status variable for a value of "2" (the action invoked when you click a Save button or icon or choose Edit>>Save As New Record). If the value of the current status variable is "2", the following section of the script is executed:
The first line tells DataEase to save a new record in the RESERVATIONS table. The subsequent lines tell DataEase which information to enter as the new record. The enter a record command can be used with copy all from or to specify individual fields. In our example, we do a combination of both. Copy all from TEMPMEM tells DataEase to copy all field values in TEMPMEM to the corresponding fields (e.g., those that have the identical names) in RESERVATIONS. Any fields whose name differs must be dealt with on an individual basis as demonstrated in the last three lines of the above example. The enter a record command is terminated by a period.
Since all the information needed to enter a new reservation isn't included in the RESERVATIONS form, the next line of the script provides direct record entry access to RESERVATIONS.
record entry reservations .
The record entry command lets you perform traditional record entry operations from within a procedure. In the RESERVATIONS form we can enter additional information (e.g., names of family members included in the reservation) not included in the MEMBERS form. The record entry command is terminated by a period. This is the last command executed when the user invokes a save action.
The next section tells DataEase what to do if the user presses any of the remaining processing keys:
message "You are not authorized to modify or
delete records. " window .
The others command encompasses any value that is not specifically tested with a value command. In our example, that means any value other than 1 or 2 If the user invokes a modify or delete action the value in the current status variable is set to 3 (modify) or 4 (delete). For the sake of simplicity, we are disallowing those actions. If the user tries to modify or delete a record, DataEase displays the message shown above. The period terminates the message command and the end terminates the case command since there are no other values to test.
Note: If we hadn't included the others command or additional value commands to handle the current status values of 3 and 4, DataEase would simply ignore any attempt the user made to modify or delete a record.
The only commands remaining are two end commands:
To satisfy the DQL syntax requirements, a script must contain one end command for each for, if , while and/or case command.
The complete script for the INPUT RESERVATIONS procedure reads:
if data-entry ROOMS REQUIRED > any CLUB ROOMS with
(CLUB ID = data-entry CLUB ID and DEPARTURE DATE =
data-entry DEPARTURE DATE) VACANCIES then
message "Not enough rooms are available. |
Please choose another club or date." window.
while current status not = 1 do
input using MEMBERS into "TEMPMEM" .
modify records in CLUB ROOMS with
(CLUB ID = data-entry CLUB ID and
DEPARTURE DATE = data-entry DEPARTURE DATE)
VACANCIES:= VACANCIES- data-entry ROOMSREQUIRED.
enter a record in RESERVATIONS
copy all from TEMPMEM ;
CLUB ID:= data-entry CLUB ID;
DEPARTURE DATE := data-entry DEPARTURE DATE ;
ROOMSREQUIRED := data-entry ROOMSREQUIRED .
record entry RESERVATIONS .
message "You are not authorized to |
modify or delete records " window .
This script checks the availability of rooms at the club and date requested. If there aren't adequate accommodations, it displays an error message, then redisplays the Data-entry form so the data entry clerk can try another club/date combination. Otherwise, DataEase saves a new record in the RESERVATIONS form and decrements the number of available rooms at the requested club on the specified date.
If you are creating this procedure as you read, before proceeding to the next section, choose File>>Save As. DataEase displays the Document Save As dialog.
Enter INPUT RESERVATIONS as the document name and click OK. DataEase saves the procedure on disk and returns you to the script. After the procedure is saved on disk, you can later execute the procedure by any of the following methods:
Choose File>>Open, select the procedure name from the list of documents DataEase displays, then click OK. Open the procedure in User View to execute it or in Designer View to view or modify it.
If the procedure is already open in Designer View, choose File>>User View.
Specify the name of the procedure in a run procedure command within a script or on a custom menu.
Next, we'll create a procedure that determines the discount given to each member based on the number of children and adults included in a reservation.