--select * from [dbo].[fGetCirculationOfGood]()
alter function [dbo].fGetCirculationOfGood(@Fc_ID int ,@Pro_ID_VahedMali int) returns table
return (
select * , cast(isnull( (MojoodiAvalDoreh+Kharid+BargashAzForoosh+KalaTolid+ZayeaatTolid+Additions+TrustedGoodsPurchase)-(BargashtAzKharid+Foroosh+KalaMasrafi+Deductions+TrustedGoodsSale+StationSale),0) as decimal(19,4)) Mandeh
from (
	select 
		k.ID,
		cast(isnull((
			select 
				SUM(kda.MojoodiAvalDoreh) 
			from KalaDarAnbar kda 
			where 
				kda.Kal_ID = k.ID and
				(@Fc_ID = 0 or kda.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or kda.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) MojoodiAvalDoreh,
		cast(isnull((
			select SUM(d.Tedad) 
			from Bargeh b 
				inner join BargehDetail d on b.ID = d.Bar_ID 
			where 
				b.Dom_ID_Noe = 31 and 
				b.Dom_ID_Vaziat != 101 and 
				d.Dom_ID_Noe = 666 and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) Kharid,
		cast(isnull((
			select SUM(d.Tedad) 
			from Bargeh b 
				inner join BargehDetail d on b.ID = d.Bar_ID 
			where 
				b.Dom_ID_Noe = 1162 and 
				b.Dom_ID_Vaziat != 101 and 
				d.Dom_ID_Noe = 666 and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) TrustedGoodsPurchase,
		cast(isnull((
			select SUM(d.Tedad) 
			from Bargeh b 
				inner join BargehDetail d on b.ID = d.Bar_ID 
			where
				b.Dom_ID_Noe = 32 and 
				b.Dom_ID_Vaziat != 101 and 
				d.Dom_ID_Noe = 666 and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) BargashtAzKharid,
		cast(isnull((
			select SUM(d.Tedad) 
			from Bargeh b 
				inner join BargehDetail d on b.ID = d.Bar_ID 
			where
				b.Dom_ID_Noe = 28 and 
				b.Dom_ID_Vaziat != 101 and 
				d.Dom_ID_Noe = 666 and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) Foroosh,
		cast(isnull((
			select SUM(d.Tedad) 
			from Bargeh b 
				inner join BargehDetail d on b.ID = d.Bar_ID 
			where
				b.Dom_ID_Noe = 1163 and 
				b.Dom_ID_Vaziat != 101 and 
				d.Dom_ID_Noe = 666 and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) TrustedGoodsSale,
		cast(isnull((
			select SUM(d.Tedad) 
			from Bargeh b 
				inner join BargehDetail d on b.ID = d.Bar_ID 
			where
				b.Dom_ID_Noe = 1190 and 
				b.Dom_ID_Vaziat != 101 and 
				d.Dom_ID_Noe = 666 and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) StationSale,
		cast(isnull((
			select SUM(d.Tedad) 
			from Bargeh b 
				inner join BargehDetail d on b.ID = d.Bar_ID 
			where
				b.Dom_ID_Noe = 29 and 
				b.Dom_ID_Vaziat != 101 and 
				d.Dom_ID_Noe = 666 and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) BargashAzForoosh,
		cast(isnull((
			select SUM(d.Tedad) 
			from Bargeh b 
				inner join BargehDetail d on b.ID = d.Bar_ID 
			where
				b.Dom_ID_Noe = 36 /*EnteghalBeynAnbar*/ and 
				b.Dom_ID_Vaziat != 101 and 
				d.Dom_ID_Noe = 666 and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) EnteghalAnbarSadereh,
		cast(isnull((
			select SUM(d.Tedad) 
			from Bargeh b 
				inner join BargehDetail d on b.ID = d.Bar_ID 
			where
				b.Dom_ID_Noe = 791 and 
				b.Dom_ID_Vaziat != 101 and 
				d.Dom_ID_Noe = 666 and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) Additions,
		cast(isnull((
			select SUM(d.Tedad) 
			from Bargeh b 
				inner join BargehDetail d on b.ID = d.Bar_ID 
			where 
				b.Dom_ID_Noe = 792 and 
				b.Dom_ID_Vaziat != 101 and 
				d.Dom_ID_Noe = 666 and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) Deductions,
		cast(isnull((
			select SUM(d.Tedad) 
			from Product m 
				inner join ProductDetail d on m.ID = d.Prod_ID 
			where
				m.Dom_ID_Vaziat != 101 and 
				d.dom_ID_Noe = 463 /*کالاي توليدي - هدر*/ and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or d.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or d.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) KalaTolid,
		cast(isnull((
			select SUM(d.Tedad) 
			from Product m 
			inner join ProductDetail d on m.ID = d.Prod_ID 
			where
				m.Dom_ID_Vaziat != 101 and 
				d.dom_ID_Noe = 260 /*کالاي مصرفي*/ and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or d.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or d.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) KalaMasrafi,
		cast(isnull((
			select SUM(d.Tedad) 
			from Product m 
				inner join ProductDetail d on m.ID = d.Prod_ID 
			where
				m.Dom_ID_Vaziat != 101 and 
				d.dom_ID_Noe = 259 /*ضايعات*/ and 
				d.Kal_ID = k.ID and
				(@Fc_ID = 0 or d.fc_ID = @Fc_ID) and
				(@Pro_ID_VahedMali = 0 or d.pro_ID_VahedMali = @Pro_ID_VahedMali)
		),0) as decimal(19,4)) ZayeaatTolid
	
	from kala k
)x)