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.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;
|
|
@Service
|
public class CluesServiceImpl extends BaseService implements CluesServiceIfc{
|
@Transactional(rollbackFor = Exception.class)
|
@Override
|
public void save(T481101Entity t481101Entity) {
|
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" +
|
" @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(100),\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_value varchar(150)\n"+
|
" \ndeclare " +
|
" @areaCode varchar(50),\n" +
|
" @telList_refid 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" +
|
"@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" +
|
"@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" +
|
",periodid" +
|
",EnterCode" +
|
",EnterName" +
|
",EnterDate" +
|
",ModifyName" +
|
",ModifyDate" +
|
",PostName" +
|
",PostDate" +
|
",CreateUsercode" +
|
",CreateUserName" +
|
",isRead" +
|
",Email" +
|
",Country " +
|
",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" +
|
",@periodid" +
|
",@EnterCode" +
|
",@EnterName" +
|
",@EnterDate" +
|
",@ModifyName" +
|
",@ModifyDate" +
|
",@PostName" +
|
",@PostDate" +
|
",@CreateUsercode" +
|
",@CreateUserName" +
|
",@isRead" +
|
",@Email" +
|
",@Country " +
|
",@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 " +
|
",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" +
|
"";
|
//System.out.println("clues:"+sql);
|
this.doBaseExecute(sql);
|
}
|
|
|
|
@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.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<T482103Entity> contactsList) {
|
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);
|
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;
|
}
|
|
/**
|
* 生成联系人信息sql
|
* @param contactsList
|
* @return
|
*/
|
private String contactAddSql(List<T482103Entity> contactsList) {
|
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" +
|
",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" +
|
",@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_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="";
|
for(T482111Entity mediaEntity:mediaEntityList){
|
if(mediaEntity.getRefId()==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" +
|
" update t482111 set " +
|
"type=@media_type \n" +
|
",value=@media_value from t482111 where refid=@last_inserted_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_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="";
|
for(T482116Entity telListEntity:telListEntities){
|
if(telListEntity.getRefId()==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" +
|
" update t482116 set " +
|
"areaCode=@areaCode \n" +
|
",tel=@telList_tel \n" +
|
" from t482116 where refid=@telList_refid\n";
|
}
|
return sql;
|
}
|
}
|
