| Line 17... |
Line 17... |
| 17 |
* @author ashikali
|
17 |
* @author ashikali
|
| 18 |
*/
|
18 |
*/
|
| 19 |
|
19 |
|
| 20 |
@Entity
|
20 |
@Entity
|
| 21 |
@Table(name = "transaction.`order`", schema = "transaction")
|
21 |
@Table(name = "transaction.`order`", schema = "transaction")
|
| 22 |
@NamedQueries({@NamedQuery(name = "Order.selectAll", query = "select o from Order o"),
|
22 |
@NamedQueries({ @NamedQuery(name = "Order.selectAll", query = "select o from Order o"),
|
| 23 |
@NamedQuery(name = "Order.selectById", query = "select o from Order o where o.id= :id"),
|
23 |
@NamedQuery(name = "Order.selectById", query = "select o from Order o where o.id= :id"),
|
| 24 |
@NamedQuery(name = "Order.selectByTransactionId", query = "select t.id, t.createTimestamp, o.retailerAddress1, o.retailerAddress2, o.retailerCity, "
|
24 |
@NamedQuery(name = "Order.selectByTransactionId", query = "select t.id, t.createTimestamp, o.retailerAddress1, o.retailerAddress2, o.retailerCity, "
|
| 25 |
+ "o.retailerPinCode, o.retailerState, o.shippingCost, o.statusDescription, o.invoiceNumber, o.airwayBillNumber, o.totalAmount, li.brand, li.modelName, "
|
25 |
+ "o.retailerPinCode, o.retailerState, o.shippingCost, o.statusDescription, o.invoiceNumber, o.airwayBillNumber, o.totalAmount, li.brand, li.modelName, "
|
| 26 |
+ "li.modelNumber, li.color, li.quantity, li.unitPrice, p.id, p.name, o.shippingTimestamp, o.status, o.promisedDeliveryTime, o.retailerName, t.status, i.catalogItemId from Transaction t join Order o on o.transactionId = t.id "
|
26 |
+ "li.modelNumber, li.color, li.quantity, li.unitPrice, p.id, p.name, o.shippingTimestamp, o.status, o.promisedDeliveryTime, o.retailerName, t.status, i.catalogItemId from Transaction t join Order o on o.transactionId = t.id "
|
| 27 |
+ "join LineItem li on li.orderId = o.id left join Provider p on p.id = o.logisticsProviderId join Item i on i.id=li.itemId where o.transactionId = :transactionId"),
|
27 |
+ "join LineItem li on li.orderId = o.id left join Provider p on p.id = o.logisticsProviderId join Item i on i.id=li.itemId where o.transactionId = :transactionId"),
|
| Line 128... |
Line 128... |
| 128 |
+ " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId "
|
128 |
+ " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId "
|
| 129 |
+ " join Item i on i.id = tl.itemId where "
|
129 |
+ " join Item i on i.id = tl.itemId where "
|
| 130 |
+ " fs.active = 1 and fs.internal = 0 and o.status in (3,4) and i.catalogItemId in :catalogItemId and i.categoryId=10006 and fs.id = :fofoId group by i.catalogItemId, i.brand,i.modelName,i.modelNumber"),
|
130 |
+ " fs.active = 1 and fs.internal = 0 and o.status in (3,4) and i.catalogItemId in :catalogItemId and i.categoryId=10006 and fs.id = :fofoId group by i.catalogItemId, i.brand,i.modelName,i.modelNumber"),
|
| 131 |
|
131 |
|
| 132 |
@NamedQuery(name = "Order.selectGroupByBrandLmp", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand,"
|
132 |
@NamedQuery(name = "Order.selectGroupByBrandLmp", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand,"
|
| 133 |
+ " DATE_FORMAT(o.billingTimestamp, '%m-%d-%Y'),sum(cast(o.totalAmount AS integer)))"
|
133 |
+ " DATE_FORMAT(o.billingTimestamp,:groupParameter ),sum(cast(o.totalAmount AS integer)))"
|
| 134 |
+ " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)"
|
134 |
+ " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)"
|
| 135 |
+ " and o.billingTimestamp >= :lmsStartDate and fs.id in :fofoId and fs.warehouseId in :warehouseId and fs.internal = 0 group by li.brand,DATE_FORMAT(o.billingTimestamp, '%m-%d-%Y')"),
|
135 |
+ " and (o.billingTimestamp between :lmsStartDate and :endDate) and fs.id in :fofoId and fs.warehouseId in :warehouseId and fs.internal = 0 group by li.brand,DATE_FORMAT(o.billingTimestamp, :groupParameter)"),
|
| 136 |
|
136 |
|
| 137 |
@NamedQuery(name = "Order.selectGrnPendingOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)"
|
137 |
@NamedQuery(name = "Order.selectGrnPendingOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)"
|
| 138 |
+ " from FofoStore fs join Order o on fs.id = o.retailerId "
|
138 |
+ " from FofoStore fs join Order o on fs.id = o.retailerId "
|
| 139 |
+ " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId "
|
139 |
+ " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId "
|
| 140 |
+ " join Item i on (i.id = tl.itemId ) where " + " fs.active = 1 and fs.internal = 0 and"
|
140 |
+ " join Item i on (i.id = tl.itemId ) where " + " fs.active = 1 and fs.internal = 0 and"
|
| Line 175... |
Line 175... |
| 175 |
+ " sum(case when po.name = 'ZEST MONEY' then cast(pot.amount As int) else 0 end),"
|
175 |
+ " sum(case when po.name = 'ZEST MONEY' then cast(pot.amount As int) else 0 end),"
|
| 176 |
+ " sum(case when po.name = 'SAMSUNG SURE' then cast(pot.amount As int) else 0 end),sum(cast(pot.amount As int)))"
|
176 |
+ " sum(case when po.name = 'SAMSUNG SURE' then cast(pot.amount As int) else 0 end),sum(cast(pot.amount As int)))"
|
| 177 |
+ " from PaymentOptionTransaction pot join PaymentOption po on pot.paymentOptionId=po.id join com.spice.profitmandi.dao.entity.user.User u on u.id=pot.fofoId"
|
177 |
+ " from PaymentOptionTransaction pot join PaymentOption po on pot.paymentOptionId=po.id join com.spice.profitmandi.dao.entity.user.User u on u.id=pot.fofoId"
|
| 178 |
+ " join Address a on a.id=u.addressId where 1=1 and pot.fofoId = :fofoId and pot.createTimestamp between :startDate and :endDate "
|
178 |
+ " join Address a on a.id=u.addressId where 1=1 and pot.fofoId = :fofoId and pot.createTimestamp between :startDate and :endDate "
|
| 179 |
+ " group by (pot.createTimestamp), pot.referenceType "),
|
179 |
+ " group by (pot.createTimestamp), pot.referenceType "),
|
| 180 |
|
- |
|
| 181 |
|
180 |
|
| 182 |
})
|
181 |
})
|
| 183 |
//@NamedNativeQuery(name = "Order.findByEmailAddress", query = "select * from transaction.`order` o where o.id = '1694173'", resultClass = Order.class)
|
182 |
//@NamedNativeQuery(name = "Order.findByEmailAddress", query = "select * from transaction.`order` o where o.id = '1694173'", resultClass = Order.class)
|
| 184 |
|
183 |
|
| 185 |
|
- |
|
| 186 |
@NamedNativeQueries({
|
184 |
@NamedNativeQueries({
|
| 187 |
|
- |
|
| 188 |
@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 "
|
- |
|
| 189 |
+ "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 "
|
- |
|
| 190 |
+ "(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 "
|
- |
|
| 191 |
+ "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"
|
- |
|
| 192 |
+ " 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 "
|
- |
|
| 193 |
+ " 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) "
|
- |
|
| 194 |
+ "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 "
|
- |
|
| 195 |
+ "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"),
|
- |
|
| 196 |
|
- |
|
| 197 |
@NamedNativeQuery(name = "selectPriceDropReport", query = "select fs.code, pd.id, ci.brand,ci.model_name,"
|
- |
|
| 198 |
+ " ci.model_number,pd.affected_on, pd.amount, pd.partner_payout, pdi.imei, pdi.status, pdi.update_timestamp, pdi.rejection_reason"
|
- |
|
| 199 |
+ " 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 )"
|
- |
|
| 200 |
+ " 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")
|
- |
|
| 201 |
|
185 |
|
| - |
|
186 |
@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 "
|
| - |
|
187 |
+ "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 "
|
| - |
|
188 |
+ "(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 "
|
| - |
|
189 |
+ "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"
|
| - |
|
190 |
+ " 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 "
|
| - |
|
191 |
+ " 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) "
|
| - |
|
192 |
+ "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 "
|
| - |
|
193 |
+ "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"),
|
| 202 |
})
|
194 |
|
| - |
|
195 |
@NamedNativeQuery(name = "selectPriceDropReport", query = "select fs.code, pd.id, ci.brand,ci.model_name,"
|
| - |
|
196 |
+ " ci.model_number,pd.affected_on, pd.amount, pd.partner_payout, pdi.imei, pdi.status, pdi.update_timestamp, pdi.rejection_reason"
|
| - |
|
197 |
+ " 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 )"
|
| - |
|
198 |
+ " 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")
|
| 203 |
|
199 |
|
| - |
|
200 |
})
|
| 204 |
|
201 |
|
| 205 |
@SqlResultSetMappings({
|
202 |
@SqlResultSetMappings({
|
| 206 |
|
203 |
|
| 207 |
@SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {
|
204 |
@SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {
|
| 208 |
@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {
|
205 |
@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {
|
| - |
|
206 |
@ColumnResult(name = "id", type = Integer.class),
|
| 209 |
@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class),
|
207 |
@ColumnResult(name = "brand", type = String.class),
|
| 210 |
@ColumnResult(name = "secondary_plan", type = Long.class),
|
208 |
@ColumnResult(name = "secondary_plan", type = Long.class),
|
| 211 |
@ColumnResult(name = "total_price", type = Long.class),
|
209 |
@ColumnResult(name = "total_price", type = Long.class),
|
| 212 |
@ColumnResult(name = "auth_id", type = Integer.class),
|
210 |
@ColumnResult(name = "auth_id", type = Integer.class),
|
| 213 |
@ColumnResult(name = "commited_timestamp", type = LocalDateTime.class)}) }),
|
211 |
@ColumnResult(name = "commited_timestamp", type = LocalDateTime.class) }) }),
|
| 214 |
|
212 |
|
| 215 |
@SqlResultSetMapping(name = "PriceDropReportModel", classes = {
|
213 |
@SqlResultSetMapping(name = "PriceDropReportModel", classes = {
|
| 216 |
@ConstructorResult(targetClass = PriceDropReportModel.class, columns = {
|
214 |
@ConstructorResult(targetClass = PriceDropReportModel.class, columns = {
|
| 217 |
@ColumnResult(name = "code", type = String.class),
|
215 |
@ColumnResult(name = "code", type = String.class),
|
| 218 |
@ColumnResult(name = "id", type = Integer.class),
|
216 |
@ColumnResult(name = "id", type = Integer.class),
|
| 219 |
@ColumnResult(name = "brand", type = String.class),
|
217 |
@ColumnResult(name = "brand", type = String.class),
|
| 220 |
@ColumnResult(name = "model_name", type = String.class),
|
218 |
@ColumnResult(name = "model_name", type = String.class),
|
| 221 |
@ColumnResult(name = "model_number", type = String.class),
|
219 |
@ColumnResult(name = "model_number", type = String.class),
|
| 222 |
@ColumnResult(name = "affected_on", type = LocalDateTime.class),
|
220 |
@ColumnResult(name = "affected_on", type = LocalDateTime.class),
|
| 223 |
@ColumnResult(name = "amount", type = Float.class),
|
221 |
@ColumnResult(name = "amount", type = Float.class),
|
| 224 |
@ColumnResult(name = "partner_payout", type = Float.class),
|
222 |
@ColumnResult(name = "partner_payout", type = Float.class),
|
| 225 |
@ColumnResult(name = "imei", type = String.class),
|
223 |
@ColumnResult(name = "imei", type = String.class),
|
| 226 |
@ColumnResult(name = "status", type = String.class),
|
224 |
@ColumnResult(name = "status", type = String.class),
|
| 227 |
@ColumnResult(name = "update_timestamp", type = LocalDateTime.class),
|
225 |
@ColumnResult(name = "update_timestamp", type = LocalDateTime.class),
|
| 228 |
@ColumnResult(name = "rejection_reason", type = String.class),
|
226 |
@ColumnResult(name = "rejection_reason", type = String.class), }) })
|
| 229 |
}) })
|
- |
|
| 230 |
|
227 |
|
| 231 |
})
|
228 |
})
|
| 232 |
|
229 |
|
| 233 |
public class Order implements Serializable {
|
230 |
public class Order implements Serializable {
|
| 234 |
|
231 |
|
| 235 |
private static final long serialVersionUID = 1L;
|
232 |
private static final long serialVersionUID = 1L;
|