package com.yc.crm.base.service;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.crm.base.entity.*;
|
import com.yc.crm.base.util.Utils;
|
import com.yc.crm.clues.entity.T480107Entity;
|
import com.yc.crm.clues.entity.T480114Entity;
|
import com.yc.exception.ApplicationException;
|
import com.yc.service.BaseService;
|
import com.yc.utils.DateUtil;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.beans.BeanUtils;
|
import org.springframework.jdbc.core.BeanPropertyRowMapper;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import javax.servlet.http.HttpSession;
|
import java.text.SimpleDateFormat;
|
import java.time.LocalDateTime;
|
import java.time.ZoneId;
|
import java.time.format.DateTimeFormatter;
|
import java.time.temporal.ChronoUnit;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class CrmServiceImpl extends BaseService implements CrmServiceIfc {
|
@Override
|
public Map getBaseInfo(String userCode) {
|
HashMap map = new HashMap();
|
//----系统参数
|
List<SysdictEntity> list = this.jdbcTemplate.query("select dictid,interValue as id,dictvalue as name from _sysdict where dictid in(-48013000,-48013001,-48013002,-48013003,-48013004,-48013005,-48013006,-48013007,-48013008,-48013009,-48013010,-48013011,-48013012,-48013013,-48013014,-48013015)\n" +
|
"order by dictid desc,sequence\n ", new BeanPropertyRowMapper<>(SysdictEntity.class));
|
map.put("bizType", list.stream().filter(x -> x.getDictid() == -48013000).collect(Collectors.toList()));
|
map.put("intentionLevel", list.stream().filter(x -> x.getDictid() == -48013001).collect(Collectors.toList()));
|
map.put("annualProcurement", list.stream().filter(x -> x.getDictid() == -48013002).collect(Collectors.toList()));
|
map.put("timezone", list.stream().filter(x -> x.getDictid() == -48013003).collect(Collectors.toList()));
|
map.put("scaleId", list.stream().filter(x -> x.getDictid() == -48013004).collect(Collectors.toList()));
|
map.put("inquiryOrigin", list.stream().filter(x -> x.getDictid() == -48013005).collect(Collectors.toList()));
|
map.put("inquiryCountry", list.stream().filter(x -> x.getDictid() == -48013006).collect(Collectors.toList()));
|
map.put("mediaList", list.stream().filter(x -> x.getDictid() == -48013007).collect(Collectors.toList()));
|
map.put("telAreaCode", list.stream().filter(x -> x.getDictid() == -48013008).collect(Collectors.toList()));
|
map.put("postGrade", list.stream().filter(x -> x.getDictid() == -48013009).collect(Collectors.toList()));
|
map.put("systemFlag", list.stream().filter(x -> x.getDictid() == -48013010).collect(Collectors.toList()));
|
map.put("followUpType", list.stream().filter(x -> x.getDictid() == -48013011).collect(Collectors.toList()));
|
map.put("cluesStatusFlag", list.stream().filter(x -> x.getDictid() == -48013012).collect(Collectors.toList()));
|
map.put("failStatus", list.stream().filter(x -> x.getDictid() == -48013013).collect(Collectors.toList()));
|
map.put("quickTxtList", list.stream().filter(x -> x.getDictid() == -48013014).collect(Collectors.toList()));
|
map.put("remindTimes", list.stream().filter(x -> x.getDictid() == -48013015).collect(Collectors.toList()));
|
//---线索来源
|
List<T480107Entity> t480107Entities = this.jdbcTemplate.query("select leadSourceCode\n" +
|
",leadSourceName\n" +
|
",memo \n" +
|
",rowid \n" +
|
",treecontrol \n" +
|
",isnull(parentrowid,'') as parentrowid from t480107", new BeanPropertyRowMapper<>(T480107Entity.class));
|
List<T480107Entity> treeEntiries = Utils.convertToTree(t480107Entities, T480107Entity::getParentrowid, T480107Entity::getRowid);
|
map.put("originList", treeEntiries);
|
//---国家
|
List<T480114Entity> t480114Entities = this.jdbcTemplate.query("select " +
|
// "areaid as regionsCode\n" +
|
"regionsCode\n" +
|
//",areaname as regionsName\n" +
|
",regionsName\n" +
|
"--,memo \n" +
|
",rowid \n" +
|
",treecontrol \n" +//t480114,t110202
|
",isnull(parentrowid,'') as parentrowid from t480114", new BeanPropertyRowMapper<>(T480114Entity.class));
|
List<T480114Entity> treeEntiries114 = Utils.convertToTree(t480114Entities, T480114Entity::getParentrowid, T480114Entity::getRowid);
|
map.put("country", treeEntiries114);
|
//---省市
|
List<T110202Entity> t110202Entities = this.jdbcTemplate.query("select " +
|
"areaid\n" +
|
",areaname\n" +
|
"--,memo \n" +
|
",rowid \n" +
|
",treecontrol \n" +//t480114,t110202
|
",isnull(parentrowid,'') as parentrowid from t110202", new BeanPropertyRowMapper<>(T110202Entity.class));
|
List<T110202Entity> treeEntiries110202 = Utils.convertToTree(t110202Entities, T110202Entity::getParentrowid, T110202Entity::getRowid);
|
map.put("province", treeEntiries110202);
|
//---主营产品
|
List<T480116Entity> t480116Entities = this.jdbcTemplate.query("select categoryId\n" +
|
",categoryName\n" +
|
",memo \n" +
|
",rowid \n" +
|
",treecontrol \n" +//t480114
|
",isnull(parentrowid,'') as parentrowid from t480116", new BeanPropertyRowMapper<>(T480116Entity.class));
|
List<T480116Entity> treeEntiries116 = Utils.convertToTree(t480116Entities, T480116Entity::getParentrowid, T480116Entity::getRowid);
|
map.put("categoryIds", treeEntiries116);
|
|
//---标签
|
List<T482115Entity> t482115Entities = this.jdbcTemplate.query("select tag_Id, \n" +
|
"tag_Name, \n" +
|
" sort_Id, \n" +
|
" system_Flag,\n" +
|
"tag_Color, \n" +
|
" tag_Type,create_time,update_time from t482115 where isnull(system_Flag,0)=1 or userCode= " + GridUtils.prossSqlParm(userCode) + " order by sort_Id asc", new BeanPropertyRowMapper<>(T482115Entity.class));
|
map.put("tags", t482115Entities);
|
return map;
|
}
|
|
@Override
|
public Object getUserInfo(HttpSession session, Page page) {
|
// String ccCode = (String) session.getAttribute(SessionKey.CCCODE);
|
String where = "";
|
if (page != null && StringUtils.isNotBlank(page.getKey())) {
|
|
where = " where email like '%" + page.getKey() + "%' or userName like '%" + page.getKey() + "%'";
|
}
|
String sql = " select ccCode,ccName,userCode,userName,email from _sysuser " + where + "order by ccCode asc";
|
List<UserEntity> query = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserEntity.class));
|
if (page != null && StringUtils.isNotBlank(page.getKey())) {
|
//直接输出符合条件的用户列表，没树形结构
|
return query;
|
} else {
|
// 没查询email条件则输出树形结构
|
List<DepartmentsEntity> departments = query.stream().distinct().map(x -> {
|
DepartmentsEntity departmentsEntity = new DepartmentsEntity();
|
departmentsEntity.setCcCode(x.getCcCode());
|
departmentsEntity.setCcName(x.getCcName());
|
return departmentsEntity;
|
}).collect(Collectors.toList());
|
departments.stream().forEach(x -> {
|
List<UserEntity> collect = query.stream().filter(x1 -> x1.getCcCode().equalsIgnoreCase(x.getCcCode())).collect(Collectors.toList());
|
x.setSubList(collect);
|
});
|
return departments;
|
}
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void followUpSave(T482105Entity entity) {
|
if (StringUtils.isBlank(entity.getContent())) {
|
throw new ApplicationException("跟进内容不能为空");
|
}
|
String sql = "declare " +
|
" @companyId varchar(50), \n" +
|
" @companyName nvarchar(100),\n" +
|
" @userCode varchar(30), \n" +
|
" @userName nvarchar(30),\n" +
|
" @id bigint, \n" +
|
" @refCode varchar(50),\n" +
|
" @enable_flag bit, \n" +
|
" @delete_flag bit, \n" +
|
" @inquiry_id int, \n" +
|
" @comment_count int, \n" +
|
" @content nvarchar(4000),\n" +
|
" @plain_content nvarchar(4000),\n" +
|
" @latitude varchar(50), \n" +
|
" @longitude varchar(50), \n" +
|
" @address nvarchar(200),\n" +
|
" @attach_list varchar(150),\n" +
|
" @followUp_type nvarchar(20),\n" +
|
" @followUser varchar(20),\n" +
|
" @followUp_next_time datetime, \n" +
|
" @create_time datetime, \n" +
|
" @update_time datetime\n" +
|
" select @companyId=" + GridUtils.prossSqlParm(entity.getCompanyId()) + "\n" +
|
",@companyName=" + GridUtils.prossSqlParm(entity.getCompanyName()) + "\n" +
|
",@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + "\n" +
|
",@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + "\n" +
|
",@id=" + entity.getId() + "\n" +
|
",@refCode=" + GridUtils.prossSqlParm(entity.getRefCode()) + "\n" +
|
",@inquiry_id=" + entity.getInquiryId() + "\n" +
|
",@content=" + GridUtils.prossSqlParm(entity.getContent()) + "\n" +
|
",@plain_content=" + GridUtils.prossSqlParm(entity.getPlainContent()) + "\n" +
|
",@latitude=" + GridUtils.prossSqlParm(entity.getLatitude()) + "\n" +
|
",@longitude=" + GridUtils.prossSqlParm(entity.getLongitude()) + "\n" +
|
",@address=" + GridUtils.prossSqlParm(entity.getAddress()) + "\n" +
|
",@attach_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + "\n" +
|
",@followUp_type=" + GridUtils.prossSqlParm(entity.getFollowUpType()) + "\n" +
|
",@followUser=" + GridUtils.prossSqlParm(entity.getFollowUser()) + "\n" +
|
",@followUp_next_time=" + GridUtils.prossSqlParm(entity.getFollowUpNextTime()) + "\n" +
|
(StringUtils.isNotBlank(entity.getCreateTime()) ? ",@create_time=" + GridUtils.prossSqlParm(entity.getCreateTime()) + "\n" : ",@create_time=getdate() \n") +
|
",@update_time=getdate() \n" +
|
" if isnull(@id,'')='' \n" +//新增
|
" begin \n" +
|
" insert into t482105(" +
|
"companyId \n" +
|
",companyName \n" +
|
",userCode \n" +
|
",userName \n" +
|
",refCode \n" +
|
",inquiry_id \n" +
|
",content \n" +
|
",plain_content \n" +
|
",latitude \n" +
|
",longitude \n" +
|
",address \n" +
|
",attach_list \n" +
|
",followUp_type \n" +
|
",followUser \n" +
|
",followUp_next_time \n" +
|
",create_time \n" +
|
",update_time " +
|
")values(" +
|
"@companyId \n" +
|
",@companyName \n" +
|
",@userCode \n" +
|
",@userName \n" +
|
",@refCode \n" +
|
",@inquiry_id \n" +
|
",@content \n" +
|
",@plain_content \n" +
|
",@latitude \n" +
|
",@longitude \n" +
|
",@address \n" +
|
",@attach_list \n" +
|
",@followUp_type \n" +
|
",@followUser \n" +
|
",@followUp_next_time \n" +
|
",@create_time \n" +
|
",@update_time )\n" +
|
"end \n" +
|
" else \n" +//修改
|
"begin\n" +
|
" update a set \n" +
|
" content=" + GridUtils.prossSqlParm(entity.getContent()) + "\n" +
|
",plain_content=" + GridUtils.prossSqlParm(entity.getPlainContent()) + "\n" +
|
",latitude =" + GridUtils.prossSqlParm(entity.getLatitude()) + "\n" +
|
",longitude=" + GridUtils.prossSqlParm(entity.getLongitude()) + "\n" +
|
",address=" + GridUtils.prossSqlParm(entity.getAddress()) + "\n" +
|
",attach_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + "\n" +
|
",followUp_type=" + GridUtils.prossSqlParm(entity.getFollowUpType()) + "\n" +
|
",followUser=" + GridUtils.prossSqlParm(entity.getFollowUser()) + "\n" +
|
",followUp_next_time=" + GridUtils.prossSqlParm(entity.getFollowUpNextTime()) + "\n" +
|
",update_time=@update_time from t482105 a where id=@id \n " +
|
"end\n";
|
this.doBaseExecute(sql);
|
}
|
|
@Override
|
public Response followUpList(FollowUpRequestEntity request) {
|
String where = "";
|
if (request.getSearchTxt() != null && request.getSearchTxt().size() > 0) {
|
//拼接查询条件
|
where = " where ( 1=1 ";
|
Set<Map.Entry<String, String>> entries = request.getSearchTxt().entrySet();
|
for (Map.Entry<String, String> entry : entries) {
|
if (entry.getKey().equalsIgnoreCase("refCode")) {
|
where += " and refCode=" + GridUtils.prossSqlParm(entry.getValue());
|
}
|
if (entry.getKey().equalsIgnoreCase("followUpType")) {
|
where += " and followUp_type=" + GridUtils.prossSqlParm(entry.getValue());
|
}
|
if (entry.getKey().equalsIgnoreCase("formid")) {
|
request.setFormid(entry.getValue());
|
}
|
}
|
where += ")";
|
}
|
String fileds =
|
" companyId \n" +
|
",companyName \n" +
|
",userCode \n" +
|
",userName \n" +
|
",id,refCode \n" +
|
",inquiry_id \n" +
|
",comment_count \n" +
|
",content \n" +
|
",plain_content \n" +
|
",latitude \n" +
|
",longitude \n" +
|
",address \n" +
|
",attach_list \n" +
|
",followUp_type \n" +
|
",followUser \n" +
|
",followUp_next_time \n" +
|
",create_time \n" +
|
",case when convert(varchar(19),dateadd(hh,1,update_time),120)>convert(varchar(19),GETDATE(),120) then 1 else 0 end as deleteFlag" +//1小时内可以删除跟进
|
",update_time ,@TotalRowCount as totalRowCount ,@pageCount as pageCount ";
|
|
String sql = "set nocount on ; \n" +
|
" declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" +
|
" select @Limit = " + request.getPageSize() + " , @Page = " + request.getPageNo() + " ; \n" +
|
" declare @TotalRowCount int ,@pageCount int; \n" +
|
" select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
|
" select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
|
" select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from t482105 a " + where + " \n" +
|
" SELECT * FROM ( \n" +
|
" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.create_time desc) AS NO,\n" +
|
fileds + " from t482105 a " + where +
|
" order by a.create_time desc \n" +
|
" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo";
|
List<T482105Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482105Entity.class));
|
list.stream().forEach(x -> {
|
//处理图片，附件，生成调用url及相关信息
|
FileStructEntity fileStructEntity = new FileStructEntity();
|
fileStructEntity.setDbid(request.getDbid());
|
fileStructEntity.setFormid(request.getFormid());
|
fileStructEntity.setFileStr(x.getAttachList());
|
x.setFiles(Utils.getFileEntities(fileStructEntity));
|
});
|
Response response = new Response();
|
response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
|
response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
|
response.setData(list);
|
return response;
|
}
|
|
@Override
|
public void followUpDel(Integer id) {
|
this.doBaseExecute("delete from t482105 where id=" + id);
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void scheduleDel(Integer id) {
|
this.doBaseExecute("delete from t482113 where refid=" + id + " \n delete from t482112 where id=" + id);
|
}
|
|
@Override
|
public void scheduleAttention(T482112Entity entity) {
|
this.doBaseExecute(" update t482112 set pinFlag=" + entity.getPinFlag() + " where id=" + entity.getId());
|
}
|
|
@Override
|
public void scheduleCompleted(Integer id) {
|
this.doBaseExecute(" update t482112 set completeFlag=1 where id=" + id);
|
}
|
|
@Override
|
public List<T482117Entity> personalList(String userCode, String groupid) {
|
String where = " isnull(groupId,'')=" + GridUtils.prossSqlParm(groupid);
|
if (StringUtils.equalsIgnoreCase(groupid, "个人分组")) {
|
where += " and userCode=" + GridUtils.prossSqlParm(userCode);
|
}
|
return this.jdbcTemplate.query("select quickid,content,companyid ,companyName ,userCode,userName,groupid from t482117 where " + where, new BeanPropertyRowMapper<>(T482117Entity.class));
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void personalSave(List<T482117Entity> list, String userCode) {
|
String sql = "declare @content nvarchar(150),@companyid varchar(50),@companyName nvarchar(150),@userCode varchar(50),@userName nvarchar(50),@groupid varchar(50)='个人分组'\n" +
|
"delete from t482117 where userCode=" + GridUtils.prossSqlParm(userCode) + "\n";
|
for (T482117Entity entity : list) {
|
sql += " select @content=" + GridUtils.prossSqlParm(entity.getContent()) + ",@companyid=" + GridUtils.prossSqlParm(entity.getCompanyId()) + ",@companyName =" + GridUtils.prossSqlParm(entity.getCompanyName()) + ",@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + ",@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + "\n" +
|
"insert into t482117(content,companyid,companyName,userCode,userName,groupid) values(@content,@companyid,@companyName,@userCode,@userName,@groupid)\n";
|
}
|
this.doBaseExecute(sql);
|
}
|
|
@Override
|
public Response auditList(String userCode, AuditRequestEntity request) {
|
String where = " where formid=" + request.getFormid() + " and docCode=" + GridUtils.prossSqlParm(request.getDocCode());
|
String fileds = " userCode,userName,oldValue,newValue,fieldName,auditDateTime,@TotalRowCount as totalRowCount ,@pageCount as pageCount ";
|
|
String sql = "set nocount on ; \n" +
|
" declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" +
|
" select @Limit = " + request.getPageSize() + " , @Page = " + request.getPageNo() + " ; \n" +
|
" declare @TotalRowCount int ,@pageCount int; \n" +
|
" select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
|
" select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
|
" select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from _sysAudit a " + where + " \n" +
|
" SELECT * FROM ( \n" +
|
" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.auditDateTime desc) AS NO,\n" +
|
fileds + " from _sysAudit a " + where +
|
" order by a.auditDateTime desc \n" +
|
" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo";
|
List<AuditEntity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AuditEntity.class));
|
Map<String, List<Map>> maps = new LinkedHashMap<>();
|
List<AuditEntity> collect = list.stream().sorted(Comparator.comparing(AuditEntity::getAuditDateTime).reversed()).distinct().collect(Collectors.toList());
|
collect.stream().forEach(x -> {
|
//把相同时间的放在同一组输出
|
List<Map> auditEntities = new ArrayList<>();
|
list.stream().forEach(y -> {
|
if (x.getAuditDateTime().equals(y.getAuditDateTime())) {
|
Map<String, String> map = new HashMap<>();
|
map.put("oldValue", y.getOldValue());
|
map.put("newValue", y.getNewValue());
|
map.put("fieldName", y.getFieldName());
|
map.put("userCode", y.getUserCode());
|
map.put("userName", y.getUserName());
|
auditEntities.add(map);
|
}
|
});
|
maps.put(DateUtil.formatDate(x.getAuditDateTime(), "yyyy-MM-dd HH:mm:ss"), auditEntities);
|
});
|
Response response = new Response();
|
response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
|
response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
|
response.setData(maps);
|
return response;
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void commentSave(T482104Entity entity) {
|
if (StringUtils.isBlank(entity.getComment())) {
|
throw new ApplicationException("评论内容不能为空");
|
}
|
String sql = "declare " +
|
"@userCode varchar(50)\n" +
|
",@userName varchar(50)\n" +
|
",@refId int \n" +
|
",@id int \n" +
|
",@comment varchar(3000) \n" +
|
",@attachList varchar(300) \n" +
|
",@senders varchar(300) \n" +
|
",@createTime datetime\n" +
|
",@updateTime datetime\n" +
|
" select " +
|
"@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + " ,\n" +
|
"@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + " ,\n" +
|
"@refId=" + entity.getRefId() + ", \n" +
|
"@id=" + entity.getId() + ", \n" +
|
"@comment=" + GridUtils.prossSqlParm(entity.getComment()) + " ,\n" +
|
"@attachList=" + GridUtils.prossSqlParm(entity.getAttachList()) + " ,\n" +
|
"@senders=" + GridUtils.prossSqlParm(entity.getSenders()) + " ,\n" +
|
"@createTime=getdate(),\n" +
|
"@updateTime=getdate()\n" +
|
" if isnull(@id,'')=''\n" +
|
" begin\n" +
|
" insert into t482104(" +
|
" userCode" +
|
",userName \n" +
|
",refId \n" +
|
",comment \n" +
|
",attachList\n" +
|
",senders\n" +
|
",createTime\n" +
|
",updateTime)values(" +
|
" @userCode" +
|
",@userName \n" +
|
",@refId \n" +
|
",@comment\n" +
|
",@attachList\n" +
|
",@senders\n" +
|
",@createTime\n" +
|
",@updateTime)" +
|
"end\n" +
|
"else\n" +
|
"begin\n" +
|
" update t482104 set " +
|
" userCode=@userCode" +
|
",userName=@userName \n" +
|
",refId=@refId \n" +
|
",comment=@comment \n" +
|
",attachList=@attachList\n" +
|
",senders=@senders\n" +
|
",updateTime=@updateTime where id=@id\n" +
|
"end\n";
|
this.doBaseExecute(sql);
|
|
}
|
|
/**
|
* 日程周期的生成
|
* 有设置周期性，则需要根据周期性设置重复生成相关的日程
|
* 每天例如：start:2024-8-1,end:2024-8-4
|
* 指定结束时间(2024-8-3)，则生成从开始时间到结束时间的相同日程
|
* 2024-8-1,2024-8-2,2024-8-3
|
* 每周例如：start:2024-8-1,end:2024-8-24
|
* 指定结束时间(2024-8-20)，则会按周一次生成从开始时间到结束时间的相同日程
|
* 2024-8-1,2024-8-7,2024-8-14
|
* 每月例如：start:2024-8-1,end:2024-10-24
|
* 指定结束时间(2024-9-20)，则会按周一次生成从开始时间到结束时间的相同日程
|
* 2024-8-1,2024-9-1
|
*
|
* @param entity
|
* @return
|
*/
|
private List<T482112Entity> prossSchedule(T482112Entity entity) {
|
List<T482112Entity> list = new ArrayList<>();
|
if (entity.getType().equalsIgnoreCase("not")) {
|
//没设置则直接返回
|
list.add(entity);
|
} else {
|
if (StringUtils.isBlank(entity.getStartTime())) {
|
throw new ApplicationException("开始日期不能为空");
|
}
|
if (StringUtils.isBlank(entity.getEndTime())) {
|
throw new ApplicationException("结束日期不能为空");
|
}
|
if (StringUtils.isBlank(entity.getRepeatEnd())) {
|
throw new ApplicationException("周期性结束日期不能为空");
|
}
|
if (entity.getId() == null || entity.getId() == 0) {//新增
|
calRepeatDate(entity, list);
|
} else if (entity.getId() != null && entity.getId() != 0 && entity.getRepeatFlag() == 1) {//修改且选择了生成后续重复日程
|
//修改才执行
|
//表示需要以当前重复日程重复生成新的重复日程
|
calRepeatDate(entity, list);
|
} else {
|
list.add(entity);
|
}
|
}
|
return list;
|
}
|
|
private void calRepeatDate(T482112Entity entity, List<T482112Entity> list) {
|
|
//开始日期
|
String pattern = "yyyy-MM-dd HH:mm:ss";
|
Calendar calStart = Calendar.getInstance();
|
LocalDateTime start = null;
|
LocalDateTime end = null;
|
LocalDateTime repeatEnd = null;
|
try {
|
start = LocalDateTime.parse(entity.getStartTime(), DateTimeFormatter.ofPattern(pattern));
|
|
} catch (Exception e) {
|
//表示没有时间，需要增加00:00:00，以便不出错
|
start = LocalDateTime.parse(entity.getStartTime() + " 00:00:00", DateTimeFormatter.ofPattern(pattern));
|
}
|
try {
|
end = LocalDateTime.parse(entity.getEndTime(), DateTimeFormatter.ofPattern(pattern));
|
} catch (Exception e) {
|
end = LocalDateTime.parse(entity.getEndTime() + " 00:00:00", DateTimeFormatter.ofPattern(pattern));
|
}
|
try {
|
repeatEnd = LocalDateTime.parse(entity.getRepeatEnd(), DateTimeFormatter.ofPattern(pattern));
|
} catch (Exception e) {
|
repeatEnd = LocalDateTime.parse(entity.getRepeatEnd() + " 00:00:00", DateTimeFormatter.ofPattern(pattern));
|
}
|
calStart.setTime(Date.from(start.atZone(ZoneId.systemDefault()).toInstant()));
|
//结束日期取周期性设置
|
Calendar calEnd = Calendar.getInstance();
|
calEnd.setTime(Date.from(repeatEnd.atZone(ZoneId.systemDefault()).toInstant()));
|
if (entity.getType().equalsIgnoreCase("everyday")) {
|
//1，----每天
|
repeatByDay(entity, list, start, end, pattern, calStart,1);
|
}
|
if (entity.getType().equalsIgnoreCase("everyweek")) {
|
//2，----每周
|
repeatByWeek(entity, list, calStart, calEnd, start, end, pattern,1);
|
}
|
if (entity.getType().equalsIgnoreCase("everymonth")) {
|
//3，----每月
|
repeatByMonth(entity, list, calStart, calEnd, start, end, pattern,1);
|
}
|
if (entity.getType().equalsIgnoreCase("ext")) {
|
if(entity.getNum()==null||entity.getNum()==0){
|
throw new ApplicationException("自定义重复周期[num]不能为空");
|
}
|
//4，----自定义
|
if (entity.getType().equalsIgnoreCase("day")) {
|
//1，----每天
|
repeatByDay(entity, list, start, end, pattern, calStart,entity.getNum());
|
}
|
if (entity.getType().equalsIgnoreCase("week")) {
|
//2，----每周
|
repeatByWeek(entity, list, calStart, calEnd, start, end, pattern,entity.getNum());
|
}
|
if (entity.getType().equalsIgnoreCase("month")) {
|
//3，----每月
|
repeatByMonth(entity, list, calStart, calEnd, start, end, pattern,entity.getNum());
|
}
|
}
|
// String s=null;
|
// s.toLowerCase();
|
}
|
|
private static void repeatByDay(T482112Entity entity, List<T482112Entity> list, LocalDateTime start, LocalDateTime end, String pattern, Calendar calStart,int interval) {
|
long dayNumber = (ChronoUnit.DAYS.between(start, end)-1)/interval;
|
list.add(entity);
|
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
|
while (dayNumber > 0) {
|
calStart.add(Calendar.DAY_OF_MONTH, 1); //加一天
|
T482112Entity temp = new T482112Entity();
|
BeanUtils.copyProperties(entity, temp);
|
temp.setId(null);
|
String dateStr = sdf.format(calStart.getTime());
|
temp.setStartTime(dateStr);
|
//新结束日期=新的日期+相差天数
|
calStart.add(Calendar.DAY_OF_MONTH, (int) dayNumber);
|
dateStr = sdf.format(calStart.getTime());
|
temp.setEndTime(dateStr);
|
calStart.add(Calendar.DAY_OF_MONTH, -(int) dayNumber);//还原回来
|
list.add(temp);
|
dayNumber--;
|
}
|
}
|
|
private void repeatByWeek(T482112Entity entity, List<T482112Entity> list, Calendar calStart, Calendar calEnd, LocalDateTime start, LocalDateTime end, String pattern,int interval) {
|
int start_weekInYear = this.getWeekInYear(calStart.getTime());
|
int end_weekInYear = this.getWeekInYear(calEnd.getTime());
|
long weekNumber = (end_weekInYear-start_weekInYear)/interval;
|
long dayNumber = ChronoUnit.DAYS.between(start, end);
|
list.add(entity);
|
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
|
while (weekNumber > 0) {
|
calStart.add(Calendar.WEEK_OF_YEAR, 1); //加一周
|
T482112Entity temp = new T482112Entity();
|
BeanUtils.copyProperties(entity, temp);
|
temp.setId(null);
|
String dateStr = sdf.format(calStart.getTime());
|
temp.setStartTime(dateStr);
|
//新结束日期=新的日期+相差天数
|
calStart.add(Calendar.DAY_OF_MONTH, (int) dayNumber);
|
dateStr = sdf.format(calStart.getTime());
|
temp.setEndTime(dateStr);
|
calStart.add(Calendar.DAY_OF_MONTH, -(int) dayNumber);//还原回来
|
list.add(temp);
|
weekNumber--;
|
}
|
}
|
|
private static void repeatByMonth(T482112Entity entity, List<T482112Entity> list, Calendar calStart, Calendar calEnd, LocalDateTime start, LocalDateTime end, String pattern,int interval) {
|
int start_monthInYear = calStart.get(Calendar.MONTH);
|
int end_monthInYear = calEnd.get(Calendar.MONTH);
|
long monthNumber = (end_monthInYear-start_monthInYear)/interval;
|
long dayNumber = ChronoUnit.DAYS.between(start, end);
|
list.add(entity);
|
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
|
while (monthNumber > 0) {
|
calStart.add(Calendar.MONTH, 1); //加一个月
|
T482112Entity temp = new T482112Entity();
|
BeanUtils.copyProperties(entity, temp);
|
temp.setId(null);
|
String dateStr = sdf.format(calStart.getTime());
|
temp.setStartTime(dateStr);
|
//新结束日期=新的日期+相差天数
|
calStart.add(Calendar.DAY_OF_MONTH, (int) dayNumber);
|
dateStr = sdf.format(calStart.getTime());
|
temp.setEndTime(dateStr);
|
calStart.add(Calendar.DAY_OF_MONTH, -(int) dayNumber);//还原回来
|
list.add(temp);
|
monthNumber--;
|
}
|
}
|
|
//获取到所传日期在当年第几周
|
public int getWeekInYear(Date date){
|
Calendar calendar = Calendar.getInstance();
|
calendar.setFirstDayOfWeek(Calendar.MONDAY);
|
calendar.setTime(date);
|
return calendar.get(Calendar.WEEK_OF_YEAR);
|
}
|
/**
|
* 因为日历比较用的是time属性，这个值是创建时候生成，就算二个日期一样，也会不一样。所以这里判断年月日，时分秒
|
*
|
* @param start
|
* @param end
|
* @return
|
*/
|
private boolean checkCalendar(Calendar start, Calendar end) {
|
int dayNumber = DateUtil.daysBetween(end.getTime(), start.getTime());
|
if (dayNumber > 0) {
|
return true;
|
} else {
|
return false;
|
}
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void scheduleSave(T482112Entity t482112Entity) {
|
if (StringUtils.isBlank(t482112Entity.getTitle())) {
|
throw new ApplicationException("日程内容不能为空");
|
}
|
String sql = " declare @companyId varchar(50),\n" +
|
" @companyName nvarchar(100),\n" +
|
" @userCode varchar(30),\n" +
|
" @userName nvarchar(30),\n" +
|
" @id bigint,\n" +
|
" @refType varchar(20),\n" +
|
" @refCode varchar(50) ,\n" +
|
" @title nvarchar(100),\n" +
|
" @color varchar(10),\n" +
|
" @participant_id varchar(100),\n" +
|
" @start_time datetime,\n" +
|
" @end_time datetime,\n" +
|
" @remark nvarchar(230),\n" +
|
" @attach_list varchar(230),\n" +
|
" @image_list varchar(230),\n" +
|
" @full_day_flag bit,\n" +
|
" @create_time datetime,\n" +
|
" @update_time datetime,@last_inserted_id bigint\n" +
|
",@rule_type varchar(50)" +
|
",@rule_unit varchar(50)\n" +
|
",@rule_repeatStart varchar(50)\n" +
|
",@rule_repeatEnd varchar(50)\n" +
|
",@rule_num int \n" +
|
",@remind_refid int \n" +
|
",@remind_type varchar(50)\n" +
|
",@remind_time varchar(50)\n";
|
List<T482112Entity> list = this.prossSchedule(t482112Entity);
|
for (T482112Entity entity : list) {
|
sql += " select @companyId=" + GridUtils.prossSqlParm(entity.getCompanyId()) + " ,\n" +
|
"@companyName=" + GridUtils.prossSqlParm(entity.getCompanyName()) + " ,\n" +
|
"@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + " ,\n" +
|
"@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + " ,\n" +
|
"@id=" + entity.getId() + " ,\n" +
|
"@refType=0 ,\n" +
|
"@refCode=" + GridUtils.prossSqlParm(entity.getRefCode()) + " ,\n" +
|
"@title=" + GridUtils.prossSqlParm(entity.getTitle()) + " ,\n" +
|
"@color=" + GridUtils.prossSqlParm(entity.getColor()) + " ,\n" +
|
"@participant_id =" + GridUtils.prossSqlParm(entity.getParticipantId()) + " ,\n" +
|
"@start_time=" + GridUtils.prossSqlParm(entity.getStartTime()) + " ,\n" +
|
"@end_time=" + GridUtils.prossSqlParm(entity.getEndTime()) + " ,\n" +
|
"@remark=" + GridUtils.prossSqlParm(entity.getRemark()) + " ,\n" +
|
"@attach_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + " ,\n" +
|
"@image_list=" + GridUtils.prossSqlParm(entity.getImageList()) + " ,\n" +
|
"@full_day_flag=" + entity.getFullDayFlag() + " ,\n" +
|
"@rule_unit=" + GridUtils.prossSqlParm(entity.getUnit()) + " ,\n" +
|
"@rule_type=" + GridUtils.prossSqlParm(entity.getType()) + " ,\n" +
|
"@rule_repeatStart=" + GridUtils.prossSqlParm(entity.getRepeatStart()) + " ,\n" +
|
"@rule_repeatEnd=" + GridUtils.prossSqlParm(entity.getRepeatEnd()) + " ,\n" +
|
"@rule_num =" + entity.getNum() + " ,\n" +
|
"@create_time=getdate() ,\n" +
|
"@update_time=getdate() \n" +
|
"if isnull(@id,'')='' \n" +
|
" begin\n" +
|
" insert into t482112(" +
|
" companyId \n" +
|
",companyName \n" +
|
",userCode \n" +
|
",userName \n" +
|
",refType \n" +
|
",refCode \n" +
|
",title \n" +
|
",color \n" +
|
",participant_id\n" +
|
",start_time \n" +
|
",end_time \n" +
|
",remark \n" +
|
",attach_list \n" +
|
",image_list \n" +
|
",full_day_flag \n" +
|
",unit \n" +
|
",type \n" +
|
",repeat_Start \n" +
|
",repeat_End \n" +
|
",num " +
|
",create_time \n" +
|
",update_time \n)values(" +
|
" @companyId \n" +
|
",@companyName \n" +
|
",@userCode \n" +
|
",@userName \n" +
|
",@refType \n" +
|
",@refCode \n" +
|
",@title \n" +
|
",@color \n" +
|
",@participant_id\n" +
|
",@start_time \n" +
|
",@end_time \n" +
|
",@remark \n" +
|
",@attach_list \n" +
|
",@image_list \n" +
|
",@full_day_flag \n" +
|
",@rule_unit \n" +
|
",@rule_type \n" +
|
",@rule_repeatStart \n" +
|
",@rule_repeatEnd \n" +
|
",@rule_num " +
|
",@create_time \n" +
|
",@update_time )\n" +
|
" SELECT @last_inserted_id=IDENT_CURRENT('t482112')\n" +
|
"end\n" +
|
" else\n" +//修改
|
"begin\n" +
|
" update t482112 set \n" +
|
"title=@title \n" +
|
",color =@color \n" +
|
",participant_id=@participant_id\n" +
|
",start_time=@start_time \n" +
|
",end_time=@end_time \n" +
|
",remark=@remark \n" +
|
",attach_list=@attach_list \n" +
|
",image_list =@image_list \n" +
|
",unit=@rule_unit \n" +
|
",type=@rule_type \n" +
|
",num=@rule_num \n" +
|
",repeat_Start=@rule_repeatStart \n" +
|
",repeat_End=@rule_repeatEnd \n" +
|
",update_time=@update_time\n" +
|
",full_day_flag=@full_day_flag \n" +
|
" SELECT @last_inserted_id=@id\n" +
|
"end\n" +
|
AddRemindTimesSql(entity.getRemindTimes());
|
}
|
this.doBaseExecute(sql);
|
}
|
|
/**
|
* 生成周期性日程设置sql
|
*
|
* @param entity
|
* @return
|
*/
|
private String AddRemindTimesSql(List<T482113Entity> entity) {
|
//只有新增，所以要先全部删除
|
String sql = " delete from t482113 where refId=@last_inserted_id \n";
|
for (T482113Entity t482113 : entity) {
|
sql += "\nselect\n" +
|
" @remind_type=" + GridUtils.prossSqlParm(t482113.getType()) + "\n" +
|
",@remind_time=" + GridUtils.prossSqlParm(t482113.getTime()) + "\n" +
|
",@remind_refid=@last_inserted_id\n" +
|
" insert into t482113(" +
|
" refId, \n" +
|
"time, \n" +
|
"type \n" +
|
")values(" +
|
"@remind_refid, \n" +
|
"@remind_time, \n" +
|
"@remind_type \n" +
|
")\n";
|
}
|
return sql;
|
}
|
|
@Override
|
public Response scheduleList(ScheduleRequestEntity request) {
|
String where = "";
|
if (request.getSearchTxt() == null) {
|
throw new ApplicationException("查询参数不能都为空");
|
} else {
|
String refCode = request.getSearchTxt().get("refCode");
|
if (refCode == null) {
|
throw new ApplicationException("refCode参数不能都为空");
|
}
|
where = " where refCode=" + GridUtils.prossSqlParm(refCode);
|
String completeFlag = request.getSearchTxt().get("completeFlag");
|
if (completeFlag == null) {
|
where += " and isnull(completeFlag,0)=0";
|
} else {
|
where += " and isnull(completeFlag,0)=" + completeFlag;
|
}
|
}
|
String fileds = "refType \n" +
|
",id,refCode \n" +
|
",title \n" +
|
",color \n" +
|
",participant_id\n" +
|
",start_time \n" +
|
",end_time \n" +
|
",remark \n" +
|
",attach_list \n" +
|
",image_list \n" +
|
",full_day_flag \n" +
|
",unit \n" +
|
",type \n" +
|
",repeat_Start \n" +
|
",repeat_End \n" +
|
",num " +
|
",create_time \n" +
|
",update_time \n" +
|
",@TotalRowCount as totalRowCount ,@pageCount as pageCount ";
|
|
String sql = "set nocount on ; \n" +
|
" declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" +
|
" select @Limit = " + request.getPageSize() + " , @Page = " + request.getPageNo() + " ; \n" +
|
" declare @TotalRowCount int ,@pageCount int; \n" +
|
" select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
|
" select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
|
" select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from t482112 a " + where + " \n" +
|
" SELECT * FROM ( \n" +
|
" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.update_time desc) AS NO,\n" +
|
fileds + " from t482112 a " + where +
|
" order by a.update_time desc \n" +
|
" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo";
|
List<T482112Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482112Entity.class));
|
|
list.stream().forEach(x -> {
|
if (StringUtils.isNotBlank(x.getAttachList())) {
|
FileStructEntity fileStructEntity = new FileStructEntity();
|
fileStructEntity.setDbid(request.getDbid());
|
fileStructEntity.setFormid(request.getFormId() + "");
|
fileStructEntity.setFileStr(x.getAttachList());
|
x.setFiles(Utils.getFileEntities(fileStructEntity));
|
}
|
});
|
Response response = new Response();
|
response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
|
response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
|
response.setData(list);
|
return response;
|
}
|
|
@Override
|
public Response commentList(CommentRequestEntity request) {
|
String where = "";
|
if (request.getSearchTxt() == null) {
|
throw new ApplicationException("查询参数不能都为空");
|
} else {
|
String refId = request.getSearchTxt().get("refId");
|
if (refId == null) {
|
throw new ApplicationException("refId参数不能都为空");
|
}
|
where = " where refId=" + GridUtils.prossSqlParm(refId);
|
}
|
String fileds = "userCode, \n" +
|
"userName, \n" +
|
" refId, \n" +
|
" id, \n" +
|
"comment, \n" +
|
"attachList,\n" +
|
"convert(varchar(19), createTime,120) as createTime,senders,\n" +
|
"convert(varchar(19), updateTime,120) as updateTime" +
|
",case when usercode=@usercode then 1 else 0 end as enableFlag,@TotalRowCount as totalRowCount ,@pageCount as pageCount ";
|
|
String sql = "set nocount on ; \n" +
|
" declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ,@usercode varchar(50)=" + GridUtils.prossSqlParm(request.getUserCode()) + " \n" +
|
" select @Limit = " + request.getPageSize() + " , @Page = " + request.getPageNo() + " ; \n" +
|
" declare @TotalRowCount int ,@pageCount int; \n" +
|
" select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
|
" select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
|
" select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from t482104 a " + where + " \n" +
|
" SELECT * FROM ( \n" +
|
" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.updateTime desc) AS NO,\n" +
|
fileds + " from t482104 a " + where +
|
" order by a.updateTime desc \n" +
|
" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo";
|
List<T482104Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482104Entity.class));
|
|
list.stream().forEach(x -> {
|
if (StringUtils.isNotBlank(x.getAttachList())) {
|
FileStructEntity fileStructEntity = new FileStructEntity();
|
fileStructEntity.setDbid(request.getDbid());
|
fileStructEntity.setFormid(request.getFormId() + "");
|
fileStructEntity.setFileStr(x.getAttachList());
|
x.setFiles(Utils.getFileEntities(fileStructEntity));
|
}
|
});
|
Response response = new Response();
|
response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
|
response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
|
response.setData(list);
|
return response;
|
}
|
|
@Override
|
public void commentDel(Integer id) {
|
this.doBaseExecute("delete from t482104 where id=" + id);
|
}
|
}
|
