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

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