@Override
 public int recordCounter() {
   String tableName = "tbl_crm_lead";
   operationStatus = dbu.clientCounter(tableName, 0);
   if (operationStatus >= Limits.max_limit) return 2;
   return 0;
 }
 @Override
 public String listTask(String leadId) {
   String query =
       "select userId, GROUP_CONCAT(taskid SEPARATOR ',') as taskid from tbl_crm_lead_task where leadId = ? GROUP BY userId;";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setString(1, leadId);
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in listTask in LeadHelper", e);
   }
   ResultSet rs = dbu.select(preparedStatement);
   List<String> allTaskResultList = new ArrayList<String>();
   try {
     while (rs.next()) {
       List<String> result = new ArrayList<String>();
       result = JSPUtil.fetchTaskByUser(rs.getString("userId"), rs.getString("taskid"));
       for (String task : result) {
         allTaskResultList.add(task);
       }
     }
   } catch (ServiceException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in Lead Helper Class", e);
   } catch (AuthTokenException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in Lead Helper Class", e);
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in Lead Helper Class", e);
   } catch (Exception e) {
     ZLog.err("VNC CRM for Zimbra", "Error in Lead Helper Class", e);
   }
   return allTaskResultList.toString();
 }
 @Override
 public int recordCounter() {
   String tableName = "tbl_crm_company";
   operationStatus = dbu.adminCounter(tableName);
   if (operationStatus >= Limits.max_limit) return 2;
   return 0;
 }
 @Override
 public String listSharedItems(String leadId) {
   List<SharedItemBean> returnValue = new ArrayList<SharedItemBean>();
   String query = "select * from tbl_crm_share where leadId = ?; ";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setString(1, leadId);
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in listSharedItems in LeadHelper", e);
   }
   ResultSet rs = dbu.select(preparedStatement);
   SharedItemBean sharedItemBean = null;
   try {
     while (rs.next()) {
       sharedItemBean = new SharedItemBean();
       sharedItemBean.setLeadId(rs.getInt("leadId"));
       sharedItemBean.setUserId(rs.getString("userId"));
       sharedItemBean.setWriteAccess(rs.getBoolean("writeAccess"));
       returnValue.add(sharedItemBean);
     }
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error listSharedItems result set in Lead Helper Class", e);
   }
   return gson.toJson(returnValue);
 }
 @Override
 public List<AbstractBean> getAllActiveRecords() {
   List<AbstractBean> retValue = new ArrayList<AbstractBean>();
   String query = "select * from tbl_crm_company where status = ?;";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setBoolean(1, true);
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in getting all active records in CompanyHelper", e);
   }
   ResultSet rs = dbu.select(preparedStatement);
   CompanyBean companyBean = null;
   try {
     while (rs.next()) {
       companyBean = new CompanyBean();
       companyBean.setCompanyId(rs.getInt("companyId"));
       companyBean.setCompanyName(rs.getString("companyName"));
       companyBean.setCompanyAddress(rs.getString("companyAddress"));
       companyBean.setCompanyPhone(rs.getString("companyPhone"));
       companyBean.setCompanyFax(rs.getString("companyFax"));
       companyBean.setCompanyEmail(rs.getString("companyEmail"));
       companyBean.setStatus(rs.getBoolean("status"));
       companyBean.setCreateBy(rs.getString("createBy"));
       companyBean.setCreateDate(rs.getString("createDate"));
       companyBean.setWriteBy(rs.getString("writeBy"));
       companyBean.setWriteDate(rs.getString("writeDate"));
       retValue.add(companyBean);
     }
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in Company Helper Class", e);
   }
   return retValue;
 }
 @Override
 public int add(AbstractBean ab) {
   LeadBean leadBean = (LeadBean) ab;
   String query =
       "insert into tbl_crm_lead values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setInt(1, leadBean.getLeadId());
     preparedStatement.setString(2, leadBean.getSubjectName());
     preparedStatement.setString(3, leadBean.getLeadDescription());
     preparedStatement.setString(4, leadBean.getContactName());
     preparedStatement.setString(5, leadBean.getCompanyId());
     preparedStatement.setString(6, leadBean.getValuation());
     preparedStatement.setString(7, leadBean.getLeadState());
     preparedStatement.setString(8, leadBean.getLeadClassId());
     preparedStatement.setString(9, leadBean.getPartnerName());
     preparedStatement.setString(10, leadBean.getPhone());
     preparedStatement.setString(11, leadBean.getFax());
     preparedStatement.setString(12, leadBean.getEmail());
     preparedStatement.setString(13, leadBean.getWorkPhone());
     preparedStatement.setString(14, leadBean.getMobile());
     preparedStatement.setString(15, leadBean.getStreet1());
     preparedStatement.setString(16, leadBean.getStreet2());
     preparedStatement.setString(17, leadBean.getCity());
     preparedStatement.setString(18, leadBean.getZip());
     preparedStatement.setString(19, leadBean.getStateId());
     preparedStatement.setString(20, leadBean.getCountryId());
     preparedStatement.setString(21, leadBean.getType());
     preparedStatement.setString(22, leadBean.getDateOpen());
     preparedStatement.setString(23, leadBean.getDateClose());
     preparedStatement.setString(24, leadBean.getExpectedDateClose());
     preparedStatement.setString(25, leadBean.getStageId());
     preparedStatement.setString(26, leadBean.getProbability());
     preparedStatement.setString(27, leadBean.getChannelId());
     preparedStatement.setString(28, leadBean.getSectionId());
     preparedStatement.setString(29, leadBean.getCategoryId());
     preparedStatement.setString(30, leadBean.getDayClose());
     preparedStatement.setString(31, leadBean.getDayOpen());
     preparedStatement.setString(32, leadBean.getReferredBy());
     preparedStatement.setString(33, leadBean.getUserId());
     preparedStatement.setString(34, leadBean.getPriorityId());
     preparedStatement.setString(35, leadBean.getNextActionDate());
     preparedStatement.setString(36, leadBean.getNextAction());
     preparedStatement.setBoolean(37, leadBean.isStatus());
     preparedStatement.setString(38, leadBean.getCreateBy());
     preparedStatement.setTimestamp(39, new Timestamp(System.currentTimeMillis()));
     preparedStatement.setString(40, leadBean.getWriteBy());
     preparedStatement.setTimestamp(41, new Timestamp(System.currentTimeMillis()));
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in insert operation in LeadHelper", e);
   }
   operationStatus = dbu.insert(preparedStatement);
   if (operationStatus == 1) {
     return Notification.record_saved;
   } else {
     return Notification.record_not_saved;
   }
 }
 @Override
 public int update(AbstractBean ab) {
   LeadBean leadBean = (LeadBean) ab;
   String query =
       "update tbl_crm_lead set subjectName = ?, leadDescription= ?, contactName = ?, companyId = ?, valuation = ?, leadState = ?, leadClassId = ?, partnerName = ?, phone = ?, fax = ?, email = ?, workPhone = ?, mobile = ?, street1 = ?, street2 = ?, city = ?, zip = ?, stateId = ?, countryId = ?, type = ?, dateOpen = ?, dateClose = ?, expectedDateClose = ?, stageId = ?, probability = ?, channelId = ?, sectionId = ?, categoryId = ?, dayClose = ?, dayOpen = ?, referredBy = ?, userId = ?, priorityId = ?, nextActionDate = ?, nextAction = ?, writeBy = ?, writeDate = ? where leadId = ?;";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setString(1, leadBean.getSubjectName());
     preparedStatement.setString(2, leadBean.getLeadDescription());
     preparedStatement.setString(3, leadBean.getContactName());
     preparedStatement.setString(4, leadBean.getCompanyId());
     preparedStatement.setString(5, leadBean.getValuation());
     preparedStatement.setString(6, leadBean.getLeadState());
     preparedStatement.setString(7, leadBean.getLeadClassId());
     preparedStatement.setString(8, leadBean.getPartnerName());
     preparedStatement.setString(9, leadBean.getPhone());
     preparedStatement.setString(10, leadBean.getFax());
     preparedStatement.setString(11, leadBean.getEmail());
     preparedStatement.setString(12, leadBean.getWorkPhone());
     preparedStatement.setString(13, leadBean.getMobile());
     preparedStatement.setString(14, leadBean.getStreet1());
     preparedStatement.setString(15, leadBean.getStreet2());
     preparedStatement.setString(16, leadBean.getCity());
     preparedStatement.setString(17, leadBean.getZip());
     preparedStatement.setString(18, leadBean.getStateId());
     preparedStatement.setString(19, leadBean.getCountryId());
     preparedStatement.setString(20, leadBean.getType());
     preparedStatement.setString(21, leadBean.getDateOpen());
     preparedStatement.setString(22, leadBean.getDateClose());
     preparedStatement.setString(23, leadBean.getExpectedDateClose());
     preparedStatement.setString(24, leadBean.getStageId());
     preparedStatement.setString(25, leadBean.getProbability());
     preparedStatement.setString(26, leadBean.getChannelId());
     preparedStatement.setString(27, leadBean.getSectionId());
     preparedStatement.setString(28, leadBean.getCategoryId());
     preparedStatement.setString(29, leadBean.getDayClose());
     preparedStatement.setString(30, leadBean.getDayOpen());
     preparedStatement.setString(31, leadBean.getReferredBy());
     preparedStatement.setString(32, leadBean.getUserId());
     preparedStatement.setString(33, leadBean.getPriorityId());
     preparedStatement.setString(34, leadBean.getNextActionDate());
     preparedStatement.setString(35, leadBean.getNextAction());
     preparedStatement.setString(36, leadBean.getWriteBy());
     preparedStatement.setTimestamp(37, new Timestamp(System.currentTimeMillis()));
     preparedStatement.setInt(38, leadBean.getLeadId());
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in update operation in LeadHelper", e);
   }
   operationStatus = dbu.insert(preparedStatement);
   if (operationStatus == 1) {
     return Notification.record_update;
   } else {
     return Notification.record_not_update;
   }
 }
 @Override
 public int deleteSharedItems(String leadId) {
   String query = "delete from tbl_crm_share where leadId = ?; ";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setString(1, leadId);
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in deleteSharedItems in LeadHelper", e);
   }
   operationStatus = dbu.delete(preparedStatement);
   return operationStatus;
 }
 @Override
 public AbstractBean getRecordByName(String name) {
   String query = "select * from tbl_crm_company where companyName = ?;";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setString(1, name);
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in recordByName in CompanyHelper", e);
   }
   ResultSet rs = dbu.select(preparedStatement);
   return (getRecordFromResultSet(rs));
 }
 @Override
 public int delete(AbstractBean ab) {
   CompanyBean companyBean = (CompanyBean) ab;
   String query = "delete from tbl_crm_company where companyId = ?;";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setInt(1, companyBean.getCompanyId());
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in delete operation in CompanyHelper", e);
   }
   operationStatus = dbu.delete(preparedStatement);
   return operationStatus;
 }
 @Override
 public int deleteTask(String array, String leadId) {
   String query = "delete from tbl_crm_lead_task where leadId = ? and taskId IN (" + array + "); ";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setString(1, leadId);
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in deleteTask in LeadHelper", e);
   }
   operationStatus = dbu.delete(preparedStatement);
   if (operationStatus == 1) {
     return Notification.task_detached;
   } else {
     return Notification.task_not_detached;
   }
 }
 @Override
 public int deleteByIds(String arrayIds, String user) {
   String query =
       "update tbl_crm_company set status = ?, writeBy = ?, writeDate = ? where companyId IN ("
           + arrayIds
           + ");";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setBoolean(1, false);
     preparedStatement.setString(2, user);
     preparedStatement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in deleteByIds in CompanyHelper", e);
   }
   operationStatus = dbu.delete(preparedStatement);
   return operationStatus;
 }
 @Override
 public int delete(AbstractBean ab) {
   LeadBean leadBean = (LeadBean) ab;
   String query = "delete from tbl_crm_lead where leadId = ?;";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setInt(1, leadBean.getLeadId());
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in delete operation in LeadHelper", e);
   }
   operationStatus = dbu.delete(preparedStatement);
   if (operationStatus == 1) {
     return Notification.record_delete;
   } else {
     return Notification.record_not_delete;
   }
 }
 @Override
 public boolean checkWriteAccess(String leadId, String userId) {
   String query = "select writeAccess from tbl_crm_share where leadId = ? AND userId = ?; ";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setString(1, leadId);
     preparedStatement.setString(2, userId);
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in deleteSharedItems in LeadHelper", e);
   }
   ResultSet rs = dbu.select(preparedStatement);
   try {
     while (rs.next()) {
       return rs.getBoolean("writeAccess");
     }
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error listSharedItems result set in Lead Helper Class", e);
   }
   return true;
 }
 @Override
 public int addTask(String array, String leadId, String userId) {
   String[] str = array.split(",");
   for (String taskId : str) {
     String query = "insert into tbl_crm_lead_task values (?,?,?); ";
     try {
       preparedStatement = DBUtility.connection.prepareStatement(query);
       preparedStatement.setString(1, leadId);
       preparedStatement.setString(2, taskId);
       preparedStatement.setString(3, userId);
     } catch (SQLException e) {
       ZLog.err("VNC CRM for Zimbra", "Error in addTask in LeadHelper", e);
     }
     operationStatus = dbu.insert(preparedStatement);
   }
   if (operationStatus == 1) {
     return Notification.task_attach;
   } else {
     return Notification.task_not_attach;
   }
 }
 @Override
 public int addSharedItems(String userArray, String accessArray, String leadId) {
   String[] users = userArray.split(",");
   String[] wAccess = accessArray.split(",");
   for (int i = 0; i < users.length; i++) {
     String query = "insert into tbl_crm_share values (?,?,?); ";
     try {
       preparedStatement = DBUtility.connection.prepareStatement(query);
       preparedStatement.setString(1, leadId);
       preparedStatement.setString(2, users[i]);
       preparedStatement.setString(3, wAccess[i]);
     } catch (SQLException e) {
       ZLog.err("VNC CRM for Zimbra", "Error in addShareItems in LeadHelper", e);
     }
     operationStatus = dbu.insert(preparedStatement);
   }
   if (operationStatus == 1) {
     return Notification.record_shared;
   } else {
     return Notification.record_not_shared;
   }
 }
 @Override
 public int update(AbstractBean ab) {
   CompanyBean companyBean = (CompanyBean) ab;
   String query =
       "update tbl_crm_company set companyName = ?, companyAddress = ?, companyPhone = ?, companyFax = ?, companyEmail = ?, status = ?, writeBy = ?, writeDate = ? where companyId = ?;";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setString(1, companyBean.getCompanyName());
     preparedStatement.setString(2, companyBean.getCompanyAddress());
     preparedStatement.setString(3, companyBean.getCompanyPhone());
     preparedStatement.setString(4, companyBean.getCompanyFax());
     preparedStatement.setString(5, companyBean.getCompanyEmail());
     preparedStatement.setBoolean(6, companyBean.isStatus());
     preparedStatement.setString(7, companyBean.getWriteBy());
     preparedStatement.setTimestamp(8, new Timestamp(System.currentTimeMillis()));
     preparedStatement.setInt(9, companyBean.getCompanyId());
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in update operation in CompanyHelper", e);
   }
   operationStatus = dbu.update(preparedStatement);
   return operationStatus;
 }
 @Override
 public int add(AbstractBean ab) {
   CompanyBean companyBean = (CompanyBean) ab;
   String query = "insert into tbl_crm_company values (?,?,?,?,?,?,?,?,?,?,?);";
   try {
     preparedStatement = DBUtility.connection.prepareStatement(query);
     preparedStatement.setInt(1, companyBean.getCompanyId());
     preparedStatement.setString(2, companyBean.getCompanyName());
     preparedStatement.setString(3, companyBean.getCompanyAddress());
     preparedStatement.setString(4, companyBean.getCompanyPhone());
     preparedStatement.setString(5, companyBean.getCompanyFax());
     preparedStatement.setString(6, companyBean.getCompanyEmail());
     preparedStatement.setBoolean(7, companyBean.isStatus());
     preparedStatement.setString(8, companyBean.getCreateBy());
     preparedStatement.setTimestamp(9, new Timestamp(System.currentTimeMillis()));
     preparedStatement.setString(10, companyBean.getWriteBy());
     preparedStatement.setTimestamp(11, new Timestamp(System.currentTimeMillis()));
   } catch (SQLException e) {
     ZLog.err("VNC CRM for Zimbra", "Error in insert operation in CompanyHelper", e);
   }
   operationStatus = dbu.insert(preparedStatement);
   return operationStatus;
 }
  @Override
  public List<AbstractBean> getAllActiveFilterRecords(String array, String field, String username) {
    List<AbstractBean> retValue = new ArrayList<AbstractBean>();
    String query =
        "select * from tbl_crm_lead where type = 0 and status = ? and userId = ? and "
            + field
            + " IN ("
            + array
            + ") UNION select * from tbl_crm_lead where leadId IN (select leadId from tbl_crm_share where userId = ?) and type = 0 and status = ? and "
            + field
            + " IN ("
            + array
            + ");";
    try {
      preparedStatement = DBUtility.connection.prepareStatement(query);
      preparedStatement.setBoolean(1, true);
      preparedStatement.setString(2, username);
      preparedStatement.setString(3, username);
      preparedStatement.setBoolean(4, true);
    } catch (SQLException e) {
      ZLog.err("VNC CRM for Zimbra", "Error in getting all active records in LeadHelper", e);
    }
    ResultSet rs = dbu.select(preparedStatement);
    LeadBean leadBean = null;
    CountryHelper countryHelper = new CountryHelper();
    StateHelper stateHelper = new StateHelper();
    ChannelHelper channelHelper = new ChannelHelper();
    SectionHelper sectionHelper = new SectionHelper();
    CategoryHelper categoryHelper = new CategoryHelper();
    PriorityHelper priorityHelper = new PriorityHelper();
    StageHelper stageHelper = new StageHelper();
    CompanyHelper companyHelper = new CompanyHelper();
    LeadClassHelper leadClassHelper = new LeadClassHelper();

    try {
      while (rs.next()) {
        leadBean = new LeadBean();
        leadBean.setLeadId(rs.getInt("leadId"));
        leadBean.setSubjectName(rs.getString("subjectName"));
        leadBean.setLeadDescription(rs.getString("leadDescription"));
        leadBean.setContactName(rs.getString("contactName"));
        leadBean.setCompanyBean(
            (CompanyBean) (companyHelper.getRecordById(rs.getString("companyId"))));
        leadBean.setValuation(rs.getString("valuation"));
        leadBean.setLeadState(rs.getString("leadState"));
        leadBean.setLeadClassBean(
            (LeadClassBean) (leadClassHelper.getRecordById(rs.getString("leadClassId"))));
        leadBean.setPartnerName(rs.getString("partnerName"));
        leadBean.setPhone(rs.getString("phone"));
        leadBean.setFax(rs.getString("fax"));
        leadBean.setEmail(rs.getString("email"));
        leadBean.setWorkPhone(rs.getString("workPhone"));
        leadBean.setMobile(rs.getString("mobile"));
        leadBean.setStreet1(rs.getString("street1"));
        leadBean.setStreet2(rs.getString("street2"));
        leadBean.setCity(rs.getString("city"));
        leadBean.setZip(rs.getString("zip"));
        leadBean.setCountryBean(
            (CountryBean) (countryHelper.getRecordById(rs.getString("countryId"))));
        leadBean.setStateBean((StateBean) (stateHelper.getRecordById(rs.getString("stateId"))));
        leadBean.setChannelBean(
            (ChannelBean) (channelHelper.getRecordById(rs.getString("channelId"))));
        leadBean.setPriorityBean(
            (PriorityBean) (priorityHelper.getRecordById(rs.getString("priorityId"))));
        leadBean.setStageBean((StageBean) (stageHelper.getRecordById(rs.getString("stageId"))));
        leadBean.setCategoryBean(
            (CategoryBean) (categoryHelper.getRecordById(rs.getString("categoryId"))));
        leadBean.setSectionBean(
            (SectionBean) (sectionHelper.getRecordById(rs.getString("sectionId"))));
        leadBean.setType(rs.getString("type"));
        leadBean.setDateOpen(rs.getString("dateOpen"));
        leadBean.setDateClose(rs.getString("dateClose"));
        leadBean.setExpectedDateClose(rs.getString("expectedDateClose"));
        leadBean.setProbability(rs.getString("probability"));
        leadBean.setDayClose(rs.getString("dayClose"));
        leadBean.setDayOpen(rs.getString("dayOpen"));
        leadBean.setReferredBy(rs.getString("referredBy"));
        leadBean.setUserId(rs.getString("userId"));
        leadBean.setNextActionDate(rs.getString("nextActionDate"));
        leadBean.setNextAction(rs.getString("nextAction"));
        leadBean.setStatus(rs.getBoolean("status"));
        leadBean.setCreateBy(rs.getString("createBy"));
        leadBean.setCreateDate(rs.getString("createDate"));
        leadBean.setWriteBy(rs.getString("writeBy"));
        leadBean.setWriteDate(rs.getString("writeDate"));
        retValue.add(leadBean);
      }
    } catch (SQLException e) {
      ZLog.err("VNC CRM for Zimbra", "Error in Lead Helper Class", e);
    }
    return retValue;
  }