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
 646                 String sql = null;
 647             sql = generateChartSQL(userId, request);
 648             logger.debug(EELFLoggerDelegate.debugLogger, ("SQL generated " + sql));
 649                         String dbInfo = reportRuntime.getDBInfo();
 650                         DataSet ds = ConnectionUtils.getDataSet(sql, dbInfo);
 651                         if(ds.getRowCount()<=0) {
 652                                 logger.info(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 653                                 logger.info(EELFLoggerDelegate.debugLogger, (getChartType().toUpperCase()+" - " + "Report ID : " + reportRuntime.getReportID() + " DATA IS EMPTY"));
 654                                 logger.info(EELFLoggerDelegate.debugLogger, ("QUERY - " + sql));
 655                                 logger.info(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 656                         }
 657
 658                         return ds;
 659                 } // loadChartData
 660
 661                 public String generateChartSQL(String userId, HttpServletRequest request ) throws RaptorException  {
 662                         List reportCols = reportRuntime.getAllColumns();
 663                         List chartValueCols = getChartValueColumnsList(AppConstants.CHART_ALL_COLUMNS, null); // parameter is 0 has this requires all columns.
 664                         String reportSQL = reportRuntime.getWholeSQL();
 665
 666                         //Add order by clause
 667                         Pattern re1 = Pattern.compile("(^[\r\n]*|([\\s]))[Oo][Rr][Dd][Ee][Rr](.*?[^\r\n]*)[Bb][Yy]",Pattern.DOTALL);
 668                         Matcher matcher = re1.matcher(reportSQL);
 669
 670                         reportSQL = reportSQL + " ";
 671                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Ss][Ee][Ll][Ee][Cc][Tt]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" SELECT ");
 672                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Ww][Hh][Ee][Rr][Ee]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" WHERE ");
 673                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Ww][Hh][Ee][Nn]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" WHEN ");
 674                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Aa][Nn][Dd]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" AND ");
 675
 676                         if(!reportRuntime.getReportType().equals(AppConstants.RT_HIVE)) {
 677                                 int startPoint = reportSQL.lastIndexOf(" FROM ");
 678                                 if(startPoint <= 0) {
 679                                         startPoint = reportSQL.lastIndexOf(" from ");
 680                                 }
 681                                 if(startPoint <= 0) {
 682                                         startPoint = reportSQL.lastIndexOf("from ");
 683                                 }
 684                                 if(startPoint <= 0) {
 685                                         startPoint = reportSQL.lastIndexOf("FROM ");
 686                                 }
 687
 688                                 if (!matcher.find(startPoint)) {
 689                                         reportSQL = reportSQL + " ORDER BY 1" ;
 690                                 }
 691                         }
 692                         reportRuntime.setWholeSQL(reportSQL);
 693
 694                         logger.debug(EELFLoggerDelegate.debugLogger, (" *************************************************************************************** "));
 695                         logger.debug(EELFLoggerDelegate.debugLogger, ("WHOLE_SQL" + reportSQL));
 696                         logger.debug(EELFLoggerDelegate.debugLogger, (" *************************************************************************************** "));
 697
 698                         if (reportRuntime.getFormFieldList() != null) {
 699                                 for (Iterator iter = reportRuntime.getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 700                                         FormFieldType fft = (FormFieldType) iter.next();
 701                                         String fieldId = fft.getFieldId();
 702                                         String fieldDisplay = reportRuntime.getFormFieldDisplayName(fft);
 703                                         String formfield_value = "";
 704                                         formfield_value = AppUtils.getRequestNvlValue(request, fieldId);
 705                         String paramValue = nvl(formfield_value);
 706                                 if(paramValue.length()>0) {
 707                                         reportSQL = Utils.replaceInString(reportSQL,  fieldDisplay, nvl(
 708                                             paramValue, "NULL"));
 709                                 }
 710                                 reportSQL = Utils.replaceInString(reportSQL, "'" + fieldDisplay + "'", nvl(
 711                                         paramValue, "NULL"));
 712                                 reportSQL = Utils.replaceInString(reportSQL,  fieldDisplay , nvl(
 713                                             paramValue, "NULL"));
 714                                 }
 715                         }
 716                 logger.debug(EELFLoggerDelegate.debugLogger, ("SQL " + reportSQL));
 717                         String legendCol = "1 a";
 718                         StringBuffer groupCol = new StringBuffer();
 719                         StringBuffer seriesCol = new StringBuffer();
 720                         StringBuffer valueCols = new StringBuffer();
 721
 722                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 723                                 DataColumnType dc = (DataColumnType) iter.next();
 724                                 String colName = getColumnSelectStr(dc, request);
 725                                 if (nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))
 726                                         legendCol = getSelectExpr(dc, colName)+" " + dc.getColId();
 727                                 if ((!nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))
 728                                                 && (dc.getChartSeq()!=null &&  dc.getChartSeq().intValue() <= 0) && dc.isGroupBreak()) {
 729                                         groupCol.append(", ");
 730                                         groupCol.append(colName + " " +  dc.getColId());
 731                                 }
 732                         } // for
 733                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 734                                 DataColumnType dc = (DataColumnType) iter.next();
 735                                 if(dc.isChartSeries()!=null && dc.isChartSeries().booleanValue()) {
 736                                         seriesCol.append(", "+ getSelectExpr(dc,getColumnSelectStr(dc, request))+ " " +  dc.getColId());
 737                                 }
 738                         }
 739
 740                         for (Iterator iter = chartValueCols.iterator(); iter.hasNext();) {
 741                                 DataColumnType dc = (DataColumnType) iter.next();
 742                                 String colName = getColumnSelectStr(dc, request);
 743                                 String paramValue = "";
 744                                 if(AppUtils.nvl(colName).startsWith("[")) {
 745                                         if (reportRuntime.getFormFieldList() != null) {
 746                                                 for (Iterator iterC = reportRuntime.getFormFieldList().getFormField().iterator(); iterC.hasNext();) {
 747                                                         FormFieldType fft = (FormFieldType) iterC.next();
 748                                                         String fieldId = fft.getFieldId();
 749                                                         String fieldDisplay = reportRuntime.getFormFieldDisplayName(fft);
 750                                                         String formfield_value = "";
 751                                                         if(AppUtils.nvl(fieldDisplay).equals(colName)) {
 752                                                                 formfield_value = AppUtils.getRequestNvlValue(request, fieldId);
 753                                                                 paramValue = nvl(formfield_value);
 754                                                         }
 755                                                 }
 756
 757                                         }
 758
 759                                         seriesCol.append("," + (AppUtils.nvl(paramValue).length()>0? paramValue:"null") + " " + dc.getColId());
 760                                 } else {
 761                                  seriesCol.append("," + (AppUtils.nvl(paramValue).length()>0? paramValue:formatChartColumn(colName)) + " " + dc.getColId());
 762                                 }
 763                         } // for
 764
 765                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 766                                 DataColumnType dc = (DataColumnType) iter.next();
 767                                 String colName = dc.getDisplayName();
 768                                 String colValue = getColumnSelectStr(dc, request);
 769                                 if(colName.equals(AppConstants.RI_CHART_TOTAL_COL))
 770                                         seriesCol.append(", " + AppConstants.RI_CHART_TOTAL_COL + " " + AppConstants.RI_CHART_TOTAL_COL );
 771                                 if (colName.equals(AppConstants.RI_CHART_COLOR))
 772                                         seriesCol.append(", " + colValue + " " + AppConstants.RI_CHART_COLOR );
 773                                 if(colName.equals(AppConstants.RI_CHART_MARKER_START))
 774                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_START + " " + AppConstants.RI_CHART_MARKER_START );
 775                                 if(colName.equals(AppConstants.RI_CHART_MARKER_END))
 776                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_END + " " + AppConstants.RI_CHART_MARKER_END );
 777                                 if(colName.equals(AppConstants.RI_CHART_MARKER_TEXT_LEFT))
 778                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_TEXT_LEFT + " " + AppConstants.RI_CHART_MARKER_TEXT_LEFT );
 779                                 if(colName.equals(AppConstants.RI_CHART_MARKER_TEXT_RIGHT))
 780                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_TEXT_RIGHT + " " + AppConstants.RI_CHART_MARKER_TEXT_RIGHT );
 781                         }
 782
 783                 String final_sql = "";
 784                 reportSQL = Utils.replaceInString(reportSQL, " from ", " FROM ");
 785                 reportSQL = Utils.replaceInString(reportSQL, " From ", " FROM ");
 786                 reportSQL = Utils.replaceInString(reportSQL, " select ", " SELECT ");
 787                 reportSQL = Utils.replaceInString(reportSQL, " union ", " UNION ");
 788
 789                 int pos = 0;
 790                 int pos_first_select = 0;
 791                 int pos_dup_select = 0;
 792                 int pos_prev_select = 0;
 793                 int pos_last_select = 0;
 794                 if (reportSQL.indexOf("FROM", pos)!=-1) {
 795                     pos = reportSQL.indexOf("FROM", pos);
 796                     pos_dup_select = reportSQL.lastIndexOf("SELECT",pos);
 797                     pos_first_select = reportSQL.indexOf("SELECT");
 798                     logger.debug(EELFLoggerDelegate.debugLogger, ("pos_select " + pos_first_select + " " + pos_dup_select));
 799                     if(pos_dup_select > pos_first_select) {
 800                         logger.debug(EELFLoggerDelegate.debugLogger, ("********pos_dup_select ********" + pos_dup_select));
 801                         pos_prev_select = pos_first_select;
 802                         pos_last_select = pos_dup_select;
 803                         while (pos_last_select > pos_prev_select) {
 804                             logger.debug(EELFLoggerDelegate.debugLogger, ("pos_last , pos_prev " + pos_last_select + " " + pos_prev_select));
 805                             pos = reportSQL.indexOf("FROM", pos+2);
 806                             pos_prev_select = pos_last_select;
 807                             pos_last_select = reportSQL.lastIndexOf("SELECT",pos);
 808                             logger.debug(EELFLoggerDelegate.debugLogger, ("in WHILE LOOP LAST " + pos_last_select));
 809                         }
 810                      }
 811
 812                   }
 813                  final_sql += " "+reportSQL.substring(pos);
 814                  logger.debug(EELFLoggerDelegate.debugLogger, ("Final SQL " + final_sql));
 815                  String sql =  "SELECT " + legendCol + ", " + legendCol+"_1" + seriesCol.toString()+ nvl(valueCols.toString(), ", 1")
 816                                         + groupCol.toString()
 817                                         + final_sql;
 818                  logger.debug(EELFLoggerDelegate.debugLogger, ("Final sql in generateChartSQL " +sql));
 819
 820                  return sql;
 821                 } // generateChartSQL
 822
 823                 private String getColumnSelectStr(DataColumnType dc, HttpServletRequest request) {
 824                         String colName = dc.getColName();
 825                         String paramValue = null;
 826                         if (reportRuntime.getFormFieldList() != null) {
 827                                 for (Iterator iter = reportRuntime.getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 828                                         FormFieldType fft = (FormFieldType) iter.next();
 829                                         String fieldId = fft.getFieldId();
 830                                         String fieldDisplay = reportRuntime.getFormFieldDisplayName(fft);
 831                                         String formfield_value = "";
 832                                         formfield_value = AppUtils.getRequestNvlValue(request, fieldId);
 833                         paramValue = nvl(formfield_value);
 834                                 if(paramValue.length()>0) {
 835                                                         colName = Utils.replaceInString(colName, "'" + fieldDisplay + "'", "'"+nvl(
 836                                              paramValue, "NULL")+"'");
 837                                                         colName = Utils.replaceInString(colName,  fieldDisplay, nvl(
 838                                                                                         paramValue, "NULL"));
 839                                 }
 840                                 }
 841                                 return colName;
 842                         }
 843                         return colName;
 844                 } // getColumnSelectStr
 845
 846
 847
 848                 public String getSelectExpr(DataColumnType dct) {
 849                         return getSelectExpr(dct, dct.getColName() /* colName */);
 850                 } // getSelectExpr
 851
 852                 private String getSelectExpr(DataColumnType dct, String colName) {
 853                         String colType = dct.getColType();
 854                         if (colType.equals(AppConstants.CT_CHAR)
 855                                         || ((nvl(dct.getColFormat()).length() == 0) && (!colType
 856                                                         .equals(AppConstants.CT_DATE))))
 857                                 return colName;
 858                         else
 859                                 return "TO_CHAR(" + colName + ", '"
 860                                                 + nvl(dct.getColFormat(), AppConstants.DEFAULT_DATE_FORMAT) + "')";
 861                 } // getSelectExpr
 862
 863             private String formatChartColumn(String colName) {
 864                 logger.debug(EELFLoggerDelegate.debugLogger, ("Format Chart Column Input colName " + colName));
 865                 colName =  colName.trim();
 866                 colName = Utils.replaceInString(colName, "TO_CHAR", "to_char");
 867                 colName = Utils.replaceInString(colName, "to_number", "TO_NUMBER");
 868                 colName = colName.replaceAll(",[\\s]*\\(", ",(");
 869                 StringBuffer colNameBuf = new StringBuffer(colName);
 870                 int pos = 0, posFormatStart = 0, posFormatEnd = 0;
 871                 String format = "";
 872
 873                 if(colNameBuf.indexOf("999")==-1 && colNameBuf.indexOf("990")==-1) {
 874                         logger.debug(EELFLoggerDelegate.debugLogger, (" return colName " + colNameBuf.toString()));
 875                     return colNameBuf.toString();
 876                 }
 877
 878                 while (colNameBuf.indexOf("to_char")!=-1) {
 879                     if(colNameBuf.indexOf("999")!=-1 || colNameBuf.indexOf("990")!=-1) {
 880                         pos = colNameBuf.indexOf("to_char");
 881                         colNameBuf.insert(pos, " TO_NUMBER ( CR_RAPTOR.SAFE_TO_NUMBER (");
 882                         pos = colNameBuf.indexOf("to_char");
 883                         colNameBuf.replace(pos, pos+7, "TO_CHAR");
 884                         logger.debug(EELFLoggerDelegate.debugLogger, ("After adding to_number " + colNameBuf.toString()));
 885                         posFormatStart = colNameBuf.indexOf(",'", pos)+1;
 886                         posFormatEnd = colNameBuf.indexOf(")",posFormatStart);
 887                         logger.debug(EELFLoggerDelegate.debugLogger, (posFormatStart + " " + posFormatEnd + " "+ pos));
 888                         format = colNameBuf.substring(posFormatStart, posFormatEnd);
 889                         colNameBuf.insert(posFormatEnd+1, " ," + format + ") , "+ format + ")");
 890                         logger.debug(EELFLoggerDelegate.debugLogger, ("colNameBuf " + colNameBuf.toString()));
 891                     }
 892                 }
 893                 logger.debug(EELFLoggerDelegate.debugLogger, (" return colName " + colNameBuf.toString()));
 894                 return colNameBuf.toString();
 895             }
 896
 897                 public List getChartValueColumnsList( int filter, HashMap formValues) { /*filter; all=0;create without new chart =1; createNewChart=2 */
 898                         List reportCols = reportRuntime.getAllColumns();
 899
 900                         ArrayList chartValueCols = new ArrayList();
 901                         int flag = 0;
 902                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 903                                 flag = 0;
 904                                 DataColumnType dc = (DataColumnType) iter.next();
 905                                 flag = reportRuntime.getDependsOnFormFieldFlag(dc, formValues);
 906
 907                                 if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) && flag == 0 && !(nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))) {
 908                                         if(nvl(dc.getChartGroup()).length()<=0) {
 909                                                 if( filter == 2 && (dc.isCreateInNewChart()!=null && dc.isCreateInNewChart().booleanValue())) {
 910                                                         chartValueCols.add(dc);
 911                                                 } else if (filter == 1 && (dc.isCreateInNewChart()==null || !dc.isCreateInNewChart().booleanValue())) {
 912                                                         chartValueCols.add(dc);
 913                                                 }
 914                                                 else if(filter == 0)
 915                                                         chartValueCols.add(dc);
 916                                         } else chartValueCols.add(dc);
 917                                 }
 918                         } // for
 919                         Collections.sort(chartValueCols, new ChartSeqComparator());
 920                         return chartValueCols;
 921                 } // getChartValueColumnsList
 922
 923                 public String parseTitle(String title, HashMap formValues) {
 924                         Set set = formValues.entrySet();
 925                         for(Iterator iter = set.iterator(); iter.hasNext(); ) {
 926                                 Map.Entry entry = (Entry<String,String>) iter.next();
 927                                 if(title.indexOf("["+ entry.getKey() + "]")!= -1) {
 928                                         title = Utils.replaceInString(title, "["+entry.getKey()+"]", nvl(
 929                             (String) entry.getValue(), ""));
 930                                 }
 931                         }
 932                         return title;
 933                 }
 934
 935        public java.util.Date getDateFromDateStr(String dateStr) {
 936                                 SimpleDateFormat MMDDYYYYFormat                         = new SimpleDateFormat("MM/dd/yyyy");
 937                         SimpleDateFormat EEEMMDDYYYYFormat                      = new SimpleDateFormat("EEE, MM/dd/yyyy"); //2012-11-01 00:00:00
 938                         SimpleDateFormat YYYYMMDDFormat                         = new SimpleDateFormat("yyyy/MM/dd");
 939                         SimpleDateFormat MONYYYYFormat                          = new SimpleDateFormat("MMM yyyy");
 940                         SimpleDateFormat MMYYYYFormat                           = new SimpleDateFormat("MM/yyyy");
 941                         SimpleDateFormat MMMMMDDYYYYFormat                      = new SimpleDateFormat("MMMMM dd, yyyy");
 942                         SimpleDateFormat timestampFormat                        = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
 943                         SimpleDateFormat timestampHrFormat              = new SimpleDateFormat("yyyy-MM-dd HH");
 944                         SimpleDateFormat timestampDayFormat             = new SimpleDateFormat("yyyy-MM-dd");
 945                         SimpleDateFormat DDMONYYYYFormat                = new SimpleDateFormat("dd-MMM-yyyy");
 946                         SimpleDateFormat MONTHYYYYFormat                = new SimpleDateFormat("MMMMM, yyyy");
 947                         SimpleDateFormat MMDDYYYYHHFormat               = new SimpleDateFormat("MM/dd/yyyy HH");
 948                         SimpleDateFormat MMDDYYYYHHMMSSFormat           = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
 949                         SimpleDateFormat MMDDYYYYHHMMFormat             = new SimpleDateFormat("MM/dd/yyyy HH:mm");
 950                         SimpleDateFormat YYYYMMDDHHMMSSFormat           = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
 951                         SimpleDateFormat YYYYMMDDHHMMFormat             = new SimpleDateFormat("yyyy/MM/dd HH:mm");
 952                         SimpleDateFormat DDMONYYYYHHMMSSFormat          = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
 953                         SimpleDateFormat DDMONYYYYHHMMFormat            = new SimpleDateFormat("dd-MMM-yyyy HH:mm");
 954                         SimpleDateFormat MMDDYYFormat                           = new SimpleDateFormat("MM/dd/yy");
 955                         SimpleDateFormat MMDDYYHHMMFormat               = new SimpleDateFormat("MM/dd/yy HH:mm");
 956                         SimpleDateFormat MMDDYYHHMMSSFormat             = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
 957                         SimpleDateFormat timestampFormat1               = new SimpleDateFormat("yyyy-M-d.HH.mm. s. S");
 958                         SimpleDateFormat MMDDYYYYHHMMZFormat            = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
 959                         SimpleDateFormat YYYYFormat                             = new SimpleDateFormat("yyyy");
 960                                 java.util.Date date = null;
 961
 962                         int formatFlag = 0;
 963
 964                         final int YEARFLAG = 1;
 965                         final int MONTHFLAG = 2;
 966                         final int DAYFLAG = 3;
 967                         final int HOURFLAG = 4;
 968                         final int MINFLAG = 5;
 969                         final int SECFLAG = 6;
 970                         final int MILLISECFLAG = 7;
 971                         final int DAYOFTHEWEEKFLAG = 8;
 972                         final int FLAGDATE = 9;
 973
 974                                 date = MMDDYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
 975                     if(date!=null)
 976                                         formatFlag = SECFLAG;
 977                     if(date==null) {
 978                         date = EEEMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
 979                         if(date!=null)
 980                                                 formatFlag = DAYOFTHEWEEKFLAG;
 981                     }
 982                     if(date==null) {
 983                         date = MMDDYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
 984                         if(date!=null)
 985                                                 formatFlag = MINFLAG;
 986                     }
 987                     if(date==null) {
 988                         date = MMDDYYYYHHFormat.parse(dateStr, new ParsePosition(0));
 989                         if(date!=null)
 990                                                 formatFlag = HOURFLAG;
 991                     }
 992                     if(date==null) {
 993                         date = MMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
 994                         if(date!=null)
 995                                                 formatFlag = DAYFLAG;
 996                     }
 997                     if(date==null) {
 998                         date = YYYYMMDDFormat.parse(dateStr, new ParsePosition(0));
 999                         if(date!=null)
1000                                                 formatFlag = DAYFLAG;
1001                     }
1002                     if(date==null) {
1003                         date = timestampFormat.parse(dateStr, new ParsePosition(0));
1004                         if(date!=null)
1005                                                 formatFlag = SECFLAG;
1006                     }
1007                     if(date==null) {
1008                         date = timestampHrFormat.parse(dateStr, new ParsePosition(0));
1009                         if(date!=null)
1010                                                 formatFlag = HOURFLAG;
1011                     }
1012                     if(date==null) {
1013                         date = timestampDayFormat.parse(dateStr, new ParsePosition(0));
1014                         if(date!=null)
1015                                                 formatFlag = DAYFLAG;
1016                     }
1017
1018                     if(date==null) {
1019                         date = MONYYYYFormat.parse(dateStr, new ParsePosition(0));
1020                         if(date!=null)
1021                                                 formatFlag = MONTHFLAG;
1022                     }
1023                     if(date==null) {
1024                         date = MMYYYYFormat.parse(dateStr, new ParsePosition(0));
1025                         if(date!=null)
1026                                                 formatFlag = MONTHFLAG;
1027                     }
1028                     if(date==null) {
1029                         date = MMMMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1030                         if(date!=null)
1031                                                 formatFlag = DAYFLAG;
1032                     }
1033                     if(date==null) {
1034                         date = MONTHYYYYFormat.parse(dateStr, new ParsePosition(0));
1035                         if(date!=null)
1036                                                 formatFlag = MONTHFLAG;
1037                     }
1038
1039                     if(date==null) {
1040                         date = YYYYMMDDHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1041                         if(date!=null)
1042                                                 formatFlag = SECFLAG;
1043                     }
1044
1045                     if(date==null) {
1046                         date = YYYYMMDDHHMMFormat.parse(dateStr, new ParsePosition(0));
1047                         if(date!=null)
1048                                                 formatFlag = MINFLAG;
1049                     }
1050
1051                     if(date==null) {
1052                         date = DDMONYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1053                         if(date!=null)
1054                                                 formatFlag = SECFLAG;
1055                     }
1056
1057                     if(date==null) {
1058                         date = DDMONYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1059                         if(date!=null)
1060                                                 formatFlag = MINFLAG;
1061                     }
1062
1063                     if(date==null) {
1064                         date = DDMONYYYYFormat.parse(dateStr, new ParsePosition(0));
1065                         if(date!=null)
1066                                                 formatFlag = DAYFLAG;
1067                     }
1068
1069                     if(date==null) {
1070                         date = MMDDYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1071                         if(date!=null)
1072                                                 formatFlag = SECFLAG;
1073                     }
1074
1075                     if(date==null) {
1076                         date = MMDDYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1077                         if(date!=null)
1078                                                 formatFlag = MINFLAG;
1079                     }
1080
1081                     if(date==null) {
1082                         date = MMDDYYFormat.parse(dateStr, new ParsePosition(0));
1083                         if(date!=null)
1084                                                 formatFlag = DAYFLAG;
1085                     }
1086
1087                     if(date==null) {
1088                         date = timestampFormat1.parse(dateStr, new ParsePosition(0));
1089                         if(date!=null)
1090                                                 formatFlag = SECFLAG;
1091                     }
1092
1093                     if(date==null) {
1094                         date = MMDDYYYYHHMMZFormat.parse(dateStr, new ParsePosition(0));
1095                         if(date!=null)
1096                                                 formatFlag = MINFLAG;
1097                     }
1098
1099                     if(date==null) {
1100                         date = YYYYFormat.parse(dateStr, new ParsePosition(0));
1101                         /* Some random numbers should not satisfy this year format. */
1102                         if(dateStr.length()>4)
1103                                                 date = null;
1104                         if(date!=null)
1105                                                 formatFlag = YEARFLAG;
1106                     }
1107                     if(date==null)
1108                         date = null;
1109                return date;
1110        }
1111
1112        public int getFlagFromDateStr(String dateStr) {
1113                                 SimpleDateFormat MMDDYYYYFormat                         = new SimpleDateFormat("MM/dd/yyyy");
1114                         SimpleDateFormat EEEMMDDYYYYFormat                      = new SimpleDateFormat("EEE, MM/dd/yyyy"); //2012-11-01 00:00:00
1115                         SimpleDateFormat YYYYMMDDFormat                         = new SimpleDateFormat("yyyy/MM/dd");
1116                         SimpleDateFormat MONYYYYFormat                          = new SimpleDateFormat("MMM yyyy");
1117                         SimpleDateFormat MMYYYYFormat                           = new SimpleDateFormat("MM/yyyy");
1118                         SimpleDateFormat MMMMMDDYYYYFormat                      = new SimpleDateFormat("MMMMM dd, yyyy");
1119                         SimpleDateFormat timestampFormat                        = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
1120                         SimpleDateFormat timestampHrFormat              = new SimpleDateFormat("yyyy-MM-dd HH");
1121                         SimpleDateFormat timestampDayFormat             = new SimpleDateFormat("yyyy-MM-dd");
1122                         SimpleDateFormat DDMONYYYYFormat                = new SimpleDateFormat("dd-MMM-yyyy");
1123                         SimpleDateFormat MONTHYYYYFormat                = new SimpleDateFormat("MMMMM, yyyy");
1124                         SimpleDateFormat MMDDYYYYHHFormat               = new SimpleDateFormat("MM/dd/yyyy HH");
1125                         SimpleDateFormat MMDDYYYYHHMMSSFormat           = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
1126                         SimpleDateFormat MMDDYYYYHHMMFormat             = new SimpleDateFormat("MM/dd/yyyy HH:mm");
1127                         SimpleDateFormat YYYYMMDDHHMMSSFormat           = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
1128                         SimpleDateFormat YYYYMMDDHHMMFormat             = new SimpleDateFormat("yyyy/MM/dd HH:mm");
1129                         SimpleDateFormat DDMONYYYYHHMMSSFormat          = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
1130                         SimpleDateFormat DDMONYYYYHHMMFormat            = new SimpleDateFormat("dd-MMM-yyyy HH:mm");
1131                         SimpleDateFormat MMDDYYFormat                           = new SimpleDateFormat("MM/dd/yy");
1132                         SimpleDateFormat MMDDYYHHMMFormat               = new SimpleDateFormat("MM/dd/yy HH:mm");
1133                         SimpleDateFormat MMDDYYHHMMSSFormat             = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
1134                         SimpleDateFormat timestampFormat1               = new SimpleDateFormat("yyyy-M-d.HH.mm. s. S");
1135                         SimpleDateFormat MMDDYYYYHHMMZFormat            = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
1136                         SimpleDateFormat YYYYFormat                             = new SimpleDateFormat("yyyy");
1137                                 java.util.Date date = null;
1138
1139                         int formatFlag = 0;
1140
1141                         final int YEARFLAG = 1;
1142                         final int MONTHFLAG = 2;
1143                         final int DAYFLAG = 3;
1144                         final int HOURFLAG = 4;
1145                         final int MINFLAG = 5;
1146                         final int SECFLAG = 6;
1147                         final int MILLISECFLAG = 7;
1148                         final int DAYOFTHEWEEKFLAG = 8;
1149                         final int FLAGDATE = 9;
1150
1151                                 date = MMDDYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1152                     if(date!=null)
1153                                         formatFlag = SECFLAG;
1154                     if(date==null) {
1155                         date = EEEMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1156                         if(date!=null)
1157                                                 formatFlag = DAYOFTHEWEEKFLAG;
1158                     }
1159                     if(date==null) {
1160                         date = MMDDYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1161                         if(date!=null)
1162                                                 formatFlag = MINFLAG;
1163                     }
1164                     if(date==null) {
1165                         date = MMDDYYYYHHFormat.parse(dateStr, new ParsePosition(0));
1166                         if(date!=null)
1167                                                 formatFlag = HOURFLAG;
1168                     }
1169                     if(date==null) {
1170                         date = MMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1171                         if(date!=null)
1172                                                 formatFlag = DAYFLAG;
1173                     }
1174                     if(date==null) {
1175                         date = YYYYMMDDFormat.parse(dateStr, new ParsePosition(0));
1176                         if(date!=null)
1177                                                 formatFlag = DAYFLAG;
1178                     }
1179                     if(date==null) {
1180                         date = timestampFormat.parse(dateStr, new ParsePosition(0));
1181                         if(date!=null)
1182                                                 formatFlag = SECFLAG;
1183                     }
1184                     if(date==null) {
1185                         date = timestampHrFormat.parse(dateStr, new ParsePosition(0));
1186                         if(date!=null)
1187                                                 formatFlag = HOURFLAG;
1188                     }
1189                     if(date==null) {
1190                         date = timestampDayFormat.parse(dateStr, new ParsePosition(0));
1191                         if(date!=null)
1192                                                 formatFlag = DAYFLAG;
1193                     }
1194                     if(date==null) {
1195                         date = MONYYYYFormat.parse(dateStr, new ParsePosition(0));
1196                         if(date!=null)
1197                                                 formatFlag = MONTHFLAG;
1198                     }
1199                     if(date==null) {
1200                         date = MMYYYYFormat.parse(dateStr, new ParsePosition(0));
1201                         if(date!=null)
1202                                                 formatFlag = MONTHFLAG;
1203                     }
1204                     if(date==null) {
1205                         date = MMMMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1206                         if(date!=null)
1207                                                 formatFlag = DAYFLAG;
1208                     }
1209                     if(date==null) {
1210                         date = MONTHYYYYFormat.parse(dateStr, new ParsePosition(0));
1211                         if(date!=null)
1212                                                 formatFlag = MONTHFLAG;
1213                     }
1214
1215                     if(date==null) {
1216                         date = YYYYMMDDHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1217                         if(date!=null)
1218                                                 formatFlag = SECFLAG;
1219                     }
1220
1221                     if(date==null) {
1222                         date = YYYYMMDDHHMMFormat.parse(dateStr, new ParsePosition(0));
1223                         if(date!=null)
1224                                                 formatFlag = MINFLAG;
1225                     }
1226
1227                     if(date==null) {
1228                         date = DDMONYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1229                         if(date!=null)
1230                                                 formatFlag = SECFLAG;
1231                     }
1232
1233                     if(date==null) {
1234                         date = DDMONYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1235                         if(date!=null)
1236                                                 formatFlag = MINFLAG;
1237                     }
1238
1239                     if(date==null) {
1240                         date = DDMONYYYYFormat.parse(dateStr, new ParsePosition(0));
1241                         if(date!=null)
1242                                                 formatFlag = DAYFLAG;
1243                     }
1244
1245                     if(date==null) {
1246                         date = MMDDYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1247                         if(date!=null)
1248                                                 formatFlag = SECFLAG;
1249                     }
1250
1251                     if(date==null) {
1252                         date = MMDDYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1253                         if(date!=null)
1254                                                 formatFlag = MINFLAG;
1255                     }
1256
1257                     if(date==null) {
1258                         date = MMDDYYFormat.parse(dateStr, new ParsePosition(0));
1259                         if(date!=null)
1260                                                 formatFlag = DAYFLAG;
1261                     }
1262
1263                     if(date==null) {
1264                         date = timestampFormat1.parse(dateStr, new ParsePosition(0));
1265                         if(date!=null)
1266                                                 formatFlag = SECFLAG;
1267                     }
1268
1269                     if(date==null) {
1270                         date = MMDDYYYYHHMMZFormat.parse(dateStr, new ParsePosition(0));
1271                         if(date!=null)
1272                                                 formatFlag = MINFLAG;
1273                     }
1274
1275                     if(date==null) {
1276                         date = YYYYFormat.parse(dateStr, new ParsePosition(0));
1277                         /* Some random numbers should not satisfy this year format. */
1278                         if(dateStr.length()>4)
1279                                                 date = null;
1280                         if(date!=null)
1281                                                 formatFlag = YEARFLAG;
1282                     }
1283                     if(date==null)
1284                         date = null;
1285                return formatFlag;
1286        }
1287
1288        public static String[] reverse(String[] arr) {
1289            List<String> list = Arrays.asList(arr);
1290            Collections.reverse(list);
1291            return (String[])list.toArray();
1292        }
1293
1294        public int getNumberOfDecimalPlaces(double num) {
1295            Double d = num;
1296            String[] splitter = d.toString().split("\\.");
1297            splitter[0].length();   // Before Decimal Count
1298            splitter[1].length();   // After  Decimal Count
1299            return splitter[1].length();
1300        }
1301
1302        public boolean getBooleanValue(String s) {
1303          return getBooleanValue(s,null);
1304        }
1305
1306        public boolean getBooleanValue(String s, Boolean defaultValue) {
1307            s = nvl(s);
1308            if(s.length()<=0 && defaultValue!=null)
1309                            return defaultValue.booleanValue();
1310            else if(s.length()<=0)
1311                            return false;
1312            else {
1313            if(s.toUpperCase().startsWith("Y") || s.toLowerCase().equals("true"))
1314               return true;
1315            else
1316                    return false;
1317            }
1318        }
1319
1320
1321        public String IntToLetter(int Int) {
1322             if (Int<27){
1323               return Character.toString((char)(Int+96));
1324             } else {
1325               if (Int%26==0) {
1326                 return IntToLetter((Int/26)-1)+IntToLetter((Int%26)+1);
1327               } else {
1328                 return IntToLetter(Int/26)+IntToLetter(Int%26);
1329               }
1330             }
1331           }
1332
1333
1334
1335
1336         private void clearReportRuntimeBackup(HttpServletRequest request) {
1337         HttpSession session = request.getSession();
1338                 session.removeAttribute(AppConstants.DRILLDOWN_REPORTS_LIST);
1339                 request.removeAttribute(AppConstants.DRILLDOWN_INDEX);
1340                 session.removeAttribute(AppConstants.DRILLDOWN_INDEX);
1341                 request.removeAttribute(AppConstants.FORM_DRILLDOWN_INDEX);
1342                 session.removeAttribute(AppConstants.FORM_DRILLDOWN_INDEX);
1343                 Enumeration<String> enum1 = session.getAttributeNames();
1344                 String attributeName = "";
1345                 while(enum1.hasMoreElements()) {
1346                         attributeName = enum1.nextElement();
1347                         if(attributeName.startsWith("parent_")) {
1348                                 session.removeAttribute(attributeName);
1349                         }
1350                 }
1351         session.removeAttribute(AppConstants.DRILLDOWN_REPORTS_LIST);
1352                 session.removeAttribute(AppConstants.SI_BACKUP_FOR_REP_ID);
1353                 session.removeAttribute(AppConstants.SI_COLUMN_LOOKUP);
1354         session.removeAttribute(AppConstants.SI_DASHBOARD_REP_ID);
1355         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP);
1356         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME);
1357         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP);
1358         session.removeAttribute(AppConstants.SI_DASHBOARD_CHARTDATA_MAP);
1359                 session.removeAttribute(AppConstants.SI_DASHBOARD_DISPLAYTYPE_MAP);
1360         session.removeAttribute(AppConstants.SI_DATA_SIZE_FOR_TEXTFIELD_POPUP);
1361         session.removeAttribute(AppConstants.SI_MAP);
1362                 session.removeAttribute(AppConstants.SI_MAP_OBJECT);
1363         session.removeAttribute(AppConstants.SI_REPORT_DEFINITION);
1364         session.removeAttribute(AppConstants.SI_REPORT_RUNTIME);
1365                 session.removeAttribute(AppConstants.SI_REPORT_RUN_BACKUP);
1366         session.removeAttribute(AppConstants.SI_REPORT_SCHEDULE);
1367         session.removeAttribute(AppConstants.RI_REPORT_DATA);
1368         session.removeAttribute(AppConstants.RI_CHART_DATA);
1369         session.removeAttribute(AppConstants.SI_FORMFIELD_INFO);
1370         session.removeAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO);
1371
1372         } // clearReportRuntimeBackup
1373
1374
1375     public static synchronized java.util.HashMap getRequestParametersMap(ReportRuntime rr, HttpServletRequest request)
1376     {
1377         HashMap valuesMap = new HashMap();
1378
1379                 ReportFormFields rff = rr.getReportFormFields();
1380
1381                 int idx = 0;
1382                 FormField ff = null;
1383
1384                 Map fieldNameMap = new HashMap();
1385                 int countOfFields = 0 ;
1386
1387
1388                 for(rff.resetNext(); rff.hasNext(); idx++) {
1389                          ff = rff.getNext();
1390                          fieldNameMap.put(ff.getFieldName(), ff.getFieldDisplayName());
1391                          countOfFields++;
1392                 }
1393
1394                 List formParameter = new ArrayList();
1395                 String formField = "";
1396                 for(int i = 0 ; i < rff.size(); i++) {
1397                         ff = ((FormField)rff.getFormField(i));
1398                         formField = ff.getFieldName();
1399                         boolean isMultiValue = false;
1400                         isMultiValue = ff.getFieldType().equals(FormField.FFT_CHECK_BOX)
1401                         || ff.getFieldType().equals(FormField.FFT_LIST_MULTI);
1402                         boolean isTextArea = (ff.getFieldType().equals(FormField.FFT_TEXTAREA) && rr.getReportDefType()
1403                                         .equals(AppConstants.RD_SQL_BASED));
1404
1405                         if(request.getParameterValues(formField) != null && isMultiValue ) {
1406                                         String[] vals = request.getParameterValues(formField);
1407                                         StringBuffer value = new StringBuffer("");
1408                                         if(!AppUtils.getRequestFlag(request, AppConstants.RI_RESET_ACTION)) {
1409
1410                                                 if ( isMultiValue ) {
1411                                                         value.append("(");
1412                                                 }
1413                                                 for(int j = 0 ; j < vals.length; j++) {
1414                                                         if(isMultiValue)
1415                                                                 value.append("'");
1416                                                         try {
1417                                                                 if(vals[j] !=null && vals[j].length() > 0) {
1418                                                                         vals[j] = Utils.oracleSafe(vals[j]);
1419                                                                         value.append(java.net.URLDecoder.decode(vals[j], "UTF-8"));
1420                                                                 }
1421                                                                 else
1422                                                                         value.append(vals[j]);
1423                                                         } catch (UnsupportedEncodingException ex) {value.append(vals[j]);}
1424                                                         catch (IllegalArgumentException ex1){value.append(vals[j]);}
1425                                                         catch (Exception ex2){
1426                                                                 value.append(vals[j]);
1427                                                         }
1428
1429
1430                                                         if(isMultiValue)
1431                                 value.append("'");
1432
1433                                                         if(j != vals.length -1) {
1434                                                                 value.append(",");
1435                                                         }
1436                                                 }
1437                                                 if(vals.length > 0) {
1438                                                         value.append(")");
1439                                                 }
1440                                         }
1441
1442                                  valuesMap.put(fieldNameMap.get(formField), value.toString());
1443                                  value = new StringBuffer("");
1444                     } else if(request.getParameter(formField) != null) {
1445                         if(isTextArea) {
1446                                 String value = "";
1447                                 value = request.getParameter(formField);
1448
1449                                 value = Utils.oracleSafe(value);
1450                                 value = "('" + Utils.replaceInString(value, ",", "'|'") + "')";
1451                                 value = Utils.replaceInString(value, "|", ",");
1452                                 valuesMap.put(fieldNameMap.get(formField), value);
1453                                 value = "";
1454                         } else {
1455                                         String value = "";
1456                                         if(!AppUtils.getRequestFlag(request, AppConstants.RI_RESET_ACTION))
1457                                                 value = request.getParameter(formField);
1458                                         valuesMap.put(fieldNameMap.get(formField), Utils.oracleSafe(value));
1459                         }
1460
1461                 } else {
1462                         valuesMap.put(fieldNameMap.get(formField), "" );
1463                 }
1464
1465         }
1466
1467                 return valuesMap;
1468
1469         }
1470
1471 }