alter proc prcKarkardVisitor @pcode bigint,@fcId int,@orgId int,@fromDate int,@toDate int, @fromPrice decimal(19,4), @toPrice decimal(19,4),@kalId int,@proIdGroohKala int as
begin

------------------------------------------------------------------------- ويزيتوري در تک تک کالاها|خدمات بستانکار
select
	m.ID [ID],
	m.Code [Code],
	m.tarikh [Tarikh],
	case m.Dom_ID_Noe
		when 28 then-- 'ويزيتوري فاکتور فروش
			case d.Dom_ID_Noe
				when 667 then -- Khadamat
					'ويزيتوري فروش ('+ cast( m.Code  as nvarchar) + ') نام خدمات :'+(select tt.Title +'('+ cast (tt.Code  as nvarchar)+')' from tafsili tt where tt.ID = d.Taf_ID_Khadamat)
				when 666 then --Kala
					'ويزيتوري فروش ('+ cast( m.Code  as nvarchar) + ') نام کالا :' + (select kk.name +'('+ cast (kk.Code  as nvarchar)+')' from Kala kk where kk.ID = d.Kal_ID) end
				
		when 31 then --'ويزيتوري خريد 
			case d.Dom_ID_Noe
				when 666 then --Kala
					'ويزيتوري خريد ('+ cast( m.Code  as nvarchar) + ') نام کالا :' + (select kk.name +'('+ cast (kk.Code  as nvarchar)+')' from Kala kk where kk.ID = d.Kal_ID) end
	end as [Sharh],
	m.Taf_ID_Moshtari [Taf_ID_Moshtari],
	taf.Code [Taf_Code_Moshtari],
	taf.Title [Taf_Title_Moshtari],
	d.mablagh [Fi],
	d.tedad [Tedad],
	
	case m.Dom_ID_noe
		when 28 then -- Foroosh
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.Bar_ID = m.ID) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = m.ID and
				d.Dom_ID_NoeMaster = 40 and
				d.Dom_ID_vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = m.ID and
				d.Dom_ID_noeMaster = 42 and
				d.Dom_ID_vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.Bar_ID = m.ID) as decimal(19,4))
			,0.0)
		when 31 then -- Kharid
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad  - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.Bar_ID = m.ID) as decimal(19,4))
			,0.0) -
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = m.ID and
				d.Dom_ID_noeMaster = 42 and
				d.Dom_ID_vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = m.ID and
				d.Dom_ID_noeMaster = 40 and
				d.Dom_ID_vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.Bar_ID = m.ID) as decimal(19,4))
			,0.0)
	end as [MablaghFactor],
	d.MablagheTakhfif[MablaghTakhfif],
	d.DarsadeVisitor[DarsadVisitor],
	d.MablagheVisitor [MablagheVisitor],
	0.0[MablaghVisitorBedehKar],
	d.MablagheVisitor[MablaghVisitorBestanKar],
	case m.Dom_ID_noe
		when 28 then 12 --Foroosh
		when 31 then 13 -- Kharid
	end as [NoeRadif],
	m.kar_ID [Kar_ID_Creator],
	d.Kal_ID [Kal_ID],
	k.Code [Kal_Code],
	k.Name [Kal_Title],
	s.code [Taf_Code_Service],
	s.Title [Taf_Title_Service],
	creator.Title [Kar_Title_Creator],
	isnull(k.Pro_ID_Grooh,0) [Pro_ID_GroohKala]
from Bargeh m 
	join BargehDetail d on m.ID = d.Bar_ID 
	join Tafsili taf on m.Taf_ID_Moshtari = taf.ID
	inner join Karbar kar on m.Kar_ID = kar.ID
	inner join Tafsili creator on kar.Taf_ID = creator.ID
	left join Kala k on d.Kal_ID = k.ID
	left join Tafsili s on d.Taf_ID_Khadamat = s.ID
where 
	m.Dom_ID_Vaziat != 101 and 
	d.Taf_ID_Visitor is not null and
	m.Dom_ID_noe in (28,31) and -- Kharid , Foroosh
	d.Taf_ID_Visitor = @pcode and
	(@fcId = 0 or m.FC_ID = @fcId) and
	(@orgId = 0 or m.Pro_ID_VahedMali = @orgId) and
	(@fromDate = 0 or m.Tarikh >= @fromDate) and
	(@toDate = 0 or m.Tarikh <= @toDate) and
	(@fromPrice = 0 or isnull(d.MablagheVisitor,0) >= @fromPrice) and
	(@toPrice = 0 or isnull(d.MablagheVisitor,0) <= @toPrice) and
	(@kalId = 0 or isnull(d.Kal_ID,0) = @kalId) and
	(@proIdGroohKala = 0 or isnull(k.Pro_ID_Grooh,0) = @proIdGroohKala)
-----------
union all
-----------
------------------------------------------------------------------------- ويزيتوري در تک تک کالاها بدهکار
select
	m.ID ,
	m.Code ,
	m.tarikh,
	case m.Dom_ID_Noe
		when 29 then --'ويزيتوري برگشت از فروش 
			case d.Dom_ID_Noe
				when 666 then --Kala
					'ويزيتوري برگشت از فروش ('+ cast( m.Code  as nvarchar) + ') نام کالا :' + (select name +'('+ cast (Code  as nvarchar)+')' from Kala where ID = d.Kal_ID) end
		when 32 then --'ويزيتوري برگشت از خرید 
			case d.Dom_ID_Noe
				when 666 then --Kala
					'ويزيتوري برگشت از خرید  ('+ cast( m.Code  as nvarchar) + ') نام کالا :' + (select name +'('+ cast (Code  as nvarchar)+')' from Kala where ID = d.Kal_ID) end

	end ,
	m.Taf_ID_Moshtari,
	taf.Code ,
	taf.Title ,
	d.mablagh,
	d.tedad,
	
	case m.Dom_ID_noe
		when 29 then -- BargashtAzForoosh
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad  - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.Bar_ID = m.ID) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = m.ID and
				d.Dom_ID_noeMaster = 42 and
				d.Dom_ID_vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = m.ID and
				d.Dom_ID_noeMaster = 40 and
				d.Dom_ID_vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.Bar_ID = m.ID) as decimal(19,4))
			,0.0) 
		when 32 then -- BargashAzKharid
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif,0))
				from BargehDetail d
				where d.Bar_ID = m.ID) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = m.ID and
				d.Dom_ID_noeMaster = 40 and
				d.Dom_ID_vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = m.ID and
				d.Dom_ID_noeMaster = 42 and
				d.Dom_ID_vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.Bar_ID = m.ID) as decimal(19,4))
			,0.0)
	end ,
	d.MablagheTakhfif,
	d.DarsadeVisitor,
	d.MablagheVisitor ,
	d.MablagheVisitor,
	0.0,
	case m.Dom_ID_noe
		when 29 then 15 -- BargashtAzForoosh
		when 32 then 14 -- BargashtAzKharid
	end ,
	m.Kar_ID ,
	d.Kal_ID ,
	k.Code ,
	k.Name ,
	s.code,
	s.Title,
	creator.Title,
	isnull(k.Pro_ID_Grooh,0)
from Bargeh m 
	join BargehDetail d on m.ID = d.Bar_ID 
	join Tafsili taf on m.Taf_ID_Moshtari = taf.ID
	inner join Karbar kar on m.Kar_ID = kar.ID
	inner join Tafsili creator on kar.Taf_ID = creator.ID
	left join Kala k on d.Kal_ID = k.ID
	left join Tafsili s on d.Taf_ID_Khadamat = s.ID
where 
	m.Dom_ID_Vaziat != 101 and 
	d.Taf_ID_Visitor is not null and
	m.Dom_ID_Noe in (29,32) and -- BargashAzKharid , BargashAzForoosh
	d.Taf_ID_Visitor = @pcode and
	(@fcId = 0 or m.FC_ID = @fcId) and
	(@orgId = 0 or m.Pro_ID_VahedMali = @orgId) and
	(@fromDate = 0 or m.Tarikh >= @fromDate) and
	(@toDate = 0 or m.Tarikh <= @toDate) and
	(@fromPrice = 0 or isnull(d.MablagheVisitor,0) >= @fromPrice) and
	(@toPrice = 0 or isnull(d.MablagheVisitor,0) <= @toPrice) and
	(@kalId = 0 or isnull(d.Kal_ID,0) = @kalId) and
	(@proIdGroohKala = 0 or isnull(k.Pro_ID_Grooh,0) = @proIdGroohKala)
-----------
union all
-----------
select  ------------------------------------------------------------------------- ويزيتوري کل فاکتور بستانکار
	b.ID ,
	b.Code ,
	b.Tarikh,
	case b.Dom_ID_Noe
		when 28 then 'ويزيتوري نهایی فاکتور فروش (' + cast( b.Code  as nvarchar) + ')'
		when 31 then 'ويزيتوري نهایی فاکتور خرید (' + cast( b.Code  as nvarchar) + ')'
	end ,
	b.Taf_ID_Moshtari,
	taf.Code ,
	taf.Title ,
	0,
	0,
	case b.Dom_ID_noe
		when 28 then -- Foroosh
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = b.ID and
				d.Dom_ID_NoeMaster = 40 and
				d.Dom_ID_vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = b.ID and
				d.Dom_ID_NoeMaster = 42 and
				d.Dom_ID_Vaziat != 101) 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)
		when 31 then -- Kharid
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad  - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = b.ID and
				d.Dom_ID_NoeMaster = 42 and
				d.Dom_ID_Vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = b.ID and
				d.Dom_ID_NoeMaster = 40 and
				d.Dom_ID_Vaziat != 101) 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)
	end ,
	0.0,
	b.DarsadeVisitor,
	b.MablagheVisitor ,
	0.0 ,
	b.MablagheVisitor,
	case b.Dom_ID_Noe
		when 28 then 12 --Foroosh
		when 31 then 13 -- Kharid
	end ,
	b.kar_ID ,
	0,
	'',
	'',
	null,
	'',
	creator.Title,
	0
from Bargeh b
	join Tafsili taf on b.Taf_ID_Moshtari = taf.ID
	inner join Karbar kar on b.Kar_ID = kar.ID
	inner join Tafsili creator on kar.Taf_ID = creator.ID
where
	b.Dom_ID_Vaziat != 101 and 
	--b.noe not in (2,5)/*PishFactorForoosh , PishFactorKharid*/ and
	b.Taf_ID_Visitor is not null and
	b.Dom_ID_Noe in (28,31) and-- Kharid , Foroosh
	b.Taf_ID_Visitor = @pcode and
	(@fcId = 0 or b.FC_ID = @fcId) and
	(@orgId = 0 or b.Pro_ID_VahedMali = @orgId) and
	(@fromDate = 0 or b.Tarikh >= @fromDate) and
	(@toDate = 0 or b.Tarikh <= @toDate) and
	(@fromPrice = 0 or isnull(b.MablagheVisitor,0) >= @fromPrice) and
	(@toPrice = 0 or isnull(b.MablagheVisitor,0) <= @toPrice)
-----------
union all
-----------
select  ------------------------------------------------------------------------- ويزيتوري کل فاکتور بدهکار
	b.ID ,
	b.Code ,
	b.Tarikh[tarikh],
	case b.Dom_ID_Noe
		when 29 then 'ويزيتوري نهایی فاکتور برگشت از فروش (' + cast( b.Code  as nvarchar) + ')'
		when 32 then 'ويزيتوري نهایی فاکتور برگشت از خرید (' + cast( b.Code  as nvarchar) + ')'
	end ,
	b.Taf_ID_Moshtari ,
	taf.Code ,
	taf.Title ,
	0,
	0,
	case b.Dom_ID_noe
		when 29 then -- BargashtAzForoosh
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad  - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = b.ID and
				d.Dom_ID_NoeMaster = 42 and
				d.Dom_ID_Vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = b.ID and
				d.Dom_ID_NoeMaster = 40 and
				d.Dom_ID_Vaziat != 101) 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)
		when 32 then -- BargashAzKharid
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif,0))
				from BargehDetail d
				where d.Bar_ID = b.ID) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = b.ID and
				d.Dom_ID_NoeMaster = 40 and
				d.Dom_ID_Vaziat != 101) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.Bar_ID = b.ID	 and
				d.Dom_ID_NoeMaster = 42 and
				d.Dom_ID_Vaziat != 101) 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)
	end ,
	0.0,
	b.DarsadeVisitor,
	b.MablagheVisitor ,
	b.MablagheVisitor ,
	0.0 ,
	case b.Dom_ID_Noe
		when 29 then 15 -- BargashtAzForoosh
		when 32 then 14 -- BargashtAzKharid
	end ,
	b.Kar_ID ,
	0 ,
	'',
	'',
	null,
	'',
	creator.Title,
	0
from Bargeh b
	join Tafsili taf on b.Taf_ID_Moshtari = taf.ID
	inner join Karbar kar on b.Kar_ID = kar.ID
	inner join Tafsili creator on kar.Taf_ID = creator.ID
where
	b.Dom_ID_Vaziat != 101 and 
	b.Taf_ID_Visitor is not null and
	b.Dom_ID_Noe in (29,32) and -- BargashtAzKharid , BargashtAzForoosh
	b.Taf_ID_Visitor = @pcode and
	(@fcId = 0 or b.FC_ID = @fcId) and
	(@orgId = 0 or b.Pro_ID_VahedMali = @orgId) and
	(@fromDate = 0 or b.Tarikh >= @fromDate) and
	(@toDate = 0 or b.Tarikh <= @toDate) and
	(@fromPrice = 0 or isnull(b.MablagheVisitor,0) >= @fromPrice) and
	(@toPrice = 0 or isnull(b.MablagheVisitor,0) <= @toPrice)

order by Tarikh
end