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