| Line 212... |
Line 212... |
| 212 |
|
212 |
|
| 213 |
<select id = "getHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
|
213 |
<select id = "getHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
|
| 214 |
SELECT x.itemId, ci.brand, ci.model_name as modelName,
|
214 |
SELECT x.itemId, ci.brand, ci.model_name as modelName,
|
| 215 |
ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
215 |
ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
| 216 |
from
|
216 |
from
|
| 217 |
(select i.itemId, count(*) as inx from scanNew s
|
217 |
(select i.itemId, sum(s.quantity) as inx from scanNew s
|
| 218 |
JOIN inventoryItem i ON (i.id = s.inventoryItemId)
|
218 |
JOIN inventoryItem i ON (i.id = s.inventoryItemId)
|
| 219 |
JOIN catalog.item c ON i.itemId = c.id
|
219 |
JOIN catalog.item c ON i.itemId = c.id
|
| 220 |
where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
|
220 |
where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
|
| 221 |
and s.scannedAt < #{date}
|
221 |
and s.scannedAt < #{date}
|
| 222 |
group by i.itemId ) as x
|
222 |
group by i.itemId ) as x
|
| 223 |
LEFT JOIN
|
223 |
LEFT JOIN
|
| 224 |
(select i.itemId, count(*) as outx from scanNew s
|
224 |
(select i.itemId, sum(s.quantity) as outx from scanNew s
|
| 225 |
JOIN inventoryItem i on i.id = s.inventoryItemId
|
225 |
JOIN inventoryItem i on i.id = s.inventoryItemId
|
| 226 |
JOIN catalog.item c ON i.itemId = c.id
|
226 |
JOIN catalog.item c ON i.itemId = c.id
|
| 227 |
where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
|
227 |
where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
|
| 228 |
and s.scannedAt < #{date}
|
228 |
and s.scannedAt < #{date}
|
| 229 |
group by i.itemId) as y
|
229 |
group by i.itemId) as y
|
| Line 234... |
Line 234... |
| 234 |
|
234 |
|
| 235 |
<select id = "getOurHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
|
235 |
<select id = "getOurHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
|
| 236 |
SELECT x.itemId, ci.brand, ci.model_name as modelName,
|
236 |
SELECT x.itemId, ci.brand, ci.model_name as modelName,
|
| 237 |
ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
237 |
ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
| 238 |
from
|
238 |
from
|
| 239 |
(select i.itemId, count(*) as inx from scanNew s
|
239 |
(select i.itemId, sum(s.quantity) as inx from scanNew s
|
| 240 |
JOIN inventoryItem i ON (i.id = s.inventoryItemId)
|
240 |
JOIN inventoryItem i ON (i.id = s.inventoryItemId)
|
| 241 |
JOIN catalog.item c ON i.itemId = c.id
|
241 |
JOIN catalog.item c ON i.itemId = c.id
|
| 242 |
where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
|
242 |
where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
|
| 243 |
and s.scannedAt < #{date}
|
243 |
and s.scannedAt < #{date}
|
| 244 |
and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
|
244 |
and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
|
| 245 |
and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
|
245 |
and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
|
| 246 |
group by i.itemId ) as x
|
246 |
group by i.itemId ) as x
|
| 247 |
LEFT JOIN
|
247 |
LEFT JOIN
|
| 248 |
(select i.itemId, count(*) as outx from scanNew s
|
248 |
(select i.itemId, sum(s.quantity) as outx from scanNew s
|
| 249 |
JOIN inventoryItem i on i.id = s.inventoryItemId
|
249 |
JOIN inventoryItem i on i.id = s.inventoryItemId
|
| 250 |
JOIN catalog.item c ON i.itemId = c.id
|
250 |
JOIN catalog.item c ON i.itemId = c.id
|
| 251 |
where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
|
251 |
where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
|
| 252 |
and s.scannedAt < #{date}
|
252 |
and s.scannedAt < #{date}
|
| 253 |
and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
|
253 |
and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
|