Subversion Repositories SmartDukaan

Rev

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

Rev 12658 Rev 12800
Line 67... Line 67...
67
67
68 
  <select id="getPurchaseScansByGrnDate" parameterType="map" resultType="detailedPurchaseScan">
 68 
  <select id="getPurchaseScansByGrnDate" parameterType="map" resultType="detailedPurchaseScan">
69 
    SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
 69 
    SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
70 
        p.invoiceNumber AS invoiceNumbers, inv.receivedFrom AS receivedBy, i.itemId,brand, modelName, modelNumber, color,
70 
        p.invoiceNumber AS invoiceNumbers, inv.receivedFrom AS receivedBy, i.itemId,brand, modelName, modelNumber, color,
71 
        unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId,
71 
        unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId,
72 
        nlc, po.warehouseId, po.taxType, 1000 * UNIX_TIMESTAMP(inv.invoiceDate) as invoiceDate
 72 
        nlc, po.warehouseId, po.taxType, 1000 * UNIX_TIMESTAMP(inv.invoiceDate) as invoiceDate, p.purchaseComments
73 
    FROM purchase p
 73 
    FROM purchase p
74 
    JOIN purchaseorder po
74 
    JOIN purchaseorder po
75 
        ON (po.id = p.purchaseOrder_id)
 75 
        ON (po.id = p.purchaseOrder_id)
76 
    JOIN inventoryItem i
 76 
    JOIN inventoryItem i
77 
        ON (p.id = i.purchaseId)
 77 
        ON (p.id = i.purchaseId)
Line 116... Line 116...
116 
        SUM((age &gt;= 3 and age &lt; 4) * currentQuantity) AS threeToFourCount,
116 
        SUM((age &gt;= 3 and age &lt; 4) * currentQuantity) AS threeToFourCount,
117 
        SUM((age &gt;= 4) * currentQuantity) as fourPlusCount,
 117 
        SUM((age &gt;= 4) * currentQuantity) as fourPlusCount,
118 
        SUM((age &gt;= 1) * currentQuantity) as onePlusCount,
 118 
        SUM((age &gt;= 1) * currentQuantity) as onePlusCount,
119 
        SUM((age &gt;= 13) * currentQuantity) as threeMonthPlusCount,
 119 
        SUM((age &gt;= 13) * currentQuantity) as threeMonthPlusCount,
120 
        SUM((age &gt;= 26) * currentQuantity) as sixMonthPlusCount,
 120 
        SUM((age &gt;= 26) * currentQuantity) as sixMonthPlusCount,
121 
        SUM((age &gt; 0 and age &lt;= 13) * currentQuantity) AS zeroToThreeMonthCount,
 121 
        SUM((age &gt;= 0 and age &lt;= 13) * currentQuantity) AS zeroToThreeMonthCount,
122 
        SUM((age &gt; 13 and age &lt;= 26) * currentQuantity) AS threeToSixMonthCount,
 122 
        SUM((age &gt; 13 and age &lt;= 26) * currentQuantity) AS threeToSixMonthCount,
123 
        SUM((age &gt; 26 and age &lt;= 52) * currentQuantity) AS sixToTwelveMonthCount,
 123 
        SUM((age &gt; 26 and age &lt;= 52) * currentQuantity) AS sixToTwelveMonthCount,
124 
        SUM((age &gt; 52) * currentQuantity) AS twelveMonthsPlusCount,
 124 
        SUM((age &gt; 52) * currentQuantity) AS twelveMonthsPlusCount,
125 
        SUM((age &gt;= 1) * unitPrice*currentQuantity) as onePlusCost,
 125 
        SUM((age &gt;= 1) * unitPrice*currentQuantity) as onePlusCost,
126 
        SUM(currentQuantity) as zeroPlusCount,
 126 
        SUM(currentQuantity) as zeroPlusCount,
Line 462... Line 462...
462 
	SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId
462 
	SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId
463 
	FROM scanNew s
463 
	FROM scanNew s
464 
	JOIN inventoryItem i on s.inventoryItemId = i.id
464 
	JOIN inventoryItem i on s.inventoryItemId = i.id
465 
	WHERE i.purchaseId= #{purchaseId} and s.type=#{scanType}
 465 
	WHERE i.purchaseId= #{purchaseId} and s.type=#{scanType}
466 
</select>
 466 
</select>
467
467 
 
- 
 
 468 
<select id="getInventoryAgeConsideredItems" resultType="invAgeConsiderItems" parameterType = "java.lang.Long">
- 
 
 469 
	SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId, DATEDIFF(now(), MIN(s.scannedAt)) / 7 as age,
- 
 
 470 
	l.productGroup as category, (l.unitPrice * ii.currentQuantity) as cost
- 
 
 471 
	FROM inventoryItem ii JOIN scanNew s ON (s.inventoryItemId = ii.id) JOIN purchase p ON (p.id = ii.purchaseId)
- 
 
 472 
	JOIN lineitem l ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = ii.itemId)
- 
 
 473 
	WHERE ((ii.lastScanType IN ('PURCHASE', 'SALE_RET', 'MARKED_GOOD')) OR (ii.currentQuantity>0) OR (ii.lastScanType IN ('SALE') and ii.currentQuantity = 1))
- 
 
 474 
	AND ii.lastScanType != 'MARKED_BAD' AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT') AND ii.itemId = #{itemId}
- 
 
 475 
	AND ii.physicalWarehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
- 
 
 476 
	GROUP BY ii.id, ii.itemId, ii.currentQuantity order by ii.id desc;
- 
 
 477 
</select>
- 
 
 478 
 
468 
</mapper>
 479 
</mapper>