| | |
| | | import com.yc.action.grid.GridUtils; |
| | | import com.yc.crm.mail.entity.*; |
| | | import com.yc.entity.AttachmentConfig; |
| | | import com.yc.entity.attachment.AttachmentEntity; |
| | | import com.yc.sdk.shopping.service.imagedata.ShoppingImageDataIfc; |
| | | import com.yc.sdk.weixincp.util.UploadFiles; |
| | | import com.yc.service.BaseService; |
| | |
| | | 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 java.util.ArrayList; |
| | | import java.util.Arrays; |
| | | import java.util.List; |
| | | import java.util.Map; |
| | | import java.util.stream.Collectors; |
| | | |
| | | /** |
| | |
| | | |
| | | final static String shoppingImageServer = AttachmentConfig.get("attachment.server"); |
| | | |
| | | @Transactional(rollbackFor = Exception.class) |
| | | @Override |
| | | public t482101HEntity saveReceivingMail(t482101HEntity m) { |
| | | String sql = "set nocount on\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,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,receiver," + |
| | | "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()) + "," + |
| | | +m.getMailType() + "," + GridUtils.prossSqlParm(m.getSenderName()) + "," + GridUtils.prossSqlParm(m.getSender()) + "," + |
| | | GridUtils.prossSqlParm(StringUtils.join(m.getReceiver(), ",")) + "," + 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.getMessageId()) + "," + |
| | | m.getReadFlag() + "," + m.getDeleteFlag() + ",getdate(),getdate()," + GridUtils.prossSqlParm(m.getReceiveTime()) + "," + m.getAttachFlag() + "," + |
| | | GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n" + |
| | | 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); |
| | |
| | | } |
| | | } |
| | | |
| | | @Transactional(rollbackFor = Exception.class) |
| | | @Override |
| | | public void saveReceivingMailList(List<t482101HEntity> mail) { |
| | | String sql = "set nocount on\n"; |
| | |
| | | "@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,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,receiver," + |
| | | "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()) + "," + |
| | | +m.getMailType() + "," + GridUtils.prossSqlParm(m.getSenderName()) + "," + GridUtils.prossSqlParm(m.getSender()) + "," + |
| | | GridUtils.prossSqlParm(StringUtils.join(m.getReceiver(), ",")) + "," + 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.getMessageId()) + "," + |
| | | m.getReadFlag() + "," + m.getDeleteFlag() + ",getdate(),getdate()," + GridUtils.prossSqlParm(m.getReceiveTime()) + "," + |
| | | m.getAttachFlag() + "," + GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n" + |
| | | m.getReadFlag() + "," + m.getDeleteFlag() + ",getdate(),getdate()," + m.getAttachFlag() + "," + GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n" + |
| | | mailFileSql(m.getMailFile()); |
| | | } |
| | | jdbcTemplate.update(sql); |
| | |
| | | } |
| | | } |
| | | |
| | | @Transactional(rollbackFor = Exception.class) |
| | | @Override |
| | | public Integer updateReceivingMail(t482101HEntity mail) { |
| | | String sql = "set nocount on\n"; |
| | |
| | | } |
| | | } |
| | | |
| | | @Transactional(rollbackFor = Exception.class) |
| | | @Override |
| | | public Integer updateDeleteFlag(String userCode, String docCode) { |
| | | String sql = "set nocount on\n"; |
| | |
| | | } |
| | | } |
| | | |
| | | @Transactional(rollbackFor = Exception.class) |
| | | @Override |
| | | public Integer deleteEmail(String userCode, String docCode) { |
| | | String sql = "set nocount on\n"; |
| | |
| | | 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," + |
| | | "(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) { |
| | |
| | | } |
| | | |
| | | @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, " + |
| | | 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 from t482101H "; |
| | | sql += " where userCode=" + GridUtils.prossSqlParm(userCode); |
| | |
| | | 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 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; |
| | |
| | | } |
| | | } |
| | | |
| | | |
| | | @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()) + "," + |
| | | "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"; |
| | | return jdbcTemplate.queryForObject(sql, Integer.class); |
| | | } catch (Exception e) { |
| | | throw e; |
| | | } |
| | | } |
| | | |
| | | @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> 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 |
| | | 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; |
| | |
| | | } |
| | | } |
| | | |
| | | @Transactional(rollbackFor = Exception.class) |
| | | @Override |
| | | public void updateRead(String docCode, boolean status) { |
| | | String sql = " set nocount on \n"; |
| | |
| | | } |
| | | } |
| | | |
| | | @Transactional(rollbackFor = Exception.class) |
| | | @Override |
| | | public void updateMailHandle(String handleTime, String docCode) { |
| | | String sql = " set nocount on \n"; |