Subversion Repositories SmartDukaan

Rev

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

Rev 22522 Rev 23110
Line 38... Line 38...
38 
							+ "when (sr.type in('PURCHASE')) then "
 38 
							+ "when (sr.type in('PURCHASE')) then "
39 
							+ "sr.quantity end), "
 39 
							+ "sr.quantity end), "
40 
					+ "sum("
 40 
					+ "sum("
41 
						+ "case "
 41 
						+ "case "
42 
							+ "when (sr.type in ('SALE')) then "
 42 
							+ "when (sr.type in ('SALE')) then "
43 
								+ "(-sr.quantity * ii.unitPrice) "
 43 
								+ "(-sr.quantity * (ii.unitPrice - ii.priceDropAmount)) "
44 
							+ "when (sr.type in('PURCHASE')) then "
 44 
							+ "when (sr.type in('PURCHASE')) then "
45 
								+ "(sr.quantity * ii.unitPrice) end) "
 45 
								+ "(sr.quantity * (ii.unitPrice - ii.priceDropAmount)) end) "
46 
							+ "/ "
 46 
							+ "/ "
47 
					+"sum("
 47 
					+"sum("
48 
					+ "case "
 48 
					+ "case "
49 
						+ "when (sr.type in('SALE')) then "
 49 
						+ "when (sr.type in('SALE')) then "
50 
							+ "-sr.quantity "
 50 
							+ "-sr.quantity "
Line 67... Line 67...
67 
							+ "when (sr.type in('PURCHASE')) then "
 67 
							+ "when (sr.type in('PURCHASE')) then "
68 
							+ "sr.quantity end), "
 68 
							+ "sr.quantity end), "
69 
					+ "sum("
 69 
					+ "sum("
70 
						+ "case "
 70 
						+ "case "
71 
							+ "when (sr.type in ('SALE')) then "
 71 
							+ "when (sr.type in ('SALE')) then "
72 
								+ "(-sr.quantity * ii.unitPrice) "
 72 
								+ "(-sr.quantity * (ii.unitPrice - ii.priceDropAmount)) "
73 
							+ "when (sr.type in('PURCHASE')) then "
 73 
							+ "when (sr.type in('PURCHASE')) then "
74 
								+ "(sr.quantity * ii.unitPrice) end) "
 74 
								+ "(sr.quantity * (ii.unitPrice - ii.priceDropAmount)) end) "
75 
							+ "/ "
 75 
							+ "/ "
76 
					+"sum("
 76 
					+"sum("
77 
					+ "case "
 77 
					+ "case "
78 
						+ "when (sr.type in('SALE')) then "
 78 
						+ "when (sr.type in('SALE')) then "
79 
							+ "-sr.quantity "
 79 
							+ "-sr.quantity "
Line 84... Line 84...
84 
					+ "from ScanRecord sr join InventoryItem ii on sr.inventoryItemId = ii.id "
 84 
					+ "from ScanRecord sr join InventoryItem ii on sr.inventoryItemId = ii.id "
85 
					+ "where sr.createTimestamp > :createTimestamp and sr.fofoId = :fofoId "
 85 
					+ "where sr.createTimestamp > :createTimestamp and sr.fofoId = :fofoId "
86 
					+ "group by ii.itemId"),
 86 
					+ "group by ii.itemId"),
87 
	@NamedQuery(
 87 
	@NamedQuery(
88 
			name = "ScanRecord.selectPurchaseByFofoId",
 88 
			name = "ScanRecord.selectPurchaseByFofoId",
89 
			query = "select new com.spice.profitmandi.dao.model.ItemLedgerRow(ii.itemId, sum(sr.quantity), avg(ii.unitPrice)) 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"),
 89 
			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"),
90
90
91 
	@NamedQuery(
 91 
	@NamedQuery(
92 
			name = "ScanRecord.selectSaleByFofoId",
 92 
			name = "ScanRecord.selectSaleByFofoId",
93 
			query = "select new com.spice.profitmandi.dao.model.ItemLedgerRow(ii.itemId, sum(sr.quantity), avg(ii.unitPrice)) from ScanRecord sr join InventoryItem ii on ii.id = sr.inventoryItemId where sr.type='SALE' and sr.createTimestamp >= :startDateTime and sr.createTimestamp <= :endDateTime and sr.fofoId = :fofoId group by ii.itemId")
 93 
			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='SALE' and sr.createTimestamp >= :startDateTime and sr.createTimestamp <= :endDateTime and sr.fofoId = :fofoId group by ii.itemId")
94 
})
 94 
})
95 
public class ScanRecord {
 95 
public class ScanRecord {
96
96
97 
	@Id
 97 
	@Id
98 
	@Column(name="id", unique=true, updatable=false)
 98 
	@Column(name="id", unique=true, updatable=false)