IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='PrcGetSaalMaliSanadDaem')
	DROP proc PrcGetSaalMaliSanadDaem
GO


CREATE proc PrcGetSaalMaliSanadDaem as
begin

select
	0[Code],
	0[CodeSanad],
	d.codeKol[CodeKol],
	null[codeMoin],
	null[codeTafsili],
	null[codeTafsili2],
	null[codeTafsili3],
	sum(isnull(mablaghBedehkar,0))[MablaghBedehkar],
	sum(isnull(mablaghBestankar,0))[MablaghBestankar],
	0.0[Mandeh],
	''[Sharh]
from sanad m,sanadDetail d, kol k
where
	m.code=d.codeSanad and
	k.code=d.codeKol and
	codeKol is not null and
	codeMoin is null and
	codeTafsili is null and
	codeTafsili2 is null and
	codeTafsili3 is null and
	k.MahiatHesab not in (5 , 6) and
	m.code not in (2000000000 , 2000000001)
	
group by d.codeKol

union all

select
	0[Code],
	0[CodeSanad],
	d.codeKol,
	d.CodeMoin[codeMoin],
	null[codeTafsili],
	null[codeTafsili2],
	null[codeTafsili3],
	sum(isnull(mablaghBedehkar,0))[MablaghBedehkar],
	sum(isnull(mablaghBestankar,0))[MablaghBestankar],
	0.0[Mandeh],
	''[Sharh]
from sanad m,sanadDetail d, kol k
where
	m.code=d.codeSanad and
	k.code=d.codeKol and
	codeKol is not null and
	codeMoin is not null and
	codeTafsili is null and
	codeTafsili2 is null and
	codeTafsili3 is null and
	k.MahiatHesab not in (5 , 6) and
	m.code not in (2000000000 , 2000000001)
	
group by d.codekol , d.codemoin

union all

select
	0[Code],
	0[CodeSanad],
	d.codeKol,
	d.CodeMoin[codeMoin],
	d.CodeTafsili[codeTafsili],
	null[codeTafsili2],
	null[codeTafsili3],
	sum(isnull(mablaghBedehkar,0))[MablaghBedehkar],
	sum(isnull(mablaghBestankar,0))[MablaghBestankar],
	0.0[Mandeh],
	''[Sharh]
from sanad m,sanadDetail d, kol k
where
	m.code=d.codeSanad and
	k.code=d.codeKol and
	codeKol is not null and
	codeMoin is not null and
	codeTafsili is not null and
	codeTafsili2 is null and
	codeTafsili3 is null and
	k.MahiatHesab not in (5 , 6) and
	m.code not in (2000000000 , 2000000001)
	
group by d.codekol , d.codemoin , d.CodeTafsili

union all

select
	0[Code],
	0[CodeSanad],
	d.codeKol,
	d.CodeMoin[codeMoin],
	d.CodeTafsili[codeTafsili],
	d.CodeTafsili2[codeTafsili2],
	null[codeTafsili3],
	sum(isnull(mablaghBedehkar,0))[MablaghBedehkar],
	sum(isnull(mablaghBestankar,0))[MablaghBestankar],
	0.0[Mandeh],
	''[Sharh]
from sanad m,sanadDetail d, kol k
where
	m.code=d.codeSanad and
	k.code=d.codeKol and
	codeKol is not null and
	codeMoin is not null and
	codeTafsili is not null and
	codeTafsili2 is not null and
	codeTafsili3 is null and
	k.MahiatHesab not in (5 , 6) and
	m.code not in (2000000000 , 2000000001)
	
group by d.codekol , d.codemoin , d.CodeTafsili , d.CodeTafsili2

union all

select
	0[Code],
	0[CodeSanad],
	d.codeKol,
	d.CodeMoin[codeMoin],
	d.CodeTafsili[codeTafsili],
	d.CodeTafsili2[codeTafsili2],
	d.CodeTafsili3[codeTafsili3],
	sum(isnull(mablaghBedehkar,0))[MablaghBedehkar],
	sum(isnull(mablaghBestankar,0))[MablaghBestankar],
	0.0[Mandeh],
	''[Sharh]
from sanad m,sanadDetail d, kol k
where
	m.code=d.codeSanad and
	k.code=d.codeKol and
	codeKol is not null and
	codeMoin is not null and
	codeTafsili is not null and
	codeTafsili2 is not null and
	codeTafsili3 is not null and
	k.MahiatHesab not in (5 , 6) and
	m.code not in (2000000000 , 2000000001)
	
group by d.codekol , d.codemoin , d.CodeTafsili , d.CodeTafsili2 , d.CodeTafsili3

end