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(invoiceNumber)) AS invoiceNumbers, i.itemId,

        brand, modelName, modelNumber, color, unitPrice, SUM(initialQuantity) AS quantity

    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)

    WHERE po.createdAt BETWEEN #{startDate} AND #{endDate} 

    GROUP BY p.purchaseOrder_id, i.itemId

    ORDER BY p.purchaseOrder_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>

</mapper>