private List<CloseTicketEntity> getOpenTaskPracticeCountIdBaseOnTheTech(
String techId, String taskNo, String value) {
StringBuilder sql =
new StringBuilder(
"SELECT ppd.id AS taskid, ppd.`clientid` AS clientid FROM projectplandetails ppd LEFT JOIN project p ON p.`projectid`=ppd.`projectid` LEFT JOIN users u ON u.uid = p.telcallsid ");
sql.append(
" LEFT JOIN users u1 ON u1.uid = ppd.resourceid AND u1.`UserType`=2 WHERE ppd.delflag = 0 AND ppd.status <>-1 AND ppd.status <> 1 AND ppd.`clientid` <> 0 AND ppd.resourceid = ? ");
if (taskNo != null & taskNo.length() > 0)
sql.append(" and ppd.id LIKE '").append(value).append("%'");
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
CloseTicketEntity obj = null;
List<CloseTicketEntity> list = new ArrayList<CloseTicketEntity>();
try {
connection = DBManager.getConnection(OneClickConstants.EMGR_DS);
ps = connection.prepareStatement(sql.toString());
ps.setString(1, techId);
rs = ps.executeQuery();
while (rs.next()) {
obj = new CloseTicketEntity();
;
obj.setTicketId(Helper.chkNull(rs.getString("taskid")));
obj.setPusername(Helper.chkNull(rs.getString("clientid")));
list.add(obj);
}
} catch (Exception e) {
logger.error("Error While getting task id count ", e);
} finally {
try {
if (connection != null) {
connection.close();
}
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
} catch (Exception e) {
logger.error("Error while Connection closeing... ");
}
}
return list;
}
private List<CloseTicketEntity> getOpenTicketPracticeIdBaseOnTheTech(
String techId, String TicketNom, String value) {
StringBuilder sql =
new StringBuilder(
"SELECT t.ticketid as ticketid,t.clientid AS PracticeID FROM tickets t LEFT JOIN users u ON u.uid= t.clientid WHERE t.assignedto = ? ");
if (TicketNom != null && TicketNom.length() > 0)
sql.append(" and ticketid like '").append(value).append("%' ");
sql.append(" AND t.createdDateTime AND t.status NOT IN (2,10,15,20) ");
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
CloseTicketEntity obj = null;
List<CloseTicketEntity> list = new ArrayList<CloseTicketEntity>();
try {
connection = DBManager.getConnection(OneClickConstants.EMGR_DS);
ps = connection.prepareStatement(sql.toString());
ps.setString(1, techId);
rs = ps.executeQuery();
while (rs.next()) {
obj = new CloseTicketEntity();
;
obj.setTicketId(Helper.chkNull(rs.getString("ticketid")));
obj.setPusername(Helper.chkNull(rs.getString("PracticeID")));
list.add(obj);
}
} catch (Exception e) {
logger.error("Error While getting practice user name from emanager database ", e);
} finally {
try {
if (connection != null) {
connection.close();
}
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
} catch (Exception e) {
logger.error("Error while Connection closeing... ");
}
}
return list;
}
@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();
}