Subversion Repositories SmartDukaan

<select id = "getHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">

	SELECT x.itemId, ci.brand, ci.model_name as modelName, 

	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') and c.type = 'SERIALIZED'

		and s.scannedAt < #{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') and c.type = 'SERIALIZED'

		and s.scannedAt < #{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  

  </select>

<select id = "getHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">

	SELECT x.itemId, ci.brand, ci.model_name as modelName, 

	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') and c.type = 'NON_SERIALIZED' 

		and s.scannedAt < #{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') and c.type = 'NON_SERIALIZED'

		and s.scannedAt < #{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  

  </select>

 <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">

 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity 

 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId 

 	LEFT JOIN catalog.item c ON i.itemId = c.id 

 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED' 

 	group by itemId, s.type

 </select>

 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">

 	select itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity 

 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId 

 	LEFT JOIN catalog.item c ON i.itemId = c.id 

 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED' 

 	group by itemId, s.type

 </select>