保价机查询.sql

来自「手机进销存系统Delphi源码,管理手机的进货还有销售方面的功能」· SQL 代码 · 共 88 行

declare @fdate datetime--开始日期
declare @edate datetime-- 结束日期
declare @sjxh   char(5)
declare @tempid   char(12)
declare @temp   char(20)
declare @count int

set @fdate='2000-01-01'
set @edate='2005-03-07'
set @sjxh='XH005'
declare @tmp table

(hrxx char(12),
 m char(20)

)

delete tpztemp

insert into tpztemp(fhrxx,fmon,fdate,fcolor,flx,fpz,fname)
--找手机串号流水号没有被销售的记录
select b.fid,b.fmon,b.fjhdate,b.fsjno,b.fhylx,b.fjbpz,b.fcolor from  
(
--以串号找HRXX 以防有些手机销售后，被退货而重新入库
select a.*,thrxx.fid,thrc.fid as fhrcid ,thylx.fname as fhylx,tcolor.fcolor as fcolor,thrxx.fmon,tjbpz.fname as fjbpz from (

--得到所有的手机串号和串号流水号一段时间内的
select  thrxx.fno as fsjno,thrc.fdate as fjhdate
from thrxx,thrc
where    thrxx.fhrcid=thrc.fid and (thrc.fhylx='JH' or thrc.fhylx='CF')--and thrc.fdate>=@fdate and thrc.fdate<=@edate and thrc.fsjxh=@sjxh

--添加时间限制和型号限制
) a ,thrxx,thrc,thylx,tcolor,tjbpz
where thrxx.fno=a.fsjno and thrc.fid=thrxx.fhrcid and thylx.fid=thrc.fhylx and tcolor.fid=thrxx.fcolor and tjbpz.fid=thrc.fjbpz 
)b 
where (b.fid not in(select fcode from thxc) and b.fid not in (select fhrxx from tbs))or (b.fid in (select fhrxx from tbs where tbs.fid not in (select fbs from tbsfh)))
		--or(b.fid in(select fcode from thxc) and b.fid in(select fhrxx from tbs where fid not in(select fbs from tbsfh)))


--select * from tpztemp

DECLARE tpz SCROLL CURSOR FOR
SELECT fhrxx FROM tpztemp
OPEN tpz
FETCH first FROM tpz
into @tempid
WHILE @@FETCH_STATUS = 0
BEGIN
	
		
		set @count=(select count(*)from thrxx where thrxx.fid =@tempid and thrxx.fid not in (select fhrxx from tgtlh )and thrxx.fid not in(select fhrxx from tbs ))
			if @count>0
			begin
				insert into @tmp(hrxx,m)values(@tempid,'本公司存货')

			end
		set @count=(select count(*) from thrxx where thrxx.fid=@tempid and thrxx.fid  in(select fhrxx from tgtlh where tgtlh.fid not in(select fgl from tgtth)))  
			if @count>0
			--update tpztemp set fyz=(select tgt.fname from tgt,tgtlh where tgtlh.fgt=tgt.fid and tgtlh.fhrxx=@tempid)where fhrxx=@tempid and exists(select count(*) from thrxx where thrxx.fid=@tempid and thrxx.fid  in(select fhrxx from tgtlh where tgtlh.fid not in(select fgl from tgtth)))
			begin
				set @temp='在代理商'+(select tgt.fna from tgt,tgtlh where tgtlh.fgt=tgt.fid and tgtlh.fhrxx=@tempid)
				insert into @tmp(hrxx,m)values(@tempid,@temp)
			end
		set @count=(select count(*) from thrxx where thrxx.fid=@tempid and thrxx.fid in(select fhrxx from tbs where tbs.fid not in(select fbs from tbsfh)))
			if @count>0
			--update tpztemp set fyz='在报损的路上' where fhrxx=@tempid and exists(select count(*) from thrxx where thrxx.fid=@tempid and thrxx.fid in(select fhrxx from tbs where tbs.fid not in(select fbs from tbsfh)))
				
			begin
				insert into @tmp(hrxx,m)values(@tempid,'在报损的路上')
			end

		set @count=(select count(*) from thrxx where thrxx.fid=@tempid and thrxx.fid in(select fhrxx from tgtlh where fid in(select fgl from tgtth)) and thrxx.fid not in(select fhrxx from tbs))
			if @count>0
			begin
				insert into @tmp(hrxx,m)values(@tempid,'代理退领在本公司')
			end
   FETCH NEXT FROM tpz
   into @tempid
END		
CLOSE tpz
DEALLOCATE tpz
select hrxx,count(*) from @tmp
group  by hrxx
order by hrxx

--update tpztemp set fyz=(select m from @tmp where hrxx=tpztemp.fhrxx)
--select a.fhrxx as fid,a.fcolor as fno,a.fmon as fjhmon,a.flx as fhylx,a.fpz as fjbpz,a.fyz as fwhere,a.fdate as fjhdate ,a.fname as fcolor from tpztemp a