﻿alter function dbo.fAllCodeBedBesGold(@fcId int ,@orgId int ,@fromDate int=0, @toDate int=0, @strTafId nvarchar(max), @strKalId nvarchar(max), @strDomId nvarchar(max),@strKalIdExpect nvarchar(max)) returns table
return(
	---------------------------------------------------------------------------------- سند دستی - قدیم
	--select
	--	bar.Taf_ID_Moshtari Taf_ID,
	--	isnull(bg.Kal_ID,0) Kal_ID,
	--	sum(case when bg.Dom_ID_Nature = 799 /*بدهکار*/ then bg.Price end) Debit,
	--	sum(case when bg.Dom_ID_Nature = 800 /*بستانکار*/ then bg.Price end) Credit,
	--	sum(case when bg.Dom_ID_Nature = 799 /*بدهکار*/ then isnull(bg.Quantity,0) end) DebitQuantity,
	--	sum(case when bg.Dom_ID_Nature = 800 /*بستانکار*/ then isnull(bg.Quantity,0) end) CreditQuantity,
	--	sum(case when bg.Dom_ID_Nature = 799 /*بدهکار*/ then bg.QuantityBase end) DebitGold,
	--	sum(case when bg.Dom_ID_Nature = 800 /*بستانکار*/ then bg.QuantityBase end) CreditGold
	--from Bargeh bar
	--	inner join BargehGold bg on bar.ID = bg.Bar_ID 
	--		and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
	--		and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
	--		and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
	--		and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	--where
	--	bar.Dom_ID_Vaziat = 100 and
	--	bar.Dom_ID_Noe = 2233 and
	--	bg.Dom_ID_RowType = 2250/*سند دستي*/ and
 --       (@fromDate = 0 or bar.Tarikh >= @fromDate) and
 --       (@toDate = 0 or bar.Tarikh <= @toDate) 
	--group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- اول دوره مبلغی
	--union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		0 Kal_ID,
		sum(case when bg.Dom_ID_Nature = 799 /*بدهکار*/ then bg.Price end) Debit,
		sum(case when bg.Dom_ID_Nature = 800 /*بستانکار*/ then bg.Price end) Credit,
		0 DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID 
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType = 2281/*اول دوره (مبلغ)*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari
	---------------------------------------------------------------------------------- اول دوره وزنی
	union all
		select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		0 Debit,
		0 Credit,
		sum(case when bg.Dom_ID_Nature = 799 /*بدهکار*/ then isnull(bg.Quantity,0) end) DebitQuantity,
		sum(case when bg.Dom_ID_Nature = 800 /*بستانکار*/ then isnull(bg.Quantity,0) end) CreditQuantity,
		sum(case when bg.Dom_ID_Nature = 799 /*بدهکار*/ then bg.QuantityBase end) DebitGold,
		sum(case when bg.Dom_ID_Nature = 800 /*بستانکار*/ then bg.QuantityBase end) CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID 
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType = 2282/*اول دوره (وزن)*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- فروش - پولی
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		sum(Quantity * Price)+sum(isnull(PriceGap,0)) Debit,
		0 Credit,
		0 DebitQuantity,
		sum(isnull(bg.Quantity,0)) CreditQuantity,
		0 DebitGold,
		sum(QuantityBase) CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2237,2241) /*فروش آبشده پولی - فروش سکه پولی*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- خرید - پولی
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		0 Debit,
		sum(Quantity * Price)+sum(isnull(PriceGap,0)) Credit,

		sum(isnull(bg.Quantity,0)) DebitQuantity,
		0 CreditQuantity,

		sum(QuantityBase) DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2239,2242) /*خرید سکه پولی - خرید آبشده پولی*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- فروش - فیزیکی
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		sum((isnull(bg.Quantity,0) * isnull(bg.Price,0)) + isnull(bg.PriceGap,0)) Debit,
		0 Credit,
		0 DebitQuantity,
		sum(isnull(bg.Quantity,0)) CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2289,2287) /*فروش آبشده - فروش سکه*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- خرید - فیزیکی
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		0 Debit,
		sum((isnull(bg.Quantity,0) * isnull(bg.Price,0)) + isnull(bg.PriceGap,0)) Credit,
		sum(isnull(bg.Quantity,0)) DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2288,2286) /*خرید سکه - خرید آبشده*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- دریافت آبشده و سکه
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		0 Debit,
		0 Credit,
		0 DebitQuantity,
		sum(isnull(bg.Quantity,0)) CreditQuantity,
		0 DebitGold,
		sum(isnull(bg.QuantityBase,0)) CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2290,2294) /*دريافت طلا - دريافت سکه*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- پرداخت آبشده و سکه
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		0 Debit,
		0 Credit,
		sum(isnull(bg.Quantity,0)) DebitQuantity,
		0 CreditQuantity,
		sum(QuantityBase) DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2291,2295) /*پرداخت طلا - پرداخت سکه*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- دریافت/پرداخت حواله وزنی/سکه
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		0 Debit,
		0 Credit,
		sum(isnull((case when bg.Dom_ID_RowType = 2335 and k.Dom_ID_RowType = 2226 /*سکه*/ then bg.Quantity else 0 end),0)) DebitQuantity,
		sum(isnull((case when bg.Dom_ID_RowType = 2334 and k.Dom_ID_RowType = 2226 /*سکه*/ then bg.Quantity else 0 end),0)) CreditQuantity,
		sum(isnull((case when bg.Dom_ID_RowType in (2307,2335) then bg.QuantityBase else 0 end),0)) DebitGold,
		sum(isnull((case when bg.Dom_ID_RowType in (2306,2334) then bg.QuantityBase else 0 end),0)) CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
		inner join Kala k on bg.Kal_ID = k.ID
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2306,2307,2334,2335) /*دریافت حواله وزنی*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- دریافت/پرداخت حواله وزنی/سکه - عطف
	union all
	select
		bg.Taf_ID Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		0 Debit,
		0 Credit,
		sum(isnull((case when bg.Dom_ID_RowType = 2334 and k.Dom_ID_RowType = 2226 /*سکه*/ then bg.Quantity else 0 end),0)) DebitQuantity,
		sum(isnull((case when bg.Dom_ID_RowType = 2335 and k.Dom_ID_RowType = 2226 /*سکه*/ then bg.Quantity else 0 end),0)) CreditQuantity,
		sum(isnull((case when bg.Dom_ID_RowType in (2306,2334) then bg.QuantityBase else 0 end),0)) DebitGold,
		sum(isnull((case when bg.Dom_ID_RowType in (2307,2335) then bg.QuantityBase else 0 end),0)) CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bg.Taf_ID in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
		inner join Kala k on bg.Kal_ID = k.ID
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2306,2307,2334,2335) /*دریافت حواله وزنی*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bg.Taf_ID,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- دریافت نقد، حواله بانکی
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		0 Kal_ID,
		0 Debit,
		sum(isnull(bg.Price,0)) Credit,
		0 DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2292,2299,2296) /*دريافت نقد - دريافت چک - دريافت حواله بانکي*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari
	---------------------------------------------------------------------------------- دریافت نقد، حواله بانکی، عطف
	union all
	select
		bg.Taf_ID Taf_ID,
		0 Kal_ID,
		sum(isnull(bg.Price,0)) Debit,
		0 Credit,
		0 DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bg.Taf_ID in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2292,2299,2296) /*دريافت نقد - دريافت چک - دريافت حواله بانکي*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bg.Taf_ID
	---------------------------------------------------------------------------------- پرداخت نقد، حواله بانکی
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		0 Kal_ID,
		sum(isnull(bg.Price,0)) Debit,
		0 Credit,	
		0 DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2293,2297,2300) /*پرداخت نقد - پرداخت چک - پرداخت حواله بانکي*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari
	---------------------------------------------------------------------------------- پرداخت نقد، حواله بانکی، عطف
	union all
	select
		bg.Taf_ID Taf_ID,
		0 Kal_ID,
		0 Debit,
		sum(isnull(bg.Price,0)) Credit,
		0 DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bg.Taf_ID in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2293,2297,2300) /*پرداخت نقد - پرداخت چک - پرداخت حواله بانکي*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bg.Taf_ID
	---------------------------------------------------------------------------------- خرید/فروش ساخته
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		case when bg.Dom_ID_RowType=2320 then sum(Quantity * Price) + sum(isnull(bg.PriceMade,0)) + sum(isnull(bg.PriceWage,0)) else 0 end Debit,
		case when bg.Dom_ID_RowType=2319 then sum(Quantity * Price) + sum(isnull(bg.PriceMade,0)) + sum(isnull(bg.PriceWage,0)) else 0 end Credit,

		0 DebitQuantity,
		0 CreditQuantity,

		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2319,2320) /*خرید ساخته - فروش ساخته*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0),bg.Dom_ID_RowType
	
	---------------------------------------------------------------------------------- دریافت حواله عادی ریالی
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		0 Debit,
		sum(isnull(bg.Price,0)) Credit,
		0 DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
		--inner join Kala k on bg.Kal_ID = k.ID
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2332) /*دریافت حواله عادی ریالی*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- دریافت حواله عادی ریالی، عطف
	union all
	select
		bg.Taf_ID Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		sum(isnull(bg.Price,0)) Debit,
		0 Credit,
		0 DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bg.Taf_ID in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
		--inner join Kala k on bg.Kal_ID = k.ID
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2332) /*دریافت حواله عادی ریالی*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bg.Taf_ID,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- پرداخت حواله عادی ریالی
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		sum(isnull(bg.Price,0)) Debit,
		0 Credit,
		0 DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
		--inner join Kala k on bg.Kal_ID = k.ID
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2333) /*پرداخت حواله عادی ریالی*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- پرداخت حواله عادی ریالی، عطف
	union all
	select
		bg.Taf_ID Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		0 Debit,
		sum(isnull(bg.Price,0)) Credit,
		0 DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bg.Taf_ID in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
		--inner join Kala k on bg.Kal_ID = k.ID
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2333) /*پرداخت حواله عادی ریالی*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bg.Taf_ID,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- دريافت ساخته
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		sum(case when isnull(bg.PriceWage,0)> 0 then (case bg.Dom_ID_Detail when 2375 then bg.PriceWage* bg.Quantity when 2376 then bg.PriceWage* bg.QuantityBase when 2377 then bg.PriceWage* bg.Cnt else 0 end) else 0 end) Debit,
		0 Credit,
		0 DebitQuantity,
		0 CreditQuantity,
		0 DebitGold,
		sum(isnull(bg.QuantityBase,0)) + sum(isnull((case when isnull(bg.PercentMade,0) > 0 then (bg.PercentMade*bg.Quantity /*از وزن اولیه باید بگیرد*//100) else 0 end),0)) CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bg.Taf_ID in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
		--inner join Kala k on bg.Kal_ID = k.ID
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2321) /*دريافت ساخته*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- پرداخت ساخته
	union all
	select
		bar.Taf_ID_Moshtari Taf_ID,
		isnull(bg.Kal_ID,0) Kal_ID,
		0 Debit,
		sum(case when isnull(bg.PriceWage,0)> 0 then (case bg.Dom_ID_Detail when 2375 then bg.PriceWage* bg.Quantity when 2376 then bg.PriceWage* bg.QuantityBase when 2377 then bg.PriceWage* bg.Cnt else 0 end) else 0 end) Credit,
		0 DebitQuantity,
		0 CreditQuantity,
		sum(isnull(bg.QuantityBase,0)) + sum(isnull((case when isnull(bg.PercentMade,0) > 0 then (bg.PercentMade*bg.Quantity /*از وزن اولیه باید بگیرد*//100) else 0 end),0)) DebitGold,
		0 CreditGold
	from Bargeh bar
		inner join BargehGold bg on bar.ID = bg.Bar_ID
			and (@strTafId ='' or bg.Taf_ID in (select * from dbo.CastStringToTable(@strTafId))) 
			and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
			and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
			and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
		--inner join Kala k on bg.Kal_ID = k.ID
	where
		bar.Dom_ID_Vaziat = 100 and
		bar.Dom_ID_Noe = 2233 and
		bg.Dom_ID_RowType in (2322) /*پرداخت ساخته*/ and
        (@fromDate = 0 or bar.Tarikh >= @fromDate) and
        (@toDate = 0 or bar.Tarikh <= @toDate) 
	group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	---------------------------------------------------------------------------------- قدیم
	--union all
	--select
	--	bar.Taf_ID_Moshtari Taf_ID,
	--	isnull(bg.Kal_ID,0) Kal_ID,
	--	sum(Price) Debit,
	--	0 Credit,
	--	0 DebitQuantity,
	--	0 CreditQuantity,
	--	0 DebitGold,
	--	0 CreditGold
	--from Bargeh bar
	--	inner join BargehGold bg on bar.ID = bg.Bar_ID
	--		and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
	--		and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
	--		and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
	--		and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	--where
	--	bar.Dom_ID_Vaziat = 100 and
	--	bar.Dom_ID_Noe = 2233 and
	--	bg.Dom_ID_RowType in (2244) /*پرداخت حواله بانکی*/ and
 --       (@fromDate = 0 or bar.Tarikh >= @fromDate) and
 --       (@toDate = 0 or bar.Tarikh <= @toDate) 
	--group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	--union all
	--select
	--	bar.Taf_ID_Moshtari Taf_ID,
	--	isnull(bg.Kal_ID,0) Kal_ID,
	--	0 Debit,
	--	sum(Price) Credit,
	--	0 DebitQuantity,
	--	0 CreditQuantity,
	--	0 DebitGold,
	--	0 CreditGold
	--from Bargeh bar
	--	inner join BargehGold bg on bar.ID = bg.Bar_ID
	--		and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
	--		and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
	--		and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
	--		and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	--where
	--	bar.Dom_ID_Vaziat = 100 and
	--	bar.Dom_ID_Noe = 2233 and
	--	bg.Dom_ID_RowType in (2243) /*فیش واریزی*/ and
 --       (@fromDate = 0 or bar.Tarikh >= @fromDate) and
 --       (@toDate = 0 or bar.Tarikh <= @toDate) 
	--group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	--union all
	-------------------------------------------------------------حواله انتقال - بستانکار - قدیم
	--select
	--	bar.Taf_ID_Moshtari Taf_ID,
	--	isnull(bg.Kal_ID,0) Kal_ID,	
	--	sum(Price) Debit,
	--	0 Credit,
	--	sum(isnull(Quantity,0)) DebitQuantity,
	--	0 CreditQuantity,
	--	0 DebitGold,
	--	0 CreditGold
	--from Bargeh bar
	--	inner join BargehGold bg on bar.ID = bg.Bar_ID
	--		and (@strTafId ='' or bar.Taf_ID_Moshtari in (select * from dbo.CastStringToTable(@strTafId))) 
	--		and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
	--		and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
	--		and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	--where
	--	bar.Dom_ID_Vaziat = 100 and
	--	bar.Dom_ID_Noe = 2233 and
	--	bg.Dom_ID_RowType in (2245) /*حواله انتقال*/ and
 --       (@fromDate = 0 or bar.Tarikh >= @fromDate) and
 --       (@toDate = 0 or bar.Tarikh <= @toDate) 
	--group by bar.Taf_ID_Moshtari,isnull(bg.Kal_ID,0)
	--union all
	---------------------------------------------------------------حواله انتقال - بدهکار - قدیم
	--select
	--	bg.Taf_ID Taf_ID,
	--	isnull(bg.Kal_ID,0) Kal_ID,
	--	0 Debit,
	--	sum(Price) Credit,
	--	0 DebitQuantity,
	--	sum(isnull(Quantity,0)) CreditQuantity,
	--	0 DebitGold,
	--	0 CreditGold
	--from Bargeh bar
	--	inner join BargehGold bg on bar.ID = bg.Bar_ID
	--		and (@strTafId ='' or bg.Taf_ID in (select * from dbo.CastStringToTable(@strTafId))) 
	--		and (@strKalId ='' or isnull(bg.Kal_ID,0) in (select * from dbo.CastStringToTable(@strKalId)))
	--		and (@strDomId ='' or isnull(bg.Dom_ID_RowType,0) in (select * from dbo.CastStringToTable(@strDomId)))
	--		and (@strKalIdExpect ='' or isnull(bg.Kal_ID,0) not in (select * from dbo.CastStringToTable(@strKalIdExpect)))
	--where
	--	bar.Dom_ID_Vaziat = 100 and
	--	bar.Dom_ID_Noe = 2233 and
	--	bg.Dom_ID_RowType in (2245) /*حواله انتقال*/ and
 --       (@fromDate = 0 or bar.Tarikh >= @fromDate) and
 --       (@toDate = 0 or bar.Tarikh <= @toDate) 
	--group by bg.Taf_ID,isnull(bg.Kal_ID,0)

);