Fixed Sonar "Blocker Bugs"

Fixed potential "thread-Leak"

Change-Id: I7efcbd4d7b6b69ca1f234bed8d28cbb8e51e3e25
Signed-off-by: Vasyl Razinkov <vasylrazinkov@sidero.ie>
Issue-ID: PORTAL-584

2 files changed, 330 insertions, 460 deletions

diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SchedulerUtil.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SchedulerUtil.java
index 24af7e25..313f04ee 100644
--- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SchedulerUtil.java
+++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SchedulerUtil.java
@@ -33,11 +33,10 @@
  *
  * ============LICENSE_END============================================
  *
- * 
+ *
  */
 package org.onap.portalsdk.analytics.scheduler;
 
-import java.io.IOException;
 import java.io.InputStream;
 import java.math.BigDecimal;
 import java.sql.Connection;
@@ -51,338 +50,208 @@ import java.util.Calendar;
 import java.util.Date;
 import java.util.List;
 import java.util.Locale;
-
 import org.onap.portalsdk.analytics.error.ReportSQLException;
 import org.onap.portalsdk.analytics.system.DbUtils;
+import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;
 
-//import oracle.jdbc.*;
-//import oracle.sql.BLOB;
+public class SchedulerUtil {
 
+    private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(SchedulerUtil.class);
 
-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{
-			if(stat!=null)
-				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{
-    		if(stat!=null)
-    			stat.close();
-		}
-        	
+    private Connection conn = null;
+
+    public static Date trunc_hour(final 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(final Date v_date, final int i) {
+
+        Calendar cal = Calendar.getInstance();
+        cal.setTime(v_date);
+        cal.add(Calendar.HOUR, i);
+        return cal.getTime();
+    }
+
+    public static Date add_months(final Date v_date, final int i) {
+
+        Calendar cal = Calendar.getInstance();
+        cal.setTime(v_date);
+        cal.add(Calendar.MONTH, i);
+        return cal.getTime();
+    }
+
+    public static Date add_days(final Date v_date, final int i) {
+
+        Calendar cal = Calendar.getInstance();
+        cal.setTime(v_date);
+        cal.add(Calendar.DATE, i);
+        return cal.getTime();
+    }
+
+    public static Date to_date(final String input, final String format) {
+
+        Date date = null;
+        try {
+            date = new SimpleDateFormat(format, Locale.ENGLISH).parse(input);
+        } catch (final Exception e) {
+            logger.warn("Failed to Format Date", e);
+        }
+        return date;
+    }
+
+    public static String to_date_str(final Date input, final String format) {
+
+        String date = null;
+        try {
+            date = new SimpleDateFormat(format, Locale.ENGLISH).format(input);
+        } catch (final Exception e) {
+            logger.warn("Failed to Format Date", e);
+        }
+        return date;
     }
-    
-    /*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{
-			if(stat!=null)
-				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 static String[] cr_dissecturl(final String formfields, final String delimiter) {
+        if (formfields == null || formfields.isEmpty()) {
+            return new String[]{};
+        }
+        return formfields.split("&");
+    }
+
+    protected Connection getConnection() {
+        return conn;
+    }
+
+    protected void setConnection(final Connection _conn) {
+        conn = _conn;
+    }
+
+    protected Connection init() throws  ReportSQLException {
+        if (conn != null) {
+            return conn;
+        }
+        conn = DbUtils.getConnection();
+        return conn;
+    }
+
+    protected void closeConnection() throws SQLException {
+        if (conn != null) {
+            conn.close();
+        }
+    }
+
+    public void insertOrUpdate(final String sql) throws SQLException, ReportSQLException {
+
+        try (final Statement stat = conn.createStatement()) {
+            stat.executeUpdate(sql);
+        }
+    }
+
+    public void updateBinaryStream(final String sql, final BigDecimal id, final InputStream is, final int size)
+        throws SQLException, ReportSQLException {
+
+        // cludge hack for oracle databases
+        if (conn.getMetaData().getDatabaseProductName().toLowerCase().contains("oracle")) {
+            throw new ReportSQLException("only maria db support for this ");
+        }
+
+        try (final PreparedStatement stat = conn.prepareStatement(sql)) {
+            stat.setBigDecimal(2, id);
+            stat.setBinaryStream(1, is, size);
+            stat.executeUpdate();
+        }
+    }
+
+    public void insertOrUpdateWithPrepared(final String sql, final List<Object> params, final List<Integer> types)
+        throws SQLException {
+
+        try (final PreparedStatement stat = conn.prepareStatement(sql)) {
+            conn.getMetaData();
+            int i2;
+            final int paramLength = params.size();
+            for (int i = 0; i < paramLength; i++) {
+                i2 = i + 1;
+                final Object param = params.get(i);
+                final int type = types.get(i);
+
+                if ("NULL".equals(param)) {
+                    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();
+        }
+    }
+
+    public Object getSingleResult(final String sql, final String fieldName) {
+        Object o = null;
+        try (final Statement stat = conn.createStatement();
+            final ResultSet rs = stat.executeQuery(sql);) {
+            while (rs.next()) {
+                o = rs.getObject(fieldName);
+            }
+        } catch (final SQLException sqlE) {
+            logger.warn("Failed to get single result", sqlE);
+        }
+        return o;
+    }
+
+    public InputStream getDBStream(final String sql, final String fieldname) throws SQLException, ReportSQLException {
+
+        // cludge hack for oracle databases
+        if (conn.getMetaData().getDatabaseProductName().toLowerCase().contains("oracle")) {
+            throw new ReportSQLException("only maria db support for this ");
+        }
+
+        InputStream o = null;
+        try (final Statement stat = conn.createStatement();
+            final ResultSet rs = stat.executeQuery(sql);) {
+
+            while (rs.next()) {
+                o = rs.getBinaryStream(fieldname);
+            }
+        } catch (SQLException sqlE) {
+            logger.warn("Failed to get DB Stream", sqlE);
+        }
+        return o;
+    }
+
+    public void getAndExecute(final String sql, final Executor executor) throws SQLException {
+        try (final Statement stat = conn.createStatement();
+            final ResultSet rs = stat.executeQuery(sql);) {
+            while (rs.next()) {
+                executor.execute(rs);
+            }
+        } catch (SQLException sqlE) {
+            logger.warn("Failed to get DB Stream", sqlE);
+        }
+    }
+
+    interface Executor {
+
+        public void execute(ResultSet rs) throws SQLException;
     }
-	
-	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/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java
index 8b9aabc2..3c871d87 100644
--- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java
+++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/scheduler/SendNotifications.java
@@ -38,21 +38,20 @@
 package org.onap.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.onap.portalsdk.analytics.error.RaptorException;
 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.core.logging.logic.EELFLoggerDelegate;
 
 public class SendNotifications {
-	
+
+	private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(SendNotifications.class);
 	SchedulerUtil schedulerUtil;
 	public SendEmail sendEmail;
 	
@@ -63,7 +62,6 @@ public class SendNotifications {
 		init();
 	}
 
-		
 	public  void deInit() throws SQLException {
 		schedulerUtil.closeConnection();
 	}
@@ -71,133 +69,136 @@ public class SendNotifications {
 	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();
-
-	}
+    public void send_notification(final String p_mail_server, final String p_sender, final String p_system_name,
+        final String p_system_url, final int connectionTimeout) throws RaptorException, Exception {
+
+        logger.info(p_mail_server + " " + p_sender + " " + p_system_name + " " + p_system_url);
+        final int p_time_interval = Globals.getSchedulerInterval();
+        int v_num_recs = 0;
+        BigDecimal v_id = null;
+        String v_r_action = "report.download.pdf";
+
+        final String CNotificationsql = Globals.getAvailableSchedules()
+            .replace("[currentDate]", Globals.getCurrentDateString());
+
+        try (final ResultSet rs = schedulerUtil.getConnection().createStatement().executeQuery(CNotificationsql);) {
+            while (rs.next()) {
+                final int v_schedule_id = rs.getInt("schedule_id");
+                final int offset = get_report_sched_offset(rs.getInt("rep_id"), v_schedule_id);
+
+                if (offset >= p_time_interval) {
+                    continue;
+                }
+
+                final Date v_touch_date = (Date) schedulerUtil.getSingleResult(
+                    "select touch_date from cr_report_email_sent_log where schedule_id = " + v_schedule_id
+                        + " and log_id = (select max(log_id) from cr_report_email_sent_log where schedule_id = "
+                        + v_schedule_id + ")", "touch_date");
+                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"))) {
+                    final String v_gen_key = ("Z" + UUID.randomUUID()).toString()
+                        .substring(0, 24); // 25 character string
+                    final 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() + " )");
+
+                    final 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";
+                    }
+
+                    final String v_formfields;
+                    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"));
+                    }
+
+                    final String v_url =
+                        p_system_url + "&r_action=" + v_r_action + "&log_id=" + v_id + "&user_id=" + rs
+                            .getString("user_id")
+                            + "&pdfAttachmentKey=" + v_gen_key + "&download_limit=" + rs.getInt("max_row")
+                            + v_formfields;
+
+                    final boolean v_attach_email_yn = shouldSendAttachmentInEmail(v_schedule_id);
+
+                    final String v_email_msg =
+                        "<html><body><p><b><u><i>" + p_system_name + " System Notification</i></u></b></p>"
+                            + "<p>Report <b>" + rs.getString("title")
+                            + "</b> is available for viewing.</p><p>You can view the report if it is attached. </br>"
+                            + "If it is not attached, or you have problem to open it, you can log into Business Direct and run the report.</p>"
+                            + "</body></html>";
+
+                    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() + " )");
+                }
+            }
+        } catch (final SQLException e) {
+            logger.warn("Failed to open connection", e);
+        }
+    }
 
 	private boolean shouldSendAttachmentInEmail(int v_schedule_id) throws SQLException, ReportSQLException {