Subversion Repositories SmartDukaan

Rev

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

Rev 35323 Rev 35463
Line 25... Line 25...
25 
 
 25 
 
26 
        @NamedQuery(name = "FofoStore.selectAllDtrUserIds", query = "select ua.userId from FofoStore fs join UserAccount ua on fs.id=ua.accountKey "
 26 
        @NamedQuery(name = "FofoStore.selectAllDtrUserIds", query = "select ua.userId from FofoStore fs join UserAccount ua on fs.id=ua.accountKey "
27 
                + "where ua.type='saholic' and fs.internal=false and fs.active=true"),
 27 
                + "where ua.type='saholic' and fs.internal=false and fs.active=true"),
28 
 
 28 
 
29 
        @NamedQuery(name = "FofoStore.selectGroupByBrandWarehousePartnerSale", query = "select new com.spice.profitmandi.dao.model.BrandWisePartnerSaleModel(foi.brand, "
 29 
        @NamedQuery(name = "FofoStore.selectGroupByBrandWarehousePartnerSale", query = "select new com.spice.profitmandi.dao.model.BrandWisePartnerSaleModel(foi.brand, "
30 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then CAST(foi.quantity*foi.mop  AS integer ) else 0 end),"
 30 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then CAST(foi.quantity*foi.mop  AS integer ) else 0 end),"
31 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then foi.quantity  else 0 end),"
 31 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then foi.quantity  else 0 end),"
32 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then CAST(foi.quantity*foi.mop  AS integer ) else 0 end),"
 32 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then CAST(foi.quantity*foi.mop  AS integer ) else 0 end),"
33 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then foi.quantity else 0 end), "
 33 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then foi.quantity else 0 end), "
34 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate  then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
 34 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
35 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate then foi.quantity else 0 end))"
 35 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then foi.quantity else 0 end))"
36 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true "
 36 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true "
37 
                + "and foi.createTimestamp >= :lmtdStartDate and i.categoryId = :categoryId and fs.id in :fofoId"
 37 
                + "and foi.createTimestamp >= :lmtdStartDate and i.categoryId = :categoryId and fs.id in :fofoId"
38 
                + " group by foi.brand"),
 38 
                + " group by foi.brand"),
39 
        @NamedQuery(name = "FofoStore.selectGroupByBrandWarehousePartnerAccessoriesSale", query = "select new com.spice.profitmandi.dao.model.BrandWisePartnerSaleModel(foi.brand, "
 39 
        @NamedQuery(name = "FofoStore.selectGroupByBrandWarehousePartnerAccessoriesSale", query = "select new com.spice.profitmandi.dao.model.BrandWisePartnerSaleModel(foi.brand, "
40 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then CAST(foi.quantity*foi.mop  AS integer ) else 0 end),"
 40 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then CAST(foi.quantity*foi.mop  AS integer ) else 0 end),"
41 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then foi.quantity  else 0 end),"
 41 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then foi.quantity  else 0 end),"
42 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then CAST(foi.quantity*foi.mop  AS integer ) else 0 end),"
 42 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then CAST(foi.quantity*foi.mop  AS integer ) else 0 end),"
43 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then foi.quantity else 0 end), "
 43 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then foi.quantity else 0 end), "
44 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate  then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
 44 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
45 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate then foi.quantity else 0 end))"
 45 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then foi.quantity else 0 end))"
46 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true "
 46 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true "
47 
                + "and foi.createTimestamp >= :lmtdStartDate and i.categoryId != :categoryId and fs.id in :fofoId"),
 47 
                + "and foi.createTimestamp >= :lmtdStartDate and i.categoryId != :categoryId and fs.id in :fofoId"),
48 
 
 48 
 
49 
        @NamedQuery(name = "FofoStore.selectGroupByWarehouseBrandWisePartnerSale", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseBrandSaleModel("
 49 
        @NamedQuery(name = "FofoStore.selectGroupByWarehouseBrandWisePartnerSale", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseBrandSaleModel("
50 
                + "(case when a.city in ('Noida', 'Ghaziabad')  and foi.brand = 'Vivo' then 7573 else fs.warehouseId end) AS col_0_0_,"
 50 
                + "(case when a.city in ('Noida', 'Ghaziabad')  and foi.brand = 'Vivo' then 7573 else fs.warehouseId end) AS col_0_0_,"
51 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
 51 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
52 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then foi.quantity  else 0 end),"
 52 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then foi.quantity  else 0 end),"
53 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
 53 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
54 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then foi.quantity else 0 end), "
 54 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then foi.quantity else 0 end), "
55 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate  then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
 55 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
56 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate then foi.quantity else 0 end))"
 56 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then foi.quantity else 0 end))"
57 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId"
 57 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId"
58 
                + " join com.spice.profitmandi.dao.entity.user.User u on u.id = fs.id join Address a on a.id = u.addressId"
 58 
                + " join com.spice.profitmandi.dao.entity.user.User u on u.id = fs.id join Address a on a.id = u.addressId"
59 
                + " where fo.cancelledTimestamp is null and fs.active = true"
 59 
                + " where fo.cancelledTimestamp is null and fs.active = true"
60 
                + " and foi.createTimestamp >= :lmtdStartDate and foi.brand = :brand and i.categoryId = :categoryId and fs.id in :fofoId"
 60 
                + " and foi.createTimestamp >= :lmtdStartDate and foi.brand = :brand and i.categoryId = :categoryId and fs.id in :fofoId"
61 
                + " group by col_0_0_"),
 61 
                + " group by col_0_0_"),
62 
 
 62 
 
63 
        @NamedQuery(name = "FofoStore.selectWarehouseBrandItemSale", query = "select new com.spice.profitmandi.dao.model.WarehouseBrandWiseItemSaleModel(fs.warehouseId,i.id, i.brand, i.modelName, i.modelNumber,i.color,"
 63 
        @NamedQuery(name = "FofoStore.selectWarehouseBrandItemSale", query = "select new com.spice.profitmandi.dao.model.WarehouseBrandWiseItemSaleModel(fs.warehouseId,i.id, i.brand, i.modelName, i.modelNumber,i.color,"
64 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
 64 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
65 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then foi.quantity  else 0 end),"
 65 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then foi.quantity  else 0 end),"
66 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
 66 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
67 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then foi.quantity else 0 end), "
 67 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then foi.quantity else 0 end), "
68 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate  then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
 68 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
69 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate then foi.quantity else 0 end))"
 69 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then foi.quantity else 0 end))"
70 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true"
 70 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true"
71 
                + " and  foi.createTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and i.brand = :brand and i.categoryId = :categoryId and fs.id in :fofoId group by i.id"),
 71 
                + " and  foi.createTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and i.brand = :brand and i.categoryId = :categoryId and fs.id in :fofoId group by i.id"),
72 
 
 72 
 
73 
        @NamedQuery(name = "FofoStore.selectGroupByBrandAccesoriesWarehousePartnerSale", query = "select new com.spice.profitmandi.dao.model.BrandWisePartnerSaleModel(foi.brand, "
 73 
        @NamedQuery(name = "FofoStore.selectGroupByBrandAccesoriesWarehousePartnerSale", query = "select new com.spice.profitmandi.dao.model.BrandWisePartnerSaleModel(foi.brand, "
74 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
 74 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
75 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then foi.quantity  else 0 end),"
 75 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then foi.quantity  else 0 end),"
76 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
 76 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
77 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then foi.quantity else 0 end), "
 77 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then foi.quantity else 0 end), "
78 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate  then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
 78 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
79 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate then foi.quantity else 0 end))"
 79 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then foi.quantity else 0 end))"
80 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true "
 80 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true "
81 
                + "and foi.createTimestamp >= :lmtdStartDate and i.categoryId != :categoryId and fs.id in :fofoId"
 81 
                + "and foi.createTimestamp >= :lmtdStartDate and i.categoryId != :categoryId and fs.id in :fofoId"
82 
                + " group by foi.brand"),
 82 
                + " group by foi.brand"),
83 
 
 83 
 
84 
        @NamedQuery(name = "FofoStore.selectGroupByWarehouseAccesoriesBrandWisePartnerSale", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseBrandSaleModel(fs.warehouseId,"
 84 
        @NamedQuery(name = "FofoStore.selectGroupByWarehouseAccesoriesBrandWisePartnerSale", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseBrandSaleModel(fs.warehouseId,"
85 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
 85 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
86 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then foi.quantity  else 0 end),"
 86 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then foi.quantity  else 0 end),"
87 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
 87 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
88 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then foi.quantity else 0 end), "
 88 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then foi.quantity else 0 end), "
89 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate  then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
 89 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
90 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate then foi.quantity else 0 end))"
 90 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then foi.quantity else 0 end))"
91 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true"
 91 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true"
92 
                + " and foi.createTimestamp >= :lmtdStartDate and foi.brand = :brand and i.categoryId != :categoryId and fs.id in :fofoId"
 92 
                + " and foi.createTimestamp >= :lmtdStartDate and foi.brand = :brand and i.categoryId != :categoryId and fs.id in :fofoId"
93 
                + " group by fs.warehouseId"),
 93 
                + " group by fs.warehouseId"),
94 
 
 94 
 
95 
        @NamedQuery(name = "FofoStore.selectWarehouseAccesoriesBrandItemSale", query = "select new com.spice.profitmandi.dao.model.WarehouseBrandWiseItemSaleModel(fs.warehouseId,i.id, i.brand, i.modelName, i.modelNumber,i.color,"
 95 
        @NamedQuery(name = "FofoStore.selectWarehouseAccesoriesBrandItemSale", query = "select new com.spice.profitmandi.dao.model.WarehouseBrandWiseItemSaleModel(fs.warehouseId,i.id, i.brand, i.modelName, i.modelNumber,i.color,"
96 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
 96 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
97 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :lms then foi.quantity  else 0 end),"
 97 
                + "sum(case when foi.createTimestamp >= :lmsStartDate AND foi.createTimestamp < :lmsEndDate then foi.quantity  else 0 end),"
98 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
 98 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end),"
99 
                + "sum(case when concat(year(foi.createTimestamp), month(foi.createTimestamp))= :mtd  then foi.quantity else 0 end), "
 99 
                + "sum(case when foi.createTimestamp >= :mtdStartDate AND foi.createTimestamp < :mtdEndDate then foi.quantity else 0 end), "
100 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate  then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
 100 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then CAST(foi.quantity*foi.mop  AS int) else 0 end), "
101 
                + "sum(case when foi.createTimestamp between  :lmtdStartDate and :lmtdEndDate then foi.quantity else 0 end))"
 101 
                + "sum(case when foi.createTimestamp >= :lmtdStartDate AND foi.createTimestamp < :lmtdEndDate then foi.quantity else 0 end))"
102 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true"
 102 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true"
103 
                + " and  foi.createTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and i.brand = :brand and i.categoryId != :categoryId  and fs.id in :fofoId group by i.id"),
 103 
                + " and  foi.createTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and i.brand = :brand and i.categoryId != :categoryId  and fs.id in :fofoId group by i.id"),
104 
 
 104 
 
105 
        @NamedQuery(name = "FofoStore.selectPartnerPendingIndentItem", query = "select new com.spice.profitmandi.dao.model.PartnerPendingIndentItemModel(o.retailerName,"
 105 
        @NamedQuery(name = "FofoStore.selectPartnerPendingIndentItem", query = "select new com.spice.profitmandi.dao.model.PartnerPendingIndentItemModel(o.retailerName,"
106 
                + " li.quantity, li.totalPrice)"
 106 
                + " li.quantity, li.totalPrice)"
107 
                + " from FofoStore fs join Order o on fs.id = o.retailerId join  LineItem li on li.orderId = o.id "
 107 
                + " from FofoStore fs join Order o on fs.id = o.retailerId join  LineItem li on li.orderId = o.id "
108 
                + " where li.itemId = :itemId and fs.warehouseId = :warehouseId and o.status = 3"),
 108 
                + " where li.itemId = :itemId and fs.warehouseId = :warehouseId and o.status = 3"),
109 
 
 109 
 
110 
        @NamedQuery(name = "FofoStore.selectUnbilledActivateStockGroupByBrand", query = "select new com.spice.profitmandi.dao.model.BrandWiseUnbilledActivateStockModel(i.brand,"
 110 
        @NamedQuery(name = "FofoStore.selectUnbilledActivateStockGroupByBrand", query = "select new com.spice.profitmandi.dao.model.BrandWiseUnbilledActivateStockModel(i.brand,"
111 
                + "  sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp)) = :almtd then 1 else 0 end),"
 111 
                + "  sum(case when ai.activationTimestamp >= :almtdStartDate AND ai.activationTimestamp < :almtdEndDate then 1 else 0 end),"
112 
                + "  sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp)) = :almtd then CAST(tl.sellingPrice  AS int) else 0 end))"
 112 
                + "  sum(case when ai.activationTimestamp >= :almtdStartDate AND ai.activationTimestamp < :almtdEndDate then CAST(tl.sellingPrice  AS int) else 0 end))"
113 
                + " from FofoStore fs join InventoryItem ii on fs.id = ii.fofoId join ActivatedImei ai on ai.serialNumber = ii.serialNumber join Item i on i.id = ii.itemId join TagListing tl on tl.itemId = i.id"
 113 
                + " from FofoStore fs join InventoryItem ii on fs.id = ii.fofoId join ActivatedImei ai on ai.serialNumber = ii.serialNumber join Item i on i.id = ii.itemId join TagListing tl on tl.itemId = i.id"
114 
                + " where fs.active = true and ii.goodQuantity >= 1 and fs.id in :fofoId group by i.brand"),
 114 
                + " where fs.active = true and ii.goodQuantity >= 1 and fs.id in :fofoId group by i.brand"),
115 
 
 115 
 
116 
        @NamedQuery(name = "FofoStore.selectUnbilledActivateStockGroupByWarehouse", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseBrandUnbilledActivatedModel(fs.warehouseId,"
 116 
        @NamedQuery(name = "FofoStore.selectUnbilledActivateStockGroupByWarehouse", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseBrandUnbilledActivatedModel(fs.warehouseId,"
117 
                + "  sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp)) = :almtd then 1 else 0 end),"
 117 
                + "  sum(case when ai.activationTimestamp >= :almtdStartDate AND ai.activationTimestamp < :almtdEndDate then 1 else 0 end),"
118 
                + "  sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp)) = :almtd then CAST(tl.sellingPrice  AS int) else 0 end))"
 118 
                + "  sum(case when ai.activationTimestamp >= :almtdStartDate AND ai.activationTimestamp < :almtdEndDate then CAST(tl.sellingPrice  AS int) else 0 end))"
119 
                + " from FofoStore fs join InventoryItem ii on fs.id = ii.fofoId join ActivatedImei ai on ai.serialNumber = ii.serialNumber join Item i on i.id = ii.itemId join TagListing tl on tl.itemId = i.id"
 119 
                + " from FofoStore fs join InventoryItem ii on fs.id = ii.fofoId join ActivatedImei ai on ai.serialNumber = ii.serialNumber join Item i on i.id = ii.itemId join TagListing tl on tl.itemId = i.id"
120 
                + " where fs.active = true and ii.goodQuantity >= 1 and i.brand = :brand and fs.id in :fofoId group by fs.warehouseId"),
 120 
                + " where fs.active = true and ii.goodQuantity >= 1 and i.brand = :brand and fs.id in :fofoId group by fs.warehouseId"),
121 
 
 121 
 
122 
        @NamedQuery(name = "FofoStore.selectWarehouseBrandItemUnbilledActivateStock", query = "select new com.spice.profitmandi.dao.model.WarehouseBrandItemUnbilledActivatedModel(fs.warehouseId,i.id, i.brand, i.modelName, i.modelNumber,i.color,"
 122 
        @NamedQuery(name = "FofoStore.selectWarehouseBrandItemUnbilledActivateStock", query = "select new com.spice.profitmandi.dao.model.WarehouseBrandItemUnbilledActivatedModel(fs.warehouseId,i.id, i.brand, i.modelName, i.modelNumber,i.color,"
123 
                + "  sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp)) = :almtd then 1 else 0 end),"
 123 
                + "  sum(case when ai.activationTimestamp >= :almtdStartDate AND ai.activationTimestamp < :almtdEndDate then 1 else 0 end),"
124 
                + "  sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp)) = :almtd then CAST(tl.sellingPrice  AS int) else 0 end))"
 124 
                + "  sum(case when ai.activationTimestamp >= :almtdStartDate AND ai.activationTimestamp < :almtdEndDate then CAST(tl.sellingPrice  AS int) else 0 end))"
125 
                + " from FofoStore fs join InventoryItem ii on fs.id = ii.fofoId join ActivatedImei ai on ai.serialNumber = ii.serialNumber join Item i on i.id = ii.itemId join TagListing tl on tl.itemId = i.id"
 125 
                + " from FofoStore fs join InventoryItem ii on fs.id = ii.fofoId join ActivatedImei ai on ai.serialNumber = ii.serialNumber join Item i on i.id = ii.itemId join TagListing tl on tl.itemId = i.id"
126 
                + " where fs.active = true and ii.goodQuantity >= 1 and i.brand = :brand and fs.warehouseId in :warehouseId and fs.id in :fofoId  group by i.id"),
 126 
                + " where fs.active = true and ii.goodQuantity >= 1 and i.brand = :brand and fs.warehouseId in :warehouseId and fs.id in :fofoId  group by i.id"),
127 
 
 127 
 
128 
        @NamedQuery(name = "FofoStore.selectGroupByBrandLms", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(foi.brand, DATE_FORMAT(fo.createTimestamp, :groupParameter),sum(cast(foi.mop*foi.quantity As int)))"
 128 
        @NamedQuery(name = "FofoStore.selectGroupByBrandLms", query = "select new com.spice.profitmandi.dao.model.BrandWiseModel(foi.brand, DATE_FORMAT(fo.createTimestamp, :groupParameter),sum(cast(foi.mop*foi.quantity As int)))"
129 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true "
 129 
                + " from FofoStore fs join FofoOrder fo on fs.id = fo.fofoId join FofoOrderItem foi on foi.orderId = fo.id  join Item i on i.id = foi.itemId  where fo.cancelledTimestamp is null and fs.active = true "