Subversion Repositories SmartDukaan

Rev

Rev 10347 | Rev 10402 | 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
    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;= 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 !=16
           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, 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 i.physicalWarehouseId = #{physicalWarehouseId} 
                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 i.physicalWarehouseId = #{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','MARKED_BAD') 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 ('DOA_OUT','MARKED_GOOD','BAD_SALE') 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 
        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>
 
</mapper>