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 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 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 list = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(T481104Entity.class)); //增加联系人输出 if (list != null && list.size() > 0) { list.stream().forEach(x -> { List 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 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 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 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 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 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> entries = request.getSearchTxt().entrySet(); for (Map.Entry 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 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 temp = new ArrayList(); 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 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 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 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 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 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 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 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; } }