<?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.PlanYearsMapper">

	<resultMap type="com.ozs.plan.doman.PlanYears" id="PlanYearsResult">
		<id 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="delay_reason" column="delayReason"/>
		<result property="remarks" column="remarks"/>
		<result property="project_status" column="projectStatus"/>
		<result property="send_letter" column="sendLetter"/>
		<result property="letter_str" column="letterStr"/>
		<result property="created" column="created"/>
		<result property="create_time" column="createTime"/>
		<result property="updated" column="updated"/>
		<result property="update_time" column="updateTime"/>
		<result property="refuse_reason" column="refuseReason"/>
		<result property="del_flay" column="delFlay"/>
	</resultMap>

	<update id="updateInfoById" parameterType="com.ozs.plan.doman.PlanYears">
		update plan_years
		set project_name=#{projectName},
			project_type=#{projectType},
			demand_overview=#{demandOverview},
			evaluation=#{evaluation},
			purchase_services=#{purchaseServices},
			plan_demand_sub_time=#{planDemandSubTime},
			plan_purchase_finish_time=#{planPurchaseFinishTime},
			plan_deliver_time=#{planDeliverTime},
			project_attr=#{projectAttr},
			adjust_reason=#{adjustReason},
			remarks=#{remarks},
			updated=#{updated},
			update_time=#{updateTime}
		where plan_year_id = #{planYearId}
	</update>

	<select id="selectPlanYearsList" parameterType="com.ozs.plan.doman.PlanYears" resultMap="PlanYearsResult">
		select * from plan_years
		<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 del_flay=0
		</where>
		order by create_time desc
	</select>


	<select id="countProjectName" resultType="java.lang.Integer" parameterType="java.lang.String">
		select count(plan_years.plan_year_id)
		from plan_years
		where project_name = #{projectName}
		  and del_flay = 0
	</select>

	<select id="getById" resultType="com.ozs.plan.doman.PlanYears" parameterType="java.lang.Long">
		select *
		from plan_years
		where plan_year_id = #{planYearId}
		  and del_flay = 0
	</select>

	<select id="countProjectNameOther" resultType="java.lang.Integer" parameterType="java.lang.String">
		select count(plan_years.plan_year_id)
		from plan_years
		where project_name = #{projectName}
		  and plan_year_id != #{planYearId}
		  and del_flay = 0
	</select>

	<select id="query" resultType="com.ozs.plan.doman.PlanYears"
			parameterType="com.ozs.plan.doman.vo.requestVo.PlanYearsStandardVo">
		select p.* from
		plan_years p left join pm_audit_dept_ref r
		on p.plan_year_id = r.ref_id and r.ref_type = 0
		<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="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="beginTime != null "><!-- 开始时间检索 -->
				and date_format(p.plan_demand_sub_time,'%y%m') &gt;= date_format(#{beginTime},'%y%m')
			</if>
			<if test="endTime != null "><!-- 结束时间检索 -->
				and date_format(p.plan_demand_sub_time,'%y%m') &lt;= date_format(#{endTime},'%y%m')
			</if>
			and p.del_flay=0
		</where>
		order by create_time desc

	</select>

	<!--	创建计划信息-->
	<insert id="insertPlanYears" parameterType="com.ozs.plan.doman.PlanYears" useGeneratedKeys="true"
			keyProperty="planYearId">
		insert into plan_years (purchase_dept_id, project_name, project_type, demand_overview, evaluation,
								purchase_services, organ_division, budget_account, plan_demand_sub_time,
								plan_purchase_finish_time,
								plan_deliver_time, project_attr, adjust_reason, remarks, project_status,
								created, create_time, del_flay, send_letter)
		values (#{purchaseDeptId}, #{projectName}, #{projectType}, #{demandOverview}, #{evaluation},
				#{purchaseServices}, #{organDivision}, #{budgetAccount}, #{planDemandSubTime},
				#{planPurchaseFinishTime},
				#{planDeliverTime}, #{projectAttr}, #{adjustReason}, #{remarks}, #{projectStatus},
				#{created}, #{createTime}, 0, 0)
	</insert>

	<!--	计划删除-->
	<update id="deletePlanYearsByIds" parameterType="java.lang.Long">
		update plan_years set del_flay=1 where plan_year_id in
		<foreach collection="array" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
	</update>

	<update id="deletePlanYearsById" parameterType="java.lang.Long">
		update plan_years
		set del_flay=1
		where plan_year_id = #{plan_year_id}
	</update>

	<!--	计划提交-->
	<update id="commit" parameterType="java.lang.Long">
		update plan_years
		set project_status=1
		where plan_year_id = #{plan_year_id}
	</update>

	<!--	计划审核-->
	<update id="review" parameterType="com.ozs.plan.doman.vo.requestVo.PlanYearsStandardVo">
		update plan_years
		<set>
			<if test="refuseReason != null and refuseReason != ''">
				refuse_reason=#{refuseReason},
			</if>
			<if test="projectStatus != null and projectStatus != ''">
				project_status=#{projectStatus},
			</if>
			<if test="adjustReason != null and adjustReason != ''">
				adjust_reason=#{adjustReason}
			</if>
			<if test="projectType != null and projectType != ''">
				project_type= #{projectType}
			</if>
		</set>
		where plan_year_id = #{planYearId}
	</update>

	<update id="sendLetter" parameterType="com.ozs.plan.doman.vo.requestVo.PlanYearsStandardVo">
		update plan_years
		set send_letter=1,
		<if test="letterStr != null and letterStr != ''">
			letter_str=#{letterStr}
		</if>
		where plan_year_id = #{planYearId}
	</update>


</mapper>
