Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.entity.transaction;

import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;

import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;

import com.spice.profitmandi.dao.model.PriceDropReportModel;

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`", schema = "transaction")

@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.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.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.status in (7,9,10,12)"

				+ "	and o.billingTimestamp >= :startDate 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"),

		@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) 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.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 "),

})

//@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"

				+ " 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")

})

@SqlResultSetMappings({

		@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), }) })

})

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;

	@Transient

	private 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 Integer jacketNumber;

	 */

	@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;

	}