Subversion Repositories SmartDukaan

Rev

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

Rev 12357 Rev 12543
Line 133... Line 133...
133 
                ON (s.inventoryItemId = ii.id)
133 
                ON (s.inventoryItemId = ii.id)
134 
           WHERE (ii.lastScanType IN ('PURCHASE', 'SALE_RET', 'MARKED_GOOD')
134 
           WHERE (ii.lastScanType IN ('PURCHASE', 'SALE_RET', 'MARKED_GOOD')
135 
           OR ii.currentQuantity>1)
 135 
           OR ii.currentQuantity>1)
136 
           AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT')
 136 
           AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT')
137 
           AND ii.physicalWarehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40
137 
           AND ii.physicalWarehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40
138 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
138 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
139 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
139 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
140 
    JOIN purchase p
140 
    JOIN purchase p
141 
        ON (p.id = t.purchaseId)
141 
        ON (p.id = t.purchaseId)
142 
    JOIN lineitem l
142 
    JOIN lineitem l
143 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
 143 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
Line 238... Line 238...
238 
	(select i.itemId, count(*) as inx from scanNew s
238 
	(select i.itemId, count(*) as inx from scanNew s
239 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
239 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
240 
		JOIN catalog.item c ON i.itemId = c.id
240 
		JOIN catalog.item c ON i.itemId = c.id
241 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
 241 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
242 
		and s.scannedAt < #{date}
242 
		and s.scannedAt < #{date}
243 
		and s.warehouseId != 16
 243 
		and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
- 
 
 244 
	    and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
244 
		group by i.itemId ) as x
245 
		group by i.itemId ) as x
245 
	LEFT JOIN
246 
	LEFT JOIN
246 
	(select i.itemId, count(*) as outx from scanNew s
247 
	(select i.itemId, count(*) as outx from scanNew s
247 
		JOIN inventoryItem i on i.id = s.inventoryItemId
248 
		JOIN inventoryItem i on i.id = s.inventoryItemId
248 
		JOIN catalog.item c ON i.itemId = c.id
249 
		JOIN catalog.item c ON i.itemId = c.id
249 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
 250 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
250 
		and s.scannedAt < #{date}
 251 
		and s.scannedAt < #{date}
251 
		and s.warehouseId != 16
252 
		and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
- 
 
 253 
	    and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
252 
		group by i.itemId) as y
254 
		group by i.itemId) as y
253 
	ON x.itemId = y.itemId
255 
	ON x.itemId = y.itemId
254 
	JOIN catalog.item ci ON ci.id = x.itemId
256 
	JOIN catalog.item ci ON ci.id = x.itemId
255 
	where ci.type = 'SERIALIZED' having quantity>0
257 
	where ci.type = 'SERIALIZED' having quantity>0
256 
</select>
 258 
</select>
Line 309... Line 311...
309 
	(SELECT i.itemId, sum(quantity) as inx from scanNew s
311 
	(SELECT i.itemId, sum(quantity) as inx from scanNew s
310 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
312 
		JOIN inventoryItem i ON (i.id = s.inventoryItemId)
311 
		JOIN catalog.item c ON i.itemId = c.id
313 
		JOIN catalog.item c ON i.itemId = c.id
312 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
314 
		where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
313 
		and s.scannedAt &lt; #{date}
 315 
		and s.scannedAt &lt; #{date}
314 
		and s.warehouseId != 16
 316 
		and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
- 
 
 317 
	    and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
315 
		group by i.itemId ) as x
318 
		group by i.itemId ) as x
316 
	LEFT JOIN
319 
	LEFT JOIN
317 
	(SELECT i.itemId, sum(quantity) as outx from scanNew s
320 
	(SELECT i.itemId, sum(quantity) as outx from scanNew s
318 
		JOIN inventoryItem i on i.id = s.inventoryItemId
321 
		JOIN inventoryItem i on i.id = s.inventoryItemId
319 
		JOIN catalog.item c ON i.itemId = c.id
322 
		JOIN catalog.item c ON i.itemId = c.id
320 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
 323 
		where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
321 
		and s.scannedAt &lt; #{date}
 324 
		and s.scannedAt &lt; #{date}
322 
		and s.warehouseId != 16
325 
		and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
- 
 
 326 
	    and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
323 
		group by i.itemId) as y
327 
		group by i.itemId) as y
324 
	ON x.itemId = y.itemId
328 
	ON x.itemId = y.itemId
325 
	JOIN catalog.item ci ON ci.id = x.itemId
329 
	JOIN catalog.item ci ON ci.id = x.itemId
326 
	where ci.type = 'NON_SERIALIZED' having quantity>0
330 
	where ci.type = 'NON_SERIALIZED' having quantity>0
327 
</select>
 331 
</select>
Line 331... Line 335...
331 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
335 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
332 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
336 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
333 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
 337 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
334 
 	and i.currentWarehouseId not in (1,2,6,9)
338 
 	and i.currentWarehouseId not in (1,2,6,9)
335 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
339 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
336 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
340 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
337 
 	group by itemId, s.type
 341 
 	group by itemId, s.type
338 
 </select>
 342 
 </select>
339
343
340 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
 344 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
341 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
345 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
342 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
346 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
343 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
347 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
344 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
 348 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
345 
 	and i.currentWarehouseId not in (1,2,6,9)
 349 
 	and i.currentWarehouseId not in (1,2,6,9)
346 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
350 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
347 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
351 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
348 
 	group by itemId, s.type
 352 
 	group by itemId, s.type
349 
 </select>
 353 
 </select>
350 
 
 354 
 
351 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
 355 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
352 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
356 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity