set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[sp_Recalculate](@Thn varchar(4)= '',@Bln int=0)
As
Begin
Declare @strThn varchar(4)
Declare @strBln varchar(4)
Declare @strPriorBln varchar(4)
Declare @strNextThn varchar(4)
Declare @iBln int
Declare @iCurrentBln int
Declare @iThn int
Declare @MySQL NVarchar(1500)
Declare @CekJournal int
Declare @SaldoNoAcc4 numeric(18,4)
Declare @SaldoNoAcc314 numeric(18,4)
--set Tahun dan Bulan
if @Thn=''
begin
set @iThn = year(getdate())
Set @strThn= cast(@iThn as varchar(4))
end else Set @strThn= @Thn
Set @iThn=convert(int,@strThn)
if @iThn < year(getdate())
Set @iCurrentBln = 12
else
begin
if @Bln=0 set @iCurrentBln =month(getdate())
else
begin
if month(getdate()) =12 set @iCurrentBln= 12
else Set @iCurrentBln= @Bln
end
end
--Hapus master bulan awal yang saldoawal,mutasidebet dan mutasi kredit=0
Set @MySQL =' Delete from master01'+@strThn+' where (saldoawal=0) and (mutasidebet=0) and (mutasikredit=0)'
Exec sp_executeSQL @MySQL
--Truncate master berikutnya, setting master bulan awal
Declare @i int
Declare @strI varchar(2)
Set @i=1
While (@i <@iCurrentBln+1) --and (@iCurrentBln<12)
begin
Set @strI = cast(@i as varchar(2))
if len(@strI)=1 set @strI='0'+@strI
if @i=1
begin
Set @MySQL =' Update master'+@strI+@strThn+' set mutasidebet=0,mutasikredit=0,saldoakhir=isnull(saldoawal,0)'
Exec sp_executeSQL @MySQL
end
else
begin
Set @MySQL =' if object_id(''master'+@strI+@strThn+''') is not null Truncate table master'+@strI+@strThn
Exec sp_executeSQL @MySQL
end
set @i=@i+1
end
--Set Journal bulan awal group by noacc
Set @MySQL =' if object_id(''tempdb..##TempJournal'') is not null drop table ##TempJournal '+
' select noacc,sum(isnull(debet,0)) debet,sum(isnull(kredit,0)) kredit '+
' into ##TempJournal from journal01'+@strThn+' where noacc is not null group by noacc order by noacc'
Exec sp_executeSQL @MySQL
Declare @noacc varchar(20),@debet numeric(18,4),@kredit numeric(18,4)
Declare @saldoakhir numeric(18,4)
Declare @saldoawal numeric(18,4)
DECLARE @ParmDefinition NVARCHAR(500)
--looping untuk master bulan awal berdasar journal awal
Declare crRecalculate cursor scroll for
select * from ##TempJournal
open crRecalculate
fetch first from crRecalculate into @noacc,@debet,@kredit
while @@fetch_status=0
begin
-- update master bulan awal berdasar journal bulan awal
set @saldoakhir = @debet-@kredit
Set @MySQL=N'Update master01'+@strThn+' set mutasidebet=@debet1,mutasikredit=@kredit1,'+
'saldoakhir=saldoawal+@saldoakhir1 where noacc=@noacc1'
SET @ParmDefinition = N'@debet1 numeric(18,4),@kredit1 numeric(18,4),@saldoakhir1 numeric(18,4),@noacc1 varchar(20)'
Exec sp_executeSQL @MySQL, @ParmDefinition,@debet1 =@debet,@kredit1=@kredit,@saldoakhir1=@saldoakhir,@noacc1=@noacc
fetch next from crRecalculate into @noacc,@debet,@kredit
end
close crRecalculate
deallocate crRecalculate
--Looping master berikutnya berdasar journal bulan berikutnya
Set @iBln=2
While (@iBln < @iCurrentBln+1) --and (@iCurrentBln<12)
begin
Set @strBln=convert(varchar(2),@iBln)
if len(@strBln)=1 set @strBln='0'+@strBln
--cek journal jika belum ada keluar looping
Set @MySQL = ' if object_id(''Tempdb..##CekJournal'') is not null drop table ##CekJournal'+
' Select 0 Cek into ##CekJournal'+
' if object_id(''journal'+@strBln+@strThn+''') is null Update ##CekJournal set Cek=0 '+
' else Update ##CekJournal set Cek=1 '
Exec sp_executeSQL @MySQL
set @CekJournal=(Select Cek from ##CekJournal)
if @CekJournal=0 break
set @strPriorBln = cast((@iBln-1) as varchar(2))
if len(@strPriorBln)=1 set @strPriorBln='0'+@strPriorBln
-- insert master bulan yg di looping berdasar master sebelumnya
Set @MySQL =' Insert into master'+@strBln+@strThn+'(noacc,saldoawal,mutasidebet,mutasikredit,saldoakhir)'+
' Select noacc,case when noacc<''40000000'' then (isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) else 0 end ,0,0,case when noacc<=''40000000'' then (isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) else 0 end from master'+@strPriorBln+ @strThn
Exec sp_executeSQL @MySQL
-- ambil journal untuk bulan yg di looping
Set @MySQL =' if object_id(''tempdb..##TempJournal'') is not null drop table ##TempJournal'+
' select noacc,sum(isnull(debet,0)) debet,sum(isnull(kredit,0)) kredit '+
' into ##TempJournal from journal'+@strBln+@strThn+' where noacc is not null group by noacc order by noacc'
Exec sp_executeSQL @MySQL
Declare crRecalculate cursor scroll for
Select * from ##TempJournal
open crRecalculate
fetch first from crRecalculate into @noacc,@debet,@kredit
while @@fetch_status=0
begin
-- ambil saldo awal dari saldo akhir bulan sebelumnya
set @MySQL ='if object_id(''Tempdb..##TempSaldo'') is not null drop table ##TempSaldo select isnull(saldoakhir,0) saldoakhir into ##TempSaldo from master'+@strPriorBln+@strThn+
' where noacc= '''+@noacc+''''
Exec sp_executeSQL @MySQL
set @saldoawal =(select isnull(saldoakhir,0) from ##TempSaldo )
set @saldoakhir = @debet-@kredit
--setting master bulan yg di looping
if (@noacc <'40000000') and (@noacc <>'31404001')
begin
set @MySQL=N'if not exists(Select * from master'+@strBln+@strThn+' where noacc=@noacc1) '+
' insert into master'+@strBln+@strThn+'(noacc,saldoawal,mutasidebet,mutasikredit,saldoakhir) '+
' select @noacc1,0,@debet1,@kredit1,@saldoakhir1 else'+
' Update master'+@strBln+@strThn+' set saldoawal=isnull(@saldoawal1,0), mutasidebet=isnull(@debet1,0),mutasikredit=isnull(@kredit1,0),'+
' saldoakhir=isnull(@saldoawal1,0)+isnull(@saldoakhir1,0) where noacc=@noacc1'
SET @ParmDefinition = N'@debet1 numeric(18,4),@kredit1 numeric(18,4),@saldoawal1 numeric(18,4),@saldoakhir1 numeric(18,4),@noacc1 varchar(20)'
Exec sp_executeSQL @MySQL, @ParmDefinition,@debet1 =@debet,@kredit1=@kredit,@saldoawal1=@saldoawal,@saldoakhir1=@saldoakhir,@noacc1=@noacc
end
if (@noacc >='40000000')
begin
set @MySQL=N'if not exists(Select * from master'+@strBln+@strThn+' where noacc=@noacc1) '+
' insert into master'+@strBln+@strThn+'(noacc,saldoawal,mutasidebet,mutasikredit,saldoakhir) '+
' select @noacc1,0,@debet1,@kredit1,@saldoakhir1 else'+
' Update master'+@strBln+@strThn+' set saldoawal=0, mutasidebet=isnull(@debet1,0),mutasikredit=isnull(@kredit1,0),'+
' saldoakhir=isnull(@saldoakhir1,0) where noacc=@noacc1'
SET @ParmDefinition = N'@debet1 numeric(18,4),@kredit1 numeric(18,4),@saldoawal1 numeric(18,4),@saldoakhir1 numeric(18,4),@noacc1 varchar(20)'--
Exec sp_executeSQL @MySQL, @ParmDefinition,@debet1 =@debet,@kredit1=@kredit,@saldoawal1=@saldoawal,@saldoakhir1=@saldoakhir,@noacc1=@noacc
end
fetch next from crRecalculate into @noacc,@debet,@kredit
end
close crRecalculate
deallocate crRecalculate
-- setting current to year bulan looping
set @MySQL ='if object_id(''Tempdb..##TempSaldo'') is not null drop table ##TempSaldo select sum(isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) saldoakhir into ##TempSaldo from master'+@strPriorBln+@strThn+
' where noacc >= ''40000000'''
Exec sp_executeSQL @MySQL
set @SaldoNoAcc4 =(select isnull(saldoakhir,0) from ##TempSaldo)
set @MySQL ='if object_id(''Tempdb..##TempSaldo'') is not null drop table ##TempSaldo select sum(isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) saldoakhir into ##TempSaldo from master'+@strPriorBln+@strThn+
' where noacc = ''31404001'''
Exec sp_executeSQL @MySQL
set @SaldoNoAcc314 =(select isnull(saldoakhir,0) from ##TempSaldo)
set @SaldoNoAcc4 = isnull(@SaldoNoAcc4,0)+ isnull(@SaldoNoAcc314,0)
set @MySQL =N' Update master'+@strBln+@strThn+' set saldoawal=isnull(@SaldoNoAcc,0)'+
', saldoakhir=isnull(@SaldoNoAcc,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)'+
' where noacc= ''31404001'''
set @parmdefinition =N'@SaldoNoAcc Numeric(18,4)'
Exec sp_executeSQL @MySQL,@parmdefinition,@SaldoNoAcc=@SaldoNoAcc4
set @MySQL =' Update master'+@strPriorBln+@strThn+' set saldoawal=0,saldoakhir=isnull(mutasidebet,0)-isnull(mutasikredit,0)'+
' where noacc >= ''40000000'''
Exec sp_executeSQL @MySQL
Set @iBln=@iBln+1
end
--setting untuk 1 bulan setelah current bulan , belum akhir bulan untuk tahun itu
if (@iBln = @iCurrentBln+1) and (@iCurrentBln<12)
begin
Set @strBln=convert(varchar(2),@iBln)
if len(@strBln)=1 set @strBln='0'+@strBln
set @strPriorBln = cast((@iBln-1) as varchar(2))
if len(@strPriorBln)=1 set @strPriorBln='0'+@strPriorBln
Set @MySQL =' if object_id(''master'+@strBln+@strThn+''') is null select * into dbo.master'+@strBln+@strThn+' from master'
Exec sp_executeSQL @MySQL
Set @MySQL =' Truncate table master'+@strBln+@strThn
Exec sp_executeSQL @MySQL
Set @MySQL =' Insert into master'+@strBln+@strThn+'(noacc,saldoawal,mutasidebet,mutasikredit,saldoakhir)'+
' Select noacc,isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0),0,0,isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0) from master'+@strPriorBln+ @strThn
Exec sp_executeSQL @MySQL
set @mysql ='if object_id(''tempdb..##tempsaldo'') is not null drop table ##tempsaldo select sum(isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) saldoakhir into ##tempsaldo from master'+@strPriorBln+@strthn+
' where noacc >= ''40000000'''
exec sp_executesql @mysql
set @saldonoacc4 =(select isnull(saldoakhir,0) from ##tempsaldo)
set @MySQL ='if object_id(''Tempdb..##TempSaldo'') is not null drop table ##TempSaldo select sum(isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) saldoakhir into ##TempSaldo from master'+@strPriorBln+@strThn+
' where noacc = ''31404001'''
Exec sp_executeSQL @MySQL
set @SaldoNoAcc314 =(select isnull(saldoakhir,0) from ##TempSaldo)
set @saldonoacc4 =isnull(@saldonoacc4,0)+isnull(@SaldoNoAcc314,0)
set @mysql =' update master'+@strbln+@strthn+' set saldoawal=isnull(@saldonoacc,0)'+
' ,saldoakhir=isnull(@saldonoacc,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)'+
' where noacc= ''31404001'''
set @parmdefinition =N'@SaldoNoAcc Numeric(18,4)'
Exec sp_executeSQL @MySQL,@parmdefinition,@SaldoNoAcc=@SaldoNoAcc4
set @mysql ='update master'+@strPriorbln+@strthn+' set saldoawal=0,saldoakhir=isnull(mutasidebet,0)-isnull(mutasikredit,0)'+
' where noacc >= ''40000000'''
exec sp_executesql @mysql
end
set @iThn=(select cast(@strThn as int) )
set @iThn=@iThn+1
set @strBln='01'
set @StrNextThn= (select convert(varchar(4),@iThn) )
--cek journal Next Year
Set @MySQL = ' if object_id(''Tempdb..##CekJournal'') is not null drop table ##CekJournal'+
' Select 0 Cek into ##CekJournal'+
' if object_id(''journal'+@strBln+@StrNextThn+''') is null Update ##CekJournal set Cek=0 '+
' else Update ##CekJournal set Cek=1 '
Exec sp_executeSQL @MySQL
set @CekJournal=(Select Cek from ##CekJournal)
--setting untuk bulan terakhir dan pindah saldo awal tahun berikutnya tdk ada journal
if (@iCurrentBln=12) and (@CekJournal=0)
begin
Set @MySQL =' if object_id(''master01'+@StrNextThn+''') is null select * into dbo.master01'+@StrNextThn+' from master'
Exec sp_executeSQL @MySQL
Set @MySQL =' Truncate table master01'+@StrNextThn
Exec sp_executeSQL @MySQL
Set @MySQL =' Insert into master01'+@StrNextThn+'(noacc,saldoawal,mutasidebet,mutasikredit,saldoakhir)'+
' Select noacc,isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0),0,0,isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0) from master12'+@strThn
Exec sp_executeSQL @MySQL
set @mysql ='if object_id(''tempdb..##tempsaldo'') is not null drop table ##tempsaldo select sum(isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) saldoakhir into ##tempsaldo from master12'+@strthn+
' where noacc >= ''40000000'''
exec sp_executesql @mysql
set @saldonoacc4 =(select isnull(saldoakhir,0) from ##tempsaldo)
set @MySQL ='if object_id(''Tempdb..##TempSaldo'') is not null drop table ##TempSaldo select sum(isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) saldoakhir into ##TempSaldo from master12'+@strThn+
' where noacc = ''31404001'''
Exec sp_executeSQL @MySQL
set @SaldoNoAcc314 =(select isnull(saldoakhir,0) from ##TempSaldo)
set @saldonoacc4 =isnull(@saldonoacc4,0)+isnull(@SaldoNoAcc314,0)
set @mysql =' update master01'+@StrNextThn+' set saldoawal=isnull(@saldonoacc,0)'+
' ,saldoakhir=isnull(@saldonoacc,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)'+
' where noacc= ''31404001'''
set @parmdefinition =N'@SaldoNoAcc Numeric(18,4)'
Exec sp_executeSQL @MySQL,@parmdefinition,@SaldoNoAcc=@SaldoNoAcc4
set @mysql ='update master01'+@StrNextThn+' set saldoawal=0,saldoakhir=isnull(mutasidebet,0)-isnull(mutasikredit,0)'+
' where noacc >= ''40000000'''
exec sp_executesql @mysql
end
--setting untuk bulan terakhir dan pindah saldo awal tahun berikutnya ada journal
if (@iCurrentBln=12) and (@CekJournal=1)
Begin
Set @MySQL =' if object_id(''tempdb..##TempJournal'') is not null drop table ##TempJournal'+
' select noacc,sum(isnull(debet,0)) debet,sum(isnull(kredit,0)) kredit '+
' into ##TempJournal from journal'+@strBln+@strNextThn+' where noacc is not null group by noacc order by noacc'
Exec sp_executeSQL @MySQL
Set @MySQL =' Delete from master'+@strBln+@strNextThn+' where (isnull(saldoawal,0)=0) and (isnull(mutasidebet,0)=0) and (isnull(mutasikredit,0)=0)'
Exec sp_executeSQL @MySQL
declare crrecalculate cursor scroll for
select * from ##tempjournal
open crrecalculate
fetch first from crrecalculate into @noacc,@debet,@kredit
while @@fetch_status=0
begin
set @strpriorbln = '12'
set @mysql ='if object_id(''Tempdb..##TempSaldo'') is not null drop table ##TempSaldo select (isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) saldoakhir into ##tempsaldo from master'+@strpriorbln+@strthn+
' where noacc= '''+@noacc+''''
exec sp_executesql @mysql
set @saldoawal =(select isnull(saldoakhir,0) from ##tempsaldo )
set @saldoakhir = @debet-@kredit
if (@noacc <'40000000') and (@noacc<>'31404001')
begin
set @MySQL=N'if not exists(Select * from master'+@strBln+@strNextThn+' where noacc=@noacc1) '+
' insert into master'+@strBln+@strNextThn+'(noacc,saldoawal,mutasidebet,mutasikredit,saldoakhir) '+
' select isnull(@noacc1,''''),0,isnull(@debet1,0),isnull(@kredit1,0),isnull(@saldoakhir1,0) else'+
' update master'+@strBln+@strNextthn+' set saldoawal=isnull(@saldoawal1,0), mutasidebet=isnull(@debet1,0),mutasikredit=isnull(@kredit1,0),'+
' saldoakhir=isnull(@saldoawal1,0)+@saldoakhir1 where noacc=@noacc1'
set @parmdefinition =N'@debet1 numeric(18,4),@kredit1 numeric(18,4),@saldoawal1 numeric(18,4),@saldoakhir1 numeric(18,4),@noacc1 varchar(20)'
exec sp_executesql @mysql, @parmdefinition,@debet1 =@debet,@kredit1=@kredit,@saldoawal1=@saldoawal,@saldoakhir1=@saldoakhir,@noacc1=@noacc
end
if @noacc >= '40000000'
begin
set @MySQL=N'if not exists(Select * from master'+@strBln+@strNextThn+' where noacc=@noacc1) '+
' insert into master'+@strBln+@strNextThn+'(noacc,saldoawal,mutasidebet,mutasikredit,saldoakhir) '+
' select isnull(@noacc1,''''),0,isnull(@debet1,0),isnull(@kredit1,0),isnull(@saldoakhir1,0) else'+
' update master'+@strBln+@strNextthn+' set saldoawal=0, mutasidebet=isnull(@debet1,0),mutasikredit=isnull(@kredit1,0),'+
' saldoakhir=0 where noacc=@noacc1'
set @parmdefinition =N'@debet1 numeric(18,4),@kredit1 numeric(18,4),@saldoawal1 numeric(18,4),@saldoakhir1 numeric(18,4),@noacc1 varchar(20)'
exec sp_executesql @mysql, @parmdefinition,@debet1 =@debet,@kredit1=@kredit,@saldoawal1=@saldoawal,@saldoakhir1=@saldoakhir,@noacc1=@noacc
end
set @mysql ='if object_id(''tempdb..##tempsaldo'') is not null drop table ##tempsaldo select sum(isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) saldoakhir into ##tempsaldo from master12'+@strThn+
' where noacc >= ''40000000'''
exec sp_executesql @mysql
set @saldonoacc4 =(select isnull(saldoakhir,0) from ##tempsaldo)
set @MySQL ='if object_id(''Tempdb..##TempSaldo'') is not null drop table ##TempSaldo select sum(isnull(saldoawal,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)) saldoakhir into ##TempSaldo from master12'+@strThn+
' where noacc = ''31404001'''
Exec sp_executeSQL @MySQL
set @SaldoNoAcc314 =(select isnull(saldoakhir,0) from ##TempSaldo)
set @saldonoacc4 =isnull(@saldonoacc4,0) +isnull(@SaldoNoAcc314,0)
set @mysql ='update master'+@strbln+@strNextThn+' set saldoawal=isnull(@saldonoacc,0)'+
',saldoakhir=isnull(@saldonoacc,0)+isnull(mutasidebet,0)-isnull(mutasikredit,0)'+
' where noacc= ''31404001'''
set @parmdefinition =N'@SaldoNoAcc Numeric(18,4)'
Exec sp_executeSQL @MySQL,@parmdefinition,@SaldoNoAcc=@SaldoNoAcc4
set @mysql ='update master12'+@strThn+' set saldoawal=0,saldoakhir=isnull(mutasidebet,0)-isnull(mutasikredit,0)'+
' where noacc >= ''40000000'''
exec sp_executesql @mysql
fetch next from crrecalculate into @noacc,@debet,@kredit
end
close crrecalculate
deallocate crrecalculate
End
-- hapus master yg noacc=''dan noacc yg tidak ada transaksi
Set @i=1
While (@i <=@iCurrentBln+1)
begin
Set @strI = cast(@i as varchar(2))
if len(@strI)=1 set @strI='0'+@strI
Set @MySQL =' if object_id(''master'+@strI+@strThn+''') is not null Delete from master'+@strI+@strThn+
' where isnull(noacc,'''')='''''
Exec sp_executeSQL @MySQL
Set @MySQL =' if object_id(''master'+@strI+@strThn+''') is not null Delete from master'+@strI+@strThn+
' where (isnull(saldoawal,0) = 0) and (isnull(mutasidebet,0) = 0) and (isnull(mutasikredit,0) = 0)'
Exec sp_executeSQL @MySQL
set @i=@i+1
end
End
