-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2023-06-16
-- Description: Отправка информации по направлениям в ВЗН регистр в RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_nosology_register_direction;

CREATE PROCEDURE sp_nosology_register_direction
(
	"Xml" xml DEFAULT NULL::xml
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" varchar(100) DEFAULT 'rds.registry'::varchar(100)
,	"dateTime" timestamp DEFAULT NULL::timestamp
,	"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
Begin

    Declare
        _Result text;
    	_ContentEncoding varchar(255) := 'utf-8';
    	_Type varchar(255) := 'NOSOLOGY_DIRECTION';
    	_OwnerID UUID := "ID";
    Begin
        "dateTime" := coalesce("dateTime",current_timestamp::timestamp);
        
        if(not exists(select * from akuz.T_PATIENT_BENEFITS_DIRECTION where PatientBenefitsDirectionID = "ID"))
        then
		    RAISE EXCEPTION 'Направление не найдено!';
        end if;
    
        if "Xml" is null
        then
                _Result := (select XMLELEMENT
    		        (name "NOSOLOGY_DIRECTION" , xmlforest(
    		            pbd.PatientBenefitsDirectionID                      as "ID"
    		            ,lm.LpuCode								            as "LPU"
                        ,pbd.EnterDate::date::varchar                       as "EnterDate"
                        ,pbd.LastName                                       as "LastName"
                        ,pbd.FirstName                                      as "FirstName"
                        ,pbd.FatherName                                     as "FatherName"
    				    ,pbd.BirthSurname					                as "BirthSurname"
                        ,pbd.Birthday::date::varchar                        as "Birthday"
                        ,pbd.Date::date::varchar                            as "Date"
                        ,e.Code                                             as "Sex"
                        ,pbd.Kod                                            as "Kod"
                        ,pbd.DocSeries							            as "DocSeries"
                        ,pbd.DocNumber							            as "DocNumber"
    				    ,eDt.Code									        as "DocType"
                        ,pbd.PAC_ID                                         as "RegisterID"
                        ,pbd.SNILS								            as "SNILS"
                        ,pbd.ChangeType					                    as "ChangeType"
                        ,rbd.ExcludeReason                                  as "ExcludeReason"
                        ,pbd.DocIssueDate::date::varchar                    as "DocIssueDate"
                        ,pbd.DocIssueOrg								    as "DocIssueOrg"
                        ,pbd.Enp									        as "Enp"
                        ,pbd.RealFiasAddress						        as "RealFiasAddress"
                        ,pbd.RealFiasHome							        as "RealFiasHome"
                        ,pbd.RealCity								        as "RealCity"
                        ,pbd.RealCityPrefix						            as "RealCityPrefix"
                        ,pbd.RealStreet							            as "RealStreet"
                        ,pbd.RealStreetPrefix						        as "RealStreetPrefix"
                        ,pbd.RealHouse							            as "RealHouse"
                        ,pbd.RealBuild							            as "RealBuild"
                        ,pbd.RealStruc							            as "RealStruc"
                        ,pbd.RealRoom								        as "RealRoom"
                        ,pbd.RealAOIDArea							        as "RealAOIDArea"
                        ,pbd.RealLiveStartDate::date::varchar               as "RealLiveStartDate"
                        ,pbd.RealLiveEndDate::date::varchar                 as "RealLiveEndDate"
                        ,pbd.LegalFiasAddress						        as "LegalFiasAddress"
                        ,pbd.LegalFiasHome						            as "LegalFiasHome"
                        ,pbd.LegalCity							            as "LegalCity"
                        ,pbd.LegalCityPrefix						        as "LegalCityPrefix"
                        ,pbd.LegalStreet							        as "LegalStreet"
                        ,pbd.LegalStreetPrefix					            as "LegalStreetPrefix"
                        ,pbd.LegalHouse							            as "LegalHouse"
                        ,pbd.LegalBuild							            as "LegalBuild"
                        ,pbd.LegalStruc							            as "LegalStruc"
                        ,pbd.LegalRoom							            as "LegalRoom"
                        ,pbd.LegalAOIDArea							        as "RealAOIDArea"
                        ,pbd.LegalLiveStartDate::date::varchar              as "LegalLiveStartDate"
                        ,pbd.LegalLiveEndDate::date::varchar                as "LegalLiveEndDate"
                        ,cs.CODE									        as "CitizenShip"
                        ,pbd.PolicySerial							        as "PolicySerial"
                        ,pbd.PolicyNumber							        as "PolicyNumber"
                        ,pbd.SmoCod								            as "SmoCod"
                        ,pbd.PolicyForm							            as "PolicyForm"
                        ,rbd.WorkPlace							            as "WorkPlace"
                        ,rbd.WorkPost								        as "WorkPost"
                        ,rbd.RationaleForDirection				            as "RationaleForDirection"
                        ,rbd.Number								            as "Number"
                        ,rbd.FedReg								            as "FedReg"
                        ,rbd.FedRegCategoria						        as "FedRegCategoria"
                        ,rbd.LLOReg								            as "LLOReg"
                        ,rbd.Invalidity							            as "Invalidity"
                        ,wPost.FIO							                as "PostFIO"
                        ,wHeadPost.FIO						                as "HeadPostFIO"
                        ,wMcPost.FIO							            as "McPostFIO"
                        -- собираем льготы
    				    ,(SELECT XMLAGG(
                            (xmlforest(
                                     rbd.PatientBenefitsDirectionID             as "DirectionID"
                                    , cast(pbdd.DiagDate as varchar(10))        as "DiagDate"
                                    , pbdd.Document                             as "Document"
                                    , cast(pbdd.DocumentDate as varchar(10))    as "DocumentDate"
                                    , pbdd.Mkb                                  as "Mkb"
                                    , b.BenefitCode                             as "BenefitCode"
                                    , ebb.Code                                  as "BenefitViews"
                                    , post.Code                                 as "PostCode"
                                    , pbdd.ChangeType                           as "ChangeType"
                                    )
                                ))
                           from AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA pbdd
                                    join AKUZ.T_BENEFITS b on b.BenefitsID = pbdd.Benefits
                                    join VCLib.T_ENUMS ebb on ebb.EnumID = b.BenefitViews
                                    left join AKUZ.T_POST post on post.ResourceID = pbdd.Doctor
                           where pbdd.PatientBenefitsDirection = rbd.PatientBenefitsDirectionID) as "NOSOLOGY_DIRECTION_BENEFIT"
                    ))
                from AKUZ.T_VZN_BENEFITS_DIRECTION rbd
                    join AKUZ.T_POST post on post.ResourceID = rbd.Post
                    join AKUZ.T_WORKER wPost on wPost.WorkerId = post.Worker
                    join AKUZ.T_POST headPost on headPost.ResourceID = rbd.HeadPost
                    join AKUZ.T_WORKER wHeadPost on wHeadPost.WorkerId = headPost.Worker
                    join AKUZ.T_POST mcPost on mcPost.ResourceID = rbd.MedicalCommissionPost
                    join AKUZ.T_WORKER wMcPost on wMcPost.WorkerId = mcPost.Worker
                    join AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = rbd.PatientBenefitsDirectionID
                    join VCLib.T_ENUMS e on e.EnumID = pbd.Sex
                    join AKUZ.T_LPU_MAIN lm on lm.LpuID = pbd.LpuMain
    			    join AKUZ.T_LPU_SETTINGS ls on ls.LpuMain = lm.LpuID
                    left join AKUZ.T_POLICY p on p.PolicyID = dbo.fn_GetOmsPolicy(pbd.Patient, "dateTime")
                    join VCLib.T_ENUMS eDt on eDt.EnumID = pbd.DocType
    			    left join EHR.T_EHR_RUS_O00015 cs on cs.EhrRusID = pbd.CitizenShip
                where pbd.PatientBenefitsDirectionID = "ID");
    
            INSERT INTO AKUZ.T_RABBIT_SEND_OBJECT
                (RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
            select uuid_generate_v4()--(<RabbitSendObjectID, uniqueidentifier,>
                  ,"QueueName"--,<QueueName, varchar(255),>
                  ,dbo.ConvertTextToBinaryZip(_Result, '')--,<Content, text,>
                  ,null--,<MessageID, varchar(255),>
                  ,null--,<Headers, varchar(255),>
                  ,_ContentEncoding--,<ContentEncoding, varchar(255),>
                  ,_Type--,<Type, varchar(255),>
                  ,"User"--,<UserID, varchar(255),>
                  ,0--,<State, int,>)
                  ,"dateTime"--,<CreateDateTime, datetime,>)
                  ,_OwnerID; --,<OwnerID, varchar(255),>
        else
    
            drop table if exists tmp_NOSOLOGY_DIRECTION;
            CREATE TEMP TABLE tmp_NOSOLOGY_DIRECTION AS
            SELECT
                     (xpath('/ID[1]/text()', t.x))[1]::text::uuid as ID
                    ,(xpath('//RegisterID[1]/text()', t.x))[1]::text::int as RegisterID
                from
                    (
                        select unnest(xpath('/NOSOLOGY_DIRECTION', "Xml"::xml)) x
                    )t;
    
            IF NOT EXISTS (SELECT *
                FROM AKUZ.T_PATIENT_BENEFITS_DIRECTION AS pbd
                    JOIN tmp_NOSOLOGY_DIRECTION AS rd ON rd.ID = pbd.PatientBenefitsDirectionID
            ) THEN
                RAISE EXCEPTION 'Не найдены данные по направлению';
            END IF;
    
            -- Устанавливаем идентификатор пациента из RDS в направлении
            update AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd
                set PAC_ID = rd.RegisterID
            from tmp_NOSOLOGY_DIRECTION rd
                where rd.ID = pbd.PatientBenefitsDirectionID;
    
            -- Добавление льготы
    
            INSERT INTO AKUZ.T_PATIENT_BENEFITS
            (PatientBenefitsID, Patient, Benefits, BenefitDocumentNumber, BeginDate ,Mkb ,Active, Deleted, CreateType, LPU_INF, RegDate, PAC_ID, EndDate)
            SELECT
                uuid_generate_v4()--<PatientBenefitsID, uniqueidentifier,>
                 ,pbd.Patient--<Patient, uniqueidentifier,>
                 ,pbdd.Benefits--<Benefits, uniqueidentifier,>
                 ,pbdd.Document--<BenefitDocumentNumber, varchar(255),>
                 ,pbd.Date--<BeginDate, datetime,>
                 ,(select m.MkbID from AKUZ.T_MKB m where m.MCBCode = pbdd.Mkb limit 1)--<Mkb, uniqueidentifier,>
                 ,1--<Active, bit,>
                 ,0--<Deleted, bit,>
                 ,2--<CreateType, int,>
                 ,lm.LpuCode--<LPU_INF, varchar(4),>
                 ,"dateTime"--<RegDate, datetime,>
                 ,pbd.PAC_ID
                 ,CASE
                      WHEN b.Duration IS NOT NULL AND b.PeriodOfAct IS NOT NULL AND b.CloseType IS NOT NULL
                          THEN
                          CASE
                              WHEN b.CloseType = 0 AND b.PeriodOfAct = 0 THEN pbd.Birthday + INTERVAL '1 day' * b.Duration
                              WHEN b.CloseType = 0 AND b.PeriodOfAct = 1 THEN pbd.Birthday + INTERVAL '1 month' * b.Duration
                              WHEN b.CloseType = 0 AND b.PeriodOfAct = 2 THEN pbd.Birthday + INTERVAL '1 year' * b.Duration
                              WHEN b.CloseType = 1 AND b.PeriodOfAct = 0 THEN pbd.Date + INTERVAL '1 day' * b.Duration
                              WHEN b.CloseType = 1 AND b.PeriodOfAct = 1 THEN pbd.Date + INTERVAL '1 month' * b.Duration
                              WHEN b.CloseType = 1 AND b.PeriodOfAct = 2 THEN pbd.Date + INTERVAL '1 year' * b.Duration
                              ELSE NULL
                              END
                      ELSE NULL
                END--<EndDate, datetime,>
            from AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA pbdd
                     join AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = pbdd.PatientBenefitsDirection
                     join tmp_NOSOLOGY_DIRECTION rd on rd.ID = pbd.PatientBenefitsDirectionID
                     join AKUZ.T_LPU_MAIN lm on lm.LpuID = pbd.LpuMain
                     join AKUZ.T_LPU_SETTINGS ls on ls.LpuMain = lm.LpuID
                     join AKUZ.T_BENEFITS b on b.BenefitsID = pbdd.Benefits
            where pbdd.ChangeType = 1
              and not exists
                (
                    select *
                    from AKUZ.T_PATIENT_BENEFITS pb
                    where pb.Patient = pbd.Patient
                        and pb.Benefits = pbdd.Benefits
                        and (pb.EndDate is null or pb.EndDate >= current_timestamp::date)
                );
    
             -- Закрытие льготы
            update AKUZ.T_PATIENT_BENEFITS pb
            set EndDate = pbd.Date
                ,Active = '0'
                ,Deleted = '1'
            from AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA pbdd
                left join AKUZ.T_MKB mkb on mkb.MCBCode = pbdd.Mkb
                join AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = pbdd.PatientBenefitsDirection
                join tmp_NOSOLOGY_DIRECTION rd on rd.ID = pbd.PatientBenefitsDirectionID
            where pb.Patient = pbd.Patient and pb.Benefits = pbdd.Benefits and (pb.EndDate is null or pb.EndDate >= now()) and pbdd.ChangeType = 3;

		    update AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd
            set EndDate = pbd.Date
                ,Active = '0'
                ,Deleted = '1'
		    from tmp_NOSOLOGY_DIRECTION rd
                join AKUZ.T_PATIENT_BENEFITS pb on pb.Patient = pbd.Patient
		    	join AKUZ.T_BENEFITS b on b.BenefitsID = pb.Benefits
		    	join AKUZ.T_LPU_MAIN lp on lp.LpuID = pbd.LpuMain
            where rd.ID = pbd.PatientBenefitsDirectionID and pbd.ChangeType = 3 and (b.Private = '0 'or (b.Private = '1' and (lp.LpuCode = pb.LPU_INF or lp.TFomsCode = pb.LPU_INF))) and (pb.EndDate is null or pb.EndDate >= now())
                    and b.BenefitType in (24204);
    
        end if;
    End;
End;
$$;
