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/base/IdNameSql.java | 400 +++++++++++++++++++++ 1 file changed, 400 insertions(+) create mode 100644 ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/base/IdNameSql.java (limited to 'ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/base/IdNameSql.java') diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/base/IdNameSql.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/base/IdNameSql.java new file mode 100644 index 00000000..602025fe --- /dev/null +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/model/base/IdNameSql.java @@ -0,0 +1,400 @@ +/*- + * ================================================================================ + * 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 + * =========================================================================================== + * + * ------------------------------------------------------------------------------------------- + * IdNameSql.java - This class is used to generate form field items when sql is provided. + * ------------------------------------------------------------------------------------------- + * + * Created By : Stan Pishamanov + * Modified By: Sundar Ramalingam + * + * Changes + * ------- + * 08-Jun-2009 : Version 8.3 (RS); Rownum references is avoided for reports connnecting to Daytona + * Database. + * + */ +package org.openecomp.portalsdk.analytics.model.base; + +import java.util.*; + +import org.openecomp.portalsdk.analytics.controller.ActionHandler; +import org.openecomp.portalsdk.analytics.error.RaptorException; +import org.openecomp.portalsdk.analytics.model.*; +import org.openecomp.portalsdk.analytics.model.runtime.*; +import org.openecomp.portalsdk.analytics.system.*; +import org.openecomp.portalsdk.analytics.util.*; +import org.openecomp.portalsdk.core.logging.logic.EELFLoggerDelegate; + +public class IdNameSql extends IdNameList { + + EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(IdNameSql.class); + + + protected int dataSize = -1; + + protected int dataSizeUsedInPopup = -1; + + private String sql = null; + + private String oldSql = null; + + private String defaultSQL = null; + + private String sqlNoOrderBy = null; + + public IdNameSql(int pageNo, String sql, String defaultSQL) { + this(sql,defaultSQL); + this.pageNo = pageNo; + } // IdNameSql + + public IdNameSql(String sql) { + this(); + setSql(sql); + } // IdNameSql + + public IdNameSql(String sql, String defaultSQL) { + this(); + setDefaultSQL(defaultSQL); + setSql(sql); + } // IdNameSql + + protected IdNameSql() { + super(); + } // IdNameSql + + public boolean canUseSearchString() { + return true; + } + + public String getSql() { + return sql; + } + + public String getOldSql() { + return oldSql; + } + + public String getBaseSQL() { + return "SELECT id FROM (" + sql + ") xid"; + } + + public String getBaseWholeSQL() { + return "SELECT id, name FROM (" + sql + ") xid"; + } + + public String getBaseWholeReadonlySQL() { + return "SELECT id, name, ff_readonly FROM (" + sql + ") xid"; + } + + public String getBaseSQLForPDFExcel(boolean multiParam) { + if(!multiParam) + return "SELECT id, name FROM (" + sql + ") xid where id = '[VALUE]'"; + else + return "SELECT id, name FROM (" + sql + ") xid where id in [VALUE]"; + + } + + // public String getSqlNoOrderBy() { return sqlNoOrderBy; } + + protected void setSql(String sql) { + this.sql = sql; + } + + public void setOldSql(String oldSql) { + this.oldSql = oldSql; + } + + protected void setSqlNoOrderBy(String sql) { + this.sqlNoOrderBy = sql; + } + + public int getDataSize() { + return dataSize; + } // getDataSize + + public int getDataSizeUsedinPopup() { + return dataSizeUsedInPopup; + } // getDataSizeUsedinPopup + + public void setDataSizeUsedinPopup(int dataSizePop) { + this.dataSizeUsedInPopup = dataSizePop; + } // getDataSizeUsedinPopup + + public void clearData() { + removeAllElements(); + } // clearData + +/* public void loadData(String pageNo, String searchString, String dbInfo, String userId) throws RaptorException { + // setSql(searchString); + loadUserData(pageNo, searchString, dbInfo,userId); + } // loadData +*/ + + public void loadUserData(String pageNo, String searchString, String dbInfo,String userId) throws RaptorException { + int iPageNo = 0; + + if (pageNo != null) + try { + iPageNo = Integer.parseInt(pageNo); + } catch (NumberFormatException e) { + } + + loadUserData(iPageNo, searchString, dbInfo,userId); + } // loadData + + public void loadUserData(int pageNo, String searchString, String dbInfo, String userId) throws RaptorException { + if(userId!=null) { + String sql = Utils.replaceInString(getSql(), "[LOGGED_USERID]", userId); + //String defaultSQL = ""; + if(defaultSQL!=null && (defaultSQL.trim().toLowerCase().startsWith("select")) ) { + defaultSQL = Utils.replaceInString(getDefaultSQL(), "[LOGGED_USERID]", userId); + setDefaultSQL(defaultSQL); + } + setSql(sql); + + } + loadData(searchString,pageNo, dbInfo); + } + + public void loadData(String searchString, int pageNo, String dbInfo) throws RaptorException { + + //boolean dataAlreadyLoaded = (this.pageNo == pageNo); + + //if (dataAlreadyLoaded) + // return; + + this.pageNo = pageNo; + + performLoadData(searchString, dbInfo); + } // loadData + + protected void performLoadData(String searchString, String dbInfo) throws RaptorException { + long currentTime = System.currentTimeMillis(); + int startRow = 0; + int endRow = dataSize; + String readOnlyInSql = "ff_readonly"; + String dbType = Globals.getDBType(); + if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) { + try { + org.openecomp.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.openecomp.portalsdk.analytics.util.RemDbInfo(); + dbType = remDbInfo.getDBType(dbInfo); + } catch (Exception ex) { + throw new RaptorException(ex); + } + } + if (pageNo >= 0) { + startRow = pageNo * pageSize; + endRow = startRow + pageSize; + } // if + DataSet ds = null; + DataSet dsDefault = null; + StringBuffer query = new StringBuffer(""); + StringBuffer queryPop = new StringBuffer(""); + String sql = getSql(); + boolean avail_ReadOnly = (sql.toLowerCase().indexOf(readOnlyInSql)!=-1); + + if (dbType.equals("DAYTONA") && getSql().trim().toUpperCase().startsWith("SELECT")) { + query.append(getSql()); + } else { + if(avail_ReadOnly) // need to add readonlyinsql + if(!(Globals.isMySQL() && dbType.equals(AppConstants.MYSQL))) + query.append("SELECT rownum, id, name, " + readOnlyInSql +" FROM ("+ Globals.getReportSqlForFormfield() +", " + readOnlyInSql + " FROM (" + sql + + ") x "+ Globals.getReportSqlForFormfieldSuffix()); + else + query.append("SELECT id, name, " + readOnlyInSql +" FROM ("+ Globals.getReportSqlForFormfield() +", " + readOnlyInSql + " FROM (" + sql + + ") x "+ Globals.getReportSqlForFormfieldSuffix()); + else + query.append(Globals.getReportSqlForFormfieldPrefix()+ Globals.getReportSqlForFormfield() +" FROM (" + sql + + ") x " + Globals.getReportSqlForFormfieldSuffix()); + if(pageNo!= -2 && (dbType.equals(AppConstants.ORACLE)) ) { + query.append(" WHERE rownum <= " + ((dataSize < 0) ? (endRow + 1) : endRow)); + } else if(pageNo!=2 && (dbType.equals(AppConstants.POSTGRESQL))) { + query.append(" LIMIT " + ((dataSize < 0) ? (endRow + 1) : endRow)); + + } else if(pageNo!=2 && (dbType.equals(AppConstants.MYSQL))) { + query.append(" LIMIT " + startRow); //((dataSize < 0) ? (endRow + 1) : endRow) + + } + if(searchString!=null && searchString.length()>0 && !searchString.equals("%")) { + if(pageNo == -2) query.append(" WHERE "); + else query.append(" and "); + query.append("name like '"+ searchString +"'"); + } + if(dbType.equals(AppConstants.POSTGRESQL)) { + query.append(") xx OFFSET " + startRow); + } else if(dbType.equals(AppConstants.MYSQL)) { + query.append(" ," + ((dataSize < 0) ? (endRow + 1) : endRow) +") xx"); + } else if(dbType.equals(AppConstants.ORACLE)) + query.append(") xx WHERE rownum>" + startRow); + } + String defaultQuery =""; + boolean readOnly = true; + ds = ConnectionUtils.getDataSet(query.toString(), dbInfo); + + // if ( (dbInfo!=null) && (!dbInfo.equals(AppConstants.DB_LOCAL))) { + // Globals.getRDbUtils().setDBPrefix(dbInfo); + // ds = RemDbUtils.executeQuery(query); + // } + // else + // ds = DbUtils.executeQuery(query); + clearData(); + if (dbType.equals("DAYTONA") && (getDefaultSQL()!=null && getDefaultSQL().trim().toUpperCase().startsWith("SELECT"))) { + defaultQuery = getDefaultSQL(); + } else if (getDefaultSQL()!=null && getDefaultSQL().length()>10 && getDefaultSQL().substring(0,10).toLowerCase().startsWith("select")) { + defaultQuery = Globals.getReportSqlForFormfieldPrefix()+ Globals.getReportSqlForFormfield() +" FROM (" + getDefaultSQL() + + ") x " + + ") xx "; + logger.debug(EELFLoggerDelegate.debugLogger, ("Default Query " +defaultQuery)); + } + HashMap defaultMap = new HashMap(); + if(!isNull(defaultQuery)) { + dsDefault = ConnectionUtils.getDataSet(defaultQuery, dbInfo); + if(dsDefault!=null && dsDefault.getRowCount()>0) { + for (int i = 0; i < dsDefault.getRowCount(); i++) { + //addValue(dsDefault.getString(i, 0), dsDefault.getString(i, 1), true); + defaultMap.put(dsDefault.getString(i, "id"), dsDefault.getString(i, "name")); + } + } + } + + for (int i = 0; i < ((pageNo!=-2)?Math.min(ds.getRowCount(), pageSize):ds.getRowCount()); i++) { + //if(getCount()==0) + // addValue(ds.getString(i, 0), ds.getString(i, 1)); + if(i==0 && avail_ReadOnly) + readOnly = ds.getString(i, "ff_readonly").toUpperCase().startsWith("Y")||ds.getString(i, "ff_readonly").toUpperCase().startsWith("T"); + if(getCount()>=0) {//&& !((IdNameValue)getValue(0)).getId().equals(ds.getString(i, 0))) + if(defaultMap.get(ds.getString(i, "id")) == null) + if(avail_ReadOnly) + addValue(ds.getString(i, "id"), ds.getString(i, "name"), false, readOnly); + else + addValue(ds.getString(i, "id"), ds.getString(i, "name"), false); + else + if(avail_ReadOnly) + addValue(ds.getString(i, "id"), ds.getString(i, "name"), true, readOnly); + else + addValue(ds.getString(i, "id"), ds.getString(i, "name"), true); + } + } + + if (!(dbType.equals("DAYTONA"))) { + if (ds.getRowCount() <= pageSize) { + if(dsDefault!=null && dsDefault.getRowCount()>0) + dataSize = ds.getRowCount()+1; + else + dataSize = ds.getRowCount(); + + //System.out.println("IDNAME SQL COUNT");*/ + if(searchString!=null && searchString.length()>0 && !searchString.equals("%")) { + queryPop = new StringBuffer(""); + queryPop.append("SELECT count(*) num_rows FROM ("+ Globals.getReportSqlForFormfield() +", name FROM (" + sql + + ") x "); + if(searchString!=null && searchString.length()>0 && !searchString.equals("%")) + queryPop.append(" where name like '"+ searchString +"'"); + queryPop.append(") xx "); + + ds = ConnectionUtils.getDataSet(queryPop.toString(), dbInfo); + try { + dataSizeUsedInPopup = Integer.parseInt(ds.getString(0, 0)); + } catch (NumberFormatException e) { + } + } else if(dataSizeUsedInPopup == -3) { + queryPop = new StringBuffer(""); + //System.out.println("IDNAME SQL COUNT"); + //queryPop.append("SELECT count(*) num_rows FROM ("+query.toString()+") x"); + queryPop.append("SELECT count(*) num_rows FROM ("+ Globals.getReportSqlForFormfield() +", name FROM (" + sql + + ") x "); + queryPop.append(") xx "); + + ds = ConnectionUtils.getDataSet(queryPop.toString(), dbInfo); + // if ( (dbInfo!=null) && + // (!dbInfo.equals(AppConstants.DB_LOCAL))) { + // Globals.getRDbUtils().setDBPrefix(dbInfo); + // ds = RemDbUtils.executeQuery(query); + // } + // else + // ds = DbUtils.executeQuery(query); + // + try { + dataSizeUsedInPopup = Integer.parseInt(ds.getString(0, 0)); + } catch (NumberFormatException e) { + } + + } + + } else { + //pageNo = 0; + if(pageNo!= -2) { + queryPop = new StringBuffer(""); + //System.out.println("IDNAME SQL COUNT"); + //queryPop.append("SELECT count(*) num_rows FROM ("+query.toString()+") x"); + queryPop.append("SELECT count(*) num_rows FROM ("+ Globals.getReportSqlForFormfield() +" FROM (" + sql + + ") x "); + queryPop.append(") xx "); + + ds = ConnectionUtils.getDataSet(queryPop.toString(), dbInfo); + // if ( (dbInfo!=null) && + // (!dbInfo.equals(AppConstants.DB_LOCAL))) { + // Globals.getRDbUtils().setDBPrefix(dbInfo); + // ds = RemDbUtils.executeQuery(query); + // } + // else + // ds = DbUtils.executeQuery(query); + // + try { + dataSize = Integer.parseInt(ds.getString(0, 0)); + dataSizeUsedInPopup = Integer.parseInt(ds.getString(0, 0)); + } catch (NumberFormatException e) { + } + } + } // else + } // dataSize < 0 + long totalTime = System.currentTimeMillis() - currentTime; + logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] ------->Time Taken to the above formfield Query (+ count Query if any) --- " + totalTime)); + } // performLoadData + + + public String getDefaultSQL() { + + return defaultSQL; + } + + + public void setDefaultSQL(String defaultSQL) { + + this.defaultSQL = defaultSQL; + } + + public void setSQL(String sql_) + { + this.sql = sql_; + } + + public static boolean isNull(String a) { + if ((a == null) || (a.length() == 0) || a.equalsIgnoreCase("null")) + return true; + else + return false; + } +} // IdNameSql -- cgit 1.2.3-korg