IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='prcKarkardVisitor')
	DROP proc prcKarkardVisitor
GO


CREATE proc prcKarkardVisitor @pcode bigint as
begin

------------------------------------------------------------------------- ويزيتوري در تک تک کالاها بستانکار
select
	0[Radif],
	m.ID [ID],
	m.Code [CodeBargeh],
	m.tarikh[Tarikh],
	case m.Dom_ID_Noe
		when 28 then-- 'ويزيتوري فاکتور فروش
			case d.isKala
				when 0 then -- Khadamat
					'ويزيتوري فروش ('+ cast( m.Code  as nvarchar) + ') نام خدمات :'+(select name +'('+ cast (Code  as nvarchar)+')' from tafsili where ID = d.Taf_ID_Khadamat)
				when 1 then --Kala
					'ويزيتوري فروش ('+ cast( m.Code  as nvarchar) + ') نام کالا :' + (select name +'('+ cast (Code  as nvarchar)+')' from Kala where ID = d.Kal_ID) end
				
		when 31 then --'ويزيتوري خريد 
			case d.isKala
				when 1 then --Kala
					'ويزيتوري خريد ('+ cast( m.Code  as nvarchar) + ') نام کالا :' + (select name +'('+ cast (Code  as nvarchar)+')' from Kala where ID = d.Kal_ID) end
	end as [sharh],
	m.Taf_ID_Moshtari[Taf_ID_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],
	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] ,
	m.Dom_ID_Vaziat[Vaziat],
	d.Kal_ID [Kal_ID],
	k.Pro_ID_Grooh [Pro_ID_GroohKala]
from 
	(Bargeh m join BargehDetail d on m.ID = d.Bar_ID join Kala k on d.Kal_ID = k.ID)
where 
	m.Dom_ID_Vaziat != 101 and 
	--m.noe not in (30,33)/*PishFactorForoosh , PishFactorKharid*/ and
	d.Taf_ID_Visitor is not null and
	m.Dom_ID_noe in (28,31) and -- Kharid , Foroosh
	d.Taf_ID_Visitor = @pcode
-----------
union all
-----------
------------------------------------------------------------------------- ويزيتوري در تک تک کالاها بدهکار
select
	0[Radif],
	m.ID [ID],
	m.Code [CodeBargeh],
	m.tarikh[Tarikh],
	case m.Dom_ID_Noe
		when 29 then --'ويزيتوري برگشت از فروش 
			case d.isKala
				when 1 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.isKala
				when 1 then --Kala
					'ويزيتوري برگشت از خرید  ('+ cast( m.Code  as nvarchar) + ') نام کالا :' + (select name +'('+ cast (Code  as nvarchar)+')' from Kala where ID = d.Kal_ID) end

	end as [sharh],
	m.Taf_ID_Moshtari[Taf_ID_Moshtari],
	d.mablagh[Fi],
	d.tedad[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 as [MablaghFactor],
	d.MablagheTakhfif[MablaghTakhfif],
	d.DarsadeVisitor[DarsadVisitor],
	d.MablagheVisitor[MablaghVisitorBedehKar],
	0.0[MablaghVisitorBestanKar],
	case m.Dom_ID_noe
		when 29 then 15 -- BargashtAzForoosh
		when 32 then 14 -- BargashtAzKharid
	end as [NoeRadif],
	m.Kar_ID [Kar_ID],
	m.Dom_ID_Vaziat[Vaziat],
	d.Kal_ID [Kal_ID],
	k.Pro_ID_Grooh [Pro_ID_GroohKala]
from 
	(Bargeh m join BargehDetail d on m.ID = d.Bar_ID join Kala k on d.Kal_ID = k.ID)
where 
	m.Dom_ID_Vaziat != 101 and 
	--m.noe not in (2,5)/*PishFactorForoosh , PishFactorKharid*/ and
	d.Taf_ID_Visitor is not null and
	m.Dom_ID_Noe in (29,32) and -- BargashAzKharid , BargashAzForoosh
	d.Taf_ID_Visitor = @pcode
-----------
union all
-----------
select  ------------------------------------------------------------------------- ويزيتوري کل فاکتور بستانکار
	0[Radif],
	b.ID [ID],
	b.Code [CodeBargeh],
	b.Tarikh[tarikh],
	case b.Dom_ID_Noe
		when 28 then 'ويزيتوري نهایی فاکتور فروش (' + cast( b.Code  as nvarchar) + ')'
		when 31 then 'ويزيتوري نهایی فاکتور خرید (' + cast( b.Code  as nvarchar) + ')'
	end as[Sharh],
	b.Taf_ID_Moshtari[Taf_ID_Moshtari],
	0[Fi],
	0[Tedad],
	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 as [MablaghFactor],
	0.0[MablaghTakhfif],
	b.DarsadeVisitor[DarsadVisitor],
	0.0 [MablaghVisitorBedehKar],
	b.MablagheVisitor[MablaghVisitorBestanKar],
	case b.Dom_ID_Noe
		when 28 then 12 --Foroosh
		when 31 then 13 -- Kharid
	end as [NoeRadif],
	b.kar_ID [Kar_ID] ,
	b.Dom_ID_Vaziat [Vaziat],
	0 [Kal_ID],
	0[Pro_ID_GroohKala]
from Bargeh b
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
-----------
union all
-----------
select  ------------------------------------------------------------------------- ويزيتوري کل فاکتور بدهکار
	0[Radif],
	b.ID [ID],
	b.Code [CodeBargeh],
	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 as[Sharh],
	b.Taf_ID_Moshtari [Taf_ID_Moshtari],
	0[Fi],
	0[Tedad],
	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 as [MablaghFactor],
	0.0[MablaghTakhfif],
	b.DarsadeVisitor[DarsadVisitor],
	b.MablagheVisitor [MablaghVisitorBedehKar],
	0.0 [MablaghVisitorBestanKar],
	case b.Dom_ID_Noe
		when 29 then 15 -- BargashtAzForoosh
		when 32 then 14 -- BargashtAzKharid
	end as [NoeRadif],
	b.Kar_ID [Kar_ID],
	b.Dom_ID_Vaziat [Vaziat],
	0 [Kal_ID],
	0[Pro_ID_GroohKala]
from Bargeh b
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 (29,32) and -- BargashtAzKharid , BargashtAzForoosh
	b.Taf_ID_Visitor = @pcode

order by Tarikh
end