From 8e978f735eb286b9bf175dad4b27e02799c2082d Mon Sep 17 00:00:00 2001 From: Parshad Patel Date: Tue, 10 Sep 2019 16:16:39 +0900 Subject: Fix sonar issues in epsdk-analytics A "NullPointerException" could be thrown Either log or rethrow this exception Use try-with-resources or close this in a "finally" clause Issue-ID: PORTAL-562 Change-Id: Ic77f71ffdd11ad327bffebc8fd483e32633e4b30 Signed-off-by: Parshad Patel --- .../portalsdk/analytics/model/ReportLoader.java | 1614 ++++++++++---------- 1 file changed, 792 insertions(+), 822 deletions(-) (limited to 'ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportLoader.java') diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportLoader.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportLoader.java index 03df7731..8072d730 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportLoader.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportLoader.java @@ -33,10 +33,10 @@ * * ============LICENSE_END============================================ * - * + * */ /* =========================================================================================== - * This class is part of RAPTOR (Rapid Application Programming Tool for OLAP Reporting) + * This class is part of RAPTOR (Rapid Application Programming Tool for OLAP Reporting) * Raptor : This tool is used to generate different kinds of reports with lot of utilities * =========================================================================================== * @@ -44,14 +44,14 @@ * ReportLoader.java - This class is used to call database interaction related to reports. * ------------------------------------------------------------------------------------------- * - * + * * * Changes * ------- - * 28-Aug-2009 : Version 8.5.1 (Sundar); - * 18-Aug-2009 : Version 8.5.1 (Sundar); - * 27-Jul-2009 : Version 8.4 (Sundar); + * 28-Aug-2009 : Version 8.5.1 (Sundar); + * 18-Aug-2009 : Version 8.5.1 (Sundar); + * 27-Jul-2009 : Version 8.4 (Sundar); * */ package org.onap.portalsdk.analytics.model; @@ -93,89 +93,86 @@ import org.owasp.esapi.ESAPI; public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { - private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportLoader.class); - + private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportLoader.class); + + public static final String NOT_FOUND_IN_DB = " not found in the database"; + public static String loadCustomReportXML(String reportID) throws RaptorException { - Connection connection = DbUtils.getConnection(); - try { - return loadCustomReportXML(connection, reportID); - } finally { - DbUtils.clearConnection(connection); - } - } // loadCustomReportXML - - public static String loadCustomReportXML(Connection connection, String reportID) - throws RaptorException { - - StringBuffer sb = new StringBuffer(); - - String sql = Globals.getLoadCustomReportXml(); - - try(PreparedStatement stmt = connection.prepareStatement(sql)) { - - stmt.setInt(1,Integer.parseInt(reportID)); - - try(ResultSet rs = stmt.executeQuery()){ - if(Globals.isWeblogicServer()) { - java.sql.Clob clob= null; - Object obj = null; - if (rs.next()) { - clob = rs.getClob(1); - } - else - throw new RaptorException("Report " + reportID + " not found in the database"); - - int len = 0; - char[] buffer = new char[512]; - Reader in = null; - in = new InputStreamReader(clob.getAsciiStream()); - // if(obj instanceof oracle.sql.CLOB) { - // in = ((oracle.sql.CLOB) obj).getCharacterStream(); - // } else if (obj instanceof weblogic.jdbc.wrapper.Clob) { - // in = ((weblogic.jdbc.base.BaseClob) obj).getCharacterStream(); - // } - while ((len = in.read(buffer)) != -1) - sb.append(buffer, 0, len); - in.close(); - } else if (Globals.isPostgreSQL() || Globals.isMySQL()) { - String clob= null; - Object obj = null; - if (rs.next()) { - sb.append(rs.getString(1)); - } - else - throw new RaptorException("Report " + reportID + " not found in the database"); - } else { - /*oracle.sql.CLOB clob = null; - if (rs.next()) - clob = (oracle.sql.CLOB) rs.getObject(1); - else - throw new RaptorException("Report " + reportID + " not found in the database"); - int len = 0; - char[] buffer = new char[512]; - Reader in = clob.getCharacterStream(); - while ((len = in.read(buffer)) != -1) - sb.append(buffer, 0, len); - in.close();*/ - throw new RaptorException("only maria db support for this "); - } - } - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } catch (IOException ex) { - throw new RaptorException (ex.getMessage(), ex.getCause()); - } - return sb.toString(); - } // loadCustomReportXML - - private static void dbUpdateReportXML(Connection connection, String reportID, - String reportXML) throws RaptorException { - - String sql = ""; - if(!Globals.isMySQL()) - sql = Globals.getDBUpdateReportXml(); - else - sql = Globals.getDBUpdateReportXmlMySqlSelect(); + Connection connection = DbUtils.getConnection(); + try { + return loadCustomReportXML(connection, reportID); + } finally { + DbUtils.clearConnection(connection); + } + } // loadCustomReportXML + + public static String loadCustomReportXML(Connection connection, String reportID) + throws RaptorException { + + StringBuffer sb = new StringBuffer(); + + String sql = Globals.getLoadCustomReportXml(); + + try (PreparedStatement stmt = connection.prepareStatement(sql)) { + + stmt.setInt(1, Integer.parseInt(reportID)); + + try (ResultSet rs = stmt.executeQuery()) { + if (Globals.isWeblogicServer()) { + java.sql.Clob clob = null; + Object obj = null; + if (rs.next()) { + clob = rs.getClob(1); + } else + throw new RaptorException("Report " + reportID + NOT_FOUND_IN_DB); + + int len = 0; + char[] buffer = new char[512]; + Reader in = null; + in = new InputStreamReader(clob.getAsciiStream()); + // if(obj instanceof oracle.sql.CLOB) { + // in = ((oracle.sql.CLOB) obj).getCharacterStream(); + // } else if (obj instanceof weblogic.jdbc.wrapper.Clob) { + // in = ((weblogic.jdbc.base.BaseClob) obj).getCharacterStream(); + // } + while ((len = in.read(buffer)) != -1) + sb.append(buffer, 0, len); + in.close(); + } else if (Globals.isPostgreSQL() || Globals.isMySQL()) { + String clob = null; + Object obj = null; + if (rs.next()) { + sb.append(rs.getString(1)); + } else + throw new RaptorException("Report " + reportID + NOT_FOUND_IN_DB); + } else { + /* + * oracle.sql.CLOB clob = null; if (rs.next()) clob = (oracle.sql.CLOB) rs.getObject(1); else throw + * new RaptorException("Report " + reportID + NOT_FOUND_IN_DB); int len = 0; char[] buffer = new + * char[512]; Reader in = clob.getCharacterStream(); while ((len = in.read(buffer)) != -1) + * sb.append(buffer, 0, len); in.close(); + */ + throw new RaptorException("only maria db support for this "); + } + } + } catch (SQLException ex) { + logger.error(EELFLoggerDelegate.errorLogger, "SQLException occured in loadCustomReportXML", ex); + throw new ReportSQLException(ex.getMessage(), ex.getCause()); + } catch (IOException ex) { + logger.error(EELFLoggerDelegate.errorLogger, "IOException occured in loadCustomReportXML", ex); + throw new RaptorException(ex.getMessage(), ex.getCause()); + } + return sb.toString(); + } // loadCustomReportXML + + private static void dbUpdateReportXML(Connection connection, String reportID, + String reportXML) throws RaptorException { + + String sql = ""; + if (!Globals.isMySQL()) + sql = Globals.getDBUpdateReportXml(); + else + sql = Globals.getDBUpdateReportXmlMySqlSelect(); try (PreparedStatement stmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)) { @@ -183,14 +180,11 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { try (ResultSet rs = stmt.executeQuery()) { // Writer out = null; /* - * if(Globals.isWeblogicServer()) { java.sql.Clob clob = null; if (rs.next()) clob = - * rs.getClob(1); else throw new RaptorException("Report " + reportID + - * " not found in the database"); + * if(Globals.isWeblogicServer()) { java.sql.Clob clob = null; if (rs.next()) clob = rs.getClob(1); + * else throw new RaptorException("Report " + reportID + NOT_FOUND_IN_DB); * - * if (clob.length() > reportXML.length()) clob.truncate(0); - * //clob.trim(reportXML.length()); out = - * ((weblogic.jdbc.vendor.oracle.OracleThinClob)clob).getCharacterOutputStream(); } - * else + * if (clob.length() > reportXML.length()) clob.truncate(0); //clob.trim(reportXML.length()); out = + * ((weblogic.jdbc.vendor.oracle.OracleThinClob)clob).getCharacterOutputStream(); } else */ if (Globals.isPostgreSQL()) { if (rs.next()) { @@ -200,7 +194,7 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { // sb.append(rs.getString(1)); } else { throw new RaptorException( - "Report " + reportID + " not found in the database"); + "Report " + reportID + NOT_FOUND_IN_DB); } } else if (Globals.isMySQL()) { if (rs.next()) { @@ -215,14 +209,13 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { } } else { throw new RaptorException( - "Report " + reportID + " not found in the database"); + "Report " + reportID + NOT_FOUND_IN_DB); } } else { /* - * oracle.sql.CLOB clob = null; if (rs.next()) clob = (oracle.sql.CLOB) - * rs.getObject(2); else throw new RaptorException("Report " + reportID + - * " not found in the database"); + * oracle.sql.CLOB clob = null; if (rs.next()) clob = (oracle.sql.CLOB) rs.getObject(2); else throw + * new RaptorException("Report " + reportID + NOT_FOUND_IN_DB); * * if (clob.length() > reportXML.length()) clob.trim(reportXML.length()); out = * clob.getCharacterOutputStream(); @@ -230,76 +223,67 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { throw new RaptorException("only maria db support for this "); } /* - * if(!(Globals.isPostgreSQL() || Globals.isMySQL())) { out.write(reportXML); - * out.flush(); out.close(); } + * if(!(Globals.isPostgreSQL() || Globals.isMySQL())) { out.write(reportXML); out.flush(); + * out.close(); } */ } } catch (SQLException ex) { + logger.error(EELFLoggerDelegate.errorLogger, "SQLException occured in dbUpdateReportXML", ex); throw new ReportSQLException(ex.getMessage(), ex.getCause()); } catch (IOException ex) { + logger.error(EELFLoggerDelegate.errorLogger, "IOException occured in dbUpdateReportXML", ex); throw new RaptorException(ex.getMessage(), ex.getCause()); } } // dbUpdateReportXML - public static void updateCustomReportRec(Connection connection, ReportWrapper rw, - String reportXML) throws RaptorException { - /* DbUtils.executeUpdate(connection,"UPDATE cr_report SET title='" - + Utils.oracleSafe(rw.getReportName()) + "', descr='" - + Utils.oracleSafe(rw.getReportDescr()) + "', public_yn='" - + (rw.isPublic() ? "Y" : "N") + "', menu_id='" + rw.getMenuID() - + "', menu_approved_yn='" + (rw.isMenuApproved() ? "Y" : "N") + "', owner_id=" - + rw.getOwnerID() + ", maint_id=" + rw.getUpdateID() - + ", maint_date=TO_DATE('" + rw.getUpdateDate() + "', '" - + Globals.getOracleTimeFormat() + "'), dashboard_type_yn='"+ (rw.isDashboardType()?"Y":"N")+"', dashboard_yn= '" - + (rw.getReportType().equals(AppConstants.RT_DASHBOARD)?"Y":"N") + "' WHERE rep_id = " + rw.getReportID());*/ - - String sql = Globals.getUpdateCustomReportRec(); - - sql = sql.replace("[Utils.oracleSafe(rw.getReportName())]", Utils.oracleSafe(rw.getReportName())); - sql = sql.replace("[Utils.oracleSafe(rw.getReportDescr())]", Utils.oracleSafe(rw.getReportDescr())); - sql = sql.replace("[(rw.isPublic()]",(rw.isPublic() ? "Y" : "N")); - sql = sql.replace("[rw.getMenuID()]", rw.getMenuID()); - sql = sql.replace("[(rw.isMenuApproved()]", (rw.isMenuApproved() ? "Y" : "N")); - sql = sql.replace("[rw.getOwnerID()]",rw.getOwnerID()); - sql = sql.replace("[rw.getUpdateID()]",rw.getUpdateID()); - sql = sql.replace("[rw.getUpdateDate()]",rw.getUpdateDate()); - sql = sql.replace("[Globals.getTimeFormat()]", Globals.getTimeFormat()); - sql = sql.replace("[(rw.isDashboardType()]", (rw.isDashboardType()?"Y":"N")); - sql = sql.replace("[(rw.getReportType().equals(AppConstants.RT_DASHBOARD)]", (rw.getReportType().equals(AppConstants.RT_DASHBOARD)?"Y":"N")); - sql = sql.replace("[rw.getReportID()]", rw.getReportID()); - - DbUtils.executeUpdate(connection, sql); - - dbUpdateReportXML(connection, rw.getReportID(), reportXML); - } // updateCustomReportRec - - public static boolean isDashboardType ( String reportID ) throws RaptorException { - return false; -/* String sql = "select dashboard_type_yn from cr_report where rep_id = ?"; - Connection connection = DbUtils.getConnection(); - PreparedStatement stmt = null; - ResultSet rs = null; - boolean dashboardType= false; - try { - stmt = connection.prepareStatement(sql); - stmt.setString(1, reportID); - rs = stmt.executeQuery(); - if(rs.next()) { - dashboardType = nvls(rs.getString(1),"N").trim().toUpperCase().startsWith("Y"); - } - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } finally { - try { - rs.close(); - stmt.close(); - DbUtils.clearConnection(connection); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } - } - return dashboardType;*/ - } + public static void updateCustomReportRec(Connection connection, ReportWrapper rw, + String reportXML) throws RaptorException { + /* + * DbUtils.executeUpdate(connection,"UPDATE cr_report SET title='" + + * Utils.oracleSafe(rw.getReportName()) + "', descr='" + Utils.oracleSafe(rw.getReportDescr()) + + * "', public_yn='" + (rw.isPublic() ? "Y" : "N") + "', menu_id='" + rw.getMenuID() + + * "', menu_approved_yn='" + (rw.isMenuApproved() ? "Y" : "N") + "', owner_id=" + rw.getOwnerID() + + * ", maint_id=" + rw.getUpdateID() + ", maint_date=TO_DATE('" + rw.getUpdateDate() + "', '" + + * Globals.getOracleTimeFormat() + "'), dashboard_type_yn='"+ + * (rw.isDashboardType()?"Y":"N")+"', dashboard_yn= '" + + * (rw.getReportType().equals(AppConstants.RT_DASHBOARD)?"Y":"N") + "' WHERE rep_id = " + + * rw.getReportID()); + */ + + String sql = Globals.getUpdateCustomReportRec(); + + sql = sql.replace("[Utils.oracleSafe(rw.getReportName())]", Utils.oracleSafe(rw.getReportName())); + sql = sql.replace("[Utils.oracleSafe(rw.getReportDescr())]", Utils.oracleSafe(rw.getReportDescr())); + sql = sql.replace("[(rw.isPublic()]", (rw.isPublic() ? "Y" : "N")); + sql = sql.replace("[rw.getMenuID()]", rw.getMenuID()); + sql = sql.replace("[(rw.isMenuApproved()]", (rw.isMenuApproved() ? "Y" : "N")); + sql = sql.replace("[rw.getOwnerID()]", rw.getOwnerID()); + sql = sql.replace("[rw.getUpdateID()]", rw.getUpdateID()); + sql = sql.replace("[rw.getUpdateDate()]", rw.getUpdateDate()); + sql = sql.replace("[Globals.getTimeFormat()]", Globals.getTimeFormat()); + sql = sql.replace("[(rw.isDashboardType()]", (rw.isDashboardType() ? "Y" : "N")); + sql = sql.replace("[(rw.getReportType().equals(AppConstants.RT_DASHBOARD)]", + (rw.getReportType().equals(AppConstants.RT_DASHBOARD) ? "Y" : "N")); + sql = sql.replace("[rw.getReportID()]", rw.getReportID()); + + DbUtils.executeUpdate(connection, sql); + + dbUpdateReportXML(connection, rw.getReportID(), reportXML); + } // updateCustomReportRec + + public static boolean isDashboardType(String reportID) throws RaptorException { + return false; + /* + * String sql = "select dashboard_type_yn from cr_report where rep_id = ?"; Connection connection = + * DbUtils.getConnection(); PreparedStatement stmt = null; ResultSet rs = null; boolean + * dashboardType= false; try { stmt = connection.prepareStatement(sql); stmt.setString(1, reportID); + * rs = stmt.executeQuery(); if(rs.next()) { dashboardType = + * nvls(rs.getString(1),"N").trim().toUpperCase().startsWith("Y"); } } catch (SQLException ex) { + * throw new ReportSQLException (ex.getMessage(), ex.getCause()); } finally { try { rs.close(); + * stmt.close(); DbUtils.clearConnection(connection); } catch (SQLException ex) { throw new + * ReportSQLException (ex.getMessage(), ex.getCause()); } } return dashboardType; + */ + } public static boolean isReportsAlreadyScheduled(String reportID) throws RaptorException { // String sql = "select rep_id from cr_report_schedule where rep_id = ?"; @@ -316,6 +300,7 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { } } } catch (SQLException ex) { + logger.error(EELFLoggerDelegate.errorLogger, "SQLException occured in isReportsAlreadyScheduled", ex); throw new ReportSQLException(ex.getMessage(), ex.getCause()); } finally { DbUtils.clearConnection(connection); @@ -323,93 +308,71 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { return isScheduled; } - public static void createCustomReportRec(Connection connection, ReportWrapper rw, - String reportXML) throws RaptorException { - - /*DbUtils - .executeUpdate( - connection, - "INSERT INTO cr_report(rep_id, title, descr, public_yn, menu_id, menu_approved_yn, report_xml, owner_id, create_id, create_date, maint_id, maint_date, dashboard_type_yn, dashboard_yn, folder_id) VALUES(" - + rw.getReportID() - + ", '" - + Utils.oracleSafe(rw.getReportName()) - + "', '" - + Utils.oracleSafe(rw.getReportDescr()) - + "', '" - + (rw.isPublic() ? "Y" : "N") - + "', '" - + rw.getMenuID() - + "', '" - + (rw.isMenuApproved() ? "Y" : "N") - + "', '', " - + rw.getOwnerID() - + ", " - + rw.getCreateID() - + ", TO_DATE('" - + rw.getCreateDate() - + "', '" - + Globals.getOracleTimeFormat() - + "'), " - + rw.getUpdateID() - + ", TO_DATE('" - + rw.getUpdateDate() - + "', '" - + Globals.getOracleTimeFormat() - + "'), '" - + (rw.isDashboardType()?"Y":"N") - + "', '" - + (rw.getReportType().equals(AppConstants.RT_DASHBOARD)?"Y":"N") - + "', " - + rw.getFolderId() - + ")");*/ - String sql = Globals.getCreateCustomReportRec(); - - sql = sql.replace("[rw.getReportID()]", rw.getReportID()); - sql = sql.replace("[Utils.oracleSafe(rw.getReportName())]", Utils.oracleSafe(rw.getReportName())); - sql = sql.replace("[Utils.oracleSafe(rw.getReportDescr())]", Utils.oracleSafe(rw.getReportDescr())); - sql = sql.replace("[rw.isPublic()]", (rw.isPublic() ? "Y" : "N")); - sql = sql.replace("[rw.getMenuID()]", rw.getMenuID()); - sql = sql.replace("[rw.isMenuApproved()]", (rw.isMenuApproved() ? "Y" : "N")); - sql = sql.replace("[rw.getOwnerID()]", rw.getOwnerID()); - sql = sql.replace("[rw.getCreateID()]", rw.getCreateID()); - sql = sql.replace("[rw.getCreateDate()]", rw.getCreateDate()); - sql = sql.replace("[Globals.getTimeFormat()]", Globals.getTimeFormat()); - sql = sql.replace("[rw.getUpdateID()]", rw.getUpdateID()); - sql = sql.replace("[rw.getUpdateDate()]", rw.getUpdateDate()); - sql = sql.replace("[rw.isDashboardType()]", (rw.isDashboardType()?"Y":"N")); - sql = sql.replace("[rw.getReportType().equals(AppConstants.RT_DASHBOARD)]", (rw.getReportType().equals(AppConstants.RT_DASHBOARD)?"Y":"N")); - sql = sql.replace("[rw.getFolderId()]", rw.getFolderId()); - - - DbUtils.executeUpdate(connection,sql); - - dbUpdateReportXML(connection, rw.getReportID(), reportXML); - } // createCustomReportRec - - public static Vector getUserReportNames(HttpServletRequest request) { - return getUserReportNames(AppUtils.getUserID(request)); - } // getUserReportNames - - public static Vector getUserReportNames(String userID) { - Vector reportIdNames = new Vector(); - - try { - - String sql = Globals.getTheUserReportNames(); - sql = sql.replace("[userID]", userID); - DataSet ds = DbUtils.executeQuery(sql); - - //DataSet ds = DbUtils - // .executeQuery("SELECT cr.rep_id, cr.title FROM cr_report cr WHERE nvl(cr.owner_id, cr.create_id) = " - // + userID); - - for (int i = 0; i < ds.getRowCount(); i++) - reportIdNames.add(new IdNameValue(ds.getString(i, 0), ds.getString(i, 1))); - } catch (Exception e) { - } - - return reportIdNames; - } // getUserReportNames + public static void createCustomReportRec(Connection connection, ReportWrapper rw, + String reportXML) throws RaptorException { + + /* + * DbUtils .executeUpdate( connection, + * "INSERT INTO cr_report(rep_id, title, descr, public_yn, menu_id, menu_approved_yn, report_xml, owner_id, create_id, create_date, maint_id, maint_date, dashboard_type_yn, dashboard_yn, folder_id) VALUES(" + * + rw.getReportID() + ", '" + Utils.oracleSafe(rw.getReportName()) + "', '" + + * Utils.oracleSafe(rw.getReportDescr()) + "', '" + (rw.isPublic() ? "Y" : "N") + "', '" + + * rw.getMenuID() + "', '" + (rw.isMenuApproved() ? "Y" : "N") + + * "', '', " + rw.getOwnerID() + ", " + + * rw.getCreateID() + ", TO_DATE('" + rw.getCreateDate() + "', '" + Globals.getOracleTimeFormat() + + * "'), " + rw.getUpdateID() + ", TO_DATE('" + rw.getUpdateDate() + "', '" + + * Globals.getOracleTimeFormat() + "'), '" + (rw.isDashboardType()?"Y":"N") + "', '" + + * (rw.getReportType().equals(AppConstants.RT_DASHBOARD)?"Y":"N") + "', " + rw.getFolderId() + ")"); + */ + String sql = Globals.getCreateCustomReportRec(); + + sql = sql.replace("[rw.getReportID()]", rw.getReportID()); + sql = sql.replace("[Utils.oracleSafe(rw.getReportName())]", Utils.oracleSafe(rw.getReportName())); + sql = sql.replace("[Utils.oracleSafe(rw.getReportDescr())]", Utils.oracleSafe(rw.getReportDescr())); + sql = sql.replace("[rw.isPublic()]", (rw.isPublic() ? "Y" : "N")); + sql = sql.replace("[rw.getMenuID()]", rw.getMenuID()); + sql = sql.replace("[rw.isMenuApproved()]", (rw.isMenuApproved() ? "Y" : "N")); + sql = sql.replace("[rw.getOwnerID()]", rw.getOwnerID()); + sql = sql.replace("[rw.getCreateID()]", rw.getCreateID()); + sql = sql.replace("[rw.getCreateDate()]", rw.getCreateDate()); + sql = sql.replace("[Globals.getTimeFormat()]", Globals.getTimeFormat()); + sql = sql.replace("[rw.getUpdateID()]", rw.getUpdateID()); + sql = sql.replace("[rw.getUpdateDate()]", rw.getUpdateDate()); + sql = sql.replace("[rw.isDashboardType()]", (rw.isDashboardType() ? "Y" : "N")); + sql = sql.replace("[rw.getReportType().equals(AppConstants.RT_DASHBOARD)]", + (rw.getReportType().equals(AppConstants.RT_DASHBOARD) ? "Y" : "N")); + sql = sql.replace("[rw.getFolderId()]", rw.getFolderId()); + + DbUtils.executeUpdate(connection, sql); + + dbUpdateReportXML(connection, rw.getReportID(), reportXML); + } // createCustomReportRec + + public static Vector getUserReportNames(HttpServletRequest request) { + return getUserReportNames(AppUtils.getUserID(request)); + } // getUserReportNames + + public static Vector getUserReportNames(String userID) { + Vector reportIdNames = new Vector(); + + try { + + String sql = Globals.getTheUserReportNames(); + sql = sql.replace("[userID]", userID); + DataSet ds = DbUtils.executeQuery(sql); + + // DataSet ds = DbUtils + // .executeQuery("SELECT cr.rep_id, cr.title FROM cr_report cr WHERE nvl(cr.owner_id, cr.create_id) + // = " + // + userID); + + for (int i = 0; i < ds.getRowCount(); i++) + reportIdNames.add(new IdNameValue(ds.getString(i, 0), ds.getString(i, 1))); + } catch (Exception e) { + logger.error(EELFLoggerDelegate.errorLogger, "Exception occured in getUserReportNames", e); + } + + return reportIdNames; + } // getUserReportNames public static String getReportOwnerID(String reportID) throws RaptorException { @@ -429,6 +392,7 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { } } } catch (SQLException ex) { + logger.error(EELFLoggerDelegate.errorLogger, "SQLException occured in getReportOwnerID", ex); throw new ReportSQLException(ex.getMessage(), ex.getCause()); } finally { DbUtils.clearConnection(connection); @@ -437,615 +401,621 @@ public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject { return reportOwnerID; } // getReportOwnerID - public static void deleteReportRecord(String reportID) throws RaptorException { - Connection con = DbUtils.startTransaction(); - - /*try { - DbUtils.executeUpdate(con, "DELETE cr_report_log WHERE rep_id = " + reportID); - DbUtils.executeUpdate(con, "DELETE cr_report_schedule_users WHERE rep_id = " - + reportID); - DbUtils.executeUpdate(con, "DELETE cr_report_schedule WHERE rep_id = " + reportID); - DbUtils.executeUpdate(con, "DELETE cr_report_access WHERE rep_id = " + reportID); - DbUtils.executeUpdate(con, "DELETE cr_report_email_sent_log WHERE rep_id = " + reportID); - DbUtils.executeUpdate(con, "DELETE cr_favorite_reports WHERE rep_id = " + reportID); - DbUtils.executeUpdate(con, "DELETE cr_report WHERE rep_id = " + reportID); - DbUtils.commitTransaction(con); - } */ - - try{ - String sql1= Globals.getDeleteReportRecordLog(); - sql1 = sql1.replace("[reportID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),reportID)); - String sql2= Globals.getDeleteReportRecordUsers(); - sql2 = sql2.replace("[reportID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),reportID)); - String sql3= Globals.getDeleteReportRecordSchedule(); - sql3 = sql3.replace("[reportID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),reportID)); - String sql4= Globals.getDeleteReportRecordAccess(); - sql4 = sql4.replace("[reportID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),reportID)); - String sql5= Globals.getDeleteReportRecordEmail(); - sql5 = sql5.replace("[reportID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),reportID)); - String sql6= Globals.getDeleteReportRecordFavorite(); - sql6 = sql6.replace("[reportID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),reportID)); - String sql7= Globals.getDeleteReportRecordReport(); - sql7 = sql7.replace("[reportID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),reportID)); - - DbUtils.executeUpdate(con, sql1); - DbUtils.executeUpdate(con, sql2); - DbUtils.executeUpdate(con, sql3); - DbUtils.executeUpdate(con, sql4); - DbUtils.executeUpdate(con, sql5); - DbUtils.executeUpdate(con, sql6); - DbUtils.executeUpdate(con, sql7); - DbUtils.commitTransaction(con); - - } - - - catch (Exception e) { - DbUtils.rollbackTransaction(con); - } finally { - DbUtils.clearConnection(con); + public static void deleteReportRecord(String reportID) throws RaptorException { + Connection con = DbUtils.startTransaction(); + + /* + * try { DbUtils.executeUpdate(con, "DELETE cr_report_log WHERE rep_id = " + reportID); + * DbUtils.executeUpdate(con, "DELETE cr_report_schedule_users WHERE rep_id = " + reportID); + * DbUtils.executeUpdate(con, "DELETE cr_report_schedule WHERE rep_id = " + reportID); + * DbUtils.executeUpdate(con, "DELETE cr_report_access WHERE rep_id = " + reportID); + * DbUtils.executeUpdate(con, "DELETE cr_report_email_sent_log WHERE rep_id = " + reportID); + * DbUtils.executeUpdate(con, "DELETE cr_favorite_reports WHERE rep_id = " + reportID); + * DbUtils.executeUpdate(con, "DELETE cr_report WHERE rep_id = " + reportID); + * DbUtils.commitTransaction(con); } + */ + + try { + String sql1 = Globals.getDeleteReportRecordLog(); + sql1 = sql1.replace("[reportID]", ESAPI.encoder().encodeForSQL(SecurityCodecUtil.getCodec(), reportID)); + String sql2 = Globals.getDeleteReportRecordUsers(); + sql2 = sql2.replace("[reportID]", ESAPI.encoder().encodeForSQL(SecurityCodecUtil.getCodec(), reportID)); + String sql3 = Globals.getDeleteReportRecordSchedule(); + sql3 = sql3.replace("[reportID]", ESAPI.encoder().encodeForSQL(SecurityCodecUtil.getCodec(), reportID)); + String sql4 = Globals.getDeleteReportRecordAccess(); + sql4 = sql4.replace("[reportID]", ESAPI.encoder().encodeForSQL(SecurityCodecUtil.getCodec(), reportID)); + String sql5 = Globals.getDeleteReportRecordEmail(); + sql5 = sql5.replace("[reportID]", ESAPI.encoder().encodeForSQL(SecurityCodecUtil.getCodec(), reportID)); + String sql6 = Globals.getDeleteReportRecordFavorite(); + sql6 = sql6.replace("[reportID]", ESAPI.encoder().encodeForSQL(SecurityCodecUtil.getCodec(), reportID)); + String sql7 = Globals.getDeleteReportRecordReport(); + sql7 = sql7.replace("[reportID]", ESAPI.encoder().encodeForSQL(SecurityCodecUtil.getCodec(), reportID)); + + DbUtils.executeUpdate(con, sql1); + DbUtils.executeUpdate(con, sql2); + DbUtils.executeUpdate(con, sql3); + DbUtils.executeUpdate(con, sql4); + DbUtils.executeUpdate(con, sql5); + DbUtils.executeUpdate(con, sql6); + DbUtils.executeUpdate(con, sql7); + DbUtils.commitTransaction(con); + } - } // deleteReportRecord - - public static ArrayList loadQuickLinks(HttpServletRequest request, String menuId, boolean b) throws RaptorException { - String userID = AppUtils.getUserID(request); - StringBuffer roleList = new StringBuffer(); - roleList.append("-1"); - for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) - roleList.append("," + ((String) iter.next())); - - // DataSet ds = DbUtils.executeQuery("SELECT cr.rep_id, cr.title FROM - // cr_report cr WHERE cr.public_yn = 'Y' AND cr.menu_id = - // '"+nvls(menuId)+"' AND cr.menu_approved_yn = 'Y' ORDER BY cr.title"); - // Copied from SearchHandler and simplified - /*String query = "SELECT cr.rep_id, " - + "cr.title, " - + "cr.descr " - + "FROM cr_report cr, " - + "(SELECT rep_id, " - + "MIN(read_only_yn) read_only_yn " - + "FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = " - + userID - + ") " - + "UNION ALL " - + "(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN (" - + roleList.toString() + "))" + ") report_access " - + "GROUP BY rep_id) ra " + "WHERE INSTR('|'||cr.menu_id||'|', '|'||'" - + nvls(menuId) + "'||'|') > 0 AND " + "cr.menu_approved_yn = 'Y' AND " - + "cr.rep_id = ra.rep_id (+) AND " - + "(nvl(cr.owner_id, cr.create_id) = " + userID - + " OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL) " - + "ORDER BY cr.title";*/ - - String query = Globals.getLoadQuickLinks(); - query = query.replace("[userID]", userID); - query = query.replace("[roleList.toString()]", roleList.toString()); - query = query.replace("[nvls(menuId)]", nvls(menuId)); - - DataSet ds = DbUtils - .executeQuery(query); - - ArrayList quickLinks = new ArrayList(ds.getRowCount()); - StringBuffer link = new StringBuffer(""); - for (int i = 0; i < ds.getRowCount(); i++) { - link = new StringBuffer(""); - link.append("" +ds.getString(i, 1) + "" + (Globals.getShowDescrAtRuntime() ? " - " + ds.getString(i, 2) : "") ); - quickLinks.add(link.toString()); + + catch (Exception e) { + logger.error(EELFLoggerDelegate.errorLogger, "Exception occured in deleteReportRecord", e); + DbUtils.rollbackTransaction(con); + } finally { + DbUtils.clearConnection(con); } + } // deleteReportRecord + + public static ArrayList loadQuickLinks(HttpServletRequest request, String menuId, boolean b) + throws RaptorException { + String userID = AppUtils.getUserID(request); + StringBuffer roleList = new StringBuffer(); + roleList.append("-1"); + for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) + roleList.append("," + ((String) iter.next())); + + // DataSet ds = DbUtils.executeQuery("SELECT cr.rep_id, cr.title FROM + // cr_report cr WHERE cr.public_yn = 'Y' AND cr.menu_id = + // '"+nvls(menuId)+"' AND cr.menu_approved_yn = 'Y' ORDER BY cr.title"); + // Copied from SearchHandler and simplified + /* + * String query = "SELECT cr.rep_id, " + "cr.title, " + "cr.descr " + "FROM cr_report cr, " + + * "(SELECT rep_id, " + "MIN(read_only_yn) read_only_yn " + + * "FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = " + userID + * + ") " + "UNION ALL " + + * "(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN (" + + * roleList.toString() + "))" + ") report_access " + "GROUP BY rep_id) ra " + + * "WHERE INSTR('|'||cr.menu_id||'|', '|'||'" + nvls(menuId) + "'||'|') > 0 AND " + + * "cr.menu_approved_yn = 'Y' AND " + "cr.rep_id = ra.rep_id (+) AND " + + * "(nvl(cr.owner_id, cr.create_id) = " + userID + + * " OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL) " + "ORDER BY cr.title"; + */ + + String query = Globals.getLoadQuickLinks(); + query = query.replace("[userID]", userID); + query = query.replace("[roleList.toString()]", roleList.toString()); + query = query.replace("[nvls(menuId)]", nvls(menuId)); + + DataSet ds = DbUtils + .executeQuery(query); - return quickLinks; - } // loadQuickLinks - - public static ArrayList getQuickLinksJSON(HttpServletRequest request, String menuId, boolean b) throws RaptorException { - String userID = AppUtils.getUserID(request); - StringBuffer roleList = new StringBuffer(); - roleList.append("-1"); - for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) - roleList.append("," + ((String) iter.next())); - - String query = Globals.getLoadQuickLinks(); - query = query.replace("[userID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),userID)); - query = query.replace("[roleList.toString()]", roleList.toString()); - query = query.replace("[nvls(menuId)]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),nvls(menuId))); - - DataSet ds = DbUtils - .executeQuery(query); - - ArrayList quickLinksArray = new ArrayList(ds.getRowCount()); + ArrayList quickLinks = new ArrayList(ds.getRowCount()); + StringBuffer link = new StringBuffer(""); for (int i = 0; i < ds.getRowCount(); i++) { - QuickLink quickLink = new QuickLink(); - StringBuffer link = new StringBuffer(""); - link.append(AppUtils.getReportExecuteActionURLNG() +"c_master="+ ds.getString(i, 0)); - if(b) link.append("&PAGE_ID="+menuId+"&refresh=Y"); - quickLink.setReportURL(link.toString()); - quickLink.setReportName(ds.getString(i, 1)); - quickLink.setShowDescr(Globals.getShowDescrAtRuntime()); - quickLink.setReportDescr(ds.getString(i, 2)); - quickLinksArray.add(quickLink); + link = new StringBuffer(""); + link.append("" + ds.getString(i, 1) + "" + + (Globals.getShowDescrAtRuntime() ? " - " + ds.getString(i, 2) : "")); + quickLinks.add(link.toString()); } - return quickLinksArray; - } // loadQuickLinks - - //this will retrieve all the reports within the specified folder. - public static ReportSearchResult loadFolderReports(HttpServletRequest request, String menuId, boolean b, String folderId, boolean isUserReport, boolean isPublicReport) throws RaptorException { - String HTML_FORM = "forma"; - String userID = AppUtils.getUserID(request); - StringBuffer roleList = new StringBuffer(); - roleList.append("-1"); - String rep_title_sql = "''"; - String PRIVATE_ICON = "Private "; - - for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) - roleList.append("," + ((String) iter.next())); - - /*String sql= "SELECT cr.rep_id, " + - "cr.rep_id report_id, " + - rep_title_sql+ - "||DECODE(cr.public_yn, 'Y', '', '" + - PRIVATE_ICON + - "')||cr.title||'' title, " + - "cr.descr, " + - "au.first_name||' '||au.last_name owner_name, " + - "TO_CHAR(cr.create_date, 'MM/DD/YYYY') create_date, " + - "DECODE(NVL(cr.owner_id, cr.create_id), " + - userID + - ", 'N', NVL(ra.read_only_yn, 'Y')) read_only_yn, " + - "DECODE(NVL(cr.owner_id, cr.create_id), " + - userID + - ", 'Y', 'N') user_is_owner_yn " + - " FROM cr_report cr, " + - "app_user au, " + - "(SELECT rep_id, MIN(read_only_yn) read_only_yn " + - "FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = " + - userID + - ") " + - "UNION ALL " + - "(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN " + - "(-1,1000,1))" + ") report_access GROUP BY rep_id) ra " + - "WHERE TO_CHAR(cr.rep_id) = nvl('', TO_CHAR(cr.rep_id)) AND UPPER(cr.title) LIKE UPPER('%%') " + - "AND nvl(cr.owner_id, cr.create_id) = au.user_id AND cr.rep_id = ra.rep_id (+) " + - " AND cr.folder_id= '" + folderId + "'" ;*/ - - /*String sql = "" + - "SELECT cr.rep_id, " - + "cr.rep_id report_id, " - + rep_title_sql + "||DECODE(cr.public_yn, 'Y', '', '" + PRIVATE_ICON + "')||cr.title||'' title, " - + "cr.descr, " - + "au.first_name||' '||au.last_name owner_name, " - + "TO_CHAR(cr.create_date, 'MM/DD/YYYY') create_date, " - + "DECODE(NVL(cr.owner_id, cr.create_id), " + userID - + ", 'N', NVL(ra.read_only_yn, 'Y')) read_only_yn, " - + "DECODE(NVL(cr.owner_id, cr.create_id), " + userID - + ", 'Y', 'N') user_is_owner_yn " - + "FROM cr_report cr, " - + "app_user au, " - + "(SELECT rep_id, " - + "MIN(read_only_yn) read_only_yn " - + "FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = " - + userID - + ") " - + "UNION ALL " - + "(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN (" - + roleList.toString() + "))" + ") report_access " + "GROUP BY rep_id) ra " - + "WHERE " + "nvl(cr.owner_id, cr.create_id) = au.user_id " - + "AND cr.rep_id = ra.rep_id (+) AND cr.folder_id= '" + folderId + "'";*/ - - String sql = Globals.getLoadFolderReports(); - sql = sql.replace("[userID]", userID); - sql = sql.replace("[PRIVATE_ICON]", PRIVATE_ICON); - sql = sql.replace("[rep_title_sql]", rep_title_sql); - sql = sql.replace("[roleList.toString()]", roleList.toString()); - sql = sql.replace("[folderId]", folderId); - - - // String user_sql = " AND nvl(cr.owner_id, cr.create_id) = " + userID; - // String public_sql = " AND (nvl(cr.owner_id, cr.create_id) = " + userID - // + " OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL)"; - - String user_sql = Globals.getLoadFolderReportsUser(); - user_sql = user_sql.replace("[userID]", userID); - String public_sql = Globals.getLoadFolderReportsPublicSql(); - public_sql = public_sql.replace("[userID]", userID); - - if (isUserReport) - // My reports - user is owner - sql += user_sql; - else if (isPublicReport) - // Public reports - user has read or write access to the report - // (user is owner or report is public or user has explicit user or - // role access) - if (!AppUtils.isSuperUser(request)) - sql += public_sql; - else if (!AppUtils.isSuperUser(request)) - // All reports - // If user is super user - gets unrestricted access to all reports - // (read_only gets overriden later) - // else - not super user - doesn't get access to private reports of - // other users (= Public reports); Admin users get edit right - // override later - sql += public_sql; - logger.debug(EELFLoggerDelegate.debugLogger, ("query is for folder list is : " + sql)); - - DataSet ds = DbUtils.executeQuery(sql); - - /*Vector quickLinks = new Vector(ds.getRowCount()); - StringBuffer link = new StringBuffer(""); + return quickLinks; + } // loadQuickLinks + + public static ArrayList getQuickLinksJSON(HttpServletRequest request, String menuId, boolean b) + throws RaptorException { + String userID = AppUtils.getUserID(request); + StringBuffer roleList = new StringBuffer(); + roleList.append("-1"); + for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) + roleList.append("," + ((String) iter.next())); + + String query = Globals.getLoadQuickLinks(); + query = query.replace("[userID]", ESAPI.encoder().encodeForSQL(SecurityCodecUtil.getCodec(), userID)); + query = query.replace("[roleList.toString()]", roleList.toString()); + query = query.replace("[nvls(menuId)]", + ESAPI.encoder().encodeForSQL(SecurityCodecUtil.getCodec(), nvls(menuId))); + + DataSet ds = DbUtils + .executeQuery(query); + + ArrayList quickLinksArray = new ArrayList(ds.getRowCount()); for (int i = 0; i < ds.getRowCount(); i++) { - link = new StringBuffer(""); - link.append("" +ds.getString(i, 2) + "" + (Globals.getShowDescrAtRuntime() ? " - " + ds.getString(i, 2) : "") ); - quickLinks.add(link.toString()); + QuickLink quickLink = new QuickLink(); + StringBuffer link = new StringBuffer(""); + link.append(AppUtils.getReportExecuteActionURLNG() + "c_master=" + ds.getString(i, 0)); + if (b) + link.append("&PAGE_ID=" + menuId + "&refresh=Y"); + quickLink.setReportURL(link.toString()); + quickLink.setReportName(ds.getString(i, 1)); + quickLink.setShowDescr(Globals.getShowDescrAtRuntime()); + quickLink.setReportDescr(ds.getString(i, 2)); + quickLinksArray.add(quickLink); } - return quickLinks;*/ - ReportSearchResult rsr = new ReportSearchResult(-1, ds.getRowCount(), 6, 7); - rsr.parseData(ds, request); - //rsr.truncateToPage(pageNo); - - return rsr; - } // loadFolderReports + return quickLinksArray; + } // loadQuickLinks + + // this will retrieve all the reports within the specified folder. + public static ReportSearchResult loadFolderReports(HttpServletRequest request, String menuId, boolean b, + String folderId, boolean isUserReport, boolean isPublicReport) throws RaptorException { + String HTML_FORM = "forma"; + String userID = AppUtils.getUserID(request); + StringBuffer roleList = new StringBuffer(); + roleList.append("-1"); + String rep_title_sql = "''"; + String PRIVATE_ICON = "Private "; + + for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) + roleList.append("," + ((String) iter.next())); + + /* + * String sql= "SELECT cr.rep_id, " + "cr.rep_id report_id, " + rep_title_sql+ + * "||DECODE(cr.public_yn, 'Y', '', '" + PRIVATE_ICON + "')||cr.title||'' title, " + + * "cr.descr, " + "au.first_name||' '||au.last_name owner_name, " + + * "TO_CHAR(cr.create_date, 'MM/DD/YYYY') create_date, " + "DECODE(NVL(cr.owner_id, cr.create_id), " + * + userID + ", 'N', NVL(ra.read_only_yn, 'Y')) read_only_yn, " + + * "DECODE(NVL(cr.owner_id, cr.create_id), " + userID + ", 'Y', 'N') user_is_owner_yn " + + * " FROM cr_report cr, " + "app_user au, " + "(SELECT rep_id, MIN(read_only_yn) read_only_yn " + + * "FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = " + userID + * + ") " + "UNION ALL " + + * "(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN " + + * "(-1,1000,1))" + ") report_access GROUP BY rep_id) ra " + + * "WHERE TO_CHAR(cr.rep_id) = nvl('', TO_CHAR(cr.rep_id)) AND UPPER(cr.title) LIKE UPPER('%%') " + + * "AND nvl(cr.owner_id, cr.create_id) = au.user_id AND cr.rep_id = ra.rep_id (+) " + + * " AND cr.folder_id= '" + folderId + "'" ; + */ + + /* + * String sql = "" + "SELECT cr.rep_id, " + "cr.rep_id report_id, " + rep_title_sql + + * "||DECODE(cr.public_yn, 'Y', '', '" + PRIVATE_ICON + "')||cr.title||'' title, " + + * "cr.descr, " + "au.first_name||' '||au.last_name owner_name, " + + * "TO_CHAR(cr.create_date, 'MM/DD/YYYY') create_date, " + "DECODE(NVL(cr.owner_id, cr.create_id), " + * + userID + ", 'N', NVL(ra.read_only_yn, 'Y')) read_only_yn, " + + * "DECODE(NVL(cr.owner_id, cr.create_id), " + userID + ", 'Y', 'N') user_is_owner_yn " + + * "FROM cr_report cr, " + "app_user au, " + "(SELECT rep_id, " + "MIN(read_only_yn) read_only_yn " + * + "FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = " + + * userID + ") " + "UNION ALL " + + * "(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN (" + + * roleList.toString() + "))" + ") report_access " + "GROUP BY rep_id) ra " + "WHERE " + + * "nvl(cr.owner_id, cr.create_id) = au.user_id " + + * "AND cr.rep_id = ra.rep_id (+) AND cr.folder_id= '" + folderId + "'"; + */ + + String sql = Globals.getLoadFolderReports(); + sql = sql.replace("[userID]", userID); + sql = sql.replace("[PRIVATE_ICON]", PRIVATE_ICON); + sql = sql.replace("[rep_title_sql]", rep_title_sql); + sql = sql.replace("[roleList.toString()]", roleList.toString()); + sql = sql.replace("[folderId]", folderId); + + // String user_sql = " AND nvl(cr.owner_id, cr.create_id) = " + userID; + // String public_sql = " AND (nvl(cr.owner_id, cr.create_id) = " + userID + // + " OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL)"; + + String user_sql = Globals.getLoadFolderReportsUser(); + user_sql = user_sql.replace("[userID]", userID); + String public_sql = Globals.getLoadFolderReportsPublicSql(); + public_sql = public_sql.replace("[userID]", userID); + + if (isUserReport) + // My reports - user is owner + sql += user_sql; + else if (isPublicReport) + // Public reports - user has read or write access to the report + // (user is owner or report is public or user has explicit user or + // role access) + if (!AppUtils.isSuperUser(request)) + sql += public_sql; + else if (!AppUtils.isSuperUser(request)) + // All reports + // If user is super user - gets unrestricted access to all reports + // (read_only gets overriden later) + // else - not super user - doesn't get access to private reports of + // other users (= Public reports); Admin users get edit right + // override later + sql += public_sql; + logger.debug(EELFLoggerDelegate.debugLogger, ("query is for folder list is : " + sql)); + + DataSet ds = DbUtils.executeQuery(sql); + + /* + * Vector quickLinks = new Vector(ds.getRowCount()); StringBuffer link = new StringBuffer(""); for + * (int i = 0; i < ds.getRowCount(); i++) { link = new StringBuffer(""); link.append("" +ds.getString(i, 2) + "" + + * (Globals.getShowDescrAtRuntime() ? " - " + ds.getString(i, 2) : "") ); + * quickLinks.add(link.toString()); } + * + * return quickLinks; + */ + ReportSearchResult rsr = new ReportSearchResult(-1, ds.getRowCount(), 6, 7); + rsr.parseData(ds, request); + // rsr.truncateToPage(pageNo); + + return rsr; + } // loadFolderReports public static ArrayList loadQuickDownloadLinks(String userID, HttpServletRequest request) throws RaptorException { - /*String query = " SELECT a.file_name, b.title,to_char(a.dwnld_start_time, 'Dy DD-Mon-YYYY HH24:MI:SS') as time, "+ - " a.dwnld_start_time " + - " FROM cr_report_dwnld_log a, cr_report b where a.user_id = "+userID +" and "+ - " a.rep_id = b.rep_id " + - " and (a.dwnld_start_time) >= to_date(to_char(sysdate-24/24, 'mm/dd/yyyy'), 'mm/dd/yyyy') " + - " and a.record_ready_time is not null " + - " order by a.dwnld_start_time desc"; */ - - String query = Globals.getLoadQuickDownloadLinks(); - query = query.replace("[userID]", userID); - - + /* + * String query = + * " SELECT a.file_name, b.title,to_char(a.dwnld_start_time, 'Dy DD-Mon-YYYY HH24:MI:SS') as time, " + * + " a.dwnld_start_time " + " FROM cr_report_dwnld_log a, cr_report b where a.user_id = "+userID + * +" and "+ " a.rep_id = b.rep_id " + + * " and (a.dwnld_start_time) >= to_date(to_char(sysdate-24/24, 'mm/dd/yyyy'), 'mm/dd/yyyy') " + + * " and a.record_ready_time is not null " + " order by a.dwnld_start_time desc"; + */ + + String query = Globals.getLoadQuickDownloadLinks(); + query = query.replace("[userID]", userID); + DataSet ds = DbUtils .executeQuery(query); ArrayList quickDownloadLinks = new ArrayList(ds.getRowCount()); logger.debug(EELFLoggerDelegate.debugLogger, ("ROW SIZE " + ds.getRowCount())); for (int i = 0; i < ds.getRowCount(); i++) { - quickDownloadLinks.add("" + ds.getString(i, 1)+ "" + " "+ ds.getString(i, 2)); + quickDownloadLinks.add("" + ds.getString(i, 1) + "" + " " + ds.getString(i, 2)); } logger.debug(EELFLoggerDelegate.debugLogger, ("VECTOR SIZE " + quickDownloadLinks.size())); return quickDownloadLinks; } // loadQuickLinks - - public static HashMap loadReportsToSchedule (HttpServletRequest request) throws RaptorException { - String userID = AppUtils.getUserID(request); - StringBuffer roleList = new StringBuffer(); - roleList.append("-1"); - for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) - roleList.append("," + ((String) iter.next())); - /*StringBuffer query = new StringBuffer(""); - query.append("SELECT cr.rep_id, "); - query.append("Initcap(cr.title), "); - query.append("cr.descr "); - query.append("FROM cr_report cr, "); - query.append("(SELECT rep_id, "); - query.append("MIN(read_only_yn) read_only_yn "); - query.append("FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = "); - query.append(userID); - query.append(") "); - query.append("UNION ALL "); - query.append("(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ("); - query.append(roleList.toString() + "))" + ") report_access "); - query.append("GROUP BY rep_id) ra " + "WHERE "); - query.append("cr.rep_id = ra.rep_id (+) AND "); - query.append(" (cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL or cr.owner_id = " + userID +") "); - query.append("ORDER BY Initcap(cr.title)") ;*/ - - String sql = Globals.getLoadReportsToSchedule(); - sql = sql.replace("[userID]", userID); - sql = sql.replace("[roleList.toString()]", roleList.toString()); - - // DataSet ds = DbUtils - // .executeQuery(query.toString()); - - DataSet ds = DbUtils - .executeQuery(sql); - HashMap map = new HashMap(); - for (int i = 0; i < ds.getRowCount(); i++) { - map.put(ds.getItem(i,0), ds.getItem(i,1)); - } - - return map; + + public static HashMap loadReportsToSchedule(HttpServletRequest request) throws RaptorException { + String userID = AppUtils.getUserID(request); + StringBuffer roleList = new StringBuffer(); + roleList.append("-1"); + for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) + roleList.append("," + ((String) iter.next())); + /* + * StringBuffer query = new StringBuffer(""); query.append("SELECT cr.rep_id, "); + * query.append("Initcap(cr.title), "); query.append("cr.descr "); + * query.append("FROM cr_report cr, "); query.append("(SELECT rep_id, "); + * query.append("MIN(read_only_yn) read_only_yn "); query. + * append("FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = "); + * query.append(userID); query.append(") "); query.append("UNION ALL "); + * query.append("(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN (" + * ); query.append(roleList.toString() + "))" + ") report_access "); + * query.append("GROUP BY rep_id) ra " + "WHERE "); query.append("cr.rep_id = ra.rep_id (+) AND "); + * query.append(" (cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL or cr.owner_id = " + userID + * +") "); query.append("ORDER BY Initcap(cr.title)") ; + */ + + String sql = Globals.getLoadReportsToSchedule(); + sql = sql.replace("[userID]", userID); + sql = sql.replace("[roleList.toString()]", roleList.toString()); + + // DataSet ds = DbUtils + // .executeQuery(query.toString()); + + DataSet ds = DbUtils + .executeQuery(sql); + HashMap map = new HashMap(); + for (int i = 0; i < ds.getRowCount(); i++) { + map.put(ds.getItem(i, 0), ds.getItem(i, 1)); + } + + return map; } - - public static HashMap loadReportsToAddInDashboard (HttpServletRequest request) throws RaptorException { - String userID = AppUtils.getUserID(request); - StringBuffer roleList = new StringBuffer(); - roleList.append("-1"); - for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) - roleList.append("," + ((String) iter.next())); - /*StringBuffer query = new StringBuffer(""); - query.append("SELECT cr.rep_id, "); - query.append("cr.title, "); - query.append("cr.descr "); - query.append("FROM cr_report cr, "); - query.append("(SELECT rep_id, "); - query.append("MIN(read_only_yn) read_only_yn "); - query.append("FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = "); - query.append(userID); - query.append(") "); - query.append("UNION ALL "); - query.append("(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ("); - query.append(roleList.toString() + "))" + ") report_access "); - query.append("GROUP BY rep_id) ra " + "WHERE "); - query.append("cr.rep_id = ra.rep_id (+) AND "); - query.append("(nvl(cr.owner_id, cr.create_id) = " + userID); - query.append(" OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL) "); - query.append(" AND (cr.dashboard_yn = 'N' or cr.dashboard_yn is null) "); - query.append("ORDER BY cr.title") ;*/ - - String sql = Globals.getLoadReportsToAddInDashboard(); - sql = sql.replace("[userID]", userID); - sql = sql.replace("[roleList.toString()]", roleList.toString()); - - // DataSet ds = DbUtils - // .executeQuery(query.toString()); - - DataSet ds = DbUtils - .executeQuery(sql); - - HashMap map = new HashMap(); - for (int i = 0; i < ds.getRowCount(); i++) { - map.put(ds.getItem(i,0), ds.getItem(i,1)); - } - - return map; + + public static HashMap loadReportsToAddInDashboard(HttpServletRequest request) throws RaptorException { + String userID = AppUtils.getUserID(request); + StringBuffer roleList = new StringBuffer(); + roleList.append("-1"); + for (Iterator iter = AppUtils.getUserRoles(request).iterator(); iter.hasNext();) + roleList.append("," + ((String) iter.next())); + /* + * StringBuffer query = new StringBuffer(""); query.append("SELECT cr.rep_id, "); + * query.append("cr.title, "); query.append("cr.descr "); query.append("FROM cr_report cr, "); + * query.append("(SELECT rep_id, "); query.append("MIN(read_only_yn) read_only_yn "); query. + * append("FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = "); + * query.append(userID); query.append(") "); query.append("UNION ALL "); + * query.append("(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN (" + * ); query.append(roleList.toString() + "))" + ") report_access "); + * query.append("GROUP BY rep_id) ra " + "WHERE "); query.append("cr.rep_id = ra.rep_id (+) AND "); + * query.append("(nvl(cr.owner_id, cr.create_id) = " + userID); + * query.append(" OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL) "); + * query.append(" AND (cr.dashboard_yn = 'N' or cr.dashboard_yn is null) "); + * query.append("ORDER BY cr.title") ; + */ + + String sql = Globals.getLoadReportsToAddInDashboard(); + sql = sql.replace("[userID]", userID); + sql = sql.replace("[roleList.toString()]", roleList.toString()); + + // DataSet ds = DbUtils + // .executeQuery(query.toString()); + + DataSet ds = DbUtils + .executeQuery(sql); + + HashMap map = new HashMap(); + for (int i = 0; i < ds.getRowCount(); i++) { + map.put(ds.getItem(i, 0), ds.getItem(i, 1)); + } + + return map; } - + public static Vector loadMyRecentLinks(String userID, HttpServletRequest request) throws RaptorException { - /* StringBuffer query = new StringBuffer(""); - query.append("select rep_id, title, descr, form_fields from ( select rownum, rep_id, title, descr, form_fields from "); - query.append(" (select cr.rep_id, cr.title, a.form_fields, cr.descr, a.log_time, a.user_id, a.action, a.action_value " ); - query.append(" from cr_report_log a, cr_report cr where user_id = " + userID); - query.append(" and action = 'Report Execution Time' and a.rep_id = cr.rep_id order by log_time desc) x where rownum <= 6 ) y where rownum >= 1");*/ -// DataSet ds = DbUtils -// .executeQuery( -// " SELECT a.file_name, b.title,to_char(a.dwnld_start_time, 'Dy DD-Mon-YYYY HH24:MI:SS') as time, "+ -// " a.dwnld_start_time " + -// " FROM cr_report_dwnld_log a, cr_report b where a.user_id = "+userID +" and "+ -// " a.rep_id = b.rep_id and (a.dwnld_start_time) >= to_date(to_char(sysdate-24/24, 'mm/dd/yyyy'), 'mm/dd/yyyy') " + -// " and a.record_ready_time is not null " + -// " order by a.dwnld_start_time desc"); -// DataSet ds = DbUtils - // .executeQuery(query.toString()); - - - String sql = Globals.getLoadMyRecentLinks(); - sql = sql.replace("[userID]", userID); - - DataSet ds = DbUtils - .executeQuery(sql); - + /* + * StringBuffer query = new StringBuffer(""); query. + * append("select rep_id, title, descr, form_fields from ( select rownum, rep_id, title, descr, form_fields from " + * ); query. + * append(" (select cr.rep_id, cr.title, a.form_fields, cr.descr, a.log_time, a.user_id, a.action, a.action_value " + * ); query.append(" from cr_report_log a, cr_report cr where user_id = " + userID); query. + * append(" and action = 'Report Execution Time' and a.rep_id = cr.rep_id order by log_time desc) x where rownum <= 6 ) y where rownum >= 1" + * ); + */ + // DataSet ds = DbUtils + // .executeQuery( + // " SELECT a.file_name, b.title,to_char(a.dwnld_start_time, 'Dy DD-Mon-YYYY HH24:MI:SS') as time, + // "+ + // " a.dwnld_start_time " + + // " FROM cr_report_dwnld_log a, cr_report b where a.user_id = "+userID +" and "+ + // " a.rep_id = b.rep_id and (a.dwnld_start_time) >= to_date(to_char(sysdate-24/24, 'mm/dd/yyyy'), + // 'mm/dd/yyyy') " + + // " and a.record_ready_time is not null " + + // " order by a.dwnld_start_time desc"); + // DataSet ds = DbUtils + // .executeQuery(query.toString()); + + String sql = Globals.getLoadMyRecentLinks(); + sql = sql.replace("[userID]", userID); + + DataSet ds = DbUtils + .executeQuery(sql); + Vector myRecentLinks = new Vector(ds.getRowCount()); logger.debug(EELFLoggerDelegate.debugLogger, ("ROW SIZE " + ds.getRowCount())); for (int i = 0; i < ds.getRowCount(); i++) { - myRecentLinks.add("" + ds.getString(i, 1)+ ""); + myRecentLinks.add("" + ds.getString(i, 1) + ""); } logger.debug(EELFLoggerDelegate.debugLogger, ("VECTOR SIZE " + myRecentLinks.size())); return myRecentLinks; - } // loadQuickLinks - + } // loadQuickLinks + public static void createReportLogEntry(Connection connection, String reportID, - String userID, String action, String executionTime,String form_fields) throws RaptorException { - if(form_fields.length()>=4000) form_fields = ""; - //String stmt = "INSERT INTO cr_report_log (rep_id, log_time, user_id, action, action_value, form_fields) VALUES(" - // + reportID + ", SYSDATE, " + userID + ", '" + action + "' , '" + executionTime + "', '"+ form_fields +"')"; - - String stmt = Globals.getCreateReportLogEntry(); - stmt = stmt.replace("[reportID]", reportID); - stmt = stmt.replace("[userID]", userID); - stmt = stmt.replace("[action]", action); - stmt = stmt.replace("[executionTime]", executionTime); - stmt = stmt.replace("[form_fields]", form_fields); - - if (Globals.getEnableReportLog()) - if (connection == null) - DbUtils.executeUpdate(stmt); - else - DbUtils.executeUpdate(connection, stmt); - } // createReportLogEntry - + String userID, String action, String executionTime, String form_fields) throws RaptorException { + if (form_fields.length() >= 4000) + form_fields = ""; + // String stmt = "INSERT INTO cr_report_log (rep_id, log_time, user_id, action, action_value, + // form_fields) VALUES(" + // + reportID + ", SYSDATE, " + userID + ", '" + action + "' , '" + executionTime + "', '"+ + // form_fields +"')"; + + String stmt = Globals.getCreateReportLogEntry(); + stmt = stmt.replace("[reportID]", reportID); + stmt = stmt.replace("[userID]", userID); + stmt = stmt.replace("[action]", action); + stmt = stmt.replace("[executionTime]", executionTime); + stmt = stmt.replace("[form_fields]", form_fields); + + if (Globals.getEnableReportLog()) + if (connection == null) + DbUtils.executeUpdate(stmt); + else + DbUtils.executeUpdate(connection, stmt); + } // createReportLogEntry + public static void createReportLogEntryForExecutionTime(Connection connection, String reportID, - String userID, String executionTime, String action, String formFields) throws RaptorException { - if(formFields.length()>=4000) formFields = ""; - //String stmt = "INSERT INTO cr_report_log (rep_id, log_time, user_id, action, action_value, form_fields) VALUES(" - // + reportID + ", sysdate+1/(24*60*60) , " + userID + ", '" + action + "' , '" + executionTime + "', '"+ formFields +"')"; - - String stmt = Globals.getCreateReportLogEntryExecTime(); - stmt = stmt.replace("[reportID]", reportID); - stmt = stmt.replace("[userID]", userID); - stmt = stmt.replace("[action]", action); - stmt = stmt.replace("[executionTime]", executionTime); - stmt = stmt.replace("[formFields]", formFields); - - if (Globals.getEnableReportLog()) - if (connection == null) - DbUtils.executeUpdate(stmt); - else - DbUtils.executeUpdate(connection, stmt); - } // createReportLogEntry - - public static void clearReportLogEntries(String reportId, String userId) throws RaptorException { - String sql = Globals.getClearReportLogEntries(); - Connection connection = DbUtils.getConnection(); - int rowsAffected = 0; - try(PreparedStatement stmt = connection.prepareStatement(sql)) { - stmt.setInt(1, Integer.parseInt(reportId)); - stmt.setInt(2, Integer.parseInt(userId)); - rowsAffected = stmt.executeUpdate(); - if(rowsAffected > 0) connection.commit(); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } finally { - DbUtils.clearConnection(connection); - } - } // clearReportLogEntries - - public static Vector loadReportLogEntries(String reportId) throws RaptorException { - /* StringBuffer query = new StringBuffer("SELECT x.log_time, x.user_id,") ; - query.append(" (CASE WHEN x.action = 'Report Execution Time' THEN "); - query.append(" ''||x.action||''"); - query.append(" ELSE x.action END) action, " ); - query.append(" (CASE WHEN x.action = 'Report Execution Time' THEN "); - query.append(" action_value " ); - query.append(" ELSE 'N/A' END) time_taken, " ); - query.append( " (CASE WHEN x.action = 'Report Execution Time' THEN '\"Run' ELSE 'N/A' END) run_image, " ); - query.append(" x.name FROM "); - query.append(" (SELECT rl.rep_id, TO_CHAR(rl.log_time, 'Month DD, YYYY HH:MI:SS AM') log_time, rl.action_value, fuser.last_name ||', '||fuser.first_name name, "); - query.append(" rl.user_id, rl.action, rl.form_fields FROM cr_report_log rl, fn_user fuser WHERE rl.rep_id = "+ nvls(reportId)+ " and rl.action != 'Report Run' and fuser.user_id = rl.user_id" ); - query.append(" ORDER BY rl.log_time DESC) x WHERE ROWNUM <= 100");*/ -// DataSet ds = DbUtils -// .executeQuery("SELECT x.log_time, x.user_id, x.action FROM (SELECT TO_CHAR(rl.log_time, 'Month DD, YYYY HH:MI:SS AM') log_time, rl.user_id, rl.action FROM cr_report_log rl WHERE rl.rep_id = " -// + nvls(reportId) + " ORDER BY rl.log_time DESC) x WHERE ROWNUM <= 100"); - // DataSet ds = DbUtils.executeQuery(query.toString()); - + String userID, String executionTime, String action, String formFields) throws RaptorException { + if (formFields.length() >= 4000) + formFields = ""; + // String stmt = "INSERT INTO cr_report_log (rep_id, log_time, user_id, action, action_value, + // form_fields) VALUES(" + // + reportID + ", sysdate+1/(24*60*60) , " + userID + ", '" + action + "' , '" + executionTime + + // "', '"+ formFields +"')"; + + String stmt = Globals.getCreateReportLogEntryExecTime(); + stmt = stmt.replace("[reportID]", reportID); + stmt = stmt.replace("[userID]", userID); + stmt = stmt.replace("[action]", action); + stmt = stmt.replace("[executionTime]", executionTime); + stmt = stmt.replace("[formFields]", formFields); + + if (Globals.getEnableReportLog()) + if (connection == null) + DbUtils.executeUpdate(stmt); + else + DbUtils.executeUpdate(connection, stmt); + } // createReportLogEntry + + public static void clearReportLogEntries(String reportId, String userId) throws RaptorException { + String sql = Globals.getClearReportLogEntries(); + Connection connection = DbUtils.getConnection(); + int rowsAffected = 0; + try (PreparedStatement stmt = connection.prepareStatement(sql)) { + stmt.setInt(1, Integer.parseInt(reportId)); + stmt.setInt(2, Integer.parseInt(userId)); + rowsAffected = stmt.executeUpdate(); + if (rowsAffected > 0) + connection.commit(); + } catch (SQLException ex) { + logger.error(EELFLoggerDelegate.errorLogger, "SQLException occured in clearReportLogEntries", ex); + throw new ReportSQLException(ex.getMessage(), ex.getCause()); + } finally { + DbUtils.clearConnection(connection); + } + } // clearReportLogEntries + + public static Vector loadReportLogEntries(String reportId) throws RaptorException { + /* + * StringBuffer query = new StringBuffer("SELECT x.log_time, x.user_id,") ; + * query.append(" (CASE WHEN x.action = 'Report Execution Time' THEN "); query.append(" ''||x.action||''" + * ); query.append(" ELSE x.action END) action, " ); + * query.append(" (CASE WHEN x.action = 'Report Execution Time' THEN "); + * query.append(" action_value " ); query.append(" ELSE 'N/A' END) time_taken, " ); query.append( + * " (CASE WHEN x.action = 'Report Execution Time' THEN '\"Run' ELSE 'N/A' END) run_image, " + * ); query.append(" x.name FROM "); query. + * append(" (SELECT rl.rep_id, TO_CHAR(rl.log_time, 'Month DD, YYYY HH:MI:SS AM') log_time, rl.action_value, fuser.last_name ||', '||fuser.first_name name, " + * ); query. + * append(" rl.user_id, rl.action, rl.form_fields FROM cr_report_log rl, fn_user fuser WHERE rl.rep_id = " + * + nvls(reportId)+ " and rl.action != 'Report Run' and fuser.user_id = rl.user_id" ); + * query.append(" ORDER BY rl.log_time DESC) x WHERE ROWNUM <= 100"); + */ + // DataSet ds = DbUtils + // .executeQuery("SELECT x.log_time, x.user_id, x.action FROM (SELECT TO_CHAR(rl.log_time, 'Month + // DD, YYYY HH:MI:SS AM') log_time, rl.user_id, rl.action FROM cr_report_log rl WHERE rl.rep_id = " + // + nvls(reportId) + " ORDER BY rl.log_time DESC) x WHERE ROWNUM <= 100"); + // DataSet ds = DbUtils.executeQuery(query.toString()); + String sql = Globals.getLoadReportLogEntries(); - sql = sql.replace("[AppUtils.getRaptorActionURL()]", AppUtils.getRaptorActionURL()); + sql = sql.replace("[AppUtils.getRaptorActionURL()]", AppUtils.getRaptorActionURL()); sql = sql.replace("[AppUtils.getImgFolderURL()]", AppUtils.getImgFolderURL()); sql = sql.replace("[nvls(reportId)]", nvls(reportId)); - - + DataSet ds = DbUtils.executeQuery(sql); - - Vector logEntries = new Vector(ds.getRowCount()); - - for (int i = 0; i < ds.getRowCount(); i++) - logEntries.add(new ReportLogEntry(ds.getString(i, 0), ds - .getString(i, 5), ds.getString(i, 2), ds.getString(i, 3), ds.getString(i, 4))); - - return logEntries; - } // loadReportLogEntries - - public static boolean doesUserCanScheduleReport(HttpServletRequest request, String scheduleId) throws RaptorException { - boolean flagLimit = false; - boolean flagScheduleIdPresent = false; - String userId = AppUtils.getUserID(request); - if(AppUtils.isAdminUser(request))return true; - //String query = "select crs.sched_user_id, count(*) from cr_report_schedule crs where sched_user_id = " + userId + " group by crs.sched_user_id having count(*) >= " + Globals.getScheduleLimit(); - String query = Globals.getDoesUserCanScheduleReport(); - query = query.replace("[userId]", userId); - query = query.replace("[Globals.getScheduleLimit()]", String.valueOf(Globals.getScheduleLimit())); - - DataSet ds = DbUtils.executeQuery(query); - logger.debug(EELFLoggerDelegate.debugLogger, (" User Schedule ds.getRowCount() " + ds.getRowCount() + " " +(ds.getRowCount()>0))); - if(ds.getRowCount() > 0) flagLimit = true; - else flagLimit = false; - logger.debug(EELFLoggerDelegate.debugLogger, ("scheduleId " + scheduleId)); - if(scheduleId==null || scheduleId.trim().length()<=0) return !flagLimit; - //query = "select crs.schedule_id from cr_report_schedule crs where schedule_id = " + scheduleId; - query = Globals.getDoesUserCanSchedule(); - query = query.replace("[scheduleId]", scheduleId); - - if(ds.getRowCount() > 0) flagScheduleIdPresent = true; - else flagScheduleIdPresent = false; - if(!flagLimit) return true; - if(flagLimit && flagScheduleIdPresent) return true; - else return false; - } - - public static String getSystemDateTime() throws RaptorException { - //String query = "select to_char(sysdate,'MM/dd/yyyy HH24:mi:ss') from dual"; - String query = Globals.getTheSystemDateTime(); - - DataSet ds = DbUtils.executeQuery(query); - String timeStr = ""; - if(ds.getRowCount() > 0) { - timeStr = ds.getString(0,0); - } - return timeStr; - - } - - public static String getNextDaySystemDateTime() throws RaptorException { - //String query = "select to_char(sysdate+1,'MM/dd/yyyy HH24:mi:ss') from dual"; - String query = Globals.getTheNextDayDateTime(); - DataSet ds = DbUtils.executeQuery(query); - String timeStr = ""; - if(ds.getRowCount() > 0) { - timeStr = ds.getString(0,0); - } - return timeStr; - - } - - public static String getNext15MinutesOfSystemDateTime() throws RaptorException { - //String query = "select to_char(sysdate+15/(24*60),'MM/dd/yyyy HH24:mi:ss') from dual"; - String query = Globals.getTheNextFifteenMinDateTime(); - - DataSet ds = DbUtils.executeQuery(query); - String timeStr = ""; - if(ds.getRowCount() > 0) { - timeStr = ds.getString(0,0); - } - return timeStr; - - } - - public static String getNext30MinutesOfSystemDateTime() throws RaptorException { - //String query = "select to_char(sysdate+30/(24*60),'MM/dd/yyyy HH24:mi:ss') from dual"; - String query = Globals.getTheNextThirtyMinDateTime(); - DataSet ds = DbUtils.executeQuery(query); - String timeStr = ""; - if(ds.getRowCount() > 0) { - timeStr = ds.getString(0,0); - } - return timeStr; - - } - - public static String getTemplateFile(String reportId) throws RaptorException { - //String query = "select template_file from cr_report_template_map where report_id = " + reportId; - String query = Globals.getTheTemplateFile(); - query = query.replace("[reportId]", reportId); - String templateFile = ""; - try { - DataSet ds = DbUtils.executeQuery(query); - if(ds.getRowCount() > 0) { - templateFile = ds.getString(0,0); - } - }catch(RaptorException ex) { - logger.debug(EELFLoggerDelegate.debugLogger, ("SQL Exception while trying to access cr_report_template_map ")); - } - return templateFile; - - } - - - public static HashMap loadPDFImgLookUp() throws RaptorException { - StringBuffer query = new StringBuffer(""); - HashMap pdfImgMap = new HashMap(); - //query.append("select image_id, image_loc from cr_raptor_pdf_img"); - query.append(Globals.getLoadPdfImgLookup()); - DataSet ds = DbUtils.executeQuery(query.toString()); - for (int i = 0; i < ds.getRowCount(); i++) { - pdfImgMap.put(ds.getString(i, 0), ds.getString(i,1)); - } - return pdfImgMap; - } // loadQuickLinks - - public static HashMap loadActionImgLookUp() throws RaptorException { - StringBuffer query = new StringBuffer(""); - HashMap pdfImgMap = new HashMap(); - //query.append("select image_id, image_loc from cr_raptor_action_img"); - query.append(Globals.getLoadActionImgLookup()); - DataSet ds = DbUtils.executeQuery(query.toString()); - for (int i = 0; i < ds.getRowCount(); i++) { - pdfImgMap.put(ds.getString(i, 0), ds.getString(i,1)); - } - return pdfImgMap; - } // loadQuickLinks -} // ReportLoader + Vector logEntries = new Vector(ds.getRowCount()); + + for (int i = 0; i < ds.getRowCount(); i++) + logEntries.add(new ReportLogEntry(ds.getString(i, 0), ds + .getString(i, 5), ds.getString(i, 2), ds.getString(i, 3), ds.getString(i, 4))); + + return logEntries; + } // loadReportLogEntries + + public static boolean doesUserCanScheduleReport(HttpServletRequest request, String scheduleId) + throws RaptorException { + boolean flagLimit = false; + boolean flagScheduleIdPresent = false; + String userId = AppUtils.getUserID(request); + if (AppUtils.isAdminUser(request)) + return true; + // String query = "select crs.sched_user_id, count(*) from cr_report_schedule crs where + // sched_user_id = " + userId + " group by crs.sched_user_id having count(*) >= " + + // Globals.getScheduleLimit(); + String query = Globals.getDoesUserCanScheduleReport(); + query = query.replace("[userId]", userId); + query = query.replace("[Globals.getScheduleLimit()]", String.valueOf(Globals.getScheduleLimit())); + + DataSet ds = DbUtils.executeQuery(query); + logger.debug(EELFLoggerDelegate.debugLogger, + (" User Schedule ds.getRowCount() " + ds.getRowCount() + " " + (ds.getRowCount() > 0))); + if (ds.getRowCount() > 0) + flagLimit = true; + else + flagLimit = false; + logger.debug(EELFLoggerDelegate.debugLogger, ("scheduleId " + scheduleId)); + if (scheduleId == null || scheduleId.trim().length() <= 0) + return !flagLimit; + // query = "select crs.schedule_id from cr_report_schedule crs where schedule_id = " + scheduleId; + query = Globals.getDoesUserCanSchedule(); + query = query.replace("[scheduleId]", scheduleId); + + if (ds.getRowCount() > 0) + flagScheduleIdPresent = true; + else + flagScheduleIdPresent = false; + if (!flagLimit) + return true; + if (flagLimit && flagScheduleIdPresent) + return true; + else + return false; + } + + public static String getSystemDateTime() throws RaptorException { + // String query = "select to_char(sysdate,'MM/dd/yyyy HH24:mi:ss') from dual"; + String query = Globals.getTheSystemDateTime(); + + DataSet ds = DbUtils.executeQuery(query); + String timeStr = ""; + if (ds.getRowCount() > 0) { + timeStr = ds.getString(0, 0); + } + return timeStr; + + } + + public static String getNextDaySystemDateTime() throws RaptorException { + // String query = "select to_char(sysdate+1,'MM/dd/yyyy HH24:mi:ss') from dual"; + String query = Globals.getTheNextDayDateTime(); + DataSet ds = DbUtils.executeQuery(query); + String timeStr = ""; + if (ds.getRowCount() > 0) { + timeStr = ds.getString(0, 0); + } + return timeStr; + + } + + public static String getNext15MinutesOfSystemDateTime() throws RaptorException { + // String query = "select to_char(sysdate+15/(24*60),'MM/dd/yyyy HH24:mi:ss') from dual"; + String query = Globals.getTheNextFifteenMinDateTime(); + + DataSet ds = DbUtils.executeQuery(query); + String timeStr = ""; + if (ds.getRowCount() > 0) { + timeStr = ds.getString(0, 0); + } + return timeStr; + + } + + public static String getNext30MinutesOfSystemDateTime() throws RaptorException { + // String query = "select to_char(sysdate+30/(24*60),'MM/dd/yyyy HH24:mi:ss') from dual"; + String query = Globals.getTheNextThirtyMinDateTime(); + DataSet ds = DbUtils.executeQuery(query); + String timeStr = ""; + if (ds.getRowCount() > 0) { + timeStr = ds.getString(0, 0); + } + return timeStr; + + } + + public static String getTemplateFile(String reportId) throws RaptorException { + // String query = "select template_file from cr_report_template_map where report_id = " + reportId; + String query = Globals.getTheTemplateFile(); + query = query.replace("[reportId]", reportId); + String templateFile = ""; + try { + DataSet ds = DbUtils.executeQuery(query); + if (ds.getRowCount() > 0) { + templateFile = ds.getString(0, 0); + } + } catch (RaptorException ex) { + logger.debug(EELFLoggerDelegate.debugLogger, + ("SQL Exception while trying to access cr_report_template_map "), ex); + } + return templateFile; + } + + public static HashMap loadPDFImgLookUp() throws RaptorException { + StringBuffer query = new StringBuffer(""); + HashMap pdfImgMap = new HashMap(); + // query.append("select image_id, image_loc from cr_raptor_pdf_img"); + query.append(Globals.getLoadPdfImgLookup()); + DataSet ds = DbUtils.executeQuery(query.toString()); + for (int i = 0; i < ds.getRowCount(); i++) { + pdfImgMap.put(ds.getString(i, 0), ds.getString(i, 1)); + } + return pdfImgMap; + } // loadQuickLinks + + public static HashMap loadActionImgLookUp() throws RaptorException { + StringBuffer query = new StringBuffer(""); + HashMap pdfImgMap = new HashMap(); + // query.append("select image_id, image_loc from cr_raptor_action_img"); + query.append(Globals.getLoadActionImgLookup()); + DataSet ds = DbUtils.executeQuery(query.toString()); + for (int i = 0; i < ds.getRowCount(); i++) { + pdfImgMap.put(ds.getString(i, 0), ds.getString(i, 1)); + } + return pdfImgMap; + } // loadQuickLinks + +} // ReportLoader -- cgit 1.2.3-korg