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