手机利润计算单元.sql

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

/*************************************************************************
功能：act 为2生成(一定时间内的所有进货)手机的销售情况
    act 为1 生成一定时间内的手机销售情况
      对于，库存的查询手机，必需从建帐日期开始才能查询准确
	注：进货，以进货日期为准，销售以销售日期为准，库存以领货日期为准,退货（指未销退货）以退货日期为准
说明：
     @sear  查询的报表类型1.总表，２。分类型，３。详细报表
     @sjxh需要查找的手机型号

************************************************************************/

declare @Ffdate datetime
declare @fedate datetime
declare @sear  int
declare @act int
declare @sjxh char(5)
declare @temptable table
(
fid char(12)

)

set @act=1
set @sear=2
set @Ffdate='2001-01-01'
set @fedate='2005-12-30'
--drop table #temp
--select * from thxc
set @sjxh='xh001'
if @sjxh='ALL'
set @sjxh='%%'
if @act=1--一段时间内的销售情况 
	begin
		insert into @temptable
		select thxc.fid  
		from thxc,thrxx,thrc
		where thxc.fdate>=@ffdate and thxc.fdate<=@fedate
			and thxc.fcode not in(select fhrxx from tkhth) 
			and thxc.fcode =thrxx.fid and thrxx.fhrcid=thrc.fid 
			and thrc.fsjxh like ltrim(rtrim(@sjxh))
	end
if @act=2 
	begin
		insert into @temptable
		select thxc.fid
		from thxc 
		where thxc.fcode in
					(select fid 
					 from thrxx 
					 where thrxx.fhrcid in
								(select fid 
								 from thrc 
								 where fdate>=@ffdate and fdate<=@fedate and thrc.fsjxh like ltrim(rtrim(@sjxh))))
			and thxc.fcode not in(select fhrxx from tkhth) 
	end

if @sear=1
select sum(b.fmon-b.fjhmon) as flr 
from @temptable a,thxc b
where a.fid=b.fid
if @sear=2
	begin
		select tsjcs.fna as fsjcs,tsjxh.fmodel as fsjxh,thrxx.fno,tjbpz.fname as fjbpz,tcolor.fcolor,b.fjhmon,b.fmon as fsalemon,(b.fmon-b.fjhmon) as flr,tygzl.fname as fygname,b.fname as fkhname,b.ftel as fkhtel,b.fdate as fdate,b.fmemo
		from @temptable a,thxc b,thrxx,tcolor,thrc,tsjcs,tsjxh,tjbpz,tygzl
		where a.fid=b.fid and thrxx.fid=b.fcode and thrxx.fcolor=tcolor.fid
			and thrxx.fhrcid=thrc.fid and tsjcs.fid=tsjxh.fsjcs and thrc.fsjxh=tsjxh.fid
			and tjbpz.fid=thrc.fjbpz and tygzl.fid=b.fperson
		--order by 
	end