IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='prcKartexAnbar')
	DROP proc prcKartexAnbar
GO
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],
	cast( 0 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
	(@codeKalaAz='0' or k.codeKala>=@codeKalaAz) and
	(@codeKalaEla='0' or k.codeKala<=@codeKalaEla) 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],
	case m.noe
            when 0 then cast( 10 as smallint) -- Foroosh
            when 1 then cast( 2 as smallint) -- BargashtAzForoosh
            when 3 then cast( 1 as smallint) -- Kharid
            when 4 then cast( 11 as smallint) -- BargashtAzKharid
            when 2 then cast( 13 as smallint) -- PishFactororoosh
	end [Noe],
        m.CodeMoshtari[CodeTafsili],
    	t.codeHesab[CodeHesabTafsili],
    	isnull(t.CodeGroohAshkas,0)[CodeGoroohAshkhas],
	t.name[NameTafsili],
        case m.noe
            when 0 then 'فاکتور فروش (' + cast ( m.CodeBargeh as nvarchar ) + ')'-- Foroosh
            when 1 then 'فاکتور برگشت از فروش (' + cast ( m.CodeBargeh as nvarchar )  + ')' -- BargashtAzForoosh
            when 3 then 'فاکتور خرید (' + cast ( m.CodeBargeh as nvarchar )  + ')' -- Kharid
            when 4 then 'فاکتور برگشت از خرید (' + cast ( m.CodeBargeh as nvarchar )  + ')' -- BargashtAzKharid
            when 2 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 0 then 0.0 -- Foroosh
            when 1 then d.tedad -- BargashtAzForoosh
            when 3 then d.tedad -- Kharid
            when 4 then 0.0 -- BargashtAzKharid
            when 2 then 0.0 -- PishFactorForoosh
	end [MeghdarVaredeh],
	case m.noe
            when 0 then cast(d.tedad as decimal(19,4)) -- Foroosh
            when 1 then 0.0 -- BargashtAzForoosh
            when 3 then 0.0 -- Kharid
            when 4 then cast(d.tedad as decimal(19,4)) -- BargashtAzKharid
            when 2 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 0 then 0.0 -- Foroosh
            when 1 then d.GheimatBarghashtiBeAnbar -- BargashtAzForoosh
            when 3 then d.mablagh -- Kharid
            when 4 then 0.0 -- BargashtAzKharid
            when 2 then 0.0 -- PishFactorForoosh
	end [MablaghVaredeh],
	0.0[MablaghSadereh],
	case m.noe
            when 0 then 0.0-- Foroosh
            when 1 then cast(d.tedad * d.GheimatBarghashtiBeAnbar as decimal(19,4)) -- BargashtAzForoosh
            when 3 then cast(d.tedad * d.mablagh as decimal(19,4)) -- Kharid
            when 4 then 0.0 -- BargashtAzKharid
            when 2 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 != 5 and
	(@codeKalaAz='0' or k.codeKala>=@codeKalaAz) and
	(@codeKalaEla='0' or k.codeKala<=@codeKalaEla) 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 1 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)and
	(
		m.noe not in
		(
		case @WithPishFactorForoosh
			when 0 then 2
			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],
	cast( 8 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],
        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],

	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 (8) /*HavalehEnteghaliBeinAnbar*/ and
	(@codeKalaAz='0' or k.codeKala>=@codeKalaAz) and
	(@codeKalaEla='0' or k.codeKala<=@codeKalaEla) 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 1 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)
union all
------------------------------------------------------------ حواله انتقالی بین انبار صادره
select
        0[Radif],
        m.code[Code],
  	m.codeBargeh[CodeBargeh],
        m.Tarikh[Tarikh],
	cast( 16 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],
        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],

	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 (8) /*HavalehEnteghaliBeinAnbar*/ and
	(@codeKalaAz='0' or k.codeKala>=@codeKalaAz) and
	(@codeKalaEla='0' or k.codeKala<=@codeKalaEla) 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 1 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)
union all
--------------------------------------------------- انبار گردانی
select
        0[Radif],
        m.code[Code],
  	isnull(m.codeBargeh,0)[CodeBargeh],
        m.Tarikh[Tarikh],
	case d.noe
		when 0 then cast( 12 as smallint) -- Hazienh
		when 1 then cast( 3 as smallint) -- Daramad
	end [Noe],
        d.CodeTafsili[CodeTafsili],
    	t.CodeHesab[CodeHesabTafsili],
    	isnull(t.CodeGroohAshkas,0)[CodeGoroohAshkhas],
	t.name[NameTafsili],
        case d.noe
		when 0 then 'کسورات انبار گردانی (' + cast (m.code as nvarchar) + ')' -- Hazienh 
		when 1 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 0 then 0.0 -- Hazienh 
		when 1 then cast(d.tedad as decimal(19,4))  -- Daramad
	end [MeghdarVaredeh],
	case d.noe
		when 0 then cast(d.tedad as decimal(19,4))-- Hazienh 
		when 1 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 0 then 0.0 -- Hazienh 
		when 1 then d.mablagh  -- Daramad
	end [MablaghVaredeh],
	0.0[MablaghSadereh],
	case d.noe
		when 0 then 0.0 -- Hazienh 
		when 1 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 != 1 and
	(@codeKalaAz='0' or k.codeKala>=@codeKalaAz) and
	(@codeKalaEla='0' or k.codeKala<=@codeKalaEla) 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 1 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)

union all
-------------------------------------------------- تولید
select 
	0[Radif],
	t.code[Code],
	t.CodeTolid[CodeBargeh],
	t.tarikh[Tarikh],
	cast( 4 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 != 1 and
	t.noe = 0 and -- Tolid
	(@codeKalaAz = '0' or k.codeKala >= @codeKalaAz) and
	(@codeKalaEla = '0' or k.codeKala <= @codeKalaEla) 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 1 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)
--------------------------------------------------
union all
--------------------------------------------------
select 
	0[Radif],
	m.code[Code],
	m.CodeTolid[CodeBargeh],
	m.tarikh[Tarikh],
	case d.noe
		when 0 then cast( 5 as smallint) -- TolidZayeaat
		when 1 then cast( 14 as smallint) -- TolidKalaMasrafi
	end [Noe], 
	cast( 0 as bigint)[CodeTafsili],
	0[CodeHesabTafsili],
	0 [CodeGoroohAshkhas],
	'--'[NameTafsili],
	case d.noe
		when 0 then 'تولید ضایعات ('+ cast(m.codeTolid as nvarchar) + ')' -- TolidZayeaat
		when 1 then 'کالای مصرفی تولید ('+ cast(m.codeTolid as nvarchar) + ')' -- TolidKalaMasrafi
	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 0 then cast(d.tedad * m.tedad as decimal(19,4)) -- TolidZayeaat
		when 1 then 0.0 -- TolidKalaMasrafi
	end [MeghdarVaredeh],
	case d.noe
		when 0 then 0.0 -- TolidZayeaat
		when 1 then cast(d.tedad * m.tedad as decimal(19,4))-- TolidKalaMasrafi
	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 0 then d.mablagh -- TolidZayeaat
		when 1 then 0.0 -- TolidKalaMasrafi
	end [MablaghVaredeh],
	0.0[MablaghSadereh],
	case d.noe
		when 0 then cast(d.tedad * d.mablagh as decimal(19,4) ) -- TolidZayeaat
		when 1 then 0.0 -- TolidKalaMasrafi
	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 != 1 and
	m.noe = 0 and -- Tolid
	(@codeKalaAz = '0' or k.codeKala >= @codeKalaAz) and
	(@codeKalaEla = '0' or k.codeKala <= @codeKalaEla) 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 1 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)
--------------------------------------------------
union all
-------------------------------------------------- تبدیل
select -- select * from TolidDetail
	0[Radif],
	t.code[Code],
	t.CodeTolid[CodeBargeh],
	t.tarikh[Tarikh],
	cast( 15 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 != 1 and
	t.noe = 1 and -- Tabdil
	(@codeKalaAz = '0' or k.codeKala >= @codeKalaAz) and
	(@codeKalaEla = '0' or k.codeKala <= @codeKalaEla) 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 1 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)
--------------------------------------------------
union all
--------------------------------------------------
select 
	0[Radif],
	m.code[Code],
	m.CodeTolid[CodeBargeh],
	m.tarikh[Tarikh],
	case d.noe
		when 0 then cast( 7 as smallint) -- TabdilZayeaat
		when 1 then cast( 15 as smallint) -- TabdilKalaMasrafi
	end [Noe], 
	cast( 0 as bigint)[CodeTafsili],
	0[CodeHesabTafsili],
	0 [CodeGoroohAshkhas],
	'--'[NameTafsili],
	case d.noe
		when 0 then 'تبدیل ، کالای بوجود آمده ('+ cast(m.codeTolid as nvarchar) + ')' -- TabdilZayeaat
		when 1 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 0 then cast(d.tedad * m.tedad as decimal(19,4)) -- TabdilZayeaat
		when 1 then 0.0 -- TabdilKalaMasrafi
	end [MeghdarVaredeh],
	case d.noe
		when 0 then 0.0 -- TabdilZayeaat
		when 1 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 0 then d.mablagh -- TabdilZayeaat
		when 1 then 0.0 -- TabdilKalaMasrafi
	end [MablaghVaredeh],
	0.0[MablaghSadereh],
	case d.noe
		when 0 then cast(d.tedad * d.mablagh as decimal(19,4) ) -- TabdilZayeaat
		when 1 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 != 1 and
	m.noe = 1 and -- Tabdil
	(@codeKalaAz = '0' or k.codeKala >= @codeKalaAz) and
	(@codeKalaEla = '0' or k.codeKala <= @codeKalaEla) 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 1 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)

union all
-------------------------------------------------- مصرف کالا و تولید کالا
select 
	0[Radif],
	m.code [Code],
	m.CodeBargeh [CodeBargeh],
	m.tarikh [Tarikh],
	case m.noe
		when 0 then cast( 17 as smallint) -- useKala
		when 1 then cast( 9 as smallint) -- ProuductKala
	end [Noe], 
	m.CodeTafsili [CodeTafsili],
	t.codeHesab [CodeHesabTafsili],
	0 [CodeGoroohAshkhas],
	t.name [NameTafsili],
	case m.noe
		when 0 then 'کالای مصرفی ('+ cast(m.codeBargeh as nvarchar) + ')' -- UseKala
		when 1 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 0 then 0.0 -- UseKala
		when 1 then cast(d.tedad as decimal(19,4)) -- ProuductKala
	end [MeghdarVaredeh],
	case m.noe
		when 0 then cast(d.tedad as decimal(19,4)) -- UseKala
		when 1 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 0 then 0.0 -- UseKala
		when 1 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 != 1 and
	(@codeKalaAz = '0' or k.codeKala >= @codeKalaAz) and
	(@codeKalaEla = '0' or k.codeKala <= @codeKalaEla) 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 1 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	) and
	(@CodeProjeh=0 or m.CodeProjeh = @CodeProjeh)

order by CodeKala , Noe , Tarikh
end

GO