eCoWorksV3.git - Gitblit

Merge remote-tracking branch 'origin/dev' into dev

xinyb

2024-09-19 18ffbca9acaccd5099a7a63652f52210f59a7e40

提交 \| 用户 \| age
7204e3	1	package com.yc.crm.base.service;
F	2
	3	import com.yc.action.grid.GridUtils;
	4	import com.yc.crm.base.entity.*;
	5	import com.yc.crm.base.util.Utils;
	6	import com.yc.crm.clues.entity.T480107Entity;
	7	import com.yc.crm.clues.entity.T480114Entity;
035491	8	import com.yc.exception.ApplicationException;
7204e3	9	import com.yc.service.BaseService;
035491	10	import com.yc.utils.DateUtil;
d32c21	11	import com.yc.utils.SessionKey;
05aec6	12	import org.apache.commons.lang3.RandomStringUtils;
7204e3	13	import org.apache.commons.lang3.StringUtils;
035491	14	import org.springframework.beans.BeanUtils;
7204e3	15	import org.springframework.jdbc.core.BeanPropertyRowMapper;
F	16	import org.springframework.stereotype.Service;
035491	17	import org.springframework.transaction.annotation.Transactional;
7204e3	18
F	19	import javax.servlet.http.HttpSession;
035491	20	import java.text.SimpleDateFormat;
F	21	import java.time.LocalDateTime;
	22	import java.time.ZoneId;
	23	import java.time.format.DateTimeFormatter;
	24	import java.time.temporal.ChronoUnit;
	25	import java.util.*;
7204e3	26	import java.util.stream.Collectors;
F	27
	28	@Service
	29	public class CrmServiceImpl extends BaseService implements CrmServiceIfc {
	30	@Override
d32c21	31	public Map getBaseInfo(HttpSession session) {
F	32	String userCode=session.getAttribute(SessionKey.USERCODE).toString();
7204e3	33	HashMap map = new HashMap();
d32c21	34	map.put("isAdmin", ("SuperUser".equalsIgnoreCase(session.getAttribute(SessionKey.USERTYPE) + "") \|\| "DeveloperUser".equalsIgnoreCase(session.getAttribute(SessionKey.USERTYPE) + "")) ? 1 : 0);
7204e3	35	//----系统参数
035491	36	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" +
7204e3	37	"order by dictid desc,sequence\n ", new BeanPropertyRowMapper<>(SysdictEntity.class));
035491	38	map.put("bizType", list.stream().filter(x -> x.getDictid() == -48013000).collect(Collectors.toList()));
F	39	map.put("intentionLevel", list.stream().filter(x -> x.getDictid() == -48013001).collect(Collectors.toList()));
	40	map.put("annualProcurement", list.stream().filter(x -> x.getDictid() == -48013002).collect(Collectors.toList()));
	41	map.put("timezone", list.stream().filter(x -> x.getDictid() == -48013003).collect(Collectors.toList()));
	42	map.put("scaleId", list.stream().filter(x -> x.getDictid() == -48013004).collect(Collectors.toList()));
	43	map.put("inquiryOrigin", list.stream().filter(x -> x.getDictid() == -48013005).collect(Collectors.toList()));
	44	map.put("inquiryCountry", list.stream().filter(x -> x.getDictid() == -48013006).collect(Collectors.toList()));
	45	map.put("mediaList", list.stream().filter(x -> x.getDictid() == -48013007).collect(Collectors.toList()));
	46	map.put("telAreaCode", list.stream().filter(x -> x.getDictid() == -48013008).collect(Collectors.toList()));
	47	map.put("postGrade", list.stream().filter(x -> x.getDictid() == -48013009).collect(Collectors.toList()));
	48	map.put("systemFlag", list.stream().filter(x -> x.getDictid() == -48013010).collect(Collectors.toList()));
	49	map.put("followUpType", list.stream().filter(x -> x.getDictid() == -48013011).collect(Collectors.toList()));
	50	map.put("cluesStatusFlag", list.stream().filter(x -> x.getDictid() == -48013012).collect(Collectors.toList()));
	51	map.put("failStatus", list.stream().filter(x -> x.getDictid() == -48013013).collect(Collectors.toList()));
	52	map.put("quickTxtList", list.stream().filter(x -> x.getDictid() == -48013014).collect(Collectors.toList()));
	53	map.put("remindTimes", list.stream().filter(x -> x.getDictid() == -48013015).collect(Collectors.toList()));
7204e3	54	//---线索来源
F	55	List<T480107Entity> t480107Entities = this.jdbcTemplate.query("select leadSourceCode\n" +
	56	",leadSourceName\n" +
	57	",memo \n" +
	58	",rowid \n" +
	59	",treecontrol \n" +
	60	",isnull(parentrowid,'') as parentrowid from t480107", new BeanPropertyRowMapper<>(T480107Entity.class));
035491	61	List<T480107Entity> treeEntiries = Utils.convertToTree(t480107Entities, T480107Entity::getParentrowid, T480107Entity::getRowid);
F	62	map.put("originList", treeEntiries);
	63	//---国家
	64	List<T480114Entity> t480114Entities = this.jdbcTemplate.query("select " +
	65	// "areaid as regionsCode\n" +
	66	"regionsCode\n" +
	67	//",areaname as regionsName\n" +
7204e3	68	",regionsName\n" +
035491	69	"--,memo \n" +
F	70	",rowid \n" +
	71	",treecontrol \n" +//t480114,t110202
	72	",isnull(parentrowid,'') as parentrowid from t480114", new BeanPropertyRowMapper<>(T480114Entity.class));
	73	List<T480114Entity> treeEntiries114 = Utils.convertToTree(t480114Entities, T480114Entity::getParentrowid, T480114Entity::getRowid);
	74	map.put("country", treeEntiries114);
	75	//---省市
	76	List<T110202Entity> t110202Entities = this.jdbcTemplate.query("select " +
	77	"areaid\n" +
	78	",areaname\n" +
	79	"--,memo \n" +
	80	",rowid \n" +
	81	",treecontrol \n" +//t480114,t110202
	82	",isnull(parentrowid,'') as parentrowid from t110202", new BeanPropertyRowMapper<>(T110202Entity.class));
	83	List<T110202Entity> treeEntiries110202 = Utils.convertToTree(t110202Entities, T110202Entity::getParentrowid, T110202Entity::getRowid);
	84	map.put("province", treeEntiries110202);
	85	//---主营产品
	86	List<T480116Entity> t480116Entities = this.jdbcTemplate.query("select categoryId\n" +
	87	",categoryName\n" +
7204e3	88	",memo \n" +
F	89	",rowid \n" +
035491	90	",treecontrol \n" +//t480114
F	91	",isnull(parentrowid,'') as parentrowid from t480116", new BeanPropertyRowMapper<>(T480116Entity.class));
	92	List<T480116Entity> treeEntiries116 = Utils.convertToTree(t480116Entities, T480116Entity::getParentrowid, T480116Entity::getRowid);
	93	map.put("categoryIds", treeEntiries116);
	94
7204e3	95	//---标签
F	96	List<T482115Entity> t482115Entities = this.jdbcTemplate.query("select tag_Id, \n" +
	97	"tag_Name, \n" +
	98	" sort_Id, \n" +
	99	" system_Flag,\n" +
	100	"tag_Color, \n" +
035491	101	" 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));
F	102	map.put("tags", t482115Entities);
7204e3	103	return map;
F	104	}
	105
	106	@Override
d32c21	107	public Object getMailUserInfo(String key) {
F	108	//--取用户
	109	String sql = " select ccCode,ccName,userCode,userName,email from _sysuser where email like '%" + key + "%' or userName like '%" + key + "%' \n" +
	110	"union all\n" +
	111	//--取联系人(线索，客户)
	112	" select '' as ccCode,'' as ccName,'' as userCode,name as userName,email from t482103 where email like '%" + key + "%' or name like '%" + key + "%'\n";
	113
	114	List<UserEntity> query = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserEntity.class));
	115	return query;
	116	}
	117
	118	@Override
7204e3	119	public Object getUserInfo(HttpSession session, Page page) {
035491	120	// String ccCode = (String) session.getAttribute(SessionKey.CCCODE);
F	121	String where = "";
	122	if (page != null && StringUtils.isNotBlank(page.getKey())) {
7204e3	123
d32c21	124	where = " where ccCode='"+page.getKey()+"' or email like '%" + page.getKey() + "%' or userName like '%" + page.getKey() + "%'";
7204e3	125	}
035491	126	String sql = " select ccCode,ccName,userCode,userName,email from _sysuser " + where + "order by ccCode asc";
7204e3	127	List<UserEntity> query = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserEntity.class));
035491	128	if (page != null && StringUtils.isNotBlank(page.getKey())) {
F	129	//直接输出符合条件的用户列表，没树形结构
	130	return query;
	131	} else {
	132	// 没查询email条件则输出树形结构
	133	List<DepartmentsEntity> departments = query.stream().distinct().map(x -> {
	134	DepartmentsEntity departmentsEntity = new DepartmentsEntity();
	135	departmentsEntity.setCcCode(x.getCcCode());
	136	departmentsEntity.setCcName(x.getCcName());
	137	return departmentsEntity;
	138	}).collect(Collectors.toList());
	139	departments.stream().forEach(x -> {
	140	List<UserEntity> collect = query.stream().filter(x1 -> x1.getCcCode().equalsIgnoreCase(x.getCcCode())).collect(Collectors.toList());
	141	x.setSubList(collect);
	142	});
	143	return departments;
	144	}
	145	}
	146
	147	@Transactional(rollbackFor = Exception.class)
	148	@Override
	149	public void followUpSave(T482105Entity entity) {
	150	if (StringUtils.isBlank(entity.getContent())) {
	151	throw new ApplicationException("跟进内容不能为空");
	152	}
	153	String sql = "declare " +
	154	" @companyId varchar(50), \n" +
	155	" @companyName nvarchar(100),\n" +
	156	" @userCode varchar(30), \n" +
	157	" @userName nvarchar(30),\n" +
	158	" @id bigint, \n" +
	159	" @refCode varchar(50),\n" +
05aec6	160	" @refName nvarchar(150),\n" +
035491	161	" @enable_flag bit, \n" +
F	162	" @delete_flag bit, \n" +
	163	" @inquiry_id int, \n" +
	164	" @comment_count int, \n" +
	165	" @content nvarchar(4000),\n" +
	166	" @plain_content nvarchar(4000),\n" +
	167	" @latitude varchar(50), \n" +
	168	" @longitude varchar(50), \n" +
	169	" @address nvarchar(200),\n" +
	170	" @attach_list varchar(150),\n" +
05aec6	171	" @image_list varchar(150),\n" +
035491	172	" @followUp_type nvarchar(20),\n" +
F	173	" @followUser varchar(20),\n" +
	174	" @followUp_next_time datetime, \n" +
	175	" @create_time datetime, \n" +
	176	" @update_time datetime\n" +
	177	" select @companyId=" + GridUtils.prossSqlParm(entity.getCompanyId()) + "\n" +
	178	",@companyName=" + GridUtils.prossSqlParm(entity.getCompanyName()) + "\n" +
	179	",@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + "\n" +
	180	",@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + "\n" +
	181	",@id=" + entity.getId() + "\n" +
	182	",@refCode=" + GridUtils.prossSqlParm(entity.getRefCode()) + "\n" +
05aec6	183	",@refName=" + GridUtils.prossSqlParm(entity.getRefName()) + "\n" +
035491	184	",@inquiry_id=" + entity.getInquiryId() + "\n" +
F	185	",@content=" + GridUtils.prossSqlParm(entity.getContent()) + "\n" +
	186	",@plain_content=" + GridUtils.prossSqlParm(entity.getPlainContent()) + "\n" +
	187	",@latitude=" + GridUtils.prossSqlParm(entity.getLatitude()) + "\n" +
	188	",@longitude=" + GridUtils.prossSqlParm(entity.getLongitude()) + "\n" +
	189	",@address=" + GridUtils.prossSqlParm(entity.getAddress()) + "\n" +
	190	",@attach_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + "\n" +
05aec6	191	",@image_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + "\n" +
035491	192	",@followUp_type=" + GridUtils.prossSqlParm(entity.getFollowUpType()) + "\n" +
F	193	",@followUser=" + GridUtils.prossSqlParm(entity.getFollowUser()) + "\n" +
	194	",@followUp_next_time=" + GridUtils.prossSqlParm(entity.getFollowUpNextTime()) + "\n" +
	195	(StringUtils.isNotBlank(entity.getCreateTime()) ? ",@create_time=" + GridUtils.prossSqlParm(entity.getCreateTime()) + "\n" : ",@create_time=getdate() \n") +
	196	",@update_time=getdate() \n" +
	197	" if isnull(@id,'')='' \n" +//新增
	198	" begin \n" +
	199	" insert into t482105(" +
	200	"companyId \n" +
	201	",companyName \n" +
	202	",userCode \n" +
	203	",userName \n" +
	204	",refCode \n" +
05aec6	205	",refName \n" +
035491	206	",inquiry_id \n" +
F	207	",content \n" +
	208	",plain_content \n" +
	209	",latitude \n" +
	210	",longitude \n" +
	211	",address \n" +
	212	",attach_list \n" +
05aec6	213	",image_list \n" +
035491	214	",followUp_type \n" +
F	215	",followUser \n" +
	216	",followUp_next_time \n" +
	217	",create_time \n" +
	218	",update_time " +
	219	")values(" +
	220	"@companyId \n" +
	221	",@companyName \n" +
	222	",@userCode \n" +
	223	",@userName \n" +
	224	",@refCode \n" +
05aec6	225	",@refName \n" +
035491	226	",@inquiry_id \n" +
F	227	",@content \n" +
	228	",@plain_content \n" +
	229	",@latitude \n" +
	230	",@longitude \n" +
	231	",@address \n" +
	232	",@attach_list \n" +
05aec6	233	",@image_list \n" +
035491	234	",@followUp_type \n" +
F	235	",@followUser \n" +
	236	",@followUp_next_time \n" +
	237	",@create_time \n" +
	238	",@update_time )\n" +
	239	"end \n" +
	240	" else \n" +//修改
	241	"begin\n" +
	242	" update a set \n" +
	243	" content=" + GridUtils.prossSqlParm(entity.getContent()) + "\n" +
	244	",plain_content=" + GridUtils.prossSqlParm(entity.getPlainContent()) + "\n" +
	245	",latitude =" + GridUtils.prossSqlParm(entity.getLatitude()) + "\n" +
	246	",longitude=" + GridUtils.prossSqlParm(entity.getLongitude()) + "\n" +
	247	",address=" + GridUtils.prossSqlParm(entity.getAddress()) + "\n" +
	248	",attach_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + "\n" +
05aec6	249	",image_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + "\n" +
035491	250	",followUp_type=" + GridUtils.prossSqlParm(entity.getFollowUpType()) + "\n" +
F	251	",followUser=" + GridUtils.prossSqlParm(entity.getFollowUser()) + "\n" +
	252	",followUp_next_time=" + GridUtils.prossSqlParm(entity.getFollowUpNextTime()) + "\n" +
	253	",update_time=@update_time from t482105 a where id=@id \n " +
	254	"end\n";
	255	this.doBaseExecute(sql);
	256	}
	257
	258	@Override
	259	public Response followUpList(FollowUpRequestEntity request) {
	260	String where = "";
	261	if (request.getSearchTxt() != null && request.getSearchTxt().size() > 0) {
	262	//拼接查询条件
	263	where = " where ( 1=1 ";
	264	Set<Map.Entry<String, String>> entries = request.getSearchTxt().entrySet();
	265	for (Map.Entry<String, String> entry : entries) {
	266	if (entry.getKey().equalsIgnoreCase("refCode")) {
	267	where += " and refCode=" + GridUtils.prossSqlParm(entry.getValue());
	268	}
	269	if (entry.getKey().equalsIgnoreCase("followUpType")) {
	270	where += " and followUp_type=" + GridUtils.prossSqlParm(entry.getValue());
	271	}
	272	if (entry.getKey().equalsIgnoreCase("formid")) {
	273	request.setFormid(entry.getValue());
	274	}
	275	}
	276	where += ")";
	277	}
	278	String fileds =
	279	" companyId \n" +
	280	",companyName \n" +
	281	",userCode \n" +
	282	",userName \n" +
05aec6	283	",id,refCode,refName \n" +
035491	284	",inquiry_id \n" +
F	285	",comment_count \n" +
	286	",content \n" +
	287	",plain_content \n" +
	288	",latitude \n" +
	289	",longitude \n" +
	290	",address \n" +
	291	",attach_list \n" +
	292	",followUp_type \n" +
	293	",followUser \n" +
05aec6	294	",followUp_next_time as followUpNextTime\n" +
035491	295	",create_time \n" +
F	296	",case when convert(varchar(19),dateadd(hh,1,update_time),120)>convert(varchar(19),GETDATE(),120) then 1 else 0 end as deleteFlag" +//1小时内可以删除跟进
	297	",update_time ,@TotalRowCount as totalRowCount ,@pageCount as pageCount ";
	298
	299	String sql = "set nocount on ; \n" +
	300	" declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" +
	301	" select @Limit = " + request.getPageSize() + " , @Page = " + request.getPageNo() + " ; \n" +
	302	" declare @TotalRowCount int ,@pageCount int; \n" +
	303	" select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
	304	" select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
	305	" select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from t482105 a " + where + " \n" +
	306	" SELECT * FROM ( \n" +
	307	" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.create_time desc) AS NO,\n" +
	308	fileds + " from t482105 a " + where +
	309	" order by a.create_time desc \n" +
	310	" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo";
	311	List<T482105Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482105Entity.class));
	312	list.stream().forEach(x -> {
	313	//处理图片，附件，生成调用url及相关信息
05aec6	314	if (StringUtils.isNotBlank(x.getAttachList())) {
F	315	FileStructEntity fileStructEntity = new FileStructEntity();
	316	fileStructEntity.setDbid(request.getDbid());
	317	fileStructEntity.setFormid(request.getFormid());
	318	fileStructEntity.setFileStr(x.getAttachList());
	319	x.setFiles(Utils.getFileEntities(fileStructEntity));
	320	}
	321	if (StringUtils.isNotBlank(x.getImageList())) {
	322	FileStructEntity fileStructEntity = new FileStructEntity();
	323	fileStructEntity.setDbid(request.getDbid());
	324	fileStructEntity.setFormid(request.getFormid());
	325	fileStructEntity.setFileStr(x.getImageList());
	326	x.setFiles(Utils.getFileEntities(fileStructEntity));
	327	}
035491	328	});
F	329	Response response = new Response();
	330	response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
	331	response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
	332	response.setData(list);
	333	return response;
	334	}
	335
	336	@Override
	337	public void followUpDel(Integer id) {
	338	this.doBaseExecute("delete from t482105 where id=" + id);
	339	}
	340
	341	@Transactional(rollbackFor = Exception.class)
	342	@Override
05aec6	343	public void scheduleDel(T482112Entity entity) {
F	344	if(entity.getSamedeleteFlag()==0\|\|entity.getSamedeleteFlag()==null){
	345	//只删除本身
	346	this.doBaseExecute("delete from t482113 where refid=" + entity.getId() + " \n delete from t482112 where id=" + entity.getId());
	347	}else{
	348	if(StringUtils.isBlank(entity.getSamedeleteId())){
	349	throw new ApplicationException("指定samedeleteFlag=1时samedeleteId参数不能为空");
	350	}
	351	String[] deleteId=entity.getSamedeleteId().split(";");
	352	String orStr=" or samedeleteId like '%"+deleteId[deleteId.length-1]+"%'";
	353	this.doBaseExecute("delete from t482113 where refid=" + entity.getId() + orStr+" \n delete from t482112 where id=" + entity.getId()+orStr);
	354	}
035491	355	}
F	356
	357	@Override
	358	public void scheduleAttention(T482112Entity entity) {
	359	this.doBaseExecute(" update t482112 set pinFlag=" + entity.getPinFlag() + " where id=" + entity.getId());
	360	}
	361
	362	@Override
	363	public void scheduleCompleted(Integer id) {
	364	this.doBaseExecute(" update t482112 set completeFlag=1 where id=" + id);
	365	}
	366
	367	@Override
	368	public List<T482117Entity> personalList(String userCode, String groupid) {
	369	String where = " isnull(groupId,'')=" + GridUtils.prossSqlParm(groupid);
	370	if (StringUtils.equalsIgnoreCase(groupid, "个人分组")) {
	371	where += " and userCode=" + GridUtils.prossSqlParm(userCode);
	372	}
	373	return this.jdbcTemplate.query("select quickid,content,companyid ,companyName ,userCode,userName,groupid from t482117 where " + where, new BeanPropertyRowMapper<>(T482117Entity.class));
	374	}
	375
	376	@Transactional(rollbackFor = Exception.class)
	377	@Override
	378	public void personalSave(List<T482117Entity> list, String userCode) {
	379	String sql = "declare @content nvarchar(150),@companyid varchar(50),@companyName nvarchar(150),@userCode varchar(50),@userName nvarchar(50),@groupid varchar(50)='个人分组'\n" +
	380	"delete from t482117 where userCode=" + GridUtils.prossSqlParm(userCode) + "\n";
	381	for (T482117Entity entity : list) {
	382	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" +
	383	"insert into t482117(content,companyid,companyName,userCode,userName,groupid) values(@content,@companyid,@companyName,@userCode,@userName,@groupid)\n";
	384	}
	385	this.doBaseExecute(sql);
	386	}
	387
	388	@Override
	389	public Response auditList(String userCode, AuditRequestEntity request) {
	390	String where = " where formid=" + request.getFormid() + " and docCode=" + GridUtils.prossSqlParm(request.getDocCode());
	391	String fileds = " userCode,userName,oldValue,newValue,fieldName,auditDateTime,@TotalRowCount as totalRowCount ,@pageCount as pageCount ";
	392
	393	String sql = "set nocount on ; \n" +
	394	" declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" +
	395	" select @Limit = " + request.getPageSize() + " , @Page = " + request.getPageNo() + " ; \n" +
	396	" declare @TotalRowCount int ,@pageCount int; \n" +
	397	" select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
	398	" select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
	399	" select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from _sysAudit a " + where + " \n" +
	400	" SELECT * FROM ( \n" +
	401	" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.auditDateTime desc) AS NO,\n" +
	402	fileds + " from _sysAudit a " + where +
	403	" order by a.auditDateTime desc \n" +
	404	" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo";
	405	List<AuditEntity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AuditEntity.class));
	406	Map<String, List<Map>> maps = new LinkedHashMap<>();
	407	List<AuditEntity> collect = list.stream().sorted(Comparator.comparing(AuditEntity::getAuditDateTime).reversed()).distinct().collect(Collectors.toList());
	408	collect.stream().forEach(x -> {
	409	//把相同时间的放在同一组输出
	410	List<Map> auditEntities = new ArrayList<>();
	411	list.stream().forEach(y -> {
	412	if (x.getAuditDateTime().equals(y.getAuditDateTime())) {
	413	Map<String, String> map = new HashMap<>();
	414	map.put("oldValue", y.getOldValue());
	415	map.put("newValue", y.getNewValue());
	416	map.put("fieldName", y.getFieldName());
	417	map.put("userCode", y.getUserCode());
	418	map.put("userName", y.getUserName());
	419	auditEntities.add(map);
	420	}
	421	});
	422	maps.put(DateUtil.formatDate(x.getAuditDateTime(), "yyyy-MM-dd HH:mm:ss"), auditEntities);
	423	});
	424	Response response = new Response();
	425	response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
	426	response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
	427	response.setData(maps);
	428	return response;
	429	}
	430
	431	@Transactional(rollbackFor = Exception.class)
	432	@Override
	433	public void commentSave(T482104Entity entity) {
	434	if (StringUtils.isBlank(entity.getComment())) {
	435	throw new ApplicationException("评论内容不能为空");
	436	}
	437	String sql = "declare " +
	438	"@userCode varchar(50)\n" +
	439	",@userName varchar(50)\n" +
	440	",@refId int \n" +
	441	",@id int \n" +
	442	",@comment varchar(3000) \n" +
	443	",@attachList varchar(300) \n" +
d32c21	444	",@imageList varchar(300) \n" +
035491	445	",@senders varchar(300) \n" +
F	446	",@createTime datetime\n" +
	447	",@updateTime datetime\n" +
	448	" select " +
	449	"@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + " ,\n" +
	450	"@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + " ,\n" +
	451	"@refId=" + entity.getRefId() + ", \n" +
	452	"@id=" + entity.getId() + ", \n" +
	453	"@comment=" + GridUtils.prossSqlParm(entity.getComment()) + " ,\n" +
	454	"@attachList=" + GridUtils.prossSqlParm(entity.getAttachList()) + " ,\n" +
d32c21	455	"@imageList=" + GridUtils.prossSqlParm(entity.getImageList()) + " ,\n" +
035491	456	"@senders=" + GridUtils.prossSqlParm(entity.getSenders()) + " ,\n" +
F	457	"@createTime=getdate(),\n" +
	458	"@updateTime=getdate()\n" +
	459	" if isnull(@id,'')=''\n" +
	460	" begin\n" +
	461	" insert into t482104(" +
	462	" userCode" +
	463	",userName \n" +
	464	",refId \n" +
	465	",comment \n" +
	466	",attachList\n" +
d32c21	467	",imageList\n" +
035491	468	",senders\n" +
F	469	",createTime\n" +
	470	",updateTime)values(" +
	471	" @userCode" +
	472	",@userName \n" +
	473	",@refId \n" +
	474	",@comment\n" +
	475	",@attachList\n" +
d32c21	476	",@imageList\n" +
035491	477	",@senders\n" +
F	478	",@createTime\n" +
	479	",@updateTime)" +
	480	"end\n" +
	481	"else\n" +
	482	"begin\n" +
	483	" update t482104 set " +
	484	" userCode=@userCode" +
	485	",userName=@userName \n" +
	486	",refId=@refId \n" +
	487	",comment=@comment \n" +
	488	",attachList=@attachList\n" +
d32c21	489	",imageList=@imageList\n" +
035491	490	",senders=@senders\n" +
F	491	",updateTime=@updateTime where id=@id\n" +
	492	"end\n";
	493	this.doBaseExecute(sql);
	494
	495	}
	496
	497	/**
	498	* 日程周期的生成
	499	* 有设置周期性，则需要根据周期性设置重复生成相关的日程
	500	* 每天例如：start:2024-8-1,end:2024-8-4
	501	* 指定结束时间(2024-8-3)，则生成从开始时间到结束时间的相同日程
	502	* 2024-8-1,2024-8-2,2024-8-3
	503	* 每周例如：start:2024-8-1,end:2024-8-24
	504	* 指定结束时间(2024-8-20)，则会按周一次生成从开始时间到结束时间的相同日程
	505	* 2024-8-1,2024-8-7,2024-8-14
	506	* 每月例如：start:2024-8-1,end:2024-10-24
	507	* 指定结束时间(2024-9-20)，则会按周一次生成从开始时间到结束时间的相同日程
	508	* 2024-8-1,2024-9-1
	509	*
	510	* @param entity
	511	* @return
	512	*/
	513	private List<T482112Entity> prossSchedule(T482112Entity entity) {
	514	List<T482112Entity> list = new ArrayList<>();
	515	if (entity.getType().equalsIgnoreCase("not")) {
	516	//没设置则直接返回
	517	list.add(entity);
	518	} else {
	519	if (StringUtils.isBlank(entity.getStartTime())) {
	520	throw new ApplicationException("开始日期不能为空");
	521	}
	522	if (StringUtils.isBlank(entity.getEndTime())) {
	523	throw new ApplicationException("结束日期不能为空");
	524	}
	525	if (StringUtils.isBlank(entity.getRepeatEnd())) {
	526	throw new ApplicationException("周期性结束日期不能为空");
	527	}
	528	if (entity.getId() == null \|\| entity.getId() == 0) {//新增
	529	calRepeatDate(entity, list);
	530	} else if (entity.getId() != null && entity.getId() != 0 && entity.getRepeatFlag() == 1) {//修改且选择了生成后续重复日程
	531	//修改才执行
	532	//表示需要以当前重复日程重复生成新的重复日程
	533	calRepeatDate(entity, list);
	534	} else {
	535	list.add(entity);
	536	}
	537	}
	538	return list;
	539	}
	540
	541	private void calRepeatDate(T482112Entity entity, List<T482112Entity> list) {
	542
	543	//开始日期
	544	String pattern = "yyyy-MM-dd HH:mm:ss";
	545	Calendar calStart = Calendar.getInstance();
	546	LocalDateTime start = null;
	547	LocalDateTime end = null;
	548	LocalDateTime repeatEnd = null;
	549	try {
	550	start = LocalDateTime.parse(entity.getStartTime(), DateTimeFormatter.ofPattern(pattern));
	551
	552	} catch (Exception e) {
05aec6	553	//表示没有时间，需要增加00:00:00才不出错
035491	554	start = LocalDateTime.parse(entity.getStartTime() + " 00:00:00", DateTimeFormatter.ofPattern(pattern));
F	555	}
	556	try {
	557	end = LocalDateTime.parse(entity.getEndTime(), DateTimeFormatter.ofPattern(pattern));
	558	} catch (Exception e) {
	559	end = LocalDateTime.parse(entity.getEndTime() + " 00:00:00", DateTimeFormatter.ofPattern(pattern));
	560	}
	561	try {
	562	repeatEnd = LocalDateTime.parse(entity.getRepeatEnd(), DateTimeFormatter.ofPattern(pattern));
	563	} catch (Exception e) {
	564	repeatEnd = LocalDateTime.parse(entity.getRepeatEnd() + " 00:00:00", DateTimeFormatter.ofPattern(pattern));
	565	}
	566	calStart.setTime(Date.from(start.atZone(ZoneId.systemDefault()).toInstant()));
	567	//结束日期取周期性设置
	568	Calendar calEnd = Calendar.getInstance();
	569	calEnd.setTime(Date.from(repeatEnd.atZone(ZoneId.systemDefault()).toInstant()));
	570	if (entity.getType().equalsIgnoreCase("everyday")) {
	571	//1，----每天
05aec6	572	repeatByDay(entity, list, start, end, pattern, calStart, calEnd,1);
035491	573	}
F	574	if (entity.getType().equalsIgnoreCase("everyweek")) {
	575	//2，----每周
05aec6	576	repeatByWeek(entity, list, calStart, calEnd, start, end, pattern, 1);
035491	577	}
F	578	if (entity.getType().equalsIgnoreCase("everymonth")) {
	579	//3，----每月
05aec6	580	repeatByMonth(entity, list, calStart, calEnd, start, end, pattern, 1);
035491	581	}
F	582	if (entity.getType().equalsIgnoreCase("ext")) {
05aec6	583	if (entity.getNum() == null \|\| entity.getNum() == 0) {
F	584	throw new ApplicationException("自定义重复周期[num]不能为空");
035491	585	}
F	586	//4，----自定义
05aec6	587	if (entity.getUnit().equalsIgnoreCase("day")) {
035491	588	//1，----每天
05aec6	589	repeatByDay(entity, list, start, end, pattern, calStart,calEnd, entity.getNum());
035491	590	}
05aec6	591	if (entity.getUnit().equalsIgnoreCase("week")) {
035491	592	//2，----每周
05aec6	593	repeatByWeek(entity, list, calStart, calEnd, start, end, pattern, entity.getNum());
035491	594	}
05aec6	595	if (entity.getUnit().equalsIgnoreCase("month")) {
035491	596	//3，----每月
05aec6	597	repeatByMonth(entity, list, calStart, calEnd, start, end, pattern, entity.getNum());
035491	598	}
F	599	}
	600	}
	601
05aec6	602	private static void repeatByDay(T482112Entity entity, List<T482112Entity> list, LocalDateTime start, LocalDateTime end, String pattern, Calendar calStart, Calendar CalEnd, int interval) {
F	603	long dayNumber = (long) Math.floor((ChronoUnit.DAYS.between(start, end)) / interval);
	604	//增加相同标记，以便能把相关联的日程也一起删除
	605	String same= entity.getSamedeleteId();
	606	if(StringUtils.isBlank(same)){
	607	same= RandomStringUtils.randomAlphanumeric(5);
	608	}
	609	entity.setSamedeleteId(same);
035491	610	list.add(entity);
F	611	SimpleDateFormat sdf = new SimpleDateFormat(pattern);
05aec6	612	String currentSame = entity.getSamedeleteId();
F	613	while (checkCalendar(calStart,CalEnd)) {
035491	614	calStart.add(Calendar.DAY_OF_MONTH, 1); //加一天
F	615	T482112Entity temp = new T482112Entity();
	616	BeanUtils.copyProperties(entity, temp);
05aec6	617	//保存修改，内容一点也不变也会生成新的日程，因为这里id都设置成null
035491	618	temp.setId(null);
05aec6	619	String newSame= currentSame+";"+RandomStringUtils.randomAlphanumeric(5);
F	620	temp.setSamedeleteId(newSame);
	621	currentSame=newSame;
035491	622	String dateStr = sdf.format(calStart.getTime());
F	623	temp.setStartTime(dateStr);
	624	//新结束日期=新的日期+相差天数
	625	calStart.add(Calendar.DAY_OF_MONTH, (int) dayNumber);
	626	dateStr = sdf.format(calStart.getTime());
	627	temp.setEndTime(dateStr);
	628	calStart.add(Calendar.DAY_OF_MONTH, -(int) dayNumber);//还原回来
	629	list.add(temp);
	630	}
	631	}
	632
05aec6	633	private void repeatByWeek(T482112Entity entity, List<T482112Entity> list, Calendar calStart, Calendar calEnd, LocalDateTime start, LocalDateTime end, String pattern, int interval) {
035491	634	int start_weekInYear = this.getWeekInYear(calStart.getTime());
F	635	int end_weekInYear = this.getWeekInYear(calEnd.getTime());
05aec6	636	long weekNumber = (long) Math.floor((end_weekInYear - start_weekInYear) / interval);
035491	637	long dayNumber = ChronoUnit.DAYS.between(start, end);
05aec6	638	//增加相同标记，以便能把相关联的日程也一起删除
F	639	String same= entity.getSamedeleteId();
	640	if(StringUtils.isBlank(same)){
	641	same= RandomStringUtils.randomAlphanumeric(5);
	642	}
	643	entity.setSamedeleteId(same);
	644	String currentSame = entity.getSamedeleteId();
035491	645	list.add(entity);
F	646	SimpleDateFormat sdf = new SimpleDateFormat(pattern);
	647	while (weekNumber > 0) {
	648	calStart.add(Calendar.WEEK_OF_YEAR, 1); //加一周
	649	T482112Entity temp = new T482112Entity();
	650	BeanUtils.copyProperties(entity, temp);
	651	temp.setId(null);
05aec6	652	String newSame= currentSame+";"+RandomStringUtils.randomAlphanumeric(5);
F	653	temp.setSamedeleteId(newSame);
	654	currentSame=newSame;
035491	655	String dateStr = sdf.format(calStart.getTime());
F	656	temp.setStartTime(dateStr);
	657	//新结束日期=新的日期+相差天数
	658	calStart.add(Calendar.DAY_OF_MONTH, (int) dayNumber);
	659	dateStr = sdf.format(calStart.getTime());
	660	temp.setEndTime(dateStr);
	661	calStart.add(Calendar.DAY_OF_MONTH, -(int) dayNumber);//还原回来
	662	list.add(temp);
	663	weekNumber--;
	664	}
	665	}
	666
05aec6	667	private static void repeatByMonth(T482112Entity entity, List<T482112Entity> list, Calendar calStart, Calendar calEnd, LocalDateTime start, LocalDateTime end, String pattern, int interval) {
035491	668	int start_monthInYear = calStart.get(Calendar.MONTH);
F	669	int end_monthInYear = calEnd.get(Calendar.MONTH);
05aec6	670	long monthNumber = (long) Math.floor((end_monthInYear - start_monthInYear) / interval);//向下取整，11.7取11
035491	671	long dayNumber = ChronoUnit.DAYS.between(start, end);
05aec6	672	//增加相同标记，以便能把相关联的日程也一起删除
F	673	String same= entity.getSamedeleteId();
	674	if(StringUtils.isBlank(same)){
	675	same= RandomStringUtils.randomAlphanumeric(5);
	676	}
	677	entity.setSamedeleteId(same);
	678	String currentSame = entity.getSamedeleteId();
035491	679	list.add(entity);
F	680	SimpleDateFormat sdf = new SimpleDateFormat(pattern);
	681	while (monthNumber > 0) {
	682	calStart.add(Calendar.MONTH, 1); //加一个月
	683	T482112Entity temp = new T482112Entity();
	684	BeanUtils.copyProperties(entity, temp);
	685	temp.setId(null);
05aec6	686	String newSame= currentSame+";"+RandomStringUtils.randomAlphanumeric(5);
F	687	temp.setSamedeleteId(newSame);
	688	currentSame=newSame;
035491	689	String dateStr = sdf.format(calStart.getTime());
F	690	temp.setStartTime(dateStr);
	691	//新结束日期=新的日期+相差天数
	692	calStart.add(Calendar.DAY_OF_MONTH, (int) dayNumber);
	693	dateStr = sdf.format(calStart.getTime());
	694	temp.setEndTime(dateStr);
	695	calStart.add(Calendar.DAY_OF_MONTH, -(int) dayNumber);//还原回来
	696	list.add(temp);
	697	monthNumber--;
	698	}
	699	}
	700
	701	//获取到所传日期在当年第几周
05aec6	702	public int getWeekInYear(Date date) {
035491	703	Calendar calendar = Calendar.getInstance();
F	704	calendar.setFirstDayOfWeek(Calendar.MONDAY);
	705	calendar.setTime(date);
	706	return calendar.get(Calendar.WEEK_OF_YEAR);
	707	}
05aec6	708
035491	709	/**
F	710	* 因为日历比较用的是time属性，这个值是创建时候生成，就算二个日期一样，也会不一样。所以这里判断年月日，时分秒
	711	*
	712	* @param start
	713	* @param end
	714	* @return
	715	*/
05aec6	716	private static boolean checkCalendar(Calendar start, Calendar end) {
035491	717	int dayNumber = DateUtil.daysBetween(end.getTime(), start.getTime());
F	718	if (dayNumber > 0) {
	719	return true;
	720	} else {
	721	return false;
	722	}
	723	}
	724
	725	@Transactional(rollbackFor = Exception.class)
	726	@Override
	727	public void scheduleSave(T482112Entity t482112Entity) {
	728	if (StringUtils.isBlank(t482112Entity.getTitle())) {
	729	throw new ApplicationException("日程内容不能为空");
	730	}
	731	String sql = " declare @companyId varchar(50),\n" +
	732	" @companyName nvarchar(100),\n" +
	733	" @userCode varchar(30),\n" +
	734	" @userName nvarchar(30),\n" +
	735	" @id bigint,\n" +
	736	" @refType varchar(20),\n" +
	737	" @refCode varchar(50) ,\n" +
05aec6	738	" @refName nvarchar(150) ,\n" +
035491	739	" @title nvarchar(100),\n" +
F	740	" @color varchar(10),\n" +
	741	" @participant_id varchar(100),\n" +
	742	" @start_time datetime,\n" +
	743	" @end_time datetime,\n" +
	744	" @remark nvarchar(230),\n" +
	745	" @attach_list varchar(230),\n" +
	746	" @image_list varchar(230),\n" +
05aec6	747	" @samedeleteId varchar(2000),\n" +
035491	748	" @full_day_flag bit,\n" +
F	749	" @create_time datetime,\n" +
	750	" @update_time datetime,@last_inserted_id bigint\n" +
	751	",@rule_type varchar(50)" +
	752	",@rule_unit varchar(50)\n" +
	753	",@rule_repeatStart varchar(50)\n" +
	754	",@rule_repeatEnd varchar(50)\n" +
	755	",@rule_num int \n" +
	756	",@remind_refid int \n" +
	757	",@remind_type varchar(50)\n" +
	758	",@remind_time varchar(50)\n";
	759	List<T482112Entity> list = this.prossSchedule(t482112Entity);
	760	for (T482112Entity entity : list) {
	761	sql += " select @companyId=" + GridUtils.prossSqlParm(entity.getCompanyId()) + " ,\n" +
	762	"@companyName=" + GridUtils.prossSqlParm(entity.getCompanyName()) + " ,\n" +
	763	"@userCode=" + GridUtils.prossSqlParm(entity.getUserCode()) + " ,\n" +
	764	"@userName=" + GridUtils.prossSqlParm(entity.getUserName()) + " ,\n" +
	765	"@id=" + entity.getId() + " ,\n" +
	766	"@refType=0 ,\n" +
	767	"@refCode=" + GridUtils.prossSqlParm(entity.getRefCode()) + " ,\n" +
05aec6	768	"@refName=" + GridUtils.prossSqlParm(entity.getRefName()) + " ,\n" +
035491	769	"@title=" + GridUtils.prossSqlParm(entity.getTitle()) + " ,\n" +
F	770	"@color=" + GridUtils.prossSqlParm(entity.getColor()) + " ,\n" +
	771	"@participant_id =" + GridUtils.prossSqlParm(entity.getParticipantId()) + " ,\n" +
	772	"@start_time=" + GridUtils.prossSqlParm(entity.getStartTime()) + " ,\n" +
	773	"@end_time=" + GridUtils.prossSqlParm(entity.getEndTime()) + " ,\n" +
	774	"@remark=" + GridUtils.prossSqlParm(entity.getRemark()) + " ,\n" +
	775	"@attach_list=" + GridUtils.prossSqlParm(entity.getAttachList()) + " ,\n" +
	776	"@image_list=" + GridUtils.prossSqlParm(entity.getImageList()) + " ,\n" +
05aec6	777	"@samedeleteId=" + GridUtils.prossSqlParm(entity.getSamedeleteId()) + " ,\n" +
035491	778	"@full_day_flag=" + entity.getFullDayFlag() + " ,\n" +
F	779	"@rule_unit=" + GridUtils.prossSqlParm(entity.getUnit()) + " ,\n" +
	780	"@rule_type=" + GridUtils.prossSqlParm(entity.getType()) + " ,\n" +
	781	"@rule_repeatStart=" + GridUtils.prossSqlParm(entity.getRepeatStart()) + " ,\n" +
	782	"@rule_repeatEnd=" + GridUtils.prossSqlParm(entity.getRepeatEnd()) + " ,\n" +
	783	"@rule_num =" + entity.getNum() + " ,\n" +
	784	"@create_time=getdate() ,\n" +
	785	"@update_time=getdate() \n" +
	786	"if isnull(@id,'')='' \n" +
	787	" begin\n" +
	788	" insert into t482112(" +
	789	" companyId \n" +
	790	",companyName \n" +
	791	",userCode \n" +
	792	",userName \n" +
	793	",refType \n" +
	794	",refCode \n" +
05aec6	795	",refName \n" +
035491	796	",title \n" +
F	797	",color \n" +
	798	",participant_id\n" +
	799	",start_time \n" +
	800	",end_time \n" +
	801	",remark \n" +
	802	",attach_list \n" +
	803	",image_list \n" +
05aec6	804	",samedeleteId \n" +
035491	805	",full_day_flag \n" +
F	806	",unit \n" +
	807	",type \n" +
	808	",repeat_Start \n" +
	809	",repeat_End \n" +
	810	",num " +
	811	",create_time \n" +
	812	",update_time \n)values(" +
	813	" @companyId \n" +
	814	",@companyName \n" +
	815	",@userCode \n" +
	816	",@userName \n" +
	817	",@refType \n" +
	818	",@refCode \n" +
05aec6	819	",@refName \n" +
035491	820	",@title \n" +
F	821	",@color \n" +
	822	",@participant_id\n" +
	823	",@start_time \n" +
	824	",@end_time \n" +
	825	",@remark \n" +
	826	",@attach_list \n" +
	827	",@image_list \n" +
05aec6	828	",@samedeleteId \n" +
035491	829	",@full_day_flag \n" +
F	830	",@rule_unit \n" +
	831	",@rule_type \n" +
	832	",@rule_repeatStart \n" +
	833	",@rule_repeatEnd \n" +
	834	",@rule_num " +
	835	",@create_time \n" +
	836	",@update_time )\n" +
	837	" SELECT @last_inserted_id=IDENT_CURRENT('t482112')\n" +
	838	"end\n" +
	839	" else\n" +//修改
	840	"begin\n" +
	841	" update t482112 set \n" +
	842	"title=@title \n" +
	843	",color =@color \n" +
	844	",participant_id=@participant_id\n" +
	845	",start_time=@start_time \n" +
	846	",end_time=@end_time \n" +
	847	",remark=@remark \n" +
	848	",attach_list=@attach_list \n" +
	849	",image_list =@image_list \n" +
	850	",unit=@rule_unit \n" +
	851	",type=@rule_type \n" +
	852	",num=@rule_num \n" +
	853	",repeat_Start=@rule_repeatStart \n" +
	854	",repeat_End=@rule_repeatEnd \n" +
	855	",update_time=@update_time\n" +
05aec6	856	",full_day_flag=@full_day_flag where id=@id\n" +
035491	857	" SELECT @last_inserted_id=@id\n" +
F	858	"end\n" +
05aec6	859	AddRemindTimesSql(entity.getRemindTimes())+"\n" +
F	860	" exec p482114Save @last_inserted_id \n";
	861	//TODO 日程提醒功能 t482114
035491	862	}
F	863	this.doBaseExecute(sql);
	864	}
	865
	866	/**
	867	* 生成周期性日程设置sql
	868	*
	869	* @param entity
	870	* @return
	871	*/
	872	private String AddRemindTimesSql(List<T482113Entity> entity) {
	873	//只有新增，所以要先全部删除
	874	String sql = " delete from t482113 where refId=@last_inserted_id \n";
	875	for (T482113Entity t482113 : entity) {
	876	sql += "\nselect\n" +
	877	" @remind_type=" + GridUtils.prossSqlParm(t482113.getType()) + "\n" +
	878	",@remind_time=" + GridUtils.prossSqlParm(t482113.getTime()) + "\n" +
	879	",@remind_refid=@last_inserted_id\n" +
	880	" insert into t482113(" +
	881	" refId, \n" +
	882	"time, \n" +
05aec6	883	"type, \n" +
F	884	"samedeleteId \n" +
035491	885	")values(" +
F	886	"@remind_refid, \n" +
	887	"@remind_time, \n" +
05aec6	888	"@remind_type, \n" +
F	889	"@samedeleteId \n" +
035491	890	")\n";
F	891	}
	892	return sql;
	893	}
	894
	895	@Override
05aec6	896	public List<T482112Entity> listByMonth(String date,String dbid,String formid) {
F	897	if (StringUtils.isBlank(date)) {
	898	throw new ApplicationException("时间参数不能都为空");
	899	}
	900	String where = " where DateDiff(month, start_time,'"+date+"-01')=0\n" ;//同一个月
	901	String fileds = "refType \n" +
	902	",id,refCode,refName \n" +
	903	",title \n" +
	904	",color \n" +
	905	",participant_id\n" +
	906	",convert(varchar(10), start_time,120) as start_time \n" +
	907	",end_time \n" +
	908	",remark \n" +
	909	",attach_list \n" +
	910	",image_list \n" +
	911	",full_day_flag \n" +
	912	",unit \n" +
	913	",type \n" +
	914	",repeat_Start \n" +
	915	",repeat_End \n" +
	916	",num " +
	917	",create_time \n" +
	918	",update_time \n";
	919
	920	String sql = "set nocount on ; \n" +
	921	"select " +
	922	fileds + " from t482112 a " + where +
	923	" order by a.start_time asc \n";
	924	List<T482112Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482112Entity.class));
	925
	926	list.stream().forEach(x -> {
	927	if (StringUtils.isNotBlank(x.getAttachList())) {
	928	FileStructEntity fileStructEntity = new FileStructEntity();
	929	fileStructEntity.setDbid(dbid);
	930	fileStructEntity.setFormid(formid);
	931	fileStructEntity.setFileStr(x.getAttachList());
	932	x.setFiles(Utils.getFileEntities(fileStructEntity));
	933	}
	934	});
	935	return list;
	936	}
	937
	938	@Override
035491	939	public Response scheduleList(ScheduleRequestEntity request) {
05aec6	940	String where = " where 1=1 ";
035491	941	if (request.getSearchTxt() == null) {
F	942	throw new ApplicationException("查询参数不能都为空");
	943	} else {
	944	String refCode = request.getSearchTxt().get("refCode");
05aec6	945	if (refCode != null) {
F	946	where += " and refCode=" + GridUtils.prossSqlParm(refCode);
	947	}
035491	948	String completeFlag = request.getSearchTxt().get("completeFlag");
F	949	if (completeFlag == null) {
05aec6	950	where += " and isnull(completeFlag,0)=0 ";
035491	951	} else {
F	952	where += " and isnull(completeFlag,0)=" + completeFlag;
	953	}
	954	}
	955	String fileds = "refType \n" +
05aec6	956	",id,refCode,refName \n" +
035491	957	",title \n" +
F	958	",color \n" +
d32c21	959	",userCode \n" +
F	960	",userName \n" +
035491	961	",participant_id\n" +
F	962	",start_time \n" +
	963	",end_time \n" +
	964	",remark \n" +
	965	",attach_list \n" +
	966	",image_list \n" +
	967	",full_day_flag \n" +
	968	",unit \n" +
	969	",type \n" +
	970	",repeat_Start \n" +
	971	",repeat_End \n" +
	972	",num " +
	973	",create_time \n" +
	974	",update_time \n" +
d32c21	975	",pinFlag \n" +
F	976	",completeFlag \n" +
035491	977	",@TotalRowCount as totalRowCount ,@pageCount as pageCount ";
F	978
	979	String sql = "set nocount on ; \n" +
	980	" declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" +
	981	" select @Limit = " + request.getPageSize() + " , @Page = " + request.getPageNo() + " ; \n" +
	982	" declare @TotalRowCount int ,@pageCount int; \n" +
	983	" select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
	984	" select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
	985	" select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from t482112 a " + where + " \n" +
	986	" SELECT * FROM ( \n" +
	987	" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.update_time desc) AS NO,\n" +
	988	fileds + " from t482112 a " + where +
	989	" order by a.update_time desc \n" +
	990	" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo";
	991	List<T482112Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482112Entity.class));
	992
	993	list.stream().forEach(x -> {
	994	if (StringUtils.isNotBlank(x.getAttachList())) {
	995	FileStructEntity fileStructEntity = new FileStructEntity();
	996	fileStructEntity.setDbid(request.getDbid());
	997	fileStructEntity.setFormid(request.getFormId() + "");
	998	fileStructEntity.setFileStr(x.getAttachList());
	999	x.setFiles(Utils.getFileEntities(fileStructEntity));
	1000	}
	1001	});
	1002	Response response = new Response();
	1003	response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
	1004	response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
	1005	response.setData(list);
	1006	return response;
	1007	}
	1008
	1009	@Override
	1010	public Response commentList(CommentRequestEntity request) {
	1011	String where = "";
	1012	if (request.getSearchTxt() == null) {
	1013	throw new ApplicationException("查询参数不能都为空");
	1014	} else {
	1015	String refId = request.getSearchTxt().get("refId");
	1016	if (refId == null) {
	1017	throw new ApplicationException("refId参数不能都为空");
	1018	}
	1019	where = " where refId=" + GridUtils.prossSqlParm(refId);
	1020	}
	1021	String fileds = "userCode, \n" +
	1022	"userName, \n" +
	1023	" refId, \n" +
	1024	" id, \n" +
	1025	"comment, \n" +
	1026	"attachList,\n" +
d32c21	1027	"imageList,\n" +
035491	1028	"convert(varchar(19), createTime,120) as createTime,senders,\n" +
F	1029	"convert(varchar(19), updateTime,120) as updateTime" +
	1030	",case when usercode=@usercode then 1 else 0 end as enableFlag,@TotalRowCount as totalRowCount ,@pageCount as pageCount ";
	1031
	1032	String sql = "set nocount on ; \n" +
	1033	" declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ,@usercode varchar(50)=" + GridUtils.prossSqlParm(request.getUserCode()) + " \n" +
	1034	" select @Limit = " + request.getPageSize() + " , @Page = " + request.getPageNo() + " ; \n" +
	1035	" declare @TotalRowCount int ,@pageCount int; \n" +
	1036	" select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
	1037	" select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
	1038	" select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from t482104 a " + where + " \n" +
	1039	" SELECT * FROM ( \n" +
	1040	" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.updateTime desc) AS NO,\n" +
	1041	fileds + " from t482104 a " + where +
	1042	" order by a.updateTime desc \n" +
	1043	" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo";
	1044	List<T482104Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T482104Entity.class));
	1045
	1046	list.stream().forEach(x -> {
	1047	if (StringUtils.isNotBlank(x.getAttachList())) {
	1048	FileStructEntity fileStructEntity = new FileStructEntity();
	1049	fileStructEntity.setDbid(request.getDbid());
	1050	fileStructEntity.setFormid(request.getFormId() + "");
	1051	fileStructEntity.setFileStr(x.getAttachList());
	1052	x.setFiles(Utils.getFileEntities(fileStructEntity));
	1053	}
d32c21	1054	if (StringUtils.isNotBlank(x.getImageList())) {
F	1055	FileStructEntity fileStructEntity = new FileStructEntity();
	1056	fileStructEntity.setDbid(request.getDbid());
	1057	fileStructEntity.setFormid(request.getFormId() + "");
	1058	fileStructEntity.setFileStr(x.getImageList());
	1059	x.setImageFiles(Utils.getFileEntities(fileStructEntity));
	1060	}
035491	1061	});
F	1062	Response response = new Response();
	1063	response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0);
	1064	response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0);
	1065	response.setData(list);
	1066	return response;
	1067	}
	1068
	1069	@Override
	1070	public void commentDel(Integer id) {
	1071	this.doBaseExecute("delete from t482104 where id=" + id);
7204e3	1072	}
F	1073	}