Subversion Repositories SmartDukaan

Rev

Rev 33944 | Rev 34584 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 33944 Rev 33947
Line 73... Line 73...
73 
 
 73 
 
74 
        @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"),
 74 
        @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"),
75 
 
 75 
 
76 
        @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"),
 76 
        @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"),
77 
 
 77 
 
78 
        @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"),
 78 
        @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"),
79 
        @NamedQuery(name = "Order.selectTodayOrdersRBM", query = "select new  com.spice.profitmandi.dao.model.BrandAuthUserPOModel( p.authUserId,\n" +
 79 
        @NamedQuery(name = "Order.selectTodayOrdersRBM", query = "select new  com.spice.profitmandi.dao.model.BrandAuthUserPOModel( p.authUserId,\n" +
80 
                "       li.brand,\n" +
 80 
                "       li.brand,\n" +
81 
                "       sum(case\n" +
 81 
                "       sum(case\n" +
82 
                "               when (o.createTimestamp between :startDate and :endDate and o.refundTimestamp is null) then cast(o.totalAmount AS integer )\n" +
 82 
                "               when (o.createTimestamp between :startDate and :endDate and o.refundTimestamp is null) then cast(o.totalAmount AS integer )\n" +
83 
                "               else 0 end),\n" +
 83 
                "               else 0 end),\n" +