Subversion Repositories SmartDukaan

Rev

Rev 36327 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 36327 Rev 36470
Line 146... Line 146...
146 
 
 146 
 
147 
        @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"),
 147 
        @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"),
148 
 
 148 
 
149 
        @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"),
 149 
        @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"),
150 
 
 150 
 
- 
 
 151 
        @NamedQuery(name = "Order.selectCollectionSummary", query = "select new com.spice.profitmandi.dao.model.CollectionSummary(pot.createTimestamp, "
- 
 
 152 
                + "  pot.referenceType,"
- 
 
 153 
                + "  sum(case when po.name = 'CASH' then cast(pot.amount As int) else 0 end),"
- 
 
 154 
                + "  sum(case when po.name = 'PINELABS' then cast(pot.amount As int) else 0 end),"
- 
 
 155 
                + "  sum(case when po.name = 'BAJAJ FINSERV' then cast(pot.amount As int) else 0 end),"
- 
 
 156 
                + "  sum(case when po.name = 'HOME CREDIT' then cast(pot.amount As int) else 0 end),"
- 
 
 157 
                + "  sum(case when po.name = 'PAYTM' then cast(pot.amount As int) else 0 end),"
- 
 
 158 
                + "  sum(case when po.name = 'CAPITAL FIRST' then cast(pot.amount As int) else 0 end),"
151 
        @NamedQuery(name = "Order.selectCollectionSummary", query = "select new com.spice.profitmandi.dao.model.CollectionSummary(pot.createTimestamp, " + "  pot.referenceType,sum(case when po.name = 'CASH' then cast(pot.amount As int) else 0 end)," + "  sum(case when po.name = 'PINELABS' then cast(pot.amount As int) else 0 end)," + "  sum(case when po.name = 'BAJAJ FINSERV' then cast(pot.amount As int) else 0 end)," + "  sum(case when po.name = 'HOME CREDIT' then cast(pot.amount As int) else 0 end)," + "  sum(case when po.name = 'PAYTM' then cast(pot.amount As int) else 0 end)," + "  sum(case when po.name = 'CAPITAL FIRST' then cast(pot.amount As int) else 0 end)," + "  sum(case when po.name = 'ZEST MONEY' then cast(pot.amount As int) else 0 end)," + "  sum(case when po.name = 'SAMSUNG SURE' then cast(pot.amount As int) else 0 end),sum(cast(pot.amount As int)))" + "	 from PaymentOptionTransaction pot join PaymentOption po on pot.paymentOptionId=po.id join com.spice.profitmandi.dao.entity.user.User u on u.id=pot.fofoId" + "	 join Address a on a.id=u.addressId where 1=1 and  pot.fofoId = :fofoId and pot.createTimestamp between :startDate and :endDate " + "  group by (pot.createTimestamp), pot.referenceType "),
 159 
                + "  sum(case when po.name = 'ZEST MONEY' then cast(pot.amount As int) else 0 end),"
- 
 
 160 
                + "  sum(case when po.name = 'SAMSUNG SURE' then cast(pot.amount As int) else 0 end),"
- 
 
 161 
                + "  sum(case when po.name = 'MOBIKWIK' then cast(pot.amount As int) else 0 end),"
- 
 
 162 
                + "  sum(case when po.name = 'BENOW' then cast(pot.amount As int) else 0 end),"
- 
 
 163 
                + "  sum(case when po.name = 'RAZOR PAY SWP' then cast(pot.amount As int) else 0 end),"
- 
 
 164 
                + "  sum(case when po.name = 'IDFC (CF)' then cast(pot.amount As int) else 0 end),"
- 
 
 165 
                + "  sum(case when po.name = 'HDB' then cast(pot.amount As int) else 0 end),"
- 
 
 166 
                + "  sum(case when po.name = 'UPI PAYMENT' then cast(pot.amount As int) else 0 end),"
- 
 
 167 
                + "  sum(case when po.name = 'ACCOUNT TRANSFER' then cast(pot.amount As int) else 0 end),"
- 
 
 168 
                + "  sum(case when po.name = 'ONLINE' then cast(pot.amount As int) else 0 end),"
- 
 
 169 
                + "  sum(case when po.name = 'TVS' then cast(pot.amount As int) else 0 end),"
- 
 
 170 
                + "  sum(case when po.name = 'CLEVERPE' then cast(pot.amount As int) else 0 end),"
- 
 
 171 
                + "  sum(case when po.name = 'AMAZON PAY' then cast(pot.amount As int) else 0 end),"
- 
 
 172 
                + "  sum(case when po.name = 'SAMSUNG UPGRADE' then cast(pot.amount As int) else 0 end),"
- 
 
 173 
                + "  sum(cast(pot.amount As int)))"
- 
 
 174 
                + "  from PaymentOptionTransaction pot join PaymentOption po on pot.paymentOptionId=po.id"
- 
 
 175 
                + "  join com.spice.profitmandi.dao.entity.user.User u on u.id=pot.fofoId"
- 
 
 176 
                + "  join Address a on a.id=u.addressId where 1=1 and pot.fofoId = :fofoId"
- 
 
 177 
                + "  and pot.createTimestamp between :startDate and :endDate"
- 
 
 178 
                + "  group by (pot.createTimestamp), pot.referenceType "),
152 
 
 179 
 
153 
 
 180 
 
154 
        @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)"),
 181 
        @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)"),
155 
 
 182 
 
156 
        @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"),
 183 
        @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"),