<?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="com.aucma.production.mapper.MrpProdWeeklyPlanMapper">

    <resultMap type="MrpProdWeeklyPlan" id="MrpProdWeeklyPlanResult">
            <result property="id"    column="id"    />
            <result property="yearWeek"    column="year_week"    />
            <result property="weekStartDate"    column="week_start_date"    />
            <result property="seqNo"    column="seq_no"    />
            <result property="itemCode"    column="item_code"    />
            <result property="itemDesc"    column="item_desc"    />
            <result property="plantCode"    column="plant_code"    />
            <result property="orderNo"    column="order_no"    />
            <result property="orderLineNo"    column="order_line_no"    />
            <result property="planD1"    column="plan_d1"    />
            <result property="planD2"    column="plan_d2"    />
            <result property="planD3"    column="plan_d3"    />
            <result property="planD4"    column="plan_d4"    />
            <result property="planD5"    column="plan_d5"    />
            <result property="planD6"    column="plan_d6"    />
            <result property="planD7"    column="plan_d7"    />
            <result property="planWeekTotal"    column="plan_week_total"    />
            <result property="createdAt"    column="created_at"    />
            <result property="updatedAt"    column="updated_at"    />
    </resultMap>

    <sql id="selectMrpProdWeeklyPlanVo">
        select id, year_week, week_start_date, seq_no, item_code, item_desc, plant_code, order_no, order_line_no, plan_d1, plan_d2, plan_d3, plan_d4, plan_d5, plan_d6, plan_d7, plan_week_total, created_at, updated_at from mrp_prod_weekly_plan
    </sql>

    <select id="selectMrpProdWeeklyPlanList" parameterType="MrpProdWeeklyPlan" resultMap="MrpProdWeeklyPlanResult">
        <include refid="selectMrpProdWeeklyPlanVo"/>
        <where>
                        <if test="yearWeek != null  and yearWeek != ''"> and year_week = #{yearWeek}</if>
                        <if test="weekStartDate != null "> and week_start_date = #{weekStartDate}</if>
                        <if test="params != null and params.beginWeekStartDate != null and params.beginWeekStartDate != ''"> and week_start_date &gt;= to_date(#{params.beginWeekStartDate}, 'yyyy-mm-dd')</if>
                        <if test="params != null and params.endWeekStartDate != null and params.endWeekStartDate != ''"> and week_start_date &lt;= to_date(#{params.endWeekStartDate}, 'yyyy-mm-dd')</if>
                        <if test="seqNo != null "> and seq_no = #{seqNo}</if>
                        <if test="itemCode != null  and itemCode != ''"> and item_code = #{itemCode}</if>
                        <if test="itemDesc != null  and itemDesc != ''"> and item_desc = #{itemDesc}</if>
                        <if test="plantCode != null  and plantCode != ''"> and plant_code = #{plantCode}</if>
                        <if test="orderNo != null  and orderNo != ''"> and order_no = #{orderNo}</if>
                        <if test="orderLineNo != null  and orderLineNo != ''"> and order_line_no = #{orderLineNo}</if>
                        <if test="planD1 != null "> and plan_d1 = #{planD1}</if>
                        <if test="planD2 != null "> and plan_d2 = #{planD2}</if>
                        <if test="planD3 != null "> and plan_d3 = #{planD3}</if>
                        <if test="planD4 != null "> and plan_d4 = #{planD4}</if>
                        <if test="planD5 != null "> and plan_d5 = #{planD5}</if>
                        <if test="planD6 != null "> and plan_d6 = #{planD6}</if>
                        <if test="planD7 != null "> and plan_d7 = #{planD7}</if>
                        <if test="planWeekTotal != null "> and plan_week_total = #{planWeekTotal}</if>
                        <if test="createdAt != null "> and created_at = #{createdAt}</if>
                        <if test="updatedAt != null "> and updated_at = #{updatedAt}</if>
        </where>
    </select>

    <select id="selectMrpProdWeeklyPlanById" parameterType="Long" resultMap="MrpProdWeeklyPlanResult">
            <include refid="selectMrpProdWeeklyPlanVo"/>
            where id = #{id}
    </select>

    <insert id="insertMrpProdWeeklyPlan" parameterType="MrpProdWeeklyPlan">
            <selectKey keyProperty="id" resultType="long" order="BEFORE">
                SELECT seq_mrp_prod_weekly_plan.NEXTVAL as id FROM DUAL
            </selectKey>
        insert into mrp_prod_weekly_plan
        <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="id != null">id,</if>
                <if test="yearWeek != null">year_week,</if>
                <if test="weekStartDate != null">week_start_date,</if>
                <if test="seqNo != null">seq_no,</if>
                <if test="itemCode != null">item_code,</if>
                <if test="itemDesc != null">item_desc,</if>
                <if test="plantCode != null">plant_code,</if>
                <if test="orderNo != null">order_no,</if>
                <if test="orderLineNo != null">order_line_no,</if>
                <if test="planD1 != null">plan_d1,</if>
                <if test="planD2 != null">plan_d2,</if>
                <if test="planD3 != null">plan_d3,</if>
                <if test="planD4 != null">plan_d4,</if>
                <if test="planD5 != null">plan_d5,</if>
                <if test="planD6 != null">plan_d6,</if>
                <if test="planD7 != null">plan_d7,</if>
                <if test="planWeekTotal != null">plan_week_total,</if>
                <if test="createdAt != null">created_at,</if>
                <if test="updatedAt != null">updated_at,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="id != null">#{id},</if>
                <if test="yearWeek != null">#{yearWeek},</if>
                <if test="weekStartDate != null">#{weekStartDate},</if>
                <if test="seqNo != null">#{seqNo},</if>
                <if test="itemCode != null">#{itemCode},</if>
                <if test="itemDesc != null">#{itemDesc},</if>
                <if test="plantCode != null">#{plantCode},</if>
                <if test="orderNo != null">#{orderNo},</if>
                <if test="orderLineNo != null">#{orderLineNo},</if>
                <if test="planD1 != null">#{planD1},</if>
                <if test="planD2 != null">#{planD2},</if>
                <if test="planD3 != null">#{planD3},</if>
                <if test="planD4 != null">#{planD4},</if>
                <if test="planD5 != null">#{planD5},</if>
                <if test="planD6 != null">#{planD6},</if>
                <if test="planD7 != null">#{planD7},</if>
                <if test="planWeekTotal != null">#{planWeekTotal},</if>
                <if test="createdAt != null">#{createdAt},</if>
                <if test="updatedAt != null">#{updatedAt},</if>
        </trim>
    </insert>

    <update id="updateMrpProdWeeklyPlan" parameterType="MrpProdWeeklyPlan">
        update mrp_prod_weekly_plan
        <trim prefix="SET" suffixOverrides=",">
                    <if test="yearWeek != null">year_week = #{yearWeek},</if>
                    <if test="weekStartDate != null">week_start_date = #{weekStartDate},</if>
                    <if test="seqNo != null">seq_no = #{seqNo},</if>
                    <if test="itemCode != null">item_code = #{itemCode},</if>
                    <if test="itemDesc != null">item_desc = #{itemDesc},</if>
                    <if test="plantCode != null">plant_code = #{plantCode},</if>
                    <if test="orderNo != null">order_no = #{orderNo},</if>
                    <if test="orderLineNo != null">order_line_no = #{orderLineNo},</if>
                    <if test="planD1 != null">plan_d1 = #{planD1},</if>
                    <if test="planD2 != null">plan_d2 = #{planD2},</if>
                    <if test="planD3 != null">plan_d3 = #{planD3},</if>
                    <if test="planD4 != null">plan_d4 = #{planD4},</if>
                    <if test="planD5 != null">plan_d5 = #{planD5},</if>
                    <if test="planD6 != null">plan_d6 = #{planD6},</if>
                    <if test="planD7 != null">plan_d7 = #{planD7},</if>
                    <!-- plan_week_total 是虚拟列，不能更新 -->
                    <if test="createdAt != null">created_at = #{createdAt},</if>
                    <if test="updatedAt != null">updated_at = #{updatedAt},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteMrpProdWeeklyPlanById" parameterType="Long">
        delete from mrp_prod_weekly_plan where id = #{id}
    </delete>

    <delete id="deleteMrpProdWeeklyPlanByIds" parameterType="String">
        delete from mrp_prod_weekly_plan where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id,jdbcType=NUMERIC}
        </foreach>
    </delete>

    <insert id="batchInsert" parameterType="java.util.List">
        INSERT ALL
        <foreach collection="list" item="item" separator=" ">
            INTO mrp_prod_weekly_plan (id, year_week, week_start_date, seq_no, item_code, item_desc, plant_code, order_no, order_line_no, plan_d1, plan_d2, plan_d3, plan_d4, plan_d5, plan_d6, plan_d7, created_at)
            VALUES (seq_mrp_prod_weekly_plan.NEXTVAL, #{item.yearWeek,jdbcType=VARCHAR}, #{item.weekStartDate,jdbcType=DATE}, #{item.seqNo,jdbcType=NUMERIC}, #{item.itemCode,jdbcType=VARCHAR}, #{item.itemDesc,jdbcType=VARCHAR}, #{item.plantCode,jdbcType=VARCHAR}, #{item.orderNo,jdbcType=VARCHAR}, #{item.orderLineNo,jdbcType=VARCHAR}, #{item.planD1,jdbcType=NUMERIC}, #{item.planD2,jdbcType=NUMERIC}, #{item.planD3,jdbcType=NUMERIC}, #{item.planD4,jdbcType=NUMERIC}, #{item.planD5,jdbcType=NUMERIC}, #{item.planD6,jdbcType=NUMERIC}, #{item.planD7,jdbcType=NUMERIC}, #{item.createdAt,jdbcType=TIMESTAMP})
        </foreach>
        SELECT 1 FROM DUAL
    </insert>
</mapper>