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 (getReportType().equals(AppConstants.RT_CROSSTAB))
 649                                 rd = loadCrossTabReportData(pageNo, userId, downloadLimit, request, download);
 650                         else if (getReportType().equals(AppConstants.RT_LINEAR))
 651                                 rd = loadLinearReportData(pageNo, userId, downloadLimit, request, download);
 652                         else
 653                                 throw new RuntimeException(
 654                                                 "[ReportRuntime.loadReportData] Invalid report type");
 655
 656                         if (pageNo >= 0)
 657                                 if (Globals.getCacheCurPageData()) {
 658                                         pageDataCache = rd;
 659                                         cachedPageNo = pageNo;
 660                                 }
 661                 } // if // Commented So that Data is refreshed from DB again
 662
 663                 return rd;
 664         } // loadReportData
 665
 666         private ReportData loadCrossTabReportData(int pageNo, String userId, int downloadLimit, HttpServletRequest request, boolean download) throws RaptorException {
 667                 String reportSQL = generateSQL(reportParamValues, userId, request);
 668                 setWholeSQL(reportSQL);
 669                 cachedSQL = reportSQL;
 670         wholeSQL = reportSQL;
 671         List reportCols = getAllColumns();
 672         // replace the request parameter specified in the drill down
 673         DataColumnType dataColumnRequest = getCrossTabValueColumn();
 674         reportSQL = parseReportSQLForDrillDownParams(reportSQL, dataColumnRequest, request);
 675
 676
 677                 DataSet ds = null;
 678                 // try {
 679                 String dbInfo = getDBInfo();
 680                 StringBuffer colNames = new StringBuffer();
 681                 StringBuffer colExtraIdNames = new StringBuffer();
 682                 StringBuffer colExtraDateNames = new StringBuffer();
 683
 684
 685                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 686                         DataColumnType dc = (DataColumnType) iter.next();
 687                         //TODO: commented if (dc.isVisible()) {
 688                                 if (colNames.length() > 0)
 689                                         colNames.append(", ");
 690                                 colNames.append(dc.getColId());
 691                         //TODO uncomment if it's not working} // if
 692
 693                         // Checking for extra fields necessary for drill-down
 694                                 if (nvl(dc.getDrillDownURL()).length() > 0) {
 695                                                 System.out.println("Drilldown URL " + dc.getDrillDownURL());
 696
 697                                 }
 698                 } // for
 699
 700                 if (reportSQL.toUpperCase().indexOf("GROUP BY ") < 0)
 701                         colNames.append(colExtraIdNames.toString());
 702                 colNames.append(colExtraDateNames.toString());
 703                 //reportSQL = " SELECT ROWNUM rnum, "
 704                 //              + colNames.toString() + " FROM (" + reportSQL + ") ";
 705
 706                 String rSQL = Globals.getLoadCrosstabReportData();
 707                 rSQL = rSQL.replace("[colNames.toString()]", colNames.toString());
 708                 rSQL = rSQL.replace("[reportSQL]", reportSQL);
 709                 reportSQL = rSQL;
 710                 setWholeSQL(reportSQL);
 711                 if (crossTabOrderManager == null)
 712                         crossTabOrderManager = new CrossTabOrderManager(this, userId,request);
 713                 ds = ConnectionUtils.getDataSet(reportSQL, dbInfo);
 714
 715                 ReportData rd = new ReportData(pageNo, false);
 716                 ReportFormFields childReportFormFields = null;
 717                 if(doesReportContainsGroupFormField()) {
 718                         List reportCols1 = getAllColumns();
 719                         reportCols = new Vector();
 720                         outer:
 721                         for (Iterator iter = reportCols1.iterator(); iter.hasNext();) {
 722                                 DataColumnType dct = (DataColumnType) iter.next();
 723                                 for (int k=0; k<ds.getColumnCount(); k++) {
 724                                         if(dct.getColId().toUpperCase().trim().equals(ds.getColumnName(k).trim())) {
 725                                                 reportCols.add(dct);
 726                                                 continue outer;
 727                                         }
 728                                 }
 729                         }
 730
 731                         if (getFormFieldList() != null) {
 732                                 String paramValue = "";
 733                                 for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 734                                         FormFieldType fft = (FormFieldType) iter.next();
 735                                         if(fft.isGroupFormField()!=null && fft.isGroupFormField().booleanValue()) {
 736                                 paramValue = Utils.oracleSafe(nvl(reportParamValues
 737                                                 .getParamValue(fft.getFieldId())));
 738                                 outer:
 739                                         for (Iterator iter1 = reportCols1.iterator(); iter1.hasNext();) {
 740                                                 DataColumnType dct = (DataColumnType) iter1.next();
 741                                                         if(("["+fft.getFieldName()+ "]").equals(dct.getColName().trim())) {
 742                                                                 dct.setDisplayName(paramValue);
 743                                                                 continue outer;
 744                                                         }
 745                                         }
 746
 747
 748                                         }
 749                                 }
 750                         }
 751                 }
 752
 753                 int dataColumnIdx = (rd.reportRowHeaderCols.size() + rd.reportColumnHeaderRows.size())-1;
 754                 DataColumnType dataColumn = getCrossTabValueColumn();
 755
 756                 String columnValue = "";
 757
 758
 759                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 760                         DataColumnType dct = (DataColumnType) iter.next();
 761
 762                         if (nvl(dct.getDrillDownURL()).length() > 0) {
 763                                 childReportFormFields = getChildReportFormFields(request,dct.getDrillDownURL());
 764                         }
 765
 766                         if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_ROW)) {
 767                                 RowHeaderCol rhc = new RowHeaderCol();
 768                                 rhc.setColumnTitle(dct.getDisplayName());
 769                                 // rhc.setColumnWidth("10%");
 770                                 //rhc.setColumnWidth(dct.getDisplayWidth() + "%");
 771                                 if(nvl(dct.getDisplayWidthInPxls()).length()<=0) {
 772                                         dct.setDisplayWidthInPxls("100px");
 773                                 }
 774                                 if(dct.getDisplayWidthInPxls().endsWith("px"))
 775                                         rhc.setColumnWidth(dct.getDisplayWidthInPxls());
 776                                 else
 777                                         rhc.setColumnWidth(dct.getDisplayWidthInPxls()+"px");
 778
 779                                 rhc.setAlignment(dct.getDisplayAlignment());
 780                                 rhc.setDisplayHeaderAlignment(dct.getDisplayHeaderAlignment());
 781                                 rhc.setNowrap(nvl(dataColumn.getNowrap(),"null").equals("false")?"null":nvl(dataColumn.getNowrap(),"null"));
 782                                 rd.reportRowHeaderCols.addRowHeaderCol(rhc);
 783                         } else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_COLUMN)) {
 784                                 ColumnHeaderRow chr = new ColumnHeaderRow();
 785                                 chr.setAlignment(dct.getDisplayHeaderAlignment());
 786                                 chr.setRowHeight("15");
 787                                 if(nvl(dct.getDisplayWidthInPxls()).length()<=0) {
 788                                         dct.setDisplayWidthInPxls("80px");
 789                                 }
 790                                 if(dct.getDisplayWidthInPxls().endsWith("px"))
 791                                         chr.setDisplayWidth(dct.getDisplayWidthInPxls());
 792                                 else
 793                                         chr.setDisplayWidth(dct.getDisplayWidthInPxls()+"px");
 794
 795                                 rd.reportColumnHeaderRows.addColumnHeaderRow(chr);
 796                         } else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_VALUE)) {
 797                                 columnValue = dct.getColId();
 798                         } else {
 799                                 if(!dct.getColId().toLowerCase().endsWith("_sort")) {
 800                                         RowHeaderCol rhc = new RowHeaderCol();
 801                                         rhc.setVisible(false);
 802                                         rd.reportRowHeaderCols.addRowHeaderCol(rhc);
 803                                 }
 804                         }
 805                 } // for
 806
 807                 //int dataColumnIdx = getCrossTabValueColumnIndex();
 808                 FormatProcessor formatProcessor = new FormatProcessor(getSemaphoreById(dataColumn
 809                                 .getSemaphoreId()),
 810                                 getReportDefType().equals(AppConstants.RD_SQL_BASED) ? AppConstants.CT_NUMBER
 811                                                 : dataColumn.getColType(), dataColumn.getColFormat(), false);
 812         List dataList = new ArrayList();
 813        /* //fillup all rows based on rowheaders
 814         Vector rowHeaders = crossTabOrderManager.getRowHeaderValues();
 815         CrossTabColumnValues crossTabRowValues;
 816         int size = 0;
 817         for (int i = 0; i < rowHeaders.size(); i++) {
 818                 if((i+1)==rowHeaders.size()) {
 819                         crossTabRowValues = (CrossTabColumnValues) rowHeaders.get(i);
 820                         size = crossTabRowValues.getValuesCount();
 821                 }
 822                 }
 823
 824         for (int i = 0; i < size; i++) {
 825                         dataList.add(new DataRow());
 826                 }*/
 827
 828                 for (int i = 0; i < ds.getRowCount(); i++) {
 829                         Vector rValues = new Vector();
 830                         Vector cValues = new Vector();
 831                         Vector cValuesSort = new Vector();
 832
 833                         int colIdx = 0;
 834                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 835                                 try {
 836                                         DataColumnType dct = (DataColumnType) iter.next();
 837                                         if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_ROW))
 838                                                 rValues.add(addDataValue(dct, ds.getString(i, dct.getColId())));
 839                                         if (nvl(dct.getCrossTabValue()).trim().length()<=0 && !dct.getColId().toLowerCase().endsWith("_sort"))
 840                                                 rValues.add(addDataValue(dct, ds.getString(i, dct.getColId())));
 841                                         if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_COLUMN)) {
 842                                                 cValues.add(addDataValue(dct, ds.getString(i, dct.getColId())));
 843                                                 if(getColumnById(dct.getColId().toLowerCase()+"_sort")!=null)
 844                                                         cValuesSort.add(addDataValue(dct, new Integer(ds.getString(i, dct.getColId()+"_sort")).toString()));
 845                                         }
 846                                         if (dct.isVisible())
 847                                                 colIdx++;
 848                                 } catch (ArrayIndexOutOfBoundsException ex ) {continue;}
 849                                 catch (NumberFormatException ex1) {
 850                                         ex1.printStackTrace();
 851                                         continue;
 852                                 }
 853                         } // for
 854
 855                         DataValue dv = new DataValue();
 856                         dv.setDisplayValue(ds.getString(i, columnValue));
 857                         if (nvl(dataColumn.getCrossTabValue()).trim().length()<=0)
 858                          dv.setVisible(false);
 859                         dv.setAlignment(dataColumn.getDisplayAlignment());
 860             dv.setDisplayTotal(dataColumn.getDisplayTotal());
 861             dv.setColName(dataColumn.getColName());
 862             dv.setDisplayName(dataColumn.getDisplayName());
 863             dv.setColId(dataColumn.getColId());
 864             dv.setNowrap(nvl(dataColumn.getNowrap(),"null").equals("false")?"null":nvl(dataColumn.getNowrap(),"null"));
 865             /*StringBuffer indentation = new StringBuffer("");
 866             if(dataColumn.getIndentation()!=null && dataColumn.getIndentation().intValue()>0) {
 867                 for (int indent=0; indent < dataColumn.getIndentation(); indent++) {
 868                         indentation.append("\t");
 869                 }
 870                 dv.setNowrap("true");
 871             }
 872             dv.setIndentation(indentation.toString());*/
 873
 874                         if (nvl(dataColumn.getDrillDownURL()).length() > 0) {
 875                 if(dv.getDisplayValue().length() > 0) {
 876                         dv.setDrillDownURL(parseDrillDownURL(i, /* dataColumnIdx, */ds, dataColumn,request, childReportFormFields));
 877                         dv.setDrillDowninPoPUp(dataColumn.isDrillinPoPUp()!=null?dataColumn.isDrillinPoPUp():false);
 878                 }
 879                                 if (dv.getDisplayValue().length() == 0) {
 880                                         //dv.setDisplayValue("[NULL]");
 881                     dv.setDisplayValue("");
 882                 }
 883                         } // if
 884
 885                         rd.setDataValue(rValues, cValues, cValuesSort.size()==0?null:cValuesSort, dv, formatProcessor, crossTabOrderManager, dataList);
 886                 } // for
 887                 rd.setReportDataList(dataList);
 888                 /*if (getReportDataSize() < 0)
 889                         setReportDataSize(rd.getDataRowCount());*/
 890
 891                 /*if (pageNo >= 0)
 892                         rd.truncateData(pageNo * getPageSize(), (pageNo + 1) * getPageSize() - 1);
 893                 else {
 894             if( downloadLimit != -1)
 895                          rd.truncateData(0, downloadLimit - 1);
 896             else
 897              rd.truncateData(0, -1);
 898         }*/
 899
 900                 if (colDataTotalsCrosstab == null)
 901                         colDataTotalsCrosstab = generateDataTotalsCrossTab(AppConstants.CV_COLUMN, userId,request);
 902                 if (displayColTotals && colDataTotalsCrosstab != null)
 903                         rd.setColumnDataTotalsCrossTab(colDataTotalsCrosstab, dataColumn
 904                                         .getDisplayAlignment(), getCrossTabDisplayTotal(AppConstants.CV_COLUMN),
 905                                         crossTabOrderManager, dataList);
 906
 907                 if (rowDataTotalsCrosstab == null)
 908                         rowDataTotalsCrosstab = generateDataTotalsCrossTab(AppConstants.CV_ROW, userId, request);
 909                 if (displayRowTotals && rowDataTotalsCrosstab != null)
 910                         rd.setRowDataTotalsCrossTab(rowDataTotalsCrosstab, dataColumn
 911                                         .getDisplayAlignment(), getCrossTabDisplayTotal(AppConstants.CV_ROW),
 912                                         crossTabOrderManager, dataList);
 913
 914                 if (displayColTotals
 915                                 && displayRowTotals
 916                                 && getCrossTabDisplayTotal(AppConstants.CV_COLUMN).equals(
 917                                                 getCrossTabDisplayTotal(AppConstants.CV_ROW))) {
 918                         // Display grand total
 919                         if (grandTotalCrosstab == null)
 920                                 grandTotalCrosstab = ((CrossTabTotalValue) generateDataTotalsCrossTab("",
 921                                                 userId,request).get(0)).getTotalValue();
 922                         if (grandTotalCrosstab != null)
 923                                 rd.setGrandTotalCrossTab(Utils.truncateTotalDecimals(grandTotalCrosstab),
 924                                                 dataColumn.getDisplayAlignment(),
 925                                                 getCrossTabDisplayTotal(AppConstants.CV_COLUMN), dataList);
 926                 } // if
 927
 928                 rd.consolidateColumnHeaders(visualManager);
 929                 //if (Globals.getMergeCrosstabRowHeadings())
 930                 //      rd.consolidateRowHeaders();
 931                 //rd.addRowNumbers(pageNo, dataList);
 932
 933                 if (displayColTotals && colDataTotalsCrosstab != null) {
 934                         String totalLabel = "Total";
 935                         String colDisplayTotal = getCrossTabDisplayTotal(AppConstants.CV_COLUMN);
 936                         if (colDisplayTotal.length() > 0
 937                                         && (!colDisplayTotal.equals(AppConstants.TOTAL_SUM_ID)))
 938                                 totalLabel = nvl(AppConstants.TOTAL_FUNCTIONS.getNameById(colDisplayTotal));
 939                         if (getReportDataSize() > getPageSize())
 940                                 totalLabel += "_nl_(for all pages)";
 941
 942                         //rd.setCrossTabColumnTotalLabel(totalLabel);
 943                 } // if
 944
 945                 //rd.applyVisibility();
 946                 //Collections.sort((List)dataList, new DataRowComparable());
 947                 DataRow drInFor1 = null;
 948                 Vector<DataValue> v1= null, v2 = null;
 949                 ArrayList<String> temp = new ArrayList<String>();
 950                 if (Globals.getMergeCrosstabRowHeadings()) {
 951                         for (int i = 0; i < dataList.size(); i++) {
 952                                 drInFor1 = (DataRow)dataList.get(i);
 953                                 drInFor1.setRowNum(i+1);
 954                                 v1 = drInFor1.getRowValues();
 955                                 if(i<dataList.size()-1) {
 956                                         v2 = ((DataRow)dataList.get(i+1)).getRowValues();
 957                                 } /*else {
 958                                         v2 = ((DataRow)dataList.get(i-1)).getRowValues();
 959                                 }*/
 960                                 for (int j = 0; j < v1.size(); j++) {
 961                                         if(j==0) {
 962                                                 if(v1.get(j).getDisplayValue().length()>0) { // another ArrayList
 963                                                         temp = new ArrayList();
 964                                                         temp.add(v1.get(j).getDisplayValue());
 965                                                 }
 966                                                 if(v2!=null && temp.get(j).equals(v2.get(j).getDisplayValue())) {
 967                                                         v2.get(j).setDisplayValue("");
 968                                                 }
 969                                         }
 970                                 }
 971                         }
 972                 }
 973         rd.setReportDataList(dataList);
 974                 if (getReportDataSize() < 0) {
 975                         //setReportDataSize(rd.getDataRowCount());
 976                         setReportDataSize(rd.getReportDataList().size());
 977                 }
 978
 979                 return rd;
 980         } // loadCrossTabReportData
 981
 982
 983         public DataValue addDataValue(DataColumnType dataColumn, String columnValue) {
 984                 DataValue dv = new DataValue();
 985                 dv.setDisplayValue(columnValue);
 986                 if (nvl(dataColumn.getCrossTabValue()).trim().length()<=0)
 987                  dv.setVisible(false);
 988                 dv.setAlignment(dataColumn.getDisplayAlignment());
 989         dv.setDisplayTotal(dataColumn.getDisplayTotal());
 990         dv.setColName(dataColumn.getColName());
 991         dv.setDisplayName(dataColumn.getDisplayName());
 992         dv.setColId(dataColumn.getColId());
 993         dv.setNowrap(nvl(dataColumn.getNowrap(),"null").equals("false")?"null":nvl(dataColumn.getNowrap(),"null"));
 994                 return dv;
 995
 996         }
 997
 998         /*private*/ public boolean doesReportContainsGroupFormField() {
 999            int flag = 0;
1000                 if(getFormFieldList()!=null) {
1001                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
1002                                 FormFieldType fft = (FormFieldType) iter.next();
1003                                 if(fft.isGroupFormField()!=null && fft.isGroupFormField().booleanValue()) {
1004                                         flag = 1;
1005                                         break;
1006                                 }
1007                         }
1008                 }
1009                 return (flag ==1);
1010         }
1011
1012         private ReportData loadLinearReportData(int pageNo, String userId, int downloadLimit, HttpServletRequest request, boolean download) throws RaptorException {
1013                 String action = nvl(request.getParameter(AppConstants.RI_ACTION), request.getParameter("action"));;
1014
1015                 String reportSQL = "";
1016
1017                 if(action.endsWith("session")) {
1018                         reportSQL  = getWholeSQL();
1019                         wholeSQL = reportSQL;
1020                 } else {
1021                         reportSQL = generateSQL(reportParamValues, visualManager.getSortByColId(),
1022                                 visualManager.getSortByAscDesc(), userId, request);
1023                         wholeSQL = reportSQL;
1024                         setWholeSQL(wholeSQL);
1025                 }
1026                 DataSet ds = null;
1027                 String dbInfo = getDBInfo();
1028                 ds = ConnectionUtils.getDataSet(wholeSQL, dbInfo);
1029                 setReportDataSize(ds.getRowCount());
1030         //wholeSQL = reportSQL;
1031         HttpSession session = request.getSession();
1032         //debugLogger.debug(" ******** Download Limit ********* " + downloadLimit + " %%%%%%%%%%PAGE " + pageNo );
1033         List reportCols = null;
1034         StringBuffer colNames = new StringBuffer();
1035
1036         if(download && action.endsWith("session")) {
1037             reportCols = getAllColumns();
1038             colNames = new StringBuffer();
1039             for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1040
1041                 DataColumnType dc = (DataColumnType) iter.next();
1042                         if (colNames.length() > 0)
1043                                 colNames.append(", ");
1044                         colNames.append(dc.getColId());
1045             }
1046
1047         }
1048         else {
1049                 String pagedSQL = null; // reportSQL;
1050                 if (pageNo >= 0)
1051                         pagedSQL = generatePagedSQL(pageNo, userId, request, false, null);
1052                 else
1053                         pagedSQL = generateSubsetSQL(0, downloadLimit, userId, request, false, null);
1054         // replace the request parameter specified in the drill down
1055         reportCols = getAllColumns();
1056         colNames = new StringBuffer();
1057         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1058
1059             DataColumnType dc = (DataColumnType) iter.next();
1060                         if (colNames.length() > 0)
1061                                 colNames.append(", ");
1062                         colNames.append(dc.getColId());
1063             if (dc.isVisible()) {
1064                reportSQL = parseReportSQLForDrillDownParams(reportSQL, dc, request);
1065                pagedSQL  = parseReportSQLForDrillDownParams(pagedSQL, dc, request);
1066             }
1067         }
1068
1069                 cachedSQL = pagedSQL;
1070
1071
1072                 // try {
1073                 if(doesReportContainsGroupFormField()) {
1074                         if (pageNo >= 0)
1075                                 pagedSQL = generatePagedSQL(pageNo, userId, request, true, reportParamValues);
1076                         else
1077                                 pagedSQL = generateSubsetSQL(0, downloadLimit, userId, request, true, reportParamValues);
1078                 }
1079                 //check for Group formfield
1080                 //if groupformfield get columns from sql
1081
1082                 ds = ConnectionUtils.getDataSet(pagedSQL, dbInfo);
1083
1084                 if(doesReportContainsGroupFormField()) {
1085                         List reportCols1 = getAllColumns();
1086                         reportCols = new Vector();
1087                         outer:
1088                         for (Iterator iter = reportCols1.iterator(); iter.hasNext();) {
1089                                 DataColumnType dct = (DataColumnType) iter.next();
1090                                 for (int k=0; k<ds.getColumnCount(); k++) {
1091                                         if(dct.getColId().toUpperCase().trim().equals(ds.getColumnName(k).trim())) {
1092                                                 reportCols.add(dct);
1093                                                 continue outer;
1094                                         }
1095                                 }
1096                         }
1097
1098                         if (getFormFieldList() != null) {
1099                                 String paramValue = "";
1100                                 for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
1101                                         FormFieldType fft = (FormFieldType) iter.next();
1102                                         if(fft.isGroupFormField()!=null && fft.isGroupFormField().booleanValue()) {
1103                                 paramValue = Utils.oracleSafe(nvl(reportParamValues
1104                                                 .getParamValue(fft.getFieldId())));
1105                                 outer:
1106                                         for (Iterator iter1 = reportCols1.iterator(); iter1.hasNext();) {
1107                                                 DataColumnType dct = (DataColumnType) iter1.next();
1108                                                         if(("["+fft.getFieldName()+ "]").equals(dct.getColName().trim())) {
1109                                                                 dct.setDisplayName(paramValue);
1110                                                                 continue outer;
1111                                                         }
1112                                         }
1113
1114
1115                                         }
1116                                 }
1117                         }
1118                 }
1119
1120
1121                 // if ( (remDbInfo!=null) && (!remDbInfo.equals(AppConstants.DB_LOCAL)))
1122                 // {
1123                 // Globals.getRDbUtils().setDBPrefix(remDbInfo);
1124                 // ds = RemDbUtils.executeQuery(pagedSQL);
1125                 // }
1126                 // else
1127                 // ds = DbUtils.executeQuery(pagedSQL);
1128                 /*
1129                  * } catch(SQLException e) { throw new
1130                  * ReportSQLException("[ReportRuntime.loadLinearReportData]
1131                  * "+e.getMessage(), pagedSQL); }
1132                  */
1133
1134                 if (getReportDataSize() < 0)
1135                         if (pageNo < 0)
1136                                 setReportDataSize(ds.getRowCount());
1137                         else if (ds.getRowCount() <= getPageSize())
1138                                 setReportDataSize(ds.getRowCount());
1139                         else {
1140
1141                                 /*Pattern re1 = Pattern.compile("[Oo][Rr][Dd][Ee][Rr](.*?[^\r\n]*)[Bb][Yy]", Pattern.DOTALL);
1142                                 Pattern re2 = Pattern.compile("[Oo][Rr][Dd][Ee][Rr](.*?[^\r\n]*)[Bb][Yy]((.*?[^\r\n]*)|[\\s]|[^0-9a-zA-Z])\\)", Pattern.DOTALL);
1143                                 Matcher matcher = re1.matcher(reportSQL);
1144                                 Matcher matcher2 = null;
1145                                 int startPoint = reportSQL.length()-30;
1146                                 String startReportSQL = "";
1147                                 String endReportSQL = "";
1148                                 while(reportSQL.indexOf("xid", startPoint)!=-1)startPoint++;
1149                                 if (matcher.find(startPoint)) {
1150                                         startReportSQL  = reportSQL.substring(0, reportSQL.indexOf(matcher.group()));
1151                                         endReportSQL    = reportSQL.substring(reportSQL.indexOf(matcher.group()));
1152                                         matcher2 = re2.matcher(endReportSQL);
1153                                         if(matcher2.find())
1154                                                 endReportSQL    = endReportSQL.substring(matcher.group().length()-1);
1155                                         else
1156                                                 endReportSQL    = "";
1157                                         reportSQL = startReportSQL + endReportSQL;
1158                                 }*/
1159                                 String countSQL = "SELECT count(*) FROM (" + reportSQL + ")"+ (Globals.isPostgreSQL() || Globals.isMySQL()?" AS ":"")  +" x ";
1160                                 String dbType = "";
1161
1162                                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
1163                                         try {
1164                                          org.onap.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.onap.portalsdk.analytics.util.RemDbInfo();
1165                                          dbType = remDbInfo.getDBType(dbInfo);
1166                                          if (dbType.equals("DAYTONA") && reportSQL.trim().toUpperCase().startsWith("SELECT")) {
1167                                                 Pattern re1 = Pattern.compile("order(.*?[^\r\n]*)by", Pattern.DOTALL);
1168                                                 Matcher matcher = re1.matcher(reportSQL);
1169                                                 int startPoint = reportSQL.length()-30;
1170                                                 while(reportSQL.indexOf("xid", startPoint)!=-1)startPoint++;
1171                                                 if (matcher.find(startPoint)) {
1172                                                         reportSQL = reportSQL.substring(0, reportSQL.indexOf(matcher.group()));
1173                                                 }
1174                                                 countSQL = "SELECT count(*) FROM (" + reportSQL + ")"+ (Globals.isPostgreSQL() || Globals.isMySQL()?" AS ":"")  +" x";
1175                                                 countSQL = countSQL + " ("+ colNames+ ")";
1176                                          } else if (dbType.equals("DAYTONA")) {
1177                                                  setReportDataSize(50);
1178                                          }
1179                                         } catch (Exception ex) {
1180                                    throw new RaptorException(ex);
1181                                         }
1182                                 }
1183
1184                                 DataSet ds2 = null;
1185                                 // try {
1186                                 if(reportSQL.trim().toUpperCase().startsWith("SELECT")) {
1187                                         ds2 = ConnectionUtils.getDataSet(countSQL, dbInfo);
1188                                         if (ds2.getRowCount() > 0)
1189                                                 setReportDataSize(ds2.getInt(0, 0));
1190                                         else
1191                                                 throw new RuntimeException(
1192                                                                 "[ReportRuntime.loadLinearReportData] Unable to load report data size");
1193                                 } else
1194                                         setReportDataSize(50);
1195                                 // if ( (remDbInfo!=null) &&
1196                                 // (!remDbInfo.equals(AppConstants.DB_LOCAL))){
1197                                 // Globals.getRDbUtils().setDBPrefix(remDbInfo);
1198                                 // ds2 = RemDbUtils.executeQuery(countSQL);
1199                                 // }
1200                                 // else
1201                                 // ds2 = DbUtils.executeQuery(countSQL);
1202                                 /*
1203                                  * } catch(SQLException e) { throw new
1204                                  * ReportSQLException("[ReportRuntime.loadLinearReportData size]
1205                                  * "+e.getMessage(), countSQL); }
1206                                  */
1207
1208
1209                         } // else
1210         }
1211                 ReportData rd = new ReportData(pageNo, true);
1212
1213                 // Already defined changed for modifying request parameters
1214         //List reportCols = getAllColumns();
1215                 Vector visibleCols = new Vector(reportCols.size());
1216                 Vector formatProcessors = new Vector(reportCols.size());
1217
1218                 // ColumnHeaderRow chr = new ColumnHeaderRow();
1219                 // rd.reportColumnHeaderRows.addColumnHeaderRow(chr);
1220                 // chr.setRowHeight("30");
1221         int count =0 ;
1222
1223         /* ADDED */
1224                 ReportFormFields rff = getReportFormFields();
1225                 ReportFormFields childReportFormFields = null;
1226                 String fieldDisplayName = "";
1227                 String fieldValue = "";
1228
1229                 for (int c = 0; c < reportCols.size(); c++) {
1230             if(reportCols.get(c)!=null) {
1231                                 DataColumnType dct = (DataColumnType) reportCols.get(c);
1232                                 if(nvl(dct.getDependsOnFormField()).length()>0 && nvl(dct.getDependsOnFormField()).indexOf("[")!=-1) {
1233                                         for(int i = 0 ; i < rff.size(); i++) {
1234                                                 fieldDisplayName = "["+((FormField)rff.getFormField(i)).getFieldDisplayName()+"]";
1235                                                 fieldValue = "";
1236                                                 //if(dct.getOriginalDisplayName()==null) dct.setOriginalDisplayName(dct.getDisplayName());
1237                                                 if (dct.getDependsOnFormField().equals(fieldDisplayName)) {
1238                                                         fieldValue = nvl(request.getParameter(((FormField)rff.getFormField(i)).getFieldName()));
1239
1240                                                         if (fieldValue.length()>0) {
1241                                                                 if(!fieldValue.toUpperCase().equals("Y"))
1242                                                                         dct.setDisplayName(fieldValue);
1243                                                                 if(!dct.isVisible())
1244                                                                         dct.setVisible(true);
1245                                                         } else {
1246                                                                 dct.setVisible(false);
1247                                                         }
1248                                                 }
1249                                         }
1250             }
1251                 }
1252                 }
1253
1254         /* ADDED */
1255                 String displayName = "";
1256                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1257
1258             DataColumnType dc = (DataColumnType) iter.next();
1259
1260                         formatProcessors.add(count,new FormatProcessor(
1261                                         getSemaphoreById(dc.getSemaphoreId()), dc.getColType(), dc
1262                                                         .getColFormat(), getReportDefType().equals(
1263                                                         AppConstants.RD_SQL_BASED)));
1264
1265                         if (nvl(dc.getDrillDownURL()).length() > 0) {
1266                                 childReportFormFields = getChildReportFormFields(request,AppUtils.getDrillActionURL()+""+dc.getDrillDownURL());
1267                         }
1268                         if (dc.isVisible()) {
1269                                 visibleCols.add(count,dc);
1270                                 //if(dc.getColId().startsWith("group")) {
1271                                         for (int d = 0; d < reportCols.size(); d++) {
1272                                                 if(reportCols.get(d)!=null) {
1273                                                         DataColumnType dct1 = (DataColumnType) reportCols.get(d);
1274                                                         if(dct1.getColId().equals(dc.getColId()+"_name") && ds.getRowCount()>0) {
1275                                                                 displayName = ds.getString(0,dct1.getColId());
1276                                                                 dc.setDisplayName(displayName);
1277                                                         }
1278                                                 }
1279                                         }
1280                                 //}
1281
1282                                 String widthInPxls = dc.getDisplayWidthInPxls();
1283
1284                                 if(nvl(widthInPxls).endsWith("px"))
1285                                         dc.setDisplayWidthInPxls(widthInPxls);
1286                                 else {
1287                                         widthInPxls = widthInPxls+"px";
1288                                         dc.setDisplayWidthInPxls(widthInPxls+"px");
1289                                 }
1290
1291                                 rd.createColumn(dc.getColId(), dc.getDisplayName(), dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
1292                                                 visualManager.isColumnVisible(dc.getColId()), visualManager
1293                                                                 .getSortByColId().equals(dc.getColId()) ? visualManager
1294                                                                 .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);
1295                                 // chr.addColumnHeader(new ColumnHeader(dc.getDisplayName(),
1296                                 // (dc.getDisplayWidth()>100)?"10%":(""+dc.getDisplayWidth()+"%")));
1297                         } // if
1298             else {
1299               visibleCols.add(count,null);
1300                                 rd.createColumn(dc.getColId(), AppConstants.HIDDEN, dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
1301                                                 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);
1302 //              formatProcessors.add(count,null);
1303             }
1304             count++;
1305                 } // for
1306
1307                 if(getReportDefType().equals(AppConstants.RD_SQL_BASED_DATAMIN) && pageNo ==0) {
1308                         /*Vector v = null;
1309                         try {
1310                                 v = addForecastData(reportSQL);
1311                         } catch (Exception ex) {
1312                                 ex.printStackTrace();
1313                                 throw new RaptorException (ex);
1314                         }
1315                         session.setAttribute("FORECASTED_DATA", v);
1316                         DataSet dsWhole = ConnectionUtils.getDataSet(wholeSQL, dbInfo);
1317                         dsWhole.addAll(v);
1318                         session.setAttribute(AppConstants.RI_CHART_FORECAST_DATA, dsWhole);
1319                 }
1320
1321                 if(getReportDefType().equals(AppConstants.RD_SQL_BASED_DATAMIN) && session.getAttribute("FORECASTED_DATA")!=null) {
1322                         Vector vForecastedData = (Vector)session.getAttribute("FORECASTED_DATA");
1323                         if(vForecastedData.size() > 0)
1324                         ds.addAll(vForecastedData);*/
1325                 }
1326
1327
1328                 // Utils._assert(chr.size()==ds.getColumnCount(),
1329                 // "[ReportRuntime.loadLinearReportData] The number of visible columns
1330                 // does not match the number of data columns");
1331         //TODO: This should be optimized to accept -1 for flat file download
1332                 for (int r = 0; r < Math.min(ds.getRowCount(), ((pageNo < 0) ? (downloadLimit == -1?Globals.getFlatFileUpperLimit():Globals.getDownloadLimit() ) : getPageSize())); r++) {
1333                         DataRow dr = new DataRow();
1334                         rd.reportDataRows.addDataRow(dr);
1335
1336                         for (int c = 0; c < reportCols.size(); c++) {
1337                 if(reportCols.get(c)!=null) {
1338                                 DataColumnType dct = (DataColumnType) reportCols.get(c);
1339                                 DataValue dv = new DataValue();
1340                                 dr.addDataValue(dv);
1341                                 dv.setDisplayValue(ds.getString(r, ds.getColumnIndex(dct.getColId())));
1342                     dv.setColName(dct.getColName());
1343                     dv.setColId(dct.getColId());
1344                     dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
1345                     StringBuffer indentation = new StringBuffer("");
1346                     if(dct.getIndentation()!=null && dct.getIndentation()>0) {
1347                         for (int indent=0; indent< dct.getIndentation(); indent++) {
1348                                 indentation.append("\t");
1349                         }
1350                         dv.setNowrap("true");
1351                     }
1352                     dv.setIndentation(indentation.toString());
1353                     if(dct.isVisible()) {
1354
1355                         dv.setVisible(true);
1356                                         dv.setAlignment(dct.getDisplayAlignment());
1357                             dv.setDisplayTotal(dct.getDisplayTotal());
1358                             dv.setDisplayName(dct.getDisplayName());
1359
1360                                         if (nvl(dct.getDrillDownURL()).length() > 0) {
1361
1362                                 if(dv.getDisplayValue().length() > 0) {
1363                                         dv.setDrillDownURL(parseDrillDownURL(r, /* c, */ds, dct,request, childReportFormFields));
1364                                         dv.setDrillDowninPoPUp(dct.isDrillinPoPUp()!=null?dct.isDrillinPoPUp():false);
1365                                 }
1366
1367                                 if (dv.getDisplayValue().length() == 0) {
1368                                                         //dv.setDisplayValue("[NULL]");
1369                                     dv.setDisplayValue("");
1370                                 }
1371                                         } // if
1372
1373                                 } else {
1374                                         dv.setVisible(false);
1375                                         dv.setHidden(true);
1376                                 }
1377                     //System.out.println("in Linear report b4" + dr.getFormatId() + dr.getBgColorHtml() + dv.getDisplayValue());
1378
1379                     if(dr.getFormatId()!=null)
1380                                  ((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, true);
1381                     else
1382                                  ((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, false);
1383
1384                     //System.out.println("in Linear report After" + dr.getFormatId() + dr.getBgColorHtml() + dv.getDisplayValue());
1385                 } // if reportCols
1386                         } // for
1387                 } // for
1388
1389                 //Only if rownumber options is needed
1390                 //rd.addRowNumbers(pageNo, getPageSize());
1391
1392                 if (colDataTotalsLinear == null) {
1393                         if(!download && !action.endsWith("session"))
1394                         colDataTotalsLinear = generateColumnDataTotalsLinear(new ArrayList(reportCols), userId,
1395                                         getDbInfo(),request);
1396                         if(download && action.endsWith("session"))
1397                         colDataTotalsLinear = generateColumnDataTotalsLinear(new ArrayList(reportCols), userId,
1398                                         getDbInfo(), getTotalSql());
1399                 }
1400                 if (displayColTotals && colDataTotalsLinear != null) {
1401                         String totalLabel = "Total";
1402                         if (getReportDataSize() > getPageSize())
1403                                 totalLabel += "<br><font size=1>(for all pages)</font>";
1404
1405                         rd.setColumnDataTotalsLinear(colDataTotalsLinear, totalLabel);
1406                 } // if
1407         // Please note the below function doesn't set the visibility for dv since this is set in this function. - Sundar
1408                 rd.applyVisibility();
1409
1410                 return rd;
1411         } // loadLinearReportData
1412
1413
1414
1415         public DataRow generateColumnDataTotalsLinear(ArrayList reportCols, String userId,
1416                         String dbInfo, String reportSQL) throws RaptorException {
1417                 DataRow dr = null;
1418
1419                 boolean displayColTotals = false;
1420                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1421
1422                         DataColumnType dct = (DataColumnType) iter.next();
1423             if( dct != null ) {
1424                         if (nvl(dct.getDisplayTotal()).length() > 0) {
1425                                 displayColTotals = true;
1426                                 break;
1427                         } // if
1428             } // if checking dct
1429                 } // for
1430
1431                 DataSet ds = null;
1432                 if (displayColTotals) {
1433                         dr = new DataRow();
1434                         // ds =
1435                         // DbUtils.executeQuery(generateTotalSQLLinear(reportParamValues,
1436                         // userId));
1437                         ds = ConnectionUtils.getDataSet(reportSQL,
1438                                         dbInfo);
1439
1440                 for (int c = 0; c < reportCols.size(); c++) {
1441                         DataColumnType dct = (DataColumnType) reportCols.get(c);
1442             if ( dct != null ) {
1443                         DataValue dv = new DataValue();
1444
1445                         String totalValue = "";
1446                         if (ds != null)
1447                                 totalValue = ds.getString(0, c);
1448                         if (nvl(dct.getDisplayTotal()).length() > 0
1449                                         && (!dct.getDisplayTotal().equals(AppConstants.TOTAL_SUM_ID)))
1450                                 totalValue = nvl(AppConstants.TOTAL_FUNCTIONS.getNameById(dct
1451                                                 .getDisplayTotal()))
1452                                                 + ": " + totalValue;
1453                         dv.setDisplayValue(Utils.truncateTotalDecimals(totalValue));
1454
1455                         dv.setAlignment(dct.getDisplayAlignment());
1456                 dv.setColName(dct.getColName());
1457                 dv.setDisplayName(dct.getDisplayName());
1458                 dv.setColId(dct.getColId());
1459                 dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
1460                 StringBuffer indentation = new StringBuffer("");
1461                 if(dct.getIndentation()!=null && dct.getIndentation()>0) {
1462                     for (int indent=0; indent< dct.getIndentation(); indent++) {
1463                         indentation.append("\t");
1464                     }
1465                     dv.setNowrap("true");
1466                 }
1467                 dv.setIndentation(indentation.toString());
1468                 dv.setDisplayTotal(dct.getDisplayTotal());
1469                         dv.setBold(true);
1470                         dv.setVisible(dct.isVisible());
1471                         if(dv.isVisible())
1472                                 dr.addDataValue(dv);
1473             } // dct check
1474                 } // for
1475                 }
1476
1477                 return dr;
1478         } // generateColumnDataTotalsLinear
1479
1480
1481         public ReportData loadHiveLinearReportData(String reportSQL, String userId, int downloadLimit, HttpServletRequest request) throws RaptorException {
1482         wholeSQL = reportSQL;
1483         int countRows = getHiveReportCount(wholeSQL);
1484         setReportDataSize(countRows);
1485         if(countRows < 1001)
1486                 wholeSQL += " limit "+ countRows;
1487         else
1488                 wholeSQL += " limit "+ downloadLimit;
1489         HttpSession session = request.getSession();
1490
1491                 DataSet ds = null;
1492                 // try {
1493                 String dbInfo = getDBInfo();
1494
1495         List reportCols = getAllColumns();
1496         StringBuffer colNames = new StringBuffer();
1497         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1498
1499             DataColumnType dc = (DataColumnType) iter.next();
1500                         if (colNames.length() > 0)
1501                                 colNames.append(", ");
1502                         colNames.append(dc.getColId());
1503         }
1504
1505                 ds = ConnectionUtils.getDataSet(wholeSQL, dbInfo);
1506
1507                 ReportData rd = new ReportData(0, true);
1508
1509                 // Already defined changed for modifying request parameters
1510         //List reportCols = getAllColumns();
1511                 Vector visibleCols = new Vector(reportCols.size());
1512                 Vector formatProcessors = new Vector(reportCols.size());
1513
1514                 // ColumnHeaderRow chr = new ColumnHeaderRow();
1515                 // rd.reportColumnHeaderRows.addColumnHeaderRow(chr);
1516                 // chr.setRowHeight("30");
1517         int count =0 ;
1518
1519         /* ADDED */
1520                 ReportFormFields rff = getReportFormFields();
1521                 ReportFormFields childReportFormFields = null;
1522                 String fieldDisplayName = "";
1523                 String fieldValue = "";
1524
1525                 for (int c = 0; c < reportCols.size(); c++) {
1526             if(reportCols.get(c)!=null) {
1527                                 DataColumnType dct = (DataColumnType) reportCols.get(c);
1528                                 if(nvl(dct.getDependsOnFormField()).length()>0 && nvl(dct.getDependsOnFormField()).indexOf("[")!=-1) {
1529                                         for(int i = 0 ; i < rff.size(); i++) {
1530                                                 fieldDisplayName = "["+((FormField)rff.getFormField(i)).getFieldDisplayName()+"]";
1531                                                 fieldValue = "";
1532                                                 //if(dct.getOriginalDisplayName()==null) dct.setOriginalDisplayName(dct.getDisplayName());
1533                                                 if (dct.getDependsOnFormField().equals(fieldDisplayName)) {
1534                                                         fieldValue = nvl(request.getParameter(((FormField)rff.getFormField(i)).getFieldName()));
1535
1536                                                         if (fieldValue.length()>0) {
1537                                                                 if(!fieldValue.toUpperCase().equals("Y"))
1538                                                                         dct.setDisplayName(fieldValue);
1539                                                                 if(!dct.isVisible())
1540                                                                         dct.setVisible(true);
1541                                                         } else {
1542                                                                 dct.setVisible(false);
1543                                                         }
1544                                                 }
1545                                         }
1546             }
1547                 }
1548                 }
1549
1550         /* ADDED */
1551                 String displayName = "";
1552                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1553
1554             DataColumnType dc = (DataColumnType) iter.next();
1555
1556                         formatProcessors.add(count,new FormatProcessor(
1557                                         getSemaphoreById(dc.getSemaphoreId()), dc.getColType(), dc
1558                                                         .getColFormat(), getReportDefType().equals(
1559                                                         AppConstants.RD_SQL_BASED)));
1560
1561                         if (nvl(dc.getDrillDownURL()).length() > 0) {
1562                                 childReportFormFields = getChildReportFormFields(request,dc.getDrillDownURL());
1563                         }
1564                         if (dc.isVisible()) {
1565                                 visibleCols.add(count,dc);
1566                                 //if(dc.getColId().startsWith("group")) {
1567                                         for (int d = 0; d < reportCols.size(); d++) {
1568                                                 if(reportCols.get(d)!=null) {
1569                                                         DataColumnType dct1 = (DataColumnType) reportCols.get(d);
1570                                                         if(dct1.getColId().equals(dc.getColId()+"_name") && ds.getRowCount()>0) {
1571                                                                 displayName = ds.getString(0,dct1.getColId());
1572                                                                 dc.setDisplayName(displayName);
1573                                                         }
1574                                                 }
1575                                         }
1576                                 //}
1577
1578                                 String widthInPxls = dc.getDisplayWidthInPxls();
1579
1580                                 if(nvl(widthInPxls).endsWith("px"))
1581                                         dc.setDisplayWidthInPxls(widthInPxls);
1582                                 else {
1583                                         widthInPxls = widthInPxls+"px";
1584                                         dc.setDisplayWidthInPxls(widthInPxls+"px");
1585                                 }
1586
1587                                 rd.createColumn(dc.getColId(), dc.getDisplayName(), dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
1588                                                 visualManager.isColumnVisible(dc.getColId()), visualManager
1589                                                                 .getSortByColId().equals(dc.getColId()) ? visualManager
1590                                                                 .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);
1591                                 // chr.addColumnHeader(new ColumnHeader(dc.getDisplayName(),
1592                                 // (dc.getDisplayWidth()>100)?"10%":(""+dc.getDisplayWidth()+"%")));
1593                         } // if
1594             else {
1595               visibleCols.add(count,null);
1596                                 rd.createColumn(dc.getColId(), "", dc.getDisplayWidthInPxls(), dc.getDisplayHeaderAlignment(),
1597                                                 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);
1598 //              formatProcessors.add(count,null);
1599             }
1600             count++;
1601                 } // for
1602
1603                 ArrayList reportDataList = new ArrayList();
1604                 for (int r = 0; r < ds.getRowCount(); r++) {
1605                         DataRow dr = new DataRow();
1606                         rd.reportDataRows.addDataRow(dr);
1607
1608                         for (int c = 0; c < reportCols.size(); c++) {
1609                 if(reportCols.get(c)!=null) {
1610                                 DataColumnType dct = (DataColumnType) reportCols.get(c);
1611                                 DataValue dv = new DataValue();
1612                                 dr.addDataValue(dv);
1613                                 dv.setDisplayValue(ds.getString(r, c));
1614                     dv.setColName(dct.getColName());
1615                     dv.setColId(dct.getColId());
1616                     dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
1617                     StringBuffer indentation = new StringBuffer("");
1618                     if(dct.getIndentation()!=null && dct.getIndentation()>0) {
1619                         for (int indent=0; indent< dct.getIndentation(); indent++) {
1620                                 indentation.append("\t");
1621                         }
1622                         dv.setNowrap("true");
1623                     }
1624                     dv.setIndentation(indentation.toString());
1625                     if(dct.isVisible()) {
1626
1627                         dv.setVisible(true);
1628                                         dv.setAlignment(dct.getDisplayAlignment());
1629                             dv.setDisplayTotal(dct.getDisplayTotal());
1630                             dv.setDisplayName(dct.getDisplayName());
1631
1632                                         if (nvl(dct.getDrillDownURL()).length() > 0) {
1633
1634                                 if(dv.getDisplayValue().length() > 0) {
1635                                         dv.setDrillDownURL(parseDrillDownURL(r, /* c, */ds, dct,request, childReportFormFields));
1636                                         dv.setDrillDowninPoPUp(dct.isDrillinPoPUp()!=null?dct.isDrillinPoPUp():false);
1637                                 }
1638
1639                                 if (dv.getDisplayValue().length() == 0) {
1640                                                         //dv.setDisplayValue("[NULL]");
1641                                     dv.setDisplayValue("");
1642                                 }
1643                                         } // if
1644
1645                                 } else {
1646                                         dv.setVisible(false);
1647                                         dv.setHidden(true);
1648                                 }
1649                     //System.out.println("in Linear report b4" + dr.getFormatId() + dr.getBgColorHtml() + dv.getDisplayValue());
1650
1651                     if(dr.getFormatId()!=null)
1652                                  ((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, true);
1653                     else
1654                                  ((FormatProcessor) formatProcessors.get(c)).setHtmlFormatters(dv, dr, false);
1655
1656                     //System.out.println("in Linear report After" + dr.getFormatId() + dr.getBgColorHtml() + dv.getDisplayValue());
1657                 } // if reportCols
1658                         } // for
1659                         reportDataList.add(dr);
1660                 } // for
1661                 rd.setReportDataList(reportDataList);
1662                 //Only if rownumber options is needed
1663                 //rd.addRowNumbers(pageNo, getPageSize());
1664
1665                 if (colDataTotalsLinear == null)
1666                         colDataTotalsLinear = generateColumnDataTotalsLinear(new ArrayList(reportCols), userId,
1667                                         getDbInfo(),request);
1668                 if (displayColTotals && colDataTotalsLinear != null) {
1669                         String totalLabel = "Total";
1670                         if (getReportDataSize() > getPageSize())
1671                                 totalLabel += "<br><font size=1>(for all pages)</font>";
1672
1673                         rd.setColumnDataTotalsLinear(colDataTotalsLinear, totalLabel);
1674                 } // if
1675         // Please note the below function doesn't set the visibility for dv since this is set in this function. - Sundar
1676                 rd.applyVisibility();
1677
1678                 return rd;
1679         } // loadHiveLinearReportData
1680
1681         //For Hive reports
1682         public int getHiveReportCount(String sql) throws RaptorException {
1683                 //select t from (select count(*) t from (select * from program)x)x1;
1684                 int count = 0;
1685         String countSql = "select t from (select count(*) t from ("+ sql + ")" +  (Globals.isPostgreSQL() || Globals.isMySQL() ?" AS ":"")   + " x) AS x1";
1686
1687         DataSet ds = null;
1688                 // try {
1689                 String dbInfo = getDBInfo();
1690                 System.out.println("SQL getReportCount()- " + countSql);
1691                 try {
1692                         ds = ConnectionUtils.getDataSet(countSql, dbInfo);
1693                         int totalRows = 0;
1694                         String dbType = "";
1695                         if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
1696                                 try {
1697                                         org.onap.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.onap.portalsdk.analytics.util.RemDbInfo();
1698                                         dbType = remDbInfo.getDBType(dbInfo);
1699                                 } catch (Exception ex) {
1700                                         throw new RaptorException(ex);
1701                                 }
1702                         }
1703                         if( ds.getRowCount()>0) {
1704                                 count = Integer.parseInt(ds.getString(0,0));
1705                         }
1706                 } catch (NumberFormatException ex) {}
1707                 return count;
1708
1709         } // getReportCount
1710
1711
1712         /*private*/ public ReportFormFields getChildReportFormFields( HttpServletRequest request, String URL ) throws RaptorException {
1713                 String childReportID = getReportID(URL);
1714
1715                 ReportRuntime ddRr = (new ReportHandler()).loadReportRuntime(request, childReportID,
1716                                 false, 1);
1717
1718                 ReportFormFields ddReportFormFields = ddRr.getReportFormFields();
1719                 return ddReportFormFields;
1720         }
1721
1722         private String getReportID(String URL) {
1723                 URL = nvl(URL);
1724                 int pos = URL.toLowerCase().indexOf("c_master=")+9;
1725                 String reportID = "";
1726                 if(URL.toLowerCase().indexOf("&", pos)!=-1)
1727                         reportID = URL.substring(pos, URL.toLowerCase().indexOf("&", pos));
1728                 else
1729                         reportID = URL.substring(pos);
1730                 return reportID;
1731
1732         }
1733         /*private*/ public String parseDrillDownURL(int rowIdx, /* int colIdx, */DataSet ds, DataColumnType dct, HttpServletRequest request, ReportFormFields ddReportFormFields)
1734                         throws RaptorException {
1735                 Vector viewActions = DataCache.getDataViewActions();
1736                 javax.servlet.http.HttpSession session = request.getSession();
1737
1738                 StringBuffer dUrl = new StringBuffer();
1739
1740                 //String childReportID = getReportID(dct.getDrillDownURL());
1741
1742                 //ReportRuntime ddRr = (new ReportHandler()).loadReportRuntime(request, childReportID,
1743                         //      false, 1);
1744
1745                 //ReportFormFields ddReportFormFields = ddRr.getReportFormFields();
1746
1747                 boolean isViewAction = false;
1748                 int flag = 0;
1749                 String requestParam ="";
1750                 for (int k = 0; k < viewActions.size(); k++)
1751                         if (dct.getDrillDownURL().equals(
1752                                         AppUtils.getBaseActionURL() + ((String) viewActions.get(k))))
1753                                 isViewAction = true;
1754                 if (isViewAction) {
1755                         // Drill-down to record details
1756                         String param = nvl(dct.getDrillDownParams()); // i.e.
1757                                                                                                                         // "c_master=[bo1.RECID$]"
1758                         param = param.substring(AppUtils.getBaseActionParam().length() + 1,
1759                                         param.length() - 1); // i.e. "bo1.RECID$"
1760                         param = param.replace('.', '_'); // i.e. "bo1.RECID$"
1761
1762                         dUrl.append(AppUtils.getBaseActionParam());
1763                         dUrl.append(java.net.URLEncoder.encode(ds.getString(rowIdx, param.toLowerCase())));
1764                 } else {
1765                         // Drill-down to another report
1766                         // Replacing col ids with values
1767                         String param = nvl(dct.getDrillDownParams());
1768                         while (param.indexOf('[') >= 0) {
1769                                 int startIdx = param.indexOf('[');
1770                                 int endIdx = param.indexOf(']');
1771                                 StringBuffer sb = new StringBuffer();
1772                                 if(startIdx>endIdx) {
1773                                         if (endIdx < param.length() - 1)
1774                                                 sb.append(param.substring(endIdx + 1));
1775                                         param = sb.toString();
1776                     continue;
1777                                 }
1778                                 if (startIdx > 0)
1779                                         sb.append(param.substring(0, startIdx));
1780
1781                                 if (param.charAt(startIdx + 1) == '!') {
1782                                         // Parameter is a form field value
1783                                         String fieldId = param.substring(startIdx + 2, endIdx);
1784                                         String fieldValue = (String) reportParamValues.get(fieldId);
1785
1786                                         sb.append(java.net.URLEncoder.encode(nvl(fieldValue)));
1787                     //TODO Add a else if condition to check whether the param is from request Param
1788                     //TODO make a unique symbol like #
1789                 }else if (param.charAt(startIdx + 1) == '#') {
1790                     flag = 1;
1791                     String fieldId = param.substring(startIdx + 2, endIdx);
1792                     String fieldValue = request.getParameter(fieldId);
1793                     sb.append(java.net.URLEncoder.encode(nvl(fieldValue)));
1794
1795                 }else {
1796                                         // Parameter is a column value
1797                                         String fieldValue = "";
1798                                         String colValue = null;
1799                                         String colId = null;
1800                                         if (param.indexOf('!') < 0 || param.indexOf('!') > endIdx)
1801                                                 colId = param.substring(startIdx + 1, endIdx);
1802                                         else {
1803                                                 // Need to use NVL(column, form field)
1804                                                 colId = param.substring(startIdx + 1, param.indexOf('!'));
1805
1806                                                 String fieldId = param.substring(param.indexOf('!') + 1, endIdx);
1807                                                 FormField ff = getFormField(fieldId);
1808                                                 if (ff.getFieldType().equals(FormField.FFT_TEXTAREA)) {
1809                                                         fieldValue = reportParamValues.getParamValueforTextAreaDrilldown(fieldId);
1810                                                 } else
1811                                                         fieldValue = (String) reportParamValues.get(fieldId);
1812                                         } // else
1813
1814                                         DataColumnType column = getColumnById(colId);
1815                                         String columnName = "";
1816                                         int groupColumn = 0;
1817                                         int groupMatch = 0;
1818                                         if(column.getColName().startsWith("[")) {
1819                                                 groupColumn = 1;
1820                                                 columnName = column.getDisplayName();
1821                                                 for(ddReportFormFields.resetNext(); ddReportFormFields.hasNext(); ) {
1822                                                         FormField ff = ddReportFormFields.getNext();
1823                                                         if(ff.getFieldDisplayName().toLowerCase().equals(columnName.toLowerCase())) {
1824                                                                 groupMatch = 1;
1825                                                                 sb.delete(sb.lastIndexOf("&")+1, sb.length());
1826                                                                 sb.append(ff.getFieldName()+"=");
1827                                                         }
1828                                                 }
1829                                         }
1830                                         if (groupColumn == 0 || (groupColumn == 1 && groupMatch == 1)) {
1831                                                 String dependsOn = column.getDependsOnFormField();
1832                                                 if(nvl(dependsOn).length()>0)
1833                                                         System.out.println("DependsOn " + dependsOn);
1834                                                 if (column != null) {
1835                                                  //     if (column.getColType().equals(AppConstants.CT_DATE))
1836                                                                 //if (!nvl(column.getColFormat(), AppConstants.DEFAULT_DATE_FORMAT)
1837                                                                         //      .equals(AppConstants.DEFAULT_DATE_FORMAT))
1838                                                                         // Use extra column instead
1839                                                                         //commented out below line usually for Visual
1840                                                                         //colId += AppConstants.DD_COL_EXTENSION;
1841                                                         colValue = ds.getString(rowIdx, colId.toLowerCase());
1842                                                         // if SQL-Based and drill-down param is a date, decode
1843                                                         // it to the expected Oracle format **/
1844                                                         if (getReportDefType().equals(AppConstants.RD_SQL_BASED))
1845                                                                 if (!getColumnNoParseDateFlag(column))
1846                                                                         if (ReportParamDateValueParser.isDateParam(colValue))
1847                                                                                 colValue = ReportParamDateValueParser
1848                                                                                                 .formatDateParamValue(colValue);
1849                                                 } // if
1850
1851                                                 String suppressValues = "|" + nvl(dct.getDrillDownType()) + "|";
1852                                                 if (suppressValues.length() > 2
1853                                                                 && suppressValues.indexOf("|" + colValue + "|") >= 0)
1854                                                         // Parameter value is suppressed and not passed to the
1855                                                         // drill-down report
1856                                                         colValue = null;
1857
1858                                                 sb.append(java.net.URLEncoder.encode(nvl(colValue, fieldValue)));
1859                                         } else {
1860                                                 sb.delete(sb.lastIndexOf("&")+1, sb.length());
1861                                         }
1862                                 } // else
1863
1864                                 if (endIdx < param.length() - 1)
1865                                         sb.append(param.substring(endIdx + 1));
1866                                 param = sb.toString();
1867                         } // while
1868             if(Globals.getPassRequestParamInDrilldown()) {
1869                 if(param.indexOf('#') < 0) {
1870                     String[] reqParameters = Globals.getRequestParams().split(",");
1871                     String[] sessionParameters = Globals.getSessionParams().split(",");
1872                     for (int i = 0; i < reqParameters.length; i++) {
1873                         if(request.getParameter(reqParameters[i])!=null) {
1874                            if(!reqParameters[i].toUpperCase().startsWith("FF")){
1875                                 if(param.length()>0) {
1876                                   param += "&" + reqParameters[i]+"="
1877                                         + request.getParameter(reqParameters[i]);
1878                                 } else {
1879                                     param += "&" + reqParameters[i]+"="
1880                                     + request.getParameter(reqParameters[i]);
1881
1882                                 }
1883                            }
1884                         }
1885                     }
1886                     for (int i = 0; i < sessionParameters.length; i++) {
1887                         if(session.getAttribute(sessionParameters[i].toUpperCase())!=null) {
1888                            if(!sessionParameters[i].toUpperCase().startsWith("FF")){
1889                                 if(param.length()>0) {
1890                                   param += "&" + sessionParameters[i].toUpperCase()+"="
1891                                         + (String)session.getAttribute(sessionParameters[i].toUpperCase());
1892                                 } else {
1893                                     param += "&" + sessionParameters[i].toUpperCase()+"="
1894                                     + (String)session.getAttribute(sessionParameters[i].toUpperCase());
1895
1896                                 }
1897                            }
1898                         } else {
1899                             param += "&" + sessionParameters[i].toUpperCase()+"="
1900                             + (String)session.getAttribute(sessionParameters[i]);
1901
1902                         }
1903                     }
1904
1905                 }
1906             }
1907
1908                         dUrl.append(param.toString());
1909                         dUrl.append("&");
1910                         dUrl.append(AppConstants.RI_DISPLAY_CONTENT);
1911                         dUrl.append("=Y");
1912                         dUrl.append("&");
1913                         if(dct.isDrillinPoPUp()==null || (!dct.isDrillinPoPUp().booleanValue())) {
1914                                 dUrl.append(AppConstants.RI_SHOW_BACK_BTN);
1915                                 dUrl.append("=Y");
1916                                 dUrl.append("&");
1917                         }
1918                         dUrl.append(AppConstants.DRILLDOWN_INDEX);
1919                         int index = Integer.parseInt(nvl(AppUtils.getRequestValue(request, AppConstants.DRILLDOWN_INDEX), "0"));
1920                         /*
1921                         int form_index = Integer.parseInt(nvl(AppUtils.getRequestValue(request, AppConstants.FORM_DRILLDOWN_INDEX), "0"));
1922                         index = index>0 ? --index : 0;
1923                         form_index = form_index>0 ? --form_index : 0;*/
1924                 request.setAttribute(AppConstants.DRILLDOWN_INDEX, Integer.toString(index));
1925                 /*session.setAttribute(AppConstants.DRILLDOWN_INDEX, Integer.toString(index));
1926                 request.setAttribute(AppConstants.FORM_DRILLDOWN_INDEX, Integer.toString(form_index));
1927                 session.setAttribute(AppConstants.FORM_DRILLDOWN_INDEX, Integer.toString(form_index));*/
1928
1929                         dUrl.append("=" + AppUtils.getRequestNvlValue(request, AppConstants.DRILLDOWN_INDEX));
1930
1931             //TODO Add a if condition to check whether the param is request Param
1932                 } // if
1933
1934                 if (dUrl.length() > 0)
1935                         dUrl.insert(0, ((dct.getDrillDownURL()).indexOf('&') > 0) ? '&' : '&');
1936                 dUrl.insert(0, AppUtils.getDrillActionURL()+dct.getDrillDownURL());
1937
1938         //debugLogger.debug(" [[[[[[[[[[[[[[[[ " +  dUrl);
1939
1940                 return dUrl.toString();
1941         } // parseDrillDownURL
1942
1943         /** *********************************************************************************** */
1944
1945         public DataRow generateColumnDataTotalsLinear(ArrayList reportCols, String userId,
1946                         String dbInfo, HttpServletRequest request) throws RaptorException {
1947                 DataRow dr = null;
1948
1949                 displayColTotals = false;
1950                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1951
1952                         DataColumnType dct = (DataColumnType) iter.next();
1953             if( dct != null ) {
1954                         if (nvl(dct.getDisplayTotal()).length() > 0) {
1955                                 displayColTotals = true;
1956                                 break;
1957                         } // if
1958             } // if checking dct
1959                 } // for
1960
1961                 DataSet ds = null;
1962                 if (displayColTotals) {
1963                         dr = new DataRow();
1964                         // ds =
1965                         // DbUtils.executeQuery(generateTotalSQLLinear(reportParamValues,
1966                         // userId));
1967                         ds = ConnectionUtils.getDataSet(generateTotalSQLLinear(reportParamValues, userId,request),
1968                                         dbInfo);
1969
1970                 for (int c = 0; c < reportCols.size(); c++) {
1971                         DataColumnType dct = (DataColumnType) reportCols.get(c);
1972             if ( dct != null ) {
1973                         DataValue dv = new DataValue();
1974                         if(dv.isVisible())
1975                                 dr.addDataValue(dv);
1976
1977                         String totalValue = "";
1978                         if (ds != null)
1979                                 totalValue = ds.getString(0, c);
1980                         if (nvl(dct.getDisplayTotal()).length() > 0
1981                                         && (!dct.getDisplayTotal().equals(AppConstants.TOTAL_SUM_ID)))
1982                                 totalValue = nvl(AppConstants.TOTAL_FUNCTIONS.getNameById(dct
1983                                                 .getDisplayTotal()))
1984                                                 + ": " + totalValue;
1985                         dv.setDisplayValue(Utils.truncateTotalDecimals(totalValue));
1986
1987                         dv.setAlignment(dct.getDisplayAlignment());
1988                 dv.setColName(dct.getColName());
1989                 dv.setDisplayName(dct.getDisplayName());
1990                 dv.setColId(dct.getColId());
1991                 dv.setNowrap(nvl(dct.getNowrap(),"null").equals("false")?"null":nvl(dct.getNowrap(),"null"));
1992                 StringBuffer indentation = new StringBuffer("");
1993                 if(dct.getIndentation()!=null && dct.getIndentation()>0) {
1994                     for (int indent=0; indent< dct.getIndentation(); indent++) {
1995                         indentation.append("\t");
1996                     }
1997                     dv.setNowrap("true");
1998                 }
1999                 dv.setIndentation(indentation.toString());
2000                 dv.setDisplayTotal(dct.getDisplayTotal());
2001                         dv.setBold(true);
2002             } // dct check
2003                 } // for
2004                 }
2005
2006                 return dr;
2007         } // generateColumnDataTotalsLinear
2008
2009         private Vector generateDataTotalsCrossTab(String rowColPos, String userId, HttpServletRequest request)
2010                         throws RaptorException {
2011                 String sql = getWholeSQL();
2012                 Vector dataTotals = new Vector();
2013
2014                 boolean displayTotals = ((rowColPos.length() == 0) || (getCrossTabDisplayTotal(
2015                                 rowColPos).length() > 0));
2016                 if (rowColPos.equals(AppConstants.CV_COLUMN))
2017                         displayColTotals = displayTotals;
2018                 else if (rowColPos.equals(AppConstants.CV_ROW))
2019                         displayRowTotals = displayTotals;
2020
2021                 if (displayTotals) {
2022                         // DataSet ds =
2023                         // DbUtils.executeQuery(generateTotalSQLCrossTab(reportParamValues,
2024                         // rowColPos, userId));
2025                         String executeSql = generateTotalSQLCrossTab(
2026                                         sql, rowColPos, userId, request, reportParamValues);
2027                         DataSet ds = ConnectionUtils.getDataSet(executeSql, getDbInfo());
2028
2029                         for (int i = 0; i < ds.getRowCount(); i++) {
2030                                 Vector headerValues = new Vector();
2031                                 String totalValue = null;
2032
2033                                 int cPos = 0;
2034                                 for (Iterator iter = getAllColumns().iterator(); iter.hasNext();) {
2035                                         DataColumnType dct = (DataColumnType) iter.next();
2036
2037                                         if (rowColPos.length() > 0
2038                                                         && nvl(dct.getCrossTabValue()).equals(rowColPos)) {
2039                                                 DataValue dataValue = new DataValue();
2040                                                 dataValue.setBold(true);
2041                                                 dataValue.setAlignment("center");
2042                                                 dataValue.setDisplayValue(ds.getString(i, cPos++));
2043                                                 headerValues.add(dataValue);
2044
2045                                                 //headerValues.add(ds.getString(i, cPos++));
2046                                         }
2047                                         else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_VALUE))
2048                                                 totalValue = ds.getString(i, cPos++);
2049                                 } // for
2050
2051                                 dataTotals.add(new CrossTabTotalValue(headerValues, Utils
2052                                                 .truncateTotalDecimals(totalValue)));
2053                         } // for
2054                 } // if
2055
2056                 return dataTotals;
2057         } // generateDataTotalsCrossTab
2058
2059         /** *********************************************************************************** */
2060
2061         public void hideColVisual(String colId) {
2062                 visualManager.hideColumn(colId);
2063                 if (pageDataCache != null)
2064                         pageDataCache.columnVisualShowHide(colId, false);
2065         } // hideColVisual
2066
2067         public void showColVisual(String colId) {
2068                 visualManager.showColumn(colId);
2069                 if (pageDataCache != null)
2070                         pageDataCache.columnVisualShowHide(colId, true);
2071         } // showColVisual
2072
2073         public void sortColVisual(String colId) {
2074                 visualManager.setSortByColumn(colId);
2075                 resetCache(true);
2076
2077                 pageDataCache = null;
2078                 cachedPageNo = -1;
2079         } // sortColVisual
2080
2081         /** *********************************************************************************** */
2082
2083         public String generateDistinctValuesSQL(DataColumnType dct, String userId, HttpServletRequest request) throws RaptorException  {
2084                 return super.generateDistinctValuesSQL(reportParamValues, dct, userId, request);
2085         } // generateDistinctValuesSQL
2086
2087         public String getDbInfo() {
2088                 return this.cr.getDbInfo();
2089         }
2090
2091 //    private String fixSQL(StringBuffer sql) {
2092 //        int pos = 0;
2093 //        int pos_f_format = 0;
2094 //        int pos_t_format = 0;
2095 //        int pos_alias = 0;
2096 //        String format = "";
2097 //        String alias = null;
2098 //        if(sql.indexOf("SELECT", 7)!= -1) {
2099 //            pos = sql.indexOf("SELECT", 7);
2100 //            if(sql.indexOf("TO_CHAR", pos)!= -1){
2101 //                pos = sql.indexOf("TO_CHAR", pos);
2102 //                if(sql.indexOf("999",pos)!= -1) {
2103 //                    pos = sql.indexOf("999",pos);
2104 //                    pos_f_format = sql.lastIndexOf(", '", pos);
2105 //                    if(pos_f_format == -1 || (pos - pos_f_format > 10)) {
2106 //                        pos_f_format = sql.lastIndexOf(",'", pos);
2107 //                        pos_f_format -= 1;
2108 //                    }
2109 //                    pos = pos_f_format;
2110 //                    if(sql.indexOf("')", pos)!= -1) {
2111 //                        pos_t_format = sql.indexOf("')", pos);
2112 //                        //debugLogger.debug("pos_t - " + pos_t_format + " " + pos);
2113 //                        if(pos_t_format == -1 || (pos_t_format - pos > 20)) {
2114 //                            pos_t_format = sql.indexOf("' )", pos);
2115 //                            pos_t_format += 3;
2116 //                        }
2117 //                        else if (pos_t_format != -1)
2118 //                            pos_t_format += 2;
2119 //                        format = sql.substring(pos_f_format+3, pos_t_format);
2120 //                        //alias = sql.substring(pos_t_format+3, pos_t_format+6);
2121 //                        pos_alias = sql.indexOf(" ", pos_t_format);
2122 //                        alias = sql.substring(pos_alias+1, pos_alias+4);
2123 //                    }
2124 //                }
2125 //            }
2126 //
2127 //            if(sql.indexOf(alias)!=-1) {
2128 //                pos = sql.indexOf(alias);
2129 //                //debugLogger.debug(pos + " " + alias.length()+1 + "\n" + sql);
2130 //                sql.delete(pos,pos+4);
2131 //                sql.insert(pos, "TO_NUMBER("+alias+", '"+format+"')),'"+ format + "')");
2132 //                pos = sql.lastIndexOf("SUM", pos);
2133 //                if(pos==-1)
2134 //                    pos = sql.lastIndexOf("AVG", pos);
2135 //                else if (pos==-1)
2136 //                    pos = sql.lastIndexOf("COUNT", pos);
2137 //                else if (pos == -1)
2138 //                    pos = sql.lastIndexOf("STDDEV", pos);
2139 //                else if (pos == -1)
2140 //                    pos = sql.lastIndexOf("VARIANCE", pos);
2141 //                sql.insert(pos, "TO_CHAR (");
2142 //            }
2143 //
2144 //        }
2145 //
2146 //        //debugLogger.debug("Alias|" + alias + "|  Format " + format);
2147 //        //debugLogger.debug(sql.toString());
2148 //        return sql.toString();
2149 //    } // FixSQL
2150
2151     public String parseReportSQL(String sql) throws RaptorException {
2152         StringBuffer parsedSQL = new StringBuffer();
2153
2154         Vector updatedReportCols = new Vector();
2155
2156         curSQLParsePos = 0;
2157         int lastParsePos = curSQLParsePos;
2158         String lastToken = null;
2159         logger.debug(EELFLoggerDelegate.debugLogger, ("Flat File parseReportSQL ******* SQL  " + sql));
2160                 sql = sql.replaceAll("([\\s]*\\() (?!FROM)", "(");
2161                 sql = sql.replaceAll("[\\s]*\\)", ")");
2162         //sql = sql.replaceAll("[dD][eE][cC][oO][dD][eE] ", "decode");
2163         //sql = sql.replaceAll("[\\s]*\\(", "(");
2164         //sql = replaceNewLine(sql, "decode ", "decode");
2165         //sql = replaceNewLine(sql, "DECODE ", "decode");
2166         //sql = replaceNewLine(sql, "Decode ", "decode");
2167
2168         String  nextToken       = getNextSQLParseToken(sql, true);
2169         String  dbInfo          = getDbInfo();
2170         boolean isCYMBALScript = false;
2171                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
2172                         try {
2173                          org.onap.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.onap.portalsdk.analytics.util.RemDbInfo();
2174                          String dbType = remDbInfo.getDBType(dbInfo);
2175                          if (dbType.equals("DAYTONA") && !(nextToken.toUpperCase().equals("SELECT"))) {
2176                                  isCYMBALScript = true;
2177                          }
2178                         } catch (Exception ex) {
2179                    throw new RaptorException(ex);
2180                         }
2181                 }
2182                 if ( isCYMBALScript == false ) {
2183                 while (nextToken.length() > 0) {
2184                     //System.out.println("LastToken " + lastToken + " NextToken " + nextToken);
2185
2186
2187                     if (parsedSQL.length() == 0) {
2188                         if (nextToken.toUpperCase().equals("SELECT"))
2189                             parsedSQL.append("SELECT ");
2190                         else
2191                             throw new org.onap.portalsdk.analytics.error.ValidationException(
2192                                     "The SQL must start with the SELECT keyword.");
2193                     } else if (nextToken.toUpperCase().equals("DISTINCT")
2194                             && parsedSQL.toString().equals("SELECT ")) {
2195                         parsedSQL.append("DISTINCT ");
2196                     } else if (nextToken.equals("*")
2197                             && (parsedSQL.toString().equals("SELECT ") || parsedSQL.toString().equals(
2198                                     "SELECT DISTINCT "))) {
2199                         throw new org.onap.portalsdk.analytics.error.ValidationException(
2200                                 "You cannot use \"SELECT *\". Please specify select columns/expressions.");
2201                     } else if (nextToken.toUpperCase().equals("FROM")) {
2202                         if (lastToken != null) {
2203                             updatedReportCols.add(getParseSQLDataColumn(lastToken, null, parsedSQL,
2204                                     updatedReportCols, false));
2205                             lastToken = null;
2206                         }
2207
2208                         parsedSQL.append(" \n");
2209                         while (lastParsePos < sql.length()
2210                                 && Character.isWhitespace(sql.charAt(lastParsePos)))
2211                             lastParsePos++;
2212                         parsedSQL.append(sql.substring(lastParsePos));
2213                         break;
2214                     } else {
2215                         //System.out.println("Next Token  " + nextToken);
2216                         if (nextToken.charAt(nextToken.length() - 1) == ',') {
2217                             // The token ends with ,
2218                             nextToken = nextToken.substring(0, nextToken.length() - 1);
2219
2220                             if (nextToken.length() == 0) {
2221                                 if (lastToken != null) {
2222                                     updatedReportCols.add(getParseSQLDataColumn(lastToken, null,
2223                                             parsedSQL, updatedReportCols, false));
2224                                     lastToken = null;
2225                                 } // else just comma => ignore it
2226                             } else {
2227                                 //System.out.println("Next Token " + nextToken + " is Here" + " Last Token " + lastToken);
2228                                 if (lastToken != null) {
2229                                     updatedReportCols.add(getParseSQLDataColumn(lastToken, nextToken,
2230                                             parsedSQL, updatedReportCols, false));
2231                                     lastToken = null;
2232                                 } else
2233                                     updatedReportCols.add(getParseSQLDataColumn(nextToken, null,
2234                                             parsedSQL, updatedReportCols, false));
2235                             }
2236                         } else {
2237                             // The token doesn't end with ,
2238                             if (lastToken == null)
2239                                 lastToken = nextToken;
2240                             else {
2241                                 String token = getNextSQLParseToken(sql, false);
2242                                 //System.out.println(" ********** " + token + " " + lastToken);
2243                                 if (!token.toUpperCase().equals("FROM"))
2244                                     throw new org.onap.portalsdk.analytics.error.ValidationException(
2245                                             "|FROM keyword or a comma expected after [" + nextToken
2246                                                     + "].");
2247                                     //System.out.println("Next Token " + nextToken);
2248                                     updatedReportCols.add(getParseSQLDataColumn(lastToken, nextToken,
2249                                         parsedSQL, updatedReportCols, false));
2250                                 lastToken = null;
2251                             } // else
2252                         } // else
2253                     } // else
2254
2255                     lastParsePos = curSQLParsePos;
2256                     nextToken = getNextSQLParseToken(sql, true);
2257                 } // while
2258                 }  else { // if CYMBAL Script
2259                         nextToken = getNextCYMBALSQLParseToken(sql, true);
2260                         Pattern re                      = null;
2261                         Matcher matcher         = null;
2262                         String extracted        = null;
2263                         while (nextToken.length() > 0) {
2264                                 if (lastToken == null) lastToken = nextToken;
2265
2266                                 if( lastToken.toUpperCase().equals("DO DISPLAY")) {
2267                                         re              = Pattern.compile("each(.*)\\[.(.*?)\\]");   //\\[(.*?)\\]
2268                                         matcher = re.matcher(nextToken);
2269                                         if (matcher.find()) {
2270                                                 extracted = matcher.group();
2271                                                 re              = Pattern.compile("\\[(.*?)\\]");
2272                                         matcher = re.matcher(nextToken);
2273                                         if(matcher.find()) {
2274                                                 extracted = matcher.group();
2275                                                 extracted = extracted.substring(1,extracted.length()-2);
2276                                                 StringTokenizer sToken = new StringTokenizer(extracted);
2277                                                 while(sToken.hasMoreTokens()) {
2278                                             updatedReportCols.add(getParseSQLDataColumn("", sToken.nextToken(),
2279                                                         new StringBuffer(""), updatedReportCols, true));
2280                                                 }
2281                                         }
2282
2283                                         }
2284
2285                                 }
2286                                 lastToken = nextToken;
2287                                 nextToken = getNextCYMBALSQLParseToken(sql, true);
2288                         }
2289                 }
2290
2291         if (updatedReportCols.size() == 0)
2292             throw new org.onap.portalsdk.analytics.error.ValidationException(
2293                     "The SQL statement must have at least one column in the SELECT clause.");
2294
2295
2296         return parsedSQL.toString();
2297
2298     } // parseReportSQL
2299
2300         private String getNextCYMBALSQLParseToken(String sql, boolean updateParsePos) {
2301                 int braketCount = 0;
2302                 boolean isInsideQuote = false;
2303                 StringBuffer nextToken = new StringBuffer();
2304                 for (int idxNext = curSQLParsePos; idxNext < sql.length(); idxNext++) {
2305                         char ch = sql.charAt(idxNext);
2306
2307                         if (ch!='\n')
2308                                         nextToken.append(ch);
2309                         else break;
2310                 } // for
2311
2312                 return nextToken.toString();
2313         } // getNextSQLParseToken
2314
2315     private String getNextSQLParseToken(String sql, boolean updateParsePos) {
2316         int braketCount = 0;
2317         boolean isInsideQuote = false;
2318         StringBuffer nextToken = new StringBuffer();
2319         for (int idxNext = curSQLParsePos; idxNext < sql.length(); idxNext++) {
2320             char ch = sql.charAt(idxNext);
2321
2322             if (Character.isWhitespace(ch) || ch == ',') {
2323                 if (ch == ',')
2324                     nextToken.append(ch);
2325
2326                 if (nextToken.length() == 0)
2327                     continue;
2328                 else if (braketCount == 0 && (!isInsideQuote)) {
2329                     if (updateParsePos)
2330                         curSQLParsePos = idxNext + ((ch == ',') ? 1 : 0);
2331                     break;
2332                 } else if (ch != ',' && nextToken.charAt(nextToken.length() - 1) != ' ')
2333                     nextToken.append(' ');
2334             } else {
2335                 nextToken.append(ch);
2336
2337                 if (ch == '(' || ch == '[')
2338                     braketCount++;
2339                 else if (ch == ')' || ch == ']')
2340                     braketCount--;
2341                 else if (ch == '\''/* ||ch=='\"' */)
2342                     isInsideQuote = (!isInsideQuote);
2343             } // else
2344         } // for
2345
2346         return nextToken.toString();
2347     } // getNextSQLParseToken
2348
2349     private DataColumnType getParseSQLDataColumn(String sqlExpression, String colId,
2350             StringBuffer parsedSQL, Vector updatedReportCols, boolean isCYMBALScript) throws RaptorException {
2351         DataColumnType dct = null;
2352
2353         if (colId != null) {
2354             if (!isParseSQLColID(colId))
2355                 throw new org.onap.portalsdk.analytics.error.ValidationException(
2356                         "["
2357                                 + colId
2358                                 + "] must either be a valid column id consisting only of letters, numbers, and underscores, or there must be a comma in front of it.");
2359
2360             dct = getColumnById(colId);
2361         } else {
2362             // Getting unique column id
2363             colId = "";
2364             int colIdN = 0;
2365             for (int i = 0; (i < sqlExpression.length()) && (colIdN < 2); i++)
2366                 if (Character.isLetter(sqlExpression.charAt(i))) {
2367                     colId += sqlExpression.toLowerCase().charAt(i);
2368                     colIdN++;
2369                 } // if
2370
2371             colIdN = getAllColumns().size() + updatedReportCols.size();
2372             for (boolean idAlreadyUsed = true; idAlreadyUsed; colIdN++) {
2373                 String newColId = colId + colIdN;
2374                 idAlreadyUsed = false;
2375
2376                 for (Iterator iter = getAllColumns().iterator(); iter.hasNext();)
2377                     if (newColId.equals(((DataColumnType) iter.next()).getColId())) {
2378                         idAlreadyUsed = true;
2379                         break;
2380                     }
2381
2382                 if (!idAlreadyUsed)
2383                     for (Iterator iter = updatedReportCols.iterator(); iter.hasNext();)
2384                         if (newColId.equals(((DataColumnType) iter.next()).getColId())) {
2385                             idAlreadyUsed = true;
2386                             break;
2387                         }
2388             } // for
2389
2390             colId += (colIdN - 1);
2391         } // else
2392
2393         if (dct == null) {
2394                 dct = (new ObjectFactory()).createDataColumnType();
2395             dct.setColId(colId);
2396             dct.setDisplayWidth(10);
2397             dct.setDisplayAlignment("Left");
2398             dct.setVisible(true);
2399             dct.setGroupBreak(false); // ???
2400
2401             boolean isValidIdentifier = Character.isLetterOrDigit(sqlExpression.charAt(0));
2402             for (int i = 0; i < sqlExpression.length(); i++)
2403                 if (!(Character.isLetterOrDigit(sqlExpression.charAt(i))
2404                         || (sqlExpression.charAt(i) == '_') || (sqlExpression.charAt(i) == '$'))) {
2405                     isValidIdentifier = false;
2406                     break;
2407                 } // if
2408
2409             if (isValidIdentifier) {
2410                 dct.setDisplayName(sqlExpression);
2411             } else {
2412                 dct.setDisplayName(colId);
2413             } // else
2414         } // if
2415         if(!isCYMBALScript)
2416                 sqlExpression = sqlExpression.replaceAll(", '", ",'");
2417         dct.setDbColName(sqlExpression);
2418         dct.setColName(sqlExpression);
2419         dct.setCalculated(true);
2420         dct.setColType(AppConstants.CT_CHAR);
2421         dct.setDbColType(AppConstants.CT_CHAR);
2422         adjustColumnType(dct); // ???
2423
2424         if(!isCYMBALScript) {
2425                 if (parsedSQL.toString().equals("SELECT ")
2426                         || parsedSQL.toString().equals("SELECT DISTINCT "))
2427                     parsedSQL.append("\n\t");
2428                 else
2429                     parsedSQL.append(", \n\t");
2430                 parsedSQL.append(sqlExpression);
2431                 parsedSQL.append(" ");
2432                 parsedSQL.append(colId);
2433         }
2434
2435         return dct;
2436     } // getParseSQLDataColumn
2437
2438     private boolean isParseSQLColID(String token) {
2439         if (nvl(token).length() == 0)
2440             return false;
2441
2442         for (int i = 0; i < token.length(); i++) {
2443             char ch = token.charAt(i);
2444
2445             if (i == 0 && ch == '_')
2446                 return false;
2447
2448             if (!(Character.isLetterOrDigit(ch) || ch == '_'))
2449                 return false;
2450         } // for
2451
2452         return true;
2453     } // isParseSQLColID
2454
2455     /*private*/ public String parseReportSQLForDrillDownParams(String reportSQL, DataColumnType dataColumnRequest, HttpServletRequest request){
2456         String param = nvl(dataColumnRequest.getDrillDownParams());
2457         String sql = reportSQL;
2458         int pos = 0;
2459         while (param.indexOf('[', pos) >= 0) {
2460             int startIdx = param.indexOf('[',pos);
2461             int endIdx = param.indexOf(']',startIdx+1);
2462             pos = startIdx+1;
2463             StringBuffer sb = new StringBuffer();
2464             if (startIdx > 0)
2465                 sb.append(param.substring(0, startIdx));
2466             else break;
2467
2468             if (param.charAt(startIdx + 1) == '#') {
2469                 // Parameter is a form field value
2470                 String fieldId = param.substring(startIdx + 2, endIdx);
2471                 String fieldValue = ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(), request.getParameter(fieldId));
2472                 sql = Utils.replaceInString(sql, "[" + fieldId.toUpperCase()+"]", fieldValue );
2473             }
2474         }
2475         return sql;
2476     }
2477
2478 public List getMapMarkers(ReportData rd, org.onap.portalsdk.analytics.xmlobj.ReportMap xmlmap){
2479
2480                 ArrayList markers = new ArrayList();
2481                 int rNum = 0;
2482                 HashMap colHash = new HashMap();
2483
2484                 for(rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext(); rNum++) {
2485                         DataRow dr = rd.reportDataRows.getNext();
2486                         for(dr.resetNext(); dr.hasNext(); ) {
2487                                 DataValue dv = dr.getNext();
2488                                 colHash.put(dv.getColId(), dv.getDisplayValueLinkHtml());
2489                         }
2490
2491                         for (int i = 0; i < xmlmap.getMarkers().size(); i ++){
2492                                 Marker marker = new Marker("", "", "");
2493                                 org.onap.portalsdk.analytics.xmlobj.Marker m = (org.onap.portalsdk.analytics.xmlobj.Marker) xmlmap.getMarkers().get(i);
2494                                 String address = (String) colHash.get(m.getAddressColumn());
2495                                 String data = (String) colHash.get(m.getDataColumn());
2496                                 marker.setAddress(address);
2497                                 if (xmlmap.getAddAddressInDataYN() != null && xmlmap.getAddAddressInDataYN().equals("Y")){
2498                                         marker.setData(address + "<br/>" + data);
2499                                 }
2500                                 else{
2501                                         marker.setData(data);
2502                                 }
2503                                 marker.setColor(m.getMarkerColor());
2504                                 markers.add(marker);
2505                                 System.out.println("%%%%%%%%%%%% marker is : " + address + data);
2506                         }
2507
2508                 }
2509
2510                 return markers;
2511         }
2512
2513
2514
2515         public ReportParamValues getReportParamValues() {
2516                 return reportParamValues;
2517         }
2518
2519         public String getFormFieldFilled(String title) {
2520                 if( getFormFieldList()!=null && reportParamValues!=null && nvl(title).length()>0) {
2521                         for (Iterator iter1 = getFormFieldList().getFormField().iterator(); iter1.hasNext();) {
2522                                 FormFieldType fft = (FormFieldType) iter1.next();
2523                                 String fieldDisplay = getFormFieldDisplayName(fft);
2524                                 String fieldId = fft.getFieldId();
2525                                 if(!fft.getFieldType().equals(FormField.FFT_BLANK) && !fft.getFieldType().equals(FormField.FFT_LIST_MULTI) && !fft.getFieldType().equals(FormField.FFT_TEXTAREA)) {
2526                                         String paramValue = Utils.oracleSafe(nvl(reportParamValues.getParamValue(fieldId)));
2527                                         title = Utils.replaceInString(title, fieldDisplay, nvl(
2528                                     paramValue, ""));
2529                                 }
2530                         }
2531                 }
2532                 return title;
2533         }
2534
2535     public synchronized Object clone() {
2536         try {
2537                 return super.clone();
2538         } catch (CloneNotSupportedException e) {
2539             // this shouldn't happen, since we are Cloneable
2540             throw new InternalError("Cloning throws error.");
2541         }
2542     }
2543
2544     public VisualManager getVisualManager() {
2545         return visualManager;
2546     }
2547
2548         public String getReportSQLWithRowNum(String _orderBy, boolean asc) {
2549                 String sql = getWholeSQL();
2550         int closeBracketPos = 0;
2551         // Added reportSQLOnlyFirstPart which has Column information with Rownum
2552                 return nvl(getReportSQLOnlyFirstPart()) + " " + sql + ") x ";
2553
2554         }
2555
2556         public int getDisplayMode() {
2557                 return DISPLAY_MODE;
2558         }
2559
2560         public void setDisplayMode(int mode) {
2561                 DISPLAY_MODE = mode;
2562         }
2563
2564         public int getDateOption() {
2565                 return DATE_OPTION;
2566         }
2567
2568         public void setDateOption(int dateOption) {
2569                 DATE_OPTION = dateOption;
2570         }
2571
2572         public boolean isDisplayColTotals() {
2573                 return displayColTotals;
2574         }
2575
2576         public void setDisplayColTotals(boolean displayColTotals) {
2577                 this.displayColTotals = displayColTotals;
2578         }
2579
2580         public boolean isDisplayRowTotals() {
2581                 return displayRowTotals;
2582         }
2583
2584         public void setDisplayRowTotals(boolean displayRowTotals) {
2585                 this.displayRowTotals = displayRowTotals;
2586         }
2587
2588
2589         private boolean canPersistLinearReport() {
2590                 boolean visibleColExist = false;
2591
2592                 if (getDataSourceList().getDataSource().size() > 0) {
2593                         for (Iterator iter = getAllColumns().iterator(); iter.hasNext();) {
2594                                 DataColumnType dct = (DataColumnType) iter.next();
2595
2596                                 if (dct.isVisible()) {
2597                                         visibleColExist = true;
2598                                         break;
2599                                 }
2600                         } // for
2601                 } // if
2602
2603                 return visibleColExist;
2604         } // canPersistLinearReport
2605
2606         public void persistLinearReport(HttpServletRequest request)
2607                         throws RaptorException {
2608                 if (!canPersistLinearReport())
2609                         return;
2610
2611                 Connection connection = null;
2612                 try {
2613                         String userID = AppUtils.getUserID(request);
2614                         String reportXML = marshal();
2615                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report " + reportID
2616                                         + " XML marshalled succesfully"));
2617
2618                         // Update report
2619                         verifySQLBasedReportAccess(request);
2620                         reportSecurity.reportUpdate(request);
2621                         connection = DbUtils.startTransaction();
2622                         ReportLoader.updateCustomReportRec(connection, this, reportXML);
2623                         ReportLoader.createReportLogEntry(connection, reportID, userID,
2624                                         AppConstants.RLA_UPDATE, "", "");
2625                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report "
2626                                         + reportID + " succesfull"));
2627                         DbUtils.commitTransaction(connection);
2628                 } catch (RaptorException e) {
2629                         e.printStackTrace();
2630                         DbUtils.rollbackTransaction(connection);
2631                         throw e;
2632                 } finally {
2633                         DbUtils.clearConnection(connection);
2634                 }
2635         } // persistLinearReport
2636
2637         public void persistDashboardReport(HttpServletRequest request)
2638                         throws RaptorException {
2639
2640                 Connection connection = null;
2641                 try {
2642                         String userID = AppUtils.getUserID(request);
2643                         String reportXML = marshal();
2644                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report " + reportID
2645                                         + " XML marshalled succesfully"));
2646
2647                         // Update report
2648                         verifySQLBasedReportAccess(request);
2649                         reportSecurity.reportUpdate(request);
2650                         connection = DbUtils.startTransaction();
2651                         ReportLoader.updateCustomReportRec(connection, this, reportXML);
2652                         ReportLoader.createReportLogEntry(connection, reportID, userID,
2653                                         AppConstants.RLA_UPDATE, "", "");
2654                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report "
2655                                         + reportID + " succesfull"));
2656                         DbUtils.commitTransaction(connection);
2657                 } catch (RaptorException e) {
2658                         e.printStackTrace();
2659                         DbUtils.rollbackTransaction(connection);
2660                         throw e;
2661                 } finally {
2662                         DbUtils.clearConnection(connection);
2663                 }
2664         } // persistDashboardReport
2665         public String getTotalSql() {
2666                 return totalSql;
2667         }
2668
2669         public void setTotalSql(String totalSql) {
2670                 this.totalSql = totalSql;
2671         }
2672
2673         public void setTriggerFormFieldCheck( ReportFormFields FormFieldList, FormField selectedFormField) {
2674
2675                 for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
2676                         FormField ff = (FormField) iter.next();
2677                         if(!ff.getFieldName().equals(selectedFormField.getFieldName())) {
2678                                 if(nvl(ff.getBaseSQL()).length()>0 && ff.getBaseSQL().indexOf("["+selectedFormField.getFieldDisplayName() +"]")!= -1) {
2679                                         selectedFormField.setTriggerOtherFormFields(true);
2680                                 }
2681
2682                         }
2683                 }
2684
2685         }
2686
2687         public void setTriggerThisFormFieldCheck( ReportFormFields FormFieldList, FormField selectedFormField) {
2688
2689                 String sql = nvl(selectedFormField.getBaseSQL()).length()>0 ? selectedFormField.getBaseSQL():"";
2690                 for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
2691                         FormField ff = (FormField) iter.next();
2692                         if(!ff.getFieldName().equals(selectedFormField.getFieldName())) {
2693                                 if(sql.indexOf("["+ff.getFieldDisplayName() +"]")!= -1) {
2694                                         selectedFormField.setTriggerThisFormfield(true);
2695                                         break;
2696                                 }
2697
2698                         }
2699                 }
2700
2701         }
2702
2703         private boolean isAllowEdit(HttpServletRequest request) {
2704                 boolean allowEdit = false;
2705                 String userId = AppUtils.getUserID(request);
2706                 try {
2707                         if( AppUtils.isAdminUser(request) || AppUtils.isSuperUser(request) ) {
2708                                 allowEdit = true;
2709                         } else {
2710                                 if(getOwnerID().equals(userId)) allowEdit = true;
2711                                 else allowEdit = false;
2712                         }
2713                 } catch (RaptorException ex) {
2714                         allowEdit = false;
2715                 }
2716                 return allowEdit;
2717         }
2718         public ReportJSONRuntime createReportJSONRuntime(HttpServletRequest request, ReportData rd) {
2719                 String userId = AppUtils.getUserID(request);
2720                 ObjectMapper mapper = new ObjectMapper();
2721                 ReportJSONRuntime reportJSONRuntime = new ReportJSONRuntime();
2722                 reportJSONRuntime.setReportTitle(getReportTitle());
2723                 //reportJSONRuntime.setReportSubTitle(getReportSubTitle());
2724                 reportJSONRuntime.setReportID(getReportID());
2725                 reportJSONRuntime.setReportDescr(getReportDescr());
2726                 reportJSONRuntime.setReportName(getReportName());
2727                 reportJSONRuntime.setReportSubTitle(getReportSubTitle());
2728                 reportJSONRuntime.setAllowSchedule(isAllowSchedule());
2729                 reportJSONRuntime.setAllowEdit(isAllowEdit(request));
2730                 reportJSONRuntime.setColIdxTobeFreezed(getFrozenColumnId());
2731                 reportJSONRuntime.setNumFormCols(getNumFormColsAsInt());
2732                 //back button url
2733                 reportJSONRuntime.setBackBtnURL("");
2734                 String chartType = getChartType();
2735                 boolean displayChart = (nvl(chartType).length()>0)&&getDisplayChart();
2736                 boolean displayChartWizard = getDisplayChart();
2737                 reportJSONRuntime.setChartAvailable(displayChart);
2738                 reportJSONRuntime.setChartWizardAvailable(displayChartWizard);
2739                 reportJSONRuntime.setDisplayData(!isDisplayOptionHideData());
2740                 reportJSONRuntime.setDisplayForm(!isDisplayOptionHideForm());
2741                 reportJSONRuntime.setHideFormFieldsAfterRun(isHideFormFieldAfterRun());
2742                 reportJSONRuntime.setDisplayExcel(!isDisplayOptionHideExcelIcons());
2743                 reportJSONRuntime.setDisplayPDF(!isDisplayOptionHidePDFIcons());
2744                 ArrayList<IdNameValue> formFieldValues = new ArrayList<IdNameValue>();
2745                 ArrayList<FormFieldJSON> formFieldJSONList = new ArrayList<FormFieldJSON>();
2746                 if(getReportFormFields()!=null) {
2747                         formFieldJSONList = new ArrayList<FormFieldJSON>(getReportFormFields().size());
2748                 for (Iterator iter = getReportFormFields().iterator(); iter.hasNext();) {
2749                         formFieldValues = new ArrayList<IdNameValue>();
2750                         FormField ff = (FormField) iter.next();
2751                         ff.setDbInfo(getDbInfo());
2752                         FormFieldJSON ffJSON = new FormFieldJSON();
2753                         ffJSON.setFieldId(ff.getFieldName());
2754                         ffJSON.setFieldType(ff.getFieldType());
2755                         ffJSON.setFieldDisplayName(ff.getFieldDisplayName());
2756                         ffJSON.setHelpText(ff.getHelpText());
2757                         ffJSON.setValidationType(ff.getValidationType());
2758                         ffJSON.setVisible(ff.isVisible());
2759                         //ffJSON.setTriggerOtherFormFields(ff.getDependsOn());
2760                         IdNameList lookup =  null;
2761                         lookup = ff.getLookupList();
2762                         String selectedValue = "";
2763                         String oldSQL = "";
2764                         IdNameList lookupList = null;
2765                         boolean readOnly = false;
2766                         if(lookup!=null) {
2767                                         if(!ff.hasPredefinedList) {
2768                                                         IdNameSql lu = (IdNameSql) lookup;
2769                                                 String SQL = lu.getSql();
2770                                                 oldSQL = lu.getSql();
2771                                 setTriggerFormFieldCheck( getReportFormFields(), ff);
2772                                 ffJSON.setTriggerOtherFormFields(ff.isTriggerOtherFormFields());
2773                                                 SQL = parseAndFillReq_Session_UserValues(request, SQL, userId);
2774                                                 SQL = parseAndFillWithCurrentValues(request, SQL, ff);
2775                                                 String defaultSQL = lu.getDefaultSQL();
2776                                                 defaultSQL = parseAndFillReq_Session_UserValues(request, defaultSQL, userId);
2777                                                 defaultSQL = parseAndFillWithCurrentValues(request, defaultSQL, ff);
2778                                                 lookup = new IdNameSql(-1,SQL,defaultSQL);
2779
2780                                                 lookupList = lookup;
2781                                     try {
2782                                         lookup.loadUserData(0, "", ff.getDbInfo(), ff.getUserId());
2783                                     } catch (Exception e ){ e.printStackTrace(); //throw new RaptorRuntimeException(e);
2784                                                 }
2785                                 }
2786                                 lookup.trimToSize();
2787
2788                                 String[] requestValue = request.getParameterValues(ff.getFieldName());
2789
2790                                 if(lookup != null  && lookup.size() > 0) {
2791                                 for (lookup.resetNext(); lookup.hasNext();) {
2792                                         IdNameValue value = lookup.getNext();
2793                                         readOnly = value.isReadOnly();
2794                                         if(requestValue != null && Arrays.asList(requestValue).contains(value.getId())) {
2795                                                 //if(value.getId().equals(requestValue))
2796                                                  value.setDefaultValue(true);
2797                                         } else if (AppUtils.nvl(ff.getDefaultValue()).length()>0) {
2798                                                 if(ff.getDefaultValue().equals(value.getId())) {
2799                                                         value.setDefaultValue(true);
2800                                                 }
2801                                         }
2802                                         if(!(ff.getFieldType().equals(FormField.FFT_CHECK_BOX) || ff.getFieldType().equals(FormField.FFT_COMBO_BOX) || ff.getFieldType().equals(FormField.FFT_LIST_BOX)
2803                                                         || ff.getFieldType().equals(FormField.FFT_LIST_MULTI)) && value.isDefaultValue())
2804                                                 formFieldValues.add(value);
2805                                         else if(ff.getFieldType().equals(FormField.FFT_CHECK_BOX) || ff.getFieldType().equals(FormField.FFT_COMBO_BOX) || ff.getFieldType().equals(FormField.FFT_LIST_BOX)
2806                                                         || ff.getFieldType().equals(FormField.FFT_LIST_MULTI)) {
2807                                                 formFieldValues.add(value);
2808                                         }
2809                                         //break;
2810                                 }
2811                                 } else {
2812                                         if(requestValue!=null && requestValue.length>0) {
2813                                                 IdNameValue value = new IdNameValue(requestValue[0], requestValue[0], true, false);
2814                                                 formFieldValues.add(value);
2815                                         }
2816                                 }
2817
2818                         } else {
2819                                 setTriggerFormFieldCheck( getReportFormFields(), ff);
2820                                 ffJSON.setTriggerOtherFormFields(ff.isTriggerOtherFormFields());
2821                                 String[] requestValue = request.getParameterValues(ff.getFieldName());
2822                                 if(requestValue!=null && requestValue.length>0) {
2823                                         IdNameValue value = new IdNameValue(requestValue[0], requestValue[0], true, false);
2824                                         formFieldValues.add(value);
2825                                 } else if (AppUtils.nvl(ff.getDefaultValue()).length()>0) {
2826                                         IdNameValue value = new IdNameValue(ff.getDefaultValue(), ff.getDefaultValue(), true, false);
2827                                         formFieldValues.add(value);
2828                                 }
2829                         }
2830                 if(!ff.hasPredefinedList) {
2831             if(oldSQL != null && !oldSQL.equals("") && lookup!=null) {
2832                 ((IdNameSql)lookup).setSQL(oldSQL);
2833             }
2834                 }
2835
2836
2837
2838                         ffJSON.setFormFieldValues(formFieldValues);
2839                         formFieldJSONList.add(ffJSON);
2840                 } // for
2841           }
2842                 reportJSONRuntime.setFormFieldList(formFieldJSONList);
2843                 //reportJSONRuntime.setReportDataColumns(get);
2844                 int count = 0;
2845                 Map<String,Object> dvJSON = null;
2846                 if(rd!=null) {
2847                         count = 0;
2848                         reportJSONRuntime.setTotalRows(getReportDataSize());
2849                         ArrayList<ColumnHeader> colList = new ArrayList<ColumnHeader>();
2850                         ArrayList<Map<String,Object>> reportDataRows = new ArrayList<Map<String,Object>>();
2851                         for(rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext(); ) {
2852                                 count++;
2853                                 ColumnHeaderRow chr = rd.reportColumnHeaderRows.getNext();
2854                                 for(chr.resetNext(); chr.hasNext(); ) {
2855                                         colList.add(chr.getNext());
2856                                 }
2857                         }
2858                         if(getReportDataSize() > 0) {
2859                                 count = 0;
2860                                 for(rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext(); count++) {
2861                                         dvJSON = new HashMap<String,Object>();
2862                                         DataRow dr = rd.reportDataRows.getNext();
2863                                         for(dr.resetNext(); dr.hasNext(); ) {
2864                                                 DataValue dv = dr.getNext();
2865                                                 try {
2866                                                         dvJSON.put(dv.getColId(), dv);
2867                                                 } catch (Exception ex) {
2868                                                         ex.printStackTrace();
2869
2870                                                 }
2871                                         }
2872                                         reportDataRows.add(dvJSON);
2873                                 }
2874                         }
2875                         reportJSONRuntime.setReportDataColumns(colList);
2876                         reportJSONRuntime.setReportDataRows(reportDataRows);
2877                         //reportJSONRuntime.setSqlWhole(getWholeSQL());
2878                         reportJSONRuntime.setPageSize(getPageSize());
2879
2880                 }
2881
2882                 if(getReportDataSize() <= 0) {
2883                         reportJSONRuntime.setMessage(getEmptyMessage());
2884                 }
2885                 reportJSONRuntime.setSqlWhole(getWholeSQL());
2886                 return reportJSONRuntime;
2887
2888         }
2889
2890         public ReportJSONRuntime createFormFieldJSONRuntime(HttpServletRequest request) {
2891                 String userId = AppUtils.getUserID(request);
2892                 ObjectMapper mapper = new ObjectMapper();
2893                 ReportJSONRuntime reportJSONRuntime = new ReportJSONRuntime();
2894                 reportJSONRuntime.setReportTitle(getReportTitle());
2895                 reportJSONRuntime.setReportID(getReportID());
2896                 reportJSONRuntime.setReportName(getReportName());
2897                 reportJSONRuntime.setReportSubTitle(getReportSubTitle());
2898                 reportJSONRuntime.setAllowEdit(isAllowEdit(request));
2899                 reportJSONRuntime.setNumFormCols(getNumFormColsAsInt());
2900                 ArrayList<IdNameValue> formFieldValues = new ArrayList<IdNameValue>();
2901                 ArrayList<FormFieldJSON> formFieldJSONList = new ArrayList<FormFieldJSON>();
2902                 if(reportFormFields!=null) {
2903                         formFieldJSONList = new ArrayList<FormFieldJSON>(reportFormFields.size());
2904                         for (Iterator iter = reportFormFields.iterator(); iter.hasNext();) {
2905                                 formFieldValues = new ArrayList<IdNameValue>();
2906                                 FormField ff = (FormField) iter.next();
2907                                 FormFieldJSON ffJSON = new FormFieldJSON();
2908                                 ffJSON.setFieldId(ff.getFieldName());
2909                                 ffJSON.setFieldType(ff.getFieldType());
2910                                 ffJSON.setFieldDisplayName(ff.getFieldDisplayName());
2911                                 ffJSON.setHelpText(ff.getHelpText());
2912                                 ffJSON.setValidationType(ff.getValidationType());
2913                                 ffJSON.setFormFieldValues(formFieldValues);
2914                                 ffJSON.setVisible(ff.isVisible());
2915                                 formFieldJSONList.add(ffJSON);
2916                         }
2917                 for (Iterator iter = reportFormFields.iterator(); iter.hasNext();) {
2918                         formFieldValues = new ArrayList<IdNameValue>();
2919                         FormField ff = (FormField) iter.next();
2920                         ff.setDbInfo(getDbInfo());
2921                         for (Iterator iter1 = formFieldJSONList.iterator(); iter1.hasNext();) {
2922                                 FormFieldJSON ffJSON = (FormFieldJSON) iter1.next();
2923                                 if(ffJSON.getFieldId().equals(ff.getFieldName())) {
2924                                         IdNameList lookup =  null;
2925                                         lookup = ff.getLookupList();
2926                                         String selectedValue = "";
2927                                         String oldSQL = "";
2928                                         IdNameList lookupList = null;
2929                                         boolean readOnly = false;
2930                                         if(lookup!=null) {
2931                                                         if(!ff.hasPredefinedList) {
2932                                                                         IdNameSql lu = (IdNameSql) lookup;
2933                                                                 String SQL = lu.getSql();
2934                                                                 oldSQL = lu.getSql();
2935                                                 setTriggerFormFieldCheck( getReportFormFields(), ff);
2936                                                 ffJSON.setTriggerOtherFormFields(ff.isTriggerOtherFormFields());
2937                                                 setTriggerThisFormFieldCheck(getReportFormFields(), ff);
2938                                                                 SQL = parseAndFillReq_Session_UserValues(request, SQL, userId);
2939                                                                 SQL = parseAndFillOtherFormfieldValues(request, SQL, userId, formFieldJSONList);
2940                                                                 //SQL = parseAndFillWithCurrentValues(formGrid,SQL, ff);
2941                                                                 String defaultSQL = lu.getDefaultSQL();
2942                                                                 defaultSQL = parseAndFillReq_Session_UserValues(request, defaultSQL, userId);
2943                                                                 //defaultSQL = parseAndFillWithCurrentValues(formGrid,defaultSQL, ff);
2944                                                                 lookup = new IdNameSql(-1,SQL,defaultSQL);
2945
2946                                                                 lookupList = lookup;
2947                                                     try {
2948                                                         lookup.loadUserData(0, "", ff.getDbInfo(), ff.getUserId());
2949                                                     } catch (Exception e ){ e.printStackTrace(); //throw new RaptorRuntimeException(e);
2950                                                                 }
2951                                                 }
2952                                                 lookup.trimToSize();
2953
2954                                                 String requestValue = request.getParameter(ff.getFieldName());
2955                                                 ArrayList<String> requestValueList = new ArrayList<String>();
2956                                                 requestValueList.add(requestValue);
2957
2958                                                 /*if(ff.isTriggerThisFormfield()) {
2959                                                         refreshFormFieldsWithLatestValue(request, userId, ff, formFieldJSONList);
2960                                                 }*/
2961
2962
2963                                                 for (lookup.resetNext(); lookup.hasNext();) {
2964                                                         IdNameValue value = lookup.getNext();
2965                                                         readOnly = value.isReadOnly();
2966                                                         if(nvl(requestValue).length()>0) {
2967                                                                 if(value.getId().equals(requestValue))
2968                                                                  value.setDefaultValue(true);
2969                                                         }
2970                                                         formFieldValues.add(value);
2971                                                         //break;
2972                                                 }
2973
2974                                         } else {
2975                                                 setTriggerFormFieldCheck( getReportFormFields(), ff);
2976                                                 ffJSON.setTriggerOtherFormFields(ff.isTriggerOtherFormFields());
2977                                                 String[] requestValue = request.getParameterValues(ff.getFieldName());
2978                                                 if(requestValue!=null && requestValue.length>0) {
2979                                                         IdNameValue value = new IdNameValue(requestValue[0], requestValue[0], true, false);
2980                                                         formFieldValues.add(value);
2981                                                 } else if (AppUtils.nvl(ff.getDefaultValue()).length()>0) {
2982                                                         IdNameValue value = new IdNameValue(ff.getDefaultValue(), ff.getDefaultValue(), true, false);
2983                                                         formFieldValues.add(value);
2984                                                 }
2985                                         }
2986                                 if(!ff.hasPredefinedList) {
2987                             if(oldSQL != null && !oldSQL.equals("") && lookup!=null) {
2988                                 ((IdNameSql)lookup).setSQL(oldSQL);
2989                             }
2990                                 }
2991
2992
2993                                 //if(!ff.isTriggerThisFormfield()) {
2994                                         ffJSON.setFormFieldValues(formFieldValues);
2995                                 //}
2996
2997                                 break;
2998
2999                                 } //if
3000                         } //for
3001                 }//for
3002                 }//if
3003
3004                 reportJSONRuntime.setFormFieldList(formFieldJSONList);
3005                 //reportJSONRuntime.setReportDataColumns(get);
3006
3007                 return reportJSONRuntime;
3008
3009         }
3010
3011
3012         private String parseAndFillOtherFormfieldValues(HttpServletRequest request, String SQL, String userId,  ArrayList<FormFieldJSON> formFieldJSONList) {
3013                 ArrayList<IdNameValue> formFieldValues = new ArrayList<IdNameValue>();
3014                         String selectedValue = "";
3015                         String displayName = "";
3016                                 for (Iterator iter1 = formFieldJSONList.iterator(); iter1.hasNext();) {
3017                                         FormFieldJSON ffJSON = (FormFieldJSON) iter1.next();
3018                                         displayName = ffJSON.getFieldDisplayName();
3019                                         ArrayList<IdNameValue> formfieldvalues = ffJSON.getFormFieldValues();
3020                                         for (int i = 0; i< formfieldvalues.size(); i++) {
3021                                                 IdNameValue formfieldItem = formfieldvalues.get(i);
3022                                                 if(formfieldItem.isDefaultValue()) {
3023                                                         selectedValue = formfieldItem.getId();
3024                                                 }
3025                                         }
3026                                         SQL = Utils.replaceInString(SQL, "["+displayName+"]", selectedValue);
3027                                 }
3028                                 return SQL;
3029
3030         }
3031
3032 //      private void refreshFormFieldsWithLatestValue(HttpServletRequest request, String userId, FormField ff_src, ArrayList<FormFieldJSON> formFieldJSONList) {
3033 //              ArrayList<IdNameValue> formFieldValues = new ArrayList<IdNameValue>();
3034 //              List<String> requestValueList = null;
3035 //              IdNameList lookup =  null;
3036 //              lookup = ff_src.getLookupList();
3037 //              IdNameSql lu = (IdNameSql) lookup;
3038 //              String SQL = "" ;
3039 //              String oldSQL = "";
3040 //              String oldDefaultSQL = "";
3041 //              String defaultSQL  = "";
3042 //              IdNameList lookupList = null;
3043 //              if(lu != null) {
3044 //                      SQL = lu.getSql();
3045 //                      oldSQL = lu.getSql();
3046 //                      oldDefaultSQL = lu.getDefaultSQL();
3047 //                      defaultSQL = lu.getDefaultSQL();
3048 //              }
3049 //              boolean readOnly = false;
3050 //              for (Iterator iter1 = formFieldJSONList.iterator(); iter1.hasNext();) {
3051 //                              FormFieldJSON ffJSON = (FormFieldJSON) iter1.next();
3052 //                              if((ffJSON.getFieldId().equals(ff_src.getFieldName())) && ffJSON.isVisible()) {
3053 //                                      for (Iterator iter = reportFormFields.iterator(); iter.hasNext();) {
3054 //                                              formFieldValues = new ArrayList<IdNameValue>();
3055 //                                              FormField ff = (FormField) iter.next();
3056 //                                              if(!ff.getFieldName().equals(ff_src.getFieldName())) {
3057 //                                              //IdNameList lookup =  null;
3058 //                                              //lookup = ff.getLookupList();
3059 //                                              String selectedValue = "";
3060 //
3061 //
3062 //
3063 //                                              String [] requestParam = request.getParameterValues(ff.getFieldName());
3064 //                                              if(requestParam != null) {
3065 //                                                      requestValueList = Arrays.asList(request.getParameterValues(ff.getFieldName()));
3066 //
3067 //                                              } else {
3068 //                                                      requestValueList = new ArrayList<String>();
3069 //                                              }
3070 //
3071 //
3072 //                                              if(nvl(ff_src.getBaseSQL()).length()>0 && ff_src.getBaseSQL().indexOf("["+ff.getFieldDisplayName() +"]")!= -1) {
3073 //                                                      if(lookup!=null) {
3074 //                                                              try {
3075 //                                                                      if(!ff_src.hasPredefinedList) {
3076 //                                                                              String formatSelected = null;
3077 //                                                                              if(ff_src.getFieldType().equals(FormField.FFT_LIST_MULTI) || ff_src.getFieldType().equals(FormField.FFT_CHECK_BOX)) {
3078 //                                                                                              formatSelected = formatSelectedItems(requestValueList, ff_src.getFieldType());
3079 //                                                                              } else
3080 //                                                                                              formatSelected = requestValueList.size()>0?requestValueList.get(0):"";
3081 //                                                                              SQL = Utils.replaceInString(SQL, "["+ff_src.getFieldDisplayName()+"]", formatSelected);
3082 //                                                                              defaultSQL = Utils.replaceInString(defaultSQL, "["+ff_src.getFieldDisplayName()+"]", formatSelected);
3083 //                                                                              defaultSQL = parseAndFillWithCurrentValues(request, defaultSQL, ff_src);
3084 //                                                                              defaultSQL = parseAndFillReq_Session_UserValues(request, defaultSQL, userId);
3085 //                                                                              SQL = parseAndFillReq_Session_UserValues(request, SQL, userId);
3086 //                                                                              SQL = parseAndFillWithCurrentValues(request, SQL, ff_src);
3087 //
3088 //                                                                      }
3089 //                                                              } catch (Exception ex) {
3090 //                                                                              ex.printStackTrace();
3091 //                                                              }
3092 //
3093 //                                                      }
3094 //
3095 //                                              }
3096 //                                      }
3097 //                              }
3098 //
3099 //                                      if(nvl(ff_src.getBaseSQL()).length()>0) {
3100 //                                              lookup = new IdNameSql(-1,SQL,defaultSQL);
3101 //                                              lookupList = lookup;
3102 //                                              try {
3103 //                                                      lookup.loadUserData(0, "", ff_src.getDbInfo(), ff_src.getUserId());
3104 //                                              } catch (Exception e ){
3105 //                                                      e.printStackTrace(); //throw new RaptorRuntimeException(e);
3106 //                                              }
3107 //                                              if(!ff_src.hasPredefinedList) {
3108 //                                                      lookup.trimToSize();
3109 //                                                      for (lookup.resetNext(); lookup.hasNext();) {
3110 //                                                                      IdNameValue value = lookup.getNext();
3111 //                                                                      readOnly = value.isReadOnly();
3112 //                                                                      formFieldValues.add(value);
3113 //                                                              }
3114 //                                              }
3115 //                                              ffJSON.setFormFieldValues(formFieldValues);
3116 //                                      }
3117 //                                      if(!ff_src.hasPredefinedList) {
3118 //                                                      if(oldSQL != null && !oldSQL.equals("")) {
3119 //                                                              ((IdNameSql)lookup).setSQL(oldSQL);
3120 //                                                      }
3121 //                                                      if(oldDefaultSQL != null && !oldDefaultSQL.equals("")) {
3122 //                                                              ((IdNameSql)lookup).setDefaultSQL(oldDefaultSQL);
3123 //                                                      }
3124 //                                      }
3125 //
3126 //                              }
3127 //                      }
3128 //
3129 //      }
3130
3131
3132 //      private void triggerOtherFormFieldsWithThisValue(HttpServletRequest request, String userId, FormField ff_src, ArrayList<String> requestValueList, ArrayList<FormFieldJSON> formFieldJSONList) {
3133 //              ArrayList<IdNameValue> formFieldValues = new ArrayList<IdNameValue>();
3134 //              //ArrayList<FormFieldJSON> formFieldJSONList = new ArrayList<FormFieldJSON>();
3135 //              for (Iterator iter = reportFormFields.iterator(); iter.hasNext();) {
3136 //                      formFieldValues = new ArrayList<IdNameValue>();
3137 //                      FormField ff = (FormField) iter.next();
3138 //                      if(!ff_src.getFieldName().equals(ff.getFieldName())) {
3139 //                              for (Iterator iter1 = formFieldJSONList.iterator(); iter1.hasNext();) {
3140 //                                      FormFieldJSON ffJSON = (FormFieldJSON) iter1.next();
3141 //                                      if(ffJSON.getFieldId().equals(ff.getFieldName()) && ffJSON.isVisible()) {
3142 //                                              if(nvl(ff.getBaseSQL()).length()>0 && ff.getBaseSQL().indexOf("["+ff_src.getFieldDisplayName() +"]")!= -1) {
3143 //                                                      IdNameList lookup =  null;
3144 //                                              lookup = ff.getLookupList();
3145 //                                              String selectedValue = "";
3146 //                                              String oldSQL = "";
3147 //                                              String oldDefaultSQL = "";
3148 //                                              IdNameList lookupList = null;
3149 //                                              boolean readOnly = false;
3150 //                                                      if(lookup!=null) {
3151 //                                                              try {
3152 //                                                                      if(!ff.hasPredefinedList) {
3153 //                                                                                      IdNameSql lu = (IdNameSql) lookup;
3154 //                                                                                      String SQL = lu.getSql();
3155 //                                                                                      oldSQL = lu.getSql();
3156 //                                                                                      oldDefaultSQL = lu.getDefaultSQL();
3157 //                                                                                      String defaultSQL  = lu.getDefaultSQL();
3158 //                                                                                      String formatSelected = null;
3159 //                                                                                      if(ff_src.getFieldType().equals(FormField.FFT_LIST_MULTI) || ff_src.getFieldType().equals(FormField.FFT_CHECK_BOX)) {
3160 //                                                                                              formatSelected = formatSelectedItems(requestValueList, ff.getFieldType());
3161 //                                                                                      }
3162 //                                                                                      else
3163 //                                                                                              formatSelected = requestValueList.size()>0?requestValueList.get(0):"";
3164 //                                                                                      SQL = Utils.replaceInString(SQL, "["+ff_src.getFieldDisplayName()+"]", formatSelected);
3165 //                                                                                      defaultSQL = Utils.replaceInString(defaultSQL, "["+ff_src.getFieldDisplayName()+"]", formatSelected);
3166 //                                                                                      defaultSQL = parseAndFillWithCurrentValues(request, defaultSQL, ff_src);
3167 //                                                                                      defaultSQL = parseAndFillReq_Session_UserValues(request, defaultSQL, userId);
3168 //                                                                                      SQL = parseAndFillReq_Session_UserValues(request, SQL, userId);
3169 //                                                                                      SQL = parseAndFillWithCurrentValues(request, SQL, ff_src);
3170 //                                                                                      lookup = new IdNameSql(-1,SQL,defaultSQL);
3171 //                                                                                      lookupList = lookup;
3172 //                                                                                      try {
3173 //                                                                                              lookup.loadUserData(0, "", ff.getDbInfo(), ff.getUserId());
3174 //                                                                                      } catch (Exception e ){
3175 //                                                                                              e.printStackTrace(); //throw new RaptorRuntimeException(e);
3176 //                                                                                      }
3177 //                                                                      }
3178 //                                                                      lookup.trimToSize();
3179 //
3180 //
3181 //
3182 //                                                                      for (lookup.resetNext(); lookup.hasNext();) {
3183 //                                                                              IdNameValue value = lookup.getNext();
3184 //                                                                              readOnly = value.isReadOnly();
3185 ////                                                                            if(nvl(requestValue).length()>0) {
3186 ////                                                                                    if(value.getId().equals(requestValue))
3187 ////                                                                                     value.setDefaultValue(true);
3188 ////                                                                            }
3189 //                                                                              formFieldValues.add(value);
3190 //                                                                              //break;
3191 //                                                                      }
3192 //
3193 //                                                                      ffJSON.setFormFieldValues(formFieldValues);
3194 //
3195 //                                                                      if(!ff.hasPredefinedList) {
3196 //                                                                              if(oldSQL != null && !oldSQL.equals("")) {
3197 //                                                                                      ((IdNameSql)lookup).setSQL(oldSQL);
3198 //                                                                              }
3199 //                                                                              if(oldDefaultSQL != null && !oldDefaultSQL.equals("")) {
3200 //                                                                                      ((IdNameSql)lookup).setDefaultSQL(oldDefaultSQL);
3201 //                                                                              }
3202 //                                                                      }
3203 //                                                              } catch (Exception ex) {
3204 //                                                                      ex.printStackTrace();
3205 //                                                              }
3206 //                                                      }
3207 //
3208 //                                              } //ff baseSQL
3209 //                                      }
3210 //                              }
3211 //                      }
3212 //              }
3213 //
3214 //      }
3215
3216         public String formatSelectedItems(List selectedItems, String type) {
3217                 StringBuffer value = new StringBuffer("");
3218                 int count = 0;
3219                 boolean multiple = false;
3220                 if(type.equals("LIST_MULTI_SELECT"))
3221                         multiple = true;
3222                 //multiple = (selectedItems.size()>1);
3223                 for(Iterator iter = selectedItems.iterator(); iter.hasNext(); ) {
3224                         count++;
3225                         String entry = (String) iter.next();
3226                         if(count == 1 && multiple)
3227                                 value.append("(");
3228                         //if(type.equals(FormField.FFT_CHECK_BOX))
3229                         /*if(type.equals(FormField.FFT_CHECK_BOX))
3230                                 value.append("'"+Utils.oracleSafe(entry)+"'");
3231                         else*/ if (type.equals(FormField.FFT_LIST_MULTI))
3232                                 value.append("'"+Utils.oracleSafe(entry)+"'");
3233                         else if(type.equals(FormField.FFT_LIST_BOX))
3234                                 value.append(Utils.oracleSafe(entry));
3235                         else
3236                                 value.append("'"+Utils.oracleSafe(entry)+"'");
3237                         if((count < selectedItems.size()) && multiple)
3238                                 value.append(",");
3239                         if((count == selectedItems.size()) && multiple)
3240                                 value.append(")");
3241                 }
3242
3243                 if(value.length()>0)
3244                         return value.toString();
3245                 else
3246                         return null;
3247         }
3248
3249 public String parseAndFillWithCurrentValues(HttpServletRequest request, String sql, FormField source_Formfield) {
3250
3251                 if (getFormFieldList() != null) {
3252                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
3253                                 FormFieldType fft = (FormFieldType) iter.next();
3254                                 String fieldId = fft.getFieldId();
3255                                 String fieldDisplay = getFormFieldDisplayName(fft);
3256                                 String formfield_value = "";
3257                                 List<String> selectedItems = new ArrayList<String>();
3258                                 //Added so that Combo Box in old RAPTOR definition is translated to List box
3259                                 if(fft.getFieldType().equals(FormField.FFT_COMBO_BOX)) {
3260                                         fft.setFieldType(FormField.FFT_LIST_BOX);
3261                                 }
3262                                 if(!fft.getFieldType().equals(FormField.FFT_BLANK)) {
3263                                         //if(source_Formfield==null || (source_Formfield!=null && !fft.getFieldId().equals(source_Formfield.getFieldName()))) {
3264                                         // Add oracle safe
3265                                         // Add param base sql
3266                                         if(fft.getFieldType().equals(FormField.FFT_LIST_MULTI) || fft.getFieldType().equals(FormField.FFT_CHECK_BOX)) {
3267                                                 if(request.getParameterValues(fieldId)!=null && request.getParameterValues(fieldId).length > 0) {
3268
3269                                                         selectedItems = Arrays.asList(request.getParameterValues(fieldId));
3270                                                         formfield_value = formatSelectedItems(selectedItems, fft.getFieldType());
3271                                                 } else {
3272                                                         formfield_value = "";
3273                                                 }
3274                                         } else  if(fft.getFieldType().equals(FormField.FFT_RADIO_BTN)) {
3275                                                 if(request.getParameter(fieldId)!=null) {
3276                                                          formfield_value = request.getParameter(fieldId);
3277                                                 } else {
3278                                                         formfield_value = "";
3279                                                 }
3280                                         } else if (fft.getFieldType().equals(FormField.FFT_HIDDEN)) {
3281                                                 if(request.getParameter(fieldId)!=null) {
3282                                                          formfield_value = request.getParameter(fieldId);
3283                                                 } else {
3284                                                         formfield_value = "";
3285                                                 }
3286                                         } else if((fft.getFieldType().equals(FormField.FFT_TEXT)  || fft.getFieldType().equals(FormField.FFT_TEXTAREA)) &&
3287                                                    (!fft.getValidationType().equals(FormField.VT_DATE) && !fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)&&
3288                                                              !fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) && !fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) ) {
3289                                                 if(request.getParameter(fieldId)!=null) {
3290                                                          formfield_value = request.getParameter(fieldId);
3291                                                 } else {
3292                                                         formfield_value = "";
3293                                                 }
3294                            } else if (fft.getValidationType().equals(FormField.VT_DATE) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)||
3295                                                      fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
3296                                                 /*if(formGrid.hasFellow(fieldId, true)) {
3297                                                         Datebox tb = (Datebox) formGrid.getFellowIfAny(fieldId, true);
3298                                                         try {
3299                                                         formfield_value = tb.getText();
3300                                                         } catch (WrongValueException ex) {
3301                                                                 formfield_value = "";
3302                                                         }
3303                                                         if(AppUtils.nvl(formfield_value).length() > 0) {
3304                                                                 if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)) {
3305                                                                                 if(formGrid.hasFellow(fieldId+"_Hr", true)) {
3306                                                                                         Label hiddenLbHr = (Label) formGrid.getFellowIfAny(fieldId+"_Hr", true);
3307                                                                                         formfield_value = formfield_value + " " + hiddenLbHr.getValue();
3308                                                                                 }
3309                                                                         } else if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN)) {
3310                                                                                 if(formGrid.hasFellow(fieldId+"_Min", true)) {
3311                                                                                         Label hiddenLbHr = (Label) formGrid.getFellowIfAny(fieldId+"_Hr", true);
3312                                                                                         //formfield_value = formfield_value + " " + hiddenLbHr.getValue();
3313                                                                                         Label hiddenLbMin = (Label) formGrid.getFellowIfAny(fieldId+"_Min", true);
3314                                                                                         formfield_value = formfield_value + " " + hiddenLbHr.getValue() + ":" +hiddenLbMin.getValue();
3315
3316                                                                                 }
3317                                                                         } else if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
3318                                                                                 if(formGrid.hasFellow(fieldId+"_Sec", true)) {
3319                                                                                         Label hiddenLbHr = (Label) formGrid.getFellowIfAny(fieldId+"_Hr", true);
3320                                                                                         //formfield_value = formfield_value + " " + hiddenLbHr.getValue();
3321                                                                                         Label hiddenLbMin = (Label) formGrid.getFellowIfAny(fieldId+"_Min", true);
3322                                                                                         //formfield_value = formfield_value + " " + hiddenLbHr.getValue() + ":" +hiddenLbMin.getValue();
3323                                                                                         Label hiddenLbSec = (Label) formGrid.getFellowIfAny(fieldId+"_Sec", true);
3324                                                                                         formfield_value = formfield_value + " " + hiddenLbHr.getValue() + ":" +hiddenLbMin.getValue()+ ":" +hiddenLbSec.getValue();
3325
3326                                                                                 }
3327                                                                         }
3328                                                         }
3329
3330                                                 } else {
3331                                                         formfield_value = "";
3332                                                 }*/
3333                            } else if ((fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)||
3334                                              fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC))) {
3335                                                 /*if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)||
3336                                                              fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
3337                                                                         if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)) {
3338                                                                                 if(formGrid.hasFellow(fieldId+"_Hr", true)) {
3339                                                                                         Label hiddenLbHr = (Label) formGrid.getFellowIfAny(fieldId+"_Hr", true);
3340                                                                                         formfield_value = formfield_value + " " + hiddenLbHr.getValue();
3341                                                                                 }
3342                                                                         } else if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN)) {
3343                                                                                 if(formGrid.hasFellow(fieldId+"_Min", true)) {
3344                                                                                         Label hiddenLbHr = (Label) formGrid.getFellowIfAny(fieldId+"_Hr", true);
3345                                                                                         //formfield_value = formfield_value + " " + hiddenLbHr.getValue();
3346                                                                                         Label hiddenLbMin = (Label) formGrid.getFellowIfAny(fieldId+"_Min", true);
3347                                                                                         formfield_value = formfield_value + " " + hiddenLbHr.getValue() + ":" +hiddenLbMin.getValue();
3348
3349                                                                                 }
3350                                                                         } else if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
3351                                                                                 if(formGrid.hasFellow(fieldId+"_Sec", true)) {
3352                                                                                         Label hiddenLbHr = (Label) formGrid.getFellowIfAny(fieldId+"_Hr", true);
3353                                                                                         //formfield_value = formfield_value + " " + hiddenLbHr.getValue();
3354                                                                                         Label hiddenLbMin = (Label) formGrid.getFellowIfAny(fieldId+"_Min", true);
3355                                                                                         //formfield_value = formfield_value + " " + hiddenLbHr.getValue() + ":" +hiddenLbMin.getValue();
3356                                                                                         Label hiddenLbSec = (Label) formGrid.getFellowIfAny(fieldId+"_Sec", true);
3357                                                                                         formfield_value = formfield_value + " " + hiddenLbHr.getValue() + ":" +hiddenLbMin.getValue()+ ":" +hiddenLbSec.getValue();
3358
3359                                                                                 }
3360                                                                         }
3361
3362                                                                 } */
3363                                         } else if (fft.getFieldType().equals(FormField.FFT_TEXT_W_POPUP)) {
3364                                                 if(request.getParameter(fieldId)!=null) {
3365                                                          formfield_value = request.getParameter(fieldId);
3366                                                 } else {
3367                                                         formfield_value = "";
3368                                                 }
3369                                         } else if (fft.getFieldType().equals(FormField.FFT_LIST_BOX)) {
3370                                                 if(request.getParameter(fieldId)!=null) {
3371                                                          formfield_value = request.getParameter(fieldId);
3372                                                 } else {
3373                                                         formfield_value = "";
3374                                                 }
3375                                  //}
3376                                 }
3377                                 if(nvl(formfield_value).length()>0) {
3378                                         sql = Utils.replaceInString(sql, fieldDisplay, formfield_value);
3379                                 } else {
3380                                         sql = Utils.replaceInString(sql, "'"+fieldDisplay+"'", "null");
3381                                         sql = Utils.replaceInString(sql, fieldDisplay, "null");
3382                                         //sql = Utils.replaceInString(sql, fieldDisplay, "''");
3383                                 }
3384                         } // for
3385                 }
3386                 }
3387                 return sql;
3388         }
3389         public String parseAndFillReq_Session_UserValues(HttpServletRequest request, String sql, String user_id) {
3390                 HttpSession session = request.getSession();
3391
3392         String[] reqParameters = Globals.getRequestParams().split(",");
3393         String[] sessionParameters = Globals.getSessionParams().split(",");
3394         String[] scheduleSessionParameters = Globals.getSessionParamsForScheduling().split(",");
3395
3396
3397         if(AppUtils.nvl(sql).length()>0) {
3398             for (int i = 0; i < reqParameters.length; i++) {
3399                 if(!reqParameters[i].startsWith("ff") && (request.getParameter(reqParameters[i].toUpperCase())!=null && request.getParameter(reqParameters[i].toUpperCase()).length() > 0))
3400                  sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i].toUpperCase()) );
3401                 else if (request.getParameter(reqParameters[i])!=null && request.getParameter(reqParameters[i]).length() > 0)
3402                  sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i]) );
3403             }
3404
3405             for (int i = 0; i < scheduleSessionParameters.length; i++) {
3406                 //debugLogger.debug(" Session " + " scheduleSessionParameters[i] " + scheduleSessionParameters[i].toUpperCase() + " " + request.getParameter(scheduleSessionParameters[i]));
3407                 if(request.getParameter(scheduleSessionParameters[i])!=null && request.getParameter(scheduleSessionParameters[i]).trim().length()>0 )
3408                         sql = Utils.replaceInString(sql, "[" + scheduleSessionParameters[i].toUpperCase()+"]", request.getParameter(scheduleSessionParameters[i]) );
3409                 if(request.getAttribute(scheduleSessionParameters[i])!=null && ((String)request.getAttribute(scheduleSessionParameters[i])).trim().length()>0 )
3410                         sql = Utils.replaceInString(sql, "[" + scheduleSessionParameters[i].toUpperCase()+"]", (String) request.getAttribute(scheduleSessionParameters[i]) );
3411
3412             }
3413
3414             for (int i = 0; i < sessionParameters.length; i++) {
3415                 //if(!sessionParameters[i].startsWith("ff"))
3416                  //fieldSQL = Utils.replaceInString(fieldSQL, "[" + sessionParameters[i].toUpperCase()+"]", (String)session.getAttribute(sessionParameters[i].toUpperCase()) );
3417                 //else {
3418                 if (session.getAttribute(sessionParameters[i])!=null && ((String)session.getAttribute(sessionParameters[i])).length() > 0) {
3419                  //debugLogger.debug(" Session " + " sessionParameters[i] " + sessionParameters[i] + " " + (String)session.getAttribute(sessionParameters[i]));
3420                  sql = Utils.replaceInString(sql, "[" + sessionParameters[i].toUpperCase()+"]", (String)session.getAttribute(sessionParameters[i]) );
3421                 }
3422                // }
3423             }
3424                 sql = Utils.replaceInString(sql, "[USERID]", user_id);
3425                 sql = Utils.replaceInString(sql, "[USER_ID]", user_id);
3426                 sql = Utils.replaceInString(sql, "[LOGGED_USERID]", user_id);
3427
3428         }
3429         return sql;
3430         }
3431
3432 } // ReportRuntime