-- =============================================
-- 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 * FROM akuz.t_patient_benefits_direction WHERE patientbenefitsdirectionid = "ID")
            THEN
                RAISE EXCEPTION 'Направление не найдено!';
            END IF;
            --выясняем тип операции
            SELECT XMLELEMENT(
                       NAME "BSK_DIRECTION",
                       XMLFOREST(
                           "ID" AS "ID",
                           lm.lpucode AS "LPU",
                           TO_CHAR(pbd.enterdate, 'DD.MM.YYYY') AS "EnterDate",
                           pbd.lastname AS "LastName",
                           pbd.firstname AS "FirstName",
                           pbd.fathername AS "FatherName",
                           TO_CHAR(pbd.birthday, 'DD.MM.YYYY') AS "Birthday",
                           TO_CHAR(pbd.date, 'DD.MM.YYYY') 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, 'DD.MM.YYYY') 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, 'DD.MM.YYYY') AS "RealLiveStartDate",
                           TO_CHAR(pbd.realliveenddate, 'DD.MM.YYYY') 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, 'DD.MM.YYYY') AS "LegalLiveStartDate",
                           TO_CHAR(pbd.legalliveenddate, 'DD.MM.YYYY') 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, 'DD.MM.YYYY') AS "DiagDate",
                                               pbdd.document AS "Document",
                                               TO_CHAR(pbdd.documentdate, 'DD.MM.YYYY') 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, 'DD.MM.YYYY') AS "DateBegin",
                                               TO_CHAR(bbdd.dateend, 'DD.MM.YYYY') AS "DateEnd",
                                               bbdd.exclusionreason AS "ExclusionReason",
                                               TO_CHAR(bbdd.estimatedddate, 'DD.MM.YYYY') AS "EstimatedDDate",
                                               TO_CHAR(bbdd.dregistrationdate, 'DD.MM.YYYY') AS "DRegistrationDate",
                                               bbdd.place AS "Place",
                                               lmto.lpucode AS "LpuTo",
                                               TO_CHAR(bbdd.closedate, 'DD.MM.YYYY') 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, 'DD.MM.YYYY') AS
                                                                   "DateStart",
                                                                   TO_CHAR(meds.enddate, 'DD.MM.YYYY') 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, 'DD.MM.YYYY') 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, 'DD.MM.YYYY') 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"
                       )
                   )
            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 *
                 FROM akuz.t_patient_benefits_direction pbd
                 JOIN
                 tmprr_direction rd
                     ON rd.id = pbd.patientbenefitsdirectionid
                )
            THEN
                RAISE EXCEPTION USING MESSAGE = 'Некорректное направление на льготу';
            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
                 , (SELECT m.mkbid FROM akuz.t_mkb m WHERE m.mcbcode = pbdd.mkb LIMIT 1)
                 , '1'
                 , '0'
                 , 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*
                             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  = '0'
              , deleted = '1'
            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  = '0'
              , deleted = '1'
            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;
$$; 