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 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 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 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 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 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 sceneList = null; if (!StringUtils.isEmpty(scene)){ String[] split = scene.split(","); sceneList=Arrays.asList(split); } return securitySupervisService.equipmentTopStatisticsDetail(sceneList); } }