Subversion Repositories SmartDukaan

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.selectFirstBillingByRetailer",

                query = "select min(o.billingTimestamp) from Order o " +

                        "where o.retailerId = :retailerId and o.billingTimestamp >= :minDate"),

        @NamedQuery(name = "Order.selectFirstBillingByRetailers",

                query = "select o.retailerId, min(o.billingTimestamp) from Order o " +

                        "where o.retailerId in :retailerIds and o.billingTimestamp >= :minDate " +

                        "group by o.retailerId"),

        @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.selectAllQtyPendingIndentAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvQtyFofoIdModel(" + "  o.retailerId, SUM(case when c.parentCategoryId  = 10011 then CAST(li.quantity  AS integer) else 0 end), SUM(case when c.parentCategoryId  = 14200 then  CAST(li.quantity  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.selectAllQtyGrnPendingAccessoriesTvGroupByFofoId", query = "select new com.spice.profitmandi.dao.model.InStockAccessoriesTvQtyFofoIdModel(" + "  o.retailerId, SUM(case when c.parentCategoryId  = 10011 then CAST(li.quantity  AS integer) else 0 end), SUM(case when c.parentCategoryId  = 14200 then  CAST(li.quantity  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(case when po.name = 'MOBIKWIK' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'BENOW' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'RAZOR PAY SWP' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'IDFC (CF)' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'HDB' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'UPI PAYMENT' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'ACCOUNT TRANSFER' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'ONLINE' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'TVS' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'CLEVERPE' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'AMAZON PAY' then cast(pot.amount As int) else 0 end),"

                + "  sum(case when po.name = 'SAMSUNG UPGRADE' 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.getLastOrderByFofoIds", query = "select o.retailerId, Max(o.id) from  Order o where  o.retailerId in :fofoIds and o.status in (7,9,10,12) group by o.retailerId"),

        @NamedQuery(name = "Order.selectAllBilledByCategoryOrderGroupByBrandFofoIds", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(o.retailerId, 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 in :fofoIds and i.categoryId in (:categoryIds)  group by o.retailerId, li.brand, DATE_FORMAT(o.billingTimestamp, '%m-%Y')"),

        @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 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 o.billing_timestamp >= :startDate and o.billing_timestamp < :nextDate" + " 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 o.billing_timestamp >= :startDate and o.billing_timestamp < :nextDate) " + "left join transaction.lineitem li on  (o.id = li.order_id and psp.brand=li.brand) where psp.fofo_id in :fofoIds and 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.selectCreateToBillingAvgByFofoId", 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 o.customer_id = :fofoId and 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.selectBillingToDeliverAvgByFofoId", 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 o.customer_id = :fofoId and 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" +

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

    @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 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 = "original_wallet_amount")

    private Float originalWalletAmount;

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

    }