@Override
public List<HistoryPractice> getListOfOpenTicketPractice(
String TechId, String id, String value, String key, String sqls, String TicketNom) {
Connection connection = null;
PreparedStatement ps = null;
// PreparedStatement psFav = null;
ResultSet rs = null;
// StringBuilder sqlFav = new StringBuilder("select ID, E_MANAGER_PID from ers_favpract where
// TECH_ID = ?");
// List<PracticeEntity> favPracticeList = new ArrayList<PracticeEntity>();
List<HistoryPractice> plist = new ArrayList<HistoryPractice>();
List<HistoryPractice> finalList = new ArrayList<HistoryPractice>();
List<CloseTicketEntity> list = getOpenTicketPracticeIdBaseOnTheTech(TechId, TicketNom, value);
StringBuilder sql =
new StringBuilder(
"select E_MANAGER_PRACTICE_ID,PRACTICE_NAME ,APU_ID from ers_practice where PRACTICE_NAME IS NOT NULL ");
if (list.size() > 0) {
sql.append(" and E_MANAGER_PRACTICE_ID in(");
int e = 0;
for (CloseTicketEntity s : list) {
sql.append("?");
++e;
if (e != list.size()) {
sql.append(",");
}
}
sql.append(")");
if (!value.trim().equals("") && !key.equalsIgnoreCase("ticketid"))
sql.append(" and ").append(key).append(" like '").append(value.trim()).append("%' ");
if (value.trim().equals("")) sql.append(" ").append(sqls);
else sql.append(" ").append(sqls);
;
try {
int i = 0;
connection = DBManager.getConnection(OneClickConstants.ERS_DS);
// psFav = connection.prepareStatement(sqlFav.toString());
// psFav.setString(1, id);
// rs = psFav.executeQuery();
// while(rs.next())
// {
// PracticeEntity obj = new
// PracticeEntity(rs.getString("id"),rs.getString("E_MANAGER_PID"));
// favPracticeList.add(obj);
// }
ps = connection.prepareStatement(sql.toString());
for (CloseTicketEntity s : list) {
ps.setString(++i, s.getPusername());
}
rs = ps.executeQuery();
while (rs.next()) {
HistoryPractice obj =
new HistoryPractice(
Helper.chkNull(rs.getString("PRACTICE_NAME")),
Helper.chkNull(rs.getString("APU_ID")),
Helper.chkNull(rs.getString("E_MANAGER_PRACTICE_ID")));
// for(PracticeEntity ob : favPracticeList)
// {
// if(ob.geteManagerPracticeId().equalsIgnoreCase(obj.geteManagerPracticeId()))
// {
// obj.setFavPracticeFlag(1);
// obj.setId(ob.getId());
// }
// }
plist.add(obj);
}
for (int k = 0; k < list.size(); k++) {
for (int j = 0; j < plist.size(); j++) {
if ((list.get(k).getPusername().equalsIgnoreCase(plist.get(j).geteManagerPracticeId()))
& list.get(k).getDuplicatFlag() == 'N') {
finalList.add(
new HistoryPractice(
plist.get(j).getPracticeName(),
plist.get(j).getApuId(),
plist.get(j).geteManagerPracticeId(),
list.get(k).getTicketId()));
list.get(k).setDuplicatFlag('Y');
}
}
}
} catch (Exception ex) {
logger.error("Error While getting my practice based on tech ", ex);
} finally {
try {
if (connection != null) {
connection.close();
}
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
} catch (Exception ex) {
logger.error("Error while Connection closeing... ");
}
}
}
return finalList;
}
@Override
public Integer getTotalNoOfOpenTicketPractice(
String TechId, String id, String value, String key, String techId, String TicketNo) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
int count = 0;
List<HistoryPractice> plist = new ArrayList<HistoryPractice>();
List<HistoryPractice> finalList = new ArrayList<HistoryPractice>();
List<CloseTicketEntity> list = getOpenTicketPracticeIdBaseOnTheTech(TechId, TicketNo, value);
StringBuilder sql =
new StringBuilder(
"select E_MANAGER_PRACTICE_ID from ers_practice where PRACTICE_NAME IS NOT NULL ");
if (list.size() > 0) {
sql.append(" and E_MANAGER_PRACTICE_ID in(");
int e = 0;
for (CloseTicketEntity s : list) {
sql.append("?");
++e;
if (e != list.size()) {
sql.append(",");
}
}
sql.append(")");
if (!value.trim().equals("") && !key.equalsIgnoreCase("ticketid"))
sql.append(" and ").append(key).append(" like '").append(value.trim()).append("%' ");
try {
int i = 0;
connection = DBManager.getConnection(OneClickConstants.ERS_DS);
ps = connection.prepareStatement(sql.toString());
for (CloseTicketEntity s : list) {
ps.setString(++i, s.getPusername());
}
rs = ps.executeQuery();
while (rs.next()) {
HistoryPractice obj =
new HistoryPractice(Helper.chkNull(rs.getString("E_MANAGER_PRACTICE_ID")));
plist.add(obj);
}
for (int k = 0; k < list.size(); k++) {
for (int j = 0; j < plist.size(); j++) {
if ((list.get(k).getPusername().equalsIgnoreCase(plist.get(j).getId()))
& list.get(k).getDuplicatFlag() == 'N') {
finalList.add(new HistoryPractice(plist.get(j).getId(), list.get(k).getTicketId()));
list.get(k).setDuplicatFlag('Y');
}
}
}
} catch (Exception ex) {
logger.error("Error While getting open ticket count ", ex);
} finally {
try {
if (connection != null) {
connection.close();
}
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
} catch (Exception ex) {
logger.error("Error while Connection closeing... ");
}
}
}
return finalList.size();
}