附件入库销售库存情况查询.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 @slx CHAR(2)--销售类型 （赠品，还是销售）AL为所有销售类型

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


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

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

declare @tempwx table
(
flx char(2),
fpjzl char(5),
ffjlx char(10),
ffjcs char(16),
fjx char(20),
fnum int,
fxc 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=2
set @lx='AL'
set @slx='AL'

if @slx='AL'
	set @slx='%%'

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 into @temp
		select 'FJ' AS flx,fid,fpjzl,fmon,fnum,fdate,fadr,fmemo
		from tfjrc
		where fdate>=@ffdate and fdate<=@fedate 
		and fadr like ltrim(rtrim(@fadr))
		insert into @temp
		select 'FS' as flx,fid,fpjzl,fmon,fnum,fdate,ffxlx,fmemo
		from tfjxc
		where fdate>=@ffdate and fdate<=@fedate and ffxlx like ltrim(rtrim(@slx))
		--select * from tfjxc
	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))
						--and fjx like ltrim(rtrim(@fjx))
					--union 
					--select fid 
					--from tpjzl
					--where fname like ltrim(rtrim(@fname ))
						--and fpjlx like ltrim(rtrim(@fpjlx))
						--and fjx is null
					
				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 into @temp
		select 'FJ' as flx,fid,fpjzl,fmon,fnum,fdate,fadr,fmemo
		from tfjrc
		where fdate>=@ffdate and fdate<=@fedate 
		and fadr like ltrim(rtrim(@fadr))
		and fpjzl in (select fid from @temppjzl)
		
		--得到销售的数据
		insert into @temp
		select 'FS' as flx,fid,fpjzl,fmon,fnum,fdate,ffxlx,fmemo
		from tfjxc
		where fdate>=@ffdate and fdate<=@fedate and fpjzl in (select fid from @temppjzl)
			and ffxlx like ltrim(rtrim(@slx))
		end

			insert into @tempcount
			select a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
			from(	
				--统计出总数
				
						select top 10000 flx,fpjzl,sum(fnum) as fnum
						from @temp
						group by 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 --and a.flx like @lx
			order by a.flx,b.ffjlx,b.ffjcs,b.fjx
	--得到未销的数据			
	insert into @tempwx
	select  'FW' as flx,a.fpjzl,a.ffjlx,a.ffjcs,a.fjx,a.fnum,isnull(b.fxcnum,0)as fxcnum,isnull(b.fwxnum,a.fnum) as fwxnum
	from(
			select top 10000 * 
			from @tempcount a
			where a.flx='FJ'
	)a left outer join
	(
		select  top 10000 a.fpjzl,a.ffjlx,a.ffjcs,a.fjx,b.fnum as fxcnum,(a.fnum-b.fnum) as fwxnum
		from
		(
			select top 10000 * 
			from @tempcount a
			where a.flx='FJ'
		)a,  
		(
			select top  10000 * 
			from @tempcount a
			where a.flx='FS'
		)b
	 where a.fpjzl=b.fpjzl
	order by a.ffjlx,a.ffjcs,a.fjx
	)b
	on a.fpjzl=b.fpjzl


if @act=1
begin
		--统计出每种型号的数据量
	select a.flx,
			(case a.flx
			 when 'FJ' then '进货'
			 when 'FS' then '销售'
			 when 'FW' then '库存'
			 end
			) as flxcn,	
				a.ffjlx,a.fjx,a.fallcount
	from
	(
		select  top 10000 a.*
		from
			(	
			select  top 10000 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.flx,b.ffjlx,b.fjx
			order by b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
			)a 
		union
		select  top 10000 a.*
		from
			(
			select top 10000 a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
			from @tempwx a
			group  by a.flx,a.ffjlx,a.fjx
			order by  a.flx,ltrim(rtrim(a.ffjlx)),ltrim(rtrim(a.fjx))
			)a
	)a
	where a.flx like @lx
	order by a.flx
end






if @act=2 
begin	
		select a.flx,
				(case a.flx
				 when 'FJ' then '进货'
				 when 'FS' 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.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.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
					from @tempwx a
					group  by 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
				--order by a.flx,b.ffjlx,b.fjx,b.ffjcs
			    )a
			union
			select  top 10000 a.* 
			from (
				--统计出总数据报表
				select  top 10000  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.flx,b.ffjlx,b.fjx,sum(b.fnum) as fallcount
					from @tempcount b
					group by b.flx,b.ffjlx,b.fjx
					--order by b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
					)a,
					(
					select top 10000 a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
					from(	
						--统计出总数
				
								select top 10000 flx,fpjzl,sum(fnum) as fnum
								from @temp
								group by 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.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
		where a.flx like @lx
		order by a.flx,a.ffjlx,a.fjx,a.ffjcs
	
end	

/***********************
**************************/





if @act=3 
begin

	select a.flx,
			(case a.flx
				 when 'FJ' then '进货'
				 when 'FS' then '销售'
				 when 'FW' then '库存'
				 end
			) as flxcn,
					a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum,a.fdate,a.fnumb,a.fhyd,a.fmemo
	from
		(
			SELECT TOP 10000 a.* ,null as fdate,null as fnumb,null as fhyd, null as fmemo
			FROM
			    (	
				SELECT  top 10000 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.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
					from @tempwx a
					group  by 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
				--order by a.flx,b.ffjlx,b.fjx,b.ffjcs
			     )a
			union
  			SELECT TOP 10000 *
			FROM
			(
			select 	a.flx,a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum,b.fdate,b.fnumb,b.fhyd,b.fmemo
			from 
				(
		
				select  top 10000 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.flx,b.ffjlx,b.fjx,sum(b.fnum) as fallcount
					from @tempcount b
					group by b.flx,b.ffjlx,b.fjx
					--order by b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
					)a,
					(
					select top 10000 a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
					from(	
						--统计出总数
				
								select top 10000 flx,fpjzl,sum(fnum) as fnum
								from @temp
								group by 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.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,
									(
									case
									when substring(b.fadr,1,1)='H'   then  (select fna from thyd where ltrim(rtrim(fid))=B.FADR)
									when substring(b.fadr,1,1)<>'H'  then  (select  fname from tfxlx where fid=fadr)
								
									end 	
										) as fhyd,b.fmemo
									--substring(b.fadr,1,1) as fadr
				from
					(
					select a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
					from(	
						--统计出总数
				
								select top 10000 flx,fpjzl,sum(fnum) as fnum
								from @temp
								group by 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.flx=b.flx and a.fpjzl=b.fpjzl 
				--order by a.flx,a.ffjlx,a.ffjcs,a.fjx
				)b
			where 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
	where a.flx like @lx
	order by a.flx,a.ffjlx,a.fjx,a.ffjcs,a.fdate,a.fhyd
end
--select * from @temp