From 6beb446925c967aca92f5513adf36c5db77c00d6 Mon Sep 17 00:00:00 2001 From: TATTAVARADA Date: Thu, 27 Apr 2017 07:53:18 -0400 Subject: [PORTAL-7] Rebase This rebasing includes common libraries and common overlays projects abstraction of components Change-Id: Ia1efa4deacdc5701e6205104ac021a6c80ed60ba Signed-off-by: st782s --- .../analytics/scheduler/SendNotifications.java | 462 +++++++++++++++++++++ 1 file changed, 462 insertions(+) create mode 100644 ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java (limited to 'ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java') diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java new file mode 100644 index 00000000..fefad844 --- /dev/null +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java @@ -0,0 +1,462 @@ +/*- + * ================================================================================ + * eCOMP Portal SDK + * ================================================================================ + * Copyright (C) 2017 AT&T Intellectual Property + * ================================================================================ + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * ================================================================================ + */ +package org.openecomp.portalsdk.analytics.scheduler; + +import java.math.BigDecimal; +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.text.SimpleDateFormat; +import java.util.Calendar; +import java.util.Date; +import java.util.UUID; + +import org.openecomp.portalsdk.analytics.error.RaptorException; +import org.openecomp.portalsdk.analytics.error.ReportSQLException; +import org.openecomp.portalsdk.analytics.scheduler.SchedulerUtil.Executor; +import org.openecomp.portalsdk.analytics.system.Globals; +import org.openecomp.portalsdk.analytics.util.Log; + +public class SendNotifications { + + 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(String p_mail_server, String p_sender, String p_system_name, String p_system_url, int connectionTimeout) throws RaptorException, Exception { + + System.out.println(p_mail_server + " " + p_sender + " " + p_system_name + " " + p_system_url); + int p_time_interval = Globals.getSchedulerInterval(); + int v_num_recs = 0; + String v_gen_key; + BigDecimal v_id = null; + String v_url; + String v_r_action = "report.download.pdf"; + String v_email_msg; + String v_formfields; + // String error_m; + // int transfer_timeout_limit = 1800; + // boolean v_attach_email_yn = true; + int v_schedule_id; + Date v_touch_date; + // Exception for_rec; + + Connection conn = schedulerUtil.getConnection(); + Statement stat = conn.createStatement(); + + String CNotificationsql = + /* + "SELECT x.rep_id, x.schedule_id, x.conditional_yn, x.condition_large_sql, x.notify_type, x.max_row, x.initial_formfields, x.processed_formfields, r.title, x.user_id " + + "FROM (" + + "SELECT rs.rep_id, rs.schedule_id, rs.sched_user_id user_id, rs.conditional_yn, rs.condition_large_sql, " + + "rs.notify_type, rs.max_row, rs.initial_formfields, rs.processed_formfields " + + "FROM cr_report_schedule rs " + + "WHERE rs.enabled_yn='Y' " + + "AND rs.start_" + + "date <= sysdate " + + "AND (rs.end_date >= sysdate or rs.end_date is null ) " + + "AND rs.run_date IS NOT NULL " + + ") x, cr_report r " + + "WHERE x.rep_id = r.rep_id "; + */ + + Globals.getAvailableSchedules().replace("[currentDate]", Globals.getCurrentDateString()); + + + ResultSet rs = stat.executeQuery(CNotificationsql); + + while (rs.next()) { + + v_schedule_id = rs.getInt("schedule_id"); + int offset = get_report_sched_offset(rs.getInt("rep_id"), v_schedule_id); + + if(offset >= p_time_interval) continue; + + + 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"); + if (v_touch_date != null) { + if (Math.abs(System.currentTimeMillis() - v_touch_date.getTime()) /1000 < (p_time_interval - 1)) { + return; + } + } + + if ("Y".equals(rs.getString("conditional_yn"))) { + + v_num_recs = (Integer) schedulerUtil.getSingleResult("select count(*) count from (" + rs.getString("condition_large_sql") + " )", "count"); + } + + if (v_num_recs > 0 || "N".equals(rs.getString("conditional_yn"))) { + + v_gen_key = ("Z" + UUID.randomUUID()).toString().substring(0,24); // 25 character string + Object sequenceId = schedulerUtil.getSingleResult(Globals.getSequenceNextVal().replace("[sequenceName]", "seq_email_sent_log_id"), "id"); + + if(sequenceId instanceof Long) + v_id = new BigDecimal((Long)sequenceId); + else if(sequenceId instanceof BigDecimal) + v_id = (BigDecimal)sequenceId; + + 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() + " )"); + + int notify_type = rs.getInt("notify_type"); + if (notify_type == 4) + v_r_action = "report.download"; + else if (notify_type == 2) + v_r_action = "report.download.pdf"; + else if (notify_type == 3) + v_r_action = "report.csv.download"; + else if (notify_type == 5) + v_r_action = "report.download.excel2007"; + else if (notify_type == 6) + v_r_action = "download.all"; + + if (rs.getObject("processed_formfields") != null) + v_formfields = modify_formfields(v_schedule_id, rs.getString("processed_formFields")); + else + v_formfields = strip_formfields(v_schedule_id, rs.getString("initial_formfields")); + + 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; + + boolean v_attach_email_yn = shouldSendAttachmentInEmail(v_schedule_id); + + v_email_msg = "

" + p_system_name + " System Notification

" + "

Report " + rs.getString("title") + " is available for viewing.

You can view the report if it is attached.
" + + "If it is not attached, or you have problem to open it, you can log into Business Direct and run the report.

" + ""; + + 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); + + } 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() + " )"); + 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() + " )"); + + } + + schedulerUtil.insertOrUpdate("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); + 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() + " )"); + + } + + } + + if(rs!=null) + rs.close(); + if(stat!=null) + stat.close(); + //conn.close(); + + } + + 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; + } + +} -- cgit 1.2.3-korg