-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-04-01
-- Description: Загрузка регистра БСК.
-- =============================================
DROP PROCEDURE IF EXISTS sp_GetBSKRegister;

CREATE PROCEDURE sp_GetBSKRegister
(
	"Xml" text DEFAULT NULL
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT 'rds.registry'::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(36);
    Begin
        IF ("Xml" is not null)
        then
	    	"dateTime" := coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
            
            -- Получаем тип операции		
			select into
				_OperationType
				(xpath('/BSK_REGISTER/header[1]/TYPE[1]/text()',"Xml"::xml))[1]::varchar(36);

            -- Generate table Tmp_BSK_REGISTER
			Drop Table If Exists Tmp_BSK_REGISTER;
            Create Temp Table Tmp_BSK_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, 'YYYY.MM.DD')              Birthday
                  ,(xpath('PATIENT/Kod[1]/text()', x))[1]::varchar(32)		                            Kod
                  ,(xpath('PATIENT/DocSeries[1]/text()', x))[1]::varchar(8)                             DocSeries
                  ,(xpath('PATIENT/DocNumber[1]/text()', x))[1]::varchar(8)                             DocNumber
                  ,(xpath('PATIENT/Sex[1]/text()', x))[1]::text::int                                    Sex
                  ,(xpath('PATIENT/Snils[1]/text()', x))[1]::varchar(14)                                Snils
                  ,to_timestamp((xpath('PATIENT/DateBegin[1]/text()', x))[1]::text, 'YYYY.MM.DD')             DateBegin
                  ,to_timestamp((xpath('PATIENT/DateEnd[1]/text()', x))[1]::text, 'YYYY.MM.DD')               DateEnd
                  ,to_timestamp((xpath('PATIENT/LastEditDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')          LastEditDate
                  ,(xpath('PATIENT/Lpu[1]/text()', x))[1]::varchar(6)                                   Lpu
                  ,(xpath('PATIENT/ExcludeReason[1]/text()', x))[1]::text::int                          ExcludeReason
                  ,(xpath('PATIENT/DocType[1]/text()', x))[1]::text::int                                DocType
                  ,(xpath('PATIENT/PatientId[1]/text()', x))[1]::text::UUID                             PatientId
            from
    			(
				    select unnest(xpath('/BSK_REGISTER/body[1]/PATIENT', "Xml"::xml)) as x
    			) PATIENT;	

	    	
            -- Generate table Tmp_BSK_BENEFITS
			Drop Table If Exists Tmp_BSK_BENEFITS;
            Create Temp Table Tmp_BSK_BENEFITS
            as
            select (xpath('BENEFITS/RegisterID[1]/text()', x))[1]::text::int										RegisterID
                  ,(xpath('BENEFITS/BenID[1]/text()', x))[1]::text::UUID									        BenID
                  ,to_timestamp((xpath('BENEFITS/DiagDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')			    DiagDate
                  ,(xpath('BENEFITS/Document[1]/text()', x))[1]::varchar(16)									    Document
                  ,to_timestamp((xpath('BENEFITS/DocumentDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')         DocumentDate
                  ,(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, 'YYYY.MM.DD')		    BeginDate
                  ,to_timestamp((xpath('BENEFITS/EndDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')		        EndDate
                  ,(xpath('BENEFITS/Lpu[1]/text()', x))[1]::varchar(6)										        Lpu
                  ,(xpath('BENEFITS/ServiceCode[1]/text()', x))[1]::varchar(20)								    ServiceCode
                  ,(xpath('BENEFITS/ServiceName[1]/text()', x))[1]::varchar(255)								    ServiceName
                  ,(xpath('BENEFITS/ExclusionReason[1]/text()', x))[1]::text::int								    ExclusionReason
                  ,to_timestamp((xpath('BENEFITS/EstimatedDDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')       EstimatedDDate
                  ,to_timestamp((xpath('BENEFITS/DRegistrationDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')    DRegistrationDate
                  ,(xpath('BENEFITS/Place[1]/text()', x))[1]::text::int											Place
                  ,(xpath('BENEFITS/LpuTo[1]/text()', x))[1]::varchar(6)										    LpuTo
                  ,to_timestamp((xpath('BENEFITS/CloseDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')			CloseDate
            from
    			(
				    select unnest(xpath('/BSK_REGISTER/body[1]/PATIENT/BENEFITS', "Xml"::xml)) as x
    			) BENEFITS;	

	  --  	-- Generate table Tmp_BSK_MEDICAMENTS
			--Drop Table If Exists Tmp_BSK_MEDICAMENTS;
   --         Create Temp Table Tmp_BSK_MEDICAMENTS
   --         as
   --         select (xpath('MEDICAMENTS/BenID[1]/text()', x))[1]::text::UUID							    BenID
   --               ,(xpath('MEDICAMENTS/Mnn[1]/text()', x))[1]::text::int									Mnn
   --               ,to_timestamp((xpath('MEDICAMENTS/DateStart[1]/text()', x))[1]::text, 'YYYY.MM.DD') DateStart
   --               ,to_timestamp((xpath('MEDICAMENTS/EndDate[1]/text()', x))[1]::text, 'YYYY.MM.DD')   EndDate
   --               ,(xpath('MEDICAMENTS/MedicationForms[1]/text()', x))[1]::text::UUID				        MedicationForms
   --               ,(xpath('MEDICAMENTS/Regiment[1]/text()', x))[1]::varchar(255)                           Regiment
   --               ,(xpath('MEDICAMENTS/Dose[1]/text()', x))[1]::text::decimal(31,10)                             Dose
   --               ,(xpath('MEDICAMENTS/Prepid[1]/text()', x))[1]::text::int								Prepid
   --               ,(xpath('MEDICAMENTS/DayliCoeff[1]/text()', x))[1]::text::decimal(31,10)                       DayliCoeff
   --               ,(xpath('MEDICAMENTS/MeasureUnit[1]/text()', x))[1]::varchar(255)						MeasureUnit
   --               ,(xpath('MEDICAMENTS/MeasureUnitType[1]/text()', x))[1]::text::int						MeasureUnitType
   --               ,(xpath('MEDICAMENTS/SingleDose[1]/text()', x))[1]::text::decimal(31,10)                       SingleDose
   --               ,(xpath('MEDICAMENTS/Multiplicity[1]/text()', x))[1]::text::int							Multiplicity
   --               ,(xpath('MEDICAMENTS/DoseView[1]/text()', x))[1]::varchar(255)							DoseView
   --               ,(xpath('MEDICAMENTS/DoseView[1]/text()', x))[1]::text::int								DoseViewType
   --         from
   -- 			(
			--	    select unnest(xpath('/BSK_REGISTER/body[1]/PATIENT/BENEFITS/MEDICAMENTS', "Xml"::xml)) as x
   -- 			) MEDICAMENTS;	
                
            -- Обработка операции
            if (_OperationType = 'FTLoad')
            then
                -- Связываем регистр с пациентами
                -- Поиск по СНИЛС + ДР
                update Tmp_BSK_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_BSK_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 pr.PatientId is null 
	    			and pr.DocSeries is not null
                    and	pr.DocNumber is not null;

                -- Поиск по ФИО + ДР
                update Tmp_BSK_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 = 19007;

                -- Загружаем новые
                INSERT INTO AKUZ.T_PATIENT_BENEFITS
                           (PatientBenefitsID ,Patient ,Benefits ,BenefitDocumentNumber ,BeginDate ,EndDate ,Mkb ,Active
                           ,Deleted ,CreateType ,LPU_INF ,RegDate, PAC_ID)
                SELECT
                           gen_random_uuid()--<PatientBenefitsID, uniqueidentifier,>
                           ,p.PatientID--<Patient, uniqueidentifier,>
                           ,b.BenefitsID--<Benefits, uniqueidentifier,>
                           ,pb.Document--<BenefitDocumentNumber, varchar(255),>
                           ,pb.BeginDate--<BeginDate, datetime,>
                           ,coalesce(pb.CloseDate, 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,>
                           ,pb.Lpu--<LPU_INF, varchar(6),>
                           ,"dateTime"--<RegDate, datetime,>)
	    				   ,pb.RegisterID
                from Tmp_BSK_BENEFITS pb
                    join Tmp_BSK_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;
        end if;
    End;
End;
$$;