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 (dc.isCalculated()) {
 829                         if (reportRuntime.getFormFieldList() != null) {
 830                                 for (Iterator iter = reportRuntime.getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 831                                         FormFieldType fft = (FormFieldType) iter.next();
 832                                         String fieldId = fft.getFieldId();
 833                                         String fieldDisplay = reportRuntime.getFormFieldDisplayName(fft);
 834                                         String formfield_value = "";
 835                                         formfield_value = AppUtils.getRequestNvlValue(request, fieldId);
 836                         paramValue = nvl(formfield_value);
 837                                 if(paramValue.length()>0) {
 838                                         /*sql = Utils.replaceInString(sql, "'" + fieldDisplay + "'", nvl(
 839                                             paramValue, "NULL"));*/
 840                                                         colName = Utils.replaceInString(colName, "'" + fieldDisplay + "'", "'"+nvl(
 841                                              paramValue, "NULL")+"'");
 842                                                         colName = Utils.replaceInString(colName,  fieldDisplay, nvl(
 843                                                                                         paramValue, "NULL"));
 844                                 }
 845                                 }
 846                                 return colName;
 847                         }
 848                 //}
 849                         return colName;
 850                 } // getColumnSelectStr
 851
 852
 853
 854                 public String getSelectExpr(DataColumnType dct) {
 855                         // String colName =
 856                         // dct.isCalculated()?dct.getColName():((nvl(dct.getTableId()).length()>0)?(dct.getTableId()+"."+dct.getColName()):dct.getColName());
 857                         return getSelectExpr(dct, dct.getColName() /* colName */);
 858                 } // getSelectExpr
 859
 860                 private String getSelectExpr(DataColumnType dct, String colName) {
 861                         String colType = dct.getColType();
 862                         if (colType.equals(AppConstants.CT_CHAR)
 863                                         || ((nvl(dct.getColFormat()).length() == 0) && (!colType
 864                                                         .equals(AppConstants.CT_DATE))))
 865                                 return colName;
 866                         else
 867                                 return "TO_CHAR(" + colName + ", '"
 868                                                 + nvl(dct.getColFormat(), AppConstants.DEFAULT_DATE_FORMAT) + "')";
 869                 } // getSelectExpr
 870
 871             private String formatChartColumn(String colName) {
 872                 logger.debug(EELFLoggerDelegate.debugLogger, ("Format Chart Column Input colName " + colName));
 873                 colName =  colName.trim();
 874                 colName = Utils.replaceInString(colName, "TO_CHAR", "to_char");
 875                 colName = Utils.replaceInString(colName, "to_number", "TO_NUMBER");
 876                 //reportSQL = reportSQL.replaceAll("[\\s]*\\(", "(");
 877                 colName = colName.replaceAll(",[\\s]*\\(", ",(");
 878                 StringBuffer colNameBuf = new StringBuffer(colName);
 879                 int pos = 0, posFormatStart = 0, posFormatEnd = 0;
 880                 String format = "";
 881
 882                 if(colNameBuf.indexOf("999")==-1 && colNameBuf.indexOf("990")==-1) {
 883                         logger.debug(EELFLoggerDelegate.debugLogger, (" return colName " + colNameBuf.toString()));
 884                     return colNameBuf.toString();
 885                 }
 886
 887                 while (colNameBuf.indexOf("to_char")!=-1) {
 888                     if(colNameBuf.indexOf("999")!=-1 || colNameBuf.indexOf("990")!=-1) {
 889                         pos = colNameBuf.indexOf("to_char");
 890                         colNameBuf.insert(pos, " TO_NUMBER ( CR_RAPTOR.SAFE_TO_NUMBER (");
 891                         pos = colNameBuf.indexOf("to_char");
 892                         colNameBuf.replace(pos, pos+7, "TO_CHAR");
 893                         //colName = Utils.replaceInString(colNameBuf.toString(), "to_char", " TO_NUMBER ( CR_RAPTOR.SAFE_TO_NUMBER ( TO_CHAR ");
 894                         logger.debug(EELFLoggerDelegate.debugLogger, ("After adding to_number " + colNameBuf.toString()));
 895                         //posFormatStart = colNameBuf.lastIndexOf(",'")+1;
 896                         posFormatStart = colNameBuf.indexOf(",'", pos)+1;
 897                         posFormatEnd = colNameBuf.indexOf(")",posFormatStart);
 898                         logger.debug(EELFLoggerDelegate.debugLogger, (posFormatStart + " " + posFormatEnd + " "+ pos));
 899                         format = colNameBuf.substring(posFormatStart, posFormatEnd);
 900                         //posFormatEnd = colNameBuf.indexOf(")",posFormatEnd);
 901                         colNameBuf.insert(posFormatEnd+1, " ," + format + ") , "+ format + ")");
 902                         logger.debug(EELFLoggerDelegate.debugLogger, ("colNameBuf " + colNameBuf.toString()));
 903                     }
 904                 }
 905                 logger.debug(EELFLoggerDelegate.debugLogger, (" return colName " + colNameBuf.toString()));
 906                 return colNameBuf.toString();
 907             }
 908
 909                 public List getChartValueColumnsList( int filter, HashMap formValues) { /*filter; all=0;create without new chart =1; createNewChart=2 */
 910                         List reportCols = reportRuntime.getAllColumns();
 911
 912                         ArrayList chartValueCols = new ArrayList();
 913                         int flag = 0;
 914                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 915                                 flag = 0;
 916                                 DataColumnType dc = (DataColumnType) iter.next();
 917 //                          if(filter == 2 || filter == 1) {
 918                                 flag = reportRuntime.getDependsOnFormFieldFlag(dc, formValues);
 919
 920                                 if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) && flag == 0 && !(nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))) {
 921                                         if(nvl(dc.getChartGroup()).length()<=0) {
 922                                                 if( filter == 2 && (dc.isCreateInNewChart()!=null && dc.isCreateInNewChart().booleanValue())) {
 923                                                         chartValueCols.add(dc);
 924                                                 } else if (filter == 1 && (dc.isCreateInNewChart()==null || !dc.isCreateInNewChart().booleanValue())) {
 925                                                         chartValueCols.add(dc);
 926                                                 }
 927                                                 else if(filter == 0)
 928                                                         chartValueCols.add(dc);
 929                                         } else chartValueCols.add(dc);
 930                                 }
 931 //                              } else
 932 //                                      chartValueCols.add(dc);
 933                         } // for
 934                         Collections.sort(chartValueCols, new ChartSeqComparator());
 935                         return chartValueCols;
 936                 } // getChartValueColumnsList
 937
 938                 public String parseTitle(String title, HashMap formValues) {
 939                         Set set = formValues.entrySet();
 940                         for(Iterator iter = set.iterator(); iter.hasNext(); ) {
 941                                 Map.Entry entry = (Entry<String,String>) iter.next();
 942                                 if(title.indexOf("["+ entry.getKey() + "]")!= -1) {
 943                                         title = Utils.replaceInString(title, "["+entry.getKey()+"]", nvl(
 944                             (String) entry.getValue(), ""));
 945                                 }
 946                         }
 947                         return title;
 948                 }
 949
 950        public java.util.Date getDateFromDateStr(String dateStr) {
 951                                 SimpleDateFormat MMDDYYYYFormat                         = new SimpleDateFormat("MM/dd/yyyy");
 952                         SimpleDateFormat EEEMMDDYYYYFormat                      = new SimpleDateFormat("EEE, MM/dd/yyyy"); //2012-11-01 00:00:00
 953                         SimpleDateFormat YYYYMMDDFormat                         = new SimpleDateFormat("yyyy/MM/dd");
 954                         SimpleDateFormat MONYYYYFormat                          = new SimpleDateFormat("MMM yyyy");
 955                         SimpleDateFormat MMYYYYFormat                           = new SimpleDateFormat("MM/yyyy");
 956                         SimpleDateFormat MMMMMDDYYYYFormat                      = new SimpleDateFormat("MMMMM dd, yyyy");
 957                         SimpleDateFormat timestampFormat                        = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
 958                         SimpleDateFormat timestampHrFormat              = new SimpleDateFormat("yyyy-MM-dd HH");
 959                         SimpleDateFormat timestampDayFormat             = new SimpleDateFormat("yyyy-MM-dd");
 960                         SimpleDateFormat DDMONYYYYFormat                = new SimpleDateFormat("dd-MMM-yyyy");
 961                         SimpleDateFormat MONTHYYYYFormat                = new SimpleDateFormat("MMMMM, yyyy");
 962                         SimpleDateFormat MMDDYYYYHHFormat               = new SimpleDateFormat("MM/dd/yyyy HH");
 963                         SimpleDateFormat MMDDYYYYHHMMSSFormat           = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
 964                         SimpleDateFormat MMDDYYYYHHMMFormat             = new SimpleDateFormat("MM/dd/yyyy HH:mm");
 965                         SimpleDateFormat YYYYMMDDHHMMSSFormat           = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
 966                         SimpleDateFormat YYYYMMDDHHMMFormat             = new SimpleDateFormat("yyyy/MM/dd HH:mm");
 967                         SimpleDateFormat DDMONYYYYHHMMSSFormat          = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
 968                         SimpleDateFormat DDMONYYYYHHMMFormat            = new SimpleDateFormat("dd-MMM-yyyy HH:mm");
 969                         SimpleDateFormat MMDDYYFormat                           = new SimpleDateFormat("MM/dd/yy");
 970                         SimpleDateFormat MMDDYYHHMMFormat               = new SimpleDateFormat("MM/dd/yy HH:mm");
 971                         SimpleDateFormat MMDDYYHHMMSSFormat             = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
 972                         SimpleDateFormat timestampFormat1               = new SimpleDateFormat("yyyy-M-d.HH.mm. s. S");
 973                         SimpleDateFormat MMDDYYYYHHMMZFormat            = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
 974                         SimpleDateFormat YYYYFormat                             = new SimpleDateFormat("yyyy");
 975                                 java.util.Date date = null;
 976
 977                         int formatFlag = 0;
 978
 979                         final int YEARFLAG = 1;
 980                         final int MONTHFLAG = 2;
 981                         final int DAYFLAG = 3;
 982                         final int HOURFLAG = 4;
 983                         final int MINFLAG = 5;
 984                         final int SECFLAG = 6;
 985                         final int MILLISECFLAG = 7;
 986                         final int DAYOFTHEWEEKFLAG = 8;
 987                         final int FLAGDATE = 9;
 988                                 /*int yearFlag                  = 1;
 989                         int monthFlag           = 2;
 990                         int dayFlag             = 3;
 991                         int hourFlag            = 4;
 992                         int minFlag             = 5;
 993                         int secFlag             = 6;
 994                         int milliSecFlag        = 7;
 995                         int dayoftheweekFlag  = 8;
 996                                 int flagDate        = 10;
 997                                 */
 998
 999                                 date = MMDDYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1000                     if(date!=null)
1001                                         formatFlag = SECFLAG;
1002                     if(date==null) {
1003                         date = EEEMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1004                         if(date!=null)
1005                                                 formatFlag = DAYOFTHEWEEKFLAG;
1006                     }
1007                     if(date==null) {
1008                         date = MMDDYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1009                         if(date!=null)
1010                                                 formatFlag = MINFLAG;
1011                     }
1012                     if(date==null) {
1013                         //MMDDYYYYHHFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
1014                         date = MMDDYYYYHHFormat.parse(dateStr, new ParsePosition(0));
1015                         if(date!=null)
1016                                                 formatFlag = HOURFLAG;
1017                     }
1018                     if(date==null) {
1019                         date = MMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1020                         if(date!=null)
1021                                                 formatFlag = DAYFLAG;
1022                     }
1023                     if(date==null) {
1024                         date = YYYYMMDDFormat.parse(dateStr, new ParsePosition(0));
1025                         if(date!=null)
1026                                                 formatFlag = DAYFLAG;
1027                     }
1028                     if(date==null) {
1029                         date = timestampFormat.parse(dateStr, new ParsePosition(0));
1030                         if(date!=null)
1031                                                 formatFlag = SECFLAG;
1032                     }
1033                     if(date==null) {
1034                         date = timestampHrFormat.parse(dateStr, new ParsePosition(0));
1035                         if(date!=null)
1036                                                 formatFlag = HOURFLAG;
1037                     }
1038                     if(date==null) {
1039                         date = timestampDayFormat.parse(dateStr, new ParsePosition(0));
1040                         if(date!=null)
1041                                                 formatFlag = DAYFLAG;
1042                     }
1043
1044                     if(date==null) {
1045                         date = MONYYYYFormat.parse(dateStr, new ParsePosition(0));
1046                         if(date!=null)
1047                                                 formatFlag = MONTHFLAG;
1048                     }
1049                     if(date==null) {
1050                         date = MMYYYYFormat.parse(dateStr, new ParsePosition(0));
1051                         if(date!=null)
1052                                                 formatFlag = MONTHFLAG;
1053                     }
1054                     if(date==null) {
1055                         date = MMMMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1056                         if(date!=null)
1057                                                 formatFlag = DAYFLAG;
1058                     }
1059                     if(date==null) {
1060                         date = MONTHYYYYFormat.parse(dateStr, new ParsePosition(0));
1061                         if(date!=null)
1062                                                 formatFlag = MONTHFLAG;
1063                     }
1064
1065                     if(date==null) {
1066                         date = YYYYMMDDHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1067                         if(date!=null)
1068                                                 formatFlag = SECFLAG;
1069                     }
1070
1071                     if(date==null) {
1072                         date = YYYYMMDDHHMMFormat.parse(dateStr, new ParsePosition(0));
1073                         if(date!=null)
1074                                                 formatFlag = MINFLAG;
1075                     }
1076
1077                     if(date==null) {
1078                         date = DDMONYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1079                         if(date!=null)
1080                                                 formatFlag = SECFLAG;
1081                     }
1082
1083                     if(date==null) {
1084                         date = DDMONYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1085                         if(date!=null)
1086                                                 formatFlag = MINFLAG;
1087                     }
1088
1089                     if(date==null) {
1090                         date = DDMONYYYYFormat.parse(dateStr, new ParsePosition(0));
1091                         if(date!=null)
1092                                                 formatFlag = DAYFLAG;
1093                     }
1094
1095                     if(date==null) {
1096                         date = MMDDYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1097                         if(date!=null)
1098                                                 formatFlag = SECFLAG;
1099                     }
1100
1101                     if(date==null) {
1102                         date = MMDDYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1103                         if(date!=null)
1104                                                 formatFlag = MINFLAG;
1105                     }
1106
1107                     if(date==null) {
1108                         date = MMDDYYFormat.parse(dateStr, new ParsePosition(0));
1109                         if(date!=null)
1110                                                 formatFlag = DAYFLAG;
1111                     }
1112
1113                     if(date==null) {
1114                         date = timestampFormat1.parse(dateStr, new ParsePosition(0));
1115                         if(date!=null)
1116                                                 formatFlag = SECFLAG;
1117                     }
1118
1119                     if(date==null) {
1120                         date = MMDDYYYYHHMMZFormat.parse(dateStr, new ParsePosition(0));
1121                         if(date!=null)
1122                                                 formatFlag = MINFLAG;
1123                     }
1124
1125                     if(date==null) {
1126                         date = YYYYFormat.parse(dateStr, new ParsePosition(0));
1127                         /* Some random numbers should not satisfy this year format. */
1128                         if(dateStr.length()>4)
1129                                                 date = null;
1130                         if(date!=null)
1131                                                 formatFlag = YEARFLAG;
1132                     }
1133                     if(date==null)
1134                         date = null;
1135                return date;
1136        }
1137
1138        public int getFlagFromDateStr(String dateStr) {
1139                                 SimpleDateFormat MMDDYYYYFormat                         = new SimpleDateFormat("MM/dd/yyyy");
1140                         SimpleDateFormat EEEMMDDYYYYFormat                      = new SimpleDateFormat("EEE, MM/dd/yyyy"); //2012-11-01 00:00:00
1141                         SimpleDateFormat YYYYMMDDFormat                         = new SimpleDateFormat("yyyy/MM/dd");
1142                         SimpleDateFormat MONYYYYFormat                          = new SimpleDateFormat("MMM yyyy");
1143                         SimpleDateFormat MMYYYYFormat                           = new SimpleDateFormat("MM/yyyy");
1144                         SimpleDateFormat MMMMMDDYYYYFormat                      = new SimpleDateFormat("MMMMM dd, yyyy");
1145                         SimpleDateFormat timestampFormat                        = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
1146                         SimpleDateFormat timestampHrFormat              = new SimpleDateFormat("yyyy-MM-dd HH");
1147                         SimpleDateFormat timestampDayFormat             = new SimpleDateFormat("yyyy-MM-dd");
1148                         SimpleDateFormat DDMONYYYYFormat                = new SimpleDateFormat("dd-MMM-yyyy");
1149                         SimpleDateFormat MONTHYYYYFormat                = new SimpleDateFormat("MMMMM, yyyy");
1150                         SimpleDateFormat MMDDYYYYHHFormat               = new SimpleDateFormat("MM/dd/yyyy HH");
1151                         SimpleDateFormat MMDDYYYYHHMMSSFormat           = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
1152                         SimpleDateFormat MMDDYYYYHHMMFormat             = new SimpleDateFormat("MM/dd/yyyy HH:mm");
1153                         SimpleDateFormat YYYYMMDDHHMMSSFormat           = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
1154                         SimpleDateFormat YYYYMMDDHHMMFormat             = new SimpleDateFormat("yyyy/MM/dd HH:mm");
1155                         SimpleDateFormat DDMONYYYYHHMMSSFormat          = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
1156                         SimpleDateFormat DDMONYYYYHHMMFormat            = new SimpleDateFormat("dd-MMM-yyyy HH:mm");
1157                         SimpleDateFormat MMDDYYFormat                           = new SimpleDateFormat("MM/dd/yy");
1158                         SimpleDateFormat MMDDYYHHMMFormat               = new SimpleDateFormat("MM/dd/yy HH:mm");
1159                         SimpleDateFormat MMDDYYHHMMSSFormat             = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
1160                         SimpleDateFormat timestampFormat1               = new SimpleDateFormat("yyyy-M-d.HH.mm. s. S");
1161                         SimpleDateFormat MMDDYYYYHHMMZFormat            = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
1162                         SimpleDateFormat YYYYFormat                             = new SimpleDateFormat("yyyy");
1163                                 java.util.Date date = null;
1164
1165                         int formatFlag = 0;
1166
1167                         final int YEARFLAG = 1;
1168                         final int MONTHFLAG = 2;
1169                         final int DAYFLAG = 3;
1170                         final int HOURFLAG = 4;
1171                         final int MINFLAG = 5;
1172                         final int SECFLAG = 6;
1173                         final int MILLISECFLAG = 7;
1174                         final int DAYOFTHEWEEKFLAG = 8;
1175                         final int FLAGDATE = 9;
1176                                 /*int yearFlag                  = 1;
1177                         int monthFlag           = 2;
1178                         int dayFlag             = 3;
1179                         int hourFlag            = 4;
1180                         int minFlag             = 5;
1181                         int secFlag             = 6;
1182                         int milliSecFlag        = 7;
1183                         int dayoftheweekFlag  = 8;
1184                                 int flagDate        = 10;
1185                                 */
1186
1187                                 date = MMDDYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1188                     if(date!=null)
1189                                         formatFlag = SECFLAG;
1190                     if(date==null) {
1191                         date = EEEMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1192                         if(date!=null)
1193                                                 formatFlag = DAYOFTHEWEEKFLAG;
1194                     }
1195                     if(date==null) {
1196                         date = MMDDYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1197                         if(date!=null)
1198                                                 formatFlag = MINFLAG;
1199                     }
1200                     if(date==null) {
1201                         //MMDDYYYYHHFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
1202                         date = MMDDYYYYHHFormat.parse(dateStr, new ParsePosition(0));
1203                         if(date!=null)
1204                                                 formatFlag = HOURFLAG;
1205                     }
1206                     if(date==null) {
1207                         date = MMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1208                         if(date!=null)
1209                                                 formatFlag = DAYFLAG;
1210                     }
1211                     if(date==null) {
1212                         date = YYYYMMDDFormat.parse(dateStr, new ParsePosition(0));
1213                         if(date!=null)
1214                                                 formatFlag = DAYFLAG;
1215                     }
1216                     if(date==null) {
1217                         date = timestampFormat.parse(dateStr, new ParsePosition(0));
1218                         if(date!=null)
1219                                                 formatFlag = SECFLAG;
1220                     }
1221                     if(date==null) {
1222                         date = timestampHrFormat.parse(dateStr, new ParsePosition(0));
1223                         if(date!=null)
1224                                                 formatFlag = HOURFLAG;
1225                     }
1226                     if(date==null) {
1227                         date = timestampDayFormat.parse(dateStr, new ParsePosition(0));
1228                         if(date!=null)
1229                                                 formatFlag = DAYFLAG;
1230                     }
1231                     if(date==null) {
1232                         date = MONYYYYFormat.parse(dateStr, new ParsePosition(0));
1233                         if(date!=null)
1234                                                 formatFlag = MONTHFLAG;
1235                     }
1236                     if(date==null) {
1237                         date = MMYYYYFormat.parse(dateStr, new ParsePosition(0));
1238                         if(date!=null)
1239                                                 formatFlag = MONTHFLAG;
1240                     }
1241                     if(date==null) {
1242                         date = MMMMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1243                         if(date!=null)
1244                                                 formatFlag = DAYFLAG;
1245                     }
1246                     if(date==null) {
1247                         date = MONTHYYYYFormat.parse(dateStr, new ParsePosition(0));
1248                         if(date!=null)
1249                                                 formatFlag = MONTHFLAG;
1250                     }
1251
1252                     if(date==null) {
1253                         date = YYYYMMDDHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1254                         if(date!=null)
1255                                                 formatFlag = SECFLAG;
1256                     }
1257
1258                     if(date==null) {
1259                         date = YYYYMMDDHHMMFormat.parse(dateStr, new ParsePosition(0));
1260                         if(date!=null)
1261                                                 formatFlag = MINFLAG;
1262                     }
1263
1264                     if(date==null) {
1265                         date = DDMONYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1266                         if(date!=null)
1267                                                 formatFlag = SECFLAG;
1268                     }
1269
1270                     if(date==null) {
1271                         date = DDMONYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1272                         if(date!=null)
1273                                                 formatFlag = MINFLAG;
1274                     }
1275
1276                     if(date==null) {
1277                         date = DDMONYYYYFormat.parse(dateStr, new ParsePosition(0));
1278                         if(date!=null)
1279                                                 formatFlag = DAYFLAG;
1280                     }
1281
1282                     if(date==null) {
1283                         date = MMDDYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1284                         if(date!=null)
1285                                                 formatFlag = SECFLAG;
1286                     }
1287
1288                     if(date==null) {
1289                         date = MMDDYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1290                         if(date!=null)
1291                                                 formatFlag = MINFLAG;
1292                     }
1293
1294                     if(date==null) {
1295                         date = MMDDYYFormat.parse(dateStr, new ParsePosition(0));
1296                         if(date!=null)
1297                                                 formatFlag = DAYFLAG;
1298                     }
1299
1300                     if(date==null) {
1301                         date = timestampFormat1.parse(dateStr, new ParsePosition(0));
1302                         if(date!=null)
1303                                                 formatFlag = SECFLAG;
1304                     }
1305
1306                     if(date==null) {
1307                         date = MMDDYYYYHHMMZFormat.parse(dateStr, new ParsePosition(0));
1308                         if(date!=null)
1309                                                 formatFlag = MINFLAG;
1310                     }
1311
1312                     if(date==null) {
1313                         date = YYYYFormat.parse(dateStr, new ParsePosition(0));
1314                         /* Some random numbers should not satisfy this year format. */
1315                         if(dateStr.length()>4)
1316                                                 date = null;
1317                         if(date!=null)
1318                                                 formatFlag = YEARFLAG;
1319                     }
1320                     if(date==null)
1321                         date = null;
1322                return formatFlag;
1323        }
1324
1325        public static String[] reverse(String[] arr) {
1326            List<String> list = Arrays.asList(arr);
1327            Collections.reverse(list);
1328            return (String[])list.toArray();
1329        }
1330
1331        public int getNumberOfDecimalPlaces(double num) {
1332            Double d = num;
1333            String[] splitter = d.toString().split("\\.");
1334            splitter[0].length();   // Before Decimal Count
1335            splitter[1].length();   // After  Decimal Count
1336            return splitter[1].length();
1337        }
1338
1339        public boolean getBooleanValue(String s) {
1340          return getBooleanValue(s,null);
1341        }
1342
1343        public boolean getBooleanValue(String s, Boolean defaultValue) {
1344            s = nvl(s);
1345            if(s.length()<=0 && defaultValue!=null)
1346                            return defaultValue.booleanValue();
1347            else if(s.length()<=0)
1348                            return false;
1349            else {
1350            if(s.toUpperCase().startsWith("Y") || s.toLowerCase().equals("true"))
1351               return true;
1352            else
1353                    return false;
1354            }
1355        }
1356
1357
1358        public String IntToLetter(int Int) {
1359             if (Int<27){
1360               return Character.toString((char)(Int+96));
1361             } else {
1362               if (Int%26==0) {
1363                 return IntToLetter((Int/26)-1)+IntToLetter((Int%26)+1);
1364               } else {
1365                 return IntToLetter(Int/26)+IntToLetter(Int%26);
1366               }
1367             }
1368           }
1369
1370
1371
1372
1373         private void clearReportRuntimeBackup(HttpServletRequest request) {
1374                 //Session sess = Sessions.getCurrent(true)getCurrent();
1375         //HttpSession session = (HttpSession)sess.getNativeSession();
1376         HttpSession session = request.getSession();
1377                 session.removeAttribute(AppConstants.DRILLDOWN_REPORTS_LIST);
1378                 request.removeAttribute(AppConstants.DRILLDOWN_INDEX);
1379                 session.removeAttribute(AppConstants.DRILLDOWN_INDEX);
1380                 request.removeAttribute(AppConstants.FORM_DRILLDOWN_INDEX);
1381                 session.removeAttribute(AppConstants.FORM_DRILLDOWN_INDEX);
1382                 Enumeration<String> enum1 = session.getAttributeNames();
1383                 String attributeName = "";
1384                 while(enum1.hasMoreElements()) {
1385                         attributeName = enum1.nextElement();
1386                         if(attributeName.startsWith("parent_")) {
1387                                 session.removeAttribute(attributeName);
1388                         }
1389                 }
1390         session.removeAttribute(AppConstants.DRILLDOWN_REPORTS_LIST);
1391                 session.removeAttribute(AppConstants.SI_BACKUP_FOR_REP_ID);
1392                 session.removeAttribute(AppConstants.SI_COLUMN_LOOKUP);
1393         session.removeAttribute(AppConstants.SI_DASHBOARD_REP_ID);
1394         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP);
1395         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME);
1396         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP);
1397         session.removeAttribute(AppConstants.SI_DASHBOARD_CHARTDATA_MAP);
1398                 session.removeAttribute(AppConstants.SI_DASHBOARD_DISPLAYTYPE_MAP);
1399         session.removeAttribute(AppConstants.SI_DATA_SIZE_FOR_TEXTFIELD_POPUP);
1400         session.removeAttribute(AppConstants.SI_MAP);
1401                 session.removeAttribute(AppConstants.SI_MAP_OBJECT);
1402         session.removeAttribute(AppConstants.SI_REPORT_DEFINITION);
1403         session.removeAttribute(AppConstants.SI_REPORT_RUNTIME);
1404                 session.removeAttribute(AppConstants.SI_REPORT_RUN_BACKUP);
1405         session.removeAttribute(AppConstants.SI_REPORT_SCHEDULE);
1406         session.removeAttribute(AppConstants.RI_REPORT_DATA);
1407         session.removeAttribute(AppConstants.RI_CHART_DATA);
1408         session.removeAttribute(AppConstants.SI_FORMFIELD_INFO);
1409         session.removeAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO);
1410
1411         } // clearReportRuntimeBackup
1412
1413
1414     public static synchronized java.util.HashMap getRequestParametersMap(ReportRuntime rr, HttpServletRequest request)
1415     {
1416         HashMap valuesMap = new HashMap();
1417
1418                 ReportFormFields rff = rr.getReportFormFields();
1419
1420                 int idx = 0;
1421                 FormField ff = null;
1422
1423                 Map fieldNameMap = new HashMap();
1424                 int countOfFields = 0 ;
1425
1426
1427                 for(rff.resetNext(); rff.hasNext(); idx++) {
1428                          ff = rff.getNext();
1429                          fieldNameMap.put(ff.getFieldName(), ff.getFieldDisplayName());
1430                          countOfFields++;
1431                 }
1432
1433                 List formParameter = new ArrayList();
1434                 String formField = "";
1435                 for(int i = 0 ; i < rff.size(); i++) {
1436                         ff = ((FormField)rff.getFormField(i));
1437                         formField = ff.getFieldName();
1438                         boolean isMultiValue = false;
1439                         isMultiValue = ff.getFieldType().equals(FormField.FFT_CHECK_BOX)
1440                         || ff.getFieldType().equals(FormField.FFT_LIST_MULTI);
1441                         boolean isTextArea = (ff.getFieldType().equals(FormField.FFT_TEXTAREA) && rr.getReportDefType()
1442                                         .equals(AppConstants.RD_SQL_BASED));
1443
1444                         if(request.getParameterValues(formField) != null && isMultiValue ) {
1445                                         String[] vals = request.getParameterValues(formField);
1446                                         StringBuffer value = new StringBuffer("");
1447                                         if(!AppUtils.getRequestFlag(request, AppConstants.RI_RESET_ACTION)) {
1448
1449                                                 if ( isMultiValue ) {
1450                                                         value.append("(");
1451                                                 }
1452                                                 for(int j = 0 ; j < vals.length; j++) {
1453                                                         if(isMultiValue)
1454                                                                 value.append("'");
1455                                                         try {
1456                                                                 if(vals[j] !=null && vals[j].length() > 0) {
1457                                                                         vals[j] = Utils.oracleSafe(vals[j]);
1458                                                                         value.append(java.net.URLDecoder.decode(vals[j], "UTF-8"));// + ",";
1459                                                                 }
1460                                                                 else
1461                                                                         value.append(vals[j]);
1462                                                         } catch (UnsupportedEncodingException ex) {value.append(vals[j]);}
1463                                                         catch (IllegalArgumentException ex1){value.append(vals[j]);}
1464                                                         catch (Exception ex2){
1465                                                                 value.append(vals[j]);
1466                                                         }
1467
1468
1469                                                         if(isMultiValue)
1470                                 value.append("'");
1471
1472                                                         if(j != vals.length -1) {
1473                                                                 value.append(",");
1474                                                         }
1475                                                 }
1476                                                 if(vals.length > 0) {
1477                                                         value.append(")");
1478                                                 }
1479                                         }
1480
1481                                         //value = value.substring(0 , value.length());
1482
1483                                  valuesMap.put(fieldNameMap.get(formField), value.toString());
1484                                  value = new StringBuffer("");
1485                     } else if(request.getParameter(formField) != null) {
1486                         if(isTextArea) {
1487                                 String value = "";
1488                                 value = request.getParameter(formField);
1489
1490                                 value = Utils.oracleSafe(value);
1491                                 value = "('" + Utils.replaceInString(value, ",", "'|'") + "')";
1492                                 value = Utils.replaceInString(value, "|", ",");
1493                                 valuesMap.put(fieldNameMap.get(formField), value);
1494                                 value = "";
1495                         } else {
1496                                         String value = "";
1497                                         if(!AppUtils.getRequestFlag(request, AppConstants.RI_RESET_ACTION))
1498                                                 value = request.getParameter(formField);
1499                                         valuesMap.put(fieldNameMap.get(formField), Utils.oracleSafe(value));
1500                         }
1501
1502                 } else {
1503                         valuesMap.put(fieldNameMap.get(formField), "" );
1504                 }
1505
1506         }
1507
1508                 return valuesMap;
1509
1510         }
1511
1512 }