/*
* ============LICENSE_START==========================================
* ONAP Portal SDK
* ===================================================================
* Copyright © 2017 AT&T Intellectual Property. All rights reserved.
* ===================================================================
*
* Unless otherwise specified, all software contained herein is licensed
* under the Apache License, Version 2.0 (the "License");
* you may not use this software 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.
*
* Unless otherwise specified, all documentation contained herein is licensed
* under the Creative Commons License, Attribution 4.0 Intl. (the "License");
* you may not use this documentation except in compliance with the License.
* You may obtain a copy of the License at
*
* https://creativecommons.org/licenses/by/4.0/
*
* Unless required by applicable law or agreed to in writing, documentation
* 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.
*
* ============LICENSE_END============================================
*
*
*/
/* ===========================================================================================
* 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);
- isDashboardType is made to return false, as any report can be added to Dashboard.
* 18-Aug-2009 : Version 8.5.1 (Sundar);- request Object is passed to prevent caching user/roles - Datamining/Hosting.
* 27-Jul-2009 : Version 8.4 (Sundar);- Admin User is given the same privilege as Super User when the property
* "admin_role_equiv_to_super_role" in raptor.properties is Y. A check is made in corresponding to that.
*
*/
package org.onap.portalsdk.analytics.model;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.Writer;
import java.nio.charset.StandardCharsets;
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.onap.portalsdk.analytics.error.RaptorException;
import org.onap.portalsdk.analytics.error.ReportSQLException;
import org.onap.portalsdk.analytics.model.base.IdNameValue;
import org.onap.portalsdk.analytics.model.base.ReportWrapper;
import org.onap.portalsdk.analytics.model.definition.ReportLogEntry;
import org.onap.portalsdk.analytics.model.search.ReportSearchResult;
import org.onap.portalsdk.analytics.system.AppUtils;
import org.onap.portalsdk.analytics.system.DbUtils;
import org.onap.portalsdk.analytics.system.Globals;
import org.onap.portalsdk.analytics.system.fusion.domain.QuickLink;
import org.onap.portalsdk.analytics.util.AppConstants;
import org.onap.portalsdk.analytics.util.DataSet;
import org.onap.portalsdk.analytics.util.Utils;
import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;
import org.onap.portalsdk.core.util.SecurityCodecUtil;
import org.owasp.esapi.ESAPI;
public class ReportLoader extends org.onap.portalsdk.analytics.RaptorObject {
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_DB);
int len = 0;
char[] buffer = new char[512];
Reader in = null;
in = new InputStreamReader(clob.getAsciiStream());
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)) {
stmt.setInt(1, Integer.parseInt(reportID));
try (ResultSet rs = stmt.executeQuery()) {
/*
* 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 (Globals.isPostgreSQL()) {
if (rs.next()) {
rs.updateString("report_xml", reportXML);
rs.updateRow();
connection.commit();
} else {
throw new RaptorException(
"Report " + reportID + NOT_FOUND_IN_DB);
}
} else if (Globals.isMySQL()) {
if (rs.next()) {
final InputStream stream = rs.getBinaryStream("report_xml");
try (InputStream streamNew = new ByteArrayInputStream(
reportXML.getBytes(StandardCharsets.UTF_8));
final PreparedStatement update = connection
.prepareStatement(Globals.getDBUpdateReportXmlMySql())) {
update.setBinaryStream(1, streamNew);
update.setInt(2, Integer.parseInt(reportID));
update.execute();
}
} 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(2); else throw
* new RaptorException("Report " + reportID + NOT_FOUND_IN_DB);
*
* 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) {
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 boolean isReportsAlreadyScheduled(String reportID) throws RaptorException {
String sql = Globals.getIsReportAlreadyScheduled();
Connection connection = DbUtils.getConnection();
boolean isScheduled = false;
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
stmt.setInt(1, Integer.parseInt(reportID));
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
isScheduled = true;
}
}
} catch (SQLException ex) {
logger.error(EELFLoggerDelegate.errorLogger, "SQLException occured in isReportsAlreadyScheduled", ex);
throw new ReportSQLException(ex.getMessage(), ex.getCause());
} finally {
DbUtils.clearConnection(connection);
}
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);
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 {
String sql = Globals.getTheReportOwnerId();
Connection connection = DbUtils.getConnection();
String reportOwnerID = null;
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
stmt.setInt(1, Integer.parseInt(reportID));
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
reportOwnerID = rs.getString(1);
}
}
} catch (SQLException ex) {
logger.error(EELFLoggerDelegate.errorLogger, "SQLException occured in getReportOwnerID", ex);
throw new ReportSQLException(ex.getMessage(), ex.getCause());
} finally {
DbUtils.clearConnection(connection);
}
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) {
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()));
// 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]", 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++) {
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");
link.append("v2/run/"+ ds.getString(i, 0));
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 = " ";
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 = 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);
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(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(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"
* );
*/
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 = 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) {
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 '' 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");
*/
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 = 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 = 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 = 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 = 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 = 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 = 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 = 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(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(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