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

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