IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetSetSpecialBenefitsList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetSpecialBenefitsList]
/****** Object:  StoredProcedure [dbo].[sp_GetSetSpecialBenefitsList]    Script Date: 18.11.2015 22:00:23 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-02-07
-- Description: Загрузка льготников по специальной льготе из RDS
-- =============================================
create PROCEDURE [dbo].[sp_GetSetSpecialBenefitsList]
    @Xml xml = null,
    @ID uniqueidentifier = null,
    @IsNew bit = 1,
    @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(36)')
              ,@OwnerID = t.c.value('./header[1]/ID[1]', 'uniqueidentifier')
              ,@Result = cast(@Xml as varchar(max))
        from @Xml.nodes('SPECIAL_BENEFITS_REGISTR') T(c)

		select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(36)')
        from @Xml.nodes('SPECIAL_BENEFITS_REGISTR') T(c)

		if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #SPECIAL_BENEFITS_REGISTR
            SELECT t.c.value('./ID[1]', 'uniqueidentifier')                                AS ID,
                   t.c.value('./ADRESS[1]', 'varchar(256)')                                AS Adress,
                   t.c.value('./COMMENT[1]', 'varchar(1024)')                              AS Comment,
                   CONVERT(DATETIME, t.c.value('./CHANGE_DATE[1]', 'varchar(10)'), 104)    AS DateOfChange,
                   CONVERT(DATETIME, t.c.value('./DECLUSION_DATE[1]', 'varchar(10)'), 104) AS DateOfDeclusion,
                   CONVERT(DATETIME, t.c.value('./INCLUSION_DATE[1]', 'varchar(10)'), 104) AS DateOfInclusion,
                   t.c.value('./OMS_NUMBER[1]', 'varchar(30)')                             AS OmsNumber,
                   t.c.value('./OMS_SERIAL[1]', 'varchar(30)')                             AS OmsSerial,
                   t.c.value('./UNIQUE_NUMBER[1]', 'varchar(128)')                         AS UniqueNumber,
                   t.c.value('./MKB[1]', 'varchar(10)')                                    AS Mkb,
                   t.c.value('./DIAGNOSIS[1]', 'varchar(4096)')                            AS Diagnosis,
                   t.c.value('./DOC_SERIES[1]', 'varchar(30)')                             AS DocSeries,
                   t.c.value('./DOC_NUMBER[1]', 'varchar(30)')                             AS DocNumber,
                   CONVERT(DATETIME, t.c.value('./BIRTHDAY[1]', 'varchar(10)'), 104)       AS Birthday,
                   t.c.value('./FIRST_NAME[1]', 'varchar(60)')                             AS FirstName,
                   t.c.value('./LAST_MANE[1]', 'varchar(60)')                              AS LastName,
                   t.c.value('./FATHER_NAME[1]', 'varchar(60)')                            AS FatherName,
                   CONVERT(DATETIME, t.c.value('./DOC_ISSUE_DATE[1]', 'varchar(10)'), 104) AS DocIssueDate,
                   t.c.value('./DOC_ISSUE_ORG[1]', 'varchar(256)')                         AS DocIssueOrg,
                   t.c.value('./DOC_TYPE[1]', 'int')                                       AS DocumentType,
                   t.c.value('./INCLUSION_LPU[1]', 'varchar(6)')                           AS InclusionLPU,
                   t.c.value('./SEX[1]', 'int')                                            AS Sex,
                   t.c.value('./BIRTHDAY_LAST_NAME[1]', 'varchar(60)')                     AS BirthdayLastName,
                   t.c.value('./SMO[1]', 'varchar(5)')                                     AS F002Smo,
                   t.c.value('./SNILS[1]', 'varchar(14)')                                  AS SNILS,
                   t.c.value('./BENIFITS[1]', 'varchar(255)')                              AS Benefits,
                   t.c.value('./BENIFITS_VIEWS[1]', 'int')                                 AS BenefitViews
            INTO #SPECIAL_BENEFITS_REGISTR
            FROM @Xml.nodes('SPECIAL_BENEFITS_REGISTR/body/SPECIAL_BENEFITS_REGISTR_ITEMS/SPECIAL_BENEFITS_REGISTR_ITEM') T(c)

            ALTER TABLE #SPECIAL_BENEFITS_REGISTR ADD [Patient] uniqueidentifier

            -- Связываем регистр с пациентами
            -- Поиск по СНИЛС + ДР
            UPDATE nos
            SET nos.Patient = p.PatientID
            FROM #SPECIAL_BENEFITS_REGISTR nos
            JOIN AKUZ.T_PATIENT p ON p.SNILS = nos.SNILS AND p.Birthday = nos.Birthday
            WHERE
                  nos.Patient IS NULL
              AND nos.SNILS IS NOT NULL
              AND nos.Birthday IS NOT NULL

            -- Поиск по серии и номеру документа
            UPDATE nos
            SET nos.Patient = p.PatientID
            FROM #SPECIAL_BENEFITS_REGISTR nos
            JOIN AKUZ.T_PATIENT p ON LEFT(p.DocSeries, 8) = nos.DocSeries AND LEFT(p.DocNumber, 8) = nos.DocNumber AND
                                     p.Birthday = nos.Birthday
            WHERE
                  nos.Patient IS NULL
              AND nos.DocSeries IS NOT NULL
              AND nos.DocNumber IS NOT NULL
              AND nos.Birthday IS NOT NULL

            -- Поиск по ФИО + ДР
            UPDATE nos
            SET nos.Patient = p.PatientID
            FROM #SPECIAL_BENEFITS_REGISTR nos
            JOIN AKUZ.T_PATIENT p ON p.LastName = nos.LastName AND p.FirstName = nos.FirstName AND
                                     (p.FatherName = nos.FatherName OR
                                      p.FatherName IS NULL AND nos.FatherName IS NULL) AND p.Birthday = nos.Birthday
            WHERE nos.Patient IS NULL

		    delete pd
		    from [AKUZ].T_PATIENT_BENEFITS pd
		    join [AKUZ].T_BENEFITS b on b.BenefitsID = pd.Benefits
		    where b.BenefitViews = 19009 and CreateType = 2

		    INSERT INTO [AKUZ].[T_PATIENT_BENEFITS]
		           ([PatientBenefitsID]
		           ,[Patient]
		           ,[Benefits]
		           ,[BenefitDocument]
		           ,[BenefitDocumentNumber]
		           ,[BeginDate]
		           ,[EndDate]
		           ,[Mkb]
		           ,[Active]
		           ,[Deleted]
		           ,[CreateType]
		           ,[PAC_ID]
		           ,[LPU_INF]
		           ,[SOST_R]
		           ,[ReasonRegistration]
		           ,[ReasonDeregistration]
		           ,[BenefitDocumentSeries]
		           ,[BenefitDocType]
		           ,[RegDate])
		     SELECT
		           newid()
		           ,nos.[Patient]
		           ,(select TOP 1 b.BenefitsID from [AKUZ].T_BENEFITS b
				   where b.BenefitCode = nos.[Benefits] and b.BenefitViews = 19009)
		           ,null
		           ,null
		           ,nos.DateOfInclusion
		           ,nos.DateOfDeclusion
		           ,(select TOP 1 m.MkbID from [AKUZ].T_MKB m where nos.Mkb = m.MCBCode)
		           ,1
		           ,0
		           ,2
		           ,nos.[UniqueNumber]
		           ,nos.InclusionLPU
		           ,null
		           ,null
		           ,null
		           ,null
		           ,null
		           ,nos.DateOfInclusion
				   FROM #SPECIAL_BENEFITS_REGISTR nos
				   where nos.[Patient] is not null
                       

        END
        ELSE
        BEGIN
            -- Generate table #SPECIAL_BENEFITS_REG
            SELECT t.c.value('./header[1]/ID[1]', 'uniqueidentifier')                              AS ID,
                   t.c.value('./body[1]/ADRESS[1]', 'varchar(256)')                                AS Adress,
                   t.c.value('./body[1]/COMMENT[1]', 'varchar(1024)')                              AS Comment,
                   CONVERT(DATETIME, t.c.value('./body[1]/CHANGE_DATE[1]', 'varchar(10)'), 104)    AS DateOfChange,
                   CONVERT(DATETIME, t.c.value('./body[1]/DECLUSION_DATE[1]', 'varchar(10)'), 104) AS DateOfDeclusion,
                   CONVERT(DATETIME, t.c.value('./body[1]/INCLUSION_DATE[1]', 'varchar(10)'), 104) AS DateOfInclusion,
                   t.c.value('./body[1]/OMS_NUMBER[1]', 'varchar(30)')                             AS OmsNumber,
                   t.c.value('./body[1]/OMS_SERIAL[1]', 'varchar(30)')                             AS OmsSerial,
                   t.c.value('./body[1]/UNIQUE_NUMBER[1]', 'varchar(128)')                         AS UniqueNumber,
                   t.c.value('./body[1]/MKB[1]', 'varchar(10)')                                    AS Mkb,
                   t.c.value('./body[1]/DIAGNOSIS[1]', 'varchar(4096)')                            AS Diagnosis,
                   t.c.value('./body[1]/DOC_SERIES[1]', 'varchar(30)')                             AS DocSeries,
                   t.c.value('./body[1]/DOC_NUMBER[1]', 'varchar(30)')                             AS DocNumber,
                   CONVERT(DATETIME, t.c.value('./body[1]/BIRTHDAY[1]', 'varchar(10)'), 104)       AS Birthday,
                   t.c.value('./body[1]/FIRST_NAME[1]', 'varchar(60)')                             AS FirstName,
                   t.c.value('./body[1]/LAST_MANE[1]', 'varchar(60)')                              AS LastName,
                   t.c.value('./body[1]/FATHER_NAME[1]', 'varchar(60)')                            AS FatherName,
                   CONVERT(DATETIME, t.c.value('./body[1]/DOC_ISSUE_DATE[1]', 'varchar(10)'), 104) AS DocIssueDate,
                   t.c.value('./body[1]/DOC_ISSUE_ORG[1]', 'varchar(256)')                         AS DocIssueOrg,
                   t.c.value('./body[1]/DOC_TYPE[1]', 'int')                                       AS DocumentType,
                   t.c.value('./body[1]/INCLUSION_LPU[1]', 'varchar(6)')                           AS InclusionLPU,
                   t.c.value('./body[1]/SEX[1]', 'int')                                            AS Sex,
                   t.c.value('./body[1]/BIRTHDAY_LAST_NAME[1]', 'varchar(60)')                     AS BirthdayLastName,
                   t.c.value('./body[1]/SMO[1]', 'varchar(5)')                                     AS F002Smo,
                   t.c.value('./body[1]/SNILS[1]', 'varchar(14)')                                  AS SNILS,
                   t.c.value('./body[1]/BENIFITS[1]', 'varchar(255)')                              AS Benefits,
                   t.c.value('./body[1]/BENIFITS_VIEWS[1]', 'int')                                 AS BenefitViews
            INTO #SPECIAL_BENEFITS_REG
            FROM @Xml.nodes('SPECIAL_BENEFITS_REGISTR') T(c)

            ALTER TABLE #SPECIAL_BENEFITS_REG ADD [Patient] uniqueidentifier

            -- Поиск по СНИЛС + ДР
            UPDATE nos
            SET nos.Patient = p.PatientID
            FROM #SPECIAL_BENEFITS_REG nos
            JOIN AKUZ.T_PATIENT p ON p.SNILS = nos.SNILS AND p.Birthday = nos.Birthday
            WHERE
                  nos.Patient IS NULL
              AND nos.SNILS IS NOT NULL
              AND nos.Birthday IS NOT NULL

            -- Поиск по серии и номеру документа
            UPDATE nos
            SET nos.Patient = p.PatientID
            FROM #SPECIAL_BENEFITS_REG nos
            JOIN AKUZ.T_PATIENT p ON LEFT(p.DocSeries, 8) = nos.DocSeries AND LEFT(p.DocNumber, 8) = nos.DocNumber AND
                                     p.Birthday = nos.Birthday
            WHERE
                  nos.Patient IS NULL
              AND nos.DocSeries IS NOT NULL
              AND nos.DocNumber IS NOT NULL
              AND nos.Birthday IS NOT NULL

            -- Поиск по ФИО + ДР
            UPDATE nos
            SET nos.Patient = p.PatientID
            FROM #SPECIAL_BENEFITS_REG nos
            JOIN AKUZ.T_PATIENT p ON p.LastName = nos.LastName AND p.FirstName = nos.FirstName AND
                                     (p.FatherName = nos.FatherName OR
                                      p.FatherName IS NULL AND nos.FatherName IS NULL) AND p.Birthday = nos.Birthday
            WHERE nos.Patient IS NULL

            IF(@OperationType = @DELETE)
            BEGIN

            delete pd
            from [AKUZ].T_PATIENT_BENEFITS pd
            join [AKUZ].T_BENEFITS b on b.BenefitsID = pd.Benefits
            join #SPECIAL_BENEFITS_REG nos on nos.[Patient] = pd.Patient
            where b.BenefitViews = 19009 and pd.CreateType = 2 and b.BenefitCode = nos.[Benefits]

            END
            ELSE
            BEGIN
            delete pd
            from [AKUZ].T_PATIENT_BENEFITS pd
            join [AKUZ].T_BENEFITS b on b.BenefitsID = pd.Benefits
            join #SPECIAL_BENEFITS_REG nos on nos.[Patient] = pd.Patient
            where b.BenefitViews = 19009 and pd.CreateType = 2 and b.BenefitCode = nos.[Benefits]

            INSERT INTO [AKUZ].[T_PATIENT_BENEFITS]
                       ([PatientBenefitsID]
                       ,[Patient]
                       ,[Benefits]
                       ,[BenefitDocument]
                       ,[BenefitDocumentNumber]
                       ,[BeginDate]
                       ,[EndDate]
                       ,[Mkb]
                       ,[Active]
                       ,[Deleted]
                       ,[CreateType]
                       ,[PAC_ID]
                       ,[LPU_INF]
                       ,[SOST_R]
                       ,[ReasonRegistration]
                       ,[ReasonDeregistration]
                       ,[BenefitDocumentSeries]
                       ,[BenefitDocType]
                       ,[RegDate])
                 SELECT
                       newid()
                       ,nos.[Patient]
                       ,(select TOP 1 b.BenefitsID from [AKUZ].T_BENEFITS b
                       where b.BenefitCode = nos.[Benefits] and b.BenefitViews = 19009)
                       ,null
                       ,null
                       ,nos.DateOfInclusion
                       ,nos.DateOfDeclusion
                       ,(select TOP 1 m.MkbID from [AKUZ].T_MKB m where nos.Mkb = m.MCBCode)
                       ,1
                       ,0
                       ,2
                       ,nos.[UniqueNumber]
                       ,nos.InclusionLPU
                       ,null
                       ,null
                       ,null
                       ,null
                       ,null
                       ,nos.DateOfInclusion
                       FROM #SPECIAL_BENEFITS_REG nos
                       where nos.[Patient] is not null
            END

        END
    END
END