| | |
| | | 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; |
| | |
| | | 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{ |
| | |
| | | "@PostDate=getdate() , \n" + |
| | | "@CreateUsercode="+ GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) +", \n" + |
| | | "@CreateUserName="+ GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) +" , \n" + |
| | | "@isRead =0, \n" + |
| | | "@isRead ="+t481101Entity.getIsRead()+", \n" +//TODO 自己新建,isRead=1,其他人 |
| | | "@Email=null, \n" + |
| | | "@Country ="+ GridUtils.prossSqlParm(t481101Entity.getCountry()) +", \n" + |
| | | "@Address ="+ GridUtils.prossSqlParm(t481101Entity.getAddress()) +", \n" + |
| | |
| | | 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; |
| | |
| | | ",@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" + |
| | |
| | | ",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()); |
| | |
| | | * @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" + |
| | |
| | | ",@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" + |
| | |
| | | ",@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()) |
| | | ; |
| | |
| | | * @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(" + |
| | |
| | | * @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; |
| | |
| | | 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; |
| | | } |
| | |
| | | * @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(" + |
| | |
| | | * @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; |
| | |
| | | " @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; |
| | | } |