From 3b74e3df72726e188d36393ecfd7964d095ef7e8 Mon Sep 17 00:00:00 2001 From: xinyb <574600396@qq.com> Date: 星期一, 30 九月 2024 11:38:39 +0800 Subject: [PATCH] CRM邮箱推送和拉取 --- src/com/yc/crm/mail/service/MailAccountImpl.java | 179 +++++++++++++++++++++++++++++++++++++++++++++++++---------- 1 files changed, 149 insertions(+), 30 deletions(-) diff --git a/src/com/yc/crm/mail/service/MailAccountImpl.java b/src/com/yc/crm/mail/service/MailAccountImpl.java index f550851..d995f2c 100644 --- a/src/com/yc/crm/mail/service/MailAccountImpl.java +++ b/src/com/yc/crm/mail/service/MailAccountImpl.java @@ -6,6 +6,7 @@ import org.apache.commons.lang3.StringUtils; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.stereotype.Service; +import org.springframework.transaction.annotation.Transactional; import java.util.List; @@ -18,42 +19,120 @@ */ @Service public class MailAccountImpl extends BaseService implements MailAccountIfc { + @Transactional(rollbackFor = Exception.class) @Override - public void addEmailAccount(T482102Entity account) { + public T482102Entity addEmailAccount(T482102Entity account) { String sql = "set nocount on\n"; try { - sql += "insert into t482102(companyId,companyName,email,password,alias_email,userCode,userName,receiveProtocol,receiveSSL,receivePort,receiveHost,smtpSSL,smtpPort,smtpHost,invalid,create_time,update_time)\n" + + sql+="declare @hasMail varchar(200)\n" + + "select @hasMail=isnull(email,'') from t482102 where email=" +GridUtils.prossSqlParm(account.getEmail())+ + "if @hasMail !=''\n" + + "begin\n" + + "raiserror('閭574600396@qq.com宸茬粡缁戝畾杩囷紝涓嶈兘閲嶅缁戝畾',16,1)\n" + + "return\n" + + "end \n"; + sql += "insert into t482102(companyId,companyName,email,password,alias_email,userCode,userName,receiveProtocol," + + "receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost," + + "smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost,invalid,bisync_flag,proxyFlag,mailType,mailStatus,create_time,update_time)\n" + "values(" + GridUtils.prossSqlParm(account.getCompanyId()) + "," + GridUtils.prossSqlParm(account.getCompanyName()) + "," + GridUtils.prossSqlParm(account.getEmail()) + "," + GridUtils.prossSqlParm(account.getPassword()) + "," + GridUtils.prossSqlParm(account.getAliasEmail()) + "," + GridUtils.prossSqlParm(account.getUserCode()) + "," + GridUtils.prossSqlParm(account.getUserName()) + "," + - GridUtils.prossSqlParm(account.getReceiveProtocol()) + ",'" + account.isReceiveSSL() + "'," + account.getReceivePort() + "," + GridUtils.prossSqlParm(account.getReceiveHost()) + ",'" + - account.isSmtpSSL() + "'," + account.getSmtpPort() + "," + GridUtils.prossSqlParm(account.getSmtpHost()) + ",'" + account.isInvalid() + "'," + - "convert(varchar(19),getdate(),120),convert(varchar(19),getdate(),120))"; - jdbcTemplate.update(sql); + GridUtils.prossSqlParm(account.getReceiveProtocol()) + "," + GridUtils.prossSqlParm(account.getReceiveEmail()) + "," + GridUtils.prossSqlParm(account.getReceivePassword()) + "," + + "'" + account.isReceiveSSL() + "'," + account.getReceivePort() + "," + GridUtils.prossSqlParm(account.getReceiveHost()) + "," + + GridUtils.prossSqlParm(account.getSmtpEmail()) + "," + GridUtils.prossSqlParm(account.getSmtpPassword()) + ",'" + account.isSmtpSSL() + "'," + + account.getSmtpPort() + "," + GridUtils.prossSqlParm(account.getSmtpHost()) + ",'" + account.isInvalid() + "','" + account.isBiSyncFlag() + "','" + account.isProxyFlag() + "'," + + account.getMailType() + "," + GridUtils.prossSqlParm(account.getMailStatus()) + ",convert(varchar(19),getdate(),120),convert(varchar(19),getdate(),120)) \n"; + sql += "select top 1 companyId,companyName,email,password,alias_email as aliasEmail,userCode,userName,accountId,receive_server_user_name as receiveServerUserName," + + "isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,isnull(mailType,0) as mailType,mailStatus,keywordList,siteUrl,receiveProtocol," + + "receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost,smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost," + + "isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion " + + "from t482102 " + + "where userCode=" + GridUtils.prossSqlParm(account.getUserCode()) + " and email=" + GridUtils.prossSqlParm(account.getEmail()); + sql += " order by accountId desc"; + List<T482102Entity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class)); + if (list.size() > 0) { + return list.get(0); + } + return null; } catch (Exception e) { throw e; } } + @Transactional(rollbackFor = Exception.class) @Override public Integer updateEmailAccount(T482102Entity account) { String sql = "set nocount on\n"; try { - sql += "update t482102 set alias_email=" + GridUtils.prossSqlParm(account.getAliasEmail()) + "," + - "password=" + GridUtils.prossSqlParm(account.getPassword()) + "," + - " receiveProtocol=" + GridUtils.prossSqlParm(account.getReceiveProtocol()) + "," + - " receiveSSL='" + account.isReceiveSSL() + "',receivePort=" + account.getReceivePort() + ", " + - " receiveHost=" + GridUtils.prossSqlParm(account.getReceiveHost()) + ",smtpSSL='" + account.isSmtpSSL() + "'," + - " smtpPort=" + account.getSmtpPort() + ",smtpHost=" + GridUtils.prossSqlParm(account.getSmtpHost()) + "," + - " invalid='" + account.isInvalid() + "',update_time=convert(varchar(19),getdate(),120) " + - " where userCode=" + GridUtils.prossSqlParm(account.getUserCode()) + " and accountId=" + account.getAccountId(); - sql += "select @@ROWCOUNT"; + sql += "update t482102 set alias_email=" + GridUtils.prossSqlParm(account.getAliasEmail()) + ","; + if (StringUtils.isNotBlank(account.getPassword())) { + sql += "password=" + GridUtils.prossSqlParm(account.getPassword()) + ","; + } + sql += " receiveEmail=" + GridUtils.prossSqlParm(account.getReceiveEmail()) + ","; + if (StringUtils.isNotBlank(account.getReceivePassword())) { + sql += "receivePassword=" + GridUtils.prossSqlParm(account.getReceivePassword()) + ","; + } + sql += " receiveSSL='" + account.isReceiveSSL() + "',receivePort=" + account.getReceivePort() + ", " + + " receiveHost=" + GridUtils.prossSqlParm(account.getReceiveHost()) + "," + + " smtpEmail=" + GridUtils.prossSqlParm(account.getSmtpEmail()) + ","; + if (StringUtils.isNotBlank(account.getSmtpPassword())) { + sql += "smtpPassword=" + GridUtils.prossSqlParm(account.getSmtpPassword()) + ","; + } + sql += " smtpSSL='" + account.isSmtpSSL() + "'," + " smtpPort=" + account.getSmtpPort() + ",smtpHost=" + GridUtils.prossSqlParm(account.getSmtpHost()) + "," + + " invalid='" + account.isInvalid() + "',bisync_flag='" + account.isBiSyncFlag() + "',proxyFlag='" + account.isProxyFlag() + "'," + + " mailType=" + account.getMailType() + ",mailStatus=" + GridUtils.prossSqlParm(account.getMailStatus()) + +// " ,update_time=convert(varchar(19),getdate(),120) " + + " where userCode=" + GridUtils.prossSqlParm(account.getUserCode()) + " and accountId=" + account.getAccountId() + "\n"; + sql += " select @@ROWCOUNT"; return jdbcTemplate.queryForObject(sql, Integer.class); } catch (Exception e) { throw e; } } + @Transactional(rollbackFor = Exception.class) + @Override + public Integer updateEmailTime(Integer accountId) { + String sql = "set nocount on\n"; + try { + sql += "update t482102 set update_time=convert(varchar(19),getdate(),120) " + + " where accountId=" + accountId + "\n"; + sql += " select @@ROWCOUNT"; + return jdbcTemplate.queryForObject(sql, Integer.class); + } catch (Exception e) { + throw e; + } + } + + @Transactional(rollbackFor = Exception.class) + @Override + public Integer updateMailStatus(String status, Integer accountId) { + String sql = "set nocount on\n"; + try { + sql += "update t482102 set mailStatus=" + GridUtils.prossSqlParm(status) + //",update_time=convert(varchar(19),getdate(),120) " + + " where accountId=" + accountId + "\n"; + sql += " select @@ROWCOUNT"; + return jdbcTemplate.queryForObject(sql, Integer.class); + } catch (Exception e) { + throw e; + } + } + + @Transactional(rollbackFor = Exception.class) + @Override + public Integer updateAliasEmail(String aliasEmail, Integer accountId) { + String sql = "set nocount on\n"; + try { + sql += "update t482102 set alias_email=" + GridUtils.prossSqlParm(aliasEmail) + //",update_time=convert(varchar(19),getdate(),120) " + + " where accountId=" + accountId + "\n"; + sql += " select @@ROWCOUNT"; + return jdbcTemplate.queryForObject(sql, Integer.class); + } catch (Exception e) { + throw e; + } + } + + @Transactional(rollbackFor = Exception.class) @Override public Integer deleteEmailAccount(String userCode, Integer accountId) { String sql = "set nocount on\n"; @@ -67,17 +146,28 @@ } @Override - public List<T482102Entity> getAccount(String userCode, String email) { + public List<T482102Entity> getAccountList() { + String sql = "set nocount on\n"; + sql += "select companyId,companyName,email,password,alias_email as aliasEmail,userCode,userName,accountId,receive_server_user_name as receiveServerUserName," + + "isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,isnull(mailType,0) as mailType,mailStatus,keywordList,siteUrl,receiveProtocol," + + "receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost,smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost," + + "isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion " + + "from t482102 where invalid=1"; + sql += " order by accountId"; + return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class)); + } + + @Override + public List<T482102Entity> getAccount(String userCode) { String sql = "set nocount on\n"; try { sql += "select companyId,companyName,email,password,alias_email as aliasEmail,userCode,userName,accountId,receive_server_user_name as receiveServerUserName," + - "isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,mailType,keywordList,siteUrl,receiveProtocol,receiveSSL,receivePort,receiveHost," + - "smtpSSL,smtpPort,smtpHost,isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion " + - "from t482102 " + + "isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,isnull(mailType,0) as mailType,mailStatus,keywordList,siteUrl,receiveProtocol," + + "receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost,smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost," + + "isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion, " + + "(case when isnull(mailStatus,'')='姝e父' then 0 else -1 end) as mailStatusCode from t482102 " + "where userCode=" + GridUtils.prossSqlParm(userCode); - if (StringUtils.isNotBlank(email)) { - sql += " and email=" + GridUtils.prossSqlParm(email); - } + sql += " order by accountId"; return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class)); } catch (Exception e) { throw e; @@ -85,15 +175,44 @@ } @Override - public T482102Entity getAccountInfo(String userCode, String email) throws Exception { - if (StringUtils.isBlank(userCode) || StringUtils.isBlank(email)) { - throw new Exception("鍙傛暟UserCode鍜宔mail涓嶈兘涓虹┖"); - } - List<T482102Entity> t482101HEntity = getAccount(userCode, email); - if (t482101HEntity.size() > 0) { - return t482101HEntity.get(0); - } else { + public T482102Entity getAccountInfo(String userCode, Integer accountId) throws Exception { + String sql = "set nocount on\n"; + try { + sql += "select companyId,companyName,email,password,alias_email as aliasEmail,userCode,userName,accountId,receive_server_user_name as receiveServerUserName," + + "isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,isnull(mailType,0) as mailType,mailStatus,keywordList,siteUrl,receiveProtocol," + + "receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost,smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost," + + "isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion " + + "from t482102 " + + "where userCode=" + GridUtils.prossSqlParm(userCode) + " and accountId=" + accountId; + sql += " order by accountId desc"; + List<T482102Entity> t482101HEntity = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class)); + if (t482101HEntity.size() > 0) { + return t482101HEntity.get(0); + } return null; + } catch (Exception e) { + throw e; + } + } + + @Override + public T482102Entity getAccountInfo(String userCode, String eMail) throws Exception { + String sql = "set nocount on\n"; + try { + sql += "select top 1 companyId,companyName,email,password,alias_email as aliasEmail,userCode,userName,accountId,receive_server_user_name as receiveServerUserName," + + "isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,isnull(mailType,0) as mailType,mailStatus,keywordList,siteUrl,receiveProtocol," + + "receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost,smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost," + + "isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion " + + "from t482102 " + + "where userCode=" + GridUtils.prossSqlParm(userCode) + " and email=" + GridUtils.prossSqlParm(eMail); + sql += " order by accountId"; + List<T482102Entity> t482101HEntity = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class)); + if (t482101HEntity.size() > 0) { + return t482101HEntity.get(0); + } + return null; + } catch (Exception e) { + throw e; } } } -- Gitblit v1.8.0