if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ProfInterviewLoad_karelia]') and xtype = N'P')
drop procedure [dbo].[sp_ProfInterviewLoad_karelia]
GO
CREATE procedure [dbo].[sp_ProfInterviewLoad_karelia] 
        @Worker varchar(36) = null,
        @QueueName varchar(36) = null,
        @MsgId varchar(36) = null,
        @FileID uniqueidentifier,
        @x xml
AS 
BEGIN 
 
SELECT 
       T.c.value('./VERSION[1]', 'varchar(5)') AS [VERSION]
      ,T.c.value('./DATA[1]', 'datetime') AS [DATA]
      ,T.c.value('./FILENAME[1]', 'varchar(26)') AS [FILENAME]
      ,T.c.value('./YEAR[1]', 'int') AS [YEAR]
      ,T.c.value('./SMO[1]', 'int') AS [SMO]
      ,T.c.value('./ZAP[1]', 'int') AS [ZAP]
INTO #ZGLV
FROM @x.nodes('/PERS_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(30)') AS [FAM]
     ,T.c.value('./IM[1]', 'varchar(30)') AS [IM]
     ,T.c.value('./OT[1]', 'varchar(30)') AS [OT]
     ,T.c.value('./W[1]', 'int') AS [W]
     ,T.c.value('./DR[1]', 'datetime') AS [DR]
     ,T.c.value('./VPOLIS[1]', 'int') AS [VPOLIS]
     ,T.c.value('./SPOLIS[1]', 'varchar(30)') AS [SPOLIS]
     ,T.c.value('./NPOLIS[1]', 'varchar(30)') AS [NPOLIS]
     ,T.c.value('./PHONE1[1]', 'varchar(30)') AS [PHONE1]
     ,T.c.value('./PHONE2[1]', 'varchar(30)') AS [PHONE2]
     ,T.c.value('./ABOUT_WHAT[1]', 'varchar(4)') AS [ABOUT_WHAT]
     ,T.c.value('./QUESTION_DATE[1]', 'datetime') AS [QUESTION_DATE]
     ,T.c.value('./QUESTION_RSLT[1]', 'int') AS [QUESTION_RSLT]
     ,T.c.value('./InsuredPolicyRecordID[1]', 'varchar(36)') AS [InsuredPolicyRecordID] -- заглушка
     ,T.c.value('./ActualInsuredPolicyRecordID[1]', 'varchar(36)') AS [ActualInsuredPolicyRecordID] -- заглушка
     ,T.c.value('../ZGLV[1]/YEAR[1]', 'int') AS [YEAR]
     ,T.c.value('../ZGLV[1]/DATA[1]', 'datetime') AS [DATA]
     ,T.c.value('../ZGLV[1]/SMO[1]', 'int') AS [SMO]
     ,T.c.value('./DispDirectID[1]', 'varchar(36)') AS [DispDirectID] -- заглушка
     ,T.c.value('./V016Dispt[1]', 'varchar(36)') AS [V016Dispt] -- заглушка
     ,T.c.value('./Insured[1]', 'varchar(36)') AS [Insured] -- заглушка
INTO #PERS
FROM @x.nodes('/PERS_LIST/PERS') T(c)

SELECT
     NewID() as rejectID
    ,T.c.value('../ID_PAC[1]', 'varchar(36)') AS [ID_PAC]
    ,T.c.value('../NPOLIS[1]', 'varchar(30)') AS [NPOLIS]
    ,T.c.value('./REJECT_REASON[1]', 'int') AS [REJECT_REASON]
    ,T.c.value('./ANSWER[1]', 'int') AS [ANSWER]
INTO #REJECT
FROM @x.nodes('/PERS_LIST/PERS/REJECT') T(c)


--Ищем полис застрахованного

--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)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null 
--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)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null 
--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)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null 
--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)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null 
--Ищем актуальный полис застрахованного 
update t1
set t1.[ActualInsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
    join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
    --join [ISZL].T_F008_OMS_TYPE f008 on f008.IDDOC = p.OmsType
  where t2.Insured = t3.Insured and t2.DateBegin <= t1.[DATA] and (not(t2.DateStop is not null and t2.DateStop <= t1.[DATA]))
  order by t2.DateBegin, p.OmsType
 )
from #PERS t1
join [ISZL].T_INSURED_POLICY_RECORD t3 on t3.[InsuredPolicyRecordID] = t1.[InsuredPolicyRecordID]

--Проставляем нужного Insured
update p
set p.Insured = t1.Insured
from #PERS p
join [ISZL].T_INSURED_POLICY_RECORD t1 on t1.InsuredPolicyRecordID = p.InsuredPolicyRecordID
where p.Insured is null

--Проставляем V016Dispt (в новом формате значение v016 присылают в поле ABOUT_WHAT)
update p
set p.V016Dispt = d.V016ID
from #PERS p
join iszl.T_V016_DISPT d on d.IDDT = p.ABOUT_WHAT

--Проставляем DispDirectId
update p
set p.DispDirectId = (select top 1 d.DispDirectId from iszl.T_DISP_DIRECT d where d.Insured = p.Insured and d.[YEAR] = p.[YEAR] and p.V016Dispt = d.V016Dispt and d.type_ = (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT'))
from #PERS p

create table #Errors
(
    OSHIB int,
    ID_PAC varchar(36),
    NPOLIS varchar(30),
    COMMENT varchar(250)
)

----Проверка на дубли в файле
;WITH CTE AS 
(
      select p.ItemID, p.ID_PAC, p.FAM, p.IM, p.OT, p.DR, p.NPOLIS, p.QUESTION_DATE, p.QUESTION_RSLT, ROW_NUMBER() OVER 
      (PARTITION BY p.ID_PAC, p.FAM, p.IM, p.OT, p.DR, p.QUESTION_DATE, p.QUESTION_RSLT ORDER BY p.ItemID) as R from #PERS p
)
Select *
into #PERS_D
FROM CTE
Where R>1


Insert into #Errors
select 2, t1.ID_PAC, t1.NPOLIS, 'Запись имеет дубликат в файле. NPOLIS('+cast(t1.NPOLIS as varchar)+')'
from #PERS t1
join #PERS_D t2 on t2.NPOLIS = t1.NPOLIS


declare @out_content varchar(max)
set @out_content = 
    '<?xml version="1.0" encoding="Windows-1251"?>' 
        + 
        (
            SELECT 
                OSHIB as 'CODE'
                ,COMMENT as 'COMMENT'
            FROM #Errors err
            FOR XML PATH('ERR'),ROOT('ROOT')
        )

if ((select count(*) from #Errors) = 0)
begin
    declare @currentDate datetime
    set @currentDate = getdate()

    declare @filename varchar(50)
    set @filename = (select zglv.[FILENAME] from #ZGLV zglv)
    --checkFileExist
    declare @DdFileExist uniqueidentifier
    set @DdFileExist = (
                select top 1 dd.[FileID] 
                from [ISZL].[T_DISP_DIRECT_INTERVIEW_FILE] dd
                join [VCLib].[T_FILE] f on dd.FileID = f.FileID 
                where f.[Name] = @filename
            )
    if(@DdFileExist is not null)
    begin
        delete ddinfo
        from ISZL.T_DISP_DIRECT_INTERVIEW ddinfo
        where ddinfo.DispDirectInterviewFile = @DdFileExist
        
        delete from ISZL.T_DISP_DIRECT_INTERVIEW_FILE
        where FileID = @DdFileExist
        
        delete vc
        from VCLib.T_FILE vc
        where vc.FileID = @DdFileExist
    end
    
    declare @fileId1 uniqueidentifier
    set @fileId1 = newid()


    insert into VCLib.T_FILE
    (
         [FileID]
        ,[Name]
        ,[FileNameForm]
        ,[SaveFileLocation]
        ,[UseOle]
        ,[type_]
    )
    select @fileId1
          ,@filename
          ,0
          ,0
          ,0
          ,(select ent.EntityID from VCLib.T_ENTITIES ent where ent.EntName = 'DISP_DIRECT_INTERVIEW_FILE')

    insert into ISZL.T_DISP_DIRECT_INTERVIEW_FILE
    (
         [FileID]
        ,[ReceivedDate]
        ,[ReceivedTime]
        ,[VERSION]
    )
    select @fileId1
          ,GETDATE()
          ,GETDATE()
          ,zglv.[VERSION]
    from #ZGLV zglv

    insert into ISZL.T_DISP_DIRECT_INTERVIEW
    (    FAM
        ,IM
        ,OT
        ,DR
        ,W
        ,[DispDirectInterviewID]
        ,[Date]
        ,[DispDirect]
        ,[DispDirectInterviewFile]
        ,[Smo]
        ,[QUESTION_DATE]
        ,[QUESTION_RSLT]
        ,[V016Dispt]
        ,[Insured]
    )
    select
         pers.FAM
        ,pers.IM
        ,pers.OT
        ,pers.DR
        ,pers.W
        ,pers.ItemID
        ,pers.[DATA]
        ,pers.DispDirectID
        ,@fileId1
        ,o.OrganizationID
        ,pers.QUESTION_DATE
        ,pers.QUESTION_RSLT
        ,pers.V016Dispt
        ,pers.Insured
    from #PERS pers
    left join ISZL.T_ORGANIZATION o on o.Code = pers.SMO and o.type_ = (select top 1 EntityID from VCLib.T_ENTITIES e where e.EntName = 'SMO')

    -------Insert назначений
    INSERT INTO [ISZL].[T_DISP_DIRECT_INTERVIEW_REJECT]
           ([DispDirectInterviewRejectID]
           ,[DispDirectInterview]
           ,[REJECT_REASON]
           ,[ANSWER])
     SELECT
            r.rejectID
           ,p.ItemID
           ,r.REJECT_REASON
           ,r.ANSWER
     FROM #REJECT r
     join #PERS p on p.ID_PAC = r.ID_PAC or p.NPOLIS = r.NPOLIS
end

DECLARE @t1 table (isErr bit, Xml varchar(max))
INSERT @t1 (isErr, Xml)
select case when (select count(*) from #Errors) = 0 then 1 else 0 end, @out_content
select * from @t1

DROP TABLE #Errors
DROP TABLE #PERS
DROP TABLE #ZGLV
DROP TABLE #REJECT
DROP TABLE #PERS_D
END