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<Map> statisOilFreqCompare(DataStatisReqVo param);
|
|
List<StatDataTableVo> statFanByModel(DataStatisReqVo param);
|
|
List<StatDataTableVo> statFanByPosition(DataStatisReqVo param);
|
|
List<StatDataTableVo> statFanByOilType(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);
|
}
|
