<?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">

    <sql id="deletedUser">
        (select t_s_y.id,date_sub(t_p.starttime,interval 1 hour) as starttime from t_sys_users t_s_y
        LEFT join (select starttime,endtime from check_task_period ) t_p on DELETE_TIME &gt;= starttime and DELETE_TIME
        &lt; endtime
        WHERE STATUS =-1 and DELETE_TIME &gt;= t_p.starttime and DELETE_TIME &lt; t_p.endtime ) deleted_user
    </sql>

    <sql id="taskexclouddeleteUser">


        ( select id from ( select check_task.*,deleted_user.starttime as u_delet_time from check_task left join
        <include refid="deletedUser"/>
        on check_task.checked_person = deleted_user.id ) v_task where v_task.u_delet_time is not null and
        v_task.end_time &gt; v_task.u_delet_time
        )

    </sql>

    <sql id="classexclouddeleteUser">


        (select id from ( select sys_user_class.*,deleted_user.starttime as u_delet_time from sys_user_class left join
        <include refid="deletedUser"/>
        on sys_user_class.user_id = deleted_user.id ) v_user_class where v_user_class.u_delet_time is not null and
        v_user_class.end_time &gt; v_user_class.u_delet_time
        )

    </sql>

    <sql id="unregularclassexclouddeleteUser">


        (select id from ( select sys_user_class_unregular.*,deleted_user.starttime as u_delet_time from
        sys_user_class_unregular left join
        <include refid="deletedUser"/>
        on sys_user_class_unregular.user_id = deleted_user.id ) v_user_class where v_user_class.u_delet_time is not null
        and v_user_class.end_time &gt; v_user_class.u_delet_time
        )

    </sql>

    <select id="selectWreckerOrgan" resultType="com.xintong.visualinspection.bean.WreckerGroup">
        SELECT ORGANID AS deptid,COUNT(1) AS people_num ,t.name FROM t_sys_users o
        LEFT JOIN t_br_layer_wrecker_group t ON o.ORGANID = t.ORGAN_ID
        WHERE o.STATUS = 1 AND o.ORGANID IN
        (
        SELECT organ_id FROM t_br_layer_wrecker_group
        )
        GROUP BY o.ORGANID
    </select>

    <select id="selectWreckerInfo" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        SELECT t.* , m.code_name AS name FROM
        (
        SELECT
        COUNT(1) AS checked_num , dept_id ,COUNT(DISTINCT user_id) AS checked_people_num,
        wrecker_check_type,
        sum(check_score) AS all_check_score
        FROM
        other_check
        WHERE
        check_type = 5
        <if test="start_date!=null and end_date != null">
            AND check_time &gt;= #{start_date} AND check_time &lt; #{end_date}
        </if>
        GROUP BY
        wrecker_check_type,dept_id
        ) t LEFT JOIN ( SELECT * FROM sys_code where code_flag = 'wrecker_group_check_type' ) m ON t.wrecker_check_type
        = m.code_value
    </select>

    <select id="selectBFStatistics" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        SELECT y.user_id,y.dept_id,IFNULL(x.checked_num,0)+IFNULL(o1.check_a_num,0)
        checked_num,IFNULL(x.score,0)+IFNULL(o1.check_a_score,0)
        score,o2.check_t_num,o2.check_t_score,o3.check_x_num,o3.check_x_score,o4.check_s_num,o4.check_s_score
        ,IFNULL(x.checked_num,0)+IFNULL(o1.check_a_num,0)+IFNULL(o2.check_t_num,0)+IFNULL(o3.check_x_num,0)+IFNULL(o4.check_s_num,0)
        check_all_num
        ,IFNULL(x.score,0)+IFNULL(o1.check_a_score,0)+IFNULL(o2.check_t_score,0)+IFNULL(o3.check_x_score,0)+IFNULL(o4.check_s_score,0)
        check_all_score
        FROM

        ( select ORGANID AS dept_id , id AS user_id from t_sys_users t where 1=1
        <if test="position_ids != null and position_ids.size() > 0">
            AND t.positionid in
            <foreach collection="position_ids" item="position_id" index="index"
                     open="(" close=")" separator=",">
                #{position_id}
            </foreach>
        </if>
        <if test="dept_id!=null">
            AND t.ORGANID =#{dept_id}
        </if>
        ) y LEFT JOIN
        (
        SELECT m.* , t.checked_num FROM
        (
        SELECT COUNT(1) AS checked_num , t.checked_person AS user_id FROM check_task t
        WHERE 1=1 and t.check_status!=22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <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_person
        ) t LEFT JOIN (
        SELECT u.checked_person AS user_id , u.checked_dept AS dept_id , sum(u.score) AS score FROM (
        SELECT IFNULL(t.checked_person, ct.checked_person ) AS checked_person , IFNULL(t.checked_dept,ct.checked_dept)
        AS checked_dept ,
        IFNULL(t.check_item_score,0) AS score FROM check_task ct
        LEFT JOIN check_score t ON ct.id = t.task_id
        WHERE 1=1 and ct.check_status!=22 and ct.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="start_date!=null and end_date != null">
            AND ct.start_time &gt;= #{start_date} AND ct.end_time &lt; #{end_date}
        </if>
        <!--<if test="dept_id!=null">-->
            <!--AND ct.checked_dept =#{dept_id}-->
        <!--</if>-->
        )
        u GROUP BY checked_person
        ) m ON t.user_id = m.user_id
        ) x
        ON y.user_id = x.user_id

        LEFT JOIN (SELECT user_id,dept_id,COUNT(1) check_a_num,SUM(check_score) check_a_score FROM other_check WHERE
        check_type = 1
        <if test="start_date!=null and end_date != null">
            AND check_time &gt;= #{start_date} AND check_time &lt; #{end_date}
        </if>
        GROUP BY user_id,dept_id) o1 ON o1.user_id = y.user_id
        LEFT JOIN (SELECT user_id,dept_id,COUNT(1) check_t_num,SUM(check_score) check_t_score FROM other_check WHERE
        check_type = 2
        <if test="start_date!=null and end_date != null">
            AND check_time &gt;= #{start_date} AND check_time &lt; #{end_date}
        </if>
        GROUP BY user_id,dept_id) o2 ON o2.user_id = y.user_id
        LEFT JOIN (SELECT user_id,dept_id,COUNT(1) check_x_num,SUM(check_score) check_x_score FROM other_check WHERE
        check_type = 3
        <if test="start_date!=null and end_date != null">
            AND check_time &gt;= #{start_date} AND check_time &lt; #{end_date}
        </if>
        GROUP BY user_id,dept_id) o3 ON o3.user_id = y.user_id
        LEFT JOIN (SELECT user_id,dept_id,COUNT(1) check_s_num,SUM(check_score) check_s_score FROM other_check WHERE
        check_type = 4
        <if test="start_date!=null and end_date != null">
            AND check_time &gt;= #{start_date} AND check_time &lt; #{end_date}
        </if>
        GROUP BY user_id,dept_id) o4 ON o4.user_id = y.user_id


    </select>


    <select id="selectuserchangestation" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.UserChangeStation">

        select a.checked_person as userid,
        checked_dept as old_dept,
        a.dept as new_dept ,
        (select TRUENAME from t_sys_users where a.checked_person=id) as username,
        (select ORGANNAME from t_sys_organ where a.checked_dept=id) as old_dept_name,
        (select ORGANNAME from t_sys_organ where a.dept=id) as new_dept_name,
        (select count(*) from check_task where checked_person=a.checked_person and checked_dept=a.checked_dept
        <if test="start_date!=null and end_date != null">
            AND start_time &gt;= #{start_date} AND end_time &lt; #{end_date}
        </if>
         and check_status!=22) as old_dept_check_num,
        (select count(*) from check_task where checked_person=a.checked_person and checked_dept=a.dept

        <if test="start_date!=null and end_date != null">
            AND start_time &gt;= #{start_date} AND end_time &lt; #{end_date}
        </if>

        and check_status!=22) as new_dept_check_num

        from (

        select checked_person,checked_dept,(select u.ORGANID from t_sys_users u where u.id = t.checked_person) as dept
        from check_task t where 1=1

        <if test="start_date!=null and end_date != null">
            AND start_time &gt;= #{start_date} AND end_time &lt; #{end_date}
        </if>

        GROUP BY checked_person,checked_dept ORDER BY checked_person

        ) a where a.checked_dept!=a.dept
    </select>

    <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, t.check_item_score AS score FROM check_score t
        LEFT JOIN check_item ci ON t.check_item_id = ci.id
        LEFT JOIN check_task ct ON ct.id = t.task_id
        WHERE 1=1 and ct.check_status!=22 and ct.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="start_date!=null and end_date != null">
            AND ct.start_time &gt;= #{start_date} AND ct.end_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="selectMList" parameterType="com.xintong.visualinspection.pojo.TaskAndScore"
            resultType="com.xintong.visualinspection.pojo.TaskAndScore">

        SELECT a.*,b.id as id1 ,b.update_time as
        update_time1,b.content,b.pics,b.videos,b.task_id,b.check_item_id,b.checkman as checkman1,b.checked_person as
        checked_person1 ,b.checked_dept as checked_dept1,b.remark as remark1,b.check_item_score from check_task a LEFT
        JOIN check_score b on a.id=b.task_id where
        a.monitor_user_id != -1 and a.monitor_user_id != 0 and
        a.start_time &gt;= #{start_time} and a.end_time &lt; #{end_time} and a.check_status !=22 and a.id not in
        <include refid="taskexclouddeleteUser"/>

    </select>


    <select id="selectCheckedNum" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="java.lang.Integer">

        select sum(num) AS num from (
        SELECT COUNT(DISTINCT ct.checked_person) as num FROM check_task ct
        WHERE ct.check_status != 22 and ct.id not in
        <include refid="taskexclouddeleteUser"/>
        AND  ct.id not in <include refid="selectExuserTask"/>
        <if test="dept_id!=null">
            AND ct.checked_dept = #{dept_id}
        </if>
        <if test="dept_list!=null and queryType == 1 and dept_list.size()>0 ">
            AND ct.checked_dept IN
            <foreach item="obj" collection="dept_list" open="("
                     separator="," close=")">
                #{obj.id}
            </foreach>
        </if>
        <if test="mth!=null and mth!=''">
            AND monthfunc (ct.start_time) = #{mth}
            AND monthfunc (ct.end_time) = #{mth}
        </if>
        GROUP BY ct.checked_dept
        ) u
    </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 and t.check_status!=22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <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 left join t_sys_users u on t.checked_person = u.id
        WHERE 1=1 and t.check_status!=22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <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="position_ids != null and position_ids.size() > 0">
            AND u.positionid in
            <foreach collection="position_ids" item="position_id" index="index"
                     open="(" close=")" separator=",">
                #{position_id}
            </foreach>
        </if>

        <if test="period_id !=null ">
            AND t.checked_person not in (select user_id from check_statistic_exclude_users where period_id = #{period_id})
        </if>
        GROUP BY t.checked_dept
    </select>

    <select id="getselectFeeStationOperationScoreDetail"
            parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        SELECT
        o.dept_id,
        COUNT(1) checked_num,
        SUM(o.check_score) check_total_score
        FROM
        other_check o
        WHERE
        o.check_type = #{check_type}

        <if test="check_type!=2 ">
            AND user_id = 0
        </if>

        <if test="start_date!=null and end_date != null">
            AND o.check_time &gt;= #{start_date} AND o.check_time &lt; #{end_date}
        </if>
        GROUP BY
        dept_id
    </select>
    <select id="selectFeeStationCheckedScore" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        SELECT IFNULL(sum(ct.check_item_score),0) AS score ,t.checked_dept AS dept_id ,t.checked_person AS user_id
        FROM check_task t
        LEFT JOIN check_score ct ON t.id = ct.task_id
        WHERE 1=1 and t.check_status!=22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        AND  t.id not in <include refid="selectExuserTask"/>
        <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="period_id !=null ">
            AND t.checked_person not in (select user_id from check_statistic_exclude_users where period_id = #{period_id})
        </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,s.id FROM (
        SELECT t.checked_dept,ci.parent_id, t.check_item_score AS score FROM check_score t
        LEFT JOIN check_item ci ON t.check_item_id = ci.id
        LEFT JOIN check_task ct ON ct.id = t.task_id
        LEFT JOIN t_sys_users uu on t.checked_person = uu.id
        WHERE 1=1 AND ct.check_status!=22 AND t.check_item_score is not null and ct.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="start_date!=null and end_date != null">
            AND ct.start_time &gt;= #{start_date} AND ct.end_time &lt; #{end_date}
        </if>
        <if test="position_ids != null and position_ids.size() > 0">
            AND uu.positionid in
            <foreach collection="position_ids" item="position_id" index="index"
                     open="(" close=")" separator=",">
                #{position_id}
            </foreach>
        </if>

        <if test="period_id !=null ">
            AND t.checked_person not in (select user_id from check_statistic_exclude_users where period_id = #{period_id})
        </if>
        )
        u LEFT JOIN check_item s ON u.parent_id = s.id GROUP BY dept_id,item_id
    </select>

    <sql id="selectAllCheckedInfo">
        SELECT
        check_num_t.checked_num , checked_score_t.mth, checked_score_t.checkd_all_score ,
        checked_score_t.checked_person,checked_score_t.checked_dept
        FROM
        (
        SELECT
        monthfunc (t.start_time) AS mth,
        sum(m.check_item_score) AS checkd_all_score, m.*
        FROM
        check_score m INNER JOIN
        ( select * From check_task t
        where t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        AND  t.id not in <include refid="selectExuserTask"/>
        <if test="start_date !=null">
            AND t.start_time >= #{start_date}
        </if>
        <if test="end_date !=null">
            AND t.end_time &lt; #{end_date}
        </if>
        <if test="mth!=null and mth!=''">
            AND monthfunc (t.start_time) =#{mth}
        </if>
        ) t
        ON m.task_id = t.id
        WHERE 1=1
        GROUP BY mth , m.checked_person

        ) checked_score_t
        LEFT JOIN (
        SELECT
        COUNT(t.checked_person) AS checked_num,
        t.checked_person,
        monthfunc (t.start_time) AS mth,
        t.id
        FROM
        check_task t
        WHERE
        t.checked_person IS NOT NULL
        AND t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        AND  t.id not in <include refid="selectExuserTask"/>
        <if test="start_date !=null">
            AND t.start_time >= #{start_date}
        </if>
        <if test="end_date !=null">
            AND t.end_time &lt; #{end_date}
        </if>
        <if test="mth!=null and mth!=''">
            AND monthfunc (t.start_time) =#{mth}
        </if>
        GROUP BY
        t.checked_person,
        mth
        ) check_num_t ON checked_score_t.checked_person = check_num_t.checked_person
        AND check_num_t.mth = checked_score_t.mth
    </sql>


    <!--包含未扣分的-->
    <sql id="selectAllCheckedInfoAllCheck">
        SELECT
        check_num_t.checked_num , check_num_t.mth, IFNULL(checked_score_t.checkd_all_score ,0) as checkd_all_score,
        check_num_t.checked_person,check_num_t.checked_dept
        FROM

        (
        SELECT
        COUNT(t.checked_person) AS checked_num,
        t.checked_person,
        monthfunc (t.start_time) AS mth,
        t.id,t.checked_dept
        FROM
        check_task t
        WHERE
        t.checked_person IS NOT NULL
        AND t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        AND  t.id not in <include refid="selectExuserTask"/>
        <if test="start_date !=null">
            AND t.start_time >= #{start_date}
        </if>
        <if test="end_date !=null">
            AND t.end_time &lt; #{end_date}
        </if>
        <if test="mth!=null and mth!=''">
            AND monthfunc (t.start_time) =#{mth}
        </if>
        GROUP BY
        t.checked_person,
        t.checked_dept,
        mth
        ) check_num_t

        LEFT JOIN (
        SELECT
        monthfunc (t.start_time) AS mth,
        sum(m.check_item_score) AS checkd_all_score, m.*
        FROM
        check_score m INNER JOIN
        ( select * From check_task t
        where t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="start_date !=null">
            AND t.start_time >= #{start_date}
        </if>
        <if test="end_date !=null">
            AND t.end_time &lt; #{end_date}
        </if>
        <if test="mth!=null and mth!=''">
            AND monthfunc (t.start_time) =#{mth}
        </if>
        ) t
        ON m.task_id = t.id
        WHERE 1=1
        GROUP BY mth , m.checked_person,m.checked_dept

        ) checked_score_t ON (checked_score_t.checked_person = check_num_t.checked_person and
        checked_score_t.checked_dept = check_num_t.checked_dept)
        AND check_num_t.mth = checked_score_t.mth
    </sql>

    <select id="selectYearScoreInfo" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        SELECT sum(tab.checkd_all_score) AS all_check_score , sum(tab.checked_num) AS checked_num , tab.mth FROM (
        <include refid="selectAllCheckedInfoAllCheck"/>
        ) tab
        WHERE 1=1
        <if test="dept_list!=null and queryType == 1 and dept_list.size()>0 ">
            AND tab.checked_dept IN
            <foreach item="obj" collection="dept_list" open="("
                     separator="," close=")">
                #{obj.id}
            </foreach>
        </if>
        <if test="dept_id!=null and queryType ==2">
            AND tab.checked_dept=#{dept_id}
        </if>
        GROUP BY tab.mth
    </select>

    <select id="selectFeeStationScoreInfo" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        SELECT sum(tab.checkd_all_score) AS all_check_score , sum(tab.checked_num) AS checked_num ,tab.checked_dept AS
        dept_id FROM (
        <include refid="selectAllCheckedInfoAllCheck"/>
        ) tab
        WHERE 1=1
        GROUP BY tab.checked_dept
    </select>


    <sql id="selectEveryCheckedInfo">
        SELECT SUM(sc.check_item_score) AS checkd_all_score , t.checked_person ,t.checked_dept FROM check_task t
        INNER JOIN check_score sc ON sc.task_id = t.id
        WHERE monthfunc( t.start_time ) = #{mth} and t.id not in
        <include refid="taskexclouddeleteUser"/>
        GROUP BY t.checked_person
    </sql>

    <select id="selectCheckedScoreList" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        SELECT tab.checkd_all_score AS all_check_score,tab.checked_person AS user_id , tab.checked_dept AS dept_id
        ,tab.checked_num
        FROM (
        <include refid="selectAllCheckedInfoAllCheck"/>
        ) tab
        WHERE 1=1
        <if test="dept_list!=null and queryType == 1 and dept_list.size()>0 ">
            AND tab.checked_dept IN
            <foreach item="obj" collection="dept_list" open="("
                     separator="," close=")">
                #{obj.id}
            </foreach>
        </if>
        <if test="dept_id!=null and queryType ==2">
            AND tab.checked_dept=#{dept_id}
        </if>
    </select>

    <select id="selectfeestationChekcedSocre" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        select tso.parentid , ci.name AS check_item_name ,sum(t.check_item_score) AS all_check_score, t.checked_dept
        FROM
        ( SELECT ci.parent_id, t.* from check_score t INNER JOIN check_task ct ON ct.check_status !=22 and ct.id not in
        <include refid="taskexclouddeleteUser"/>
        AND monthfunc( ct.start_time ) = '2017/07' AND ct.id = t.task_id
        LEFT JOIN check_item ci ON t.check_item_id = ci.id ) t
        LEFT JOIN check_item ci ON t.parent_id = ci.id
        LEFT JOIN t_sys_organ tso ON tso.id = t.checked_dept
        <if test="queryType!=null and queryType == 0">
            GROUP BY t.parent_id , tso.parentid
            order BY t.parent_id , tso.parentid
        </if>
        <if test="queryType!=null and queryType == 1">
            GROUP BY t.parent_id , t.checked_dept
            ORDER BY t.parent_id , t.checked_dept
        </if>
    </select>


    <select id="selectCheckedItemScoreInfo" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        SELECT count(DISTINCT m.task_id) AS checked_num , sum(m.check_item_score) AS all_check_score ,
        m.parent_id,m.checked_dept as dept_id,(select parentid from t_sys_organ where id=m.checked_dept ) as
        parent_dept_id,m.check_item_name AS name FROM check_task t INNER JOIN
        (
        select ci.name AS check_item_name , t.* FROM
        ( SELECT ci.parent_id, t.* from check_score t LEFT JOIN check_item ci ON t.check_item_id = ci.id
        WHERE 1=1
        <if test="dept_list!=null and queryType == 1 and dept_list.size()>0 ">
            AND t.checked_dept IN
            <foreach item="obj" collection="dept_list" open="("
                     separator="," close=")">
                #{obj.id}
            </foreach>
        </if>
        <if test="dept_id!=null and queryType ==2">
            AND t.checked_dept=#{dept_id}
        </if>
        ) t
        LEFT JOIN check_item ci ON t.parent_id = ci.id
        ) m ON t.id = m.task_id
        where t.check_status !=22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        AND  t.id not in <include refid="selectExuserTask"/>
        AND monthfunc( t.start_time ) = #{mth}
        GROUP BY m.parent_id,m.checked_dept order by m.checked_dept
    </select>


    <!--包含扣分-->
    <sql id="selectOneAllCheckedInfoData">
        SELECT
        check_num_t.checked_num , check_num_t.mth, IFNULL(checked_score_t.checkd_all_score ,0) as all_check_score,
        check_num_t.checked_person,check_num_t.checked_dept
        FROM

        (
        SELECT
        COUNT( t.id) AS checked_num,
        t.checked_person,
        monthfunc (t.start_time) AS mth,t.checked_dept
        FROM
        check_task t
        WHERE
        t.checked_person IS NOT NULL
        AND t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="start_date !=null">
            AND t.start_time &gt;= #{start_date}
        </if>
        <if test="end_date !=null">
            AND t.end_time &lt; #{end_date}
        </if>
        <if test="user_id !=null ">
            AND t.checked_person = #{user_id}
        </if>
        GROUP BY mth
        ) check_num_t

        LEFT JOIN (
        SELECT
        monthfunc (t.start_time) AS mth,
        sum(m.check_item_score) AS checkd_all_score,m.*
        FROM
        check_score m INNER JOIN
        ( select * From check_task t
        where t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="start_date !=null">
            AND t.start_time &gt;= #{start_date}
        </if>
        <if test="end_date !=null">
            AND t.end_time &lt; #{end_date}
        </if>
        ) t
        ON m.task_id = t.id
        WHERE 1=1
        <if test="user_id !=null ">
            AND m.checked_person = #{user_id}
        </if>
        GROUP BY mth

        ) checked_score_t ON checked_score_t.checked_person = check_num_t.checked_person
        AND check_num_t.mth = checked_score_t.mth
    </sql>


    <sql id="selectOneCheckedInfo">
        SELECT
        check_num_t.checked_num , checked_score_t.mth, checked_score_t.checkd_all_score AS all_check_score ,
        checked_score_t.checked_person,checked_score_t.checked_dept
        FROM
        (
        SELECT
        monthfunc (t.start_time) AS mth,
        sum(m.check_item_score) AS checkd_all_score,m.*
        FROM
        check_score m INNER JOIN
        ( select * From check_task t
        where t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="start_date !=null">
            AND t.start_time &gt;= #{start_date}
        </if>
        <if test="end_date !=null">
            AND t.end_time &lt; #{end_date}
        </if>
        ) t
        ON m.task_id = t.id
        WHERE 1=1
        <if test="user_id !=null ">
            AND m.checked_person = #{user_id}
        </if>
        GROUP BY mth

        ) checked_score_t
        LEFT JOIN (
        SELECT
        COUNT( t.id) AS checked_num,
        t.checked_person,
        monthfunc (t.start_time) AS mth
        FROM
        check_task t
        WHERE
        t.checked_person IS NOT NULL
        AND t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="start_date !=null">
            AND t.start_time &gt;= #{start_date}
        </if>
        <if test="end_date !=null">
            AND t.end_time &lt; #{end_date}
        </if>
        <if test="user_id !=null ">
            AND t.checked_person = #{user_id}
        </if>
        GROUP BY mth
        ) check_num_t ON checked_score_t.checked_person = check_num_t.checked_person
        AND check_num_t.mth = checked_score_t.mth
    </sql>

    <select id="selectOneCheckedInfoData" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        SELECT * FROM (
        <include refid="selectOneAllCheckedInfoData"/>
        ) t
    </select>

    <select id="selectOneCheckedItemInfo" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">

        select a.*,(select truename from t_sys_users where id = a.user_id LIMIT 1 ) as checked_name from (

        SELECT
        count(DISTINCT t.id) AS checked_num ,
        sum(cs.check_item_score) AS all_check_score ,
        (select parent_id from check_item where check_item.id = cs.check_item_id LIMIT 1) as parent_id,
        ( select name from check_item where id = (select parent_id from check_item where check_item.id =
        cs.check_item_id LIMIT 1)) AS NAME,
        cs.checked_person as user_id
        FROM
        check_task t

        left join check_score cs on (cs.task_id = t.id )

        WHERE
        t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        and (cs.checked_person = #{user_id} )
        <if test="start_date !=null">
            AND t.start_time >= #{start_date}
        </if>
        <if test="end_date !=null">
            AND t.end_time &lt; #{end_date}
        </if>

        GROUP BY
        parent_id,user_id

        ) a where a.all_check_score is not null

    </select>

    <select id="selectOneCheckedDetailInfo" parameterType="com.xintong.visualinspection.bean.StatisticsBean"
            resultType="com.xintong.visualinspection.bean.StatisticsBean">
        SELECT t.name AS check_task_name , t.start_time AS start_date , t.end_time AS end_date,cs.check_item_score AS
        score , ci.name AS name,ci.parent_name, cs.pics ,t.checkman AS checkman_id,
        cs.checked_person AS user_id ,t.checked_dept AS dept_id
        <if test="queryType!=null and queryType==1">
            ,cst.update_time,t.id as task_id
        </if>

        FROM
        <if test="queryType!=null and queryType==0">
            check_task
        </if>

        <if test="queryType==null or (queryType!=null and queryType==1) ">
            v_check_task
        </if>
        t
        LEFT JOIN check_score cs ON t.id = cs.task_id

        <if test="queryType!=null and queryType==1">
            left join ( select task_id,max(update_time) as update_time from check_task_status where check_status =4 or
            check_status = 14 GROUP BY task_id ) cst on t.id = cst.task_id
        </if>

        LEFT JOIN (SELECT ci.* ,cp.name AS parent_name FROM check_item ci LEFT JOIN check_item cp ON ci.parent_id =
        cp.id) ci ON ci.id = cs.check_item_id
        where t.check_status !=22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="queryType!=null and queryType==0">
            AND ((t.checked_person = #{user_id} and cs.checked_person = #{user_id}) )
        </if>
        <if test="queryType!=null and queryType==1 and user_id!=null and user_id!=''">
            AND t.checkman = #{user_id}
        </if>
        <if test="start_date !=null and end_date !=null and queryType ==1">
            AND t.check_time >= #{start_date} AND t.check_time &lt; #{end_date}
        </if>
        <if test="start_date !=null and queryType == 0">
            AND t.start_time >= #{start_date}
        </if>
        <if test="end_date !=null and queryType == 0">
            AND t.end_time &lt; #{end_date}
        </if>
        <if test="score_flag!=null and score_flag == 1">
            AND cs.check_item_score IS NOT NULL
        </if>
        <if test="score_flag!=null and score_flag == 0">
            AND cs.check_item_score IS NULL
        </if>

        <if test="queryType!=null and queryType==1">
            ORDER BY cst.update_time desc
        </if>
    </select>

    <select id="selectCheckManInfo" parameterType="com.xintong.visualinspection.bean.CheckmanStatisticsBean"
            resultType="com.xintong.visualinspection.bean.CheckmanStatisticsBean">
        SELECT
        COUNT(1) AS all_checkman,
        COUNT(1) AS all_task_num,
        COUNT(cs.check_item_score) AS checked_num ,
        sum(
        TIMESTAMPDIFF(MINUTE, t.start_time,t.end_time)
        ) AS all_check_minute,
        COUNT(DISTINCT(t.checked_person)) AS all_checked_num,
        SUM(cs.check_item_score) AS all_checked_score
        FROM
        v_check_task t
        LEFT JOIN check_score cs ON t.id = cs.task_id
        WHERE
        t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="checkman_id!=null and checkman_id!=''">
            AND t.checkman = #{checkman_id}
        </if>
        <if test="start_date !=null">
            AND t.check_time >= #{start_date}
        </if>
        <if test="end_date !=null">
            AND t.check_time &lt; #{end_date}
        </if>
    </select>
    <!--稽查人员工作量统计 -->
    <select id="selectCheckManInfo1" parameterType="com.xintong.visualinspection.bean.CheckmanStatisticsBean"
            resultType="com.xintong.visualinspection.bean.CheckmanStatisticsBean">
        SELECT COUNT(1) AS all_task_num , sum(
        TIMESTAMPDIFF(MINUTE, t.start_time,t.end_time)
        ) AS all_check_minute , COUNT(DISTINCT(t.checked_person)) AS checked_num
        FROM v_check_task t
        WHERE
        t.check_time >= #{start_date}
        AND t.check_time &lt; #{end_date}

        <if test="checkman_id!=null">
            AND t.checkman = #{checkman_id}
        </if>
    </select>


    <select id="selectCheckManInfo2" parameterType="com.xintong.visualinspection.bean.CheckmanStatisticsBean"
            resultType="com.xintong.visualinspection.bean.CheckmanStatisticsBean">
        SELECT
        SUM(cs.check_item_score ) AS all_checked_score , COUNT(DISTINCT cs.checked_person) AS checked_people_num ,
        COUNT(DISTINCT cs.task_id) AS checked_task_num , count(1) AS all_checkman
        FROM
        check_score cs
        LEFT JOIN v_check_task t ON t.id = cs.task_id AND t.check_status != 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        WHERE
        t.check_time >= #{start_date}
        AND t.check_time &lt; #{end_date}
        <if test="checkman_id!=null">
            AND t.checkman = #{checkman_id}
        </if>
    </select>

    <select id="selectCheckManInfo3" parameterType="com.xintong.visualinspection.bean.CheckmanStatisticsBean"
            resultType="com.xintong.visualinspection.bean.CheckmanStatisticsBean">
        SELECT COUNT(1) AS unable_task_num FROM v_check_task t WHERE
        t.check_time >= #{start_date}
        AND t.check_time &lt; #{end_date}
        AND t.check_status = 22 and t.id not in
        <include refid="taskexclouddeleteUser"/>
        <if test="checkman_id!=null">
            AND t.checkman = #{checkman_id}
        </if>
    </select>
    <!--稽查人员工作量统计 end  -->


    <sql id="select_Fswork_condition">
        AND t.user_id IS NOT NULL
        <if test="start_time!=null">
            AND t.start_time >= #{start_time}
        </if>
        <if test="end_time!=null">
            AND t.end_time &lt; #{end_time}
        </if>
        <if test="dept_list!=null and dept_list.size()>0 ">
            AND t.dept_id IN
            <foreach item="obj" collection="dept_list" open="("
                     separator="," close=")">
                #{obj.id}
            </foreach>
        </if>
        <if test="dept_id!=null">
            AND t.dept_id=#{dept_id}
        </if>
        <if test="user_id!=null">
            AND t.user_id =#{user_id}
        </if>
    </sql>


    <resultMap id="statisticsFsWork" type="com.xintong.visualinspection.bean.StatisticsFsWork">
        <id property="user_id" column="user_id"/>
        <collection property="subStatisticsFsWork" ofType="com.xintong.visualinspection.bean.StatisticsFsWork">
            <id property="id" column="id"/>
            <result property="start_time" column="start_time"/>
            <result column="work_date" property="work_date"/>
            <result property="dept_id" column="dept_id"/>
            <result property="class_type" column="class_type"/>
            <result property="check_id" column="check_id"/>
            <result property="end_time" column="end_time"/>
            <result property="work_days" column="work_days"/>
            <result property="work_minutes" column="work_minutes"/>
        </collection>
    </resultMap>


    <resultMap id="taskBaseResultMap" type="com.xintong.visualinspection.bean.Task">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="check_status" property="check_status" jdbcType="INTEGER"/>
        <result column="check_status_name" property="check_status_name" jdbcType="VARCHAR"/>
        <result column="video_id" property="video_id" jdbcType="VARCHAR"/>
        <result column="create_time" property="create_time" jdbcType="VARCHAR"/>
        <result column="update_time" property="update_time" jdbcType="VARCHAR"/>
        <result column="start_time" property="start_time" jdbcType="VARCHAR"/>
        <result column="end_time" property="end_time" jdbcType="VARCHAR"/>
        <result column="checkman" property="checkman" jdbcType="INTEGER"/>
        <result column="recheckman" property="recheckman" jdbcType="INTEGER"/>
        <result column="checked_person" property="checked_person" jdbcType="INTEGER"/>
        <result column="checked_dept" property="checked_dept" jdbcType="INTEGER"/>
        <result column="rule_id" property="rule_id" jdbcType="INTEGER"/>
        <result column="checked_location" property="checked_location" jdbcType="VARCHAR"/>
        <result column="checked_location_type" property="checked_location_type" jdbcType="INTEGER"/>
        <result column="remark" property="remark" jdbcType="VARCHAR"/>
        <result column="check_period" property="check_period" jdbcType="VARCHAR"/>
        <result column="class_type" property="class_type" jdbcType="INTEGER"/>
        <result column="period_id" property="period_id" jdbcType="INTEGER"/>
        <result column="monitor_user_id" property="monitor_user_id" jdbcType="INTEGER"/>
        <result column="user_class_unregular_id" property="user_class_unregular_id" jdbcType="INTEGER"/>
        <result column="appeal_result" property="appeal_result" jdbcType="INTEGER"/>
    </resultMap>

    <select id="getAllTasklist" parameterType="com.xintong.visualinspection.bean.StatisticsFsWork"
            resultMap="taskBaseResultMap">

        select * from check_task where check_status!=22 and id not in
        <include refid="classexclouddeleteUser"/>

        <if test="start_time!=null">
            AND start_time >= #{start_time}
        </if>
        <if test="end_time!=null">
            AND end_time &lt; #{end_time}
        </if>

        order by checked_person

    </select>


    <select id="selectFsWork" parameterType="com.xintong.visualinspection.bean.StatisticsFsWork"
            resultMap="statisticsFsWork">
        SELECT t.* , ct.id AS check_id FROM sys_user_class t
        LEFT JOIN check_task ct ON t.id = ct.user_class_id AND ct.check_status != 22 and ct.id not in
        <include refid="taskexclouddeleteUser"/>
        AND t.class_type = ct.class_type AND t.user_id = ct.checked_person
        WHERE 1=1 AND t.user_id IS NOT NULL and t.id not in
        <include refid="classexclouddeleteUser"/>
        <include refid="select_Fswork_condition"/>
        ORDER BY t.work_date , t.user_id
    </select>

    <select id="selectFsWorkGather" parameterType="com.xintong.visualinspection.bean.StatisticsFsWork"
            resultType="com.xintong.visualinspection.bean.StatisticsFsWork">
        SELECT COUNT(DISTINCT(work_date)) AS work_days , SUM( TIMESTAMPDIFF(MINUTE, t.start_time,t.end_time)) AS
        work_minutes , t.*
        FROM sys_user_class t
        WHERE 1=1 AND t.user_id IS NOT NULL and t.id not in
        <include refid="classexclouddeleteUser"/>
        <include refid="select_Fswork_condition"/>
        GROUP BY t.user_id
    </select>

    <select id="selectFsUnregularWork" parameterType="com.xintong.visualinspection.bean.StatisticsFsWork"
            resultMap="statisticsFsWork">
        SELECT t.* , ct.id AS check_id FROM sys_user_class_unregular t
        LEFT JOIN check_task ct ON (t.id = ct.user_class_unregular_id OR ( ct.start_time > t.start_time AND ct.end_time
        &lt; t.end_time ) )
        AND ct.check_status != 22 and ct.id not in
        <include refid="taskexclouddeleteUser"/>
        AND t.class_type = ct.class_type AND t.user_id = ct.checked_person
        WHERE 1=1 AND t.user_id IS NOT NULL and t.id not in
        <include refid="unregularclassexclouddeleteUser"/>
        <include refid="select_Fswork_condition"/>
        ORDER BY t.work_date
    </select>

    <select id="selectFsUnregularWorkGather" parameterType="com.xintong.visualinspection.bean.StatisticsFsWork"
            resultType="com.xintong.visualinspection.bean.StatisticsFsWork">
        SELECT COUNT(DISTINCT(work_date)) AS work_days , SUM( TIMESTAMPDIFF(MINUTE, t.start_time,t.end_time)) AS
        work_minutes , t.*
        FROM sys_user_class_unregular t
        WHERE 1=1 AND t.user_id IS NOT NULL and t.id not in
        <include refid="unregularclassexclouddeleteUser"/>
        <include refid="select_Fswork_condition"/>
        GROUP BY t.user_id
    </select>

    <select id="selectFsWorkGatherGroupByWorkType" parameterType="com.xintong.visualinspection.bean.StatisticsFsWork"
            resultMap="statisticsFsWork">
        SELECT COUNT(DISTINCT(work_date)) AS work_days , t.*
        FROM sys_user_class t
        WHERE 1=1 AND t.user_id IS NOT NULL and t.id not in
        <include refid="classexclouddeleteUser"/>
        <include refid="select_Fswork_condition"/>
        GROUP BY t.user_id , t.class_type
    </select>

    <select id="selectFsUnregularWorkGatherGroupByWorkType"
            parameterType="com.xintong.visualinspection.bean.StatisticsFsWork" resultMap="statisticsFsWork">
        SELECT COUNT(DISTINCT(work_date)) AS work_days , t.*
        FROM sys_user_class_unregular t
        WHERE 1=1 AND t.user_id IS NOT NULL and t.id not in
        <include refid="classexclouddeleteUser"/>
        <include refid="select_Fswork_condition"/>
        GROUP BY t.user_id , t.class_type
    </select>

    <resultMap id="appealNum" type="com.xintong.visualinspection.bean.CheckAppealStatistic">
        <id property="dept_id" column="dept_id"/>
        <collection property="subCheckApealStatistic" ofType="com.xintong.visualinspection.bean.CheckAppealStatistic">
            <result column="appeal_num" property="appeal_num"/>
            <result property="appeal_result" column="appeal_result"/>
        </collection>
    </resultMap>

    <select id="selectCheckAppealNum" parameterType="com.xintong.visualinspection.bean.CheckAppealStatistic"
            resultMap="appealNum">
        SELECT COUNT(t.appeal_dept) AS appeal_num ,t.appeal_dept AS dept_id,t.appeal_result FROM check_appeal t
        INNER JOIN check_task ct ON t.task_id = ct.id WHERE 1=1 and t.task_id not in
        <include refid="taskexclouddeleteUser"/>
        and t.task_id not in (select id from check_task where check_task.check_status = 22)
        <if test="start_time!=null ">
            AND t.appeal_time >= #{start_time}
        </if>
        <if test="end_time!=null ">
            AND t.appeal_time &lt; #{end_time}
        </if>
        <if test="dept_list!=null and dept_list.size()>0 ">
            AND t.appeal_dept IN
            <foreach item="obj" collection="dept_list" open="("
                     separator="," close=")">
                #{obj.id}
            </foreach>
        </if>
        <if test="dept_id!=null">
            AND t.appeal_dept=#{dept_id}
        </if>
        GROUP BY t.appeal_dept , t.appeal_result
        ORDER BY t.appeal_dept
    </select>

    <select id="selectWreckerStaff" parameterType="com.xintong.visualinspection.bean.WreckerStatisticsBO"
            resultType="com.xintong.visualinspection.bean.WreckerStatisticsBO">
        SELECT o.id AS user_id , tlw.NAME as dept_name , o.TRUENAME AS name ,b.name AS position_name , o.WORKNO as
        work_no FROM t_sys_users o
        LEFT JOIN sys_job b ON o.POSITIONID = b.id
        LEFT JOIN t_br_layer_wrecker_group tlw ON tlw.ORGAN_ID = o.ORGANID
        where o.`STATUS` = 1
        AND o.ORGANID IN
        (
        SELECT ORGAN_ID FROM t_br_layer_wrecker_group
        )
        <if test="positionid != null">
            AND o.positionid = #{positionid}
        </if>
        <if test="dept_id != null">
            AND o.organid = #{dept_id}
        </if>
    </select>

    <select id="selectWreckerStatisticDetail" parameterType="com.xintong.visualinspection.bean.WreckerStatisticsBO"
            resultType="com.xintong.visualinspection.bean.WreckerStatisticsBO">
        SELECT cim.name AS item_name , COUNT(1) AS check_num , t.user_id , t.wrecker_check_type , SUM(t.check_score) AS
        score
        FROM
        (
        SELECT ci.parent_id , o.* FROM other_check o
        LEFT JOIN check_item ci ON o.check_item_id = ci.id
        WHERE o.check_type = 5 AND o.wrecker_check_type IN (1,2)
        <if test="start_date!=null and end_date != null">
            AND o.check_time &gt;= #{start_date} AND o.check_time &lt; #{end_date}
        </if>
        ) t LEFT JOIN check_item cim ON t.parent_id = cim.id
        GROUP BY user_id , t.parent_id ;
    </select>

    <select id="selectWreckerStatistic" parameterType="com.xintong.visualinspection.bean.WreckerStatisticsBO"
            resultType="com.xintong.visualinspection.bean.WreckerStatisticsBO">
        SELECT o.user_id , COUNT(1) AS check_num , sum(o.check_score) AS score ,o.wrecker_check_type FROM other_check o
        WHERE o.check_type = 5 AND o.wrecker_check_type IN (3,4,5)
        <if test="start_date!=null and end_date != null">
            AND o.check_time &gt;= #{start_date} AND o.check_time &lt; #{end_date}
        </if>
        GROUP BY o.wrecker_check_type , o.user_id
    </select>



    <select id="getcheckSExUser" parameterType="com.xintong.visualinspection.pojo.StatisticExUser" resultType="com.xintong.visualinspection.pojo.StatisticExUser">
        select * from check_statistic_exclude_users where  period_id = #{period_id}
    </select>

    <delete id="deletecheckSExUser" parameterType="com.xintong.visualinspection.pojo.StatisticExUser">
         delete from  check_statistic_exclude_users where  period_id = #{period_id} and user_id = #{user_id}
    </delete>

    <insert id="addcheckSExUser"  parameterType="com.xintong.visualinspection.pojo.StatisticExUser">
         INSERT INTO
        check_statistic_exclude_users
        (period_id,user_id)
        VALUES
        (#{period_id}, #{user_id})
    </insert>



    <sql id="selectExuserTask">
        (select a.id from check_task a,check_statistic_exclude_users b where a.checked_person = b.user_id  and a.period_id = b.period_id)
    </sql>


</mapper>