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, sgstRate, cgstRate, igstRate, 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, l.invoicePrice as invoicePrice, 
        unitPrice, sgstRate, cgstRate, igstRate, 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 purchaseorder po
    JOIN purchase p 
        ON (po.id = p.purchaseOrder_id)
    JOIN (select * from invoice group by invoiceNumber, supplierId) inv
        ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId)
    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)
    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 z.Item_Id as itemId, z.Brand as brand, z.ModelName as modelName, z.ModelNumber as modelNumber, z.Color as color, 
        sum(z.Quantity) quantity FROM (SELECT x.itemId Item_Id, ci.brand Brand, ci.model_name as ModelName, ci.model_number as ModelNumber, 
        ci.color Color, (x.inx-if(y.outx is null,0,y.outx)) Quantity, x.billingWarehouseId, wh.displayName Warehouse 
        FROM (SELECT i.itemId, sum(quantity) as inx, w.billingWarehouseId 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 not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' 
        and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt &lt; #{date} GROUP BY i.itemId, w.billingWarehouseId ) as x 
        LEFT JOIN (SELECT i.itemId, sum(quantity) as outx, w.billingWarehouseId 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 not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' 
        and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt &lt; #{date} 
        GROUP BY i.itemId, w.billingWarehouseId ) as y ON (x.itemId = y.itemId AND x.billingWarehouseId = y.billingWarehouseId) 
        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
</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 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 FROM (SELECT x.itemId Item_Id, ci.brand Brand, ci.model_name as ModelName, ci.model_number as ModelNumber, 
        ci.color Color, (x.inx-if(y.outx is null,0,y.outx)) Quantity, x.billingWarehouseId, wh.displayName Warehouse 
        FROM (SELECT i.itemId, sum(quantity) as inx, w.billingWarehouseId 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 not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' 
        and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt &lt; #{date} GROUP BY i.itemId, w.billingWarehouseId ) as x 
        LEFT JOIN (SELECT i.itemId, sum(quantity) as outx, w.billingWarehouseId 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 not in (select id from inventory.warehouse where vendor_id= 40 and inventoryType = 'GOOD' 
        and warehouseType ='OURS_THIRDPARTY' and source in (3,7,8)) AND s.scannedAt &lt; #{date} GROUP BY i.itemId, w.billingWarehouseId ) as y 
        ON (x.itemId = y.itemId AND x.billingWarehouseId = y.billingWarehouseId) 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
</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 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 
        FROM (SELECT x.itemId Item_Id, ci.brand Brand, ci.model_name as ModelName, ci.model_number as ModelNumber, ci.color Color, 
        (x.inx-if(y.outx is null,0,y.outx)) Quantity, x.billingWarehouseId, wh.displayName Warehouse 
        FROM (SELECT i.itemId, sum(quantity) as inx, w.billingWarehouseId 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 ('MARKED_BAD','DOA_IN','SALE_RET_UNUSABLE','DOA_REJECTED')  
        AND w.billingWarehouseId not in (9,12) AND s.scannedAt &gt; '2013-01-01'
        AND s.scannedAt &lt; #{date} GROUP BY i.itemId, w.billingWarehouseId ) as x 
        LEFT JOIN (SELECT i.itemId, sum(quantity) as outx, w.billingWarehouseId 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 ('DOA_OUT','BAD_SALE','MARKED_GOOD','BAD_PURCHASE_RETURN','LOST_IN_WAREHOUSE_BAD','EMPTY_PRODUCT_RECEIVED','WRONG_PRODUCT_RECEIVED') 
        AND w.billingWarehouseId not in (9,12) AND s.scannedAt &gt; '2013-01-01' 
        AND s.scannedAt &lt; #{date} GROUP BY i.itemId, w.billingWarehouseId ) as y ON (x.itemId = y.itemId 
        AND x.billingWarehouseId = y.billingWarehouseId) 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
</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>

<select id="fetchMismatchScansPerTransferInvoiceNumber" parameterType="java.util.Date" resultType="transferInvoiceScan" >
        SELECT r.shipmentReference, t.id transferLotId, r.source, r.quantity, 
        CASE r.inventoryType WHEN 1 THEN 'GOOD' WHEN 2 THEN 'BAD' END inventoryType, 
        1000 * UNIX_TIMESTAMP(DATE(r.createdOn)) date, sum(s.quantity) scannedQuantity 
        FROM removalStockShipmentReference r JOIN transferLot t on r.id = t.shipmentReference 
        JOIN scanNew s on t.id = s.transferLotId WHERE DATE(r.createdOn) = #{date} 
        AND s.scannedAt BETWEEN #{date} and ADDDATE(#{date},1) AND s.type= 'WAREHOUSE_TRANSFER_IN' 
        GROUP BY r.id, r.source, r.inventoryType, r.quantity 
        HAVING sum(s.quantity)!=r.quantity
</select>

</mapper>