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)
 999                                                         chartValueCols.add(dc);
1000                                         } else chartValueCols.add(dc);
1001                                 }
1002 //                              } else
1003 //                                      chartValueCols.add(dc);
1004                         } // for
1005                         Collections.sort(chartValueCols, new ChartSeqComparator());
1006                         return chartValueCols;
1007                 } // getChartValueColumnsList
1008
1009                 public String parseTitle(String title, HashMap formValues) {
1010                         Set set = formValues.entrySet();
1011                         for(Iterator iter = set.iterator(); iter.hasNext(); ) {
1012                                 Map.Entry entry = (Entry<String,String>) iter.next();
1013                                 if(title.indexOf("["+ entry.getKey() + "]")!= -1) {
1014                                         title = Utils.replaceInString(title, "["+entry.getKey()+"]", nvl(
1015                             (String) entry.getValue(), ""));
1016                                 }
1017                         }
1018                         return title;
1019                 }
1020
1021        public java.util.Date getDateFromDateStr(String dateStr) {
1022                                 SimpleDateFormat MMDDYYYYFormat                         = new SimpleDateFormat("MM/dd/yyyy");
1023                         SimpleDateFormat EEEMMDDYYYYFormat                      = new SimpleDateFormat("EEE, MM/dd/yyyy"); //2012-11-01 00:00:00
1024                         SimpleDateFormat YYYYMMDDFormat                         = new SimpleDateFormat("yyyy/MM/dd");
1025                         SimpleDateFormat MONYYYYFormat                          = new SimpleDateFormat("MMM yyyy");
1026                         SimpleDateFormat MMYYYYFormat                           = new SimpleDateFormat("MM/yyyy");
1027                         SimpleDateFormat MMMMMDDYYYYFormat                      = new SimpleDateFormat("MMMMM dd, yyyy");
1028                         SimpleDateFormat timestampFormat                        = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
1029                         SimpleDateFormat timestampHrFormat              = new SimpleDateFormat("yyyy-MM-dd HH");
1030                         SimpleDateFormat timestampDayFormat             = new SimpleDateFormat("yyyy-MM-dd");
1031                         SimpleDateFormat DDMONYYYYFormat                = new SimpleDateFormat("dd-MMM-yyyy");
1032                         SimpleDateFormat MONTHYYYYFormat                = new SimpleDateFormat("MMMMM, yyyy");
1033                         SimpleDateFormat MMDDYYYYHHFormat               = new SimpleDateFormat("MM/dd/yyyy HH");
1034                         SimpleDateFormat MMDDYYYYHHMMSSFormat           = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
1035                         SimpleDateFormat MMDDYYYYHHMMFormat             = new SimpleDateFormat("MM/dd/yyyy HH:mm");
1036                         SimpleDateFormat YYYYMMDDHHMMSSFormat           = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
1037                         SimpleDateFormat YYYYMMDDHHMMFormat             = new SimpleDateFormat("yyyy/MM/dd HH:mm");
1038                         SimpleDateFormat DDMONYYYYHHMMSSFormat          = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
1039                         SimpleDateFormat DDMONYYYYHHMMFormat            = new SimpleDateFormat("dd-MMM-yyyy HH:mm");
1040                         SimpleDateFormat MMDDYYFormat                           = new SimpleDateFormat("MM/dd/yy");
1041                         SimpleDateFormat MMDDYYHHMMFormat               = new SimpleDateFormat("MM/dd/yy HH:mm");
1042                         SimpleDateFormat MMDDYYHHMMSSFormat             = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
1043                         SimpleDateFormat timestampFormat1               = new SimpleDateFormat("yyyy-M-d.HH.mm. s. S");
1044                         SimpleDateFormat MMDDYYYYHHMMZFormat            = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
1045                         SimpleDateFormat YYYYFormat                             = new SimpleDateFormat("yyyy");
1046                                 java.util.Date date = null;
1047
1048                         int formatFlag = 0;
1049
1050                         final int YEARFLAG = 1;
1051                         final int MONTHFLAG = 2;
1052                         final int DAYFLAG = 3;
1053                         final int HOURFLAG = 4;
1054                         final int MINFLAG = 5;
1055                         final int SECFLAG = 6;
1056                         final int MILLISECFLAG = 7;
1057                         final int DAYOFTHEWEEKFLAG = 8;
1058                         final int FLAGDATE = 9;
1059                                 /*int yearFlag                  = 1;
1060                         int monthFlag           = 2;
1061                         int dayFlag             = 3;
1062                         int hourFlag            = 4;
1063                         int minFlag             = 5;
1064                         int secFlag             = 6;
1065                         int milliSecFlag        = 7;
1066                         int dayoftheweekFlag  = 8;
1067                                 int flagDate        = 10;
1068                                 */
1069
1070                                 date = MMDDYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1071                     if(date!=null)
1072                                         formatFlag = SECFLAG;
1073                     if(date==null) {
1074                         date = EEEMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1075                         if(date!=null)
1076                                                 formatFlag = DAYOFTHEWEEKFLAG;
1077                     }
1078                     if(date==null) {
1079                         date = MMDDYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1080                         if(date!=null)
1081                                                 formatFlag = MINFLAG;
1082                     }
1083                     if(date==null) {
1084                         //MMDDYYYYHHFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
1085                         date = MMDDYYYYHHFormat.parse(dateStr, new ParsePosition(0));
1086                         if(date!=null)
1087                                                 formatFlag = HOURFLAG;
1088                     }
1089                     if(date==null) {
1090                         date = MMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1091                         if(date!=null)
1092                                                 formatFlag = DAYFLAG;
1093                     }
1094                     if(date==null) {
1095                         date = YYYYMMDDFormat.parse(dateStr, new ParsePosition(0));
1096                         if(date!=null)
1097                                                 formatFlag = DAYFLAG;
1098                     }
1099                     if(date==null) {
1100                         date = timestampFormat.parse(dateStr, new ParsePosition(0));
1101                         if(date!=null)
1102                                                 formatFlag = SECFLAG;
1103                     }
1104                     if(date==null) {
1105                         date = timestampHrFormat.parse(dateStr, new ParsePosition(0));
1106                         if(date!=null)
1107                                                 formatFlag = HOURFLAG;
1108                     }
1109                     if(date==null) {
1110                         date = timestampDayFormat.parse(dateStr, new ParsePosition(0));
1111                         if(date!=null)
1112                                                 formatFlag = DAYFLAG;
1113                     }
1114
1115                     if(date==null) {
1116                         date = MONYYYYFormat.parse(dateStr, new ParsePosition(0));
1117                         if(date!=null)
1118                                                 formatFlag = MONTHFLAG;
1119                     }
1120                     if(date==null) {
1121                         date = MMYYYYFormat.parse(dateStr, new ParsePosition(0));
1122                         if(date!=null)
1123                                                 formatFlag = MONTHFLAG;
1124                     }
1125                     if(date==null) {
1126                         date = MMMMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1127                         if(date!=null)
1128                                                 formatFlag = DAYFLAG;
1129                     }
1130                     if(date==null) {
1131                         date = MONTHYYYYFormat.parse(dateStr, new ParsePosition(0));
1132                         if(date!=null)
1133                                                 formatFlag = MONTHFLAG;
1134                     }
1135
1136                     if(date==null) {
1137                         date = YYYYMMDDHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1138                         if(date!=null)
1139                                                 formatFlag = SECFLAG;
1140                     }
1141
1142                     if(date==null) {
1143                         date = YYYYMMDDHHMMFormat.parse(dateStr, new ParsePosition(0));
1144                         if(date!=null)
1145                                                 formatFlag = MINFLAG;
1146                     }
1147
1148                     if(date==null) {
1149                         date = DDMONYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1150                         if(date!=null)
1151                                                 formatFlag = SECFLAG;
1152                     }
1153
1154                     if(date==null) {
1155                         date = DDMONYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1156                         if(date!=null)
1157                                                 formatFlag = MINFLAG;
1158                     }
1159
1160                     if(date==null) {
1161                         date = DDMONYYYYFormat.parse(dateStr, new ParsePosition(0));
1162                         if(date!=null)
1163                                                 formatFlag = DAYFLAG;
1164                     }
1165
1166                     if(date==null) {
1167                         date = MMDDYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1168                         if(date!=null)
1169                                                 formatFlag = SECFLAG;
1170                     }
1171
1172                     if(date==null) {
1173                         date = MMDDYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1174                         if(date!=null)
1175                                                 formatFlag = MINFLAG;
1176                     }
1177
1178                     if(date==null) {
1179                         date = MMDDYYFormat.parse(dateStr, new ParsePosition(0));
1180                         if(date!=null)
1181                                                 formatFlag = DAYFLAG;
1182                     }
1183
1184                     if(date==null) {
1185                         date = timestampFormat1.parse(dateStr, new ParsePosition(0));
1186                         if(date!=null)
1187                                                 formatFlag = SECFLAG;
1188                     }
1189
1190                     if(date==null) {
1191                         date = MMDDYYYYHHMMZFormat.parse(dateStr, new ParsePosition(0));
1192                         if(date!=null)
1193                                                 formatFlag = MINFLAG;
1194                     }
1195
1196                     if(date==null) {
1197                         date = YYYYFormat.parse(dateStr, new ParsePosition(0));
1198                         /* Some random numbers should not satisfy this year format. */
1199                         if(dateStr.length()>4)
1200                                                 date = null;
1201                         if(date!=null)
1202                                                 formatFlag = YEARFLAG;
1203                     }
1204                     if(date==null)
1205                         date = null;
1206                return date;
1207        }
1208
1209        public int getFlagFromDateStr(String dateStr) {
1210                                 SimpleDateFormat MMDDYYYYFormat                         = new SimpleDateFormat("MM/dd/yyyy");
1211                         SimpleDateFormat EEEMMDDYYYYFormat                      = new SimpleDateFormat("EEE, MM/dd/yyyy"); //2012-11-01 00:00:00
1212                         SimpleDateFormat YYYYMMDDFormat                         = new SimpleDateFormat("yyyy/MM/dd");
1213                         SimpleDateFormat MONYYYYFormat                          = new SimpleDateFormat("MMM yyyy");
1214                         SimpleDateFormat MMYYYYFormat                           = new SimpleDateFormat("MM/yyyy");
1215                         SimpleDateFormat MMMMMDDYYYYFormat                      = new SimpleDateFormat("MMMMM dd, yyyy");
1216                         SimpleDateFormat timestampFormat                        = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
1217                         SimpleDateFormat timestampHrFormat              = new SimpleDateFormat("yyyy-MM-dd HH");
1218                         SimpleDateFormat timestampDayFormat             = new SimpleDateFormat("yyyy-MM-dd");
1219                         SimpleDateFormat DDMONYYYYFormat                = new SimpleDateFormat("dd-MMM-yyyy");
1220                         SimpleDateFormat MONTHYYYYFormat                = new SimpleDateFormat("MMMMM, yyyy");
1221                         SimpleDateFormat MMDDYYYYHHFormat               = new SimpleDateFormat("MM/dd/yyyy HH");
1222                         SimpleDateFormat MMDDYYYYHHMMSSFormat           = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
1223                         SimpleDateFormat MMDDYYYYHHMMFormat             = new SimpleDateFormat("MM/dd/yyyy HH:mm");
1224                         SimpleDateFormat YYYYMMDDHHMMSSFormat           = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
1225                         SimpleDateFormat YYYYMMDDHHMMFormat             = new SimpleDateFormat("yyyy/MM/dd HH:mm");
1226                         SimpleDateFormat DDMONYYYYHHMMSSFormat          = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
1227                         SimpleDateFormat DDMONYYYYHHMMFormat            = new SimpleDateFormat("dd-MMM-yyyy HH:mm");
1228                         SimpleDateFormat MMDDYYFormat                           = new SimpleDateFormat("MM/dd/yy");
1229                         SimpleDateFormat MMDDYYHHMMFormat               = new SimpleDateFormat("MM/dd/yy HH:mm");
1230                         SimpleDateFormat MMDDYYHHMMSSFormat             = new SimpleDateFormat("MM/dd/yy HH:mm:ss");
1231                         SimpleDateFormat timestampFormat1               = new SimpleDateFormat("yyyy-M-d.HH.mm. s. S");
1232                         SimpleDateFormat MMDDYYYYHHMMZFormat            = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
1233                         SimpleDateFormat YYYYFormat                             = new SimpleDateFormat("yyyy");
1234                                 java.util.Date date = null;
1235
1236                         int formatFlag = 0;
1237
1238                         final int YEARFLAG = 1;
1239                         final int MONTHFLAG = 2;
1240                         final int DAYFLAG = 3;
1241                         final int HOURFLAG = 4;
1242                         final int MINFLAG = 5;
1243                         final int SECFLAG = 6;
1244                         final int MILLISECFLAG = 7;
1245                         final int DAYOFTHEWEEKFLAG = 8;
1246                         final int FLAGDATE = 9;
1247                                 /*int yearFlag                  = 1;
1248                         int monthFlag           = 2;
1249                         int dayFlag             = 3;
1250                         int hourFlag            = 4;
1251                         int minFlag             = 5;
1252                         int secFlag             = 6;
1253                         int milliSecFlag        = 7;
1254                         int dayoftheweekFlag  = 8;
1255                                 int flagDate        = 10;
1256                                 */
1257
1258                                 date = MMDDYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1259                     if(date!=null)
1260                                         formatFlag = SECFLAG;
1261                     if(date==null) {
1262                         date = EEEMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1263                         if(date!=null)
1264                                                 formatFlag = DAYOFTHEWEEKFLAG;
1265                     }
1266                     if(date==null) {
1267                         date = MMDDYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1268                         if(date!=null)
1269                                                 formatFlag = MINFLAG;
1270                     }
1271                     if(date==null) {
1272                         //MMDDYYYYHHFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
1273                         date = MMDDYYYYHHFormat.parse(dateStr, new ParsePosition(0));
1274                         if(date!=null)
1275                                                 formatFlag = HOURFLAG;
1276                     }
1277                     if(date==null) {
1278                         date = MMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1279                         if(date!=null)
1280                                                 formatFlag = DAYFLAG;
1281                     }
1282                     if(date==null) {
1283                         date = YYYYMMDDFormat.parse(dateStr, new ParsePosition(0));
1284                         if(date!=null)
1285                                                 formatFlag = DAYFLAG;
1286                     }
1287                     if(date==null) {
1288                         date = timestampFormat.parse(dateStr, new ParsePosition(0));
1289                         if(date!=null)
1290                                                 formatFlag = SECFLAG;
1291                     }
1292                     if(date==null) {
1293                         date = timestampHrFormat.parse(dateStr, new ParsePosition(0));
1294                         if(date!=null)
1295                                                 formatFlag = HOURFLAG;
1296                     }
1297                     if(date==null) {
1298                         date = timestampDayFormat.parse(dateStr, new ParsePosition(0));
1299                         if(date!=null)
1300                                                 formatFlag = DAYFLAG;
1301                     }
1302                     if(date==null) {
1303                         date = MONYYYYFormat.parse(dateStr, new ParsePosition(0));
1304                         if(date!=null)
1305                                                 formatFlag = MONTHFLAG;
1306                     }
1307                     if(date==null) {
1308                         date = MMYYYYFormat.parse(dateStr, new ParsePosition(0));
1309                         if(date!=null)
1310                                                 formatFlag = MONTHFLAG;
1311                     }
1312                     if(date==null) {
1313                         date = MMMMMDDYYYYFormat.parse(dateStr, new ParsePosition(0));
1314                         if(date!=null)
1315                                                 formatFlag = DAYFLAG;
1316                     }
1317                     if(date==null) {
1318                         date = MONTHYYYYFormat.parse(dateStr, new ParsePosition(0));
1319                         if(date!=null)
1320                                                 formatFlag = MONTHFLAG;
1321                     }
1322
1323                     if(date==null) {
1324                         date = YYYYMMDDHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1325                         if(date!=null)
1326                                                 formatFlag = SECFLAG;
1327                     }
1328
1329                     if(date==null) {
1330                         date = YYYYMMDDHHMMFormat.parse(dateStr, new ParsePosition(0));
1331                         if(date!=null)
1332                                                 formatFlag = MINFLAG;
1333                     }
1334
1335                     if(date==null) {
1336                         date = DDMONYYYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1337                         if(date!=null)
1338                                                 formatFlag = SECFLAG;
1339                     }
1340
1341                     if(date==null) {
1342                         date = DDMONYYYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1343                         if(date!=null)
1344                                                 formatFlag = MINFLAG;
1345                     }
1346
1347                     if(date==null) {
1348                         date = DDMONYYYYFormat.parse(dateStr, new ParsePosition(0));
1349                         if(date!=null)
1350                                                 formatFlag = DAYFLAG;
1351                     }
1352
1353                     if(date==null) {
1354                         date = MMDDYYHHMMSSFormat.parse(dateStr, new ParsePosition(0));
1355                         if(date!=null)
1356                                                 formatFlag = SECFLAG;
1357                     }
1358
1359                     if(date==null) {
1360                         date = MMDDYYHHMMFormat.parse(dateStr, new ParsePosition(0));
1361                         if(date!=null)
1362                                                 formatFlag = MINFLAG;
1363                     }
1364
1365                     if(date==null) {
1366                         date = MMDDYYFormat.parse(dateStr, new ParsePosition(0));
1367                         if(date!=null)
1368                                                 formatFlag = DAYFLAG;
1369                     }
1370
1371                     if(date==null) {
1372                         date = timestampFormat1.parse(dateStr, new ParsePosition(0));
1373                         if(date!=null)
1374                                                 formatFlag = SECFLAG;
1375                     }
1376
1377                     if(date==null) {
1378                         date = MMDDYYYYHHMMZFormat.parse(dateStr, new ParsePosition(0));
1379                         if(date!=null)
1380                                                 formatFlag = MINFLAG;
1381                     }
1382
1383                     if(date==null) {
1384                         date = YYYYFormat.parse(dateStr, new ParsePosition(0));
1385                         /* Some random numbers should not satisfy this year format. */
1386                         if(dateStr.length()>4)
1387                                                 date = null;
1388                         if(date!=null)
1389                                                 formatFlag = YEARFLAG;
1390                     }
1391                     if(date==null)
1392                         date = null;
1393                return formatFlag;
1394        }
1395
1396        public static String[] reverse(String[] arr) {
1397            List<String> list = Arrays.asList(arr);
1398            Collections.reverse(list);
1399            return (String[])list.toArray();
1400        }
1401
1402        public int getNumberOfDecimalPlaces(double num) {
1403            Double d = num;
1404            String[] splitter = d.toString().split("\\.");
1405            splitter[0].length();   // Before Decimal Count
1406            splitter[1].length();   // After  Decimal Count
1407            return splitter[1].length();
1408        }
1409
1410        public boolean getBooleanValue(String s) {
1411          return getBooleanValue(s,null);
1412        }
1413
1414        public boolean getBooleanValue(String s, Boolean defaultValue) {
1415            s = nvl(s);
1416            if(s.length()<=0 && defaultValue!=null)
1417                            return defaultValue.booleanValue();
1418            else if(s.length()<=0)
1419                            return false;
1420            else {
1421            if(s.toUpperCase().startsWith("Y") || s.toLowerCase().equals("true"))
1422               return true;
1423            else
1424                    return false;
1425            }
1426        }
1427
1428
1429        public String IntToLetter(int Int) {
1430             if (Int<27){
1431               return Character.toString((char)(Int+96));
1432             } else {
1433               if (Int%26==0) {
1434                 return IntToLetter((Int/26)-1)+IntToLetter((Int%26)+1);
1435               } else {
1436                 return IntToLetter(Int/26)+IntToLetter(Int%26);
1437               }
1438             }
1439           }
1440
1441
1442
1443
1444         private void clearReportRuntimeBackup(HttpServletRequest request) {
1445                 //Session sess = Sessions.getCurrent(true)getCurrent();
1446         //HttpSession session = (HttpSession)sess.getNativeSession();
1447         HttpSession session = request.getSession();
1448                 session.removeAttribute(AppConstants.DRILLDOWN_REPORTS_LIST);
1449                 request.removeAttribute(AppConstants.DRILLDOWN_INDEX);
1450                 session.removeAttribute(AppConstants.DRILLDOWN_INDEX);
1451                 request.removeAttribute(AppConstants.FORM_DRILLDOWN_INDEX);
1452                 session.removeAttribute(AppConstants.FORM_DRILLDOWN_INDEX);
1453                 Enumeration<String> enum1 = session.getAttributeNames();
1454                 String attributeName = "";
1455                 while(enum1.hasMoreElements()) {
1456                         attributeName = enum1.nextElement();
1457                         if(attributeName.startsWith("parent_")) {
1458                                 session.removeAttribute(attributeName);
1459                         }
1460                 }
1461         session.removeAttribute(AppConstants.DRILLDOWN_REPORTS_LIST);
1462                 session.removeAttribute(AppConstants.SI_BACKUP_FOR_REP_ID);
1463                 session.removeAttribute(AppConstants.SI_COLUMN_LOOKUP);
1464         session.removeAttribute(AppConstants.SI_DASHBOARD_REP_ID);
1465         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP);
1466         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME);
1467         session.removeAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP);
1468         session.removeAttribute(AppConstants.SI_DASHBOARD_CHARTDATA_MAP);
1469                 session.removeAttribute(AppConstants.SI_DASHBOARD_DISPLAYTYPE_MAP);
1470         session.removeAttribute(AppConstants.SI_DATA_SIZE_FOR_TEXTFIELD_POPUP);
1471         session.removeAttribute(AppConstants.SI_MAP);
1472                 session.removeAttribute(AppConstants.SI_MAP_OBJECT);
1473         session.removeAttribute(AppConstants.SI_REPORT_DEFINITION);
1474         session.removeAttribute(AppConstants.SI_REPORT_RUNTIME);
1475                 session.removeAttribute(AppConstants.SI_REPORT_RUN_BACKUP);
1476         session.removeAttribute(AppConstants.SI_REPORT_SCHEDULE);
1477         session.removeAttribute(AppConstants.RI_REPORT_DATA);
1478         session.removeAttribute(AppConstants.RI_CHART_DATA);
1479         session.removeAttribute(AppConstants.SI_FORMFIELD_INFO);
1480         session.removeAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO);
1481
1482         } // clearReportRuntimeBackup
1483
1484
1485     public static synchronized java.util.HashMap getRequestParametersMap(ReportRuntime rr, HttpServletRequest request)
1486     {
1487         HashMap valuesMap = new HashMap();
1488
1489                 ReportFormFields rff = rr.getReportFormFields();
1490
1491                 int idx = 0;
1492                 FormField ff = null;
1493
1494                 Map fieldNameMap = new HashMap();
1495                 int countOfFields = 0 ;
1496
1497
1498                 for(rff.resetNext(); rff.hasNext(); idx++) {
1499                          ff = rff.getNext();
1500                          fieldNameMap.put(ff.getFieldName(), ff.getFieldDisplayName());
1501                          countOfFields++;
1502                 }
1503
1504                 List formParameter = new ArrayList();
1505                 String formField = "";
1506                 for(int i = 0 ; i < rff.size(); i++) {
1507                         ff = ((FormField)rff.getFormField(i));
1508                         formField = ff.getFieldName();
1509                         boolean isMultiValue = false;
1510                         isMultiValue = ff.getFieldType().equals(FormField.FFT_CHECK_BOX)
1511                         || ff.getFieldType().equals(FormField.FFT_LIST_MULTI);
1512                         boolean isTextArea = (ff.getFieldType().equals(FormField.FFT_TEXTAREA) && rr.getReportDefType()
1513                                         .equals(AppConstants.RD_SQL_BASED));
1514
1515                         if(request.getParameterValues(formField) != null && isMultiValue ) {
1516                                         String[] vals = request.getParameterValues(formField);
1517                                         StringBuffer value = new StringBuffer("");
1518                                         if(!AppUtils.getRequestFlag(request, AppConstants.RI_RESET_ACTION)) {
1519
1520                                                 if ( isMultiValue ) {
1521                                                         value.append("(");
1522                                                 }
1523                                                 for(int j = 0 ; j < vals.length; j++) {
1524                                                         if(isMultiValue)
1525                                                                 value.append("'");
1526                                                         try {
1527                                                                 if(vals[j] !=null && vals[j].length() > 0) {
1528                                                                         vals[j] = Utils.oracleSafe(vals[j]);
1529                                                                         value.append(java.net.URLDecoder.decode(vals[j], "UTF-8"));// + ",";
1530                                                                 }
1531                                                                 else
1532                                                                         value.append(vals[j]);
1533                                                         } catch (UnsupportedEncodingException ex) {value.append(vals[j]);}
1534                                                         catch (IllegalArgumentException ex1){value.append(vals[j]);}
1535                                                         catch (Exception ex2){
1536                                                                 value.append(vals[j]);
1537                                                         }
1538
1539
1540                                                         if(isMultiValue)
1541                                 value.append("'");
1542
1543                                                         if(j != vals.length -1) {
1544                                                                 value.append(",");
1545                                                         }
1546                                                 }
1547                                                 if(vals.length > 0) {
1548                                                         value.append(")");
1549                                                 }
1550                                         }
1551
1552                                         //value = value.substring(0 , value.length());
1553
1554                                  valuesMap.put(fieldNameMap.get(formField), value.toString());
1555                                  value = new StringBuffer("");
1556                     } else if(request.getParameter(formField) != null) {
1557                         if(isTextArea) {
1558                                 String value = "";
1559                                 value = request.getParameter(formField);
1560
1561                                 value = Utils.oracleSafe(value);
1562                                 value = "('" + Utils.replaceInString(value, ",", "'|'") + "')";
1563                                 value = Utils.replaceInString(value, "|", ",");
1564                                 valuesMap.put(fieldNameMap.get(formField), value);
1565                                 value = "";
1566                         } else {
1567                                         String value = "";
1568                                         if(!AppUtils.getRequestFlag(request, AppConstants.RI_RESET_ACTION))
1569                                                 value = request.getParameter(formField);
1570                                         valuesMap.put(fieldNameMap.get(formField), Utils.oracleSafe(value));
1571                         }
1572
1573                 } else {
1574                         valuesMap.put(fieldNameMap.get(formField), "" );
1575                 }
1576
1577         }
1578
1579                 return valuesMap;
1580
1581         }
1582
1583 }