| Line 182... |
Line 182... |
| 182 |
<connection>transactionDC_Prod</connection>
|
182 |
<connection>transactionDC_Prod</connection>
|
| 183 |
<sql>SELECT SQL_NO_CACHE o.id 'order_id', o.customer_id, o.customer_pincode, o.status 'order_status', o.total_amount, ps.hotspotId as pickupStoreId,
|
183 |
<sql>SELECT SQL_NO_CACHE o.id 'order_id', o.customer_id, o.customer_pincode, o.status 'order_status', o.total_amount, ps.hotspotId as pickupStoreId,
|
| 184 |
datediff(o.created_timestamp, '20101231') as date_id, hour(o.created_timestamp) as order_hour, o.transaction_id, l.item_id, l.quantity,
|
184 |
datediff(o.created_timestamp, '20101231') as date_id, hour(o.created_timestamp) as order_hour, o.transaction_id, l.item_id, l.quantity,
|
| 185 |
case when unit_price<250 then 0 when unit_price<500 then 1 when unit_price<1000 then 2
|
185 |
case when unit_price<250 then 0 when unit_price<500 then 1 when unit_price<1000 then 2
|
| 186 |
when unit_price<2000 then 3 when unit_price < 5000 then 4 when unit_price < 10000 then 5 when unit_price < 15000 then 6
|
186 |
when unit_price<2000 then 3 when unit_price < 5000 then 4 when unit_price < 10000 then 5 when unit_price < 15000 then 6
|
| 187 |
when unit_price <20000 then 7 when unit_price < 50000 then 8 else 9 end as price_range, l.transfer_price, p.gatewayId,
|
187 |
when unit_price <20000 then 7 when unit_price < 50000 then 8 else 9 end as price_range, l.transfer_price, case when l.nlc is null then l.transfer_price else l.nlc end as nlc, p.gatewayId,
|
| 188 |
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',
|
188 |
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',
|
| 189 |
upper(trim(t.coupon_code)) as coupon_code,
|
189 |
upper(trim(t.coupon_code)) as coupon_code,
|
| 190 |
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'
|
190 |
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'
|
| 191 |
when t.session_source like 'PAID%' then 'Paid' else 'Referer' end as session_source_type,
|
191 |
when t.session_source like 'PAID%' then 'Paid' else 'Referer' end as session_source_type,
|
| 192 |
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,
|
| 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
|
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
|
| 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
|
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
|
| 195 |
when datediff(t.session_start_time, u.source_start_time) > 1 then 4 else 5 end as days_to_purchase, aff.name aff_name,
|
195 |
when datediff(t.session_start_time, u.source_start_time) > 1 then 4 else 5 end as days_to_purchase, aff.name aff_name,
|
| 196 |
case when o.originalOrderId is null then datediff(o.created_timestamp, '20101231')
|
196 |
case when o.originalOrderId is null then datediff(o.created_timestamp, '20101231')
|
| 197 |
else datediff((select created_timestamp from `order` where id = o.originalOrderId), '20101231') end as booking_date_id
|
197 |
else datediff((select created_timestamp from `order` where id = o.originalOrderId), '20101231') end as booking_date_id ,
|
| - |
|
198 |
o.otg as otg,
|
| - |
|
199 |
o.insurer, o.insuranceAmount
|
| 198 |
FROM `order` o
|
200 |
FROM `order` o
|
| 199 |
left join lineitem l on(o.id = l.order_id )
|
201 |
left join lineitem l on(o.id = l.order_id )
|
| 200 |
left join payment.payment p on (o.transaction_id = p.merchantTxnId and p.id=(select max(id) from payment.payment where merchantTxnId = o.transaction_id))
|
202 |
left join payment.payment p on (o.transaction_id = p.merchantTxnId and p.id=(select max(id) from payment.payment where merchantTxnId = o.transaction_id))
|
| 201 |
left join payment.paymentattribute pa on (p.id = pa.payment_id and pa.name = 'payMethod')
|
203 |
left join payment.paymentattribute pa on (p.id = pa.payment_id and pa.name = 'payMethod')
|
| 202 |
left join transaction t on (o.transaction_id = t.id)
|
204 |
left join transaction t on (o.transaction_id = t.id)
|
| 203 |
left join user.tracklog tl on (tl.user_id = o.customer_id and tl.data like concat(convert(p.id, char), '%'))
|
205 |
left join user.tracklog tl on (tl.user_id = o.customer_id and tl.data like concat(convert(p.id, char), '%'))
|
| 204 |
left join user.user u on (u.id = o.customer_id) left join user.affiliate aff on (tl.affiliate_id = aff.id)
|
206 |
left join user.user u on (u.id = o.customer_id) left join user.affiliate aff on (tl.affiliate_id = aff.id)
|
| 205 |
left join logistics.pickupstore ps on (o.pickupStoreId = ps.id) group by order_id</sql>
|
207 |
left join logistics.pickupstore ps on (o.pickupStoreId = ps.id) where o.status > 1 group by order_id</sql>
|
| 206 |
<limit>0</limit>
|
208 |
<limit>0</limit>
|
| 207 |
<lookup/>
|
209 |
<lookup/>
|
| 208 |
<execute_each_row>N</execute_each_row>
|
210 |
<execute_each_row>N</execute_each_row>
|
| 209 |
<variables_active>N</variables_active>
|
211 |
<variables_active>N</variables_active>
|
| 210 |
<lazy_conversion_active>N</lazy_conversion_active>
|
212 |
<lazy_conversion_active>N</lazy_conversion_active>
|
| Line 311... |
Line 313... |
| 311 |
<length>-2</length>
|
313 |
<length>-2</length>
|
| 312 |
<precision>-2</precision>
|
314 |
<precision>-2</precision>
|
| 313 |
</field> <select_unspecified>N</select_unspecified>
|
315 |
</field> <select_unspecified>N</select_unspecified>
|
| 314 |
</fields> <cluster_schema/>
|
316 |
</fields> <cluster_schema/>
|
| 315 |
<remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
|
317 |
<remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
|
| 316 |
<xloc>371</xloc>
|
318 |
<xloc>369</xloc>
|
| 317 |
<yloc>160</yloc>
|
319 |
<yloc>162</yloc>
|
| 318 |
<draw>Y</draw>
|
320 |
<draw>Y</draw>
|
| 319 |
</GUI>
|
321 |
</GUI>
|
| 320 |
</step>
|
322 |
</step>
|
| 321 |
|
323 |
|
| 322 |
<step>
|
324 |
<step>
|
| Line 432... |
Line 434... |
| 432 |
<stream_name>pickupStoreId</stream_name>
|
434 |
<stream_name>pickupStoreId</stream_name>
|
| 433 |
</field>
|
435 |
</field>
|
| 434 |
</fields>
|
436 |
</fields>
|
| 435 |
<cluster_schema/>
|
437 |
<cluster_schema/>
|
| 436 |
<remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
|
438 |
<remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
|
| 437 |
<xloc>630</xloc>
|
439 |
<xloc>631</xloc>
|
| 438 |
<yloc>161</yloc>
|
440 |
<yloc>162</yloc>
|
| 439 |
<draw>Y</draw>
|
441 |
<draw>Y</draw>
|
| 440 |
</GUI>
|
442 |
</GUI>
|
| 441 |
</step>
|
443 |
</step>
|
| 442 |
|
444 |
|
| 443 |
<step_error_handling>
|
445 |
<step_error_handling>
|