Fix NPE & other issues in ReportWrapper

Fix A "NullPointerException" could be thrown
Either log or rethrow this exception
Move the string literal on the left side of this string comparison

Issue-ID: PORTAL-562
Change-Id: I73ed2f3a874bc1275cee93a3156d06b527ca1fda
Signed-off-by: Parshad Patel <pars.patel@samsung.com>

1 files changed, 385 insertions, 336 deletions

diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java
index 3c871d87..3a761d84 100644
--- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java
+++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java
@@ -33,7 +33,7 @@
  *
  * ============LICENSE_END============================================
  *
- * 
+ *
  */
 package org.onap.portalsdk.analytics.scheduler;
 
@@ -51,27 +51,27 @@ import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;
 
 public class SendNotifications {
 
-	private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(SendNotifications.class);
-	SchedulerUtil schedulerUtil;
-	public SendEmail sendEmail;
-	
-	public SendNotifications() throws Exception {
-		schedulerUtil = new SchedulerUtil();
-		sendEmail = new SendEmail();
-		sendEmail.setSchedulerUtil(schedulerUtil);
-		init();
-	}
-
-	public  void deInit() throws SQLException {
-		schedulerUtil.closeConnection();
-	}
-	
-	public  void init() throws SQLException, ReportSQLException {
-		schedulerUtil.init();
-	}
+    private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(SendNotifications.class);
+    SchedulerUtil schedulerUtil;
+    public SendEmail sendEmail;
+
+    public SendNotifications() throws Exception {
+        schedulerUtil = new SchedulerUtil();
+        sendEmail = new SendEmail();
+        sendEmail.setSchedulerUtil(schedulerUtil);
+        init();
+    }
+
+    public void deInit() throws SQLException {
+        schedulerUtil.closeConnection();
+    }
+
+    public void init() throws SQLException, ReportSQLException {
+        schedulerUtil.init();
+    }
 
     public void send_notification(final String p_mail_server, final String p_sender, final String p_system_name,
-        final String p_system_url, final int connectionTimeout) throws RaptorException, Exception {
+            final String p_system_url, final int connectionTimeout) throws RaptorException, Exception {
 
         logger.info(p_mail_server + " " + p_sender + " " + p_system_name + " " + p_system_url);
         final int p_time_interval = Globals.getSchedulerInterval();
@@ -80,7 +80,7 @@ public class SendNotifications {
         String v_r_action = "report.download.pdf";
 
         final String CNotificationsql = Globals.getAvailableSchedules()
-            .replace("[currentDate]", Globals.getCurrentDateString());
+                .replace("[currentDate]", Globals.getCurrentDateString());
 
         try (final ResultSet rs = schedulerUtil.getConnection().createStatement().executeQuery(CNotificationsql);) {
             while (rs.next()) {
@@ -92,9 +92,10 @@ public class SendNotifications {
                 }
 
                 final Date v_touch_date = (Date) schedulerUtil.getSingleResult(
-                    "select touch_date from cr_report_email_sent_log where schedule_id = " + v_schedule_id
-                        + " and log_id = (select max(log_id) from cr_report_email_sent_log where schedule_id = "
-                        + v_schedule_id + ")", "touch_date");
+                        "select touch_date from cr_report_email_sent_log where schedule_id = " + v_schedule_id
+                                + " and log_id = (select max(log_id) from cr_report_email_sent_log where schedule_id = "
+                                + v_schedule_id + ")",
+                        "touch_date");
                 if (v_touch_date != null) {
                     if (Math.abs(System.currentTimeMillis() - v_touch_date.getTime()) / 1000 < (p_time_interval - 1)) {
                         return;
@@ -103,16 +104,18 @@ public class SendNotifications {
 
                 if ("Y".equals(rs.getString("conditional_yn"))) {
                     v_num_recs = (Integer) schedulerUtil
-                        .getSingleResult("select count(*) count from (" + rs.getString("condition_large_sql") + " )",
-                            "count");
+                            .getSingleResult(
+                                    "select count(*) count from (" + rs.getString("condition_large_sql") + " )",
+                                    "count");
                 }
 
                 if (v_num_recs > 0 || "N".equals(rs.getString("conditional_yn"))) {
                     final String v_gen_key = ("Z" + UUID.randomUUID()).toString()
-                        .substring(0, 24); // 25 character string
+                            .substring(0, 24); // 25 character string
                     final Object sequenceId =
-                        schedulerUtil.getSingleResult(
-                            Globals.getSequenceNextVal().replace("[sequenceName]", "seq_email_sent_log_id"), "id");
+                            schedulerUtil.getSingleResult(
+                                    Globals.getSequenceNextVal().replace("[sequenceName]", "seq_email_sent_log_id"),
+                                    "id");
 
                     if (sequenceId instanceof Long) {
                         v_id = new BigDecimal((Long) sequenceId);
@@ -121,10 +124,11 @@ public class SendNotifications {
                     }
 
                     schedulerUtil.insertOrUpdate(
-                        "insert into cr_report_email_sent_log (log_id, gen_key, schedule_id, rep_id, user_id, touch_date) values ("
-                            + v_id + ",'" + v_gen_key + "'," + rs.getInt("schedule_id") + "," + rs.getInt("rep_id")
-                            + ","
-                            + rs.getInt("user_id") + ", " + Globals.getCurrentDateString() + " )");
+                            "insert into cr_report_email_sent_log (log_id, gen_key, schedule_id, rep_id, user_id, touch_date) values ("
+                                    + v_id + ",'" + v_gen_key + "'," + rs.getInt("schedule_id") + ","
+                                    + rs.getInt("rep_id")
+                                    + ","
+                                    + rs.getInt("user_id") + ", " + Globals.getCurrentDateString() + " )");
 
                     final int notify_type = rs.getInt("notify_type");
                     if (notify_type == 4) {
@@ -147,52 +151,55 @@ public class SendNotifications {
                     }
 
                     final String v_url =
-                        p_system_url + "&r_action=" + v_r_action + "&log_id=" + v_id + "&user_id=" + rs
-                            .getString("user_id")
-                            + "&pdfAttachmentKey=" + v_gen_key + "&download_limit=" + rs.getInt("max_row")
-                            + v_formfields;
+                            p_system_url + "&r_action=" + v_r_action + "&log_id=" + v_id + "&user_id=" + rs
+                                    .getString("user_id")
+                                    + "&pdfAttachmentKey=" + v_gen_key + "&download_limit=" + rs.getInt("max_row")
+                                    + v_formfields;
 
                     final boolean v_attach_email_yn = shouldSendAttachmentInEmail(v_schedule_id);
 
                     final String v_email_msg =
-                        "<html><body><p><b><u><i>" + p_system_name + " System Notification</i></u></b></p>"
-                            + "<p>Report <b>" + rs.getString("title")
-                            + "</b> is available for viewing.</p><p>You can view the report if it is attached. </br>"
-                            + "If it is not attached, or you have problem to open it, you can log into Business Direct and run the report.</p>"
-                            + "</body></html>";
+                            "<html><body><p><b><u><i>" + p_system_name + " System Notification</i></u></b></p>"
+                                    + "<p>Report <b>" + rs.getString("title")
+                                    + "</b> is available for viewing.</p><p>You can view the report if it is attached. </br>"
+                                    + "If it is not attached, or you have problem to open it, you can log into Business Direct and run the report.</p>"
+                                    + "</body></html>";
 
                     if (rs.getInt("notify_type") != 6) {
                         sendEmail.sendEmail(p_mail_server, p_sender,
-                            p_system_name + " System Notification: Report " + rs.getString("title") + " generated",
-                            v_email_msg, v_url, rs.getInt("notify_type"), v_schedule_id, p_time_interval,
-                            v_attach_email_yn,
-                            connectionTimeout);
+                                p_system_name + " System Notification: Report " + rs.getString("title") + " generated",
+                                v_email_msg, v_url, rs.getInt("notify_type"), v_schedule_id, p_time_interval,
+                                v_attach_email_yn,
+                                connectionTimeout);
 
                     } else {
                         // may not necessary
                         schedulerUtil.insertOrUpdate(
-                            "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
-                                + v_schedule_id + ",'" + v_url + "'," + "'Success: http request began.', " + Globals
-                                .getCurrentDateString() + " )");
+                                "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
+                                        + v_schedule_id + ",'" + v_url + "'," + "'Success: http request began.', "
+                                        + Globals
+                                                .getCurrentDateString()
+                                        + " )");
                         schedulerUtil.insertOrUpdate(
-                            "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
-                                + v_schedule_id + ",'" + v_url + "',"
-                                + "'Success: http response recieved. Code  resp.status_code '' desc '' resp.reason_phrase', "
-                                + Globals.getCurrentDateString() + " )");
+                                "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values  ("
+                                        + v_schedule_id + ",'" + v_url + "',"
+                                        + "'Success: http response recieved. Code  resp.status_code '' desc '' resp.reason_phrase', "
+                                        + Globals.getCurrentDateString() + " )");
                     }
 
                     schedulerUtil.insertOrUpdate(
-                        "update cr_report_schedule set run_date = " + Globals.getCurrentDateString()
-                            + " where schedule_id="
-                            + v_schedule_id);
+                            "update cr_report_schedule set run_date = " + Globals.getCurrentDateString()
+                                    + " where schedule_id="
+                                    + v_schedule_id);
 
                     schedulerUtil.insertOrUpdate(
-                        "update cr_report_email_sent_log set sent_date= " + Globals.getCurrentDateString()
-                            + " , access_flag='N' where log_id=" + v_id);
+                            "update cr_report_email_sent_log set sent_date= " + Globals.getCurrentDateString()
+                                    + " , access_flag='N' where log_id=" + v_id);
                     schedulerUtil.insertOrUpdate(
-                        "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values ("
-                            + v_schedule_id
-                            + ",'" + v_url + "','Success: Email Sent', " + Globals.getCurrentDateString() + " )");
+                            "insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values ("
+                                    + v_schedule_id
+                                    + ",'" + v_url + "','Success: Email Sent', " + Globals.getCurrentDateString()
+                                    + " )");
                 }
             }
         } catch (final SQLException e) {
@@ -200,280 +207,322 @@ public class SendNotifications {
         }
     }
 
-	private boolean shouldSendAttachmentInEmail(int v_schedule_id) throws SQLException, ReportSQLException {
-
-		String l_boolean = (String) schedulerUtil.getSingleResult("SELECT ATTACHMENT_YN from cr_report_schedule where schedule_id  = " + v_schedule_id, "ATTACHMENT_YN");
-		if ("Y".equals(l_boolean))
-			return true;
-		return false;
-	}
-
-	private String strip_formfields(int v_schedule_id, String p_formfields) throws SQLException, ReportSQLException {
-
-		String v_formfields_insert = "";
-		String v_formfields_generate = "";
-		String v_name = "";
-		String v_value = "";
-		
-		
-		String[] column_values = schedulerUtil.cr_dissecturl(p_formfields, "&");
-		
-		for(String column_value : column_values){
-			if(column_value == null || column_value.isEmpty())
-				continue;
-			
-			v_name = column_value.substring(0, column_value.indexOf('='));
-			v_formfields_insert += column_value + "&";
-			v_value = column_value.substring(column_value.indexOf('=') + 1);
-			if (column_value.indexOf("_auto") > 0) {
-				v_formfields_generate = v_formfields_generate + v_name.substring(0, v_name.indexOf("_auto")) + "=" + v_value + "&";
-			} else {
-				v_formfields_generate = v_formfields_generate + column_value + "&";
-			}
-		}
-		
-		schedulerUtil.insertOrUpdate("update CR_REPORT_SCHEDULE set processed_formfields ='" + v_formfields_insert + "' where schedule_id = " + v_schedule_id);
-
-		return v_formfields_generate.substring(0, v_formfields_generate.length());
-
-	}
-
-	private String modify_formfields(int v_schedule_id, String p_formfields) throws SQLException, ReportSQLException {
-
-		class Result {
-
-			String v_formfields_insert = "";
-			String v_formfields_generate = "";
-			String v_name = "";
-			String v_value = "";
-			Date v_date;
-			String v_hour = "";
-			String v_hour_value = "";
-		}
-
-		final Result result = new Result();
-
-		final String v_recurrence = (String) schedulerUtil.getSingleResult("select recurrence from cr_report_schedule where schedule_id = " + v_schedule_id, "recurrence");
-
-		String[] column_values = schedulerUtil.cr_dissecturl(p_formfields, "&");
-		
-		for(String column_value : column_values){
-			if (column_value == null)
-				column_value = "";
-
-			if ("MONTHLY".equals(v_recurrence)) {
-				if (column_value.indexOf("_auto") > 0) {
-					result.v_name = column_value.substring(0, column_value.indexOf('='));
-					result.v_date = schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
-					result.v_value = schedulerUtil.add_months(result.v_date, 1).toString();
-
-					if (result.v_name.length() > 0) {
-						result.v_formfields_insert = result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
-						result.v_formfields_generate = result.v_formfields_generate + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value + "&";
-					}
-				} else {
-					result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
-					result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
-				}
-
-			} else if ("DAILY".equals(v_recurrence)) {
-				if (column_value.indexOf("_auto") > 0) {
-					result.v_name = column_value.substring(0, column_value.indexOf('='));
-					result.v_date = schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
-					result.v_value = schedulerUtil.add_months(result.v_date, 1).toString();
-
-					if (result.v_name.length() > 0) {
-						result.v_formfields_insert = result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
-						result.v_formfields_generate = result.v_formfields_generate + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value + "&";
-					}
-				} else {
-					result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
-					result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
-				}
-
-			} else if ("DAILY_MO_FR".equals(v_recurrence)) {
-				if (column_value.indexOf("_auto") > 0) {
-					result.v_name = column_value.substring(0, column_value.indexOf('='));
-					result.v_date = schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
-					SimpleDateFormat sdf = new SimpleDateFormat("EEE");
-					sdf.format(result.v_date);
-					if ("FRI".equals(result.v_date.toString())) {
-						result.v_date = schedulerUtil.add_days(result.v_date, 3);
-					} else if ("SAT".equals(result.v_date.toString())) {
-						result.v_date = schedulerUtil.add_days(result.v_date, 2);
-					} else {
-						result.v_date = schedulerUtil.add_days(result.v_date, 1);
-					}
-					result.v_value = result.v_date.toString();
-					if (result.v_name.length() > 0) {
-						result.v_formfields_insert = result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
-						result.v_formfields_generate = result.v_formfields_generate + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value + "&";
-					}
-
-				} else {
-					result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
-					result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
-				}
-
-			} else if ("HOURLY".equals(v_recurrence)) {
-				
-				result.v_name = column_value.indexOf('=')>0?column_value.substring(0, column_value.indexOf('=')) : "";
-				if (column_value.indexOf("_auto") > 0) {
-					
-					
-					String[] column_values2 = schedulerUtil.cr_dissecturl(p_formfields, "&");
-					
-					for(String column_value2 : column_values2){
-						
-						String key = column_value2.substring(0, column_value2.indexOf("="));
-						if(key.equals(result.v_name.substring(0, result.v_name.indexOf("_auto")))  || key.equals(result.v_name.substring(0, result.v_name.indexOf("_Hr")))){
-							result.v_hour = column_value2;
-						}
-					}
-					
-//					schedulerUtil.getAndExecute("select c.column_value from table(CR_DISSECTURL(p_formfields)) c where substr(c.column_value, 1, instr(c.column_value, '=')-1) = substr(" + result.v_name + ",1,instr(" + result.v_name + ",'_auto')-1)||'_Hr'", new Executor() {
-//
-//						@Override
-//						public void execute(ResultSet rs) throws SQLException {
-//
-//							result.v_hour = rs.getString("column_value");
-//						}
-//
-//					});
-
-					if (result.v_hour.length() > 0) {
-						result.v_hour_value = result.v_hour.substring(result.v_hour.indexOf('=') + 1);
-					}
-
-					result.v_date = schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1) + " " + result.v_hour_value, "mm/dd/yyyy HH24:MI:SS");
-
-					result.v_value = schedulerUtil.to_date_str(schedulerUtil.add_hours(result.v_date, 1), "mm/dd/yyyy HH24");
-
-					if (result.v_name.length() > 0) {
-						if (result.v_hour.length() > 0) {
-
-							result.v_formfields_insert = result.v_formfields_insert + result.v_name + "=" + result.v_value.substring(0, 10) + "&" + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "_Hr=" + result.v_value.substring(11, 13);
-							result.v_formfields_generate = result.v_formfields_generate + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value.substring(0, 10) + "&" + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "_Hr="
-									+ result.v_value.substring(11, 13) + "&";
-
-						} else {
-							result.v_formfields_insert = result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
-							result.v_formfields_generate = result.v_formfields_generate + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value + "&";
-						}
-					}
-				}
-				if (column_value.indexOf("_Hr") <= 0) {
-					result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
-					result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
-				}
-			} else if ("WEEKLY".equals(v_recurrence)) {
-
-				if (column_value.indexOf("_auto") > 0) {
-					result.v_name = column_value.substring(0, column_value.indexOf('='));
-					result.v_date = schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
-					result.v_value = schedulerUtil.add_days(result.v_date, 7).toString();
-
-					if (result.v_name.length() > 0) {
-						result.v_formfields_insert = result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
-						result.v_formfields_generate = result.v_formfields_generate + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value + "&";
-					}
-
-				} else {
-					result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
-					result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
-				}
-
-			} else {
-				if (column_value.indexOf("_auto") > 0) {
-					result.v_name = column_value.substring(0, column_value.indexOf('='));
-					result.v_date = schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
-					result.v_value = schedulerUtil.add_days(result.v_date, 7).toString();
-					if (result.v_name.length() > 0) {
-						result.v_formfields_insert = result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
-						result.v_formfields_generate = result.v_formfields_generate + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value + "&";
-					}
-
-				} else {
-					result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
-					result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
-				}
-			}
-		}
-
-		schedulerUtil.insertOrUpdate("update CR_REPORT_SCHEDULE set processed_formfields ='" + result.v_formfields_insert + "' where schedule_id =" + v_schedule_id);
-		return "&" + result.v_formfields_generate.substring(0, result.v_formfields_generate.length());
-	}
-
-	private int get_report_sched_offset(int p_rep_id, int p_schedule_id) throws SQLException, ReportSQLException {
-
-		class CrReportSchedule {
-
-			Date run_date;
-			String recurrence;
-		}
-		Date v_last_date = null;
-		Date v_sysdate = new Date();
-
-		final CrReportSchedule v_report_schedule_rec = new CrReportSchedule();
-
-		schedulerUtil.getAndExecute("SELECT * FROM cr_report_schedule WHERE rep_id = " + p_rep_id + " and schedule_id = " + p_schedule_id, new Executor() {
-
-			@Override
-			public void execute(ResultSet rs) throws SQLException {
-
-				v_report_schedule_rec.recurrence = rs.getString("recurrence");
-				java.sql.Timestamp runDate = rs.getTimestamp("run_date");
-				v_report_schedule_rec.run_date = new Date(runDate.getTime()) ;
-			}
-
-		});
-
-		if (v_report_schedule_rec.run_date == null || v_report_schedule_rec.run_date.compareTo(v_sysdate) > 0) {
-			return Integer.MAX_VALUE;
-		}
-
-		Date v_next_date = v_report_schedule_rec.run_date;
-
-		while (v_next_date.compareTo(v_sysdate) < 0) {
-			
-			if ("HOURLY".equals(v_report_schedule_rec.recurrence)) {
-				v_next_date = SchedulerUtil.add_hours(v_next_date, 1);
-			} else if ("DAILY".equals(v_report_schedule_rec.recurrence)) {
-				v_next_date = SchedulerUtil.add_days(v_next_date, 1);
-			} else if ("DAILY_MO_FR".equals(v_report_schedule_rec.recurrence)) {
-				SimpleDateFormat sdf = new SimpleDateFormat("EEE");
-				sdf.format(v_next_date);
-				if ("FRI".equals(v_next_date.toString())) {
-					v_next_date = SchedulerUtil.add_days(v_next_date, 3);
-				} else if ("SAT".equals(v_next_date.toString())) {
-					v_next_date = SchedulerUtil.add_days(v_next_date, 2);
-				} else {
-					v_next_date = SchedulerUtil.add_days(v_next_date, 1);
-				}
-			} else if ("WEEKLY".equals(v_report_schedule_rec.recurrence)) {
-				v_next_date = SchedulerUtil.add_days(v_next_date, 7);
-			} else if ("MONTHLY".equals(v_report_schedule_rec.recurrence)) {
-				v_next_date = SchedulerUtil.add_months(v_next_date, 1);
-			} else {
-				break;
-			}
-			v_last_date = v_next_date;
-		}
-
-		if (SchedulerUtil.trunc_hour(v_last_date).compareTo(SchedulerUtil.trunc_hour(v_sysdate)) == 0) {
-			return (int)(Math.abs (v_sysdate.getTime() - v_last_date.getTime()) / 1000);
-		} else {
-			// More than an hour
-			return 3601;
-		}
-	}
-
-	public SchedulerUtil getSchedulerUtil() {
-		return schedulerUtil;
-	}
-
-	public void setSchedulerUtil(SchedulerUtil schedulerUtil) {
-		this.schedulerUtil = schedulerUtil;
-	}
+    private boolean shouldSendAttachmentInEmail(int v_schedule_id) throws SQLException, ReportSQLException {
+
+        String l_boolean = (String) schedulerUtil.getSingleResult(
+                "SELECT ATTACHMENT_YN from cr_report_schedule where schedule_id  = " + v_schedule_id, "ATTACHMENT_YN");
+        if ("Y".equals(l_boolean))
+            return true;
+        return false;
+    }
+
+    private String strip_formfields(int v_schedule_id, String p_formfields) throws SQLException, ReportSQLException {
+
+        String v_formfields_insert = "";
+        String v_formfields_generate = "";
+        String v_name = "";
+        String v_value = "";
+
+        String[] column_values = schedulerUtil.cr_dissecturl(p_formfields, "&");
+
+        for (String column_value : column_values) {
+            if (column_value == null || column_value.isEmpty())
+                continue;
+
+            v_name = column_value.substring(0, column_value.indexOf('='));
+            v_formfields_insert += column_value + "&";
+            v_value = column_value.substring(column_value.indexOf('=') + 1);
+            if (column_value.indexOf("_auto") > 0) {
+                v_formfields_generate =
+                        v_formfields_generate + v_name.substring(0, v_name.indexOf("_auto")) + "=" + v_value + "&";
+            } else {
+                v_formfields_generate = v_formfields_generate + column_value + "&";
+            }
+        }
+
+        schedulerUtil.insertOrUpdate("update CR_REPORT_SCHEDULE set processed_formfields ='" + v_formfields_insert
+                + "' where schedule_id = " + v_schedule_id);
+
+        return v_formfields_generate.substring(0, v_formfields_generate.length());
+
+    }
+
+    private String modify_formfields(int v_schedule_id, String p_formfields) throws SQLException, ReportSQLException {
+
+        class Result {
+
+            String v_formfields_insert = "";
+            String v_formfields_generate = "";
+            String v_name = "";
+            String v_value = "";
+            Date v_date;
+            String v_hour = "";
+            String v_hour_value = "";
+        }
+
+        final Result result = new Result();
+
+        final String v_recurrence = (String) schedulerUtil.getSingleResult(
+                "select recurrence from cr_report_schedule where schedule_id = " + v_schedule_id, "recurrence");
+
+        String[] column_values = schedulerUtil.cr_dissecturl(p_formfields, "&");
+
+        for (String column_value : column_values) {
+            if (column_value == null)
+                column_value = "";
+
+            if ("MONTHLY".equals(v_recurrence)) {
+                if (column_value.indexOf("_auto") > 0) {
+                    result.v_name = column_value.substring(0, column_value.indexOf('='));
+                    result.v_date =
+                            schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
+                    result.v_value = schedulerUtil.add_months(result.v_date, 1).toString();
+
+                    if (result.v_name.length() > 0) {
+                        result.v_formfields_insert =
+                                result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
+                        result.v_formfields_generate = result.v_formfields_generate
+                                + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
+                                + "&";
+                    }
+                } else {
+                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
+                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
+                }
+
+            } else if ("DAILY".equals(v_recurrence)) {
+                if (column_value.indexOf("_auto") > 0) {
+                    result.v_name = column_value.substring(0, column_value.indexOf('='));
+                    result.v_date =
+                            schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
+                    result.v_value = schedulerUtil.add_months(result.v_date, 1).toString();
+
+                    if (result.v_name.length() > 0) {
+                        result.v_formfields_insert =
+                                result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
+                        result.v_formfields_generate = result.v_formfields_generate
+                                + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
+                                + "&";
+                    }
+                } else {
+                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
+                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
+                }
+
+            } else if ("DAILY_MO_FR".equals(v_recurrence)) {
+                if (column_value.indexOf("_auto") > 0) {
+                    result.v_name = column_value.substring(0, column_value.indexOf('='));
+                    result.v_date =
+                            schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
+                    SimpleDateFormat sdf = new SimpleDateFormat("EEE");
+                    sdf.format(result.v_date);
+                    if ("FRI".equals(result.v_date.toString())) {
+                        result.v_date = schedulerUtil.add_days(result.v_date, 3);
+                    } else if ("SAT".equals(result.v_date.toString())) {
+                        result.v_date = schedulerUtil.add_days(result.v_date, 2);
+                    } else {
+                        result.v_date = schedulerUtil.add_days(result.v_date, 1);
+                    }
+                    result.v_value = result.v_date.toString();
+                    if (result.v_name.length() > 0) {
+                        result.v_formfields_insert =
+                                result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
+                        result.v_formfields_generate = result.v_formfields_generate
+                                + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
+                                + "&";
+                    }
+
+                } else {
+                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
+                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
+                }
+
+            } else if ("HOURLY".equals(v_recurrence)) {
+
+                result.v_name =
+                        column_value.indexOf('=') > 0 ? column_value.substring(0, column_value.indexOf('=')) : "";
+                if (column_value.indexOf("_auto") > 0) {
+
+                    String[] column_values2 = schedulerUtil.cr_dissecturl(p_formfields, "&");
+
+                    for (String column_value2 : column_values2) {
+
+                        String key = column_value2.substring(0, column_value2.indexOf("="));
+                        if (key.equals(result.v_name.substring(0, result.v_name.indexOf("_auto")))
+                                || key.equals(result.v_name.substring(0, result.v_name.indexOf("_Hr")))) {
+                            result.v_hour = column_value2;
+                        }
+                    }
+
+                    // schedulerUtil.getAndExecute("select c.column_value from table(CR_DISSECTURL(p_formfields)) c
+                    // where substr(c.column_value, 1, instr(c.column_value, '=')-1) = substr(" + result.v_name +
+                    // ",1,instr(" + result.v_name + ",'_auto')-1)||'_Hr'", new Executor() {
+                    //
+                    // @Override
+                    // public void execute(ResultSet rs) throws SQLException {
+                    //
+                    // result.v_hour = rs.getString("column_value");
+                    // }
+                    //
+                    // });
+
+                    if (result.v_hour.length() > 0) {
+                        result.v_hour_value = result.v_hour.substring(result.v_hour.indexOf('=') + 1);
+                    }
+
+                    result.v_date = schedulerUtil.to_date(
+                            column_value.substring(column_value.indexOf('=') + 1) + " " + result.v_hour_value,
+                            "mm/dd/yyyy HH24:MI:SS");
+
+                    result.v_value =
+                            schedulerUtil.to_date_str(schedulerUtil.add_hours(result.v_date, 1), "mm/dd/yyyy HH24");
+
+                    if (result.v_name.length() > 0) {
+                        if (result.v_hour.length() > 0) {
+
+                            result.v_formfields_insert =
+                                    result.v_formfields_insert + result.v_name + "=" + result.v_value.substring(0, 10)
+                                            + "&" + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "_Hr="
+                                            + result.v_value.substring(11, 13);
+                            result.v_formfields_generate = result.v_formfields_generate
+                                    + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "="
+                                    + result.v_value.substring(0, 10) + "&"
+                                    + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "_Hr="
+                                    + result.v_value.substring(11, 13) + "&";
+
+                        } else {
+                            result.v_formfields_insert =
+                                    result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
+                            result.v_formfields_generate = result.v_formfields_generate
+                                    + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
+                                    + "&";
+                        }
+                    }
+                }
+                if (column_value.indexOf("_Hr") <= 0) {
+                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
+                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
+                }
+            } else if ("WEEKLY".equals(v_recurrence)) {
+
+                if (column_value.indexOf("_auto") > 0) {
+                    result.v_name = column_value.substring(0, column_value.indexOf('='));
+                    result.v_date =
+                            schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
+                    result.v_value = schedulerUtil.add_days(result.v_date, 7).toString();
+
+                    if (result.v_name.length() > 0) {
+                        result.v_formfields_insert =
+                                result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
+                        result.v_formfields_generate = result.v_formfields_generate
+                                + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
+                                + "&";
+                    }
+
+                } else {
+                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
+                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
+                }
+
+            } else {
+                if (column_value.indexOf("_auto") > 0) {
+                    result.v_name = column_value.substring(0, column_value.indexOf('='));
+                    result.v_date =
+                            schedulerUtil.to_date(column_value.substring(column_value.indexOf('=') + 1), "mm/dd/yyyy");
+                    result.v_value = schedulerUtil.add_days(result.v_date, 7).toString();
+                    if (result.v_name.length() > 0) {
+                        result.v_formfields_insert =
+                                result.v_formfields_insert + result.v_name + "=" + result.v_value + "&";
+                        result.v_formfields_generate = result.v_formfields_generate
+                                + result.v_name.substring(0, result.v_name.indexOf("_auto")) + "=" + result.v_value
+                                + "&";
+                    }
+
+                } else {
+                    result.v_formfields_insert = result.v_formfields_insert + column_value + "&";
+                    result.v_formfields_generate = result.v_formfields_generate + column_value + "&";
+                }
+            }
+        }
+
+        schedulerUtil.insertOrUpdate("update CR_REPORT_SCHEDULE set processed_formfields ='"
+                + result.v_formfields_insert + "' where schedule_id =" + v_schedule_id);
+        return "&" + result.v_formfields_generate.substring(0, result.v_formfields_generate.length());
+    }
+
+    private int get_report_sched_offset(int p_rep_id, int p_schedule_id) throws SQLException, ReportSQLException {
+
+        class CrReportSchedule {
+
+            Date run_date;
+            String recurrence;
+        }
+        Date v_last_date = null;
+        Date v_sysdate = new Date();
+
+        final CrReportSchedule v_report_schedule_rec = new CrReportSchedule();
+
+        schedulerUtil.getAndExecute(
+                "SELECT * FROM cr_report_schedule WHERE rep_id = " + p_rep_id + " and schedule_id = " + p_schedule_id,
+                new Executor() {
+
+                    @Override
+                    public void execute(ResultSet rs) throws SQLException {
+
+                        v_report_schedule_rec.recurrence = rs.getString("recurrence");
+                        java.sql.Timestamp runDate = rs.getTimestamp("run_date");
+                        v_report_schedule_rec.run_date = new Date(runDate.getTime());
+                    }
+
+                });
+
+        if (v_report_schedule_rec.run_date == null || v_report_schedule_rec.run_date.compareTo(v_sysdate) > 0) {
+            return Integer.MAX_VALUE;
+        }
+
+        Date v_next_date = v_report_schedule_rec.run_date;
+
+        while (v_next_date.compareTo(v_sysdate) < 0) {
+
+            if ("HOURLY".equals(v_report_schedule_rec.recurrence)) {
+                v_next_date = SchedulerUtil.add_hours(v_next_date, 1);
+            } else if ("DAILY".equals(v_report_schedule_rec.recurrence)) {
+                v_next_date = SchedulerUtil.add_days(v_next_date, 1);
+            } else if ("DAILY_MO_FR".equals(v_report_schedule_rec.recurrence)) {
+                SimpleDateFormat sdf = new SimpleDateFormat("EEE");
+                sdf.format(v_next_date);
+                if ("FRI".equals(v_next_date.toString())) {
+                    v_next_date = SchedulerUtil.add_days(v_next_date, 3);
+                } else if ("SAT".equals(v_next_date.toString())) {
+                    v_next_date = SchedulerUtil.add_days(v_next_date, 2);
+                } else {
+                    v_next_date = SchedulerUtil.add_days(v_next_date, 1);
+                }
+            } else if ("WEEKLY".equals(v_report_schedule_rec.recurrence)) {
+                v_next_date = SchedulerUtil.add_days(v_next_date, 7);
+            } else if ("MONTHLY".equals(v_report_schedule_rec.recurrence)) {
+                v_next_date = SchedulerUtil.add_months(v_next_date, 1);
+            } else {
+                break;
+            }
+            v_last_date = v_next_date;
+        }
+
+        if (v_last_date != null
+                && SchedulerUtil.trunc_hour(v_last_date).compareTo(SchedulerUtil.trunc_hour(v_sysdate)) == 0) {
+            return (int) (Math.abs(v_sysdate.getTime() - v_last_date.getTime()) / 1000);
+        } else {
+            // More than an hour
+            return 3601;
+        }
+    }
+
+    public SchedulerUtil getSchedulerUtil() {
+        return schedulerUtil;
+    }
+
+    public void setSchedulerUtil(SchedulerUtil schedulerUtil) {
+        this.schedulerUtil = schedulerUtil;
+    }
 
 }