Subversion Repositories SmartDukaan

Rev

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

Rev 33052 Rev 33145
Line 89... Line 89...
89 
 
 89 
 
90 
        @NamedNativeQuery(name = "Order.selectAllOrderWeight", query = "select i.catalog_item_id from transaction.`order` o join transaction.lineitem li on o.id = li.order_id " + "join catalog.item i on i.id = li.item_id   " + "where date(o.created_timestamp) between :startDate and :endDate  and o.status = 12 and i.hsnCode = '85171300' and i.brand != 'Live Demo'" + " group by i.catalog_item_id order by  sum((datediff(now(),o.created_timestamp)/30)*li.quantity) desc"),
 90 
        @NamedNativeQuery(name = "Order.selectAllOrderWeight", query = "select i.catalog_item_id from transaction.`order` o join transaction.lineitem li on o.id = li.order_id " + "join catalog.item i on i.id = li.item_id   " + "where date(o.created_timestamp) between :startDate and :endDate  and o.status = 12 and i.hsnCode = '85171300' and i.brand != 'Live Demo'" + " group by i.catalog_item_id order by  sum((datediff(now(),o.created_timestamp)/30)*li.quantity) desc"),
91 
 
 91 
 
92 
        @NamedNativeQuery(name = "selectPartnerSecondaryGroupByBrand", query = "select ifnull(a.customer_id,fs.id) as id,ifnull(a.sb,brand) as brand,a.secondary_plan,sum(a.total_price) as total_price,a.auth_id, a.commited_timestamp from " + "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,psp.commited_timestamp from transaction.`order` o join transaction.lineitem li on " + "(o.id = li.order_id) left join  auth.partner_secondary_plan psp on (psp.fofo_id = o.customer_id  and date(psp.commited_timestamp) >= :startDate and " + "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" + " 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, psp.commited_timestamp " + " 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) " + "left join transaction.lineitem li on  (o.id = li.order_id and psp.brand=li.brand) where psp.fofo_id in :fofoIds and date(psp.commited_timestamp) >= :startDate " + "and psp.active = 1) a on fs.id = a.customer_id or fs.id = a.fofo_id where fs.id in :fofoIds group by id,brand;", resultSetMapping = "partnerSecondaryPlanModel"),
 92 
        @NamedNativeQuery(name = "selectPartnerSecondaryGroupByBrand", query = "select ifnull(a.customer_id,fs.id) as id,ifnull(a.sb,brand) as brand,a.secondary_plan,sum(a.total_price) as total_price,a.auth_id, a.commited_timestamp from " + "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,psp.commited_timestamp from transaction.`order` o join transaction.lineitem li on " + "(o.id = li.order_id) left join  auth.partner_secondary_plan psp on (psp.fofo_id = o.customer_id  and date(psp.commited_timestamp) >= :startDate and " + "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" + " 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, psp.commited_timestamp " + " 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) " + "left join transaction.lineitem li on  (o.id = li.order_id and psp.brand=li.brand) where psp.fofo_id in :fofoIds and date(psp.commited_timestamp) >= :startDate " + "and psp.active = 1) a on fs.id = a.customer_id or fs.id = a.fofo_id where fs.id in :fofoIds group by id,brand;", resultSetMapping = "partnerSecondaryPlanModel"),
93 
 
 93 
 
- 
 
 94 
        @NamedNativeQuery(name = "selectPriceDropReport", query = "select fs.code, pd.id, ci.brand,ci.model_name,"
- 
 
 95 
                + " ci.model_number,pd.affected_on, pd.amount, pd.partner_payout, pdi.imei, pdi.status, pdi.update_timestamp, pdi.rejection_reason,pdi.credit_timestamp,pdi.reject_timestamp"
- 
 
 96 
                + " from transaction.price_drop pd join transaction.price_drop_imei pdi on pdi.price_drop_id = pd.id "
- 
 
 97 
                + " join (select catalog_item_id, brand, model_name, model_number from catalog.item group by catalog_item_id )"
94 
        @NamedNativeQuery(name = "selectPriceDropReport", query = "select fs.code, pd.id, ci.brand,ci.model_name," + " ci.model_number,pd.affected_on, pd.amount, pd.partner_payout, pdi.imei, pdi.status, pdi.update_timestamp, pdi.rejection_reason" + " from transaction.price_drop pd join transaction.price_drop_imei pdi on pdi.price_drop_id = pd.id join (select catalog_item_id, brand, model_name, model_number from catalog.item group by catalog_item_id )" + " ci on ci.catalog_item_id=pd.catalog_id join fofo.fofo_store fs on fs.id=pdi.retailer_id where  pdi.retailer_id = :fofoId and pd.affected_on between :startDate and :endDate and pd.amount > 0;", resultSetMapping = "PriceDropReportModel"),
 98 
                + " ci on ci.catalog_item_id=pd.catalog_id join fofo.fofo_store fs on fs.id=pdi.retailer_id where  pdi.retailer_id = :fofoId and pd.affected_on between :startDate and :endDate and pd.amount > 0;",
- 
 
 99 
                resultSetMapping = "PriceDropReportModel"),
95 
 
 100 
 
96 
        @NamedNativeQuery(name = "Order.selectCreateToBillingAvg", query = "select o.customer_id, avg(DATEDIFF(o.billing_timestamp,o.created_timestamp)) as average from transaction.`order` o join fofo.fofo_store fs on o.customer_id = fs.id where date(o.created_timestamp) between :startDate and :endDate  and  o.billing_timestamp is not null and fs.internal = 0  group by o.customer_id", resultSetMapping = "BillingDaysAvg"),
 101 
        @NamedNativeQuery(name = "Order.selectCreateToBillingAvg", query = "select o.customer_id, avg(DATEDIFF(o.billing_timestamp,o.created_timestamp)) as average from transaction.`order` o join fofo.fofo_store fs on o.customer_id = fs.id where date(o.created_timestamp) between :startDate and :endDate  and  o.billing_timestamp is not null and fs.internal = 0  group by o.customer_id", resultSetMapping = "BillingDaysAvg"),
97 
 
 102 
 
98 
        @NamedNativeQuery(name = "Order.selectBillingToDeliverAvg", query = "select o.customer_id, avg(DATEDIFF(o.delivery_timestamp,o.billing_timestamp)) as average  from transaction.`order` o join fofo.fofo_store fs on o.customer_id = fs.id where date(o.created_timestamp) between :startDate and :endDate  and  o.billing_timestamp is not null and o.delivery_timestamp is not null and fs.internal = 0 group by o.customer_id", resultSetMapping = "BillingDaysAvg"),
 103 
        @NamedNativeQuery(name = "Order.selectBillingToDeliverAvg", query = "select o.customer_id, avg(DATEDIFF(o.delivery_timestamp,o.billing_timestamp)) as average  from transaction.`order` o join fofo.fofo_store fs on o.customer_id = fs.id where date(o.created_timestamp) between :startDate and :endDate  and  o.billing_timestamp is not null and o.delivery_timestamp is not null and fs.internal = 0 group by o.customer_id", resultSetMapping = "BillingDaysAvg"),
99 
 
 104 
 
Line 105... Line 110...
105 
 
 110 
 
106 
@SqlResultSetMappings({
 111 
@SqlResultSetMappings({
107 
 
 112 
 
108 
        @SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "secondary_plan", type = Long.class), @ColumnResult(name = "total_price", type = Long.class), @ColumnResult(name = "auth_id", type = Integer.class), @ColumnResult(name = "commited_timestamp", type = LocalDateTime.class)})}),
 113 
        @SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "secondary_plan", type = Long.class), @ColumnResult(name = "total_price", type = Long.class), @ColumnResult(name = "auth_id", type = Integer.class), @ColumnResult(name = "commited_timestamp", type = LocalDateTime.class)})}),
109 
 
 114 
 
110 
        @SqlResultSetMapping(name = "PriceDropReportModel", classes = {@ConstructorResult(targetClass = PriceDropReportModel.class, columns = {@ColumnResult(name = "code", type = String.class), @ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "model_name", type = String.class), @ColumnResult(name = "model_number", type = String.class), @ColumnResult(name = "affected_on", type = LocalDateTime.class), @ColumnResult(name = "amount", type = Float.class), @ColumnResult(name = "partner_payout", type = Float.class), @ColumnResult(name = "imei", type = String.class), @ColumnResult(name = "status", type = String.class), @ColumnResult(name = "update_timestamp", type = LocalDateTime.class), @ColumnResult(name = "rejection_reason", type = String.class),})}),
 115 
        @SqlResultSetMapping(name = "PriceDropReportModel", classes = {@ConstructorResult(targetClass = PriceDropReportModel.class, columns = {@ColumnResult(name = "code", type = String.class), @ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "model_name", type = String.class), @ColumnResult(name = "model_number", type = String.class), @ColumnResult(name = "affected_on", type = LocalDateTime.class), @ColumnResult(name = "amount", type = Float.class), @ColumnResult(name = "partner_payout", type = Float.class), @ColumnResult(name = "imei", type = String.class), @ColumnResult(name = "status", type = String.class), @ColumnResult(name = "update_timestamp", type = LocalDateTime.class), @ColumnResult(name = "rejection_reason", type = String.class), @ColumnResult(name = "credit_timestamp", type = LocalDateTime.class), @ColumnResult(name = "reject_timestamp", type = LocalDateTime.class)})}),
111 
 
 116 
 
112 
        @SqlResultSetMapping(name = "BillingDaysAvg", classes = {@ConstructorResult(targetClass = BillingDaysAvg.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "average", type = Long.class)})}),
 117 
        @SqlResultSetMapping(name = "BillingDaysAvg", classes = {@ConstructorResult(targetClass = BillingDaysAvg.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "average", type = Long.class)})}),
113 
 
 118 
 
114 
        @SqlResultSetMapping(name = "LastSaleDays", classes = {@ConstructorResult(targetClass = LastSaleDateModel.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "maxdate", type = LocalDateTime.class)})})
 119 
        @SqlResultSetMapping(name = "LastSaleDays", classes = {@ConstructorResult(targetClass = LastSaleDateModel.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "maxdate", type = LocalDateTime.class)})})
115 
 
 120