成某一时期，所有的进货，退货（未销），销售,库存表，.sql

手机进销存系统Delphi源码,管理手机的进货还有销售方面的功能

/*************************************************************************
功能：生成某一时期，所有的进货，退货（未销），销售,库存表，
      对于，库存的查询手机，必需从建帐日期开始才能查询准确
	注：进货，以进货日期为准，销售以销售日期为准，库存以领货日期为准,退货（指未销退货）以退货日期为准
说明：@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