<?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.visuallnspection_qzd.dao.FwqStatisticsDao">

    <select id="getselectFeeStationOperationScoreDetail"
            parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        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="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="selectFeeStationCheckNum" parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        SELECT
        COUNT(t.checked_dept) AS checked_num,
        t.checked_dept AS dept_id
        FROM view_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="period_id != null and period_id != ''">
            AND t.period_id = #{period_id}
        </if>
        GROUP BY t.checked_dept
    </select>

    <select id="selectFeeStationCheckedPersonScoreDetail"
            parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        SELECT
        t.checked_dept     AS dept_id,
        t.check_item_id,
        t.check_item_score AS score
        FROM check_score t
        JOIN view_check_task ct ON ct.id = t.task_id
        WHERE t.check_item_score IS NOT NULL
        <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="period_id != null and period_id != ''">
            AND ct.period_id = #{period_id}
        </if>
    </select>

    <select id="selectStatistics" parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        SELECT
        u.dept_id,
        u.check_item_id,
        sum(u.score) AS score
        FROM (
        SELECT
        t.checked_dept  dept_id,
        t.check_item_id,
        t.check_item_score AS score
        FROM check_score t
        JOIN view_check_task ct ON ct.id = t.task_id
        WHERE 1 = 1
        <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 GROUP BY check_item_id
    </select>

    <select id="selectCheckedTimes" parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        SELECT COUNT(1) AS checked_num , t.checked_dept dept_id FROM view_check_task t
        WHERE 1=1
        <if test="dept_id != null and dept_id != ''">
            AND t.checked_dept =#{dept_id}
        </if>
        <if test="start_date!=null and end_date != null">
            AND t.start_time &gt;= #{start_date} AND t.end_time &lt; #{end_date}
        </if>
    </select>

    <select id="selectOneCheckedDetailInfo" parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        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,
        cs.pics,
        t.checkman          AS checkman_id,
        t.checked_dept      AS dept_id,
        cs.check_item_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">
            view_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>
        where 1=1
        <if test="dept_id != null and dept_id != ''">
            AND t.checked_dept = #{dept_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="selectOneCheckedInfoData" parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        SELECT * FROM (
        <include refid="selectOneAllCheckedInfoData"/>
        ) t
    </select>

    <select id="selectOneCheckedItemInfo" parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        SELECT
        a.*
        FROM (
        SELECT
        count(t.id)     AS checked_num,
        sum(cs.check_item_score) AS all_check_score,
        cs.check_item_id,
        cs.checked_dept as dept_id
        FROM
        view_check_task t
        LEFT JOIN check_score cs ON cs.task_id = t.id
        WHERE
        1 = 1
        and cs.checked_dept = #{dept_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
        check_item_id
        ) a where a.all_check_score is not null
    </select>

    <select id="selectYearScoreInfo" parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        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="selectFeeStationCheckedScore" parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        SELECT
        IFNULL(sum(ct.check_item_score), 0) AS score,
        t.checked_dept                      AS dept_id
        FROM view_check_task t
        LEFT JOIN check_score ct ON t.id = ct.task_id
        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="period_id !=null and period_id != '' ">
            AND t.period_id =  #{period_id}
        </if>
        GROUP BY t.checked_dept
    </select>

    <select id="selectFeeStationScoreInfo" parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        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>

    <select id="selectCheckedItemScoreInfo" parameterType="com.xintong.visualinspection.bean.FwqStatisticsBean"
            resultType="com.xintong.visualinspection.bean.FwqStatisticsBean">
        SELECT
          count(DISTINCT m.task_id)   AS checked_num,
          sum(m.check_item_score)     AS all_check_score,
          m.check_item_id,
          m.checked_dept              AS dept_id
        FROM view_check_task t INNER JOIN
          check_score m ON t.id = m.task_id
        WHERE 1=1
        AND monthfunc(t.start_time) = #{mth}
        GROUP BY  m.check_item_id,m.checked_dept
        ORDER BY m.checked_dept
    </select>

    <select id="selectlimituser"   parameterType="com.xintong.visualinspection.pojo.fwq.ServiceAreaStatistic" resultType="com.xintong.visualinspection.pojo.fwq.ServiceAreaStatistic">

        select a.* from (
        <include refid="selectc"/>
        ) a where a.dept_id IN ( select d.dept_id from  (select p.dept_id,
        COUNT(dept_id) AS COUNT from (<include refid="selectc"/>) p GROUP BY p.dept_id ) d where d.count >= #{limitnumber} )

    </select>

    <sql id="selectc">
        SELECT *
        FROM
        check_dept_score AS user_total_score
        WHERE
        period_id >= #{startperiod_id}
        AND period_id &lt;= #{endperiod_id}
        AND score >= #{limitscore} AND score &lt;= #{limitscore2}
        GROUP BY dept_id, period_id
    </sql>


    <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_dept
        FROM
        (
        SELECT
        COUNT(t.id)             AS checked_num,
        monthfunc(t.start_time) AS mth,
        t.checked_dept
        FROM
        view_check_task t
        WHERE 1 = 1
        <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="dept_id !=null ">
            AND t.checked_dept = #{dept_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 view_check_task t
            WHERE 1 = 1
        <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="dept_id !=null ">
            AND m.checked_dept = #{dept_id}
        </if>
        GROUP BY mth

        ) checked_score_t ON checked_score_t.checked_dept = check_num_t.checked_dept
        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_dept
        FROM
        (
        SELECT
        COUNT(t.checked_dept)   AS checked_num,
        monthfunc(t.start_time) AS mth,
        t.id,
        t.checked_dept
        FROM
        view_check_task t
        WHERE
        t.checked_dept IS NOT NULL
        <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_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 view_check_task t
        WHERE 1 = 1
        <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_dept
        ) checked_score_t ON checked_score_t.checked_dept = check_num_t.checked_dept
        AND check_num_t.mth = checked_score_t.mth
    </sql>

</mapper>


