package com.yc.crm.clues.service;
|
|
import com.yc.action.grid.GridUtils;
|
import com.yc.crm.base.entity.AuditEntity;
|
import com.yc.crm.base.entity.FileStructEntity;
|
import com.yc.crm.base.entity.Response;
|
import com.yc.crm.base.util.Utils;
|
import com.yc.crm.clues.entity.*;
|
import com.yc.exception.ApplicationException;
|
import com.yc.service.BaseService;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.jdbc.core.BeanPropertyRowMapper;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Set;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class CluesServiceImpl extends BaseService implements CluesServiceIfc {
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void save(T481101Entity t481101Entity) {
|
if (StringUtils.isBlank(t481101Entity.getCluesName())) {
|
throw new ApplicationException("线索名称不能为空");
|
}
|
String sql = " declare " +
|
" @DocCode nvarchar(16),\n" +
|
" @FormID int,\n" +
|
" @DocDate datetime,\n" +
|
" @periodid varchar(50),\n" +
|
" @DocStatus int,\n" +
|
" @DocStatusName varchar(50),\n" +
|
" @companyid nvarchar(20),\n" +
|
" @companyname varchar(50),\n" +
|
" @ccCode varchar(20),\n" +
|
" @ccName varchar(50),\n" +
|
" @EnterCode varchar(20),\n" +
|
" @EnterName varchar(50),\n" +
|
" @EnterDate datetime,\n" +
|
" @ModifyName varchar(40),\n" +
|
" @ModifyDate datetime,\n" +
|
" @modifylog varchar(200),\n" +
|
" @PostName varchar(40),\n" +
|
" @PostDate datetime,\n" +
|
" @CreateUsercode varchar(50),\n" +
|
" @CreateUserName varchar(50),\n" +
|
" @isRead int,\n" +
|
" @Email varchar(50),\n" +
|
" @Website varchar(100),\n" +
|
" @Phone varchar(50),\n" +
|
" @DoNotCall int,\n" +
|
" @MobilePhone varchar(50),\n" +
|
" @CltStatus varchar(20),\n" +
|
" @Country varchar(50),\n" +
|
" @Province varchar(50),\n" +
|
" @City varchar(50),\n" +
|
" @District varchar(50),\n" +
|
" @Street varchar(50),\n" +
|
" @Address varchar(200),\n" +
|
" @PostCode varchar(50),\n" +
|
" @HDMemo varchar(500),\n" +
|
" @clues_name nvarchar(50),\n" +
|
" @last_edit_user varchar(20),\n" +
|
" @edit_time datetime,\n" +
|
" @public_time datetime,\n" +
|
" @tagList varchar(200),\n" +
|
" @homepage varchar(50),\n" +
|
" @origin_list nvarchar(250),\n" +
|
" @short_name nvarchar(250),\n" +
|
" @corporate_name nvarchar(250),\n" +
|
" @biz_type nvarchar(20),\n" +
|
" @tel_area_code varchar(10),\n" +
|
" @tel varchar(20),\n" +
|
" @intention_level varchar(50),\n" +
|
" @annual_procurement money,\n" +
|
" @timezone varchar(10),\n" +
|
" @ad_keyword nvarchar(300),\n" +
|
" @image_list varchar(100),\n" +
|
" @scale_id varchar(100),\n" +
|
" @seller varchar(50),\n" +
|
" @inquiry_origin nvarchar(100),\n" +
|
" @category_ids nvarchar(100),\n" +
|
" @inquiry_country nvarchar(50),\n" +
|
" @pin_flag bit \n" +
|
" \ndeclare " +
|
" @main_customer_flag bit,\n" +
|
" @id bigint, \n" +
|
" @refCode varchar(50),\n" +
|
" @name nvarchar(50),\n" +
|
" @contact_email nvarchar(150),\n" +
|
" @gender nvarchar(10),\n" +
|
" @post nvarchar(50),\n" +
|
" @remark nvarchar(50),\n" +
|
" @contact_image_list nvarchar(150),\n" +
|
" @birth nvarchar(50),\n" +
|
" @post_grade nvarchar(50),\n" +
|
" @growth_level int,\n" +
|
" @create_time datetime,\n" +
|
" @update_time datetime,\n" +
|
" @last_inserted_id bigint\n" +//自增主键值
|
" \ndeclare " +
|
" @media_type varchar(50),\n" +
|
" @media_refid bigint, \n" +
|
" @media_id bigint, \n" +
|
" @media_value varchar(150)\n" +
|
" \ndeclare " +
|
" @areaCode varchar(50),\n" +
|
" @telList_refid bigint, \n" +
|
" @telList_id bigint, \n" +
|
" @telList_tel varchar(150)\n" +
|
//---赋值
|
" select " +
|
"@FormID=481101 , \n" +
|
"@DocDate=convert(datetime,convert(varchar(10),GETDATE(),120) ) , \n" +
|
"@DocCode=" + GridUtils.prossSqlParm(t481101Entity.getDocCode()) + ", \n" +
|
"@companyid=" + GridUtils.prossSqlParm(t481101Entity.getCompanyid()) + ", \n" +
|
"@companyname= " + GridUtils.prossSqlParm(t481101Entity.getCompanyname()) + ", \n" +
|
"@ccCode=" + GridUtils.prossSqlParm(t481101Entity.getCcCode()) + ", \n" +
|
"@ccName= " + GridUtils.prossSqlParm(t481101Entity.getCcName()) + ", \n" +
|
"@periodid= dbo.GetPeriodID(@FormID,@CompanyID,getdate()), \n" +
|
"@EnterCode=" + GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) + ", \n" +
|
"@EnterName=" + GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) + " , \n" +
|
"@EnterDate=getdate() , \n" +
|
"@ModifyName =" + GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) + ", \n" +
|
"@ModifyDate =getdate() , \n" +
|
"@PostName =" + GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) + ", \n" +
|
"@PostDate=getdate() , \n" +
|
"@CreateUsercode=" + GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) + ", \n" +
|
"@CreateUserName=" + GridUtils.prossSqlParm(t481101Entity.getCreateUserName()) + " , \n" +
|
"@isRead =" + t481101Entity.getIsRead() + ", \n" +//TODO 自己新建，isRead=1,其他人
|
"@Email=null, \n" +
|
"@Country =" + GridUtils.prossSqlParm(t481101Entity.getCountry()) + ", \n" +
|
"@province =" + GridUtils.prossSqlParm(t481101Entity.getProvince()) + ", \n" +
|
"@city =" + GridUtils.prossSqlParm(t481101Entity.getCity()) + ", \n" +
|
"@Address =" + GridUtils.prossSqlParm(t481101Entity.getAddress()) + ", \n" +
|
"@PostCode =" + GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) + ", \n" +
|
"@HDMemo =" + GridUtils.prossSqlParm(t481101Entity.getHdMemo()) + ", \n" +
|
"@clues_name= " + GridUtils.prossSqlParm(t481101Entity.getCluesName()) + ", \n" +
|
"@last_edit_user=" + GridUtils.prossSqlParm(t481101Entity.getCreateUsercode()) + ", \n" +
|
"@edit_time=getdate(), \n" +
|
"@public_time =getdate(), \n" +
|
"@tagList= " + GridUtils.prossSqlParm(t481101Entity.getTagList()) + ", \n" +
|
"@homepage=" + GridUtils.prossSqlParm(t481101Entity.getHomepage()) + ", \n" +
|
"@origin_list= " + GridUtils.prossSqlParm(t481101Entity.getOriginList()) + ", \n" +
|
"@short_name=" + GridUtils.prossSqlParm(t481101Entity.getShortName()) + ", \n" +
|
"@corporate_name=" + GridUtils.prossSqlParm(t481101Entity.getCorporateName()) + ", \n" +
|
"@biz_type=" + GridUtils.prossSqlParm(t481101Entity.getBizType()) + ", \n" +
|
"@tel_area_code=" + GridUtils.prossSqlParm(t481101Entity.getTelAreaCode()) + ", \n" +
|
"@tel=" + GridUtils.prossSqlParm(t481101Entity.getTel()) + ", \n" +
|
"@intention_level=" + GridUtils.prossSqlParm(t481101Entity.getIntentionLevel()) + ", \n" +
|
"@annual_procurement=" + t481101Entity.getAnnualProcurement() + ", \n" +
|
"@timezone=" + GridUtils.prossSqlParm(t481101Entity.getTimezone()) + ", \n" +
|
"@ad_keyword =" + GridUtils.prossSqlParm(t481101Entity.getAdKeyword()) + ", \n" +
|
"@image_list =" + GridUtils.prossSqlParm(t481101Entity.getImageList()) + ", \n" +
|
"@scale_id=" + GridUtils.prossSqlParm(t481101Entity.getScaleId()) + ", \n" +
|
"@seller =" + GridUtils.prossSqlParm(t481101Entity.getSeller()) + ", \n" +
|
"@inquiry_origin=" + GridUtils.prossSqlParm(t481101Entity.getInquiryOrigin()) + ", \n" +
|
"@category_ids =" + GridUtils.prossSqlParm(t481101Entity.getCategoryIds()) + ", \n" +
|
"@inquiry_country=" + GridUtils.prossSqlParm(t481101Entity.getInquiryCountry()) + ", \n" +
|
"@pin_flag=" + t481101Entity.getPinFlag() + " \n" +
|
" if isnull(@doccode,'')=''\n" +//新增
|
"begin\n" +
|
//新增则生成单号
|
" exec sp_newdoccode @formid,@EnterCode,@doccode output \n" +
|
"insert into t481101H(" +
|
"FormID" +
|
",DocDate" +
|
",DocCode" +
|
",companyid" +
|
",companyname" +
|
",ccCode" +
|
",ccName" +
|
",periodid" +
|
",EnterCode" +
|
",EnterName" +
|
",EnterDate" +
|
",ModifyName" +
|
",ModifyDate" +
|
",PostName" +
|
",PostDate" +
|
",CreateUsercode" +
|
",CreateUserName" +
|
",isRead" +
|
",Email" +
|
",Country " +
|
",province " +
|
",city " +
|
",Address" +
|
",PostCode" +
|
",HDMemo" +
|
",clues_name" +
|
",last_edit_user" +
|
",edit_time" +
|
",public_time" +
|
",tagList" +
|
",homepage" +
|
",origin_list" +
|
",short_name" +
|
",corporate_name" +
|
",biz_type" +
|
",tel_area_code" +
|
",tel" +
|
",intention_level" +
|
",annual_procurement" +
|
",timezone" +
|
",ad_keyword " +
|
",image_list " +
|
",scale_id" +
|
",seller" +
|
",inquiry_origin" +
|
",category_ids" +
|
",inquiry_country" +
|
",pin_flag ,DocStatusName" +
|
")values(" +
|
"@FormID" +
|
",@DocDate" +
|
",@DocCode" +
|
",@companyid" +
|
",@companyname" +
|
",@ccCode" +
|
",@ccName" +
|
",@periodid" +
|
",@EnterCode" +
|
",@EnterName" +
|
",@EnterDate" +
|
",@ModifyName" +
|
",@ModifyDate" +
|
",@PostName" +
|
",@PostDate" +
|
",@CreateUsercode" +
|
",@CreateUserName" +
|
",@isRead" +
|
",@Email" +
|
",@Country " +
|
",@province " +
|
",@city " +
|
",@Address" +
|
",@PostCode" +
|
",@HDMemo" +
|
",@clues_name" +
|
",@last_edit_user" +
|
",@edit_time" +
|
",@public_time" +
|
",@tagList" +
|
",@homepage" +
|
",@origin_list" +
|
",@short_name" +
|
",@corporate_name" +
|
",@biz_type" +
|
",@tel_area_code" +
|
",@tel" +
|
",@intention_level" +
|
",@annual_procurement" +
|
",@timezone" +
|
",@ad_keyword " +
|
",@image_list " +
|
",@scale_id" +
|
",@seller" +
|
",@inquiry_origin" +
|
",@category_ids" +
|
",@inquiry_country" +
|
",@pin_flag,'起草' " +
|
")\n" +
|
//----处理联系人信息
|
contactAddSql(t481101Entity.getContactsList(),"线索") +
|
"end\n" +
|
"else \n" +//-------修改
|
"begin\n" +
|
" update t481101H set " +
|
"ModifyName=@ModifyName" +
|
",ModifyDate=@ModifyDate" +
|
",Country=@Country " +
|
",province=@province " +
|
",city=@city " +
|
",Address=@Address" +
|
",HDMemo=@HDMemo" +
|
",clues_name=@clues_name" +
|
",last_edit_user=@last_edit_user" +
|
",edit_time=@edit_time" +
|
",tagList=@tagList" +
|
",homepage=@homepage" +
|
",origin_list=@origin_list" +
|
",short_name=@short_name" +
|
",corporate_name=@corporate_name" +
|
",biz_type=@biz_type" +
|
",tel_area_code=@tel_area_code" +
|
",tel=@tel" +
|
",intention_level=@intention_level" +
|
",annual_procurement=@annual_procurement" +
|
",timezone=@timezone" +
|
",ad_keyword=@ad_keyword " +
|
",image_list=@image_list " +
|
",scale_id=@scale_id" +
|
",seller=@seller" +
|
",inquiry_origin=@inquiry_origin" +
|
",category_ids=@category_ids" +
|
",inquiry_country=@inquiry_country,pin_flag=@pin_flag from t481101H where doccode=@doccode\n" +
|
//----处理联系人信息
|
contactUpdateSql(t481101Entity.getContactsList(),"线索") +
|
"end\n" +
|
//--处理审计功能
|
auditAddSql(t481101Entity);
|
//System.out.println("clues:"+sql);
|
this.doBaseExecute(sql);
|
}
|
|
private String auditAddSql(T481101Entity t481101Entity) {
|
List<AuditEntity> list = t481101Entity.getAuditRecords();
|
if (list == null || list.size() == 0) {
|
return "";
|
}
|
String sql = " declare @now datetime=getdate()\n";//方便同一时间提交到时输出成一条
|
for (AuditEntity audit : list) {
|
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";
|
}
|
return sql;
|
}
|
|
@Override
|
public Response contactList(CluesRequestEntity request) {
|
String fileds = " " +
|
"c.refCode,c.id,a.docCode,a.clues_name \n" +
|
",c.name \n" +
|
",c.email \n" +
|
",@TotalRowCount as totalRowCount ,@pageCount as pageCount \n";
|
String where = " contactType='线索' ";
|
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 t482103 c \n" +
|
" left join t481101h a on a.docCode=c.refCode\n" +
|
" left join t481104h b on a.docCode=b.refCode" +
|
" where " + where + " \n" +
|
" SELECT * FROM ( \n" +
|
" select top 100 percent ROW_NUMBER() OVER (ORDER BY a.docCode) AS NO,\n" +
|
fileds + " from t482103 c \n" +
|
" left join t481101h a on a.docCode=c.refCode\n" +
|
" left join t481104h b on a.docCode=b.refCode" +
|
" where " + where +
|
" order by a.docCode asc \n" +
|
" ) t WHERE t.NO BETWEEN @StartRowNo AND @EndRowNo";
|
List<ContactEntity> list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContactEntity.class));
|
//增加联系人输出
|
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;
|
}
|
|
@Override
|
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.address \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 " +
|
",b.systemFlag " +
|
",b.cluesStatusFlag " +
|
",@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 a.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);
|
//附件的输出
|
//处理图片，附件，生成调用url及相关信息
|
if (StringUtils.isNotBlank(x.getImageList())) {
|
FileStructEntity fileStructEntity = new FileStructEntity();
|
fileStructEntity.setDbid(request.getDbid());
|
fileStructEntity.setFormid(request.getFormId() + "");
|
fileStructEntity.setFileStr(x.getImageList());
|
x.setFiles(Utils.getFileEntities(fileStructEntity));
|
}
|
});
|
}
|
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 cluesDel(String docCode) {
|
//删除线索，需要把相关的联系人，跟进，日程，评论也删除
|
String sql="declare @docCode varchar(50)="+ GridUtils.prossSqlParm(docCode)+"\n" +
|
" exec p481101Del @docCode";
|
this.doBaseExecute(sql);
|
}
|
|
@Override
|
public void cluesFail(T481104Entity entity) {
|
String sql="declare @docCode varchar(50)="+ GridUtils.prossSqlParm(entity.getDocCode())+",\n" +
|
"@failStatus int=" +entity.getFailStatus()+",\n"+
|
"@failStatusName varchar(50)="+ GridUtils.prossSqlParm(entity.getFailStatusName())+",\n" +
|
"@failReason varchar(250)="+ GridUtils.prossSqlParm(entity.getFailReason())+"\n" +
|
"update t481101h set fail_Status=@failStatus,fail_Status_Name=@failStatusName,fail_Reason=@failReason where docCode=@docCode\n";
|
this.doBaseExecute(sql);
|
}
|
|
@Override
|
public T481101Entity get(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.address \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 " +
|
",b.systemFlag " +
|
",b.cluesStatusFlag ";
|
String where = " b.refCode= " + GridUtils.prossSqlParm(request.getDocCode());
|
|
String sql = "set nocount on ; \n" +
|
" select \n" +
|
fileds + " from t481101h a left join t481104h b on a.docCode=b.refCode\n" +
|
" where " + where;
|
T481104Entity entity = this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(T481104Entity.class));
|
//增加联系人输出
|
if (entity != null) {
|
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(entity.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);
|
});
|
}
|
entity.setContactsList(query);
|
//附件的输出
|
//处理图片，附件，生成调用url及相关信息
|
if (StringUtils.isNotBlank(entity.getImageList())) {
|
FileStructEntity fileStructEntity = new FileStructEntity();
|
fileStructEntity.setDbid(request.getDbid());
|
fileStructEntity.setFormid(request.getFormId() + "");
|
fileStructEntity.setFileStr(entity.getImageList());
|
entity.setFiles(Utils.getFileEntities(fileStructEntity));
|
}
|
}
|
return entity;
|
}
|
|
@Override
|
public void attention(CluesRequestEntity cluesRequest) {
|
String sql = "update a set a.pinFlag=" + cluesRequest.getPinFlag() + " from t481104h a where id=" + cluesRequest.getId();
|
this.doBaseExecute(sql);
|
}
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void newAssign(AssignCluesEntity assignClues) {
|
if(StringUtils.isBlank(assignClues.getOwnerCode())&&StringUtils.isBlank(assignClues.getOwnerCcCode())){
|
throw new ApplicationException("部门和用户不能都为空");
|
}
|
String sql = " declare @refCode varchar(20),@ownerCode varchar(20),@ownerName varchar(20)\n" +
|
" ,@ownerCcCode varchar(20),@ownerCcName varchar(20),@ownerType int=0" +
|
"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";
|
if(StringUtils.isNotBlank(assignClues.getOwnerCcCode())) {
|
sql+=" select @ownerType=1 \n";//表示当前线索是分配到个人，而不是部门
|
}
|
sql+=" insert into t481104h(refCode \n" +
|
",ownerCode \n" +
|
",ownerName \n" +
|
",ownerCcCode \n" +
|
",ownerCcName \n" +
|
",ownerType \n" +
|
",enterTime \n)values(@refCode,@ownerCode,@ownerName,@ownerType,getdate())";
|
this.doBaseExecute(sql);
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void assign(AssignCluesEntity assignClues,String userName) {
|
String sql = " declare @id int,@reasonForRollback nvarchar(300),@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()) + ",@reasonForRollback='线索已移出,原因："+userName+"重新分配给"+assignClues.getOwnerName()+"'\n" +
|
" select @id=id from t481104h where refCode=@refCode\n" +
|
" update t481104h set exitTime=getDate(),reasonForRollback=@reasonForRollback where id=@id\n" +
|
" insert into t481104h(refCode \n" +
|
",ownerCode \n" +
|
",ownerName \n" +
|
",enterTime \n)values(@refCode,@ownerCode,@ownerName,getdate())";
|
this.doBaseExecute(sql);
|
}
|
|
@Override
|
public void changeStatus(T481104Entity entity) {
|
String sql = " update a set a.cluesStatusFlag=" + entity.getCluesStatusFlag() + " from t481104h a where id= " + entity.getId();
|
this.doBaseExecute(sql);
|
}
|
|
/**
|
* 拼接查询条件
|
*
|
* @param request
|
* @return
|
*/
|
private String getSearchInfo(CluesRequestEntity request) {
|
//自已创建或已发布给指定人员或属于当前部门
|
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";
|
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 in (select list from getinstr( '" + entry.getValue() + "'))";
|
}
|
if (entry.getKey().equalsIgnoreCase("systemFlag")) {
|
where += " and isnull(b.systemFlag,0) = " + entry.getValue() + "";
|
}
|
if (entry.getKey().equalsIgnoreCase("cluesStatusFlag")) {
|
where += " and isnull(b.cluesStatusFlag,0) = " + entry.getValue() + "";
|
}
|
if (entry.getKey().equalsIgnoreCase("tagList")) {
|
where += " and a.tag_List like '%" + entry.getValue() + "%'";
|
}
|
if (entry.getKey().equalsIgnoreCase("failStatus")) {
|
where += " and isnull(a.failStatus,0) = " + entry.getValue() + "";
|
}
|
if (entry.getKey().equalsIgnoreCase("cluesStatusFlag")) {
|
where += " and isnull(b.cluesStatusFlag,0) = " + entry.getValue() + "";
|
}
|
if (entry.getKey().equalsIgnoreCase("originList")) {
|
where += " and a.originList like '%" + entry.getValue() + "%'";
|
}
|
if (entry.getKey().equalsIgnoreCase("createUsercode")) {
|
where += " and a.createUsercode in (select list from getinstr( '" + entry.getValue() + "'))";
|
}
|
if (entry.getKey().equalsIgnoreCase("country")) {
|
where += " and a.country = '" + 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 后期增加处理联系，未联系功能
|
|
//处理是查询我的线索还是所有线索
|
//481101是公海线索，481104是私海线索
|
|
|
}
|
return where + " )";
|
}
|
|
public String contactUpdateSql(List<T482103Entity> contactsList,String contactType) {
|
if (contactsList == null || contactsList.size() == 0) {
|
return "";
|
}
|
String sql = "";
|
for (T482103Entity contact : contactsList) {
|
if (StringUtils.isNotBlank(contact.getRefCode()) && contact.getId() == null) {
|
List<T482103Entity> temp = new ArrayList<T482103Entity>();
|
temp.add(contact);
|
sql += contactAddSql(temp,contactType);
|
continue;
|
}
|
sql += "\nselect\n" +
|
" @main_customer_flag=" + contact.getMainCustomerFlag() + "\n" +
|
",@id=" + contact.getId() + "\n" +
|
",@name=" + GridUtils.prossSqlParm(contact.getName()) + "\n" +
|
",@contact_email=" + GridUtils.prossSqlParm(contact.getEmail()) + "\n" +
|
",@gender=" + GridUtils.prossSqlParm(contact.getGender()) + "\n" +
|
",@post=" + GridUtils.prossSqlParm(contact.getPost()) + "\n" +
|
",@remark=" + GridUtils.prossSqlParm(contact.getRemark()) + "\n" +
|
",@contact_image_list =" + GridUtils.prossSqlParm(contact.getImageList()) + "\n" +
|
",@birth=" + GridUtils.prossSqlParm(contact.getBirth()) + "\n" +
|
",@post_grade=" + GridUtils.prossSqlParm(contact.getPostGrade()) + "\n" +
|
",@update_time=getdate()\n " +
|
" update t482103 set\n" +
|
"main_customer_flag=@main_customer_flag \n" +
|
",name=@name \n" +
|
",email=@contact_email \n" +
|
",gender=@gender \n" +
|
",post=@post \n" +
|
",remark=@remark \n" +
|
",image_list=@contact_image_list \n" +
|
",birth=@birth \n" +
|
",post_grade=@post_grade \n" +
|
",update_time=@update_time from t482103 where id=@id \n " +
|
" select @last_inserted_id=@id\n" +
|
mediaUpdateSql(contact.getMediaList()) +
|
telListUpdateSql(contact.getTelList());
|
|
}
|
return sql;
|
}
|
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void contactDel(Integer id) {
|
String sql = "declare @id int=" + id + ",@mainCustomerFlag int \n" +
|
" select @mainCustomerFlag=isnull(main_Customer_Flag,0) from t482103 where id=@id\n" +
|
" if isnull(@mainCustomerFlag,0)=1 \n" +
|
" begin \n" +
|
" raiserror('联系人已设置为主要联系人，不能删除',16,1)" +
|
" return" +
|
" end\n" +
|
" delete from t482116 where refid=@id\n" +
|
" delete from t482111 where refid=@id\n" +
|
" delete from t482103 where id=@id";
|
this.doBaseExecute(sql);
|
}
|
|
/**
|
* 生成联系人信息sql
|
*
|
* @param contactsList
|
* @return
|
*/
|
public String contactAddSql(List<T482103Entity> contactsList,String contactType) {
|
if (contactsList == null || contactsList.size() == 0) {
|
return "";
|
}
|
String sql = "";
|
for (T482103Entity contact : contactsList) {
|
sql += "\nselect\n" +
|
" @main_customer_flag=" + contact.getMainCustomerFlag() + "\n" +
|
",@refCode=@doccode\n" +
|
",@name=" + GridUtils.prossSqlParm(contact.getName()) + "\n" +
|
",@contact_email=" + GridUtils.prossSqlParm(contact.getEmail()) + "\n" +
|
",@gender=" + GridUtils.prossSqlParm(contact.getGender()) + "\n" +
|
",@post=" + GridUtils.prossSqlParm(contact.getPost()) + "\n" +
|
",@remark=" + GridUtils.prossSqlParm(contact.getRemark()) + "\n" +
|
",@contact_image_list =" + GridUtils.prossSqlParm(contact.getImageList()) + "\n" +
|
",@birth=" + GridUtils.prossSqlParm(contact.getBirth()) + "\n" +
|
",@post_grade=" + GridUtils.prossSqlParm(contact.getPostGrade()) + "\n" +
|
",@create_time =getdate() \n" +
|
",@update_time=getdate()\n " +
|
" insert into t482103(" +
|
"main_customer_flag \n" +
|
",refCode \n" +
|
",name \n" +
|
",email \n" +
|
",gender \n" +
|
",post \n" +
|
",remark \n" +
|
",image_list \n" +
|
",birth \n" +
|
",contactType \n" +
|
",post_grade \n" +
|
",create_time \n" +
|
",update_time " +
|
")values(" +
|
"@main_customer_flag \n" +
|
",@refCode \n" +
|
",@name \n" +
|
",@contact_email \n" +
|
",@gender \n" +
|
",@post \n" +
|
",@remark \n" +
|
",@contact_image_list \n" +
|
",@birth \n" +
|
",'"+contactType+"' \n" +
|
",@post_grade \n" +
|
",@create_time \n" +
|
",@update_time " +
|
")\n" +
|
" SELECT @last_inserted_id=IDENT_CURRENT('t482103')\n" +
|
mediaAddSql(contact.getMediaList()) +
|
telListAddSql(contact.getTelList());
|
}
|
return sql;
|
}
|
|
/**
|
* 生成社交平台信息sql
|
*
|
* @param mediaEntityList
|
* @return
|
*/
|
private String mediaAddSql(List<T482111Entity> mediaEntityList) {
|
if (mediaEntityList == null || mediaEntityList.size() == 0) {
|
return "";
|
}
|
String sql = "";
|
for (T482111Entity contact : mediaEntityList) {
|
sql += "\nselect\n" +
|
" @media_type=" + GridUtils.prossSqlParm(contact.getType()) + "\n" +
|
",@media_value=" + GridUtils.prossSqlParm(contact.getValue()) + "\n" +
|
",@media_id=" + contact.getId() + "\n" +
|
",@media_refid=@last_inserted_id\n" +
|
" insert into t482111(" +
|
"type \n" +
|
",value,refid \n" +
|
")values(" +
|
"@media_type \n" +
|
",@media_value \n" +
|
",@media_refid \n" +
|
")\n";
|
}
|
return sql;
|
}
|
|
/**
|
* 修改社交平台信息sql
|
*
|
* @param mediaEntityList
|
* @return
|
*/
|
private String mediaUpdateSql(List<T482111Entity> mediaEntityList) {
|
if (mediaEntityList == null || mediaEntityList.size() == 0) {
|
return "";
|
}
|
String sql = "";
|
//增加删除，保证最新
|
String collectIds = mediaEntityList.stream().filter(x -> x.getId() != null && x.getId() != 0).map(x -> x.getId() + "").collect(Collectors.joining(","));
|
if (collectIds.equalsIgnoreCase("")) {
|
//没有修改，都是新增情况
|
sql = " delete from t482111 \n";
|
} else {
|
sql = " delete from t482111 where id not in(" + collectIds + ")\n";
|
}
|
for (T482111Entity mediaEntity : mediaEntityList) {
|
if (mediaEntity.getId() == null) {
|
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" +
|
",@media_id=" + mediaEntity.getId() + "\n" +
|
" update t482111 set " +
|
"type=@media_type \n" +
|
",value=@media_value from t482111 where id=@media_id \n";
|
}
|
|
|
return sql;
|
}
|
|
/**
|
* 生成电话列表信息sql
|
*
|
* @param telListEntities
|
* @return
|
*/
|
private String telListAddSql(List<T482116Entity> telListEntities) {
|
if (telListEntities == null || telListEntities.size() == 0) {
|
return "";
|
}
|
String sql = "";
|
for (T482116Entity contact : telListEntities) {
|
sql += "\nselect\n" +
|
" @areaCode=" + GridUtils.prossSqlParm(contact.getAreaCode()) + "\n" +
|
",@telList_tel=" + GridUtils.prossSqlParm(contact.getTel()) + "\n" +
|
",@telList_id=" + contact.getId() + "\n" +
|
",@telList_refid=@last_inserted_id\n" +
|
" insert into t482116(" +
|
"areaCode \n" +
|
",tel ,refid \n" +
|
")values(" +
|
"@areaCode \n" +
|
",@telList_tel \n" +
|
",@telList_refid \n" +
|
")\n";
|
}
|
return sql;
|
}
|
|
/**
|
* 生成电话列表信息sql
|
*
|
* @param telListEntities
|
* @return
|
*/
|
private String telListUpdateSql(List<T482116Entity> telListEntities) {
|
if (telListEntities == null || telListEntities.size() == 0) {
|
return "";
|
}
|
String sql = "";
|
//增加删除，保证最新
|
String collectIds = telListEntities.stream().filter(x -> x.getId() != null && x.getId() != 0).map(x -> x.getId() + "").collect(Collectors.joining(","));
|
if (collectIds.equalsIgnoreCase("")) {
|
//没有修改，都是新增情况
|
sql = " delete from t482116 \n";
|
} else {
|
sql = " delete from t482116 where id not in(" + collectIds + ")\n";
|
}
|
for (T482116Entity telListEntity : telListEntities) {
|
if (telListEntity.getId() == null) {
|
List<T482116Entity> temp = new ArrayList<>();
|
temp.add(telListEntity);
|
sql += telListAddSql(temp);
|
continue;
|
}
|
sql += "\nselect\n" +
|
" @areaCode=" + GridUtils.prossSqlParm(telListEntity.getAreaCode()) + "\n" +
|
",@telList_tel=" + GridUtils.prossSqlParm(telListEntity.getTel()) + "\n" +
|
",@telList_refid=@last_inserted_id\n" +
|
",@telList_id=" + telListEntity.getId() + "\n" +
|
" update t482116 set " +
|
"areaCode=@areaCode \n" +
|
",tel=@telList_tel \n" +
|
" from t482116 where id=@telList_id\n";
|
}
|
return sql;
|
}
|
}
|
