From 7204e3dff0490732e861ccd1338e3e3c31d768c6 Mon Sep 17 00:00:00 2001
From: fs-danaus <danaus314@qq.com>
Date: 星期五, 09 八月 2024 11:14:47 +0800
Subject: [PATCH] crm线索列表接口及代码整理
---
src/com/yc/crm/clues/service/CluesServiceImpl.java | 252 +++++++++++++++++++++++++++++++++++++++++--------
1 files changed, 208 insertions(+), 44 deletions(-)
diff --git a/src/com/yc/crm/clues/service/CluesServiceImpl.java b/src/com/yc/crm/clues/service/CluesServiceImpl.java
index 9390218..453637f 100644
--- a/src/com/yc/crm/clues/service/CluesServiceImpl.java
+++ b/src/com/yc/crm/clues/service/CluesServiceImpl.java
@@ -1,6 +1,7 @@
package com.yc.crm.clues.service;
import com.yc.action.grid.GridUtils;
+import com.yc.crm.base.entity.Response;
import com.yc.crm.clues.entity.*;
import com.yc.service.BaseService;
import org.apache.commons.lang3.StringUtils;
@@ -9,10 +10,9 @@
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
-import java.util.HashMap;
import java.util.List;
import java.util.Map;
-import java.util.stream.Collectors;
+import java.util.Set;
@Service
public class CluesServiceImpl extends BaseService implements CluesServiceIfc{
@@ -117,7 +117,7 @@
"@PostDate=getdate() , \n" +
"@CreateUsercode="+ GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) +", \n" +
"@CreateUserName="+ GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) +" , \n" +
- "@isRead =0, \n" +
+ "@isRead ="+t481101Entity.getIsRead()+", \n" +//TODO 鑷繁鏂板缓锛宨sRead=1,鍏朵粬浜�
"@Email=null, \n" +
"@Country ="+ GridUtils.prossSqlParm(t481101Entity.getCountry()) +", \n" +
"@Address ="+ GridUtils.prossSqlParm(t481101Entity.getAddress()) +", \n" +
@@ -277,34 +277,198 @@
contactUpdateSql(t481101Entity.getContactsList()) +
"end\n" +
"";
- System.out.println("clues:"+sql);
+ //System.out.println("clues:"+sql);
this.doBaseExecute(sql);
}
+
+
@Override
- public Map getBaseInfo() {
- HashMap map = new HashMap();
- List<SysdictEntity> list = this.jdbcTemplate.query("select dictid,interValue as id,dictvalue as name from _sysdict where dictid in(-48013000,-48013001,-48013002,-48013003,-48013004,-48013005,-48013006,-48013007,-48013008,-48013009)\n" +
- "order by dictid desc,sequence\n ", new BeanPropertyRowMapper<>(SysdictEntity.class));
- map.put("鍏徃绫诲瀷",list.stream().filter(x->x.getDictid()==-48013000).collect(Collectors.toList()));
- map.put("閲囪喘鎰忓悜",list.stream().filter(x->x.getDictid()==-48013001).collect(Collectors.toList()));
- map.put("骞撮噰璐",list.stream().filter(x->x.getDictid()==-48013002).collect(Collectors.toList()));
- map.put("鏃跺尯",list.stream().filter(x->x.getDictid()==-48013003).collect(Collectors.toList()));
- map.put("瑙勬ā",list.stream().filter(x->x.getDictid()==-48013004).collect(Collectors.toList()));
- map.put("璁块棶鏉ユ簮",list.stream().filter(x->x.getDictid()==-48013005).collect(Collectors.toList()));
- map.put("璁块棶IP鎵�鍦ㄥ湴",list.stream().filter(x->x.getDictid()==-48013006).collect(Collectors.toList()));
- map.put("绀句氦骞冲彴",list.stream().filter(x->x.getDictid()==-48013007).collect(Collectors.toList()));
- map.put("鐢佃瘽鍖哄彿",list.stream().filter(x->x.getDictid()==-48013008).collect(Collectors.toList()));
- map.put("鑱岀骇",list.stream().filter(x->x.getDictid()==-48013009).collect(Collectors.toList()));
- return map;
+ public Response getAllClues(CluesRequestEntity request) {
+ String fileds="" +
+ "a.DocCode \n" +
+ ",a.FormID \n" +
+ ",a.DocStatus \n" +
+ ",a.DocStatusName \n" +
+ ",a.companyid \n" +
+ ",a.companyname \n" +
+ ",a.EnterCode \n" +
+ ",a.EnterName \n" +
+ ",a.EnterDate \n" +
+ ",a.ModifyName \n" +
+ ",a.ModifyDate \n" +
+ ",a.PostName \n" +
+ ",a.PostDate \n" +
+ ",a.CreateUsercode \n" +
+ ",a.CreateUserName \n" +
+ ",a.Country \n" +
+ ",a.HDMemo \n" +
+ ",a.clues_name \n" +
+ ",a.archive_time \n" +
+ ",a.order_time \n" +
+ ",a.lost_day_count \n" +
+ ",a.transfer_count \n" +
+ ",a.release_count \n" +
+ ",a.is_archive \n" +
+ ",a.archive_type \n" +
+ ",a.status_id \n" +
+ ",a.status \n" +
+ ",a.follow_up_time \n" +
+ ",a.fail_type \n" +
+ ",a.fail_status \n" +
+ ",a.fail_status_name \n" +
+ ",a.fail_reason \n" +
+ ",a.read_flag \n" +
+ ",a.last_edit_user \n" +
+ ",a.edit_time \n" +
+ ",a.private_time \n" +
+ ",a.public_time \n" +
+ ",a.is_public \n" +
+ ",a.store_id \n" +
+ ",a.star \n" +
+ ",a.transform_task_status \n" +
+ ",a.next_follow_up_time \n" +
+ ",a.duplicate_flag \n" +
+ ",a.tagList \n" +
+ ",a.homepage \n" +
+ ",a.origin_list \n" +
+ ",a.short_name \n" +
+ ",a.corporate_name \n" +
+ ",a.biz_type \n" +
+ ",a.tel_area_code \n" +
+ ",a.tel \n" +
+ ",a.intention_level \n" +
+ ",a.annual_procurement \n" +
+ ",a.timezone \n" +
+ ",a.ad_keyword \n" +
+ ",a.image_list \n" +
+ ",a.scale_id \n" +
+ ",a.seller \n" +
+ ",a.inquiry_origin \n" +
+ ",a.category_ids \n" +
+ ",a.inquiry_country \n" +
+ ",b.id \n" +
+ ",b.refCode \n" +
+ ",b.ownerCode \n" +
+ ",b.ownerName \n" +
+ ",b.enterTime \n" +
+ ",b.exitTime \n" +
+ ",b.isRead as hasRead \n" +
+ ",b.reasonForRollback \n" +
+ ",b.costs \n" +
+ ",b.pinFlag as hasPinFlag \n" +
+ ",b.firstFllowUpTime \n" +
+ ",b.lastModifyTime \n" +
+ ",b.daysNotContacted " +
+ ",@TotalRowCount as totalRowCount ,@pageCount as pageCount \n";
+ String where=" 1=1 ";
+ if(request.getSearchTxt()!=null&&request.getSearchTxt().size()>0) {
+ //鎷兼帴鏌ヨ鏉′欢
+ where+= getSearchInfo(request);
+ }
+ String sql="set nocount on ; \n" +
+// " declare @key varchar(300) \n" +
+// " select @key ="+1+" \n" +
+ " declare @Limit int , @Page int ,@StartRowNo int ,@EndRowNo int ; \n" +
+ " select @Limit = "+request.getPageSize()+" , @Page = "+request.getPageNo()+" ; \n" +
+ " declare @TotalRowCount int ,@pageCount int; \n" +
+ " select @StartRowNo = (isnull(@Page,0) - 1) * isnull(@Limit,0) + 1 ; \n" +
+ " select @EndRowNo = isnull(@Page,0) * isnull(@Limit,0) ; \n" +
+ " select @TotalRowCount =count(1),@pageCount=CEILING((COUNT(1)+0.0)/" + request.getPageSize() + ") from t481101h a left join t481104h b on a.docCode=b.refCode where "+where+" \n" +
+ " SELECT * FROM ( \n" +
+ " select top 100 percent ROW_NUMBER() OVER (ORDER BY docCode) AS NO,\n" +
+ fileds+" from t481101h a left join t481104h b on a.docCode=b.refCode\n" +
+ " where " +where+
+ " order by a.docCode asc \n" +
+ " ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo";
+ List<T481104Entity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T481104Entity.class));
+ //澧炲姞鑱旂郴浜鸿緭鍑�
+ if(list!=null&&list.size()>0){
+ list.stream().forEach(x->{
+ List<T482103Entity> query = this.jdbcTemplate.query("select main_customer_flag\n" +
+ ",refCode,id \n" +
+ ",name \n" +
+ ",email \n" +
+ ",gender \n" +
+ ",post \n" +
+ ",remark \n" +
+ ",image_list \n" +
+ ",birth \n" +
+ ",post_grade \n" +
+ ",growth_level \n" +
+ ",create_time \n" +
+ ",update_time \n" +
+ ",DocVersion \n from t482103 where refCode=" + GridUtils.prossSqlParm(x.getDocCode()), new BeanPropertyRowMapper<>(T482103Entity.class));
+ if(query!=null&&query.size()>0){
+ query.stream().forEach(y->{
+ //--绀句氦骞冲彴
+ List<T482111Entity> mediaList = this.jdbcTemplate.query("select \n" +
+ "refId,id \n" +
+ ",value \n" +
+ ",type from t482111 where refId=" +y.getId(), new BeanPropertyRowMapper<>(T482111Entity.class));
+ y.setMediaList(mediaList);
+ //鐢佃瘽鍒楄〃
+ List<T482116Entity> telList = this.jdbcTemplate.query("select " +
+ "refId,id,areaCode,tel from t482116 where refId=" + y.getId(), new BeanPropertyRowMapper<>(T482116Entity.class));
+ y.setTelList(telList);
+ });
+ }
+ x.setContactsList(query);
+ });
+ }
+ Response response=new Response();
+ response.setTotalCount((list!=null&&list.size()>0)?list.get(0).getTotalRowCount():0);
+ response.setPageCount((list!=null&&list.size()>0)?list.get(0).getPageCount():0);
+ response.setData(list);
+ return response;
+ }
+ @Transactional(rollbackFor = Exception.class)
+ @Override
+ public void assign(AssignCluesEntity assignClues) {
+ String sql=" declare @refCode varchar(20),@ownerCode varchar(20),@ownerName varchar(20)\n" +
+ "select @refCode=" +GridUtils.prossSqlParm(assignClues.getDocCode())+",@ownerCode=" +GridUtils.prossSqlParm(assignClues.getOwnerCode())+",@ownerName=" +GridUtils.prossSqlParm(assignClues.getOwnerName())+"\n"+
+ " insert into t481104h(refCode \n" +
+ ",ownerCode \n" +
+ ",ownerName \n" +
+ ",enterTime \n)values(@refCode,@ownerCode,@ownerName,getdate())";
+ this.doBaseExecute(sql);
+ }
+ /**
+ * 鎷兼帴鏌ヨ鏉′欢
+ * @param request
+ * @return
+ */
+ private String getSearchInfo(CluesRequestEntity request) {
+ String where=" and(1=1 ";
+ Set<Map.Entry<String, String>> entries = request.getSearchTxt().entrySet();
+ for (Map.Entry<String, String> entry : entries) {
+ if(entry.getKey().equalsIgnoreCase("cluesName")){
+ where+=" and a.clues_name like '%"+entry.getValue()+"%'";
+ }
+ if(entry.getKey().equalsIgnoreCase("isRead")){
+ where+=" and isnull(b.isRead,0)="+entry.getValue();
+ }
+ if(entry.getKey().equalsIgnoreCase("pinFlag")){
+ where+=" and isnull(b.pinFlag,0)="+entry.getValue();
+ }
+ if(entry.getKey().equalsIgnoreCase("ownerCode")){
+ where+=" and b.ownerCode = '%"+entry.getValue()+"%'";
+ }
+ if(entry.getKey().equalsIgnoreCase("lastModifyTime")){
+ String[] value=entry.getValue().split(";");
+
+ where+=" and (b.lastModifyTime >= DATEADD(day, -"+value[0]+", CAST(GETDATE() AS date)) )";//TODO 鍚庢湡澧炲姞澶勭悊鑱旂郴锛屾湭鑱旂郴鍔熻兘
+ }
+ }
+ return where+" )";
}
- private String contactUpdateSql(List<CrmContactsEntity> contactsList) {
+ private String contactUpdateSql(List<T482103Entity> contactsList) {
if(contactsList==null||contactsList.size()==0){ return "";}
String sql="";
- for(CrmContactsEntity contact:contactsList){
+ for(T482103Entity contact:contactsList){
if(StringUtils.isNotBlank(contact.getRefCode())&&contact.getId()==null){
- List<CrmContactsEntity> temp=new ArrayList<CrmContactsEntity>();
+ List<T482103Entity> temp=new ArrayList<T482103Entity>();
temp.add(contact);
sql+= contactAddSql(temp);
continue;
@@ -321,7 +485,7 @@
",@birth="+GridUtils.prossSqlParm(contact.getBirth())+"\n" +
",@post_grade="+GridUtils.prossSqlParm(contact.getPostGrade())+"\n" +
",@update_time=getdate()\n " +
- " update crm_contacts set\n" +
+ " update t482103 set\n" +
"main_customer_flag=@main_customer_flag \n" +
",name=@name \n" +
",email=@contact_email \n" +
@@ -331,7 +495,7 @@
",image_list=@contact_image_list \n" +
",birth=@birth \n" +
",post_grade=@post_grade \n" +
- ",update_time=@update_time from crm_contacts where id=@id \n " +
+ ",update_time=@update_time from t482103 where id=@id \n " +
" select @last_inserted_id=@id\n" +
mediaUpdateSql(contact.getMediaList())+
telListUpdateSql(contact.getTelList());
@@ -345,10 +509,10 @@
* @param contactsList
* @return
*/
- private String contactAddSql(List<CrmContactsEntity> contactsList) {
+ private String contactAddSql(List<T482103Entity> contactsList) {
if(contactsList==null||contactsList.size()==0){ return "";}
String sql="" ;
- for(CrmContactsEntity contact:contactsList){
+ for(T482103Entity contact:contactsList){
sql+="\nselect\n" +
" @main_customer_flag="+contact.getMainCustomerFlag()+"\n" +
",@refCode=@doccode\n" +
@@ -362,7 +526,7 @@
",@post_grade="+GridUtils.prossSqlParm(contact.getPostGrade())+"\n" +
",@create_time =getdate() \n" +
",@update_time=getdate()\n " +
- " insert into crm_contacts(" +
+ " insert into t482103(" +
"main_customer_flag \n" +
",refCode \n" +
",name \n" +
@@ -389,7 +553,7 @@
",@create_time \n" +
",@update_time " +
")\n" +
- " SELECT @last_inserted_id=IDENT_CURRENT('crm_contacts')\n"+
+ " SELECT @last_inserted_id=IDENT_CURRENT('t482103')\n"+
mediaAddSql(contact.getMediaList())+
telListAddSql(contact.getTelList())
;
@@ -401,15 +565,15 @@
* @param mediaEntityList
* @return
*/
- private String mediaAddSql(List<CrmMediaEntity> mediaEntityList) {
+ private String mediaAddSql(List<T482111Entity> mediaEntityList) {
if(mediaEntityList==null||mediaEntityList.size()==0){ return "";}
String sql="";
- for(CrmMediaEntity contact:mediaEntityList){
+ for(T482111Entity contact:mediaEntityList){
sql+="\nselect\n" +
" @media_type="+GridUtils.prossSqlParm(contact.getType())+"\n" +
",@media_value="+GridUtils.prossSqlParm(contact.getValue())+"\n" +
",@media_refid=@last_inserted_id\n" +
- " insert into crm_media(" +
+ " insert into t482111(" +
"type \n" +
",value,refid \n" +
")values(" +
@@ -425,12 +589,12 @@
* @param mediaEntityList
* @return
*/
- private String mediaUpdateSql(List<CrmMediaEntity> mediaEntityList) {
+ private String mediaUpdateSql(List<T482111Entity> mediaEntityList) {
if(mediaEntityList==null||mediaEntityList.size()==0){ return "";}
String sql="";
- for(CrmMediaEntity mediaEntity:mediaEntityList){
+ for(T482111Entity mediaEntity:mediaEntityList){
if(mediaEntity.getRefId()==null){
- List<CrmMediaEntity> temp=new ArrayList<>();
+ List<T482111Entity> temp=new ArrayList<>();
temp.add(mediaEntity);
sql+=mediaAddSql(temp);
continue;
@@ -438,9 +602,9 @@
sql+="\nselect\n" +
" @media_type="+GridUtils.prossSqlParm(mediaEntity.getType())+"\n" +
",@media_value="+GridUtils.prossSqlParm(mediaEntity.getValue())+"\n" +
- " update crm_media set " +
+ " update t482111 set " +
"type=@media_type \n" +
- ",value=@media_value from crm_media where refid=@last_inserted_id \n" ;
+ ",value=@media_value from t482111 where refid=@last_inserted_id \n" ;
}
return sql;
}
@@ -449,15 +613,15 @@
* @param telListEntities
* @return
*/
- private String telListAddSql(List<CrmTelListEntity> telListEntities) {
+ private String telListAddSql(List<T482116Entity> telListEntities) {
if(telListEntities==null||telListEntities.size()==0){ return "";}
String sql="";
- for(CrmTelListEntity contact:telListEntities){
+ for(T482116Entity contact:telListEntities){
sql+="\nselect\n" +
" @areaCode="+GridUtils.prossSqlParm(contact.getAreaCode())+"\n" +
",@telList_tel="+GridUtils.prossSqlParm(contact.getTel())+"\n" +
",@telList_refid=@last_inserted_id\n" +
- " insert into crm_tellist(" +
+ " insert into t482116(" +
"areaCode \n" +
",tel ,refid \n" +
")values(" +
@@ -473,12 +637,12 @@
* @param telListEntities
* @return
*/
- private String telListUpdateSql(List<CrmTelListEntity> telListEntities) {
+ private String telListUpdateSql(List<T482116Entity> telListEntities) {
if(telListEntities==null||telListEntities.size()==0){ return "";}
String sql="";
- for(CrmTelListEntity telListEntity:telListEntities){
+ for(T482116Entity telListEntity:telListEntities){
if(telListEntity.getRefId()==null){
- List<CrmTelListEntity> temp=new ArrayList<>();
+ List<T482116Entity> temp=new ArrayList<>();
temp.add(telListEntity);
sql+=telListAddSql(temp);
continue;
@@ -487,10 +651,10 @@
" @areaCode="+GridUtils.prossSqlParm(telListEntity.getAreaCode())+"\n" +
",@telList_tel="+GridUtils.prossSqlParm(telListEntity.getTel())+"\n" +
",@telList_refid=@last_inserted_id\n" +
- " update crm_tellist set " +
+ " update t482116 set " +
"areaCode=@areaCode \n" +
",tel=@telList_tel \n" +
- " from crm_tellist where refid=@telList_refid\n";
+ " from t482116 where refid=@telList_refid\n";
}
return sql;
}
--
Gitblit v1.8.0