/************************************************************
功能:
	如果想得到某一個月的所有這些消息，有三個地方需要進(jìn)行時間判定
	1.手機(jī)入庫部分
	2.退庫示回的部分
	3.手機(jī)銷售部分
作者：陳銀軍
時間：2005.8.22
****************************************************************/
declare @date  char(6)
declare @odate char(6) 
Declare @TMps Table
(
fsjxh char(50),
fid   char(5),
fjh  int,
fkt int,
fsg int,
fcf int,
ftotal int,
fwxtk int,
falsales int,
fkc int
)
set @date='200508'
set @odate=cast(cast(@date as int)-1 as char(6))
if cast(substring(@date,5,2)as int)=1
set @odate=cast(cast(substring(@date,1,4)as int)-1 as char(4))+'12'
--得到上一個月的號
insert into @TMps 
select a.*,isnull(b.fkc,0) as fkc
from(
select a.* ,isnull(b.ftotal,0) as falsales
from 
		(
			select a.*,isnull(b.total,0) as fwxtk
			from (select c.fsjxh,b.fid,b.fjh,b.fkt,b.fsg,b.fcf,(fjh+fkt+fsg+fcf)as ftotal 
			      from 
				   (
						--開始得到本月內(nèi)所有類型手機(jī)的的進(jìn)貨入庫數(shù),包括所有的入庫類型(4種入庫類型)得到義叉表
					select a.*, isnull(b.fjh,0)AS FJH,ISNULL(b.fkt,0)AS FKT,ISNULL(b.fsg,0)AS FSG,ISNULL(b.fcf,0)AS FCF
					from vAllSjxh a left outer join(						

							select a.fsjxhid,
							sum(case a.fhylxid when 'JH' then a.flxtotal else 0 end) as FJH,
							sum(case a.fhylxid when 'KT' then a.flxtotal else 0 end) as FKT,
							sum(case a.fhylxid when 'SG' then a.flxtotal else 0 end) as FSG,
							sum(case a.fhylxid when 'CF' then a.flxtotal else 0 end) as FCF

				    from  
					(SELECT top 1000  fsjxhid, fhylxid, SUM(fnum) AS flxtotal
					 FROM 
					      (SELECT TOP 1000 dbo.tsjxh.fid AS fsjxhid,dbo.thrc.fid,  dbo.thrc.fnum,dbo.thrc.fdate, dbo.thylx.fid AS fhylxid
					       FROM dbo.tsjxh INNER JOIN
    							 dbo.tsjcs ON dbo.tsjxh.fsjcs = dbo.tsjcs.fid INNER JOIN
    							 dbo.thrc ON dbo.tsjxh.fid = dbo.thrc.fsjxh INNER JOIN
     							 dbo.tjbpz ON dbo.thrc.fjbpz = dbo.tjbpz.fid INNER JOIN
    							 dbo.thylx ON dbo.thrc.fhylx = dbo.thylx.fid INNER JOIN
   							 dbo.thyd ON dbo.thrc.fhyd = dbo.thyd.fid
							--設(shè)置查詢的日期段也就是手機(jī)進(jìn)貨的日期，就能查出這一時段所有進(jìn)貨的手機(jī)了
						where ltrim(rtrim(substring(thrc.fid,3,6)))=@date
					       ORDER BY dbo.thrc.fid DESC) a
				    GROUP BY fsjxhid, fhylxid
				    ORDER BY fsjxhid) a 
			      GROUP BY fsjxhid
)b on a.fid=b.fsjxhid
					
					--結(jié)束得到本月內(nèi)所有類型手機(jī)的的進(jìn)貨入庫數(shù),包括所有的入庫類型(4種入庫類型)
					
					)  b,vAllSjxh c
			where b.fid=c.fid) a left outer join(
								SELECT c.fsjxh, COUNT(*) AS total
								FROM (select tbs.fhrxx
  								      from tbs
  								      WHERE (dbo.tbs.fhrxx NOT IN
          											(SELECT fhrxx
           											 FROM thhdd))
									      AND (dbo.tbs.fhrxx NOT IN
             											 (SELECT fhrxx
             											  FROM tkhth))
										and ltrim(rtrim(substring(tbs.fid,3,6)))=@date
										--加上時間條件，就能找到這一個月的所有退庫未銷手機(jī)
									) b INNER JOIN
										dbo.thrxx r ON b.fhrxx = r.fid INNER JOIN
										dbo.thrc c ON r.fhrcid = c.fid
								GROUP BY c.fsjxh

								)b on a.fid=b.fsjxh
) a full outer  join (SELECT TOP 1000  dbo.thrc.fsjxh as fsjxhid, COUNT(*) AS ftotal
						FROM dbo.thxc INNER JOIN
      							dbo.thrxx ON dbo.thxc.fcode = dbo.thrxx.fid INNER JOIN
      							dbo.thrc ON dbo.thrxx.fhrcid = dbo.thrc.fid
						where 	ltrim(rtrim(substring(thxc.fid,3,6)))=@date
							--設(shè)置查詢的日期段也就是手機(jī)賣手機(jī)的日期，就能查出這一時段所有賣出的手機(jī)了
						GROUP BY dbo.thrc.fsjxh
						ORDER BY dbo.thrc.fsjxh
) b on ltrim(rtrim(a.fid))=ltrim(rtrim(b.fsjxhid))
		
)a left outer join 
(select fsjxh as fid,(fjh+fkt+fsg+fcf-ftc-fxc)as fkc from tsjpd where substring(fid,3,6)=@odate) b
on b.fid=a.fid

delete from @TMps where fjh=0 and fkt=0 and fsg=0 and fcf=0 and ftotal=0 and fwxtk=0 and falsales=0 and fkc=0
select * from @TMps