Subversion Repositories SmartDukaan

Rev

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

Rev 35458 Rev 35464
Line 8... Line 8...
8 
	@Table(name = "fofo.activated_imei")
 8 
	@Table(name = "fofo.activated_imei")
9 
 
 9 
 
10 
@NamedQueries({
 10 
@NamedQueries({
11 
 
 11 
 
12 
		@NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByBrand", query = "select new com.spice.profitmandi.dao.model.BrandWiseActivatedModel(li.brand, "
 12 
		@NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByBrand", query = "select new com.spice.profitmandi.dao.model.BrandWiseActivatedModel(li.brand, "
13 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms then 1 else 0 end),"
 13 
				+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then 1 else 0 end),"
14 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms  then CAST(li.unitPrice  AS int) else 0 end),"
 14 
				+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then CAST(li.unitPrice AS int) else 0 end),"
15 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd then 1 else 0 end),"
 15 
				+ "sum(case when ai.activationTimestamp >= :mtdStartDate then 1 else 0 end),"
16 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd   then CAST(li.unitPrice AS int) else 0 end), "
 16 
				+ "sum(case when ai.activationTimestamp >= :mtdStartDate then CAST(li.unitPrice AS int) else 0 end), "
17 
				+ "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then 1 else 0 end), "
 17 
				+ "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then 1 else 0 end), "
18 
				+ "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then  CAST(li.unitPrice  AS int) else 0 end))"
 18 
				+ "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then CAST(li.unitPrice AS int) else 0 end))"
19 
				+ " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
 19 
				+ " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
20 
				+ "	join FofoStore fs on fs.id = o.retailerId where (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and fs.id in :fofoIds group by li.brand"),
 20 
				+ "	join FofoStore fs on fs.id = o.retailerId where ai.activationTimestamp >= :lmsStartDate and (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and fs.id in :fofoIds group by li.brand"),
21 
 
 21 
 
22 
		@NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByWarehouse", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseActivatedModel(o.warehouseId, "
 22 
		@NamedQuery(name = "ActivatedImei.selectActivatedModelGroupByWarehouse", query = "select new com.spice.profitmandi.dao.model.WarehouseWiseActivatedModel(o.warehouseId, "
23 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms then 1 else 0 end),"
 23 
				+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then 1 else 0 end),"
24 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms  then CAST(li.unitPrice  AS int) else 0 end),"
 24 
				+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then CAST(li.unitPrice AS int) else 0 end),"
25 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd then 1 else 0 end),"
 25 
				+ "sum(case when ai.activationTimestamp >= :mtdStartDate then 1 else 0 end),"
26 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd   then CAST(li.unitPrice AS int) else 0 end), "
 26 
				+ "sum(case when ai.activationTimestamp >= :mtdStartDate then CAST(li.unitPrice AS int) else 0 end), "
27 
				+ "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then 1 else 0 end), "
 27 
				+ "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then 1 else 0 end), "
28 
				+ "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then  CAST(li.unitPrice  AS int) else 0 end))"
 28 
				+ "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then CAST(li.unitPrice AS int) else 0 end))"
29 
				+ " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
 29 
				+ " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
30 
				+ " join FofoStore fs on fs.id = o.retailerId where (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and li.brand = :brand and fs.id in :fofoIds"
 30 
				+ " join FofoStore fs on fs.id = o.retailerId where ai.activationTimestamp >= :lmsStartDate and (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and li.brand = :brand and fs.id in :fofoIds"
31 
				+ " group by o.warehouseId"),
 31 
				+ " group by o.warehouseId"),
32 
 
 32 
 
33 
		@NamedQuery(name = "ActivatedImei.selectWarehouseBrandActivatedItem", query = "select new com.spice.profitmandi.dao.model.WarehouseBrandWiseItemActivatedModel(fs.warehouseId, li.itemId, li.brand,li.modelName,"
 33 
		@NamedQuery(name = "ActivatedImei.selectWarehouseBrandActivatedItem", query = "select new com.spice.profitmandi.dao.model.WarehouseBrandWiseItemActivatedModel(fs.warehouseId, li.itemId, li.brand,li.modelName,"
34 
				+ " li.modelNumber, li.color,"
 34 
				+ " li.modelNumber, li.color,"
35 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms then 1 else 0 end),"
 35 
				+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then 1 else 0 end),"
36 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :lms  then CAST(li.unitPrice  AS int) else 0 end),"
 36 
				+ "sum(case when ai.activationTimestamp >= :lmsStartDate and ai.activationTimestamp < :mtdStartDate then CAST(li.unitPrice AS int) else 0 end),"
37 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd then 1 else 0 end),"
 37 
				+ "sum(case when ai.activationTimestamp >= :mtdStartDate then 1 else 0 end),"
38 
				+ "sum(case when concat(year(ai.activationTimestamp), month(ai.activationTimestamp))= :mtd   then CAST(li.unitPrice AS int) else 0 end), "
 38 
				+ "sum(case when ai.activationTimestamp >= :mtdStartDate then CAST(li.unitPrice AS int) else 0 end), "
39 
				+ "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then 1 else 0 end), "
 39 
				+ "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then 1 else 0 end), "
40 
				+ "sum(case when ai.activationTimestamp between  :lmtdStartDate and :lmtdEndDate then  CAST(li.unitPrice  AS int) else 0 end))"
 40 
				+ "sum(case when ai.activationTimestamp >= :lmtdStartDate and ai.activationTimestamp < :lmtdEndDate then CAST(li.unitPrice AS int) else 0 end))"
41 
				+ " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
 41 
				+ " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
42 
				+ " join FofoStore fs on fs.id = o.retailerId where (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and fs.warehouseId in :warehouseId and li.brand = :brand and fs.id in :fofoIds "
 42 
				+ " join FofoStore fs on fs.id = o.retailerId where ai.activationTimestamp >= :lmsStartDate and (fs.fofoType = 'FRANCHISE' or fs.fofoType = 'THIRD_PARTY') and fs.warehouseId in :warehouseId and li.brand = :brand and fs.id in :fofoIds "
43 
				+ " group by li.itemId"),
 43 
				+ " group by li.itemId"),
44 
 
 44 
 
45 
		@NamedQuery(name = "ActivatedImei.selectActivatedUpdationDate", query = "select new com.spice.profitmandi.dao.model.ActivationImeiUpdationModel(fs.warehouseId,li.brand, "
 45 
		@NamedQuery(name = "ActivatedImei.selectActivatedUpdationDate", query = "select new com.spice.profitmandi.dao.model.ActivationImeiUpdationModel(fs.warehouseId,li.brand, "
46 
				+ " Max(ai.createTimestamp))"
 46 
				+ " Max(ai.createTimestamp))"
47 
				+ " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "
 47 
				+ " from ActivatedImei ai join LineItemImeiView lim on ai.serialNumber = lim.serialNumber join LineItem li on li.id = lim.lineItemId join Order o on o.id = li.orderId "