IF EXISTS (SELECT *
           FROM sys.objects
           WHERE
                 object_id = OBJECT_ID(N'[dbo].[sp_GetSetRDSNosology]')
             AND type IN (N'P', N'PC'))
    DROP PROCEDURE dbo.sp_GetSetRDSNosology
/****** Object:  StoredProcedure [dbo].[sp_GetSetRDSNosology]    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: 2015-11-03
-- Description: Загрузка льготников по 12 ВЗН
-- =============================================
CREATE PROCEDURE dbo.sp_GetSetRDSNosology @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()),
        @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('NOSOLOGY') T(c)

            CREATE TABLE #NOSOLOGY
            (
                ID                 UNIQUEIDENTIFIER,
                Adress             VARCHAR(256),
                CardStatus         VARCHAR(256),
                Comment            VARCHAR(1024),
                DateOfDeclusion    DATETIME,
                DateOfInclusion    DATETIME,
                ExecutiveAuthority VARCHAR(256),
                Head               VARCHAR(256),
                OmsNumber          VARCHAR(30),
                OmsSerial          VARCHAR(30),
                UniqueNumber       VARCHAR(128),
                Diagnosis          VARCHAR(4096),
                DocSeries          VARCHAR(30),
                DocNumber          VARCHAR(30),
                Birthday           DATETIME,
                FirstName          VARCHAR(60),
                LastName           VARCHAR(60),
                FatherName         VARCHAR(60),
                DocIssueDate       DATETIME,
                DocIssueOrg        VARCHAR(256),
                DeclusionLPU       VARCHAR(6),
                DocumentType       INT,
                ExemptionBy178     INT,
                ExemptionBy890     INT,
                InclusionLPU       VARCHAR(6),
                Region             VARCHAR(13),
                Sex                INT,
                SNILS              VARCHAR(14),
                BirthdayLastName   VARCHAR(60),
                Disability         INT,
                TherapyResistance  INT,
                DeathDate          DATETIME,
                OKATO              VARCHAR(MAX),
                F002Smo            VARCHAR(5),
                Patient            UNIQUEIDENTIFIER,
                DateOfChange       DATETIME
            );

            CREATE TABLE #NOSOLOGY_BENEFITS
            (
                NosologyID        UNIQUEIDENTIFIER,
                Mkb               VARCHAR(10),
                CardStatus        VARCHAR(256),
                Head              VARCHAR(256),
                Diagnosis         VARCHAR(4096),
                Disability        BIT,
                TherapyResistance BIT,
                BenefitCode       VARCHAR(255),
                DateBegin         DATETIME,
                EndDate           DATETIME,
                UniqueNumber      VARCHAR(128)
            );

            IF (@OperationType = @FTLOAD)
                BEGIN

                    INSERT INTO
                        #NOSOLOGY (ID,
                                   Adress,
                                   Comment,
                                   DateOfChange,
                                   DateOfDeclusion,
                                   DateOfInclusion,
                                   ExecutiveAuthority,
                                   OmsNumber,
                                   OmsSerial,
                                   DocSeries,
                                   DocNumber,
                                   Birthday,
                                   FirstName,
                                   LastName,
                                   FatherName,
                                   DocIssueDate,
                                   DocIssueOrg,
                                   DeclusionLPU,
                                   DocumentType,
                                   ExemptionBy178,
                                   ExemptionBy890,
                                   InclusionLPU,
                                   region,
                                   sex,
                                   snils,
                                   BirthdayLastName,
                                   DeathDate,
                                   OKATO,
                                   F002Smo)
                    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('./EXECUTIVE_AUTHORITY[1]', 'varchar(256)')             AS ExecutiveAuthority,
                        t.c.value('./OMS_NUMBER[1]', 'varchar(30)')                       AS OmsNumber,
                        t.c.value('./OMS_SERIAL[1]', 'varchar(30)')                       AS OmsSerial,
                        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_NAME[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('./DECLUSION_LPU[1]', 'varchar(6)')                     AS DeclusionLPU,
                        t.c.value('./DOC_TYPE[1]', 'int')                                 AS DocumentType,
                        t.c.value('./EX178[1]', 'int')                                    AS ExemptionBy178,
                        t.c.value('./EX890[1]', 'int')                                    AS ExemptionBy890,
                        t.c.value('./INCLUSION_LPU[1]', 'varchar(6)')                     AS InclusionLPU,
                        t.c.value('./REGION[1]', 'varchar(13)')                           AS Region,
                        t.c.value('./SEX[1]', 'int')                                      AS Sex,
                        t.c.value('./SNILS[1]', 'varchar(14)')                            AS SNILS,
                        t.c.value('./BIRTHDAY_LAST_NAME[1]', 'varchar(60)')               AS BirthdayLastName,
                        CONVERT(DATETIME, t.c.value('./DEATH_DATE[1]', 'varchar(10)'),
                                          104)                                            AS DeathDate,
                        t.c.value('./OKATO[1]', 'varchar(max)')                           AS OKATO,
                        t.c.value('./SMO[1]', 'varchar(5)')                               AS F002Smo
                    FROM @Xml.nodes('NOSOLOGY/body/NOSOLOGYS/NOSOLOGY') T(c)

                    INSERT INTO
                        #NOSOLOGY_BENEFITS(NosologyID,
                                           CardStatus,
                                           TherapyResistance,
                                           Head,
                                           Mkb,
                                           Diagnosis,
                                           UniqueNumber,
                                           Disability,
                                           BenefitCode,
                                           DateBegin,
                                           EndDate)
                    SELECT
                        t.c.value('./NOSOLOGYID[1]', 'uniqueidentifier')                    AS NosologyID,
                        t.c.value('./CARD_STATUS[1]', 'varchar(256)')                       AS CardStatus,
                        t.c.value('./THERAPY_RESISTANCE[1]', 'int')                         AS TherapyResistance,
                        t.c.value('./HEAD[1]', 'varchar(256)')                              AS Head,
                        t.c.value('./MKB[1]', 'varchar(10)')                                AS Mkb,
                        t.c.value('./DIAGNOSIS[1]', 'varchar(4096)')                        AS Diagnosis,
                        t.c.value('./UNIQUE_NUMBER[1]', 'varchar(128)')                     AS UniqueNumber,
                        t.c.value('./DIASBILITY[1]', 'int')                                 AS Disability,
                        t.c.value('./BENEFIT_CODE[1]', 'varchar(255)')                      AS BenefitCode,
                        CONVERT(DATETIME, t.c.value('./DATE_BEGIN[1]', 'varchar(10)'), 104) AS DateBegin,
                        CONVERT(DATETIME, t.c.value('./END_DATE[1]', 'varchar(10)'), 104)   AS DateEnd
                    FROM @Xml.nodes('NOSOLOGY/body/NOSOLOGYS/NOSOLOGY/NOSOLOGY_BENEFITS/NOSOLOGY_BENEFIT') T(c)
                    --

                END
            ELSE
                BEGIN
                    INSERT INTO
                        #NOSOLOGY (ID,
                                   Adress,
                                   Comment,
                                   DateOfChange,
                                   DateOfDeclusion,
                                   DateOfInclusion,
                                   ExecutiveAuthority,
                                   OmsNumber,
                                   OmsSerial,
                                   DocSeries,
                                   DocNumber,
                                   Birthday,
                                   FirstName,
                                   LastName,
                                   FatherName,
                                   DocIssueDate,
                                   DocIssueOrg,
                                   DeclusionLPU,
                                   DocumentType,
                                   ExemptionBy178,
                                   ExemptionBy890,
                                   InclusionLPU,
                                   region,
                                   sex,
                                   snils,
                                   BirthdayLastName,
                                   DeathDate,
                                   OKATO,
                                   F002Smo)
                    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]/EXECUTIVE_AUTHORITY[1]', 'varchar(256)')             AS ExecutiveAuthority,
                        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]/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_NAME[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]/DECLUSION_LPU[1]', 'varchar(6)')                     AS DeclusionLPU,
                        t.c.value('./body[1]/DOC_TYPE[1]', 'int')                                 AS DocumentType,
                        t.c.value('./body[1]/EX178[1]', 'int')                                    AS ExemptionBy178,
                        t.c.value('./body[1]/EX890[1]', 'int')                                    AS ExemptionBy890,
                        t.c.value('./body[1]/INCLUSION_LPU[1]', 'varchar(6)')                     AS InclusionLPU,
                        t.c.value('./body[1]/REGION[1]', 'varchar(13)')                           AS Region,
                        t.c.value('./body[1]/SEX[1]', 'int')                                      AS Sex,
                        t.c.value('./body[1]/SNILS[1]', 'varchar(14)')                            AS SNILS,
                        t.c.value('./body[1]/BIRTHDAY_LAST_NAME[1]', 'varchar(60)')               AS BirthdayLastName,
                        CONVERT(DATETIME, t.c.value('./body[1]/DEATH_DATE[1]', 'varchar(10)'),
                                          104)                                                    AS DeathDate,
                        t.c.value('./body[1]/OKATO[1]', 'varchar(max)')                           AS OKATO,
                        t.c.value('./body[1]/SMO[1]', 'varchar(5)')                               AS F002Smo
                    FROM @Xml.nodes('NOSOLOGY') T(c)

                    INSERT INTO
                        #NOSOLOGY_BENEFITS(NosologyID,
                                           CardStatus,
                                           TherapyResistance,
                                           Head,
                                           Mkb,
                                           Diagnosis,
                                           UniqueNumber,
                                           Disability,
                                           BenefitCode,
                                           DateBegin,
                                           EndDate)
                    SELECT
                        t.c.value('./NOSOLOGYID[1]', 'uniqueidentifier')                    AS NosologyID,
                        t.c.value('./CARD_STATUS[1]', 'varchar(256)')                       AS CardStatus,
                        t.c.value('./THERAPY_RESISTANCE[1]', 'int')                         AS TherapyResistance,
                        t.c.value('./HEAD[1]', 'varchar(256)')                              AS Head,
                        t.c.value('./MKB[1]', 'varchar(10)')                                AS Mkb,
                        t.c.value('./DIAGNOSIS[1]', 'varchar(4096)')                        AS Diagnosis,
                        t.c.value('./UNIQUE_NUMBER[1]', 'varchar(128)')                     AS UniqueNumber,
                        t.c.value('./DIASBILITY[1]', 'int')                                 AS Disability,
                        t.c.value('./BENEFIT_CODE[1]', 'varchar(255)')                      AS BenefitCode,
                        CONVERT(DATETIME, t.c.value('./DATE_BEGIN[1]', 'varchar(10)'), 104) AS DateBegin,
                        CONVERT(DATETIME, t.c.value('./END_DATE[1]', 'varchar(10)'), 104)   AS DateEnd
                    FROM @Xml.nodes('NOSOLOGY/body/NOSOLOGY_BENEFITS/NOSOLOGY_BENEFIT') T(c)
                END


            -- Связываем регистр с пациентами
            -- Поиск по СНИЛС + ДР
            UPDATE nos
            SET
                nos.Patient = p.PatientID
            FROM #NOSOLOGY 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 #NOSOLOGY 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 #NOSOLOGY 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 = @FTLOAD)
                BEGIN

                    DELETE pd
                    FROM AKUZ.T_PATIENT_BENEFITS pd
                    JOIN AKUZ.T_BENEFITS b ON b.BenefitsID = pd.Benefits
                    WHERE
                          b.BenefitViews = 19006
                      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 = nb.BenefitCode
                           AND b.BenefitViews = 19006),
                        NULL,
                        NULL,
                        nb.DateBegin,
                        nb.EndDate,
                        nb.Mkb,
                        1,
                        0,
                        2,
                        nos.UniqueNumber,
                        nos.InclusionLPU,
                        NULL,
                        NULL,
                        NULL,
                        NULL,
                        NULL,
                        nos.DateOfInclusion
                    FROM #NOSOLOGY nos
                    RIGHT JOIN #NOSOLOGY_BENEFITS nb ON nb.NosologyID = nos.ID
                    WHERE
                        nos.Patient IS NOT NULL
                END
            ELSE
                IF (@OperationType = @DELETE)
                    BEGIN

                        DELETE pd
                        FROM AKUZ.T_PATIENT_BENEFITS pd
                        JOIN AKUZ.T_BENEFITS b ON b.BenefitsID = pd.Benefits
                        JOIN #NOSOLOGY nos ON nos.Patient = pd.Patient
                        RIGHT JOIN #NOSOLOGY_BENEFITS nb ON nb.NosologyID = nos.ID
                        WHERE
                              b.BenefitViews = 19006
                          AND pd.CreateType = 2

                    END
                ELSE
                    BEGIN
                        DELETE pd
                        FROM AKUZ.T_PATIENT_BENEFITS pd
                        JOIN AKUZ.T_BENEFITS b ON b.BenefitsID = pd.Benefits
                        JOIN #NOSOLOGY nos ON nos.Patient = pd.Patient
                        RIGHT JOIN #NOSOLOGY_BENEFITS nb ON nb.NosologyID = nos.ID
                        WHERE
                              b.BenefitViews = 19006
                          AND pd.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 = nb.BenefitCode
                               AND b.BenefitViews = 19006),
                            NULL,
                            NULL,
                            nb.DateBegin,
                            nb.EndDate,
                            nb.Mkb,
                            1,
                            0,
                            2,
                            nos.UniqueNumber,
                            nos.InclusionLPU,
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            NULL,
                            nos.DateOfInclusion
                        FROM #NOSOLOGY nos
                        RIGHT JOIN #NOSOLOGY_BENEFITS nb ON nb.NosologyID = nos.ID
                        WHERE
                            nos.Patient IS NOT NULL
                    END
        END
END