Subversion Repositories SmartDukaan

Rev

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

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