We need to write A one java program along with properties file then one query is required to fetch the record from DB and then make in html form and send to the people..
package emailapp1;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.TimeZone;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
public class BCLoadAndSchedulerMonitorEmail{
public static void main(String args[]) throws AddressException {
BCLoadAndSchedulerMonitorEmail monitorEmail = new BCLoadAndSchedulerMonitorEmail();
//List<JobStatus> jobStatusPortalList = monitorEmail.getJobStatusDisplayofPortalDB();
List<JobStatus> jobStatusPortalReportingList = monitorEmail.getJobStatusDisplayofPortalReportingDB();
//jobStatusPortalReportingList.addAll(jobStatusPortalList);
List<JobStatus> failuredList = new ArrayList<JobStatus>();
for (JobStatus jobStatus : jobStatusPortalReportingList) {
if(jobStatus!=null && jobStatus.getMERCHANT_NBR()!=null && !jobStatus.getMERCHANT_NBR().equalsIgnoreCase(""))
{
// if(jobStatus.getStatus().equalsIgnoreCase("FAILED") || jobStatus.getStatus().equalsIgnoreCase("NOT EXECUTED"))
failuredList.add(jobStatus);
}
}
// Comparator<JobStatus> order = new Comparator<JobStatus>() {
// String status1 = "";
// String status2 = "";
// public int compare(JobStatus jobStatus1, JobStatus jobStatus2) {
// status1 = jobStatus1.getStatus().trim();
// status2 = jobStatus2.getStatus().trim();
// return status1.compareTo(status2);
// }};
// Collections.sort(failuredList, order);
if(failuredList.size()>0)
{
String emailBodyContent = BCLoadAndSchedulerMonitorEmail.createHtmlTemplate(failuredList);
//System.out.println(emailBodyContent);
System.out.println("before sending mail");
BCLoadAndSchedulerMonitorEmail.sendEmail(emailBodyContent);
}
//System.out.println(BCLoadAndSchedulerMonitorEmail.createHtmlTemplate(failuredList));
}
private static boolean sendEmail(String message) {
Session mailSession = getMailSession();
//code to prepare recipient email addresses
List<InternetAddress> recipientAddresses = null;
InternetAddress[] recipientArray = null;
String[] emailList =null;
try {
String email = BCLoadAndSchedulerMonitorEmail.loadPropvalues("BC_Email_List");
if(email!=null && email.length()>0)
{
emailList=email.split(",");
}
recipientAddresses = createRecipients(emailList);
recipientArray = new InternetAddress[recipientAddresses.size()];
recipientAddresses.toArray(recipientArray);
} catch (AddressException ex) {
ex.printStackTrace();
}
try {
// create a message
javax.mail.Message msg = new MimeMessage(mailSession);
msg.setFrom(new InternetAddress(BCLoadAndSchedulerMonitorEmail.loadPropvalues("EMAILFROMADDRESS")));
msg.setRecipients(javax.mail.Message.RecipientType.TO, recipientArray);
// subject
msg.setSubject(BCLoadAndSchedulerMonitorEmail.loadPropvalues("EMAIL_SUBJECT_FAILED"));
// date sent
msg.setSentDate(new Date());
if (message != null) {
if (message.startsWith("<HTML") || message.startsWith("<html")) {
msg.setContent(message, "text/html");
} else {
msg.setText(message);
}
}
// send email
System.out.println("in mail method ");
Transport.send(msg);
//System.out.println(message);
} catch (Throwable e) {
e.printStackTrace();
System.out.println(e);
}
return true;
}
private static Session getMailSession() {
Properties p = new Properties();
p.setProperty("mail.smtp.host", BCLoadAndSchedulerMonitorEmail.loadPropvalues("SMTP_HOST_NAME"));
p.setProperty("mail.smtp.port", BCLoadAndSchedulerMonitorEmail.loadPropvalues("SMTP_PORT"));
Session session = Session.getInstance(p);
return session;
}
/**
* If any of the emails are invalid, it throws exception
*
* @param recipients list of Strings representing valid emails
* @return list of InternetAddress objects
* @throws AddressException if any invalid email addresses
*/
private static List<InternetAddress> createRecipients(String[] recipients)
throws AddressException {
ArrayList<InternetAddress> addresses = new ArrayList<InternetAddress>(
recipients.length);
String email = null;
for (int i = 0; i < recipients.length; i++) {
email = recipients[i];
if (validateEmail(email)) {
addresses.add(new InternetAddress(email));
}
}
return addresses;
}
/**
* Method to check whether the given email is valid or not
* @param address
* @return true if given email is valid or false if not valid
*/
private static boolean validateEmail(String address) {
try {
new InternetAddress(address);
} catch (AddressException ex) {
return false;
}
return true;
}
/**
* Method to return connection object
* @return Connection
*/
public static Connection getConnection(String url) {
Connection connection = null;
try {
Class.forName(BCLoadAndSchedulerMonitorEmail.loadPropvalues("JDBC_DRIVER_NAME"));
connection = DriverManager.getConnection(url,
BCLoadAndSchedulerMonitorEmail.loadPropvalues("JDBC_USERNAME"),
BCLoadAndSchedulerMonitorEmail.loadPropvalues("JDBC_PASSWORD"));
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return connection;
}
/**
* Method to return Load Track Details
* @return List
*/
public List<JobStatus> getJobStatusDisplayofPortalDB()
{
PreparedStatement preparedStatement =null;
ResultSet resultSet = null;
JobStatus jobStatus =null;
List<JobStatus> jobStatusList = new ArrayList<JobStatus>();
String isDayLightSavingTime = isDayLightSavingTime();
String loadTrackDetailsSql ="SELECT MERCHANT_NBR,(SELECT b.en_PROGRAM_NAME FROM MERCHANT A,PROGRAM B WHERE A.PROGRAM_ID=B.PROGRAM_ID and a.merchant_nbr=c.merchant_nbr) merchant_name, PRODUCT_CD,APPROVED,DECLINE ,PENDING,VERID,SUM(APPROVED+DECLINE +PENDING+VERID) TOTAL from (SELECT MERCHANT_NBR,PRODUCT_CD, SUM (NVL(APPROVED,0)) APPROVED, SUM (NVL(DECLINE,0)) DECLINE, SUM (NVL(PENDING,0)) PENDING, SUM (nvl(VERID,0)) VERID from ( SELECT MERCHANT_NBR,PRODUCT_CD,SUM(NVL(CNT,0)) APPROVED, NULL DECLINE, NULL PENDING , NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='APPROVED' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,SUM(NVL(CNT,0)) DECLINE , NULL PENDING , NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ORDER BY 1 DESC ) WHERE RESULTCODE='DECLINE' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,NULL DECLINE ,SUM(NVL(CNT,0)) PENDING, NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='PENDING' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,NULL DECLINE ,NULL PENDING,SUM(NVL(CNT,0)) VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='VERID' GROUP BY MERCHANT_NBR,product_cd ) GROUP BY MERCHANT_NBR,PRODUCT_CD ) c group by MERCHANT_NBR,PRODUCT_CD,APPROVED,DECLINE ,PENDING,VERID";
Connection connection = getConnection(BCLoadAndSchedulerMonitorEmail.loadPropvalues("JDBC_URL_PORTAL"));
try {
preparedStatement = connection.prepareStatement(loadTrackDetailsSql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next())
{
jobStatus.setMERCHANT_NBR(resultSet.getString("MERCHANT_NBR"));
jobStatus.setMERCHANT_NAME(resultSet.getString("MERCHANT_NAME"));
jobStatus.setPRODUCT_CD(resultSet.getString("PRODUCT_CD"));
jobStatus.setAPPROVED(resultSet.getString("APPROVED"));
jobStatus.setDECLINE(resultSet.getString("DECLINE"));
jobStatus.setPENDING(resultSet.getString("PENDING"));
jobStatus.setVERID(resultSet.getString("VERID"));
jobStatusList.add(jobStatus);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally{
BCLoadAndSchedulerMonitorEmail.closeConnections(resultSet, preparedStatement, connection);
}
return jobStatusList;
}
/**
* Method to get the scheduler job details from portal Reporting
* @return List<JobStatus>
*/
public List<JobStatus> getJobStatusDisplayofPortalReportingDB()
{
List<JobStatus> jobStatusList = new ArrayList<JobStatus>();
PreparedStatement preparedStatement =null;
ResultSet resultSet = null;
JobStatus jobStatus =null;
String isDayLightSavingTime = isDayLightSavingTime();
String schedulerJobDetailsSql ="SELECT MERCHANT_NBR,(SELECT b.en_PROGRAM_NAME FROM MERCHANT A,PROGRAM B WHERE A.PROGRAM_ID=B.PROGRAM_ID and a.merchant_nbr=c.merchant_nbr) merchant_name, PRODUCT_CD,APPROVED,DECLINE ,PENDING,VERID,SUM(APPROVED+DECLINE +PENDING+VERID) TOTAL from (SELECT MERCHANT_NBR,PRODUCT_CD, SUM (NVL(APPROVED,0)) APPROVED, SUM (NVL(DECLINE,0)) DECLINE, SUM (NVL(PENDING,0)) PENDING, SUM (nvl(VERID,0)) VERID from ( SELECT MERCHANT_NBR,PRODUCT_CD,SUM(NVL(CNT,0)) APPROVED, NULL DECLINE, NULL PENDING , NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='APPROVED' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,SUM(NVL(CNT,0)) DECLINE , NULL PENDING , NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ORDER BY 1 DESC ) WHERE RESULTCODE='DECLINE' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,NULL DECLINE ,SUM(NVL(CNT,0)) PENDING, NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='PENDING' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,NULL DECLINE ,NULL PENDING,SUM(NVL(CNT,0)) VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='VERID' GROUP BY MERCHANT_NBR,product_cd ) GROUP BY MERCHANT_NBR,PRODUCT_CD ) c group by MERCHANT_NBR,PRODUCT_CD,APPROVED,DECLINE ,PENDING,VERID";
Connection connection = getConnection(BCLoadAndSchedulerMonitorEmail.loadPropvalues("JDBC_URL_PORTAL_REPORTING"));
try {
preparedStatement = connection.prepareStatement(schedulerJobDetailsSql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next())
{
jobStatus = new JobStatus();
jobStatus.setMERCHANT_NBR(resultSet.getString("MERCHANT_NBR"));
jobStatus.setMERCHANT_NAME(resultSet.getString("MERCHANT_NAME"));
jobStatus.setPRODUCT_CD(resultSet.getString("PRODUCT_CD"));
jobStatus.setAPPROVED(resultSet.getString("APPROVED"));
jobStatus.setDECLINE(resultSet.getString("DECLINE"));
jobStatus.setPENDING(resultSet.getString("PENDING"));
jobStatus.setVERID(resultSet.getString("VERID"));
jobStatusList.add(jobStatus);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally{
BCLoadAndSchedulerMonitorEmail.closeConnections(resultSet, preparedStatement, connection);
}
return jobStatusList;
}
/**
* JobStatus Bean class
* @author uday.sariki
*
*/
class JobStatus
{
private String MERCHANT_NBR;
private String MERCHANT_NAME;
private String PRODUCT_CD;
private String APPROVED;
private String DECLINE;
private String PENDING;
private String VERID;
public void setMERCHANT_NBR(String MERCHANT_NBR) {
this.MERCHANT_NBR = MERCHANT_NBR;
}
public String getMERCHANT_NBR() {
return MERCHANT_NBR;
}
public void setMERCHANT_NAME(String MERCHANT_NAME) {
this.MERCHANT_NAME = MERCHANT_NAME;
}
public String getMERCHANT_NAME() {
return MERCHANT_NAME;
}
public void setPRODUCT_CD(String PRODUCT_CD) {
this.PRODUCT_CD = PRODUCT_CD;
}
public String getPRODUCT_CD() {
return PRODUCT_CD;
}
public void setAPPROVED(String APPROVED) {
this.APPROVED = APPROVED;
}
public String getAPPROVED() {
return APPROVED;
}
public void setDECLINE(String DECLINE) {
this.DECLINE = DECLINE;
}
public String getDECLINE() {
return DECLINE;
}
public void setPENDING(String PENDING) {
this.PENDING = PENDING;
}
public String getPENDING() {
return PENDING;
}
public void setVERID(String VERID) {
this.VERID = VERID;
}
public String getVERID() {
return VERID;
}
}
private static void closeConnections(ResultSet resultSet,
PreparedStatement preparedStatement, Connection connection) {
try {
if (resultSet != null) {
resultSet.close();
}
if(preparedStatement!=null)
{
preparedStatement.close();
}
if(preparedStatement!=null)
{
preparedStatement.close();
}
if(connection!=null)
{
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static String loadPropvalues(String propValue)
{
//File runtimeFile = new File("/appdata/webpos/dbmonitoring/dbMonitoring.properties");
//File runtimeFile = new File("/export/home/ecomadm/BCDBjobs/dbMonitoring.properties");
File runtimeFile = new File("D://GE GDC Working Folder/ashish.negi/EtailProject/EmailApplication/EmailApp1/dbMonitoring.properties");
java.util.Properties runtimeProps = new java.util.Properties();
if (runtimeFile.exists())
{
FileInputStream propFile=null;
try
{
propFile = new FileInputStream(runtimeFile);
runtimeProps.load(propFile);
propValue = runtimeProps.getProperty(propValue);
propFile.close();
}
catch (IOException ioe)
{
ioe.printStackTrace();
}
finally
{
try
{
if (propFile != null)
{
propFile.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
return propValue;
}
private static String createHtmlTemplate(List<JobStatus> portalReportingDBList)
{
StringBuffer buf = new StringBuffer();
//Html main open and close tags
String htmlOpenTags = "<html lang='en-US'><head><meta charset='UTF-8'><meta name='viewport' content='width=device-width, initial-scale=1.0'>" +
"<title>BC Load and Scheduler Status</title></head><body><table border='0' cellpadding='2' cellspacing='2'>";
String htmlCloseTags="</table></body></html>";
//Html PortalReporting open and close tags
String htmlPortalReportingOpenTags = "<tr> <td align='center' style='padding-top:30px;'><table border='1' cellpadding='2' cellspacing='0'>" +
"<tr style='background-color:black;'>" +
"<td colspan='7' align='center' style='font-size:24px;font-weight:bold;color:white;'>Etail "+BCLoadAndSchedulerMonitorEmail.getCurrentDateAndTime()+"</td></tr>" +
"<tr style='background-color:B0B0B0;'><td align = 'center' style='font-size:18px;font-weight:bold;'>Merchant Number</td><td align = 'center' style='font-size:18px;font-weight:bold;'>" +
"Status</td><td align = 'center' style='font-size:18px;font-weight:bold;'>Merchant Name</td><td align = 'center' style='font-size:18px;font-weight:bold;'>PD/GC</td>" +
"<td align = 'center' style='font-size:18px;font-weight:bold;'>Approved</td><td align = 'center' style='font-size:18px;font-weight:bold;'>Stage Count</td> " +
"<td align = 'center' style='font-size:18px;font-weight:bold;'>Declined</td></tr>";
String htmlPortalReportingCloseTags = "</table></td></tr>";
//Html Portal open and close tags
/* String htmlPortalOpenTags ="<tr><td align='center'><table border='1' cellpadding='5' cellspacing='0'><tr style='background-color:black;'>" +
"<td colspan='3' align='center' style='font-size:24px;font-weight:bold;color:white;'>PORTAL SCHEDULER TRACK </td></tr>" +
"<tr style='background-color:B0B0B0;'><td align = 'center' style='font-size:18px;font-weight:bold;'>Job Name</td><td align = 'center' style='font-size:18px;font-weight:bold;'>" +
"Status</td><td align = 'center' style='font-size:18px;font-weight:bold;'>Reason</td></tr>";
String htmlPortalCloseTags = "</table></td></tr>";*/
buf.append(htmlOpenTags);
//Adding the Portal Html open tags Content first and dynamic rows.
//buf.append(htmlPortalOpenTags+BCLoadAndSchedulerMonitorEmail.prepareHtmlRowsforPortalSchema(portalDBList));
//Adding the close tags of portal schema
//buf.append(htmlPortalCloseTags);
//Adding the Portal Reporting Html open tags Content first and dynamic rows.
buf.append(htmlPortalReportingOpenTags+BCLoadAndSchedulerMonitorEmail.prepareHtmlRowsforPortalReportingSchema(portalReportingDBList));
//Adding the close tags of Portal Reporting schema
buf.append(htmlPortalReportingCloseTags);
buf.append(htmlCloseTags);
return buf.toString();
}
/**
* Method not in use
* Prepare Html row string for portal function
* @param portalDBList
* @return string
*/
private static String prepareHtmlRowsforPortalSchema(List<JobStatus> portalDBList){
StringBuffer buf = new StringBuffer();
String evenOddColor = "";
if(portalDBList!=null && portalDBList.size()>0)
{
JobStatus jobStatus = null;
for(int i=0;i<portalDBList.size();i++)
{
jobStatus = (JobStatus)portalDBList.get(i);
buf.append("<tr style='background-color:"+evenOddColor+";'><td align = 'center'>"+jobStatus.getMERCHANT_NBR()+"</td>");
if (jobStatus.getMERCHANT_NBR() != null
&& !jobStatus.getMERCHANT_NBR().equalsIgnoreCase("")
&& jobStatus.getMERCHANT_NBR().equalsIgnoreCase("FAILED")) {
buf.append("<td align = 'center' style='background-color:red;font-size:11px;'> ");
} else {
buf.append("<td align = 'center' style='background-color:SeaGreen;font-size:11px;'> ");
}
buf.append(jobStatus.getMERCHANT_NBR()+" </td><td align = 'center'>");
buf.append((jobStatus.getMERCHANT_NAME() != null)?jobStatus.getMERCHANT_NAME():" "+"</td></tr>");
if(evenOddColor.equals(""))
evenOddColor="Silver";
else
evenOddColor="";
}
}else{
buf.append(" <tr><td colspan='3' align='center' style='font-size:14px;font-weight:bold;color:red;'> NO TABLE DATA </td></tr>");
}
return buf.toString();
}
/**
* Prepare Html row string for Portal_Reporting function
* @param portalDBList
* @return string
*/
private static String prepareHtmlRowsforPortalReportingSchema(List<JobStatus> portalDBList){
StringBuffer mainBuffer = new StringBuffer();
String evenOddColor = "";
if(portalDBList!=null && portalDBList.size()>0)
{
JobStatus jobStatus = null;
for(int i=0;i<portalDBList.size();i++)
{
StringBuffer subBuffer = new StringBuffer();
jobStatus = (JobStatus)portalDBList.get(i);
subBuffer.append("<tr style='background-color:"+evenOddColor+";'><td align = 'left'>"+jobStatus.getMERCHANT_NBR() +"</td>");
subBuffer.append(jobStatus.getMERCHANT_NAME()+"</nobr></td><td align = 'center' style='font-size:11px;'>");
subBuffer.append((jobStatus.getPRODUCT_CD() != null)?jobStatus.getPRODUCT_CD():" ");
subBuffer.append("</td><td align = 'center' style='font-size:11px;'>");
subBuffer.append((jobStatus.getAPPROVED() != null)?jobStatus.getAPPROVED():" ");
subBuffer.append("</td><td align = 'center' style='font-size:11px;'>");
subBuffer.append((jobStatus.getPENDING()!= null)?jobStatus.getPENDING():" ");
subBuffer.append("</td><td align = 'center' style='font-size:11px;'>");
subBuffer.append((jobStatus.getDECLINE() != null)?jobStatus.getDECLINE():" ");
subBuffer.append("</td><td align = 'center' style='font-size:11px;'>");
subBuffer.append((jobStatus.getVERID()!= null)?jobStatus.getVERID():" ");
subBuffer.append("</td></tr>");
if(evenOddColor.equals(""))
evenOddColor="Silver";
else
evenOddColor="";
mainBuffer.append(subBuffer);
}
}/*else{
mainBuffer.append(" <tr><td colspan='7' align='center' style='font-size:18px;font-weight:bold;color:red;'> NO TABLE DATA </td></tr>");
}*/
return mainBuffer.toString();
}
/**
* Method to return current date and time
* @return
*/
private static String getCurrentDateAndTime()
{
Date dNow = new Date( );
SimpleDateFormat ft = new SimpleDateFormat ("MM/dd/yyyy '-' hh:mm:ss a zzz");
return ft.format(dNow);
}
/**
* Method to return String to know is day light saving time or not
* @return
*/
private String isDayLightSavingTime()
{
TimeZone tz = TimeZone.getTimeZone("US/Eastern");
boolean inDs = tz.inDaylightTime(new Date());
if(inDs)
return "Y";
else
return "N";
}
}
package emailapp1;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.TimeZone;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
public class BCLoadAndSchedulerMonitorEmail{
public static void main(String args[]) throws AddressException {
BCLoadAndSchedulerMonitorEmail monitorEmail = new BCLoadAndSchedulerMonitorEmail();
//List<JobStatus> jobStatusPortalList = monitorEmail.getJobStatusDisplayofPortalDB();
List<JobStatus> jobStatusPortalReportingList = monitorEmail.getJobStatusDisplayofPortalReportingDB();
//jobStatusPortalReportingList.addAll(jobStatusPortalList);
List<JobStatus> failuredList = new ArrayList<JobStatus>();
for (JobStatus jobStatus : jobStatusPortalReportingList) {
if(jobStatus!=null && jobStatus.getMERCHANT_NBR()!=null && !jobStatus.getMERCHANT_NBR().equalsIgnoreCase(""))
{
// if(jobStatus.getStatus().equalsIgnoreCase("FAILED") || jobStatus.getStatus().equalsIgnoreCase("NOT EXECUTED"))
failuredList.add(jobStatus);
}
}
// Comparator<JobStatus> order = new Comparator<JobStatus>() {
// String status1 = "";
// String status2 = "";
// public int compare(JobStatus jobStatus1, JobStatus jobStatus2) {
// status1 = jobStatus1.getStatus().trim();
// status2 = jobStatus2.getStatus().trim();
// return status1.compareTo(status2);
// }};
// Collections.sort(failuredList, order);
if(failuredList.size()>0)
{
String emailBodyContent = BCLoadAndSchedulerMonitorEmail.createHtmlTemplate(failuredList);
//System.out.println(emailBodyContent);
System.out.println("before sending mail");
BCLoadAndSchedulerMonitorEmail.sendEmail(emailBodyContent);
}
//System.out.println(BCLoadAndSchedulerMonitorEmail.createHtmlTemplate(failuredList));
}
private static boolean sendEmail(String message) {
Session mailSession = getMailSession();
//code to prepare recipient email addresses
List<InternetAddress> recipientAddresses = null;
InternetAddress[] recipientArray = null;
String[] emailList =null;
try {
String email = BCLoadAndSchedulerMonitorEmail.loadPropvalues("BC_Email_List");
if(email!=null && email.length()>0)
{
emailList=email.split(",");
}
recipientAddresses = createRecipients(emailList);
recipientArray = new InternetAddress[recipientAddresses.size()];
recipientAddresses.toArray(recipientArray);
} catch (AddressException ex) {
ex.printStackTrace();
}
try {
// create a message
javax.mail.Message msg = new MimeMessage(mailSession);
msg.setFrom(new InternetAddress(BCLoadAndSchedulerMonitorEmail.loadPropvalues("EMAILFROMADDRESS")));
msg.setRecipients(javax.mail.Message.RecipientType.TO, recipientArray);
// subject
msg.setSubject(BCLoadAndSchedulerMonitorEmail.loadPropvalues("EMAIL_SUBJECT_FAILED"));
// date sent
msg.setSentDate(new Date());
if (message != null) {
if (message.startsWith("<HTML") || message.startsWith("<html")) {
msg.setContent(message, "text/html");
} else {
msg.setText(message);
}
}
// send email
System.out.println("in mail method ");
Transport.send(msg);
//System.out.println(message);
} catch (Throwable e) {
e.printStackTrace();
System.out.println(e);
}
return true;
}
private static Session getMailSession() {
Properties p = new Properties();
p.setProperty("mail.smtp.host", BCLoadAndSchedulerMonitorEmail.loadPropvalues("SMTP_HOST_NAME"));
p.setProperty("mail.smtp.port", BCLoadAndSchedulerMonitorEmail.loadPropvalues("SMTP_PORT"));
Session session = Session.getInstance(p);
return session;
}
/**
* If any of the emails are invalid, it throws exception
*
* @param recipients list of Strings representing valid emails
* @return list of InternetAddress objects
* @throws AddressException if any invalid email addresses
*/
private static List<InternetAddress> createRecipients(String[] recipients)
throws AddressException {
ArrayList<InternetAddress> addresses = new ArrayList<InternetAddress>(
recipients.length);
String email = null;
for (int i = 0; i < recipients.length; i++) {
email = recipients[i];
if (validateEmail(email)) {
addresses.add(new InternetAddress(email));
}
}
return addresses;
}
/**
* Method to check whether the given email is valid or not
* @param address
* @return true if given email is valid or false if not valid
*/
private static boolean validateEmail(String address) {
try {
new InternetAddress(address);
} catch (AddressException ex) {
return false;
}
return true;
}
/**
* Method to return connection object
* @return Connection
*/
public static Connection getConnection(String url) {
Connection connection = null;
try {
Class.forName(BCLoadAndSchedulerMonitorEmail.loadPropvalues("JDBC_DRIVER_NAME"));
connection = DriverManager.getConnection(url,
BCLoadAndSchedulerMonitorEmail.loadPropvalues("JDBC_USERNAME"),
BCLoadAndSchedulerMonitorEmail.loadPropvalues("JDBC_PASSWORD"));
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return connection;
}
/**
* Method to return Load Track Details
* @return List
*/
public List<JobStatus> getJobStatusDisplayofPortalDB()
{
PreparedStatement preparedStatement =null;
ResultSet resultSet = null;
JobStatus jobStatus =null;
List<JobStatus> jobStatusList = new ArrayList<JobStatus>();
String isDayLightSavingTime = isDayLightSavingTime();
String loadTrackDetailsSql ="SELECT MERCHANT_NBR,(SELECT b.en_PROGRAM_NAME FROM MERCHANT A,PROGRAM B WHERE A.PROGRAM_ID=B.PROGRAM_ID and a.merchant_nbr=c.merchant_nbr) merchant_name, PRODUCT_CD,APPROVED,DECLINE ,PENDING,VERID,SUM(APPROVED+DECLINE +PENDING+VERID) TOTAL from (SELECT MERCHANT_NBR,PRODUCT_CD, SUM (NVL(APPROVED,0)) APPROVED, SUM (NVL(DECLINE,0)) DECLINE, SUM (NVL(PENDING,0)) PENDING, SUM (nvl(VERID,0)) VERID from ( SELECT MERCHANT_NBR,PRODUCT_CD,SUM(NVL(CNT,0)) APPROVED, NULL DECLINE, NULL PENDING , NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='APPROVED' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,SUM(NVL(CNT,0)) DECLINE , NULL PENDING , NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ORDER BY 1 DESC ) WHERE RESULTCODE='DECLINE' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,NULL DECLINE ,SUM(NVL(CNT,0)) PENDING, NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='PENDING' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,NULL DECLINE ,NULL PENDING,SUM(NVL(CNT,0)) VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='VERID' GROUP BY MERCHANT_NBR,product_cd ) GROUP BY MERCHANT_NBR,PRODUCT_CD ) c group by MERCHANT_NBR,PRODUCT_CD,APPROVED,DECLINE ,PENDING,VERID";
Connection connection = getConnection(BCLoadAndSchedulerMonitorEmail.loadPropvalues("JDBC_URL_PORTAL"));
try {
preparedStatement = connection.prepareStatement(loadTrackDetailsSql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next())
{
jobStatus.setMERCHANT_NBR(resultSet.getString("MERCHANT_NBR"));
jobStatus.setMERCHANT_NAME(resultSet.getString("MERCHANT_NAME"));
jobStatus.setPRODUCT_CD(resultSet.getString("PRODUCT_CD"));
jobStatus.setAPPROVED(resultSet.getString("APPROVED"));
jobStatus.setDECLINE(resultSet.getString("DECLINE"));
jobStatus.setPENDING(resultSet.getString("PENDING"));
jobStatus.setVERID(resultSet.getString("VERID"));
jobStatusList.add(jobStatus);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally{
BCLoadAndSchedulerMonitorEmail.closeConnections(resultSet, preparedStatement, connection);
}
return jobStatusList;
}
/**
* Method to get the scheduler job details from portal Reporting
* @return List<JobStatus>
*/
public List<JobStatus> getJobStatusDisplayofPortalReportingDB()
{
List<JobStatus> jobStatusList = new ArrayList<JobStatus>();
PreparedStatement preparedStatement =null;
ResultSet resultSet = null;
JobStatus jobStatus =null;
String isDayLightSavingTime = isDayLightSavingTime();
String schedulerJobDetailsSql ="SELECT MERCHANT_NBR,(SELECT b.en_PROGRAM_NAME FROM MERCHANT A,PROGRAM B WHERE A.PROGRAM_ID=B.PROGRAM_ID and a.merchant_nbr=c.merchant_nbr) merchant_name, PRODUCT_CD,APPROVED,DECLINE ,PENDING,VERID,SUM(APPROVED+DECLINE +PENDING+VERID) TOTAL from (SELECT MERCHANT_NBR,PRODUCT_CD, SUM (NVL(APPROVED,0)) APPROVED, SUM (NVL(DECLINE,0)) DECLINE, SUM (NVL(PENDING,0)) PENDING, SUM (nvl(VERID,0)) VERID from ( SELECT MERCHANT_NBR,PRODUCT_CD,SUM(NVL(CNT,0)) APPROVED, NULL DECLINE, NULL PENDING , NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='APPROVED' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,SUM(NVL(CNT,0)) DECLINE , NULL PENDING , NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ORDER BY 1 DESC ) WHERE RESULTCODE='DECLINE' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,NULL DECLINE ,SUM(NVL(CNT,0)) PENDING, NULL VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='PENDING' GROUP BY MERCHANT_NBR,product_cd UNION ALL SELECT MERCHANT_NBR,PRODUCT_CD,NULL APPROVED,NULL DECLINE ,NULL PENDING,SUM(NVL(CNT,0)) VERID FROM (select merchant_nbr,PRODUCT_CD,resultcode,count(1) CNT from ( SELECT INITIATION_TIMESTAMP ApplyDate, PRODUCT_CD, CASE WHEN result_code ='00' THEN 'APPROVED' WHEN result_code ='06' THEN 'PENDING' WHEN result_code ='07' THEN 'DECLINE' WHEN result_code ='04' THEN 'VERID' ELSE 'PROCESS ERROR' END AS Resultcode, Merchant_nbr, COUNT(*) NumberOfApp FROM WEBSITE_MERCHANT_ACTY WHERE result_code IS NOT NULL AND TRUNC(INITIATION_TIMESTAMP) =TRUNC(SYSDATE-8) GROUP BY INITIATION_TIMESTAMP, PRODUCT_CD, result_code, MERCHANT_NBR ) GROUP BY MERCHANT_NBR,RESULTCODE,PRODUCT_CD ) WHERE RESULTCODE='VERID' GROUP BY MERCHANT_NBR,product_cd ) GROUP BY MERCHANT_NBR,PRODUCT_CD ) c group by MERCHANT_NBR,PRODUCT_CD,APPROVED,DECLINE ,PENDING,VERID";
Connection connection = getConnection(BCLoadAndSchedulerMonitorEmail.loadPropvalues("JDBC_URL_PORTAL_REPORTING"));
try {
preparedStatement = connection.prepareStatement(schedulerJobDetailsSql);
resultSet = preparedStatement.executeQuery();
while(resultSet.next())
{
jobStatus = new JobStatus();
jobStatus.setMERCHANT_NBR(resultSet.getString("MERCHANT_NBR"));
jobStatus.setMERCHANT_NAME(resultSet.getString("MERCHANT_NAME"));
jobStatus.setPRODUCT_CD(resultSet.getString("PRODUCT_CD"));
jobStatus.setAPPROVED(resultSet.getString("APPROVED"));
jobStatus.setDECLINE(resultSet.getString("DECLINE"));
jobStatus.setPENDING(resultSet.getString("PENDING"));
jobStatus.setVERID(resultSet.getString("VERID"));
jobStatusList.add(jobStatus);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally{
BCLoadAndSchedulerMonitorEmail.closeConnections(resultSet, preparedStatement, connection);
}
return jobStatusList;
}
/**
* JobStatus Bean class
* @author uday.sariki
*
*/
class JobStatus
{
private String MERCHANT_NBR;
private String MERCHANT_NAME;
private String PRODUCT_CD;
private String APPROVED;
private String DECLINE;
private String PENDING;
private String VERID;
public void setMERCHANT_NBR(String MERCHANT_NBR) {
this.MERCHANT_NBR = MERCHANT_NBR;
}
public String getMERCHANT_NBR() {
return MERCHANT_NBR;
}
public void setMERCHANT_NAME(String MERCHANT_NAME) {
this.MERCHANT_NAME = MERCHANT_NAME;
}
public String getMERCHANT_NAME() {
return MERCHANT_NAME;
}
public void setPRODUCT_CD(String PRODUCT_CD) {
this.PRODUCT_CD = PRODUCT_CD;
}
public String getPRODUCT_CD() {
return PRODUCT_CD;
}
public void setAPPROVED(String APPROVED) {
this.APPROVED = APPROVED;
}
public String getAPPROVED() {
return APPROVED;
}
public void setDECLINE(String DECLINE) {
this.DECLINE = DECLINE;
}
public String getDECLINE() {
return DECLINE;
}
public void setPENDING(String PENDING) {
this.PENDING = PENDING;
}
public String getPENDING() {
return PENDING;
}
public void setVERID(String VERID) {
this.VERID = VERID;
}
public String getVERID() {
return VERID;
}
}
private static void closeConnections(ResultSet resultSet,
PreparedStatement preparedStatement, Connection connection) {
try {
if (resultSet != null) {
resultSet.close();
}
if(preparedStatement!=null)
{
preparedStatement.close();
}
if(preparedStatement!=null)
{
preparedStatement.close();
}
if(connection!=null)
{
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static String loadPropvalues(String propValue)
{
//File runtimeFile = new File("/appdata/webpos/dbmonitoring/dbMonitoring.properties");
//File runtimeFile = new File("/export/home/ecomadm/BCDBjobs/dbMonitoring.properties");
File runtimeFile = new File("D://GE GDC Working Folder/ashish.negi/EtailProject/EmailApplication/EmailApp1/dbMonitoring.properties");
java.util.Properties runtimeProps = new java.util.Properties();
if (runtimeFile.exists())
{
FileInputStream propFile=null;
try
{
propFile = new FileInputStream(runtimeFile);
runtimeProps.load(propFile);
propValue = runtimeProps.getProperty(propValue);
propFile.close();
}
catch (IOException ioe)
{
ioe.printStackTrace();
}
finally
{
try
{
if (propFile != null)
{
propFile.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
return propValue;
}
private static String createHtmlTemplate(List<JobStatus> portalReportingDBList)
{
StringBuffer buf = new StringBuffer();
//Html main open and close tags
String htmlOpenTags = "<html lang='en-US'><head><meta charset='UTF-8'><meta name='viewport' content='width=device-width, initial-scale=1.0'>" +
"<title>BC Load and Scheduler Status</title></head><body><table border='0' cellpadding='2' cellspacing='2'>";
String htmlCloseTags="</table></body></html>";
//Html PortalReporting open and close tags
String htmlPortalReportingOpenTags = "<tr> <td align='center' style='padding-top:30px;'><table border='1' cellpadding='2' cellspacing='0'>" +
"<tr style='background-color:black;'>" +
"<td colspan='7' align='center' style='font-size:24px;font-weight:bold;color:white;'>Etail "+BCLoadAndSchedulerMonitorEmail.getCurrentDateAndTime()+"</td></tr>" +
"<tr style='background-color:B0B0B0;'><td align = 'center' style='font-size:18px;font-weight:bold;'>Merchant Number</td><td align = 'center' style='font-size:18px;font-weight:bold;'>" +
"Status</td><td align = 'center' style='font-size:18px;font-weight:bold;'>Merchant Name</td><td align = 'center' style='font-size:18px;font-weight:bold;'>PD/GC</td>" +
"<td align = 'center' style='font-size:18px;font-weight:bold;'>Approved</td><td align = 'center' style='font-size:18px;font-weight:bold;'>Stage Count</td> " +
"<td align = 'center' style='font-size:18px;font-weight:bold;'>Declined</td></tr>";
String htmlPortalReportingCloseTags = "</table></td></tr>";
//Html Portal open and close tags
/* String htmlPortalOpenTags ="<tr><td align='center'><table border='1' cellpadding='5' cellspacing='0'><tr style='background-color:black;'>" +
"<td colspan='3' align='center' style='font-size:24px;font-weight:bold;color:white;'>PORTAL SCHEDULER TRACK </td></tr>" +
"<tr style='background-color:B0B0B0;'><td align = 'center' style='font-size:18px;font-weight:bold;'>Job Name</td><td align = 'center' style='font-size:18px;font-weight:bold;'>" +
"Status</td><td align = 'center' style='font-size:18px;font-weight:bold;'>Reason</td></tr>";
String htmlPortalCloseTags = "</table></td></tr>";*/
buf.append(htmlOpenTags);
//Adding the Portal Html open tags Content first and dynamic rows.
//buf.append(htmlPortalOpenTags+BCLoadAndSchedulerMonitorEmail.prepareHtmlRowsforPortalSchema(portalDBList));
//Adding the close tags of portal schema
//buf.append(htmlPortalCloseTags);
//Adding the Portal Reporting Html open tags Content first and dynamic rows.
buf.append(htmlPortalReportingOpenTags+BCLoadAndSchedulerMonitorEmail.prepareHtmlRowsforPortalReportingSchema(portalReportingDBList));
//Adding the close tags of Portal Reporting schema
buf.append(htmlPortalReportingCloseTags);
buf.append(htmlCloseTags);
return buf.toString();
}
/**
* Method not in use
* Prepare Html row string for portal function
* @param portalDBList
* @return string
*/
private static String prepareHtmlRowsforPortalSchema(List<JobStatus> portalDBList){
StringBuffer buf = new StringBuffer();
String evenOddColor = "";
if(portalDBList!=null && portalDBList.size()>0)
{
JobStatus jobStatus = null;
for(int i=0;i<portalDBList.size();i++)
{
jobStatus = (JobStatus)portalDBList.get(i);
buf.append("<tr style='background-color:"+evenOddColor+";'><td align = 'center'>"+jobStatus.getMERCHANT_NBR()+"</td>");
if (jobStatus.getMERCHANT_NBR() != null
&& !jobStatus.getMERCHANT_NBR().equalsIgnoreCase("")
&& jobStatus.getMERCHANT_NBR().equalsIgnoreCase("FAILED")) {
buf.append("<td align = 'center' style='background-color:red;font-size:11px;'> ");
} else {
buf.append("<td align = 'center' style='background-color:SeaGreen;font-size:11px;'> ");
}
buf.append(jobStatus.getMERCHANT_NBR()+" </td><td align = 'center'>");
buf.append((jobStatus.getMERCHANT_NAME() != null)?jobStatus.getMERCHANT_NAME():" "+"</td></tr>");
if(evenOddColor.equals(""))
evenOddColor="Silver";
else
evenOddColor="";
}
}else{
buf.append(" <tr><td colspan='3' align='center' style='font-size:14px;font-weight:bold;color:red;'> NO TABLE DATA </td></tr>");
}
return buf.toString();
}
/**
* Prepare Html row string for Portal_Reporting function
* @param portalDBList
* @return string
*/
private static String prepareHtmlRowsforPortalReportingSchema(List<JobStatus> portalDBList){
StringBuffer mainBuffer = new StringBuffer();
String evenOddColor = "";
if(portalDBList!=null && portalDBList.size()>0)
{
JobStatus jobStatus = null;
for(int i=0;i<portalDBList.size();i++)
{
StringBuffer subBuffer = new StringBuffer();
jobStatus = (JobStatus)portalDBList.get(i);
subBuffer.append("<tr style='background-color:"+evenOddColor+";'><td align = 'left'>"+jobStatus.getMERCHANT_NBR() +"</td>");
subBuffer.append(jobStatus.getMERCHANT_NAME()+"</nobr></td><td align = 'center' style='font-size:11px;'>");
subBuffer.append((jobStatus.getPRODUCT_CD() != null)?jobStatus.getPRODUCT_CD():" ");
subBuffer.append("</td><td align = 'center' style='font-size:11px;'>");
subBuffer.append((jobStatus.getAPPROVED() != null)?jobStatus.getAPPROVED():" ");
subBuffer.append("</td><td align = 'center' style='font-size:11px;'>");
subBuffer.append((jobStatus.getPENDING()!= null)?jobStatus.getPENDING():" ");
subBuffer.append("</td><td align = 'center' style='font-size:11px;'>");
subBuffer.append((jobStatus.getDECLINE() != null)?jobStatus.getDECLINE():" ");
subBuffer.append("</td><td align = 'center' style='font-size:11px;'>");
subBuffer.append((jobStatus.getVERID()!= null)?jobStatus.getVERID():" ");
subBuffer.append("</td></tr>");
if(evenOddColor.equals(""))
evenOddColor="Silver";
else
evenOddColor="";
mainBuffer.append(subBuffer);
}
}/*else{
mainBuffer.append(" <tr><td colspan='7' align='center' style='font-size:18px;font-weight:bold;color:red;'> NO TABLE DATA </td></tr>");
}*/
return mainBuffer.toString();
}
/**
* Method to return current date and time
* @return
*/
private static String getCurrentDateAndTime()
{
Date dNow = new Date( );
SimpleDateFormat ft = new SimpleDateFormat ("MM/dd/yyyy '-' hh:mm:ss a zzz");
return ft.format(dNow);
}
/**
* Method to return String to know is day light saving time or not
* @return
*/
private String isDayLightSavingTime()
{
TimeZone tz = TimeZone.getTimeZone("US/Eastern");
boolean inDs = tz.inDaylightTime(new Date());
if(inDs)
return "Y";
else
return "N";
}
}
and one property file require..
# E-mail Settings
SMTP_HOST_NAME = 3.182.40.137
SMTP_PORT= 34
SMTP_AUTH_USER = null;
SMTP_AUTH_PWD = null;
EMAIL_SUBJECT = Business DB Job Status
EMAIL_SUBJECT_FAILED = Alert Job failed
EMAILFROMADDRESS = ashunegi34@gmail.com
BC_Email_List = ashunegi34@gmail.com
#BC_Email_List = ashunegi34@gmail.com
# JDBC settings
JDBC_DRIVER_NAME =oracle.jdbc.OracleDriver
JDBC_URL_PORTAL =jdbc:oracle:thin:@
JDBC_URL_PORTAL_REPORTING =jdbc:oracle:thin:@
JDBC_USERNAME =
JDBC_PASSWORD =
remember to add oracle14.jar file in lib..folder..
No comments:
Post a Comment