programs_info/无锡市生命线项目/数据库备份/删除前备份/代码修改前备份/前置数据库中的视图都移动到后管平台进行的修改/SecuritySuperviseMapper.java

package com.slave.midScreen.mapper;

import com.slave.analysis.model.ZhjgAnalysisEmergency;
import com.slave.analysis.model.ZhjgAnalysisHiddenTrouble;
import com.slave.demo.model.DemoClass;
import com.slave.midScreen.model.in.HangYeTongBaoSelect;
import com.slave.midScreen.model.in.SelectFenXi;
import com.slave.midScreen.model.in.SelectInfo;
import com.slave.midScreen.model.in.SelectInfoDetail;
import com.slave.midScreen.util.DictCode;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import tk.mybatis.mapper.common.Mapper;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Component
public interface SecuritySuperviseMapper {

    @Select("${sql}")
    List<Map<String, Object>> select1(@Param("sql") String sql);

    //    //返回集合
//    @Select("select * from demo_clas WHERE name like '%#{name}%")
//    List<DemoClass> queryName(@Param("name") String name);
    //todo 到时候要处理:处置进度(没有完成的进度)
    @Select("<script> SELECT COALESCE (COUNT (1),0) AS COUNT,trouble.hidden_trouble_grade,trouble.scene FROM zhjg_analysis_hidden_trouble trouble WHERE trouble.dispose_progress in \n" +
            "<foreach collection='gkztCodeList' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach> \n" +
            " GROUP BY trouble.scene,--专项场景_燃气/内涝等\n" +
            "trouble.hidden_trouble_grade </script>")
    List<Map<String, Object>> findTroubleCountList(@Param("gkztCodeList") List<String> gkztCodeList);

    @Select("<script> SELECT COUNT (1) COUNT,trouble.scene FROM zhjg_analysis_hidden_trouble trouble WHERE trouble.dispose_progress in \n" +
            "<foreach collection='gkztCodeList' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach> \n" +
            " AND EXTRACT (EPOCH FROM (now()-trouble.find_time))> (24*60*60)--超时24小时\n" +
            "GROUP BY trouble.scene--专项场景_燃气/内涝等\n" +
            "</script>")
    List<Map<String, Object>> findOverHourTroubleCountList(@Param("gkztCodeList") List<String> gkztCodeList);

    @Select("<script> SELECT COALESCE (COUNT (1),0) AS COUNT,zhjg_analysis_emergency.response_grade,zhjg_analysis_emergency.scene FROM zhjg_analysis_emergency WHERE zhjg_analysis_emergency.dispose_progress in \n" +
            "<foreach collection='gkztCodeList' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach> \n" +
            " GROUP BY zhjg_analysis_emergency.scene,--专项场景_燃气/内涝等\n" +
            "zhjg_analysis_emergency.response_grade--'等级'\n" +
            "</script>")
    List<Map<String, Object>> findEmergencyCountList(@Param("gkztCodeList") List<String> gkztCodeList);

    @Select("<script> SELECT COUNT (1) COUNT,scene FROM zhjg_analysis_emergency WHERE dispose_progress in \n" +
            "<foreach collection='gkztCodeList' index='index' item='item' open='(' separator=',' close=')'>#{item}</foreach> \n" +
            " AND EXTRACT (EPOCH FROM (now()-find_time))> (24*60*60)--超时24小时\n" +
            "GROUP BY scene--专项场景_燃气/内涝等\n" +
            "</script>")
    List<Map<String, Object>> findOverHourEmergencyCountList(@Param("gkztCodeList") List<String> gkztCodeList);

    @MapKey("area")
    List<Map<String, Object>> findCountTroubleEveryPlace(@Param("entity") SelectInfo info, @Param("gkztCodeList") List<String> gkztCodeList);

    @MapKey("area")
    List<Map<String, Object>> findCountEmergencyEveryPlace(@Param("entity") SelectInfo info, @Param("gkztCodeList") List<String> gkztCodeList);

    @MapKey("id")
    List<Map<String, Object>> findInfoTroubleEveryPlace(@Param("entity") SelectInfo info, @Param("gkztCodeList") List<String> gkztCodeList);

    @MapKey("id")
    List<Map<String, Object>> findInfoEmergencyEveryPlace(@Param("entity") SelectInfo info, @Param("gkztCodeList") List<String> gkztCodeList);
    @MapKey("id")
    List<Map<String, Object>> findInfoTroubleDetailEveryPlace(@Param("entity") SelectInfoDetail info, @Param("gkztCodeList") List<String> gkztCodeList);

    List<Map<String, Object>> findInfoEmergencyDetailEveryPlace(@Param("entity") SelectInfoDetail info, @Param("gkztCodeList") List<String> gkztCodeList);

    //    @Select("SELECT*FROM ((\n" +
//            "SELECT*FROM industry_supervision_report WHERE EXTRACT (YEAR FROM release_time)=#{year,jdbcType=INTEGER} AND LEVEL='国家级' AND scene='地下管线' ORDER BY release_time DESC LIMIT 1) UNION ALL (\n" +
//            "SELECT*FROM industry_supervision_report WHERE EXTRACT (YEAR FROM release_time)=#{year,jdbcType=INTEGER} AND LEVEL='江苏省' AND scene='地下管线' ORDER BY release_time DESC LIMIT 1) UNION ALL (\n" +
//            "SELECT*FROM industry_supervision_report WHERE EXTRACT (YEAR FROM release_time)=#{year,jdbcType=INTEGER} AND LEVEL='无锡市' AND scene='地下管线' ORDER BY release_time DESC LIMIT 1)) T")
    @Select("SELECT*FROM industry_supervision_report where scene=#{scene} and state='1' and del_flag=1 ORDER BY release_time DESC LIMIT 3")
    List<Map<String, Object>> findHangYeTongBaoList(@Param("year") Integer year, @Param("scene") String scene);

    @Select("SELECT*FROM industry_supervision_evaluate evaluate WHERE evaluate.del_flag=1 AND evaluate.STATE='1' AND scene=#{ scene } ORDER BY order_num DESC")
    List<Map<String, Object>> findHangYePingJiaList(@Param("scene") String scene);

    @Select("SELECT*FROM social_public_case  where scene=#{scene} ORDER BY release_time DESC LIMIT 3")
    List<Map<String, Object>> findAnLiFenXiList(@Param("year") Integer year,@Param("scene") String scene);

    @MapKey("id")
    List<Map<String, Object>> findShiJianFenXi(@Param("entity") SelectFenXi info);

//    @MapKey("id")
//    List<Map<String, Object>> findYinHuanFenXi(@Param("entity") SelectFenXi info);

    @MapKey("id")
    List<Map<String, Object>> findYinHuanFenXiOther(@Param("entity") SelectFenXi info);

    //    @Select("SELECT COUNT (1) all_month,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=1) AS month1,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=2) AS month2,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=3) AS month3,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=4) AS month4,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=5) AS month5,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=6) AS month6,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=7) AS month7,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=8) AS month8,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=9) AS month9,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=10) AS month10,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=11) AS month11,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=12) AS month12,response_grade FROM zhjg_analysis_emergency WHERE EXTRACT (YEAR FROM find_time)=#{year,jdbcType=INTEGER} AND scene IN ( '燃气','供水','排水' ) GROUP BY response_grade")
//    List<Map<String, Object>> findShiJianFenXiTongJi(@Param("year") Integer year);
    @Select("SELECT COUNT (1) all_count,EXTRACT (YEAR FROM find_time) AS findYear,EXTRACT (MONTH FROM find_time) AS findMonth,response_grade FROM zhjg_analysis_emergency WHERE find_time>=#{start_time,jdbcType=TIMESTAMP} AND find_time< #{end_time,jdbcType=TIMESTAMP} AND scene IN (#{scene}) GROUP BY EXTRACT (YEAR FROM find_time),EXTRACT (MONTH FROM find_time),response_grade")
    List<Map<String, Object>> findShiJianFenXiTongJi(@Param("start_time") Date start_time, @Param("end_time") Date end_time, @Param("scene") String scene);

    @Select("SELECT COUNT (1) all_count,EXTRACT (YEAR FROM find_time) AS findYear,EXTRACT (MONTH FROM find_time) AS findMonth,response_grade FROM zhjg_analysis_emergency WHERE find_time>=#{start_time,jdbcType=TIMESTAMP} AND find_time< #{end_time,jdbcType=TIMESTAMP} GROUP BY EXTRACT (YEAR FROM find_time),EXTRACT (MONTH FROM find_time),response_grade")
    List<Map<String, Object>> findShiJianFenXiTongJiOfYear(@Param("start_time") Date start_time, @Param("end_time") Date end_time);

//    @Select("SELECT COUNT (1) all_month,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=1) AS month1,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=2) AS month2,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=3) AS month3,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=4) AS month4,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=5) AS month5,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=6) AS month6,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=7) AS month7,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=8) AS month8,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=9) AS month9,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=10) AS month10,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=11) AS month11,COUNT (*) FILTER (WHERE EXTRACT (MONTH FROM find_time)=12) AS month12,hidden_trouble_grade FROM zhjg_analysis_hidden_trouble WHERE EXTRACT (YEAR FROM find_time)=#{year,jdbcType=INTEGER} AND scene IN ( '第三方' ) GROUP BY hidden_trouble_grade")
//    List<Map<String, Object>> findYinHuanFenXiTongJi(@Param("year") Integer year);

    @Select("SELECT t2.all_count,t2.project_code,t3.xmmc FROM (\n" +
            "SELECT COUNT (1) AS all_count,T.project_code FROM (\n" +
            "SELECT regexp_split_to_table(hidden_trouble_type,',') hidden_trouble_type_one,*FROM zhjg_analysis_hidden_trouble WHERE EXTRACT (YEAR FROM zhjg_analysis_hidden_trouble.find_time)=#{year,jdbcType=INTEGER} and zhjg_analysis_hidden_trouble.scene in (#{scene})) T GROUP BY T.project_code ORDER BY T.project_code) t2 LEFT JOIN citylifeline.sg_jg_dsfsgxmxx t3 ON t3.bsm=t2.project_code")
    List<Map<String, Object>> findYinHuanFenXiTongJi(@Param("year") Integer year, @Param("scene") String scene);

    @Select("SELECT COUNT (1) all_count,EXTRACT (YEAR FROM find_time) AS findYear,EXTRACT (MONTH FROM find_time) AS findMonth,hidden_trouble_grade FROM zhjg_analysis_hidden_trouble WHERE find_time>=#{ start_time,jdbcType=TIMESTAMP } AND find_time< #{ end_time,jdbcType=TIMESTAMP } AND scene IN (#{scene}) GROUP BY EXTRACT (YEAR FROM find_time),EXTRACT (MONTH FROM find_time),hidden_trouble_grade")
    List<Map<String, Object>> findYinHuanFenXiOtherTongJi(@Param("start_time") Date start_time, @Param("end_time") Date end_time, @Param("scene") String scene);

    @Select("SELECT COUNT (1) all_count,EXTRACT (YEAR FROM find_time) AS findYear,EXTRACT (MONTH FROM find_time) AS findMonth,hidden_trouble_grade FROM zhjg_analysis_hidden_trouble WHERE find_time>=#{ start_time,jdbcType=TIMESTAMP } AND find_time< #{ end_time,jdbcType=TIMESTAMP } GROUP BY EXTRACT (YEAR FROM find_time),EXTRACT (MONTH FROM find_time),hidden_trouble_grade")
    List<Map<String, Object>> findYinHuanFenXiOtherTongJiOfYear(@Param("start_time") Date start_time, @Param("end_time") Date end_time);

    @Select("SELECT COUNT (1) all_count,area,hidden_trouble_grade FROM zhjg_analysis_hidden_trouble WHERE EXTRACT (YEAR FROM find_time)=#{year,jdbcType=INTEGER}  AND scene IN (#{scene} ) GROUP BY area,hidden_trouble_grade ORDER BY area")
    List<Map<String, Object>> findYinHuanFenXiTongJi2(@Param("year") Integer year, @Param("scene") String scene);

    @Select("SELECT COUNT (1) all_count,area,hidden_trouble_grade FROM zhjg_analysis_hidden_trouble WHERE EXTRACT (YEAR FROM find_time)=#{year,jdbcType=INTEGER}  AND scene IN ( #{scene} ) GROUP BY area,hidden_trouble_grade ORDER BY area")
    List<Map<String, Object>> findYinHuanFenXiOtherTongJi2(@Param("year") Integer year, @Param("scene") String scene);

    @Select("SELECT COUNT (1) all_count,area,response_grade FROM zhjg_analysis_emergency WHERE EXTRACT (YEAR FROM find_time)=#{year,jdbcType=INTEGER} AND scene IN ( #{scene} ) GROUP BY area,response_grade ORDER BY area")
    List<Map<String, Object>> findShiJianFenXiTongJi2(@Param("year") Integer year, @Param("scene") String scene);

    @MapKey("response_grade")
    List<Map<String, Object>> findShiJianFenXiTongJi3(@Param("year") Integer year, @Param("place") String place, @Param("scene") String scene);

    @MapKey("all_month")
    List<Map<String, Object>> findShiJianFenXiTongJi4(@Param("year") Integer year, @Param("place") String place, @Param("gkztCodeList") List<String> gkztCodeList, @Param("scene") String scene);

    @MapKey("hidden_trouble_grade")
    List<Map<String, Object>> findYinHuanFenXiTongJi3(@Param("year") Integer year, @Param("place") String place, @Param("scene") String scene);

    @MapKey("hidden_trouble_grade")
    List<Map<String, Object>> findYinHuanFenXiOtherTongJi3(@Param("year") Integer year, @Param("place") String place, @Param("scene") String scene,@Param("grade") String grade);

    @MapKey("all_month")
    List<Map<String, Object>> findYinHuanFenXiTongJi4(@Param("year") Integer year, @Param("place") String place, @Param("gkztCodeList") List<String> gkztCodeList, @Param("scene") String scene);

    @MapKey("all_month")
    List<Map<String, Object>> findYinHuanFenXiOtherTongJi4(@Param("year") Integer year, @Param("place") String place, @Param("gkztCodeList") List<String> gkztCodeList, @Param("scene") String scene);

    @Select("SELECT COUNT (1) all_count,EXTRACT (YEAR FROM T.find_time) AS findYear,EXTRACT (MONTH FROM T.find_time) AS findMonth,T.relevance_facility_one FROM (\n" +
            "SELECT regexp_split_to_table(zhjg_analysis_emergency.relevance_facility,',') AS relevance_facility_one,zhjg_analysis_emergency.*FROM zhjg_analysis_emergency where  scene IN ( #{scene} )) AS T WHERE T.find_time>=#{start_time,jdbcType=TIMESTAMP} AND T.find_time< #{end_time,jdbcType=TIMESTAMP} GROUP BY EXTRACT (YEAR FROM T.find_time),EXTRACT (MONTH FROM T.find_time),T.relevance_facility_one")
    List<Map<String, Object>> findShiJIanFenXiDi3Fang(@Param("start_time") Date start_time, @Param("end_time") Date end_time, @Param("scene") String scene);

    @Select("SELECT COUNT\n" +
            "\t( 1 ) all_count,\n" +
            "\tCOUNT ( * ) FILTER ( WHERE T.area = '320214' ) AS area320214,\n" +
            "\tCOUNT ( * ) FILTER ( WHERE T.area = '320211' ) AS area320211,\n" +
            "\tCOUNT ( * ) FILTER ( WHERE T.area = '320206' ) AS area320206,\n" +
            "\tCOUNT ( * ) FILTER ( WHERE T.area = '320299' ) AS area320299,\n" +
            "\tCOUNT ( * ) FILTER ( WHERE T.area = '320205' ) AS area320205,\n" +
            "\tCOUNT ( * ) FILTER ( WHERE T.area = '320213' ) AS area320213,\n" +
            "\trelevance_facility_one \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tregexp_split_to_table( relevance_facility, ',' ) relevance_facility_one,* \n" +
            "\tFROM\n" +
            "\t\tzhjg_analysis_emergency \n" +
            "\tWHERE\n" +
            "\t\tEXTRACT ( YEAR FROM zhjg_analysis_emergency.find_time ) =#{year}\n" +
            "\t\tAND zhjg_analysis_emergency.scene IN ( #{scene} ) \n" +
            "\t) T \n" +
            "GROUP BY\t\n" +
            "\tT.relevance_facility_one ")
    List<Map<String, Object>> findShiJIanFenXiDi3FangTongJi2(@Param("year") Integer year, @Param("scene") String scene);

    @MapKey("relevance_facility_one")
    List<Map<String, Object>> findShiJIanFenXiDi3FangTongJi3(@Param("year") Integer year, @Param("place") String place, @Param("scene") String scene);

    @MapKey("all_month")
    List<Map<String, Object>> findShiJIanFenXiDi3FangTongJi4(@Param("year") Integer year, @Param("place") String place, @Param("gkztCodeList") List<String> gkztCodeList, @Param("scene") String scene);

    @Select("SELECT t2.*,t3.xmmc FROM (\n" +
            "SELECT COUNT (1) AS all_count,T.project_code FROM (\n" +
            "SELECT*FROM zhjg_analysis_emergency WHERE EXTRACT (YEAR FROM zhjg_analysis_emergency.find_time)=#{year} AND zhjg_analysis_emergency.scene IN (#{scene})) T GROUP BY T.project_code ORDER BY all_count) t2 LEFT JOIN citylifeline.sg_jg_dsfsgxmxx t3 ON t3.bsm=t2.project_code")
    List<Map<String, Object>> findShiJIanFenXiDi3FangTongJiLeft(@Param("year") Integer year, @Param("scene") String scene);

    @Select("SELECT COUNT (1) AS all_count,T.relevance_facility_one FROM (\n" +
            "SELECT regexp_split_to_table(relevance_facility,',') relevance_facility_one,*FROM zhjg_analysis_emergency WHERE EXTRACT (YEAR FROM zhjg_analysis_emergency.find_time)=#{year} AND zhjg_analysis_emergency.scene IN (#{scene})) T GROUP BY T.relevance_facility_one ORDER BY all_count DESC")
    List<Map<String, Object>> findShiJIanFenXiDi3FangTongJiRight(@Param("year") Integer year, @Param("scene") String scene);

    @MapKey("id")
    List<Map<String, Object>> equipmentStatistics(@Param("place") String area, @Param("sceneList") List<String> scene);

    @MapKey("type")
    List<Map<String, Object>> equipmentStatisticsDetail(@Param("place") String area, @Param("sceneList") List<String> scene);

    @MapKey("scene")
    List<Map<String, Object>> equipmentTopStatisticsDetail(@Param("sceneList") List<String> scene);

    @MapKey("id")
    List<Map<String, Object>> findFengXianHistoryList(@Param("entity") SelectFenXi info);
}