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 width, String height, boolean animation, String rotateLabels, boolean staggerLabels, boolean showMaxMin, boolean showLegend, boolean showControls, String topMargin, String bottomMargin, boolean showTitle, String subType
 306                 String userId = AppUtils.getUserID(request);
 307                 String width                            = chartOptionsMap.get("width");
 308                 String height                           = chartOptionsMap.get("height");
 309                 boolean animation                       = getBooleanValue(chartOptionsMap.get("animation"), true);
 310                 String rotateLabels             = chartOptionsMap.get("rotateLabels");
 311                 boolean staggerLabels           = getBooleanValue(chartOptionsMap.get("staggerLabels"));
 312                 boolean showMaxMin                      = getBooleanValue(chartOptionsMap.get("showMaxMin"), false);
 313                 boolean showLegend                      = getBooleanValue(chartOptionsMap.get("showLegend"), true);
 314                 boolean showControls            = getBooleanValue(chartOptionsMap.get("showControls"), true);
 315                 String topMargin                        = chartOptionsMap.get("topMargin");
 316                 String bottomMargin             = chartOptionsMap.get("bottomMargin");
 317                 String leftMargin                       = chartOptionsMap.get("leftMargin");
 318                 String rightMargin                      = chartOptionsMap.get("rightMargin");
 319                 boolean showTitle                       = getBooleanValue(chartOptionsMap.get("showTitle"), true);
 320                 String subType                          = chartOptionsMap.get("subType");
 321                 boolean stacked                         = getBooleanValue(chartOptionsMap.get("stacked"), false);
 322                 boolean horizontalBar           = getBooleanValue(chartOptionsMap.get("horizontalBar"), false);
 323                 boolean barRealTimeAxis         = getBooleanValue(chartOptionsMap.get("barRealTimeAxis"), true);
 324                 boolean barReduceXAxisLabels= getBooleanValue(chartOptionsMap.get("barReduceXAxisLabels"), false);
 325                 boolean timeAxis                        = getBooleanValue(chartOptionsMap.get("timeAxis"), true);
 326
 327
 328                 boolean logScale = getBooleanValue(chartOptionsMap.get("logScale"), false);
 329
 330                 int precision    =  2;
 331
 332                 try {
 333                         precision = Integer.parseInt(chartOptionsMap.get("precision"));
 334                 } catch (NumberFormatException ex) {
 335
 336                 }
 337
 338         final Long user_id = new Long((long) UserUtils.getUserId(request));
 339
 340         HttpSession session = null;
 341         session = request.getSession();
 342         String chartType = reportRuntime.getChartType();
 343             List l = reportRuntime.getAllColumns();
 344             List lGroups = reportRuntime.getAllChartGroups();
 345             HashMap mapYAxis = reportRuntime.getAllChartYAxis(reportRuntime.getReportParamValues());
 346             //ReportParamValues reportParamValues = reportRuntime.getReportParamValues();
 347             String chartLeftAxisLabel = reportRuntime.getFormFieldFilled(nvl(reportRuntime.getChartLeftAxisLabel()));
 348             String chartRightAxisLabel = reportRuntime.getFormFieldFilled(nvl(reportRuntime.getChartRightAxisLabel()));
 349
 350             boolean multipleSeries = reportRuntime.isMultiSeries();
 351
 352                 java.util.HashMap formValues = null;
 353                 formValues = getRequestParametersMap(reportRuntime, request);
 354
 355
 356             String legendColumnName = (reportRuntime.getChartLegendColumn()!=null)?reportRuntime.getChartLegendColumn().getDisplayName():"Legend Column";
 357                 boolean displayChart = reportRuntime.getDisplayChart();
 358                 HashMap additionalChartOptionsMap = new HashMap();
 359
 360                 StringBuffer wholeScript = new StringBuffer("");
 361
 362                 String title = reportRuntime.getReportTitle();
 363
 364                 title = parseTitle(title, formValues);
 365                 ObjectMapper mapper = new ObjectMapper();
 366                 ChartJSON chartJSON = new ChartJSON();
 367                 String sql = "";
 368                 if(displayChart) {
 369                         DataSet ds = null;
 370                         if(showData) {
 371
 372                                 try {
 373                                         if (!(chartType.equals(AppConstants.GT_HIERARCHICAL) || chartType.equals(AppConstants.GT_HIERARCHICAL_SUNBURST) || chartType.equals(AppConstants.GT_ANNOTATION_CHART))) {
 374                                                 sql = generateChartSQL(userId, request );
 375                                                 ds = (DataSet) loadChartData(new Long(user_id).toString(), request);
 376                                         } else if(chartType.equals(AppConstants.GT_ANNOTATION_CHART)) {
 377                                                 sql = reportRuntime.getWholeSQL();
 378                                                 String reportSQL = reportRuntime.getWholeSQL();
 379                                                 String dbInfo = reportRuntime.getDBInfo();
 380                                                 ds = ConnectionUtils.getDataSet(reportSQL, dbInfo);
 381                                                 if(ds.getRowCount()<=0) {
 382                                                         logger.debug(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 383                                                         logger.info(EELFLoggerDelegate.debugLogger, (chartType.toUpperCase()+" - " + "Report ID : " + reportRuntime.getReportID() + " DATA IS EMPTY" ));
 384                                                         logger.info(EELFLoggerDelegate.debugLogger, ("QUERY - " + reportSQL));
 385                                                         logger.info(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 386                                                 }
 387                                         } else if(chartType.equals(AppConstants.GT_HIERARCHICAL)||chartType.equals(AppConstants.GT_HIERARCHICAL_SUNBURST)) {
 388                                                 sql = reportRuntime.getWholeSQL();
 389                                                 String reportSQL = reportRuntime.getWholeSQL();
 390                                                 String dbInfo = reportRuntime.getDBInfo();
 391                                                 ds = ConnectionUtils.getDataSet(reportSQL, dbInfo);
 392                                         }
 393                                 } catch (RaptorException ex) {
 394                                         //throw new RaptorException("Error while loading chart data", ex);
 395                                         logger.error(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 396                                         logger.error(EELFLoggerDelegate.debugLogger, (chartType.toUpperCase()+" - " + "Report ID : " + reportRuntime.getReportID() + " ERROR THROWN FOR GIVEN QUERY "));
 397                                         logger.error(EELFLoggerDelegate.debugLogger, ("QUERY - " + reportRuntime.getWholeSQL()));
 398                                         logger.error(EELFLoggerDelegate.debugLogger, ("ERROR STACK TRACE" + ex.getMessage()));
 399                                         logger.error(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 400
 401                                 }
 402                                 if(ds==null) {
 403                                         //displayChart = false;
 404                                         if(chartType.equals(AppConstants.GT_ANNOTATION_CHART))
 405                                                 ds = new DataSet();
 406                                         else
 407                                                 displayChart = false;
 408                                 }
 409                         }
 410                         if(displayChart) {
 411
 412                                 chartJSON.setReportID(reportRuntime.getReportID());
 413                                 chartJSON.setReportName(reportRuntime.getReportName());
 414                                 chartJSON.setReportDescr(reportRuntime.getReportDescr());
 415                                 chartJSON.setReportTitle(reportRuntime.getReportTitle());
 416                                 chartJSON.setReportSubTitle(reportRuntime.getReportSubTitle());
 417
 418                                 List<DataColumnType> dcList = reportRuntime.getOnlyVisibleColumns();
 419                                 int countIndex = 0;
 420                                 ArrayList<ChartColumnJSON> chartColumnJSONList = new ArrayList<ChartColumnJSON>();
 421                                 for(Iterator iter = dcList.iterator(); iter.hasNext(); ) {
 422                                         ChartColumnJSON ccJSON = new ChartColumnJSON();
 423                                         DataColumnType dc = (DataColumnType) iter.next();
 424                                         ccJSON.setIndex(countIndex);
 425                                         ccJSON.setValue(dc.getColId());
 426                                         ccJSON.setTitle(dc.getDisplayName());
 427                                         countIndex++;
 428                                         chartColumnJSONList.add(ccJSON);
 429                                 }
 430                                 chartJSON.setChartColumnJSONList(chartColumnJSONList);
 431                                 chartJSON.setChartAvailable(displayChart);
 432
 433                                 ChartTypeJSON chartTypeJSON = new ChartTypeJSON();
 434                                 chartTypeJSON.setIndex(0);
 435                                 chartTypeJSON.setTitle("");
 436                                 chartTypeJSON.setValue(chartType);
 437                                 chartJSON.setChartTypeJSON(chartTypeJSON);
 438                                 chartJSON.setWidth(width);
 439                                 chartJSON.setHeight(height);
 440                                 chartJSON.setAnimation(animation);
 441                                 chartJSON.setRotateLabels(rotateLabels);
 442                                 chartJSON.setStaggerLabels(staggerLabels);
 443                                 chartJSON.setShowTitle(showTitle);
 444                                 DomainAxisJSON domainAxisJSON = new DomainAxisJSON();
 445                                 domainAxisJSON.setIndex(0);
 446                                 if(reportRuntime.getChartLegendColumn()!=null)
 447                                         domainAxisJSON.setTitle(reportRuntime.getChartLegendColumn().getDisplayName());
 448                                 else
 449                                         domainAxisJSON.setTitle("");
 450                                 if(reportRuntime.getChartLegendColumn()!=null)
 451                                         domainAxisJSON.setValue(reportRuntime.getChartLegendColumn().getColId());
 452                                 else
 453                                         domainAxisJSON.setValue("");
 454                                 chartJSON.setDomainAxisJSON(domainAxisJSON);
 455
 456
 457                                 List<DataColumnType> reportCols = reportRuntime.getAllColumns();
 458                                 boolean hasSeriesColumn = false;
 459                                 //ArrayList<Item>
 460                                 for (Iterator<DataColumnType> iter = reportCols.iterator(); iter
 461                                                 .hasNext();) {
 462                                         DataColumnType dct = (DataColumnType) iter.next();
 463                                         if(dct.isChartSeries()!=null && dct.isChartSeries().booleanValue()) {
 464                                                 chartJSON.setHasCategoryAxis(true);
 465                                                 CategoryAxisJSON categoryAxisJSON = new CategoryAxisJSON();
 466                                                 categoryAxisJSON.setIndex(0);
 467                                                 categoryAxisJSON.setTitle(dct.getDisplayName());
 468                                                 categoryAxisJSON.setValue(dct.getColId());
 469                                                 chartJSON.setCategoryAxisJSON(categoryAxisJSON);
 470                                         }
 471                                         //allColumns
 472                                                         //.add(new Item(dct.getColId(), dct.getDisplayName()));
 473                                 }
 474                                 //chartJSON.setCategoryAxis(categoryAxis);
 475                                 //chartJSON.set
 476
 477                                 List<DataColumnType> chartValueCols = reportRuntime.getChartValueColumnsList(AppConstants.CHART_ALL_COLUMNS, null);
 478                                 DataColumnType dct_RangeAxis = null;
 479                                 //int noChart = 0;
 480                                 //if(chartValueCols.size()<=0) {
 481                                         //chartValueCols.addAll(reportCols);
 482                                         //noChart = 1;
 483                                 //}
 484                                 if(chartValueCols.size() <= 0) {
 485                                         chartValueCols = reportCols;
 486                                 }
 487                                 ArrayList<RangeAxisJSON> rangeAxisJSONList = new ArrayList<RangeAxisJSON>();
 488                                 for (int k = 0; k < chartValueCols.size(); k++) {
 489                                         dct_RangeAxis = chartValueCols.get(k);
 490                                         RangeAxisJSON rangeAxisJSON = new RangeAxisJSON();
 491
 492                                         RangeAxisLabelJSON rangeAxisLabelJSON = new RangeAxisLabelJSON();
 493                                         rangeAxisLabelJSON.setIndex(0);
 494                                         rangeAxisLabelJSON.setTitle(dct_RangeAxis.getDisplayName());
 495                                         rangeAxisLabelJSON.setValue(dct_RangeAxis.getColId());
 496                                         rangeAxisJSON.setRangeAxisLabelJSON(rangeAxisLabelJSON);
 497                                         RangeLineTypeJSON rangeLineTypeJSON = new RangeLineTypeJSON();
 498                                         rangeLineTypeJSON.setIndex(0);
 499                                         rangeLineTypeJSON.setTitle("");
 500                                         rangeLineTypeJSON.setValue(dct_RangeAxis.getChartLineType());
 501                                         rangeAxisJSON.setRangeLineTypeJSON(rangeLineTypeJSON);
 502
 503                                         RangeColorJSON rangeColorJSON = new RangeColorJSON();
 504                                         rangeColorJSON.setIndex(0);
 505                                         rangeColorJSON.setTitle("");
 506                                         rangeColorJSON.setValue(dct_RangeAxis.getChartColor());
 507                                         rangeAxisJSON.setRangeColorJSON(rangeColorJSON);
 508                                         String chartGroup = "";
 509                                         chartGroup = AppUtils.nvl(dct_RangeAxis.getChartGroup());
 510                                         if(chartGroup.indexOf("|")!=-1)
 511                                                 chartGroup = chartGroup.substring(0, chartGroup.indexOf("|"));
 512
 513
 514                                         rangeAxisJSON.setRangeChartGroup(chartGroup);
 515                                         String yAxis = "";
 516                                         yAxis = AppUtils.nvl(dct_RangeAxis.getYAxis());
 517                                         if(yAxis.indexOf("|")!=-1)
 518                                                 yAxis = yAxis.substring(0, yAxis.indexOf("|"));
 519
 520                                         rangeAxisJSON.setRangeYAxis(yAxis);
 521                                         rangeAxisJSON.setShowAsArea((dct_RangeAxis.isIsRangeAxisFilled()!=null && dct_RangeAxis.isIsRangeAxisFilled().booleanValue())?true:false);
 522                                         rangeAxisJSONList.add(rangeAxisJSON);
 523                                 }
 524                                 CommonChartOptions commonChartOptions = new CommonChartOptions();
 525                                 commonChartOptions.setLegendPosition(AppUtils.nvl(reportRuntime.getLegendPosition()).length()>0?reportRuntime.getLegendPosition().toLowerCase():"top");
 526                                 String legendLabelAngle = "";
 527                                 legendLabelAngle = reportRuntime.getLegendLabelAngle().toLowerCase();
 528                                 commonChartOptions.setLegendLabelAngle(AppUtils.nvl(legendLabelAngle).length()>0?legendLabelAngle:"up45");
 529                                 commonChartOptions.setHideLegend(reportRuntime.hideChartLegend());
 530                                 commonChartOptions.setAnimateAnimatedChart(reportRuntime.isAnimateAnimatedChart());
 531                                 commonChartOptions.setTopMargin(reportRuntime.getTopMargin()!=null?reportRuntime.getTopMargin():new Integer("30"));
 532                                 commonChartOptions.setBottomMargin(reportRuntime.getBottomMargin()!=null?reportRuntime.getBottomMargin():new Integer("50"));
 533                                 commonChartOptions.setLeftMargin(reportRuntime.getLeftMargin()!=null?reportRuntime.getLeftMargin():new Integer("100"));
 534                                 commonChartOptions.setRightMargin(reportRuntime.getRightMargin()!=null?reportRuntime.getRightMargin():new Integer("60"));
 535                                 chartJSON.setCommonChartOptions(commonChartOptions);
 536
 537                                 if(chartType.equals(AppConstants.GT_BAR_3D)) {
 538                                         BarChartOptions barChartOptions = new BarChartOptions();
 539                                         barChartOptions.setDisplayBarControls(reportRuntime.displayBarControls()?true:false);
 540                                         barChartOptions.setMinimizeXAxisTickers(reportRuntime.isLessXaxisTickers()?true:false);
 541                                         barChartOptions.setStackedChart(reportRuntime.isChartStacked()?true:false);
 542                                         barChartOptions.setTimeAxis(reportRuntime.isTimeAxis()?true:false);
 543                                         barChartOptions.setVerticalOrientation(reportRuntime.isVerticalOrientation()?true:false);
 544                                         barChartOptions.setxAxisDateType(reportRuntime.isXAxisDateType()?true:false);
 545                                         barChartOptions.setyAxisLogScale(reportRuntime.isLogScale()?true:false);
 546                                         chartJSON.setBarChartOptions(barChartOptions);
 547                                         chartJSON.setTimeSeriesChartOptions(null);
 548                                         chartJSON.setPieChartOptions(null);
 549                                         chartJSON.setFlexTimeSeriesChartOptions(null);
 550
 551                                 } else if(chartType.equals(AppConstants.GT_TIME_SERIES)) {
 552                                         TimeSeriesChartOptions timeSeriesChartOptions = new TimeSeriesChartOptions();
 553                                         timeSeriesChartOptions.setAddXAxisTicker(reportRuntime.isAddXAxisTickers());
 554                                         timeSeriesChartOptions.setLineChartRenderer(AppUtils.nvl(reportRuntime.getTimeSeriesRender()).length()>0?reportRuntime.getTimeSeriesRender():"line");
 555                                         timeSeriesChartOptions.setMultiSeries(reportRuntime.isMultiSeries());
 556                                         timeSeriesChartOptions.setNonTimeAxis(reportRuntime.isTimeAxis());
 557                                         timeSeriesChartOptions.setShowXAxisLabel(reportRuntime.isShowXaxisLabel());
 558                                         chartJSON.setBarChartOptions(null);
 559                                         chartJSON.setTimeSeriesChartOptions(timeSeriesChartOptions);
 560                                         chartJSON.setPieChartOptions(null);
 561                                         chartJSON.setFlexTimeSeriesChartOptions(null);
 562                                 } else if(chartType.equals(AppConstants.GT_ANNOTATION_CHART) || chartType.equals(AppConstants.GT_FLEX_TIME_CHARTS)) {
 563                                         FlexTimeSeriesChartOptions flexTimeSeriesChartOptions = new FlexTimeSeriesChartOptions();
 564                                         flexTimeSeriesChartOptions.setZoomIn(reportRuntime.getZoomIn()!=null?reportRuntime.getZoomIn():new Integer("25"));
 565                                         String timeAxisTypeStr = "";
 566                                         timeAxisTypeStr = reportRuntime.getTimeAxisType().toLowerCase();
 567                                         flexTimeSeriesChartOptions.setTimeAxisType(timeAxisTypeStr);
 568                                         chartJSON.setBarChartOptions(null);
 569                                         chartJSON.setTimeSeriesChartOptions(null);
 570                                         chartJSON.setPieChartOptions(null);
 571                                         chartJSON.setFlexTimeSeriesChartOptions(flexTimeSeriesChartOptions);
 572                                 }
 573                                 chartJSON.setRangeAxisList(rangeAxisJSONList);
 574                                 chartJSON.setPrimaryAxisLabel(reportRuntime.getChartLeftAxisLabel());
 575                                 chartJSON.setSecondaryAxisLabel(reportRuntime.getChartRightAxisLabel());
 576                                 chartJSON.setMinRange(reportRuntime.getRangeAxisLowerLimit());
 577                                 chartJSON.setMaxRange(reportRuntime.getRangeAxisUpperLimit());
 578
 579                                 if(showData) {
 580                                         ArrayList<ArrayList<Row>> wholeList = new ArrayList<ArrayList<Row>>();
 581
 582                                         ArrayList<Row> rowList = new ArrayList<Row>();
 583                                         if(showData) {
 584                                                 for (int i = 0; i < ds.getRowCount(); i++) {
 585                                                         rowList = new ArrayList<Row>();
 586                                                         for (int j = 0; j<ds.getColumnCount(); j++) {
 587                                                                 Row row = new Row();
 588                                                                 row.setColId(ds.getColumnName(j));
 589                                                                 row.setDisplayValue(ds.getString(i, j));
 590                                                                 row.setDataType(ds.getColumnType(j));
 591                                                                 rowList.add(row);
 592                                                         }
 593                                                         wholeList.add(rowList);
 594
 595                                                 }
 596
 597                                                 chartJSON.setWholeList(wholeList);
 598                                         }
 599                                 }
 600
 601
 602
 603
 604
 605                         }
 606                 } else {
 607                         // chart is not visible
 608                         chartJSON.setReportID(reportRuntime.getReportID());
 609                         chartJSON.setReportName(reportRuntime.getReportName());
 610                         chartJSON.setReportDescr(reportRuntime.getReportDescr());
 611                         chartJSON.setReportTitle(reportRuntime.getReportTitle());
 612                         chartJSON.setReportSubTitle(reportRuntime.getReportSubTitle());
 613                         chartJSON.setChartAvailable(displayChart);
 614                         ChartTypeJSON chartTypeJSON = new ChartTypeJSON();
 615                         chartTypeJSON.setIndex(0);
 616                         chartTypeJSON.setTitle("");
 617                         chartTypeJSON.setValue(chartType);
 618                         chartJSON.setChartTypeJSON(chartTypeJSON);
 619                 }
 620                 //mapper.setVisibility(JsonMethod.FIELD, Visibility.ANY);
 621                 //mapper.setVisibilityChecker(mapper.getVisibilityChecker().with(JsonAutoDetect.Visibility.NONE));
 622                 mapper.configure(SerializationFeature.FAIL_ON_EMPTY_BEANS, false);
 623                 mapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
 624                 String jsonInString = "";
 625                 try {
 626                         jsonInString = mapper.writerWithDefaultPrettyPrinter().writeValueAsString(chartJSON);
 627                 } catch (Exception ex) {
 628                         ex.printStackTrace();
 629
 630                 }
 631
 632                 return jsonInString;
 633         }
 634
 635                 public String nvl(String s) {
 636                         return (s == null) ? "" : s;
 637                 }
 638
 639                 public String nvl(String s, String sDefault) {
 640                         return nvl(s).equals("") ? sDefault : s;
 641                 }
 642
 643                 public static String nvls(String s) {
 644                         return (s == null) ? "" : s;
 645                 }
 646
 647                 public static String nvls(String s, String sDefault) {
 648                         return nvls(s).equals("") ? sDefault : s;
 649                 }
 650
 651                 public boolean getFlagInBoolean(String s) {
 652                         return nvl(s).toUpperCase().startsWith("Y") || nvl(s).equalsIgnoreCase("true");
 653                 }
 654
 655                 public DataSet loadChartData(String userId, HttpServletRequest request) throws RaptorException {
 656                         if (nvl(getChartType()).length() == 0)
 657                                 return null;
 658                         //TODO: display chart function to be added.
 659                         //if (!getDisplayChart())
 660                         //      return null;
 661
 662                 String sql = null;
 663             sql = generateChartSQL(userId, request);
 664             logger.debug(EELFLoggerDelegate.debugLogger, ("SQL generated " + sql));
 665                         String dbInfo = reportRuntime.getDBInfo();
 666                         DataSet ds = ConnectionUtils.getDataSet(sql, dbInfo);
 667                         if(ds.getRowCount()<=0) {
 668                                 logger.info(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 669                                 logger.info(EELFLoggerDelegate.debugLogger, (getChartType().toUpperCase()+" - " + "Report ID : " + reportRuntime.getReportID() + " DATA IS EMPTY"));
 670                                 logger.info(EELFLoggerDelegate.debugLogger, ("QUERY - " + sql));
 671                                 logger.info(EELFLoggerDelegate.debugLogger, ("********************************************************************************"));
 672                         }
 673
 674                         return ds;
 675                 } // loadChartData
 676
 677                 public String generateChartSQL(String userId, HttpServletRequest request ) throws RaptorException  {
 678                         List reportCols = reportRuntime.getAllColumns();
 679                         List chartValueCols = getChartValueColumnsList(AppConstants.CHART_ALL_COLUMNS, null); // parameter is 0 has this requires all columns.
 680                         String reportSQL = reportRuntime.getWholeSQL();
 681
 682                         //Add order by clause
 683                         Pattern re1 = Pattern.compile("(^[\r\n]*|([\\s]))[Oo][Rr][Dd][Ee][Rr](.*?[^\r\n]*)[Bb][Yy]",Pattern.DOTALL);
 684                         //Pattern re1 = Pattern.compile("order(.*?[^\r\n]*)by", Pattern.DOTALL);
 685                         Matcher matcher = re1.matcher(reportSQL);
 686                         //Pattern re1 = Pattern.compile("(^[\r\n]*|([\\s]))[Oo][Rr][Dd][Ee][Rr][Tt](.*?[^\r\n]*)[Bb][Yy]",Pattern.DOTALL);
 687                         //int startPoint = sql.length()-30;
 688
 689                         reportSQL = reportSQL + " ";
 690                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Ss][Ee][Ll][Ee][Cc][Tt]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" SELECT ");
 691                         //reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Ff][Rr][Oo][Mm]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" FROM ");
 692                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Ww][Hh][Ee][Rr][Ee]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" WHERE ");
 693                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Ww][Hh][Ee][Nn]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" WHEN ");
 694                         reportSQL = Pattern.compile("(^[\r\n]*|([\\s]))[Aa][Nn][Dd]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(reportSQL).replaceAll(" AND ");
 695
 696                         if(!reportRuntime.getReportType().equals(AppConstants.RT_HIVE)) {
 697                                 int startPoint = reportSQL.lastIndexOf(" FROM ");
 698                                 if(startPoint <= 0) {
 699                                         startPoint = reportSQL.lastIndexOf(" from ");
 700                                 }
 701                                 if(startPoint <= 0) {
 702                                         startPoint = reportSQL.lastIndexOf("from ");
 703                                 }
 704                                 if(startPoint <= 0) {
 705                                         startPoint = reportSQL.lastIndexOf("FROM ");
 706                                 }
 707
 708                                 if (!matcher.find(startPoint)) {
 709                                         reportSQL = reportSQL + " ORDER BY 1" ;
 710                                 }
 711                         }
 712                         reportRuntime.setWholeSQL(reportSQL);
 713
 714                         logger.debug(EELFLoggerDelegate.debugLogger, (" *************************************************************************************** "));
 715                         logger.debug(EELFLoggerDelegate.debugLogger, ("WHOLE_SQL" + reportSQL));
 716                         logger.debug(EELFLoggerDelegate.debugLogger, (" *************************************************************************************** "));
 717
 718                         if (reportRuntime.getFormFieldList() != null) {
 719                                 for (Iterator iter = reportRuntime.getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 720                                         FormFieldType fft = (FormFieldType) iter.next();
 721                                         String fieldId = fft.getFieldId();
 722                                         String fieldDisplay = reportRuntime.getFormFieldDisplayName(fft);
 723                                         String formfield_value = "";
 724                                         formfield_value = AppUtils.getRequestNvlValue(request, fieldId);
 725                         String paramValue = nvl(formfield_value);
 726                                 if(paramValue.length()>0) {
 727                                         /*sql = Utils.replaceInString(sql, "'" + fieldDisplay + "'", nvl(
 728                                             paramValue, "NULL"));*/
 729                                         reportSQL = Utils.replaceInString(reportSQL,  fieldDisplay, nvl(
 730                                             paramValue, "NULL"));
 731                                 }
 732                                     /*sql = Utils.replaceInString(sql, "'" + fieldDisplay + "'", nvl(
 733                                             paramValue, "NULL"));*/
 734                                 reportSQL = Utils.replaceInString(reportSQL, "'" + fieldDisplay + "'", nvl(
 735                                         paramValue, "NULL"));
 736                                 reportSQL = Utils.replaceInString(reportSQL,  fieldDisplay , nvl(
 737                                             paramValue, "NULL"));
 738                                 }
 739                         }
 740                 logger.debug(EELFLoggerDelegate.debugLogger, ("SQL " + reportSQL));
 741                         String legendCol = "1 a";
 742                         // String valueCol = "1";
 743                         StringBuffer groupCol = new StringBuffer();
 744                         StringBuffer seriesCol = new StringBuffer();
 745                         StringBuffer valueCols = new StringBuffer();
 746
 747                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 748                                 DataColumnType dc = (DataColumnType) iter.next();
 749                                 String colName = getColumnSelectStr(dc, request);
 750                                 if (nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))
 751                                         legendCol = getSelectExpr(dc, colName)+" " + dc.getColId();
 752                                 // if(dc.getChartSeq()>0)
 753                                 // valueCol = "NVL("+colName+", 0) "+dc.getColId();
 754                                 if ((!nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))
 755                                                 && (dc.getChartSeq()!=null &&  dc.getChartSeq().intValue() <= 0) && dc.isGroupBreak()) {
 756                                         groupCol.append(", ");
 757                                         groupCol.append(colName + " " +  dc.getColId());
 758                                 }
 759                         } // for
 760                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 761                                 DataColumnType dc = (DataColumnType) iter.next();
 762                                 if(dc.isChartSeries()!=null && dc.isChartSeries().booleanValue()) {
 763                                         //System.out.println("*****************, "+ " " +getColumnSelectStr(dc, paramValues)+ " "+ getSelectExpr(dc,getColumnSelectStr(dc, paramValues)));
 764                                         seriesCol.append(", "+ getSelectExpr(dc,getColumnSelectStr(dc, request))+ " " +  dc.getColId());
 765                                 }
 766                         }
 767
 768                         /*for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 769                                 DataColumnType dc = (DataColumnType) iter.next();
 770                                 if(!dc.isChartSeries() && !(nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))) {
 771                                         //System.out.println("*****************, "+ " " +getColumnSelectStr(dc, paramValues)+ " "+ getSelectExpr(dc,getColumnSelectStr(dc, paramValues)));
 772                                         seriesCol.append(", "+ formatChartColumn(getSelectExpr(dc,getColumnSelectStr(dc, paramValues)))+ " " +  dc.getColId());
 773                         }
 774                         }*/
 775
 776                         for (Iterator iter = chartValueCols.iterator(); iter.hasNext();) {
 777                                 DataColumnType dc = (DataColumnType) iter.next();
 778                                 String colName = getColumnSelectStr(dc, request);
 779                                 String paramValue = "";
 780                                 if(AppUtils.nvl(colName).startsWith("[")) {
 781                                         if (reportRuntime.getFormFieldList() != null) {
 782                                                 for (Iterator iterC = reportRuntime.getFormFieldList().getFormField().iterator(); iterC.hasNext();) {
 783                                                         FormFieldType fft = (FormFieldType) iterC.next();
 784                                                         String fieldId = fft.getFieldId();
 785                                                         String fieldDisplay = reportRuntime.getFormFieldDisplayName(fft);
 786                                                         String formfield_value = "";
 787                                                         if(AppUtils.nvl(fieldDisplay).equals(colName)) {
 788                                                                 formfield_value = AppUtils.getRequestNvlValue(request, fieldId);
 789                                                                 paramValue = nvl(formfield_value);
 790                                                         }
 791                                                 }
 792
 793                                         }
 794
 795                                         seriesCol.append("," + (AppUtils.nvl(paramValue).length()>0? paramValue:"null") + " " + dc.getColId());
 796                                 } else {
 797                                 //valueCols.append(", NVL(" + formatChartColumn(colName) + ",0) " + dc.getColId());
 798                                  seriesCol.append("," + (AppUtils.nvl(paramValue).length()>0? paramValue:formatChartColumn(colName)) + " " + dc.getColId());
 799                                 }
 800                         } // for
 801
 802                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 803                                 DataColumnType dc = (DataColumnType) iter.next();
 804                                 String colName = dc.getDisplayName();
 805                                 String colValue = getColumnSelectStr(dc, request);
 806                                 //String colName = getColumnSelectStr(dc, formGrid);
 807                                 if(colName.equals(AppConstants.RI_CHART_TOTAL_COL))
 808                                         seriesCol.append(", " + AppConstants.RI_CHART_TOTAL_COL + " " + AppConstants.RI_CHART_TOTAL_COL );
 809                                 if (colName.equals(AppConstants.RI_CHART_COLOR))
 810                                         seriesCol.append(", " + colValue + " " + AppConstants.RI_CHART_COLOR );
 811                                 if(colName.equals(AppConstants.RI_CHART_MARKER_START))
 812                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_START + " " + AppConstants.RI_CHART_MARKER_START );
 813                                 if(colName.equals(AppConstants.RI_CHART_MARKER_END))
 814                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_END + " " + AppConstants.RI_CHART_MARKER_END );
 815                                 if(colName.equals(AppConstants.RI_CHART_MARKER_TEXT_LEFT))
 816                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_TEXT_LEFT + " " + AppConstants.RI_CHART_MARKER_TEXT_LEFT );
 817                                 if(colName.equals(AppConstants.RI_CHART_MARKER_TEXT_RIGHT))
 818                                         seriesCol.append(", " + AppConstants.RI_CHART_MARKER_TEXT_RIGHT + " " + AppConstants.RI_CHART_MARKER_TEXT_RIGHT );
 819                                 //if(colName.equals(AppConstants.RI_ANOMALY_TEXT))
 820                                         //seriesCol.append(", " + AppConstants.RI_ANOMALY_TEXT + " " + AppConstants.RI_ANOMALY_TEXT );
 821                         }
 822
 823                  //debugLogger.debug("ReportSQL Chart " + reportSQL );
 824                         /*for (Iterator iter = chartValueCols.iterator(); iter.hasNext();) {
 825                                 DataColumnType dc = (DataColumnType) iter.next();
 826                                 String colName = getColumnSelectStr(dc, paramValues);
 827                                 //valueCols.append(", NVL(" + formatChartColumn(colName) + ",0) " + dc.getColId());
 828                                 valueCols.append("," + formatChartColumn(colName) + " " + dc.getColId());
 829                         } // for
 830                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 831                                 DataColumnType dc = (DataColumnType) iter.next();
 832                                 String colName = getColumnSelectStr(dc, paramValues);
 833                                 //if(colName.equals(AppConstants.RI_CHART_TOTAL_COL) || colName.equals(AppConstants.RI_CHART_COLOR)) {
 834                                         if(colName.equals(AppConstants.RI_CHART_TOTAL_COL))
 835                                                 valueCols.append(", " + AppConstants.RI_CHART_TOTAL_COL + " " + AppConstants.RI_CHART_TOTAL_COL );
 836                                         if (colName.equals(AppConstants.RI_CHART_COLOR))
 837                                                 valueCols.append(", " + AppConstants.RI_CHART_COLOR + " " + AppConstants.RI_CHART_COLOR );
 838                                         if (colName.equals(AppConstants.RI_CHART_INCLUDE))
 839                                                 valueCols.append(", " + AppConstants.RI_CHART_INCLUDE + " " + AppConstants.RI_CHART_INCLUDE );
 840                                 //}
 841                         }*/
 842                 String final_sql = "";
 843                 reportSQL = Utils.replaceInString(reportSQL, " from ", " FROM ");
 844                 reportSQL = Utils.replaceInString(reportSQL, " From ", " FROM ");
 845                 reportSQL = Utils.replaceInString(reportSQL, " select ", " SELECT ");
 846                 reportSQL = Utils.replaceInString(reportSQL, " union ", " UNION ");
 847                 //reportSQL = reportSQL.replaceAll("[\\s]*\\(", "(");
 848 //              if(reportSQL.indexOf("UNION") != -1) {
 849 //                  if(reportSQL.indexOf("FROM(")!=-1)
 850 //                      final_sql += " "+reportSQL.substring(reportSQL.indexOf("FROM(") );
 851 //                  else if (reportSQL.indexOf("FROM (")!=-1)
 852 //                      final_sql += " "+reportSQL.substring(reportSQL.indexOf("FROM (") );
 853 //                  //TODO ELSE THROW ERROR
 854 //              }
 855 //              else {
 856 //                  final_sql += " "+reportSQL.substring(reportSQL.toUpperCase().indexOf(" FROM "));
 857 //              }
 858                 int pos = 0;
 859                 int pos_first_select = 0;
 860                 int pos_dup_select = 0;
 861                 int pos_prev_select = 0;
 862                 int pos_last_select = 0;
 863                 if (reportSQL.indexOf("FROM", pos)!=-1) {
 864                     pos = reportSQL.indexOf("FROM", pos);
 865                     pos_dup_select = reportSQL.lastIndexOf("SELECT",pos);
 866                     pos_first_select = reportSQL.indexOf("SELECT");//,pos);
 867                     logger.debug(EELFLoggerDelegate.debugLogger, ("pos_select " + pos_first_select + " " + pos_dup_select));
 868                     if(pos_dup_select > pos_first_select) {
 869                         logger.debug(EELFLoggerDelegate.debugLogger, ("********pos_dup_select ********" + pos_dup_select));
 870                         //pos_dup_select1 =  pos_dup_select;
 871                         pos_prev_select = pos_first_select;
 872                         pos_last_select = pos_dup_select;
 873                         while (pos_last_select > pos_prev_select) {
 874                             logger.debug(EELFLoggerDelegate.debugLogger, ("pos_last , pos_prev " + pos_last_select + " " + pos_prev_select));
 875                             pos = reportSQL.indexOf("FROM", pos+2);
 876                             pos_prev_select = pos_last_select;
 877                             pos_last_select = reportSQL.lastIndexOf("SELECT",pos);
 878                             logger.debug(EELFLoggerDelegate.debugLogger, ("in WHILE LOOP LAST " + pos_last_select));
 879                         }
 880                      }
 881
 882                   }
 883                  final_sql += " "+reportSQL.substring(pos);
 884                  logger.debug(EELFLoggerDelegate.debugLogger, ("Final SQL " + final_sql));
 885                  String sql =  "SELECT " + legendCol + ", " + legendCol+"_1" + seriesCol.toString()+ nvl(valueCols.toString(), ", 1")
 886                                         + groupCol.toString()
 887                                         + final_sql;
 888                  logger.debug(EELFLoggerDelegate.debugLogger, ("Final sql in generateChartSQL " +sql));
 889
 890                  return sql;
 891                 } // generateChartSQL
 892
 893                 private String getColumnSelectStr(DataColumnType dc, HttpServletRequest request) {
 894                         //String colName = dc.isCalculated() ? dc.getColName()
 895                                 //      : ((nvl(dc.getTableId()).length() > 0) ? (dc.getTableId() + "." + dc
 896                                         //              .getColName()) : dc.getColName());
 897                         String colName = dc.getColName();
 898                         String paramValue = null;
 899                         //if (dc.isCalculated()) {
 900                         if (reportRuntime.getFormFieldList() != null) {
 901                                 for (Iterator iter = reportRuntime.getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 902                                         FormFieldType fft = (FormFieldType) iter.next();
 903                                         String fieldId = fft.getFieldId();
 904                                         String fieldDisplay = reportRuntime.getFormFieldDisplayName(fft);
 905                                         String formfield_value = "";
 906                                         formfield_value = AppUtils.getRequestNvlValue(request, fieldId);
 907                         paramValue = nvl(formfield_value);
 908                                 if(paramValue.length()>0) {
 909                                         /*sql = Utils.replaceInString(sql, "'" + fieldDisplay + "'", nvl(
 910                                             paramValue, "NULL"));*/
 911                                                         colName = Utils.replaceInString(colName, "'" + fieldDisplay + "'", "'"+nvl(
 912                                              paramValue, "NULL")+"'");
 913                                                         colName = Utils.replaceInString(colName,  fieldDisplay, nvl(
 914                                                                                         paramValue, "NULL"));
 915                                 }
 916                                 }
 917                                 return colName;
 918                         }
 919                 //}
 920                         return colName;
 921                 } // getColumnSelectStr
 922
 923
 924
 925                 public String getSelectExpr(DataColumnType dct) {
 926                         // String colName =
 927                         // dct.isCalculated()?dct.getColName():((nvl(dct.getTableId()).length()>0)?(dct.getTableId()+"."+dct.getColName()):dct.getColName());
 928                         return getSelectExpr(dct, dct.getColName() /* colName */);
 929                 } // getSelectExpr
 930
 931                 private String getSelectExpr(DataColumnType dct, String colName) {
 932                         String colType = dct.getColType();
 933                         if (colType.equals(AppConstants.CT_CHAR)
 934                                         || ((nvl(dct.getColFormat()).length() == 0) && (!colType
 935                                                         .equals(AppConstants.CT_DATE))))
 936                                 return colName;
 937                         else
 938                                 return "TO_CHAR(" + colName + ", '"
 939                                                 + nvl(dct.getColFormat(), AppConstants.DEFAULT_DATE_FORMAT) + "')";
 940                 } // getSelectExpr
 941
 942             private String formatChartColumn(String colName) {
 943                 logger.debug(EELFLoggerDelegate.debugLogger, ("Format Chart Column Input colName " + colName));
 944                 colName =  colName.trim();
 945                 colName = Utils.replaceInString(colName, "TO_CHAR", "to_char");
 946                 colName = Utils.replaceInString(colName, "to_number", "TO_NUMBER");
 947                 //reportSQL = reportSQL.replaceAll("[\\s]*\\(", "(");
 948                 colName = colName.replaceAll(",[\\s]*\\(", ",(");
 949                 StringBuffer colNameBuf = new StringBuffer(colName);
 950                 int pos = 0, posFormatStart = 0, posFormatEnd = 0;
 951                 String format = "";
 952
 953                 if(colNameBuf.indexOf("999")==-1 && colNameBuf.indexOf("990")==-1) {
 954                         logger.debug(EELFLoggerDelegate.debugLogger, (" return colName " + colNameBuf.toString()));
 955                     return colNameBuf.toString();
 956                 }
 957
 958                 while (colNameBuf.indexOf("to_char")!=-1) {
 959                     if(colNameBuf.indexOf("999")!=-1 || colNameBuf.indexOf("990")!=-1) {
 960                         pos = colNameBuf.indexOf("to_char");
 961                         colNameBuf.insert(pos, " TO_NUMBER ( CR_RAPTOR.SAFE_TO_NUMBER (");
 962                         pos = colNameBuf.indexOf("to_char");
 963                         colNameBuf.replace(pos, pos+7, "TO_CHAR");
 964                         //colName = Utils.replaceInString(colNameBuf.toString(), "to_char", " TO_NUMBER ( CR_RAPTOR.SAFE_TO_NUMBER ( TO_CHAR ");
 965                         logger.debug(EELFLoggerDelegate.debugLogger, ("After adding to_number " + colNameBuf.toString()));
 966                         //posFormatStart = colNameBuf.lastIndexOf(",'")+1;
 967                         posFormatStart = colNameBuf.indexOf(",'", pos)+1;
 968                         posFormatEnd = colNameBuf.indexOf(")",posFormatStart);
 969                         logger.debug(EELFLoggerDelegate.debugLogger, (posFormatStart + " " + posFormatEnd + " "+ pos));
 970                         format = colNameBuf.substring(posFormatStart, posFormatEnd);
 971                         //posFormatEnd = colNameBuf.indexOf(")",posFormatEnd);
 972                         colNameBuf.insert(posFormatEnd+1, " ," + format + ") , "+ format + ")");
 973                         logger.debug(EELFLoggerDelegate.debugLogger, ("colNameBuf " + colNameBuf.toString()));
 974                     }
 975                 }
 976                 logger.debug(EELFLoggerDelegate.debugLogger, (" return colName " + colNameBuf.toString()));
 977                 return colNameBuf.toString();
 978             }
 979
 980                 public List getChartValueColumnsList( int filter, HashMap formValues) { /*filter; all=0;create without new chart =1; createNewChart=2 */
 981                         List reportCols = reportRuntime.getAllColumns();
 982
 983                         ArrayList chartValueCols = new ArrayList();
 984                         int flag = 0;
 985                         for (Iterator iter = reportCols.iterator(); iter.hasNext();) {
 986                                 flag = 0;
 987                                 DataColumnType dc = (DataColumnType) iter.next();
 988 //                          if(filter == 2 || filter == 1) {
 989                                 flag = reportRuntime.getDependsOnFormFieldFlag(dc, formValues);
 990
 991                                 if( (dc.getChartSeq()!=null &&  dc.getChartSeq()> 0) && flag == 0 && !(nvl(dc.getColOnChart()).equals(AppConstants.GC_LEGEND))) {
 992                                         if(nvl(dc.getChartGroup()).length()<=0) {
 993                                                 if( filter == 2 && (dc.isCreateInNewChart()!=null && dc.isCreateInNewChart().booleanValue())) {
 994                                                         chartValueCols.add(dc);
 995                                                 } else if (filter == 1 && (dc.isCreateInNewChart()==null || !dc.isCreateInNewChart().booleanValue())) {
 996                                                         chartValueCols.add(dc);
 997                                                 }
 998                                                 else if(filter == 0) chartValueCols.add(dc);
 999                                         } else chartValueCols.add(dc);
1000                                 }
1001 //                              } else
1002 //                                      chartValueCols.add(dc);
1003                         } // for
1004                         Collections.sort(chartValueCols, new ChartSeqComparator());
1005                         return chartValueCols;
1006                 } // getChartValueColumnsList
1007
1008                 public String parseTitle(String title, HashMap formValues) {
1009                         Set set = formValues.entrySet();
1010                         for(Iterator iter = set.iterator(); iter.hasNext(); ) {
1011                                 Map.Entry entry = (Entry<String,String>) iter.next();
1012                                 if(title.indexOf("["+ entry.getKey() + "]")!= -1) {
1013                                         title = Utils.replaceInString(title, "["+entry.getKey()+"]", nvl(
1014                             (String) entry.getValue(), ""));
1015                                 }
1016                         }
1017                         return title;
1018                 }
1019
1020        public java.util.Date getDateFromDateStr(String dateStr) {
1021                                 SimpleDateFormat MMDDYYYYFormat                         = new SimpleDateFormat("MM/dd/yyyy");
1022                         SimpleDateFormat EEEMMDDYYYYFormat                      = new SimpleDateFormat("EEE, MM/dd/yyyy"); //2012-11-01 00:00:00
1023                         SimpleDateFormat YYYYMMDDFormat                         = new SimpleDateFormat("yyyy/MM/dd");
1024                         SimpleDateFormat MONYYYYFormat                          = new SimpleDateFormat("MMM yyyy");
1025                         SimpleDateFormat MMYYYYFormat                           = new SimpleDateFormat("MM/yyyy");
1026                         SimpleDateFormat MMMMMDDYYYYFormat                      = new SimpleDateFormat("MMMMM dd, yyyy");
1027                         SimpleDateFormat timestampFormat                        = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
1028                         SimpleDateFormat timestampHrFormat              = new SimpleDateFormat("yyyy-MM-dd HH");
1029                         SimpleDateFormat timestampDayFormat             = new SimpleDateFormat("yyyy-MM-dd");
1030                         SimpleDateFormat DDMONYYYYFormat                = new SimpleDateFormat("dd-MMM-yyyy");
1031                         SimpleDateFormat MONTHYYYYFormat                = new SimpleDateFormat("MMMMM, yyyy");
1032                         SimpleDateFormat MMDDYYYYHHFormat               = new SimpleDateFormat("MM/dd/yyyy HH");
1033                         SimpleDateFormat MMDDYYYYHHMMSSFormat           = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
1034                         SimpleDateFormat MMDDYYYYHHMMFormat             = new SimpleDateFormat("MM/dd/yyyy HH:mm");
1035                         SimpleDateFormat YYYYMMDDHHMMSSFormat           = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
1036                         SimpleDateFormat YYYYMMDDHHMMFormat             = new SimpleDateFormat("yyyy/MM/dd HH:mm");
1037                         SimpleDateFormat DDMONYYYYHHMMSSFormat          = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
1038                         SimpleDateFormat DDMONYYYYHHMMFormat            = new SimpleDateFormat("dd-MMM-yyyy HH:mm");
1039                         SimpleDateFormat MMDDYYFormat                           = new SimpleDateFormat("MM/dd/yy");
1040                         SimpleDateFormat MMDDYYHHMMFormat               = new SimpleDateFormat("MM/dd/yy HH:mm");
1041                         SimpleDateFormat MMDDYYHHMMSSFormat             = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
1042                         SimpleDateFormat timestampFormat1               = new SimpleDateFormat("yyyy-M-d.HH.mm. s. S");
1043                         SimpleDateFormat MMDDYYYYHHMMZFormat            = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
1044                         SimpleDateFormat YYYYFormat                             = new SimpleDateFormat("yyyy");
1045                                 java.util.Date date = null;
1046
1047                         int formatFlag = 0;
1048
1049                         final int YEARFLAG = 1;
1050                         final int MONTHFLAG = 2;
1051                         final int DAYFLAG = 3;
1052                         final int HOURFLAG = 4;
1053                         final int MINFLAG = 5;
1054                         final int SECFLAG = 6;
1055                         final int MILLISECFLAG = 7;
1056                         final int DAYOFTHEWEEKFLAG = 8;
1057                         final int FLAGDATE = 9;
1058                                 /*int yearFlag                  = 1;
1059                         int monthFlag           = 2;
1060                         int dayFlag             = 3;
1061                         int hourFlag            = 4;
1062                         int minFlag             = 5;
1063                         int secFlag             = 6;
1064                         int milliSecFlag        = 7;
1065                         int dayoftheweekFlag  = 8;
1066                                 int flagDate        = 10;
1067                                 */
1068
1069                                 date = MMDDYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1070                     if(date!=null) formatFlag = SECFLAG;
1071                     if(date==null) {
1072                         date = EEEMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1073                         if(date!=null) formatFlag = DAYOFTHEWEEKFLAG;
1074                     }
1075                     if(date==null) {
1076                         date = MMDDYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1077                         if(date!=null) formatFlag = MINFLAG;
1078                     }
1079                     if(date==null) {
1080                         //MMDDYYYYHHFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
1081                         date = MMDDYYYYHHFormat.parse(dateStr, new ParsePosition(0));
1082                         if(date!=null) formatFlag = HOURFLAG;
1083                     }
1084                     if(date==null) {
1085                         date = MMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1086                         if(date!=null) formatFlag = DAYFLAG;
1087                     }
1088                     if(date==null) {
1089                         date = YYYYMMDDFormat.parse(dateStr, new ParsePosition(0));
1090                         if(date!=null) formatFlag = DAYFLAG;
1091                     }
1092                     if(date==null) {
1093                         date = timestampFormat.parse(dateStr, new ParsePosition(0));
1094                         if(date!=null) formatFlag = SECFLAG;
1095                     }
1096                     if(date==null) {
1097                         date = timestampHrFormat.parse(dateStr, new ParsePosition(0));
1098                         if(date!=null) formatFlag = HOURFLAG;
1099                     }
1100                     if(date==null) {
1101                         date = timestampDayFormat.parse(dateStr, new ParsePosition(0));
1102                         if(date!=null) formatFlag = DAYFLAG;
1103                     }
1104
1105                     if(date==null) {
1106                         date = MONYYYYFormat.parse(dateStr, new ParsePosition(0));
1107                         if(date!=null) formatFlag = MONTHFLAG;
1108                     }
1109                     if(date==null) {
1110                         date = MMYYYYFormat.parse(dateStr, new ParsePosition(0));
1111                         if(date!=null) formatFlag = MONTHFLAG;
1112                     }
1113                     if(date==null) {
1114                         date = MMMMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1115                         if(date!=null) formatFlag = DAYFLAG;
1116                     }
1117                     if(date==null) {
1118                         date = MONTHYYYYFormat.parse(dateStr, new ParsePosition(0));
1119                         if(date!=null) formatFlag = MONTHFLAG;
1120                     }
1121
1122                     if(date==null) {
1123                         date = YYYYMMDDHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1124                         if(date!=null) formatFlag = SECFLAG;
1125                     }
1126
1127                     if(date==null) {
1128                         date = YYYYMMDDHHMMFormat.parse(dateStr, new ParsePosition(0));
1129                         if(date!=null) formatFlag = MINFLAG;
1130                     }
1131
1132                     if(date==null) {
1133                         date = DDMONYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1134                         if(date!=null) formatFlag = SECFLAG;
1135                     }
1136
1137                     if(date==null) {
1138                         date = DDMONYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1139                         if(date!=null) formatFlag = MINFLAG;
1140                     }
1141
1142                     if(date==null) {
1143                         date = DDMONYYYYFormat.parse(dateStr, new ParsePosition(0));
1144                         if(date!=null) formatFlag = DAYFLAG;
1145                     }
1146
1147                     if(date==null) {
1148                         date = MMDDYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1149                         if(date!=null) formatFlag = SECFLAG;
1150                     }
1151
1152                     if(date==null) {
1153                         date = MMDDYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1154                         if(date!=null) formatFlag = MINFLAG;
1155                     }
1156
1157                     if(date==null) {
1158                         date = MMDDYYFormat.parse(dateStr, new ParsePosition(0));
1159                         if(date!=null) formatFlag = DAYFLAG;
1160                     }
1161
1162                     if(date==null) {
1163                         date = timestampFormat1.parse(dateStr, new ParsePosition(0));
1164                         if(date!=null) formatFlag = SECFLAG;
1165                     }
1166
1167                     if(date==null) {
1168                         date = MMDDYYYYHHMMZFormat.parse(dateStr, new ParsePosition(0));
1169                         if(date!=null) formatFlag = MINFLAG;
1170                     }
1171
1172                     if(date==null) {
1173                         date = YYYYFormat.parse(dateStr, new ParsePosition(0));
1174                         /* Some random numbers should not satisfy this year format. */
1175                         if(dateStr.length()>4) date = null;
1176                         if(date!=null) formatFlag = YEARFLAG;
1177                     }
1178                     if(date==null)
1179                         date = null;
1180                return date;
1181        }
1182
1183        public int getFlagFromDateStr(String dateStr) {
1184                                 SimpleDateFormat MMDDYYYYFormat                         = new SimpleDateFormat("MM/dd/yyyy");
1185                         SimpleDateFormat EEEMMDDYYYYFormat                      = new SimpleDateFormat("EEE, MM/dd/yyyy"); //2012-11-01 00:00:00
1186                         SimpleDateFormat YYYYMMDDFormat                         = new SimpleDateFormat("yyyy/MM/dd");
1187                         SimpleDateFormat MONYYYYFormat                          = new SimpleDateFormat("MMM yyyy");
1188                         SimpleDateFormat MMYYYYFormat                           = new SimpleDateFormat("MM/yyyy");
1189                         SimpleDateFormat MMMMMDDYYYYFormat                      = new SimpleDateFormat("MMMMM dd, yyyy");
1190                         SimpleDateFormat timestampFormat                        = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
1191                         SimpleDateFormat timestampHrFormat              = new SimpleDateFormat("yyyy-MM-dd HH");
1192                         SimpleDateFormat timestampDayFormat             = new SimpleDateFormat("yyyy-MM-dd");
1193                         SimpleDateFormat DDMONYYYYFormat                = new SimpleDateFormat("dd-MMM-yyyy");
1194                         SimpleDateFormat MONTHYYYYFormat                = new SimpleDateFormat("MMMMM, yyyy");
1195                         SimpleDateFormat MMDDYYYYHHFormat               = new SimpleDateFormat("MM/dd/yyyy HH");
1196                         SimpleDateFormat MMDDYYYYHHMMSSFormat           = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
1197                         SimpleDateFormat MMDDYYYYHHMMFormat             = new SimpleDateFormat("MM/dd/yyyy HH:mm");
1198                         SimpleDateFormat YYYYMMDDHHMMSSFormat           = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
1199                         SimpleDateFormat YYYYMMDDHHMMFormat             = new SimpleDateFormat("yyyy/MM/dd HH:mm");
1200                         SimpleDateFormat DDMONYYYYHHMMSSFormat          = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
1201                         SimpleDateFormat DDMONYYYYHHMMFormat            = new SimpleDateFormat("dd-MMM-yyyy HH:mm");
1202                         SimpleDateFormat MMDDYYFormat                           = new SimpleDateFormat("MM/dd/yy");
1203                         SimpleDateFormat MMDDYYHHMMFormat               = new SimpleDateFormat("MM/dd/yy HH:mm");
1204                         SimpleDateFormat MMDDYYHHMMSSFormat             = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
1205                         SimpleDateFormat timestampFormat1               = new SimpleDateFormat("yyyy-M-d.HH.mm. s. S");
1206                         SimpleDateFormat MMDDYYYYHHMMZFormat            = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
1207                         SimpleDateFormat YYYYFormat                             = new SimpleDateFormat("yyyy");
1208                                 java.util.Date date = null;
1209
1210                         int formatFlag = 0;
1211
1212                         final int YEARFLAG = 1;
1213                         final int MONTHFLAG = 2;
1214                         final int DAYFLAG = 3;
1215                         final int HOURFLAG = 4;
1216                         final int MINFLAG = 5;
1217                         final int SECFLAG = 6;
1218                         final int MILLISECFLAG = 7;
1219                         final int DAYOFTHEWEEKFLAG = 8;
1220                         final int FLAGDATE = 9;
1221                                 /*int yearFlag                  = 1;
1222                         int monthFlag           = 2;
1223                         int dayFlag             = 3;
1224                         int hourFlag            = 4;
1225                         int minFlag             = 5;
1226                         int secFlag             = 6;
1227                         int milliSecFlag        = 7;
1228                         int dayoftheweekFlag  = 8;
1229                                 int flagDate        = 10;
1230                                 */
1231
1232                                 date = MMDDYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1233                     if(date!=null) formatFlag = SECFLAG;
1234                     if(date==null) {
1235                         date = EEEMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1236                         if(date!=null) formatFlag = DAYOFTHEWEEKFLAG;
1237                     }
1238                     if(date==null) {
1239                         date = MMDDYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1240                         if(date!=null) formatFlag = MINFLAG;
1241                     }
1242                     if(date==null) {
1243                         //MMDDYYYYHHFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
1244                         date = MMDDYYYYHHFormat.parse(dateStr, new ParsePosition(0));
1245                         if(date!=null) formatFlag = HOURFLAG;
1246                     }
1247                     if(date==null) {
1248                         date = MMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1249                         if(date!=null) formatFlag = DAYFLAG;
1250                     }
1251                     if(date==null) {
1252                         date = YYYYMMDDFormat.parse(dateStr, new ParsePosition(0));
1253                         if(date!=null) formatFlag = DAYFLAG;
1254                     }
1255                     if(date==null) {
1256                         date = timestampFormat.parse(dateStr, new ParsePosition(0));
1257                         if(date!=null) formatFlag = SECFLAG;
1258                     }
1259                     if(date==null) {
1260                         date = timestampHrFormat.parse(dateStr, new ParsePosition(0));
1261                         if(date!=null) formatFlag = HOURFLAG;
1262                     }
1263                     if(date==null) {
1264                         date = timestampDayFormat.parse(dateStr, new ParsePosition(0));
1265                         if(date!=null) formatFlag = DAYFLAG;
1266                     }
1267                     if(date==null) {
1268                         date = MONYYYYFormat.parse(dateStr, new ParsePosition(0));
1269                         if(date!=null) formatFlag = MONTHFLAG;
1270                     }
1271                     if(date==null) {
1272                         date = MMYYYYFormat.parse(dateStr, new ParsePosition(0));
1273                         if(date!=null) formatFlag = MONTHFLAG;
1274                     }
1275                     if(date==null) {
1276                         date = MMMMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1277                         if(date!=null) formatFlag = DAYFLAG;
1278                     }
1279                     if(date==null) {
1280                         date = MONTHYYYYFormat.parse(dateStr, new ParsePosition(0));
1281                         if(date!=null) formatFlag = MONTHFLAG;
1282                     }
1283
1284                     if(date==null) {
1285                         date = YYYYMMDDHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1286                         if(date!=null) formatFlag = SECFLAG;
1287                     }
1288
1289                     if(date==null) {
1290                         date = YYYYMMDDHHMMFormat.parse(dateStr, new ParsePosition(0));
1291                         if(date!=null) formatFlag = MINFLAG;
1292                     }
1293
1294                     if(date==null) {
1295                         date = DDMONYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1296                         if(date!=null) formatFlag = SECFLAG;
1297                     }
1298
1299                     if(date==null) {
1300                         date = DDMONYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1301                         if(date!=null) formatFlag = MINFLAG;
1302                     }
1303
1304                     if(date==null) {
1305                         date = DDMONYYYYFormat.parse(dateStr, new ParsePosition(0));
1306                         if(date!=null) formatFlag = DAYFLAG;
1307                     }
1308
1309                     if(date==null) {
1310                         date = MMDDYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1311                         if(date!=null) formatFlag = SECFLAG;
1312                     }
1313
1314                     if(date==null) {
1315                         date = MMDDYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1316                         if(date!=null) formatFlag = MINFLAG;
1317                     }
1318
1319                     if(date==null) {
1320                         date = MMDDYYFormat.parse(dateStr, new ParsePosition(0));
1321                         if(date!=null) formatFlag = DAYFLAG;
1322                     }
1323
1324                     if(date==null) {
1325                         date = timestampFormat1.parse(dateStr, new ParsePosition(0));
1326                         if(date!=null) formatFlag = SECFLAG;
1327                     }
1328
1329                     if(date==null) {
1330                         date = MMDDYYYYHHMMZFormat.parse(dateStr, new ParsePosition(0));
1331                         if(date!=null) formatFlag = MINFLAG;
1332                     }
1333
1334                     if(date==null) {
1335                         date = YYYYFormat.parse(dateStr, new ParsePosition(0));
1336                         /* Some random numbers should not satisfy this year format. */
1337                         if(dateStr.length()>4) date = null;
1338                         if(date!=null) formatFlag = YEARFLAG;
1339                     }
1340                     if(date==null)
1341                         date = null;
1342                return formatFlag;
1343        }
1344
1345        public static String[] reverse(String[] arr) {
1346            List<String> list = Arrays.asList(arr);
1347            Collections.reverse(list);
1348            return (String[])list.toArray();
1349        }
1350
1351        public int getNumberOfDecimalPlaces(double num) {
1352            Double d = num;
1353            String[] splitter = d.toString().split("\\.");
1354            splitter[0].length();   // Before Decimal Count
1355            splitter[1].length();   // After  Decimal Count
1356            return splitter[1].length();
1357        }
1358
1359        public boolean getBooleanValue(String s) {
1360          return getBooleanValue(s,null);
1361        }
1362
1363        public boolean getBooleanValue(String s, Boolean defaultValue) {
1364            s = nvl(s);
1365            if(s.length()<=0 && defaultValue!=null) return defaultValue.booleanValue();
1366            else if(s.length()<=0) return false;
1367            else {
1368            if(s.toUpperCase().startsWith("Y") || s.toLowerCase().equals("true"))
1369               return true;
1370            else
1371                    return false;
1372            }
1373        }
1374
1375
1376        public String IntToLetter(int Int) {
1377             if (Int<27){
1378               return Character.toString((char)(Int+96));
1379             } else {
1380               if (Int%26==0) {
1381                 return IntToLetter((Int/26)-1)+IntToLetter((Int%26)+1);
1382               } else {
1383                 return IntToLetter(Int/26)+IntToLetter(Int%26);
1384               }
1385             }
1386           }
1387
1388
1389
1390
1391         private void clearReportRuntimeBackup(HttpServletRequest request) {
1392                 //Session sess = Sessions.getCurrent(true)getCurrent();
1393         //HttpSession session = (HttpSession)sess.getNativeSession();
1394         HttpSession session = request.getSession();
1395                 session.removeAttribute(AppConstants.DRILLDOWN_REPORTS_LIST);
1396                 request.removeAttribute(AppConstants.DRILLDOWN_INDEX);
1397                 session.removeAttribute(AppConstants.DRILLDOWN_INDEX);
1398                 request.removeAttribute(AppConstants.FORM_DRILLDOWN_INDEX);
1399                 session.removeAttribute(AppConstants.FORM_DRILLDOWN_INDEX);
1400                 Enumeration<String> enum1 = session.getAttributeNames();
1401                 String attributeName = "";
1402                 while(enum1.hasMoreElements()) {
1403                         attributeName = enum1.nextElement();
1404                         if(attributeName.startsWith("parent_")) {
1405                                 session.removeAttribute(attributeName);
1406                         }
1407                 }
1408         session.removeAttribute(AppConstants.DRILLDOWN_REPORTS_LIST);
1409                 session.removeAttribute(AppConstants.SI_BACKUP_FOR_REP_ID);
1410                 session.removeAttribute(AppConstants.SI_COLUMN_LOOKUP);
1411         session.removeAttribute(AppConstants.SI_DASHBOARD_REP_ID);
1412         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP);
1413         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME);
1414         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP);
1415         session.removeAttribute(AppConstants.SI_DASHBOARD_CHARTDATA_MAP);
1416                 session.removeAttribute(AppConstants.SI_DASHBOARD_DISPLAYTYPE_MAP);
1417         session.removeAttribute(AppConstants.SI_DATA_SIZE_FOR_TEXTFIELD_POPUP);
1418         session.removeAttribute(AppConstants.SI_MAP);
1419                 session.removeAttribute(AppConstants.SI_MAP_OBJECT);
1420         session.removeAttribute(AppConstants.SI_REPORT_DEFINITION);
1421         session.removeAttribute(AppConstants.SI_REPORT_RUNTIME);
1422                 session.removeAttribute(AppConstants.SI_REPORT_RUN_BACKUP);
1423         session.removeAttribute(AppConstants.SI_REPORT_SCHEDULE);
1424         session.removeAttribute(AppConstants.RI_REPORT_DATA);
1425         session.removeAttribute(AppConstants.RI_CHART_DATA);
1426         session.removeAttribute(AppConstants.SI_FORMFIELD_INFO);
1427         session.removeAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO);
1428
1429         } // clearReportRuntimeBackup
1430
1431
1432     public static synchronized java.util.HashMap getRequestParametersMap(ReportRuntime rr, HttpServletRequest request)
1433     {
1434         HashMap valuesMap = new HashMap();
1435
1436                 ReportFormFields rff = rr.getReportFormFields();
1437
1438                 int idx = 0;
1439                 FormField ff = null;
1440
1441                 Map fieldNameMap = new HashMap();
1442                 int countOfFields = 0 ;
1443
1444
1445                 for(rff.resetNext(); rff.hasNext(); idx++) {
1446                          ff = rff.getNext();
1447                          fieldNameMap.put(ff.getFieldName(), ff.getFieldDisplayName());
1448                          countOfFields++;
1449                 }
1450
1451                 List formParameter = new ArrayList();
1452                 String formField = "";
1453                 for(int i = 0 ; i < rff.size(); i++) {
1454                         ff = ((FormField)rff.getFormField(i));
1455                         formField = ff.getFieldName();
1456                         boolean isMultiValue = false;
1457                         isMultiValue = ff.getFieldType().equals(FormField.FFT_CHECK_BOX)
1458                         || ff.getFieldType().equals(FormField.FFT_LIST_MULTI);
1459                         boolean isTextArea = (ff.getFieldType().equals(FormField.FFT_TEXTAREA) && rr.getReportDefType()
1460                                         .equals(AppConstants.RD_SQL_BASED));
1461
1462                         if(request.getParameterValues(formField) != null && isMultiValue ) {
1463                                         String[] vals = request.getParameterValues(formField);
1464                                         StringBuffer value = new StringBuffer("");
1465                                         if(!AppUtils.getRequestFlag(request, AppConstants.RI_RESET_ACTION)) {
1466
1467                                                 if ( isMultiValue ) {
1468                                                         value.append("(");
1469                                                 }
1470                                                 for(int j = 0 ; j < vals.length; j++) {
1471                                                         if(isMultiValue) value.append("'");
1472                                                         try {
1473                                                                 if(vals[j] !=null && vals[j].length() > 0) {
1474                                                                         vals[j] = Utils.oracleSafe(vals[j]);
1475                                                                         value.append(java.net.URLDecoder.decode(vals[j], "UTF-8"));// + ",";
1476                                                                 }
1477                                                                 else
1478                                                                         value.append(vals[j]);
1479                                                         } catch (UnsupportedEncodingException ex) {value.append(vals[j]);}
1480                                                         catch (IllegalArgumentException ex1){value.append(vals[j]);}
1481                                                         catch (Exception ex2){
1482                                                                 value.append(vals[j]);
1483                                                         }
1484
1485
1486                                                         if(isMultiValue) value.append("'");
1487
1488                                                         if(j != vals.length -1) {
1489                                                                 value.append(",");
1490                                                         }
1491                                                 }
1492                                                 if(vals.length > 0) {
1493                                                         value.append(")");
1494                                                 }
1495                                         }
1496
1497                                         //value = value.substring(0 , value.length());
1498
1499                                  valuesMap.put(fieldNameMap.get(formField), value.toString());
1500                                  value = new StringBuffer("");
1501                     } else if(request.getParameter(formField) != null) {
1502                         if(isTextArea) {
1503                                 String value = "";
1504                                 value = request.getParameter(formField);
1505
1506                                 value = Utils.oracleSafe(value);
1507                                 value = "('" + Utils.replaceInString(value, ",", "'|'") + "')";
1508                                 value = Utils.replaceInString(value, "|", ",");
1509                                 valuesMap.put(fieldNameMap.get(formField), value);
1510                                 value = "";
1511                         } else {
1512                                         String value = "";
1513                                         if(!AppUtils.getRequestFlag(request, AppConstants.RI_RESET_ACTION))
1514                                                 value = request.getParameter(formField);
1515                                         valuesMap.put(fieldNameMap.get(formField), Utils.oracleSafe(value));
1516                         }
1517
1518                 } else {
1519                         valuesMap.put(fieldNameMap.get(formField), "" );
1520                 }
1521
1522         }
1523
1524                 return valuesMap;
1525
1526         }
1527
1528 }