package com.yc.crm.mail.service;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.crm.mail.entity.*;
|
import com.yc.entity.AttachmentConfig;
|
import com.yc.sdk.shopping.service.imagedata.ShoppingImageDataIfc;
|
import com.yc.sdk.weixincp.util.UploadFiles;
|
import com.yc.service.BaseService;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.jdbc.core.BeanPropertyRowMapper;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.stream.Collectors;
|
|
/**
|
* @BelongsProject: eCoWorksV3
|
* @BelongsPackage: com.yc.crm.mail.service
|
* @author: xinyb
|
* @CreateTime: 2024-08-06 09:57
|
* @Description:
|
*/
|
@Service
|
public class MailImpl extends BaseService implements MailIfc {
|
@Autowired
|
MailAccountIfc emailAccountIfc;
|
@Autowired
|
ShoppingImageDataIfc imgData;
|
|
final static String shoppingImageServer = AttachmentConfig.get("attachment.server");
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public t482101HEntity saveReceivingMail(t482101HEntity m) {
|
String sql = "set nocount on\n";
|
try {
|
if (StringUtils.isBlank(m.getDocCode())) {
|
sql += "declare @docCode varchar(50) ,@unid varchar(50),@fieldId varchar(100),@originalFileName varchar(100)," +
|
"@physicalFile varchar(50),@OriginalPicture varbinary(max) \n";
|
sql += "exec sp_newdoccode 482101," + GridUtils.prossSqlParm(m.getUserCode()) + ",@docCode output \n";
|
sql += "insert into t482101H(companyId,companyName,formId,docCode,docdate,PeriodID,postCode,postname,mailType,senderName,sender,receiver," +
|
"cc,bcc,subject,userCode,userName,content,plain_text,messageId,read_flag,delete_flag,create_time,update_time," +//receive_time
|
"attach_flag,attachment_list) values ";
|
sql += "(" + GridUtils.prossSqlParm(m.getCompanyId()) + "," + GridUtils.prossSqlParm(m.getCompanyName()) + ",482101," +
|
"@docCode,convert(varchar(10),getdate(),120),convert(varchar(7),getdate(),120),"
|
+ GridUtils.prossSqlParm(m.getUserCode()) + "," + GridUtils.prossSqlParm(m.getUserName()) + "," +
|
+m.getMailType() + "," + GridUtils.prossSqlParm(m.getSenderName()) + "," + GridUtils.prossSqlParm(m.getSender()) + "," +
|
GridUtils.prossSqlParm(StringUtils.join(m.getReceiver(), ",")) + "," + GridUtils.prossSqlParm(StringUtils.join(m.getCc(), ",")) + "," +
|
GridUtils.prossSqlParm(StringUtils.join(m.getBcc(), ",")) + "," +
|
GridUtils.prossSqlParm(m.getSubject()) + "," + GridUtils.prossSqlParm(m.getUserCode()) + "," + GridUtils.prossSqlParm(m.getUserName()) + "," +
|
GridUtils.prossSqlParm(m.getContent()) + "," + GridUtils.prossSqlParm(m.getPlainText()) + "," + GridUtils.prossSqlParm(m.getMessageId()) + "," +
|
m.getReadFlag() + "," + m.getDeleteFlag() + ",getdate(),getdate()," + m.getAttachFlag() + "," + GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n" +
|
mailFileSql(m.getMailFile());
|
sql += "select @docCode as docCode";
|
String docCode = jdbcTemplate.queryForObject(sql, String.class);
|
if (StringUtils.isNotBlank(docCode)) {
|
m.setDocCode(docCode);
|
}
|
} else {
|
updateReceivingMail(m);//修改
|
}
|
return m;
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void saveReceivingMailList(List<t482101HEntity> mail) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "declare @docCode varchar(50) ,@unid varchar(50),@fieldId varchar(100),@originalFileName varchar(100)," +
|
"@physicalFile varchar(50),@OriginalPicture varbinary(max) \n";
|
for (t482101HEntity m : mail) {//遍历内容
|
sql += "exec sp_newdoccode 482101," + GridUtils.prossSqlParm(m.getUserCode()) + ",@docCode output \n";
|
sql += "insert into t482101H(companyId,companyName,formId,docCode,docdate,PeriodID,postCode,postname,mailType,senderName,sender,receiver," +
|
"cc,bcc,subject,userCode,userName,content,plain_text,messageId,read_flag,delete_flag,create_time,update_time," +
|
"attach_flag,attachment_list) values ";
|
sql += "(" + GridUtils.prossSqlParm(m.getCompanyId()) + "," + GridUtils.prossSqlParm(m.getCompanyName()) + ",482101," +
|
"@docCode,convert(varchar(10),getdate(),120),convert(varchar(7),getdate(),120),"
|
+ GridUtils.prossSqlParm(m.getUserCode()) + "," + GridUtils.prossSqlParm(m.getUserName()) + "," +
|
+m.getMailType() + "," + GridUtils.prossSqlParm(m.getSenderName()) + "," + GridUtils.prossSqlParm(m.getSender()) + "," +
|
GridUtils.prossSqlParm(StringUtils.join(m.getReceiver(), ",")) + "," + GridUtils.prossSqlParm(StringUtils.join(m.getCc(), ",")) + "," +
|
GridUtils.prossSqlParm(StringUtils.join(m.getBcc(), ",")) + "," +
|
GridUtils.prossSqlParm(m.getSubject()) + "," + GridUtils.prossSqlParm(m.getUserCode()) + "," + GridUtils.prossSqlParm(m.getUserName()) + "," +
|
GridUtils.prossSqlParm(m.getContent()) + "," + GridUtils.prossSqlParm(m.getPlainText()) + "," + GridUtils.prossSqlParm(m.getMessageId()) + "," +
|
m.getReadFlag() + "," + m.getDeleteFlag() + ",getdate(),getdate()," + m.getAttachFlag() + "," + GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n" +
|
mailFileSql(m.getMailFile());
|
}
|
jdbcTemplate.update(sql);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
/**
|
* 邮件附件的处理
|
*
|
* @param mailFile
|
* @return
|
*/
|
private String mailFileSql(MailFileEntity mailFile) {
|
if (mailFile == null) {//附件保存
|
return "";
|
}
|
String sql = "";
|
String originalFileName = "";
|
String fileType = "";
|
try {
|
for (MailFileEntity.MailBodyPart file : mailFile.getPart()) {
|
originalFileName = file.getFileName();
|
fileType = file.getFileType();
|
sql += "select @unid=" + GridUtils.prossSqlParm(mailFile.getUnId()) + "\n" +
|
",@fieldId=" + GridUtils.prossSqlParm(file.getFieldId()) + "\n" +
|
",@originalFileName=" + GridUtils.prossSqlParm(originalFileName) + "\n" +
|
",@physicalFile=" + GridUtils.prossSqlParm(file.getPhysicalFile()) + "\n" +
|
",@OriginalPicture=" + UploadFiles.byteToHexString(file.getByteFile()) + "\n";
|
sql += "insert into _sys_Attachment(unId,formId,docCode,fieldId,physicalFile,originalFileName,FileSize,fileType,UploadTime,LastUpdateTime,OriginalPicture) values ";
|
sql += " (@unid,482101,@docCode,@fieldId,@physicalFile,@originalFileName," + file.getFileSize() + "," + GridUtils.prossSqlParm(fileType) + ",getdate(),getdate(),@OriginalPicture) \n";
|
}
|
return sql;
|
} catch (Exception e) {
|
return "";
|
}
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public Integer updateReceivingMail(t482101HEntity mail) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "update t482101H set sender=" + GridUtils.prossSqlParm(mail.getSender()) + ",receiver=" + GridUtils.prossSqlParm(StringUtils.join(mail.getReceiver(), ",")) + "," +
|
"cc=" + GridUtils.prossSqlParm(StringUtils.join(mail.getCc(), ",")) + ",bcc=" + GridUtils.prossSqlParm(StringUtils.join(mail.getBcc(), ",")) + ",subject=" + GridUtils.prossSqlParm(mail.getSubject()) + "," +
|
"content=" + GridUtils.prossSqlParm(mail.getContent()) + ",attachment_list=" + GridUtils.prossSqlParm(mail.getAttachmentList()) + ",mailType=" + mail.getMailType() + "," +
|
"update_time=getdate() \n";
|
sql += " where docCode=" + GridUtils.prossSqlParm(mail.getDocCode()) + "\n";
|
sql += "select @@ROWCOUNT";
|
return jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public Integer updateDeleteFlag(String userCode, String docCode) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
|
sql += "update t482101H deleteFlag=1 where userCode =" + GridUtils.prossSqlParm(userCode) +
|
" and docCode in (select list from GetInStr(@docCode))\n";//标记删除
|
sql += " select @@ROWCOUNT";
|
return jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public Integer deleteEmail(String userCode, String docCode) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
|
sql += "delete t482101H where userCode =" + GridUtils.prossSqlParm(userCode) +
|
" and docCode in (select list from GetInStr(@docCode))\n";//标记删除
|
sql += " select @@ROWCOUNT";
|
return jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public Integer deleteEmail(String messageId) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "declare @messageId varchar(200) ='" + messageId + "' \n";
|
sql += "delete t482101H where messageId in (select list from GetInStr(@messageId))\n";//删除
|
sql += " select @@ROWCOUNT";
|
return jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public t482101HEntity getReceivingMailInfo(String docCode) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "select top 1 DocCode,companyId,companyName,senderName,senderName,sender,receiver,mailType,subject,content,plain_text,file_unid as fileUNID,messageId," +
|
"read_flag,delete_flag,attach_flag,urgent_flag,receipt_flag,track_flag,userCode,userName,create_time,update_time," +
|
"send_status,receive_time,sign_id,attachment_list,remark,remark," +
|
"(case when (isnull(handle_time,'') <> '' and isnull(handle_time,'') < getdate()) then 1 else 0 end) as handle,handle_time," +
|
"DocVersion from t482101H " +
|
"where docCode=" + GridUtils.prossSqlParm(docCode);
|
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(t482101HEntity.class)).get(0);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public List<t482101HList> getReceivingMailList(String email, Integer mailType, boolean isNoRead, String userCode, Integer page, Integer limit) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "select companyId,companyName,mailType,DocCode,senderName,sender,receiver,subject,plain_text," +
|
"receive_time,update_time,create_time,senderName,sender,userCode,userName,folder_id,delete_Flag, " +
|
"read_Flag,(case when (isnull(handle_time,'') <> '' and isnull(handle_time,'') < getdate()) then 1 else 0 end) as handle," +
|
"handle_time from t482101H ";
|
sql += " where userCode=" + GridUtils.prossSqlParm(userCode);
|
sql += " and mailType=" + mailType;//0：草稿箱 1：收件箱 2：发件箱
|
if (StringUtils.isNotBlank(email)) {
|
if (mailType == 1) {
|
sql += " and receiver like '%" + email + "%'";
|
} else if (mailType == 2) {
|
sql += " and sender =" + GridUtils.prossSqlParm(email);
|
}
|
}
|
if (isNoRead) {//0表示未读，1表示已读
|
sql += " and isnull(read_flag,0) = 0";
|
}
|
sql += " order by docCode asc \n";
|
sql += " OFFSET (" + page + " - 1) * " + limit + " ROWS FETCH NEXT " + limit + " ROWS ONLY";
|
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(t482101HList.class));
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public List<t482101HList> getHandleMailList(String email, String userCode, Integer page, Integer limit) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "select companyId,companyName,mailType,DocCode,senderName,sender,receiver,subject,plain_text," +
|
"receive_time,update_time,create_time,senderName,sender,userCode,userName,folder_id,delete_Flag, " +
|
"read_Flag,(case when (isnull(handle_time,'') <> '' and isnull(handle_time,'') < getdate()) then 1 else 0 end) as handle," +
|
"handle_time from t482101H ";
|
sql += " where isnull(handle_time,'') <> '' and isnull(handle_time,'') < getdate()";
|
sql += " and userCode=" + GridUtils.prossSqlParm(userCode) + " and receiver like '%" + email + "%'";
|
sql += " order by docCode asc \n";
|
sql += " OFFSET (" + page + " - 1) * " + limit + " ROWS FETCH NEXT " + limit + " ROWS ONLY";
|
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(t482101HList.class));
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public List<t482101HList> getUserMailList(String userCode) {
|
String sql = " set nocount on \n";
|
try {
|
sql = "select a.DocCode,isnull(a.mailType,0) as mailType,isnull(a.read_flag,0) as read_flag,a.sender,a.receiver, " +
|
"(case when (isnull(handle_time,'') <> '' and isnull(handle_time,'') < getdate()) then 1 else 0 end) as handle " +
|
" from t482101H a where a.userCode=" + GridUtils.prossSqlParm(userCode) + " order by a.mailType";
|
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(t482101HList.class));
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public Integer updateMailDrafts(t482101HEntity mail) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "update t482101H set sender=" + GridUtils.prossSqlParm(mail.getSender()) + ",receiver=" + GridUtils.prossSqlParm(StringUtils.join(mail.getReceiver(), ",")) + "," +
|
"cc=" + GridUtils.prossSqlParm(StringUtils.join(mail.getCc(), ",")) + ",bcc=" + GridUtils.prossSqlParm(StringUtils.join(mail.getBcc(), ",")) + "," +
|
"subject=" + GridUtils.prossSqlParm(mail.getSubject()) + ",messageId=" + GridUtils.prossSqlParm(mail.getMessageId()) + "," +
|
"content=" + GridUtils.prossSqlParm(mail.getContent()) + ",attachment_list=" + GridUtils.prossSqlParm(mail.getAttachmentList()) + ",mailType=" + mail.getMailType() + "," +
|
"update_time=getdate() \n";
|
sql += " where docCode=" + GridUtils.prossSqlParm(mail.getDocCode());
|
sql += " select @@ROWCOUNT";
|
return jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public List<String> getMessageIdList(String receiver) {
|
List<String> messageId = null;
|
String sql = " set nocount on \n";
|
try {
|
sql += "select messageId from t482101H where receiver='" + receiver + "'";
|
List<t482101HEntity> message = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(t482101HEntity.class));
|
if (message != null && message.size() > 0) {
|
messageId = new ArrayList<>();
|
messageId = message.stream().map(t482101HEntity::getMessageId).distinct().collect(Collectors.toList());//去重
|
}
|
return messageId;
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void updateRead(String docCode, boolean status) {
|
String sql = " set nocount on \n";
|
try {
|
sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
|
sql += "update t482101H set read_flag=" + (status ? 1 : 0) + " where docCode in (select list from GetInStr(@docCode))\n";
|
sql += "select @@ROWCOUNT";
|
jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public List<MailModuleEntity> getMailModuleList(String userCode) {
|
List<MailModuleEntity> module = MailModuleEntity.setMailInitial();
|
try {
|
List<T482102Entity> account = emailAccountIfc.getAccount(userCode);//获取这个账号绑定的邮箱
|
List<t482101HList> userMail = getUserMailList(userCode);
|
for (MailModuleEntity m : module) {
|
List<t482101HList> mailTypeList = null;
|
int mailType = m.getMailType();//类型
|
switch (mailType) {
|
case 0://草稿箱
|
case 1://收件箱
|
case 2://发件箱
|
if (mailType == 0) {
|
m.setChildren(new ArrayList<>());
|
if (userMail.size() > 0) {
|
m.setTotal((int) userMail.stream().filter(s -> s.getMailType().equals(mailType)).count());
|
}
|
} else {
|
List<MailModuleEntity.MailCode> codeList = new ArrayList<>();
|
MailModuleEntity.MailCode code = new MailModuleEntity.MailCode();
|
code.setKey(mailType == 1 ? "InboxPage1" : "IndexPage1");
|
code.setMailType(mailType);
|
code.setMailName(mailType == 1 ? "全部收件" : "全部发件");
|
codeList.add(code);
|
if (account.size() == 0) {//没有直接跳过
|
m.setChildren(codeList);
|
continue;
|
}
|
int allCount = 0;
|
int count = 0;
|
for (T482102Entity a : account) {
|
MailModuleEntity.MailCode nextCode = new MailModuleEntity.MailCode();
|
String eMail = (mailType == 1 ? a.getReceiveEmail() : a.getSmtpEmail());//获取邮箱账号
|
nextCode.setKey(mailType == 1 ? "receiver" : "sender");
|
nextCode.setMailName(eMail);
|
nextCode.setMailType(m.getMailType());
|
// if (userMail.size() > 0) {
|
// count = (mailType == 1 ? (int) userMail.stream().filter(s -> s.getMailType().equals(mailType) && s.getReceiver().contains(eMail)).count() :
|
// (int) userMail.stream().filter(s -> s.getMailType().equals(mailType) && s.getSender().contains(eMail)).count());
|
// nextCode.setTotal(count);
|
// }
|
if (mailType == 1) {//是收件箱，统计未读数量
|
count = (int) userMail.stream().filter(s -> s.getMailType().equals(mailType) && s.getReceiver().contains(eMail) && s.getReadFlag().equals(0)).count();
|
nextCode.setTotal(count);
|
}
|
codeList.add(nextCode);
|
allCount = (allCount + count);
|
}
|
codeList.get(0).setTotal(allCount);
|
m.setTotal(allCount);
|
m.setChildren(codeList);
|
}
|
break;
|
case 3://待处理邮件
|
m.setChildren(new ArrayList<>());
|
if (userMail.size() > 0) {
|
m.setTotal((int) userMail.stream().filter(s -> s.getHandle().equals(1)).count());
|
}
|
break;
|
case 4://未读邮件
|
m.setChildren(new ArrayList<>());
|
if (userMail.size() > 0) {
|
m.setTotal((int) userMail.stream().filter(s -> s.getReadFlag().equals(0)).count());
|
}
|
break;
|
case 5://群邮箱
|
m.setChildren(new ArrayList<>());
|
break;
|
default:
|
break;
|
}
|
}
|
return module;
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void updateMailHandle(String handleTime, String docCode) {
|
String sql = " set nocount on \n";
|
try {
|
sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
|
sql += "update t482101H set handle_time=" + (StringUtils.isBlank(handleTime) ? "null" : GridUtils.prossSqlParm(handleTime)) +
|
" where docCode in (select list from GetInStr(@docCode))\n";
|
sql += "select @@ROWCOUNT";
|
jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
throw e;
|
}
|
}
|
|
@Override
|
public Integer getMailTotal(String email, Integer mailType, boolean isNoRead, String userCode) {
|
String sql = "set nocount on\n";
|
try {
|
sql += "select count(*) from t482101H ";
|
sql += " where userCode=" + GridUtils.prossSqlParm(userCode);
|
if (mailType < 3) {
|
sql += " and mailType=" + mailType;//0：草稿箱 1：收件箱 2：发件箱
|
}
|
if (StringUtils.isNotBlank(email)) {
|
if (mailType == 1 || mailType == 3) {
|
sql += " and receiver like '%" + email + "%' and isnull(handle_time,'') <> '' and isnull(handle_time,'') < getdate() ";
|
} else if (mailType == 2) {
|
sql += " and sender =" + GridUtils.prossSqlParm(email);
|
}
|
}
|
if (isNoRead) {//0表示未读，1表示已读
|
sql += " and isnull(read_flag,0) = 0";
|
}
|
return jdbcTemplate.queryForObject(sql, Integer.class);
|
} catch (Exception e) {
|
return 0;
|
}
|
}
|
}
|
