<?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.OperationStationHourlyThroughputMapper">
    
    <resultMap type="OperationStationHourlyThroughput" id="OperationStationHourlyThroughputResult">
        <id     property="id"              column="id"              />
        <result property="areaId"          column="area_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="selectOperationStationHourlyThroughputVo">
        select id, area_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_station_hourly_throughput
    </sql>

    <select id="selectOperationStationHourlyThroughputList" parameterType="OperationStationHourlyThroughput" resultMap="OperationStationHourlyThroughputResult">
        <include refid="selectOperationStationHourlyThroughputVo"/>
        <where>  
            del_flag = '0'
            <if test="areaId != null and areaId != '' "> and area_id = #{areaId}</if>
            <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="countStationHourlyThroughput" resultType="com.sundot.airport.ledger.domain.vo.StationHourlyThroughputVO">
        SELECT
            t.hour_of_day AS hourOfDay,
            t.area_id AS areaId,
            IFNULL(SUM(t.throughput), 0) AS areaFlow,
            t.total_hour_flow AS totalHourFlow
        FROM (
            SELECT
                hour_of_day,
                area_id,
                throughput,
                SUM(throughput) OVER (PARTITION BY hour_of_day) AS total_hour_flow
            FROM operation_station_hourly_throughput
            WHERE
                del_flag = '0'
                AND hour_of_day IS NOT NULL
                AND hour_of_day != ''
            -- 近24小时自动匹配
            AND STR_TO_DATE(CONCAT(record_date, hour_of_day), '%Y%m%d%H') >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
        ) t
        GROUP BY t.hour_of_day, t.area_id
        ORDER BY t.hour_of_day ASC
    </select>
</mapper>
