gusucode.com > 精典源码Delphi062：化工颜料生产管理(商业源码)源码程序 > 精典源码Delphi062：化工颜料生产管理(商业源码)源码程序/精典源码Delphi062：化工颜料生产管理(商业源码)/化工颜料生产管理/lds-source/doc/旧的存储过程.txt

    --用于月结计算
CREATE proc proc_MonthCalc @StartDate dateTime,@EndDate dateTime,@LastYear char(4),@LastMon char(2)
as
select code=t.code,name=t.name,quantity=sum(t.quantity)
from
(  select code=sd.sd_mat_code,name=sd.sd_mat_name,
     quantity=sum(case when sh.sh_type in (0,2) then sd.sd_quantity
                       when sh.sh_type in (1,3) then -sd.sd_quantity end)
  from  stock_detail sd,stock_head sh
  where (sd.sd_code=sh.sh_code)
  and (sh.sh_date between @StartDate and @EndDate)
  group by sd.sd_mat_code,sd.sd_mat_name
  union all
  select code=sm_code,name=sm_name,quantity=sm_quantity
  from stock_month where (sm_year=@LastYear) and (sm_month=@LastMon)
) t
group by t.code,t.name



GO

--------------------------------------------------------------------------
--@IsMaterial=1 ,material
--@IsMaterial=0,product
CREATE proc proc_MonthReport
 @IsMaterial int,@StartDate datetime, @EndDate datetime,@LastYear char(4),@LastMon char(2)
as

if (@IsMaterial=1)
begin
select occupation.code,occupation.name
   ,blncQty=balance.blncQty
   ,blncAmmount=balance.blncQty*m.m_price
   ,inQty=occupation.inQty
   ,inAmmount=occupation.inQty*m.m_price
   ,outQtyG=occupation.outQtyG
   ,outAmmountG=occupation.outQtyG*m.m_price
   ,outQtyD=occupation.outQtyD
   ,outAmmountD=occupation.outQtyd*m.m_price
   ,saveQty=isnull(balance.blncQty,0)+isnull(occupation.inQty,0)-isnull(occupation.outQtyG,0)-isnull(occupation.outQtyD,0)
   ,saveAmmount=(isnull(balance.blncQty,0)+isnull(occupation.inQty,0)-isnull(occupation.outQtyG,0)-isnull(occupation.outQtyD,0))*m.m_price

from
  (select code=sd.sd_mat_code,name=sd.sd_mat_name
         ,inqty=sum(case when sh.sh_type=0 then sd.sd_quantity end)
         ,outQtyG=sum(case when (sh.sh_type=1) and (sh.sh_dest='广州') then sd.sd_quantity end)
         ,outQtyD=sum(case when (sh.sh_type=1) and (sh.sh_dest='东莞') then sd.sd_quantity end )
  from stock_detail sd
  join stock_head sh on (sd.sd_code=sh.sh_code)
  where  (sh.sh_date between @StartDate and @EndDate) and (sh.sh_type in (0,1))
  group by sd.sd_mat_code,sd.sd_mat_name) occupation
  
left outer join 
  (select code=sm_code,name=sm_name,blncQty=sm_quantity from stock_month 
                   where (sm_Year=@LastYear)and (sm_month=@LastMon)) Balance
         on (balance.code=Occupation.code)
left join material m on (m.m_code=occupation.code)
union all
select code=sm.sm_code,name=sm.sm_name
      ,blncQty=sm.sm_quantity
      ,blncAmmount=sm.sm_quantity*m.m_price
      ,inQty=null,inAmmount=null
      ,outQtyG=null,outAmountG=null
      ,outQtyD=null,outAmountG=null
      ,SaveQty=sm.sm_quantity
      ,SaveAmmount=sm.sm_quantity*m.m_price
  from stock_month sm
  join material m on sm.sm_code=m.m_code
  where (sm.sm_year=@LastYear) and (sm.sm_month=@LastMon) and (sm.sm_code not in 
       (select distinct sd.sd_mat_code from stock_detail sd,stock_head sh 
                                   where (sd.sd_code=sh.sh_code)and (sh.sh_date between @StartDate and @EndDate)))
       and (sm.sm_code in (select m_code from material)) 
end
else
--计算成品月报
begin
select occupation.code,occupation.name
   ,blncQty=balance.blncQty
   ,blncAmmount=balance.blncQty*m.m_price
   ,inQty=occupation.inQty
   ,inAmmount=occupation.inQty*m.m_price
   ,outQtyG=occupation.outQtyG
   ,outAmmountG=occupation.outQtyG*m.m_price
   ,outQtyD=0.0
   ,outAmmountD=0.0
   ,saveQty=isnull(balance.blncQty,0)+isnull(occupation.inQty,0)-isnull(occupation.outQtyG,0)
   ,saveAmmount=(isnull(balance.blncQty,0)+isnull(occupation.inQty,0)-isnull(occupation.outQtyG,0))*m.m_price

from
  (select code=sd.sd_mat_code,name=sd.sd_mat_name
         ,inqty=sum(case when sh.sh_type=2 then sd.sd_quantity end)
         ,outQtyG=sum(case when (sh.sh_type=3) then sd.sd_quantity end)
         ,outQtyD=null
  from stock_detail sd
  join stock_head sh on (sd.sd_code=sh.sh_code)
  where  (sh.sh_date between @StartDate and @EndDate) and (sh.sh_type in (2,3))
  group by sd.sd_mat_code,sd.sd_mat_name) occupation
  
left outer join 
  (select code=sm_code,name=sm_name,blncQty=sm_quantity from stock_month 
                   where (sm_Year=@LastYear)and (sm_month=@LastMon)) Balance
         on (balance.code=Occupation.code)
left join material m on (m.m_code=occupation.code)
union all
--包含本期未发生，但上期有月结的库存成品
select code=sm.sm_code,name=sm.sm_name
      ,blncQty=sm.sm_quantity
      ,blncAmmount=sm.sm_quantity*m.m_price
      ,inQty=null,inAmmount=null
      ,outQtyG=null,outAmountG=null
      ,outQtyD=null,outAmountG=null
      ,SaveQty=sm.sm_quantity
      ,SaveAmmount=sm.sm_quantity*m.m_price
  from stock_month sm
  join material m on sm.sm_code=m.m_code
  where (sm.sm_year=@LastYear) and (sm.sm_month=@LastMon)
     and (sm.sm_code not in 
       (select distinct sd.sd_mat_code from stock_detail sd,stock_head sh 
                                   where (sd.sd_code=sh.sh_code)and (sh.sh_date between @StartDate and @EndDate)))
       and (sm.sm_code in (select muh_code from make_up_head))
end




GO