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