| Line 202... |
Line 202... |
| 202 |
@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class),
|
202 |
@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class),
|
| 203 |
@ColumnResult(name = "secondary_plan", type = Long.class),
|
203 |
@ColumnResult(name = "secondary_plan", type = Long.class),
|
| 204 |
@ColumnResult(name = "total_price", type = Long.class),
|
204 |
@ColumnResult(name = "total_price", type = Long.class),
|
| 205 |
@ColumnResult(name = "auth_id", type = Integer.class) }) })
|
205 |
@ColumnResult(name = "auth_id", type = Integer.class) }) })
|
| 206 |
|
206 |
|
| 207 |
@NamedNativeQuery(name = "selectPartnerSecondaryGroupByBrand", query = "select fs.id,ifnull(a.sb,brand) as brand,a.secondary_plan,sum(a.total_price) as total_price,a.auth_id from "
|
207 |
@NamedNativeQuery(name = "selectPartnerSecondaryGroupByBrand", query = "select ifnull(a.customer_id,fs.id),ifnull(a.sb,brand) as brand,a.secondary_plan,sum(a.total_price) as total_price,a.auth_id from "
|
| 208 |
+ "fofo.fofo_store fs left join (select o.customer_id , li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id from transaction.`order` o join transaction.lineitem li on "
|
208 |
+ "fofo.fofo_store fs left join (select o.customer_id , li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id from transaction.`order` o join transaction.lineitem li on "
|
| 209 |
+ "(o.id = li.order_id) left join auth.partner_secondary_plan psp on (psp.fofo_id = o.customer_id and date(psp.create_timestamp) = :startDate and "
|
209 |
+ "(o.id = li.order_id) left join auth.partner_secondary_plan psp on (psp.fofo_id = o.customer_id and date(psp.create_timestamp) = :startDate and "
|
| 210 |
+ "psp.active = 1 and psp.brand = li.brand ) where o.customer_id in :fofoIds and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate"
|
210 |
+ "psp.active = 1 and psp.brand = li.brand ) where o.customer_id in :fofoIds and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate"
|
| 211 |
+ " union select if(li.brand is null, null, o.customer_id) as customer_id, li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id "
|
211 |
+ " union select if(li.brand is null, null, o.customer_id) as customer_id, li.brand as sb, li.total_price, psp.fofo_id , psp.brand, psp.secondary_plan,psp.auth_id "
|
| 212 |
+ "from auth.partner_secondary_plan psp left join transaction.`order` o on (psp.fofo_id = o.customer_id and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate) "
|
212 |
+ "from auth.partner_secondary_plan psp left join transaction.`order` o on (psp.fofo_id = o.customer_id and o.status in (7,9,10,12) and date(o.billing_timestamp) = :startDate) "
|