--prcFreeGoodsForKartexAnbar 31,14000815,85
    --85,4216,4217
alter proc prcFreeGoodsForKartexAnbar
--declare
 @Fc_ID int=31,  
 @ToDate int=14000813,    
 @ProIdAnbar int=85
as    
begin    

declare @tbl as table(
	FC_ID	int not null,
	ID	int not null,
	Tarikh	int not null,
	Saat	int not null,
	Dom_ID_Noe	int not null,
	Kal_ID	int not null,
	Pro_ID_Unit	int not null,
	Dom_ID_Restoration	int not null,
	Quantity	float not null,
	Price	decimal not null,
	IsEntry	bit not null
)

insert into @tbl
----------------------------------- موجودی اول دوره    
select    
	d.Fc_ID [FC_ID],
	d.ID[ID],    
	d.tarikh[Tarikh],    
	0[Saat],    
	cast( 213 as int)Dom_ID_Noe,
	d.Kal_ID [Kal_ID],    
	--k.Code [Kal_Code],    
	d.Pro_ID_Unit [Pro_ID_Unit],    
	--pUnit.Name [Pro_Title_Unit],    
	--k.name[Kal_Title],    
	d.Dom_ID_RestorationCode [Dom_ID_Restoration],
	d.MojoodiAvalDoreh Quantity,    
	d.GheymatAvalDoreh Price,    
	cast(1 as bit) [IsEntry]
from    
KalaDarAnbar d 
	join Kala k on d.Kal_ID = k.ID    
	join OtherProperties pUnit on d.Pro_ID_Unit = pUnit.ID    
	left join CommercialStoreItemRefrenceID r on d.ID = r.KDA_ID    
where    
	d.Fc_ID = @Fc_ID and  
	d.Pro_ID_Anbar = @ProIdAnbar and  
	d.Tarikh <= @ToDate and
	r.iD is null
    
union all    
------------------------------------------------------------ برگه    
select    
    m.Fc_ID [FC_ID],  
    d.ID[ID] ,    
    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) -- فاکتور مانده پروژه  
		else 0
	end Dom_ID_Noe,    
    d.Kal_ID [Kal_ID],    
	--k.Code  [Kal_Code], 
	d.Pro_ID_Unit [Pro_ID_Unit],       
	--pUnit.Name,    
	--k.name[Kal_Title],    
	d.Dom_ID_Restoration,
    d.Tedad ,    
    d.mablagh + isnull(d.MablaghSupplement,0.0) ,    
	cast(case when m.Dom_ID_noe in (29,31,791,1162/*TrustedGoodsPurchase*/) then 1 else 0 end as bit) [IsEntry]
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
	join OtherProperties pUnit on d.Pro_ID_Unit = pUnit.ID    
	left join CommercialStoreItemRefrenceID r on d.ID = r.BD_ID
where    
	d.Dom_ID_Noe = 666 and m.Dom_ID_noe not in (30 /*PishFactorForoosh*/,33 /*PishFactorKharid*/,1158 /*TrustedGoodsDaryafti*/,1159/*TrustedGoodsPardakhti*/) and    
	m.Fc_ID = @Fc_ID and  
	d.Pro_ID_Anbar = @ProIdAnbar and  
	m.Tarikh <= @ToDate     and
	r.iD is null
    
union all    
------------------------------------------------------------ حواله انتقالی بین انبار صادره    
select    
	m.Fc_ID [FC_ID],  
	d.ID [ID],    
	m.Tarikh[Tarikh],    
	m.Saat [Saat],    
	cast( 229 as smallint) /*EnteghalBeynAnbarKhorooji*/ Dom_ID_Noe,    

	d.Kal_ID [Kal_ID],    
	--k.Code [Kal_Code],    
	d.Pro_ID_Unit [Pro_ID_Unit],    
	--pUnit.Name,    
	--k.name[Kal_Title], 
	d.Dom_ID_Restoration,   
	d.Tedad ,    
	d.Mablagh ,    

	cast(0 as bit) [IsEntry]
from Bargeh m     
	join BargehDetail d on m.ID= d.Bar_ID    
	join Kala k on d.Kal_ID = k.ID   
	join OtherProperties pUnit on d.Pro_ID_Unit = pUnit.ID    
	left join CommercialStoreItemRefrenceID r on d.ID = r.BD_ID 
where    
	d.Dom_ID_Noe = 666 and     
	m.Dom_ID_Noe in (36) /*HavalehEnteghaliBeinAnbar*/ and    
	m.Fc_ID = @Fc_ID and  
	d.Pro_ID_Anbar = @ProIdAnbar and  
	m.Tarikh <= @ToDate  and
	--r.iD is null
	r.ID not in 
	(
	select ISNULL(r.BD_ID,0)
	from CommercialStore sd
		inner join CommercialStoreItem sdi on sd.ID = sdi.CS_ID
	where
		sd.FC_ID = 31 and
		sd.Pro_ID_Anbar = 4246 and
		sdi.Dom_ID_DocType = 229/*انتقال بین انبار خروجی*/
	)
union all    
------------------------------------------------------------ حواله انتقالی بین انبار وارده    
select    
	m.Fc_ID [FC_ID],  
	d.ID [ID],    
	m.Tarikh[Tarikh],    
	m.Saat [Saat],    
	cast( 221 as smallint) /*EnteghalBeynAnbarVoroodi*/ Dom_ID_Noe,    
	d.Kal_ID [Kal_ID],    
	--k.Code [Kal_Code],    
	d.Pro_ID_Unit [Pro_ID_Unit],  
	--pUnit.Name,    
	--k.name[Kal_Title],    
	d.Dom_ID_Restoration,
	d.Tedad ,    
	d.Mablagh ,    

	cast(1 as bit) [IsEntry]
from Bargeh m     
	join BargehDetail d on m.ID= d.Bar_ID    
	join Kala k on d.Kal_ID = k.ID    
	join OtherProperties pUnit on d.Pro_ID_Unit = pUnit.ID    
	left join CommercialStoreItemRefrenceID r on d.ID = r.BD_ID
where    
	d.Dom_ID_Noe = 666 and m.Dom_ID_noe in (36) /*HavalehEnteghaliBeinAnbar*/ and    
	m.Fc_ID = @Fc_ID and  
	d.Pro_ID_AnbarMaghsad = @ProIdAnbar and  
	m.Tarikh <= @ToDate  and
	r.ID not in 
	(
	select ISNULL(r.BD_ID,0)
	from CommercialStore sd
		inner join CommercialStoreItem sdi on sd.ID = sdi.CS_ID
	where
		sd.FC_ID = 31 and
		sd.Pro_ID_Anbar = 4246 and
		sdi.Dom_ID_DocType = 221/*انتقال بین انبار ورودی*/
	)
 
union all    
----------------------------------------------------     تولید
    
select     
 d.Fc_ID [FC_ID],  
 d.ID [ID],    
 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 Dom_ID_Noe,     
 isnull(d.Kal_ID,0) [Kal_ID],    
 --k.Code  [Kal_Code],    
 k.Pro_ID_Vahed [Pro_ID_Unit],    
 --pUnit.Name,    
 --k.name [Kal_Title],    
 d.Dom_ID_Restoration,
 CAST(d.tedad as float) ,    
 case d.Dom_ID_Noe    
  when 259 then d.Price -- 259 ضايعات    
  when 260 then d.Price -- 260 کالاي مصرفي    
  when 463 then d.Price -- 463 کالاي توليدي    
 end ,    
   	 cast(case when d.Dom_ID_Noe  in (259,463) /*TolidZayeaat,tolid*/  then 1 else 0 end as bit) [IsEntry]
from Product m     
 join ProductDetail d on m.ID = d.Prod_ID    
 join Kala k on d.Kal_ID = k.ID    
 join OtherProperties pUnit on k.Pro_ID_Vahed = pUnit.ID    
 left join CommercialStoreItemRefrenceID r on d.ID = r.ProductDetail_ID
where    
 --m.Dom_ID_Vaziat != 101 /*فقط خواندني*/ and -- فقط قیمت دار شده ها بیاید
 m.Dom_ID_Vaziat = 104 /*فقط خواندني*/ and -- فقط قیمت دار شده ها بیاید
    m.Fc_ID = @Fc_ID and  
 d.Pro_ID_Anbar = @ProIdAnbar and  
 d.Tarikh <= @ToDate and
 r.iD is null
 
union all    
----------------------------------------------------    تبدیل کالا
    
select     
 m.Fc_ID [FC_ID],  
 d.ID [ID],    
 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 Dom_ID_Noe,     
 isnull(d.Kal_ID,0) [Kal_ID],    
 --k.Code  [Kal_Code],    
 d.Pro_ID_Unit [Pro_ID_Unit],    
 --pUnit.Name,    
 --k.name [Kal_Title],    
 d.Dom_ID_Restoration,
 CAST(d.tedad as float) ,    
 case d.Dom_ID_Noe    
  when 260 then d.Price -- 260 کالاي مصرفي    
  when 463 then d.Price -- 463 کالاي توليدي    
 end ,    
   	 cast(case when d.Dom_ID_Noe  in (463) /*رسید تبدیل*/  then 1 else 0 end as bit) [IsEntry]
from ConvertGood m     
 join ConvertGoodDetail d on m.ID = d.CG_ID    
 join Kala k on d.Kal_ID = k.ID    
 join OtherProperties pUnit on d.Pro_ID_Unit = pUnit.ID    
 left join CommercialStoreItemRefrenceID r on d.ID = r.ConvertGoodDetail_ID
where    
 m.Dom_ID_Vaziat != 101 and    
    m.Fc_ID = @Fc_ID and  
 d.Pro_ID_Anbar = @ProIdAnbar and  
 m.Tarikh <= @ToDate  and
 r.iD is null
----------------------------------------------------    

select 
	CAST(1 as bit) Selected,
	t.* ,
	k.Code Kal_Code,    
	k.name Kal_Title,
	pUnit.Name Pro_Title_Unit,
	dom.title Dom_Title_Noe,
	domRestoration.Title Dom_Title_Restoration
from @tbl t 
	join Kala k on t.Kal_ID = k.ID    
	join OtherProperties pUnit on t.Pro_ID_Unit = pUnit.ID    
	join Domain dom on t.Dom_ID_Noe = dom.ID    
	join Domain domRestoration on t.Dom_ID_Restoration = domRestoration.ID    
order by t.Dom_ID_Noe , t.Tarikh    
end