手机串号跟踪.sql

手机进销存系统Delphi源码,管理手机的进货还有销售方面的功能

/**********************************************************
功能：手机串号跟踪


************************************************************/
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