<?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.ozs.plan.mapper.ProvisionalPlanMapper">
    <resultMap type="com.ozs.plan.doman.ProvisionalPlan" id="ProvisionalPlanResult">
        <id property="plan_practical_id" column="planPracticalId"/>
        <id property="plan_year_id" column="planYearId"/>
        <result property="plan_year_id" column="planYearId"/>
        <result property="purchase_dept_id" column="purchaseDeptId"/>
        <result property="project_name" column="projectName"/>
        <result property="project_type" column="projectType"/>
        <result property="demand_overview" column="demandOverview"/>
        <result property="evaluation" column="evaluation"/>
        <result property="is_excess" column="isExcess"/>
        <result property="purchase_services" column="purchaseServices"/>
        <result property="purchase_mode" column="purchaseMode"/>
        <result property="plan_demand_sub_time" column="planDemandSubTime"/>
        <result property="plan_purchase_finish_time" column="planPurchaseFinishTime"/>
        <result property="plan_deliver_time" column="planDeliverTime"/>
        <result property="project_attr" column="projectAttr"/>
        <result property="adjust_reason" column="adjustReason"/>
        <result property="remarks" column="remarks"/>
        <result property="project_status" column="projectStatus"/>
        <result property="refuse_reason" column="refuseReason"/>
        <result property="del_flay" column="delFlay"/>
        <result property="plan_type" column="planType"/>
        <result property="created" column="created"/>
        <result property="create_time" column="createTime"/>
        <result property="updated" column="updated"/>
        <result property="update_time" column="updateTime"/>
    </resultMap>

    <delete id="deleteProvisionalPlanById" parameterType="java.lang.Long">
        delete
        from plan_practical
        where plan_practical_id = #{planPracticalId}
    </delete>

    <update id="updateProvisionalPlanById" parameterType="com.ozs.plan.doman.vo.requestVo.ProvisionalPlanVo">
        update plan_practical
        <trim prefix="SET" suffixOverrides=",">
            <if test="purchaseDeptId != null and purchaseDeptId != ''">
                purchase_dept_id = #{purchaseDeptId},
            </if>
            <if test="projectName != null and projectName != ''">
                project_name = #{projectName},
            </if>
            <if test="projectType != null and projectType != ''">
                project_type = #{projectType},
            </if>
            <if test="demandOverview != null and demandOverview != ''">
                demand_overview = #{demandOverview},
            </if>
            <if test="evaluation != null and evaluation != ''">
                evaluation = #{evaluation},
            </if>
            <if test="isExcess != null and isExcess != '' ">
                is_excess = #{isExcess},
            </if>
            <if test="purchaseServices != null and purchaseServices != '' ">
                purchase_services = #{purchaseServices},
            </if>
            <if test="purchaseMode != null and purchaseMode != '' ">
                purchase_mode = #{purchaseMode},
            </if>
            <if test="planDemandSubTime != null">
                plan_demand_sub_time = #{planDemandSubTime},
            </if>
            <if test="planPurchaseFinishTime != null">
                plan_purchase_finish_time = #{planPurchaseFinishTime},
            </if>
            <if test="planDeliverTime != null">
                plan_deliver_time = #{planDeliverTime},
            </if>
            <if test="projectAttr != null and projectAttr != '' ">
                project_attr = #{projectAttr},
            </if>
            <if test="adjustReason != null and adjustReason != '' ">
                adjust_reason = #{adjustReason},
            </if>
            <if test="remarks != null and remarks != '' ">
                remarks = #{remarks},
            </if>
            <if test="projectStatus != null and projectStatus != '' ">
                project_status = #{projectStatus},
            </if>
            <if test="refuseReason != null and refuseReason != '' ">
                refuse_reason = #{refuseReason},
            </if>
            <if test="delFlay != null and delFlay != '' ">
                del_flay = #{delFlay},
            </if>
            <if test="planType != null and planType != '' ">
                plan_type = #{planType},
            </if>
            <if test="updated != null and updated != '' ">
                updated = #{updated},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
        </trim>
        where plan_practical_id = #{planPracticalId}
    </update>

    <select id="selectProjectName" resultType="com.ozs.plan.doman.ProvisionalPlan" parameterType="java.lang.String">
        select *
        from plan_practical
        where project_name = #{projectName}
          and del_flay = 0
          and plan_type = "1"
    </select>

    <update id="commitProvisionalPlan" parameterType="java.lang.Long">
        update plan_practical
        set project_status = 1
        where plan_type = 1
          and plan_practical_id = #{planPracticalId}
    </update>

    <select id="seletById" resultType="com.ozs.plan.doman.ProvisionalPlan" parameterType="java.lang.Long">
        select *
        from plan_practical
        where plan_type = 1
          and plan_practical_id = #{planPracticalId}
    </select>

    <select id="selectProvisionalPlan" resultType="com.ozs.plan.doman.ProvisionalPlan"
            parameterType="com.ozs.plan.doman.vo.requestVo.ProvisionalPlanVo">
        select * from plan_practical
        <where>
            <if test="projectName != null and projectName != ''">
                and project_name like concat('%', #{projectName}, '%')
            </if>
            <if test="purchaseServices != null and purchaseServices != ''">
                and purchase_services = #{purchaseServices}
            </if>
            <if test="isExcess != null and isExcess != ''">
                and is_excess = #{isExcess}
            </if>
            <if test="projectStatus != null and projectStatus != ''">
                and project_status = #{projectStatus}
            </if>
            <if test="beginTime != null  "><!-- 开始时间检索 -->
                and date_format(plan_demand_sub_time,'%y%m%d') &gt;= date_format(#{beginTime},'%y%m%d')
            </if>
            <if test="endTime != null  "><!-- 结束时间检索 -->
                and date_format(plan_demand_sub_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')
            </if>
            and plan_type = 1
        </where>
        order by create_time desc
    </select>

    <select id="selectProvisionalPlanAudit" resultType="com.ozs.plan.doman.ProvisionalPlan"
            parameterType="com.ozs.plan.doman.vo.requestVo.ProvisionalPlanVo">
        select * from plan_practical
        <where>
            <if test="projectName != null and projectName != ''">
                and project_name like concat('%', #{projectName}, '%')
            </if>
            <if test="purchaseServices != null and purchaseServices != ''">
                and purchase_services = #{purchaseServices}
            </if>
            <if test="isExcess != null and isExcess != ''">
                and is_excess = #{isExcess}
            </if>
            <if test="projectStatus == null or projectStatus ==''">
                and project_status in (1,3)
            </if>
            <if test="projectStatus != null and projectStatus != ''">
                and project_status = #{projectStatus}
            </if>
            <if test="beginTime != null  "><!-- 开始时间检索 -->
                and date_format(plan_demand_sub_time,'%y%m%d') &gt;= date_format(#{beginTime},'%y%m%d')
            </if>
            <if test="endTime != null  "><!-- 结束时间检索 -->
                and date_format(plan_demand_sub_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')
            </if>
            and plan_type = 1
        </where>
        order by create_time desc
    </select>

    <!--	计划审核-->
    <update id="review" parameterType="com.ozs.plan.doman.vo.requestVo.ProvisionalPlanVo">
        update plan_practical
        <trim prefix="SET" suffixOverrides=",">
            <if test="refuseReason != null and refuseReason != ''">
                refuse_reason = #{refuseReason},
            </if>
            <if test="projectStatus != null and projectStatus != ''">
                project_status = #{projectStatus},
            </if>
        </trim>
        where plan_practical_id = #{planPracticalId}
    </update>

    <select id="selectProvisionalPlanByYearId" resultType="com.ozs.plan.doman.ProvisionalPlan"
            parameterType="java.lang.Integer">
        select *
        from plan_practical
        where plan_year_id = #{planYearId}
    </select>

    <select id="query" resultType="com.ozs.plan.doman.ProvisionalPlan"
            parameterType="com.ozs.plan.doman.vo.requestVo.ProvisionalPlanVo">
        select p.* from
        plan_practical p left join pm_audit_dept_ref r
        on p.plan_practical_id = r.ref_id and r.ref_type = 1 and p.plan_type = 1
        <where>
            ( #{created} = 1 or r.dept_id = #{purchaseDeptId} )
            <if test="projectName != null and projectName != ''">
                AND p.project_name like concat('%', #{projectName}, '%')
            </if>
            <if test="purchaseServices != null and purchaseServices != ''">
                AND p.purchase_services = #{purchaseServices}
            </if>
            <if test="isExcess != null and isExcess != ''">
                AND p.is_excess = #{isExcess}
            </if>
            <if test="projectStatus == null or projectStatus ==''">
                AND p.project_status in (1,3)
            </if>
            <if test="projectStatus != null and projectStatus != ''">
                AND p.project_status = #{projectStatus}
            </if>
            <if test="params!= null and params.beginTime!= null "><!-- 开始时间检索 -->
                and date_format(p.plan_demand_sub_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
            </if>
            <if test="params!= null and params.beginTime!= null "><!-- 结束时间检索 -->
                and date_format(p.plan_demand_sub_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
            </if>
            and p.del_flay=0
        </where>
        order by create_time desc

    </select>
</mapper>
