gerrit.onap Code Review - portal/sdk.git/blob

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