附件代理领销存情况查询.sql

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

/***********************************************
统计代理附件每种类型的进货
@fall 标记是否查找万能ALL,所有的机型（万能机型和适用机型）BFJ只查询有适用机型，WLJ只查询适用万能机型的
@fpjzl all,所有的配件资料当对配件资料进行选择后，就不为ALL
@act
	1:查看总表
	2:查看明细表
**************************************************/
--select f.fid,r.fmon,r.fnum,r.fdate, from tfjrc r
declare @ffdate datetime
declare @fedate datetime

declare @fpjzl char(5)--配件资料all,所有的配件资料
declare @fadr char(4)--配件货源地
declare @fname char(5)--配件厂商
declare @fpjlx char(2)--配件类型--附件类型 ALL为所有附件类型
declare @fjx char(5)--标记查找的机型'ALL为所有'
declare @fall char(3)--标记是否查找万能ALL,所有的机型（万能机型和适用机型）BFJ只查询有适用机型，WLJ只查询适用万能机型的
declare @act int
declare @lx char(2)
DECLARE @GT CHAR(4)

declare @temp table
(
flx char(2),
fid char(12),
fpjzl char(5),
fmon  money,
fnum int,
fdate datetime,
fgt char(4)



)
declare @temppjzl table
(
fid char(12)
)

declare @tempcount table
(
fgt char(4),
flx char(2),
fpjzl char(5),
ffjlx char(10),
ffjcs char(15),
fjx char(20),
fnum int
)

declare @tempwx table
(
fgt char(4),
flx char(2),
fpjzl char(5),
ffjlx char(10),
ffjcs char(16),
fjx char(20),
fnum int,
fxc int,
fth int,
fwxnum int
)
set @ffdate='2001-01-01'
set @fedate='2005-12-30'


set @fpjzl='000'
set @fadr='ALL'
set @fname='ALL'
set @fpjlx='AL'
set @fjx='ALL'
set @fall='ALL'
set @act=3
set @lx='AL'


SET @GT='ALL'

IF @GT='ALL'
	SET @GT='%%'



if @lx='AL'
set @lx='%%'



if @fadr='ALL'
 set @fadr='%%'
if @fname='ALL'
 set @fname='%%'
if @fpjlx='AL'
set @fpjlx='%%'
if @fjx='ALL'
set @fjx='%%'

--得到配件资料
	if @fpjzl='ALL' 
	begin

		insert @temp
		select (
			case substring(fid,1,2)
				when 'FL' then 'FL'
				when 'FX' then 'FS'
				when 'FT' then 'FT'
			end
					
				) as flx,fid,fpjzl,fmon,fnum,fdate,fgt
		from tglfj
		where  fdate>=@ffdate and fdate<=@fedate and fgt like ltrim(rtrim(@gt))

	end


	if @fpjzl<>'ALL'
		begin
				if @fall='ALL'--当不是查找所有的附件类型 资料时，但是附件的适用机型又是所有的情况
				begin
					insert  into @temppjzl
					select fid 
					from tpjzl
					where fname like ltrim(rtrim(@fname ))
						and fpjlx like ltrim(rtrim(@fpjlx))
						
				end
				if @fall='BFJ'--只查部分适用机型
					insert  into @temppjzl
					select fid 
					from tpjzl
					where fname like ltrim(rtrim(@fname ))
						and fpjlx like ltrim(rtrim(@fpjlx))
						and fjx like ltrim(rtrim(@fjx))
				if @fall='WLJ'--只查部分适用机型
					insert  into @temppjzl
					select fid 
					from tpjzl
					where fname like ltrim(rtrim(@fname ))
						and fpjlx like ltrim(rtrim(@fpjlx))
						and fjx is null
		insert @temp
		select (
			case substring(fid,1,2)
				when 'FL' then 'FL'
				when 'FX' then 'FS'
				when 'FT' then 'FT'
			end
					
				) as flx,fid,fpjzl,fmon,fnum,fdate,fgt
		from tglfj
		where  fdate>=@ffdate and fdate<=@fedate and fgt like ltrim(rtrim(@gt)) and fpjzl in (select fid from @temppjzl)
	end	



			insert into @tempcount
			select a.fgt,a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
			from(	
				--统计出总数
				
						select top 10000 fgt,flx,fpjzl,sum(fnum) as fnum
						from @temp
						group by fgt,flx,fpjzl
						order by fgt,flx,fpjzl
					
				
			     )a,(	
				--查看附件类型 
				select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
					ltrim(rtrim((case 
							when p.fjx is null then '万能'
							when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and  tsjxh.fid=p.fjx)
						      end))) as fjx

				 from tpjzl p,tfjlx f,tfjcs
				 where f.fid=p.fpjlx and tfjcs.fid=p.fname
			     )b
			where a.fpjzl=b.fid --and a.flx like @lx
			order by a.fgt,a.flx,b.ffjlx,b.ffjcs,b.fjx
	--得到未销的数据	




	insert into @tempwx
	select  a.fgt,'FW' as flx,a.fpjzl,a.ffjlx,a.ffjcs,a.fjx,a.fnum,isnull(b.fxcnum,0)as fxcnum,isnull(b.fthnum,0) as fthnum,isnull(b.fwxnum,a.fnum) as fwxnum
	from(
			select top 10000 * 
			from 	@tempcount a
			where a.flx='FL'
		)a left outer join
		(
				select  top 10000 a.fgt,a.fpjzl,a.ffjlx,a.ffjcs,a.fjx,b.fnum as fxcnum,(a.fnum-b.fnum-c.fnum) as fwxnum,c.fnum as fthnum
				from
				(
					select top 10000 * 
					from @tempcount a
					where a.flx='FL'
				)a,  
				(
					select top  10000 * 
					from @tempcount a
					where a.flx='FS'
					)b,
					(
					select top  10000 * 
					from @tempcount a
					where a.flx='FT'
				)c
	 where a.fpjzl=b.fpjzl and a.fgt=b.fgt and  a.fpjzl=c.fpjzl and c.fgt=a.fgt and b.fpjzl=c.fpjzl and b.fgt=c.fgt
	
	)b
	on a.fpjzl=b.fpjzl and a.fgt=b.fgt
	order by a.fgt,a.fpjzl,a.ffjcs,a.fjx
--select * from @tempcount	
--select * from @temp
--select * from @tempwx

if @act=1
begin
		--统计出每种型号的数据量
	select ltrim(rtrim(tgt.fna)) as fgt,a.flx,
			(case a.flx
			 when 'FL' then '领货'
			 when 'FS' then '销售'
			 when 'FT' then '退货'
       		         when 'FW' then '库存'
			 end
			) as flxcn,	
				a.ffjlx,a.fjx,a.fallcount
	from
	(
		select  top 10000 a.*
		from
			(	
			select  top 10000 b.fgt,b.flx,ltrim(rtrim(b.ffjlx)) as ffjlx,ltrim(rtrim(b.fjx)) as fjx,sum(b.fnum) as fallcount
			from @tempcount b
			group by b.fgt,b.flx,b.ffjlx,b.fjx
			order by b.fgt,b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
			)a 
		union
		select  top 10000 a.*
		from
			(
			select top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
			from @tempwx a
			group  by a.fgt,a.flx,a.ffjlx,a.fjx
			order by  a.fgt,a.flx,ltrim(rtrim(a.ffjlx)),ltrim(rtrim(a.fjx))
			)a
	)a,tgt
	where a.flx like @lx and tgt.fid=a.fgt
	order by a.fgt,a.flx
end






if @act=2 
begin	
		select ltrim(rtrim(tgt.fna)) as fgt,a.flx,
				(case a.flx
					 when 'FL' then '领货'
					 when 'FS' then '销售'
					 when 'FT' then '退货'
		       		         when 'FW' then '库存'
					 end
				) as flxcn,
						a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum
		from(
			select  top 10000 a.* 
			from (	

				SELECT  top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fwxnum	as fnum
				from
					(	
	
					select top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
					from @tempwx a
					group  by a.fgt,a.flx,a.ffjlx,a.fjx
					--order by  a.flx,ltrim(rtrim(a.ffjlx)),ltrim(rtrim(a.fjx))
					)A,@TEMPWX B
				WHERE a.ffjlx=b.ffjlx and a.fjx=b.fjx and a.fgt=b.fgt
				--order by a.flx,b.ffjlx,b.fjx,b.ffjcs
			    )a
			union
			select  top 10000 a.* 
			from (
				--统计出总数据报表
				select  top 10000  a.fgt,a.flx,ltrim(rtrim(a.ffjlx))as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fnum
				from
					(
					select top 1000 b.fgt,b.flx,b.ffjlx,b.fjx,sum(b.fnum) as fallcount
					from @tempcount b
					group by b.fgt,b.flx,b.ffjlx,b.fjx
					--order by b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
					)a,
					(
					select top 10000 a.fgt,a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
					from(	
						--统计出总数
				
								select top 10000 fgt,flx,fpjzl,sum(fnum) as fnum
								from @temp
								group by fgt,flx,fpjzl
								--order by flx,fpjzl
					
				
					     )a,(	
						--查看附件类型 
						select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
							ltrim(rtrim((case 
									when p.fjx is null then '万能'
									when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and  tsjxh.fid=p.fjx)
								      end))) as fjx

						 from tpjzl p,tfjlx f,tfjcs
						 where f.fid=p.fpjlx and tfjcs.fid=p.fname
					     )b
					where a.fpjzl=b.fid 
					--order by a.flx,b.ffjlx,b.ffjcs,b.fjx
					)b
				where a.fgt=b.fgt and a.flx=b.flx and ltrim(rtrim(a.ffjlx))=ltrim(rtrim(b.ffjlx)) and ltrim(rtrim(a.fjx))=ltrim(rtrim(b.fjx))
				--order by a.flx,a.ffjlx,a.fjx,b.ffjcs
			    )a
		)a,tgt
		where a.flx like @lx and tgt.fid=a.fgt
		order by a.fgt,a.flx,a.ffjlx,a.fjx,a.ffjcs
	
end	







if @act=3 
begin

	select ltrim(rtrim(tgt.fna)) as fgt,a.flx,
			(case a.flx			
				 when 'FL' then '领货'
				 when 'FS' then '销售'
				 when 'FT' then '退货'
     			         when 'FW' then '库存'
			end
		) as flxcn,
					a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum,a.fdate,a.fnumb
	from
		(
			SELECT TOP 10000 a.* ,null as fdate,null as fnumb
			FROM
			    (	
				SELECT  top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fwxnum	as fnum
				from
					(	
	
					select top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
					from @tempwx a
					group  by a.fgt,a.flx,a.ffjlx,a.fjx
					--order by  a.fgt,a.flx,ltrim(rtrim(a.ffjlx)),ltrim(rtrim(a.fjx))
					)A,@TEMPWX B
				WHERE a.ffjlx=b.ffjlx and a.fjx=b.fjx and a.fgt=b.fgt
				--order by a.flx,b.ffjlx,b.fjx,b.ffjcs
			  )a
			union
  			SELECT TOP 10000 *
			FROM
			(
			select 	a.fgt,a.flx,a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum,b.fdate,b.fnumb
			from 
				(
		
				select  top 10000 a.fgt,a.flx,ltrim(rtrim(a.ffjlx))as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fnum
				from
					(
					select top 1000 b.fgt,b.flx,b.ffjlx,b.fjx,sum(b.fnum) as fallcount
					from @tempcount b
					group by b.fgt,b.flx,b.ffjlx,b.fjx
					--order by b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
					)a,
					(
					select top 10000 a.fgt,a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
					from(	
						--统计出总数
				
								select top 10000 fgt, flx,fpjzl,sum(fnum) as fnum
								from @temp
								group by fgt,flx,fpjzl
								--order by flx,fpjzl
					
				
					     )a,(	
						--查看附件类型 
						select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
							ltrim(rtrim((case 
									when p.fjx is null then '万能'
									when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and  tsjxh.fid=p.fjx)
								      end))) as fjx

						 from tpjzl p,tfjlx f,tfjcs
						 where f.fid=p.fpjlx and tfjcs.fid=p.fname
					     )b
					where a.fpjzl=b.fid 
					--order by a.flx,b.ffjlx,b.ffjcs,b.fjx
					)b
				where a.fgt=b.fgt and a.flx=b.flx and ltrim(rtrim(a.ffjlx))=ltrim(rtrim(b.ffjlx)) and ltrim(rtrim(a.fjx))=ltrim(rtrim(b.fjx))
				order by a.flx,a.ffjlx,a.fjx,b.ffjcs
				)a,
				(
				select  top 10000 a.*,b.fdate,b.fnum as fnumb
				from
					(
					select a.fgt,a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
					from(	
						--统计出总数
				
								select top 10000 fgt,flx,fpjzl,sum(fnum) as fnum
								from @temp
								group by fgt,flx,fpjzl
								--order by flx,fpjzl
					
				
					     )a,(	
						--查看附件类型 
						select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
							ltrim(rtrim((case 
									when p.fjx is null then '万能'
									when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and  tsjxh.fid=p.fjx)
								      end))) as fjx

						 from tpjzl p,tfjlx f,tfjcs
						 where f.fid=p.fpjlx and tfjcs.fid=p.fname
					     )b
					where a.fpjzl=b.fid
					)a,@temp b
				where a.fgt=b.fgt and a.flx=b.flx and a.fpjzl=b.fpjzl 
				order by a.flx,a.ffjlx,a.ffjcs,a.fjx
				)b
			where a.fgt=b.fgt and a.flx=b.flx and a.ffjlx=b.ffjlx and a.ffjcs=b.ffjcs and a.fjx=b.fjx
			--order by a.flx,a.ffjlx,a.fjx,a.ffjcs,b.fdate,b.fhyd
			)A
		)a,tgt
	where a.flx like @lx and tgt.fid=a.fgt
	order by a.fgt,a.flx,a.ffjlx,a.fjx,a.ffjcs,a.fdate
end
--select * from @temp