/**********************************************************
功能：手機串號跟蹤


************************************************************/
declare @fno char(16)
declare @fid char(12)
declare @count int
declare @fbad char(1)--是否損壞'T'為損壞,'F'為未損壞
declare @fuse char(1)--換貨'T'，質保'F'

declare @temp table
(
fno char(16),
fid  char(12),
fdate datetime,
fact  char(20),
fmemo char(500)
)
set @fno='2512455555555555'

declare @hrxx table
(
fid char(12)
)
insert into @hrxx
select fid 
from thrxx
where fno=@fno

DECLARE tpz SCROLL CURSOR FOR
select fid from @hrxx
OPEN tpz
FETCH first FROM tpz
into @fid
WHILE @@FETCH_STATUS = 0
BEGIN
--入庫
if exists (select count(*) from thrxx where fid=@fid)
insert into @temp
select @fno as fno,@fid,thrc.fdate,'入庫' as fact,(case 
					when thyd.fid='HY01' THEN '以'+ltrim(rtrim(thyd.fna))+'形式入庫'
					when thyd.fid='HY02' then '由于'+ltrim(rtrim(thyd.fna))+'而重新入庫'
					when thyd.fid>'HY02' then '從'+ltrim(rtrim(thyd.fna))+'進貨入庫'
				end
					) as fmemo
from thrxx,thrc,thyd
where thrc.fid=thrxx.fhrcid and thyd.fid=thrc.fhyd and thrxx.fid=@fid

--代理領貨
if exists(select * from tgtlh where fhrxx=@fid)
insert into @temp
select @fno as fno,@fid,tgtlh.fdate,'代理領貨' as fact,'由代理商:'+ltrim(rtrim(tgt.fna))+'領貨' as fmemo
from tgtlh,tgt
where tgtlh.fhrxx=@fid and tgt.fid=tgtlh.fgt
--代理銷售

if (exists(select * from tgtlh where fhrxx=@fid and fid not in(select fgl from tgtth)) and exists(select * from thxc where thxc.fcode=@fid))
insert into @temp
select @fno as fno,@fid as fid,thxc.fdate,'代理銷售' as fact,(case 
						when thxc.fname in(select tgt.fna from tgt  ) then '由代理商：'+ltrim(rtrim(thxc.fname))+'銷售出去'
						when thxc.fname not in(select tgt.fna from tgt) then '由代理商：'+ltrim(rtrim((select tgt.fna from tgt where tgt.fid in (select fgt from tgtlh where tgtlh.fhrxx=@fid))))+'銷售給:'+thxc.fname+';聯系電話:'+thxc.ftel
						end	) as fmemo
from thxc
where thxc.fcode=@fid



--代理退貨

if (exists(select * from tgtlh where fhrxx=@fid and fid in(select fgl from tgtth)))
insert into @temp
select @fno as fno, @fid as fid ,tgtth.fdate,'代理退貨' as fact ,'代理商：'+ltrim(rtrim(tgt.fna)) +'退領' as fmemo
from tgtth,tgtlh,tgt
where tgtth.fgl=tgtlh.fid and tgtlh.fgt=tgt.fid and tgtlh.fhrxx=@fid

--零售銷售
if (exists(select * from tgtlh where fhrxx=@fid and fid in(select fgl from tgtth)) or  (not exists(select * from tgtlh where fhrxx=@fid ))) and exists(select * from thxc where thxc.fcode=@fid)
insert into @temp
select @fno as fno, @fid as fid,thxc.fdate,'零售' as fact ,'由'+ltrim(rtrim(tygzl.fname))+'銷售給：'+ltrim(rtrim(thxc.fname))+';聯系電話:'+ltrim(rtrim(thxc.ftel)) as fmemo
from thxc,tygzl
where thxc.fcode=@fid and thxc.fperson=tygzl.fid



--客戶換貨等待
if (exists(select * from thhdd where fhrxx=@fid and fuse='T'))
insert into @temp
select @fno as fno, @fid as fid ,thhdd.fdate,'換貨等待' as fact ,'手機由于技術原因，客戶換貨等待' as fmemo
from thhdd
where thhdd.fhrxx=@fid

--客戶領機


--客戶退貨,未損壞
if (exists(select * from tkhth where tkhth.fhrxx=@fid) and (not exists(select * from tbs where fhrxx=@fid)))
insert into @temp
select @fno as fno,@fid as fid,tkhth.fdate,'客戶退貨' as fact ,'手機無技術原因重新入庫'
from tkhth,thxc,tygzl
where tkhth.fhrxx=@fid and thxc.fcode=@fid and tygzl.fid=thxc.fperson


--客戶退貨,手機損壞
if (exists(select * from tkhth where tkhth.fhrxx=@fid)) and (exists(select * from tbs where fhrxx=@fid))
insert into @temp
select @fno as fno,@fid as fid,tkhth.fdate,'客戶退貨' as fact ,'手機因技術原因被退回上級經銷商' as fmemo
from tkhth,thxc,tygzl
where tkhth.fhrxx=@fid and thxc.fcode=@fid and tygzl.fid=thxc.fperson
--客戶換新機

if (exists(select * from tkhhj where tkhhj.fohrxx=@fid))
insert into @temp
select @fno as fno,@fid as fid,tkhhj.fdate ,'客戶換新機' as fact ,'新機:'+ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel))+';串號為:'+ltrim(rtrim(thrxx.fno))+';銷售員工：'+ltrim(rtrim(tygzl.fname))
from tkhhj,tygzl,thrxx,thxc,tsjcs,tsjxh,thrc
where tkhhj.fohrxx=@fid and thxc.fcode=tkhhj.fnhrxx and thrxx.fid=tkhhj.fnhrxx and thxc.fperson=tygzl.fid and thrc.fid=thrxx.fhrcid and tsjxh.fid=thrc.fsjxh and tsjcs.fid=tsjxh.fsjcs

--返廠維修

if (exists(select * from thhdd where fhrxx=@fid  and fuse='F'))
insert into @temp
select @fno as fno, @fid as fid ,thhdd.fdate,'手機返廠維修' as fact ,'手機由于技術原因但不能換機，進行返廠維修客服' as fmemo
from thhdd
where thhdd.fhrxx=@fid




--領貨(換貨領機，質何領機)
if exists(select * from thhlh,thhdd where thhlh.fhhdd=thhdd.fid and thhdd.fhrxx=@fid)
begin


set @fbad=(select fbad from thhlh,thhdd where thhlh.fhhdd=thhdd.fid and thhdd.fhrxx=@fid)
set @fuse=(select fuse from thhlh,thhdd where thhlh.fhhdd=thhdd.fid and thhdd.fhrxx=@fid)
if @fuse='F'
	insert into @temp
	select @fno as fno, @fid ,thhlh.fdate,'質保機領貨' as fact ,'手機質保后，客戶領回'
	from thhlh,thhdd
	where thhlh.fhhdd=thhdd.fid and thhdd.fhrxx=@fid
if @fuse='T'
  begin
	
	if @fbad='T'
	insert into @temp
	select @fno as fno,@fid ,thhlh.fdate,'領取換貨等待機' as fact ,'新機型號：'+ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel))+';串號：'+ltrim(rtrim(thrxx.fno))+';銷售員：'+ltrim(rtrim(tygzl.fname))
	from thhlh,thhdd,thrxx,thxc,thrc,tsjcs,tsjxh,tygzl
	where thhlh.fhhdd=thhdd.fid and thhdd.fhrxx=@fid and thxc.fid=thhlh.fhrc and thxc.fcode=thrxx.fid and thrc.fid=thrxx.fhrcid  and tsjxh.fid=thrc.fsjxh and tsjcs.fid=tsjxh.fsjcs and tygzl.fid=thxc.fperson
				 
	if @fbad='F'
	insert into @temp
	select @fno as fno,@fid ,thhlh.fdate,'領取換貨等待機' as fact ,'手機沒有損壞，客戶領取原機'
	from thhlh,thhdd
	where thhlh.fhhdd=thhdd.fid and thhdd.fhrxx=@fid
  end

end


--故障機返回


--未銷退庫
if (exists(select * from tbs where fhrxx=@fid)) and ( not exists(select * from thxc where thxc.fcode=@fid))
insert into @temp
select @fno as fno,@fid as fid ,tbs.fdate,'未銷退庫' as fact ,'由于手機過時，未經銷售而退庫'
from tbs
where tbs.fhrxx=@fid



	 FETCH NEXT FROM tpz
   into @fid
END
CLOSE tpz
DEALLOCATE tpz
select a.fno,a.fid,a.fdate,a.fact,a.fmemo,b.fsjxh,b.fname as fjbpz,b.fcolor from @temp a,(select  thrxx.fid,ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) as fsjxh,tjbpz.fname,tcolor.fcolor,thrxx.fno
from thrc,tsjcs,tsjxh,tjbpz,thrxx,tcolor
where thrc.fid in(select  top 1 fhrcid from thrxx where thrxx.fno=@fno)
	and tsjcs.fid=tsjxh.fsjcs and thrc.fsjxh=tsjxh.fid
	and tjbpz.fid=thrc.fjbpz
	and thrxx.fhrcid=thrc.fid
	and thrxx.fcolor=tcolor.fid) b
where a.fno=b.fno
order by a.fid,a.fdate
--select * from @hrxx