@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;
}