/* (non-Javadoc)
   * @see com.krawler.spring.crm.accountModule.crmAccountDAO#getAccountOwners(java.util.HashMap, java.util.ArrayList, java.util.ArrayList)
   */
  public KwlReturnObject getAccountOwners(
      HashMap<String, Object> requestParams, ArrayList filter_names, ArrayList filter_params)
      throws ServiceException {
    List ll = null;
    int dl = 0;
    try {
      String Hql = "";
      if (requestParams.containsKey("usersList") && requestParams.get("usersList") != null) {
        Hql =
            " select distinct c from accountOwners ao inner join ao.account c where c.crmLead.usersByCreatedbyid = c.usersByCreatedbyid.userID ";
        Hql += StringUtil.filterQuery(filter_names, "and");
        StringBuffer usersList = (StringBuffer) requestParams.get("usersList");
        Hql += " and ao.usersByUserid.userID in (" + usersList + ") ";
      } else {
        boolean distinctFlag = false;
        if (requestParams.containsKey("distinctFlag")
            && requestParams.get("distinctFlag") != null) {
          distinctFlag = (Boolean) requestParams.get("distinctFlag");
        }

        if (distinctFlag) {
          Hql = "select distinct c from accountOwners ao inner join ao.account c ";
        } else {
          Hql = "select ao, c from accountOwners ao inner join ao.account c ";
        }
        Hql += StringUtil.filterQuery(filter_names, "where");
      }

      ll = executeQuery(Hql, filter_params.toArray());
      dl = ll.size();
      boolean pagingFlag = false;
      if (requestParams.containsKey("pagingFlag") && requestParams.get("pagingFlag") != null) {
        pagingFlag = Boolean.parseBoolean(requestParams.get("pagingFlag").toString());
      }
      if (pagingFlag) {
        int start = 0;
        int limit = 25;
        if (requestParams.containsKey("start") && requestParams.containsKey("limit")) {
          start = Integer.parseInt(requestParams.get("start").toString());
          limit = Integer.parseInt(requestParams.get("limit").toString());
        }
        ll = executeQueryPaging(Hql, filter_params.toArray(), new Integer[] {start, limit});
      }
    } catch (Exception ex) {
      throw ServiceException.FAILURE("crmAccountDAOImpl.getAccountOwners : " + ex.getMessage(), ex);
    }
    return new KwlReturnObject(true, KWLErrorMsgs.S01, "", ll, dl);
  }
  /* (non-Javadoc)
   * @see com.krawler.spring.crm.accountModule.crmAccountDAO#getAccountProducts(java.util.ArrayList, java.util.ArrayList)
   */
  public KwlReturnObject getAccountProducts(ArrayList filter_names, ArrayList filter_params)
      throws ServiceException {
    List ll = null;
    int dl = 0;
    try {
      String Hql = "from AccountProducts ap ";
      String filterQuery = StringUtil.filterQuery(filter_names, "where");
      Hql += filterQuery;
      String selectInQuery = Hql;

      ll = executeQuery(selectInQuery, filter_params.toArray());
      dl = ll.size();
    } catch (Exception ex) {
      throw ServiceException.FAILURE(
          "crmAccountDAOImpl.getAccountProducts : " + ex.getMessage(), ex);
    }
    return new KwlReturnObject(true, KWLErrorMsgs.S01, "", ll, dl);
  }
 /* (non-Javadoc)
  * @see com.krawler.spring.crm.accountModule.crmAccountDAO#getAccountRevenue(java.util.HashMap, java.util.ArrayList, java.util.ArrayList)
  */
 public double getAccountRevenue(
     HashMap<String, Object> requestParams, ArrayList filter_names, ArrayList filter_params)
     throws ServiceException {
   List ll = null;
   double revenue = 0.0;
   try {
     String hql = "select distinct c.accountid from accountOwners ao inner join ao.account c ";
     hql += StringUtil.filterQuery(filter_names, "where");
     hql = " select sum(ca.revenue) from CrmAccount ca where ca.accountid in(" + hql + ")";
     ll = executeQuery(hql, filter_params.toArray());
     if (ll != null && ll.size() > 0) {
       revenue = ll.get(0) == null ? 0 : Double.parseDouble((String) ll.get(0));
     }
   } catch (Exception ex) {
     throw ServiceException.FAILURE("crmAccountDAOImpl.getAccountOwners : " + ex.getMessage(), ex);
   }
   return revenue;
 }
  // ******************* Account Project *************************
  @Override
  public KwlReturnObject getAccountProjectDetails(ArrayList filter_names, ArrayList filter_params)
      throws ServiceException {
    List ll = null;
    int dl = 0;
    try {
      String Hql = " Select c FROM AccountProject c ";
      int start = 0;
      int limit = 10;
      String filterQuery = StringUtil.filterQuery(filter_names, "where");
      Hql += filterQuery;

      //            ll = executeQuery( Hql, filter_params.toArray());
      //            dl = ll.size();
      ll = executeQueryPaging(Hql, filter_params.toArray(), new Integer[] {start, limit});
    } catch (Exception e) {
      logger.warn(e.getMessage(), e);
      throw ServiceException.FAILURE("crmAccountDAOImpl.getAccounts : " + e.getMessage(), e);
    }
    return new KwlReturnObject(true, KWLErrorMsgs.S01, "", ll, dl);
  }
  /* (non-Javadoc)
   * @see com.krawler.spring.crm.accountModule.crmAccountDAO#getCrmAccountCustomData(java.util.HashMap)
   */
  public KwlReturnObject getCrmAccountCustomData(HashMap<String, Object> requestParams)
      throws ServiceException {
    List ll = null;
    int dl = 0;
    try {
      ArrayList filter_names = new ArrayList();
      ArrayList filter_params = new ArrayList();

      if (requestParams.containsKey("filter_names")) {
        filter_names = new ArrayList((List<String>) requestParams.get("filter_names"));
      }
      if (requestParams.containsKey("filter_values")) {
        filter_params = new ArrayList((List<String>) requestParams.get("filter_values"));
      }

      String Hql = "from CrmAccountCustomData ";

      String filterQuery = StringUtil.filterQuery(filter_names, "where");
      int ind = filterQuery.indexOf("(");
      if (ind > -1) {
        int index = Integer.valueOf(filterQuery.substring(ind + 1, ind + 2));
        filterQuery =
            filterQuery.replaceAll("(" + index + ")", filter_params.get(index).toString());
        filter_params.remove(index);
      }
      Hql += filterQuery;

      ll = executeQueryPaging(Hql, filter_params.toArray(), new Integer[] {0, 1});
      dl = ll.size();
    } catch (Exception e) {
      e.printStackTrace();
      throw ServiceException.FAILURE(
          "crmAccountDAOImpl.getCrmAccountCustomData : " + e.getMessage(), e);
    }
    return new KwlReturnObject(true, KWLErrorMsgs.S01, "", ll, dl);
  }
  /* (non-Javadoc)
   * @see com.krawler.spring.crm.accountModule.crmAccountDAO#getAccounts(java.util.HashMap)
   */
  public KwlReturnObject getAccounts(HashMap<String, Object> requestParams)
      throws ServiceException {
    List ll = null;
    int dl = 0;
    try {
      ArrayList filter_names = new ArrayList();
      ArrayList filter_params = new ArrayList();

      if (requestParams.containsKey("filter_names")) {
        filter_names = (ArrayList) requestParams.get("filter_names");
      }
      if (requestParams.containsKey("filter_params")) {
        filter_params = (ArrayList) requestParams.get("filter_params");
      }

      String Hql = "select distinct c from CrmAccount c ";
      if (filter_names.contains("INp.productId.productid")) {
        Hql += " inner join c.crmProducts as p ";
      }
      String filterQuery = StringUtil.filterQuery(filter_names, "where");
      int ind = filterQuery.indexOf("(");
      if (ind > -1) {
        int index = Integer.valueOf(filterQuery.substring(ind + 1, ind + 2));
        filterQuery =
            filterQuery.replaceAll("(" + index + ")", filter_params.get(index).toString());
        filter_params.remove(index);
      }
      Hql += filterQuery;

      ll = executeQuery(Hql, filter_params.toArray());
      dl = ll.size();
    } catch (Exception e) {
      throw ServiceException.FAILURE("crmAccountDAOImpl.getActiveAccount : " + e.getMessage(), e);
    }
    return new KwlReturnObject(true, "002", "", ll, dl);
  }
  /* (non-Javadoc)
   * @see com.krawler.spring.crm.accountModule.crmAccountDAO#getAllAccounts(java.util.HashMap, java.util.ArrayList, java.util.ArrayList)
   */
  public KwlReturnObject getAllAccounts(
      HashMap<String, Object> requestParams, ArrayList filter_names, ArrayList filter_params)
      throws ServiceException {
    List ll = null;
    int dl = 0;
    try {
      String Hql = "select distinct c from accountOwners ao inner join ao.account c ";
      //            String Hql = "select c from CrmAccount c ";
      String filterQuery = StringUtil.filterQuery(filter_names, "where");
      Hql += filterQuery;
      int ind = Hql.indexOf("(");
      if (ind > -1) {
        int index = Integer.valueOf(Hql.substring(ind + 1, ind + 2));
        Hql = Hql.replaceAll("(" + index + ")", filter_params.get(index).toString());
        filter_params.remove(index);
      }

      if (requestParams.containsKey("ss") && requestParams.get("ss") != null) {
        String ss = StringEscapeUtils.escapeJavaScript(requestParams.get("ss").toString());
        if (!StringUtil.isNullOrEmpty(ss)) {
          String[] searchcol = new String[] {"c.accountname"};
          StringUtil.insertParamSearchString(filter_params, ss, 1);
          String searchQuery = StringUtil.getSearchString(ss, "and", searchcol);
          Hql += searchQuery;
        }
      }

      String selectInQuery = Hql;
      boolean pagingFlag = false;
      if (requestParams.containsKey("pagingFlag") && requestParams.get("pagingFlag") != null) {
        pagingFlag = Boolean.parseBoolean(requestParams.get("pagingFlag").toString());
      }
      boolean countFlag = true;
      if (requestParams.containsKey("countFlag") && requestParams.get("countFlag") != null) {
        countFlag = Boolean.parseBoolean(requestParams.get("countFlag").toString());
      }

      ArrayList order_by = null;
      ArrayList order_type = null;
      if (requestParams.containsKey("order_by"))
        order_by = (ArrayList) requestParams.get("order_by");
      if (requestParams.containsKey("order_type"))
        order_type = (ArrayList) requestParams.get("order_type");
      String orderQuery = StringUtil.orderQuery(order_by, order_type);
      if (StringUtil.isNullOrEmpty(orderQuery)) {
        orderQuery = " order by c.accountname ";
      }
      selectInQuery += orderQuery;

      if (countFlag) {
        ll = executeQuery(selectInQuery, filter_params.toArray());
        dl = ll.size();
      }
      if (pagingFlag) {
        int start = 0;
        int limit = 25;
        if (requestParams.containsKey("start") && requestParams.containsKey("limit")) {
          start = Integer.parseInt(requestParams.get("start").toString());
          limit = Integer.parseInt(requestParams.get("limit").toString());
        }
        ll =
            executeQueryPaging(
                selectInQuery, filter_params.toArray(), new Integer[] {start, limit});
        if (!countFlag) {
          dl = ll.size();
        }
      }
    } catch (Exception e) {
      throw ServiceException.FAILURE("crmAccountDAOImpl.getAllAccounts : " + e.getMessage(), e);
    }
    return new KwlReturnObject(true, KWLErrorMsgs.S01, "", ll, dl);
  }