<?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.AdministrativePenaltyIndicatorMapper">

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

    <!-- 查询个人行政类处罚情况 -->
    <select id="queryPersonalAdministrativePenaltyStatus" resultType="String">
        SELECT administrative_status
        FROM sys_user
        WHERE user_id = #{userId}
    </select>

    <!-- 查询组织行政类处罚情况统计 -->
    <select id="queryOrgAdministrativePenaltyStats"
            resultType="com.sundot.airport.system.domain.portrait.AdministrativePenaltyStats">
        SELECT
        COALESCE(SUM(CASE WHEN administrative_status IS NOT NULL AND administrative_status != '' AND
        administrative_status != '无' THEN 1 ELSE 0 END), 0) AS penalizedCount,
        COUNT(*) AS totalCount,
        COALESCE(ROUND(SUM(CASE WHEN administrative_status IS NOT NULL AND administrative_status != '' AND
        administrative_status != '无' THEN 1 ELSE 0 END) / COUNT(*), 4), 0) AS penaltyPercentage FROM sys_user
        WHERE user_id in
        <include refid="userIdsCondition"/>
        and del_flag='0' and status='0'
    </select>

</mapper>