| Line 31... |
Line 31... |
| 31 |
ORDER BY scannedAt
|
31 |
ORDER BY scannedAt
|
| 32 |
</select>
|
32 |
</select>
|
| 33 |
|
33 |
|
| 34 |
<select id="getPurchaseScans" parameterType="map" resultType="detailedPurchaseScan">
|
34 |
<select id="getPurchaseScans" parameterType="map" resultType="detailedPurchaseScan">
|
| 35 |
SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
|
35 |
SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
|
| 36 |
GROUP_CONCAT(DISTINCT(invoiceNumber)) AS invoiceNumbers, i.itemId,
|
36 |
GROUP_CONCAT(DISTINCT(p.invoiceNumber)) AS invoiceNumbers, GROUP_CONCAT(DISTINCT(inv.receivedFrom)) AS receivedBy, i.itemId,
|
| 37 |
brand, modelName, modelNumber, color, unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId
|
37 |
brand, modelName, modelNumber, color, unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId
|
| 38 |
FROM purchase p
|
38 |
FROM purchase p
|
| 39 |
JOIN purchaseorder po
|
39 |
JOIN purchaseorder po
|
| 40 |
ON (po.id = p.purchaseOrder_id)
|
40 |
ON (po.id = p.purchaseOrder_id)
|
| 41 |
JOIN inventoryItem i
|
41 |
JOIN inventoryItem i
|
| 42 |
ON (p.id = i.purchaseId)
|
42 |
ON (p.id = i.purchaseId)
|
| 43 |
JOIN lineitem l
|
43 |
JOIN lineitem l
|
| 44 |
ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
|
44 |
ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
|
| 45 |
JOIN supplier s
|
45 |
JOIN supplier s
|
| 46 |
ON (s.id = po.supplierId)
|
46 |
ON (s.id = po.supplierId)
|
| - |
|
47 |
JOIN invoice inv
|
| - |
|
48 |
ON (inv.invoiceNumber = p.invoiceNumber)
|
| 47 |
WHERE po.createdAt BETWEEN #{startDate} AND #{endDate}
|
49 |
WHERE po.createdAt BETWEEN #{startDate} AND #{endDate}
|
| 48 |
AND lastScanType != 'DOA_REPLACED'
|
50 |
AND lastScanType != 'DOA_REPLACED'
|
| 49 |
GROUP BY p.purchaseOrder_id, p.id, i.itemId
|
51 |
GROUP BY p.purchaseOrder_id, p.id, i.itemId
|
| 50 |
ORDER BY p.purchaseOrder_id, p.id, i.itemId
|
52 |
ORDER BY p.purchaseOrder_id, p.id, i.itemId
|
| 51 |
</select>
|
53 |
</select>
|