* $RCSfile: AccountListEJB.java,v $    $Revision: 1.3 $  $Date: 2005/07/18 21:04:54 $ - $Author: mcallist $

*

* 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.

*/

  private static Logger logger = Logger.getLogger(AccountListEJB.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()

  {

  }

  /**

   * Gets the Whole list of GLAccounts

   *

   * @param   individualID

   * @param   hashmap

   * @return

   */

  public GLAccountList getGLAccountList(int individualID, HashMap hashmap) throws AuthorizationFailedException

  {

    if (!CVUtility.isModuleVisible("GLAccount", individualID, this.dataSource))

    {

      throw new AuthorizationFailedException("GLAccount- getGLAccountList");

    }

    Integer intStart = (Integer) hashmap.get("startATparam");

    Integer intEnd = (Integer) hashmap.get("EndAtparam");

    String strSearch = (String) hashmap.get("searchString");

    String strSortMem = (String) hashmap.get("sortmem");

    Character chrSortType = (Character) hashmap.get("sortType");

    char charSort = chrSortType.charValue();

    int intStartParam = intStart.intValue();

    int intEndParam = intEnd.intValue();

    int beginIndex = 0;

    GLAccountList glList = new GLAccountList();

    glList.setSortMember(strSortMem);

    CVDal cvdl = new CVDal(dataSource);

    Collection colList = null;

    String sortType = "ASC";

    if (charSort == 'D')

    {

      sortType = "DESC";

    }

    //SELECT gla.glaccountsid GLAccountsID,gla.title Name ,gla.accounttype Type ,gla.Balance Balance ,gla1.title ParentAccount FROM glaccount gla left outer join glaccount gla1 on gla.parent = gla1.glaccountsid

    String selectQuery =

      " SELECT gla.glaccountsid GLAccountsID,gla.title Name ,gla.glaccounttype Type ,gla.Balance Balance ,gla1.title ParentAccount ";

    String fromQuery =

      " FROM glaccount gla left outer join glaccount gla1 on gla.parent = gla1.glaccountsid";

    String whereQuery = " where 1=1 ";

    String groupByQuery =

      " group by GLAccountsID,Name,Type,Balance,ParentAccount";

    String orderByQuery = " order by '" + strSortMem + "' " + sortType;

    String listQuery =

      selectQuery + fromQuery + whereQuery + groupByQuery + orderByQuery;

    if ((strSearch != null) && strSearch.startsWith("SIMPLE :"))

    {

      strSearch = strSearch.substring(8);

      whereQuery =

        whereQuery + "AND  gla.glaccountsid like '%" + strSearch

        + "%'   OR gla.title like '%" + strSearch

        + "%' OR gla.accounttype like  '%" + strSearch

        + "%' OR gla.Balance like  '%" + strSearch + "%' OR gla1.title like '%"

        + strSearch + "%' ";

    }

    String advSearchstr = strSearch;

    if ((advSearchstr != null) && advSearchstr.startsWith("ADVANCE:"))

    {

      advSearchstr = advSearchstr.substring(8);

      cvdl.setSqlQueryToNull();

      cvdl.setSqlQuery("DROP TABLE IF EXISTS `glaccountlistsearch`;");

      cvdl.executeUpdate();

      cvdl.clearParameters();

      strSearch = "create TEMPORARY TABLE glaccountlistsearch " + advSearchstr;

      cvdl.setSqlQuery(strSearch);

      cvdl.executeUpdate();

      cvdl.setSqlQueryToNull();

      cvdl.clearParameters();

      fromQuery = fromQuery + " ,glaccountlistsearch";

      whereQuery =

        whereQuery + " AND gla.glaccountsid = glaccountlistsearch.glaccountsid";

    }

    listQuery =

      selectQuery + fromQuery + whereQuery + groupByQuery + orderByQuery;

    cvdl.setSqlQuery(listQuery);

    colList = cvdl.executeQuery();

    cvdl.clearParameters();

    if ((advSearchstr != null) && advSearchstr.startsWith("ADVANCE:"))

    {

      cvdl.setSqlQueryToNull();

      cvdl.setSqlQuery("DROP TABLE glaccountlistsearch");

      cvdl.executeUpdate();

      cvdl.clearParameters();

    }

    cvdl.destroy();

    if (colList != null)

    {

      Iterator it = colList.iterator();

      int i = 0;

      while (it.hasNext())

      {

        i++;

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

        int glAccID = ((Long) hm.get("GLAccountsID")).intValue();

        String strGLAccountID = new String();

        strGLAccountID = String.valueOf(glAccID);

        try

        {

          StringMember strName = null;

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

          {

            strName =

              new StringMember("Name", (String) hm.get("Name"), 10, "", 'T',

                false);

          }

          else

          {

            strName = new StringMember("Name", null, 10, "", 'T', false);

          }

          StringMember strType =

            new StringMember("Type", (String) hm.get("Type"), 10, "", 'T', false);

          DoubleMember dblBalance =

            new DoubleMember("Balance",

              new Double(Double.parseDouble(hm.get("Balance").toString())), 10,

              "", 'T', false, 10);

          StringMember strParentAccount =

            new StringMember("ParentAccount", (String) hm.get("ParentAccount"),

              10, "", 'T', false);

          GLAccountListElement glListElement =

            new GLAccountListElement(glAccID);

          glListElement.put("GLAccountsID", strGLAccountID);

          glListElement.put("Name", strName);

          glListElement.put("Type", strType);

          glListElement.put("Balance", dblBalance);

          glListElement.put("ParentAccount", strParentAccount);

          StringBuffer stringbuffer = new StringBuffer("00000000000");

          stringbuffer.setLength(11);

          String s3 = (new Integer(i)).toString();

          stringbuffer.replace(stringbuffer.length() - s3.length(),

            stringbuffer.length(), s3);

          String s4 = stringbuffer.toString();

          glList.put(s4, glListElement);

        }

        catch (Exception e)

        {

          System.out.println(" Exception in packing data " + e);

          //e.printStackTrace();

        }

      }

    }

    glList.setTotalNoOfRecords(glList.size());

    glList.setListType("GLAccount");

    glList.setBeginIndex(beginIndex);

    //filelist.setEndIndex(endIndex);

    glList.setEndIndex(glList.size());

    return glList;

  }

   // getGLAccountList ends here

  /**

   * Gets the Whole list of Inventory

   *

   * @param   individualID

   * @param   hashmap

   * @return

   */

  public InventoryList getInventoryList(int individualID, HashMap hashmap) throws AuthorizationFailedException

  {

    if (!CVUtility.isModuleVisible("Inventory", individualID, this.dataSource))

    {

      throw new AuthorizationFailedException("Inventory- getInventoryList");

    }

    Integer intStart = (Integer) hashmap.get("startATparam");

    Integer intEnd = (Integer) hashmap.get("EndAtparam");

    String strSearch = (String) hashmap.get("searchString");

    String strSortMem = (String) hashmap.get("sortmem");

    Character chrSortType = (Character) hashmap.get("sortType");

    char charSort = chrSortType.charValue();

    int intStartParam = intStart.intValue();

    int intEndParam = intEnd.intValue();

    int beginIndex = 0;

    InventoryList invList = new InventoryList();

    invList.setSortMember(strSortMem);

    CVDal cvdl = new CVDal(dataSource);

    Collection colList = null;

    String sortType = "ASC";

    if (charSort == 'D')

    {

      sortType = "DESC";

    }

    String selectQuery =

      " select ven.entityid as EntityID,inventory.inventoryid as InventoryID,item.title as ItemName,inventory.title as Identifier  ,man.name as Manufacturer,ven.name as Vendor";

    String fromQuery =

      " from inventory  left outer join item on inventory.item = item.itemid left outer join entity man on item.manufacturerid = man.entityid left outer join entity ven on item.vendorid  = ven.entityid";

    String whereQuery = " where inventory.linestatus != 'deleted' ";

    String groupByQuery =

      " group by EntityID,InventoryID,ItemName,Identifier,Manufacturer,Vendor";

    String orderByQuery = " order by '" + strSortMem + "' " + sortType;

    String listQuery =

      selectQuery + fromQuery + whereQuery + groupByQuery + orderByQuery;

    if ((strSearch != null) && strSearch.startsWith("SIMPLE :"))

    {

      strSearch = strSearch.substring(8);

      whereQuery =

        whereQuery + "AND  inventory.inventoryid like '%" + strSearch

        + "%'   OR item.title like '%" + strSearch

        + "%' OR inventory.title like  '%" + strSearch

        + "%' OR man.name like  '%" + strSearch + "%' OR ven.name like '%"

        + strSearch + "%' ";

    }

    String advSearchstr = strSearch;

    if ((advSearchstr != null) && advSearchstr.startsWith("ADVANCE:"))

    {

      advSearchstr = advSearchstr.substring(8);

      cvdl.setSqlQueryToNull();

      cvdl.setSqlQuery("DROP TABLE IF EXISTS `inventorylistsearch`;");

      cvdl.executeUpdate();

      cvdl.clearParameters();

      strSearch = "create TEMPORARY TABLE inventorylistsearch " + advSearchstr;

      cvdl.setSqlQuery(strSearch);

      cvdl.executeUpdate();

      cvdl.setSqlQueryToNull();

      cvdl.clearParameters();

      fromQuery = fromQuery + " ,inventorylistsearch";

      whereQuery =

        whereQuery

        + " AND inventory.inventoryid = inventorylistsearch.inventoryid";

    }

    listQuery =

      selectQuery + fromQuery + whereQuery + groupByQuery + orderByQuery;

    cvdl.setSqlQuery(listQuery);

    colList = cvdl.executeQuery();

    cvdl.clearParameters();

    if ((advSearchstr != null) && advSearchstr.startsWith("ADVANCE:"))

    {

      cvdl.setSqlQueryToNull();

      cvdl.setSqlQuery("DROP TABLE inventorylistsearch");

      cvdl.executeUpdate();

      cvdl.clearParameters();

    }

    cvdl.destroy();

    if (colList != null)

    {

      Iterator it = colList.iterator();

      int i = 0;

      while (it.hasNext())

      {

        i++;

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

        int invID = ((Long) hm.get("InventoryID")).intValue();

        int entityID = 0;

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

        {

          entityID = ((Long) hm.get("EntityID")).intValue();

        }

        try

        {

          IntMember intInvID =

            new IntMember("InventoryID", invID, 10, null, 'T', true, 10);

          IntMember intEntityID =

            new IntMember("EntityID", entityID, 10, null, 'T', false, 10);

          StringMember strItemName = null;

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

          {

            strItemName =

              new StringMember("ItemName", (String) hm.get("ItemName"), 10,

                null, 'T', false);

          }

          else

          {

            strItemName =

              new StringMember("ItemName", null, 10, null, 'T', false);

          }

          StringMember strIdentifier =

            new StringMember("Identifier", (String) hm.get("Identifier"), 10,

              null, 'T', false);

          StringMember strManufacturer =

            new StringMember("Manufacturer", (String) hm.get("Manufacturer"),

              10, null, 'T', false);

          StringMember strVendor =

            new StringMember("Vendor", (String) hm.get("Vendor"), 10, "", 'T',

              true);

          InventoryListElement invListElement = new InventoryListElement(invID);

          invListElement.put("InventoryID", intInvID);

          invListElement.put("ItemName", strItemName);

          invListElement.put("Identifier", strIdentifier);

          invListElement.put("Manufacturer", strManufacturer);

          invListElement.put("Vendor", strVendor);

          invListElement.put("EntityID", intEntityID);

          StringBuffer stringbuffer = new StringBuffer("00000000000");

          stringbuffer.setLength(11);

          String s3 = (new Integer(i)).toString();

          stringbuffer.replace(stringbuffer.length() - s3.length(),

            stringbuffer.length(), s3);

          String s4 = stringbuffer.toString();

          invList.put(s4, invListElement);

        }

        catch (Exception e)

        {

          System.out.println(" Exception in packing data " + e);

          //e.printStackTrace();

        }

      }

    }

    invList.setTotalNoOfRecords(invList.size());

    invList.setListType("Inventory");

    invList.setBeginIndex(beginIndex);

    //invList.setEndIndex(endIndex);

    invList.setEndIndex(invList.size());

    return invList;

  }

   // getInventoryList ends here

  /**

   * Gets the Whole list of Item

   *

   * @param   individualID

   * @param   hashmap

   * @return

   */

  public ItemList getItemList(int individualID, HashMap hashmap) throws AuthorizationFailedException

  {

    if (!CVUtility.isModuleVisible("OrderHistory", individualID, this.dataSource))

    {

      throw new AuthorizationFailedException("Item- getItemList");

    }

    Integer intStart = (Integer) hashmap.get("startATparam");

    Integer intEnd = (Integer) hashmap.get("EndAtparam");

    String strSearch = (String) hashmap.get("searchString");

    String strSortMem = (String) hashmap.get("sortmem");

    Character chrSortType = (Character) hashmap.get("sortType");

    char charSort = chrSortType.charValue();

    int intStartParam = intStart.intValue();

    int intEndParam = intEnd.intValue();

    int beginIndex = 0;

    Float f = new Float(5);

    ItemList list = new ItemList();

    list.setSortMember(strSortMem);

    CVDal cvdl = new CVDal(dataSource);

    Collection colList;

    String sortType= "";

    if (charSort == 'D')

    {

          sortType = "DESC";

    }

    String appendSearchCondition ="";

    if ((strSearch != null) && strSearch.startsWith("ADVANCE:"))

    {

      int searchIndex = (strSearch.toUpperCase()).indexOf("WHERE");

      appendSearchCondition = " and "+strSearch.substring((searchIndex+5),strSearch.length());

    }

  String itemQuery = " select item.itemid,item.sku,item.title as Name ,itemtype.title as type ,item.listprice as Price ,count(inventory.qty) as OnHand ,taxclass.taxclassid as taxclassid ,taxclass.title as taxclass,item.cost ,"+

             " ven.entityid as vendorid,ven.name as vendor,man.entityid as manid, man.name as manufacturer  from  item left outer join itemtype on item.type = itemtype.itemtypeid "+

             " left outer join inventory on item.itemid = inventory.item left outer join taxclass on item.taxclass = taxclass.taxclassid "+

             " left outer join entity man on item.manufacturerid = man.entityid   left outer join entity ven on item.vendorid = ven.entityid "+

             " where item.deletestatus != 'Deleted' "+appendSearchCondition+" Group by item.itemid,item.sku,item.title,itemtype.title,item.listprice, taxclass.title,item.cost order by '"+ strSortMem +"' "+sortType;

  cvdl.setSqlQuery(itemQuery);

    colList = cvdl.executeQuery();

    cvdl.clearParameters();

    cvdl.destroy();

    if (colList != null)

    {

      Iterator it = colList.iterator();

      int i = 0;

      while (it.hasNext())

      {

        i++;

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

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