| Line 54... |
Line 54... |
| 54 |
|
54 |
|
| 55 |
<select id="fetchMismatchScansPerInvoiceNumber" parameterType="java.util.Date" resultType="invoiceScan" >
|
55 |
<select id="fetchMismatchScansPerInvoiceNumber" parameterType="java.util.Date" resultType="invoiceScan" >
|
| 56 |
SELECT i.invoiceNumber, i.numItems, sum(initialQuantity) as scannedQuantity, i.date, s.name as supplierName
|
56 |
SELECT i.invoiceNumber, i.numItems, sum(initialQuantity) as scannedQuantity, i.date, s.name as supplierName
|
| 57 |
FROM invoice i
|
57 |
FROM invoice i
|
| 58 |
JOIN supplier s
|
58 |
JOIN supplier s
|
| 59 |
ON (s.id = i.supplierId)
|
- |
|
| 60 |
LEFT JOIN purchase p
|
59 |
LEFT JOIN purchase p
|
| 61 |
ON (p.invoiceNumber = i.invoiceNumber)
|
60 |
ON (p.invoiceNumber = i.invoiceNumber)
|
| 62 |
LEFT JOIN inventoryItem ii ON (ii.purchaseId = p.id)
|
61 |
LEFT JOIN inventoryItem ii
|
| - |
|
62 |
ON (ii.purchaseId = p.id)
|
| - |
|
63 |
JOIN scanNew sn
|
| - |
|
64 |
ON (sn.inventoryItemId = ii.id)
|
| 63 |
WHERE i.date = #{date}
|
65 |
WHERE i.date = #{date}
|
| - |
|
66 |
AND sn.scannedAt between #{date} and ADDDATE(#{date},1)
|
| - |
|
67 |
AND sn.type = 'PURCHASE'
|
| - |
|
68 |
AND s.id !=1
|
| 64 |
GROUP BY i.invoiceNumber, i.numItems
|
69 |
GROUP BY i.invoiceNumber, i.numItems
|
| 65 |
HAVING SUM(initialQuantity) != i.numItems
|
70 |
HAVING SUM(initialQuantity) != i.numItems
|
| 66 |
</select>
|
71 |
</select>
|
| 67 |
|
72 |
|
| 68 |
<select id="getInventoryAge" resultType="inventoryAge">
|
73 |
<select id="getInventoryAge" resultType="inventoryAge">
|
| Line 90... |
Line 95... |
| 90 |
ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
|
95 |
ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
|
| 91 |
GROUP BY t.itemId
|
96 |
GROUP BY t.itemId
|
| 92 |
HAVING SUM(currentQuantity) != 0
|
97 |
HAVING SUM(currentQuantity) != 0
|
| 93 |
</select>
|
98 |
</select>
|
| 94 |
|
99 |
|
| - |
|
100 |
<!--select id="inventoryAgeOnePlus" resultType="detailedInventoryAge">
|
| - |
|
101 |
SELECT t.itemId, brand, modelName, modelNumber, color, scannedAt, i.warehouseId
|
| - |
|
102 |
SUM((age >= 1) * currentQuantity) AS onePlusCount,
|
| - |
|
103 |
SUM((age >= 1) * unitPrice) as onePlusCost,
|
| - |
|
104 |
l.productGroup as category
|
| - |
|
105 |
FROM (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId, MIN(s.scannedAt)
|
| - |
|
106 |
DATEDIFF(now(), MAX(s.scannedAt)) / 7 as age
|
| - |
|
107 |
FROM inventoryItem ii
|
| - |
|
108 |
JOIN scanNew s
|
| - |
|
109 |
ON (s.inventoryItemId = ii.id)
|
| - |
|
110 |
WHERE ii.lastScanType IN ('PURCHASE', 'SALE_RET')
|
| - |
|
111 |
GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
|
| - |
|
112 |
JOIN purchase p
|
| - |
|
113 |
ON (p.id = t.purchaseId)
|
| - |
|
114 |
JOIN lineitem l
|
| - |
|
115 |
ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
|
| - |
|
116 |
GROUP BY t.itemId, i.warehouseId
|
| - |
|
117 |
HAVING SUM(currentQuantity) != 0
|
| - |
|
118 |
</select-->
|
| - |
|
119 |
|
| - |
|
120 |
|
| - |
|
121 |
|
| 95 |
<select id = "getScansForItem" parameterType = "map" resultType = "scan">
|
122 |
<select id = "getScansForItem" parameterType = "map" resultType = "scan">
|
| 96 |
SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt,
|
123 |
SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt,
|
| 97 |
s.quantity, s.orderId from scanNew s JOIN inventoryItem i
|
124 |
s.quantity, s.orderId from scanNew s JOIN inventoryItem i
|
| 98 |
ON
|
125 |
ON
|
| 99 |
s.inventoryItemId = i.id
|
126 |
s.inventoryItemId = i.id
|