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

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