提交 | 用户 | age
|
385e2d
|
1 |
package com.yc.crm.mail.service; |
X |
2 |
|
|
3 |
import com.yc.action.grid.GridUtils; |
|
4 |
import com.yc.crm.mail.entity.T482102Entity; |
|
5 |
import com.yc.service.BaseService; |
|
6 |
import org.apache.commons.lang3.StringUtils; |
|
7 |
import org.springframework.jdbc.core.BeanPropertyRowMapper; |
|
8 |
import org.springframework.stereotype.Service; |
048b74
|
9 |
import org.springframework.transaction.annotation.Transactional; |
385e2d
|
10 |
|
X |
11 |
import java.util.List; |
|
12 |
|
|
13 |
/** |
|
14 |
* @BelongsProject: eCoWorksV3 |
|
15 |
* @BelongsPackage: com.yc.crm.service |
|
16 |
* @author: xinyb |
|
17 |
* @CreateTime: 2024-07-26 09:06 |
|
18 |
* @Description: |
|
19 |
*/ |
|
20 |
@Service |
|
21 |
public class MailAccountImpl extends BaseService implements MailAccountIfc { |
048b74
|
22 |
@Transactional(rollbackFor = Exception.class) |
385e2d
|
23 |
@Override |
3b74e3
|
24 |
public T482102Entity addEmailAccount(T482102Entity account) { |
385e2d
|
25 |
String sql = "set nocount on\n"; |
X |
26 |
try { |
3b74e3
|
27 |
sql+="declare @hasMail varchar(200)\n" + |
X |
28 |
"select @hasMail=isnull(email,'') from t482102 where email=" +GridUtils.prossSqlParm(account.getEmail())+ |
|
29 |
"if @hasMail !=''\n" + |
|
30 |
"begin\n" + |
|
31 |
"raiserror('邮箱574600396@qq.com已经绑定过,不能重复绑定',16,1)\n" + |
|
32 |
"return\n" + |
|
33 |
"end \n"; |
6a63e2
|
34 |
sql += "insert into t482102(companyId,companyName,email,password,alias_email,userCode,userName,receiveProtocol," + |
X |
35 |
"receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost," + |
877259
|
36 |
"smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost,invalid,bisync_flag,proxyFlag,mailType,mailStatus,create_time,update_time)\n" + |
385e2d
|
37 |
"values(" + GridUtils.prossSqlParm(account.getCompanyId()) + "," + GridUtils.prossSqlParm(account.getCompanyName()) + "," + GridUtils.prossSqlParm(account.getEmail()) + "," + |
f5cc47
|
38 |
GridUtils.prossSqlParm(account.getPassword()) + "," + |
385e2d
|
39 |
GridUtils.prossSqlParm(account.getAliasEmail()) + "," + GridUtils.prossSqlParm(account.getUserCode()) + "," + GridUtils.prossSqlParm(account.getUserName()) + "," + |
6a63e2
|
40 |
GridUtils.prossSqlParm(account.getReceiveProtocol()) + "," + GridUtils.prossSqlParm(account.getReceiveEmail()) + "," + GridUtils.prossSqlParm(account.getReceivePassword()) + "," + |
X |
41 |
"'" + account.isReceiveSSL() + "'," + account.getReceivePort() + "," + GridUtils.prossSqlParm(account.getReceiveHost()) + "," + |
|
42 |
GridUtils.prossSqlParm(account.getSmtpEmail()) + "," + GridUtils.prossSqlParm(account.getSmtpPassword()) + ",'" + account.isSmtpSSL() + "'," + |
8c1986
|
43 |
account.getSmtpPort() + "," + GridUtils.prossSqlParm(account.getSmtpHost()) + ",'" + account.isInvalid() + "','" + account.isBiSyncFlag() + "','" + account.isProxyFlag() + "'," + |
3b74e3
|
44 |
account.getMailType() + "," + GridUtils.prossSqlParm(account.getMailStatus()) + ",convert(varchar(19),getdate(),120),convert(varchar(19),getdate(),120)) \n"; |
X |
45 |
sql += "select top 1 companyId,companyName,email,password,alias_email as aliasEmail,userCode,userName,accountId,receive_server_user_name as receiveServerUserName," + |
|
46 |
"isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,isnull(mailType,0) as mailType,mailStatus,keywordList,siteUrl,receiveProtocol," + |
|
47 |
"receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost,smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost," + |
|
48 |
"isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion " + |
|
49 |
"from t482102 " + |
|
50 |
"where userCode=" + GridUtils.prossSqlParm(account.getUserCode()) + " and email=" + GridUtils.prossSqlParm(account.getEmail()); |
|
51 |
sql += " order by accountId desc"; |
|
52 |
List<T482102Entity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class)); |
|
53 |
if (list.size() > 0) { |
|
54 |
return list.get(0); |
|
55 |
} |
|
56 |
return null; |
385e2d
|
57 |
} catch (Exception e) { |
X |
58 |
throw e; |
|
59 |
} |
|
60 |
} |
|
61 |
|
048b74
|
62 |
@Transactional(rollbackFor = Exception.class) |
385e2d
|
63 |
@Override |
X |
64 |
public Integer updateEmailAccount(T482102Entity account) { |
|
65 |
String sql = "set nocount on\n"; |
|
66 |
try { |
6a63e2
|
67 |
sql += "update t482102 set alias_email=" + GridUtils.prossSqlParm(account.getAliasEmail()) + ","; |
X |
68 |
if (StringUtils.isNotBlank(account.getPassword())) { |
|
69 |
sql += "password=" + GridUtils.prossSqlParm(account.getPassword()) + ","; |
|
70 |
} |
8c1986
|
71 |
sql += " receiveEmail=" + GridUtils.prossSqlParm(account.getReceiveEmail()) + ","; |
6a63e2
|
72 |
if (StringUtils.isNotBlank(account.getReceivePassword())) { |
X |
73 |
sql += "receivePassword=" + GridUtils.prossSqlParm(account.getReceivePassword()) + ","; |
|
74 |
} |
|
75 |
sql += " receiveSSL='" + account.isReceiveSSL() + "',receivePort=" + account.getReceivePort() + ", " + |
|
76 |
" receiveHost=" + GridUtils.prossSqlParm(account.getReceiveHost()) + "," + |
|
77 |
" smtpEmail=" + GridUtils.prossSqlParm(account.getSmtpEmail()) + ","; |
|
78 |
if (StringUtils.isNotBlank(account.getSmtpPassword())) { |
|
79 |
sql += "smtpPassword=" + GridUtils.prossSqlParm(account.getSmtpPassword()) + ","; |
|
80 |
} |
|
81 |
sql += " smtpSSL='" + account.isSmtpSSL() + "'," + " smtpPort=" + account.getSmtpPort() + ",smtpHost=" + GridUtils.prossSqlParm(account.getSmtpHost()) + "," + |
8c1986
|
82 |
" invalid='" + account.isInvalid() + "',bisync_flag='" + account.isBiSyncFlag() + "',proxyFlag='" + account.isProxyFlag() + "'," + |
7cf738
|
83 |
" mailType=" + account.getMailType() + ",mailStatus=" + GridUtils.prossSqlParm(account.getMailStatus()) + |
X |
84 |
// " ,update_time=convert(varchar(19),getdate(),120) " + |
b7ef4b
|
85 |
" where userCode=" + GridUtils.prossSqlParm(account.getUserCode()) + " and accountId=" + account.getAccountId() + "\n"; |
X |
86 |
sql += " select @@ROWCOUNT"; |
7cf738
|
87 |
return jdbcTemplate.queryForObject(sql, Integer.class); |
X |
88 |
} catch (Exception e) { |
|
89 |
throw e; |
|
90 |
} |
|
91 |
} |
|
92 |
|
048b74
|
93 |
@Transactional(rollbackFor = Exception.class) |
7cf738
|
94 |
@Override |
X |
95 |
public Integer updateEmailTime(Integer accountId) { |
|
96 |
String sql = "set nocount on\n"; |
|
97 |
try { |
|
98 |
sql += "update t482102 set update_time=convert(varchar(19),getdate(),120) " + |
b7ef4b
|
99 |
" where accountId=" + accountId + "\n"; |
X |
100 |
sql += " select @@ROWCOUNT"; |
877259
|
101 |
return jdbcTemplate.queryForObject(sql, Integer.class); |
X |
102 |
} catch (Exception e) { |
|
103 |
throw e; |
|
104 |
} |
|
105 |
} |
|
106 |
|
048b74
|
107 |
@Transactional(rollbackFor = Exception.class) |
877259
|
108 |
@Override |
X |
109 |
public Integer updateMailStatus(String status, Integer accountId) { |
|
110 |
String sql = "set nocount on\n"; |
|
111 |
try { |
7cf738
|
112 |
sql += "update t482102 set mailStatus=" + GridUtils.prossSqlParm(status) + //",update_time=convert(varchar(19),getdate(),120) " + |
b7ef4b
|
113 |
" where accountId=" + accountId + "\n"; |
X |
114 |
sql += " select @@ROWCOUNT"; |
877259
|
115 |
return jdbcTemplate.queryForObject(sql, Integer.class); |
X |
116 |
} catch (Exception e) { |
|
117 |
throw e; |
|
118 |
} |
|
119 |
} |
|
120 |
|
048b74
|
121 |
@Transactional(rollbackFor = Exception.class) |
877259
|
122 |
@Override |
X |
123 |
public Integer updateAliasEmail(String aliasEmail, Integer accountId) { |
|
124 |
String sql = "set nocount on\n"; |
|
125 |
try { |
7cf738
|
126 |
sql += "update t482102 set alias_email=" + GridUtils.prossSqlParm(aliasEmail) + //",update_time=convert(varchar(19),getdate(),120) " + |
b7ef4b
|
127 |
" where accountId=" + accountId + "\n"; |
X |
128 |
sql += " select @@ROWCOUNT"; |
f5cc47
|
129 |
return jdbcTemplate.queryForObject(sql, Integer.class); |
385e2d
|
130 |
} catch (Exception e) { |
X |
131 |
throw e; |
|
132 |
} |
|
133 |
} |
|
134 |
|
048b74
|
135 |
@Transactional(rollbackFor = Exception.class) |
385e2d
|
136 |
@Override |
X |
137 |
public Integer deleteEmailAccount(String userCode, Integer accountId) { |
|
138 |
String sql = "set nocount on\n"; |
|
139 |
try { |
|
140 |
sql += "delete t482102 where userCode='" + userCode + "' and accountId=" + accountId + "\n"; |
|
141 |
sql += "select @@ROWCOUNT"; |
f5cc47
|
142 |
return jdbcTemplate.queryForObject(sql, Integer.class); |
385e2d
|
143 |
} catch (Exception e) { |
X |
144 |
throw e; |
|
145 |
} |
|
146 |
} |
|
147 |
|
|
148 |
@Override |
18553a
|
149 |
public List<T482102Entity> getAccountList() { |
F |
150 |
String sql = "set nocount on\n"; |
b7ef4b
|
151 |
sql += "select companyId,companyName,email,password,alias_email as aliasEmail,userCode,userName,accountId,receive_server_user_name as receiveServerUserName," + |
X |
152 |
"isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,isnull(mailType,0) as mailType,mailStatus,keywordList,siteUrl,receiveProtocol," + |
|
153 |
"receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost,smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost," + |
|
154 |
"isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion " + |
|
155 |
"from t482102 where invalid=1"; |
|
156 |
sql += " order by accountId"; |
|
157 |
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class)); |
18553a
|
158 |
} |
F |
159 |
|
|
160 |
@Override |
877259
|
161 |
public List<T482102Entity> getAccount(String userCode) { |
385e2d
|
162 |
String sql = "set nocount on\n"; |
X |
163 |
try { |
|
164 |
sql += "select companyId,companyName,email,password,alias_email as aliasEmail,userCode,userName,accountId,receive_server_user_name as receiveServerUserName," + |
877259
|
165 |
"isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,isnull(mailType,0) as mailType,mailStatus,keywordList,siteUrl,receiveProtocol," + |
6a63e2
|
166 |
"receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost,smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost," + |
ee2316
|
167 |
"isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion, " + |
X |
168 |
"(case when isnull(mailStatus,'')='正常' then 0 else -1 end) as mailStatusCode from t482102 " + |
385e2d
|
169 |
"where userCode=" + GridUtils.prossSqlParm(userCode); |
877259
|
170 |
sql += " order by accountId"; |
385e2d
|
171 |
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class)); |
X |
172 |
} catch (Exception e) { |
|
173 |
throw e; |
|
174 |
} |
|
175 |
} |
|
176 |
|
|
177 |
@Override |
877259
|
178 |
public T482102Entity getAccountInfo(String userCode, Integer accountId) throws Exception { |
X |
179 |
String sql = "set nocount on\n"; |
|
180 |
try { |
|
181 |
sql += "select companyId,companyName,email,password,alias_email as aliasEmail,userCode,userName,accountId,receive_server_user_name as receiveServerUserName," + |
|
182 |
"isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,isnull(mailType,0) as mailType,mailStatus,keywordList,siteUrl,receiveProtocol," + |
|
183 |
"receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost,smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost," + |
|
184 |
"isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion " + |
|
185 |
"from t482102 " + |
|
186 |
"where userCode=" + GridUtils.prossSqlParm(userCode) + " and accountId=" + accountId; |
3b74e3
|
187 |
sql += " order by accountId desc"; |
877259
|
188 |
List<T482102Entity> t482101HEntity = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class)); |
X |
189 |
if (t482101HEntity.size() > 0) { |
|
190 |
return t482101HEntity.get(0); |
|
191 |
} |
385e2d
|
192 |
return null; |
877259
|
193 |
} catch (Exception e) { |
X |
194 |
throw e; |
|
195 |
} |
|
196 |
} |
|
197 |
|
|
198 |
@Override |
|
199 |
public T482102Entity getAccountInfo(String userCode, String eMail) throws Exception { |
|
200 |
String sql = "set nocount on\n"; |
|
201 |
try { |
7caeae
|
202 |
sql += "select top 1 companyId,companyName,email,password,alias_email as aliasEmail,userCode,userName,accountId,receive_server_user_name as receiveServerUserName," + |
877259
|
203 |
"isnull(bisync_flag,0) as biSyncFlag,isnull(proxyFlag,0) as proxyFlag,mailDomain,isnull(mailType,0) as mailType,mailStatus,keywordList,siteUrl,receiveProtocol," + |
X |
204 |
"receiveEmail,receivePassword,receiveSSL,receivePort,receiveHost,smtpEmail,smtpPassword,smtpSSL,smtpPort,smtpHost," + |
|
205 |
"isnull(invalid,0) as invalid,create_time as createTime,update_time as updateTime,DocVersion " + |
|
206 |
"from t482102 " + |
|
207 |
"where userCode=" + GridUtils.prossSqlParm(userCode) + " and email=" + GridUtils.prossSqlParm(eMail); |
|
208 |
sql += " order by accountId"; |
|
209 |
List<T482102Entity> t482101HEntity = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482102Entity.class)); |
|
210 |
if (t482101HEntity.size() > 0) { |
|
211 |
return t482101HEntity.get(0); |
|
212 |
} |
|
213 |
return null; |
|
214 |
} catch (Exception e) { |
|
215 |
throw e; |
385e2d
|
216 |
} |
X |
217 |
} |
|
218 |
} |