| Line 46... |
Line 46... |
| 46 |
|
46 |
|
| 47 |
@NamedQuery(name = "FofoOrder.selectPartnersSaleByRange", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel(" + " fs.warehouseId, fs.id,sum(case when foi.quantity is null then 0 else foi.quantity end),i.brand,i.modelName,i.modelNumber,i.catalogItemId)" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId " + " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId " + " join Item i on i.id = tl.itemId" + " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null " + " and fo.createTimestamp > :startDate and tl.mop between :startPrice and :endPrice and i.categoryId=10006 group by fs.id,i.catalogItemId,i.brand,i.modelName,i.modelNumber"),
|
47 |
@NamedQuery(name = "FofoOrder.selectPartnersSaleByRange", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel(" + " fs.warehouseId, fs.id,sum(case when foi.quantity is null then 0 else foi.quantity end),i.brand,i.modelName,i.modelNumber,i.catalogItemId)" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId " + " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId " + " join Item i on i.id = tl.itemId" + " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null " + " and fo.createTimestamp > :startDate and tl.mop between :startPrice and :endPrice and i.categoryId=10006 group by fs.id,i.catalogItemId,i.brand,i.modelName,i.modelNumber"),
|
| 48 |
|
48 |
|
| 49 |
@NamedQuery(name = "FofoOrder.selectPartnersSaleItemByCatalogIdFofoId", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel(" + " fs.warehouseId, fs.id,sum(foi.quantity),i.brand,i.modelName,i.modelNumber,i.catalogItemId)" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId " + " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId " + " join Item i on i.id = tl.itemId" + " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null and fo.createTimestamp > :startDate and i.catalogItemId in :catalogItemId and i.categoryId=10006 and fs.id = :fofoId group by i.catalogItemId, i.brand,i.modelName,i.modelNumber"),
|
49 |
@NamedQuery(name = "FofoOrder.selectPartnersSaleItemByCatalogIdFofoId", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel(" + " fs.warehouseId, fs.id,sum(foi.quantity),i.brand,i.modelName,i.modelNumber,i.catalogItemId)" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId " + " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId " + " join Item i on i.id = tl.itemId" + " where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null and fo.createTimestamp > :startDate and i.catalogItemId in :catalogItemId and i.categoryId=10006 and fs.id = :fofoId group by i.catalogItemId, i.brand,i.modelName,i.modelNumber"),
|
| 50 |
|
50 |
|
| - |
|
51 |
@NamedQuery(name = "FofoOrder.selectSaleByCatalogId", query = "select new com.spice.profitmandi.dao.model.FofoIdCatalogQtyModel(" +
|
| - |
|
52 |
" fs.warehouseId, fs.id,i.catalogItemId, sum(case when foi.quantity is null then 0 else foi.quantity end))" +
|
| - |
|
53 |
" from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId " +
|
| - |
|
54 |
" join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId " +
|
| - |
|
55 |
" join Item i on i.id = tl.itemId" +
|
| - |
|
56 |
" where fs.active = 1 and fs.internal = 0 and fo.cancelledTimestamp is null and fo.createTimestamp > :startDate" +
|
| 51 |
@NamedQuery(name = "FofoOrder.selectSaleByCatalogId", query = "select new com.spice.profitmandi.dao.model.FofoIdCatalogQtyModel(" + " fs.warehouseId, fs.id,i.catalogItemId, sum(case when foi.quantity is null then 0 else foi.quantity end))" + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId " + " join FofoOrderItem foi on foi.orderId = fo.id join TagListing tl on tl.itemId = foi.itemId " + " join Item i on i.id = tl.itemId" + " 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"),
|
57 |
" and i.catalogItemId in :catalogItemId and fs.warehouseId in :warehouseId and i.categoryId=10006 group by i.catalogItemId, fs.id"),
|
| 52 |
|
58 |
|
| 53 |
|
59 |
|
| - |
|
60 |
@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," +
|
| - |
|
61 |
" 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," +
|
| - |
|
62 |
" ca.city, ca.pinCode, fo.invoiceNumber, p.purchaseReference, fo.customerGstNumber, fo.cancelledTimestamp, p.completeTimestamp, hd.hygieneRating,hd.rating, hd.status, hd.remark," +
|
| - |
|
63 |
" hd.createdTimestamp, hd.disposedTimestamp, hd.nextTimestamp, ai.activationTimestamp, ai.createTimestamp, c.label)" +
|
| 54 |
@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," + " 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," + " ca.city, ca.pinCode, fo.invoiceNumber, p.purchaseReference, fo.customerGstNumber, fo.cancelledTimestamp, p.completeTimestamp, hd.hygieneRating,hd.rating, hd.status, hd.remark," + " hd.createdTimestamp, hd.disposedTimestamp, hd.nextTimestamp, ai.activationTimestamp, ai.createTimestamp, c.label)" + " 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" + " 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" + " 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" + " 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 "),
|
64 |
" 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" + " 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" + " 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" + " 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 "),
|
| 55 |
|
65 |
|
| 56 |
@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," + " 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," + " ca.city, ca.pinCode, fo.invoiceNumber, p.purchaseReference, fo.customerGstNumber, fo.cancelledTimestamp, p.completeTimestamp, hd.hygieneRating,hd.rating, hd.status, hd.remark," + " hd.createdTimestamp, hd.disposedTimestamp, hd.nextTimestamp, ai.activationTimestamp, ai.createTimestamp, c.label)" + " 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" + " 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" + " 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" + " where (foi.createTimestamp between :startDate and :endDate or foi.createTimestamp is null) and fs.id=:fofoId and i.brand=:brand group by fo.id "),
|
66 |
@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," + " 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," + " ca.city, ca.pinCode, fo.invoiceNumber, p.purchaseReference, fo.customerGstNumber, fo.cancelledTimestamp, p.completeTimestamp, hd.hygieneRating,hd.rating, hd.status, hd.remark," + " hd.createdTimestamp, hd.disposedTimestamp, hd.nextTimestamp, ai.activationTimestamp, ai.createTimestamp, c.label)" + " 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" + " 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" + " 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" + " where (foi.createTimestamp between :startDate and :endDate or foi.createTimestamp is null) and fs.id=:fofoId and i.brand=:brand group by fo.id "),
|
| 57 |
|
67 |
|
| 58 |
@NamedQuery(name = "FofoOrder.selectWalletSummaryReport", query = "select new com.spice.profitmandi.dao.model.WalletSummaryReportModel(uwh.id, fs.code, ua.name, " + " u.emailId, ua.phoneNumber, uwh.amount, uwh.refundableAmount, uwh.reference, uwh.referenceType, uwh.timestamp, uwh.businessTimestamp, uwh.description)" + " from FofoStore fs join com.spice.profitmandi.dao.entity.user.User u on u.id=fs.id join" + " Address ua on u.addressId=ua.id join UserWallet uw on uw.userId=fs.id join UserWalletHistory uwh on uwh.walletId=uw.id " + " where 1=1 and uwh.timestamp between :startDate and :endDate and fs.id=:fofoId group by uwh.timestamp"),
|
68 |
@NamedQuery(name = "FofoOrder.selectWalletSummaryReport", query = "select new com.spice.profitmandi.dao.model.WalletSummaryReportModel(uwh.id, fs.code, ua.name, " + " u.emailId, ua.phoneNumber, uwh.amount, uwh.refundableAmount, uwh.reference, uwh.referenceType, uwh.timestamp, uwh.businessTimestamp, uwh.description)" + " from FofoStore fs join com.spice.profitmandi.dao.entity.user.User u on u.id=fs.id join" + " Address ua on u.addressId=ua.id join UserWallet uw on uw.userId=fs.id join UserWalletHistory uwh on uwh.walletId=uw.id " + " where 1=1 and uwh.timestamp between :startDate and :endDate and fs.id=:fofoId group by uwh.timestamp"),
|
| 59 |
|
69 |
|
| Line 62... |
Line 72... |
| 62 |
|
72 |
|
| 63 |
@NamedQuery(name = "FofoOrder.selectSchemePayoutReport", query = "select new com.spice.profitmandi.dao.model.SchemePayoutReportModel(i.id, i.brand, i.modelName, i.modelNumber," + " i.color,ii.unitPrice-ii.priceDropAmount,case when foi.dp is not null then foi.dp else 0 end, s.id, s.name, s.type, s.amountType, s.amount,p.purchaseReference, fo.invoiceNumber,(case when sio.rolledBackTimestamp is null " + " then sio.amount when sio.rolledBackTimestamp is not null then 0 end),sio.status, sio.statusDescription, sio.createTimestamp, sio.rolledBackTimestamp, ii.serialNumber ) from " + " 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 " + " 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" + " 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 between :startDate and :endDate " + " or ii.createTimestamp between :startDate and :endDate )" + "and ii.fofoId= :fofoId "),
|
73 |
@NamedQuery(name = "FofoOrder.selectSchemePayoutReport", query = "select new com.spice.profitmandi.dao.model.SchemePayoutReportModel(i.id, i.brand, i.modelName, i.modelNumber," + " i.color,ii.unitPrice-ii.priceDropAmount,case when foi.dp is not null then foi.dp else 0 end, s.id, s.name, s.type, s.amountType, s.amount,p.purchaseReference, fo.invoiceNumber,(case when sio.rolledBackTimestamp is null " + " then sio.amount when sio.rolledBackTimestamp is not null then 0 end),sio.status, sio.statusDescription, sio.createTimestamp, sio.rolledBackTimestamp, ii.serialNumber ) from " + " 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 " + " 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" + " 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 between :startDate and :endDate " + " or ii.createTimestamp between :startDate and :endDate )" + "and ii.fofoId= :fofoId "),
|
| 64 |
|
74 |
|
| 65 |
@NamedQuery(name = "FofoOrder.selectInvoiceSchemeOutSummaryReport", query = "select new com.spice.profitmandi.dao.model.FocoSchemeOutReportModel(fo.invoiceNumber, foi.quantity, foi.brand," + " 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 " + " 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" + " fo.createTimestamp between :startDate and :endDate and sio.rolledBackTimestamp is null group by fo.id, foi.id "),
|
75 |
@NamedQuery(name = "FofoOrder.selectInvoiceSchemeOutSummaryReport", query = "select new com.spice.profitmandi.dao.model.FocoSchemeOutReportModel(fo.invoiceNumber, foi.quantity, foi.brand," + " 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 " + " 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" + " fo.createTimestamp between :startDate and :endDate and sio.rolledBackTimestamp is null group by fo.id, foi.id "),
|
| 66 |
|
76 |
|
| 67 |
|
- |
|
| - |
|
77 |
//String sellerName, String hsnCode, String gstNumber, String stateName
|
| - |
|
78 |
@NamedQuery(name = "FofoOrder.selectPartnerBillingSummaryReport", query = "select new com.spice.profitmandi.dao.model.PartnerBillingSummaryModel(o.id, o.createTimestamp, o.billingTimestamp," +
|
| - |
|
79 |
" o.deliveryTimestamp, o.partnerGrnTimestamp, o.transactionId, case when o.logisticsTransactionId is null then 'NA' else o.logisticsTransactionId end,o.airwayBillNumber, " +
|
| - |
|
80 |
" 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," +
|
| - |
|
81 |
" l.igstRate, l.cgstRate, l.sgstRate, org.name, l.hsnCode, s.gstin, state.name )" +
|
| 68 |
@NamedQuery(name = "FofoOrder.selectPartnerBillingSummaryReport", query = "select new com.spice.profitmandi.dao.model.PartnerBillingSummaryModel(o.id, o.createTimestamp, o.billingTimestamp," + " o.deliveryTimestamp, o.partnerGrnTimestamp, o.transactionId, case when o.logisticsTransactionId is null then 'NA' else o.logisticsTransactionId end,o.airwayBillNumber, " + " 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," + " 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" + " join FofoStore fs on fs.id=o.retailerId where 1=1 and o.billingTimestamp between :startDate and :endDate and o.retailerId = :fofoId "),
|
82 |
" 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" +
|
| - |
|
83 |
" join FofoStore fs on fs.id=o.retailerId join Seller s on s.id=o.sellerId join State state on s.stateId join Organisation org on org.id=s.organisationId" +
|
| - |
|
84 |
" where 1=1 and o.billingTimestamp between :startDate and :endDate and o.retailerId = :fofoId "),
|
| 69 |
|
85 |
|
| 70 |
|
86 |
|
| 71 |
})
|
87 |
})
|
| 72 |
|
88 |
|
| 73 |
|
89 |
|