/**
* 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;
}
/**
* 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;
}
/** 得到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;
}