-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-03-30
-- =============================================
DROP PROCEDURE IF EXISTS public.sp_bsk_register_direction;

CREATE PROCEDURE public.sp_bsk_register_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) := 'BSK_DIRECTION';
        _ownerid         uuid         := "ID";
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        IF "Xml" IS NULL
        THEN
            IF NOT EXISTS(SELECT 1 FROM akuz.t_patient_benefits_direction WHERE patientbenefitsdirectionid = "ID")
            THEN
                RAISE EXCEPTION 'Направление не найдено!';
                RETURN;
            END IF;
            --выясняем тип операции
            SELECT XMLELEMENT(
                       NAME "BSK_DIRECTION",
                       XMLFOREST(
                           "ID" 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",
                           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(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS "CreateDateTime",
                           pbd.birthsurname AS "BirthSurname",
                           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 "BSK_DIRECTION_BENEFIT",
                                           XMLFOREST(
                                               pbdd.patientbenefitsdirection AS "DirectionID",
                                               bbdd.patientbenefitsdirectiondataid AS "DirectionBenID",
                                               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",
                                               ms.name AS "ServiceName",
                                               ms.code AS "ServiceCode",
                                               TO_CHAR(bbdd.datebegin, 'YYYY.MM.DD') AS "DateBegin",
                                               CASE when bbdd.DateEnd is not null   then TO_CHAR(bbdd.DateEnd, 'YYYY.MM.DD')
                                                    WHEN pbdd.changetype = 3 THEN TO_CHAR(COALESCE(bbdd.dateend, pbd.date), 'YYYY.MM.DD')
                                               END AS "DateEnd",
                                               bbdd.exclusionreason AS "ExclusionReason",
                                               TO_CHAR(bbdd.estimatedddate, 'YYYY.MM.DD') AS "EstimatedDDate",
                                               TO_CHAR(bbdd.dregistrationdate, 'YYYY.MM.DD') AS "DRegistrationDate",
                                               bbdd.place AS "Place",
                                               lmto.lpucode AS "LpuTo",
                                               CASE when bbdd.closedate is not null   then TO_CHAR(bbdd.closedate, 'YYYY.MM.DD')
                                                    WHEN pbdd.changetype = 3 THEN TO_CHAR(COALESCE(bbdd.closedate, pbd.date), 'YYYY.MM.DD')
                                               END AS "CloseDate",
                                               bbdd.concomitantmkb AS "ConcomitantMkb",
                                               bbdd.concomitantdisease AS "ConcomitantDisease",
                                               (SELECT XMLAGG(
                                                           XMLELEMENT(
                                                               NAME "BSK_BENEFITS_DIRECTION_MEDICAMENT",
                                                               XMLFOREST(
                                                                   meds.bskbenefitsdirectiondata AS
                                                                   "DirectionBenID",
                                                                   mnn.otherid AS "Mnn",
                                                                   TO_CHAR(meds.datestart, 'YYYY.MM.DD') AS
                                                                   "DateStart",
                                                                   TO_CHAR(meds.enddate, 'YYYY.MM.DD') AS "EndDate",
                                                                   meds.medicationforms AS "MedicationForms",
                                                                   meds.regiment AS "Regiment",
                                                                   meds.dose AS "Dose",
                                                                   meds.prepid AS "Prepid",
                                                                   meds.daylicoeff AS "DayliCoeff",
                                                                   dv.name AS "MeasureUnit",
                                                                   dv.viewtype AS "MeasureUnitType",
                                                                   meds.singledose AS "SingleDose",
                                                                   meds.multiplicity AS "Multiplicity",
                                                                   dvdv.name AS "DoseViewUnit",
                                                                   dvdv.viewtype AS "DoseViewUnitType"
                                                               )
                                                           )
                                                       )
                                                FROM akuz.t_bsk_benefits_direction_medicaments meds
                                                JOIN      pharm.t_c_mnn                        mnn ON mnn.cmnnid = meds.cmnn
                                                LEFT JOIN pharm.t_dose_view                    dv ON dv.code = meds.measureunit
                                                LEFT JOIN pharm.t_dose_view                    dvdv ON dvdv.code = meds.doseview
                                                WHERE meds.bskbenefitsdirectiondata = bbdd.patientbenefitsdirectiondataid
                                               ) AS "BSK_BENEFITS_DIRECTION_MEDICAMENTS",
                                               (SELECT XMLAGG(
                                                           XMLELEMENT(
                                                               NAME "BSK_REGISTER_COMPLICATION_LINK",
                                                               XMLFOREST(
                                                                   compl.bskregistercomplicationlinkid AS
                                                                   "ComplicationID",
                                                                   compl.bskbenefitsdirectiondata AS
                                                                   "DirectionBenID",
                                                                   compl.complicationtype AS "ComplicationType",
                                                                   TO_CHAR(compl.registrationdate, 'YYYY.MM.DD') AS
                                                                   "RegistrationDate",
                                                                   compl.mkbcode AS "MKBCode",
                                                                   compl.diagnosis AS "Diagnosis",
                                                                   class.idehr AS "EhrRusR6JR8PINUS",
                                                                   rrb.currversion AS "EhrRusR6JR8PINUSVer",
                                                                   type.idehr AS "EhrRusIN6SHID9F6",
                                                                   rrb1.currversion AS "EhrRusIN6SHID9F6Ver",
                                                                   stage.idehr AS "EhrRusJEDS208EN8",
                                                                   rrb2.currversion AS "EhrRusJEDS208EN8Ver",
                                                                   chf.leftventricularejectionfraction AS
                                                                   "LeftVentricularEjectionFraction",
                                                                   TO_CHAR(chf.fractionregistrationdate, 'YYYY.MM.DD') AS
                                                                   "FractionRegistrationDate"
                                                               )
                                                           )
                                                       )
                                                FROM akuz.t_bsk_register_complication_link          compl
                                                JOIN      akuz.t_bsk_register_chronic_heart_failure chf
                                                              ON chf.bskregistercomplicationlinkid = compl.bskregistercomplicationlinkid
                                                LEFT JOIN ehr.t_ehr_rus_r6jr8pinus                  class
                                                              ON chf.chronicheartfailureclass = class.ehrrusid
                                                LEFT JOIN ehr.t_ehr_rus_ref_books                   rrb
                                                              ON rrb.ehrrusrefbooksid = class.ehrrusrefbooks
                                                LEFT JOIN ehr.t_ehr_rus_in6shid9f6                  type
                                                              ON chf.chronicheartfailuretype = type.ehrrusid
                                                LEFT JOIN ehr.t_ehr_rus_ref_books                   rrb1
                                                              ON rrb1.ehrrusrefbooksid = type.ehrrusrefbooks
                                                LEFT JOIN ehr.t_ehr_rus_jeds208en8                  stage
                                                              ON chf.chronicheartfailurestage = stage.ehrrusid
                                                LEFT JOIN ehr.t_ehr_rus_ref_books                   rrb2
                                                              ON rrb2.ehrrusrefbooksid = stage.ehrrusrefbooks
                                                WHERE compl.bskbenefitsdirectiondata = bbdd.patientbenefitsdirectiondataid
                                               ) AS "BSK_REGISTER_COMPLICATION_LINKS"
                                           )
                                       )
                                   )

                            FROM akuz.t_bsk_benefits_direction_data          bbdd
                            JOIN      akuz.t_patient_benefits_direction_data pbdd
                                          ON pbdd.patientbenefitsdirectiondataid = bbdd.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_medical_service                 ms ON ms.medicalserviceid = bbdd.medicalservice
                            LEFT JOIN akuz.t_lpu_main                        lmto ON lmto.lpuid = bbdd.lpumainto
                            WHERE pbdd.patientbenefitsdirection = "ID"
                           ) AS "BSK_DIRECTION_BENEFITS"
                       )
                   )::text
            INTO _result
            FROM akuz.t_bsk_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
            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 #RR_DIRECTION
            CREATE TEMP TABLE tmprr_direction
            AS
                SELECT (XPATH('/BSK_REGISTER/ID[1]/text()', x))[1]::text::uuid        AS id,
                       (XPATH('/BSK_REGISTER/RegisterID[1]/text()', x))[1]::text::int AS registerid
                FROM UNNEST(XPATH('//BSK_REGISTER', "Xml"::xml)) AS x;

            IF NOT EXISTS
                (SELECT 1
                 FROM akuz.t_patient_benefits_direction pbd
                 JOIN
                 tmprr_direction                        rd
                     ON rd.id = pbd.patientbenefitsdirectionid
                )
            THEN
                RAISE EXCEPTION USING MESSAGE = 'Некорректное направление на льготу';
                RETURN;
            END IF;

            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()
                 , pbd.patient
                 , pbdd.benefits
                 , pbdd.document
                 , pbd.date
                 , pbdd.mkb
                 , true
                 , false
                 , 2
                 , lm.lpucode
                 , "dateTime"
                 , pbd.pac_id
                 , COALESCE(bsk.closedate, bsk.dateend
                , 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 akuz.t_bsk_benefits_direction_data     bsk
                     ON bsk.patientbenefitsdirectiondataid = pbdd.patientbenefitsdirectiondataid
            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 1
                             FROM akuz.t_patient_benefits pb
                             WHERE pb.patient = pbd.patient
                               AND pb.benefits = pbdd.benefits
                               AND (pb.enddate IS NULL OR pb.enddate > NOW()::timestamp)
                            );

            -- Закрытие льготы
            UPDATE akuz.t_patient_benefits pb
            SET enddate = pbd.date
              , active  = false
              , deleted = true
            FROM akuz.t_patient_benefits_direction      pbd
            JOIN akuz.t_patient_benefits_direction_data pbdd
                     ON pbd.patientbenefitsdirectionid = pbdd.patientbenefitsdirection
            JOIN tmprr_direction                        rd ON rd.id = pbd.patientbenefitsdirectionid
            WHERE pb.patient = pbd.patient
              AND pbdd.changetype = 3
              AND pb.benefits = pbdd.benefits
              AND (pb.enddate IS NULL OR pb.enddate >= NOW()::timestamp);


            UPDATE akuz.t_patient_benefits pb
            SET enddate = pbd.date
              , active  = false
              , deleted = true
            FROM akuz.t_patient_benefits           pbb
            JOIN akuz.t_patient_benefits_direction pbd ON pbb.patient = pbd.patient
            JOIN tmprr_direction                   rd ON rd.id = pbd.patientbenefitsdirectionid
            JOIN akuz.t_benefits                   b ON b.benefitsid = pbb.benefits
            JOIN akuz.t_lpu_main                   lp ON lp.lpuid = pbd.lpumain
            WHERE pbb.patientbenefitsid = pb.patientbenefitsid
              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()::timestamp)
              AND b.benefittype IN (24207);
            -- Drop tables
            DROP TABLE IF EXISTS tmprr_direction;
        END IF;
    END;
END;
$$;