package com.yc.crm.mail.service;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.crm.mail.entity.MailFileEntity;
|
import com.yc.entity.DataSourceEntity;
|
import com.yc.entity.attachment.AttachmentEntity;
|
import com.yc.multiData.MultiDataSource;
|
import com.yc.sdk.shopping.entity.ImageEntity;
|
import com.yc.sdk.shopping.entity.ShoppingImageEntity;
|
import com.yc.sdk.shopping.util.BlobToFile;
|
import com.yc.sdk.shopping.util.SettingKey;
|
import com.yc.service.BaseService;
|
import com.yc.utils.ImageUtils;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.dao.DataAccessException;
|
import org.springframework.dao.EmptyResultDataAccessException;
|
import org.springframework.jdbc.LobRetrievalFailureException;
|
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
|
import org.springframework.jdbc.support.lob.DefaultLobHandler;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpSession;
|
import java.io.File;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
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;
|
|
/**
|
* @BelongsProject: eCoWorksV3
|
* @BelongsPackage: com.yc.crm.mail.service
|
* @author: xinyb
|
* @CreateTime: 2024-09-19 10:19
|
* @Description:邮件附件或图片处理
|
*/
|
@Service("MailFileImpl")
|
public class MailFileImpl extends BaseService implements MailFileIfc {
|
private final ShoppingImageEntity shoppingImage = new ShoppingImageEntity();
|
@Autowired
|
private DefaultLobHandler defaultLobHandler;
|
|
@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.setFileType(list.get(i).get("FileType") == null ? null : (String) list.get(i).get("FileType"));
|
attachment.setFileSize(list.get(i).get("FileSize") == null ? 0 : (Long) list.get(i).get("FileSize"));
|
attachment.setSeq(list.get(i).get("seq") == null ? 0 : (Integer) list.get(i).get("seq"));
|
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;
|
}
|
|
|
@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 ImageEntity getImageFile(String unid, Integer width, Integer height, boolean isShowOrgImage, HttpServletRequest request, String attachmentPath, String formidPath) throws Exception {
|
HttpSession session = request.getSession();
|
if (unid == null || "".equals(unid)) {
|
// this.print(response, "获取图片时，必须传递参数 " + SettingKey.UNID);
|
return null;
|
}
|
if (isShowOrgImage) {
|
width = null;
|
height = null;
|
}
|
ShoppingImageEntity shoppingImageEntity = null;
|
DataSourceEntity dataSourceEntity = null;
|
try {
|
dataSourceEntity = MultiDataSource.getDataSourceMap(request);
|
if (dataSourceEntity == null) {
|
throw new Exception("没有找到数据源，获取图片文件失败!");
|
}
|
|
String rootPath = session.getServletContext().getRealPath("/");
|
//String uuid = UUID.randomUUID().toString().toUpperCase();
|
|
shoppingImageEntity = getImage(unid, rootPath, dataSourceEntity.getDbId(), attachmentPath, formidPath);
|
|
if (shoppingImageEntity == null || shoppingImageEntity.getImage() == null) {
|
return null;
|
}
|
|
//取原图路径
|
String shoppingImageFileName = SettingKey.getShoppingImageFileName(rootPath, unid, width, height, isShowOrgImage, dataSourceEntity.getDbId() + "", shoppingImageEntity.getFileType(), attachmentPath, formidPath);
|
File file = shoppingImageEntity.getImage();
|
|
if (file != null) {
|
|
if (width != null && height != null && shoppingImageEntity.getFileType() != null
|
//图片文件才缩放，否则会报错
|
&& StringUtils.containsAny(shoppingImageEntity.getFileType().toLowerCase(), "jpg", "png", "gif", "jpeg", "tiff", "bmp", "raw", "tga", "fpx", "webp")) {
|
// 缩放图片
|
ImageUtils.scale(file.getPath(), shoppingImageFileName, width, height);
|
} else {
|
shoppingImageFileName = file.getPath(); // 不缩放
|
}
|
ImageEntity image = new ImageEntity();
|
image.setFile(new File(shoppingImageFileName));
|
image.setFileType(shoppingImageEntity.getFileType());
|
image.setOriginalFileName(shoppingImageEntity.getOriginalFileName());
|
return image;
|
}
|
} catch (IOException e) {
|
System.out.println("输出缩略图时出错,出错文件名：" + (shoppingImageEntity != null ? shoppingImageEntity.getPhysicalFile() : "") + (dataSourceEntity != null ? "dbid:[" + dataSourceEntity.getDbId() + "],系统名称：[" + dataSourceEntity.getSystemDescribe() + "]" : ""));
|
throw e;
|
} catch (Exception e) {
|
throw e;
|
} finally {
|
//if (width != null && height != null && file != null && file.exists())
|
// file.delete();
|
}
|
return null;
|
}
|
|
private ShoppingImageEntity getImage(String unids, String rootPath, int dbId, String attachmentPath, String formidPath) {
|
if (unids == null || "".equals(unids)) return null;
|
String unid = "", mailSeq = "";
|
String unidStr[] = unids.split(SettingKey.NAVSPLIT); //
|
unid = unidStr[0];
|
if (unidStr.length > 1) {
|
String seqs[] = unidStr[1].split(";");
|
mailSeq = seqs[seqs.length - 1]; //只取最后一个 seq , 因为有时候传过来的值是: 7394;7382 ,如果不处理会导致语法错误
|
}
|
|
String sql = " set nocount on ; \n"
|
+ " declare @myrowcount int,@myerror int \n"
|
+ " declare @unid varchar(50) = " + GridUtils.prossSqlParm(unid) + " \n"
|
+ " declare @FormId int,@DocCode varchar(50),@FieldId varchar(50),@RowId varchar(50) \n"
|
+ " declare @OriginalFileName varchar(200),@PhysicalFile varchar(50),@FileType varchar(50),@FileSize bigint \n"
|
+ " declare @AuthorCode varchar(50),@AuthorName varchar(50),@OriginalPicture varbinary(max) \n"
|
|
+ " select top 1 @FormId = a.FormId,@DocCode = a.DocCode,@FieldId = a.FieldId,@RowId = a.RowId, \n"
|
+ " @OriginalFileName = a.OriginalFileName ,@PhysicalFile = a.PhysicalFile,@FileType = a.FileType,\n"
|
+ " @FileSize = isnull(a.FileSize,0),@AuthorCode = a.AuthorCode,@AuthorName = a.AuthorName,\n"
|
+ " @OriginalPicture = a.OriginalPicture \n"
|
+ " from " + (formidPath != null && "9747".equals(formidPath) ? "_sys_AttachmentLog" : "_sys_Attachment9") + " a \n"
|
+ " where a.UNID = @unid \n"
|
+ (mailSeq != null && !"".equals(mailSeq) ? " and a.fieldid = '" + mailSeq + "' \n" : "")
|
+ " select @myrowcount = @@rowcount,@myerror = @@error \n"
|
|
+ " if isnull(@myrowcount,0) = 0 \n"
|
+ " begin \n"
|
+ " select top 1 @FormId = a.FormId,@DocCode = a.DocCode,@FieldId = a.FieldId,@RowId = a.RowId, \n"
|
+ " @OriginalFileName = a.OriginalFileName ,@PhysicalFile = a.PhysicalFile,@FileType = a.FileType,\n"
|
+ " @FileSize = isnull(a.FileSize,0),@AuthorCode = a.AuthorCode,@AuthorName = a.AuthorName,\n"
|
+ " @OriginalPicture = a.OriginalPicture \n"
|
+ " from " + (formidPath != null && "9747".equals(formidPath) ? "_sys_AttachmentLog" : "_sys_Attachment") + " a \n"
|
+ " where a.UNID = @unid \n"
|
+ (mailSeq != null && !"".equals(mailSeq) ? " and a.fieldid = '" + mailSeq + "' \n" : "")
|
+ " end \n "
|
+ " select @FormId as FormId,@DocCode as DocCode,@FieldId as FieldId,@RowId as RowId, \n" +
|
" @OriginalFileName as OriginalFileName ,@PhysicalFile as PhysicalFile,@FileType as FileType,\n" +
|
" isnull(@FileSize,0) as FileSize,@AuthorCode as AuthorCode,@AuthorName as AuthorName,\n" +
|
" @OriginalPicture as OriginalPicture \n";
|
try {
|
this.jdbcTemplate.query(sql, new AbstractLobStreamingResultSetExtractor() {
|
protected void handleNoRowFound() throws LobRetrievalFailureException {
|
System.out.println("在数据库id为：" + dbId + "中，导购管理 ShoppingImageImpl 未找到 unid＝" + unids + " 的图片文件(或附件)!");
|
shoppingImage.setFormId(-1);
|
shoppingImage.setDocCode("");
|
shoppingImage.setFieldId("");
|
shoppingImage.setRowId("");
|
|
shoppingImage.setOriginalFileName("");
|
shoppingImage.setPhysicalFile(null);
|
shoppingImage.setFileType("");
|
shoppingImage.setFileSize(0L);
|
shoppingImage.setAuthorCode("");
|
shoppingImage.setAuthorName("");
|
shoppingImage.setImage(null);
|
}
|
|
@Override
|
protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException {
|
shoppingImage.setFormId(rs.getString("FormId") == null ? 0 : rs.getInt("FormId"));
|
shoppingImage.setDocCode(rs.getString("DocCode") == null ? "" : rs.getString("DocCode"));
|
shoppingImage.setFieldId(rs.getString("FieldId") == null ? "" : rs.getString("FieldId"));
|
shoppingImage.setRowId(rs.getString("RowId") == null ? "" : rs.getString("RowId"));
|
|
shoppingImage.setOriginalFileName(rs.getString("OriginalFileName") == null ? null : rs.getString("OriginalFileName"));
|
shoppingImage.setPhysicalFile(rs.getString("PhysicalFile") == null ? null : rs.getString("PhysicalFile"));
|
shoppingImage.setFileType(rs.getString("FileType") == null ? null : rs.getString("FileType"));
|
shoppingImage.setFileSize(rs.getLong("FileSize"));
|
shoppingImage.setAuthorCode(rs.getString("AuthorCode") == null ? null : rs.getString("AuthorName"));
|
shoppingImage.setAuthorName(rs.getString("AuthorName") == null ? null : rs.getString("AuthorName"));
|
|
|
//取原图路径
|
String shoppingImageFileName = SettingKey.getShoppingImageFileName(rootPath, unids, null, null, true, dbId + "", shoppingImage.getFileType(), attachmentPath, formidPath);
|
InputStream is = null;
|
byte[] bytes = null;
|
try {
|
is = defaultLobHandler.getBlobAsBinaryStream(rs, "OriginalPicture");
|
if (is != null) {
|
bytes = ShoppingImageEntity.InputStreamToByte(is); //取二进制图片
|
|
File file = BlobToFile.writeBytesToFile(bytes, shoppingImageFileName); //
|
if (file != null) {
|
shoppingImage.setImage(file);
|
}
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
throw e;
|
} finally {
|
bytes = null;
|
if (is != null) is.close();
|
}
|
|
} // end function
|
});
|
|
return shoppingImage;
|
|
} catch (DataAccessException e) {
|
if (e instanceof EmptyResultDataAccessException) {
|
return null;
|
} else {
|
System.out.println("dbid:" + dbId + ",error:" + (e.getCause() != null ? e.getCause().getMessage() : e.getMessage()));
|
e.printStackTrace();
|
throw e;
|
}
|
} catch (Exception e) {
|
System.out.println("dbid:" + dbId + ",error:" + (e.getCause() != null ? e.getCause().getMessage() : e.getMessage()));
|
e.printStackTrace();
|
throw e;
|
}
|
|
}
|
|
/**
|
* 判断是否为数字
|
*
|
* @param str
|
* @return
|
*/
|
private static boolean isNumeric(String str) {
|
Pattern pattern = Pattern.compile("\\d+");
|
Matcher matcher = pattern.matcher(str);
|
return matcher.matches();
|
}
|
}
|
