<?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.yeejoin.amos.supervision.business.dao.mapper.PlanMapper">
	<!-- 另存计划 -->
	<insert id="saveAs" parameterType="String">
		INSERT INTO p_plan (
			`name`,
			`dept_id`,
			`org_code`,
			`route_id`,
			`plan_type`,
			`is_fixed_date`,
			`in_order`,
			`plan_begin`,
			`plan_end`,
			`day_rate`,
			`day_time`,
			`day_interval`,
			`day_interval_unit`,
			`day_begin`,
			`day_end`,
			`execute_interval`,
			`month_type`,
			`what_day`,
			`what_week`,
			`week_day`,
			`duration`,
			`user_id`,
			`execute_rate`,
			`week_begin_num`,
			`week_end_num`,
			`min_space`,
			`is_score`,
			`score_formula`,
			`status`,
			`error`,
			`next_gen_date`,
			`create_by`,
			`create_date`,
			`last_upd_by`,
			`last_upd_time`,
			`remark`,
			`remark1`,
			`remark2`,
			`is_single_execution`,
			`user_dept`
		) SELECT
			CONCAT(`name`, '复制'),
			`dept_id`,
			`org_code`,
			`route_id`,
			`plan_type`,
			`is_fixed_date`,
			`in_order`,
			`plan_begin`,
			`plan_end`,
			`day_rate`,
			`day_time`,
			`day_interval`,
			`day_interval_unit`,
			`day_begin`,
			`day_end`,
			`execute_interval`,
			`month_type`,
			`what_day`,
			`what_week`,
			`week_day`,
			`duration`,
			`user_id`,
			`execute_rate`,
			`week_begin_num`,
			`week_end_num`,
			`min_space`,
			`is_score`,
			`score_formula`,
			`status`,
			`error`,
			`next_gen_date`,
			`create_by`,
             CURRENT_TIMESTAMP,
			`last_upd_by`,
			`last_upd_time`,
			`remark`,
			`remark1`,
			`remark2`,
			`is_single_execution`,
			`user_dept`

		FROM
			`p_plan`
		WHERE
			id = #{id}
	</insert>
	<select id="getPlanByRouteId" resultType="com.yeejoin.amos.supervision.dao.entity.Plan">
		SELECT * FROM `p_plan`
		<where>
			is_delete = 0
			<if test="routeId != null"> and route_id = #{routeId}</if>
		</where>
	</select>
	<update id="updPlanStatusOrGenDate">
	 update p_plan
	 <trim prefix="set" suffixOverrides=",">
		<if test="status!=null">  status = #{status},</if>
		<if test="first_flag!=null">  first_flag = #{first_flag},</if>
		<if test="next_gen_date!=null"> next_gen_date =  #{next_gen_date},</if>
		<if test="plan_task_id!=null"> plan_task_id =  #{plan_task_id},</if>
		</trim>
	 where id = #{id}
	</update>
	<!--统计-巡检计划 -->
	<select id="countPlanInfoData" resultType="long">
		SELECT
		count(1) AS total_num
		FROM
		p_plan a ,
		p_route b
		WHERE
		a.route_Id = b.id and a.is_delete = 0
	<if test="planName!=null"> and a.name like concat(concat("%",#{planName}),"%")</if>
	<if test="checkTypeId!=null and checkTypeId != '' "> and a.check_type_id = #{checkTypeId}</if>
	<if test="leadPerson!=null and leadPerson != '' "> and a.lead_people_ids = #{leadPerson}</if>
	<if test="routeId!=null"> and a.route_Id = #{routeId}</if>
	<if test="remark!=null"> and a.remark1 like concat(concat("%",#{remark}),"%")</if>
	<if test="userId!=null"> and FIND_IN_SET(#{userId},a.user_id)</if>
	<if test="orgCode!=null"> and (a.org_Code like concat (#{orgCode},"-%")or a.org_Code= #{orgCode})</if>
	<if test="ownerId!=null"> and b.owner_id = #{ownerId}</if>
	<if test="planStatus!=null and planStatus != '' "> and a.`status` = #{planStatus}</if>
	</select>
	<!--巡检计划查询 -->
	<select id="getPlanInfo" resultType="java.util.HashMap">
	SELECT
		a.id,a.name ,
	    IFNULL(a.plan_begin,"") planBegin,
	    a.`status`,
	    IFNULL(a.plan_end,"") planEnd,
	    a.plan_end planEnd,
	    a.plan_type planType,
	    b.name routeName,
	    a.route_id routeId,
	    a.is_fixed_date isFixedDate,
	    a.remark,
		a.`user_id` as userId,
		a.create_by as createBy,
		a.create_date as createDate,
		b.boss,
	    b.owner_id AS ownerId,
		b.owner_name AS ownerName,
	    b.boss_name as bossName,
	    a.user_name as userName,
	    a.check_type_id as checkTypeId,
		a.check_type_name as checkTypeName,
		a.lead_department_ids as leadDepartmentIds,
		a.lead_department_names as leadDepartmentNames,
		a.lead_people_ids as leadPeopleIds,
		a.lead_people_names as leadPeopleNames,
		a.maker_user_id as makerUserId,
		a.maker_user_name as makerUserName,
		a.maker_user_dept_name as makerUserDeptName,
		a.check_level as checkLevel,
		(select count(1) from p_route_point ppo where ppo.route_id = b.id) as totalPoint,
		(select count(1) from p_plan_task t where t.plan_id = a.id) as totalPlanTask,
		(select count(1) from p_plan_task t where t.plan_id = a.id and t.finish_status <![CDATA[<=]]> 1 ) as waitFinishPlanTask
		FROM
		p_plan a,
		p_route b
		WHERE
		a.route_Id = b.id and a.is_delete = 0
		<if test="planName!=null"> and a.name like concat(concat("%",#{planName}),"%")</if>
		<if test="checkTypeId!=null and checkTypeId != '' "> and a.check_type_id = #{checkTypeId}</if>
		<if test="leadPerson!=null and leadPerson != '' "> and a.lead_people_ids = #{leadPerson}</if>
		<if test="routeId!=null"> and a.route_Id = #{routeId}</if>
		<if test="remark!=null"> and a.remark1 like concat(concat("%",#{remark}),"%")</if>
		<if test="userId!=null"> and FIND_IN_SET(#{userId},a.user_id)</if>
		<if test="orgCode!=null"> and (a.org_Code like concat (#{orgCode},"-%")or a.org_Code= #{orgCode})</if>
		<if test="ownerId!=null"> and b.owner_id = #{ownerId}</if>
		<if test="planStatus!=null and planStatus != '' "> and a.`status` = #{planStatus}</if>
		order by a.id desc
		<choose>
	     <when test="pageSize==-1"></when>
	     <when test="pageSize!=-1">limit #{offset},#{pageSize}</when>
	    </choose>
	</select>
  <!--巡检计划查询不分页 -->
	<select id="getPlanInfoNoPage" resultType="java.util.HashMap">
	SELECT
		a.id,a.name ,a.plan_type planType,b.name routeName,a.route_id routeId,a.is_fixed_date isFixedDate,a.remark
		FROM
		p_plan a,
		p_route b
		WHERE
		a.is_delete = 0 and a.route_Id = b.id
		<if test="planName!=null"> and a.name like concat(concat("%",#{planName}),"%")</if>
		<if test="planType!=null"> and a.plan_Type = #{planType}</if>
		<if test="routeId!=null"> and a.route_Id = #{routeId}</if>
		<if test="remark!=null"> and a.remark like concat(concat("%",#{remark}),"%")</if>
		<if test="orgCode!=null"> and (a.org_code LIKE CONCAT( #{orgCode}, '-%' ) or a.org_code= #{orgCode} )</if>
		<if test="userId!=null"> and FIND_IN_SET(#{userId},a.user_id)</if>
		order by a.id
	</select>

	<select id="queryPlanListByOrgCode" resultType="Map">
		select p.id, p.name from p_plan p where p.is_delete = 0 and (p.org_code like concat(#{orgCode},"-%") or p.org_code = #{orgCode})
	</select>

	<update id="initUpdatePlanStatus">
	 	UPDATE p_plan p SET p.`status` = 1 WHERE  p.is_delete = 0 AND p.`status` = 0 AND p.plan_end <![CDATA[ < ]]> CURDATE();
	</update>
	
	<update id="initUpdatePlanNextGenDate">
	 	UPDATE p_plan p SET  p.next_gen_date = CURDATE() WHERE p.is_delete = 0 AND p.`status` = 0 AND p.plan_end IS NULL AND p.next_gen_date <![CDATA[ < ]]> CURDATE();
	</update>
</mapper>