| Line 111... |
Line 111... |
| 111 |
+ " 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"),
|
| 112 |
|
112 |
|
| 113 |
|
113 |
|
| 114 |
@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,"
|
| 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' "
|
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' "
|
| 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' "
|
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' "
|
| 117 |
+ " 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' "
|
| 118 |
+ " 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 "
|
| 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 "),
|
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 "),
|
| 120 |
|
120 |
|
| 121 |
@NamedQuery(name = "FofoOrder.selectSchemePayoutReport", query = "select new com.spice.profitmandi.dao.model.SchemePayoutReportModel(i.id, i.brand, i.modelName, i.modelNumber,"
|
121 |
@NamedQuery(name = "FofoOrder.selectSchemePayoutReport", query = "select new com.spice.profitmandi.dao.model.SchemePayoutReportModel(i.id, i.brand, i.modelName, i.modelNumber,"
|
| 122 |
+ " i.color,ii.unitPrice-ii.priceDropAmount,foi.dp, s.id, s.name, s.type, s.amountType, s.amount,p.purchaseReference, fo.invoiceNumber,(case when sio.rolledBackTimestamp is null "
|
122 |
+ " i.color,ii.unitPrice-ii.priceDropAmount,foi.dp, s.id, s.name, s.type, s.amountType, s.amount,p.purchaseReference, fo.invoiceNumber,(case when sio.rolledBackTimestamp is null "
|
| 123 |
+ " then sio.amount when sio.rolledBackTimestamp is not null then 0 end),sio.status, sio.statusDescription, sio.createTimestamp, sio.rolledBackTimestamp, ii.serialNumber ) from "
|
123 |
+ " then sio.amount when sio.rolledBackTimestamp is not null then 0 end),sio.status, sio.statusDescription, sio.createTimestamp, sio.rolledBackTimestamp, ii.serialNumber ) from "
|
| 124 |
+ " SchemeInOut sio join InventoryItem ii on sio.inventoryItemId=ii.id join com.spice.profitmandi.dao.entity.catalog.Item i on i.id=ii.itemId join FofoStore fs on fs.id=ii.fofoId "
|
124 |
+ " SchemeInOut sio join InventoryItem ii on sio.inventoryItemId=ii.id join com.spice.profitmandi.dao.entity.catalog.Item i on i.id=ii.itemId join FofoStore fs on fs.id=ii.fofoId "
|
| 125 |
+ " join com.spice.profitmandi.dao.entity.user.User u on u.id = fs.id join Address ua on u.addressId = ua.id join Scheme s on sio.schemeId=s.id join Purchase p on p.id=ii.purchaseId"
|
125 |
+ " join com.spice.profitmandi.dao.entity.user.User u on u.id = fs.id join Address ua on u.addressId = ua.id join Scheme s on sio.schemeId=s.id join Purchase p on p.id=ii.purchaseId"
|
| 126 |
+ " left join FofoLineItem fli on fli.inventoryItemId=ii.id left join FofoOrderItem foi on foi.id=fli.fofoOrderItemId left join FofoOrder fo on fo.id=foi.orderId where foi.createTimestamp"
|
126 |
+ " left join FofoLineItem fli on fli.inventoryItemId=ii.id left join FofoOrderItem foi on foi.id=fli.fofoOrderItemId left join FofoOrder fo on fo.id=foi.orderId where " +
|
| 127 |
+ " between :startDate and :endDate and ii.fofoId= :fofoId "),
|
127 |
" foi.createTimestamp between :startDate and :endDate " +
|
| - |
|
128 |
" and ii.createTimestamp between :startDate and :endDate " +
|
| - |
|
129 |
"and i i.fofoId= :fofoId "),
|
| 128 |
|
130 |
|
| 129 |
@NamedQuery(name = "FofoOrder.selectInvoiceSchemeOutSummaryReport", query = "select new com.spice.profitmandi.dao.model.FocoSchemeOutReportModel(fo.invoiceNumber, foi.quantity, foi.brand,"
|
131 |
@NamedQuery(name = "FofoOrder.selectInvoiceSchemeOutSummaryReport", query = "select new com.spice.profitmandi.dao.model.FocoSchemeOutReportModel(fo.invoiceNumber, foi.quantity, foi.brand,"
|
| 130 |
+ " foi.modelName, foi.modelNumber, foi.color, sum(cast(sio.amount As float)) ) FROM FofoOrderItem foi join FofoOrder fo on fo.id=foi.orderId join FofoLineItem fli on "
|
132 |
+ " foi.modelName, foi.modelNumber, foi.color, sum(cast(sio.amount As float)) ) FROM FofoOrderItem foi join FofoOrder fo on fo.id=foi.orderId join FofoLineItem fli on "
|
| 131 |
+ " fli.fofoOrderItemId=foi.id join SchemeInOut sio on sio.inventoryItemId=fli.inventoryItemId join Scheme s on s.id=sio.schemeId where s.type ='IN' and fo.fofoId= :fofoId and"
|
133 |
+ " fli.fofoOrderItemId=foi.id join SchemeInOut sio on sio.inventoryItemId=fli.inventoryItemId join Scheme s on s.id=sio.schemeId where s.type ='IN' and fo.fofoId= :fofoId and"
|
| 132 |
+ " fo.createTimestamp between :startDate and :endDate and sio.rolledBackTimestamp is null group by fo.id, foi.id "),
|
134 |
+ " fo.createTimestamp between :startDate and :endDate and sio.rolledBackTimestamp is null group by fo.id, foi.id "),
|
| 133 |
|
135 |
|
| 134 |
|
136 |
|
| 135 |
@NamedQuery(name = "FofoOrder.selectPartnerBillingSummaryReport", query = "select new com.spice.profitmandi.dao.model.PartnerBillingSummaryModel(o.id, o.createTimestamp, o.billingTimestamp,"
|
137 |
@NamedQuery(name = "FofoOrder.selectPartnerBillingSummaryReport", query = "select new com.spice.profitmandi.dao.model.PartnerBillingSummaryModel(o.id, o.createTimestamp, o.billingTimestamp,"
|
| 136 |
+ " o.deliveryTimestamp, o.partnerGrnTimestamp, o.transactionId, case when o.logisticsTransactionId is null then 'NA' else o.logisticsTransactionId end,o.airwayBillNumber, "
|
138 |
+ " o.deliveryTimestamp, o.partnerGrnTimestamp, o.transactionId, case when o.logisticsTransactionId is null then 'NA' else o.logisticsTransactionId end,o.airwayBillNumber, "
|
| 137 |
+ " os.statusSubGroup, os.statusName, o.retailerId, o.retailerName, i.id, i.brand, i.modelName, i.modelNumber, i.color, l.unitPrice, l.quantity, l.totalPrice, o.invoiceNumber,"
|
139 |
+ " os.statusSubGroup, os.statusName, o.retailerId, o.retailerName, i.id, i.brand, i.modelName, i.modelNumber, i.color, l.unitPrice, l.quantity, l.totalPrice, o.invoiceNumber,"
|
| 138 |
+ " l.igstRate, l.cgstRate, l.sgstRate ) From Order o join LineItem l on l.orderId = o.id join Item i on l.itemId = i.id join Category c on i.categoryId = c.id join OrderStatus os on os.status=o.status"
|
140 |
+ " l.igstRate, l.cgstRate, l.sgstRate ) From Order o join LineItem l on l.orderId = o.id join Item i on l.itemId = i.id join Category c on i.categoryId = c.id join OrderStatus os on os.status=o.status"
|
| 139 |
+ " join FofoStore fs on fs.id=o.retailerId where 1=1 and o.billingTimestamp between :startDate and :endDate and o.retailerId = :fofoId "),
|
141 |
+ " join FofoStore fs on fs.id=o.retailerId where 1=1 and o.billingTimestamp between :startDate and :endDate and o.retailerId = :fofoId "),
|
| 140 |
|
142 |
|
| 141 |
})
|
143 |
})
|
| 142 |
public class FofoOrder implements Serializable {
|
144 |
public class FofoOrder implements Serializable {
|