Subversion Repositories SmartDukaan

    <sql>SELECT SQL_NO_CACHE o.id &apos;order_id&apos;, o.customer_id, o.customer_pincode, 

    <sql>SELECT SQL_NO_CACHE o.id &apos;order_id&apos;, o.customer_id, o.customer_pincode,  o.status &apos;order_status&apos;, o.total_amount, ps.hotspotId as pickupStoreId,  

datediff(o.created_timestamp, '20101231') as date_id, hour(o.created_timestamp) as order_hour,

		datediff(o.created_timestamp, '20101231') as date_id, hour(o.created_timestamp) as order_hour, o.transaction_id, l.item_id, l.quantity, 

o.transaction_id, l.item_id, l.quantity,

		case when unit_price<250 then 0 when unit_price<500 then 1 when unit_price<1000 then 2 

case when unit_price<250 then 0 when unit_price<500 then 1 when unit_price<1000 then 2 when unit_price<2000 then 3 when unit_price < 5000 then 4 when unit_price < 10000 then 5 when unit_price < 15000 then 6 when unit_price <20000 then 7 when unit_price < 50000 then 8 else 9 end as price_range,

			 when unit_price<2000 then 3 when unit_price < 5000 then 4 when unit_price < 10000 then 5 when unit_price < 15000 then 6 

l.transfer_price, p.gatewayId, 

			 when unit_price <20000 then 7 when unit_price < 50000 then 8 else 9 end as price_range, l.transfer_price, p.gatewayId,  

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',

		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', 

upper(trim(t.coupon_code)) as coupon_code, 

		upper(trim(t.coupon_code)) as coupon_code,  

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,

		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' 

SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTR(session_source, LOCATE('http://', LCASE(session_source)), LENGTH(session_source)), 'http://', ''), 'www.', ''), '/', 1) as session_source_url,

		SUBSTRING_INDEX(REPLACE(REPLACE(SUBSTR(session_source, LOCATE('http://', LCASE(session_source)), LENGTH(session_source)), 'http://', ''), 'www.', ''), '/', 1) as session_source_url, 

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

		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

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

			 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

when datediff(t.session_start_time, u.source_start_time) > 1 then 4 else 5 end as days_to_purchase,

			 when datediff(t.session_start_time, u.source_start_time) > 1 then 4 else 5 end as days_to_purchase, aff.name aff_name, 

aff.name aff_name,

		case when o.originalOrderId is null then datediff(o.created_timestamp, '20101231')

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

			 else datediff((select created_timestamp from `order` where id = o.originalOrderId), '20101231') end as booking_date_id 

FROM `order` o 

FROM `order` o  

left join lineitem l on(o.id = l.order_id ) 

left join lineitem l on(o.id = l.order_id )  

left join (select * from payment.payment order by id desc) p on (o.transaction_id = p.merchantTxnId) 

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))  

left join payment.paymentattribute pa on (p.id = pa.payment_id and pa.name = 'payMethod')

left join payment.paymentattribute pa on (p.id = pa.payment_id and pa.name = 'payMethod') 

left join transaction t on (o.transaction_id = t.id)

left join transaction t on (o.transaction_id = t.id) 

left join user.tracklog tl on (tl.data like concat(convert(p.id, char), '%')) 

left join user.tracklog tl on (tl.user_id = o.customer_id and tl.data like concat(convert(p.id, char), '%'))  

left join user.affiliate aff on (tl.affiliate_id = aff.id)

left join user.user u on (u.id = o.customer_id)  left join user.affiliate aff on (tl.affiliate_id = aff.id) 

left join logistics.pickupstore ps on (o.pickupStoreId = ps.id)

left join logistics.pickupstore ps on (o.pickupStoreId = ps.id) group by order_id</sql>