package com.yc.crm.mail.service;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.crm.mail.entity.T482102Entity;
|
import com.yc.service.BaseService;
|
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;
|
|
/**
|
* @BelongsProject: eCoWorksV3
|
* @BelongsPackage: com.yc.crm.service
|
* @author: xinyb
|
* @CreateTime: 2024-07-26 09:06
|
* @Description:
|
*/
|
@Service
|
public class MailAccountImpl extends BaseService implements MailAccountIfc {
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public T482102Entity addEmailAccount(T482102Entity account) {
|
String sql = "set nocount on\n";
|
try {
|
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()) + "," + 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()) + ",";
|
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";
|
try {
|
sql += "delete t482102 where userCode='" + userCode + "' and accountId=" + accountId + "\n";
|
sql += "select @@ROWCOUNT";
|
return jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
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,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,'')='正常' then 0 else -1 end) as mailStatusCode from t482102 " +
|
"where userCode=" + GridUtils.prossSqlParm(userCode);
|
sql += " order by accountId";
|
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class));
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
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;
|
}
|
}
|
}
|