CRM邮箱配置调整

xinyb

6 天以前 ee2316e2cb7afde1c54f5e4216a1b0d1e055749e

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

@@ -1,6 +1,9 @@
package com.yc.crm.mail.service;

import com.yc.action.grid.GridUtils;
import com.yc.crm.base.entity.T482115Entity;
import com.yc.crm.mail.action.MailFolder;
import com.yc.crm.mail.action.MailTag;
import com.yc.crm.mail.entity.*;
import com.yc.entity.AttachmentConfig;
import com.yc.sdk.shopping.service.imagedata.ShoppingImageDataIfc;
@@ -29,6 +32,10 @@
    MailAccountIfc emailAccountIfc;
    @Autowired
    ShoppingImageDataIfc imgData;
    @Autowired
    MailTagIfc mailTagIfc;
    @Autowired
    MailFolderIfc mailFolderIfc;

    final static String shoppingImageServer = AttachmentConfig.get("attachment.server");

@@ -41,14 +48,14 @@
                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," +
                sql += "insert into t482101H(companyId,companyName,formId,docCode,docdate,PeriodID,postCode,postname,mailType,senderName,sender,sender_time,receiver,receiving_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),"
                        + 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(), ",")) + "," +
                        +m.getMailType() + "," + GridUtils.prossSqlParm(m.getSenderName()) + "," + GridUtils.prossSqlParm(m.getSender()) + "," + GridUtils.prossSqlParm(m.getSenderTime()) + "," +
                        GridUtils.prossSqlParm(StringUtils.join(m.getReceiver(), ",")) + "," + GridUtils.prossSqlParm(m.getReceivingTime()) + "," + 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()) + "," +
@@ -70,28 +77,29 @@

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void saveReceivingMailList(List<t482101HEntity> mail) {
    public String 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)," +
            sql += "declare @docCode varchar(50) ,@unid varchar(50),@fieldId varchar(100),@originalFileName varchar(100),@docCodeList varchar(3000)=''," +
                    "@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," +
                sql += " select @docCodeList=@docCodeList+@docCode+';'\n" +//返回单号
                        "insert into t482101H(companyId,companyName,formId,docCode,docdate,PeriodID,postCode,postname,mailType,senderName,sender,sender_time,receiver,receiving_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),"
                        + 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(), ",")) + "," +
                        +m.getMailType() + "," + GridUtils.prossSqlParm(m.getSenderName()) + "," + GridUtils.prossSqlParm(m.getSender()) + "," + GridUtils.prossSqlParm(m.getSenderTime()) + "," +
                        GridUtils.prossSqlParm(StringUtils.join(m.getReceiver(), ",")) + "," + GridUtils.prossSqlParm(m.getReceivingTime()) + "," + 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);
            return jdbcTemplate.queryForObject(sql + "\n select @docCodeList", String.class);
        } catch (Exception e) {
            throw e;
        }
@@ -166,7 +174,7 @@
        String sql = "set nocount on\n";
        try {
            sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
            sql += "delete t482101H where userCode =" + GridUtils.prossSqlParm(userCode) +
            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);
@@ -180,7 +188,7 @@
        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 += " delete t482101H where messageId in (select list from GetInStr(@messageId))\n";//删除
            sql += " select @@ROWCOUNT";
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
@@ -194,7 +202,7 @@
        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," +
                    "send_status,receive_time,sign_id,attachment_list,remark,remark,sender_time,receiving_time," +
                    "(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);
@@ -209,9 +217,9 @@
        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, " +
                    "receive_time,update_time,create_time,senderName,sender,userCode,userName,folder_id,delete_Flag,attachment_list," +
                    "read_Flag,(case when (isnull(handle_time,'') <> '' and isnull(handle_time,'') < getdate()) then 1 else 0 end) as handle," +
                    "handle_time from t482101H ";
                    "handle_time,sender_time,receiving_time from t482101H ";
            sql += " where userCode=" + GridUtils.prossSqlParm(userCode);
            sql += " and mailType=" + mailType;//0：草稿箱 1：收件箱 2：发件箱
            if (StringUtils.isNotBlank(email)) {
@@ -239,9 +247,35 @@
            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 ";
                    "handle_time,sender_time,receiving_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> getFolderMailList(String type, String userCode, Integer page, Integer limit) throws Exception {
        String sql = "set nocount on\n";
        try {
            String[] id = type.split("_");
            if (id.length != 2) {
                throw new Exception("邮件类型Id格式不正确");
            }
            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,sender_time,receiving_time from t482101H ";
            sql += " where userCode=" + GridUtils.prossSqlParm(userCode) + " \n";
            if (id[0].equals("folder")) {//是文件夹
                sql += " and folder_id=" + id[1] + " \n";
            } else if (id[0].equals("tag")) {//是标签
                sql += " and tag_list like '%," + id[1] + ",%'";
            }
            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));
@@ -305,6 +339,28 @@
        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 void updateMailType(String docCode, Integer folderId, String tagId) {
        String sql = " set nocount on \n";
        try {
            boolean bol = false;
            sql += "declare @docCode varchar(200) ='" + docCode + "' \n";
            sql += "update t482101H set \n";
            if (!folderId.equals(0)) {
                sql += " folder_id=" + folderId + "\n";
                bol = true;
            }
            if (StringUtils.isNotBlank(tagId)) {
                sql += (bol ? "," : "") + " tag_list='," + tagId + ",' \n";
            }
            sql += " where docCode in (select list from GetInStr(@docCode))\n";
            sql += "select @@ROWCOUNT";
            jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
@@ -391,6 +447,43 @@
        }
    }

    @Override
    public List<MailModuleBelowEntity> getMailModuleBelowList(String userCode) {
        List<MailModuleBelowEntity> below = MailModuleBelowEntity.setMailInitial();
        try {
            for (MailModuleBelowEntity b : below) {
                String key = b.getKey();
                if (key.equals("moduleBelowA")) {//文件夹
                    List<T482107Entity> t482107 = mailFolderIfc.getMailFolder(userCode);
                    b.setList(MailFolder.getFolderModule(t482107, ""));
                }
                if (key.equals("moduleBelowB")) {//标签邮件
                    List<T482115Entity> tag = mailTagIfc.getTagList(userCode, "", -1);
                    b.setList(MailTag.getTagModule(tag));
                }
                if (key.equals("moduleBelowC")) {//显示更多
                    List<MailModuleBelowEntity> CList = new ArrayList<>();
                    MailModuleBelowEntity delete = new MailModuleBelowEntity();
                    delete.setKey("deleteMail");
                    delete.setName("已删除邮件");
                    CList.add(delete);
                    MailModuleBelowEntity waste = new MailModuleBelowEntity();
                    waste.setKey("wasteMail");
                    waste.setName("垃圾邮件");
                    CList.add(waste);
                    MailModuleBelowEntity track = new MailModuleBelowEntity();
                    track.setKey("trackMail");
                    track.setName("追踪信息");
                    CList.add(track);
                    b.setList(CList);
                }
            }
            return below;
        } catch (Exception e) {
            throw e;
        }
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void updateMailHandle(String handleTime, String docCode) {
@@ -407,7 +500,7 @@
    }

    @Override
    public Integer getMailTotal(String email, Integer mailType, boolean isNoRead, String userCode) {
    public Integer getMailTotal(String email, Integer mailType, boolean isNoRead, String id, String userCode) {
        String sql = "set nocount on\n";
        try {
            sql += "select count(*) from t482101H ";
@@ -416,15 +509,27 @@
                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() ";
                if (mailType == 1) {
                    sql += " and receiver like '%" + email + "%'";
                } else if (mailType == 2) {
                    sql += " and sender =" + GridUtils.prossSqlParm(email);
                } else if (mailType == 3) {
                    sql += " and isnull(handle_time,'') <> '' and isnull(handle_time,'') < getdate() ";
                }
            }
            if (isNoRead) {//0表示未读，1表示已读
                sql += " and isnull(read_flag,0) = 0";
            }
            if (StringUtils.isNotBlank(id)) {//文件夹
                String[] type = id.split("_");
                if (type.length == 2) {
                    if (type[0].equals("folder")) {
                        sql += " and folder_id=" + type[1];
                    } else if (type[0].equals("tag")) {
                        sql += " and tag_list like '%," + type[1] + ",%'";
                    }
                }
            }
            return jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            return 0;