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

CREATE PROCEDURE sp_nosology_register_direction(
    "Xml" text DEFAULT NULL
, "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 'Направление не найдено!';
            RETURN;
        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 gen_random_uuid()--(<RabbitSendObjectID, uniqueidentifier,>
                 , "QueueName"--,<QueueName, varchar(255),>
                 , 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;
            -- Generate table tmp_NOSOLOGY_DIRECTION
            CREATE TEMP TABLE tmp_nosology_direction
            AS
                SELECT (XPATH('/NOSOLOGY_DIRECTION/ID[1]/text()', x))[1]::text::uuid        AS id
                     , (XPATH('/NOSOLOGY_DIRECTION/RegisterID[1]/text()', x))[1]::text::int AS registerid
                FROM UNNEST(XPATH('//NOSOLOGY_DIRECTION', "Xml"::xml)) AS x;
            
            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 'Не найдены данные по направлению';
                RETURN;
            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 gen_random_uuid()--<PatientBenefitsID, uniqueidentifier,>
                 , pbd.patient--<Patient, uniqueidentifier,>
                 , pbdd.benefits--<Benefits, uniqueidentifier,>
                 , pbdd.document--<BenefitDocumentNumber, varchar(255),>
                 , pbd.date--<BeginDate, datetime,>
                 , pbdd.mkb--<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 pb
            SET enddate = pbd.date,
                active  = 0,
                deleted = 1
            FROM akuz.t_patient_benefits_direction pbd
            JOIN tmp_nosology_direction            rd
                     ON rd.id = pbd.patientbenefitsdirectionid
            JOIN akuz.t_benefits                   b
                     ON b.benefitsid = pb.benefits
            JOIN akuz.t_lpu_main                   lp
                     ON lp.lpuid = pbd.lpumain
            WHERE pb.patient = pbd.patient
              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;
$$;
