eCoWorksV3.git - Gitblit

xinyb

3 天以前 3b74e3df72726e188d36393ecfd7964d095ef7e8

提交 \| 用户 \| 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	}