* $RCSfile: PaymentEJB.java,v $    $Revision: 1.2 $  $Date: 2005/06/28 19:28:42 $ - $Author: mking_cv $

*

* The contents of this file are subject to the Open Software License

* Version 2.1 (the "License"); you may not use this file except in

* compliance with the License. You may obtain a copy of the License at

* http://www.centraview.com/opensource/license.html

*

* Software distributed under the License is distributed on an "AS IS"

* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the

* License for the specific language governing rights and limitations

* under the License.

*

* The Original Code is: CentraView Open Source.

*

* The developer of the Original Code is CentraView.  Portions of the

* Original Code created by CentraView are Copyright (c) 2004 CentraView,

* LLC; All Rights Reserved.  The terms "CentraView" and the CentraView

* logos are trademarks and service marks of CentraView, LLC.

*/

    protected javax.ejb.EntityContext ctx;

    protected Context environment;

  private PaymentVO paymentVO;

    private boolean isDirty = false;  //update data if isDirty is true (ejbStore)

  private String dataSource = "MySqlDS";

    /*

  *  Create Expense record in Database

  */

  public PaymentPK ejbCreate(PaymentVO paymentVO,int userID, String ds)

    {

      int paymentID = 0;

     this.dataSource = ds;

       CVDal cvdal = new CVDal(ds);

     try

     {

       //ALLSQL.put("account.payment.addpayment","insert into payment (entityid,reference,description,paymentmethod,checknumber,cardnumber,cardtype,expiration,amount ,created) values (?,?,?,?,?,?,?,?,?,concat(CURRENT_DATE , CURRENT_TIME))");

       cvdal.setSql("account.payment.addpayment");

      cvdal.setInt(1,paymentVO.getEntityID());

      cvdal.setString(2,paymentVO.getReference());

      cvdal.setString(3,paymentVO.getDescription());

      cvdal.setInt(4,paymentVO.getPaymentMethodID());

      cvdal.setString(5,paymentVO.getCheckNumber());

      cvdal.setString(6,paymentVO.getCardNumber());

      cvdal.setString(7,paymentVO.getCardType());

      paymentVO.setExpirationDate(new java.sql.Date(2003,12,12));

      cvdal.setDate(8,new java.sql.Date(2003,12,12));

      // TODO are these HARDCODED dates??!  fix 'em

      cvdal.setFloat(9,paymentVO.getPaymentAmount());

      if(paymentVO.getExternalID() != null)

      cvdal.setString(10,paymentVO.getExternalID());

      else

      cvdal.setString(10,"0");

      cvdal.setInt(11,userID);

       cvdal.executeUpdate();

       this.paymentVO = paymentVO;

       paymentID  = cvdal.getAutoGeneratedKey();

       this.paymentVO.setPaymentID(paymentID);

       cvdal.clearParameters();

      PaymentLines paymentLines = paymentVO.getPaymentLines();

      Set listkey = paymentLines.keySet();

        Iterator it =  listkey.iterator();

      while (it.hasNext())

      {

        Object str = ( Object )it.next();

        ItemElement ele  = ( ItemElement)paymentLines.get(str);

        this.addApplyPayment(ele,paymentID);

      }

            InitialContext ic = CVUtility.getInitialContext();

            AuthorizationLocalHome authorizationHome = (AuthorizationLocalHome)ic.lookup("local/Authorization");

            AuthorizationLocal authorizationLocal = authorizationHome.create();

            authorizationLocal.setDataSource(ds);

            authorizationLocal.saveCurrentDefaultPermission("Payment", paymentID, userID);

     }

     catch(Exception e)

     {

       System.out.println("[Exception][PaymentEJB.ejbCreate] Exception Thrown: "+e);

       e.printStackTrace();

     }

     finally

     {

         cvdal.destroy();

         cvdal = null;

     }

    return new PaymentPK(paymentID, ds);

    }// end of ejbCreate

    /*

     *  Apply Payment to Invoices in Database

     */

   private void addApplyPayment(ItemElement ele ,int paymentID)

   {

      CVDal cvdal = new CVDal(this.dataSource);

    try

    {

       IntMember invoiceID = (IntMember)ele.get("InvoiceId");

       int invID = ( (Integer)invoiceID.getMemberValue()).intValue();

       DoubleMember appAmt = (DoubleMember)ele.get("AmountApplied");

       float amt = ( (Number)appAmt.getMemberValue()).floatValue();

       cvdal.setSql("account.payment.applypaymentinvoices");

         cvdal.setInt(1,paymentID);

         cvdal.setInt(2,invID);

       cvdal.setFloat(3,amt);

       cvdal.executeUpdate();

       cvdal.clearParameters();

    }

    catch(Exception e)

    {

      e.printStackTrace();

    }

    finally

    {

      cvdal.destroy();

      cvdal = null;

    }

   }// end of applyPayment

   /*

   *  Update applyPayment record in Database

   */

   private void updateApplyPayment(ItemElement ele ,int paymentID)

   {

      CVDal cvdal = new CVDal(this.dataSource);

    try

    {

      IntMember invoiceID = (IntMember)ele.get("InvoiceId");

      int invID = ( (Integer)invoiceID.getMemberValue()).intValue();

      DoubleMember appAmt = (DoubleMember)ele.get("AmountApplied");

      float amt = ( (Double)appAmt.getMemberValue()).floatValue();

      cvdal.setSql("account.payment.updatepaymentinvoices");

      //ALLSQL.put("account.payment.updatepaymentinvoices","update applypayment set amount = ? where paymentid = ? and invoiceid =?");

      cvdal.setFloat(1,amt);

      cvdal.setInt(2,paymentID);

      cvdal.setInt(3,invID);

      cvdal.executeUpdate();

      cvdal.clearParameters();

    }

    catch(Exception e)

    {

      e.printStackTrace();

    }

    finally

    {

      cvdal.destroy();

      cvdal = null;

    }

   }// end of update ApplyPayment

     /**

      * EJB Container callback method

     */

     public void ejbPostCreate (PaymentVO paymentVO,int userID, String ds) throws CreateException

     {

     }

     /**

      * Finds if a given payment exits in the database

      * @returns    PaymentPK class (EJB clients get Remote)

      * @param  int  PaymentID

      */

     public PaymentPK ejbFindByPrimaryKey(PaymentPK primaryKey) throws FinderException

     {

       HashMap hm = getBasic(primaryKey);

       if (hm == null)

        {

           throw new FinderException("Could not find Payment: " + primaryKey);

        }

        else

        {

         return primaryKey;

        }

     }// end of ejbFindByPrimaryKey

     /*

     *  get Basic Payment

     */

     private HashMap getBasic(PaymentPK primaryKey)

     {

         CVDal dl = new CVDal(primaryKey.getDataSource());

         dl.setSql("account.payment.getpayment");

     //ALLSQL.put("account.payment.getpayment","select payment.EntityID, entity.name as Entity ,amount,reference,description,PaymentMethod,CardType,CardNumber,Expiration,CheckNumber from payment inner join entity on payment.entityid =  entity.entityid where paymentid = ?");

     dl.setInt(1,primaryKey.getId());

         Collection col = dl.executeQuery();

         dl.destroy();

         dl = null;

       Iterator it = col.iterator();

       if (!it.hasNext())

         return null;

       else

         return (HashMap)it.next();

     }// end of getBasic

  /*

  *  This method set the basicform for

  *

  */

     private void setBasicForm(HashMap hm)

     {

       this.paymentVO = new PaymentVO();

    Long paymentID = null;

    if( hm.get("PaymentID") != null )

    {

      this.paymentVO.setPaymentID(Integer.parseInt(hm.get("PaymentID").toString()));

    }

    Long entityID = null;

    if( hm.get("EntityID") != null )

    {

      this.paymentVO.setEntityID(Integer.parseInt(hm.get("EntityID").toString()));

    }

    if( hm.get("PaymentMethod") != null )

    {

      this.paymentVO.setPaymentMethodID(Integer.parseInt(hm.get("PaymentMethod").toString()));

    }

    if( hm.get("amount") != null )

    {

      this.paymentVO.setPaymentAmount(Float.parseFloat(hm.get("amount").toString()));

    }

    this.paymentVO.setExpirationDate((java.sql.Date)hm.get("Expiration"));

    if(hm.get("Entity") != null)

    this.paymentVO.setEntity((String)hm.get("Entity") );

    if(hm.get("description") != null)

    this.paymentVO.setDescription((String)hm.get("description") );

    if(hm.get("reference") != null)

    this.paymentVO.setReference((String)hm.get("reference") );

    if(hm.get("CheckNumber") != null)

    this.paymentVO.setCheckNumber((String)hm.get("CheckNumber") );

    if(hm.get("CardNumber") != null)

    this.paymentVO.setCardNumber((String)hm.get("CardNumber") );

    if(hm.get("CardType") != null)

    this.paymentVO.setCardType((String)hm.get("CardType") );

    if(hm.get("Created") != null)

    this.paymentVO.setCreatedOn((java.sql.Timestamp)hm.get("Created") );

    if(hm.get("Modified") != null)

    this.paymentVO.setModifiedOn((java.sql.Timestamp)hm.get("Modified") );

    if(hm.get("ExternalID") != null)

    this.paymentVO.setExternalID((String)hm.get("ExternalID") );

     }//end of setBasicForm

     /*

     *  Set the Invoices Data in PaymentVO Object

     */

  private void setPaymentInvoices()

  {

    CVDal dl = new CVDal(this.dataSource);

    try

    {

      String q1 = " create temporary table  temppaylines select invoice.invoiceid invoiceid,invoice.title invoicetitle ,invoice.invoicedate invoicedate,invoice.total invoicetotal  , applypayment.amount amountdue, applypayment.amount amountapplied from invoice, applypayment where 1 = 0";

      String q2 = " create temporary table temp3 select applypayment.invoiceid from applypayment where applypayment.paymentid =  ? and applypayment.linestatus !=  'Deleted' ";

      String q3 = " insert into temppaylines ( invoiceid, invoicetitle, invoicedate, invoicetotal,amountdue ) select invoice.InvoiceID,invoice.title ,invoice.invoicedate, invoice.total ,invoice.total   from invoice ,temp3   where  invoice.InvoiceID = temp3.invoiceid   group by invoice.InvoiceID,invoice.title ,invoice.invoicedate, invoice.total";

      String q4 = " create temporary table temp1 select  applypayment.invoiceid, sum(applypayment.amount) as AmountApplied from temppaylines ,applypayment where temppaylines.invoiceid = applypayment.invoiceid  and applypayment.linestatus !=  'Deleted'  and applypayment.paymentid = ? group by  applypayment.invoiceid ";

      String q5 = " create temporary table temp2 select  temppaylines.invoiceid, (temppaylines.invoicetotal -  sum( applypayment.amount ) ) as AmountDue,sum( applypayment.amount )  as AmountApplied from applypayment  ,temppaylines where  applypayment.invoiceid = temppaylines.invoiceid and  applypayment.paymentid = ? and  applypayment.linestatus !=  'Deleted' group by temppaylines.invoiceid";

      String q6 = " update  temppaylines ,temp1 set temppaylines.AmountApplied   = temp1.AmountApplied   where temppaylines.invoiceid = temp1.invoiceid ";

      String q7 = " update  temppaylines ,temp2 set temppaylines.amountdue = temp2.AmountDue  , temppaylines.amountapplied = temp2.amountapplied where temppaylines.invoiceid = temp2.invoiceid ";

      String q8 = " select invoiceid,invoicetitle,invoicedate,invoicetotal,amountdue,amountapplied from temppaylines ";

//      String q2 = "select invoice.InvoiceID,invoice.title ,invoice.created,invoice.total  , (  invoice.total - temppaylines.AmountApplied  ) as AmountDue ,temppaylines.AmountApplied ,temppaylines.lineid ,temppaylines.linestatus from invoice ,temppaylines where  invoice.InvoiceID = temppaylines.InvoiceID group  by invoice.InvoiceID ";

//      String q3 = "drop table temppaylines ";

      dl.setSqlQuery(q1);

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery(q2);

      dl.setInt(1,this.paymentVO.getPaymentID());

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.clearParameters();

      dl.setSqlQuery(q3);

       dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery(q4);

      dl.setInt(1,this.paymentVO.getPaymentID());

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery(q5);

      dl.setInt(1,this.paymentVO.getPaymentID());

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery(q6);

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery(q7);

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery(q8);

      Collection col = dl.executeQuery();

      dl.setSqlQueryToNull();

      dl.setSqlQuery("drop table temppaylines");

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery("drop table temp1 ");

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery("drop table temp2");

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery("drop table temp3");

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      if (col != null)

      {

        PaymentLines paymentLines= new PaymentLines();

        PaymentLines paymentAppliedLines= new PaymentLines();

        Iterator it = col.iterator();

        int count = 1;

        int countApplied = 1;

        while (it.hasNext())

        {

          HashMap hm =(HashMap)it.next();

          //Long lineID = (Long)hm.get("lineid");

          IntMember LineId = new IntMember("LineId",1,10,"",'T',false,20);

          Long invID = (Long)hm.get("invoiceid");

          IntMember invoiceID = new IntMember("InvoiceId",invID.intValue(),10,"",'T',false,20);

          StringMember invoiceNum = null;

          /*

          if(hm.get("invoicetitle") != null)

          */

          invoiceNum = new StringMember("InvoiceNum", invID+"",10,"",'T',false);

          DateMember invDate = new DateMember( "Date" , (Date)hm.get("invoicedate")  ,10 , "" , 'T' , false ,1 ,"EST" );

          Double total = new Double(0);

          if(hm.get("invoicetotal") != null ){

            total = new Double(((Number) hm.get("invoicetotal")).doubleValue());

        }

          DoubleMember dblTotal = new DoubleMember( "Total"  ,total , 10 , "", 'T' , false , 10 );

          Double amtApp = new Double(0);

          if(hm.get("amountapplied") != null ){

            amtApp = new Double(((Number) hm.get("amountapplied")).doubleValue());

      }

          DoubleMember dblAppAmt = new DoubleMember( "AmountApplied"  ,amtApp , 10 , "", 'T' , false , 10 );

          Double amtDue = null;

          if(hm.get("amountdue") != null){

            amtDue = new Double(((Number)hm.get("amountdue")).doubleValue());

          }

          DoubleMember dblAmtDue = new DoubleMember( "AmountDue"  ,amtDue , 10 , "", 'T' , false , 10 );

          ItemElement ie = new ItemElement(11);

          ie.put ("LineId",LineId);

          ie.put ("InvoiceId",invoiceID);

          ie.put ("InvoiceNum",invoiceNum);

          ie.put ("Date",invDate);

          ie.put ("Total",dblTotal);

          ie.put ("AmountDue",dblAmtDue);

          ie.put ("AmountApplied",dblAppAmt);

          ie.setLineStatus("Active");

          double totalPayament = total.doubleValue();

          double amountApplied = amtApp.doubleValue();

          if (amountApplied > 0.0d){

            paymentAppliedLines.put(countApplied+"",ie);

            countApplied++;

          }

          double paidPayment = totalPayament - amountApplied;

          if(paidPayment == 0.0d){

          }

          else{

            paymentLines.put(""+count,ie);

            count ++;

          }

        }

        this.paymentVO.setPaymentLines(paymentLines);

        this.paymentVO.setPaymentAppliedLines(paymentAppliedLines);

      }// end of if

    }

    catch(Exception e)

    {

      e.printStackTrace();

    }

    finally

    {

      dl.destroy();

      dl = null;

    }

  }// end of setPaymentInvoices

    /**

     * A container invokes this method before it ends the life of the entity object. This

     * happens as a result of a client's invoking a remove operation, or when a container

     * decides to terminate the entity object after a timeout. This method is called with

     * no transaction context.

     */

    public void ejbRemove ()

    {

      CVDal dl = new CVDal(this.dataSource);

    try

    {

      dl.setSql("account.payment.deletepayment");

      //ALLSQL.put("account.payment.deletepayment","update payment set linestatus = 'Deleted'  where paymentid = ?");

        dl.setInt(1,this.paymentVO.getPaymentID());

      dl.executeUpdate();

      dl.clearParameters();

      dl.setSqlQueryToNull();

      dl.setSql("account.payment.deletepaymentinvoices");

      //ALLSQL.put("account.payment.deletepaymentinvoices","update applypayment set linestatus = 'Deleted'  where paymentid = ?");

      dl.setInt(1,this.paymentVO.getPaymentID());

      dl.executeUpdate();

      dl.clearParameters();

    }

    catch(Exception e)

    {

      e.printStackTrace();

    }

    finally

    {

      dl.destroy();

      dl = null;

    }

    }

    /**

     * The activate method is called when the instance is activated from its 'passive' state.

     * The instance should acquire any resource that it has released earlier in the ejbPassivate()

     * method. This method is called with no transaction context.

     */

    public void ejbActivate    ()

    {

    }

    /**

     * The passivate method is called before the instance enters the 'passive' state. The

     * instance should release any resources that it can re-acquire later in the ejbActivate()

     * method. After the passivate method completes, the instance must be in a state that

     * allows the container to use the Java Serialization protocol to externalize and store

     * away the instance's state. This method is called with no transaction context.

     */

    public void ejbPassivate    ()

    {

    }

    /*

     * Set the associated entity context. The container calls this method after the instance

     * creation. The enterprise Bean instance should store the reference to the context

     * object in an instance variable. This method is called with no transaction context.

     */

    public void setEntityContext(EntityContext ctx)

    {

        this.ctx = ctx;

    }

    public void unsetEntityContext()

    {

     this.ctx = null;

    }

  public void ejbLoad()

  {

    PaymentPK paymentPK = (PaymentPK)(ctx.getPrimaryKey());

    this.setDataSource(paymentPK.getDataSource());

    HashMap hm = getBasic(paymentPK);

    if (hm == null)

      return;

    else

      setBasicForm(hm);

  }

  public void ejbStore()

  {

    if (isDirty)

    {

      CVDal cvdal = new CVDal(this.dataSource);

      try

      {

        cvdal.setSql("account.payment.updatepayment");

        //ALLSQL.put("account.payment.updatepayment","update payment set entityid = ? ,reference = ? ,description = ?,paymentmethod = ? ,checknumber = ?,cardnumber = ?,cardtype = ? ,expiration = ?, amount = ? where paymentid  = ?");

        cvdal.setInt(1,paymentVO.getEntityID());

        cvdal.setString(2,paymentVO.getReference());

        cvdal.setString(3,paymentVO.getDescription());

        cvdal.setInt(4,paymentVO.getPaymentMethodID());

        cvdal.setString(5,paymentVO.getCheckNumber());

        cvdal.setString(6,paymentVO.getCardNumber());

        cvdal.setString(7,paymentVO.getCardType());

        cvdal.setDate(8,paymentVO.getExpirationDate());

        //cvdal.setFloat(9,paymentVO.getPaymentAmount());

        if(paymentVO.getExternalID() != null)

        cvdal.setString(9,paymentVO.getExternalID());

        else

        cvdal.setString(9,"0");

        cvdal.setInt(10,paymentVO.getModifiedBy());

        cvdal.setInt(11,paymentVO.getPaymentID());

        cvdal.executeUpdate();

        cvdal.clearParameters();

        cvdal.setSqlQueryToNull();

        PaymentLines paymentLines = paymentVO.getPaymentLines();

        Set listkey = paymentLines.keySet();

        Iterator it =  listkey.iterator();

        while (it.hasNext())

        {

          Object str = ( Object )it.next();

          ItemElement ele  = ( ItemElement)paymentLines.get( str );

          this.updateApplyPayment(ele,paymentVO.getPaymentID());

        }

        cvdal.setSql("account.payment.selectApplypaymentamount");

        cvdal.setInt(1,paymentVO.getPaymentID());

        Collection col = cvdal.executeQuery();

        cvdal.clearParameters();

        Iterator itk = col.iterator();

        while (itk.hasNext())

        {

          HashMap hm =(HashMap)itk.next();

          float Amount = ((Number)hm.get("Amount")).floatValue();

          cvdal.setSql("account.payment.updatepaymentamount");

          cvdal.setFloat(1,Amount);

          cvdal.setInt(2,paymentVO.getPaymentID());

          cvdal.executeUpdate();

          cvdal.clearParameters();

        }

      }

      catch(Exception e)

      {

        e.printStackTrace();

      }

      finally

      {

        cvdal.destroy();

        cvdal = null;

        this.isDirty = false;

      }

    }// end of if

  }// end of ejbStore

  public PaymentVO getPaymentVO()

  {

    ejbLoad();

    setPaymentInvoices();

    return this.paymentVO;

  }// end of getPaymentVO

  /*

   * set the Payment Form

   */

  public void setPaymentVO(PaymentVO paymentVO,int userID)

  {

    // begin :: adding for fieldlevel rights...

    PaymentVO paymentDBVO = getPaymentVO();

    paymentVO = (PaymentVO)CVUtility.replaceVO(paymentDBVO, paymentVO, "Payment", userID, this.dataSource);

    // end :: adding for fieldlevel rights...

    this.paymentVO = paymentVO;

    this.isDirty = true;

  }// end of setPaymentVO

  /**

   * @author Kevin McAllister <kevin@centraview.com>

   * This simply sets the target datasource to be used for DB interaction

   * @param ds A string that contains the cannonical JNDI name of the datasource

   */

   public void setDataSource(String ds) {

     this.dataSource = ds;

   }

}