Ejemplo n.º 1
0
 /**
  * getCrfListCount, 得到用户投诉总量
  *
  * @param @param sm
  * @param @param imsi
  * @param @return
  * @param @throws Exception
  */
 public int getCrfListCount(SearchModel sm, String statusStr) throws Exception {
   int totalCount = 0;
   try {
     conn = MccCfg.dbPool.getConnection(MccCfg.webdbAlias);
     String cityids = sm.getCityIds();
     String nets = sm.getNetModeLst();
     String imsi = sm.getImsiStr();
     String sql =
         "select count(*) from ex_crf c left join ex_h0_dic h on c.IMEI = h.IMEI where date_format(SUBMIT_TIME, '%Y-%m-%d')"
             + " between date_format(?, '%Y-%m-%d') and date_format(?, '%Y-%m-%d') and CITY_ID in("
             + cityids
             + ") and NETMODE in("
             + nets
             + ") ";
     if (imsi != null && !"".equals(imsi)) {
       sql += " and C.IMSI ='" + imsi + "'";
     }
     sql += " and PRO_STATUS in(" + statusStr + ")";
     prepStmt = conn.prepareStatement(sql);
     prepStmt.setString(1, sm.getBdateStr());
     prepStmt.setString(2, sm.getEdateStr());
     rs = prepStmt.executeQuery();
     while (rs.next()) {
       totalCount = rs.getInt(1);
     }
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     closeAll();
   }
   return totalCount;
 }
Ejemplo n.º 2
0
 /**
  * getCrfList, 得到用户投诉数据
  *
  * @param @param sm
  * @param @return
  * @param @throws Exception
  */
 public ArrayList<UserCrfModel> getCrfList(
     SearchModel sm, String statusStr, int startInd, int rowcount) throws Exception {
   ArrayList<UserCrfModel> crfList = new ArrayList<UserCrfModel>();
   try {
     conn = MccCfg.dbPool.getConnection(MccCfg.webdbAlias);
     String cityids = sm.getCityIds();
     String nets = sm.getNetModeLst();
     String imsi = sm.getImsiStr();
     String sql =
         "select c.ID, c.IMSI, SUBMIT_TIME, TRO_CLASS, TRO_ITEMS,TRO_TIME, TRO_FREQUENCY, TRO_POSITION, TRO_DESC, "
             + "PRO_STATUS, PRO_IDEA,NETMODE, MARSLON, MARSLAT, NETMODENAME,LAC, CI, SID, NID, BID,LOCSOURCE,RXLEV, L_TAC,L_ENBID, "
             + "L_CELLID,L_PCI,L_RSRP,h.TYPELABLE from ex_crf c left join ex_h0_dic h on c.IMEI = h.IMEI where date_format(SUBMIT_TIME, '%Y-%m-%d')"
             + " between date_format(?, '%Y-%m-%d') and date_format(?, '%Y-%m-%d') and CITY_ID in("
             + cityids
             + ") and NETMODE in("
             + nets
             + ") ";
     if (imsi != null && !"".equals(imsi)) {
       sql += " and C.IMSI ='" + imsi + "'";
     }
     sql += " and PRO_STATUS in(" + statusStr + ")";
     sql += " order by SUBMIT_TIME asc limit " + startInd + "," + rowcount;
     prepStmt = conn.prepareStatement(sql);
     prepStmt.setString(1, sm.getBdateStr());
     prepStmt.setString(2, sm.getEdateStr());
     System.out.println(sql);
     rs = prepStmt.executeQuery();
     while (rs.next()) {
       UserCrfModel userCrf = new UserCrfModel();
       userCrf.setID(rs.getLong(1));
       userCrf.setIMSI(rs.getString(2));
       Date submitTime = rs.getTimestamp(3);
       userCrf.setSUBMIT_TIME(DateUtil.toDateString(submitTime, "yyyy-MM-dd HH:mm:ss"));
       userCrf.setTRO_CLASS(rs.getString(4));
       userCrf.setTRO_ITEMS(rs.getString(5));
       String troTime = DateUtil.toDateString(rs.getTimestamp(6), "yyyy-MM-dd HH:mm:ss");
       String troFrequency = rs.getString(7);
       if (troFrequency == null) {
         troFrequency = "-";
       }
       String troPositon = rs.getString(8);
       if (troPositon == null) {
         troPositon = "-";
       }
       String troDesc = rs.getString(9);
       if (troDesc == null) {
         troDesc = "-";
       }
       int proStatus = rs.getInt(10);
       if (proStatus == 1) {
         userCrf.setPRO_STATUS("待处理");
       } else if (proStatus == 2) {
         userCrf.setPRO_STATUS("处理中");
       } else if (proStatus == 3) {
         userCrf.setPRO_STATUS("关闭");
       }
       String proIdea = rs.getString(11);
       String newProIdea = "";
       // 处理意见换行
       newProIdea = splitStr(proIdea, 20);
       userCrf.setPRO_IDEA(newProIdea);
       int netmode = rs.getInt(12);
       userCrf.setNETMODE(netmode);
       String lons = rs.getString(13);
       if (lons == null) {
         lons = "-";
       }
       userCrf.setLONS(lons);
       String lats = rs.getString(14);
       if (lats == null) {
         lats = "-";
       }
       userCrf.setLATS(lats);
       String netmodeName = rs.getString(15);
       String lac = rs.getString(16);
       String ci = rs.getString(17);
       String lacci = "";
       if (lac == null || ci == null) {
         lacci = "-";
       } else {
         lacci = lac + "_" + ci;
       }
       String sidnidbid = "";
       String sid = rs.getString(18);
       String nid = rs.getString(19);
       String bid = rs.getString(20);
       if (sid == null || nid == null || bid == null) {
         sidnidbid = "-";
       } else {
         sidnidbid = sid + "_" + nid + "_" + bid;
       }
       int locs = rs.getInt(21);
       String locsource = "";
       if (locs == 1) {
         locsource = "GPS";
       } else if (locs == 2) {
         locsource = "站址";
       } else if (locs == 3) {
         locsource = "系统网络";
       } else if (locs == 4) {
         locsource = "百度网络";
       }
       String rxlev = rs.getString(22);
       if (rxlev == null) {
         rxlev = "-";
       }
       String tacenbidcellid = "";
       String tac = rs.getString(23);
       String enbid = rs.getString(24);
       String cellid = rs.getString(25);
       String pci = rs.getString(26);
       if (tac == null || enbid == null || cellid == null || pci == null) {
         tacenbidcellid = "-";
       } else {
         tacenbidcellid = tac + "_" + enbid + "_" + cellid + "_" + pci;
       }
       String rsrp = rs.getString(27);
       if (rsrp == null) {
         rsrp = "-";
       }
       String troContent =
           "\r\n发生频率:"
               + troFrequency
               + "\r\n发生时间:"
               + troTime
               + "\r\n发生位置:"
               + troPositon
               + "\r\n详情描述:"
               + troDesc
               + "\r\n网络制式:"
               + netmodeName;
       if (netmode == 1
           || netmode == 2
           || netmode == 7
           || netmode == 8
           || netmode == 20
           || netmode == 21
           || netmode == 23
           || netmode == 24
           || netmode == 26
           || netmode == 27
           || netmode == 29
           || netmode == 30) {
         troContent += "\r\nLAC_CI: " + lacci;
       } else if (netmode == 4 || netmode == 5) {
         troContent += "\r\nSID_NID_BID: " + sidnidbid;
       } else if (netmode == 14
           || netmode == 15
           || netmode == 22
           || netmode == 25
           || netmode == 28
           || netmode == 31) {
         troContent += "\r\nTAC_eNBID_CELLID_PCI: " + tacenbidcellid;
         rxlev = rsrp;
       }
       troContent +=
           "\r\n经度:"
               + userCrf.getLONS()
               + "\r\n纬度:"
               + userCrf.getLATS()
               + "\r\n参考位置:"
               + locsource
               + "\r\n信号强度: "
               + rxlev
               + "(dBm)";
       userCrf.setTRO_CONTENT(troContent);
       userCrf.setTYPELABLE(rs.getString(28));
       crfList.add(userCrf);
     }
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     closeAll();
   }
   return crfList;
 }
Ejemplo n.º 3
0
  /** 得到netmodeKpi统计 */
  public ArrayList<QaKpiModel> getNetmodeKpiList(SearchModel sm) throws Exception {

    ArrayList<QaKpiModel> tl = new ArrayList<QaKpiModel>();
    // ArrayList<QaKpiModel> lst = new ArrayList<QaKpiModel>();
    try {
      String sqlw = "";
      String tableName = "";
      String unionTableName = "";
      if (sm.getDateCycleType() == 0) { // 天
        tableName = "ue_kpi_day";
        sqlw = " days between '" + sm.getBdateStr() + "' and '" + sm.getEdateStr() + "'";
      } else if (sm.getDateCycleType() == 1) { // 周
        tableName = "ue_kpi_week";
        unionTableName = "ue_r0_week";
        sqlw = " week=" + sm.getDateWeekDate();
      } else if (sm.getDateCycleType() == 2) { // 月
        tableName = "ue_kpi_month";
        unionTableName = "ue_r0_month";
        sqlw = " month=" + sm.getDateWeekDate();
      } else sqlw = " 1=1 ";
      StringBuffer sqlStr = new StringBuffer();
      sqlStr.append("SELECT IMSI,2,");
      sqlStr.append("sum(c.E6001) E6001,sum(c.E1002) E1002,sum(c.E6002) E6002,");
      sqlStr.append(
          "sum(c.E1001) E1001,sum(c.En06001) En06001,sum(c.En06002) En06002,sum(c.E6003) E6003,sum(c.E3001) E3001,sum(c.En06003) En06003,sum(c.En06004) En06004,");
      sqlStr.append(
          "sum(c.E6005) E6005,sum(c.E4001) E4001,sum(c.En06005) En06005,sum(c.En06006) En06006,sum(c.En16006) En16006,sum(c.E6007) E6007,sum(c.E6008) E6008,");
      sqlStr.append(
          "sum(c.E2005) E2005,sum(c.En06007) En06007,sum(c.En06008) En06008,sum(c.E2002) E2002,sum(c.E6006) E6006,sum(c.E6004) E6004,sum(c.En06009) En06009,");
      sqlStr.append(
          "sum(c.E6009) E6009,sum(c.E6010) E6010,sum(c.En06010) En06010,sum(c.En16010) En16010,sum(c.E6011) E6011,sum(c.En06011) En06011,sum(c.E6012) E6012,");
      sqlStr.append(
          "sum(c.En06012) En06012,sum(c.E6013) E6013,sum(c.En06013) En06013,null as covrate,null as covnum,null as allnum FROM "
              + tableName
              + " C WHERE ");
      sqlStr.append(sqlw);
      sqlStr.append(" and IMSI = '" + sm.getImsiStr() + "' ");
      //			sqlStr.append(" and city_id in (" + sm.getCityIds()+ ") ");
      //			sqlStr.append(" group by province_name,city_name,netmodename ");

      sqlStr.append(" union ");
      sqlStr.append("select IMSI,2,");
      sqlStr.append(
          "null as E6001,null as E1002,null as E6002,null as E1001,null as En06001,null as En06002,null as E6003,null as E3001,");
      sqlStr.append(
          "null as En06003,null as En06004,null as E6005,null as E4001,null as En06005,null as En06006,null as En16006,null as E6007,");
      sqlStr.append(
          "null as E6008,null as E2005,null as En06007,null as En06008,null as E2002,null as E6006,null as E6004,null as En06009,null as E6009,");
      sqlStr.append(
          "null as E6010,null as En06010,null as En16010,null as E6011,null as En06011,null as E6012,null as En06012,null as E6013,null as En06013,round(sum(covnum)*100/sum(allnum),2) covrate,sum(covnum) covnum,sum(allnum) allnum ");
      sqlStr.append("FROM " + unionTableName + " where ");
      sqlStr.append(sqlw);
      sqlStr.append(" and IMSI = '" + sm.getImsiStr() + "' ");
      //			sqlStr.append(" group by province_name,city_name,netmodename ");

      //			sqlStr.append(" order by CONVERT(netmodename USING gbk)");
      // sqlStr.append(" order by CONVERT(netmodename USING gbk),CONVERT(Province_name USING
      // gbk),CONVERT(City_name USING gbk)");

      System.out.println("sum sql=" + sqlStr.toString());
      prepStmt = conn.prepareStatement(sqlStr.toString());
      rs = prepStmt.executeQuery();
      while (rs.next()) {
        QaKpiModel t = new QaKpiModel();
        t.setREGION(rs.getString(1));
        t.setNETMODE(rs.getString(2));
        //				t.setCityName(rs.getString(4));
        t.setE1001(rs.getInt("E1001"));
        t.setE1002(rs.getInt("E1002"));
        t.setE2002(rs.getInt("E2002"));
        t.setE2005(rs.getInt("E2005"));
        t.setE3001(rs.getInt("E3001"));
        t.setE4001(rs.getInt("E4001"));
        t.setE6001(rs.getInt("E6001"));
        t.setE6002(rs.getInt("E6002"));
        t.setE6003(rs.getInt("E6003"));
        t.setE6004(rs.getInt("E6004"));
        t.setE6005(rs.getInt("E6005"));
        t.setE6006(rs.getInt("E6006"));
        t.setE6007(rs.getInt("E6007"));
        t.setE6008(rs.getInt("E6008"));
        t.setE6009(rs.getInt("E6009"));
        t.setE6010(rs.getInt("E6010"));
        t.setE6011(rs.getInt("E6011"));
        t.setE6012(rs.getInt("E6012"));
        t.setE6013(rs.getInt("E6013"));
        t.setEn06001(rs.getString("En06001"));
        t.setEn06002(rs.getString("En06002"));
        t.setEn06003(rs.getString("En06003"));
        t.setEn06004(rs.getString("En06004"));
        t.setEn06005(rs.getString("En06005"));
        t.setEn06006(rs.getString("En06006"));
        t.setEn06007(rs.getString("En06007"));
        t.setEn06008(rs.getString("En06008"));
        t.setEn06009(rs.getString("En06009"));
        t.setEn06010(rs.getString("En06010"));
        t.setEn06011(rs.getString("En06011"));
        t.setEn06012(rs.getString("En06012"));
        t.setEn06013(rs.getString("En06013"));
        t.setEn16006(rs.getString("En16006"));
        t.setEn16010(rs.getString("En16010"));
        t.setAllNum(rs.getInt("allnum"));
        t.setCovrNum(rs.getInt("covnum"));
        t.setCovrRate(rs.getFloat("covrate"));
        tl.add(t);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll();
    }
    return tl;
  }