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

CREATE PROCEDURE [dbo].[sp_LoadProfInfo]
 @x xml
 ,@FileID uniqueidentifier = null
 ,@Worker uniqueidentifier = null
 ,@PackageType varchar = null
AS 
BEGIN 

declare @_isMobBrigade bit = 
    case 
        when @PackageType = 'DISP_DIRECT_INFO_MB' then 1 
        else 0 
    end;

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('./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('/INFORMING_PM/ZGLV') T(c)   

 SELECT 
 NewId() as [ItemID]
  ,T.c.value('../ZGLV[1]/SMO[1]', 'varchar(5)') AS [SMO]
	
 ,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('./DR[1]', 'datetime') AS [DR]
 ,T.c.value('./IDDT[1]', 'varchar(3)') AS [IDDT]
 ,T.c.value('./YEAR[1]', 'int') AS [YEAR]
 ,T.c.value('./ENP[1]', 'varchar(16)') AS [ENP]
 ,T.c.value('./NPOLIS[1]', 'varchar(20)') AS [NPOLIS]
 ,T.c.value('./DATA_INF[1]', 'datetime') AS [DATA_INF]
 ,T.c.value('./F_INF[1]', 'int') AS [F_INF]
 ,T.c.value('./N_INF[1]', 'varchar(250)') AS [N_INF]
 ,T.c.value('./TIP_INFO[1]', 'int') AS [TIP_INFO]
 
 ,T.c.value('./V016[1]', 'varchar(36)') AS [V016]--Заглушка
 ,T.c.value('./INSURED[1]', 'varchar(36)') AS [INSURED]--Заглушка
 ,T.c.value('./DispDirect[1]', 'varchar(36)') AS [DispDirect]--Заглушка
 ,T.c.value('./DuplicateCount[1]', 'int') AS [DuplicateCount]--Заглушка
INTO #ZAP
FROM @x.nodes('/INFORMING_PM/ZL') T(c)   

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

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

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


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

--step 1
update t1
set t1.INSURED = (
  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
  join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID =
  (select top 1 ipr.InsuredPolicyRecordID from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateStop is null order by ipr.DateBegin desc)
  join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR and iszlpolicy.Number = t1.NPOLIS )   
 )
from #ZAP t1
where t1.INSURED is null
 
--step 2
update t1
set t1.INSURED =  (
  select top 1 InsuredID from [ISZL].T_INSURED t2
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR)   
 )
from #ZAP t1
where t1.INSURED is null 

--step 3
update t1
set t1.INSURED =  (
  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
  join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID =
  (select top 1 ipr.InsuredPolicyRecordID from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateStop is null order by ipr.DateBegin desc)
  join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and iszlpolicy.Number = t1.NPOLIS)   
 )
from #ZAP t1
where t1.INSURED is null 

--step 4
update t1
set t1.INSURED =  (
  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
  join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID =
  (select top 1 ipr.InsuredPolicyRecordID from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateStop is null order by ipr.DateBegin desc)
  join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
  where (t2.LastName = t1.FAM and t2.Birthday = t1.DR and iszlpolicy.Number = t1.NPOLIS)   
 )
from #ZAP t1
where t1.INSURED is null

insert into #Errors
select 2, 'Застрахованный не найден в БД. NPOLIS('+ t1.NPOLIS+')'
from #ZAP t1
where t1.INSURED is null


--step 0
update t1
set t1.[DispDirect] =  (
  select top 1 [DispDirectID] from iszl.T_DISP_DIRECT t2
	--left join iszl.T_DISP_DIRECT_FACT ddf on ddf.[DispDirect] = t2.[DispDirectID]
  where t2.Insured = t1.INSURED and t2.V016Dispt = t1.V016 and t2.[YEAR] = t1.[YEAR] 
  and t2.type_ = (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT')
	--and ddf.[DispDirectFactID] is null   
 )
from #ZAP t1
where t1.INSURED is not null and t1.V016 is not null

insert into #Errors
select 3, 'Запись ПМ не найдена в БД. NPOLIS('+ t1.NPOLIS+')'
from #ZAP t1
where t1.[DispDirect] is null

--insert into #Errors
--select 4, 'У ПМ есть факт прохождения ПМ. NPOLIS('+ t1.NPOLIS+')'
--from #ZAP t1
--join iszl.T_DISP_DIRECT t2 on t2.DispDirectID = t1.DispDirect
--left join iszl.T_DISP_DIRECT_FACT ddf on ddf.[DispDirect] = t2.[DispDirectID]
--where t1.[DispDirect] is not null and ddf.[DispDirectFactID] is not null

insert into #Errors (CODE, COMMENT)
select 4, 'Уже существует информирование с указанными параметрами. NPOLIS(' + t1.NPOLIS + ')'
from #ZAP t1
left join iszl.T_ORGANIZATION orgsmo on orgsmo.Code = t1.SMO
join iszl.T_DISP_DIRECT_INFO ddi on
	ddi.Insured = cast(t1.INSURED as uniqueidentifier) and
	ddi.V016Dispt = cast(t1.V016 as uniqueidentifier) and
	ddi.TIP_INFO = t1.TIP_INFO and
	ddi.Smo = orgsmo.OrganizationID and
	cast(cast(cast(ddi.[Date] as float) as int) as datetime) = cast(cast(cast(t1.DATA_INF as float) as int) as datetime)
where t1.INSURED is not null and ddi.Insured is not null

if (Select COUNT(*) from #Errors) > 0
BEGIN
  select cast(0 as bit),
	'<?xml version="1.0" encoding="Windows-1251"?>
	' + (
	select 
		CODE AS 'CODE',
		COMMENT AS 'COMMENT'
	FROM #Errors
	FOR XML PATH('ERR'),
	ROOT('ROOT')
	)
END

if ((select FileId from #ZGLV) is not null)
begin
	--------------------
	--Сheck File Exist--
	--------------------
	declare @InfoFile uniqueidentifier
			set @InfoFile = (
						select top 1 f.[FileID] 
						from [ISZL].T_DISP_DIRECT_INFO_FILE fi 
						join VCLib.T_FILE f on f.FileID = fi.FileID
						where f.[Name] = (select  z.[FILENAME] from #ZGLV z)
					)

			if @InfoFile is not null
			begin 
				exec [dbo].sp_DispInfoDel @InfoFile
			end

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

			insert into [ISZL].[T_DISP_DIRECT_INFO_FILE] ([FileID],[VERSION],ReceivedDate,ReceivedTime,Worker)
			select FileId, [VERSION],GetDate(),GetDate(),@Worker
			from #ZGLV 
		end
	else
		begin
			DELETE FROM [ISZL].T_DISP_DIRECT_INFO
			WHERE [FileName] = (select [FILENAME] from #ZGLV)
		end

INSERT INTO iszl.T_DISP_DIRECT_INFO
	 (
      DispDirectInfoID
      ,[Date]
      ,DispDirect
      ,DispDirectInfoType
      ,DispDirectInfoFile
      ,Addr
      ,Result
      ,ENP
      ,V016Dispt
      ,TIP_INFO
      ,Insured
      ,Mo
      ,Smo
      ,CreationDate
	 ,[FileName]
	 ,IsMobileFlag
	 )
	select zap.[ItemID]
		,zap.[DATA_INF]
		,zap.[DispDirect]
		,zap.[F_INF]
		,(select top 1 [FileID] from #ZGLV)
		,zap.[N_INF]
		,null
		,zap.[ENP]
		,zap.V016
		,zap.[TIP_INFO]
		,zap.INSURED
		,null
		,orgsmo.OrganizationID
		,getdate()
		,(select top 1 [FILENAME] from #ZGLV)
		, @_isMobBrigade
	from #ZAP zap
	left join [ISZL].T_ORGANIZATION orgsmo on orgsmo.Code = zap.SMO
	where
    zap.INSURED IS NOT NULL
  and
    zap.V016 IS NOT NULL
  and
    zap.[DispDirect] IS NOT NULL;
DROP TABLE #ZAP
DROP TABLE #ZGLV
END