/*
* ============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============================================
*
*
*/
package org.onap.portalsdk.analytics.model.runtime;
import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Enumeration;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.xml.datatype.XMLGregorianCalendar;
import org.apache.commons.lang.StringUtils;
import org.json.JSONObject;
import org.onap.portalsdk.analytics.error.RaptorException;
import org.onap.portalsdk.analytics.error.ReportSQLException;
import org.onap.portalsdk.analytics.model.DataCache;
import org.onap.portalsdk.analytics.model.ReportHandler;
import org.onap.portalsdk.analytics.model.ReportLoader;
import org.onap.portalsdk.analytics.model.base.IdNameList;
import org.onap.portalsdk.analytics.model.base.IdNameSql;
import org.onap.portalsdk.analytics.model.base.IdNameValue;
import org.onap.portalsdk.analytics.model.base.ReportWrapper;
import org.onap.portalsdk.analytics.model.definition.Marker;
import org.onap.portalsdk.analytics.model.definition.ReportDefinition;
import org.onap.portalsdk.analytics.model.definition.wizard.QueryResultJSON;
import org.onap.portalsdk.analytics.model.definition.wizard.RaptorResponse;
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.CachingUtils;
import org.onap.portalsdk.analytics.util.DataSet;
import org.onap.portalsdk.analytics.util.RemDbInfo;
import org.onap.portalsdk.analytics.util.Utils;
import org.onap.portalsdk.analytics.util.XSSFilter;
import org.onap.portalsdk.analytics.view.ColumnHeader;
import org.onap.portalsdk.analytics.view.ColumnHeaderRow;
import org.onap.portalsdk.analytics.view.ColumnVisual;
import org.onap.portalsdk.analytics.view.CrossTabOrderManager;
import org.onap.portalsdk.analytics.view.CrossTabTotalValue;
import org.onap.portalsdk.analytics.view.DataRow;
import org.onap.portalsdk.analytics.view.DataValue;
import org.onap.portalsdk.analytics.view.ReportData;
import org.onap.portalsdk.analytics.view.RowHeaderCol;
import org.onap.portalsdk.analytics.xmlobj.CustomReportType;
import org.onap.portalsdk.analytics.xmlobj.DataColumnType;
import org.onap.portalsdk.analytics.xmlobj.FormFieldType;
import org.onap.portalsdk.analytics.xmlobj.ObjectFactory;
import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;
import org.onap.portalsdk.core.objectcache.jcs.JCSCacheManager;
import org.onap.portalsdk.core.util.SecurityCodecUtil;
import org.owasp.esapi.ESAPI;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.databind.DeserializationFeature;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
/**
* This class is part of RAPTOR (Rapid Application Programming Tool for OLAP Reporting)
*
*
* --------------------------------------------------------------------------------------------------
* ReportRuntime.java - This class involves in running, downloading RAPTOR reports.
* --------------------------------------------------------------------------------------------------
*
*
* Change Log
*
* 27-Aug-2009 : Version 8.5 (Sundar); - Order by logic is restored for DAYTONA.
* 13-Aug-2009 : Version 8.5 (Sundar); - Removing order by logic is rollbacked.
* 22-Jun-2009 : Version 8.4 (Sundar); - Bug while parsing SQL for text download is fixed.
*
*/
@Component
public class ReportRuntime extends ReportWrapper implements Cloneable, Serializable {
private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportRuntime.class);
private int curSQLParsePos = 0;
private String xmlFileURL = null;
private String xmlFileName = null;
private String flatFileName = null;
private String excelPageFileName = null;
private int reportDataSize = -1;
private boolean displayChart = true;
private boolean displayForm = true;
private boolean displayContent = true;
private boolean reportRunLogged = false;
private DataSet chartDataCache = null;
private ReportData pageDataCache = null;
private int cachedPageNo = -1;
private String cachedSQL = null;
private String wholeSQL = null;
private String totalSql = null;
private boolean isSinglePageDownload = false;
private ReportParamValues reportParamValues = null;
private ReportParamValuesForPDFExcel reportParamValuesFPE = null;
private ReportFormFields reportFormFields = null;
private VisualManager visualManager = null;
private CrossTabOrderManager crossTabOrderManager = null;
private boolean displayColTotals = false;
private boolean displayRowTotals = false;
private DataRow colDataTotalsLinear = null;
private Vector colDataTotalsCrosstab = null;
private Vector rowDataTotalsCrosstab = null;
private String grandTotalCrosstab = null;
public static int DISPLAY_DATA_ONLY = 1;
public static int DISPLAY_CHART_ONLY = 2;
public static int DISPLAY_CHART_AND_DATA = 3;
public static final int DATE_OPTION_MONTHLY = 1;
public static final int DATE_OPTION_YEARLY = 2;
public static final int DATE_OPTION_DAILY = 3;
private int DISPLAY_MODE = 0;
private int DATE_OPTION = -1;
private boolean fromDashBoard = false;
public boolean isFromDashBoard() {
return fromDashBoard;
}
public void setFromDashBoard(boolean fromDashBoard) {
this.fromDashBoard = fromDashBoard;
}
public boolean isSinglePageDownload() {
return isSinglePageDownload;
}
public void setSinglePageDownload(boolean isSinglePageDownload) {
this.isSinglePageDownload = isSinglePageDownload;
}
public void setReportFormFields(ReportFormFields reportFormFields) {
this.reportFormFields = reportFormFields;
}
public ReportRuntime() {
super();
}
private ReportRuntime(CustomReportType crType, String reportID, HttpServletRequest request,
String ownerID, String createID, String createDate, String updateID,
String updateDate, String menuID, boolean menuApproved) throws RaptorException {
super(crType, reportID, ownerID, createID, createDate, updateID, updateDate, menuID,
menuApproved);
initializeReportRuntime(request);
}
public ReportRuntime(ReportWrapper rw) throws RaptorException {
this(rw, null);
}
public ReportRuntime(ReportWrapper rw, HttpServletRequest request)throws RaptorException {
super(rw);
initializeReportRuntime(request);
}
private void initializeReportRuntime(HttpServletRequest request) throws RaptorException {
reportFormFields = new ReportFormFields(this, request);
setParamValues(request, true, true);
visualManager = new VisualManager();
}
public static ReportRuntime unmarshal(String reportXML, String reportID)
throws RaptorException {
return unmarshal(reportXML, reportID, null);
}
public static ReportRuntime unmarshal(String reportXML, String reportID,
HttpServletRequest request) throws RaptorException {
CustomReportType crType = ReportWrapper.unmarshalCR(reportXML);
ObjectFactory objFactory = new ObjectFactory();
logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report [" + reportID + "]: XML unmarshalled"));
return new ReportRuntime(crType, reportID, request, null, null, null, null, null, null,
false);
}
public String getXmlFileURL() {
return xmlFileURL;
}
public String getXmlFileName() {
return xmlFileName;
}
public String getFlatFileName() {
return flatFileName;
}
public String getExcelPageFileName() {
return excelPageFileName;
}
public int getReportDataSize() {
return reportDataSize;
}
public boolean getDisplayChart() {
return displayChart;
}
public boolean getDisplayForm() {
return displayForm;
}
public boolean getDisplayContent() {
return displayContent;
}
public int getCachedPageNo() {
return cachedPageNo;
}
public String getCachedSQL() {
return cachedSQL;
}
public boolean isDashboardType() throws RaptorException {
return ReportLoader.isDashboardType(getReportID());
}
public void setXmlFileURL(String xmlFileURL) {
this.xmlFileURL = xmlFileURL;
}
public void setXmlFileName(String xmlFileName) {
this.xmlFileName = xmlFileName;
}
public void setFlatFileName(String flatFileName) {
this.flatFileName = flatFileName;
}
public void setExcelPageFileName(String excelPageFileName) {
this.excelPageFileName = excelPageFileName;
}
/*private*/ public void setReportDataSize(int reportDataSize) {
this.reportDataSize = reportDataSize;
}
private void setDisplayForm(boolean displayForm) {
this.displayForm = displayForm;
}
private void setDisplayContent(boolean displayContent) {
this.displayContent = displayContent;
}
public void setDisplayFlags(boolean isFirstAccess, boolean forceDisplayContent) {
if (isFirstAccess) {
setDisplayForm(true);
if (forceDisplayContent)
setDisplayContent(true);
else if (Globals.getDisplayFormBeforeRun())
if (needFormInput())
setDisplayContent(false);
else
setDisplayContent(true);
else
setDisplayContent(true);
} else {
setDisplayContent(true);
if (Globals.getIncludeFormWithData())
setDisplayForm(true);
else if (Globals.getDisplayFormBeforeRun())
setDisplayForm(false);
else
setDisplayForm(true);
}
}
public void logReportRun(String userID, String executionTime, String formFields) throws RaptorException {
if (reportRunLogged)
return;
ReportLoader.createReportLogEntry(null, reportID, userID, AppConstants.RLA_RUN,executionTime,formFields );
reportRunLogged = true;
}
public void logReportExecutionTime(String userId, String executionTime, String action, String formFields) throws RaptorException {
ReportLoader.createReportLogEntryForExecutionTime(null, reportID, userId,executionTime , action, formFields);
}
public void logReportExecutionTimeFromLogList (String userId, String executionTime, String formFields) throws RaptorException {
ReportLoader.createReportLogEntryForExecutionTime(null, reportID, userId,executionTime , AppConstants.RLA_FROM_LOG, formFields);
}
public void resetVisualSettings() {
boolean haveToResetCachedData = (visualManager.getSortByColId().length() > 0);
visualManager = new VisualManager();
if (haveToResetCachedData)
pageDataCache = null;
if (pageDataCache != null)
pageDataCache.resetVisualSettings();
}
/** ************** ReportParamValues processing *************** */
public boolean setParamValues(HttpServletRequest request, boolean resetParams, boolean refresh) throws RaptorException {
boolean paramsUpdated = false;
if (resetParams) {
reportFormFields = new ReportFormFields(this, request);
reportParamValues = new ReportParamValues(reportFormFields, getReportDefType());
// This is called even in the wizard page. Hence this condition.
if((ReportRuntime) request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME)!=null)
reportParamValuesFPE = new ReportParamValuesForPDFExcel(reportFormFields, getReportDefType());
paramsUpdated = true;
reportRunLogged = false;
} else if (request != null) {
paramsUpdated = reportParamValues.setParamValues(request,refresh);
}
if((ReportRuntime) request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME)!=null) {
if(reportParamValuesFPE!=null) reportParamValuesFPE.setParamValues(request,refresh);
else {
reportParamValuesFPE = new ReportParamValuesForPDFExcel(reportFormFields, getReportDefType());
reportParamValuesFPE.setParamValues(request,refresh);
}
} else {
reportFormFields = new ReportFormFields(this, request);
reportParamValues = new ReportParamValues(reportFormFields, getReportDefType());
reportParamValues.setParamValues(request,refresh);
reportParamValuesFPE = new ReportParamValuesForPDFExcel(reportFormFields, getReportDefType());
reportParamValuesFPE.setParamValues(request,refresh);
}
if (paramsUpdated) {
setReportDataSize(-1);
chartDataCache = null;
pageDataCache = null;
cachedPageNo = -1;
crossTabOrderManager = null;
colDataTotalsLinear = null;
colDataTotalsCrosstab = null;
rowDataTotalsCrosstab = null;
grandTotalCrosstab = null;
if(!refresh)
resetVisualSettings();
}
displayChart = (request.getParameter(AppConstants.RI_DISPLAY_CHART) == null) ? !isDisplayOptionHideChart() : request.getParameter("display_chart")
.equals("Y");
return paramsUpdated;
}
public String getParamValue(String key) {
return reportParamValues.getParamValue(key);
}
public String getParamDisplayValue(String key) {;
return reportParamValues.getParamDisplayValue(key);
}
public Enumeration getParamKeys() {
return reportParamValues.keys();
}
public Enumeration getParamKeysForPDFExcel() {
return reportParamValuesFPE.keys();
}
public String getParamValueForPDFExcel(String key) {
return reportParamValuesFPE.getParamValue(key);
}
public ArrayList getParamNameValuePairs() {
ArrayList paramList = new ArrayList(getReportFormFields().size());
for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
FormField ff = (FormField) iter.next();
paramList.add(new IdNameValue(ff.getFieldDisplayName(), reportParamValues
.getParamDisplayValue(ff.getFieldName())));
}
return paramList;
}
public ArrayList getParamNameValuePairsforPDFExcel(HttpServletRequest request, int type /*excel =1; pdf=2*/) {
javax.servlet.http.HttpSession session = request.getSession();
ArrayList paramList = new ArrayList(getReportFormFields().size());
if(session.getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO)!=null) {
paramList = (ArrayList) session.getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO);
if(paramList!=null && paramList.size()>0)
return paramList;
}
if ( reportParamValuesFPE == null) {
reportParamValuesFPE = new ReportParamValuesForPDFExcel(reportFormFields, getReportDefType());
reportParamValuesFPE.setParamValues(request,true);
}
String valueString = "";
for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
FormField ff = (FormField) iter.next();
if(ff.isVisible() && type == 1){
valueString = reportParamValuesFPE.getParamDisplayValue(ff.getFieldName());
} else if(ff.isVisible() && type != 1) {
valueString = reportParamValuesFPE.getParamDisplayValue(ff.getFieldName());
}
if(valueString.equalsIgnoreCase("NULL"))
valueString="";
paramList.add(new IdNameValue(ff.getFieldDisplayName(), valueString));
}
String pdfAttachmentKey = AppUtils.getRequestValue(request, "pdfAttachmentKey");
boolean isSchedule = false;
if(pdfAttachmentKey != null)
isSchedule = true;
if(Globals.customizeFormFieldInfo() && type == 2) {
String[] sessionParameters = Globals.getSessionParams().split(",");
if(session != null && !isSchedule ) {
session.setAttribute("login_id", AppUtils.getUserBackdoorLoginId(request));
for (int i = 0; i < sessionParameters.length; i++) {
if(session.getAttribute(sessionParameters[i])!=null)
paramList.add(new IdNameValue(sessionParameters[i].toUpperCase(), (String)session.getAttribute(sessionParameters[i])));
}
}
if(isSchedule) {
String[] scheduleSessionParam = Globals.getSessionParamsForScheduling().split(",");
for (int i = 0; i < scheduleSessionParam.length; i++) {
if(request.getParameter(scheduleSessionParam[i])!=null)
paramList.add(new IdNameValue(ESAPI.encoder().canonicalize(scheduleSessionParam[i].toUpperCase()), ESAPI.encoder().canonicalize(request.getParameter(scheduleSessionParam[i]))));
}
}
try {
SimpleDateFormat oracleDateFormat = new SimpleDateFormat("MM/dd/yyyy kk:mm:ss");
Date sysdate = oracleDateFormat.parse(ReportLoader.getSystemDateTime());
SimpleDateFormat dtimestamp = new SimpleDateFormat(Globals.getScheduleDatePattern());
paramList.add(new IdNameValue("DATE", dtimestamp.format(sysdate)+" "+Globals.getTimeZone()));
} catch(Exception ex) {}
} else {
String[] sessionDisplayParameters = Globals.getDisplaySessionParamInPDFEXCEL().split(",");
if(session != null && !isSchedule ) {
session.setAttribute("login_id", AppUtils.getUserBackdoorLoginId(request));
for (int i = 0; i < sessionDisplayParameters.length; i++) {
String sessionDispParam = sessionDisplayParameters[i];
if(nvl(sessionDispParam).length()>0) {
String sessionDispParamArr[] = sessionDispParam.split(";");
paramList.add(new IdNameValue(sessionDispParamArr[1], nvl((String)session.getAttribute(sessionDispParamArr[0]),"")));
}
}
}
if(isSchedule) {
String[] scheduleSessionParam = Globals.getDisplayScheduleSessionParamInPDFEXCEL().split(",");
for (int i = 0; i < scheduleSessionParam.length; i++) {
String scheduleSessionDispParam = scheduleSessionParam[i];
if(nvl(scheduleSessionDispParam).length()>0) {
String scheduleSessionDispParamArr[] = scheduleSessionDispParam.split(";");
paramList.add(new IdNameValue(ESAPI.encoder().canonicalize(scheduleSessionDispParamArr[1]), ESAPI.encoder().canonicalize(nvl(request.getParameter(scheduleSessionDispParamArr[0]),""))));
}
}
}
try {
SimpleDateFormat oracleDateFormat = new SimpleDateFormat("MM/dd/yyyy kk:mm:ss");
Date sysdate = oracleDateFormat.parse(ReportLoader.getSystemDateTime());
SimpleDateFormat dtimestamp = new SimpleDateFormat(Globals.getScheduleDatePattern());
paramList.add(new IdNameValue("Report Date/Time", dtimestamp.format(sysdate)+" "+Globals.getTimeZone()));
} catch(Exception ex) {}
}
for (int i = 0; i < paramList.size(); i++) {
IdNameValue value = (IdNameValue) paramList.get(i);
String name = value.getName().replaceAll(",","~");
value.setName(name);
}
return paramList;
}
/** ************** ReportFormFields processing *************** */
public String getFormFieldComments(HttpServletRequest request) {
String comment = "";
StringBuffer st = new StringBuffer("");
if(getFormFieldList()!=null){
comment = nvl(getFormFieldList().getComment());
ArrayList al = null;
if(comment.length()>0) {
al = getParamNameValuePairsforPDFExcel(request, 2);
if(al!=null) {
for (int i=0; i < al.size(); i++) {
IdNameValue idNameValue = (IdNameValue)al.get(i);
if(nvl(idNameValue.getId()).equals("DATE"))
st.append("Date/Time Report Run:"+ idNameValue.getName() +"
");
}
for (int i=0; i < al.size(); i++) {
IdNameValue idNameValue = (IdNameValue)al.get(i);
comment = Utils.replaceInString(comment, "["+ idNameValue.getId()+"]", idNameValue.getName());
}
st.append(comment);
}
}
}
return st.toString();
}
public boolean needFormInput() {
return reportFormFields.getFieldCount() > 0;
}
public FormField getFormField(String fieldName) {
return reportFormFields.getFormField(fieldName);
}
public ReportFormFields getReportFormFields() {
return reportFormFields;
}
public void setChartDataCache(DataSet chartDataCache) {
this.chartDataCache = chartDataCache;
}
/** ************** Report Data processing *************** */
public DataSet loadChartData(String userId, HttpServletRequest request) throws RaptorException {
if (nvl(getChartType()).length() == 0)
return null;
if (!getDisplayChart())
return null;
DataSet ds = chartDataCache;
String sql = null;
if (ds == null) {
sql = generateChartSQL(reportParamValues, userId, request);
String dbInfo = getDBInfo();
ds = ConnectionUtils.getDataSet(sql, dbInfo);
if (Globals.getCacheChartData())
chartDataCache = ds;
}
return ds;
}
public String getReportDataSQL(String userId, int downloadLimit, HttpServletRequest request) throws RaptorException {
String reportSQL = "";
if(doesReportContainsGroupFormField()) {
reportSQL = generateSubsetSQL(0, downloadLimit,userId, request, true, reportParamValues);
} else
reportSQL = generateSubsetSQL(0, downloadLimit,userId, request, false, reportParamValues);
return reportSQL;
}
public ReportData loadReportData(int pageNo, String userId, int downloadLimit, HttpServletRequest request, boolean download) throws RaptorException {
ReportData rd = null;
boolean isGoBackAction = AppUtils.getRequestFlag(request, AppConstants.RI_GO_BACK);
boolean isPageDowload = Boolean.valueOf(request.getParameter(AppConstants.PAGE_DOWNLOAD));
if (pageNo >= 0)
if (pageNo == cachedPageNo && pageDataCache != null)
rd = pageDataCache;
if(isGoBackAction && rd!=null) return rd;
if(isPageDowload && pageDataCache != null) {
rd = pageDataCache;
return rd;
}
if (rd == null) {
if (getReportType().equals(AppConstants.RT_CROSSTAB))
rd = loadCrossTabReportData(pageNo, userId, downloadLimit, request, download);
else if (getReportType().equals(AppConstants.RT_LINEAR))
rd = loadLinearReportData(pageNo, userId, downloadLimit, request, download);
else
throw new RuntimeException(
"[ReportRuntime.loadReportData] Invalid report type");
if (pageNo >= 0)
if (Globals.getCacheCurPageData()) {
pageDataCache = rd;
cachedPageNo = pageNo;
}
}
return rd;
}
private ReportData loadCrossTabReportData(int pageNo, String userId, int downloadLimit, HttpServletRequest request, boolean download) throws RaptorException {
String reportSQL = generateSQL(reportParamValues, userId, request);
setWholeSQL(reportSQL);
cachedSQL = reportSQL;
wholeSQL = reportSQL;
String pagedSQL = "";
pagedSQL = generatePagedSQL(pageNo, userId, request, false, null);
List reportCols = getAllColumns();
DataColumnType dataColumnRequest = getCrossTabValueColumn();
reportSQL = parseReportSQLForDrillDownParams(reportSQL, dataColumnRequest, request);
DataSet ds = null;
String dbInfo = getDBInfo();
StringBuffer colNames = new StringBuffer();
StringBuffer colExtraIdNames = new StringBuffer();
StringBuffer colExtraDateNames = new StringBuffer();
for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
DataColumnType dc = (DataColumnType) iter.next();
if (colNames.length() > 0)
colNames.append(", ");
colNames.append(dc.getColId());
if (nvl(dc.getDrillDownURL()).length() > 0) {
System.out.println("Drilldown URL " + dc.getDrillDownURL());
}
}
if (reportSQL.toUpperCase().indexOf("GROUP BY ") < 0)
colNames.append(colExtraIdNames.toString());
colNames.append(colExtraDateNames.toString());
String rSQL = Globals.getLoadCrosstabReportData();
rSQL = rSQL.replace("[colNames.toString()]", colNames.toString());
rSQL = rSQL.replace("[reportSQL]", reportSQL);
reportSQL = rSQL;
setWholeSQL(reportSQL);
if (crossTabOrderManager == null)
crossTabOrderManager = new CrossTabOrderManager(this, userId,request);
ds = ConnectionUtils.getDataSet(reportSQL, dbInfo);
ReportData rd = new ReportData(pageNo, false);
ReportFormFields childReportFormFields = null;
if(doesReportContainsGroupFormField()) {
List reportCols1 = getAllColumns();
reportCols = new Vector();
outer:
for (Iterator iter = reportCols1.iterator(); iter.hasNext();) {
DataColumnType dct = (DataColumnType) iter.next();
for (int k=0; k 0) {
childReportFormFields = getChildReportFormFields(request,AppUtils.getDrillActionURL()+""+dct.getDrillDownURL());
}
if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_ROW)) {
RowHeaderCol rhc = new RowHeaderCol();
rhc.setColumnTitle(dct.getDisplayName());
if(nvl(dct.getDisplayWidthInPxls()).length()<=0) {
dct.setDisplayWidthInPxls("100px");
}
if(dct.getDisplayWidthInPxls().endsWith("px"))
rhc.setColumnWidth(dct.getDisplayWidthInPxls());
else
rhc.setColumnWidth(dct.getDisplayWidthInPxls()+"px");
rhc.setAlignment(dct.getDisplayAlignment());
rhc.setDisplayHeaderAlignment(dct.getDisplayHeaderAlignment());
rhc.setNowrap(nvl(dataColumn.getNowrap(),"null").equals("false")?"null":nvl(dataColumn.getNowrap(),"null"));
rd.reportRowHeaderCols.addRowHeaderCol(rhc);
} else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_COLUMN)) {
ColumnHeaderRow chr = new ColumnHeaderRow();
chr.setAlignment(dct.getDisplayHeaderAlignment());
chr.setRowHeight("15");
if(nvl(dct.getDisplayWidthInPxls()).length()<=0) {
dct.setDisplayWidthInPxls("80px");
}
if(dct.getDisplayWidthInPxls().endsWith("px"))
chr.setDisplayWidth(dct.getDisplayWidthInPxls());
else
chr.setDisplayWidth(dct.getDisplayWidthInPxls()+"px");
rd.reportColumnHeaderRows.addColumnHeaderRow(chr);
} else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_VALUE)) {
columnValue = dct.getColId();
} else {
if(!dct.getColId().toLowerCase().endsWith("_sort")) {
RowHeaderCol rhc = new RowHeaderCol();
rhc.setVisible(false);
rd.reportRowHeaderCols.addRowHeaderCol(rhc);
}
}
}
FormatProcessor formatProcessor = new FormatProcessor(getSemaphoreById(dataColumn
.getSemaphoreId()),
getReportDefType().equals(AppConstants.RD_SQL_BASED) ? AppConstants.CT_NUMBER
: dataColumn.getColType(), dataColumn.getColFormat(), false);
List dataList = new ArrayList();
for (int i = 0; i < ds.getRowCount(); i++) {
Vector rValues = new Vector();
Vector cValues = new Vector();
Vector cValuesSort = new Vector();
int colIdx = 0;
for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
try {
DataColumnType dct = (DataColumnType) iter.next();
if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_ROW))
rValues.add(addDataValue(dct, ds.getString(i, dct.getColId())));
if (nvl(dct.getCrossTabValue()).trim().length()<=0 && !dct.getColId().toLowerCase().endsWith("_sort"))
rValues.add(addDataValue(dct, ds.getString(i, dct.getColId())));
if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_COLUMN)) {
cValues.add(addDataValue(dct, ds.getString(i, dct.getColId())));
if(getColumnById(dct.getColId().toLowerCase()+"_sort")!=null)
cValuesSort.add(addDataValue(dct, new Integer(ds.getString(i, dct.getColId()+"_sort")).toString()));
}
if (dct.isVisible())
colIdx++;
} catch (ArrayIndexOutOfBoundsException ex ) {continue;}
catch (NumberFormatException ex1) {
ex1.printStackTrace();
continue;
}
} // for
DataValue dv = new DataValue();
dv.setDisplayValue(ds.getString(i, columnValue));
if (nvl(dataColumn.getCrossTabValue()).trim().length()<=0)
dv.setVisible(false);
dv.setAlignment(dataColumn.getDisplayAlignment());
dv.setDisplayTotal(dataColumn.getDisplayTotal());
dv.setColName(dataColumn.getColName());
dv.setDisplayName(dataColumn.getDisplayName());
dv.setColId(dataColumn.getColId());
dv.setNowrap(nvl(dataColumn.getNowrap(),"null").equals("false")?"null":nvl(dataColumn.getNowrap(),"null"));
if (nvl(dataColumn.getDrillDownURL()).length() > 0) {
if(dv.getDisplayValue().length() > 0) {
dv.setDrillDownURL(parseDrillDownURL(i, /* dataColumnIdx, */ds, dataColumn,request, childReportFormFields));
dv.setDrillDowninPoPUp(dataColumn.isDrillinPoPUp()!=null?dataColumn.isDrillinPoPUp():false);
}
if (dv.getDisplayValue().length() == 0) {
dv.setDisplayValue("");
}
}
rd.setDataValue(rValues, cValues, cValuesSort.size()==0?null:cValuesSort, dv, formatProcessor, crossTabOrderManager, dataList);
}
rd.setReportDataList(dataList);
if (colDataTotalsCrosstab == null)
colDataTotalsCrosstab = generateDataTotalsCrossTab(AppConstants.CV_COLUMN, userId,request);
if (displayColTotals && colDataTotalsCrosstab != null)
rd.setColumnDataTotalsCrossTab(colDataTotalsCrosstab, dataColumn
.getDisplayAlignment(), getCrossTabDisplayTotal(AppConstants.CV_COLUMN),
crossTabOrderManager, dataList);
if (rowDataTotalsCrosstab == null)
rowDataTotalsCrosstab = generateDataTotalsCrossTab(AppConstants.CV_ROW, userId, request);
if (displayRowTotals && rowDataTotalsCrosstab != null)
rd.setRowDataTotalsCrossTab(rowDataTotalsCrosstab, dataColumn
.getDisplayAlignment(), getCrossTabDisplayTotal(AppConstants.CV_ROW),
crossTabOrderManager, dataList);
if (displayColTotals
&& displayRowTotals
&& getCrossTabDisplayTotal(AppConstants.CV_COLUMN).equals(
getCrossTabDisplayTotal(AppConstants.CV_ROW))) {
// Display grand total
if (grandTotalCrosstab == null)
grandTotalCrosstab = ((CrossTabTotalValue) generateDataTotalsCrossTab("",
userId,request).get(0)).getTotalValue();
if (grandTotalCrosstab != null)
rd.setGrandTotalCrossTab(Utils.truncateTotalDecimals(grandTotalCrosstab),
dataColumn.getDisplayAlignment(),
getCrossTabDisplayTotal(AppConstants.CV_COLUMN), dataList);
}
rd.consolidateColumnHeaders(visualManager);
if (displayColTotals && colDataTotalsCrosstab != null) {
String totalLabel = "Total";
String colDisplayTotal = getCrossTabDisplayTotal(AppConstants.CV_COLUMN);
if (colDisplayTotal.length() > 0
&& (!colDisplayTotal.equals(AppConstants.TOTAL_SUM_ID)))
totalLabel = nvl(AppConstants.TOTAL_FUNCTIONS.getNameById(colDisplayTotal));
if (getReportDataSize() > getPageSize())
totalLabel += "_nl_(for all pages)";
}
DataRow drInFor1 = null;
Vector v1= null, v2 = null;
ArrayList temp = new ArrayList<>();
if (Globals.getMergeCrosstabRowHeadings()) {
for (int i = 0; i < dataList.size(); i++) {
drInFor1 = (DataRow)dataList.get(i);
drInFor1.setRowNum(i+1);
v1 = drInFor1.getRowValues();
if(i0) { // another ArrayList
temp = new ArrayList();
temp.add(v1.get(j).getDisplayValue());
}
if(v2!=null && temp.get(j).equals(v2.get(j).getDisplayValue())) {
v2.get(j).setDisplayValue("");
}
}
}
}
}
rd.setReportDataList(dataList);
if (getReportDataSize() < 0) {
setReportDataSize(rd.getReportDataList().size());
}
return rd;
}
public DataValue addDataValue(DataColumnType dataColumn, String columnValue) {
DataValue dv = new DataValue();
dv.setDisplayValue(columnValue);
if (nvl(dataColumn.getCrossTabValue()).trim().length()<=0)
dv.setVisible(false);
dv.setAlignment(dataColumn.getDisplayAlignment());
dv.setDisplayTotal(dataColumn.getDisplayTotal());
dv.setColName(dataColumn.getColName());
dv.setDisplayName(dataColumn.getDisplayName());
dv.setColId(dataColumn.getColId());
dv.setNowrap(nvl(dataColumn.getNowrap(),"null").equals("false")?"null":nvl(dataColumn.getNowrap(),"null"));
return dv;
}
public boolean doesReportContainsGroupFormField() {
int flag = 0;
if(getFormFieldList()!=null) {
for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
FormFieldType fft = (FormFieldType) iter.next();
if(fft.isGroupFormField()!=null && fft.isGroupFormField().booleanValue()) {
flag = 1;
break;
}
}
}
return (flag ==1);
}
private ReportData loadLinearReportData(int pageNo, String userId, int downloadLimit, HttpServletRequest request, boolean download) throws RaptorException {
long startTime = System.currentTimeMillis();
logger.info("Session ID: " + request.getSession().getId());
boolean isSameSession = false;
boolean isChildReport = false;
if(request.getParameter(AppConstants.CHILD_REPORT) != null) {
isChildReport = Boolean.valueOf(request.getParameter(AppConstants.CHILD_REPORT));
}
//setFromDashBoard(isChildReport);
String action = nvl(request.getParameter(AppConstants.RI_ACTION), request.getParameter("action"));;
String reportSQL = "";
if(action.endsWith("session")) {
reportSQL = getWholeSQL();
wholeSQL = reportSQL;
} else {
reportSQL = generateSQL(reportParamValues, visualManager.getSortByColId(),
visualManager.getSortByAscDesc(), userId, request);
wholeSQL = reportSQL;
setWholeSQL(wholeSQL);
}
DataSet ds = null;
String dbInfo = getDBInfo();
long runWHoleSqlStart = System.currentTimeMillis();
String SQLForCount = "SELECT count(*) FROM (" + reportSQL + ")"+ (Globals.isPostgreSQL() || Globals.isMySQL()?" AS ":"") +" x ";
boolean isPageDowload = Boolean.valueOf(request.getParameter(AppConstants.PAGE_DOWNLOAD));
if(download) {
if(isPageDowload) {
ds = ConnectionUtils.getDataSet(cachedSQL, dbInfo);
}
else {
String downloadAll = generateSubsetSQL(0, downloadLimit, userId, request, true, null);
ds = ConnectionUtils.getDataSet(downloadAll, dbInfo);
}
}
long runWHoleSqlEnd = System.currentTimeMillis();
logger.info("Run whole sql to set reportDataSize took:"+ (runWHoleSqlEnd - runWHoleSqlStart)/1000F + "Seconds");
long hibernateSessionTimeStart = System.currentTimeMillis();
HttpSession session = request.getSession();
long hibernateSessionTimeEnd = System.currentTimeMillis();
logger.info("Hibernate session time : " + (hibernateSessionTimeEnd - hibernateSessionTimeStart)/1000F + " Seconds");
List reportCols = null;
StringBuffer colNames = new StringBuffer();
if(download && action.endsWith("session")) {
reportCols = getAllColumns();
colNames = new StringBuffer();
for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
DataColumnType dc = (DataColumnType) iter.next();
if (colNames.length() > 0)
colNames.append(", ");
colNames.append(dc.getColId());
}
}
else {
String pagedSQL = null;
long genaratePageSqlStart = System.currentTimeMillis();
boolean getColumnNamesFromReportSQL = false;
if(Globals.getDBType().equals(AppConstants.ORACLE)) {
getColumnNamesFromReportSQL = true;
}
if (pageNo >= 0)
pagedSQL = generatePagedSQL(pageNo, userId, request, getColumnNamesFromReportSQL, null);
else
pagedSQL = generateSubsetSQL(0, downloadLimit, userId, request, getColumnNamesFromReportSQL, null);
long genaratePageSqlend = System.currentTimeMillis();
logger.info("generate paged sql took : " + (genaratePageSqlend - genaratePageSqlStart)/1000F + " Seconds");
reportCols = getAllColumns();
colNames = new StringBuffer();
for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
DataColumnType dc = (DataColumnType) iter.next();
if (colNames.length() > 0)
colNames.append(", ");
colNames.append(dc.getColId());
if (dc.isVisible()) {
reportSQL = parseReportSQLForDrillDownParams(reportSQL, dc, request);
pagedSQL = parseReportSQLForDrillDownParams(pagedSQL, dc, request);
}
}
cachedSQL = pagedSQL;
if(doesReportContainsGroupFormField()) {
if (pageNo >= 0)
pagedSQL = generatePagedSQL(pageNo, userId, request, getColumnNamesFromReportSQL, reportParamValues);
else
pagedSQL = generateSubsetSQL(0, downloadLimit, userId, request, getColumnNamesFromReportSQL, reportParamValues);
}
long start = System.currentTimeMillis();
ds = ConnectionUtils.getDataSet(pagedSQL, dbInfo);
long end = System.currentTimeMillis();
float sec = (end - start) / 1000F;
logger.info("Get page data : "+sec + " seconds");
if (ds.getRowCount() < getPageSize() && pageNo == 0) {
setReportDataSize(ds.getRowCount());
} else {
if (pageNo > 0 && CachingUtils.isTotalCountAvailable(request.getSession().getId() + wholeSQL) && !download) {
setReportDataSize(CachingUtils.getTotalRowsCount(request.getSession().getId() + wholeSQL));
} else {
DataSet countData = null;
if (!fromDashBoard) {
if(!isChildReport) {
countData = ConnectionUtils.getDataSet(SQLForCount, dbInfo);
setReportDataSize(countData.getInt(0, 0));
CachingUtils.cacheTotalRowCount(request.getSession().getId() + wholeSQL, countData.getInt(0, 0));
} else {
setReportDataSize(ds.getRowCount());
}
}
}
}
if(doesReportContainsGroupFormField()) {
List reportCols1 = getAllColumns();
reportCols = new Vector();
outer:
for (Iterator iter = reportCols1.iterator(); iter.hasNext();) {
DataColumnType dct = (DataColumnType) iter.next();
for (int k=0; k 0)
setReportDataSize(ds2.getInt(0, 0));
else
throw new RuntimeException(
"[ReportRuntime.loadLinearReportData] Unable to load report data size");
} else
setReportDataSize(50);
}
}
ReportData rd = new ReportData(pageNo, true);
Vector visibleCols = new Vector(reportCols.size());
Vector formatProcessors = new Vector(reportCols.size());
int count =0 ;
ReportFormFields rff = getReportFormFields();
ReportFormFields childReportFormFields = null;
String fieldDisplayName = "";
String fieldValue = "";
for (int c = 0; c < reportCols.size(); c++) {
if(reportCols.get(c)!=null) {
DataColumnType dct = (DataColumnType) reportCols.get(c);
if(nvl(dct.getDependsOnFormField()).length()>0 && nvl(dct.getDependsOnFormField()).indexOf("[")!=-1) {
for(int i = 0 ; i < rff.size(); i++) {
fieldDisplayName = "["+((FormField)rff.getFormField(i)).getFieldDisplayName()+"]";
fieldValue = "";
if (dct.getDependsOnFormField().equals(fieldDisplayName)) {
fieldValue = nvl(request.getParameter(((FormField)rff.getFormField(i)).getFieldName()));
if (fieldValue.length()>0) {
if(!fieldValue.toUpperCase().equals("Y"))
dct.setDisplayName(fieldValue);
if(!dct.isVisible())
dct.setVisible(true);
} else {
dct.setVisible(false);
}
}
}
}
}
}
String displayName = "";
for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
DataColumnType dc = (DataColumnType) iter.next();
formatProcessors.add(count,new FormatProcessor(
getSemaphoreById(dc.getSemaphoreId()), dc.getColType(), dc
.getColFormat(), getReportDefType().equals(
AppConstants.RD_SQL_BASED)));
if (nvl(dc.getDrillDownURL()).length() > 0) {
if(!dc.getDrillDownURL().contains("c_master")) {
childReportFormFields = getChildReportFormFields(request,AppUtils.getDrillActionURL()+""+dc.getDrillDownURL());
}else {
childReportFormFields = getChildReportFormFields(request,dc.getDrillDownURL());
}
}
if (dc.isVisible()) {
visibleCols.add(count,dc);
for (int d = 0; d < reportCols.size(); d++) {
if(reportCols.get(d)!=null) {
DataColumnType dct1 = (DataColumnType) reportCols.get(d);
if(dct1.getColId().equals(dc.getColId()+"_name") && ds.getRowCount()>0) {
displayName = ds.getString(0,dct1.getColId());
dc.setDisplayName(displayName);
}
}
}
//}
String widthInPxls = dc.getDisplayWidthInPxls();
if(nvl(widthInPxls).endsWith("px"))
dc.setDisplayWidthInPxls(widthInPxls);
else {
widthInPxls = widthInPxls+"px";
dc.setDisplayWidthInPxls(widthInPxls+"px");
}
rd.createColumn(dc.getColId(), dc.getDisplayName(), dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
visualManager.isColumnVisible(dc.getColId()), visualManager
.getSortByColId().equals(dc.getColId()) ? visualManager
.getSortByAscDesc() : null, isRuntimeColSortDisabled(), dc.getLevel()!=null?dc.getLevel():0, dc.getStart()!=null?dc.getStart():0, dc.getColspan()!=null?dc.getColspan():0, dc.isIsSortable()!=null?dc.isIsSortable():false);
}
else {
visibleCols.add(count,null);
rd.createColumn(dc.getColId(), AppConstants.HIDDEN, dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
true, null,false, dc.getLevel()!=null?dc.getLevel():0, dc.getStart()!=null?dc.getStart():0, dc.getColspan()!=null?dc.getColspan():0, dc.isIsSortable()!=null?dc.isIsSortable():false);
}
count++;
}
if(getReportDefType().equals(AppConstants.RD_SQL_BASED_DATAMIN) && pageNo ==0) {
}
for (int r = 0; r < Math.min(ds.getRowCount(), ((pageNo < 0) ? (downloadLimit == -1?Globals.getFlatFileUpperLimit():Globals.getDownloadLimit() ) : getPageSize())); r++) {
DataRow dr = new DataRow();
rd.reportDataRows.addDataRow(dr);
for (int c = 0; c < reportCols.size(); c++) {
if(reportCols.get(c)!=null) {
DataColumnType dct = (DataColumnType) reportCols.get(c);
DataValue dv = new DataValue();
dr.addDataValue(dv);
String reverseDispalyValue = Utils.removeLinkToForDownload(ds.getString(r, ds.getColumnIndex(dct.getColId())));
reverseDispalyValue = StringUtils.substringBefore(reverseDispalyValue, ",");
if(download && Utils.isRemoveLinkToEligible(ds.getString(r, ds.getColumnIndex(dct.getColId()))))
dv.setDisplayValue(reverseDispalyValue);
else
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"));
StringBuffer indentation = new StringBuffer("");
if(dct.getIndentation()!=null && dct.getIndentation()>0) {
for (int indent=0; indent< dct.getIndentation(); indent++) {
indentation.append("\t");
}
dv.setNowrap("true");
}
dv.setIndentation(indentation.toString());
if(dct.isVisible()) {
dv.setVisible(true);
dv.setAlignment(dct.getDisplayAlignment());
dv.setDisplayTotal(dct.getDisplayTotal());
dv.setDisplayName(dct.getDisplayName());
if (nvl(dct.getDrillDownURL()).length() > 0) {
if(dv.getDisplayValue().length() > 0) {
dv.setDrillDownURL(parseDrillDownURL(r, /* c, */ds, dct,request, childReportFormFields));
dv.setDrillDowninPoPUp(dct.isDrillinPoPUp()!=null?dct.isDrillinPoPUp():false);
}
if (dv.getDisplayValue().length() == 0) {
dv.setDisplayValue("");
}
} // if
} else {
dv.setVisible(false);
dv.setHidden(true);
}
if(dr.getFormatId()!=null)
((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, true);
else
((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, false);
}
}
}
if (colDataTotalsLinear == null) {
if(!download && !action.endsWith("session"))
colDataTotalsLinear = generateColumnDataTotalsLinear(new ArrayList(reportCols), userId,
getDbInfo(),request);
if(download && action.endsWith("session"))
colDataTotalsLinear = generateColumnDataTotalsLinear(new ArrayList(reportCols), userId,
getDbInfo(), getTotalSql());
}
if (displayColTotals && colDataTotalsLinear != null) {
String totalLabel = "Total";
if (getReportDataSize() > getPageSize())
totalLabel += "
(for all pages)";
rd.setColumnDataTotalsLinear(colDataTotalsLinear, totalLabel);
}
rd.applyVisibility();
long endTime = System.currentTimeMillis();
float totalTime = (endTime - startTime)/1000F;
logger.info("Linearreportdata took: " + totalTime +"Seconds");
return rd;
}
public DataRow generateColumnDataTotalsLinear(ArrayList reportCols, String userId,
String dbInfo, String reportSQL) throws RaptorException {
DataRow dr = null;
boolean displayColTotals = false;
for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
DataColumnType dct = (DataColumnType) iter.next();
if( dct != null ) {
if (nvl(dct.getDisplayTotal()).length() > 0) {
displayColTotals = true;
break;
}
}
}
DataSet ds = null;
if (displayColTotals) {
dr = new DataRow();
ds = ConnectionUtils.getDataSet(reportSQL,
dbInfo);
for (int c = 0; c < reportCols.size(); c++) {
DataColumnType dct = (DataColumnType) reportCols.get(c);
if ( dct != null ) {
DataValue dv = new DataValue();
String totalValue = "";
if (ds != null)
totalValue = ds.getString(0, c);
if (nvl(dct.getDisplayTotal()).length() > 0
&& (!dct.getDisplayTotal().equals(AppConstants.TOTAL_SUM_ID)))
totalValue = nvl(AppConstants.TOTAL_FUNCTIONS.getNameById(dct
.getDisplayTotal()))
+ ": " + totalValue;
dv.setDisplayValue(Utils.truncateTotalDecimals(totalValue));
dv.setAlignment(dct.getDisplayAlignment());
dv.setColName(dct.getColName());
dv.setDisplayName(dct.getDisplayName());
dv.setColId(dct.getColId());
dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
StringBuffer indentation = new StringBuffer("");
if(dct.getIndentation()!=null && dct.getIndentation()>0) {
for (int indent=0; indent< dct.getIndentation(); indent++) {
indentation.append("\t");
}
dv.setNowrap("true");
}
dv.setIndentation(indentation.toString());
dv.setDisplayTotal(dct.getDisplayTotal());
dv.setBold(true);
dv.setVisible(dct.isVisible());
if(dv.isVisible())
dr.addDataValue(dv);
}
}
}
return dr;
}
public ReportData loadHiveLinearReportData(String reportSQL, String userId, int downloadLimit, HttpServletRequest request) throws RaptorException {
wholeSQL = reportSQL;
int countRows = getHiveReportCount(wholeSQL);
setReportDataSize(countRows);
if(countRows < 1001)
wholeSQL += " limit "+ countRows;
else
wholeSQL += " limit "+ downloadLimit;
HttpSession session = request.getSession();
DataSet ds = null;
String dbInfo = getDBInfo();
List reportCols = getAllColumns();
StringBuffer colNames = new StringBuffer();
for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
DataColumnType dc = (DataColumnType) iter.next();
if (colNames.length() > 0)
colNames.append(", ");
colNames.append(dc.getColId());
}
ds = ConnectionUtils.getDataSet(wholeSQL, dbInfo);
ReportData rd = new ReportData(0, true);
Vector visibleCols = new Vector(reportCols.size());
Vector formatProcessors = new Vector(reportCols.size());
int count =0 ;
ReportFormFields rff = getReportFormFields();
ReportFormFields childReportFormFields = null;
String fieldDisplayName = "";
String fieldValue = "";
for (int c = 0; c < reportCols.size(); c++) {
if(reportCols.get(c)!=null) {
DataColumnType dct = (DataColumnType) reportCols.get(c);
if(nvl(dct.getDependsOnFormField()).length()>0 && nvl(dct.getDependsOnFormField()).indexOf("[")!=-1) {
for(int i = 0 ; i < rff.size(); i++) {
fieldDisplayName = "["+((FormField)rff.getFormField(i)).getFieldDisplayName()+"]";
fieldValue = "";
if (dct.getDependsOnFormField().equals(fieldDisplayName)) {
fieldValue = nvl(request.getParameter(((FormField)rff.getFormField(i)).getFieldName()));
if (fieldValue.length()>0) {
if(!fieldValue.toUpperCase().equals("Y"))
dct.setDisplayName(fieldValue);
if(!dct.isVisible())
dct.setVisible(true);
} else {
dct.setVisible(false);
}
}
}
}
}
}
String displayName = "";
for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
DataColumnType dc = (DataColumnType) iter.next();
formatProcessors.add(count,new FormatProcessor(
getSemaphoreById(dc.getSemaphoreId()), dc.getColType(), dc
.getColFormat(), getReportDefType().equals(
AppConstants.RD_SQL_BASED)));
if (nvl(dc.getDrillDownURL()).length() > 0) {
childReportFormFields = getChildReportFormFields(request,dc.getDrillDownURL());
}
if (dc.isVisible()) {
visibleCols.add(count,dc);
for (int d = 0; d < reportCols.size(); d++) {
if(reportCols.get(d)!=null) {
DataColumnType dct1 = (DataColumnType) reportCols.get(d);
if(dct1.getColId().equals(dc.getColId()+"_name") && ds.getRowCount()>0) {
displayName = ds.getString(0,dct1.getColId());
dc.setDisplayName(displayName);
}
}
}
//}
String widthInPxls = dc.getDisplayWidthInPxls();
if(nvl(widthInPxls).endsWith("px"))
dc.setDisplayWidthInPxls(widthInPxls);
else {
widthInPxls = widthInPxls+"px";
dc.setDisplayWidthInPxls(widthInPxls+"px");
}
rd.createColumn(dc.getColId(), dc.getDisplayName(), dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
visualManager.isColumnVisible(dc.getColId()), visualManager
.getSortByColId().equals(dc.getColId()) ? visualManager
.getSortByAscDesc() : null, isRuntimeColSortDisabled(), dc.getLevel()!=null?dc.getLevel():0, dc.getStart()!=null?dc.getStart():0, dc.getColspan()!=null?dc.getColspan():0, dc.isIsSortable()!=null?dc.isIsSortable():false);
}
else {
visibleCols.add(count,null);
rd.createColumn(dc.getColId(), "", dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
true, null,false, dc.getLevel()!=null?dc.getLevel():0, dc.getStart()!=null?dc.getStart():0, dc.getColspan()!=null?dc.getColspan():0, dc.isIsSortable()!=null?dc.isIsSortable():false);
}
count++;
}
ArrayList reportDataList = new ArrayList();
for (int r = 0; r < ds.getRowCount(); r++) {
DataRow dr = new DataRow();
rd.reportDataRows.addDataRow(dr);
for (int c = 0; c < reportCols.size(); c++) {
if(reportCols.get(c)!=null) {
DataColumnType dct = (DataColumnType) reportCols.get(c);
DataValue dv = new DataValue();
dr.addDataValue(dv);
dv.setDisplayValue(ds.getString(r, c));
dv.setColName(dct.getColName());
dv.setColId(dct.getColId());
dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
StringBuffer indentation = new StringBuffer("");
if(dct.getIndentation()!=null && dct.getIndentation()>0) {
for (int indent=0; indent< dct.getIndentation(); indent++) {
indentation.append("\t");
}
dv.setNowrap("true");
}
dv.setIndentation(indentation.toString());
if(dct.isVisible()) {
dv.setVisible(true);
dv.setAlignment(dct.getDisplayAlignment());
dv.setDisplayTotal(dct.getDisplayTotal());
dv.setDisplayName(dct.getDisplayName());
if (nvl(dct.getDrillDownURL()).length() > 0) {
if(dv.getDisplayValue().length() > 0) {
dv.setDrillDownURL(parseDrillDownURL(r, /* c, */ds, dct,request, childReportFormFields));
dv.setDrillDowninPoPUp(dct.isDrillinPoPUp()!=null?dct.isDrillinPoPUp():false);
}
if (dv.getDisplayValue().length() == 0) {
dv.setDisplayValue("");
}
}
} else {
dv.setVisible(false);
dv.setHidden(true);
}
if(dr.getFormatId()!=null)
((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, true);
else
((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, false);
}
}
reportDataList.add(dr);
}
rd.setReportDataList(reportDataList);
if (colDataTotalsLinear == null)
colDataTotalsLinear = generateColumnDataTotalsLinear(new ArrayList(reportCols), userId,
getDbInfo(),request);
if (displayColTotals && colDataTotalsLinear != null) {
String totalLabel = "Total";
if (getReportDataSize() > getPageSize())
totalLabel += "
(for all pages)";
rd.setColumnDataTotalsLinear(colDataTotalsLinear, totalLabel);
}
rd.applyVisibility();
return rd;
}
public int getHiveReportCount(String sql) throws RaptorException {
int count = 0;
String countSql = "select t from (select count(*) t from ("+ sql + ")" + (Globals.isPostgreSQL() || Globals.isMySQL() ?" AS ":"") + " x) AS x1";
DataSet ds = null;
String dbInfo = getDBInfo();
System.out.println("SQL getReportCount()- " + countSql);
try {
ds = ConnectionUtils.getDataSet(countSql, dbInfo);
int totalRows = 0;
String dbType = "";
if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
try {
dbType = remDbInfo.getDBType(dbInfo);
} catch (Exception ex) {
throw new RaptorException(ex);
}
}
if( ds.getRowCount()>0) {
count = Integer.parseInt(ds.getString(0,0));
}
} catch (NumberFormatException ex) {}
return count;
}
public ReportFormFields getChildReportFormFields( HttpServletRequest request, String URL ) throws RaptorException {
String childReportID = getReportID(URL);
ReportRuntime ddRr = (new ReportHandler()).loadReportRuntime(request, childReportID,
false, 1);
ReportFormFields ddReportFormFields = ddRr.getReportFormFields();
return ddReportFormFields;
}
private String getReportID(String URL) {
URL = nvl(URL);
int pos = URL.toLowerCase().indexOf("c_master=")+9;
String reportID = "";
if(URL.toLowerCase().indexOf("&", pos)!=-1)
reportID = URL.substring(pos, URL.toLowerCase().indexOf("&", pos));
else
reportID = URL.substring(pos);
return reportID;
}
public String parseDrillDownURL(int rowIdx, /* int colIdx, */DataSet ds, DataColumnType dct, HttpServletRequest request, ReportFormFields ddReportFormFields)
throws RaptorException {
Vector viewActions = DataCache.getDataViewActions();
javax.servlet.http.HttpSession session = request.getSession();
StringBuffer dUrl = new StringBuffer();
boolean isViewAction = false;
int flag = 0;
String requestParam ="";
for (int k = 0; k < viewActions.size(); k++)
if (dct.getDrillDownURL().equals(
AppUtils.getBaseActionURL() + ((String) viewActions.get(k))))
isViewAction = true;
if (isViewAction) {
String param = nvl(dct.getDrillDownParams());
param = param.substring(AppUtils.getBaseActionParam().length() + 1,
param.length() - 1);
param = param.replace('.', '_');
dUrl.append(AppUtils.getBaseActionParam());
dUrl.append(java.net.URLEncoder.encode(ds.getString(rowIdx, param.toLowerCase())));
} else {
String param = nvl(dct.getDrillDownParams());
while (param.indexOf('[') >= 0) {
int startIdx = param.indexOf('[');
int endIdx = param.indexOf(']');
StringBuffer sb = new StringBuffer();
if(startIdx>endIdx) {
if (endIdx < param.length() - 1)
sb.append(param.substring(endIdx + 1));
param = sb.toString();
continue;
}
if (startIdx > 0)
sb.append(param.substring(0, startIdx));
if (param.charAt(startIdx + 1) == '!') {
String fieldId = param.substring(startIdx + 2, endIdx);
String fieldValue = (String) reportParamValues.get(fieldId);
sb.append(java.net.URLEncoder.encode(nvl(fieldValue)));
}else if (param.charAt(startIdx + 1) == '#') {
flag = 1;
String fieldId = param.substring(startIdx + 2, endIdx);
String fieldValue = request.getParameter(fieldId);
sb.append(java.net.URLEncoder.encode(nvl(fieldValue)));
}else {
String fieldValue = "";
String colValue = null;
String colId = null;
if (param.indexOf('!') < 0 || param.indexOf('!') > endIdx)
colId = param.substring(startIdx + 1, endIdx);
else {
colId = param.substring(startIdx + 1, param.indexOf('!'));
String fieldId = param.substring(param.indexOf('!') + 1, endIdx);
FormField ff = getFormField(fieldId);
if (ff.getFieldType().equals(FormField.FFT_TEXTAREA)) {
fieldValue = reportParamValues.getParamValueforTextAreaDrilldown(fieldId);
} else
fieldValue = (String) reportParamValues.get(fieldId);
}
DataColumnType column = getColumnById(colId);
String columnName = "";
int groupColumn = 0;
int groupMatch = 0;
if(column.getColName().startsWith("[")) {
groupColumn = 1;
columnName = column.getDisplayName();
for(ddReportFormFields.resetNext(); ddReportFormFields.hasNext(); ) {
FormField ff = ddReportFormFields.getNext();
if(ff.getFieldDisplayName().toLowerCase().equals(columnName.toLowerCase())) {
groupMatch = 1;
sb.delete(sb.lastIndexOf("&")+1, sb.length());
sb.append(ff.getFieldName()+"=");
}
}
}
if (groupColumn == 0 || (groupColumn == 1 && groupMatch == 1)) {
String dependsOn = column.getDependsOnFormField();
if(nvl(dependsOn).length()>0)
System.out.println("DependsOn " + dependsOn);
if (column != null) {
colValue = ds.getString(rowIdx, colId.toLowerCase());
if (getReportDefType().equals(AppConstants.RD_SQL_BASED))
if (!getColumnNoParseDateFlag(column))
if (ReportParamDateValueParser.isDateParam(colValue))
colValue = ReportParamDateValueParser
.formatDateParamValue(colValue);
}
String suppressValues = "|" + nvl(dct.getDrillDownType()) + "|";
if (suppressValues.length() > 2
&& suppressValues.indexOf("|" + colValue + "|") >= 0)
colValue = null;
sb.append(java.net.URLEncoder.encode(nvl(colValue, fieldValue)));
} else {
sb.delete(sb.lastIndexOf("&")+1, sb.length());
}
}
if (endIdx < param.length() - 1)
sb.append(param.substring(endIdx + 1));
param = sb.toString();
}
if(Globals.getPassRequestParamInDrilldown()) {
if(param.indexOf('#') < 0) {
String[] reqParameters = Globals.getRequestParams().split(",");
String[] sessionParameters = Globals.getSessionParams().split(",");
for (int i = 0; i < reqParameters.length; i++) {
if(request.getParameter(reqParameters[i])!=null) {
if(!reqParameters[i].toUpperCase().startsWith("FF")){
if(param.length()>0) {
param += "&" + reqParameters[i]+"="
+ request.getParameter(reqParameters[i]);
} else {
param += "&" + reqParameters[i]+"="
+ request.getParameter(reqParameters[i]);
}
}
}
}
for (int i = 0; i < sessionParameters.length; i++) {
if(session.getAttribute(sessionParameters[i].toUpperCase())!=null) {
if(!sessionParameters[i].toUpperCase().startsWith("FF")){
if(param.length()>0) {
param += "&" + sessionParameters[i].toUpperCase()+"="
+ (String)session.getAttribute(sessionParameters[i].toUpperCase());
} else {
param += "&" + sessionParameters[i].toUpperCase()+"="
+ (String)session.getAttribute(sessionParameters[i].toUpperCase());
}
}
} else {
param += "&" + sessionParameters[i].toUpperCase()+"="
+ (String)session.getAttribute(sessionParameters[i]);
}
}
}
}
dUrl.append(param.toString());
dUrl.append("&");
dUrl.append(AppConstants.RI_DISPLAY_CONTENT);
dUrl.append("=Y");
dUrl.append("&");
if(dct.isDrillinPoPUp()==null || (!dct.isDrillinPoPUp().booleanValue())) {
dUrl.append(AppConstants.RI_SHOW_BACK_BTN);
dUrl.append("=Y");
dUrl.append("&");
}
dUrl.append(AppConstants.DRILLDOWN_INDEX);
int index = Integer.parseInt(nvl(AppUtils.getRequestValue(request, AppConstants.DRILLDOWN_INDEX), "0"));
request.setAttribute(AppConstants.DRILLDOWN_INDEX, Integer.toString(index));
dUrl.append("=" + AppUtils.getRequestNvlValue(request, AppConstants.DRILLDOWN_INDEX));
}
if (dUrl.length() > 0)
dUrl.insert(0, ((dct.getDrillDownURL()).indexOf('&') > 0) ? '&' : '&');
dUrl.insert(0, AppUtils.getDrillActionURL()+dct.getDrillDownURL());
return dUrl.toString();
}
/** *********************************************************************************** */
public DataRow generateColumnDataTotalsLinear(ArrayList reportCols, String userId,
String dbInfo, HttpServletRequest request) throws RaptorException {
DataRow dr = null;
displayColTotals = false;
for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
DataColumnType dct = (DataColumnType) iter.next();
if( dct != null ) {
if (nvl(dct.getDisplayTotal()).length() > 0) {
displayColTotals = true;
break;
}
}
}
DataSet ds = null;
if (displayColTotals) {
dr = new DataRow();
ds = ConnectionUtils.getDataSet(generateTotalSQLLinear(reportParamValues, userId,request),
dbInfo);
for (int c = 0; c < reportCols.size(); c++) {
DataColumnType dct = (DataColumnType) reportCols.get(c);
if ( dct != null ) {
DataValue dv = new DataValue();
if(dv.isVisible())
dr.addDataValue(dv);
String totalValue = "";
if (ds != null)
totalValue = ds.getString(0, c);
if (nvl(dct.getDisplayTotal()).length() > 0
&& (!dct.getDisplayTotal().equals(AppConstants.TOTAL_SUM_ID)))
totalValue = nvl(AppConstants.TOTAL_FUNCTIONS.getNameById(dct
.getDisplayTotal()))
+ ": " + totalValue;
if( nvl(dct.getDisplayTotal()).length() > 0 && dct.getDisplayTotal().equals(AppConstants.TOTAL_SUM_ID)){
totalValue = "Total: "+ totalValue;
}
dv.setDisplayValue(Utils.truncateTotalDecimals(totalValue));
dv.setAlignment(dct.getDisplayAlignment());
dv.setColName(dct.getColName());
dv.setDisplayName(dct.getDisplayName());
dv.setColId(dct.getColId());
dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
StringBuffer indentation = new StringBuffer("");
if(dct.getIndentation()!=null && dct.getIndentation()>0) {
for (int indent=0; indent< dct.getIndentation(); indent++) {
indentation.append("\t");
}
dv.setNowrap("true");
}
dv.setIndentation(indentation.toString());
dv.setDisplayTotal(dct.getDisplayTotal());
dv.setBold(true);
}
}
}
return dr;
}
private Vector generateDataTotalsCrossTab(String rowColPos, String userId, HttpServletRequest request)
throws RaptorException {
String sql = getWholeSQL();
Vector dataTotals = new Vector();
boolean displayTotals = ((rowColPos.length() == 0) || (getCrossTabDisplayTotal(
rowColPos).length() > 0));
if (rowColPos.equals(AppConstants.CV_COLUMN))
displayColTotals = displayTotals;
else if (rowColPos.equals(AppConstants.CV_ROW))
displayRowTotals = displayTotals;
if (displayTotals) {
String executeSql = generateTotalSQLCrossTab(
sql, rowColPos, userId, request, reportParamValues);
DataSet ds = ConnectionUtils.getDataSet(executeSql, getDbInfo());
for (int i = 0; i < ds.getRowCount(); i++) {
Vector headerValues = new Vector();
String totalValue = null;
int cPos = 0;
for (Iterator iter = getAllColumns().iterator(); iter.hasNext();) {
DataColumnType dct = (DataColumnType) iter.next();
if (rowColPos.length() > 0
&& nvl(dct.getCrossTabValue()).equals(rowColPos)) {
DataValue dataValue = new DataValue();
dataValue.setBold(true);
dataValue.setAlignment("center");
dataValue.setDisplayValue(ds.getString(i, cPos++));
headerValues.add(dataValue);
}
else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_VALUE))
totalValue = ds.getString(i, cPos++);
}
dataTotals.add(new CrossTabTotalValue(headerValues, Utils
.truncateTotalDecimals(totalValue)));
}
}
return dataTotals;
}
/** *********************************************************************************** */
public void hideColVisual(String colId) {
visualManager.hideColumn(colId);
if (pageDataCache != null)
pageDataCache.columnVisualShowHide(colId, false);
}
public void showColVisual(String colId) {
visualManager.showColumn(colId);
if (pageDataCache != null)
pageDataCache.columnVisualShowHide(colId, true);
}
public void sortColVisual(String colId) {
visualManager.setSortByColumn(colId);
resetCache(true);
pageDataCache = null;
cachedPageNo = -1;
}
/** *********************************************************************************** */
public String generateDistinctValuesSQL(DataColumnType dct, String userId, HttpServletRequest request) throws RaptorException {
return super.generateDistinctValuesSQL(reportParamValues, dct, userId, request);
}
public String getDbInfo() {
return this.cr.getDbInfo();
}
public String parseReportSQL(String sql) throws RaptorException {
StringBuffer parsedSQL = new StringBuffer();
Vector updatedReportCols = new Vector();
curSQLParsePos = 0;
int lastParsePos = curSQLParsePos;
String lastToken = null;
logger.debug(EELFLoggerDelegate.debugLogger, ("Flat File parseReportSQL ******* SQL " + sql));
sql = sql.replaceAll("([\\s]*\\() (?!FROM)", "(");
sql = sql.replaceAll("[\\s]*\\)", ")");
String nextToken = getNextSQLParseToken(sql, true);
String dbInfo = getDbInfo();
boolean isCYMBALScript = false;
if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
try {
String dbType = remDbInfo.getDBType(dbInfo);
if (dbType.equals("DAYTONA") && !(nextToken.toUpperCase().equals("SELECT"))) {
isCYMBALScript = true;
}
} catch (Exception ex) {
throw new RaptorException(ex);
}
}
if ( isCYMBALScript == false ) {
while (nextToken.length() > 0) {
if (parsedSQL.length() == 0) {
if (nextToken.toUpperCase().equals("SELECT"))
parsedSQL.append("SELECT ");
else
throw new org.onap.portalsdk.analytics.error.ValidationException(
"The SQL must start with the SELECT keyword.");
} else if (nextToken.toUpperCase().equals("DISTINCT")
&& parsedSQL.toString().equals("SELECT ")) {
parsedSQL.append("DISTINCT ");
} else if (nextToken.equals("*")
&& (parsedSQL.toString().equals("SELECT ") || parsedSQL.toString().equals(
"SELECT DISTINCT "))) {
throw new org.onap.portalsdk.analytics.error.ValidationException(
"You cannot use \"SELECT *\". Please specify select columns/expressions.");
} else if (nextToken.toUpperCase().equals("FROM")) {
if (lastToken != null) {
updatedReportCols.add(getParseSQLDataColumn(lastToken, null, parsedSQL,
updatedReportCols, false));
lastToken = null;
}
parsedSQL.append(" \n");
while (lastParsePos < sql.length()
&& Character.isWhitespace(sql.charAt(lastParsePos)))
lastParsePos++;
parsedSQL.append(sql.substring(lastParsePos));
break;
} else {
if (nextToken.charAt(nextToken.length() - 1) == ',') {
nextToken = nextToken.substring(0, nextToken.length() - 1);
if (nextToken.length() == 0) {
if (lastToken != null) {
updatedReportCols.add(getParseSQLDataColumn(lastToken, null,
parsedSQL, updatedReportCols, false));
lastToken = null;
}
} else {
if (lastToken != null) {
updatedReportCols.add(getParseSQLDataColumn(lastToken, nextToken,
parsedSQL, updatedReportCols, false));
lastToken = null;
} else
updatedReportCols.add(getParseSQLDataColumn(nextToken, null,
parsedSQL, updatedReportCols, false));
}
} else {
if (lastToken == null)
lastToken = nextToken;
else {
String token = getNextSQLParseToken(sql, false);
if (!token.toUpperCase().equals("FROM"))
throw new org.onap.portalsdk.analytics.error.ValidationException(
"|FROM keyword or a comma expected after [" + nextToken
+ "].");
updatedReportCols.add(getParseSQLDataColumn(lastToken, nextToken,
parsedSQL, updatedReportCols, false));
lastToken = null;
}
}
}
lastParsePos = curSQLParsePos;
nextToken = getNextSQLParseToken(sql, true);
}
} else {
nextToken = getNextCYMBALSQLParseToken(sql, true);
Pattern re = null;
Matcher matcher = null;
String extracted = null;
while (nextToken.length() > 0) {
if (lastToken == null) lastToken = nextToken;
if( lastToken.toUpperCase().equals("DO DISPLAY")) {
re = Pattern.compile("each(.*)\\[.(.*?)\\]");
matcher = re.matcher(nextToken);
if (matcher.find()) {
extracted = matcher.group();
re = Pattern.compile("\\[(.*?)\\]");
matcher = re.matcher(nextToken);
if(matcher.find()) {
extracted = matcher.group();
extracted = extracted.substring(1,extracted.length()-2);
StringTokenizer sToken = new StringTokenizer(extracted);
while(sToken.hasMoreTokens()) {
updatedReportCols.add(getParseSQLDataColumn("", sToken.nextToken(),
new StringBuffer(""), updatedReportCols, true));
}
}
}
}
lastToken = nextToken;
nextToken = getNextCYMBALSQLParseToken(sql, true);
}
}
if (updatedReportCols.size() == 0)
throw new org.onap.portalsdk.analytics.error.ValidationException(
"The SQL statement must have at least one column in the SELECT clause.");
return parsedSQL.toString();
}
private String getNextCYMBALSQLParseToken(String sql, boolean updateParsePos) {
int braketCount = 0;
boolean isInsideQuote = false;
StringBuffer nextToken = new StringBuffer();
for (int idxNext = curSQLParsePos; idxNext < sql.length(); idxNext++) {
char ch = sql.charAt(idxNext);
if (ch!='\n')
nextToken.append(ch);
else break;
}
return nextToken.toString();
}
private String getNextSQLParseToken(String sql, boolean updateParsePos) {
int braketCount = 0;
boolean isInsideQuote = false;
StringBuffer nextToken = new StringBuffer();
for (int idxNext = curSQLParsePos; idxNext < sql.length(); idxNext++) {
char ch = sql.charAt(idxNext);
if (Character.isWhitespace(ch) || ch == ',') {
if (ch == ',')
nextToken.append(ch);
if (nextToken.length() == 0)
continue;
else if (braketCount == 0 && (!isInsideQuote)) {
if (updateParsePos)
curSQLParsePos = idxNext + ((ch == ',') ? 1 : 0);
break;
} else if (ch != ',' && nextToken.charAt(nextToken.length() - 1) != ' ')
nextToken.append(' ');
} else {
nextToken.append(ch);
if (ch == '(' || ch == '[')
braketCount++;
else if (ch == ')' || ch == ']')
braketCount--;
else if (ch == '\''/* ||ch=='\"' */)
isInsideQuote = (!isInsideQuote);
}
}
return nextToken.toString();
}
private DataColumnType getParseSQLDataColumn(String sqlExpression, String colId,
StringBuffer parsedSQL, Vector updatedReportCols, boolean isCYMBALScript) throws RaptorException {
DataColumnType dct = null;
if (colId != null) {
if (!isParseSQLColID(colId))
throw new org.onap.portalsdk.analytics.error.ValidationException(
"["
+ colId
+ "] must either be a valid column id consisting only of letters, numbers, and underscores, or there must be a comma in front of it.");
dct = getColumnById(colId);
} else {
colId = "";
int colIdN = 0;
for (int i = 0; (i < sqlExpression.length()) && (colIdN < 2); i++)
if (Character.isLetter(sqlExpression.charAt(i))) {
colId += sqlExpression.toLowerCase().charAt(i);
colIdN++;
} // if
colIdN = getAllColumns().size() + updatedReportCols.size();
for (boolean idAlreadyUsed = true; idAlreadyUsed; colIdN++) {
String newColId = colId + colIdN;
idAlreadyUsed = false;
for (Iterator iter = getAllColumns().iterator(); iter.hasNext();)
if (newColId.equals(((DataColumnType) iter.next()).getColId())) {
idAlreadyUsed = true;
break;
}
if (!idAlreadyUsed)
for (Iterator iter = updatedReportCols.iterator(); iter.hasNext();)
if (newColId.equals(((DataColumnType) iter.next()).getColId())) {
idAlreadyUsed = true;
break;
}
}
colId += (colIdN - 1);
}
if (dct == null) {
dct = (new ObjectFactory()).createDataColumnType();
dct.setColId(colId);
dct.setDisplayWidth(10);
dct.setDisplayAlignment("Left");
dct.setVisible(true);
dct.setGroupBreak(false);
boolean isValidIdentifier = Character.isLetterOrDigit(sqlExpression.charAt(0));
for (int i = 0; i < sqlExpression.length(); i++)
if (!(Character.isLetterOrDigit(sqlExpression.charAt(i))
|| (sqlExpression.charAt(i) == '_') || (sqlExpression.charAt(i) == '$'))) {
isValidIdentifier = false;
break;
} // if
if (isValidIdentifier) {
dct.setDisplayName(sqlExpression);
} else {
dct.setDisplayName(colId);
}
}
if(!isCYMBALScript)
sqlExpression = sqlExpression.replaceAll(", '", ",'");
dct.setDbColName(sqlExpression);
dct.setColName(sqlExpression);
dct.setCalculated(true);
dct.setColType(AppConstants.CT_CHAR);
dct.setDbColType(AppConstants.CT_CHAR);
adjustColumnType(dct);
if(!isCYMBALScript) {
if (parsedSQL.toString().equals("SELECT ")
|| parsedSQL.toString().equals("SELECT DISTINCT "))
parsedSQL.append("\n\t");
else
parsedSQL.append(", \n\t");
parsedSQL.append(sqlExpression);
parsedSQL.append(" ");
parsedSQL.append(colId);
}
return dct;
}
private boolean isParseSQLColID(String token) {
if (nvl(token).length() == 0)
return false;
for (int i = 0; i < token.length(); i++) {
char ch = token.charAt(i);
if (i == 0 && ch == '_')
return false;
if (!(Character.isLetterOrDigit(ch) || ch == '_'))
return false;
}
return true;
}
public String parseReportSQLForDrillDownParams(String reportSQL, DataColumnType dataColumnRequest, HttpServletRequest request){
String param = nvl(dataColumnRequest.getDrillDownParams());
String sql = reportSQL;
int pos = 0;
while (param.indexOf('[', pos) >= 0) {
int startIdx = param.indexOf('[',pos);
int endIdx = param.indexOf(']',startIdx+1);
pos = startIdx+1;
StringBuffer sb = new StringBuffer();
if (startIdx > 0)
sb.append(param.substring(0, startIdx));
else break;
if (param.charAt(startIdx + 1) == '#') {
String fieldId = param.substring(startIdx + 2, endIdx);
String fieldValue = ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(), request.getParameter(fieldId));
sql = Utils.replaceInString(sql, "[" + fieldId.toUpperCase()+"]", fieldValue );
}
}
return sql;
}
public List getMapMarkers(ReportData rd, org.onap.portalsdk.analytics.xmlobj.ReportMap xmlmap){
ArrayList markers = new ArrayList();
int rNum = 0;
HashMap colHash = new HashMap();
for(rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext(); rNum++) {
DataRow dr = rd.reportDataRows.getNext();
for(dr.resetNext(); dr.hasNext(); ) {
DataValue dv = dr.getNext();
colHash.put(dv.getColId(), dv.getDisplayValueLinkHtml());
}
for (int i = 0; i < xmlmap.getMarkers().size(); i ++){
Marker marker = new Marker("", "", "");
org.onap.portalsdk.analytics.xmlobj.Marker m = (org.onap.portalsdk.analytics.xmlobj.Marker) xmlmap.getMarkers().get(i);
String address = (String) colHash.get(m.getAddressColumn());
String data = (String) colHash.get(m.getDataColumn());
marker.setAddress(address);
if (xmlmap.getAddAddressInDataYN() != null && xmlmap.getAddAddressInDataYN().equals("Y")){
marker.setData(address + "
" + data);
}
else{
marker.setData(data);
}
marker.setColor(m.getMarkerColor());
markers.add(marker);
System.out.println("%%%%%%%%%%%% marker is : " + address + data);
}
}
return markers;
}
public ReportParamValues getReportParamValues() {
return reportParamValues;
}
public String getFormFieldFilled(String title) {
if( getFormFieldList()!=null && reportParamValues!=null && nvl(title).length()>0) {
for (Iterator iter1 = getFormFieldList().getFormField().iterator(); iter1.hasNext();) {
FormFieldType fft = (FormFieldType) iter1.next();
String fieldDisplay = getFormFieldDisplayName(fft);
String fieldId = fft.getFieldId();
if(!fft.getFieldType().equals(FormField.FFT_BLANK) && !fft.getFieldType().equals(FormField.FFT_LIST_MULTI) && !fft.getFieldType().equals(FormField.FFT_TEXTAREA)) {
String paramValue = Utils.oracleSafe(nvl(reportParamValues.getParamValue(fieldId)));
title = Utils.replaceInString(title, fieldDisplay, nvl(
paramValue, ""));
}
}
}
return title;
}
public synchronized Object clone() {
try {
return super.clone();
} catch (CloneNotSupportedException e) {
throw new InternalError("Cloning throws error.");
}
}
public VisualManager getVisualManager() {
return visualManager;
}
public String getReportSQLWithRowNum(String _orderBy, boolean asc) {
String sql = getWholeSQL();
int closeBracketPos = 0;
return nvl(getReportSQLOnlyFirstPart()) + " " + sql + ") x ";
}
public int getDisplayMode() {
return DISPLAY_MODE;
}
public void setDisplayMode(int mode) {
DISPLAY_MODE = mode;
}
public int getDateOption() {
return DATE_OPTION;
}
public void setDateOption(int dateOption) {
DATE_OPTION = dateOption;
}
public boolean isDisplayColTotals() {
return displayColTotals;
}
public void setDisplayColTotals(boolean displayColTotals) {
this.displayColTotals = displayColTotals;
}
public boolean isDisplayRowTotals() {
return displayRowTotals;
}
public void setDisplayRowTotals(boolean displayRowTotals) {
this.displayRowTotals = displayRowTotals;
}
private boolean canPersistLinearReport() {
boolean visibleColExist = false;
if (getDataSourceList().getDataSource().size() > 0) {
for (Iterator iter = getAllColumns().iterator(); iter.hasNext();) {
DataColumnType dct = (DataColumnType) iter.next();
if (dct.isVisible()) {
visibleColExist = true;
break;
}
}
}
return visibleColExist;
}
public void persistLinearReport(HttpServletRequest request)
throws RaptorException {
if (!canPersistLinearReport())
return;
Connection connection = null;
try {
String userID = AppUtils.getUserID(request);
String reportXML = marshal();
logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report " + reportID
+ " XML marshalled succesfully"));
verifySQLBasedReportAccess(request);
reportSecurity.reportUpdate(request);
connection = DbUtils.startTransaction();
ReportLoader.updateCustomReportRec(connection, this, reportXML);
ReportLoader.createReportLogEntry(connection, reportID, userID,
AppConstants.RLA_UPDATE, "", "");
logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report "
+ reportID + " succesfull"));
DbUtils.commitTransaction(connection);
} catch (RaptorException e) {
e.printStackTrace();
DbUtils.rollbackTransaction(connection);
throw e;
} finally {
DbUtils.clearConnection(connection);
}
}
public void persistDashboardReport(HttpServletRequest request)
throws RaptorException {
Connection connection = null;
try {
String userID = AppUtils.getUserID(request);
String reportXML = marshal();
logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report " + reportID
+ " XML marshalled succesfully"));
verifySQLBasedReportAccess(request);
reportSecurity.reportUpdate(request);
connection = DbUtils.startTransaction();
ReportLoader.updateCustomReportRec(connection, this, reportXML);
ReportLoader.createReportLogEntry(connection, reportID, userID,
AppConstants.RLA_UPDATE, "", "");
logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report "
+ reportID + " succesfull"));
DbUtils.commitTransaction(connection);
} catch (RaptorException e) {
e.printStackTrace();
DbUtils.rollbackTransaction(connection);
throw e;
} finally {
DbUtils.clearConnection(connection);
}
}
public String getTotalSql() {
return totalSql;
}
public void setTotalSql(String totalSql) {
this.totalSql = totalSql;
}
public void setTriggerFormFieldCheck( ReportFormFields FormFieldList, FormField selectedFormField) {
for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
FormField ff = (FormField) iter.next();
if(!ff.getFieldName().equals(selectedFormField.getFieldName())) {
if(nvl(ff.getBaseSQL()).length()>0 && ff.getBaseSQL().indexOf("["+selectedFormField.getFieldDisplayName() +"]")!= -1) {
selectedFormField.setTriggerOtherFormFields(true);
}
}
}
}
public void setTriggerThisFormFieldCheck( ReportFormFields FormFieldList, FormField selectedFormField) {
String sql = nvl(selectedFormField.getBaseSQL()).length()>0 ? selectedFormField.getBaseSQL():"";
for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
FormField ff = (FormField) iter.next();
if(!ff.getFieldName().equals(selectedFormField.getFieldName())) {
if(sql.indexOf("["+ff.getFieldDisplayName() +"]")!= -1) {
selectedFormField.setTriggerThisFormfield(true);
break;
}
}
}
}
private boolean isAllowEdit(HttpServletRequest request) {
boolean allowEdit = false;
String userId = AppUtils.getUserID(request);
try {
if( AppUtils.isAdminUser(request) || AppUtils.isSuperUser(request) ) {
allowEdit = true;
} else {
if(getOwnerID().equals(userId)) allowEdit = true;
else allowEdit = false;
}
} catch (RaptorException ex) {
allowEdit = false;
}
return allowEdit;
}
public String convertToDate(XMLGregorianCalendar xc) {
DateFormat df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
GregorianCalendar gCalendar = xc.toGregorianCalendar();
//Converted to date object
Date date = gCalendar.getTime();
//Formatted to String value
String dateString = df.format(date);
return dateString;
}
public String convertCalendarToDate(Date xc) {
DateFormat df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
String dateString = df.format(xc);
return dateString;
}
public RaptorResponse retrieveDataForGivenQuery( String reQsql, HttpServletRequest request, FormField ff)
throws RaptorException {
RaptorResponse raptorResponse = new RaptorResponse();
String sql = reQsql;
String jsonInString = "";
ServletContext servletContext = request.getSession().getServletContext();
if (!Globals.isSystemInitialized()) {
Globals.initializeSystem(servletContext);
}
if (!sql.trim().toUpperCase().startsWith("SELECT")) {
ErrorJSONRuntime errorJSONRuntime = new ErrorJSONRuntime();
errorJSONRuntime.setErrormessage("Invalid statement - the SQL must start with the keyword SELECT");
errorJSONRuntime.setStacktrace("SQL Error");
ObjectMapper mapper = new ObjectMapper();
mapper.configure(SerializationFeature.FAIL_ON_EMPTY_BEANS, false);
mapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
try {
jsonInString = mapper.writerWithDefaultPrettyPrinter().writeValueAsString(errorJSONRuntime);
raptorResponse.data().put("elements", jsonInString);
return raptorResponse;
} catch (Exception ex) {
logger.error(EELFLoggerDelegate.errorLogger,
"[Controller.processRequest]Invalid raptor action [retrieveDataForGivenQuery].", ex);
}
} else {
DataSet ds = null;
String userId = AppUtils.getUserID(request);
sql = Utils.replaceInString(sql, "[LOGGED_USERID]", userId);
sql = Utils.replaceInString(sql, "[USERID]", userId);
String[] reqParameters = Globals.getRequestParams().split(",");
String[] sessionParameters = Globals.getSessionParams().split(",");
javax.servlet.http.HttpSession session = request.getSession();
logger.debug(EELFLoggerDelegate.debugLogger, ("B4 testRunSQL " + sql));
if (request != null) {
for (int i = 0; i < reqParameters.length; i++) {
if (!reqParameters[i].startsWith("ff"))
sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase() + "]",
ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),request.getParameter(reqParameters[i].toUpperCase())));
else
sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase() + "]",
ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),request.getParameter(reqParameters[i])));
}
}
if (session != null) {
for (int i = 0; i < sessionParameters.length; i++) {
logger.debug(EELFLoggerDelegate.debugLogger, (" Session " + " sessionParameters[i] "
+ sessionParameters[i] + " " + (String) session.getAttribute(sessionParameters[i])));
sql = Utils.replaceInString(sql, "[" + sessionParameters[i].toUpperCase() + "]",
(String) session.getAttribute(sessionParameters[i]));
}
}
logger.debug(EELFLoggerDelegate.debugLogger, ("After testRunSQL " + sql));
try {
ds = ConnectionUtils.getDataSet(sql, ff.getDbInfo(), true);
QueryResultJSON queryResultJSON = new QueryResultJSON();
queryResultJSON.setTotalRows(ds.getRowCount());
Map dvJSON;
ArrayList colList = new ArrayList<>();
ArrayList