Rev 35474 | Rev 35490 | Go to most recent revision | View as "text/plain" | Blame | Compare with Previous | Last modification | View Log | RSS feed
package com.spice.profitmandi.dao.entity.transaction;import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;import com.spice.profitmandi.dao.model.*;import com.spice.profitmandi.model.WarehouseItemQtyModel;import in.shop2020.model.v1.order.OrderStatus;import in.shop2020.model.v1.order.TaxType;import javax.persistence.*;import java.io.Serializable;import java.time.LocalDateTime;import java.time.format.DateTimeFormatter;import java.util.List;/*** This class basically contains order details** @author ashikali*/@Entity@Table(name = "transaction.`order`")@NamedQueries({@NamedQuery(name = "Order.selectAll", query = "select o from Order o"), @NamedQuery(name = "Order.selectById", query = "select o from Order o where o.id= :id"), @NamedQuery(name = "Order.selectByTransactionId", query = "select t.id, t.createTimestamp, o.retailerAddress1, o.retailerAddress2, o.retailerCity, " + "o.retailerPinCode, o.retailerState, o.shippingCost, o.statusDescription, o.invoiceNumber, o.airwayBillNumber, o.totalAmount, li.brand, li.modelName, " + "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 " + "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"),@NamedQuery(name = "Order.partnerStockPoAndGrnPending",query = "select new com.spice.profitmandi.dao.model.PoAndGrnPendingPartnerStockModel" +"(li.itemId, odr.partnerGrnTimestamp, odr.billingTimestamp) " +"FROM Order odr " +"JOIN LineItem li on li.orderId = odr.id " +"WHERE odr.retailerId = (:fofoId) " +"AND odr.refundTimestamp is null " +"AND (odr.billingTimestamp is null OR (odr.billingTimestamp is not null AND odr.partnerGrnTimestamp is null))"),@NamedQuery(name = "Order.selectItemIdTypeQuantity", query = "select new com.spice.profitmandi.dao.model.ItemIdTypeQuantity(li.itemId, i.type, li.quantity) from Order o join LineItem li on li.orderId = o.id join Item i on i.id = li.itemId where o.invoiceNumber = :invoiceNumber and o.retailerId = :retailerId"),@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderBillingModel(li.brand, " + "sum(case when o.billingTimestamp >= :today then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when concat(year(o.billingTimestamp ), month(o.billingTimestamp ))= :mtd then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between :lmtdStartDate and :lmtdEndDate then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between :lmtdStartDate and :lmsEndDate then CAST(o.totalAmount AS integer) else 0 end)," + " sum(case when o.billingTimestamp >= :today then li.quantity else 0 end)," + " sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then li.quantity else 0 end)," + " sum(case when concat(year(o.billingTimestamp), month(o.billingTimestamp))= :mtd then li.quantity else 0 end)," + " sum(case when o.billingTimestamp between :lmtdStartDate and :lmtdEndDate then li.quantity else 0 end)," + " sum(case when o.billingTimestamp between :lmtdStartDate and :lmsEndDate then li.quantity else 0 end)" + " )" + " 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)" + " and o.billingTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and fs.internal = false group by li.brand"),@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrandFofoId", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast(o.totalAmount AS integer)))" + " 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)" + " and o.billingTimestamp >= :startDate and o.retailerId =:fofoId group by li.brand,DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),@NamedQuery(name = "Order.selectAllBilledByCategoryOrderGroupByBrandFofoId", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast((li.unitPrice * li.quantity) AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId join Item i on i.id = li.itemId where o.billingTimestamp >= :startDate and o.billingTimestamp <= :endDate and o.retailerId =:fofoId and i.categoryId in (:categoryIds) group by li.brand,DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),@NamedQuery(name = "Order.selectSecondaryGroupByYearMonth", query = "select new com.spice.profitmandi.dao.model.PartnerMonthlySaleModel(o.retailerId," + " DATE_FORMAT(o.billingTimestamp, '%m-%Y'),sum(cast(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.billingTimestamp between :startDate and :endDate and o.retailerId in :fofoIds group by o.retailerId, DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),@NamedQuery(name = "Order.selectAllBilledOrderGroupByBrandWarehouse", query = "select new com.spice.profitmandi.dao.model.SecondaryWarehouseWiseOrderBilllingModel(fs.warehouseId,date(o.billingTimestamp), li.brand, " + "sum(CAST(o.totalAmount AS integer)),sum( li.quantity))" + " 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)" + " and o.billingTimestamp >= :startDate and o.billingTimestamp < :endDate and (null is :brand or li.brand = :brand) and fs.internal = 0 group by fs.warehouseId, date(o.billingTimestamp), li.brand"),@NamedQuery(name = "Order.selectAllBilledOrderItemByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderItemBillingModel(o.retailerName,li.brand,i.modelName, " + " i.modelNumber, i.color," + "o.totalAmount," + " li.quantity)" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and date(o.billingTimestamp) in :date and fs.warehouseId in :warehouseId and li.brand = :brand and fs.internal = false order by li.itemId desc"),@NamedQuery(name = "Order.selectTodayBilledOrderItemByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderItemBillingModel(o.retailerName,li.brand,i.modelName, " + " i.modelNumber, i.color," + "o.totalAmount," + " li.quantity)" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and fs.warehouseId in :warehouseId and li.brand in :brand order by li.itemId desc"),@NamedQuery(name = "Order.selectAllBilledOrderBrandByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockBrandModel(li.brand," + " Sum(li.quantity),Sum(CAST(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.billingTimestamp <= :endDate and o.retailerId = :fofoId group by li.brand"),@NamedQuery(name = "Order.selectAllBilledOrderBrandItemByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockBrandItemModel(" + " i.brand, i.modelName, i.modelNumber, i.color,Sum(li.quantity), Sum(CAST(o.totalAmount AS integer)))" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId where o.status in (7,9,10,12)" + " and o.billingTimestamp >= :startDate and o.billingTimestamp <= :endDate and o.retailerId = :fofoId group by i.id"),@NamedQuery(name = "Order.selectAllGrnPendingOrderByCatalogId", query = "select new com.spice.profitmandi.dao.model.FofoIdQtyModel(" + " fs.warehouseId,fs.id,sum(li.quantity )) from FofoStore fs join Order o on fs.id = o.retailerId " + " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId " + " join Item i on (i.id = tl.itemId ) where " + " fs.active = false and fs.internal = true and" + " o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null" + " and i.catalogItemId in :catalogItemId group by fs.id"),@NamedQuery(name = "Order.selectAllPendingIndentOrderByCatalogId", query = "select new com.spice.profitmandi.dao.model.FofoIdQtyModel(" + " fs.warehouseId,fs.id,sum(case when li.quantity is null then 0 else li.quantity end))" + " from FofoStore fs join Order o on fs.id = o.retailerId" + " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId " + " join Item i on i.id = tl.itemId where " + " fs.active = false and fs.internal = true and o.status in (3,4) and i.catalogItemId in :catalogItemId group by fs.id"),@NamedQuery(name = "Order.selectGrnPendingOrdersByCatalogId", query = "select new com.spice.profitmandi.dao.model.FofoIdCatalogQtyModel(" + " fs.warehouseId,fs.id,i.catalogItemId,sum(li.quantity )) from FofoStore fs join Order o on fs.id = o.retailerId " + " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId " + " join Item i on (i.id = tl.itemId ) where " + " fs.active = true and fs.internal = false and" + " o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null" + " and i.catalogItemId in :catalogItemId and fs.warehouseId in :warehouseId group by i.catalogItemId, fs.id"),@NamedQuery(name = "Order.selectPendingIndentOrdersByCatalogId", query = "select new com.spice.profitmandi.dao.model.FofoIdCatalogQtyModel(" + " fs.warehouseId,fs.id,i.catalogItemId,sum(case when li.quantity is null then 0 else li.quantity end))" + " from FofoStore fs join Order o on fs.id = o.retailerId" + " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId " + " join Item i on i.id = tl.itemId where " + " fs.active = true and fs.internal = false and o.status in (3,4) and i.catalogItemId in :catalogItemId and fs.warehouseId in :warehouseId group by i.catalogItemId, fs.id"),@NamedQuery(name = "Order.selectFirstBilling", query = "select new com.spice.profitmandi.dao.model.FofoFirstBillingModel(fs.id, min(o.billingTimestamp) )" + " from FofoStore fs join Order o on fs.id = o.retailerId group by fs.id"),@NamedQuery(name = "Order.selectAllGrnPendingOrderByRange", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel(" + " fs.warehouseId,fs.id,sum(li.quantity ),i.brand,i.modelName,i.modelNumber,i.catalogItemId) from FofoStore fs join Order o on fs.id = o.retailerId " + " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId " + " join Item i on (i.id = tl.itemId ) where fs.active = true and fs.internal = false and" + " o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null" + " and tl.mop between :startPrice and :endPrice and i.categoryId=10006 group by fs.id,i.catalogItemId,i.brand,i.modelName,i.modelNumber"),// query = "select o.*,tl.sellingPrice as tlSellingPrice" +// "from FofoStore fs " +@NamedQuery(name = "Order.selectGrnPendingByFofoIdForAllMobile",query = "select o " +"from FofoStore fs " +"join Order o on fs.id = o.retailerId " +"join LineItem li on o.id = li.orderId " +"join TagListing tl on tl.itemId = li.itemId " +"join Item i on i.id = tl.itemId " +"where fs.active = true and fs.internal = false " +"and fs.id = :fofoId " +"and o.billingTimestamp is not null " +"and o.refundTimestamp is null " +"and o.partnerGrnTimestamp is null " +"and i.categoryId = 10006 " +"group by o.id"),@NamedQuery(name = "Order.selectAllPendingIndentOrderByRange", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel(" + " fs.warehouseId, fs.id,sum(case when li.quantity is null then 0 else li.quantity end),i.brand,i.modelName,i.modelNumber,i.catalogItemId)" + " from FofoStore fs join Order o on fs.id = o.retailerId" + " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId " + " join Item i on i.id = tl.itemId where " + " fs.active = true and fs.internal = false and o.status in (3,4) and tl.mop between :startPrice and :endPrice and i.categoryId=10006 group by fs.id,i.catalogItemId,i.brand,i.modelName,i.modelNumber"),@NamedQuery(name = "Order.selectAllGrnPendingOrderItemByCatalogIdFofoId", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel(" + " fs.warehouseId, fs.id, sum(li.quantity), i.brand, i.modelName, i.modelNumber, i.catalogItemId) from FofoStore fs join Order o on fs.id = o.retailerId " + " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId " + " join Item i on (i.id = tl.itemId ) where " + " fs.active = true and fs.internal = false and" + " o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null" + " and i.catalogItemId in :catalogItemId and i.categoryId=10006 and fs.id = :fofoId group by i.catalogItemId, i.brand,i.modelName,i.modelNumber"),@NamedQuery(name = "Order.selectAllPendingIndentOrderItemByCatalogIdFofoId", query = "select new com.spice.profitmandi.dao.model.FofoIdItemDetailModel(" + " fs.warehouseId, fs.id, sum(li.quantity), i.brand, i.modelName, i.modelNumber, i.catalogItemId)" + " from FofoStore fs join Order o on fs.id = o.retailerId" + " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId " + " join Item i on i.id = tl.itemId where " + " fs.active = true and fs.internal = false 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"),@NamedQuery(name = "Order.selectGroupByBrandLmp", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(li.brand," + " DATE_FORMAT(o.billingTimestamp,:groupParameter ),sum(cast(o.totalAmount AS integer)))" + " 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)" + " and (o.billingTimestamp between :lmsStartDate and :endDate) and fs.id in :fofoId and fs.warehouseId in :warehouseId and fs.internal = false group by li.brand,DATE_FORMAT(o.billingTimestamp, :groupParameter)"),@NamedQuery(name = "Order.selectGrnPendingOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)" + " from FofoStore fs join Order o on fs.id = o.retailerId " + " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId " + " join Item i on (i.id = tl.itemId ) where fs.active = true and fs.internal = false and" + " o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null" + " and i.catalogItemId = :catalogItemId and fs.id = :fofoId group by i.catalogItemId, fs.id"),@NamedQuery(name = "Order.selectPendingIndentOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)" + " from FofoStore fs join Order o on fs.id = o.retailerId" + " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId " + " join Item i on i.id = tl.itemId where " + " fs.active = true and fs.internal = false and o.status in (3,4)and i.catalogItemId = :catalogItemId and fs.id = :fofoId group by i.catalogItemId, fs.id"),@NamedQuery(name = "Order.selectTodayOrders", query = "select new com.spice.profitmandi.dao.model.BrandRegionPOModel(" + " case when (pr.regionId is not null) then pr.regionId else fs.warehouseId end , li.brand, " + " sum(case when (o.createTimestamp between :startDate and :endDate and o.refundTimestamp is null) then cast(o.totalAmount AS integer ) else 0 end)," + " sum(case when (o.billingTimestamp is not null and (o.createTimestamp between :startDate and :endDate)) then cast(o.totalAmount AS integer) else 0 end)," + " sum(case when o.billingTimestamp is not null then cast(o.totalAmount AS integer ) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId" + " left join PartnerRegion pr on pr.fofoId=o.retailerId and pr.regionId=18" + " where o.refundTimestamp is null and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate)) and fs.internal = false and li.brand not in ('Smartdukaan','Dummy')" + " group by case when (pr.regionId is not null) then pr.regionId else fs.warehouseId end, li.brand"),/* @NamedQuery(name = "Order.selectTodayOrdersRBM", query = "select new com.spice.profitmandi.dao.model.BrandAuthUserPOModel( p.authUserId," +" li.brand," +" sum(case" +" when (o.createTimestamp between :startDate and :endDate and o.refundTimestamp is null) then cast(o.totalAmount AS integer )" +" else 0 end)," +" sum(case" +" when o.billingTimestamp is not null and (o.createTimestamp between :startDate and :endDate)" +" then cast(o.totalAmount AS integer )" +" else 0 end)," +" sum(case when o.billingTimestamp is not null then cast(o.totalAmount AS integer ) else 0 end)," +" cast (count(distinct o.retailerId) as int))" +"from Order o" +" join LineItem li" +" on o.id = li.orderId" +" join FofoStore fs on fs.id = o.retailerId" +" join PartnerPosition pp on pp.fofoId = o.retailerId" +" join Position p on p.id = pp.positionId" +"where o.refundTimestamp is null" +" and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate))" +" and fs.internal = false" +" and p.categoryId = :categoryId" +" and p.escalationType = :escalationType" +" and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +"group by p.authUserId, li.brand"),*/@NamedQuery(name = "Order.selectTodayPartnersBilledRBM", query = "select new com.spice.profitmandi.dao.model.AuthIdPartnerCountModel( p.authUserId," +" cast (count(distinct o.retailerId) as int))" +"from Order o" +" join LineItem li" +" on o.id = li.orderId" +" join FofoStore fs on fs.id = o.retailerId" +" join PartnerPosition pp on pp.fofoId = o.retailerId" +" join Position p on p.id = pp.positionId" +" where o.refundTimestamp is null" +" and (o.createTimestamp between :startDate and :endDate)" +" and fs.internal = false" +" and p.categoryId = 18" +" and p.escalationType = 'L1'" +" and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +"group by p.authUserId"),@NamedQuery(name = "Order.selectAllPendingIndentAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel(" + " o.retailerId, SUM(case when c.parentCategoryId = 10011 then CAST(o.totalAmount AS integer) else 0 end), SUM(case when c.parentCategoryId = 14200 then CAST(o.totalAmount AS integer) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join Category c on c.id = i.categoryId " + " where o.retailerId in :fofoIds and o.status in (3,4) and i.categoryId != 10006 group by o.retailerId"),@NamedQuery(name = "Order.selectAllGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvFofoIdModel(" + " o.retailerId, SUM(case when c.parentCategoryId = 10011 then CAST(o.totalAmount AS integer) else 0 end), SUM(case when c.parentCategoryId = 14200 then CAST(o.totalAmount AS integer) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join Item i on i.id = li.itemId join Category c on c.id = i.categoryId " + " where o.retailerId in :fofoIds and o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null and i.categoryId != 10006 group by o.retailerId"),@NamedQuery(name = "Order.selectPartnersBilledBetweenDates", query = "select new com.spice.profitmandi.dao.model.PartnerSecondaryModel(fs.warehouseId, fs.id, count(o.id), sum(o.totalAmount) )" + " from FofoStore fs left join Order o on (fs.id=o.retailerId and o.billingTimestamp between :startDate and :endDate " + " and o.refundTimestamp is null) where" + " fs.internal = false and (fs.active= true or o.retailerId is not null) group by fs.warehouseId, fs.id"),@NamedQuery(name = "Order.selectCollectionSummary", query = "select new com.spice.profitmandi.dao.model.CollectionSummary(pot.createTimestamp, " + " pot.referenceType,sum(case when po.name = 'CASH' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'PINELABS' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'BAJAJ FINSERV' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'HOME CREDIT' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'PAYTM' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'CAPITAL FIRST' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'ZEST MONEY' then cast(pot.amount As int) else 0 end)," + " sum(case when po.name = 'SAMSUNG SURE' then cast(pot.amount As int) else 0 end),sum(cast(pot.amount As int)))" + " 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" + " join Address a on a.id=u.addressId where 1=1 and pot.fofoId = :fofoId and pot.createTimestamp between :startDate and :endDate " + " group by (pot.createTimestamp), pot.referenceType "),@NamedQuery(name = "Order.getLastOrderByFofoId", query = "select COALESCE(Max(o.id),0) from Order o where o.retailerId = :fofoId and o.status in (7,9,10,12)"),@NamedQuery(name = "Order.selectOrderValueByCreateDateGroupByFofoId", query = "select new com.spice.profitmandi.common.model.IdAmountModel(o.retailerId, sum(o.totalAmount)) from Order o where o.retailerId in :retailerIds and o.status in :status and o.createTimestamp between :startDate and :endDate group by o.retailerId"),@NamedQuery(name = "Order.selectOrderValueByBillingDateGroupByFofoId", query = "select new com.spice.profitmandi.common.model.IdAmountModel(o.retailerId, sum(o.totalAmount)) from Order o where o.retailerId in :retailerIds and o.billingTimestamp between :startDate and :endDate group by o.retailerId"),@NamedQuery(name = "Order.selectOrdersByBilledDateCatalogId",query = "select new com.spice.profitmandi.common.model.GrnPendingDataModel(" +"o.retailerId, fs.code, o.retailerName, o.invoiceNumber, o.billingTimestamp, l.itemId, l.brand, l.modelName, l.modelNumber, l.color, " +"l.unitPrice, lii.serialNumber, v.name, wi.created, ai.activationTimestamp, ai.createTimestamp)" +"from Order o join FofoStore fs on fs.id = o.retailerId" +" join LineItem l on l.orderId = o.id" +" join LineItemImei lii on lii.lineItemId = l.id" +" join Warehouse w on w.id = o.fulfilmentWarehouseId" +" join Vendor v on v.id = w.vendor" +" join WarehouseInventoryItem wi on wi.serialNumber = lii.serialNumber" +" join WarehouseScan ws on (ws.orderId = o.id and ws.type = 'SALE' and wi.id = ws.inventoryItemId)" +" join Item i on i.id=l.itemId " +" left join ActivatedImei ai on ai.serialNumber = lii.serialNumber " +"where fs.internal=false and i.catalogItemId=:catalogId and o.billingTimestamp >= :startDate " +"and (:endDate is null or o.billingTimestamp < :endDate) and (o.refundTimestamp is null or o.refundTimestamp <= :endDate)"),@NamedQuery(name = "Order.selectAllLiveDemoBrandItemsByBillingDate",query = "select new com.spice.profitmandi.dao.model.LiveDemoOrderLineItemModel (" +"o.id, " +"o.invoiceNumber, " +"o.billingTimestamp, " +"o.retailerName, " +"o.retailerCity, " +"o.retailerPinCode, " +"o.retailerState, " +"lii.serialNumber, " +"li.modelNumber, " +"li.modelName, " +"li.brand," +"li.itemId )" +"FROM Order o JOIN LineItem li on o.id = li.orderId " +"JOIN LineItemImei lii on li.id = lii.lineItemId " +"WHERE li.brand = 'Live Demo' " +"AND o.billingTimestamp between :startDate and :endDate"),@NamedQuery(name = "Order.selectTodayPocoBilling",query = "select new com.spice.profitmandi.dao.model.LiveDemoOrderLineItemModel (" +"o.id, " +"o.invoiceNumber, " +"o.billingTimestamp, " +"o.retailerName, " +"o.retailerCity, " +"o.retailerPinCode, " +"o.retailerState, " +"lii.serialNumber, " +"li.modelNumber, " +"li.modelName, " +"li.brand," +"li.itemId )" +"FROM Order o JOIN LineItem li on o.id = li.orderId " +"JOIN LineItemImei lii on li.id = lii.lineItemId " +"JOIN FofoStore fs on fs.id=o.retailerId "+"WHERE li.brand = 'POCO' " +"AND fs.internal = false " +"AND o.billingTimestamp between :startDate and :endDate")})//@NamedNativeQuery(name = "Order.findByEmailAddress", query = "select * from transaction.`order` o where o.id = '1694173'", resultClass = Order.class)@NamedNativeQueries({@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"),@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"),@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,pdi.credit_timestamp,pdi.reject_timestamp"+ " 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"),@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"),@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"),@NamedNativeQuery(name = "Order.getLastOrders", query = "select o.customer_id, Max(o.created_timestamp) as maxdate from transaction.`order` o join fofo.fofo_store fs on fs.id = o.customer_id where o.status in (7,9,10,12) and o.delivery_timestamp is not null and fs.internal = 0 group by o.customer_id", resultSetMapping = "LastSaleDays"),@NamedNativeQuery(name = "Order.itemAvailability", query = "select grouped.item_id, " +" grouped.vendor_warehouse_id, " +" sum(grouped.availability) as availability, " +" sum(grouped.reserved) as reserved, " +" sum(grouped.availability - grouped.reserved) as net_availability " +"from (select l.item_id, " +" o.fulfilmentWarehouseId as vendor_warehouse_id, " +" 0 as availability, " +" sum(l.quantity) as reserved " +" from fofo.fofo_store fs " +" join transaction.order o on o.customer_id = fs.id " +" join transaction.lineitem l on l.order_id = o.id " +" join catalog.item i on i.id = l.item_id " +" where o.status in (3, 4) " +" and l.item_id in :itemIds " +" and o.warehouse_id = :warehouseId " +" group by l.item_id, o.warehouse_id, o.fulfilmentWarehouseId " +" union " +" select ii.itemId AS item_id, " +" ii.currentWarehouseId, " +" sum((case " +" when (s2.type in ('SALE', 'LOST_IN_WAREHOUSE', 'PURCHASE_RETURN', 'MARKED_USED', 'MARKED_BAD')) " +" then -(s2.quantity) " +" when (s2.type in ('PURCHASE', 'SALE_RET', 'MARKED_GOOD')) then s2.quantity end)) AS availability, " +" 0 as reserved " +" from ((warehouse.scanNew s1 join warehouse.scanNew s2 " +" on ((s1.inventoryItemId = s2.inventoryItemId))) join warehouse.inventoryItem ii " +" on ((ii.id = s2.inventoryItemId)) join catalog.item i on i.id = ii.itemId) " +" where ((s1.type = 'PURCHASE') and (s1.scannedAt >= '2017-07-01')) " +" and ii.itemId in :itemIds " +" and ii.physicalWarehouseId = :warehouseId " +" group by ii.itemId, ii.currentWarehouseId) as grouped " +"group by item_id, vendor_warehouse_id", resultSetMapping = "itemAvailabilityReservationMapping"),@NamedNativeQuery(name = "Order.selectTodayOrdersRBM",query = "SELECT auth_user_id, brand, " +" SUM(today_po_value) AS today_po_value, " +" SUM(today_billed_value) AS today_billed_value, " +" SUM(total_billed_value) AS total_billed_value, " +" COUNT(DISTINCT customer_id) AS partner_count " +"FROM ( " +" SELECT p.auth_user_id, li.brand, o.customer_id, " +" CASE WHEN o.refund_timestamp IS NULL THEN o.total_amount ELSE 0 END AS today_po_value, " +" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS today_billed_value, " +" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS total_billed_value " +" FROM transaction.`order` o " +" JOIN transaction.lineitem li ON o.id = li.order_id " +" JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +" JOIN cs.partner_position ps ON ps.partner_id = fs.id " +" JOIN cs.position p ON p.id = ps.position_id AND p.category_id = :categoryId AND p.escalation_type = :escalationType " +" WHERE o.refund_timestamp IS NULL AND o.created_timestamp BETWEEN :startDate AND :endDate " +" AND fs.internal = FALSE AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +" UNION ALL " +" SELECT p.auth_user_id, li.brand, o.customer_id, 0, 0, " +" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END " +" FROM transaction.`order` o " +" JOIN transaction.lineitem li ON o.id = li.order_id " +" JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +" JOIN cs.partner_position ps ON ps.partner_id = fs.id " +" JOIN cs.position p ON p.id = ps.position_id AND p.category_id = :categoryId AND p.escalation_type = :escalationType " +" WHERE o.refund_timestamp IS NULL AND o.billing_timestamp BETWEEN :startDate AND :endDate " +" AND o.created_timestamp NOT BETWEEN :startDate AND :endDate " +" AND fs.internal = FALSE AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +" UNION ALL " +" SELECT p.auth_user_id, li.brand, o.customer_id, " +" CASE WHEN o.refund_timestamp IS NULL THEN o.total_amount ELSE 0 END, " +" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END, " +" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END " +" FROM transaction.`order` o " +" JOIN transaction.lineitem li ON o.id = li.order_id " +" JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +" JOIN cs.partner_region pr ON pr.fofo_id = fs.id " +" JOIN cs.partner_position ps ON ps.partner_id = 0 AND ps.region_id = pr.region_id " +" JOIN cs.position p ON p.id = ps.position_id AND p.category_id = :categoryId AND p.escalation_type = :escalationType " +" WHERE o.refund_timestamp IS NULL AND o.created_timestamp BETWEEN :startDate AND :endDate " +" AND fs.internal = FALSE AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +" UNION ALL " +" SELECT p.auth_user_id, li.brand, o.customer_id, 0, 0, " +" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END " +" FROM transaction.`order` o " +" JOIN transaction.lineitem li ON o.id = li.order_id " +" JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +" JOIN cs.partner_region pr ON pr.fofo_id = fs.id " +" JOIN cs.partner_position ps ON ps.partner_id = 0 AND ps.region_id = pr.region_id " +" JOIN cs.position p ON p.id = ps.position_id AND p.category_id = :categoryId AND p.escalation_type = :escalationType " +" WHERE o.refund_timestamp IS NULL AND o.billing_timestamp BETWEEN :startDate AND :endDate " +" AND o.created_timestamp NOT BETWEEN :startDate AND :endDate " +" AND fs.internal = FALSE AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +") AS combined " +"GROUP BY auth_user_id, brand " +"ORDER BY auth_user_id, brand",resultSetMapping = "TodayPoByAuthUsers"),@NamedNativeQuery(name = "Order.selectTodayOrdersModelWise",query = "SELECT o.created_timestamp,o.customer_name,fs.code,fs.warehouse_id, li.brand,concat(i.model_name, ' ', i.model_number) as modal,i.catalog_item_id ,coalesce(cc.status, '-') as status,sum(li.quantity) as quantity," +" SUM(" +" CASE" +" WHEN (o.created_timestamp BETWEEN :startDate AND :endDate and o.refund_timestamp is null)" +" AND o.refund_timestamp IS NULL" +" THEN o.total_amount" +" ELSE 0" +" END" +" ) AS today_po_value," +" SUM(" +" CASE" +" WHEN o.billing_timestamp IS NOT NULL" +" AND (o.created_timestamp BETWEEN :startDate AND :endDate)" +" THEN o.total_amount" +" ELSE 0" +" END" +" ) AS today_billed_value" +" FROM transaction.`order` o" +" JOIN transaction.lineitem li" +" ON o.id = li.order_id" +" JOIN fofo.fofo_store fs ON fs.id = o.customer_id" +" join catalog.item i on i.id = li.item_id" +" join catalog.catagoriesd_catalog cc on i.catalog_item_id = cc.catalog_id AND cc.end_date IS NULL" +" WHERE o.refund_timestamp IS NULL" +" AND (" +" (o.created_timestamp BETWEEN :startDate AND :endDate)" +" OR (o.billing_timestamp BETWEEN :startDate AND :endDate)" +" )" +" AND fs.internal = FALSE" +" AND fs.active = TRUE" +" AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +"GROUP BY o.customer_id, i.catalog_item_id",resultSetMapping = "TodayPoByCatalog"),@NamedNativeQuery(name = "Order.selectTodayOrdersNative",query = "SELECT " +" COALESCE(pr.region_id, fs.warehouse_id) AS warehouse_id, " +" combined.brand, " +" SUM(combined.today_po_value) AS today_po_value, " +" SUM(combined.today_billed_value) AS today_billed_value, " +" SUM(combined.total_billed_value) AS total_billed_value " +"FROM ( " +" SELECT " +" o.customer_id, " +" li.brand, " +" CASE WHEN o.refund_timestamp IS NULL THEN o.total_amount ELSE 0 END AS today_po_value, " +" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS today_billed_value, " +" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS total_billed_value " +" FROM transaction.`order` o " +" JOIN transaction.lineitem li ON o.id = li.order_id " +" JOIN fofo.fofo_store fs2 ON fs2.id = o.customer_id " +" WHERE o.refund_timestamp IS NULL " +" AND o.created_timestamp BETWEEN :startDate AND :endDate " +" AND fs2.internal = FALSE " +" AND li.brand NOT IN ('Smartdukaan', 'Dummy') " +" UNION ALL " +" SELECT " +" o.customer_id, " +" li.brand, " +" 0 AS today_po_value, " +" 0 AS today_billed_value, " +" CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS total_billed_value " +" FROM transaction.`order` o " +" JOIN transaction.lineitem li ON o.id = li.order_id " +" JOIN fofo.fofo_store fs2 ON fs2.id = o.customer_id " +" WHERE o.refund_timestamp IS NULL " +" AND o.billing_timestamp BETWEEN :startDate AND :endDate " +" AND o.created_timestamp NOT BETWEEN :startDate AND :endDate " +" AND fs2.internal = FALSE " +" AND li.brand NOT IN ('Smartdukaan', 'Dummy') " +") AS combined " +"JOIN fofo.fofo_store fs ON fs.id = combined.customer_id " +"LEFT JOIN cs.partner_region pr ON pr.fofo_id = combined.customer_id AND pr.region_id = 18 " +"GROUP BY COALESCE(pr.region_id, fs.warehouse_id), combined.brand " +"ORDER BY warehouse_id, combined.brand",resultSetMapping = "TodayPoByRegion")})@SqlResultSetMappings({@SqlResultSetMapping(name = "itemAvailabilityReservationMapping", classes = {@ConstructorResult(targetClass = WarehouseItemQtyModel.class,columns = {@ColumnResult(name = "item_id", type = Integer.class), @ColumnResult(name = "vendor_warehouse_id", type = Integer.class),@ColumnResult(name = "availability", type = Integer.class),@ColumnResult(name = "reserved", type = Integer.class), @ColumnResult(name = "net_availability", type = Integer.class)})}),@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)})}),@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)})}),@SqlResultSetMapping(name = "BillingDaysAvg", classes = {@ConstructorResult(targetClass = BillingDaysAvg.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "average", type = Long.class)})}),@SqlResultSetMapping(name = "LastSaleDays", classes = {@ConstructorResult(targetClass = LastSaleDateModel.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "maxdate", type = LocalDateTime.class)})}),@SqlResultSetMapping(name = "TodayPoByAuthUsers",classes = @ConstructorResult(targetClass = BrandAuthUserPOModel.class,columns = {@ColumnResult(name = "auth_user_id", type = Integer.class),@ColumnResult(name = "brand", type = String.class),@ColumnResult(name = "today_po_value", type = Long.class),@ColumnResult(name = "today_billed_value", type = Long.class),@ColumnResult(name = "total_billed_value", type = Long.class),@ColumnResult(name = "partner_count", type = Integer.class)})),@SqlResultSetMapping(name = "TodayPoByCatalog",classes = @ConstructorResult(targetClass = ModelWisePartnerPoModel.class,columns = {@ColumnResult(name = "created_timestamp", type = LocalDateTime.class),@ColumnResult(name = "customer_name", type = String.class),@ColumnResult(name = "code", type = String.class),@ColumnResult(name = "warehouse_id", type = Integer.class),@ColumnResult(name = "brand", type = String.class),@ColumnResult(name = "modal", type = String.class),@ColumnResult(name = "catalog_item_id", type = Integer.class),@ColumnResult(name = "status", type = String.class),@ColumnResult(name = "quantity", type = Long.class),@ColumnResult(name = "today_po_value", type = Long.class),@ColumnResult(name = "today_billed_value", type = Long.class)})),@SqlResultSetMapping(name = "TodayPoByRegion",classes = @ConstructorResult(targetClass = BrandRegionPOModel.class,columns = {@ColumnResult(name = "warehouse_id", type = Integer.class),@ColumnResult(name = "brand", type = String.class),@ColumnResult(name = "today_po_value", type = Long.class),@ColumnResult(name = "today_billed_value", type = Long.class),@ColumnResult(name = "total_billed_value", type = Long.class)}))})public class Order implements Serializable {private static final long serialVersionUID = 1L;@Id@Column(name = "id", unique = true, updatable = false)@GeneratedValue(strategy = GenerationType.IDENTITY)private Integer id;@Column(name = "warehouse_id")private Integer warehouseId;@Column(name = "seller_id")private Integer sellerId;@Column(name = "warehouse_address_id")private Integer warehouseAddressId;@Column(name = "logistics_provider_id")private Integer logisticsProviderId;@Column(name = "airwaybill_no", length = 50)private String airwayBillNumber;@Column(name = "tracking_id", length = 50)private String trackingId;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "expected_delivery_time")private LocalDateTime expectedDeliveryTime;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "promised_delivery_time")private LocalDateTime promisedDeliveryTime;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "expected_shipping_time")private LocalDateTime expectedShippingTime;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "promised_shipping_time")private LocalDateTime promisedShippingTime;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "partner_grn_timestamp")private LocalDateTime partnerGrnTimestamp;@Transientprivate List<String> returnedImeis;@Column(name = "customer_id")private Integer retailerId;@Column(name = "customer_name", length = 50)private String retailerName;@Column(name = "customer_mobilenumber", length = 20)private String retailerMobileNumber;@Column(name = "customer_pincode", length = 10)private String retailerPinCode;@Column(name = "customer_address1", length = 100)private String retailerAddress1;@Column(name = "customer_address2", length = 100)private String retailerAddress2;@Column(name = "customer_city", length = 100)private String retailerCity;@Column(name = "customer_state", length = 100)private String retailerState;@Column(name = "customer_email", length = 50)private String retailerEmailId;@Column(name = "status")@Enumerated(EnumType.ORDINAL)private OrderStatus status;@Column(name = "statusDescription", length = 50)private String statusDescription;@Column(name = "total_amount")private Float totalAmount;@Column(name = "gvAmount")private Float gvAmount;@Column(name = "total_weight")private Float totalWeight;@Column(name = "invoice_number", length = 30)private String invoiceNumber;@Column(name = "billed_by", length = 30)private String billedBy;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "created_timestamp")private LocalDateTime createTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "accepted_timestamp")private LocalDateTime acceptedTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "billing_timestamp")private LocalDateTime billingTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "shipping_timestamp")private LocalDateTime shippingTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "pickup_timestamp")private LocalDateTime pickupTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "delivery_timestamp")private LocalDateTime deliveryTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "outofstock_timestamp")private LocalDateTime outOfStockTimestamp;@Column(name = "transaction_id")private Integer transactionId;@Column(name = "receiver", length = 50)private String receiver;@Column(name = "batchNo")private Integer batchNumber;@Column(name = "serialNo")private Integer serialNumber;@Column(name = "doaFlag", columnDefinition = "tinyInteger(1) default 0")private Boolean doaFlag;@Column(name = "pickupRequestNo")private Integer pickupRequestNumber;@Column(name = "jacket_number", length = 10)private Long jacketNumber;public Boolean getDoaFlag() {return doaFlag;}public Long getJacketNumber() {return jacketNumber;}public void setJacketNumber(Long jacketNumber) {this.jacketNumber = jacketNumber;}public Boolean getCod() {return cod;}public Boolean getVendorPaid() {return vendorPaid;}public Boolean getOtg() {return otg;}public Boolean getIrnGenerated() {return irnGenerated;}public void setIrnGenerated(Boolean irnGenerated) {this.irnGenerated = irnGenerated;}@Column(name = "new_order_id")private Integer newOrderId;@Column(name = "doa_auth_timestamp")private LocalDateTime doaAuthTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "doa_pickup_timestamp")private LocalDateTime doaPickupTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "received_return_timestamp")private LocalDateTime receiverReturnTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "reship_timestamp")private LocalDateTime reShipTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "refund_timestamp")private LocalDateTime refundTimestamp;@Column(name = "purchase_order_id")private Integer purchaseOrderId;@Column(name = "cod", columnDefinition = "tinyInteger(1) default 0")private Boolean cod;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "verification_timestamp")private LocalDateTime verificationTimestamp;@Column(name = "refunded_by", length = 30)private String refundBy;@Column(name = "refund_reason", length = 256)private String refundReason;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "cod_reconciliation_timestamp")private LocalDateTime codReconciliationTimestamp;private Integer previousStatus;@Column(name = "vendorId")private Integer vendorId;@Column(name = "delayReasonText", length = 250)private String delayReasonText;@Column(name = "doa_logistics_provider_id")private Integer doaLogisticsProviderId;/** @Column(name = "delay_reason") private DelayReason delayReason;*/@Column(name = "vendor_paid", columnDefinition = "tinyInteger(1) default 0")private Boolean vendorPaid;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "local_connected_timestamp")private LocalDateTime localConnectedTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "reached_destination_timestamp")private LocalDateTime reachedDestinationTimestamp;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "first_dlvyatmp_timestamp")private LocalDateTime firstDlvyatmpTimestamp;@Column(name = "originalOrderId")private Integer originalOrderId;@Column(name = "fulfilmentWarehouseId")private Integer fulfilmentWarehouseId;@Column(name = "orderType")private Integer orderType;@Column(name = "pickupStoreId")private Integer pickupStoreId;@Column(name = "otg", columnDefinition = "tinyInteger(1) default 0")private Boolean otg;@Convert(converter = LocalDateTimeAttributeConverter.class)@Column(name = "courier_delivery_time")private LocalDateTime courierDeliveryTimestamp;@Column(name = "insurer")private Integer insurer;@Column(name = "insuranceAmount")private Float insuranceAmount;@Column(name = "freebieItemId")private Integer freebieItemId;@Column(name = "source")private Integer source;@Column(name = "advanceAmount")private Float advanceAmount;@Column(name = "storeId")private Integer storeId;@Column(name = "productCondition")private Integer productCondition;@Column(name = "dataProtectionInsurer")private Integer dataProtectionInsurer;@Column(name = "dataProtectionAmount")private Integer dataProtectionAmount;@Column(name = "taxType")@Enumerated(EnumType.ORDINAL)private TaxType taxType;@Column(name = "logisticsTransactionId", length = 100)private String logisticsTransactionId;@Column(name = "shippingCost")private Float shippingCost;@Column(name = "codCharges")private Float codCharges;@Column(name = "wallet_amount")private Float walletAmount;@Column(name = "net_payable_amount")private Float netPayableAmount;@Column(name = "shippingRefund")private Float shippingRefund;@Column(name = "irn_generated")private Boolean irnGenerated;@Column(name = "shipment_hold")private Boolean shipmentHold;@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)@JoinColumn(name = "id", insertable = false, updatable = false, nullable = false, referencedColumnName = "order_id")private LineItem lineItem;public Order() {}public LocalDateTime getPartnerGrnTimestamp() {return partnerGrnTimestamp;}public void setPartnerGrnTimestamp(LocalDateTime partnerGrnTimestamp) {this.partnerGrnTimestamp = partnerGrnTimestamp;}public List<String> getReturnedImeis() {return returnedImeis;}public void setReturnedImeis(List<String> returnedImeis) {this.returnedImeis = returnedImeis;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public Integer getWarehouseId() {return warehouseId;}public void setWarehouseId(Integer warehouseId) {this.warehouseId = warehouseId;}public Integer getSellerId() {return sellerId;}public void setSellerId(Integer sellerId) {this.sellerId = sellerId;}public Integer getWarehouseAddressId() {return warehouseAddressId;}public void setWarehouseAddressId(Integer warehouseAddressId) {this.warehouseAddressId = warehouseAddressId;}public Integer getLogisticsProviderId() {return logisticsProviderId;}public void setLogisticsProviderId(Integer logisticsProviderId) {this.logisticsProviderId = logisticsProviderId;}public String getAirwayBillNumber() {return airwayBillNumber;}public void setAirwayBillNumber(String airwayBillNumber) {this.airwayBillNumber = airwayBillNumber;}public String getTrackingId() {return trackingId;}public void setTrackingId(String trackingId) {this.trackingId = trackingId;}public LocalDateTime getExpectedDeliveryTime() {return expectedDeliveryTime;}public void setExpectedDeliveryTime(LocalDateTime expectedDeliveryTime) {this.expectedDeliveryTime = expectedDeliveryTime;}public LocalDateTime getPromisedDeliveryTime() {return promisedDeliveryTime;}public void setPromisedDeliveryTime(LocalDateTime promisedDeliveryTime) {this.promisedDeliveryTime = promisedDeliveryTime;}public LocalDateTime getExpectedShippingTime() {return expectedShippingTime;}public void setExpectedShippingTime(LocalDateTime expectedShippingTime) {this.expectedShippingTime = expectedShippingTime;}public LocalDateTime getPromisedShippingTime() {return promisedShippingTime;}public void setPromisedShippingTime(LocalDateTime promisedShippingTime) {this.promisedShippingTime = promisedShippingTime;}public Integer getRetailerId() {return retailerId;}public void setRetailerId(Integer retailerId) {this.retailerId = retailerId;}public String getRetailerName() {return retailerName;}public void setRetailerName(String retailerName) {this.retailerName = retailerName;}public String getRetailerMobileNumber() {return retailerMobileNumber;}public void setRetailerMobileNumber(String retailerMobileNumber) {this.retailerMobileNumber = retailerMobileNumber;}public String getRetailerPinCode() {return retailerPinCode;}public void setRetailerPinCode(String retailerPinCode) {this.retailerPinCode = retailerPinCode;}public String getRetailerAddress1() {return retailerAddress1;}public void setRetailerAddress1(String retailerAddress1) {this.retailerAddress1 = retailerAddress1;}public String getRetailerAddress2() {return retailerAddress2;}public void setRetailerAddress2(String retailerAddress2) {this.retailerAddress2 = retailerAddress2;}public String getRetailerCity() {return retailerCity;}public void setRetailerCity(String retailerCity) {this.retailerCity = retailerCity;}public String getRetailerState() {return retailerState;}public void setRetailerState(String retailerState) {this.retailerState = retailerState;}public String getRetailerEmailId() {return retailerEmailId;}public void setRetailerEmailId(String retailerEmailId) {this.retailerEmailId = retailerEmailId;}public OrderStatus getStatus() {return status;}public void setStatus(OrderStatus status) {this.status = status;}public String getStatusDescription() {return statusDescription;}public void setStatusDescription(String statusDescription) {this.statusDescription = statusDescription;}public Float getTotalAmount() {return totalAmount;}public void setTotalAmount(Float totalAmount) {this.totalAmount = totalAmount;}public Float getGvAmount() {return gvAmount;}public void setGvAmount(Float gvAmount) {this.gvAmount = gvAmount;}public Float getTotalWeight() {return totalWeight;}public void setTotalWeight(Float totalWeight) {this.totalWeight = totalWeight;}public String getInvoiceNumber() {return invoiceNumber;}public void setInvoiceNumber(String invoiceNumber) {this.invoiceNumber = invoiceNumber;}public String getBilledBy() {return billedBy;}public void setBilledBy(String billedBy) {this.billedBy = billedBy;}public LocalDateTime getCreateTimestamp() {return createTimestamp;}public void setCreateTimestamp(LocalDateTime createTimestamp) {this.createTimestamp = createTimestamp;}public LocalDateTime getAcceptedTimestamp() {return acceptedTimestamp;}public void setAcceptedTimestamp(LocalDateTime acceptedTimestamp) {this.acceptedTimestamp = acceptedTimestamp;}public LocalDateTime getBillingTimestamp() {return billingTimestamp;}public void setBillingTimestamp(LocalDateTime billingTimestamp) {this.billingTimestamp = billingTimestamp;}public LocalDateTime getShippingTimestamp() {return shippingTimestamp;}public void setShippingTimestamp(LocalDateTime shippingTimestamp) {this.shippingTimestamp = shippingTimestamp;}public LocalDateTime getPickupTimestamp() {return pickupTimestamp;}public void setPickupTimestamp(LocalDateTime pickupTimestamp) {this.pickupTimestamp = pickupTimestamp;}public LocalDateTime getDeliveryTimestamp() {return deliveryTimestamp;}public void setDeliveryTimestamp(LocalDateTime deliveryTimestamp) {this.deliveryTimestamp = deliveryTimestamp;}public LocalDateTime getOutOfStockTimestamp() {return outOfStockTimestamp;}public void setOutOfStockTimestamp(LocalDateTime outOfStockTimestamp) {this.outOfStockTimestamp = outOfStockTimestamp;}public Integer getTransactionId() {return transactionId;}public void setTransactionId(Integer transactionId) {this.transactionId = transactionId;}/** public Integer getJacketNumber() { return jacketNumber; } public void* setJacketNumber(Integer jacketNumber) { this.jacketNumber = jacketNumber; }*/public String getReceiver() {return receiver;}public void setReceiver(String receiver) {this.receiver = receiver;}public Integer getBatchNumber() {return batchNumber;}public void setBatchNumber(Integer batchNumber) {this.batchNumber = batchNumber;}public Integer getSerialNumber() {return serialNumber;}public void setSerialNumber(Integer serialNumber) {this.serialNumber = serialNumber;}public Boolean getShipmentHold() {return shipmentHold;}public void setShipmentHold(Boolean shipmentHold) {this.shipmentHold = shipmentHold;}public Boolean isDoaFlag() {return doaFlag;}public void setDoaFlag(Boolean doaFlag) {this.doaFlag = doaFlag;}public Integer getPickupRequestNumber() {return pickupRequestNumber;}public void setPickupRequestNumber(Integer pickupRequestNumber) {this.pickupRequestNumber = pickupRequestNumber;}public Integer getNewOrderId() {return newOrderId;}public void setNewOrderId(Integer newOrderId) {this.newOrderId = newOrderId;}public LocalDateTime getDoaAuthTimestamp() {return doaAuthTimestamp;}public void setDoaAuthTimestamp(LocalDateTime doaAuthTimestamp) {this.doaAuthTimestamp = doaAuthTimestamp;}public LocalDateTime getDoaPickupTimestamp() {return doaPickupTimestamp;}public void setDoaPickupTimestamp(LocalDateTime doaPickupTimestamp) {this.doaPickupTimestamp = doaPickupTimestamp;}public LocalDateTime getReceiverReturnTimestamp() {return receiverReturnTimestamp;}public void setReceiverReturnTimestamp(LocalDateTime receiverReturnTimestamp) {this.receiverReturnTimestamp = receiverReturnTimestamp;}public LocalDateTime getReShipTimestamp() {return reShipTimestamp;}public void setReShipTimestamp(LocalDateTime reShipTimestamp) {this.reShipTimestamp = reShipTimestamp;}public LocalDateTime getRefundTimestamp() {return refundTimestamp;}public void setRefundTimestamp(LocalDateTime refundTimestamp) {this.refundTimestamp = refundTimestamp;}public Integer getPurchaseOrderId() {return purchaseOrderId;}public void setPurchaseOrderId(Integer purchaseOrderId) {this.purchaseOrderId = purchaseOrderId;}public Boolean isCod() {return cod;}public void setCod(Boolean cod) {this.cod = cod;}public LocalDateTime getVerificationTimestamp() {return verificationTimestamp;}public void setVerificationTimestamp(LocalDateTime verificationTimestamp) {this.verificationTimestamp = verificationTimestamp;}public String getRefundBy() {return refundBy;}public void setRefundBy(String refundBy) {this.refundBy = refundBy;}public String getRefundReason() {return refundReason;}public void setRefundReason(String refundReason) {this.refundReason = refundReason;}/** public DelayReason getDelayReason() { return delayReason; } public void* setDelayReason(DelayReason delayReason) { this.delayReason = delayReason; }*/public LocalDateTime getCodReconciliationTimestamp() {return codReconciliationTimestamp;}public void setCodReconciliationTimestamp(LocalDateTime codReconciliationTimestamp) {this.codReconciliationTimestamp = codReconciliationTimestamp;}public Integer getPreviousStatus() {return previousStatus;}public void setPreviousStatus(Integer previousStatus) {this.previousStatus = previousStatus;}public Integer getVendorId() {return vendorId;}public void setVendorId(Integer vendorId) {this.vendorId = vendorId;}public String getDelayReasonText() {return delayReasonText;}public void setDelayReasonText(String delayReasonText) {this.delayReasonText = delayReasonText;}public Integer getDoaLogisticsProviderId() {return doaLogisticsProviderId;}public void setDoaLogisticsProviderId(Integer doaLogisticsProviderId) {this.doaLogisticsProviderId = doaLogisticsProviderId;}public Boolean isVendorPaid() {return vendorPaid;}public void setVendorPaid(Boolean vendorPaid) {this.vendorPaid = vendorPaid;}public LocalDateTime getLocalConnectedTimestamp() {return localConnectedTimestamp;}public void setLocalConnectedTimestamp(LocalDateTime localConnectedTimestamp) {this.localConnectedTimestamp = localConnectedTimestamp;}public LocalDateTime getReachedDestinationTimestamp() {return reachedDestinationTimestamp;}public void setReachedDestinationTimestamp(LocalDateTime reachedDestinationTimestamp) {this.reachedDestinationTimestamp = reachedDestinationTimestamp;}public LocalDateTime getFirstDlvyatmpTimestamp() {return firstDlvyatmpTimestamp;}public void setFirstDlvyatmpTimestamp(LocalDateTime firstDlvyatmpTimestamp) {this.firstDlvyatmpTimestamp = firstDlvyatmpTimestamp;}public Integer getOriginalOrderId() {return originalOrderId;}public void setOriginalOrderId(Integer originalOrderId) {this.originalOrderId = originalOrderId;}public Integer getFulfilmentWarehouseId() {return fulfilmentWarehouseId;}public void setFulfilmentWarehouseId(Integer fulfilmentWarehouseId) {this.fulfilmentWarehouseId = fulfilmentWarehouseId;}public Integer getOrderType() {return orderType;}public void setOrderType(Integer orderType) {this.orderType = orderType;}public Integer getPickupStoreId() {return pickupStoreId;}public void setPickupStoreId(Integer pickupStoreId) {this.pickupStoreId = pickupStoreId;}public Boolean isOtg() {return otg;}public void setOtg(Boolean otg) {this.otg = otg;}public LocalDateTime getCourierDeliveryTimestamp() {return courierDeliveryTimestamp;}public void setCourierDeliveryTimestamp(LocalDateTime courierDeliveryTimestamp) {this.courierDeliveryTimestamp = courierDeliveryTimestamp;}public Integer getInsurer() {return insurer;}public void setInsurer(Integer insurer) {this.insurer = insurer;}public Float getInsuranceAmount() {return insuranceAmount;}public void setInsuranceAmount(Float insuranceAmount) {this.insuranceAmount = insuranceAmount;}public Integer getFreebieItemId() {return freebieItemId;}public void setFreebieItemId(Integer freebieItemId) {this.freebieItemId = freebieItemId;}public Integer getSource() {return source;}public void setSource(Integer source) {this.source = source;}public Float getAdvanceAmount() {return advanceAmount;}public void setAdvanceAmount(Float advanceAmount) {this.advanceAmount = advanceAmount;}public Integer getStoreId() {return storeId;}public void setStoreId(Integer storeId) {this.storeId = storeId;}public Integer getProductCondition() {return productCondition;}public void setProductCondition(Integer productCondition) {this.productCondition = productCondition;}public Integer getDataProtectionInsurer() {return dataProtectionInsurer;}public void setDataProtectionInsurer(Integer dataProtectionInsurer) {this.dataProtectionInsurer = dataProtectionInsurer;}public Integer getDataProtectionAmount() {return dataProtectionAmount;}public void setDataProtectionAmount(Integer dataProtectionAmount) {this.dataProtectionAmount = dataProtectionAmount;}public TaxType getTaxType() {return taxType;}public void setTaxType(TaxType taxType) {this.taxType = taxType;}public String getLogisticsTransactionId() {return logisticsTransactionId;}public void setLogisticsTransactionId(String logisticsTransactionId) {this.logisticsTransactionId = logisticsTransactionId;}public Float getShippingCost() {return shippingCost;}public void setShippingCost(Float shippingCost) {this.shippingCost = shippingCost;}public Float getCodCharges() {return codCharges;}public void setCodCharges(Float codCharges) {this.codCharges = codCharges;}public Float getWalletAmount() {return walletAmount;}public void setWalletAmount(Float walletAmount) {this.walletAmount = walletAmount;}public Float getNetPayableAmount() {return netPayableAmount;}public void setNetPayableAmount(Float netPayableAmount) {this.netPayableAmount = netPayableAmount;}public Float getShippingRefund() {return shippingRefund;}public void setShippingRefund(Float shippingRefund) {this.shippingRefund = shippingRefund;}public LineItem getLineItem() {return lineItem;}public void setLineItem(LineItem lineItem) {this.lineItem = lineItem;}public String getFormattedDate() {DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm");return this.createTimestamp.format(formatter);}@Overridepublic int hashCode() {final int prime = 31;int result = 1;result = prime * result + ((id == null) ? 0 : id.hashCode());return result;}@Overridepublic boolean equals(Object obj) {if (this == obj)return true;if (obj == null)return false;if (getClass() != obj.getClass())return false;Order other = (Order) obj;if (id == null) {if (other.id != null)return false;} else if (!id.equals(other.id))return false;return true;}public Boolean isIrnGenerated() {return irnGenerated;}public void setIrnGenerated(boolean irnGenerated) {this.irnGenerated = irnGenerated;}@Overridepublic String toString() {return "Order [id=" + id + "]";}}