所有手机库进货销售退库示回的总追加到盘点表中sql.sql

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

/*************************************
功能:
	每月月初进行自动盘点,
	把统计出来的信息,自动追加到TSJPD中去
	这样每次统计的时候就只需要将上一个月的库存调出来就行了,
	没有必要进行重新统计,统计上一个月的
        当新的一月一开始就统计原来上一个月的数据
作者:陈银军
时间:2005.8.22
***************************************/
declare @ndate char(6)
declare @date char(6)
set @ndate=Convert(char(6),GetDate(),112)
set @date=cast(cast(@ndate as int)-1 as char(6))
if cast(substring(@ndate,5,2)as int)=1
set @date=cast(cast(substring(@date,1,4)as int)-1 as char(4))+'12'
select @date

insert into tsjpd 
select ('SP'+Convert(char(6),GetDate(),112)+Right('0000'+(substring(a.fid,3,5)),4)) as fid,a.fid as fsjxh,a.fjh,a.fkt,a.fsg,a.fcf,a.fwxtk,a.falsales as fsale
 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

--删掉统计过程中所有没有没有任何变货的数据项
delete from tsjpd where fjh=0 and fkt=0 and fsg=0 and fcf=0 and ftc=0 and fxc=0