<?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.TaskReportMapper">
	<select id="queryWithPoint" resultType="java.util.Map">

		SELECT
		<if test="summation != 'true'">
			pd.pointId itemId,
			pd.pointName itemName,
		</if>
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '01' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish01,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '02' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish02,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '03' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish03,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '04' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish04,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '05' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish05,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '06' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish06,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '07' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish07,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '08' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish08,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '09' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish09,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '10' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish10,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '11' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish11,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '12' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish12,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '01' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait01,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '02' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait02,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '03' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait03,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '04' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait04,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '05' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait05,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '06' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait06,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '07' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait07,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '08' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait08,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '09' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait09,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '10' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait10,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '11' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait11,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '12' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait12
		FROM
		(
		SELECT
		p.point_id pointId,
		p.pointName,
		p.dateMonth,
		count(
		CASE
		WHEN p.is_finish = 1 THEN
		1
		ELSE
		NULL
		END
		) taskCountFinish,
		count(
		p.is_finish
		) taskCountWait
		FROM
		(
		SELECT
		DATE_FORMAT(d.create_date, '%Y') dateYear,
		DATE_FORMAT(d.create_date, '%m') dateMonth,
		d.point_id,
		d.task_no,
		d.is_finish,
		pp.`name` pointName
		FROM
		p_plan_task_detail d
		LEFT JOIN p_point pp ON pp.id = d.point_id
		WHERE pp.id is not NULL
		<if test="orgCode != null and orgCode !=''" >
			And pp.org_code= #{orgCode}
		</if>
		AND d.create_date &gt;= '${startDate}'
		AND d.create_date &lt;= '${endDate}'
		<if test="planId != null and planId !=''" >
			and EXISTS(select 1 from p_plan_task pt where pt.id = d.task_no and pt.plan_id = ${planId})
		</if>
		<if test="routeId != null and routeId !=''" >
			and EXISTS(select 1 from p_plan_task pt where pt.id = d.task_no and pt.route_id = ${routeId})
		</if>
		) p
		GROUP BY
		p.point_id,
		p.dateMonth
		) pd
		<if test="summation != 'true'">
			GROUP BY
			pd.pointId
		</if>


	</select>


	<select id="queryWithDept" resultType="java.util.Map">
		select
		<if test="summation != 'true'">
			pd.departmentId  itemId,
			pd.departmentName itemName,
		</if>

		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '01' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish01,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '02' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish02,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '03' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish03,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '04' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish04,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '05' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish05,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '06' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish06,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '07' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish07,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '08' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish08,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '09' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish09,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '10' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish10,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '11' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish11,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '12' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish12,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '01' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait01,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '02' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait02,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '03' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait03,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '04' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait04,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '05' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait05,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '06' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait06,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '07' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait07,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '08' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait08,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '09' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait09,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '10' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait10,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '11' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait11,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '12' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait12
		from (
		SELECT
		p.department_id departmentId,
		p.department_id departmentName,
		p.dateMonth,
		count(
		CASE
		WHEN p.is_finish = 1 THEN
		1
		ELSE
		NULL
		END
		) taskCountFinish,
		count(
			p.is_finish
		) taskCountWait
		FROM
		(
		SELECT
		DATE_FORMAT(d.create_date, '%Y') dateYear,
		DATE_FORMAT(d.create_date, '%m') dateMonth,
		d.point_id,
		d.task_no,
		pt.user_dept department_id,
		d.is_finish
		FROM  p_plan_task_detail d
		LEFT JOIN p_plan_task pt  on pt.id = d.task_no
		LEFT JOIN p_plan  pl on  pt.plan_id=pl.id
		WHERE
		    d.create_date &gt;= '${startDate}'
		AND d.create_date &lt;= '${endDate}'
		<if test="orgCode != null and orgCode !=''" >
			And (pl.org_code LIKE CONCAT( #{orgCode}, '-%' ) or pl.org_code= #{orgCode} )
		</if>
		<if test="planId != null and planId !=''" >
			and EXISTS(select 1 from p_plan_task pt where pt.id = d.task_no and pt.plan_id = ${planId})
		</if>
		<if test="routeId != null and routeId !=''" >
			and EXISTS(select 1 from p_plan_task pt where pt.id = d.task_no and pt.route_id = ${routeId})
		</if>
		) p
		GROUP BY
		p.department_id,
		p.dateMonth) pd
		<if test="summation != 'true'">
			GROUP BY
			pd.departmentId
		</if>

	</select>


	<select id="queryWithUser" resultType="java.util.Map">
		SELECT
		<if test="summation != 'true'">
			pd.uesrId itemId,
			pd.uesrId itemName,
		</if>

		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '01' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish01,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '02' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish02,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '03' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish03,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '04' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish04,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '05' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish05,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '06' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish06,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '07' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish07,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '08' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish08,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '09' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish09,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '10' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish10,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '11' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish11,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '12' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish12,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '01' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait01,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '02' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait02,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '03' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait03,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '04' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait04,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '05' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait05,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '06' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait06,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '07' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait07,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '08' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait08,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '09' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait09,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '10' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait10,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '11' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait11,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '12' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait12
		from (
		SELECT
		p.user_id uesrId,
		p.userName,
		p.dateMonth,
		count(
		CASE
		WHEN p.is_finish = 1 THEN
		1
		ELSE
		NULL
		END
		) taskCountFinish,
		count(
		p.is_finish
		) taskCountWait
		FROM
		(
		SELECT
		DATE_FORMAT(d.create_date, '%Y') dateYear,
		DATE_FORMAT(d.create_date, '%m') dateMonth,
		d.point_id,
		d.task_no,
		pt.user_id,
		 pt.user_id userName,
		d.is_finish
		FROM
		p_plan_task_detail d
		LEFT JOIN p_plan_task pt ON pt.id = d.task_no
		<!-- LEFT JOIN s_user su ON su.id = pt.user_id -->
		WHERE  pt.user_id is NOT NULL
		<if test="orgCode != null and orgCode !=''" >
			And (pt.org_code LIKE CONCAT( #{orgCode}, '-%' ) or pt.org_code= #{orgCode} )
		</if>
		AND d.create_date &gt;= '${startDate}'
		AND d.create_date &lt;= '${endDate}'
		<if test="planId != null and planId !=''" >
			and EXISTS(select 1 from p_plan_task pt where pt.id = d.task_no and pt.plan_id = ${planId})
		</if>
		<if test="routeId != null and routeId !=''" >
			and EXISTS(select 1 from p_plan_task pt where pt.id = d.task_no and pt.route_id = ${routeId})
		</if>
		) p
		GROUP BY
		p.user_id,
		p.dateMonth)pd
		<if test="summation != 'true'">
			GROUP BY
			pd.uesrId
		</if>
	</select>


	<select id="queryWithPlan" resultType="java.util.Map">
		SELECT
		<if test="summation != 'true'">
			pd.planId itemId,
			pd.planName itemName,
		</if>
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '01' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish01,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '02' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish02,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '03' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish03,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '04' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish04,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '05' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish05,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '06' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish06,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '07' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish07,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '08' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish08,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '09' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish09,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '10' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish10,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '11' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish11,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '12' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish12,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '01' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait01,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '02' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait02,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '03' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait03,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '04' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait04,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '05' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait05,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '06' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait06,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '07' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait07,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '08' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait08,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '09' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait09,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '10' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait10,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '11' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait11,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '12' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait12
		FROM
		(
		SELECT
		p.plan_id planId,
		p.planName,
		p.dateMonth,
		count(
		CASE
		WHEN p.is_finish = 1 THEN
		1
		ELSE
		NULL
		END
		) taskCountFinish,
		count(
		p.is_finish
		) taskCountWait
		FROM
		(
		SELECT
		DATE_FORMAT(d.create_date, '%Y') dateYear,
		DATE_FORMAT(d.create_date, '%m') dateMonth,
		d.point_id,
		d.task_no,
		pt.plan_id,
		pp.`name` planName,
		d.is_finish
		FROM
			p_plan_task_detail d
			LEFT JOIN p_plan_task pt ON pt.id = d.task_no
			LEFT JOIN p_plan pp ON pp.id = pt.plan_id
		WHERE
		pp.id IS NOT NULL
		<if test="orgCode != null and orgCode !=''" >
			And (pp.org_code LIKE CONCAT( #{orgCode}, '-%' ) or pp.org_code= #{orgCode} )
		</if>
		AND d.create_date &gt;= '${startDate}'
		AND d.create_date &lt;= '${endDate}'
		<if test="planId != null and planId !=''" >
			and EXISTS(select 1 from p_plan_task pt where pt.id = d.task_no and pt.plan_id = ${planId})
		</if>
		<if test="routeId != null and routeId !=''" >
			and EXISTS(select 1 from p_plan_task pt where pt.id = d.task_no and pt.route_id = ${routeId})
		</if>
		) p
		GROUP BY
		p.plan_id,
		p.dateMonth
		) pd
		<if test="summation != 'true'">
			GROUP BY
			pd.planId
		</if>
	</select>


	<select id="queryWithRoute" resultType="java.util.Map">
		SELECT
		<if test="summation != 'true'">
			pd.routeId itemId,
			pd.routeName itemName,
		</if>
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '01' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish01,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '02' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish02,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '03' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish03,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '04' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish04,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '05' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish05,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '06' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish06,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '07' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish07,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '08' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish08,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '09' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish09,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '10' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish10,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '11' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish11,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '12' THEN
		pd.taskCountFinish
		END
		),
		0
		) taskCountFinish12,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '01' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait01,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '02' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait02,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '03' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait03,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '04' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait04,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '05' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait05,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '06' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait06,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '07' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait07,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '08' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait08,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '09' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait09,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '10' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait10,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '11' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait11,
		IFNULL(
		sum(
		CASE
		WHEN pd.dateMonth = '12' THEN
		pd.taskCountWait
		END
		),
		0
		) taskCountWait12
		FROM
		(
		SELECT
		p.route_id routeId,
		p.routeName,
		p.dateMonth,
		count(
		CASE
		WHEN p.is_finish = 1 THEN
		1
		ELSE
		NULL
		END
		) taskCountFinish,
		count(
		p.is_finish
		) taskCountWait
		FROM
		(
		SELECT
		DATE_FORMAT(d.create_date, '%Y') dateYear,
		DATE_FORMAT(d.create_date, '%m') dateMonth,
		d.point_id,
		d.task_no,
		pr.id route_id,
		pr.`name` routeName,
		d.is_finish
		FROM
		p_plan_task_detail d
		LEFT JOIN p_plan_task pt ON pt.id = d.task_no
		LEFT JOIN p_route pr ON pr.id = pt.route_id
		WHERE pr.id is not NULL
		<if test="orgCode != null and orgCode !=''" >
			And pr.org_code= #{orgCode}
		</if>
		AND d.create_date &gt;= '${startDate}'
		AND d.create_date &lt;= '${endDate}'
		<if test="planId != null and planId !=''" >
			and EXISTS(select 1 from p_plan_task pt where pt.id = d.task_no and pt.plan_id = ${planId})
		</if>
		<if test="routeId != null and routeId !=''" >
			and EXISTS(select 1 from p_plan_task pt where pt.id = d.task_no and pt.route_id = ${routeId})
		</if>
		) p
		GROUP BY
		p.route_id,
		p.dateMonth
		) pd
		<if test="summation != 'true'">
			GROUP BY
			pd.routeId
		</if>
	</select>


	<select id="comprehensiveStatisticalForUser" resultType="java.util.Map">
		<!-- SELECT
		<if test="summation == 'false'">
			pl.userId,
			(
			SELECT
			name
			FROM
			s_user su
			WHERE
			su.id = pl.userId
			) userName,
			(
			SELECT
			(
			SELECT
			sd.department_name
			FROM
			s_department sd
			WHERE
			sd.id = su.department_id
			)
			FROM
			s_user su
			WHERE
			su.id = pl.userId
			) deptName,
		</if>

	pl.counPointToCheck,
	pl.countPointFinish,
	pl.uncheckedPointCount,
	pl.finishedPointCount,
	pl.notFinishTaskCount,
	pl.notQualifiedPointCount,
	pl.finishTaskCount,
	pl.planTaskCount,
	IFNULL(pl.score, 0) score,
	IFNULL(pl.actCheckPointCount, 0) actCheckPointCount,
	IFNULL(pl.doTaskCount, 0) doTaskCount,
	CONCAT(
		ROUND(
			(
				IFNULL(
					pl.uncheckedPointCount / pl.counPointToCheck,
					0
				) * 100
			),
			2
		),
		'%'
	) uncheckedPointRate,
	CONCAT(
		ROUND(
			(
				IFNULL(
					pl.notQualifiedPointCount / pl.counPointToCheck,
					0
				) * 100
			),
			2
		),
		'%'
	) notQualifiedPointCountRate,
	CONCAT(
		ROUND(
			(
				IFNULL(
					pl.finishTaskCount / pl.planTaskCount,
					0
				) * 100
			),
			2
		),
		'%'
	) finishTaskRate
FROM
	(
		SELECT
			pt.user_id userId,
			sum(pt.point_num) counPointToCheck,
			sum(pt.finish_num) countPointFinish,
			sum((
				SELECT
					count(1)
				FROM
					p_plan_task_detail pd
				WHERE
					pd.task_no = pt.id
				AND pd.is_finish = 2
			)) uncheckedPointCount,
			sum((
				SELECT
					count(1)
				FROM
					p_plan_task_detail pd
				WHERE
					pd.task_no = pt.id
				AND pd.is_finish = 1
			)) finishedPointCount,
			sum((
				SELECT
					count(1)
				FROM
					p_plan_task_detail pd
				WHERE
					pd.task_no = pt.id
				AND pd.`status` != 1
			)) notQualifiedPointCount,
			sum(
				CASE
				WHEN pt.finish_status = 2 THEN
					1
				ELSE
					0
				END
			) finishTaskCount,
			sum(
				CASE
				WHEN pt.finish_status != 2 THEN
					1
				ELSE
					0
				END
			) notFinishTaskCount,
			count(1) planTaskCount,
			sum((
				SELECT
					sum(pc.score) score
				FROM
					p_check pc
				WHERE
					pc.plan_task_id = pt.id
			) )score,
			sum((
				SELECT
					count(DISTINCT pc.point_id)
				FROM
					p_check pc
				WHERE
					pc.plan_task_id = pt.id
					and pc.is_ok !=3
			)) actCheckPointCount,
			sum((
				SELECT
					count(DISTINCT pc.plan_task_id)
				FROM
					p_check pc
				WHERE
					pc.plan_task_id = pt.id
					and pc.is_ok !=3
			)) doTaskCount
		FROM
			p_plan_task pt
		WHERE
			pt.end_time BETWEEN '${startTime}'
		AND '${endTime}'

		<if test="summation == 'false'">
			GROUP BY
			pt.user_id
		</if>

	) pl -->
	SELECT
        <if test="summation == 'false'">
            pl.userId,
			pl.user_dept,
        </if>
        pl.counPointToCheck,
        pl.countPointFinish,
        pl.uncheckedPointCount,
        pl.finishedPointCount,
        pl.notFinishTaskCount,
        pl.notQualifiedPointCount,
        pl.finishTaskCount,
        pl.planTaskCount,
        IFNULL(pl.score, 0) score,
        IFNULL(pl.actCheckPointCount, 0) actCheckPointCount,
        IFNULL(pl.doTaskCount, 0) doTaskCount,
        CONCAT(
        ROUND(
        (
        IFNULL(
        pl.uncheckedPointCount / pl.counPointToCheck,
        0
        ) * 100
        ),
        2
        ),
        '%'
        ) uncheckedPointRate,
        CONCAT(
        ROUND(
        (
        IFNULL(
        pl.notQualifiedPointCount / pl.counPointToCheck,
        0
        ) * 100
        ),
        2
        ),
        '%'
        ) notQualifiedPointCountRate,
        CONCAT(
        ROUND(
        (
        IFNULL(
        pl.finishTaskCount / pl.planTaskCount,
        0
        ) * 100
        ),
        2
        ),
        '%'
        ) finishTaskRate
        FROM
        (
        SELECT
        pt.user_id userId,
		pt.user_dept,
        sum(pt.point_num) counPointToCheck,
        sum(pt.finish_num) countPointFinish,
        sum((
        SELECT
        count(1)
        FROM
        p_plan_task_detail pd
        WHERE
        pd.task_no = pt.id
        AND pd.is_finish = 2
        )) uncheckedPointCount,
        sum((
        SELECT
        count(1)
        FROM
        p_plan_task_detail pd
        WHERE
        pd.task_no = pt.id
        AND pd.is_finish = 1
        )) finishedPointCount,
        sum((
        SELECT
        count(1)
        FROM
        p_plan_task_detail pd
        WHERE
        pd.task_no = pt.id
        AND pd.`status` != 1
        )) notQualifiedPointCount,
        sum(
        CASE
        WHEN pt.finish_status = 2 THEN
        1
        ELSE
        0
        END
        ) finishTaskCount,
        sum(
        CASE
        WHEN pt.finish_status != 2 THEN
        1
        ELSE
        0
        END
        ) notFinishTaskCount,
        count(1) planTaskCount,
        sum((
        SELECT
        sum(pc.score) score
        FROM
        p_check pc
        WHERE
        pc.plan_task_id = pt.id
        ) )score,
        sum((
        SELECT
        count(DISTINCT pc.point_id)
        FROM
        p_check pc
        WHERE
        pc.plan_task_id = pt.id
        and pc.is_ok !=3
        )) actCheckPointCount,
        sum((
        SELECT
        count(DISTINCT pc.plan_task_id)
        FROM
        p_check pc
        WHERE
        pc.plan_task_id = pt.id
        and pc.is_ok !=3
        )) doTaskCount
        FROM
        p_plan_task pt
        WHERE
         pt.user_id >0
        AND pt.end_time BETWEEN '${startTime}'
        AND '${endTime}'

        <if test="summation == 'false'">
            GROUP BY
            pt.user_id
        </if>

        ) pl
	</select>


</mapper>