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