diff options
author | 2019-10-10 20:47:23 +0900 | |
---|---|---|
committer | 2019-10-10 20:48:19 +0900 | |
commit | 97a09a16a65d48e96c9429d931267cb4f523f005 (patch) | |
tree | b109336548d126a04bb7e57ae677018d980c37bd /ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java | |
parent | d98d4c9f564428ee9edd398675b27226645d7ef5 (diff) |
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>
Diffstat (limited to 'ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java')
-rw-r--r-- | ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java | 721 |
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; + } } |