| Line 68... |
Line 68... |
| 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, sgstRate, cgstRate, igstRate, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId,
|
71 |
unitPrice, sgstRate, cgstRate, igstRate, 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, p.purchaseComments
|
72 |
nlc, po.warehouseId, po.taxType, 1000 * UNIX_TIMESTAMP(inv.invoiceDate) as invoiceDate, p.purchaseComments
|
| 73 |
FROM purchase p
|
73 |
FROM purchaseorder po
|
| 74 |
JOIN purchaseorder po
|
74 |
JOIN purchase p
|
| 75 |
ON (po.id = p.purchaseOrder_id)
|
75 |
ON (po.id = p.purchaseOrder_id)
|
| - |
|
76 |
JOIN (select * from invoice limit 1) inv
|
| - |
|
77 |
ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId)
|
| 76 |
JOIN inventoryItem i
|
78 |
JOIN inventoryItem i
|
| 77 |
ON (p.id = i.purchaseId)
|
79 |
ON (p.id = i.purchaseId)
|
| 78 |
JOIN lineitem l
|
80 |
JOIN lineitem l
|
| 79 |
ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
|
81 |
ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
|
| 80 |
JOIN supplier s
|
82 |
JOIN supplier s
|
| 81 |
ON (s.id = po.supplierId)
|
83 |
ON (s.id = po.supplierId)
|
| 82 |
JOIN invoice inv
|
- |
|
| 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
|