📄 procedure.sql
字号:
use Finance;
go
-- ### AccountReport ###############################################################
if (exists (select name, type from sysobjects where (name = 'AccountReport') and (type = 'P')))
drop procedure AccountReport;
go
create procedure AccountReport(@accountid char(6), @start char(10), @end char(10)) as
begin
select
mt.TanggalEntry as [TanggalEntry], mt.NoBukti as [NoBukti], t.Uraian as [Uraian],
case t.Mutasi when 0 then t.Jumlah else 0 end as [Debet],
case t.Mutasi when 1 then t.Jumlah else 0 end as [Kredit],
dbo.CurrencyString(mt.MataUang) as [MataUang], 0 as [Kurs]
from Account as [ac]
inner join TransactionDetail as [t] on (ac.Kode = t.IdAccount)
inner join MainTransaction as [mt] on (t.IdMainTrans = mt.NoBukti)
where
(mt.TanggalEntry >= cast(@start as smalldatetime)) and (mt.TanggalEntry <= cast(@end as smalldatetime)) and
(ac.Kode = @accountid) and (mt.NoBukti != '00.00.00.000');
end
go
-- ### TransactionIdExist ##########################################################
if (exists (select name, type from sysobjects where (name = 'TransactionIdExist') and (type = 'P')))
drop procedure TransactionIdExist;
go
create procedure TransactionIdExist(@idx char(12), @exist bit output) as
begin
if (exists (select NoBukti from MainTransaction where (NoBukti = @idx)))
set @exist = 1;
else
set @exist = 0;
end
go
-- ### TransactionRemove ###########################################################
if (exists (select name, type from sysobjects where (name = 'TransactionRemove') and (type = 'P')))
drop procedure TransactionRemove;
go
create procedure TransactionRemove(@idx char(12)) as
begin
delete from TransactionDetail where (IdMainTrans = @idx);
delete from MainTransaction where (NoBukti = @idx);
end
go
-- ### TransactionQuery ############################################################
if (exists (select name, type from sysobjects where (name = 'TransactionQuery') and (type = 'P')))
drop procedure TransactionQuery;
go
create procedure TransactionQuery(@type bit, @nobukti char(12), @dibayarkan varchar(30), @entrydate varchar(10), @paymethod tinyint, @bank varchar(30), @nocek varchar(30), @cdate varchar(10), @valuta bit) as
begin
if (@type = 0)
begin
if (len(@nocek) = 0)
begin
insert into MainTransaction
values (@nobukti, @dibayarkan, cast(@entrydate as smalldatetime), @paymethod, @bank, @nocek, null, @valuta);
end
else
begin
insert into MainTransaction
values (@nobukti, @dibayarkan, cast(@entrydate as smalldatetime), @paymethod, @bank, @nocek, cast(@cdate as smalldatetime), @valuta);
end
end
else
begin
if (len(@nocek) = 0)
begin
update MainTransaction
set
Dibayarkan = @dibayarkan,
TanggalEntry = cast(@entrydate as smalldatetime),
SistemBayar = @paymethod,
NamaBank = @bank,
NoCek = @nocek,
TanggalCek = null,
MataUang = @valuta
where
(NoBukti = @nobukti);
end
else
begin
update MainTransaction
set
Dibayarkan = @dibayarkan,
TanggalEntry = cast(@entrydate as smalldatetime),
SistemBayar = @paymethod,
NamaBank = @bank,
NoCek = @nocek,
TanggalCek = cast(@cdate as smalldatetime),
MataUang = @valuta
where
(NoBukti = @nobukti);
end
end
end
go
-- ### TransactionDetailRemove #####################################################
if (exists (select name, type from sysobjects where (name = 'TransactionDetailRemove') and (type = 'P')))
drop procedure TransactionDetailRemove;
go
create procedure TransactionDetailRemove(@idx int) as
begin
delete from TransactionDetail where (Idx = @idx);
end
go
-- ### TransactionDetailQuery ######################################################
if (exists (select name, type from sysobjects where (name = 'TransactionDetailQuery') and (type = 'P')))
drop procedure TransactionDetailQuery;
go
create procedure TransactionDetailQuery(@type bit, @mtid char(12), @uraian varchar(50), @accountid char(6), @areaid char(3), @wo varchar(5), @jumlah varchar(18), @mutasi bit, @upidx int, @newidx int output) as
begin
if (@type = 0)
begin
declare @n int;
set @n = isnull((select max(Idx) + 1 from TransactionDetail), 1);
insert into TransactionDetail
values (@n, @mtid, @uraian, @accountid, @areaid, @wo, cast(@jumlah as money), @mutasi);
set @newidx = @n;
end
else
begin
update TransactionDetail
set
Uraian = @uraian,
IdAccount = @accountid,
IdArea = @areaid,
WorkOrder = @wo,
Jumlah = cast(@jumlah as money),
Mutasi = @mutasi
where
(Idx = @upidx);
set @newidx = -1;
end
end
go
-- ### TransactionViewDetail #######################################################
if (exists (select name, type from sysobjects where (name = 'TransactionViewDetail') and (type = 'P')))
drop procedure TransactionViewDetail;
go
create procedure TransactionViewDetail(@code char(12)) as
begin
select
t.Idx as [Idx], t.Uraian as [Uraian],
t.IdAccount as [IdAccount], ac.NamaAccount as [NamaAccount],
t.IdArea as [IdArea], a.NamaArea as [NamaArea], t.Mutasi as [Mutasi],
t.WorkOrder as [WorkOrder],
case t.Mutasi when 0 then t.Jumlah else 0 end as [Debet],
case t.Mutasi when 1 then t.Jumlah else 0 end as [Kredit]
from TransactionDetail as [t]
inner join Account as [ac] on (t.IdAccount = ac.Kode)
inner join Area as [a] on (t.IdArea = a.IdArea)
where
(IdMainTrans = @code);
end
go
-- ### AreaRemove ##################################################################
if (exists (select name, type from sysobjects where (name = 'AreaRemove') and (type = 'P')))
drop procedure AreaRemove;
go
create procedure AreaRemove(@code char(3)) as
begin
delete from Area where (IdArea = @code);
end
go
-- ### AreaCodeExist ###############################################################
if (exists (select name, type from sysobjects where (name = 'AreaCodeExist') and (type = 'P')))
drop procedure AreaCodeExist;
go
create procedure AreaCodeExist(@code char(3), @exist bit output) as
begin
if (exists (select IdArea from Area where (IdArea = @code)))
set @exist = 1;
else
set @exist = 0;
end
go
-- ### AreaQuery ###################################################################
if (exists (select name, type from sysobjects where (name = 'AreaQuery') and (type = 'P')))
drop procedure AreaQuery;
go
create procedure AreaQuery(@type bit, @code char(3), @name varchar(30)) as
begin
if (@type = 0)
begin
insert into Area
values (@code, @name);
end
else
begin
update Area
set
NamaArea = @name
where
(IdArea = @code);
end
end
go
-- ### AccountRemove ###############################################################
if (exists (select name, type from sysobjects where (name = 'AccountRemove') and (type = 'P')))
drop procedure AccountRemove;
go
create procedure AccountRemove(@code char(6)) as
begin
delete from Account where (Kode = @code);
end
go
-- ### AccountCodeExist ############################################################
if (exists (select name, type from sysobjects where (name = 'AccountCodeExist') and (type = 'P')))
drop procedure AccountCodeExist;
go
create procedure AccountCodeExist(@code char(6), @exist bit output) as
begin
if (exists (select Kode from Account where (Kode = @code)))
set @exist = 1;
else
set @exist = 0;
end
go
-- ### AccountQuery ################################################################
if (exists (select name, type from sysobjects where (name = 'AccountQuery') and (type = 'P')))
drop procedure AccountQuery;
go
create procedure AccountQuery(@type bit, @code char(6), @name varchar(30), @saldo varchar(18)) as
begin
if (@type = 0)
begin
insert into Account
values (@code, @name, cast(@saldo as money), 'D');
end
else
begin
update Account
set
NamaAccount = @name,
SaldoAwal = cast(@saldo as money)
where
(Kode = @code);
end
end
go
-- ### TransactionListReport #######################################################
if (exists (select name, type from sysobjects where (name = 'TransactionListReport') and (type = 'P')))
drop procedure TransactionListReport;
go
create procedure TransactionListReport(@year int, @month int) as
begin
select
mt.TanggalEntry as [TanggalEntry], mt.NoBukti as [NoBukti],
t.IdAccount as [IdAccount], t.WorkOrder as [WorkOrder],
t.IdArea as [IdArea], t.Uraian as [Uraian],
case t.Mutasi when 0 then t.Jumlah else 0 end as [Debet],
case t.Mutasi when 1 then t.Jumlah else 0 end as [Kredit]
from TransactionDetail as [t]
inner join MainTransaction as [mt] on (t.IdMainTrans = mt.NoBukti)
where
(year(mt.TanggalEntry) = @year) and (month(mt.TanggalEntry) = @month) and
(mt.NoBukti != '00.00.00.000')
order by
mt.NoBukti;
end
go
-- ### AccountTotalDetail ##########################################################
if (exists (select name, type from sysobjects where (name = 'AccountTotalDetail') and (type = 'P')))
drop procedure AccountTotalDetail;
go
create procedure AccountTotalDetail(@accountid char(6), @year smallint, @month tinyint, @total varchar(20) output) as
begin
set @total =
cast(
isnull(
(
select (sum(Debet) - sum(Kredit)) as [MainTotal]
from AccountMutasiView
where
(year(TanggalEntry) = @year) and (month(TanggalEntry) = @month) and
(Kode = @accountid)
), 0)
as varchar(20));
end
go
-- ### AccountLabaRugiDetail #######################################################
if (exists (select name, type from sysobjects where (name = 'AccountLabaRugiDetail') and (type = 'P')))
drop procedure AccountLabaRugiDetail;
go
create procedure AccountLabaRugiDetail(@accountid char(6), @start varchar(10), @end varchar(10), @total varchar(20) output) as
begin
set @total =
cast(
isnull(
(
select (sum(Debet) - sum(Kredit)) as [MainTotal]
from AccountMutasiView
where
(TanggalEntry between cast(@start as smalldatetime) and cast(@end as smalldatetime)) and (Kode = @accountid)
), 0)
as varchar(20));
end
go
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -