<?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.ledger.mapper.OperationLanePeakThroughputMapper">
    
    <resultMap type="OperationLanePeakThroughput" id="OperationLanePeakThroughputResult">
        <id     property="id"              column="id"              />
        <result property="deptId"          column="dept_id"         />
        <result property="deptName"        column="dept_name"       />
        <result property="teamId"          column="team_id"         />
        <result property="teamName"        column="team_name"       />
        <result property="groupId"         column="group_id"        />
        <result property="groupName"       column="group_name"      />
        <result property="laneId"          column="lane_id"         />
        <result property="recordDate"      column="record_date"     />
        <result property="hourOfDay"       column="hour_of_day"     />
        <result property="throughput"      column="throughput"      />
        <result property="throughputRate"  column="throughput_rate" />
        <result property="remark"          column="remark"          />
        <result property="createBy"        column="create_by"       />
        <result property="createTime"      column="create_time"     />
        <result property="updateBy"        column="update_by"       />
        <result property="updateTime"      column="update_time"     />
        <result property="delFlag"         column="del_flag"        />
    </resultMap>

    <sql id="selectOperationLanePeakThroughputVo">
        select id, dept_id, dept_name, team_id, team_name, group_id, group_name, 
               lane_id, record_date, hour_of_day, throughput, throughput_rate, remark, 
               create_by, create_time, update_by, update_time, del_flag
        from operation_lane_peak_throughput
    </sql>

    <select id="selectOperationLanePeakThroughputList" parameterType="OperationLanePeakThroughput" resultMap="OperationLanePeakThroughputResult">
        <include refid="selectOperationLanePeakThroughputVo"/>
        <where>  
            del_flag = '0'
            <if test="deptId != null "> and dept_id = #{deptId}</if>
            <if test="deptName != null  and deptName != ''"> and dept_name like concat('%', #{deptName}, '%')</if>
            <if test="teamId != null "> and team_id = #{teamId}</if>
            <if test="teamName != null  and teamName != ''"> and team_name like concat('%', #{teamName}, '%')</if>
            <if test="groupId != null "> and group_id = #{groupId}</if>
            <if test="groupName != null  and groupName != ''"> and group_name like concat('%', #{groupName}, '%')</if>
            <if test="laneId != null  and laneId != ''"> and lane_id = #{laneId}</if>
            <if test="recordDate != null "> and record_date = #{recordDate}</if>
            <if test="hourOfDay != null  and hourOfDay != ''"> and hour_of_day = #{hourOfDay}</if>
        </where>
        order by record_date desc, create_time desc
    </select>

    <select id="countLanePeakThroughput" parameterType="com.sundot.airport.ledger.domain.vo.CountQueryReqVO" resultType="com.sundot.airport.ledger.domain.vo.LaneThroughputResVO">
        SELECT
            record_date AS recordDate,
            <!-- 动态分组名称：小组>班组>部门 -->
            <choose>
                <when test="groupId != null">
                    group_name AS groupName
                </when>
                <when test="teamId != null">
                    team_name AS groupName
                </when>
                <when test="deptId != null">
                    dept_name AS groupName
                </when>
            </choose>,
            SUM(throughput) AS totalThroughput,
            COUNT(*) AS totalRecord,
            <!-- 过检率：分母0返回0.0，保留1位小数 -->
            ROUND(IF(COUNT(*) = 0, 0, SUM(throughput) / COUNT(*)), 1) AS throughputRate
        FROM operation_lane_peak_throughput
        WHERE
            del_flag = '0'
            <!-- 近30天自然日 -->
            AND record_date >= DATE_SUB(CURDATE(), INTERVAL 29 DAY)
            AND record_date &lt;= CURDATE()
            <!-- 三级联动动态筛选 -->
            <if test="deptId != null">
                AND dept_id = #{deptId}
            </if>
            <if test="teamId != null">
                AND team_id = #{teamId}
            </if>
            <if test="groupId != null">
                AND group_id = #{groupId}
            </if>
            <!-- 动态分组：按选中层级+日期分组 -->
        GROUP BY
            <choose>
                <when test="groupId != null">
                    group_id, record_date
                </when>
                <when test="teamId != null">
                    team_id, record_date
                </when>
                <when test="deptId != null">
                    dept_id, record_date
                </when>
            </choose>
        <!-- 按日期升序，大屏展示顺序 -->
        ORDER BY record_date ASC
    </select>

</mapper>
