gerrit.onap Code Review - portal/sdk.git/blob

   1 /*
   2  * ============LICENSE_START==========================================
   3  * ONAP Portal SDK
   4  * ===================================================================
   5  * Copyright © 2017 AT&T Intellectual Property. All rights reserved.
   6  * ===================================================================
   7  *
   8  * Unless otherwise specified, all software contained herein is licensed
   9  * under the Apache License, Version 2.0 (the "License");
  10  * you may not use this software except in compliance with the License.
  11  * You may obtain a copy of the License at
  12  *
  13  *             http://www.apache.org/licenses/LICENSE-2.0
  14  *
  15  * Unless required by applicable law or agreed to in writing, software
  16  * distributed under the License is distributed on an "AS IS" BASIS,
  17  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  18  * See the License for the specific language governing permissions and
  19  * limitations under the License.
  20  *
  21  * Unless otherwise specified, all documentation contained herein is licensed
  22  * under the Creative Commons License, Attribution 4.0 Intl. (the "License");
  23  * you may not use this documentation except in compliance with the License.
  24  * You may obtain a copy of the License at
  25  *
  26  *             https://creativecommons.org/licenses/by/4.0/
  27  *
  28  * Unless required by applicable law or agreed to in writing, documentation
  29  * distributed under the License is distributed on an "AS IS" BASIS,
  30  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  31  * See the License for the specific language governing permissions and
  32  * limitations under the License.
  33  *
  34  * ============LICENSE_END============================================
  35  *
  36  *
  37  */
  38 package org.onap.portalsdk.analytics.model.runtime;
  39
  40 import java.io.Serializable;
  41 import java.sql.Connection;
  42 import java.text.SimpleDateFormat;
  43 import java.util.ArrayList;
  44 import java.util.Arrays;
  45 import java.util.Date;
  46 import java.util.Enumeration;
  47 import java.util.HashMap;
  48 import java.util.Iterator;
  49 import java.util.List;
  50 import java.util.Map;
  51 import java.util.StringTokenizer;
  52 import java.util.Vector;
  53 import java.util.regex.Matcher;
  54 import java.util.regex.Pattern;
  55
  56 import javax.servlet.http.HttpServletRequest;
  57 import javax.servlet.http.HttpSession;
  58 import org.apache.commons.lang.StringUtils;
  59 import org.apache.jcs.engine.CacheUtils;
  60 import org.onap.portalsdk.analytics.error.RaptorException;
  61 import org.onap.portalsdk.analytics.model.DataCache;
  62 import org.onap.portalsdk.analytics.model.ReportHandler;
  63 import org.onap.portalsdk.analytics.model.ReportLoader;
  64 import org.onap.portalsdk.analytics.model.base.IdNameList;
  65 import org.onap.portalsdk.analytics.model.base.IdNameSql;
  66 import org.onap.portalsdk.analytics.model.base.IdNameValue;
  67 import org.onap.portalsdk.analytics.model.base.ReportWrapper;
  68 import org.onap.portalsdk.analytics.model.definition.Marker;
  69 import org.onap.portalsdk.analytics.system.AppUtils;
  70 import org.onap.portalsdk.analytics.system.ConnectionUtils;
  71 import org.onap.portalsdk.analytics.system.DbUtils;
  72 import org.onap.portalsdk.analytics.system.Globals;
  73 import org.onap.portalsdk.analytics.util.AppConstants;
  74 import org.onap.portalsdk.analytics.util.CachingUtils;
  75 import org.onap.portalsdk.analytics.util.DataSet;
  76 import org.onap.portalsdk.analytics.util.RemDbInfo;
  77 import org.onap.portalsdk.analytics.util.Utils;
  78 import org.onap.portalsdk.analytics.view.ColumnHeader;
  79 import org.onap.portalsdk.analytics.view.ColumnHeaderRow;
  80 import org.onap.portalsdk.analytics.view.CrossTabOrderManager;
  81 import org.onap.portalsdk.analytics.view.CrossTabTotalValue;
  82 import org.onap.portalsdk.analytics.view.DataRow;
  83 import org.onap.portalsdk.analytics.view.DataValue;
  84 import org.onap.portalsdk.analytics.view.ReportData;
  85 import org.onap.portalsdk.analytics.view.RowHeaderCol;
  86 import org.onap.portalsdk.analytics.xmlobj.CustomReportType;
  87 import org.onap.portalsdk.analytics.xmlobj.DataColumnType;
  88 import org.onap.portalsdk.analytics.xmlobj.FormFieldType;
  89 import org.onap.portalsdk.analytics.xmlobj.ObjectFactory;
  90 import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;
  91 import org.onap.portalsdk.core.objectcache.jcs.JCSCacheManager;
  92 import org.onap.portalsdk.core.util.SecurityCodecUtil;
  93 import org.owasp.esapi.ESAPI;
  94 import org.springframework.beans.factory.annotation.Autowired;
  95 import org.springframework.stereotype.Component;
  96 import com.fasterxml.jackson.databind.ObjectMapper;
  97
  98
  99 /**<HR/>
 100  * This class is part of <B><I>RAPTOR (Rapid Application Programming Tool for OLAP Reporting)</I></B><BR/>
 101  * <HR/>
 102  *
 103  * --------------------------------------------------------------------------------------------------<BR/>
 104  * <B>ReportRuntime.java</B> -  This class involves in running, downloading RAPTOR reports.
 105  * --------------------------------------------------------------------------------------------------<BR/>
 106  *
 107  *
 108  * <U>Change Log</U><BR/><BR/>
 109  *
 110  * 27-Aug-2009 : Version 8.5 (Sundar); <UL><LI>Order by logic is restored for DAYTONA.</LI></UL>
 111  * 13-Aug-2009 : Version 8.5 (Sundar); <UL><LI>Removing order by logic is rollbacked.</LI></UL>
 112  * 22-Jun-2009 : Version 8.4 (Sundar); <UL><LI>Bug while parsing SQL for text download is fixed.</LI></UL>
 113  *
 114  */
 115
 116 @Component
 117 public class ReportRuntime extends ReportWrapper implements Cloneable, Serializable {
 118
 119         private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportRuntime.class);
 120
 121     private int curSQLParsePos = 0;
 122
 123         private String xmlFileURL = null;
 124
 125     private String xmlFileName = null;
 126
 127         private String flatFileName = null;
 128
 129         private String excelPageFileName = null;
 130
 131         private int reportDataSize = -1;
 132
 133         private boolean displayChart = true;
 134
 135         private boolean displayForm = true;
 136
 137         private boolean displayContent = true;
 138
 139         private boolean reportRunLogged = false;
 140
 141         private DataSet chartDataCache = null;
 142
 143         private ReportData pageDataCache = null;
 144
 145         private int cachedPageNo = -1;
 146
 147         private String cachedSQL = null;
 148
 149     private String wholeSQL = null;
 150
 151     private String totalSql = null;
 152
 153     private boolean isSinglePageDownload = false;
 154
 155         private ReportParamValues reportParamValues = null;
 156
 157         private ReportParamValuesForPDFExcel reportParamValuesFPE = null;
 158
 159         private ReportFormFields reportFormFields = null;
 160
 161         private VisualManager visualManager = null;
 162
 163         private CrossTabOrderManager crossTabOrderManager = null;
 164
 165         private boolean displayColTotals = false;
 166
 167         private boolean displayRowTotals = false;
 168
 169         private DataRow colDataTotalsLinear = null;
 170
 171         private Vector colDataTotalsCrosstab = null;
 172
 173         private Vector rowDataTotalsCrosstab = null;
 174
 175         private String grandTotalCrosstab = null;
 176
 177         public static int DISPLAY_DATA_ONLY = 1;
 178         public static int DISPLAY_CHART_ONLY = 2;
 179         public static int DISPLAY_CHART_AND_DATA = 3;
 180
 181         public static final int DATE_OPTION_MONTHLY = 1;
 182         public static final int DATE_OPTION_YEARLY = 2;
 183         public static final int DATE_OPTION_DAILY = 3;
 184
 185
 186         private int DISPLAY_MODE = 0;
 187
 188         private int DATE_OPTION = -1;
 189
 190
 191         public boolean isSinglePageDownload() {
 192                 return isSinglePageDownload;
 193         }
 194
 195         public void setSinglePageDownload(boolean isSinglePageDownload) {
 196                 this.isSinglePageDownload = isSinglePageDownload;
 197         }
 198
 199         public void setReportFormFields(ReportFormFields reportFormFields) {
 200                 this.reportFormFields = reportFormFields;
 201         }
 202
 203         public ReportRuntime() {
 204                 super();
 205         }
 206
 207         private ReportRuntime(CustomReportType crType, String reportID, HttpServletRequest request,
 208                         String ownerID, String createID, String createDate, String updateID,
 209                         String updateDate, String menuID, boolean menuApproved) throws RaptorException {
 210                 super(crType, reportID, ownerID, createID, createDate, updateID, updateDate, menuID,
 211                                 menuApproved);
 212                 initializeReportRuntime(request);
 213         }
 214
 215         public ReportRuntime(ReportWrapper rw) throws RaptorException {
 216                 this(rw, null);
 217         }
 218
 219         public ReportRuntime(ReportWrapper rw, HttpServletRequest request)throws RaptorException {
 220                 super(rw);
 221                 initializeReportRuntime(request);
 222         }
 223
 224         private void initializeReportRuntime(HttpServletRequest request) throws RaptorException {
 225                 reportFormFields = new ReportFormFields(this, request);
 226                 setParamValues(request, true, true);
 227
 228                 visualManager = new VisualManager();
 229         }
 230
 231
 232         public static ReportRuntime unmarshal(String reportXML, String reportID)
 233                         throws RaptorException {
 234                 return unmarshal(reportXML, reportID, null);
 235         }
 236
 237         public static ReportRuntime unmarshal(String reportXML, String reportID,
 238                         HttpServletRequest request) throws RaptorException  {
 239                 CustomReportType crType = ReportWrapper.unmarshalCR(reportXML);
 240                 ObjectFactory objFactory = new ObjectFactory();
 241
 242                 logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report [" + reportID + "]: XML unmarshalled"));
 243
 244                 return new ReportRuntime(crType, reportID, request, null, null, null, null, null, null,
 245                                 false);
 246
 247         }
 248
 249         public String getXmlFileURL() {
 250                 return xmlFileURL;
 251         }
 252
 253         public String getXmlFileName() {
 254             return xmlFileName;
 255         }
 256
 257         public String getFlatFileName() {
 258                 return flatFileName;
 259         }
 260
 261         public String getExcelPageFileName() {
 262                 return excelPageFileName;
 263         }
 264
 265         public int getReportDataSize() {
 266                 return reportDataSize;
 267         }
 268
 269         public boolean getDisplayChart() {
 270                 return displayChart;
 271         }
 272
 273         public boolean getDisplayForm() {
 274                 return displayForm;
 275         }
 276
 277         public boolean getDisplayContent() {
 278                 return displayContent;
 279         }
 280
 281         public int getCachedPageNo() {
 282                 return cachedPageNo;
 283         }
 284
 285         public String getCachedSQL() {
 286                 return cachedSQL;
 287         }
 288
 289         public boolean isDashboardType() throws RaptorException {
 290                 return ReportLoader.isDashboardType(getReportID());
 291         }
 292
 293
 294         public void setXmlFileURL(String xmlFileURL) {
 295                 this.xmlFileURL = xmlFileURL;
 296         }
 297
 298         public void setXmlFileName(String xmlFileName) {
 299           this.xmlFileName = xmlFileName;
 300         }
 301
 302         public void setFlatFileName(String flatFileName) {
 303                 this.flatFileName = flatFileName;
 304         }
 305
 306         public void setExcelPageFileName(String excelPageFileName) {
 307                 this.excelPageFileName = excelPageFileName;
 308         }
 309
 310         /*private*/ public void setReportDataSize(int reportDataSize) {
 311                 this.reportDataSize = reportDataSize;
 312         }
 313
 314         private void setDisplayForm(boolean displayForm) {
 315                 this.displayForm = displayForm;
 316         }
 317
 318         private void setDisplayContent(boolean displayContent) {
 319                 this.displayContent = displayContent;
 320         }
 321
 322         public void setDisplayFlags(boolean isFirstAccess, boolean forceDisplayContent) {
 323                 if (isFirstAccess) {
 324                         setDisplayForm(true);
 325
 326                         if (forceDisplayContent)
 327                                 setDisplayContent(true);
 328                         else if (Globals.getDisplayFormBeforeRun())
 329                                 if (needFormInput())
 330                                         setDisplayContent(false);
 331                                 else
 332                                         setDisplayContent(true);
 333                         else
 334                                 setDisplayContent(true);
 335                 } else {
 336                         setDisplayContent(true);
 337
 338                         if (Globals.getIncludeFormWithData())
 339                                 setDisplayForm(true);
 340                         else if (Globals.getDisplayFormBeforeRun())
 341                                 setDisplayForm(false);
 342                         else
 343                                 setDisplayForm(true);
 344                 }
 345         }
 346
 347         public void logReportRun(String userID, String executionTime, String formFields) throws RaptorException {
 348                 if (reportRunLogged)
 349                         return;
 350
 351                 ReportLoader.createReportLogEntry(null, reportID, userID, AppConstants.RLA_RUN,executionTime,formFields );
 352                 reportRunLogged = true;
 353         }
 354
 355         public void logReportExecutionTime(String userId, String executionTime, String action, String formFields) throws RaptorException        {
 356                 ReportLoader.createReportLogEntryForExecutionTime(null, reportID, userId,executionTime , action, formFields);
 357         }
 358
 359         public void logReportExecutionTimeFromLogList (String userId, String executionTime, String formFields) throws RaptorException   {
 360                 ReportLoader.createReportLogEntryForExecutionTime(null, reportID, userId,executionTime , AppConstants.RLA_FROM_LOG, formFields);
 361         }
 362
 363         public void resetVisualSettings() {
 364                 boolean haveToResetCachedData = (visualManager.getSortByColId().length() > 0);
 365                 visualManager = new VisualManager();
 366
 367                 if (haveToResetCachedData)
 368                         pageDataCache = null;
 369
 370                 if (pageDataCache != null)
 371                         pageDataCache.resetVisualSettings();
 372         }
 373
 374         /** ************** ReportParamValues processing *************** */
 375
 376         public boolean setParamValues(HttpServletRequest request, boolean resetParams, boolean refresh) throws RaptorException {
 377                 boolean paramsUpdated = false;
 378                 if (resetParams) {
 379             reportFormFields = new ReportFormFields(this, request);
 380                         reportParamValues = new ReportParamValues(reportFormFields, getReportDefType());
 381                     // This is called even in the wizard page. Hence this condition.
 382                         if((ReportRuntime) request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME)!=null)
 383                           reportParamValuesFPE = new ReportParamValuesForPDFExcel(reportFormFields, getReportDefType());
 384                         paramsUpdated = true;
 385
 386                         reportRunLogged = false;
 387                 } else if (request != null) {
 388                         paramsUpdated = reportParamValues.setParamValues(request,refresh);
 389                 }
 390
 391                         if((ReportRuntime) request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME)!=null) {
 392                                 if(reportParamValuesFPE!=null)  reportParamValuesFPE.setParamValues(request,refresh);
 393                                 else {
 394                                           reportParamValuesFPE = new ReportParamValuesForPDFExcel(reportFormFields, getReportDefType());
 395                                           reportParamValuesFPE.setParamValues(request,refresh);
 396                                 }
 397                         } else {
 398                                 reportFormFields = new ReportFormFields(this, request);
 399                                 reportParamValues = new ReportParamValues(reportFormFields, getReportDefType());
 400                                 reportParamValues.setParamValues(request,refresh);
 401                                 reportParamValuesFPE = new ReportParamValuesForPDFExcel(reportFormFields, getReportDefType());
 402                                 reportParamValuesFPE.setParamValues(request,refresh);
 403
 404                         }
 405                 if (paramsUpdated) {
 406                         setReportDataSize(-1);
 407                         chartDataCache = null;
 408                         pageDataCache = null;
 409                         cachedPageNo = -1;
 410
 411                         crossTabOrderManager = null;
 412
 413                         colDataTotalsLinear = null;
 414                         colDataTotalsCrosstab = null;
 415                         rowDataTotalsCrosstab = null;
 416                         grandTotalCrosstab = null;
 417                         if(!refresh)
 418                                 resetVisualSettings();
 419                 }
 420
 421                 displayChart = (request.getParameter(AppConstants.RI_DISPLAY_CHART) == null) ? !isDisplayOptionHideChart() : request.getParameter("display_chart")
 422                                 .equals("Y");
 423
 424                 return paramsUpdated;
 425         }
 426
 427         public String getParamValue(String key) {
 428                 return reportParamValues.getParamValue(key);
 429         }
 430
 431         public String getParamDisplayValue(String key) {;
 432                 return reportParamValues.getParamDisplayValue(key);
 433         }
 434         public Enumeration getParamKeys() {
 435                 return reportParamValues.keys();
 436         }
 437
 438         public Enumeration getParamKeysForPDFExcel() {
 439                 return reportParamValuesFPE.keys();
 440         }
 441
 442         public String getParamValueForPDFExcel(String key) {
 443                 return reportParamValuesFPE.getParamValue(key);
 444         }
 445
 446         public ArrayList getParamNameValuePairs() {
 447                 ArrayList paramList = new ArrayList(getReportFormFields().size());
 448                 for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
 449                         FormField ff = (FormField) iter.next();
 450                         paramList.add(new IdNameValue(ff.getFieldDisplayName(), reportParamValues
 451                                         .getParamDisplayValue(ff.getFieldName())));
 452                 }
 453                 return paramList;
 454         }
 455
 456         public ArrayList getParamNameValuePairsforPDFExcel(HttpServletRequest request, int type /*excel =1; pdf=2*/) {
 457                 javax.servlet.http.HttpSession session = request.getSession();
 458                 ArrayList paramList = new ArrayList(getReportFormFields().size());
 459                 if(session.getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO)!=null) {
 460                         paramList = (ArrayList) session.getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO);
 461                         if(paramList!=null && paramList.size()>0)
 462                                 return paramList;
 463                 }
 464                 if ( reportParamValuesFPE == null) {
 465                         reportParamValuesFPE = new ReportParamValuesForPDFExcel(reportFormFields, getReportDefType());
 466                         reportParamValuesFPE.setParamValues(request,true);
 467                 }
 468
 469                 String valueString = "";
 470                 for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
 471                         FormField ff = (FormField) iter.next();
 472                         if(ff.isVisible() && type == 1){
 473                                 valueString = reportParamValuesFPE.getParamDisplayValue(ff.getFieldName());
 474                         } else if(ff.isVisible() && type != 1) {
 475                                 valueString = reportParamValuesFPE.getParamDisplayValue(ff.getFieldName());
 476                         }
 477                                 if(valueString.equalsIgnoreCase("NULL"))
 478                                         valueString="";
 479                                 paramList.add(new IdNameValue(ff.getFieldDisplayName(), valueString));
 480                 }
 481
 482                 String pdfAttachmentKey = AppUtils.getRequestValue(request, "pdfAttachmentKey");
 483                 boolean isSchedule = false;
 484                 if(pdfAttachmentKey != null)
 485                         isSchedule = true;
 486                 if(Globals.customizeFormFieldInfo() && type == 2) {
 487                         String[] sessionParameters = Globals.getSessionParams().split(",");
 488
 489                 if(session != null && !isSchedule ) {
 490                         session.setAttribute("login_id", AppUtils.getUserBackdoorLoginId(request));
 491                     for (int i = 0; i < sessionParameters.length; i++) {
 492                          if(session.getAttribute(sessionParameters[i])!=null)
 493                           paramList.add(new IdNameValue(sessionParameters[i].toUpperCase(), (String)session.getAttribute(sessionParameters[i])));
 494                     }
 495                  }
 496
 497                 if(isSchedule) {
 498                    String[] scheduleSessionParam = Globals.getSessionParamsForScheduling().split(",");
 499                        for (int i = 0; i < scheduleSessionParam.length; i++) {
 500                            if(request.getParameter(scheduleSessionParam[i])!=null)
 501                                    paramList.add(new IdNameValue(ESAPI.encoder().canonicalize(scheduleSessionParam[i].toUpperCase()), ESAPI.encoder().canonicalize(request.getParameter(scheduleSessionParam[i]))));
 502                        }
 503                 }
 504
 505                 try {
 506                         SimpleDateFormat oracleDateFormat = new SimpleDateFormat("MM/dd/yyyy kk:mm:ss");
 507                         Date sysdate = oracleDateFormat.parse(ReportLoader.getSystemDateTime());
 508                         SimpleDateFormat dtimestamp = new SimpleDateFormat(Globals.getScheduleDatePattern());
 509                         paramList.add(new IdNameValue("DATE", dtimestamp.format(sysdate)+" "+Globals.getTimeZone()));
 510                 } catch(Exception ex) {}
 511
 512                 } else {
 513                                 String[] sessionDisplayParameters = Globals.getDisplaySessionParamInPDFEXCEL().split(",");
 514                                 if(session != null && !isSchedule ) {
 515                                 session.setAttribute("login_id", AppUtils.getUserBackdoorLoginId(request));
 516                                         for (int i = 0; i < sessionDisplayParameters.length; i++) {
 517                                                 String sessionDispParam = sessionDisplayParameters[i];
 518                                                 if(nvl(sessionDispParam).length()>0) {
 519                                                         String sessionDispParamArr[] = sessionDispParam.split(";");
 520                                                         paramList.add(new IdNameValue(sessionDispParamArr[1], nvl((String)session.getAttribute(sessionDispParamArr[0]),"")));
 521                                                 }
 522                                         }
 523                                 }
 524                         if(isSchedule) {
 525                                         String[] scheduleSessionParam = Globals.getDisplayScheduleSessionParamInPDFEXCEL().split(",");
 526                                         for (int i = 0; i < scheduleSessionParam.length; i++) {
 527                                                 String scheduleSessionDispParam = scheduleSessionParam[i];
 528                                                 if(nvl(scheduleSessionDispParam).length()>0) {
 529                                                         String scheduleSessionDispParamArr[] = scheduleSessionDispParam.split(";");
 530                                                         paramList.add(new IdNameValue(ESAPI.encoder().canonicalize(scheduleSessionDispParamArr[1]), ESAPI.encoder().canonicalize(nvl(request.getParameter(scheduleSessionDispParamArr[0]),""))));
 531                                                 }
 532                                         }
 533                         }
 534                         try {
 535                                 SimpleDateFormat oracleDateFormat = new SimpleDateFormat("MM/dd/yyyy kk:mm:ss");
 536                                 Date sysdate = oracleDateFormat.parse(ReportLoader.getSystemDateTime());
 537                                 SimpleDateFormat dtimestamp = new SimpleDateFormat(Globals.getScheduleDatePattern());
 538                                 paramList.add(new IdNameValue("Report Date/Time", dtimestamp.format(sysdate)+" "+Globals.getTimeZone()));
 539                         } catch(Exception ex) {}
 540
 541                         }
 542
 543                   for (int i = 0; i < paramList.size(); i++) {
 544                           IdNameValue value = (IdNameValue) paramList.get(i);
 545                           String name = value.getName().replaceAll(",","~");
 546                           value.setName(name);
 547                   }
 548                 return paramList;
 549         }
 550
 551         /** ************** ReportFormFields processing *************** */
 552
 553         public String getFormFieldComments(HttpServletRequest request) {
 554                 String comment = "";
 555                 StringBuffer st = new StringBuffer("");
 556                 if(getFormFieldList()!=null){
 557                         comment = nvl(getFormFieldList().getComment());
 558                         ArrayList al = null;
 559                         if(comment.length()>0) {
 560                                 al = getParamNameValuePairsforPDFExcel(request, 2);
 561                                 if(al!=null) {
 562                                         for (int i=0; i < al.size(); i++) {
 563                                                 IdNameValue idNameValue = (IdNameValue)al.get(i);
 564                                                 if(nvl(idNameValue.getId()).equals("DATE"))
 565                                                         st.append("<b>Date/Time Report Run:</b>"+  idNameValue.getName() +"<br></br>");
 566                                         }
 567
 568                                         for (int i=0; i < al.size(); i++) {
 569                                                 IdNameValue idNameValue = (IdNameValue)al.get(i);
 570                                                 comment = Utils.replaceInString(comment, "["+ idNameValue.getId()+"]", idNameValue.getName());
 571                                         }
 572                                         st.append(comment);
 573
 574                                 }
 575                         }
 576                 }
 577                 return st.toString();
 578         }
 579
 580         public boolean needFormInput() {
 581                 return reportFormFields.getFieldCount() > 0;
 582         }
 583
 584         public FormField getFormField(String fieldName) {
 585                 return reportFormFields.getFormField(fieldName);
 586         }
 587
 588         public ReportFormFields getReportFormFields() {
 589                 return reportFormFields;
 590         }
 591
 592         public void setChartDataCache(DataSet chartDataCache) {
 593                 this.chartDataCache = chartDataCache;
 594         }
 595
 596
 597         /** ************** Report Data processing *************** */
 598         public DataSet loadChartData(String userId, HttpServletRequest request) throws RaptorException {
 599                 if (nvl(getChartType()).length() == 0)
 600                         return null;
 601                 if (!getDisplayChart())
 602                         return null;
 603
 604                 DataSet ds = chartDataCache;
 605         String sql = null;
 606                 if (ds == null) {
 607             sql = generateChartSQL(reportParamValues, userId, request);
 608                         String dbInfo = getDBInfo();
 609                         ds = ConnectionUtils.getDataSet(sql, dbInfo);
 610                         if (Globals.getCacheChartData())
 611                                 chartDataCache = ds;
 612                 }
 613
 614                 return ds;
 615         }
 616
 617         public String getReportDataSQL(String userId, int downloadLimit, HttpServletRequest request) throws RaptorException {
 618                 String reportSQL = "";
 619                 if(doesReportContainsGroupFormField()) {
 620                         reportSQL = generateSubsetSQL(0, downloadLimit,userId, request, true, reportParamValues);
 621                 } else
 622                         reportSQL = generateSubsetSQL(0, downloadLimit,userId, request, false, reportParamValues);
 623                 return reportSQL;
 624         }
 625
 626         public ReportData loadReportData(int pageNo, String userId, int downloadLimit, HttpServletRequest request, boolean download) throws RaptorException {
 627                 ReportData rd = null;
 628                 boolean isGoBackAction = AppUtils.getRequestFlag(request, AppConstants.RI_GO_BACK);
 629                 if (pageNo >= 0)
 630                         if (pageNo == cachedPageNo && pageDataCache != null)
 631                                 rd = pageDataCache;
 632
 633                 if(isGoBackAction && rd!=null) return rd;
 634                 if (rd == null) {
 635                         if (getReportType().equals(AppConstants.RT_CROSSTAB))
 636                                 rd = loadCrossTabReportData(pageNo, userId, downloadLimit, request, download);
 637                         else if (getReportType().equals(AppConstants.RT_LINEAR))
 638                                 rd = loadLinearReportData(pageNo, userId, downloadLimit, request, download);
 639                         else
 640                                 throw new RuntimeException(
 641                                                 "[ReportRuntime.loadReportData] Invalid report type");
 642
 643                         if (pageNo >= 0)
 644                                 if (Globals.getCacheCurPageData()) {
 645                                         pageDataCache = rd;
 646                                         cachedPageNo = pageNo;
 647                                 }
 648                 }
 649
 650                 return rd;
 651         }
 652
 653         private ReportData loadCrossTabReportData(int pageNo, String userId, int downloadLimit, HttpServletRequest request, boolean download) throws RaptorException {
 654                 String reportSQL = generateSQL(reportParamValues, userId, request);
 655                 setWholeSQL(reportSQL);
 656                 cachedSQL = reportSQL;
 657         wholeSQL = reportSQL;
 658         List reportCols = getAllColumns();
 659         DataColumnType dataColumnRequest = getCrossTabValueColumn();
 660         reportSQL = parseReportSQLForDrillDownParams(reportSQL, dataColumnRequest, request);
 661
 662
 663                 DataSet ds = null;
 664                 String dbInfo = getDBInfo();
 665                 StringBuffer colNames = new StringBuffer();
 666                 StringBuffer colExtraIdNames = new StringBuffer();
 667                 StringBuffer colExtraDateNames = new StringBuffer();
 668
 669
 670                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 671                         DataColumnType dc = (DataColumnType) iter.next();
 672                                 if (colNames.length() > 0)
 673                                         colNames.append(", ");
 674                                 colNames.append(dc.getColId());
 675                                 if (nvl(dc.getDrillDownURL()).length() > 0) {
 676                                                 System.out.println("Drilldown URL " + dc.getDrillDownURL());
 677
 678                                 }
 679                 }
 680
 681                 if (reportSQL.toUpperCase().indexOf("GROUP BY ") < 0)
 682                         colNames.append(colExtraIdNames.toString());
 683                 colNames.append(colExtraDateNames.toString());
 684
 685                 String rSQL = Globals.getLoadCrosstabReportData();
 686                 rSQL = rSQL.replace("[colNames.toString()]", colNames.toString());
 687                 rSQL = rSQL.replace("[reportSQL]", reportSQL);
 688                 reportSQL = rSQL;
 689                 setWholeSQL(reportSQL);
 690                 if (crossTabOrderManager == null)
 691                         crossTabOrderManager = new CrossTabOrderManager(this, userId,request);
 692                 ds = ConnectionUtils.getDataSet(reportSQL, dbInfo);
 693
 694                 ReportData rd = new ReportData(pageNo, false);
 695                 ReportFormFields childReportFormFields = null;
 696                 if(doesReportContainsGroupFormField()) {
 697                         List reportCols1 = getAllColumns();
 698                         reportCols = new Vector();
 699                         outer:
 700                         for (Iterator iter = reportCols1.iterator(); iter.hasNext();) {
 701                                 DataColumnType dct = (DataColumnType) iter.next();
 702                                 for (int k=0; k<ds.getColumnCount(); k++) {
 703                                         if(dct.getColId().toUpperCase().trim().equals(ds.getColumnName(k).trim())) {
 704                                                 reportCols.add(dct);
 705                                                 continue outer;
 706                                         }
 707                                 }
 708                         }
 709
 710                         if (getFormFieldList() != null) {
 711                                 String paramValue = "";
 712                                 for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 713                                         FormFieldType fft = (FormFieldType) iter.next();
 714                                         if(fft.isGroupFormField()!=null && fft.isGroupFormField().booleanValue()) {
 715                                 paramValue = Utils.oracleSafe(nvl(reportParamValues
 716                                                 .getParamValue(fft.getFieldId())));
 717                                 outer:
 718                                         for (Iterator iter1 = reportCols1.iterator(); iter1.hasNext();) {
 719                                                 DataColumnType dct = (DataColumnType) iter1.next();
 720                                                         if(("["+fft.getFieldName()+ "]").equals(dct.getColName().trim())) {
 721                                                                 dct.setDisplayName(paramValue);
 722                                                                 continue outer;
 723                                                         }
 724                                         }
 725
 726
 727                                         }
 728                                 }
 729                         }
 730                 }
 731
 732                 int dataColumnIdx = (rd.reportRowHeaderCols.size() + rd.reportColumnHeaderRows.size())-1;
 733                 DataColumnType dataColumn = getCrossTabValueColumn();
 734
 735                 String columnValue = "";
 736
 737
 738                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 739                         DataColumnType dct = (DataColumnType) iter.next();
 740
 741                         if (nvl(dct.getDrillDownURL()).length() > 0) {
 742                                 childReportFormFields = getChildReportFormFields(request,dct.getDrillDownURL());
 743                         }
 744
 745                         if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_ROW)) {
 746                                 RowHeaderCol rhc = new RowHeaderCol();
 747                                 rhc.setColumnTitle(dct.getDisplayName());
 748                                 if(nvl(dct.getDisplayWidthInPxls()).length()<=0) {
 749                                         dct.setDisplayWidthInPxls("100px");
 750                                 }
 751                                 if(dct.getDisplayWidthInPxls().endsWith("px"))
 752                                         rhc.setColumnWidth(dct.getDisplayWidthInPxls());
 753                                 else
 754                                         rhc.setColumnWidth(dct.getDisplayWidthInPxls()+"px");
 755
 756                                 rhc.setAlignment(dct.getDisplayAlignment());
 757                                 rhc.setDisplayHeaderAlignment(dct.getDisplayHeaderAlignment());
 758                                 rhc.setNowrap(nvl(dataColumn.getNowrap(),"null").equals("false")?"null":nvl(dataColumn.getNowrap(),"null"));
 759                                 rd.reportRowHeaderCols.addRowHeaderCol(rhc);
 760                         } else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_COLUMN)) {
 761                                 ColumnHeaderRow chr = new ColumnHeaderRow();
 762                                 chr.setAlignment(dct.getDisplayHeaderAlignment());
 763                                 chr.setRowHeight("15");
 764                                 if(nvl(dct.getDisplayWidthInPxls()).length()<=0) {
 765                                         dct.setDisplayWidthInPxls("80px");
 766                                 }
 767                                 if(dct.getDisplayWidthInPxls().endsWith("px"))
 768                                         chr.setDisplayWidth(dct.getDisplayWidthInPxls());
 769                                 else
 770                                         chr.setDisplayWidth(dct.getDisplayWidthInPxls()+"px");
 771
 772                                 rd.reportColumnHeaderRows.addColumnHeaderRow(chr);
 773                         } else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_VALUE)) {
 774                                 columnValue = dct.getColId();
 775                         } else {
 776                                 if(!dct.getColId().toLowerCase().endsWith("_sort")) {
 777                                         RowHeaderCol rhc = new RowHeaderCol();
 778                                         rhc.setVisible(false);
 779                                         rd.reportRowHeaderCols.addRowHeaderCol(rhc);
 780                                 }
 781                         }
 782                 }
 783                 FormatProcessor formatProcessor = new FormatProcessor(getSemaphoreById(dataColumn
 784                                 .getSemaphoreId()),
 785                                 getReportDefType().equals(AppConstants.RD_SQL_BASED) ? AppConstants.CT_NUMBER
 786                                                 : dataColumn.getColType(), dataColumn.getColFormat(), false);
 787         List dataList = new ArrayList();
 788                 for (int i = 0; i < ds.getRowCount(); i++) {
 789                         Vector rValues = new Vector();
 790                         Vector cValues = new Vector();
 791                         Vector cValuesSort = new Vector();
 792
 793                         int colIdx = 0;
 794                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 795                                 try {
 796                                         DataColumnType dct = (DataColumnType) iter.next();
 797                                         if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_ROW))
 798                                                 rValues.add(addDataValue(dct, ds.getString(i, dct.getColId())));
 799                                         if (nvl(dct.getCrossTabValue()).trim().length()<=0 && !dct.getColId().toLowerCase().endsWith("_sort"))
 800                                                 rValues.add(addDataValue(dct, ds.getString(i, dct.getColId())));
 801                                         if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_COLUMN)) {
 802                                                 cValues.add(addDataValue(dct, ds.getString(i, dct.getColId())));
 803                                                 if(getColumnById(dct.getColId().toLowerCase()+"_sort")!=null)
 804                                                         cValuesSort.add(addDataValue(dct, new Integer(ds.getString(i, dct.getColId()+"_sort")).toString()));
 805                                         }
 806                                         if (dct.isVisible())
 807                                                 colIdx++;
 808                                 } catch (ArrayIndexOutOfBoundsException ex ) {continue;}
 809                                 catch (NumberFormatException ex1) {
 810                                         ex1.printStackTrace();
 811                                         continue;
 812                                 }
 813                         } // for
 814
 815                         DataValue dv = new DataValue();
 816                         dv.setDisplayValue(ds.getString(i, columnValue));
 817                         if (nvl(dataColumn.getCrossTabValue()).trim().length()<=0)
 818                          dv.setVisible(false);
 819                         dv.setAlignment(dataColumn.getDisplayAlignment());
 820             dv.setDisplayTotal(dataColumn.getDisplayTotal());
 821             dv.setColName(dataColumn.getColName());
 822             dv.setDisplayName(dataColumn.getDisplayName());
 823             dv.setColId(dataColumn.getColId());
 824             dv.setNowrap(nvl(dataColumn.getNowrap(),"null").equals("false")?"null":nvl(dataColumn.getNowrap(),"null"));
 825                         if (nvl(dataColumn.getDrillDownURL()).length() > 0) {
 826                 if(dv.getDisplayValue().length() > 0) {
 827                         dv.setDrillDownURL(parseDrillDownURL(i, /* dataColumnIdx, */ds, dataColumn,request, childReportFormFields));
 828                         dv.setDrillDowninPoPUp(dataColumn.isDrillinPoPUp()!=null?dataColumn.isDrillinPoPUp():false);
 829                 }
 830                                 if (dv.getDisplayValue().length() == 0) {
 831                     dv.setDisplayValue("");
 832                 }
 833                         }
 834
 835                         rd.setDataValue(rValues, cValues, cValuesSort.size()==0?null:cValuesSort, dv, formatProcessor, crossTabOrderManager, dataList);
 836                 }
 837                 rd.setReportDataList(dataList);
 838                 if (colDataTotalsCrosstab == null)
 839                         colDataTotalsCrosstab = generateDataTotalsCrossTab(AppConstants.CV_COLUMN, userId,request);
 840                 if (displayColTotals && colDataTotalsCrosstab != null)
 841                         rd.setColumnDataTotalsCrossTab(colDataTotalsCrosstab, dataColumn
 842                                         .getDisplayAlignment(), getCrossTabDisplayTotal(AppConstants.CV_COLUMN),
 843                                         crossTabOrderManager, dataList);
 844
 845                 if (rowDataTotalsCrosstab == null)
 846                         rowDataTotalsCrosstab = generateDataTotalsCrossTab(AppConstants.CV_ROW, userId, request);
 847                 if (displayRowTotals && rowDataTotalsCrosstab != null)
 848                         rd.setRowDataTotalsCrossTab(rowDataTotalsCrosstab, dataColumn
 849                                         .getDisplayAlignment(), getCrossTabDisplayTotal(AppConstants.CV_ROW),
 850                                         crossTabOrderManager, dataList);
 851
 852                 if (displayColTotals
 853                                 && displayRowTotals
 854                                 && getCrossTabDisplayTotal(AppConstants.CV_COLUMN).equals(
 855                                                 getCrossTabDisplayTotal(AppConstants.CV_ROW))) {
 856                         // Display grand total
 857                         if (grandTotalCrosstab == null)
 858                                 grandTotalCrosstab = ((CrossTabTotalValue) generateDataTotalsCrossTab("",
 859                                                 userId,request).get(0)).getTotalValue();
 860                         if (grandTotalCrosstab != null)
 861                                 rd.setGrandTotalCrossTab(Utils.truncateTotalDecimals(grandTotalCrosstab),
 862                                                 dataColumn.getDisplayAlignment(),
 863                                                 getCrossTabDisplayTotal(AppConstants.CV_COLUMN), dataList);
 864                 }
 865
 866                 rd.consolidateColumnHeaders(visualManager);
 867                 if (displayColTotals && colDataTotalsCrosstab != null) {
 868                         String totalLabel = "Total";
 869                         String colDisplayTotal = getCrossTabDisplayTotal(AppConstants.CV_COLUMN);
 870                         if (colDisplayTotal.length() > 0
 871                                         && (!colDisplayTotal.equals(AppConstants.TOTAL_SUM_ID)))
 872                                 totalLabel = nvl(AppConstants.TOTAL_FUNCTIONS.getNameById(colDisplayTotal));
 873                         if (getReportDataSize() > getPageSize())
 874                                 totalLabel += "_nl_(for all pages)";
 875
 876
 877                 }
 878                 DataRow drInFor1 = null;
 879                 Vector<DataValue> v1= null, v2 = null;
 880                 ArrayList<String> temp = new ArrayList<>();
 881                 if (Globals.getMergeCrosstabRowHeadings()) {
 882                         for (int i = 0; i < dataList.size(); i++) {
 883                                 drInFor1 = (DataRow)dataList.get(i);
 884                                 drInFor1.setRowNum(i+1);
 885                                 v1 = drInFor1.getRowValues();
 886                                 if(i<dataList.size()-1) {
 887                                         v2 = ((DataRow)dataList.get(i+1)).getRowValues();
 888                                 }
 889                                 for (int j = 0; j < v1.size(); j++) {
 890                                         if(j==0) {
 891                                                 if(v1.get(j).getDisplayValue().length()>0) { // another ArrayList
 892                                                         temp = new ArrayList();
 893                                                         temp.add(v1.get(j).getDisplayValue());
 894                                                 }
 895                                                 if(v2!=null && temp.get(j).equals(v2.get(j).getDisplayValue())) {
 896                                                         v2.get(j).setDisplayValue("");
 897                                                 }
 898                                         }
 899                                 }
 900                         }
 901                 }
 902         rd.setReportDataList(dataList);
 903                 if (getReportDataSize() < 0) {
 904                         setReportDataSize(rd.getReportDataList().size());
 905                 }
 906
 907                 return rd;
 908         }
 909
 910         public DataValue addDataValue(DataColumnType dataColumn, String columnValue) {
 911                 DataValue dv = new DataValue();
 912                 dv.setDisplayValue(columnValue);
 913                 if (nvl(dataColumn.getCrossTabValue()).trim().length()<=0)
 914                  dv.setVisible(false);
 915                 dv.setAlignment(dataColumn.getDisplayAlignment());
 916         dv.setDisplayTotal(dataColumn.getDisplayTotal());
 917         dv.setColName(dataColumn.getColName());
 918         dv.setDisplayName(dataColumn.getDisplayName());
 919         dv.setColId(dataColumn.getColId());
 920         dv.setNowrap(nvl(dataColumn.getNowrap(),"null").equals("false")?"null":nvl(dataColumn.getNowrap(),"null"));
 921                 return dv;
 922
 923         }
 924
 925  public boolean doesReportContainsGroupFormField() {
 926            int flag = 0;
 927                 if(getFormFieldList()!=null) {
 928                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 929                                 FormFieldType fft = (FormFieldType) iter.next();
 930                                 if(fft.isGroupFormField()!=null && fft.isGroupFormField().booleanValue()) {
 931                                         flag = 1;
 932                                         break;
 933                                 }
 934                         }
 935                 }
 936                 return (flag ==1);
 937         }
 938
 939         private ReportData loadLinearReportData(int pageNo, String userId, int downloadLimit, HttpServletRequest request, boolean download) throws RaptorException {
 940                 long startTime = System.currentTimeMillis();
 941                 logger.info("Session ID: " + request.getSession().getId());
 942         boolean isSameSession = false;
 943
 944                 String action = nvl(request.getParameter(AppConstants.RI_ACTION), request.getParameter("action"));;
 945                 String reportSQL = "";
 946                 if(action.endsWith("session")) {
 947                         reportSQL  = getWholeSQL();
 948                         wholeSQL = reportSQL;
 949                 } else {
 950                         reportSQL = generateSQL(reportParamValues, visualManager.getSortByColId(),
 951                                 visualManager.getSortByAscDesc(), userId, request);
 952                         wholeSQL = reportSQL;
 953                         setWholeSQL(wholeSQL);
 954                 }
 955                 DataSet ds = null;
 956                 String dbInfo = getDBInfo();
 957                 long runWHoleSqlStart = System.currentTimeMillis();
 958                 String SQLForCount = "SELECT count(*) FROM (" + reportSQL + ")"+ (Globals.isPostgreSQL() || Globals.isMySQL()?" AS ":"")  +" x ";
 959                 boolean isPageDowload = Boolean.valueOf(request.getParameter(AppConstants.PAGE_DOWNLOAD));
 960                 if(download) {
 961                           if(isPageDowload) {
 962                              ds = ConnectionUtils.getDataSet(cachedSQL, dbInfo);
 963                           }
 964                           else {
 965                                  String downloadAll = generateSubsetSQL(0, downloadLimit, userId, request, true, null);
 966                                  ds = ConnectionUtils.getDataSet(downloadAll, dbInfo);
 967                           }
 968                         }
 969                 long runWHoleSqlEnd = System.currentTimeMillis();
 970                 logger.info("Run whole sql to set reportDataSize took:"+ (runWHoleSqlEnd - runWHoleSqlStart)/1000F + "Seconds");
 971                 long hibernateSessionTimeStart = System.currentTimeMillis();
 972         HttpSession session = request.getSession();
 973         long hibernateSessionTimeEnd = System.currentTimeMillis();
 974         logger.info("Hibernate session time : " + (hibernateSessionTimeEnd - hibernateSessionTimeStart)/1000F + " Seconds");
 975         List reportCols = null;
 976         StringBuffer colNames = new StringBuffer();
 977
 978         if(download && action.endsWith("session")) {
 979             reportCols = getAllColumns();
 980             colNames = new StringBuffer();
 981             for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 982
 983                 DataColumnType dc = (DataColumnType) iter.next();
 984                         if (colNames.length() > 0)
 985                                 colNames.append(", ");
 986                         colNames.append(dc.getColId());
 987             }
 988
 989         }
 990         else {
 991                 String pagedSQL = null;
 992                 long genaratePageSqlStart = System.currentTimeMillis();
 993                 boolean getColumnNamesFromReportSQL = false;
 994                 if(Globals.getDBType().equals(AppConstants.ORACLE)) {
 995                         getColumnNamesFromReportSQL = true;
 996                 }
 997                 if (pageNo >= 0)
 998                         pagedSQL = generatePagedSQL(pageNo, userId, request, getColumnNamesFromReportSQL, null);
 999                 else
1000                         pagedSQL = generateSubsetSQL(0, downloadLimit, userId, request, getColumnNamesFromReportSQL, null);
1001                 long genaratePageSqlend = System.currentTimeMillis();
1002                 logger.info("generate paged sql took : " + (genaratePageSqlend - genaratePageSqlStart)/1000F + " Seconds");
1003         reportCols = getAllColumns();
1004         colNames = new StringBuffer();
1005         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1006
1007             DataColumnType dc = (DataColumnType) iter.next();
1008                         if (colNames.length() > 0)
1009                                 colNames.append(", ");
1010                         colNames.append(dc.getColId());
1011             if (dc.isVisible()) {
1012                reportSQL = parseReportSQLForDrillDownParams(reportSQL, dc, request);
1013                pagedSQL  = parseReportSQLForDrillDownParams(pagedSQL, dc, request);
1014             }
1015         }
1016
1017                 cachedSQL = pagedSQL;
1018
1019
1020
1021                 if(doesReportContainsGroupFormField()) {
1022                         if (pageNo >= 0)
1023                                 pagedSQL = generatePagedSQL(pageNo, userId, request, getColumnNamesFromReportSQL, reportParamValues);
1024                         else
1025                                 pagedSQL = generateSubsetSQL(0, downloadLimit, userId, request, getColumnNamesFromReportSQL, reportParamValues);
1026                 }
1027                 long start = System.currentTimeMillis();
1028                 ds = ConnectionUtils.getDataSet(pagedSQL, dbInfo);
1029                 long end = System.currentTimeMillis();
1030                 float sec = (end - start) / 1000F;
1031                 logger.info("Get page data : "+sec + " seconds");
1032                         if (ds.getRowCount() < getPageSize() && pageNo == 0) {
1033                                 setReportDataSize(ds.getRowCount());
1034                         } else {
1035                                 if (CachingUtils.isTotalCountAvailable(request.getSession().getId() + wholeSQL) && !download) {
1036                                         setReportDataSize(CachingUtils.getTotalRowsCount(request.getSession().getId() + wholeSQL));
1037                                 } else {
1038                                         DataSet countData = null;
1039                                         countData = ConnectionUtils.getDataSet(SQLForCount, dbInfo);
1040                                         setReportDataSize(countData.getInt(0, 0));
1041                                         CachingUtils.cacheTotalRowCount(request.getSession().getId() + wholeSQL, countData.getInt(0, 0));
1042                                 }
1043                         }
1044                 if(doesReportContainsGroupFormField()) {
1045                         List reportCols1 = getAllColumns();
1046                         reportCols = new Vector();
1047                         outer:
1048                         for (Iterator iter = reportCols1.iterator(); iter.hasNext();) {
1049                                 DataColumnType dct = (DataColumnType) iter.next();
1050                                 for (int k=0; k<ds.getColumnCount(); k++) {
1051                                         if(dct.getColId().toUpperCase().trim().equals(ds.getColumnName(k).trim())) {
1052                                                 reportCols.add(dct);
1053                                                 continue outer;
1054                                         }
1055                                 }
1056                         }
1057
1058                         if (getFormFieldList() != null) {
1059                                 String paramValue = "";
1060                                 for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
1061                                         FormFieldType fft = (FormFieldType) iter.next();
1062                                         if(fft.isGroupFormField()!=null && fft.isGroupFormField().booleanValue()) {
1063                                 paramValue = Utils.oracleSafe(nvl(reportParamValues
1064                                                 .getParamValue(fft.getFieldId())));
1065                                 outer:
1066                                         for (Iterator iter1 = reportCols1.iterator(); iter1.hasNext();) {
1067                                                 DataColumnType dct = (DataColumnType) iter1.next();
1068                                                         if(("["+fft.getFieldName()+ "]").equals(dct.getColName().trim())) {
1069                                                                 dct.setDisplayName(paramValue);
1070                                                                 continue outer;
1071                                                         }
1072                                         }
1073
1074
1075                                         }
1076                                 }
1077                         }
1078                 }
1079
1080                 if (getReportDataSize() < 0)
1081                         if (pageNo < 0)
1082                                 setReportDataSize(ds.getRowCount());
1083                         else if (ds.getRowCount() <= getPageSize())
1084                                 setReportDataSize(ds.getRowCount());
1085                         else {
1086
1087                                 String countSQL = "SELECT count(*) FROM (" + reportSQL + ")"+ (Globals.isPostgreSQL() || Globals.isMySQL()?" AS ":"")  +" x ";
1088                                 String dbType = "";
1089
1090                                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
1091                                         try {
1092                                          dbType = remDbInfo.getDBType(dbInfo);
1093                                          if (dbType.equals("DAYTONA") && reportSQL.trim().toUpperCase().startsWith("SELECT")) {
1094                                                 Pattern re1 = Pattern.compile("order(.*?[^\r\n]*)by", Pattern.DOTALL);
1095                                                 Matcher matcher = re1.matcher(reportSQL);
1096                                                 int startPoint = reportSQL.length()-30;
1097                                                 while(reportSQL.indexOf("xid", startPoint)!=-1)startPoint++;
1098                                                 if (matcher.find(startPoint)) {
1099                                                         reportSQL = reportSQL.substring(0, reportSQL.indexOf(matcher.group()));
1100                                                 }
1101                                                 countSQL = "SELECT count(*) FROM (" + reportSQL + ")"+ (Globals.isPostgreSQL() || Globals.isMySQL()?" AS ":"")  +" x";
1102                                                 countSQL = countSQL + " ("+ colNames+ ")";
1103                                          } else if (dbType.equals("DAYTONA")) {
1104                                                  setReportDataSize(50);
1105                                          }
1106                                         } catch (Exception ex) {
1107                                    throw new RaptorException(ex);
1108                                         }
1109                                 }
1110
1111                                 DataSet ds2 = null;
1112                                 if(reportSQL.trim().toUpperCase().startsWith("SELECT")) {
1113                                         long start1 = System.currentTimeMillis();
1114                                         ds2 = ConnectionUtils.getDataSet(countSQL, dbInfo);
1115                                         long end1 = System.currentTimeMillis();
1116                                         float sec1 = (end1 - start1) / 1000F; System.out.println("Get count data : "+sec1 + " seconds");
1117                                         if (ds2.getRowCount() > 0)
1118                                                 setReportDataSize(ds2.getInt(0, 0));
1119                                         else
1120                                                 throw new RuntimeException(
1121                                                                 "[ReportRuntime.loadLinearReportData] Unable to load report data size");
1122                                 } else
1123                                         setReportDataSize(50);
1124
1125
1126                         }
1127         }
1128                 ReportData rd = new ReportData(pageNo, true);
1129                 Vector visibleCols = new Vector(reportCols.size());
1130                 Vector formatProcessors = new Vector(reportCols.size());
1131
1132                 // ColumnHeaderRow chr = new ColumnHeaderRow();
1133                 // rd.reportColumnHeaderRows.addColumnHeaderRow(chr);
1134                 // chr.setRowHeight("30");
1135         int count =0 ;
1136                 ReportFormFields rff = getReportFormFields();
1137                 ReportFormFields childReportFormFields = null;
1138                 String fieldDisplayName = "";
1139                 String fieldValue = "";
1140
1141                 for (int c = 0; c < reportCols.size(); c++) {
1142             if(reportCols.get(c)!=null) {
1143                                 DataColumnType dct = (DataColumnType) reportCols.get(c);
1144                                 if(nvl(dct.getDependsOnFormField()).length()>0 && nvl(dct.getDependsOnFormField()).indexOf("[")!=-1) {
1145                                         for(int i = 0 ; i < rff.size(); i++) {
1146                                                 fieldDisplayName = "["+((FormField)rff.getFormField(i)).getFieldDisplayName()+"]";
1147                                                 fieldValue = "";
1148                                                 if (dct.getDependsOnFormField().equals(fieldDisplayName)) {
1149                                                         fieldValue = nvl(request.getParameter(((FormField)rff.getFormField(i)).getFieldName()));
1150
1151                                                         if (fieldValue.length()>0) {
1152                                                                 if(!fieldValue.toUpperCase().equals("Y"))
1153                                                                         dct.setDisplayName(fieldValue);
1154                                                                 if(!dct.isVisible())
1155                                                                         dct.setVisible(true);
1156                                                         } else {
1157                                                                 dct.setVisible(false);
1158                                                         }
1159                                                 }
1160                                         }
1161             }
1162                 }
1163                 }
1164                 String displayName = "";
1165                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1166
1167             DataColumnType dc = (DataColumnType) iter.next();
1168
1169                         formatProcessors.add(count,new FormatProcessor(
1170                                         getSemaphoreById(dc.getSemaphoreId()), dc.getColType(), dc
1171                                                         .getColFormat(), getReportDefType().equals(
1172                                                         AppConstants.RD_SQL_BASED)));
1173
1174                         if (nvl(dc.getDrillDownURL()).length() > 0) {
1175                                 if(!dc.getDrillDownURL().contains("c_master")) {
1176                                 childReportFormFields = getChildReportFormFields(request,AppUtils.getDrillActionURL()+""+dc.getDrillDownURL());
1177                                 }else {
1178                                       childReportFormFields = getChildReportFormFields(request,dc.getDrillDownURL());
1179                                 }
1180                         }
1181                         if (dc.isVisible()) {
1182                                 visibleCols.add(count,dc);
1183                                         for (int d = 0; d < reportCols.size(); d++) {
1184                                                 if(reportCols.get(d)!=null) {
1185                                                         DataColumnType dct1 = (DataColumnType) reportCols.get(d);
1186                                                         if(dct1.getColId().equals(dc.getColId()+"_name") && ds.getRowCount()>0) {
1187                                                                 displayName = ds.getString(0,dct1.getColId());
1188                                                                 dc.setDisplayName(displayName);
1189                                                         }
1190                                                 }
1191                                         }
1192                                 //}
1193
1194                                 String widthInPxls = dc.getDisplayWidthInPxls();
1195
1196                                 if(nvl(widthInPxls).endsWith("px"))
1197                                         dc.setDisplayWidthInPxls(widthInPxls);
1198                                 else {
1199                                         widthInPxls = widthInPxls+"px";
1200                                         dc.setDisplayWidthInPxls(widthInPxls+"px");
1201                                 }
1202
1203                                 rd.createColumn(dc.getColId(), dc.getDisplayName(), dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
1204                                                 visualManager.isColumnVisible(dc.getColId()), visualManager
1205                                                                 .getSortByColId().equals(dc.getColId()) ? visualManager
1206                                                                 .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);
1207                         }
1208             else {
1209               visibleCols.add(count,null);
1210                                 rd.createColumn(dc.getColId(), AppConstants.HIDDEN, dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
1211                                                 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);
1212             }
1213             count++;
1214                 }
1215
1216                 if(getReportDefType().equals(AppConstants.RD_SQL_BASED_DATAMIN) && pageNo ==0) {
1217
1218                 }
1219
1220                 for (int r = 0; r < Math.min(ds.getRowCount(), ((pageNo < 0) ? (downloadLimit == -1?Globals.getFlatFileUpperLimit():Globals.getDownloadLimit() ) : getPageSize())); r++) {
1221                         DataRow dr = new DataRow();
1222                         rd.reportDataRows.addDataRow(dr);
1223
1224                         for (int c = 0; c < reportCols.size(); c++) {
1225                 if(reportCols.get(c)!=null) {
1226                                 DataColumnType dct = (DataColumnType) reportCols.get(c);
1227                                 DataValue dv = new DataValue();
1228                                 dr.addDataValue(dv);
1229                                 String reverseDispalyValue = reverseDisplayValue(ds.getString(r, ds.getColumnIndex(dct.getColId())));
1230                                 reverseDispalyValue = StringUtils.substringBefore(reverseDispalyValue, ",");
1231                                 if(download)
1232                                         dv.setDisplayValue(reverseDispalyValue);
1233                                 else
1234                                 dv.setDisplayValue(ds.getString(r, ds.getColumnIndex(dct.getColId())));
1235                     dv.setColName(dct.getColName());
1236                     dv.setColId(dct.getColId());
1237                     dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
1238                     StringBuffer indentation = new StringBuffer("");
1239                     if(dct.getIndentation()!=null && dct.getIndentation()>0) {
1240                         for (int indent=0; indent< dct.getIndentation(); indent++) {
1241                                 indentation.append("\t");
1242                         }
1243                         dv.setNowrap("true");
1244                     }
1245                     dv.setIndentation(indentation.toString());
1246                     if(dct.isVisible()) {
1247
1248                         dv.setVisible(true);
1249                                         dv.setAlignment(dct.getDisplayAlignment());
1250                             dv.setDisplayTotal(dct.getDisplayTotal());
1251                             dv.setDisplayName(dct.getDisplayName());
1252
1253                                         if (nvl(dct.getDrillDownURL()).length() > 0) {
1254
1255                                 if(dv.getDisplayValue().length() > 0) {
1256                                         dv.setDrillDownURL(parseDrillDownURL(r, /* c, */ds, dct,request, childReportFormFields));
1257                                         dv.setDrillDowninPoPUp(dct.isDrillinPoPUp()!=null?dct.isDrillinPoPUp():false);
1258                                 }
1259
1260                                 if (dv.getDisplayValue().length() == 0) {
1261                                     dv.setDisplayValue("");
1262                                 }
1263                                         } // if
1264
1265                                 } else {
1266                                         dv.setVisible(false);
1267                                         dv.setHidden(true);
1268                                 }
1269                     if(dr.getFormatId()!=null)
1270                                  ((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, true);
1271                     else
1272                                  ((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, false);
1273                 }
1274                         }
1275                 }
1276
1277
1278                 if (colDataTotalsLinear == null) {
1279                         if(!download && !action.endsWith("session"))
1280                         colDataTotalsLinear = generateColumnDataTotalsLinear(new ArrayList(reportCols), userId,
1281                                         getDbInfo(),request);
1282                         if(download && action.endsWith("session"))
1283                         colDataTotalsLinear = generateColumnDataTotalsLinear(new ArrayList(reportCols), userId,
1284                                         getDbInfo(), getTotalSql());
1285                 }
1286                 if (displayColTotals && colDataTotalsLinear != null) {
1287                         String totalLabel = "Total";
1288                         if (getReportDataSize() > getPageSize())
1289                                 totalLabel += "<br><font size=1>(for all pages)</font>";
1290
1291                         rd.setColumnDataTotalsLinear(colDataTotalsLinear, totalLabel);
1292                 }
1293                 rd.applyVisibility();
1294                 long endTime = System.currentTimeMillis();
1295                 float totalTime = (endTime - startTime)/1000F;
1296                 logger.info("Linearreportdata took: " + totalTime +"Seconds");
1297                 return rd;
1298         }
1299
1300
1301
1302         public DataRow generateColumnDataTotalsLinear(ArrayList reportCols, String userId,
1303                         String dbInfo, String reportSQL) throws RaptorException {
1304                 DataRow dr = null;
1305
1306                 boolean displayColTotals = false;
1307                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1308
1309                         DataColumnType dct = (DataColumnType) iter.next();
1310             if( dct != null ) {
1311                         if (nvl(dct.getDisplayTotal()).length() > 0) {
1312                                 displayColTotals = true;
1313                                 break;
1314                         }
1315             }
1316                 }
1317
1318                 DataSet ds = null;
1319                 if (displayColTotals) {
1320                         dr = new DataRow();
1321                         ds = ConnectionUtils.getDataSet(reportSQL,
1322                                         dbInfo);
1323
1324                 for (int c = 0; c < reportCols.size(); c++) {
1325                         DataColumnType dct = (DataColumnType) reportCols.get(c);
1326             if ( dct != null ) {
1327                         DataValue dv = new DataValue();
1328
1329                         String totalValue = "";
1330                         if (ds != null)
1331                                 totalValue = ds.getString(0, c);
1332                         if (nvl(dct.getDisplayTotal()).length() > 0
1333                                         && (!dct.getDisplayTotal().equals(AppConstants.TOTAL_SUM_ID)))
1334                                 totalValue = nvl(AppConstants.TOTAL_FUNCTIONS.getNameById(dct
1335                                                 .getDisplayTotal()))
1336                                                 + ": " + totalValue;
1337                         dv.setDisplayValue(Utils.truncateTotalDecimals(totalValue));
1338
1339                         dv.setAlignment(dct.getDisplayAlignment());
1340                 dv.setColName(dct.getColName());
1341                 dv.setDisplayName(dct.getDisplayName());
1342                 dv.setColId(dct.getColId());
1343                 dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
1344                 StringBuffer indentation = new StringBuffer("");
1345                 if(dct.getIndentation()!=null && dct.getIndentation()>0) {
1346                     for (int indent=0; indent< dct.getIndentation(); indent++) {
1347                         indentation.append("\t");
1348                     }
1349                     dv.setNowrap("true");
1350                 }
1351                 dv.setIndentation(indentation.toString());
1352                 dv.setDisplayTotal(dct.getDisplayTotal());
1353                         dv.setBold(true);
1354                         dv.setVisible(dct.isVisible());
1355                         if(dv.isVisible())
1356                                 dr.addDataValue(dv);
1357             }
1358                 }
1359                 }
1360
1361                 return dr;
1362         }
1363
1364
1365         public ReportData loadHiveLinearReportData(String reportSQL, String userId, int downloadLimit, HttpServletRequest request) throws RaptorException {
1366         wholeSQL = reportSQL;
1367         int countRows = getHiveReportCount(wholeSQL);
1368         setReportDataSize(countRows);
1369         if(countRows < 1001)
1370                 wholeSQL += " limit "+ countRows;
1371         else
1372                 wholeSQL += " limit "+ downloadLimit;
1373         HttpSession session = request.getSession();
1374
1375                 DataSet ds = null;
1376                 String dbInfo = getDBInfo();
1377
1378         List reportCols = getAllColumns();
1379         StringBuffer colNames = new StringBuffer();
1380         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1381
1382             DataColumnType dc = (DataColumnType) iter.next();
1383                         if (colNames.length() > 0)
1384                                 colNames.append(", ");
1385                         colNames.append(dc.getColId());
1386         }
1387
1388                 ds = ConnectionUtils.getDataSet(wholeSQL, dbInfo);
1389
1390                 ReportData rd = new ReportData(0, true);
1391                 Vector visibleCols = new Vector(reportCols.size());
1392                 Vector formatProcessors = new Vector(reportCols.size());
1393         int count =0 ;
1394
1395
1396                 ReportFormFields rff = getReportFormFields();
1397                 ReportFormFields childReportFormFields = null;
1398                 String fieldDisplayName = "";
1399                 String fieldValue = "";
1400
1401                 for (int c = 0; c < reportCols.size(); c++) {
1402             if(reportCols.get(c)!=null) {
1403                                 DataColumnType dct = (DataColumnType) reportCols.get(c);
1404                                 if(nvl(dct.getDependsOnFormField()).length()>0 && nvl(dct.getDependsOnFormField()).indexOf("[")!=-1) {
1405                                         for(int i = 0 ; i < rff.size(); i++) {
1406                                                 fieldDisplayName = "["+((FormField)rff.getFormField(i)).getFieldDisplayName()+"]";
1407                                                 fieldValue = "";
1408                                                 if (dct.getDependsOnFormField().equals(fieldDisplayName)) {
1409                                                         fieldValue = nvl(request.getParameter(((FormField)rff.getFormField(i)).getFieldName()));
1410
1411                                                         if (fieldValue.length()>0) {
1412                                                                 if(!fieldValue.toUpperCase().equals("Y"))
1413                                                                         dct.setDisplayName(fieldValue);
1414                                                                 if(!dct.isVisible())
1415                                                                         dct.setVisible(true);
1416                                                         } else {
1417                                                                 dct.setVisible(false);
1418                                                         }
1419                                                 }
1420                                         }
1421             }
1422                 }
1423                 }
1424
1425                 String displayName = "";
1426                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1427
1428             DataColumnType dc = (DataColumnType) iter.next();
1429
1430                         formatProcessors.add(count,new FormatProcessor(
1431                                         getSemaphoreById(dc.getSemaphoreId()), dc.getColType(), dc
1432                                                         .getColFormat(), getReportDefType().equals(
1433                                                         AppConstants.RD_SQL_BASED)));
1434
1435                         if (nvl(dc.getDrillDownURL()).length() > 0) {
1436                                 childReportFormFields = getChildReportFormFields(request,dc.getDrillDownURL());
1437                         }
1438                         if (dc.isVisible()) {
1439                                 visibleCols.add(count,dc);
1440                                         for (int d = 0; d < reportCols.size(); d++) {
1441                                                 if(reportCols.get(d)!=null) {
1442                                                         DataColumnType dct1 = (DataColumnType) reportCols.get(d);
1443                                                         if(dct1.getColId().equals(dc.getColId()+"_name") && ds.getRowCount()>0) {
1444                                                                 displayName = ds.getString(0,dct1.getColId());
1445                                                                 dc.setDisplayName(displayName);
1446                                                         }
1447                                                 }
1448                                         }
1449                                 //}
1450
1451                                 String widthInPxls = dc.getDisplayWidthInPxls();
1452
1453                                 if(nvl(widthInPxls).endsWith("px"))
1454                                         dc.setDisplayWidthInPxls(widthInPxls);
1455                                 else {
1456                                         widthInPxls = widthInPxls+"px";
1457                                         dc.setDisplayWidthInPxls(widthInPxls+"px");
1458                                 }
1459
1460                                 rd.createColumn(dc.getColId(), dc.getDisplayName(), dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
1461                                                 visualManager.isColumnVisible(dc.getColId()), visualManager
1462                                                                 .getSortByColId().equals(dc.getColId()) ? visualManager
1463                                                                 .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);
1464
1465                         }
1466             else {
1467               visibleCols.add(count,null);
1468                                 rd.createColumn(dc.getColId(), "", dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
1469                                                 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);
1470             }
1471             count++;
1472                 }
1473
1474                 ArrayList reportDataList = new ArrayList();
1475                 for (int r = 0; r < ds.getRowCount(); r++) {
1476                         DataRow dr = new DataRow();
1477                         rd.reportDataRows.addDataRow(dr);
1478
1479                         for (int c = 0; c < reportCols.size(); c++) {
1480                 if(reportCols.get(c)!=null) {
1481                                 DataColumnType dct = (DataColumnType) reportCols.get(c);
1482                                 DataValue dv = new DataValue();
1483                                 dr.addDataValue(dv);
1484                                 dv.setDisplayValue(ds.getString(r, c));
1485                     dv.setColName(dct.getColName());
1486                     dv.setColId(dct.getColId());
1487                     dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
1488                     StringBuffer indentation = new StringBuffer("");
1489                     if(dct.getIndentation()!=null && dct.getIndentation()>0) {
1490                         for (int indent=0; indent< dct.getIndentation(); indent++) {
1491                                 indentation.append("\t");
1492                         }
1493                         dv.setNowrap("true");
1494                     }
1495                     dv.setIndentation(indentation.toString());
1496                     if(dct.isVisible()) {
1497
1498                         dv.setVisible(true);
1499                                         dv.setAlignment(dct.getDisplayAlignment());
1500                             dv.setDisplayTotal(dct.getDisplayTotal());
1501                             dv.setDisplayName(dct.getDisplayName());
1502
1503                                         if (nvl(dct.getDrillDownURL()).length() > 0) {
1504
1505                                 if(dv.getDisplayValue().length() > 0) {
1506                                         dv.setDrillDownURL(parseDrillDownURL(r, /* c, */ds, dct,request, childReportFormFields));
1507                                         dv.setDrillDowninPoPUp(dct.isDrillinPoPUp()!=null?dct.isDrillinPoPUp():false);
1508                                 }
1509
1510                                 if (dv.getDisplayValue().length() == 0) {
1511                                     dv.setDisplayValue("");
1512                                 }
1513                                         }
1514
1515                                 } else {
1516                                         dv.setVisible(false);
1517                                         dv.setHidden(true);
1518                                 }
1519
1520                     if(dr.getFormatId()!=null)
1521                                  ((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, true);
1522                     else
1523                                  ((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, false);
1524                 }
1525                         }
1526                         reportDataList.add(dr);
1527                 }
1528                 rd.setReportDataList(reportDataList);
1529
1530                 if (colDataTotalsLinear == null)
1531                         colDataTotalsLinear = generateColumnDataTotalsLinear(new ArrayList(reportCols), userId,
1532                                         getDbInfo(),request);
1533                 if (displayColTotals && colDataTotalsLinear != null) {
1534                         String totalLabel = "Total";
1535                         if (getReportDataSize() > getPageSize())
1536                                 totalLabel += "<br><font size=1>(for all pages)</font>";
1537
1538                         rd.setColumnDataTotalsLinear(colDataTotalsLinear, totalLabel);
1539                 }
1540
1541                 rd.applyVisibility();
1542
1543                 return rd;
1544         }
1545
1546
1547         public int getHiveReportCount(String sql) throws RaptorException {
1548                 int count = 0;
1549         String countSql = "select t from (select count(*) t from ("+ sql + ")" +  (Globals.isPostgreSQL() || Globals.isMySQL() ?" AS ":"")   + " x) AS x1";
1550
1551         DataSet ds = null;
1552                 String dbInfo = getDBInfo();
1553                 System.out.println("SQL getReportCount()- " + countSql);
1554                 try {
1555                         ds = ConnectionUtils.getDataSet(countSql, dbInfo);
1556                         int totalRows = 0;
1557                         String dbType = "";
1558                         if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
1559                                 try {
1560                                         dbType = remDbInfo.getDBType(dbInfo);
1561                                 } catch (Exception ex) {
1562                                         throw new RaptorException(ex);
1563                                 }
1564                         }
1565                         if( ds.getRowCount()>0) {
1566                                 count = Integer.parseInt(ds.getString(0,0));
1567                         }
1568                 } catch (NumberFormatException ex) {}
1569                 return count;
1570
1571         }
1572
1573
1574  public ReportFormFields getChildReportFormFields( HttpServletRequest request, String URL ) throws RaptorException {
1575                 String childReportID = getReportID(URL);
1576
1577                 ReportRuntime ddRr = (new ReportHandler()).loadReportRuntime(request, childReportID,
1578                                 false, 1);
1579
1580                 ReportFormFields ddReportFormFields = ddRr.getReportFormFields();
1581                 return ddReportFormFields;
1582         }
1583
1584         private String getReportID(String URL) {
1585                 URL = nvl(URL);
1586                 int pos = URL.toLowerCase().indexOf("c_master=")+9;
1587                 String reportID = "";
1588                 if(URL.toLowerCase().indexOf("&", pos)!=-1)
1589                         reportID = URL.substring(pos, URL.toLowerCase().indexOf("&", pos));
1590                 else
1591                         reportID = URL.substring(pos);
1592                 return reportID;
1593
1594         }
1595 public String parseDrillDownURL(int rowIdx, /* int colIdx, */DataSet ds, DataColumnType dct, HttpServletRequest request, ReportFormFields ddReportFormFields)
1596                         throws RaptorException {
1597                 Vector viewActions = DataCache.getDataViewActions();
1598                 javax.servlet.http.HttpSession session = request.getSession();
1599
1600                 StringBuffer dUrl = new StringBuffer();
1601                 boolean isViewAction = false;
1602                 int flag = 0;
1603                 String requestParam ="";
1604                 for (int k = 0; k < viewActions.size(); k++)
1605                         if (dct.getDrillDownURL().equals(
1606                                         AppUtils.getBaseActionURL() + ((String) viewActions.get(k))))
1607                                 isViewAction = true;
1608                 if (isViewAction) {
1609                         String param = nvl(dct.getDrillDownParams());
1610                         param = param.substring(AppUtils.getBaseActionParam().length() + 1,
1611                                         param.length() - 1);
1612                         param = param.replace('.', '_');
1613
1614                         dUrl.append(AppUtils.getBaseActionParam());
1615                         dUrl.append(java.net.URLEncoder.encode(ds.getString(rowIdx, param.toLowerCase())));
1616                 } else {
1617                         String param = nvl(dct.getDrillDownParams());
1618                         while (param.indexOf('[') >= 0) {
1619                                 int startIdx = param.indexOf('[');
1620                                 int endIdx = param.indexOf(']');
1621                                 StringBuffer sb = new StringBuffer();
1622                                 if(startIdx>endIdx) {
1623                                         if (endIdx < param.length() - 1)
1624                                                 sb.append(param.substring(endIdx + 1));
1625                                         param = sb.toString();
1626                     continue;
1627                                 }
1628                                 if (startIdx > 0)
1629                                         sb.append(param.substring(0, startIdx));
1630
1631                                 if (param.charAt(startIdx + 1) == '!') {
1632                                         String fieldId = param.substring(startIdx + 2, endIdx);
1633                                         String fieldValue = (String) reportParamValues.get(fieldId);
1634
1635                                         sb.append(java.net.URLEncoder.encode(nvl(fieldValue)));
1636                 }else if (param.charAt(startIdx + 1) == '#') {
1637                     flag = 1;
1638                     String fieldId = param.substring(startIdx + 2, endIdx);
1639                     String fieldValue = request.getParameter(fieldId);
1640                     sb.append(java.net.URLEncoder.encode(nvl(fieldValue)));
1641
1642                 }else {
1643                                         String fieldValue = "";
1644                                         String colValue = null;
1645                                         String colId = null;
1646                                         if (param.indexOf('!') < 0 || param.indexOf('!') > endIdx)
1647                                                 colId = param.substring(startIdx + 1, endIdx);
1648                                         else {
1649                                                 colId = param.substring(startIdx + 1, param.indexOf('!'));
1650
1651                                                 String fieldId = param.substring(param.indexOf('!') + 1, endIdx);
1652                                                 FormField ff = getFormField(fieldId);
1653                                                 if (ff.getFieldType().equals(FormField.FFT_TEXTAREA)) {
1654                                                         fieldValue = reportParamValues.getParamValueforTextAreaDrilldown(fieldId);
1655                                                 } else
1656                                                         fieldValue = (String) reportParamValues.get(fieldId);
1657                                         }
1658
1659                                         DataColumnType column = getColumnById(colId);
1660                                         String columnName = "";
1661                                         int groupColumn = 0;
1662                                         int groupMatch = 0;
1663                                         if(column.getColName().startsWith("[")) {
1664                                                 groupColumn = 1;
1665                                                 columnName = column.getDisplayName();
1666                                                 for(ddReportFormFields.resetNext(); ddReportFormFields.hasNext(); ) {
1667                                                         FormField ff = ddReportFormFields.getNext();
1668                                                         if(ff.getFieldDisplayName().toLowerCase().equals(columnName.toLowerCase())) {
1669                                                                 groupMatch = 1;
1670                                                                 sb.delete(sb.lastIndexOf("&")+1, sb.length());
1671                                                                 sb.append(ff.getFieldName()+"=");
1672                                                         }
1673                                                 }
1674                                         }
1675                                         if (groupColumn == 0 || (groupColumn == 1 && groupMatch == 1)) {
1676                                                 String dependsOn = column.getDependsOnFormField();
1677                                                 if(nvl(dependsOn).length()>0)
1678                                                         System.out.println("DependsOn " + dependsOn);
1679                                                 if (column != null) {
1680                                                         colValue = ds.getString(rowIdx, colId.toLowerCase());
1681                                                         if (getReportDefType().equals(AppConstants.RD_SQL_BASED))
1682                                                                 if (!getColumnNoParseDateFlag(column))
1683                                                                         if (ReportParamDateValueParser.isDateParam(colValue))
1684                                                                                 colValue = ReportParamDateValueParser
1685                                                                                                 .formatDateParamValue(colValue);
1686                                                 }
1687
1688                                                 String suppressValues = "|" + nvl(dct.getDrillDownType()) + "|";
1689                                                 if (suppressValues.length() > 2
1690                                                                 && suppressValues.indexOf("|" + colValue + "|") >= 0)
1691                                                         colValue = null;
1692
1693                                                 sb.append(java.net.URLEncoder.encode(nvl(colValue, fieldValue)));
1694                                         } else {
1695                                                 sb.delete(sb.lastIndexOf("&")+1, sb.length());
1696                                         }
1697                                 }
1698
1699                                 if (endIdx < param.length() - 1)
1700                                         sb.append(param.substring(endIdx + 1));
1701                                 param = sb.toString();
1702                         }
1703             if(Globals.getPassRequestParamInDrilldown()) {
1704                 if(param.indexOf('#') < 0) {
1705                     String[] reqParameters = Globals.getRequestParams().split(",");
1706                     String[] sessionParameters = Globals.getSessionParams().split(",");
1707                     for (int i = 0; i < reqParameters.length; i++) {
1708                         if(request.getParameter(reqParameters[i])!=null) {
1709                            if(!reqParameters[i].toUpperCase().startsWith("FF")){
1710                                 if(param.length()>0) {
1711                                   param += "&" + reqParameters[i]+"="
1712                                         + request.getParameter(reqParameters[i]);
1713                                 } else {
1714                                     param += "&" + reqParameters[i]+"="
1715                                     + request.getParameter(reqParameters[i]);
1716
1717                                 }
1718                            }
1719                         }
1720                     }
1721                     for (int i = 0; i < sessionParameters.length; i++) {
1722                         if(session.getAttribute(sessionParameters[i].toUpperCase())!=null) {
1723                            if(!sessionParameters[i].toUpperCase().startsWith("FF")){
1724                                 if(param.length()>0) {
1725                                   param += "&" + sessionParameters[i].toUpperCase()+"="
1726                                         + (String)session.getAttribute(sessionParameters[i].toUpperCase());
1727                                 } else {
1728                                     param += "&" + sessionParameters[i].toUpperCase()+"="
1729                                     + (String)session.getAttribute(sessionParameters[i].toUpperCase());
1730
1731                                 }
1732                            }
1733                         } else {
1734                             param += "&" + sessionParameters[i].toUpperCase()+"="
1735                             + (String)session.getAttribute(sessionParameters[i]);
1736
1737                         }
1738                     }
1739
1740                 }
1741             }
1742
1743                         dUrl.append(param.toString());
1744                         dUrl.append("&");
1745                         dUrl.append(AppConstants.RI_DISPLAY_CONTENT);
1746                         dUrl.append("=Y");
1747                         dUrl.append("&");
1748                         if(dct.isDrillinPoPUp()==null || (!dct.isDrillinPoPUp().booleanValue())) {
1749                                 dUrl.append(AppConstants.RI_SHOW_BACK_BTN);
1750                                 dUrl.append("=Y");
1751                                 dUrl.append("&");
1752                         }
1753                         dUrl.append(AppConstants.DRILLDOWN_INDEX);
1754                         int index = Integer.parseInt(nvl(AppUtils.getRequestValue(request, AppConstants.DRILLDOWN_INDEX), "0"));
1755                 request.setAttribute(AppConstants.DRILLDOWN_INDEX, Integer.toString(index));
1756                         dUrl.append("=" + AppUtils.getRequestNvlValue(request, AppConstants.DRILLDOWN_INDEX));
1757                 }
1758
1759                 if (dUrl.length() > 0)
1760                         dUrl.insert(0, ((dct.getDrillDownURL()).indexOf('&') > 0) ? '&' : '&');
1761                 dUrl.insert(0, AppUtils.getDrillActionURL()+dct.getDrillDownURL());
1762
1763                 return dUrl.toString();
1764         }
1765
1766         /** *********************************************************************************** */
1767
1768         public DataRow generateColumnDataTotalsLinear(ArrayList reportCols, String userId,
1769                         String dbInfo, HttpServletRequest request) throws RaptorException {
1770                 DataRow dr = null;
1771
1772                 displayColTotals = false;
1773                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1774
1775                         DataColumnType dct = (DataColumnType) iter.next();
1776             if( dct != null ) {
1777                         if (nvl(dct.getDisplayTotal()).length() > 0) {
1778                                 displayColTotals = true;
1779                                 break;
1780                         }
1781             }
1782                 }
1783
1784                 DataSet ds = null;
1785                 if (displayColTotals) {
1786                         dr = new DataRow();
1787                         ds = ConnectionUtils.getDataSet(generateTotalSQLLinear(reportParamValues, userId,request),
1788                                         dbInfo);
1789
1790                 for (int c = 0; c < reportCols.size(); c++) {
1791                         DataColumnType dct = (DataColumnType) reportCols.get(c);
1792             if ( dct != null ) {
1793                         DataValue dv = new DataValue();
1794                         if(dv.isVisible())
1795                                 dr.addDataValue(dv);
1796
1797                         String totalValue = "";
1798                         if (ds != null)
1799                                 totalValue = ds.getString(0, c);
1800                         if (nvl(dct.getDisplayTotal()).length() > 0
1801                                         && (!dct.getDisplayTotal().equals(AppConstants.TOTAL_SUM_ID)))
1802                                 totalValue = nvl(AppConstants.TOTAL_FUNCTIONS.getNameById(dct
1803                                                 .getDisplayTotal()))
1804                                                 + ": " + totalValue;
1805                         if( nvl(dct.getDisplayTotal()).length() > 0 && dct.getDisplayTotal().equals(AppConstants.TOTAL_SUM_ID)){
1806                                 totalValue = "Total: "+ totalValue;
1807                         }
1808                         dv.setDisplayValue(Utils.truncateTotalDecimals(totalValue));
1809
1810                         dv.setAlignment(dct.getDisplayAlignment());
1811                 dv.setColName(dct.getColName());
1812                 dv.setDisplayName(dct.getDisplayName());
1813                 dv.setColId(dct.getColId());
1814                 dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
1815                 StringBuffer indentation = new StringBuffer("");
1816                 if(dct.getIndentation()!=null && dct.getIndentation()>0) {
1817                     for (int indent=0; indent< dct.getIndentation(); indent++) {
1818                         indentation.append("\t");
1819                     }
1820                     dv.setNowrap("true");
1821                 }
1822                 dv.setIndentation(indentation.toString());
1823                 dv.setDisplayTotal(dct.getDisplayTotal());
1824                         dv.setBold(true);
1825             }
1826                 }
1827                 }
1828
1829                 return dr;
1830         }
1831
1832         private Vector generateDataTotalsCrossTab(String rowColPos, String userId, HttpServletRequest request)
1833                         throws RaptorException {
1834                 String sql = getWholeSQL();
1835                 Vector dataTotals = new Vector();
1836
1837                 boolean displayTotals = ((rowColPos.length() == 0) || (getCrossTabDisplayTotal(
1838                                 rowColPos).length() > 0));
1839                 if (rowColPos.equals(AppConstants.CV_COLUMN))
1840                         displayColTotals = displayTotals;
1841                 else if (rowColPos.equals(AppConstants.CV_ROW))
1842                         displayRowTotals = displayTotals;
1843
1844                 if (displayTotals) {
1845                         String executeSql = generateTotalSQLCrossTab(
1846                                         sql, rowColPos, userId, request, reportParamValues);
1847                         DataSet ds = ConnectionUtils.getDataSet(executeSql, getDbInfo());
1848
1849                         for (int i = 0; i < ds.getRowCount(); i++) {
1850                                 Vector headerValues = new Vector();
1851                                 String totalValue = null;
1852
1853                                 int cPos = 0;
1854                                 for (Iterator iter = getAllColumns().iterator(); iter.hasNext();) {
1855                                         DataColumnType dct = (DataColumnType) iter.next();
1856
1857                                         if (rowColPos.length() > 0
1858                                                         && nvl(dct.getCrossTabValue()).equals(rowColPos)) {
1859                                                 DataValue dataValue = new DataValue();
1860                                                 dataValue.setBold(true);
1861                                                 dataValue.setAlignment("center");
1862                                                 dataValue.setDisplayValue(ds.getString(i, cPos++));
1863                                                 headerValues.add(dataValue);
1864                                         }
1865                                         else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_VALUE))
1866                                                 totalValue = ds.getString(i, cPos++);
1867                                 }
1868
1869                                 dataTotals.add(new CrossTabTotalValue(headerValues, Utils
1870                                                 .truncateTotalDecimals(totalValue)));
1871                         }
1872                 }
1873
1874                 return dataTotals;
1875         }
1876         /** *********************************************************************************** */
1877
1878         public void hideColVisual(String colId) {
1879                 visualManager.hideColumn(colId);
1880                 if (pageDataCache != null)
1881                         pageDataCache.columnVisualShowHide(colId, false);
1882         }
1883
1884         public void showColVisual(String colId) {
1885                 visualManager.showColumn(colId);
1886                 if (pageDataCache != null)
1887                         pageDataCache.columnVisualShowHide(colId, true);
1888         }
1889
1890         public void sortColVisual(String colId) {
1891                 visualManager.setSortByColumn(colId);
1892                 resetCache(true);
1893
1894                 pageDataCache = null;
1895                 cachedPageNo = -1;
1896         }
1897
1898         /** *********************************************************************************** */
1899
1900         public String generateDistinctValuesSQL(DataColumnType dct, String userId, HttpServletRequest request) throws RaptorException  {
1901                 return super.generateDistinctValuesSQL(reportParamValues, dct, userId, request);
1902         }
1903
1904         public String getDbInfo() {
1905                 return this.cr.getDbInfo();
1906         }
1907
1908     public String parseReportSQL(String sql) throws RaptorException {
1909         StringBuffer parsedSQL = new StringBuffer();
1910
1911         Vector updatedReportCols = new Vector();
1912
1913         curSQLParsePos = 0;
1914         int lastParsePos = curSQLParsePos;
1915         String lastToken = null;
1916         logger.debug(EELFLoggerDelegate.debugLogger, ("Flat File parseReportSQL ******* SQL  " + sql));
1917                 sql = sql.replaceAll("([\\s]*\\() (?!FROM)", "(");
1918                 sql = sql.replaceAll("[\\s]*\\)", ")");
1919
1920         String  nextToken       = getNextSQLParseToken(sql, true);
1921         String  dbInfo          = getDbInfo();
1922         boolean isCYMBALScript = false;
1923                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
1924                         try {
1925                          String dbType = remDbInfo.getDBType(dbInfo);
1926                          if (dbType.equals("DAYTONA") && !(nextToken.toUpperCase().equals("SELECT"))) {
1927                                  isCYMBALScript = true;
1928                          }
1929                         } catch (Exception ex) {
1930                    throw new RaptorException(ex);
1931                         }
1932                 }
1933                 if ( isCYMBALScript == false ) {
1934                 while (nextToken.length() > 0) {
1935                     if (parsedSQL.length() == 0) {
1936                         if (nextToken.toUpperCase().equals("SELECT"))
1937                             parsedSQL.append("SELECT ");
1938                         else
1939                             throw new org.onap.portalsdk.analytics.error.ValidationException(
1940                                     "The SQL must start with the SELECT keyword.");
1941                     } else if (nextToken.toUpperCase().equals("DISTINCT")
1942                             && parsedSQL.toString().equals("SELECT ")) {
1943                         parsedSQL.append("DISTINCT ");
1944                     } else if (nextToken.equals("*")
1945                             && (parsedSQL.toString().equals("SELECT ") || parsedSQL.toString().equals(
1946                                     "SELECT DISTINCT "))) {
1947                         throw new org.onap.portalsdk.analytics.error.ValidationException(
1948                                 "You cannot use \"SELECT *\". Please specify select columns/expressions.");
1949                     } else if (nextToken.toUpperCase().equals("FROM")) {
1950                         if (lastToken != null) {
1951                             updatedReportCols.add(getParseSQLDataColumn(lastToken, null, parsedSQL,
1952                                     updatedReportCols, false));
1953                             lastToken = null;
1954                         }
1955
1956                         parsedSQL.append(" \n");
1957                         while (lastParsePos < sql.length()
1958                                 && Character.isWhitespace(sql.charAt(lastParsePos)))
1959                             lastParsePos++;
1960                         parsedSQL.append(sql.substring(lastParsePos));
1961                         break;
1962                     } else {
1963                         if (nextToken.charAt(nextToken.length() - 1) == ',') {
1964                             nextToken = nextToken.substring(0, nextToken.length() - 1);
1965
1966                             if (nextToken.length() == 0) {
1967                                 if (lastToken != null) {
1968                                     updatedReportCols.add(getParseSQLDataColumn(lastToken, null,
1969                                             parsedSQL, updatedReportCols, false));
1970                                     lastToken = null;
1971                                 }
1972                             } else {
1973                                 if (lastToken != null) {
1974                                     updatedReportCols.add(getParseSQLDataColumn(lastToken, nextToken,
1975                                             parsedSQL, updatedReportCols, false));
1976                                     lastToken = null;
1977                                 } else
1978                                     updatedReportCols.add(getParseSQLDataColumn(nextToken, null,
1979                                             parsedSQL, updatedReportCols, false));
1980                             }
1981                         } else {
1982                             if (lastToken == null)
1983                                 lastToken = nextToken;
1984                             else {
1985                                 String token = getNextSQLParseToken(sql, false);
1986                                 if (!token.toUpperCase().equals("FROM"))
1987                                     throw new org.onap.portalsdk.analytics.error.ValidationException(
1988                                             "|FROM keyword or a comma expected after [" + nextToken
1989                                                     + "].");
1990                                     updatedReportCols.add(getParseSQLDataColumn(lastToken, nextToken,
1991                                         parsedSQL, updatedReportCols, false));
1992                                 lastToken = null;
1993                             }
1994                         }
1995                     }
1996
1997                     lastParsePos = curSQLParsePos;
1998                     nextToken = getNextSQLParseToken(sql, true);
1999                 }
2000                 }  else {
2001                         nextToken = getNextCYMBALSQLParseToken(sql, true);
2002                         Pattern re                      = null;
2003                         Matcher matcher         = null;
2004                         String extracted        = null;
2005                         while (nextToken.length() > 0) {
2006                                 if (lastToken == null) lastToken = nextToken;
2007
2008                                 if( lastToken.toUpperCase().equals("DO DISPLAY")) {
2009                                         re              = Pattern.compile("each(.*)\\[.(.*?)\\]");
2010                                         matcher = re.matcher(nextToken);
2011                                         if (matcher.find()) {
2012                                                 extracted = matcher.group();
2013                                                 re              = Pattern.compile("\\[(.*?)\\]");
2014                                         matcher = re.matcher(nextToken);
2015                                         if(matcher.find()) {
2016                                                 extracted = matcher.group();
2017                                                 extracted = extracted.substring(1,extracted.length()-2);
2018                                                 StringTokenizer sToken = new StringTokenizer(extracted);
2019                                                 while(sToken.hasMoreTokens()) {
2020                                             updatedReportCols.add(getParseSQLDataColumn("", sToken.nextToken(),
2021                                                         new StringBuffer(""), updatedReportCols, true));
2022                                                 }
2023                                         }
2024
2025                                         }
2026
2027                                 }
2028                                 lastToken = nextToken;
2029                                 nextToken = getNextCYMBALSQLParseToken(sql, true);
2030                         }
2031                 }
2032
2033         if (updatedReportCols.size() == 0)
2034             throw new org.onap.portalsdk.analytics.error.ValidationException(
2035                     "The SQL statement must have at least one column in the SELECT clause.");
2036
2037
2038         return parsedSQL.toString();
2039
2040     }
2041
2042         private String getNextCYMBALSQLParseToken(String sql, boolean updateParsePos) {
2043                 int braketCount = 0;
2044                 boolean isInsideQuote = false;
2045                 StringBuffer nextToken = new StringBuffer();
2046                 for (int idxNext = curSQLParsePos; idxNext < sql.length(); idxNext++) {
2047                         char ch = sql.charAt(idxNext);
2048
2049                         if (ch!='\n')
2050                                         nextToken.append(ch);
2051                         else break;
2052                 }
2053
2054                 return nextToken.toString();
2055         }
2056     private String getNextSQLParseToken(String sql, boolean updateParsePos) {
2057         int braketCount = 0;
2058         boolean isInsideQuote = false;
2059         StringBuffer nextToken = new StringBuffer();
2060         for (int idxNext = curSQLParsePos; idxNext < sql.length(); idxNext++) {
2061             char ch = sql.charAt(idxNext);
2062
2063             if (Character.isWhitespace(ch) || ch == ',') {
2064                 if (ch == ',')
2065                     nextToken.append(ch);
2066
2067                 if (nextToken.length() == 0)
2068                     continue;
2069                 else if (braketCount == 0 && (!isInsideQuote)) {
2070                     if (updateParsePos)
2071                         curSQLParsePos = idxNext + ((ch == ',') ? 1 : 0);
2072                     break;
2073                 } else if (ch != ',' && nextToken.charAt(nextToken.length() - 1) != ' ')
2074                     nextToken.append(' ');
2075             } else {
2076                 nextToken.append(ch);
2077
2078                 if (ch == '(' || ch == '[')
2079                     braketCount++;
2080                 else if (ch == ')' || ch == ']')
2081                     braketCount--;
2082                 else if (ch == '\''/* ||ch=='\"' */)
2083                     isInsideQuote = (!isInsideQuote);
2084             }
2085         }
2086
2087         return nextToken.toString();
2088     }
2089     private DataColumnType getParseSQLDataColumn(String sqlExpression, String colId,
2090             StringBuffer parsedSQL, Vector updatedReportCols, boolean isCYMBALScript) throws RaptorException {
2091         DataColumnType dct = null;
2092
2093         if (colId != null) {
2094             if (!isParseSQLColID(colId))
2095                 throw new org.onap.portalsdk.analytics.error.ValidationException(
2096                         "["
2097                                 + colId
2098                                 + "] must either be a valid column id consisting only of letters, numbers, and underscores, or there must be a comma in front of it.");
2099
2100             dct = getColumnById(colId);
2101         } else {
2102
2103             colId = "";
2104             int colIdN = 0;
2105             for (int i = 0; (i < sqlExpression.length()) && (colIdN < 2); i++)
2106                 if (Character.isLetter(sqlExpression.charAt(i))) {
2107                     colId += sqlExpression.toLowerCase().charAt(i);
2108                     colIdN++;
2109                 } // if
2110
2111             colIdN = getAllColumns().size() + updatedReportCols.size();
2112             for (boolean idAlreadyUsed = true; idAlreadyUsed; colIdN++) {
2113                 String newColId = colId + colIdN;
2114                 idAlreadyUsed = false;
2115
2116                 for (Iterator iter = getAllColumns().iterator(); iter.hasNext();)
2117                     if (newColId.equals(((DataColumnType) iter.next()).getColId())) {
2118                         idAlreadyUsed = true;
2119                         break;
2120                     }
2121
2122                 if (!idAlreadyUsed)
2123                     for (Iterator iter = updatedReportCols.iterator(); iter.hasNext();)
2124                         if (newColId.equals(((DataColumnType) iter.next()).getColId())) {
2125                             idAlreadyUsed = true;
2126                             break;
2127                         }
2128             }
2129
2130             colId += (colIdN - 1);
2131         }
2132
2133         if (dct == null) {
2134                 dct = (new ObjectFactory()).createDataColumnType();
2135             dct.setColId(colId);
2136             dct.setDisplayWidth(10);
2137             dct.setDisplayAlignment("Left");
2138             dct.setVisible(true);
2139             dct.setGroupBreak(false);
2140
2141             boolean isValidIdentifier = Character.isLetterOrDigit(sqlExpression.charAt(0));
2142             for (int i = 0; i < sqlExpression.length(); i++)
2143                 if (!(Character.isLetterOrDigit(sqlExpression.charAt(i))
2144                         || (sqlExpression.charAt(i) == '_') || (sqlExpression.charAt(i) == '$'))) {
2145                     isValidIdentifier = false;
2146                     break;
2147                 } // if
2148
2149             if (isValidIdentifier) {
2150                 dct.setDisplayName(sqlExpression);
2151             } else {
2152                 dct.setDisplayName(colId);
2153             }
2154         }
2155         if(!isCYMBALScript)
2156                 sqlExpression = sqlExpression.replaceAll(", '", ",'");
2157         dct.setDbColName(sqlExpression);
2158         dct.setColName(sqlExpression);
2159         dct.setCalculated(true);
2160         dct.setColType(AppConstants.CT_CHAR);
2161         dct.setDbColType(AppConstants.CT_CHAR);
2162         adjustColumnType(dct);
2163
2164         if(!isCYMBALScript) {
2165                 if (parsedSQL.toString().equals("SELECT ")
2166                         || parsedSQL.toString().equals("SELECT DISTINCT "))
2167                     parsedSQL.append("\n\t");
2168                 else
2169                     parsedSQL.append(", \n\t");
2170                 parsedSQL.append(sqlExpression);
2171                 parsedSQL.append(" ");
2172                 parsedSQL.append(colId);
2173         }
2174
2175         return dct;
2176     }
2177     private boolean isParseSQLColID(String token) {
2178         if (nvl(token).length() == 0)
2179             return false;
2180
2181         for (int i = 0; i < token.length(); i++) {
2182             char ch = token.charAt(i);
2183
2184             if (i == 0 && ch == '_')
2185                 return false;
2186
2187             if (!(Character.isLetterOrDigit(ch) || ch == '_'))
2188                 return false;
2189         }
2190
2191         return true;
2192     }
2193
2194      public String parseReportSQLForDrillDownParams(String reportSQL, DataColumnType dataColumnRequest, HttpServletRequest request){
2195         String param = nvl(dataColumnRequest.getDrillDownParams());
2196         String sql = reportSQL;
2197         int pos = 0;
2198         while (param.indexOf('[', pos) >= 0) {
2199             int startIdx = param.indexOf('[',pos);
2200             int endIdx = param.indexOf(']',startIdx+1);
2201             pos = startIdx+1;
2202             StringBuffer sb = new StringBuffer();
2203             if (startIdx > 0)
2204                 sb.append(param.substring(0, startIdx));
2205             else break;
2206
2207             if (param.charAt(startIdx + 1) == '#') {
2208                 String fieldId = param.substring(startIdx + 2, endIdx);
2209                 String fieldValue = ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(), request.getParameter(fieldId));
2210                 sql = Utils.replaceInString(sql, "[" + fieldId.toUpperCase()+"]", fieldValue );
2211             }
2212         }
2213         return sql;
2214     }
2215
2216 public List getMapMarkers(ReportData rd, org.onap.portalsdk.analytics.xmlobj.ReportMap xmlmap){
2217
2218                 ArrayList markers = new ArrayList();
2219                 int rNum = 0;
2220                 HashMap colHash = new HashMap();
2221
2222                 for(rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext(); rNum++) {
2223                         DataRow dr = rd.reportDataRows.getNext();
2224                         for(dr.resetNext(); dr.hasNext(); ) {
2225                                 DataValue dv = dr.getNext();
2226                                 colHash.put(dv.getColId(), dv.getDisplayValueLinkHtml());
2227                         }
2228
2229                         for (int i = 0; i < xmlmap.getMarkers().size(); i ++){
2230                                 Marker marker = new Marker("", "", "");
2231                                 org.onap.portalsdk.analytics.xmlobj.Marker m = (org.onap.portalsdk.analytics.xmlobj.Marker) xmlmap.getMarkers().get(i);
2232                                 String address = (String) colHash.get(m.getAddressColumn());
2233                                 String data = (String) colHash.get(m.getDataColumn());
2234                                 marker.setAddress(address);
2235                                 if (xmlmap.getAddAddressInDataYN() != null && xmlmap.getAddAddressInDataYN().equals("Y")){
2236                                         marker.setData(address + "<br/>" + data);
2237                                 }
2238                                 else{
2239                                         marker.setData(data);
2240                                 }
2241                                 marker.setColor(m.getMarkerColor());
2242                                 markers.add(marker);
2243                                 System.out.println("%%%%%%%%%%%% marker is : " + address + data);
2244                         }
2245
2246                 }
2247
2248                 return markers;
2249         }
2250
2251
2252
2253         public ReportParamValues getReportParamValues() {
2254                 return reportParamValues;
2255         }
2256
2257         public String getFormFieldFilled(String title) {
2258                 if( getFormFieldList()!=null && reportParamValues!=null && nvl(title).length()>0) {
2259                         for (Iterator iter1 = getFormFieldList().getFormField().iterator(); iter1.hasNext();) {
2260                                 FormFieldType fft = (FormFieldType) iter1.next();
2261                                 String fieldDisplay = getFormFieldDisplayName(fft);
2262                                 String fieldId = fft.getFieldId();
2263                                 if(!fft.getFieldType().equals(FormField.FFT_BLANK) && !fft.getFieldType().equals(FormField.FFT_LIST_MULTI) && !fft.getFieldType().equals(FormField.FFT_TEXTAREA)) {
2264                                         String paramValue = Utils.oracleSafe(nvl(reportParamValues.getParamValue(fieldId)));
2265                                         title = Utils.replaceInString(title, fieldDisplay, nvl(
2266                                     paramValue, ""));
2267                                 }
2268                         }
2269                 }
2270                 return title;
2271         }
2272
2273     public synchronized Object clone() {
2274         try {
2275                 return super.clone();
2276         } catch (CloneNotSupportedException e) {
2277             throw new InternalError("Cloning throws error.");
2278         }
2279     }
2280
2281     public VisualManager getVisualManager() {
2282         return visualManager;
2283     }
2284
2285         public String getReportSQLWithRowNum(String _orderBy, boolean asc) {
2286                 String sql = getWholeSQL();
2287         int closeBracketPos = 0;
2288                 return nvl(getReportSQLOnlyFirstPart()) + " " + sql + ") x ";
2289
2290         }
2291
2292         public int getDisplayMode() {
2293                 return DISPLAY_MODE;
2294         }
2295
2296         public void setDisplayMode(int mode) {
2297                 DISPLAY_MODE = mode;
2298         }
2299
2300         public int getDateOption() {
2301                 return DATE_OPTION;
2302         }
2303
2304         public void setDateOption(int dateOption) {
2305                 DATE_OPTION = dateOption;
2306         }
2307
2308         public boolean isDisplayColTotals() {
2309                 return displayColTotals;
2310         }
2311
2312         public void setDisplayColTotals(boolean displayColTotals) {
2313                 this.displayColTotals = displayColTotals;
2314         }
2315
2316         public boolean isDisplayRowTotals() {
2317                 return displayRowTotals;
2318         }
2319
2320         public void setDisplayRowTotals(boolean displayRowTotals) {
2321                 this.displayRowTotals = displayRowTotals;
2322         }
2323
2324
2325         private boolean canPersistLinearReport() {
2326                 boolean visibleColExist = false;
2327
2328                 if (getDataSourceList().getDataSource().size() > 0) {
2329                         for (Iterator iter = getAllColumns().iterator(); iter.hasNext();) {
2330                                 DataColumnType dct = (DataColumnType) iter.next();
2331
2332                                 if (dct.isVisible()) {
2333                                         visibleColExist = true;
2334                                         break;
2335                                 }
2336                         }
2337                 }
2338
2339                 return visibleColExist;
2340         }
2341
2342         public void persistLinearReport(HttpServletRequest request)
2343                         throws RaptorException {
2344                 if (!canPersistLinearReport())
2345                         return;
2346
2347                 Connection connection = null;
2348                 try {
2349                         String userID = AppUtils.getUserID(request);
2350                         String reportXML = marshal();
2351                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report " + reportID
2352                                         + " XML marshalled succesfully"));
2353                         verifySQLBasedReportAccess(request);
2354                         reportSecurity.reportUpdate(request);
2355                         connection = DbUtils.startTransaction();
2356                         ReportLoader.updateCustomReportRec(connection, this, reportXML);
2357                         ReportLoader.createReportLogEntry(connection, reportID, userID,
2358                                         AppConstants.RLA_UPDATE, "", "");
2359                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report "
2360                                         + reportID + " succesfull"));
2361                         DbUtils.commitTransaction(connection);
2362                 } catch (RaptorException e) {
2363                         e.printStackTrace();
2364                         DbUtils.rollbackTransaction(connection);
2365                         throw e;
2366                 } finally {
2367                         DbUtils.clearConnection(connection);
2368                 }
2369         }
2370
2371         public void persistDashboardReport(HttpServletRequest request)
2372                         throws RaptorException {
2373
2374                 Connection connection = null;
2375                 try {
2376                         String userID = AppUtils.getUserID(request);
2377                         String reportXML = marshal();
2378                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report " + reportID
2379                                         + " XML marshalled succesfully"));
2380                         verifySQLBasedReportAccess(request);
2381                         reportSecurity.reportUpdate(request);
2382                         connection = DbUtils.startTransaction();
2383                         ReportLoader.updateCustomReportRec(connection, this, reportXML);
2384                         ReportLoader.createReportLogEntry(connection, reportID, userID,
2385                                         AppConstants.RLA_UPDATE, "", "");
2386                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report "
2387                                         + reportID + " succesfull"));
2388                         DbUtils.commitTransaction(connection);
2389                 } catch (RaptorException e) {
2390                         e.printStackTrace();
2391                         DbUtils.rollbackTransaction(connection);
2392                         throw e;
2393                 } finally {
2394                         DbUtils.clearConnection(connection);
2395                 }
2396         }
2397         public String getTotalSql() {
2398                 return totalSql;
2399         }
2400
2401         public void setTotalSql(String totalSql) {
2402                 this.totalSql = totalSql;
2403         }
2404
2405         public void setTriggerFormFieldCheck( ReportFormFields FormFieldList, FormField selectedFormField) {
2406
2407                 for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
2408                         FormField ff = (FormField) iter.next();
2409                         if(!ff.getFieldName().equals(selectedFormField.getFieldName())) {
2410                                 if(nvl(ff.getBaseSQL()).length()>0 && ff.getBaseSQL().indexOf("["+selectedFormField.getFieldDisplayName() +"]")!= -1) {
2411                                         selectedFormField.setTriggerOtherFormFields(true);
2412                                 }
2413
2414                         }
2415                 }
2416
2417         }
2418
2419         public void setTriggerThisFormFieldCheck( ReportFormFields FormFieldList, FormField selectedFormField) {
2420
2421                 String sql = nvl(selectedFormField.getBaseSQL()).length()>0 ? selectedFormField.getBaseSQL():"";
2422                 for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
2423                         FormField ff = (FormField) iter.next();
2424                         if(!ff.getFieldName().equals(selectedFormField.getFieldName())) {
2425                                 if(sql.indexOf("["+ff.getFieldDisplayName() +"]")!= -1) {
2426                                         selectedFormField.setTriggerThisFormfield(true);
2427                                         break;
2428                                 }
2429
2430                         }
2431                 }
2432
2433         }
2434
2435         private boolean isAllowEdit(HttpServletRequest request) {
2436                 boolean allowEdit = false;
2437                 String userId = AppUtils.getUserID(request);
2438                 try {
2439                         if( AppUtils.isAdminUser(request) || AppUtils.isSuperUser(request) ) {
2440                                 allowEdit = true;
2441                         } else {
2442                                 if(getOwnerID().equals(userId)) allowEdit = true;
2443                                 else allowEdit = false;
2444                         }
2445                 } catch (RaptorException ex) {
2446                         allowEdit = false;
2447                 }
2448                 return allowEdit;
2449         }
2450         public ReportJSONRuntime createReportJSONRuntime(HttpServletRequest request, ReportData rd) {
2451                 String userId = AppUtils.getUserID(request);
2452                 ObjectMapper mapper = new ObjectMapper();
2453                 ReportJSONRuntime reportJSONRuntime = new ReportJSONRuntime();
2454                 reportJSONRuntime.setReportTitle(getReportTitle());
2455                 reportJSONRuntime.setReportID(getReportID());
2456                 reportJSONRuntime.setReportDescr(getReportDescr());
2457                 reportJSONRuntime.setReportName(getReportName());
2458                 reportJSONRuntime.setReportSubTitle(getReportSubTitle());
2459                 reportJSONRuntime.setAllowSchedule(isAllowSchedule());
2460                 reportJSONRuntime.setAllowEdit(isAllowEdit(request));
2461                 reportJSONRuntime.setColIdxTobeFreezed(getFrozenColumnId());
2462                 reportJSONRuntime.setNumFormCols(getNumFormColsAsInt());
2463                 reportJSONRuntime.setBackBtnURL("");
2464                 String chartType = getChartType();
2465                 boolean displayChart = (nvl(chartType).length()>0)&&getDisplayChart();
2466                 boolean displayChartWizard = getDisplayChart();
2467                 reportJSONRuntime.setChartAvailable(displayChart);
2468                 reportJSONRuntime.setChartWizardAvailable(displayChartWizard);
2469                 reportJSONRuntime.setDisplayData(!isDisplayOptionHideData());
2470                 reportJSONRuntime.setDisplayForm(!isDisplayOptionHideForm());
2471                 reportJSONRuntime.setHideFormFieldsAfterRun(isHideFormFieldAfterRun());
2472                 reportJSONRuntime.setDisplayExcel(!isDisplayOptionHideExcelIcons());
2473                 reportJSONRuntime.setDisplayPDF(!isDisplayOptionHidePDFIcons());
2474                 ArrayList<IdNameValue> formFieldValues = new ArrayList<>();
2475                 ArrayList<FormFieldJSON> formFieldJSONList = new ArrayList<>();
2476                 if(getReportFormFields()!=null) {
2477                         formFieldJSONList = new ArrayList<>(getReportFormFields().size());
2478                 for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
2479                         formFieldValues = new ArrayList<>();
2480                         FormField ff = (FormField) iter.next();
2481                         ff.setDbInfo(getDbInfo());
2482                         FormFieldJSON ffJSON = new FormFieldJSON();
2483                         ffJSON.setFieldId(ff.getFieldName());
2484                         ffJSON.setFieldType(ff.getFieldType());
2485                         ffJSON.setFieldDisplayName(ff.getFieldDisplayName());
2486                         ffJSON.setHelpText(ff.getHelpText());
2487                         ffJSON.setValidationType(ff.getValidationType());
2488                         ffJSON.setVisible(ff.isVisible());
2489                         IdNameList lookup =  null;
2490                         lookup = ff.getLookupList();
2491                         String selectedValue = "";
2492                         String oldSQL = "";
2493                         IdNameList lookupList = null;
2494                         boolean readOnly = false;
2495                         if(lookup!=null) {
2496                                         if(!ff.hasPredefinedList) {
2497                                                         IdNameSql lu = (IdNameSql) lookup;
2498                                                 String SQL = lu.getSql();
2499                                                 oldSQL = lu.getSql();
2500                                 setTriggerFormFieldCheck( getReportFormFields(), ff);
2501                                 ffJSON.setTriggerOtherFormFields(ff.isTriggerOtherFormFields());
2502                                                 SQL = parseAndFillReq_Session_UserValues(request, SQL, userId);
2503                                                 SQL = parseAndFillWithCurrentValues(request, SQL, ff);
2504                                                 String defaultSQL = lu.getDefaultSQL();
2505                                                 defaultSQL = parseAndFillReq_Session_UserValues(request, defaultSQL, userId);
2506                                                 defaultSQL = parseAndFillWithCurrentValues(request, defaultSQL, ff);
2507                                                 lookup = new IdNameSql(-1,SQL,defaultSQL);
2508
2509                                                 lookupList = lookup;
2510                                     try {
2511                                         lookup.loadUserData(0, "", ff.getDbInfo(), ff.getUserId());
2512                                     } catch (Exception e ){ e.printStackTrace();
2513                                                 }
2514                                 }
2515                                 lookup.trimToSize();
2516
2517                                 String[] requestValue = request.getParameterValues(ff.getFieldName());
2518
2519                                 if(lookup != null  && lookup.size() > 0) {
2520                                 for (lookup.resetNext(); lookup.hasNext();) {
2521                                         IdNameValue value = lookup.getNext();
2522                                         readOnly = value.isReadOnly();
2523                                         if(requestValue != null && Arrays.asList(requestValue).contains(value.getId())) {
2524                                                  value.setDefaultValue(true);
2525                                         } else if (AppUtils.nvl(ff.getDefaultValue()).length()>0) {
2526                                                 if(ff.getDefaultValue().equals(value.getId())) {
2527                                                         value.setDefaultValue(true);
2528                                                 }
2529                                         }
2530                                         if(!(ff.getFieldType().equals(FormField.FFT_CHECK_BOX) || ff.getFieldType().equals(FormField.FFT_COMBO_BOX) || ff.getFieldType().equals(FormField.FFT_LIST_BOX)
2531                                                         || ff.getFieldType().equals(FormField.FFT_LIST_MULTI)) && value.isDefaultValue())
2532                                                 formFieldValues.add(value);
2533                                         else if(ff.getFieldType().equals(FormField.FFT_CHECK_BOX) || ff.getFieldType().equals(FormField.FFT_COMBO_BOX) || ff.getFieldType().equals(FormField.FFT_LIST_BOX)
2534                                                         || ff.getFieldType().equals(FormField.FFT_LIST_MULTI)) {
2535                                                 formFieldValues.add(value);
2536                                         }
2537
2538                                 }
2539                                 } else {
2540                                         if(requestValue!=null && requestValue.length>0) {
2541                                                 IdNameValue value = new IdNameValue(requestValue[0], requestValue[0], true, false);
2542                                                 formFieldValues.add(value);
2543                                         }
2544                                 }
2545
2546                         } else {
2547                                 setTriggerFormFieldCheck( getReportFormFields(), ff);
2548                                 ffJSON.setTriggerOtherFormFields(ff.isTriggerOtherFormFields());
2549                                 String[] requestValue = request.getParameterValues(ff.getFieldName());
2550                                 if(requestValue!=null && requestValue.length>0) {
2551                                         IdNameValue value = new IdNameValue(requestValue[0], requestValue[0], true, false);
2552                                         formFieldValues.add(value);
2553                                 } else if (AppUtils.nvl(ff.getDefaultValue()).length()>0) {
2554                                         IdNameValue value = new IdNameValue(ff.getDefaultValue(), ff.getDefaultValue(), true, false);
2555                                         formFieldValues.add(value);
2556                                 }
2557                         }
2558                 if(!ff.hasPredefinedList) {
2559             if(oldSQL != null && !oldSQL.equals("") && lookup!=null) {
2560                 ((IdNameSql)lookup).setSQL(oldSQL);
2561             }
2562                 }
2563
2564
2565
2566                         ffJSON.setFormFieldValues(formFieldValues);
2567                         formFieldJSONList.add(ffJSON);
2568                 } // for
2569           }
2570                 reportJSONRuntime.setFormFieldList(formFieldJSONList);
2571                 int count = 0;
2572                 Map<String,Object> dvJSON = null;
2573                 if(rd!=null) {
2574                         count = 0;
2575                         reportJSONRuntime.setTotalRows(getReportDataSize());
2576                         ArrayList<ColumnHeader> colList = new ArrayList<>();
2577                         ArrayList<Map<String,Object>> reportDataRows = new ArrayList<>();
2578                         ArrayList<Map<String,Object>> reportTotalDataRows = new ArrayList<Map<String,Object>>();
2579                         for(rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext(); ) {
2580                                 count++;
2581                                 ColumnHeaderRow chr = rd.reportColumnHeaderRows.getNext();
2582                                 for(chr.resetNext(); chr.hasNext(); ) {
2583                                         colList.add(chr.getNext());
2584                                 }
2585                         }
2586                         if(getReportDataSize() > 0) {
2587                                 count = 0;
2588                                 for(rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext(); count++) {
2589                                         dvJSON = new HashMap<>();
2590                                         DataRow dr = rd.reportDataRows.getNext();
2591                                         for(dr.resetNext(); dr.hasNext(); ) {
2592                                                 DataValue dv = dr.getNext();
2593                                                 try {
2594                                                         dvJSON.put(dv.getColId(), dv);
2595                                                 } catch (Exception ex) {
2596                                                         ex.printStackTrace();
2597
2598                                                 }
2599                                         }
2600                                         reportDataRows.add(dvJSON);
2601                                 }
2602                         }
2603                         if(rd.reportDataTotalRow != null) {
2604                                 for(rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext(); count++) {
2605                                         dvJSON = new HashMap<String,Object>();
2606                                         DataRow dr = rd.reportDataTotalRow.getNext();
2607                                         for(dr.resetNext(); dr.hasNext(); ) {
2608                                                 DataValue dv = dr.getNext();
2609                                                 try {
2610                                                         dvJSON.put(dv.getColId(), dv);
2611                                                 } catch (Exception ex) {
2612                                                         ex.printStackTrace();
2613
2614                                                 }
2615                                         }
2616                                         reportTotalDataRows.add(dvJSON);
2617                                 }
2618                         }
2619                         reportJSONRuntime.setReportDataColumns(colList);
2620                         reportJSONRuntime.setReportDataRows(reportDataRows);
2621                         reportJSONRuntime.setPageSize(getPageSize());
2622                         reportJSONRuntime.setReportTotalDataRows(reportTotalDataRows);
2623
2624                 }
2625
2626                 if(getReportDataSize() <= 0) {
2627                         reportJSONRuntime.setMessage(getEmptyMessage());
2628                 }
2629                 reportJSONRuntime.setSqlWhole(getWholeSQL());
2630                 return reportJSONRuntime;
2631
2632         }
2633
2634         public ReportJSONRuntime createFormFieldJSONRuntime(HttpServletRequest request) {
2635                 String userId = AppUtils.getUserID(request);
2636                 ObjectMapper mapper = new ObjectMapper();
2637                 ReportJSONRuntime reportJSONRuntime = new ReportJSONRuntime();
2638                 reportJSONRuntime.setReportTitle(getReportTitle());
2639                 reportJSONRuntime.setReportID(getReportID());
2640                 reportJSONRuntime.setReportName(getReportName());
2641                 reportJSONRuntime.setReportSubTitle(getReportSubTitle());
2642                 reportJSONRuntime.setAllowEdit(isAllowEdit(request));
2643                 reportJSONRuntime.setNumFormCols(getNumFormColsAsInt());
2644                 ArrayList<IdNameValue> formFieldValues = new ArrayList<>();
2645                 ArrayList<FormFieldJSON> formFieldJSONList = new ArrayList<>();
2646                 if(reportFormFields!=null) {
2647                         formFieldJSONList = new ArrayList<>(reportFormFields.size());
2648                         for (Iterator iter = reportFormFields.iterator(); iter.hasNext();) {
2649                                 formFieldValues = new ArrayList<>();
2650                                 FormField ff = (FormField) iter.next();
2651                                 FormFieldJSON ffJSON = new FormFieldJSON();
2652                                 ffJSON.setFieldId(ff.getFieldName());
2653                                 ffJSON.setFieldType(ff.getFieldType());
2654                                 ffJSON.setFieldDisplayName(ff.getFieldDisplayName());
2655                                 ffJSON.setHelpText(ff.getHelpText());
2656                                 ffJSON.setValidationType(ff.getValidationType());
2657                                 ffJSON.setFormFieldValues(formFieldValues);
2658                                 ffJSON.setVisible(ff.isVisible());
2659                                 formFieldJSONList.add(ffJSON);
2660                         }
2661                 for (Iterator iter = reportFormFields.iterator(); iter.hasNext();) {
2662                         formFieldValues = new ArrayList<>();
2663                         FormField ff = (FormField) iter.next();
2664                         ff.setDbInfo(getDbInfo());
2665                         for (Iterator iter1 = formFieldJSONList.iterator(); iter1.hasNext();) {
2666                                 FormFieldJSON ffJSON = (FormFieldJSON) iter1.next();
2667                                 if(ffJSON.getFieldId().equals(ff.getFieldName())) {
2668                                         IdNameList lookup =  null;
2669                                         lookup = ff.getLookupList();
2670                                         String selectedValue = "";
2671                                         String oldSQL = "";
2672                                         IdNameList lookupList = null;
2673                                         boolean readOnly = false;
2674                                         if(lookup!=null) {
2675                                                         if(!ff.hasPredefinedList) {
2676                                                                         IdNameSql lu = (IdNameSql) lookup;
2677                                                                 String SQL = lu.getSql();
2678                                                                 oldSQL = lu.getSql();
2679                                                 setTriggerFormFieldCheck( getReportFormFields(), ff);
2680                                                 ffJSON.setTriggerOtherFormFields(ff.isTriggerOtherFormFields());
2681                                                 setTriggerThisFormFieldCheck(getReportFormFields(), ff);
2682                                                                 SQL = parseAndFillReq_Session_UserValues(request, SQL, userId);
2683                                                                 SQL = parseAndFillOtherFormfieldValues(request, SQL, userId, formFieldJSONList);
2684                                                                 String defaultSQL = lu.getDefaultSQL();
2685                                                                 defaultSQL = parseAndFillReq_Session_UserValues(request, defaultSQL, userId);
2686                                                                 lookup = new IdNameSql(-1,SQL,defaultSQL);
2687
2688                                                                 lookupList = lookup;
2689                                                     try {
2690                                                         lookup.loadUserData(0, "", ff.getDbInfo(), ff.getUserId());
2691                                                                 }catch (Exception e) {
2692                                                                 }
2693                                                 }
2694                                                 lookup.trimToSize();
2695
2696                                                 String requestValue = request.getParameter(ff.getFieldName());
2697                                                 ArrayList<String> requestValueList = new ArrayList<>();
2698                                                 requestValueList.add(requestValue);
2699
2700
2701                                                 for (lookup.resetNext(); lookup.hasNext();) {
2702                                                         IdNameValue value = lookup.getNext();
2703                                                         readOnly = value.isReadOnly();
2704                                                         if(nvl(requestValue).length()>0) {
2705                                                                 if(value.getId().equals(requestValue))
2706                                                                  value.setDefaultValue(true);
2707                                                         }
2708                                                         formFieldValues.add(value);
2709
2710                                                 }
2711
2712                                         } else {
2713                                                 setTriggerFormFieldCheck( getReportFormFields(), ff);
2714                                                 ffJSON.setTriggerOtherFormFields(ff.isTriggerOtherFormFields());
2715                                                 String[] requestValue = request.getParameterValues(ff.getFieldName());
2716                                                 if(requestValue!=null && requestValue.length>0) {
2717                                                         IdNameValue value = new IdNameValue(requestValue[0], requestValue[0], true, false);
2718                                                         formFieldValues.add(value);
2719                                                 } else if (AppUtils.nvl(ff.getDefaultValue()).length()>0) {
2720                                                         IdNameValue value = new IdNameValue(ff.getDefaultValue(), ff.getDefaultValue(), true, false);
2721                                                         formFieldValues.add(value);
2722                                                 }
2723                                         }
2724                                 if(!ff.hasPredefinedList) {
2725                             if(oldSQL != null && !oldSQL.equals("") && lookup!=null) {
2726                                 ((IdNameSql)lookup).setSQL(oldSQL);
2727                             }
2728                                 }
2729                                         ffJSON.setFormFieldValues(formFieldValues);
2730
2731
2732                                 break;
2733
2734                                 }
2735                         }
2736                 }
2737                 }
2738
2739                 reportJSONRuntime.setFormFieldList(formFieldJSONList);
2740
2741                 return reportJSONRuntime;
2742
2743         }
2744
2745
2746         private String parseAndFillOtherFormfieldValues(HttpServletRequest request, String SQL, String userId,
2747                         ArrayList<FormFieldJSON> formFieldJSONList) {
2748                 ArrayList<IdNameValue> formFieldValues = new ArrayList<>();
2749                 String selectedValue = "";
2750                 String displayName = "";
2751                 for (Iterator iter1 = formFieldJSONList.iterator(); iter1.hasNext();) {
2752                         FormFieldJSON ffJSON = (FormFieldJSON) iter1.next();
2753                         String fieldId = ffJSON.getFieldId();
2754                         displayName = ffJSON.getFieldDisplayName();
2755                         if (request.getParameter(fieldId) != null && request.getParameter(fieldId).length() > 0) {
2756                                 if (ffJSON.getFieldType().equals(FormField.FFT_LIST_MULTI)
2757                                                 || ffJSON.getFieldType().equals(FormField.FFT_CHECK_BOX)) {
2758                                         try {
2759                                                 selectedValue = formatListValue(displayName, Utils.oracleSafe(request.getParameter(fieldId)),
2760                                                                 null, false, true, null, null);
2761                                         } catch (RaptorException e) {
2762                                                 e.printStackTrace();
2763                                         }
2764                                 } else {
2765                                         selectedValue = "'" + request.getParameter(fieldId) + "'";
2766                                 }
2767                         } else {
2768                                 ArrayList<IdNameValue> formfieldvalues = ffJSON.getFormFieldValues();
2769                                 for (int i = 0; i < formfieldvalues.size(); i++) {
2770                                         IdNameValue formfieldItem = formfieldvalues.get(i);
2771                                         if (formfieldItem.isDefaultValue()) {
2772                                                 selectedValue = formfieldItem.getId();
2773                                         }
2774                                 }
2775                         }
2776                         SQL = Utils.replaceInString(SQL, "[" + displayName + "]", selectedValue);
2777                 }
2778                 return SQL;
2779         }
2780
2781         public String formatSelectedItems(List selectedItems, String type) {
2782                 StringBuffer value = new StringBuffer("");
2783                 int count = 0;
2784                 boolean multiple = false;
2785                 if(type.equals("LIST_MULTI_SELECT"))
2786                         multiple = true;
2787                 for(Iterator iter = selectedItems.iterator(); iter.hasNext(); ) {
2788                         count++;
2789                         String entry = (String) iter.next();
2790                         if(count == 1 && multiple)
2791                                 value.append("(");
2792                         else if (type.equals(FormField.FFT_LIST_MULTI))
2793                                 value.append("'"+Utils.oracleSafe(entry)+"'");
2794                         else if(type.equals(FormField.FFT_LIST_BOX))
2795                                 value.append(Utils.oracleSafe(entry));
2796                         else
2797                                 value.append("'"+Utils.oracleSafe(entry)+"'");
2798                         if((count < selectedItems.size()) && multiple)
2799                                 value.append(",");
2800                         if((count == selectedItems.size()) && multiple)
2801                                 value.append(")");
2802                 }
2803
2804                 if(value.length()>0)
2805                         return value.toString();
2806                 else
2807                         return null;
2808         }
2809
2810 public String parseAndFillWithCurrentValues(HttpServletRequest request, String sql, FormField source_Formfield) {
2811
2812                 if (getFormFieldList() != null) {
2813                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
2814                                 FormFieldType fft = (FormFieldType) iter.next();
2815                                 String fieldId = fft.getFieldId();
2816                                 String fieldDisplay = getFormFieldDisplayName(fft);
2817                                 String formfield_value = "";
2818                                 List<String> selectedItems = new ArrayList<>();
2819                                 if(fft.getFieldType().equals(FormField.FFT_COMBO_BOX)) {
2820                                         fft.setFieldType(FormField.FFT_LIST_BOX);
2821                                 }
2822                                 if(!fft.getFieldType().equals(FormField.FFT_BLANK)) {
2823                                         if(fft.getFieldType().equals(FormField.FFT_LIST_MULTI) || fft.getFieldType().equals(FormField.FFT_CHECK_BOX)) {
2824                                                 if(request.getParameterValues(fieldId)!=null && request.getParameterValues(fieldId).length > 0) {
2825
2826                                                         selectedItems = Arrays.asList(request.getParameterValues(fieldId));
2827                                                         formfield_value = formatSelectedItems(selectedItems, fft.getFieldType());
2828                                                 } else {
2829                                                         formfield_value = "";
2830                                                 }
2831                                         } else  if(fft.getFieldType().equals(FormField.FFT_RADIO_BTN)) {
2832                                                 if(request.getParameter(fieldId)!=null) {
2833                                                          formfield_value = request.getParameter(fieldId);
2834                                                 } else {
2835                                                         formfield_value = "";
2836                                                 }
2837                                         } else if (fft.getFieldType().equals(FormField.FFT_HIDDEN)) {
2838                                                 if(request.getParameter(fieldId)!=null) {
2839                                                          formfield_value = request.getParameter(fieldId);
2840                                                 } else {
2841                                                         formfield_value = "";
2842                                                 }
2843                                         } else if((fft.getFieldType().equals(FormField.FFT_TEXT)  || fft.getFieldType().equals(FormField.FFT_TEXTAREA)) &&
2844                                                    (!fft.getValidationType().equals(FormField.VT_DATE) && !fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)&&
2845                                                              !fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) && !fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) ) {
2846                                                 if(request.getParameter(fieldId)!=null) {
2847                                                          formfield_value = request.getParameter(fieldId);
2848                                                 } else {
2849                                                         formfield_value = "";
2850                                                 }
2851                            } else if (fft.getValidationType().equals(FormField.VT_DATE) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)||
2852                                                      fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
2853
2854                            } else if ((fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)||
2855                                              fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC))) {
2856
2857                                         } else if (fft.getFieldType().equals(FormField.FFT_TEXT_W_POPUP)) {
2858                                                 if(request.getParameter(fieldId)!=null) {
2859                                                          formfield_value = request.getParameter(fieldId);
2860                                                 } else {
2861                                                         formfield_value = "";
2862                                                 }
2863                                         } else if (fft.getFieldType().equals(FormField.FFT_LIST_BOX)) {
2864                                                 if(request.getParameter(fieldId)!=null) {
2865                                                          formfield_value = request.getParameter(fieldId);
2866                                                 } else {
2867                                                         formfield_value = "";
2868                                                 }
2869
2870                                 }
2871                                 if(nvl(formfield_value).length()>0) {
2872                                         sql = Utils.replaceInString(sql, fieldDisplay, formfield_value);
2873                                 } else {
2874                                         sql = Utils.replaceInString(sql, "'"+fieldDisplay+"'", "null");
2875                                         sql = Utils.replaceInString(sql, fieldDisplay, "null");
2876                                 }
2877                         }
2878                 }
2879                 }
2880                 return sql;
2881         }
2882         public String parseAndFillReq_Session_UserValues(HttpServletRequest request, String sql, String user_id) {
2883                 HttpSession session = request.getSession();
2884
2885         String[] reqParameters = Globals.getRequestParams().split(",");
2886         String[] sessionParameters = Globals.getSessionParams().split(",");
2887         String[] scheduleSessionParameters = Globals.getSessionParamsForScheduling().split(",");
2888
2889
2890         if(AppUtils.nvl(sql).length()>0) {
2891             for (int i = 0; i < reqParameters.length; i++) {
2892                 if(!reqParameters[i].startsWith("ff") && (request.getParameter(reqParameters[i].toUpperCase())!=null && request.getParameter(reqParameters[i].toUpperCase()).length() > 0))
2893                  sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i].toUpperCase()) );
2894                 else if (request.getParameter(reqParameters[i])!=null && request.getParameter(reqParameters[i]).length() > 0)
2895                  sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i]) );
2896             }
2897
2898             for (int i = 0; i < scheduleSessionParameters.length; i++) {
2899                 if(request.getParameter(scheduleSessionParameters[i])!=null && request.getParameter(scheduleSessionParameters[i]).trim().length()>0 )
2900                         sql = Utils.replaceInString(sql, "[" + scheduleSessionParameters[i].toUpperCase()+"]", request.getParameter(scheduleSessionParameters[i]) );
2901                 if(request.getAttribute(scheduleSessionParameters[i])!=null && ((String)request.getAttribute(scheduleSessionParameters[i])).trim().length()>0 )
2902                         sql = Utils.replaceInString(sql, "[" + scheduleSessionParameters[i].toUpperCase()+"]", (String) request.getAttribute(scheduleSessionParameters[i]) );
2903
2904             }
2905
2906             for (int i = 0; i < sessionParameters.length; i++) {
2907                 if (session.getAttribute(sessionParameters[i])!=null && ((String)session.getAttribute(sessionParameters[i])).length() > 0) {
2908                  sql = Utils.replaceInString(sql, "[" + sessionParameters[i].toUpperCase()+"]", (String)session.getAttribute(sessionParameters[i]) );
2909                 }
2910
2911             }
2912                 sql = Utils.replaceInString(sql, "[USERID]", user_id);
2913                 sql = Utils.replaceInString(sql, "[USER_ID]", user_id);
2914                 sql = Utils.replaceInString(sql, "[LOGGED_USERID]", user_id);
2915
2916         }
2917         return sql;
2918         }
2919
2920         private static String reverseDisplayValue(String value) {
2921                 int i = value.length() - 1;
2922                 int start, end = i + 1;
2923                 String result = "";
2924                 while (i >= 0) {
2925                         if (value.charAt(i) == ',') {
2926                                 start = i + 1;
2927                                 while (start != end)
2928                                         result += value.charAt(start++);
2929                                 result += ',';
2930                                 end = i;
2931                         }
2932                         i--;
2933                 }
2934                 start = 0;
2935                 while (start != end)
2936                         result += value.charAt(start++);
2937                 return result;
2938         }
2939
2940 }