From 40a17c1d7e5f0edca350a49210e16223a875d290 Mon Sep 17 00:00:00 2001 From: "RAMALINGAM, SUNDAR (sr7234)" Date: Wed, 25 Jul 2018 16:13:16 -0400 Subject: Reporting Enhancements Issue-ID: PORTAL-314 Remote DB logic is added to enhance RAPTOR to pull content from different DB as well as Different DB TYPE Change-Id: I5cb343cd9daa912d78e7a8e409d28c8845d9afb4 Signed-off-by: RAMALINGAM, SUNDAR (sr7234) --- .../analytics/model/base/ReportWrapper.java | 13 +++--- .../model/definition/wizard/DefinitionJSON.java | 9 +++- .../analytics/model/runtime/ReportRuntime.java | 2 +- .../analytics/system/fusion/RemoteDbUtils.java | 8 +++- .../system/fusion/web/RaptorControllerAsync.java | 42 ++++++++++++++++- .../onap/portalsdk/analytics/util/RemDbInfo.java | 52 ++++++++++++++++++++-- 6 files changed, 112 insertions(+), 14 deletions(-) (limited to 'ecomp-sdk/epsdk-analytics/src/main') diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/base/ReportWrapper.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/base/ReportWrapper.java index 97faffb1..1dce0e36 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/base/ReportWrapper.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/base/ReportWrapper.java @@ -3684,11 +3684,10 @@ public class ReportWrapper extends org.onap.portalsdk.analytics.RaptorObject { setWholeSQL(reportSQL); if(nvl(reportSQL).length()>0) reportSQL = generateSQL(userId, request); - - if (reportSQL.toUpperCase().indexOf("ORDER BY ") < 0) { + if (nvl(reportSQL).toUpperCase().indexOf("ORDER BY ") < 0) { StringBuffer sortBy = null; - if (reportSQL.toUpperCase().indexOf("GROUP BY ") < 0) + if (nvl(reportSQL).toUpperCase().indexOf("GROUP BY ") < 0) if (getDataSourceList().getDataSource().size() > 0) { DataSourceType dst = (DataSourceType) getDataSourceList().getDataSource() .get(0); @@ -3706,15 +3705,17 @@ public class ReportWrapper extends org.onap.portalsdk.analytics.RaptorObject { } // while } } // if - if (reportSQL.trim().toUpperCase().startsWith("SELECT")) { + if (nvl(reportSQL).trim().toUpperCase().startsWith("SELECT")) { //if (!(dbType.equals("DAYTONA") && reportSQL.trim().toUpperCase().startsWith("SELECT"))) // reportSQL += " ORDER BY " + ((sortBy == null) ? "1" : sortBy.toString()); } } - StringBuffer colNames = new StringBuffer(); StringBuffer colExtraIdNames = new StringBuffer(); StringBuffer colExtraDateNames = new StringBuffer(); + if(getDBType()!=null && getDBType().equals(AppConstants.ORACLE)) { + colNames.append("rownum rnum"); + } if(getColumnNamesFromReportSQL) { DataSet ds = ConnectionUtils.getDataSet(reportSQL, dbInfo); @@ -3848,7 +3849,7 @@ public class ReportWrapper extends org.onap.portalsdk.analytics.RaptorObject { partSql = partSql+ " LIMIT "+ String.valueOf(startRow)+" , "+ String.valueOf(endRow); } else if(getDBType().equals(AppConstants.ORACLE)) { reportSQL = reportSQL.replace(" AS ", " "); - partSql = "where rownum >= "+ String.valueOf(startRow)+" and rownum <= "+(Integer.parseInt(String.valueOf(startRow)) + Integer.parseInt(String.valueOf(endRow))); + partSql = "where rnum >= "+ String.valueOf(startRow)+" and rnum <= "+(Integer.parseInt(String.valueOf(startRow)) + Integer.parseInt(String.valueOf(endRow))); } else if(getDBType().equals(AppConstants.POSTGRESQL)) { partSql = partSql + " LIMIT "+ String.valueOf(endRow)+" , "+ String.valueOf(startRow);//limit [pageSize] offset [startRow] } diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/definition/wizard/DefinitionJSON.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/definition/wizard/DefinitionJSON.java index 47b1ef9e..483505af 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/definition/wizard/DefinitionJSON.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/definition/wizard/DefinitionJSON.java @@ -51,6 +51,7 @@ public class DefinitionJSON implements WizardJSON { private String dbInfo; private String formHelpText; private Integer pageSize; + private List dbInfoList = null; private List displayArea = null; private Boolean hideFormFieldsAfterRun; private Integer maxRowsInExcelCSVDownload; @@ -327,5 +328,11 @@ public class DefinitionJSON implements WizardJSON { this.repDefType = repDefType; } - + public List getDbInfoList() { + return dbInfoList; + } + + public void setDbInfoList(List dbInfoList) { + this.dbInfoList = dbInfoList; + } } diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/runtime/ReportRuntime.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/runtime/ReportRuntime.java index bb99318f..4b72502f 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/runtime/ReportRuntime.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/runtime/ReportRuntime.java @@ -1338,7 +1338,7 @@ public class ReportRuntime extends ReportWrapper implements Cloneable, Serializa DataColumnType dct = (DataColumnType) reportCols.get(c); DataValue dv = new DataValue(); dr.addDataValue(dv); - dv.setDisplayValue(ds.getString(r, c)); + dv.setDisplayValue(ds.getString(r, ds.getColumnIndex(dct.getColId()))); dv.setColName(dct.getColName()); dv.setColId(dct.getColId()); dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null")); diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/fusion/RemoteDbUtils.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/fusion/RemoteDbUtils.java index 032b394b..fcff3183 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/fusion/RemoteDbUtils.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/fusion/RemoteDbUtils.java @@ -58,7 +58,13 @@ public class RemoteDbUtils implements RDbUtils{ public Connection getRemoteConnection(String dbKey) { - return raptorAdapter.getConnection(dbKey); + try { + org.onap.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.onap.portalsdk.analytics.util.RemDbInfo(); + return remDbInfo.getDBConnection(dbKey); + } catch (Exception ex) { + ex.printStackTrace(); + return null; + } } public void clearConnection(Connection conn) { diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/fusion/web/RaptorControllerAsync.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/fusion/web/RaptorControllerAsync.java index e06a1765..c761bdcc 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/fusion/web/RaptorControllerAsync.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/system/fusion/web/RaptorControllerAsync.java @@ -96,6 +96,7 @@ import org.onap.portalsdk.analytics.model.runtime.ReportFormFields; import org.onap.portalsdk.analytics.model.runtime.ReportRuntime; import org.onap.portalsdk.analytics.system.AppUtils; import org.onap.portalsdk.analytics.system.ConnectionUtils; +import org.onap.portalsdk.analytics.system.DbUtils; import org.onap.portalsdk.analytics.system.Globals; import org.onap.portalsdk.analytics.util.AppConstants; import org.onap.portalsdk.analytics.util.DataSet; @@ -711,6 +712,24 @@ public class RaptorControllerAsync extends RestrictedBaseController { // } if (rdef != null) { + String dbInfo = definitionJSON.getDbInfo(); + rdef.setDBInfo(dbInfo); + //save dbType + String schemaSql = Globals.getRemoteDbSchemaSqlWithWhereClause(); + schemaSql = schemaSql.replace("[schema_id]", dbInfo); + String dbType = null; + DataSet ds = null; + try { + ds = DbUtils.executeQuery(schemaSql); + + String prefix = "", desc = ""; + + for (int i = 0; i < ds.getRowCount(); i++) { + dbType = ds.getItem(i, 2); + } + } + catch (Exception e) {} + rdef.setDBType(dbType); String reportName = definitionJSON.getReportName(); String errorString = ""; if (AppUtils.nvl(reportName).length() <= 0) @@ -1358,6 +1377,7 @@ public class RaptorControllerAsync extends RestrictedBaseController { if (pathVariables.containsKey("id")) { id = pathVariables.get("id"); } + String detailId = ""; if (pathVariables.containsKey("detailId")) { detailId = pathVariables.get("detailId"); @@ -1467,6 +1487,26 @@ public class RaptorControllerAsync extends RestrictedBaseController { wizardJSON.setReportTitle((rdef != null) ? rdef.getReportTitle() : ""); wizardJSON.setReportSubTitle((rdef != null) ? rdef.getReportSubTitle() : ""); + + String schemaSql = Globals.getRemoteDbSchemaSql(); + DataSet ds = null; + ArrayList dbInfoList = new ArrayList(); + try { + ds = DbUtils.executeQuery(schemaSql); + + String prefix = "", desc = ""; + + for (int i = 0; i < ds.getRowCount(); i++) { + IdNameBooleanJSON dBNameJSON = new IdNameBooleanJSON(); + dBNameJSON.setId(ds.getItem(i, 0)); + dBNameJSON.setName(ds.getItem(i, 0)); + dBNameJSON.setSelected(false); + dbInfoList.add(dBNameJSON); + } + } + catch (Exception e) {} + wizardJSON.setDbInfoList(dbInfoList); + /*Robert add*/ wizardJSON.setOneTimeRec((rdef != null) ? rdef.getIsOneTimeScheduleAllowed() : "false"); wizardJSON.setHourlyRec((rdef != null) ? rdef.getIsHourlyScheduleAllowed() : "false"); @@ -1572,7 +1612,7 @@ public class RaptorControllerAsync extends RestrictedBaseController { try { response.setContentType("application/json"); - ds = ConnectionUtils.getDataSet(sql, "local", true); + ds = ConnectionUtils.getDataSet(sql, rdef.getDBInfo(), true); QueryResultJSON queryResultJSON = new QueryResultJSON(); queryResultJSON.setQuery(queryJSON.getQuery()); diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/util/RemDbInfo.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/util/RemDbInfo.java index 0b9e08c7..57c1e31d 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/util/RemDbInfo.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/util/RemDbInfo.java @@ -37,24 +37,40 @@ */ package org.onap.portalsdk.analytics.util; +import java.sql.Connection; import java.util.HashMap; +import javax.servlet.ServletContext; + import org.onap.portalsdk.analytics.system.DbUtils; import org.onap.portalsdk.analytics.system.Globals; +import org.onap.portalsdk.analytics.system.fusion.adapter.RaptorAdapter; +import org.onap.portalsdk.analytics.system.fusion.adapter.SpringContext; + +import com.mchange.v2.c3p0.ComboPooledDataSource; public class RemDbInfo { private HashMap remDbMap = null; private HashMap remDbTypeMap = null; + private HashMap remDbConnectionMap = null; public RemDbInfo() throws Exception { if (remDbMap == null) { load(); } } + + private RaptorAdapter raptorAdapter; + + + public void initializeDbUtils(ServletContext servletContext) { + raptorAdapter = (RaptorAdapter)SpringContext.getApplicationContext().getBean("raptorAdapter"); + } // initializeDbUtils public void load() throws Exception { remDbMap = new HashMap(); remDbTypeMap = new HashMap(); + remDbConnectionMap = new HashMap(); try { //String query = " SELECT a.SCHEMA_ID, a.SCHEMA_DESC, DATASOURCE_TYPE, rownum id FROM SCHEMA_INFO a " + // " where schema_id = 'local' union " + @@ -65,20 +81,35 @@ public class RemDbInfo { Globals.getDbUtils(); ds = DbUtils.executeQuery(query); - String prefix = "", desc = "", dbType = ""; + String prefix = "", desc = "", dbType = "", connectionUrl = "", username = "", password = "", driver_class = ""; if(ds.getRowCount() > 0) { for (int i = 0; i < ds.getRowCount(); i++) { - prefix = ds.getItem(i, 0); - desc = ds.getItem(i, 1); - dbType = ds.getItem(i, 2); + prefix = ds.getItem(i, 0); + desc = ds.getItem(i, 1); + dbType = ds.getItem(i, 2); + connectionUrl = ds.getItem(i, 3); + username = ds.getItem(i, 4); + password = ds.getItem(i, 5); + driver_class = ds.getItem(i, 6); + + ComboPooledDataSource cpds = new ComboPooledDataSource(); + cpds.setDriverClass( driver_class ); //loads the jdbc driver + cpds.setJdbcUrl( connectionUrl); + cpds.setUser(username); + cpds.setPassword(password); + + + remDbMap.put(prefix, desc); remDbTypeMap.put(prefix, dbType); + remDbConnectionMap.put(prefix, cpds); } } else { remDbMap.put("local", "local"); remDbTypeMap.put("local", Globals.getDBType()); + remDbConnectionMap.put("local", raptorAdapter.getConnection()); } } catch (Exception e) {} @@ -100,6 +131,19 @@ public class RemDbInfo { return ""; } + + public Connection getDBConnection(String prefix) { + if ((remDbConnectionMap != null) && (remDbConnectionMap.containsKey(prefix))) { + try { + return ((ComboPooledDataSource) remDbConnectionMap.get(prefix)).getConnection(); + } catch (Exception ex) { + ex.printStackTrace(); + return null; + } + } + + return null; + } public HashMap getDbHash() { return remDbMap; -- cgit 1.2.3-korg