Subversion Repositories SmartDukaan

Rev

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

Rev 11750 Rev 12357
Line 81... Line 81...
81 
        ON (s.id = po.supplierId)
 81 
        ON (s.id = po.supplierId)
82 
    JOIN invoice inv
 82 
    JOIN invoice inv
83 
    	ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId)
 83 
    	ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId)
84 
    WHERE p.receivedOn BETWEEN #{startDate} AND #{endDate}
 84 
    WHERE p.receivedOn BETWEEN #{startDate} AND #{endDate}
85 
    AND inv.date  < #{endDate}
 85 
    AND inv.date  < #{endDate}
86 
    AND inv.date  > ADDDATE(#{startDate},-1)
 86 
    AND inv.date  >= ADDDATE(#{startDate},-1)
87 
    AND lastScanType != 'DOA_REPLACED'
87 
    AND lastScanType != 'DOA_REPLACED'
88 
    GROUP BY p.purchaseOrder_id, p.id, i.itemId
 88 
    GROUP BY p.purchaseOrder_id, p.id, i.itemId
89 
    ORDER BY p.purchaseOrder_id, p.id, i.itemId
 89 
    ORDER BY p.purchaseOrder_id, p.id, i.itemId
90 
  </select>
 90 
  </select>
91
91
Line 132... Line 132...
132 
           JOIN scanNew s
132 
           JOIN scanNew s
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 (16, 1771)
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 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
139 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
139 
    JOIN purchase p
140 
    JOIN purchase p
140 
        ON (p.id = t.purchaseId)
141 
        ON (p.id = t.purchaseId)
141 
    JOIN lineitem l
142 
    JOIN lineitem l
142 
        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 329... Line 330...
329 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
330 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
330 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
331 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
331 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
332 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
332 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
 333 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
333 
 	and i.currentWarehouseId not in (1,2,6,9)
334 
 	and i.currentWarehouseId not in (1,2,6,9)
334 
 	and s.warehouseId != 16
 335 
 	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)
335 
 	group by itemId, s.type
 337 
 	group by itemId, s.type
336 
 </select>
 338 
 </select>
337
339
338 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
 340 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
339 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
341 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
340 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
342 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
341 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
343 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
342 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
 344 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
343 
 	and i.currentWarehouseId not in (1,2,6,9)
 345 
 	and i.currentWarehouseId not in (1,2,6,9)
344 
 	and s.warehouseId != 16
346 
 	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)
345 
 	group by itemId, s.type
 348 
 	group by itemId, s.type
346 
 </select>
 349 
 </select>
347 
 
 350 
 
348 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
 351 
 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
349 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
352 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
350 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
353 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
351 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
354 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
352 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
 355 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
353 
 	and s.warehouseId != 16
 356 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
- 
 
 357 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
354 
 	group by itemId, s.type
 358 
 	group by itemId, s.type
355 
 </select>
 359 
 </select>
356
360
357 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
 361 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
358 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
362 
 	SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity
359 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
363 
 	FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
360 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
364 
 	LEFT JOIN catalog.item c ON i.itemId = c.id
361 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
 365 
 	where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
362 
 	and s.warehouseId != 16
 366 
 	and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
- 
 
 367 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
363 
 	group by itemId, s.type
 368 
 	group by itemId, s.type
364 
 </select>
 369 
 </select>
365
370
366 
<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
 371 
<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
367 
 	SELECT toWarehouseId
372 
 	SELECT toWarehouseId
Line 402... Line 407...
402 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
 407 
	AND type = 'WAREHOUSE_TRANSFER_OUT'
403 
	LIMIT 1
 408 
	LIMIT 1
404 
</select>
 409 
</select>
405 
 
 410 
 
406 
<select id="getAmazonTransferredSkuDetails" resultType= "amazonTransferredSkuDetail" parameterType="map">
 411 
<select id="getAmazonTransferredSkuDetails" resultType= "amazonTransferredSkuDetail" parameterType="map">
407 
	SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, sum(Y.quantity) as quantity, Y.unitPrice , Y.brand, Y.modelName, Y.modelNumber, Y.category, Y.color
 412 
	SELECT Z.itemId, Z.purchaseId, Z.purchaseDate, sum(Z.quantity) as quantity, Z.unitPrice, Z.nlc,
- 
 
 413 
	Z.brand, Z.modelName, Z.modelNumber, Z.category, Z.color, Z.taxType
408 
	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
414 
	FROM  (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.nlc, X.brand,
- 
 
 415 
	X.modelName, X.modelNumber, X.category, X.color , X.inventoryItemId, X.scanTime, X.taxType
409 
	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
416 
	FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate,
410 
	FROM scanNew s
417 
	s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.brand as brand,
- 
 
 418 
	l.modelName as modelName, l.modelNumber as modelNumber, l.productGroup as category,
411 
	JOIN inventoryItem i on s.inventoryItemId= i.id
419 
	l.color as color , i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType
- 
 
 420 
	WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s
412 
	JOIN transferLot t on (s.transferLotId= t.id)
421 
	JOIN inventoryItem i on s.inventoryItemId= i.id JOIN transferLot t on (s.transferLotId= t.id)
413 
	JOIN purchase p on p.id= i.purchaseId
422 
	JOIN purchase p on p.id= i.purchaseId JOIN purchaseorder po on p.purchaseOrder_id = po.id
414 
	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId)
423 
	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId)
415 
	WHERE i.itemId in
424 
	WHERE i.itemId in
416 
	<foreach item="itemId" index="index" collection="itemIds"
 425 
	<foreach item="itemId" index="index" collection="itemIds"
417 
        open="(" separator="," close=")">
 426 
        open="(" separator="," close=")">
418 
	 #{itemId}
427 
	 #{itemId}
419 
	</foreach>
428 
	</foreach>
420 
	AND t.destinationWarehouseId in (16, 1771)
429 
	and t.destinationWarehouseId in (select id from inventory.warehouse where vendor_id= 40
- 
 
 430 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
421 
	AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER')
431 
	AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER')
422 
	AND i.transferStatus is not null AND type='WAREHOUSE_TRANSFER_OUT'
432 
	AND i.transferStatus is not null AND type='WAREHOUSE_TRANSFER_OUT'
423 
	group by i.itemId, s.transferLotId, i.purchaseId, i.id
433 
	GROUP BY i.itemId, s.transferLotId, i.purchaseId, i.id order by s.scannedAt desc)
- 
 
 434 
	as X GROUP BY X.inventoryItemId
- 
 
 435 
	UNION SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, Y.quantity, Y.unitPrice, Y.nlc, Y.brand,
- 
 
 436 
	Y.modelName, Y.modelNumber, Y.category, Y.color , Y.inventoryItemId, Y.scanTime, Y.taxType
- 
 
 437 
	FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate,
- 
 
 438 
	s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.brand as brand, l.modelName,
- 
 
 439 
	l.modelNumber as modelNumber, l.productGroup as category, l.color as color,
- 
 
 440 
	i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType
- 
 
 441 
	WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s
424 
	order by s.scannedAt desc) as X group by inventoryItemId) as Y group by Y.itemId, Y.purchaseId
 442 
	JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on p.id= i.purchaseId
425 
	order by Y.purchaseId desc
 443 
	JOIN purchaseorder po on p.purchaseOrder_id = po.id
- 
 
 444 
	JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId)
- 
 
 445 
	WHERE i.itemId in
- 
 
 446 
	<foreach item="itemId" index="index" collection="itemIds"
- 
 
 447 
        open="(" separator="," close=")">
- 
 
 448 
	 #{itemId}
- 
 
 449 
	</foreach>
- 
 
 450 
	and po.warehouseId in (select id from inventory.warehouse where vendor_id= 40
- 
 
 451 
	and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3)
- 
 
 452 
	GROUP BY i.itemId, i.purchaseId, i.id ORDER BY s.scannedAt desc) as Y
- 
 
 453 
	GROUP BY Y.inventoryItemId) as Z GROUP BY Z.itemId, Z.purchaseId order by Z.purchaseId desc;
426 
</select>
 454 
</select>
427 
 
 455 
 
428 
<select id="getScansforPurchase" resultType="scan" parameterType="map" >
 456 
<select id="getScansforPurchase" resultType="scan" parameterType="map" >
429 
	SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId
457 
	SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId
430 
	FROM scanNew s
458 
	FROM scanNew s