-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2022-03-16
-- Description: Загрузка молочного регистра.
-- =============================================
DROP PROCEDURE IF EXISTS sp_GetMilkRegister;

CREATE PROCEDURE sp_GetMilkRegister
(
	"Xml" text DEFAULT NULL
,	"IsNew" boolean DEFAULT '1'
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT NULL::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('/MILK_REGISTER/header[1]/TYPE[1]/text()',"Xml"::xml))[1]::varchar(36);

            -- Generate table TMP_MILK_REGISTER
	    	Drop Table If Exists TMP_MILK_REGISTER;
            Create Temp Table TMP_MILK_REGISTER
            as
            select (xpath('PATIENT/RegisterID[1]/text()', x))[1]::text::int                      RegisterID
                  ,(xpath('PATIENT/LastName[1]/text()', x))[1]::varchar(40)                             LastName
                  ,(xpath('PATIENT/FirstName[1]/text()', x))[1]::varchar(40)                            FirstName
                  ,(xpath('PATIENT/FatherName[1]/text()', x))[1]::varchar(40)                           FatherName
                  ,to_timestamp((xpath('PATIENT/Birthday[1]/text()', x))[1]::text, 'DD.MM.YYYY')  Birthday
                  ,(xpath('PATIENT/DocSeries[1]/text()', x))[1]::varchar(16)                            DocSeries
                  ,(xpath('PATIENT/DocNumber[1]/text()', x))[1]::varchar(16)                            DocNumber
                  ,(xpath('PATIENT/Sex[1]/text()', x))[1]::text::int                             Sex
                  ,(xpath('PATIENT/Snils[1]/text()', x))[1]::varchar(14)                                Snils
                  ,(xpath('PATIENT/DocType[1]/text()', x))[1]::text::int                         DocType
	    		  ,(xpath('PATIENT/Polis[1]/text()', x))[1]::varchar(255) 								 Polis
	    		  ,(xpath('PATIENT/OKATO[1]/text()', x))[1]::varchar(255)								 OKATO
	    		  ,(xpath('PATIENT/City[1]/text()', x))[1]::varchar(255)							     City
	    		  ,(xpath('PATIENT/LHouse[1]/text()', x))[1]::varchar(255)								 LHouse
	    		  ,(xpath('PATIENT/Block[1]/text()', x))[1]::varchar(255)							     Block
	    		  ,(xpath('PATIENT/Flat[1]/text()', x))[1]::varchar(255)								 Flat
	    		  ,(xpath('PATIENT/LFlat[1]/text()', x))[1]::varchar(255)								 LFlat
	    		  ,(xpath('PATIENT/Room[1]/text()', x))[1]::varchar(255)							     Room
	    		  ,(xpath('PATIENT/House[1]/text()', x))[1]::varchar(255)								 House
	    		  ,(xpath('PATIENT/Street[1]/text()', x))[1]::varchar(255)								 Street
	    		  ,(xpath('PATIENT/ExcludeReason[1]/text()', x))[1]::text::int                   ExcludeReason
                  ,(xpath('PATIENT/PatientId[1]/text()', x))[1]::text::UUID                             PatientId
            from
        	(
        		select	unnest(xpath('MILK_REGISTER/body[1]/PATIENT', x)) as x
        		from 
        		(
        			select "Xml"::xml as x
        		) root
        	) PATIENT;	

            -- Generate table TMP_MILK_BENEFITS
	    	Drop Table If Exists TMP_MILK_BENEFITS;
            Create Temp Table TMP_MILK_BENEFITS
            as
            select (xpath('BENEFITS/RegisterID[1]/text()', x))[1]::text::int                        RegisterID
                  ,to_timestamp((xpath('BENEFITS/DiagDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')    DiagDate
                  ,(xpath('BENEFITS/Mkb[1]/text()', x))[1]::varchar(8)                                     Mkb
                  ,(xpath('BENEFITS/BenefitCode[1]/text()', x))[1]::varchar(255)                           BenefitCode
                  ,(xpath('BENEFITS/BenefitViews[1]/text()', x))[1]::text::int                      BenefitViews
                  ,to_timestamp((xpath('BENEFITS/BeginDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')   BeginDate
                  ,to_timestamp((xpath('BENEFITS/EndDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')     EndDate
            from
        	(
        		select	unnest(xpath('MILK_REGISTER/body[1]/PATIENT/BENEFITS', x)) as x
        		from 
        		(
        			select "Xml"::xml as x
        		) root
        	) BENEFITS;

            -- Обработка операции
            if (_OperationType = 'FTLoad')
            Then
                -- Связываем регистр с пациентами
                -- Поиск по СНИЛС + ДР
                update TMP_MILK_REGISTER pr
                set PatientId = p.PatientID
                from AKUZ.T_PATIENT p
                where p.SNILS = pr.Snils and p.Birthday = pr.Birthday and
	    			pr.Snils is not null
	    			and pr.Birthday is not null;

                -- Поиск по серии и номеру документа
                update TMP_MILK_REGISTER pr
                set PatientId = p.PatientID
                from AKUZ.T_PATIENT p
                where LEFT(p.DocSeries, 8) = pr.DocSeries and LEFT(p.DocNumber, 8) = pr.DocNumber and p.Birthday = pr.Birthday and
	    			pr.PatientId is null
	    			and pr.DocSeries is not null
                    and pr.DocNumber is not null;

                -- Поиск по ФИО + ДР
                update TMP_MILK_REGISTER pr
                set PatientId = p.PatientID
                from AKUZ.T_PATIENT p
                where  p.LastName = pr.LastName and p.FirstName = pr.FirstName and (p.FatherName = pr.FatherName or p.FatherName is null and pr.FatherName is null) and p.Birthday = pr.Birthday and pr.PatientId is null;

	    		delete from akuz.T_PATIENT_BENEFITS pb 
	    		    using AKUZ.T_BENEFITS b
	    		where b.BenefitsID = pb.Benefits and b.BenefitViews = 19099;

                -- Загружаем новые
                INSERT INTO AKUZ.T_PATIENT_BENEFITS
                           (PatientBenefitsID ,Patient ,Benefits ,BeginDate ,EndDate ,Mkb ,Active
                           ,Deleted ,CreateType ,RegDate, PAC_ID)
                SELECT
                           gen_random_uuid()--<PatientBenefitsID, uniqueidentifier,>
                           ,p.PatientID--<Patient, uniqueidentifier,>
                           ,b.BenefitsID--<Benefits, uniqueidentifier,>
                           ,pb.BeginDate--<BeginDate, datetime,>
                           ,pb.EndDate--<EndDate, datetime,>
                           ,(select m.MkbID from AKUZ.T_MKB m where m.MCBCode = pb.Mkb limit 1)--<Mkb, uniqueidentifier,>
                           ,'1'--<Active, bit,>
                           ,'0'--<Deleted, bit,>
                           ,2--<CreateType, int,>
                           ,"dateTime"--<RegDate, datetime,>)
	    				   ,pb.RegisterID
                from TMP_MILK_BENEFITS pb
                    join TMP_MILK_REGISTER pr on pr.RegisterID = pb.RegisterID
                    join AKUZ.T_PATIENT p on p.PatientID = pr.PatientId
                    join VCLib.T_ENUMS eBv on eBv.Code = pb.BenefitViews and eBv.EnumTypeID = 190
                    join AKUZ.T_BENEFITS b on b.BenefitCode = pb.BenefitCode and b.BenefitViews = eBv.EnumID;
            END if;

            -- Drop tables
            DROP TABLE TMP_MILK_REGISTER;
            DROP TABLE TMP_MILK_BENEFITS;
            end if;
    End;
End;
$$;