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 /*                              posFormField = sql.indexOf(fieldDisplay);
 575                                 posAnd = sql.lastIndexOf("and", posFormField);
 576                                 if(posAnd < 0) posAnd = 0;
 577                                 else if (posAnd > 2) posAnd = posAnd - 2;
 578                                         matcher = re1.matcher(sql);
 579 */
 580                                         posFormField = sql.indexOf(fieldDisplay);
 581                                         int posSelectField = sql.lastIndexOf("SELECT ", posFormField);
 582                                         int whereField = sql.indexOf(" WHERE" , posSelectField);
 583                                         int andField = 0;
 584                                         if(posFormField > whereField)
 585                                                 andField = sql.lastIndexOf(" AND ", posFormField);
 586                                         if (posFormField > andField && andField > whereField)
 587                                                 posAnd = andField;
 588                                         else
 589                                                 posAnd = 0;
 590                                         matcher = re1.matcher(sql);
 591
 592
 593                                         if (posAnd > 0 && matcher.find(posAnd-1)) {
 594                                                         matcher = re1.matcher(sql);
 595                                                         index = sql!=null?sql.lastIndexOf("["+fft.getFieldName()+"]"):-1;
 596
 597                                                         if(andField>0)
 598                                                                 index = andField;
 599                                                         else
 600                                                                 index = whereField;
 601                                                         if(index >= 0 && matcher.find(index-1)) {
 602                                                                 sql = sql.replace(matcher.group(), " ");
 603                                                         }
 604                                         } else {
 605
 606                                                 //sql = sql.replace
 607                                                 re1 = Pattern.compile("(^[\r\n]|[\\s])WHERE(.*?[^\r\n]*)\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL);
 608                                                 matcher = re1.matcher(sql);
 609                                                 if(matcher.find(whereField-1)) {
 610                                                         matcher = re1.matcher(sql);
 611                                                         index = sql!=null?sql.lastIndexOf("["+fft.getFieldName()+"]"):-1;
 612                                                         if(index >= 0 && matcher.find(index-30)) {
 613                                                                 sql = sql.replace(matcher.group(), " WHERE 1=1 ");
 614                                                 }
 615                                                 } /*else {
 616                                                         replaceValue = formatListValue("", Utils
 617                                                                         .oracleSafe(nvl(paramValues.getParamValue(fieldId))), null, false,
 618                                                                         true, null, paramValues.getParamBaseSQL(fieldId));
 619                                                         sql = Utils.replaceInString(sql, fieldDisplay, replaceValue);
 620                                                 }*/
 621
 622                                         }
 623                                                 }
 624                                         }
 625
 626
 627                                 } else {
 628                                         String paramValue = "";
 629                                         if(paramValues.isParameterTextAreaValueAndModified(fieldId)) {
 630                                                 String value = "";
 631                                                 value = nvl(paramValues
 632                                                                 .getParamValue(fieldId));
 633                                                 paramValue = value;
 634                                         } else
 635                                                 paramValue = nvl(paramValues
 636                                                                 .getParamValue(fieldId));
 637
 638                                         if (paramValue!=null && paramValue.length() > 0) {
 639                         if(paramValue.toLowerCase().trim().startsWith("select ")) {
 640                             paramValue = Utils.replaceInString(paramValue, "[LOGGED_USERID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),userId));
 641                             paramValue = Utils.replaceInString(paramValue, "[USERID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),userId));
 642                             paramValue = Utils.replaceInString(paramValue, "[USER_ID]", ESAPI.encoder().encodeForSQL( SecurityCodecUtil.getCodec(),userId));
 643
 644                             paramValue = Utils.replaceInString(paramValue, "''", "'");
 645                             ds = ConnectionUtils.getDataSet(paramValue, dbInfo);
 646                             if (ds.getRowCount() > 0){
 647                                                                 paramValue = ds.getString(0, 0);
 648                                                         }
 649                         }
 650                         logger.debug(EELFLoggerDelegate.debugLogger, ("SQLSQLBASED B4^^^^^^^^^ " + sql + " " + fft.getValidationType() + " " + fft.getFieldName() + " " + fft.getFieldId()));
 651                         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) ))) {
 652                                 if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_HR)) {
 653                                             sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 654                                                     paramValue) +((nvl(paramValues
 655                                                                                         .getParamValue(fieldId+"_Hr") ).length()>0)?" "+addZero(nvl(paramValues
 656                                                                                                         .getParamValue(fieldId+"_Hr")  ) ):""));
 657                                         }
 658                                         else if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_MIN)) {
 659                                                     sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 660                                                             paramValue) + ((nvl(paramValues
 661                                                                                                 .getParamValue(fieldId+"_Hr") ).length()>0)?" "+addZero(nvl(paramValues
 662                                                                                                 .getParamValue(fieldId+"_Hr") )  ):"") + ((nvl(paramValues
 663                                                                                                                 .getParamValue(fieldId+"_Min") ).length()>0)?":" + addZero(nvl(paramValues
 664                                                                                                                 .getParamValue(fieldId+"_Min") ) ) : "")                )  ;
 665                                         }
 666                                         else if(fft.getValidationType().equals(FormField.VT_TIMESTAMP_SEC)) {
 667                                                     sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 668                                                             paramValue) + ((nvl(paramValues
 669                                                                                                 .getParamValue(fieldId+"_Hr") ).length()>0)?" "+addZero(nvl(paramValues
 670                                                                                                                 .getParamValue(fieldId+"_Hr")  ) ):"") + ((nvl(paramValues
 671                                                                                                                                 .getParamValue(fieldId+"_Min") ).length()>0)?":" + addZero(nvl(paramValues
 672                                                                                                                                 .getParamValue(fieldId+"_Min")  ) ) : "")                + ((nvl(paramValues
 673                                                                                                                                 .getParamValue(fieldId+"_Sec") ).length()>0)?":"+addZero(nvl(paramValues
 674                                                                                                                                 .getParamValue(fieldId+"_Sec") ) ) : ""                 ) ) ;
 675                                         } else {
 676                                                         sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 677                                                                 paramValue, "NULL"));
 678                                         }
 679
 680
 681                         } else {
 682                         if(paramValue!=null && paramValue.length() > 0) {
 683                                 if(sql.indexOf("'"+fieldDisplay+"'")!=-1 || sql.indexOf("'"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"'")!=-1
 684                                                 || sql.indexOf("'%"+fieldDisplay+"%'")!=-1 || sql.indexOf("'%"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"%'")!=-1
 685                                                 || sql.indexOf("'_"+fieldDisplay+"_'")!=-1 || sql.indexOf("'_"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"_'")!=-1
 686                                         || sql.indexOf("'%_"+fieldDisplay+"_%'")!=-1 || sql.indexOf("^"+fieldDisplay+"^")!=-1 || sql.indexOf("'%_"+fieldDisplay)!=-1 || sql.indexOf(fieldDisplay+"_%'")!=-1) {
 687                                           sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 688                                                 paramValue, "NULL"));
 689                                 } else {
 690                                         if(sql.indexOf(fieldDisplay)!=-1) {
 691                                                 if(nvl(paramValue).length()>0) {
 692                                                         try {
 693                                                                 double vD = Double.parseDouble(paramValue);
 694                                                                 sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 695                                                                 paramValue, "NULL"));
 696
 697                                                         } catch (NumberFormatException ex) {
 698                                                                  if (/*dbType.equals("DAYTONA") &&*/ sql.trim().toUpperCase().startsWith("SELECT")) {
 699                                                                                 sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 700                                                                                         paramValue, "NULL"));
 701                                                                  } else
 702                                                                     throw new UserDefinedException("Expected number, Given String for the form field \"" + fieldDisplay+"\"");
 703                                                         }
 704                                                 } else
 705                                                         sql = Utils.replaceInString(sql, fieldDisplay, nvl(
 706                                                                 paramValue, "NULL"));
 707
 708                                         }
 709                                 }
 710                         }
 711                         else {
 712                                  if (dbType.equals("DAYTONA") && sql.trim().toUpperCase().startsWith("SELECT")) {
 713                                         sql = sql + " ";
 714                                         re1 = Pattern.compile("(^[\r\n]|[\\s]|[^0-9a-zA-Z])AND(.*?[^\r\n]*)"+ "\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL);
 715                                 posFormField = sql.indexOf(fieldDisplay);
 716                                 posAnd = sql.lastIndexOf(" AND ", posFormField);
 717                                 if(posAnd < 0)
 718                                                                 posAnd = 0;
 719                                 else if (posAnd > 2)
 720                                                                 posAnd = posAnd - 2;
 721                                         matcher = re1.matcher(sql);
 722                                         if (matcher.find(posAnd)) {
 723                                                 sql = sql.replace(matcher.group(), "");
 724                                         }
 725                                  } else {
 726                                 sql = Utils.replaceInString(sql, "'" + fieldDisplay + "'", nvl(
 727                                     paramValue, "NULL"));
 728                             sql = Utils.replaceInString(sql,  fieldDisplay, nvl(
 729                                     paramValue, "NULL"));
 730                                  }
 731                         }
 732                        }
 733
 734                    }
 735
 736                                  if (dbType.equals("DAYTONA") && sql.trim().toUpperCase().startsWith("SELECT")) {
 737                                         sql = sql + " ";
 738                                         re1 = Pattern.compile("(^[\r\n]|[\\s]|[^0-9a-zA-Z])AND(.*?[^\r\n]*)"+ "\\["+fft.getFieldName()+ "\\](.*?)\\s", Pattern.DOTALL); //+[\'\\)|\'|\\s]
 739                                 posFormField = sql.indexOf(fieldDisplay);
 740                                 posAnd = sql.lastIndexOf(" AND ", posFormField);
 741                                 if(posAnd < 0)
 742                                                                 posAnd = 0;
 743                                 else if (posAnd > 2)
 744                                                                 posAnd = posAnd - 2;
 745                                         matcher = re1.matcher(sql);
 746                                         if (matcher.find(posAnd)) {
 747                                                 sql = sql.replace(matcher.group(), " ");
 748                                         }
 749                                  } else {
 750
 751                                         logger.debug(EELFLoggerDelegate.debugLogger, ("ParamValue |" + paramValue + "| Sql |" + sql  + "| Multi Value |" + paramValues.isParameterMultiValue(fieldId)));
 752                                         sql = Utils.replaceInString(sql, "'" + fieldDisplay + "'", nvl(
 753                             paramValue, "NULL"));
 754                     sql = Utils.replaceInString(sql,  fieldDisplay , nvl(
 755                             paramValue, "NULL"));
 756                     logger.debug(EELFLoggerDelegate.debugLogger, ("SQLSQLBASED AFTER^^^^^^^^^ " + sql));
 757                                  }
 758
 759                                 } // else
 760                                 } // if BLANK
 761                         } // for
 762             if(request != null ) {
 763                 for (int i = 0; i < reqParameters.length; i++) {
 764                     if(!reqParameters[i].startsWith("ff")) {
 765                         if (nvl(request.getParameter(reqParameters[i].toUpperCase())).length() > 0)
 766                                 sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i].toUpperCase()) );
 767                     }
 768                     else
 769                       sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i]) );
 770                 }
 771
 772                 for (int i = 0; i < scheduleSessionParameters.length; i++) {
 773                         if(nvl(request.getParameter(scheduleSessionParameters[i])).trim().length()>0 )
 774                                 sql = Utils.replaceInString(sql, "[" + scheduleSessionParameters[i].toUpperCase()+"]", request.getParameter(scheduleSessionParameters[i]) );
 775                                 }
 776              }
 777             if(session != null ) {
 778                 for (int i = 0; i < sessionParameters.length; i++) {
 779                       logger.debug(EELFLoggerDelegate.debugLogger, (" Session " + " sessionParameters[i] " + sessionParameters[i] + " " + (String)session.getAttribute(sessionParameters[i])));
 780                       sql = Utils.replaceInString(sql, "[" + sessionParameters[i].toUpperCase()+"]", (String)session.getAttribute(sessionParameters[i]) );
 781
 782                 }
 783              }
 784                 } else {
 785         logger.debug(EELFLoggerDelegate.debugLogger, ("BEFORE LOGGED USERID REPLACE " + sql));
 786         sql = Utils.replaceInString(sql, "[LOGGED_USERID]", userId);
 787         sql = Utils.replaceInString(sql, "[USERID]", userId);
 788         sql = Utils.replaceInString(sql, "[USER_ID]", userId);
 789         logger.debug(EELFLoggerDelegate.debugLogger, ("AFTER LOGGED USERID REPLACE " + sql));
 790         // Added for Simon's GM Project where they need to get page_id in their query
 791         logger.debug(EELFLoggerDelegate.debugLogger, ("SQLSQLBASED no formfields " + sql));
 792         if(request != null ) {
 793            for (int i = 0; i < reqParameters.length; i++) {
 794              sql = Utils.replaceInString(sql, "[" + reqParameters[i].toUpperCase()+"]", request.getParameter(reqParameters[i]) );
 795            }
 796         }
 797         if(session != null ) {
 798             for (int i = 0; i < sessionParameters.length; i++) {
 799                 logger.debug(EELFLoggerDelegate.debugLogger, (" Session " + " sessionParameters[i] " + sessionParameters[i] + " " + (String)session.getAttribute(sessionParameters[i])));
 800                 sql = Utils.replaceInString(sql, "[" + sessionParameters[i].toUpperCase()+"]", (String)session.getAttribute(sessionParameters[i]) );
 801             }
 802          }
 803                 }
 804                 // if it is not multiple select and ParamValue is empty this is the place it can be replaced.
 805                 sql = Utils.replaceInString(sql, "[LOGGED_USERID]", userId);
 806                 sql = Utils.replaceInString(sql, "[USERID]", userId);
 807                 sql = Utils.replaceInString(sql, "[USER_ID]", userId);
 808         logger.debug(EELFLoggerDelegate.debugLogger, ("SQLSQLBASED no formfields after"  + sql));
 809
 810                 sql = Pattern.compile("([\n][\\s]*)",Pattern.DOTALL).matcher(sql).replaceAll(" ");
 811                 return sql;
 812
 813         }
 814         public void persistScheduleData(Connection conn, HttpServletRequest request) throws RaptorException {
 815                 if (!infoUpdated)
 816                         return;
 817                 if (reportID.equals("-1"))
 818                         return;
 819
 820
 821                 try {
 822                 String sched_id = "";
 823                         StringBuffer query = new StringBuffer("");
 824                         query.append(" SELECT 1 FROM cr_report_schedule WHERE rep_id = " + reportID +  " and schedule_id = " + getScheduleID());
 825                         if(!AppUtils.isAdminUser(request))
 826                                 query.append(" and sched_user_id = " + getScheduleUserID());
 827                         DataSet ds = DbUtils.executeQuery(conn, query.toString());
 828                         if (ds.getRowCount() > 0) {
 829                                 StringBuffer sb = new StringBuffer();
 830                                 sb.append("UPDATE cr_report_schedule SET enabled_yn = '");
 831                                 sb.append(getSchedEnabled());
 832                                 sb.append("', start_date = ");
 833                                 if (getStartDate().length() > 0) {
 834                                         sb.append("TO_DATE('");
 835                                         sb.append(getStartDate());
 836                                         sb.append("', 'MM/DD/YYYY')");
 837                                 } else
 838                                         sb.append("NULL");
 839                                 sb.append(", end_date = ");
 840                                 if (getEndDate().length() > 0) {
 841                                         sb.append("TO_DATE('");
 842                                         sb.append(getEndDate());
 843                                         sb.append(" ");
 844                                         sb.append(getEndHour());
 845                                         sb.append(":");
 846                                         sb.append(getEndMin());
 847                                         sb.append(" ");
 848                                         sb.append(getEndAMPM());
 849                                         sb.append("', 'MM/DD/YYYY HH:MI AM')");
 850                                 } else
 851                                         sb.append("NULL");
 852                                 sb.append(", run_date = ");
 853                                 if (getRunDate().length() > 0) {
 854                                         sb.append("TO_DATE('");
 855                                         sb.append(getRunDate());
 856                                         sb.append(" ");
 857                                         sb.append(getRunHour());
 858                                         sb.append(":");
 859                                         sb.append(getRunMin());
 860                                         sb.append(" ");
 861                                         sb.append(getRunAMPM());
 862                                         sb.append("', 'MM/DD/YYYY HH:MI AM')");
 863                                 } else
 864                                         sb.append("NULL");
 865                                 sb.append(", recurrence = ");
 866                                 if (getRecurrence().length() > 0) {
 867                                         sb.append("'");
 868                                         sb.append(getRecurrence());
 869                                         sb.append("'");
 870                                 } else
 871                                         sb.append("NULL");
 872                                 sb.append(", conditional_yn = '");
 873                                 sb.append(getConditional());
 874                                         sb.append("'");
 875
 876                                 sb.append(", notify_type = ");
 877                                 sb.append(getNotify_type());
 878                                 sb.append(", encrypt_yn = '");
 879                                 sb.append(getEncryptMode()+"'");
 880                                 sb.append(", attachment_yn = '");
 881                                 sb.append(getAttachmentMode()+"'");
 882                                 sb.append(", max_row = ");
 883                                 sb.append(getDownloadLimit());
 884                                 sb.append(", initial_formFields = '");
 885                                 sb.append(getFormFields()+"'");
 886                                 sb.append(", processed_formfields = ''");
 887                                 sb.append(" WHERE rep_id = ");
 888                                 sb.append(reportID + " and sched_user_id = ");
 889                                 sb.append(getScheduleUserID());
 890                                 sb.append(" and schedule_id = ");
 891                                 sb.append(getScheduleID());
 892
 893                                 DbUtils.executeUpdate(conn, sb.toString());
 894                         } else {
 895                                 String w_sql = Globals.getNewScheduleData();
 896                                 DataSet dsSeq = DbUtils.executeQuery(w_sql);
 897                                 String schedule_id = dsSeq.getString(0,0);
 898                                 setScheduleID(schedule_id);
 899                                 StringBuffer sb = new StringBuffer();
 900                                 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(");
 901                                 sb.append(getScheduleID() + ", ");
 902                                 sb.append(getScheduleUserID() + ", ");
 903                                 sb.append(reportID);
 904                                 sb.append(", '");
 905                                 sb.append(getSchedEnabled());
 906                                 sb.append("', ");
 907                                 if (getStartDate().length() > 0) {
 908                                         sb.append("TO_DATE('");
 909                                         sb.append(getStartDate());
 910                                         sb.append("', 'MM/DD/YYYY')");
 911                                 } else
 912                                         sb.append("NULL");
 913                                 sb.append(", ");
 914                                 if (getEndDate().length() > 0) {
 915                                         sb.append("TO_DATE('");
 916                                         sb.append(getEndDate());
 917                                         sb.append(" ");
 918                                         sb.append(getEndHour());
 919                                         sb.append(":");
 920                                         sb.append(getEndMin());
 921                                         sb.append(" ");
 922                                         sb.append(getEndAMPM());
 923                                         sb.append("', 'MM/DD/YYYY HH:MI AM')");
 924                                 } else
 925                                         sb.append("NULL");
 926                                 sb.append(", ");
 927                                 if (getRunDate().length() > 0) {
 928                                         sb.append("TO_DATE('");
 929                                         sb.append(getRunDate());
 930                                         sb.append(" ");
 931                                         sb.append(getRunHour());
 932                                         sb.append(":");
 933                                         sb.append(getRunMin());
 934                                         sb.append(" ");
 935                                         sb.append(getRunAMPM());
 936                                         sb.append("', 'MM/DD/YYYY HH:MI AM')");
 937                                 } else
 938                                         sb.append("NULL");
 939                                 sb.append(", ");
 940                                 if (getRecurrence().length() > 0) {
 941                                         sb.append("'");
 942                                         sb.append(getRecurrence());
 943                                         sb.append("'");
 944                                 } else
 945                                         sb.append("NULL");
 946                                 sb.append(", '");
 947                                 sb.append(getConditional());
 948                                 sb.append("', ");
 949                                 sb.append(getNotify_type());
 950                                 sb.append(", ");
 951                                 sb.append(getDownloadLimit());
 952                                 sb.append(", '");
 953                                 sb.append(getFormFields()+"'");
 954                                 sb.append(",'");
 955                                 sb.append(getEncryptMode()+"'");
 956                                 sb.append(",'");
 957                                 sb.append(getAttachmentMode()+"'");
 958                                 sb.append(")");
 959                                 DbUtils.executeUpdate(conn, sb.toString());
 960
 961                         } // else
 962
 963
 964                         String d_sql = Globals.getExecuteUpdate();
 965                         d_sql = d_sql.replace("[reportID]", reportID);
 966                         d_sql = d_sql.replace("[getScheduleID()]", getScheduleID());
 967
 968                         DbUtils.executeUpdate(conn, d_sql);
 969
 970                         for (int i = 0; i < emailToUsers.size(); i++){
 971
 972                                 String sql = Globals.getExecuteUpdateUsers();
 973                                 sql = sql.replace("[getScheduleID()]", getScheduleID());
 974                                 sql = sql.replace("[reportID]", reportID);
 975                                 sql = sql.replace("[emailToUsers.get(i)).getId()]", ((IdNameValue) emailToUsers.get(i)).getId());
 976                                 sql = sql.replace("[(i + 1)]", String.valueOf(i + 1));
 977                                 DbUtils.executeUpdate(conn, sql);
 978
 979                         }
 980                         for (int i = 0; i < emailToRoles.size(); i++){
 981
 982                                 String sql = Globals.getExecuteUpdateRoles();
 983                                 sql = sql.replace("[getScheduleID()]", getScheduleID());
 984                                 sql = sql.replace("[reportID]", reportID);
 985                                 sql = sql.replace("[emailToRoles.get(i)).getId()]", ((IdNameValue) emailToRoles.get(i)).getId());
 986                                 sql = sql.replace("[((emailToUsers.size() + i + 1)]", String.valueOf(emailToUsers.size() + i + 1));
 987
 988                                 DbUtils.executeUpdate(conn, sql);
 989                         }
 990
 991                                 DbUtils.commitTransaction(conn);
 992
 993                         persistConditionSql(conn, getScheduleID(), parseScheduleSQL(request, getConditionSQL()));
 994
 995
 996                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report " + reportID + " - schedule data updated"));
 997                         String e_sql = Globals.getExecuteUpdateActivity();
 998                         e_sql = e_sql.replace("[getScheduleID()]", getScheduleID());
 999                         e_sql = e_sql.replace("[getRunDate()]", getRunDate());
1000                         e_sql = e_sql.replace("[getRunHour()]", getRunHour());
1001                         e_sql = e_sql.replace("[getRunMin()]", getRunMin());
1002                         e_sql = e_sql.replace("[getRunAMPM()]", getRunAMPM());
1003
1004                         DbUtils.executeUpdate(conn, e_sql);
1005
1006                         infoUpdated = false;
1007
1008                 } catch (RaptorException e) {
1009                         if (conn != null)
1010                                 DbUtils.rollbackTransaction(conn);
1011                         throw e;
1012                 } // catch
1013
1014         } // persistScheduleData
1015
1016         //deleting the schedule - Start
1017         public void deleteScheduleData(Connection conn) throws RaptorException {
1018                 if (reportID.equals("-1"))
1019                         return;
1020
1021                 Connection connection = (conn != null) ? conn : DbUtils.startTransaction();
1022                 String sched_id = "";
1023                 try {
1024                         //DataSet ds = DbUtils.executeQuery(connection,
1025                         //              "SELECT 1 FROM cr_report_schedule WHERE rep_id = " + reportID +" and sched_user_id = " + getScheduleUserID() + " and schedule_id = " + getScheduleID());
1026                         String a_sql = Globals.getDeleteScheduleData();
1027                         a_sql = a_sql.replace("[reportID]", reportID);
1028                         a_sql = a_sql.replace("[getScheduleUserID()]", getScheduleUserID());
1029                         a_sql = a_sql.replace("[getScheduleID()]", getScheduleID());
1030                         DataSet ds = DbUtils.executeQuery(connection, a_sql);
1031
1032                         if (ds.getRowCount() > 0) {
1033                                 //DbUtils.executeUpdate(connection,
1034                                 //              "DELETE cr_report_schedule_users WHERE rep_id = " + reportID+ " and schedule_id = " + getScheduleID());
1035                                 String b_sql = Globals.getDeleteScheduleDataUsers();
1036                                 b_sql = b_sql.replace("[reportID]", reportID);
1037                                 b_sql = b_sql.replace("[getScheduleID()]", getScheduleID());
1038
1039                                 DbUtils.executeUpdate(connection, b_sql);
1040
1041                                 StringBuffer sb = new StringBuffer();
1042                                 String c_sql = Globals.getDeleteScheduleDataId();
1043                                 c_sql = c_sql.replace("[reportID]", reportID);
1044                                 c_sql = c_sql.replace("[getScheduleUserID()]", getScheduleUserID());
1045                                 c_sql = c_sql.replace("[getScheduleID()]", getScheduleID());
1046
1047                                 sb.append(c_sql);
1048                                 //sb.append("DELETE FROM cr_report_schedule where rep_id = " + reportID +" and sched_user_id = " + getScheduleUserID() + " and schedule_id = " + getScheduleID());
1049
1050                                 DbUtils.executeUpdate(connection, sb.toString());
1051                         }
1052                         if (conn == null)
1053                                 DbUtils.commitTransaction(connection);
1054
1055                         logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] DB update report " + reportID + " - schedule data deleted"));
1056                 } catch (RaptorException e) {
1057                         if (conn == null)
1058                                 DbUtils.rollbackTransaction(connection);
1059                         throw e;
1060                 } // catch
1061         finally {
1062             if (conn == null)
1063             DbUtils.clearConnection(connection);
1064         }
1065         }       //deleteScheduleData
1066
1067         public String getScheduleUserID() {
1068                 return scheduleUserID;
1069         }
1070
1071         public void setScheduleUserID(String scheduleUserID) {
1072                 this.scheduleUserID = scheduleUserID;
1073         }
1074
1075         public String getScheduleID() {
1076                 return nvl(scheduleID);
1077         }
1078
1079         public void setScheduleID(String scheduleID) {
1080                 this.scheduleID = scheduleID;
1081         }
1082
1083         public String getEndAMPM() {
1084                 return endAMPM;
1085         }
1086
1087         public void setEndAMPM(String endAMPM) {
1088                 if (nvl(endAMPM).equals(this.endAMPM))
1089                         return;
1090                 infoUpdated = true;
1091                 this.endAMPM = nvl(endAMPM, "PM");
1092         }
1093
1094         public String getEndHour() {
1095                 return endHour;
1096         }
1097
1098         public void setEndHour(String endHour) {
1099                 if (nvl(endHour).equals(this.endHour))
1100                         return;
1101                 infoUpdated = true;
1102                 this.endHour = nvl(endHour, "11");
1103         }
1104
1105         public String getEndMin() {
1106                 return endMin;
1107         }
1108
1109         public void setEndMin(String endMin) {
1110                 if (nvl(endMin).equals(this.endMin))
1111                         return;
1112                 infoUpdated = true;
1113                 this.endMin = nvl(endMin, "45");
1114         }
1115
1116         public static boolean isNull(String a) {
1117                 if ((a == null) || (a.length() == 0) || a.equalsIgnoreCase("null"))
1118                         return true;
1119                 else
1120                         return false;
1121         }
1122
1123     public String addZero(String num) {
1124         int numInt = 0;
1125         try {
1126                 numInt = Integer.parseInt(num);
1127         }catch(NumberFormatException ex){
1128                 numInt = 0;
1129         }
1130         if(numInt < 10)
1131                         return "0"+numInt;
1132         else return ""+numInt;
1133     }
1134
1135         public static String loadConditionalSQL(String scheduleId)
1136                 throws RaptorException {
1137                 StringBuffer sb = new StringBuffer();
1138
1139                 PreparedStatement stmt = null;
1140
1141                 ResultSet rs = null;
1142                 String condition_sql = "";
1143                 Connection connection = null;
1144
1145                 try {
1146                         connection = DbUtils.getConnection();
1147
1148                         //String sql = "SELECT condition_large_sql FROM cr_report_schedule WHERE schedule_id=?";
1149                         String sql = Globals.getLoadCondSql();
1150                         stmt = connection.prepareStatement(sql);
1151                         stmt.setString(1,scheduleId);
1152                         rs = stmt.executeQuery();
1153                         if(Globals.isWeblogicServer()) {
1154                                         java.sql.Clob clob= null;
1155                                         Object obj = null;
1156                                         if (rs.next()) {
1157                                                 clob = rs.getClob(1);
1158                                         }
1159                                         else
1160                                                 throw new RuntimeException("Schedule ID " + scheduleId + " not found in the database");
1161
1162                                         int len = 0;
1163                                         char[] buffer = new char[512];
1164                                         try(Reader in = new InputStreamReader(clob.getAsciiStream())) {
1165                                                 //                      if(obj instanceof oracle.sql.CLOB) {
1166                                                 //                              in = ((oracle.sql.CLOB) obj).getCharacterStream();
1167                                                 //                      } else if (obj instanceof weblogic.jdbc.wrapper.Clob) {
1168                                                 //                              in = ((weblogic.jdbc.base.BaseClob) obj).getCharacterStream();
1169                                                 //                      }
1170                                                 while ((len = in.read(buffer)) != -1)
1171                                                         sb.append(buffer, 0, len);
1172                                         } catch(Exception e) {
1173                                                 //if any error while operating the input stream, just throw the error out
1174                                                 //so that outer try/catch block could handle it
1175                                                 throw e;
1176                                         }
1177             } else if (Globals.isPostgreSQL() || Globals.isMySQL()) {
1178                    String clob= null;
1179                                         Object obj = null;
1180                                         if (rs.next()) {
1181                                                 sb.append(rs.getString(1));
1182                                         }
1183                                         else
1184                                                 throw new RaptorException("Schedule ID " + scheduleId + " not found in the database");
1185                        } else {
1186                                 /*oracle.sql.CLOB clob = null;
1187                                 if (rs.next())
1188                                         clob = (oracle.sql.CLOB) rs.getObject(1);
1189                                 else
1190                                         throw new RuntimeException("Schedule ID " + scheduleId + " not found in the database");
1191                                 int len = 0;
1192                                 char[] buffer = new char[512];
1193                                 Reader in = null;
1194                                 if(clob!=null) {
1195                                         in = clob.getCharacterStream();
1196                                         while ((len = in.read(buffer)) != -1)
1197                                                 sb.append(buffer, 0, len);
1198                                         in.close();
1199                                 }*/
1200                     throw new RaptorException("only maria db support for this ");
1201
1202                     }
1203                 } catch (SQLException ex) {
1204                         try {
1205                         StringBuffer query = new StringBuffer("");
1206
1207                         query.append(" SELECT condition_sql FROM cr_report_schedule WHERE schedule_id = " + scheduleId);
1208                         DataSet ds = DbUtils.executeQuery(query.toString());
1209                         if(ds.getRowCount()>0) {
1210                                 condition_sql = ds.getString(0,0);
1211                         }
1212                          return condition_sql;
1213                         //throw new ReportSQLException (ex.getMessage(), ex.getCause());
1214                         } catch (RaptorException e) {
1215                                 DbUtils.rollbackTransaction(connection);
1216                                 throw e;
1217                         } // catch
1218
1219                 finally {
1220                     DbUtils.clearConnection(connection);
1221                 }
1222
1223                 } catch (IOException ex) {
1224                         throw new RaptorRuntimeException (ex.getMessage(), ex.getCause());
1225            } finally {
1226                    try {
1227                         if (connection != null)
1228                                 DbUtils.clearConnection(connection);
1229                            if(rs!=null)
1230                                    rs.close();
1231                            if(stmt!=null)
1232                                    stmt.close();
1233                    } catch (SQLException ex) {
1234                                 throw new ReportSQLException (ex.getMessage(), ex.getCause());
1235                    }
1236                 }
1237                 return sb.toString();
1238         } // loadConditionalSQL
1239
1240         private static void persistConditionSql(Connection connection, String scheduleId, String conditional_sql) throws RaptorException {
1241                 PreparedStatement stmt = null;
1242                 ResultSet rs = null;
1243
1244                 try {
1245                         //String sql = "update cr_report_schedule set condition_large_sql = EMPTY_CLOB() where  schedule_id = " + scheduleId;
1246                         String sql = Globals.getPersistCondSqlUpdate();
1247                         sql = sql.replace("[scheduleId]", scheduleId);
1248
1249                         DbUtils.executeUpdate(sql);
1250                         //sql = "SELECT condition_large_sql FROM cr_report_schedule cr WHERE schedule_id=?       FOR UPDATE";
1251                         sql = Globals.getPersistCondSqlLarge();
1252                         stmt = connection.prepareStatement(sql);
1253                         stmt.setString(1,scheduleId);
1254                         rs = stmt.executeQuery();
1255                         Writer out = null;
1256             /*if(Globals.isWeblogicServer()) {
1257                 java.sql.Clob clob = null;
1258                 if (rs.next())
1259                         clob = rs.getClob(1);
1260                 else
1261                         throw new RuntimeException("Schedule ID " + scheduleId + " not found in the database");
1262
1263                 if (clob.length() > conditional_sql.length())
1264                         clob.truncate(0);
1265                         //clob.trim(reportXML.length());
1266                         out = ((weblogic.jdbc.vendor.oracle.OracleThinClob)clob).getCharacterOutputStream();
1267             } else*/
1268                         if (Globals.isPostgreSQL() || Globals.isMySQL()) {
1269                                         if (rs.next()) {
1270                                                 rs.updateString(1,conditional_sql);
1271                                                 rs.updateRow();
1272                                                 //sb.append(rs.getString(1));
1273                                         }
1274                                         else
1275                                                 throw new RaptorException("Schedule ID " + scheduleId + " not found in the database");
1276              } else {/*
1277                         oracle.sql.CLOB clob = null;
1278                         if (rs.next())
1279                                 clob = (oracle.sql.CLOB) rs.getObject(1);
1280                         else
1281                                 throw new RuntimeException("Schedule ID " + scheduleId + " not found in the database");
1282
1283                         if (clob.length() > conditional_sql.length())
1284                                 clob.trim(conditional_sql.length());
1285                  out = clob.getCharacterOutputStream();*/
1286             throw new RaptorException("only maria db support for this ");
1287
1288               }
1289                         //out.write(conditional_sql);
1290                         //out.flush();
1291                         //out.close();
1292                 } catch (RaptorException ex) {
1293                         if(ex.getMessage().indexOf("invalid identifier")!= -1) {
1294                                 try {
1295                                 //String sql = "update cr_report_schedule set condition_sql = ? where schedule_id = " + scheduleId;
1296                                 String sql = Globals.getPersistCondSqlSet();
1297                                 sql = sql.replace("[scheduleId]", scheduleId);
1298                                         stmt = connection.prepareStatement(sql);
1299                                         stmt.setString(1,conditional_sql);
1300                                         stmt.executeUpdate();
1301                                         connection.commit();
1302                                 } catch (SQLException ex1) {
1303                                         try {
1304                                                 connection.rollback();
1305                                                 } catch (SQLException ex2) {}
1306
1307                                         }
1308                         } else {
1309                                 try {
1310                                         connection.rollback();
1311                                 } catch (SQLException ex2) {
1312                                                 throw new ReportSQLException (ex2.getMessage(), ex2.getCause());
1313                                 }
1314                         }
1315                 } catch (SQLException ex) {
1316                         try {
1317                                 connection.rollback();
1318                         } catch (SQLException ex2) {
1319                                         throw new ReportSQLException (ex2.getMessage(), ex2.getCause());
1320                         }
1321                 } catch (Exception ex) {
1322                         throw new RaptorRuntimeException (ex.getMessage(), ex.getCause());
1323                 } finally {
1324                            try {
1325                                    if(rs!=null)
1326                                            rs.close();
1327                                         if(stmt!=null)
1328                                                 stmt.close();
1329                                    } catch (SQLException ex) {
1330                                                 throw new ReportSQLException (ex.getMessage(), ex.getCause());
1331                                    }
1332                 }
1333         } // persistConditionSql
1334
1335         /**
1336          * Used to get encryption mode
1337          * @return the encryptMode
1338          */
1339         public String getEncryptMode() {
1340                 return encryptMode;
1341         }
1342
1343         /**
1344          * Used to set encryption mode
1345          * @param encryptMode the encryptMode to set
1346          */
1347         public void setEncryptMode(String encryptMode) {
1348                 this.encryptMode = encryptMode;
1349                 infoUpdated = true;
1350         }
1351
1352
1353         /**
1354          * Used to get Attachment mode
1355          * @return the attachment
1356          */
1357         public String getAttachmentMode() {
1358                 return attachment;
1359         }
1360
1361         public boolean isAttachmentMode() {
1362                 return nvl(attachment).toUpperCase().startsWith("Y");
1363         }
1364
1365         /**
1366          * Used to set Attachment mode
1367          * @param attachment to set
1368          */
1369         public void setAttachmentMode(String attachment) {
1370                 this.attachment = attachment;
1371                 infoUpdated = true;
1372         }
1373 } // ReportSchedule