-------------------------------دريافت در جدول دريافت پرداخت
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
			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
		join gardesheChek g on d.code = g.codeParent
	WHERE
		g.tarikh >= @tarikhAz and
		g.tarikh <= @tarikhEla and
		g.vaziat = d.vaziateChek and
		d.noedetail = 0 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 ( 8 ,10 ,11))-- DaryaftnaghdiAzMoshtari ,NaghsShodanChekBargashti

	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
		--join gardesheChek g on d.code = g.codeParent
	WHERE
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		--g.vaziat = d.vaziateChek and
		d.noedetail != 0 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 ( 8 ,10 ,11))-- DaryaftnaghdiAzMoshtari ,NaghsShodanChekBargashti

	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.CodeProjehBedehkar =t.Code
	WHERE
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		t.noe = 5 and /*Projeh*/
		d.vaziat<>1 --deleted
	GROUP BY t.code
	
	--union all
	
	--SELECT 
	--	t.code[Code],
	--	0.0[bed],
	--	sum(d.mablagh)[bes]
	--from bargeh b 
	--	join daryaftpardakht d on b.code = d.codeBargehParent
	--	join Tafsili t ON b.CodeProjeh = t.Code
	--WHERE
	--	d.tarikh >= @tarikhAz and
	--	d.tarikh <= @tarikhEla and
	--	t.noe = 5 and /*Projeh*/
	--	d.vaziat<>1 and --deleted
	--	d.noeMaster = 2 and /*Hazineh*/
	--	b.noe = 0 /*Foroosh*/
	--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
		t.noe != 3 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
			else d.vaziateChek
		end
		))
	GROUP BY t.code

	union all

	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
		t.noe = 3 and
		d.vaziat<>1 and
		(d.isAvaldoreh is null or d.isAvaldoreh=0) and
		d.noeDetail != 0 and -- Chek
		d.vaziateChek is null

	GROUP BY t.code
	
	union all 
	
	SELECT ---------------------------------------------------------------------------- پروژه
		t.code[Code],
		0.0 [bed],
		sum(d.mablagh) [bes]
	from daryaftpardakht d join Tafsili t ON d.CodeProjehBestankar =t.Code
	WHERE
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		t.noe = 5 and /*Projeh*/
		d.vaziat<>1 --deleted
	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 -- Sandoogh Nabashad
		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 -- Sandoogh
		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 (8,10,11)) -- DaryaftnaghdiAzMoshtari, NaghsShodanChekBargashti, Naghd Shodan chek
	group by t.code

	union all
	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 = 0 and
		vaziat<>1 and
		isAvaldoreh=1 and
		t.noe = 2 and -- shakhs
		vaziateChek in (10) --NaghdShodanChekBargashti
	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
		t.noe = 1 and
		(vaziateChek is null or vaziateChek in
		(
		case  when noeDetail = 0 then 6
			else vaziateChek
		end
		))
	group by t.code

-- در پرداخت چک اول دوره فقط بانک بستانکار است
/*union all
	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
		t.noe = 3 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 d.vaziat in (3 , 11) )-- kharj , NaghdShodanChek
		
	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='fSumDaryaftGardeshChekAvalDoreh2')
	drop function fSumDaryaftGardeshChekAvalDoreh2
go
create function fSumDaryaftGardeshChekAvalDoreh2(@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.noeMaster=0 and
		m.code = d.codeparent and
		m.vaziat <> 1 and
		t.noe = 1 and -- Bank
		isAvaldoreh = 1 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.noeMaster = 0 and
		m.code = d.codeparent and
		m.vaziat <> 1 and
		t.noe = 3 and -- Sandoogh
		isAvaldoreh = 1 and
		(m.vaziateChek is null or d.vaziat in (3 , 11))-- kharj , NaghdSodanChek
	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.noeMaster = 0 and
		m.code = d.codeparent and
		m.vaziat <> 1 and
		t.noe not in (1,3)and -- Bank Ya Sandoogh Nabashad
		isAvaldoreh = 1
	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 d.vaziat in (12) )-- MojoodShodanChekKharjShodeh
	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='fSumPardakhtGardeshChekAvalDoreh2')
	drop function fSumPardakhtGardeshChekAvalDoreh2
go
create function fSumPardakhtGardeshChekAvalDoreh2(@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.noeMaster = 1 and
		m.code = d.codeparent and
		m.vaziat<>1 and
		t.noe = 1 and -- bank
		isAvaldoreh = 1 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 -- 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.noeMaster = 1 and
		m.code = d.codeparent and
		m.vaziat <> 1 and
		t.noe not in (1,3) and -- bank Ya Sandoogh Nabashad
		isAvaldoreh = 1
	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 -- Daramad
		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
	
	union all------------
	------------------------------------ برای پروژه 
	select
		t.code[Code],
		0.0[bed],
		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)
		)[bes]
	from bargehDetail d,bargeh m join tafsili t on t.code=m.codeProjeh
	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],
		sum(mablagh)[bed],
		0.0 [bes]
	from daryaftpardakht d,bargeh b join tafsili t on t.code=b.codeProjeh
	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],
		0.0[bed],
		sum(d.mablagh)[bes]
	from bargeh b 
		join daryaftpardakht d on b.code = d.codeBargehParent
		join Tafsili t ON b.CodeProjeh = t.Code
	WHERE
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		t.noe = 5 and /*Projeh*/
		d.vaziat<>1 and --deleted
		d.noeMaster = 4 and /*Daramad*/
		b.noe in (0,4) /*Foroosh,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 dbo.sysobjects where name='fSumTamiratBestankar_BedehKar')
	drop function fSumTamiratBestankar_BedehKar
go
create function fSumTamiratBestankar_BedehKar(@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select -----------------------------------  بستانکاری خدمات
		t.Code[Code],
		0.0[bed],
		(cast(sum(m.MablaghHazineh) as decimal(19,4)))[bes]
	from Tamirat m join tafsili t on m.codeKhadamat = t.code
	where
		m.tarikhPaziresh >= @tarikhAz and
		m.tarikhPaziresh <= @tarikhEla and
		m.vaziatTamir not in (4,5) -- LaghvShodeh , TabdilBeFactorForoosh
	group by t.code
	
	union all

	select -----------------------------------  بستانکاری بیعانه مشتری
		t.Code[Code],
		0.0[bed],
		(cast(sum(m.MablaghBeiaaneh) as decimal(19,4)))[bes]
	from Tamirat m join tafsili t on m.codeMoshtari = t.code
	where
		m.tarikhPaziresh >= @tarikhAz and
		m.tarikhPaziresh <= @tarikhEla and
		m.vaziatTamir not in (4,5) -- LaghvShodeh , TabdilBeFactorForoosh
	group by t.code

	union all

	select -----------------------------------  بستانکاری تعمیرکار
		t.Code[Code],
		0.0[bed],
		(cast(sum(m.MablaghTamirKar) as decimal(19,4)))[bes]
	from Tamirat m join tafsili t on m.codeTamirKar = t.code
	where
		m.tarikhPaziresh >= @tarikhAz and
		m.tarikhPaziresh <= @tarikhEla and
		m.vaziatTamir not in (4,5) -- LaghvShodeh , TabdilBeFactorForoosh
	group by t.code
	union all

	select -----------------------------------  بدهکاری مشتری
		t.Code[Code],
		(cast(sum(m.MablaghHazineh) as decimal(19,4)))[bed],
		0.0[bes]
	from Tamirat m join tafsili t on m.codeMoshtari = t.code
	where
		m.tarikhPaziresh >= @tarikhAz and
		m.tarikhPaziresh <= @tarikhEla and
		m.vaziatTamir not in (4,5) -- LaghvShodeh , TabdilBeFactorForoosh
	group by t.code

	union all

	select -----------------------------------  بدهکاری بیعانه صندوق
		t.Code[Code],
		(cast(sum(m.MablaghBeiaaneh) as decimal(19,4)))[bed],
		0.0[bes]
	from Tamirat m join tafsili t on m.codeSandoogh = t.code
	where
		m.tarikhPaziresh >= @tarikhAz and
		m.tarikhPaziresh <= @tarikhEla and
		m.vaziatTamir not in (4,5) -- LaghvShodeh , TabdilBeFactorForoosh
	group by t.code

	union all

	select -----------------------------------  بدهکاری هزینه تعمیرکار
		t.Code[Code],
		(cast(sum(m.MablaghTamirKar) as decimal(19,4)))[bed],
		0.0[bes]
	from Tamirat m join tafsili t on m.codeHazinehTamirKar = t.code
	where
		m.tarikhPaziresh >= @tarikhAz and
		m.tarikhPaziresh <= @tarikhEla and
		m.vaziatTamir not in (4,5) -- LaghvShodeh , TabdilBeFactorForoosh

	group by t.code
	
	union all
	select -----------------------------------  بستانکاری دریافت نقدی شخص در گردش تعمیرات
		t.Code[Code],
		0.0[bed],
		(cast(sum(d.Mablagh) as decimal(19,4)))[bes]
	from Tamirat m join GardeshTamirat d on m.code = d.codeParent join tafsili t on m.codeMoshtari = t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.vaziatTamir = 3 -- TahvilBeMoshtari

	group by t.code

	union all
	select -----------------------------------  بدهکاری دریافت نقدی صندوق در گردش تعمیرات
		t.Code[Code],
		(cast(sum(d.Mablagh) as decimal(19,4)))[bed],
		0.0[bes]
	from Tamirat m join GardeshTamirat d on m.code = d.codeParent join tafsili t on d.codeTafsiliBedehKar = t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.vaziatTamir = 3 -- TahvilBeMoshtari

	group by t.code

	)
go

-------------------------------------------------------------- بدهکاری بستانکاری محاسبه حقوق
if exists (select name from dbo.sysobjects where name='fSumHoghoogh')
	drop function fSumHoghoogh
go
create function fSumHoghoogh (@TarikhAz int=0, @TarikhEla int=0) returns table
return(
	select
		t.Code[Code],
		sum(d.MablaghBedehKar)[bed],
		sum(d.MablaghBestanKar)[bes]
	from MohasebehHoghoogh m, MohasebehHoghooghDetail d join tafsili t on d.CodeTafsili = t.code
	where
		m.tarikhMohasebeh >= @tarikhAz and
		m.tarikhMohasebeh <= @tarikhEla and
		m.code = d.codeParent and
		m.vaziat <> 1
	group by t.code

	union all
	select
		t.Code[Code],
		sum(d.MablaghBedehKar)[bed],
		sum(d.MablaghBestanKar)[bes]
	from MohasebehHoghoogh m, MohasebehHoghooghDetail d join tafsili t on d.CodeTafsili2 = t.code
	where
		m.tarikhMohasebeh >= @tarikhAz and
		m.tarikhMohasebeh <= @tarikhEla and
		m.code = d.codeParent and
		m.vaziat <> 1
	group by t.code

	union all
	select
		t.Code[Code],
		sum(d.MablaghBedehKar)[bed],
		sum(d.MablaghBestanKar)[bes]
	from MohasebehHoghoogh m, MohasebehHoghooghDetail d join tafsili t on d.CodeTafsili3 = t.code
	where
		m.tarikhMohasebeh >= @tarikhAz and
		m.tarikhMohasebeh <= @tarikhEla and
		m.code = d.codeParent and
		m.vaziat <> 1
	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)
	union all
	select code,bed,bes from dbo.fSumTamiratBestankar_BedehKar(@TarikhAz , @TarikhEla)
	union all 
	select code,bed,bes from dbo.fSumHoghoogh(@TarikhAz , @TarikhEla)
)
GO



--------------------------------------------------------------------------------- گردش حساب کلی
IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='prcPersonCardMaster')
	DROP proc prcPersonCardMaster
GO
--prcPersonCardMaster 0 , 1392
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],
	TarikhAkharinFaaliat[TarikhAkharinFaaliat],
	ShowYadAvari[ShowYadAvari],
	TedadRoozYadAvari[TedadRoozYadAvari],
	MahiatYadAvari[MahiatYadAvari],
	t.sharh,
	t.NamePedar , 
	t.ShomareShenasname , 
	t.CodeMeli
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,
	TarikhAkharinFaaliat,
	ShowYadAvari,
	TedadRoozYadAvari,
	MahiatYadAvari,
	t.sharh,
	t.NamePedar , 
	t.ShomareShenasname , 
	t.CodeMeli
order by noe, codehesab
end
go