生成某一时期，各个代理商的领货，退货销售的总表.sql

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

/******************************************
功能：生成某一时期，所有的代理商的领货，退货销售的总表
说明：@fgt参数利用模糊查找模式,
	1。当@fGT的值为空串时，
		就执行查找所有的代理商资料
	2。当@fgt值不为空是，就直接查找相匹配的代理商资料
	3.没有用到匹配安符，
     @flx类型,查找三种的类型，LH领货，LX销售，TH退货,WX未退 ALL（所有类型）（存货） 
     @sear  查询的报表类型1.总表，２。分类型，３。详细报表		
*********************************************/
declare @temp char(20)
declare @out  char(8000)
declare @Fdate datetime
declare @fedate datetime
declare @fgt   char(4)
declare @lx   char(3)
declare @sjxh  char(5)
declare @sear  int
declare @cross table
(
fname char(20),
fval  char(20)
)


if exists (select * from sysobjects where name='tmp')
drop  table tmp
if exists (select * from sysobjects where name='tmp2')
drop  table tmp2
if exists (select * from sysobjects where name='tmp3')
drop  table tmp3

create  table tmp
(
fna char(20),
fname char(20),
fcount int
)

create  table tmp2
(
fid char(2),
fna char(20),
fname char(20),
fcount int
)
create  table tmp3
(
fid char(2),
fna char(20),
fname char(20),
fdate datetime,
fcolor char(20),
fjbpz char(20),
fno   char(16)
)

set @fdate='2001-01-01'
set @fedate='2005-01-01'
set @fgt=''
SET @LX='ALL'
SET @SEAR=3
set @sjxh='ALL'



if  @fgt=''
	set @fgt='%GT%'
if @sjxh='ALL'
	SET @sjxh='%XH%'




--select * from tgt
--领货
				if (@LX='LH') OR (@LX='ALL')
					BEGIN
						set @temp='LH'--领货（定领货时间）
						insert tmp
						select @temp,fgt,count(*) from tgtlh
						where tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and  tgtlh.fgt like @fgt
						group by fgt
					END
				if (@LX='TH') OR (@LX='ALL')
					BEGIN
						set @temp='TH'--退货(定退货时间)
						insert tmp
						select @temp,tgtlh.fgt,count(*) from tgtth,tgtlh
						where tgtth.fgl=tgtlh.fid and tgtth.fdate>=@fdate and tgtth.fdate<=@fedate and  tgtlh.fgt like @fgt
						group by tgtlh.fgt
					END
				if (@LX='LX') OR (@LX='ALL')
					BEGIN
						set @temp='LX'--（定销售时间）
						insert tmp
						select @temp,tgtlh.fgt,count(*) from thxc,tgtlh
						where tgtlh.fhrxx=thxc.fcode and tgtlh.fid not in(select fgl from tgtth ) and thxc.fdate>=@fdate and thxc.fdate<=@fedate and  tgtlh.fgt like @fgt
						group by tgtlh.fgt
						END

				if (@LX='WX') OR (@LX='ALL')
					BEGIN


						set @temp='WX'--(定领货时间)
						insert tmp
						select @temp,tgtlh.fgt,count(*) from tgtlh
						where tgtlh.fhrxx not in(select fcode from thxc) and tgtlh.fid not in(select fgl from tgtth ) and tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and  tgtlh.fgt like @fgt
						group by tgtlh.fgt
					END
			IF @SEAR=1
			select b.fna as fgtname,
						(
						case a.fna
							when 'LH' then '领货'
							when 'TH' then '退货'
							when 'LX' then '销售'
							when 'WX' then '库存'
						end
						) as flx,a.fcount
			from tmp a,tgt b
			where b.fid=ltrim(rtrim(a.fname)) 
			order by b.fid,a.fna 
				--exec  pCrossTable  tmp,'fname','fcount','sum','fna',''
--分类型报表
if @sear=2 or @sear=3
BEGIN

				if (@lx='LH') OR (@LX='ALL')
					BEGIN
						insert tmp2
						--领货（设定领货的时间）
						select 'LH', thrc.fsjxh,tgtlh.fgt,count(*)  as fcount from tgtlh,thrxx,thrc
						where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and  tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and  tgtlh.fgt like @fgt AND  thrc.fsjxh like LTRIM(RTRIM(@SJXH))
						group by tgtlh.fgt,thrc.fsjxh
						order by tgtlh.fgt
					END
					--exec  pCrossTable  tmp,'fname','fcount','sum','fna',''
					--select * from tmp2
				if (@lx='LX') OR (@LX='ALL')
					BEGIN
						--销售（设定销售时间表）
						insert tmp2
						select 'LX', thrc.fsjxh,tgtlh.fgt,count(*) as fcount from thrc,thrxx,thxc,tgtlh
						where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and thxc.fcode=thrxx.fid and tgtlh.fid not in(select fgl from tgtth) and thxc.fdate>=@fdate and thxc.fdate<=@fedate and  tgtlh.fgt like @fgt AND  thrc.fsjxh like LTRIM(RTRIM(@SJXH))
						group by tgtlh.fgt,thrc.fsjxh
						order by tgtlh.fgt
					END
				if (@lx='TH') OR (@LX='ALL')
					BEGIN
		
						--退货(设定退货时间)
						insert tmp2
						select 'TH', thrc.fsjxh,tgtlh.fgt,count(*)  as fcount from tgtlh,thrxx,thrc,tgtth
						where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and tgtlh.fid =tgtth.fgl and tgtth.fdate>=@fdate and tgtth.fdate<=@fedate and  tgtlh.fgt like @fgt
						group by tgtlh.fgt,thrc.fsjxh
						order by tgtlh.fgt 
					END
				if (@lx='WX') OR (@LX='ALL')
					BEGIN


						--库存(设定领货时间)
						insert tmp2
						select 'WX', thrc.fsjxh,tgtlh.fgt,count(*) as fcount from thrc,thrxx,tgtlh
						where  tgtlh.fhrxx not in(select fcode from thxc)   and   tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and  tgtlh.fid not in(select fgl from tgtth) and tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and  tgtlh.fgt like @fgt AND  thrc.fsjxh like LTRIM(RTRIM(@SJXH))
						group by tgtlh.fgt,thrc.fsjxh
						order by tgtlh.fgt
					 END
				--select * from tmp2
			IF @SEAR=2
			select tgt.fna, 
					(
					case tmp.fna
						when 'LH' then '领货'
						when 'TH' then '退货'
						when 'LX' then '销售'
						when 'WX' then '库存'
					end
					) as flx,tmp.fcount as ffcount,TSJCS.FNA AS FSJCS,TSJXH.FMODEL AS FSJXH,tmp2.fcount as fsjxhcount 
			from tmp2,tmp,tgt,TSJXH,TSJCS
			where tmp2.fid=tmp.fna and tmp.fname=tmp2.fname and tgt.fid=ltrim(rtrim(tmp.fname)) and TSJXH.FID=tmp2.fna AND TSJCS.FID=TSJXH.FSJCS  
			order by tgt.fna,tmp.fna,TSJCS.FID,TSJXH.FID

--所有的详细报表
	IF @SEAR=3
		BEGIN
				if (@lx='LH') OR (@LX='ALL')
					BEGIN
						insert tmp3
						--领货（设定领货的时间）
						select 'LH', thrc.fsjxh,tgtlh.fgt,tgtlh.fdate,tcolor.fid,tjbpz.fid,thrxx.fno from tgtlh,thrxx,thrc,tcolor,tjbpz
						where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and thrxx.fcolor=tcolor.fid and tjbpz.fid=thrc.fjbpz and  tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and  tgtlh.fgt like @fgt AND  thrc.fsjxh like LTRIM(RTRIM(@SJXH))
						order by tgtlh.fgt
					END
				if (@lx='LX') OR (@LX='ALL')
					BEGIN
						--销售（设定销售时间表）
						insert tmp3
						select 'LX', thrc.fsjxh,tgtlh.fgt,thxc.fdate,tcolor.fid,tjbpz.fid ,thrxx.fno from thrc,thrxx,thxc,tgtlh,tcolor,tjbpz
						where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and thxc.fcode=thrxx.fid and tgtlh.fid not in(select fgl from tgtth) and  thrxx.fcolor=tcolor.fid and tjbpz.fid=thrc.fjbpz and  thxc.fdate>=@fdate and thxc.fdate<=@fedate and  tgtlh.fgt like @fgt AND   thrc.fsjxh like LTRIM(RTRIM(@SJXH))
						order by tgtlh.fgt
					END

				if (@lx='TH') OR (@LX='ALL')
					BEGIN


						--退货(设定退货时间)
						insert tmp3
						select 'TH', thrc.fsjxh,tgtlh.fgt,tgtth.fdate,tcolor.fid,tjbpz.fid,thrxx.fno from tgtlh,thrxx,thrc,tgtth,tcolor,tjbpz
						where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and tgtlh.fid =tgtth.fgl and  thrxx.fcolor=tcolor.fid and tjbpz.fid=thrc.fjbpz and tgtth.fdate>=@fdate and tgtth.fdate<=@fedate and  tgtlh.fgt like @fgt
						order by tgtlh.fgt 
				--select * from tmp3
					END

				if (@lx='WX') OR (@LX='ALL')
					BEGIN
						--库存(设定领货时间)
						insert tmp3
						select 'WX', thrc.fsjxh,tgtlh.fgt,tgtlh.fdate,tcolor.fid,tjbpz.fid ,thrxx.fno from thrc,thrxx,tgtlh,tcolor,tjbpz
						where    tgtlh.fhrxx not in(select fcode from thxc) and tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid  and tgtlh.fid not in(select fgl from tgtth) and  thrxx.fcolor=tcolor.fid and tjbpz.fid=thrc.fjbpz and  tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and  tgtlh.fgt like @fgt AND  thrc.fsjxh like LTRIM(RTRIM(@SJXH))
						order by tgtlh.fgt
					END
			IF @SEAR=3
			   select tgt.fna, 
					(
					case tmp.fna
						when 'LH' then '领货'
						when 'TH' then '退货'
						when 'LX' then '销售'
						when 'WX' then '库存'
					end
					) as flx,tmp.fcount as ffcount,TSJCS.FNA AS FSJCS,TSJXH.FMODEL AS FSJXH,tmp2.fcount as fsjxhcount,tcolor.fcolor as fcolor,tjbpz.fname as fjbpz,tmp3.fno,tmp3.fdate 
			   from tmp2,tmp,tgt,tcolor,tjbpz,tmp3,TSJCS,TSJXH
			   where tmp2.fid=tmp.fna and tmp.fname=tmp2.fname and tgt.fid=ltrim(rtrim(tmp.fname)) and TSJXH.fid=tmp2.fna and tcolor.fid= tmp3.fcolor and tjbpz.fid=tmp3.fjbpz and tmp3.fid=tmp2.fid and tmp3.fname=tmp.fname and tmp3.fna=tmp2.fna  AND TSJCS.FID=TSJXH.FSJCS 
			   order by tgt.fna,tmp.fna,TSJCS.FID,TSJXH.FID
			
		END
END
/*
if exists (select * from sysobjects where name='tmp')
drop  table tmp
if exists (select * from sysobjects where name='tmp2')
drop  table tmp2
*/