提交 | 用户 | age
|
703a8b
|
1 |
package com.yc.crm.clues.service; |
F |
2 |
|
|
3 |
import com.yc.action.grid.GridUtils; |
035491
|
4 |
import com.yc.crm.base.entity.AuditEntity; |
F |
5 |
import com.yc.crm.base.entity.FileStructEntity; |
7204e3
|
6 |
import com.yc.crm.base.entity.Response; |
035491
|
7 |
import com.yc.crm.base.util.Utils; |
703a8b
|
8 |
import com.yc.crm.clues.entity.*; |
035491
|
9 |
import com.yc.exception.ApplicationException; |
703a8b
|
10 |
import com.yc.service.BaseService; |
F |
11 |
import org.apache.commons.lang3.StringUtils; |
|
12 |
import org.springframework.jdbc.core.BeanPropertyRowMapper; |
|
13 |
import org.springframework.stereotype.Service; |
|
14 |
import org.springframework.transaction.annotation.Transactional; |
|
15 |
|
|
16 |
import java.util.ArrayList; |
|
17 |
import java.util.List; |
|
18 |
import java.util.Map; |
7204e3
|
19 |
import java.util.Set; |
035491
|
20 |
import java.util.stream.Collectors; |
703a8b
|
21 |
|
F |
22 |
@Service |
05aec6
|
23 |
public class CluesServiceImpl extends BaseService implements CluesServiceIfc { |
703a8b
|
24 |
@Transactional(rollbackFor = Exception.class) |
F |
25 |
@Override |
|
26 |
public void save(T481101Entity t481101Entity) { |
05aec6
|
27 |
if (StringUtils.isBlank(t481101Entity.getCluesName())) { |
F |
28 |
throw new ApplicationException("线索名称不能为空"); |
035491
|
29 |
} |
05aec6
|
30 |
String sql = " declare " + |
703a8b
|
31 |
" @DocCode nvarchar(16),\n" + |
F |
32 |
" @FormID int,\n" + |
|
33 |
" @DocDate datetime,\n" + |
|
34 |
" @periodid varchar(50),\n" + |
|
35 |
" @DocStatus int,\n" + |
|
36 |
" @DocStatusName varchar(50),\n" + |
|
37 |
" @companyid nvarchar(20),\n" + |
|
38 |
" @companyname varchar(50),\n" + |
d32c21
|
39 |
" @ccCode varchar(20),\n" + |
F |
40 |
" @ccName varchar(50),\n" + |
703a8b
|
41 |
" @EnterCode varchar(20),\n" + |
F |
42 |
" @EnterName varchar(50),\n" + |
|
43 |
" @EnterDate datetime,\n" + |
|
44 |
" @ModifyName varchar(40),\n" + |
|
45 |
" @ModifyDate datetime,\n" + |
|
46 |
" @modifylog varchar(200),\n" + |
|
47 |
" @PostName varchar(40),\n" + |
|
48 |
" @PostDate datetime,\n" + |
|
49 |
" @CreateUsercode varchar(50),\n" + |
|
50 |
" @CreateUserName varchar(50),\n" + |
|
51 |
" @isRead int,\n" + |
|
52 |
" @Email varchar(50),\n" + |
|
53 |
" @Website varchar(100),\n" + |
|
54 |
" @Phone varchar(50),\n" + |
|
55 |
" @DoNotCall int,\n" + |
|
56 |
" @MobilePhone varchar(50),\n" + |
|
57 |
" @CltStatus varchar(20),\n" + |
|
58 |
" @Country varchar(50),\n" + |
|
59 |
" @Province varchar(50),\n" + |
|
60 |
" @City varchar(50),\n" + |
|
61 |
" @District varchar(50),\n" + |
|
62 |
" @Street varchar(50),\n" + |
|
63 |
" @Address varchar(200),\n" + |
|
64 |
" @PostCode varchar(50),\n" + |
|
65 |
" @HDMemo varchar(500),\n" + |
18553a
|
66 |
" @clues_name nvarchar(250),\n" + |
703a8b
|
67 |
" @last_edit_user varchar(20),\n" + |
F |
68 |
" @edit_time datetime,\n" + |
|
69 |
" @public_time datetime,\n" + |
|
70 |
" @tagList varchar(200),\n" + |
|
71 |
" @homepage varchar(50),\n" + |
|
72 |
" @origin_list nvarchar(250),\n" + |
|
73 |
" @short_name nvarchar(250),\n" + |
|
74 |
" @corporate_name nvarchar(250),\n" + |
|
75 |
" @biz_type nvarchar(20),\n" + |
|
76 |
" @tel_area_code varchar(10),\n" + |
|
77 |
" @tel varchar(20),\n" + |
|
78 |
" @intention_level varchar(50),\n" + |
|
79 |
" @annual_procurement money,\n" + |
|
80 |
" @timezone varchar(10),\n" + |
05aec6
|
81 |
" @ad_keyword nvarchar(300),\n" + |
703a8b
|
82 |
" @image_list varchar(100),\n" + |
F |
83 |
" @scale_id varchar(100),\n" + |
|
84 |
" @seller varchar(50),\n" + |
|
85 |
" @inquiry_origin nvarchar(100),\n" + |
|
86 |
" @category_ids nvarchar(100),\n" + |
|
87 |
" @inquiry_country nvarchar(50),\n" + |
|
88 |
" @pin_flag bit \n" + |
|
89 |
" \ndeclare " + |
|
90 |
" @main_customer_flag bit,\n" + |
|
91 |
" @id bigint, \n" + |
|
92 |
" @refCode varchar(50),\n" + |
|
93 |
" @name nvarchar(50),\n" + |
|
94 |
" @contact_email nvarchar(150),\n" + |
|
95 |
" @gender nvarchar(10),\n" + |
|
96 |
" @post nvarchar(50),\n" + |
|
97 |
" @remark nvarchar(50),\n" + |
|
98 |
" @contact_image_list nvarchar(150),\n" + |
|
99 |
" @birth nvarchar(50),\n" + |
|
100 |
" @post_grade nvarchar(50),\n" + |
|
101 |
" @growth_level int,\n" + |
|
102 |
" @create_time datetime,\n" + |
05aec6
|
103 |
" @update_time datetime,\n" + |
F |
104 |
" @last_inserted_id bigint\n" +//自增主键值 |
703a8b
|
105 |
" \ndeclare " + |
F |
106 |
" @media_type varchar(50),\n" + |
|
107 |
" @media_refid bigint, \n" + |
035491
|
108 |
" @media_id bigint, \n" + |
05aec6
|
109 |
" @media_value varchar(150)\n" + |
703a8b
|
110 |
" \ndeclare " + |
F |
111 |
" @areaCode varchar(50),\n" + |
|
112 |
" @telList_refid bigint, \n" + |
035491
|
113 |
" @telList_id bigint, \n" + |
05aec6
|
114 |
" @telList_tel varchar(150)\n" + |
F |
115 |
//---赋值 |
703a8b
|
116 |
" select " + |
F |
117 |
"@FormID=481101 , \n" + |
|
118 |
"@DocDate=convert(datetime,convert(varchar(10),GETDATE(),120) ) , \n" + |
05aec6
|
119 |
"@DocCode=" + GridUtils.prossSqlParm(t481101Entity.getDocCode()) + ", \n" + |
F |
120 |
"@companyid=" + GridUtils.prossSqlParm(t481101Entity.getCompanyid()) + ", \n" + |
|
121 |
"@companyname= " + GridUtils.prossSqlParm(t481101Entity.getCompanyname()) + ", \n" + |
d32c21
|
122 |
"@ccCode=" + GridUtils.prossSqlParm(t481101Entity.getCcCode()) + ", \n" + |
F |
123 |
"@ccName= " + GridUtils.prossSqlParm(t481101Entity.getCcName()) + ", \n" + |
703a8b
|
124 |
"@periodid= dbo.GetPeriodID(@FormID,@CompanyID,getdate()), \n" + |
05aec6
|
125 |
"@EnterCode=" + GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) + ", \n" + |
F |
126 |
"@EnterName=" + GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) + " , \n" + |
703a8b
|
127 |
"@EnterDate=getdate() , \n" + |
05aec6
|
128 |
"@ModifyName =" + GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) + ", \n" + |
703a8b
|
129 |
"@ModifyDate =getdate() , \n" + |
05aec6
|
130 |
"@PostName =" + GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) + ", \n" + |
703a8b
|
131 |
"@PostDate=getdate() , \n" + |
05aec6
|
132 |
"@CreateUsercode=" + GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) + ", \n" + |
F |
133 |
"@CreateUserName=" + GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) + " , \n" + |
|
134 |
"@isRead =" + t481101Entity.getIsRead() + ", \n" +//TODO 自己新建,isRead=1,其他人 |
703a8b
|
135 |
"@Email=null, \n" + |
05aec6
|
136 |
"@Country =" + GridUtils.prossSqlParm(t481101Entity.getCountry()) + ", \n" + |
F |
137 |
"@province =" + GridUtils.prossSqlParm(t481101Entity.getProvince()) + ", \n" + |
|
138 |
"@city =" + GridUtils.prossSqlParm(t481101Entity.getCity()) + ", \n" + |
|
139 |
"@Address =" + GridUtils.prossSqlParm(t481101Entity.getAddress()) + ", \n" + |
|
140 |
"@PostCode =" + GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) + ", \n" + |
|
141 |
"@HDMemo =" + GridUtils.prossSqlParm(t481101Entity.getHdMemo()) + ", \n" + |
|
142 |
"@clues_name= " + GridUtils.prossSqlParm(t481101Entity.getCluesName()) + ", \n" + |
|
143 |
"@last_edit_user=" + GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) + ", \n" + |
703a8b
|
144 |
"@edit_time=getdate(), \n" + |
F |
145 |
"@public_time =getdate(), \n" + |
05aec6
|
146 |
"@tagList= " + GridUtils.prossSqlParm(t481101Entity.getTagList()) + ", \n" + |
F |
147 |
"@homepage=" + GridUtils.prossSqlParm(t481101Entity.getHomepage()) + ", \n" + |
|
148 |
"@origin_list= " + GridUtils.prossSqlParm(t481101Entity.getOriginList()) + ", \n" + |
|
149 |
"@short_name=" + GridUtils.prossSqlParm(t481101Entity.getShortName()) + ", \n" + |
|
150 |
"@corporate_name=" + GridUtils.prossSqlParm(t481101Entity.getCorporateName()) + ", \n" + |
|
151 |
"@biz_type=" + GridUtils.prossSqlParm(t481101Entity.getBizType()) + ", \n" + |
|
152 |
"@tel_area_code=" + GridUtils.prossSqlParm(t481101Entity.getTelAreaCode()) + ", \n" + |
|
153 |
"@tel=" + GridUtils.prossSqlParm(t481101Entity.getTel()) + ", \n" + |
|
154 |
"@intention_level=" + GridUtils.prossSqlParm(t481101Entity.getIntentionLevel()) + ", \n" + |
|
155 |
"@annual_procurement=" + t481101Entity.getAnnualProcurement() + ", \n" + |
|
156 |
"@timezone=" + GridUtils.prossSqlParm(t481101Entity.getTimezone()) + ", \n" + |
|
157 |
"@ad_keyword =" + GridUtils.prossSqlParm(t481101Entity.getAdKeyword()) + ", \n" + |
|
158 |
"@image_list =" + GridUtils.prossSqlParm(t481101Entity.getImageList()) + ", \n" + |
|
159 |
"@scale_id=" + GridUtils.prossSqlParm(t481101Entity.getScaleId()) + ", \n" + |
|
160 |
"@seller =" + GridUtils.prossSqlParm(t481101Entity.getSeller()) + ", \n" + |
|
161 |
"@inquiry_origin=" + GridUtils.prossSqlParm(t481101Entity.getInquiryOrigin()) + ", \n" + |
|
162 |
"@category_ids =" + GridUtils.prossSqlParm(t481101Entity.getCategoryIds()) + ", \n" + |
|
163 |
"@inquiry_country=" + GridUtils.prossSqlParm(t481101Entity.getInquiryCountry()) + ", \n" + |
|
164 |
"@pin_flag=" + t481101Entity.getPinFlag() + " \n" + |
703a8b
|
165 |
" if isnull(@doccode,'')=''\n" +//新增 |
05aec6
|
166 |
"begin\n" + |
F |
167 |
//新增则生成单号 |
|
168 |
" exec sp_newdoccode @formid,@EnterCode,@doccode output \n" + |
703a8b
|
169 |
"insert into t481101H(" + |
F |
170 |
"FormID" + |
|
171 |
",DocDate" + |
|
172 |
",DocCode" + |
|
173 |
",companyid" + |
|
174 |
",companyname" + |
d32c21
|
175 |
",ccCode" + |
F |
176 |
",ccName" + |
703a8b
|
177 |
",periodid" + |
F |
178 |
",EnterCode" + |
|
179 |
",EnterName" + |
|
180 |
",EnterDate" + |
|
181 |
",ModifyName" + |
|
182 |
",ModifyDate" + |
|
183 |
",PostName" + |
|
184 |
",PostDate" + |
|
185 |
",CreateUsercode" + |
|
186 |
",CreateUserName" + |
|
187 |
",isRead" + |
|
188 |
",Email" + |
|
189 |
",Country " + |
035491
|
190 |
",province " + |
F |
191 |
",city " + |
703a8b
|
192 |
",Address" + |
F |
193 |
",PostCode" + |
|
194 |
",HDMemo" + |
|
195 |
",clues_name" + |
|
196 |
",last_edit_user" + |
|
197 |
",edit_time" + |
|
198 |
",public_time" + |
|
199 |
",tagList" + |
|
200 |
",homepage" + |
|
201 |
",origin_list" + |
|
202 |
",short_name" + |
|
203 |
",corporate_name" + |
|
204 |
",biz_type" + |
|
205 |
",tel_area_code" + |
|
206 |
",tel" + |
|
207 |
",intention_level" + |
|
208 |
",annual_procurement" + |
|
209 |
",timezone" + |
|
210 |
",ad_keyword " + |
|
211 |
",image_list " + |
|
212 |
",scale_id" + |
|
213 |
",seller" + |
|
214 |
",inquiry_origin" + |
|
215 |
",category_ids" + |
|
216 |
",inquiry_country" + |
|
217 |
",pin_flag ,DocStatusName" + |
|
218 |
")values(" + |
|
219 |
"@FormID" + |
|
220 |
",@DocDate" + |
|
221 |
",@DocCode" + |
|
222 |
",@companyid" + |
|
223 |
",@companyname" + |
d32c21
|
224 |
",@ccCode" + |
F |
225 |
",@ccName" + |
703a8b
|
226 |
",@periodid" + |
F |
227 |
",@EnterCode" + |
|
228 |
",@EnterName" + |
|
229 |
",@EnterDate" + |
|
230 |
",@ModifyName" + |
|
231 |
",@ModifyDate" + |
|
232 |
",@PostName" + |
|
233 |
",@PostDate" + |
|
234 |
",@CreateUsercode" + |
|
235 |
",@CreateUserName" + |
|
236 |
",@isRead" + |
|
237 |
",@Email" + |
|
238 |
",@Country " + |
035491
|
239 |
",@province " + |
F |
240 |
",@city " + |
703a8b
|
241 |
",@Address" + |
F |
242 |
",@PostCode" + |
|
243 |
",@HDMemo" + |
|
244 |
",@clues_name" + |
|
245 |
",@last_edit_user" + |
|
246 |
",@edit_time" + |
|
247 |
",@public_time" + |
|
248 |
",@tagList" + |
|
249 |
",@homepage" + |
|
250 |
",@origin_list" + |
|
251 |
",@short_name" + |
|
252 |
",@corporate_name" + |
|
253 |
",@biz_type" + |
|
254 |
",@tel_area_code" + |
|
255 |
",@tel" + |
|
256 |
",@intention_level" + |
|
257 |
",@annual_procurement" + |
|
258 |
",@timezone" + |
|
259 |
",@ad_keyword " + |
|
260 |
",@image_list " + |
|
261 |
",@scale_id" + |
|
262 |
",@seller" + |
|
263 |
",@inquiry_origin" + |
|
264 |
",@category_ids" + |
|
265 |
",@inquiry_country" + |
|
266 |
",@pin_flag,'起草' " + |
|
267 |
")\n" + |
|
268 |
//----处理联系人信息 |
ae692b
|
269 |
contactAddSql(t481101Entity.getContactsList(),"线索") + |
703a8b
|
270 |
"end\n" + |
F |
271 |
"else \n" +//-------修改 |
|
272 |
"begin\n" + |
|
273 |
" update t481101H set " + |
|
274 |
"ModifyName=@ModifyName" + |
|
275 |
",ModifyDate=@ModifyDate" + |
|
276 |
",Country=@Country " + |
035491
|
277 |
",province=@province " + |
F |
278 |
",city=@city " + |
703a8b
|
279 |
",Address=@Address" + |
F |
280 |
",HDMemo=@HDMemo" + |
|
281 |
",clues_name=@clues_name" + |
|
282 |
",last_edit_user=@last_edit_user" + |
|
283 |
",edit_time=@edit_time" + |
|
284 |
",tagList=@tagList" + |
|
285 |
",homepage=@homepage" + |
|
286 |
",origin_list=@origin_list" + |
|
287 |
",short_name=@short_name" + |
|
288 |
",corporate_name=@corporate_name" + |
|
289 |
",biz_type=@biz_type" + |
|
290 |
",tel_area_code=@tel_area_code" + |
|
291 |
",tel=@tel" + |
|
292 |
",intention_level=@intention_level" + |
|
293 |
",annual_procurement=@annual_procurement" + |
|
294 |
",timezone=@timezone" + |
|
295 |
",ad_keyword=@ad_keyword " + |
|
296 |
",image_list=@image_list " + |
|
297 |
",scale_id=@scale_id" + |
|
298 |
",seller=@seller" + |
|
299 |
",inquiry_origin=@inquiry_origin" + |
|
300 |
",category_ids=@category_ids" + |
|
301 |
",inquiry_country=@inquiry_country,pin_flag=@pin_flag from t481101H where doccode=@doccode\n" + |
|
302 |
//----处理联系人信息 |
ae692b
|
303 |
contactUpdateSql(t481101Entity.getContactsList(),"线索") + |
703a8b
|
304 |
"end\n" + |
035491
|
305 |
//--处理审计功能 |
F |
306 |
auditAddSql(t481101Entity); |
7204e3
|
307 |
//System.out.println("clues:"+sql); |
703a8b
|
308 |
this.doBaseExecute(sql); |
F |
309 |
} |
|
310 |
|
035491
|
311 |
private String auditAddSql(T481101Entity t481101Entity) { |
05aec6
|
312 |
List<AuditEntity> list = t481101Entity.getAuditRecords(); |
F |
313 |
if (list == null || list.size() == 0) { |
|
314 |
return ""; |
|
315 |
} |
|
316 |
String sql = " declare @now datetime=getdate()\n";//方便同一时间提交到时输出成一条 |
|
317 |
for (AuditEntity audit : list) { |
|
318 |
sql += " insert into _sysAudit([formid],[newvalue],[headflag],[auditType],[fieldname],[mainFormid],[usercode],[oldvalue],[fieldid],[username],[doccode],[auditIndex],[auditDateTime],[auditDate])values(" + t481101Entity.getFormId() + "," + GridUtils.prossSqlParm(audit.getNewValue()) + ",0," + GridUtils.prossSqlParm(audit.getAuditType()) + "," + GridUtils.prossSqlParm(audit.getFieldName()) + "," + t481101Entity.getFormId() + "," + GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) + "," + GridUtils.prossSqlParm(audit.getOldValue()) + "," + GridUtils.prossSqlParm(audit.getFieldId()) + "," + GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) + ",@docCode,null,@now,convert(varchar(10),@now,120))\n"; |
035491
|
319 |
} |
F |
320 |
return sql; |
|
321 |
} |
7204e3
|
322 |
|
d32c21
|
323 |
@Override |
F |
324 |
public Response contactList(CluesRequestEntity request) { |
|
325 |
String fileds = " " + |
|
326 |
"c.refCode,c.id,a.docCode,a.clues_name \n" + |
|
327 |
",c.name \n" + |
|
328 |
",c.email \n" + |
|
329 |
",@TotalRowCount as totalRowCount ,@pageCount as pageCount \n"; |
|
330 |
String where = " contactType='线索' "; |
|
331 |
if (request.getSearchTxt() != null && request.getSearchTxt().size() > 0) { |
|
332 |
//拼接查询条件 |
|
333 |
where += getSearchInfo(request); |
|
334 |
} |
|
335 |
String sql = "set nocount on ; \n" + |
|
336 |
// " declare @key varchar(300) \n" + |
|
337 |
// " select @key ="+1+" \n" + |
|
338 |
" declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" + |
|
339 |
" select @Limit = " + request.getPageSize() + " , @Page = " + request.getPageNo() + " ; \n" + |
|
340 |
" declare @TotalRowCount int ,@pageCount int; \n" + |
|
341 |
" select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" + |
|
342 |
" select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" + |
|
343 |
" select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from t482103 c \n" + |
|
344 |
" left join t481101h a on a.docCode=c.refCode\n" + |
|
345 |
" left join t481104h b on a.docCode=b.refCode" + |
|
346 |
" where " + where + " \n" + |
|
347 |
" SELECT * FROM ( \n" + |
|
348 |
" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.docCode) AS NO,\n" + |
|
349 |
fileds + " from t482103 c \n" + |
|
350 |
" left join t481101h a on a.docCode=c.refCode\n" + |
|
351 |
" left join t481104h b on a.docCode=b.refCode" + |
|
352 |
" where " + where + |
|
353 |
" order by a.docCode asc \n" + |
|
354 |
" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo"; |
|
355 |
List<ContactEntity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactEntity.class)); |
|
356 |
//增加联系人输出 |
|
357 |
Response response = new Response(); |
|
358 |
response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0); |
|
359 |
response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0); |
|
360 |
response.setData(list); |
|
361 |
return response; |
|
362 |
} |
7204e3
|
363 |
|
703a8b
|
364 |
@Override |
7204e3
|
365 |
public Response getAllClues(CluesRequestEntity request) { |
05aec6
|
366 |
String fileds = "" + |
7204e3
|
367 |
"a.DocCode \n" + |
F |
368 |
",a.FormID \n" + |
|
369 |
",a.DocStatus \n" + |
|
370 |
",a.DocStatusName \n" + |
|
371 |
",a.companyid \n" + |
|
372 |
",a.companyname \n" + |
|
373 |
",a.EnterCode \n" + |
|
374 |
",a.EnterName \n" + |
|
375 |
",a.EnterDate \n" + |
|
376 |
",a.ModifyName \n" + |
|
377 |
",a.ModifyDate \n" + |
|
378 |
",a.PostName \n" + |
|
379 |
",a.PostDate \n" + |
|
380 |
",a.CreateUsercode \n" + |
|
381 |
",a.CreateUserName \n" + |
|
382 |
",a.Country \n" + |
|
383 |
",a.HDMemo \n" + |
|
384 |
",a.clues_name \n" + |
|
385 |
",a.archive_time \n" + |
|
386 |
",a.order_time \n" + |
|
387 |
",a.lost_day_count \n" + |
|
388 |
",a.transfer_count \n" + |
|
389 |
",a.release_count \n" + |
|
390 |
",a.is_archive \n" + |
|
391 |
",a.archive_type \n" + |
|
392 |
",a.status_id \n" + |
|
393 |
",a.status \n" + |
|
394 |
",a.follow_up_time \n" + |
|
395 |
",a.fail_type \n" + |
|
396 |
",a.fail_status \n" + |
|
397 |
",a.fail_status_name \n" + |
|
398 |
",a.fail_reason \n" + |
|
399 |
",a.read_flag \n" + |
|
400 |
",a.last_edit_user \n" + |
|
401 |
",a.edit_time \n" + |
|
402 |
",a.private_time \n" + |
|
403 |
",a.public_time \n" + |
|
404 |
",a.is_public \n" + |
|
405 |
",a.store_id \n" + |
|
406 |
",a.star \n" + |
|
407 |
",a.transform_task_status \n" + |
|
408 |
",a.next_follow_up_time \n" + |
|
409 |
",a.duplicate_flag \n" + |
|
410 |
",a.tagList \n" + |
|
411 |
",a.homepage \n" + |
035491
|
412 |
",a.address \n" + |
7204e3
|
413 |
",a.origin_list \n" + |
F |
414 |
",a.short_name \n" + |
|
415 |
",a.corporate_name \n" + |
|
416 |
",a.biz_type \n" + |
|
417 |
",a.tel_area_code \n" + |
|
418 |
",a.tel \n" + |
|
419 |
",a.intention_level \n" + |
|
420 |
",a.annual_procurement \n" + |
|
421 |
",a.timezone \n" + |
|
422 |
",a.ad_keyword \n" + |
|
423 |
",a.image_list \n" + |
|
424 |
",a.scale_id \n" + |
|
425 |
",a.seller \n" + |
|
426 |
",a.inquiry_origin \n" + |
|
427 |
",a.category_ids \n" + |
|
428 |
",a.inquiry_country \n" + |
|
429 |
",b.id \n" + |
|
430 |
",b.refCode \n" + |
|
431 |
",b.ownerCode \n" + |
|
432 |
",b.ownerName \n" + |
|
433 |
",b.enterTime \n" + |
|
434 |
",b.exitTime \n" + |
|
435 |
",b.isRead as hasRead \n" + |
|
436 |
",b.reasonForRollback \n" + |
|
437 |
",b.costs \n" + |
|
438 |
",b.pinFlag as hasPinFlag \n" + |
|
439 |
",b.firstFllowUpTime \n" + |
|
440 |
",b.lastModifyTime \n" + |
|
441 |
",b.daysNotContacted " + |
035491
|
442 |
",b.systemFlag " + |
F |
443 |
",b.cluesStatusFlag " + |
7204e3
|
444 |
",@TotalRowCount as totalRowCount ,@pageCount as pageCount \n"; |
05aec6
|
445 |
String where = " 1=1 "; |
F |
446 |
if (request.getSearchTxt() != null && request.getSearchTxt().size() > 0) { |
7204e3
|
447 |
//拼接查询条件 |
05aec6
|
448 |
where += getSearchInfo(request); |
7204e3
|
449 |
} |
05aec6
|
450 |
String sql = "set nocount on ; \n" + |
7204e3
|
451 |
// " declare @key varchar(300) \n" + |
F |
452 |
// " select @key ="+1+" \n" + |
|
453 |
" declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" + |
05aec6
|
454 |
" select @Limit = " + request.getPageSize() + " , @Page = " + request.getPageNo() + " ; \n" + |
7204e3
|
455 |
" declare @TotalRowCount int ,@pageCount int; \n" + |
F |
456 |
" select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" + |
|
457 |
" select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" + |
05aec6
|
458 |
" select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from t481101h a left join t481104h b on a.docCode=b.refCode where " + where + " \n" + |
7204e3
|
459 |
" SELECT * FROM ( \n" + |
d32c21
|
460 |
" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.docCode) AS NO,\n" + |
05aec6
|
461 |
fileds + " from t481101h a left join t481104h b on a.docCode=b.refCode\n" + |
F |
462 |
" where " + where + |
7204e3
|
463 |
" order by a.docCode asc \n" + |
F |
464 |
" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo"; |
|
465 |
List<T481104Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T481104Entity.class)); |
|
466 |
//增加联系人输出 |
05aec6
|
467 |
if (list != null && list.size() > 0) { |
F |
468 |
list.stream().forEach(x -> { |
7204e3
|
469 |
List<T482103Entity> query = this.jdbcTemplate.query("select main_customer_flag\n" + |
F |
470 |
",refCode,id \n" + |
|
471 |
",name \n" + |
|
472 |
",email \n" + |
|
473 |
",gender \n" + |
|
474 |
",post \n" + |
|
475 |
",remark \n" + |
|
476 |
",image_list \n" + |
|
477 |
",birth \n" + |
|
478 |
",post_grade \n" + |
|
479 |
",growth_level \n" + |
|
480 |
",create_time \n" + |
|
481 |
",update_time \n" + |
|
482 |
",DocVersion \n from t482103 where refCode=" + GridUtils.prossSqlParm(x.getDocCode()), new BeanPropertyRowMapper<>(T482103Entity.class)); |
05aec6
|
483 |
if (query != null && query.size() > 0) { |
F |
484 |
query.stream().forEach(y -> { |
7204e3
|
485 |
//--社交平台 |
F |
486 |
List<T482111Entity> mediaList = this.jdbcTemplate.query("select \n" + |
|
487 |
"refId,id \n" + |
|
488 |
",value \n" + |
05aec6
|
489 |
",type from t482111 where refId=" + y.getId(), new BeanPropertyRowMapper<>(T482111Entity.class)); |
7204e3
|
490 |
y.setMediaList(mediaList); |
F |
491 |
//电话列表 |
|
492 |
List<T482116Entity> telList = this.jdbcTemplate.query("select " + |
|
493 |
"refId,id,areaCode,tel from t482116 where refId=" + y.getId(), new BeanPropertyRowMapper<>(T482116Entity.class)); |
|
494 |
y.setTelList(telList); |
|
495 |
}); |
|
496 |
} |
|
497 |
x.setContactsList(query); |
035491
|
498 |
//附件的输出 |
F |
499 |
//处理图片,附件,生成调用url及相关信息 |
05aec6
|
500 |
if (StringUtils.isNotBlank(x.getImageList())) { |
035491
|
501 |
FileStructEntity fileStructEntity = new FileStructEntity(); |
F |
502 |
fileStructEntity.setDbid(request.getDbid()); |
|
503 |
fileStructEntity.setFormid(request.getFormId() + ""); |
|
504 |
fileStructEntity.setFileStr(x.getImageList()); |
|
505 |
x.setFiles(Utils.getFileEntities(fileStructEntity)); |
|
506 |
} |
7204e3
|
507 |
}); |
F |
508 |
} |
05aec6
|
509 |
Response response = new Response(); |
F |
510 |
response.setTotalCount((list != null && list.size() > 0) ? list.get(0).getTotalRowCount() : 0); |
|
511 |
response.setPageCount((list != null && list.size() > 0) ? list.get(0).getPageCount() : 0); |
7204e3
|
512 |
response.setData(list); |
F |
513 |
return response; |
d32c21
|
514 |
} |
F |
515 |
@Transactional(rollbackFor = Exception.class) |
|
516 |
@Override |
|
517 |
public void cluesDel(String docCode) { |
|
518 |
//删除线索,需要把相关的联系人,跟进,日程,评论也删除 |
|
519 |
String sql="declare @docCode varchar(50)="+ GridUtils.prossSqlParm(docCode)+"\n" + |
|
520 |
" exec p481101Del @docCode"; |
|
521 |
this.doBaseExecute(sql); |
|
522 |
} |
|
523 |
|
|
524 |
@Override |
|
525 |
public void cluesFail(T481104Entity entity) { |
|
526 |
String sql="declare @docCode varchar(50)="+ GridUtils.prossSqlParm(entity.getDocCode())+",\n" + |
|
527 |
"@failStatus int=" +entity.getFailStatus()+",\n"+ |
|
528 |
"@failStatusName varchar(50)="+ GridUtils.prossSqlParm(entity.getFailStatusName())+",\n" + |
|
529 |
"@failReason varchar(250)="+ GridUtils.prossSqlParm(entity.getFailReason())+"\n" + |
|
530 |
"update t481101h set fail_Status=@failStatus,fail_Status_Name=@failStatusName,fail_Reason=@failReason where docCode=@docCode\n"; |
|
531 |
this.doBaseExecute(sql); |
7204e3
|
532 |
} |
035491
|
533 |
|
F |
534 |
@Override |
05aec6
|
535 |
public T481101Entity get(CluesRequestEntity request) { |
F |
536 |
String fileds = "" + |
|
537 |
"a.DocCode \n" + |
|
538 |
",a.FormID \n" + |
|
539 |
",a.DocStatus \n" + |
|
540 |
",a.DocStatusName \n" + |
|
541 |
",a.companyid \n" + |
|
542 |
",a.companyname \n" + |
|
543 |
",a.EnterCode \n" + |
|
544 |
",a.EnterName \n" + |
|
545 |
",a.EnterDate \n" + |
|
546 |
",a.ModifyName \n" + |
|
547 |
",a.ModifyDate \n" + |
|
548 |
",a.PostName \n" + |
|
549 |
",a.PostDate \n" + |
|
550 |
",a.CreateUsercode \n" + |
|
551 |
",a.CreateUserName \n" + |
|
552 |
",a.Country \n" + |
|
553 |
",a.HDMemo \n" + |
|
554 |
",a.clues_name \n" + |
|
555 |
",a.archive_time \n" + |
|
556 |
",a.order_time \n" + |
|
557 |
",a.lost_day_count \n" + |
|
558 |
",a.transfer_count \n" + |
|
559 |
",a.release_count \n" + |
|
560 |
",a.is_archive \n" + |
|
561 |
",a.archive_type \n" + |
|
562 |
",a.status_id \n" + |
|
563 |
",a.status \n" + |
|
564 |
",a.follow_up_time \n" + |
|
565 |
",a.fail_type \n" + |
|
566 |
",a.fail_status \n" + |
|
567 |
",a.fail_status_name \n" + |
|
568 |
",a.fail_reason \n" + |
|
569 |
",a.read_flag \n" + |
|
570 |
",a.last_edit_user \n" + |
|
571 |
",a.edit_time \n" + |
|
572 |
",a.private_time \n" + |
|
573 |
",a.public_time \n" + |
|
574 |
",a.is_public \n" + |
|
575 |
",a.store_id \n" + |
|
576 |
",a.star \n" + |
|
577 |
",a.transform_task_status \n" + |
|
578 |
",a.next_follow_up_time \n" + |
|
579 |
",a.duplicate_flag \n" + |
|
580 |
",a.tagList \n" + |
|
581 |
",a.homepage \n" + |
|
582 |
",a.address \n" + |
|
583 |
",a.origin_list \n" + |
|
584 |
",a.short_name \n" + |
|
585 |
",a.corporate_name \n" + |
|
586 |
",a.biz_type \n" + |
|
587 |
",a.tel_area_code \n" + |
|
588 |
",a.tel \n" + |
|
589 |
",a.intention_level \n" + |
|
590 |
",a.annual_procurement \n" + |
|
591 |
",a.timezone \n" + |
|
592 |
",a.ad_keyword \n" + |
|
593 |
",a.image_list \n" + |
|
594 |
",a.scale_id \n" + |
|
595 |
",a.seller \n" + |
|
596 |
",a.inquiry_origin \n" + |
|
597 |
",a.category_ids \n" + |
|
598 |
",a.inquiry_country \n" + |
|
599 |
",b.id \n" + |
|
600 |
",b.refCode \n" + |
|
601 |
",b.ownerCode \n" + |
|
602 |
",b.ownerName \n" + |
|
603 |
",b.enterTime \n" + |
|
604 |
",b.exitTime \n" + |
|
605 |
",b.isRead as hasRead \n" + |
|
606 |
",b.reasonForRollback \n" + |
|
607 |
",b.costs \n" + |
|
608 |
",b.pinFlag as hasPinFlag \n" + |
|
609 |
",b.firstFllowUpTime \n" + |
|
610 |
",b.lastModifyTime \n" + |
|
611 |
",b.daysNotContacted " + |
|
612 |
",b.systemFlag " + |
|
613 |
",b.cluesStatusFlag "; |
|
614 |
String where = " b.refCode= " + GridUtils.prossSqlParm(request.getDocCode()); |
|
615 |
|
|
616 |
String sql = "set nocount on ; \n" + |
|
617 |
" select \n" + |
|
618 |
fileds + " from t481101h a left join t481104h b on a.docCode=b.refCode\n" + |
|
619 |
" where " + where; |
|
620 |
T481104Entity entity = this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(T481104Entity.class)); |
|
621 |
//增加联系人输出 |
|
622 |
if (entity != null) { |
|
623 |
List<T482103Entity> query = this.jdbcTemplate.query("select main_customer_flag\n" + |
|
624 |
",refCode,id \n" + |
|
625 |
",name \n" + |
|
626 |
",email \n" + |
|
627 |
",gender \n" + |
|
628 |
",post \n" + |
|
629 |
",remark \n" + |
|
630 |
",image_list \n" + |
|
631 |
",birth \n" + |
|
632 |
",post_grade \n" + |
|
633 |
",growth_level \n" + |
|
634 |
",create_time \n" + |
|
635 |
",update_time \n" + |
|
636 |
",DocVersion \n from t482103 where refCode=" + GridUtils.prossSqlParm(entity.getDocCode()), new BeanPropertyRowMapper<>(T482103Entity.class)); |
|
637 |
if (query != null && query.size() > 0) { |
|
638 |
query.stream().forEach(y -> { |
|
639 |
//--社交平台 |
|
640 |
List<T482111Entity> mediaList = this.jdbcTemplate.query("select \n" + |
|
641 |
"refId,id \n" + |
|
642 |
",value \n" + |
|
643 |
",type from t482111 where refId=" + y.getId(), new BeanPropertyRowMapper<>(T482111Entity.class)); |
|
644 |
y.setMediaList(mediaList); |
|
645 |
//电话列表 |
|
646 |
List<T482116Entity> telList = this.jdbcTemplate.query("select " + |
|
647 |
"refId,id,areaCode,tel from t482116 where refId=" + y.getId(), new BeanPropertyRowMapper<>(T482116Entity.class)); |
|
648 |
y.setTelList(telList); |
|
649 |
}); |
|
650 |
} |
|
651 |
entity.setContactsList(query); |
|
652 |
//附件的输出 |
|
653 |
//处理图片,附件,生成调用url及相关信息 |
|
654 |
if (StringUtils.isNotBlank(entity.getImageList())) { |
|
655 |
FileStructEntity fileStructEntity = new FileStructEntity(); |
|
656 |
fileStructEntity.setDbid(request.getDbid()); |
|
657 |
fileStructEntity.setFormid(request.getFormId() + ""); |
|
658 |
fileStructEntity.setFileStr(entity.getImageList()); |
|
659 |
entity.setFiles(Utils.getFileEntities(fileStructEntity)); |
|
660 |
} |
|
661 |
} |
|
662 |
return entity; |
|
663 |
} |
|
664 |
|
|
665 |
@Override |
035491
|
666 |
public void attention(CluesRequestEntity cluesRequest) { |
05aec6
|
667 |
String sql = "update a set a.pinFlag=" + cluesRequest.getPinFlag() + " from t481104h a where id=" + cluesRequest.getId(); |
035491
|
668 |
this.doBaseExecute(sql); |
F |
669 |
} |
d32c21
|
670 |
@Transactional(rollbackFor = Exception.class) |
F |
671 |
@Override |
|
672 |
public void newAssign(AssignCluesEntity assignClues) { |
18553a
|
673 |
//推送规则: |
F |
674 |
// 1,系统预设个人跟进线索,客户的最大数 |
|
675 |
//2,根据业务员的级别优先推送,影响级别的因素有:业务员业绩金额(历史业绩)、畅销品(可以匹配到公司畅销品)、新人业务员(如何定义为新人?入职时间,成交数)、客户的国家、业务员优势(外语,耐心)等。(新人业务员一般会分配畅销品) |
|
676 |
//由这些维度优先级进行推送给业务员 |
|
677 |
|
d32c21
|
678 |
if(StringUtils.isBlank(assignClues.getOwnerCode())&&StringUtils.isBlank(assignClues.getOwnerCcCode())){ |
F |
679 |
throw new ApplicationException("部门和用户不能都为空"); |
|
680 |
} |
|
681 |
String sql = " declare @refCode varchar(20),@ownerCode varchar(20),@ownerName varchar(20)\n" + |
18553a
|
682 |
" ,@ownerCcCode varchar(20),@ownerCcName varchar(20),@ownerType int=0 \n" + |
d32c21
|
683 |
"select @refCode=" + GridUtils.prossSqlParm(assignClues.getDocCode()) + ",@ownerCode=" + GridUtils.prossSqlParm(assignClues.getOwnerCode()) + ",@ownerName=" + GridUtils.prossSqlParm(assignClues.getOwnerName()) + ",@ownerCcCode=" + GridUtils.prossSqlParm(assignClues.getOwnerCcCode()) + ",@ownerCcName=" + GridUtils.prossSqlParm(assignClues.getOwnerCcName()) + "\n"; |
18553a
|
684 |
if(StringUtils.isNotBlank(assignClues.getOwnerCode())) { |
d32c21
|
685 |
sql+=" select @ownerType=1 \n";//表示当前线索是分配到个人,而不是部门 |
F |
686 |
} |
18553a
|
687 |
sql+=" declare @num int=0\n" + |
F |
688 |
" select @num=count(1) from t481101h where isnull(ispush,0)=1 \n" + |
|
689 |
"if @num=1 \n" + |
|
690 |
"begin\n" + |
|
691 |
" raiserror('当前线索已分配,不能再分配',16,1)\n" + |
|
692 |
" return\n" + |
|
693 |
"end\n" + |
|
694 |
"insert into t481104h(refCode \n" + |
d32c21
|
695 |
",ownerCode \n" + |
F |
696 |
",ownerName \n" + |
|
697 |
",ownerCcCode \n" + |
|
698 |
",ownerCcName \n" + |
|
699 |
",ownerType \n" + |
18553a
|
700 |
",enterTime \n)values(@refCode,@ownerCode,@ownerName,@ownerCcCode,@ownerCcName,@ownerType,getdate())\n" + |
F |
701 |
" update t481101h set isPush=1 where doccode=@refCode\n"; |
d32c21
|
702 |
this.doBaseExecute(sql); |
F |
703 |
} |
035491
|
704 |
|
F |
705 |
@Transactional(rollbackFor = Exception.class) |
7204e3
|
706 |
@Override |
d32c21
|
707 |
public void assign(AssignCluesEntity assignClues,String userName) { |
F |
708 |
String sql = " declare @id int,@reasonForRollback nvarchar(300),@refCode varchar(20),@ownerCode varchar(20),@ownerName varchar(20)\n" + |
|
709 |
"select @refCode=" + GridUtils.prossSqlParm(assignClues.getDocCode()) + ",@ownerCode=" + GridUtils.prossSqlParm(assignClues.getOwnerCode()) + ",@ownerName=" + GridUtils.prossSqlParm(assignClues.getOwnerName()) + ",@reasonForRollback='线索已移出,原因:"+userName+"重新分配给"+assignClues.getOwnerName()+"'\n" + |
|
710 |
" select @id=id from t481104h where refCode=@refCode\n" + |
|
711 |
" update t481104h set exitTime=getDate(),reasonForRollback=@reasonForRollback where id=@id\n" + |
7204e3
|
712 |
" insert into t481104h(refCode \n" + |
F |
713 |
",ownerCode \n" + |
|
714 |
",ownerName \n" + |
18553a
|
715 |
",ownerType \n" + |
F |
716 |
",enterTime \n)values(@refCode,@ownerCode,@ownerName,1,getdate())"; |
7204e3
|
717 |
this.doBaseExecute(sql); |
F |
718 |
} |
035491
|
719 |
|
F |
720 |
@Override |
|
721 |
public void changeStatus(T481104Entity entity) { |
05aec6
|
722 |
String sql = " update a set a.cluesStatusFlag=" + entity.getCluesStatusFlag() + " from t481104h a where id= " + entity.getId(); |
035491
|
723 |
this.doBaseExecute(sql); |
F |
724 |
} |
|
725 |
|
7204e3
|
726 |
/** |
F |
727 |
* 拼接查询条件 |
05aec6
|
728 |
* |
7204e3
|
729 |
* @param request |
F |
730 |
* @return |
|
731 |
*/ |
|
732 |
private String getSearchInfo(CluesRequestEntity request) { |
d32c21
|
733 |
//自已创建或已发布给指定人员或属于当前部门 |
F |
734 |
String where = " and((((a.CreateUsercode="+GridUtils.prossSqlParm(request.getCreateUsercode())+" or (isnull(a.isPush,0)=1 and b.ownerCode="+GridUtils.prossSqlParm(request.getCreateUsercode())+")) and isnull(b.reasonForRollback,'')='') or b.ownerCcCode="+GridUtils.prossSqlParm(request.getCccode())+") \n"; |
7204e3
|
735 |
Set<Map.Entry<String, String>> entries = request.getSearchTxt().entrySet(); |
F |
736 |
for (Map.Entry<String, String> entry : entries) { |
05aec6
|
737 |
if (entry.getKey().equalsIgnoreCase("cluesName")) { |
F |
738 |
where += " and a.clues_name like '%" + entry.getValue() + "%'"; |
7204e3
|
739 |
} |
05aec6
|
740 |
if (entry.getKey().equalsIgnoreCase("isRead")) { |
F |
741 |
where += " and isnull(b.isRead,0)=" + entry.getValue(); |
7204e3
|
742 |
} |
05aec6
|
743 |
if (entry.getKey().equalsIgnoreCase("pinFlag")) { |
F |
744 |
where += " and isnull(b.pinFlag,0)=" + entry.getValue(); |
7204e3
|
745 |
} |
05aec6
|
746 |
if (entry.getKey().equalsIgnoreCase("ownerCode")) { |
d32c21
|
747 |
where += " and b.ownerCode in (select list from getinstr( '" + entry.getValue() + "'))"; |
035491
|
748 |
} |
05aec6
|
749 |
if (entry.getKey().equalsIgnoreCase("systemFlag")) { |
F |
750 |
where += " and isnull(b.systemFlag,0) = " + entry.getValue() + ""; |
035491
|
751 |
} |
05aec6
|
752 |
if (entry.getKey().equalsIgnoreCase("cluesStatusFlag")) { |
F |
753 |
where += " and isnull(b.cluesStatusFlag,0) = " + entry.getValue() + ""; |
035491
|
754 |
} |
05aec6
|
755 |
if (entry.getKey().equalsIgnoreCase("tagList")) { |
F |
756 |
where += " and a.tag_List like '%" + entry.getValue() + "%'"; |
035491
|
757 |
} |
05aec6
|
758 |
if (entry.getKey().equalsIgnoreCase("failStatus")) { |
F |
759 |
where += " and isnull(a.failStatus,0) = " + entry.getValue() + ""; |
035491
|
760 |
} |
05aec6
|
761 |
if (entry.getKey().equalsIgnoreCase("cluesStatusFlag")) { |
F |
762 |
where += " and isnull(b.cluesStatusFlag,0) = " + entry.getValue() + ""; |
035491
|
763 |
} |
05aec6
|
764 |
if (entry.getKey().equalsIgnoreCase("originList")) { |
F |
765 |
where += " and a.originList like '%" + entry.getValue() + "%'"; |
035491
|
766 |
} |
05aec6
|
767 |
if (entry.getKey().equalsIgnoreCase("createUsercode")) { |
d32c21
|
768 |
where += " and a.createUsercode in (select list from getinstr( '" + entry.getValue() + "'))"; |
035491
|
769 |
} |
05aec6
|
770 |
if (entry.getKey().equalsIgnoreCase("country")) { |
F |
771 |
where += " and a.country = '" + entry.getValue() + "'"; |
7204e3
|
772 |
} |
05aec6
|
773 |
if (entry.getKey().equalsIgnoreCase("lastModifyTime")) { |
F |
774 |
String[] value = entry.getValue().split(";"); |
7204e3
|
775 |
|
d32c21
|
776 |
where += " and (b.lastModifyTime >= DATEADD(day, -" + value[0] + ", CAST(GETDATE() AS date)) )"; |
7204e3
|
777 |
} |
d32c21
|
778 |
//TODO 后期增加处理联系,未联系功能 |
F |
779 |
|
|
780 |
//处理是查询我的线索还是所有线索 |
|
781 |
//481101是公海线索,481104是私海线索 |
|
782 |
|
|
783 |
|
7204e3
|
784 |
} |
05aec6
|
785 |
return where + " )"; |
703a8b
|
786 |
} |
F |
787 |
|
ae692b
|
788 |
public String contactUpdateSql(List<T482103Entity> contactsList,String contactType) { |
05aec6
|
789 |
if (contactsList == null || contactsList.size() == 0) { |
F |
790 |
return ""; |
|
791 |
} |
|
792 |
String sql = ""; |
|
793 |
for (T482103Entity contact : contactsList) { |
|
794 |
if (StringUtils.isNotBlank(contact.getRefCode()) && contact.getId() == null) { |
|
795 |
List<T482103Entity> temp = new ArrayList<T482103Entity>(); |
703a8b
|
796 |
temp.add(contact); |
ae692b
|
797 |
sql += contactAddSql(temp,contactType); |
703a8b
|
798 |
continue; |
F |
799 |
} |
05aec6
|
800 |
sql += "\nselect\n" + |
F |
801 |
" @main_customer_flag=" + contact.getMainCustomerFlag() + "\n" + |
|
802 |
",@id=" + contact.getId() + "\n" + |
|
803 |
",@name=" + GridUtils.prossSqlParm(contact.getName()) + "\n" + |
|
804 |
",@contact_email=" + GridUtils.prossSqlParm(contact.getEmail()) + "\n" + |
|
805 |
",@gender=" + GridUtils.prossSqlParm(contact.getGender()) + "\n" + |
|
806 |
",@post=" + GridUtils.prossSqlParm(contact.getPost()) + "\n" + |
|
807 |
",@remark=" + GridUtils.prossSqlParm(contact.getRemark()) + "\n" + |
|
808 |
",@contact_image_list =" + GridUtils.prossSqlParm(contact.getImageList()) + "\n" + |
|
809 |
",@birth=" + GridUtils.prossSqlParm(contact.getBirth()) + "\n" + |
|
810 |
",@post_grade=" + GridUtils.prossSqlParm(contact.getPostGrade()) + "\n" + |
703a8b
|
811 |
",@update_time=getdate()\n " + |
7204e3
|
812 |
" update t482103 set\n" + |
703a8b
|
813 |
"main_customer_flag=@main_customer_flag \n" + |
F |
814 |
",name=@name \n" + |
|
815 |
",email=@contact_email \n" + |
|
816 |
",gender=@gender \n" + |
|
817 |
",post=@post \n" + |
|
818 |
",remark=@remark \n" + |
|
819 |
",image_list=@contact_image_list \n" + |
|
820 |
",birth=@birth \n" + |
|
821 |
",post_grade=@post_grade \n" + |
7204e3
|
822 |
",update_time=@update_time from t482103 where id=@id \n " + |
703a8b
|
823 |
" select @last_inserted_id=@id\n" + |
05aec6
|
824 |
mediaUpdateSql(contact.getMediaList()) + |
703a8b
|
825 |
telListUpdateSql(contact.getTelList()); |
F |
826 |
|
|
827 |
} |
|
828 |
return sql; |
035491
|
829 |
} |
05aec6
|
830 |
|
F |
831 |
@Transactional(rollbackFor = Exception.class) |
035491
|
832 |
@Override |
F |
833 |
public void contactDel(Integer id) { |
05aec6
|
834 |
String sql = "declare @id int=" + id + ",@mainCustomerFlag int \n" + |
035491
|
835 |
" select @mainCustomerFlag=isnull(main_Customer_Flag,0) from t482103 where id=@id\n" + |
F |
836 |
" if isnull(@mainCustomerFlag,0)=1 \n" + |
|
837 |
" begin \n" + |
|
838 |
" raiserror('联系人已设置为主要联系人,不能删除',16,1)" + |
|
839 |
" return" + |
|
840 |
" end\n" + |
|
841 |
" delete from t482116 where refid=@id\n" + |
|
842 |
" delete from t482111 where refid=@id\n" + |
|
843 |
" delete from t482103 where id=@id"; |
|
844 |
this.doBaseExecute(sql); |
703a8b
|
845 |
} |
F |
846 |
|
|
847 |
/** |
|
848 |
* 生成联系人信息sql |
05aec6
|
849 |
* |
703a8b
|
850 |
* @param contactsList |
F |
851 |
* @return |
|
852 |
*/ |
ae692b
|
853 |
public String contactAddSql(List<T482103Entity> contactsList,String contactType) { |
05aec6
|
854 |
if (contactsList == null || contactsList.size() == 0) { |
F |
855 |
return ""; |
|
856 |
} |
|
857 |
String sql = ""; |
|
858 |
for (T482103Entity contact : contactsList) { |
|
859 |
sql += "\nselect\n" + |
|
860 |
" @main_customer_flag=" + contact.getMainCustomerFlag() + "\n" + |
|
861 |
",@refCode=@doccode\n" + |
|
862 |
",@name=" + GridUtils.prossSqlParm(contact.getName()) + "\n" + |
|
863 |
",@contact_email=" + GridUtils.prossSqlParm(contact.getEmail()) + "\n" + |
|
864 |
",@gender=" + GridUtils.prossSqlParm(contact.getGender()) + "\n" + |
|
865 |
",@post=" + GridUtils.prossSqlParm(contact.getPost()) + "\n" + |
|
866 |
",@remark=" + GridUtils.prossSqlParm(contact.getRemark()) + "\n" + |
|
867 |
",@contact_image_list =" + GridUtils.prossSqlParm(contact.getImageList()) + "\n" + |
|
868 |
",@birth=" + GridUtils.prossSqlParm(contact.getBirth()) + "\n" + |
|
869 |
",@post_grade=" + GridUtils.prossSqlParm(contact.getPostGrade()) + "\n" + |
|
870 |
",@create_time =getdate() \n" + |
|
871 |
",@update_time=getdate()\n " + |
|
872 |
" insert into t482103(" + |
|
873 |
"main_customer_flag \n" + |
|
874 |
",refCode \n" + |
|
875 |
",name \n" + |
|
876 |
",email \n" + |
|
877 |
",gender \n" + |
|
878 |
",post \n" + |
|
879 |
",remark \n" + |
|
880 |
",image_list \n" + |
|
881 |
",birth \n" + |
d32c21
|
882 |
",contactType \n" + |
05aec6
|
883 |
",post_grade \n" + |
F |
884 |
",create_time \n" + |
|
885 |
",update_time " + |
|
886 |
")values(" + |
|
887 |
"@main_customer_flag \n" + |
|
888 |
",@refCode \n" + |
|
889 |
",@name \n" + |
|
890 |
",@contact_email \n" + |
|
891 |
",@gender \n" + |
|
892 |
",@post \n" + |
|
893 |
",@remark \n" + |
|
894 |
",@contact_image_list \n" + |
|
895 |
",@birth \n" + |
ae692b
|
896 |
",'"+contactType+"' \n" + |
05aec6
|
897 |
",@post_grade \n" + |
F |
898 |
",@create_time \n" + |
|
899 |
",@update_time " + |
|
900 |
")\n" + |
|
901 |
" SELECT @last_inserted_id=IDENT_CURRENT('t482103')\n" + |
|
902 |
mediaAddSql(contact.getMediaList()) + |
|
903 |
telListAddSql(contact.getTelList()); |
703a8b
|
904 |
} |
F |
905 |
return sql; |
|
906 |
} |
05aec6
|
907 |
|
703a8b
|
908 |
/** |
F |
909 |
* 生成社交平台信息sql |
05aec6
|
910 |
* |
703a8b
|
911 |
* @param mediaEntityList |
F |
912 |
* @return |
|
913 |
*/ |
7204e3
|
914 |
private String mediaAddSql(List<T482111Entity> mediaEntityList) { |
05aec6
|
915 |
if (mediaEntityList == null || mediaEntityList.size() == 0) { |
F |
916 |
return ""; |
|
917 |
} |
|
918 |
String sql = ""; |
|
919 |
for (T482111Entity contact : mediaEntityList) { |
|
920 |
sql += "\nselect\n" + |
|
921 |
" @media_type=" + GridUtils.prossSqlParm(contact.getType()) + "\n" + |
|
922 |
",@media_value=" + GridUtils.prossSqlParm(contact.getValue()) + "\n" + |
|
923 |
",@media_id=" + contact.getId() + "\n" + |
703a8b
|
924 |
",@media_refid=@last_inserted_id\n" + |
7204e3
|
925 |
" insert into t482111(" + |
703a8b
|
926 |
"type \n" + |
F |
927 |
",value,refid \n" + |
|
928 |
")values(" + |
|
929 |
"@media_type \n" + |
|
930 |
",@media_value \n" + |
|
931 |
",@media_refid \n" + |
|
932 |
")\n"; |
|
933 |
} |
|
934 |
return sql; |
|
935 |
} |
05aec6
|
936 |
|
703a8b
|
937 |
/** |
F |
938 |
* 修改社交平台信息sql |
05aec6
|
939 |
* |
703a8b
|
940 |
* @param mediaEntityList |
F |
941 |
* @return |
|
942 |
*/ |
7204e3
|
943 |
private String mediaUpdateSql(List<T482111Entity> mediaEntityList) { |
05aec6
|
944 |
if (mediaEntityList == null || mediaEntityList.size() == 0) { |
F |
945 |
return ""; |
035491
|
946 |
} |
05aec6
|
947 |
String sql = ""; |
F |
948 |
//增加删除,保证最新 |
|
949 |
String collectIds = mediaEntityList.stream().filter(x -> x.getId() != null && x.getId() != 0).map(x -> x.getId() + "").collect(Collectors.joining(",")); |
|
950 |
if (collectIds.equalsIgnoreCase("")) { |
|
951 |
//没有修改,都是新增情况 |
|
952 |
sql = " delete from t482111 \n"; |
|
953 |
} else { |
|
954 |
sql = " delete from t482111 where id not in(" + collectIds + ")\n"; |
|
955 |
} |
|
956 |
for (T482111Entity mediaEntity : mediaEntityList) { |
|
957 |
if (mediaEntity.getId() == null) { |
|
958 |
List<T482111Entity> temp = new ArrayList<>(); |
703a8b
|
959 |
temp.add(mediaEntity); |
05aec6
|
960 |
sql += mediaAddSql(temp); |
703a8b
|
961 |
continue; |
F |
962 |
} |
05aec6
|
963 |
sql += "\nselect\n" + |
F |
964 |
" @media_type=" + GridUtils.prossSqlParm(mediaEntity.getType()) + "\n" + |
|
965 |
",@media_value=" + GridUtils.prossSqlParm(mediaEntity.getValue()) + "\n" + |
|
966 |
",@media_id=" + mediaEntity.getId() + "\n" + |
7204e3
|
967 |
" update t482111 set " + |
703a8b
|
968 |
"type=@media_type \n" + |
05aec6
|
969 |
",value=@media_value from t482111 where id=@media_id \n"; |
703a8b
|
970 |
} |
035491
|
971 |
|
F |
972 |
|
703a8b
|
973 |
return sql; |
F |
974 |
} |
05aec6
|
975 |
|
703a8b
|
976 |
/** |
F |
977 |
* 生成电话列表信息sql |
05aec6
|
978 |
* |
703a8b
|
979 |
* @param telListEntities |
F |
980 |
* @return |
|
981 |
*/ |
7204e3
|
982 |
private String telListAddSql(List<T482116Entity> telListEntities) { |
05aec6
|
983 |
if (telListEntities == null || telListEntities.size() == 0) { |
F |
984 |
return ""; |
|
985 |
} |
|
986 |
String sql = ""; |
|
987 |
for (T482116Entity contact : telListEntities) { |
|
988 |
sql += "\nselect\n" + |
|
989 |
" @areaCode=" + GridUtils.prossSqlParm(contact.getAreaCode()) + "\n" + |
|
990 |
",@telList_tel=" + GridUtils.prossSqlParm(contact.getTel()) + "\n" + |
|
991 |
",@telList_id=" + contact.getId() + "\n" + |
703a8b
|
992 |
",@telList_refid=@last_inserted_id\n" + |
7204e3
|
993 |
" insert into t482116(" + |
703a8b
|
994 |
"areaCode \n" + |
F |
995 |
",tel ,refid \n" + |
|
996 |
")values(" + |
|
997 |
"@areaCode \n" + |
|
998 |
",@telList_tel \n" + |
|
999 |
",@telList_refid \n" + |
|
1000 |
")\n"; |
|
1001 |
} |
|
1002 |
return sql; |
|
1003 |
} |
05aec6
|
1004 |
|
703a8b
|
1005 |
/** |
F |
1006 |
* 生成电话列表信息sql |
05aec6
|
1007 |
* |
703a8b
|
1008 |
* @param telListEntities |
F |
1009 |
* @return |
|
1010 |
*/ |
7204e3
|
1011 |
private String telListUpdateSql(List<T482116Entity> telListEntities) { |
05aec6
|
1012 |
if (telListEntities == null || telListEntities.size() == 0) { |
F |
1013 |
return ""; |
|
1014 |
} |
|
1015 |
String sql = ""; |
035491
|
1016 |
//增加删除,保证最新 |
05aec6
|
1017 |
String collectIds = telListEntities.stream().filter(x -> x.getId() != null && x.getId() != 0).map(x -> x.getId() + "").collect(Collectors.joining(",")); |
F |
1018 |
if (collectIds.equalsIgnoreCase("")) { |
|
1019 |
//没有修改,都是新增情况 |
|
1020 |
sql = " delete from t482116 \n"; |
|
1021 |
} else { |
|
1022 |
sql = " delete from t482116 where id not in(" + collectIds + ")\n"; |
|
1023 |
} |
|
1024 |
for (T482116Entity telListEntity : telListEntities) { |
|
1025 |
if (telListEntity.getId() == null) { |
|
1026 |
List<T482116Entity> temp = new ArrayList<>(); |
703a8b
|
1027 |
temp.add(telListEntity); |
05aec6
|
1028 |
sql += telListAddSql(temp); |
703a8b
|
1029 |
continue; |
F |
1030 |
} |
05aec6
|
1031 |
sql += "\nselect\n" + |
F |
1032 |
" @areaCode=" + GridUtils.prossSqlParm(telListEntity.getAreaCode()) + "\n" + |
|
1033 |
",@telList_tel=" + GridUtils.prossSqlParm(telListEntity.getTel()) + "\n" + |
703a8b
|
1034 |
",@telList_refid=@last_inserted_id\n" + |
05aec6
|
1035 |
",@telList_id=" + telListEntity.getId() + "\n" + |
7204e3
|
1036 |
" update t482116 set " + |
703a8b
|
1037 |
"areaCode=@areaCode \n" + |
F |
1038 |
",tel=@telList_tel \n" + |
035491
|
1039 |
" from t482116 where id=@telList_id\n"; |
703a8b
|
1040 |
} |
F |
1041 |
return sql; |
|
1042 |
} |
|
1043 |
} |