-- ===================================================
-- Author:      Vitacore (с)
-- Create date: 2019-04-23
-- Description: Синхронизация дополнительных факторов заявочного каталога
-- ===================================================
DROP PROCEDURE IF EXISTS sp_getsetadditionalfactorsrequestcatalog;

CREATE PROCEDURE sp_getsetadditionalfactorsrequestcatalog(
    "Xml" text DEFAULT NULL
, "IsNew" integer = 1
, "ID" uuid DEFAULT NULL::uuid
, "QueueName" character varying DEFAULT 'RDSSync'::character varying
, "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
, "User" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    DECLARE
        _operationtype varchar(255);
    BEGIN
        IF ("Xml" IS NOT NULL)
        THEN
            -- Получаем тип операции
            SELECT INTO
                _operationtype (XPATH('/ADDITIONAL_FACTORS_REQUEST_CATALOG/header[1]/TYPE[1]/text()',
                                      "Xml"::xml))[1]::varchar(36);
            
            -- Generate table TMP_ADDITIONAL_FACTORS_REQUEST_CATALOG
            DROP TABLE IF EXISTS tmp_additional_factors_request_catalog;
            CREATE TEMP TABLE tmp_additional_factors_request_catalog
            AS
                SELECT (XPATH('ADDITIONAL_FACTORS_REQUEST_CATALOG/ID[1]/text()', x))[1]::text::uuid            id
                     , (XPATH('ADDITIONAL_FACTORS_REQUEST_CATALOG/DrugApplicationListItem[1]/text()',
                              x))[1]::text::uuid                                                               requestcatalog
                     , (XPATH('ADDITIONAL_FACTORS_REQUEST_CATALOG/BenefitViews[1]/text()', x))[1]::text::int   benefitviews
                     , (XPATH('ADDITIONAL_FACTORS_REQUEST_CATALOG/BenefitCode[1]/text()', x))[1]::varchar(255) benefitcode
                FROM (SELECT UNNEST(XPATH(
                    'ADDITIONAL_FACTORS_REQUEST_CATALOG/body/ADDITIONAL_FACTORS_REQUEST_CATALOGS/ADDITIONAL_FACTORS_REQUEST_CATALOG',
                    x)) AS x
                      FROM (SELECT "Xml"::xml AS x
                           ) root
                     ) additional_factors_request_catalog
                WHERE _operationtype = 'FTLoad';
            
            ANALYZE tmp_additional_factors_request_catalog;
            
            -- Удаляем отсутствующие
            DELETE FROM akuz.t_additional_factors_request_catalog;
            
            -- Добавляем новые
            INSERT INTO
                akuz.t_additional_factors_request_catalog
            (additionalfactorsrequestcatalogid, requestcatalog, additionalfinanc, palliative, benefits)
            SELECT adrc.id,
                   adrc.requestcatalog,
                   b.benefitsid
            FROM tmp_additional_factors_request_catalog adrc
            LEFT JOIN akuz.t_benefits                   b
                          ON b.benefitcode = adrc.benefitcode AND b.benefitviews = adrc.benefitviews;
        
        END IF;
    END;
END;
$$;