<?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.tangguo.mapper.BbsUserPointsMapper">
    
    <select id="selectBbsUserPointsList" resultType="com.tangguo.domain.BbsUserPoints">
        SELECT
            uv.nick_name AS nick_name,
            uv.user_name,
            ud.dept_name,
            IFNULL(p.current_points, 0) AS current_points,
            IFNULL(p.accumulated_points, 0) AS accumulated_points,
            IFNULL(p.grade_name, ug.name) AS grade_name
        FROM
            sys_user uv
        LEFT JOIN
            sys_dept ud ON ud.dept_id = uv.dept_id
        LEFT JOIN
            bbs_user_points p ON p.user_name = uv.user_name
        CROSS JOIN (
            SELECT (SELECT name FROM bbs_grade WHERE is_initial = 1 ORDER BY create_time DESC LIMIT 1) AS name
        ) ug
        <where>
            <if test="points.nickName != null and points.nickName != ''">
                AND uv.nick_name LIKE CONCAT('%', #{points.nickName}, '%')
            </if>
            <if test="points.userName != null and points.userName != ''">
                AND uv.user_name LIKE CONCAT('%', #{points.userName}, '%')
            </if>
            <if test="points.startPoints != null">
                AND p.current_points >= #{points.startPoints}
            </if>
            <if test="points.endPoints != null">
                AND p.current_points &lt;= #{points.endPoints}
            </if>
        </where>
        ORDER BY
            uv.create_time DESC
    </select>
    
    
    <select id="selectBbsUserPoints" resultType="com.tangguo.domain.BbsUserPoints">
        SELECT
            uv.nick_name AS nick_name,
            uv.user_name,
            ud.dept_name,
            IFNULL(p.current_points, 0) AS current_points,
            IFNULL(p.accumulated_points, 0) AS accumulated_points,
            IFNULL(p.grade_name, ug.name) AS grade_name
        FROM
            sys_user uv
        LEFT JOIN
            sys_dept ud ON ud.dept_id = uv.dept_id
        LEFT JOIN
            bbs_user_points p ON p.user_name = uv.user_name
        CROSS JOIN (
            SELECT (SELECT name FROM bbs_grade WHERE is_initial = 1 ORDER BY create_time DESC LIMIT 1) AS name
        ) ug
        WHERE
          uv.user_name = #{userName}
    </select>
    
    
    <select id="selectSysUserCount" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM sys_user WHERE user_name = #{userName}
    </select>
    
    
    <update id="refreshUserPointsGrade">
        UPDATE
            bbs_user_points p
        LEFT JOIN
            (
                SELECT * FROM bbs_grade ORDER BY create_time LIMIT 1
            ) g ON (g.min_points &lt;= p.accumulated_points AND g.max_points >= p.accumulated_points)
        SET
            p.grade_code = g.`code`, p.grade_name = g.`name`, p.last_upgrade_time = NOW()
        WHERE
            p.grade_code != g.code
    </update>

</mapper>
