-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2022-03-16
-- Description: Отправка информации по направлениям в молочный регистр в RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_milk_register_direction;

CREATE PROCEDURE sp_milk_register_direction(
    "Xml" text DEFAULT NULL
, "ID" uuid DEFAULT NULL::uuid
, "QueueName" varchar(100) = 'RDSSync'
, "DateTime" TimeStamp = NULL
, "User" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    
    DECLARE
        _result          text;
        _contentencoding varchar(255) := 'utf-8';
        _type            varchar(255) := 'MILK_DIRECTION';
        _ownerid         uuid         := "ID";
    BEGIN
        
        "DateTime" := COALESCE("DateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        IF ("Xml" IS NULL)
        THEN
            IF (NOT EXISTS(SELECT * FROM akuz.t_patient_benefits_direction WHERE patientbenefitsdirectionid = "ID"))
            THEN
                RAISE EXCEPTION 'Направление не найдено!';
            END IF;
            
            -- Обработка операции
            _result :=
                (SELECT XMLELEMENT
                        (
                            NAME "MILK_DIRECTION"
                            , (
                                XMLAGG
                                (
                                    XMLFOREST
                                    (
                                        pbd.patientbenefitsdirectionid AS "ID"
                                        , lm.lpucode AS "LPU"
                                        , TO_CHAR(pbd.enterdate, 'YYYY.MM.DD') AS "EnterDate"
                                        , pbd.lastname AS "LastName"
                                        , pbd.firstname AS "FirstName"
                                        , pbd.fathername AS "FatherName"
                                        , pbd.birthsurname AS "BirthSurname"
                                        , TO_CHAR(pbd.birthday, 'YYYY.MM.DD') AS "Birthday"
                                        , TO_CHAR(pbd.date, 'YYYY.MM.DD') 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"
                                        , TO_CHAR(pbd.docissuedate, 'YYYY.MM.DD') 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"
                                        , TO_CHAR(pbd.reallivestartdate, 'YYYY.MM.DD') AS "RealLiveStartDate"
                                        , TO_CHAR(pbd.realliveenddate, 'YYYY.MM.DD') 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"
                                        , TO_CHAR(pbd.legallivestartdate, 'YYYY.MM.DD') AS "LegalLiveStartDate"
                                        , TO_CHAR(pbd.legalliveenddate, 'YYYY.MM.DD') AS "LegalLiveEndDate"
                                        , cs.code AS "CitizenShip"
                                    )
                                )
                                )
                            -- собираем льготы
                            , (SELECT XMLAGG
                                      (
                                          XMLELEMENT
                                          (
                                              NAME "MILK_DIRECTION_BENEFIT"
                                              , XMLFOREST
                                                (
                                                  pbdd.patientbenefitsdirection AS "DirectionID"
                                                  , TO_CHAR(pbdd.diagdate, 'YYYY.MM.DD') AS "DiagDate"
                                                  , pbdd.document AS "Document"
                                                  , TO_CHAR(pbdd.documentdate, 'YYYY.MM.DD') 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
                              )
                        )::text
                 FROM akuz.t_milk_benefits_direction         rbd
                 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(),
                   "QueueName",
                   converttexttobinaryzip(_result, ''),
                   NULL,
                   NULL,
                   _contentencoding,
                   _type,
                   "User"::text,
                   0,
                   "DateTime",
                   _ownerid;
        ELSE
            
            DROP TABLE IF EXISTS tmprr_direction;
            -- Generate table TmpRR_DIRECTION
            CREATE TEMP TABLE tmprr_direction
            AS
                SELECT (XPATH('/MILK_REGISTER/ID[1]/text()', x))[1]::text::uuid        AS id,
                       (XPATH('/MILK_REGISTER/RegisterID[1]/text()', x))[1]::text::int AS registerid
                FROM UNNEST(XPATH('//MILK_REGISTER', "Xml"::xml)) AS x;
            
            IF NOT EXISTS(SELECT *
                          FROM akuz.t_patient_benefits_direction pbd
                          JOIN tmprr_direction                   rd ON rd.id = pbd.patientbenefitsdirectionid
                         )
            THEN
                RAISE EXCEPTION USING MESSAGE = 'Не найдены данные по направлению';
            END IF;
            
            -- Устанавливаем идентификатор пациента из RDS в направлении
            UPDATE akuz.t_patient_benefits_direction pbd
            SET pac_id = rd.registerid
            FROM tmprr_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,>
                 , (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
                                 END
                    END)
            FROM akuz.t_patient_benefits_direction_data pbdd
            JOIN akuz.t_patient_benefits_direction      pbd
                     ON pbd.patientbenefitsdirectionid = pbdd.patientbenefitsdirection
            JOIN tmprr_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::TIMESTAMP)
                );
            
            -- Закрытие льготы
            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      tmprr_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 tmprr_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 (24299);
        
        END IF;
    END;
END;
$$;