IF EXISTS (SELECT *
           FROM sys.objects
           WHERE
                 object_id = OBJECT_ID(N'[dbo].[sp_GetSetNosology]')
             AND type IN (N'P', N'PC'))
    DROP PROCEDURE dbo.sp_GetSetNosology
GO

IF EXISTS (SELECT *
           FROM sys.objects
           WHERE
                 object_id = OBJECT_ID(N'[APS].[sp_GetSetNosology]')
             AND type IN (N'P', N'PC'))
    DROP PROCEDURE APS.sp_GetSetNosology
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-17
-- Description: Загрузка льготников по 7 ноз из RDS
-- =============================================
CREATE PROCEDURE APS.sp_GetSetNosology @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,
                DateOfChange         DATETIME,
                Adress               VARCHAR(256),
                AutoDateOfChange     DATETIME,
                Comment              VARCHAR(1024),
                DateOfDeclusion      DATETIME,
                DateOfInclusion      DATETIME,
                ExecutiveAuthority   VARCHAR(256),
                OmsNumber            VARCHAR(30),
                OmsSerial            VARCHAR(30),
                OperationCode        VARCHAR(128),
                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,
                SubjectOfRFAccounted INT,
                SNILS                VARCHAR(14),
                BirthdayLastName     VARCHAR(60),
                DeathDate            DATETIME,
                OKATO                VARCHAR(MAX),
                F002Smo              VARCHAR(5)
            );

            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,
                                   AutoDateOfChange,
                                   Comment,
                                   DateOfChange,
                                   DateOfDeclusion,
                                   DateOfInclusion,
                                   ExecutiveAuthority,
                                   OmsNumber,
                                   OmsSerial,
                                   OperationCode,
                                   DocSeries,
                                   DocNumber,
                                   Birthday,
                                   FirstName,
                                   LastName,
                                   FatherName,
                                   DocIssueDate,
                                   DocIssueOrg,
                                   DeclusionLPU,
                                   DocumentType,
                                   ExemptionBy178,
                                   ExemptionBy890,
                                   InclusionLPU,
                                   Region,
                                   Sex,
                                   SubjectOfRFAccounted,
                                   SNILS,
                                   BirthdayLastName,
                                   DeathDate,
                                   OKATO,
                                   F002Smo)
                    SELECT
                        t.c.value('./ID[1]', 'uniqueidentifier')                                  AS ID,
                        t.c.value('./ADRESS[1]', 'varchar(256)')                                  AS Adress,
                        CONVERT(DATETIME, t.c.value('./AUTO_DATE_CHANGE[1]', 'varchar(10)'), 104) AS AutoDateOfChange,
                        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('./OPERATION_CODE[1]', 'varchar(128)')                          AS OperationCode,
                        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('./RF_ACCOUNT[1]', 'int')                                       AS SubjectOfRFAccounted,
                        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,
                                   AutoDateOfChange,
                                   Comment,
                                   DateOfChange,
                                   DateOfDeclusion,
                                   DateOfInclusion,
                                   ExecutiveAuthority,
                                   OmsNumber,
                                   OmsSerial,
                                   OperationCode,
                                   DocSeries,
                                   DocNumber,
                                   Birthday,
                                   FirstName,
                                   LastName,
                                   FatherName,
                                   DocIssueDate,
                                   DocIssueOrg,
                                   DeclusionLPU,
                                   DocumentType,
                                   ExemptionBy178,
                                   ExemptionBy890,
                                   InclusionLPU,
                                   Region,
                                   Sex,
                                   SubjectOfRFAccounted,
                                   SNILS,
                                   BirthdayLastName,
                                   DeathDate,
                                   OKATO,
                                   F002Smo)
                    SELECT
                        t.c.value('./body[1]/ID[1]', 'uniqueidentifier')                          AS ID,
                        t.c.value('./body[1]/ADRESS[1]', 'varchar(256)')                          AS Adress,
                        CONVERT(DATETIME, t.c.value('./body[1]/AUTO_DATE_CHANGE[1]', 'varchar(10)'),
                                          104)                                                    AS AutoDateOfChange,
                        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]/OPERATION_CODE[1]', 'varchar(128)')                  AS OperationCode,
                        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]/RF_ACCOUNT[1]', 'int')                               AS SubjectOfRFAccounted,
                        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

            IF (@OperationType = @FTLOAD)
                BEGIN

                    DELETE FROM APS.T_NOSOLOGY

                    INSERT INTO
                        APS.T_NOSOLOGY
                    (NosologyID,
                     Adress,
                     AutoDateOfChange,
                     CardStatus,
                     Comment,
                     DateOfChange,
                     DateOfDeclusion,
                     DateOfInclusion,
                     ExecutiveAuthority,
                     Head,
                     OmsNumber,
                     OmsSerial,
                     OperationCode,
                     UniqueNumber,
                     Mkb,
                     Diagnosis,
                     DocSeries,
                     DocNumber,
                     Birthday,
                     FirstName,
                     LastName,
                     FatherName,
                     DocIssueDate,
                     DocIssueOrg,
                     DeclusionLPU,
                     DocumentType,
                     ExemptionBy178,
                     ExemptionBy890,
                     InclusionLPU,
                     Region,
                     Sex,
                     SubjectOfRFAccounted,
                     SNILS,
                     Benefits,
                     BirthdayLastName,
                     Disability,
                     TherapyResistance,
                     DeathDate,
                     OKATO,
                     F002Smo)
                    SELECT
                        NEWID(),
                        nos.Adress,
                        nos.AutoDateOfChange,
                        nb.CardStatus,
                        nos.Comment,
                        nos.DateOfChange,
                        nb.EndDate,
                        nb.DateBegin,
                        nos.ExecutiveAuthority,
                        nb.Head,
                        nos.OmsNumber,
                        nos.OmsSerial,
                        nos.OperationCode,
                        nb.UniqueNumber,
                        nb.Mkb,
                        nb.Diagnosis,
                        nos.DocSeries,
                        nos.DocNumber,
                        nos.Birthday,
                        nos.FirstName,
                        nos.LastName,
                        nos.FatherName,
                        nos.DocIssueDate,
                        nos.DocIssueOrg,
                        nos.DeclusionLPU,
                        edoc.EnumID,
                        nos.ExemptionBy178,
                        nos.ExemptionBy890,
                        nos.InclusionLPU,
                        (SELECT TOP 1 r.RegionID FROM APS.T_REGION r WHERE r.Code = nos.Region),
                        eSex.EnumID,
                        nos.SubjectOfRFAccounted,
                        nos.SNILS,
                        (SELECT TOP 1
                             b.BenefitsID
                         FROM APS.T_BENEFITS b
                         WHERE
                               b.BenefitCode = nb.BenefitCode
                           AND b.BenefitViews = 19006),
                        nos.BirthdayLastName,
                        ISNULL(nb.Disability, 0),
                        nb.TherapyResistance,
                        nos.DeathDate,
                        nos.OKATO,
                        nos.F002Smo
                    FROM #NOSOLOGY nos
                    LEFT JOIN VCLIB.T_ENUMS eDoc ON eDoc.EnumTypeID = 105 AND eDoc.Code = nos.DocumentType
                    LEFT JOIN VCLIB.T_ENUMS eSex ON eSex.EnumTypeID = 103 AND eSex.Code = nos.Sex
                    RIGHT JOIN #NOSOLOGY_BENEFITS nb ON nb.NosologyID = nos.ID

                END
            ELSE
                IF (@OperationType = @DELETE)
                    BEGIN

                        DELETE n
                        FROM APS.T_NOSOLOGY n
                        JOIN #NOSOLOGY nos ON nos.SNILS = n.SNILS

                    END
                ELSE
                    BEGIN

                        DELETE n
                        FROM APS.T_NOSOLOGY n
                        JOIN #NOSOLOGY nos ON nos.SNILS = n.SNILS

                        INSERT INTO
                            APS.T_NOSOLOGY
                        (NosologyID,
                         Adress,
                         AutoDateOfChange,
                         CardStatus,
                         Comment,
                         DateOfChange,
                         DateOfDeclusion,
                         DateOfInclusion,
                         ExecutiveAuthority,
                         Head,
                         OmsNumber,
                         OmsSerial,
                         OperationCode,
                         UniqueNumber,
                         Mkb,
                         Diagnosis,
                         DocSeries,
                         DocNumber,
                         Birthday,
                         FirstName,
                         LastName,
                         FatherName,
                         DocIssueDate,
                         DocIssueOrg,
                         DeclusionLPU,
                         DocumentType,
                         ExemptionBy178,
                         ExemptionBy890,
                         InclusionLPU,
                         Region,
                         Sex,
                         SubjectOfRFAccounted,
                         SNILS,
                         Benefits,
                         BirthdayLastName,
                         Disability,
                         TherapyResistance,
                         DeathDate,
                         OKATO,
                         F002Smo)
                        SELECT
                            NEWID(),
                            nos.Adress,
                            nos.AutoDateOfChange,
                            nb.CardStatus,
                            nos.Comment,
                            nos.DateOfChange,
                            nb.EndDate,
                            nb.DateBegin,
                            nos.ExecutiveAuthority,
                            nb.Head,
                            nos.OmsNumber,
                            nos.OmsSerial,
                            nos.OperationCode,
                            nb.UniqueNumber,
                            nb.Mkb,
                            nb.Diagnosis,
                            nos.DocSeries,
                            nos.DocNumber,
                            nos.Birthday,
                            nos.FirstName,
                            nos.LastName,
                            nos.FatherName,
                            nos.DocIssueDate,
                            nos.DocIssueOrg,
                            nos.DeclusionLPU,
                            eDoc.EnumID,
                            nos.ExemptionBy178,
                            nos.ExemptionBy890,
                            nos.InclusionLPU,
                            (SELECT TOP 1 r.RegionID FROM APS.T_REGION r WHERE r.Code = nos.Region),
                            eSex.EnumID,
                            nos.SubjectOfRFAccounted,
                            nos.SNILS,
                            (SELECT TOP 1
                                 b.BenefitsID
                             FROM APS.T_BENEFITS b
                             WHERE
                                   b.BenefitCode = nb.BenefitCode
                               AND b.BenefitViews = 19006),
                            nos.BirthdayLastName,
                            ISNULL(nb.Disability, 0),
                            nb.TherapyResistance,
                            nos.DeathDate,
                            nos.OKATO,
                            nos.F002Smo
                        FROM #NOSOLOGY nos
                        LEFT JOIN VCLIB.T_ENUMS eDoc ON eDoc.EnumTypeID = 105 AND eDoc.Code = nos.DocumentType
                        LEFT JOIN VCLIB.T_ENUMS eSex ON eSex.EnumTypeID = 103 AND eSex.Code = nos.Sex
                        RIGHT JOIN #NOSOLOGY_BENEFITS nb ON nb.NosologyID = nos.ID
                        WHERE
                            NOT EXISTS
                                (SELECT *
                                 FROM APS.T_NOSOLOGY N
                                 WHERE
                                     N.SNILS = nos.SNILS)
                    END
        END
END
GO