@Override
public List<HistoryPractice> getListOfMyPractice(
String TechId, String id, String value, String key, String sqls) {
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<String> list = getMyPracticeIdBaseOnTheTech(TechId);
StringBuilder sql =
new StringBuilder(
"select E_MANAGER_PRACTICE_ID,PRACTICE_NAME ,APU_ID from ers_practice where E_MANAGER_PRACTICE_ID in(");
if (list.size() >= 0) {
int i = 0;
for (String s : list) {
sql.append("?");
++i;
if (i != list.size()) {
sql.append(",");
}
}
sql.append(")");
if (!value.trim().equals(""))
sql.append(" and ").append(key).append(" like '").append(value.trim()).append("%' ");
if (value.trim().equals("")) sql.append(" and PRACTICE_NAME IS NOT NULL ").append(sqls);
else sql.append(" and PRACTICE_NAME IS NOT NULL ").append(sqls);
;
try {
int n = 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 (String s : list) {
ps.setString(++n, s);
}
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);
}
return plist;
} catch (Exception e) {
logger.error("Error While getting my practice based on tech ", 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 plist;
}
@Override
public List<PracticeEntity> getListOfPractice(
String techId, String value, String key, String sqls) {
StringBuilder sql =
new StringBuilder("SELECT E_MANAGER_PRACTICE_ID,PRACTICE_NAME ,APU_ID FROM ers_practice ");
if (!value.trim().equals(""))
sql.append(" where ").append(key).append(" like '").append(value.trim()).append("%' ");
if (value.trim().equals(""))
sql.append(" where PRACTICE_NAME IS NOT NULL ")
.append(" group by E_MANAGER_PRACTICE_ID order by PRACTICE_NAME ")
.append(sqls);
else
sql.append(" and PRACTICE_NAME IS NOT NULL ")
.append(" group by E_MANAGER_PRACTICE_ID order by PRACTICE_NAME ")
.append(sqls);
Connection connection = null;
PreparedStatement ps = null;
PreparedStatement psFav = null;
StringBuilder sqlFav =
new StringBuilder("select ID, E_MANAGER_PID from ers_favpract where TECH_ID = ?");
ResultSet rs = null;
List<PracticeEntity> practiceList = new ArrayList<PracticeEntity>();
List<PracticeEntity> favPracticeList = new ArrayList<PracticeEntity>();
try {
connection = DBManager.getConnection(OneClickConstants.ERS_DS);
ps = connection.prepareStatement(sql.toString());
psFav = connection.prepareStatement(sqlFav.toString());
psFav.setString(1, techId);
rs = psFav.executeQuery();
while (rs.next()) {
PracticeEntity obj = new PracticeEntity(rs.getString("id"), rs.getString("E_MANAGER_PID"));
favPracticeList.add(obj);
}
rs = ps.executeQuery();
while (rs.next()) {
PracticeEntity obj =
new PracticeEntity(
rs.getString("E_MANAGER_PRACTICE_ID"),
rs.getString("PRACTICE_NAME"),
rs.getString("APU_ID"));
for (PracticeEntity ob : favPracticeList) {
if (ob.geteManagerPracticeId().equalsIgnoreCase(obj.getpId())) {
obj.setFavPracticeFlag(1);
obj.setId(ob.getId());
}
}
practiceList.add(obj);
}
} catch (Exception e) {
logger.error("Error While getting the list of practice ", e);
} finally {
try {
if (connection != null) {
connection.close();
}
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (psFav != null) {
psFav.close();
}
} catch (Exception e) {
logger.error("Error while Connection closeing... ");
}
}
return practiceList;
}