WebSVN – SmartDukaan – Diff – /trunk/WarehouseService/src/main/resources/in/shop2020/warehouse/persistence/ScanMapper.xml



	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
	from 
	(select i.itemId, count(*) as inx from scanNew s 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId) 
		JOIN catalog.item c ON i.itemId = c.id 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED' 
		and i.physicalWarehouseId = #{physicalWarehouseId} 
		group by i.itemId ) as x 
	LEFT JOIN 
	(select i.itemId, count(*) as outx from scanNew s 
		JOIN inventoryItem i on i.id = s.inventoryItemId 
		JOIN catalog.item c ON i.itemId = c.id 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
		and i.physicalWarehouseId = #{physicalWarehouseId} 
		group by i.itemId) as y 
	ON x.itemId = y.itemId 
	JOIN catalog.item ci ON ci.id = x.itemId 
	where ci.type = 'SERIALIZED' having quantity>0  

	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
	from 
	(select i.itemId, count(*) as inx from scanNew s 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId) 
		JOIN catalog.item c ON i.itemId = c.id 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
		and s.scannedAt &lt; #{date} 
		group by i.itemId ) as x 
	LEFT JOIN 
	(select i.itemId, count(*) as outx from scanNew s 
		JOIN inventoryItem i on i.id = s.inventoryItemId 
		JOIN catalog.item c ON i.itemId = c.id 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
		and s.scannedAt &lt; #{date} 
		group by i.itemId) as y 
	ON x.itemId = y.itemId 
	JOIN catalog.item ci ON ci.id = x.itemId 
	where ci.type = 'SERIALIZED' having quantity>0  

	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
	from 
	(select i.itemId, sum(quantity) as inx from scanNew s 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId) 
		JOIN catalog.item c ON i.itemId = c.id 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
		and i.physicalWarehouseId = #{physicalWarehouseId} 
		group by i.itemId ) as x 
	LEFT JOIN 
	(select i.itemId, sum(quantity) as outx from scanNew s 
		JOIN inventoryItem i on i.id = s.inventoryItemId 
		JOIN catalog.item c ON i.itemId = c.id 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
		and i.physicalWarehouseId = #{physicalWarehouseId} 
		group by i.itemId) as y 
	ON x.itemId = y.itemId 
	JOIN catalog.item ci ON ci.id = x.itemId 
	where ci.type = 'NON_SERIALIZED' having quantity>0  

	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
	from 
	(SELECT i.itemId, sum(quantity) as inx from scanNew s 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId) 
		JOIN catalog.item c ON i.itemId = c.id 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED' 
		and s.scannedAt &lt; #{date}
		group by i.itemId ) as x 
	LEFT JOIN 
	(SELECT i.itemId, sum(quantity) as outx from scanNew s 
		JOIN inventoryItem i on i.id = s.inventoryItemId 
		JOIN catalog.item c ON i.itemId = c.id 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
		and s.scannedAt &lt; #{date} 
		group by i.itemId) as y 
	ON x.itemId = y.itemId 
	JOIN catalog.item ci ON ci.id = x.itemId 
	where ci.type = 'NON_SERIALIZED' having quantity>0  

Rev 7676	Rev 7678
Line 178...	Line 178...
178	`ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity`	178	`ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity`
179	`from`	179	`from`
180	`(select i.itemId, count(*) as inx from scanNew s`	180	`(select i.itemId, count(*) as inx from scanNew s`
181	`JOIN inventoryItem i ON (i.id = s.inventoryItemId)`	181	`JOIN inventoryItem i ON (i.id = s.inventoryItemId)`
182	`JOIN catalog.item c ON i.itemId = c.id`	182	`JOIN catalog.item c ON i.itemId = c.id`
183	`where s.type in ('PURCHASE','SALE_RET') and c.type = 'SERIALIZED'`	183	`where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'`
184	`and i.physicalWarehouseId = #{physicalWarehouseId}`	184	`and i.physicalWarehouseId = #{physicalWarehouseId}`
185	`group by i.itemId ) as x`	185	`group by i.itemId ) as x`
186	`LEFT JOIN`	186	`LEFT JOIN`
187	`(select i.itemId, count(*) as outx from scanNew s`	187	`(select i.itemId, count(*) as outx from scanNew s`
188	`JOIN inventoryItem i on i.id = s.inventoryItemId`	188	`JOIN inventoryItem i on i.id = s.inventoryItemId`
189	`JOIN catalog.item c ON i.itemId = c.id`	189	`JOIN catalog.item c ON i.itemId = c.id`
190	`where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'SERIALIZED'`	190	`where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'`
191	`and i.physicalWarehouseId = #{physicalWarehouseId}`	191	`and i.physicalWarehouseId = #{physicalWarehouseId}`
192	`group by i.itemId) as y`	192	`group by i.itemId) as y`
193	`ON x.itemId = y.itemId`	193	`ON x.itemId = y.itemId`
194	`JOIN catalog.item ci ON ci.id = x.itemId`	194	`JOIN catalog.item ci ON ci.id = x.itemId`
195	`where ci.type = 'SERIALIZED' having quantity>0`	195	`where ci.type = 'SERIALIZED' having quantity>0`
Line 200...	Line 200...
200	`ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity`	200	`ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity`
201	`from`	201	`from`
202	`(select i.itemId, count(*) as inx from scanNew s`	202	`(select i.itemId, count(*) as inx from scanNew s`
203	`JOIN inventoryItem i ON (i.id = s.inventoryItemId)`	203	`JOIN inventoryItem i ON (i.id = s.inventoryItemId)`
204	`JOIN catalog.item c ON i.itemId = c.id`	204	`JOIN catalog.item c ON i.itemId = c.id`
205	`where s.type in ('PURCHASE','SALE_RET') and c.type = 'SERIALIZED'`	205	`where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'`
206	`and s.scannedAt < #{date}`	206	`and s.scannedAt < #{date}`
207	`group by i.itemId ) as x`	207	`group by i.itemId ) as x`
208	`LEFT JOIN`	208	`LEFT JOIN`
209	`(select i.itemId, count(*) as outx from scanNew s`	209	`(select i.itemId, count(*) as outx from scanNew s`
210	`JOIN inventoryItem i on i.id = s.inventoryItemId`	210	`JOIN inventoryItem i on i.id = s.inventoryItemId`
211	`JOIN catalog.item c ON i.itemId = c.id`	211	`JOIN catalog.item c ON i.itemId = c.id`
212	`where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'SERIALIZED'`	212	`where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'`
213	`and s.scannedAt < #{date}`	213	`and s.scannedAt < #{date}`
214	`group by i.itemId) as y`	214	`group by i.itemId) as y`
215	`ON x.itemId = y.itemId`	215	`ON x.itemId = y.itemId`
216	`JOIN catalog.item ci ON ci.id = x.itemId`	216	`JOIN catalog.item ci ON ci.id = x.itemId`
217	`where ci.type = 'SERIALIZED' having quantity>0`	217	`where ci.type = 'SERIALIZED' having quantity>0`
Line 223...	Line 223...
223	`ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity`	223	`ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity`
224	`from`	224	`from`
225	`(select i.itemId, sum(quantity) as inx from scanNew s`	225	`(select i.itemId, sum(quantity) as inx from scanNew s`
226	`JOIN inventoryItem i ON (i.id = s.inventoryItemId)`	226	`JOIN inventoryItem i ON (i.id = s.inventoryItemId)`
227	`JOIN catalog.item c ON i.itemId = c.id`	227	`JOIN catalog.item c ON i.itemId = c.id`
228	`where s.type in ('PURCHASE','SALE_RET') and c.type = 'NON_SERIALIZED'`	228	`where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'`
229	`and i.physicalWarehouseId = #{physicalWarehouseId}`	229	`and i.physicalWarehouseId = #{physicalWarehouseId}`
230	`group by i.itemId ) as x`	230	`group by i.itemId ) as x`
231	`LEFT JOIN`	231	`LEFT JOIN`
232	`(select i.itemId, sum(quantity) as outx from scanNew s`	232	`(select i.itemId, sum(quantity) as outx from scanNew s`
233	`JOIN inventoryItem i on i.id = s.inventoryItemId`	233	`JOIN inventoryItem i on i.id = s.inventoryItemId`
234	`JOIN catalog.item c ON i.itemId = c.id`	234	`JOIN catalog.item c ON i.itemId = c.id`
235	`where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'NON_SERIALIZED'`	235	`where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'`
236	`and i.physicalWarehouseId = #{physicalWarehouseId}`	236	`and i.physicalWarehouseId = #{physicalWarehouseId}`
237	`group by i.itemId) as y`	237	`group by i.itemId) as y`
238	`ON x.itemId = y.itemId`	238	`ON x.itemId = y.itemId`
239	`JOIN catalog.item ci ON ci.id = x.itemId`	239	`JOIN catalog.item ci ON ci.id = x.itemId`
240	`where ci.type = 'NON_SERIALIZED' having quantity>0`	240	`where ci.type = 'NON_SERIALIZED' having quantity>0`
Line 245...	Line 245...
245	`ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity`	245	`ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity`
246	`from`	246	`from`
247	`(SELECT i.itemId, sum(quantity) as inx from scanNew s`	247	`(SELECT i.itemId, sum(quantity) as inx from scanNew s`
248	`JOIN inventoryItem i ON (i.id = s.inventoryItemId)`	248	`JOIN inventoryItem i ON (i.id = s.inventoryItemId)`
249	`JOIN catalog.item c ON i.itemId = c.id`	249	`JOIN catalog.item c ON i.itemId = c.id`
250	`where s.type in ('PURCHASE','SALE_RET') and c.type = 'NON_SERIALIZED'`	250	`where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'`
251	`and s.scannedAt < #{date}`	251	`and s.scannedAt < #{date}`
252	`group by i.itemId ) as x`	252	`group by i.itemId ) as x`
253	`LEFT JOIN`	253	`LEFT JOIN`
254	`(SELECT i.itemId, sum(quantity) as outx from scanNew s`	254	`(SELECT i.itemId, sum(quantity) as outx from scanNew s`
255	`JOIN inventoryItem i on i.id = s.inventoryItemId`	255	`JOIN inventoryItem i on i.id = s.inventoryItemId`
256	`JOIN catalog.item c ON i.itemId = c.id`	256	`JOIN catalog.item c ON i.itemId = c.id`
257	`where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'NON_SERIALIZED'`	257	`where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'`
258	`and s.scannedAt < #{date}`	258	`and s.scannedAt < #{date}`
259	`group by i.itemId) as y`	259	`group by i.itemId) as y`
260	`ON x.itemId = y.itemId`	260	`ON x.itemId = y.itemId`
261	`JOIN catalog.item ci ON ci.id = x.itemId`	261	`JOIN catalog.item ci ON ci.id = x.itemId`
262	`where ci.type = 'NON_SERIALIZED' having quantity>0`	262	`where ci.type = 'NON_SERIALIZED' having quantity>0`

Subversion Repositories SmartDukaan

(root)/trunk/WarehouseService/src/main/resources/in/shop2020/warehouse/persistence/ScanMapper.xml – Rev 7676 → 7678