| Line 231... |
Line 231... |
| 231 |
JOIN catalog.item ci ON ci.id = x.itemId
|
231 |
JOIN catalog.item ci ON ci.id = x.itemId
|
| 232 |
where ci.type = 'SERIALIZED' having quantity>0
|
232 |
where ci.type = 'SERIALIZED' having quantity>0
|
| 233 |
</select>
|
233 |
</select>
|
| 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 z.Item_Id as itemId, z.Brand as brand, z.ModelName as modelName, z.ModelNumber as modelNumber, z.Color as color,
|
| 237 |
ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
237 |
sum(z.Quantity) quantity FROM (SELECT x.itemId Item_Id, ci.brand Brand, ci.model_name as ModelName, ci.model_number as ModelNumber,
|
| 238 |
from
|
- |
|
| 239 |
(select i.itemId, sum(s.quantity) as inx from scanNew s
|
238 |
ci.color Color, (x.inx-if(y.outx is null,0,y.outx)) Quantity, x.billingWarehouseId, wh.displayName Warehouse
|
| 240 |
JOIN inventoryItem i ON (i.id = s.inventoryItemId)
|
239 |
FROM (SELECT i.itemId, sum(quantity) as inx, w.billingWarehouseId FROM scanNew s JOIN inventoryItem i ON (i.id = s.inventoryItemId)
|
| 241 |
JOIN catalog.item c ON i.itemId = c.id
|
240 |
JOIN catalog.item c ON i.itemId = c.id JOIN inventory.warehouse w ON w.id = s.warehouseId
|
| 242 |
where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
|
241 |
WHERE s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') AND c.type= 'SERIALIZED'
|
| 243 |
and s.scannedAt < #{date}
|
- |
|
| 244 |
and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
|
242 |
AND w.billingWarehouseId not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD'
|
| 245 |
and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
|
243 |
and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt < #{date} GROUP BY i.itemId, w.billingWarehouseId ) as x
|
| 246 |
group by i.itemId ) as x
|
- |
|
| 247 |
LEFT JOIN
|
- |
|
| 248 |
(select i.itemId, sum(s.quantity) as outx from scanNew s
|
- |
|
| 249 |
JOIN inventoryItem i on i.id = s.inventoryItemId
|
244 |
LEFT JOIN (SELECT i.itemId, sum(quantity) as outx, w.billingWarehouseId FROM scanNew s JOIN inventoryItem i on i.id = s.inventoryItemId
|
| 250 |
JOIN catalog.item c ON i.itemId = c.id
|
245 |
JOIN catalog.item c ON i.itemId = c.id JOIN inventory.warehouse w ON w.id = s.warehouseId
|
| 251 |
where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
|
246 |
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}
|
- |
|
| 253 |
and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
|
247 |
AND w.billingWarehouseId not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD'
|
| 254 |
and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
|
248 |
and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt < #{date}
|
| 255 |
group by i.itemId) as y
|
- |
|
| 256 |
ON x.itemId = y.itemId
|
- |
|
| 257 |
JOIN catalog.item ci ON ci.id = x.itemId
|
249 |
GROUP BY i.itemId, w.billingWarehouseId ) as y ON (x.itemId = y.itemId AND x.billingWarehouseId = y.billingWarehouseId)
|
| 258 |
where ci.type = 'SERIALIZED' having quantity>0
|
250 |
JOIN catalog.item ci ON ci.id = x.itemId JOIN inventory.warehouse wh on (x.billingWarehouseId = wh.id) having quantity>0) as z group by z.Item_Id
|
| 259 |
</select>
|
251 |
</select>
|
| 260 |
|
252 |
|
| 261 |
|
253 |
|
| 262 |
<select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
|
254 |
<select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
|
| 263 |
SELECT x.itemId, ci.brand, ci.model_name as modelName,
|
255 |
SELECT x.itemId, ci.brand, ci.model_name as modelName,
|
| Line 304... |
Line 296... |
| 304 |
JOIN catalog.item ci ON ci.id = x.itemId
|
296 |
JOIN catalog.item ci ON ci.id = x.itemId
|
| 305 |
where ci.type = 'NON_SERIALIZED' having quantity>0
|
297 |
where ci.type = 'NON_SERIALIZED' having quantity>0
|
| 306 |
</select>
|
298 |
</select>
|
| 307 |
|
299 |
|
| 308 |
<select id = "getOurHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
|
300 |
<select id = "getOurHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
|
| - |
|
301 |
SELECT z.Item_Id as itemId, z.Brand as brand, z.ModelName as modelName, z.ModelNumber as modelNumber, z.Color as color,
|
| 309 |
SELECT x.itemId, ci.brand, ci.model_name as modelName,
|
302 |
sum(z.Quantity) as quantity FROM (SELECT x.itemId Item_Id, ci.brand Brand, ci.model_name as ModelName, ci.model_number as ModelNumber,
|
| 310 |
ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
|
303 |
ci.color Color, (x.inx-if(y.outx is null,0,y.outx)) Quantity, x.billingWarehouseId, wh.displayName Warehouse
|
| 311 |
from
|
- |
|
| 312 |
(SELECT i.itemId, sum(quantity) as inx from scanNew s
|
304 |
FROM (SELECT i.itemId, sum(quantity) as inx, w.billingWarehouseId FROM scanNew s
|
| 313 |
JOIN inventoryItem i ON (i.id = s.inventoryItemId)
|
305 |
JOIN inventoryItem i ON (i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id
|
| 314 |
JOIN catalog.item c ON i.itemId = c.id
|
- |
|
| 315 |
where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
|
306 |
JOIN inventory.warehouse w ON w.id = s.warehouseId WHERE s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN')
|
| 316 |
and s.scannedAt < #{date}
|
- |
|
| 317 |
and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
|
307 |
AND c.type= 'NON_SERIALIZED' AND w.billingWarehouseId not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD'
|
| 318 |
and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
|
308 |
and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt < #{date} GROUP BY i.itemId, w.billingWarehouseId ) as x
|
| 319 |
group by i.itemId ) as x
|
- |
|
| 320 |
LEFT JOIN
|
- |
|
| 321 |
(SELECT i.itemId, sum(quantity) as outx from scanNew s
|
309 |
LEFT JOIN (SELECT i.itemId, sum(quantity) as outx, w.billingWarehouseId FROM scanNew s JOIN inventoryItem i on i.id = s.inventoryItemId
|
| 322 |
JOIN inventoryItem i on i.id = s.inventoryItemId
|
- |
|
| 323 |
JOIN catalog.item c ON i.itemId = c.id
|
310 |
JOIN catalog.item c ON i.itemId = c.id JOIN inventory.warehouse w ON w.id = s.warehouseId
|
| 324 |
where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
|
311 |
WHERE s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT')
|
| 325 |
and s.scannedAt < #{date}
|
- |
|
| 326 |
and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40
|
312 |
AND c.type= 'NON_SERIALIZED' AND w.billingWarehouseId not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD'
|
| 327 |
and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
|
313 |
and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt < #{date} GROUP BY i.itemId, w.billingWarehouseId ) as y
|
| 328 |
group by i.itemId) as y
|
- |
|
| 329 |
ON x.itemId = y.itemId
|
- |
|
| 330 |
JOIN catalog.item ci ON ci.id = x.itemId
|
314 |
ON (x.itemId = y.itemId AND x.billingWarehouseId = y.billingWarehouseId) JOIN catalog.item ci ON ci.id = x.itemId
|
| 331 |
where ci.type = 'NON_SERIALIZED' having quantity>0
|
315 |
JOIN inventory.warehouse wh on (x.billingWarehouseId = wh.id) having quantity>0) as z group by z.Item_Id
|
| 332 |
</select>
|
316 |
</select>
|
| 333 |
|
317 |
|
| 334 |
<select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
|
318 |
<select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
|
| 335 |
SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
|
319 |
SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity
|
| 336 |
FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
|
320 |
FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId
|
| Line 376... |
Line 360... |
| 376 |
FROM possibleWarehouseMovement
|
360 |
FROM possibleWarehouseMovement
|
| 377 |
WHERE fromWarehouseId = #{warehouseId}
|
361 |
WHERE fromWarehouseId = #{warehouseId}
|
| 378 |
</select>
|
362 |
</select>
|
| 379 |
|
363 |
|
| 380 |
<select id = "getHistoricBadInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
|
364 |
<select id = "getHistoricBadInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
|
| 381 |
SELECT badX.id as itemId, badX.brand, badX.model_name as modelName, badX.model_number as modelNumber, badX.color, sum(badX.Bad_quantity) as quantity
|
365 |
SELECT z.Item_Id as itemId, z.Brand as brand, z.ModelName as modelName, z.ModelNumber as modelNumber, z.Color as color, sum(z.Quantity) as quantity
|
| 382 |
FROM (SELECT x.id, x.label as category,x.product_group,x.brand, x.model_name,x.model_number, x.color, (x.quantity-if(y.quantity is null,0,y.quantity)) as Bad_quantity,
|
- |
|
| 383 |
x.physicalWarehouseId,x.purchaseId
|
- |
|
| 384 |
FROM (SELECT it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number, it.color,
|
366 |
FROM (SELECT x.itemId Item_Id, ci.brand Brand, ci.model_name as ModelName, ci.model_number as ModelNumber, ci.color Color,
|
| 385 |
sum(s.quantity) as quantity, i.physicalWarehouseId,i.purchaseId FROM scanNew s
|
367 |
(x.inx-if(y.outx is null,0,y.outx)) Quantity, x.billingWarehouseId, wh.displayName Warehouse
|
| 386 |
JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on i.purchaseId= p.id
|
368 |
FROM (SELECT i.itemId, sum(quantity) as inx, w.billingWarehouseId FROM scanNew s
|
| 387 |
JOIN catalog.item it on i.itemId= it.id JOIN catalog.category ct on it.category = ct.id
|
369 |
JOIN inventoryItem i ON (i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id
|
| 388 |
WHERE s.type in ('MARKED_BAD','DOA_IN','SALE_RET_UNUSABLE','DOA_REJECTED')
|
370 |
JOIN inventory.warehouse w ON w.id = s.warehouseId WHERE s.type in ('MARKED_BAD','DOA_IN','SALE_RET_UNUSABLE','DOA_REJECTED')
|
| 389 |
AND s.scannedAt > '2013-01-01' AND s.scannedAt < #{date}
|
371 |
AND w.billingWarehouseId not in (9,12) AND s.scannedAt > '2013-01-01'
|
| 390 |
AND i.physicalWarehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
|
- |
|
| 391 |
group by i.itemId,i.purchaseId) as x
|
- |
|
| 392 |
LEFT OUTER JOIN (SELECT it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number,
|
- |
|
| 393 |
it.color, sum(s.quantity) as quantity, i.physicalWarehouseId, i.purchaseId
|
372 |
AND s.scannedAt < #{date} GROUP BY i.itemId, w.billingWarehouseId ) as x
|
| 394 |
FROM scanNew s JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on i.purchaseId= p.id
|
373 |
LEFT JOIN (SELECT i.itemId, sum(quantity) as outx, w.billingWarehouseId FROM scanNew s JOIN inventoryItem i on i.id = s.inventoryItemId
|
| 395 |
JOIN catalog.item it on i.itemId= it.id JOIN catalog.category ct on it.category = ct.id
|
374 |
JOIN catalog.item c ON i.itemId = c.id JOIN inventory.warehouse w ON w.id = s.warehouseId
|
| 396 |
WHERE s.type in ('DOA_OUT','BAD_SALE','MARKED_GOOD','BAD_PURCHASE_RETURN','LOST_IN_WAREHOUSE_BAD')
|
375 |
WHERE s.type in ('DOA_OUT','BAD_SALE','MARKED_GOOD','BAD_PURCHASE_RETURN','LOST_IN_WAREHOUSE_BAD')
|
| 397 |
AND s.scannedAt > '2013-01-01' AND s.scannedAt < #{date}
|
376 |
AND w.billingWarehouseId not in (9,12) AND s.scannedAt > '2013-01-01'
|
| 398 |
AND i.physicalWarehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
|
377 |
AND s.scannedAt < #{date} GROUP BY i.itemId, w.billingWarehouseId ) as y ON (x.itemId = y.itemId
|
| 399 |
group by i.itemId,i.purchaseId) as y
|
- |
|
| 400 |
on x.id=y.id AND x.purchaseId=y.purchaseId
|
378 |
AND x.billingWarehouseId = y.billingWarehouseId) JOIN catalog.item ci ON ci.id = x.itemId
|
| 401 |
group by x.id,x.purchaseId having Bad_quantity>0)
|
379 |
JOIN inventory.warehouse wh on (x.billingWarehouseId = wh.id) having quantity>0) as z group by z.Item_Id
|
| 402 |
as badX group by badX.id
|
- |
|
| 403 |
</select>
|
380 |
</select>
|
| 404 |
|
381 |
|
| 405 |
<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
|
382 |
<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
|
| 406 |
SELECT *
|
383 |
SELECT *
|
| 407 |
FROM scanNew
|
384 |
FROM scanNew
|