| Line 66... |
Line 66... |
| 66 |
<modified_date>2011/06/23 16:31:56.870</modified_date>
|
66 |
<modified_date>2011/06/23 16:31:56.870</modified_date>
|
| 67 |
</info>
|
67 |
</info>
|
| 68 |
<notepads>
|
68 |
<notepads>
|
| 69 |
</notepads>
|
69 |
</notepads>
|
| 70 |
<connection>
|
70 |
<connection>
|
| - |
|
71 |
<name>sales_DClocal</name>
|
| - |
|
72 |
<server>localhost</server>
|
| - |
|
73 |
<type>MYSQL</type>
|
| - |
|
74 |
<access>Native</access>
|
| - |
|
75 |
<database>sales</database>
|
| - |
|
76 |
<port>3306</port>
|
| - |
|
77 |
<username>root</username>
|
| - |
|
78 |
<password>Encrypted 2be98afc86aa7f2e4b811a1608cc2fd8a</password>
|
| - |
|
79 |
<servername/>
|
| - |
|
80 |
<data_tablespace/>
|
| - |
|
81 |
<index_tablespace/>
|
| - |
|
82 |
<attributes>
|
| - |
|
83 |
<attribute><code>EXTRA_OPTION_MYSQL.defaultFetchSize</code><attribute>500</attribute></attribute>
|
| - |
|
84 |
<attribute><code>EXTRA_OPTION_MYSQL.useCursorFetch</code><attribute>true</attribute></attribute>
|
| - |
|
85 |
<attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
|
| - |
|
86 |
<attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute>
|
| - |
|
87 |
<attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute>
|
| - |
|
88 |
<attribute><code>PORT_NUMBER</code><attribute>3306</attribute></attribute>
|
| - |
|
89 |
<attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute>
|
| - |
|
90 |
<attribute><code>STREAM_RESULTS</code><attribute>Y</attribute></attribute>
|
| - |
|
91 |
<attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>N</attribute></attribute>
|
| - |
|
92 |
<attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
|
| - |
|
93 |
</attributes>
|
| - |
|
94 |
</connection>
|
| - |
|
95 |
<connection>
|
| 71 |
<name>salesDC_DW</name>
|
96 |
<name>salesDC_DW</name>
|
| 72 |
<server>localhost</server>
|
97 |
<server>localhost</server>
|
| 73 |
<type>MYSQL</type>
|
98 |
<type>MYSQL</type>
|
| 74 |
<access>Native</access>
|
99 |
<access>Native</access>
|
| 75 |
<database>sales</database>
|
100 |
<database>sales</database>
|
| Line 117... |
Line 142... |
| 117 |
<attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
|
142 |
<attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
|
| 118 |
</attributes>
|
143 |
</attributes>
|
| 119 |
</connection>
|
144 |
</connection>
|
| 120 |
<connection>
|
145 |
<connection>
|
| 121 |
<name>transactionDC_Prod</name>
|
146 |
<name>transactionDC_Prod</name>
|
| 122 |
<server>192.168.190.114</server>
|
147 |
<server>192.168.190.113</server>
|
| 123 |
<type>MYSQL</type>
|
148 |
<type>MYSQL</type>
|
| 124 |
<access>Native</access>
|
149 |
<access>Native</access>
|
| 125 |
<database>transaction</database>
|
150 |
<database>transaction</database>
|
| 126 |
<port>3306</port>
|
151 |
<port>3306</port>
|
| 127 |
<username>pentaho</username>
|
152 |
<username>pentaho</username>
|
| Line 159... |
Line 184... |
| 159 |
o.status 'order_status', o.total_amount,
|
184 |
o.status 'order_status', o.total_amount,
|
| 160 |
datediff(o.created_timestamp, '20101231') as date_id, hour(o.created_timestamp) as order_hour,
|
185 |
datediff(o.created_timestamp, '20101231') as date_id, hour(o.created_timestamp) as order_hour,
|
| 161 |
o.transaction_id, l.item_id, l.quantity,
|
186 |
o.transaction_id, l.item_id, l.quantity,
|
| 162 |
case when unit_price<2000 then 0 when unit_price < 5000 then 1 when unit_price < 10000 then 2 when unit_price < 15000 then 3 when unit_price <20000 then 4 when unit_price < 50000 then 5 else 6 end as price_range,
|
187 |
case when unit_price<2000 then 0 when unit_price < 5000 then 1 when unit_price < 10000 then 2 when unit_price < 15000 then 3 when unit_price <20000 then 4 when unit_price < 50000 then 5 else 6 end as price_range,
|
| 163 |
l.transfer_price, p.gatewayId,
|
188 |
l.transfer_price, p.gatewayId,
|
| 164 |
case when pa.value is null then case when p.gatewayId = 1 then 2099 when p.gatewayId = 2 then 1099 else null end when (pa.value = 3000 and p.gatewayId = 4 and (o.cod is null or o.cod = 0)) then 3001 else pa.value end 'payment_method',
|
189 |
case when pa.value is null then case when p.gatewayId = 1 then 2099 when p.gatewayId = 2 then 1099 else null end else pa.value end 'payment_method',
|
| 165 |
upper(trim(t.coupon_code)) as coupon_code,
|
190 |
upper(trim(t.coupon_code)) as coupon_code,
|
| 166 |
case when t.session_source is null then 'Unknown' when t.session_source like 'DIRECT%' then 'Direct' when t.session_source like 'ORGANIC%' then 'Organic' when t.session_source like 'PAID%' then 'Paid' else 'Referer' end as session_source_type,
|
191 |
case when t.session_source is null then 'Unknown' when t.session_source like 'DIRECT%' then 'Direct' when t.session_source like 'ORGANIC%' then 'Organic' when t.session_source like 'PAID%' then 'Paid' else 'Referer' end as session_source_type,
|
| 167 |
SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTR(session_source, LOCATE('http://', LCASE(session_source)), LENGTH(session_source)), 'http://', ''), 'www.', ''), '/', 1) as session_source_url,
|
192 |
SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTR(session_source, LOCATE('http://', LCASE(session_source)), LENGTH(session_source)), 'http://', ''), 'www.', ''), '/', 1) as session_source_url,
|
| 168 |
case when datediff(t.session_start_time, u.source_start_time) > 30 then 0 when datediff(t.session_start_time, u.source_start_time) > 15 then 1
|
193 |
case when datediff(t.session_start_time, u.source_start_time) > 30 then 0 when datediff(t.session_start_time, u.source_start_time) > 15 then 1
|
| 169 |
when datediff(t.session_start_time, u.source_start_time) > 7 then 2 when datediff(t.session_start_time, u.source_start_time) > 2 then 3
|
194 |
when datediff(t.session_start_time, u.source_start_time) > 7 then 2 when datediff(t.session_start_time, u.source_start_time) > 2 then 3
|
| 170 |
when datediff(t.session_start_time, u.source_start_time) > 1 then 4 else 5 end as days_to_purchase,
|
195 |
when datediff(t.session_start_time, u.source_start_time) > 1 then 4 else 5 end as days_to_purchase,
|
| 171 |
aff.name aff_name
|
196 |
aff.name aff_name,
|
| - |
|
197 |
case when o.originalOrderId is null then datediff(o.created_timestamp, '20101231') else datediff((select created_timestamp from `order` where id = o.originalOrderId), '20101231') end as booking_date_id
|
| 172 |
FROM `order` o
|
198 |
FROM `order` o
|
| 173 |
left join lineitem l on(o.id = l.order_id )
|
199 |
left join lineitem l on(o.id = l.order_id )
|
| 174 |
left join (select * from payment.payment order by id desc) p on (o.transaction_id = p.merchantTxnId)
|
200 |
left join (select * from payment.payment order by id desc) p on (o.transaction_id = p.merchantTxnId)
|
| 175 |
left join payment.paymentattribute pa on (p.id = pa.payment_id and pa.name = 'payMethod')
|
201 |
left join payment.paymentattribute pa on (p.id = pa.payment_id and pa.name = 'payMethod')
|
| 176 |
left join transaction t on (o.transaction_id = t.id)
|
202 |
left join transaction t on (o.transaction_id = t.id)
|
| Line 200... |
Line 226... |
| 200 |
<partitioning>
|
226 |
<partitioning>
|
| 201 |
<method>none</method>
|
227 |
<method>none</method>
|
| 202 |
<schema_name/>
|
228 |
<schema_name/>
|
| 203 |
</partitioning>
|
229 |
</partitioning>
|
| 204 |
<fields> <field> <name>order_id</name>
|
230 |
<fields> <field> <name>order_id</name>
|
| 205 |
<rename>order_id</rename>
|
231 |
<rename/>
|
| 206 |
<length>-2</length>
|
232 |
<length>-2</length>
|
| 207 |
<precision>-2</precision>
|
233 |
<precision>-2</precision>
|
| 208 |
</field> <field> <name>customer_id</name>
|
234 |
</field> <field> <name>customer_id</name>
|
| - |
|
235 |
<rename/>
|
| - |
|
236 |
<length>-2</length>
|
| - |
|
237 |
<precision>-2</precision>
|
| - |
|
238 |
</field> <field> <name>customer_pincode</name>
|
| 209 |
<rename>customer_id</rename>
|
239 |
<rename>pincode</rename>
|
| - |
|
240 |
<length>-2</length>
|
| - |
|
241 |
<precision>-2</precision>
|
| - |
|
242 |
</field> <field> <name>order_status</name>
|
| - |
|
243 |
<rename>status</rename>
|
| 210 |
<length>-2</length>
|
244 |
<length>-2</length>
|
| 211 |
<precision>-2</precision>
|
245 |
<precision>-2</precision>
|
| 212 |
</field> <field> <name>total_amount</name>
|
246 |
</field> <field> <name>total_amount</name>
|
| 213 |
<rename>total_amount</rename>
|
247 |
<rename/>
|
| 214 |
<length>-2</length>
|
248 |
<length>-2</length>
|
| 215 |
<precision>-2</precision>
|
249 |
<precision>-2</precision>
|
| 216 |
</field> <field> <name>date_id</name>
|
250 |
</field> <field> <name>date_id</name>
|
| 217 |
<rename>date_id</rename>
|
251 |
<rename/>
|
| 218 |
<length>-2</length>
|
252 |
<length>-2</length>
|
| 219 |
<precision>-2</precision>
|
253 |
<precision>-2</precision>
|
| 220 |
</field> <field> <name>order_hour</name>
|
254 |
</field> <field> <name>order_hour</name>
|
| 221 |
<rename>order_hour</rename>
|
255 |
<rename/>
|
| 222 |
<length>-2</length>
|
256 |
<length>-2</length>
|
| 223 |
<precision>-2</precision>
|
257 |
<precision>-2</precision>
|
| 224 |
</field> <field> <name>transaction_id</name>
|
258 |
</field> <field> <name>transaction_id</name>
|
| 225 |
<rename>transaction_id</rename>
|
259 |
<rename/>
|
| 226 |
<length>-2</length>
|
260 |
<length>-2</length>
|
| 227 |
<precision>-2</precision>
|
261 |
<precision>-2</precision>
|
| 228 |
</field> <field> <name>item_id</name>
|
262 |
</field> <field> <name>item_id</name>
|
| 229 |
<rename>item_id</rename>
|
263 |
<rename/>
|
| 230 |
<length>-2</length>
|
264 |
<length>-2</length>
|
| 231 |
<precision>-2</precision>
|
265 |
<precision>-2</precision>
|
| 232 |
</field> <field> <name>quantity</name>
|
266 |
</field> <field> <name>quantity</name>
|
| - |
|
267 |
<rename/>
|
| 233 |
<rename>quantity</rename>
|
268 |
<length>-2</length>
|
| - |
|
269 |
<precision>-2</precision>
|
| - |
|
270 |
</field> <field> <name>price_range</name>
|
| - |
|
271 |
<rename/>
|
| 234 |
<length>-2</length>
|
272 |
<length>-2</length>
|
| 235 |
<precision>-2</precision>
|
273 |
<precision>-2</precision>
|
| 236 |
</field> <field> <name>transfer_price</name>
|
274 |
</field> <field> <name>transfer_price</name>
|
| 237 |
<rename>transfer_price</rename>
|
275 |
<rename/>
|
| 238 |
<length>-2</length>
|
276 |
<length>-2</length>
|
| 239 |
<precision>-2</precision>
|
277 |
<precision>-2</precision>
|
| 240 |
</field> <field> <name>gatewayId</name>
|
278 |
</field> <field> <name>gatewayId</name>
|
| 241 |
<rename>gatewayId</rename>
|
279 |
<rename/>
|
| 242 |
<length>-2</length>
|
280 |
<length>-2</length>
|
| 243 |
<precision>-2</precision>
|
281 |
<precision>-2</precision>
|
| 244 |
</field> <field> <name>payment_method</name>
|
282 |
</field> <field> <name>payment_method</name>
|
| 245 |
<rename>payment_method</rename>
|
283 |
<rename/>
|
| 246 |
<length>-2</length>
|
284 |
<length>-2</length>
|
| 247 |
<precision>-2</precision>
|
285 |
<precision>-2</precision>
|
| 248 |
</field> <field> <name>coupon_code</name>
|
286 |
</field> <field> <name>coupon_code</name>
|
| 249 |
<rename>coupon_code</rename>
|
- |
|
| 250 |
<length>-2</length>
|
- |
|
| 251 |
<precision>-2</precision>
|
- |
|
| 252 |
</field> <field> <name>customer_pincode</name>
|
- |
|
| 253 |
<rename>pincode</rename>
|
- |
|
| 254 |
<length>-2</length>
|
- |
|
| 255 |
<precision>-2</precision>
|
- |
|
| 256 |
</field> <field> <name>order_status</name>
|
- |
|
| 257 |
<rename>status</rename>
|
287 |
<rename/>
|
| 258 |
<length>-2</length>
|
- |
|
| 259 |
<precision>-2</precision>
|
- |
|
| 260 |
</field> <field> <name>aff_name</name>
|
- |
|
| 261 |
<rename>aff_name</rename>
|
- |
|
| 262 |
<length>-2</length>
|
288 |
<length>-2</length>
|
| 263 |
<precision>-2</precision>
|
289 |
<precision>-2</precision>
|
| 264 |
</field> <field> <name>session_source_type</name>
|
290 |
</field> <field> <name>session_source_type</name>
|
| 265 |
<rename>session_source_type</rename>
|
291 |
<rename/>
|
| 266 |
<length>-2</length>
|
292 |
<length>-2</length>
|
| 267 |
<precision>-2</precision>
|
293 |
<precision>-2</precision>
|
| 268 |
</field> <field> <name>session_source_url</name>
|
294 |
</field> <field> <name>session_source_url</name>
|
| 269 |
<rename>session_source_url</rename>
|
295 |
<rename/>
|
| 270 |
<length>-2</length>
|
296 |
<length>-2</length>
|
| 271 |
<precision>-2</precision>
|
297 |
<precision>-2</precision>
|
| 272 |
</field> <field> <name>price_range</name>
|
298 |
</field> <field> <name>days_to_purchase</name>
|
| 273 |
<rename/>
|
299 |
<rename/>
|
| 274 |
<length>-2</length>
|
300 |
<length>-2</length>
|
| 275 |
<precision>-2</precision>
|
301 |
<precision>-2</precision>
|
| 276 |
</field> <field> <name>days_to_purchase</name>
|
302 |
</field> <field> <name>aff_name</name>
|
| - |
|
303 |
<rename/>
|
| - |
|
304 |
<length>-2</length>
|
| - |
|
305 |
<precision>-2</precision>
|
| - |
|
306 |
</field> <field> <name>booking_date_id</name>
|
| 277 |
<rename/>
|
307 |
<rename/>
|
| 278 |
<length>-2</length>
|
308 |
<length>-2</length>
|
| 279 |
<precision>-2</precision>
|
309 |
<precision>-2</precision>
|
| 280 |
</field> <select_unspecified>N</select_unspecified>
|
310 |
</field> <select_unspecified>N</select_unspecified>
|
| 281 |
</fields> <cluster_schema/>
|
311 |
</fields> <cluster_schema/>
|
| Line 312... |
Line 342... |
| 312 |
<tablename_field/>
|
342 |
<tablename_field/>
|
| 313 |
<tablename_in_table>Y</tablename_in_table>
|
343 |
<tablename_in_table>Y</tablename_in_table>
|
| 314 |
<return_keys>N</return_keys>
|
344 |
<return_keys>N</return_keys>
|
| 315 |
<return_field/>
|
345 |
<return_field/>
|
| 316 |
<fields>
|
346 |
<fields>
|
| - |
|
347 |
<field>
|
| - |
|
348 |
<column_name>order_id</column_name>
|
| - |
|
349 |
<stream_name>order_id</stream_name>
|
| - |
|
350 |
</field>
|
| - |
|
351 |
<field>
|
| - |
|
352 |
<column_name>customer_id</column_name>
|
| - |
|
353 |
<stream_name>customer_id</stream_name>
|
| - |
|
354 |
</field>
|
| - |
|
355 |
<field>
|
| - |
|
356 |
<column_name>pincode</column_name>
|
| - |
|
357 |
<stream_name>pincode</stream_name>
|
| - |
|
358 |
</field>
|
| - |
|
359 |
<field>
|
| - |
|
360 |
<column_name>status</column_name>
|
| - |
|
361 |
<stream_name>status</stream_name>
|
| - |
|
362 |
</field>
|
| - |
|
363 |
<field>
|
| - |
|
364 |
<column_name>total_amount</column_name>
|
| - |
|
365 |
<stream_name>total_amount</stream_name>
|
| - |
|
366 |
</field>
|
| - |
|
367 |
<field>
|
| - |
|
368 |
<column_name>date_id</column_name>
|
| - |
|
369 |
<stream_name>date_id</stream_name>
|
| - |
|
370 |
</field>
|
| - |
|
371 |
<field>
|
| - |
|
372 |
<column_name>order_hour</column_name>
|
| - |
|
373 |
<stream_name>order_hour</stream_name>
|
| - |
|
374 |
</field>
|
| - |
|
375 |
<field>
|
| - |
|
376 |
<column_name>transaction_id</column_name>
|
| - |
|
377 |
<stream_name>transaction_id</stream_name>
|
| - |
|
378 |
</field>
|
| - |
|
379 |
<field>
|
| - |
|
380 |
<column_name>item_id</column_name>
|
| - |
|
381 |
<stream_name>item_id</stream_name>
|
| - |
|
382 |
</field>
|
| - |
|
383 |
<field>
|
| - |
|
384 |
<column_name>quantity</column_name>
|
| - |
|
385 |
<stream_name>quantity</stream_name>
|
| - |
|
386 |
</field>
|
| - |
|
387 |
<field>
|
| - |
|
388 |
<column_name>price_range</column_name>
|
| - |
|
389 |
<stream_name>price_range</stream_name>
|
| - |
|
390 |
</field>
|
| - |
|
391 |
<field>
|
| - |
|
392 |
<column_name>transfer_price</column_name>
|
| - |
|
393 |
<stream_name>transfer_price</stream_name>
|
| - |
|
394 |
</field>
|
| - |
|
395 |
<field>
|
| - |
|
396 |
<column_name>gatewayId</column_name>
|
| - |
|
397 |
<stream_name>gatewayId</stream_name>
|
| - |
|
398 |
</field>
|
| - |
|
399 |
<field>
|
| - |
|
400 |
<column_name>payment_method</column_name>
|
| - |
|
401 |
<stream_name>payment_method</stream_name>
|
| - |
|
402 |
</field>
|
| - |
|
403 |
<field>
|
| - |
|
404 |
<column_name>coupon_code</column_name>
|
| - |
|
405 |
<stream_name>coupon_code</stream_name>
|
| - |
|
406 |
</field>
|
| - |
|
407 |
<field>
|
| - |
|
408 |
<column_name>session_source_type</column_name>
|
| - |
|
409 |
<stream_name>session_source_type</stream_name>
|
| - |
|
410 |
</field>
|
| - |
|
411 |
<field>
|
| - |
|
412 |
<column_name>session_source_url</column_name>
|
| - |
|
413 |
<stream_name>session_source_url</stream_name>
|
| - |
|
414 |
</field>
|
| - |
|
415 |
<field>
|
| - |
|
416 |
<column_name>days_to_purchase</column_name>
|
| - |
|
417 |
<stream_name>days_to_purchase</stream_name>
|
| - |
|
418 |
</field>
|
| - |
|
419 |
<field>
|
| - |
|
420 |
<column_name>aff_name</column_name>
|
| - |
|
421 |
<stream_name>aff_name</stream_name>
|
| - |
|
422 |
</field>
|
| - |
|
423 |
<field>
|
| - |
|
424 |
<column_name>booking_date_id</column_name>
|
| - |
|
425 |
<stream_name>booking_date_id</stream_name>
|
| - |
|
426 |
</field>
|
| 317 |
</fields>
|
427 |
</fields>
|
| 318 |
<cluster_schema/>
|
428 |
<cluster_schema/>
|
| 319 |
<remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
|
429 |
<remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
|
| 320 |
<xloc>630</xloc>
|
430 |
<xloc>630</xloc>
|
| 321 |
<yloc>161</yloc>
|
431 |
<yloc>161</yloc>
|