--------------------------جمع اول دوره شخص
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) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	declare @noe smallint
	set @noe =(select noe from tafsili where Code = @code) 
	if(@noe = 1)
	begin
		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 = 6) 
		)
		,0)
	end
	else if(@noe = 3)
	begin
		set @t=
		isnull(
		(
		select sum(mablagh) from daryaftpardakht
		where
			codeTafsiliBedehkar=4 and 
			vaziat<>1 and
			(isAvaldoreh is null or isAvaldoreh=0) and
			NoeDetail != 0
		),0)	
	end
	else
	begin
		set @t=
		isnull(
		(
		select sum(mablagh) from daryaftpardakht
		where
			codeTafsiliBedehkar=@code and 
			vaziat<>1 and
			(isAvaldoreh is null or isAvaldoreh=0)
		)
		,0)	
	end
	
	return @t
end
go
----------------------------------------------------------------------جمع پرداخت در جدول دريافت پرداخت
if exists (select name from dbo.sysobjects where name='fSumPardakht')
	drop function fSumPardakht
go
create function fSumPardakht(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	declare @noe smallint
	set @noe =(select noe from tafsili where Code = @code) 
	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) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	declare @noe smallint
	set @noe =(select noe from tafsili where Code = @code) 
if(@noe = 1 )
begin
	set @t=
	isnull(
	(
	select sum(mablagh) from daryaftpardakht d
	where
		noeMaster=0 and
		codeTafsiliBedehkar=@code/**/ and 
		vaziat<>1 and
		isAvaldoreh=1 and
		noeDetail = 0 and
		vaziateChek =6
	)
	,0)
end
else if(@noe = 3)
begin
	set @t=
	isnull(
	(
	select sum(mablagh) from daryaftpardakht d
	where
		noeMaster=0 and
		codeTafsiliBedehkar=@code and 
		vaziat<>1 and
		isAvaldoreh=1 and
		noeDetail = 0 and
		vaziateChek in (3,6)
	)
	,0)
end
else
begin
	set @t=
	isnull(
	(
	select sum(mablagh) from daryaftpardakht
	where
		noeMaster=0 and
		codeTafsiliBedehkar=@code and 
		vaziat<>1 and
		isAvaldoreh=1 and
		noeDetail = 0 and
		vaziateChek = VaziateChek
	)
	,0)
end
return @t
end
go
--------------------------------------------------جمع پرداختهاي چكهاي اول دوره شخص
if exists (select name from dbo.sysobjects where name='fSumPardakhtChekAvaldoreh')
	drop function fSumPardakhtChekAvaldoreh
go
create function fSumPardakhtChekAvaldoreh(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4)
	declare @noe smallint
	set @noe =(select noe from tafsili where Code = @code) 
	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)
	declare @noeP smallint
	set @noeP =(select noe from tafsili where code =@code)
	if(@noeP = 1) -- 1=>Bank
	begin
		set @t=
		isnull(
		(
		select sum(m.mablagh) from daryaftpardakht m,gardesheChek d
		where
			m.code=d.codeparent and
			d.CodeBedehkar= @code and
			m.vaziat<>1 and
			(isAvaldoreh is null or isAvaldoreh=0)and
			(m.vaziateChek is null or m.vaziateChek = 6)
		)
		,0)
	end
	else if (@noeP = 3) -- 3=>Sandoogh
		set @t=0
	else
	begin
		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,2,9))
		)
		,0)

	end
	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)
	declare @noeP smallint
	set @noeP =(select noe from tafsili where code =@code)
	if(@noeP = 1 or @noeP = 3) -- 1=>Bank , 3=>Sandoogh
	begin
		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)
	end
	else
	begin
		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,2,9))
		)
		,0)
		
	end
	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) returns decimal(19,4) as
begin
	declare @t decimal(19,4);
	declare @noeP smallint;
	set @noeP=(select noe from tafsili where code =@code);
	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) returns decimal(19,4) as
begin
	declare @t decimal(19,4);
	declare @noeP smallint;
	set @noeP=(select noe from tafsili where code =@code);
	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);
	declare @noeP smallint;
	set @noeP=(select noe from tafsili where code =@code);
	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);
	declare @noeP smallint;
	set @noeP=(select noe from tafsili where code =@code);
	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);
	declare @noeP smallint;
	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);
	declare @noeP smallint;
	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 dbo.sysobjects where name='fSumHazinehBanki')
	drop function fSumHazinehBanki
go
create function fSumHazinehBanki(@code bigint) returns decimal(19,4) as
begin
	return (select isnull(sum(mablagh),0.0) from gardesheChek where codeHazineh = @code)
end

go 
-------------------------------------------------------------------------------------- هزینه های ویزیتوری
if exists (select name from dbo.sysobjects where name='fSumVisitorFactor')
	drop function fSumVisitorFactor
go
create function fSumVisitorFactor(@code bigint) returns decimal(19,4) as
begin
	declare @t decimal(19,4);
	set @t=
	isnull(
	(
	select
		sum(isnull(m.MablagheVisitor,0))
	from bargeh m
	where
		(m.noe = 0 /*foroosh*/ or m.noe = 3 /*kharid*/) and
		m.vaziat<>1 and
		m.CodeVisitor is not null and
		m.CodeVisitor = @code
	)
	,0);
	set @t=@t+isnull((select sum(isnull(d.MablagheVisitor,0)) from Bargeh m , bargehDetail d
	where
		(m.noe = 0 /*foroosh*/ or m.noe = 3 /*kharid*/) and
		m.vaziat<>1 and
		d.CodeVisitor is not null and
		d.CodeVisitor = @code
	),0);

	return @t
end
go
--------------------------------------------------------------------------------- بدهکاری هزینه ویزیتور
if exists (select name from dbo.sysobjects where name='fSumhazinehBedehkar')
	drop function fSumhazinehBedehkar
go
create function fSumhazinehBedehkar(@code bigint) returns decimal(19,4) as
begin

	return
	isnull(
	(
	select
		sum( isnull(d.mablagheVisitor,0))
	from bargeh m,bargehDetail d
	where
		m.noe in (0,1,3,4) and --foroosh, bargasht az kharid
		m.code=d.codebargeh and
		d.codeFarayandVisitor = @code and
		m.vaziat<>1
	)
	,0);

end
go
--select  dbo.fSumVisitorFactor(13)
--------------------------------------------------------------------------------- گردش حساب کلی
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,CodeGrooheHazine , CodeGroohAshkas , CodeGroohKhadamat ,
	case noe
		when 2 then --shakhs
			case
			when dbo.fSumMojoodiAvalDoreh(code)<=0 then -(dbo.fSumMojoodiAvalDoreh(code))
			else 0.0 end
		when 1 then --Bank
			dbo.fSumMojoodiAvalDoreh(code)
		when 3 then --Sandoogh
			dbo.fSumMojoodiAvalDoreh(code)
		else 0
	end +
	dbo.fSumDaryaft(code)+
	dbo.fSumDaryaftChekAvaldoreh(code)+
	dbo.fSumChekDaryaftiAzChandNafar(code)+
	dbo.fSumDaryaftGardeshChek(code)+
	dbo.fSumDaryaftGardeshChekAvalDoreh(code)+
	dbo.fSumBargehBedehkar(code)+
	dbo.fSumTakhfifBargehDetail(code)+
	dbo.fSumHazinehBanki(code)+
	dbo.fSumhazinehBedehkar(code)
	[bedehkar],
	case when dbo.fSumMojoodiAvalDoreh(code)>=0 and noe = 2 then dbo.fSumMojoodiAvalDoreh(code) else 0.0 end+
	dbo.fSumPardakht(code)+
	dbo.fSumPardakhtChekAvaldoreh(code)+
	dbo.fSumChekPardakhtiBeChandNafar(code)+
	dbo.fSumPardakhtGardeshChek(code)+
	dbo.fSumPardakhtGardeshChekAvalDoreh(code)+
	dbo.fSumBargehBestankar(code)+
	dbo.fSumTakhfifBargehDetailKharid(code)+
	dbo.fSumTakhsisKhadamat(code)+
	dbo.fSumKhadamatFactor(code)+
	dbo.fSumVisitorFactor(code)
	[bestankar],
	0.0[Mandeh],
	''[Mahiat],
	tel1[Tel1],
	tel2[Tel2],
	Mobile[Mobile],
	address[Adress],
	AddressHome[AdressHome]
from tafsili
end
go