summaryrefslogtreecommitdiffstats
path: root/ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java
diff options
context:
space:
mode:
Diffstat (limited to 'ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java')
-rw-r--r--ecomp-sdk/epsdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java462
1 files changed, 462 insertions, 0 deletions
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 = "<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);
+
+ } 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;
+ }
+
+}