| | |
| | | String sql = "set nocount on\n"; |
| | | try { |
| | | if (StringUtils.isBlank(m.getDocCode())) { |
| | | sql += "declare @docCode varchar(50) \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," + |
| | |
| | | String sql = "set nocount on\n"; |
| | | try { |
| | | sql += "declare @docCode varchar(50) ,@unid varchar(50),@fieldId varchar(100),@originalFileName varchar(100)," + |
| | | "@SmallPicture varbinary(max),@OriginalPicture varbinary(max) \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," + |
| | |
| | | m.getAttachFlag() + "," + GridUtils.prossSqlParm(m.getAttachmentList()) + ") \n" + |
| | | mailFileSql(m.getMailFile()); |
| | | } |
| | | System.out.println("已执行:1"); |
| | | jdbcTemplate.update(sql); |
| | | } catch (Exception e) { |
| | | throw e; |
| | |
| | | originalFileName = file.getFileName(); |
| | | fileType = file.getFileType(); |
| | | sql += "select @unid=" + GridUtils.prossSqlParm(mailFile.getUnId()) + "\n" + |
| | | ",@fieldId=" + GridUtils.prossSqlParm(file.getCid()) + "\n" + |
| | | ",@fieldId=" + GridUtils.prossSqlParm(file.getFieldId()) + "\n" + |
| | | ",@originalFileName=" + GridUtils.prossSqlParm(originalFileName) + "\n" + |
| | | // ",@SmallPicture=" + UploadFiles.byteToHexString(file.getByteFile()) + "\n" + |
| | | ",@physicalFile=" + GridUtils.prossSqlParm(file.getPhysicalFile()) + "\n" + |
| | | ",@OriginalPicture=" + UploadFiles.byteToHexString(file.getByteFile()) + "\n"; |
| | | sql += "insert into _sys_Attachment(unId,formId,docCode,fieldId,physicalFile,originalFileName,FileSize,fileType,UploadTime,LastUpdateTime,OriginalPicture) values "; |
| | | sql += " (@unid,482101,@docCode,@fieldId,@originalFileName,@originalFileName," + file.getFileSize() + "," + GridUtils.prossSqlParm(fileType) + ",getdate(),getdate(),OriginalPicture) \n"; |
| | | sql += " (@unid,482101,@docCode,@fieldId,@physicalFile,@originalFileName," + file.getFileSize() + "," + GridUtils.prossSqlParm(fileType) + ",getdate(),getdate(),@OriginalPicture) \n"; |
| | | } |
| | | return sql; |
| | | } catch (Exception e) { |
| | |
| | | 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, " + |
| | | "read_Flag from t482101H "; |
| | | "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); |
| | | sql += " and isnull(read_flag,0) = " + (isNoRead ? 0 : 1);//0表示未读,1表示已读 |
| | | sql += " and mailType=" + mailType;//0:草稿箱 1:收件箱 2:发件箱 |
| | | if (StringUtils.isNotBlank(email)) { |
| | | if (mailType == 1) { |
| | |
| | | } else if (mailType == 2) { |
| | | sql += " and sender =" + GridUtils.prossSqlParm(email); |
| | | } |
| | | } |
| | | if (isNoRead) {//0表示未读,1表示已读 |
| | | sql += " and isnull(read_flag,0) = 0"; |
| | | } |
| | | return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(t482101HList.class)); |
| | | } catch (Exception e) { |
| | |
| | | public List<t482101HList> getUserMailList(String userCode) { |
| | | String sql = " set nocount on \n"; |
| | | try { |
| | | sql = "select a.DocCode,isnull(a.mailType,0) as mailType,isnull(a.read_flag,0) as read_flag,a.sender,a.receiver " + |
| | | "from t482101H a where a.userCode=" + GridUtils.prossSqlParm(userCode) + " order by a.mailType"; |
| | | sql = "select a.DocCode,isnull(a.mailType,0) as mailType,isnull(a.read_flag,0) as read_flag,a.sender,a.receiver, " + |
| | | "(case when (isnull(handle_time,'') <> '' and isnull(handle_time,'') < getdate()) then 1 else 0 end) as handle " + |
| | | " from t482101H a where a.userCode=" + GridUtils.prossSqlParm(userCode) + " order by a.mailType"; |
| | | return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(t482101HList.class)); |
| | | } catch (Exception e) { |
| | | throw e; |
| | |
| | | } |
| | | |
| | | @Override |
| | | public void updateRead(String docCode) { |
| | | public void updateRead(String docCode, boolean status) { |
| | | String sql = " set nocount on \n"; |
| | | try { |
| | | sql += "declare @docCode varchar(200) ='" + docCode + "' \n"; |
| | | sql += "update t482101H set read_flag=1 where docCode in (select list from GetInStr(@docCode))\n"; |
| | | sql += "update t482101H set read_flag=" + (status ? 1 : 0) + " where docCode in (select list from GetInStr(@docCode))\n"; |
| | | sql += "select @@ROWCOUNT"; |
| | | jdbcTemplate.queryForObject(sql, Integer.class); |
| | | } catch (Exception e) { |
| | |
| | | nextCode.setKey(mailType == 1 ? "receiver" : "sender"); |
| | | nextCode.setMailName(eMail); |
| | | nextCode.setMailType(m.getMailType()); |
| | | if (userMail.size() > 0) { |
| | | count = (mailType == 1 ? (int) userMail.stream().filter(s -> s.getMailType().equals(mailType) && s.getReceiver().contains(eMail)).count() : |
| | | (int) userMail.stream().filter(s -> s.getMailType().equals(mailType) && s.getSender().contains(eMail)).count()); |
| | | // if (userMail.size() > 0) { |
| | | // count = (mailType == 1 ? (int) userMail.stream().filter(s -> s.getMailType().equals(mailType) && s.getReceiver().contains(eMail)).count() : |
| | | // (int) userMail.stream().filter(s -> s.getMailType().equals(mailType) && s.getSender().contains(eMail)).count()); |
| | | // nextCode.setTotal(count); |
| | | // } |
| | | if (mailType == 1) {//是收件箱,统计未读数量 |
| | | count = (int) userMail.stream().filter(s -> s.getMailType().equals(mailType) && s.getReceiver().contains(eMail) && s.getReadFlag().equals(0)).count(); |
| | | nextCode.setTotal(count); |
| | | } |
| | | codeList.add(nextCode); |
| | |
| | | case 3://待处理邮件 |
| | | m.setChildren(new ArrayList<>()); |
| | | if (userMail.size() > 0) { |
| | | m.setTotal((int) userMail.stream().filter(s -> s.getReadFlag().equals("1")).count()); |
| | | m.setTotal((int) userMail.stream().filter(s -> s.getHandle().equals(1)).count()); |
| | | } |
| | | break; |
| | | case 4://未读邮件 |
| | | m.setChildren(new ArrayList<>()); |
| | | if (userMail.size() > 0) { |
| | | m.setTotal((int) userMail.stream().filter(s -> s.getReadFlag().equals("0")).count()); |
| | | m.setTotal((int) userMail.stream().filter(s -> s.getReadFlag().equals(0)).count()); |
| | | } |
| | | break; |
| | | case 5://群邮箱 |
| | |
| | | throw e; |
| | | } |
| | | } |
| | | |
| | | @Override |
| | | public void updateMailHandle(String handleTime, String docCode) { |
| | | String sql = " set nocount on \n"; |
| | | try { |
| | | sql += "declare @docCode varchar(200) ='" + docCode + "' \n"; |
| | | sql += "update t482101H set handle_time=" + (StringUtils.isBlank(handleTime) ? "null" : GridUtils.prossSqlParm(handleTime)) + |
| | | " where docCode = @docCode\n"; |
| | | sql += "select @@ROWCOUNT"; |
| | | jdbcTemplate.queryForObject(sql, Integer.class); |
| | | } catch (Exception e) { |
| | | throw e; |
| | | } |
| | | } |
| | | } |