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 mailFile) { try { } catch (Exception e) { throw e; } } @Override public List getAttachmentEntityList(String unIdSeq) { String unId = null; if (StringUtils.isBlank(unIdSeq)) { return null; } String[] array = unIdSeq.split(";"); unId = array[0];//在有值时候第一个必定是unId ArrayList 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> list = this.jdbcTemplate.queryForList(sql); List attachmentList = new ArrayList(); 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 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(); } }