if exists ( select * from sys.procedures p where p.name = 'sp_LoadProfList' ) DROP PROCEDURE [dbo].[sp_LoadProfList]
GO
-- =============================================
-- Author:  (C) Vitacard System 2019
-- Create date:
-- =============================================

CREATE PROCEDURE [dbo].[sp_LoadProfList] @x xml
 ,@FileID uniqueidentifier = null
 ,@Worker uniqueidentifier = null
AS 
BEGIN 
 
SELECT 
  @FileID AS [FileId]
  ,T.c.value('./VERSION[1]', 'varchar(5)') AS [VERSION]
  ,T.c.value('./DATA[1]', 'datetime') AS [DATA]
  ,T.c.value('./FILENAME[1]', 'varchar(50)') AS [FILENAME]
  ,T.c.value('./MCOD[1]', 'varchar(6)') AS [MCOD]
  ,T.c.value('./SMO[1]', 'varchar(5)') AS [SMO]
  , CAST(CAST(CAST(GETDATE() as float) as INT) as datetime) as [ReceivedDate]
  , GETDATE() as [ReceivedTime]
INTO #ZGLV
FROM @x.nodes('/PROF_LIST/ZGLV') T(c)   

 SELECT 
 NewId() as [ItemID]
 ,T.c.value('./ID_PAC[1]', 'varchar(36)') AS [ID_PAC]
 ,T.c.value('./FAM[1]', 'varchar(40)') AS [FAM]
 ,T.c.value('./IM[1]', 'varchar(40)') AS [IM]
 ,T.c.value('./OT[1]', 'varchar(40)') AS [OT]
 ,T.c.value('./W[1]', 'int') AS [W]
 ,T.c.value('./DR[1]', 'datetime') AS [DR]
 ,T.c.value('./ENP[1]', 'varchar(16)') AS [ENP]
 ,T.c.value('./VPOLIS[1]', 'int') AS [VPOLIS]
 ,T.c.value('./SPOLIS[1]', 'varchar(10)') AS [SPOLIS]
 ,T.c.value('./NPOLIS[1]', 'varchar(20)') AS [NPOLIS]
 ,T.c.value('./SNILS[1]', 'varchar(14)') AS [SNILS]

 ,T.c.value('./PROF[1]/MONTH[1]', 'int') AS [MONTH]
 ,T.c.value('./PROF[1]/YEAR[1]', 'int') AS [YEAR]
 ,T.c.value('./PROF[1]/TYPE[1]', 'int') AS [TYPE]
 ,T.c.value('./PROF[1]/IDDT[1]', 'varchar(3)') AS [IDDT]
 ,T.c.value('../ZGLV[1]/DATA[1]', 'datetime') AS [DATA]
 ,T.c.value('../ZGLV[1]/SMO[1]', 'varchar(5)') AS [SMO]
 ,T.c.value('../ZGLV[1]/MCOD[1]', 'varchar(6)') AS [CODE_MO]
 ,T.c.value('./V016[1]', 'varchar(36)') AS [V016]--Заглушка
 ,T.c.value('./InsuredPolicyRecordID[1]', 'varchar(36)') AS [InsuredPolicyRecordID] -- заглушка
 ,T.c.value('./DispDirectID[1]', 'uniqueidentifier') AS DispDirectID -- заглушка
 ,T.c.value('./DuplicateCount[1]', 'int') AS DuplicateCount -- заглушка
INTO #PERS
FROM @x.nodes('/PROF_LIST/ZL') T(c)   

SELECT 
	T.c.value('../ID_PAC[1]', 'varchar(36)') AS ID_PAC
	,T.c.value('../ENP[1]', 'varchar(16)') AS [ENP]
	,T.c.value('./CONTACT[1]', 'varchar(255)') AS [CONTACT]
	,T.c.value('./TYPE[1]', 'int') AS [TYPE]

INTO #CONTACTS
FROM @x.nodes('/PERS_LIST/ZL/CONTACTS') T(c)  

update p
set p.V016 = v016.V016ID
from #PERS p
join iszl.T_V016_DISPT v016 on v016.IDDT = p.[IDDT] and v016.DATEEND is null

create table #Errors
(
	CODE int,
	COMMENT varchar(250),
	ID_PAC varchar(36)
)

--проверка что всё заполнено!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

insert into #Errors
select 1, 'Значение поля IDDT не содержится в справочнике V016 ID_PAC('+ t1.ID_PAC+') NPOLIS('+ t1.NPOLIS+')', t1.ID_PAC
from #PERS t1
where t1.V016 is null

insert into #Errors
select 4, 'Нельзя добавить запись за указанный период (см.месяц) ID_PAC('+ p.ID_PAC+') NPOLIS('+ p.NPOLIS+')', p.ID_PAC -- 2 уже занято, ниже
from #PERS p
where not(
        DATEPART(YEAR, DATEADD(MONTH, 1, p.DATA)) < p.YEAR
            or
        DATEPART(YEAR, DATEADD(MONTH, 1, p.DATA)) = p.YEAR and (
            DATEPART(MONTH, DATEADD(MONTH, 1, p.DATA)) < p.MONTH
                or
            DATEPART(MONTH, p.DATA) = p.MONTH AND p.MONTH = 1
                or
            DATEPART(MONTH, DATEADD(MONTH, 1, p.DATA)) = p.MONTH AND DATEPART(DAY, p.DATA) <= 15
        )
    )

--step 0
	--update t1
	--set t1.[InsuredPolicyRecordID] = (
	--  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
	--  where (t2.ID_PAC = t1.ID_PAC or t2.ENP = t1.ENP)   
	-- )
	--from #ZL t1
	--where t1.INSURED is null

	--0. Ищем полис по номеру полиса. Не важно актуальный он или нет
	--step 0.
	update t1
	set t1.[InsuredPolicyRecordID] = (
	  select top 1 InsuredPolicyRecordID 
	  from [ISZL].T_INSURED_POLICY_RECORD t2
		join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Patronymic = t1.OT and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or p.Number = t1.ENP) and t2.DateStop is null)
	  order by t2.DateBegin desc     
	 )
	from #PERS t1
	where t1.[InsuredPolicyRecordID] is null and (Select COUNT(*) from #Errors t100 where t100.id_pac = t1.ID_PAC) = 0
	--step 1. без отчества
	update t1
	set t1.[InsuredPolicyRecordID] = (
	  select top 1 InsuredPolicyRecordID 
	  from [ISZL].T_INSURED_POLICY_RECORD t2
		join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM  and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or p.Number = t1.ENP) and t2.DateStop is null)
	  order by t2.DateBegin desc
	  )
	from #PERS t1
	where t1.[InsuredPolicyRecordID] is null  and (Select COUNT(*) from #Errors t100 where t100.id_pac = t1.ID_PAC) = 0
	--step 2. без отчества и ДР
	update t1
	set t1.[InsuredPolicyRecordID] = (
	  select top 1 InsuredPolicyRecordID 
	  from [ISZL].T_INSURED_POLICY_RECORD t2
		join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and (p.Number = t1.NPOLIS or p.Number = t1.ENP) and t2.DateStop is null)
	  order by t2.DateBegin desc   
	  )
	from #PERS t1
	where t1.[InsuredPolicyRecordID] is null  and (Select COUNT(*) from #Errors t100 where t100.id_pac = t1.ID_PAC) = 0
	--step 3. без отчества и Фамилии
	update t1
	set t1.[InsuredPolicyRecordID] = (
	  select top 1 InsuredPolicyRecordID 
	  from [ISZL].T_INSURED_POLICY_RECORD t2
		join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	  where (t2.FirstName = t1.IM and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or p.Number = t1.ENP) and t2.DateStop is null)
	  order by t2.DateBegin desc    
	 )
	from #PERS t1
	where t1.[InsuredPolicyRecordID] is null  and (Select COUNT(*) from #Errors t100 where t100.id_pac = t1.ID_PAC) = 0

   --step 4. без имени
	update t1
	set t1.[InsuredPolicyRecordID] = (
	  select top 1 InsuredPolicyRecordID 
	  from [ISZL].T_INSURED_POLICY_RECORD t2
		join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	  where (t2.LastName = t1.FAM and t2.Patronymic = t1.OT and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or p.Number = t1.ENP) and t2.DateStop is null)
	  order by t2.DateBegin desc    
	 )
	from #PERS t1
	where t1.[InsuredPolicyRecordID] is null  and (Select COUNT(*) from #Errors t100 where t100.id_pac = t1.ID_PAC) = 0
	
	insert into #Errors
	select 2, 'Не найден полис из учета полисов застрахованного ID_PAC('+ t1.ID_PAC+') NPOLIS('+ t1.NPOLIS+')', t1.ID_PAC
	from #PERS t1
	where t1.[InsuredPolicyRecordID] is null

	-- проверка на дубликат
	insert into #Errors (CODE, COMMENT, ID_PAC)
	select 3, 'Уже существует направление на проф. мероприятие с указанными параметрами. NPOLIS(' + p.NPOLIS + ')', p.ID_PAC
	from #PERS p
	join iszl.T_F003 mo on mo.MCOD = p.CODE_MO
	join iszl.T_DISP_DIRECT dd
	on (dd.FAM = p.FAM
		and dd.IM = p.IM
		and dd.OT = p.OT
		and dd.DR = p.DR)
	--and dd.V016Dispt = cast(p.V016 as uniqueidentifier)
	and dd.[YEAR] = p.[YEAR]
	and dd.MONTH_Q = p.[MONTH]
	where dd.type_ = (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT')


if (Select COUNT(*) from #Errors) = 0
BEGIN

	if ((select FileId from #ZGLV) is not null)
		begin
			--------------------
			--Сheck File Exist--
			--------------------
			declare @ExistFile uniqueidentifier
			set @ExistFile = (
						select top 1 dd.[FileID] 
						from [ISZL].[T_DISP_DIRECT_FILE] dd 
						where dd.[FILENAME] = (select  z.[FILENAME] from #ZGLV z)
					)

			if @ExistFile is not null
			begin 
				exec [dbo].[sp_Prof_Delete] @ExistFile
			end

			insert into VCLib.T_FILE ([FileID],[Name],[CreateDateTime],[FileNameForm],[SaveFileLocation],[UseOle],[type_])
			select z.FileId, z.[FILENAME],GetDate(), 1, 1, 0, (select EntityID from VCLib.T_ENTITIES where EntName = 'DISP_DIRECT_FILE') 
			from #ZGLV z

			insert into [ISZL].[T_DISP_DIRECT_FILE] ([FileID],[VERSION],[DATA],[FILENAME],[ReceivedDate],[RecievedTime],[YEAR],[MONTH],[CODE_MO],[Worker],[ID_MO])
			select z.FileId, [VERSION], [DATA], z.[FILENAME] ,GetDate(),GetDate(),null,null,[MCOD],@Worker,null
			from #ZGLV z
		end
	else
		begin
			--DELETE FROM [ISZL].T_DISP_DIRECT
			--WHERE [FileName] = (select [FILENAME] from #ZGLV)

			update t1
			set t1.DispDirectID = (
					select top 1 t2.DispDirectID  
					from [ISZL].T_DISP_DIRECT t2
					where (t2.YEAR = t1.YEAR and t2.[V016Dispt] = t1.V016 and t2.Insured = ipr.Insured)   
				)
			from #PERS t1
			join iszl.T_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = t1.[InsuredPolicyRecordID]
			where (Select COUNT(*) from #Errors) = 0
		end
	
	------------------
	--Добавляем только новых в iszl.T_DISP_DIRECT из #PERS  p.[TYPE] = 0
	 INSERT INTO iszl.T_DISP_DIRECT
	 (
		  [DispDirectID]
		  ,[DispDirectFile]
		  ,[ID_PAC]
		  ,[FAM]
		  ,[IM]
		  ,[OT]
		  ,[Sex]
		  ,[DR]
		  ,[ENP]
		  ,[VPOLIS]
		  ,[SPOLIS]
		  ,[NPOLIS]
		  ,[SNILS_P]
		  ,[YEAR]
		  ,[V016Dispt]
		  ,[MONTH_Q]
		  ,Smo--[CODE_SMO]
		  ,Mo--[CODE_MO]
		  ,[Insured]
		  ,[InsuredPolicyRecord]
		  ,[FileName]
	 )
	 select p.[ItemID], (select FileId from #ZGLV)
			, p.[ID_PAC]
			, p.[FAM],p.[IM],p.[OT], p.[W] ,p.[DR], p.[ENP], p.[VPOLIS]
					,p.[SPOLIS],p.[NPOLIS], p.[SNILS]
					,p.[YEAR] 
					,p.V016 ,p.[MONTH]
  					,orgsmo.OrganizationID, orgmo.OrganizationID
					,ipr.Insured
					,p.[InsuredPolicyRecordID],(select [FILENAME] from #ZGLV)
	 from #PERS p
	 join iszl.T_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = p.[InsuredPolicyRecordID]
	 left join [ISZL].T_ORGANIZATION orgsmo on orgsmo.Code = p.SMO
	 left join [ISZL].T_ORGANIZATION orgmo on orgmo.Code = p.[CODE_MO]
	where p.[TYPE] = 0 and p.DispDirectID is null
	
	--обновляем данные где type = 1
	 update dd
	 set 
		dd.[DispDirectFile] = (select FileId from #ZGLV)
		,dd.[FAM] = p.[FAM]
		,dd.[IM] = p.[IM]
		,dd.[OT] = p.[OT]
		,dd.[Sex] = p.[W]
		,dd.[DR] = p.[DR]
		,dd.[ENP] = p.[ENP]
		,dd.[VPOLIS] = p.[VPOLIS]
		,dd.[SPOLIS] = p.[SPOLIS]
		,dd.[NPOLIS] = p.[NPOLIS]
		,dd.[SNILS_P] = p.[SNILS]
		,dd.Smo = orgsmo.OrganizationID
		,dd.Mo = orgmo.OrganizationID
		,dd.[V016Dispt] = p.V016
		,dd.[MONTH_Q] = p.[MONTH]
	 from  iszl.T_DISP_DIRECT dd
		join #PERS p on dd.[ENP] = p.[ENP] 
		left join iszl.T_DISP_DIRECT_FACT fact on fact.DispDirect = dd.DispDirectID
		 left join [ISZL].T_ORGANIZATION orgsmo on orgsmo.Code = p.SMO
		 left join [ISZL].T_ORGANIZATION orgmo on orgmo.Code = p.[CODE_MO]
	 where dd.[YEAR] = p.[YEAR] and dd.V016Dispt = p.V016 and fact.DispDirectFactID is null
	 and p.[TYPE] = 1 and dd.DispDirectID = p.DispDirectID
	 and dd.type_ = (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT')
	------------------
	--Удаляем из iszl.T_DISP_DIRECT 

	delete dd
	from iszl.T_DISP_DIRECT dd
	join #PERS p on dd.[ENP] = p.[ENP] and dd.[YEAR] = p.[YEAR]
	where dd.[DispDirectFile]=(select FileId from #ZGLV) and p.[TYPE] = 2 and dd.[V016Dispt] = p.V016 and dd.DispDirectID = p.DispDirectID
	and dd.type_ = (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT')

	--------------------
	INSERT INTO iszl.T_INSURED_CONTACT
	(
		 InsuredContactID
		 ,Contact
		 ,ContactType
		 ,Insured
	)
	select
		newid()
		,con.[CONTACT]
		,(select ContactTypeID from iszl.T_CONTACT_TYPE where Name = con.[TYPE])
		,ipr.Insured
	from #CONTACTS con
	join #PERS p on con.ID_PAC = p.ID_PAC
	join iszl.T_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = p.[InsuredPolicyRecordID]
	where  p.[TYPE] = 0 and not exists (select top 1 * from iszl.T_INSURED_CONTACT c where c.Contact = con.[CONTACT] and c.Insured = ipr.Insured) and p.DispDirectID is null

END


	select (case 
	   when (select COUNT(*) from #Errors) = 0 
	   then cast(1 as bit)
	   else case
			when (select COUNT(*) from #Errors) > 0
			then cast(0 as bit)
			end
	   end),
	   '<?xml version="1.0" encoding="Windows-1251"?>' 
		+ 
		(
			SELECT 
				CODE as 'CODE'
				,COMMENT as 'COMMENT'
			FROM #Errors 
			FOR XML PATH('ERR'),ROOT('ROOT')
		)
		
DROP TABLE #PERS
DROP TABLE #ZGLV
DROP TABLE #CONTACTS
END

