Subversion Repositories SmartDukaan

Rev

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

Rev 6558 Rev 6630
Line 110... Line 110...
110 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity})
 110 
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity})
111 
  </insert>
 111 
  </insert>
112
112
113 
  <select id = "getCurrentSerializedInventoryByScans" resultType = "inventoryAvailability">
 113 
  <select id = "getCurrentSerializedInventoryByScans" resultType = "inventoryAvailability">
114 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
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
 115 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
116 
	from
116 
	from
117 
	(select i.itemId, count(*) as inx from scanNew s
117 
	(select i.itemId, count(*) as inx from scanNew s
118 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
118 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
119 
		JOIN catalog.item c ON i.itemId = c.id
119 
		JOIN catalog.item c ON i.itemId = c.id
120 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'SERIALIZED'
120 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'SERIALIZED'
121 
		group by i.itemId ) as x
121 
		group by i.itemId ) as x
122 
	JOIN
122 
	LEFT JOIN
123 
	(select i.itemId, count(*) as outx from scanNew s
123 
	(select i.itemId, count(*) as outx from scanNew s
124 
		JOIN inventoryItem i on i.id = s.inventoryItemId
124 
		JOIN inventoryItem i on i.id = s.inventoryItemId
125 
		JOIN catalog.item c ON i.itemId = c.id
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'
126 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'SERIALIZED'
127 
		group by i.itemId) as y
127 
		group by i.itemId) as y
128 
	ON x.itemId = y.itemId
128 
	ON x.itemId = y.itemId
129 
	JOIN catalog.item ci ON ci.id = x.itemId
129 
	JOIN catalog.item ci ON ci.id = x.itemId
- 
 
 130 
	where ci.type = 'SERIALIZED' having quantity>0
- 
 
 131 
  </select>
- 
 
 132
- 
 
 133 
  <select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
- 
 
 134 
	SELECT x.itemId, ci.brand, ci.model_name as modelName,
- 
 
 135 
	ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
- 
 
 136 
	from
- 
 
 137 
	(select i.itemId, sum(quantity) as inx from scanNew s
- 
 
 138 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
- 
 
 139 
		JOIN catalog.item c ON i.itemId = c.id
130 
	where (x.inx-y.outx)>0 and ci.type = 'SERIALIZED'
140 
		where s.type in ('PURCHASE','SALE_RET') and c.type = 'NON_SERIALIZED'
- 
 
 141 
		group by i.itemId ) as x
- 
 
 142 
	LEFT JOIN
- 
 
 143 
	(select i.itemId, sum(quantity) as outx from scanNew s
- 
 
 144 
		JOIN inventoryItem i on i.id = s.inventoryItemId
- 
 
 145 
		JOIN catalog.item c ON i.itemId = c.id
- 
 
 146 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN') and c.type = 'NON_SERIALIZED'
- 
 
 147 
		group by i.itemId) as y
- 
 
 148 
	ON x.itemId = y.itemId
- 
 
 149 
	JOIN catalog.item ci ON ci.id = x.itemId
- 
 
 150 
	where ci.type = 'NON_SERIALIZED' having quantity>0
131 
  </select>
 151 
  </select>
132
152
133 
</mapper>
 153 
</mapper>