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.code [Code],
	m.CodeBargeh[CodeBargeh],
	m.tarikh[Tarikh],
	case m.Noe
		when 0 then-- 'ويزيتوري فاکتور فروش
			case d.isKala
				when 0 then -- Khadamat
					'ويزيتوري فروش ('+ cast( m.CodeBargeh as nvarchar) + ') نام خدمات :'+(select name +'('+ cast (CodeHesab as nvarchar)+')' from tafsili where Code = d.CodeKhadamat2)
				when 1 then --Kala
					'ويزيتوري فروش ('+ cast( m.CodeBargeh as nvarchar) + ') نام کالا :' + (select name +'('+ cast (CodeKala as nvarchar)+')' from Kala where Code = d.codeKala) end
				
		when 3 then --'ويزيتوري خريد 
			case d.isKala
				when 1 then --Kala
					'ويزيتوري خريد ('+ cast( m.CodeBargeh as nvarchar) + ') نام کالا :' + (select name +'('+ cast (CodeKala as nvarchar)+')' from Kala where Code = d.codeKala) end
	end as [sharh],
	m.codeMoshtari[CodeMoshtari],
	d.mablagh[Fi],
	d.tedad[Tedad],
	
	case m.noe
		when 0 then -- Foroosh
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.codeBargeh = m.code) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = m.code and
				d.noeMaster = 2 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = m.code and
				d.noeMaster = 4 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.codeBargeh = m.code ) as decimal(19,4))
			,0.0)
		when 3 then -- Kharid
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad  - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.codeBargeh = m.code) as decimal(19,4))
			,0.0) -
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = m.code and
				d.noeMaster = 4 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = m.code and
				d.noeMaster = 2 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.codeBargeh = m.code ) as decimal(19,4))
			,0.0)
	end as [MablaghFactor],
	d.MablagheTakhfif[MablaghTakhfif],
	d.DarsadeVisitor[DarsadVisitor],
	0.0[MablaghVisitorBedehKar],
	d.MablagheVisitor[MablaghVisitorBestanKar],
	case m.noe
		when 0 then 12 --Foroosh
		when 3 then 13 -- Kharid
	end as [NoeRadif],
	m.CodeKarbar[CodeKarbar],
	m.Vaziat[Vaziat],
	d.codeKala[CodeKala],
	k.Codegrooh[CodeGroohKala]
from 
	(Bargeh m join BargehDetail d on m.code = d.CodeBargeh  join Kala k on d.codeKala = k.Code)
where 
	m.Vaziat != 1 and 
	m.noe not in (2,5)/*PishFactorForoosh , PishFactorKharid*/ and
	d.codeVisitor is not null and
	m.noe in (0,3) and -- Kharid , Foroosh
	d.codeVisitor = @pcode
-----------
union all
-----------
------------------------------------------------------------------------- ويزيتوري در تک تک کالاها بدهکار
select
	0[Radif],
	m.code [Code],
	m.CodeBargeh[CodeBargeh],
	m.tarikh[Tarikh],
	case m.Noe
		when 1 then --'ويزيتوري برگشت از فروش 
			case d.isKala
				when 1 then --Kala
					'ويزيتوري برگشت از فروش ('+ cast( m.CodeBargeh as nvarchar) + ') نام کالا :' + (select name +'('+ cast (CodeKala as nvarchar)+')' from Kala where Code = d.codeKala) end
		when 4 then --'ويزيتوري برگشت از خرید 
			case d.isKala
				when 1 then --Kala
					'ويزيتوري برگشت از خرید  ('+ cast( m.CodeBargeh as nvarchar) + ') نام کالا :' + (select name +'('+ cast (CodeKala as nvarchar)+')' from Kala where Code = d.codeKala) end

	end as [sharh],
	m.codeMoshtari[CodeMoshtari],
	d.mablagh[Fi],
	d.tedad[Tedad],
	
	case m.noe
		when 1 then -- BargashtAzForoosh
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad  - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.codeBargeh = m.code) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = m.code and
				d.noeMaster = 4 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = m.code and
				d.noeMaster = 2 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.codeBargeh = m.code ) as decimal(19,4))
			,0.0) 
		when 4 then -- BargashAzKharid
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif,0))
				from BargehDetail d
				where d.codeBargeh = m.code) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = m.code and
				d.noeMaster = 2 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = m.code and
				d.noeMaster = 4 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.codeBargeh = m.code ) as decimal(19,4))
			,0.0)
	end as [MablaghFactor],
	d.MablagheTakhfif[MablaghTakhfif],
	d.DarsadeVisitor[DarsadVisitor],
	d.MablagheVisitor[MablaghVisitorBedehKar],
	0.0[MablaghVisitorBestanKar],
	case m.noe
		when 1 then 15 -- BargashtAzForoosh
		when 4 then 14 -- BargashtAzKharid
	end as [NoeRadif],
	m.CodeKarbar[CodeKarbar],
	m.Vaziat[Vaziat],
	d.codeKala[CodeKala],
	k.Codegrooh[CodeGroohKala]
from 
	(Bargeh m join BargehDetail d on m.code = d.CodeBargeh join Kala k on d.codeKala = k.Code)
where 
	m.Vaziat != 1 and 
	m.noe not in (2,5)/*PishFactorForoosh , PishFactorKharid*/ and
	d.codeVisitor is not null and
	m.noe in (1,4) and -- BargashAzKharid , BargashAzForoosh
	d.codeVisitor = @pcode
-----------
union all
-----------
select  ------------------------------------------------------------------------- ويزيتوري کل فاکتور بستانکار
	0[Radif],
	b.code [Code],
	b.CodeBargeh[CodeBargeh],
	b.Tarikh[tarikh],
	case b.Noe
		when 0 then 'ويزيتوري نهایی فاکتور فروش (' + cast( b.CodeBargeh as nvarchar) + ')'
		when 3 then 'ويزيتوري نهایی فاکتور خرید (' + cast( b.CodeBargeh as nvarchar) + ')'
	end as[Sharh],
	b.codeMoshtari[CodeMoshtari],
	0[Fi],
	0[Tedad],
	case b.noe
		when 0 then -- Foroosh
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.codeBargeh = b.code) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = b.code and
				d.noeMaster = 2 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = b.code and
				d.noeMaster = 4 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.codeBargeh = b.code ) as decimal(19,4))
			,0.0)
		when 3 then -- Kharid
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad  - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.codeBargeh = b.code) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = b.code and
				d.noeMaster = 4 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = b.code and
				d.noeMaster = 2 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.codeBargeh = b.code ) as decimal(19,4))
			,0.0)
	end as [MablaghFactor],
	0.0[MablaghTakhfif],
	b.DarsadeVisitor[DarsadVisitor],
	0.0 [MablaghVisitorBedehKar],
	b.MablagheVisitor[MablaghVisitorBestanKar],
	case b.noe
		when 0 then 12 --Foroosh
		when 3 then 13 -- Kharid
	end as [NoeRadif],
	b.CodeKarbar[CodeKarbar],
	b.Vaziat[Vaziat],
	0 [CodeKala],
	0[CodeGroohKala]
from Bargeh b
where
	b.Vaziat != 1 and 
	b.noe not in (2,5)/*PishFactorForoosh , PishFactorKharid*/ and
	b.codeVisitor is not null and
	b.noe in (0,3) and-- Kharid , Foroosh
	b.codeVisitor = @pcode
-----------
union all
-----------
select  ------------------------------------------------------------------------- ويزيتوري کل فاکتور بدهکار
	0[Radif],
	b.code [Code],
	b.CodeBargeh[CodeBargeh],
	b.Tarikh[tarikh],
	case b.Noe
		when 1 then 'ويزيتوري نهایی فاکتور برگشت از فروش (' + cast( b.CodeBargeh as nvarchar) + ')'
		when 4 then 'ويزيتوري نهایی فاکتور برگشت از خرید (' + cast( b.CodeBargeh as nvarchar) + ')'
	end as[Sharh],
	b.codeMoshtari[CodeMoshtari],
	0[Fi],
	0[Tedad],
	case b.noe
		when 1 then -- BargashtAzForoosh
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad  - isnull(d.MablagheTakhfif , 0))
				from BargehDetail d
				where d.codeBargeh = b.code) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = b.code and
				d.noeMaster = 4 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = b.code and
				d.noeMaster = 2 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.codeBargeh = b.code ) as decimal(19,4))
			,0.0)
		when 4 then -- BargashAzKharid
			isnull
			(
				cast((select sum(d.mablagh * d.Meghdar1 * d.Meghdar2 * d.tedad - isnull(d.MablagheTakhfif,0))
				from BargehDetail d
				where d.codeBargeh = b.code) as decimal(19,4))
			,0.0) - 
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = b.code and
				d.noeMaster = 2 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.mablagh)
				from DaryaftPardakht d
				where d.codeBargehParent = b.code and
				d.noeMaster = 4 and
				d.vaziat != 1) as decimal(19,4))
			,0.0) +
			isnull
			(
				cast((select sum(d.MablaghArzeshAfzoodeh)
				from BargehDetail d
				where d.codeBargeh = b.code ) as decimal(19,4))
			,0.0)
	end as [MablaghFactor],
	0.0[MablaghTakhfif],
	b.DarsadeVisitor[DarsadVisitor],
	b.MablagheVisitor [MablaghVisitorBedehKar],
	0.0 [MablaghVisitorBestanKar],
	case b.noe
		when 1 then 15 -- BargashtAzForoosh
		when 4 then 14 -- BargashtAzKharid
	end as [NoeRadif],
	b.CodeKarbar[CodeKarbar],
	b.Vaziat[Vaziat],
	0 [CodeKala],
	0[CodeGroohKala]
from Bargeh b
where
	b.Vaziat != 1 and 
	b.noe not in (2,5)/*PishFactorForoosh , PishFactorKharid*/ and
	b.codeVisitor is not null and
	b.noe in (1,4) and -- BargashtAzKharid , BargashtAzForoosh
	b.codeVisitor = @pcode

order by Tarikh
end