| 提交 | 用户 | age | ||
| 95fe95 | 1 | package com.yc.crm.mail.service; |
| X | 2 | |
| 3 | import com.yc.action.grid.GridUtils; | |
| 4 | import com.yc.crm.mail.entity.MailFileEntity; | |
| 5 | import com.yc.entity.DataSourceEntity; | |
| 6 | import com.yc.entity.attachment.AttachmentEntity; | |
| 7 | import com.yc.multiData.MultiDataSource; | |
| 8 | import com.yc.sdk.shopping.entity.ImageEntity; | |
| 9 | import com.yc.sdk.shopping.entity.ShoppingImageEntity; | |
| 10 | import com.yc.sdk.shopping.util.BlobToFile; | |
| 11 | import com.yc.sdk.shopping.util.SettingKey; | |
| 12 | import com.yc.service.BaseService; | |
| 13 | import com.yc.utils.ImageUtils; | |
| 14 | import org.apache.commons.lang3.StringUtils; | |
| 15 | import org.springframework.beans.factory.annotation.Autowired; | |
| 16 | import org.springframework.dao.DataAccessException; | |
| 17 | import org.springframework.dao.EmptyResultDataAccessException; | |
| 18 | import org.springframework.jdbc.LobRetrievalFailureException; | |
| 19 | import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor; | |
| 20 | import org.springframework.jdbc.support.lob.DefaultLobHandler; | |
| 21 | import org.springframework.stereotype.Service; | |
| 22 | import org.springframework.transaction.annotation.Transactional; | |
| 23 | ||
| 24 | import javax.servlet.http.HttpServletRequest; | |
| 25 | import javax.servlet.http.HttpSession; | |
| 26 | import java.io.File; | |
| 27 | import java.io.IOException; | |
| 28 | import java.io.InputStream; | |
| 29 | import java.sql.ResultSet; | |
| 30 | import java.sql.SQLException; | |
| 31 | import java.util.ArrayList; | |
| 32 | import java.util.Arrays; | |
| 33 | import java.util.List; | |
| 34 | import java.util.Map; | |
| 35 | import java.util.regex.Matcher; | |
| 36 | import java.util.regex.Pattern; | |
| 37 | ||
| 38 | /** | |
| 39 | * @BelongsProject: eCoWorksV3 | |
| 40 | * @BelongsPackage: com.yc.crm.mail.service | |
| 41 | * @author: xinyb | |
| 42 | * @CreateTime: 2024-09-19 10:19 | |
| 43 | * @Description:邮件附件或图片处理 | |
| 44 | */ | |
| 45 | @Service("MailFileImpl") | |
| 46 | public class MailFileImpl extends BaseService implements MailFileIfc { | |
| 47 | private final ShoppingImageEntity shoppingImage = new ShoppingImageEntity() ; | |
| 48 | @Autowired | |
| 49 | private DefaultLobHandler defaultLobHandler; | |
| 50 | @Override | |
| 51 | public void saveAttachment(List<MailFileEntity> mailFile) { | |
| 52 | try { | |
| 53 | ||
| 54 | } catch (Exception e) { | |
| 55 | throw e; | |
| 56 | } | |
| 57 | } | |
| 58 | ||
| 59 | ||
| 60 | @Override | |
| 61 | public List<AttachmentEntity> getAttachmentEntityList(String unIdSeq) { | |
| 62 | String unId = null; | |
| 63 | if (StringUtils.isBlank(unIdSeq)) { | |
| 64 | return null; | |
| 65 | } | |
| 66 | String[] array = unIdSeq.split(";"); | |
| 67 | unId = array[0];//在有值时候第一个必定是unId | |
| 68 | ArrayList<String> fieldId = new ArrayList<>(Arrays.asList(array)); | |
| 69 | fieldId.remove(0);//去掉第一个元素（UNID） | |
| 70 | String sql = " set nocount on \n" | |
| 71 | + " declare @unid varchar(50) = " + GridUtils.prossSqlParm(unId) + | |
| 72 | ",@fieldid varchar(1000) = " + GridUtils.prossSqlParm(StringUtils.join(fieldId, ",")) + " \n"; | |
| 73 | ||
| 74 | sql += " select UNID,seq,DocCode,RowId,FieldId,FormId, \n" | |
| 75 | + " PhysicalPath,PhysicalFile,OriginalFileName,FileSize,FileType, \n" | |
| 76 | + " AuthorCode,AuthorName,SmallPicPath,UploadTime,OriginalPicture \n" | |
| 77 | + " from _sys_Attachment \n" | |
| 78 | + " where unid = @unid "; | |
| 79 | if (fieldId.size() > 0) { | |
| 80 | if (isNumeric(fieldId.get(0))) { | |
| 81 | sql += " and seq in(select list from GetInStr(@fieldid)) \n"; | |
| 82 | } else { | |
| 83 | sql += " and fieldid in(select list from GetInStr(@fieldid)) \n"; | |
| 84 | } | |
| 85 | } | |
| 86 | List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql); | |
| 87 | List<AttachmentEntity> attachmentList = new ArrayList<AttachmentEntity>(); | |
| 88 | for (int i = 0; list != null && i < list.size(); i++) { | |
| 89 | AttachmentEntity attachment = new AttachmentEntity(); | |
| 90 | attachment.setUnid(unId); | |
| 56d9f6 | 91 | attachment.setFileType(list.get(i).get("FileType") == null ? null : (String) list.get(i).get("FileType")); |
| X | 92 | attachment.setFileSize(list.get(i).get("FileSize") == null ? 0 : (Long) list.get(i).get("FileSize")); |
| 95fe95 | 93 | attachment.setPhysicalFile(list.get(i).get("PhysicalFile") == null ? null : (String) list.get(i).get("PhysicalFile")); |
| X | 94 | attachment.setOriginalFileName(list.get(i).get("OriginalFileName") == null ? null : (String) list.get(i).get("OriginalFileName")); |
| 95 | attachment.setOriginalPicture(list.get(i).get("OriginalPicture") == null ? null : (byte[]) list.get(i).get("OriginalPicture")); //附件处理 | |
| 96 | attachmentList.add(attachment); | |
| 97 | } | |
| 98 | return attachmentList; | |
| 99 | } | |
| 100 | ||
| 101 | ||
| 102 | ||
| 103 | @Transactional(rollbackFor = Exception.class) | |
| 104 | @Override | |
| 105 | public Integer deleteAttachment(List<String> unId) { | |
| 106 | try { | |
| 107 | if (unId == null || unId.size() == 0) { | |
| 108 | return 0; | |
| 109 | } | |
| 110 | String sql = " set nocount on \n" | |
| 111 | + " declare @unid varchar(4000) = '" + StringUtils.join(unId, ",") + "' \n"; | |
| 112 | sql += "delete _sys_Attachment where unid in (select list from GetInStr(@unid)) \n"; | |
| 113 | sql += "select @@ROWCOUNT"; | |
| 114 | return jdbcTemplate.queryForObject(sql, Integer.class); | |
| 115 | } catch (Exception e) { | |
| 116 | throw e; | |
| 117 | } | |
| 118 | } | |
| 119 | ||
| 120 | @Override | |
| 121 | public ImageEntity getImageFile(String unid, Integer width, Integer height, boolean isShowOrgImage, HttpServletRequest request, String attachmentPath, String formidPath) throws Exception { | |
| 122 | HttpSession session = request.getSession(); | |
| 123 | if (unid == null || "".equals(unid)) { | |
| 124 | // this.print(response, "获取图片时，必须传递参数 " + SettingKey.UNID); | |
| 125 | return null; | |
| 126 | } | |
| 127 | if (isShowOrgImage) { | |
| 128 | width = null; | |
| 129 | height = null; | |
| 130 | } | |
| 131 | ShoppingImageEntity shoppingImageEntity = null ; | |
| 132 | DataSourceEntity dataSourceEntity = null; | |
| 133 | try { | |
| 134 | dataSourceEntity = MultiDataSource.getDataSourceMap( request) ; | |
| 135 | if (dataSourceEntity == null) { | |
| 136 | throw new Exception("没有找到数据源，获取图片文件失败!" ) ; | |
| 137 | } | |
| 138 | ||
| 139 | String rootPath = session.getServletContext().getRealPath("/"); | |
| 140 | //String uuid = UUID.randomUUID().toString().toUpperCase(); | |
| 141 | ||
| 142 | shoppingImageEntity = getImage( unid,rootPath ,dataSourceEntity.getDbId(),attachmentPath,formidPath); | |
| 143 | ||
| 144 | if (shoppingImageEntity == null || shoppingImageEntity.getImage() == null) { | |
| 145 | return null; | |
| 146 | } | |
| 147 | ||
| 148 | //取原图路径 | |
| 149 | String shoppingImageFileName = SettingKey.getShoppingImageFileName(rootPath,unid, width,height, isShowOrgImage, dataSourceEntity.getDbId()+"", shoppingImageEntity.getFileType(),attachmentPath,formidPath); | |
| 150 | File file = shoppingImageEntity.getImage(); | |
| 151 | ||
| 152 | if (file != null) { | |
| 153 | ||
| 154 | if (width != null && height != null && shoppingImageEntity.getFileType()!=null | |
| 155 | //图片文件才缩放，否则会报错 | |
| 156 | && StringUtils.containsAny(shoppingImageEntity.getFileType().toLowerCase(),"jpg","png","gif","jpeg","tiff","bmp","raw","tga","fpx","webp")) { | |
| 157 | // 缩放图片 | |
| 158 | ImageUtils.scale(file.getPath(), shoppingImageFileName, width, height); | |
| 159 | } else { | |
| 160 | shoppingImageFileName = file.getPath(); // 不缩放 | |
| 161 | } | |
| 162 | ImageEntity image = new ImageEntity(); | |
| 163 | image.setFile(new File(shoppingImageFileName)); | |
| 164 | image.setFileType(shoppingImageEntity.getFileType()); | |
| 165 | image.setOriginalFileName(shoppingImageEntity.getOriginalFileName()); | |
| 166 | return image; | |
| 167 | } | |
| 168 | } catch (IOException e) { | |
| 169 | System.out.println("输出缩略图时出错,出错文件名：" + (shoppingImageEntity!=null?shoppingImageEntity.getPhysicalFile():"") + (dataSourceEntity!=null?"dbid:["+dataSourceEntity.getDbId() + "],系统名称：["+ dataSourceEntity.getSystemDescribe()+"]":"") ); | |
| 170 | throw e; | |
| 171 | }catch (Exception e) { | |
| 172 | throw e ; | |
| 173 | } finally { | |
| 174 | //if (width != null && height != null && file != null && file.exists()) | |
| 175 | // file.delete(); | |
| 176 | } | |
| 177 | return null; | |
| 178 | } | |
| 179 | ||
| 180 | private ShoppingImageEntity getImage(String unids, String rootPath, int dbId, String attachmentPath, String formidPath) { | |
| 181 | if (unids == null || "".equals(unids)) return null ; | |
| 182 | String unid = "" ,mailSeq = "" ; | |
| 183 | String unidStr[] = unids.split(SettingKey.NAVSPLIT) ; // | |
| 184 | unid = unidStr[0] ; | |
| 185 | if (unidStr.length > 1 ) { | |
| 186 | String seqs[] = unidStr[1].split(";") ; | |
| 187 | mailSeq = seqs[seqs.length-1]; //只取最后一个 seq , 因为有时候传过来的值是: 7394;7382 ,如果不处理会导致语法错误 | |
| 188 | } | |
| 189 | ||
| 190 | String sql = " set nocount on ; \n" | |
| 191 | + " declare @myrowcount int,@myerror int \n" | |
| 192 | + " declare @unid varchar(50) = "+ GridUtils.prossSqlParm(unid)+" \n" | |
| 193 | + " declare @FormId int,@DocCode varchar(50),@FieldId varchar(50),@RowId varchar(50) \n" | |
| 194 | + " declare @OriginalFileName varchar(200),@PhysicalFile varchar(50),@FileType varchar(50),@FileSize bigint \n" | |
| 195 | + " declare @AuthorCode varchar(50),@AuthorName varchar(50),@OriginalPicture varbinary(max) \n" | |
| 196 | ||
| 197 | + " select top 1 @FormId = a.FormId,@DocCode = a.DocCode,@FieldId = a.FieldId,@RowId = a.RowId, \n" | |
| 198 | + " @OriginalFileName = a.OriginalFileName ,@PhysicalFile = a.PhysicalFile,@FileType = a.FileType,\n" | |
| 199 | + " @FileSize = isnull(a.FileSize,0),@AuthorCode = a.AuthorCode,@AuthorName = a.AuthorName,\n" | |
| 200 | + " @OriginalPicture = a.OriginalPicture \n" | |
| 201 | + " from " + (formidPath!=null&&"9747".equals(formidPath)?"_sys_AttachmentLog":"_sys_Attachment9") + " a \n" | |
| 202 | + " where a.UNID = @unid \n" | |
| 203 | + (mailSeq !=null && !"".equals(mailSeq)?" and a.fieldid = '" + mailSeq + "' \n":"") | |
| 204 | + " select @myrowcount = @@rowcount,@myerror = @@error \n" | |
| 205 | ||
| 206 | + " if isnull(@myrowcount,0) = 0 \n" | |
| 207 | + " begin \n" | |
| 208 | + " select top 1 @FormId = a.FormId,@DocCode = a.DocCode,@FieldId = a.FieldId,@RowId = a.RowId, \n" | |
| 209 | + " @OriginalFileName = a.OriginalFileName ,@PhysicalFile = a.PhysicalFile,@FileType = a.FileType,\n" | |
| 210 | + " @FileSize = isnull(a.FileSize,0),@AuthorCode = a.AuthorCode,@AuthorName = a.AuthorName,\n" | |
| 211 | + " @OriginalPicture = a.OriginalPicture \n" | |
| 212 | + " from "+ (formidPath!=null&&"9747".equals(formidPath)?"_sys_AttachmentLog":"_sys_Attachment") +" a \n" | |
| 213 | + " where a.UNID = @unid \n" | |
| 214 | + (mailSeq !=null && !"".equals(mailSeq)?" and a.fieldid = '" + mailSeq + "' \n":"") | |
| 215 | + " end \n " | |
| 216 | + " select @FormId as FormId,@DocCode as DocCode,@FieldId as FieldId,@RowId as RowId, \n" + | |
| 217 | " @OriginalFileName as OriginalFileName ,@PhysicalFile as PhysicalFile,@FileType as FileType,\n" + | |
| 218 | " isnull(@FileSize,0) as FileSize,@AuthorCode as AuthorCode,@AuthorName as AuthorName,\n" + | |
| 219 | " @OriginalPicture as OriginalPicture \n"; | |
| 220 | try { | |
| 221 | this.jdbcTemplate.query(sql,new AbstractLobStreamingResultSetExtractor(){ | |
| 222 | protected void handleNoRowFound() throws LobRetrievalFailureException { | |
| 223 | System.out.println("在数据库id为：" + dbId + "中，导购管理 ShoppingImageImpl 未找到 unid＝" + unids + " 的图片文件(或附件)!"); | |
| 224 | shoppingImage.setFormId(-1); | |
| 225 | shoppingImage.setDocCode(""); | |
| 226 | shoppingImage.setFieldId(""); | |
| 227 | shoppingImage.setRowId(""); | |
| 228 | ||
| 229 | shoppingImage.setOriginalFileName(""); | |
| 230 | shoppingImage.setPhysicalFile(null); | |
| 231 | shoppingImage.setFileType(""); | |
| 232 | shoppingImage.setFileSize(0L); | |
| 233 | shoppingImage.setAuthorCode(""); | |
| 234 | shoppingImage.setAuthorName(""); | |
| 235 | shoppingImage.setImage(null) ; | |
| 236 | } | |
| 237 | ||
| 238 | @Override | |
| 239 | protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException { | |
| 240 | shoppingImage.setFormId(rs.getString("FormId") == null?0:rs.getInt("FormId")); | |
| 241 | shoppingImage.setDocCode(rs.getString("DocCode") == null?"":rs.getString("DocCode")); | |
| 242 | shoppingImage.setFieldId(rs.getString("FieldId") == null?"":rs.getString("FieldId")); | |
| 243 | shoppingImage.setRowId(rs.getString("RowId") == null?"":rs.getString("RowId")); | |
| 244 | ||
| 245 | shoppingImage.setOriginalFileName(rs.getString("OriginalFileName") == null?null:rs.getString("OriginalFileName")); | |
| 246 | shoppingImage.setPhysicalFile(rs.getString("PhysicalFile") == null?null:rs.getString("PhysicalFile")); | |
| 247 | shoppingImage.setFileType(rs.getString("FileType") == null?null:rs.getString("FileType")); | |
| 248 | shoppingImage.setFileSize(rs.getLong("FileSize")); | |
| 249 | shoppingImage.setAuthorCode(rs.getString("AuthorCode") == null?null:rs.getString("AuthorName")); | |
| 250 | shoppingImage.setAuthorName(rs.getString("AuthorName") == null?null:rs.getString("AuthorName")); | |
| 251 | ||
| 252 | ||
| 253 | //取原图路径 | |
| 254 | String shoppingImageFileName = SettingKey.getShoppingImageFileName(rootPath,unids, null, null, true, dbId+"", shoppingImage.getFileType(),attachmentPath,formidPath); | |
| 255 | InputStream is = null ; | |
| 256 | byte[] bytes = null ; | |
| 257 | try { | |
| 258 | is = defaultLobHandler.getBlobAsBinaryStream(rs, "OriginalPicture"); | |
| 259 | if (is != null) { | |
| 260 | bytes = ShoppingImageEntity.InputStreamToByte( is); //取二进制图片 | |
| 261 | ||
| 262 | File file = BlobToFile.writeBytesToFile(bytes,shoppingImageFileName); // | |
| 263 | if (file != null) { | |
| 264 | shoppingImage.setImage(file); | |
| 265 | } | |
| 266 | } | |
| 267 | } catch (IOException e) { | |
| 268 | e.printStackTrace(); | |
| 269 | throw e ; | |
| 270 | } finally { | |
| 271 | bytes = null ; | |
| 272 | if (is != null) is.close(); | |
| 273 | } | |
| 274 | ||
| 275 | } // end function | |
| 276 | }); | |
| 277 | ||
| 278 | return shoppingImage ; | |
| 279 | ||
| 280 | }catch(DataAccessException e ) { | |
| 281 | if (e instanceof EmptyResultDataAccessException){ | |
| 282 | return null ; | |
| 283 | }else { | |
| 284 | System.out.println("dbid:" + dbId + ",error:" + (e.getCause() != null ?e.getCause().getMessage():e.getMessage())) ; | |
| 285 | e.printStackTrace(); | |
| 286 | throw e; | |
| 287 | } | |
| 288 | }catch(Exception e){ | |
| 289 | System.out.println("dbid:" + dbId + ",error:" + (e.getCause() != null ?e.getCause().getMessage():e.getMessage())) ; | |
| 290 | e.printStackTrace(); | |
| 291 | throw e; | |
| 292 | } | |
| 293 | ||
| 294 | } | |
| 295 | /** | |
| 296 | * 判断是否为数字 | |
| 297 | * | |
| 298 | * @param str | |
| 299 | * @return | |
| 300 | */ | |
| 301 | private static boolean isNumeric(String str) { | |
| 302 | Pattern pattern = Pattern.compile("\\d+"); | |
| 303 | Matcher matcher = pattern.matcher(str); | |
| 304 | return matcher.matches(); | |
| 305 | } | |
| 306 | } | |
