-- prcKartexAnbar 0,0,0,0,'0','0',0,0,0,0,0,0,0,0,0,0,33,0,'','','',0,0,'','','','','',''
    
alter proc dbo.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    
	@TimeAz int=0,    
	@TimeEla int=0,    
	@Fc_ID int=0,  
	@Pro_ID_VahedMali int=0,  
	@strType nvarchar(max)='',  
	@strKala nvarchar(max)=''  ,
	@strRestorationCode nvarchar(max)='',
	@factorCodeAz int=0,    
	@factorCodeEla int=0, 
	@strProIdColors nvarchar(max)='',
	@strProIdSizes nvarchar(max)='',
	@strConIds nvarchar(max)='',
	@strPpIds nvarchar(max)='',
	@strPpiIds nvarchar(max)='',
	@strBoothIds nvarchar(max)=''
as    
begin    
    
----------------------------------- موجودی اول دوره    
select    
	0[Radif],    
	d.Fc_ID [FC_ID],  
	d.Pro_ID_VahedMali [Pro_ID_VahedMali],  
	d.ID[ID],    
	0[Bar_Code],    
	d.tarikh[Tarikh],    
	0[Saat],    
	cast( 213 as smallint)[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 [Anb_ID],    
	d.Kal_ID [Kal_ID],    
	k.Code [Kal_Code],    
	k.TechnicalCode [TechnicalCode],    
	k.Pro_ID_Vahed [Pro_ID_Unit],    
	k.Pro_ID_Grooh [Pro_ID_GroohKala],    
	k.name[Kal_Name],    
	d.Dom_ID_RestorationCode [Dom_ID_Restoration],
	isnull(d.Pro_ID_Color,0) [Pro_ID_Color],
	isnull(d.Pro_ID_Size,0) [Pro_ID_Size],
	d.MojoodiAvalDoreh[Tedad],    
	d.GheymatAvalDoreh[Mablagh],    
	0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش    

	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],    

	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],
	cast(1 as bit) [IsEntry],
	0 [BD_ID],
	0 [Con_ID],
	0 [PP_ID],
	0 [PPI_ID],
	0 [Booth_ID]
from    
 KalaDarAnbar d 
 join Kala k on d.Kal_ID = k.ID    
where    
	(@Fc_ID=0 or d.Fc_ID = @Fc_ID) and  
	(@Pro_ID_VahedMali=0 or d.Pro_ID_VahedMali = @Pro_ID_VahedMali) and  
	(cast(@codeKalaAz as int)='0' or k.Code >=cast(@codeKalaAz as int)) and    
	(cast(@codeKalaEla as int)='0' or k.Code <=cast(@codeKalaEla as int)) 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  
	(@Fc_ID=0 or d.Fc_ID = @Fc_ID) and  
	(@Pro_ID_VahedMali=0 or d.Pro_ID_VahedMali = @Pro_ID_VahedMali) and  
	(@strType='' or 213 in (select * from dbo.CastStringToTable(@strType))) and  
	(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and
    (@factorCodeAz = 0 and @factorCodeEla = 0)and
	(@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and  
	(@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
	(@strConIds = '') and
	(@strPpIds = '') and
	(@strPpiIds = '') and
	(@strBoothIds = '')
	
union all    
------------------------------------------------------------ برگه    
select    
	0[Radif],    
    m.Fc_ID [FC_ID],  
	m.Pro_ID_VahedMali [Pro_ID_VahedMali],  
    m.ID[ID] ,    
    m.Code [Bar_Code],    
    m.Tarikh[Tarikh],    
    m.Saat [Saat] ,    
	case m.Dom_ID_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    
		when 791 then cast( 793 as smallint) -- اضافات انبارگرداني
		when 792 then cast( 794 as smallint) -- کسورات انبارگرداني    
		when 1162 then cast( 1166 /*TrustedGoodsPurchase*/as smallint) -- خرید کالای امانی    
		when 1163 then cast( 1167 /*TrustedGoodsSale*/as smallint) -- فروش کالای امانی    
		when 1190 then cast( 1196 /*PlayStationFactor*/as smallint) -- فروش خدمات ایستگاهی
		when 1275 then cast( 1279 /*ProjectDeductionFactor*/as smallint) -- فاکتور مصرفی پروژه
		when 1276 then cast( 1280 /*ProjectAdditionProject*/as smallint) -- فاکتور مانده پروژه
	end [Noe],    
    m.Taf_ID_Moshtari[Taf_ID],    
    t.Code [Taf_Code],    
     --isnull(t.Pro_ID,0)[Pro_ID_GroohAshkhas],    
    0 [Pro_ID_GroohAshkhas],    
	t.Title[Taf_Name],    
	case m.Dom_ID_noe    
		when 28 then 'فاکتور فروش (' 
		when 29 then 'فاکتور برگشت از فروش (' 
		when 31 then 'فاکتور خرید (' 
		when 32 then 'فاکتور برگشت از خرید (' 
		when 30 then 'پیش فاکتور فروش (' 
		when 791 then 'اضافات انبار (' 
		when 792 then 'کسورات انبار (' 
		when 1162 then 'خرید کالای امانی (' 
		when 1163 then 'فروش کالای امانی (' 
		when 1190 then 'فروش خدمات ایستگاهی (' 
		when 1275 then 'فاکتور مصرفی پروژه (' 
		when 1276 then 'فاکتور مانده پروژه (' 
	end + cast ( m.Code  as nvarchar ) + ')' +(case when d.Dom_ID_Noe = 1871 then ' اشانتیون' else '' end)[Sharh],    
    m.Kar_ID [Kar_ID],    
	m.Dom_ID_Vaziat [Vaziat],    
    d.Pro_ID_Anbar [Anb_ID],    
    d.Kal_ID [Kal_ID],    
	k.Code  [Kal_Code], 
	k.TechnicalCode [TechnicalCode],    
	k.Pro_ID_Vahed [Pro_ID_Unit],       
	k.Pro_ID_Grooh [Pro_ID_GroohKala],    
	k.name[Kal_Name],    
	d.Dom_ID_Restoration,
	isnull(d.Pro_ID_Color,0),
	isnull(d.Pro_ID_Size,0),
    d.Tedad[Tedad],    
    cast((d.mablagh + isnull(d.MablaghSupplement,0.0)-isnull((d.MablagheTakhfif/d.tedad),0.0))as decimal(19,4))[Mablagh],    
	cast((isnull(d.GheimatBarghashtiBeAnbar,0.0) + isnull(d.MablaghSupplement,0.0)-isnull((d.MablagheTakhfif/d.tedad),0.0))as decimal(19,4)) [MablaghBargashtBeAnbar],    
	m.SaatSabt[SaatSabt],    
	case m.Dom_ID_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    
		when 791 then d.Tedad -- اضافات انبارگرداني    
		when 792 then 0.0 -- کسورات انبارگرداني    
		when 1162 then d.Tedad -- خرید کالای امانی    
		when 1275 then 0.0 -- فاکتور مصرفی پروژه    
		when 1276 then d.Tedad -- فاکتور مانده پروژه    
	end [MeghdarVaredeh],    
	case m.Dom_ID_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    
		when 791 then 0.0 -- اضافات انبارگرداني    
		when 792 then cast(d.tedad as decimal(19,4)) -- کسورات انبارگرداني    
		when 1163 then cast(d.tedad as decimal(19,4)) -- فروش کالای امانی    
		when 1190 then cast(d.tedad as decimal(19,4)) -- فروش خدمات ایستگاهی    
		when 1275 then cast(d.tedad as decimal(19,4)) -- فاکتور مصرفی پروژه    
		when 1276 then 0.0 -- فاکتور مانده پروژه    
	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],    
	cast((case when m.Dom_ID_noe in (29/*BargashtAzForoosh*/,31/*Kharid*/,791/*اضافات انبارگرداني*/,1162/*خرید کالای امانی*/ , 1276 /*فاکتور مانده پروژه*/) then d.mablagh+isnull(d.MablaghSupplement,0.0)-isnull((d.MablagheTakhfif/d.tedad),0.0) else 0.0 end) as decimal(19,4)) [MablaghVaredeh],    
	0.0[MablaghSadereh],
	case when m.Dom_ID_noe in (29,31,791,1162,1276) then cast(d.tedad * (d.mablagh+isnull(d.MablaghSupplement,0.0)-isnull((d.MablagheTakhfif/d.tedad),0.0)) as decimal(19,4)) else 0.0 end [MablaghJam],    
	0.0[MablaghMiangin],    
	0.0[MablaghMandeh],
	0.0[MablaghJamVaredeh],    
	0.0[MablaghJamSadereh],
	cast(case when m.Dom_ID_noe in (29,31,791,1162/*TrustedGoodsPurchase*/,1276 /*فاکتور مانده پروژه*/) then 1 else 0 end as bit) [IsEntry],
	ISNULL(d.BD_ID,0) [BD_ID],
	ISNULL(m.Con_ID,0) [Con_ID],
	ISNULL(m.PP_ID,0) [PP_ID],
	ISNULL(m.PPI_ID,0) [PPI_ID],
	ISNULL(d.Booth_ID,0) [Booth_ID]
from Bargeh m     
 join BargehDetail d on m.ID= d.Bar_ID    
 join Kala k on d.Kal_ID = k.ID    
 join tafsili t on m.Taf_ID_Moshtari=t.ID    
where    
 d.Dom_ID_Noe in (666,1871) and m.Dom_ID_noe not in (33 /*PishFactorKharid*/,1158 /*TrustedGoodsDaryafti*/,1159/*TrustedGoodsPardakhti*/) and    
 (cast(@codeKalaAz as int)='0' or k.Code >=cast(@codeKalaAz as int)) and    
 (cast(@codeKalaEla as int)='0' or k.Code <=cast(@codeKalaEla as int)) 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    
 (@TafsiliAz=0 or t.Code >=@TafsiliAz) and    
 (@TafsiliEla=0 or t.Code <=@TafsiliEla) and    
 (@Fc_ID=0 or m.Fc_ID = @Fc_ID) and  
 (@Pro_ID_VahedMali=0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and  
 (@tarikhAz=0 or m.tarikh>=@tarikhAz) and    
 (@tarikhEla=0 or m.tarikh<=@tarikhEla) and    
 (@TimeAz=0 or m.saat>=@TimeAz) and    
 (@TimeEla=0 or m.saat<=@TimeEla) and    
 (@factorCodeAz=0 or m.Code<=@factorCodeAz) and    
 (@factorCodeEla=0 or m.Code<=@factorCodeEla) and    
 (  
  @strType='' or   
  (  
  case m.Dom_ID_Noe    
            when 28 then cast( 223 as int) -- Foroosh    
            when 29 then cast( 215 as int) -- BargashtAzForoosh    
            when 31 then cast( 214 as int) -- Kharid    
            when 32 then cast( 224 as int) -- BargashtAzKharid    
            when 30 then cast( 226 as int) -- PishFactororoosh    
            when 791 then cast( 793 as int) -- اضافات انبارگرداني    
            when 792 then cast( 794 as int) -- کسورات انبارگرداني    
            when 1162 then cast( 1166 as int) -- خرید کالای امانی
            when 1163 then cast( 1167 as int) -- فروش کالای امانی
            when 1190 then cast( 1196 as int) -- فاکتور بازی
            when 1275 then cast( 1279 as int) -- فاکتور مصرفی پروژه
            when 1276 then cast( 1280 as int) -- فاکتور مانده پروژه
 end in (select * from dbo.CastStringToTable(@strType))  
  )  
 )and  
 (@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and  
 (    
  m.Dom_ID_vaziat not in    
  (    
  case @WithDeleteFactors    
   when 0 then 101 -- deleted    
   when 1 then 20000000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود    
  end    
  )    
 )and    
 (    
  m.Dom_ID_noe not in    
  (    
  case @WithPishFactorForoosh    
   when 0 then 30    
   when 1 then 20000000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود    
  end    
  )    
 ) and
 (@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and  
 (@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
	(@strConIds = '' or m.Con_ID in (select * from dbo.CastStringToTable(@strConIds))) and
	(@strPpIds = '' or m.PP_ID in (select * from dbo.CastStringToTable(@strPpIds))) and
	(@strPpiIds = '' or m.PPI_ID in (select * from dbo.CastStringToTable(@strPpiIds))) and
	(@strBoothIds = '' or d.Booth_ID in (select * from dbo.CastStringToTable(@strBoothIds))) 
 
    
union all    
------------------------------------------------------------ حواله انتقالی بین انبار صادره    
select    
	0[Radif],   
	m.Fc_ID [FC_ID],  
	isnull(d.Pro_ID_VahedMali,0) [Pro_ID_VahedMali],  
	m.ID [ID],    
	m.Code [Bar_Code],    
	m.Tarikh[Tarikh],    
	m.Saat [Saat],    
	cast( 229 as smallint) /*EnteghalBeynAnbarKhorooji*/ [Noe],    
	 
	cast( 0 as bigint)[Taf_ID],    
	0[Taf_Code],    
	0[Pro_ID_GroohAshkhas],    
	''[Taf_Name],    
	'حواله انتقالی بین انبار صادره (' + cast ( m.Code  as nvarchar )  + ')',    
	m.Kar_ID [Kar_ID],    
	m.Dom_ID_Vaziat [Vaziat],    
	d.Pro_ID_Anbar [Anb_ID],    
	d.Kal_ID [Kal_ID],    
	k.Code [Kal_Code],    
	k.TechnicalCode [TechnicalCode],    
	k.Pro_ID_Vahed [Pro_ID_Unit],    
	k.Pro_ID_Grooh [Pro_ID_GroohKala],    
	k.name[Kal_Name], 
	d.Dom_ID_Restoration,   
	isnull(d.Pro_ID_Color,0),
	isnull(d.Pro_ID_Size,0),
	d.Tedad[Tedad],    
	d.Mablagh [Mablagh],    
	0[MablaghBargashtBeAnbar],    

	m.SaatSabt[SaatSabt],    

	0.0 [MeghdarVaredeh],    
	cast(d.tedad as decimal(19,4)) [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],    

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

	0.0[MablaghJamVaredeh],    
	0.0[MablaghJamSadereh],
 	cast(0 as bit) [IsEntry],
    0 [BD_ID],
	ISNULL(m.Con_ID,0) [Con_ID],
	ISNULL(m.PP_ID,0) [PP_ID],
	ISNULL(m.PPI_ID,0) [PPI_ID],
	ISNULL(d.Booth_ID,0) [Booth_ID]
from Bargeh m     
 join BargehDetail d on m.ID= d.Bar_ID    
 join Kala k on d.Kal_ID = k.ID    
where    
	d.Dom_ID_Noe = 666 and     
	m.Dom_ID_Noe in (36) /*HavalehEnteghaliBeinAnbar*/ and    
	(cast(@codeKalaAz as int)='0' or k.Code >=cast(@codeKalaAz as int)) and    
	(cast(@codeKalaEla as int)='0' or k.Code <=cast(@codeKalaEla as int)) 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    
	(@Fc_ID=0 or m.Fc_ID = @Fc_ID) and  
	(@Pro_ID_VahedMali=0 or d.Pro_ID_VahedMali = @Pro_ID_VahedMali) and  
	(@tarikhAz=0 or m.tarikh>=@tarikhAz) and    
	(@tarikhEla=0 or m.tarikh<=@tarikhEla) and    
	(@TimeAz=0 or m.saat>=@TimeAz) and    
	(@TimeEla=0 or m.saat<=@TimeEla) and    
	(@factorCodeAz=0 or m.Code<=@factorCodeAz) and    
	(@factorCodeEla=0 or m.Code<=@factorCodeEla) and    
	(  
		@strType='' or (229 /*EnteghalBeynAnbarKhorooji*/ in (select * from dbo.CastStringToTable(@strType)))  
	)and  
	(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and  
	(    
		m.Dom_ID_vaziat not in    
		(    
			case @WithDeleteFactors    
			when 0 then 101 -- deleted    
			when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود    
			end    
		)    
	) and
	 (@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and  
	 (@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes)))    and
	(@strConIds = '' or m.Con_ID in (select * from dbo.CastStringToTable(@strConIds))) and
	(@strPpIds = '' or m.PP_ID in (select * from dbo.CastStringToTable(@strPpIds))) and
	(@strPpiIds = '' or m.PPI_ID in (select * from dbo.CastStringToTable(@strPpiIds))) and
	(@strBoothIds = '' or d.Booth_ID in (select * from dbo.CastStringToTable(@strBoothIds))) 
union all    
------------------------------------------------------------ حواله انتقالی بین انبار وارده    
select    
	0[Radif],    
	m.Fc_ID [FC_ID],  
	isnull(d.Pro_ID_VahedMaliMaghsad,0) [Pro_ID_VahedMali],  
	m.ID [ID],    
	m.Code [Bar_Code],    
	m.Tarikh[Tarikh],    
	m.Saat [Saat],    
	cast( 221 as smallint) /*EnteghalBeynAnbarVoroodi*/ [Noe],    
	cast( 0 as bigint)[Taf_ID],    
	0[Taf_Code],    
	0[Pro_ID_GroohAshkhas],    
	''[Taf_Name],    
	'حواله انتقالی بین انبار وارده (' + cast ( m.Code  as nvarchar )  + ')',    
	m.Kar_ID [Kar_ID],    
	m.Dom_ID_Vaziat [Vaziat],    
	isnull(d.Pro_ID_AnbarMaghsad , 0)[Anb_ID],    
	d.Kal_ID [Kal_ID],    
	k.Code [Kal_Code],    
	k.TechnicalCode [TechnicalCode],    
	k.Pro_ID_Vahed [Pro_ID_Unit],    
	k.Pro_ID_Grooh [Pro_ID_GroohKala],    
	k.name[Kal_Name],    
	d.Dom_ID_Restoration,
	isnull(d.Pro_ID_Color,0),
	isnull(d.Pro_ID_Size,0),
	d.Tedad[Tedad],    
	d.Mablagh [Mablagh],    
	0[MablaghBargashtBeAnbar],    

	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],    

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

	0.0[MablaghJamVaredeh],    
	0.0[MablaghJamSadereh],
	cast(1 as bit) [IsEntry],
	0 [BD_ID] ,
	ISNULL(m.Con_ID,0) [Con_ID],
	ISNULL(m.PP_ID,0) [PP_ID],
	ISNULL(m.PPI_ID,0) [PPI_ID],
	ISNULL(d.Booth_ID,0) [Booth_ID]
from Bargeh m     
 join BargehDetail d on m.ID= d.Bar_ID    
 join Kala k on d.Kal_ID = k.ID    
where    
 d.Dom_ID_Noe = 666 and m.Dom_ID_noe in (36) /*HavalehEnteghaliBeinAnbar*/ and    
 (cast(@codeKalaAz as int)='0' or k.Code >=cast(@codeKalaAz as int)) and    
 (cast(@codeKalaEla as int)='0' or k.Code <=cast(@codeKalaEla as int)) 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_AnbarMaghsad >=@AnbarAz) and    
 (@AnbarEla=0 or d.Pro_ID_AnbarMaghsad <=@AnbarEla) and    
 (@Fc_ID=0 or m.Fc_ID = @Fc_ID) and  
 (@Pro_ID_VahedMali=0 or d.Pro_ID_VahedMaliMaghsad = @Pro_ID_VahedMali) and  
 (@tarikhAz=0 or m.tarikh>=@tarikhAz) and    
 (@tarikhEla=0 or m.tarikh<=@tarikhEla) and    
 (@TimeAz=0 or m.saat>=@TimeAz) and    
 (@TimeEla=0 or m.saat<=@TimeEla) and    
 (@factorCodeAz=0 or m.Code<=@factorCodeAz) and    
 (@factorCodeEla=0 or m.Code<=@factorCodeEla) and    
 (  
  @strType='' or (221 /*EnteghalBeynAnbarVoroodi*/ in (select * from dbo.CastStringToTable(@strType)))  
 )and  
 (@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala))) and  
 (    
  m.Dom_ID_vaziat not in    
  (    
  case @WithDeleteFactors    
   when 0 then 101 -- deleted    
   when 1 then 2000 -- عدد خیلی بزرگ که هیچ وقت تورو نشود    
  end    
  )    
 ) and
 (@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and  
 (@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes)))  and
	(@strConIds = '' or m.Con_ID in (select * from dbo.CastStringToTable(@strConIds))) and
	(@strPpIds = '' or m.PP_ID in (select * from dbo.CastStringToTable(@strPpIds))) and
	(@strPpiIds = '' or m.PPI_ID in (select * from dbo.CastStringToTable(@strPpiIds))) and
	(@strBoothIds = '' or d.Booth_ID in (select * from dbo.CastStringToTable(@strBoothIds))) 
union all    
----------------------------------------------------     تولید فرمول متغیر
select     
 0[Radif],    
 d.Fc_ID [FC_ID],  
 d.Pro_ID_VahedMali [Pro_ID_VahedMali],  
 d.ID [ID],    
 m.Code [Bar_Code],    
 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)[Taf_ID],    
 cast( 0 as bigint)[Taf_Code],    
 0 [Pro_ID_GroohAshkhas],    
 '--' [Taf_Name],    
 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[Kar_ID],    
 cast(m.Dom_ID_Vaziat as smallint) [Vaziat],    
 isnull(d.Pro_ID_Anbar,0) [Anb_ID],    
 isnull(d.Kal_ID,0) [Kal_ID],    
 k.Code  [Kal_Code],    
 k.TechnicalCode [TechnicalCode],    
 k.Pro_ID_Vahed [Pro_ID_Unit],    
 k.Pro_ID_Grooh [Pro_ID_GroohKala],    
 k.name [Kal_Name],    
 d.Dom_ID_Restoration,
 isnull(d.Pro_ID_Color,0),
 isnull(d.Pro_ID_Size,0),
 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 d.Price -- 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.ID,k.Pro_ID_Vahed,d.tedad,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,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(d.Price,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 * d.Price as decimal(19,4) ) -- tolid    
 end [MablaghJam],    
 0.0[MablaghMiangin],    
 0.0[MablaghMandeh],    
    
 0.0[MablaghJamVaredeh],    
 0.0[MablaghJamSadereh],
   	 cast(case when d.Dom_ID_Noe  in (259,463) /*TolidZayeaat,tolid*/  then 1 else 0 end as bit) [IsEntry],
    0 [BD_ID] ,
     0 [Con_ID],
     0 [PP_ID],
     0 [PPI_ID],
     0 [Booth_ID]
from Product m     
 join ProductDetail d on m.ID = d.Prod_ID    
 join Kala k on d.Kal_ID = k.ID    
where    
	m.Dom_ID_Type = 1583 /*تولید متغیر*/ and
	m.Dom_ID_Vaziat != 101 and    
	(cast(@codeKalaAz as int) = '0' or k.Code >= cast(@codeKalaAz as int)) and    
	(cast(@codeKalaEla as int) = '0' or k.Code <= cast(@codeKalaEla as int)) 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    
	(@Fc_ID=0 or d.Fc_ID = @Fc_ID) and  
	(@Pro_ID_VahedMali=0 or d.Pro_ID_VahedMali = @Pro_ID_VahedMali) and  
	(@tarikhAz = 0 or d.tarikh >= @tarikhAz) and    
	(@tarikhEla = 0 or d.tarikh <= @tarikhEla)  and  
	(@TimeAz=0 or d.saat>=@TimeAz) and    
	(@TimeEla=0 or d.saat<=@TimeEla) and  
	(@factorCodeAz=0 or m.Code<=@factorCodeAz) and    
	(@factorCodeEla=0 or m.Code<=@factorCodeEla) and    
	(  
	@strType='' or   
	(  
	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 in (select * from dbo.CastStringToTable(@strType))  
	)  
	) and  
	(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala)))  and
	(@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and  
	(@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
	(@strConIds = '') and
	(@strPpIds = '') and
	(@strPpiIds = '') and
	(@strBoothIds = '')

union all   
----------------------------------------------------     تولید فرمول ثابت
select     
	0[Radif],    
	m.Fc_ID [FC_ID],  
	m.Pro_ID_VahedMali [Pro_ID_VahedMali],  
	case m.Dom_ID_Type when 1582 /*تولید ثابت*/ then m.ID when 1584 /*تولید ترکیبی*/ then isnull(m.Prod_ID,0) else 0 end [ID],    
	m.Code [Bar_Code],    
	m.tarikh[Tarikh],    
	m.Saat [Saat],    
	cast( case m.Dom_ID_Type when 1582 /*تولید ثابت*/ then 1587 when 1584 /*تولید ترکیبی*/ then 1589 else 0 end as smallint) /*تولید فرمول ثابت*/ [Noe],     
	cast( 0 as bigint)[Taf_ID],    
	cast( 0 as bigint)[Taf_Code],    
	0 [Pro_ID_GroohAshkhas],    
	'--' [Taf_Name],    
	case d.Dom_ID_Noe    
		when 259 then 'تولید ضایعات ('+ cast(m.code as nvarchar) + ') ' + case m.Dom_ID_Type when 1582 /*تولید ثابت*/ then 'فرمول ثابت' when 1584 /*تولید ترکیبی*/ then 'فرمول ترکیبی' else 'تولید ** ' end -- TolidZayeaat    
		when 260 then 'کالای مصرفی تولید ('+ cast(m.code as nvarchar) + ') ' + case m.Dom_ID_Type when 1582 /*تولید ثابت*/ then 'فرمول ثابت' when 1584 /*تولید ترکیبی*/ then 'فرمول ترکیبی' else 'تولید ** ' end -- TolidKalaMasrafi    
		when 463 then 'تولید ('+ cast(m.code as nvarchar) + ') ' + case m.Dom_ID_Type when 1582 /*تولید ثابت*/ then 'فرمول ثابت' when 1584 /*تولید ترکیبی*/ then 'فرمول ترکیبی' else 'تولید ** ' end 
	end [Sharh],    
	m.codeKarbar[Kar_ID],    
	cast(m.Dom_ID_Vaziat as smallint) [Vaziat],    
	isnull(d.Pro_ID_Anbar,0) [Anb_ID],    
	isnull(d.Kal_ID,0) [Kal_ID],    
	k.Code  [Kal_Code],    
	k.TechnicalCode [TechnicalCode],    
	k.Pro_ID_Vahed [Pro_ID_Unit],    
	k.Pro_ID_Grooh [Pro_ID_GroohKala],    
	k.name [Kal_Name],    
	d.Dom_ID_Restoration,
	isnull(d.Pro_ID_Color,0),
	isnull(d.Pro_ID_Size,0),
	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 d.Price -- 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.ID,k.Pro_ID_Vahed,d.tedad,k.Pro_ID_VahedMotevaset,k.tedadVahedMotevaset,k.Pro_ID_VahedBozorg,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(d.Price,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 * d.Price as decimal(19,4) ) -- tolid    
	end [MablaghJam],    
	0.0[MablaghMiangin],    
	0.0[MablaghMandeh],    

	0.0[MablaghJamVaredeh],    
	0.0[MablaghJamSadereh],
	cast(case when d.Dom_ID_Noe  in (259,463) /*TolidZayeaat,tolid*/  then 1 else 0 end as bit) [IsEntry],
	0 [BD_ID] ,
	0 [Con_ID],
	0 [PP_ID],
	0 [PPI_ID],
	0 [Booth_ID]
from Product m     
	join ProductDetail d on m.ID = d.Prod_ID    
	join Kala k on d.Kal_ID = k.ID    
where    
	m.Dom_ID_Type in (1582 /*تولید ثابت*/,1584 /*تولید ترکیبی*/) and
	m.Dom_ID_Vaziat != 101 and    
	(cast(@codeKalaAz as int) = '0' or k.Code >= cast(@codeKalaAz as int)) and    
	(cast(@codeKalaEla as int) = '0' or k.Code <= cast(@codeKalaEla as int)) 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    
	(@Fc_ID=0 or m.Fc_ID = @Fc_ID) and  
	(@Pro_ID_VahedMali=0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and  
	(@tarikhAz = 0 or m.tarikh >= @tarikhAz) and    
	(@tarikhEla = 0 or m.tarikh <= @tarikhEla)  and  
	(@TimeAz=0 or m.saat>=@TimeAz) and    
	(@TimeEla=0 or m.saat<=@TimeEla) and  
	(@factorCodeAz=0 or m.Code<=@factorCodeAz) and    
	(@factorCodeEla=0 or m.Code<=@factorCodeEla) and    
	(  
	@strType='' or   
	(  
		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 in (select * from dbo.CastStringToTable(@strType))  
	)  
	) and  
	(@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala)))  and
	(@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and  
	(@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
	(@strConIds = '') and
	(@strPpIds = '') and
	(@strPpiIds = '') and
	(@strBoothIds = '')

union all    
----------------------------------------------------    تبدیل کالا
    
select     
 0[Radif],    
 m.Fc_ID [FC_ID],  
 m.Pro_ID_VahedMali [Pro_ID_VahedMali],  
 m.ID [ID],    
 m.Code [Bar_Code],    
 m.tarikh[Tarikh],    
 m.Saat [Saat],    
 case d.Dom_ID_Noe    
  when 260 then cast( 228 as smallint) -- 260 کالاي مصرفي    
  when 463 then cast( 219 as smallint) -- 463 کالاي توليدي    
 end [Noe],     
 cast( 0 as bigint)[Taf_ID],    
 cast( 0 as bigint)[Taf_Code],    
 0 [Pro_ID_GroohAshkhas],    
 '--' [Taf_Name],    
 case d.Dom_ID_Noe    
  when 260 then 'کالای مصرفی تبدیل ('+ cast(m.code as nvarchar) + ')' --
  when 463 then 'تبدیل ('+ cast(m.code as nvarchar) + ')' --    
 end [Sharh],    
 m.Kar_ID_Creator [Kar_ID],    
 cast(m.Dom_ID_Vaziat as smallint) [Vaziat],    
 isnull(d.Pro_ID_Anbar,0) [Anb_ID],    
 isnull(d.Kal_ID,0) [Kal_ID],    
 k.Code  [Kal_Code],    
 k.TechnicalCode [TechnicalCode],    
 k.Pro_ID_Vahed [Pro_ID_Unit],    
 k.Pro_ID_Grooh [Pro_ID_GroohKala],    
 k.name [Kal_Name],    
 d.Dom_ID_Restoration,
 isnull(d.Pro_ID_Color,0),
 isnull(d.Pro_ID_Size,0),
 CAST(d.tedad as float) [Tedad],    
 case d.Dom_ID_Noe    
  when 260 then d.Price -- 260 کالاي مصرفي    
  when 463 then d.Price -- 463 کالاي توليدي    
 end [Mablagh],    
 0.0[MablaghBargashtBeAnbar], -- مخصوص برگشت از فروش    
    
 0[SaatSabt],    
    
 case d.Dom_ID_Noe    
  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 260 then cast(d.tedad as decimal(19,4))-- TolidKalaMasrafi    
  when 463 then 0.0 -- tolid    
 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],    
    
 case d.Dom_ID_Noe    
  when 260 then 0.0 -- KalaMasrafi    
  when 463 then isnull(d.Price,0.0) -- tolid    
 end [MablaghVaredeh],    
 case d.Dom_ID_Noe    
  when 260 then isnull(d.Price,0.0) -- KalaMasrafi    
  when 463 then 0.0 -- tolid    
 end [MablaghSadereh],    
 case d.Dom_ID_Noe    
  when 260 then cast(d.tedad * d.Price as decimal(19,4) ) -- کالا مصرفی
  when 463 then cast(d.tedad * d.Price as decimal(19,4) ) -- رسید تبدیل کالا
 end [MablaghJam],    
 0.0[MablaghMiangin],    
 0.0[MablaghMandeh],    
    
 0.0[MablaghJamVaredeh],    
 0.0[MablaghJamSadereh],
   	 cast(case when d.Dom_ID_Noe  in (463) /*رسید تبدیل*/  then 1 else 0 end as bit) [IsEntry],
    0 [BD_ID] ,
     0 [Con_ID],
     0 [PP_ID],
     0 [PPI_ID],
     0 [Booth_ID]
from ConvertGood m     
 join ConvertGoodDetail d on m.ID = d.CG_ID    
 join Kala k on d.Kal_ID = k.ID    
where    
 m.Dom_ID_Vaziat != 101 and    
 (cast(@codeKalaAz as int) = '0' or k.Code  >= cast(@codeKalaAz as int)) and    
 (cast(@codeKalaEla as int) = '0' or k.Code  <= cast(@codeKalaEla as int)) 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    
 (@Fc_ID=0 or m.Fc_ID = @Fc_ID) and  
 (@Pro_ID_VahedMali=0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and  
 (@tarikhAz = 0 or m.tarikh >= @tarikhAz) and    
 (@tarikhEla = 0 or m.tarikh <= @tarikhEla)  and  
 (@TimeAz=0 or m.saat>=@TimeAz) and    
 (@TimeEla=0 or m.saat<=@TimeEla) and  
 (@factorCodeAz=0 or m.Code<=@factorCodeAz) and    
 (@factorCodeEla=0 or m.Code<=@factorCodeEla) and    
 (  
  @strType='' or   
  (  
   case d.Dom_ID_Noe    
     when 260 then cast( 228 as smallint) -- تبديل کالاي مصرفي
     when 463 then cast( 219 as smallint) -- تبديل کالا
   end in (select * from dbo.CastStringToTable(@strType))  
 )  
 ) and  
 (@strKala='' or k.ID in (select * from dbo.CastStringToTable(@strKala)))  and
 (@strProIdColors='' or d.Pro_ID_Color in (select * from dbo.CastStringToTable(@strProIdColors))) and  
 (@strProIdSizes='' or d.Pro_ID_Size in (select * from dbo.CastStringToTable(@strProIdSizes))) and
	(@strConIds = '') and
	(@strPpIds = '') and
	(@strPpiIds = '') and
	(@strBoothIds = '')
----------------------------------------------------    
order by Code  , Noe , Tarikh    
end