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.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();
|
}
|
}
|