<?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.patrol.business.dao.mapper.TaskMapper">
	<update id="updateTask" parameterType="com.yeejoin.amos.patrol.dao.entity.Task">
		UPDATE p_task
		 <trim prefix="set" suffixOverrides=",">
  			status=#{status},
			<if test="title != null">title=#{title},</if>
			<if test="publisher > 0 ">publisher=#{publisher},</if>
			<if test="publisherName != null ">publisher_name=#{publisherName},</if>
			<if test="factFinishTime != null">fact_finish_time=#{factFinishTime},</if>
			<if test="isWarn != null">is_warn=#{isWarn},</if>
			<if test="warnTime != null">warn_time=#{warnTime},</if>
			<if test="feedbackNum != null">feedback_num=#{feedbackNum},</if>
			<if test="executor != null ">executor=#{executor},</if>
			<if test="depth > 0 ">depth=#{depth},</if>
			<if test="maxDepth > 0 ">max_depth=#{maxDepth},</if>
			<if test="remark != null ">remark=#{remark},</if>
			<if test="config != null ">config=#{config},</if>
 		</trim>
 		WHERE id=#{id}
	</update>

	<update id="updateTaskDetail" parameterType="com.yeejoin.amos.patrol.business.param.TaskDetailParam">
		UPDATE p_task_detail
		 <trim prefix="set" suffixOverrides=",">
			<if test="remark != null">remark=#{remark},</if>
			<if test="status >= 0 ">status=#{status},</if>
 		</trim>
 		WHERE id=#{id}
	</update>

	<select id="queryTaskCharts" parameterType="Map" resultType="Map">
	SELECT
		sum(a.processed) processed,
		sum(a.completed) completed,
		sum(a.cancelled) cancelled,
		sum(a.timeout) timeout,
		sum(a.total) total
	FROM
		(
			SELECT
				CASE STATUS
			WHEN 0 THEN
				1
			ELSE
				0
			END processed,
			CASE STATUS
		WHEN 1 THEN
			1
		ELSE
			0
		END completed,
		CASE STATUS
	WHEN 2 THEN
		1
	ELSE
		0
	END cancelled,
	 CASE STATUS
	WHEN 3 THEN
		1
	ELSE
		0
	END timeout,
	 1 total
	FROM
		p_task t
		<!-- LEFT JOIN s_user u on t.executor_id = u.id
		LEFT JOIN s_department d on d.id = u.department_id -->
	WHERE 1 = 1
		<!-- <if test="departmentId != null and departmentId > 0"> and d.id = #{departmentId}</if> -->
		<choose>
             <when test="departmentId > 0 ">and t.dep_id  = #{departmentId}</when>
             <when test="departmentId == -1 and ids != null and ids.size > 0">and t.executor in
             <foreach item="item" collection="ids" separator="," open="(" close=")" index="index">
				#{item}
			</foreach>
             </when>
          </choose>
		<if test="orgCode != null"> and t.org_code = #{orgCode}</if>
		<if test="startTime != null"> and t.publish_time <![CDATA[>=]]> #{startTime}</if>
		<if test="endTime != null"> and t.publish_time <![CDATA[<=]]> #{endTime}</if>
		<choose>
	        <when test="publisher != null and executorId != null">
	            and (t.executor_id = #{executorId} or t.publisher = #{publisher})
	        </when>
	        <otherwise>
	           <if test="executorId != null"> AND t.executor_id = #{executorId}</if>
	           <if test="publisher != null"> and t.publisher = #{publisher}</if>
	        </otherwise>
    	</choose>
		) a
	</select>

	<select id="queryTasks" parameterType="Map" resultType="Map">
		SELECT
			t.title,
			t.id,
			t.check_id checkId,
			t.publish_time,
			t.executor,
			CASE t. STATUS
		WHEN 0 THEN
			'处理中'
		WHEN 1 THEN
			'已完成'
		WHEN 2 THEN
			'已取消'
		WHEN 3 THEN
			'已超时'
		END STATUS
		FROM
			p_task t
		<!-- LEFT JOIN p_task_detail td on td.task_id = t.id -->
	<!-- 	LEFT JOIN s_user u on t.executor_id = u.id
		LEFT JOIN s_department d on d.id = u.department_id -->
		WHERE 1 = 1
		<!-- <if test="departmentId != null and departmentId > 0"> and d.id = #{departmentId}</if> -->
		<choose>
             <when test="departmentId != null and departmentId > 0 "> and t.dep_id = #{departmentId}</when>
             <when test="departmentId == -1 and ids != null">and t.executor in
             <foreach item="item" collection="ids" separator="," open="(" close=")" index="index">
				#{item}
			</foreach>
             </when>
          </choose>
		<if test="orgCode != null"> and t.org_code = #{orgCode}</if>
		<if test="startTime != null"> and t.publish_time<![CDATA[>=]]> #{startTime}</if>
		<if test="endTime != null"> and t.publish_time <![CDATA[<=]]> #{endTime}</if>
		<choose>
	        <when test="publisher != null and executorId != null">
	            and (t.executor_id = #{executorId} or t.publisher = #{publisher})
	        </when>
	        <otherwise>
	           <if test="executorId != null"> AND t.executor_id = #{executorId}</if>
	           <if test="publisher != null"> and t.publisher = #{publisher}</if>
	        </otherwise>
    	</choose>
		ORDER BY
			t. STATUS,
			t.finish_time

	</select>
	<!--任务统计 -->
	<select id="countTaskInfoData" resultType="long">
		SELECT
		count(1) AS total_num
		FROM
		p_task a
		<!-- <trim prefix="WHERE" prefixOverrides="AND ">
		<if test="remark!=null"> and a.remark like concat(concat("%",#{remark}),"%")</if>
		<if test="status!=null"> and a.status = #{status}</if>
		<if test="title!=null"> and a.title like concat(concat("%",#{title}),"%")</if>
		<if test="userId!=null"> and (a.publisher = #{userId} or a.executor_id = #{userId})</if>
		<if test="orgCode!=null"> and a.org_Code like #{orgCode}</if>
		<if test="pointId!=null"> and a.point_id = #{pointId}</if>
		</trim> -->

		<trim prefix="WHERE" prefixOverrides="AND ">
		<if test="remark!=null"> and a.remark like concat(concat("%",#{remark}),"%")</if>
		<if test="status!=null"> and a.status = #{status}</if>
		<if test="title!=null"> and a.title like concat(concat("%",#{title}),"%")</if>
		<if test="userId!=null"> and (a.publisher = #{userId} or a.executor_id = #{userId})</if>
		<if test="orgCode!=null"> and a.org_Code = #{orgCode}</if>
		<if test="deptId!=null"> and a.dep_id = #{deptId}</if>
		<if test="startTime!=null"> <![CDATA[ AND a.finish_time >= #{startTime} ]]></if>
		<if test="endTime!=null"> <![CDATA[AND a.finish_time <= #{endTime}  ]]></if>
		<if test="pointId!=null"> AND a.point_id = #{pointId}</if>
		</trim>





	</select>
	<!--任务查询 -->
	<select id="getTaskPageInfo" resultType="java.util.HashMap">
	SELECT
		a.id,
		a.title,
		a.executor,
		a.executor_id as executorId,
		a.publisher_name publisherName,
		a.publish_time AS publishTime,
		a.finish_time AS finishTime,
		a.status,
		IFNULL(a.fact_finish_time,"") AS factFinishTime,
<!-- 		IFNULL(case when u.mobile = '' or u.mobile is null then null else u.mobile END,u.telephone) as telep,
 -->		a.remark,
		a.feedback_num AS feedbackNum,
		(	SELECT  CONCAT(pp.`name`,'(',GROUP_CONCAT(pii.`name`),')') FROM p_task_detail ptd
		LEFT JOIN p_input_item pii ON pii.id = ptd.item_id
		LEFT JOIN p_point pp ON pp.id = ptd.point_id
		WHERE ptd.task_id = a.id
		GROUP BY pp.`name`) as pointItemName
		from  p_task a
		<!-- LEFT JOIN s_user u on u.id = a.executor_id -->
		<trim prefix="WHERE" prefixOverrides="AND ">
		<if test="remark!=null"> and a.remark like concat(concat("%",#{remark}),"%")</if>
		<if test="status!=null"> and a.status = #{status}</if>
		<if test="title!=null"> and a.title like concat(concat("%",#{title}),"%")</if>
		<if test="userId!=null"> and (a.publisher = #{userId} or a.executor_id = #{userId})</if>
		<if test="orgCode!=null"> and a.org_Code = #{orgCode}</if>
		<if test="deptId!=null"> and a.dep_id = #{deptId}</if>
		<if test="startTime!=null"> <![CDATA[ AND TO_DAYS(a.finish_time) >= TO_DAYS(#{startTime}) ]]></if>
		<if test="endTime!=null"> <![CDATA[AND TO_DAYS(a.finish_time) <= TO_DAYS(#{endTime})  ]]></if>
		<if test="pointId!=null"> AND a.point_id = #{pointId}</if>
		</trim>
		order by a.finish_time desc
		<choose>
	     <when test="pageSize==-1"></when>
	     <when test="pageSize!=-1">limit #{offset},#{pageSize}</when>
	    </choose>
	</select>

	<select id="getHidDangerCtDtOfYear" resultType="java.util.HashMap">
		SELECT s.dateMonth, sum(s.yearNum) yearNum from (
		SELECT
			date.date dateMonth,
			case  when t.id is not null then 1 else 0 end as yearNum
		FROM
			(
				SELECT
					@s :=@s - 1 AS `index`,
					LEFT (
						DATE_SUB(
							CURRENT_DATE,
							INTERVAL @s MONTH
						),
						7
					) AS `date`
				FROM
					mysql.help_topic,
					(
						SELECT
							@s := MONTH (CURRENT_DATE)
					) temp
				WHERE
					@s > 0
			) date

		LEFT JOIN p_task t ON date.date = DATE_FORMAT(t.publish_time, '%Y-%m')
		AND t.org_code = #{orgCode}
		) s
		GROUP BY
			s.dateMonth
		ORDER BY s.dateMonth
	</select>

	<select id="getHidDangerCtDtOfMonth"  resultType="java.util.HashMap">
		SELECT right(s.date, 2) date, sum(s.timeOutNum)  timeOutNum , sum(s.canelNum)  canelNum , sum(s.finishNum)  finishNum FROM (
		SELECT date.date, case when t.status = 3 then 1 else 0 end timeOutNum,
		case when t.status = 2 then 1 else 0 end canelNum,
		case when t.status = 1 then 1 else 0 end finishNum
		 from (
		SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
		FROM mysql.help_topic,(SELECT @a := 0) temp
		WHERE @a <![CDATA[<]]> DAY(LAST_DAY(CURRENT_DATE)) - DAY(CURRENT_DATE)
		UNION
		SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
		FROM mysql.help_topic,(SELECT @s := day(CURRENT_DATE)) temp
		WHERE @s > 0
		) date LEFT JOIN p_task t on date.date = DATE_FORMAT(t.publish_time,'%Y%m%d') and t.org_code = #{orgCode}
		) s	 GROUP BY s.date ORDER BY s.date
	</select>

	<select id="getHidDangerCtDtOfCur"  resultType="java.util.HashMap">
		SELECT
			CASE
		WHEN t. STATUS = 0 THEN
			'处理中'
		WHEN t. STATUS = 1 THEN
			'已完成'
		WHEN t. STATUS = 2 THEN
			'已取消'
		WHEN t. STATUS = 3 THEN
			'已超时'
		END AS name,
		 count(1) AS
		value
		FROM
			p_task t
		where  t.org_code = #{orgCode}
		GROUP BY
			t.STATUS
		ORDER BY
			t.STATUS
	</select>

	<select id="getPointIDsByRouteIDs" parameterType="java.util.List" resultType="java.util.HashMap">
        SELECT
		    r.id AS RouteID,
		    (
		        SELECT
		            GROUP_CONCAT(point_id)
		        FROM
		            p_route_point rp
		        WHERE
		            rp.route_id = r.id
		    ) AS PointID
		FROM
		    p_route r
		WHERE
		    r.id IN
          <foreach item="item" index="index"  collection="routeIDList" open="(" separator="," close=")">
              #{item}
          </foreach>
    </select>


    <resultMap id="taskInfoResultMap" type="com.yeejoin.amos.patrol.core.common.response.TaskInfoRespone">
       <result property="taskID" column="taskID" />
       <result property="taskName" column="taskName" />
       <result property="status" column="status" />
       <result property="arranger" column="arranger" />
       <result property="executor" column="executor" />
       <result property="planTime" column="planTime" />
       <result property="fishTime" column="fishTime" />
       <result property="content" column="content" />
       <result property="pointID" column="pointId" />
       <result property="pointName" column="pointName" />
       <result property="inputItemName" column="inputItemName" />
       <result property="inputValue" column="inputValue" />
       <result property="inputStatus" column="inputStatus" />
    </resultMap>
    <select id="getTaskDetailInfoByTaskId" parameterType="java.lang.Long" resultMap="taskInfoResultMap">
		SELECT
			pt.id taskID,
			pt.title taskName,
			pt.`status`,
			pt.publisher_name arranger,
			pt.executor,
			pt.finish_time AS planTime,
			pt.fact_finish_time AS fishTime,
			pt.remark AS content,
			ptd.point_id AS pointId,
			pp.`name` pointName,
			pii.`name` AS inputItemName,
			pci.input_value AS inputValue,
			pci.is_ok AS inputStatus
		FROM
			p_task_detail ptd
		LEFT JOIN p_task pt ON pt.id = ptd.task_id
		LEFT JOIN p_point pp ON pp.id = ptd.point_id
		LEFT JOIN p_check_input pci ON pci.input_id = ptd.item_id  AND pci.check_id = pt.check_id
		LEFT JOIN p_input_item pii ON pii.id = pci.input_id
		WHERE
			ptd.task_id = #{taskId}
	</select>

	<select id="getTaskForAppPie" resultType="Map">
		SELECT
			case when sum(a.proccesed) is null then 0 else sum(a.proccesed) end  proccesed,
			case when sum(a.finished) is null then 0 else sum(a.finished) end finished,
			case when sum(a.cancel) is null then 0 else sum(a.cancel) end cancel,
			case when sum(a.timeout) is null then 0 else sum(a.timeout) end timeout,
			count(1) total
		FROM
			(
				SELECT
					t.id,
					CASE t.`status`
				WHEN 0 THEN
					1
				ELSE
					0
				END proccesed,
				CASE t.`status`
			WHEN 1 THEN
				1
			ELSE
				0
			END finished,
			CASE t.`status`
		WHEN 2 THEN
			1
		ELSE
			0
		END cancel,
		 CASE t.`status`
		WHEN 3 THEN
			1
		ELSE
			0
		END timeout
		FROM
			p_task t
		<!-- LEFT JOIN s_user u ON t.executor_id = u.id
		LEFT JOIN s_department d on d.id = u.department_id -->
		<where>
			<if test="orgCode !=null and orgCode != ''">and t.org_code=#{orgCode}</if>
			<if test="userId !=null and userId > 0">and t.executor_id=#{userId}</if>
			<!-- <if test="departmentId !=null and departmentId > 0">and d.id=#{departmentId}</if> -->
			<if test="departmentId !=null and departmentId > 0">and t.dep_id=#{departmentId}</if>
		</where>	) a
	</select>

	<select id="getTaskStatistics" resultType="com.yeejoin.amos.patrol.business.vo.TaskStatisticsVo" parameterType="com.yeejoin.amos.patrol.business.param.TaskStatisticsParam">
		<!-- SELECT
			CASE
		WHEN sum(a.proccesed) IS NULL THEN
			0
		ELSE
			sum(a.proccesed)
		END proccesed,
		 CASE
		WHEN sum(a.finished) IS NULL THEN
			0
		ELSE
			sum(a.finished)
		END finished,
		 CASE
		WHEN sum(a.cancel) IS NULL THEN
			0
		ELSE
			sum(a.cancel)
		END cancel,
		 CASE
		WHEN sum(a.timeout) IS NULL THEN
			0
		ELSE
			sum(a.timeout)
		END timeout,
		 count(1) total,
		<choose>
	          <when test=" statisticsTyle == 0 ">
	              a.executor name
	         </when >
	          <when test=" statisticsTyle == 1 ">
	              a.executor name
	         </when >
	         <when test=" statisticsTyle == 2 ">
	              a.routeName name
	         </when >
	         <when test=" statisticsTyle == 3 ">
	             a.pointName name
	         </when >
	         <when test=" statisticsTyle == 4 ">
	              a.departmentName name
	         </when >
	         <when test=" statisticsTyle == 5 ">
	              a.planName name
	         </when >
	         <when test=" statisticsTyle == 6 ">
	              DATE_FORMAT(a.finish_time, '%Y-%m-%d') name
	         </when >
	         <when test=" statisticsTyle == 7 ">
	              DATE_FORMAT(a.finish_time, '%Y-%m') name
	         </when >
	   </choose>
		FROM
			(
				SELECT
					DISTINCT
					t.id,
					t.executor_id,
					u.name executor,
					t.finish_time,
					<choose>
				         <when test=" statisticsTyle == 2 ">
				              r.NAME routeName,
				         </when >
				         <when test=" statisticsTyle == 3 ">
				              pt.`name` pointName,
				         </when >
				         <when test=" statisticsTyle == 4 ">
				              d.department_name departmentName,
				         </when >
				         <when test=" statisticsTyle == 5 ">
				              pn.NAME planName,
				         </when >
				   </choose>
					CASE t.`status`
				WHEN 0 THEN
					1
				ELSE
					0
				END proccesed,
				CASE t.`status`
			WHEN 1 THEN
				1
			ELSE
				0
			END finished,
			CASE t.`status`
		WHEN 2 THEN
			1
		ELSE
			0
		END cancel,
		 CASE t.`status`
		WHEN 3 THEN
			1
		ELSE
			0
		END timeout
		FROM
			p_task t
			LEFT JOIN s_user u ON t.executor_id = u.id
		<choose>
	          <when test=" statisticsTyle == 1 ">
	             LEFT JOIN p_task_detail td ON td.task_id = t.id
	         </when >
	         <when test=" statisticsTyle == 2 ">
	                LEFT JOIN p_task_detail td ON td.task_id = t.id
					LEFT JOIN p_check c ON c.id = td.check_id
					LEFT JOIN p_plan pn ON pn.id = c.plan_id
					LEFT JOIN p_route r ON c.route_id = r.id
	         </when >
	         <when test=" statisticsTyle == 3 ">
	             LEFT JOIN p_task_detail td ON td.task_id = t.id
				 LEFT JOIN p_check c ON c.id = td.check_id
				 LEFT JOIN p_point pt on pt.id = c.point_id
	         </when >
	         <when test=" statisticsTyle == 4">
				LEFT JOIN s_department d ON d.id = u.department_id
	         </when >
	         <when test=" statisticsTyle == 5 ">
	              LEFT JOIN p_task_detail td ON td.task_id = t.id
				  LEFT JOIN p_check c ON c.id = td.check_id
				  LEFT JOIN p_plan pn ON pn.id = c.plan_id
	         </when >
	   </choose>
		WHERE
			1 = 1
		and t.org_code = #{orgCode}
		<if test="startTime !=null and startTime!= '' "> <![CDATA[ AND t.finish_time >= #{startTime} ]]> </if>
		<if test="endTime !=null and endTime!='' "><![CDATA[AND t.finish_time <= #{endTime}  ]]></if>
			<choose>
	          <when test=" statisticsTyle == 1 ">
	              and td.id is not null
	         </when >
	         <when test=" statisticsTyle == 2 ">
	              and td.id is not null and r.id is not null
	         </when >
	         <when test=" statisticsTyle == 3 ">
	             and td.id is not null and pt.id is not null
	         </when >
	         <when test=" statisticsTyle == 4 ">
	             and  d.id is not null
	         </when >
	         <when test=" statisticsTyle == 5 ">
	              and td.id is not null and pn.id is not null
	         </when >
	   </choose>
			) a
		GROUP BY
		<choose>
	          <when test=" statisticsTyle == 0 ">
	              a.executor_id
	         </when >
	          <when test=" statisticsTyle == 1 ">
	              a.executor_id
	         </when >
	         <when test=" statisticsTyle == 2 ">
	              a.routeName
	         </when >
	         <when test=" statisticsTyle == 3 ">
	             a.pointName
	         </when >
	         <when test=" statisticsTyle == 4 ">
	              a.departmentName
	         </when >
	         <when test=" statisticsTyle == 5 ">
	              a.planName
	         </when >
	         <when test=" statisticsTyle == 6 ">
	              DATE_FORMAT(a.finish_time, '%Y-%m-%d')
	         </when >
	         <when test=" statisticsTyle == 7 ">
	              DATE_FORMAT(a.finish_time, '%Y-%m')
	         </when >
	   </choose> -->
	    SELECT
        CASE
        WHEN sum(a.proccesed) IS NULL THEN
        0
        ELSE
        sum(a.proccesed)
        END proccesed,
        CASE
        WHEN sum(a.finished) IS NULL THEN
        0
        ELSE
        sum(a.finished)
        END finished,
        CASE
        WHEN sum(a.cancel) IS NULL THEN
        0
        ELSE
        sum(a.cancel)
        END cancel,
        CASE
        WHEN sum(a.timeout) IS NULL THEN
        0
        ELSE
        sum(a.timeout)
        END timeout,
        count(1) total,
        <choose>
            <when test=" statisticsTyle == 0 ">
                  CAST(a.executor AS CHAR(100))   name
            </when>
            <when test=" statisticsTyle == 1 ">
               CAST(a.executor AS CHAR(100)) name
            </when>
            <when test=" statisticsTyle == 2 ">
                a.routeName name
            </when>
            <when test=" statisticsTyle == 3 ">
                a.pointName name
            </when>
        	<when test=" statisticsTyle == 4 ">
             a.departmentName name
            </when >
            <when test=" statisticsTyle == 5 ">
                a.planName name
            </when>
            <when test=" statisticsTyle == 6 ">
                DATE_FORMAT(a.finish_time, '%Y-%m-%d') name
            </when>
            <when test=" statisticsTyle == 7 ">
                DATE_FORMAT(a.finish_time, '%Y-%m') name
            </when>
        </choose>
        FROM
        (
        SELECT
        DISTINCT
        t.id,
        t.executor,
       <!--  u.name executor, -->
        t.finish_time,
        <choose>
            <when test=" statisticsTyle == 2 ">
                r.NAME routeName,
            </when>
            <when test=" statisticsTyle == 3 ">
                pt.`name` pointName,
            </when>
            <when test=" statisticsTyle == 4 ">
            	 t.dep_id departmentName,
             </when >
            <when test=" statisticsTyle == 5 ">
                pn.NAME planName,
            </when>
        </choose>
        CASE t.`status`
        WHEN 0 THEN
        1
        ELSE
        0
        END proccesed,
        CASE t.`status`
        WHEN 1 THEN
        1
        ELSE
        0
        END finished,
        CASE t.`status`
        WHEN 2 THEN
        1
        ELSE
        0
        END cancel,
        CASE t.`status`
        WHEN 3 THEN
        1
        ELSE
        0
        END timeout
        FROM
        p_task t
        <choose>
            <when test=" statisticsTyle == 1 ">
                LEFT JOIN p_task_detail td ON td.task_id = t.id
            </when>
            <when test=" statisticsTyle == 2 ">
                LEFT JOIN p_task_detail td ON td.task_id = t.id
                LEFT JOIN p_check c ON c.id = td.check_id
                LEFT JOIN p_plan pn ON pn.id = c.plan_id
                LEFT JOIN p_route r ON c.route_id = r.id
            </when>
            <when test=" statisticsTyle == 3 ">
                LEFT JOIN p_task_detail td ON td.task_id = t.id
                LEFT JOIN p_check c ON c.id = td.check_id
                LEFT JOIN p_point pt on pt.id = c.point_id
            </when>
            <when test=" statisticsTyle == 5 ">
                LEFT JOIN p_task_detail td ON td.task_id = t.id
                LEFT JOIN p_check c ON c.id = td.check_id
                LEFT JOIN p_plan pn ON pn.id = c.plan_id
            </when>
        </choose>
        WHERE
        1 = 1
        and t.org_code = #{orgCode}
        <if test="startTime !=null and startTime!= '' "> <![CDATA[ AND t.finish_time >= #{startTime} ]]> </if>
        <if test="endTime !=null and endTime!='' "><![CDATA[AND t.finish_time <= #{endTime}  ]]></if>
        <choose>
            <when test=" statisticsTyle == 1 ">
                and td.id is not null
            </when>
            <when test=" statisticsTyle == 2 ">
                and td.id is not null and r.id is not null
            </when>
            <when test=" statisticsTyle == 3 ">
                and td.id is not null and pt.id is not null
            </when>
               <when test=" statisticsTyle == 4 ">
               and  t.dep_id !='-1'
             </when >
            <when test=" statisticsTyle == 5 ">
                and td.id is not null and pn.id is not null
            </when>
        </choose>
        ) a
        GROUP BY
        <choose>
            <when test=" statisticsTyle == 0 ">
                a.executor
            </when>
            <when test=" statisticsTyle == 1 ">
                a.executor
            </when>
            <when test=" statisticsTyle == 2 ">
                a.routeName
            </when>
            <when test=" statisticsTyle == 3 ">
                a.pointName
            </when>
           <when test=" statisticsTyle == 4 ">
            	a.departmentName
            </when >
            <when test=" statisticsTyle == 5 ">
                a.planName
            </when>
            <when test=" statisticsTyle == 6 ">
                DATE_FORMAT(a.finish_time, '%Y-%m-%d')
            </when>
            <when test=" statisticsTyle == 7 ">
                DATE_FORMAT(a.finish_time, '%Y-%m')
            </when>
        </choose>
	</select>

	<select id="getTaskFinishCountByOrgCode" resultType="String">
		SELECT
			concat(
				sum(a.notfinish),
				'/',
				sum(a.finish)
			) finish
		FROM
			(
				SELECT
					CASE
				WHEN t. STATUS = 1 THEN
					1
				ELSE
					0
				END finish,
				CASE
			WHEN t. STATUS != 1 THEN
				1
			ELSE
				0
			END notfinish
			FROM
				p_task t
			WHERE
				 t. STATUS IN (0, 1, 3) AND
				position( #{orgCode} in t.org_code)
			) a
	</select>

	<select id="findByTaskDetailId" resultType="com.yeejoin.amos.patrol.business.vo.TaskDetailVo">
		SELECT
			td.task_id taskId,
			td.item_id itemId,
			td.check_id checkId,
			td. STATUS,
			td.remark,
			td.point_id pointId,
			CASE  WHEN pci.point_classify_name is NULL THEN ii.`name`
			ELSE CONCAT(pci.point_classify_name,'-',ii.`name`)
			END itemName,
			t. NAME pointName,
			t.point_no pointNo,
			t.is_fixed isFixed
		FROM
			p_task_detail td
		LEFT JOIN p_input_item ii ON ii.id = td.item_id
		LEFT JOIN p_point t ON t.id = td.point_id
		LEFT JOIN p_check_input pci ON pci.check_id = td.check_id AND pci.input_id = td.item_id
		WHERE
			td.task_id = #{taskID}
	</select>

	<resultMap type="com.yeejoin.amos.patrol.business.vo.CheckTaskDetailVo" id="CheckTaskDetailMap">
		<id column="checkId" property="checkId"/>
		<result column="inputName" property="inputName"/>
		<result column="inputValue" property="inputValue"/>
		<result column="inputStatus" property="inputStatus"/>
		<result column="itemType" property="itemType"/>
		<result column="dataJson" property="dataJson"/>
		<result column="isMust" property="isMust"/>
		<result column="pictureJson" property="pictureJson"/>
		<result column="isMultiline" property="isMultiline"/>
		<result column="orderNo" property="orderNo"/>
		<result column="pointNo" property="pointNo"/>
		<result column="pointId" property="pointId"/>
		<result column="pointName" property="pointName"/>
		<result column="checkInputId" property="checkInputId"/>
		<result column="remark" property="remark"/>
		<collection property="pointInputImgUrls" ofType="String" select="queryCheckTaskInputItemImg" column="{checkId=checkId,itemId=itemId}" >
			<result column="photoData" property="photoData"/>
		</collection>
	</resultMap>

	<resultMap type="com.yeejoin.amos.patrol.business.vo.OfflineTasksVo" id="SynchronizedOfflineTaskDetailMap">
		<id column="checkid" property="checkid"/>
		<result column="title" property="title"/>
		<result column="publishTime" property="publish_time"/>
		<result column="executor" property="executor"/>
		<result column="status" property="status"/>
		 <collection property="task" ofType="Map"
			select="queryTaskInfoById" column="{taskId=id}" >
		</collection>
		 <collection property="taskDetail" ofType="com.yeejoin.amos.patrol.business.vo.CheckTaskDetailVo"
			select="queryCheckTaskInputItems" column="{taskId=id}" >
		</collection>
	</resultMap>

	<select id="queryCheckTaskInputItems" resultMap="CheckTaskDetailMap">
		SELECT
			ci.check_id checkId, ii.name inputName, ci.input_id itemId, ci.input_value inputValue, ci.remark,
			ii.item_type itemType, ii.data_json dataJson, ii.is_must isMust, ii.picture_json, ii.is_multiline,
			ii.order_no , ci.id checkInputId,pp.point_no pointNo,pp.id pointId,pp.name pointName
		FROM
			p_check_input ci
			INNER JOIN p_task_detail td ON td.check_id = ci.check_id AND td.item_id = ci.input_id
			LEFT JOIN p_task t ON t.id = td.task_id
			LEFT JOIN p_point pp ON pp.id = td.point_id
			LEFT JOIN p_input_item ii ON td.item_id = ii.id
		WHERE  t.id = #{taskId}
		<if test="checkId!=null and checkId> 0">AND td.check_id = #{checkId}	</if>
	</select>

	<select id="queryCheckTaskInputItemImg" resultType="String">
		SELECT photo_data photoData from p_check_shot cs where cs.check_id = #{checkId} and cs.check_input_id = #{itemId}
	</select>

	<select id="queryTaskInfoById" resultType="Map">
		select
		a.id,
		a.title,
		a.executor,
		a.publisher_name publisherName,
		a.publish_time AS publishTime,
		a.finish_time AS finishTime,
		a.status,
		a.fact_finish_time as factFinishTime,
		a.remark,
		a.feedback_num AS feedbackNum,
		a.depth,
		a.max_depth maxDepth
		from p_task a
		WHERE a.id=#{taskId}
	</select>

	<select id="synchronizedOfflineTasks" resultMap="SynchronizedOfflineTaskDetailMap">
		SELECT
			t.title,
			t.id,
			t.publish_time,
			t.executor,
			CASE t. STATUS
		WHEN 0 THEN
			'处理中'
		WHEN 1 THEN
			'已完成'
		WHEN 2 THEN
			'已取消'
		WHEN 3 THEN
			'已超时'
		END STATUS
		FROM
			p_task t
		<!-- LEFT JOIN s_user u on t.executor_id = u.id
		LEFT JOIN s_department d on d.id = u.department_id -->
		WHERE 1 = 1
		AND t.executor_id = #{executorId}
		 and t.status = #{status}
		<if test="orgCode != null"> and t.org_code = #{orgCode}</if>
		and t.finish_time <![CDATA[>=]]> sysdate()
	</select>
</mapper>