hust-epi/whepi-web/src/main/java/com/bofeng/dao/MsReportMapper.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.MsTrip;
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;
import java.util.Map;

/**
 * @Author: xielianghe
 * @Date: 2020/2/4 15:45
 */
@Mapper
@Repository
public interface MsReportMapper extends BaseMapper<MsReport> {

    List<MsReport> selectByReportDate(@Param("reportDate") String reportDate, @Param("userCreate") Long userCreate);

    List<MsReport> selectByReportDateStatus(@Param("reportDate") String reportDate, @Param("userCreate") Long userCreate);

    List<MsReport> selectByReportDateTest(Map<String, Object> queryParam);

    List<MsSuspected> selectByReportDateTestxq(Map<String, Object> queryParam);

    @Select("select * from ms_report where report_date<curdate()  and user_create=#{userCreate} ORDER BY report_date desc LIMIT 1")
    List<MsReport> getNowByYesterdayDate(@Param("userCreate") Long userCreate);

    @Select("select user_create  from ms_report where report_date<curdate() GROUP BY user_create limit #{start}, #{count}")
    List<Long> getUserByYesterdayDate(@Param("start") Long start, @Param("count") Long count);

    @Select("select property_id from sys_user_role where user_id=#{userId} and role_id=1")
    Long selectHouseIdByUserId(@Param("userId") Long userId);

    @Select("select property_id from sys_user_role where user_id=#{userId} and role_id=1")
    List<Long> selectHouseIdsByUserId(@Param("userId") Long userId);

    //确诊
    @Select("select count(suspected_id) from ms_suspected where report_id=#{reportId} and  medical=1")
    Integer selectSuspectedNum(@Param("reportId") Long reportId);

    //隔离
    @Select("select count(suspected_id) from ms_suspected where report_id=#{reportId} and  single_room=1")
    Integer selectSingleRoomNum(@Param("reportId") Long reportId);

    //正常
    @Select("select count(suspected_id) from ms_suspected where report_id=#{reportId} and  suspected_status=0")
    Integer selectisSuspectedNum(@Param("reportId") Long reportId);

    //疑似
    @Select("select count(suspected_id) from ms_suspected where report_id=#{reportId} and suspected_status=1")
    Integer selectisNoSuspectedNum(@Param("reportId") Long reportId);

    //异常
    @Select("select count(suspected_id) from ms_suspected where report_id=#{reportId} and  medical in (1,2,3)")
    Integer selectAbnormalNum(@Param("reportId") Long reportId);

    /**
     * 导出word
     */
    //工作驻地
    @Select("select a.*\n" +
            "from ms_trip a\n" +
            "LEFT JOIN ms_suspected b on a.trip_id=b.suspected_id\n" +
            "LEFT JOIN ms_report c on b.report_id=c.report_id\n" +
            "where c.report_status=1 and c.user_create=#{userId} ORDER BY c.report_date desc LIMIT 1")
    MsTrip selectWorkByUserId(@Param("userId") Long userId);

    //是否离开
    @Select("select a.*\n" +
            "from ms_trip a\n" +
            "LEFT JOIN ms_suspected b on a.trip_id=b.suspected_id\n" +
            "LEFT JOIN ms_report c on b.report_id=c.report_id\n" +
            "where c.report_status=1 and c.user_create=#{userId} and (a.work_local!=a.today_local or a.work_local_other!=a.today_local_other) ORDER BY c.report_date desc LIMIT 1")
    MsTrip selectIsTripByUserId(@Param("userId") Long userId);

    //十五日居住地
    @Select("select * from (\n" +
            "select a.today_local,a.today_local_other\n" +
            "from ms_trip a\n" +
            "LEFT JOIN ms_suspected b on a.trip_id=b.suspected_id\n" +
            "LEFT JOIN ms_report c on b.report_id=c.report_id\n" +
            "where c.report_status=1  and c.user_create=#{userId}  ORDER BY c.report_date desc  LIMIT 15 ) tt GROUP BY tt.today_local,tt.today_local_other")
    List<MsTrip> select15TripByUserId(@Param("userId") Long userId);

    //返回驻地时间
    @Select("select a.report_date,a.report_id,c.today_local,c.today_local_other \n" +
            "from ms_report a \n" +
            "LEFT JOIN ms_suspected b on a.report_id=b.report_id\n" +
            "left join ms_trip c on b.suspected_id=c.trip_id\n" +
            "where (c.work_local =c.today_local or c.work_local_other =c.today_local_other) and c.is_trip=1 and a.report_status=1 and a.user_create=#{userId}\n" +
            "ORDER BY a.report_date desc LIMIT 1")
    MsReport selectBackTripByUserId(@Param("userId") Long userId);

    //返回驻地交通方式
    @Select("select a.trip_type\n" +
            "from ms_trip_det a\n" +
            "left JOIN ms_suspected b on a.trip_id=b.suspected_id where b.report_id=#{reportId} GROUP BY a.trip_type\n")
    List<MsTripDet> selectBackTripByReportrId(@Param("reportId") Long reportId);

    //近5日的
    @Select("select b.temperature\n" +
            "from ms_report a\n" +
            "LEFT JOIN ms_suspected b on a.report_id=b.report_id\n" +
            "where a.report_status=1 and a.user_create=#{userId} and a.report_date=#{reportDate}\n")
    MsSuspected select5ByReportrId(@Param("userId") Long userId, @Param("reportDate") String reportDate);

    //是否有其他症状
    @Select("select b.*\n" +
            "from ms_report a\n" +
            "LEFT JOIN ms_suspected b on a.report_id=b.report_id\n" +
            "where a.report_status=1  and a.user_create=#{userId} and (b.cough>0 or b.muscle>0 or b.dyspnea>0 or b.fatigue>0 or b.diarrhea>0)\n")
    List<MsSuspected> selectOtherSymptomByReportrId(@Param("userId") Long userId);

    //是否确诊
    @Select("select count(a.suspected_id) from ms_suspected a inner join ms_report b on a.report_id=b.report_id where a.medical in (1,2,3) and b.report_status=1 and b.user_create=#{userId}")
    Integer selectAbnormalNumUserId(@Param("userId") Long userId);

    //性别
    @Select("select a.* from ms_suspected a inner join ms_report b on a.report_id=b.report_id where b.report_status=1 and b.user_create=#{userId}")
    List<MsSuspected> selectGrenderUserId(@Param("userId") Long userId);


    /**
     * 最近3天是否都有上报
     *
     * @param userId
     * @return
     */
//    @Select("select * from ms_report where report_status=1 and report_date=date_sub(curdate(),interval 1 day) and user_create=#{userId}\n" +
//            "union\n" +
//            "select * from ms_report where report_status=1 and report_date=date_sub(curdate(),interval 2 day) and user_create=#{userId}\n" +
//            "union\n" +
//            "select * from ms_report where report_status=1 and report_date=date_sub(curdate(),interval 3 day) and user_create=#{userId}\n" +
//            "union\n" +
//            "select * from ms_report where report_status=1 and report_date=curdate() and user_create=#{userId}")
    List<MsReport> selectMsReportLate(@Param("userId") Long userId, @Param("reportDate") String reportDate);

    /**
     * 最近提交的日报, msStatus 健康状态:1正常,2异常
     *
     * @param userId
     * @return
     */
    @Select("select * from ms_report where user_create=#{userId} and report_status=1 order by report_date desc limit 1")
    MsReport selectMsReportToday(@Param("userId") Long userId);

//    @Select("select * from (select * from ms_report where report_status=1 and report_date=date_sub(curdate(),interval 1 day) and user_create=#{userId}\n" +
//            "union select * from ms_report where report_status=1 and report_date=date_sub(curdate(),interval 2 day) and user_create=#{userId}\n" +
//            "union select * from ms_report where report_status=1 and report_date=date_sub(curdate(),interval 3 day) and user_create=#{userId} union select * from ms_report where report_status=1 and report_date=curdate() and user_create=#{userId}) aa where aa.ms_status = 2")
    List<MsReport> selectMsReportThereError(@Param("userId") Long userId, @Param("reportDate") String reportDate);

    @Select("select * from ms_report where report_status=1 and report_date > date_sub(curdate(),interval 29 day) and user_create=#{userId} and ms_status = 2")
    List<MsReport> selectMsReportThirtyError(@Param("userId") Long userId);

    /**
     * 最近15天确诊,疑似,密切接触者
     *
     * @param userId
     * @return
     */
    @Select("select * from ms_report where report_status=1 and report_date > date_sub(curdate(),interval 15 day) and ms_status=2 and user_create=#{userId}")
    List<MsReport> queryMsReportBySure(@Param("userId") Long userId);

    /**
     * 最近是否填过确诊,疑似,密切接触者
     *
     * @param userId
     * @return
     */
    @Select("select a.* from ms_report a LEFT JOIN ms_suspected b on a.report_id=b.report_id where a.report_status=1 and b.medical in (1,2,3) and a.user_create=#{userId} ORDER BY a.time_create DESC LIMIT 1")
    MsReport queryMsReportIsXG(@Param("userId") Long userId);

    /**
     * 家人确诊,疑似,密切接触者
     * @param userId
     * @return
     */
    @Select("select * from ms_report a INNER JOIN ms_suspected b on a.report_id=b.report_id where a.report_status=1 and a.user_create=#{userId} and b.is_contact = 1 ORDER BY a.time_create DESC")
    List<MsReport> queryMsReportIsXGHome(@Param("userId") Long userId);

    /**
     * 最近状态 0无,1确诊,2疑似,3密切接触者,4解除隔离
     *
     * @param userId
     * @return
     */
    @Select("select a.* from ms_report a LEFT JOIN ms_suspected b on a.report_id=b.report_id where a.report_status=1 and a.user_create=#{userId} and b.medical  = 4 and a.report_date > #{reportDate}")
    List<MsReport> queryLateStatus(@Param("userId") Long userId, @Param("reportDate") String reportDate);

    /**
     * 最近去工作驻地的时间
     *
     * @param userId
     * @return
     */
    @Select("select DATE_FORMAT(a.report_date,'%Y-%m-%d') from ms_report a LEFT JOIN ms_suspected b on a.report_id=b.report_id left join ms_trip c on b.suspected_id=c.trip_id where " +
            "(c.work_local =c.today_local or c.work_local_other =c.today_local_other) and c.is_trip=1 and a.report_status=1 and a.user_create=#{userId} ORDER BY a.report_date desc LIMIT 1")
    String returnWork(@Param("userId") Long userId);

    /**
     * 最近去工作驻地的时间
     *
     * @param userId
     * @return
     */
    @Select("select a.* from ms_report a LEFT JOIN ms_suspected b on a.report_id=b.report_id left join ms_trip c on b.suspected_id=c.trip_id where (c.work_local " +
            "=c.today_local or c.work_local_other =c.today_local_other) and c.is_trip=0 and a.report_status=1 and a.user_create=#{userId} and a.report_date>#{reportDate} ORDER BY a.report_date desc LIMIT 15")
    List<MsReport> returnWorkTime(@Param("reportDate") String reportDate, @Param("userId") Long userId);

    /**
     * 当前人员今天的地址
     *
     * @param userId
     * @return
     */
    @Select("SELECT a.*,CASE WHEN c.work_local = 4 THEN c.work_local_other ELSE c.work_local END as workMsg,CASE WHEN c.today_local = 4 THEN c.today_local_other ELSE c.today_local END as todayMsg " +
            "FROM ms_report a LEFT JOIN ms_suspected b ON a.report_id = b.report_id LEFT JOIN ms_trip c ON b.suspected_id = c.trip_id WHERE a.report_status = 1 AND a.user_create = #{userId} ORDER BY a.report_date DESC LIMIT 1")
    MsReport msAddr(@Param("userId") Long userId);

    //导出Ly异常
    @Select("select u.uptown_name,CONCAT(uu.ridgepole,uu.unit) as orgName,uh.linkman as userName,uh.phone,ur.user_id,uh1.doorplate from sys_user_role ur INNER JOIN sys_uptown_home uh on uh.house_id = ur.property_id INNER JOIN sys_uptown_house uh1 on uh1.house_id = ur.property_id INNER JOIN sys_uptown_unit uu on uu.unit_id = uh1.unit_id INNER JOIN sys_uptown u on u.uptown_id = uu.uptown_id where ur.role_id = 1 and u.uptown_id = 1238790987234 ORDER BY uh.linkman,uh1.doorplate")
    List<MsReport> selectByReportSuspected();

    //确诊、疑似、有接触史
    @Select("select count(*)\n" +
            "from ms_report a \n" +
            "INNER JOIN ms_suspected b on a.report_id=b.report_id\n" +
            "where a.report_status=1 and a.user_create=#{userId} and  b.medical=#{medical}")
    Integer selectNum(@Param("userId") Long userId, @Param("medical") Integer medical);

    //家人是否有接触史
    @Select("select count(*)\n" +
            "from ms_report a \n" +
            "INNER JOIN ms_suspected b on a.report_id=b.report_id\n" +
            "where a.report_status=1 and a.user_create=#{userId} and  b.is_contact=1")
    Integer selectFamilyContactNum(@Param("userId") Long userId);

    //病史
    @Select("select DATE_FORMAT(a.report_date,'%Y-%m-%d') as reportDate,b.*\n" +
            "from ms_report a \n" +
            "INNER JOIN ms_suspected b on a.report_id=b.report_id\n" +
            "where a.report_status=1 and a.user_create=#{userId}\n" +
            "and (b.temperature<35 or b.temperature>37.3 or b.cough>0 or b.muscle>0 or b.dyspnea>0 or b.fatigue>0 or b.diarrhea>0)\n" +
            "and a.report_date>date_add(NOW(), interval -1 MONTH)")
    List<MsSuspected> selectSymptomNum(@Param("userId") Long userId);
}