package com.bofeng.dao;

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

@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 count(a.house_id) as houseCount\n" +
                    "from sys_uptown_house a \n" +
                    "inner join sys_uptown_home c on a.house_id=c.house_id\n" +
                    "INNER JOIN sys_uptown_unit b ON b.unit_id = a.unit_id and b.uptown_id = #{uptownId}"
    )
    Map<String, Integer> selecthuiz1(@Param("uptownId") Long uptownId);

    @Select(
            "select count(a.report_id) as yichangCount\n" +
                    "from ms_report a \n" +
                    "INNER JOIN sys_uptown_house b ON a.house_id = b.house_id\n" +
                    "INNER JOIN sys_uptown_unit c ON c.unit_id = b.unit_id and c.uptown_id = #{uptownId}\n" +
                    "where a.report_date = #{date} and a.ms_status = 2 and a.report_status = 1"
    )
    Map<String, Integer> selectyicahng(@Param("uptownId") Long uptownId, @Param("date") String date);

    @Select(
            "select sum(suspected_num) as suspectedNum\n" +
                    ", count(a.report_id) as reportCount\n" +
                    "from ms_report a \n" +
                    "INNER JOIN sys_uptown_house b ON a.house_id = b.house_id\n" +
                    "INNER JOIN sys_uptown_unit c ON c.unit_id = b.unit_id and c.uptown_id = #{uptownId}\n" +
                    "where a.report_date = #{date} and a.report_status = 1"
    )
    Map<String, Integer> selecthuiz(@Param("uptownId") Long uptownId, @Param("date") String date);

    @Select(
            "select count(0) AS safetyNum\n" +
                    "from sys_uptown_house suh\n" +
                    "inner join sys_uptown_home suho on suho.house_id=suh.house_id\n" +
                    "INNER JOIN sys_uptown_unit suu ON suu.unit_id = suh.unit_id and suu.uptown_id = #{uptownId}\n" +
                    "left join ms_report msr on msr.house_id = suh.house_id and msr.report_date = #{date}\n" +
                    "left join ms_suspected mss on mss.report_id=msr.report_id"
    )
    Map<String, Integer> selecthuizong(@Param("uptownId") Long uptownId, @Param("date") String date);

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

    List<ExcelRiBaoLY> selectExcelRiBaoLY(@Param("uptownId") Long uptownId, @Param("date") String date, @Param("unitId") Long unitId);

    List<ExcelRiBaoLYBM4> exSelectLingyunUser(Map<String, Object> queryParam);

    List<ExcelRiBaoHCBM4> exSelectHCUser(Map<String, Object> queryParam);

    List<ExcelRiBaoLYBM4> exSelectLingyunUserPage(PageDb pagination, Map<String, Object> queryParam);

    @Select(
            "select suu.ridgepole,suu.unit,suh.doorplate,suho.linkman,suho.phone,msr.safety_num as safetyNum, msr.report_status as reportStatus,msr.ms_status as msStatus,\n" +
                    "msr.sure_num as sureNum,msr.suspected_num as suspectedNum,msr.normal_num as normalNum, msr.report_date as reportDate,\n" +
                    "msr.single_num as singleNum,mss.user_name as userName,mss.grender, mss.age,mss.family_status as familyStatus,\n" +
                    "mss.medical,mss.temperature,mss.cough,mss.muscle ,mss.dyspnea,mss.fatigue,mss.diarrhea,mss.single_room as singleRoom,mss.score_rezult,\n" +
                    "tr.trip_id,tr.is_trip,tr.today_local,tr.today_local_other,tr.work_local,tr.work_local_other, tr.auto_local, tr.auto_addr, ur.user_id as userId\n" +
                    "from ms_report msr\n" +
                    "inner join sys_user_role ur on ur.property_id = msr.house_id and ur.role_id = 1 \n" +
                    "inner join sys_uptown_house suh on suh.house_id = msr.house_id\n" +
                    "inner join sys_uptown_home suho on suho.house_id = msr.house_id\n" +
                    "inner join sys_uptown_unit suu on suu.unit_id = suh.unit_id\n" +
                    "left join ms_suspected mss on mss.report_id=msr.report_id\n" +
                    "left join ms_trip tr on mss.suspected_id=trip_id\n" +
                    "where ur.user_id=#{userId}\n" +
                    "order by msr.report_date desc"
    )
    List<ExcelRiBaoLYBM4> exSelectLingyunUserHistory(PageDb pagination, @Param("userId")Long userId);

    @Select(
            "select suu.ridgepole,suu.unit,suh.doorplate,suho.linkman,suho.phone,msr.safety_num as safetyNum, msr.report_status as reportStatus,\n" +
                    "msr.sure_num as sureNum,msr.suspected_num as suspectedNum,msr.normal_num as normalNum, \n" +
                    "msr.single_num as singleNum,mss.user_name as userName,mss.grender, mss.age,mss.family_status as familyStatus,\n" +
                    "mss.medical,mss.temperature,mss.cough,mss.muscle ,mss.dyspnea,mss.fatigue,mss.diarrhea,mss.single_room as singleRoom,mss.score_rezult ," +
                    "tr.trip_id,tr.is_trip,tr.today_local,tr.today_local_other,tr.work_local,tr.work_local_other, tr.auto_local, tr.auto_addr\n" +
                    "from sys_uptown_house suh\n" +
                    "inner join sys_uptown_home suho on suho.house_id=suh.house_id\n" +
                    "INNER JOIN sys_uptown_unit suu ON suu.unit_id = suh.unit_id and suu.uptown_id=#{uptownId}\n" +
                    "left join ms_report msr on msr.house_id = suh.house_id and msr.report_date = #{date}\n" +
                    "left join ms_suspected mss on mss.report_id=msr.report_id\n" +
                    "left join ms_trip tr on mss.suspected_id=trip_id\n" +
                    "order by suu.unit_id asc "
    )
    List<ExcelRiBaoPrivate> selectExcelRiBaoPrivate(@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);


    @Select("select ur.* from sys_uptown ur where ur.uptown_name = #{uptownName} and ur.status = 1")
    List<Uptown> selectUptownByName(@Param("uptownName") String uptownName);

}