51CTO下载-10[1].0重算存货总账脚本

select * into ia_summarybak from ia_summary

delete Ia_summary

--按月创建总账
declare @iastyle as varchar(60)
declare @iyear as int
select @iyear =RIGHT(db_name(),4)
select @iastyle=cvalue from accinformation where csysid='ia' and cname='cValueStyle'
if @iastyle='按仓库核算'
begin
Insert Into Ia_summary(
cWhCode,
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
cWhCode,
inventory.cInvCode,imonth,
sum(cast(iainquantity as decimal(38,6))) As innum,sum(cast(iaoutquantity as decimal(38,6))) As outnum,IsNull(sum(cast(iainquantity as decimal(38,6))),0) -IsNull(sum(cast(iaoutquantity as decimal(38,6))),0) As inum,
sum(iainPrice) As InPrice,sum(iaoutprice) As OutPrice,IsNull(sum(iainPrice),0) -IsNull(sum(iaoutprice),0) As iPrice,
sum(Case When brdflag = 1 Then Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) Else 0 End) As InDif,
sum(Case When brdflag = 0 Then Isnull(icreditdifcost,0) - IsNull(idebitdifcost,0) Else 0 End) As OutDif,
sum(Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) ) As iDif,Case When cBustype like '直运%' Then 1 Else 0 End As idir,0,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End As cFree1, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End As cFree2,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End As cFree3, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End As cFree4,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End As cFree5, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End As cFree6,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End As cFree7, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End As cFree8,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End As cFree9, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End As cFree10,
max(iTrans)
From ia_subsidiary
Left Join inventory on ia_subsidiary.cinvcode= inventory.cinvcode
where cvoutype <> '33'
group by
cWhCode,
inventory.cInvCode,imonth,Case When cBustype like '直运%' Then 1 Else 0 End ,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End
end
if @iastyle='按部门核算'
begin
Insert Into Ia_summa

ry(
cdepcode,
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
cdepcode,
inventory.cInvCode,imonth,
sum(cast(iainquantity as decimal(38,6))) As innum,sum(cast(iaoutquantity as decimal(38,6))) As outnum,IsNull(sum(cast(iainquantity as decimal(38,6))),0) -IsNull(sum(cast(iaoutquantity as decimal(38,6))),0) As inum,
sum(iainPrice) As InPrice,sum(iaoutprice) As OutPrice,IsNull(sum(iainPrice),0) -IsNull(sum(iaoutprice),0) As iPrice,
sum(Case When brdflag = 1 Then Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) Else 0 End) As InDif,
sum(Case When brdflag = 0 Then Isnull(icreditdifcost,0) - IsNull(idebitdifcost,0) Else 0 End) As OutDif,
sum(Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) ) As iDif,Case When cBustype like '直运%' Then 1 Else 0 End As idir,0,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End As cFree1, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End As cFree2,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End As cFree3, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End As cFree4,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End As cFree5, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End As cFree6,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End As cFree7, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End As cFree8,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End As cFree9, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End As cFree10,
max(iTrans)
From ia_subsidiary
Left Join inventory on ia_subsidiary.cinvcode= inventory.cinvcode
where cvoutype <> '33'
group by
cdepcode,
inventory.cInvCode,imonth,Case When cBustype like '直运%' Then 1 Else 0 End ,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End

end
if @iastyle='按存货核算'
begin
Insert Into Ia_summary(
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
inventory.cInvCode,imonth,
sum(cast(iainquantity as decimal(38,6))) As innum,sum(cast(iaoutquantity as decimal(38,6)))

As outnum,IsNull(sum(cast(iainquantity as decimal(38,6))),0) -IsNull(sum(cast(iaoutquantity as decimal(38,6))),0) As inum,
sum(iainPrice) As InPrice,sum(iaoutprice) As OutPrice,IsNull(sum(iainPrice),0) -IsNull(sum(iaoutprice),0) As iPrice,
sum(Case When brdflag = 1 Then Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) Else 0 End) As InDif,
sum(Case When brdflag = 0 Then Isnull(icreditdifcost,0) - IsNull(idebitdifcost,0) Else 0 End) As OutDif,
sum(Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) ) As iDif,Case When cBustype like '直运%' Then 1 Else 0 End As idir,0,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End As cFree1, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End As cFree2,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End As cFree3, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End As cFree4,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End As cFree5, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End As cFree6,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End As cFree7, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End As cFree8,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End As cFree9, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End As cFree10,
max(iTrans)
From ia_subsidiary
Left Join inventory on ia_subsidiary.cinvcode= inventory.cinvcode
where cvoutype <> '33'
group by
inventory.cInvCode,imonth,Case When cBustype like '直运%' Then 1 Else 0 End ,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End

end
declare @i int
declare @MaxMonth int
declare @MinMonth int
declare @StartMonth int

set @StartMonth = (select IsNull(Min(imonth),0) from Ia_Subsidiary Where imonth <>0)
set @MinMonth = (select Min(imonth) from Ia_summary Where imonth <>0)
set @MaxMonth = (select Max(iperiod) from gl_mend where bflag_ia=1 )
if not @MinMonth is null
begin
--处理期初数据
--更新已存在数据
Update Ia_summary Set iNum = IsNull(cast(Ia_summary.iNum as decimal(38,6)),0) + IsNull(cast(Pre.iNum as decimal(38,6)),0), iMoney = IsNull(Ia_summary.iMoney, 0) + IsNull(Pre.iMoney,0),
iDif = IsNull(Ia_summary.iDif,0) + IsNull(Pre.iDif,0)
From Ia_summary
Left Join Ia_summary Pre On
isnull(Ia_summary.cWhCode,'') = isnull(Pre.cWhCode,'')

And
isnull(ia_summary.cdepcode,'')=isnull(pre.cdepcode,'') and
Ia_summary.cInvCode = Pre.cInvCode And Ia_summary.iDirect = Pre.iDirect
And IsNull(Ia_summary.cFree1, '') = IsNull(Pre.cFree1, '') And IsNull(Ia_summary.cFree2, '') = IsNull(Pre.cFree2, '')
And IsNull(Ia_summary.cFree3, '') = IsNull(Pre.cFree3, '') And IsNull(Ia_summary.cFree4, '') = IsNull(Pre.cFree4, '')
And IsNull(Ia_summary.cFree5, '') = IsNull(Pre.cFree5, '') And IsNull(Ia_summary.cFree6, '') = IsNull(Pre.cFree6, '')
And IsNull(Ia_summary.cFree7, '') = IsNull(Pre.cFree7, '') And IsNull(Ia_summary.cFree8, '') = IsNull(Pre.cFree8, '')
And IsNull(Ia_summary.cFree9, '') = IsNull(Pre.cFree9, '') And IsNull(Ia_summary.cFree10, '') = IsNull(Pre.cFree10, '')
and Pre.imonth = 0
where Ia_summary.imonth = @MinMonth

--插入下月不存在的数据
Insert Into Ia_summary(
cWhCode,cdepcode,
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
Pre.cWhCode,pre.cdepcode,
Pre.cInvCode,@MinMonth,
null As innum,null As outnum,cast(Pre.inum as decimal(38,6)) As inum,
null As InPrice,null As OutPrice,Pre.iMoney As iMoney,
null As InDif,null As OutDif,Pre.iDif As iDif,
Pre.iDirect As iDirect,0,
Pre.cFree1, Pre.cFree2,
Pre.cFree3, Pre.cFree4,
Pre.cFree5, Pre.cFree6,
Pre.cFree7, Pre.cFree8,
Pre.cFree9, Pre.cFree10,
Pre.iTrans
From Ia_summary Pre
Left Join Ia_summary On
isnull(Ia_summary.cWhCode,'') = isnull(Pre.cWhCode,'') And isnull(Ia_summary.cdepcode,'') = isnull(Pre.cdepcode,'')
and Ia_summary.cInvCode = Pre.cInvCode And Ia_summary.iDirect = Pre.iDirect
And IsNull(Ia_summary.cFree1, '') = IsNull(Pre.cFree1, '') And IsNull(Ia_summary.cFree2, '') = IsNull(Pre.cFree2, '')
And IsNull(Ia_summary.cFree3, '') = IsNull(Pre.cFree3, '') And IsNull(Ia_summary.cFree4, '') = IsNull(Pre.cFree4, '')
And IsNull(Ia_summary.cFree5, '') = IsNull(Pre.cFree5, '') And IsNull(Ia_summary.cFree6, '') = IsNull(Pre.cFree6, '')
And IsNull(Ia_summary.cFree7, '') = IsNull(Pre.cFree7, '') And IsNull(Ia_summary.cFree8, '') = IsNull(Pre.cFree8, '')
And IsNull(Ia_summary.cFree9, '') = IsNull(Pre.cFree9, '') And IsNull(Ia_summary.cFree10, '') = IsNull(Pre.cFree10, '')
and Ia_summary.imonth = @MinMonth
where Pre.imonth = 0 and Ia_summary.autoid is null

While @MinMonth <= @MaxMonth
begin
Update Ia_summary Set iNum = IsNull(cast(Ia_summary.iNum as decimal(38,6)), 0) + IsNull(cast(Pre.iNum as decimal(38,6)), 0),iMoney = IsNull(Ia_summary.iMoney, 0) + IsNull(Pre.iMoney, 0),
iDif = IsNull(Ia_summary.iDif, 0) + IsNull(Pre.iDif, 0)
From Ia_summary
Left Join Ia_summary Pre On
isnull(Ia_summary.cWhCode,'') = isnull(Pre.cWhCode,'') And isnull(Ia_summary.cdepcode,'') = isnull(Pre.cdepcode,'') and
Ia_summary.cInvCode = Pre.cInvCode And Ia_summary.iDirect = Pre.iDirect

And IsNull(Ia_summary.cFree1, '') = IsNull(Pre.cFree1, '') And IsNull(Ia_summary.cFree2, '') = IsNull(Pre.cFree2, '')
And IsNull(Ia_summary.cFree3, '') = IsNull(Pre.cFree3, '') And IsNull(Ia_summary.cFree4, '') = IsNull(Pre.cFree4, '')
And IsNull(Ia_summary.cFree5, '') = IsNull(Pre.cFree5, '') And IsNull(Ia_summary.cFree6, '') = IsNull(Pre.cFree6, '')
And IsNull(Ia_summary.cFree7, '') = IsNull(Pre.cFree7, '') And IsNull(Ia_summary.cFree8, '') = IsNull(Pre.cFree8, '')
And IsNull(Ia_summary.cFree9, '') = IsNull(Pre.cFree9, '') And IsNull(Ia_summary.cFree10, '') = IsNull(Pre.cFree10, '')
and Pre.imonth = @MinMonth
where Ia_summary.imonth = @MinMonth + 1

----插入下月不存在的数据
Insert Into Ia_summary(
cWhCode,cdepcode,
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
Pre.cWhCode,pre.cdepcode,
Pre.cInvCode,@MinMonth+1,
null As innum,null As outnum,cast(Pre.inum as decimal(38,6)) As inum,
null As InPrice,null As OutPrice,Pre.iMoney As iMoney,
null As InDif,null As OutDif,Pre.iDif As iDif,
Pre.iDirect As iDirect,0,
Pre.cFree1, Pre.cFree2,
Pre.cFree3, Pre.cFree4,
Pre.cFree5, Pre.cFree6,
Pre.cFree7, Pre.cFree8,
Pre.cFree9, Pre.cFree10,
Pre.iTrans
From Ia_summary Pre
Left Join Ia_summary On
isnull(Ia_summary.cWhCode,'') = isnull(Pre.cWhCode,'') And isnull(Ia_summary.cdepcode,'') = isnull(Pre.cdepcode,'') And
Ia_summary.cInvCode = Pre.cInvCode And Ia_summary.iDirect = Pre.iDirect
And IsNull(Ia_summary.cFree1, '') = IsNull(Pre.cFree1, '') And IsNull(Ia_summary.cFree2, '') = IsNull(Pre.cFree2, '')
And IsNull(Ia_summary.cFree3, '') = IsNull(Pre.cFree3, '') And IsNull(Ia_summary.cFree4, '') = IsNull(Pre.cFree4, '')
And IsNull(Ia_summary.cFree5, '') = IsNull(Pre.cFree5, '') And IsNull(Ia_summary.cFree6, '') = IsNull(Pre.cFree6, '')
And IsNull(Ia_summary.cFree7, '') = IsNull(Pre.cFree7, '') And IsNull(Ia_summary.cFree8, '') = IsNull(Pre.cFree8, '')
And IsNull(Ia_summary.cFree9, '') = IsNull(Pre.cFree9, '') And IsNull(Ia_summary.cFree10, '') = IsNull(Pre.cFree10, '')
and Ia_summary.imonth = @MinMonth + 1
where Pre.imonth = @MinMonth and Ia_summary.autoid is null
set @MinMonth = @MinMonth + 1
end
end
else
begin
Insert Into Ia_summary(
cWhCode,cdepcode,
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
Pre.cWhCode,
pre.cdepcode,
Pre.cInvCode,@StartMonth,
null As innum,null As outnum,cast(Pre.inum as decimal(38,6)) As inum,
null As InPrice,null As OutPrice,Pre.iMoney As iMoney,
null As InDif,null As OutDif,Pre.iDif As iDif,
Pre.iDirect As iDirect,0,
Pre.cFree1, Pre.cFree2,
Pre.cFree3 , Pre.cFr

ee4,
Pre.cFree5, Pre.cFree6,
Pre.cFree7, Pre.cFree8,
Pre.cFree9, Pre.cFree10,
Pre.iTrans
From Ia_summary Pre
Left Join Ia_summary On
isnull(Ia_summary.cWhCode,'') = isnull(Pre.cWhCode,'') And isnull(Ia_summary.cdepcode,'') = isnull(Pre.cdepcode,'') And
Ia_summary.cInvCode = Pre.cInvCode And Ia_summary.iDirect = Pre.iDirect
And IsNull(Ia_summary.cFree1, '') = IsNull(Pre.cFree1, '') And IsNull(Ia_summary.cFree2, '') = IsNull(Pre.cFree2, '')
And IsNull(Ia_summary.cFree3, '') = IsNull(Pre.cFree3, '') And IsNull(Ia_summary.cFree4, '') = IsNull(Pre.cFree4, '')
And IsNull(Ia_summary.cFree5, '') = IsNull(Pre.cFree5, '') And IsNull(Ia_summary.cFree6, '') = IsNull(Pre.cFree6, '')
And IsNull(Ia_summary.cFree7, '') = IsNull(Pre.cFree7, '') And IsNull(Ia_summary.cFree8, '') = IsNull(Pre.cFree8, '')
And IsNull(Ia_summary.cFree9, '') = IsNull(Pre.cFree9, '') And IsNull(Ia_summary.cFree10, '') = IsNull(Pre.cFree10, '')
and Ia_summary.imonth = @StartMonth
where Pre.imonth = 0 and Ia_summary.autoid is null
end

--
-- delete Ia_summary

----调整期末处理标识
update ia_summary set iperiod=1 from gl_mend where ia_summary.imonth=gl_mend.iperiod
and gl_mend.bflag_ia=1
----调整年度标识
update ia_summary set iYear=@iyear

---调整平均单价
--select * from ia_summary where iperiod=1
--update ia_summary set iunitprice=cast(imoney/inum as decimal(38,6)) from warehouse join ia_summary
--on ia_summary.cwhcode=warehouse.cwhcode
--where cwhvaluestyle='全月平均法'
--and iperiod=1

declare @cur cursor
declare @cwhvaluestyle as varchar(60)
declare @whcode as varchar(60)
--declare @iastyle as varchar(60)
declare @cinvcode as varchar(60)

select @iastyle=cvalue from accinformation where csysid='ia' and cname='cValueStyle'

if @iastyle='按存货核算'
begin
--if @cwhvaluestyle='全月平均法'
update ia_summary set iunitprice=cast(Case When iNum = 0 Then 0 Else imoney/inum End as decimal(38,6))
from ia_summary join Inventory
on ia_summary.cInvcode=Inventory.cInvcode
where Inventory.cvaluetype = N'全月平均法' and iperiod=1

--if @cwhvaluestyle='移动平均法'
update ia_summary set iunitprice=cast(Case When iNum = 0 Then 0 Else imoney/inum End as decimal(38,6))
from ia_summary join Inventory
on ia_summary.cInvcode=Inventory.cInvcode
where Inventory.cvaluetype = N'移动平均法'

---if @cwhvaluestyle='计划价法'
update ia_summary set idifrate=cast(Case When imoney = 0 Then 0 when abs(idif/imoney)>1 then 0
Else idif/imoney end as decimal(38,6)) from Inventory join ia_summary
on ia_summary.cwhcode=Inventory.cInvcode
where ia_summary.cInvcode=Inventory.cInvcode and cvaluetype='计划价法' and iperiod=1
end

else
begin
--if @cwhvaluestyle='全月平均法'
update ia_summary set iunitprice=cast(Case When iNum = 0 Then 0 Else imoney/inum End as decimal(38,6))
from ia_summary

join warehouse
on ia_summary.cwhcode=warehouse.cwhcode
where warehouse.cwhvaluestyle = N'全月平均法' and iperiod=1

--if @cwhvaluestyle='移动平均法'
update ia_summary set iunitprice=cast(Case When iNum = 0 Then 0 Else imoney/inum End as decimal(38,6))
from ia_summary join warehouse
on ia_summary.cwhcode=warehouse.cwhcode
where warehouse.cwhvaluestyle = N'移动平均法'

---if @cwhvaluestyle='计划价法'
update ia_summary set idifrate=cast(Case When imoney = 0 Then 0 when abs(idif/imoney)>1 then 0 Else idif/imoney end as decimal(38,6)) from warehouse join ia_summary
on Ia_summary.cwhcode=warehouse.cwhcode
where warehouse.cwhvaluestyle = N'计划价法' and iperiod=1
end