-------------------------------دريافت در جدول دريافت پرداخت
if exists (select name from dbo.sysobjects where name='fSumDaryaft2')
	drop function fSumDaryaft2
go
create function fSumDaryaft2(@TarikhAz int=0, @TarikhEla int=0) RETURNS TABLE
	RETURN(
	SELECT -- صندوق نباشد
		t.code[Code],
		sum(d.mablagh)[bed],
		0.0[bes]
	from daryaftpardakht d join Tafsili t ON d.CodeTafsiliBedehkar =t.Code
	WHERE
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		t.noe != 3 and /*Sandoogh*/
		d.vaziat<>1 AND --deleted
		(d.isAvaldoreh is null or d.isAvaldoreh=0) and
		(d.vaziateChek is null or d.vaziateChek in
		(
		case  when t.noe = 1 and d.noeDetail = 0 then 6
			--when t.noe = 3 and d.noeDetail = 0 then 3 
			else d.vaziateChek
		END
		))
	GROUP BY t.code

	union all 
	
	SELECT -- صندوق باشد
		t.code[Code],
		sum(d.mablagh)[bed],
		0.0[bes]
	from daryaftpardakht d join Tafsili t ON d.CodeTafsiliBedehkar =t.Code
	WHERE
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		t.noe = 3 and -- Sandoogh
		d.vaziat<>1 AND --deleted
		(d.isAvaldoreh is null or d.isAvaldoreh=0) and
		(d.vaziateChek is null or d.vaziateChek in (3 , 6 , 8 , 11))-- Kharj Shode , DaryaftnaghdiAzMoshtari , Naghd Shodan chek
	GROUP BY t.code
	)
GO

----------------------------------------------------------------------جمع پرداخت در جدول دريافت پرداخت
if exists (select name from dbo.sysobjects where name='fSumPardakht2')
	drop function fSumPardakht2
go
create function fSumPardakht2(@TarikhAz int=0, @TarikhEla int=0)  RETURNS TABLE
	RETURN(
	SELECT
		t.code[Code],
		0.0[bed],
		sum(d.mablagh)[bes]
	from daryaftpardakht d join Tafsili t ON d.codeTafsiliBestankar =t.Code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		d.vaziat<>1 and
		(d.isAvaldoreh is null or d.isAvaldoreh=0) and
		(d.vaziateChek is null or d.vaziateChek in
		(
		case  when t.noe = 1 and d.noeDetail = 0 then 6
			when t.noe = 3 /*sandoogh*/ and d.noeDetail = 0 /*chek*/ then 3 /*Kharj shodeh*/
			else d.vaziateChek
		end
		))
	GROUP BY t.code
	)
GO

--------------------------------------------------جمع دريافت هاي چكهاي اول دوره شخص
if exists (select name from dbo.sysobjects where name='fSumDaryaftChekAvaldoreh2')
	drop function fSumDaryaftChekAvaldoreh2
go
create function fSumDaryaftChekAvaldoreh2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	SELECT
		t.code[Code],
		sum(d.mablagh)[bed],
		0.0[bes]
	from daryaftpardakht d join Tafsili t ON d.CodeTafsiliBedehkar =t.Code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		t.noe != 3 and -- Sandoogh
		noeMaster=0 and
		vaziat<>1 and
		isAvaldoreh=1 and
		(vaziateChek is null or vaziateChek in
		(
		case  when t.noe = 1 and noeDetail = 0 then 6
			else vaziateChek
		end
		))
	group by t.code

	union all

	SELECT
		t.code[Code],
		sum(d.mablagh)[bed],
		0.0[bes]
	from daryaftpardakht d join Tafsili t ON d.CodeTafsiliBedehkar =t.Code
	where
		t.noe = 3 and -- Sandoogh
		noeMaster=0 and
		vaziat<>1 and
		isAvaldoreh=1 and
		(vaziateChek is null or vaziateChek in (3 ,8,11)) -- Kharj Shode , DaryaftnaghdiAzMoshtari , Naghd Shodan chek
	group by t.code
	)
go

--------------------------------------------------جمع پرداختهاي چكهاي اول دوره شخص
if exists (select name from dbo.sysobjects where name='fSumPardakhtChekAvaldoreh2')
	drop function fSumPardakhtChekAvaldoreh2
go
create function fSumPardakhtChekAvaldoreh2(@TarikhAz int=0, @TarikhEla int=0)  returns table
return(
	SELECT
		t.code[Code],
		0.0[bed],
		sum(d.mablagh)[bes]
	from daryaftpardakht d join Tafsili t ON d.codeTafsiliBestankar =t.Code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		noeMaster=1 and
		vaziat<>1 and
		isAvaldoreh=1 and
		(vaziateChek is null or vaziateChek in
		(
		case  when t.noe = 1 and noeDetail = 0 then 6
			when t.noe = 3 and noeDetail = 0 then 3 
			else vaziateChek
		end
		))
	group by t.code
	)
go


--------------------------------------------------جمع دريافتهاي چك از چند نفر شخص
if exists (select name from dbo.sysobjects where name='fSumChekDaryaftiAzChandNafar2')
	drop function fSumChekDaryaftiAzChandNafar2
go
create function fSumChekDaryaftiAzChandNafar2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.code[Code],
		sum(d.mablagh)[bed],
		0.0[bes]
	from daryaftpardakht m,chekdetail d join tafsili t on d.codeTafsiliMoshtari=t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.code=d.codeparent and
		codeTafsiliBedehkar is null 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))
	group by t.code
)
go



--------------------------------------------------جمع پرداختهاي چك به چند نفر شخص
if exists (select name from dbo.sysobjects where name='fSumChekPardakhtiBeChandNafar2')
	drop function fSumChekPardakhtiBeChandNafar2
go
create function fSumChekPardakhtiBeChandNafar2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.code[Code],
		sum(d.mablagh)[bed],
		0.0[bes]
	from daryaftpardakht m,chekdetail d join tafsili t on d.codeTafsiliMoshtari=t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.code=d.codeparent and
		codeTafsiliBestankar is null 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))
	group by t.code
	)
go


--------------------------------------------------جمع دريافت گردش چك
if exists (select name from dbo.sysobjects where name='fSumDaryaftGardeshChek2')
	drop function fSumDaryaftGardeshChek2
go
create function fSumDaryaftGardeshChek2(@TarikhAz int=0, @TarikhEla int=0)  returns table -- برای بانک ، شخص و صندوق جداگانه حساب می کنیم
return(
	select -- Bank
		t.code[Code],
		sum(m.mablagh)[bed],
		0.0[bes]
	from daryaftpardakht m,gardesheChek d join tafsili t on d.codeBedehkar=t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.code=d.codeparent and
		m.vaziat<>1 and
		t.noe = 1 and -- bank
		(isAvaldoreh is null or isAvaldoreh=0)and
		(m.vaziateChek is null or m.vaziateChek in
		(
			case noeDetail 
				when 0 then 6
				else vaziateChek
			end
		))
	group by t.code

	union all

	select -- Sandoogh
		t.code[Code],
		sum(m.mablagh)[bed],
		0.0[bes]
	from daryaftpardakht m,gardesheChek d join tafsili t on d.codeBedehkar=t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.code=d.codeparent and
		m.vaziat<>1 and
		t.noe = 3 and -- Sandoogh
		(isAvaldoreh is null or isAvaldoreh=0)and
		(m.vaziateChek is null or m.vaziateChek in (3,10) )-- kharj , NaghsShodanChekBargashti
		
	group by t.code

	union all

	select -- Sandoogh Ya Bank Nabashad
		t.code[Code],
		sum(m.mablagh)[bed],
		0.0[bes]
	from daryaftpardakht m,gardesheChek d join tafsili t on d.codeBedehkar=t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.code=d.codeparent and
		m.vaziat<>1 and
		t.noe not in (1,3)and -- Bank Ya Sandoogh Nabashad
		(isAvaldoreh is null or isAvaldoreh=0)
	
	group by t.code
	)
go


--------------------------------------------------جمع پرداخت گردش چك
if exists (select name from dbo.sysobjects where name='fSumPardakhtGardeshChek2')
	drop function fSumPardakhtGardeshChek2
go
create function fSumPardakhtGardeshChek2(@TarikhAz int=0, @TarikhEla int=0)  returns table -- برای بانک ، شخص و صندوق جداگانه حساب می کنیم
return(
	select -- Bank
		t.code[Code],
		0.0[bed],
		sum(m.mablagh)[bes]
	from daryaftpardakht m,gardesheChek d join tafsili t on d.CodeBestankar=t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.code=d.codeparent and
		m.vaziat<>1 and
		t.noe = 1 and -- bank
		(isAvaldoreh is null or isAvaldoreh=0)and
		(m.vaziateChek is null or m.vaziateChek in
		(
			case noeDetail 
				when 0 then 6
				else vaziateChek
			end
		))
	group by t.code

	union all

	select -- sandoogh
		t.code[Code],
		0.0[bed],
		sum(m.mablagh)[bes]
	from daryaftpardakht m,gardesheChek d join tafsili t on d.CodeBestankar=t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.code=d.codeparent and
		m.vaziat<>1 and
		t.noe = 3 and -- Sandoogh
		(isAvaldoreh is null or isAvaldoreh=0)and
		(m.vaziateChek is null or m.vaziateChek in (3,10) )-- kharj , NaghsShodanChekBargashti
	group by t.code

	union all
	
	select -- Bank Ya Sandoogh Nabashad
		t.code[Code],
		0.0[bed],
		sum(m.mablagh)[bes]
	from daryaftpardakht m,gardesheChek d join tafsili t on d.CodeBestankar=t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.code=d.codeparent and
		m.vaziat<>1 and
		t.noe not in (1,3) and -- bank Ya Sandoogh Nabashad
		(isAvaldoreh is null or isAvaldoreh=0)
	group by t.code
		
	)
go


--------------------------------------------------جمع دريافت گردش چك اول دوره
if exists (select name from dbo.sysobjects where name='fSumDaryaftGardeshChekAvalDoreh2')
	drop function fSumDaryaftGardeshChekAvalDoreh2
go
create function fSumDaryaftGardeshChekAvalDoreh2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.code[Code],
		sum(m.mablagh)[bed],
		0.0[bes]
	from daryaftpardakht m,gardesheChek d join tafsili t on d.codeBedehkar=t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.noeMaster=0 and
		m.code=d.codeparent 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,...
	group by t.code
	)
go




--------------------------------------------------جمع پرداخت گردش چك اول دوره
if exists (select name from dbo.sysobjects where name='fSumPardakhtGardeshChekAvalDoreh2')
	drop function fSumPardakhtGardeshChekAvalDoreh2
go
create function fSumPardakhtGardeshChekAvalDoreh2(@TarikhAz int=0, @TarikhEla int=0)  returns table
return(
	select
		t.code[Code],
		0.0[bed],
		sum(m.mablagh)[bes]
	from daryaftpardakht m,gardesheChek d join tafsili t on d.codeBestankar=t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.noeMaster=1 and
		m.code=d.codeparent 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,...
	group by t.code
	)
go


-----------------------------------جمع تخفیفات جزئیات برگه(برای محاسبه در کارت حساب هزینه)ـ
if exists (select name from dbo.sysobjects where name='fSumTakhfifBargehDetail2')
	drop function fSumTakhfifBargehDetail2
go
create function fSumTakhfifBargehDetail2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.Code[Code],
		sum(MablagheTakhfif)[bed],
		0.0[bes]
	from bargeh m,bargehDetail d join tafsili t on d.codeTakhfif=t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe in (0,4) and --foroosh, bargasht az khardi
		m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code
	)
go


-----------------------------------جمع تخفیفات جزئیات برگه(برای محاسبه در کارت حساب درآمد)ـ
if exists (select name from dbo.sysobjects where name='fSumTakhfifBargehDetailKharid2')
	drop function fSumTakhfifBargehDetailKharid2
go
create function fSumTakhfifBargehDetailKharid2(@TarikhAz int=0, @TarikhEla int=0)  returns table
return(
	select
		t.code[Code],
		0.0[bed],
		sum(MablagheTakhfif)[bes]
	from bargeh m,bargehDetail d join tafsili t on d.codeTakhfif=t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe in (1,3) and --kharid, bargasht az foroosh
		m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code
	)
go


-----------------------------------جمع تخصیص خدمات
if exists (select name from dbo.sysobjects where name='fSumTakhsisKhadamat2')
	drop function fSumTakhsisKhadamat2
go
create function fSumTakhsisKhadamat2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.code[Code],
		0.0[bed],
		sum(mablaghTakhsisKhadamat)[bes]
	from bargeh m,bargehDetail d join tafsili t on t.code=d.codetakhsisKhadamat
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		--m.noe in (1,3) and --kharid, bargasht az foroosh
		m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code
	)
go



----------------------------------- جمع بستانکاری خدمات در فاکتور
if exists (select name from dbo.sysobjects where name='fSumKhadamatFactor2')
	drop function fSumKhadamatFactor2
go
create function fSumKhadamatFactor2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.code[Code],
		0.0[bed],
		sum(mablagh*tedad-isnull(d.mablagheTakhfif,0)-isnull(mablaghTakhsisKhadamat,0))[bes]
	from bargeh m,bargehDetail d join tafsili t on d.codeKhadamat2=t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe = 0 and --foroosh
		m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code
	)
go


-------------------------------------------------جمع بدهكاري در برگه ها
if exists (select name from dbo.sysobjects where name='fSumBargehBedehkar2')
	drop function fSumBargehBedehkar2
go
create function fSumBargehBedehkar2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.code[Code],
		sum(
			(isnull(d.MablaghArzeshAfzoodeh , 0.0)) + 
			(d.mablagh*d.meghdar1*d.meghdar2*d.tedad)-
			(case when d.IsKala=0 and t.noe=4 then isnull(d.MablaghTakhsisKhadamat,0) else 0 end)-
			(case when d.codeTakhfif is not null then isnull(MablagheTakhfif,0) else 0 end)
		)[bed],
		0.0[bes]
	from bargehDetail d,bargeh m join tafsili t on t.code=m.codeMoshtari
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe in (0,4) and --foroosh, bargasht az kharid
		m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code
	
	union all------------

	select
		t.code[Code],
		0.0[bed],
		sum(mablagh)[bes]
	from daryaftpardakht d,bargeh b join tafsili t on t.code=b.codeMoshtari
	where
		b.tarikh >= @tarikhAz and
		b.tarikh <= @tarikhEla and
		d.codeBargehParent=b.code and
		d.vaziat<>1 and
		d.noeMaster = 2 and -- Hazineh
		b.noe in (0,4) -- foroosh , bargashtAzkharid
	group by t.code
	
	union all------------

	select
		t.code[Code],
		sum(mablagh)[bed],
		0.0[bes]
	from daryaftpardakht d,bargeh b join tafsili t on t.code=b.codeMoshtari
	where
		b.tarikh >= @tarikhAz and
		b.tarikh <= @tarikhEla and
		d.codeBargehParent=b.code and
		d.vaziat<>1 and
		d.noeMaster = 4 and -- hazineh Nabashd => bestankar mi shavad
		b.noe in (0,4) -- foroosh , bargashtAzkharid
	group by t.code
)---end return
go



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

if exists (select name from dbo.sysobjects where name='fSumBargehBestankar2')
	drop function fSumBargehBestankar2
go
create function fSumBargehBestankar2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.code[Code],
		0.0[bed],
		sum(

			(d.mablagh*d.meghdar1*d.meghdar2*d.tedad)-
			(case when d.IsKala=0 and t.noe=4 then isnull(d.MablaghTakhsisKhadamat,0) else 0 end)-
			(case when d.codeTakhfif is not null then isnull(MablagheTakhfif,0) else 0 end)+
			(isnull(d.MablaghArzeshAfzoodeh , 0.0))
		)[bes]
	from bargehDetail d,bargeh m join tafsili t on t.code=m.codemoshtari
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe in (1,3) and -- kharid, bargasht az foroosh
		m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code

	union all

	select
		t.code[Code],
		sum(mablagh)[bed],
		0.0[bes]
	from daryaftPardakht d,bargeh b join tafsili t on t.code=b.codeMoshtari
	where
		b.tarikh >= @tarikhAz and
		b.tarikh <= @tarikhEla and
		d.codeBargehParent=b.code and
		d.vaziat<>1 and
		d.noeMaster = 4 and -- Datamad
		b.noe in (3,1) -- kharid , bargashtAzkharid
	group by t.code
	
	union all
	
	select
		t.code[Code],
		0.0[bed],
		sum(mablagh)[bes]
	from daryaftPardakht d,bargeh b join tafsili t on t.code=b.codeMoshtari
	where
		b.tarikh >= @tarikhAz and
		b.tarikh <= @tarikhEla and
		d.codeBargehParent=b.code and
		d.vaziat<>1 and
		d.noeMaster = 2 and -- hazieh
		b.noe in (3,1) -- kharid , bargashtAzkharid
	group by t.code
)---end return
go

--------------------------جمع اول دوره شخص
if exists (select name from dbo.sysobjects where name='fSumMojoodiAvalDoreh2')
	drop function fSumMojoodiAvalDoreh2
go
create function fSumMojoodiAvalDoreh2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		Code[Code],
		case when mojoodiAvalDore<0 then -mojoodiAvalDore else 0 end [bed],
		case when mojoodiAvalDore>0 then mojoodiAvalDore else 0 end [bes]
	from tafsili where noe not in (1 , 3)
	union all
	select
		Code[Code],
		mojoodiAvalDore [bed],
		0.0 [bes]
	from tafsili 
	where 
		@tarikhAz = 0 and
		noe in (1 , 3)

)
go

--------------------------------------------------------------------------------------   هزینه های بانکی در پاس شدن چک
if exists (select name from dbo.sysobjects where name='fSumHazinehBanki2')
	drop function fSumHazinehBanki2
go
create function fSumHazinehBanki2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
	t.code[Code],
	isnull(sum(g.mablagh),0.0)[bed],
	0.0[bes]
	from gardesheChek g join tafsili t on g.codeHazineh=t.code
	where
		g.tarikh >= @tarikhAz and
		g.tarikh <= @tarikhEla
	group by t.code
)
go 


-------------------------------------------------------------------------------------- هزینه های ویزیتوری
if exists (select name from dbo.sysobjects where name='fSumVisitorFactor2')
	drop function fSumVisitorFactor2
go
create function fSumVisitorFactor2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
---------------------------------------------------- Visitori Bargeh
	select
		t.code[Code],
		0.0[bed],
		sum(isnull(m.MablagheVisitor,0))[bes]
	from bargeh m join tafsili t on m.codeVisitor=t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		(m.noe in (0,3) /*foroosh , kharid*/ ) and -- DarForoosh And Kharid => BestanKar Mi shavad
		m.vaziat<>1 and
		m.CodeVisitor is not null
	group by t.code

	union all

	select
		t.code[Code],
		sum(isnull(m.MablagheVisitor,0))[bed],
		0.0[bes]
	from bargeh m join tafsili t on m.codeVisitor=t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		(m.noe in (1,4) /*BargashtAzforoosh , BargashtAzkharid*/) and -- DarForoosh And Kharid => BedehKar Mi shavad
		m.vaziat<>1 and
		m.CodeVisitor is not null
	group by t.code

	union all
---------------------------------------------------- Visitori BargehDetail
	select
		t.code[Code],
		0.0[bed],
		sum(isnull(d.MablagheVisitor,0))[bes]
	from Bargeh m,bargehDetail d  join tafsili t on t.code=d.codeVisitor
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		(m.noe in (0,3) /*Foroosh , kharid*/ ) and -- Dar Foroosh And Kharid => BestanKar Mi shavad
		m.code = d.codeBargeh and
		m.vaziat<>1 and
		d.CodeVisitor is not null
	group by t.code

	union all

	select
		t.code[Code],
		sum(isnull(d.MablagheVisitor,0))[bed],
		0.0[bes]
	from Bargeh m,bargehDetail d  join tafsili t on t.code=d.codeVisitor
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		(m.noe in (1,4) /*BargashtAzforoosh , BargashtAzkharid*/ ) and -- DarForoosh And Kharid => BedehKar Mi shavad
		m.code = d.codeBargeh and
		m.vaziat<>1 and
		d.CodeVisitor is not null
	group by t.code
)
go


--------------------------------------------------------------------------------- بدهکاری هزینه ویزیتور
if exists (select name from dbo.sysobjects where name='fSumhazinehBedehkar2')
	drop function fSumhazinehBedehkar2
go
create function fSumhazinehBedehkar2(@TarikhAz int=0, @TarikhEla int=0)  returns table
return(
---------------------------------------------------- Visitori Bargeh
	select
		t.code[Code],
		sum(isnull(m.mablagheVisitor,0))[bed],
		0.0[bes]
	from bargeh m/*,bargehDetail d */join tafsili t on t.code=m.HazineVisitor
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe in (0,3) and -- Foroosh, Kharid
		--m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code

	union all

	select
		t.code[Code],
		0.0[bed],
		sum(isnull(m.mablagheVisitor,0))[bes]
	from bargeh m/*,bargehDetail d*/ join tafsili t on t.code = m.HazineVisitor
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe in (1,4) and -- BargahtAzForoosh, BargahtAzKharid
		--m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code

	union all
---------------------------------------------------- Visitori BargehDetail
	select
		t.code[Code],
		sum(isnull(d.mablagheVisitor,0))[bed],
		0.0[bes]
	from bargeh m,bargehDetail d join tafsili t on t.code=d.codeFarayandVisitor
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe in (0,3) and -- Foroosh, Kharid
		m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code

	union all

	select
		t.code[Code],
		0.0[bed],
		sum(isnull(d.mablagheVisitor,0))[bes]
	from bargeh m,bargehDetail d join tafsili t on t.code=d.codeFarayandVisitor
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe in (1,4) and -- BargahtAzForoosh, BargahtAzKharid
		m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code

)
go

-------------------------------------------------------------------------------------- انبارگردانی بدهکار

if exists (select name from dbo.sysobjects where name='fSumAnbarGardaniBedehkar2')
	drop function fSumAnbarGardaniBedehkar2
go
create function fSumAnbarGardaniBedehkar2(@TarikhAz int=0, @TarikhEla int=0)  RETURNS TABLE
	RETURN(
	SELECT
		t.code[Code],
		sum(d.mablagh * d.tedad) [bed],
		0.0 [bes]
	from AnbarGardani m join AnbarGardaniDetail d
	on m.code = d.CodeAnbarGardani
	join Tafsili t ON d.CodeTafsili = t.Code
	WHERE
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.vaziat <> 1 and --deleted
		d.noe = 0 -- Hazineh
		
	GROUP BY t.code
	)
GO

------------------------------------------------------------------------------------------ انبارگردانی بستانکار

if exists (select name from dbo.sysobjects where name='fSumAnbarGardaniBestankar2')
	drop function fSumAnbarGardaniBestankar2
go
create function fSumAnbarGardaniBestankar2(@TarikhAz int=0, @TarikhEla int=0) RETURNS TABLE
	RETURN(
	SELECT
		t.code[Code],
		0.0[bed],
		sum(d.mablagh * d.tedad) [bes]
	from AnbarGardani m join AnbarGardaniDetail d
	on m.code = d.CodeAnbarGardani
	join Tafsili t ON d.CodeTafsili = t.Code
	WHERE
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.vaziat <> 1 and --deleted
		d.noe = 1 -- Daramad
		
	GROUP BY t.code
	)
GO

----------------------------------- جمع بستانکاری ارزش افزوده در برگه دیتیل
if exists (select name from dbo.sysobjects where name='fSumArzeshAfzoodehBargehDetailBestanKar2')
	drop function fSumArzeshAfzoodehBargehDetailBestanKar2
go
create function fSumArzeshAfzoodehBargehDetailBestanKar2(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.Code[Code],
		0.0[bed],
		sum(isnull(d.MablaghArzeshAfzoodeh , 0))[bes]
	from bargeh m,bargehDetail d join tafsili t on d.codeArzeshAfzoodeh = t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe in (0,4) and --foroosh, bargasht az khardi
		m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code
	)
go


----------------------------------- جمع بدهکاری ارزش افزوده در برگه دیتیل
if exists (select name from dbo.sysobjects where name='fSumArzeshAfzoodehBargehDetailBedehKar2')
	drop function fSumArzeshAfzoodehBargehDetailBedehKar2
go
create function fSumArzeshAfzoodehBargehDetailBedehKar2(@TarikhAz int=0, @TarikhEla int=0)  returns table
return(
	select
		t.code[Code],
		sum(isnull(d.MablaghArzeshAfzoodeh , 0))[bed],
		0.0[bes]
	from bargeh m,bargehDetail d join tafsili t on d.codeArzeshAfzoodeh = t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.noe in (1,3) and --kharid, bargasht az foroosh
		m.code=d.codebargeh and
		m.vaziat<>1
	group by t.code
	)
go
-------------------------------------------------------------- بستانکاری های تولید
if exists (select name from dbo.sysobjects where name='fSumTolidBestankar')
	drop function fSumTolidBestankar
go
create function fSumTolidBestankar(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.Code[Code],
		0.0[bed],
		(cast(sum(d.Mablagh * m.Tedad ) as decimal(19,4)))[bes]
	from Tolid m, TolidDetail d join tafsili t on d.CodeTafsili = t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.code = d.codeTolid and
		m.vaziat <> 1
		--m.Noe in (0,1) -- Tolid , Tabdil
	group by t.code
	)
go
--------------------------------------------------------------------------------- گردش حساب کلی
IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='fAllCodeBedBes')
	DROP function fAllCodeBedBes
go
create function fAllCodeBedBes(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select code,bed,bes from dbo.fSumMojoodiAvalDoreh2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumDaryaft2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumPardakht2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumDaryaftChekAvaldoreh2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumPardakhtChekAvaldoreh2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumChekDaryaftiAzChandNafar2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumChekPardakhtiBeChandNafar2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumDaryaftGardeshChek2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumPardakhtGardeshChek2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumDaryaftGardeshChekAvalDoreh2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumPardakhtGardeshChekAvalDoreh2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumTakhfifBargehDetail2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumTakhfifBargehDetailKharid2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumTakhsisKhadamat2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumKhadamatFactor2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumBargehBedehkar2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumBargehBestankar2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumhazinehBedehkar2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumVisitorFactor2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumHazinehBanki2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumAnbarGardaniBestankar2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumAnbarGardaniBedehkar2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumArzeshAfzoodehBargehDetailBestanKar2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumArzeshAfzoodehBargehDetailBedehKar2(@TarikhAz , @TarikhEla)
	union all
	select code,bed,bes from dbo.fSumTolidBestankar(@TarikhAz , @TarikhEla)
)
GO



--------------------------------------------------------------------------------- گردش حساب کلی
IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='prcPersonCardMaster')
	DROP proc prcPersonCardMaster
GO
--prcPersonCardMaster
CREATE proc prcPersonCardMaster (@TarikhAz int=0, @TarikhEla int=0)  as
begin
select
	t.code,CodeHesab,name,noe,CodeGrooheHazine , CodeGroohAshkas , CodeGroohKhadamat ,
	sum(f.bed)[bedehkar],
	sum(f.bes)[bestankar],
	0.0[Mandeh],
	''[Mahiat],
	tel1[Tel1],
	tel2[Tel2],
	Mobile[Mobile],
	address[Adress],
	AddressHome[AdressHome],
	isTakhfifEzafe[IsTakhfifEzafe],
	IsActive[IsActive]
from tafsili t left outer join dbo.fAllCodeBedBes(@TarikhAz , @TarikhEla) f on t.code=f.code
group by t.code,codeHesab,name,noe,tel1,tel2,mobile,address,addressHome,CodeGrooheHazine,CodeGroohAshkas,CodeGroohKhadamat,isTakhfifEzafe,IsActive
end
go