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

CREATE PROCEDURE sp_GetSetLpuOverRequest
(
	"Xml" text DEFAULT NULL
,	"IsNew" boolean DEFAULT '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';
			
			-- Обновляем существующие
			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);
			
			-- Удаляем временную таблицу
			DROP TABLE if exists Tmp_LPU_OVER_REQUEST;
		END if;

    End;
End;
$$;
