From 6beb446925c967aca92f5513adf36c5db77c00d6 Mon Sep 17 00:00:00 2001 From: TATTAVARADA Date: Thu, 27 Apr 2017 07:53:18 -0400 Subject: [PORTAL-7] Rebase This rebasing includes common libraries and common overlays projects abstraction of components Change-Id: Ia1efa4deacdc5701e6205104ac021a6c80ed60ba Signed-off-by: st782s --- .../portalsdk/analytics/model/ReportLoader.java | 1061 -------------------- 1 file changed, 1061 deletions(-) delete mode 100644 ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/ReportLoader.java (limited to 'ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/ReportLoader.java') diff --git a/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/ReportLoader.java b/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/ReportLoader.java deleted file mode 100644 index 2197de66..00000000 --- a/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/ReportLoader.java +++ /dev/null @@ -1,1061 +0,0 @@ -/*- - * ================================================================================ - * eCOMP Portal SDK - * ================================================================================ - * Copyright (C) 2017 AT&T Intellectual Property - * ================================================================================ - * Licensed under the Apache License, Version 2.0 (the "License"); - * you may not use this file except in compliance with the License. - * You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - * ================================================================================ - */ -/* =========================================================================================== - * 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 - * =========================================================================================== - * - * ------------------------------------------------------------------------------------------- - * 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); - * - */ -package org.openecomp.portalsdk.analytics.model; - -import java.io.IOException; -import java.io.InputStreamReader; -import java.io.Reader; -import java.io.Writer; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.ArrayList; -import java.util.HashMap; -import java.util.Iterator; -import java.util.Vector; - -import javax.servlet.http.HttpServletRequest; - -import org.openecomp.portalsdk.analytics.error.RaptorException; -import org.openecomp.portalsdk.analytics.error.ReportSQLException; -import org.openecomp.portalsdk.analytics.model.base.IdNameValue; -import org.openecomp.portalsdk.analytics.model.base.ReportWrapper; -import org.openecomp.portalsdk.analytics.model.definition.ReportLogEntry; -import org.openecomp.portalsdk.analytics.model.search.ReportSearchResult; -import org.openecomp.portalsdk.analytics.system.AppUtils; -import org.openecomp.portalsdk.analytics.system.DbUtils; -import org.openecomp.portalsdk.analytics.system.Globals; -import org.openecomp.portalsdk.analytics.system.fusion.domain.QuickLink; -import org.openecomp.portalsdk.analytics.util.AppConstants; -import org.openecomp.portalsdk.analytics.util.DataSet; -import org.openecomp.portalsdk.analytics.util.Utils; -import org.openecomp.portalsdk.core.logging.logic.EELFLoggerDelegate; - -public class ReportLoader extends org.openecomp.portalsdk.analytics.RaptorObject { - - static EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportLoader.class); - - - //private static Properties sqlProperty; - - 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(); - - PreparedStatement stmt = null; - - ResultSet rs = null; - - try { - - String sql = Globals.getLoadCustomReportXml(); - stmt = connection.prepareStatement(sql); - stmt.setInt(1,Integer.parseInt(reportID)); - 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()); - } finally { - try { - if(rs!=null) - rs.close(); - if(stmt!=null) - stmt.close(); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } - } - return sb.toString(); - } // loadCustomReportXML - - private static void dbUpdateReportXML(Connection connection, String reportID, - String reportXML) throws RaptorException { - PreparedStatement stmt = null; - ResultSet rs = null; - - try { - String sql = Globals.getDBUpdateReportXml(); - stmt = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE, - ResultSet.CONCUR_UPDATABLE); - stmt.setInt(1,Integer.parseInt(reportID)); - 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 (clob.length() > reportXML.length()) - clob.truncate(0); - //clob.trim(reportXML.length()); - out = ((weblogic.jdbc.vendor.oracle.OracleThinClob)clob).getCharacterOutputStream(); - } else*/ - if (Globals.isPostgreSQL() || Globals.isMySQL()) { - if (rs.next()) { - rs.updateString("report_xml",reportXML); - rs.updateRow(); - connection.commit(); - //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"); - - if (clob.length() > reportXML.length()) - clob.trim(reportXML.length()); - out = clob.getCharacterOutputStream();*/ - throw new RaptorException("only maria db support for this "); - } - if(!(Globals.isPostgreSQL() || Globals.isMySQL())) { - out.write(reportXML); - out.flush(); - out.close(); - } - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } catch (IOException ex) { - throw new RaptorException (ex.getMessage(), ex.getCause()); - } finally { - try { - if(rs!=null) - rs.close(); - if(stmt!=null) - stmt.close(); - } catch (SQLException ex) { - throw new ReportSQLException (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.getOracleTimeFormat()]", Globals.getOracleTimeFormat()); - 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 = ?"; - String sql = Globals.getIsReportAlreadyScheduled(); - - Connection connection = DbUtils.getConnection(); - PreparedStatement stmt = null; - ResultSet rs = null; - boolean isScheduled= false; - try { - stmt = connection.prepareStatement(sql); - stmt.setInt(1, Integer.parseInt(reportID)); - rs = stmt.executeQuery(); - if(rs.next()) { - isScheduled = true; - } - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } finally { - try { - if(rs!=null) - rs.close(); - if(stmt!=null) - stmt.close(); - DbUtils.clearConnection(connection); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } - } - 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.getOracleTimeFormat()]", Globals.getOracleTimeFormat()); - 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 String getReportOwnerID(String reportID) throws RaptorException { - - // String sql = "SELECT nvl(cr.owner_id, cr.create_id) owner FROM cr_report cr WHERE rep_id = ?"; - - String sql = Globals.getTheReportOwnerId(); - - Connection connection = DbUtils.getConnection(); - PreparedStatement stmt = null; - ResultSet rs = null; - String reportOwnerID = null; - try { - stmt = connection.prepareStatement(sql); - stmt.setInt(1, Integer.parseInt(reportID)); - rs = stmt.executeQuery(); - if(rs.next()) { - reportOwnerID = rs.getString(1); - } - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } finally { - try { - if(rs!=null) - rs.close(); - if(stmt!=null) - stmt.close(); - DbUtils.clearConnection(connection); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } - } - - 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]", reportID); - String sql2= Globals.getDeleteReportRecordUsers(); - sql2 = sql2.replace("[reportID]", reportID); - String sql3= Globals.getDeleteReportRecordSchedule(); - sql3 = sql3.replace("[reportID]", reportID); - String sql4= Globals.getDeleteReportRecordAccess(); - sql4 = sql4.replace("[reportID]", reportID); - String sql5= Globals.getDeleteReportRecordEmail(); - sql5 = sql5.replace("[reportID]", reportID); - String sql6= Globals.getDeleteReportRecordFavorite(); - sql6 = sql6.replace("[reportID]", reportID); - String sql7= Globals.getDeleteReportRecordReport(); - sql7 = sql7.replace("[reportID]", 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); - } - } // 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()); - } - - 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]", userID); - query = query.replace("[roleList.toString()]", roleList.toString()); - query = query.replace("[nvls(menuId)]", nvls(menuId)); - - DataSet ds = DbUtils - .executeQuery(query); - - ArrayList quickLinksArray = new ArrayList(ds.getRowCount()); - 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); - } - - 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); - - - 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)); - } - 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 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); - - 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)+ ""); - } - logger.debug(EELFLoggerDelegate.debugLogger, ("VECTOR SIZE " + myRecentLinks.size())); - - return myRecentLinks; - } // 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 - - 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(); - PreparedStatement stmt = null; - String reportOwnerID = null; - int rowsAffected = 0; - try { - 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 { - try { - stmt.close(); - connection.close(); - DbUtils.clearConnection(connection); - } catch (SQLException ex) { - throw new ReportSQLException (ex.getMessage(), ex.getCause()); - } - } - } // 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.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 - -- cgit 1.2.3-korg