/*
 * This program will capture the issues of redmine projects and mails the same to intended user.
 * This is purely based on a "As-Is" standard, No Assurance or Warranty on its results, hence no claim entertained !
 * This program is strictly in use for internal purpose only !
*/

/**
 * This work is an infant, Valid suggestions are accepted ! :-)
 * @author: Bhaskar Kashyap : Email-id: bskr.ksyp9@gmail.com
 */
import java.util.*;
import javax.mail.*;
import javax.mail.internet.*;
import java.sql.*;


public class SendMailSSL
{
 public static void main(String [] args){


      String host = "smtp.gmail.com"; //change your host accordingly
      String to="XXX@gmail.com"; //change accordingly
      final String user="YYY@gmail.com";//change accordingly
      final String password="*********";//change accordingly

     //Some properties & session objects
          Properties properties = System.getProperties();
          properties.setProperty("mail.smtp.starttls.enable", "true");
          properties.setProperty("mail.smtp.host", host);
          properties.put("mail.smtp.host", "smtp.gmail.com");
          properties.put("mail.smtp.socketFactory.port", "587");
          properties.put("mail.smtp.socketFactory.class",
                    "javax.net.ssl.SSLSocketFactory");
          properties.put("mail.smtp.auth", "true");
          properties.put("mail.smtp.port", "587");

          Session session = Session.getDefaultInstance(properties,
          new javax.mail.Authenticator() {
        @Override
        protected PasswordAuthentication getPasswordAuthentication() {
        return new PasswordAuthentication(user,password);
        }
        });
     //compose your message here
      try{
        Class.forName("com.mysql.jdbc.Driver");
        Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/bitnami_redmine","root","123123123");


         MimeMessage message = new MimeMessage(session);

         message.setFrom(new InternetAddress(user));
         message.addRecipient(Message.RecipientType.TO,new InternetAddress(to));
         message.setSubject("Redmine Issues");
         Statement stmt=con.createStatement();
         Statement stmt2=con.createStatement();
         Statement stmt3=con.createStatement();
         Statement stmt4=con.createStatement();
         Statement stmt5=con.createStatement();

        ResultSet rs=stmt.executeQuery("select count(*) from issues");
        ResultSet rs2=stmt2.executeQuery("select count(*) from issues where priority_id=4" );
        ResultSet rs3=stmt3.executeQuery("select count(*) from issues where priority_id=3" );
        ResultSet rs4=stmt4.executeQuery("select count(*) from issues where priority_id=2" );
        ResultSet rs5=stmt5.executeQuery("select count(*) from issues where priority_id=1" );

        while(rs.next()&&rs2.next()&&rs3.next()&&rs4.next()&&rs5.next())
        {
        System.out.println(" Total issues = "+rs.getInt(1));
        System.out.println(" Issues are in Urgent  = "+rs2.getInt(1));
        System.out.println(" Issues are in High  = "+rs3.getInt(1));
        System.out.println(" Issues are in Normal  = "+rs4.getInt(1));
        System.out.println(" Issues are in Low  = "+rs5.getInt(1));

         int msg = rs.getInt(1);
         int msg2 = rs2.getInt(1);
         int msg3 = rs3.getInt(1);
         int msg4 = rs4.getInt(1);
         int msg5 = rs5.getInt(1);

        message.setContent("<h1>Open issues in Total = "+msg+"\n"+"<br><br>Issues are in Urgent = "+msg2+"<br>Issues are in High = "+msg3+"<br>Issues are in Normal = "+msg4+"<br>Issues are in Low = "+msg5+"</h1>", "text/html");

         // Send message & display
         Transport.send(message);
         System.out.println("Mail sent Successfully...");
        }
         con.close();
      }
     catch (MessagingException mex) {
          mex.printStackTrace();
      }
      catch(Exception e){
       System.out.println(e);
   }}}