CRM邮箱配置调整

xinyb

6 天以前 ee2316e2cb7afde1c54f5e4216a1b0d1e055749e

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

@@ -1,39 +1,22 @@
package com.yc.crm.mail.service;

import com.sun.mail.imap.IMAPBodyPart;
import com.sun.mail.imap.IMAPStore;
import com.yc.action.grid.GridUtils;
import com.yc.crm.mail.entity.MailModuleEntity;
import com.yc.crm.mail.entity.T482102Entity;
import com.yc.crm.mail.entity.t482101HEntity;
import com.yc.crm.mail.entity.t482101HList;
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.entity.DataSourceEntity;
import com.yc.entity.attachment.AttachmentEntity;
import com.yc.entity.attachment.AttachmentWhereEntity;
import com.yc.factory.FactoryBean;
import com.yc.multiData.MultiDataSource;
import com.yc.sdk.shopping.service.imagedata.ShoppingImageDataIfc;
import com.yc.sdk.weixincp.util.UploadFiles;
import com.yc.service.BaseService;
import com.yc.service.upload.AttachmentIfc;
import com.yc.utils.SessionKey;
import org.apache.commons.io.FileUtils;
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 javax.mail.*;
import javax.mail.internet.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
@@ -45,34 +28,39 @@
 */
@Service
public class MailImpl extends BaseService implements MailIfc {

    @Autowired
    MailAccountIfc emailAccountIfc;
    @Autowired
    ShoppingImageDataIfc imgData;
    @Autowired
    MailTagIfc mailTagIfc;
    @Autowired
    MailFolderIfc mailFolderIfc;

    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) \n";
                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,sender,receiver," +
                        "cc,bcc,subject,userCode,userName,content,plain_text,file_unid,messageId,read_flag,delete_flag,create_time,update_time,receive_time," +
                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.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.getFileUNID()) + "," + GridUtils.prossSqlParm(m.getMessageId()) + "," +
                        m.getReadFlag() + "," + m.getDeleteFlag() + ",getdate(),getdate()," + GridUtils.prossSqlParm(m.getReceiveTime()) + "," + m.getAttachFlag() + "," +
                        GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n";
                        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)) {
@@ -87,41 +75,75 @@
        }
    }

    @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) \n";
            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,sender,receiver," +
                        "cc,bcc,subject,userCode,userName,content,plain_text,file_unid,messageId,read_flag,delete_flag,create_time,update_time,receive_time," +
                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.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.getFileUNID()) + "," + GridUtils.prossSqlParm(m.getMessageId()) + "," +
                        m.getReadFlag() + "," + m.getDeleteFlag() + ",getdate(),getdate()," + GridUtils.prossSqlParm(m.getReceiveTime()) + "," +
                        m.getAttachFlag() + "," + GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n";
//                sql +="exec postt482101V3 '' ,'',''";
                        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;
        }
    }

    /**
     * 邮件附件的处理
     *
     * @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()) + ",file_unid=" + GridUtils.prossSqlParm(mail.getFileUNID()) + ",mailType=" + mail.getMailType() + "," +
                    "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";
@@ -131,6 +153,7 @@
        }
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Integer updateDeleteFlag(String userCode, String docCode) {
        String sql = "set nocount on\n";
@@ -145,12 +168,13 @@
        }
    }

    @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) +
            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);
@@ -164,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) {
@@ -176,9 +200,11 @@
    public t482101HEntity getReceivingMailInfo(String docCode) {
        String sql = "set nocount on\n";
        try {
            sql += "select top 1 DocCode,companyId,companyName,sender,receiver,mailType,subject,content,plain_text,file_unid as fileUNID,messageId," +
            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,DocVersion from t482101H " +
                    "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);
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(t482101HEntity.class)).get(0);
        } catch (Exception e) {
@@ -187,14 +213,14 @@
    }

    @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,sender,receiver,subject,plain_text," +
                    "receive_time,update_time,create_time,sender,userCode,userName,folder_id,delete_Flag, " +
                    "read_Flag from t482101H ";
            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,attachment_list," +
                    "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);
            sql += " and isnull(read_flag,0) = " + (isNoRead ? 0 : 1);//0表示未读，1表示已读
            sql += " and mailType=" + mailType;//0：草稿箱 1：收件箱 2：发件箱
            if (StringUtils.isNotBlank(email)) {
                if (mailType == 1) {
@@ -203,6 +229,55 @@
                    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,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));
        } catch (Exception e) {
            throw e;
@@ -213,179 +288,24 @@
    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 " +
                    "from t482101H a where  a.userCode=" + GridUtils.prossSqlParm(userCode) + " order by a.mailType";
            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;
        }
    }


    @Override
    public List<t482101HEntity> getMailReceiving(T482102Entity emailEntity, HttpServletRequest request) throws MessagingException {
        String imapServer = emailEntity.getReceiveHost();//"imap.qq.com";
        String user = emailEntity.getReceiveEmail();//"xxx@qq.com";
        String pwd = emailEntity.getReceivePassword();//"xxxx";

        Properties properties = new Properties();
        properties.put("mail.store.protocol", "imaps");//emailEntity.getReceiveProtocol()); // IMAP over SSL
        properties.put("mail.imaps.host", emailEntity.getReceiveHost());
        properties.put("mail.imaps.port", emailEntity.getReceivePort());
        properties.put("mail.imaps.starttls.enable", "true");//// IMAP 协议设置 STARTTLS

        HashMap IAM = new HashMap();
        //带上IMAP ID信息，由key和value组成，例如name，version，vendor，support-email等。
        IAM.put("name", emailEntity.getReceiveEmail());
        IAM.put("version", emailEntity.getDocVersion() + "");
        IAM.put("vendor", emailEntity.getCompanyName());
        IAM.put("support-email", emailEntity.getEmail());
        //创建会话
        Session session = Session.getInstance(properties, new Authenticator() {
            @Override
            protected PasswordAuthentication getPasswordAuthentication() {
                return new PasswordAuthentication(user, pwd);
            }
        });
        //存储对象
        IMAPStore store = (IMAPStore) session.getStore(emailEntity.getReceiveProtocol());//imap协议或pop3协议类型（推荐你使用IMAP协议来存取服务器上的邮件。）
        //连接
        store.connect(imapServer, user, pwd);
        store.id(IAM);//163邮箱需要，不然会报：A3 NO SELECT Unsafe Login. Please contact kefu@188.com for help
        Folder folder = null;
        try {
            // 获得收件箱
            folder = store.getFolder("INBOX");
            // 以读写模式打开收件箱
            folder.open(Folder.READ_WRITE);
            //false 表示未读
//            FlagTerm flagTerm = new FlagTerm(new Flags(Flags.Flag.SEEN), true);
            //获得收件箱的邮件列表(已读的，根据上面的flagTerm获取已读或未读)
//            Message[] messages = folder.search(flagTerm);
            //获取收件箱邮件（全部）
            Message[] messages = folder.getMessages();
            //返回邮箱封装内容
            return setMailContent(messages, user, request);
        } catch (NoSuchProviderException e) {
            throw e;
        } catch (MessagingException e) {
            throw e;
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (folder != null) {
                    folder.close(false);
                }
                if (store != null) {
                    store.close();
                }
            } catch (MessagingException e) {
                throw e;
            }
        }
    }


    @Override
    public void sendingMail(t482101HEntity t482101H, HttpServletRequest request) throws Exception {
        try {
            //根据当前用户查询绑定的邮箱信息
            T482102Entity emailEntity = emailAccountIfc.getAccountInfo(t482101H.getUserCode(), t482101H.getSender());//返回邮箱的账号信息
            if (emailEntity == null || StringUtils.isBlank(emailEntity.getSmtpEmail())) {
                if (StringUtils.isBlank(t482101H.getDocCode())) {
                    t482101H.setMailType(0);//草稿箱状态
                    t482101H = saveReceivingMail(t482101H);//保存到草稿箱内
                }
                throw new Exception("找不到邮箱：" + t482101H.getSender() + "配置信息请完善。邮件已保存到草稿箱#" + t482101H.getDocCode());
            }
            //有发送的邮件保存到后台数据库
            //邮箱服务器配置
            Properties properties = new Properties();
            properties.setProperty("mail.smtp.host", emailEntity.getSmtpHost());
            properties.setProperty("mail.smtp.port", emailEntity.getSmtpPort() + "");
            properties.setProperty("mail.smtp.auth", "true");// // 设置SMTP是否需要认证
            properties.put("mail.smtp.ssl.enable", emailEntity.isSmtpSSL() + "");// // 设置启用SSL加密
            properties.setProperty("mail.smtp.starttls.enable", "true");//// SMTP 协议设置 STARTTLS  开启tls

            //
            String sendEmail = emailEntity.getSmtpEmail();//发件人
            String sendPassword = emailEntity.getSmtpPassword();//密码
            String recipientEmail = StringUtils.join(t482101H.getReceiver(), ",");//收件人
            Session session = Session.getInstance(properties, new Authenticator() {
                @Override
                protected PasswordAuthentication getPasswordAuthentication() {
                    return new PasswordAuthentication(sendEmail, sendPassword);
                }
            });

            MimeMessage message = new MimeMessage(session);
            message.setFrom(new InternetAddress(sendEmail));
            message.setRecipients(Message.RecipientType.TO, InternetAddress.parse(recipientEmail));
            message.setSubject(t482101H.getSubject());

            String Unique_ID = "<onBus_" + UUID.randomUUID().toString().toUpperCase() + "@mail.com>";//系统单号唯一码
            message.setHeader("Message-ID", Unique_ID);//系统内部唯一码
            t482101H.setMessageId(Unique_ID);//messageId赋值

            if (t482101H.getCc() != null && t482101H.getCc().size() > 0) {//抄送
                message.setRecipients(Message.RecipientType.CC, InternetAddress.parse(StringUtils.join(t482101H.getCc(), ",")));
            }
            if (t482101H.getBcc() != null && t482101H.getBcc().size() > 0) {//密送
                message.setRecipients(Message.RecipientType.BCC, InternetAddress.parse(StringUtils.join(t482101H.getBcc(), ",")));
            }

            //创建多部分消息
            Multipart multipart = new MimeMultipart();

            MimeBodyPart textPart = new MimeBodyPart();
            textPart.setText(t482101H.getContent());//内容
            multipart.addBodyPart(textPart);

            //下面附件内容
            if (StringUtils.isNotBlank(t482101H.getFileUNID())) {//有附件内容
                //获取到附件内容
                List<AttachmentEntity> attachmentEntities = getAttachmentEntityList(t482101H.getFileUNID());
                if (attachmentEntities != null && attachmentEntities.size() > 0) {
                    for (AttachmentEntity a : attachmentEntities) {
                        InputStream inputStream = new ByteArrayInputStream(a.getOriginalPicture());
                        File file = new File(a.getOriginalFileName());
                        FileUtils.copyInputStreamToFile(inputStream, file);
                        MimeBodyPart bodyPart = new MimeBodyPart();
                        bodyPart.attachFile(file);
                        bodyPart.setFileName(a.getOriginalFileName());
                        multipart.addBodyPart(bodyPart);
                    }
                }
            }
            message.setContent(multipart);
            try {
                if (StringUtils.isBlank(t482101H.getDocCode())) {//不在草稿箱有单号
                    saveReceivingMail(t482101H);//保存后发送
                } else {
                    t482101H.setMailType(2);//发件箱状态
                    updateMailDrafts(t482101H);//更新成发件箱
                }
                Transport.send(message);//发送
            } catch (MessagingException m) {//发送异常处理
                t482101H.setMailType(0);//草稿箱状态
                updateMailDrafts(t482101H);//更新成草稿箱
                throw new Exception("发送邮件异常,邮件已保存在草稿箱。异常原因：" + m.getCause() != null ? m.getCause().getMessage() : m.getMessage());
            }
        } 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()) + "," +
                    "content=" + GridUtils.prossSqlParm(mail.getContent()) + ",file_unid=" + GridUtils.prossSqlParm(mail.getFileUNID()) + ",mailType=" + mail.getMailType() + "," +
                    "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";
@@ -393,259 +313,6 @@
        } catch (Exception e) {
            throw e;
        }
    }

//    @Override
//    public Integer deleteMailDrafts(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;
//        }
//    }

    /**
     * 收件箱返回的信息进行封装处理
     *
     * @param messages
     * @return
     * @throws MessagingException
     * @throws IOException
     */
    public List<t482101HEntity> setMailContent(Message[] messages, String receiver, HttpServletRequest request) throws Exception {
        try {
            List<t482101HEntity> t482101HEntityList = new ArrayList<>();
            HttpSession session = request.getSession();
            List<String> messageIdList = getMessageIdList(receiver);
            Message m = null;
            List<String> deleteMsgId = new ArrayList<>();
            for (int i = 0; i < messages.length; i++) {
                m = messages[i];
                String messageId = m.getHeader("Message-ID")[0];
                if (messageIdList != null && messageIdList.contains(messageId)) {
                    if (m.isSet(Flags.Flag.DELETED)) {//邮件是否需要删除
                        deleteMsgId.add(messageId);//删除的
                    }
                    //已经存在就不需要再次封装处理
                    continue;
                }

                t482101HEntity mail = new t482101HEntity();
                mail.setMessageId(messageId);//获取邮件唯一ID
                mail.setMailType(1);//收件
                if (m.isSet(Flags.Flag.SEEN)) {//邮件已标记为已读
                    mail.setReadFlag(1);//已读
                }
                if (m.isExpunged()) {//检查一个消息是否已被删除。‌
                    mail.setDeleteFlag(1);//已删除
                }
                if (m.isSet(Flags.Flag.ANSWERED)) {//邮件是否已回复

                }
                if (m.isSet(Flags.Flag.DRAFT)) {//是否是草稿箱
                    mail.setMailType(0);//是草稿箱
                }

                mail.setClassType(1);
                mail.setUserCode((String) session.getAttribute(SessionKey.USERCODE));
                mail.setUserName((String) session.getAttribute(SessionKey.USERNAME));
                mail.setCompanyId((String) session.getAttribute(SessionKey.COMPANY_ID));
                mail.setCompanyName((String) session.getAttribute(SessionKey.COMPANY_NAME));
                mail.setSubject(m.getSubject());//标题
                Date date = m.getReceivedDate();//时间
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                mail.setReceiveTime(formatter.format(date));
                String result = "";
                StringBuilder plainText = new StringBuilder();
                if (m.isMimeType("text/plain")) {
                    plainText.append(m.getContent());
                } else if (m.isMimeType("text/html")) {//html格式
                    result = m.getContent().toString();
                } else if (m.isMimeType("multipart/*")) {
                    List<String> fileSeq = new ArrayList<>();
                    MimeMultipart mimeMultipart = (MimeMultipart) m.getContent();
                    String uuId = UUID.randomUUID().toString().toUpperCase();//生成uuid
                    result = getTextFromMimeMultipart(mimeMultipart, uuId, fileSeq, plainText, request);
                    if (fileSeq.size() > 0) {//有附件内容保存
                        mail.setFileUNID(uuId + ";" + StringUtils.join(fileSeq, ";"));
                        mail.setAttachmentList(uuId + ";" + StringUtils.join(fileSeq, ";"));
                        mail.setAttachFlag(1);//有附件
                    }
                } else {
                    result = m.getContent().toString();
                }
                mail.setContent(result);//保存内容
                mail.setPlainText(plainText.toString().trim());//明文
                String from = MimeUtility.decodeText(m.getFrom()[0].toString());
                InternetAddress internetAddress = new InternetAddress(from);
                mail.setSender(internetAddress.getAddress());//发件人
                from = MimeUtility.decodeText(m.getRecipients(Message.RecipientType.TO)[0].toString());
                internetAddress = new InternetAddress(from);
//                mail.setReceiver(internetAddress.getAddress());//收件人
                List<String> receivers = new ArrayList<>();//抄送人
                receivers.add(receiver);
                mail.setReceiver(receivers);//统一成一个收件人

                Address[] ccAddress = m.getRecipients(Message.RecipientType.CC);
                if (ccAddress != null && ccAddress.length > 0) {
                    List<String> cc = new ArrayList<>();//抄送人
                    for (Address c : ccAddress) {
                        from = MimeUtility.decodeText(c.toString());
                        internetAddress = new InternetAddress(from);
                        cc.add(internetAddress.getAddress());
                    }
                    mail.setCc(cc);
                }
                Address[] bccAddress = m.getRecipients(Message.RecipientType.BCC);
                if (bccAddress != null && bccAddress.length > 0) {
                    List<String> bcc = new ArrayList<>();//密送
                    for (Address c : bccAddress) {
                        from = MimeUtility.decodeText(c.toString());
                        internetAddress = new InternetAddress(from);
                        bcc.add(internetAddress.getAddress());
                    }
                    mail.setBcc(bcc);//密送人
                }
                t482101HEntityList.add(mail);
            }
            if (deleteMsgId.size() > 0) {
                //删除
                deleteEmail(StringUtils.join(deleteMsgId, ","));
            }
            return t482101HEntityList;
        } catch (Exception e) {
            throw e;
        }
    }

    // 辅助方法，用于递归获取纯文本邮件内容
    private String getTextFromMimeMultipart(MimeMultipart mimeMultipart, String uuId, List<String> fileSeq, StringBuilder plainText, HttpServletRequest request) throws Exception {
        int count = mimeMultipart.getCount();
        if (count == 0) {
            throw new MessagingException("Multipart with no body parts");
        }
        boolean multipartAlternative = isMultipartAlternative(mimeMultipart);
        StringBuilder result = new StringBuilder();
        if (multipartAlternative) {
            for (int i = 0; i < count; i++) {
                BodyPart bodyPart = mimeMultipart.getBodyPart(i);
                if (bodyPart.isMimeType("text/plain")) {//这个是获取纯文本
                    plainText.append(bodyPart.getContent());
                }
                if (bodyPart.isMimeType("text/html")) {//这个是获取html格式
                    result.append(bodyPart.getContent());
                }
            }
        } else {
            for (int i = 0; i < count; i++) {
                BodyPart bodyPart = mimeMultipart.getBodyPart(i);
                if (bodyPart.isMimeType("image/*")) {//图片
                    String nextResult = result.toString();
                    String unIdPath = saveFileAndImage(bodyPart, "image", uuId, request);
                    String cId = ((IMAPBodyPart) bodyPart).getContentID();//获取cId
                    if (nextResult.contains(cId) && StringUtils.isNotBlank(unIdPath)) {//有嵌套内容
                        nextResult = nextResult.replace("cid:" + cId + "", unIdPath);//替换
                        result.setLength(0);//清空先
                        result.append(nextResult);
                    }
                } else if (Part.ATTACHMENT.equalsIgnoreCase(bodyPart.getDisposition())) {//附件
                    fileSeq.add(saveFileAndImage(bodyPart, Part.ATTACHMENT, uuId, request));
                } else if (bodyPart.isMimeType("multipart/*")) {
                    result.append(getTextFromMimeMultipart((MimeMultipart) bodyPart.getContent(), uuId, fileSeq, plainText, request));
                }
            }
        }
        return result.toString();
    }

    // 辅助方法，判断是否为multipart/alternative类型的邮件
    private boolean isMultipartAlternative(MimeMultipart mimeMultipart) throws Exception {
        boolean textPlainFound = false;
        boolean textHtmlFound = false;
        int count = mimeMultipart.getCount();
        for (int i = 0; i < count; i++) {
            BodyPart bodyPart = mimeMultipart.getBodyPart(i);
            if (bodyPart.isMimeType("text/plain")) {
                textPlainFound = true;
            } else if (bodyPart.isMimeType("text/html")) {
                textHtmlFound = true;
            }
        }
        return textPlainFound && textHtmlFound;
    }

    private String saveFileAndImage(BodyPart bodyPart, String type, String unId, HttpServletRequest request) throws Exception {
        try {
            String fileName = MimeUtility.decodeText(bodyPart.getFileName());
            if (type.equals("image")) {
                String pattern = ".*\\.(jpg|jpeg|png|gif)$";
                fileName = Pattern.matches(pattern, fileName) ? fileName : fileName + ".jpg";
            }
            File file = new File(fileName);
            FileUtils.copyInputStreamToFile(bodyPart.getInputStream(), file);
            AttachmentEntity attachmentEntity = new AttachmentEntity();
            attachmentEntity.setDoccode("");
            attachmentEntity.setRowId("");
            attachmentEntity.setFormId(482101);
            attachmentEntity.setUnid(unId);
            attachmentEntity.setSeq(null);
            attachmentEntity.setFieldId("file");
            attachmentEntity.setPhysicalFile(file.getName());
            attachmentEntity.setOriginalFileName(file.getName());
            AttachmentIfc attachmentIfc = (AttachmentIfc) FactoryBean.getBean("AttachmentImpl");
            //保存附件
            AttachmentWhereEntity attachmentWhereEntity = attachmentIfc.saveAttachment(attachmentEntity, file, "2");
            if (type.equals("image")) {
                //输出 url
//                return imgData.getImageUrl(attachmentWhereEntity.getUnid() + ";" + attachmentWhereEntity.getSeq(), null,
//                        null, true, true, null);
                DataSourceEntity dataSourceEntity = MultiDataSource.getDataSourceMap(request);
                return shoppingImageServer + "/uploads/attachment/" + dataSourceEntity.getDbId() + "/482101/" + attachmentWhereEntity.getUnid() + "@p@" + attachmentWhereEntity.getSeq() + ".jpg";
            } else if (type.equals(Part.ATTACHMENT)) {
                return attachmentWhereEntity.getSeq() + "";
            } else {
                return null;
            }
        } 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> seqs = new ArrayList<>(Arrays.asList(array));
        seqs.remove(0);//去掉第一个元素（UNID）
        String sql = " set nocount on \n"
                + " declare @unid varchar(50) = " + GridUtils.prossSqlParm(unId) + "  \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 (seqs.size() > 0) {
            sql += " and seq in(" + StringUtils.join(seqs, ",") + ")\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.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;
    }

    @Override
@@ -665,12 +332,35 @@
        }
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void updateRead(String docCode) {
    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=1 where docCode in (select list from GetInStr(@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) {
@@ -709,15 +399,19 @@
                            }
                            int allCount = 0;
                            int count = 0;
                            for (T482102Entity a:account) {
                            for (T482102Entity a : account) {
                                MailModuleEntity.MailCode nextCode = new MailModuleEntity.MailCode();
                                String eMail = (mailType == 1 ? a.getReceiveEmail() : a.getSmtpEmail());//获取邮箱账号
                                nextCode.setKey(mailType == 1 ? "page" : "page");
                                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());
//                                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);
@@ -731,13 +425,13 @@
                    case 3://待处理邮件
                        m.setChildren(new ArrayList<>());
                        if (userMail.size() > 0) {
                            m.setTotal((int) userMail.stream().filter(s -> s.getReadFlag().equals("1")).count());
                            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());
                            m.setTotal((int) userMail.stream().filter(s -> s.getReadFlag().equals(0)).count());
                        }
                        break;
                    case 5://群邮箱
@@ -752,4 +446,93 @@
            throw e;
        }
    }

    @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) {
        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 id, 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) {
                    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;
        }
    }
}