/*
 * @author  : Elangovan
 * @version : 1.0
 *
 * Development Environment : Oracle9i JDeveloper
 * Name of the File        : UploadStockRateTask.java
 *
 * Creation / Modification History
 *    Elangovan           26-Apr-2002        Created
 *
 */
package oracle.otnsamples.ibfbs.admin.helper;

import java.util.Collection;
import java.util.TimerTask;
import java.util.Date;
import java.util.Vector;

import java.math.BigDecimal;

import javax.naming.InitialContext;

import javax.jms.JMSException;
import javax.jms.Message;
import javax.jms.Queue;
import javax.jms.QueueConnection;
import javax.jms.QueueConnectionFactory;
import javax.jms.QueueSession;

import oracle.toplink.tools.sessionmanagement.SessionManager;
import oracle.toplink.tools.sessionconfiguration.XMLLoader;
import oracle.toplink.threetier.ServerSession;
import oracle.toplink.threetier.ClientSession;
import oracle.toplink.queryframework.SQLCall;
import oracle.toplink.exceptions.DatabaseException;
import oracle.toplink.publicinterface.DatabaseRow;

import oracle.otnsamples.ibfbs.utils.ConnectionParams;
import oracle.otnsamples.ibfbs.admin.ejb.MailService;
import oracle.otnsamples.ibfbs.admin.ejb.MailServiceHome;
import oracle.otnsamples.ibfbs.trademanagement.helper.StockRateHelper;
import oracle.otnsamples.ibfbs.admin.exception.WebServiceAccessException;

/**
 * This class, access web services through the WebServiceHelper class and
 * populates the database with the latest market data.
 *
 * @see oracle.otnsamples.ibfbs.trademanagement.helper.StockRateHelper.java
 * @see oracle.otnsamples.ibfbs.admin.ejb.AlertQueue.java
 * @see AdminHelper.java
 * @see java.util.Timer
 * @see java.util.TimerTask  
 */
public class UploadStockRateTask extends TimerTask {

  /**
   * This method accesses the latest StockRates from the WebServicesHelper class
   * and populates the datasource with the StockRates. 
   *
   */
  public void run() {

    int status = 0;
    try {
      WebServicesHelper wshelper = new WebServicesHelper();
      StockSymbolHelper sshelper = new StockSymbolHelper();
      StockRateHelper srHelper = new StockRateHelper();

      //  Get the complete list of symbols
      Collection symbols = sshelper.getAllSymbols();

      // Retrieve the stock rates for all symbols and populate the rates
      srHelper.loadRates(wshelper.fetchStockRates(symbols));

    } catch (WebServiceAccessException we) {  // WebService access error

      // Failure
      System.err.println(" Error accessing web service :" + we.toString());

      status = -1;
    } catch (Exception ex) {                  // Generic error
      System.err.println(" Generic Exception while  "
                         + " uploading stock data :\n" + ex.toString());

      status = -1;
    } finally {
      // Send Upload status to Admin
      sendUploadStatus(status);     
    }
    // Send alerts for the new stock rates
    sendAlerts();
  }

  /**
   * This methods generates alerts and posts alert messages to the AlertQueue.
   *
   */
  public void sendAlerts() {

    ServerSession server = null; 
    try {
      // Initialize ServerSession from session.xml 
      server = (ServerSession) SessionManager.getManager().getSession(new XMLLoader(),
                                "FBSServerSession",
                                Thread.currentThread().getContextClassLoader());
    }catch(DatabaseException dbEx) {
      System.out.println(" UploadStockRateTast.sendAlerts: Cannot send alerts, "+
                             "error initializing Server Session :"+dbEx);
      return;
    }
   
    BigDecimal accountnumber = null;
    BigDecimal minlimit      = null;
    BigDecimal maxlimit      = null;
    BigDecimal rate          = null;        
    String     symbol        = null;
    String     name          = null;
    String     email         = null;
    String     mobile        = null;
    String     alertmode     = null;  

    QueueSession    session = null;
    QueueConnection qconn   = null;
   
    try {

      InitialContext ic = new InitialContext();
      
      QueueConnectionFactory factory = (QueueConnectionFactory)ic.lookup(
                                        ConnectionParams.alertQueueConnFactory);
      qconn = factory.createQueueConnection();
      Queue queue = (Queue)ic.lookup(ConnectionParams.alertQueue);
      qconn.start();
      session = qconn.createQueueSession(false, 1);
       
      // Initialize client session
      ClientSession client = server.acquireClientSession();
      
      // Get the Account Numbers for which alert has to sent
      // This is a complex SQL query and queries tables that are not mapped
      // to objects in Toplink Project descriptor. Raw SQL statements can be
      // executed using Toplink, the matching rows will be returned as an instance
      // of DatabaseRow.
      Vector alerts = (Vector) client.executeSelectingCall(new SQLCall(
        " SELECT a.AccountNumber,a.Symbol,a.MinLimit,a.MaxLimit, "
        + " ua.FirstName || '  ' ||  ua.LastName FullName ,ua.AlertMode,ua.Email, "
        + " ua.MobileEmail, sr.LowPrice "
        + " FROM Alerts a,UserAccount ua, StockRate sr "
        + " WHERE sr.StockDate = (SELECT MAX(StockDate) FROM StockRate)"
        + "  AND a.Symbol = sr.Symbol AND "
        + " (sr.LowPrice <= a.MinLimit OR sr.LowPrice >= a.MaxLimit) AND "
        + " a.AccountNumber = ua.AccountNumber "));
      
      Message message;

      // Loop through the Results and post alert messages in the alert queue
      for(int i=0;i<alerts.size();i++) {
        
        DatabaseRow alert = (DatabaseRow)alerts.elementAt(i);
        
        // Set the alert details        
        accountnumber = (BigDecimal)alert.get("ACCOUNTNUMBER");        
        symbol        = (String)alert.get("SYMBOL");
        minlimit      = (BigDecimal)alert.get("MINLIMIT");
        maxlimit      = (BigDecimal)alert.get("MAXLIMIT");
        name          = (String)alert.get("FULLNAME");
        alertmode     = (String)alert.get("ALERTMODE");
        email         = (String)alert.get("EMAIL");
        mobile        = (String)alert.get("MOBILEEMAIL");        
        rate          = (BigDecimal)alert.get("LOWPRICE");
        
        // Create and send the message
        message = session.createMessage();
        
        message.setStringProperty("accountNumber", accountnumber.toString());
        message.setStringProperty("symbol", symbol);
        message.setStringProperty("minlimit", minlimit.toString());
        message.setStringProperty("maxlimit", maxlimit.toString());
        message.setStringProperty("name", name);
        message.setStringProperty("alertMode", alertmode);
        message.setStringProperty("email", email);
        message.setStringProperty("mobile", mobile);
        message.setFloatProperty("rate", rate.floatValue());
        message.setJMSExpiration(18000L);

        // Send the message
        session.createSender(queue).send(message);
      }
      
    } catch (DatabaseException se) {  // SQL errors
      System.err.println("SQl Error while sending alert : \n"
                         + se.toString());                         
    } catch (Exception ex) {     // Generic errors
      System.err.println("Exception while sending alert : \n"
                         + ex.toString());                        
    } finally {
      // Free resources
      try  {
        if(session != null)  session.close();
        if(qconn != null)    qconn.close();
      } catch(JMSException je)     {
        System.err.println("JMSException : while closing session" + je.toString());
      }      
    }
  }

  /**
   * This methods sends the upload status to the Admin.
   * 
   * @param status status of upload
   * @since 1.0
   */
  private void sendUploadStatus(int status) {

    MailService mailService = null;
    try {

      InitialContext ic = new InitialContext();
      // Initialize the MailSender
      MailServiceHome mailServiceHome = (MailServiceHome) ic.lookup("MailService");

      mailService = mailServiceHome.create();

      mailService.sendMail(ConnectionParams.notificationMailId,
                           "uploadtask@fbs.com", " Upload Stock Rate Status",
                           getUploadMsg(status));
    } catch (Exception ex) {
      System.err.println(
           " Couldnot initialize mail service to send upload status :"
           + ex.toString());
    }
  }

  /**
   * This methods generates the status message for Admin.
   *
   * @param status status of upload
   * @return Mail body corresponding to the given status
   * @since 1.0
   */
  private String getUploadMsg(int status) {

    StringBuffer sb           = new StringBuffer();
    String       uploadStatus = (status == 0)
                                ? " Upload Success "
                                : "Upload Failed";
    sb.append(" Hi Admin,<br><br> ")
      .append("    The Status of Stock Rate Upload Task : <b><u>")
      .append(uploadStatus)
      .append(" </u></b><br><br>")
      .append("  <br><br> Upload Time : ")
      .append(new Date())
      .append("<br><br>")
      .append(" --Upload Task. ");

    return sb.toString();
  }

  /**
   * Constructs the mail body using HTML tags.
   *
   * @param accountNumber Account number
   * @param name Full name of the account holder
   * @param symbol symbol for which alert is sent
   * @param minlimit min limit fixed for alert
   * @param maxlimit max limit fixed for alert
   * @param rate current stock price
   * @return mail body in HTML
   * @since 1.0
   */
  private String getHTMLAlertMsg(int accountNumber, String name, String symbol,
                            String minlimit, String maxlimit, float rate) {

    StringBuffer sb = new StringBuffer();
    sb.append("<br><br>")
      .append(" Hi,<br><br>")
      .append("<h4><u>Alert Details</u></h4>")
      .append("<table border='1'>")
      .append(" <tr><th> Account Number</th><td>")
      .append(accountNumber)
      .append("</td></tr>")
      .append(" <tr><th> Name</th><td>" + name + "</td></tr>")
      .append(" <tr><th> Symbol</th><td>" + symbol + "</td></tr>")
      .append(" <tr><th> Min Limit</th><td>" + minlimit + "</td></tr>")
      .append(" <tr><th> Max Limit</th><td>" + maxlimit + "</td></tr>")
      .append(" <tr><th> Current Stock Price</th><th>" + rate + "</th></tr>")
      .append("</table><br>")
      .append("<font color='##990000' size='3'><b> You have a Stock Alert !!")
      .append("</b></font><br><br>")
      .append("<br><br>Happy Trading !! <br> FBS Team.");

    return sb.toString();
  }

  /**
   * Constructs the mail body as plain text.
   *
   * @param accountNumber Account number
   * @param name Full name of the account holder
   * @param symbol symbol for which alert is sent
   * @param minlimit min limit fixed for alert
   * @param maxlimit max limit fixed for alert
   * @param rate current stock price
   * @return mail body in HTML
   * @since 1.0
   */
  private String getTextAlertMsg(int accountNumber, String name, String symbol,
                            String minlimit, String maxlimit, float rate) {

    StringBuffer sb = new StringBuffer();
    sb.append("\nAlert Details \n\n")
      .append("Account Number" + accountNumber + "\n")
      .append("Name :" + name + "\n")
      .append("Symbol :" + symbol + "\n")
      .append("Min Limit :" + minlimit + "\n")
      .append("Max Limit :" + maxlimit + "\n")
      .append("Current Stock Price :" + rate + "\n");

    return sb.toString();
  }
  
}