Subversion Repositories SmartDukaan

Rev

Rev 11583 | Rev 12357 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="in.shop2020.warehouse.persistence.ScanMapper">
  <resultMap type="scan" id="scanResult">
    <id property="id" column="id"/>
  </resultMap>

  <insert id="insert" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO scanNew
      (inventoryItemId, warehouseId, type, scannedAt, quantity, orderId, transferLotId, remarks)
    VALUES
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId}, #{transferLotId}, #{remarks})
  </insert>
  
  <select id="get" parameterType="map" resultType="scan">
    SELECT * 
    FROM scanNew
    WHERE inventoryItemId = #{inventoryItemId}
    <if test="scanType != null">
        AND type = #{scanType}
    </if>
    ORDER BY scannedAt
  </select>

  <select id="getScansFromOrderId" parameterType="java.lang.Long" resultType="scan">
    SELECT * 
    FROM scanNew
    WHERE orderId = #{orderId}
    ORDER BY scannedAt
  </select>
  
  <select id="getScansForOrderAndItem" parameterType="map" resultType="scan">
    SELECT  s.id as id, s.inventoryItemId as inventoryItemId, s.warehouseId as warehouseId, s.type as type, 
        s.scannedAt as scannedAt, s.quantity as quantity, s.orderId as orderId
    FROM scanNew s
    JOIN inventoryItem i
        ON (s.inventoryItemId = i.id)
    WHERE i.itemId = #{itemId}
    AND s.orderId = #{orderId}
    ORDER BY scannedAt
  </select>  
  
  
  <select id="getPurchaseScans" parameterType="map" resultType="detailedPurchaseScan">
    SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
        GROUP_CONCAT(DISTINCT(p.invoiceNumber)) AS invoiceNumbers, GROUP_CONCAT(DISTINCT(inv.receivedFrom)) AS receivedBy, i.itemId,
        brand, modelName, modelNumber, color, unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, 
        p.id as purchaseId, nlc, po.warehouseId 
    FROM purchase p
    JOIN purchaseorder po 
        ON (po.id = p.purchaseOrder_id)
    JOIN inventoryItem i
        ON (p.id = i.purchaseId)
    JOIN lineitem l
        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
    JOIN supplier s
        ON (s.id = po.supplierId)
    JOIN invoice inv
        ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId) 
    WHERE po.createdAt BETWEEN #{startDate} AND #{endDate} 
    AND lastScanType != 'DOA_REPLACED' 
    GROUP BY p.purchaseOrder_id, p.id, i.itemId
    ORDER BY p.purchaseOrder_id, p.id, i.itemId
  </select>
  
  <select id="getPurchaseScansByGrnDate" parameterType="map" resultType="detailedPurchaseScan">
    SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
        p.invoiceNumber AS invoiceNumbers, inv.receivedFrom AS receivedBy, i.itemId,brand, modelName, modelNumber, color, 
        unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId, 
        nlc, po.warehouseId, po.taxType, 1000 * UNIX_TIMESTAMP(inv.invoiceDate) as invoiceDate
    FROM purchase p
    JOIN purchaseorder po 
        ON (po.id = p.purchaseOrder_id)
    JOIN inventoryItem i
        ON (p.id = i.purchaseId)
    JOIN lineitem l
        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
    JOIN supplier s
        ON (s.id = po.supplierId)
    JOIN invoice inv
        ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId)
    WHERE p.receivedOn BETWEEN #{startDate} AND #{endDate}
    AND inv.date  &lt; #{endDate}
    AND inv.date  &gt; ADDDATE(#{startDate},-1)
    AND lastScanType != 'DOA_REPLACED' 
    GROUP BY p.purchaseOrder_id, p.id, i.itemId
    ORDER BY p.purchaseOrder_id, p.id, i.itemId
  </select>
  
  <select id="fetchMismatchScansPerInvoiceNumber" parameterType="java.util.Date" resultType="invoiceScan" >
    SELECT i.invoiceNumber, i.numItems, sum(initialQuantity) as scannedQuantity, i.date, s.name as supplierName
    FROM invoice i
    JOIN supplier s
        ON (i.supplierId = s.id)
    LEFT JOIN purchase p
        ON (p.invoiceNumber = i.invoiceNumber)
    LEFT JOIN inventoryItem ii 
        ON (ii.purchaseId = p.id)
    JOIN scanNew sn
        ON (sn.inventoryItemId = ii.id)
    WHERE i.date = #{date}
    AND sn.scannedAt between #{date} and ADDDATE(#{date},1)
    AND sn.type = 'PURCHASE'
    AND s.id !=1
    GROUP BY i.invoiceNumber, i.numItems
    HAVING SUM(initialQuantity) != i.numItems
  </select>

  <select id="getInventoryAge" resultType="inventoryAge">
    SELECT t.itemId, brand, modelName, modelNumber, color,
        SUM((age &lt; 1) * currentQuantity) AS freshCount, 
        SUM((age &gt;= 1 and age &lt; 2) * currentQuantity) AS oneToTwoCount, 
        SUM((age &gt;= 2 and age &lt; 3) * currentQuantity) AS twoToThreeCount, 
        SUM((age &gt;= 3 and age &lt; 4) * currentQuantity) AS threeToFourCount, 
        SUM((age &gt;= 4) * currentQuantity) as fourPlusCount,
        SUM((age &gt;= 1) * currentQuantity) as onePlusCount,
        SUM((age &gt;= 13) * currentQuantity) as threeMonthPlusCount,
        SUM((age &gt;= 26) * currentQuantity) as sixMonthPlusCount,
        SUM((age &gt; 0 and age &lt;= 13) * currentQuantity) AS zeroToThreeMonthCount,
        SUM((age &gt; 13 and age &lt;= 26) * currentQuantity) AS threeToSixMonthCount,
        SUM((age &gt; 26 and age &lt;= 52) * currentQuantity) AS sixToTwelveMonthCount,
        SUM((age &gt; 52) * currentQuantity) AS twelveMonthsPlusCount,
        SUM((age &gt;= 1) * unitPrice*currentQuantity) as onePlusCost,
        SUM(currentQuantity) as zeroPlusCount,
        SUM(l.unitPrice*currentQuantity) as zeroPlusCost,
        l.productGroup as category
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId,
                DATEDIFF(now(), MIN(s.scannedAt)) / 7 as age
           FROM inventoryItem ii 
           JOIN scanNew s 
                ON (s.inventoryItemId = ii.id) 
           WHERE (ii.lastScanType IN ('PURCHASE', 'SALE_RET', 'MARKED_GOOD') 
           OR ii.currentQuantity>1)
           AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT')
           AND ii.physicalWarehouseId NOT IN (16, 1771) 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t 
    JOIN purchase p 
        ON (p.id = t.purchaseId) 
    JOIN lineitem l 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
    GROUP BY t.itemId 
    HAVING SUM(currentQuantity) != 0
  </select>
  
  <!--select id="inventoryAgeOnePlus" resultType="detailedInventoryAge">
    SELECT t.itemId, brand, modelName, modelNumber, color, scannedAt, i.warehouseId
        SUM((age &gt;= 1) * currentQuantity) AS onePlusCount, 
        SUM((age &gt;= 1) * unitPrice) as onePlusCost,
        l.productGroup as category
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId, MIN(s.scannedAt)
                DATEDIFF(now(), MAX(s.scannedAt)) / 7 as age
           FROM inventoryItem ii 
           JOIN scanNew s 
                ON (s.inventoryItemId = ii.id) 
           WHERE ii.lastScanType IN ('PURCHASE', 'SALE_RET') 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t 
    JOIN purchase p 
        ON (p.id = t.purchaseId) 
    JOIN lineitem l 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
    GROUP BY t.itemId, i.warehouseId 
    HAVING SUM(currentQuantity) != 0
  </select-->
  
  
  
  <select id = "getScansForItem" parameterType = "map" resultType = "scan">
          SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, 
          s.quantity, s.orderId from scanNew s JOIN inventoryItem i 
          ON 
          s.inventoryItemId = i.id 
          AND
          i.itemId = #{itemId} 
          AND
          s.scannedAt BETWEEN #{fromDate} AND #{toDate};
  </select>
  
  <insert id="genericScan" parameterType="scan" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO scanNew
     (inventoryItemId, warehouseId, type, scannedAt, quantity, orderId, transferLotId, remarks)
    VALUES
      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId}, #{transferLotId}, #{remarks})
  </insert>
  
  <select id = "getCurrentSerializedInventoryByScans" resultType = "inventoryAvailability">
        SELECT x.itemId, ci.brand, ci.model_name as modelName, 
        ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
        from 
        (select i.itemId, sum(quantity) as inx from scanNew s 
                JOIN inventoryItem i ON (i.id = s.inventoryItemId) 
                JOIN catalog.item c ON i.itemId = c.id 
                JOIN inventory.warehouse w ON w.id = s.warehouseId
                where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
                and w.billingWarehouseId = #{physicalWarehouseId} 
                group by i.itemId ) as x 
        LEFT JOIN 
        (select i.itemId, sum(quantity) as outx from scanNew s 
                JOIN inventoryItem i on i.id = s.inventoryItemId 
                JOIN catalog.item c ON i.itemId = c.id 
                JOIN inventory.warehouse w ON w.id = s.warehouseId
                where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
                and w.billingWarehouseId = #{physicalWarehouseId}
                group by i.itemId) as y 
        ON x.itemId = y.itemId 
        JOIN catalog.item ci ON ci.id = x.itemId 
        where ci.type = 'SERIALIZED' having quantity>0  
  </select>

<select id = "getHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
        SELECT x.itemId, ci.brand, ci.model_name as modelName, 
        ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
        from 
        (select i.itemId, count(*) as inx from scanNew s 
                JOIN inventoryItem i ON (i.id = s.inventoryItemId) 
                JOIN catalog.item c ON i.itemId = c.id 
                where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
                and s.scannedAt &lt; #{date} 
                group by i.itemId ) as x 
        LEFT JOIN 
        (select i.itemId, count(*) as outx from scanNew s 
                JOIN inventoryItem i on i.id = s.inventoryItemId 
                JOIN catalog.item c ON i.itemId = c.id 
                where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
                and s.scannedAt &lt; #{date} 
                group by i.itemId) as y 
        ON x.itemId = y.itemId 
        JOIN catalog.item ci ON ci.id = x.itemId 
        where ci.type = 'SERIALIZED' having quantity>0  
  </select>

<select id = "getOurHistoricSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
        SELECT x.itemId, ci.brand, ci.model_name as modelName, 
        ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
        from 
        (select i.itemId, count(*) as inx from scanNew s 
                JOIN inventoryItem i ON (i.id = s.inventoryItemId) 
                JOIN catalog.item c ON i.itemId = c.id 
                where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'SERIALIZED'
                and s.scannedAt &lt; #{date} 
                and s.warehouseId != 16
                group by i.itemId ) as x 
        LEFT JOIN 
        (select i.itemId, count(*) as outx from scanNew s 
                JOIN inventoryItem i on i.id = s.inventoryItemId 
                JOIN catalog.item c ON i.itemId = c.id 
                where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'SERIALIZED'
                and s.scannedAt &lt; #{date}
                and s.warehouseId != 16 
                group by i.itemId) as y 
        ON x.itemId = y.itemId 
        JOIN catalog.item ci ON ci.id = x.itemId 
        where ci.type = 'SERIALIZED' having quantity>0  
</select>

  
  <select id = "getCurrentNonSerializedInventoryByScans" resultType = "inventoryAvailability">
        SELECT x.itemId, ci.brand, ci.model_name as modelName, 
        ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
        from 
        (select i.itemId, sum(quantity) as inx from scanNew s 
                JOIN inventoryItem i ON (i.id = s.inventoryItemId) 
                JOIN catalog.item c ON i.itemId = c.id 
                JOIN inventory.warehouse w ON w.id = s.warehouseId
                where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED'
                and w.billingWarehouseId = #{physicalWarehouseId}
                group by i.itemId ) as x 
        LEFT JOIN 
        (select i.itemId, sum(quantity) as outx from scanNew s 
                JOIN inventoryItem i on i.id = s.inventoryItemId 
                JOIN catalog.item c ON i.itemId = c.id 
                JOIN inventory.warehouse w ON w.id = s.warehouseId
                where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
                and w.billingWarehouseId = #{physicalWarehouseId}
                group by i.itemId) as y 
        ON x.itemId = y.itemId 
        JOIN catalog.item ci ON ci.id = x.itemId 
        where ci.type = 'NON_SERIALIZED' having quantity>0  
  </select>

<select id = "getHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
        SELECT x.itemId, ci.brand, ci.model_name as modelName, 
        ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
        from 
        (SELECT i.itemId, sum(quantity) as inx from scanNew s 
                JOIN inventoryItem i ON (i.id = s.inventoryItemId) 
                JOIN catalog.item c ON i.itemId = c.id 
                where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED' 
                and s.scannedAt &lt; #{date}
                group by i.itemId ) as x 
        LEFT JOIN 
        (SELECT i.itemId, sum(quantity) as outx from scanNew s 
                JOIN inventoryItem i on i.id = s.inventoryItemId 
                JOIN catalog.item c ON i.itemId = c.id 
                where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
                and s.scannedAt &lt; #{date} 
                group by i.itemId) as y 
        ON x.itemId = y.itemId 
        JOIN catalog.item ci ON ci.id = x.itemId 
        where ci.type = 'NON_SERIALIZED' having quantity>0  
  </select>

<select id = "getOurHistoricNonSerializedInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
        SELECT x.itemId, ci.brand, ci.model_name as modelName, 
        ci.model_number as modelNumber, ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity
        from 
        (SELECT i.itemId, sum(quantity) as inx from scanNew s 
                JOIN inventoryItem i ON (i.id = s.inventoryItemId) 
                JOIN catalog.item c ON i.itemId = c.id 
                where s.type in ('PURCHASE','SALE_RET', 'MARKED_GOOD','WAREHOUSE_TRANSFER_IN') and c.type = 'NON_SERIALIZED' 
                and s.scannedAt &lt; #{date}
                and s.warehouseId != 16
                group by i.itemId ) as x 
        LEFT JOIN 
        (SELECT i.itemId, sum(quantity) as outx from scanNew s 
                JOIN inventoryItem i on i.id = s.inventoryItemId 
                JOIN catalog.item c ON i.itemId = c.id 
                where s.type in ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD','WAREHOUSE_TRANSFER_OUT') and c.type = 'NON_SERIALIZED'
                and s.scannedAt &lt; #{date}
                and s.warehouseId != 16 
                group by i.itemId) as y 
        ON x.itemId = y.itemId 
        JOIN catalog.item ci ON ci.id = x.itemId 
        where ci.type = 'NON_SERIALIZED' having quantity>0  
</select>

 <select id = "getMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
        SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity 
        FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId 
        LEFT JOIN catalog.item c ON i.itemId = c.id 
        where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
        and i.currentWarehouseId not in (1,2,6,9) 
        and s.warehouseId != 16
        group by itemId, s.type
 </select>
 
 <select id = "getMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
        SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity 
        FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId 
        LEFT JOIN catalog.item c ON i.itemId = c.id 
        where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
        and i.currentWarehouseId not in (1,2,6,9)
        and s.warehouseId != 16  
        group by itemId, s.type
 </select>

 <select id = "getCompleteMovementSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
        SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, count(*) as quantity 
        FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId 
        LEFT JOIN catalog.item c ON i.itemId = c.id 
        where s.scannedAt between #{startDate} and #{endDate} and c.type = 'SERIALIZED'
        and s.warehouseId != 16
        group by itemId, s.type
 </select>
 
 <select id = "getCompleteMovementNonSerializedInventoryByScans" resultType = "inventoryMovement" parameterType = "map">
        SELECT itemId, brand, model_name as modelName, model_number as modelNumber, color, s.type, sum(quantity) as quantity 
        FROM inventoryItem i JOIN scanNew s ON i.id = s.inventoryItemId 
        LEFT JOIN catalog.item c ON i.itemId = c.id 
        where s.scannedAt between #{startDate} and #{endDate} and c.type = 'NON_SERIALIZED'
        and s.warehouseId != 16
        group by itemId, s.type
 </select>
  
<select id = "getTransferrableWarehousesFromWarehouse" resultType = "java.lang.Long" parameterType = "java.lang.Long">
        SELECT toWarehouseId  
        FROM possibleWarehouseMovement  
        WHERE fromWarehouseId = #{warehouseId}
 </select>

<select id = "getHistoricBadInventoryByScans" resultType = "inventoryAvailability" parameterType = "date">
        SELECT x.itemId, ci.brand, ci.model_name as modelName, ci.model_number as modelNumber, 
        ci.color as color, (x.inx-if(y.outx is null,0,y.outx)) as quantity 
        FROM 
                (SELECT i.itemId, count(*) as inx FROM scanNew s JOIN inventoryItem i ON 
                (i.id = s.inventoryItemId) JOIN catalog.item c ON i.itemId = c.id 
                where s.type in ('DOA_IN', 'SALE_RET_UNUSABLE') and 
                s.scannedAt &lt; #{date} and s.scannedAt &gt; '2013-01-01' group by i.itemId ) as x 
                LEFT JOIN 
                (SELECT i.itemId, count(*) as outx FROM scanNew s JOIN inventoryItem i ON
                i.id = s.inventoryItemId JOIN catalog.item c ON i.itemId = c.id WHERE 
                s.type in ('DOA_OUT') and s.scannedAt &lt; #{date} and s.scannedAt &gt; '2013-01-01' 
                group by i.itemId) as y 
                ON x.itemId = y.itemId 
                JOIN catalog.item ci ON ci.id = x.itemId 
        having quantity>0;
</select>

<select id = "getScansForTransferLot" resultType = "scan" parameterType = "java.lang.Long">
        SELECT * 
        FROM scanNew
        WHERE transferLotId = #{id}
        AND type = 'WAREHOUSE_TRANSFER_OUT'
</select>

<select id= "getScansForTransferLotAndInventoryItem" resultType = "scan" parameterType="map" >
        SELECT * 
        FROM scanNew
        WHERE transferLotId = #{transferLotId}
        AND inventoryItemId = #{inventoryItemId}
        AND type = 'WAREHOUSE_TRANSFER_OUT'
        LIMIT 1
</select>

<select id="getAmazonTransferredSkuDetails" resultType= "amazonTransferredSkuDetail" parameterType="map">
        SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, sum(Y.quantity) as quantity, Y.unitPrice , Y.brand, Y.modelName, Y.modelNumber, Y.category, Y.color
        FROM (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.brand, X.modelName, X.modelNumber, X.category, X.color , X.inventoryItemId, X.scanTime 
        FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate, s.quantity as quantity, l.unitPrice as unitPrice, l.brand as brand, l.modelName as modelName, l.modelNumber as modelNumber, l.productGroup as category, l.color as color , i.id as inventoryItemId, s.scannedAt as scanTime 
        FROM scanNew s 
        JOIN inventoryItem i on s.inventoryItemId= i.id 
        JOIN transferLot t on (s.transferLotId= t.id) 
        JOIN purchase p on p.id= i.purchaseId 
        JOIN lineitem l on (l.purchaseOrder_id= p.purchaseOrder_id and l.itemId= i.itemId) 
        WHERE i.itemId in 
        <foreach item="itemId" index="index" collection="itemIds"
        open="(" separator="," close=")">
         #{itemId} 
        </foreach> 
        AND t.destinationWarehouseId in (16, 1771) 
        AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER') 
        AND i.transferStatus is not null AND type='WAREHOUSE_TRANSFER_OUT' 
        group by i.itemId, s.transferLotId, i.purchaseId, i.id 
        order by s.scannedAt desc) as X group by inventoryItemId) as Y group by Y.itemId, Y.purchaseId
        order by Y.purchaseId desc
</select>

<select id="getScansforPurchase" resultType="scan" parameterType="map" >
        SELECT s.id, s.inventoryItemId, s.warehouseId, s.type, s.scannedAt, s.quantity, s.orderId, s.remarks, s.transferLotId 
        FROM scanNew s 
        JOIN inventoryItem i on s.inventoryItemId = i.id 
        WHERE i.purchaseId= #{purchaseId} and s.type=#{scanType}
</select>
 
</mapper>