<?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.xintong.visualinspection.dao.master.StatisticsDao" >
    
    <select id="selectStatistics" parameterType="com.xintong.visualinspection.bean.StatisticsBean" resultType="com.xintong.visualinspection.bean.StatisticsBean">
    	SELECT  u.checked_person AS user_id , u.checked_dept AS dept_id ,u.parent_id AS item_id ,sum(u.score) AS score ,s.name  FROM (
			SELECT t.checked_person , t.checked_dept,ci.parent_id, ci.score  FROM check_score t 
			LEFT JOIN check_item ci ON t.check_item_id = ci.id
			WHERE 1=1 
			<if test="start_date!=null and end_date != null">
		        AND	 t.update_time &gt;= #{start_date} AND t.update_time &lt;= #{end_date} 
			</if>
			<if test="dept_id!=null">
				AND t.checked_dept =#{dept_id}			
			</if>
			 ) 
			u LEFT JOIN check_item s ON u.parent_id = s.id GROUP  BY checked_person,id
    </select>

    <select id="selectCheckedTimes" parameterType="com.xintong.visualinspection.bean.StatisticsBean" resultType="com.xintong.visualinspection.bean.StatisticsBean">
    	SELECT COUNT(1) AS checked_num , t.checked_person AS user_id FROM check_task t 
    	WHERE 1=1 
			<if test="start_date!=null and end_date != null">
		        AND	 t.start_time &gt;= #{start_date} AND t.end_time &lt;= #{end_date} 
			</if>
			<if test="dept_id!=null">
				AND t.checked_dept =#{dept_id}			
			</if>
    	GROUP BY t.checked_person
    </select>
    
    <select id="selectFeeStationCheckNum" parameterType="com.xintong.visualinspection.bean.StatisticsBean" resultType="com.xintong.visualinspection.bean.StatisticsBean">
    	SELECT COUNT(DISTINCT( t.checked_person )) AS checked_people_num , COUNT(t.checked_person) AS checked_num  ,t.checked_dept AS dept_id FROM check_task  t
    	WHERE 1=1
    		<if test="start_date!=null and end_date != null">
		        AND	 t.start_time &gt;= #{start_date} AND t.end_time &lt;= #{end_date} 
			</if>
    	 GROUP BY t.checked_dept
    </select>
    
    <select id="selectFeeStationCheckedScore" parameterType="com.xintong.visualinspection.bean.StatisticsBean" resultType="com.xintong.visualinspection.bean.StatisticsBean">
    	SELECT sum(c.score) AS score ,t.checked_dept AS dept_id 
    		FROM check_score t LEFT JOIN check_item c ON t.check_item_id = c.id
    		WHERE 1=1
    		<if test="start_date!=null and end_date != null">
		        AND	 t.update_time &gt;= #{start_date} AND t.update_time &lt;= #{end_date} 
			</if>
    		GROUP BY t.checked_dept,t.checked_person
    </select>
    
    <select id="selectFeeStationCheckedPersonScoreDetail" parameterType="com.xintong.visualinspection.bean.StatisticsBean" resultType="com.xintong.visualinspection.bean.StatisticsBean">
    	    SELECT  u.checked_dept AS dept_id ,u.parent_id AS item_id ,sum(u.score) AS score ,s.name  FROM (
			SELECT  t.checked_dept,ci.parent_id, ci.score  FROM check_score t 
			LEFT JOIN check_item ci ON t.check_item_id = ci.id
			WHERE 1=1
			<if test="start_date!=null and end_date != null">
		        AND	 t.update_time &gt;= #{start_date} AND t.update_time &lt;= #{end_date} 
			</if> )
			u LEFT JOIN check_item s ON u.parent_id = s.id GROUP  BY dept_id,item_id
    </select>
       
</mapper>