hust-epi/whepi-web/src/main/java/com/bofeng/dao/MsSuspectedMapper.java

package com.bofeng.dao;


import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.bofeng.entity.MsReport;
import com.bofeng.entity.MsSuspected;
import com.bofeng.entity.MsTripDet;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Author: xielianghe
 * @Date: 2020/2/4 15:39
 */
@Mapper
@Repository
public interface MsSuspectedMapper extends BaseMapper<MsSuspected> {

    List<MsSuspected> selectByReportId(@Param("reportId") Long reportId);

    //判断家人姓名是否重复
    @Select("select a.* from ms_suspected  a LEFT JOIN ms_report b on a.report_id=b.report_id  where a.report_id=#{reportId} and a.user_name=#{userName} and b.report_date=#{reportDate}")
    List<MsSuspected> selectUserNameNum(@Param("reportId") Long reportId, @Param("userName") String userName, @Param("reportDate") String reportDate);

    //判断家人是否有历史评分
    @Select("select score from ms_suspected where user_create=#{userCreate} and user_name=#{userName} and suspected_id !=#{suspectedId}  order by time_update desc limit 1")
    Integer selectUserNameScore(@Param("userCreate") Long userCreate, @Param("userName") String userName, @Param("suspectedId") Long suspectedId);


    //判断家人是否有历史评分
    @Select("select a.* \n" +
            "from ms_suspected a \n" +
            "INNER JOIN ms_report b on a.report_id=b.report_id \n" +
            "where b.user_create=#{userCreate} and b.report_date=(select report_date from ms_report where user_create=#{userCreate} and report_date<curdate() ORDER BY report_date desc LIMIT 1)\n" +
            "and a.user_name not in (select a.user_name from ms_suspected a INNER JOIN ms_report b on a.report_id=b.report_id where b.user_create=#{userCreate} and b.report_date=curdate())\n" +
            " ORDER by a.time_create  LIMIT 1")
    MsSuspected selectAddSuspected(@Param("userCreate") Long userCreate);

    //获取最近的一个人一个家人
    @Select("select a.*,c.* \n" +
            "from ms_suspected a \n" +
            "INNER JOIN ms_report b on a.report_id=b.report_id \n" +
            "left JOIN ms_trip c on a.suspected_id=c.trip_id \n" +
            "where b.user_create=#{userCreate} and b.report_date=(select report_date from ms_report where user_create=#{userCreate} and report_date<curdate() ORDER BY report_date desc LIMIT 1)\n" +
            "ORDER by a.time_create ")
    List<MsSuspected> selectNextSuspected(@Param("userCreate") Long userCreate);

    //选择家人
    @Select("select a.*,b.* from ms_suspected a LEFT JOIN ms_trip b on a.suspected_id=b.trip_id where a.suspected_id =#{suspectedId}")
    MsSuspected selectBySuspectedId(@Param("suspectedId") Long suspectedId);

    @Select("select * from ms_trip_det  where trip_id =#{suspectedId}")
    List<MsTripDet> selectTripSuspected(@Param("suspectedId") Long suspectedId);
}