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.definition;
  39
  40 import java.io.IOException;
  41 import java.io.InputStreamReader;
  42 import java.io.Reader;
  43 import java.io.Serializable;
  44 import java.io.Writer;
  45 import java.sql.Connection;
  46 import java.sql.PreparedStatement;
  47 import java.sql.ResultSet;
  48 import java.sql.SQLException;
  49 import java.util.ArrayList;
  50 import java.util.Collections;
  51 import java.util.Iterator;
  52 import java.util.List;
  53 import java.util.Vector;
  54 import java.util.regex.Matcher;
  55 import java.util.regex.Pattern;
  56
  57 import javax.servlet.http.HttpServletRequest;
  58
  59 import org.onap.portalsdk.analytics.RaptorObject;
  60 import org.onap.portalsdk.analytics.error.RaptorException;
  61 import org.onap.portalsdk.analytics.error.RaptorRuntimeException;
  62 import org.onap.portalsdk.analytics.error.ReportSQLException;
  63 import org.onap.portalsdk.analytics.error.UserDefinedException;
  64 import org.onap.portalsdk.analytics.model.base.IdNameValue;
  65 import org.onap.portalsdk.analytics.model.base.NameComparator;
  66 import org.onap.portalsdk.analytics.model.runtime.FormField;
  67 import org.onap.portalsdk.analytics.model.runtime.ReportParamValues;
  68 import org.onap.portalsdk.analytics.model.runtime.ReportRuntime;
  69 import org.onap.portalsdk.analytics.system.AppUtils;
  70 import org.onap.portalsdk.analytics.system.ConnectionUtils;
  71 import org.onap.portalsdk.analytics.system.DbUtils;
  72 import org.onap.portalsdk.analytics.system.Globals;
  73 import org.onap.portalsdk.analytics.util.AppConstants;
  74 import org.onap.portalsdk.analytics.util.DataSet;
  75 import org.onap.portalsdk.analytics.util.RemDbInfo;
  76 import org.onap.portalsdk.analytics.util.Utils;
  77 import org.onap.portalsdk.analytics.xmlobj.FormFieldType;
  78 import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;
  79 import org.onap.portalsdk.core.util.SecurityCodecUtil;
  80 import org.owasp.esapi.ESAPI;
  81 import org.springframework.beans.factory.annotation.Autowired;
  82 import org.springframework.stereotype.Component;
  83
  84 @Component
  85 public class ReportSchedule extends RaptorObject implements Serializable{
  86
  87         /**
  88          *
  89          */
  90         private static final long serialVersionUID = 6936844475227328201L;
  91
  92         private static transient final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportSchedule.class);
  93
  94         private static RemDbInfo remDbInfo;
  95
  96         @SuppressWarnings("static-access")
  97         @Autowired
  98     public void setRemDbInfo(RemDbInfo remDbInfo) {
  99                 this.remDbInfo = remDbInfo;
 100         }
 101
 102         private String reportID = null;
 103
 104         private String scheduleUserID = null;
 105
 106         private String scheduleID = "";
 107
 108         private boolean infoUpdated = false;
 109
 110         private String schedEnabled = "Y";
 111
 112         private String startDate = "";
 113
 114         private String endDate = "";
 115
 116         private String runDate = "";
 117
 118         private String runHour = "12";
 119
 120         private String runMin = "00";
 121
 122         private String runAMPM = "AM";
 123
 124         private String endHour = "11";
 125
 126         private String endMin = "45";
 127
 128         private String endAMPM = "PM";
 129
 130         private String recurrence = "";
 131
 132         private String conditional = "N";
 133
 134         private String conditionSQL = "";
 135
 136         private String notify_type = "1"; //1 -- link, 2 -- pdf,  4 -- excel, 3 -- csv
 137
 138         private String encryptMode = "N"; //1 -- link, 2 -- pdf,  4 -- excel, 3 -- csv
 139
 140         private String attachment = "Y"; //1 -- link, 2 -- pdf,  4 -- excel, 3 -- csv
 141
 142         private String downloadLimit = "0";
 143
 144         private String formFields = "";
 145
 146         private Vector emailToUsers = new Vector();
 147
 148         private Vector emailToRoles = new Vector();
 149
 150         public ReportSchedule() {
 151                 super();
 152         }
 153
 154         public ReportSchedule(String reportID, String scheduleUserID, boolean loadData, HttpServletRequest request) {
 155                 super();
 156
 157                 setReportID(reportID);
 158                 setScheduleUserID(scheduleUserID);
 159                 if(loadData)
 160                         loadScheduleData(request);
 161                 else
 162                         newScheduleData();
 163         } // ReportSchedule
 164
 165         public ReportSchedule(String reportID, String scheduleID, String scheduleUserID, HttpServletRequest request) {
 166                 super();
 167
 168                 setReportID(reportID);
 169                 setScheduleID(scheduleID);
 170                 setScheduleUserID(scheduleUserID);
 171                 loadScheduleData(request);
 172         } // ReportSchedule
 173
 174         void setReportID(String reportID) {
 175                 this.reportID = reportID;
 176         }
 177
 178         public String getSchedEnabled() {
 179                 return schedEnabled;
 180         }
 181
 182         public String getStartDate() {
 183                 return startDate;
 184         }
 185
 186         public String getEndDate() {
 187                 return endDate;
 188         }
 189
 190         public String getRunDate() {
 191                 return runDate;
 192         }
 193
 194         public String getRunHour() {
 195                 return runHour;
 196         }
 197
 198         public String getRunMin() {
 199                 return runMin;
 200         }
 201
 202         public String getRunAMPM() {
 203                 return runAMPM;
 204         }
 205
 206         public String getRecurrence() {
 207                 return recurrence;
 208         }
 209
 210         public String getConditional() {
 211                 return conditional;
 212         }
 213
 214         public String getConditionSQL() {
 215                 return conditionSQL;
 216         }
 217
 218         public List getEmailToUsers() {
 219                 return emailToUsers;
 220         }
 221
 222         public List getEmailToRoles() {
 223                 return emailToRoles;
 224         }
 225
 226         /**
 227          * @return the downloadLimit
 228          */
 229         public String getDownloadLimit() {
 230                 return downloadLimit;
 231         }
 232
 233         /**
 234          * @param downloadLimit the downloadLimit to set
 235          */
 236         public void setDownloadLimit(String downloadLimit) {
 237                 if(nvl(downloadLimit).equals(this.downloadLimit))
 238                         return;
 239                 this.downloadLimit = nvl(downloadLimit,"0");
 240                 infoUpdated = true;
 241         }
 242
 243         /**
 244          * @return the formFields
 245          */
 246         public String getFormFields() {
 247                 return formFields;
 248         }
 249
 250         /**
 251          * @param formFields the formFields to set
 252          */
 253         public void setFormFields(String formFields) {
 254                 if(nvl(formFields).equals(this.formFields))
 255                         return;
 256                 this.formFields = nvl(formFields);
 257                 infoUpdated = true;
 258         }
 259
 260         public String getNotify_type() {
 261                 return notify_type;
 262         }
 263
 264         /**
 265          * @param notify_type the notify_type to set
 266          * 1 -- link (default), 2 -- pdf,  4 -- excel
 267          */
 268         public void setNotify_type(String notify_type) {
 269                 if(nvl(notify_type).equals(this.notify_type))
 270                         return;
 271                 this.notify_type = nvl(notify_type,"1");
 272                 infoUpdated = true;
 273         }
 274
 275         public void setSchedEnabled(String schedEnabled) {
 276                 if (nvl(schedEnabled).equals(this.schedEnabled))
 277                         return;
 278                 infoUpdated = true;
 279                 this.schedEnabled = nvl(schedEnabled, "N");
 280         }
 281
 282         public void setStartDate(String startDate) {
 283                 if (nvl(startDate).equals(this.startDate))
 284                         return;
 285                 infoUpdated = true;
 286                 this.startDate = nvl(startDate);
 287         }
 288
 289         public void setEndDate(String endDate) {
 290                 if (nvl(endDate).equals(this.endDate))
 291                         return;
 292                 infoUpdated = true;
 293                 this.endDate = nvl(endDate);
 294         }
 295
 296         public void setRunDate(String runDate) {
 297                 if (nvl(runDate).equals(this.runDate))
 298                         return;
 299                 infoUpdated = true;
 300                 this.runDate = nvl(runDate);
 301         }
 302
 303         public void setRunHour(String runHour) {
 304                 if (nvl(runHour).equals(this.runHour))
 305                         return;
 306                 infoUpdated = true;
 307                 this.runHour = nvl(runHour, "12");
 308         }
 309
 310         public void setRunMin(String runMin) {
 311                 if (nvl(runMin).equals(this.runMin))
 312                         return;
 313                 infoUpdated = true;
 314                 this.runMin = nvl(runMin, "00");
 315         }
 316
 317         public void setRunAMPM(String runAMPM) {
 318                 if (nvl(runAMPM).equals(this.runAMPM))
 319                         return;
 320                 infoUpdated = true;
 321                 this.runAMPM = nvl(runAMPM, "AM");
 322         }
 323
 324         public void setRecurrence(String recurrence) {
 325                 if (nvl(recurrence).equals(this.recurrence))
 326                         return;
 327                 infoUpdated = true;
 328                 this.recurrence = nvl(recurrence);
 329         }
 330
 331         public void setConditional(String conditional) {
 332                 if (nvl(conditional).equals(this.conditional))
 333                         return;
 334                 infoUpdated = true;
 335                 this.conditional = nvl(conditional, "N");
 336         }
 337
 338         public void setConditionSQL(String conditionSQL) {
 339                 if (nvl(conditionSQL).equals(this.conditionSQL))
 340                         return;
 341                 infoUpdated = true;
 342                 this.conditionSQL = nvl(conditionSQL);
 343         }
 344
 345         public void addEmailToUser(String userId, String userName) {
 346                 if (nvl(userId).length() == 0)
 347                         return;
 348
 349                 for (int i = 0; i < emailToUsers.size(); i++) {
 350                         IdNameValue selUser = (IdNameValue) emailToUsers.get(i);
 351                         if (userId.equals(selUser.getId()))
 352                                 return;
 353                 } // for
 354
 355                 emailToUsers.add(new IdNameValue(userId, userName));
 356                 Collections.sort(emailToUsers, new NameComparator());
 357                 infoUpdated = true;
 358         } // addEmailToUser
 359
 360
 361         public void addEmailArrayToUser(ArrayList<IdNameValue> allSelectedUsers) {
 362                 if (allSelectedUsers==null || allSelectedUsers.size()<=0)
 363                         return;
 364                 emailToUsers.removeAllElements();
 365                 for (int i = 0; i < allSelectedUsers.size(); i++) {
 366                         emailToUsers.add(allSelectedUsers.get(i));
 367                 }
 368                 Collections.sort(emailToUsers, new NameComparator());
 369                 infoUpdated = true;
 370         } // addEmailArrayToUser
 371
 372         public void removeEmailToUser(String userId) {
 373                 if (nvl(userId).length() == 0)
 374                         return;
 375
 376                 for (int i = 0; i < emailToUsers.size(); i++) {
 377                         IdNameValue selUser = (IdNameValue) emailToUsers.get(i);
 378                         if (userId.equals(selUser.getId())) {
 379                                 infoUpdated = true;
 380                                 emailToUsers.remove(i);
 381                                 return;
 382                         } // if
 383                 } // for
 384         } // removeEmailToUser
 385
 386         public void addEmailToRole(String roleId, String roleName) {
 387                 if (nvl(roleId).length() == 0)
 388                         return;
 389
 390                 for (int i = 0; i < emailToRoles.size(); i++) {
 391                         IdNameValue selRole = (IdNameValue) emailToRoles.get(i);
 392                         if (roleId.equals(selRole.getId()))
 393                                 return;
 394                 } // for
 395
 396                 emailToRoles.add(new IdNameValue(roleId, roleName));
 397                 Collections.sort(emailToRoles, new NameComparator());
 398                 infoUpdated = true;
 399         } // addEmailToRole
 400
 401         public void addEmailArrayToRole(ArrayList<IdNameValue> allSelectedRoles) {
 402                 if (allSelectedRoles==null || allSelectedRoles.size()<=0)
 403                         return;
 404                 emailToRoles.removeAllElements();
 405                 for (int i = 0; i < allSelectedRoles.size(); i++) {
 406                         emailToRoles.add(allSelectedRoles.get(i));
 407                 }
 408                 Collections.sort(emailToRoles, new NameComparator());
 409                 infoUpdated = true;
 410         } // addEmailArrayToRole
 411
 412         public void removeEmailToRole(String roleId) {
 413                 if (nvl(roleId).length() == 0)
 414                         return;
 415
 416                 for (int i = 0; i < emailToRoles.size(); i++) {
 417                         IdNameValue selRole = (IdNameValue) emailToRoles.get(i);
 418                         if (roleId.equals(selRole.getId())) {
 419                                 infoUpdated = true;
 420                                 emailToRoles.remove(i);
 421                                 return;
 422                         } // if
 423                 } // for
 424         } // addEmailToRole
 425
 426         private void loadScheduleData(HttpServletRequest request) {
 427                 try {
 428             StringBuffer query = new StringBuffer("");
 429             String q_sql = Globals.getLoadScheduleData();
 430             q_sql = q_sql.replace("[reportID]", reportID);
 431             query.append(q_sql);
 432
 433             if(!AppUtils.isAdminUser(request))
 434                 query.append(" and rs.sched_user_id = " + getScheduleUserID());
 435             if(nvl(getScheduleID()).length()>0) {
 436                         query.append(" and rs.schedule_id = " + getScheduleID());
 437             }
 438             query.append(" order by rs.run_date desc ");
 439
 440                         DataSet ds = DbUtils
 441                                         .executeQuery(query.toString());
 442
 443                         if (ds.getRowCount() > 0) {
 444                                 schedEnabled = nvl(ds.getString(0, 0), "N");
 445                                 startDate = nvl(ds.getString(0, 1));
 446                                 endDate = nvl(ds.getString(0, 2));
 447                                 runDate = nvl(ds.getString(0, 3));
 448                                 runHour = nvl(ds.getString(0, 4), "12");
 449                                 runMin = nvl(ds.getString(0, 5), "00");
 450                                 runAMPM = nvl(ds.getString(0, 6), "AM");
 451                                 recurrence = nvl(ds.getString(0, 7));
 452                                 conditional = nvl(ds.getString(0, 8), "N");
 453                                 notify_type = nvl(ds.getString(0, 9), "1");
 454                                 downloadLimit = nvl(ds.getString(0, 10), "1000");
 455                                 formFields = nvl(ds.getString(0, 11));
 456                                 setScheduleID(ds.getString(0, 12));
 457                                 endHour = nvl(ds.getString(0, 13), "11");
 458                                 endMin = nvl(ds.getString(0, 14), "45");
 459                                 endAMPM = nvl(ds.getString(0, 15), "PM");
 460                                 encryptMode = nvl(ds.getString(0, "encrypt_yn"), "N");
 461                                 attachment = nvl(ds.getString(0, "attachment_yn"), "Y");
 462                                 conditionSQL = loadConditionalSQL(getScheduleID());
 463                         } else {
 464                                 String n_sql = Globals.getNewScheduleData();
 465                                 DataSet dsSeq = DbUtils.executeQuery(n_sql);
 466                                 String schedule_id = dsSeq.getString(0,0);
 467                                 setScheduleID(schedule_id);
 468                         }
 469             if(getScheduleID().length() > 0) {
 470                                 String t_sql = Globals.getLoadScheduleGetId();
 471                                 t_sql = t_sql.replace("[reportID]", reportID);
 472                                 t_sql = t_sql.replace("[getScheduleID()]", getScheduleID());
 473
 474                                 ds = DbUtils.executeQuery(t_sql);
 475
 476                                         for (int i = 0; i < ds.getRowCount(); i++){
 477                                         String nameToDisplay = ds.getString(i, 1);
 478                                         if (Globals.getUseLoginIdInSchedYN()!= null && Globals.getUseLoginIdInSchedYN().equals("Y")) {
 479                                                 nameToDisplay = ds.getString(i, 2);
 480                                         }
 481                                         if(nameToDisplay!=null && nameToDisplay.length() > 0)
 482                                                 emailToUsers.add(new IdNameValue(ds.getString(i, 0), nameToDisplay));
 483                                         else
 484                                                 emailToUsers.add(new IdNameValue(ds.getString(i, 0), ds.getString(i, 1)));
 485                                 }
 486                                 Collections.sort(emailToUsers, new NameComparator());
 487
 488                                 String r_sql = Globals.getLoadScheduleUsers();
 489                                 r_sql = r_sql.replace("[reportID]", reportID);
 490                                 r_sql = r_sql.replace("[getScheduleID()]", getScheduleID());
 491
 492                                 ds = DbUtils.executeQuery(r_sql);
 493
 494                                 for (int i = 0; i < ds.getRowCount(); i++)
 495                                         emailToRoles.add(new IdNameValue(ds.getString(i, 0), AppUtils.getRoleName(ds
 496                                                         .getString(i, 0))));
 497                                 Collections.sort(emailToRoles, new NameComparator());
 498
 499                                 infoUpdated = false;
 500             }
 501                 } catch (Exception e) {
 502                         throw new RuntimeException(
 503                                         "[ReportSchedule.loadScheduleData] Unable to load Report " + reportID
 504                                                         + " schedule. Error: " + e.getMessage());
 505                 }
 506         } // loadScheduleData
 507
 508         private void newScheduleData() {
 509                 try {
 510                         String sql = Globals.getNewScheduleData();
 511                         DataSet dsSeq = DbUtils.executeQuery(sql);
 512
 513                         String schedule_id = dsSeq.getString(0,0);
 514                         setScheduleID(schedule_id);
 515                 } catch (Exception e) {
 516                         throw new RuntimeException(
 517                                         "[ReportSchedule.newScheduleData] Unable to load Report " + reportID
 518                                                         + " schedule. Error: " + e.getMessage());
 519                 }
 520         } // newScheduleData
 521
 522         private String parseScheduleSQL(HttpServletRequest request, String sql) throws RaptorException {
 523         DataSet ds = null;
 524
 525         logger.debug(EELFLoggerDelegate.debugLogger, (sql));
 526         String[] reqParameters = Globals.getRequestParams().split(",");
 527         String[] sessionParameters = Globals.getSessionParams().split(",");
 528         String[] scheduleSessionParameters = Globals.getSessionParamsForScheduling().split(",");
 529         javax.servlet.http.HttpSession session = request.getSession();
 530         ReportRuntime rr = (ReportRuntime) session.getAttribute(AppConstants.SI_REPORT_RUNTIME);
 531         String userId = AppUtils.getUserID(request);
 532         String dbType = "";
 533         String dbInfo = rr.getDBInfo();
 534         ReportParamValues paramValues = rr.getReportParamValues();
 535                 int fieldCount = 0;
 536         // For Daytona removing all formfields which has null param value
 537         Pattern re1 = null;
 538         Matcher matcher = null;
 539         int index = 0;
 540         int posFormField = 0;
 541         int posAnd = 0;
 542                 if (!isNull(dbInfo) && (!dbInfo.equals(AppConstants.DB_LOCAL))) {
 543                         try {
 544                          dbType = remDbInfo.getDBType(dbInfo);
 545                         } catch (Exception ex) {
 546                            throw new RaptorException(ex);
 547                                 }
 548                 }
 549
 550                 sql = sql + " ";
 551                 sql = Pattern.compile("(^[\r\n]*|([\\s]))[Ss][Ee][Ll][Ee][Cc][Tt]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" SELECT ");
 552                 sql = Pattern.compile("(^[\r\n]*|([\\s]))[Ww][Hh][Ee][Rr][Ee]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" WHERE ");
 553                 sql = Pattern.compile("(^[\r\n]*|([\\s]))[Aa][Nn][Dd]([\r\n]*|[\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" AND ");
 554
 555                 if (rr.getFormFieldList() != null) {
 556                         for (Iterator iter = rr.getFormFieldList().getFormField().iterator(); iter.hasNext();) {
 557
 558                                 FormFieldType fft = (FormFieldType) iter.next();
 559                                 String fieldId = fft.getFieldId();
 560                                 String fieldDisplay = rr.getFormFieldDisplayName(fft);
 561                                 if(!fft.getFieldType().equals(FormField.FFT_BLANK)) {
 562                                 if (paramValues.isParameterMultiValue(fieldId)) {
 563                                         String replaceValue = rr.formatListValue(fieldDisplay, nvl(paramValues.getParamValue(fieldId)), null, false,
 564                                                         true, null, paramValues.getParamBaseSQL(fieldId));
 565                                         if(replaceValue.length() > 0) {
 566                                                 sql = Utils.replaceInString(sql, fieldDisplay, replaceValue);
 567                                         } else {
 568                                                 fieldCount++;
 569                                                 if(fieldCount == 1) {
 570                                                 }
 571                                                 while(sql.indexOf(fieldDisplay) > 0) {
 572
 573                                         re1 = Pattern.compile("(^[\r\n]|[\\s])AND(.*?[^\r\n]*)"+ "\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL);
 574
 575                                         posFormField = sql.indexOf(fieldDisplay);
 576                                         int posSelectField = sql.lastIndexOf("SELECT ", posFormField);
 577                                         int whereField = sql.indexOf(" WHERE" , posSelectField);
 578                                         int andField = 0;
 579                                         if(posFormField > whereField)
 580                                                 andField = sql.lastIndexOf(" AND ", posFormField);
 581                                         if (posFormField > andField && andField > whereField)
 582                                                 posAnd = andField;
 583                                         else
 584                                                 posAnd = 0;
 585                                         matcher = re1.matcher(sql);
 586
 587
 588                                         if (posAnd > 0 && matcher.find(posAnd-1)) {
 589                                                         matcher = re1.matcher(sql);
 590                                                         index = sql!=null?sql.lastIndexOf("["+fft.getFieldName()+"]"):-1;
 591
 592                                                         if(andField>0)
 593                                                                 index = andField;
 594                                                         else
 595                                                                 index = whereField;
 596                                                         if(index >= 0 && matcher.find(index-1)) {
 597                                                                 sql = sql.replace(matcher.group(), " ");
 598                                                         }
 599                                         } else {
 600
 601                                                 //sql = sql.replace
 602                                                 re1 = Pattern.compile("(^[\r\n]|[\\s])WHERE(.*?[^\r\n]*)\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL);
 603                                                 matcher = re1.matcher(sql);
 604                                                 if(matcher.find(whereField-1)) {
 605                                                         matcher = re1.matcher(sql);
 606                                                         index = sql!=null?sql.lastIndexOf("["+fft.getFieldName()+"]"):-1;
 607                                                         if(index >= 0 && matcher.find(index-30)) {
 608                                                                 sql = sql.replace(matcher.group(), " WHERE 1=1 ");
 609                                                 }
 610                                                 }
 611
 612                                         }
 613                                                 }
 614                                         }
 615
 616
 617                                 } else {
 618                                         String paramValue = "";
 619                                         if(paramValues.isParameterTextAreaValueAndModified(fieldId)) {
 620                                                 String value = "";
 621                                                 value = nvl(paramValues
 622                                                                 .getParamValue(fieldId));
 623                                                 paramValue = value;
 624                                         } else
 625                                                 paramValue = nvl(paramValues
 626                                                                 .getParamValue(fieldId));
 627
 628                                         if (paramValue!=null && paramValue.length() > 0) {
 629                         if(paramValue.toLowerCase().trim().startsWith("select ")) {
 630                             paramValue = Utils.replaceInString(paramValue, "[LOGGED_USERID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),userId));
 631                             paramValue = Utils.replaceInString(paramValue, "[USERID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),userId));
 632                             paramValue = Utils.replaceInString(paramValue, "[USER_ID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),userId));
 633
 634                             paramValue = Utils.replaceInString(paramValue, "''", "'");
 635                             ds = ConnectionUtils.getDataSet(paramValue, dbInfo);
 636                             if (ds.getRowCount() > 0){
 637                                                                 paramValue = ds.getString(0, 0);
 638                                                         }
 639                         }
 640                         logger.debug(EELFLoggerDelegate.debugLogger, ("SQLSQLBASED B4^^^^^^^^^ " + sql + " " + fft.getValidationType() + " " + fft.getFieldName() + " " + fft.getFieldId()));
 641                         if(fft!=null && (fft.getValidationType()!=null && (fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR) || fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN) ||fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC) ||fft.getValidationType().equals(FormField.VT_DATE) ))) {
 642                                 if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)) {
 643                                             sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 644                                                     paramValue) +((nvl(paramValues
 645                                                                                         .getParamValue(fieldId+"_Hr") ).length()>0)?" "+addZero(nvl(paramValues
 646                                                                                                         .getParamValue(fieldId+"_Hr")  ) ):""));
 647                                         }
 648                                         else if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN)) {
 649                                                     sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 650                                                             paramValue) + ((nvl(paramValues
 651                                                                                                 .getParamValue(fieldId+"_Hr") ).length()>0)?" "+addZero(nvl(paramValues
 652                                                                                                 .getParamValue(fieldId+"_Hr") )  ):"") + ((nvl(paramValues
 653                                                                                                                 .getParamValue(fieldId+"_Min") ).length()>0)?":" + addZero(nvl(paramValues
 654                                                                                                                 .getParamValue(fieldId+"_Min") ) ) : "")                )  ;
 655                                         }
 656                                         else if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
 657                                                     sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 658                                                             paramValue) + ((nvl(paramValues
 659                                                                                                 .getParamValue(fieldId+"_Hr") ).length()>0)?" "+addZero(nvl(paramValues
 660                                                                                                                 .getParamValue(fieldId+"_Hr")  ) ):"") + ((nvl(paramValues
 661                                                                                                                                 .getParamValue(fieldId+"_Min") ).length()>0)?":" + addZero(nvl(paramValues
 662                                                                                                                                 .getParamValue(fieldId+"_Min")  ) ) : "")                + ((nvl(paramValues
 663                                                                                                                                 .getParamValue(fieldId+"_Sec") ).length()>0)?":"+addZero(nvl(paramValues
 664                                                                                                                                 .getParamValue(fieldId+"_Sec") ) ) : ""                 ) ) ;
 665                                         } else {
 666                                                         sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 667                                                                 paramValue, "NULL"));
 668                                         }
 669
 670
 671                         } else {
 672                         if(paramValue!=null && paramValue.length() > 0) {
 673                                 if(sql.indexOf("'"+fieldDisplay+"'")!=-1 || sql.indexOf("'"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"'")!=-1
 674                                                 || sql.indexOf("'%"+fieldDisplay+"%'")!=-1 || sql.indexOf("'%"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"%'")!=-1
 675                                                 || sql.indexOf("'_"+fieldDisplay+"_'")!=-1 || sql.indexOf("'_"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"_'")!=-1
 676                                         || sql.indexOf("'%_"+fieldDisplay+"_%'")!=-1 || sql.indexOf("^"+fieldDisplay+"^")!=-1 || sql.indexOf("'%_"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"_%'")!=-1) {
 677                                           sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 678                                                 paramValue, "NULL"));
 679                                 } else {
 680                                         if(sql.indexOf(fieldDisplay)!=-1) {
 681                                                 if(nvl(paramValue).length()>0) {
 682                                                         try {
 683                                                                 double vD = Double.parseDouble(paramValue);
 684                                                                 sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 685                                                                 paramValue, "NULL"));
 686
 687                                                         } catch (NumberFormatException ex) {
 688                                                                  if (/*dbType.equals("DAYTONA") &&*/ sql.trim().toUpperCase().startsWith("SELECT")) {
 689                                                                                 sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 690                                                                                         paramValue, "NULL"));
 691                                                                  } else
 692                                                                     throw new UserDefinedException("Expected number, Given String for the form field \"" + fieldDisplay+"\"");
 693                                                         }
 694                                                 } else
 695                                                         sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 696                                                                 paramValue, "NULL"));
 697
 698                                         }
 699                                 }
 700                         }
 701                         else {
 702                                  if (dbType.equals("DAYTONA") && sql.trim().toUpperCase().startsWith("SELECT")) {
 703                                         sql = sql + " ";
 704                                         re1 = Pattern.compile("(^[\r\n]|[\\s]|[^0-9a-zA-Z])AND(.*?[^\r\n]*)"+ "\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL);
 705                                 posFormField = sql.indexOf(fieldDisplay);
 706                                 posAnd = sql.lastIndexOf(" AND ", posFormField);
 707                                 if(posAnd < 0)
 708                                                                 posAnd = 0;
 709                                 else if (posAnd > 2)
 710                                                                 posAnd = posAnd - 2;
 711                                         matcher = re1.matcher(sql);
 712                                         if (matcher.find(posAnd)) {
 713                                                 sql = sql.replace(matcher.group(), "");
 714                                         }
 715                                  } else {
 716                                 sql = Utils.replaceInString(sql, "'" + fieldDisplay + "'", nvl(
 717                                     paramValue, "NULL"));
 718                             sql = Utils.replaceInString(sql,  fieldDisplay, nvl(
 719                                     paramValue, "NULL"));
 720                                  }
 721                         }
 722                        }
 723
 724                    }
 725
 726                                  if (dbType.equals("DAYTONA") && sql.trim().toUpperCase().startsWith("SELECT")) {
 727                                         sql = sql + " ";
 728                                         re1 = Pattern.compile("(^[\r\n]|[\\s]|[^0-9a-zA-Z])AND(.*?[^\r\n]*)"+ "\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL); //+[\'\\)|\'|\\s]
 729                                 posFormField = sql.indexOf(fieldDisplay);
 730                                 posAnd = sql.lastIndexOf(" AND ", posFormField);
 731                                 if(posAnd < 0)
 732                                                                 posAnd = 0;
 733                                 else if (posAnd > 2)
 734                                                                 posAnd = posAnd - 2;
 735                                         matcher = re1.matcher(sql);
 736                                         if (matcher.find(posAnd)) {
 737                                                 sql = sql.replace(matcher.group(), " ");
 738                                         }
 739                                  } else {
 740
 741                                         logger.debug(EELFLoggerDelegate.debugLogger, ("ParamValue |" + paramValue + "| Sql |" + sql  + "| Multi Value |" + paramValues.isParameterMultiValue(fieldId)));
 742                                         sql = Utils.replaceInString(sql, "'" + fieldDisplay + "'", nvl(
 743                             paramValue, "NULL"));
 744                     sql = Utils.replaceInString(sql,  fieldDisplay , nvl(
 745                             paramValue, "NULL"));
 746                     logger.debug(EELFLoggerDelegate.debugLogger, ("SQLSQLBASED AFTER^^^^^^^^^ " + sql));
 747                                  }
 748
 749                                 } // else
 750                                 } // if BLANK
 751                         } // for
 752             if(request != null ) {
 753                 for (int i = 0; i < reqParameters.length; i++) {
 754                     if(!reqParameters[i].startsWith("ff")) {
 755                         if (nvl(request.getParameter(reqParameters[i].toUpperCase())).length() > 0)
 756                                 sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i].toUpperCase()) );
 757                     }
 758                     else
 759                       sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i]) );
 760                 }
 761
 762                 for (int i = 0; i < scheduleSessionParameters.length; i++) {
 763                         if(nvl(request.getParameter(scheduleSessionParameters[i])).trim().length()>0 )
 764                                 sql = Utils.replaceInString(sql, "[" + scheduleSessionParameters[i].toUpperCase()+"]", request.getParameter(scheduleSessionParameters[i]) );
 765                                 }
 766              }
 767             if(session != null ) {
 768                 for (int i = 0; i < sessionParameters.length; i++) {
 769                       logger.debug(EELFLoggerDelegate.debugLogger, (" Session " + " sessionParameters[i] " + sessionParameters[i] + " " + (String)session.getAttribute(sessionParameters[i])));
 770                       sql = Utils.replaceInString(sql, "[" + sessionParameters[i].toUpperCase()+"]", (String)session.getAttribute(sessionParameters[i]) );
 771
 772                 }
 773              }
 774                 } else {
 775         logger.debug(EELFLoggerDelegate.debugLogger, ("BEFORE LOGGED USERID REPLACE " + sql));
 776         sql = Utils.replaceInString(sql, "[LOGGED_USERID]", userId);
 777         sql = Utils.replaceInString(sql, "[USERID]", userId);
 778         sql = Utils.replaceInString(sql, "[USER_ID]", userId);
 779         logger.debug(EELFLoggerDelegate.debugLogger, ("AFTER LOGGED USERID REPLACE " + sql));
 780         // Added for Simon's GM Project where they need to get page_id in their query
 781         logger.debug(EELFLoggerDelegate.debugLogger, ("SQLSQLBASED no formfields " + sql));
 782         if(request != null ) {
 783            for (int i = 0; i < reqParameters.length; i++) {
 784              sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i]) );
 785            }
 786         }
 787         if(session != null ) {
 788             for (int i = 0; i < sessionParameters.length; i++) {
 789                 logger.debug(EELFLoggerDelegate.debugLogger, (" Session " + " sessionParameters[i] " + sessionParameters[i] + " " + (String)session.getAttribute(sessionParameters[i])));
 790                 sql = Utils.replaceInString(sql, "[" + sessionParameters[i].toUpperCase()+"]", (String)session.getAttribute(sessionParameters[i]) );
 791             }
 792          }
 793                 }
 794                 // if it is not multiple select and ParamValue is empty this is the place it can be replaced.
 795                 sql = Utils.replaceInString(sql, "[LOGGED_USERID]", userId);
 796                 sql = Utils.replaceInString(sql, "[USERID]", userId);
 797                 sql = Utils.replaceInString(sql, "[USER_ID]", userId);
 798         logger.debug(EELFLoggerDelegate.debugLogger, ("SQLSQLBASED no formfields after"  + sql));
 799
 800                 sql = Pattern.compile("([\n][\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" ");
 801                 return sql;
 802
 803         }
 804         public void persistScheduleData(Connection conn, HttpServletRequest request) throws RaptorException {
 805                 if (!infoUpdated)
 806                         return;
 807                 if (reportID.equals("-1"))
 808                         return;
 809
 810
 811                 try {
 812                 String sched_id = "";
 813                         StringBuffer query = new StringBuffer("");
 814                         query.append(" SELECT 1 FROM cr_report_schedule WHERE rep_id = " + reportID +  " and schedule_id = " + getScheduleID());
 815                         if(!AppUtils.isAdminUser(request))
 816                                 query.append(" and sched_user_id = " + getScheduleUserID());
 817                         DataSet ds = DbUtils.executeQuery(conn, query.toString());
 818                         if (ds.getRowCount() > 0) {
 819                                 StringBuffer sb = new StringBuffer();
 820                                 sb.append("UPDATE cr_report_schedule SET enabled_yn = '");
 821                                 sb.append(getSchedEnabled());
 822                                 sb.append("', start_date = ");
 823                                 if (getStartDate().length() > 0) {
 824                                         sb.append("TO_DATE('");
 825                                         sb.append(getStartDate());
 826                                         sb.append("', 'MM/DD/YYYY')");
 827                                 } else
 828                                         sb.append("NULL");
 829                                 sb.append(", end_date = ");
 830                                 if (getEndDate().length() > 0) {
 831                                         sb.append("TO_DATE('");
 832                                         sb.append(getEndDate());
 833                                         sb.append(" ");
 834                                         sb.append(getEndHour());
 835                                         sb.append(":");
 836                                         sb.append(getEndMin());
 837                                         sb.append(" ");
 838                                         sb.append(getEndAMPM());
 839                                         sb.append("', 'MM/DD/YYYY HH:MI AM')");
 840                                 } else
 841                                         sb.append("NULL");
 842                                 sb.append(", run_date = ");
 843                                 if (getRunDate().length() > 0) {
 844                                         sb.append("TO_DATE('");
 845                                         sb.append(getRunDate());
 846                                         sb.append(" ");
 847                                         sb.append(getRunHour());
 848                                         sb.append(":");
 849                                         sb.append(getRunMin());
 850                                         sb.append(" ");
 851                                         sb.append(getRunAMPM());
 852                                         sb.append("', 'MM/DD/YYYY HH:MI AM')");
 853                                 } else
 854                                         sb.append("NULL");
 855                                 sb.append(", recurrence = ");
 856                                 if (getRecurrence().length() > 0) {
 857                                         sb.append("'");
 858                                         sb.append(getRecurrence());
 859                                         sb.append("'");
 860                                 } else
 861                                         sb.append("NULL");
 862                                 sb.append(", conditional_yn = '");
 863                                 sb.append(getConditional());
 864                                         sb.append("'");
 865
 866                                 sb.append(", notify_type = ");
 867                                 sb.append(getNotify_type());
 868                                 sb.append(", encrypt_yn = '");
 869                                 sb.append(getEncryptMode()+"'");
 870                                 sb.append(", attachment_yn = '");
 871                                 sb.append(getAttachmentMode()+"'");
 872                                 sb.append(", max_row = ");
 873                                 sb.append(getDownloadLimit());
 874                                 sb.append(", initial_formFields = '");
 875                                 sb.append(getFormFields()+"'");
 876                                 sb.append(", processed_formfields = ''");
 877                                 sb.append(" WHERE rep_id = ");
 878                                 sb.append(reportID + " and sched_user_id = ");
 879                                 sb.append(getScheduleUserID());
 880                                 sb.append(" and schedule_id = ");
 881                                 sb.append(getScheduleID());
 882
 883                                 DbUtils.executeUpdate(conn, sb.toString());
 884                         } else {
 885                                 String w_sql = Globals.getNewScheduleData();
 886                                 DataSet dsSeq = DbUtils.executeQuery(w_sql);
 887                                 String schedule_id = dsSeq.getString(0,0);
 888                                 setScheduleID(schedule_id);
 889                                 StringBuffer sb = new StringBuffer();
 890                                 sb.append("INSERT INTO cr_report_schedule (schedule_id, sched_user_id, rep_id, enabled_yn, start_date, end_date, run_date, recurrence, conditional_yn, notify_type, max_row, initial_formfields, encrypt_yn, attachment_yn) VALUES(");
 891                                 sb.append(getScheduleID() + ", ");
 892                                 sb.append(getScheduleUserID() + ", ");
 893                                 sb.append(reportID);
 894                                 sb.append(", '");
 895                                 sb.append(getSchedEnabled());
 896                                 sb.append("', ");
 897                                 if (getStartDate().length() > 0) {
 898                                         sb.append("TO_DATE('");
 899                                         sb.append(getStartDate());
 900                                         sb.append("', 'MM/DD/YYYY')");
 901                                 } else
 902                                         sb.append("NULL");
 903                                 sb.append(", ");
 904                                 if (getEndDate().length() > 0) {
 905                                         sb.append("TO_DATE('");
 906                                         sb.append(getEndDate());
 907                                         sb.append(" ");
 908                                         sb.append(getEndHour());
 909                                         sb.append(":");
 910                                         sb.append(getEndMin());
 911                                         sb.append(" ");
 912                                         sb.append(getEndAMPM());
 913                                         sb.append("', 'MM/DD/YYYY HH:MI AM')");
 914                                 } else
 915                                         sb.append("NULL");
 916                                 sb.append(", ");
 917                                 if (getRunDate().length() > 0) {
 918                                         sb.append("TO_DATE('");
 919                                         sb.append(getRunDate());
 920                                         sb.append(" ");
 921                                         sb.append(getRunHour());
 922                                         sb.append(":");
 923                                         sb.append(getRunMin());
 924                                         sb.append(" ");
 925                                         sb.append(getRunAMPM());
 926                                         sb.append("', 'MM/DD/YYYY HH:MI AM')");
 927                                 } else
 928                                         sb.append("NULL");
 929                                 sb.append(", ");
 930                                 if (getRecurrence().length() > 0) {
 931                                         sb.append("'");
 932                                         sb.append(getRecurrence());
 933                                         sb.append("'");
 934                                 } else
 935                                         sb.append("NULL");
 936                                 sb.append(", '");
 937                                 sb.append(getConditional());
 938                                 sb.append("', ");
 939                                 sb.append(getNotify_type());
 940                                 sb.append(", ");
 941                                 sb.append(getDownloadLimit());
 942                                 sb.append(", '");
 943                                 sb.append(getFormFields()+"'");
 944                                 sb.append(",'");
 945                                 sb.append(getEncryptMode()+"'");
 946                                 sb.append(",'");
 947                                 sb.append(getAttachmentMode()+"'");
 948                                 sb.append(")");
 949                                 DbUtils.executeUpdate(conn, sb.toString());
 950
 951                         } // else
 952
 953
 954                         String d_sql = Globals.getExecuteUpdate();
 955                         d_sql = d_sql.replace("[reportID]", reportID);
 956                         d_sql = d_sql.replace("[getScheduleID()]", getScheduleID());
 957
 958                         DbUtils.executeUpdate(conn, d_sql);
 959
 960                         for (int i = 0; i < emailToUsers.size(); i++){
 961
 962                                 String sql = Globals.getExecuteUpdateUsers();
 963                                 sql = sql.replace("[getScheduleID()]", getScheduleID());
 964                                 sql = sql.replace("[reportID]", reportID);
 965                                 sql = sql.replace("[emailToUsers.get(i)).getId()]", ((IdNameValue) emailToUsers.get(i)).getId());
 966                                 sql = sql.replace("[(i + 1)]", String.valueOf(i + 1));
 967                                 DbUtils.executeUpdate(conn, sql);
 968
 969                         }
 970                         for (int i = 0; i < emailToRoles.size(); i++){
 971
 972                                 String sql = Globals.getExecuteUpdateRoles();
 973                                 sql = sql.replace("[getScheduleID()]", getScheduleID());
 974                                 sql = sql.replace("[reportID]", reportID);
 975                                 sql = sql.replace("[emailToRoles.get(i)).getId()]", ((IdNameValue) emailToRoles.get(i)).getId());
 976                                 sql = sql.replace("[((emailToUsers.size() + i + 1)]", String.valueOf(emailToUsers.size() + i + 1));
 977
 978                                 DbUtils.executeUpdate(conn, sql);
 979                         }
 980
 981                                 DbUtils.commitTransaction(conn);
 982
 983                         persistConditionSql(conn, getScheduleID(), parseScheduleSQL(request, getConditionSQL()));
 984
 985
 986                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report " + reportID + " - schedule data updated"));
 987                         String e_sql = Globals.getExecuteUpdateActivity();
 988                         e_sql = e_sql.replace("[getScheduleID()]", getScheduleID());
 989                         e_sql = e_sql.replace("[getRunDate()]", getRunDate());
 990                         e_sql = e_sql.replace("[getRunHour()]", getRunHour());
 991                         e_sql = e_sql.replace("[getRunMin()]", getRunMin());
 992                         e_sql = e_sql.replace("[getRunAMPM()]", getRunAMPM());
 993
 994                         DbUtils.executeUpdate(conn, e_sql);
 995
 996                         infoUpdated = false;
 997
 998                 } catch (RaptorException e) {
 999                         if (conn != null)
1000                                 DbUtils.rollbackTransaction(conn);
1001                         throw e;
1002                 } // catch
1003
1004         } // persistScheduleData
1005
1006         //deleting the schedule - Start
1007         public void deleteScheduleData(Connection conn) throws RaptorException {
1008                 if (reportID.equals("-1"))
1009                         return;
1010
1011                 Connection connection = (conn != null) ? conn : DbUtils.startTransaction();
1012                 String sched_id = "";
1013                 try {
1014                         //DataSet ds = DbUtils.executeQuery(connection,
1015                         //              "SELECT 1 FROM cr_report_schedule WHERE rep_id = " + reportID +" and sched_user_id = " + getScheduleUserID() + " and schedule_id = " + getScheduleID());
1016                         String a_sql = Globals.getDeleteScheduleData();
1017                         a_sql = a_sql.replace("[reportID]", reportID);
1018                         a_sql = a_sql.replace("[getScheduleUserID()]", getScheduleUserID());
1019                         a_sql = a_sql.replace("[getScheduleID()]", getScheduleID());
1020                         DataSet ds = DbUtils.executeQuery(connection, a_sql);
1021
1022                         if (ds.getRowCount() > 0) {
1023                                 //DbUtils.executeUpdate(connection,
1024                                 //              "DELETE cr_report_schedule_users WHERE rep_id = " + reportID+ " and schedule_id = " + getScheduleID());
1025                                 String b_sql = Globals.getDeleteScheduleDataUsers();
1026                                 b_sql = b_sql.replace("[reportID]", reportID);
1027                                 b_sql = b_sql.replace("[getScheduleID()]", getScheduleID());
1028
1029                                 DbUtils.executeUpdate(connection, b_sql);
1030
1031                                 StringBuffer sb = new StringBuffer();
1032                                 String c_sql = Globals.getDeleteScheduleDataId();
1033                                 c_sql = c_sql.replace("[reportID]", reportID);
1034                                 c_sql = c_sql.replace("[getScheduleUserID()]", getScheduleUserID());
1035                                 c_sql = c_sql.replace("[getScheduleID()]", getScheduleID());
1036
1037                                 sb.append(c_sql);
1038                                 //sb.append("DELETE FROM cr_report_schedule where rep_id = " + reportID +" and sched_user_id = " + getScheduleUserID() + " and schedule_id = " + getScheduleID());
1039
1040                                 DbUtils.executeUpdate(connection, sb.toString());
1041                         }
1042                         if (conn == null)
1043                                 DbUtils.commitTransaction(connection);
1044
1045                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report " + reportID + " - schedule data deleted"));
1046                 } catch (RaptorException e) {
1047                         if (conn == null)
1048                                 DbUtils.rollbackTransaction(connection);
1049                         throw e;
1050                 } // catch
1051         finally {
1052             if (conn == null)
1053             DbUtils.clearConnection(connection);
1054         }
1055         }       //deleteScheduleData
1056
1057         public String getScheduleUserID() {
1058                 return scheduleUserID;
1059         }
1060
1061         public void setScheduleUserID(String scheduleUserID) {
1062                 this.scheduleUserID = scheduleUserID;
1063         }
1064
1065         public String getScheduleID() {
1066                 return nvl(scheduleID);
1067         }
1068
1069         public void setScheduleID(String scheduleID) {
1070                 this.scheduleID = scheduleID;
1071         }
1072
1073         public String getEndAMPM() {
1074                 return endAMPM;
1075         }
1076
1077         public void setEndAMPM(String endAMPM) {
1078                 if (nvl(endAMPM).equals(this.endAMPM))
1079                         return;
1080                 infoUpdated = true;
1081                 this.endAMPM = nvl(endAMPM, "PM");
1082         }
1083
1084         public String getEndHour() {
1085                 return endHour;
1086         }
1087
1088         public void setEndHour(String endHour) {
1089                 if (nvl(endHour).equals(this.endHour))
1090                         return;
1091                 infoUpdated = true;
1092                 this.endHour = nvl(endHour, "11");
1093         }
1094
1095         public String getEndMin() {
1096                 return endMin;
1097         }
1098
1099         public void setEndMin(String endMin) {
1100                 if (nvl(endMin).equals(this.endMin))
1101                         return;
1102                 infoUpdated = true;
1103                 this.endMin = nvl(endMin, "45");
1104         }
1105
1106         public static boolean isNull(String a) {
1107                 if ((a == null) || (a.length() == 0) || a.equalsIgnoreCase("null"))
1108                         return true;
1109                 else
1110                         return false;
1111         }
1112
1113     public String addZero(String num) {
1114         int numInt = 0;
1115         try {
1116                 numInt = Integer.parseInt(num);
1117         }catch(NumberFormatException ex){
1118                 numInt = 0;
1119         }
1120         if(numInt < 10)
1121                         return "0"+numInt;
1122         else return ""+numInt;
1123     }
1124
1125         public static String loadConditionalSQL(String scheduleId)
1126                 throws RaptorException {
1127                 StringBuffer sb = new StringBuffer();
1128
1129                 PreparedStatement stmt = null;
1130
1131                 ResultSet rs = null;
1132                 String condition_sql = "";
1133                 Connection connection = null;
1134
1135                 try {
1136                         connection = DbUtils.getConnection();
1137
1138                         //String sql = "SELECT condition_large_sql FROM cr_report_schedule WHERE schedule_id=?";
1139                         String sql = Globals.getLoadCondSql();
1140                         stmt = connection.prepareStatement(sql);
1141                         stmt.setString(1,scheduleId);
1142                         rs = stmt.executeQuery();
1143                         if(Globals.isWeblogicServer()) {
1144                                         java.sql.Clob clob= null;
1145                                         Object obj = null;
1146                                         if (rs.next()) {
1147                                                 clob = rs.getClob(1);
1148                                         }
1149                                         else
1150                                                 throw new RuntimeException("Schedule ID " + scheduleId + " not found in the database");
1151
1152                                         int len = 0;
1153                                         char[] buffer = new char[512];
1154                                         try(Reader in = new InputStreamReader(clob.getAsciiStream())) {
1155                                                 while ((len = in.read(buffer)) != -1)
1156                                                         sb.append(buffer, 0, len);
1157                                         } catch(Exception e) {
1158                                                 //if any error while operating the input stream, just throw the error out
1159                                                 //so that outer try/catch block could handle it
1160                                                 throw e;
1161                                         }
1162             } else if (Globals.isPostgreSQL() || Globals.isMySQL()) {
1163                    String clob= null;
1164                                         Object obj = null;
1165                                         if (rs.next()) {
1166                                                 sb.append(rs.getString(1));
1167                                         }
1168                                         else
1169                                                 throw new RaptorException("Schedule ID " + scheduleId + " not found in the database");
1170                        } else {
1171                     throw new RaptorException("only maria db support for this ");
1172
1173                     }
1174                 } catch (SQLException ex) {
1175                         try {
1176                         StringBuffer query = new StringBuffer("");
1177
1178                         query.append(" SELECT condition_sql FROM cr_report_schedule WHERE schedule_id = " + scheduleId);
1179                         DataSet ds = DbUtils.executeQuery(query.toString());
1180                         if(ds.getRowCount()>0) {
1181                                 condition_sql = ds.getString(0,0);
1182                         }
1183                          return condition_sql;
1184                         } catch (RaptorException e) {
1185                                 DbUtils.rollbackTransaction(connection);
1186                                 throw e;
1187                         } // catch
1188
1189                 finally {
1190                     DbUtils.clearConnection(connection);
1191                 }
1192
1193                 } catch (IOException ex) {
1194                         throw new RaptorRuntimeException (ex.getMessage(), ex.getCause());
1195            } finally {
1196                    try {
1197                         if (connection != null)
1198                                 DbUtils.clearConnection(connection);
1199                            if(rs!=null)
1200                                    rs.close();
1201                            if(stmt!=null)
1202                                    stmt.close();
1203                    } catch (SQLException ex) {
1204                                 throw new ReportSQLException (ex.getMessage(), ex.getCause());
1205                    }
1206                 }
1207                 return sb.toString();
1208         } // loadConditionalSQL
1209
1210         private static void persistConditionSql(Connection connection, String scheduleId, String conditional_sql) throws RaptorException {
1211                 PreparedStatement stmt = null;
1212                 ResultSet rs = null;
1213
1214                 try {
1215                         //String sql = "update cr_report_schedule set condition_large_sql = EMPTY_CLOB() where  schedule_id = " + scheduleId;
1216                         String sql = Globals.getPersistCondSqlUpdate();
1217                         sql = sql.replace("[scheduleId]", scheduleId);
1218
1219                         DbUtils.executeUpdate(sql);
1220                         //sql = "SELECT condition_large_sql FROM cr_report_schedule cr WHERE schedule_id=?       FOR UPDATE";
1221                         sql = Globals.getPersistCondSqlLarge();
1222                         stmt = connection.prepareStatement(sql);
1223                         stmt.setString(1,scheduleId);
1224                         rs = stmt.executeQuery();
1225                         Writer out = null;
1226                         if (Globals.isPostgreSQL() || Globals.isMySQL()) {
1227                                         if (rs.next()) {
1228                                                 rs.updateString(1,conditional_sql);
1229                                                 rs.updateRow();
1230                                         }
1231                                         else
1232                                                 throw new RaptorException("Schedule ID " + scheduleId + " not found in the database");
1233              } else {
1234             throw new RaptorException("only maria db support for this ");
1235
1236               }
1237                 } catch (RaptorException ex) {
1238                         if(ex.getMessage().indexOf("invalid identifier")!= -1) {
1239                                 try {
1240                                 //String sql = "update cr_report_schedule set condition_sql = ? where schedule_id = " + scheduleId;
1241                                 String sql = Globals.getPersistCondSqlSet();
1242                                 sql = sql.replace("[scheduleId]", scheduleId);
1243                                         stmt = connection.prepareStatement(sql);
1244                                         stmt.setString(1,conditional_sql);
1245                                         stmt.executeUpdate();
1246                                         connection.commit();
1247                                 } catch (SQLException ex1) {
1248                                         try {
1249                                                 connection.rollback();
1250                                                 } catch (SQLException ex2) {}
1251
1252                                         }
1253                         } else {
1254                                 try {
1255                                         connection.rollback();
1256                                 } catch (SQLException ex2) {
1257                                                 throw new ReportSQLException (ex2.getMessage(), ex2.getCause());
1258                                 }
1259                         }
1260                 } catch (SQLException ex) {
1261                         try {
1262                                 connection.rollback();
1263                         } catch (SQLException ex2) {
1264                                         throw new ReportSQLException (ex2.getMessage(), ex2.getCause());
1265                         }
1266                 } catch (Exception ex) {
1267                         throw new RaptorRuntimeException (ex.getMessage(), ex.getCause());
1268                 } finally {
1269                            try {
1270                                    if(rs!=null)
1271                                            rs.close();
1272                                         if(stmt!=null)
1273                                                 stmt.close();
1274                                    } catch (SQLException ex) {
1275                                                 throw new ReportSQLException (ex.getMessage(), ex.getCause());
1276                                    }
1277                 }
1278         } // persistConditionSql
1279
1280         /**
1281          * Used to get encryption mode
1282          * @return the encryptMode
1283          */
1284         public String getEncryptMode() {
1285                 return encryptMode;
1286         }
1287
1288         /**
1289          * Used to set encryption mode
1290          * @param encryptMode the encryptMode to set
1291          */
1292         public void setEncryptMode(String encryptMode) {
1293                 this.encryptMode = encryptMode;
1294                 infoUpdated = true;
1295         }
1296
1297
1298         /**
1299          * Used to get Attachment mode
1300          * @return the attachment
1301          */
1302         public String getAttachmentMode() {
1303                 return attachment;
1304         }
1305
1306         public boolean isAttachmentMode() {
1307                 return nvl(attachment).toUpperCase().startsWith("Y");
1308         }
1309
1310         /**
1311          * Used to set Attachment mode
1312          * @param attachment to set
1313          */
1314         public void setAttachmentMode(String attachment) {
1315                 this.attachment = attachment;
1316                 infoUpdated = true;
1317         }
1318 } // ReportSchedule