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>

  <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;= 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>
</mapper>