1/*
2 * Legal Notice
3 *
4 * This document and associated source code (the "Work") is a part of a
5 * benchmark specification maintained by the TPC.
6 *
7 * The TPC reserves all right, title, and interest to the Work as provided
8 * under U.S. and international laws, including without limitation all patent
9 * and trademark rights therein.
10 *
11 * No Warranty
12 *
13 * 1.1 TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, THE INFORMATION
14 * CONTAINED HEREIN IS PROVIDED "AS IS" AND WITH ALL FAULTS, AND THE
15 * AUTHORS AND DEVELOPERS OF THE WORK HEREBY DISCLAIM ALL OTHER
16 * WARRANTIES AND CONDITIONS, EITHER EXPRESS, IMPLIED OR STATUTORY,
17 * INCLUDING, BUT NOT LIMITED TO, ANY (IF ANY) IMPLIED WARRANTIES,
18 * DUTIES OR CONDITIONS OF MERCHANTABILITY, OF FITNESS FOR A PARTICULAR
19 * PURPOSE, OF ACCURACY OR COMPLETENESS OF RESPONSES, OF RESULTS, OF
20 * WORKMANLIKE EFFORT, OF LACK OF VIRUSES, AND OF LACK OF NEGLIGENCE.
21 * ALSO, THERE IS NO WARRANTY OR CONDITION OF TITLE, QUIET ENJOYMENT,
22 * QUIET POSSESSION, CORRESPONDENCE TO DESCRIPTION OR NON-INFRINGEMENT
23 * WITH REGARD TO THE WORK.
24 * 1.2 IN NO EVENT WILL ANY AUTHOR OR DEVELOPER OF THE WORK BE LIABLE TO
25 * ANY OTHER PARTY FOR ANY DAMAGES, INCLUDING BUT NOT LIMITED TO THE
26 * COST OF PROCURING SUBSTITUTE GOODS OR SERVICES, LOST PROFITS, LOSS
27 * OF USE, LOSS OF DATA, OR ANY INCIDENTAL, CONSEQUENTIAL, DIRECT,
28 * INDIRECT, OR SPECIAL DAMAGES WHETHER UNDER CONTRACT, TORT, WARRANTY,
29 * OR OTHERWISE, ARISING IN ANY WAY OUT OF THIS OR ANY OTHER AGREEMENT
30 * RELATING TO THE WORK, WHETHER OR NOT SUCH AUTHOR OR DEVELOPER HAD
31 * ADVANCE NOTICE OF THE POSSIBILITY OF SUCH DAMAGES.
32 *
33 * Contributors:
34 * Gradient Systems
35 */
36#include "config.h"
37#include "porting.h"
38#include <stdio.h>
39#include "w_catalog_sales.h"
40#include "w_catalog_returns.h"
41#include "decimal.h"
42#include "date.h"
43#include "genrand.h"
44#include "columns.h"
45#include "tables.h"
46#include "constants.h"
47#include "build_support.h"
48#include "nulls.h"
49#include "tdefs.h"
50#include "scaling.h"
51#include "permute.h"
52#include "params.h"
53#include "parallel.h"
54#include "scd.h"
55
56#include "append_info.h"
57
58struct W_CATALOG_SALES_TBL g_w_catalog_sales;
59ds_key_t skipDays(int nTable, ds_key_t *pRemainder);
60
61static ds_key_t kNewDateIndex = 0;
62static ds_key_t jDate;
63static int nTicketItemBase = 1;
64static int *pItemPermutation;
65static int nItemCount;
66
67/*
68 * the validation process requires generating a single lineitem
69 * so the main mk_xxx routine has been split into a master record portion
70 * and a detail/lineitem portion.
71 */
72static void mk_master(void *info_arr, ds_key_t index) {
73 static decimal_t dZero, dHundred, dOne, dOneHalf;
74 int nGiftPct;
75 struct W_CATALOG_SALES_TBL *r;
76 static int bInit = 0;
77
78 r = &g_w_catalog_sales;
79
80 if (!bInit) {
81 strtodec(&dZero, "0.00");
82 strtodec(&dHundred, "100.00");
83 strtodec(&dOne, "1.00");
84 strtodec(&dOneHalf, "0.50");
85 jDate = skipDays(CATALOG_SALES, &kNewDateIndex);
86 pItemPermutation = makePermutation(NULL, (nItemCount = (int)getIDCount(ITEM)), CS_PERMUTE);
87
88 bInit = 1;
89 }
90
91 while (index > kNewDateIndex) /* need to move to a new date */
92 {
93 jDate += 1;
94 kNewDateIndex += dateScaling(CATALOG_SALES, jDate);
95 }
96
97 /***
98 * some attributes remain the same for each lineitem in an order; others are
99 * different for each lineitem.
100 *
101 * Parallel generation causes another problem, since the values that get
102 * seeded may come from a prior row. If we are seeding at the start of a
103 * parallel chunk, hunt backwards in the RNG stream to find the most recent
104 * values that were used to set the values of the orderline-invariant
105 * columns
106 */
107
108 r->cs_sold_date_sk = jDate;
109 r->cs_sold_time_sk = mk_join(CS_SOLD_TIME_SK, TIME, r->cs_call_center_sk);
110 r->cs_call_center_sk =
111 (r->cs_sold_date_sk == -1) ? -1 : mk_join(CS_CALL_CENTER_SK, CALL_CENTER, r->cs_sold_date_sk);
112
113 r->cs_bill_customer_sk = mk_join(CS_BILL_CUSTOMER_SK, CUSTOMER, 1);
114 r->cs_bill_cdemo_sk = mk_join(CS_BILL_CDEMO_SK, CUSTOMER_DEMOGRAPHICS, 1);
115 r->cs_bill_hdemo_sk = mk_join(CS_BILL_HDEMO_SK, HOUSEHOLD_DEMOGRAPHICS, 1);
116 r->cs_bill_addr_sk = mk_join(CS_BILL_ADDR_SK, CUSTOMER_ADDRESS, 1);
117
118 /* most orders are for the ordering customers, some are not */
119 genrand_integer(&nGiftPct, DIST_UNIFORM, 0, 99, 0, CS_SHIP_CUSTOMER_SK);
120 if (nGiftPct <= CS_GIFT_PCT) {
121 r->cs_ship_customer_sk = mk_join(CS_SHIP_CUSTOMER_SK, CUSTOMER, 2);
122 r->cs_ship_cdemo_sk = mk_join(CS_SHIP_CDEMO_SK, CUSTOMER_DEMOGRAPHICS, 2);
123 r->cs_ship_hdemo_sk = mk_join(CS_SHIP_HDEMO_SK, HOUSEHOLD_DEMOGRAPHICS, 2);
124 r->cs_ship_addr_sk = mk_join(CS_SHIP_ADDR_SK, CUSTOMER_ADDRESS, 2);
125 } else {
126 r->cs_ship_customer_sk = r->cs_bill_customer_sk;
127 r->cs_ship_cdemo_sk = r->cs_bill_cdemo_sk;
128 r->cs_ship_hdemo_sk = r->cs_bill_hdemo_sk;
129 r->cs_ship_addr_sk = r->cs_bill_addr_sk;
130 }
131
132 r->cs_order_number = index;
133 genrand_integer(&nTicketItemBase, DIST_UNIFORM, 1, nItemCount, 0, CS_SOLD_ITEM_SK);
134
135 return;
136}
137
138static void mk_detail(void *info_arr, int bPrint) {
139 static decimal_t dZero, dHundred, dOne, dOneHalf;
140 int nShipLag, nTemp;
141 ds_key_t kItem;
142 static ds_key_t kNewDateIndex = 0;
143 static ds_key_t jDate;
144 struct W_CATALOG_SALES_TBL *r;
145 static int bInit = 0;
146 tdef *pTdef = getSimpleTdefsByNumber(CATALOG_SALES);
147
148 r = &g_w_catalog_sales;
149
150 if (!bInit) {
151 strtodec(&dZero, "0.00");
152 strtodec(&dHundred, "100.00");
153 strtodec(&dOne, "1.00");
154 strtodec(&dOneHalf, "0.50");
155 jDate = skipDays(CATALOG_SALES, &kNewDateIndex);
156
157 bInit = 1;
158 }
159
160 nullSet(&pTdef->kNullBitMap, CS_NULLS);
161
162 /* orders are shipped some number of days after they are ordered */
163 genrand_integer(&nShipLag, DIST_UNIFORM, CS_MIN_SHIP_DELAY, CS_MAX_SHIP_DELAY, 0, CS_SHIP_DATE_SK);
164 r->cs_ship_date_sk = (r->cs_sold_date_sk == -1) ? -1 : r->cs_sold_date_sk + nShipLag;
165
166 /*
167 * items need to be unique within an order
168 * use a sequence within the permutation
169 * NB: Permutations are 1-based
170 */
171 if (++nTicketItemBase > nItemCount)
172 nTicketItemBase = 1;
173 kItem = getPermutationEntry(pItemPermutation, nTicketItemBase);
174 r->cs_sold_item_sk = matchSCDSK(kItem, r->cs_sold_date_sk, ITEM);
175
176 /* catalog page needs to be from a catlog active at the time of the sale */
177 r->cs_catalog_page_sk =
178 (r->cs_sold_date_sk == -1) ? -1 : mk_join(CS_CATALOG_PAGE_SK, CATALOG_PAGE, r->cs_sold_date_sk);
179
180 r->cs_ship_mode_sk = mk_join(CS_SHIP_MODE_SK, SHIP_MODE, 1);
181 r->cs_warehouse_sk = mk_join(CS_WAREHOUSE_SK, WAREHOUSE, 1);
182 r->cs_promo_sk = mk_join(CS_PROMO_SK, PROMOTION, 1);
183 set_pricing(CS_PRICING, &r->cs_pricing);
184
185 /**
186 * having gone to the trouble to make the sale, now let's see if it gets
187 * returned
188 */
189 genrand_integer(&nTemp, DIST_UNIFORM, 0, 99, 0, CR_IS_RETURNED);
190 if (nTemp < CR_RETURN_PCT) {
191 struct W_CATALOG_RETURNS_TBL w_catalog_returns;
192 struct W_CATALOG_RETURNS_TBL *rr = &w_catalog_returns;
193 mk_w_catalog_returns(rr, 1);
194
195 void *info = append_info_get(info_arr, CATALOG_RETURNS);
196 append_row_start(info);
197
198 append_key(info, rr->cr_returned_date_sk);
199 append_key(info, rr->cr_returned_time_sk);
200 append_key(info, rr->cr_item_sk);
201 append_key(info, rr->cr_refunded_customer_sk);
202 append_key(info, rr->cr_refunded_cdemo_sk);
203 append_key(info, rr->cr_refunded_hdemo_sk);
204 append_key(info, rr->cr_refunded_addr_sk);
205 append_key(info, rr->cr_returning_customer_sk);
206 append_key(info, rr->cr_returning_cdemo_sk);
207 append_key(info, rr->cr_returning_hdemo_sk);
208 append_key(info, rr->cr_returning_addr_sk);
209 append_key(info, rr->cr_call_center_sk);
210 append_key(info, rr->cr_catalog_page_sk);
211 append_key(info, rr->cr_ship_mode_sk);
212 append_key(info, rr->cr_warehouse_sk);
213 append_key(info, rr->cr_reason_sk);
214 append_key(info, rr->cr_order_number);
215 append_integer(info, rr->cr_pricing.quantity);
216 append_decimal(info, &rr->cr_pricing.net_paid);
217 append_decimal(info, &rr->cr_pricing.ext_tax);
218 append_decimal(info, &rr->cr_pricing.net_paid_inc_tax);
219 append_decimal(info, &rr->cr_pricing.fee);
220 append_decimal(info, &rr->cr_pricing.ext_ship_cost);
221 append_decimal(info, &rr->cr_pricing.refunded_cash);
222 append_decimal(info, &rr->cr_pricing.reversed_charge);
223 append_decimal(info, &rr->cr_pricing.store_credit);
224 append_decimal(info, &rr->cr_pricing.net_loss);
225
226 append_row_end(info);
227 }
228
229 void *info = append_info_get(info_arr, CATALOG_SALES);
230 append_row_start(info);
231
232 append_key(info, r->cs_sold_date_sk);
233 append_key(info, r->cs_sold_time_sk);
234 append_key(info, r->cs_ship_date_sk);
235 append_key(info, r->cs_bill_customer_sk);
236 append_key(info, r->cs_bill_cdemo_sk);
237 append_key(info, r->cs_bill_hdemo_sk);
238 append_key(info, r->cs_bill_addr_sk);
239 append_key(info, r->cs_ship_customer_sk);
240 append_key(info, r->cs_ship_cdemo_sk);
241 append_key(info, r->cs_ship_hdemo_sk);
242 append_key(info, r->cs_ship_addr_sk);
243 append_key(info, r->cs_call_center_sk);
244 append_key(info, r->cs_catalog_page_sk);
245 append_key(info, r->cs_ship_mode_sk);
246 append_key(info, r->cs_warehouse_sk);
247 append_key(info, r->cs_sold_item_sk);
248 append_key(info, r->cs_promo_sk);
249 append_key(info, r->cs_order_number);
250 append_integer(info, r->cs_pricing.quantity);
251 append_decimal(info, &r->cs_pricing.wholesale_cost);
252 append_decimal(info, &r->cs_pricing.list_price);
253 append_decimal(info, &r->cs_pricing.sales_price);
254 append_decimal(info, &r->cs_pricing.ext_discount_amt);
255 append_decimal(info, &r->cs_pricing.ext_sales_price);
256 append_decimal(info, &r->cs_pricing.ext_wholesale_cost);
257 append_decimal(info, &r->cs_pricing.ext_list_price);
258 append_decimal(info, &r->cs_pricing.ext_tax);
259 append_decimal(info, &r->cs_pricing.coupon_amt);
260 append_decimal(info, &r->cs_pricing.ext_ship_cost);
261 append_decimal(info, &r->cs_pricing.net_paid);
262 append_decimal(info, &r->cs_pricing.net_paid_inc_tax);
263 append_decimal(info, &r->cs_pricing.net_paid_inc_ship);
264 append_decimal(info, &r->cs_pricing.net_paid_inc_ship_tax);
265 append_decimal(info, &r->cs_pricing.net_profit);
266
267 append_row_end(info);
268
269 return;
270}
271
272/*
273 * Routine: mk_catalog_sales()
274 * Purpose: build rows for the catalog sales table
275 * Algorithm:
276 * Data Structures:
277 *
278 * Params:
279 * Returns:
280 * Called By:
281 * Calls:
282 * Assumptions:
283 * Side Effects:
284 * TODO:
285 * 20020902 jms Need to link order date/time to call center record
286 * 20020902 jms Should promos be tied to item id?
287 */
288int mk_w_catalog_sales(void *info_arr, ds_key_t index) {
289 int nLineitems, i;
290
291 mk_master(info_arr, index);
292
293 /*
294 * now we select the number of lineitems in this order, and loop through
295 * them, printing as we go
296 */
297 genrand_integer(&nLineitems, DIST_UNIFORM, 4, 14, 0, CS_ORDER_NUMBER);
298 for (i = 1; i <= nLineitems; i++) {
299 mk_detail(info_arr, 1);
300 }
301
302 /**
303 * and finally return 1 since we have already printed the rows.
304 */
305 return 0;
306}
307
308/*
309 * Routine:
310 * Purpose:
311 * Algorithm:
312 * Data Structures:
313 *
314 * Params:
315 * Returns:
316 * Called By:
317 * Calls:
318 * Assumptions:
319 * Side Effects:
320 * TODO: None
321 */
322int vld_w_catalog_sales(int nTable, ds_key_t kRow, int *Permutation) {
323 int nLineitem, nMaxLineitem, i;
324
325 row_skip(nTable, kRow - 1);
326 row_skip(CATALOG_RETURNS, (kRow - 1));
327 jDate = skipDays(CATALOG_SALES, &kNewDateIndex);
328 mk_master(NULL, kRow);
329 genrand_integer(&nMaxLineitem, DIST_UNIFORM, 4, 14, 9, CS_ORDER_NUMBER);
330 genrand_integer(&nLineitem, DIST_UNIFORM, 1, nMaxLineitem, 0, CS_PRICING_QUANTITY);
331 for (i = 1; i < nLineitem; i++) {
332 mk_detail(NULL, 0);
333 }
334 mk_detail(NULL, 1);
335
336 return (0);
337}
338