告警跨域修改

81041

2018-12-05 7fdbd065db6a1d67e7649e436073675a2d0a3b6e

 gx_tieta/src/com/fgkj/dao/impl/Battalarm_dataDAOImpl.java

@@ -443,14 +443,14 @@
         String numberSql=" SELECT FOUND_ROWS() number";

         Connection conn=DBUtil.getConn();

         String baseSql = "select SQL_CALC_FOUND_ROWS distinct db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.num,alm_signal_id,db_alarm.tb_battalarm_data.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time  "

               + " ,stationname,battgroupname"

               + " ,stationname,stationname5,battgroupname"

               + " from db_alarm.tb_battalarm_data,db_battinf.tb_battinf  "

               + " where alm_id in(?,?,?,?,?,?,?,?,?,?) and alm_signal_id%2 in(?,?) " +

                "  and alm_level in(?,?,?,?) and alm_cleared_type=0 " +

                "  and db_battinf.tb_battinf.stationname1 like ? and db_battinf.tb_battinf.stationname like ?  and db_battinf.tb_battinf.stationname2 like ?";

                "  and db_battinf.tb_battinf.stationname1 like ? and db_battinf.tb_battinf.stationname like ?  and db_battinf.tb_battinf.stationname2 like ? and db_battinf.tb_battinf.stationname5 like ? ";

         //用于电池组条件

         String idSqlT=" and db_battinf.tb_battinf.battgroupid!=? and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid ";//全部

         String idSqlF=" and db_battinf.tb_battinf.battgroupid=?  and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid ";

         String idSqlT=" and db_battinf.tb_battinf.battgroupid!=? and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid and tb_battinf.station_install=1";//全部

         String idSqlF=" and db_battinf.tb_battinf.battgroupid=?  and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid and tb_battinf.station_install=1 ";

          if(binf.getBattGroupId()==0){

            baseSql+=idSqlT;

         }else{

@@ -493,6 +493,7 @@
                  "%"+binf.getStationName1()+"%",

                  "%"+binf.getStationName()+"%",

                  "%"+binf.getStationName2()+"%",

                  "%"+binf.getStationName5()+"%",

                  binf.getBattGroupId(),

                  uinf.getUId(),

                  

@@ -521,6 +522,7 @@
                           BattInf binf=new BattInf();

                           binf.setBattGroupId(rs.getInt("battGroupId"));

                           binf.setStationName(rs.getString("stationName"));

                           binf.setStationName5(rs.getString("stationName5"));

                           binf.setBattGroupName(rs.getString("battGroupName"));

                           //battinf中的StationName8，StationName9存放告警名称和等级

                           name=AlarmDaoFactory.getAlarmName(adata.getAlm_signal_id());

@@ -548,6 +550,62 @@
         blast.setPage(page);

         list.add(blast);

         

         return list;

      }

   // 3.1电池告警实时查询<***********跨域专用**************>

   public List serchByCondition_ky(Object obj) {

         BattInf binf=(BattInf) obj;

         String sql="";

         String baseSql = "select  distinct db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.num,alm_signal_id,db_alarm.tb_battalarm_data.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time  "

               + " ,stationname,stationname5,battgroupname"

               + " from db_alarm.tb_battalarm_data,db_battinf.tb_battinf  "

               + " where alm_cleared_type=0 ";

         //用于电池组条件

         String idSqlT=" and db_battinf.tb_battinf.battgroupid!=? and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid ";//全部

         String idSqlF=" and db_battinf.tb_battinf.battgroupid=?  and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid ";

          if(binf.getBattGroupId()==0){

            baseSql+=idSqlT;

         }else{

            baseSql+=idSqlF;

         }

         String endSql="order by db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.alm_start_time desc,db_alarm.tb_battalarm_data.monnum asc ";

         sql=baseSql+endSql;

         List  list = DAOHelper.executeQuery(sql,DBUtil.getConn(),new Object[] {binf.getBattGroupId()}, new CallBack() {

                  public List getResults(ResultSet rs) {

                     List list = new ArrayList();

                     try {

                        while (rs.next()) {

                           String name="未知";         //存放告警名称

                           String type="未知";         //存放告警等级

									
                           Battalarm_data adata = new Battalarm_data();

                           adata.setNum(rs.getInt("num"));

                           adata.setBattGroupId(rs.getInt("battGroupId"));

                           adata.setAlm_signal_id(rs.getInt("alm_signal_id"));

                           adata.setMonNum(rs.getInt("monNum"));

                           adata.setAlm_value(rs.getFloat("alm_value"));

                           adata.setAlm_level(rs.getInt("alm_level"));

                           adata.setAlm_is_confirmed(rs.getInt("alm_is_confirmed"));

                           adata.setAlm_confirmed_time(rs.getTimestamp("alm_confirmed_time"));

                           adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));

                           adata.setAlm_end_time(rs.getTimestamp("alm_end_time"));							
									
                           adata.setStationname(rs.getString("stationName"));

                           adata.setBattGroupName(rs.getString("battGroupName"));

                           //battinf中的StationName8，StationName9存放告警名称和等级

                           name=AlarmDaoFactory.getAlarmName(adata.getAlm_signal_id());

                           adata.setAlarmname(name);

                           type=AlarmDaoFactory.getAlarmType(adata.getAlm_level());

                           adata.setAlarmtype(type);

                           list.add(adata);

                        }

                     } catch (SQLException e) {

                        // TODO Auto-generated catch block

                        e.printStackTrace();

                     }

                     return list;

                  }

               });

         return list;

      }

   // 3.2电池告警

@@ -1259,7 +1317,7 @@
      }

      return list;

   }

   //0.5查询实时告警数

   //0.5查询实时告警数(电池告警率)

   /*public List serchAlm(Object obj){

      Batt_Maint_Dealarm bmd=(Batt_Maint_Dealarm) obj;

      Battalarm_data adata=bmd.getAdata();

@@ -1294,25 +1352,21 @@
      return list;   

   }*/

   public List serchAlm(Object obj){

      Batt_Maint_Dealarm bmd=(Batt_Maint_Dealarm) obj;

      Battalarm_data adata=bmd.getAdata();

      BattInf binf=bmd.getBinf();

      User_inf uinf=bmd.getUinf();

      User_inf uinf=(User_inf) obj;

      String sql="select count(num) as number,alm_id " +

            "from db_alarm.tb_battalarm_data  " +

            "where  " +

            "alm_cleared_type=0  " ;

        //根据城市选取

      String citySql=" and tb_battalarm_data.BattGroupId in (select distinct db_battinf.tb_battinf.battgroupid " +

            " from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf   " +

            " where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId   " +

            " and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id  " +

            " and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid  " +

            " and db_user.tb_user_inf.uid=? and db_battinf.tb_battinf.stationname like ?  )  " +

      String citySql=" and tb_battalarm_data.BattGroupId in ( select distinct db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId " +

            " from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr " +

            " where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id  " +

            " and db_user.tb_user_battgroup_baojigroup_usr.uId=? )  " +

            " and tb_battalarm_data.BattGroupId in (select distinct db_battinf.tb_battinf.BattGroupId  from db_battinf.tb_battinf where  station_install=1) " +

            " GROUP BY(alm_id) ";

      sql+=citySql;

      //System.out.println(sql);

      List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId(),"%"+binf.getStationName()+"%"}, new CallBack() {

      List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {

         

         public List getResults(ResultSet rs) {

            List list=new ArrayList();

@@ -1366,14 +1420,14 @@
      String sql="select count(num) as number " +

            "from db_alarm.tb_battalarm_data  " +

            "where  " +

            "alm_cleared_type=0  " +

            "and alm_id not in("+AlarmDaoFactory.Alarm_vol_Online+","+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+","+AlarmDaoFactory.Alarm_res_Conn+","+AlarmDaoFactory.ALM_TYPE_DisChargeMonVol_ID+")"+

            "alm_cleared_type=0  " +//+AlarmDaoFactory.Alarm_vol_Online+","

            "and alm_id not in("+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+","+AlarmDaoFactory.Alarm_res_Conn+","+AlarmDaoFactory.ALM_TYPE_DisChargeMonVol_ID+")"+

            "and db_alarm.tb_battalarm_data.BattGroupId  in (select distinct db_battinf.tb_battinf.battgroupid " +

            " from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf   " +

            " where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId   " +

            " and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id  " +

            " and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid  " +

            " and db_user.tb_user_inf.uid=?  )";

            " and db_user.tb_user_inf.uid=?  and tb_battinf.station_install=1 )";

      //System.out.println(sql);

      List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {

         

@@ -1524,7 +1578,7 @@
      BattMap_information binformation=(BattMap_information) obj;

      String sql="select count(num) as nums  from db_alarm.tb_battalarm_data " +

            "where " +

            "battgroupid in (select distinct battgroupid from db_battinf.tb_battinf where stationid=? ) " +

            "battgroupid in (select distinct battgroupid from db_battinf.tb_battinf where stationid=? and tb_battinf.station_install=1) " +

            "and alm_cleared_type=0 and alm_id not in("+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+") limit 1 ";

      //System.out.println(sql);

      List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binformation.getStationId()}, new CallBack() {

@@ -1773,7 +1827,7 @@
      String sql="select distinct db_alarm.tb_battalarm_data.battgroupid,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationid,db_alarm.tb_battalarm_data.monnum,alm_id,alm_start_time,alm_signal_id " +

            "  from db_alarm.tb_battalarm_data,db_battinf.tb_battinf " +

            "  where alm_cleared_type=0  " +

            "  and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid " +

            "  and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid  and tb_battinf.station_install=1" +

            "  and db_alarm.tb_battalarm_data.BattGroupId  in (select distinct db_battinf.tb_battinf.battgroupid " +

            " from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf   " +

            " where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId   " +

@@ -1814,12 +1868,60 @@
      });

      return list;

   }

   //告警发声(30秒之内的告警)

   public List serchIn30(Object obj){

      User_inf uinf=(User_inf) obj;

      String sql="select distinct db_alarm.tb_battalarm_data.battgroupid,db_battinf.tb_battinf.stationId,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationid,db_alarm.tb_battalarm_data.monnum,alm_id,alm_start_time,alm_signal_id " +

            "  from db_alarm.tb_battalarm_data,db_battinf.tb_battinf " +

            "  where alm_cleared_type=0  " +

            "  and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid  and tb_battinf.station_install=1 " +

            "  and alm_id not in("+AlarmDaoFactory.Alarm_vol_Online+","+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+","+AlarmDaoFactory.Alarm_res_Conn+","+AlarmDaoFactory.ALM_TYPE_DisChargeMonVol_ID+")  " +

            "  and (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(alm_start_time))<"+AlarmDaoFactory.alarm_voice_timelong;

      //用户管理

      String userSql=" and tb_battinf.StationId in(" +

            " select distinct db_user.tb_user_battgroup_baojigroup_battgroup.StationId from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id  and db_user.tb_user_battgroup_baojigroup_usr.uId=?" +

            " ) ";

      sql+=userSql;	
      String orderSql="  order by alm_start_time desc ";

      sql+=orderSql;

      //System.out.println(sql);

      List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {

			
         public List getResults(ResultSet rs) {

            List list=new ArrayList();

            try {

               while(rs.next()){

                  Battalarm_data adata=new Battalarm_data();

                  adata.setNum(0);//0表示电池告警

                  adata.setBattGroupId(rs.getInt("battGroupId"));

                  adata.setStationid(rs.getString("stationid"));

                  adata.setStationname(rs.getString("stationname"));

                  adata.setMonNum(rs.getInt("monNum"));

                  adata.setAlm_id(rs.getInt("alm_id"));

                  adata.setAlm_signal_id(rs.getInt("alm_signal_id"));

                  adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));

                  String adataStr=adata.getStationname()

                        +"在"+ActionUtil.chageDateToString(adata.getAlm_start_time(), ActionUtil.time_yyyyMMddHHmmss)

                        +"出现了"+AlarmDaoFactory.getAlarmName(adata.getAlm_signal_id())

                        +"告警";

                  adata.setNote(adataStr);

                   list.add(adata);

               }

            } catch (SQLException e) {

               // TODO Auto-generated catch block

               e.printStackTrace();

            }

            return list;

         }

      });

      return list;

   }

   //点击项目下方的滚动，查询该条告警的实时信息

   public List  serchBatt_alarm(Object obj){

      Battalarm_data adata=(Battalarm_data) obj;

      String sql="select distinct db_alarm.tb_battalarm_data.battgroupid,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationid,db_alarm.tb_battalarm_data.monnum,alm_id,alm_start_time,alm_signal_id,alm_cleared_type " +

             "  from db_alarm.tb_battalarm_data,db_battinf.tb_battinf " +

             "  where db_battinf.tb_battinf.battgroupid=db_alarm.tb_battalarm_data.battgroupid " +

             "  where db_battinf.tb_battinf.battgroupid=db_alarm.tb_battalarm_data.battgroupid and tb_battinf.station_install=1 " +

             "  and db_alarm.tb_battalarm_data.battgroupid=? and db_alarm.tb_battalarm_data.monnum=? and alm_id=? and alm_start_time=? ";

        List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{adata.getBattGroupId(),adata.getMonNum(),adata.getAlm_id(),adata.getAlm_start_time()}, new CallBack() {

         

@@ -1848,7 +1950,36 @@
      return list;

   }



	
   //查询存在电池告警的机房个数

   public int  searchAlarmInStation(Object obj){

      User_inf uinf=(User_inf) obj;

      String sql=" select count(distinct db_battinf.tb_battinf.stationid) as nums from db_battinf.tb_battinf where  station_install=1 " +

            " and db_battinf.tb_battinf.battgroupid in (select distinct db_alarm.tb_battalarm_data.battgroupid from db_alarm.tb_battalarm_data) " +

            " and db_battinf.tb_battinf.stationid in ( select distinct db_user.tb_user_battgroup_baojigroup_battgroup.stationid " +

            " from db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr " +

            " where db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id=db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id  " +

            " and db_user.tb_user_battgroup_baojigroup_usr.uId=? ) " ;

      List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {

			
         public List getResults(ResultSet rs) {

            List list=new ArrayList();

            try {

               while(rs.next()){

                  list.add(rs.getInt("nums"));

               }

            } catch (SQLException e) {

               // TODO Auto-generated catch block

               e.printStackTrace();

            }

            return list;

         }

      });

      int nums=0;//最后的结果

      if(list!=null&&list.size()>0){

         nums=(Integer) list.get(list.size()-1);

      }

      return nums;

   }

   

   public static void main(String[] args) throws ParseException {

      Battalarm_dataDAOImpl bimp = new Battalarm_dataDAOImpl();

@@ -1916,10 +2047,17 @@
      System.out.println(list.size());*/

       // List list=bimp.serchRealTime(uinf);

       // System.out.println(list);

        List<Integer> list = bimp.serchGood(uinf);

        /*List<Integer> list = bimp.serchGood(uinf);

        for (int num : list) {

         System.out.println(num);

      }*/

        //List list=bimp.serchIn30(uinf);

        //System.out.println(list.size());

        /*int nums=bimp.searchAlarmInStation(uinf);

        System.out.println(nums);*/

        List<Battalarm_data> list = bimp.serchByCondition_ky(binf);

        for (Battalarm_data b : list) {

         System.out.println(b);

      }

        System.out.println(list.size());

   }

}