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