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

package com.bofeng.dao;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.baomidou.mybatisplus.mapper.Wrapper;
import com.bofeng.entity.*;
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.Date;
import java.util.List;

@Mapper
@Repository
public interface RbMapper extends BaseMapper<sysUptownUnit> {

    @Select("select DISTINCT c.report_status as reportStatus,a.house_id as houseId,a.doorplate as doorplate,c.report_id as reportId,c.ms_status as msStatus,b.unit_id as unitId from sys_uptown_house a " +
            "inner join sys_uptown_unit b on a.unit_id = b.unit_id " +
            "inner join sys_owner d on d.uptown_id = b.uptown_id " +
            "inner join sys_user_role e on e.property_id = d.owner_id and e.role_id = 2 " +
            "left join ms_report c on c.house_id = a.house_id and DATE_FORMAT(c.time_update,'%Y-%m-%d') = #{dateStr} " +
            "where b.unit_id = #{str}")
    List<SysUptownHouse> selectMp(@Param("str") Long str,@Param("dateStr") String dateStr);
    // and c.time_update = #{date}

    @Select("select c.ridgepole as ridgepole,c.unit as unit,c.unit_id as unitId from sys_user_role a " +
            "inner join sys_owner b on b.owner_id = a.property_id and a.role_id = 2 " +
            "inner join sys_uptown_unit c on c.uptown_id = b.uptown_id " +
            "where a.user_id = #{userId}")
    List<sysUptownUnit> selectDY(@Param("userId") Long userId);


    @Select("select a.normal_num as normalNum,e.ridgepole as ridgepole,e.unit as unit,b.doorplate as doorplate,a.safety_num as safetyNum,a.sure_num as sureNum,a.suspected_num as suspectedNum," +
            "c.user_name as userName,c.cough as cough,c.muscle as muscle,c.dyspnea as dyspnea," +
            "c.fatigue as fatigue,c.diarrhea as diarrhea,c.others as others,c.single_room as singleRoom,a.single_num as singleNum " +
            "from ms_report a " +
            "inner join sys_uptown_house b on b.house_id = a.house_id " +
            "inner join sys_uptown_unit e on e.unit_id = b.unit_id " +
            "left join ms_suspected c on c.report_id = a.report_id " +
            "where b.house_id = #{houseId} and DATE_FORMAT(a.time_update,'%Y-%m-%d') = #{dateStr}")
    List<MsSuspected> selectYcXq(@Param("houseId") Long houseId, @Param("dateStr") String dateStr);

    @Select("select * from " +
            "(" +
            "select count(*) as num,e.uptown_name as uptownName from sys_uptown_house a " +
            "inner join sys_uptown_unit b on b.unit_id = a.unit_id " +
            "inner join sys_uptown e on b.uptown_id = e.uptown_id " +
            "inner join sys_owner c on c.uptown_id = b.uptown_id " +
            "inner join sys_user_role d on d.property_id = c.owner_id and d.role_id = 2 " +
            "where d.user_id = #{userId} " +
            "GROUP BY e.uptown_name " +
            "union all " +
            "select DISTINCT count(*) as num,'' as uptownName from ms_report a " +
            "inner join sys_uptown_house b on a.house_id = b.house_id " +
            "inner join sys_uptown_unit c on c.unit_id = b.unit_id " +
            "inner join sys_owner d on d.uptown_id = c.uptown_id " +
            "inner join sys_user_role e on e.property_id = d.owner_id and e.role_id = 2 " +
            "where a.report_status = 1  and a.report_date = #{reportDate} and e.user_id = #{userId} " +
            "union all " +
            "select DISTINCT count(*) as num,'' as uptownName from ms_report a " +
            "inner join sys_uptown_house b on a.house_id = b.house_id " +
            "inner join sys_uptown_unit c on c.unit_id = b.unit_id " +
            "inner join sys_owner d on d.uptown_id = c.uptown_id " +
            "inner join sys_user_role e on e.property_id = d.owner_id and e.role_id = 2 " +
            "where a.report_status = 1 and a.ms_status = 2 and a.report_date = #{reportDate} and e.user_id = #{userId} " +
            "union all " +
            "SELECT DISTINCT " +
            "sum(a.safety_num) AS yc," +
            "'' as uptownName " +
            "FROM " +
            "ms_report a " +
            "INNER JOIN sys_uptown_house b ON a.house_id = b.house_id " +
            "INNER JOIN sys_uptown_unit c ON c.unit_id = b.unit_id " +
            "INNER JOIN sys_owner d ON d.uptown_id = c.uptown_id " +
            "INNER JOIN sys_user_role e ON e.property_id = d.owner_id " +
            "AND e.role_id = 2 " +
            "WHERE a.report_status = 1 " +
            "AND a.report_date = #{reportDate} " +
            "and e.user_id = #{userId} " +
            "GROUP BY a.house_id " +
            "union all " +
            "SELECT DISTINCT " +
            "sum(a.suspected_num+a.sure_num+a.single_num) AS ycnum," +
            "'' as uptownName " +
            "FROM " +
            "ms_report a " +
            "INNER JOIN sys_uptown_house b ON a.house_id = b.house_id " +
            "INNER JOIN sys_uptown_unit c ON c.unit_id = b.unit_id " +
            "INNER JOIN sys_owner d ON d.uptown_id = c.uptown_id " +
            "INNER JOIN sys_user_role e ON e.property_id = d.owner_id " +
            "AND e.role_id = 2 " +
            "WHERE a.report_status = 1 " +
            "and a.ms_status = 2 " +
            "AND a.report_date = #{reportDate} " +
            "and e.user_id = #{userId} " +
            "GROUP BY a.house_id" +
            ") tt ")
    List<MsSuspected> selecthuiz(@Param("userId") Long userId,@Param("reportDate") String reportDate);

    @Select(
            "select suu.ridgepole,suu.unit,suh.doorplate,suho.linkman,suho.phone,msr.safety_num as safetyNum," +
            "msr.sure_num as sureNum,msr.suspected_num as suspectedNum,msr.normal_num as normalNum," +
                    "msr.single_num as singleNum,mss.user_name as userName,mss.grender," +
            "mss.age,mss.family_status as familyStatus,mss.medical,mss.temperature,mss.cough,mss.muscle ,mss.dyspnea," +
            "mss.fatigue,mss.diarrhea,mss.single_room as singleRoom from ms_suspected mss "+
            "inner join ms_report msr on msr.report_id = mss.report_id and msr.report_date = #{date} "+
            "inner join sys_uptown_house suh on suh.house_id = msr.house_id "+
            "inner join sys_uptown_unit suu on suu.unit_id = suh.unit_id and suu.uptown_id=#{uptownId} "+
            "left join sys_uptown_home suho on suho.house_id = suh.house_id order by doorplate asc"
    )
    List<ExcelRiBao> selectExcelRiBao(@Param("uptownId") Long uptownId, @Param("date") String date);

    @Select(
            "select u.* from sys_user_role ur " +
                    "left join sys_uptown_house up on up.house_id = ur.property_id " +
                    "left join sys_uptown_unit uu on uu.unit_id = up.unit_id " +
                    "left join sys_uptown u on u.uptown_id = uu.uptown_id " +
                    "where ur.role_id = 1 and ur.user_id = #{userId} "
    )
    List<Uptown> selectUptown(@Param("userId") Long userId);
}