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 [ID],
	0[CodeBargeh],
	0[Tarikh],
	cast( 0 as smallint) [Dom_ID_Noe],
	cast( 0 as bigint) [Taf_ID],
	0 [Taf_Code],
	0  [Pro_ID_GroohAshkhas],
	'--' [Taf_Name],
	'موجودی اول دوره'[Sharh],
	0 [Kar_ID],
	cast(100 as smallint)[Vaziat],
	d.Pro_ID_Anbar [Pro_ID_Anbar],
	d.Kal_ID [Kal_ID],
	k.Code  [Kal_Code],
	k.Pro_ID_Grooh [Pro_ID_GroohKala],
	k.name [Kal_Name],
	d.MojoodiAvalDoreh[Tedad],
	0[SaatSabt],

	d.MojoodiAvalDoreh[MeghdarVaredeh],
	0.0[MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.ID,k.Pro_ID_Vahed,d.mojoodiAvalDoreh,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh]

from
	KalaDarAnbar d join Kala k
on
	d.Kal_ID = k.ID
where
	(@codeKalaAz='0' or k.Code >=@codeKalaAz) and
	(@codeKalaEla='0' or k.Code <=@codeKalaEla) and
	(@GroohKalaAz=0 or k.Pro_ID_Grooh >=@GroohKalaAz) and
	(@GroohKalaEla=0 or k.Pro_ID_Grooh <=@GroohKalaEla) and
	(@AnbarAz=0 or d.Pro_ID_Anbar >=@AnbarAz) and
	(@AnbarEla=0 or d.Pro_ID_Anbar <=@AnbarEla)

union all
------------------------------------------------------------حواله و رسید انبار  و حواله انتقالی بین انبار صادره
select
        0[Radif],
        m.ID [ID],
  	m.Code [CodeBargeh],
        m.Tarikh[Tarikh],
	case m.Dom_ID_Noe
		when 34 then cast( 10 as smallint) /*HavalehAnbar*/ 
		when 35 then cast( 1 as smallint) /*ResidAnbar*/ 
		when 36 then cast( 11 as smallint) /*EnteghalBeynAnbarKhorooji*/ 
	end  [Dom_ID_Noe],
        cast( 0 as bigint) [Taf_ID],
    	0 [Taf_Code],
    	0 [Pro_ID_GroohAshkhas],
	'' [Taf_Name],
	case m.Dom_ID_Noe
		when 34 then 'حواله انبار (' + cast ( m.Code  as nvarchar )  + ')' /*HavalehAnbar*/ 
		when 35 then 'رسید انبار (' + cast ( m.Code  as nvarchar )  + ')' /*ResidAnbar*/ 
		when 36 then 'حواله انتقالی بین انبار صادره (' + cast ( m.Code  as nvarchar )  + ')' /*EnteghalBeynAnbarKhorooji*/ 
	end [Sharh],
        m.Kar_ID  [Kar_ID],
        m.Dom_ID_Vaziat [Vaziat],
        d.Pro_ID_Anbar [Pro_ID_Anbar],
        d.Kal_ID [Kal_ID],
	k.Code  [Kal_Code],
	k.Pro_ID_Grooh [Pro_ID_GroohKala],
	k.name [Kal_Name],
	d.Tedad [Tedad],

	m.SaatSabt[SaatSabt],

	case m.Dom_ID_Noe
		when 34 then 0.0 /*HavalehAnbar*/ 
		when 35 then cast( d.tedad as decimal(19,4)) /*ResidAnbar*/ 
		when 36 then 0.0 /*EnteghalBeynAnbarKhorooji*/ 
	end [MeghdarVaredeh],
	case m.Dom_ID_Noe
		when 34 then cast( d.tedad as decimal(19,4)) /*HavalehAnbar*/ 
		when 35 then 0.0 /*ResidAnbar*/ 
		when 36 then cast(d.tedad as decimal(19,4)) /*EnteghalBeynAnbarKhorooji*/ 
	end  [MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.ID,k.Pro_ID_Vahed,d.tedad,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh]


from
	Bargeh m join BargehDetail d
on
	m.ID= d.Bar_ID
	join Kala k
on
	d.Kal_ID = k.ID
where
	d.isKala = 1 and m.Dom_ID_Noe in (34,35,36) /*HavalehAnbar,ResidAnbar,HavalehEnteghaliBeinAnbar*/ and
	(@codeKalaAz='0' or k.Code >=@codeKalaAz) and
	(@codeKalaEla='0' or k.Code <=@codeKalaEla) and
	(@GroohKalaAz=0 or k.Pro_ID_Grooh >=@GroohKalaAz) and
	(@GroohKalaEla=0 or k.Pro_ID_Grooh <=@GroohKalaEla) and
	(@AnbarAz=0 or d.Pro_ID_Anbar >=@AnbarAz) and
	(@AnbarEla=0 or d.Pro_ID_Anbar <=@AnbarEla) and
	(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
	(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
	(
		m.Dom_ID_Vaziat not in
		(
		case @WithDeleteFactors
			when 0 then 101 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)
union all
------------------------------------------------------------ حواله انتقالی بین انبار وارده
select
        0[Radif],
        m.ID [ID],
  	m.Code [CodeBargeh],
        m.Tarikh[Tarikh],
	cast( 2 as smallint) /*EnteghalBeynAnbarVoroodi*/  [Dom_ID_Noe],
        cast( 0 as bigint) [Taf_ID],
    	0 [Taf_Code],
    	0 [Pro_ID_GroohAshkhas],
	'' [Taf_Name],
        'حواله انتقالی بین انبار وارده (' + cast ( m.Code  as nvarchar )  + ')' [Sharh],
        m.Kar_ID  [Kar_ID],
        m.Dom_ID_Vaziat [Vaziat],
        isnull(d.Pro_ID_AnbarMaghsad , 0) [Pro_ID_Anbar],
        d.Kal_ID [Kal_ID],
	k.Code  [Kal_Code],
	k.Pro_ID_Grooh [Pro_ID_GroohKala],
	k.name [Kal_Name],
        d.Tedad[Tedad],

	m.SaatSabt[SaatSabt],

	cast(d.tedad as decimal(19,4)) [MeghdarVaredeh],
	0.0 [MeghdarSadereh],
	dbo.fGetVahedBasteBandi(k.ID,k.Pro_ID_Vahed,d.tedad,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,k.tedadVahedBozorg)[MeghdarPack],
	0.0[MeghdarMandeh]

from
	Bargeh m join BargehDetail d
on
	m.ID= d.Bar_ID
	join Kala k
on
	d.Kal_ID = k.ID
where
	d.isKala = 1 and m.Dom_ID_Noe in (36) /*HavalehEnteghaliBeinAnbar*/ and
	(@codeKalaAz='0' or k.Code >=@codeKalaAz) and
	(@codeKalaEla='0' or k.Code <=@codeKalaEla) and
	(@GroohKalaAz=0 or k.Pro_ID_Grooh >=@GroohKalaAz) and
	(@GroohKalaEla=0 or k.Pro_ID_Grooh <=@GroohKalaEla) and
	(@AnbarAz=0 or d.Pro_ID_Anbar >=@AnbarAz) and
	(@AnbarEla=0 or d.Pro_ID_Anbar <=@AnbarEla) and
	(@tarikhAz=0 or m.tarikh>=@tarikhAz) and
	(@tarikhEla=0 or m.tarikh<=@tarikhEla) and
	(
		m.Dom_ID_Vaziat not in
		(
		case @WithDeleteFactors
			when 0 then 101 -- deleted
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود
		end
		)
	)


order by Code  , Dom_ID_Noe , Tarikh
end

GO