<?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.kxs.user.biz.mapper.KxsShdScoreMapper">

    <resultMap type="com.kxs.user.api.model.KxsShdScore" id="KxsShdScoreMap">
        <result column="id" property="id" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
        <result column="del_flag" property="delFlag" />
        <result column="version" property="version" />
        <result column="username" property="username" />
        <result column="user_code" property="userCode" />
        <result column="first_score" property="firstScore" />
        <result column="center_score" property="centerScore" />
        <result column="high_score" property="highScore" />

    </resultMap>
    <select id="getMyStandards"
            resultType="com.kxs.user.api.vo.kxsapp.shareholder.ShareholderGetMyStandardsVO">
                SELECT first_score / 3 as firstScore,
                       center_score / 3 as centerScore,
                       high_score / 3 as highScore
                FROM kxs_shd_score
                WHERE del_flag != 1
                AND (
                first_score >= 3
                OR center_score >= 3
                OR high_score >= 3
                )
                and user_code = #{userCode}
                and month = #{month}
    </select>
    <select id="getMyStandardsAll"
            resultType="com.kxs.user.api.vo.kxsapp.shareholder.ShareholderGetMyStandardsAllVO">
        SELECT
            SUM(CASE WHEN first_score >= 3 THEN FLOOR(first_score / 3) ELSE 0 END) AS firstScoreAll,
            SUM(CASE WHEN center_score >= 3 THEN FLOOR(center_score / 3) ELSE 0 END) AS centerScoreAll,
            SUM(CASE WHEN high_score >= 3 THEN FLOOR(high_score / 3) ELSE 0 END) AS highScoreAll
        FROM
            kxs_shd_score
        WHERE
            del_flag = 0
    AND (
        first_score >= 3
        OR center_score >= 3
        OR high_score >= 3
    )
    AND month = #{month}
    </select>

    <select id="getShareholderHighScoreUserList"
            resultType="com.kxs.user.api.vo.kxsapp.subGetShareholderUserList.UserScoreListVO">
        SELECT
        a.id, u.username, u.user_code, a.user_id, u.avatar,
        <choose>
            <when test="type == 1">
                FLOOR(a.first_score/3) AS score
            </when>
            <when test="type == 2">
                FLOOR(a.center_score/3) AS score
            </when>
            <when test="type == 3">
                FLOOR(a.high_score/3) AS score
            </when>
        </choose>
        FROM kxs_shd_score a
        LEFT JOIN kxs_user u on u.id = a.user_id
        WHERE a.del_flag != 1
        <choose>
            <when test="type == 1">
                AND a.first_score >= 3
            </when>
            <when test="type == 2">
                AND a.center_score >= 3
            </when>
            <when test="type == 3">
                AND a.high_score >= 3
            </when>
        </choose>
        AND a.month = #{month}
        ORDER BY score DESC, id ASC
    </select>































</mapper>
