DROP JAVA SOURCE CQ.RECEIVEMAIL;

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED CQ.RECEIVEMAIL as import javax.mail.*;
  import javax.mail.internet.*;
  import java.util.*;
  import java.io.*;
  import java.sql.*;
  import sqlj.runtime.*;
  import oracle.sql.BLOB;

public class ReceiveMail
{
  static void getAttachments(Message message, int incidentNo)
  throws MessagingException, IOException, SQLException {
    //String attachments = "";
    Object content = message.getContent();
    if (content instanceof Multipart)
    {
      // -- Multi part message which may contain attachment
      Multipart multipart = (Multipart)message.getContent();
      // -- Loop through all parts of the message
      for (int i=0, n=multipart.getCount(); i<n; i++) {
        Part part = multipart.getBodyPart(i);
        String disposition = part.getDisposition();
        if (Part.ATTACHMENT.equals(disposition)) {
        //--if ((disposition != null) && (disposition.equals(Part.ATTACHMENT) || disposition.equals(Part.INLINE))) {
          //-- This part is a file attachment
          // --String fileName = incidentNo+"_"+part.getFileName().replace(' ','_');
          String fileName = part.getFileName().replaceAll(" ","");
          System.out.println("FILE: " + fileName);
          String contentType = part.getContentType();          
          String mimeType = contentType.substring(0,contentType.indexOf(";"));
          System.out.println("FILETYPE: " + mimeType);
          InputStream is = part.getInputStream();
          // -- To work with a BLOB column you have to insert a record
          // -- with an emptly BLOB first.
          #sql { insert into cq_incoming_attachments(att_seq, att_in_seq, att_file, att_attachment)
                                 values (:incidentNo, :incidentNo||'_'||:i, :fileName, empty_blob()) };
          // -- Retrieve the BLOB
          BLOB attachment = null;
          #sql { select att_attachment 
                   into :attachment
                   from cq_incoming_attachments  
                  where att_file = :fileName 
                    and att_seq = :incidentNo
                    and att_in_seq = :incidentNo||'_'||:i };
          // -- Fill the BLOB
          OutputStream os = attachment.getBinaryOutputStream();
          int j;
          while ((j = is.read()) != -1) {
             os.write(j);
          }
          is.close();
          os.close();
          // -- Set the BLOB by updating the record
          #sql { update cq_incoming_attachments
                    set att_attachment = :attachment
                  where att_file = :fileName
                    and att_seq = :incidentNo };
          #sql { update mail_inbox set attachment = 'Y', att_name = trim(att_name||' '||:fileName)
                 where att_seq = :incidentNo };
        }
      }
    }
  }
static void getrfc822Attachments(Message message, int incidentNo)
 throws MessagingException, IOException, SQLException {
   //String attachments = "";
   Object content = message.getContent();
  if (content instanceof Multipart)
   {
     
     // -- Multi part message which may contain attachment
     Multipart multipart = (Multipart)message.getContent();
     // -- Loop through all parts of the message
     for (int i=0, n=multipart.getCount(); i<n; i++) {
       Part part = multipart.getBodyPart(i);
         //-- This part is a file attachment
         String contentType = part.getContentType();
       //  if (contentType == "MESSAGE/RFC822")
         //if (contentType.equals("MESSAGE/RFC822"))
         if (part.isMimeType("MESSAGE/RFC822"))
         {
                    String fileName = part.getFileName()+".eml";
         
         }
         }
         
         //System.out.println("FILE: " + fileName);

//         InputStream is = part.getInputStream();
     }}
 

  static String getPlainTextBody(Message message)
  throws MessagingException, IOException
  {
    Object content = message.getContent();
    //--if (message.isMimeType("text/plain")) {
   if (message.isMimeType("text/plain")) {
      // -- Message has plain text body only
      System.out.println("SIMPLE TEXT");
      return (String) content;
    } else if (message.isMimeType("multipart/*")) {
        // -- Message is multipart. Loop through the message parts to retrieve
        // -- the body.
        Multipart mp = (Multipart) message.getContent();
        int numParts = mp.getCount();
        System.out.println("MULTIPART: "+numParts);
        for (int i = 0; i < numParts; ++i) {
          System.out.println("PART: "+mp.getBodyPart(i).getContentType());
          if (mp.getBodyPart(i).isMimeType("text/plain")) {
            // -- Return the plain text body
            return (String) mp.getBodyPart(i).getContent();
          } else if (mp.getBodyPart(i).isMimeType("multipart/*")) {
              // -- Body is also multipart (both plain text and html).
              // -- Loop through the body parts to retrieve plain text part.
              MimeMultipart mmp = (MimeMultipart) mp.getBodyPart(i).getContent();
              int numBodyParts = mmp.getCount();
              System.out.println("MULTIBODYPART: "+numBodyParts);
              for (int j = 0; j < numBodyParts; ++j) {
                System.out.println("BODYPART: "+mmp.getBodyPart(j).getContentType());
                if (mmp.getBodyPart(j).isMimeType("text/plain")) {
                  // -- Return the plain text body
                  //--return (String) mmp.getBodyPart(j).getContent();
                  return (String) mmp.getBodyPart(j).getContent();
                }
              }
          }
        }
        return "";
    } else {
        System.out.println("UNKNOWN: "+message.getContentType());
        return "";
    }
  }
 
  static void saveMessage(Message  message, String insystem)
  throws MessagingException, IOException, SQLException
  {
    
  
    //String body = "";
    int incidentNo;
    // -- Get a new incident number
    #sql { select seq_incident.nextval into :incidentNo from dual };
    // -- Get the from address information
    String from;
    
    if(message.getFrom() != null){
        from = ((InternetAddress)message.getFrom()[0]).getAddress();
    }else{
        from = " ";
    }
    
    // -- Get the reply address information
    String reply;
    
    if(message.getReplyTo() != null){
        reply = ((InternetAddress)message.getReplyTo()[0]).getAddress();
    }else{
        reply = " ";
    }
    
    // --String reply = ((InternetAddress)message.getReplyTo()[0]).getAddress();
    
    // -- Get the to address information 
    String to = InternetAddress.toString(message.getRecipients(Message.RecipientType.TO)); 
    // -- Get the cc address information  
    String cc = InternetAddress.toString(message.getRecipients(Message.RecipientType.CC)); 
    // -- Get the bcc address information
    String bcc = InternetAddress.toString(message.getRecipients(Message.RecipientType.BCC)); 
     // -- Get the sent and receive date
    java.util.Date receive = message.getSentDate();
    java.sql.Date sent = new java.sql.Date(receive.getTime());                 
    // -- Get the message subject information
    String subject = message.getSubject();
    // -- Retrieve the plain text body
    String body = getPlainTextBody(message);
    
    // --Check string lengths before insert into mail_inbox
    
                
    if (body.length() > 3999) {
        body = body.substring(0,3999);
            }
 
    
   // -- Store the message in the email table
    #sql { insert into mail_inbox (from_address, reply_address, to_address, cc_address, bcc_address, sent_time, received_time, subject, 
           mail_body,in_system, proc_ind, attachment, att_name, att_seq)
           values (substr(:from,1,200), 
                   substr(:reply,1,200), 
                   nvl(substr(:to,1,200),' '), 
                   substr(:cc,1,500), 
                   substr(:bcc,1,500), 
                   :sent, 
                   :sent, 
                   substr(nvl(:subject,'NO SUBJECT'),1,200), 
                   substr(nvl(:body,'NO MAIL BODY'),1,4000),
                   substr(:insystem,1,100), 'N', 'N', null, 
                   :incidentNo) };
                           // --mail_body, 
                           // --nvl(substr(:body,1,4000),'null'),
    // -- Retrieve the attachments
    getAttachments(message, incidentNo);
    //getrfc822Attachments(message,incidentNo );
    #sql { commit };
    
  }
  
   
 public static String Receive(String POP3Server, String usr, String pwd, String insystem)
  {
    Store store = null;
    Folder folder = null;
    Folder dfolder = null;
    try
    {
    
      // -- Get hold of the default session --
      Properties props = System.getProperties();
      props.put("mail.pop3.connectiontimeout", "60000");
      Session session = Session.getDefaultInstance(props, null);
      // -- Get hold of a POP3 message store, and connect to it --
      // --store = session.getStore("pop3");
      store = session.getStore("imap");
      store.connect(POP3Server, usr, pwd);
      System.out.println("Connected");
      // -- Try to get hold of the default folder --
      folder = store.getDefaultFolder();
      if (folder == null) throw new Exception("No default folder");
      // -- ...and its INBOX --
      folder = folder.getFolder("INBOX");
      if (folder == null) throw new Exception("No IMAP INBOX");
      // -- Open the folder for read_write (to be able to delete message) --
      folder.open(Folder.READ_WRITE);
      dfolder = folder.getFolder("READ_ITEMS");
      // -- Get the message wrappers and process them --
      Message[] msgs = folder.getMessages();
      for (int msgNum = 0; msgNum < msgs.length; msgNum++){
      try
       {
        saveMessage(msgs[msgNum], insystem);
        }
          catch (Exception ex){
          msgs[msgNum].setFlag(Flags.Flag.DELETED, true);
          
       }
        // -- Copy message from INBOX to READ_ITEMS  
        // --folder.copyMessages(msgs, dfolder);
        // -- Delete message in inbox 
        // --msgs[msgNum].setFlag(Flags.Flag.DELETED, true);   
      }
      
      folder.copyMessages(msgs, dfolder);
      
      Message[] msgs1 = folder.getMessages();
      for (int msgNum1 = 0; msgNum1 < msgs1.length; msgNum1++){
           // -- Delete message in inbox 
        msgs1[msgNum1].setFlag(Flags.Flag.DELETED, true);   
      }
      
      System.out.println("No more messages");
      return ("SUCCESS");
    }
    catch (Exception ex){
      ex.printStackTrace();
      return ex.toString();
    }
    finally{
      // -- Close down nicely --
      try{
        // close(true), to expunge deleted messages
        if (folder!=null) folder.close(true);
        if (store!=null) store.close();
      }
      catch (Exception ex){
        //ex.printStackTrace();
        return ex.toString();
      }
    }
  }
};
/