Subversion Repositories SmartDukaan

<select id="getAmazonTransferredSkuDetails" resultType= "amazonTransferredSkuDetail" parameterType="map">

	SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, sum(Y.quantity) as quantity, Y.unitPrice , Y.brand, Y.modelName, Y.modelNumber, Y.category, Y.color

	FROM (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.brand, X.modelName, X.modelNumber, X.category, X.color , X.inventoryItemId, X.scanTime 

	FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate, s.quantity as quantity, l.unitPrice as unitPrice, l.brand as brand, l.modelName as modelName, l.modelNumber as modelNumber, l.productGroup as category, l.color as color , i.id as inventoryItemId, s.scannedAt as scanTime 

	FROM scanNew s 

	JOIN inventoryItem i on s.inventoryItemId= i.id 

	JOIN transferLot t on (s.transferLotId= t.id) 

	JOIN purchase p on p.id= i.purchaseId 

	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId) 

	WHERE i.itemId in 

	<foreach item="itemId" index="index" collection="itemIds"

        open="(" separator="," close=")">

	 #{itemId} 

	</foreach> 

	AND t.destinationWarehouseId= 16 

	AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER') 

	AND i.transferStatus is not null AND type='WAREHOUSE_TRANSFER_OUT' 

	group by i.itemId, s.transferLotId, i.purchaseId, i.id 

	order by s.scannedAt desc) as X group by inventoryItemId) as Y group by Y.itemId, Y.purchaseId

</select>

<select id="getScansforPurchase" resultType="scan" parameterType="map" >

	SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId 

	FROM scanNew s 

	JOIN inventoryItem i on s.inventoryItemId = i.id 

	WHERE i.purchaseId= #{purchaseId} and s.type=#{scanType}

</select>