查询员工的销售手机的情况.sql

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

/***********************************************************
统计所有员工的销售业绩
@sear:搜索的种类，1为统计总表，2。为分类型 表3为详细表
@group:分类的种类1为按手机型号，货源类型分类
	2为按货源类型，手机型号来分类
@act :1为按时间段统计每个员工的在这一时间段内的所有的销售，退货，实销
	（以时间段内发生的行为为统计对象）
      2为统计某一时间段内的所有进货手机，每一个员工的销售，退货，实销情况
	（以时间段内手机为统计对象）
*************************************************************/
declare @sear int--搜索的种类
declare @group int--1为按手机类型货源类型统计2为按货源类型手机类型
declare @fdate datetime--起始日期
declare @fedate datetime--结束日期
declare @lx     char(2)--查类型(销售，退货，实销)
declare @sjxh  char(5)--手机型号
declare @hylx  char(2)--货源类型 
declare @yg    char(4)--员工
declare @act  int

declare @tmp table
(
fid char(2),
fhrxx char(12),
fyg char(4),
fdate datetime
)

declare @tmp3 table
(fhrxx char(12)
)
declare @tmp2 table
(
fid char(2),
fhrxx char(12),
fyg char(4),
fdate datetime,
fsjxh char(5),
fjbpz char(4),
fhylx char(4)
)
set @sear=1
set @group=1
set @act=1

set @fdate='2001-01-01'
set @fedate='2005-01-01'
set @lx='AL'
set @sjxh='ALL'
set @hylx='AL'
set @yg='YG03'
SET @yg='ALL'
if @lx='AL'
	set @lx='%%'
if @sjxh='ALL'
	set @sjxh='%%'
if @hylx='AL'
	set @hylx='%%'
if @yg='ALL'
	set @yg='%%'
	


--年有已销的手机
--那种换货没有现机的返回厂家的机子，不能算做销售，因为他根本就没关生任何利润，也不能算退货
if @act=1
	begin
		insert into @tmp
		select 'SA',thxc.fcode,thxc.fperson,thxc.fdate
		from thxc
		where thxc.fid not in(select fhrc from thhlh) and thxc.fdate>=@fdate and thxc.fdate<=@fedate 
		insert into @tmp
		SELECT 'TH',thxc.fcode,thxc.fperson,tkhth.fdate 
		from thxc,tkhth 
		where thxc.fcode=tkhth.fhrxx and thxc.fid not in(select fhrc from thhlh) and tkhth.fdate>=@fdate and tkhth.fdate<=@fedate
		insert into @tmp
		select 'WT',thxc.fcode,thxc.fperson,thxc.fdate 
		from thxc 
		where thxc.fcode not in(select fhrxx from tkhth)
		and thxc.fid not in(select fhrc from thhlh) and thxc.fdate>=@fdate and thxc.fdate<=@fedate 
	end
if @act=2
	begin
		insert into @tmp3
		select thrxx.fid from thrxx,thrc where thrxx.fhrcid=thrc.fid and thrc.fdate>=@fdate and thrc.fdate<=@fedate

		insert into @tmp
		select 'SA',thxc.fcode,thxc.fperson,thxc.fdate
		from thxc,@tmp3 b
		where thxc.fid not in(select fhrc from thhlh) and thxc.fcode=b.fhrxx
		insert into @tmp
		SELECT 'TH',thxc.fcode,thxc.fperson,tkhth.fdate 
		from thxc,tkhth ,@tmp3 b
		where thxc.fcode=tkhth.fhrxx and thxc.fid not in(select fhrc from thhlh) and thxc.fcode=b.fhrxx
		insert into @tmp
		select 'WT',thxc.fcode,thxc.fperson,thxc.fdate 
		from thxc ,@tmp3 b
		where thxc.fcode not in(select fhrxx from tkhth)
		and thxc.fid not in(select fhrc from thhlh) and thxc.fcode=b.fhrxx
	end


if @sear=1 
begin
		select fyg,(
				case a.fid
					when 'SA' then '销售'
					when 'TH' then '退货'
					when 'WT' then '实销'
				end
				)as flx,count(*)as fcount from @tmp a
		where a.fid like ltrim(rtrim(@lx))
		group by a.fyg,a.fid
		order by a.fyg,a.fid
		
end

if (@sear=2) or (@sear=3)
	--先得到数据得到每一个记录的手机型号，和货源类型
	insert into @tmp2
	select a.* ,thrc.fsjxh,thrc.fjbpz,thrc.fhylx from (
		select * from @tmp )a,thrc,thrxx where a.fhrxx=thrxx.fid and thrxx.fhrcid=thrc.fid

if @sear=2
begin
	--先得到数据得到每一个记录的手机型号，和货源类型
	--insert into @tmp2
	--select a.* ,thrc.fsjxh,thrc.fjbpz,thrc.fhylx from (
		--select * from @tmp )a,thrc,thrxx where a.fhrxx=thrxx.fid and thrxx.fhrcid=thrc.fid
	if @group=1
		begin 
			select tygzl.fname,(
						case d.fid
							when 'SA' then '销售'
							when 'TH' then '退货'
							when 'WT' then '实销'
						end
					)as flx,d.fid as flxid,d.fygcount,tsjcs.fna as fsjcs,tsjxh.fmodel as fsjxh,d.fsjxhcount,thylx.fname as fhylx,d.fhylxcount
					  
			from (
					select a.*,b.fhylx,b.fhylxcount 
					from (
						select c.fyg,c.fid,c.fygcount,b.fsjxh,b.fsjxhcount 
						from  (select top 10000 a.fyg,a.fid,count(*) as fygcount from @tmp a group by a.fyg,a.fid order by a.fyg,a.fid)c , 
						(select  top 10000 a.fyg,a.fid,a.fsjxh ,count(* ) as fsjxhcount from @tmp2 a
						group by a.fyg,a.fid,a.fsjxh
						order by a.fyg,a.fid,a.fsjxh)b
						where c.fyg=b.fyg and c.fid=b.fid)a,
						(select  top 10000 a.fyg,a.fid,a.fsjxh,a.fhylx ,count(* ) as fhylxcount from @tmp2 a
						group by a.fyg,a.fid,a.fsjxh,a.fhylx
						order by a.fyg,a.fid,a.fsjxh,a.fhylx)b
					where a.fid=b.fid and a.fyg=b.fyg and a.fsjxh=b.fsjxh AND a.fid like ltrim(rtrim(@lx))
						AND b.fsjxh like ltrim(rtrim(@sjxh))
						AND b.fhylx like ltrim(rtrim(@hylx)) 
						AND a.fyg like ltrim(rtrim(@yg))
				)d,tygzl,thylx,tsjcs,tsjxh
			where d.fyg=tygzl.fid and thylx.fid=d.fhylx and tsjcs.fid=tsjxh.fsjcs and tsjxh.fid =d.fsjxh 
			order by d.fyg,d.fid,tsjcs.fid,tsjxh.fid,thylx.fid
		end
	if @group=2 
		begin
			select tygzl.fname,(
						case d.fid
							when 'SA' then '销售'
							when 'TH' then '退货'
							when 'WT' then '实销'
						end
					)as flx,d.fid as flxid,d.fygcount,thylx.fname as fhylx,d.fhylxcount,tsjcs.fna as fsjcs,tsjxh.fmodel as fsjxh,d.fsjxhcount	
			from(	
					select a.*,b.fsjxh,b.fsjxhcount 
					from (
						select c.fyg,c.fid,c.fygcount,b.fhylx,b.fhylxcount 
						from  (select top 10000 a.fyg,a.fid,count(*) as fygcount from @tmp a group by a.fyg,a.fid order by a.fyg,a.fid)c , 
							(select  top 10000 a.fyg,a.fid,a.fhylx ,count(* ) as fhylxcount from @tmp2 a
							group by a.fyg,a.fid,a.fhylx
							order by a.fyg,a.fid,a.fhylx)b
						where c.fyg=b.fyg and c.fid=b.fid)a,
							(select  top 10000 a.fyg,a.fid,a.fhylx,a.fsjxh ,count(* ) as fsjxhcount from @tmp2 a
							group by a.fyg,a.fid,a.fhylx,a.fsjxh
							order by a.fyg,a.fid,a.fhylx,a.fsjxh)b
					where a.fid=b.fid and a.fyg=b.fyg and a.fhylx=b.fhylx AND a.fid like ltrim(rtrim(@lx))
						AND b.fsjxh like ltrim(rtrim(@sjxh))  
						AND b.fhylx like ltrim(rtrim(@hylx))
						AND a.fyg like ltrim(rtrim(@yg))
				)d,tygzl,thylx,tsjcs,tsjxh
			where d.fyg=tygzl.fid and thylx.fid=d.fhylx and tsjcs.fid=tsjxh.fsjcs and tsjxh.fid =d.fsjxh
			order by d.fyg,d.fid,thylx.fid,tsjcs.fid,tsjxh.fid
		end
end
if @sear=3
begin
	if @group=1
		begin 	
			select tygzl.fname,(
						case d.fid
							when 'SA' then '销售'
							when 'TH' then '退货'
							when 'WT' then '实销'
						end
					)as flx,d.fid as flxid,d.fygcount,tsjcs.fna as fsjcs,tsjxh.fmodel as fsjxh,d.fsjxhcount,thylx.fname as fhylx,d.fhylxcount,thrxx.fno,tcolor.fcolor,tjbpz.fname,d.fdate as fsadate,thrc.fdate as fjhdate
			from (
						select top 10000 a.* ,b.fhrxx,b.fdate
						from (
							select a.*,b.fhylx,b.fhylxcount from (
								select c.fyg,c.fid,c.fygcount,b.fsjxh,b.fsjxhcount 
								from  (select top 10000 a.fyg,a.fid,count(*) as fygcount from @tmp a group by a.fyg,a.fid order by a.fyg,a.fid)c , 
									(select  top 10000 a.fyg,a.fid,a.fsjxh ,count(* ) as fsjxhcount from @tmp2 a
									group by a.fyg,a.fid,a.fsjxh
									order by a.fyg,a.fid,a.fsjxh)b
								where c.fyg=b.fyg and c.fid=b.fid)a,
									(select  top 10000 a.fyg,a.fid,a.fsjxh,a.fhylx ,count(* ) as fhylxcount from @tmp2 a
									group by a.fyg,a.fid,a.fsjxh,a.fhylx
									order by a.fyg,a.fid,a.fsjxh,a.fhylx)b
							where a.fid=b.fid and a.fyg=b.fyg and a.fsjxh=b.fsjxh
							)a,@tmp2 b
						where a.fyg=b.fyg and a.fid=b.fid and a.fsjxh=b.fsjxh and a.fhylx=b.fhylx
							AND b.fsjxh like ltrim(rtrim(@sjxh))
							AND b.fhylx like ltrim(rtrim(@hylx))
							AND a.fyg like ltrim(rtrim(@yg))
						order by a.fyg,a.fid,a.fsjxh,a.fhylx,b.fdate
				)d,tygzl,thylx,tsjcs,tsjxh,thrxx,tcolor,thrc,tjbpz
		where d.fyg=tygzl.fid and thylx.fid=d.fhylx and tsjcs.fid=tsjxh.fsjcs and tsjxh.fid =d.fsjxh and thrxx.fhrcid=thrc.fid and tjbpz.fid=thrc.fjbpz and thrxx.fcolor=tcolor.fid and d.fhrxx=thrxx.fid
			AND d.fid  like ltrim(rtrim(@lx))
		order by d.fyg,d.fid,tsjcs.fid,tsjxh.fid,d.fhylx


		end

	if @group=2 
		begin
			select tygzl.fname,(
						case d.fid
							when 'SA' then '销售'
							when 'TH' then '退货'
							when 'WT' then '实销'
						end
					)as flx,d.fid as flxid,d.fygcount,thylx.fname as fhylx,d.fhylxcount,tsjcs.fna as fsjcs,tsjxh.fmodel as fsjxh,d.fsjxhcount,thrxx.fno,tcolor.fcolor,tjbpz.fname,d.fdate  as fsadate,thrc.fdate as fjhdate
			from (
					select top 10000 a.*, b.fhrxx,b.fdate
					from (
						select a.*,b.fsjxh,b.fsjxhcount from (
							select c.fyg,c.fid,c.fygcount,b.fhylx,b.fhylxcount 
							from  (select top 10000 a.fyg,a.fid,count(*) as fygcount from @tmp a group by a.fyg,a.fid order by a.fyg,a.fid)c , 
								(select  top 10000 a.fyg,a.fid,a.fhylx ,count(* ) as fhylxcount from @tmp2 a
								group by a.fyg,a.fid,a.fhylx
								order by a.fyg,a.fid,a.fhylx)b
							where c.fyg=b.fyg and c.fid=b.fid)a,
								(select  top 10000 a.fyg,a.fid,a.fhylx,a.fsjxh ,count(* ) as fsjxhcount from @tmp2 a
								group by a.fyg,a.fid,a.fhylx,a.fsjxh
								order by a.fyg,a.fid,a.fhylx,a.fsjxh)b
						where a.fid=b.fid and a.fyg=b.fyg and a.fhylx=b.fhylx
						) a,@tmp2 b
					where a.fyg=b.fyg and a.fid=b.fid and a.fhylx=b.fhylx and a.fsjxh=b.fsjxh
						AND b.fsjxh like ltrim(rtrim(@sjxh))  
						AND b.fhylx like ltrim(rtrim(@hylx))
						AND a.fyg like ltrim(rtrim(@yg))
					order by a.fyg,a.fid,a.fhylx,a.fsjxh,b.fdate
				)d,tygzl,thylx,tsjcs,tsjxh,thrxx,tcolor,thrc,tjbpz
		where d.fyg=tygzl.fid and thylx.fid=d.fhylx and tsjcs.fid=tsjxh.fsjcs and tsjxh.fid =d.fsjxh and thrxx.fhrcid=thrc.fid and tjbpz.fid=thrc.fjbpz and thrxx.fcolor=tcolor.fid and d.fhrxx=thrxx.fid
			AND d.fid  like ltrim(rtrim(@lx))
		order by d.fyg,d.fid,d.fhylx,tsjcs.fid,tsjxh.fid
		end

end