IF EXISTS(SELECT NAME FROM sysobjects s WHERE s.name='prcMojoodiAnbar')
	DROP proc prcMojoodiAnbar
GO
create proc prcMojoodiAnbar
	@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,
	@WithDeleteFactors bit=0 -- 0=false , 1=True
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],
	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]

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 6 then cast( 10 as smallint) /*HavalehAnbar*/ 
		when 7 then cast( 1 as smallint) /*ResidAnbar*/ 
		when 8 then cast( 11 as smallint) /*EnteghalBeynAnbarKhorooji*/ 
	end [Noe],
        cast( 0 as bigint)[CodeTafsili],
    	0[CodeHesabTafsili],
    	0[CodeGoroohAshkhas],
	''[NameTafsili],
	case m.noe
		when 6 then 'حواله انبار (' + cast ( m.CodeBargeh as nvarchar )  + ')' /*HavalehAnbar*/ 
		when 7 then 'رسید انبار (' + cast ( m.CodeBargeh as nvarchar )  + ')' /*ResidAnbar*/ 
		when 8 then 'حواله انتقالی بین انبار صادره (' + cast ( m.CodeBargeh as nvarchar )  + ')' /*EnteghalBeynAnbarKhorooji*/ 
	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],

	m.SaatSabt[SaatSabt],

	case m.noe
		when 6 then 0.0 /*HavalehAnbar*/ 
		when 7 then cast( d.tedad as decimal(19,4)) /*ResidAnbar*/ 
		when 8 then 0.0 /*EnteghalBeynAnbarKhorooji*/ 
	end [MeghdarVaredeh],
	case m.noe
		when 6 then cast( d.tedad as decimal(19,4)) /*HavalehAnbar*/ 
		when 7 then 0.0 /*ResidAnbar*/ 
		when 8 then cast(d.tedad as decimal(19,4)) /*EnteghalBeynAnbarKhorooji*/ 
	end  [MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.code,k.codevahed,d.tedad,k.codevahedMotevaset,k.tedadVahedMotevaset,k.codevahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh]


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 (6,7,8) /*HavalehAnbar,ResidAnbar,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( 2 as smallint) /*EnteghalBeynAnbarVoroodi*/ [Noe],
        cast( 0 as bigint)[CodeTafsili],
    	0[CodeHesabTafsili],
    	0[CodeGoroohAshkhas],
	''[NameTafsili],
        'حواله انتقالی بین انبار وارده (' + cast ( m.CodeBargeh as nvarchar )  + ')' [Sharh],
        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],

	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]

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
		)
	)


order by CodeKala , Noe , Tarikh
end

GO