<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zioinfo.mes.tracking.TrackingMapper">

  <select id="findProductionByLot" resultType="map">
    SELECT wo_no, item_code, good_qty, defect_qty, reported_at
    FROM mes_production WHERE lot_no = #{lotNo} LIMIT 1
  </select>

  <select id="findConsumedByWo" resultType="map">
    SELECT item_code, lot_no, qty, from_warehouse, created_at
    FROM mes_inventory_txn
    WHERE txn_type = 'CONSUME' AND ref_type = 'WORKORDER' AND ref_no = #{woNo}
    ORDER BY id ASC
  </select>

  <select id="findWoConsumingLot" resultType="map">
    SELECT ref_no, item_code, qty, created_at
    FROM mes_inventory_txn
    WHERE txn_type = 'CONSUME' AND lot_no = #{lotNo} AND ref_type = 'WORKORDER'
    ORDER BY id ASC
  </select>

  <select id="findProducedLotsByWo" resultType="map">
    SELECT lot_no, item_code, good_qty, reported_at
    FROM mes_production WHERE wo_no = #{woNo} AND lot_no IS NOT NULL
    ORDER BY id ASC
  </select>

</mapper>
