-- =============================================================
-- Author:		zhangl
-- Create date: 2009-2-18
-- Description:	删除重复数据并将被删除行的有用信息合并
-- 如电话,联系人(获取表中重复客户的存储过程GetRepeatClient_Proc)
-- 流程:需判断被标识可被删除的重复客户当前是否在报备状态
--是否在合同状态,如不在可被删除(并且在删除将合并一些有用的信息
--- 如:跟踪记录,电话,联系),如在则保留


-- Test :DelRepeateClient_Proc
-- =============================================================
alter PROCEDURE DelRepeateClient_Proc
AS
BEGIN
	SET NOCOUNT ON;

   --select * from RepeatClient where flag=0  --将被处理的数据

   --select * from RepeatClient where flag=1  --被保留的数据
   --select * from clientbase where no='121678'

 select * into #temp from RepeatClient where flag=1 --得到被保留的数据

--定义废弃客户的No,Name,Tel,LinkMen
Declare @No varchar(50)
Declare @Name varchar(50)
Declare @Tel varchar(50)
Declare @LinkMen nvarchar(50)
set @No=''
set @Name=''

---定义保留客户的编号No
Declare @cNo varchar(50)

Declare c cursor fast_forward for select no,name from repeatclient where flag=0  --循环所有被废弃的客户
open c 
fetch next from c into @No,@Name
while @@fetch_status=0
begin

--如果不在合同表中存在
if not exists(select * from contracts where clientno=(select no from clients where name =@Name))
begin 
  --然后判断是不是在报备表中存在,如果也不存在
  if not exists(select * from clientprotect where clientid=@No)
  begin
	 --删除数据并将有用数据合并,电话,联系人,跟踪记录(故应先找到被保留的数据编号) 
     
     if exists(select * from clientbase where name like '%'+@Name+'%' and no  in  (select no from #temp ))
	 begin
         set @cNo=''
		 set @Tel=''
		 set @LinkMen=''

         --得到保留客户的编号
		 select @cNo=no from clientbase where name like '%'+@Name+'%' and no in (select no from #temp) --得到被保留客户的编号
         --得到废弃客户的Tel,LinkMen
		 select @Tel=tel,@LinkMen=linkmen from clientbase where no=@No		 


         --由@No(废弃客户)和@cNo(保留客户)进行数据的合并操作
         update clientbase set tel=tel+' '+@Tel,LinkMen=LinkMen+' '+@LinkMen where no=@cNo
         
		--判断有无跟踪记录,有则合并跟踪记录
		if exists (select * from ClientTrack where cNo=@No)
		begin
			update ClientTrack set cNo=@cNo where cNo=@No  --把废弃客户的编号更新成保留客户的编号 zhangl	
		end
		
		--所有的操作都已完成,则从clientbase表中删除此条记录
		Delete from clientbase where no=@No

	 end
     
  end

end 

fetch next from c into @No,@Name
end 
close c
deallocate c 


--select * from clientbase where name like '%MTI国际学校%' and no in (select no from #temp)
--select * from #temp

drop table #temp


END
GO