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

package com.slave.midScreen.controller;

import com.github.pagehelper.PageInfo;
import com.rockstar.frame.model.extend.TableSplitResult;
import com.rockstar.frame.model.extend.Tablepar;
import com.rockstar.util.StringUtils;
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.service.SecuritySupervisService;
import com.slave2.midScreen2.service.SecuritySupervis2Service;
import io.swagger.annotations.Api;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.*;

/**
 * 中屏_安全监管
 */
@Controller
@RequestMapping(value = "/EquipmentSelectController")
@Api(value="中屏_设备和点位信息")
public class EquipmentSelectController {


    @Autowired
    private SecuritySupervisService securitySupervisService;

    @Autowired
    private SecuritySupervis2Service securitySupervis2Service;

//    /**
//     * test
//     * @param
//     * @return
//     */
//    @PostMapping(value = "test",produces = {"application/json;charset=UTF-8"})
//    @ResponseBody
//    public Object test(){
//        String sql="select * from local_dict limit 6";
//        return securitySupervisService.select1(sql);
//
//    }

//大屏中间屏幕跳转点位信息
    //    中间屏幕统计点位不同专项场景的数量
    @PostMapping(value = "pointStatistics",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object pointStatistics(String area,String scene){
        List<String> sceneList = null;
        if (!StringUtils.isEmpty(scene)){
            String[] split = scene.split(",");
            sceneList=Arrays.asList(split);
        }
        return securitySupervis2Service.pointStatistics(area,sceneList);
    }
    //大屏中间屏幕跳转点位信息
    //中间屏幕统计点位信息
    @PostMapping(value = "pointStatisticsDetail",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object pointStatisticsDetail(String area,String scene){
        List<String> sceneList = null;
        if (!StringUtils.isEmpty(scene)){
            String[] split = scene.split(",");
            sceneList=Arrays.asList(split);
        }
        return securitySupervis2Service.pointStatisticsDetail(area,sceneList);
    }


    //大屏中间屏幕跳转基础设备
    //   屏幕中间根据行政区划和所属场景获取基础设备数量
    @PostMapping(value = "equipmentStatistics",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentStatistics(String area,String scene){
        List<String> sceneList = null;
        if (!StringUtils.isEmpty(scene)){
            String[] split = scene.split(",");
            sceneList=Arrays.asList(split);
        }
        return securitySupervisService.equipmentStatistics(area,sceneList);
    }
    //大屏中间屏幕跳转基础设备
    //屏幕中间根据行政区划和所属场景获取基础设备点位信息
    @PostMapping(value = "equipmentStatisticsDetail",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentStatisticsDetail(String area,String scene){
        List<String> sceneList = null;
        if (!StringUtils.isEmpty(scene)){
            String[] split = scene.split(",");
            sceneList=Arrays.asList(split);
        }
        return securitySupervisService.equipmentStatisticsDetail(area,sceneList);
    }


    //左边屏幕燃气监测点根据设备名称分类数量
    @PostMapping(value = "ranqiJIanCedian1",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object ranqiJIanCedian1(){
        String sql="select count(1) count,SSMC from\n" +
                "        citylifeline.RQ_JS_JCD\n" +
                "        GROUP BY SSMC\n" +
                "        order by count";
        return securitySupervis2Service.select2(sql);
    }
    //左边屏幕燃气监测设备根据设备种类分数量
    @PostMapping(value = "ranqiJIanCedian2",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object ranqiJIanCedian2(){
        String sql="select count(1) count,SBLX,jczb from \n" +
                "citylifeline.RQ_JC_JCSB\n" +
                "GROUP BY SBLX,jczb";
        return securitySupervis2Service.select2(sql);
    }



    //左边屏幕供水监测点根据设备名称分类数量
    @PostMapping(value = "gongshuiJIanCedian1",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object gongshuiJIanCedian1(){
        String sql="select count(1) count,SSMC from\n" +
                "        citylifeline.GS_JS_JCD\n" +
                "        GROUP BY SSMC\n" +
                "        order by count";
        return securitySupervis2Service.select2(sql);
    }
    //左边屏幕供水监测设备根据设备种类分数量
    @PostMapping(value = "gongshuiJIanCedian2",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object gongshuiJIanCedian2(){
        String sql="select count(1) count,SBLX,jczb from \n" +
                "citylifeline.GS_JC_JCSB\n" +
                "GROUP BY SBLX,jczb";
        return securitySupervis2Service.select2(sql);
    }


    //左边屏幕排水监测点根据设备名称分类数量
    @PostMapping(value = "paishuiJIanCedian1",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object paishuiJIanCedian1(){
        String sql="select count(1) count,SSMC from\n" +
                "        citylifeline.PS_JS_JCD\n" +
                "        GROUP BY SSMC\n" +
                "        order by count";
        return securitySupervis2Service.select2(sql);
    }
    //左边屏幕排水监测设备根据设备种类分数量
    @PostMapping(value = "paishuiJIanCedian2",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object paishuiJIanCedian2(){
        String sql="select count(1) count,SBLX,jczb from \n" +
                "citylifeline.PS_JC_JCSB\n" +
                "GROUP BY SBLX,jczb";
        return securitySupervis2Service.select2(sql);
    }



    //左边屏幕桥梁监测点根据设备名称分类数量
    @PostMapping(value = "qiaoliangJIanCedian1",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object qiaoliangJIanCedian1(){
        String sql=" SELECT COUNT (1) COUNT,SSMC FROM citylifeline.ql_js_jcd GROUP BY SSMC ORDER BY COUNT";
        return securitySupervis2Service.select2(sql);
    }
    //左边屏幕桥梁监测设备根据设备种类分数量
    @PostMapping(value = "qiaoliangJIanCedian2",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object qiaoliangJIanCedian2(){
        String sql="SELECT COUNT (1) COUNT,SBLX,jczb FROM citylifeline.ql_jc_jcsb GROUP BY SBLX,jczb";
        return securitySupervis2Service.select2(sql);
    }


    //todo 左边屏幕道路监测点根据设备名称分类数量
    @PostMapping(value = "daoluJIanCedian1",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object daoluJIanCedian1(){
        String sql="";
        return null;//securitySupervis2Service.select2(sql);
    }
    //todo 左边屏幕道路监测设备根据设备种类分数量
    @PostMapping(value = "daoluJIanCedian2",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object daoluJIanCedian2(){
        String sql="";
        return null;//securitySupervis2Service.select2(sql);
    }



    //todo 左边屏幕管线监测点根据设备名称分类数量
    @PostMapping(value = "guanxianJIanCedian1",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object guanxianJIanCedian1(){
        String sql="";
        return null;//securitySupervis2Service.select2(sql);
    }
    //todo 左边屏幕管线监测设备根据设备种类分数量
    @PostMapping(value = "guanxianJIanCedian2",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object guanxianJIanCedian2(){
        String sql="";
        return null;//securitySupervis2Service.select2(sql);
    }


    //左边屏幕桥梁监测点根据设备名称分类数量
    @PostMapping(value = "disanfangJIanCedian1",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object disanfangJIanCedian1(){
        String sql=" SELECT COUNT (1) COUNT,SSMC FROM citylifeline.sg_js_jcd GROUP BY SSMC ORDER BY COUNT";
        return securitySupervis2Service.select2(sql);
    }
    //左边屏幕桥梁监测设备根据设备种类分数量
    @PostMapping(value = "disanfangJIanCedian2",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object disanfangJIanCedian2(){
        String sql="SELECT COUNT (1) COUNT,SBLX,jczb FROM citylifeline.sg_jc_jcsb GROUP BY SBLX,jczb";
        return securitySupervis2Service.select2(sql);
    }


//    根据点位信息id和类型type查看监测点位详细
    @PostMapping(value = "jianCedianInfoByIdAndType",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object jianCedianInfoByIdAndType(String id,String type){
        String sql="SELECT*FROM citylifeline.view_jcd_collect WHERE BSM='"+id+"' AND TYPE='"+type+"'";
        return securitySupervis2Service.select2(sql);
    }
    // 根据点位信息id和类型type查看监测点位关联设备列表
    @PostMapping(value = "jianCedianListByIdAndType",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object jianCedianListByIdAndType(String id,String type){
//        -- 0-燃气爆炸，1-城市内涝，2-供水爆管，3-第三方破坏，4-地下管线，5-桥梁倒塌，6-道路塌陷，
        String tableName=null;
        String tableName2=null;
        if (type.equals("0")) {
            tableName="RQ_JC_JCSB";
            tableName2="RQ_JC_JCXYZ";
        } else if (type.equals("1")) {
            tableName="PS_JC_JCSB";
            tableName2="PS_JC_JCXYZ";
        } else if (type.equals("2")) {
            tableName="GS_JC_JCSB";
            tableName2="GS_JC_JCXYZ";
        } else if (type.equals("3")) {
            tableName="sg_jc_jcsb";
            tableName2="sg_jc_jcxyz";
        } else if (type.equals("4")) {
            // todo
            tableName="";
            tableName2="";
        } else if (type.equals("5")) {
            tableName="ql_jc_jcsb";
            tableName2="ql_jc_jcxyz";
        } else if (type.equals("6")) {
            // todo
            tableName="";
            tableName2="";
        }
        String sql = "SELECT mytable.*,mytable2.bjyz FROM citylifeline."+tableName+" mytable LEFT JOIN citylifeline."+tableName2+" mytable2 ON mytable2.SBBSM=mytable.BSM WHERE mytable.DWBSM='" + id + "'";

        return securitySupervis2Service.select2(sql);
    }


    // =======================================================================================================================================================
//SELECT
//COUNT
// ( 1 ) FILTER ( WHERE scene = '1' AND facility_type = '2' ) AS typ1_count,--天然气场站数量
//
// COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND facility_type = '0' ), 0 ) AS typ2_count, --天然气管线公里数
//
//
// COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND facility_type = '1' ), 0 ) AS typ3_count, --天然气管道阀门井数量
//
//  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND facility_type = '10' ), 0 ) AS typ4_count, --天然气储备站充装台数量
//
//
//  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND facility_type = '9' ), 0 ) AS typ5_count, --液化气场站液化瓶数量
//
// COUNT ( 1 ) FILTER ( WHERE scene = '1' AND facility_type = '9' ) AS typ6_count--液化气场站数量
//
//FROM
// zhjg_analysis_facility
    // 屏幕左下角数据(燃气类数据)
    @PostMapping(value = "ranqiEquipmentInfo",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object ranqiEquipmentInfo(){
        String sql="SELECT \n" +
                "COUNT\n" +
                " ( 1 ) FILTER ( WHERE scene = '1' AND facility_type = '2' ) AS typ1_count,--天然气场站数量\n" +
                " \n" +
                " COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND facility_type = '0' ), 0 ) AS typ2_count, --天然气管线公里数\n" +
                " \n" +
                " \n" +
                " COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND facility_type = '1' ), 0 ) AS typ3_count, --天然气管道阀门井数量\n" +
                " \n" +
                "  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND facility_type = '10' ), 0 ) AS typ4_count, --天然气储备站充装台数量\n" +
                "\n" +
                " \n" +
                "  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND facility_type = '9' ), 0 ) AS typ5_count, --液化气场站液化瓶数量\n" +
                "  \n" +
                " COUNT ( 1 ) FILTER ( WHERE scene = '1' AND facility_type = '9' ) AS typ6_count--液化气场站数量\n" +
                " \n" +
                "FROM\n" +
                " zhjg_analysis_facility";
        return securitySupervis2Service.select2(sql);
    }

    // 屏幕左下角数据(内涝)
    @PostMapping(value = "paishuiEquipmentInfo",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object paishuiEquipmentInfo(){
        String sql="SELECT  \n" +
                " COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND facility_type = '5' ), 0 ) AS typ1_count, --内涝雨水管网公里数\n" +
                " COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND facility_type = '雨水明渠' ), 0 ) AS typ2_count, --雨水明渠公里数\n" +
                " COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND facility_type = '合流制排水明渠' ), 0 ) AS typ3_count, --合流制排水明渠公里数\n" +
                "  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND facility_type = '主要河道' ), 0 ) AS typ4_count, --主要河道公里数\n" +
                "  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND facility_type = '行泄通道' ), 0 ) AS typ5_count, --行泄通道公里数\n" +
                " COUNT( 1 ) FILTER ( WHERE scene = '2' AND facility_type = '调蓄池' ) AS typ6_count,--调蓄池数量\n" +
                " \n" +
                " \n" +
                "  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND facility_type = '污水管网' ), 0 ) AS typ7_count, --内涝污水管网公里数\n" +
                " COUNT( 1 ) FILTER ( WHERE scene = '2' AND facility_type = '污水泵站' ) AS typ8_count,--污水泵站数量\n" +
                "  COUNT( 1 ) FILTER ( WHERE scene = '2' AND facility_type = '污水厂' ) AS typ9_count,--污水厂站数量\n" +
                " COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND facility_type = '重点排水户' ), 0 ) AS typ10_count, --重点排水户多少家\n" +
                " \n" +
                "  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND facility_type = '污水处理能力' ), 0 ) AS typ11_count, --污水处理能力 万m3/日\n" +
                "  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND facility_type = '出场水质达标率' ), 0 ) AS typ12_count --出场水质达标总和,到时候还要除以污水厂数量\n" +
                " \n" +
                " \n" +
                "FROM\n" +
                " zhjg_analysis_facility";
        return securitySupervis2Service.select2(sql);
    }
    // 屏幕左下角数据(供水)
    @PostMapping(value = "gongshuiEquipmentInfo",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object gongshuiEquipmentInfo(){
        String sql="SELECT  \n" +
                " COUNT( 1 ) FILTER ( WHERE scene = '3' AND facility_type = '原水厂' ) AS typ1_count,--原水厂数量\n" +
                " \n" +
                "  COUNT( 1 ) FILTER ( WHERE scene = '3' AND facility_type = '自来水厂' ) AS typ2_count,--自来水厂数量\n" +
                "\n" +
                " COUNT( 1 ) FILTER ( WHERE scene = '3' AND facility_type = '增压站' ) AS typ3_count,--增压站数量\n" +
                "\n" +
                " COUNT( 1 ) FILTER ( WHERE scene = '3' AND facility_type = '二次供水房' ) AS typ4_count,--二次供水房数量 \n" +
                " \n" +
                "  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '3' AND facility_type = '核心管网' ), 0 ) AS typ5_count, --核心管网条数\n" +
                "  \n" +
                "  COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '3' AND facility_type = '老旧管网' ), 0 ) AS typ6_count, --老旧管网条数\n" +
                "\n" +
                " COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '3' AND facility_type = '管线长度' ), 0 ) AS typ7_count, --管线长度公里数\n" +
                " \n" +
                "  COUNT( 1 ) FILTER ( WHERE scene = '3' AND facility_type = '管点' ) AS typ8_count--管点个数\n" +
                "\n" +
                "FROM\n" +
                " zhjg_analysis_facility";
        return securitySupervis2Service.select2(sql);
    }
    // todo 屏幕左下角数据(桥梁)
    @PostMapping(value = "qiaoliangEquipmentInfo",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object qiaoliangEquipmentInfo(){
        String sql="";
        return securitySupervis2Service.select2(sql);
    }
    // todo 屏幕左下角数据(道路)
    @PostMapping(value = "daoluEquipmentInfo",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object daoluEquipmentInfo(){
        String sql="";
        return securitySupervis2Service.select2(sql);
    }
    // todo 屏幕左下角数据(地下管线)
    @PostMapping(value = "guanxianEquipmentInfo",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object guanxianEquipmentInfo(){
        String sql="";
        return securitySupervis2Service.select2(sql);
    }
    // 屏幕左下角数据(施工项目)
    @PostMapping(value = "disanfangEquipmentInfo",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object disanfangEquipmentInfo(){
        String sql="SELECT  \n" +
                " COUNT( 1 ) typ_count,facility_type\n" +
                "FROM\n" +
                " zhjg_analysis_facility\n" +
                " where \n" +
                " scene = '4'\n" +
                " GROUP BY facility_type";
        return securitySupervis2Service.select2(sql);
    }



    // 设备大屏数据接口===============================================================================================================


    /**
     *
     SELECT COUNT
     ( 1 ) FILTER ( WHERE scene = '1' and facility_type='2' ) AS scene1,--燃气场景
     COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND measuring_unit = '1' ), 0 ) AS sum1_1,--多少个/瓶/条
     COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND facility_type = '0'  ), 0 ) AS sum1_2,--多少公里
     COUNT ( 1 ) FILTER ( WHERE scene = '2' and facility_type='4') AS scene2,--内涝场景
     COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND measuring_unit = '0' ), 0 ) AS sum2_2,--多少公里
     COUNT ( 1 ) FILTER ( WHERE scene = '3' and facility_type='3') AS scene3,--供水场景
     COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '3' AND measuring_unit = '1' ), 0 ) AS sum3_1,--多少个/瓶/条
     COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '3' AND measuring_unit = '0' ), 0 ) AS sum3_2,--多少公里
     COUNT ( 1 ) FILTER ( WHERE scene = '4' ) AS scene4,--第三方场景
     COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '4' AND measuring_unit = '1' ), 0 ) AS sum4_1,--多少个/瓶/条
     COUNT ( 1 ) FILTER ( WHERE scene = '5' ) AS scene5,--地下管线场景
     COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '5' AND measuring_unit = '0' ), 0 ) AS sum5_2,--多少公里
     COUNT ( 1 ) FILTER ( WHERE scene = '6' ) AS scene6,--桥梁场景
     COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '6' AND measuring_unit = '0' ), 0 ) AS sum6_2,--多少公里
     COUNT ( 1 ) FILTER ( WHERE scene = '7' ) AS scene7,--道路场景
     COALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '7' AND measuring_unit = '0' ), 0 ) AS sum7_2 --多少公里

     FROM
     zhjg_analysis_facility
     * @return
     */
    // 大屏左上边设备设施统计
    @PostMapping(value = "equipmentLeftTopInfo",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentLeftTopInfo(){
        String sql="SELECT COUNT\n" +
                "\t( 1 ) FILTER ( WHERE scene = '1' AND facility_type = '2' ) AS scene1,\n" +
                "\tCOALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND measuring_unit = '1' ), 0 ) AS sum1_1,\n" +
                "\tCOALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '1' AND facility_type = '0' ), 0 ) AS sum1_2,\n" +
                "\tCOUNT ( 1 ) FILTER ( WHERE scene = '2' AND facility_type = '4' ) AS scene2,\n" +
                "\tCOALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '2' AND measuring_unit = '0' ), 0 ) AS sum2_2,\n" +
                "\tCOUNT ( 1 ) FILTER ( WHERE scene = '3' AND facility_type = '3' ) AS scene3,\n" +
                "\tCOALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '3' AND measuring_unit = '1' ), 0 ) AS sum3_1,\n" +
                "\tCOALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '3' AND measuring_unit = '0' ), 0 ) AS sum3_2,\n" +
                "\tCOUNT ( 1 ) FILTER ( WHERE scene = '4' ) AS scene4,\n" +
                "\tCOALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '4' AND measuring_unit = '1' ), 0 ) AS sum4_1,\n" +
                "\tCOUNT ( 1 ) FILTER ( WHERE scene = '5' ) AS scene5,\n" +
                "\tCOALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '5' AND measuring_unit = '0' ), 0 ) AS sum5_2,\n" +
                "\tCOUNT ( 1 ) FILTER ( WHERE scene = '6' ) AS scene6,\n" +
                "\tCOALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '6' AND measuring_unit = '0' ), 0 ) AS sum6_2,\n" +
                "\tCOUNT ( 1 ) FILTER ( WHERE scene = '7' ) AS scene7,\n" +
                "\tCOALESCE ( SUM ( facility_quantity ) FILTER ( WHERE scene = '7' AND measuring_unit = '0' ), 0 ) AS sum7_2 \n" +
                "FROM\n" +
                "\tzhjg_analysis_facility";
        return securitySupervisService.select1(sql);
    }


    /**
     select  t1.a type_count,
     t2.a all_count,
     t2.b all_line_count
     from
     (
     SELECT count(1) a   from (
     SELECT
     SBLX
     FROM  citylifeline.RQ_JC_JCSB
     GROUP BY SBLX) t) t1,(SELECT
     count(1) a,
     count(1) filter(where SBZT in(2,3)) b
     FROM  citylifeline.RQ_JC_JCSB) t2
     * @return
     */
    // 大屏左边监测设备燃气爆炸统计
    @PostMapping(value = "equipmentLeftBottomInfoRanQi",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentLeftBottomInfoRanQi(){
        String sql="select  t1.a type_count,\n" +
                " t2.a all_count,\n" +
                "t2.b all_line_count\n" +
                "from \n" +
                "(\n" +
                "SELECT count(1) a   from (\n" +
                "SELECT \n" +
                "SBLX\n" +
                "FROM  citylifeline.RQ_JC_JCSB\n" +
                "GROUP BY SBLX) t) t1,(SELECT \n" +
                "count(1) a,\n" +
                "count(1) filter(where SBZT in(2,3)) b\n" +
                "FROM  citylifeline.RQ_JC_JCSB) t2";
        return securitySupervis2Service.select2(sql);
    }

    // 大屏左边监测设备排水/内涝统计
    @PostMapping(value = "equipmentLeftBottomInfoPaiShui",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentLeftBottomInfoPaiShui(){
        String sql="select  t1.a type_count,\n" +
                " t2.a all_count,\n" +
                "t2.b all_line_count\n" +
                "from \n" +
                "(\n" +
                "SELECT count(1) a   from (\n" +
                "SELECT \n" +
                "SBLX\n" +
                "FROM  citylifeline.PS_JC_JCSB\n" +
                "GROUP BY SBLX) t) t1,(SELECT \n" +
                "count(1) a,\n" +
                "count(1) filter(where SBZT in(2,3)) b\n" +
                "FROM  citylifeline.PS_JC_JCSB) t2";
        return securitySupervis2Service.select2(sql);
    }

    //大屏左边监测设备供水统计
    @PostMapping(value = "equipmentLeftBottomInfoGongShui",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentLeftBottomInfoGongShui(){
        String sql="select  t1.a type_count,\n" +
                " t2.a all_count,\n" +
                "t2.b all_line_count\n" +
                "from \n" +
                "(\n" +
                "SELECT count(1) a   from (\n" +
                "SELECT \n" +
                "SBLX\n" +
                "FROM  citylifeline.GS_JC_JCSB\n" +
                "GROUP BY SBLX) t) t1,(SELECT \n" +
                "count(1) a,\n" +
                "count(1) filter(where SBZT in(2,3)) b\n" +
                "FROM  citylifeline.GS_JC_JCSB) t2";
        return securitySupervis2Service.select2(sql);
    }
    //todo 大屏左边监测设备地下管线统计
    @PostMapping(value = "equipmentLeftBottomInfoGuanXian",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentLeftBottomInfoGuanXian(){
        String sql="";
        return securitySupervis2Service.select2(sql);
    }
    // 大屏左边监测设备桥梁统计
    @PostMapping(value = "equipmentLeftBottomInfoQiaoLiang",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentLeftBottomInfoQiaoLiang(){
        String sql="select  t1.a type_count,\n" +
                " t2.a all_count,\n" +
                "t2.b all_line_count\n" +
                "from \n" +
                "(\n" +
                "SELECT count(1) a   from (\n" +
                "SELECT \n" +
                "SBLX\n" +
                "FROM  citylifeline.ql_jc_jcsb\n" +
                "GROUP BY SBLX) t) t1,\n" +
                "\n" +
                "(SELECT \n" +
                "count(1) a,\n" +
                "count(1) filter(where SBZT in(2,3)) b\n" +
                "FROM  citylifeline.ql_jc_jcsb) t2\n";
        return securitySupervis2Service.select2(sql);
    }
// todo   大屏左边监测设备道路塌陷统计
    @PostMapping(value = "equipmentLeftBottomInfoDaoLu",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentLeftBottomInfoDaoLu(){
        String sql="";
        return securitySupervis2Service.select2(sql);
    }
//    大屏左边监测设备第三方统计
    @PostMapping(value = "equipmentLeftBottomInfoDiSanFang",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentLeftBottomInfoDiSanFang(){
        String sql="select  t1.a type_count,\n" +
                " t2.a all_count,\n" +
                "t2.b all_line_count\n" +
                "from \n" +
                "(\n" +
                "SELECT count(1) a   from (\n" +
                "SELECT \n" +
                "SBLX\n" +
                "FROM  citylifeline.sg_jc_jcsb\n" +
                "GROUP BY SBLX) t) t1,(SELECT \n" +
                "count(1) a,\n" +
                "count(1) filter(where SBZT in(2,3)) b\n" +
                "FROM  citylifeline.sg_jc_jcsb) t2\n";
        return securitySupervis2Service.select2(sql);
    }

    /**
     SELECT
     count(1) all_count, --风险总数
     count(1) filter(where dispose_progress in('2')) as has_close_count, --已处置总数
     fxlx --风险等级
     FROM  citylifeline.view_risk_collect
     where
     EXTRACT ( YEAR FROM pgsj ) = '2011这个要填的'
     GROUP BY fxlx
     */
// 大屏左边第二个上边 风险分析
    @PostMapping(value = "fenxianTotal",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object fenxianTotal(String year){
        String sql="SELECT \n" +
                "     count(1) all_count, --风险总数\n" +
                "     count(1) filter(where dispose_progress in('2')) as has_close_count, --已处置总数\n" +
                "     fxdj --风险等级\n" +
                "     FROM  citylifeline.view_risk_collect\n" +
                "     where \n" +
                "     EXTRACT ( YEAR FROM pgsj ) = '"+year+"' \n" +
                "     GROUP BY fxdj";
        return securitySupervis2Service.select2(sql);
    }


    //大屏左边第二个下边 突发事件处置率,已上报,已处置
    @PostMapping(value = "shijianChuZhiLu",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object shijianChuZhiLu(String year){
        String sql="SELECT \n" +
                "count(1) all_count, --风险总数\n" +
                "count(1) filter(where dispose_progress in('2')) as has_close_count, --已处置总数\n" +
                "response_grade --风险等级\n" +
                "FROM  zhjg_analysis_emergency\n" +
                "where \n" +
                "EXTRACT ( YEAR FROM find_time ) = '"+year+"' \n" +
                "GROUP BY response_grade";
        return securitySupervis2Service.select2(sql);
    }

    /**
     SELECT COUNT
     ( 1 ),--数量
     xzqhdm,--行政代码
     TYPE --场景类型1-燃气爆炸，2-城市内涝，3-供水爆管，4-第三方破坏，5-地下管线，6-桥梁倒塌，7-道路塌陷

     FROM
     citylifeline.view_jcd_collect
     GROUP BY
     xzqhdm,
     TYPE
     ORDER BY
     xzqhdm
     * @param type
     * @return
     */
    // 大屏中间选中监测点位后，区域图显示各区的各场景的监测点位数量
    @PostMapping(value = "jiancedianInfoCount",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object jiancedianInfoCount(String type){
        String sql="SELECT COUNT\n" +
                " ( 1 ),--数量\n" +
                " xzqhdm,--行政代码\n" +
                " TYPE --场景类型1-燃气爆炸，2-城市内涝，3-供水爆管，4-第三方破坏，5-地下管线，6-桥梁倒塌，7-道路塌陷\n" +
                " \n" +
                "FROM\n" +
                " citylifeline.view_jcd_collect \n" +
                "GROUP BY\n" +
                " xzqhdm,\n" +
                "TYPE \n" +
                "ORDER BY\n" +
                " xzqhdm";
        return securitySupervis2Service.select2(sql);
    }

    /**
     SELECT COUNT
     ( 1 ),--数量
     area,--行政代码
     scene --场景类型1-燃气爆炸，2-城市内涝，3-供水爆管，4-第三方破坏，5-地下管线，6-桥梁倒塌，7-道路塌陷
     FROM
     zhjg_analysis_facility
     GROUP BY
     area,
     scene
     ORDER BY
     area
     * @param type
     * @return
     */
    // 大屏中间选中基础设备后的信息
    @PostMapping(value = "shebeisheshiInfoCount",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object shebeisheshiInfoCount(String type){
        String sql="SELECT COUNT\n" +
                " ( 1 ),--数量\n" +
                " area,--行政代码\n" +
                " scene --场景类型1-燃气爆炸，2-城市内涝，3-供水爆管，4-第三方破坏，5-地下管线，6-桥梁倒塌，7-道路塌陷 \n" +
                "FROM\n" +
                " zhjg_analysis_facility\n" +
                "GROUP BY\n" +
                " area,\n" +
                "scene \n" +
                "ORDER BY\n" +
                " area";
        return securitySupervisService.select1(sql);
    }




    //大屏中间屏幕跳转点位信息
//    屏幕中间根据行政区划和所属场景获取点位信息
    @PostMapping(value = "pointTopStatisticsDetail",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object pointTopStatisticsDetail(String scene){
        List<String> sceneList = null;
        if (!StringUtils.isEmpty(scene)){
            String[] split = scene.split(",");
            sceneList=Arrays.asList(split);
        }
        return securitySupervis2Service.pointTopStatisticsDetail(sceneList);
    }




    //大屏中间屏幕跳转基础设备
    //屏幕中间根据行政区划和所属场景获取基础设备点位信息
    @PostMapping(value = "equipmentTopStatisticsDetail",produces = {"application/json;charset=UTF-8"})
    @ResponseBody
    public Object equipmentTopStatisticsDetail(String scene){
        List<String> sceneList = null;
        if (!StringUtils.isEmpty(scene)){
            String[] split = scene.split(",");
            sceneList=Arrays.asList(split);
        }
        return securitySupervisService.equipmentTopStatisticsDetail(sceneList);
    }

}