--select * from dbo.F_T_GetJournal(30,'',14000101,15000101,'')
alter function F_T_GetJournal(
	@Fc_ID int=0,
	@strPro_ID_VahedMali nvarchar(200)='',
	@fromDate int=0, 
	@toDate int=0,
	@strTarikh nvarchar(max)=''
	) returns table
return(

select 
	cast(kda.ID as bigint) Reference_ID,
	kda.Tarikh,
	'موجودی اول دوره ' + k.Name Row_Title,
	cast((MojoodiAvalDoreh * GheymatAvalDoreh) as decimal(19,4)) Price
from KalaDarAnbar kda
	join kala k on kda.Kal_ID = k.id
where
	kda.Tarikh >= @fromDate and kda.Tarikh <= @toDate and
	(@Fc_ID = 0 or kda.fc_id = @Fc_ID)
union all
---------------------------------------------------------------------------- TafsiliRemind
select
	tr.ID,
	tr.Tarikh,
	'مانده اول دوره (' + dom.Title + ') - ' + t.Title,
	Price
from TafsiliRemind tr
	join Tafsili t on tr.Taf_ID = t.ID
	join Domain dom on t.Dom_ID_Noe = dom.ID
where
	(@Fc_ID = 0 or tr.fc_id = @Fc_ID) and
	tr.Tarikh >= @fromDate and tr.Tarikh <= @toDate
union all
---------------------------------------------------------------------------- Bargeh
select
	b.ID,
	b.Tarikh,
	dom.Title + ' (' + cast(b.Code as nvarchar(100)) + ')',
	cast((case 
		when b.Dom_ID_noe = 28 then
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0.0))
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) - 
			case when b.Taf_ID_Takhfif > 0 then isnull(b.MablaghTakhfif,0) else 0.0 end + 
			case when b.Taf_ID_Ezafeh > 0 then isnull(b.MablaghEzafeh,0) else 0.0 end
		when b.Dom_ID_noe = 29 then
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad  - isnull(d.MablagheTakhfif , 0.0))
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) - 
			case when b.Taf_ID_Takhfif > 0 then isnull(b.MablaghTakhfif,0) else 0.0 end + 
			case when b.Taf_ID_Ezafeh > 0 then isnull(b.MablaghEzafeh,0) else 0.0 end
		
		when b.Dom_ID_noe = 31 then
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad  - isnull(d.MablagheTakhfif , 0.0))
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) - 
			case when b.Taf_ID_Takhfif > 0 then isnull(b.MablaghTakhfif,0) else 0.0 end + 
			case when b.Taf_ID_Ezafeh > 0 then isnull(b.MablaghEzafeh,0) else 0.0 end
		when b.Dom_ID_noe = 32 then
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0.0))
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) - 
			case when b.Taf_ID_Takhfif > 0 then isnull(b.MablaghTakhfif,0) else 0.0 end + 
			case when b.Taf_ID_Ezafeh > 0 then isnull(b.MablaghEzafeh,0) else 0.0 end
		when b.Dom_ID_noe = 792 /* کسورات انبارگرداني */then
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad)
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0)
		
		when b.Dom_ID_noe = 791 /* اضافات انبارگرداني */then
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad)
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0)
		else 0.0
	end) as decimal(19,4))
from Bargeh b
	join Domain dom on b.Dom_ID_Noe = dom.ID
where
	(@Fc_ID = 0 or b.fc_id = @Fc_ID) and
	b.Dom_ID_Vaziat <> 101 and
	b.Tarikh >= @fromDate and b.Tarikh <= @toDate 
union all
---------------------------------------------------------------------------- DaryaftPardakht
select
	d.ID,
	d.Tarikh,
	dom.Title + ' ' + domDetail.Title,
	cast(d.Mablagh as decimal(19,4))
from DaryaftPardakht d
	join Domain dom on d.Dom_ID_NoeMaster = dom.ID
	join Domain domDetail on d.Dom_ID_NoeDetail = domDetail.ID
where
	(@Fc_ID = 0 or d.fc_id = @Fc_ID) and
	d.Dom_ID_NoeDetail != 44 and
	d.Bar_ID is null and
	d.Tarikh >= @fromDate and d.Tarikh <= @toDate
union all
---------------------------------------------------------------------------- GardesheChek
select
	g.ID,
	g.Tarikh,
	'گردش '+ domDetail.Title + ' ' + dom.Title + 'ی - '+domGardesh.Title,
	cast(isnull(g.Mablagh, d.Mablagh) as decimal(19,4))
from GardesheChek g
	join DaryaftPardakht d on g.DaryaftPardakht_ID = d.ID
	join Domain dom on d.Dom_ID_NoeMaster = dom.ID
	join Domain domDetail on d.Dom_ID_NoeDetail = domDetail.ID
	join Domain domGardesh on g.Dom_ID_Vaziat = domGardesh.ID
where
	(@Fc_ID = 0 or g.fc_id = @Fc_ID) and
	g.Tarikh >= @fromDate and g.Tarikh <= @toDate
	
union all
---------------------------------------------------------------------------- Product
select 
	pd.ID,
	pd.Tarikh,
	dom.Title,
	cast((case 
		when pd.Dom_ID_Noe in( 259 /*Zayeaat*/,260 /*KalaMasrafi*/, 463 /*KalaTolidi*/) then isnull(pd.Price,0.0) * ISNULL(pd.Tedad,0.0)
		when pd.Dom_ID_Noe = 262 /*Dastmozd*/ then isnull(pd.Price,0.0) 
		when pd.Dom_ID_Noe = 262 /*Dastmozd*/ then isnull(pd.Price,0.0) 
		else 0.0 
	end ) as decimal(19,4))
from Product p
	join ProductDetail pd on p.ID = pd.Prod_ID
	join Domain dom on pd.Dom_ID_Noe = dom.ID
where
	(@Fc_ID = 0 or pd.fc_id = @Fc_ID) and
	pd.Tarikh >= @fromDate and pd.Tarikh <= @toDate and
	p.Dom_ID_Vaziat <> 101

--select * from Domain where ID = 258 or Dom_ID = 258
)