package com.tievd.jyz.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.tievd.jyz.dto.CarModelProportionDTO;
import com.tievd.jyz.dto.SalesStatDTO;
import com.tievd.jyz.dto.TrafficFlowStatDTO;
import com.tievd.jyz.entity.OilRecord;
import com.tievd.jyz.entity.vo.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

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

    List<Map> statisOilPosition(@Param("licenseNum") String licenseNum, @Param("orgCode") String orgCode);

    List<Map> statisByMonth(@Param("licenseNum") String licenseNum, @Param("orgCode") String orgCode);

    List<Map> statisByHour(@Param("licenseNum") String licenseNum, @Param("orgCode") String orgCode);

    List<Map> statisByStayTime(@Param("licenseNum") String licenseNum, @Param("orgCode") String orgCode);

    IPage<Map> getStatisOilVolume(IPage page, @Param("orgCode") String orgCode, @Param("dateMonth") String dateMonth);

    Map getOilVolumeTotal(@Param("orgCode") String orgCode, @Param("dateMonth") String dateMonth);

    IPage<OilVolumeVo> descOilVolumeList(IPage page, @Param("map") Map map);

    /**
     * 按日统计运营概况
     */
    @Select("select count(1) inboundCount, count(if(behavior = 1, 1, null)) addOilCount, ifnull(sum(oil_volume), 0) saleOilCount from t_oil_record where record_day = #{date} and org_code like concat(#{orgCode}, '%')")
    OperationOverviewVO getOilRecordOverview(@Param("date") String date, @Param("orgCode") String orgCode);

    /**
     * 按日统计车辆进站数据
     */
    @Select("select ifnull(count(if(tor.behavior=1,1,null)) ,0) inboundCount, a.date from(select date_format(date_add(#{date}, 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_oil_record tor on tor.record_day = a.date and tor.org_code like concat(#{orgCode}, '%') group by a.date order by a.date asc")
    List<TrafficFlowStatDTO> inboundCountStat(@Param("intervalDay") Integer intervalDay, @Param("date") String date, @Param("orgCode") String orgCode);

    /**
     * 加油时刻统计
     */
    @Select("select count(id) inboundCount, count(if(behavior=1,1,null)) addOilCount ,date_format(tor.start_time , '%k') as time from t_oil_record tor where tor.org_code like concat(#{orgCode}, '%') and tor.record_day = #{date} group by time")
    List<OilRecordTimeStatVO> oilRecordTimeStat(@Param("date") String date, @Param("orgCode") String orgCode);

    /**
     * 加油位热力统计
     */
    @Select("select top.oil_position,count(tor.id) stopCount from t_oil_position top left join t_oil_record tor on tor.oil_position = top.oil_position and tor.org_code = top.org_code and top.org_code like concat(#{orgCode}, '%') and tor.record_day = #{date} group by top.oil_position")
    List<OilPositionHeatMapVO> getOilPositionHeatMap(@Param("date") String date, @Param("orgCode") String orgCode);

    /** 车型统计 */
    @Select("select tscm .model_name carModel,count(tor.id) addOilCount from t_sys_car_model tscm " +
            "left join t_car_info tci on tci.model_id = tscm.model_code " +
            "left join t_oil_record tor on tor.license_num = tci.license_num and tor.behavior = 1 and tor.record_day = #{date} and tor.org_code like concat(#{orgCode}, '%') " +
            "group by tscm.model_code")
    List<CarModelProportionDTO> carModelStat(@Param("date") String date, @Param("orgCode") String orgCode);

    /** 销量统计 */
    @Select("select ifnull(sum(tor.oil_volume) , 0) salesVolume,a.date from (select date_format(date_add(#{date}, 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_oil_record tor on tor.record_day = a.date and tor.org_code like concat(#{orgCode}, '%') and tor.behavior = 1" +
            " group by a.date order by a.date asc")
    List<SalesStatDTO> salesStat(@Param("intervalDay") Integer intervalDay, @Param("date") String date, @Param("orgCode") String orgCode);

    /** 按机构统计车辆进站数据 */
    @Select("select sd.org_code departCode, sd.depart_name departName, count(tor.id) inboundCount  from sys_depart sd left join  t_oil_record tor on sd.org_code = tor.org_code and tor.record_day = #{date} where sd.org_category = 3 and sd.org_code like concat(#{orgCode},'%') group by sd.org_code order by inboundCount desc limit #{limit}")
    List<RegionTrafficFlowStatVO> regionTrafficFlowStat(@Param("date")String date, @Param("orgCode")String orgCode, @Param("limit")Integer limit);

    /** 加油率统计 当月*/
    @Select("select count(tor.id) inboundCount, count(if(tor.behavior=1,1,null)) addOilCount,sd.depart_name from sys_depart sd left join t_oil_record tor on sd.org_code = tor.org_code and date_format(tor.record_day , '%Y-%m') = #{date} where sd.org_category = 3 and sd.org_code like concat(#{orgCode}, '%') group by sd.org_code order by inboundCount desc limit #{limit}")
    List<OilRecordStatVO> getRegionOilRecordStatByMonth(@Param("orgCode") String orgCode, @Param("date") String date, @Param("limit") Integer limit);

    /** 加油率统计 当天*/
    @Select("select count(tor.id) inboundCount, count(if(tor.behavior=1,1,null)) addOilCount,sd.depart_name from sys_depart sd left join t_oil_record tor on sd.org_code = tor.org_code and tor.record_day = #{date} where sd.org_category = 3 and sd.org_code like concat(#{orgCode}, '%') group by sd.org_code order by inboundCount desc limit #{limit}")
    List<OilRecordStatVO> getRegionOilRecordStatByDay(@Param("orgCode")String orgCode,  @Param("date") String date, @Param("limit") Integer limit);

    /** 销量排行 当天*/
    @Select("select sd.depart_name,ifnull(sum(tor.oil_volume),0) salesVolume from sys_depart sd left join t_oil_record tor on sd.org_code = tor.org_code and tor.record_day = #{date} where sd.org_category = 3 and sd.org_code like concat(#{orgCode},'%') group by sd.org_code order by salesVolume desc limit #{limit}")
    List<SalesStatVO> getSalesRankingByDay(@Param("orgCode")String orgCode,  @Param("date") String date, @Param("limit") Integer limit);

    /** 销量排行 当月*/
    @Select("select sd.depart_name,ifnull(sum(tor.oil_volume),0) salesVolume from sys_depart sd left join t_oil_record tor on sd.org_code = tor.org_code and date_format(tor.record_day, '%Y-%m') = #{date} where sd.org_category = 3 and sd.org_code like concat(#{orgCode},'%') group by sd.org_code order by salesVolume desc limit #{limit}")
    List<SalesStatVO> getSalesRankingByMonth(@Param("orgCode")String orgCode,  @Param("date") String date, @Param("limit") Integer limit);
    
    StatDataTableVo statisTotal(DataStatisReqVo param);
    
    List<Map> statisOrgTopTraffic(DataStatisReqVo param);
    
    List<Map> statisOrgTopOil(DataStatisReqVo param);
    
    List<Map> statisOrgTopVolume(DataStatisReqVo param);
    
    List<StatDataTableVo> statFanByModel(DataStatisReqVo param);
    
    List<StatDataTableVo> statFanByPosition(DataStatisReqVo param);
    
    List<StatDataTableVo> statBarByModel(DataStatisReqVo param);
    
    Map getVehicleFrequencyBeforeActivity(@Param("startTime") String startTime, @Param("orgCodes") List<String> orgCodes);
    
    Map getVehicleFrequencyDuringActivity(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("orgCodes") List<String> orgCodes);
    
    Map getVehicleFrequencyAfterActivity(@Param("endTime") String endTime, @Param("orgCodes") List<String> orgCodes);
}