package com.tievd.jyz.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tievd.jyz.dto.TrafficFlowStatDTO;
import com.tievd.jyz.entity.TrafficFlow;
import com.tievd.jyz.entity.vo.RegionTrafficFlowStatVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * TrafficFlow
 * @author      cube
 * @since       2023-02-27
 * @version     V2.0.0
 */
public interface TrafficFlowMapper extends BaseMapper<TrafficFlow> {

    /** 车流量查询 */
    @Select("select ifnull(sum(car_count), 0) from t_traffic_flow where capture_day = #{date} and org_code like concat(#{orgCode}, '%')")
    Long getTrafficFlow(@Param("date") String today, @Param("orgCode") String orgCode);

    /** 车流量统计 */
    @Select("select ifnull(sum(tf.car_count), 0) trafficFlow, a.date from(select date_format(date_add(#{endDay}, interval -t.help_topic_id day), '%Y-%m-%d') as 'date' from mysql.help_topic t where t.help_topic_id < #{intervalDay}) as a left join t_traffic_flow tf on tf.capture_day = a.date and tf.org_code like concat(#{orgCode}, '%') group by a.date order by a.date asc")
    List<TrafficFlowStatDTO> trafficFlowStat(@Param("intervalDay") Integer intervalDay, @Param("endDay") String endDay, @Param("orgCode") String orgCode);

    /** 车流量统计 */
    @Select("select sd.org_code departCode, sd.depart_name departName,ifnull(sum(TTF.car_count), 0) trafficFlow from sys_depart sd left join t_traffic_flow ttf on sd.org_code = ttf.org_code and ttf.capture_day = #{date} where sd.org_category = 3 and sd.org_code like concat(#{orgCode}, '%') group by sd.org_code order by trafficFlow desc limit #{limit}")
    List<RegionTrafficFlowStatVO> regionTrafficFlowStat(@Param("date") String date, @Param("orgCode") String orgCode, @Param("limit") Integer limit);
}