The DBOrderPromoADO component, which usually appears in the Order Adjust Price stage of an order processing pipeline, runs a query to retrieve data about price promotions from the database and then adjusts item prices accordingly.
The DBOrderPromoADO component executes a query to determine if the promotional conditions defined in a database table have been met by items in the OrderForm. If they have, DBOrderPromoADO applies the promotion to the OrderForm by initializing the item._oadjust_adjustedprice and item._n_unadjusted name/value pairs for the item affected by the promotion.
The item._oadjust_adjustedprice name/value pair reflects the total cost of a given item, adjusted for promotions. The item._n_unadjusted name/value pair contains the number of items that were not affected by the promotion.
Suppose, for example, that you define a promotion in which a customer who purchases one of Item A gets one of Item B at half-price. Suppose further that the following conditions exist.
When the DBOrderPromoADO component is run, the user will, by the terms of the promotion, get two of the items at the regular price of one dollar. However, the customer will get the third item at fifty percent off. Thus, the item._oadjust_adjustedprice for Item B will equal 2.50 cents.
In addition, because only one of the three Item B products will have been promotionally adjusted, the item._n_nadjusted name/value pair will be initialized to two. This initialization is critical, because once an item has had its price adjusted, the price cannot be adjusted again.
In this example, one of Item B had its price adjusted. The other two Item B items did not. Consequently, no matter how many of the three items qualify for a promotional adjustment under a later DBOrderPromoADO component, only the two previously unadjusted Item B items can be adjusted.
For detailed information about using this component, see Price Promotions.
Because the DBOrderPromoADO component expects the query that you supply to reference a specific group of table columns, the table in which you store promotions must have a given structure.
The database query must return at least the following columns:
cond_column. Contains the name of the item attribute against which the promotion condition is checked. For example, in the Volcano Coffee sample site’s vc30_promo_price table, this column contains the value _product_pfid, indicating that this name/value pair on the item Dictionary will be evaluated for each item in the items list.
cond_op. A comparison operator that compares cond_column to cond_value; for example = or <>.
cond_value. A value that identifies the item being promoted. For example, in the Volcano Coffee site, the cond_column is _product_pfid, the cond_op is “=” and the cond_value is 22. If an item in the items list has a _product_pfid that evaluates to 22, that item meets the condition set.
If this value is a number, it must be an integer value; floating point numbers (for example, 10.0) are not accepted.
cond_all. Optional field. If set to 1 (True), any product in the set of products to be purchased will trigger this promotion. Otherwise, this promotion will be triggered only by products that meet the criteria defined in cond_column, cond_op, and cond_value (the condition set). Products are removed from the condition and award sets after they have been involved in a promotion to prevent a single product from being a participant in multiple promotions.
award_column. The award_column, coupled with the award_op and award_value, define the award set for the promotion. The award column contains a value indicating the name/value pair that defines the award criteria.
For example, in the Volcano Coffee sample site’s vc30_promo_price table, this column contains the value _product_dept_id, the award_op column contains “=”, and the award_value column contains 2.
This means that if the condition set is met, then _product_dept_id is evaluated for each item in the items list. If a product in the items list contains a _product_dept_id name/value pair with a value of 2, then the promotion applies to that item.
award_op. Comparison operator that compares award_column with award_value; for example = or <>.
award_value. Indicates the value of the element referenced by award_column.
award_all. Optional field. If set to one (True), then the award can be applied to any product. Otherwise, this award will be applied only to products that meet the criteria defined in award_column, award_op, and award_value (the award set).
shopper_column. Value indicating the customer-specific information; can be @ (a wildcard value) indicating all customers are eligible for the promotion.
shopper_op. Should be set to = if a customer is specified; otherwise, it is @.
shopper_value. Value indicating the type of customer; if it is any customer, set it to @.
shopper_all. Optional field indicating whether this promotion is available to all customers. If this value is 0 (False), the promotion is available only to customers who meet the criteria defined in shopper_column, shopper_op, and shopper_value.
cond_min. A value, in cents (or other currency unit), or a number of units of the minimum purchase necessary to be eligible for the promotion. The interpretation of cond_min depends upon the value referenced in cond_basis. For example, if cond_basis contains “P”, cond_min is assumed to measure price. If cond_basis contains “Q”, cond_min is assumed to measure quantity.
cond_basis. Value indicating whether cond_min is measured in price or quantity (P or Q, respectively).
award_max. Size of the award set.
disjoint_cond_award. A number (1 or 0) indicating whether the award can be disjointed. If the value is 0, the award is disjointed; otherwise, it is not. The term disjointed, in this context, refers to whether or not an item can promote itself. Where the condition set and the award set do not refer to the same item, this value is ignored.
Suppose, for example, that you have a promotion that dictates that if you purchase one of Item A, you get one of Item A at fifty percent off.
If the award is not disjointed (disjoint_cond_award is equal to 0), then a customer needs only to purchase one of Item A to get that item at fifty percent off. If the award is disjointed (disjoint_cond_award is equal to 1), then a customer purchasing Item A will get a second Item A at fifty percent off, but will receive no discount until the second item is added to the basket.
disc_value. Number indicating the amount of the discount.
disc_type. Type of discount, either % or $.
date_start. Optional field. The date the promotion begins, at one second after midnight.
date_end. Optional field. The date the promotion ends, at one second after midnight. Because date_start and date_end are actually date/time pairs that specify midnight, date_end excludes the date specified.
Connection String. The data source name (DSN) connection string to be used to connect to the database.
An order promotion has three criteria: the customer criterion, a condition criterion, and an award criterion. If the customer does not match the customer criterion, the promotion is not applied. If the customer does match the customer criterion, then the other two criteria are applied to all the items in the order, producing two sets: the condition set (those matching the condition criterion) and the award set (those matching the award criterion).