Java tutorial
/** * Copyright (c) 2005 - 2006 Pari Networks, Inc. All Rights Reserved. * * This software is the proprietary information of Pari Networks, Inc. * */ package com.pari.nm.utils.db; import java.sql.Blob; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Set; import org.apache.commons.lang.StringEscapeUtils; import spl.lang.AttrDef; import spl.lang.Def; import sun.misc.BASE64Encoder; import com.pari.logger.PariLogger; import com.pari.logger.PariLoggerFactory; import com.pari.nm.gui.guiservices.PariException; import com.pari.nm.modules.session.UserDetails; import com.pari.nm.modules.session.UserSession; import com.pari.nm.modules.session.UserSessionManager; import com.pari.nm.modules.session.UsersFactory; import com.pari.nm.utils.MgmtReportsPaginationUtil; import com.pari.nm.utils.db.postgres.PostgresReportDBHelper; import com.pari.product.ProductDatabaseType; import com.pari.product.ProductProfileFactory; import com.pari.reports.LegendType; import com.pari.reports.ReportItem; import com.pari.reports.ReportProfile; import com.pari.reports.ReportProfileSummary; import com.pari.reports.custom.util.ReportUtil; import com.pari.reports.request.core.ColumnLevelFilter; import com.pari.reports.request.core.DrillDownFilter; import com.pari.reports.request.core.ReportDefinitionFetcher; import com.pari.reports.udreports.UserDefinedReport; import com.pari.server.ServerProperties; import com.pari.services.JobServiceProxy; import com.pari.services.NodeIdUtils; import com.pari.services.Service; import com.pari.services.ServiceContainer; import com.pari.services.ServiceContainerImpl; import com.pari.services.ServiceImpl; import com.pari.services.def.AttributeDescriptor; import com.pari.services.def.ServiceDescriptor; import com.pari.services.def.ServiceRepository; import com.pari.utils.CompressionUtils; import com.pari.web.api.table.ColumnDefinition; import com.pari.web.api.table.RendererType; import com.pari.web.api.table.TableDefinition; public final class ReportDBHelper { private static final PariLogger logger = PariLoggerFactory.getLogger("DB"); private static final String CREATE_TEMP_PAGINATION_TBL = "CREATE TABLE PAGINATION_TABLE_INDEX (page_tbl_name varchar2(100), page_real_name varchar2(256), total_count NUMBER, server_session varchar2(100))"; private static final String INSERT_TEMP_PAGINATION_TBL = "INSERT INTO PAGINATION_TABLE_INDEX (page_tbl_name, page_real_name,server_session) VALUES (?, ?, ?)"; private static Set<String> deviceReportList = new HashSet<String>(); static { deviceReportList.add("UnmanagedDevices"); deviceReportList.add("device_details"); deviceReportList.add("DiscoveredDevices"); deviceReportList.add("extended_device_attributes_report"); deviceReportList.add("InventoryNodes"); deviceReportList.add("DeviceConfigurationSearch"); deviceReportList.add("device_with_additional_details"); deviceReportList.add("EnhancedServerJobLogs"); deviceReportList.add("view_device_group_members"); deviceReportList.add("syslog_raw_syslog_summary_report"); deviceReportList.add("syslog_per_message_type_summary_report"); deviceReportList.add("syslog_per_message_summary_report"); deviceReportList.add("syslog_per_device_summary_report"); deviceReportList.add("syslog_health_summary_report"); } public static void saveSystemReportProfiles(ReportProfile[] reportProfiles) throws Exception { Connection c = null; try { c = DBHelper.getConnection(); deleteAllProfiles(c); HashSet<Integer> profileIdSet = getAllExistingReportProfiles(c); c.setAutoCommit(false); for (ReportProfile reportProfile : reportProfiles) { ReportProfileSummary profileSummary = reportProfile.getSummary(); if (profileSummary == null) { logger.warn("Invalid Reporort Profile. No Summary found."); continue; } if (profileIdSet.contains(profileSummary.getProfileId())) { // Already saved in the database. skip continue; } saveReportProfileSummary(profileSummary, c); saveReportItems(reportProfile, c); } c.commit(); } catch (Exception ex) { logger.warn("Error while saving report profiles into the database.", ex); throw ex; } finally { try { if (c != null) { c.setAutoCommit(true); DBHelper.releaseConnection(c); } } catch (Exception ee) { logger.debug("Error while releasing the connection.", ee); } } } private static final void deleteAllProfiles(Connection c) { PreparedStatement ps = null; try { ps = c.prepareStatement(DBHelperConstants.DELETE_REPORT_PROFILE_SUMMARY); ps.executeUpdate(); } catch (Exception e) { logger.info("Unable to delete existing report profiles"); } finally { try { ps.close(); } catch (Exception ee) { logger.debug("Error while closing the prepared statement.", ee); } } } private static void saveReportProfileSummary(ReportProfileSummary profileSummary, Connection c) throws Exception { PreparedStatement ps = null; try { ps = c.prepareStatement(DBHelperConstants.INSERT_REPORT_PROFILE_SUMMARY); ps.setInt(1, profileSummary.getProfileId()); ps.setString(2, profileSummary.getTitle()); ps.setString(3, profileSummary.getDescription()); ps.setInt(4, profileSummary.getCreatorId()); ps.executeUpdate(); } catch (Exception e) { throw new Exception("Error while adding report Profile: " + profileSummary.getProfileId(), e); } finally { try { ps.close(); } catch (Exception ee) { logger.debug("Error while closing the prepared statement.", ee); } } } private static void saveReportItems(ReportProfile reportProfile, Connection c) throws Exception { PreparedStatement ps = null; try { ReportProfileSummary summary = reportProfile.getSummary(); ArrayList<ReportItem> reportItemList = reportProfile.getReportItemList(); ps = c.prepareStatement(DBHelperConstants.INSERT_REPORT_PROFILE_DETAILS); int profileId = summary.getProfileId(); for (ReportItem reportItem : reportItemList) { ps.setInt(1, profileId); ps.setString(2, reportItem.getReportIdentifier()); ps.setString(3, reportItem.getGraphIdentifier()); ps.setString(4, reportItem.getSelectedColumnsAsString()); ps.setString(5, ""); ps.setString(6, ""); ps.setString(7, reportItem.getReportDescription()); ps.setString(8, reportItem.getChartDescription()); LegendType type = reportItem.getLegendType(); if (type != null) { ps.setString(9, type.getTypeStr()); } else { ps.setString(9, null); } ps.setInt(10, summary.getCreatorId()); ps.executeUpdate(); } } catch (Exception e) { throw new Exception("Error while adding report Profile: " + reportProfile.getSummary().getProfileId(), e); } finally { try { ps.close(); } catch (Exception ee) { logger.debug("Error while closing the prepared statement.", ee); } } } private static HashSet<Integer> getAllExistingReportProfiles(Connection c) { ResultSet rs = null; Statement st = null; HashSet<Integer> profileIdSet = new HashSet<Integer>(); try { st = c.createStatement(); rs = st.executeQuery("select profile_id from report_profile_summary"); while ((rs != null) && rs.next()) { int profileId = rs.getInt("profile_id"); profileIdSet.add(profileId); } } catch (Exception ex) { logger.info("Error while querying for extsing report profiles.", ex); } finally { try { if (st != null) { st.close(); } } catch (Exception ex1) { logger.debug("Error while closing the prepared statement.", ex1); } try { if (rs != null) { rs.close(); } } catch (Exception ex2) { logger.debug("Error while closing the result set.", ex2); } } return profileIdSet; } public static final ReportProfileSummary[] getAllReportProfileSummaryObjects() throws Exception { ResultSet rs = null; try { rs = DBHelper.executeQuery("Select * from report_profile_summary"); ArrayList<ReportProfileSummary> reportList = new ArrayList<ReportProfileSummary>(); while (rs.next()) { int profileId = rs.getInt("profile_id"); String title = rs.getString("profile_title"); int creatorId = rs.getInt("creator_id"); UserDetails userDetails = UsersFactory.getUser(creatorId); String userName = "Unknown"; if (userDetails != null) { userName = userDetails.getName(); } ReportProfileSummary summary = new ReportProfileSummary(); summary.setProfileId(profileId); summary.setTitle(title); summary.setCreatorId(creatorId); summary.setCreatedUser(userName); reportList.add(summary); } return reportList.toArray(new ReportProfileSummary[reportList.size()]); } catch (Exception ex) { logger.warn("Unable to load Report Profiles from db.", ex); throw ex; } finally { try { if (rs != null) { rs.close(); } } catch (Exception ex2) { logger.debug("Error while closing the result set.", ex2); } } } public static final ReportProfile getReportProfileById(int reportProfileId) throws Exception { ResultSet rs = null; try { rs = DBHelper .executeQuery("Select * from report_profile_summary where profile_id = " + reportProfileId); ReportProfileSummary summary = new ReportProfileSummary(); if (rs.next()) { int profileId = rs.getInt("profile_id"); String title = rs.getString("profile_title"); String description = rs.getString("description"); int creatorId = rs.getInt("creator_id"); UserDetails userDetails = UsersFactory.getUser(creatorId); String userName = "Unknown"; if (userDetails != null) { userName = userDetails.getName(); } summary.setProfileId(profileId); summary.setDescription(description); summary.setTitle(title); summary.setCreatorId(creatorId); summary.setCreatedUser(userName); } ReportProfile reportProfile = new ReportProfile(summary); reportProfile.setReportItemList(getReportItemsByProfileId(reportProfileId)); return reportProfile; } catch (Exception ex) { logger.warn("Unable to load Report Profile from db for the profile id: " + reportProfileId, ex); throw ex; } finally { try { if (rs != null) { rs.close(); } } catch (Exception ex2) { logger.debug("Error while closing the result set.", ex2); } } } public static final ArrayList<ReportItem> getReportItemsByProfileId(int profileId) throws Exception { ResultSet rs = null; try { rs = DBHelper.executeQuery( "Select * from report_profile_details where profile_id=" + profileId + " order by row_id ASC"); ArrayList<ReportItem> reportList = new ArrayList<ReportItem>(); while (rs.next()) { String reportIdentifier = rs.getString("report_id"); String graphIdentifier = rs.getString("graph_id"); String columns = rs.getString("columns"); String reportDescr = rs.getString("report_descr"); String chartDescr = rs.getString("chart_descr"); String legendStr = rs.getString("legend_type"); ReportItem item = new ReportItem(); item.setReportIdentifier(reportIdentifier); item.setGraphIdentifier(graphIdentifier); if ((columns != null) && (columns.length() > 0)) { item.setSelectedColumns(columns.split(",")); } item.setReportDescription(reportDescr); item.setChartDescription(chartDescr); if (legendStr != null) { item.setLegendType(LegendType.getEnumTypeFromString(legendStr)); } reportList.add(item); } return reportList; } catch (Exception ex) { logger.warn("Unable to load Report Items from db for the report profile: " + profileId, ex); throw ex; } finally { try { if (rs != null) { rs.close(); } } catch (Exception ex2) { logger.debug("Error while closing the result set.", ex2); } } } public static final void saveApplicationPreferences(String app_name, String preference_name, String preference) throws Exception { Connection c = null; PreparedStatement ps = null; String deleteSql = "delete from app_preferences where application_name ='" + app_name + "' and preference_name ='" + preference_name + "'"; String insertSql = "insert into app_preferences (application_name, preference_name, preference_value) values (?, ?, ?)"; try { c = DBHelper.getConnection(); c.setAutoCommit(false); DBHelper.executeUpdateNoCommit(c, deleteSql); ps = c.prepareStatement(insertSql); ps.setString(1, app_name); ps.setString(2, preference_name); ps.setString(3, preference); ps.executeUpdate(); c.commit(); } catch (Exception ee) { try { c.rollback(); } catch (Exception sqlEx) { logger.debug("Error while rollingback the data", sqlEx); } logger.warn("Error while saving Application Preferences", ee); throw new PariException(-1, "Error while saving Application Preferences"); } finally { try { c.setAutoCommit(true); } catch (Exception e) { logger.debug("Error while Setting auto commit", e); } if (ps != null) { try { ps.close(); } catch (SQLException e) { logger.error("Exception while closing Statement:", e); } } DBConnectionFactory.getInstance().releaseConnection(c); } } public static final String getApplicationPreferences(String appName, String preferenceName) throws Exception { String query = "select preference_value from app_preferences where application_name ='" + appName + "' and preference_name ='" + preferenceName + "'"; ResultSet rs = null; try { String value = ""; rs = DBHelper.executeQuery(query); if (rs.next()) { value = rs.getString("preference_value"); } return value; } catch (Exception ee) { ee.printStackTrace(); String errorMessage = "Error while loading Application Preferences for the Application '" + appName + "'"; logger.warn(errorMessage, ee); throw new PariException(-1, errorMessage); } finally { try { rs.close(); } catch (Exception ee) { logger.debug("Error while closing result set", ee); } } } public static final UserDefinedReport[] getAllUserDefinedReports() throws Exception { String query = "select report_id, title, creator_name, created_time, report_definition, service_definition, decorator_definition, customer_id from user_defined_reports order by title"; ArrayList<UserDefinedReport> appList = new ArrayList<UserDefinedReport>(); ResultSet rs = null; try { rs = DBHelper.executeQuery(query); while (rs.next()) { UserDefinedReport report = new UserDefinedReport(); report.setIdentifier(rs.getString("report_id")); report.setTitle(rs.getString("title")); report.setCreatedUser(rs.getString("creator_name")); report.setCustomerId(rs.getInt("customer_id")); Timestamp ts = rs.getTimestamp("created_time"); if (ts != null) { report.setCreationTime(ts.getTime()); } String defintionXML = rs.getString("report_definition"); if ((defintionXML != null) && (defintionXML.length() > 0)) { report.importReportDefinitionFromXML(defintionXML); report.getReportDef().getProps().setId(report.getIdentifier()); appList.add(report); } String serviceXML = rs.getString("service_definition"); if ((serviceXML != null) && (serviceXML.length() > 0)) { report.setServiceDef(serviceXML); } String decoratorXML = rs.getString("decorator_definition"); if ((decoratorXML != null) && (decoratorXML.length() > 0)) { report.setDecoratorDef(decoratorXML); } } } catch (Exception ee) { logger.warn("Error while loading user defined reports.", ee); } finally { try { rs.close(); } catch (Exception ee) { logger.debug("Error while closing result set", ee); } } return appList.toArray(new UserDefinedReport[appList.size()]); } public static final void addUserDefinedReport(UserDefinedReport report) throws Exception { String query = "INSERT INTO user_defined_reports (report_id, title, creator_name, created_time, report_definition, service_definition, decorator_definition,customer_id) VALUES (?,?,?,?,?,?,?,?)"; Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(query); ps.setString(1, report.getIdentifier()); ps.setString(2, report.getTitle()); ps.setString(3, report.getCreatedUser()); ps.setTimestamp(4, (new Timestamp(System.currentTimeMillis()))); ps.setString(5, report.getReportDef().getXML("GetReportDefinition", "")); ps.setString(6, report.getServiceDef()); ps.setString(7, report.getDecoratorDef()); ps.setInt(8, report.getCustomerId()); ps.executeUpdate(); } catch (Exception ex) { String error = "Error while adding user defined report: " + report.getIdentifier(); logger.warn(error, ex); throw new PariException(-1, error); } finally { try { if (ps != null) { ps.close(); } } catch (Exception e) { logger.debug("Error while closing the prepared staement", e); } try { if (c != null) { DBHelper.releaseConnection(c); } } catch (Exception e) { logger.debug("Error while releasing the connection", e); } } } public static final void updateUserDefinedReport(UserDefinedReport udr) throws Exception { String query = "UPDATE user_defined_reports SET title=?, report_definition=?, service_definition=?, decorator_definition=? where report_id=?"; Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(query); ps.setString(1, udr.getTitle()); ps.setString(2, udr.getReportDef().getXML("GetReportDefinition", "")); ps.setString(3, udr.getServiceDef()); ps.setString(4, udr.getDecoratorDef()); ps.setString(5, udr.getIdentifier()); ps.executeUpdate(); } catch (Exception ex) { String error = "Error while updating user defined report: " + udr.getIdentifier(); logger.warn(error, ex); throw new PariException(-1, error); } finally { try { if (ps != null) { ps.close(); } } catch (Exception e) { logger.debug("Error while closing the prepared staement", e); } try { if (c != null) { DBHelper.releaseConnection(c); } } catch (Exception e) { logger.debug("Error while releasing the connection", e); } } } public static final void deleteUserDefinedReport(String reportIdentifier) throws Exception { String query = "delete from user_defined_reports where report_id=?"; Connection c = null; PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(query); ps.setString(1, reportIdentifier); ps.executeUpdate(); } catch (Exception ex) { String error = "Error while deleting user defined report: " + reportIdentifier; logger.warn(error, ex); throw new PariException(-1, error); } finally { try { if (ps != null) { ps.close(); } } catch (Exception e) { logger.debug("Error while closing the prepared staement", e); } try { if (c != null) { DBHelper.releaseConnection(c); } } catch (Exception e) { logger.debug("Error while releasing the connection", e); } } } public static final UserDefinedReport getUserDefinedReportByName(String reportIdentifier) throws Exception { String query = "select report_id, title, creator_name, created_time, report_definition, service_definition, decorator_definition from user_defined_reports where report_id = '" + reportIdentifier + "' "; ResultSet rs = null; try { rs = DBHelper.executeQuery(query); if (rs.next()) { UserDefinedReport report = new UserDefinedReport(); report.setIdentifier(rs.getString("report_id")); report.setTitle(rs.getString("title")); report.setCreatedUser(rs.getString("creator_name")); Timestamp ts = rs.getTimestamp("created_time"); if (ts != null) { report.setCreationTime(ts.getTime()); } String defintionXML = rs.getString("report_definition"); if ((defintionXML != null) && (defintionXML.length() > 0)) { report.importReportDefinitionFromXML(defintionXML); } String serviceXML = rs.getString("service_definition"); if ((serviceXML != null) && (serviceXML.length() > 0)) { report.setServiceDef(serviceXML); } String decoratorXML = rs.getString("decorator_definition"); if ((decoratorXML != null) && (decoratorXML.length() > 0)) { report.setDecoratorDef(decoratorXML); } return report; } return null; } catch (Exception ee) { String errorMessage = "Error while loading custom application: " + reportIdentifier; logger.warn(errorMessage, ee); throw new PariException(-1, errorMessage); } finally { try { rs.close(); } catch (Exception ee) { logger.debug("Error while closing result set", ee); } } } /** * Deletes Temp Pagination Table * * @param reportId * @param sessionId * @throws SQLException */ public static void deleteTempTable(String reportId, String sessionId) throws SQLException { String tblName = getTempPaginationTblName(reportId, sessionId); logger.info("Temp Table to be Deleted is:\t" + tblName); if (tblName == null) { logger.error( "Table name for reportID : " + reportId + " and sessionID : " + sessionId + " doesnt exsist."); return; } try { DBHelper.executeUpdate("DROP TABLE " + tblName); } catch (Exception e) { try { DBHelper.executeUpdate("DROP MATERIALIZED VIEW " + tblName); } catch (Exception ex) { logger.error("Exception occured while dropping materilized view" + tblName, ex); } } // Now we need to delete entry from PAGINATION_TABLE_INDEX also deleteEntryFromIndexTable(reportId, sessionId); // Removed previous hard-coded logic for deleting child temp tables. Now fetching all child table names and then // deleting them all. String serviceName = ReportDefinitionFetcher.getInstance().getServiceIdentifier(reportId); ServiceDescriptor descriptor = ServiceRepository.getInstance().getServiceDescriptor(serviceName); List<String> childColumns = getColumnInfo(descriptor, false, true); if (childColumns != null) { for (String childColumn : childColumns) { String childTblName = getTempPaginationTblName(reportId, sessionId, childColumn); logger.info("Child Temp Table to be Deleted is:\t" + childTblName); DBHelper.executeUpdate("DROP TABLE " + childTblName); // Now we need to delete entry from Index table also. String pageRealName = reportId + "_" + sessionId + "_" + childColumn; logger.info("Child Temp Table to be Deleted from Index Table is:\t" + pageRealName); deleteEntryFromIndexTable(pageRealName); } } } public static void deleteTempTableBasedOnServerSessionId(String sessionId) { ResultSet rs = null; List<String> tempTableNameList = new ArrayList<String>(); try { // Get the temporary table name from PAGINATION_TABLE_INDEX table based on server sessionId. rs = DBHelper.executeQuery( "SELECT page_tbl_name FROM PAGINATION_TABLE_INDEX WHERE server_session= '" + sessionId + "'"); while (rs != null && rs.next()) { tempTableNameList.add(rs.getString("page_tbl_name")); } } catch (Exception e) { logger.error( "Exception Occured while getting Data from Temporary pagination Table: PAGINATION_TABLE_INDEX.", e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing the ResultSet Object:", e); } } } // Drop all temporary table based on server sessionId for (String tempTblName : tempTableNameList) { try { DBHelper.executeUpdate("DROP TABLE " + tempTblName); logger.debug("Temprary table " + tempTblName + " deleted."); } catch (SQLException e) { logger.error("Failed to delete temporary table :" + tempTblName, e); try { DBHelper.executeUpdate("DROP MATERIALIZED VIEW " + tempTblName); } catch (SQLException ex) { logger.error("Failed to delete temporary materlized view :" + tempTblName, ex); } } } try { // Delete entry from PAGINATION_TABLE_INDEX based on server SessionId if (tempTableNameList.size() > 0) { String query = "DELETE FROM PAGINATION_TABLE_INDEX WHERE server_session='" + sessionId + "'"; DBHelper.executeUpdate(query); } } catch (SQLException e) { logger.error("Failed to delete entry from PAGINATION_TABLE_INDEX table.", e); } } // This method will be call during the server start up to clean the temp table // created during the report pagination. public static void deleteTempTables() { Connection c = DBHelper.getConnection(); try { // set the auto commit true for the db connection c.setAutoCommit(true); } catch (SQLException e1) { e1.printStackTrace(); } finally { DBHelper.releaseConnection(c); } ResultSet rs = null; List<String> tempTableNameList = new ArrayList<String>(); try { // Get all temporary table name from PAGINATION_TABLE_INDEX table. rs = DBHelper.executeQuery("SELECT page_tbl_name FROM PAGINATION_TABLE_INDEX"); while (rs != null && rs.next()) { tempTableNameList.add(rs.getString("page_tbl_name")); } } catch (Exception e) { logger.error( "Exception Occured while getting Data from Temporary pagination Table: PAGINATION_TABLE_INDEX.", e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing the ResultSet Object:", e); } } } // Drop all temporary table for (String tempTblName : tempTableNameList) { try { DBHelper.executeUpdate("DROP TABLE " + tempTblName); logger.debug("Temprary table " + tempTblName + " deleted."); } catch (SQLException e) { logger.error("Failed to delete temporary table :" + tempTblName, e); try { DBHelper.executeUpdate("DROP MATERIALIZED VIEW " + tempTblName); } catch (SQLException ex) { logger.error("Failed to delete temporary materlized view :" + tempTblName, ex); } } } try { // Delete entry from PAGINATION_TABLE_INDEX if (tempTableNameList.size() > 0) { String query = "DELETE PAGINATION_TABLE_INDEX"; DBHelper.executeUpdate(query); } } catch (SQLException e) { logger.error("Failed to delete entry from PAGINATION_TABLE_INDEX table.", e); } } private static void deleteEntryFromIndexTable(String reportRealName) throws SQLException { String query = "DELETE FROM PAGINATION_TABLE_INDEX WHERE page_real_name='" + reportRealName + "'"; logger.info("Query to Delete entry from PAGINATION_TABLE_INDEX Table\n" + query); DBHelper.executeUpdate(query); } private static void deleteEntryFromIndexTable(String reportId, String sessionId) throws SQLException { String realName = reportId + "_" + sessionId; String query = "DELETE FROM PAGINATION_TABLE_INDEX WHERE page_real_name='" + realName + "'"; logger.info("Query to Delete entry from PAGINATION_TABLE_INDEX Table\n" + query); DBHelper.executeUpdate(query); } public static void createTempPaginationTable(ServiceDescriptor descriptor, String reportId, String sessionId) throws Exception { createTempPaginationTable(descriptor, null, reportId, sessionId, null); /* * Connection c = null; Statement st = null; int result = -1; int childResult = -1; * * try { if (descriptor == null) { throw new Exception("Unable to get ServiceDescriptor for ReportId:\t" + * reportId + "\t SessionId:\t" + sessionId); } if (descriptor != null) { StringBuffer sb = new StringBuffer(); * StringBuilder childTblCreateQuery = new StringBuilder(); String childTblName = null; String tblName = * getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new * Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } * System.out.println("Temporary Report Table Name:\t" + tblName); sb.append("CREATE TABLE \t" + tblName + * " \t ("); AttributeDescriptor[] attrDescArr = descriptor.getAllAttributeDescriptors(); if (attrDescArr != * null) { for (AttributeDescriptor attrDesc : attrDescArr) { logger.info("attrDesc.getIdentifier()====\t" + * attrDesc.getIdentifier()); if (attrDesc.getIdentifier().equals("RowId")) { // ProfileRunSummary Report * contains RowId, which is conflicting with Oracle RowId, so appended // RowId with 123 * sb.append(attrDesc.getIdentifier() + "123" + "\tvarchar2(4000),"); } else { * sb.append(attrDesc.getIdentifier() + "\tvarchar2(4000),"); } } Def[] childDefArr = * descriptor.getAllChildRefs(); if (childDefArr != null && childDefArr.length > 0) { for (Def def : * childDefArr) { String defName = def.getName(); sb.append(defName + "\tvarchar2(4000),"); * sb.append("PAGINATION_PK" + "\t NUMBER,"); // For Hierarchical Reports we need to create a child table // * table name like reportId_sessionId_defName manipulationTempPaginationTable(reportId, sessionId, defName); * childTblName = getTempPaginationTblName(reportId, sessionId, defName); * logger.info("ChildTableName:\t"+childTblName); if (childTblName == null) { throw new * Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + * sessionId+"and ChildTableIdentifier:\t"+defName); } // childTblName = reportId+"_"+sessionId+"_"+defName; * logger.info("ChildTableName:\t"+childTblName); childTblCreateQuery.append("CREATE TABLE \t" + childTblName + * " \t ("); childTblCreateQuery.append("PAGINATION_PK" + "\t NUMBER,"); ServiceDescriptor childServiceDesc = * descriptor.getContainedServiceDescriptorByName(defName); if (childServiceDesc != null) { * AttributeDescriptor[] childAttrDescArr = childServiceDesc.getAllAttributeDescriptors(); if (childAttrDescArr * != null) { for (AttributeDescriptor childAttrDesc : childAttrDescArr) { * logger.info("childAttrDesc.getIdentifier()====\t" + childAttrDesc.getIdentifier()); * childTblCreateQuery.append(childAttrDesc.getIdentifier() + "\tvarchar2(4000),"); } * childTblCreateQuery.toString(); String childTblStr = childTblCreateQuery.substring(0, * childTblCreateQuery.length() - 1); childTblCreateQuery = new StringBuilder(); * childTblCreateQuery.append(childTblStr); childTblCreateQuery.append(")"); * logger.info("create child table in stringbuilder format is----" + childTblCreateQuery.toString()); } } * * } } * * String tempStr = sb.substring(0, sb.length() - 1); sb = new StringBuffer(); sb.append(tempStr); * sb.append(")"); logger.info("create table in stringbuffer format is----" + sb.toString()); * * c = DBHelper.getConnection(); if (c == null) { * logger.error("Unable to Obtain Connection to create Temporary Pagination Table."); return; } try { st = * c.createStatement(); } catch (SQLException e) { * logger.info("Exception Occured while Creating Statement to create Temporary Pagination Table.", e); return; } * try { result = st.executeUpdate(sb.toString()); } catch (SQLException e) { * logger.info("Exception Occured while executing the Query to Create Temporary Pagination Table.", e); return; * } if (result > 0) { logger.info("Table:" + tblName + "\t Created Successfully."); } * * try { childResult = st.executeUpdate(childTblCreateQuery.toString()); } catch (SQLException e) { * logger.info("Exception Occured while executing the Child Table Query to Create Temporary Pagination Table.", * e); return; } if (childResult > 0) { logger.info("Table:" + childTblName + "\t Created Successfully."); } * * } } } catch (Exception e) { logger.error("Exception Occured while Creating Temporary Pagination Table.", e); * return; } finally { if (st != null) { st.close(); } DBHelper.releaseConnection(c); } */} /* * public static void crateTempPaginationTableNew(ServiceDescriptor descriptor, String reportId, String sessionId) * throws Exception { Connection c = null; Statement st = null; int result = -1; * * try { if (descriptor == null) { throw new Exception("Unable to get ServiceDescriptor for ReportId:\t" + reportId * + "\t SessionId:\t" + sessionId); } if (descriptor != null) { StringBuffer sb = new StringBuffer(); String * tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new * Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } * System.out.println("Temporary Report Table Name:\t" + tblName); sb.append("CREATE TABLE \t" + tblName + " \t ("); * AttributeDescriptor[] attrDescArr = descriptor.getAllAttributeDescriptors(); Long pagination_PrimaryKey = * Long.valueOf(0); if (attrDescArr != null) { for (AttributeDescriptor attrDesc : attrDescArr) { * logger.info("attrDesc.getIdentifier()====\t" + attrDesc.getIdentifier()); if * (attrDesc.getIdentifier().equals("RowId")) { // ProfileRunSummary Report contains RowId, which is conflicting * with Oracle RowId, so appended // RowId with 123 sb.append(attrDesc.getIdentifier() + "123" + * "\tvarchar2(4000),"); } else { sb.append(attrDesc.getIdentifier() + "\tvarchar2(4000),"); } } * sb.append("PAGINATION_PK" + "\t NUMBER,"); Def[] childDefArr = descriptor.getAllChildRefs(); if (childDefArr != * null && childDefArr.length > 0) { for (Def def : childDefArr) { String defName = def.getName(); sb.append(defName * + "\tvarchar2(4000),"); // For Hierarchical Reports we need to create a child table // table name like * reportId_sessionId_defName manipulationTempPaginationTable(reportId, sessionId, defName); String childTblName = * getTempPaginationTblName(reportId, sessionId, defName); logger.info("ChildTableName:\t"+childTblName); if * (childTblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + * sessionId+"and ChildTableIdentifier:\t"+defName); } StringBuilder childTblCreateQuery = new StringBuilder(); * childTblCreateQuery.append("CREATE TABLE \t" + childTblName + " \t ("); * childTblCreateQuery.append("PAGINATION_PK" + "\t NUMBER,"); // childTblCreateQuery.append(b) * descriptor.getContainedServiceDescriptors(); } } * * String tempStr = sb.substring(0, sb.length() - 1); sb = new StringBuffer(); sb.append(tempStr); sb.append(")"); * logger.info("create table in stringbuffer format is----" + sb.toString()); * * c = DBHelper.getConnection(); if (c == null) { * logger.error("Unable to Obtain Connection to create Temporary Pagination Table."); return; } try { st = * c.createStatement(); } catch (SQLException e) { * logger.info("Exception Occured while Creating Statement to create Temporary Pagination Table.", e); return; } try * { result = st.executeUpdate(sb.toString()); } catch (SQLException e) { * logger.info("Exception Occured while executing the Query to Create Temporary Pagination Table.", e); return; } if * (result > 0) { logger.info("Table:" + tblName + "\t Created Successfully."); } } } } catch (Exception e) { * logger.error("Exception Occured while Creating Temporary Pagination Table.", e); return; } finally { if (st != * null) { st.close(); } DBHelper.releaseConnection(c); } } */ public static void insertIntoTempPaginationTable(ServiceContainerImpl sImpl, String reportId, String sessionId) { insertIntoTempPaginationTable(sImpl, reportId, sessionId, null); /* * Connection c = null; PreparedStatement ps = null; Long paginationPKVal = 0L; if (sImpl != null) { try { * StringBuffer sb = new StringBuffer(); String tblName = getTempPaginationTblName(reportId, sessionId); if * (tblName == null) { throw new * Exception("No Table Exist with ReportId:\t"+reportId+"and SessionId:\t"+sessionId); } * System.out.println("Inserting Data into following Table Name:\t" + tblName); c = DBHelper.getConnection(); if * (c == null) { logger.error("Unable to get Connection."); return; } Service[] allServs = * sImpl.getAllServices(); if (allServs == null || allServs.length <= 0) { * logger.info("Now Rows fetchd for ReportId:"+reportId+"and SessionId:\t"+sessionId); } else { * logger.info("Number of Records are:\t" + allServs.length); } * * // Adding to check sb = new StringBuffer(); sb.append("INSERT INTO " + tblName + "\t Values("); * ArrayList<String> colNameList = getColumnInfo(sImpl.getServiceDescriptor()); if (colNameList != null && * !colNameList.isEmpty()) { for (int i = 0; i < colNameList.size() - 1; i++) { sb.append("?,"); } // Adding for * Temp Pagination Table Pagination Primary Key creating Uniquely for each table sb.append("?,"); * sb.append("?)"); } * * // Now we need to insert into Child Table if exists any. ServiceDescriptor serviceDesc = * sImpl.getServiceDescriptor(); if (serviceDesc != null) { Def [] defArr = serviceDesc.getAllChildRefs(); if * (defArr != null) { for (Def def : defArr) { String childDefName = def.getName(); ServiceDescriptor * childServiceDesc = serviceDesc.getContainedServiceDescriptorByName(childDefName); if (childServiceDesc != * null) { AttributeDescriptor []childAttDescArr = childServiceDesc.getAllAttributeDescriptors(); if * (childAttDescArr != null) { for (AttributeDescriptor attDesc : childAttDescArr) { String childAttIdentifier = * attDesc.getIdentifier(); } } } } } } * * try { ps = c.prepareStatement(sb.toString()); } catch (SQLException e1) { e1.printStackTrace(); } Service[] * rowsArr = sImpl.getAllServices(); if (rowsArr == null) { logger.info("Report Contians No Data."); } else { // * Adding a Pagination Primary Key for Each table to uniquely identify each row // we will insert a Long value. * we will use this Primary Key for both Paranet and childTable, childTable if exists. Long tempPaginationKeyVal * = 0L; for (Service rowService : rowsArr) { int i=1; paginationPKVal = tempPaginationKeyVal +1; if * (colNameList != null && !colNameList.isEmpty()) { for (String colName : colNameList) { try { Object obj = * rowService.getAttribute(colName); if (obj instanceof ServiceContainerImpl) { insertChildTableInfo(obj, * reportId, sessionId, sImpl, paginationPKVal); } else if (obj == null) { ps.setString(i++, ""); } else { * ps.setString(i++, String.valueOf(obj)); } } catch (SQLException e) { * logger.error("Exception Occured while Inserting Data into Temporary Pagination Table:", e); return; } } * ps.setLong(i++, paginationPKVal); try { ps.addBatch(); } catch (SQLException e) { e.printStackTrace(); } } } * } * * try { int[] count = ps.executeBatch(); logger.info("Number of Statements Executed was:\t" + count.length); } * catch (SQLException e1) { logger.error("Exception Occured while Executing Batch Insert.", e1); } * * }catch(Exception e) { logger.error("Exception Occured while Inserting Data into Temporary Pagination Table:", * e); }finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { * logger.error("Exception Occured while Closing the Prepared Statement Object.", e); } } if (c != null) { try { * c.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing the Connection Object.", * e); } } } } */} /* * private static void insertChildTableInfo(Object obj, String reportId, String sessionId, ServiceContainerImpl * sImpl) throws Exception { insertChildTableInfo(obj, reportId, sessionId, sImpl, null); } */ /** * @param obj * @throws Exception */ private static void insertChildTableInfo(Connection connection, Object obj, String reportId, String sessionId, ServiceContainer sImpl, ArrayList<String> listofChildColumns, TableDefinition tableDef, String columnName) throws Exception { PreparedStatement ps = null; StringBuffer sb = null; // Original logic seems to be completely flawed where listofChildColumns for children was used below. Instead // now using columnName passed in method which corresponds to child for which this insert method is called. // Previous logic was somehow working for one child but bombs completely once you have more than one child! // String childInfoName = null; // ServiceDescriptor sDesc = sImpl.getServiceDescriptor(); try { // if (sDesc != null) // { // Def[] childDefArr = sDesc.getAllChildRefs(); // if (childDefArr != null && childDefArr.length > 0) // { // for (Def def : childDefArr) // { // childInfoName = def.getName(); // String childTblName = getTempPaginationTblName(reportId, sessionId, childInfoName); String childTblName = getTempPaginationTblName(reportId, sessionId, columnName); if (childTblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId + " and ChildTable:\t" + columnName); } System.out.println("Inserting Data into following Child Table Name:\t" + childTblName); sb = new StringBuffer(); sb.append("INSERT INTO " + childTblName + "\t Values("); // ServiceDescriptor childServiceDesc = // sDesc.getContainedServiceDescriptorByName(childInfoName); // ArrayList<String> colNameList = getChildTableColumnInfo(childServiceDesc); // Previous implementation made incorrect assumption of only one child report, this if addressed // now totake care of multiple children. if (tableDef != null) { TableDefinition childTable = tableDef.getChildTableById(columnName); ColumnDefinition[] columnDefs = childTable.getColumnDefs(); for (int ii = 1, jj = columnDefs.length; ii <= jj; ii++) { sb.append((ii == jj) ? "?" : "?,"); } } else { if (listofChildColumns != null && !listofChildColumns.isEmpty()) { // Adding for Temp Pagination Table Pagination Primary Key creating Uniquely for each // table // sb.append("?,"); // for (int i = 0; i < listofChildColumns.size() - 1; i++) // { // sb.append("?,"); // } // sb.append("?)"); for (int ii = 1, jj = listofChildColumns.size(); ii <= jj; ii++) { sb.append((ii == jj) ? "?" : "?,"); } } } sb.append(")"); try { logger.info("Inserting Child Table info to ChildTemp Table:\n" + sb.toString()); ps = connection.prepareStatement(sb.toString()); } catch (SQLException e1) { e1.printStackTrace(); } // int i = 1; ServiceContainerImpl serviceImpl = (ServiceContainerImpl) obj; Service[] childRowsArr = serviceImpl.getAllServices(); try { if (childRowsArr == null) { logger.info("No Child Records Exists...."); } else { for (Service childRowService : childRowsArr) { // ps.setLong(i++, paginationPKVal); int i = 1; if (tableDef != null) { TableDefinition childTable = tableDef.getChildTableById(columnName); ColumnDefinition[] columnDefs = childTable.getColumnDefs(); for (ColumnDefinition columnDef : columnDefs) { Object value = childRowService.getAttribute(columnDef.getId()); value = (null == value) ? "" : value; ps.setString(i++, String.valueOf(value)); } } else { if (listofChildColumns != null && !listofChildColumns.isEmpty()) { // int i = 1; for (String colName : listofChildColumns) { Object childObj = childRowService.getAttribute(colName); if (childObj == null) { ps.setString(i++, ""); } else { ps.setString(i++, String.valueOf(childObj)); } } } } ps.executeUpdate(); } } } catch (SQLException e) { logger.error("Exception Occured while Inserting Into Child Tables:", e); } finally { if (ps != null) { try { ps.close(); } catch (SQLException sqlEx) { logger.error("Exception while closing statement", sqlEx); } } } // } // } // } } catch (Exception e) { logger.error("Exception Occured while Inserting into Temp Pagination Table.", e); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing the Prepared Statement Object.", e); } } } } private static ArrayList<String> getColumnInfo(ServiceDescriptor sDesc) { if (sDesc != null) { ArrayList<String> colNameList = new ArrayList<String>(); sDesc.getAllAttributeDescriptors(); sDesc.getContainedServiceDescriptors(); AttributeDescriptor[] attrDescArr = sDesc.getAllAttributeDescriptors(); if (attrDescArr != null) { for (AttributeDescriptor attrDesc : attrDescArr) { String colName = attrDesc.getIdentifier(); colNameList.add(colName); } } Def[] childDefArr = sDesc.getAllChildRefs(); if (childDefArr != null && childDefArr.length > 0) { for (Def def : childDefArr) { colNameList.add(def.getName()); } } return colNameList; } return null; } private static ArrayList<String> getColumnInfo(ServiceDescriptor descriptor, boolean isParentColumnRequired, boolean isChildColumnRequired) { ArrayList<String> listOfColumnName = new ArrayList<String>(); if (isParentColumnRequired) { AttributeDescriptor[] attributeDescriptors = descriptor.getAllAttributeDescriptors(); for (AttributeDescriptor attributeDescriptor : attributeDescriptors) { listOfColumnName.add(attributeDescriptor.getIdentifier()); } } if (isChildColumnRequired) { if (descriptor != null) { Def[] allChildDef = descriptor.getAllChildRefs(); if (allChildDef != null) { for (Def def : allChildDef) { listOfColumnName.add(def.getName()); } } } } return listOfColumnName; } public static ServiceContainerImpl getDataFromTempTable(String reportId, String sessionId, ServiceDescriptor descriptor, TableDefinition tableDefinition, ArrayList<String> childColumnsList, Map<String, String> mapOfConditions, List<ColumnLevelFilter> columnLevelFilters) throws Exception { Connection connection = null; Statement statement = null; ResultSet rs = null; String tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } logger.info("Getting Data from the following Table Name:\t" + tblName); ServiceContainerImpl sImpl = new ServiceContainerImpl(descriptor, null); Def[] childDefArr = descriptor.getAllChildRefs(); ArrayList<String> childIdentifersList = null; String childIdentifierName = null; if (childDefArr != null && childDefArr.length > 0) { childIdentifersList = new ArrayList<String>(); for (Def def : childDefArr) { childIdentifierName = def.getName(); childIdentifersList.add(childIdentifierName); } } connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); return null; } StringBuffer buffer = new StringBuffer(); if (null != mapOfConditions) { int condition = mapOfConditions.size(); buffer.append((0 == mapOfConditions.size() ? "" : "WHERE ")); for (Map.Entry<String, String> map : mapOfConditions.entrySet()) { String columnName = map.getKey(); if (null != tableDefinition) { if (true == "hostname".equals(columnName.toLowerCase())) { buffer.append("DEVICE IN (SELECT ID FROM IOS_VERSION WHERE " + map.getValue() + ")"); } else if (true == "ipaddress".equals(columnName.toLowerCase())) { buffer.append("DEVICE IN (SELECT ID FROM NODES WHERE " + map.getValue() + ")"); } else { buffer.append(map.getValue()); } } else { buffer.append(map.getValue()); } condition -= 1; // decrease buffer.append((0 == condition) ? " " : " OR "); } } StringBuffer columnFilterBuffer = new StringBuffer(); columnFilterBuffer.append(" WHERE "); boolean isFirst = true; if (columnLevelFilters != null) { for (ColumnLevelFilter filter : columnLevelFilters) { if (!isFirst) { columnFilterBuffer.append(" AND "); } if (null != tableDefinition && isDeviceINQueryRequired(tableDefinition, reportId, filter.getColumnName().toLowerCase())) { if (true == "hostname".equals(filter.getColumnName().toLowerCase())) { columnFilterBuffer .append("DEVICE IN (SELECT ID FROM IOS_VERSION WHERE " + filter.getQuery() + ")"); } else if (true == "ipaddress".equals(filter.getColumnName().toLowerCase())) { columnFilterBuffer .append("DEVICE IN (SELECT ID FROM NODES WHERE " + filter.getQuery() + ")"); } else { columnFilterBuffer.append(filter.getQuery()); } } else { if ("CustomerName".equalsIgnoreCase(filter.getColumnName().toLowerCase())) { columnFilterBuffer.append("customerid IN (SELECT customer_id FROM customers WHERE " + filter.getQuery().replace("CustomerName", "Customer_Name") + ")"); } else { columnFilterBuffer.append(filter.getQuery()); } } isFirst = false; } } String query = "SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (SELECT * FROM " + tblName + " " + (columnLevelFilters != null && columnLevelFilters.size() > 0 ? columnFilterBuffer.toString() : "") + ") Q1 " + String.valueOf(buffer) + " ) "; try { statement = connection.createStatement(); rs = statement.executeQuery(query); ArrayList<String> columnList = null; if (descriptor.getIdentifier().equals("extended_device_attributes_report")) { columnList = getColumnInfo(tableDefinition); } else { columnList = getColumnInfo(descriptor); } while ((rs != null) && rs.next()) { ServiceImpl service = new ServiceImpl(descriptor); if (columnList != null) { for (String column : columnList) { // Need to add Code to update for Child Tables: // if (column.equalsIgnoreCase("JobRunSummary")) if (childIdentifersList != null && childIdentifersList.contains(column)) { HashMap<String, String> parentKeyValueMap = new HashMap<String, String>(); AttrDef[] attrDefArr = descriptor.getKeys(); if (attrDefArr != null) { for (AttrDef attDef : attrDefArr) { String key = attDef.getName(); String value = rs.getString(attDef.getName()); parentKeyValueMap.put(key, value); } } // we need to populate Child Table Information // 1. get ChildTableName // String childInfoName = "JobRunSummary"; String childInfoName = childIdentifierName; String childTblName = getTempPaginationTblName(reportId, sessionId, childInfoName); if (childTblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId + " and ChildTblName:\t" + childInfoName); } logger.info("Getting Data from the following Table Name:\t" + childTblName); // Create child columns list from child table definition object instead of using // listOfChildColumns which incorrectly includes all child columns names. So previous // implementation bombs when more than one child is present. if (tableDefinition != null) { // ServiceContainerImpl serviceContainer = ReportDBHelper.getChildTblInformation1( // childTableName, parentKeyValueMap, container, listOfChildColumns); TableDefinition childTable = tableDefinition.getChildTableById(childInfoName); ColumnDefinition[] columnDefs = childTable.getColumnDefs(); List<String> childColumns = new ArrayList<>(); for (ColumnDefinition columnDef : columnDefs) { childColumns.add(columnDef.getId()); } ServiceContainerImpl servImpl = getChildTblInformation1(childTblName, parentKeyValueMap, sImpl, childColumns); // ServiceImpl servImpl = getChildTblInformation(childTblName, parentKeyValueMap, sImpl, // childColumnsList); // service.setAttribute(column, servImpl); logger.info("========================================================" + servImpl.size()); if (servImpl != null && servImpl.size() > 0) { // service.setAttribute(column, servImpl.getServiceContainer()); service.setAttribute(column, servImpl); } } // service.setServiceContainer(servImpl); } else if (column.equals("RowId")) { // In ProfileRunSummary Report, column named RowId, it is conflicting with RowId in Oracle, // so appended 123 to RowId. service.setAttribute("RowId", rs.getString(column + "123")); } else if (column.equals("User")) { // In Unmanaged Device Report, column named User, it is conflicting with User in Oracle, so // appended 123 to User. service.setAttribute("User", rs.getString(column + "123")); } else if (column.equals("Comment")) { // In My Pending Approvals Report, column named Comment, it is conflicting with Comment in // Oracle, so appended 123 to Comment. service.setAttribute("Comment", rs.getString(column + "123")); } else if (column.equals("Size")) { // Manage script repository export contains size, which is conflicting with Oracle reserved // word, and this column changed to 'size123' service.setAttribute("Size", rs.getString(column + "123")); } else if (column.toLowerCase().equals("timestamp")) { service.setAttribute("Timestamp", rs.getString(column + "123")); service.setAttribute("timestamp", rs.getString(column + "123")); } else { String columnName = getRenderColumnName(column, tableDefinition); columnName = (null == columnName) ? column : columnName; service.setAttribute(column, rs.getString(columnName)); } } } sImpl.addService(service); service.setServiceContainer(sImpl); } } catch (Exception ex) { logger.error("Error while creating customer upload details report.", ex); try { throw ex; } catch (Exception e) { e.printStackTrace(); } } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } try { if (statement != null) { statement.close(); } } catch (SQLException sqlEx) { logger.error("Exception while closing statement", sqlEx); } DBHelper.releaseConnection(connection); } return sImpl; } /* * public static ServiceContainerImpl getDataFromTempTable1(String reportId, String sessionId, int start, int * pageLength, ServiceDescriptor descriptor, TableDefinition tableDef, ArrayList<String> childColumnsList, * Map<String, String> mapOfConditions) throws Exception { Connection connection = null; Statement statement = null; * ResultSet rs = null; * * String tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new * Exception("No Table Exist with ReportId:\t" + reportId + " and SessionId:\t" + sessionId); } * logger.info("Getting Data from the following Table Name:\t" + tblName); ServiceContainerImpl sImpl = new * ServiceContainerImpl(descriptor, null); Def[] childDefArr = descriptor.getAllChildRefs(); ArrayList<String> * childIdentifersList = null; String childIdentifierName = null; if (childDefArr != null && childDefArr.length > 0) * { childIdentifersList = new ArrayList<String>(); for (Def def : childDefArr) { childIdentifierName = * def.getName(); childIdentifersList.add(childIdentifierName); } } connection = DBHelper.getConnection(); if * (connection == null) { logger.info("Unable to get Connection."); return null; } // StringBuffer queryString = * getQuery(reportId, start, pageLength, tblName, mapOfConditions, tableDef); StringBuffer buffer = new * StringBuffer(); if (null != mapOfConditions) { * * int condition = mapOfConditions.size(); buffer.append((0 == mapOfConditions.size() ? "" : "WHERE ")); for * (Map.Entry<String, String> map : mapOfConditions.entrySet()) { String columnName = map.getKey(); if (null != * tableDef) { // Add the condition for referring the device table only if the report contains a column // for * deviceID field else by pass. If there exist any other report then new mechanism should // be introduced . * * if (!(tableDef.getId().equals("UnmanagedDevices"))) { if (true == "hostname".equals(columnName.toLowerCase())) { * buffer.append("DEVICE IN (SELECT ID FROM IOS_VERSION WHERE " + map.getValue() + ")"); } else if (true == * "ipaddress".equals(columnName.toLowerCase())) { buffer.append("DEVICE IN (SELECT ID FROM NODES WHERE " + * map.getValue() + ")"); } else { buffer.append(map.getValue()); } } else { buffer.append(map.getValue()); } * * } else { buffer.append(map.getValue()); } condition -= 1; // decrease buffer.append((0 == condition) ? " " : * " OR "); } } * * StringBuffer sb = new StringBuffer(); if (start <= 0) { start = 1; } if (start <= 0 && pageLength <= 0) { * sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (SELECT * FROM " + tblName + " ) Q1 " + * String.valueOf(buffer) + " ) "); } else if (start <= 0 && pageLength > 0) { * sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (SELECT * FROM " + tblName + " ) Q1 " + * String.valueOf(buffer) + " ) WHERE RN BETWEEN " + start + " AND " + pageLength); } else if (pageLength <= 0 && * start > 0) { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (SELECT * FROM " + tblName + " ) Q1 " + * String.valueOf(buffer) + " ) WHERE RN >= " + start); } else { * sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (SELECT * FROM " + tblName + " ) Q1 " + * String.valueOf(buffer) + " ) WHERE RN BETWEEN " + start + " AND " + pageLength); } * * try { statement = connection.createStatement(); rs = statement.executeQuery(sb.toString()); ArrayList<String> * columnList = getColumnInfo(descriptor); while ((rs != null) && rs.next()) { ServiceImpl service = new * ServiceImpl(descriptor); if (columnList != null) { for (String column : columnList) { // Need to add Code to * update for Child Tables: // if (column.equalsIgnoreCase("JobRunSummary")) if (childIdentifersList != null && * childIdentifersList.contains(column)) { HashMap<String, String> parentKeyValueMap = new HashMap<String, * String>(); AttrDef[] attrDefArr = descriptor.getKeys(); if (attrDefArr != null) { for (AttrDef attDef : * attrDefArr) { String key = attDef.getName(); String value = rs.getString(attDef.getName()); * parentKeyValueMap.put(key, value); } } // we need to populate Child Table Information // 1. get ChildTableName // * String childInfoName = "JobRunSummary"; String childInfoName = childIdentifierName; String childTblName = * getTempPaginationTblName(reportId, sessionId, childInfoName); if (childTblName == null) { throw new * Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId + " and ChildTblName:\t" * + childInfoName); } logger.info("Getting Data from the following Table Name:\t" + childTblName); * ServiceContainerImpl servImpl = getChildTblInformation1(childTblName, parentKeyValueMap, sImpl, * childColumnsList); // ServiceImpl servImpl = getChildTblInformation(childTblName, parentKeyValueMap, sImpl, // * childColumnsList); // service.setAttribute(column, servImpl); * logger.info("========================================================" + servImpl.size()); if (servImpl != null * && servImpl.size() > 0) { // service.setAttribute(column, servImpl.getServiceContainer()); * service.setAttribute(column, servImpl); } // service.setServiceContainer(servImpl); } else if * (column.equalsIgnoreCase("RowId")) { // In ProfileRunSummary Report, column named RowId, it is conflicting with * RowId in Oracle, // so appended 123 to RowId. service.setAttribute("RowId", rs.getString(column + "123")); } else * if (column.equalsIgnoreCase("User")) { // In Unmanaged Device Report, column named User, it is conflicting with * User in Oracle, so // appended 123 to User. service.setAttribute("User", rs.getString(column + "123")); } else if * (column.equalsIgnoreCase("Comment")) { // In My Pending Approvals Report, column named Comment, it is conflicting * with Comment in // Oracle, so appended 123 to Comment. service.setAttribute("Comment", rs.getString(column + * "123")); } else if (column.equalsIgnoreCase("Size")) { // Manage script repository export contains size, which is * conflicting with Oracle reserved // word, and this column changed to 'size123' service.setAttribute("Size", * rs.getString(column + "123")); } else if (column.equalsIgnoreCase("Timestamp")) { * service.setAttribute("Timestamp", rs.getString(column + "123")); } else { String columnName = * getRenderColumnName(column, tableDef); columnName = (null == columnName) ? column : columnName; * service.setAttribute(column, rs.getString(columnName)); } } } sImpl.addService(service); * service.setServiceContainer(sImpl); } } catch (Exception ex) { * logger.error("Error while creating customer upload details report.", ex); try { throw ex; } catch (Exception e) { * e.printStackTrace(); } } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } try { if * (statement != null) { statement.close(); } } catch (SQLException sqlEx) { * logger.error("Exception while closing statement", sqlEx); } * * DBHelper.releaseConnection(connection); } return sImpl; } */ public static ServiceContainerImpl getDataFromTempTable(String reportId, String sessionId, int start, int pageLength, ServiceDescriptor descriptor, TableDefinition tableDef, ArrayList<String> childColumnsList, Map<String, String> mapOfConditions, Map<String, String> sortingFilters, List<ColumnLevelFilter> columnLevelFilters, String uniqueColumn) throws Exception { Connection connection = null; Statement statement = null; ResultSet rs = null; String tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + " and SessionId:\t" + sessionId); } logger.info("Getting Data from the following Table Name:\t" + tblName); ServiceContainerImpl sImpl = new ServiceContainerImpl(descriptor, null); Def[] childDefArr = descriptor.getAllChildRefs(); ArrayList<String> childIdentifersList = null; String childIdentifierName = null; if (childDefArr != null && childDefArr.length > 0) { childIdentifersList = new ArrayList<String>(); for (Def def : childDefArr) { childIdentifierName = def.getName(); childIdentifersList.add(childIdentifierName); } } connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); return null; } // StringBuffer sb = getQuery(reportId, start, pageLength, tblName, mapOfConditions, tableDef, sortingFilters, // columnLevelFilters, uniqueColumn); // StringBuffer sb = getQuery(reportId, start, pageLength, tblName, mapOfConditions, tableDef, sortingFilters); String childTblName = getTempPaginationTblName(reportId, sessionId, childIdentifierName); // Create child columns list from child table definition object instead of using // childColumnsList which incorrectly includes all child columns names. So previous // implementation bombs when more than one child is present. List<String> childColList = new ArrayList<>(); if (tableDef != null) { TableDefinition childTable = tableDef.getChildTableById(childIdentifierName); if (childTable != null) { ColumnDefinition[] columnDefs = childTable.getColumnDefs(); for (ColumnDefinition columnDef : columnDefs) { childColList.add(columnDef.getId()); } } } StringBuffer sb = getQuery(reportId, start, pageLength, tblName, mapOfConditions, tableDef, sortingFilters, childTblName, childColList, columnLevelFilters, uniqueColumn); try { statement = connection.createStatement(); rs = statement.executeQuery(sb.toString()); ArrayList<String> columnList = null; if (reportId.equals("extended_device_attributes_report")) { columnList = getColumnInfo(tableDef); } else { columnList = getColumnInfo(descriptor); } while ((rs != null) && rs.next()) { ServiceImpl service = new ServiceImpl(descriptor); if (columnList != null) { for (String column : uniqueColumn != null ? new String[] { uniqueColumn } : columnList.toArray(new String[] {})) { // Need to add Code to update for Child Tables: // if (column.equalsIgnoreCase("JobRunSummary")) if (childIdentifersList != null && childIdentifersList.contains(column)) { HashMap<String, String> parentKeyValueMap = new HashMap<String, String>(); AttrDef[] attrDefArr = descriptor.getKeys(); if (attrDefArr != null) { for (AttrDef attDef : attrDefArr) { String key = attDef.getName(); String value = rs.getString(attDef.getName()); parentKeyValueMap.put(key, value); } } // we need to populate Child Table Information // 1. get ChildTableName // String childInfoName = "JobRunSummary"; String childInfoName = childIdentifierName; // String childTblName = getTempPaginationTblName(reportId, sessionId, childInfoName); childTblName = getTempPaginationTblName(reportId, sessionId, column); if (childTblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId + " and ChildTblName:\t" + childInfoName); } logger.info("Getting Data from the following Table Name:\t" + childTblName); // Create child columns list from child table definition object instead of using // childColumnsList which incorrectly includes all child columns names. So previous // implementation bombs when more than one child is present. if (tableDef != null) { TableDefinition childTable = tableDef.getChildTableById(column); ColumnDefinition[] columnDefs = childTable.getColumnDefs(); List<String> childColumns = new ArrayList<>(); for (ColumnDefinition columnDef : columnDefs) { childColumns.add(columnDef.getId()); } ServiceContainerImpl servImpl = getChildTblInformation1(childTblName, parentKeyValueMap, sImpl, childColumns); logger.info("========================================================" + servImpl.size()); if (servImpl != null && servImpl.size() > 0) { service.setAttribute(column, servImpl); } } } else if (column.equalsIgnoreCase("RowId")) { // In ProfileRunSummary Report, column named RowId, it is conflicting with RowId in Oracle, // so appended 123 to RowId. service.setAttribute("RowId", rs.getString(column + "123")); } else if (column.equalsIgnoreCase("User")) { // In Unmanaged Device Report, column named User, it is conflicting with User in Oracle, so // appended 123 to User. service.setAttribute("User", rs.getString(column + "123")); } else if (column.equalsIgnoreCase("Comment")) { // In My Pending Approvals Report, column named Comment, it is conflicting with Comment in // Oracle, so appended 123 to Comment. service.setAttribute("Comment", rs.getString(column + "123")); } else if (column.equalsIgnoreCase("Size")) { // Manage script repository export contains size, which is conflicting with Oracle reserved // word, and this column changed to 'size123' service.setAttribute("Size", rs.getString(column + "123")); } else if (column.equalsIgnoreCase("Timestamp")) { service.setAttribute(column, rs.getString(column + "123")); } else if (column.equalsIgnoreCase("ChildrenCount")) { service.setAttribute("ChildrenCount", rs.getInt("childCount")); } else if ((!(reportId.equals("swim_install_analysis_report"))) && column.equalsIgnoreCase("Details")) { String columnName = getRenderColumnName(column, tableDef); columnName = (null == columnName) ? column : columnName; String value = CompressionUtils.getUncompressedDecoded(rs.getString(columnName)); service.setAttribute(column, value); } else if (column.equalsIgnoreCase("ViolationDetails")) { Blob blob = rs.getBlob(column); byte b[] = null; if (blob != null) { b = blob.getBytes(1, (int) blob.length()); } if (b != null) { BASE64Encoder encoder = new BASE64Encoder(); service.setAttribute(column, encoder.encode(b)); } } else { String columnName = getRenderColumnName(column, tableDef); columnName = (null == columnName) ? column : columnName; service.setAttribute(column, rs.getString(columnName)); } } } sImpl.addService(service); service.setServiceContainer(sImpl); } } catch (Exception ex) { logger.error("Error while creating customer upload details report.", ex); try { throw ex; } catch (Exception e) { e.printStackTrace(); } } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } try { if (statement != null) { statement.close(); } } catch (SQLException sqlEx) { logger.error("Exception while closing statement", sqlEx); } DBHelper.releaseConnection(connection); } return sImpl; } public static StringBuffer getQuery(String reportId, int start, int pageLength, String tblName, Map<String, String> mapOfConditions, TableDefinition tableDef, Map<String, String> sortingFilters, List<ColumnLevelFilter> columnLevelFilters, String uniqueColumn) { StringBuffer buffer = new StringBuffer(); String query = getReportSpecificQuery(reportId, tblName, sortingFilters, tableDef); if (query == null) { // query = "SELECT * FROM " + tblName; StringBuffer queryBuffer = getSortingCondition(sortingFilters, reportId, tableDef, tblName, columnLevelFilters, uniqueColumn); query = queryBuffer.toString(); buffer = getQueryCondition(mapOfConditions, reportId, tableDef); } else { buffer = getQueryCondition(mapOfConditions, reportId, tableDef); } StringBuffer sb = new StringBuffer(); if (start <= 0) { start = 1; } String finalQuery = query; if (uniqueColumn != null) { finalQuery = "SELECT DISTINCT " + ColumnLevelFilter.getColumnName(uniqueColumn) + " from ( SELECT * from (" + query + ") X " + String.valueOf(buffer) + " ) T order by " + ColumnLevelFilter.getColumnName(uniqueColumn) + " ASC nulls first"; } else { finalQuery = "SELECT * from (" + query + ") X " + String.valueOf(buffer) + ""; } if (start <= 0 && pageLength <= 0) { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM ( " + finalQuery + " ) Q1 " + " ) "); } else if (start <= 0 && pageLength > 0) { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (" + finalQuery + " ) Q1 " + " ) WHERE RN BETWEEN " + start + " AND " + pageLength); } else if (pageLength <= 0 && start > 0) { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (" + finalQuery + " ) Q1 " + " ) WHERE RN >= " + start); } else { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (" + finalQuery + " ) Q1 " + " ) WHERE RN BETWEEN " + start + " AND " + pageLength); } return sb; } public static String getColumnLevelFilterQuery(List<ColumnLevelFilter> columnLevelFilters, TableDefinition tableDefinition, String reportId) { StringBuffer columnFilterBuffer = new StringBuffer(); columnFilterBuffer.append(" WHERE "); boolean isFirst = true; if (columnLevelFilters != null && columnLevelFilters.size() > 0) { for (ColumnLevelFilter filter : columnLevelFilters) { if (!isFirst) { columnFilterBuffer.append(" AND "); } if (null != tableDefinition && isDeviceINQueryRequired(tableDefinition, reportId, filter.getColumnName().toLowerCase())) { if (true == "hostname".equals(filter.getColumnName().toLowerCase())) { if (filter.getValues().contains(null)) { columnFilterBuffer.append( "(DEVICE is null or DEVICE = '' or DEVICE IN (SELECT ID FROM IOS_VERSION WHERE " + filter.getQuery() + "))"); } else { columnFilterBuffer.append( "DEVICE IN (SELECT ID FROM IOS_VERSION WHERE " + filter.getQuery() + ")"); } } else if (true == "ipaddress".equals(filter.getColumnName().toLowerCase())) { if (filter.getValues().contains(null)) { columnFilterBuffer.append( "(DEVICE is null or DEVICE = '' or DEVICE IN (SELECT ID FROM NODES WHERE " + filter.getQuery() + "))"); } else { columnFilterBuffer .append("DEVICE IN (SELECT ID FROM NODES WHERE " + filter.getQuery() + ")"); } } else { columnFilterBuffer.append(filter.getQuery()); } } else { if ("CustomerName".equalsIgnoreCase(filter.getColumnName().toLowerCase())) { columnFilterBuffer.append("customerid IN (SELECT customer_id FROM customers WHERE " + filter.getQuery().replace("CustomerName", "Customer_Name") + ")"); } else { columnFilterBuffer.append(filter.getQuery()); } } isFirst = false; } return StringEscapeUtils.unescapeXml(columnFilterBuffer.toString()); } return ""; } // Create query that joins parent and child table and includes search criteria for child table also public static StringBuffer getQuery(String reportId, int start, int pageLength, String tblName, Map<String, String> mapOfConditions, TableDefinition tableDef, Map<String, String> sortingFilters, String childTblName, List<String> childColumnsList, List<ColumnLevelFilter> columnLevelFilters, String uniqueColumn) { StringBuffer buffer = new StringBuffer(); String query = getReportSpecificQuery(reportId, tblName, sortingFilters, tableDef); boolean childExists = false; if (query == null) { // If child exists then form query using join, etc. if ((childTblName != null) && (childColumnsList != null) && (childColumnsList.size() != 0) && (!"eol_hw_status_report".equals(reportId))) { childExists = true; if ("device_support_levels".equalsIgnoreCase(reportId)) { query = "SELECT DISTINCT p.* FROM " + tblName + " p LEFT JOIN " + childTblName + " c ON p.ID = c.ID "; } else { query = "SELECT DISTINCT p.* FROM " + tblName + " p LEFT JOIN " + childTblName + " c ON p.jobid = c.jobid "; } Map<String, String> updatedMap = null; if (mapOfConditions != null) { updatedMap = createQualifiedMap(mapOfConditions, "p"); // Further update map with child filter conditions // TODO: Child filter condition also should be sent from web client. // ***Temporarily parse parent filter condition to find search string...this will work only when // all/case-insensitive/matchanywhere is selected in web client*** String searchStr = parseConditionMap(mapOfConditions); // updatedMap.put("paramname", "UPPER(c.paramname) LIKE UPPER('%" + searchStr + "%')"); // updatedMap.put("paramvalue", "UPPER(c.paramvalue) LIKE UPPER('%" + searchStr + "%')"); int i = 0; if (searchStr != null) { for (String childColName : childColumnsList) { if (i == 0) { // Skip first child column that is join column name, e.g. jobId i++; continue; } updatedMap.put(childColName, "UPPER(c." + childColName + ") LIKE UPPER('%" + searchStr + "%')"); } } } buffer = getQueryCondition(updatedMap, reportId, tableDef); StringBuffer queryBuffer = getSortingCondition(sortingFilters, reportId, tableDef, "(" + query + buffer + ")", columnLevelFilters, uniqueColumn); buffer = new StringBuffer(); query = queryBuffer.toString(); } else { // query = "SELECT * FROM " + tblName; StringBuffer queryBuffer = getSortingCondition(sortingFilters, reportId, tableDef, tblName, columnLevelFilters, uniqueColumn); query = queryBuffer.toString(); buffer = getQueryCondition(mapOfConditions, reportId, tableDef); } } else { buffer = getSpecificQueryCondition(mapOfConditions, reportId, tableDef); } StringBuffer sb = new StringBuffer(); if (start <= 0) { start = 1; } String finalQuery = query; if (uniqueColumn != null) { finalQuery = "SELECT DISTINCT " + ColumnLevelFilter.getColumnName(uniqueColumn) + " from ( SELECT * from (" + query + ") X " + String.valueOf(buffer) + " ) T order by " + ColumnLevelFilter.getColumnName(uniqueColumn) + " ASC nulls first"; } else { finalQuery = "SELECT * from (" + query + ") X " + String.valueOf(buffer) + ""; } if (start <= 0 && pageLength <= 0) { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM ( " + finalQuery + " ) Q1 " + " ) "); } else if (start <= 0 && pageLength > 0) { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (" + finalQuery + " ) Q1 " + " ) WHERE RN BETWEEN " + start + " AND " + pageLength); } else if (pageLength <= 0 && start > 0) { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (" + finalQuery + " ) Q1 " + " ) WHERE RN >= " + start); } else { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (" + finalQuery + " ) Q1 " + " ) WHERE " + "RN BETWEEN " + start + " AND " + pageLength); } return sb; } // Find search string from map of conditions. Delete this method once web client is fixed and it sends child filter // condition also. private static String parseConditionMap(Map<String, String> mapOfConditions) { for (Entry<String, String> entry : mapOfConditions.entrySet()) { String value = entry.getValue(); // if regex string return null, e.g. REGEXP_LIKE(jobId,'612|586'). We don't want to search in child tables // for regex values. if (value.contains("REGEXP_LIKE")) { return null; } // e.g. jobType=UPPER(jobType) LIKE UPPER('%r333%') int index = value.lastIndexOf('('); if (index != -1) { // return "r333" return value.substring(index + 3, value.length() - 3); } } return null; } // Qualify values in map with prefix string, e.g. convert jobId=UPPER(jobId) LIKE UPPER('%r333%') to // jobId=UPPER(p.jobId) LIKE UPPER('%r333%') private static Map<String, String> createQualifiedMap(Map<String, String> mapOfConditions, String qualifier) { // Create new map and return it (if we update this map and its used some where else, things might break..) Map<String, String> qualifiedMap = new HashMap<>(); if (mapOfConditions != null) { for (Entry<String, String> entry : mapOfConditions.entrySet()) { String value = entry.getValue(); if (value.startsWith("UPPER(")) { StringBuilder sb = new StringBuilder(value); sb.insert("UPPER(".length(), qualifier + "."); qualifiedMap.put(entry.getKey(), sb.toString()); } else if (value.startsWith("REGEXP_LIKE(")) { StringBuilder sb = new StringBuilder(value); sb.insert("REGEXP_LIKE(".length(), qualifier + "."); qualifiedMap.put(entry.getKey(), sb.toString()); } } } return qualifiedMap; } private static StringBuffer getSpecificQueryCondition(Map<String, String> mapOfConditions, String reportId, TableDefinition tableDef) { if (mapOfConditions != null && mapOfConditions.size() > 0) { StringBuffer andCondition = null; Map<String, String> conditionMap = new HashMap<String, String>(); for (String key : mapOfConditions.keySet()) { if (mapOfConditions.get(key) != null) { String value = mapOfConditions.get(key); value = value.replaceAll(key, "Q1." + key); conditionMap.put(key, value); } } if (reportId.equalsIgnoreCase("device_details") || reportId.equalsIgnoreCase("device_with_additional_details")) { Map<String, String> conditionMap1 = new HashMap<String, String>(); if (conditionMap.containsKey("ParentId")) { String value = conditionMap.get("ParentId"); conditionMap.remove("ParentId"); conditionMap1.put("ParentId", value); } if (conditionMap.containsKey("CustomerName")) { String value = conditionMap.get("CustomerName"); conditionMap.remove("CustomerName"); conditionMap1.put("CustomerName", value); } if (conditionMap1.size() > 1) { andCondition = getAndQueryCondition(conditionMap1, reportId, tableDef, " AND "); } else if (conditionMap1.size() == 1) { andCondition = getAndQueryCondition(conditionMap1, reportId, tableDef, null); } } if (andCondition != null) { StringBuffer sb = new StringBuffer(); StringBuffer query = getQueryCondition(conditionMap, reportId, tableDef); if (query != null && query.length() != 0) { return sb.append(query).append(" AND").append("( ").append(andCondition).append(" )"); } return sb.append("WHERE ").append(andCondition); } return getQueryCondition(conditionMap, reportId, tableDef); } return getQueryCondition(mapOfConditions, reportId, tableDef); } private static StringBuffer getAndQueryCondition(Map<String, String> mapOfConditions, String reportId, TableDefinition tableDef, String queryCondition) { StringBuffer buffer = new StringBuffer(); if (null != mapOfConditions) { int condition = mapOfConditions.size(); for (Map.Entry<String, String> map : mapOfConditions.entrySet()) { String columnName = map.getKey(); if (null != tableDef) { // Add the condition for referring the device table only if the report contains a column // for deviceID field else by pass. If there exist any other report then new mechanism should // be introduced . if (isDeviceINQueryRequired(tableDef, reportId, columnName.toLowerCase())) { if (true == "hostname".equals(columnName.toLowerCase())) { buffer.append("DEVICE IN (SELECT ID FROM(SELECT ID FROM IOS_VERSION WHERE " + map.getValue() + "))"); } else if (true == "ipaddress".equals(columnName.toLowerCase())) { buffer.append("DEVICE IN (SELECT ID FROM(SELECT ID FROM NODES WHERE " + map.getValue() + "))"); } else { buffer.append(map.getValue()); } } else { buffer.append(map.getValue()); } } else { buffer.append(map.getValue()); } condition -= 1; // decrease if (queryCondition != null) { buffer.append((0 == condition) ? " " : queryCondition); } else { buffer.append((0 == condition) ? " " : " OR "); } } } return buffer; } private static boolean isDeviceINQueryRequired(TableDefinition tableDef, String reportId) { if (deviceReportList.contains(reportId) || deviceReportList.contains(tableDef.getId())) { return false; } return true; } private static StringBuffer getQueryCondition(Map<String, String> mapOfConditions, String reportId, TableDefinition tableDef) { StringBuffer buffer = new StringBuffer(); if (null != mapOfConditions) { int condition = mapOfConditions.size(); buffer.append((0 == mapOfConditions.size() ? "" : "WHERE ")); for (Map.Entry<String, String> map : mapOfConditions.entrySet()) { String columnName = map.getKey(); if (null != tableDef) { // Add the condition for referring the device table only if the report contains a column // for deviceID field else by pass. If there exist any other report then new mechanism should // be introduced . if (isDeviceINQueryRequired(tableDef, reportId, columnName.toLowerCase())) { if (true == "hostname".equals(columnName.toLowerCase())) { buffer.append("DEVICE IN (SELECT ID FROM IOS_VERSION WHERE " + map.getValue() + ")"); } else if (true == "ipaddress".equals(columnName.toLowerCase())) { buffer.append("DEVICE IN (SELECT ID FROM NODES WHERE " + map.getValue() + ")"); } else { buffer.append(map.getValue()); } } else if (isCustomerIdINQueryRequired(tableDef, reportId)) { if (true == "customerid".equals(columnName.toLowerCase())) { String value = map.getValue(); value = value.replace(columnName, "customer_name"); buffer.append("CUSTOMERID IN (SELECT customer_id FROM customers WHERE " + value + ")"); } else { buffer.append(map.getValue()); } } else { buffer.append(map.getValue()); } } else { buffer.append(map.getValue()); } condition -= 1; // decrease buffer.append((0 == condition) ? " " : " OR "); } } if (reportId.equals("export_profile_report")) { String value = String.valueOf(buffer); if (value.contains("UPPER(FileSize)")) { value = value.replace("UPPER(FileSize)", "ROUND(FILESIZE/1024,2)"); buffer = new StringBuffer(); buffer.append(value); } } return buffer; } private static boolean isCustomerIdINQueryRequired(TableDefinition tableDef, String reportId) { if (tableDef.getId().equals("DiscoveredDevices") || tableDef.getId().equals("CustomerUploadLogDetails") || tableDef.getId().equals("SmartnetDetails")) { return true; } return false; } private static StringBuffer getSortingCondition(Map<String, String> sortingFilters, String reportId, TableDefinition tableDef, String tmpTblName, List<ColumnLevelFilter> columnLevelFilters, String uniqueColumn) { StringBuffer buffer = new StringBuffer(); if (uniqueColumn != null) { sortingFilters = new HashMap<String, String>(); sortingFilters.put(uniqueColumn, ""); } if (sortingFilters != null && sortingFilters.size() > 0) { String value = null; boolean isCustomerQuery = isCustomerIdINQueryRequired(tableDef, reportId); for (String key : sortingFilters.keySet()) { String jobLog = getJobLogDetailsValue(key, reportId, sortingFilters); value = getColumnLevelFilterQuery(columnLevelFilters, tableDef, reportId) + " " + (uniqueColumn == null ? (jobLog != null ? jobLog : sortingFilters.get(key)) : ""); boolean isDeviceQuery = isDeviceINQueryRequired(tableDef, reportId, key); if (isDeviceQuery || isCustomerQuery) { if (isDeviceQuery && true == "hostname".equals(key.toLowerCase())) { buffer.append("SELECT * FROM (SELECT * FROM " + tmpTblName + " temp" + " LEFT JOIN IOS_VERSION ON temp" + ".DEVICE=TO_CHAR(IOS_VERSION.ID)) " + value); } else if (isDeviceQuery && true == "ipaddress".equals(key.toLowerCase())) { buffer.append("SELECT * FROM (SELECT * FROM " + tmpTblName + " temp LEFT JOIN NODES ON temp" + ".DEVICE=TO_CHAR(NODES.ID)) " + value); } else if (isCustomerQuery) { buffer.append("SELECT * FROM (SELECT " + tmpTblName + ".*,CUSTOMERS.CUSTOMER_NAME AS CUSTOMERNAME FROM " + tmpTblName + " LEFT JOIN CUSTOMERS ON " + tmpTblName + ".CUSTOMERID=CUSTOMERS.CUSTOMER_ID) " + value); } else { buffer.append("SELECT * FROM " + tmpTblName + " " + value); } } else { buffer.append("SELECT * FROM " + tmpTblName + " " + value); } } } else { buffer.append("SELECT * FROM " + tmpTblName + " " + getColumnLevelFilterQuery(columnLevelFilters, tableDef, reportId)); } return buffer; } private static String getJobLogDetailsValue(String key, String reportId, Map<String, String> sortingFilters) { if (reportId.endsWith("audit_logs") && key.equals("JobLogDetails")) { String sortData = sortingFilters.get(key); sortData = sortData.replace("JobLogDetails", "JobRunId"); return sortData; } return null; } private static boolean isDeviceINQueryRequired(TableDefinition tableDef, String reportId, String columnName) { if (tableDef.getId().equals("DeletedDevices") || tableDef.getId().equals("UnmanagedDevices") || reportId.equalsIgnoreCase("device_details") || tableDef.getId().equals("DiscoveredDevices") || reportId.equalsIgnoreCase("extended_device_attributes_report") || tableDef.getId().equals("InventoryNodes") || tableDef.getId().equals("InvChange") || tableDef.getId().equals("DeviceConfigurationSearch") || reportId.equalsIgnoreCase("device_with_additional_details") || reportId.equalsIgnoreCase("device_contract_summary") || reportId.equalsIgnoreCase("view_device_group_members") || reportId.equalsIgnoreCase("syslog_health_summary_report") || reportId.equalsIgnoreCase("syslog_raw_syslog_summary_report") || reportId.equalsIgnoreCase("syslog_per_message_type_summary_report") || reportId.equalsIgnoreCase("syslog_per_message_summary_report") || reportId.equalsIgnoreCase("syslog_per_device_summary_report")) { return false; } else if (tableDef.getId().equals("CustomerUploadLogDetails") && columnName.equalsIgnoreCase("ipaddress")) { return false; } else if (tableDef.getId().equals("EnhancedServerJobLogs") && columnName.equalsIgnoreCase("hostname")) { return false; } return true; } private static String getReportSpecificQuery(String reportId, String tblName, Map<String, String> sortingFilters, TableDefinition tableDef) { String query = null; if (reportId.equals("device_details") || reportId.equals("device_with_additional_details")) { query = " Select temp.*,temp1.ChildCount from " + tblName + " temp INNER JOIN (SELECT parent.Device ,count(child.Device) as ChildCount from " + tblName + " parent LEFT OUTER JOIN " + tblName + " child on parent.device = child.ParentId group by parent.Device) temp1 on temp.device =temp1.device"; } if (query != null) { if (sortingFilters != null && sortingFilters.size() > 0) { String value = ""; for (String key : sortingFilters.keySet()) { value = sortingFilters.get(key); } query = query + " " + value; } } return query; } public static ServiceContainerImpl getChildTblInformation1(String childTblName, HashMap<String, String> parentKeyValueMap, ServiceContainerImpl sImpl, List<String> childColumnInfoList) { Connection connection = null; Statement statement = null; ResultSet rs = null; String childIdentifierName = null; ServiceDescriptor sd = sImpl.getServiceDescriptor(); Def[] childDefArr = sd.getAllChildRefs(); if (childDefArr != null && childDefArr.length > 0) { for (Def def : childDefArr) { childIdentifierName = def.getName(); } } // ServiceDescriptor childSd = sd.getContainedServiceDescriptorByName("JobRunSummary"); ServiceDescriptor childSd = sd.getContainedServiceDescriptorByName(childIdentifierName); // ServiceImpl service = new ServiceImpl(sImpl.getServiceDescriptor()); // ServiceImpl service = new ServiceImpl(childSd); ServiceImpl service = null; connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); return null; } StringBuffer sb = new StringBuffer(); sb.append("SELECT * FROM " + childTblName + " WHERE "); if (parentKeyValueMap != null) { for (String key : parentKeyValueMap.keySet()) { String value = parentKeyValueMap.get(key); sb.append(key + "= '" + value + "'"); if (parentKeyValueMap.size() > 1) { sb.append("and"); } } } ServiceContainerImpl childSc = new ServiceContainerImpl(childSd, service); try { statement = connection.createStatement(); rs = statement.executeQuery(sb.toString()); while ((rs != null) && rs.next()) { // ServiceImpl service = new ServiceImpl(childSd); service = new ServiceImpl(childSd); if (childColumnInfoList != null) { // ServiceContainerImpl childSc = new ServiceContainerImpl(childSd, service); // childSc = new ServiceContainerImpl(childSd, service); childSc.addService(service); service.setServiceContainer(childSc); for (String column : childColumnInfoList) { if (column.equals("RowId")) { // In ProfileRunSummary Report, column named RowId, it is conflicting with RowId in Oracle, // so appended 123 to RowId. service.setAttribute("RowId", rs.getString(column + "123")); } else if (column.equals("User")) { // In Unmanaged Device Report, column named User, it is conflicting with User in Oracle, so // appended 123 to User. service.setAttribute("User", rs.getString(column + "123")); } else if (column.equals("Comment")) { // In My Pending Approvals Report, column named Comment, it is conflicting with Comment in // Oracle, so appended 123 to Comment. service.setAttribute("Comment", rs.getString(column + "123")); } else { service.setAttribute(column, rs.getString(column)); } } } } } catch (Exception ex) { logger.error("Error while creating customer upload details report.", ex); try { throw ex; } catch (Exception e) { e.printStackTrace(); } } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } try { if (statement != null) { statement.close(); } } catch (SQLException sqlEx) { logger.error("Exception while closing statement", sqlEx); } DBHelper.releaseConnection(connection); } return childSc; } // Check Temp Table Exists or not. // SELECT * FROM TEMP_PAGINATION_TBL public static ResultSet checkTempPaginationTblExists() { Connection connection = DBHelper.getConnection(); Statement statement = null; ResultSet rs = null; try { statement = connection.createStatement(); if (statement == null) { throw new Exception("Unable to get statement from DBConnection."); } rs = statement.executeQuery("SELECT * FROM PAGINATION_TABLE_INDEX"); return rs; } catch (SQLException e) { logger.info("PAGINATION_TABLE_INDEX Table is Not Present, So Create PAGINATION_TABLE_INDEX table----", e); try { executeSQLStmt(connection, CREATE_TEMP_PAGINATION_TBL); } catch (Exception e1) { logger.info("Exception occured while creating Temporary Pagination table", e1); try { executeSQLStmt(connection, CREATE_TEMP_PAGINATION_TBL); } catch (Exception e2) { logger.error("Exception Occured while Creating Temporary Pagination table in Catch Block:", e2); } } } catch (Exception e) { logger.info("Exception occured while Creating Temporary Pagination table::", e); } finally { if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing Temporary Pagination table Statement:", e); } } try { if (rs != null) { rs.close(); } } catch (Exception ex) { logger.error("Exception Occured while Closing ResultSet Temporary Pagination table:", ex); } try { DBHelper.releaseConnection(connection); } catch (Exception e) { logger.error("Exception Occured while Closing connection ", e); } } return null; } public static void executeSQLStmt(Connection connection, String query) throws Exception { Statement statement = connection.createStatement(); if (statement == null) { throw new Exception("Unable to get statement"); } if (query != null && query.trim().length() > 0 && !query.isEmpty()) { statement.executeUpdate(query); } try { statement.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing Temporary Pagination table Statement:", e); } finally { try { statement.close(); } catch (SQLException ex) { logger.error("Exception Occured while Closing Temporary Pagination table Statement:", ex); } } } public static void manipulationTempPaginationTable(String reportId, String clientSessionId, String serverSessionId) throws SQLException { manipulationTempPaginationTable(reportId, clientSessionId, null, serverSessionId); } public static void manipulationTempPaginationTable(String reportId, String sessionId, String childIdentifierName, String serverSessionId) throws SQLException { Connection con = DBHelper.getConnection(); if (con == null) { logger.error("Unable to Obtain Connection."); } PreparedStatement ps = null; try { String tempTblValue = null; ps = con.prepareStatement(INSERT_TEMP_PAGINATION_TBL); String tempTblName = "TEMP_" + System.nanoTime(); if (childIdentifierName != null && childIdentifierName.trim().length() > 0) { tempTblValue = reportId + "_" + sessionId + "_" + childIdentifierName; } else { tempTblValue = reportId + "_" + sessionId; } UserSession serverSession = UserSessionManager.getInstance().getCurrentUserSession(); ps.setString(1, tempTblName); ps.setString(2, tempTblValue); if (serverSession != null) { ps.setString(3, serverSession.getSessionId()); } else { ps.setString(3, serverSessionId); } ps.executeUpdate(); } catch (SQLException sqle) { logger.info("Exception occured while Manipulating version table :::", sqle); throw sqle; } finally { if (ps != null) { ps.close(); } DBHelper.releaseConnection(con); } } public static String getTempPaginationTblName(String reportId, String sessionId) { return getTempPaginationTblName(reportId, sessionId, null); } public static String getTempPaginationTblName(String reportId, String sessionId, String childIdentifierName) { ResultSet rs = null; String tempTblName = null; String tempTblValue = null; try { if (childIdentifierName != null && childIdentifierName.trim().length() > 0) { tempTblValue = reportId + "_" + sessionId + "_" + childIdentifierName; } else { tempTblValue = reportId + "_" + sessionId; } rs = DBHelper.executeQuery("SELECT page_tbl_name FROM PAGINATION_TABLE_INDEX WHERE page_real_name= '" + tempTblValue + "'"); if (rs != null && rs.next()) { tempTblName = rs.getString("page_tbl_name"); logger.info("Temp Table Name inside getTempPaginationTableName()---" + tempTblName); } return tempTblName; } catch (Exception e) { logger.error( "Exception Occured while getting Data from Temporary pagination Table: PAGINATION_TABLE_INDEX.", e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing the ResultSet Object:", e); } } } return null; } public static List<String> getTempPaginationTblName(String sessionId) { ResultSet rs = null; String tempTblName = null; String tempTblValue = null; List<String> tempTbleNameList = new ArrayList<String>(); try { tempTblValue = "%_" + sessionId + "%"; rs = DBHelper .executeQuery("SELECT page_tbl_name FROM PAGINATION_TABLE_INDEX WHERE page_real_name like '" + tempTblValue + "'"); while (rs != null && rs.next()) { tempTblName = rs.getString("page_tbl_name"); tempTbleNameList.add(tempTblName); logger.info("Temp Table Name inside getTempPaginationTableName()---" + tempTblName); } } catch (Exception e) { logger.error( "Exception Occured while getting Data from Temporary pagination Table: PAGINATION_TABLE_INDEX.", e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing the ResultSet Object:", e); } } } return tempTbleNameList; } public static boolean checkTempTableExists(String reportId, String sessionId) throws Exception { String tblName = getTempPaginationTblName(reportId, sessionId); Connection c = null; ResultSet rs = null; ResultSet tables = null; if (tblName == null) { throw new Exception("Unable to get Temp Pagination Table Name:"); } // String query = "SELECT * FROM " + tblName; boolean exists = false; try { // Use table metadata to check if tables exists in db schema. // Relying on exception, which is earlier implementation, is not a good idea. // Also it used to print exception stack trace in logs every time DBHelper throws exception, i.e. if table // doesn't exist c = DBHelper.getConnection(); if (isPostgres()) { String query = "select count(*) from " + tblName; rs = DBHelper.executeQuery(query); if (rs.next()) { exists = true; } } else { DatabaseMetaData dbm = c.getMetaData(); String[] tableType = { "TABLE", "VIEW" }; tables = dbm.getTables(null, null, tblName, tableType); if (tables.next()) { exists = true; } } // rs = DBHelper.executeQuery(query); // exists = true; } catch (Exception e) { logger.error("Temp Table :" + tblName + " does not exist"); // exists = false; } finally { try { if (tables != null) { tables.close(); } if (rs != null) { rs.close(); } } catch (Exception ex2) { logger.debug("Error while closing the result set.", ex2); } DBHelper.releaseConnection(c); } return exists; } public static void createTempPaginationTable(ServiceDescriptor descriptor, TableDefinition tableDef, String reportId, String sessionId, ArrayList<String> childColNamesList) throws SQLException { Connection c = null; Statement st = null; int result = -1; int childResult = -1; try { if (descriptor == null) { throw new Exception("Unable to get ServiceDescriptor for ReportId:\t" + reportId + "\t SessionId:\t" + sessionId); } if (descriptor.getIdentifier().equals("extended_device_attributes_report")) { createTempTableForPagination(tableDef, reportId, sessionId, childColNamesList); } else { StringBuffer sb = new StringBuffer(); StringBuilder childTblCreateQuery = new StringBuilder(); String childTblName = null; String tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new Exception( "No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } System.out.println("Temporary Report Table Name:\t" + tblName); sb.append("CREATE TABLE \t" + tblName + " \t ("); AttributeDescriptor[] attrDescArr = descriptor.getAllAttributeDescriptors(); if (attrDescArr != null) { for (AttributeDescriptor attrDesc : attrDescArr) { String identifier = attrDesc.getIdentifier(); identifier = identifier.replace(' ', '_'); String columnName = getRenderColumnName(identifier, tableDef); identifier = (null == columnName) ? identifier : columnName; logger.info("attrDesc.getIdentifier()====\t" + attrDesc.getIdentifier()); if (identifier.equals("RowId")) { // ProfileRunSummary Report contains RowId, which is conflicting with Oracle RowId, so // appended // RowId with 123 sb.append(identifier + "123" + "\tvarchar2(4000),"); } else if (identifier.equals("User")) { // Unmanaged Device Report contains User, which is conflicting with Oracle User, so appended // User with 123 sb.append(identifier + "123" + "\tvarchar2(4000),"); } else if (identifier.equalsIgnoreCase("Comment")) { // Pending Approvals Report contains Comment, which is conflicting with Oracle Reserved Word // Comment, so appended // Comment with 123 sb.append(identifier + "123" + "\tvarchar2(4000),"); } else if (identifier.equals("Size")) { // Manage script repository export contains size, which is conflicting with Oracle reserved // word, and this column changed to 'size123' sb.append(identifier + "123" + "\tvarchar2(4000),"); } else if (identifier.toLowerCase().equals("timestamp")) { // Manage script repository export contains size, which is conflicting with Oracle reserved // word, and this column changed to 'size123' sb.append(identifier + "123" + "\tvarchar2(4000),"); } else if (identifier.toLowerCase().equals("violationdetails")) { // Manage script repository export contains size, which is conflicting with Oracle reserved // word, and this column changed to 'size123' sb.append(identifier + "\tBlob,"); } else { sb.append(identifier + "\tvarchar2(4000),"); } } Def[] childDefArr = descriptor.getAllChildRefs(); List<StringBuffer> childBufferList = new ArrayList<>(); if (childDefArr != null && childDefArr.length > 0) { for (Def def : childDefArr) { String defName = def.getName(); sb.append(defName + "\tvarchar2(4000),"); // For Hierarchical Reports we need to create a child table // table name like reportId_sessionId_defName manipulationTempPaginationTable(reportId, sessionId, defName, null); childTblName = getTempPaginationTblName(reportId, sessionId, defName); logger.info("ChildTableName:\t" + childTblName); if (childTblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId + "and ChildTableIdentifier:\t" + defName); } // childTblName = reportId+"_"+sessionId+"_"+defName; logger.info("ChildTableName:\t" + childTblName); // childTblCreateQuery.append("CREATE TABLE \t" + childTblName + " \t ("); // childTblCreateQuery.append("PAGINATION_PK" + "\t NUMBER,"); ServiceDescriptor childServiceDesc = descriptor .getContainedServiceDescriptorByName(defName); if (childServiceDesc != null) { AttributeDescriptor[] childAttrDescArr = childServiceDesc .getAllAttributeDescriptors(); if (childAttrDescArr != null) { /* * for (AttributeDescriptor childAttrDesc : childAttrDescArr) { * logger.info("childAttrDesc.getIdentifier()====\t" + * childAttrDesc.getIdentifier()); * childTblCreateQuery.append(childAttrDesc.getIdentifier() + "\tvarchar2(4000),"); * } */ // for (String colName : childColNamesList) // { // childTblCreateQuery.append(colName + "\tvarchar2(4000),"); // } // // childTblCreateQuery.toString(); // String childTblStr = childTblCreateQuery.substring(0, // childTblCreateQuery.length() - 1); // childTblCreateQuery = new StringBuilder(); // childTblCreateQuery.append(childTblStr); // childTblCreateQuery.append(")"); // logger.info("create child table in stringbuilder format is----" // + childTblCreateQuery.toString()); StringBuffer childBuffer = new StringBuffer(); childBuffer.append("CREATE TABLE " + childTblName + " ("); // Multiple children was not considered in previous implementation. Now this is // addressed and // child table for each child entry in table definition will be created. Using child // table // column ids instead of childColNamesList which was used earlier. TableDefinition childTable = tableDef.getChildTableById(defName); ColumnDefinition[] columnDefs = childTable.getColumnDefs(); for (ColumnDefinition columnDef : columnDefs) { childBuffer.append(columnDef.getId() + " varchar2(4000),"); } childBuffer.deleteCharAt(childBuffer.length() - 1); childBuffer.append(")"); logger.info("[CREATE-PAGINATION-TEMP-TABLE] Create Child Table format is " + String.valueOf(childBuffer)); childBufferList.add(childBuffer); } } } } // sb.append("PAGINATION_PK" + "\t NUMBER,"); String tempStr = sb.substring(0, sb.length() - 1); sb = new StringBuffer(); sb.append(tempStr); sb.append(")"); logger.info("create table in stringbuffer format is----" + sb.toString()); c = DBHelper.getConnection(); if (c == null) { logger.error("Unable to Obtain Connection to create Temporary Pagination Table."); return; } try { st = c.createStatement(); } catch (SQLException e) { logger.info( "Exception Occured while Creating Statement to create Temporary Pagination Table.", e); return; } try { result = st.executeUpdate(sb.toString()); } catch (SQLException e) { logger.info( "Exception Occured while executing the Query to Create Temporary Pagination Table.", e); return; } if (result > 0) { logger.info("Table:" + tblName + "\t Created Successfully."); } // try // { // childResult = st.executeUpdate(childTblCreateQuery.toString()); // } // catch (SQLException e) // { // logger.info( // "Exception Occured while executing the Child Table Query to Create Temporary Pagination Table.", // e); // return; // } if (childBufferList.size() > 0) { for (StringBuffer childBuffer : childBufferList) { if (0 != childBuffer.length()) { try { result = st.executeUpdate(childBuffer.toString()); logger.info(result <= 2 ? "[CREATE-PAGINATION-TEMP-TABLE] Child Table '" + childTblName + "' created successfully." : "[CREATE-PAGINATION-TEMP-TABLE] Failed to cretae child table '" + childTblName + "'."); } catch (SQLException e) { logger.info( "Exception Occured while executing the Child Table Query to Create Temporary Pagination Table.", e); return; } } } } if (childResult > 0) { logger.info("Table:" + childTblName + "\t Created Successfully."); } } } } catch (Exception e) { logger.error("Exception Occured while Creating Temporary Pagination Table.", e); return; } finally { if (st != null) { st.close(); } DBHelper.releaseConnection(c); } } public static void createTempTableForPagination(TableDefinition tableDef, String reportId, String sessionId, ArrayList<String> childColNamesList) throws Exception { Connection c = null; Statement st = null; int result = -1; int childResult = -1; StringBuffer sb = new StringBuffer(); StringBuilder childTblCreateQuery = new StringBuilder(); String childTblName = null; String tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } System.out.println("Temporary Report Table Name:\t" + tblName); sb.append("CREATE TABLE \t" + tblName + " \t ("); ColumnDefinition[] colDefs = tableDef.getColumnDefs(); try { if (colDefs != null) { for (ColumnDefinition colDef : colDefs) { String identifier = colDef.getId(); identifier = identifier.replace(' ', '_'); String columnName = getRenderColumnName(identifier, tableDef); identifier = (null == columnName) ? identifier : columnName; if (identifier.equals("RowId")) { // ProfileRunSummary Report contains RowId, which is conflicting with Oracle RowId, so // appended // RowId with 123 sb.append(identifier + "123" + "\tvarchar2(4000),"); } else if (identifier.equals("User")) { // Unmanaged Device Report contains User, which is conflicting with Oracle User, so appended // User with 123 sb.append(identifier + "123" + "\tvarchar2(4000),"); } else if (identifier.equalsIgnoreCase("Comment")) { // Pending Approvals Report contains Comment, which is conflicting with Oracle Reserved Word // Comment, so appended // Comment with 123 sb.append(identifier + "123" + "\tvarchar2(4000),"); } else if (identifier.equals("Size")) { // Manage script repository export contains size, which is conflicting with Oracle reserved // word, and this column changed to 'size123' sb.append(identifier + "123" + "\tvarchar2(4000),"); } else if (identifier.toLowerCase().equals("timestamp")) { // Manage script repository export contains size, which is conflicting with Oracle reserved // word, and this column changed to 'size123' sb.append(identifier + "123" + "\tvarchar2(4000),"); } else if (identifier.toLowerCase().equals("violationdetails")) { // Manage script repository export contains size, which is conflicting with Oracle reserved // word, and this column changed to 'size123' sb.append(identifier + "\tBlob,"); } else { if (identifier.toLowerCase().equals("hostname") || identifier.toLowerCase().equals("ipaddress")) { continue; } sb.append(identifier + "\tvarchar2(4000),"); } } sb.append("hostname\tvarchar2(4000),"); sb.append("ipaddress\tvarchar2(4000),"); TableDefinition[] childDefArr = tableDef.getChildTables(); if (childDefArr != null && childDefArr.length > 0) { for (TableDefinition def : childDefArr) { String defName = def.getId(); sb.append(defName + "\tvarchar2(4000),"); // For Hierarchical Reports we need to create a child table // table name like reportId_sessionId_defName manipulationTempPaginationTable(reportId, sessionId, defName, null); childTblName = getTempPaginationTblName(reportId, sessionId, defName); logger.info("ChildTableName:\t" + childTblName); if (childTblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId + "and ChildTableIdentifier:\t" + defName); } // childTblName = reportId+"_"+sessionId+"_"+defName; logger.info("ChildTableName:\t" + childTblName); childTblCreateQuery.append("CREATE TABLE \t" + childTblName + " \t ("); ColumnDefinition[] childAttrDescArr = def.getColumnDefs(); if (childAttrDescArr != null) { /* * for (AttributeDescriptor childAttrDesc : childAttrDescArr) { * logger.info("childAttrDesc.getIdentifier()====\t" + childAttrDesc.getIdentifier()); * childTblCreateQuery.append(childAttrDesc.getIdentifier() + "\tvarchar2(4000),"); } */ for (String colName : childColNamesList) { childTblCreateQuery.append(colName + "\tvarchar2(4000),"); } childTblCreateQuery.toString(); String childTblStr = childTblCreateQuery.substring(0, childTblCreateQuery.length() - 1); childTblCreateQuery = new StringBuilder(); childTblCreateQuery.append(childTblStr); childTblCreateQuery.append(")"); logger.info("create child table in stringbuilder format is----" + childTblCreateQuery.toString()); } } } // sb.append("PAGINATION_PK" + "\t NUMBER,"); String tempStr = sb.substring(0, sb.length() - 1); sb = new StringBuffer(); sb.append(tempStr); sb.append(")"); logger.info("create table in stringbuffer format is----" + sb.toString()); c = DBHelper.getConnection(); if (c == null) { logger.error("Unable to Obtain Connection to create Temporary Pagination Table."); return; } try { st = c.createStatement(); } catch (SQLException e) { logger.info("Exception Occured while Creating Statement to create Temporary Pagination Table.", e); return; } try { result = st.executeUpdate(sb.toString()); } catch (SQLException e) { logger.info("Exception Occured while executing the Query to Create Temporary Pagination Table.", e); return; } if (result > 0) { logger.info("Table:" + tblName + "\t Created Successfully."); } try { childResult = st.executeUpdate(childTblCreateQuery.toString()); } catch (SQLException e) { logger.info( "Exception Occured while executing the Child Table Query to Create Temporary Pagination Table.", e); return; } if (childResult > 0) { logger.info("Table:" + childTblName + "\t Created Successfully."); } } } finally { ReportUtil.closeAll(c, st, null); } } public static void insertIntoTempPaginationTable(ServiceContainerImpl sImpl, String reportId, String sessionId, ArrayList<String> childColNameList) { insertIntoTempPaginationTable(sImpl.getServiceDescriptor(), sImpl, reportId, sessionId, childColNameList); } public static void insertIntoTempPaginationTable(ServiceDescriptor descriptor, ServiceContainerImpl sImpl, String reportId, String sessionId, ArrayList<String> childColNameList) { Connection c = null; PreparedStatement ps = null; if (sImpl != null) { try { StringBuffer sb = new StringBuffer(); String tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new Exception( "No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } c = DBHelper.getConnection(); if (c == null) { logger.error("Unable to get Connection."); return; } Service[] allServs = sImpl.getAllServices(); if (allServs == null || allServs.length <= 0) { logger.info("Now Rows fetchd for ReportId:" + reportId + "and SessionId:\t" + sessionId); } else { logger.info("Number of Records are:\t" + allServs.length); } // Adding to check sb = new StringBuffer(); sb.append("INSERT INTO " + tblName + "\t Values("); ArrayList<String> colNameList = getColumnInfo(descriptor); if (colNameList != null && !colNameList.isEmpty()) { for (int i = 0; i < colNameList.size() - 1; i++) { sb.append("?,"); } sb.append("?)"); } try { ps = c.prepareStatement(sb.toString()); } catch (SQLException e1) { logger.error("Exception Occured while Executing statement:", e1); } Service[] rowsArr = sImpl.getAllServices(); if (rowsArr == null) { logger.info("Report Contians No Data."); } else { for (Service rowService : rowsArr) { int i = 1; if (colNameList != null && !colNameList.isEmpty()) { for (String colName : colNameList) { try { Object obj = rowService.getAttribute(colName); if (obj instanceof ServiceContainerImpl) { ps.setString(i++, String.valueOf(obj)); if (obj != null) { insertChildTableInfo(c, obj, reportId, sessionId, sImpl, childColNameList, null, colName); } } else if (obj == null) { ps.setString(i++, ""); } else { ps.setString(i++, String.valueOf(obj)); } } catch (SQLException e) { logger.error( "Exception Occured while Inserting Data into Temporary Pagination Table:", e); return; } } } try { ps.addBatch(); } catch (SQLException e) { e.printStackTrace(); } } } try { int[] count = ps.executeBatch(); logger.info("Number of Statements Executed was:\t" + count.length); } catch (SQLException e1) { logger.error("Exception Occured while Executing Batch Insert.", e1); } } catch (Exception e) { logger.error("Exception Occured while Inserting Data into Temporary Pagination Table:", e); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing the Prepared Statement Object.", e); } } if (c != null) { try { c.setAutoCommit(true); DBHelper.releaseConnection(c); } catch (SQLException e) { logger.error("Exception Occured while Closing the Connection Object.", e); } } } } } public static void insertIntoTempPaginationTable(TableDefinition tableDefinition, ServiceDescriptor descriptor, ServiceContainer sImpl, String reportId, String sessionId, ArrayList<String> childColNameList) { Connection c = null; PreparedStatement ps = null; if (sImpl != null) { UserSession session = UserSessionManager.getInstance().getCurrentUserSession(); PaginationReportDbHelper.instance.setLoginTimeZoneName(session.getLoginTimeZone()); try { StringBuffer sb = new StringBuffer(); String tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new Exception( "No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } c = DBHelper.getConnection(); if (c == null) { logger.error("Unable to get Connection."); return; } Service[] allServs = sImpl.getAllServices(); if (allServs == null || allServs.length <= 0) { logger.info("Now Rows fetchd for ReportId:" + reportId + "and SessionId:\t" + sessionId); } else { logger.info("Number of Records are:\t" + allServs.length); } // Adding to check sb = new StringBuffer(); sb.append("INSERT INTO " + tblName + "\t Values("); ArrayList<String> colNameList = null; if (reportId.equals("extended_device_attributes_report")) { colNameList = getColumnInfo(tableDefinition); } else { colNameList = getColumnInfo(descriptor); } if (colNameList != null && !colNameList.isEmpty()) { for (int i = 0; i < colNameList.size() - 1; i++) { sb.append("?,"); } sb.append("?)"); } try { ps = c.prepareStatement(sb.toString()); } catch (SQLException e1) { logger.error("Exception Occured while Executing statement:", e1); } Service[] rowsArr = sImpl.getAllServices(); if (rowsArr == null) { logger.info("Report Contians No Data."); } else { for (Service rowService : rowsArr) { int i = 1; if (colNameList != null && !colNameList.isEmpty()) { for (String colName : colNameList) { try { Object value = rowService.getAttribute(colName); if (value == null) { ps.setString(i++, ""); } else if (value instanceof ServiceContainerImpl) { ps.setString(i++, String.valueOf(value)); if (value != null) { insertChildTableInfo(c, value, reportId, sessionId, sImpl, childColNameList, tableDefinition, colName); } } else { ColumnDefinition columnDefinition = tableDefinition.getColumn(colName); if (null == columnDefinition) { ps.setString(i++, String.valueOf(value)); continue; } RendererType renderer = columnDefinition.getRenderer(); switch (renderer) { case BOOLEAN: { value = (true == Boolean.valueOf(String.valueOf(value))) ? "Yes" : "No"; ps.setString(i++, String.valueOf(value)); break; } case DATE: case DATE_TIME: { String temp = String.valueOf(value); Long longValue = (true == temp.isEmpty()) ? new Long(0L) : Long.valueOf(temp); value = (0 == longValue || -1 == longValue) ? "" : PaginationReportDbHelper.instance.convertDateFormat(longValue, "EEE, MMM d, yyyy HH:mm:ss Z"); // value = (0 == longValue) ? "" : new SimpleDateFormat( // "EEE, MMM d, yyyy HH:mm:ss Z").format(new Date(longValue)); ps.setString(i++, String.valueOf(value)); break; } case EOL_DATE: case EOL_DIFF_DATE: { String temp = String.valueOf(value); Long longValue = (true == temp.isEmpty()) ? new Long(0L) : Long.valueOf(temp); value = (0 == longValue) ? "" : PaginationReportDbHelper.instance.convertDateFormat(longValue, "MMM d, yyyy"); // value = (0 == longValue) ? "" : new SimpleDateFormat("MMM d, yyyy") // .format(new Date(longValue)); ps.setString(i++, String.valueOf(value)); break; } default: { ps.setString(i++, String.valueOf(value)); } } } } catch (SQLException e) { logger.error( "Exception Occured while Inserting Data into Temporary Pagination Table:", e); return; } } } try { ps.addBatch(); } catch (SQLException e) { e.printStackTrace(); } } } try { int[] count = ps.executeBatch(); logger.info("Number of Statements Executed was:\t" + count.length); } catch (SQLException e1) { logger.error("Exception Occured while Executing Batch Insert.", e1); } } catch (Exception e) { logger.error("Exception Occured while Inserting Data into Temporary Pagination Table:", e); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing the Prepared Statement Object.", e); } } if (c != null) { try { c.setAutoCommit(true); DBHelper.releaseConnection(c); } catch (SQLException e) { logger.error("Exception Occured while Closing the Connection Object.", e); } } } } } public static ArrayList<String> getColumnInfo(TableDefinition tableDefinition) { ArrayList<String> colNameList = new ArrayList<String>(); ColumnDefinition[] colDefs = tableDefinition.getColumnDefs(); for (ColumnDefinition colDef : colDefs) { if (colDef.getId().equalsIgnoreCase("hostname") || colDef.getId().equalsIgnoreCase("ipaddress")) { continue; } colNameList.add(colDef.getId()); } colNameList.add("HOSTNAME"); colNameList.add("IPADDRESS"); return colNameList; } public static ServiceContainerImpl getManagementDataFromTempTable(String reportId, String sessionId, ServiceDescriptor descriptor, ArrayList<String> childColumnsList, Map<String, String> mapOfConditions) throws Exception { Connection connection = null; Statement statement = null; ResultSet rs = null; String tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } logger.info("Getting Data from the following Table Name:\t" + tblName); ServiceContainerImpl sImpl = new ServiceContainerImpl(descriptor, null); Def[] childDefArr = descriptor.getAllChildRefs(); ArrayList<String> childIdentifersList = null; String childIdentifierName = null; if (childDefArr != null && childDefArr.length > 0) { childIdentifersList = new ArrayList<String>(); for (Def def : childDefArr) { childIdentifierName = def.getName(); childIdentifersList.add(childIdentifierName); } } connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); return null; } int condition = mapOfConditions.size(); StringBuffer buffer = new StringBuffer(); buffer.append((0 == mapOfConditions.size() ? "" : "WHERE ")); for (Map.Entry<String, String> map : mapOfConditions.entrySet()) { condition -= 1; // decrease buffer.append(map.getValue()); buffer.append((0 == condition) ? " " : " OR "); } String query = "SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (SELECT * FROM " + tblName + " ) Q1 " + String.valueOf(buffer) + " ) "; try { statement = connection.createStatement(); rs = statement.executeQuery(query); ArrayList<String> columnList = getColumnInfo(descriptor); while ((rs != null) && rs.next()) { ServiceImpl service = new ServiceImpl(); if (columnList != null) { for (String column : columnList) { String columnName = column.replace(' ', '_'); // Need to add Code to update for Child Tables: // if (column.equalsIgnoreCase("JobRunSummary")) if (childIdentifersList != null && childIdentifersList.contains(column)) { HashMap<String, String> parentKeyValueMap = new HashMap<String, String>(); AttrDef[] attrDefArr = descriptor.getKeys(); if (attrDefArr != null) { for (AttrDef attDef : attrDefArr) { String key = attDef.getName(); String value = rs.getString(attDef.getName()); parentKeyValueMap.put(key, value); } } // we need to populate Child Table Information // 1. get ChildTableName // String childInfoName = "JobRunSummary"; String childInfoName = childIdentifierName; String childTblName = getTempPaginationTblName(reportId, sessionId, childInfoName); if (childTblName == null) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId + " and ChildTblName:\t" + childInfoName); } logger.info("Getting Data from the following Table Name:\t" + childTblName); ServiceContainerImpl servImpl = getChildTblInformation1(childTblName, parentKeyValueMap, sImpl, childColumnsList); // ServiceImpl servImpl = getChildTblInformation(childTblName, parentKeyValueMap, sImpl, // childColumnsList); // service.setAttribute(column, servImpl); if (servImpl != null && servImpl.size() > 0) { // service.setAttribute(column, servImpl.getServiceContainer()); service.setAttribute(column, servImpl); } // service.setServiceContainer(servImpl); } else if (column.equals("RowId")) { // In ProfileRunSummary Report, column named RowId, it is conflicting with RowId in Oracle, // so appended 123 to RowId. service.setAttribute("RowId", rs.getString(column + "123")); } else if (column.equals("User")) { // In Unmanaged Device Report, column named User, it is conflicting with User in Oracle, so // appended 123 to User. service.setAttribute("User", rs.getString(column + "123")); } else if (column.equals("Comment")) { // In My Pending Approvals Report, column named Comment, it is conflicting with Comment in // Oracle, so appended 123 to Comment. service.setAttribute("Comment", rs.getString(column + "123")); } else if (column.equals("Size")) { // Manage script repository export contains size, which is conflicting with Oracle reserved // word, and this column changed to 'size123' service.setAttribute("Size", rs.getString(column + "123")); } else if (column.equals("Timestamp")) { service.setAttribute("Timestamp", rs.getString(column + "123")); } else { service.setAttribute(column, rs.getString(columnName)); } } } sImpl.addManageServices(service); service.setServiceContainer(sImpl); } } catch (Exception ex) { logger.error("Error while creating customer upload details report.", ex); try { throw ex; } catch (Exception e) { e.printStackTrace(); } } finally { try { if (rs != null) { rs.close(); } } catch (Exception ee) { } if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing Statement:", e); } } DBHelper.releaseConnection(connection); } return sImpl; } public static int getTempTableRecordCountByCol(String reportId, String sessionId, TableDefinition tableDef, Map<String, String> mapOfConditions, List<ColumnLevelFilter> columnLevelFilters, String uniqueColumn) throws Exception { // using report and session id get temp table name String tempTableName = getTempPaginationTblName(reportId, sessionId); if (null == tempTableName) { String error = "Failed to get Temp Table name for ReportId[" + reportId + "] SessionId[" + sessionId + "]"; logger.error(error); throw new Exception(error); } // check whether temp table exist or not. boolean isTempTableExists = checkTempTableExists(reportId, sessionId); if (false == isTempTableExists) { String error = "Table does not exist for Report Id[" + reportId + "] SessionId[" + sessionId + "]"; logger.error(error); throw new Exception(error); } int totalRecordCount = 0; Connection connection = null; Statement statement = null; ResultSet resultset = null; try { connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); throw new Exception("Unable to get connection"); } StringBuffer buffer = new StringBuffer(); if (null != mapOfConditions) { if (reportId.equalsIgnoreCase("device_details") || reportId.equalsIgnoreCase("device_with_additional_details")) { buffer = getSpecificQueryCondition(mapOfConditions, reportId, tableDef); if (!buffer.equals("")) { buffer = new StringBuffer().append(" Q1 ").append(buffer); } } else { int condition = mapOfConditions.size(); buffer.append((0 == mapOfConditions.size() ? "" : " WHERE ")); for (Map.Entry<String, String> map : mapOfConditions.entrySet()) { String columnName = map.getKey(); if (null != tableDef) { // Add the condition for refering the device table only if the report contains a column // for deviceID field else by pass. If there exist any other report then new mechanism // should // be introduced . if (isDeviceINQueryRequired(tableDef, reportId, columnName)) { if (true == "hostname".equals(columnName.toLowerCase())) { if (isPostgres()) { buffer.append("DEVICE IN (SELECT CAST (ID AS TEXT) FROM IOS_VERSION WHERE " + map.getValue() + ")"); } else { buffer.append("DEVICE IN (SELECT ID FROM IOS_VERSION WHERE " + map.getValue() + ")"); } } else if (true == "ipaddress".equals(columnName.toLowerCase())) { if (isPostgres()) { buffer.append("DEVICE IN (SELECT CAST (ID AS TEXT) FROM NODES WHERE " + map.getValue() + ")"); } else { buffer.append( "DEVICE IN (SELECT ID FROM NODES WHERE " + map.getValue() + ")"); } } else { buffer.append(map.getValue()); } } else if (isCustomerIdINQueryRequired(tableDef, reportId)) { if (true == "customerid".equals(columnName.toLowerCase())) { String value = map.getValue(); value = value.replace(columnName, "customer_name"); buffer.append("CUSTOMERID IN (SELECT customer_id FROM customers WHERE " + value + ")"); } else { buffer.append(map.getValue()); } } else { buffer.append(map.getValue()); } } else { buffer.append(map.getValue()); } condition -= 1; // decrease buffer.append((0 == condition) ? " " : " OR "); } } } String columnLevelWhere = null; if (isPostgres()) { columnLevelWhere = PostgresReportDBHelper.getColumnLevelFilterQuery(columnLevelFilters, tableDef, reportId); } else { columnLevelWhere = getColumnLevelFilterQuery(columnLevelFilters, tableDef, reportId); } String additionalQuery = ""; if (uniqueColumn != null && "hostname".equals(uniqueColumn.toLowerCase()) && isDeviceINQueryRequired(tableDef, reportId, uniqueColumn.toLowerCase())) { if (isPostgres()) { additionalQuery = " temp LEFT JOIN IOS_VERSION ON temp.DEVICE=CAST(IOS_VERSION.ID AS TEXT)"; } else { additionalQuery = " temp LEFT JOIN IOS_VERSION ON temp.DEVICE=TO_CHAR(IOS_VERSION.ID)"; } } else if (uniqueColumn != null && true == "ipaddress".equals(uniqueColumn.toLowerCase()) && isDeviceINQueryRequired(tableDef, reportId, uniqueColumn.toLowerCase())) { additionalQuery = " temp LEFT JOIN NODES ON temp.DEVICE=TO_CHAR(NODES.ID) "; if (isPostgres()) { additionalQuery = " temp LEFT JOIN NODES ON temp.DEVICE=CAST(NODES.ID AS TEXT) "; } } else if (uniqueColumn != null && true == "customername".equalsIgnoreCase(uniqueColumn)) { additionalQuery = " temp LEFT JOIN CUSTOMERS ON temp.CUSTOMERID=CUSTOMERS.CUSTOMER_ID"; } String query = null; if (uniqueColumn == null) { query = "SELECT COUNT(*) AS ROWCOUNT FROM (SELECT * FROM " + tempTableName + String.valueOf(buffer) + " ) X " + columnLevelWhere; } else if (uniqueColumn.equalsIgnoreCase("CustomerName")) { query = "SELECT COUNT(*) AS ROWCOUNT FROM (SELECT DISTINCT customer_name as CustomerName FROM (SELECT * FROM " + tempTableName + additionalQuery + String.valueOf(buffer) + " ) X " + columnLevelWhere + " ) Y"; } else { query = "SELECT COUNT(*) AS ROWCOUNT FROM (SELECT DISTINCT " + ColumnLevelFilter.getColumnName(uniqueColumn) + " FROM (SELECT * FROM " + tempTableName + additionalQuery + String.valueOf(buffer) + " ) X " + columnLevelWhere + " ) Y"; } // if (colName != null) // { // query = "SELECT COUNT(DISTINCT " + colName + ") AS ROWCOUNT FROM " + tempTableName // + String.valueOf(buffer); // } statement = connection.createStatement(); resultset = statement.executeQuery(query); while ((resultset != null) && resultset.next()) { totalRecordCount = resultset.getInt("ROWCOUNT"); break; } return totalRecordCount; } catch (Exception ex) { logger.error("Error while getting row count from temp table.", ex); throw ex; } finally { try { if (null != resultset) { resultset.close(); } } catch (Exception ee) { } if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Exception while closing Statement:", e); } } DBHelper.releaseConnection(connection); } } public static int getTempTableRecordCount(String reportId, String sessionId, TableDefinition tableDef, Map<String, String> mapOfConditions, List<ColumnLevelFilter> columnLevelFilters, String uniqueColumn, List<DrillDownFilter> drillDownFilters) throws Exception { return getTempTableRecordCountByCol(reportId, sessionId, tableDef, mapOfConditions, columnLevelFilters, uniqueColumn); } public static ServiceContainerImpl getReportSummary(String reportId, String sessionId, List<String> listOfTableDefColumns, List<String> listOfSelectedColumns, String conditions, String groupByColumnName, ServiceDescriptor descriptor) throws Exception { String tempTableName = getTempPaginationTblName(reportId, sessionId); if (null == tempTableName) { throw new Exception("No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } Connection connection = null; Statement statement = null; ResultSet resultset = null; ServiceContainerImpl container = null; try { connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); throw new Exception("Unable to get connection"); } container = new ServiceContainerImpl(); int size = 0; StringBuilder queryBuilder = new StringBuilder(); queryBuilder.append("SELECT "); // If child table exists, e.g. change mgmt report contains job metadata as child. Then create sql query in // different way, e.g. // SELECT COUNT(*) AS COUNT, paramvalue as "change rec id" FROM TEMP_1412097918439232000 where // paramname='change rec id' GROUP BY paramvalue; // Start child.. Def[] childDefArr = descriptor.getAllChildRefs(); String childIdentifierName = null; if (childDefArr != null && childDefArr.length > 0) { for (Def def : childDefArr) { childIdentifierName = def.getName(); } } String childTblName = getTempPaginationTblName(reportId, sessionId, childIdentifierName); ServiceDescriptor chSd = descriptor.getContainedServiceDescriptorByName("JobMetadata"); // This is hack to treat JobType and JobStatus charts differently. These are not custom job metadata // attributes. boolean flag = ((groupByColumnName != null) && (groupByColumnName.equalsIgnoreCase("JobType") || groupByColumnName.equalsIgnoreCase("JobStatus"))) ? false : true; if (childTblName != null && chSd != null && flag) { // Making safe assumption that size of listOfSelectedColumns is always 2. // JobMetadata child columns - jobId, paramName, paramValue, paramType AttrDef[] allAttributes = chSd.getAllAttributes(); if (allAttributes.length >= 2) { String attrName = allAttributes[0].getName(); String attrValue = allAttributes[1].getName(); queryBuilder.append(listOfSelectedColumns.get(0)); queryBuilder.append(","); // queryBuilder.append(" paramvalue AS "); queryBuilder.append(" " + attrValue + " AS "); queryBuilder.append("\"" + listOfSelectedColumns.get(1) + "\""); // Create condition string specific for child tables // conditions = "paramname = '" + listOfSelectedColumns.get(1) + "'"; conditions = attrName + " = '" + listOfSelectedColumns.get(1) + "'"; // Change group by column name for child tables // groupByColumnName = "paramvalue"; groupByColumnName = attrValue; } // Change table name to child table tempTableName = childTblName; } else { for (String columnName : listOfSelectedColumns) { queryBuilder.append(++size == listOfSelectedColumns.size() ? columnName : columnName + ", "); } } queryBuilder.append(" FROM "); queryBuilder.append(tempTableName); if (null != conditions) { queryBuilder.append(" WHERE "); queryBuilder.append(conditions); } if (null != groupByColumnName) { queryBuilder.append(" GROUP BY " + groupByColumnName + " ORDER BY " + groupByColumnName); } String query = getReportSummarySpecificQuery(queryBuilder, reportId, listOfSelectedColumns, tempTableName, groupByColumnName); if (query == null) { query = String.valueOf(queryBuilder); } statement = connection.createStatement(); resultset = statement.executeQuery(query); while ((null != resultset) && resultset.next()) { ServiceImpl service = new ServiceImpl(); for (String columnName : listOfTableDefColumns) { if (columnName.equalsIgnoreCase("ExecutionTime") && isPostgres()) { String value = resultset.getString(columnName); try { long val = new SimpleDateFormat("EEE, MMM d, yyyy HH:mm:ss").parse(value).getTime(); service.put(columnName, val); } catch (Exception e) { service.put(columnName, value); } } else { String value = (true == "timestamp".equals(columnName.toLowerCase())) ? resultset.getString(columnName + "123") : resultset.getString(columnName); service.put(columnName, value); } } container.addManageServices(service); } } catch (Exception e) { logger.error("Error while getting row count from temp table.", e); throw e; } finally { try { if (null != resultset) { resultset.close(); } } catch (Exception ee) { } if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Exception while closing Statement:", e); } } DBHelper.releaseConnection(connection); } return container; } private static String getReportSummarySpecificQuery(StringBuilder queryBuilder, String reportId, List<String> listOfSelectedColumns, String tmpName, String groupByColumnName) { String query = null; if (reportId.equalsIgnoreCase("nccm_memory_status") || reportId.equalsIgnoreCase("nccm_cpu_status") || reportId.equalsIgnoreCase("nccm_user_status") || reportId.equalsIgnoreCase("nccm_license_status") || reportId.equalsIgnoreCase("nccm_jobs_status") || reportId.equalsIgnoreCase("nccm_memory_status")) { Calendar c = Calendar.getInstance(); c.setTime(new Date()); c.add(Calendar.DATE, -2); long threeMonthDate = c.getTimeInMillis(); if (listOfSelectedColumns.contains("timestamp123")) { if (isPostgres()) { query = "Select * from (" + queryBuilder + " )" + tmpName + " WHERE timestamp123 >" + "CAST(" + threeMonthDate + " AS TEXT) ORDER BY timestamp123 ASC"; } else { query = "Select * from (" + queryBuilder + " )" + tmpName + " WHERE timestamp123 >" + threeMonthDate + " ORDER BY timestamp123 ASC"; } } } else if (reportId.equals("device_config_changes_summary")) { query = "Select * from (" + String.valueOf(queryBuilder) + " )" + tmpName + " ORDER BY " + groupByColumnName + " ASC"; } return query; } public static ServiceContainerImpl getComplianceReportSummary(List<String> listOfTableDefColumns, String policyGroupName, List<String> queryList) throws Exception { Connection connection = null; Statement statement = null; ResultSet resultset = null; ServiceContainerImpl container = null; try { connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); throw new Exception("Unable to get connection"); } String query = queryList.get(0); container = new ServiceContainerImpl(); statement = connection.createStatement(); resultset = statement.executeQuery(query); while ((null != resultset) && resultset.next()) { ServiceImpl service = new ServiceImpl(); for (String columnName : listOfTableDefColumns) { service.put(columnName, resultset.getString(columnName)); } container.addManageServices(service); } // Add devices with no violations also to the container. This is calculated by selecting devices which has // rule_result = 'NotApplicable' or 'Passed' and out of these devices exclude the ones for which rule_result // = 'Failed' String noViolationsQuery = queryList.get(1); resultset = DBHelper.executeQuery(noViolationsQuery); if (resultset.next()) { ServiceImpl service = new ServiceImpl(); for (String columnName : listOfTableDefColumns) { service.put(columnName, resultset.getString(columnName)); } container.addManageServices(service); } // query = "select 'No_Data' as SEVERITY, count(distinct(device_id)) as COUNT from POLICY_GROUP_VIOLATIONS " // + "where device_id not in (select device_id from POLICY_GROUP_VIOLATIONS " // + "where policy_grp_name = '" + policyGroupName + "')"; // // query = "select count(DEVICE) AS COUNT, Severity from PSIRT_VIEW group by Severity"; // // resultset = DBHelper.executeQuery(query); // // while ((null != resultset) && resultset.next()) // { // ServiceImpl service = new ServiceImpl(); // for (String columnName : listOfTableDefColumns) // { // service.put(columnName, resultset.getString(columnName)); // } // container.addManageServices(service); // } } catch (Exception e) { logger.error("Error while getting row count from temp table.", e); throw e; } finally { try { if (null != resultset) { resultset.close(); } } catch (Exception ee) { } if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Exception while closing Statement:", e); } } if (connection != null) { try { connection.commit(); } catch (Exception ex) { logger.info("Error while commiting Config Change Summary Query.", ex); } } try { connection.setAutoCommit(true); } catch (Exception e) { logger.debug("Error while Setting auto commit", e); } DBHelper.releaseConnection(connection); } return container; } private static String getRenderColumnName(String columnName, TableDefinition tableDefinition) { if (null != tableDefinition) { ColumnDefinition columnDefinition = tableDefinition.getColumn(columnName); if (null == columnDefinition) { return null; } RendererType renderer = columnDefinition.getRenderer(); switch (renderer) { case DEVICE: { return "DEVICE"; } default: { return null; } } } return null; } public static List<UserDefinedReport> getAllUserDefinedReports(int start, int pageLength) { StringBuffer sb = MgmtReportsPaginationUtil.getQuery(start, pageLength, "user_defined_reports", null); ArrayList<UserDefinedReport> appList = new ArrayList<UserDefinedReport>(); ResultSet rs = null; try { rs = DBHelper.executeQuery(sb.toString()); while (rs.next()) { UserDefinedReport report = new UserDefinedReport(); report.setIdentifier(rs.getString("report_id")); report.setTitle(rs.getString("title")); report.setCreatedUser(rs.getString("creator_name")); Timestamp ts = rs.getTimestamp("created_time"); if (ts != null) { report.setCreationTime(ts.getTime()); } String defintionXML = rs.getString("report_definition"); if ((defintionXML != null) && (defintionXML.length() > 0)) { report.importReportDefinitionFromXML(defintionXML); report.getReportDef().getProps().setId(report.getIdentifier()); appList.add(report); } String serviceXML = rs.getString("service_definition"); if ((serviceXML != null) && (serviceXML.length() > 0)) { report.setServiceDef(serviceXML); } String decoratorXML = rs.getString("decorator_definition"); if ((decoratorXML != null) && (decoratorXML.length() > 0)) { report.setDecoratorDef(decoratorXML); } } } catch (Exception ee) { logger.warn("Error while loading user defined reports.", ee); } finally { try { rs.close(); } catch (Exception ee) { logger.debug("Error while closing result set", ee); } } return appList; } public static int getAllUserDefinedReportCount() { return PolicyDBHelper.getRecordCount("user_defined_reports", null); } public static int getStartReportRecordCount(String reportId, String sessionId, TableDefinition tableDef, Map<String, String> mapOfRowFilters) throws Exception { // using report and session id get temp table name String tempTableName = getTempPaginationTblName(reportId, sessionId); if (null == tempTableName) { String error = "Failed to get Temp Table name for ReportId[" + reportId + "] SessionId[" + sessionId + "]"; logger.error(error); throw new Exception(error); } // check whether temp table exist or not. boolean isTempTableExists = checkTempTableExists(reportId, sessionId); if (false == isTempTableExists) { String error = "Table does not exist for Report Id[" + reportId + "] SessionId[" + sessionId + "]"; logger.error(error); throw new Exception(error); } int totalRecordCount = 0; Connection connection = null; Statement statement = null; ResultSet resultset = null; try { connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); throw new Exception("Unable to get connection"); } String query = "SELECT TOTAL_COUNT FROM PAGINATION_TABLE_INDEX WHERE page_tbl_name ='" + tempTableName + "'"; statement = connection.createStatement(); resultset = statement.executeQuery(query); while ((resultset != null) && resultset.next()) { totalRecordCount = resultset.getInt("TOTAL_COUNT"); break; } return totalRecordCount; } catch (Exception ex) { logger.error("Error while getting total count from temp table.", ex); throw ex; } finally { try { if (null != resultset) { resultset.close(); } } catch (Exception ee) { } if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Exception while closing Statement:", e); } } DBHelper.releaseConnection(connection); } } public static void deleteTempTable(String reportId, String sessionId, String view) throws SQLException { String tblName = getTempPaginationTblName(reportId, sessionId); logger.info("Temp Table to be Deleted is:\t" + tblName); if (tblName == null) { logger.error( "Table name for reportID : " + reportId + " and sessionID : " + sessionId + " doesnt exsist."); return; } try { DBHelper.executeUpdate("DROP " + view + " " + tblName); } catch (Exception e) { logger.error("Exception occured while dropping the materilized view" + tblName, e); } // Now we need to delete entry from PAGINATION_TABLE_INDEX also deleteEntryFromIndexTable(reportId, sessionId); } public static StringBuffer getQuery(String reportId, int start, int pageLength, String tblName, Map<String, String> mapOfConditions, TableDefinition tableDef, Map<String, String> sortingFilters, String[] selectedObjects, HashMap filterMap, Connection c, List<ColumnLevelFilter> columnLevelFilters, String uniqueColumn) { StringBuffer buffer = new StringBuffer(); String deviceColumn = getDeviceColumn(reportId); String query = getDeviceFilterQuery(tblName, selectedObjects, filterMap, c, deviceColumn); if (query == null) { StringBuffer queryBuffer = getSortingCondition(sortingFilters, reportId, tableDef, tblName, columnLevelFilters, uniqueColumn); query = queryBuffer.toString(); buffer = getQueryCondition(mapOfConditions, reportId, tableDef); } else { StringBuffer queryBuffer = getSortingCondition(sortingFilters, reportId, tableDef, query, columnLevelFilters, uniqueColumn); query = "(" + queryBuffer.toString() + ")"; buffer = getQueryCondition(mapOfConditions, reportId, tableDef); } StringBuffer sb = new StringBuffer(); if (start <= 0) { start = 1; } String finalQuery = query; if (uniqueColumn != null) { finalQuery = "SELECT DISTINCT " + ColumnLevelFilter.getColumnName(uniqueColumn) + " AS " + uniqueColumn + " from ( SELECT * from (" + query + ") X " + String.valueOf(buffer) + " ) T order by " + ColumnLevelFilter.getColumnName(uniqueColumn) + " ASC nulls first"; } else { finalQuery = "SELECT * from (" + query + ") X " + String.valueOf(buffer) + ""; } if (start <= 0 && pageLength <= 0) { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM ( " + finalQuery + " ) Q1 " + " ) "); } else if (start <= 0 && pageLength > 0) { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (" + finalQuery + " ) Q1 " + " ) WHERE RN BETWEEN " + start + " AND " + pageLength); } else if (pageLength <= 0 && start > 0) { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (" + finalQuery + " ) Q1 " + " ) WHERE RN >= " + start); } else { sb.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (" + finalQuery + " ) Q1 " + " ) WHERE RN BETWEEN " + start + " AND " + pageLength); } return sb; } public static String getDeviceFilterQuery(String tblName, String[] selectedObjects, HashMap filterMap, Connection c) { filterSelectedObjects(selectedObjects, filterMap, c); String sbuf = null; if ((selectedObjects != null) && (selectedObjects.length != 0)) { if (ProductProfileFactory.getInstance().getProfile().getDatabaseProperties().getDatabaseType() .equals(ProductDatabaseType.DERBY)) { sbuf = "(select * from " + tblName + " tbl , SESSION.node_id_filters nd where tbl.id=nd.node_id) "; } else { sbuf = "(select * from " + tblName + " tbl , node_id_filters nd where tbl.id=nd.node_id )"; } } return sbuf; } public static String getDeviceFilterQuery(String tblName, String[] selectedObjects, HashMap filterMap, Connection c, String deviceId) { filterSelectedObjects(selectedObjects, filterMap, c); String sbuf = null; if (selectedObjects != null) { if (ProductProfileFactory.getInstance().getProfile().getDatabaseProperties().getDatabaseType() .equals(ProductDatabaseType.DERBY)) { sbuf = "(select * from " + tblName + " tbl , SESSION.node_id_filters nd where tbl." + deviceId + "=nd.node_id) "; } else { sbuf = "(select * from " + tblName + " tbl , node_id_filters nd where tbl." + deviceId + "=nd.node_id )"; } } return sbuf; } private static void filterSelectedObjects(String[] selectedObjects, HashMap filterMap, Connection c) { try { if ((selectedObjects != null) && (selectedObjects.length > 0)) { NodeIdUtils.insertIntoTempNodeFilterTable( NodeIdUtils.getNodeIdsAsArray(selectedObjects, filterMap, false), c); } } catch (Exception ex) { logger.warn("Error while executing inserting node id into filter Query.", ex); } } public static int getTempTableRecordCount(String reportId, String sessionId, TableDefinition tableDef, Map<String, String> mapOfConditions, String[] selectedObjects, List<ColumnLevelFilter> columnLevelFilters, String uniqueColumn) throws PariException { // using report and session id get temp table name String tempTableName = getTempPaginationTblName(reportId, sessionId); if (null == tempTableName) { String error = "Failed to get Temp Table name for ReportId[" + reportId + "] SessionId[" + sessionId + "]"; logger.error(error); throw new PariException(error); } // check whether temp table exist or not. boolean isTempTableExists = false; try { isTempTableExists = checkTempTableExists(reportId, sessionId); } catch (Exception e) { throw new PariException(e.getMessage()); } if (false == isTempTableExists) { String error = "Table does not exist for Report Id[" + reportId + "] SessionId[" + sessionId + "]"; logger.error(error); throw new PariException(error); } int totalRecordCount = 0; Connection connection = null; Statement statement = null; ResultSet resultset = null; try { connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); throw new Exception("Unable to get connection"); } connection.setAutoCommit(false); StringBuffer buffer = new StringBuffer(); if (null != mapOfConditions) { int condition = mapOfConditions.size(); buffer.append((0 == mapOfConditions.size() ? "" : " WHERE ")); for (Map.Entry<String, String> map : mapOfConditions.entrySet()) { String columnName = map.getKey(); if (null != tableDef) { // Add the condition for refering the device table only if the report contains a column // for deviceID field else by pass. If there exist any other report then new mechanism should // be introduced . if (isDeviceINQueryRequired(tableDef, reportId, columnName)) { if (true == "hostname".equals(columnName.toLowerCase())) { buffer.append( "DEVICE IN (SELECT ID FROM IOS_VERSION WHERE " + map.getValue() + ")"); } else if (true == "ipaddress".equals(columnName.toLowerCase())) { buffer.append("DEVICE IN (SELECT ID FROM NODES WHERE " + map.getValue() + ")"); } else { buffer.append(map.getValue()); } } else if (isCustomerIdINQueryRequired(tableDef, reportId)) { if (true == "customerid".equals(columnName.toLowerCase())) { String value = map.getValue(); value = value.replace(columnName, "customer_name"); buffer.append( "CUSTOMERID IN (SELECT customer_id FROM customers WHERE " + value + ")"); } else { buffer.append(map.getValue()); } } else { buffer.append(map.getValue()); } } else { buffer.append(map.getValue()); } condition -= 1; // decrease buffer.append((0 == condition) ? " " : " OR "); } } String columnLevelWhere = getColumnLevelFilterQuery(columnLevelFilters, tableDef, reportId); String query = null; if (uniqueColumn == null) { String tblQuery = tempTableName; if (selectedObjects != null) { String deviceColumn = getDeviceColumn(reportId); tblQuery = getDeviceFilterQuery(tempTableName, selectedObjects, null, connection, deviceColumn); } String fromClause = ""; if ("hostname".equalsIgnoreCase(uniqueColumn) && isDeviceINQueryRequired(tableDef, reportId, uniqueColumn)) { fromClause = "(SELECT * FROM " + tblQuery + " TBL LEFT JOIN IOS_VERSION ON TBL.DEVICE=TO_CHAR(IOS_VERSION.ID))"; } else if ("ipaddress".equalsIgnoreCase(uniqueColumn) && isDeviceINQueryRequired(tableDef, reportId, uniqueColumn)) { fromClause = "(SELECT * FROM " + tblQuery + " TBL LEFT JOIN NODES ON TBL.DEVICE=TO_CHAR(NODES.ID))"; } else { fromClause = tblQuery; } query = "SELECT COUNT(*) AS ROWCOUNT FROM (SELECT * FROM " + fromClause + " X " + String.valueOf(buffer) + ") T " + columnLevelWhere; } else { String tblQuery = tempTableName; if (selectedObjects != null) { String deviceColumn = getDeviceColumn(reportId); tblQuery = getDeviceFilterQuery(tempTableName, selectedObjects, null, connection, deviceColumn); } String fromClause = ""; if ("hostname".equalsIgnoreCase(uniqueColumn) && isDeviceINQueryRequired(tableDef, reportId, uniqueColumn)) { fromClause = "(SELECT * FROM " + tblQuery + " TBL LEFT JOIN IOS_VERSION ON TBL.DEVICE=TO_CHAR(IOS_VERSION.ID))"; } else if ("ipaddress".equalsIgnoreCase(uniqueColumn) && isDeviceINQueryRequired(tableDef, reportId, uniqueColumn)) { fromClause = "(SELECT * FROM " + tblQuery + " TBL LEFT JOIN NODES ON TBL.DEVICE=TO_CHAR(NODES.ID))"; } else { fromClause = tblQuery; } query = "SELECT COUNT(*) AS ROWCOUNT FROM (SELECT DISTINCT " + ColumnLevelFilter.getColumnName(uniqueColumn) + " from (SELECT * FROM " + fromClause + " X " + String.valueOf(buffer) + ") T " + columnLevelWhere + " ) Y"; } statement = connection.createStatement(); resultset = statement.executeQuery(query); while ((resultset != null) && resultset.next()) { totalRecordCount = resultset.getInt("ROWCOUNT"); break; } return totalRecordCount; } catch (Exception ex) { logger.error("Error while getting row count from temp table.", ex); throw new PariException(ex.getMessage()); } finally { try { if (null != resultset) { resultset.close(); } } catch (Exception ee) { } if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Exception while closing Statement:", e); } } if (connection != null) { try { connection.commit(); } catch (Exception ex) { logger.info("Error while commiting Config Change Summary Query.", ex); } } try { connection.setAutoCommit(true); } catch (Exception e) { logger.debug("Error while Setting auto commit", e); } DBHelper.releaseConnection(connection); } } private static String getDeviceColumn(String reportId) { if (reportId.equalsIgnoreCase("EnhancedServerJobLogs")) { return "Device"; } return "id"; } public static ServiceContainer getConfigChangeReportSummary(String query, String chartScale) throws Exception { Connection connection = null; Statement statement = null; ResultSet resultset = null; ServiceContainerImpl container = null; try { connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); throw new Exception("Unable to get connection"); } connection.setAutoCommit(false); container = new ServiceContainerImpl(); statement = connection.createStatement(); if (isPostgres()) { PostgresTimeZoneDBHelper.setClientTimeZone(connection); } resultset = statement.executeQuery(query); while ((null != resultset) && resultset.next()) { ServiceImpl service = new ServiceImpl(); service.put(chartScale, resultset.getString(chartScale)); service.put("Value", resultset.getString("Value")); container.addManageServices(service); } } catch (Exception e) { logger.error("Error while getting row count from temp table.", e); throw e; } finally { try { if (null != resultset) { resultset.close(); } } catch (Exception ee) { } if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Exception while closing Statement:", e); } } try { connection.commit(); connection.setAutoCommit(true); DBHelper.releaseConnection(connection); } catch (Exception e) { logger.error("Exception while closing Statement:", e); } } return container; } public static ServiceContainer getReportSummary(String reportId, String query, List<String> listOfTableDefColumns, List<String> listOfSelectedColumns) throws Exception { Connection connection = null; Statement statement = null; ResultSet resultset = null; ServiceContainerImpl container = null; try { connection = DBHelper.getConnection(); container = new ServiceContainerImpl(); if (connection == null) { logger.info("Unable to get Connection."); throw new Exception("Unable to get connection"); } statement = connection.createStatement(); resultset = statement.executeQuery(query); while ((null != resultset) && resultset.next()) { ServiceImpl service = new ServiceImpl(); for (String columnName : listOfTableDefColumns) { String value = (true == "timestamp".equals(columnName.toLowerCase())) ? resultset.getString(columnName + "123") : resultset.getString(columnName); service.put(columnName, value); } container.addManageServices(service); } } catch (Exception e) { logger.error("Error while getting report summary.", e); throw new PariException(e.getMessage()); } finally { try { if (null != resultset) { resultset.close(); } } catch (Exception ee) { } if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Exception while closing Statement:", e); } } DBHelper.releaseConnection(connection); } return container; } public static List<Integer> getDeviceIdListExcludeModules(String query) { List<Integer> deviceIdList = new ArrayList<Integer>(); Connection connection = null; Statement statement = null; ResultSet resultset = null; try { connection = DBHelper.getConnection(); if (connection == null) { logger.info("Unable to get Connection."); throw new Exception("Unable to get connection"); } statement = connection.createStatement(); resultset = statement.executeQuery(query); while ((null != resultset) && resultset.next()) { Integer value = resultset.getInt("ID"); deviceIdList.add(value); } } catch (Exception e) { logger.error("Error while getting device id list.", e); } finally { try { if (null != resultset) { resultset.close(); } } catch (Exception ee) { } if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Exception while closing Statement:", e); } } DBHelper.releaseConnection(connection); } return deviceIdList; } public static String getQuery(String reportId, String tblName, Map<String, String> rowFilter, String childTblName, String parentCriteria, String childCriteria, TableDefinition tableDef) { StringBuffer buffer = getQueryCondition(rowFilter, reportId, tableDef); StringBuilder sb = new StringBuilder(); sb.append("(SELECT distinct parentTbl.* FROM (").append(tblName).append(") parentTbl INNER JOIN ") .append(childTblName).append(" childTbl ON parentTbl.").append(parentCriteria).append("=childTbl.") .append(childCriteria).append(" ").append(buffer).append(")"); return sb.toString(); } public static String getJobsBasedOnCustomerAccessQuery() { return JobServiceProxy.getCustomerSpecificJobQuery(); } public static boolean isPostgres() { return ServerProperties.getInstance().isPostgres(); } }