IF EXISTS (SELECT *
           FROM sys.objects
           WHERE
                 object_id = OBJECT_ID(N'[dbo].[sp_GetSetNosologyList]')
             AND type IN (N'P', N'PC'))
    DROP PROCEDURE dbo.sp_GetSetNosologyList
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-17
-- Description: Выгрузка/загрузка регистра ВЗН льготников RDS
-- =============================================
CREATE PROCEDURE dbo.sp_GetSetNosologyList(
    @Xml xml = NULL,
    @IsNew BIT = 1,
    @ID UNIQUEIDENTIFIER = NULL,
    @QueueName VARCHAR(100) = NULL,
    @dateTime DATETIME = NULL,
    @User UNIQUEIDENTIFIER = NULL,
    @Snils VARCHAR(14) = NULL,
    @Birthday DATETIME = NULL,
    @DocSeries VARCHAR(30) = NULL,
    @DocNumber VARCHAR(30) = NULL,
    @FirstName VARCHAR(60) = NULL,
    @LastName VARCHAR(60) = NULL,
    @FatherName VARCHAR(60) = NULL,
    @PacId INT = 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 NULL)
        BEGIN
            -- Инициализация
            SELECT
                @ContentEncoding = 'utf-8',
                @Type = 'NOSOLOGY',
                @OwnerID = @ID

            --выясняем тип операции
            IF (@ID IS NULL)
                BEGIN
                    SELECT @OperationType = @FTLOAD
                END
            ELSE
                IF (@IsNew = 1)
                    BEGIN
                        SELECT @OperationType = @INSERT
                    END
                ELSE
                    IF (@IsNew = 0 AND EXISTS (SELECT * FROM RDS.T_NOSOLOGY n WHERE n.NosologyID = @ID))
                        BEGIN
                            SELECT @OperationType = @UPDATE
                        END
                    ELSE
                        IF (@IsNew = 0 AND NOT EXISTS (SELECT * FROM RDS.T_NOSOLOGY n WHERE n.NosologyID = @ID))
                            BEGIN
                                SELECT @OperationType = @DELETE
                                SELECT
                                    @Snils = CASE
                                                 WHEN RTRIM(LTRIM(REPLACE(REPLACE(@Snils, '0', ''), '-', ''))) = ''
                                                     THEN NULL
                                                 ELSE @Snils
                                        END

                                SELECT
                                    @Result =
                                    (SELECT
                                         @OperationType                       AS 'header/TYPE',
                                         @ID                                  AS 'header/ID',
                                         @Snils                               AS 'body/SNILS',
                                         CONVERT(VARCHAR(10), @Birthday, 104) AS 'body/BIRTHDAY',
                                         @DocSeries                           AS 'body/DOC_SERIES',
                                         @DocNumber                           AS 'body/DOC_NUMBER',
                                         @FirstName                           AS 'body/FIRST_NAME',
                                         @LastName                            AS 'body/LAST_NAME',
                                         @FatherName                          AS 'body/FATHER_NAME',
                                         @PacId                               AS 'body/PAC_ID'
                                     FOR XML PATH(''), ROOT('NOSOLOGY'))
                            END

            -- Обработка операции
            IF (@OperationType = @FTLOAD)
                BEGIN
                    SELECT
                        @Result =
                        (SELECT
                             @OperationType AS 'header/TYPE',
                             (SELECT
                                  (SELECT
                                       nos.NosologyID                                 AS ID,
                                       nos.Adress                                     AS ADRESS,
                                       CONVERT(VARCHAR(10), nos.DateOfChange, 104)    AS CHANGE_DATE,
                                       nos.PAC_ID                                     AS PAC_ID,
                                       nos.FirstName                                  AS FIRST_NAME,
                                       nos.LastName                                   AS LAST_NAME,
                                       nos.FatherName                                 AS FATHER_NAME,
                                       CONVERT(VARCHAR(10), nos.Birthday, 104)        AS BIRTHDAY,
                                       nos.BirthdayLastName                           AS BIRTHDAY_LAST_NAME,
                                       eSex.Code                                      AS SEX,
                                       CASE
                                           WHEN RTRIM(LTRIM(REPLACE(REPLACE(nos.SNILS, '0', ''), '-', ''))) = ''
                                               THEN NULL
                                           ELSE nos.SNILS
                                           END                                        AS SNILS,
                                       eDoc.Code                                      AS DOC_TYPE,
                                       nos.DocSeries                                  AS DOC_SERIES,
                                       nos.DocNumber                                  AS DOC_NUMBER,
                                       CONVERT(VARCHAR(10), nos.DocIssueDate, 104)    AS DOC_ISSUE_DATE,
                                       nos.DocIssueOrg                                AS DOC_ISSUE_ORG,
                                       nos.OmsSerial                                  AS OMS_SERIAL,
                                       nos.OmsNumber                                  AS OMS_NUMBER,
                                       nos.F002Smo                                    AS SMO,
                                       nos.OKATO                                      AS OKATO,
                                       CONVERT(VARCHAR(10), nos.DateOfInclusion, 104) AS INCLUSION_DATE,
                                       nos.InclusionLPU                               AS INCLUSION_LPU,
                                       nos.ExecutiveAuthority                         AS EXECUTIVE_AUTHORITY,
                                       CONVERT(VARCHAR(10), nos.DateOfDeclusion, 104) AS DECLUSION_DATE,
                                       nos.DeclusionLPU                               AS DECLUSION_LPU,
                                       nos.ExemptionBy178                             AS EX178,
                                       nos.ExemptionBy890                             AS EX890,
                                       nos.SubjectOfRFAccounted                       AS RF_ACCOUNT,
                                       CONVERT(VARCHAR(10), nos.DeathDate, 104)       AS DEATH_DATE,
                                       (SELECT
                                            nb.UniqueNumber                         AS UNIQUE_NUMBER,
                                            nos.NosologyID                          AS NOSOLOGYID,
                                            nb.Mkb                                  AS MKB,
                                            nb.CardStatus                           AS CARD_STATUS,
                                            nb.Head                                 AS HEAD,
                                            nb.Diagnosis                            AS DIAGNOSIS,
                                            nb.Disability                           AS DIASBILITY,
                                            nb.TherapyResistance                    AS THERAPY_RESISTANCE,
                                            b.BenefitCode                           AS BENEFIT_CODE,
                                            CONVERT(VARCHAR(10), nb.DateBegin, 104) AS DATE_BEGIN,
                                            CONVERT(VARCHAR(10), nb.EndDate, 104)   AS END_DATE
                                        FROM RDS.T_NOSOLOGY_BENEFITS nb
                                        JOIN RDS.T_BENEFITS b ON b.BenefitsID = nb.Benefits
                                        WHERE
                                            nb.Nosology = nos.NosologyID
                                        FOR XML PATH('NOSOLOGY_BENEFIT'), ROOT('NOSOLOGY_BENEFITS'), TYPE)
                                   FROM RDS.T_NOSOLOGY nos
                                   LEFT JOIN RDS.T_REGION reg ON reg.RegionID = nos.Region
                                   LEFT JOIN VCLib.T_ENUMS eSex ON eSex.EnumTypeID = 103 AND eSex.EnumID = NOS.Sex
                                   LEFT JOIN vclib.T_ENUMS eDoc
                                   ON eDoc.EnumTypeID = 105 AND eDoc.EnumID = nos.DocumentType
                                   FOR XML PATH('NOSOLOGY'), ROOT('NOSOLOGYS'), TYPE)
                              FOR XML PATH (''), ROOT ('body'), TYPE)
                         FOR XML PATH(''), ROOT('NOSOLOGY'))

                END
            ELSE
                IF (@OperationType <> @DELETE)
                    BEGIN
                        SELECT
                            @Result =
                            (SELECT
                                 @OperationType AS 'header/TYPE',
                                 @ID            AS 'header/ID',
                                 (SELECT
                                      nos.NosologyID                                 AS ID,
                                      CONVERT(VARCHAR(10), nos.DateOfChange, 104)    AS CHANGE_DATE,
                                      nos.PAC_ID                                     AS PAC_ID,
                                      nos.FirstName                                  AS FIRST_NAME,
                                      nos.LastName                                   AS LAST_NAME,
                                      nos.FatherName                                 AS FATHER_NAME,
                                      CONVERT(VARCHAR(10), nos.Birthday, 104)        AS BIRTHDAY,
                                      nos.BirthdayLastName                           AS BIRTHDAY_LAST_NAME,
                                      eSex.Code                                      AS SEX,
                                      CASE
                                          WHEN RTRIM(LTRIM(REPLACE(REPLACE(nos.SNILS, '0', ''), '-', ''))) = ''
                                              THEN NULL
                                          ELSE nos.SNILS
                                          END                                        AS SNILS,
                                      eDoc.Code                                      AS DOC_TYPE,
                                      nos.DocSeries                                  AS DOC_SERIES,
                                      nos.DocNumber                                  AS DOC_NUMBER,
                                      CONVERT(VARCHAR(10), nos.DocIssueDate, 104)    AS DOC_ISSUE_DATE,
                                      nos.DocIssueOrg                                AS DOC_ISSUE_ORG,
                                      nos.OmsSerial                                  AS OMS_SERIAL,
                                      nos.OmsNumber                                  AS OMS_NUMBER,
                                      nos.F002Smo                                    AS SMO,
                                      nos.Adress                                     AS ADRESS,
                                      nos.OKATO                                      AS OKATO,
                                      CONVERT(VARCHAR(10), nos.DateOfInclusion, 104) AS INCLUSION_DATE,
                                      nos.InclusionLPU                               AS INCLUSION_LPU,
                                      nos.ExecutiveAuthority                         AS EXECUTIVE_AUTHORITY,
                                      CONVERT(VARCHAR(10), nos.DateOfDeclusion, 104) AS DECLUSION_DATE,
                                      nos.DeclusionLPU                               AS DECLUSION_LPU,
                                      nos.ExemptionBy178                             AS EX178,
                                      nos.ExemptionBy890                             AS EX890,
                                      nos.SubjectOfRFAccounted                       AS RF_ACCOUNT,
                                      CONVERT(VARCHAR(10), nos.DeathDate, 104)       AS DEATH_DATE,
                                      (SELECT
                                           nb.UniqueNumber                         AS UNIQUE_NUMBER,
                                           nos.NosologyID                          AS NOSOLOGYID,
                                           nb.Mkb                                  AS MKB,
                                           nb.CardStatus                           AS CARD_STATUS,
                                           nb.Head                                 AS HEAD,
                                           nb.Diagnosis                            AS DIAGNOSIS,
                                           nb.Disability                           AS DIASBILITY,
                                           nb.TherapyResistance                    AS THERAPY_RESISTANCE,
                                           b.BenefitCode                           AS BENEFIT_CODE,
                                           CONVERT(VARCHAR(10), nb.DateBegin, 104) AS DATE_BEGIN,
                                           CONVERT(VARCHAR(10), nb.EndDate, 104)   AS END_DATE
                                       FROM RDS.T_NOSOLOGY_BENEFITS nb
                                       JOIN RDS.T_BENEFITS b ON b.BenefitsID = nb.Benefits
                                       WHERE
                                           nb.Nosology = nos.NosologyID
                                       FOR XML PATH('NOSOLOGY_BENEFIT'), ROOT('NOSOLOGY_BENEFITS'), TYPE)
                                  FROM RDS.T_NOSOLOGY nos
                                  LEFT JOIN RDS.T_REGION reg ON reg.RegionID = nos.Region
                                  LEFT JOIN VCLib.T_ENUMS eSex ON eSex.EnumTypeID = 103 AND eSex.EnumID = NOS.Sex
                                  LEFT JOIN vclib.T_ENUMS eDoc ON eDoc.EnumTypeID = 105
                                      AND eDoc.EnumID = nos.DocumentType
                                  WHERE
                                      nos.NosologyID = @ID
                                  FOR XML PATH ( '') , ROOT ( 'body') , TYPE)
                             FOR XML PATH(''), ROOT('NOSOLOGY'))
                    END

            -- Отправка всем остальным подписанным серверам AKUZ-а и APS
            INSERT INTO
                RDS.T_RABBIT_SEND_OBJECT
            (RabbitSendObjectID,
             QueueName,
             Content,
             MessageID,
             Headers,
             ContentEncoding,
             Type,
             UserID,
             State,
             CreateDateTime,
             OwnerID)
            SELECT
                NEWID(),                                 --(<RabbitSendObjectID, uniqueidentifier,>
                s.ServerName,                            --,<QueueName, varchar(255),>
                dbo.ConvertTextToBinaryZip(@Result, ''), --,<Content, text,>
                NULL,                                    --,<MessageID, varchar(255),>
                NULL,                                    --,<Headers, varchar(255),>
                @ContentEncoding,                        --,<ContentEncoding, varchar(255),>
                @Type,                                   --,<Type, varchar(255),>
                @User,                                   --,<UserID, varchar(255),>
                0,                                       --,<State, int,>)
                @dateTime,                               --,<CreateDateTime, datetime,>)
                @OwnerID--,<OwnerID, varchar(255),>
            FROM RDS.T_SYNC_SERVERS s
            WHERE
                 (@QueueName IS NOT NULL AND s.ServerName = @QueueName)
              OR (@QueueName IS NULL AND s.ServerType IN (0, 1)) -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
        END
END
GO

