From 8c9c19f430ee91b72955b7cb48d9cf5c48da22ff Mon Sep 17 00:00:00 2001 From: mravula Date: Wed, 20 May 2020 05:14:48 -0400 Subject: enabled analytics test cases and resolved conflicts Issue-ID: PORTAL-902 Change-Id: Ifff7f9329673ef464b3414e6945da57ba5d1b6bd Change-Id: If40c7e0b4ef4abbe5f1d6b3784a3b57abd20bd81 Signed-off-by: mravula --- .../portalsdk/analytics/scheduler/SendEmail.java | 351 ++++++++------------- 1 file changed, 128 insertions(+), 223 deletions(-) (limited to 'ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler') diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendEmail.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendEmail.java index 1e0f574b..191334f6 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendEmail.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendEmail.java @@ -36,9 +36,9 @@ * */ package org.onap.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; @@ -51,75 +51,59 @@ import java.sql.Types; import java.util.ArrayList; import java.util.Calendar; import java.util.List; - +import org.apache.http.client.methods.CloseableHttpResponse; +import org.apache.http.client.methods.HttpGet; +import org.apache.http.impl.client.CloseableHttpClient; +import org.apache.http.impl.client.HttpClients; import org.onap.portalsdk.analytics.error.ReportSQLException; import org.onap.portalsdk.analytics.scheduler.SchedulerUtil.Executor; import org.onap.portalsdk.analytics.system.Globals; - - - - +import org.onap.portalsdk.analytics.util.EmailUtils; +import org.onap.portalsdk.analytics.util.MailAttachment; public class SendEmail { - -SchedulerUtil schedulerUtil; - + SchedulerUtil schedulerUtil; + EmailUtils emailUtils; 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{ - + 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 Exception { String allEmailAddr = ""; final List emailArr = new ArrayList<>(); - //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+""); + 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 + emailArr.add(rs.getString("email")); } - }); - 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 mailAttachments = null; - + List mailAttachments = null; + p_send_attachment=true; 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); - */ + emailUtils.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; @@ -133,160 +117,134 @@ SchedulerUtil schedulerUtil; 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{ - - + 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 { + try { URL url = new URL(p_url); con = (HttpURLConnection) url.openConnection(); - con.setConnectTimeout(connectionTimeout*1000); + 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() + " )"); + 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(); - + 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() + " )"); List params = new ArrayList<>(); List types = new ArrayList<>(); 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)" + 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 - ); - - try(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); - }catch (Exception e){ - //throw the exception to outer block for handling it + params, types); + try (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); + } catch (Exception e) { throw e; } - - String userAddRecSql = - Globals.getSchedulerUserEmails().replace("[p_schedule_id]", p_schedule_id+""); - + 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") + ")"); + 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() + " )"); - - + 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() + " )"); + 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) + if (con != null) con.disconnect(); } return v_hist_rec; - } - - - protected void prepareHisRecUpdate(final HistRec v_hist_rec, List params, - List types) { - params.add( v_hist_rec.hist_id); + protected void prepareHisRecUpdate(final HistRec v_hist_rec, List params, List types) { + params.add(v_hist_rec.hist_id); types.add(Types.BIGINT); - params.add( v_hist_rec.sched_user_id); + params.add(v_hist_rec.sched_user_id); types.add(Types.INTEGER); - params.add( v_hist_rec.schedule_id); + params.add(v_hist_rec.schedule_id); types.add(Types.INTEGER); - params.add( v_hist_rec.user_id); + params.add(v_hist_rec.user_id); types.add(Types.INTEGER); - params.add( v_hist_rec.rep_id); + params.add(v_hist_rec.rep_id); types.add(Types.BIGINT); - params.add( new java.sql.Date(Calendar.getInstance().getTime().getTime())); + params.add(new java.sql.Date(Calendar.getInstance().getTime().getTime())); types.add(Types.DATE); - params.add( v_hist_rec.recurrence); + params.add(v_hist_rec.recurrence); types.add(Types.VARCHAR); - params.add( v_hist_rec.file_type_id); + params.add(v_hist_rec.file_type_id); types.add(Types.INTEGER); - params.add( v_hist_rec.file_name); + params.add(v_hist_rec.file_name); types.add(Types.VARCHAR); - params.add( v_hist_rec.file_size); + params.add(v_hist_rec.file_size); types.add(Types.INTEGER); - params.add( v_hist_rec.raptor_url); + params.add(v_hist_rec.raptor_url); types.add(Types.VARCHAR); - params.add( "N"); + params.add("N"); types.add(Types.VARCHAR); - params.add( "NULL"); + params.add("NULL"); types.add(Types.INTEGER); - params.add( v_hist_rec.deleted_yn); + params.add(v_hist_rec.deleted_yn); types.add(Types.VARCHAR); - params.add(v_hist_rec.sched_user_id ); + 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 { - + 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"); - - + 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('.')); - - } - }); - + 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; @@ -295,140 +253,87 @@ SchedulerUtil schedulerUtil; } } 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"); - } - }); - - + 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 add_attachment(String p_url, int v_file_type, int p_schedule_id, int connectionTimeout) throws SQLException,ReportSQLException{ - - + private List add_attachment(String p_url, int v_file_type, int p_schedule_id, int connectionTimeout) + throws SQLException, ReportSQLException,Exception { List mailAttachmentList = new ArrayList(); - 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\""; - - } - * ... / + final HistRec vHistRec = 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.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() + " )"); + 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() + " )"); - + 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() + " )"); + 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) + if (con != null) con.disconnect(); } - - return mailAttachmentList; - }*/ - - - void createFile(HttpURLConnection con, String outputFolder, String fileName) - throws IOException { - //BufferedReader in = new BufferedReader(new InputStreamReader(con.getInputStream())); - InputStream in = con.getInputStream(); - + } + public void createFile(HttpURLConnection con, String outputFolder, String fileName) throws IOException { + InputStream in = con.getInputStream(); try { - - try (FileOutputStream out = new FileOutputStream(outputFolder + java.io.File.separator + fileName )) { + try (FileOutputStream out = new FileOutputStream(outputFolder + java.io.File.separator + fileName)) { int inputLine; - while ((inputLine = in.read()) != -1) { out.write(inputLine); } out.flush(); - } catch(Exception e){ + } catch (Exception e) { throw e; } - - } - finally { + } finally { in.close(); } } - - public SchedulerUtil getSchedulerUtil() { return schedulerUtil; } - - public void setSchedulerUtil(SchedulerUtil schedulerUtil) { this.schedulerUtil = schedulerUtil; } - } -- cgit 1.2.3-korg