| Line 83... |
Line 83... |
| 83 |
+ " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId "
|
83 |
+ " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId "
|
| 84 |
+ " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId "
|
84 |
+ " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId "
|
| 85 |
+ " join Item i on i.id = tl.itemId"
|
85 |
+ " join Item i on i.id = tl.itemId"
|
| 86 |
+ " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null and fo.createTimestamp > :startDate and i.catalogItemId in :catalogItemId and fs.warehouseId in :warehouseId and i.categoryId=10006 group by i.catalogItemId, fs.id"),
|
86 |
+ " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null and fo.createTimestamp > :startDate and i.catalogItemId in :catalogItemId and fs.warehouseId in :warehouseId and i.categoryId=10006 group by i.catalogItemId, fs.id"),
|
| 87 |
|
87 |
|
| 88 |
|
88 |
|
| 89 |
@NamedQuery(name = "FofoOrder.selectFocoSaleReport", query = "select new com.spice.profitmandi.dao.model.FocoSaleReportModel(fs.code, ua.name,ua.city, ua.state, w.displayName, foi.itemId,"
|
89 |
@NamedQuery(name = "FofoOrder.selectFocoSaleReport", query = "select new com.spice.profitmandi.dao.model.FocoSaleReportModel(fs.code, ua.name,ua.city, ua.state, w.displayName, foi.itemId,"
|
| 90 |
+ " foi.brand, foi.modelName, foi.modelNumber, foi.color, foi.quantity, cast(foi.dp As int), cast(foi.sellingPrice As int), cast(foi.mop As int), fli.serialNumber, foi.createTimestamp, ca.name, ca.phoneNumber,"
|
90 |
+ " foi.brand, foi.modelName, foi.modelNumber, foi.color, foi.quantity, cast(foi.dp As int), cast(foi.sellingPrice As int), cast(foi.mop As int), fli.serialNumber, foi.createTimestamp, ca.name, ca.phoneNumber,"
|
| 91 |
+ " ca.city, ca.pinCode, fo.invoiceNumber, p.purchaseReference, fo.customerGstNumber, fo.cancelledTimestamp, p.completeTimestamp, hd.hygieneRating,hd.rating, hd.status, hd.remark,"
|
91 |
+ " ca.city, ca.pinCode, fo.invoiceNumber, p.purchaseReference, fo.customerGstNumber, fo.cancelledTimestamp, p.completeTimestamp, hd.hygieneRating,hd.rating, hd.status, hd.remark,"
|
| 92 |
+ " hd.createdTimestamp, hd.disposedTimestamp, hd.nextTimestamp, ai.activationTimestamp, ai.createTimestamp, c.label)"
|
92 |
+ " hd.createdTimestamp, hd.disposedTimestamp, hd.nextTimestamp, ai.activationTimestamp, ai.createTimestamp, c.label)"
|
| 93 |
+ " from FofoStore fs join Warehouse w on w.id=fs.warehouseId join com.spice.profitmandi.dao.entity.user.User u on u.id=fs.id join Address ua on u.addressId = ua.id join FofoOrder fo on fo.fofoId=fs.id left join FofoOrderItem foi"
|
93 |
+ " from FofoStore fs join Warehouse w on w.id=fs.warehouseId join com.spice.profitmandi.dao.entity.user.User u on u.id=fs.id join Address ua on u.addressId = ua.id join FofoOrder fo on fo.fofoId=fs.id left join FofoOrderItem foi"
|
| 94 |
+ " on foi.orderId=fo.id join Item i on i.id=foi.itemId join Category c on c.id=i.categoryId left join CustomerAddress ca on ca.id=fo.customerAddressId left join FofoLineItem fli on fli.fofoOrderItemId=foi.id"
|
94 |
+ " on foi.orderId=fo.id join Item i on i.id=foi.itemId join Category c on c.id=i.categoryId left join CustomerAddress ca on ca.id=fo.customerAddressId left join FofoLineItem fli on fli.fofoOrderItemId=foi.id"
|
| 95 |
+ " left join InventoryItem ii on ii.id=fli.inventoryItemId left join Purchase p on ii.purchaseId=p.id left join HygieneData hd on hd.orderId=fo.id left join ActivatedImei ai on ai.serialNumber=fli.serialNumber"
|
95 |
+ " left join InventoryItem ii on ii.id=fli.inventoryItemId left join Purchase p on ii.purchaseId=p.id left join HygieneData hd on hd.orderId=fo.id left join ActivatedImei ai on ai.serialNumber=fli.serialNumber"
|
| 96 |
+ " where 1=1 and (foi.createTimestamp between :startDate and :endDate or foi.createTimestamp is null) and fs.code= :code and fs.id=:fofoId group by fo.id "),
|
96 |
+ " where 1=1 and (foi.createTimestamp between :startDate and :endDate or foi.createTimestamp is null) and fs.code= :code and fs.id=:fofoId group by fo.id "),
|
| 97 |
|
97 |
|
| - |
|
98 |
@NamedQuery(name = "FofoOrder.selectFocoSaleReportByBrand", query = "select new com.spice.profitmandi.dao.model.FocoSaleReportModel(fs.code, ua.name,ua.city, ua.state, w.displayName, foi.itemId,"
|
| - |
|
99 |
+ " foi.brand, foi.modelName, foi.modelNumber, foi.color, foi.quantity, cast(foi.dp As int), cast(foi.sellingPrice As int), cast(foi.mop As int), fli.serialNumber, foi.createTimestamp, ca.name, ca.phoneNumber,"
|
| - |
|
100 |
+ " ca.city, ca.pinCode, fo.invoiceNumber, p.purchaseReference, fo.customerGstNumber, fo.cancelledTimestamp, p.completeTimestamp, hd.hygieneRating,hd.rating, hd.status, hd.remark,"
|
| - |
|
101 |
+ " hd.createdTimestamp, hd.disposedTimestamp, hd.nextTimestamp, ai.activationTimestamp, ai.createTimestamp, c.label)"
|
| - |
|
102 |
+ " from FofoStore fs join Warehouse w on w.id=fs.warehouseId join com.spice.profitmandi.dao.entity.user.User u on u.id=fs.id join Address ua on u.addressId = ua.id join FofoOrder fo on fo.fofoId=fs.id left join FofoOrderItem foi"
|
| - |
|
103 |
+ " on foi.orderId=fo.id join Item i on i.id=foi.itemId join Category c on c.id=i.categoryId left join CustomerAddress ca on ca.id=fo.customerAddressId left join FofoLineItem fli on fli.fofoOrderItemId=foi.id"
|
| - |
|
104 |
+ " left join InventoryItem ii on ii.id=fli.inventoryItemId left join Purchase p on ii.purchaseId=p.id left join HygieneData hd on hd.orderId=fo.id left join ActivatedImei ai on ai.serialNumber=fli.serialNumber"
|
| - |
|
105 |
+ " where (foi.createTimestamp between :startDate and :endDate or foi.createTimestamp is null) and fs.id=:fofoId and i.brand=:brand group by fo.id "),
|
| - |
|
106 |
|
| 98 |
@NamedQuery(name = "FofoOrder.selectWalletSummaryReport", query = "select new com.spice.profitmandi.dao.model.WalletSummaryReportModel(uwh.id, fs.code, ua.name, "
|
107 |
@NamedQuery(name = "FofoOrder.selectWalletSummaryReport", query = "select new com.spice.profitmandi.dao.model.WalletSummaryReportModel(uwh.id, fs.code, ua.name, "
|
| 99 |
+ " u.emailId, ua.phoneNumber, uwh.amount, uwh.refundableAmount, uwh.reference, uwh.referenceType, uwh.timestamp, uwh.businessTimestamp, uwh.description)"
|
108 |
+ " u.emailId, ua.phoneNumber, uwh.amount, uwh.refundableAmount, uwh.reference, uwh.referenceType, uwh.timestamp, uwh.businessTimestamp, uwh.description)"
|
| 100 |
+ " from FofoStore fs join com.spice.profitmandi.dao.entity.user.User u on u.id=fs.id join"
|
109 |
+ " from FofoStore fs join com.spice.profitmandi.dao.entity.user.User u on u.id=fs.id join"
|
| 101 |
+ " Address ua on u.addressId=ua.id join UserWallet uw on uw.userId=fs.id join UserWalletHistory uwh on uwh.walletId=uw.id "
|
110 |
+ " Address ua on u.addressId=ua.id join UserWallet uw on uw.userId=fs.id join UserWalletHistory uwh on uwh.walletId=uw.id "
|
| 102 |
+ " where 1=1 and uwh.timestamp between :startDate and :endDate and fs.id=:fofoId group by uwh.timestamp"),
|
111 |
+ " where 1=1 and uwh.timestamp between :startDate and :endDate and fs.id=:fofoId group by uwh.timestamp"),
|
| 103 |
|
112 |
|
| 104 |
|
113 |
|
| 105 |
@NamedQuery(name = "FofoOrder.selectPendingIndentReport", query = "select new com.spice.profitmandi.dao.model.PendingIndentReportModel(o.transactionId , o.id, o.createTimestamp,"
|
114 |
@NamedQuery(name = "FofoOrder.selectPendingIndentReport", query = "select new com.spice.profitmandi.dao.model.PendingIndentReportModel(o.transactionId , o.id, o.createTimestamp,"
|
| 106 |
+ " l.itemId, l.brand, l.modelName, l.modelNumber, l.color, l.quantity, l.unitPrice, o.walletAmount,case o.status when 3 then 'In Process' when 4 then 'Accepted' "
|
115 |
+ " l.itemId, l.brand, l.modelName, l.modelNumber, l.color, l.quantity, l.unitPrice, o.walletAmount,case o.status when 3 then 'In Process' when 4 then 'Accepted' "
|
| 107 |
+ " when 7 then 'Billed' when 9 then 'Shipped' when 12 then 'Delivered' when 13 then 'Cancellation in process' when 15 then 'Cancelled,amount refunded to wallet' "
|
116 |
+ " when 7 then 'Billed' when 9 then 'Shipped' when 12 then 'Delivered' when 13 then 'Cancellation in process' when 15 then 'Cancelled,amount refunded to wallet' "
|
| 108 |
+ " when 31 then 'Returned to Origin, refunded' when 34 then 'Low inventory cancellation, refunded' "
|
117 |
+ " when 31 then 'Returned to Origin, refunded' when 34 then 'Low inventory cancellation, refunded' "
|
| 109 |
+ " else o.statusDescription end as Description, o.invoiceNumber, o.billingTimestamp ) from Order o join LineItem l on l.orderId=o.id "
|
118 |
+ " else o.statusDescription end as Description, o.invoiceNumber, o.billingTimestamp ) from Order o join LineItem l on l.orderId=o.id "
|
| 110 |
+ " where o.partnerGrnTimestamp is null and o.retailerId= :fofoId and o.status != 1 and o.createTimestamp between :startDate and :endDate group by o.status,o.createTimestamp "),
|
119 |
+ " where o.partnerGrnTimestamp is null and o.retailerId= :fofoId and o.status != 1 and o.createTimestamp between :startDate and :endDate group by o.status,o.createTimestamp "),
|