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)

    VALUES

      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity}, #{orderId})

  </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="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 

    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)

    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="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 (s.id = i.supplierId)

    LEFT JOIN purchase p

        ON (p.invoiceNumber = i.invoiceNumber)

    LEFT JOIN inventoryItem ii ON (ii.purchaseId = p.id)

    WHERE i.date = #{date}

    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 < 1) * currentQuantity) AS freshCount, 

        SUM((age >= 1 and age < 2) * currentQuantity) AS oneToTwoCount, 

        SUM((age >= 2 and age < 3) * currentQuantity) AS twoToThreeCount, 

        SUM((age >= 3 and age < 4) * currentQuantity) AS threeToFourCount, 

        SUM((age >= 4) * currentQuantity) as fourPlusCount,

        SUM((age >= 1) * currentQuantity) as onePlusCount,

        SUM((age >= 1) * unitPrice) as onePlusCost,

        SUM(currentQuantity) as zeroPlusCount,

        SUM(l.unitPrice) as zeroPlusCost,

        l.productGroup as category

    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId,

                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 

    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="scanForPurchaseReturn" parameterType="scan" useGeneratedKeys="true" keyProperty="id">

    INSERT INTO scanNew

      (inventoryItemId, warehouseId, type, scannedAt, quantity)

    VALUES

      (#{inventoryItemId}, #{warehouseId}, #{type}, NOW(), #{quantity})

  </insert>

</mapper>