--select * from dbo.fGetTarazAzmayeshi(33,0,0,0,0,0,0,0,0,0,0,0,0,'','8')  
alter function fGetTarazAzmayeshi  
  (  
   @FcId int = 0,  
   @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)='',
   @StrOrgIds nvarchar(100)=''  
  ) returns table  
return   
( 
------------------------------------------------------------------------------------------------ مانده دوره قبل  
select   
 s.ID [ID],  
 s.Code[Code],  
 s.Tarikh[Tarikh] ,  
 k.ID [Kol_ID],   
 isnull(m.ID,0) [Moin_ID],   
 isnull(t.ID,0) [Taf_ID],  
 isnull(t2.ID,0) [Taf2_ID],  
 
 k.Code [Kol_Code],   
 isnull(m.Code,'') [Moin_Code],   
 isnull(t.Code,0) [Taf_Code],  
 isnull(t2.Code,0) [Taf2_Code],  
 
 k.Title [Kol_Title],   
 isnull(m.title,'') [Moin_Title],   
 isnull(t.Title,'') [Taf_Title],  
 isnull(t2.Title,'') [Taf2_Title],  
  
 sum(d.Debit) [MandehDorehGhablBedehKar] ,  
 sum(d.Credit) [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 AccountItem k on k.ID = d.AI_ID_Kol
    left join AccountItem m on d.AI_ID_Moin = m.ID  
    left join Tafsili t on d.Taf_ID = t.ID  
    left join Tafsili t2 on d.Taf2_ID = t2.ID
where  
	s.fc_id = @FcId and
 s.tarikh < @TarikhAz   
 and (@CodeHesabKolAz = 0 or k.Code >= @CodeHesabKolAz)  
 and (@CodeHesabKolEla = 0 or k.Code <= @CodeHesabKolEla)  
 and (@CodeHesabMoinAz = 0 or m.Code >= @CodeHesabMoinAz)  
 and (@CodeHesabMoinEla = 0 or m.Code <= @CodeHesabMoinEla)  
 and (@CodeHesabTafsiliAz = 0 or t.Code >= @CodeHesabTafsiliAz)  
 and (@CodeHesabTafsiliEla = 0 or t.Code <= @CodeHesabTafsiliEla)  
 and (@CodeHesabTafsili2Az = 0 or t2.Code >= @CodeHesabTafsili2Az)  
 and (@CodeHesabTafsili2Ela = 0 or t2.Code <= @CodeHesabTafsili2Ela)  
 and (@StrVaziatSanad = '' or s.Dom_ID_Vaziat in (@StrVaziatSanad) )  
 and (@StrOrgIds = '' or s.Pro_ID_VahedMali in (@StrOrgIds) )  
group by  s.ID , s.Code, s.Tarikh, k.ID, k.Code, k.Title , m.ID , m.Code , m.Title , t.ID , t.Code , t.Title , t2.ID , t2.Code , t2.Title
------------------------------------------------------------------------------------------------ افتتاحیه  
union all  
select   
 s.ID [ID],  
 s.Code[Code],  
 s.Tarikh[Tarikh] ,  
 k.ID[Kol_ID],   
 isnull(m.ID,0) [Moin_ID],   
 isnull(t.ID,0) [Taf_ID],  
 isnull(t2.ID,0) [Taf2_ID],
 
 k.Code [Kol_Code],   
 isnull(m.Code,'') [Moin_Code],   
 isnull(t.Code,0) [Taf_Code],  
 isnull(t2.Code,0) [Taf2_Code],  
 
 k.Title [Kol_Title],   
 isnull(m.title,'') [Moin_Title],   
 isnull(t.Title,'') [Taf_Title],  
 isnull(t2.Title,'') [Taf2_Title],  
 
 0.0 [MandehDorehGhablBedehKar] ,  
 0.0 [MandehDorehGhablBestanKar],  
 sum(d.Debit) [MandehAvalDorehBedehKar] , -- افتتاحیه  
 sum(d.Credit) [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 AccountItem k on k.ID = d.AI_ID_Kol
    left join AccountItem m on d.AI_ID_Moin = m.ID
    left join Tafsili t on d.Taf_ID = t.ID
    left join Tafsili t2 on d.Taf2_ID = t2.ID
where  
	s.fc_id = @FcId and
 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.code >= @CodeHesabKolAz)  
 and (@CodeHesabKolEla = 0 or k.code <= @CodeHesabKolEla)  
 and (@CodeHesabMoinAz = 0 or m.code >= @CodeHesabMoinAz)  
 and (@CodeHesabMoinEla = 0 or m.code <= @CodeHesabMoinEla)  
 and (@CodeHesabTafsiliAz = 0 or t.code >= @CodeHesabTafsiliAz)  
 and (@CodeHesabTafsiliEla = 0 or t.code <= @CodeHesabTafsiliEla)  
 and (@CodeHesabTafsili2Az = 0 or t2.code >= @CodeHesabTafsili2Az)  
 and (@CodeHesabTafsili2Ela = 0 or t2.code <= @CodeHesabTafsili2Ela)  
 and (@StrVaziatSanad = '' or s.Dom_ID_Vaziat in (@StrVaziatSanad) )  
 and (@StrOrgIds = '' or s.Pro_ID_VahedMali in (@StrOrgIds) )  
group by  s.ID , s.Code, s.Tarikh, k.ID, k.Code , k.Title , m.ID , m.Code , m.Title , t.ID , t.Code , t.Title , t2.ID , t2.Code , t2.Title
------------------------------------------------------------------------------------------------ طی دوره  
union all  
select   
 s.ID [ID],  
 s.Code[Code],  
 s.Tarikh[Tarikh] ,  
 k.ID[Kol_ID],   
 isnull(m.ID,0) [Moin_ID],   
 isnull(t.ID,0) [Taf_ID],  
 isnull(t2.ID,0) [Taf2_ID],  
 
 k.Code [Kol_Code],   
 isnull(m.Code,'') [Moin_Code],   
 isnull(t.Code,0) [Taf_Code],  
 isnull(t2.Code,0) [Taf2_Code],  
 
 k.Title [Kol_Title],   
 isnull(m.title,'') [Moin_Title],   
 isnull(t.Title,'') [Taf_Title],  
 isnull(t2.Title,'') [Taf2_Title],  
 
 0.0 [MandehDorehGhablBedehKar] ,  
 0.0 [MandehDorehGhablBestanKar],  
 0.0 [MandehAvalDorehBedehKar] , -- افتتاحیه  
 0.0 [MandehAvalDorehBestanKar] , -- افتتاحیه  
 sum(d.Debit) [GardeshBedehKar] ,  
 sum(d.Credit) [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 AccountItem k on k.ID = d.AI_ID_Kol
    left join AccountItem m on d.AI_ID_Moin = m.ID  
    left join Tafsili t on d.Taf_ID = t.ID  
    left join Tafsili t2 on d.Taf2_ID = t2.ID
where  
	s.fc_id = @FcId and
 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.code >= @CodeHesabKolAz)  
 and (@CodeHesabKolEla = 0 or k.code <= @CodeHesabKolEla)  
 and (@CodeHesabMoinAz = 0 or m.code >= @CodeHesabMoinAz)  
 and (@CodeHesabMoinEla = 0 or m.code <= @CodeHesabMoinEla)  
 and (@CodeHesabTafsiliAz = 0 or t.code >= @CodeHesabTafsiliAz)  
 and (@CodeHesabTafsiliEla = 0 or t.code <= @CodeHesabTafsiliEla)  
 and (@CodeHesabTafsili2Az = 0 or t2.code >= @CodeHesabTafsili2Az)  
 and (@CodeHesabTafsili2Ela = 0 or t2.code <= @CodeHesabTafsili2Ela)  
 and (@StrVaziatSanad = '' or s.Dom_ID_Vaziat in (@StrVaziatSanad) )  
 and (@StrOrgIds = '' or s.Pro_ID_VahedMali in (@StrOrgIds) )  
group by  s.ID , s.Code, s.Tarikh, k.ID, k.Code , k.Title , m.ID , m.Code , m.Title , t.ID , t.Code , t.Title , t2.ID , t2.Code , t2.Title
------------------------------------------------------------------------------------------------ اختتامیه  
union all  
select   
 s.ID [ID],  
 s.Code[Code],  
 s.Tarikh[Tarikh] ,  
 k.ID[Kol_ID],   
 --k.CodeHesab[CodeHesabKol],   
 --k.Name [NameKol] ,  
 isnull(m.ID,0) [Moin_ID],   
 isnull(t.ID,0) [Taf_ID],  
 isnull(t2.ID,0) [Taf2_ID],  
 
 k.Code [Kol_Code],   
 isnull(m.Code,'') [Moin_Code],   
 isnull(t.Code,0) [Taf_Code],  
 isnull(t2.Code,0) [Taf2_Code],  
 
 k.Title [Kol_Title],   
 isnull(m.title,'') [Moin_Title],   
 isnull(t.Title,'') [Taf_Title],  
 isnull(t2.Title,'') [Taf2_Title],  
 
 0.0 [MandehDorehGhablBedehKar] ,  
 0.0 [MandehDorehGhablBestanKar],  
 0.0 [MandehAvalDorehBedehKar] , -- افتتاحیه  
 0.0 [MandehAvalDorehBestanKar] , -- افتتاحیه  
 0.0 [GardeshBedehKar] ,  
 0.0 [GardeshBestanKar] ,  
 sum(d.Debit) [MandehPayanDorehBedehKar] , -- اختتامیه  
 sum(d.Credit) [MandehPayanDorehBestanKar] , -- -- اختتامیه  
 0.0[MandehBedehkar] ,  
 0.0[MandehBestankar]  
from Sanad s   
    inner join SanadDetail d on s.ID = d.San_ID
    inner join AccountItem k on k.ID = d.AI_ID_Kol
    left join AccountItem m on d.AI_ID_Moin = m.ID
    left join Tafsili t on d.Taf_ID = t.ID
    left join Tafsili t2 on d.Taf2_ID = t2.ID
where  
	s.fc_id = @FcId and
 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.code >= @CodeHesabKolAz)  
 and (@CodeHesabKolEla = 0 or k.code <= @CodeHesabKolEla)  
 and (@CodeHesabMoinAz = 0 or m.code >= @CodeHesabMoinAz)  
 and (@CodeHesabMoinEla = 0 or m.code <= @CodeHesabMoinEla)  
 and (@CodeHesabTafsiliAz = 0 or t.code >= @CodeHesabTafsiliAz)  
 and (@CodeHesabTafsiliEla = 0 or t.code <= @CodeHesabTafsiliEla)  
 and (@CodeHesabTafsili2Az = 0 or t2.code >= @CodeHesabTafsili2Az)  
 and (@CodeHesabTafsili2Ela = 0 or t2.code <= @CodeHesabTafsili2Ela)  
 and (@StrVaziatSanad = '' or s.Dom_ID_Vaziat in (@StrVaziatSanad) )  
 and (@StrOrgIds = '' or s.Pro_ID_VahedMali in (@StrOrgIds) )  
group by  s.ID , s.Code, s.Tarikh, k.ID, k.Code , k.Title , m.ID , m.Code , m.Title , t.ID , t.Code , t.Title , t2.ID , t2.Code , t2.Title
)