Subversion Repositories SmartDukaan

Rev

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

Rev 6494 Rev 6558
Line 101... Line 101...
101 
	  i.itemId = #{itemId}
101 
	  i.itemId = #{itemId}
102 
	  AND
 102 
	  AND
103 
	  s.scannedAt BETWEEN #{fromDate} AND #{toDate};
 103 
	  s.scannedAt BETWEEN #{fromDate} AND #{toDate};
104 
  </select>
 104 
  </select>
105
105
106 
  <insert id="scanForPurchaseReturn" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
 106 
  <insert id="genericScan" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
107 
    INSERT INTO scanNew
 107 
    INSERT INTO scanNew
108 
      (inventoryItemId, warehouseId, type, scannedAt, quantity)
 108 
      (inventoryItemId, warehouseId, type, scannedAt, quantity)
109 
    VALUES
 109 
    VALUES
110 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity})
 110 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity})
111 
  </insert>
 111 
  </insert>
112
112
- 
 
 113 
  <select id = "getCurrentSerializedInventoryByScans" resultType = "inventoryAvailability">
- 
 
 114 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
- 
 
 115 
	ci.model_number as modelNumber, ci.color as color, (x.inx-y.outx) as quantity
- 
 
 116 
	from
- 
 
 117 
	(select i.itemId, count(*) as inx from scanNew s
- 
 
 118 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
- 
 
 119 
		JOIN catalog.item c ON i.itemId = c.id
- 
 
 120 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'SERIALIZED'
- 
 
 121 
		group by i.itemId ) as x
- 
 
 122 
	JOIN
- 
 
 123 
	(select i.itemId, count(*) as outx from scanNew s
- 
 
 124 
		JOIN inventoryItem i on i.id = s.inventoryItemId
- 
 
 125 
		JOIN catalog.item c ON i.itemId = c.id
- 
 
 126 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'SERIALIZED'
- 
 
 127 
		group by i.itemId) as y
- 
 
 128 
	ON x.itemId = y.itemId
- 
 
 129 
	JOIN catalog.item ci ON ci.id = x.itemId
- 
 
 130 
	where (x.inx-y.outx)>0 and ci.type = 'SERIALIZED'
- 
 
 131 
  </select>
- 
 
 132
113 
</mapper>
 133 
</mapper>