Subversion Repositories SmartDukaan

Rev

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

Rev 23884 Rev 24220
Line 29... Line 29...
29 
			query = "select "
 29 
			query = "select "
30 
					+"new com.spice.profitmandi.dao.model.ItemLedgerRow("
 30 
					+"new com.spice.profitmandi.dao.model.ItemLedgerRow("
31 
					+ "ii.itemId, "
 31 
					+ "ii.itemId, "
32 
					+"sum("
 32 
					+"sum("
33 
						+ "case "
 33 
						+ "case "
34 
							+ "when (sr.type in('SALE', 'PURCHASE_RET')) then "
 34 
							+ "when (sr.type in('SALE', 'PURCHASE_RET', 'PURCHASE_RET_BAD')) then "
35 
								+ "-sr.quantity "
 35 
								+ "-sr.quantity "
36 
							+ "when (sr.type in('PURCHASE')) then "
 36 
							+ "when (sr.type in('PURCHASE','SALE_RET_UNUSABLE')) then "
37 
							+ "sr.quantity end), "
 37 
							+ "sr.quantity end), "
38 
					+ "sum("
 38 
					+ "sum("
39 
						+ "case "
 39 
						+ "case "
40 
							+ "when (sr.type in ('SALE', 'PURCHASE_RET')) then "
 40 
							+ "when (sr.type in ('SALE', 'PURCHASE_RET')) then "
41 
								+ "(-sr.quantity * (ii.unitPrice - ii.priceDropAmount)) "
 41 
								+ "(-sr.quantity * (ii.unitPrice - ii.priceDropAmount)) "
42 
							+ "when (sr.type in('PURCHASE')) then "
 42 
							+ "when (sr.type in('PURCHASE')) then "
43 
								+ "(sr.quantity * (ii.unitPrice - ii.priceDropAmount)) end) "
 43 
								+ "(sr.quantity * (ii.unitPrice - ii.priceDropAmount)) end) "
44 
							+ "/ "
 44 
							+ "/ "
45 
					+"sum("
 45 
					+"sum("
46 
					+ "case "
 46 
					+ "case "
47 
						+ "when (sr.type in('SALE', 'PURCHASE_RET')) then "
 47 
						+ "when (sr.type in('SALE', 'PURCHASE_RET', 'PURCHASE_RET_BAD')) then "
48 
							+ "-sr.quantity "
 48 
							+ "-sr.quantity "
49 
						+ "when (sr.type in('PURCHASE')) then "
 49 
						+ "when (sr.type in('PURCHASE', 'SALE_RET_UNUSABLE')) then "
50 
						+ "sr.quantity end)"
 50 
						+ "sr.quantity end)"
51 
					+ ") "
 51 
					+ ") "
52 
					//+ "ii.unitPrice "
 52 
					//+ "ii.unitPrice "
53 
					+ "from ScanRecord sr join InventoryItem ii on sr.inventoryItemId = ii.id "
 53 
					+ "from ScanRecord sr join InventoryItem ii on sr.inventoryItemId = ii.id "
54 
					+ "where sr.createTimestamp < :createTimestamp and sr.fofoId = :fofoId "
 54 
					+ "where sr.createTimestamp < :createTimestamp and sr.fofoId = :fofoId "
Line 58... Line 58...
58 
			query = "select "
 58 
			query = "select "
59 
					+"new com.spice.profitmandi.dao.model.ItemLedgerRow("
 59 
					+"new com.spice.profitmandi.dao.model.ItemLedgerRow("
60 
					+ "ii.itemId, "
 60 
					+ "ii.itemId, "
61 
					+"sum("
 61 
					+"sum("
62 
						+ "case "
 62 
						+ "case "
63 
							+ "when (sr.type in('SALE','PURCHASE_RET')) then "
 63 
							+ "when (sr.type in('SALE','PURCHASE_RET','PURCHASE_RET_BAD')) then "
64 
								+ "-sr.quantity "
 64 
								+ "-sr.quantity "
65 
							+ "when (sr.type in('PURCHASE')) then "
 65 
							+ "when (sr.type in('PURCHASE', 'SALE_RET_UNUSABLE')) then "
66 
							+ "sr.quantity end), "
 66 
							+ "sr.quantity end), "
67 
					+ "sum("
 67 
					+ "sum("
68 
						+ "case "
 68 
						+ "case "
69 
							+ "when (sr.type in ('SALE', 'PURCHASE_RET')) then "
 69 
							+ "when (sr.type in ('SALE', 'PURCHASE_RET','PURCHASE_RET_BAD')) then "
70 
								+ "(-sr.quantity * (ii.unitPrice - ii.priceDropAmount)) "
 70 
								+ "(-sr.quantity * (ii.unitPrice - ii.priceDropAmount)) "
71 
							+ "when (sr.type in('PURCHASE')) then "
 71 
							+ "when (sr.type in('PURCHASE','SALE_RET_UNUSABLE')) then "
72 
								+ "(sr.quantity * (ii.unitPrice - ii.priceDropAmount)) end) "
 72 
								+ "(sr.quantity * (ii.unitPrice - ii.priceDropAmount)) end) "
73 
							+ "/ "
 73 
							+ "/ "
74 
					+"sum("
 74 
					+"sum("
75 
					+ "case "
 75 
					+ "case "
76 
						+ "when (sr.type in('SALE', 'PURCHASE_RET')) then "
 76 
						+ "when (sr.type in('SALE', 'PURCHASE_RET', 'PURCHASE_RET_BAD')) then "
77 
							+ "-sr.quantity "
 77 
							+ "-sr.quantity "
78 
						+ "when (sr.type in('PURCHASE')) then "
 78 
						+ "when (sr.type in('PURCHASE', 'SALE_RET_UNUSABLE')) then "
79 
						+ "sr.quantity end)"
 79 
						+ "sr.quantity end)"
80 
					+ ") "
 80 
					+ ") "
81 
					//+ "ii.unitPrice "
 81 
					//+ "ii.unitPrice "
82 
					+ "from ScanRecord sr join InventoryItem ii on sr.inventoryItemId = ii.id "
 82 
					+ "from ScanRecord sr join InventoryItem ii on sr.inventoryItemId = ii.id "
83 
					+ "where sr.createTimestamp > :createTimestamp and sr.fofoId = :fofoId "
 83 
					+ "where sr.createTimestamp < :createTimestamp and sr.fofoId = :fofoId "
84 
					+ "group by ii.itemId"),
 84 
					+ "group by ii.itemId"),
85 
	@NamedQuery(
 85 
	@NamedQuery(
86 
			name = "ScanRecord.selectPurchaseByFofoId",
 86 
			name = "ScanRecord.selectPurchaseByFofoId",
87 
			query = "select new com.spice.profitmandi.dao.model.ItemLedgerRow(ii.itemId, sum(sr.quantity), avg(ii.unitPrice - ii.priceDropAmount)) from ScanRecord sr join InventoryItem ii on ii.id = sr.inventoryItemId where sr.type='PURCHASE' and sr.createTimestamp >= :startDateTime and sr.createTimestamp <= :endDateTime and sr.fofoId = :fofoId group by ii.itemId"),
 87 
			query = "select new com.spice.profitmandi.dao.model.ItemLedgerRow(ii.itemId, sum(sr.quantity), avg(ii.unitPrice - ii.priceDropAmount)) from ScanRecord sr join InventoryItem ii on ii.id = sr.inventoryItemId where sr.type in ('PURCHASE', 'SALE_RET_UNUSABLE') and sr.createTimestamp >= :startDateTime and sr.createTimestamp <= :endDateTime and sr.fofoId = :fofoId group by ii.itemId"),
88
88
89 
	@NamedQuery(
 89 
	@NamedQuery(
90 
			name = "ScanRecord.selectSaleByFofoId",
 90 
			name = "ScanRecord.selectSaleByFofoId",
91 
			query = "select new com.spice.profitmandi.dao.model.ItemLedgerRow(ii.itemId, sum(sr.quantity), avg(ii.unitPrice - ii.priceDropAmount)) from ScanRecord sr join InventoryItem ii on ii.id = sr.inventoryItemId where sr.type in ('SALE', 'PURCHASE_RET') and sr.createTimestamp >= :startDateTime and sr.createTimestamp <= :endDateTime and sr.fofoId = :fofoId group by ii.itemId")
 91 
			query = "select new com.spice.profitmandi.dao.model.ItemLedgerRow(ii.itemId, sum(sr.quantity), avg(ii.unitPrice - ii.priceDropAmount)) from ScanRecord sr join InventoryItem ii on ii.id = sr.inventoryItemId where sr.type in ('SALE', 'PURCHASE_RET', 'PURCHASE_RET_BAD') and sr.createTimestamp >= :startDateTime and sr.createTimestamp <= :endDateTime and sr.fofoId = :fofoId group by ii.itemId")
92 
})
 92 
})
93 
public class ScanRecord {
 93 
public class ScanRecord {
94
94
95 
	@Id
 95 
	@Id
96 
	@Column(name="id", unique=true, updatable=false)
 96 
	@Column(name="id", unique=true, updatable=false)