IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='prcKartexAnbar')
	DROP proc prcKartexAnbar
GO
-- prcKartexAnbar 0,0,0,0,'0','0',0,0,0,0,0,0,0,0,0

create proc prcKartexAnbar
	@TarikhAz int=0,
	@TarikhEla int=0,
	@GroohKalaAz int=0,
	@GroohKalaEla int=0,
	@codeKalaAz nvarchar(20)='0',
	@codeKalaEla nvarchar(20)='0',
	@GroohShakhsAz int=0,
	@GroohShakhsEla int=0,
	@TafsiliAz bigint=0,
	@TafsiliEla bigint=0,
	@AnbarAz int=0,
	@AnbarEla int=0,
	@WithPishFactorForoosh bit=0, -- 0=false , 1=True
	@WithDeleteFactors bit=0, -- 0=false , 1=True
	@CodeProjeh bigint
as
begin

----------------------------------- موجودی اول دوره
select
	0[Radif],
	0[Code],
	0[CodeBargeh],
	0[Tarikh],
	0[Saat],
	cast( 213 as smallint)[Noe],
	cast( 0 as bigint)[CodeTafsili],
	0[CodeHesabTafsili],
	0 [CodeGoroohAshkhas],
	'--'[NameTafsili],
	'موجودی اول دوره'[Sharh],
	0[CodeKarbar],
	cast(100 as smallint) [Vaziat],
	d.CodeAnbar[CodeAnbar],
	d.codeKala[CodeKala],
	k.CodeKala[CodeKalaNamayeshi],
	k.CodeGrooh[CodeGorooh],
	k.name[NameKala],
	d.MojoodiAvalDoreh[Tedad],
	d.GheymatAvalDoreh[Mablagh],
	0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش

	0[SaatSabt],

	d.MojoodiAvalDoreh[MeghdarVaredeh],
	0.0[MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.mojoodiAvalDoreh,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh],

	d.GheymatAvalDoreh[MablaghVaredeh],
	0.0[MablaghSadereh],
	cast(d.MojoodiAvalDoreh * d.GheymatAvalDoreh as decimal(19,4) )[MablaghJam],
	0.0[MablaghMiangin],
	0.0[MablaghMandeh],

	0.0[MablaghJamVaredeh],
	0.0[MablaghJamSadereh],
	0 [CodeProjeh]
from
	KalaDarAnbar d join Kala k on d.CodeKala = k.Code
where
	(cast(@codeKalaAz as int)='0' or k.codeKala>=cast(@codeKalaAz as int)) and
	(cast(@codeKalaEla as int)='0' or k.codeKala<=cast(@codeKalaEla as int)) and
	(@GroohKalaAz=0 or k.CodeGrooh>=@GroohKalaAz) and
	(@GroohKalaEla=0 or k.CodeGrooh<=@GroohKalaEla) and
	(@AnbarAz=0 or d.CodeAnbar>=@AnbarAz) and
	(@AnbarEla=0 or d.CodeAnbar<=@AnbarEla)

union all
------------------------------------------------------------ برگه
select
    0[Radif],
    m.code[Code],
  	m.codeBargeh[CodeBargeh],
    m.Tarikh[Tarikh],
    m.Saat [Saat] ,
	case m.noe
            when 28 then cast( 223 as smallint) -- Foroosh
            when 29 then cast( 215 as smallint) -- BargashtAzForoosh
            when 31 then cast( 214 as smallint) -- Kharid
            when 32 then cast( 224 as smallint) -- BargashtAzKharid
            when 30 then cast( 226 as smallint) -- PishFactororoosh
	end [Noe],
        m.CodeMoshtari[CodeTafsili],
    	t.codeHesab[CodeHesabTafsili],
    	isnull(t.CodeGroohAshkas,0)[CodeGoroohAshkhas],
	t.name[NameTafsili],
        case m.noe
            when 28 then 'فاکتور فروش (' + cast ( m.CodeBargeh as nvarchar ) + ')'-- Foroosh
            when 29 then 'فاکتور برگشت از فروش (' + cast ( m.CodeBargeh as nvarchar )  + ')' -- BargashtAzForoosh
            when 31 then 'فاکتور خرید (' + cast ( m.CodeBargeh as nvarchar )  + ')' -- Kharid
            when 32 then 'فاکتور برگشت از خرید (' + cast ( m.CodeBargeh as nvarchar )  + ')' -- BargashtAzKharid
            when 30 then 'پیش فاکتور فروش (' + cast ( m.CodeBargeh as nvarchar )  + ')' -- PishFactorForoosh
	end [Sharh],
    m.codeKarbar[CodeKarbar],
    m.vaziat[Vaziat],
    d.codeAnbar[CodeAnbar],
    d.codeKala[CodeKala],
	k.CodeKala[CodeKalaNamayeshi],
	k.CodeGrooh[CodeGorooh],
	k.name[NameKala],
    d.Tedad[Tedad],
    d.mablagh[Mablagh],
	d.GheimatBarghashtiBeAnbar[MablaghBargashtBeAnbar],

	m.SaatSabt[SaatSabt],

	case m.noe
            when 28 then 0.0 -- Foroosh
            when 29 then d.tedad -- BargashtAzForoosh
            when 31 then d.tedad -- Kharid
            when 32 then 0.0 -- BargashtAzKharid
            when 30 then 0.0 -- PishFactorForoosh
	end [MeghdarVaredeh],
	case m.noe
            when 28 then cast(d.tedad as decimal(19,4)) -- Foroosh
            when 29 then 0.0 -- BargashtAzForoosh
            when 31 then 0.0 -- Kharid
            when 32 then cast(d.tedad as decimal(19,4)) -- BargashtAzKharid
            when 30 then cast(d.tedad as decimal(19,4)) -- PishFactorForoosh
	end [MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh],
	case m.noe
            when 28 then 0.0 -- Foroosh
            when 29 then d.GheimatBarghashtiBeAnbar -- BargashtAzForoosh
            when 31 then d.mablagh -- Kharid
            when 32 then 0.0 -- BargashtAzKharid
            when 30 then 0.0 -- PishFactorForoosh
	end [MablaghVaredeh],
	0.0[MablaghSadereh],
	case m.noe
             when 28 then 0.0-- Foroosh
             when 29 then cast(d.tedad * d.GheimatBarghashtiBeAnbar as decimal(19,4)) -- BargashtAzForoosh
             when 31 then cast(d.tedad * d.mablagh as decimal(19,4)) -- Kharid
             when 32 then 0.0 -- BargashtAzKharid
             when 30 then 0.0 -- PishFactorForoosh
	end [MablaghJam],
	0.0[MablaghMiangin],
	0.0[MablaghMandeh],

	0.0[MablaghJamVaredeh],
	0.0[MablaghJamSadereh],
	ISNULL(m.CodeProjeh,0) [CodeProjeh]
from Bargeh m 
	join BargehDetail d on	m.code= d.codeBargeh 
	join Kala k on d.CodeKala = k.Code
	join tafsili t on m.codeMoshtari=t.code
where
	d.isKala = 1 and /*m.noe != 2 and*/ m.noe != 33 /*PishFactorKharid*/ and
	(cast(@codeKalaAz as int)='0' or k.codeKala>=cast(@codeKalaAz as int)) and
	(cast(@codeKalaEla as int)='0' or k.codeKala<=cast(@codeKalaEla as int)) and
	(@GroohKalaAz=0 or k.CodeGrooh>=@GroohKalaAz) and
	(@GroohKalaEla=0 or k.CodeGrooh<=@GroohKalaEla) and
	(@AnbarAz=0 or d.CodeAnbar>=@AnbarAz) and
	(@AnbarEla=0 or d.CodeAnbar<=@AnbarEla) and
	(@TafsiliAz=0 or t.codeHesab>=@TafsiliAz) and
	(@TafsiliEla=0 or t.codeHesab<=@TafsiliEla) and
	(@GroohShakhsAz=0 or t.CodeGroohAshkas>=@GroohShakhsAz) and
	(@GroohShakhsEla=0 or t.CodeGroohAshkas<=@GroohShakhsEla) and
	(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
	(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
	(
		m.vaziat not in
		(
		case @WithDeleteFactors
			when 0 then 101 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)and
	(
		m.noe not in
		(
		case @WithPishFactorForoosh
			when 0 then 30
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	) and
	(@CodeProjeh=0 or m.CodeProjeh = @CodeProjeh)

union all
------------------------------------------------------------ حواله انتقالی بین انبار صادره
select
	0[Radif],
	m.code[Code],
	m.codeBargeh[CodeBargeh],
	m.Tarikh[Tarikh],
	m.Saat [Saat],
	cast( 229 as smallint) /*EnteghalBeynAnbarKhorooji*/ [Noe],
	
	cast( 0 as bigint)[CodeTafsili],
	0[CodeHesabTafsili],
	0[CodeGoroohAshkhas],
	''[NameTafsili],
	'حواله انتقالی بین انبار صادره (' + cast ( m.CodeBargeh as nvarchar )  + ')',
	m.codeKarbar[CodeKarbar],
	m.vaziat[Vaziat],
	d.codeAnbar[CodeAnbar],
	d.codeKala[CodeKala],
	k.CodeKala[CodeKalaNamayeshi],
	k.CodeGrooh[CodeGorooh],
	k.name[NameKala],
	d.Tedad[Tedad],
	0[Mablagh],
	0[MablaghBargashtBeAnbar],

	m.SaatSabt[SaatSabt],

	0.0 [MeghdarVaredeh],
	cast(d.tedad as decimal(19,4)) [MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh],

	0.0 [MablaghVaredeh],
	0.0[MablaghSadereh],
	0 [MablaghJam],
	0.0[MablaghMiangin],
	0.0[MablaghMandeh],

	0.0[MablaghJamVaredeh],
	0.0[MablaghJamSadereh],
	0 [CodeProjeh]
from Bargeh m 
	join BargehDetail d on	m.code= d.codeBargeh 
	join Kala k on d.CodeKala = k.Code
where
	d.isKala = 1 and 
	m.noe in (36) /*HavalehEnteghaliBeinAnbar*/ and
	(cast(@codeKalaAz as int)='0' or k.codeKala>=cast(@codeKalaAz as int)) and
	(cast(@codeKalaEla as int)='0' or k.codeKala<=cast(@codeKalaEla as int)) and
	(@GroohKalaAz=0 or k.CodeGrooh>=@GroohKalaAz) and
	(@GroohKalaEla=0 or k.CodeGrooh<=@GroohKalaEla) and
	(@AnbarAz=0 or d.CodeAnbar>=@AnbarAz) and
	(@AnbarEla=0 or d.CodeAnbar<=@AnbarEla) and
	(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
	(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
	(
		m.vaziat not in
		(
		case @WithDeleteFactors
			when 0 then 101 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)
union all
------------------------------------------------------------ حواله انتقالی بین انبار وارده
select
	0[Radif],
	m.code[Code],
	m.codeBargeh[CodeBargeh],
	m.Tarikh[Tarikh],
	m.Saat [Saat],
	cast( 221 as smallint) /*EnteghalBeynAnbarVoroodi*/ [Noe],
	cast( 0 as bigint)[CodeTafsili],
	0[CodeHesabTafsili],
	0[CodeGoroohAshkhas],
	''[NameTafsili],
	'حواله انتقالی بین انبار وارده (' + cast ( m.CodeBargeh as nvarchar )  + ')',
	m.codeKarbar[CodeKarbar],
	m.vaziat[Vaziat],
	isnull(d.codeAnbarMaghsad , 0)[CodeAnbar],
	d.codeKala[CodeKala],
	k.CodeKala[CodeKalaNamayeshi],
	k.CodeGrooh[CodeGorooh],
	k.name[NameKala],
	d.Tedad[Tedad],
	0[Mablagh],
	0[MablaghBargashtBeAnbar],

	m.SaatSabt[SaatSabt],

	cast(d.tedad as decimal(19,4)) [MeghdarVaredeh],
	0.0 [MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh],

	0.0 [MablaghVaredeh],
	0.0[MablaghSadereh],
	0 [MablaghJam],
	0.0[MablaghMiangin],
	0.0[MablaghMandeh],

	0.0[MablaghJamVaredeh],
	0.0[MablaghJamSadereh],
	0 [CodeProjeh]
from Bargeh m 
	join BargehDetail d on m.code= d.codeBargeh 
	join Kala k on d.CodeKala = k.Code
where
	d.isKala = 1 and m.noe in (36) /*HavalehEnteghaliBeinAnbar*/ and
	(cast(@codeKalaAz as int)='0' or k.codeKala>=cast(@codeKalaAz as int)) and
	(cast(@codeKalaEla as int)='0' or k.codeKala<=cast(@codeKalaEla as int)) and
	(@GroohKalaAz=0 or k.CodeGrooh>=@GroohKalaAz) and
	(@GroohKalaEla=0 or k.CodeGrooh<=@GroohKalaEla) and
	(@AnbarAz=0 or d.codeAnbarMaghsad>=@AnbarAz) and
	(@AnbarEla=0 or d.codeAnbarMaghsad<=@AnbarEla) and
	(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
	(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
	(
		m.vaziat not in
		(
		case @WithDeleteFactors
			when 0 then 101 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)
union all
--------------------------------------------------- انبار گردانی
select
	0[Radif],
	m.code[Code],
	isnull(m.codeBargeh,0)[CodeBargeh],
	m.Tarikh[Tarikh],
	0 [Saat],
	case d.noe
	when 253 then cast( 225 as smallint) -- Hazienh
	when 254 then cast( 216 as smallint) -- Daramad
	end [Noe],
	d.CodeTafsili[CodeTafsili],
	t.CodeHesab[CodeHesabTafsili],
	isnull(t.CodeGroohAshkas,0)[CodeGoroohAshkhas],
	t.name[NameTafsili],
	case d.noe
	when 253 then 'کسورات انبار گردانی (' + cast (m.code as nvarchar) + ')' -- Hazienh 
	when 254 then 'اضافات انبارگردانی (' + cast (m.code as nvarchar) + ')'  -- Daramad
	end [Sharh],
	m.codeKarbar[CodeKarbar],
	m.vaziat[Vaziat],
	d.codeAnbar[CodeAnbar],
	d.codeKala[CodeKala],
	k.CodeKala[CodeKalaNamayeshi],
	k.CodeGrooh[CodeGorooh],
	k.name[NameKala],
	d.Tedad[Tedad],
	d.mablagh[Mablagh],
	0.0[MablaghBargashtBeAnbar],

	m.SaatSabt[SaatSabt],

	case d.noe
	when 253 then 0.0 -- Hazienh 
	when 254 then cast(d.tedad as decimal(19,4))  -- Daramad
	end [MeghdarVaredeh],
	case d.noe
	when 253 then cast(d.tedad as decimal(19,4))-- Hazienh 
	when 254 then 0.0  -- Daramad
	end [MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh],

	case d.noe
	when 253 then 0.0 -- Hazienh 
	when 254 then d.mablagh  -- Daramad
	end [MablaghVaredeh],
	0.0[MablaghSadereh],
	case d.noe
	when 253 then 0.0 -- Hazienh 
	when 254 then cast(d.tedad * d.mablagh as decimal(19,4))  -- Daramad
	end [MablaghJam],
	0.0[MablaghMiangin],
	0.0[MablaghMandeh],

	0.0[MablaghJamVaredeh],
	0.0[MablaghJamSadereh],
	0 [CodeProjeh]
from AnbarGardani m 
	join AnbarGardaniDetail d on	m.code = d.codeAnbarGardani 
	join tafsili t on t.code = d.codeTafsili
	join kala k on k.code=d.codeKala
where
	m.vaziat != 101 and
	(cast(@codeKalaAz as int)='0' or k.codeKala>=cast(@codeKalaAz as int)) and
	(cast(@codeKalaEla as int)='0' or k.codeKala<=cast(@codeKalaEla as int)) and
	(@GroohKalaAz=0 or k.CodeGrooh>=@GroohKalaAz) and
	(@GroohKalaEla=0 or k.CodeGrooh<=@GroohKalaEla) and
	(@AnbarAz=0 or d.CodeAnbar>=@AnbarAz) and
	(@AnbarEla=0 or d.CodeAnbar<=@AnbarEla) and
	(@TafsiliAz=0 or t.codeHesab>=@TafsiliAz) and
	(@TafsiliEla=0 or t.codeHesab<=@TafsiliEla) and
	(@GroohShakhsAz=0 or t.CodeGroohAshkas>=@GroohShakhsAz) and
	(@GroohShakhsEla=0 or t.CodeGroohAshkas<=@GroohShakhsEla) and
	(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
	(@tarikhEla=0 or m.tarikh<=@tarikhEla)and
	(
		m.vaziat not in
		(
		case @WithDeleteFactors
			when 0 then 101 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)

union all
-------------------------------------------------- تولید
--select 
--	0[Radif],
--	t.code[Code],
--	t.CodeTolid[CodeBargeh],
--	t.tarikh[Tarikh],
--	cast( 217 as smallint)[Noe], -- TolidKalaTolidi
--	cast( 0 as bigint)[CodeTafsili],
--	0[CodeHesabTafsili],
--	0 [CodeGoroohAshkhas],
--	'--'[NameTafsili],
--	'تولید کالا (' + cast(t.codeTolid as nvarchar) + ')'[Sharh],
--	t.codeKarbar[CodeKarbar],
--	t.Vaziat[Vaziat],
--	t.CodeAnbar[CodeAnbar],
--	t.codeKala[CodeKala],
--	k.CodeKala[CodeKalaNamayeshi],
--	k.CodeGrooh[CodeGorooh],
--	k.name[NameKala],
--	t.tedad[Tedad],
--	t.GheimatTamamShodeh	[Mablagh],
--	0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش

--	0[SaatSabt],

--	t.tedad[MeghdarVaredeh],
--	0.0[MeghdarSadereh],
--	dbo.fGetVahedBasteBandi(k.code,k.codevahed,t.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
--	0.0[MeghdarMandeh],

--	t.GheimatTamamShodeh	[MablaghVaredeh],
--	0.0[MablaghSadereh],
--	cast(t.tedad * t.GheimatTamamShodeh	 as decimal(19,4) )[MablaghJam],
--	0.0[MablaghMiangin],
--	0.0[MablaghMandeh],

--	0.0[MablaghJamVaredeh],
--	0.0[MablaghJamSadereh],
--	0 [CodeProjeh]
--from
--	Tolid t join Kala k on	t.CodeKala = k.Code
--where
--	t.vaziat != 101 and
--	t.noe = 264 and -- Tolid
--	(cast(@codeKalaAz as int) = '0' or k.codeKala >= cast(@codeKalaAz as int)) and
--	(cast(@codeKalaEla as int) = '0' or k.codeKala <= cast(@codeKalaEla as int)) and
--	(@GroohKalaAz = 0 or k.CodeGrooh >= @GroohKalaAz) and
--	(@GroohKalaEla = 0 or k.CodeGrooh <= @GroohKalaEla) and
--	(@AnbarAz = 0 or t.CodeAnbar >= @AnbarAz) and
--	(@AnbarEla = 0 or t.CodeAnbar <= @AnbarEla) and
--	(@tarikhAz = 0 or t.tarikh >= @tarikhAz) and
--	(@tarikhEla = 0 or t.tarikh <= @tarikhEla) and
--	(
--		t.vaziat not in
--		(
--		case @WithDeleteFactors
--			when 0 then 101 -- deleted
--			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
--		end
--		)
--	)
--------------------------------------------------
--union all
----------------------------------------------------

select 
	0[Radif],
	m.code[Code],
	m.Code [CodeBargeh],
	d.tarikh[Tarikh],
	d.Saat [Saat],
	case d.Dom_ID_Noe
		when 259 then cast( 218 as smallint) -- 259	ضايعات
		when 260 then cast( 227 as smallint) -- 260	کالاي مصرفي
		when 463 then cast( 217 as smallint) -- 463	کالاي توليدي
	end [Noe], 
	cast( 0 as bigint)[CodeTafsili],
	cast( 0 as bigint)[CodeHesabTafsili],
	0 [CodeGoroohAshkhas],
	'--' [NameTafsili],
	case d.Dom_ID_Noe
		when 259 then 'تولید ضایعات ('+ cast(m.code as nvarchar) + ')' -- TolidZayeaat
		when 260 then 'کالای مصرفی تولید ('+ cast(m.code as nvarchar) + ')' -- TolidKalaMasrafi
		when 463 then 'تولید ('+ cast(m.code as nvarchar) + ')' --
	end [Sharh],
	m.codeKarbar[CodeKarbar],
	cast(m.Dom_ID_Vaziat as smallint) [Vaziat],
	isnull(d.Anb_ID,0) [CodeAnbar],
	isnull(d.Kal_ID,0) [CodeKala],
	k.CodeKala [CodeKalaNamayeshi],
	k.CodeGrooh [CodeGorooh],
	k.name [NameKala],
	CAST(d.tedad as float) [Tedad],
	case d.Dom_ID_Noe
		when 259 then d.Price -- 259	ضايعات
		when 260 then d.Price -- 260	کالاي مصرفي
		when 463 then m.PriceFinal -- 463	کالاي توليدي
	end [Mablagh],
	0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش

	0[SaatSabt],

	case d.Dom_ID_Noe
		when 259 then cast(d.tedad as decimal(19,4)) -- TolidZayeaat
		when 260 then 0.0 -- TolidKalaMasrafi
		when 463 then cast(d.tedad as decimal(19,4)) -- tolid
	end [MeghdarVaredeh],
	case d.Dom_ID_Noe
		when 259 then 0.0 -- TolidZayeaat
		when 260 then cast(d.tedad as decimal(19,4))-- TolidKalaMasrafi
		when 463 then 0.0 -- tolid
	end [MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh],

	case d.Dom_ID_Noe
		when 259 then isnull(d.Price,0.0) -- TolidZayeaat
		when 260 then 0.0 -- TolidKalaMasrafi
		when 463 then isnull(m.PriceFinal,0.0) -- tolid
	end [MablaghVaredeh],
	0.0[MablaghSadereh],
	case d.Dom_ID_Noe
		when 259 then cast(d.tedad * d.Price as decimal(19,4) ) -- TolidZayeaat
		when 260 then 0.0 -- TolidKalaMasrafi
		when 463 then cast(d.tedad * m.PriceFinal as decimal(19,4) ) -- tolid
	end [MablaghJam],
	0.0[MablaghMiangin],
	0.0[MablaghMandeh],

	0.0[MablaghJamVaredeh],
	0.0[MablaghJamSadereh],
	0 [CodeProjeh]
from Product m 
	join ProductDetail d on m.ID = d.Prod_ID
	join Kala k on	d.Kal_ID = k.Code
where
	m.Dom_ID_Vaziat != 101 and
	(cast(@codeKalaAz as int) = '0' or k.codeKala >= cast(@codeKalaAz as int)) and
	(cast(@codeKalaEla as int) = '0' or k.codeKala <= cast(@codeKalaEla as int)) and
	(@GroohKalaAz = 0 or k.CodeGrooh >= @GroohKalaAz) and
	(@GroohKalaEla = 0 or k.CodeGrooh <= @GroohKalaEla) and
	(@AnbarAz = 0 or d.Anb_ID >= @AnbarAz) and
	(@AnbarEla = 0 or d.Anb_ID <= @AnbarEla) and
	(@tarikhAz = 0 or d.tarikh >= @tarikhAz) and
	(@tarikhEla = 0 or d.tarikh <= @tarikhEla)
----------------------------------------------------
union all

-------------------------------------------------- تبدیل
--select -- select * from TolidDetail
--	0[Radif],
--	t.code[Code],
--	t.CodeTolid[CodeBargeh],
--	t.tarikh[Tarikh],
--	cast( 228 as smallint)[Noe], -- TabdilKalaMasrafi
--	cast( 0 as bigint)[CodeTafsili],
--	0[CodeHesabTafsili],
--	0 [CodeGoroohAshkhas],
--	'--'[NameTafsili],
--	'تبدیل کالا (' + cast(t.codeTolid as nvarchar) + ')'[Sharh],
--	t.codeKarbar[CodeKarbar],
--	t.Vaziat[Vaziat],
--	t.CodeAnbar[CodeAnbar],
--	t.codeKala[CodeKala],
--	k.CodeKala[CodeKalaNamayeshi],
--	k.CodeGrooh[CodeGorooh],
--	k.name[NameKala],
--	t.tedad[Tedad],
--	t.GheimatTamamShodeh [Mablagh],
--	0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش

--	0[SaatSabt],

--	0.0[MeghdarVaredeh],
--	cast(t.tedad as decimal(19,4)) [MeghdarSadereh],
--	dbo.fGetVahedBasteBandi(k.code,k.codevahed,t.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
--	0.0[MeghdarMandeh],

--	0.0 [MablaghVaredeh],
--	0.0 [MablaghSadereh],
--	0.0 [MablaghJam],
--	0.0 [MablaghMiangin],
--	0.0 [MablaghMandeh],

--	0.0[MablaghJamVaredeh],
--	0.0[MablaghJamSadereh],
--	0 [CodeProjeh]
--from
--	Tolid t join Kala k on	t.CodeKala = k.Code
--where
--	t.vaziat != 101 and
--	t.noe = 265 and -- Tabdil
--	(cast(@codeKalaAz as int) = '0' or k.codeKala >= cast(@codeKalaAz as int)) and
--	(cast(@codeKalaEla as int) = '0' or k.codeKala <= cast(@codeKalaEla as int)) and
--	(@GroohKalaAz = 0 or k.CodeGrooh >= @GroohKalaAz) and
--	(@GroohKalaEla = 0 or k.CodeGrooh <= @GroohKalaEla) and
--	(@AnbarAz = 0 or t.CodeAnbar >= @AnbarAz) and
--	(@AnbarEla = 0 or t.CodeAnbar <= @AnbarEla) and
--	(@tarikhAz = 0 or t.tarikh >= @tarikhAz) and
--	(@tarikhEla = 0 or t.tarikh <= @tarikhEla) and
--	(
--		t.vaziat not in
--		(
--		case @WithDeleteFactors
--			when 0 then 101 -- deleted
--			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
--		end
--		)
--	)
--------------------------------------------------
--union all
----------------------------------------------------
--select 
--	0[Radif],
--	m.code[Code],
--	m.CodeTolid[CodeBargeh],
--	m.tarikh[Tarikh],
--	case d.noe
--		when 259 then cast( 220 as smallint) -- TabdilZayeaat
--		when 260 then cast( 228 as smallint) -- TabdilKalaMasrafi
--	end [Noe], 
--	cast( 0 as bigint)[CodeTafsili],
--	0[CodeHesabTafsili],
--	0 [CodeGoroohAshkhas],
--	'--'[NameTafsili],
--	case d.noe
--		when 259 then 'تبدیل ، کالای بوجود آمده ('+ cast(m.codeTolid as nvarchar) + ')' -- TabdilZayeaat
--		when 260 then 'کالای مصرفی تبدیل ('+ cast(m.codeTolid as nvarchar) + ')' -- TabdilKalaMasrafi
--	end [Sharh],
--	m.codeKarbar[CodeKarbar],
--	m.Vaziat[Vaziat],
--	d.CodeAnbar[CodeAnbar],
--	d.codeKala[CodeKala],
--	k.CodeKala[CodeKalaNamayeshi],
--	k.CodeGrooh[CodeGorooh],
--	k.name[NameKala],
--	d.tedad * m.tedad[Tedad],
--	d.mablagh [Mablagh],
--	0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش

--	0[SaatSabt],

--	case d.noe
--		when 259 then cast(d.tedad * m.tedad as decimal(19,4)) -- TabdilZayeaat
--		when 260 then 0.0 -- TabdilKalaMasrafi
--	end [MeghdarVaredeh],
--	case d.noe
--		when 259 then 0.0 -- TabdilZayeaat
--		when 260 then cast(d.tedad * m.tedad as decimal(19,4))-- TabdilKalaMasrafi
--	end [MeghdarSadereh],
--	dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad * m.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
--	0.0[MeghdarMandeh],

--	case d.noe
--		when 259 then d.mablagh -- TabdilZayeaat
--		when 260 then 0.0 -- TabdilKalaMasrafi
--	end [MablaghVaredeh],
--	0.0[MablaghSadereh],
--	case d.noe
--		when 259 then cast(d.tedad * d.mablagh as decimal(19,4) ) -- TabdilZayeaat
--		when 260 then 0.0 -- TabdilKalaMasrafi
--	end [MablaghJam],
--	0.0[MablaghMiangin],
--	0.0[MablaghMandeh],

--	0.0[MablaghJamVaredeh],
--	0.0[MablaghJamSadereh],
--	0 [CodeProjeh]
--from
	
--	Tolid m join TolidDetail d
--on
--	m.code = d.CodeTolid
--	join Kala k
--on
--	d.CodeKala = k.Code
--where
--	m.vaziat != 101 and
--	m.noe = 265 and -- Tabdil
--	(cast(@codeKalaAz as int) = '0' or k.codeKala >= cast(@codeKalaAz as int)) and
--	(cast(@codeKalaEla as int) = '0' or k.codeKala <= cast(@codeKalaEla as int)) and
--	(@GroohKalaAz = 0 or k.CodeGrooh >= @GroohKalaAz) and
--	(@GroohKalaEla = 0 or k.CodeGrooh <= @GroohKalaEla) and
--	(@AnbarAz = 0 or d.CodeAnbar >= @AnbarAz) and
--	(@AnbarEla = 0 or d.CodeAnbar <= @AnbarEla) and
--	(@tarikhAz = 0 or m.tarikh >= @tarikhAz) and
--	(@tarikhEla = 0 or m.tarikh <= @tarikhEla) and
--	(
--		m.vaziat not in
--		(
--		case @WithDeleteFactors
--			when 0 then 101 -- deleted
--			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
--		end
--		)
--	)

--union all
-------------------------------------------------- مصرف کالا و تولید کالا
select 
	0[Radif],
	m.code [Code],
	m.CodeBargeh [CodeBargeh],
	m.tarikh [Tarikh],
	m.Saat [Saat],
	case m.noe
		when 406 then cast( 230 as smallint) -- useKala
		when 407 then cast( 222 as smallint) -- ProuductKala
	end [Noe], 
	m.CodeTafsili [CodeTafsili],
	t.codeHesab [CodeHesabTafsili],
	0 [CodeGoroohAshkhas],
	t.name [NameTafsili],
	case m.noe
		when 406 then 'کالای مصرفی ('+ cast(m.codeBargeh as nvarchar) + ')' -- UseKala
		when 407 then 'کالای تولیدی ('+ cast(m.codeBargeh as nvarchar) + ')' -- ProuductKala
	end [Sharh],
	m.codeKarbar[CodeKarbar],
	m.Vaziat[Vaziat],
	d.CodeAnbar[CodeAnbar],
	d.codeKala[CodeKala],
	k.CodeKala[CodeKalaNamayeshi],
	k.CodeGrooh[CodeGorooh],
	k.name[NameKala],
	d.tedad [Tedad],
	d.mablagh [Mablagh],
	0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش

	0[SaatSabt],

	case m.noe
		when 406 then 0.0 -- UseKala
		when 407 then cast(d.tedad as decimal(19,4)) -- ProuductKala
	end [MeghdarVaredeh],
	case m.noe
		when 406 then cast(d.tedad as decimal(19,4)) -- UseKala
		when 407 then 0.0
	end [MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad ,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh],

	case m.noe
		when 406 then 0.0 -- UseKala
		when 407 then  d.mablagh -- ProuductKala
	end [MablaghVaredeh],
	0.0[MablaghSadereh],
	cast(d.tedad * d.mablagh as decimal(19,4)) [MablaghJam],
	0.0[MablaghMiangin],
	0.0[MablaghMandeh],

	0.0[MablaghJamVaredeh],
	0.0[MablaghJamSadereh],
	ISNULL(m.CodeProjeh , 0) [CodeProjeh]
from UseProductionKala m 
	join UseProductionKalaDetail d on m.code = d.CodeBargeh
	join Kala k on d.CodeKala = k.Code
	join tafsili t on t.code= m.codeTafsili
where
	m.vaziat != 101 and
	(cast(@codeKalaAz as int) = '0' or k.codeKala >= cast(@codeKalaAz as int)) and
	(cast(@codeKalaEla as int) = '0' or k.codeKala <= cast(@codeKalaEla as int)) and
	(@GroohKalaAz = 0 or k.CodeGrooh >= @GroohKalaAz) and
	(@GroohKalaEla = 0 or k.CodeGrooh <= @GroohKalaEla) and
	(@AnbarAz = 0 or d.CodeAnbar >= @AnbarAz) and
	(@AnbarEla = 0 or d.CodeAnbar <= @AnbarEla) and
	(@tarikhAz = 0 or m.tarikh >= @tarikhAz) and
	(@tarikhEla = 0 or m.tarikh <= @tarikhEla) and
	(
		m.vaziat not in
		(
		case @WithDeleteFactors
			when 0 then 101 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	) and
	(@CodeProjeh=0 or m.CodeProjeh = @CodeProjeh)

order by CodeKala , Noe , Tarikh
end

GO