CMC插入查询

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:          <Andy Shen>
-- Create date:     <2010-11-22>
-- Description:     <查询客户基础表的信息再插入到客户关系表中并且去重>
-- =============================================
ALTER PROC [dbo].[sp_SelectCustomeridInsert]
(
 @ServiceGroupId uniqueidentifier,
 @Groupid UNIQUEIDENTIFIER,
 @CreatePerson VARCHAR(50),
 @CreatePersonid UNIQUEIDENTIFIER,
 @CreateTime DATETIME,
 @ModifyPerson VARCHAR(50),
 @ModifyPersonid UNIQUEIDENTIFIER,
 @ModifyTime DATETIME,
 
 --Customer search parameters
 @TypeId UNIQUEIDENTIFIER,
    @ProjectId UNIQUEIDENTIFIER,
 @Nationality VARCHAR(100),
 @Industry VARCHAR(100),
 @CertificateType VARCHAR(100),
 @CertificateNo VARCHAR(100),
 @Birthday VARCHAR(100),
 @CurrentBirthPlace VARCHAR(100),
 @BirthPlace VARCHAR(100),
 @EducationInfo VARCHAR(100),
 @Marriage VARCHAR(100),
 @HaveChild VARCHAR(100),
 @ChildCount VARCHAR(100),
 @ChildAgeInfo VARCHAR(100),
 @LiveTogather VARCHAR(100),
 @RealEstateCount VARCHAR(100),
 @BoughtInfo VARCHAR(100),
 @LiveInfo VARCHAR(100),
 @PersonalYearIncome VARCHAR(100),
 @FamilyYearInconme VARCHAR(100),
 @WorkLevel VARCHAR(100),
 @CompanyType VARCHAR(100),
 @CityIDInCityBasic UNIQUEIDENTIFIER,
 @CityIDInProjctBasic UNIQUEIDENTIFIER

)

AS
BEGIN
 DECLARE @where VARCHAR(max),
   @groupsql varchar(2000),
   @Time datetime
   
 set @Time=getdate()
 --所在业务组或者客服组的客户组包含的客户id

  set @groupsql='select Customerid
  from tb_CustomerInGroup_Relation
  where groupid in
  (
  select CustomerObjectId
  from dbo.tb_DataAuthority_UserCustomers_Relation
  where userobjectid=
     (select relationid
   from dbo.tb_SysAuthority_UserInGroup
   where userobjectid='''+convert(varchar(36),@ServiceGroupId)+''' and ServiceGroupId='''+convert(varchar(36),@ServiceGroupId)+'''
      and ActiveStatus=1 and (objecttype=1 or objecttype=2)
   ) and ActiveStatus=1 and objecttype=1 and (IsAutoStop=0 or (IsAutoStop=1 and StartTime<='''+convert(varchar,@Time,20)+''' and EndTime>='''+convert(varchar,@Time,20)+'''))
  ) and ActiveStatus=1 '
 
  
SET @where='SELECT DISTINCT cu.Customerid
  FROM dbo.tb_Customers_Basic AS cu
    LEFT JOIN dbo.tb_Customer_CardInfo AS CC ON cu.CustomerId = CC.Customerid
    LEFT  JOIN dbo.tb_City_Basic AS ci ON ci.CityId=cu.CityId  WHERE cu.CustomerId in('+@groupsql+')'
  
    if (@TypeId<>'00000000-0000-0000-0000-000000000000')
    begin
  set @where=@where+' and cc.ChannelTypeId = ''' + CONVERT(VARCHAR(100) , @TypeId)  +''' '
   END
  if (@ProjectId<>'00000000-0000-0000-0000-000000000000' )
   begin
  set @where=@where+' and cc.EnterChannelId = '''+ CONVERT(VARCHAR(100) , @ProjectId) +''' '
   END
  if (@CityIDInProjctBasic<>'00000000-0000-0000-0000-000000000000'  )
   begin
  set @where=@where+' and cc.CityId = '''+ CONVERT(VARCHAR(100) , @CityIDInProjctBasic) +''' '
   END
   IF(@CityIDInCityBasic<>'00000000-0000-0000-0000-000000000000'  )
   begin
  set @where=@where+' and cu.Cityid = '''+ CONVERT(VARCHAR(100) , @CityIDInCityBasic) +''' '
   END
 
  if @Nationality<>'' and len(@Nationality)>0
   begin
  set @where=@where+' and cu.Nationality = '''+ @Nationality +''' '
 END
  if @CertificateType<>'' and len(@CertificateType)>0
   begin
  set @where=@where+' and cu.CertificateType = '''+ @CertificateType +''' '
 END
  if @CertificateNo<>'' and len(@CertificateNo)>0
   begin
  set @where=@where+' and cu.CertificateNo = '''+ @CertificateNo +''' '
 END
  if @Birthday<>'' and len(@Birthday)>0
   begin
  set @where=@where+' and cu.Birthday = '''+ @Birthday +''' '
 END
  if @CurrentBirthPlace<>'' and len(@CurrentBirthPlace)>0
   begin
  set @where=@where+' and cu.CurrentBirthPlace = '''+ @CurrentBirthPlace +''' '
 END
  if @BirthPlace<>'' and len(@BirthPlace)>0
   begin
  set @where=@where+' and cu.BirthPlace = '''+ @BirthPlace +''' '
 END
  if @EducationInfo<>'' and len(@EducationInfo)>0
   begin
  set @where=@where+' and cu.EducationInfo = '''+ @EducationInfo +''' '
 END 
  if @Marriage<>'' and len(@Marriage)>0
   begin
    set @where=@where+' and cu.Marriage = '''+ @Marriage +''' '
 END
  if @HaveChild<>'' and len(@HaveChild)>0
   begin
    set @where=@where+' and cu.HaveChild = '''+ @HaveChild +''' '
 END
  if (@ChildCount<>'' AND LEN(@ChildCount)>0 and CONVERT(INT,@ChildCount) > 0)
   begin
    set @where=@where+' and cu.ChildCount = '''+ @ChildCount +''' '
 END
  if @ChildAgeInfo<>'' and len(@ChildAgeInfo)>0
   begin
    set @where=@where+' and cu.ChildAgeInfo = '''+ @ChildAgeInfo +''' '
 END
  if @LiveTogather<>'' and len(@LiveTogather)>0
   begin
    set @where=@where+' and cu.LiveTogather IN('+@LiveTogather+')'
 END
 
  if @RealEstateCount<>'' and len(@RealEstateCount)>0
   begin
    set @where=@where+' and cu.RealEstateCount IN ('+ @RealEstateCount +')'
 END
 
  if @BoughtInfo<>'' and len(@BoughtInfo)>0
   begin
    set @where=@where+' and cu.BoughtInfo IN ('+ @BoughtInfo +')'
 END
 
  if @LiveInfo<>'' and len(@LiveInfo)>0
   begin
    set @where=@where+' and cu.LiveInfo IN ('+ @LiveInfo +')'
 END
 
  if @PersonalYearIncome<>'' and len(@PersonalYearIncome)>0
   begin
    set @where=@where+' and cu.PersonalYearIncome IN ('+ @PersonalYearIncome +')'
 END
 
  if @FamilyYearInconme<>'' and len(@FamilyYearInconme)>0
   begin
    set @where=@where+' and cu.FamilyYearInconme IN ('+ @FamilyYearInconme +')'
 END
 
  if @Industry<>'' and len(@Industry)>0
   begin
    set @where=@where+' and cu.Industry IN ('+ @Industry +')'
 END
 
  if @WorkLevel<>'' and len(@WorkLevel)>0
   begin
    set @where=@where+' and cu.WorkLevel IN ('+ @WorkLevel +')'
 END
 
  if @CompanyType<>'' and len(@CompanyType)>0
   begin
    set @where=@where+' and cu.CompanyType IN ('+ @CompanyType +')'
    END

DECLARE @insertSql VARCHAR(MAX);
SET @insertSql = '
INSERT INTO tb_CustomerInGroup_Relation
(Relationid,Customerid,Groupid,RemarkInfo,ActiveStatus,CreatePerson,CreatePersonid,CreateTime,ModifyPerson,ModifyPersonid,ModifyTime)
(
 SELECT NEWID(),s.Customerid,'''+CONVERT(VARCHAR(36),@Groupid)+''','''',1,'''+@CreatePerson+''','''+CONVERT(VARCHAR(36),@CreatePersonid)+''',GETDATE(),'''+@CreatePerson+''','''+CONVERT(VARCHAR(36),@CreatePersonid)+''',GETDATE()
 FROM
 (' + @where

    + ' AND cu.CustomerId NOT IN
   (
     SELECT Customerid FROM tb_CustomerInGroup_Relation WHERE Groupid='''+CONVERT(VARCHAR(36),@Groupid)+'''
   )
 )s
)'

 EXEC(@insertSql);
 --PRINT @where;
 --PRINT @insertSql
 RETURN @@ROWCOUNT;
END