IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetBSKRegister]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetBSKRegister]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-04-01
-- Description: Загрузка регистра БСК.
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetBSKRegister]
    @Xml xml = null
    ,@ID uniqueidentifier = null
    ,@QueueName varchar(100) = 'RDSSync'
    ,@dateTime datetime = null
    ,@User uniqueidentifier = null
AS
BEGIN
    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OperationType varchar(255)
           ,@OwnerID uniqueidentifier
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)

    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

    IF (@Xml is not null)
    BEGIN
        
        -- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(6)')
        from @Xml.nodes('BSK_REGISTER') T(c)

        -- Generate table #BSK_REGISTER
        select t.c.value('(./RegisterID/text())[1]', 'int')                                   as [RegisterID]
              ,t.c.value('(./LastName/text())[1]', 'varchar(40)')                             as [LastName]
              ,t.c.value('(./FirstName/text())[1]', 'varchar(40)')                            as [FirstName]
              ,t.c.value('(./FatherName/text())[1]', 'varchar(40)')                           as [FatherName]
              ,convert(datetime, t.c.value('(./Birthday/text())[1]', 'varchar(10)'), 104)     as [Birthday]
              ,t.c.value('(./Kod/text())[1]', 'varchar(32)')		                          as [Kod]
              ,t.c.value('(./DocSeries/text())[1]', 'varchar(8)')                             as [DocSeries]
              ,t.c.value('(./DocNumber/text())[1]', 'varchar(8)')                             as [DocNumber]
              ,t.c.value('(./Sex/text())[1]', 'int')                                          as [Sex]
              ,t.c.value('(./Snils/text())[1]', 'varchar(14)')                                as [Snils]
              ,convert(datetime, t.c.value('(./DateBegin/text())[1]', 'varchar(10)'), 104)    as [DateBegin]
              ,convert(datetime, t.c.value('(./DateEnd/text())[1]', 'varchar(10)'), 104)      as [DateEnd]
              ,convert(datetime, t.c.value('(./LastEditDate/text())[1]', 'varchar(10)'), 104) as [LastEditDate]
              ,t.c.value('(./Lpu/text())[1]', 'varchar(6)')                                   as [Lpu]
              ,t.c.value('(./ExcludeReason/text())[1]', 'int')                                as [ExcludeReason]
              ,t.c.value('(./DocType/text())[1]', 'int')                                      as [DocType]
              ,t.c.value('(./PatientId/text())[1]', 'uniqueidentifier')                       as [PatientId]
            INTO #BSK_REGISTER
        from @Xml.nodes('BSK_REGISTER/body/PATIENT') T(c)
        where @OperationType = @FTLOAD

		
        -- Generate table #BSK_BENEFITS
        select t.c.value('(./RegisterID/text())[1]', 'int')											as [RegisterID]
              ,t.c.value('(./BenID/text())[1]', 'uniqueidentifier')									as [BenID]
              ,convert(datetime, t.c.value('(./DiagDate/text())[1]', 'varchar(10)'), 104)			as [DiagDate]
              ,t.c.value('(./Document/text())[1]', 'varchar(16)')									as [Document]
              ,convert(datetime, t.c.value('(./DocumentDate/text())[1]', 'varchar(10)'), 104)       as [DocumentDate]
              ,t.c.value('(./Mkb/text())[1]', 'varchar(8)')											as [Mkb]
              ,t.c.value('(./BenefitCode/text())[1]', 'varchar(255)')								as [BenefitCode]
              ,t.c.value('(./BenefitViews/text())[1]', 'int')										as [BenefitViews]
              ,convert(datetime, t.c.value('(./BeginDate/text())[1]', 'varchar(10)'), 104)			as [BeginDate]
              ,convert(datetime, t.c.value('(./EndDate/text())[1]', 'varchar(10)'), 104)			as [EndDate]
              ,t.c.value('(./Lpu/text())[1]', 'varchar(6)')											as [Lpu]
              ,t.c.value('(./ServiceCode/text())[1]', 'varchar(20)')								as [ServiceCode]
              ,t.c.value('(./ServiceName/text())[1]', 'varchar(255)')								as [ServiceName]
              ,t.c.value('(./ExclusionReason/text())[1]', 'int')									as [ExclusionReason]
              ,convert(datetime, t.c.value('(./EstimatedDDate/text())[1]', 'varchar(10)'), 104)     as [EstimatedDDate]
              ,convert(datetime, t.c.value('(./DRegistrationDate/text())[1]', 'varchar(10)'), 104)  as [DRegistrationDate]
              ,t.c.value('(./Place/text())[1]', 'int')												as [Place]
              ,t.c.value('(./LpuTo/text())[1]', 'varchar(6)')										as [LpuTo]
              ,convert(datetime, t.c.value('(./CloseDate/text())[1]', 'varchar(10)'), 104)			as [CloseDate]
            INTO #BSK_BENEFITS
        from @Xml.nodes('BSK_REGISTER/body/PATIENT/BENEFITS') T(c)
        where @OperationType = @FTLOAD

		---- Generate table #BSK_MEDICAMENTS
  --      select t.c.value('(./BenID/text())[1]', 'uniqueidentifier')							as [BenID]
  --            ,t.c.value('(./Mnn/text())[1]', 'int')										as [Mnn]
  --            ,convert(datetime, t.c.value('(./DateStart/text())[1]', 'varchar(10)'), 104)  as [DateStart]
  --            ,convert(datetime, t.c.value('(./EndDate/text())[1]', 'varchar(10)'), 104)    as [EndDate]
  --            ,t.c.value('(./MedicationForms/text())[1]', 'uniqueidentifier')				as [MedicationForms]
  --            ,t.c.value('(./Regiment/text())[1]', 'varchar(255)')                          as [Regiment]
  --            ,t.c.value('(./Dose/text())[1]', 'decimal(31,10)')                            as [Dose]
  --            ,t.c.value('(./Prepid/text())[1]', 'int')										as [Prepid]
  --            ,t.c.value('(./DayliCoeff/text())[1]', 'decimal(31,10)')                      as [DayliCoeff]
  --            ,t.c.value('(./MeasureUnit/text())[1]', 'varchar(255)')						as [MeasureUnit]
  --            ,t.c.value('(./MeasureUnitType/text())[1]', 'int')							as [MeasureUnitType]
  --            ,t.c.value('(./SingleDose/text())[1]', 'decimal(31,10)')                      as [SingleDose]
  --            ,t.c.value('(./Multiplicity/text())[1]', 'int')								as [Multiplicity]
  --            ,t.c.value('(./DoseView/text())[1]', 'varchar(255)')							as [DoseView]
  --            ,t.c.value('(./DoseView/text())[1]', 'int')									as [DoseViewType]
  --          INTO #BSK_MEDICAMENTS
  --      from @Xml.nodes('BSK_REGISTER/body/PATIENT/BENEFITS/MEDICAMENTS') T(c)
  --      where @OperationType = @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Связываем регистр с пациентами
            -- Поиск по СНИЛС + ДР
            update pr
            set pr.PatientId = p.PatientID
            from #BSK_REGISTER pr
                join [AKUZ].T_PATIENT p on p.SNILS = pr.Snils and p.Birthday = pr.Birthday
            where pr.Snils is not null and pr.Birthday is not null

            -- Поиск по серии и номеру документа
            update pr
            set pr.PatientId = p.PatientID
            from #BSK_REGISTER pr
                join [AKUZ].T_PATIENT p on LEFT(p.DocSeries, 8) = pr.DocSeries and LEFT(p.DocNumber, 8) = pr.DocNumber
            where 
				pr.PatientId is null 
				and pr.DocSeries is not null
                and	pr.DocNumber is not null

            -- Поиск по ФИО + ДР
            update pr
            set pr.PatientId = p.PatientID
            from #BSK_REGISTER pr
                join [AKUZ].T_PATIENT p on p.LastName = pr.LastName and p.FirstName = pr.FirstName and (p.FatherName = pr.FatherName or p.FatherName is null and pr.FatherName is null) and p.Birthday = pr.Birthday
            where pr.PatientId is null

			delete pb
            from akuz.T_PATIENT_BENEFITS pb 
			join [AKUZ].T_BENEFITS b on b.BenefitsID = pb.Benefits
			where b.BenefitViews in (19007)

            -- Загружаем новые
            INSERT INTO [AKUZ].[T_PATIENT_BENEFITS]
                       ([PatientBenefitsID] ,[Patient] ,[Benefits] ,[BenefitDocumentNumber] ,[BeginDate] ,[EndDate] ,[Mkb] ,[Active]
                       ,[Deleted] ,[CreateType] ,[LPU_INF] ,[RegDate], [PAC_ID])
            SELECT
                       NEWID()--<PatientBenefitsID, uniqueidentifier,>
                       ,p.PatientID--<Patient, uniqueidentifier,>
                       ,b.BenefitsID--<Benefits, uniqueidentifier,>
                       ,pb.Document--<BenefitDocumentNumber, varchar(255),>
                       ,pb.BeginDate--<BeginDate, datetime,>
                       ,isnull(pb.CloseDate, pb.EndDate)--<EndDate, datetime,>
                       ,(select top 1 m.MkbID from AKUZ.T_MKB m where m.MCBCode = pb.Mkb)--<Mkb, uniqueidentifier,>
                       ,1--<Active, bit,>
                       ,0--<Deleted, bit,>
                       ,2--<CreateType, int,>
                       ,pb.Lpu--<LPU_INF, varchar(6),>
                       ,@dateTime--<RegDate, datetime,>)
					   ,pb.RegisterID
            from #BSK_BENEFITS pb
                join #BSK_REGISTER pr on pr.RegisterID = pb.RegisterID
                join [AKUZ].T_PATIENT p on p.PatientID = pr.PatientId
                join [VCLib].T_ENUMS eBv on eBv.Code = pb.BenefitViews and eBv.EnumTypeID = 190
                join [AKUZ].T_BENEFITS b on b.BenefitCode = pb.BenefitCode
                        and
                    b.BenefitViews = eBv.EnumID
        END
    END
END