| Line 62... |
Line 62... |
| 62 |
AND lastScanType != 'DOA_REPLACED'
|
62 |
AND lastScanType != 'DOA_REPLACED'
|
| 63 |
GROUP BY p.purchaseOrder_id, p.id, i.itemId
|
63 |
GROUP BY p.purchaseOrder_id, p.id, i.itemId
|
| 64 |
ORDER BY p.purchaseOrder_id, p.id, i.itemId
|
64 |
ORDER BY p.purchaseOrder_id, p.id, i.itemId
|
| 65 |
</select>
|
65 |
</select>
|
| 66 |
|
66 |
|
| - |
|
67 |
<select id="getPurchaseScansByGrnDate" parameterType="map" resultType="detailedPurchaseScan">
|
| - |
|
68 |
SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
|
| - |
|
69 |
p.invoiceNumber AS invoiceNumbers, inv.receivedFrom AS receivedBy, i.itemId,brand, modelName, modelNumber, color,
|
| - |
|
70 |
unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId
|
| - |
|
71 |
FROM purchase p
|
| - |
|
72 |
JOIN purchaseorder po
|
| - |
|
73 |
ON (po.id = p.purchaseOrder_id)
|
| - |
|
74 |
JOIN inventoryItem i
|
| - |
|
75 |
ON (p.id = i.purchaseId)
|
| - |
|
76 |
JOIN lineitem l
|
| - |
|
77 |
ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
|
| - |
|
78 |
JOIN supplier s
|
| - |
|
79 |
ON (s.id = po.supplierId)
|
| - |
|
80 |
JOIN invoice inv
|
| - |
|
81 |
ON (inv.invoiceNumber = p.invoiceNumber)
|
| - |
|
82 |
WHERE p.receivedOn BETWEEN #{startDate} AND #{endDate}
|
| - |
|
83 |
AND lastScanType != 'DOA_REPLACED'
|
| - |
|
84 |
GROUP BY p.id, i.itemId
|
| - |
|
85 |
ORDER BY p.id, i.itemId
|
| - |
|
86 |
</select>
|
| - |
|
87 |
|
| 67 |
<select id="fetchMismatchScansPerInvoiceNumber" parameterType="java.util.Date" resultType="invoiceScan" >
|
88 |
<select id="fetchMismatchScansPerInvoiceNumber" parameterType="java.util.Date" resultType="invoiceScan" >
|
| 68 |
SELECT i.invoiceNumber, i.numItems, sum(initialQuantity) as scannedQuantity, i.date, s.name as supplierName
|
89 |
SELECT i.invoiceNumber, i.numItems, sum(initialQuantity) as scannedQuantity, i.date, s.name as supplierName
|
| 69 |
FROM invoice i
|
90 |
FROM invoice i
|
| 70 |
JOIN supplier s
|
91 |
JOIN supplier s
|
| 71 |
ON (i.supplierId = s.id)
|
92 |
ON (i.supplierId = s.id)
|