* $RCSfile: AccountHelperEJB.java,v $    $Revision: 1.1.1.1 $  $Date: 2005/04/28 20:21:26 $ - $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.

*/

This is the EJB class for Accounting

The Logic for methods defined in Remote interface

is defined in this class

*/

  protected SessionContext ctx;

  private String dataSource = "MySqlDS";

  public void setSessionContext(SessionContext ctx)

  {

    this.ctx=ctx;

  }

  /**

   *

   *

   */

  public void ejbCreate()

  {

  }

  /**

   *

   *

   */

  public void ejbRemove()

  {

  }

  /**

   *

   *

   */

  public void ejbActivate()

  {

  }

  /**

   *

   *

   */

  public void ejbPassivate()

  {

  }

  /** returns Item Types in vector

    * @return Vector

    */

  public Vector getItemTypes()

  {

    Vector vec = new Vector();

    CVDal dl = new CVDal(dataSource);

    try

    {

      dl.setSqlQueryToNull();

        dl.setSql("account.helper.getitemtypes");

      Collection  col  = (Collection)dl.executeQuery();

      Iterator it = col.iterator();

      if (col != null)

      {

        while (it.hasNext())

        {

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

          String name  = (String)hm.get("ItemType");

          int id     = ((Long)hm.get("ID")).intValue();

          DDNameValue dd = new DDNameValue(id,name);

          vec.add(dd);

        }

      }

    }

    catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.getItemTypes()] Exception thrown: "+e);

      e.printStackTrace();

    }

    finally

    {

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }

    return vec;

  }

  /**

   * returns GLAccounts in vector

   * @return Vector

   */

  public Vector getGLAccounts()

  {

    Vector arl = new Vector();

    CVDal dl = new CVDal(dataSource);

    try

    {

      dl.setSqlQueryToNull();

      dl.setSql("account.helper.getglaccounts");

      Collection  col  = (Collection)dl.executeQuery();

      Iterator it = col.iterator();

      if (col != null)

      {

        while (it.hasNext())

        {

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

          String name  = (String)hm.get("GLAccount");

          int id     = ((Long)hm.get("ID")).intValue();

          DDNameValue dd = new DDNameValue(id,name);

          arl.add(dd);

        }

      }

    }catch(Exception e){

      System.out.println("[Exception][AccountHelperEJB.getGLAccounts()] Exception thrown: "+e);

      e.printStackTrace();

    }finally{

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }

    return arl;

  }   // end getGLAccounts() method

        /** returns new GLAccounts in vector

          * @return Vector

          */

        public Vector getNewGLAccounts()

        {

                Vector arl = new Vector();

                CVDal dl = new CVDal(dataSource);

                try

                {

                        dl.setSqlQueryToNull();

                        dl.setSql("account.helper.getnewglaccounts");

                        Collection  col  = (Collection)dl.executeQuery();

                        Iterator it = col.iterator();

                        HashMap hm = new HashMap();

                        String name = "";

                        int id   = 0;

                        String description = "";

                        float balance = 0;

                        String type = "";

                        int parent = 0;

                        String externalId = "";

                        GLAccountVO glaVO = new GLAccountVO();

                        if (col != null)

                        {

                                while (it.hasNext())

                                {

                                  glaVO = new GLAccountVO();

                                  name = "";

                                  description = "";

                                  externalId = "";

                                  hm = (HashMap)it.next();

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

                                  {

                                    name = hm.get("GLAccount").toString();

                                  }

                                  id = Integer.parseInt(hm.get("ID").toString());

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

                                  {

                                    description = hm.get("Description").toString();

                                  }

                                  balance = Float.parseFloat(hm.get("Balance").toString());

                                  type = hm.get("Type").toString();

                                  parent = Integer.parseInt(hm.get("Parent").toString());

                                  glaVO.setBalance(balance);

                                  glaVO.setDescription(description);

                                  glaVO.setGlaccountID(id);

                                  glaVO.setExternalID(externalId);

                                  glaVO.setGLAccountType(type);

                                  glaVO.setParentAccountID(parent);

                                  glaVO.setTitle(name);

                                  arl.add(glaVO);

                                }

                        }

                }

                catch(Exception e)

                {

                        System.out.println("[Exception][AccountHelperEJB.getGLAccounts()] Exception thrown: "+e);

                        e.printStackTrace();

                }

                finally

                {

                        dl.clearParameters();

                        dl.destroy();

                        dl = null;

                }

                return arl;

        }

  /**

   * Returns the Account Locations in vector

   *

   * @return Vector of DDName Values

   */

  public Vector getLocations()

  {

    Vector vec = new Vector();

    CVDal dl = new CVDal(dataSource);

    try

    {

      dl.setSqlQueryToNull();

      dl.setSqlQuery("SELECT locationid, title FROM location");

      Collection  col  = (Collection)dl.executeQuery();

      Iterator it = col.iterator();

      if (col != null)

      {

        while (it.hasNext())

        {

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

          String name = (String) hm.get("title");

          int id    = ((Number) hm.get("locationid")).intValue();

          DDNameValue dd = new DDNameValue(id,name);

          vec.add(dd);

        }

      }

    }

    catch(Exception e)

    {

      System.out.println("[Exception] AccountHelperEJB.getLocations: " + e.toString());

      //e.printStackTrace();

    }

    finally

    {

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }

    return vec;

  }

  /** Add the GLAccount in DataBase

  * Note: This method is kept for to be Majorly used from

  *    Syncronization module only.

  *    Users are not supposed to Add or Modify any

  *    Account directly as it can affect the whole

  *    System in dangerous manner.

  */

  public void addGLAccount(GLAccountVO glaVO)

  {

    CVDal dl = new CVDal(dataSource);

    try

    {

      dl.setSqlQueryToNull();

      dl.setSql("account.helper.addglaccount");

      //ALLSQL.put("account.helper.addglaccount",

      //"insert into glaccount(title,description,balance,

      // glaccounttype,parent,externalid) values(?,?,?,?,?,?)");

      dl.setString(1,glaVO.getTitle());

      dl.setString(2,glaVO.getDescription());

      dl.setFloat(3,glaVO.getBalance());

      dl.setString(4,glaVO.getGLAccountType());

      dl.setInt(5,glaVO.getParentAccountID());

      dl.setString(6,glaVO.getExternalID());

      dl.executeUpdate();

      dl.destroy();

    }catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.addGLAccount()] Exception thrown: "+e);

      e.printStackTrace();

    }

    finally

    {

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }

    return;

  }

        public String deleteObjects(String modulename, ArrayList arl){

          CVDal dl = new CVDal(dataSource);

          String query = "";

          String ids = "";

          char zap = ' ';

          Collection col = null;

          Iterator it = arl.iterator();

          while(it.hasNext())

          {

            query += zap + Integer.parseInt(it.next().toString());

            zap = ',';

          }

          zap = ' ';

          try

          {

            if (modulename.equals("Individual"))

            {

              dl.setSql("account.helper.getEmployeesByExternalIds");

              dl.setString(1,query);

              col = dl.executeQuery();

              it = col.iterator();

              while(it.hasNext())

              {

                ids += zap + Integer.parseInt(it.next().toString());

                zap = ',';

              }

              dl.setSql("account.helper.deleteEmployee");

              dl.setString(1,ids);

              dl.executeUpdate();

            }else{

              dl.setSql("account.helper.delete" + modulename);

              dl.setString(1, query);

              dl.executeUpdate();

            }

          }catch(Exception e)

          {

            System.out.println("[Exception][AccountHelperEJB.updateGLAccount()] Exception thrown: "+e);

            e.printStackTrace();

          }

          finally

          {

              dl.clearParameters();

              dl.destroy();

              dl = null;

          }

          return query;

        }

  public void updateGLAccount(GLAccountVO glaVO)

  {

    //ALLSQL.put("account.helper.updateglaccount",

    //"update glaccount set title = ?,description = ?,

    // balance = ?,accounttype = ?,parent = ?,externalid = ?

    // where glaccountid = ?");

    CVDal dl = new CVDal(dataSource);

    try

    {

      dl.setSql("account.helper.updateglaccount");

      dl.setString(1,glaVO.getTitle());

      dl.setString(2,glaVO.getDescription());

      dl.setFloat(3,glaVO.getBalance());

      dl.setString(4,glaVO.getGLAccountType());

      dl.setInt(5,glaVO.getParentAccountID());

      dl.setString(6,glaVO.getExternalID());

      dl.setInt(7,glaVO.getGlaccountID());

      dl.executeUpdate();

      dl.destroy();

    }catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.updateGLAccount()] Exception thrown: "+e);

      e.printStackTrace();

    }

    finally

    {

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }

  }

  public void addPaymentMethod(PaymentMethodVO pmVO)

  {

    //ALLSQL.put("account.helper.addPaymentMethod",

    // "insert into paymentmethod(title,externalid)

    // values(?,?)");

    CVDal dl = new CVDal(dataSource);

    try

    {

      dl.setSql("account.helper.addPaymentMethod");

      dl.setString(1,pmVO.getTitle());

      dl.setString(2,pmVO.getExternalID());

      dl.executeUpdate();

      dl.destroy();

    }catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.addPaymentMethod()] Exception thrown: "+e);

      e.printStackTrace();

    }

    finally

    {

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }

  }

  public void savePaymentMethod(PaymentMethodVO pmVO)

  {

    try

    {

      CVDal dl = new CVDal(dataSource);

      dl.setSqlQueryToNull();

                        // try to find payment method by title

                dl.setSqlQuery("SELECT MethodID FROM paymentmethod WHERE UPPER(Title)=UPPER(?) AND ExternalId<>?");

                        dl.setString(1,pmVO.getTitle());

                        dl.setString(2,pmVO.getExternalID());

      Iterator it = ((Collection)dl.executeQuery()).iterator();

                        dl.setSqlQueryToNull();

      if (it.hasNext())

      {

                           // such payment method exists, update it

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

                           int id     = ((Long)hm.get("MethodID")).intValue();

                           dl.setSql("account.helper.updatePaymentMethod");

                           dl.setInt(3,id);

      }

                        else {

                            // add new payment method

                            dl.setSql("account.helper.addPaymentMethod");

                        }

                        dl.setString(1,pmVO.getTitle());

                        dl.setString(2,pmVO.getExternalID());

                        dl.executeUpdate();

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.updatePaymentMethod()] Exception thrown: "+e);

      e.printStackTrace();

    }

  }

  public void updatePaymentMethod(PaymentMethodVO pmVO)

  {

    //ALLSQL.put("account.helper.updatePaymentMethod",

    // "update paymentmethod set title = ?,

    // externalid = ? where methodid = ?");

    try

    {

      CVDal dl = new CVDal(dataSource);

      dl.setSql("account.helper.updatePaymentMethod");

      dl.setString(1,pmVO.getTitle());

      dl.setString(2,pmVO.getExternalID());

      dl.setInt(3,pmVO.getMethodID());

      dl.executeUpdate();

      dl.destroy();

    }catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.updatePaymentMethod()] Exception thrown: "+e);

      e.printStackTrace();

    }

  }

  /** returns Tax Classes in vector

    * @return Vector

    */

  public Vector getTaxClasses()

  {

    Vector vec = new Vector();

    CVDal dl = new CVDal(dataSource);

    try

    {

      dl.setSqlQueryToNull();

        dl.setSql("account.helper.gettaxclasses");

      Collection  col  = (Collection)dl.executeQuery();

      Iterator it = col.iterator();

      if (col != null)

      {

        while (it.hasNext())

        {

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

          String name  = (String)hm.get("TaxClass");

          int id     = Integer.parseInt(hm.get("ID").toString());

          DDNameValue dd = new DDNameValue(id,name);

          vec.add(dd);

        }

      }

    }

    catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.getTaxClasses()] Exception thrown: "+e);

      e.printStackTrace();

    }

    finally

    {

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }

    return vec;

  }

  /** returns Account Status in vector

    * @return Vector

    */

  public Vector getAccountingStatus()

  {

    Vector vec = new Vector();

    CVDal dl = new CVDal(dataSource);

    try

    {

      dl.setSqlQueryToNull();

        dl.setSql("account.helper.getaccountingstatus");

      Collection  col  = (Collection)dl.executeQuery();

      Iterator it = col.iterator();

      if (col != null)

      {

        while (it.hasNext())

        {

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

          String name  = (String)hm.get("AccountingStatus");

          int id     = ((Long)hm.get("ID")).intValue();

          DDNameValue dd = new DDNameValue(id,name);

          vec.add(dd);

        }

      }

    }

    catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.getAccountingStatus()] Exception thrown: "+e);

      e.printStackTrace();

    }

    finally

    {

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }

    return vec;

  }

  /** returns Accounting Terms in vector

    * @return Vector

    */

  public Vector getAccountingTerms()

  {

    Vector vec = new Vector();

    CVDal dl = new CVDal(dataSource);

    try

    {

      dl.setSqlQueryToNull();

        dl.setSql("account.helper.getaccountingterms");

      Collection  col  = (Collection)dl.executeQuery();

      Iterator it = col.iterator();

      if (col != null)

      {

        while (it.hasNext())

        {

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

          String name  = (String)hm.get("AccountingTerms");

          int id     = ((Long)hm.get("ID")).intValue();

          DDNameValue dd = new DDNameValue(id,name);

          vec.add(dd);

        }

      }

    }

    catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.getAccountingTerms] Exception Thrown: " + e);

      e.printStackTrace();

    }

    finally

    {

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }

    return vec;

  }

  /** returns Payment Methods in vector

    * @return Vector

    */

  public Vector getPaymentMethods()

  {

    Vector arl = new Vector();

    CVDal dl = new CVDal(dataSource);

    try

    {

      dl.setSqlQueryToNull();

        dl.setSql("account.helper.getpaymentmethods");

      Collection  col  = (Collection)dl.executeQuery();

      Iterator it = col.iterator();

      if (col != null)

      {

        while (it.hasNext())

        {

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

          String name  = (String)hm.get("PaymentMethod");

          int id     = ((Long)hm.get("ID")).intValue();

          DDNameValue dd = new DDNameValue(id,name);

          arl.add(dd);

        }

      }

    }

    catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.getPaymentMethods] Exception Thrown: "+e);

      e.printStackTrace();

    }

    finally

    {

      dl.clearParameters();

      dl.destroy();

      dl = null;

    }

    return arl;

  }

        /** returns new Payment Methods in vector

          * @return Vector

          */

        public Vector getNewPaymentMethods()

        {

                Vector arl = new Vector();

                CVDal dl = new CVDal(dataSource);

                try

                {

                        dl.setSqlQueryToNull();

                    dl.setSql("account.helper.getnewpaymentmethods");

                        Collection  col  = (Collection)dl.executeQuery();

                        Iterator it = col.iterator();

            HashMap hm  = new HashMap();

            PaymentMethodVO pvo = new PaymentMethodVO();

            String name  = "";

            int id = 0;

            String externalId = "";

                        if (col != null)

                        {

                                while (it.hasNext())

                                {

                hm = (HashMap)it.next();

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

                {

                  name = hm.get("PaymentMethod").toString();

                }

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

                {

                  id = Integer.parseInt(hm.get("ID").toString());

                }

                arl.add(new PaymentMethodVO(id,name,externalId));

                                }

                        }

                }

                catch(Exception e)

                {

                        System.out.println("[Exception][AccountHelperEJB.getPaymentMethods] Exception Thrown: "+e);

                        e.printStackTrace();

                }

                finally

                {

                        dl.clearParameters();

                        dl.destroy();

                        dl = null;

                }

                return arl;

        }

    /* This method returns all invocies for the given entity

   * @param int entityID

   * @return PaymentLines , invocies for the entity

   */

  public PaymentLines getPaymentInvoices(int entityID)

  {

    CVDal dl = new CVDal(dataSource);

    PaymentLines paymentLines= new PaymentLines();

    try

    {

      String q1 = " create temporary table  temppayinv 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 = " insert into temppayinv ( invoiceid, invoicetitle, invoicedate, invoicetotal,amountdue ) select invoice.InvoiceID,invoice.title ,invoice.invoicedate, invoice.total ,invoice.total   from invoice where  invoice.customerid = ? group by invoice.InvoiceID,invoice.title ,invoice.invoicedate, invoice.total";

      String q3 = " create temporary table t1 select  applypayment.invoiceid, sum(applypayment.amount) as AmountApplied from temppayinv ,applypayment where temppayinv.invoiceid = applypayment.invoiceid  and applypayment.linestatus != 'Deleted' group by  applypayment.invoiceid ";

      //String q4 = " create temporary table t2 select  temppayinv.invoiceid, (temppayinv.invoicetotal -  sum( applypayment.amount ) ) as AmountDue from applypayment ,temppayinv where  applypayment.invoiceid = temppayinv.invoiceid and applypayment.linestatus != 'Deleted' group by  temppayinv.invoiceid ";

      String q5 = " update  temppayinv ,t1 set temppayinv.AmountApplied   = t1.AmountApplied   where temppayinv.invoiceid = t1.invoiceid ";

      //String q6 = " update  temppayinv ,t2 set temppayinv.amountdue = t2.AmountDue  where temppayinv.invoiceid = t2.invoiceid ";

      String q7 = "select invoiceid,invoicetitle,invoicedate,invoicetotal,amountdue,amountapplied from temppayinv where amountapplied = 0";

      dl.setSqlQuery(q1);

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery(q2);

      dl.setInt(1,entityID);

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery(q3);

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      /*dl.setSqlQuery(q4);

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      */

      dl.setSqlQuery(q5);

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.executeUpdate();

      dl.setSqlQueryToNull();*/

      dl.setSqlQuery(q7);

      Collection col = dl.executeQuery();

      dl.setSqlQueryToNull();

      dl.setSqlQuery("drop table temppayinv");

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      dl.setSqlQuery("drop table t1");

      dl.executeUpdate();

      dl.setSqlQueryToNull();

      Iterator it = col.iterator();

      int count = 1;

      while (it.hasNext())

      {

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

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

        IntMember invoiceID = new IntMember("InvoiceId",Integer.parseInt(hm.get("invoiceid").toString()),10,"",'T',false,20);

        StringMember invoiceNum = null;

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

        invoiceNum = new StringMember("InvoiceNum",((Number) hm.get("invoiceid")).toString(),10,"",'T',false);

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

                                DoubleMember dblTotal = null;

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

          dblTotal = new DoubleMember( "Total"  ,new Double(Double.parseDouble(hm.get("invoicetotal").toString())) , 10 , "", 'T' , false , 10 );

        DoubleMember dblAppAmt = null;

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

          dblAppAmt = new DoubleMember( "AmountApplied"  ,new Double(Double.parseDouble(hm.get("amountapplied").toString())) , 10 , "", 'T' , false , 10 );

        DoubleMember dblAmtDue = null;

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

          dblAmtDue = new DoubleMember( "AmountDue"  ,new Double(Double.parseDouble(hm.get("amountdue").toString())) , 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");

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

        count ++;

      }

    }

    catch(Exception e)

    {

      System.out.println("[Exception][AccountHelperEJB.getPaymentInvoices] Exception Thrown: "+e);

      e.printStackTrace();

    }

    finally

    {

      dl.destroy();

      dl = null;

    }

    return paymentLines;

  }

  public void addVendor(int entityID,int userID)

   {

     CVDal dl = new CVDal(dataSource);

     try

     {

       dl.setSql("vendor.addvendor");

       //insert into vendor (entityid) values (?)

       dl.setInt(1,entityID); //entityid

       dl.executeUpdate();

     }

     catch(Exception e)

     {

       System.out.println("[Exception][AccountHelperEJB.addVendor] Exception Thrown: "+e);

       e.printStackTrace();

     }

     finally

     {

       dl.destroy();

       dl = null;

     }

   }// end of addVendor

   public void deleteVendor(int entityID,int userID)

   {

     CVDal dl = new CVDal(dataSource);

     try

     {

       dl.setSql("vendor.deletevendor");

       //delete from  vendor where entityid  = ?

       dl.setInt(1,entityID); //entityid

       dl.executeUpdate();

     }

     catch(Exception e)

     {

       System.out.println("[Exception][AccountHelperEJB.deleteVendor] Exception Thrown: "+e);

       e.printStackTrace();

     }

     finally

     {

       dl.destroy();

       dl = null;

     }

   }// end of deleteVendor

  /**

   * Returns Tax in float for a given Tax Class & Jurisdiction.

   *

   * @param taxClassId The tax class id.

   * @param taxJurisdictionId The tax jusrisdiction id.

   *

   * @return The tax value.

   */

  public float getTax(int taxClassId, int taxJurisdictionId)

  {

    CVDal dl = new CVDal(dataSource);

    float returnFloat = 0.0f;

    try

    {

      dl.setSqlQueryToNull();

      dl.setSql("account.tax.gettax");

      dl.setInt(1,taxClassId);

      dl.setInt(2,taxJurisdictionId);

      Collection  col  = (Collection) dl.executeQuery();

      if (col != null)

      {

        if (!col.isEmpty())

        {

          Iterator it = col.iterator();

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

          returnFloat  = Float.parseFloat(hm.get( "TaxRate" ).toString());

        } //end of if statement (!col.isEmpty())

      } //end of if statement (col != null)

    } //end of try block

    catch (Exception e)

    {

      System.out.println("[Exception]: AccountHelperEJB.getTax: " + e.toString());

      e.printStackTrace();

    }  //end of catch block (Exception)

    finally

    {

      dl.destroy();

      dl = null;

    } //end of finally block

    return returnFloat;

  }  //end of getTax method

  // TODO rewrite the calculateInvoiceItems, calculateOrderItems and calculatePurchaseOrderItems

  // These methods all do basically the same thing.  They should be rewritten to be three

  // small methods which call a private method which does the work in a generalized way, any

  // type specific code can be in the smaller methods.

  /*

  *  Add Items to the invoice from Comma Separated String into the ItemLines

  */

  public InvoiceVO calculateInvoiceItems(int userId, InvoiceVO invoiceVO, String newItemID)

  {

    //CVDal dl = new CVDal(dataSource);

    StringTokenizer st;

    String token, nextItr;

    ItemLines itemLines = invoiceVO.getItemLines();

    if(itemLines == null)

      itemLines = new ItemLines();

    int counter = itemLines.size();

    if (counter!=0)

    {

      Set s = itemLines.keySet();

      Iterator itr = s.iterator();

      while(itr.hasNext() )

      {

        float taxRate = 0.0f;

        //ItemElement ie = (ItemElement)itr.next();

        ItemElement ie = (ItemElement)itemLines.get(itr.next());

        int id = ((Integer)((IntMember)ie.get("ItemId")).getMemberValue()).intValue();

        try

        {

          InitialContext ic = CVUtility.getInitialContext();

          ItemLocalHome home = (ItemLocalHome)ic.lookup("local/Item");

          ItemLocal itemLocal = home.create();

          itemLocal.setDataSource(this.dataSource);

          ItemVO item = itemLocal.getItem(userId,id);

          int taxClassId = item.getTaxClassId();

          int taxJurisdictionId = 0;

          if (invoiceVO.getShipToId()!=0)

          {

            ContactHelperLocalHome home1 = ( ContactHelperLocalHome )ic.lookup("local/ContactHelper");

            ContactHelperLocal contactHelperLocal = home1.create();

            contactHelperLocal.setDataSource(this.dataSource);

            AddressVO addVO = contactHelperLocal.getAddress(invoiceVO.getShipToId());

            taxJurisdictionId = 0;

            if (taxJurisdictionId !=0)

            {

              taxRate = getTax(taxClassId, taxJurisdictionId);

            }//if (taxJurisdictionId !=0)

          }//if InvoiceVO.shipToId!=null

          ((FloatMember)ie.get("UnitTaxrate")).setMemberValue(taxRate);

        }catch (Exception e)

        {

          ((FloatMember)ie.get("UnitTaxrate")).setMemberValue(0.0f);

        }

      }//while

    }//if counter!=0

    if (newItemID != null && !newItemID.equals("") )

      {

        st = new StringTokenizer(newItemID, ",");

        while (st.hasMoreTokens())

        {

          try

          {

          float taxRate = 0.0f;

          token   = (String)st.nextToken();

          int intToken = Integer.parseInt(token);

          InitialContext ic = CVUtility.getInitialContext();

          ItemLocalHome home = (ItemLocalHome)ic.lookup("local/Item");

          ItemLocal itemLocal = home.create();

          itemLocal.setDataSource(this.dataSource);

          ItemVO item = itemLocal.getItem(userId,intToken);

          //Get the Required Fields from the Item VO

          String name = item.getItemName();

          String sku = item.getSku();

          float price = (float)item.getPrice();

          int id = item.getItemId();

          int taxClassId = item.getTaxClassId();

          int taxJurisdictionId = 0;

          if (invoiceVO.getShipToId()!=0)

          {

            try

            {

            ContactHelperLocalHome home2 =( ContactHelperLocalHome)ic.lookup("local/ContactHelper");

            ContactHelperLocal contactHelperLocal = home2.create();

            contactHelperLocal.setDataSource(this.dataSource);

            AddressVO addVO = contactHelperLocal.getAddress(invoiceVO.getShipToId());

            taxJurisdictionId = 0;

            if (taxJurisdictionId !=0)

            {

              taxRate = getTax(taxClassId, taxJurisdictionId);

            }//if (taxJurisdictionId !=0)

            }catch(Exception e)

              {

                System.out.println("[Exception][AccountHelperEJB.calculateInvoiceItems] Exception Thrown: "+e);

                e.printStackTrace();

              }

          }//if shipToId !=null

          //Form the ItemLines

              counter += 1;

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

              IntMember ItemId = new IntMember("ItemId",id,10,"",'T',false,20);

              StringMember SKU = new StringMember("SKU",sku,10,"",'T',false);

              StringMember Description = new StringMember("Description",name,10,"",'T',false);

              FloatMember  Quantity = new FloatMember("Quantity",new Float(1.0f),10,"",'T',false,20);

              FloatMember  PriceEach = new FloatMember("PriceEach",new Float(price),10,"",'T',false,20);

              FloatMember  PriceExtended = new FloatMember("PriceExtended",new Float(0.0f),10,"",'T',false,20);

              FloatMember  UnitTax = new FloatMember("UnitTax",new Float(0.0f),10,"",'T',false,20);

              FloatMember  TaxRate = new FloatMember("UnitTaxrate",new Float(taxRate),10,"",'T',false,20);

              FloatMember  OrderQuantity = new FloatMember("OrderQuantity",new Float(1.0f),10,"",'T',false,20);

              FloatMember  PendingQuantity = new FloatMember("PendingQuantity",new Float(0.0f),10,"",'T',false,20);

              ItemElement ie = new ItemElement(counter);