/*************************************************************************
功能：act 為2生成(一定時(shí)間內(nèi)的所有進(jìn)貨)手機(jī)的銷售情況
    act 為1 生成一定時(shí)間內(nèi)的手機(jī)銷售情況
      對于，庫存的查詢手機(jī)，必需從建帳日期開始才能查詢準(zhǔn)確
	注：進(jìn)貨，以進(jìn)貨日期為準(zhǔn)，銷售以銷售日期為準(zhǔn)，庫存以領(lǐng)貨日期為準(zhǔn),退貨（指未銷退貨）以退貨日期為準(zhǔn)
說明：
     @sear  查詢的報(bào)表類型1.總表，２。分類型，３。詳細(xì)報(bào)表
     @sjxh需要查找的手機(jī)型號(hào)

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

declare @Ffdate datetime
declare @fedate datetime
declare @sear  int
declare @act int
declare @sjxh char(5)
declare @temptable table
(
fid char(12)

)

set @act=1
set @sear=2
set @Ffdate='2001-01-01'
set @fedate='2005-12-30'
--drop table #temp
--select * from thxc
set @sjxh='xh001'
if @sjxh='ALL'
set @sjxh='%%'
if @act=1--一段時(shí)間內(nèi)的銷售情況 
	begin
		insert into @temptable
		select thxc.fid  
		from thxc,thrxx,thrc
		where thxc.fdate>=@ffdate and thxc.fdate<=@fedate
			and thxc.fcode not in(select fhrxx from tkhth) 
			and thxc.fcode =thrxx.fid and thrxx.fhrcid=thrc.fid 
			and thrc.fsjxh like ltrim(rtrim(@sjxh))
	end
if @act=2 
	begin
		insert into @temptable
		select thxc.fid
		from thxc 
		where thxc.fcode in
					(select fid 
					 from thrxx 
					 where thrxx.fhrcid in
								(select fid 
								 from thrc 
								 where fdate>=@ffdate and fdate<=@fedate and thrc.fsjxh like ltrim(rtrim(@sjxh))))
			and thxc.fcode not in(select fhrxx from tkhth) 
	end

if @sear=1
select sum(b.fmon-b.fjhmon) as flr 
from @temptable a,thxc b
where a.fid=b.fid
if @sear=2
	begin
		select tsjcs.fna as fsjcs,tsjxh.fmodel as fsjxh,thrxx.fno,tjbpz.fname as fjbpz,tcolor.fcolor,b.fjhmon,b.fmon as fsalemon,(b.fmon-b.fjhmon) as flr,tygzl.fname as fygname,b.fname as fkhname,b.ftel as fkhtel,b.fdate as fdate,b.fmemo
		from @temptable a,thxc b,thrxx,tcolor,thrc,tsjcs,tsjxh,tjbpz,tygzl
		where a.fid=b.fid and thrxx.fid=b.fcode and thrxx.fcolor=tcolor.fid
			and thrxx.fhrcid=thrc.fid and tsjcs.fid=tsjxh.fsjcs and thrc.fsjxh=tsjxh.fid
			and tjbpz.fid=thrc.fjbpz and tygzl.fid=b.fperson
		--order by 
	end