Procedure 2: CALCULATE DISCOUNTS

The second procedure is based on the fact that Club ParaDEASE discounts trip reservations based on the number of children and adults included in each reservation. The object of the procedure is to calculate the following totals:

• Number of children going on the trip.

• Number of adults going on the trip.

• Dollar amount of the trip.

• Dollar amount of the discount applied to the cost of the trip.

After each of these totals is determined, we use a variable (an expression that represents a varying value) to store the calculated result. Variables let us use the values as often as we want within the procedure without repeating the formulas used to derive them.

The script for the CALCULATE DISCOUNTS procedure must tell DataEase to do all of the following:

• Create five separate variables to store: each member's reservation total, the number of children, the number of adults, each member's total discount amount, and the RESERVATION ID.

• Set up discounts based on the number of children and adults with the discounts expressed as a dollar amount to be subtracted from the total reservation price.

• Process the related RESERVATION DETAIL records, calculating the sum of the RESERVATION PRICE for all family members included in the reservation.

• Modify the RESERVATIONS records, entering each reservation's final price in the TOTAL DUE field.

Script for the CALCULATE DISCOUNTS Procedure

The full script for the CALCULATE DISCOUNTS procedure reads as follows:

 define temp "KIDS" Number .

 define temp "ADULTS" Number .

 define global "DISCOUNT" Number .

 define global "RESTOTAL" Number .

 define global "RESERVATION#" Number .

 for RESERVATIONS

 with CONFIRMED = NO ;

  assign temp KIDS := count of RESERVATION DETAIL

  named "KIDCOUNT" with (AGE STATUS = "child" ).

  assign temp ADULTS := count of RESERVATION

  DETAIL named "ADULTCOUNT" with (AGE STATUS =

   "adult" ).

  assign global RESTOTAL := sum of RESERVATION

  DETAIL RESERVATION PRICE .

  assign global RESERVATION # := RESERVATION ID .

   case (temp KIDS)

    value = 1 :

   assign global DISCOUNT := global RESTOTAL * 0.05 .

    value = 2 :

   assign global DISCOUNT := global RESTOTAL * 0.10 .

    value >= 3 :

   assign global DISCOUNT := global RESTOTAL * 0.15 .

  end

  if temp ADULTS > 2 then

   assign global DISCOUNT := global DISCOUNT + (( global

   RESTOTAL - global

   DISCOUNT) * 0.05) .

  end

  modify records

   TOTAL DUE := global RESTOTAL- (global RESTOTAL *

   global DISCOUNT ).

  run procedure RESERVATION INVOICE .

 end

Explanation of the Script

Notice first that this script does not start with a for command. When using variables in a script, you should define the variables at the beginning. Defining variables at the start of a procedure makes it easy to find (and change) the variables if necessary, and it also makes the script easier to follow.

Now, let's look at this script one statement at a time.

The first line in the script creates and names a temporary variable to hold the total number of children taking the trip:

 define temp "KIDS" Number ; .

This first line has five elements:

• The DQL define command. This tells DataEase to create a variable.

• The keyword temp. This tells DataEase the variable is a temporary variable, not a global variable.

• The name of the variable (KIDS). The name of a variable must be enclosed in quotes when it is first specified in a script.

• The type of the variable (Number). A variable can be a number, a text expression, or any other DataEase field type except Choice, Dollar, Sequenced ID, or Yes/No.

• A period. The define command must always end with a period.

The first five lines of the script are similar in purpose:

 define temp "KIDS" Number .

 define temp "ADULTS" Number .

 define global "DISCOUNT" Number .

 define global "RESTOTAL" Number .

 define global "RESERVATION#" Number .

These statements create two temporary variables and three global variables. The temporary variables include one to store the number of children and one to store the number of adults. Global variables are used to store the total reservation price, the discount amount applied to each reservation total, and the RESERVATION ID. A global variable can be passed from one procedure to another, avoiding the need to recalculate the value each time you use it. Since the RESERVATIONS form doesn't store the discount amount or the pre-discount total, we'll store both values in global variables so they can be passed along and used in the next procedure. The RESERVATION ID is also stored in a global variable so the ID can be passed to the next procedure to identify which record to process.

The next part of the script tells DataEase the name of the primary form and which records to select:

 for RESERVATIONS

 with CONFIRMED = NO ;

The group of records we want DataEase to select are the unconfirmed RESERVATIONS records. Reservations are not considered confirmed until the control procedure described at the end of this chapter is complete. This for command processes each record that stores the value NO in the CONFIRMED field.

The next part of the script tells DataEase how to calculate the number of children taking the trip (the value we want to assign to the "KIDS" variable):

 assign temp KIDS := count of RESERVATION DETAIL

 named "KIDCOUNT" with (AGE STATUS = "child" ).

The above statement uses the DQL assign command:

 assign temp KIDS :=

This statement tells DataEase to assign KIDS a value. The assignment operator symbol (:=) follows the name of the variable. This operator serves the same purpose with a temporary variable that it did in the previous procedure following a modify records command. The value of the variable is determined by the expression on the right of the symbol. The remainder of this line reads:

 count of RESERVATION DETAIL named "KIDCOUNT"

 with (AGE STATUS = "child" );

This part of the assign statement tells DataEase what value to assign to the KIDS variable - the number of related records that store the value child in the AGE STATUS field.

DataEase lets you add criteria to a predefined relationship (e.g., with AGE STATUS = "child"). This is another use of an ad hoc relationship. When you create an ad hoc relationship in this manner, any additional criteria you specify applies to the relationship for the remainder of the script. But before we stipulate the selection criterion (AGE STATUS = "child"), we use the named operator to rename the relationship.

In addition to creating ad hoc relationships, you can use the named operator to provide a custom relationship name, much the way you do in the Relationships form. When you rename a relationship in a script, the original predefined relationship remains unchanged. This feature lets you use either relationship throughout the remainder of the script: the original predefined relationship or the modified, renamed relationship.

Note: You can't use the same relationship name to specify two different sets of ad hoc criteria. If you want to specify two or more sets of criteria for the same relationship, you must provide a unique name for each new relationship. The unique name lets DataEase distinguish different groups of records selected from the same table.

Prior to this line in the sample script, the relationship between RESERVATIONS and RESERVATION DETAIL is based on the original Match fields specified in the Relationships form (e.g., RESERVATION ID = RESERVATION ID). After this line in the script, the relationship has changed. The new relationship still links records based on the matching RESERVATION ID but now RESERVATIONS records are related only to those matching RESERVATION DETAIL records that contain the value child in the AGE STATUS field. Because we established a unique name for the new relationship (KIDCOUNT), we still have access to the original relationship (RESERVATION DETAIL), which we can use in its original form or create an ad hoc relationship again using different criteria.

The next two lines of the script serve a similar purpose:

 assign temp ADULTS := count of RESERVATION DETAIL

 named "ADULTCOUNT" with (AGE STATUS = "adult" ).

This time the assign statement tells DataEase what value to assign to the ADULTS variable: the number of related records that store the value adult in the AGE STATUS field. Because we still want to be able to access the original RESERVATION DETAIL relationship to calculate the total cost of the reservation later in the script, we again rename the relationship (ADULTCOUNT) before we specify the ad hoc criteria.

The next line of the script assigns a value to the global variable that stores the total price of the reservation:

 assign global RESTOTAL := sum of RESERVATION

 DETAIL RESERVATION PRICE .

This line tells DataEase to process all the related RESERVATION DETAIL records (not just the related adult or child records) and to sum the value in the RESERVATION PRICE field. Notice that this time we use the original relationship rather than adding any ad hoc criteria because we want DataEase to process all the related records.

The next line of the script assigns a value to the last of the global variables:

 assign global RESERVATION# := RESERVATION ID .

This line tells DataEase to assign the value in the RESERVATION ID field in the current record to the RESERVATION # variable. We will use this variable as the selection criteria in the next procedure to identify the record we want DataEase to process.

Because the assign statements are contained within the for loop, each variable is reassigned each time a new record is processed by the for command.

The whole script up to this point reads as follows:

 define temp "KIDS" Number .

 define temp "ADULTS" Number .

 define global "DISCOUNT" Number .

 define global "RESTOTAL" Number .

 define global "RESERVATION" Number .

 for RESERVATIONS

 with CONFIRMED = NO ;

  assign temp KIDS := count of RESERVATION DETAIL

  named "KIDCOUNT" with ( AGE STATUS = "child" ) .

  assign temp ADULTS := count of RESERVATION

  DETAIL named "ADULTCOUNT" with ( AGE STATUS =

   "adult" ) .

  assign global RESTOTAL := sum of RESERVATION

  DETAIL RESERVATION PRICE .

  assign global RESERVATION := RESERVATION ID .

The next section of the script begins with a case command:

 case ( temp KIDS)

The discount applied to each reservation is determined first by the number of children taking the trip. An additional discount is given for reservations that include more than two adults. This line of the script tells DataEase to check the value currently stored in the KIDS variable. Remember that each time DataEase cycles through the for loop, the value in each variable is rederived.

The next two lines of the script tell DataEase what to do if one child is included in the reservation:

 value = 1 :

  assign global DISCOUNT := global RESTOTAL * 0.05 .

If the value in the KIDS variable is one, DataEase assigns the DISCOUNT variable a value equal to five percent of the total reservation cost.

The next four lines of the script are similar to the last two:

 value = 2 :

  assign global DISCOUNT := global RESTOTAL * 0.10 .

 value >= 3 :

  assign global DISCOUNT := global RESTOTAL * 0.15 .

 end

If the value in the KIDS variable is not 1, DataEase checks the next value statement. If the value is 2, DataEase assigns the DISCOUNT variable a value equal to 10 percent of the reservation total. If the value in the KIDS variable is 3 or more, DataEase assigns the DISCOUNT variable a value equal to 15 percent of the reservation total. The next line of the script contains an end command. This end terminates the case command.

The next three lines of the script tell DataEase to increase the discount amount if more that two adults are included in the reservation:

 if temp ADULTS > 2 then

  assign global DISCOUNT := global DISCOUNT + ( (

 global RESTOTAL - global DISCOUNT ) * 0.05 ) .

 end

Each reservation total is discounted an additional 5 percent for three or more adults. The if command checks the value in the ADULTS variable. If that value exceeds 2, the value in the DISCOUNT variable is reassigned to figure in the additional 5 percent. The parentheses in the assign statement clarify the sequence of mathematical operations. When parentheses are used, DataEase performs math operations from the inside out. In this example, DataEase subtracts the current value in the DISCOUNT variable from the amount held in the RESTOTAL variable. The result is multiplied by 5 percent and that value is added to the original discount dollar amount.

The end command terminates the if command.

The last section of the query modifies the RESERVATIONS record to enter the discounted reservation cost in the TOTAL DUE field:

 modify records

  TOTAL DUE := global RESTOTAL - global DISCOUNT.

Because this portion of the script is still under the control of the for command, DataEase modifies just the record currently being processed. The TOTAL DUE field is set equal to the total cost of the reservation minus the discount amount DataEase calculated earlier in the script. The modify records processing command is terminated by a period.

The final command tells DataEase to run another procedure:

  run procedure RESERVATION INVOICE .

 end

run procedure is a Control command that tells DataEase to run another procedure that prints an invoice for the current RESERVATIONS record. Because this command is within the for loop, the RESERVATION INVOICE procedure is run once for each record processed by the for command. The RESERVATION INVOICE procedure is explained in the next section of this chapter. The only remaining command is another end command. This end terminates the for loop.

The completed script for the CALCULATE DISCOUNTS procedure reads as follows:

 define temp "KIDS" Number .

 define temp "ADULTS" Number .

 define global "DISCOUNT" Number .

 define global "RESTOTAL" Number .

 define global "RESERVATION #" Number .

 for RESERVATIONS

 with CONFIRMED = NO ;

  assign temp KIDS := count of RESERVATION DETAIL

  named "KIDCOUNT" with ( AGE STATUS = "child" ) .

  assign temp ADULTS := count of RESERVATION

  DETAIL named "ADULTCOUNT" with ( AGE STATUS =

   "adult" ) .

  assign global RESTOTAL := sum of RESERVATION

  DETAIL RESERVATION PRICE .

  assign global RESERVATION # := RESERVATION ID .

   case ( temp KIDS )

    value = 1 :

   assign global DISCOUNT := global RESTOTAL * 0.05 .

    value = 2 :

   assign global DISCOUNT := global RESTOTAL * 0.10 .

    value >= 3 :

   assign global DISCOUNT := global RESTOTAL * 0.15 .

  end

  if temp ADULTS > 2 then

   assign global DISCOUNT := global DISCOUNT + ( ( global

   RESTOTAL - global

   DISCOUNT ) * 0.05 ) .

  end

  modify records

   TOTAL DUE := global RESTOTAL - ( global RESTOTAL *

   global DISCOUNT ) .

  run procedure RESERVATION INVOICE .

 end

If you are creating this script as you read, choose File>>Save to save the procedure on disk. When DataEase asks you to name the procedure, enter CALCULATE DISCOUNTS.