<?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.production.mapper.ProductionMapper">

  <resultMap id="prodMap" type="com.zioinfo.mes.production.MesProduction">
    <id property="id" column="id"/>
    <result property="workorderId" column="workorder_id"/>
    <result property="woNo" column="wo_no"/>
    <result property="itemCode" column="item_code"/>
    <result property="lotNo" column="lot_no"/>
    <result property="goodQty" column="good_qty"/>
    <result property="defectQty" column="defect_qty"/>
    <result property="defectCode" column="defect_code"/>
    <result property="processCode" column="process_code"/>
    <result property="equipmentCode" column="equipment_code"/>
    <result property="workerId" column="worker_id"/>
    <result property="warehouseCode" column="warehouse_code"/>
    <result property="reportedAt" column="reported_at"/>
    <result property="createdBy" column="created_by"/>
  </resultMap>

  <select id="findAll" resultMap="prodMap">
    SELECT * FROM mes_production
    <where>
      <if test="woNo != null and woNo != ''">AND wo_no = #{woNo}</if>
      <if test="itemCode != null and itemCode != ''">AND item_code = #{itemCode}</if>
    </where>
    ORDER BY id DESC LIMIT #{limit}
  </select>

  <select id="findById" resultMap="prodMap">SELECT * FROM mes_production WHERE id = #{id}</select>
  <select id="findByWorkorder" resultMap="prodMap">
    SELECT * FROM mes_production WHERE workorder_id = #{workorderId} ORDER BY id ASC
  </select>

  <insert id="insert" parameterType="com.zioinfo.mes.production.MesProduction" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_production (workorder_id, wo_no, item_code, lot_no, good_qty, defect_qty,
      defect_code, process_code, equipment_code, worker_id, warehouse_code, reported_at, created_by)
    VALUES (#{workorderId}, #{woNo}, #{itemCode}, #{lotNo}, #{goodQty}, #{defectQty},
      #{defectCode}, #{processCode}, #{equipmentCode}, #{workerId}, #{warehouseCode}, NOW(), #{createdBy})
  </insert>

  <select id="dailyOutput" resultType="map">
    SELECT reported_at::date AS day,
           COALESCE(SUM(good_qty),0) AS good_qty,
           COALESCE(SUM(defect_qty),0) AS defect_qty
    FROM mes_production
    WHERE reported_at &gt;= CURRENT_DATE - (#{days} || ' days')::interval
    GROUP BY reported_at::date
    ORDER BY day ASC
  </select>

  <select id="defectPareto" resultType="map">
    SELECT COALESCE(defect_code,'UNKNOWN') AS defect_code, COALESCE(SUM(defect_qty),0) AS defect_qty
    FROM mes_production
    WHERE defect_qty > 0 AND reported_at &gt;= CURRENT_DATE - (#{days} || ' days')::interval
    GROUP BY defect_code
    ORDER BY defect_qty DESC
  </select>

</mapper>
