Subversion Repositories SmartDukaan

<?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, p.purchaseComments 
    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 ii.lastScanType != 'MARKED_BAD' 
           AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT')
           AND ii.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)) 
           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, sum(s.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 = 'SERIALIZED'
                and s.scannedAt &lt; #{date} 
                group by i.itemId ) as x 
        LEFT JOIN 
        (select i.itemId, sum(s.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 = '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, sum(s.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 = 'SERIALIZED'
                and s.scannedAt &lt; #{date} 
                and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40 
            and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) 
                group by i.itemId ) as x 
        LEFT JOIN 
        (select i.itemId, sum(s.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 = 'SERIALIZED'
                and s.scannedAt &lt; #{date}
                and s.warehouseId not in (select id from inventory.warehouse where vendor_id= 40 
            and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) 
                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 not in (select id from inventory.warehouse where vendor_id= 40 
            and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) 
                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 not in (select id from inventory.warehouse where vendor_id= 40 
            and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) 
                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 not in (select id from inventory.warehouse where vendor_id= 40 
        and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) 
        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 not in (select id from inventory.warehouse where vendor_id= 40 
        and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) 
        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, 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 = 'SERIALIZED' 
        AND s.warehouseId NOT IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
        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 NOT IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))  
        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 badX.id as itemId, badX.brand, badX.model_name as modelName, badX.model_number as modelNumber, badX.color, sum(badX.Bad_quantity) as quantity 
        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, 
        x.physicalWarehouseId,x.purchaseId 
        FROM (SELECT it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number, it.color, 
        sum(s.quantity) as quantity, i.physicalWarehouseId,i.purchaseId FROM scanNew s 
        JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on i.purchaseId= p.id 
        JOIN catalog.item it on i.itemId= it.id JOIN catalog.category ct on it.category = ct.id 
        WHERE s.type in ('MARKED_BAD','DOA_IN','SALE_RET_UNUSABLE','DOA_REJECTED') 
        AND s.scannedAt &gt; '2013-01-01' AND s.scannedAt &lt; #{date} 
        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)) 
        group by i.itemId,i.purchaseId) as x 
        LEFT OUTER JOIN (SELECT it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number,
        it.color, sum(s.quantity) as quantity, i.physicalWarehouseId, i.purchaseId 
        FROM scanNew s JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on i.purchaseId= p.id 
        JOIN catalog.item it on i.itemId= it.id JOIN catalog.category ct on it.category = ct.id 
        WHERE s.type in ('DOA_OUT','BAD_SALE','MARKED_GOOD','BAD_PURCHASE_RETURN','LOST_IN_WAREHOUSE_BAD') 
        AND s.scannedAt &gt; '2013-01-01' AND s.scannedAt &lt; #{date} 
        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)) 
        group by i.itemId,i.purchaseId) as y 
        on x.id=y.id AND x.purchaseId=y.purchaseId 
        group by x.id,x.purchaseId having Bad_quantity>0) 
        as badX group by badX.id
</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 Z.itemId, Z.purchaseId, Z.purchaseDate, sum(Z.quantity) as quantity, Z.unitPrice, Z.nlc, 
        Z.brand, Z.modelName, Z.modelNumber, Z.category, Z.color, Z.taxType  
        FROM  (SELECT X.itemId, X.purchaseId, X.purchaseDate, X.quantity, X.unitPrice, X.nlc, X.brand, 
        X.modelName, X.modelNumber, X.category, X.color , X.inventoryItemId, X.scanTime, X.taxType
        FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate, 
        s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, 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, CASE po.taxType 
        WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType 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 purchaseorder po on p.purchaseOrder_id = po.id 
        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 (select id from inventory.warehouse where vendor_id= 40 
        and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3) 
        AND t.status in ('TRANSFER_COMPLETE','IN_TRANSIT','PARTIAL_TRANSFER') 
        AND i.transferStatus is not null AND s.type='WAREHOUSE_TRANSFER_OUT' 
        GROUP BY i.itemId, s.transferLotId, i.purchaseId, i.id order by s.scannedAt desc) 
        as X GROUP BY X.inventoryItemId 
        UNION SELECT Y.itemId, Y.purchaseId, Y.purchaseDate, Y.quantity, Y.unitPrice, Y.nlc, Y.brand, 
        Y.modelName, Y.modelNumber, Y.category, Y.color , Y.inventoryItemId, Y.scanTime, Y.taxType 
        FROM (SELECT i.itemId as itemId, i.purchaseId as purchaseId, p.receivedOn as purchaseDate, 
        s.quantity as quantity, l.unitPrice as unitPrice, l.nlc as nlc, l.brand as brand, l.modelName, 
        l.modelNumber as modelNumber, l.productGroup as category, l.color as color, 
        i.id as inventoryItemId, s.scannedAt as scanTime, CASE po.taxType 
        WHEN 0 THEN 'VAT' WHEN 1 THEN 'CST' WHEN 2 THEN 'CFORM' END as taxType FROM scanNew s 
        JOIN inventoryItem i on s.inventoryItemId = i.id JOIN purchase p on p.id= i.purchaseId 
        JOIN purchaseorder po on p.purchaseOrder_id = po.id 
        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 po.warehouseId in (select id from inventory.warehouse where vendor_id= 40 
        and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source = 3) 
        GROUP BY i.itemId, i.purchaseId, i.id ORDER BY s.scannedAt desc) as Y 
        GROUP BY Y.inventoryItemId) as Z GROUP BY Z.itemId, Z.purchaseId order by Z.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>

<select id="getInventoryAgeConsideredItems" resultType="invAgeConsiderItems" parameterType = "java.lang.Long">
        SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId, DATEDIFF(now(), MIN(s.scannedAt)) / 7 as age, 
        l.productGroup as category, l.unitPrice as cost 
        FROM inventoryItem ii JOIN scanNew s ON (s.inventoryItemId = ii.id) JOIN purchase p ON (p.id = ii.purchaseId) 
        JOIN lineitem l ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = ii.itemId) 
        WHERE ((ii.lastScanType IN ('PURCHASE', 'SALE_RET', 'MARKED_GOOD')) OR (ii.currentQuantity>0) OR (ii.lastScanType IN ('SALE') and ii.currentQuantity = 1)) 
        AND ii.lastScanType != 'MARKED_BAD' AND (transferStatus is NULL or transferStatus != 'IN_TRANSIT') AND ii.itemId = #{itemId} 
        AND ii.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)) 
        GROUP BY ii.id, ii.itemId, ii.currentQuantity order by ii.id desc;
</select>

<select id="getCompleteMovementForThirdPartyWarehouse" 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 s.warehouseId IN (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8))
        group by itemId, s.type
</select>

</mapper>