/*************************************************************************
功能：生成某一時期，所有的進貨，退貨（未銷），銷售,庫存表，
      對于，庫存的查詢手機，必需從建帳日期開始才能查詢準確
	注：進貨，以進貨日期為準，銷售以銷售日期為準，庫存以領貨日期為準,退貨（指未銷退貨）以退貨日期為準
說明：@fgt參數利用模糊查找模式,
	1。當@fGT的值為空串時，
		就執行查找所有的代理商資料
	2。當@fgt值不為空是，就直接查找相匹配的代理商資料
	3.沒有用到匹配安符，
     @flx類型,查找三種的類型，JH領貨，JX銷售，TH退貨,WX未退 ALL（所有類型）（存貨） 
     @sear  查詢的報表類型1.總表，２。分類型，３。詳細報表
     @sjxh需要查找的手機型號
     @hyd 貨源地
     @group 為1則是按型號貨源地為2按貨源地型號統計
************************************************************************/
declare @temp char(20)
declare @out  char(8000)
declare @Fdate datetime
declare @fedate datetime
declare @fgt   char(4)
declare @lx   char(3)
declare @sear  int
declare @sjxh  char(5)
declare @hyd   char(4)
declare @sql char(100)
DECLARE @GROUP INT
DECLARE @rclx char(2)--入庫類型（四種1.客戶退貨，2舊機收購3本月進貨4廠有返回）

declare @tempid  char(12)--用于檢測手機狀態時所設的用于存手機串號入庫流水號
declare @lxid    char(2)--用于存檢測手機狀態時所設的存發生行為的
DECLARE @STA   CHAR(100)--用于存檢測手機狀態時的狀態值
declare @count  int --存檢測時的臨時值

if exists (select * from sysobjects where name='tmp')
drop  table tmp
if exists (select * from sysobjects where name='tmp2')
drop  table tmp2


create  table tmp
(
fname char(20),
fcount int
)

create  table tmp2
(
fid char(10),
fn1 char(20),
fn1count int,
fn2 char(20),
fn2count int
)
DECLARE  @tmp2 TABLE
(
fid char(10),
fna char(20),
fname char(20),
fcount int,
fhrxx  char(12),
fdate   datetime
)
declare @statu table
(fhrxx char(12),
fsta char(100)
)


set @fdate='2001-01-01'
set @fedate='2005-01-01'
set @fgt='ALL'
SET @LX='ALL'
SET @SEAR=2
set @sjxh='ALL'
set @group=2
set @rclx='AL'

set @hyd='ALL'

if @rclx='AL'
   set @rclx='%%'

if @hyd='ALL'
	set @hyd='%HY%'
if @sjxh='ALL'
	SET @sjxh='%XH%'
if @lx='ALL'
	set @lx='%%'




--得到數據

insert  @tmp2
select 'JH',thrc.fsjxh,thrc.fhyd,1,thrxx.fid,thrc.fdate from thrxx,thrc where thrxx.fhrcid=thrc.fid
		and thrc.fhylx  like @rclx AND thrc.fsjxh like ltrim(rtrim(@sjxh)) AND thrc.fdate>=@fdate and thrc.fdate<=@fedate and thrc.fhyd  like ltrim(rtrim(@hyd))

insert @tmp2
select  'JX',thrc.fsjxh,thrc.fhyd ,1,thrxx.fid,thxc.fdate from thrxx,thrc,thxc where thrxx.fid=thxc.fcode and thrxx.fhrcid=thrc.fid
		and thrc.fhylx LIKE @rclx AND thrc.fsjxh like ltrim(rtrim(@sjxh)) AND thrc.fdate>=@fdate and thrc.fdate<=@fedate and thrc.fhyd  like ltrim(rtrim(@hyd))
insert @tmp2
select 'TH', thrc.fsjxh,thrc.fhyd ,1 ,thrxx.fid ,tbs.fdate from tbs,thrxx,thrc where tbs.fhrxx not in (select fcode from thxc) and thrc.fid=thrxx.fhrcid and tbs.fhrxx=thrxx.fid
		and thrc.fhylx LIKE @rclx AND thrc.fsjxh like ltrim(rtrim(@sjxh)) AND thrc.fdate>=@fdate and thrc.fdate<=@fedate and thrc.fhyd  like ltrim(rtrim(@hyd))
insert @tmp2
select 'WX',thrc.fsjxh,thrc.fhyd ,1 ,thrxx.fid,thrc.fdate from thrxx,thrc where thrxx.fhrcid=thrc.fid and thrxx.fid not in (select fcode from thxc) and thrxx.fid not in(select fhrxx from tbs)
		and thrc.fhylx LIKE @rclx AND thrc.fsjxh like ltrim(rtrim(@sjxh)) AND thrc.fdate>=@fdate and thrc.fdate<=@fedate and thrc.fhyd  like ltrim(rtrim(@hyd))



insert tmp
select fid,count(*) from @tmp2
group by fid





insert tmp
select fid,count(*) from @tmp2
group by fid

if @sear=1 
begin
select (
	case fname
		when 'JH' THEN '進貨'
		WHEN 'JX' THEN '銷售'
		when 'TH' then '退庫'
		when 'WX' then '庫存'
       end )as fname,fcount from tmp
where fname like @lx
end



if @group=1 --按型號貨源地統計
begin
INSERT TMP2
select a.fid,a.fna,fsjxhcount,b.fname,b.fhydcount
from(
select top 10000 fid,fna,count(*)as fsjxhcount from  @tmp2
group by fid,fna
order by fid)a left outer join(
select  top 10000 fid,fna,fname,count(*)as fhydcount from @tmp2
group by fid,fna,fname)b
on a.fid=b.fid and b.fna=a.fna
end



--按貨源地型號
if @group =2 
begin
insert tmp2
select a.fid,a.fname,a.fhydcount,b.fna,b.fsjxhcount
from (
select  top 10000 fid,fname,count(*)as fhydcount from  @tmp2
group by fid,fname
order by fid)a left outer join(
select  top 1000 fid,fname,fna,count(*) as fsjxhcount from @tmp2
group by fid, fname,fna
order by fid)b on a.fid=b.fid and a.fname=b.fname
end


if (@sear=2) and (@group=2) 
begin
select a.flxid,a.fname as flx,a.fcount as fallcount,b.fhyd as fhyd,b.fn1count as fhydcount,b.fsjcs as fsjcs,b.fsjxh as fsjxh,b.fn2count  as fsjxhcounnt
from (
select (
	case fname
		when 'JH' THEN '進貨'
		WHEN 'JX' THEN '銷售'
		when 'TH' then '退庫'
		when 'WX' then '庫存'
       end )as fname,fcount,fname as flxid from tmp)a ,(

select (
	case a.fid
		when 'JH' THEN '進貨'
		WHEN 'JX' THEN '銷售'
		when 'TH' then '退庫'
		when 'WX' then '庫存'
       end )as fid,thyd.fna as fhyd,a.fn1count,tsjcs.fna as fsjcs,tsjxh.fmodel as fsjxh ,fn2count from tmp2 a,thyd,tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and a.fn1=thyd.fid and tsjxh.fid=a.fn2
)b where ltrim(rtrim(a.fname))=ltrim(rtrim(b.fid)) and a.flxid like @lx
order by a.flxid,b.fsjcs,b.fsjxh,b.fhyd
end

if (@sear=2) and (@group=1) 
begin
select a.flxid,a.fname,a.fcount as fallcount,b.fsjcs as fsjcs,b.fsjxh as fsjxh,b.fsjxhcount as fsjxhcount ,b.fhyd as fhyd,b.fhydcount as fhydcount
from ( 
select (
	case fname
		when 'JH' THEN '進貨'
		WHEN 'JX' THEN '銷售'
		when 'TH' then '退庫'
		when 'WX' then '庫存'
       end )as fname,fcount,fname as flxid from tmp)a ,(
select 	
	(case a.fid
		when 'JH' THEN '進貨'
		WHEN 'JX' THEN '銷售'
		when 'TH' then '退庫'
		when 'WX' then '庫存'
        end )as fid,tsjcs.fna as fsjcs,tsjxh.fmodel as fsjxh,a.fn1count as fsjxhcount,thyd.fna as fhyd,a.fn2count as fhydcount
from tmp2 a,tsjcs,tsjxh,thyd
where a.fn1=tsjxh.fid and a.fn2=thyd.fid and tsjxh.fsjcs=tsjcs.fid) b 
where ltrim(rtrim(a.fname))=ltrim(rtrim(b.fid)) and a.flxid like @lx
order by a.flxid,b.fsjcs,b.fsjxh,b.fhyd
end

--查看所有的詳細信息：
--select * from tmp2
--select * from @tmp2
--得到每臺手機的詳細信息(除掉進貨的手機都查詢其現在的狀態縮小范圍)


DECLARE tpz SCROLL CURSOR FOR
select  fhrxx,fid from @tmp2 where fid<>'JH'
OPEN tpz
FETCH first FROM tpz
into @tempid,@lxid
WHILE @@FETCH_STATUS = 0
BEGIN
--跟蹤手機的使用狀態
	if @lxid='JX' 
		BEGIN
			set @sta='客戶正在使用'
			if (select count(*) from tkhth where fhrxx=@tempid)>0
				begin
					set @sta='客戶不滿意而退貨' 
			 		if (select count(*) from tbs where fhrxx=@tempid)>0
						set @sta='客戶由于機子故障退貨'
				end
			if (select count(*) from thhdd where fhrxx=@tempid)>0 
				begin
					set @sta='客戶正等待換機（無現貨）' 
					if (select count(*)from thhdd,thhlh where thhdd.fhrxx=@tempid and thhdd.fid=thhlh.fhhdd)>0
						set @sta='客戶已換機(無現貨)'
				end

		END	
	IF @LXID='TH'
		BEGIN
			set @sta='手機未銷售已退庫'
		END
	IF @LXID='WX'
		BEGIN
			set @sta='手機正在本公司存貨'
			if (select count(*) from  tgtlh where tgtlh.fhrxx=@tempid and fid not in(select fgl from tgtth))>0
				set @sta='手機正在'+(select fna from tgt where tgt.fid=(select fgt from tgtlh where fhrxx=@tempid))
		END
	insert into @statu(fhrxx,fsta)	values (@tempid,@sta)
   FETCH NEXT FROM tpz
   into @tempid,@lxid
END
CLOSE tpz
DEALLOCATE tpz
--select a.*,thrxx.fno from @statu a,thrxx where thrxx.fid=a.fhrxx






if (@sear=3)and (@group=1)
begin
select (case a.fname
		when 'JH' THEN '進貨'
		WHEN 'JX' THEN '銷售'
		when 'TH' then '退庫'
		when 'WX' then '庫存'
       end )as flx,a.fcount as fallcount,b.*
from
(select 
	 fname,fcount from tmp
)a,(
select top 10000 a.fid as flx,tsjcs.fna as fsjcs,tsjxh.fmodel as fsjxh,a.fn1count as fsjxhcount,thyd.fna as fhyd ,a.fn2count as fhydcount,b.fhrxx,b.fdate as flxdate,c.fcolor,c.fjbpz,c.fno,c.fhylx,s.fsta
 from tmp2 a,@tmp2 b,vSimpleSj c,tsjxh,tsjcs,thyd,@statu s
 where a.fid=b.fid and a.fn1=b.fna and a.fn2=b.fname and c.fid=b.fhrxx and a.fn1=tsjxh.fid and tsjcs.fid=tsjxh.fsjcs and a.fn2=thyd.fid and s.fhrxx=b.fhrxx 
order by a.fid,b.fsjxh
)b
where ltrim(rtrim(a.fname))=ltrim(rtrim(b.flx)) and a.fname like @lx
order by a.fname,b.fsjcs,b.fsjxh,b.fhyd
end
if (@sear=3)and (@group=2)
begin
select (case a.fname
		when 'JH' THEN '進貨'
		WHEN 'JX' THEN '銷售'
		when 'TH' then '退庫'
		when 'WX' then '庫存'
       end )as flx,a.fcount as fallcount,b.*
from
(select fname,fcount from tmp)a,(

select top 10000 a.fid as flx ,thyd.fna as fhyd,a.fn1count as fhydcount,tsjcs.fna as fsjcs,tsjxh.fmodel as fsjxh ,a.fn2count as fsjxhcount,b.fhrxx,b.fdate as flxdate,c.fcolor,c.fjbpz,c.fno,c.fhylx,s.fsta
 from tmp2 a,@tmp2 b,vSimpleSj c,thyd,tsjxh,tsjcs,@statu s
 where a.fid=b.fid and a.fn1=b.fname and a.fn2=b.fna and c.fid=b.fhrxx and thyd.fid=a.fn1 and tsjxh.fid=a.fn2 and tsjxh.fsjcs=tsjcs.fid and s.fhrxx=b.fhrxx 
order by a.fid,b.fhyd
)b
where ltrim(rtrim(a.fname))=ltrim(rtrim(b.flx)) and a.fname like @lx
 order by a.fname,b.fhyd,b.fsjcs,b.fsjxh
end

drop  table tmp

drop  table tmp2