所有手机库进货销售退库示回的总sql.sql

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

/************************************************************
功能:
	如果想得到某一个月的所有这些消息，有三个地方需要进行时间判定
	1.手机入库部分
	2.退库示回的部分
	3.手机销售部分
作者：陈银军
时间：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 
				   (
						--开始得到本月内所有类型手机的的进货入库数,包括所有的入库类型(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
							--设置查询的日期段也就是手机进货的日期，就能查出这一时段所有进货的手机了
						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
					
					--结束得到本月内所有类型手机的的进货入库数,包括所有的入库类型(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
							--设置查询的日期段也就是手机卖手机的日期，就能查出这一时段所有卖出的手机了
						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