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

   1 /*
   2  * ============LICENSE_START==========================================
   3  * ONAP Portal SDK
   4  * ===================================================================
   5  * Copyright © 2017 AT&T Intellectual Property. All rights reserved.
   6  * ===================================================================
   7  *
   8  * Unless otherwise specified, all software contained herein is licensed
   9  * under the Apache License, Version 2.0 (the "License");
  10  * you may not use this software except in compliance with the License.
  11  * You may obtain a copy of the License at
  12  *
  13  *             http://www.apache.org/licenses/LICENSE-2.0
  14  *
  15  * Unless required by applicable law or agreed to in writing, software
  16  * distributed under the License is distributed on an "AS IS" BASIS,
  17  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  18  * See the License for the specific language governing permissions and
  19  * limitations under the License.
  20  *
  21  * Unless otherwise specified, all documentation contained herein is licensed
  22  * under the Creative Commons License, Attribution 4.0 Intl. (the "License");
  23  * you may not use this documentation except in compliance with the License.
  24  * You may obtain a copy of the License at
  25  *
  26  *             https://creativecommons.org/licenses/by/4.0/
  27  *
  28  * Unless required by applicable law or agreed to in writing, documentation
  29  * distributed under the License is distributed on an "AS IS" BASIS,
  30  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  31  * See the License for the specific language governing permissions and
  32  * limitations under the License.
  33  *
  34  * ============LICENSE_END============================================
  35  *
  36  *
  37  */
  38 package org.onap.portalsdk.analytics.model.runtime;
  39
  40 import java.io.UnsupportedEncodingException;
  41 import java.text.ParsePosition;
  42 import java.text.SimpleDateFormat;
  43 import java.util.ArrayList;
  44 import java.util.Arrays;
  45 import java.util.Collections;
  46 import java.util.Enumeration;
  47 import java.util.HashMap;
  48 import java.util.Iterator;
  49 import java.util.List;
  50 import java.util.Map;
  51 import java.util.Map.Entry;
  52 import java.util.Set;
  53 import java.util.regex.Matcher;
  54 import java.util.regex.Pattern;
  55
  56 import javax.servlet.http.HttpServletRequest;
  57 import javax.servlet.http.HttpSession;
  58
  59 import org.onap.portalsdk.analytics.error.RaptorException;
  60 import org.onap.portalsdk.analytics.model.ReportHandler;
  61 import org.onap.portalsdk.analytics.model.base.ChartSeqComparator;
  62 import org.onap.portalsdk.analytics.system.AppUtils;
  63 import org.onap.portalsdk.analytics.system.ConnectionUtils;
  64 import org.onap.portalsdk.analytics.util.AppConstants;
  65 import org.onap.portalsdk.analytics.util.DataSet;
  66 import org.onap.portalsdk.analytics.util.Utils;
  67 import org.onap.portalsdk.analytics.xmlobj.DataColumnType;
  68 import org.onap.portalsdk.analytics.xmlobj.FormFieldType;
  69 import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;
  70 import org.onap.portalsdk.core.web.support.UserUtils;
  71
  72 import com.fasterxml.jackson.databind.DeserializationFeature;
  73 import com.fasterxml.jackson.databind.ObjectMapper;
  74 import com.fasterxml.jackson.databind.SerializationFeature;
  75
  76 public class ChartJSONHelper {
  77
  78         private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ChartJSONHelper.class);
  79
  80         private ReportRuntime reportRuntime;
  81         private String chartType;
  82
  83     public static final long HOUR = 3600L * 1000L;
  84     public static final long DAY = 3600L * 1000L * 24L;
  85     public static final long MONTH = 3600L * 1000L * 24L * 31L;
  86     public static final long YEAR = 3600L * 1000L * 24L * 365L;
  87
  88
  89         public ChartJSONHelper() {
  90
  91         }
  92
  93         /**
  94          * @return the chartType
  95          */
  96         public String getChartType() {
  97                 return chartType;
  98         }
  99
 100         /**
 101          * @param chartType the chartType to set
 102          */
 103         public void setChartType(String chartType) {
 104                 this.chartType = chartType;
 105         }
 106
 107         public ChartJSONHelper(ReportRuntime rr) {
 108                 this.reportRuntime = rr;
 109         }
 110
 111     public String generateJSON(String reportID, HttpServletRequest request, boolean showData)
 112             throws RaptorException {
 113         // From annotations chart
 114         clearReportRuntimeBackup(request);
 115
 116         ReportHandler rh = new ReportHandler();
 117
 118         HashMap<String, String> chartOptionsMap = new HashMap<>();
 119
 120         try {
 121             if (reportID != null) {
 122                 reportRuntime = rh.loadReportRuntime(request, reportID, true, 1);
 123                 setChartType(reportRuntime.getChartType());
 124             }
 125
 126             String rotateLabelsStr = "";
 127             rotateLabelsStr = AppUtils.nvl(reportRuntime.getLegendLabelAngle());
 128             if (rotateLabelsStr.equalsIgnoreCase("standard")) {
 129                 rotateLabelsStr = "0";
 130             } else if (rotateLabelsStr.equalsIgnoreCase("up45")) {
 131                 rotateLabelsStr = "45";
 132             } else if (rotateLabelsStr.equalsIgnoreCase("down45")) {
 133                 rotateLabelsStr = "-45";
 134             } else if (rotateLabelsStr.equalsIgnoreCase("up90")) {
 135                 rotateLabelsStr = "90";
 136             } else if (rotateLabelsStr.equalsIgnoreCase("down90")) {
 137                 rotateLabelsStr = "-90";
 138             } else
 139                 rotateLabelsStr = "0";
 140
 141             String width = (AppUtils.getRequestNvlValue(request, "width").length() > 0
 142                     ? AppUtils.getRequestNvlValue(request, "width")
 143                     : (AppUtils.nvl(reportRuntime.getChartWidth()).length() > 0
 144                             ? reportRuntime.getChartWidth()
 145                             : "700"));
 146             String height = (AppUtils.getRequestNvlValue(request, "height").length() > 0
 147                     ? AppUtils.getRequestNvlValue(request, "height")
 148                     : (AppUtils.nvl(reportRuntime.getChartHeight()).length() > 0
 149                             ? reportRuntime.getChartHeight()
 150                             : "300"));
 151             String animationStr = (AppUtils.getRequestNvlValue(request, "animation").length() > 0
 152                     ? AppUtils.getRequestNvlValue(request, "animation")
 153                     : new Boolean(reportRuntime.isAnimateAnimatedChart()).toString());
 154
 155             String rotateLabels = (AppUtils.getRequestNvlValue(request, "rotateLabels").length() > 0
 156                     ? AppUtils.getRequestNvlValue(request, "rotateLabels")
 157                     : (rotateLabelsStr.length() > 0 ? rotateLabelsStr : "0"));
 158             String staggerLabelsStr =
 159                     (AppUtils.getRequestNvlValue(request, "staggerLabels").length() > 0
 160                             ? AppUtils.getRequestNvlValue(request, "staggerLabels")
 161                             : "false");
 162             String showMaxMinStr = (AppUtils.getRequestNvlValue(request, "showMaxMin").length() > 0
 163                     ? AppUtils.getRequestNvlValue(request, "showMaxMin")
 164                     : "false");
 165             String showControlsStr =
 166                     (AppUtils.getRequestNvlValue(request, "showControls").length() > 0
 167                             ? AppUtils.getRequestNvlValue(request, "showControls")
 168                             : new Boolean(reportRuntime.displayBarControls()).toString());
 169             String showLegendStr = (AppUtils.getRequestNvlValue(request, "showLegend").length() > 0
 170                     ? AppUtils.getRequestNvlValue(request, "showLegend")
 171                     : new Boolean(!new Boolean(reportRuntime.hideChartLegend())).toString());
 172             String topMarginStr = AppUtils.getRequestNvlValue(request, "topMargin");
 173             String topMargin = (AppUtils.nvl(topMarginStr).length() <= 0)
 174                     ? (reportRuntime.getTopMargin() != null
 175                             ? reportRuntime.getTopMargin().toString()
 176                             : "30")
 177                     : topMarginStr;
 178             String bottomMarginStr = AppUtils.getRequestNvlValue(request, "bottomMargin");
 179             String bottomMargin = (AppUtils.nvl(bottomMarginStr).length() <= 0)
 180                     ? (reportRuntime.getBottomMargin() != null
 181                             ? reportRuntime.getBottomMargin().toString()
 182                             : "50")
 183                     : bottomMarginStr;
 184             String leftMarginStr = AppUtils.getRequestNvlValue(request, "leftMargin");
 185             String leftMargin = (AppUtils.nvl(leftMarginStr).length() <= 0)
 186                     ? (reportRuntime.getLeftMargin() != null
 187                             ? reportRuntime.getLeftMargin().toString()
 188                             : "100")
 189                     : leftMarginStr;
 190             String rightMarginStr = AppUtils.getRequestNvlValue(request, "rightMargin");
 191             String rightMargin = (AppUtils.nvl(rightMarginStr).length() <= 0)
 192                     ? (reportRuntime.getRightMargin() != null
 193                             ? reportRuntime.getRightMargin().toString()
 194                             : "160")
 195                     : rightMarginStr;
 196             String showTitleStr = (AppUtils.getRequestNvlValue(request, "showTitle").length() > 0
 197                     ? AppUtils.getRequestNvlValue(request, "showTitle")
 198                     : new Boolean(reportRuntime.displayChartTitle()).toString());
 199             String subType = AppUtils.getRequestNvlValue(request, "subType").length() > 0
 200                     ? AppUtils.getRequestNvlValue(request, "subType")
 201                     : (AppUtils.nvl(reportRuntime.getTimeSeriesRender()).equals("area")
 202                             ? reportRuntime.getTimeSeriesRender()
 203                             : "");
 204             String stackedStr = AppUtils.getRequestNvlValue(request, "stacked").length() > 0
 205                     ? AppUtils.getRequestNvlValue(request, "stacked")
 206                     : new Boolean(reportRuntime.isChartStacked()).toString();
 207             String horizontalBar =
 208                     AppUtils.getRequestNvlValue(request, "horizontalBar").length() > 0
 209                             ? AppUtils.getRequestNvlValue(request, "horizontalBar")
 210                             : new Boolean(reportRuntime.isHorizontalOrientation()).toString();
 211             String barRealTimeAxis = AppUtils.getRequestNvlValue(request, "barRealTimeAxis");
 212             String barReduceXAxisLabels =
 213                     AppUtils.getRequestNvlValue(request, "barReduceXAxisLabels").length() > 0
 214                             ? AppUtils.getRequestNvlValue(request, "barReduceXAxisLabels")
 215                             : new Boolean(reportRuntime.isLessXaxisTickers()).toString();;
 216             String timeAxis = AppUtils.getRequestNvlValue(request, "timeAxis").length() > 0
 217                     ? AppUtils.getRequestNvlValue(request, "timeAxis")
 218                     : new Boolean(reportRuntime.isTimeAxis()).toString();
 219             String logScale = AppUtils.getRequestNvlValue(request, "logScale").length() > 0
 220                     ? AppUtils.getRequestNvlValue(request, "logScale")
 221                     : new Boolean(reportRuntime.isLogScale()).toString();
 222             String precision = AppUtils.getRequestNvlValue(request, "precision").length() > 0
 223                     ? AppUtils.getRequestNvlValue(request, "precision")
 224                     : "2";
 225
 226
 227             chartOptionsMap.put("width", width);
 228             chartOptionsMap.put("height", height);
 229             chartOptionsMap.put("animation", animationStr);
 230             chartOptionsMap.put("rotateLabels", rotateLabels);
 231             chartOptionsMap.put("staggerLabels", staggerLabelsStr);
 232             chartOptionsMap.put("showMaxMin", showMaxMinStr);
 233             chartOptionsMap.put("showControls", showControlsStr);
 234             chartOptionsMap.put("showLegend", showLegendStr);
 235             chartOptionsMap.put("topMargin", topMargin);
 236             chartOptionsMap.put("bottomMargin", bottomMargin);
 237             chartOptionsMap.put("leftMargin", leftMargin);
 238             chartOptionsMap.put("rightMargin", rightMargin);
 239             chartOptionsMap.put("showTitle", showTitleStr);
 240             chartOptionsMap.put("subType", subType);
 241             chartOptionsMap.put("stacked", stackedStr);
 242             chartOptionsMap.put("horizontalBar", horizontalBar);
 243             chartOptionsMap.put("timeAxis", timeAxis);
 244             chartOptionsMap.put("barRealTimeAxis", barRealTimeAxis);
 245             chartOptionsMap.put("barReduceXAxisLabels", barReduceXAxisLabels);
 246
 247             chartOptionsMap.put("logScale", logScale);
 248             chartOptionsMap.put("precision", precision);
 249
 250
 251         } catch (RaptorException ex) {
 252             logger.error("Exception in generateJSON", ex);
 253         }
 254         return generateJSON(reportRuntime, chartOptionsMap, request, showData);
 255     }
 256
 257         public String generateJSON(ReportRuntime reportRuntime, HttpServletRequest request, boolean showData) throws RaptorException {
 258
 259                 String rotateLabelsStr = "";
 260                 rotateLabelsStr = AppUtils.nvl(reportRuntime.getLegendLabelAngle());
 261         if (rotateLabelsStr.equalsIgnoreCase("standard")) {
 262             rotateLabelsStr = "0";
 263         } else if (rotateLabelsStr.equalsIgnoreCase("up45")) {
 264             rotateLabelsStr = "45";
 265         } else if (rotateLabelsStr.equalsIgnoreCase("down45")) {
 266             rotateLabelsStr = "-45";
 267         } else if (rotateLabelsStr.equalsIgnoreCase("up90")) {
 268             rotateLabelsStr = "90";
 269         } else if (rotateLabelsStr.equalsIgnoreCase("down90")) {
 270             rotateLabelsStr = "-90";
 271         } else {
 272             rotateLabelsStr = "0";
 273         }
 274
 275                 HashMap<String,String> chartOptionsMap = new HashMap<>();
 276                 chartOptionsMap.put("width", reportRuntime.getChartWidth());
 277                 chartOptionsMap.put("height", reportRuntime.getChartHeight());
 278                 chartOptionsMap.put("animation", Boolean.toString(reportRuntime.isAnimateAnimatedChart()));
 279                 chartOptionsMap.put("rotateLabels", rotateLabelsStr);
 280                 chartOptionsMap.put("staggerLabels", "false");
 281                 chartOptionsMap.put("showMaxMin", "false");
 282                 chartOptionsMap.put("showControls", Boolean.toString(reportRuntime.displayBarControls()));
 283                 chartOptionsMap.put("showLegend", Boolean.toString(!reportRuntime.hideChartLegend()));
 284                 chartOptionsMap.put("topMargin", reportRuntime.getTopMargin()!=null?reportRuntime.getTopMargin().toString():"30");
 285                 chartOptionsMap.put("bottomMargin", reportRuntime.getBottomMargin()!=null?reportRuntime.getBottomMargin().toString():"50");
 286                 chartOptionsMap.put("leftMargin", reportRuntime.getLeftMargin()!=null?reportRuntime.getLeftMargin().toString():"100");
 287                 chartOptionsMap.put("rightMargin", reportRuntime.getRightMargin()!=null?reportRuntime.getRightMargin().toString():"160");
 288                 chartOptionsMap.put("showTitle", Boolean.toString(reportRuntime.displayChartTitle()));
 289                 chartOptionsMap.put("subType", (AppUtils.nvl(reportRuntime.getTimeSeriesRender()).equals("area")?reportRuntime.getTimeSeriesRender():""));
 290                 chartOptionsMap.put("stacked", Boolean.toString(reportRuntime.isChartStacked()));
 291                 chartOptionsMap.put("horizontalBar", Boolean.toString(reportRuntime.isHorizontalOrientation()));
 292                 chartOptionsMap.put("timeAxis", Boolean.toString(reportRuntime.isTimeAxis()));
 293                 chartOptionsMap.put("barReduceXAxisLabels", Boolean.toString(reportRuntime.isLessXaxisTickers()));
 294
 295                 chartOptionsMap.put("logScale", Boolean.toString(reportRuntime.isLogScale()));
 296                 chartOptionsMap.put("precision", "2");
 297
 298
 299
 300                 return generateJSON(reportRuntime, chartOptionsMap, request, showData);
 301         }
 302
 303         public String generateJSON(ReportRuntime reportRuntime,Map<String, String> chartOptionsMap, HttpServletRequest request, boolean showData) throws RaptorException {
 304
 305                 String userId = AppUtils.getUserID(request);
 306                 String width                            = chartOptionsMap.get("width");
 307                 String height                           = chartOptionsMap.get("height");
 308                 boolean animation                       = getBooleanValue(chartOptionsMap.get("animation"), true);
 309                 String rotateLabels             = chartOptionsMap.get("rotateLabels");
 310                 boolean staggerLabels           = getBooleanValue(chartOptionsMap.get("staggerLabels"));
 311                 boolean showMaxMin                      = getBooleanValue(chartOptionsMap.get("showMaxMin"), false);
 312                 boolean showLegend                      = getBooleanValue(chartOptionsMap.get("showLegend"), true);
 313                 boolean showControls            = getBooleanValue(chartOptionsMap.get("showControls"), true);
 314                 String topMargin                        = chartOptionsMap.get("topMargin");
 315                 String bottomMargin             = chartOptionsMap.get("bottomMargin");
 316                 String leftMargin                       = chartOptionsMap.get("leftMargin");
 317                 String rightMargin                      = chartOptionsMap.get("rightMargin");
 318                 boolean showTitle                       = getBooleanValue(chartOptionsMap.get("showTitle"), true);
 319                 String subType                          = chartOptionsMap.get("subType");
 320                 boolean stacked                         = getBooleanValue(chartOptionsMap.get("stacked"), false);
 321                 boolean horizontalBar           = getBooleanValue(chartOptionsMap.get("horizontalBar"), false);
 322                 boolean barRealTimeAxis         = getBooleanValue(chartOptionsMap.get("barRealTimeAxis"), true);
 323                 boolean barReduceXAxisLabels= getBooleanValue(chartOptionsMap.get("barReduceXAxisLabels"), false);
 324                 boolean timeAxis                        = getBooleanValue(chartOptionsMap.get("timeAxis"), true);
 325
 326
 327                 boolean logScale = getBooleanValue(chartOptionsMap.get("logScale"), false);
 328
 329                 int precision    =  2;
 330
 331                 try {
 332                         precision = Integer.parseInt(chartOptionsMap.get("precision"));
 333                 } catch (NumberFormatException ex) {
 334
 335                 }
 336
 337         final Long user_id = new Long((long) UserUtils.getUserId(request));
 338
 339         HttpSession session = null;
 340         session = request.getSession();
 341         String chartType = reportRuntime.getChartType();
 342             List l = reportRuntime.getAllColumns();
 343             List lGroups = reportRuntime.getAllChartGroups();
 344             HashMap mapYAxis = reportRuntime.getAllChartYAxis(reportRuntime.getReportParamValues());
 345             String chartLeftAxisLabel = reportRuntime.getFormFieldFilled(nvl(reportRuntime.getChartLeftAxisLabel()));
 346             String chartRightAxisLabel = reportRuntime.getFormFieldFilled(nvl(reportRuntime.getChartRightAxisLabel()));
 347
 348             boolean multipleSeries = reportRuntime.isMultiSeries();
 349
 350                 java.util.HashMap formValues = null;
 351                 formValues = getRequestParametersMap(reportRuntime, request);
 352
 353
 354             String legendColumnName = (reportRuntime.getChartLegendColumn()!=null)?reportRuntime.getChartLegendColumn().getDisplayName():"Legend Column";
 355                 boolean displayChart = reportRuntime.getDisplayChart();
 356                 HashMap additionalChartOptionsMap = new HashMap();
 357
 358                 StringBuffer wholeScript = new StringBuffer("");
 359
 360                 String title = reportRuntime.getReportTitle();
 361
 362                 title = parseTitle(title, formValues);
 363                 ObjectMapper mapper = new ObjectMapper();
 364                 ChartJSON chartJSON = new ChartJSON();
 365                 String sql = "";
 366                 if(displayChart) {
 367                         DataSet ds = null;
 368                         if(showData) {
 369
 370                                 try {
 371                                         if (!(chartType.equals(AppConstants.GT_HIERARCHICAL) || chartType.equals(AppConstants.GT_HIERARCHICAL_SUNBURST) || chartType.equals(AppConstants.GT_ANNOTATION_CHART))) {
 372                                                 sql = generateChartSQL(userId, request );
 373                                                 ds = (DataSet) loadChartData(new Long(user_id).toString(), request);
 374                                         } else if(chartType.equals(AppConstants.GT_ANNOTATION_CHART)) {
 375                                                 sql = reportRuntime.getWholeSQL();
 376                                                 String reportSQL = reportRuntime.getWholeSQL();
 377                                                 String dbInfo = reportRuntime.getDBInfo();
 378                                                 ds = ConnectionUtils.getDataSet(reportSQL, dbInfo);
 379                                                 if(ds.getRowCount()<=0) {
 380                                                         logger.debug(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 381                                                         logger.info(EELFLoggerDelegate.debugLogger, (chartType.toUpperCase()+" - " + "Report ID : " + reportRuntime.getReportID() + " DATA IS EMPTY" ));
 382                                                         logger.info(EELFLoggerDelegate.debugLogger, ("QUERY - " + reportSQL));
 383                                                         logger.info(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 384                                                 }
 385                                         } else if(chartType.equals(AppConstants.GT_HIERARCHICAL)||chartType.equals(AppConstants.GT_HIERARCHICAL_SUNBURST)) {
 386                                                 sql = reportRuntime.getWholeSQL();
 387                                                 String reportSQL = reportRuntime.getWholeSQL();
 388                                                 String dbInfo = reportRuntime.getDBInfo();
 389                                                 ds = ConnectionUtils.getDataSet(reportSQL, dbInfo);
 390                                         }
 391                                 } catch (RaptorException ex) {
 392                                         logger.error(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 393                                         logger.error(EELFLoggerDelegate.debugLogger, (chartType.toUpperCase()+" - " + "Report ID : " + reportRuntime.getReportID() + " ERROR THROWN FOR GIVEN QUERY "));
 394                                         logger.error(EELFLoggerDelegate.debugLogger, ("QUERY - " + reportRuntime.getWholeSQL()));
 395                                         logger.error(EELFLoggerDelegate.debugLogger, ("ERROR STACK TRACE" + ex.getMessage()));
 396                                         logger.error(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 397
 398                                 }
 399                                 if(ds==null) {
 400                                         if(chartType.equals(AppConstants.GT_ANNOTATION_CHART))
 401                                                 ds = new DataSet();
 402                                         else
 403                                                 displayChart = false;
 404                                 }
 405                         }
 406                         if(displayChart) {
 407
 408                                 chartJSON.setReportID(reportRuntime.getReportID());
 409                                 chartJSON.setReportName(reportRuntime.getReportName());
 410                                 chartJSON.setReportDescr(reportRuntime.getReportDescr());
 411                                 chartJSON.setReportTitle(reportRuntime.getReportTitle());
 412                                 chartJSON.setReportSubTitle(reportRuntime.getReportSubTitle());
 413
 414                                 List<DataColumnType> dcList = reportRuntime.getOnlyVisibleColumns();
 415                                 int countIndex = 0;
 416                                 ArrayList<ChartColumnJSON> chartColumnJSONList = new ArrayList<>();
 417                                 for(Iterator iter = dcList.iterator(); iter.hasNext(); ) {
 418                                         ChartColumnJSON ccJSON = new ChartColumnJSON();
 419                                         DataColumnType dc = (DataColumnType) iter.next();
 420                                         ccJSON.setIndex(countIndex);
 421                                         ccJSON.setValue(dc.getColId());
 422                                         ccJSON.setTitle(dc.getDisplayName());
 423                                         countIndex++;
 424                                         chartColumnJSONList.add(ccJSON);
 425                                 }
 426                                 chartJSON.setChartColumnJSONList(chartColumnJSONList);
 427                                 chartJSON.setChartAvailable(displayChart);
 428
 429                                 ChartTypeJSON chartTypeJSON = new ChartTypeJSON();
 430                                 chartTypeJSON.setIndex(0);
 431                                 chartTypeJSON.setTitle("");
 432                                 chartTypeJSON.setValue(chartType);
 433                                 chartJSON.setChartTypeJSON(chartTypeJSON);
 434                                 chartJSON.setWidth(width);
 435                                 chartJSON.setHeight(height);
 436                                 chartJSON.setAnimation(animation);
 437                                 chartJSON.setRotateLabels(rotateLabels);
 438                                 chartJSON.setStaggerLabels(staggerLabels);
 439                                 chartJSON.setShowTitle(showTitle);
 440                                 DomainAxisJSON domainAxisJSON = new DomainAxisJSON();
 441                                 domainAxisJSON.setIndex(0);
 442                                 if(reportRuntime.getChartLegendColumn()!=null)
 443                                         domainAxisJSON.setTitle(reportRuntime.getChartLegendColumn().getDisplayName());
 444                                 else
 445                                         domainAxisJSON.setTitle("");
 446                                 if(reportRuntime.getChartLegendColumn()!=null)
 447                                         domainAxisJSON.setValue(reportRuntime.getChartLegendColumn().getColId());
 448                                 else
 449                                         domainAxisJSON.setValue("");
 450                                 chartJSON.setDomainAxisJSON(domainAxisJSON);
 451
 452
 453                                 List<DataColumnType> reportCols = reportRuntime.getAllColumns();
 454                                 boolean hasSeriesColumn = false;
 455                                 //ArrayList<Item>
 456                                 for (Iterator<DataColumnType> iter = reportCols.iterator(); iter
 457                                                 .hasNext();) {
 458                                         DataColumnType dct = (DataColumnType) iter.next();
 459                                         if(dct.isChartSeries()!=null && dct.isChartSeries().booleanValue()) {
 460                                                 chartJSON.setHasCategoryAxis(true);
 461                                                 CategoryAxisJSON categoryAxisJSON = new CategoryAxisJSON();
 462                                                 categoryAxisJSON.setIndex(0);
 463                                                 categoryAxisJSON.setTitle(dct.getDisplayName());
 464                                                 categoryAxisJSON.setValue(dct.getColId());
 465                                                 chartJSON.setCategoryAxisJSON(categoryAxisJSON);
 466                                         }
 467                                 }
 468
 469                                 List<DataColumnType> chartValueCols = reportRuntime.getChartValueColumnsList(AppConstants.CHART_ALL_COLUMNS, null);
 470                                 DataColumnType dct_RangeAxis = null;
 471
 472                                 if(chartValueCols.size() <= 0) {
 473                                         chartValueCols = reportCols;
 474                                 }
 475                                 ArrayList<RangeAxisJSON> rangeAxisJSONList = new ArrayList<>();
 476                                 for (int k = 0; k < chartValueCols.size(); k++) {
 477                                         dct_RangeAxis = chartValueCols.get(k);
 478                                         RangeAxisJSON rangeAxisJSON = new RangeAxisJSON();
 479
 480                                         RangeAxisLabelJSON rangeAxisLabelJSON = new RangeAxisLabelJSON();
 481                                         rangeAxisLabelJSON.setIndex(0);
 482                                         rangeAxisLabelJSON.setTitle(dct_RangeAxis.getDisplayName());
 483                                         rangeAxisLabelJSON.setValue(dct_RangeAxis.getColId());
 484                                         rangeAxisJSON.setRangeAxisLabelJSON(rangeAxisLabelJSON);
 485                                         RangeLineTypeJSON rangeLineTypeJSON = new RangeLineTypeJSON();
 486                                         rangeLineTypeJSON.setIndex(0);
 487                                         rangeLineTypeJSON.setTitle("");
 488                                         rangeLineTypeJSON.setValue(dct_RangeAxis.getChartLineType());
 489                                         rangeAxisJSON.setRangeLineTypeJSON(rangeLineTypeJSON);
 490
 491                                         RangeColorJSON rangeColorJSON = new RangeColorJSON();
 492                                         rangeColorJSON.setIndex(0);
 493                                         rangeColorJSON.setTitle("");
 494                                         rangeColorJSON.setValue(dct_RangeAxis.getChartColor());
 495                                         rangeAxisJSON.setRangeColorJSON(rangeColorJSON);
 496                                         String chartGroup = "";
 497                                         chartGroup = AppUtils.nvl(dct_RangeAxis.getChartGroup());
 498                                         if(chartGroup.indexOf("|")!=-1)
 499                                                 chartGroup = chartGroup.substring(0, chartGroup.indexOf("|"));
 500
 501
 502                                         rangeAxisJSON.setRangeChartGroup(chartGroup);
 503                                         String yAxis = "";
 504                                         yAxis = AppUtils.nvl(dct_RangeAxis.getYAxis());
 505                                         if(yAxis.indexOf("|")!=-1)
 506                                                 yAxis = yAxis.substring(0, yAxis.indexOf("|"));
 507
 508                                         rangeAxisJSON.setRangeYAxis(yAxis);
 509                                         rangeAxisJSON.setShowAsArea((dct_RangeAxis.isIsRangeAxisFilled()!=null && dct_RangeAxis.isIsRangeAxisFilled().booleanValue())?true:false);
 510                                         rangeAxisJSONList.add(rangeAxisJSON);
 511                                 }
 512                                 CommonChartOptions commonChartOptions = new CommonChartOptions();
 513                                 commonChartOptions.setLegendPosition(AppUtils.nvl(reportRuntime.getLegendPosition()).length()>0?reportRuntime.getLegendPosition().toLowerCase():"top");
 514                                 String legendLabelAngle = "";
 515                                 legendLabelAngle = reportRuntime.getLegendLabelAngle().toLowerCase();
 516                                 commonChartOptions.setLegendLabelAngle(AppUtils.nvl(legendLabelAngle).length()>0?legendLabelAngle:"up45");
 517                                 commonChartOptions.setHideLegend(reportRuntime.hideChartLegend());
 518                                 commonChartOptions.setAnimateAnimatedChart(reportRuntime.isAnimateAnimatedChart());
 519                                 commonChartOptions.setTopMargin(reportRuntime.getTopMargin()!=null?reportRuntime.getTopMargin():new Integer("30"));
 520                                 commonChartOptions.setBottomMargin(reportRuntime.getBottomMargin()!=null?reportRuntime.getBottomMargin():new Integer("50"));
 521                                 commonChartOptions.setLeftMargin(reportRuntime.getLeftMargin()!=null?reportRuntime.getLeftMargin():new Integer("100"));
 522                                 commonChartOptions.setRightMargin(reportRuntime.getRightMargin()!=null?reportRuntime.getRightMargin():new Integer("60"));
 523                                 chartJSON.setCommonChartOptions(commonChartOptions);
 524
 525                                 if(chartType.equals(AppConstants.GT_BAR_3D)) {
 526                                         BarChartOptions barChartOptions = new BarChartOptions();
 527                                         barChartOptions.setDisplayBarControls(reportRuntime.displayBarControls()?true:false);
 528                                         barChartOptions.setMinimizeXAxisTickers(reportRuntime.isLessXaxisTickers()?true:false);
 529                                         barChartOptions.setStackedChart(reportRuntime.isChartStacked()?true:false);
 530                                         barChartOptions.setTimeAxis(reportRuntime.isTimeAxis()?true:false);
 531                                         barChartOptions.setVerticalOrientation(reportRuntime.isVerticalOrientation()?true:false);
 532                                         barChartOptions.setxAxisDateType(reportRuntime.isXAxisDateType()?true:false);
 533                                         barChartOptions.setyAxisLogScale(reportRuntime.isLogScale()?true:false);
 534                                         chartJSON.setBarChartOptions(barChartOptions);
 535                                         chartJSON.setTimeSeriesChartOptions(null);
 536                                         chartJSON.setPieChartOptions(null);
 537                                         chartJSON.setFlexTimeSeriesChartOptions(null);
 538
 539                                 } else if(chartType.equals(AppConstants.GT_TIME_SERIES)) {
 540                                         TimeSeriesChartOptions timeSeriesChartOptions = new TimeSeriesChartOptions();
 541                                         timeSeriesChartOptions.setAddXAxisTicker(reportRuntime.isAddXAxisTickers());
 542                                         timeSeriesChartOptions.setLineChartRenderer(AppUtils.nvl(reportRuntime.getTimeSeriesRender()).length()>0?reportRuntime.getTimeSeriesRender():"line");
 543                                         timeSeriesChartOptions.setMultiSeries(reportRuntime.isMultiSeries());
 544                                         timeSeriesChartOptions.setNonTimeAxis(reportRuntime.isTimeAxis());
 545                                         timeSeriesChartOptions.setShowXAxisLabel(reportRuntime.isShowXaxisLabel());
 546                                         chartJSON.setBarChartOptions(null);
 547                                         chartJSON.setTimeSeriesChartOptions(timeSeriesChartOptions);
 548                                         chartJSON.setPieChartOptions(null);
 549                                         chartJSON.setFlexTimeSeriesChartOptions(null);
 550                                 } else if(chartType.equals(AppConstants.GT_ANNOTATION_CHART) || chartType.equals(AppConstants.GT_FLEX_TIME_CHARTS)) {
 551                                         FlexTimeSeriesChartOptions flexTimeSeriesChartOptions = new FlexTimeSeriesChartOptions();
 552                                         flexTimeSeriesChartOptions.setZoomIn(reportRuntime.getZoomIn()!=null?reportRuntime.getZoomIn():new Integer("25"));
 553                                         String timeAxisTypeStr = "";
 554                                         timeAxisTypeStr = reportRuntime.getTimeAxisType().toLowerCase();
 555                                         flexTimeSeriesChartOptions.setTimeAxisType(timeAxisTypeStr);
 556                                         chartJSON.setBarChartOptions(null);
 557                                         chartJSON.setTimeSeriesChartOptions(null);
 558                                         chartJSON.setPieChartOptions(null);
 559                                         chartJSON.setFlexTimeSeriesChartOptions(flexTimeSeriesChartOptions);
 560                                 }
 561                                 chartJSON.setRangeAxisList(rangeAxisJSONList);
 562                                 chartJSON.setPrimaryAxisLabel(reportRuntime.getChartLeftAxisLabel());
 563                                 chartJSON.setSecondaryAxisLabel(reportRuntime.getChartRightAxisLabel());
 564                                 chartJSON.setMinRange(reportRuntime.getRangeAxisLowerLimit());
 565                                 chartJSON.setMaxRange(reportRuntime.getRangeAxisUpperLimit());
 566
 567                                 if(showData) {
 568                                         ArrayList<ArrayList<Row>> wholeList = new ArrayList<>();
 569
 570                                         ArrayList<Row> rowList = new ArrayList<>();
 571                                         if(showData) {
 572                                                 for (int i = 0; i < ds.getRowCount(); i++) {
 573                                                         rowList = new ArrayList<>();
 574                                                         for (int j = 0; j<ds.getColumnCount(); j++) {
 575                                                                 Row row = new Row();
 576                                                                 row.setColId(ds.getColumnName(j));
 577                                                                 row.setDisplayValue(ds.getString(i, j));
 578                                                                 row.setDataType(ds.getColumnType(j));
 579                                                                 rowList.add(row);
 580                                                         }
 581                                                         wholeList.add(rowList);
 582
 583                                                 }
 584
 585                                                 chartJSON.setWholeList(wholeList);
 586                                         }
 587                                 }
 588
 589
 590
 591
 592
 593                         }
 594                 } else {
 595                         // chart is not visible
 596                         chartJSON.setReportID(reportRuntime.getReportID());
 597                         chartJSON.setReportName(reportRuntime.getReportName());
 598                         chartJSON.setReportDescr(reportRuntime.getReportDescr());
 599                         chartJSON.setReportTitle(reportRuntime.getReportTitle());
 600                         chartJSON.setReportSubTitle(reportRuntime.getReportSubTitle());
 601                         chartJSON.setChartAvailable(displayChart);
 602                         ChartTypeJSON chartTypeJSON = new ChartTypeJSON();
 603                         chartTypeJSON.setIndex(0);
 604                         chartTypeJSON.setTitle("");
 605                         chartTypeJSON.setValue(chartType);
 606                         chartJSON.setChartTypeJSON(chartTypeJSON);
 607                 }
 608                 mapper.configure(SerializationFeature.FAIL_ON_EMPTY_BEANS, false);
 609                 mapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
 610                 String jsonInString = "";
 611                 try {
 612                         jsonInString = mapper.writerWithDefaultPrettyPrinter().writeValueAsString(chartJSON);
 613                 } catch (Exception ex) {
 614                         ex.printStackTrace();
 615
 616                 }
 617
 618                 return jsonInString;
 619         }
 620
 621                 public String nvl(String s) {
 622                         return (s == null) ? "" : s;
 623                 }
 624
 625                 public String nvl(String s, String sDefault) {
 626                         return nvl(s).equals("") ? sDefault : s;
 627                 }
 628
 629                 public static String nvls(String s) {
 630                         return (s == null) ? "" : s;
 631                 }
 632
 633                 public static String nvls(String s, String sDefault) {
 634                         return nvls(s).equals("") ? sDefault : s;
 635                 }
 636
 637                 public boolean getFlagInBoolean(String s) {
 638                         return nvl(s).toUpperCase().startsWith("Y") || nvl(s).equalsIgnoreCase("true");
 639                 }
 640
 641                 public DataSet loadChartData(String userId, HttpServletRequest request) throws RaptorException {
 642                         if (nvl(getChartType()).length() == 0)
 643                                 return null;
 644                         //TODO: display chart function to be added.
 645                         //if (!getDisplayChart())
 646                         //      return null;
 647
 648                 String sql = null;
 649             sql = generateChartSQL(userId, request);
 650             logger.debug(EELFLoggerDelegate.debugLogger, ("SQL generated " + sql));
 651                         String dbInfo = reportRuntime.getDBInfo();
 652                         DataSet ds = ConnectionUtils.getDataSet(sql, dbInfo);
 653                         if(ds.getRowCount()<=0) {
 654                                 logger.info(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 655                                 logger.info(EELFLoggerDelegate.debugLogger, (getChartType().toUpperCase()+" - " + "Report ID : " + reportRuntime.getReportID() + " DATA IS EMPTY"));
 656                                 logger.info(EELFLoggerDelegate.debugLogger, ("QUERY - " + sql));
 657                                 logger.info(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 658                         }
 659
 660                         return ds;
 661                 } // loadChartData
 662
 663                 public String generateChartSQL(String userId, HttpServletRequest request ) throws RaptorException  {
 664                         List reportCols = reportRuntime.getAllColumns();
 665                         List chartValueCols = getChartValueColumnsList(AppConstants.CHART_ALL_COLUMNS, null); // parameter is 0 has this requires all columns.
 666                         String reportSQL = reportRuntime.getWholeSQL();
 667
 668                         //Add order by clause
 669                         Pattern re1 = Pattern.compile("(^[\r\n]*|([\\s]))[Oo][Rr][Dd][Ee][Rr](.*?[^\r\n]*)[Bb][Yy]",Pattern.DOTALL);
 670                         Matcher matcher = re1.matcher(reportSQL);
 671
 672                         reportSQL = reportSQL + " ";
 673                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Ss][Ee][Ll][Ee][Cc][Tt]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" SELECT ");
 674                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Ww][Hh][Ee][Rr][Ee]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" WHERE ");
 675                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Ww][Hh][Ee][Nn]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" WHEN ");
 676                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Aa][Nn][Dd]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" AND ");
 677
 678                         if(!reportRuntime.getReportType().equals(AppConstants.RT_HIVE)) {
 679                                 int startPoint = reportSQL.lastIndexOf(" FROM ");
 680                                 if(startPoint <= 0) {
 681                                         startPoint = reportSQL.lastIndexOf(" from ");
 682                                 }
 683                                 if(startPoint <= 0) {
 684                                         startPoint = reportSQL.lastIndexOf("from ");
 685                                 }
 686                                 if(startPoint <= 0) {
 687                                         startPoint = reportSQL.lastIndexOf("FROM ");
 688                                 }
 689
 690                                 if (!matcher.find(startPoint)) {
 691                                         reportSQL = reportSQL + " ORDER BY 1" ;
 692                                 }
 693                         }
 694                         reportRuntime.setWholeSQL(reportSQL);
 695
 696                         logger.debug(EELFLoggerDelegate.debugLogger, (" *************************************************************************************** "));
 697                         logger.debug(EELFLoggerDelegate.debugLogger, ("WHOLE_SQL" + reportSQL));
 698                         logger.debug(EELFLoggerDelegate.debugLogger, (" *************************************************************************************** "));
 699
 700                         if (reportRuntime.getFormFieldList() != null) {
 701                                 for (Iterator iter = reportRuntime.getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 702                                         FormFieldType fft = (FormFieldType) iter.next();
 703                                         String fieldId = fft.getFieldId();
 704                                         String fieldDisplay = reportRuntime.getFormFieldDisplayName(fft);
 705                                         String formfield_value = "";
 706                                         formfield_value = AppUtils.getRequestNvlValue(request, fieldId);
 707                         String paramValue = nvl(formfield_value);
 708                                 if(paramValue.length()>0) {
 709                                         reportSQL = Utils.replaceInString(reportSQL,  fieldDisplay, nvl(
 710                                             paramValue, "NULL"));
 711                                 }
 712                                 reportSQL = Utils.replaceInString(reportSQL, "'" + fieldDisplay + "'", nvl(
 713                                         paramValue, "NULL"));
 714                                 reportSQL = Utils.replaceInString(reportSQL,  fieldDisplay , nvl(
 715                                             paramValue, "NULL"));
 716                                 }
 717                         }
 718                 logger.debug(EELFLoggerDelegate.debugLogger, ("SQL " + reportSQL));
 719                         String legendCol = "1 a";
 720                         StringBuffer groupCol = new StringBuffer();
 721                         StringBuffer seriesCol = new StringBuffer();
 722                         StringBuffer valueCols = new StringBuffer();
 723
 724                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 725                                 DataColumnType dc = (DataColumnType) iter.next();
 726                                 String colName = getColumnSelectStr(dc, request);
 727                                 if (nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))
 728                                         legendCol = getSelectExpr(dc, colName)+" " + dc.getColId();
 729                                 if ((!nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))
 730                                                 && (dc.getChartSeq()!=null &&  dc.getChartSeq().intValue() <= 0) && dc.isGroupBreak()) {
 731                                         groupCol.append(", ");
 732                                         groupCol.append(colName + " " +  dc.getColId());
 733                                 }
 734                         } // for
 735                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 736                                 DataColumnType dc = (DataColumnType) iter.next();
 737                                 if(dc.isChartSeries()!=null && dc.isChartSeries().booleanValue()) {
 738                                         seriesCol.append(", "+ getSelectExpr(dc,getColumnSelectStr(dc, request))+ " " +  dc.getColId());
 739                                 }
 740                         }
 741
 742                         for (Iterator iter = chartValueCols.iterator(); iter.hasNext();) {
 743                                 DataColumnType dc = (DataColumnType) iter.next();
 744                                 String colName = getColumnSelectStr(dc, request);
 745                                 String paramValue = "";
 746                                 if(AppUtils.nvl(colName).startsWith("[")) {
 747                                         if (reportRuntime.getFormFieldList() != null) {
 748                                                 for (Iterator iterC = reportRuntime.getFormFieldList().getFormField().iterator(); iterC.hasNext();) {
 749                                                         FormFieldType fft = (FormFieldType) iterC.next();
 750                                                         String fieldId = fft.getFieldId();
 751                                                         String fieldDisplay = reportRuntime.getFormFieldDisplayName(fft);
 752                                                         String formfield_value = "";
 753                                                         if(AppUtils.nvl(fieldDisplay).equals(colName)) {
 754                                                                 formfield_value = AppUtils.getRequestNvlValue(request, fieldId);
 755                                                                 paramValue = nvl(formfield_value);
 756                                                         }
 757                                                 }
 758
 759                                         }
 760
 761                                         seriesCol.append("," + (AppUtils.nvl(paramValue).length()>0? paramValue:"null") + " " + dc.getColId());
 762                                 } else {
 763                                  seriesCol.append("," + (AppUtils.nvl(paramValue).length()>0? paramValue:formatChartColumn(colName)) + " " + dc.getColId());
 764                                 }
 765                         } // for
 766
 767                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 768                                 DataColumnType dc = (DataColumnType) iter.next();
 769                                 String colName = dc.getDisplayName();
 770                                 String colValue = getColumnSelectStr(dc, request);
 771                                 if(colName.equals(AppConstants.RI_CHART_TOTAL_COL))
 772                                         seriesCol.append(", " + AppConstants.RI_CHART_TOTAL_COL + " " + AppConstants.RI_CHART_TOTAL_COL );
 773                                 if (colName.equals(AppConstants.RI_CHART_COLOR))
 774                                         seriesCol.append(", " + colValue + " " + AppConstants.RI_CHART_COLOR );
 775                                 if(colName.equals(AppConstants.RI_CHART_MARKER_START))
 776                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_START + " " + AppConstants.RI_CHART_MARKER_START );
 777                                 if(colName.equals(AppConstants.RI_CHART_MARKER_END))
 778                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_END + " " + AppConstants.RI_CHART_MARKER_END );
 779                                 if(colName.equals(AppConstants.RI_CHART_MARKER_TEXT_LEFT))
 780                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_TEXT_LEFT + " " + AppConstants.RI_CHART_MARKER_TEXT_LEFT );
 781                                 if(colName.equals(AppConstants.RI_CHART_MARKER_TEXT_RIGHT))
 782                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_TEXT_RIGHT + " " + AppConstants.RI_CHART_MARKER_TEXT_RIGHT );
 783                         }
 784
 785                 String final_sql = "";
 786                 reportSQL = Utils.replaceInString(reportSQL, " from ", " FROM ");
 787                 reportSQL = Utils.replaceInString(reportSQL, " From ", " FROM ");
 788                 reportSQL = Utils.replaceInString(reportSQL, " select ", " SELECT ");
 789                 reportSQL = Utils.replaceInString(reportSQL, " union ", " UNION ");
 790
 791                 int pos = 0;
 792                 int pos_first_select = 0;
 793                 int pos_dup_select = 0;
 794                 int pos_prev_select = 0;
 795                 int pos_last_select = 0;
 796                 if (reportSQL.indexOf("FROM", pos)!=-1) {
 797                     pos = reportSQL.indexOf("FROM", pos);
 798                     pos_dup_select = reportSQL.lastIndexOf("SELECT",pos);
 799                     pos_first_select = reportSQL.indexOf("SELECT");
 800                     logger.debug(EELFLoggerDelegate.debugLogger, ("pos_select " + pos_first_select + " " + pos_dup_select));
 801                     if(pos_dup_select > pos_first_select) {
 802                         logger.debug(EELFLoggerDelegate.debugLogger, ("********pos_dup_select ********" + pos_dup_select));
 803                         pos_prev_select = pos_first_select;
 804                         pos_last_select = pos_dup_select;
 805                         while (pos_last_select > pos_prev_select) {
 806                             logger.debug(EELFLoggerDelegate.debugLogger, ("pos_last , pos_prev " + pos_last_select + " " + pos_prev_select));
 807                             pos = reportSQL.indexOf("FROM", pos+2);
 808                             pos_prev_select = pos_last_select;
 809                             pos_last_select = reportSQL.lastIndexOf("SELECT",pos);
 810                             logger.debug(EELFLoggerDelegate.debugLogger, ("in WHILE LOOP LAST " + pos_last_select));
 811                         }
 812                      }
 813
 814                   }
 815                  final_sql += " "+reportSQL.substring(pos);
 816                  logger.debug(EELFLoggerDelegate.debugLogger, ("Final SQL " + final_sql));
 817                  String sql =  "SELECT " + legendCol + ", " + legendCol+"_1" + seriesCol.toString()+ nvl(valueCols.toString(), ", 1")
 818                                         + groupCol.toString()
 819                                         + final_sql;
 820                  logger.debug(EELFLoggerDelegate.debugLogger, ("Final sql in generateChartSQL " +sql));
 821
 822                  return sql;
 823                 } // generateChartSQL
 824
 825                 private String getColumnSelectStr(DataColumnType dc, HttpServletRequest request) {
 826                         String colName = dc.getColName();
 827                         String paramValue = null;
 828                         if (reportRuntime.getFormFieldList() != null) {
 829                                 for (Iterator iter = reportRuntime.getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 830                                         FormFieldType fft = (FormFieldType) iter.next();
 831                                         String fieldId = fft.getFieldId();
 832                                         String fieldDisplay = reportRuntime.getFormFieldDisplayName(fft);
 833                                         String formfield_value = "";
 834                                         formfield_value = AppUtils.getRequestNvlValue(request, fieldId);
 835                         paramValue = nvl(formfield_value);
 836                                 if(paramValue.length()>0) {
 837                                                         colName = Utils.replaceInString(colName, "'" + fieldDisplay + "'", "'"+nvl(
 838                                              paramValue, "NULL")+"'");
 839                                                         colName = Utils.replaceInString(colName,  fieldDisplay, nvl(
 840                                                                                         paramValue, "NULL"));
 841                                 }
 842                                 }
 843                                 return colName;
 844                         }
 845                         return colName;
 846                 } // getColumnSelectStr
 847
 848
 849
 850                 public String getSelectExpr(DataColumnType dct) {
 851                         // String colName =
 852                         // dct.isCalculated()?dct.getColName():((nvl(dct.getTableId()).length()>0)?(dct.getTableId()+"."+dct.getColName()):dct.getColName());
 853                         return getSelectExpr(dct, dct.getColName() /* colName */);
 854                 } // getSelectExpr
 855
 856                 private String getSelectExpr(DataColumnType dct, String colName) {
 857                         String colType = dct.getColType();
 858                         if (colType.equals(AppConstants.CT_CHAR)
 859                                         || ((nvl(dct.getColFormat()).length() == 0) && (!colType
 860                                                         .equals(AppConstants.CT_DATE))))
 861                                 return colName;
 862                         else
 863                                 return "TO_CHAR(" + colName + ", '"
 864                                                 + nvl(dct.getColFormat(), AppConstants.DEFAULT_DATE_FORMAT) + "')";
 865                 } // getSelectExpr
 866
 867             private String formatChartColumn(String colName) {
 868                 logger.debug(EELFLoggerDelegate.debugLogger, ("Format Chart Column Input colName " + colName));
 869                 colName =  colName.trim();
 870                 colName = Utils.replaceInString(colName, "TO_CHAR", "to_char");
 871                 colName = Utils.replaceInString(colName, "to_number", "TO_NUMBER");
 872                 colName = colName.replaceAll(",[\\s]*\\(", ",(");
 873                 StringBuffer colNameBuf = new StringBuffer(colName);
 874                 int pos = 0, posFormatStart = 0, posFormatEnd = 0;
 875                 String format = "";
 876
 877                 if(colNameBuf.indexOf("999")==-1 && colNameBuf.indexOf("990")==-1) {
 878                         logger.debug(EELFLoggerDelegate.debugLogger, (" return colName " + colNameBuf.toString()));
 879                     return colNameBuf.toString();
 880                 }
 881
 882                 while (colNameBuf.indexOf("to_char")!=-1) {
 883                     if(colNameBuf.indexOf("999")!=-1 || colNameBuf.indexOf("990")!=-1) {
 884                         pos = colNameBuf.indexOf("to_char");
 885                         colNameBuf.insert(pos, " TO_NUMBER ( CR_RAPTOR.SAFE_TO_NUMBER (");
 886                         pos = colNameBuf.indexOf("to_char");
 887                         colNameBuf.replace(pos, pos+7, "TO_CHAR");
 888                         logger.debug(EELFLoggerDelegate.debugLogger, ("After adding to_number " + colNameBuf.toString()));
 889                         posFormatStart = colNameBuf.indexOf(",'", pos)+1;
 890                         posFormatEnd = colNameBuf.indexOf(")",posFormatStart);
 891                         logger.debug(EELFLoggerDelegate.debugLogger, (posFormatStart + " " + posFormatEnd + " "+ pos));
 892                         format = colNameBuf.substring(posFormatStart, posFormatEnd);
 893                         colNameBuf.insert(posFormatEnd+1, " ," + format + ") , "+ format + ")");
 894                         logger.debug(EELFLoggerDelegate.debugLogger, ("colNameBuf " + colNameBuf.toString()));
 895                     }
 896                 }
 897                 logger.debug(EELFLoggerDelegate.debugLogger, (" return colName " + colNameBuf.toString()));
 898                 return colNameBuf.toString();
 899             }
 900
 901                 public List getChartValueColumnsList( int filter, HashMap formValues) { /*filter; all=0;create without new chart =1; createNewChart=2 */
 902                         List reportCols = reportRuntime.getAllColumns();
 903
 904                         ArrayList chartValueCols = new ArrayList();
 905                         int flag = 0;
 906                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 907                                 flag = 0;
 908                                 DataColumnType dc = (DataColumnType) iter.next();
 909                                 flag = reportRuntime.getDependsOnFormFieldFlag(dc, formValues);
 910
 911                                 if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) && flag == 0 && !(nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))) {
 912                                         if(nvl(dc.getChartGroup()).length()<=0) {
 913                                                 if( filter == 2 && (dc.isCreateInNewChart()!=null && dc.isCreateInNewChart().booleanValue())) {
 914                                                         chartValueCols.add(dc);
 915                                                 } else if (filter == 1 && (dc.isCreateInNewChart()==null || !dc.isCreateInNewChart().booleanValue())) {
 916                                                         chartValueCols.add(dc);
 917                                                 }
 918                                                 else if(filter == 0)
 919                                                         chartValueCols.add(dc);
 920                                         } else chartValueCols.add(dc);
 921                                 }
 922                         } // for
 923                         Collections.sort(chartValueCols, new ChartSeqComparator());
 924                         return chartValueCols;
 925                 } // getChartValueColumnsList
 926
 927                 public String parseTitle(String title, HashMap formValues) {
 928                         Set set = formValues.entrySet();
 929                         for(Iterator iter = set.iterator(); iter.hasNext(); ) {
 930                                 Map.Entry entry = (Entry<String,String>) iter.next();
 931                                 if(title.indexOf("["+ entry.getKey() + "]")!= -1) {
 932                                         title = Utils.replaceInString(title, "["+entry.getKey()+"]", nvl(
 933                             (String) entry.getValue(), ""));
 934                                 }
 935                         }
 936                         return title;
 937                 }
 938
 939        public java.util.Date getDateFromDateStr(String dateStr) {
 940                                 SimpleDateFormat MMDDYYYYFormat                         = new SimpleDateFormat("MM/dd/yyyy");
 941                         SimpleDateFormat EEEMMDDYYYYFormat                      = new SimpleDateFormat("EEE, MM/dd/yyyy"); //2012-11-01 00:00:00
 942                         SimpleDateFormat YYYYMMDDFormat                         = new SimpleDateFormat("yyyy/MM/dd");
 943                         SimpleDateFormat MONYYYYFormat                          = new SimpleDateFormat("MMM yyyy");
 944                         SimpleDateFormat MMYYYYFormat                           = new SimpleDateFormat("MM/yyyy");
 945                         SimpleDateFormat MMMMMDDYYYYFormat                      = new SimpleDateFormat("MMMMM dd, yyyy");
 946                         SimpleDateFormat timestampFormat                        = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
 947                         SimpleDateFormat timestampHrFormat              = new SimpleDateFormat("yyyy-MM-dd HH");
 948                         SimpleDateFormat timestampDayFormat             = new SimpleDateFormat("yyyy-MM-dd");
 949                         SimpleDateFormat DDMONYYYYFormat                = new SimpleDateFormat("dd-MMM-yyyy");
 950                         SimpleDateFormat MONTHYYYYFormat                = new SimpleDateFormat("MMMMM, yyyy");
 951                         SimpleDateFormat MMDDYYYYHHFormat               = new SimpleDateFormat("MM/dd/yyyy HH");
 952                         SimpleDateFormat MMDDYYYYHHMMSSFormat           = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
 953                         SimpleDateFormat MMDDYYYYHHMMFormat             = new SimpleDateFormat("MM/dd/yyyy HH:mm");
 954                         SimpleDateFormat YYYYMMDDHHMMSSFormat           = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
 955                         SimpleDateFormat YYYYMMDDHHMMFormat             = new SimpleDateFormat("yyyy/MM/dd HH:mm");
 956                         SimpleDateFormat DDMONYYYYHHMMSSFormat          = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
 957                         SimpleDateFormat DDMONYYYYHHMMFormat            = new SimpleDateFormat("dd-MMM-yyyy HH:mm");
 958                         SimpleDateFormat MMDDYYFormat                           = new SimpleDateFormat("MM/dd/yy");
 959                         SimpleDateFormat MMDDYYHHMMFormat               = new SimpleDateFormat("MM/dd/yy HH:mm");
 960                         SimpleDateFormat MMDDYYHHMMSSFormat             = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
 961                         SimpleDateFormat timestampFormat1               = new SimpleDateFormat("yyyy-M-d.HH.mm. s. S");
 962                         SimpleDateFormat MMDDYYYYHHMMZFormat            = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
 963                         SimpleDateFormat YYYYFormat                             = new SimpleDateFormat("yyyy");
 964                                 java.util.Date date = null;
 965
 966                         int formatFlag = 0;
 967
 968                         final int YEARFLAG = 1;
 969                         final int MONTHFLAG = 2;
 970                         final int DAYFLAG = 3;
 971                         final int HOURFLAG = 4;
 972                         final int MINFLAG = 5;
 973                         final int SECFLAG = 6;
 974                         final int MILLISECFLAG = 7;
 975                         final int DAYOFTHEWEEKFLAG = 8;
 976                         final int FLAGDATE = 9;
 977
 978                                 date = MMDDYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
 979                     if(date!=null)
 980                                         formatFlag = SECFLAG;
 981                     if(date==null) {
 982                         date = EEEMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
 983                         if(date!=null)
 984                                                 formatFlag = DAYOFTHEWEEKFLAG;
 985                     }
 986                     if(date==null) {
 987                         date = MMDDYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
 988                         if(date!=null)
 989                                                 formatFlag = MINFLAG;
 990                     }
 991                     if(date==null) {
 992                         date = MMDDYYYYHHFormat.parse(dateStr, new ParsePosition(0));
 993                         if(date!=null)
 994                                                 formatFlag = HOURFLAG;
 995                     }
 996                     if(date==null) {
 997                         date = MMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
 998                         if(date!=null)
 999                                                 formatFlag = DAYFLAG;
1000                     }
1001                     if(date==null) {
1002                         date = YYYYMMDDFormat.parse(dateStr, new ParsePosition(0));
1003                         if(date!=null)
1004                                                 formatFlag = DAYFLAG;
1005                     }
1006                     if(date==null) {
1007                         date = timestampFormat.parse(dateStr, new ParsePosition(0));
1008                         if(date!=null)
1009                                                 formatFlag = SECFLAG;
1010                     }
1011                     if(date==null) {
1012                         date = timestampHrFormat.parse(dateStr, new ParsePosition(0));
1013                         if(date!=null)
1014                                                 formatFlag = HOURFLAG;
1015                     }
1016                     if(date==null) {
1017                         date = timestampDayFormat.parse(dateStr, new ParsePosition(0));
1018                         if(date!=null)
1019                                                 formatFlag = DAYFLAG;
1020                     }
1021
1022                     if(date==null) {
1023                         date = MONYYYYFormat.parse(dateStr, new ParsePosition(0));
1024                         if(date!=null)
1025                                                 formatFlag = MONTHFLAG;
1026                     }
1027                     if(date==null) {
1028                         date = MMYYYYFormat.parse(dateStr, new ParsePosition(0));
1029                         if(date!=null)
1030                                                 formatFlag = MONTHFLAG;
1031                     }
1032                     if(date==null) {
1033                         date = MMMMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1034                         if(date!=null)
1035                                                 formatFlag = DAYFLAG;
1036                     }
1037                     if(date==null) {
1038                         date = MONTHYYYYFormat.parse(dateStr, new ParsePosition(0));
1039                         if(date!=null)
1040                                                 formatFlag = MONTHFLAG;
1041                     }
1042
1043                     if(date==null) {
1044                         date = YYYYMMDDHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1045                         if(date!=null)
1046                                                 formatFlag = SECFLAG;
1047                     }
1048
1049                     if(date==null) {
1050                         date = YYYYMMDDHHMMFormat.parse(dateStr, new ParsePosition(0));
1051                         if(date!=null)
1052                                                 formatFlag = MINFLAG;
1053                     }
1054
1055                     if(date==null) {
1056                         date = DDMONYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1057                         if(date!=null)
1058                                                 formatFlag = SECFLAG;
1059                     }
1060
1061                     if(date==null) {
1062                         date = DDMONYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1063                         if(date!=null)
1064                                                 formatFlag = MINFLAG;
1065                     }
1066
1067                     if(date==null) {
1068                         date = DDMONYYYYFormat.parse(dateStr, new ParsePosition(0));
1069                         if(date!=null)
1070                                                 formatFlag = DAYFLAG;
1071                     }
1072
1073                     if(date==null) {
1074                         date = MMDDYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1075                         if(date!=null)
1076                                                 formatFlag = SECFLAG;
1077                     }
1078
1079                     if(date==null) {
1080                         date = MMDDYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1081                         if(date!=null)
1082                                                 formatFlag = MINFLAG;
1083                     }
1084
1085                     if(date==null) {
1086                         date = MMDDYYFormat.parse(dateStr, new ParsePosition(0));
1087                         if(date!=null)
1088                                                 formatFlag = DAYFLAG;
1089                     }
1090
1091                     if(date==null) {
1092                         date = timestampFormat1.parse(dateStr, new ParsePosition(0));
1093                         if(date!=null)
1094                                                 formatFlag = SECFLAG;
1095                     }
1096
1097                     if(date==null) {
1098                         date = MMDDYYYYHHMMZFormat.parse(dateStr, new ParsePosition(0));
1099                         if(date!=null)
1100                                                 formatFlag = MINFLAG;
1101                     }
1102
1103                     if(date==null) {
1104                         date = YYYYFormat.parse(dateStr, new ParsePosition(0));
1105                         /* Some random numbers should not satisfy this year format. */
1106                         if(dateStr.length()>4)
1107                                                 date = null;
1108                         if(date!=null)
1109                                                 formatFlag = YEARFLAG;
1110                     }
1111                     if(date==null)
1112                         date = null;
1113                return date;
1114        }
1115
1116        public int getFlagFromDateStr(String dateStr) {
1117                                 SimpleDateFormat MMDDYYYYFormat                         = new SimpleDateFormat("MM/dd/yyyy");
1118                         SimpleDateFormat EEEMMDDYYYYFormat                      = new SimpleDateFormat("EEE, MM/dd/yyyy"); //2012-11-01 00:00:00
1119                         SimpleDateFormat YYYYMMDDFormat                         = new SimpleDateFormat("yyyy/MM/dd");
1120                         SimpleDateFormat MONYYYYFormat                          = new SimpleDateFormat("MMM yyyy");
1121                         SimpleDateFormat MMYYYYFormat                           = new SimpleDateFormat("MM/yyyy");
1122                         SimpleDateFormat MMMMMDDYYYYFormat                      = new SimpleDateFormat("MMMMM dd, yyyy");
1123                         SimpleDateFormat timestampFormat                        = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
1124                         SimpleDateFormat timestampHrFormat              = new SimpleDateFormat("yyyy-MM-dd HH");
1125                         SimpleDateFormat timestampDayFormat             = new SimpleDateFormat("yyyy-MM-dd");
1126                         SimpleDateFormat DDMONYYYYFormat                = new SimpleDateFormat("dd-MMM-yyyy");
1127                         SimpleDateFormat MONTHYYYYFormat                = new SimpleDateFormat("MMMMM, yyyy");
1128                         SimpleDateFormat MMDDYYYYHHFormat               = new SimpleDateFormat("MM/dd/yyyy HH");
1129                         SimpleDateFormat MMDDYYYYHHMMSSFormat           = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
1130                         SimpleDateFormat MMDDYYYYHHMMFormat             = new SimpleDateFormat("MM/dd/yyyy HH:mm");
1131                         SimpleDateFormat YYYYMMDDHHMMSSFormat           = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
1132                         SimpleDateFormat YYYYMMDDHHMMFormat             = new SimpleDateFormat("yyyy/MM/dd HH:mm");
1133                         SimpleDateFormat DDMONYYYYHHMMSSFormat          = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
1134                         SimpleDateFormat DDMONYYYYHHMMFormat            = new SimpleDateFormat("dd-MMM-yyyy HH:mm");
1135                         SimpleDateFormat MMDDYYFormat                           = new SimpleDateFormat("MM/dd/yy");
1136                         SimpleDateFormat MMDDYYHHMMFormat               = new SimpleDateFormat("MM/dd/yy HH:mm");
1137                         SimpleDateFormat MMDDYYHHMMSSFormat             = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
1138                         SimpleDateFormat timestampFormat1               = new SimpleDateFormat("yyyy-M-d.HH.mm. s. S");
1139                         SimpleDateFormat MMDDYYYYHHMMZFormat            = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
1140                         SimpleDateFormat YYYYFormat                             = new SimpleDateFormat("yyyy");
1141                                 java.util.Date date = null;
1142
1143                         int formatFlag = 0;
1144
1145                         final int YEARFLAG = 1;
1146                         final int MONTHFLAG = 2;
1147                         final int DAYFLAG = 3;
1148                         final int HOURFLAG = 4;
1149                         final int MINFLAG = 5;
1150                         final int SECFLAG = 6;
1151                         final int MILLISECFLAG = 7;
1152                         final int DAYOFTHEWEEKFLAG = 8;
1153                         final int FLAGDATE = 9;
1154
1155                                 date = MMDDYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1156                     if(date!=null)
1157                                         formatFlag = SECFLAG;
1158                     if(date==null) {
1159                         date = EEEMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1160                         if(date!=null)
1161                                                 formatFlag = DAYOFTHEWEEKFLAG;
1162                     }
1163                     if(date==null) {
1164                         date = MMDDYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1165                         if(date!=null)
1166                                                 formatFlag = MINFLAG;
1167                     }
1168                     if(date==null) {
1169                         date = MMDDYYYYHHFormat.parse(dateStr, new ParsePosition(0));
1170                         if(date!=null)
1171                                                 formatFlag = HOURFLAG;
1172                     }
1173                     if(date==null) {
1174                         date = MMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1175                         if(date!=null)
1176                                                 formatFlag = DAYFLAG;
1177                     }
1178                     if(date==null) {
1179                         date = YYYYMMDDFormat.parse(dateStr, new ParsePosition(0));
1180                         if(date!=null)
1181                                                 formatFlag = DAYFLAG;
1182                     }
1183                     if(date==null) {
1184                         date = timestampFormat.parse(dateStr, new ParsePosition(0));
1185                         if(date!=null)
1186                                                 formatFlag = SECFLAG;
1187                     }
1188                     if(date==null) {
1189                         date = timestampHrFormat.parse(dateStr, new ParsePosition(0));
1190                         if(date!=null)
1191                                                 formatFlag = HOURFLAG;
1192                     }
1193                     if(date==null) {
1194                         date = timestampDayFormat.parse(dateStr, new ParsePosition(0));
1195                         if(date!=null)
1196                                                 formatFlag = DAYFLAG;
1197                     }
1198                     if(date==null) {
1199                         date = MONYYYYFormat.parse(dateStr, new ParsePosition(0));
1200                         if(date!=null)
1201                                                 formatFlag = MONTHFLAG;
1202                     }
1203                     if(date==null) {
1204                         date = MMYYYYFormat.parse(dateStr, new ParsePosition(0));
1205                         if(date!=null)
1206                                                 formatFlag = MONTHFLAG;
1207                     }
1208                     if(date==null) {
1209                         date = MMMMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1210                         if(date!=null)
1211                                                 formatFlag = DAYFLAG;
1212                     }
1213                     if(date==null) {
1214                         date = MONTHYYYYFormat.parse(dateStr, new ParsePosition(0));
1215                         if(date!=null)
1216                                                 formatFlag = MONTHFLAG;
1217                     }
1218
1219                     if(date==null) {
1220                         date = YYYYMMDDHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1221                         if(date!=null)
1222                                                 formatFlag = SECFLAG;
1223                     }
1224
1225                     if(date==null) {
1226                         date = YYYYMMDDHHMMFormat.parse(dateStr, new ParsePosition(0));
1227                         if(date!=null)
1228                                                 formatFlag = MINFLAG;
1229                     }
1230
1231                     if(date==null) {
1232                         date = DDMONYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1233                         if(date!=null)
1234                                                 formatFlag = SECFLAG;
1235                     }
1236
1237                     if(date==null) {
1238                         date = DDMONYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1239                         if(date!=null)
1240                                                 formatFlag = MINFLAG;
1241                     }
1242
1243                     if(date==null) {
1244                         date = DDMONYYYYFormat.parse(dateStr, new ParsePosition(0));
1245                         if(date!=null)
1246                                                 formatFlag = DAYFLAG;
1247                     }
1248
1249                     if(date==null) {
1250                         date = MMDDYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1251                         if(date!=null)
1252                                                 formatFlag = SECFLAG;
1253                     }
1254
1255                     if(date==null) {
1256                         date = MMDDYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1257                         if(date!=null)
1258                                                 formatFlag = MINFLAG;
1259                     }
1260
1261                     if(date==null) {
1262                         date = MMDDYYFormat.parse(dateStr, new ParsePosition(0));
1263                         if(date!=null)
1264                                                 formatFlag = DAYFLAG;
1265                     }
1266
1267                     if(date==null) {
1268                         date = timestampFormat1.parse(dateStr, new ParsePosition(0));
1269                         if(date!=null)
1270                                                 formatFlag = SECFLAG;
1271                     }
1272
1273                     if(date==null) {
1274                         date = MMDDYYYYHHMMZFormat.parse(dateStr, new ParsePosition(0));
1275                         if(date!=null)
1276                                                 formatFlag = MINFLAG;
1277                     }
1278
1279                     if(date==null) {
1280                         date = YYYYFormat.parse(dateStr, new ParsePosition(0));
1281                         /* Some random numbers should not satisfy this year format. */
1282                         if(dateStr.length()>4)
1283                                                 date = null;
1284                         if(date!=null)
1285                                                 formatFlag = YEARFLAG;
1286                     }
1287                     if(date==null)
1288                         date = null;
1289                return formatFlag;
1290        }
1291
1292        public static String[] reverse(String[] arr) {
1293            List<String> list = Arrays.asList(arr);
1294            Collections.reverse(list);
1295            return (String[])list.toArray();
1296        }
1297
1298        public int getNumberOfDecimalPlaces(double num) {
1299            Double d = num;
1300            String[] splitter = d.toString().split("\\.");
1301            splitter[0].length();   // Before Decimal Count
1302            splitter[1].length();   // After  Decimal Count
1303            return splitter[1].length();
1304        }
1305
1306        public boolean getBooleanValue(String s) {
1307          return getBooleanValue(s,null);
1308        }
1309
1310        public boolean getBooleanValue(String s, Boolean defaultValue) {
1311            s = nvl(s);
1312            if(s.length()<=0 && defaultValue!=null)
1313                            return defaultValue.booleanValue();
1314            else if(s.length()<=0)
1315                            return false;
1316            else {
1317            if(s.toUpperCase().startsWith("Y") || s.toLowerCase().equals("true"))
1318               return true;
1319            else
1320                    return false;
1321            }
1322        }
1323
1324
1325        public String IntToLetter(int Int) {
1326             if (Int<27){
1327               return Character.toString((char)(Int+96));
1328             } else {
1329               if (Int%26==0) {
1330                 return IntToLetter((Int/26)-1)+IntToLetter((Int%26)+1);
1331               } else {
1332                 return IntToLetter(Int/26)+IntToLetter(Int%26);
1333               }
1334             }
1335           }
1336
1337
1338
1339
1340         private void clearReportRuntimeBackup(HttpServletRequest request) {
1341         HttpSession session = request.getSession();
1342                 session.removeAttribute(AppConstants.DRILLDOWN_REPORTS_LIST);
1343                 request.removeAttribute(AppConstants.DRILLDOWN_INDEX);
1344                 session.removeAttribute(AppConstants.DRILLDOWN_INDEX);
1345                 request.removeAttribute(AppConstants.FORM_DRILLDOWN_INDEX);
1346                 session.removeAttribute(AppConstants.FORM_DRILLDOWN_INDEX);
1347                 Enumeration<String> enum1 = session.getAttributeNames();
1348                 String attributeName = "";
1349                 while(enum1.hasMoreElements()) {
1350                         attributeName = enum1.nextElement();
1351                         if(attributeName.startsWith("parent_")) {
1352                                 session.removeAttribute(attributeName);
1353                         }
1354                 }
1355         session.removeAttribute(AppConstants.DRILLDOWN_REPORTS_LIST);
1356                 session.removeAttribute(AppConstants.SI_BACKUP_FOR_REP_ID);
1357                 session.removeAttribute(AppConstants.SI_COLUMN_LOOKUP);
1358         session.removeAttribute(AppConstants.SI_DASHBOARD_REP_ID);
1359         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP);
1360         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME);
1361         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP);
1362         session.removeAttribute(AppConstants.SI_DASHBOARD_CHARTDATA_MAP);
1363                 session.removeAttribute(AppConstants.SI_DASHBOARD_DISPLAYTYPE_MAP);
1364         session.removeAttribute(AppConstants.SI_DATA_SIZE_FOR_TEXTFIELD_POPUP);
1365         session.removeAttribute(AppConstants.SI_MAP);
1366                 session.removeAttribute(AppConstants.SI_MAP_OBJECT);
1367         session.removeAttribute(AppConstants.SI_REPORT_DEFINITION);
1368         session.removeAttribute(AppConstants.SI_REPORT_RUNTIME);
1369                 session.removeAttribute(AppConstants.SI_REPORT_RUN_BACKUP);
1370         session.removeAttribute(AppConstants.SI_REPORT_SCHEDULE);
1371         session.removeAttribute(AppConstants.RI_REPORT_DATA);
1372         session.removeAttribute(AppConstants.RI_CHART_DATA);
1373         session.removeAttribute(AppConstants.SI_FORMFIELD_INFO);
1374         session.removeAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO);
1375
1376         } // clearReportRuntimeBackup
1377
1378
1379     public static synchronized java.util.HashMap getRequestParametersMap(ReportRuntime rr, HttpServletRequest request)
1380     {
1381         HashMap valuesMap = new HashMap();
1382
1383                 ReportFormFields rff = rr.getReportFormFields();
1384
1385                 int idx = 0;
1386                 FormField ff = null;
1387
1388                 Map fieldNameMap = new HashMap();
1389                 int countOfFields = 0 ;
1390
1391
1392                 for(rff.resetNext(); rff.hasNext(); idx++) {
1393                          ff = rff.getNext();
1394                          fieldNameMap.put(ff.getFieldName(), ff.getFieldDisplayName());
1395                          countOfFields++;
1396                 }
1397
1398                 List formParameter = new ArrayList();
1399                 String formField = "";
1400                 for(int i = 0 ; i < rff.size(); i++) {
1401                         ff = ((FormField)rff.getFormField(i));
1402                         formField = ff.getFieldName();
1403                         boolean isMultiValue = false;
1404                         isMultiValue = ff.getFieldType().equals(FormField.FFT_CHECK_BOX)
1405                         || ff.getFieldType().equals(FormField.FFT_LIST_MULTI);
1406                         boolean isTextArea = (ff.getFieldType().equals(FormField.FFT_TEXTAREA) && rr.getReportDefType()
1407                                         .equals(AppConstants.RD_SQL_BASED));
1408
1409                         if(request.getParameterValues(formField) != null && isMultiValue ) {
1410                                         String[] vals = request.getParameterValues(formField);
1411                                         StringBuffer value = new StringBuffer("");
1412                                         if(!AppUtils.getRequestFlag(request, AppConstants.RI_RESET_ACTION)) {
1413
1414                                                 if ( isMultiValue ) {
1415                                                         value.append("(");
1416                                                 }
1417                                                 for(int j = 0 ; j < vals.length; j++) {
1418                                                         if(isMultiValue)
1419                                                                 value.append("'");
1420                                                         try {
1421                                                                 if(vals[j] !=null && vals[j].length() > 0) {
1422                                                                         vals[j] = Utils.oracleSafe(vals[j]);
1423                                                                         value.append(java.net.URLDecoder.decode(vals[j], "UTF-8"));// + ",";
1424                                                                 }
1425                                                                 else
1426                                                                         value.append(vals[j]);
1427                                                         } catch (UnsupportedEncodingException ex) {value.append(vals[j]);}
1428                                                         catch (IllegalArgumentException ex1){value.append(vals[j]);}
1429                                                         catch (Exception ex2){
1430                                                                 value.append(vals[j]);
1431                                                         }
1432
1433
1434                                                         if(isMultiValue)
1435                                 value.append("'");
1436
1437                                                         if(j != vals.length -1) {
1438                                                                 value.append(",");
1439                                                         }
1440                                                 }
1441                                                 if(vals.length > 0) {
1442                                                         value.append(")");
1443                                                 }
1444                                         }
1445
1446                                  valuesMap.put(fieldNameMap.get(formField), value.toString());
1447                                  value = new StringBuffer("");
1448                     } else if(request.getParameter(formField) != null) {
1449                         if(isTextArea) {
1450                                 String value = "";
1451                                 value = request.getParameter(formField);
1452
1453                                 value = Utils.oracleSafe(value);
1454                                 value = "('" + Utils.replaceInString(value, ",", "'|'") + "')";
1455                                 value = Utils.replaceInString(value, "|", ",");
1456                                 valuesMap.put(fieldNameMap.get(formField), value);
1457                                 value = "";
1458                         } else {
1459                                         String value = "";
1460                                         if(!AppUtils.getRequestFlag(request, AppConstants.RI_RESET_ACTION))
1461                                                 value = request.getParameter(formField);
1462                                         valuesMap.put(fieldNameMap.get(formField), Utils.oracleSafe(value));
1463                         }
1464
1465                 } else {
1466                         valuesMap.put(fieldNameMap.get(formField), "" );
1467                 }
1468
1469         }
1470
1471                 return valuesMap;
1472
1473         }
1474
1475 }