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


CREATE proc PrcGetSaalMaliSanadMovaghat 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 in (5 , 6)
	
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 in (5 , 6)
	
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 in (5 , 6)
	
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 in (5 , 6)
	
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 in (5 , 6)
	
group by d.codekol , d.codemoin , d.CodeTafsili , d.CodeTafsili2 , d.CodeTafsili3

end