private boolean validateLabelMaster(RELabelSetupMainDetails labelTypeMaster) throws Exception {
Connection con = null;
MessagesUtil msgUtil = null;
String scrIdChek = null;
try {
con = TagLibConnectionUtil.getConnection();
msgUtil = new MessagesUtil();
if ("ADD".equals(labelTypeMaster.getOperation())) {
scrIdChek = "SELECT 'X' FROM SOFTWARE_SERVICES SR WHERE SR.SCREEN_ID = ?";
if (QueryBuilderUtil.isRecordExits(con, scrIdChek, labelTypeMaster.getScrnId())) {
msgUtil.put(
TagLibConstants.ERROR_LEVEL,
"Screen Id already Exists--> " + labelTypeMaster.getScrnId());
}
}
if (msgUtil.isEmpty()) {
return true;
} else {
labelTypeMaster.setErrMsgs(msgUtil.getErrMsgs());
labelTypeMaster.setSucessMsgs(msgUtil.getSucessMsgs());
return false;
}
} finally {
TagLibConnectionUtil.closeConnection(con);
msgUtil = null;
scrIdChek = null;
}
}
public void doResetSequences() throws Exception {
Connection con = null;
try {
con = TagLibConnectionUtil.getConnection();
resetSequence("SOFTWARE_SERVICES", "SOFTWARE_SERVICES_ID_SEQ", con);
resetSequence("LABEL_FIELDS", "LABEL_FIELDS_ID_SEQ", con);
resetSequence("LABEL_FIELD_LANGUAGE", "LABEL_FIELD_LANGUAGE_ID_SEQ", con);
resetSequence("LABEL_FIELDS_SHORTCUTKEYS", "LABEL_FIELD_SHORTCUT_ID_SEQ", con);
} finally {
TagLibConnectionUtil.closeConnection(con);
}
}
public void setSrchResults(RELabelSetupSrchParams searchParams) throws Exception {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ArrayList<RELabelSetupSrchParams> searchResults = new ArrayList<RELabelSetupSrchParams>();
String labelFetchQry =
"SELECT ID, SCREEN_ID,DESCRIPTION,JSP_NAME,VO_CLASS,HANDLER_CLASS,"
+ "REMARKS,STATUS FROM SOFTWARE_SERVICES ";
try {
QueryFields fields = new QueryFields();
fields.put("SCREEN_ID", searchParams.getScrnId(), QueryConstants.LIKE_COND);
fields.put("DESCRIPTION", searchParams.getDesc(), QueryConstants.LIKE_COND);
fields.put("JSP_NAME", searchParams.getJspName(), QueryConstants.LIKE_COND);
fields.put("VO_CLASS", searchParams.getVoClass(), QueryConstants.LIKE_COND);
fields.put("HANDLER_CLASS", searchParams.getHandlerClass(), QueryConstants.DATE_TYPE);
fields.put("REMARKS", searchParams.getRemarks(), QueryConstants.LIKE_COND);
fields.put("STATUS", searchParams.getRemarks(), QueryConstants.LIKE_COND);
String whereCond = QueryBuilderUtil.buildWhereCond(fields);
String finalQry = labelFetchQry;
if (!TagLibStringUtility.isNullEmpty(whereCond)) {
finalQry = finalQry + " WHERE " + whereCond;
}
finalQry = finalQry + "ORDER BY SCREEN_ID DESC";
con = TagLibConnectionUtil.getConnection();
searchParams.totalRecords = QueryBuilderUtil.getTotalRowCount(finalQry, con);
String pagedQry =
QueryBuilderUtil.getPaginationQry(
finalQry, searchParams.startNumber, searchParams.endNumber);
pstmt = con.prepareStatement(pagedQry);
rs = pstmt.executeQuery();
while (rs.next()) {
RELabelSetupSrchParams labelSetup = new RELabelSetupSrchParams();
labelSetup.setId(rs.getLong("ID"));
labelSetup.setScrnId(rs.getString("SCREEN_ID"));
labelSetup.setDesc(rs.getString("DESCRIPTION"));
labelSetup.setJspName(rs.getString("JSP_NAME"));
labelSetup.setVoClass(rs.getString("VO_CLASS"));
labelSetup.setHandlerClass(rs.getString("HANDLER_CLASS"));
labelSetup.setRemarks(rs.getString("REMARKS"));
labelSetup.setStatus(rs.getString("STATUS"));
searchResults.add(labelSetup);
}
searchParams.setSearchResults(searchResults);
} catch (Exception e) {
e.printStackTrace();
} finally {
TagLibConnectionUtil.closePreparedStatement(pstmt, rs);
TagLibConnectionUtil.closeConnection(con);
}
}
public RELabelSetupMainDetails insertLabelMaster(RELabelSetupMainDetails mainDetails)
throws Exception {
Connection connection = null;
PreparedStatement pstmt = null;
String insertQuery = "";
long labelSeqId = 0;
try {
if (validateLabelMaster(mainDetails)) {
connection = TagLibConnectionUtil.getConnection();
connection.setAutoCommit(false);
labelSeqId = QueryBuilderUtil.generateSeqNo("SOFTWARE_SERVICES_ID_SEQ", connection);
insertQuery =
"INSERT INTO SOFTWARE_SERVICES(ID,SCREEN_ID,DESCRIPTION,JSP_NAME,VO_CLASS, "
+ "HANDLER_CLASS,REMARKS,STATUS) VALUES (?,?,?,?,?,?,?,?)";
pstmt = connection.prepareStatement(insertQuery);
pstmt.setLong(1, labelSeqId);
pstmt.setString(2, mainDetails.getScrnId());
pstmt.setString(3, mainDetails.getDesc());
pstmt.setString(4, mainDetails.getJspName());
pstmt.setString(5, mainDetails.getVoClass());
pstmt.setString(6, mainDetails.getHandlerClass());
pstmt.setString(7, mainDetails.getRemarks());
pstmt.setString(8, mainDetails.getStatus());
pstmt.executeUpdate();
insertLabelFields(mainDetails.getFieldsList(), connection, labelSeqId);
insertLabelFieldLang(
mainDetails.getFieldsList(),
mainDetails.getLangWiseFieldDescMap(),
connection,
labelSeqId);
connection.commit();
}
} catch (Exception e) {
connection.rollback();
throw e;
} finally {
TagLibConnectionUtil.closePreparedStatement(pstmt);
TagLibConnectionUtil.closeConnection(connection);
}
return mainDetails;
}
public RELabelSetupMainDetails deleteLabelMaster(Long setupSeqId) throws Exception {
String DELETE_LABEL_MASTER = "DELETE FROM SOFTWARE_SERVICES WHERE ID=? ";
Connection connection = null;
PreparedStatement pstmt = null;
RELabelSetupMainDetails mainDetails = null;
try {
mainDetails = loadLabelSetupMainDetails(setupSeqId + "");
connection = TagLibConnectionUtil.getConnection();
connection.setAutoCommit(false);
deleteLabelFieldsAndLang(connection, setupSeqId);
pstmt = connection.prepareStatement(DELETE_LABEL_MASTER);
pstmt.setLong(1, setupSeqId);
pstmt.executeUpdate();
connection.commit();
} catch (Exception e) {
connection.rollback();
e.printStackTrace();
} finally {
TagLibConnectionUtil.closePreparedStatement(pstmt);
TagLibConnectionUtil.closeConnection(connection);
}
return mainDetails;
}
public RELabelSetupMainDetails updateLabelMaster(RELabelSetupMainDetails labelTypeMaster)
throws Exception {
Connection connection = null;
PreparedStatement pstmt = null;
String updateQuery = "";
try {
connection = TagLibConnectionUtil.getConnection();
connection.setAutoCommit(false);
updateQuery =
"UPDATE SOFTWARE_SERVICES SET SCREEN_ID=?,DESCRIPTION=?,JSP_NAME=?, VO_CLASS=?,HANDLER_CLASS=?,REMARKS=?,STATUS=? WHERE ID =? ";
pstmt = connection.prepareStatement(updateQuery);
pstmt.setString(1, labelTypeMaster.getScrnId());
pstmt.setString(2, labelTypeMaster.getDesc());
pstmt.setString(3, labelTypeMaster.getJspName());
pstmt.setString(4, labelTypeMaster.getVoClass());
pstmt.setString(5, labelTypeMaster.getHandlerClass());
pstmt.setString(6, labelTypeMaster.getRemarks());
pstmt.setString(7, labelTypeMaster.getStatus());
pstmt.setLong(8, labelTypeMaster.getSeqId());
pstmt.executeUpdate();
insertLabelFields(labelTypeMaster.getFieldsList(), connection, labelTypeMaster.getSeqId());
insertLabelFieldLang(
labelTypeMaster.getFieldsList(),
labelTypeMaster.getLangWiseFieldDescMap(),
connection,
labelTypeMaster.getSeqId());
connection.commit();
} catch (Exception e) {
connection.rollback();
e.printStackTrace();
throw e;
} finally {
TagLibConnectionUtil.closePreparedStatement(pstmt);
TagLibConnectionUtil.closeConnection(connection);
}
return labelTypeMaster;
}
private void insertLabelFields(List<RELabelFields> fieldsList, Connection conn, long setupSeqId)
throws Exception {
PreparedStatement labelPstmt = null;
PreparedStatement keysPstmt = null;
long labelFieldSeqId = 0;
String labelQuery = null;
String keysQuery = null;
try {
deleteLabelFieldsAndLang(conn, setupSeqId);
if (!CollectionUtils.isNullEmpty(fieldsList)) {
labelQuery =
"INSERT INTO LABEL_FIELDS(ID,SERVICE_ID,FIELD_ID,VO_FIELD_NAME,VO_FIELD_TYPE,LABEL_TYPE,REMARKS,STATUS) VALUES(?,?,?,?,?,?,?,?)";
keysQuery =
"INSERT INTO LABEL_FIELDS_SHORTCUTKEYS(ID,FIELD_ID,SHORTCUT_KEY) VALUES(LABEL_FIELD_SHORTCUT_ID_SEQ.NEXTVAL,?,?)";
labelPstmt = conn.prepareStatement(labelQuery);
keysPstmt = conn.prepareStatement(keysQuery);
for (RELabelFields labelFields : fieldsList) {
labelPstmt.clearParameters();
keysPstmt.clearParameters();
if (!TagLibStringUtility.isNullEmpty(labelFields.getLabelId())) {
labelFieldSeqId = QueryBuilderUtil.generateSeqNo("LABEL_FIELDS_ID_SEQ", conn);
labelFields.setLabelSeqId(labelFieldSeqId);
labelPstmt.setLong(1, labelFieldSeqId);
labelPstmt.setLong(2, setupSeqId);
labelPstmt.setString(3, labelFields.getLabelId());
labelPstmt.setString(4, labelFields.getVoFieldName());
labelPstmt.setString(5, labelFields.getVoFieldType());
labelPstmt.setString(6, labelFields.getLabelType());
labelPstmt.setString(7, labelFields.getRemarks());
labelPstmt.setString(8, labelFields.getStatus());
labelPstmt.executeUpdate();
keysPstmt.setLong(1, labelFieldSeqId);
keysPstmt.setString(2, labelFields.getShortCutKey());
keysPstmt.executeUpdate();
}
}
}
} finally {
labelQuery = null;
TagLibConnectionUtil.closePreparedStatement(labelPstmt);
}
}
private void insertLabelFieldLang(
List<RELabelFields> fieldsList,
Map<String, Map<String, String>> langWiseFieldDescMap,
Connection conn,
long setupSeqId)
throws Exception {
PreparedStatement fieldPstmt = null;
String fieldLanguage = null;
Map<String, String> labelDescMap = null;
String lang = null;
try {
if (!CollectionUtils.isNullEmpty(fieldsList)) {
fieldLanguage =
"INSERT INTO LABEL_FIELD_LANGUAGE(ID, LABEL_ID, LANGUAGE_ID, LABEL_DESCRIPTION) VALUES(LABEL_FIELD_LANGUAGE_ID_SEQ.NEXTVAL,?,?,?)";
fieldPstmt = conn.prepareStatement(fieldLanguage);
Iterator<String> langKey = langWiseFieldDescMap.keySet().iterator();
while (langKey.hasNext()) {
lang = langKey.next();
labelDescMap = langWiseFieldDescMap.get(lang);
for (RELabelFields labelFields : fieldsList) {
fieldPstmt.clearParameters();
String labelId = labelFields.getLabelId();
String desc = labelDescMap.get(labelFields.getLabelId());
if (!TagLibStringUtility.isNullEmpty(labelId)
&& !TagLibStringUtility.isNullEmpty(desc)) {
fieldPstmt.setLong(1, labelFields.getLabelSeqId());
fieldPstmt.setString(2, lang);
fieldPstmt.setString(3, desc);
fieldPstmt.executeUpdate();
}
}
}
}
} finally {
fieldLanguage = null;
labelDescMap = null;
lang = null;
TagLibConnectionUtil.closePreparedStatement(fieldPstmt);
}
}
private void deleteLabelFieldsAndLang(Connection connection, long seqId) throws Exception {
String labelFieldDeleteQuery =
"DELETE FROM LABEL_FIELD_LANGUAGE T1 WHERE T1.LABEL_ID IN (SELECT ID FROM LABEL_FIELDS T2 WHERE T2.SERVICE_ID = ?)";
String labelShortKeysDeleteQuery =
"DELETE FROM LABEL_FIELDS_SHORTCUTKEYS T4 WHERE T4.FIELD_ID IN (SELECT ID FROM LABEL_FIELDS T2 WHERE T2.SERVICE_ID = ?)";
String labelFieldLangDelQuery = "DELETE FROM LABEL_FIELDS WHERE SERVICE_ID=?";
PreparedStatement pstmt = null;
try {
pstmt = connection.prepareStatement(labelFieldDeleteQuery);
pstmt.setLong(1, seqId);
pstmt.executeUpdate();
pstmt = connection.prepareStatement(labelShortKeysDeleteQuery);
pstmt.setLong(1, seqId);
pstmt.executeUpdate();
pstmt = connection.prepareStatement(labelFieldLangDelQuery);
pstmt.setLong(1, seqId);
pstmt.executeUpdate();
} finally {
TagLibConnectionUtil.closePreparedStatement(pstmt);
}
}
private Map<String, Map<String, String>> getLangWiseFieldDescMap(long setupSeqId, Connection con)
throws Exception {
PreparedStatement pstmt = null;
ResultSet rs = null;
String FIELD_LANG_QUERY = null;
Map<String, Map<String, String>> langWiseFieldDescMap = null;
Map<String, String> fieldDescMap = null;
try {
langWiseFieldDescMap = new LinkedHashMap<String, Map<String, String>>();
FIELD_LANG_QUERY =
"SELECT FL.ID, FL.LANGUAGE_ID, FL.LABEL_DESCRIPTION, T1.FIELD_ID "
+ "FROM LABEL_FIELD_LANGUAGE FL, LABEL_FIELDS T1 "
+ "WHERE T1.ID = FL.LABEL_ID "
+ " AND T1.SERVICE_ID = ?";
pstmt = con.prepareStatement(FIELD_LANG_QUERY);
pstmt.setLong(1, setupSeqId);
rs = pstmt.executeQuery();
while (rs.next()) {
String lang = rs.getString("LANGUAGE_ID");
String fieldId = rs.getString("FIELD_ID");
String desc = rs.getString("LABEL_DESCRIPTION");
fieldDescMap = langWiseFieldDescMap.get(lang);
if (fieldDescMap == null) {
fieldDescMap = new LinkedHashMap<String, String>();
fieldDescMap.put(fieldId, desc);
langWiseFieldDescMap.put(lang, fieldDescMap);
} else {
fieldDescMap.put(fieldId, desc);
}
}
} finally {
TagLibConnectionUtil.closePreparedStatement(pstmt, rs);
FIELD_LANG_QUERY = null;
fieldDescMap = null;
}
return langWiseFieldDescMap;
}
public RELabelSetupMainDetails loadLabelSetupMainDetails(String setupSeqId) throws Exception {
String FETCH_QRY =
"SELECT ID,SCREEN_ID,DESCRIPTION,JSP_NAME,VO_CLASS,HANDLER_CLASS, REMARKS, STATUS FROM SOFTWARE_SERVICES WHERE ID=?";
String LABEL_QUERY =
"SELECT LF.ID,LF.FIELD_ID, LF.LABEL_TYPE, LF.VO_FIELD_TYPE, LF.REMARKS, LF.STATUS, LF.VO_FIELD_NAME, "
+ "(SELECT T4.SHORTCUT_KEY FROM LABEL_FIELDS_SHORTCUTKEYS T4 WHERE T4.FIELD_ID = LF.ID AND ROWNUM = 1) SHORTCUT_KEY "
+ "FROM LABEL_FIELDS LF WHERE LF.SERVICE_ID=?";
Connection con = null;
PreparedStatement pstmt = null;
PreparedStatement labelpstmt = null;
ResultSet rs = null;
ResultSet labelRs = null;
RELabelSetupMainDetails mainDetails = null;
RELabelFields labelFields = null;
List<RELabelFields> labelList = null;
try {
con = TagLibConnectionUtil.getConnection();
labelFields = new RELabelFields();
mainDetails = new RELabelSetupMainDetails();
mainDetails.setLang("ENGLISH");
pstmt = con.prepareStatement(FETCH_QRY);
labelpstmt = con.prepareStatement(LABEL_QUERY);
pstmt.setString(1, setupSeqId);
rs = pstmt.executeQuery();
if (rs.next()) {
mainDetails.setSeqId(rs.getLong("ID"));
mainDetails.setScrnId(rs.getString("SCREEN_ID"));
mainDetails.setDesc(rs.getString("DESCRIPTION"));
mainDetails.setJspName(rs.getString("JSP_NAME"));
mainDetails.setVoClass(rs.getString("VO_CLASS"));
mainDetails.setHandlerClass(rs.getString("HANDLER_CLASS"));
mainDetails.setRemarks(rs.getString("REMARKS"));
mainDetails.setStatus(rs.getString("STATUS"));
labelpstmt.setString(1, setupSeqId);
labelRs = labelpstmt.executeQuery();
labelList = new ArrayList<RELabelFields>();
while (labelRs.next()) {
labelFields = new RELabelFields();
labelFields.setLabelSeqId(labelRs.getLong("ID"));
labelFields.setLabelId(labelRs.getString("FIELD_ID"));
labelFields.setLabelType(labelRs.getString("LABEL_TYPE"));
labelFields.setVoFieldName(labelRs.getString("VO_FIELD_NAME"));
labelFields.setVoFieldType(labelRs.getString("VO_FIELD_TYPE"));
labelFields.setShortCutKey(labelRs.getString("SHORTCUT_KEY"));
labelFields.setLabelType(labelRs.getString("LABEL_TYPE"));
;
labelFields.setStatus(labelRs.getString("STATUS"));
labelFields.setRemarks(labelRs.getString("REMARKS"));
labelList.add(labelFields);
}
if (CollectionUtils.isNullEmpty(labelList)) {
labelFields = new RELabelFields();
labelList.add(labelFields);
}
mainDetails.setFieldsList(labelList);
mainDetails.setLangWiseFieldDescMap(getLangWiseFieldDescMap(mainDetails.getSeqId(), con));
}
} finally {
TagLibConnectionUtil.closePreparedStatement(pstmt, rs);
TagLibConnectionUtil.closePreparedStatement(labelpstmt, labelRs);
TagLibConnectionUtil.closeConnection(con);
}
return mainDetails;
}