~ Tiez Notes ~

CREATE procedure sp_rptFPDSurveyAll (
@v_branchid varchar(4),
@v_kodefpd varchar(1),
@v_tgl smalldatetime
)
AS
Begin
set transaction isolation level read uncommitted
begin transaction
declare @v_dateawal smalldatetime
declare @v_dateakhir smalldatetime
declare @v_bln varchar(2)
declare @v_tglakhir varchar(4)
declare @v_blnakhir varchar(2)
declare @v_thnakhir varchar(2)
declare @v_tgl_akhir smalldatetime
if @v_kodefpd = ’1′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -1, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl)
end
else if @v_kodefpd = ’2′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -2, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl) end
else if @v_kodefpd = ’3′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -3, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl) end
else if @v_kodefpd = ’4′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -4, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl) end
else if @v_kodefpd = ’5′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -5, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl)
end
else if @v_kodefpd = ’6′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -6, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl)
end
else
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -6, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl)
end
if @v_branchid = ‘-1′
select aa.branchid, aa.branchname, aa.surveyorname, sum(mapReal) as mapReal, sum(mapTgk) as mapTgk,
sum(TB) as TB
from
(select ownergroupid as branchid, dbo.f_getbranch(ownergroupid) as branchname,
suveyorname as surveyorname, count(norek+nopin) mapReal, 0 as mapTgk, 0 as TB
from vp_report5
where realizationdate between @v_dateawal and @v_dateakhir
group by ownergroupid, suveyorname
union all
select branchid, dbo.f_getbranch(branchid) as branchname, surveyorname, 0 as mapReal, count(norek+nopin) mapTgk, 0 as TB
from rptfinancore
where agingday > 0 and realisasidate between @v_dateawal and @v_dateakhir
group by branchid, surveyorname
union all
select branchid, branchname, surveyorname, 0 as mapReal, 0 as mapTgk, count(norek+nopin) TB
from rptfinancore with(nolock)
where reposesdate is not null and moveardate is not null
and realisasidate between @v_dateawal and @v_dateakhir
group by branchid, branchname, surveyorname
) aa
group by aa.branchid, aa.branchname, aa.surveyorname
else
select aa.branchid, aa.branchname, aa.surveyorname, sum(mapReal) as mapReal, sum(mapTgk) as mapTgk,
sum(TB) as TB
from
(select ownergroupid as branchid, dbo.f_getbranch(ownergroupid) as branchname,
suveyorname as surveyorname, count(norek+nopin) mapReal, 0 as mapTgk, 0 as TB
from vp_report5
where ownergroupid = @v_branchid and realizationdate between @v_dateawal and @v_dateakhir
group by ownergroupid, suveyorname
union all
select branchid, dbo.f_getbranch(branchid) as branchname, surveyorname, 0 as mapReal, count(norek+nopin) mapTgk, 0 as TB
from rptfinancore
where agingday > 0 and branchid = @v_branchid and realisasidate between @v_dateawal and @v_dateakhir
group by branchid, surveyorname
union all
select branchid, branchname, surveyorname, 0 as mapReal, 0 as mapTgk, count(norek+nopin) TB
from rptfinancore with(nolock)
where reposesdate is not null and moveardate is not null
and branchid = @v_branchid and realisasidate between @v_dateawal and @v_dateakhir
group by branchid, branchname, surveyorname
) aa
group by aa.branchid, aa.branchname, aa.surveyorname
commit transaction
end
GO