Subversion Repositories SmartDukaan

Rev

Rev 3436 | Rev 3482 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 3436 Rev 3481
Line 156... Line 156...
156 
           </partitioning>
 156 
           </partitioning>
157 
    <connection>transactionDC_Prod</connection>
 157 
    <connection>transactionDC_Prod</connection>
158 
    <sql>SELECT SQL_NO_CACHE o.id &apos;order_id&apos;, o.customer_id, o.customer_pincode,
158 
    <sql>SELECT SQL_NO_CACHE o.id &apos;order_id&apos;, o.customer_id, o.customer_pincode,
159 
o.status &apos;order_status&apos;, o.total_amount,
159 
o.status &apos;order_status&apos;, o.total_amount,
160 
datediff(o.created_timestamp, &apos;20101231&apos;) as date_id, hour(o.created_timestamp) as order_hour,
 160 
datediff(o.created_timestamp, &apos;20101231&apos;) as date_id, hour(o.created_timestamp) as order_hour,
161 
o.transaction_id, l.item_id, l.quantity, l.unit_price, l.transfer_price, p.gatewayId,
161 
o.transaction_id, l.item_id, l.quantity,
- 
 
 162 
case when unit_price&lt;2000 then 0 when unit_price &lt; 5000 then 1 when unit_price &lt; 10000 then 2 when unit_price &lt; 15000 then 3 when unit_price &lt;20000 then 4 when unit_price &lt; 50000 then 5 else 6 end as price_range,
- 
 
 163 
l.transfer_price, p.gatewayId,
162 
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 &apos;payment_method&apos;,
 164 
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 &apos;payment_method&apos;,
163 
t.coupon_code,
165 
t.coupon_code,
164 
case when t.session_source is null then &apos;Unknown&apos; when t.session_source like &apos;DIRECT%&apos; then &apos;Direct&apos; when t.session_source like &apos;ORGANIC%&apos; then &apos;Organic&apos; when t.session_source like &apos;PAID%&apos; then &apos;Paid&apos; else &apos;Referer&apos; end as session_source_type,
 166 
case when t.session_source is null then &apos;Unknown&apos; when t.session_source like &apos;DIRECT%&apos; then &apos;Direct&apos; when t.session_source like &apos;ORGANIC%&apos; then &apos;Organic&apos; when t.session_source like &apos;PAID%&apos; then &apos;Paid&apos; else &apos;Referer&apos; end as session_source_type,
165 
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(t.session_source,&apos;:&#47;&#47;&apos;,-1),&apos;&#47;&apos;,1),&apos;www.&apos;,-1) as session_source_url,
 167 
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(t.session_source,&apos;:&#47;&#47;&apos;,-1),&apos;&#47;&apos;,1),&apos;www.&apos;,-1) as session_source_url,
166 
case when datediff(t.session_start_time, &apos;20101231&apos;) &gt; 0 then datediff(t.session_start_time, &apos;20101231&apos;) else 1 end as session_start_date_id,
168 
datediff(t.session_start_time, u.source_start_time) as days_to_purchase,
167 
case when datediff(u.source_start_time, &apos;20101231&apos;) &gt; 0 then datediff(u.source_start_time, &apos;20101231&apos;) else 1 end as first_source_start_date_id,
- 
 
168 
hour(t.session_start_time) as session_start_hour, aff.name aff_name
 169 
aff.name aff_name
169 
FROM `order` o
170 
FROM `order` o
170 
left join lineitem l on(o.id = l.order_id )
171 
left join lineitem l on(o.id = l.order_id )
171 
left join (select * from payment.payment order by id desc) p on (o.transaction_id = p.merchantTxnId)
172 
left join (select * from payment.payment order by id desc) p on (o.transaction_id = p.merchantTxnId)
172 
left join payment.paymentattribute pa on (p.id = pa.payment_id and pa.name = &apos;payMethod&apos;)
 173 
left join payment.paymentattribute pa on (p.id = pa.payment_id and pa.name = &apos;payMethod&apos;)
173 
left join transaction t on (o.transaction_id = t.id)
 174 
left join transaction t on (o.transaction_id = t.id)
Line 228... Line 229...
228 
        <precision>-2</precision>
 229 
        <precision>-2</precision>
229 
      </field>      <field>        <name>quantity</name>
 230 
      </field>      <field>        <name>quantity</name>
230 
        <rename>quantity</rename>
 231 
        <rename>quantity</rename>
231 
        <length>-2</length>
 232 
        <length>-2</length>
232 
        <precision>-2</precision>
 233 
        <precision>-2</precision>
233 
      </field>      <field>        <name>unit_price</name>
 - 
 
234 
        <rename>unit_price</rename>
 - 
 
235 
        <length>-2</length>
 - 
 
236 
        <precision>-2</precision>
 - 
 
237 
      </field>      <field>        <name>transfer_price</name>
 234 
      </field>      <field>        <name>transfer_price</name>
238 
        <rename>transfer_price</rename>
 235 
        <rename>transfer_price</rename>
239 
        <length>-2</length>
 236 
        <length>-2</length>
240 
        <precision>-2</precision>
 237 
        <precision>-2</precision>
241 
      </field>      <field>        <name>gatewayId</name>
 238 
      </field>      <field>        <name>gatewayId</name>
Line 248... Line 245...
248 
        <precision>-2</precision>
 245 
        <precision>-2</precision>
249 
      </field>      <field>        <name>coupon_code</name>
 246 
      </field>      <field>        <name>coupon_code</name>
250 
        <rename>coupon_code</rename>
 247 
        <rename>coupon_code</rename>
251 
        <length>-2</length>
 248 
        <length>-2</length>
252 
        <precision>-2</precision>
 249 
        <precision>-2</precision>
253 
      </field>      <field>        <name>session_start_date_id</name>
 - 
 
254 
        <rename>session_start_date_id</rename>
 - 
 
255 
        <length>-2</length>
 - 
 
256 
        <precision>-2</precision>
 - 
 
257 
      </field>      <field>        <name>session_start_hour</name>
 - 
 
258 
        <rename>session_start_hour</rename>
 - 
 
259 
        <length>-2</length>
 - 
 
260 
        <precision>-2</precision>
 - 
 
261 
      </field>      <field>        <name>customer_pincode</name>
 250 
      </field>      <field>        <name>customer_pincode</name>
262 
        <rename>pincode</rename>
 251 
        <rename>pincode</rename>
263 
        <length>-2</length>
 252 
        <length>-2</length>
264 
        <precision>-2</precision>
 253 
        <precision>-2</precision>
265 
      </field>      <field>        <name>order_status</name>
 254 
      </field>      <field>        <name>order_status</name>
Line 268... Line 257...
268 
        <precision>-2</precision>
 257 
        <precision>-2</precision>
269 
      </field>      <field>        <name>aff_name</name>
 258 
      </field>      <field>        <name>aff_name</name>
270 
        <rename>aff_name</rename>
 259 
        <rename>aff_name</rename>
271 
        <length>-2</length>
 260 
        <length>-2</length>
272 
        <precision>-2</precision>
 261 
        <precision>-2</precision>
273 
      </field>      <field>        <name>first_source_start_date_id</name>
 - 
 
274 
        <rename>first_source_start_date_id</rename>
 - 
 
275 
        <length>-2</length>
 - 
 
276 
        <precision>-2</precision>
 - 
 
277 
      </field>      <field>        <name>session_source_type</name>
 262 
      </field>      <field>        <name>session_source_type</name>
278 
        <rename>session_source_type</rename>
 263 
        <rename>session_source_type</rename>
279 
        <length>-2</length>
 264 
        <length>-2</length>
280 
        <precision>-2</precision>
 265 
        <precision>-2</precision>
281 
      </field>      <field>        <name>session_source_url</name>
 266 
      </field>      <field>        <name>session_source_url</name>
282 
        <rename>session_source_url</rename>
 267 
        <rename>session_source_url</rename>
283 
        <length>-2</length>
 268 
        <length>-2</length>
284 
        <precision>-2</precision>
 269 
        <precision>-2</precision>
- 
 
 270 
      </field>      <field>        <name>price_range</name>
- 
 
 271 
        <rename/>
- 
 
 272 
        <length>-2</length>
- 
 
 273 
        <precision>-2</precision>
- 
 
 274 
      </field>      <field>        <name>days_to_purchase</name>
- 
 
 275 
        <rename/>
- 
 
 276 
        <length>-2</length>
- 
 
 277 
        <precision>-2</precision>
285 
      </field>        <select_unspecified>N</select_unspecified>
 278 
      </field>        <select_unspecified>N</select_unspecified>
286 
    </fields>     <cluster_schema/>
 279 
    </fields>     <cluster_schema/>
287 
 <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
 280 
 <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
288 
      <xloc>371</xloc>
 281 
      <xloc>371</xloc>
289 
      <yloc>160</yloc>
 282 
      <yloc>160</yloc>