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

CREATE PROCEDURE sp_rr_direction(
    "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
        _result          text;
        _contentencoding varchar(255) := 'utf-8';
        _type            varchar(255) := 'RR_DIRECTION';
        _ownerid         uuid         := "ID";
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        IF "Xml" IS NULL
        THEN
            -- Обработка операции
            SELECT XMLELEMENT
                   (
                       NAME "RR_DIRECTION"
                       , XMLFOREST
                         (
                           pbd.patientbenefitsdirectionid AS "ID"
                           , CASE WHEN ls.regioncode = '1600000000000' THEN '16' || lm.code ELSE lm.lpucode END AS "LPU"
                           , TO_CHAR(pbd.enterdate, 'YYYY.MM.DD') AS "EnterDate"
                           , pbd.lastname AS "LastName"
                           , pbd.firstname AS "FirstName"
                           , pbd.fathername AS "FatherName"
                           , TO_CHAR(pbd.birthday, 'YYYY.MM.DD') AS "Birthday"
                           , e.code AS "Sex"
                           , pbd.kod AS "Kod"
                           , CASE WHEN pbd.docseries = '0' THEN NULL ELSE pbd.docseries END AS "DocSeries"
                           , CASE WHEN pbd.docnumber = '0' THEN NULL ELSE pbd.docnumber END AS "DocNumber"
                           , pbd.pac_id AS "RrPatient"
                           , CASE
                                 WHEN TRIM(REPLACE(REPLACE(pbd.snils, '0', ''), '-', '')) = '' THEN NULL
                                 ELSE pbd.snils
                             END AS "SNILS"
                           , edt.code AS "DocType"
                           , pbd.changetype AS "ChangeType"
                           , rbd.excludereason AS "ExcludeReason"
                           , rbd.kodadr AS "KodAdr"
                           , pbd.birthsurname AS "BirthSurname"
                           , lmf.lpucode AS "LpuFirst"
                           , rbd.sost_r AS "SOST_R"
                           , 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 "RR_DIRECTION_BENEFIT"
                                       , XMLFOREST
                                         (
                                           rbd.patientbenefitsdirectionid 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"
                                           , rbdd.dop AS "Dop"
                                           , pdop.code AS "DivisionManager"
                                         )
                                   )
                               )
                        FROM akuz.t_regional_benefits_direction_data     rbdd
                        JOIN      akuz.t_patient_benefits_direction_data pbdd
                                      ON pbdd.patientbenefitsdirectiondataid = rbdd.patientbenefitsdirectiondataid
                        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
                        LEFT JOIN akuz.t_post                            pdop ON pdop.resourceid = rbdd.divisionmanager
                        WHERE pbdd.patientbenefitsdirection = rbd.patientbenefitsdirectionid
                       )
                   )::text
            INTO _result
            FROM akuz.t_regional_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 akuz.t_lpu_main                   lmf ON lmf.lpuid = rbd.lpumainfirst
            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",
                   0,
                   "dateTime",
                   _ownerid;
        ELSE
            -- Generate table Tmp_RR_DIRECTION
            DROP TABLE IF EXISTS tmp_rr_direction;
            CREATE TEMP TABLE tmp_rr_direction
            AS
                SELECT (XPATH('RR_DIRECTION/ID[1]/text()', x))[1]::text::uuid       AS id,
                       (XPATH('RR_DIRECTION/RrPatient[1]/text()', x))[1]::text::int AS rrpatient
                FROM UNNEST(XPATH('//RR_DIRECTION', "Xml"::xml)) AS x;
            
            IF NOT EXISTS
                (SELECT *
                 FROM akuz.t_patient_benefits_direction pbd
                 JOIN
                 tmp_rr_direction                       rd
                     ON rd.id = pbd.patientbenefitsdirectionid
                )
            THEN
                RAISE EXCEPTION USING MESSAGE = 'Не найдены данные по направлению на региональную льготу';
                RETURN;
            END IF;
            
            -- Устанавливаем идентификатор пациента из RDS в направлении
            UPDATE akuz.t_patient_benefits_direction pbd
            SET pac_id = rd.rrpatient
            FROM tmp_rr_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()
                 , pbd.patient
                 , pbdd.benefits
                 , pbdd.document
                 , pbd.date
                 , (SELECT m.mkbid FROM akuz.t_mkb m WHERE m.mcbcode = pbdd.mkb LIMIT 1)
                 , '1'
                 , '0'
                 , 2
                 , lm.lpucode
                 , "dateTime"
                 , pbd.pac_id
                 , CASE
                       WHEN ls.regioncode = '1600000000000' AND (b.benefitcode = '057' OR b.benefitcode = '57') AND
                            b.benefitviews = 19002
                           THEN CASE
                                    WHEN pbd.snils IS NOT NULL AND pbd.snils <> ''
                                        THEN pbd.birthday + 3 * INTERVAL '1 Year' - INTERVAL '1 Day'
                                    ELSE pbd.birthday + 6 * INTERVAL '1 Month' - INTERVAL '1 Day'
                                END
                       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 + b.duration * INTERVAL '1 DAY'
                                    WHEN b.closetype = 0 AND b.periodofact = 1
                                        THEN pbd.birthday + b.duration * INTERVAL '1 MONTH'
                                    WHEN b.closetype = 0 AND b.periodofact = 2
                                        THEN pbd.birthday + b.duration * INTERVAL '1 YEAR'
                                    WHEN b.closetype = 1 AND b.periodofact = 0
                                        THEN pbd.date + b.duration * INTERVAL '1 DAY'
                                    WHEN b.closetype = 1 AND b.periodofact = 1
                                        THEN pbd.date + b.duration * INTERVAL '1 MONTH'
                                    WHEN b.closetype = 1 AND b.periodofact = 2
                                        THEN pbd.date + b.duration * INTERVAL '1 YEAR'
                                END
                   END
            FROM akuz.t_patient_benefits_direction_data pbdd
            JOIN akuz.t_patient_benefits_direction      pbd
                     ON pbd.patientbenefitsdirectionid = pbdd.patientbenefitsdirection
            JOIN tmp_rr_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
                 JOIN akuz.t_benefits         b ON b.benefitsid = pb.benefits
                 WHERE pb.patient = pbd.patient
                   AND pb.benefits = pbdd.benefits
                   AND (pb.enddate IS NULL OR pb.enddate > NOW()::timestamp)
                   AND (b.private = '0' OR (b.private = '1' AND (lm.lpucode = pb.lpu_inf OR lm.tfomscode = pb.lpu_inf)))
                );
            
            -- Закрытие льготы
            UPDATE akuz.t_patient_benefits pb2
            SET enddate = pbd.date,
                active  = '0',
                deleted = '1'
            FROM akuz.t_patient_benefits                pb
            JOIN akuz.t_patient_benefits_direction_data pbdd
                     ON pb.benefits = pbdd.benefits
            JOIN akuz.t_patient_benefits_direction      pbd
                     ON pbd.patientbenefitsdirectionid = pbdd.patientbenefitsdirection AND pb.patient = pbd.patient
            JOIN tmp_rr_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 pb2.patientbenefitsid = pb.patientbenefitsid
              AND pbdd.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());
            
            
            UPDATE akuz.t_patient_benefits pb2
            SET enddate = pbd.enterdate,
                active  = FALSE,
                deleted = TRUE
            FROM akuz.t_patient_benefits           pb
            JOIN akuz.t_patient_benefits_direction pbd
                     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
            JOIN tmp_rr_direction                  rd ON rd.id = pbd.patientbenefitsdirectionid
            WHERE pb2.patientbenefitsid = pb.patientbenefitsid
              AND 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 (24202, 24203);
        
        END IF;
    END;
END;
$$;