crm线索列表接口及代码整理

fs-danaus

2024-08-09 7204e3dff0490732e861ccd1338e3e3c31d768c6

 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 自己新建，isRead=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;
    }