--------------------------جمع اول دوره شخص
if exists (select name from dbo.sysobjects where name='fSumMojoodiAvalDoreh')
	drop function fSumMojoodiAvalDoreh
go
create function fSumMojoodiAvalDoreh(@code bigint) returns decimal(19,4) as
begin
	return (select isnull(mojoodiAvalDore,0.0) from tafsili where code=@code)
end
go
-------------------------------جمع دريافت در جدول دريافت پرداخت
if exists (select name from dbo.sysobjects where name='fSumDaryaft')
	drop function fSumDaryaft
go
create function fSumDaryaft(@code bigint,@noe smallint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	set @t=
	isnull(
	(
	select sum(mablagh) from daryaftpardakht
	where
		codeTafsiliBedehkar=@code and 
		vaziat<>1 and
		(isAvaldoreh is null or isAvaldoreh=0) and
		--(vaziateChek is null or vaziateChek not in (0,1,2,4,5,7,8,9))
		(vaziateChek is null or vaziateChek in
		(
		case  when @noe = 1 and noeDetail = 0 then 6
			when @noe = 3 and noeDetail = 0 then 3 
			else vaziateChek
		end
		))
		
	)
	,0)
	return @t
end
go
----------------------------------------------------------------------جمع پرداخت در جدول دريافت پرداخت
if exists (select name from dbo.sysobjects where name='fSumPardakht')
	drop function fSumPardakht
go
create function fSumPardakht(@code bigint,@noe smallint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	set @t=
	isnull(
	(
	select sum(mablagh) from daryaftpardakht
	where
		codeTafsiliBestankar=@code and 
		vaziat<>1 and
		(isAvaldoreh is null or isAvaldoreh=0) and
		--(vaziateChek is null or vaziateChek not in (0,1,2,4,5,7,8,9))
		(vaziateChek is null or vaziateChek in
		(
		case  when @noe = 1 and noeDetail = 0 then 6
			when @noe = 3 and noeDetail = 0 then 3 
			else vaziateChek
		end
		))
	),0)
	return @t
end
go

--------------------------------------------------جمع دريافت هاي چكهاي اول دوره شخص
if exists (select name from dbo.sysobjects where name='fSumDaryaftChekAvaldoreh')
	drop function fSumDaryaftChekAvaldoreh
go
create function fSumDaryaftChekAvaldoreh(@code bigint,@noe smallint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	set @t=
	isnull(
	(
	select sum(mablagh) from daryaftpardakht
	where
		noeMaster=0 and
		codeTafsiliBedehkar=@code/**/ and 
		vaziat<>1 and
		isAvaldoreh=1 and
		--(vaziateChek is null or vaziateChek not in (0,1,2,4,5,7,8,9))
		(vaziateChek is null or vaziateChek in
		(
		case  when @noe = 1 and noeDetail = 0 then 6
			when @noe = 3 and noeDetail = 0 then 100 -- عدد خیلی بزرگ مه هیچ وقت تورو نشود
			else vaziateChek
		end
		))
	)
	,0)
	return @t
end
go
--------------------------------------------------جمع پرداختهاي چكهاي اول دوره شخص
if exists (select name from dbo.sysobjects where name='fSumPardakhtChekAvaldoreh')
	drop function fSumPardakhtChekAvaldoreh
go
create function fSumPardakhtChekAvaldoreh(@code bigint,@noe smallint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	set @t=
	isnull(
	(
	select sum(mablagh) from daryaftpardakht
	where
		noeMaster=1 and
		codeTafsiliBestankar=@code/**/ and 
		vaziat<>1 and
		isAvaldoreh=1 and
		--(vaziateChek is null or vaziateChek not in (0,1,2,4,5,7,8,9))
		(vaziateChek is null or vaziateChek in
		(
		case  when @noe = 1 and noeDetail = 0 then 6
			when @noe = 3 and noeDetail = 0 then 3 
			else vaziateChek
		end
		))
	)
	,0)
	return @t
end


go
--------------------------------------------------جمع دريافتهاي چك از چند نفر شخص
if exists (select name from dbo.sysobjects where name='fSumChekDaryaftiAzChandNafar')
	drop function fSumChekDaryaftiAzChandNafar
go
create function fSumChekDaryaftiAzChandNafar(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	set @t=
	isnull(
	(
	select sum(d.mablagh) from daryaftpardakht m,chekdetail d
	where
		m.code=d.codeparent and
		codeTafsiliBedehkar is null and
		d.codeTafsiliMoshtari=@code and
		vaziat<>1 and
		(isAvaldoreh is null or isAvaldoreh=0) and
		(vaziateChek is null or vaziateChek not in (0,1,2,4,5,7,8,9))
	)
	,0)
	return @t
end
go
--------------------------------------------------جمع پرداختهاي چك به چند نفر شخص
if exists (select name from dbo.sysobjects where name='fSumChekPardakhtiBeChandNafar')
	drop function fSumChekPardakhtiBeChandNafar
go
create function fSumChekPardakhtiBeChandNafar(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	set @t=
	isnull(
	(
	select sum(d.mablagh) from daryaftpardakht m,chekdetail d
	where
		m.code=d.codeparent and
		codeTafsiliBestankar is null and
		d.codeTafsiliMoshtari=@code and
		vaziat<>1 and
		(isAvaldoreh is null or isAvaldoreh=0) and
		(vaziateChek is null or vaziateChek not in (0,1,2,4,5,7,8,9))
	)
	,0)
	return @t
end
go

--------------------------------------------------جمع دريافت گردش چك
if exists (select name from dbo.sysobjects where name='fSumDaryaftGardeshChek')
	drop function fSumDaryaftGardeshChek
go
create function fSumDaryaftGardeshChek(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	set @t=
	isnull(
	(
	select sum(m.mablagh) from daryaftpardakht m,gardesheChek d
	where
		m.code=d.codeparent and
		CodeBedehkar=@code and
		m.vaziat<>1 and
		(isAvaldoreh is null or isAvaldoreh=0)and
		(d.vaziat is null or d.vaziat not in(0,1,2,4,5,7,8,9))--bargasht, pas dadane chek bargashti, bargasht be moshtari/az moshtari,...
	)
	,0)
	return @t
end
go

--------------------------------------------------جمع پرداخت گردش چك
if exists (select name from dbo.sysobjects where name='fSumPardakhtGardeshChek')
	drop function fSumPardakhtGardeshChek
go
create function fSumPardakhtGardeshChek(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	set @t=
	isnull(
	(
	select sum(m.mablagh) from daryaftpardakht m,gardesheChek d
	where
		m.code=d.codeparent and
		CodeBestankar=@code and
		m.vaziat<>1 and
		(isAvaldoreh is null or isAvaldoreh=0)and
		(d.vaziat is null or d.vaziat not in(0,1,2,4,5,7,8,9))--bargasht, pas dadane chek bargashti, bargasht be moshtari/az moshtari,...
	)
	,0)
	return @t
end
go

--------------------------------------------------جمع دريافت گردش چك اول دوره
if exists (select name from dbo.sysobjects where name='fSumDaryaftGardeshChekAvalDoreh')
	drop function fSumDaryaftGardeshChekAvalDoreh
go
create function fSumDaryaftGardeshChekAvalDoreh(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	set @t=
	isnull(
	(
	select sum(m.mablagh) from daryaftpardakht m,gardesheChek d
	where
		m.noeMaster=0 and
		m.code=d.codeparent and
		CodeBedehkar=@code and
		m.vaziat<>1 and
		isAvaldoreh=1 and
		(m.vaziateChek is null or m.vaziateChek not in(0,1,2,4,5,7,8,9))--bargasht, pas dadane chek bargashti, bargasht be moshtari/az moshtari,...
	)
	,0)
	return @t
end
go


--------------------------------------------------جمع پرداخت گردش چك اول دوره
if exists (select name from dbo.sysobjects where name='fSumPardakhtGardeshChekAvalDoreh')
	drop function fSumPardakhtGardeshChekAvalDoreh
go
create function fSumPardakhtGardeshChekAvalDoreh(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	set @t=
	isnull(
	(
	select sum(m.mablagh) from daryaftpardakht m,gardesheChek d
	where
		m.noeMaster=1 and
		m.code=d.codeparent and
		CodeBestankar=@code and
		m.vaziat<>1 and
		isAvaldoreh=1 and
		(d.vaziat is null or d.vaziat not in(0,1,2,4,5,7,8,9))--bargasht, pas dadane chek bargashti, bargasht be moshtari/az moshtari,...
	)
	,0)
	return @t
end
go

-------------------------------------------------جمع بدهكاري در برگه ها
if exists (select name from dbo.sysobjects where name='fSumBargehBedehkar')
	drop function fSumBargehBedehkar
go
create function fSumBargehBedehkar(@code bigint,@noeP smallint) returns decimal(19,4) as
begin
	declare @t decimal(19,4);
	set @t=
	isnull(
	(
	select
		sum(
			(d.mablagh*d.meghdar1*d.meghdar2*d.tedad)-
			(case when d.IsKala=0 and @noeP=4 then isnull(d.MablaghTakhsisKhadamat,0) else 0 end)-
			(case when d.codeTakhfif is not null then isnull(MablagheTakhfif,0) else 0 end)
		)
	from bargeh m,bargehDetail d
	where
		m.noe in (0,4) and --foroosh, bargasht az kharid
		m.code=d.codebargeh and
		codeMoshtari=@code and
		m.vaziat<>1
	)
	,0);
	set @t=@t-isnull((select sum(mablagh) from daryaftPardakht d,bargeh b
	where
		d.codeBargehParent=b.code and
		b.codeMoshtari=@code and
		d.vaziat<>1 and
		d.noeMaster =2 and
		b.noe in (0,4) -- foroosh , bargashtAzkharid
	),0);
	set @t=@t+isnull((select sum(mablagh) from daryaftPardakht d,bargeh b
	where
		d.codeBargehParent=b.code and
		b.codeMoshtari=@code and
		d.vaziat<>1 and
		d.noeMaster =4 and
		b.noe in (0,4) -- foroosh , bargashtAzkharid
	),0);
	return @t
end
go


-------------------------------------------------جمع بستانکاری در برگه ها

if exists (select name from dbo.sysobjects where name='fSumBargehBestankar')
	drop function fSumBargehBestankar
go
create function fSumBargehBestankar(@code bigint,@noeP smallint) returns decimal(19,4) as
begin
	declare @t decimal(19,4);
	set @t=
	isnull(
	(
	select
		sum(
			(d.mablagh*d.meghdar1*d.meghdar2*d.tedad)-
			(case when d.IsKala=0 and @noeP=4 then isnull(d.MablaghTakhsisKhadamat,0) else 0 end)-
			(case when d.codeTakhfif is not null then isnull(MablagheTakhfif,0) else 0 end)
		)
	from bargeh m,bargehDetail d
	where
		m.noe in (1,3) and --kharid, bargasht az foroosh
		m.code=d.codebargeh and
		codeMoshtari=@code and
		m.vaziat<>1
	)
	,0);
	set @t=@t+isnull((select sum(mablagh) from daryaftPardakht d,bargeh b
	where
		d.codeBargehParent=b.code and
		b.codeMoshtari=@code and
		d.vaziat<>1 and
		d.noeMaster =2 and
		b.noe in (3,1) -- kharid , bargashtAzkharid
	),0);
	set @t=@t-isnull((select sum(mablagh) from daryaftPardakht d,bargeh b
	where
		d.codeBargehParent=b.code and
		b.codeMoshtari=@code and
		d.vaziat<>1 and
		d.noeMaster =4 and
		b.noe in (3,1) -- kharid , bargashtAzkharid
	),0);
	return @t
end
go


-----------------------------------جمع تخفیفات جزئیات برگه(برای محاسبه در کارت حساب هزینه)ـ
if exists (select name from dbo.sysobjects where name='fSumTakhfifBargehDetail')
	drop function fSumTakhfifBargehDetail
go
create function fSumTakhfifBargehDetail(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4);
	set @t=
	isnull(
	(
	select
		sum(MablagheTakhfif)
	from bargeh m,bargehDetail d
	where
		m.noe in (0,4) and --foroosh, bargasht az khardi
		m.code=d.codebargeh and
		d.codeTakhfif=@code and
		m.vaziat<>1
	)
	,0);
	return @t
end
go
-----------------------------------جمع تخفیفات جزئیات برگه(برای محاسبه در کارت حساب درآمد)ـ
if exists (select name from dbo.sysobjects where name='fSumTakhfifBargehDetailKharid')
	drop function fSumTakhfifBargehDetailKharid
go
create function fSumTakhfifBargehDetailKharid(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4);
	set @t=
	isnull(
	(
	select
		sum(MablagheTakhfif)
	from bargeh m,bargehDetail d
	where
		m.noe in (1,3) and --kharid, bargasht az foroosh
		m.code=d.codebargeh and
		d.codeTakhfif=@code and
		m.vaziat<>1
	)
	,0);
	return @t
end
go
-----------------------------------جمع تخصیص خدمات
if exists (select name from dbo.sysobjects where name='fSumTakhsisKhadamat')
	drop function fSumTakhsisKhadamat
go
create function fSumTakhsisKhadamat(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4);
	set @t=
	isnull(
	(
	select
		sum(mablaghTakhsisKhadamat)
	from bargeh m,bargehDetail d
	where
		--m.noe in (1,3) and --kharid, bargasht az foroosh
		m.code=d.codebargeh and
		d.codeTakhsisKhadamat=@code and
		m.vaziat<>1
	)
	,0);
	return @t
end

go

-----------------------------------جمع بستانکاری خدمات در فاکتور
if exists (select name from dbo.sysobjects where name='fSumKhadamatFactor')
	drop function fSumKhadamatFactor
go
create function fSumKhadamatFactor(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4);
	set @t=
	isnull(
	(
	select
		sum(mablagh*tedad-isnull(d.mablagheTakhfif,0)-isnull(mablaghTakhsisKhadamat,0))
	from bargeh m,bargehDetail d
	where
		m.noe = 0 and --foroosh
		m.code=d.codebargeh and
		m.vaziat<>1 and
		d.codeKhadamat2=@code
	)
	,0);
	return @t
end

go
--------------------------------------------------------------------------------- گردش حساب کلی
IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='prcPersonCardMaster')
	DROP proc prcPersonCardMaster
GO
--prcPersonCardMaster
CREATE proc prcPersonCardMaster as
begin

select
	code,CodeHesab,name,noe,
	case when dbo.fSumMojoodiAvalDoreh(code)<0 then -(dbo.fSumMojoodiAvalDoreh(code)) else 0 end+
	dbo.fSumDaryaft(code,noe)+
	dbo.fSumDaryaftChekAvaldoreh(code,noe)+
	dbo.fSumChekDaryaftiAzChandNafar(code)+
	dbo.fSumDaryaftGardeshChek(code)+
	dbo.fSumDaryaftGardeshChekAvalDoreh(code)+
	dbo.fSumBargehBedehkar(code,noe)+
	dbo.fSumTakhfifBargehDetail(code)
	[bedehkar],
	case when dbo.fSumMojoodiAvalDoreh(code)>0 then dbo.fSumMojoodiAvalDoreh(code) else 0 end+
	dbo.fSumPardakht(code,noe)+
	dbo.fSumPardakhtChekAvaldoreh(code,noe)+
	dbo.fSumChekPardakhtiBeChandNafar(code)+
	dbo.fSumPardakhtGardeshChek(code)+
	dbo.fSumPardakhtGardeshChekAvalDoreh(code)+
	dbo.fSumBargehBestankar(code,noe)+
	dbo.fSumTakhfifBargehDetailKharid(code)+
	dbo.fSumTakhsisKhadamat(code)+
	dbo.fSumKhadamatFactor(code)
	[bestankar],
	0.0[Mandeh],
	''[Mahiat],
	tel1[Tel1],
	tel2[Tel2],
	Mobile[Mobile],
	address[Adress],
	AddressHome[AdressHome]
from tafsili
end
go