提交 | 用户 | 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 |
} |