<?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.plan.PlanMapper">

  <resultMap id="planMap" type="com.zioinfo.mes.plan.MesPlan">
    <id property="id" column="id"/>
    <result property="planNo" column="plan_no"/>
    <result property="itemCode" column="item_code"/>
    <result property="itemName" column="item_name"/>
    <result property="planQty" column="plan_qty"/>
    <result property="startDate" column="start_date"/>
    <result property="endDate" column="end_date"/>
    <result property="status" column="status"/>
    <result property="remark" column="remark"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="planMap">
    SELECT p.*, it.item_name FROM mes_plan p
    LEFT JOIN mes_item it ON it.item_code = p.item_code
    <where>
      <if test="status != null and status != ''">AND p.status = #{status}</if>
      <if test="itemCode != null and itemCode != ''">AND p.item_code = #{itemCode}</if>
    </where>
    ORDER BY p.start_date ASC, p.id DESC
  </select>

  <select id="findById" resultMap="planMap">
    SELECT p.*, it.item_name FROM mes_plan p
    LEFT JOIN mes_item it ON it.item_code = p.item_code WHERE p.id = #{id}
  </select>

  <insert id="insert" parameterType="com.zioinfo.mes.plan.MesPlan" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_plan (plan_no, item_code, plan_qty, start_date, end_date, status, remark, created_by)
    VALUES (#{planNo}, #{itemCode}, #{planQty}, #{startDate}, #{endDate}, #{status}, #{remark}, #{createdBy})
  </insert>

  <update id="update" parameterType="com.zioinfo.mes.plan.MesPlan">
    UPDATE mes_plan SET item_code = #{itemCode}, plan_qty = #{planQty}, start_date = #{startDate},
      end_date = #{endDate}, remark = #{remark} WHERE id = #{id}
  </update>

  <update id="updateStatus">UPDATE mes_plan SET status = #{status} WHERE id = #{id}</update>
  <delete id="delete">DELETE FROM mes_plan WHERE id = #{id}</delete>

</mapper>
