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