DBOrderPromoADO
Use this component to run a query to retrieve data about price promotions from the database and adjust item prices accordingly.
Intended use: Order Processing pipeline, Order Adjust Price stage.
Note
- The DBOrderPromoADO component should not be used for future development. The discount features of the Content Selection pipeline provide a more powerful and general mechanism for applying product discounts. For more information, see Discount Objects.
Configuration Values
You can determine where the DBOrderPromoADO component retrieves the document and where it writes the result by setting component properties. Use the following boxes on the DB Order Promo (ADO) tab of the Component Properties dialog box to determine this information.
Box | Description |
Query | Type either the full SQL text of the query executed by this component or the name of a query whose full SQL text is defined in the Global.asa file. |
Connection string | Type the connection string to connect to the database. This may be a full data source name (DSN) or a name in a connection map. If blank, the default connection string is used. |
Values Read
The DBOrderPromoADO component reads the following values from the indicated dictionaries.
Key | Dictionary | Description |
item._iadjust_currentprice |
Order | The current price of the item before promotion is applied. |
item._product_* |
Order | Optional. |
_shopper_* |
Order | Optional. |
item._n_unadjusted |
Order | The quantity of this item that have not been discounted. |
item.quantity |
Order | The quantity of the item ordered. |
DefaultConnectionString |
Context | Optional. The key in the Application dictionary containing the default query string. |
ConnectionStringMap |
Context | Optional. The map in which to look up the name of a mapped connection string. |
QueryMap |
Context | Optional. A reference to a dictionary containing a query map. |
Values Written
The DBOrderPromoADO component writes the following values to the Order dictionary.
Key | Description |
item._oadjust_adjustedprice |
The total cost for an item. May have been adjusted by the application of a promotion. |
item._n_unadjusted |
The quantity of this item that have not been discounted. May have been adjusted by the application of a promotion. |
Remarks
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, the DBOrderPromoADO component applies the promotion to the OrderForm object by initializing item.
_oadjust
_adjustedprice
and item._n_unadjusted
keys for the item affected by the promotion.
The item.
_oadjust
_adjustedprice
key reflects the total cost of a given item, adjusted for promotions. The item.
_n
_unadjusted
key contains the number of items that were not affected by the promotion.
Suppose, for example, that you define a promotion in which a user who purchases one of Item A gets one of Item B at half-price. Suppose further that the following conditions exist:
The user has purchased one of Item A.
The user has purchased three of Item B.
Item B has a regular price of 1.00.
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 1.00. However, the user will get the third item at fifty percent off. Thus, the item.
_oadjust
_adjustedprice
key for Item B will be 2.50.
In addition, because only one of the three Item B products will have been promotionally adjusted, the item.
_n
_unadjusted
key will be set to two. This 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.
The Promotions Table
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:
Database Column | Description |
cond_column | This column contains the name of the item attribute against which the promotion condition is checked. |
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, if the cond_column is _product_pfid, the cond_op is "=" and the cond_value is 22; then an item in the items list with a _product_pfid that evaluates to 22 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. 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 columns, define the award set for the promotion. The award column contains a value indicating the key and value that defines the award criteria. |
award_op | A comparison operator that compares award_column with award_value; for example = or <>. |
award_value | This column indicates the value of the element referenced by the award_column column. |
award_all | Optional. If set to 1 (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 the award_column, award_op, and award_value columns (the award set). |
shopper_column | A value indicating the user-specific information. This can be a wildcard value (@) that indicates all users are eligible for the promotion. |
shopper_op | This column should be set to = if a user is specified; otherwise, it is a wildcard value (@). |
shopper_value | A value indicating the type of user; if it is any user, set it to a wildcard value (@). |
shopper_all | Optional. This column indicates whether this promotion is available to all users. If this value is 0 (False), the promotion is available only to users who meet the criteria defined in the shopper_column, shopper_op, and shopper_value columns. |
cond_min | This column contains a value, in cents (or other currency unit), or the number of units of the minimum purchase necessary to be eligible for the promotion. The interpretation of the cond_min column depends on the value referenced in the cond_basis column. For example, if the cond_basis column contains "P", the cond_min column is assumed to measure price. If the cond_basis column contains "Q", the cond_min column is assumed to measure quantity. |
cond_basis | A value indicating whether the cond_min column is measured in price or quantity (P or Q, respectively). |
award_max | The size of the award set. |
disjoint_cond_award | The column contains 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 (the column disjoint_cond_award is equal to 0), then a user needs to purchase only one of Item A to get that item at fifty percent off. If the award is disjointed (the column disjoint_cond_award is equal to 1), then a user 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 | A number indicating the amount of the discount. |
disc_type | A type of discount, either a percent (%) or a currency ($) symbol. |
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 the date_start and date_end columns are actually date-time pairs that specify midnight, the date_end column excludes the date specified. |
Connection String | The DSN connection string to be used to connect to the database. |
An order promotion has three criteria: the user criterion, a condition criterion, and an award criterion. If the user does not match the user criterion, the promotion is not applied. If the user does match the user 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).