diff options
Diffstat (limited to 'ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler')
3 files changed, 1244 insertions, 0 deletions
diff --git a/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SchedulerUtil.java b/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SchedulerUtil.java new file mode 100644 index 00000000..d5162cc1 --- /dev/null +++ b/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SchedulerUtil.java @@ -0,0 +1,369 @@ +/*- + * ================================================================================ + * 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.io.ByteArrayInputStream; +import java.io.IOException; +import java.io.InputStream; +import java.io.OutputStream; +import java.math.BigDecimal; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Types; +import java.text.SimpleDateFormat; +import java.util.Calendar; +import java.util.Date; +import java.util.List; +import java.util.Locale; + +import org.openecomp.portalsdk.analytics.error.RaptorException; +import org.openecomp.portalsdk.analytics.error.ReportSQLException; +import org.openecomp.portalsdk.analytics.system.DbUtils; + + + + +public class SchedulerUtil { + + private Connection conn = null; + + protected Connection getConnection() { + return conn; + } + + protected void setConnection(Connection _conn) { + conn = _conn; + } + + protected Connection init() throws SQLException, ReportSQLException{ + if(conn != null) + return conn; + conn = DbUtils.getConnection(); + return conn; + } + + protected void closeConnection() throws SQLException { + if(conn != null) conn.close(); + } + + public void insertOrUpdate(String sql) throws SQLException, ReportSQLException { + + Statement stat = null; + try{ + //conn = getConnection(); + stat = conn.createStatement(); + stat.executeUpdate(sql); + + } finally{ + stat.close(); + //conn.close(); + } + } + + public void updateBinaryStream(String sql, BigDecimal id, InputStream is, int size) throws SQLException, ReportSQLException, IOException { + + // cludge hack for oracle databases + if(conn.getMetaData().getDatabaseProductName().toLowerCase().contains("oracle")) { +/* updateBlob(sql,id,is,size); +*/ throw new ReportSQLException("only maria db support for this "); + + } + + PreparedStatement stat = null; + try { + stat = conn.prepareStatement(sql); + stat.setBigDecimal(2, id); + stat.setBinaryStream(1, is, size); + stat.executeUpdate(); + + } finally{ + stat.close(); + } + + } + + /*public void updateBlob(String sql, BigDecimal id, InputStream is, int size) throws SQLException, ReportSQLException, IOException { + PreparedStatement stat = null; + OutputStream out = null; + BLOB blob = null; + try { + stat = conn.prepareStatement(sql); + blob = BLOB.createTemporary(conn,false, BLOB.DURATION_SESSION); + out = blob.getBinaryOutputStream(); + + int read; + while((read = is.read()) != -1) { + out.write(read); + } + out.flush(); + + stat.setBigDecimal(2, id); + stat.setBlob(1, blob); + stat.executeUpdate(); + + } + catch (SQLException sqL) { + sqL.printStackTrace(); + } + finally{ + out.close(); + stat.close(); + } + + }*/ + + + public void insertOrUpdateWithPrepared(String sql, List<Object> params, List<Integer> types) throws SQLException, ReportSQLException { + + PreparedStatement stat = null; + try{ + //conn = getConnection(); + stat = conn.prepareStatement(sql); + conn.getMetaData(); + int i2; + int paramLength = params.size(); + for(int i = 0 ; i< paramLength ; i++) { + i2 = i+1; + Object param = params.get(i); + int type = types.get(i); + + if(param.equals("NULL")) { + stat.setNull(i2, type); + } + else if(type == Types.VARCHAR) { + stat.setString(i2, (String)param); + } + else if(type == Types.INTEGER) { + stat.setInt(i2, (Integer)param); + } + else if(type == Types.NUMERIC) { + stat.setLong(i2, (Long)param); + } + else if(type == Types.DOUBLE) { + stat.setDouble(i2, (Double)param); + } + else if(type == Types.DATE) { + stat.setDate(i2, (java.sql.Date)param); + } + else if(type == Types.TIMESTAMP) { + stat.setTimestamp(i2, (java.sql.Timestamp)param); + } + else if(type == Types.BIGINT) { + stat.setBigDecimal(i2, (BigDecimal)param); + } + else + throw new SQLException("Unidentified Object; Please contact admin and have this method updated with the current object type"); + + } + + stat.executeUpdate(); + + } finally{ + stat.close(); + //conn.close(); + } + } + + + public Object getSingleResult(String sql, String fieldname) throws SQLException, ReportSQLException{ + + Statement stat = null; + ResultSet rs = null; + Object o=null; + try{ + //conn = getConnection(); + stat = conn.createStatement(); + rs = stat.executeQuery(sql); + + while (rs.next()) { + o = rs.getObject(fieldname); + } + } + catch(SQLException sqlE){ + sqlE.printStackTrace(); + } + + finally{ + if(rs!=null) + rs.close(); + if(stat!=null) + stat.close(); + //conn.close(); + } + return o; + } + + public InputStream getDBStream(String sql, String fieldname) throws SQLException, ReportSQLException, IOException{ + + // cludge hack for oracle databases + if(conn.getMetaData().getDatabaseProductName().toLowerCase().contains("oracle")) { + /*return getDBBlob(sql,fieldname);*/ + throw new ReportSQLException("only maria db support for this "); + + } + + + Statement stat = null; + ResultSet rs = null; + InputStream o=null; + try{ + //conn = getConnection(); + stat = conn.createStatement(); + rs = stat.executeQuery(sql); + + while (rs.next()) { + o = rs.getBinaryStream(fieldname); + } + } + catch(SQLException sqlE){ + sqlE.printStackTrace(); + } + + finally{ + if(rs!=null) + rs.close(); + if(stat!=null) + stat.close(); + //conn.close(); + } + return o; + } + + /*public InputStream getDBBlob(String sql, String fieldname) throws SQLException, ReportSQLException, IOException{ + + + Statement stat = null; + ResultSet rs = null; + BLOB blob=null; + ByteArrayInputStream in = null; + try{ + stat = conn.createStatement(); + rs = stat.executeQuery(sql); + + if (rs.next()) { + blob = ((OracleResultSet) rs).getBLOB(fieldname); + in = new ByteArrayInputStream(blob.getBytes(1,(int)blob.length())); + + } + } + catch(SQLException sqlE){ + sqlE.printStackTrace(); + } + + finally{ + if(rs!=null) + rs.close(); + if(stat!=null) + stat.close(); + //conn.close(); + } + return in; + } + */ + + public void getAndExecute(String sql, Executor executor) throws SQLException, ReportSQLException{ + //Connection conn = getConnection(); + Statement stat = conn.createStatement(); + ResultSet rs = stat.executeQuery(sql); + + + while (rs.next()) { + executor.execute(rs); + } + + if(rs!=null) + rs.close(); + if(stat!=null) + stat.close(); + //conn.close(); + } + + interface Executor{ + public void execute(ResultSet rs) throws SQLException; + } + + + + + + + public static Date trunc_hour(Date v_date) { + + Calendar calendar = Calendar.getInstance(); + calendar.setTime(v_date); + calendar.set(Calendar.MILLISECOND, 0); + calendar.set(Calendar.SECOND, 0); + calendar.set(Calendar.MINUTE, 0); + return calendar.getTime(); + } + + public static Date add_hours(Date v_date, int i) { + + Calendar cal = Calendar.getInstance(); + cal.setTime(v_date); + cal.add(Calendar.HOUR, i); + return cal.getTime(); + } + + public static Date add_months(Date v_date, int i) { + + Calendar cal = Calendar.getInstance(); + cal.setTime(v_date); + cal.add(Calendar.MONTH, i); + return cal.getTime(); + } + + public static Date add_days(Date v_date, int i) { + + Calendar cal = Calendar.getInstance(); + cal.setTime(v_date); + cal.add(Calendar.DATE, i); + return cal.getTime(); + } + + public static Date to_date(String input, String format) { + + Date date = null; + try { + date = new SimpleDateFormat(format, Locale.ENGLISH).parse(input); + } catch (Exception e) { + } + return date; + } + + public static String to_date_str(Date input, String format) { + + String date = null; + try { + date = new SimpleDateFormat(format, Locale.ENGLISH).format(input); + } catch (Exception e) { + } + return date; + } + + public static String[] cr_dissecturl(String formfields, String delimiter){ + if(formfields == null || formfields.isEmpty()) + return new String[]{}; + return formfields.split("&"); + } +} diff --git a/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendEmail.java b/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendEmail.java new file mode 100644 index 00000000..6f51cee4 --- /dev/null +++ b/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendEmail.java @@ -0,0 +1,415 @@ +/*- + * ================================================================================ + * 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.io.File; +import java.io.FileInputStream; +import java.io.FileNotFoundException; +import java.io.FileOutputStream; +import java.io.IOException; +import java.io.InputStream; +import java.math.BigDecimal; +import java.net.HttpURLConnection; +import java.net.URL; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Types; +import java.util.ArrayList; +import java.util.Calendar; +import java.util.List; + +import org.openecomp.portalsdk.analytics.error.ReportSQLException; +import org.openecomp.portalsdk.analytics.scheduler.SchedulerUtil.Executor; +import org.openecomp.portalsdk.analytics.system.Globals; + + + +public class SendEmail { + +SchedulerUtil schedulerUtil; + + public SendEmail() { + + } + + + public void sendEmail( String p_mail_server, String p_sender, String p_subject, String p_mail_text, String p_url, int p_file_type, int p_schedule_id, int p_time_interval, boolean p_send_attachment, int connectionTimeout) throws SQLException, ReportSQLException{ + + String allEmailAddr = ""; + final List<String> emailArr = new ArrayList<String>(); + //int count1 = 0; + String schedular_email; + + + schedular_email = (String) schedulerUtil.getSingleResult("select email from fn_user au, cr_report_schedule crs where CRS.SCHED_USER_ID = AU.USER_ID and CRS.SCHEDULE_ID = "+ p_schedule_id, "email"); + + + String sql=Globals.getSchedulerUserEmails().replace("[p_schedule_id]", p_schedule_id+""); + schedulerUtil.getAndExecute(sql, new Executor() { + + @Override + public void execute(ResultSet rs) throws SQLException { + + emailArr.add(rs.getString("email")); + // count1 = count1 + 1 + } + + }); + + if (!p_send_attachment) { + http_to_blob(p_url, p_file_type, p_schedule_id, connectionTimeout); + } + + int i = 0; + for (String email : emailArr) { + /* If the email address is invalid ignore that email address */ + if (email.contains("@")) { + + if (i == 0) + allEmailAddr = email; + else + allEmailAddr += ',' + email; + + i++; + } + } + + /*List<MailAttachment> mailAttachments = null; + + if (p_file_type > 1 && p_send_attachment) { + mailAttachments = add_attachment(p_url, p_file_type, p_schedule_id, connectionTimeout); + } + AppUtils.notifyWithAttachments(p_mail_text, emailArr.toArray(new String[emailArr.size()]), p_sender, p_subject, new String[] { schedular_email }, null, mailAttachments, true); + */ + } + + class HistRec { + + String file_blob; + BigDecimal rep_id; + BigDecimal hist_id; + String file_name; + int sched_user_id; + String recurrence; + int file_size = 0; + String raptor_url; + int schedule_id; + int file_type_id; + int user_id; + String deleted_yn; + } + + + private HistRec http_to_blob(String p_url, int v_file_type, int p_schedule_id, int connectionTimeout) throws SQLException , ReportSQLException{ + + + final HistRec v_hist_rec = initializeVHistoryRecord(p_url, v_file_type, p_schedule_id); + HttpURLConnection con = null; + try { + URL url = new URL(p_url); + con = (HttpURLConnection) url.openConnection(); + con.setConnectTimeout(connectionTimeout*1000); + con.setRequestMethod("GET"); + schedulerUtil.insertOrUpdate("INSERT INTO cr_filehist_log (SCHEDULE_ID, url, notes, run_time) VALUES ("+ p_schedule_id +",'" + p_url+ "','http_to_blob: Initiated HTTP request', " + Globals.getCurrentDateString() + " )"); + int responseCode = con.getResponseCode(); + String outputFolder = Globals.getProjectFolder() + java.io.File.separator + Globals.getOutputFolder(); + String fileName = v_hist_rec.file_name; + createFile(con, outputFolder, fileName); + + File readFile = new File(outputFolder + java.io.File.separator + fileName); + // need to revist this conversion; may not be safe for large file sizes + v_hist_rec.file_size = (int)readFile.length(); + + schedulerUtil.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + p_schedule_id + ",'" + p_url +"', 'http_to_blob: http response recieved. Code " + responseCode + "', " + Globals.getCurrentDateString() + " )"); + + //v_hist_rec.file_blob = response.toString(); + //v_hist_rec.file_size = v_hist_rec.file_blob.length(); + + List<Object> params = new ArrayList<Object>(); + List<Integer> types = new ArrayList<Integer>(); + prepareHisRecUpdate(v_hist_rec, params, types); + + schedulerUtil + .insertOrUpdateWithPrepared("INSERT INTO cr_report_file_history(HIST_ID, SCHED_USER_ID, SCHEDULE_ID, USER_ID, REP_ID, RUN_DATE, RECURRENCE, FILE_TYPE_ID, FILE_NAME, FILE_SIZE, RAPTOR_URL, ERROR_YN, ERROR_CODE, DELETED_YN, DELETED_BY)" + + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", + params, types + ); + + FileInputStream fileStream = new FileInputStream(readFile); + schedulerUtil.updateBinaryStream("update cr_report_file_history set file_blob = ? where hist_id = ?", v_hist_rec.hist_id, fileStream, v_hist_rec.file_size); + fileStream.close(); + + String userAddRecSql = + Globals.getSchedulerUserEmails().replace("[p_schedule_id]", p_schedule_id+""); + + schedulerUtil.getAndExecute(userAddRecSql, new Executor() { + + @Override + public void execute(ResultSet rs) throws SQLException { + + try { + schedulerUtil.insertOrUpdate("INSERT INTO CR_HIST_USER_MAP (HIST_ID, USER_ID) values ( " + v_hist_rec.hist_id + "," + rs.getInt("user_id") + ")"); + } catch (ReportSQLException e) { + throw new SQLException(e.getMessage()); + } + } + + }); + + schedulerUtil.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + p_schedule_id + ",'" + p_url + "','Success: http_to_blob', " + Globals.getCurrentDateString() + " )"); + + + } catch (Exception e) { + schedulerUtil.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + p_schedule_id + ",'" + p_url + "', 'Failure: http_to_blob : Exception" + e.getMessage() +"', " + Globals.getCurrentDateString() + " )"); + e.printStackTrace(); + } finally { + if(con != null) + con.disconnect(); + } + return v_hist_rec; + + } + + + protected void prepareHisRecUpdate(final HistRec v_hist_rec, List<Object> params, + List<Integer> types) { + params.add( v_hist_rec.hist_id); + types.add(Types.BIGINT); + params.add( v_hist_rec.sched_user_id); + types.add(Types.INTEGER); + params.add( v_hist_rec.schedule_id); + types.add(Types.INTEGER); + params.add( v_hist_rec.user_id); + types.add(Types.INTEGER); + params.add( v_hist_rec.rep_id); + types.add(Types.BIGINT); + params.add( new java.sql.Date(Calendar.getInstance().getTime().getTime())); + types.add(Types.DATE); + params.add( v_hist_rec.recurrence); + types.add(Types.VARCHAR); + params.add( v_hist_rec.file_type_id); + types.add(Types.INTEGER); + params.add( v_hist_rec.file_name); + types.add(Types.VARCHAR); + params.add( v_hist_rec.file_size); + types.add(Types.INTEGER); + params.add( v_hist_rec.raptor_url); + types.add(Types.VARCHAR); + params.add( "N"); + types.add(Types.VARCHAR); + params.add( "NULL"); + types.add(Types.INTEGER); + params.add( v_hist_rec.deleted_yn); + types.add(Types.VARCHAR); + params.add(v_hist_rec.sched_user_id ); + types.add(Types.INTEGER); + } + + + protected HistRec initializeVHistoryRecord(String p_url, int v_file_type, + int p_schedule_id) throws SQLException, + ReportSQLException { + + final HistRec v_hist_rec = new HistRec(); + + v_hist_rec.rep_id = (BigDecimal) schedulerUtil.getSingleResult("SELECT rep_id FROM cr_report_schedule WHERE schedule_id =" + p_schedule_id, "rep_id"); + + Object sequenceId = schedulerUtil.getSingleResult(Globals.getSequenceNextVal().replace("[sequenceName]", "seq_cr_report_file_history"),"ID"); + + if(sequenceId instanceof Long) + v_hist_rec.hist_id = new BigDecimal((Long)sequenceId); + else if(sequenceId instanceof BigDecimal) + v_hist_rec.hist_id = (BigDecimal)sequenceId; + + v_hist_rec.file_name = (String) schedulerUtil.getSingleResult("select translate(title||to_char( "+ Globals.getCurrentDateString() + ",'MM-dd-yyyyHH24:mm:ss'), " + + "'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'||'():;.-`~^\\|'||chr(34)||chr(39)||chr(9)||' ', " + + "'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')|| "+ v_hist_rec.hist_id +" as title FROM cr_report WHERE rep_id = "+v_hist_rec.rep_id, "title"); + + + class File { + + String file_name; + String file_ext; + } + final File file = new File(); + schedulerUtil.getAndExecute("select template_file from cr_report_template_map where report_id = " + v_hist_rec.rep_id, new Executor() { + + @Override + public void execute(ResultSet rs) throws SQLException { + + file.file_name = rs.getString("template_file"); + file.file_ext = file.file_name.substring(file.file_name.indexOf('.')); + + } + }); + + if (v_file_type == 2) { + v_hist_rec.file_name = v_hist_rec.file_name + ".pdf"; + + } else if (v_file_type == 4) { + v_hist_rec.file_name = v_hist_rec.file_name + ".xls"; + + } else if (v_file_type == 5) { + if (file.file_name != null && file.file_ext.length() > 0) { + v_hist_rec.file_name = v_hist_rec.file_name + file.file_ext; + } else { + v_hist_rec.file_name = v_hist_rec.file_name + ".xlsx"; + } + } else if (v_file_type == 3) { + v_hist_rec.file_name = v_hist_rec.file_name + ".csv"; + + } + + + schedulerUtil.getAndExecute("select sched_user_id, rep_id, recurrence from cr_report_schedule where schedule_id="+p_schedule_id, new Executor() { + + @Override + public void execute(ResultSet rs) throws SQLException { + v_hist_rec.sched_user_id = rs.getInt("sched_user_id"); + v_hist_rec.rep_id = rs.getBigDecimal("rep_id"); + v_hist_rec.recurrence = rs.getString("recurrence"); + } + }); + + + v_hist_rec.file_size = 0; + v_hist_rec.raptor_url = p_url; + v_hist_rec.schedule_id = p_schedule_id; + v_hist_rec.file_type_id = v_file_type; + v_hist_rec.user_id = v_hist_rec.sched_user_id; + v_hist_rec.deleted_yn = "N"; + + return v_hist_rec; + } + + + + /*private List<MailAttachment> add_attachment(String p_url, int v_file_type, int p_schedule_id, int connectionTimeout) throws SQLException,ReportSQLException{ + + + List<MailAttachment> mailAttachmentList = new ArrayList<MailAttachment>(); + final HistRec vHistRec = initializeVHistoryRecord(p_url, v_file_type, p_schedule_id); + + /* + refer to http_to_blob for more details + + String v_content_type; + String v_content_disposition; + int transfer_timeout_limit = 1800; + String v_title; + + if (v_file_type == 2) { + v_content_type = "application/pdf"; + v_content_disposition ="inline; filename=\""+v_title+".pdf\""; + + }else if(v_file_type == 4){ + v_content_type = "application/excel"; + v_content_disposition ="inline; filename=\""+v_title+".xls\""; + + }else if(v_file_type == 5){ + v_content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; + + if (file.file_ext != null && file.file_ext.length() > 0) { + v_content_disposition ="inline; filename=\""+v_title+ file.file_ext+"\""; + if (".xlsm".equals(file.file_ext)) { + v_content_type = "application/vnd.ms-excel.sheet.macroEnabled.12"; + } + } else { + v_content_disposition ="inline; filename=\""+v_title+".xlsx\""; + }; + }else if(v_file_type == 3){ + v_content_type = "application/csv"; + v_content_disposition ="inline; filename=\""+v_title+".csv\""; + + } + * ... / + HttpURLConnection con = null; + try { + URL url = new URL(p_url); + con = (HttpURLConnection) url.openConnection(); + con.setConnectTimeout(connectionTimeout*1000); + con.setRequestMethod("GET"); + schedulerUtil.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + p_schedule_id + ",'"+ p_url +"', 'Success: http request began.', " + Globals.getCurrentDateString() + " )"); + int responseCode = con.getResponseCode(); + + String outputFolder = Globals.getProjectFolder() + java.io.File.separator + Globals.getOutputFolder(); + String fileName = vHistRec.file_name; + createFile(con, outputFolder, fileName); + + MailAttachment mailAttachment = new MailAttachment(); + mailAttachment.setAttachmentType(MailAttachment.FILE_ATTACHMENT); + mailAttachment.setFilePathName(outputFolder); + mailAttachment.setFileName(fileName); + mailAttachmentList.add(mailAttachment); + + schedulerUtil.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + p_schedule_id + ",'" + p_url +"', 'Success: http response recieved. Code " + responseCode + "', " + Globals.getCurrentDateString() + " )"); + + schedulerUtil.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + p_schedule_id + ",'" + p_url + "','Success: added attachment', " + Globals.getCurrentDateString() + " )"); + + } catch (Exception e) { + schedulerUtil.insertOrUpdate("insert into cr_schedule_activity_log (SCHEDULE_ID, url, notes, run_time) values (" + p_schedule_id + ",'" + p_url + "', 'Failure: adding attachment : Exception" + e.getMessage() +"', " + Globals.getCurrentDateString() + " )"); + e.printStackTrace(); + } finally { + if(con != null) + con.disconnect(); + } + + + return mailAttachmentList; + }*/ + + + void createFile(HttpURLConnection con, String outputFolder, String fileName) + throws IOException, FileNotFoundException { + //BufferedReader in = new BufferedReader(new InputStreamReader(con.getInputStream())); + InputStream in = con.getInputStream(); + + try { + + FileOutputStream out = new FileOutputStream(outputFolder + java.io.File.separator + fileName ); + try { + int inputLine; + + while ((inputLine = in.read()) != -1) { + out.write(inputLine); + } + out.flush(); + } + finally { + out.close(); + } + + } + finally { + in.close(); + } + } + + + public SchedulerUtil getSchedulerUtil() { + return schedulerUtil; + } + + + public void setSchedulerUtil(SchedulerUtil schedulerUtil) { + this.schedulerUtil = schedulerUtil; + } + +} diff --git a/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java b/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java new file mode 100644 index 00000000..40adbee4 --- /dev/null +++ b/ecomp-sdk/sdk-analytics/src/main/java/org/openecomp/portalsdk/analytics/scheduler/SendNotifications.java @@ -0,0 +1,460 @@ +/*- + * ================================================================================ + * 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.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; + +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; + } + +} |