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
 ,@PackageName varchar(max) = null
 ,@FileName varchar(max) = null
AS 
BEGIN 

DECLARE @CrDate as date
SET @CrDate = GetDate()

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

    --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_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = p.InsuredPolicyRecordID
        cross apply
        (
            select top 1 mo.Code CODE_MO
            from ISZL.T_MO_ATTACHMENT mAttach
                join iszl.T_ORGANIZATION mo on mo.OrganizationID = mAttach.Mo
            where mAttach.Insured = ipr.Insured
                    and
                mAttach.AttachDate <= p.DATA
                    and
                mAttach.[Status] = 1
                    and
                not exists
                (
                    select *
                    from ISZL.T_MO_ATTACHMENT mDetach
                    where mDetach.AttachDate < p.DATA
                            and
                        mDetach.AttachDate >= AttachDate
                            and
                        mDetach.Status = 2
                            and
                        mDetach.Mo = mAttach.Mo
                )
            order by mAttach.AttachDate desc, mAttach.ActualDate desc, mAttach.CreationDateTime desc, mAttach.MoAttachmentID
        ) aAttach
    where p.CODE_MO <> aAttach.CODE_MO



    -- проверка на дубликат
    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)
    join iszl.T_V016_DISPT v on v.V016ID = dd.V016Dispt
        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') 
        and p.TYPE = 0 
        and ((cast(p.V016 as uniqueidentifier) = dd.V016Dispt) or (p.IDDT = 'ДВ4' and v.IDDT = 'ОПВ') or (p.IDDT = 'ОПВ' and v.IDDT = 'ДВ4'))
        


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

    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 t1.[type] <> 0 and (Select COUNT(*) from #Errors) = 0 
    
    ------------------
    --Добавляем только новых в 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]
          ,CreationDate
     )
     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)
                    ,@CrDate
     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')


    -- Сбор идентификаторов на удаление
    select distinct dd.DispDirectID as DispDirectID 
        into #forDelete
    from #PERS p
        join ISZL.T_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = p.InsuredPolicyRecordID
        join ISZL.T_DISP_DIRECT dd on dd.Insured = ipr.Insured
        join [ISZL].T_ORGANIZATION orgmo on orgmo.OrganizationID = dd.Mo
                and
            dd.[YEAR] = p.[YEAR]
                and
            dd.MONTH_Q = p.[MONTH]
                and
            dd.V016Dispt = p.V016
                and
            orgmo.Code = p.CODE_MO
    where p.InsuredPolicyRecordID is not null
            and
        p.[TYPE] = 2
            and
        dd.type_ = (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT')

    -- Отвязываем факт
    update ddf
    set ddf.DispDirect = null
    from ISZL.T_DISP_DIRECT_FACT ddf
    where ddf.DispDirect in 
        (
            select fd.DispDirectID
            from #forDelete fd
        )

    -- Удаляем информирование
    delete ddi
    from ISZL.T_DISP_DIRECT_INFO ddi
    where ddi.DispDirect in 
        (
            select fd.DispDirectID
            from #forDelete fd
        )

    -- Удаляем опросы
    delete ddi
    from ISZL.T_DISP_DIRECT_INTERVIEW ddi
    where ddi.DispDirect in 
        (
            select fd.DispDirectID
            from #forDelete fd
        )

    -- Удаляем историю статусов
    delete dds
    from ISZL.T_DISP_DIRECT_STATUS dds
    where dds.DispDirect in 
        (
            select fd.DispDirectID
            from #forDelete fd
        )

    -- Удаляем контакты этой записи ДН
    delete pc
    from ISZL.T_PATIENT_CONTACT pc
    where pc.DispDirect in 
        (
            select fd.DispDirectID
            from #forDelete fd
        )

    -- Сами записи ДН
    delete dd
    from ISZL.T_DISP_DIRECT dd
    where dd.DispDirectID in
        (
            select fd.DispDirectID
            from #forDelete fd
        )



    --------------------
    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(0 as bit)
       else case
            when (select COUNT(*) from #Errors) > 0
            then cast(1 as bit)
            end
       end) as IsSingleXmlFlk,
       '<?xml version="1.0" encoding="Windows-1251"?>' 
        + 
        (
            SELECT 
                CODE as 'CODE'
                ,COMMENT as 'COMMENT'
            FROM #Errors 
            FOR XML PATH('ERR'),ROOT('ROOT')
        ) as Content,
        'FLK_'+ @FileName+'.xml'  as [Filename],
        'FLK' as [TargetDirectory],
        null as [CodePage]
        
DROP TABLE #PERS
DROP TABLE #ZGLV
DROP TABLE #CONTACTS
DROP TABLE #Errors
END
