--prcPersonCardMaster 0 , 13970101
alter proc prcPersonCardMaster (@Fc_ID int ,@Pro_ID_VahedMali int ,@TarikhAz int=0, @TarikhEla int=0)  as
begin
	begin try
		drop table #tmp_prcPersonCardMaster
	end try
	begin catch
	end catch
	
	select
	t.ID,
	t.Code,
	t.Title,
	cast(t.Dom_ID_Noe as int) Dom_ID_Noe,
	0 Pro_ID,
	cast(isnull(sum(f.bed),0.0) as decimal(19,4)) [Bedehkar],
	cast(isnull(sum(f.bes),0.0)as decimal(19,4))[Bestankar],
	cast(0 as decimal(19,4))[Mandeh],
	'' [Mahiat],
	cast('' as nvarchar(max)) [Tel1],
	cast('' as nvarchar(max)) [Tel2],
	cast('' as nvarchar(max)) [Mobile],
	cast('' as nvarchar(max)) [Adress],
	cast('' as nvarchar(max)) [AdressHome],
	cast(0 as bit) [IsTakhfifEzafe],
	0 Dom_ID_Active,
	TarikhAkharinFaaliat[TarikhAkharinFaaliat],
	cast(0 as bit) [ShowYadAvari],
	0 [TedadRoozYadAvari],
	cast('' as nvarchar(max)) [MahiatYadAvari],
	cast('' as nvarchar(max)) [Description],
	cast('' as nvarchar(max)) FatherName , 
	cast('' as nvarchar(max)) CertificateNo , 
	cast('' as nvarchar(max)) NationalCode
into #tmp_prcPersonCardMaster
from tafsili t 
	join dbo.fAllCodeBedBes(@Fc_ID ,@Pro_ID_VahedMali ,@TarikhAz , @TarikhEla) f on t.ID=f.ID
group by 
	t.ID,
	code ,
	Title,
	Dom_ID_noe,
	TarikhAkharinFaaliat
order by Dom_ID_noe, code 

---------------------------------------------------------------- people
update t set
	t.Pro_ID = peo.Pro_ID,
	t.Tel1 = peo.Tel1,
	t.Tel2 = peo.Tel2,
	t.Mobile = peo.Mobile,
	t.Adress = peo.AddressJob,
	t.AdressHome = peo.AddressHome,
	t.Dom_ID_Active = peo.Dom_ID_Active,
	t.[Description] = peo.[Description],
	t.FatherName = peo.FatherName , 
	t.CertificateNo = peo.CertificateNo, 
	t.NationalCode = peo.NationalCode
from #tmp_prcPersonCardMaster t
	inner join People peo on t.ID = peo.Taf_ID
---------------------------------------------------------------- bank
update t set
	t.Tel1 = ba.Tel,
	t.Dom_ID_Active = ba.Dom_ID_Status,
	t.[Description] = ba.[Description]
from #tmp_prcPersonCardMaster t
	inner join BankAccount ba on t.ID = ba.Taf_ID
---------------------------------------------------------------- sandoogh
update t set
	t.Dom_ID_Active = p.Dom_ID_Status
from #tmp_prcPersonCardMaster t
	inner join OtherProperties p on t.ID = p.Taf_ID and t.Dom_ID_noe = 51 -- sandoogh
---------------------------------------------------------------- hazineh
update t set
	t.Pro_ID = p.pro_ID_Parent,
	t.Dom_ID_Active = p.Dom_ID_Status,
	t.IsTakhfifEzafe = isnull(p.flag,0)
from #tmp_prcPersonCardMaster t
	inner join OtherProperties p on t.ID = p.Taf_ID and t.Dom_ID_noe = 48 -- hazineh
---------------------------------------------------------------- khadamat
update t set
	t.Pro_ID = p.pro_ID_Parent,
	t.Dom_ID_Active = p.Dom_ID_Status,
	t.IsTakhfifEzafe = isnull(p.flag,0)
from #tmp_prcPersonCardMaster t
	inner join OtherProperties p on t.ID = p.Taf_ID and t.Dom_ID_noe = 52 -- khadamat
	
	
select * from #tmp_prcPersonCardMaster
end