cpyjc.sql

仓库管理信息系统

declare @nf integer,@yf integer 
set @nf=2003
set @yf=08

/*当月期初\进仓、出仓清0*/
update 产品每月结存表 set 上月数量=0,上月金额=0,进仓数量=0,
出仓数量=0,进仓金额=0,出仓金额=0, 本月数量=0,本月金额=0
where 年份=@nf and 月份=@yf
/*添加当月新产品*/
insert into dbo.产品每月结存表(年份,月份,产品id,分类id,名称规格,单位) 
select @nf,@yf,产品表.产品id,产品表.分类id,产品表.名称规格,产品表.单位 from 产品表 left join 
(select 产品id from dbo.产品每月结存表 
where  dbo.产品每月结存表.年份=@nf and 
dbo.产品每月结存表.月份=@yf) as cpnb
on 产品表.产品id=cpnb.产品id where cpnb.产品id is null
/*获取期初数*/
update 产品每月结存表 set 上月数量=mnqc.初始余量,
上月金额=mnqc.初始余额 
from (select 产品id,初始余量,初始余额 from 产品每年库存表 
      where 年份=@nf) as mnqc
where 年份=@nf and 月份=@yf and 
mnqc.产品id=产品每月结存表.产品id
/*上月结存*/
update 产品每月结存表 set 上月数量=上月数量+djhz.数量,
上月金额=上月金额+djhz.金额 from 
(select 产品ID,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.产品id,d.数量,d.金额 from dbo.成品进仓单 m 
inner join dbo.成品进仓明细表 d 
on m.单据号=d.单据号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)<@yf) as DJ
group by 产品ID) AS DJHZ 
where 产品每月结存表.年份=@nf and 产品每月结存表.月份=@yf and
产品每月结存表.产品ID=DJHZ.产品ID

update 产品每月结存表 set 上月数量=上月数量-djhz.数量,
上月金额=上月金额-djhz.金额 from
(select 产品ID,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.产品id,d.数量,d.金额 from dbo.成品出仓单 m 
inner join dbo.成品出仓明细表 d 
on m.单据号=d.单据号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)<@yf) as DJ
group by 产品ID) AS DJHZ 
where 产品每月结存表.年份=@nf and 产品每月结存表.月份=@yf and
产品每月结存表.产品ID=DJHZ.产品ID
/*更新进仓*/
update 产品每月结存表 set 进仓数量=djhz.数量,进仓金额=djhz.金额 from 
(select 产品ID,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.产品id,d.数量,d.金额 from dbo.成品进仓单 m 
inner join dbo.成品进仓明细表 d 
on m.单据号=d.单据号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)=@yf) as DJ
group by 产品ID) AS DJHZ 
where 产品每月结存表.年份=@nf and 产品每月结存表.月份=@yf and
产品每月结存表.产品ID=DJHZ.产品ID
/*更新出仓*/
update 产品每月结存表 set 出仓数量=djhz.数量,出仓金额=djhz.金额 from 
(select 产品ID,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.产品id,d.数量,d.金额 from dbo.成品出仓单 m 
inner join dbo.成品出仓明细表 d 
on m.单据号=d.单据号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)=@yf) as DJ
group by 产品ID) AS DJHZ 
where 产品每月结存表.年份=@nf and 产品每月结存表.月份=@yf and
产品每月结存表.产品ID=DJHZ.产品ID
/*更新库存*//*获取期初数*/
update 产品每月结存表 set  本月数量=mnqc.初始余量,
本月金额=mnqc.初始余额 
from (select 产品id,初始余量,初始余额 from 产品每年库存表 
      where 年份=@nf) as mnqc
where 年份=@nf and 月份=@yf and 
mnqc.产品id=产品每月结存表.产品id
/*本月结存*/
update 产品每月结存表 set 本月数量=本月数量+djhz.数量,
本月金额=本月金额+djhz.金额 from
(select 产品ID,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.产品id,d.数量,d.金额 from dbo.成品进仓单 m 
inner join dbo.成品进仓明细表 d 
on m.单据号=d.单据号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)<=@yf) as DJ
group by 产品ID) AS DJHZ 
where 产品每月结存表.年份=@nf and 产品每月结存表.月份=@yf and
产品每月结存表.产品ID=DJHZ.产品ID

update 产品每月结存表 set 本月数量=本月数量-djhz.数量,
本月金额=本月金额-djhz.金额 from
(select 产品ID,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.产品id,d.数量,d.金额 from dbo.成品出仓单 m 
inner join dbo.成品出仓明细表 d 
on m.单据号=d.单据号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)<=@yf) as DJ
group by 产品ID) AS DJHZ 
where 产品每月结存表.年份=@nf and 产品每月结存表.月份=@yf and
产品每月结存表.产品ID=DJHZ.产品ID
GO