if exists(select name from sysobjects where name ='fGetTarazAzmayeshi')
	drop function fGetTarazAzmayeshi
go
--select * from dbo.fGetTarazAzmayeshi(0,0,0,0,0,0,0,0,0,0,0,0,'')  
create function fGetTarazAzmayeshi  
  (  
   @CodeSanadAz int = 0,  
   @CodeSanadEla int = 0,  
   @TarikhAz int = 0,  
   @TarikhEla int = 0,  
   @CodeHesabKolAz bigint = 0,  
   @CodeHesabKolEla bigint = 0,  
   @CodeHesabMoinAz bigint = 0,  
   @CodeHesabMoinEla bigint = 0,  
   @CodeHesabTafsiliAz bigint = 0,  
   @CodeHesabTafsiliEla bigint = 0,  
   @CodeHesabTafsili2Az bigint = 0,  
   @CodeHesabTafsili2Ela bigint = 0,  
   @StrVaziatSanad nvarchar(100)=''  
  ) returns table  
return   
( 
------------------------------------------------------------------------------------------------ مانده دوره قبل  
select   
 s.ID [ID],  
 s.Code[Code],  
 s.Tarikh[Tarikh] ,  
 k.code[Kol_ID],   
 --k.CodeHesab[CodeHesabKol],   
 --k.Name [NameKol] ,  
 isnull(m.Code,0) [Moin_ID],   
 isnull(t.Code,0) [Taf_ID],  
 isnull(t2.Code,0) [Taf2_ID],  
  
 sum(d.BedehKar) [MandehDorehGhablBedehKar] ,  
 sum(d.BestanKar) [MandehDorehGhablBestanKar],  
 0.0 [MandehAvalDorehBedehKar] , -- افتتاحیه  
 0.0 [MandehAvalDorehBestanKar] , -- افتتاحیه  
 0.0 [GardeshBedehKar] ,  
 0.0 [GardeshBestanKar] ,  
 0.0 [MandehPayanDorehBedehKar] , -- اختتامیه  
 0.0 [MandehPayanDorehBestanKar] , -- -- اختتامیه  
 0.0[MandehBedehkar] ,  
 0.0[MandehBestankar]  
from Sanad s   
    inner join SanadDetail d on s.ID = d.San_ID
    inner join Kol k on k.code = d.Kol_ID
    left join Moin m on d.Moin_ID = m.Code  
    left join Tafsili t on d.Taf_ID = t.Code  
    left join Tafsili t2 on d.Taf2_ID = t2.Code    
where  
 s.tarikh < @TarikhAz   
 and (@CodeHesabKolAz = 0 or k.codeHesab >= @CodeHesabKolAz)  
 and (@CodeHesabKolEla = 0 or k.codeHesab <= @CodeHesabKolEla)  
 and (@CodeHesabMoinAz = 0 or m.codeHesab >= @CodeHesabMoinAz)  
 and (@CodeHesabMoinEla = 0 or m.codeHesab <= @CodeHesabMoinEla)  
 and (@CodeHesabTafsiliAz = 0 or t.codeHesab >= @CodeHesabTafsiliAz)  
 and (@CodeHesabTafsiliEla = 0 or t.codeHesab <= @CodeHesabTafsiliEla)  
 and (@CodeHesabTafsili2Az = 0 or t2.codeHesab >= @CodeHesabTafsili2Az)  
 and (@CodeHesabTafsili2Ela = 0 or t2.codeHesab <= @CodeHesabTafsili2Ela)  
 and (@StrVaziatSanad = '' or s.Dom_ID_Vaziat in (@StrVaziatSanad) )  
group by  s.ID , s.Code, s.Tarikh, k.code, k.CodeHesab, k.Name , m.Code , m.CodeHesab , m.Name , t.Code , t.CodeHesab , t.Name , t2.Code , t2.CodeHesab , t2.Name  
------------------------------------------------------------------------------------------------ افتتاحیه  
union all  
select   
 s.ID [ID],  
 s.Code[Code],  
 s.Tarikh[Tarikh] ,  
 k.code[Kol_ID],   
 --k.CodeHesab[CodeHesabKol],   
 --k.Name [NameKol] ,  
 isnull(m.Code,0) [Moin_ID],   
 isnull(t.Code,0) [Taf_ID],  
 isnull(t2.Code,0) [Taf2_ID],  
  
 0.0 [MandehDorehGhablBedehKar] ,  
 0.0 [MandehDorehGhablBestanKar],  
 sum(d.BedehKar) [MandehAvalDorehBedehKar] , -- افتتاحیه  
 sum(d.BestanKar) [MandehAvalDorehBestanKar] , -- افتتاحیه  
 0.0 [GardeshBedehKar] ,  
 0.0 [GardeshBestanKar] ,  
 0.0 [MandehPayanDorehBedehKar] , -- اختتامیه  
 0.0 [MandehPayanDorehBestanKar] , -- -- اختتامیه  
 0.0[MandehBedehkar] ,  
 0.0[MandehBestankar]  
from Sanad s   
    inner join SanadDetail d on s.ID = d.San_ID
    inner join Kol k on k.code = d.Kol_ID
    left join Moin m on d.Moin_ID = m.Code  
    left join Tafsili t on d.Taf_ID = t.Code  
    left join Tafsili t2 on d.Taf2_ID = t2.Code    
where  
 s.Dom_ID_Noe = 158 -- افتتاحیه  
 and (@CodeSanadAz = 0 or s.Code >= @CodeSanadAz)  
 and (@CodeSanadEla = 0 or s.Code <= @CodeSanadEla)  
 and (@TarikhAz = 0 or s.tarikh >= @TarikhAz )  
 and (@TarikhEla = 0 or s.tarikh <= @TarikhEla )  
 and (@CodeHesabKolAz = 0 or k.codeHesab >= @CodeHesabKolAz)  
 and (@CodeHesabKolEla = 0 or k.codeHesab <= @CodeHesabKolEla)  
 and (@CodeHesabMoinAz = 0 or m.codeHesab >= @CodeHesabMoinAz)  
 and (@CodeHesabMoinEla = 0 or m.codeHesab <= @CodeHesabMoinEla)  
 and (@CodeHesabTafsiliAz = 0 or t.codeHesab >= @CodeHesabTafsiliAz)  
 and (@CodeHesabTafsiliEla = 0 or t.codeHesab <= @CodeHesabTafsiliEla)  
 and (@CodeHesabTafsili2Az = 0 or t2.codeHesab >= @CodeHesabTafsili2Az)  
 and (@CodeHesabTafsili2Ela = 0 or t2.codeHesab <= @CodeHesabTafsili2Ela)  
 and (@StrVaziatSanad = '' or s.Dom_ID_Vaziat in (@StrVaziatSanad) )  
group by  s.ID , s.Code, s.Tarikh, k.code, k.CodeHesab, k.Name , m.Code , m.CodeHesab , m.Name , t.Code , t.CodeHesab , t.Name , t2.Code , t2.CodeHesab , t2.Name  
------------------------------------------------------------------------------------------------ طی دوره  
union all  
select   
 s.ID [ID],  
 s.Code[Code],  
 s.Tarikh[Tarikh] ,  
 k.code[Kol_ID],   
 --k.CodeHesab[CodeHesabKol],   
 --k.Name [NameKol] ,  
 isnull(m.Code,0) [Moin_ID],   
 isnull(t.Code,0) [Taf_ID],  
 isnull(t2.Code,0) [Taf2_ID],  
  
 0.0 [MandehDorehGhablBedehKar] ,  
 0.0 [MandehDorehGhablBestanKar],  
 0.0 [MandehAvalDorehBedehKar] , -- افتتاحیه  
 0.0 [MandehAvalDorehBestanKar] , -- افتتاحیه  
 sum(d.BedehKar) [GardeshBedehKar] ,  
 sum(d.BestanKar) [GardeshBestanKar] ,  
 0.0 [MandehPayanDorehBedehKar] , -- اختتامیه  
 0.0 [MandehPayanDorehBestanKar] , -- -- اختتامیه  
 0.0[MandehBedehkar] ,  
 0.0[MandehBestankar]  
from Sanad s   
    inner join SanadDetail d on s.ID = d.San_ID
    inner join Kol k on k.code = d.Kol_ID
    left join Moin m on d.Moin_ID = m.Code  
    left join Tafsili t on d.Taf_ID = t.Code  
    left join Tafsili t2 on d.Taf2_ID = t2.Code    
where  
 s.Dom_ID_Noe not in (158,160) -- افتتاحیه و اختتامیه  
 and (@CodeSanadAz = 0 or s.Code >= @CodeSanadAz)  
 and (@CodeSanadEla = 0 or s.Code <= @CodeSanadEla)  
 and (@TarikhAz = 0 or s.tarikh >= @TarikhAz )  
 and (@TarikhEla = 0 or s.tarikh <= @TarikhEla )  
 and (@CodeHesabKolAz = 0 or k.codeHesab >= @CodeHesabKolAz)  
 and (@CodeHesabKolEla = 0 or k.codeHesab <= @CodeHesabKolEla)  
 and (@CodeHesabMoinAz = 0 or m.codeHesab >= @CodeHesabMoinAz)  
 and (@CodeHesabMoinEla = 0 or m.codeHesab <= @CodeHesabMoinEla)  
 and (@CodeHesabTafsiliAz = 0 or t.codeHesab >= @CodeHesabTafsiliAz)  
 and (@CodeHesabTafsiliEla = 0 or t.codeHesab <= @CodeHesabTafsiliEla)  
 and (@CodeHesabTafsili2Az = 0 or t2.codeHesab >= @CodeHesabTafsili2Az)  
 and (@CodeHesabTafsili2Ela = 0 or t2.codeHesab <= @CodeHesabTafsili2Ela)  
 and (@StrVaziatSanad = '' or s.Dom_ID_Vaziat in (@StrVaziatSanad) )  
group by  s.ID , s.Code, s.Tarikh, k.code, k.CodeHesab, k.Name , m.Code , m.CodeHesab , m.Name , t.Code , t.CodeHesab , t.Name , t2.Code , t2.CodeHesab , t2.Name  
------------------------------------------------------------------------------------------------ اختتامیه  
union all  
select   
 s.ID [ID],  
 s.Code[Code],  
 s.Tarikh[Tarikh] ,  
 k.code[Kol_ID],   
 --k.CodeHesab[CodeHesabKol],   
 --k.Name [NameKol] ,  
 isnull(m.Code,0) [Moin_ID],   
 isnull(t.Code,0) [Taf_ID],  
 isnull(t2.Code,0) [Taf2_ID],  
  
 0.0 [MandehDorehGhablBedehKar] ,  
 0.0 [MandehDorehGhablBestanKar],  
 0.0 [MandehAvalDorehBedehKar] , -- افتتاحیه  
 0.0 [MandehAvalDorehBestanKar] , -- افتتاحیه  
 0.0 [GardeshBedehKar] ,  
 0.0 [GardeshBestanKar] ,  
 sum(d.BedehKar) [MandehPayanDorehBedehKar] , -- اختتامیه  
 sum(d.BestanKar) [MandehPayanDorehBestanKar] , -- -- اختتامیه  
 0.0[MandehBedehkar] ,  
 0.0[MandehBestankar]  
from Sanad s   
    inner join SanadDetail d on s.ID = d.San_ID
    inner join Kol k on k.code = d.Kol_ID
    left join Moin m on d.Moin_ID = m.Code  
    left join Tafsili t on d.Taf_ID = t.Code  
    left join Tafsili t2 on d.Taf2_ID = t2.Code    
where  
 s.Dom_ID_Noe = 160 -- اختتامیه  
 and (@CodeSanadAz = 0 or s.Code >= @CodeSanadAz)  
 and (@CodeSanadEla = 0 or s.Code <= @CodeSanadEla)  
 and (@TarikhAz = 0 or s.tarikh >= @TarikhAz )  
 and (@TarikhEla = 0 or s.tarikh <= @TarikhEla )  
 and (@CodeHesabKolAz = 0 or k.codeHesab >= @CodeHesabKolAz)  
 and (@CodeHesabKolEla = 0 or k.codeHesab <= @CodeHesabKolEla)  
 and (@CodeHesabMoinAz = 0 or m.codeHesab >= @CodeHesabMoinAz)  
 and (@CodeHesabMoinEla = 0 or m.codeHesab <= @CodeHesabMoinEla)  
 and (@CodeHesabTafsiliAz = 0 or t.codeHesab >= @CodeHesabTafsiliAz)  
 and (@CodeHesabTafsiliEla = 0 or t.codeHesab <= @CodeHesabTafsiliEla)  
 and (@CodeHesabTafsili2Az = 0 or t2.codeHesab >= @CodeHesabTafsili2Az)  
 and (@CodeHesabTafsili2Ela = 0 or t2.codeHesab <= @CodeHesabTafsili2Ela)  
 and (@StrVaziatSanad = '' or s.Dom_ID_Vaziat in (@StrVaziatSanad) )  
group by  s.ID , s.Code, s.Tarikh, k.code, k.CodeHesab, k.Name , m.Code , m.CodeHesab , m.Name , t.Code , t.CodeHesab , t.Name , t2.Code , t2.CodeHesab , t2.Name  
)