<?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.QueryStatsMapper">
    <!-- 根据参数获取巡检统计信息 -->
    <select id="getCheckStats" resultType="com.yeejoin.amos.patrol.business.vo.CheckStatsVo">
	    SELECT
		    pointId AS id,
		    pointName AS name,
		    COUNT(c.id) AS real_check_num,
		    SUM(c.score) AS score,
		    t2.plan_check_num,
		    t3.plan_finish_num,
		    t2.plan_check_num - t3.plan_finish_num AS omissionNum
		FROM
		    (
		        SELECT
		            p.id AS pointId,
		            p.`name` AS pointName
		        FROM
		            p_point p
		        LEFT JOIN p_plan_task_detail ptd ON ptd.point_id = p.id
		        LEFT JOIN p_check c ON c.point_id = p.id
		        GROUP BY
		            p.id
		    ) t1
		LEFT JOIN (
		    SELECT
		        ptd.point_id,
		        COUNT(ptd.point_id) AS plan_check_num
		    FROM
		        p_plan_task_detail ptd
		    GROUP BY
		        ptd.point_id
		) t2 ON t2.point_id = t1.pointId
		LEFT JOIN (
		    SELECT
		        ptd.point_id,
		        COUNT(ptd.point_id) AS plan_finish_num
		    FROM
		        p_plan_task_detail ptd
		    WHERE
		        ptd.is_finish = 2
		    GROUP BY
		        ptd.point_id
		) t3 ON t3.point_id = t1.pointId
		LEFT JOIN p_check c ON c.point_id = t1.pointId
		GROUP BY t1.pointId;
    </select>
    
</mapper>