CRM邮箱接口优化

xinyb

2024-09-19 1956855b43808d81f246d9570d477ac1cbc404b7

 src/com/yc/crm/mail/service/MailImpl.java

@@ -3,7 +3,6 @@
import com.yc.action.grid.GridUtils;
import com.yc.crm.mail.entity.*;
import com.yc.entity.AttachmentConfig;
import com.yc.entity.attachment.AttachmentEntity;
import com.yc.sdk.shopping.service.imagedata.ShoppingImageDataIfc;
import com.yc.sdk.weixincp.util.UploadFiles;
import com.yc.service.BaseService;
@@ -14,11 +13,7 @@
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
@@ -47,7 +42,7 @@
                        "@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," +
                        "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),"
@@ -57,8 +52,7 @@
                        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()," + GridUtils.prossSqlParm(m.getReceiveTime()) + "," + m.getAttachFlag() + "," +
                        GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n" +
                        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);
@@ -84,7 +78,7 @@
            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,receive_time," +
                        "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),"
@@ -94,8 +88,7 @@
                        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()," + GridUtils.prossSqlParm(m.getReceiveTime()) + "," +
                        m.getAttachFlag() + "," + GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n" +
                        m.getReadFlag() + "," + m.getDeleteFlag() + ",getdate(),getdate()," + m.getAttachFlag() + "," + GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n" +
                        mailFileSql(m.getMailFile());
            }
            jdbcTemplate.update(sql);
@@ -212,7 +205,7 @@
    }

    @Override
    public List<t482101HList> getReceivingMailList(String email, Integer mailType, boolean isNoRead, String userCode) {
    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," +
@@ -231,6 +224,26 @@
            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;
@@ -269,84 +282,6 @@
    }

    @Override
    public void saveAttachment(List<MailFileEntity> mailFile) {
        try {

        } catch (Exception e) {
            throw e;
        }
    }


    @Override
    public List<AttachmentEntity> getAttachmentEntityList(String unIdSeq) {
        String unId = null;
        if (StringUtils.isBlank(unIdSeq)) {
            return null;
        }
        String[] array = unIdSeq.split(";");
        unId = array[0];//在有值时候第一个必定是unId
        ArrayList<String> fieldId = new ArrayList<>(Arrays.asList(array));
        fieldId.remove(0);//去掉第一个元素（UNID）
        String sql = " set nocount on \n"
                + " declare @unid varchar(50) = " + GridUtils.prossSqlParm(unId) +
                ",@fieldid varchar(1000) = " + GridUtils.prossSqlParm(StringUtils.join(fieldId, ",")) + " \n";

        sql += " select UNID,seq,DocCode,RowId,FieldId,FormId, \n"
                + " PhysicalPath,PhysicalFile,OriginalFileName,FileSize,FileType, \n"
                + " AuthorCode,AuthorName,SmallPicPath,UploadTime,OriginalPicture \n"
                + " from _sys_Attachment \n"
                + " where unid = @unid ";
        if (fieldId.size() > 0) {
            if (isNumeric(fieldId.get(0))) {
                sql += " and seq in(select list from GetInStr(@fieldid)) \n";
            } else {
                sql += " and fieldid in(select list from GetInStr(@fieldid)) \n";
            }
        }
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
        List<AttachmentEntity> attachmentList = new ArrayList<AttachmentEntity>();
        for (int i = 0; list != null && i < list.size(); i++) {
            AttachmentEntity attachment = new AttachmentEntity();
            attachment.setUnid(unId);
            attachment.setPhysicalFile(list.get(i).get("PhysicalFile") == null ? null : (String) list.get(i).get("PhysicalFile"));
            attachment.setOriginalFileName(list.get(i).get("OriginalFileName") == null ? null : (String) list.get(i).get("OriginalFileName"));
            attachment.setOriginalPicture(list.get(i).get("OriginalPicture") == null ? null : (byte[]) list.get(i).get("OriginalPicture"));  //附件处理
            attachmentList.add(attachment);
        }
        return attachmentList;
    }

    /**
     * 判断是否为数字
     *
     * @param str
     * @return
     */
    private static boolean isNumeric(String str) {
        Pattern pattern = Pattern.compile("\\d+");
        Matcher matcher = pattern.matcher(str);
        return matcher.matches();
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Integer deleteAttachment(List<String> unId) {
        try {
            if (unId == null || unId.size() == 0) {
                return 0;
            }
            String sql = " set nocount on \n"
                    + " declare @unid varchar(4000) = '" + StringUtils.join(unId, ",") + "'  \n";
            sql += "delete _sys_Attachment where unid in (select list from GetInStr(@unid)) \n";
            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";
@@ -362,6 +297,7 @@
            throw e;
        }
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void updateRead(String docCode, boolean status) {
@@ -454,6 +390,7 @@
            throw e;
        }
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void updateMailHandle(String handleTime, String docCode) {