Subversion Repositories SmartDukaan

Rev

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

Rev 33805 Rev 33823
Line 88... Line 88...
88 
                "where o.refundTimestamp is null\n" +
 88 
                "where o.refundTimestamp is null\n" +
89 
                "  and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate))\n" +
 89 
                "  and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate))\n" +
90 
                "  and fs.internal = false\n" +
 90 
                "  and fs.internal = false\n" +
91 
                "  and p.categoryId = 18\n" +
 91 
                "  and p.categoryId = 18\n" +
92 
                "  and p.escalationType = 'L1'\n" +
 92 
                "  and p.escalationType = 'L1'\n" +
93 
                "  and li.brand not in ('Smartdukaan', 'Dummy')\n" +
 93 
                "  and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')\n" +
94 
                "group by p.authUserId, li.brand"),
 94 
                "group by p.authUserId, li.brand"),
95 
 
 95 
 
96 
        @NamedQuery(name = "Order.selectTodayPartnersBilledRBM", query = "select new  com.spice.profitmandi.dao.model.AuthIdPartnerCountModel( p.authUserId,\n" +
 96 
        @NamedQuery(name = "Order.selectTodayPartnersBilledRBM", query = "select new  com.spice.profitmandi.dao.model.AuthIdPartnerCountModel( p.authUserId,\n" +
97 
                "       cast (count(distinct o.retailerId) as int))\n" +
 97 
                "       cast (count(distinct o.retailerId) as int))\n" +
98 
                "from Order o\n" +
 98 
                "from Order o\n" +
Line 100... Line 100...
100 
                "              on o.id = li.orderId\n" +
 100 
                "              on o.id = li.orderId\n" +
101 
                "         join FofoStore fs on fs.id = o.retailerId\n" +
 101 
                "         join FofoStore fs on fs.id = o.retailerId\n" +
102 
                "         join PartnerPosition pp on pp.fofoId = o.retailerId\n" +
 102 
                "         join PartnerPosition pp on pp.fofoId = o.retailerId\n" +
103 
                "         join Position p on p.id = pp.positionId\n" +
 103 
                "         join Position p on p.id = pp.positionId\n" +
104 
                "where o.refundTimestamp is null\n" +
 104 
                "where o.refundTimestamp is null\n" +
105 
                "  and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate))\n" +
 105 
                "  and (o.createTimestamp between :startDate and :endDate)\n" +
106 
                "  and fs.internal = false\n" +
 106 
                "  and fs.internal = false\n" +
107 
                "  and p.categoryId = 18\n" +
 107 
                "  and p.categoryId = 18\n" +
108 
                "  and p.escalationType = 'L1'\n" +
 108 
                "  and p.escalationType = 'L1'\n" +
109 
                "  and li.brand not in ('Smartdukaan', 'Dummy')\n" +
 109 
                "  and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')\n" +
110 
                "group by p.authUserId"),
 110 
                "group by p.authUserId"),
111 
 
 111 
 
112 
        @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"),
 112 
        @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"),
113 
 
 113 
 
114 
        @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"),
 114 
        @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"),