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