<?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.CheckUserStatisticsDao" >
    <resultMap id="BaseResultMap" type="com.xintong.visualinspection.bean.UserStatistic" >
        <id column="id" property="id" jdbcType="INTEGER" />

        <result column="user_id" property="user_id" jdbcType="INTEGER" />
        <result column="period_id" property="period_id" jdbcType="INTEGER" />
        <result column="user_score" property="user_score" jdbcType="INTEGER" />
        <result column="user_check_num" property="user_check_num" jdbcType="INTEGER" />
        <result column="user_total_score" property="user_total_score" jdbcType="DOUBLE" />
        <result column="user_ranking_c" property="user_ranking_c" jdbcType="INTEGER" />
        <result column="user_ranking_d" property="user_ranking_d" jdbcType="INTEGER" />
        <result column="user_ranking_s" property="user_ranking_s" jdbcType="INTEGER" />
        <result column="dept_id" property="dept_id" jdbcType="INTEGER" />

    </resultMap>


    <select id="getOne" parameterType="java.lang.Long" resultMap="BaseResultMap" >
        SELECT *
        FROM check_statistics
        WHERE id = #{id}
    </select>
    
    <select id="getByUser" parameterType="com.xintong.visualinspection.bean.UserStatistic" resultMap="BaseResultMap" >
        SELECT *
        FROM check_statistics
        WHERE user_id = #{user_id} and period_id = #{period_id}
    </select>
    
    <insert id="insert" useGeneratedKeys="true" keyProperty="id"  parameterType="com.xintong.visualinspection.bean.UserStatistic" >
        <include refid="insertc"/>
    </insert>

    <update id="update" parameterType="com.xintong.visualinspection.bean.UserStatistic" >
        <include refid="updatec"/>
    </update>

    <delete id="delete" parameterType="java.lang.Long" >
        DELETE FROM
        check_statistics
        WHERE
        id =#{id}
    </delete>

    <insert id="saveandupdate" useGeneratedKeys="true" keyProperty="id" parameterType="com.xintong.visualinspection.bean.UserStatistic" >
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            select count(*) as count from check_statistics where user_id = #{user_id} and period_id = #{period_id}
        </selectKey>
        <if test="count>0">
           <include refid="updatec"/>
        </if>
        <if test="count==0">
            <include refid="insertc"/>
        </if>
    </insert>


    <sql id="updatec">
        UPDATE
        check_statistics
        SET
        <if test="user_score != null">user_score = #{user_score},</if>
        <if test="user_check_num != null">user_check_num = #{user_check_num},</if>
        <if test="user_ranking_c != null">user_ranking_c = #{user_ranking_c},</if>
        <if test="user_ranking_d != null">user_ranking_d = #{user_ranking_d},</if>
        <if test="user_ranking_s != null">user_ranking_s = #{user_ranking_s},</if>
        <if test="dept_id != null">dept_id = #{dept_id}</if>
        WHERE
        user_id = #{user_id} and period_id = #{period_id}
    </sql>
    <sql id="insertc">
         INSERT INTO
        check_statistics
        (user_id,period_id,user_score,user_check_num,user_ranking_c,user_ranking_d,user_ranking_s,dept_id)
        VALUES
        (#{user_id}, #{period_id},#{user_score},#{user_check_num},#{user_ranking_c},#{user_ranking_d},#{user_ranking_s},#{dept_id})
    </sql>


    <select id="selectlimituser"   parameterType="com.xintong.visualinspection.bean.UserStatistic" resultMap="BaseResultMap">

        select c.* from (
            <include refid="selectc"/>
        ) c where c.user_id in ( select d.user_id from  (select p.user_id,count(user_id) as count from (<include refid="selectc"/>) p GROUP BY p.user_id ) d where d.count >=#{limitnumber} )

    </select>

    <sql id="selectc">
      SELECT
        b.*
        FROM
        (
        SELECT
        a.*,
        (
        1000- a.user_score / ( a.user_check_num * 1.00 )
        ) AS user_total_score
        FROM
        (
        SELECT
        *
        FROM
        check_statistics
        WHERE
        period_id >= #{startperiod_id}
        AND period_id &lt;= #{endperiod_id} AND user_check_num > 0
        ) a
        ) b
        WHERE
        b.user_total_score >=#{limitscore} and  b.user_total_score &lt;= #{limitscore2}
        GROUP BY user_id ,period_id
    </sql>


</mapper>