/*
drop function fSumDaryaft
drop function fSumDaryaft2
drop function fSumPardakht
drop function fSumPardakht2
drop function fSumPardakhtChekAvaldoreh
drop function fSumPardakhtChekAvaldoreh2
drop function fSumChekDaryaftiAzChandNafar
drop function fSumChekDaryaftiAzChandNafar2
drop function fSumChekPardakhtiBeChandNafar
drop function fSumChekPardakhtiBeChandNafar2
drop function fSumDaryaftGardeshChek
drop function fSumDaryaftGardeshChek2
drop function fSumDaryaftGardeshChekAvalDoreh
drop function fSumDaryaftGardeshChekAvalDoreh2
drop function fSumPardakhtGardeshChek
drop function fSumPardakhtGardeshChek2
drop function fSumPardakhtGardeshChekAvalDoreh
drop function fSumPardakhtGardeshChekAvalDoreh2
drop function fSumTakhfifBargehDetail
drop function fSumTakhfifBargehDetail2
drop function fSumTakhfifBargehDetailKharid
drop function fSumTakhfifBargehDetailKharid2
drop function fSumTakhsisKhadamat
drop function fSumTakhsisKhadamat2
drop function fSumKhadamatFactor
drop function fSumKhadamatFactor2
drop function fSumBargehBedehkar
drop function fSumBargehBedehkar2
drop function fSumBargehBestankar
drop function fSumBargehBestankar2
drop function fSumMojoodiAvalDoreh
drop function fSumMojoodiAvalDoreh2
drop function fSumHazinehBanki
drop function fSumHazinehBanki2
drop function fSumVisitorFactor
drop function fSumVisitorFactor2
drop function fSumhazinehBedehkar
drop function fSumhazinehBedehkar2
drop function fSumAnbarGardaniBedehkar2
drop function fSumAnbarGardaniBestankar2
drop function fSumArzeshAfzoodehBargehDetailBestanKar2
drop function fSumArzeshAfzoodehBargehDetailBedehKar2
drop function fSumTolidBestankar
drop function fSumTamiratBestankar_BedehKar
drop function fSumHoghoogh
drop function fSumPardakht2
*/
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 -- صندوق نباشد
		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 != 51 and /*Sandoogh*/
		d.vaziat<>101 AND --deleted
		(d.isAvaldoreh is null or d.isAvaldoreh=0) and
		(d.vaziateChek is null or d.vaziateChek in
		(
		case  when t.noe = 49 /*Bank*/ and d.noeDetail = 44 /*Chek*/ then 112 /*Pass*/
			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 = 44 and
		t.noe = 51 and -- Sandoogh
		d.vaziat<>101 AND --deleted
		(d.isAvaldoreh is null or d.isAvaldoreh=0) and
		(d.vaziateChek is null or d.vaziateChek in ( 114 ,116 ,117))-- DaryaftnaghdiAzMoshtari ,NaghsShodanChekBargashti, NaghdShodanChek 

	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 != 44 and
		t.noe = 51 and -- Sandoogh
		d.vaziat<>101 AND --deleted
		(d.isAvaldoreh is null or d.isAvaldoreh=0) and
		(d.vaziateChek is null or d.vaziateChek in  ( 114 ,116 ,117))-- DaryaftnaghdiAzMoshtari ,NaghsShodanChekBargashti, NaghdShodanChek 
	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 = 53 and /*Projeh*/
		d.vaziat<>101 --deleted
	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 != 51 and
		d.vaziat<>101 and
		(d.isAvaldoreh is null or d.isAvaldoreh=0) and
		(d.vaziateChek is null or d.vaziateChek in
		(
		case  when t.noe = 49 and d.noeDetail = 44 then 112
			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 = 51 and
		d.vaziat<>101 and
		(d.isAvaldoreh is null or d.isAvaldoreh=0) and
		d.noeDetail != 44 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 = 53 and /*Projeh*/
		d.vaziat<>101 --deleted
	GROUP BY t.code
	union all
	--------------------------------------------------جمع دريافت هاي چكهاي اول دوره شخص
	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 != 51 and -- Sandoogh
		noeMaster = 38 and
		vaziat<>101 and
		isAvaldoreh = 1 and
		(vaziateChek is null or vaziateChek in
		(
		case  when t.noe = 49 and noeDetail = 44 then 112
			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 = 51 and -- Sandoogh
		noeMaster=38 and
		vaziat<>101 and
		isAvaldoreh=1 and
		(vaziateChek is null or vaziateChek in (114,116,117)) -- 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 = 38 and
		vaziat<>101 and
		isAvaldoreh=1 and
		t.noe = 50 and -- shakhs
		vaziateChek in (116) --NaghdShodanChekBargashti
	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 = 38 and
		vaziat<>101 and
		isAvaldoreh=1 and
		t.noe = 49 and
		(vaziateChek is null or vaziateChek in
		(
		case  when noeDetail = 44 then 112
			else vaziateChek
		end
		))
	group by t.code
	union all
	-------------------------------------------------- جمع دريافت گردش چك
	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 <> 101 and
		t.noe = 49 and -- bank
		(isAvaldoreh is null or isAvaldoreh = 0)and
		(m.vaziateChek is null or m.vaziateChek in
		(
			case noeDetail 
				when 44 then 112
				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 = 51 and -- Sandoogh
		(isAvaldoreh is null or isAvaldoreh=0)and
		(m.vaziateChek is null or d.vaziat in (109 , 117) )-- 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<>101 and
		t.noe not in (49,51)and -- Bank Ya Sandoogh Nabashad
		(isAvaldoreh is null or isAvaldoreh=0)

	group by t.code
	union all
	--------------------------------------------------جمع دريافت گردش چك اول دوره
	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=38 and
		m.code = d.codeparent and
		m.vaziat <> 101 and
		t.noe = 49 and -- Bank
		isAvaldoreh = 1 and
		(m.vaziateChek is null or m.vaziateChek in
		(
			case noeDetail 
				when 44 then 112
				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 = 38 and
		m.code = d.codeparent and
		m.vaziat <> 101 and
		t.noe = 51 and -- Sandoogh
		isAvaldoreh = 1 and
		(m.vaziateChek is null or d.vaziat in (109 , 117))-- 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 = 38 and
		m.code = d.codeparent and
		m.vaziat <> 101 and
		t.noe not in (49,51)and -- Bank Ya Sandoogh Nabashad
		isAvaldoreh = 1
	group by t.code
	union all
	select -- Shakhs
		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 = 38 and
		m.code = d.codeparent and
		m.vaziat <> 101 and
		t.noe = 50 and -- Shakhs
		d.vaziat = 118 and -- MogoodShodanChekKharjShodeh
		isAvaldoreh = 1
	group by t.code
	union all
	--------------------------------------------------جمع پرداخت گردش چك
	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<>101 and
		t.noe = 49 and -- bank
		(isAvaldoreh is null or isAvaldoreh=0)and
		(m.vaziateChek is null or m.vaziateChek in
		(
			case noeDetail 
				when 44 then 112
				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<>101 and
		t.noe = 51 and -- Sandoogh
		(isAvaldoreh is null or isAvaldoreh=0)and
		(m.vaziateChek is null or d.vaziat in (118) )-- 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<>101 and
		t.noe not in (49,51) and -- bank Ya Sandoogh Nabashad
		(isAvaldoreh is null or isAvaldoreh=0)
	group by t.code
	union all
	--------------------------------------------------جمع پرداخت گردش چك اول دوره
	select -- Bank
		t.code[Code],
		0.0[bed],
		sum(m.mablagh)[bes]
	from daryaftpardakht m
		join gardesheChek d on m.code = d.codeparent and d.Vaziat = 112
		join  tafsili t on m.CodeTafsiliBestankar = t.code
	where
		d.tarikh >= @tarikhAz and
		d.tarikh <= @tarikhEla and
		m.noeMaster = 39 and
		m.vaziat<>101 and
		t.noe = 49 and -- bank
		isAvaldoreh = 1 and
		(m.vaziateChek is null or m.vaziateChek in
		(
			case noeDetail 
				when 44 then 112
				else vaziateChek
			end
		))
	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 = 39 and
		m.code = d.codeparent and
		m.vaziat <> 101 and
		t.noe not in (49,51) and -- bank Ya Sandoogh Nabashad
		isAvaldoreh = 1
	group by t.code
	union all
	-----------------------------------جمع تخفیفات جزئیات برگه(برای محاسبه در کارت حساب هزینه)ـ
	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 (28,32) and --foroosh, bargasht az khardi
		m.code=d.codebargeh and
		m.vaziat<>101
	group by t.code
	union all
	-----------------------------------جمع تخفیفات جزئیات برگه(برای محاسبه در کارت حساب درآمد)ـ
	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 (29,31) and --kharid, bargasht az foroosh
		m.code=d.codebargeh and
		m.vaziat<>101
	group by t.code
	union all
	-----------------------------------جمع تخصیص خدمات
	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<>101
	group by t.code
	union all
	----------------------------------- جمع بستانکاری خدمات در فاکتور
	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 = 28 and --foroosh
		m.code=d.codebargeh and
		m.vaziat<>101
	group by t.code
	union all
	-------------------------------------------------جمع بدهكاري در برگه ها
	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=52 /*Khadamat*/ 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 (28,32) and --foroosh, bargasht az kharid
		m.code=d.codebargeh and
		m.vaziat<>101
	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<>101 and
		d.noeMaster = 40 and -- Hazineh
		b.noe in (28,32) -- 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<>101 and
		d.noeMaster = 42 and -- Daramad
		b.noe in (28,32) -- foroosh , bargashtAzkharid
	group by t.code
	union all
	-------------------------------------------------جمع بستانکاری در برگه ها
	select
		t.code[Code],
		0.0[bed],
		sum(

			(d.mablagh*d.meghdar1*d.meghdar2*d.tedad)-
			(case when d.IsKala=0 and t.noe=52 /*Khadamat*/ 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 (29,31) and -- kharid, bargasht az foroosh
		m.code=d.codebargeh and
		m.vaziat<>101
	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<>101 and
		d.noeMaster = 42 and -- Datamad
		b.noe in (31,29) -- kharid , FactorBargashtAzForoosh 
	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<>101 and
		d.noeMaster = 40 and -- hazieh
		b.noe in (31,29) -- kharid , FactorBargashtAzForoosh 
	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=52 /*Khadamat*/ 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 (28,32) and --foroosh, bargasht az kharid
		m.code=d.codebargeh and
		m.vaziat<>101
	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<>101 and
		d.noeMaster = 40 and -- Hazineh
		b.noe in (28,32) -- 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 = 53 and /*Projeh*/
		d.vaziat<>101 and --deleted
		d.noeMaster = 42 and /*Daramad*/
		b.noe in (28,32) -- foroosh , bargashtAzkharid
	GROUP BY t.code
	union all
	--------------------------جمع اول دوره شخص
	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 (49 , 51)
		
	union all
	select
		Code[Code],
		mojoodiAvalDore [bed],
		0.0 [bes]
	from tafsili 
	where 
		@tarikhAz = 0 and
		noe in (49 , 51)
	union all
	--------------------------------------------------------------------------------------   هزینه های بانکی در پاس شدن چک
	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
	union all
	-------------------------------------------------------------------------------------- هزینه های ویزیتوری
	---------------------------------------------------- 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 (28,31) /*foroosh , kharid*/ ) and -- DarForoosh And Kharid => BestanKar Mi shavad
		m.vaziat<>101 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 (29,32) /*BargashtAzforoosh , BargashtAzkharid*/) and -- DarForoosh And Kharid => BedehKar Mi shavad
		m.vaziat<>101 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 (28,31) /*Foroosh , kharid*/ ) and -- Dar Foroosh And Kharid => BestanKar Mi shavad
		m.code = d.codeBargeh and
		m.vaziat<>101 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 (29,32) /*BargashtAzforoosh , BargashtAzkharid*/ ) and -- DarForoosh And Kharid => BedehKar Mi shavad
		m.code = d.codeBargeh and
		m.vaziat<>101 and
		d.CodeVisitor is not null
	group by t.code
	union all
	--------------------------------------------------------------------------------- بدهکاری هزینه ویزیتور
	---------------------------------------------------- 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 (28,31) and -- Foroosh, Kharid
		--m.code=d.codebargeh and
		m.vaziat<>101
	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 (29,32) and -- BargahtAzForoosh, BargahtAzKharid
		--m.code=d.codebargeh and
		m.vaziat<>101
	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 (28,31) and -- Foroosh, Kharid
		m.code=d.codebargeh and
		m.vaziat<>101
	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 (29,32) and -- BargahtAzForoosh, BargahtAzKharid
		m.code=d.codebargeh and
		m.vaziat<>101
	group by t.code
	union all
	-------------------------------------------------------------------------------------- انبارگردانی بدهکار
	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 <> 101 and --deleted
		d.noe = 253 -- Hazineh
		
	GROUP BY t.code
	union all
	------------------------------------------------------------------------------------------ انبارگردانی بستانکار
	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 <> 101 and --deleted
		d.noe = 254 -- Daramad
		
	GROUP BY t.code
	union all
	----------------------------------- جمع بستانکاری ارزش افزوده در برگه دیتیل
	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 (28,32) and --foroosh, bargasht az khardi
		m.code=d.codebargeh and
		m.vaziat<>101
	group by t.code
	union all
	----------------------------------- جمع بدهکاری ارزش افزوده در برگه دیتیل
	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 (29,31) and --kharid, bargasht az foroosh
		m.code=d.codebargeh and
		m.vaziat<>101
	group by t.code
	union all
	-------------------------------------------------------------- دستمزدهای های تولید
	select
		t.Code[Code],
		0.0 [bed],
		sum(d.price) [bes]
	from Product m, ProductDetail d join tafsili t on d.taf_ID = t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.id = d.prod_ID and
		m.dom_ID_vaziat <> 101 and
		d.Dom_ID_Noe = 262 /*Dastmozd*/
	group by t.code
	union all
	-------------------------------------------------------------- هزینه های تولید
	select
		t.Code[Code],
		sum(d.price) [bed],
		0.0 [bes]
	from Product m, ProductDetail d join tafsili t on d.taf_ID = t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.id = d.prod_ID and
		m.dom_ID_vaziat <> 101 and
		d.Dom_ID_Noe = 261 /*Hazineh*/
	group by t.code
	union all
	-------------------------------------------------------------- پرداختی های هزینه های تولید
	select
		t.Code[Code],
		0.0 [bed],
		sum(d.price) [bes]
	from Product m, ProductDetail d join tafsili t on d.taf_ID_Bestankar = t.code
	where
		m.tarikh >= @tarikhAz and
		m.tarikh <= @tarikhEla and
		m.id = d.prod_ID and
		m.dom_ID_vaziat <> 101 and
		d.Dom_ID_Noe = 261 /*Hazineh*/
	group by t.code
	union all
	-------------------------------------------------------------- بستانکاری تعمیرات
	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 (289,290) -- 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 (289,290) -- 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 (289,290) -- 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 (289,290) -- 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 (289,290) -- 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 (289,290) -- 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 = 288 -- 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 = 288 -- TahvilBeMoshtari

	group by t.code
	---------------------------------------------------------------- بدهکاری بستانکاری محاسبه حقوق
	--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
	----------------------------------------------------------------------------------- گردش حساب کلی
	--	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 , 13940101
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