-- ===================================================
-- Author:      Vitacore (с)
-- Create date: 2016-02-29
-- Description: Синхронизация превышения заявки по ЛПУ
-- ===================================================
DROP PROCEDURE IF EXISTS sp_getsetlpuoverrequest;

CREATE PROCEDURE sp_getsetlpuoverrequest(
    "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
            -- Получаем тип операции
            _operationtype := NULLIF(TRIM(XPATH('/LPU_OVER_REQUEST/header[1]/TYPE[1]/text()', "Xml"::xml)::text, '{}"'),
                                     '')::varchar(36);
            
            -- Создаём временную таблицу из xml
            DROP TABLE IF EXISTS tmp_lpu_over_request;
            CREATE TEMP TABLE tmp_lpu_over_request --(ID UUID, DrugApplicationListItemID UUID, LpuMainID varchar(6));
            AS
                SELECT NULLIF(TRIM(XPATH('/LPU_OVER_REQUEST/ID[1]/text()', x)::text, '{}"'), '')::uuid AS id
                     , NULLIF(TRIM(XPATH('/LPU_OVER_REQUEST/DrugApplicationListItemID[1]/text()', x)::text, '{}"'),
                              '')::uuid                                                                AS drugapplicationlistitemid
                     , NULLIF(TRIM(XPATH('/LPU_OVER_REQUEST/LpuMainID[1]/text()', x)::text, '{}"'),
                              '')::varchar(6)                                                          AS lpumainid
                FROM (SELECT UNNEST(XPATH('/LPU_OVER_REQUEST/body/LPU_OVER_REQUESTS/LPU_OVER_REQUEST', x)) AS x
                      FROM (SELECT "Xml"::xml AS x
                           ) root
                     ) lpu_over_request
                WHERE _operationtype = 'FTLoad';
            
            ANALYZE tmp_lpu_over_request;
            
            -- Обновляем существующие
            UPDATE akuz.t_lpu_over_request lor
            SET requestcatalog = tmp.drugapplicationlistitemid
              , lpumain        = lm.lpuid
            FROM tmp_lpu_over_request tmp
            JOIN akuz.t_lpu_main      lm ON tmp.lpumainid = lm.lpucode
            WHERE tmp.id = lor.lpuoverrequestid;
            
            -- Добавляем новые
            INSERT INTO
                akuz.t_lpu_over_request(lpuoverrequestid, requestcatalog, lpumain)
            SELECT tmp.id, tmp.drugapplicationlistitemid, lm.lpuid
            FROM tmp_lpu_over_request AS tmp
            JOIN akuz.t_lpu_main      AS lm ON tmp.lpumainid = lm.lpucode
            WHERE NOT EXISTS(SELECT lor.lpuoverrequestid
                             FROM akuz.t_lpu_over_request AS lor
                             WHERE lor.lpuoverrequestid = tmp.id
                            );
            
            -- Удаляем отсутствующие
            DELETE
            FROM akuz.t_lpu_over_request lor
            WHERE NOT EXISTS(SELECT tmp.id FROM tmp_lpu_over_request AS tmp WHERE tmp.id = lor.lpuoverrequestid);
        
        END IF;
    
    END;
END;
$$;
