/*************************************
功能:
	每月月初進行自動盤點,
	把統(tǒng)計出來的信息,自動追加到TSJPD中去
	這樣每次統(tǒng)計的時候就只需要將上一個月的庫存調(diào)出來就行了,
	沒有必要進行重新統(tǒng)計,統(tǒng)計上一個月的
        當新的一月一開始就統(tǒng)計原來上一個月的數(shù)據(jù)
作者:陳銀軍
時間:2005.8.22
***************************************/
declare @ndate char(6)
declare @date char(6)
set @ndate=Convert(char(6),GetDate(),112)
set @date=cast(cast(@ndate as int)-1 as char(6))
if cast(substring(@ndate,5,2)as int)=1
set @date=cast(cast(substring(@date,1,4)as int)-1 as char(4))+'12'
select @date

insert into tsjpd 
select ('SP'+Convert(char(6),GetDate(),112)+Right('0000'+(substring(a.fid,3,5)),4)) as fid,a.fid as fsjxh,a.fjh,a.fkt,a.fsg,a.fcf,a.fwxtk,a.falsales as fsale
 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)所有類型手機的的進貨入庫數(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è)置查詢的日期段也就是手機進貨的日期，就能查出這一時段所有進貨的手機了
						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)所有類型手機的的進貨入庫數(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
										--加上時間條件，就能找到這一個月的所有退庫未銷手機
									) 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è)置查詢的日期段也就是手機賣手機的日期，就能查出這一時段所有賣出的手機了
						GROUP BY dbo.thrc.fsjxh
						ORDER BY dbo.thrc.fsjxh
) b on ltrim(rtrim(a.fid))=ltrim(rtrim(b.fsjxhid))
		
) a

--刪掉統(tǒng)計過程中所有沒有沒有任何變貨的數(shù)據(jù)項
delete from tsjpd where fjh=0 and fkt=0 and fsg=0 and fcf=0 and ftc=0 and fxc=0