<?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.sundot.airport.system.mapper.portrait.RewardPenaltyIndicatorMapper">

    <resultMap type="com.sundot.airport.system.domain.portrait.RewardPenaltyStats" id="RewardPenaltyStatsResult">
        <result property="totalPersonTimes" column="total_person_times"/>
        <result property="totalCount" column="total_count"/>
        <result property="averageTimes" column="average_times"/>
    </resultMap>

    <sql id="userIdsCondition">
        <foreach collection="userIds" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </sql>

    <!-- 查询个人工作奖励次数 -->
    <select id="queryPersonalWorkRewards" resultType="java.lang.Integer">
        SELECT CASE WHEN work_rewards_number IS NOT NULL THEN work_rewards_number ELSE 0 END
        FROM sys_user
        WHERE user_id = #{userId}
          AND del_flag = '0'
    </select>

    <!-- 查询组织工作奖励统计 -->
    <select id="queryOrgWorkRewardsStats" resultMap="RewardPenaltyStatsResult">
        SELECT
        COALESCE(SUM(CASE WHEN work_rewards_number IS NOT NULL THEN work_rewards_number ELSE 0 END), 0) AS
        total_person_times,
        COUNT(*) AS total_count,
        COALESCE(ROUND(SUM(CASE WHEN work_rewards_number IS NOT NULL THEN work_rewards_number ELSE 0 END) / COUNT(*),
        2), 0) AS average_times
        FROM sys_user
        WHERE del_flag = '0'
        AND status = '0'
        AND user_id IN
        <include refid="userIdsCondition"/>
    </select>

    <!-- 查询个人工作处罚次数 -->
    <select id="queryPersonalWorkPenalties" resultType="java.lang.Integer">
        SELECT CASE WHEN work_penalties_number IS NOT NULL THEN work_penalties_number ELSE 0 END
        FROM sys_user
        WHERE user_id = #{userId}
          AND del_flag = '0'
    </select>

    <!-- 查询组织工作处罚统计 -->
    <select id="queryOrgWorkPenaltiesStats" resultMap="RewardPenaltyStatsResult">
        SELECT
        COALESCE(SUM(CASE WHEN work_penalties_number IS NOT NULL THEN work_penalties_number ELSE 0 END), 0) AS
        total_person_times,
        COUNT(*) AS total_count,
        COALESCE(ROUND(SUM(CASE WHEN work_penalties_number IS NOT NULL THEN work_penalties_number ELSE 0 END) /
        COUNT(*), 2), 0) AS average_times
        FROM sys_user
        WHERE del_flag = '0'
        AND status = '0'
        AND user_id IN
        <include refid="userIdsCondition"/>
    </select>

</mapper>