--select * from dbo.F_T_GetMianginPrice(0,0,0,258,0)
alter function dbo.F_T_GetMianginPrice(@Fc_ID as integer =0, @Pro_ID_VahedMali as integer, @Pro_ID_Anbar as integer=0,@Kal_ID as integer=0,@Tarikh as integer=0) returns table    
return (  
	select
		Pro_ID_Anbar,
		Kal_ID,
		cast(SumPrice as float) SumPrice,
		cast(SumQuantity as decimal(19,4)) SumQuantity,
		cast(case when SumQuantity = 0 then 0 else SumPrice/SumQuantity end as decimal(19,4)) AvgPrice
	from (  
		select    
			d.Pro_ID_Anbar,
			d.Kal_ID,
			sum(isnull(d.mablagh,0)*isnull(d.tedad,0)*isnull(d.meghdar1,1)*isnull(d.meghdar2,1)) SumPrice,  
			sum(isnull(d.tedad,0)*isnull(d.meghdar1,1)*isnull(d.meghdar2,1)) SumQuantity
		from bargeh b 
			join bargehDetail d on d.Bar_ID =b.ID    
		where    
			d.Dom_ID_Noe = 666 and    
			b.Dom_ID_noe in(31,29,791,1162) and  -- خرید - برگشت از فروش - اضافات انبارگرداني - خرید کالای امانی
			b.Dom_ID_vaziat<>101 and--deleted    
			(@Fc_ID = 0 or b.fc_ID = @Fc_ID) and
			(@Pro_ID_VahedMali = 0 or b.pro_ID_VahedMali = @Pro_ID_VahedMali) and
			(@Pro_ID_Anbar = 0 or d.Pro_ID_Anbar = @Pro_ID_Anbar) and
			(@Kal_ID = 0 or d.Kal_ID = @Kal_ID) and
			(@Tarikh = 0 or b.Tarikh <= @Tarikh)
		group by 
			d.Kal_ID,
			d.Pro_ID_Anbar
		union all
		select   
			k.Pro_ID_Anbar,
			k.Kal_ID, 
			sum(isnull(k.gheymatAvalDoreh,0)*isnull(k.mojoodiAvalDoreh,0)),
			sum(isnull(k.mojoodiAvalDoreh,0))    
		from kalaDarAnbar k 
		where
			(@Fc_ID = 0 or k.fc_ID = @Fc_ID) and
			(@Pro_ID_VahedMali = 0 or k.pro_ID_VahedMali = @Pro_ID_VahedMali) and
			(@Pro_ID_Anbar = 0 or k.Pro_ID_Anbar = @Pro_ID_Anbar) and
			(@Kal_ID = 0 or k.Kal_ID = @Kal_ID) and
			(@Tarikh = 0 or k.Tarikh <= @Tarikh)
		group by 
			k.Pro_ID_Anbar,
			k.Kal_ID
	) x
)