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.base;
  21
  22 import java.io.StringWriter;
  23 import java.util.ArrayList;
  24 import java.util.Calendar;
  25 import java.util.Collections;
  26 import java.util.GregorianCalendar;
  27 import java.util.HashMap;
  28 import java.util.Iterator;
  29 import java.util.List;
  30 import java.util.Map;
  31 import java.util.Map.Entry;
  32 import java.util.Set;
  33 import java.util.StringTokenizer;
  34 import java.util.TreeSet;
  35 import java.util.Vector;
  36 import java.util.regex.Matcher;
  37 import java.util.regex.Pattern;
  38
  39 import javax.servlet.http.HttpServletRequest;
  40 import javax.xml.bind.JAXBContext;
  41 import javax.xml.bind.JAXBException;
  42 import javax.xml.bind.Marshaller;
  43 import javax.xml.bind.Unmarshaller;
  44 import javax.xml.datatype.DatatypeConfigurationException;
  45 import javax.xml.datatype.DatatypeFactory;
  46 import javax.xml.transform.stream.StreamResult;
  47
  48 import org.openecomp.portalsdk.analytics.error.RaptorException;
  49 import org.openecomp.portalsdk.analytics.error.RaptorRuntimeException;
  50 import org.openecomp.portalsdk.analytics.error.UserDefinedException;
  51 import org.openecomp.portalsdk.analytics.model.DataCache;
  52 import org.openecomp.portalsdk.analytics.model.ReportLoader;
  53 import org.openecomp.portalsdk.analytics.model.definition.TableSource;
  54 import org.openecomp.portalsdk.analytics.model.runtime.FormField;
  55 import org.openecomp.portalsdk.analytics.model.runtime.ReportParamValues;
  56 import org.openecomp.portalsdk.analytics.model.runtime.ReportRuntime;
  57 import org.openecomp.portalsdk.analytics.system.AppUtils;
  58 import org.openecomp.portalsdk.analytics.system.ConnectionUtils;
  59 import org.openecomp.portalsdk.analytics.system.DbUtils;
  60 import org.openecomp.portalsdk.analytics.system.Globals;
  61 import org.openecomp.portalsdk.analytics.util.AppConstants;
  62 import org.openecomp.portalsdk.analytics.util.DataSet;
  63 import org.openecomp.portalsdk.analytics.util.SQLCorrector;
  64 import org.openecomp.portalsdk.analytics.util.Utils;
  65 import org.openecomp.portalsdk.analytics.xmlobj.ChartAdditionalOptions;
  66 import org.openecomp.portalsdk.analytics.xmlobj.ChartDrillFormfield;
  67 import org.openecomp.portalsdk.analytics.xmlobj.ChartDrillOptions;
  68 import org.openecomp.portalsdk.analytics.xmlobj.ColFilterList;
  69 import org.openecomp.portalsdk.analytics.xmlobj.ColFilterType;
  70 import org.openecomp.portalsdk.analytics.xmlobj.CustomReportType;
  71 import org.openecomp.portalsdk.analytics.xmlobj.DashboardEditorList;
  72 import org.openecomp.portalsdk.analytics.xmlobj.DashboardReports;
  73 import org.openecomp.portalsdk.analytics.xmlobj.DashboardReportsNew;
  74 import org.openecomp.portalsdk.analytics.xmlobj.DataColumnList;
  75 import org.openecomp.portalsdk.analytics.xmlobj.DataColumnType;
  76 import org.openecomp.portalsdk.analytics.xmlobj.DataSourceList;
  77 import org.openecomp.portalsdk.analytics.xmlobj.DataSourceType;
  78 import org.openecomp.portalsdk.analytics.xmlobj.DataminingOptions;
  79 import org.openecomp.portalsdk.analytics.xmlobj.FormFieldList;
  80 import org.openecomp.portalsdk.analytics.xmlobj.FormFieldType;
  81 import org.openecomp.portalsdk.analytics.xmlobj.FormatList;
  82 import org.openecomp.portalsdk.analytics.xmlobj.FormatType;
  83 import org.openecomp.portalsdk.analytics.xmlobj.JavascriptItemType;
  84 import org.openecomp.portalsdk.analytics.xmlobj.JavascriptList;
  85 import org.openecomp.portalsdk.analytics.xmlobj.Marker;
  86 import org.openecomp.portalsdk.analytics.xmlobj.ObjectFactory;
  87 import org.openecomp.portalsdk.analytics.xmlobj.PDFAdditionalOptions;
  88 import org.openecomp.portalsdk.analytics.xmlobj.PredefinedValueList;
  89 import org.openecomp.portalsdk.analytics.xmlobj.ReportMap;
  90 import org.openecomp.portalsdk.analytics.xmlobj.Reports;
  91 import org.openecomp.portalsdk.analytics.xmlobj.SemaphoreList;
  92 import org.openecomp.portalsdk.analytics.xmlobj.SemaphoreType;
  93 import org.openecomp.portalsdk.core.logging.logic.EELFLoggerDelegate;
  94
  95 /**<HR/>
  96  * This class is part of <B><I>RAPTOR (Rapid Application Programming Tool for OLAP Reporting)</I></B><BR/>
  97  * <HR/>
  98  *
  99  * --------------------------------------------------------------------------------------------------<BR/>
 100  * <B>ReportWrapper.java</B> - This is the base class for the RAPTOR. This involves in creating,<BR/>
 101  * modifying, running RAPTOR reports.<BR/>
 102  * --------------------------------------------------------------------------------------------------<BR/>
 103  *
 104  *
 105  * <U>Change Log</U><BR/><BR/>
 106  *
 107  * 31-Aug-2009 : Version 8.5.1 (Sundar);<UL><LI> All the elements in the meta xml is copied to the target reports. </LI></UL>
 108  * 18-Aug-2009 : Version 8.5.1 (Sundar);<UL><LI> request Object is passed to prevent caching user/roles - Datamining/Hosting. </LI></UL>
 109  * 27-Jul-2009 : Version 8.4 (Sundar); <UL><LI> verifySQLBasedReportAccess method checks for Admin user instead of super user. </LI></UL>
 110  * 09-Jul-2009 : Version 8.4 (Sundar); <UL><LI> Bug due to parsing and removing formfields from "and" is bulletproofed to the right "and" to which the formfield is associated. </LI></UL>
 111  * 08-Jul-2009 : Version 8.4 (Sundar); <UL><LI> Bug due to parsing and removing formfields when there is no parameter for Daytona specific database is resolved. </LI></UL>
 112  * 29-Jun-2009 : Version 8.4 (Sundar); <UL><LI> isLastSeriesALineChart() and setLastSeriesALineChart(String value) method have been added for the Bar Chart enhancements. </LI></UL>
 113  * 23-Jun-2009 : Version 8.4 (Sundar); <UL><LI> check for cr.getChartAdditionalOptions() for null value is added.</LI></UL>
 114  * 22-Jun-2009 : Version 8.4 (Sundar); <UL><LI> Wrapper functions to call JAXB were added. These Wrapper
 115  * functions are related to the Pareto chart, Time Difference Chart, Multiple Pie Chart and generic Chart Options.</LI></UL>
 116  *
 117  */
 118
 119 public class ReportWrapper extends org.openecomp.portalsdk.analytics.RaptorObject {
 120
 121         EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportWrapper.class);
 122
 123
 124     protected CustomReportType cr = null;
 125
 126         protected Vector allColumns = null;
 127
 128         protected Vector allVisibleColumns = null;
 129
 130         protected Vector allFilters = null;
 131
 132         protected String generatedSQL = null;
 133
 134         protected String generatedChartSQL = null;
 135
 136         protected String wholeSQL = null; // For display purposes only
 137
 138
 139         protected String reportID = null;
 140
 141         protected String menuID = "";
 142
 143         protected boolean menuApproved = false;
 144
 145         protected String reportDefType = "";
 146
 147         protected ReportSecurity reportSecurity = null;
 148
 149         protected String reportSQLWithRowNum = null;
 150
 151         protected String reportSQLOnlyFirstPart = null;
 152
 153
 154         private ReportWrapper(CustomReportType cr, String reportID, ReportSecurity reportSecurity) {
 155                 super();
 156
 157                 if (reportID == null)
 158                         reportID = "-1";
 159
 160                 this.cr = cr;
 161                 this.reportID = reportID;
 162
 163                 this.reportSecurity = reportSecurity;
 164         } // ReportWrapper
 165
 166         public ReportWrapper(ReportWrapper rw) {
 167                 this(rw.getCustomReport(), // .cloneCustomReport()
 168                                 rw.getReportID(), rw.reportSecurity);
 169
 170                 this.menuID = rw.getMenuID();
 171                 this.menuApproved = rw.isMenuApproved();
 172
 173                 this.reportDefType = rw.getReportDefType();
 174         } // ReportWrapper
 175
 176         public ReportWrapper(CustomReportType cr, String reportID, String ownerID, String createID,
 177                         String createDate, String updateID, String updateDate, String menuID,
 178                         boolean menuApproved) throws RaptorException {
 179                 this(cr, reportID, null);
 180
 181                 if (ownerID == null)
 182                         // Need to load the report record from the database
 183                         if (!reportID.equals("-1"))
 184                                 try {
 185                                         /*DataSet ds = DbUtils
 186                                                         .executeQuery("SELECT NVL(cr.owner_id, cr.create_id) owner_id, cr.create_id, TO_CHAR(cr.create_date, '"
 187                                                                         + Globals.getOracleTimeFormat()
 188                                                                         + "') create_date, maint_id, TO_CHAR(cr.maint_date, '"
 189                                                                         + Globals.getOracleTimeFormat()
 190                                                                         + "') update_date, cr.menu_id, cr.menu_approved_yn FROM cr_report cr WHERE cr.rep_id="
 191                                                                         + reportID);*/
 192
 193                                         String r_sql = Globals.getReportWrapperFormat();
 194                                         r_sql = r_sql.replace("[Globals.getTimeFormat()]", Globals.getTimeFormat());
 195                                         r_sql = r_sql.replace("[reportID]", reportID);
 196
 197                                         DataSet ds = DbUtils
 198                                                         .executeQuery(r_sql);
 199
 200                                         ownerID = ds.getString(0, 0);
 201                                         createID = ds.getString(0, 1);
 202                                         createDate = ds.getString(0, 2);
 203                                         updateID = ds.getString(0, 3);
 204                                         updateDate = ds.getString(0, 4);
 205                                         menuID = nvl(ds.getString(0, 5));
 206                                         menuApproved = nvl(ds.getString(0, 6)).equals("Y");
 207                                 } catch (Exception e) {
 208                                         String eMsg = "ReportWrapper.ReportWrapper: Unable to load report record details. Exception: "
 209                                                         + e.getMessage();
 210                                         //Log.write(eMsg);
 211                                         logger.error(EELFLoggerDelegate.debugLogger, ("[EXCEPTION ENCOUNTERED IN RAPTOR] "+ eMsg));
 212                                         throw new RaptorRuntimeException(eMsg);
 213                                 }
 214
 215                 this.menuID = nvl(menuID);
 216                 this.menuApproved = menuApproved;
 217
 218                 if (!reportID.equals("-1"))
 219                         updateReportDefType();
 220
 221                 reportSecurity = new ReportSecurity(reportID, ownerID, createID, createDate, updateID,
 222                                 updateDate, cr.isPublic());
 223         } // ReportWrapper
 224
 225         public CustomReportType getCustomReport() {
 226                 return cr;
 227         }
 228
 229         public String getReportID() {
 230                 return reportID;
 231         }
 232
 233         public String getMenuID() {
 234                 return menuID;
 235         }
 236
 237         public boolean checkMenuIDSelected(String chkMenuID) {
 238                 return ("|" + menuID + "|").indexOf("|" + chkMenuID + "|") >= 0;
 239         }
 240
 241         public boolean isMenuApproved() {
 242                 return menuApproved;
 243         }
 244
 245         public String getReportDefType() {
 246                 return reportDefType;
 247         }
 248
 249         public void setMenuID(String menuID) {
 250                 this.menuID = menuID;
 251         }
 252
 253         public void setMenuApproved(boolean menuApproved) {
 254                 this.menuApproved = menuApproved;
 255         }
 256
 257         public void setReportDefType(String reportDefType) {
 258                 this.reportDefType = reportDefType;
 259         }
 260
 261         public void updateReportDefType() {
 262                 this.reportDefType = (nvl(cr.getReportSQL()).length() > 0) ? ((cr.getDataminingOptions()!=null && nvl(cr.getDataminingOptions().getClassifier()).length()>0) ?
 263                                 AppConstants.RD_SQL_BASED_DATAMIN:AppConstants.RD_SQL_BASED)
 264                                 : AppConstants.RD_VISUAL;
 265         }
 266
 267         public String getJavascriptElement() {
 268                 return cr.getJavascriptElement();
 269         }
 270
 271         public int getPageSize() {
 272                 return cr.getPageSize()==null?50:cr.getPageSize();
 273         }
 274
 275         public int getMaxRowsInExcelDownload() {
 276                 return cr.getMaxRowsInExcelDownload()==null?500:cr.getMaxRowsInExcelDownload();
 277         }
 278
 279     public boolean isDisplayFolderTree() {
 280                 return cr.isDisplayFolderTree()!=null?cr.isDisplayFolderTree().booleanValue():false;
 281     }
 282
 283         public boolean isHideFormFieldAfterRun() {
 284                 return cr.isHideFormFieldAfterRun()!=null?cr.isHideFormFieldAfterRun().booleanValue():false;
 285         }
 286
 287         public void setHideFormFieldAfterRun(boolean hideFormFieldAfterRun) {
 288                 cr.setHideFormFieldAfterRun(hideFormFieldAfterRun);
 289         }
 290
 291     public boolean isReportInNewWindow() {
 292                 return cr.isReportInNewWindow()!=null?cr.isReportInNewWindow().booleanValue():false;
 293     }
 294
 295     public String getReportType() {
 296                 return cr.getReportType();
 297         }
 298
 299         public String getReportName() {
 300                 return cr.getReportName();
 301         }
 302
 303         public String getDBInfo() {
 304                 return cr.getDbInfo();
 305         }
 306
 307         public String getDBType() {
 308                 return cr.getDbType();
 309         }
 310
 311         public boolean isDrillDownURLInPopupPresent() {
 312                 return cr.isDrillURLInPoPUpPresent()!=null?cr.isDrillURLInPoPUpPresent().booleanValue():false;
 313         }
 314
 315         public void setDrillDownURLInPopupPresent(boolean value) {
 316                 cr.setDrillURLInPoPUpPresent(value);
 317         }
 318
 319         public String getReportDescr() {
 320                 return cr.getReportDescr();
 321         }
 322
 323         public String getChartType() {
 324                 return cr.getChartType();
 325         }
 326
 327         public boolean displayChartTitle() {
 328                 return cr.isShowChartTitle();
 329         }
 330
 331         public void setShowChartTitle(boolean showTitle) {
 332                 cr.setShowChartTitle(showTitle);
 333         }
 334
 335
 336         public String getChartTypeFixed() {
 337                 return cr.getChartTypeFixed();
 338         }
 339
 340         public boolean isChartTypeFixed() {
 341                 return nvl(cr.getChartTypeFixed()).length() > 0 ? cr.getChartTypeFixed().equals("Y")
 342                                 : (!Globals.getAllowRuntimeChartSel());
 343         }
 344
 345         public String getChartLeftAxisLabel() {
 346                 return cr.getChartLeftAxisLabel();
 347         }
 348
 349         public String getChartRightAxisLabel() {
 350                 return cr.getChartRightAxisLabel();
 351         }
 352
 353         public String getChartWidth() {
 354                 return cr.getChartWidth();
 355         }
 356
 357         public int getChartWidthAsInt() {
 358                 return getIntValue(cr.getChartWidth(), Globals.getDefaultChartWidth());
 359         }
 360
 361         public String getChartHeight() {
 362                 return cr.getChartHeight()==null?"500":cr.getChartHeight();
 363         }
 364
 365         /*public boolean isChartMultiSeries() {
 366                 //String s = cr.getChartMultiSeries();
 367                 return
 368                 return (nvl(s).length()>0)? (s.equals("Y")||s.equals("y")||s.equalsIgnoreCase("true")?true:false):true;
 369         }*/
 370
 371         public boolean displayPieOrderinRunPage() {
 372                 String s = "";
 373                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getChartMultiplePieOrder():"";
 374                 if(nvl(s).indexOf("|")!= -1) {
 375                         s = s.substring(s.indexOf("|")+1);
 376                         return getFlagInBoolean(s);
 377                 } else return false;
 378         }
 379
 380         public boolean isMultiplePieOrderByRow() {
 381                 String s = "";
 382                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getChartMultiplePieOrder():"";
 383                 if(nvl(s).indexOf("|")!= -1) s = s.substring(0, s.indexOf("|"));
 384                 return (nvl(s).length()>0)? (s.equals("row")?true:false):true;
 385         }
 386
 387         public boolean isMultiplePieOrderByColumn() {
 388                 String s = "";
 389                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getChartMultiplePieOrder():"";
 390                 if(nvl(s).indexOf("|")!= -1) s = s.substring(0, s.indexOf("|"));
 391                 return (nvl(s).length()>0)&&(s.equals("column"))?true:false;
 392         }
 393
 394         public boolean displayPieLabelDisplayinRunPage() {
 395                 String s = "";
 396                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getChartMultiplePieLabelDisplay():"";
 397                 if(nvl(s).indexOf("|")!= -1) {
 398                         s = s.substring(s.indexOf("|")+1);
 399                         return getFlagInBoolean(s);
 400                 } else return false;
 401         }
 402
 403         public String getMultiplePieLabelDisplay() {
 404                 String s = "";
 405                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getChartMultiplePieLabelDisplay():"";
 406                 if(nvl(s).indexOf("|")!= -1) s = s.substring(0, s.indexOf("|"));
 407                 return s;
 408         }
 409
 410         public boolean displayChartDisplayinRunPage() {
 411                 String s = "";
 412                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getChartDisplay():"";
 413                 if(nvl(s).indexOf("|")!= -1) {
 414                         s = s.substring(s.indexOf("|")+1);
 415                         return getFlagInBoolean(s);
 416                 } else return false;
 417         }
 418
 419         public boolean isChartDisplayIn3D() {
 420                 String s = "";
 421                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getChartDisplay():"";
 422                 if(nvl(s).length()<=0) return true;
 423                 if(nvl(s).indexOf("|")!= -1) s = s.substring(0, s.indexOf("|"));
 424                 return (nvl(s).length()>0)&&(s.equals("3D"))?true:false;
 425         }
 426
 427         public boolean displayChartOrientationInRunPage() {
 428                 String s = "";
 429                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getChartOrientation():"";
 430                 if(nvl(s).indexOf("|")!= -1) {
 431                         s = s.substring(s.indexOf("|")+1);
 432                         return getFlagInBoolean(s);
 433                 } else return false;
 434
 435         }
 436
 437         public String getLinearRegression() {
 438                 String s = "";
 439                 s = nvl((cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getLinearRegression():"Y");
 440                 return s;
 441         }
 442
 443         public void setLinearRegression(String linear) {
 444                 cr.getChartAdditionalOptions().setLinearRegression(linear);
 445         }
 446
 447         public String getLinearRegressionColor() {
 448                 return (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getLinearRegressionColor():"";
 449         }
 450
 451         public String getCustomizedRegressionPoint() {
 452                 return (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getMaxRegression():"";
 453         }
 454
 455         public void setCustomizedRegressionPoint( String d) {
 456                 cr.getChartAdditionalOptions().setMaxRegression(d);
 457         }
 458
 459         public void setLinearRegressionColor(String color) {
 460                 cr.getChartAdditionalOptions().setLinearRegressionColor(color);
 461         }
 462
 463         public String getExponentialRegressionColor() {
 464                 return (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getExponentialRegressionColor():"";
 465         }
 466
 467         public void setExponentialRegressionColor(String color) {
 468                 cr.getChartAdditionalOptions().setExponentialRegressionColor(color);
 469         }
 470
 471         public void setRangeAxisUpperLimit(String d) {
 472                 if(cr.getChartAdditionalOptions()!=null)
 473                 cr.getChartAdditionalOptions().setRangeAxisUpperLimit(d);
 474         }
 475
 476         public void setRangeAxisLowerLimit(String d) {
 477                 if(cr.getChartAdditionalOptions()!=null)
 478                 cr.getChartAdditionalOptions().setRangeAxisLowerLimit(d);
 479         }
 480
 481         public String getRangeAxisUpperLimit() {
 482                 return (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getRangeAxisUpperLimit():"";
 483         }
 484
 485         public String getRangeAxisLowerLimit() {
 486                 return (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getRangeAxisLowerLimit():"";
 487         }
 488
 489         public boolean isChartAnimate() {
 490                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isAnimate()!=null?cr.getChartAdditionalOptions().isAnimate():false):false;
 491         }
 492
 493         public boolean isAnimateAnimatedChart() {
 494                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isAnimateAnimatedChart()!=null?cr.getChartAdditionalOptions().isAnimateAnimatedChart():false):true;
 495         }
 496
 497         public void setAnimateAnimatedChart(boolean animate) {
 498                 cr.getChartAdditionalOptions().setAnimateAnimatedChart(animate);
 499         }
 500
 501         public void setChartStacked(boolean stacked) {
 502                 cr.getChartAdditionalOptions().setStacked(stacked);
 503         }
 504
 505         public boolean isChartStacked() {
 506                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isStacked()!=null?cr.getChartAdditionalOptions().isStacked():true):false;
 507         }
 508
 509         public void setBarControls(boolean barControls) {
 510                 cr.getChartAdditionalOptions().setBarControls(barControls);
 511         }
 512
 513         public boolean displayBarControls() {
 514                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isBarControls()!=null?cr.getChartAdditionalOptions().isBarControls():false):false;
 515         }
 516
 517         public void setXAxisDateType(boolean dateType) {
 518                 cr.getChartAdditionalOptions().setXAxisDateType(dateType);
 519         }
 520
 521         public boolean isXAxisDateType() {
 522                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isXAxisDateType()!=null?cr.getChartAdditionalOptions().isXAxisDateType():false):false;
 523         }
 524
 525         public void setLessXaxisTickers(boolean lessTickers) {
 526                 cr.getChartAdditionalOptions().setLessXaxisTickers(lessTickers);
 527         }
 528
 529         public boolean isLessXaxisTickers() {
 530                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isLessXaxisTickers()!=null?cr.getChartAdditionalOptions().isLessXaxisTickers():false):false;
 531         }
 532
 533         public void setTimeAxis(boolean timeAxis) {
 534                 cr.getChartAdditionalOptions().setTimeAxis(timeAxis);
 535         }
 536
 537         public boolean isTimeAxis() {
 538                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isTimeAxis()!=null?cr.getChartAdditionalOptions().isTimeAxis():true):true;
 539         }
 540
 541         public void setLogScale(boolean logScale) {
 542                 cr.getChartAdditionalOptions().setLogScale(logScale);
 543         }
 544
 545         public boolean isLogScale() {
 546                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isLogScale()!=null?cr.getChartAdditionalOptions().isLogScale():false):false;
 547         }
 548
 549
 550         public void setMultiSeries(boolean multiSeries) {
 551                 cr.getChartAdditionalOptions().setMultiSeries(multiSeries);
 552                 cr.setChartMultiSeries(multiSeries?"Y":"N");
 553         }
 554
 555         public boolean isMultiSeries() {
 556                 if(AppUtils.nvl(cr.getChartMultiSeries()).equals("Y"))
 557                         cr.getChartAdditionalOptions().setMultiSeries(true);
 558                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isMultiSeries()!=null?cr.getChartAdditionalOptions().isMultiSeries():false):false;
 559         }
 560
 561         public void setTimeSeriesRender(String timeSeriesRenderer) {
 562                 cr.getChartAdditionalOptions().setTimeSeriesRender(timeSeriesRenderer);
 563         }
 564
 565         public String getTimeSeriesRender() {
 566                 return (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getTimeSeriesRender():"line";
 567         }
 568
 569         public void setShowXAxisLabel(boolean showXaxisLabel) {
 570                 cr.getChartAdditionalOptions().setShowXAxisLabel(showXaxisLabel);
 571         }
 572
 573         public boolean isShowXaxisLabel() {
 574                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isShowXAxisLabel()!=null?cr.getChartAdditionalOptions().isShowXAxisLabel():false):false;
 575         }
 576
 577         public void setAddXAxisTickers(boolean addXAxisTickers) {
 578                 cr.getChartAdditionalOptions().setAddXAxisTickers(addXAxisTickers);
 579         }
 580
 581         public boolean isAddXAxisTickers() {
 582                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().isAddXAxisTickers()!=null?cr.getChartAdditionalOptions().isAddXAxisTickers():false):true;
 583         }
 584
 585         public void setZoomIn(Integer zoomIn) {
 586                 cr.getChartAdditionalOptions().setZoomIn(zoomIn);
 587         }
 588
 589         public Integer getZoomIn() {
 590                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().getZoomIn()!=null?cr.getChartAdditionalOptions().getZoomIn():new Integer("25")): new Integer("25");
 591         }
 592
 593         public void setTimeAxisType(String timeAxisType) {
 594                 cr.getChartAdditionalOptions().setTimeAxisType(timeAxisType);
 595         }
 596
 597         public String getTimeAxisType() {
 598                 return (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().getTimeAxisType()!=null?cr.getChartAdditionalOptions().getTimeAxisType():"hourly"): "hourly";
 599         }
 600
 601         public void setTopMargin(Integer topMargin) {
 602                 cr.getChartAdditionalOptions().setTopMargin(topMargin);
 603         }
 604
 605         public Integer getTopMargin() {
 606                 return (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getTopMargin(): new Integer("30");
 607         }
 608
 609         public void setBottomMargin(Integer bottomMargin) {
 610                 cr.getChartAdditionalOptions().setBottomMargin(bottomMargin);
 611         }
 612
 613         public Integer getBottomMargin() {
 614                 return (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getBottomMargin(): new Integer("50");
 615         }
 616
 617         public void setRightMargin(Integer rightMargin) {
 618                 cr.getChartAdditionalOptions().setRightMargin(rightMargin);
 619         }
 620
 621         public Integer getRightMargin() {
 622                 return (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getRightMargin(): new Integer("60");
 623         }
 624
 625         public void setLeftMargin(Integer leftMargin) {
 626                 cr.getChartAdditionalOptions().setLeftMargin(leftMargin);
 627         }
 628
 629         public Integer getLeftMargin() {
 630                 return (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getLeftMargin(): new Integer("100");
 631         }
 632
 633
 634         public boolean isVerticalOrientation() {
 635                 String s = "";
 636                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getChartOrientation():"";
 637                 if(nvl(s).length()<=0) return true;
 638                 if(nvl(s).indexOf("|")!= -1) s = s.substring(0, s.indexOf("|"));
 639                 return (nvl(s).length()>0)&&(s.equals("vertical"))?true:false;
 640         }
 641
 642         public boolean isHorizontalOrientation() {
 643                 String s = "";
 644                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getChartOrientation():"";
 645                 if(nvl(s).indexOf("|")!= -1) s = s.substring(0, s.indexOf("|"));
 646                 return (nvl(s).length()>0)&&(s.equals("horizontal"))?true:false;
 647         }
 648
 649         public boolean displaySecondaryChartRendererInRunPage() {
 650                 String s = "";
 651                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getSecondaryChartRenderer():"";
 652                 if(nvl(s).indexOf("|")!= -1) {
 653                         s = s.substring(s.indexOf("|")+1);
 654                         return getFlagInBoolean(s);
 655                 } else return false;
 656
 657         }
 658
 659         public String getSecondaryChartRenderer() {
 660                 String s = "";
 661                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getSecondaryChartRenderer():"";
 662                 if(nvl(s).indexOf("|")!= -1) s = s.substring(0, s.indexOf("|"));
 663                 return s;
 664         }
 665
 666         public String getOverlayItemValueOnStackBar() {
 667                 String s = "";
 668                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getOverlayItemValueOnStackBar():"N";
 669                 return s;
 670         }
 671
 672         public boolean displayIntervalInputInRunPage() {
 673                 String s = "";
 674                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getIntervalFromdate():"";
 675                 if(nvl(s).indexOf("|")!= -1) {
 676                         s = s.substring(s.indexOf("|")+1);
 677                    return getFlagInBoolean(s);
 678                 } else return false;
 679         }
 680
 681         public boolean showLegendDisplayOptionsInRunPage() {
 682                 String s = "";
 683                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getHidechartLegend():"";
 684                 if(nvl(s).indexOf("|")!= -1) {
 685                         s = s.substring(s.indexOf("|")+1);
 686                         return getFlagInBoolean(s);
 687                 } else return false;
 688         }
 689
 690         public String getIntervalFromdate() {
 691                 String s = "";
 692                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getIntervalFromdate():"";
 693                 if(nvl(s).indexOf("|")!= -1) s = s.substring(0, s.indexOf("|"));
 694                 return nvl(s,"");
 695         }
 696
 697         public String getIntervalTodate() {
 698                 String s = "";
 699                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getIntervalTodate():"";
 700                 if(nvl(s).indexOf("|")!= -1) s = s.substring(0, s.indexOf("|"));
 701                 return nvl(s,"");
 702         }
 703
 704         public String getIntervalLabel() {
 705                 return cr.getChartAdditionalOptions()!=null ? nvl(cr.getChartAdditionalOptions().getIntervalLabel()):"";
 706         }
 707
 708         public String getLegendPosition() {
 709                 String s = "";
 710                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getLegendPosition():"";
 711                 return nvl(s,"bottom");
 712         }
 713
 714         public String getLegendLabelAngle() {
 715                 String s = "";
 716                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getLabelAngle():"";
 717                 return nvl(s,"UP90");
 718         }
 719
 720         public String getMaxLabelsInDomainAxis() {
 721                 String s = "";
 722                 s = (cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getMaxLabelsInDomainAxis():"";
 723                 return nvl(s,"99");
 724         }
 725
 726         public boolean isLastSeriesALineChart() {
 727                 String s = "";
 728                 s = nvl((cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getLastSeriesALineChart():"");
 729                 return s.equals("Y");
 730         }
 731
 732         public boolean isLastSeriesABarChart() {
 733                 String s = "";
 734                 s = nvl((cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getLastSeriesABarChart():"");
 735                 return s.equals("Y");
 736         }
 737
 738         public void setChartLegendDisplay(String value) {
 739         cr.getChartAdditionalOptions().setHidechartLegend(value);
 740         }
 741
 742         public boolean hideChartLegend() {
 743                 String s = "";
 744                 s = nvl((cr.getChartAdditionalOptions()!=null)?cr.getChartAdditionalOptions().getHidechartLegend():"N");
 745                 if(nvl(s).length()<=0) s = "N";
 746                 if(nvl(s).indexOf("|")!= -1) s = s.substring(0, s.indexOf("|"));
 747                 return s.equals("Y");
 748         }
 749
 750         public void setChartToolTips(String value) {
 751         cr.getChartAdditionalOptions().setHideToolTips(value);
 752         }
 753
 754         public void setDomainAxisValuesAsString(String value) {
 755         cr.getChartAdditionalOptions().setKeepDomainAxisValueAsString(value);
 756         }
 757
 758         public boolean hideChartToolTips() {
 759                 boolean s = true;
 760                 s = (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().getHideToolTips()!=null?
 761                                 (cr.getChartAdditionalOptions().getHideToolTips().equals("Y")?true:false):(Globals.hideToolTipsGlobally()?true:false)):(Globals.hideToolTipsGlobally()?true:false);
 762                 return s;
 763         }
 764
 765         public boolean keepDomainAxisValueInChartAsString() {
 766                 boolean s = true;
 767                 s = (cr.getChartAdditionalOptions()!=null)?(cr.getChartAdditionalOptions().getKeepDomainAxisValueAsString()!=null?
 768                                 (cr.getChartAdditionalOptions().getKeepDomainAxisValueAsString().equals("Y")?true:false):false):false;
 769                 return s;
 770         }
 771
 772         public int getChartHeightAsInt() {
 773                 return getIntValue(cr.getChartHeight(), Globals.getDefaultChartHeight());
 774         }
 775
 776         public boolean isPublic() {
 777                 return cr.isPublic();
 778         }
 779
 780         public boolean isDashboardType() throws RaptorException {
 781                 return cr.isDashboardType()!=null?cr.isDashboardType().booleanValue():false;
 782         }
 783
 784         // public String getCreateId() { return cr.getCreateId(); }
 785         // public Calendar getCreateDate() { return cr.getCreateDate(); }
 786         public String getReportSQL() {
 787                 return cr.getReportSQL();
 788         }
 789
 790         public String getReportTitle() {
 791                 return cr.getReportTitle();
 792         }
 793
 794         public String getReportSubTitle() {
 795                 return cr.getReportSubTitle();
 796         }
 797
 798         public String getReportHeader() {
 799                 return cr.getReportHeader();
 800         }
 801
 802         public String getReportFooter() {
 803                 return cr.getReportFooter();
 804         }
 805
 806         public String getNumDashCols() {
 807                 return cr.getNumDashCols();
 808         }
 809
 810         public int getNumDashColsAsInt() {
 811                 return getIntValue(cr.getNumDashCols(), 1);
 812         }
 813
 814         public String getNumFormCols() {
 815                 return cr.getNumFormCols();
 816         }
 817
 818         public int getNumFormColsAsInt() {
 819                 return getIntValue(cr.getNumFormCols(), 5);
 820         }
 821
 822         public String getDisplayOptions() {
 823                 return cr.getDisplayOptions();
 824         }
 825
 826
 827
 828 //Additional Methods
 829
 830         public int getJumpTo() {
 831                 return cr.getJumpTo()==null?1:cr.getJumpTo();
 832         }
 833         public void setJumpTo(int value){
 834                 cr.setJumpTo(value);
 835         }
 836
 837
 838         public int getSearchPageSize(){
 839                 return cr.getSearchPageSize()==null?20:cr.getSearchPageSize();
 840         }
 841         public void setSearchPageSize(int value){
 842                 cr.setSearchPageSize(value);
 843         }
 844
 845
 846         public boolean isToggleLayout(){
 847                 if(cr.isToggleLayout()!=null)
 848                         return cr.isToggleLayout();
 849
 850                 else
 851                         return Globals.displayRuntimeOptionsAsDefault();
 852
 853         }
 854         public void setToggleLayout(boolean value){
 855                 cr.setToggleLayout(value);
 856         }
 857
 858         public boolean isShowPageSize(){
 859                 if(cr.isShowPageSize()!=null)
 860                         return cr.isShowPageSize();
 861
 862                 else
 863                         return Globals.displayRuntimeOptionsAsDefault();
 864
 865         }
 866         public void setShowPageSize(boolean value){
 867                 cr.setShowPageSize(value);
 868         }
 869
 870         public boolean isShowNavPos(){
 871                 if(cr.isShowNavPos()!=null)
 872                         return cr.isShowNavPos();
 873
 874                 else
 875                         return Globals.displayRuntimeOptionsAsDefault();
 876
 877         }
 878         public void setShowNavPos(boolean value){
 879                 cr.setShowNavPos(value);
 880         }
 881
 882         public boolean isShowGotoOption(){
 883                 if(cr.isShowGotoOption()!=null)
 884                         return cr.isShowGotoOption();
 885
 886                 else
 887                         return Globals.displayRuntimeOptionsAsDefault();
 888
 889         }
 890         public void setShowGotoOption(boolean value){
 891                 cr.setShowGotoOption(value);
 892         }
 893
 894         public boolean isPageNav(){
 895
 896                 if(cr.isPageNav()!=null)
 897                         return cr.isPageNav();
 898
 899                 else
 900                         return Globals.displayRuntimeOptionsAsDefault();
 901
 902         }
 903
 904         public void setPageNav(boolean value){
 905                 cr.setPageNav(value);
 906         }
 907
 908
 909         public String getNavPosition(){
 910                 if(cr.getNavPosition()!=null)
 911                         return cr.getNavPosition();
 912
 913                 else
 914                         return "top";
 915                 //return cr.getNavPosition();
 916         }
 917         public void setNavPosition(String value){
 918                 cr.setNavPosition(value);
 919         }
 920
 921
 922         public String getDashboardEditor(){
 923                 return getDashBoardReportsNew().getDashboardEditor();
 924         }
 925
 926         public void setDashboardEditor(String value){
 927                 getDashBoardReportsNew().setDashboardEditor(value);
 928         }
 929
 930
 931         public DashboardEditorList getDashboardEditorList(){
 932                 return getDashBoardReportsNew().getDashboardEditorList();
 933         }
 934
 935         public void setDashboardEditorList(DashboardEditorList value){
 936                 getDashBoardReportsNew().setDashboardEditorList(value);
 937         }
 938
 939         public  PDFAdditionalOptions getPDFAdditionalOptions() {
 940                 try {
 941                         if(cr.getPdfAdditionalOptions()==null)
 942                     addPDFAdditionalOptions(new ObjectFactory());
 943                         } catch(RaptorException ex) {
 944                                 ex.printStackTrace();
 945                         }
 946                 return cr.getPdfAdditionalOptions();
 947         }
 948
 949         public String getPDFFont(){
 950                 return getPDFAdditionalOptions().getPDFFont()!=null?getPDFAdditionalOptions().getPDFFont():Globals.getDataFontFamily();
 951         }
 952         public void setPDFFont(String value){
 953                 getPDFAdditionalOptions().setPDFFont(value);
 954         }
 955
 956         public int getPDFFontSize() {
 957                 return getPDFAdditionalOptions().getPDFFontSize()==null?9:getPDFAdditionalOptions().getPDFFontSize();
 958         }
 959         public void setPDFFontSize(int value){
 960                 getPDFAdditionalOptions().setPDFFontSize(value);
 961         }
 962
 963         public String getPDFOrientation(){
 964                 return getPDFAdditionalOptions().getPDFOrientation()!=null?"portrait":"landscape";
 965         }
 966         public void setPDFOrientation(String value){
 967                 getPDFAdditionalOptions().setPDFOrientation(value);
 968         }
 969
 970         public String getPDFLogo1(){
 971                 return getPDFAdditionalOptions().getPDFLogo1();
 972         }
 973         public void setPDFLogo1(String value){
 974                 getPDFAdditionalOptions().setPDFLogo1(value);
 975         }
 976
 977         public String getPDFLogo2(){
 978                 return getPDFAdditionalOptions().getPDFLogo2();
 979         }
 980         public void setPDFLogo2(String value){
 981                 getPDFAdditionalOptions().setPDFLogo2(value);
 982         }
 983
 984         public int getPDFLogo1Size() {
 985                 return getPDFAdditionalOptions().getPDFLogo1Size()==null?0:getPDFAdditionalOptions().getPDFLogo1Size();
 986         }
 987         public void setPDFLogo1Size(int value){
 988                 getPDFAdditionalOptions().setPDFLogo1Size(value);
 989         }
 990
 991         public int getPDFLogo2Size() {
 992                 return getPDFAdditionalOptions().getPDFLogo2Size()==null?0:getPDFAdditionalOptions().getPDFLogo2Size();
 993         }
 994         public void setPDFLogo2Size(int value){
 995                 getPDFAdditionalOptions().setPDFLogo2Size(value);
 996         }
 997
 998         public boolean isPDFCoverPage(){
 999
1000                 if(getPDFAdditionalOptions().isPDFCoverPage()!=null)
1001                         return getPDFAdditionalOptions().isPDFCoverPage();
1002
1003                 else
1004                         return true;
1005
1006         }
1007
1008         public void setPDFCoverPage(boolean value){
1009                 getPDFAdditionalOptions().setPDFCoverPage(value);
1010         }
1011
1012         public String getPDFFooter1(){
1013                 return getPDFAdditionalOptions().getPDFFooter1();
1014         }
1015         public void setPDFFooter1(String value){
1016                 getPDFAdditionalOptions().setPDFFooter1(value);
1017         }
1018
1019         public String getPDFFooter2(){
1020                 return getPDFAdditionalOptions().getPDFFooter2();
1021         }
1022         public void setPDFFooter2(String value){
1023                 getPDFAdditionalOptions().setPDFFooter2(value);
1024         }
1025
1026
1027
1028 //End of Additional Methods
1029
1030         public String getDataContainerHeight() {
1031                 return cr.getDataContainerHeight();
1032         }
1033
1034         public String getDataContainerWidth() {
1035                 return cr.getDataContainerWidth();
1036         }
1037
1038         public boolean isAllowSchedule() {
1039                 String allowSchedule = getAllowSchedule();
1040                 return (allowSchedule !=null )? allowSchedule.startsWith("Y"):false;
1041         }
1042
1043         public String getAllowSchedule() {
1044                 return cr.getAllowSchedule();
1045         }
1046
1047         /* Multi Group */
1048
1049         public boolean isMultiGroupColumn() {
1050                 String multiGroupColumn = getMultiGroupColumn();
1051                 return (multiGroupColumn !=null )? multiGroupColumn.startsWith("Y"):false;
1052         }
1053
1054         public String getMultiGroupColumn() {
1055                 return cr.getMultiGroupColumn();
1056         }
1057
1058         public void setMultiGroupColumn(String value) {
1059                 cr.setMultiGroupColumn(value);
1060         }
1061
1062         private int getColumnGroupLevel(String colId) throws RaptorException  {
1063                 DataColumnType dc = getColumnById(colId);
1064                 return (dc == null) ? 0 : dc.getLevel();
1065         } // getColumnGroupLevel
1066
1067         public int getMaxGroupLevel()  {
1068                 List reportCols = getAllColumns();
1069                 int maxLevel = 0;
1070                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1071                         DataColumnType dc = (DataColumnType) iter.next();
1072                         if (dc.getLevel()!=null) {
1073                                 if(maxLevel < dc.getLevel())
1074                                         maxLevel = dc.getLevel();
1075                         }
1076                 } // for
1077                 return maxLevel;
1078         } // getMaxGroupLevel
1079
1080         private int getColumnGroupStart(String colId) throws RaptorException  {
1081                 DataColumnType dc = getColumnById(colId);
1082                 return (dc == null) ? 0 : dc.getStart();
1083         } // getColumnGroupStart
1084
1085         private int getColumnGroupColSpan(String colId) throws RaptorException  {
1086                 DataColumnType dc = getColumnById(colId);
1087                 return (dc == null) ? 0 : dc.getColspan();
1088         } // getColumnGroupColSpan
1089
1090         public void setTopDown(String value) {
1091                 cr.setTopDown(value);
1092         }
1093
1094         public boolean isTopDown() {
1095                 String topDown = getTopDownOption();
1096                 return (topDown !=null )? topDown.startsWith("Y"):false;
1097         }
1098
1099         public String getTopDownOption() {
1100                 return cr.getTopDown();
1101         }
1102
1103         public void setSizedByContent(String value) {
1104                 cr.setSizedByContent(value);
1105         }
1106
1107         public boolean isSizedByContent() {
1108                 String sizedByContent = getSizedByContentOption();
1109                 return (sizedByContent !=null )? sizedByContent.startsWith("Y"):false;
1110         }
1111
1112         public String getSizedByContentOption() {
1113                 return cr.getSizedByContent();
1114         }
1115
1116         public String getDashboardOptions() {
1117                 return cr.getDashboardOptions();
1118         }
1119
1120         public boolean isDashboardOptionHideChart() {
1121                 return nvl(getDashboardOptions()).length() > 0 && (getDashboardOptions().charAt(0) == 'Y');
1122         }
1123
1124         public boolean isDashboardOptionHideData() {
1125                 return nvl(getDashboardOptions()).length() > 0 && (getDashboardOptions().charAt(1) == 'Y');
1126         }
1127
1128         public boolean isDashboardOptionHideBtns() {
1129                 return nvl(getDashboardOptions()).length() > 0 && (getDashboardOptions().charAt(2) == 'Y');
1130         }
1131
1132         public boolean isDisplayOptionHideForm() {
1133                 return nvl(getDisplayOptions()).length() > 0 && (getDisplayOptions().charAt(0) == 'Y');
1134         }
1135
1136         public boolean isDisplayOptionHideChart() {
1137                 return nvl(getDisplayOptions()).length() > 1 && (getDisplayOptions().charAt(1) == 'Y');
1138         }
1139
1140         public boolean isDisplayOptionHideData() {
1141                 return nvl(getDisplayOptions()).length() > 2 && (getDisplayOptions().charAt(2) == 'Y');
1142         }
1143
1144         public boolean isDisplayOptionHideBtns() {
1145                 return nvl(getDisplayOptions()).length() > 3 && (getDisplayOptions().charAt(3) == 'Y');
1146         }
1147
1148         public boolean isDisplayOptionHideMap() {
1149                 return nvl(getDisplayOptions()).length() > 4 && (getDisplayOptions().charAt(4) == 'Y');
1150         }
1151
1152         public boolean isDisplayOptionHideExcelIcons() {
1153                 return nvl(getDisplayOptions()).length() > 5 && (getDisplayOptions().charAt(5) == 'Y');
1154         }
1155
1156         public boolean isDisplayOptionHidePDFIcons() {
1157                 return nvl(getDisplayOptions()).length() > 6 && (getDisplayOptions().charAt(6) == 'Y');
1158         }
1159
1160         public String getComment() {
1161                 return cr.getComment();
1162         }
1163
1164         public DataSourceList getDataSourceList() {
1165                 return cr.getDataSourceList();
1166         }
1167
1168         public ChartAdditionalOptions getChartAdditionalOptions() {
1169                 return cr.getChartAdditionalOptions();
1170         }
1171
1172         public ChartDrillOptions getChartDrillOptions() {
1173                 return cr.getChartDrillOptions();
1174         }
1175
1176
1177         public DataminingOptions getDataminingOptions() {
1178                 return cr.getDataminingOptions();
1179         }
1180
1181         public  DashboardReports getDashBoardReports() {
1182                 return cr.getDashBoardReports();
1183         }
1184
1185
1186         public  DashboardReportsNew getDashBoardReportsNew() {
1187                 try {
1188                         if(cr.getDashBoardReportsNew()==null)
1189                     addDashboardReportsNew(new ObjectFactory());
1190                         } catch(RaptorException ex) {
1191                                 ex.printStackTrace();
1192                         }
1193                 return cr.getDashBoardReportsNew();
1194         }
1195
1196         public String getDashboardLayoutHTML() {
1197                 return cr.getDashboardLayoutHTML();
1198         }
1199
1200         public FormFieldList getFormFieldList() {
1201                 return cr.getFormFieldList();
1202         }
1203
1204         public JavascriptList getJavascriptList() {
1205                 return cr.getJavascriptList();
1206         }
1207
1208         public SemaphoreList getSemaphoreList() {
1209                 return cr.getSemaphoreList();
1210         }
1211
1212         public void setPageSize(int value) {
1213                 cr.setPageSize(value);
1214         }
1215
1216         public void setAllowSchedule(String value) {
1217                 cr.setAllowSchedule(value);
1218         }
1219
1220         public void setMaxRowsInExcelDownload(int value) {
1221                 cr.setMaxRowsInExcelDownload(value);
1222         }
1223
1224         public void setReportInNewWindow (boolean value) {
1225                 cr.setReportInNewWindow(value);
1226         }
1227
1228         public void setDisplayFolderTree (boolean value) {
1229                 cr.setDisplayFolderTree(value);
1230         }
1231
1232         public void setReportType(String value) {
1233                 cr.setReportType(value);
1234         }
1235
1236         public void setReportName(String value) {
1237                 cr.setReportName(value);
1238         }
1239
1240         public void setDBInfo(String value) {
1241                 if (!(cr.getDbInfo() != null && cr.getDbInfo().length() > 0))
1242                         cr.setDbInfo(value);
1243         }
1244
1245         public void setDBType(String value) {
1246                 if (!(cr.getDbType() != null && cr.getDbType().length() > 0))
1247                         cr.setDbType(value);
1248         }
1249
1250         public void setReportDescr(String value) {
1251                 cr.setReportDescr(value);
1252         }
1253
1254         public void setChartType(String value) {
1255                 cr.setChartType(value);
1256         }
1257
1258         public void setChartMultiplePieOrder(String value) {
1259                 cr.getChartAdditionalOptions().setChartMultiplePieOrder(value);
1260         }
1261
1262         public void setChartMultiplePieLabelDisplay(String value) {
1263                 cr.getChartAdditionalOptions().setChartMultiplePieLabelDisplay(value);
1264         }
1265
1266         public void setChartOrientation(String value) {
1267                 cr.getChartAdditionalOptions().setChartOrientation(value);
1268         }
1269
1270         public void setSecondaryChartRenderer(String value) {
1271                 cr.getChartAdditionalOptions().setSecondaryChartRenderer(value);
1272         }
1273
1274         public void setOverlayItemValueOnStackBar(String value) {
1275                 cr.getChartAdditionalOptions().setOverlayItemValueOnStackBar(value);
1276         }
1277
1278         public void setIntervalFromdate(String value) {
1279                 cr.getChartAdditionalOptions().setIntervalFromdate(value);
1280         }
1281
1282         public void setIntervalLabel(String value) {
1283                 cr.getChartAdditionalOptions().setIntervalLabel(value);
1284         }
1285
1286         public void setIntervalTodate(String value) {
1287                 cr.getChartAdditionalOptions().setIntervalTodate(value);
1288         }
1289
1290         public void setLegendPosition(String value) {
1291                 cr.getChartAdditionalOptions().setLegendPosition(value);
1292         }
1293
1294         public void setLegendLabelAngle(String value) {
1295                 cr.getChartAdditionalOptions().setLabelAngle(value);
1296         }
1297
1298         public void setMaxLabelsInDomainAxis(String value) {
1299                 if(nvl(value).length()<=0) value = "99";
1300                 cr.getChartAdditionalOptions().setMaxLabelsInDomainAxis(value);
1301         }
1302
1303         public void setLastSeriesALineChart(String value) {
1304                 cr.getChartAdditionalOptions().setLastSeriesALineChart(value);
1305         }
1306
1307         public void setLastSeriesABarChart(String value) {
1308                 cr.getChartAdditionalOptions().setLastSeriesABarChart(value);
1309         }
1310
1311         public void setChartDisplay(String value) {
1312                 cr.getChartAdditionalOptions().setChartDisplay(value);
1313         }
1314
1315         public void setChartAnimate(boolean animate) {
1316                 if(cr.getChartAdditionalOptions()!=null)
1317                 cr.getChartAdditionalOptions().setAnimate(animate);
1318                 else {
1319                         try {
1320                         if(getChartAdditionalOptions()==null)
1321                     addChartAdditionalOptions(new ObjectFactory());
1322                         } catch(RaptorException ex) {
1323                                 ex.printStackTrace();
1324                         }
1325                         if(cr.getChartAdditionalOptions()!=null)
1326                                 cr.getChartAdditionalOptions().setAnimate(animate);
1327
1328                 }
1329
1330         }
1331
1332         public void addChartAdditionalOptions(ObjectFactory objFactory) throws RaptorException {
1333                 ChartAdditionalOptions chartOptions = objFactory.createChartAdditionalOptions();
1334                 cr.setChartAdditionalOptions(chartOptions);
1335     }
1336
1337         public void addDashboardReportsNew(ObjectFactory objFactory) throws RaptorException {
1338                 DashboardReportsNew dashboardReports = objFactory.createDashboardReportsNew();
1339                 cr.setDashBoardReportsNew(dashboardReports);
1340     }
1341
1342         public void addPDFAdditionalOptions(ObjectFactory objFactory) throws RaptorException {
1343                 PDFAdditionalOptions pdfOptions = objFactory.createPDFAdditionalOptions();
1344                 cr.setPdfAdditionalOptions(pdfOptions);
1345     }
1346
1347         public void setChartTypeFixed(String value) {
1348                 cr.setChartTypeFixed(value);
1349         }
1350
1351         public void setChartLeftAxisLabel(String value) {
1352                 cr.setChartLeftAxisLabel(value);
1353         }
1354
1355         public void setChartRightAxisLabel(String value) {
1356                 cr.setChartRightAxisLabel(value);
1357         }
1358
1359         public void setChartWidth(String value) {
1360                 cr.setChartWidth(value);
1361         }
1362
1363         public void setChartHeight(String value) {
1364                 cr.setChartHeight(value);
1365         }
1366
1367         public void setChartMultiSeries(String value) {
1368                 cr.setChartMultiSeries(value);
1369         }
1370
1371         public void setPublic(boolean value) {
1372                 cr.setPublic(value);
1373                 if (reportSecurity != null)
1374                         reportSecurity.setPublic(value);
1375         }
1376
1377         // public void setCreateId(String value) { cr.setCreateId(value); }
1378         // public void setCreateDate(Calendar value) { cr.setCreateDate(value); }
1379         public void setReportSQL(String value) {
1380                 cr.setReportSQL(value);
1381         }
1382
1383         public void setReportTitle(String value) {
1384                 cr.setReportTitle(value);
1385         }
1386
1387         public void setReportSubTitle(String value) {
1388                 cr.setReportSubTitle(value);
1389         }
1390
1391         public void setReportHeader(String value) {
1392                 cr.setReportHeader(value);
1393         }
1394
1395         public void setReportFooter(String value) {
1396                 cr.setReportFooter(value);
1397         }
1398
1399         public void setNumFormCols(String value) {
1400                 cr.setNumFormCols(value);
1401         }
1402
1403         public void setNumDashCols(String value) {
1404                 cr.setNumDashCols(value);
1405         }
1406
1407         public void setDisplayOptions(String value) {
1408                 cr.setDisplayOptions(value);
1409         }
1410
1411         public void setDataContainerHeight(String value) {
1412                 cr.setDataContainerHeight(value);
1413         }
1414
1415         public void setDataContainerWidth(String value) {
1416                 cr.setDataContainerWidth(value);
1417         }
1418
1419         public void setDashboardOptions(String value) {
1420                 cr.setDashboardOptions(value);
1421         }
1422
1423         public void setComment(String value) {
1424                 cr.setComment(value);
1425         }
1426
1427         public void setDashboardType(boolean dashboardType) {
1428                 cr.setDashboardType(dashboardType);
1429         }
1430
1431         public void setDashboardLayoutHTML(String html) {
1432                 cr.setDashboardLayoutHTML(html);
1433         }
1434
1435         public void setDataSourceList(DataSourceList value) {
1436                 cr.setDataSourceList(value);
1437         }
1438
1439         public void setFormFieldList(FormFieldList value) {
1440                 cr.setFormFieldList(value);
1441         }
1442
1443         public  void setDashBoardReports(DashboardReports value) {
1444                 cr.setDashBoardReports(value);
1445         }
1446
1447         public void setSemaphoreList(SemaphoreList value) {
1448                 cr.setSemaphoreList(value);
1449         }
1450
1451         public void setJavascriptList(JavascriptList value) {
1452                 cr.setJavascriptList(value);
1453         }
1454
1455         public void setJavascriptElement(String javascriptElement) {
1456                 cr.setJavascriptElement(javascriptElement);
1457         }
1458
1459         public void checkUserReadAccess(HttpServletRequest request) throws RaptorException  {
1460                 reportSecurity.checkUserReadAccess(request, null);
1461         }
1462         public void checkUserReadAccess(HttpServletRequest request, String userID) throws RaptorException  {
1463                 reportSecurity.checkUserReadAccess(request, userID);
1464         }
1465
1466         public void checkUserWriteAccess(HttpServletRequest request) throws RaptorException  {
1467                 reportSecurity.checkUserWriteAccess(request);
1468                 verifySQLBasedReportAccess(request);
1469         }
1470
1471         public String getOwnerID() {
1472                 return reportSecurity.getOwnerID();
1473         }
1474
1475         public String getCreateID() {
1476                 return reportSecurity.getCreateID();
1477         }
1478
1479         public String getCreateDate() {
1480                 return reportSecurity.getCreateDate();
1481         }
1482
1483         public String getUpdateID() {
1484                 return reportSecurity.getUpdateID();
1485         }
1486
1487         public String getUpdateDate() {
1488                 return reportSecurity.getUpdateDate();
1489         }
1490
1491         public ReportSecurity getReportSecurity() {
1492                 return reportSecurity;
1493         }
1494
1495         /****Report Maps - Start****/
1496         public ReportMap getReportMap() {
1497                 return cr.getReportMap();
1498         }
1499
1500         public void setReportMap(ReportMap reportMap) {
1501                 cr.setReportMap(reportMap);
1502         }
1503         /****Report Maps - End****/
1504
1505         /****Report Chart Drilldown - Start****/
1506         public ChartDrillOptions getReportChartDrillOptions() {
1507                 return cr.getChartDrillOptions();
1508         }
1509
1510         public void setReportChartDrillOptions(ChartDrillOptions chartDrillOptions) {
1511                 cr.setChartDrillOptions(chartDrillOptions);
1512         }
1513         /****Report Maps - End****/
1514
1515
1516         /** ************************************************************************************************* */
1517
1518         public String getFormHelpText() {
1519                 String formHelpText = nvl(getComment());
1520
1521                 if (formHelpText.indexOf('|') >= 0)
1522                         formHelpText = formHelpText.substring(formHelpText.lastIndexOf('|') + 1);
1523
1524                 return formHelpText;
1525         } // getFormHelpText
1526
1527         public void setFormHelpText(String formHelpText) {
1528                 String comment = nvl(getComment());
1529
1530                 if (comment.indexOf('|') >= 0)
1531                         comment = comment.substring(0, comment.lastIndexOf('|'));
1532                 if (comment.length() > 0)
1533                         comment += '|';
1534
1535                 setComment(comment + formHelpText);
1536         } // setFormHelpText
1537
1538         public boolean isRuntimeColSortDisabled() {
1539                 String comment = nvl(getComment());
1540
1541                 if (comment.indexOf('|') < 0)
1542                         return false;
1543
1544                 return comment.substring(0, comment.indexOf('|')).equals("Y");
1545         } // isRuntimeColSortDisabled
1546
1547         public void setRuntimeColSortDisabled(boolean value) {
1548                 String comment = nvl(getComment());
1549
1550                 if (comment.indexOf('|') >= 0)
1551                         comment = comment.substring(comment.indexOf('|') + 1);
1552
1553                 setComment((value ? "Y" : "N") + "|" + comment);
1554         } // setRuntimeColSortDisabled
1555
1556         /** ************************************************************************************************* */
1557
1558         protected void verifySQLBasedReportAccess(HttpServletRequest request)  throws RaptorException  {
1559                 String userID = AppUtils.getUserID(request);
1560                 if (getReportDefType().equals(AppConstants.RD_SQL_BASED)
1561                                 && (!Globals.getAllowSQLBasedReports()) && (!AppUtils.isAdminUser(request)))
1562                         throw new org.openecomp.portalsdk.analytics.error.UserAccessException(reportID, "[" + userID + "] "
1563                                         + AppUtils.getUserName(request), AppConstants.UA_WRITE);
1564         } // verifySQLBasedReportAccess
1565
1566         /** ************************************************************************************************* */
1567
1568         private String getColumnNameById(String colId) throws RaptorException  {
1569                 DataColumnType dc = getColumnById(colId);
1570                 return (dc == null) ? "NULL" : dc.getColName();
1571         } // getColumnNameById
1572
1573         // Checks if drill-down URL points to individual record display (return
1574         // true) or another report (return false)
1575         private boolean isViewAction(String value) throws RaptorException {
1576                 try {
1577                         Vector viewActions = org.openecomp.portalsdk.analytics.model.DataCache.getDataViewActions();
1578
1579                         for (int i = 0; i < viewActions.size(); i++)
1580                                 if (value.equals(AppUtils.getBaseActionURL() + ((String) viewActions.get(i))))
1581                                         return true;
1582                 } catch (Exception e) {
1583                         throw new RaptorRuntimeException("ReportWrapper.isViewAction Exception: "
1584                                         + e.getMessage());
1585                 }
1586
1587                 return false;
1588         } // isViewAction
1589
1590         public String getSelectExpr(DataColumnType dct) {
1591                 // String colName =
1592                 // dct.isCalculated()?dct.getColName():((nvl(dct.getTableId()).length()>0)?(dct.getTableId()+"."+dct.getColName()):dct.getColName());
1593                 return getSelectExpr(dct, dct.getColName() /* colName */);
1594         } // getSelectExpr
1595
1596         /*private String getSelectExpr(DataColumnType dct, String colName) {
1597                 String colType = dct.getColType();
1598                 if (colType.equals(AppConstants.CT_CHAR)
1599                                 || ((nvl(dct.getColFormat()).length() == 0) && (!colType
1600                                                 .equals(AppConstants.CT_DATE))))
1601                         return colName;
1602                 else
1603                         return "TO_CHAR(" + colName + ", '"
1604                                         + nvl(dct.getColFormat(), AppConstants.DEFAULT_DATE_FORMAT) + "')";
1605         } // getSelectExpr
1606         */
1607
1608         private String getSelectExpr(DataColumnType dct, String colName) {
1609                 String colType = dct.getColType();
1610                 if(colType.equals(AppConstants.CT_NUMBER)) {
1611                         return colName;
1612                 } else
1613                 if (colType.equals(AppConstants.CT_CHAR)
1614                                 || ((nvl(dct.getColFormat()).length() == 0) && (!colType
1615                                                 .equals(AppConstants.CT_DATE))))
1616                         return colName;
1617
1618                 else
1619                         return "TO_CHAR(" + colName + ", '"
1620                                         + nvl(dct.getColFormat(), AppConstants.DEFAULT_DATE_FORMAT) + "')";
1621         } // getSelectExpr
1622
1623
1624         /** ************************************************************************************************* */
1625
1626         public DataSourceType getTableById(String tableId) {
1627                 for (Iterator iter = getDataSourceList().getDataSource().iterator(); iter.hasNext();) {
1628                         DataSourceType ds = (DataSourceType) iter.next();
1629                         if (ds.getTableId().equals(tableId))
1630                                 return ds;
1631                 } // for
1632
1633                 return null;
1634         } // getTableById
1635
1636         public DataSourceType getTableByDBName(String tableName) {
1637                 for (Iterator iter = getDataSourceList().getDataSource().iterator(); iter.hasNext();) {
1638                         DataSourceType ds = (DataSourceType) iter.next();
1639                         if (ds.getTableName().equals(tableName))
1640                                 return ds;
1641                 } // for
1642
1643                 return null;
1644         } // getTableByDBName
1645
1646         public DataSourceType getColumnTableById(String colId) {
1647                 return getTableById(getColumnById(colId).getTableId());
1648         } // getColumnTableById
1649
1650         public DataColumnType getColumnById(String colId) {
1651                 List reportCols = getAllColumns();
1652                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1653                         DataColumnType dc = (DataColumnType) iter.next();
1654                         if (dc.getColId().toLowerCase().equals(colId.toLowerCase()))
1655                                 return dc;
1656                 } // for
1657
1658                 return null;
1659         } // getColumnById
1660
1661         public DataColumnType getChartLegendColumn() {
1662                 List reportCols = getAllColumns();
1663                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1664                         DataColumnType dc = (DataColumnType) iter.next();
1665                         if (nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))
1666                                 return dc;
1667                 } // for
1668                 return null;
1669         } // getChartLegendColumn
1670
1671         /*
1672          * public DataColumnType getChartValueColumn() { List reportCols =
1673          * getAllColumns(); for(Iterator iter=reportCols.iterator(); iter.hasNext(); ) {
1674          * DataColumnType dc = (DataColumnType) iter.next(); if(dc.getChartSeq()>0)
1675          * return dc; } // for
1676          *
1677          * return null; } // getChartValueColumn
1678          */
1679
1680         public List getChartValueColumnsList( int filter, HashMap formValues) { /*filter; all=0;create without new chart =1; createNewChart=2 */
1681                 List reportCols = getAllColumns();
1682
1683                 ArrayList chartValueCols = new ArrayList();
1684                 int flag = 0;
1685                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1686                         flag = 0;
1687                         DataColumnType dc = (DataColumnType) iter.next();
1688 //                  if(filter == 2 || filter == 1) {
1689                         flag = getDependsOnFormFieldFlag(dc, formValues);
1690
1691                         if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) && flag == 0 ) {
1692                                 if(!AppUtils.nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND)) {
1693                                 if(nvl(dc.getChartGroup()).length()<=0) {
1694                                         if( filter == 2 && (dc.isCreateInNewChart()!=null && dc.isCreateInNewChart().booleanValue())) {
1695                                                 chartValueCols.add(dc);
1696                                         } else if (filter == 1 && (dc.isCreateInNewChart()==null || !dc.isCreateInNewChart().booleanValue())) {
1697                                                 chartValueCols.add(dc);
1698                                         }
1699                                         else if(filter == 0) chartValueCols.add(dc);
1700                                 } else chartValueCols.add(dc);
1701                         }
1702                         }
1703 //                      } else
1704 //                              chartValueCols.add(dc);
1705                 } // for
1706                 Collections.sort(chartValueCols, new ChartSeqComparator());
1707                 return chartValueCols;
1708         } // getChartValueColumnsList
1709
1710
1711         /* public ListModelList<Item> getChartValueColumnsListModelList( int filter, HashMap formValues) { / *filter; all=0;create without new chart =1; createNewChart=2 * /
1712                 List reportCols = getAllColumns();
1713
1714                 ArrayList chartValueCols = new ArrayList();
1715                 ListModelList<Item> chartValueListModelList = new ListModelList<Item>();
1716                 int flag = 0;
1717                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1718                         flag = 0;
1719                         DataColumnType dc = (DataColumnType) iter.next();
1720 //                  if(filter == 2 || filter == 1) {
1721                         flag = getDependsOnFormFieldFlag(dc, formValues);
1722
1723                         if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) && flag == 0 ) {
1724                                 if(nvl(dc.getChartGroup()).length()<=0) {
1725                                         if( filter == 2 && (dc.isCreateInNewChart()!=null && dc.isCreateInNewChart().booleanValue())) {
1726                                                 chartValueCols.add(dc);
1727                                         } else if (filter == 1 && (dc.isCreateInNewChart()==null || !dc.isCreateInNewChart().booleanValue())) {
1728                                                 chartValueCols.add(dc);
1729                                         }
1730                                         else if(filter == 0) chartValueCols.add(dc);
1731                                 } else chartValueCols.add(dc);
1732                         }
1733 //                      } else
1734 //                              chartValueCols.add(dc);
1735                         chartValueListModelList.add(new Item(dc.getColId(), dc.getDisplayName()));
1736                 } // for
1737                 Collections.sort(chartValueCols, new ChartSeqComparator());
1738                 return chartValueListModelList;
1739         } // getChartValueColumnsList */
1740
1741
1742         /** Check whether chart has series (Category) columns **/
1743         public boolean hasSeriesColumn() {
1744                 List reportCols = getAllColumns();
1745
1746                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1747                         DataColumnType dc = (DataColumnType) iter.next();
1748                         if (dc.isChartSeries()!=null && dc.isChartSeries().booleanValue())
1749                                 return true;
1750                 } // for
1751                 return false;
1752         } // hasSeriesColumn
1753
1754
1755         public List getChartDisplayNamesList( int filter, HashMap formValues) { /*filter; all=0;create without new chart =1; createNewChart=2 */
1756                 List reportCols = getAllColumns();
1757                 ArrayList chartValueColNames = new ArrayList();
1758                 int flag = 0;
1759                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1760                                 flag = 0;
1761                                 DataColumnType dc = (DataColumnType) iter.next();
1762 //                      if(filter == 2 || filter == 1) {
1763                                 flag = getDependsOnFormFieldFlag(dc, formValues);
1764
1765                                 if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) && flag == 0) {
1766                                                 if(nvl(dc.getChartGroup()).length()<=0) {
1767                                                         if( filter == 2 && (dc.isCreateInNewChart()!=null && dc.isCreateInNewChart().booleanValue()) ) {
1768                                                                 chartValueColNames.add(dc.getDisplayName());
1769                                                         } else if (filter == 1 && (dc.isCreateInNewChart()==null || !dc.isCreateInNewChart().booleanValue())) {
1770                                                                 chartValueColNames.add(dc.getDisplayName());
1771                                                         }
1772                                                         else if(filter == 0) chartValueColNames.add(dc.getDisplayName());
1773                                                 } else if(filter == 0) chartValueColNames.add(dc.getDisplayName());
1774                                         }
1775         //                      } else
1776         //                              chartValueColNames.add(dc.getDisplayName());
1777
1778                 }
1779                 return chartValueColNames;
1780         } // getChartDisplayNamesList
1781
1782
1783         public List getChartColumnColorsList(int filter, HashMap formValues) { /*filter; all=0;create without new chart =1; createNewChart=2 */
1784                 List reportCols = getAllColumns();
1785                 ArrayList chartValueColColors = new ArrayList();
1786                 int flag = 0;
1787                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1788                         flag = 0;
1789                         DataColumnType dc = (DataColumnType) iter.next();
1790 //                  if(filter == 2 || filter == 1) {
1791                         flag = getDependsOnFormFieldFlag(dc, formValues);
1792
1793                         if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0)  && flag == 0) {
1794                                 if(nvl(dc.getChartGroup()).length()<=0) {
1795                                         if( filter == 2 && (dc.isCreateInNewChart()!=null && dc.isCreateInNewChart().booleanValue()) ) {
1796                                                 chartValueColColors.add(dc.getChartColor());
1797                                         } else if (filter == 1 && (dc.isCreateInNewChart()==null || !dc.isCreateInNewChart().booleanValue())) {
1798                                                 chartValueColColors.add(dc.getChartColor());
1799                                         }
1800                                         else if(filter == 0) chartValueColColors.add(dc.getChartColor());
1801                                 } else if(filter == 0) chartValueColColors.add(dc.getChartColor());
1802                         }
1803 //                      } else
1804 //                              chartValueColColors.add(dc.getChartColor());
1805                 }
1806                 return chartValueColColors;
1807         } // getChartColumnColorsList
1808
1809         public List getChartValueColumnAxisList( int filter, HashMap formValues) { /*filter; all=0;create without new chart =1; createNewChart=2 */
1810                 List reportCols = getAllColumns();
1811                 ArrayList chartValueColAxis = new ArrayList();
1812                 int flag = 0;
1813                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1814                         flag = 0;
1815                         DataColumnType dc = (DataColumnType) iter.next();
1816 //                  if(filter == 2 || filter == 1) {
1817                         flag = getDependsOnFormFieldFlag(dc, formValues);
1818
1819                         if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0)  && flag == 0) {
1820                                 if(nvl(dc.getChartGroup()).length()<=0) {
1821                                         if( filter == 2 && (dc.isCreateInNewChart()!=null && dc.isCreateInNewChart().booleanValue())) {
1822                                                 chartValueColAxis.add(nvl(dc.getColOnChart(), "0"));
1823                                         } else if (filter == 1 && (dc.isCreateInNewChart()==null || !dc.isCreateInNewChart().booleanValue())) {
1824                                                 chartValueColAxis.add(nvl(dc.getColOnChart(), "0"));
1825                                         }
1826                                         else if(filter == 0) chartValueColAxis.add(nvl(dc.getColOnChart(), "0"));
1827                                 } else if(filter == 0) chartValueColAxis.add(nvl(dc.getColOnChart(), "0"));
1828                         }
1829 //                      } else
1830 //                              chartValueColAxis.add(nvl(dc.getColOnChart(), "0"));
1831                 }
1832                 return chartValueColAxis;
1833         } // getChartColumnAxisList
1834
1835
1836         public List getChartValueNewChartList() {
1837                 ArrayList chartValueNewChartAxis = new ArrayList();
1838                 for (Iterator iter = getChartValueColumnsList(2, null).iterator(); iter.hasNext();)
1839                         chartValueNewChartAxis.add(new Boolean(((DataColumnType) iter.next()).isCreateInNewChart()));
1840                 return chartValueNewChartAxis;
1841         } // getChartValueNewChartList
1842
1843         public List getAllChartGroups() {
1844                 ArrayList chartGroups = new ArrayList();
1845                 String chartGroupName="";
1846                 List reportCols = getAllColumns();
1847                 Set groupSet = new TreeSet();
1848                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1849                         DataColumnType dc = (DataColumnType) iter.next();
1850                         if(dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) {
1851                                 chartGroupName = dc.getChartGroup();
1852                                 if(nvl(chartGroupName).length()>0)
1853                                         groupSet.add(chartGroupName);
1854                         }
1855                 }
1856                 List l = new ArrayList(groupSet);
1857                 return l;
1858         } // getAllChartGroups
1859
1860         public HashMap getAllChartYAxis(ReportParamValues reportParamValues) {
1861                 String chartYAxis="";
1862                 List reportCols = getAllColumns();
1863                 HashMap hashMap = new HashMap();
1864                 FormFieldList formFieldList = getFormFieldList();
1865                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1866                         DataColumnType dc = (DataColumnType) iter.next();
1867                         if(dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) {
1868                                 chartYAxis = dc.getYAxis();
1869                                 if(formFieldList!=null && reportParamValues!=null) {
1870                                         for (Iterator iter1 = getFormFieldList().getFormField().iterator(); iter1.hasNext();) {
1871                                                 FormFieldType fft = (FormFieldType) iter1.next();
1872                                                 String fieldDisplay = getFormFieldDisplayName(fft);
1873                                                 String fieldId = fft.getFieldId();
1874                                                 if(!fft.getFieldType().equals(FormField.FFT_BLANK) && !fft.getFieldType().equals(FormField.FFT_LIST_MULTI) && !fft.getFieldType().equals(FormField.FFT_TEXTAREA)) {
1875                                                         String paramValue = Utils.oracleSafe(nvl(reportParamValues.getParamValue(fieldId)));
1876                                                         chartYAxis = Utils.replaceInString(chartYAxis, fieldDisplay, nvl(
1877                                                 paramValue, ""));
1878                                                 }
1879                                         }
1880                                 }
1881                                 if(nvl(dc.getChartGroup()).length()>0)
1882                                         hashMap.put(dc.getChartGroup(),chartYAxis);
1883                         }
1884                 }
1885                 return hashMap;
1886         } // getAllChartGroups
1887
1888         public List getChartGroupColumnAxisList( String chartGroupName, HashMap formValues ) { /*filter; all=0;create without new chart =1; createNewChart=2 */
1889                 List reportCols = getAllColumns();
1890                 ArrayList chartGroupColAxis = new ArrayList();
1891                 String chartGroup = chartGroupName.substring(0,chartGroupName.lastIndexOf("|"));
1892                 int flag = 0;
1893                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1894                         flag = 0;
1895                         DataColumnType dc = (DataColumnType) iter.next();
1896 //                  if(filter == 2 || filter == 1) {
1897                         flag = getDependsOnFormFieldFlag(dc, formValues);
1898
1899                         if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0)  && flag == 0) {
1900                                 if( nvl(dc.getChartGroup()).indexOf("|") > 0 && (nvl(dc.getChartGroup().substring(0,dc.getChartGroup().lastIndexOf("|"))).equals(chartGroup))) {
1901                                 //if( nvl(dc.getChartGroup().substring(0,dc.getChartGroup().lastIndexOf("|"))).equals(chartGroup)) {
1902                                         //System.out.println("$$$$$$$DC " + dc.getColId()+ " " + dc.getColOnChart());
1903                                         chartGroupColAxis.add(dc);
1904                                 }
1905                         }
1906 //                      } else
1907 //                              chartValueColAxis.add(nvl(dc.getColOnChart(), "0"));
1908                 }
1909                 Collections.sort(chartGroupColAxis, new ChartSeqComparator());
1910                 return chartGroupColAxis;
1911         } // getChartColumnAxisList
1912
1913         public List getChartGroupValueColumnAxisList( String chartGroupName, HashMap formValues ) {
1914                  List reportCols = getAllColumns();
1915                  String index =  chartGroupName.substring(chartGroupName.lastIndexOf("|")+1);
1916                  String chartGroup = chartGroupName.substring(0,chartGroupName.lastIndexOf("|"));
1917                  //System.out.println("$$$$INDEX " + index);
1918                  ArrayList chartGroupValueColAxis = new ArrayList();
1919                         int flag = 0;
1920
1921                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1922                                 flag = 0;
1923                                 DataColumnType dc = (DataColumnType) iter.next();
1924                                 flag = getDependsOnFormFieldFlag(dc, formValues);
1925
1926                                 if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) && flag == 0) {
1927                                         //System.out.println(" Chartgroup " + dc.getChartGroup().substring(0,dc.getChartGroup().lastIndexOf("|")));
1928                                         if( nvl(dc.getChartGroup()).indexOf("|") > 0 && (nvl(dc.getChartGroup().substring(0,dc.getChartGroup().lastIndexOf("|"))).equals(chartGroup))) {
1929                                         //if( nvl(dc.getChartGroup().substring(0,dc.getChartGroup().lastIndexOf("|"))).equals(chartGroup)) {
1930                                                 //System.out.println(" Added Chartgroupname " + chartGroup + " " + dc.getChartGroup() + " " + index);
1931                                                 chartGroupValueColAxis.add(dc);
1932                                         }
1933                                 }
1934                         }
1935                 return chartGroupValueColAxis;
1936         } // getChartColumnAxisList
1937
1938         public List getChartGroupDisplayNamesList( String chartGroupName, HashMap formValues) {
1939                 List reportCols = getAllColumns();
1940                 ArrayList chartGroupValueColNames = new ArrayList();
1941                 String chartGroup = chartGroupName.substring(0,chartGroupName.lastIndexOf("|"));
1942                 int flag = 0;
1943
1944                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1945                                 flag = 0;
1946                                 DataColumnType dc = (DataColumnType) iter.next();
1947                                 //System.out.println("$$$$$CHART " + dc.getChartSeq()+ " " + dc.getChartGroup()+ " " + chartGroup);
1948                                 flag = getDependsOnFormFieldFlag(dc, formValues);
1949
1950                                 if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) && flag == 0 ) {
1951                                         if( nvl(dc.getChartGroup()).indexOf("|") > 0 && (nvl(dc.getChartGroup().substring(0,dc.getChartGroup().lastIndexOf("|"))).equals(chartGroup))) {
1952                                                         chartGroupValueColNames.add(dc.getDisplayName());
1953                                         }
1954                                 }
1955                         }
1956                 return chartGroupValueColNames;
1957         } // getChartDisplayNamesList
1958
1959
1960         public List getChartGroupColumnColorsList(String chartGroupName, HashMap formValues) {
1961                 List reportCols = getAllColumns();
1962                 ArrayList chartValueColColors = new ArrayList();
1963                 String chartGroup = chartGroupName.substring(0,chartGroupName.lastIndexOf("|"));
1964                 int flag = 0;
1965                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1966                         flag = 0;
1967                         DataColumnType dc = (DataColumnType) iter.next();
1968                         flag = getDependsOnFormFieldFlag(dc, formValues);
1969
1970                         if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) && flag == 0 ) {
1971                                 if( nvl(dc.getChartGroup()).indexOf("|") > 0 && (nvl(dc.getChartGroup().substring(0,dc.getChartGroup().lastIndexOf("|"))).equals(chartGroup))) {
1972                                 //if( nvl(dc.getChartGroup().substring(0,dc.getChartGroup().lastIndexOf("|"))).equals(chartGroup)) {
1973                                                 chartValueColColors.add(dc.getChartColor());
1974                                 }
1975                         }
1976                 }
1977                 return chartValueColColors;
1978         } // getChartColumnColorsList
1979
1980
1981         public List getCrossTabRowColumns() {
1982                 List reportCols = getAllColumns();
1983                 Vector v = new Vector(reportCols.size());
1984
1985                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1986                         DataColumnType dc = (DataColumnType) iter.next();
1987                         if (nvl(dc.getCrossTabValue()).equals(AppConstants.CV_ROW))
1988                                 v.add(dc);
1989                 } // for
1990
1991                 return v;
1992         } // getCrossTabRowColumns
1993
1994         public List getCrossTabColColumns() {
1995                 List reportCols = getAllColumns();
1996                 Vector v = new Vector(reportCols.size());
1997
1998                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
1999                         DataColumnType dc = (DataColumnType) iter.next();
2000                         if (nvl(dc.getCrossTabValue()).equals(AppConstants.CV_COLUMN))
2001                                 v.add(dc);
2002                 } // for
2003
2004                 return v;
2005         } // getCrossTabColColumns
2006
2007         public String getCrossTabDisplayTotal(String rowColPos) {
2008                 DataColumnType dct = getCrossTabValueColumn();
2009                 if (dct == null)
2010                         return "";
2011
2012                 String displayTotal = nvl(dct.getDisplayTotal());
2013                 if (displayTotal.indexOf('|') >= 0) {
2014                         String displayColTotal = displayTotal.substring(0, displayTotal.indexOf('|'));
2015                         String displayRowTotal = displayTotal.substring(displayTotal.indexOf('|') + 1);
2016
2017                         if (rowColPos.equals(AppConstants.CV_COLUMN))
2018                                 displayTotal = displayColTotal;
2019                         else if (rowColPos.equals(AppConstants.CV_ROW))
2020                                 displayTotal = displayRowTotal;
2021                         else if (displayColTotal.equals(displayRowTotal))
2022                                 displayTotal = displayColTotal;
2023                 } // if
2024
2025                 return displayTotal;
2026         } // getCrossTabDisplayTotal
2027
2028         public DataColumnType getCrossTabValueColumn() {
2029                 List reportCols = getAllColumns();
2030                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
2031                         DataColumnType dc = (DataColumnType) iter.next();
2032                         if (nvl(dc.getCrossTabValue()).equals(AppConstants.CV_VALUE))
2033                                 return dc;
2034                 } // for
2035
2036                 return null;
2037         } // getCrossTabValueColumn
2038
2039         public int getCrossTabValueColumnIndex() { // Returns the index counting
2040                                                                                                 // only visible columns
2041                 List reportCols = getAllColumns();
2042
2043                 int idx = 0;
2044                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
2045                         DataColumnType dc = (DataColumnType) iter.next();
2046                         if (nvl(dc.getCrossTabValue()).equals(AppConstants.CV_VALUE))
2047                                 break;
2048                         if (dc.isVisible())
2049                                 idx++;
2050                 } // for
2051
2052                 return idx;
2053         } // getCrossTabValueColumnIndex
2054
2055         public ColFilterType getFilterById(String colId, int filterIndex) {
2056                 DataColumnType dc = getColumnById(colId);
2057                 try {
2058                         return (ColFilterType) dc.getColFilterList().getColFilter().get(filterIndex);
2059                 } catch (Exception e) {
2060                         return null;
2061                 }
2062         } // getFilterById
2063
2064         public boolean needFormInput() {
2065                 List reportCols = getAllColumns();
2066                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
2067                         DataColumnType dct = (DataColumnType) iter.next();
2068
2069                         if (dct.getColFilterList() != null) {
2070                                 List fList = dct.getColFilterList().getColFilter();
2071                                 for (Iterator iterF = fList.iterator(); iterF.hasNext();) {
2072                                         ColFilterType cft = (ColFilterType) iterF.next();
2073
2074                                         if (nvl(cft.getArgType()).equals(AppConstants.AT_FORM))
2075                                                 return true;
2076                                 } // for
2077                         } // if
2078                 } // for
2079
2080                 return false;
2081         } // needFormInput
2082
2083         public int getNumSortColumns() {
2084                 int numSortCols = 0;
2085                 for (Iterator iter = getAllColumns().iterator(); iter.hasNext();) {
2086                         DataColumnType dct = (DataColumnType) iter.next();
2087                         if (dct.getOrderBySeq() != null && dct.getOrderBySeq() > 0)
2088                                 numSortCols++;
2089                 } // for
2090
2091                 return numSortCols;
2092         } // getNumSortColumns
2093
2094         public SemaphoreType getSemaphoreById(String semaphoreId) {
2095                 if (getSemaphoreList() != null && semaphoreId != null)
2096                         for (Iterator iter = getSemaphoreList().getSemaphore().iterator(); iter.hasNext();) {
2097                                 SemaphoreType sem = (SemaphoreType) iter.next();
2098                                 if (sem.getSemaphoreId().equals(semaphoreId))
2099                                         return sem;
2100                         } // for
2101
2102                 return null;
2103         } // getSemaphoreById
2104
2105         public void deleteSemaphore(SemaphoreType semaphore) {
2106                 if (getSemaphoreList() != null) {
2107                         if(getSemaphoreList().getSemaphore()!= null)
2108                                 getSemaphoreList().getSemaphore().remove((SemaphoreType) semaphore);
2109                 }
2110         } //  deleteSemaphore
2111
2112
2113         public void setSemaphore(SemaphoreType sem) {
2114                 if (getSemaphoreList() != null) {
2115                         getSemaphoreList().getSemaphore().add(sem);
2116                 }
2117
2118         } // setSemaphore
2119
2120         public static FormatType getSemaphoreFormatById(SemaphoreType semaphore, String formatId) {
2121                 if (semaphore != null)
2122                         for (Iterator iter = semaphore.getFormatList().getFormat().iterator(); iter
2123                                         .hasNext();) {
2124                                 FormatType fmt = (FormatType) iter.next();
2125                                 if (fmt.getFormatId().equals(formatId))
2126                                         return fmt;
2127                         } // for
2128
2129                 return null;
2130         } // getSemaphoreFormatById
2131
2132         public FormFieldType getFormFieldById(String fieldId) {
2133                 if (getFormFieldList() != null && fieldId != null)
2134                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
2135                                 FormFieldType fft = (FormFieldType) iter.next();
2136                                 if (fft.getFieldId().equals(fieldId))
2137                                         return fft;
2138                         } // for
2139
2140                 return null;
2141         } // getFormFieldById
2142
2143         public FormFieldType getFormFieldByDisplayValue(String fieldDisplay) {
2144                 // fieldDisplay expected to be [fieldName]
2145                 if (getFormFieldList() != null && fieldDisplay != null)
2146                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
2147                                 FormFieldType fft = (FormFieldType) iter.next();
2148                                 if (fieldDisplay.equals(getFormFieldDisplayName(fft)))
2149                                         return fft;
2150                         } // for
2151
2152                 return null;
2153         } // getFormFieldById
2154
2155         public String getFormFieldDisplayName(FormFieldType fft) {
2156                 return "[" + fft.getFieldName() + "]";
2157         } // getFormFieldDisplayName
2158
2159         /** ************************************************************************************************* */
2160
2161         public void resetCache(boolean sqlOnly) {
2162                 generatedSQL = null;
2163                 if (!sqlOnly) {
2164                         allColumns = null;
2165                         allFilters = null;
2166                 }
2167         } // resetCache
2168
2169         public String getOuterJoinType(DataSourceType curTable) {
2170                 String refDefinition = nvl(curTable.getRefDefinition());
2171                 int outerJoinIdx = refDefinition.indexOf(" (+)");
2172                 if (outerJoinIdx < 0)
2173                         // No outer join
2174                         return "";
2175
2176                 int equalSignIdx = refDefinition.indexOf("=");
2177                 if (refDefinition.indexOf(curTable.getTableId()) < equalSignIdx)
2178                         // Cur. table is on the left side
2179                         return (outerJoinIdx < equalSignIdx) ? AppConstants.OJ_CURRENT
2180                                         : AppConstants.OJ_JOINED;
2181                 else
2182                         // Joined table is on the left side
2183                         return (outerJoinIdx < equalSignIdx) ? AppConstants.OJ_JOINED
2184                                         : AppConstants.OJ_CURRENT;
2185         } // getOuterJoinType
2186
2187         public String getFormFieldName(ColFilterType filter) {
2188                 FormFieldType fft = null;
2189                 if (filter.getArgType().equals(AppConstants.AT_FORM))
2190                         fft = getFormFieldByDisplayValue(filter.getArgValue());
2191
2192                 return (fft != null) ? fft.getFieldId() : filter.getColId() + "_f"
2193                                 + filter.getFilterSeq();
2194         } // getFormFieldName
2195
2196         public String getFormFieldDisplayName(DataColumnType column, ColFilterType filter) {
2197                 FormFieldType fft = null;
2198                 if (filter.getArgType().equals(AppConstants.AT_FORM))
2199                         fft = getFormFieldByDisplayValue(filter.getArgValue());
2200
2201                 return (fft != null) ? fft.getFieldName() : column.getDisplayName() + "&nbsp;"
2202                                 + filter.getExpression();
2203         } // getFormFieldDisplayName
2204
2205         public Calendar getFormFieldRangeStart(ColFilterType filter) {
2206                 FormFieldType fft = null;
2207                 if (filter.getArgType().equals(AppConstants.AT_FORM))
2208                         fft = getFormFieldByDisplayValue(filter.getArgValue());
2209
2210                 return (fft != null) ? fft.getRangeStartDate().toGregorianCalendar() : null;
2211         } // getFormFieldRangeStart
2212
2213         public Calendar getFormFieldRangeEnd(ColFilterType filter) {
2214                 FormFieldType fft = null;
2215                 if (filter.getArgType().equals(AppConstants.AT_FORM))
2216                         fft = getFormFieldByDisplayValue(filter.getArgValue());
2217
2218                 //System.out.println("as " + fft.getRangeEndDate());
2219                 return (fft != null) ? fft.getRangeEndDate().toGregorianCalendar() : null;
2220         } // getFormFieldRangeEnd
2221
2222         public String getFormFieldRangeStartSQL(ColFilterType filter) {
2223                 FormFieldType fft = null;
2224                 if (filter.getArgType().equals(AppConstants.AT_FORM))
2225                         fft = getFormFieldByDisplayValue(filter.getArgValue());
2226
2227                 return (fft != null) ? fft.getRangeStartDateSQL() : null;
2228         } // getFormFieldRangeStart
2229
2230         public String getFormFieldRangeEndSQL(ColFilterType filter) {
2231                 FormFieldType fft = null;
2232                 if (filter.getArgType().equals(AppConstants.AT_FORM))
2233                         fft = getFormFieldByDisplayValue(filter.getArgValue());
2234
2235                 //System.out.println("as " + fft.getRangeEndDate());
2236                 return (fft != null) ? fft.getRangeEndDateSQL() : null;
2237         } // getFormFieldRangeEnd
2238
2239         public String getUniqueTableId(String tableName) {
2240                 String tableIdPrefix = tableName.startsWith("MSA_") ? tableName.substring(4, 6)
2241                                 : tableName.substring(0, 2);
2242                 String tableId = "";
2243
2244                 int tableIdN = getDataSourceList().getDataSource().size() + 1;
2245                 do {
2246                         tableId = tableIdPrefix.toLowerCase() + (tableIdN++);
2247                 } while (getTableById(tableId) != null);
2248
2249                 return tableId;
2250         } // getUniqueTableId
2251
2252         /** ************************************************************************************************* */
2253
2254         protected void deleteDataSourceType(String tableId) {
2255                 List dsList = getDataSourceList().getDataSource();
2256                 for (Iterator iter = dsList.iterator(); iter.hasNext();) {
2257                         DataSourceType dst = (DataSourceType) iter.next();
2258                         if (dst.getTableId().equals(tableId))
2259                                 iter.remove();
2260                         else if (nvl(dst.getRefTableId()).equals(tableId)) {
2261                                 dst.setRefTableId(null);
2262                                 dst.setRefDefinition(null);
2263                         }
2264                 } // for
2265
2266                 resetCache(false);
2267         } // deleteDataSourceType
2268
2269         public static void adjustColumnType(DataColumnType dct) {
2270                 dct.setColType(dct.getDbColType());
2271
2272                 if (dct.isCalculated())
2273                         if (dct.getColName().startsWith("SUM(") || dct.getColName().startsWith("COUNT(")
2274                                         || dct.getColName().startsWith("AVG(")
2275                                         || dct.getColName().startsWith("STDDEV(")
2276                                         || dct.getColName().startsWith("VARIANCE("))
2277                                 dct.setColType(AppConstants.CT_NUMBER);
2278                         else if (dct.getColName().startsWith("DECODE(") || dct.getColName().startsWith("coalesce("))
2279                                 dct.setColType(AppConstants.CT_CHAR);
2280         } // adjustColumnType
2281
2282         public static boolean getColumnNoParseDateFlag(DataColumnType dct) {
2283                 return (nvls(dct.getComment()).indexOf(AppConstants.CF_NO_PARSE_DATE) >= 0);
2284         } // getColumnNoParseDateFlag
2285
2286         public static void setColumnNoParseDateFlag(DataColumnType dct, boolean noParseDateFlag) {
2287                 dct.setComment(noParseDateFlag ? AppConstants.CF_NO_PARSE_DATE : null);
2288         } // setColumnNoParseDateFlag
2289
2290         /** ************************************************************************************************* */
2291
2292         public static String getSQLBasedFFTColTableName(String fftColId) {
2293                 return fftColId.substring(0, fftColId.indexOf('.'));
2294         } // getSQLBasedFFTColTableName
2295
2296         public static String getSQLBasedFFTColColumnName(String fftColId) {
2297                 fftColId = (fftColId.indexOf('|') < 0) ? fftColId : fftColId.substring(0, fftColId
2298                                 .indexOf('|'));
2299                 return fftColId.substring(fftColId.indexOf('.') + 1);
2300         } // getSQLBasedFFTColColumnName
2301
2302         public static String getSQLBasedFFTColDisplayFormat(String fftColId) {
2303                 return (fftColId.indexOf('|') < 0) ? "" : fftColId
2304                                 .substring(fftColId.indexOf('|') + 1);
2305         } // getSQLBasedFFTColDisplayFormat
2306
2307         /** ************************************************************************************************* */
2308
2309         public List<DataColumnType> getAllColumns() {
2310                 if (cr == null)
2311                         throw new NullPointerException("CustomReport not initialized");
2312
2313                 if (allColumns == null) {
2314                         allColumns = new Vector();
2315
2316                         List dsList = getDataSourceList().getDataSource();
2317                         for (Iterator iter = dsList.iterator(); iter.hasNext();) {
2318                                 DataSourceType ds = (DataSourceType) iter.next();
2319
2320                                 // allColumns.addAll(ds.getDataColumnList().getDataColumn());
2321                                 List dcList = ds.getDataColumnList().getDataColumn();
2322                                 for (Iterator iterC = dcList.iterator(); iterC.hasNext();) {
2323                                         DataColumnType dc = (DataColumnType) iterC.next();
2324
2325                                         allColumns.add(dc);
2326                                 } // for
2327                         } // for
2328
2329                         Collections.sort(allColumns, new OrderSeqComparator());
2330                 } // if
2331
2332                 return allColumns;
2333         } // getAllColumns
2334
2335         public List getOnlyVisibleColumns() {
2336                 if (cr == null)
2337                         throw new NullPointerException("CustomReport not initialized");
2338
2339                 if (allVisibleColumns == null) {
2340                         allVisibleColumns = new Vector();
2341
2342                         List dsList = getDataSourceList().getDataSource();
2343                         for (Iterator iter = dsList.iterator(); iter.hasNext();) {
2344                                 DataSourceType ds = (DataSourceType) iter.next();
2345
2346                                 // allColumns.addAll(ds.getDataColumnList().getDataColumn());
2347                                 List dcList = ds.getDataColumnList().getDataColumn();
2348                                 for (Iterator iterC = dcList.iterator(); iterC.hasNext();) {
2349                                         DataColumnType dc = (DataColumnType) iterC.next();
2350                                         if(dc.isVisible())
2351                                                 allVisibleColumns.add(dc);
2352                                 } // for
2353                         } // for
2354
2355                         Collections.sort(allVisibleColumns, new OrderSeqComparator());
2356                 } // if
2357
2358                 return allVisibleColumns;
2359         } // getOnlyVisibleColumns
2360         public int getVisibleColumnCount() {
2361                 if (cr == null)
2362                         throw new NullPointerException("CustomReport not initialized");
2363                 int colCount = 0;
2364                         List dsList = getDataSourceList().getDataSource();
2365                         for (Iterator iter = dsList.iterator(); iter.hasNext();) {
2366                                 DataSourceType ds = (DataSourceType) iter.next();
2367
2368                                 // allColumns.addAll(ds.getDataColumnList().getDataColumn());
2369                                 List dcList = ds.getDataColumnList().getDataColumn();
2370                                 for (Iterator iterC = dcList.iterator(); iterC.hasNext();) {
2371                                         DataColumnType dc = (DataColumnType) iterC.next();
2372                                         if(dc.isVisible()) colCount ++;
2373                                 } // for
2374                         } // for
2375
2376                 return colCount;
2377         }
2378
2379         public List getAllFilters() {
2380                 if (cr == null)
2381                         throw new NullPointerException("CustomReport not initialized");
2382
2383                 // if(allFilters==null) {
2384                 allFilters = new Vector();
2385
2386                 List reportCols = getAllColumns();
2387                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
2388                         DataColumnType dct = (DataColumnType) iter.next();
2389
2390                         if (dct.getColFilterList() != null) {
2391                                 List colFilters = dct.getColFilterList().getColFilter();
2392
2393                                 for (Iterator iterF = colFilters.iterator(); iterF.hasNext();) {
2394                                         ColFilterType cft = (ColFilterType) iterF.next();
2395
2396                                         allFilters.add(cft);
2397                                 } // for
2398                         } // if
2399                 } // for
2400
2401                 // Collections.sort(allFilters, ??);
2402                 // } // if
2403
2404                 return allFilters;
2405         } // getAllFilters
2406
2407         private String formatValue(String value, DataColumnType dc, boolean useDefaultDateFormat) throws RaptorException {
2408                 return formatValue(value, dc, useDefaultDateFormat, getColumnTableById(dc.getColId()), null);
2409         } // formatValue
2410
2411         private String formatValue(String value, DataColumnType dc, boolean useDefaultDateFormat,
2412                         DataSourceType ds, FormFieldType fft) throws RaptorException {
2413                 String fmtValue = null;
2414
2415                 if (nvl(value).length() == 0)
2416                         fmtValue = "";
2417                 else if (value.equals(AppConstants.FILTER_MAX_VALUE)
2418                                 || value.equals(AppConstants.FILTER_MIN_VALUE))
2419                         fmtValue = "(SELECT "
2420                                         + (value.equals(AppConstants.FILTER_MAX_VALUE) ? "MAX" : "MIN") + "("
2421                                         + dc.getColName() + ") FROM " + ds.getTableName() + ")";
2422                 else if (dc.getColType().equals(AppConstants.CT_NUMBER)) {
2423                         try {
2424                                 double vD = Double.parseDouble(value);
2425                                 fmtValue = value;
2426                         } catch(NumberFormatException ex) {
2427                         throw new UserDefinedException("Expected number, Given String for the form field \"" + fft.getFieldName()+"\"");
2428                         }
2429                 }
2430                 else if (dc.getColType().equals(AppConstants.CT_DATE)) {
2431                         if (fft!=null && (fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) ) {
2432                                 fmtValue = "TO_DATE('"
2433                                         + value
2434                                         + "', '"
2435                                         + (useDefaultDateFormat ? AppConstants.DEFAULT_DATE_FORMAT : nvl(dc
2436                                                         .getColFormat(), AppConstants.DEFAULT_DATE_FORMAT));//+" HH24:MI:SS')";
2437                                 fmtValue = fmtValue + " HH24";
2438                                 if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC))
2439                                         fmtValue = fmtValue + ":MI";
2440                                 if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC))
2441                                         fmtValue = fmtValue + " HH24:MI:SS";
2442                         } else {
2443                                 fmtValue = "TO_DATE('"
2444                                         + value
2445                                         + "', '"
2446                                         + (useDefaultDateFormat ? AppConstants.DEFAULT_DATE_FORMAT : nvl(dc
2447                                                         .getColFormat(), AppConstants.DEFAULT_DATE_FORMAT)) + "')";
2448                         if (Globals.getMonthFormatUseLastDay())
2449                                 if (!useDefaultDateFormat)
2450                                         if (nvl(dc.getColFormat(), AppConstants.DEFAULT_DATE_FORMAT).equals(
2451                                                         "MM/YYYY")
2452                                                         || nvl(dc.getColFormat(), AppConstants.DEFAULT_DATE_FORMAT)
2453                                                                         .equals("MONTH, YYYY"))
2454                                                 fmtValue = "ADD_MONTHS(" + fmtValue + ", 1)-1";
2455                         }
2456         }else {
2457                         fmtValue = value;
2458                         if (!fmtValue.startsWith("'"))
2459                                 fmtValue = "'" + fmtValue + "'";
2460                 }
2461
2462                 return fmtValue;
2463         } // formatValue
2464
2465         private String formatListValue(String listValue, DataColumnType dc,
2466                         boolean useDefaultDateFormat, boolean useOnlyPipeDelimiter) throws RaptorException {
2467                 return formatListValue("", listValue, dc, useDefaultDateFormat, useOnlyPipeDelimiter,
2468                                 getColumnTableById(dc.getColId()), null);
2469         } // formatListValue
2470
2471         public String formatListValue(String fieldDisplay, String listValue, DataColumnType dc,
2472                         boolean useDefaultDateFormat, boolean useOnlyPipeDelimiter, DataSourceType ds,
2473                         String listBaseSQL) throws RaptorException {
2474                 StringBuffer fmtValue = new StringBuffer("");
2475         //if(nvl(listValue,"").trim().length()>0) {
2476                 // The below statement is commented so that pipe is taken out from parsing for text area form field
2477 //              StringTokenizer st = new StringTokenizer(listValue, useOnlyPipeDelimiter ? "|"
2478                                 //: ",|\n\r\f");
2479                 StringTokenizer st = new StringTokenizer(listValue, useOnlyPipeDelimiter ? "|"
2480                                 : ",\n\r\f");
2481
2482                 while (st.hasMoreTokens()) {
2483                         if (fmtValue.length() > 0)
2484                                 fmtValue.append(", ");
2485
2486                         if (dc == null) {
2487                                 // For SQL-based reports - value always string
2488                                 String value = st.nextToken().trim();
2489                                if (value.startsWith("'"))
2490                                  fmtValue.append(value);
2491                                else
2492                                  fmtValue.append("'" + value + "'");
2493                         } else
2494                                 fmtValue.append(formatValue(st.nextToken().trim(), dc, useDefaultDateFormat,
2495                                                 ds, null) );
2496
2497                 } // while
2498
2499                 if (fmtValue.length() == 0) {
2500                         if(nvl(fieldDisplay).length() > 0) {
2501                                 fmtValue.append("");
2502                         } else {
2503                                 fmtValue.append("(");
2504                                 fmtValue.append(nvl(listBaseSQL, "NULL"));
2505                                 fmtValue.append(")");
2506                         }
2507                 } else if (fmtValue.charAt(0) != '(') {
2508                         fmtValue.insert(0, '(');
2509                         fmtValue.append(')');
2510                 }
2511        /* }  else {
2512             fmtValue = new StringBuffer("()");
2513         }*/
2514                 return fmtValue.toString();
2515         } // formatListValue
2516
2517         private String getColumnSelectStr(DataColumnType dc, ReportParamValues paramValues) {
2518                 String colName = dc.isCalculated() ? dc.getColName()
2519                                 : ((nvl(dc.getTableId()).length() > 0) ? (dc.getTableId() + "." + dc
2520                                                 .getColName()) : dc.getColName());
2521                 String paramValue = null;
2522                 if (dc.isCalculated())
2523                         if (getFormFieldList() != null)
2524                                 for (Iterator iter2 = getFormFieldList().getFormField().iterator(); iter2
2525                                                 .hasNext();) {
2526                                         FormFieldType fft = (FormFieldType) iter2.next();
2527                                         String fieldId = fft.getFieldId();
2528                                         String fieldDisplay = getFormFieldDisplayName(fft);
2529                                         if (!paramValues.isParameterMultiValue(fieldId)) {
2530                                                 paramValue = paramValues.getParamValue(fieldId);
2531                                                 if(paramValue!=null && paramValue.length() > 0) {
2532                                                         colName = Utils.replaceInString(colName, fieldDisplay, Utils
2533                                                                         .oracleSafe(nvl(paramValue, "NULL")));
2534                                                 } else {
2535                                                         colName = Utils.replaceInString(colName, "'" + fieldDisplay + "'", nvl(
2536                              paramValue, "NULL"));
2537                                                         colName = Utils.replaceInString(colName,  fieldDisplay, nvl(
2538                                                                         paramValue, "NULL"));
2539                                                 }
2540                                         }
2541                                 } // for
2542
2543                 return colName;
2544         } // getColumnSelectStr
2545
2546         private void addExtraIdSelect(StringBuffer selectExtraIdCl, String drillDownParams,
2547                         boolean includeSelectExpr) {
2548                 // drillDownParams - example value "c_master=[bo1.RECID$]"
2549                 drillDownParams = drillDownParams.substring(10, drillDownParams.length() - 1); // i.e.
2550                                                                                                                                                                                 // "bo1.RECID$"
2551
2552                 selectExtraIdCl.append(", ");
2553                 if (includeSelectExpr) {
2554                         selectExtraIdCl.append(drillDownParams);
2555                         selectExtraIdCl.append(" ");
2556                 } // if
2557                 selectExtraIdCl.append(drillDownParams.replace('.', '_')); // i.e.
2558                                                                                                                                         // "bo1_RECID$"
2559         } // addExtraIdSelect
2560
2561         private void addExtraDateSelect(StringBuffer selectExtraDateCl, String drillDownParams,
2562                         ReportParamValues paramValues, boolean includeSelectExpr) {
2563                 // drillDownParams - example value "ff1=[dl1]&fc2=[mo3]"
2564                 String colId = "";
2565                 while (drillDownParams.indexOf('[') >= 0) {
2566                         int startIdx = drillDownParams.indexOf('[');
2567                         int endIdx = drillDownParams.indexOf(']');
2568
2569                         if(startIdx<=endIdx) {
2570                                 colId = drillDownParams.substring(startIdx + 1, endIdx); // i.e.
2571                         } else {
2572                                 drillDownParams = drillDownParams.substring(endIdx + 1);
2573                                 continue;
2574                         }
2575                                                                                                                                                         // "dl1"
2576
2577                         DataColumnType column = getColumnById(colId);
2578                         if (column != null)
2579                                 if (column.getColType().equals(AppConstants.CT_DATE))
2580                                         if (!nvl(column.getColFormat(), AppConstants.DEFAULT_DATE_FORMAT).equals(
2581                                                         AppConstants.DEFAULT_DATE_FORMAT))
2582                                                 if (selectExtraDateCl.toString().indexOf(
2583                                                                 " " + colId + AppConstants.DD_COL_EXTENSION) < 0) {
2584                                                         selectExtraDateCl.append(", ");
2585                                                         if (includeSelectExpr) {
2586                                                                 selectExtraDateCl.append("TO_CHAR("
2587                                                                                 + getColumnSelectStr(column, paramValues) + ", '"
2588                                                                                 + AppConstants.DEFAULT_DATE_FORMAT + "')");
2589                                                                 selectExtraDateCl.append(" ");
2590                                                         } // if
2591                                                         selectExtraDateCl.append(colId + AppConstants.DD_COL_EXTENSION); // i.e.
2592                                                                                                                                                                                                 // "dl1_dde"
2593                                                 } // if
2594
2595                         drillDownParams = drillDownParams.substring(endIdx + 1);
2596                 } // while
2597         } // addExtraDateSelect
2598
2599         /*
2600          * public String generateSQL() { return generateSQL(null); } // generateSQL
2601          */
2602         public String generateSQL(String userId, HttpServletRequest request)  throws RaptorException  {
2603                 return generateSQL(new ReportParamValues(), userId, request);
2604         } // generateSQL
2605
2606         public String generateSQL(ReportParamValues paramValues, String userId, HttpServletRequest request) throws RaptorException  {
2607                 return generateSQL(paramValues, null, AppConstants.SO_ASC, userId, request);
2608         } // generateSQL
2609
2610         public String generateSQL(ReportParamValues paramValues, String overrideSortByColId,
2611                         String overrideSortByAscDesc, String userId, HttpServletRequest request) throws RaptorException {
2612                 if (cr == null)
2613                         throw new NullPointerException("CustomReport not initialized");
2614         if(nvl(getWholeSQL()).length()>0) return getWholeSQL();
2615                 if (paramValues.size() > 0)
2616                         resetCache(true);
2617         //resetCache(true);
2618                 if (generatedSQL == null) {
2619                         if (getReportDefType().equals(AppConstants.RD_SQL_BASED) || getReportDefType().equals(AppConstants.RD_SQL_BASED_DATAMIN)) {
2620                                 generatedSQL = generateSQLSQLBased(paramValues, overrideSortByColId,
2621                                                 overrideSortByAscDesc, userId, request );
2622                                 generatedChartSQL = generateSQLSQLBased(paramValues, null,
2623                                                 AppConstants.SO_ASC, userId, request );
2624                         } else if (getReportDefType().equals(AppConstants.RD_VISUAL) && !getReportType().equals(AppConstants.RT_CROSSTAB)) {
2625                                 generatedSQL = generateSQLVisual(paramValues, overrideSortByColId,
2626                                                 overrideSortByAscDesc, userId, request);
2627                                 generatedChartSQL = generateSQLVisual(paramValues, null,
2628                                                 AppConstants.SO_ASC, userId, request);
2629                         } else {
2630                                 generatedSQL = generateSQLCrossTabVisual(paramValues, overrideSortByColId,
2631                                                 overrideSortByAscDesc, userId, request);
2632                           }
2633
2634             //debugLogger.debug("******************");
2635             //debugLogger.debug("SQL Before Changing new line \n" + generatedSQL);
2636             //debugLogger.debug("******************");
2637                         generatedSQL = replaceNewLine(generatedSQL, ""+ '\n', " "+'\n'+" " );
2638                         //chart sql should not be null
2639                         if(nvl(generatedChartSQL).trim().length()>0)
2640                            generatedChartSQL = replaceNewLine(generatedChartSQL, ""+ '\n', " "+'\n'+" " );
2641             //(generatedSQL, "\n", " \n ");
2642             //debugLogger.debug("******************");
2643             //debugLogger.debug("SQL After Changing new line \n" + generatedSQL);
2644             //debugLogger.debug("******************");
2645                         //generatedSQL = replaceNewLine(generatedSQL, "SELECT", "SELECT ");
2646             //generatedSQL = replaceNewLine(generatedSQL, "select", "select ");
2647             //debugLogger.debug("SQL After Changing new line \n" + generatedSQL);
2648             //debugLogger.debug("[[[[[[[[[[[[[[[[[[");
2649             //generatedSQL = Utils.replaceInString(generatedSQL, "\n", " ");
2650             //generatedSQL = Utils.replaceInString(generatedSQL, "\t", " ");
2651                 } // if
2652
2653                 return generatedSQL;
2654         } // generateSQL
2655
2656         public String generateSQLSQLBased(ReportParamValues paramValues,
2657                         String overrideSortByColId, String overrideSortByAscDesc, String userId, HttpServletRequest request) throws RaptorException {
2658                 String sql = getReportSQL();
2659         DataSet ds = null;
2660         //debugLogger.debug(" generateSQLSQLBased " + sql);
2661         String[] reqParameters = Globals.getRequestParams().split(",");
2662         String[] sessionParameters = Globals.getSessionParams().split(",");
2663         String[] scheduleSessionParameters = Globals.getSessionParamsForScheduling().split(",");
2664         javax.servlet.http.HttpSession session = request.getSession();
2665         String dbType = "";
2666         String dbInfo = getDBInfo();
2667                 int fieldCount = 0;
2668         // For Daytona removing all formfields which has null param value
2669         Pattern re1 = null;
2670         Matcher matcher = null;
2671         int index = 0;
2672         int posFormField = 0;
2673         int posAnd = 0;
2674                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
2675                         try {
2676                          org.openecomp.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.openecomp.portalsdk.analytics.util.RemDbInfo();
2677                          dbType = remDbInfo.getDBType(dbInfo);
2678                         } catch (Exception ex) {
2679                            throw new RaptorException(ex);
2680                                 }
2681                 }
2682
2683                 sql = sql + " ";
2684                 sql = Pattern.compile("(^[\r\n]*|([\\s]))[Ss][Ee][Ll][Ee][Cc][Tt]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" SELECT ");
2685                 //sql = Pattern.compile("(^[\r\n]*|([\\s]))[Ff][Rr][Oo][Mm]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" FROM ");
2686                 sql = Pattern.compile("(^[\r\n]*|([\\s]))[Ww][Hh][Ee][Rr][Ee]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" WHERE ");
2687                 sql = Pattern.compile("(^[\r\n]*|([\\s]))[Ww][Hh][Ee][Nn]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" WHEN ");
2688                 sql = Pattern.compile("(^[\r\n]*|([\\s]))[Aa][Nn][Dd]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" AND ");
2689
2690                 if (getFormFieldList() != null) {
2691                         for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
2692
2693                                 FormFieldType fft = (FormFieldType) iter.next();
2694                                 String fieldId = fft.getFieldId();
2695                                 String fieldDisplay = getFormFieldDisplayName(fft);
2696                                 if(!fft.getFieldType().equals(FormField.FFT_BLANK)) {
2697                                 if (paramValues.isParameterMultiValue(fieldId)) {
2698                                         String replaceValue = formatListValue(fieldDisplay, Utils
2699                                                         .oracleSafe(nvl(paramValues.getParamValue(fieldId))), null, false,
2700                                                         true, null, paramValues.getParamBaseSQL(fieldId));
2701                                         if(replaceValue.length() > 0) {
2702                                                 sql = Utils.replaceInString(sql, fieldDisplay, replaceValue);
2703                                         } else {
2704                                                 fieldCount++;
2705                                                 if(fieldCount == 1) {
2706                                                         //sql = sql + " ";
2707                                                         //sql = Pattern.compile("(^[\r\n]*|([\\s]))[Ss][Ee][Ll][Ee][Cc][Tt]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" SELECT ");
2708                                                         //sql = Pattern.compile("(^[\r\n]*|([\\s]))[Ww][Hh][Ee][Rr][Ee]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" WHERE ");
2709                                                         //sql = Pattern.compile("(^[\r\n]*|([\\s]))[Aa][Nn][Dd]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" AND ");
2710                                                 }
2711                                                 //sql = getReportSQL();
2712                                                 while(sql.indexOf(fieldDisplay) > 0) {
2713 /*                                              sql = Utils.replaceInString(sql, "SELECT ", "select ");
2714                                                 sql = Utils.replaceInString(sql, "WHERE", "where");
2715                                                 sql = Utils.replaceInString(sql, " AND ", " and ");
2716 */
2717                                         re1 = Pattern.compile("(^[\r\n]|[\\s])AND(.*?[^\r\n]*)"+ "\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL);
2718                                                 //re1 = Pattern.compile("(^[\r\n]|[\\s])AND(.*?[^\r\n]*)"+ "\\["+fft.getFieldName()+ "\\]", Pattern.DOTALL);
2719 /*                              posFormField = sql.indexOf(fieldDisplay);
2720                                 posAnd = sql.lastIndexOf("and", posFormField);
2721                                 if(posAnd < 0) posAnd = 0;
2722                                 else if (posAnd > 2) posAnd = posAnd - 2;
2723                                         matcher = re1.matcher(sql);
2724 */
2725                                         posFormField = sql.indexOf(fieldDisplay);
2726                                         int posSelectField = sql.lastIndexOf("SELECT ", posFormField);
2727                                         int andField = 0;
2728                                         int whereField = 0, whenField = 0;
2729                                         andField = sql.lastIndexOf(" AND ", posFormField);
2730                                         whereField = sql.indexOf(" WHERE" , posSelectField);
2731                                         whenField = sql.indexOf(" WHEN" , posSelectField);
2732
2733                                         if(posFormField > whereField)
2734                                                 andField = sql.lastIndexOf(" AND ", posFormField);
2735                                         if (posFormField > andField && (andField > whereField || andField > whenField))
2736                                                 posAnd = andField;
2737                                         else
2738                                                 posAnd = 0;
2739                                         matcher = re1.matcher(sql);
2740
2741
2742                                         if (posAnd > 0 && matcher.find(posAnd-1)) {
2743                                                         //sql = Utils.replaceInString(sql, matcher.group(), " ");
2744                                                         matcher = re1.matcher(sql);
2745                                                         index = sql!=null?sql.lastIndexOf("["+fft.getFieldName()+"]"):-1;
2746
2747                                                         if(andField>0)
2748                                                                 index = andField;
2749                                                         else
2750                                                                 index = whereField;
2751                                                         if(index >= 0 && matcher.find(index-1)) {
2752                                                                 sql = sql.replace(matcher.group(), " ");
2753                                                         }
2754                                         } else {
2755
2756                                                 //sql = sql.replace
2757                                                 re1 = Pattern.compile("(^[\r\n]|[\\s])WHERE(.*?[^\r\n]*)\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL);
2758                                                 matcher = re1.matcher(sql);
2759                                                 if(whereField != -1) {
2760                                                         if(matcher.find(whereField-1)) {
2761                                                                 matcher = re1.matcher(sql);
2762                                                                 index = sql!=null?sql.lastIndexOf("["+fft.getFieldName()+"]"):-1;
2763                                                                 if(index >= 0 && matcher.find(index-30)) {
2764                                                                         sql = sql.replace(matcher.group(), " WHERE 1=1 ");
2765                                                         }
2766                                                                 //sql = Utils.replaceInString(sql, matcher.group(), " where 1=1 ");
2767                                                         } /*else {
2768                                                                 replaceValue = formatListValue("", Utils
2769                                                                                 .oracleSafe(nvl(paramValues.getParamValue(fieldId))), null, false,
2770                                                                                 true, null, paramValues.getParamBaseSQL(fieldId));
2771                                                                 sql = Utils.replaceInString(sql, fieldDisplay, replaceValue);
2772                                                         }*/
2773                                                 } else {
2774                                                         sql = Utils.replaceInString(sql, fieldDisplay, replaceValue);
2775                                                 }
2776
2777                                         }
2778                                                 }
2779                                         }
2780
2781                                 //sql = Utils.replaceInString(sql, " select ", " SELECT ");
2782                                 //sql = Utils.replaceInString(sql, " where ", " WHERE ");
2783                                 //sql = Utils.replaceInString(sql, " and ", " AND ");
2784
2785                                 } else {
2786                                         String paramValue = "";
2787                                         if(paramValues.isParameterTextAreaValueAndModified(fieldId)) {
2788                                                 String value = "";
2789                                                 value = nvl(paramValues
2790                                                                 .getParamValue(fieldId));
2791 //                                              value = Utils.oracleSafe(nvl(value));
2792 //                                              if (!(dbType.equals("DAYTONA") && sql.trim().toUpperCase().startsWith("SELECT"))) {
2793 //                                                      value = "('" + Utils.replaceInString(value, ",", "'|'") + "')";
2794 //                                                      value = Utils.replaceInString(value, "|", ",");
2795 //                                                      paramValue = XSSFilter.filterRequestOnlyScript(value);
2796 //                                              } else if (nvl(value.trim()).length()>0) {
2797 //                                                      value = "('" + Utils.replaceInString(value, ",", "'|'") + "')";
2798 //                                                      value = Utils.replaceInString(value, "|", ",");
2799 //                                                      paramValue = XSSFilter.filterRequestOnlyScript(value);
2800 //                                              }
2801                                                 paramValue = value;
2802                                         } else
2803                                                 paramValue = Utils.oracleSafe(nvl(paramValues
2804                                                                 .getParamValue(fieldId)));
2805
2806                                         if (paramValue!=null && paramValue.length() > 0) {
2807                         if(paramValue.toLowerCase().trim().startsWith("select ")) {
2808                             paramValue = Utils.replaceInString(paramValue, "[LOGGED_USERID]", userId);
2809                             paramValue = Utils.replaceInString(paramValue, "[USERID]", userId);
2810                             paramValue = Utils.replaceInString(paramValue, "[USER_ID]", userId);
2811
2812                             paramValue = Utils.replaceInString(paramValue, "''", "'");
2813                             ds = ConnectionUtils.getDataSet(paramValue, dbInfo);
2814                             if (ds.getRowCount() > 0) paramValue = ds.getString(0, 0);
2815                         }
2816                         //debugLogger.debug("SQLSQLBASED B4^^^^^^^^^ " + sql + " " + fft.getValidationType() + " " + fft.getFieldName() + " " + fft.getFieldId());
2817                         if(fft!=null && (fft.getValidationType()!=null && (fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) ||fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC) ||fft.getValidationType().equals(FormField.VT_DATE) ))) {
2818                                 //System.out.println("paramValues.getParamValue(fieldId_Hr) Inside if " + fft.getValidationType()  + " " + fieldDisplay);
2819                                 if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)) {
2820                                             sql = Utils.replaceInString(sql, fieldDisplay, nvl(
2821                                                     paramValue) +((nvl(paramValues
2822                                                                                         .getParamValue(fieldId+"_Hr") ).length()>0)?" "+addZero(Utils.oracleSafe(nvl(paramValues
2823                                                                                                         .getParamValue(fieldId+"_Hr") ) ) ):""));
2824                                         }
2825                                         else if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN)) {
2826 /*                                              System.out.println("paramValues.getParamValue(fieldId_Hr)" + paramValues
2827                                                                                 .getParamValue(fieldId+"_Hr") + " " + paramValues
2828                                                                                 .getParamValue(fieldId+"_Min")) ;
2829 */                                                  sql = Utils.replaceInString(sql, fieldDisplay, nvl(
2830                                                             paramValue) + ((nvl(paramValues
2831                                                                                                 .getParamValue(fieldId+"_Hr") ).length()>0)?" "+addZero(Utils.oracleSafe(nvl(paramValues
2832                                                                                                 .getParamValue(fieldId+"_Hr") ) ) ):"") + ((nvl(paramValues
2833                                                                                                                 .getParamValue(fieldId+"_Min") ).length()>0)?":" + addZero(Utils.oracleSafe(nvl(paramValues
2834                                                                                                                 .getParamValue(fieldId+"_Min") ) ) ) : "")              )  ;
2835                                         }
2836                                         else if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
2837                                                     sql = Utils.replaceInString(sql, fieldDisplay, nvl(
2838                                                             paramValue) + ((nvl(paramValues
2839                                                                                                 .getParamValue(fieldId+"_Hr") ).length()>0)?" "+addZero(Utils.oracleSafe(nvl(paramValues
2840                                                                                                                 .getParamValue(fieldId+"_Hr") ) ) ):"") + ((nvl(paramValues
2841                                                                                                                                 .getParamValue(fieldId+"_Min") ).length()>0)?":" + addZero(Utils.oracleSafe(nvl(paramValues
2842                                                                                                                                 .getParamValue(fieldId+"_Min") ) ) ) : "")               + ((nvl(paramValues
2843                                                                                                                                 .getParamValue(fieldId+"_Sec") ).length()>0)?":"+addZero(Utils.oracleSafe(nvl(paramValues
2844                                                                                                                                 .getParamValue(fieldId+"_Sec") ) ) ) : ""               ) ) ;
2845                                         } else {
2846                                                         sql = Utils.replaceInString(sql, fieldDisplay, nvl(
2847                                                                 paramValue, "NULL"));
2848                                         }
2849
2850
2851                         } else {
2852                         if(paramValue!=null && paramValue.length() > 0) {
2853                                 if(sql.indexOf("'"+fieldDisplay+"'")!=-1 || sql.indexOf("'"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"'")!=-1
2854                                                 || sql.indexOf("'%"+fieldDisplay+"%'")!=-1 || sql.indexOf("'%"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"%'")!=-1
2855                                                 || sql.indexOf("'_"+fieldDisplay+"_'")!=-1 || sql.indexOf("'_"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"_'")!=-1
2856                                         || sql.indexOf("'%_"+fieldDisplay+"_%'")!=-1 || sql.indexOf("^"+fieldDisplay+"^")!=-1 || sql.indexOf("'%_"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"_%'")!=-1) {
2857                                           sql = Utils.replaceInString(sql, fieldDisplay, nvl(
2858                                                 paramValue, "NULL"));
2859                                 } else {
2860                                         if(sql.indexOf(fieldDisplay)!=-1) {
2861                                                 if(nvl(paramValue).length()>0) {
2862                                                         try {
2863                                                                 double vD = Double.parseDouble(paramValue);
2864                                                                 sql = Utils.replaceInString(sql, fieldDisplay, nvl(
2865                                                                 paramValue, "NULL"));
2866
2867                                                         } catch (NumberFormatException ex) {
2868                                                                  if (/*dbType.equals("DAYTONA") &&*/ sql.trim().toUpperCase().startsWith("SELECT")) {
2869                                                                                 sql = Utils.replaceInString(sql, fieldDisplay, nvl(
2870                                                                                         paramValue, "NULL"));
2871                                                                  } else
2872                                                                     throw new UserDefinedException("Expected number, Given String for the form field \"" + fieldDisplay+"\"");
2873                                                         }
2874                                                         /*sql = Utils.replaceInString(sql, fieldDisplay, nvl(
2875                                                                 paramValue, "NULL"));*/
2876                                                 } else
2877                                                         sql = Utils.replaceInString(sql, fieldDisplay, nvl(
2878                                                                 paramValue, "NULL"));
2879
2880                                         }
2881                                 }
2882                         }
2883                         else {
2884                                  if (dbType.equals("DAYTONA") && sql.trim().toUpperCase().startsWith("SELECT")) {
2885                                         sql = sql + " ";
2886                                         re1 = Pattern.compile("(^[\r\n]|[\\s]|[^0-9a-zA-Z])AND(.*?[^\r\n]*)"+ "\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL);
2887                                 posFormField = sql.indexOf(fieldDisplay);
2888                                 posAnd = sql.lastIndexOf(" AND ", posFormField);
2889                                 if(posAnd < 0) posAnd = 0;
2890                                 else if (posAnd > 2) posAnd = posAnd - 2;
2891                                         matcher = re1.matcher(sql);
2892                                         if (matcher.find(posAnd)) {
2893                                                 sql = sql.replace(matcher.group(), "");
2894                                         }
2895                                  } else {
2896                                 sql = Utils.replaceInString(sql, "'" + fieldDisplay + "'", nvl(
2897                                     paramValue, "NULL"));
2898                             sql = Utils.replaceInString(sql,  fieldDisplay, nvl(
2899                                     paramValue, "NULL"));
2900                                  }
2901                         }
2902                        }
2903
2904                    }
2905
2906                                  if (dbType.equals("DAYTONA") && sql.trim().toUpperCase().startsWith("SELECT")) {
2907                                         sql = sql + " ";
2908                                         re1 = Pattern.compile("(^[\r\n]|[\\s]|[^0-9a-zA-Z])AND(.*?[^\r\n]*)"+ "\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL); //+[\'\\)|\'|\\s]
2909                                 posFormField = sql.indexOf(fieldDisplay);
2910                                 posAnd = sql.lastIndexOf(" AND ", posFormField);
2911                                 if(posAnd < 0) posAnd = 0;
2912                                 else if (posAnd > 2) posAnd = posAnd - 2;
2913                                         matcher = re1.matcher(sql);
2914                                         if (matcher.find(posAnd)) {
2915                                                 sql = sql.replace(matcher.group(), " ");
2916                                         }
2917                                  } else {
2918                                         if( fft.isGroupFormField()!=null && fft.isGroupFormField().booleanValue()) {
2919                                                 sql = Pattern.compile("[[\\s*][,]]\\["+fft.getFieldName()+"\\](.*?)[,]",Pattern.MULTILINE).matcher(sql).replaceAll(" ");
2920                                                 //sql = Pattern.compile("[,][\\s*]\\["+fft.getFieldName()+"\\][\\s]",Pattern.MULTILINE).matcher(sql).replaceAll(" ");
2921                                                 sql = Pattern.compile("(,.+?)[\\s*]\\["+fft.getFieldName()+"\\][\\s]",Pattern.MULTILINE).matcher(sql).replaceAll(" ");
2922                                                 //sql = Pattern.compile("(?:,?)[\\s*]\\["+fft.getFieldName()+"\\]",Pattern.MULTILINE).matcher(sql).replaceAll("");
2923                                                 //sql = Pattern.compile("[,][\\s*]\\["+fft.getFieldName()+"\\]",Pattern.MULTILINE).matcher(sql).replaceAll(" ");
2924                                                 //sql = Pattern.compile( "\\["+fft.getFieldName()+"\\](.*?[^\r\n]*)[,]",Pattern.DOTALL).matcher(sql).replaceAll("");
2925
2926                                                 //sql = Pattern.compile("[,]|(.*?[^\r\n]*)"+fieldDisplay+"(.*?)\\s",Pattern.DOTALL).matcher(sql).replaceAll("");
2927                                                 //sql = Pattern.compile("(.*?[^\r\n]*)"+fieldDisplay+"(.*?)\\s|[,]",Pattern.DOTALL).matcher(sql).replaceAll("");
2928 /*                                          sql = Utils.replaceInString(sql, "," + fieldDisplay , nvl(
2929                                                     paramValue, ""));
2930                                              sql = Utils.replaceInString(sql,  fieldDisplay + "," , nvl(
2931                                                     paramValue, ""));
2932 */                                      } else {
2933                                                                 //debugLogger.debug("ParamValue |" + paramValue + "| Sql |" + sql  + "| Multi Value |" + paramValues.isParameterMultiValue(fieldId));
2934                                             sql = Utils.replaceInString(sql, "'" + fieldDisplay + "'", nvl(
2935                                                     paramValue, "NULL"));
2936                                              sql = Utils.replaceInString(sql,  fieldDisplay , nvl(
2937                                                     paramValue, "NULL"));
2938                                             //debugLogger.debug("SQLSQLBASED AFTER^^^^^^^^^ " + sql);
2939                                         }
2940                                  }
2941
2942                                 } // else
2943                                 } // if BLANK
2944                         } // for
2945             if(request != null ) {
2946                 for (int i = 0; i < reqParameters.length; i++) {
2947                     if(!reqParameters[i].startsWith("ff")) {
2948                         if (nvl(request.getParameter(reqParameters[i].toUpperCase())).length() > 0)
2949                                 sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i].toUpperCase()) );
2950                     }
2951                     else
2952                       sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i]) );
2953                 }
2954
2955                 for (int i = 0; i < scheduleSessionParameters.length; i++) {
2956                         if(nvl(request.getParameter(scheduleSessionParameters[i])).trim().length()>0 )
2957                                 sql = Utils.replaceInString(sql, "[" + scheduleSessionParameters[i].toUpperCase()+"]", request.getParameter(scheduleSessionParameters[i]) );
2958                                 }
2959              }
2960             if(session != null ) {
2961                 for (int i = 0; i < sessionParameters.length; i++) {
2962                     //if(!sessionParameters[i].startsWith("ff"))
2963                      // paramValue = Utils.replaceInString(paramValue, "[" + sessionParameters[i].toUpperCase()+"]", (String)session.getAttribute(sessionParameters[i].toUpperCase()) );
2964                    // else {
2965                       //debugLogger.debug(" Session " + " sessionParameters[i] " + sessionParameters[i] + " " + (String)session.getAttribute(sessionParameters[i]));
2966                       sql = Utils.replaceInString(sql, "[" + sessionParameters[i].toUpperCase()+"]", (String)session.getAttribute(sessionParameters[i]) );
2967                     //}
2968                 }
2969              }
2970                 } else {
2971         //debugLogger.debug("BEFORE LOGGED USERID REPLACE " + sql);
2972         //sql = Utils.replaceInString(sql, "'[logged_userId]'", "'"+userId+"'");
2973         //debugLogger.debug("Replacing string 2 " + sql);
2974         sql = Utils.replaceInString(sql, "[LOGGED_USERID]", userId);
2975         sql = Utils.replaceInString(sql, "[USERID]", userId);
2976         sql = Utils.replaceInString(sql, "[USER_ID]", userId);
2977         //debugLogger.debug("AFTER LOGGED USERID REPLACE " + sql);
2978         // Added for Simon's GM Project where they need to get page_id in their query
2979         //debugLogger.debug("SQLSQLBASED no formfields " + sql);
2980         if(request != null ) {
2981            for (int i = 0; i < reqParameters.length; i++) {
2982              sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i]) );
2983            }
2984         }
2985         if(session != null ) {
2986             for (int i = 0; i < sessionParameters.length; i++) {
2987                 //debugLogger.debug(" Session " + " sessionParameters[i] " + sessionParameters[i] + " " + (String)session.getAttribute(sessionParameters[i]));
2988               sql = Utils.replaceInString(sql, "[" + sessionParameters[i].toUpperCase()+"]", (String)session.getAttribute(sessionParameters[i]) );
2989             }
2990          }
2991                 }
2992                 // if it is not multiple select and ParamValue is empty this is the place it can be replaced.
2993                 sql = Utils.replaceInString(sql, "[LOGGED_USERID]", userId);
2994                 sql = Utils.replaceInString(sql, "[USERID]", userId);
2995                 sql = Utils.replaceInString(sql, "[USER_ID]", userId);
2996         //debugLogger.debug("SQLSQLBASED no formfields after"  + sql);
2997         //debugLogger.debug("Replacing String 2 "+ sql);
2998         //debugLogger.debug("Replaced String " + sql);
2999
3000         int closeBracketPos = 0;
3001                 if (nvl(overrideSortByColId).length() > 0) {
3002                         if(sql.lastIndexOf(")")!= -1) closeBracketPos = sql.lastIndexOf(")");
3003                         int idxOrderBy = (closeBracketPos>0)?sql.toUpperCase().indexOf("ORDER BY", closeBracketPos):sql.toUpperCase().lastIndexOf("ORDER BY");
3004                         DataColumnType dct = getColumnById(overrideSortByColId+"_sort");
3005                         if(dct!=null && dct.getColName().length()>0) {
3006                                 overrideSortByColId = overrideSortByColId+"_sort";
3007                         }
3008                         if (idxOrderBy < 0)
3009                                 sql += " ORDER BY " + overrideSortByColId + " " + overrideSortByAscDesc;
3010                         else {
3011                                 int braketCount = 0;
3012                                 int idxOrderByClauseEnd = 0;
3013                                 for (idxOrderByClauseEnd = idxOrderBy; idxOrderByClauseEnd < sql.length(); idxOrderByClauseEnd++) {
3014                                         char ch = sql.charAt(idxOrderByClauseEnd);
3015
3016                                         if (ch == '(')
3017                                                 braketCount++;
3018                                         else if (ch == ')') {
3019                                                 if (braketCount == 0)
3020                                                         break;
3021                                                 braketCount--;
3022                                         }
3023                                 } // for
3024
3025                                 sql = sql.substring(0, idxOrderBy) + " ORDER BY " + overrideSortByColId + " "
3026                                                 + overrideSortByAscDesc + sql.substring(idxOrderByClauseEnd);
3027                         } // else
3028                 } // if
3029                 sql = Pattern.compile("([\n][\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" ");
3030                 return sql;
3031         } // generateSQLSQLBased
3032
3033         public String generateSQLVisual(ReportParamValues paramValues, String overrideSortByColId,
3034                         String overrideSortByAscDesc, String userId, HttpServletRequest request)throws RaptorException  {
3035                 StringBuffer selectCl = new StringBuffer();
3036                 StringBuffer fromCl = new StringBuffer();
3037                 StringBuffer whereCl = new StringBuffer();
3038                 StringBuffer groupByCl = new StringBuffer();
3039                 StringBuffer havingCl = new StringBuffer();
3040                 StringBuffer orderByCl = new StringBuffer();
3041                 StringBuffer selectExtraIdCl = new StringBuffer();
3042                 StringBuffer selectExtraDateCl = new StringBuffer();
3043
3044                 int whereClBracketCount = 0;
3045                 int havingClBracketCount = 0;
3046                 int whereClCarryoverBrackets = 0;
3047                 int havingClCarryoverBrackets = 0;
3048
3049                 // Identifying FROM clause tables and WHERE clause joins
3050                 List dsList = getDataSourceList().getDataSource();
3051                 for (Iterator iter = dsList.iterator(); iter.hasNext();) {
3052                         DataSourceType ds = (DataSourceType) iter.next();
3053
3054                         if (fromCl.length() > 0)
3055                                 fromCl.append(", ");
3056                         fromCl.append(ds.getTableName());
3057                         fromCl.append(" ");
3058                         fromCl.append(ds.getTableId());
3059
3060                         if (nvl(ds.getRefTableId()).length() > 0) {
3061                                 if (whereCl.length() > 0)
3062                                         whereCl.append(" AND ");
3063                                 whereCl.append(ds.getRefDefinition());
3064                         } // if
3065                         // Add the condition.
3066                         TableSource tableSource = null;
3067                         String dBInfo = this.cr.getDbInfo();
3068                         Vector userRoles = AppUtils.getUserRoles(request);
3069                         tableSource = DataCache.getTableSource(ds.getTableName(), dBInfo,userRoles,userId, request);
3070                         if (userId != null && (!AppUtils.isSuperUser(request))
3071                                         && (!AppUtils.isAdminUser(request)) && tableSource != null
3072                                         && nvl(tableSource.getFilterSql()).length() > 0) {
3073                                 if (whereCl.length() > 0)
3074                                         whereCl.append(" AND ");
3075                                 whereCl.append(Utils.replaceInString(Utils.replaceInString(tableSource
3076                                                 .getFilterSql(), "[" + ds.getTableName() + "]", ds.getTableId()),
3077                                                 "[USER_ID]", userId));
3078                         } // if
3079                 } // for
3080
3081                 List reportCols = getAllColumns();
3082
3083                 boolean isGroupStmt = false;
3084                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3085                         DataColumnType dc = (DataColumnType) iter.next();
3086                         if (dc.isGroupBreak()) {
3087                                 isGroupStmt = true;
3088                                 break;
3089                         } // if
3090                 } // for
3091
3092                 // Identifying SELECT and GROUP BY clause fields and WHERE and HAVING
3093                 // clause filters
3094                 // Collections.sort(reportCols, new OrderSeqComparator());
3095                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3096                         DataColumnType dc = (DataColumnType) iter.next();
3097                         String colName = getColumnSelectStr(dc, paramValues);
3098
3099                         // SELECT clause fields
3100                         //TODO: Uncomment if it's not working -- if (dc.isVisible()) {
3101                                 if (selectCl.length() > 0)
3102                                         selectCl.append(", ");
3103                                 selectCl.append(getSelectExpr(dc, colName));
3104                                 selectCl.append(" ");
3105                                 selectCl.append(dc.getColId());
3106                         //TODO } // if
3107
3108                         // Checking for extra fields necessary for drill-down
3109                         if (nvl(dc.getDrillDownURL()).length() > 0)
3110                                 if (isViewAction(dc.getDrillDownURL()))
3111                                         addExtraIdSelect(selectExtraIdCl, nvl(dc.getDrillDownParams()), true);
3112                                 else
3113                                         addExtraDateSelect(selectExtraDateCl, nvl(dc.getDrillDownParams()),
3114                                                         paramValues, true);
3115
3116                         // GROUP BY clause fields
3117                         if (dc.isGroupBreak()) {
3118                                 if (groupByCl.length() > 0)
3119                                         groupByCl.append(", ");
3120                                 groupByCl.append(colName);
3121                         } // if
3122
3123                         // WHERE/HAVING clause fields
3124                         //boolean isHavingCl = isGroupStmt && dc.isVisible() && (!dc.isGroupBreak());
3125             boolean isHavingCl = isGroupStmt && (!dc.isGroupBreak());
3126                         StringBuffer filterCl = isHavingCl ? havingCl : whereCl;
3127                         // StringBuffer filterCl =
3128                         // isGroupStmt?(dc.isVisible()?(dc.isGroupBreak()?whereCl:havingCl):whereCl):whereCl;
3129                         if (dc.getColFilterList() != null) {
3130                                 int fNo = 0;
3131                                 List fList = dc.getColFilterList().getColFilter();
3132                                 for (Iterator iterF = fList.iterator(); iterF.hasNext(); fNo++) {
3133                                         ColFilterType cf = (ColFilterType) iterF.next();
3134
3135                                         StringBuffer curFilter = new StringBuffer();
3136                                         if (filterCl.length() > 0)
3137                                                 curFilter.append(" " + cf.getJoinCondition() + " ");
3138                                         if ((isHavingCl ? havingClCarryoverBrackets : whereClCarryoverBrackets) > 0)
3139                                                 for (int b = 0; b < (isHavingCl ? havingClCarryoverBrackets
3140                                                                 : whereClCarryoverBrackets); b++)
3141                                                         filterCl.append('(');
3142                                         curFilter.append(nvl(cf.getOpenBrackets()));
3143                                         curFilter.append(colName + " ");
3144                                         curFilter.append(cf.getExpression() + " ");
3145
3146                                         boolean applyFilter = true;
3147                                         if ((nvl(cf.getArgValue()).length() > 0)
3148                                                         || (nvl(cf.getArgType()).equals(AppConstants.AT_FORM)))
3149                                                 if (nvl(cf.getArgType()).equals(AppConstants.AT_FORMULA))
3150                                                         curFilter.append(cf.getArgValue());
3151                                                 else if (nvl(cf.getArgType()).equals(AppConstants.AT_VALUE))
3152                                                         curFilter.append(formatValue(cf.getArgValue(), dc, false));
3153                                                 else if (nvl(cf.getArgType()).equals(AppConstants.AT_LIST))
3154                                                         curFilter.append(formatListValue(cf.getArgValue(), dc, false,
3155                                                                         false));
3156                                                 else if (nvl(cf.getArgType()).equals(AppConstants.AT_COLUMN))
3157                                                         curFilter.append(getColumnNameById(cf.getArgValue()));
3158                                                 else if (nvl(cf.getArgType()).equals(AppConstants.AT_FORM)) {
3159                                                         String fieldName = getFormFieldName(cf);
3160                                                         String fieldValue = Utils.oracleSafe(paramValues
3161                                                                         .getParamValue(fieldName));
3162                                                         boolean isMultiValue = paramValues
3163                                                                         .isParameterMultiValue(fieldName);
3164                                                         boolean usePipeDelimiterOnly = false;
3165
3166                                                         FormFieldType fft = getFormFieldByDisplayValue(cf.getArgValue());
3167                                                         if (fft == null)
3168                                                                 // If not FormField => applying default value
3169                                                                 fieldValue = nvl(fieldValue, Utils
3170                                                                                 .oracleSafe(cf.getArgValue()));
3171                                                         else
3172                                                                 usePipeDelimiterOnly = fft.getFieldType().equals(
3173                                                                                 FormField.FFT_CHECK_BOX)
3174                                                                                 || fft.getFieldType().equals(FormField.FFT_LIST_MULTI);
3175                                                         //Added for TimeStamp validation
3176                                                         String fieldId = fft.getFieldId();
3177                                 if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)||fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN)||fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
3178                                     fieldValue = nvl(
3179                                                 fieldValue + " " + addZero(Utils.oracleSafe(nvl(paramValues
3180                                                                                 .getParamValue(fieldId+"_Hr") ) ) ) )  ;
3181                                     if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
3182                                         fieldValue = fieldValue + (nvl(paramValues
3183                                                                                         .getParamValue(fieldId+"_Min")).length()>0 ? ":" + addZero(Utils.oracleSafe(nvl(paramValues
3184                                                                                                 .getParamValue(fieldId+"_Min")))): "") ;
3185                                     }
3186                                     if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
3187                                         fieldValue = fieldValue + (nvl(paramValues
3188                                                                                         .getParamValue(fieldId+"_Sec")).length()>0 ? ":"+ addZero(Utils.oracleSafe(nvl(paramValues
3189                                                                                                                 .getParamValue(fieldId+"_Sec")))) : "");
3190                                         }
3191                                 }
3192
3193                                                 // End
3194                                                         if (nvl(fieldValue).length() == 0)
3195                                                                 // Does not append filter with missing form
3196                                                                 // field argument
3197                                                                 applyFilter = false;
3198                                                         else if (isMultiValue || nvl(cf.getExpression()).equals("IN")
3199                                                                         || nvl(cf.getExpression()).equals("NOT IN"))
3200                                                                 curFilter.append(formatListValue(fieldValue, dc, true,
3201                                                                                 usePipeDelimiterOnly));
3202                                                         else
3203                                                                 curFilter.append(formatValue(fieldValue, dc, true, null, fft));
3204                                                 } // else
3205                                         curFilter.append(nvl(cf.getCloseBrackets()));
3206
3207                                         if (applyFilter) {
3208                                                 filterCl.append(curFilter.toString());
3209
3210                                                 if (isHavingCl) {
3211                                                         havingClBracketCount += (nvl(cf.getOpenBrackets()).length() - nvl(
3212                                                                         cf.getCloseBrackets()).length());
3213                                                         havingClCarryoverBrackets = 0;
3214                                                 } else {
3215                                                         whereClBracketCount += (nvl(cf.getOpenBrackets()).length() - nvl(
3216                                                                         cf.getCloseBrackets()).length());
3217                                                         whereClCarryoverBrackets = 0;
3218                                                 }
3219                                         } else if (nvl(cf.getOpenBrackets()).length() != nvl(cf.getCloseBrackets())
3220                                                         .length())
3221                                                 if (nvl(cf.getOpenBrackets()).length() > nvl(cf.getCloseBrackets())
3222                                                                 .length()) {
3223                                                         // Carry over opening brackets
3224                                                         if (isHavingCl)
3225                                                                 havingClCarryoverBrackets += (nvl(cf.getOpenBrackets())
3226                                                                                 .length() - nvl(cf.getCloseBrackets()).length());
3227                                                         else
3228                                                                 whereClCarryoverBrackets += (nvl(cf.getOpenBrackets())
3229                                                                                 .length() - nvl(cf.getCloseBrackets()).length());
3230
3231                                                         if (isHavingCl)
3232                                                                 havingClBracketCount += (nvl(cf.getOpenBrackets()).length() - nvl(
3233                                                                                 cf.getCloseBrackets()).length());
3234                                                         else
3235                                                                 whereClBracketCount += (nvl(cf.getOpenBrackets()).length() - nvl(
3236                                                                                 cf.getCloseBrackets()).length());
3237                                                 } else {
3238                                                         // Adding closing brackets
3239                                                         if (filterCl.length() > 0) {
3240                                                                 for (int b = 0; b < nvl(cf.getCloseBrackets()).length()
3241                                                                                 - nvl(cf.getOpenBrackets()).length(); b++)
3242                                                                         filterCl.append(')');
3243
3244                                                                 if (isHavingCl)
3245                                                                         havingClBracketCount += (nvl(cf.getOpenBrackets())
3246                                                                                         .length() - nvl(cf.getCloseBrackets()).length());
3247                                                                 else
3248                                                                         whereClBracketCount += (nvl(cf.getOpenBrackets()).length() - nvl(
3249                                                                                         cf.getCloseBrackets()).length());
3250                                                         } // if
3251                                                 } // else
3252                                 } // for
3253                         } // if
3254                 } // for
3255
3256                 // Identifying ORDER BY clause fields
3257                 DataColumnType overrideSortByCol = null;
3258                 if (overrideSortByColId != null)
3259                         overrideSortByCol = getColumnById(overrideSortByColId);
3260
3261                 if (overrideSortByCol != null) {
3262                         orderByCl.append(getColumnSelectStr(overrideSortByCol, paramValues));
3263                         orderByCl.append(" ");
3264                         orderByCl.append(nvl(overrideSortByAscDesc, AppConstants.SO_ASC));
3265                 } else if (getReportType().equals(AppConstants.RT_CROSSTAB)) {
3266                         /*
3267                          * for(Iterator iter=reportCols.iterator(); iter.hasNext(); ) {
3268                          * DataColumnType dc = (DataColumnType) iter.next();
3269                          *
3270                          * if(nvl(dc.getCrossTabValue()).equals(AppConstants.CV_ROW)||nvl(dc.getCrossTabValue()).equals(AppConstants.CV_COLUMN)) {
3271                          * if(orderByCl.length()>0) orderByCl.append(", ");
3272                          * orderByCl.append(getColumnSelectStr(dc, paramValues));
3273                          * orderByCl.append(" ");
3274                          * if(dc.getColType().equals(AppConstants.CT_DATE))
3275                          * orderByCl.append(AppConstants.SO_DESC); else
3276                          * orderByCl.append(AppConstants.SO_ASC); } // if } // for
3277                          */
3278                 } else {
3279                         Collections.sort(reportCols, new OrderBySeqComparator());
3280                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3281                                 DataColumnType dc = (DataColumnType) iter.next();
3282
3283                                 if (dc.getOrderBySeq() > 0) {
3284                                         if (orderByCl.length() > 0)
3285                                                 orderByCl.append(", ");
3286                                         orderByCl.append(getColumnSelectStr(dc, paramValues));
3287                                         orderByCl.append(" ");
3288                                         orderByCl.append(dc.getOrderByAscDesc());
3289                                 } // if
3290                         } // for
3291                         Collections.sort(reportCols, new OrderSeqComparator());
3292                 } // else
3293
3294                 // Adding up the actual statement
3295                 StringBuffer sql = new StringBuffer();
3296                 //sql.append("SELECT "); // Need to add PK for /*+ FIRST_ROWS */ ");
3297                 sql.append(Globals.getGenerateSqlVisualSelect());
3298                 //sql.append((selectCl.length() == 0) ? "COUNT(*) cnt" : selectCl.toString());
3299                 sql.append((selectCl.length() == 0) ? Globals.getGenerateSqlVisualCount() : selectCl.toString());
3300                 if (groupByCl.length() == 0)
3301                         sql.append(selectExtraIdCl.toString());
3302                 sql.append(selectExtraDateCl.toString());
3303         //      sql.append(" FROM ");
3304                 sql.append((fromCl.length() == 0) ? Globals.getGenerateSqlVisualDual() : "FROM "+fromCl.toString());
3305                 if (whereCl.length() > 0) {
3306                         if (whereClBracketCount > 0) {
3307                                 for (int b = 0; b < whereClBracketCount; b++)
3308                                         whereCl.append(')');
3309                         } else if (whereClBracketCount < 0) {
3310                                 for (int b = 0; b < Math.abs(whereClBracketCount); b++)
3311                                         whereCl.insert(0, '(');
3312                         } // else
3313
3314                         sql.append(" WHERE ");
3315                         sql.append(whereCl.toString());
3316                 } // if
3317                 if (groupByCl.length() > 0) {
3318                         sql.append(" GROUP BY ");
3319                         sql.append(groupByCl.toString());
3320
3321                         if (havingCl.length() > 0) {
3322                                 if (havingClBracketCount > 0) {
3323                                         for (int b = 0; b < havingClBracketCount; b++)
3324                                                 havingCl.append(')');
3325                                 } else if (havingClBracketCount < 0) {
3326                                         for (int b = 0; b < Math.abs(havingClBracketCount); b++)
3327                                                 havingCl.insert(0, '(');
3328                                 } // else
3329
3330                                 sql.append(" HAVING ");
3331                                 sql.append(havingCl.toString());
3332                         }
3333                 }
3334                 if (orderByCl.length() > 0) {
3335                         sql.append(" ORDER BY ");
3336                         sql.append(orderByCl.toString());
3337                 }
3338         //String sqlStr = Utils.replaceInString(sql.toString(), "[LOGGED_USERID]", userId);
3339                 //return sqlStr;
3340         return sql.toString();
3341         } // generateSQLVisual
3342
3343     public String generateSQLCrossTabVisual(ReportParamValues paramValues, String overrideSortByColId,
3344             String overrideSortByAscDesc, String userId, HttpServletRequest request) throws RaptorException  {
3345         StringBuffer selectCl = new StringBuffer();
3346         StringBuffer fromCl = new StringBuffer();
3347         StringBuffer whereCl = new StringBuffer();
3348         StringBuffer groupByCl = new StringBuffer();
3349         StringBuffer havingCl = new StringBuffer();
3350         StringBuffer orderByCl = new StringBuffer();
3351         StringBuffer selectExtraIdCl = new StringBuffer();
3352         StringBuffer selectExtraDateCl = new StringBuffer();
3353
3354         int whereClBracketCount = 0;
3355         int havingClBracketCount = 0;
3356         int whereClCarryoverBrackets = 0;
3357         int havingClCarryoverBrackets = 0;
3358
3359         // Identifying FROM clause tables and WHERE clause joins
3360         List dsList = getDataSourceList().getDataSource();
3361         for (Iterator iter = dsList.iterator(); iter.hasNext();) {
3362             DataSourceType ds = (DataSourceType) iter.next();
3363
3364             if (fromCl.length() > 0)
3365                 fromCl.append(", ");
3366             fromCl.append(ds.getTableName());
3367             fromCl.append(" ");
3368             fromCl.append(ds.getTableId());
3369
3370             if (nvl(ds.getRefTableId()).length() > 0) {
3371                 if (whereCl.length() > 0)
3372                     whereCl.append(" AND ");
3373                 whereCl.append(ds.getRefDefinition());
3374             } // if
3375             // Add the condition.
3376             TableSource tableSource = null;
3377             String dBInfo = this.cr.getDbInfo();
3378             Vector userRoles = AppUtils.getUserRoles(request);
3379             tableSource = DataCache.getTableSource(ds.getTableName(), dBInfo,userRoles,userId, request);
3380             if (userId != null && (!AppUtils.isSuperUser(request))
3381                     && (!AppUtils.isAdminUser(request)) && tableSource != null
3382                     && nvl(tableSource.getFilterSql()).length() > 0) {
3383                 if (whereCl.length() > 0)
3384                     whereCl.append(" AND ");
3385                 whereCl.append(Utils.replaceInString(Utils.replaceInString(tableSource
3386                         .getFilterSql(), "[" + ds.getTableName() + "]", ds.getTableId()),
3387                         "[USER_ID]", userId));
3388             } // if
3389         } // for
3390
3391         List reportCols = getAllColumns();
3392
3393         boolean isGroupStmt = false;
3394         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3395             DataColumnType dc = (DataColumnType) iter.next();
3396             if (dc.isGroupBreak()) {
3397                 isGroupStmt = true;
3398                 break;
3399             } // if
3400         } // for
3401
3402         // Identifying SELECT and GROUP BY clause fields and WHERE and HAVING
3403         // clause filters
3404         // Collections.sort(reportCols, new OrderSeqComparator());
3405         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3406             DataColumnType dc = (DataColumnType) iter.next();
3407             String colName = getColumnSelectStr(dc, paramValues);
3408
3409             // SELECT clause fields
3410             if (dc.isVisible()) {
3411                 if (selectCl.length() > 0)
3412                     selectCl.append(", ");
3413                 selectCl.append(getSelectExpr(dc, colName));
3414                 selectCl.append(" ");
3415                 selectCl.append(dc.getColId());
3416              } // if
3417
3418             // Checking for extra fields necessary for drill-down
3419             if (nvl(dc.getDrillDownURL()).length() > 0)
3420                 if (isViewAction(dc.getDrillDownURL()))
3421                     addExtraIdSelect(selectExtraIdCl, nvl(dc.getDrillDownParams()), true);
3422                 else
3423                     addExtraDateSelect(selectExtraDateCl, nvl(dc.getDrillDownParams()),
3424                             paramValues, true);
3425
3426             // GROUP BY clause fields
3427             if (dc.isGroupBreak()) {
3428                 if (groupByCl.length() > 0)
3429                     groupByCl.append(", ");
3430                 groupByCl.append(colName);
3431             } // if
3432
3433             // WHERE/HAVING clause fields
3434             boolean isHavingCl = isGroupStmt && dc.isVisible() && (!dc.isGroupBreak());
3435             //boolean isHavingCl = isGroupStmt && (!dc.isGroupBreak());
3436             //StringBuffer filterCl = isHavingCl ? havingCl : whereCl;
3437              StringBuffer filterCl =
3438              isGroupStmt?(dc.isVisible()?(dc.isGroupBreak()?whereCl:havingCl):whereCl):whereCl;
3439             if (dc.getColFilterList() != null) {
3440                 int fNo = 0;
3441                 List fList = dc.getColFilterList().getColFilter();
3442                 for (Iterator iterF = fList.iterator(); iterF.hasNext(); fNo++) {
3443                     ColFilterType cf = (ColFilterType) iterF.next();
3444
3445                     StringBuffer curFilter = new StringBuffer();
3446                     if (filterCl.length() > 0)
3447                         curFilter.append(" " + cf.getJoinCondition() + " ");
3448                     if ((isHavingCl ? havingClCarryoverBrackets : whereClCarryoverBrackets) > 0)
3449                         for (int b = 0; b < (isHavingCl ? havingClCarryoverBrackets
3450                                 : whereClCarryoverBrackets); b++)
3451                             filterCl.append('(');
3452                     curFilter.append(nvl(cf.getOpenBrackets()));
3453                     curFilter.append(colName + " ");
3454                     curFilter.append(cf.getExpression() + " ");
3455
3456                     boolean applyFilter = true;
3457                     if ((nvl(cf.getArgValue()).length() > 0)
3458                             || (nvl(cf.getArgType()).equals(AppConstants.AT_FORM)))
3459                         if (nvl(cf.getArgType()).equals(AppConstants.AT_FORMULA))
3460                             curFilter.append(cf.getArgValue());
3461                         else if (nvl(cf.getArgType()).equals(AppConstants.AT_VALUE))
3462                             curFilter.append(formatValue(cf.getArgValue(), dc, false));
3463                         else if (nvl(cf.getArgType()).equals(AppConstants.AT_LIST))
3464                             curFilter.append(formatListValue(cf.getArgValue(), dc, false,
3465                                     false));
3466                         else if (nvl(cf.getArgType()).equals(AppConstants.AT_COLUMN))
3467                             curFilter.append(getColumnNameById(cf.getArgValue()));
3468                         else if (nvl(cf.getArgType()).equals(AppConstants.AT_FORM)) {
3469                             String fieldName = getFormFieldName(cf);
3470                             String fieldValue = Utils.oracleSafe(paramValues
3471                                     .getParamValue(fieldName));
3472                             boolean isMultiValue = paramValues
3473                                     .isParameterMultiValue(fieldName);
3474                             boolean usePipeDelimiterOnly = false;
3475
3476                             FormFieldType fft = getFormFieldByDisplayValue(cf.getArgValue());
3477                             if (fft == null)
3478                                 // If not FormField => applying default value
3479                                 fieldValue = nvl(fieldValue, Utils
3480                                         .oracleSafe(cf.getArgValue()));
3481                             else
3482                                 usePipeDelimiterOnly = fft.getFieldType().equals(
3483                                         FormField.FFT_CHECK_BOX)
3484                                         || fft.getFieldType().equals(FormField.FFT_LIST_MULTI);
3485
3486                             if (nvl(fieldValue).length() == 0)
3487                                 // Does not append filter with missing form
3488                                 // field argument
3489                                 applyFilter = false;
3490                             else if (isMultiValue || nvl(cf.getExpression()).equals("IN")
3491                                     || nvl(cf.getExpression()).equals("NOT IN"))
3492                                 curFilter.append(formatListValue(fieldValue, dc, true,
3493                                         usePipeDelimiterOnly));
3494                             else
3495                                 curFilter.append(formatValue(fieldValue, dc, true));
3496                         } // else
3497                     curFilter.append(nvl(cf.getCloseBrackets()));
3498
3499                     if (applyFilter) {
3500                         filterCl.append(curFilter.toString());
3501
3502                         if (isHavingCl) {
3503                             havingClBracketCount += (nvl(cf.getOpenBrackets()).length() - nvl(
3504                                     cf.getCloseBrackets()).length());
3505                             havingClCarryoverBrackets = 0;
3506                         } else {
3507                             whereClBracketCount += (nvl(cf.getOpenBrackets()).length() - nvl(
3508                                     cf.getCloseBrackets()).length());
3509                             whereClCarryoverBrackets = 0;
3510                         }
3511                     } else if (nvl(cf.getOpenBrackets()).length() != nvl(cf.getCloseBrackets())
3512                             .length())
3513                         if (nvl(cf.getOpenBrackets()).length() > nvl(cf.getCloseBrackets())
3514                                 .length()) {
3515                             // Carry over opening brackets
3516                             if (isHavingCl)
3517                                 havingClCarryoverBrackets += (nvl(cf.getOpenBrackets())
3518                                         .length() - nvl(cf.getCloseBrackets()).length());
3519                             else
3520                                 whereClCarryoverBrackets += (nvl(cf.getOpenBrackets())
3521                                         .length() - nvl(cf.getCloseBrackets()).length());
3522
3523                             if (isHavingCl)
3524                                 havingClBracketCount += (nvl(cf.getOpenBrackets()).length() - nvl(
3525                                         cf.getCloseBrackets()).length());
3526                             else
3527                                 whereClBracketCount += (nvl(cf.getOpenBrackets()).length() - nvl(
3528                                         cf.getCloseBrackets()).length());
3529                         } else {
3530                             // Adding closing brackets
3531                             if (filterCl.length() > 0) {
3532                                 for (int b = 0; b < nvl(cf.getCloseBrackets()).length()
3533                                         - nvl(cf.getOpenBrackets()).length(); b++)
3534                                     filterCl.append(')');
3535
3536                                 if (isHavingCl)
3537                                     havingClBracketCount += (nvl(cf.getOpenBrackets())
3538                                             .length() - nvl(cf.getCloseBrackets()).length());
3539                                 else
3540                                     whereClBracketCount += (nvl(cf.getOpenBrackets()).length() - nvl(
3541                                             cf.getCloseBrackets()).length());
3542                             } // if
3543                         } // else
3544                 } // for
3545             } // if
3546         } // for
3547
3548         // Identifying ORDER BY clause fields
3549         DataColumnType overrideSortByCol = null;
3550         if (overrideSortByColId != null)
3551             overrideSortByCol = getColumnById(overrideSortByColId);
3552
3553         if (overrideSortByCol != null) {
3554             orderByCl.append(getColumnSelectStr(overrideSortByCol, paramValues));
3555             orderByCl.append(" ");
3556             orderByCl.append(nvl(overrideSortByAscDesc, AppConstants.SO_ASC));
3557         } else if (getReportType().equals(AppConstants.RT_CROSSTAB)) {
3558             /*
3559              * for(Iterator iter=reportCols.iterator(); iter.hasNext(); ) {
3560              * DataColumnType dc = (DataColumnType) iter.next();
3561              *
3562              * if(nvl(dc.getCrossTabValue()).equals(AppConstants.CV_ROW)||nvl(dc.getCrossTabValue()).equals(AppConstants.CV_COLUMN)) {
3563              * if(orderByCl.length()>0) orderByCl.append(", ");
3564              * orderByCl.append(getColumnSelectStr(dc, paramValues));
3565              * orderByCl.append(" ");
3566              * if(dc.getColType().equals(AppConstants.CT_DATE))
3567              * orderByCl.append(AppConstants.SO_DESC); else
3568              * orderByCl.append(AppConstants.SO_ASC); } // if } // for
3569              */
3570         } else {
3571             Collections.sort(reportCols, new OrderBySeqComparator());
3572             for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3573                 DataColumnType dc = (DataColumnType) iter.next();
3574
3575                 if (dc.getOrderBySeq() > 0) {
3576                     if (orderByCl.length() > 0)
3577                         orderByCl.append(", ");
3578                     orderByCl.append(getColumnSelectStr(dc, paramValues));
3579                     orderByCl.append(" ");
3580                     orderByCl.append(dc.getOrderByAscDesc());
3581                 } // if
3582             } // for
3583             Collections.sort(reportCols, new OrderSeqComparator());
3584         } // else
3585
3586         // Adding up the actual statement
3587         StringBuffer sql = new StringBuffer();
3588         //sql.append("SELECT "); // Need to add PK for /*+ FIRST_ROWS */ ");
3589         sql.append(Globals.getGenerateSqlVisualSelect());
3590      //   sql.append((selectCl.length() == 0) ? "COUNT(*) cnt" : selectCl.toString());
3591         sql.append((selectCl.length() == 0) ? Globals.getGenerateSqlVisualCount() : selectCl.toString());
3592         if (groupByCl.length() == 0)
3593             sql.append(selectExtraIdCl.toString());
3594         sql.append(selectExtraDateCl.toString());
3595        // sql.append(" FROM ");
3596         sql.append((fromCl.length() == 0) ? Globals.getGenerateSqlVisualDual() : "FROM "+fromCl.toString());
3597         if (whereCl.length() > 0) {
3598             if (whereClBracketCount > 0) {
3599                 for (int b = 0; b < whereClBracketCount; b++)
3600                     whereCl.append(')');
3601             } else if (whereClBracketCount < 0) {
3602                 for (int b = 0; b < Math.abs(whereClBracketCount); b++)
3603                     whereCl.insert(0, '(');
3604             } // else
3605
3606             sql.append(" WHERE ");
3607             sql.append(whereCl.toString());
3608         } // if
3609         if (groupByCl.length() > 0) {
3610             sql.append(" GROUP BY ");
3611             sql.append(groupByCl.toString());
3612
3613             if (havingCl.length() > 0) {
3614                 if (havingClBracketCount > 0) {
3615                     for (int b = 0; b < havingClBracketCount; b++)
3616                         havingCl.append(')');
3617                 } else if (havingClBracketCount < 0) {
3618                     for (int b = 0; b < Math.abs(havingClBracketCount); b++)
3619                         havingCl.insert(0, '(');
3620                 } // else
3621
3622                 sql.append(" HAVING ");
3623                 sql.append(havingCl.toString());
3624             }
3625         }
3626         if (orderByCl.length() > 0) {
3627             sql.append(" ORDER BY ");
3628             sql.append(orderByCl.toString());
3629         }
3630
3631         System.out.println("Created SQL statement: "+sql);
3632
3633         //String sqlStr = Utils.replaceInString(sql.toString(), "[LOGGED_USERID]", userId);
3634         //return sqlStr;
3635         return sql.toString();
3636     } // generateSQLCrossTabVisual
3637
3638
3639         public String generatePagedSQL(int pageNo, String userId, HttpServletRequest request, boolean getColumnNamesFromReportSQL, ReportParamValues paramValues) throws RaptorException  {
3640                 int counter = 0;
3641                 if(!Globals.isMySQL())
3642                         counter = 1;
3643                 return generateSubsetSQL(pageNo * getPageSize() + counter, ((pageNo + 1) * getPageSize())
3644                                 + ((pageNo == 0) ? 1 : 0), userId, request, getColumnNamesFromReportSQL, paramValues);
3645         } // generatePagedSQL
3646
3647         public String generateSubsetSQL(int startRow, int endRow, String userId, HttpServletRequest request, boolean getColumnNamesFromReportSQL, ReportParamValues paramValues) throws RaptorException  {
3648         //debugLogger.debug(" ******** End Row ********* " + endRow);
3649                 String dbInfo = getDBInfo();
3650                  String dbType = "";
3651                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
3652                         try {
3653                          org.openecomp.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.openecomp.portalsdk.analytics.util.RemDbInfo();
3654                          dbType = remDbInfo.getDBType(dbInfo);
3655                         } catch (Exception ex) {
3656                            throw new RaptorException(ex);
3657                                 }
3658                 }
3659                 List reportCols = getAllColumns();
3660                 String wholeSQL_OrderBy = getWholeSQL();
3661                 String reportSQL = getWholeSQL();
3662                 reportSQL = reportSQL.replace(";", "");
3663                 setWholeSQL(reportSQL);
3664                 if(nvl(reportSQL).length()>0)
3665                         reportSQL = generateSQL(userId, request);
3666
3667                 if (reportSQL.toUpperCase().indexOf("ORDER BY ") < 0) {
3668                         StringBuffer sortBy = null;
3669
3670                         if (reportSQL.toUpperCase().indexOf("GROUP BY ") < 0)
3671                                 if (getDataSourceList().getDataSource().size() > 0) {
3672                                         DataSourceType dst = (DataSourceType) getDataSourceList().getDataSource()
3673                                                         .get(0);
3674                                         String tId = dst.getTableId();
3675                                         String tPK = dst.getTablePK();
3676                                         if (nvl(tPK).length() > 0) {
3677                                                 sortBy = new StringBuffer();
3678                                                 StringTokenizer st = new StringTokenizer(tPK, ", ");
3679                                                 while (st.hasMoreTokens()) {
3680                                                         if (sortBy.length() > 0)
3681                                                                 sortBy.append(",");
3682                                                         sortBy.append(tId);
3683                                                         sortBy.append(".");
3684                                                         sortBy.append(st.nextToken());
3685                                                 } // while
3686                                         }
3687                                 } // if
3688                         if (reportSQL.trim().toUpperCase().startsWith("SELECT")) {
3689                                  //if (!(dbType.equals("DAYTONA") && reportSQL.trim().toUpperCase().startsWith("SELECT")))
3690                                         // reportSQL += " ORDER BY " + ((sortBy == null) ? "1" : sortBy.toString());
3691                         }
3692                 }
3693
3694                 StringBuffer colNames = new StringBuffer();
3695                 StringBuffer colExtraIdNames = new StringBuffer();
3696                 StringBuffer colExtraDateNames = new StringBuffer();
3697
3698                 if(getColumnNamesFromReportSQL) {
3699                         DataSet ds = ConnectionUtils.getDataSet(reportSQL, dbInfo);
3700                         List reportCols1 = getAllColumns();
3701                         reportCols = new Vector();
3702                         outer:
3703                         for (Iterator iter = reportCols1.iterator(); iter.hasNext();) {
3704                                 DataColumnType dct = (DataColumnType) iter.next();
3705                                 for (int k=0; k<ds.getColumnCount(); k++) {
3706                                         if(dct.getColId().toUpperCase().trim().equals(ds.getColumnName(k).trim())) {
3707                                                 reportCols.add(dct);
3708                                                 continue outer;
3709                                         }
3710                                 }
3711                         }
3712
3713                         if (getFormFieldList() != null) {
3714                                 String paramValue = "";
3715                                 for (Iterator iter = getFormFieldList().getFormField().iterator(); iter.hasNext();) {
3716                                         FormFieldType fft = (FormFieldType) iter.next();
3717                                         if(fft.isGroupFormField()!=null && fft.isGroupFormField().booleanValue()) {
3718                                 paramValue = Utils.oracleSafe(nvl(paramValues
3719                                                 .getParamValue(fft.getFieldId())));
3720                                 outer:
3721                                         for (Iterator iter1 = reportCols1.iterator(); iter1.hasNext();) {
3722                                                 DataColumnType dct = (DataColumnType) iter1.next();
3723                                                         if(("["+fft.getFieldName()+ "]").equals(dct.getColName().trim())) {
3724                                                                 dct.setDisplayName(paramValue);
3725                                                                 continue outer;
3726                                                         }
3727                                         }
3728
3729
3730                                         }
3731                                 }
3732                         }
3733
3734                 }
3735
3736                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3737                                 DataColumnType dc = (DataColumnType) iter.next();
3738                                 //TODO: commented if (dc.isVisible()) {
3739                                         if (colNames.length() > 0)
3740                                                 colNames.append(", ");
3741                                         colNames.append(dc.getColId());
3742                                 //TODO uncomment if it's not working} // if
3743
3744                                 // Checking for extra fields necessary for drill-down
3745                                 if (nvl(dc.getDrillDownURL()).length() > 0)
3746                                         if (isViewAction(dc.getDrillDownURL()))
3747                                                 addExtraIdSelect(colExtraIdNames, nvl(dc.getDrillDownParams()), false);
3748                                         else
3749                                                 addExtraDateSelect(colExtraDateNames, nvl(dc.getDrillDownParams()), null,
3750                                                                 false);
3751                         } // for
3752
3753                         if (reportSQL.toUpperCase().indexOf("GROUP BY ") < 0)
3754                                 colNames.append(colExtraIdNames.toString());
3755                         //commented to avoid coldId_dde
3756                         //colNames.append(colExtraDateNames.toString());
3757
3758                 /*
3759                  * if(pageNo==0) if(reportSQL.toUpperCase().indexOf(" WHERE ")<0)
3760                  * if(reportSQL.toUpperCase().indexOf(" GROUP BY ")<0) reportSQL =
3761                  * reportSQL.substring(0, reportSQL.toUpperCase().indexOf(" ORDER BY
3762                  * "))+" WHERE ROWNUM <=
3763                  * "+getPageSize()+reportSQL.substring(reportSQL.toUpperCase().indexOf("
3764                  * ORDER BY ")); else reportSQL = "SELECT "+colNames.toString()+" FROM
3765                  * (SELECT ROWNUM rnum, "+colNames.toString()+" FROM ("+reportSQL+") x)
3766                  * y WHERE rnum <= "+getPageSize()+" ORDER BY rnum"; else reportSQL =
3767                  * reportSQL.substring(0, reportSQL.toUpperCase().indexOf(" WHERE "))+"
3768                  * WHERE ROWNUM <= "+getPageSize()+" AND
3769                  * "+reportSQL.substring(reportSQL.toUpperCase().indexOf(" WHERE ")+7);
3770                  * else reportSQL = "SELECT "+colNames.toString()+" FROM (SELECT ROWNUM
3771                  * rnum, "+colNames.toString()+" FROM ("+reportSQL+") x) y WHERE rnum >=
3772                  * "+(pageNo*getPageSize()+1)+" AND rnum <=
3773                  * "+((pageNo+1)*getPageSize())+" ORDER BY rnum";
3774                  */
3775                  if (dbType.equals("DAYTONA") && reportSQL.trim().toUpperCase().startsWith("SELECT")) {
3776                          if(endRow == -1) endRow = (getMaxRowsInExcelDownload()>0)?getMaxRowsInExcelDownload():Globals.getDownloadLimit();
3777                                         reportSQL = reportSQL + " LIMIT TO " +(startRow==0?startRow+1:startRow)+"->"+endRow;
3778                                         return reportSQL;
3779                          } else if (dbType.equals("DAYTONA")) {
3780                                  return reportSQL;
3781                          }
3782
3783                 //reportSQL = "SELECT " + colNames.toString() + " FROM (SELECT ROWNUM rnum, "
3784                 //              + colNames.toString() + " FROM (" + reportSQL + ") x ";
3785
3786                 String rSQL = Globals.getGenerateSubsetSql();
3787                 rSQL = rSQL.replace("[colNames.toString()]", colNames.toString());
3788                 rSQL = rSQL.replace("[reportSQL]", reportSQL);
3789
3790                 reportSQL=rSQL;
3791                 //added rownum for total report where row header need to be shown
3792                 //reportSQLOnlyFirstPart = "SELECT rnum," + colNames.toString() + " FROM (SELECT ROWNUM rnum, "
3793                 //+ colNames.toString() + " FROM (" ;
3794
3795                 reportSQLOnlyFirstPart = Globals.getReportSqlOnlyFirstPart();
3796                 reportSQLOnlyFirstPart = reportSQLOnlyFirstPart.replace("[colNames.toString()]", colNames.toString());
3797
3798
3799        reportSQLWithRowNum = reportSQL;
3800
3801       /*           if( endRow != -1)
3802                    reportSQL += " WHERE ROWNUM <= " + endRow;
3803                     reportSQL += " ) y WHERE rnum >= " + startRow + " ORDER BY rnum";
3804                 return reportSQL;*/
3805                 String parta = Globals.getReportSqlOnlySecondPartA();
3806                 String partb = Globals.getReportSqlOnlySecondPartB();
3807
3808                 String partSql = "";
3809                 if(!AppUtils.isNotEmpty(getDBType())){
3810                         setDBType(Globals.getDBType());
3811                 }
3812
3813                 int closeBracketPos = 0;
3814                 if(wholeSQL_OrderBy.lastIndexOf(")")!= -1) closeBracketPos = wholeSQL_OrderBy.lastIndexOf(")");
3815                 int idxOrderBy = (closeBracketPos>0)?wholeSQL_OrderBy.toUpperCase().indexOf("ORDER BY", closeBracketPos):wholeSQL_OrderBy.toUpperCase().lastIndexOf("ORDER BY");
3816                 String orderbyclause =  "";
3817                 if (idxOrderBy < 0) {
3818                         orderbyclause = " ORDER BY 1 ";
3819                         partSql += " "+ orderbyclause+ " ";
3820                 }
3821                 else {
3822                       orderbyclause = wholeSQL_OrderBy.substring(idxOrderBy);
3823                       partSql += " "+ orderbyclause+ " ";
3824                 }
3825
3826                 if(getDBType().equals(AppConstants.MYSQL)) {
3827                         partSql = partSql+ " LIMIT "+ String.valueOf(startRow)+" , "+ String.valueOf(endRow);
3828                 } else if(getDBType().equals(AppConstants.ORACLE)) {
3829                         reportSQL = reportSQL.replace(" AS ", " ");
3830                         partSql = "where rownum >= "+ String.valueOf(startRow)+" and rownum <= "+(Integer.parseInt(String.valueOf(startRow)) + Integer.parseInt(String.valueOf(endRow)));
3831                 } else if(getDBType().equals(AppConstants.POSTGRESQL)) {
3832                         partSql = partSql + " LIMIT "+ String.valueOf(endRow)+" , "+ String.valueOf(startRow);//limit [pageSize] offset [startRow]
3833                 }
3834
3835                 // Limit only to MYSQL or MariaDB
3836                 //if (reportSQL.toUpperCase().indexOf("ORDER BY ") < 0)
3837                         //partSql += " ORDER BY 1";
3838                 //else  {
3839
3840
3841
3842                 /*if(!Globals.isMySQL())
3843                         parta = parta.replace("[endRow]", String.valueOf(endRow));
3844                 else
3845                         parta = parta.replace("[startRow]", String.valueOf(startRow));
3846
3847                 //String partb = Globals.getReportSqlOnlySecondPartB();
3848                 if(!Globals.isMySQL())
3849                         partb = partb.replace("[startRow]", String.valueOf(startRow));
3850                 else
3851                         partb = partb.replace("[pageSize]", String.valueOf(getPageSize()));
3852
3853                 if( endRow != -1)
3854                         reportSQL += parta;*/
3855                 reportSQL += partSql;
3856
3857                 return reportSQL;
3858
3859         } // generateSubsetSQL
3860
3861         public String generateChartSQL(ReportParamValues paramValues, String userId, HttpServletRequest request ) throws RaptorException  {
3862                 List reportCols = getAllColumns();
3863                 List chartValueCols = getChartValueColumnsList(AppConstants.CHART_ALL_COLUMNS, null); // parameter is 0 has this requires all columns.
3864                 String reportSQL = generateSQL(userId, request);
3865                 //if(nvl(reportSQL).length()>0) reportSQL = generatedChartSQL;
3866         logger.debug(EELFLoggerDelegate.debugLogger, ("SQL " + reportSQL));
3867                 String legendCol = "1 a";
3868                 // String valueCol = "1";
3869                 StringBuffer groupCol = new StringBuffer();
3870                 StringBuffer seriesCol = new StringBuffer();
3871                 StringBuffer valueCols = new StringBuffer();
3872
3873                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3874                         DataColumnType dc = (DataColumnType) iter.next();
3875                         String colName = getColumnSelectStr(dc, paramValues);
3876                         if (nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))
3877                                 legendCol = getSelectExpr(dc, colName)+" " + dc.getColId();
3878                         // if(dc.getChartSeq()>0)
3879                         // valueCol = "NVL("+colName+", 0) "+dc.getColId();
3880                         if ((!nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))
3881                                         && (dc.getChartSeq()==null || dc.getChartSeq() <= 0) && dc.isGroupBreak()) {
3882                                 groupCol.append(", ");
3883                                 groupCol.append(colName + " " +  dc.getColId());
3884                         }
3885                 } // for
3886                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3887                         DataColumnType dc = (DataColumnType) iter.next();
3888                         if(dc.isChartSeries()!=null && dc.isChartSeries().booleanValue()) {
3889                                 //System.out.println("*****************, "+ " " +getColumnSelectStr(dc, paramValues)+ " "+ getSelectExpr(dc,getColumnSelectStr(dc, paramValues)));
3890                                 seriesCol.append(", "+ getSelectExpr(dc,getColumnSelectStr(dc, paramValues))+ " " +  dc.getColId());
3891                         }
3892                 }
3893
3894                 /*for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3895                         DataColumnType dc = (DataColumnType) iter.next();
3896                         if(!dc.isChartSeries() && !(nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))) {
3897                                 //System.out.println("*****************, "+ " " +getColumnSelectStr(dc, paramValues)+ " "+ getSelectExpr(dc,getColumnSelectStr(dc, paramValues)));
3898                                 seriesCol.append(", "+ formatChartColumn(getSelectExpr(dc,getColumnSelectStr(dc, paramValues)))+ " " +  dc.getColId());
3899                 }
3900                 }*/
3901
3902                 for (Iterator iter = chartValueCols.iterator(); iter.hasNext();) {
3903                         DataColumnType dc = (DataColumnType) iter.next();
3904                         String colName = getColumnSelectStr(dc, paramValues);
3905                         //valueCols.append(", NVL(" + formatChartColumn(colName) + ",0) " + dc.getColId());
3906                         seriesCol.append("," + formatChartColumn(colName) + " " + dc.getColId());
3907                 } // for
3908
3909                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3910                         DataColumnType dc = (DataColumnType) iter.next();
3911                         String colName = getColumnSelectStr(dc, paramValues);
3912                         if(colName.equals(AppConstants.RI_CHART_TOTAL_COL))
3913                                 seriesCol.append(", " + AppConstants.RI_CHART_TOTAL_COL + " " + AppConstants.RI_CHART_TOTAL_COL );
3914                         if (colName.equals(AppConstants.RI_CHART_COLOR))
3915                                 seriesCol.append(", " + AppConstants.RI_CHART_COLOR + " " + AppConstants.RI_CHART_COLOR );
3916                         if(colName.equals(AppConstants.RI_CHART_MARKER_START))
3917                                 seriesCol.append(", " + AppConstants.RI_CHART_MARKER_START + " " + AppConstants.RI_CHART_MARKER_START );
3918                         if(colName.equals(AppConstants.RI_CHART_MARKER_END))
3919                                 seriesCol.append(", " + AppConstants.RI_CHART_MARKER_END + " " + AppConstants.RI_CHART_MARKER_END );
3920                         if(colName.equals(AppConstants.RI_CHART_MARKER_TEXT_LEFT))
3921                                 seriesCol.append(", " + AppConstants.RI_CHART_MARKER_TEXT_LEFT + " " + AppConstants.RI_CHART_MARKER_TEXT_LEFT );
3922                         if(colName.equals(AppConstants.RI_CHART_MARKER_TEXT_RIGHT))
3923                                 seriesCol.append(", " + AppConstants.RI_CHART_MARKER_TEXT_RIGHT + " " + AppConstants.RI_CHART_MARKER_TEXT_RIGHT );
3924                         if(colName.equals(AppConstants.RI_ANOMALY_TEXT))
3925                                 seriesCol.append(", " + AppConstants.RI_ANOMALY_TEXT + " " + AppConstants.RI_ANOMALY_TEXT );
3926                 }
3927
3928          //debugLogger.debug("ReportSQL Chart " + reportSQL );
3929                 /*for (Iterator iter = chartValueCols.iterator(); iter.hasNext();) {
3930                         DataColumnType dc = (DataColumnType) iter.next();
3931                         String colName = getColumnSelectStr(dc, paramValues);
3932                         //valueCols.append(", NVL(" + formatChartColumn(colName) + ",0) " + dc.getColId());
3933                         valueCols.append("," + formatChartColumn(colName) + " " + dc.getColId());
3934                 } // for
3935                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
3936                         DataColumnType dc = (DataColumnType) iter.next();
3937                         String colName = getColumnSelectStr(dc, paramValues);
3938                         //if(colName.equals(AppConstants.RI_CHART_TOTAL_COL) || colName.equals(AppConstants.RI_CHART_COLOR)) {
3939                                 if(colName.equals(AppConstants.RI_CHART_TOTAL_COL))
3940                                         valueCols.append(", " + AppConstants.RI_CHART_TOTAL_COL + " " + AppConstants.RI_CHART_TOTAL_COL );
3941                                 if (colName.equals(AppConstants.RI_CHART_COLOR))
3942                                         valueCols.append(", " + AppConstants.RI_CHART_COLOR + " " + AppConstants.RI_CHART_COLOR );
3943                                 if (colName.equals(AppConstants.RI_CHART_INCLUDE))
3944                                         valueCols.append(", " + AppConstants.RI_CHART_INCLUDE + " " + AppConstants.RI_CHART_INCLUDE );
3945                         //}
3946                 }*/
3947         String final_sql = "";
3948         reportSQL = Utils.replaceInString(reportSQL, " from ", " FROM ");
3949         reportSQL = Utils.replaceInString(reportSQL, " select ", " SELECT ");
3950         reportSQL = Utils.replaceInString(reportSQL, " union ", " UNION ");
3951         //reportSQL = reportSQL.replaceAll("[\\s]*\\(", "(");
3952 //        if(reportSQL.indexOf("UNION") != -1) {
3953 //            if(reportSQL.indexOf("FROM(")!=-1)
3954 //                final_sql += " "+reportSQL.substring(reportSQL.indexOf("FROM(") );
3955 //            else if (reportSQL.indexOf("FROM (")!=-1)
3956 //                final_sql += " "+reportSQL.substring(reportSQL.indexOf("FROM (") );
3957 //            //TODO ELSE THROW ERROR
3958 //        }
3959 //        else {
3960 //            final_sql += " "+reportSQL.substring(reportSQL.toUpperCase().indexOf(" FROM "));
3961 //        }
3962         int pos = 0;
3963         int pos_first_select = 0;
3964         int pos_dup_select = 0;
3965         int pos_prev_select = 0;
3966         int pos_last_select = 0;
3967         if (reportSQL.indexOf("FROM", pos)!=-1) {
3968             pos = reportSQL.indexOf("FROM", pos);
3969             pos_dup_select = reportSQL.lastIndexOf("SELECT",pos);
3970             pos_first_select = reportSQL.indexOf("SELECT");//,pos);
3971             logger.debug(EELFLoggerDelegate.debugLogger, ("pos_select " + pos_first_select + " " + pos_dup_select));
3972             if(pos_dup_select > pos_first_select) {
3973                 logger.debug(EELFLoggerDelegate.debugLogger, ("********pos_dup_select ********" + pos_dup_select));
3974                 //pos_dup_select1 =  pos_dup_select;
3975                 pos_prev_select = pos_first_select;
3976                 pos_last_select = pos_dup_select;
3977                 while (pos_last_select > pos_prev_select) {
3978                     logger.debug(EELFLoggerDelegate.debugLogger, ("pos_last , pos_prev " + pos_last_select + " " + pos_prev_select));
3979                     pos = reportSQL.indexOf("FROM", pos+2);
3980                     pos_prev_select = pos_last_select;
3981                     pos_last_select = reportSQL.lastIndexOf("SELECT",pos);
3982                     logger.debug(EELFLoggerDelegate.debugLogger, ("in WHILE LOOP LAST " + pos_last_select));
3983                 }
3984              }
3985
3986           }
3987          final_sql += " "+reportSQL.substring(pos);
3988          logger.debug(EELFLoggerDelegate.debugLogger, ("Final SQL " + final_sql));
3989         String sql =  "SELECT " + legendCol + ", " + legendCol+"_1" + seriesCol.toString()+ nvl(valueCols.toString(), ", 1")
3990                                 + groupCol.toString()
3991                                 + final_sql;
3992         logger.debug(EELFLoggerDelegate.debugLogger, ("Final sql in generateChartSQL " +sql));
3993
3994         return sql;
3995         } // generateChartSQL
3996
3997     private String formatChartColumn(String colName) {
3998
3999         logger.debug(EELFLoggerDelegate.debugLogger, ("Format Chart Column Input colName" + colName));
4000         colName =  colName.trim();
4001         colName = Utils.replaceInString(colName, "TO_CHAR", "to_char");
4002         colName = Utils.replaceInString(colName, "to_number", "TO_NUMBER");
4003         //reportSQL = reportSQL.replaceAll("[\\s]*\\(", "(");
4004         colName = colName.replaceAll(",[\\s]*\\(", ",(");
4005         StringBuffer colNameBuf = new StringBuffer(colName);
4006         int pos = 0, posFormatStart = 0, posFormatEnd = 0;
4007         String format = "";
4008         if(colNameBuf.indexOf("999")==-1 && colNameBuf.indexOf("990")==-1) {
4009                 logger.debug(EELFLoggerDelegate.debugLogger, (" return colName " + colNameBuf.toString()));
4010             return colNameBuf.toString();
4011         }
4012         while (colNameBuf.indexOf("to_char")!=-1) {
4013             if(colNameBuf.indexOf("999")!=-1 || colNameBuf.indexOf("990")!=-1) {
4014                 pos = colNameBuf.indexOf("to_char");
4015                 colNameBuf.insert(pos, " TO_NUMBER ( CR_RAPTOR.SAFE_TO_NUMBER (");
4016                 pos = colNameBuf.indexOf("to_char");
4017                 colNameBuf.replace(pos, pos+7, "TO_CHAR");
4018                 //colName = Utils.replaceInString(colNameBuf.toString(), "to_char", " TO_NUMBER ( CR_RAPTOR.SAFE_TO_NUMBER ( TO_CHAR ");
4019                 logger.debug(EELFLoggerDelegate.debugLogger, ("After adding to_number " + colNameBuf.toString()));
4020                 //posFormatStart = colNameBuf.lastIndexOf(",'")+1;
4021                 posFormatStart = colNameBuf.indexOf(",'", pos)+1;
4022                 posFormatEnd = colNameBuf.indexOf(")",posFormatStart);
4023                 logger.debug(EELFLoggerDelegate.debugLogger, (posFormatStart + " " + posFormatEnd + " "+ pos));
4024                 format = colNameBuf.substring(posFormatStart, posFormatEnd);
4025                 //posFormatEnd = colNameBuf.indexOf(")",posFormatEnd);
4026                 colNameBuf.insert(posFormatEnd+1, " ," + format + ") , "+ format + ")");
4027                 logger.debug(EELFLoggerDelegate.debugLogger, ("colNameBuf " + colNameBuf.toString()));
4028             }
4029         }
4030         logger.debug(EELFLoggerDelegate.debugLogger, (" return colName " + colNameBuf.toString()));
4031         return colNameBuf.toString();
4032     }
4033         public String generateTotalSQLLinear(ReportParamValues paramValues, String userId, HttpServletRequest request) throws RaptorException  {
4034                 List reportCols = getAllColumns();
4035                 String reportSQL = generateSQL(userId,request);
4036         //debugLogger.debug("After GenerateSQL  "  + reportSQL);
4037
4038                 StringBuffer sbSelect = new StringBuffer();
4039                 StringBuffer sbTotal = new StringBuffer();
4040                 StringBuffer colNames = new StringBuffer();
4041             for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
4042
4043                     DataColumnType dc = (DataColumnType) iter.next();
4044                                 if (colNames.length() > 0)
4045                                         colNames.append(", ");
4046                                 colNames.append(dc.getColId());
4047              }
4048                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
4049                         DataColumnType dct = (DataColumnType) iter.next();
4050
4051                         //if (!dct.isVisible())
4052                         //      continue;
4053
4054                         String colName = getColumnSelectStr(dct, paramValues);
4055
4056                         sbSelect.append((sbSelect.length() == 0) ? "SELECT " : ", ");
4057
4058                         sbSelect.append(colName);
4059                         sbSelect.append(" ");
4060                         sbSelect.append(dct.getColId());
4061
4062
4063             sbTotal.append((sbTotal.length() == 0) ? "SELECT " : ", ");
4064                         if (nvl(dct.getDisplayTotal()).length() > 0) {
4065                                 // sbTotal.append(getSelectExpr(dct,
4066                                 // dct.getDisplayTotal()+dct.getColId()+")"));
4067                                 String displayTotal = dct.getDisplayTotal();
4068                                 StringBuffer sb = new StringBuffer();
4069                                 for (int i = 0; i < displayTotal.length(); i++) {
4070                                         char ch = displayTotal.charAt(i);
4071                                         if (ch == '+' || ch == '-')
4072                                                 sb.append(dct.getColId() + ")");
4073                                         sb.append(ch);
4074                                 } // for
4075                                 sb.append(dct.getColId() + ")");
4076
4077                 //debugLogger.debug("SB " + sb.toString() + "\n " + getSelectExpr(dct, sb.toString()));
4078                                 sbTotal.append(getSelectExpr(dct, sb.toString()));
4079                 //debugLogger.debug("SBTOTAL " + sbTotal.toString());
4080                         } else
4081                                 sbTotal.append("NULL");
4082                         sbTotal.append(" total_");
4083                         sbTotal.append(dct.getColId());
4084                 } // for
4085
4086         //debugLogger.debug(" ******  " + sbTotal.toString());
4087         logger.debug(EELFLoggerDelegate.debugLogger, ("REPORTWRAPPER " + reportSQL));
4088         int pos = 0;
4089         int pos_first_select = 0;
4090         int pos_dup_select = 0;
4091         int pos_prev_select = 0;
4092         int pos_last_select = 0;
4093
4094         //reportSQL = Utils.replaceInString(reportSQL, " from ", " FROM ");
4095         //reportSQL = Utils.replaceInString(reportSQL, "select ", "SELECT ");
4096         reportSQL = replaceNewLine(reportSQL, " from ", " FROM ");
4097         reportSQL = replaceNewLine(reportSQL, "from ", " FROM ");
4098         reportSQL = replaceNewLine(reportSQL, "FROM ", " FROM ");
4099
4100         reportSQL = " "+reportSQL;
4101         reportSQL = replaceNewLine(reportSQL, "select ", " SELECT ");
4102         reportSQL = replaceNewLine(reportSQL, "SELECT ", " SELECT ");
4103         if (reportSQL.indexOf("FROM", pos)!=-1) {
4104             pos = reportSQL.indexOf("FROM", pos);
4105             pos_dup_select = reportSQL.lastIndexOf("SELECT",pos);
4106             pos_first_select = reportSQL.indexOf("SELECT");//,pos);
4107             logger.debug(EELFLoggerDelegate.debugLogger, ("pos_select " + pos_first_select + " " + pos_dup_select));
4108             if(pos_dup_select > pos_first_select) {
4109                 logger.debug(EELFLoggerDelegate.debugLogger, ("********pos_dup_select ********" + pos_dup_select));
4110                 //pos_dup_select1 =  pos_dup_select;
4111                 pos_prev_select = pos_first_select;
4112                 pos_last_select = pos_dup_select;
4113                 while (pos_last_select > pos_prev_select) {
4114                     logger.debug(EELFLoggerDelegate.debugLogger, ("pos_last , pos_prev " + pos_last_select + " " + pos_prev_select));
4115                     pos = reportSQL.indexOf("FROM", pos+2);
4116                     pos_prev_select = pos_last_select;
4117                     pos_last_select = reportSQL.lastIndexOf("SELECT",pos);
4118                     logger.debug(EELFLoggerDelegate.debugLogger, ("in WHILE LOOP LAST " + pos_last_select));
4119                 }
4120              }
4121
4122           }
4123
4124
4125                 //sbSelect.append(reportSQL.substring(reportSQL.toUpperCase().indexOf(" FROM ")));
4126
4127         logger.debug(EELFLoggerDelegate.debugLogger, (" *************** " + pos + " " + reportSQL));
4128         //sbSelect.append(" "+ reportSQL.substring(pos));
4129         sbSelect.append(" "+reportSQL.substring(pos));
4130         logger.debug(EELFLoggerDelegate.debugLogger, (" **************** " + sbSelect.toString()));
4131         sbTotal.append(" FROM (");
4132                 sbTotal.append(sbSelect.toString());
4133                 sbTotal.append(") totalSQL");
4134
4135                 String dbType = "";
4136                 String dbInfo = getDBInfo();
4137                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
4138                         try {
4139                          org.openecomp.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.openecomp.portalsdk.analytics.util.RemDbInfo();
4140                          dbType = remDbInfo.getDBType(dbInfo);
4141                         } catch (Exception ex) {
4142                            throw new RaptorException(ex);
4143                         }
4144                 }
4145                 if (dbType.equals("DAYTONA")) {
4146                         sbTotal.append("("+ colNames+ ")");
4147                 }
4148         String sql = sbTotal.toString();
4149         sql = Utils.replaceInString(sql, " from ", " FROM ");
4150         sql = Utils.replaceInString(sql, "select ", "SELECT ");
4151         //sql = Utils.replaceInString(sql, " select ", " SELECT ");
4152         logger.debug(EELFLoggerDelegate.debugLogger, ("Before SQL Corrector "  + sql));
4153         String corrected_SQL = new SQLCorrector().fixSQL(new StringBuffer(sql));
4154         logger.debug(EELFLoggerDelegate.debugLogger, ("************"));
4155         logger.debug(EELFLoggerDelegate.debugLogger, ("Corrected SQL " + corrected_SQL));
4156         return  corrected_SQL;
4157                 //return sbTotal.toString();
4158         } // generateTotalSQLLinear
4159
4160         public String generateTotalSQLCrossTab(String sql, String rowColPos,
4161                         String userId, HttpServletRequest request, ReportParamValues paramValues) throws RaptorException  {
4162                 List reportCols = getAllColumns();
4163                 String reportSQL = sql;
4164
4165                 StringBuffer sbSelect = new StringBuffer();
4166                 StringBuffer sbGroup = new StringBuffer();
4167                 // StringBuffer sbOrder = new StringBuffer();
4168                 StringBuffer sbTotal = new StringBuffer();
4169                 StringBuffer colNames = new StringBuffer();
4170             for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
4171
4172                     DataColumnType dc = (DataColumnType) iter.next();
4173                                 if (colNames.length() > 0)
4174                                         colNames.append(", ");
4175                                 colNames.append(dc.getColId());
4176              }
4177                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
4178                         DataColumnType dct = (DataColumnType) iter.next();
4179
4180                         if (!dct.isVisible())
4181                                 continue;
4182
4183
4184                         String colName = getColumnSelectStr(dct, paramValues);
4185                         String colExpr = getSelectExpr(dct, colName);
4186
4187                         sbSelect.append((sbSelect.length() == 0) ? "SELECT " : ", ");
4188
4189                         if (nvl(dct.getCrossTabValue()).equals(rowColPos)) {
4190                                 //sbSelect.append(colExpr);
4191                                 sbSelect.append(dct.getColId());
4192                                 sbGroup.append((sbGroup.length() == 0) ? " GROUP BY " : ", ");
4193                                 sbGroup.append(dct.getColId());
4194
4195                                 /*
4196                                  * sbOrder.append((sbOrder.length()==0)?" ORDER BY ":", ");
4197                                  * sbOrder.append(dct.getColId());
4198                                  * if(dct.getColType().equals(AppConstants.CT_DATE))
4199                                  * sbOrder.append(" DESC");
4200                                  */
4201
4202                                 sbTotal.append((sbTotal.length() == 0) ? "SELECT " : ", ");
4203                                 sbTotal.append(dct.getColId());
4204                         } else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_VALUE)) {
4205                                 //sbSelect.append(colName);
4206                                 sbSelect.append(dct.getColId());
4207
4208                                 String displayTotal = getCrossTabDisplayTotal(rowColPos);
4209                                 if (displayTotal.length() > 0) {
4210                                         // displayTotal += dct.getColId()+")";
4211                                         StringBuffer sb = new StringBuffer();
4212                                         for (int i = 0; i < displayTotal.length(); i++) {
4213                                                 char ch = displayTotal.charAt(i);
4214                                                 if (ch == '+' || ch == '-')
4215                                                         sb.append(dct.getColId() + ")");
4216                                                 sb.append(ch);
4217                                         } // for
4218                                         sb.append(dct.getColId() + ")");
4219
4220                                         displayTotal = sb.toString();
4221                                 } else
4222                                         displayTotal = "COUNT(*)";
4223
4224                                 sbTotal.append((sbTotal.length() == 0) ? "SELECT " : ", ");
4225                                 sbTotal.append(getSelectExpr(dct, displayTotal));
4226                                 sbTotal.append(" total_");
4227                                 sbTotal.append(dct.getColId());
4228                         } else {
4229                                 //sbSelect.append(colExpr);
4230                                 sbSelect.append(dct.getColId());
4231                         } // if
4232
4233                         sbSelect.append(" ");
4234                         sbSelect.append(dct.getColId());
4235                 } // for
4236
4237                 sbSelect.append(reportSQL.substring(reportSQL.toUpperCase().indexOf(" FROM ")));
4238
4239                 sbTotal.append(" FROM (");
4240                 sbTotal.append(sbSelect.toString());
4241                 sbTotal.append(") totalSQL");
4242                 sbTotal.append(sbGroup.toString());
4243                 String dbType = "";
4244                 String dbInfo = getDBInfo();
4245                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
4246                         try {
4247                          org.openecomp.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.openecomp.portalsdk.analytics.util.RemDbInfo();
4248                          dbType = remDbInfo.getDBType(dbInfo);
4249                         } catch (Exception ex) {
4250                            throw new RaptorException(ex);
4251                         }
4252                 }
4253                 if (dbType.equals("DAYTONA")) {
4254                         sbTotal.append("("+ colNames+ ")");
4255                 }
4256
4257                 // sbTotal.append(sbOrder.toString());
4258
4259                 //debugLogger.debug(getReportDefType() + " " + AppConstants.RD_SQL_BASED);
4260         //debugLogger.debug("SQL To Delete " + sbTotal.toString());
4261         sql = "";
4262         if (getReportDefType().equals(AppConstants.RD_SQL_BASED)) {
4263             sql = Utils.replaceInString(sbTotal.toString(), " from ", " FROM ");
4264             sql = Utils.replaceInString(sql, "select ", "SELECT ");
4265             return new SQLCorrector().fixSQL(new StringBuffer(sql));
4266         }
4267
4268         return sbTotal.toString();
4269
4270         } // generateTotalSQLCrossTab
4271
4272
4273         public String generateTotalSQLCrossTab(ReportParamValues paramValues, String rowColPos,
4274                         String userId, HttpServletRequest request) throws RaptorException  {
4275                 List reportCols = getAllColumns();
4276                 String reportSQL = generateSQL(userId, request);
4277
4278                 StringBuffer sbSelect = new StringBuffer();
4279                 StringBuffer sbGroup = new StringBuffer();
4280                 // StringBuffer sbOrder = new StringBuffer();
4281                 StringBuffer sbTotal = new StringBuffer();
4282                 StringBuffer colNames = new StringBuffer();
4283             for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
4284
4285                     DataColumnType dc = (DataColumnType) iter.next();
4286                                 if (colNames.length() > 0)
4287                                         colNames.append(", ");
4288                                 colNames.append(dc.getColId());
4289              }
4290                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
4291                         DataColumnType dct = (DataColumnType) iter.next();
4292
4293                         if (!dct.isVisible())
4294                                 continue;
4295
4296                         String colName = getColumnSelectStr(dct, paramValues);
4297                         String colExpr = getSelectExpr(dct, colName);
4298
4299                         sbSelect.append((sbSelect.length() == 0) ? "SELECT " : ", ");
4300
4301                         if (nvl(dct.getCrossTabValue()).equals(rowColPos)) {
4302                                 sbSelect.append(colExpr);
4303
4304                                 sbGroup.append((sbGroup.length() == 0) ? " GROUP BY " : ", ");
4305                                 sbGroup.append(dct.getColId());
4306
4307                                 /*
4308                                  * sbOrder.append((sbOrder.length()==0)?" ORDER BY ":", ");
4309                                  * sbOrder.append(dct.getColId());
4310                                  * if(dct.getColType().equals(AppConstants.CT_DATE))
4311                                  * sbOrder.append(" DESC");
4312                                  */
4313
4314                                 sbTotal.append((sbTotal.length() == 0) ? "SELECT " : ", ");
4315                                 sbTotal.append(dct.getColId());
4316                         } else if (nvl(dct.getCrossTabValue()).equals(AppConstants.CV_VALUE)) {
4317                                 sbSelect.append(colName);
4318
4319                                 String displayTotal = getCrossTabDisplayTotal(rowColPos);
4320                                 if (displayTotal.length() > 0) {
4321                                         // displayTotal += dct.getColId()+")";
4322                                         StringBuffer sb = new StringBuffer();
4323                                         for (int i = 0; i < displayTotal.length(); i++) {
4324                                                 char ch = displayTotal.charAt(i);
4325                                                 if (ch == '+' || ch == '-')
4326                                                         sb.append(dct.getColId() + ")");
4327                                                 sb.append(ch);
4328                                         } // for
4329                                         sb.append(dct.getColId() + ")");
4330
4331                                         displayTotal = sb.toString();
4332                                 } else
4333                                         displayTotal = "COUNT(*)";
4334
4335                                 sbTotal.append((sbTotal.length() == 0) ? "SELECT " : ", ");
4336                                 sbTotal.append(getSelectExpr(dct, displayTotal));
4337                                 sbTotal.append(" total_");
4338                                 sbTotal.append(dct.getColId());
4339                         } else {
4340                                 sbSelect.append(colExpr);
4341                         } // if
4342
4343                         sbSelect.append(" ");
4344                         sbSelect.append(dct.getColId());
4345                 } // for
4346
4347                 sbSelect.append(reportSQL.substring(reportSQL.toUpperCase().indexOf(" FROM ")));
4348
4349                 sbTotal.append(" FROM (");
4350                 sbTotal.append(sbSelect.toString());
4351                 sbTotal.append(") totalSQL");
4352                 sbTotal.append(sbGroup.toString());
4353                 String dbType = "";
4354                 String dbInfo = getDBInfo();
4355                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
4356                         try {
4357                          org.openecomp.portalsdk.analytics.util.RemDbInfo remDbInfo = new org.openecomp.portalsdk.analytics.util.RemDbInfo();
4358                          dbType = remDbInfo.getDBType(dbInfo);
4359                         } catch (Exception ex) {
4360                            throw new RaptorException(ex);
4361                         }
4362                 }
4363                 if (dbType.equals("DAYTONA")) {
4364                         sbTotal.append("("+ colNames+ ")");
4365                 }
4366
4367                 // sbTotal.append(sbOrder.toString());
4368
4369                 //debugLogger.debug(getReportDefType() + " " + AppConstants.RD_SQL_BASED);
4370         //debugLogger.debug("SQL To Delete " + sbTotal.toString());
4371         String sql = "";
4372         if (getReportDefType().equals(AppConstants.RD_SQL_BASED)) {
4373             sql = Utils.replaceInString(sbTotal.toString(), " from ", " FROM ");
4374             sql = Utils.replaceInString(sql, "select ", "SELECT ");
4375             return new SQLCorrector().fixSQL(new StringBuffer(sql));
4376         }
4377
4378         return sbTotal.toString();
4379
4380         } // generateTotalSQLCrossTab
4381
4382         public String generateDistinctValuesSQL(ReportParamValues paramValues, DataColumnType dct,
4383                         String userId, HttpServletRequest request)  throws RaptorException  {
4384                 DataSourceType dst = getColumnTableById(dct.getColId());
4385                 String colName = getColumnSelectStr(dct, paramValues);
4386                 String colExpr = getSelectExpr(dct, colName);
4387                 ReportRuntime rr = (ReportRuntime) request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME);
4388                 StringBuffer sb = new StringBuffer();
4389                 sb.append("SELECT DISTINCT ");
4390                 if (getReportDefType().equals(AppConstants.RD_SQL_BASED)) {
4391                         sb.append(dct.getColId());
4392                         sb.append(" FROM (");
4393                         //paramvalues added below to filter distinct values based on formfields.
4394                         //sb.append(generateSQL(paramValues, userId, request));
4395                         sb.append(rr.getWholeSQL());
4396                         sb.append(") "  + (Globals.isPostgreSQL() || Globals.isMySQL() ?" AS ":"")  + " report_sql ORDER BY 1");
4397                 } else {
4398                         sb.append(colExpr);
4399                         sb.append(" ");
4400                         sb.append(dct.getColId());
4401                         if (!colExpr.equals(colName)) {
4402                                 sb.append(", ");
4403                                 sb.append(colName);
4404                         } // if
4405                         sb.append(" FROM ");
4406                         sb.append(dst.getTableName());
4407                         sb.append(" ");
4408                         sb.append(dst.getTableId());
4409                         sb.append(" ORDER BY ");
4410                         sb.append(colName);
4411                         if (dct.getColType().equals(AppConstants.CT_DATE))
4412                                 sb.append(" DESC");
4413                 } // else
4414
4415                 return sb.toString();
4416         } // generateDistinctValuesSQL
4417
4418         /** ************************************************************************************************* */
4419
4420         public DataSourceType getTableWithoutColumns() {
4421                 List dsList = getDataSourceList().getDataSource();
4422                 for (Iterator iter = dsList.iterator(); iter.hasNext();) {
4423                         DataSourceType ds = (DataSourceType) iter.next();
4424
4425                         if (ds.getDataColumnList().getDataColumn().size() == 0)
4426                                 return ds;
4427                 } // for
4428
4429                 return null;
4430         } // getTableWithoutColumns
4431
4432         public CustomReportType cloneCustomReportClearTables() throws RaptorException {
4433                 ReportWrapper nrw = new ReportWrapper(cloneCustomReport(), reportID, getOwnerID(),
4434                                 getCreateID(), getCreateDate(), getUpdateID(), getUpdateDate(), getMenuID(),
4435                                 isMenuApproved());
4436
4437                 DataSourceType ndst = null;
4438                 while ((ndst = nrw.getTableWithoutColumns()) != null)
4439                         nrw.deleteDataSourceType(ndst.getTableId());
4440
4441                 return nrw.getCustomReport();
4442         } // cloneCustomReportClearTables
4443
4444         public String marshal() throws RaptorException {
4445                 StringWriter sw = new StringWriter();
4446                 ObjectFactory objFactory = new ObjectFactory();
4447
4448         try {
4449                         JAXBContext jc = JAXBContext.newInstance("org.openecomp.portalsdk.analytics.xmlobj");
4450                         Marshaller m = jc.createMarshaller();
4451                         m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
4452                         //JAXBElement jaxbElement = new JAXBElement(new QName("customReport"), Object.class, "");
4453                         //m.marshal( System.out );
4454                         //m.marshal(jaxbElement, new StreamResult(sw));
4455                         m.marshal((getTableWithoutColumns() == null) ? objFactory.createCustomReport(cr) : objFactory.createCustomReport(cloneCustomReportClearTables()),
4456                                         new StreamResult(sw));
4457                 } catch (JAXBException ex) {
4458                         throw new RaptorException (ex.getMessage(), ex.getCause());
4459                 }
4460                 return sw.toString();
4461         } // marshal
4462
4463         public static CustomReportType unmarshalCR(String reportXML) throws RaptorException {
4464                 //CustomReport cr = null;
4465                 try {
4466                 JAXBContext jc = JAXBContext.newInstance("org.openecomp.portalsdk.analytics.xmlobj");
4467                 Unmarshaller u = jc.createUnmarshaller();
4468                 javax.xml.bind.JAXBElement<CustomReportType> doc = (javax.xml.bind.JAXBElement<CustomReportType>) u.unmarshal(new java.io.StringReader(
4469                                 reportXML));
4470                 return doc.getValue();
4471                 } catch (JAXBException ex) {
4472                         ex.printStackTrace();
4473                         throw new RaptorException (ex.getMessage(), ex.getCause());
4474                 }
4475
4476
4477         } // unmarshal
4478
4479         protected static CustomReportType createBlankCR() throws RaptorException {
4480                 return createBlankCR("N/A");
4481         } // createBlank
4482
4483         protected static CustomReportType createBlankCR(String createID) throws RaptorException {
4484                 ObjectFactory objFactory = new ObjectFactory();
4485                 CustomReportType cr = objFactory.createCustomReportType();
4486                 //CustomReport cr = null;
4487                 try {
4488                         //cr = (CustomReport) objFactory.createCustomReport(customReportType);
4489
4490                         cr.setReportName("");
4491                         cr.setReportDescr("");
4492                         cr.setChartType("");
4493                         cr.setPublic(false);
4494                         cr.setCreateId(createID);
4495                         cr.setCreateDate(DatatypeFactory.newInstance().newXMLGregorianCalendar(new GregorianCalendar()));
4496                         // cr.setReportSQL("");
4497                         cr.setReportType("");
4498                         cr.setPageSize(50);
4499
4500                         DataSourceList dataSourceList = objFactory.createDataSourceList();
4501                         cr.setDataSourceList(dataSourceList);
4502                 } catch (DatatypeConfigurationException ex) {
4503                         throw new RaptorException (ex.getMessage(), ex.getCause());
4504                 }
4505                 return cr;
4506         } // createBlank
4507
4508         protected void replaceCustomReportWithClone() throws RaptorException {
4509                 try {
4510                         CustomReportType clone = cloneCustomReport();
4511                         this.cr = clone;
4512                 } catch (Exception e) {
4513                         e.printStackTrace();
4514                         logger.debug(EELFLoggerDelegate.debugLogger, ("[SYSTEM ERROR] ReportWrapper.replaceCustomReportWithClone generated exception for report ["
4515                                         + reportID + "]. Exception: " + e.getMessage()));
4516                         throw new RaptorException("[SYSTEM ERROR] ReportWrapper.replaceCustomReportWithClone generated exception for report ["
4517                                         + reportID + "]. Exception: " + e.getMessage(), e.getCause());
4518                 }
4519         } // replaceCustomReportWithClone
4520
4521         /** ************************************************************************************************* */
4522
4523         public FormatType cloneFormatType(ObjectFactory objFactory, FormatType ft)
4524                         throws JAXBException {
4525                 FormatType nft = objFactory.createFormatType();
4526
4527                 nft.setLessThanValue(ft.getLessThanValue());
4528                 nft.setExpression(ft.getExpression());
4529                 nft.setBold(ft.isBold());
4530                 nft.setItalic(ft.isItalic());
4531                 nft.setUnderline(ft.isUnderline());
4532                 if (nvl(ft.getBgColor()).length() > 0)
4533                         nft.setBgColor(ft.getBgColor());
4534                 if (nvl(ft.getFontColor()).length() > 0)
4535                         nft.setFontColor(ft.getFontColor());
4536                 if (nvl(ft.getFontFace()).length() > 0)
4537                         nft.setFontFace(ft.getFontFace());
4538                 if (nvl(ft.getFontSize()).length() > 0)
4539                         nft.setFontSize(ft.getFontSize());
4540                 if (nvl(ft.getAlignment()).length() > 0)
4541                         nft.setAlignment(ft.getAlignment());
4542                 if (nvl(ft.getComment()).length() > 0)
4543                         nft.setComment(ft.getComment());
4544
4545                 nft.setFormatId(ft.getFormatId());
4546
4547                 return nft;
4548         } // cloneFormatType
4549
4550         public SemaphoreType cloneSemaphoreType(ObjectFactory objFactory, SemaphoreType st)
4551                         throws JAXBException {
4552                 SemaphoreType nst = objFactory.createSemaphoreType();
4553
4554                 nst.setSemaphoreName(st.getSemaphoreName());
4555                 nst.setSemaphoreType(st.getSemaphoreType());
4556                 nst.setSemaphoreId(st.getSemaphoreId());
4557                 if (nvl(st.getComment()).length() > 0)
4558                         nst.setComment(st.getComment());
4559
4560                 if (st.getFormatList() != null) {
4561                         FormatList formatList = objFactory.createFormatList();
4562                         nst.setFormatList(formatList);
4563
4564                         for (Iterator iter = st.getFormatList().getFormat().iterator(); iter.hasNext();)
4565                                 formatList.getFormat().add(
4566                                                 cloneFormatType(objFactory, (FormatType) iter.next()));
4567                 } // if
4568
4569                 return nst;
4570         } // cloneSemaphoreType
4571
4572         public Reports cloneDashboardType(ObjectFactory objFactory, Reports rpt)
4573         throws JAXBException {
4574                 Reports nrpt = objFactory.createReports();
4575
4576                 nrpt.setReportId(rpt.getReportId());
4577                 nrpt.setBgcolor(rpt.getBgcolor());
4578                 return nrpt;
4579                 } // cloneDashboardType
4580
4581         public Marker cloneMarkerType(ObjectFactory objFactory, Marker marker)
4582         throws JAXBException {
4583                 Marker nMarker = objFactory.createMarker();
4584                 nMarker.setAddressColumn(marker.getAddressColumn());
4585                 nMarker.setDataColumn(marker.getDataColumn());
4586                 nMarker.setDataHeader(marker.getDataHeader());
4587                 nMarker.setMarkerColor(marker.getMarkerColor());
4588                 return nMarker;
4589                 } // cloneDashboardType
4590
4591         public ChartDrillFormfield cloneChartDrillFormfield(ObjectFactory objFactory, ChartDrillFormfield chartDrillFormfield)
4592         throws JAXBException {
4593                 ChartDrillFormfield nChartDrillFormfield = objFactory.createChartDrillFormfield();
4594                 nChartDrillFormfield.setFormfield(chartDrillFormfield.getFormfield());
4595                 return nChartDrillFormfield;
4596                 } // cloneDashboardType
4597
4598         public boolean isChartDrillDownContainsName(String name) {
4599                 for (Iterator iter = getChartDrillOptions().getTargetFormfield().iterator(); iter
4600                                 .hasNext();) {
4601                                          org.openecomp.portalsdk.analytics.xmlobj.ChartDrillFormfield cdf = (org.openecomp.portalsdk.analytics.xmlobj.ChartDrillFormfield) iter.next();
4602                                          if(cdf.getFormfield().equals(name)) {
4603                                                  return true;
4604                                          }
4605                                 }
4606                 return false;
4607         }
4608         public FormFieldType cloneFormFieldType(ObjectFactory objFactory, FormFieldType fft)
4609                         throws JAXBException {
4610                 FormFieldType nfft = objFactory.createFormFieldType();
4611
4612                 nfft.setColId(fft.getColId());
4613                 nfft.setFieldName(fft.getFieldName());
4614                 nfft.setFieldType(fft.getFieldType());
4615                 if (nvl(fft.getVisible()).length() > 0)
4616                         nfft.setVisible(fft.getVisible());
4617                 if (nvl(fft.getValidationType()).length() > 0)
4618                         nfft.setValidationType(fft.getValidationType());
4619                 if (nvl(fft.getMandatory()).length() > 0)
4620                         nfft.setMandatory(fft.getMandatory());
4621                 if (nvl(fft.getDefaultValue()).length() > 0)
4622                         nfft.setDefaultValue(fft.getDefaultValue());
4623                 nfft.setOrderBySeq(fft.getOrderBySeq());
4624                 if (nvl(fft.getFieldSQL()).length() > 0)
4625                         nfft.setFieldSQL(fft.getFieldSQL());
4626                 if (nvl(fft.getFieldDefaultSQL()).length() > 0)
4627                         nfft.setFieldDefaultSQL(fft.getFieldDefaultSQL());
4628                 if(fft.getRangeStartDate()!=null)
4629                         nfft.setRangeStartDate(fft.getRangeStartDate());
4630                 if(fft.getRangeEndDate()!=null)
4631                         nfft.setRangeEndDate(fft.getRangeEndDate());
4632                 if(fft.getRangeStartDateSQL()!=null)
4633                         nfft.setRangeStartDateSQL(fft.getRangeStartDateSQL());
4634                 if(fft.getRangeEndDateSQL()!=null)
4635                         nfft.setRangeEndDateSQL(fft.getRangeEndDateSQL());
4636
4637                 if (nvl(fft.getComment()).length() > 0)
4638                         nfft.setComment(fft.getComment());
4639
4640                 if (fft.getPredefinedValueList() != null) {
4641                         PredefinedValueList predefinedValueList = objFactory.createPredefinedValueList();
4642                         nfft.setPredefinedValueList(predefinedValueList);
4643
4644                         for (Iterator iter = fft.getPredefinedValueList().getPredefinedValue().iterator(); iter
4645                                         .hasNext();)
4646                                 predefinedValueList.getPredefinedValue().add(new String((String) iter.next()));
4647                 } // if
4648                 if (nvl(fft.getDependsOn()).length() > 0)
4649                         nfft.setDependsOn(fft.getDependsOn());
4650
4651                 nfft.setGroupFormField((fft.isGroupFormField()!=null && fft.isGroupFormField().booleanValue())?true:false);
4652                 if (nvl(fft.getMultiSelectListSize()).length() > 0)
4653                         nfft.setMultiSelectListSize(fft.getMultiSelectListSize());
4654
4655                 nfft.setFieldId(fft.getFieldId());
4656                 return nfft;
4657         } // cloneFormFieldType
4658
4659         public JavascriptItemType cloneJavascriptType(ObjectFactory objFactory, JavascriptItemType jit)
4660         throws JAXBException {
4661                 JavascriptItemType njit = objFactory.createJavascriptItemType();
4662
4663                 njit.setId(jit.getId());
4664                 njit.setFieldId(jit.getFieldId());
4665                 njit.setCallText(jit.getCallText());
4666                 return njit;
4667         } // cloneJavascriptType
4668
4669         public ColFilterType cloneColFilterType(ObjectFactory objFactory, ColFilterType cft)
4670                         throws JAXBException {
4671                 ColFilterType ncft = objFactory.createColFilterType();
4672
4673                 ncft.setColId(cft.getColId());
4674                 ncft.setFilterSeq(cft.getFilterSeq());
4675                 ncft.setJoinCondition(cft.getJoinCondition());
4676                 if (nvl(cft.getOpenBrackets()).length() > 0)
4677                         ncft.setOpenBrackets(cft.getOpenBrackets());
4678                 ncft.setExpression(cft.getExpression());
4679                 if (nvl(cft.getArgType()).length() > 0)
4680                         ncft.setArgType(cft.getArgType());
4681                 if (nvl(cft.getArgValue()).length() > 0)
4682                         ncft.setArgValue(cft.getArgValue());
4683                 if (nvl(cft.getCloseBrackets()).length() > 0)
4684                         ncft.setCloseBrackets(cft.getCloseBrackets());
4685                 if (nvl(cft.getComment()).length() > 0)
4686                         ncft.setComment(cft.getComment());
4687
4688                 return ncft;
4689         } // cloneColFilterType
4690
4691         public DataColumnType cloneDataColumnType(ObjectFactory objFactory, DataColumnType dct)
4692                         throws JAXBException {
4693                 DataColumnType ndct = objFactory.createDataColumnType();
4694
4695                 ndct.setTableId(dct.getTableId());
4696                 ndct.setDbColName(dct.getDbColName());
4697                 if (nvl(dct.getCrossTabValue()).length() > 0)
4698                         ndct.setCrossTabValue(dct.getCrossTabValue());
4699                 ndct.setColName(dct.getColName());
4700                 ndct.setDisplayName(dct.getDisplayName());
4701                 if (dct.getDisplayWidth() > 0)
4702                         ndct.setDisplayWidth(dct.getDisplayWidth());
4703                 if (nvl(dct.getDisplayWidthInPxls()).length()>0)
4704                         ndct.setDisplayWidthInPxls(dct.getDisplayWidthInPxls());
4705                 if (nvl(dct.getDisplayAlignment()).length() > 0)
4706                         ndct.setDisplayAlignment(dct.getDisplayAlignment());
4707                 if (nvl(dct.getDisplayHeaderAlignment()).length() > 0)
4708                         ndct.setDisplayHeaderAlignment(dct.getDisplayHeaderAlignment());
4709                 ndct.setOrderSeq(dct.getOrderSeq());
4710                 ndct.setVisible(dct.isVisible());
4711                 ndct.setCalculated(dct.isCalculated());
4712                 ndct.setColType(dct.getColType());
4713                 if(dct.getColType().equals(AppConstants.CT_HYPERLINK)) {
4714                         ndct.setHyperlinkURL(dct.getHyperlinkURL());
4715                         ndct.setHyperlinkType(dct.getHyperlinkType());
4716                         if(dct.getHyperlinkType().equals("IMAGE")) {
4717                                 ndct.setActionImg(dct.getActionImg());
4718                         }
4719                 }
4720
4721                 if(dct.getIndentation()!=null) {
4722                         ndct.setIndentation(dct.getIndentation());
4723                 }
4724
4725                 if (nvl(dct.getColFormat()).length() > 0)
4726                         ndct.setColFormat(dct.getColFormat());
4727                 ndct.setGroupBreak(dct.isGroupBreak());
4728                 ndct.setNowrap(dct.getNowrap());
4729                 if (nvl(dct.getYAxis()).length() > 0)
4730                         ndct.setYAxis(dct.getYAxis());
4731                 if (dct.getOrderBySeq()!=null &&  dct.getOrderBySeq() > 0)
4732                         ndct.setOrderBySeq(dct.getOrderBySeq());
4733                 if (nvl(dct.getOrderByAscDesc()).length() > 0)
4734                         ndct.setOrderByAscDesc(dct.getOrderByAscDesc());
4735                 if (nvl(dct.getDisplayTotal()).length() > 0)
4736                         ndct.setDisplayTotal(dct.getDisplayTotal());
4737                 if (nvl(dct.getColOnChart()).length() > 0)
4738                         ndct.setColOnChart(dct.getColOnChart());
4739                 if (dct.getChartSeq() !=null)
4740                         ndct.setChartSeq(dct.getChartSeq());
4741                 if (nvl(dct.getChartColor()).length() > 0)
4742                         ndct.setChartColor(dct.getChartColor());
4743                 if (nvl(dct.getChartLineType()).length() > 0)
4744                         ndct.setChartLineType(dct.getChartLineType());
4745                 ndct.setChartSeries((dct.isChartSeries()!=null && dct.isChartSeries().booleanValue())?true:false);
4746                 ndct.setIsRangeAxisFilled((dct.isIsRangeAxisFilled()!=null && dct.isIsRangeAxisFilled().booleanValue())?true:false);
4747
4748                 if (dct.isCreateInNewChart()!=null)
4749                         ndct.setCreateInNewChart(dct.isCreateInNewChart());
4750                 if (nvl(dct.getDrillDownType()).length() > 0)
4751                         ndct.setDrillDownType(dct.getDrillDownType());
4752                 ndct.setDrillinPoPUp(dct.isDrillinPoPUp()!=null?dct.isDrillinPoPUp():false);
4753                 if (nvl(dct.getDrillDownURL()).length() > 0)
4754                         ndct.setDrillDownURL(dct.getDrillDownURL());
4755                 if (nvl(dct.getDrillDownParams()).length() > 0)
4756                         ndct.setDrillDownParams(dct.getDrillDownParams());
4757                 if (nvl(dct.getComment()).length() > 0)
4758                         ndct.setComment(dct.getComment());
4759                 if (nvl(dct.getDependsOnFormField()).length() > 0)
4760                         ndct.setDependsOnFormField(dct.getDependsOnFormField());
4761                 if (dct.getColFilterList() != null) {
4762                         ColFilterList colFilterList = objFactory.createColFilterList();
4763                         ndct.setColFilterList(colFilterList);
4764
4765                         for (Iterator iter = dct.getColFilterList().getColFilter().iterator(); iter
4766                                         .hasNext();)
4767                                 colFilterList.getColFilter().add(
4768                                                 cloneColFilterType(objFactory, (ColFilterType) iter.next()));
4769                 } // if
4770
4771                 if (nvl(dct.getSemaphoreId()).length() > 0)
4772                         ndct.setSemaphoreId(dct.getSemaphoreId());
4773                 if (nvl(dct.getDbColType()).length() > 0)
4774                         ndct.setDbColType(dct.getDbColType());
4775                 else {
4776                         ndct.setDbColType(dct.getColType());
4777                         adjustColumnType(ndct);
4778                 }
4779                 if (nvl(dct.getChartGroup()).length() > 0)
4780                         ndct.setChartGroup(dct.getChartGroup());
4781
4782                 if (nvl(dct.getYAxis()).length() > 0)
4783                         ndct.setYAxis(dct.getYAxis());
4784
4785                 if (nvl(dct.getDependsOnFormField()).length() > 0)
4786                         ndct.setDependsOnFormField(dct.getDependsOnFormField());
4787
4788
4789
4790                 if(nvl(dct.getNowrap()).length() > 0)
4791                         ndct.setNowrap(dct.getNowrap());
4792
4793                 if(dct.getIndentation()!=null) {
4794                         ndct.setIndentation(dct.getIndentation());
4795                 }
4796
4797                 ndct.setEnhancedPagination((dct.isEnhancedPagination()!=null && dct.isEnhancedPagination().booleanValue())?true:false);
4798                 if(nvl(dct.getDataMiningCol()).length() > 0)
4799                         ndct.setDataMiningCol(dct.getDataMiningCol());
4800
4801                 ndct.setColId(dct.getColId());
4802
4803                 // ndct.setSemaphoreId(nvl(dct.getSemaphoreId()));
4804                 // if(nvl(dct.getDbColType()).length()>0)
4805                 // ndct.setDbColType(dct.getDbColType());
4806                 return ndct;
4807         } // cloneDataColumnType
4808
4809         public DataSourceType cloneDataSourceType(ObjectFactory objFactory, DataSourceType dst)
4810                         throws JAXBException {
4811                 DataSourceType ndst = objFactory.createDataSourceType();
4812
4813                 ndst.setTableName(dst.getTableName());
4814                 ndst.setTablePK(dst.getTablePK());
4815                 ndst.setDisplayName(dst.getDisplayName());
4816                 if (nvl(dst.getRefTableId()).length() > 0)
4817                         ndst.setRefTableId(dst.getRefTableId());
4818                 if (nvl(dst.getRefDefinition()).length() > 0)
4819                         ndst.setRefDefinition(dst.getRefDefinition());
4820                 if (nvl(dst.getComment()).length() > 0)
4821                         ndst.setComment(dst.getComment());
4822                 DataColumnList dataColumnList = objFactory.createDataColumnList();
4823                 ndst.setDataColumnList(dataColumnList);
4824
4825                 for (Iterator iter = dst.getDataColumnList().getDataColumn().iterator(); iter
4826                                 .hasNext();)
4827                         dataColumnList.getDataColumn().add(
4828                                         cloneDataColumnType(objFactory, (DataColumnType) iter.next()));
4829                 ndst.setTableId(dst.getTableId());
4830
4831
4832                 return ndst;
4833         } // cloneDataSourceType
4834
4835         public CustomReportType cloneCustomReport() throws RaptorException {
4836                 ObjectFactory objFactory = new ObjectFactory();
4837                 CustomReportType ncr = objFactory.createCustomReportType();
4838
4839                 //CustomReport ncr = null;
4840                 try {
4841                         //ncr = (CustomReport) objFactory.createCustomReport(customReportType);
4842                         ncr.setReportName(cr.getReportName());
4843                         ncr.setReportDescr(cr.getReportDescr());
4844                         if (nvl(cr.getNumDashCols()).length() > 0)
4845                                 ncr.setNumDashCols(cr.getNumDashCols());
4846                         if (nvl(cr.getDashboardLayoutHTML()).length() > 0)
4847                                 ncr.setDashboardLayoutHTML(cr.getDashboardLayoutHTML());
4848                         if (nvl(cr.getDbInfo()).length() > 0)
4849                                 ncr.setDbInfo(cr.getDbInfo());
4850                         ncr.setChartType(cr.getChartType());
4851                         if (nvl(cr.getChartTypeFixed()).length() > 0)
4852                                 ncr.setChartTypeFixed(cr.getChartTypeFixed());
4853                         if (nvl(cr.getChartMultiSeries()).length() > 0)
4854                                 ncr.setChartMultiSeries(cr.getChartMultiSeries());
4855                         if (nvl(cr.getChartLeftAxisLabel()).length() > 0)
4856                                 ncr.setChartLeftAxisLabel(cr.getChartLeftAxisLabel());
4857                         if (nvl(cr.getChartRightAxisLabel()).length() > 0)
4858                                 ncr.setChartRightAxisLabel(cr.getChartRightAxisLabel());
4859                         if (nvl(cr.getChartWidth()).length() > 0)
4860                                 ncr.setChartWidth(cr.getChartWidth());
4861                         if (nvl(cr.getChartHeight()).length() > 0)
4862                                 ncr.setChartHeight(cr.getChartHeight());
4863                         ncr.setShowChartTitle(cr.isShowChartTitle());
4864                         ncr.setPublic(cr.isPublic());
4865                         ncr.setHideFormFieldAfterRun(cr.isHideFormFieldAfterRun());
4866                         ncr.setCreateId(cr.getCreateId());
4867                         ncr.setCreateDate(cr.getCreateDate());
4868                         if (nvl(cr.getReportSQL()).length() > 0)
4869                                 ncr.setReportSQL(cr.getReportSQL());
4870                         if (nvl(cr.getReportTitle()).length() > 0)
4871                                 ncr.setReportTitle(cr.getReportTitle());
4872                         if (nvl(cr.getReportSubTitle()).length() > 0)
4873                                 ncr.setReportSubTitle(cr.getReportSubTitle());
4874                         if (nvl(cr.getReportHeader()).length() > 0)
4875                                 ncr.setReportHeader(cr.getReportHeader());
4876                         if (cr.getFrozenColumns()!=null)
4877                                 ncr.setFrozenColumns(cr.getFrozenColumns());
4878                         if (nvl(cr.getPdfImgLogo()).length()>0)
4879                                 ncr.setPdfImgLogo(cr.getPdfImgLogo());
4880                         if (nvl(cr.getEmptyMessage()).length()>0)
4881                                 ncr.setEmptyMessage(cr.getEmptyMessage());
4882                         if (nvl(cr.getWidthNoColumn()).length()>0)
4883                                 ncr.setWidthNoColumn(cr.getWidthNoColumn());
4884                         if (nvl(cr.getDataGridAlign()).length()>0)
4885                                 ncr.setDataGridAlign(cr.getDataGridAlign());
4886
4887                         if (nvl(cr.getReportFooter()).length() > 0)
4888                                 ncr.setReportFooter(cr.getReportFooter());
4889                         if (nvl(cr.getNumFormCols()).length() > 0)
4890                                 ncr.setNumFormCols(cr.getNumFormCols());
4891                         if (nvl(cr.getDisplayOptions()).length() > 0)
4892                                 ncr.setDisplayOptions(cr.getDisplayOptions());
4893                         if (nvl(cr.getDataContainerHeight()).length() > 0)
4894                                 ncr.setDataContainerHeight(cr.getDataContainerHeight());
4895                         if (nvl(cr.getDataContainerWidth()).length() > 0)
4896                                 ncr.setDataContainerWidth(cr.getDataContainerWidth());
4897                         if (nvl(cr.getAllowSchedule()).length() > 0)
4898                                 ncr.setAllowSchedule(cr.getAllowSchedule());
4899                         if (nvl(cr.getTopDown()).length() > 0)
4900                                 ncr.setTopDown(cr.getTopDown());
4901                         if (nvl(cr.getSizedByContent()).length() > 0)
4902                                 ncr.setSizedByContent(cr.getSizedByContent());
4903                         if (nvl(cr.getComment()).length() > 0)
4904                                 ncr.setComment(cr.getComment());
4905                         if (nvl(cr.getDashboardOptions()).length()>0)
4906                                 ncr.setDashboardOptions(cr.getDashboardOptions());
4907
4908                         if(cr.isDashboardType()!=null)
4909                                 ncr.setDashboardType(cr.isDashboardType());
4910                         if(cr.isReportInNewWindow()!=null)
4911                                 ncr.setReportInNewWindow(cr.isReportInNewWindow());
4912                         ncr.setDisplayFolderTree(cr.isDisplayFolderTree());
4913                         if (cr.getDashBoardReports() == null) {
4914                                 if (cr.getMaxRowsInExcelDownload()!=null && cr.getMaxRowsInExcelDownload()>0)
4915                                         ncr.setMaxRowsInExcelDownload(cr.getMaxRowsInExcelDownload());
4916                         }
4917
4918                         if (nvl(cr.getJavascriptElement()).length()>0)
4919                                 ncr.setJavascriptElement(cr.getJavascriptElement());
4920                         if (nvl(cr.getFolderId()).length()>0)
4921                                 ncr.setFolderId(cr.getFolderId());
4922                         ncr.setDrillURLInPoPUpPresent((cr.isDrillURLInPoPUpPresent()!=null && cr.isDrillURLInPoPUpPresent().booleanValue())?true:false);
4923
4924                         if (nvl(cr.getIsOneTimeScheduleAllowed()).length()>0)
4925                                 ncr.setIsOneTimeScheduleAllowed(cr.getIsOneTimeScheduleAllowed());
4926                         if (nvl(cr.getIsHourlyScheduleAllowed()).length()>0)
4927                                 ncr.setIsHourlyScheduleAllowed(cr.getIsHourlyScheduleAllowed());
4928                         if (nvl(cr.getIsDailyScheduleAllowed()).length()>0)
4929                                 ncr.setIsDailyScheduleAllowed(cr.getIsDailyScheduleAllowed());
4930                         if (nvl(cr.getIsDailyMFScheduleAllowed()).length()>0)
4931                                 ncr.setIsDailyMFScheduleAllowed(cr.getIsDailyMFScheduleAllowed());
4932                         if (nvl(cr.getIsWeeklyScheduleAllowed()).length()>0)
4933                                 ncr.setIsWeeklyScheduleAllowed(cr.getIsWeeklyScheduleAllowed());
4934                         if (nvl(cr.getIsMonthlyScheduleAllowed()).length()>0)
4935                                 ncr.setIsMonthlyScheduleAllowed(cr.getIsMonthlyScheduleAllowed());
4936
4937                         ncr.setPageSize(cr.getPageSize());
4938                         ncr.setReportType(cr.getReportType());
4939
4940
4941                         DataSourceList dataSourceList = objFactory.createDataSourceList();
4942                         ncr.setDataSourceList(dataSourceList);
4943
4944                         for (Iterator iter = cr.getDataSourceList().getDataSource().iterator(); iter.hasNext();) {
4945                                 dataSourceList.getDataSource().add(
4946                                                 cloneDataSourceType(objFactory, (DataSourceType) iter.next()));
4947                         }
4948
4949                         if (cr.getFormFieldList() != null) {
4950                                 FormFieldList formFieldList = objFactory.createFormFieldList();
4951                                 ncr.setFormFieldList(formFieldList);
4952                                 ncr.getFormFieldList().setComment(formFieldList.getComment());
4953
4954                                 for (Iterator iter = cr.getFormFieldList().getFormField().iterator(); iter
4955                                                 .hasNext();)
4956                                         formFieldList.getFormField().add(
4957                                                         cloneFormFieldType(objFactory, (FormFieldType) iter.next()));
4958                                 formFieldList.setComment(cr.getFormFieldList().getComment());
4959                         } // if
4960
4961                         if (cr.getJavascriptList() != null) {
4962                                 JavascriptList javascriptList = objFactory.createJavascriptList();
4963                                 ncr.setJavascriptList(javascriptList);
4964
4965                                 for (Iterator iter = cr.getJavascriptList().getJavascriptItem().iterator(); iter
4966                                                 .hasNext();)
4967                                         javascriptList.getJavascriptItem().add(
4968                                                         cloneJavascriptType(objFactory, (JavascriptItemType) iter.next()));
4969                         } // if
4970
4971                         if (cr.getSemaphoreList() != null) {
4972                                 SemaphoreList semaphoreList = objFactory.createSemaphoreList();
4973                                 ncr.setSemaphoreList(semaphoreList);
4974
4975                                 for (Iterator iter = cr.getSemaphoreList().getSemaphore().iterator(); iter
4976                                                 .hasNext();) {
4977                                         semaphoreList.getSemaphore().add(
4978                                                         cloneSemaphoreType(objFactory, (SemaphoreType) iter.next()));
4979                                 }
4980                         } // if
4981
4982                         if (nvl(cr.getDashboardOptions()).length()>0)
4983                                 ncr.setDashboardOptions(cr.getDashboardOptions());
4984                         if(cr.isDashboardType()!=null)
4985                                 ncr.setDashboardType(cr.isDashboardType());
4986                         if(cr.isReportInNewWindow()!=null)
4987                                 ncr.setReportInNewWindow(cr.isReportInNewWindow());
4988                         ncr.setDisplayFolderTree(cr.isDisplayFolderTree());
4989                         if (cr.getDashBoardReports() == null) {
4990                                 if (cr.getMaxRowsInExcelDownload()!=null && cr.getMaxRowsInExcelDownload()>0)
4991                                         ncr.setMaxRowsInExcelDownload(cr.getMaxRowsInExcelDownload());
4992                         }
4993
4994                         if (cr.getDashBoardReports() != null) {
4995                                 DashboardReports dashboardReports = objFactory.createDashboardReports();
4996                                 ncr.setDashBoardReports(dashboardReports);
4997
4998                                 for (Iterator iter = cr.getDashBoardReports().getReportsList().iterator(); iter
4999                                                 .hasNext();) {
5000                                         dashboardReports.getReportsList().add(
5001                                                         cloneDashboardType(objFactory, (Reports) iter.next()));
5002                                 }
5003                         } // if
5004
5005                         if (cr.getChartAdditionalOptions() != null) {
5006                                 ChartAdditionalOptions chartAdditionalOptions = objFactory.createChartAdditionalOptions();
5007                                 if(nvl(cr.getChartAdditionalOptions().getChartMultiplePieOrder()).length()>0)
5008                                         chartAdditionalOptions.setChartMultiplePieOrder(cr.getChartAdditionalOptions().getChartMultiplePieOrder());
5009                                 if(nvl(cr.getChartAdditionalOptions().getChartMultiplePieLabelDisplay()).length()>0)
5010                                         chartAdditionalOptions.setChartMultiplePieLabelDisplay(cr.getChartAdditionalOptions().getChartMultiplePieLabelDisplay());
5011
5012                                 if(nvl(cr.getChartAdditionalOptions().getChartOrientation()).length()>0)
5013                                         chartAdditionalOptions.setChartOrientation(cr.getChartAdditionalOptions().getChartOrientation());
5014                                 if(nvl(cr.getChartAdditionalOptions().getSecondaryChartRenderer()).length()>0)
5015                                         chartAdditionalOptions.setSecondaryChartRenderer(cr.getChartAdditionalOptions().getSecondaryChartRenderer());
5016
5017                                 if(nvl(cr.getChartAdditionalOptions().getChartDisplay()).length()>0)
5018                                         chartAdditionalOptions.setChartDisplay(cr.getChartAdditionalOptions().getChartDisplay());
5019                                 if(nvl(cr.getChartAdditionalOptions().getHideToolTips()).length()>0)
5020                                         chartAdditionalOptions.setHideToolTips(cr.getChartAdditionalOptions().getHideToolTips());
5021                                 if(nvl(cr.getChartAdditionalOptions().getHidechartLegend()).length()>0)
5022                                         chartAdditionalOptions.setHidechartLegend(cr.getChartAdditionalOptions().getHidechartLegend());
5023                                 if(nvl(cr.getChartAdditionalOptions().getLegendPosition()).length()>0)
5024                                         chartAdditionalOptions.setLegendPosition(cr.getChartAdditionalOptions().getLegendPosition());
5025                                 if(nvl(cr.getChartAdditionalOptions().getLabelAngle()).length()>0)
5026                                         chartAdditionalOptions.setLabelAngle(cr.getChartAdditionalOptions().getLabelAngle());
5027
5028                                 if(nvl(cr.getChartAdditionalOptions().getIntervalFromdate()).length()>0)
5029                                         chartAdditionalOptions.setIntervalFromdate(cr.getChartAdditionalOptions().getIntervalFromdate());
5030                                 if(nvl(cr.getChartAdditionalOptions().getIntervalTodate()).length()>0)
5031                                         chartAdditionalOptions.setIntervalTodate(cr.getChartAdditionalOptions().getIntervalTodate());
5032                                 if(nvl(cr.getChartAdditionalOptions().getIntervalLabel()).length()>0)
5033                                         chartAdditionalOptions.setIntervalLabel(cr.getChartAdditionalOptions().getIntervalLabel());
5034
5035                                 if(nvl(cr.getChartAdditionalOptions().getLastSeriesALineChart()).length()>0)
5036                                         chartAdditionalOptions.setLastSeriesALineChart(cr.getChartAdditionalOptions().getLastSeriesALineChart());
5037                                 if(nvl(cr.getChartAdditionalOptions().getLastSeriesABarChart()).length()>0)
5038                                         chartAdditionalOptions.setLastSeriesABarChart(cr.getChartAdditionalOptions().getLastSeriesABarChart());
5039
5040                                 if(nvl(cr.getChartAdditionalOptions().getMaxLabelsInDomainAxis()).length()>0)
5041                                         chartAdditionalOptions.setMaxLabelsInDomainAxis(cr.getChartAdditionalOptions().getMaxLabelsInDomainAxis());
5042                                 if(nvl(cr.getChartAdditionalOptions().getLinearRegression()).length()>0)
5043                                         chartAdditionalOptions.setLinearRegression(cr.getChartAdditionalOptions().getLinearRegression());
5044                                 if(nvl(cr.getChartAdditionalOptions().getLinearRegressionColor()).length()>0)
5045                                         chartAdditionalOptions.setLinearRegressionColor(cr.getChartAdditionalOptions().getLinearRegressionColor());
5046                                 if(nvl(cr.getChartAdditionalOptions().getExponentialRegressionColor()).length()>0)
5047                                         chartAdditionalOptions.setExponentialRegressionColor(cr.getChartAdditionalOptions().getExponentialRegressionColor());
5048                                 if(nvl(cr.getChartAdditionalOptions().getMaxRegression()).length()>0)
5049                                         chartAdditionalOptions.setMaxRegression(cr.getChartAdditionalOptions().getMaxRegression());
5050                                 if(nvl(cr.getChartAdditionalOptions().getRangeAxisUpperLimit()).length()>0)
5051                                         chartAdditionalOptions.setRangeAxisUpperLimit(cr.getChartAdditionalOptions().getRangeAxisUpperLimit());
5052                                 if(nvl(cr.getChartAdditionalOptions().getRangeAxisLowerLimit()).length()>0)
5053                                         chartAdditionalOptions.setRangeAxisLowerLimit(cr.getChartAdditionalOptions().getRangeAxisLowerLimit());
5054                                 if(nvl(cr.getChartAdditionalOptions().getOverlayItemValueOnStackBar()).length()>0)
5055                                         chartAdditionalOptions.setOverlayItemValueOnStackBar(cr.getChartAdditionalOptions().getOverlayItemValueOnStackBar());
5056                                 chartAdditionalOptions.setAnimate((cr.getChartAdditionalOptions().isAnimate()!=null && cr.getChartAdditionalOptions().isAnimate().booleanValue())?true:false);
5057
5058                                 if(nvl(cr.getChartAdditionalOptions().getKeepDomainAxisValueAsString()).length()>0)
5059                                         chartAdditionalOptions.setKeepDomainAxisValueAsString(cr.getChartAdditionalOptions().getKeepDomainAxisValueAsString());
5060
5061
5062                                 // Animate
5063                                 chartAdditionalOptions.setAnimateAnimatedChart((cr.getChartAdditionalOptions().isAnimateAnimatedChart()!=null && cr.getChartAdditionalOptions().isAnimateAnimatedChart().booleanValue())?true:false);
5064                                 chartAdditionalOptions.setStacked((cr.getChartAdditionalOptions().isStacked()!=null && cr.getChartAdditionalOptions().isStacked().booleanValue())?true:false);
5065                                 chartAdditionalOptions.setBarControls((cr.getChartAdditionalOptions().isBarControls()!=null && cr.getChartAdditionalOptions().isBarControls().booleanValue())?true:false);
5066                                 chartAdditionalOptions.setXAxisDateType((cr.getChartAdditionalOptions().isXAxisDateType()!=null && cr.getChartAdditionalOptions().isXAxisDateType().booleanValue())?true:false);
5067                                 chartAdditionalOptions.setLessXaxisTickers((cr.getChartAdditionalOptions().isLessXaxisTickers()!=null && cr.getChartAdditionalOptions().isLessXaxisTickers().booleanValue())?true:false);
5068                                 chartAdditionalOptions.setTimeAxis((cr.getChartAdditionalOptions().isTimeAxis()!=null && cr.getChartAdditionalOptions().isTimeAxis().booleanValue())?true:false);
5069
5070                                 if(nvl(cr.getChartAdditionalOptions().getTimeSeriesRender()).length()>0)
5071                                         chartAdditionalOptions.setTimeSeriesRender(cr.getChartAdditionalOptions().getTimeSeriesRender());
5072
5073                                 chartAdditionalOptions.setMultiSeries((cr.getChartAdditionalOptions().isMultiSeries()!=null && cr.getChartAdditionalOptions().isMultiSeries().booleanValue())?true:false);
5074
5075                                 chartAdditionalOptions.setTopMargin(cr.getChartAdditionalOptions().getTopMargin()!=null?cr.getChartAdditionalOptions().getTopMargin():new Integer(30));
5076                                 chartAdditionalOptions.setBottomMargin(cr.getChartAdditionalOptions().getBottomMargin()!=null?cr.getChartAdditionalOptions().getBottomMargin():new Integer(50));
5077                                 chartAdditionalOptions.setLeftMargin(cr.getChartAdditionalOptions().getLeftMargin()!=null?cr.getChartAdditionalOptions().getLeftMargin():new Integer(100));
5078                                 chartAdditionalOptions.setRightMargin(cr.getChartAdditionalOptions().getRightMargin()!=null?cr.getChartAdditionalOptions().getRightMargin():new Integer(60));
5079
5080
5081                                 ncr.setChartAdditionalOptions(chartAdditionalOptions);
5082                         } // if
5083
5084                         if (nvl(cr.getJavascriptElement()).length()>0)
5085                                 ncr.setJavascriptElement(cr.getJavascriptElement());
5086                         if (nvl(cr.getFolderId()).length()>0)
5087                                 ncr.setFolderId(cr.getFolderId());
5088
5089                         if (cr.getChartDrillOptions() != null) {
5090                                 ChartDrillOptions chartDrillOptions = objFactory.createChartDrillOptions();
5091
5092                                 if(nvl(cr.getChartDrillOptions().getDrillReportId()).length()>0)
5093                                         chartDrillOptions.setDrillReportId(cr.getChartDrillOptions().getDrillReportId());
5094
5095                                 for (Iterator iter = cr.getChartDrillOptions().getTargetFormfield().iterator(); iter
5096                                                 .hasNext();) {
5097                                         chartDrillOptions.getTargetFormfield().add(
5098                                                                         cloneChartDrillFormfield(objFactory, (ChartDrillFormfield)iter.next()));
5099
5100                                                 }
5101
5102                                 if(nvl(cr.getChartDrillOptions().getDrillXAxisFormField()).length()>0)
5103                                         chartDrillOptions.setDrillXAxisFormField(cr.getChartDrillOptions().getDrillXAxisFormField());
5104                                 if(nvl(cr.getChartDrillOptions().getDrillYAxisFormField()).length()>0)
5105                                         chartDrillOptions.setDrillYAxisFormField(cr.getChartDrillOptions().getDrillYAxisFormField());
5106                                 if(nvl(cr.getChartDrillOptions().getDrillSeriesFormField()).length()>0)
5107                                         chartDrillOptions.setDrillSeriesFormField(cr.getChartDrillOptions().getDrillSeriesFormField());
5108
5109
5110                                 ncr.setChartDrillOptions(chartDrillOptions);
5111                         }
5112
5113                         if (nvl(cr.getIsOneTimeScheduleAllowed()).length()>0)
5114                                 ncr.setIsOneTimeScheduleAllowed(cr.getIsOneTimeScheduleAllowed());
5115                         if (nvl(cr.getIsHourlyScheduleAllowed()).length()>0)
5116                                 ncr.setIsHourlyScheduleAllowed(cr.getIsHourlyScheduleAllowed());
5117                         if (nvl(cr.getIsDailyScheduleAllowed()).length()>0)
5118                                 ncr.setIsDailyScheduleAllowed(cr.getIsDailyScheduleAllowed());
5119                         if (nvl(cr.getIsDailyMFScheduleAllowed()).length()>0)
5120                                 ncr.setIsDailyMFScheduleAllowed(cr.getIsDailyMFScheduleAllowed());
5121                         if (nvl(cr.getIsWeeklyScheduleAllowed()).length()>0)
5122                                 ncr.setIsWeeklyScheduleAllowed(cr.getIsWeeklyScheduleAllowed());
5123                         if (nvl(cr.getIsMonthlyScheduleAllowed()).length()>0)
5124                                 ncr.setIsMonthlyScheduleAllowed(cr.getIsMonthlyScheduleAllowed());
5125
5126                         ncr.setPageSize(cr.getPageSize());
5127                         ncr.setReportType(cr.getReportType());
5128
5129                         if (cr.getReportMap() != null){
5130                                 ReportMap repMap = objFactory.createReportMap();
5131                                 if(nvl(cr.getReportMap().getMarkerColor()).length()>0)
5132                                         repMap.setMarkerColor(cr.getReportMap().getMarkerColor());
5133                                 if(nvl(cr.getReportMap().getUseDefaultSize()).length()>0)
5134                                         repMap.setUseDefaultSize(cr.getReportMap().getUseDefaultSize());
5135                                 if(nvl(cr.getReportMap().getHeight()).length()>0)
5136                                         repMap.setHeight(cr.getReportMap().getHeight());
5137                                 if(nvl(cr.getReportMap().getWidth()).length()>0)
5138                                         repMap.setWidth(cr.getReportMap().getWidth());
5139                                 if(nvl(cr.getReportMap().getIsMapAllowedYN()).length()>0)
5140                                         repMap.setIsMapAllowedYN(cr.getReportMap().getIsMapAllowedYN());
5141                                 if(nvl(cr.getReportMap().getAddAddressInDataYN()).length()>0)
5142                                         repMap.setAddAddressInDataYN(cr.getReportMap().getAddAddressInDataYN());
5143                                 if(nvl(cr.getReportMap().getAddressColumn()).length()>0)
5144                                         repMap.setAddressColumn(cr.getReportMap().getAddressColumn());
5145                                 if(nvl(cr.getReportMap().getDataColumn()).length()>0)
5146                                         repMap.setDataColumn(cr.getReportMap().getDataColumn());
5147                                 if(nvl(cr.getReportMap().getDefaultMapType()).length()>0)
5148                                         repMap.setDefaultMapType(cr.getReportMap().getDefaultMapType());
5149                                 if(nvl(cr.getReportMap().getLatColumn()).length()>0)
5150                                         repMap.setLatColumn(cr.getReportMap().getLatColumn());
5151                                 if(nvl(cr.getReportMap().getLongColumn()).length()>0)
5152                                         repMap.setLongColumn(cr.getReportMap().getLongColumn());
5153                                 if(nvl(cr.getReportMap().getColorColumn()).length()>0)
5154                                         repMap.setColorColumn(cr.getReportMap().getColorColumn());
5155                                 if(nvl(cr.getReportMap().getLegendColumn()).length()>0)
5156                                         repMap.setLegendColumn(cr.getReportMap().getLegendColumn());
5157
5158
5159                                 for (Iterator iter = cr.getReportMap().getMarkers().iterator(); iter
5160                                 .hasNext();) {
5161                                         repMap.getMarkers().add(
5162                                                         cloneMarkerType(objFactory, (Marker)iter.next()));
5163
5164                                 }
5165
5166                                 ncr.setReportMap(repMap);
5167                         }
5168
5169
5170
5171                 } catch (JAXBException ex) { // try
5172                         throw new RaptorException(ex.getMessage(), ex.getCause());
5173                 }
5174
5175                 return ncr;
5176         } // cloneCustomReport
5177
5178         /** ************************************************************************************************* */
5179
5180         public void printFormatType(FormatType ft) {
5181         System.out.println("------------------------------------------------");
5182                 System.out.println("Semaphore Col Format");
5183                 System.out.println("------------------------------------------------");
5184                 System.out.println("FormatId: [" + ft.getFormatId() + "]");
5185                 System.out.println("LessThanValue: [" + ft.getLessThanValue() + "]");
5186                 System.out.println("Expression: [" + ft.getExpression() + "]");
5187                 System.out.println("Bold: [" + ft.isBold() + "]");
5188                 System.out.println("Italic: [" + ft.isItalic() + "]");
5189                 System.out.println("Underline: [" + ft.isUnderline() + "]");
5190                 System.out.println("BgColor: [" + ft.getBgColor() + "]");
5191                 System.out.println("FontColor: [" + ft.getFontColor() + "]");
5192                 System.out.println("FontFace: [" + ft.getFontFace() + "]");
5193                 System.out.println("FontSize: [" + ft.getFontSize() + "]");
5194                 System.out.println("Alignment: [" + ft.getAlignment() + "]");
5195                 System.out.println("Comment: [" + ft.getComment() + "]");
5196                 System.out.println("------------------------------------------------");
5197         } // printFormatType
5198
5199         public void printSemaphoreType(SemaphoreType st) {
5200                 System.out.println("------------------------------------------------");
5201                 System.out.println("Semaphore");
5202                 System.out.println("------------------------------------------------");
5203                 System.out.println("SemaphoreId: [" + st.getSemaphoreId() + "]");
5204                 System.out.println("SemaphoreName: [" + st.getSemaphoreName() + "]");
5205                 System.out.println("SemaphoreType: [" + st.getSemaphoreType() + "]");
5206                 System.out.println("Comment: [" + st.getComment() + "]");
5207
5208                 if (st.getFormatList() != null)
5209                         for (Iterator iter = st.getFormatList().getFormat().iterator(); iter.hasNext();)
5210                                 printFormatType((FormatType) iter.next());
5211
5212                 System.out.println("------------------------------------------------");
5213         } // printSemaphoreType
5214
5215         public void printFormFieldType(FormFieldType fft) {
5216                 System.out.println("------------------------------------------------");
5217                 System.out.println("Form Field");
5218                 System.out.println("------------------------------------------------");
5219                 System.out.println("FieldId: [" + fft.getFieldId() + "]");
5220                 System.out.println("ColId: [" + fft.getColId() + "]");
5221                 System.out.println("FieldName: [" + fft.getFieldName() + "]");
5222                 System.out.println("FieldType: [" + fft.getFieldType() + "]");
5223                 System.out.println("ValidationType: [" + fft.getValidationType() + "]");
5224                 System.out.println("Mandatory: [" + fft.getMandatory() + "]");
5225                 System.out.println("DefaultValue: [" + fft.getDefaultValue() + "]");
5226                 System.out.println("OrderBySeq: [" + fft.getOrderBySeq() + "]");
5227                 System.out.println("FieldSQL: [" + fft.getFieldSQL() + "]");
5228                 System.out.println("Comment: [" + fft.getComment() + "]");
5229                 if (fft.getPredefinedValueList() != null)
5230                         for (Iterator iter = fft.getPredefinedValueList().getPredefinedValue().iterator(); iter
5231                                         .hasNext();)
5232                                 System.out.println("PredefinedValues: [" + ((String) iter.next()) + "]");
5233
5234                 System.out.println("------------------------------------------------");
5235         } // printFormFieldType
5236
5237         public void printColFilterType(ColFilterType cft) {
5238                 System.out.println("------------------------------------------------");
5239                 System.out.println("Col Filter");
5240                 System.out.println("------------------------------------------------");
5241                 System.out.println("ColId: [" + cft.getColId() + "]");
5242                 System.out.println("FilterSeq: [" + cft.getFilterSeq() + "]");
5243                 System.out.println("JoinCondition: [" + cft.getJoinCondition() + "]");
5244                 System.out.println("OpenBrackets: [" + cft.getOpenBrackets() + "]");
5245                 System.out.println("Expression: [" + cft.getExpression() + "]");
5246                 System.out.println("ArgType: [" + cft.getArgType() + "]");
5247                 System.out.println("ArgValue: [" + cft.getArgValue() + "]");
5248                 System.out.println("CloseBrackets: [" + cft.getCloseBrackets() + "]");
5249                 System.out.println("Comment: [" + cft.getComment() + "]");
5250                 System.out.println("------------------------------------------------");
5251         } // printColFilterType
5252
5253         public void printDataColumnType(DataColumnType dct) {
5254                 System.out.println("------------------------------------------------");
5255                 System.out.println("Data Column");
5256                 System.out.println("------------------------------------------------");
5257                 System.out.println("ColId: [" + dct.getColId() + "]");
5258                 System.out.println("TableId: [" + dct.getTableId() + "]");
5259                 System.out.println("DbColName: [" + dct.getDbColName() + "]");
5260                 System.out.println("CrossTabValue: [" + dct.getCrossTabValue() + "]");
5261                 System.out.println("ColName: [" + dct.getColName() + "]");
5262                 System.out.println("DisplayName: [" + dct.getDisplayName() + "]");
5263                 System.out.println("DisplayWidth: [" + dct.getDisplayWidth() + "]");
5264                 System.out.println("DisplayAlignment: [" + dct.getDisplayAlignment() + "]");
5265                 System.out.println("DisplayHeaderAlignment: [" + dct.getDisplayHeaderAlignment() + "]");
5266                 System.out.println("OrderSeq(): [" + dct.getOrderSeq() + "]");
5267                 System.out.println("Visible: [" + dct.isVisible() + "]");
5268                 System.out.println("Calculated: [" + dct.isCalculated() + "]");
5269                 System.out.println("ColType: [" + dct.getColType() + "]");
5270                 System.out.println("ColFormat: [" + dct.getColFormat() + "]");
5271                 System.out.println("GroupBreak: [" + dct.isGroupBreak() + "]");
5272                 System.out.println("OrderBySeq: [" + dct.getOrderBySeq() + "]");
5273                 System.out.println("OrderByAscDesc: [" + dct.getOrderByAscDesc() + "]");
5274                 System.out.println("DisplayTotal: [" + dct.getDisplayTotal() + "]");
5275                 System.out.println("ColOnChart: [" + dct.getColOnChart() + "]");
5276                 System.out.println("ChartSeq: [" + dct.getChartSeq() + "]");
5277                 System.out.println("ChartColor: [" + dct.getChartColor() + "]");
5278                 System.out.println("DrillDownType: [" + dct.getDrillDownType() + "]");
5279                 System.out.println("DrillDownURL: [" + dct.getDrillDownURL() + "]");
5280                 System.out.println("DrillDownParams: [" + dct.getDrillDownParams() + "]");
5281                 System.out.println("Comment: [" + dct.getComment() + "]");
5282
5283                 if (dct.getColFilterList() != null)
5284                         for (Iterator iter = dct.getColFilterList().getColFilter().iterator(); iter
5285                                         .hasNext();)
5286                                 printColFilterType((ColFilterType) iter.next());
5287
5288                 System.out.println("SemaphoreId: [" + dct.getSemaphoreId() + "]");
5289                 System.out.println("DbColType: [" + dct.getDbColType() + "]");
5290                 System.out.println("------------------------------------------------");
5291         } // printDataColumnType
5292
5293         public void printDataSourceType(DataSourceType dst) {
5294                 System.out.println("------------------------------------------------");
5295                 System.out.println("Data Source");
5296                 System.out.println("------------------------------------------------");
5297                 System.out.println("TableId: [" + dst.getTableId() + "]");
5298                 System.out.println("TableName: [" + dst.getTableName() + "]");
5299                 System.out.println("TablePK: [" + dst.getTablePK() + "]");
5300                 System.out.println("DisplayName: [" + dst.getDisplayName() + "]");
5301                 System.out.println("RefTableId: [" + dst.getRefTableId() + "]");
5302                 System.out.println("RefDefinition: [" + dst.getRefDefinition() + "]");
5303                 System.out.println("Comment: [" + dst.getComment() + "]");
5304
5305                 for (Iterator iter = dst.getDataColumnList().getDataColumn().iterator(); iter
5306                                 .hasNext();)
5307                         printDataColumnType((DataColumnType) iter.next());
5308
5309                 System.out.println("------------------------------------------------");
5310         } // printDataSourceType
5311
5312         public void print() {
5313                 System.out.println("------------------------------------------------");
5314                 System.out.println("ReportWrapper object");
5315                 System.out.println("------------------------------------------------");
5316                 System.out.println("PageSize: [" + getPageSize() + "]");
5317                 System.out.println("ReportType: [" + getReportType() + "]");
5318                 System.out.println("ReportName: [" + getReportName() + "]");
5319                 System.out.println("ReportDescr: [" + getReportDescr() + "]");
5320                 System.out.println("ChartType: [" + getChartType() + "]");
5321                 System.out.println("ChartTypeFixed: [" + getChartTypeFixed() + "]");
5322                 //System.out.println("ChartLeftAxisLabel: [" + getChartLeftAxisLabel() + "]");
5323                 //System.out.println("ChartRightAxisLabel: [" + getChartRightAxisLabel() + "]");
5324                 System.out.println("ChartWidth: [" + getChartWidth() + "]");
5325                 System.out.println("ChartHeight: [" + getChartHeight() + "]");
5326                 System.out.println("Public: [" + isPublic() + "]");
5327                 System.out.println("CreateId: NOT USED ANYMORE[" + /* getCreateId()+ */"]");
5328                 System.out.println("CreateDate: NOT USED ANYMORE[" + /* getCreateDate()+ */"]");
5329                 System.out.println("ReportSQL: [" + getReportSQL() + "]");
5330                 System.out.println("ReportTitle: [" + getReportTitle() + "]");
5331                 System.out.println("DbInfo: [" + getDBInfo() + "]");
5332                 System.out.println("ReportSubTitle: [" + getReportSubTitle() + "]");
5333                 System.out.println("ReportHeader: [" + getReportHeader() + "]");
5334                 System.out.println("ReportFooter: [" + getReportFooter() + "]");
5335                 System.out.println("NumFormCols: [" + getNumFormCols() + "]");
5336                 System.out.println("DisplayOptions: [" + getDisplayOptions() + "]");
5337                 System.out.println("Comment: [" + getComment() + "]");
5338
5339                 for (Iterator iter = cr.getDataSourceList().getDataSource().iterator(); iter.hasNext();)
5340                         printDataSourceType((DataSourceType) iter.next());
5341
5342                 if (cr.getFormFieldList() != null)
5343                         for (Iterator iter = cr.getFormFieldList().getFormField().iterator(); iter
5344                                         .hasNext();)
5345                                 printFormFieldType((FormFieldType) iter.next());
5346
5347                 if (cr.getSemaphoreList() != null)
5348                         for (Iterator iter = cr.getSemaphoreList().getSemaphore().iterator(); iter
5349                                         .hasNext();)
5350                                 printSemaphoreType((SemaphoreType) iter.next());
5351
5352                 System.out.println("------------------------------------------------");
5353                 System.out.println("ReportWrapper object end");
5354                 System.out.println("------------------------------------------------");
5355         } // print
5356
5357         private int getIntValue(String value, int defaultValue) {
5358                 int iValue = defaultValue;
5359                 try {
5360                         iValue = Integer.parseInt(value);
5361                 } catch (Exception e) {
5362                 }
5363
5364                 return iValue;
5365         } // getIntValue
5366      public static String replaceNewLine( String strSource, String strFind, String chrReplace )
5367     {
5368         // buffer to hold the target string after replacement is done.
5369         StringBuffer sbfTemp = new StringBuffer();
5370
5371         try
5372         {
5373             // for each occurrence of strFind in strSource, replace it with chrReplace.
5374             int intIndex = strSource.indexOf( strFind, 0 );
5375
5376             // check if there is any instace of strFind in strSource
5377             if( intIndex >= 0 )
5378             {
5379                 // holds the index from where the search is supposed to happen.
5380                 int intStart = 0;
5381
5382                 // size of the source string
5383                 int intTotalSize = strSource.length();
5384
5385                 while( intStart < intTotalSize &&
5386                 ( ( intIndex = strSource.indexOf( strFind, intStart ) ) >= 0 ) )
5387                 {
5388                     // check if strFind is at the beginning... i.e., at index intStart
5389                     if( intIndex == intStart )
5390                     {
5391                         /*
5392                          * starts with strFind...just append chrReplace
5393                          * to the target
5394                          */
5395                         sbfTemp.append( chrReplace );
5396                     }
5397                     else
5398                     {
5399                         // append the sub-string...plus chrReplace
5400                         sbfTemp.append( strSource.substring( intStart,  intIndex ) );
5401                         sbfTemp.append( chrReplace );
5402                     }
5403
5404                     // advance string index
5405                     intStart = intIndex + strFind.length();
5406                 }
5407
5408                 // append the last portion of the source string.
5409                 sbfTemp.append( strSource.substring( intStart ) );
5410             }
5411             else
5412             {
5413                 // strFind not found... just copy the text as it is.
5414                 sbfTemp.append( strSource );
5415             }
5416         }
5417         catch( Exception expGeneral )
5418         {
5419             // in case of any exception, return the source string as it is.
5420             sbfTemp = new StringBuffer( strSource );
5421         }
5422
5423         return sbfTemp.toString();
5424     }
5425
5426     /*folder id*/
5427     public String getFolderId() {
5428                 return nvl(cr.getFolderId()).length()>0?cr.getFolderId():"NULL";
5429         }
5430     public void setFolderId(String folderId ) {
5431                 cr.setFolderId(folderId);
5432         }
5433
5434     public String addZero(String num) {
5435         int numInt = 0;
5436         try {
5437                 numInt = Integer.parseInt(num);
5438         }catch(NumberFormatException ex){
5439                 numInt = 0;
5440         }
5441         if(numInt < 10) return "0"+numInt;
5442         else return ""+numInt;
5443     }
5444
5445         public String getIsDailyMFScheduleAllowed() {
5446                 return cr.getIsDailyMFScheduleAllowed();
5447         }
5448
5449         public void setIsDailyMFScheduleAllowed(String isDailyMFScheduleAllowed) {
5450                 cr.setIsDailyMFScheduleAllowed(isDailyMFScheduleAllowed);
5451         }
5452
5453         public String getIsDailyScheduleAllowed() {
5454                 return cr.getIsDailyScheduleAllowed();
5455         }
5456
5457         public void setIsDailyScheduleAllowed(String isDailyScheduleAllowed) {
5458                 cr.setIsDailyScheduleAllowed(isDailyScheduleAllowed);
5459         }
5460
5461         public String getIsHourlyScheduleAllowed() {
5462                 return cr.getIsHourlyScheduleAllowed();
5463         }
5464
5465         public void setIsHourlyScheduleAllowed(String isHourlyScheduleAllowed) {
5466                 cr.setIsHourlyScheduleAllowed(isHourlyScheduleAllowed);
5467         }
5468
5469         public String getIsMonthlyScheduleAllowed() {
5470                 return cr.getIsMonthlyScheduleAllowed();
5471         }
5472
5473         public void setIsMonthlyScheduleAllowed(String isMonthlyScheduleAllowed) {
5474                 cr.setIsMonthlyScheduleAllowed(isMonthlyScheduleAllowed);
5475         }
5476
5477         public String getIsOneTimeScheduleAllowed() {
5478                 return cr.getIsOneTimeScheduleAllowed();
5479         }
5480
5481         public void setIsOneTimeScheduleAllowed(String isOneTimeScheduleAllowed) {
5482                 cr.setIsOneTimeScheduleAllowed(isOneTimeScheduleAllowed);
5483         }
5484
5485         public String getIsWeeklyScheduleAllowed() {
5486                 return cr.getIsWeeklyScheduleAllowed();
5487         }
5488
5489         public void setIsWeeklyScheduleAllowed(String isWeeklyScheduleAllowed) {
5490                 cr.setIsWeeklyScheduleAllowed(isWeeklyScheduleAllowed);
5491
5492         }
5493
5494         public static boolean isNull(String a) {
5495                 if ((a == null) || (a.length() == 0) || a.equalsIgnoreCase("null"))
5496                         return true;
5497                 else
5498                         return false;
5499         }
5500
5501         public int getDependsOnFormFieldFlag(DataColumnType dc, HashMap formValues) {
5502                 int flag = 0;
5503                 String fieldValue = "";
5504                 if(nvl(dc.getDependsOnFormField()).length()>0 && nvl(dc.getDependsOnFormField()).indexOf("[")!=-1) {
5505                         if(formValues != null) {
5506                                 Set set = formValues.entrySet();
5507                                 String value = "";
5508                                 for(Iterator iter1 = set.iterator(); iter1.hasNext(); ) {
5509                                         Map.Entry entry = (Entry) iter1.next();
5510                                         value = (String) entry.getValue();
5511                                         if (dc.getDependsOnFormField().equals("["+entry.getKey()+"]")) {
5512                                                 fieldValue = nvl(value);
5513
5514                                                 if (fieldValue.length()>0 && !fieldValue.equals("NULL")) {
5515                                                         flag = 0;
5516                                                 } else {
5517                                                         flag = 1;
5518                                                 }
5519
5520                                         }
5521                                 }
5522                         }
5523                 }
5524
5525                 return flag;
5526         }
5527
5528         /* Datamining Getter Setter */
5529
5530         public String getClassifier() {
5531                 return (cr.getDataminingOptions()!=null?cr.getDataminingOptions().getClassifier():"");
5532         }
5533
5534         public void setClassifier( String classifier) {
5535                 cr.getDataminingOptions().setClassifier(classifier);
5536         }
5537
5538
5539         public int getForecastingPeriod() {
5540                 return (cr.getDataminingOptions()!=null? new Integer(cr.getDataminingOptions().getForecastingUnits()).intValue():-1);
5541         }
5542
5543         public void setForecastingPeriod( String period) {
5544                 cr.getDataminingOptions().setForecastingUnits(period);
5545         }
5546
5547         public String getForecastingTimeFormat() {
5548                 return (cr.getDataminingOptions()!=null?cr.getDataminingOptions().getTimeformat():"");
5549         }
5550
5551         public void setForecastingTimeFormat( String format) {
5552                 cr.getDataminingOptions().setTimeformat(format);
5553         }
5554
5555         /**
5556          * Get Number of Columns to Frozen in Data Grid
5557          */
5558
5559         public int getFrozenColumns() {
5560                 return cr.getFrozenColumns()==null?0:cr.getFrozenColumns();
5561         }
5562
5563         public String getFrozenColumnId() {
5564                 int noOfColumns = cr.getFrozenColumns()==null?0:cr.getFrozenColumns();
5565                 if(noOfColumns != 0) {
5566                         List reportCols = getOnlyVisibleColumns();
5567                         int colIdx = 0;
5568                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
5569                                 ++colIdx;
5570                                 DataColumnType dc = (DataColumnType) iter.next();
5571                                 if(colIdx == noOfColumns) {
5572
5573                                         return dc.getColId();
5574                                 } else continue;
5575                         } // for
5576                         return "";
5577                 } else return "";
5578
5579         }
5580
5581         /**
5582          * Set Number of Columns to Frozen in Data Grid
5583          */
5584
5585         public void setFrozenColumns( int frozenColumns) {
5586                 cr.setFrozenColumns(frozenColumns);
5587         }
5588
5589         /**
5590          * @return the reportSQLWithRowNum for ZK Support
5591          */
5592         public String getReportSQLWithRowNum() {
5593                 return reportSQLWithRowNum;
5594         }
5595
5596         /**
5597          * @param reportSQLWithRowNum the reportSQLWithRowNum to set  for ZK Support
5598          */
5599         public void setReportSQLWithRowNum(String reportSQLWithRowNum) {
5600                 this.reportSQLWithRowNum = reportSQLWithRowNum;
5601         }
5602
5603         //used for Zk sort
5604         public void setReportSQLOnlyFirstPart(String reportSQLOnlyFirstPart) {
5605                 this.reportSQLOnlyFirstPart = reportSQLOnlyFirstPart;
5606         }
5607
5608         public String getReportSQLOnlyFirstPart() {
5609                 return this.reportSQLOnlyFirstPart;
5610         }
5611
5612         public String getTemplateFile() throws RaptorException {
5613                 return ReportLoader.getTemplateFile(getReportID());
5614         }
5615
5616         public String getPdfImg() {
5617                 return cr.getPdfImgLogo();
5618         }
5619
5620
5621         public String getEmptyMessage() {
5622                 String emptyMessage = cr.getEmptyMessage();
5623                 if(nvl(emptyMessage).length()<=0)
5624                         emptyMessage = Globals.getReportEmptyMessage();
5625                 return emptyMessage;
5626         }
5627
5628         public void setPdfImg(String img_loc) {
5629                 cr.setPdfImgLogo(img_loc);
5630         }
5631
5632         public void setEmptyMessage(String emptyMessage) {
5633                 cr.setEmptyMessage(emptyMessage);
5634         }
5635
5636         public void setDrillReportIdForChart(String reportId) {
5637                 //(cr.getChartDrillOptions()!=null)?cr.getChartDrillOptions().setDrillReportId():"";
5638                 cr.getChartDrillOptions().setDrillReportId(reportId);
5639         }
5640
5641         public String getDrillReportIdForChart() {
5642                 return (cr.getChartDrillOptions()!=null)?cr.getChartDrillOptions().getDrillReportId():"";
5643         }
5644
5645         public void setDrillXAxisFormField(String formField) {
5646                 //(cr.getChartDrillOptions()!=null)?cr.getChartDrillOptions().setDrillReportId():"";
5647                 cr.getChartDrillOptions().setDrillXAxisFormField(formField);
5648         }
5649
5650         public String getDrillXAxisFormField() {
5651                 return (cr.getChartDrillOptions()!=null)?cr.getChartDrillOptions().getDrillXAxisFormField():"";
5652         }
5653
5654         public void setDrillYAxisFormField(String formField) {
5655                 //(cr.getChartDrillOptions()!=null)?cr.getChartDrillOptions().setDrillReportId():"";
5656                 cr.getChartDrillOptions().setDrillYAxisFormField(formField);
5657         }
5658
5659         public String getDrillYAxisFormField() {
5660                 return (cr.getChartDrillOptions()!=null)?cr.getChartDrillOptions().getDrillYAxisFormField():"";
5661         }
5662
5663         public void setDrillSeriesFormField(String formField) {
5664                 //(cr.getChartDrillOptions()!=null)?cr.getChartDrillOptions().setDrillReportId():"";
5665                 cr.getChartDrillOptions().setDrillSeriesFormField(formField);
5666         }
5667
5668         public String getDrillSeriesFormField() {
5669                 return (cr.getChartDrillOptions()!=null)?cr.getChartDrillOptions().getDrillSeriesFormField():"";
5670         }
5671
5672         public boolean isEnhancedPaginationNeeded() {
5673                 List reportCols = getAllColumns();
5674
5675                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
5676                         DataColumnType dc = (DataColumnType) iter.next();
5677                         if (dc.isEnhancedPagination()!=null  && dc.isEnhancedPagination().booleanValue())
5678                                 return true;
5679                 } // for
5680                 return false;
5681         }
5682
5683         public DataColumnType getColumnWhichNeedEnhancedPagination() {
5684                 List reportCols = getAllColumns();
5685
5686                 for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
5687                         DataColumnType dc = (DataColumnType) iter.next();
5688                         if (dc.isEnhancedPagination()!=null  && dc.isEnhancedPagination().booleanValue())
5689                                 return dc;
5690                 } // for
5691                 return null;
5692         }
5693
5694         public void setDataGridAlign(String align) {
5695                 cr.setDataGridAlign(align);
5696         }
5697
5698
5699         public String getDataGridAlign() {
5700                 return (cr.getDataGridAlign()!=null)?cr.getDataGridAlign():"left";
5701         }
5702
5703         public void setWidthNoColumn(String width) {
5704                 cr.setWidthNoColumn(width);
5705         }
5706
5707
5708         public String getWidthNoColumn() {
5709                 return (cr.getWidthNoColumn()!=null)?cr.getWidthNoColumn():"30px";
5710         }
5711
5712     public void setWholeSQL(String sql) {
5713         wholeSQL = sql;
5714      }
5715         public String getWholeSQL() {
5716            return wholeSQL;
5717         }
5718
5719 } // ReportWrapper