<?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.ncr.mapper.NcrMapper">

  <resultMap id="ncrMap" type="com.zioinfo.mes.ncr.MesNcr">
    <id property="id" column="id"/>
    <result property="ncrNo" column="ncr_no"/>
    <result property="itemCode" column="item_code"/>
    <result property="lotNo" column="lot_no"/>
    <result property="refType" column="ref_type"/>
    <result property="refNo" column="ref_no"/>
    <result property="defectCode" column="defect_code"/>
    <result property="defectQty" column="defect_qty"/>
    <result property="description" column="description"/>
    <result property="disposition" column="disposition"/>
    <result property="severity" column="severity"/>
    <result property="status" column="status"/>
    <result property="capaId" column="capa_id"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
    <result property="updatedAt" column="updated_at"/>
  </resultMap>

  <select id="findAll" resultMap="ncrMap">
    SELECT * FROM mes_ncr
    <where>
      <if test="status != null and status != ''">AND status = #{status}</if>
      <if test="severity != null and severity != ''">AND severity = #{severity}</if>
      <if test="itemCode != null and itemCode != ''">AND item_code = #{itemCode}</if>
    </where>
    ORDER BY id DESC
  </select>

  <select id="findById" resultMap="ncrMap">SELECT * FROM mes_ncr WHERE id = #{id}</select>

  <insert id="insert" parameterType="com.zioinfo.mes.ncr.MesNcr" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_ncr (ncr_no, item_code, lot_no, ref_type, ref_no, defect_code, defect_qty,
      description, disposition, severity, status, created_by)
    VALUES (#{ncrNo}, #{itemCode}, #{lotNo}, #{refType}, #{refNo}, #{defectCode}, #{defectQty},
      #{description}, #{disposition}, #{severity}, #{status}, #{createdBy})
  </insert>

  <update id="updateDisposition">
    UPDATE mes_ncr SET disposition = #{disposition}, status = #{status}, updated_at = NOW() WHERE id = #{id}
  </update>

  <update id="updateStatus">
    UPDATE mes_ncr SET status = #{status}, updated_at = NOW() WHERE id = #{id}
  </update>

  <update id="linkCapa">
    UPDATE mes_ncr SET capa_id = #{capaId}, updated_at = NOW() WHERE id = #{id}
  </update>

  <select id="countByStatus" resultType="map">
    SELECT status, COUNT(*) AS cnt FROM mes_ncr GROUP BY status
  </select>

</mapper>
