-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2022-04-18
-- Description: Отправка информации по молочным рецептам в RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_milk_recipe;

CREATE PROCEDURE sp_milk_recipe(
    "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_RECIPE';
        _ownerid         uuid         := "ID";
    BEGIN
        "DateTime" := COALESCE("DateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        IF "Xml" IS NULL
        THEN
            IF (NOT EXISTS(SELECT * FROM akuz.t_milk_recipe WHERE medicalrecordid = "ID"))
            THEN
                RAISE EXCEPTION 'Рецепт не найден!';
                RETURN;
            END IF;
            
            SELECT XMLELEMENT
                   (
                       NAME MILK_RECIPE,
                       XMLFOREST
                       (
                           milkrecipe.medicalrecordid AS "MilkRecipeID"
                           , dairycenter.number AS "DairyCenter"
                           , lm.lpucode AS "LPU"
                           , patient.lastname AS "LastName"
                           , patient.firstname AS "FirstName"
                           , patient.fathername AS "FatherName"
                           , TO_CHAR(patient.birthday, 'YYYY.MM.DD') AS "Birthday"
                           , e.code AS "Sex"
                           , patient.docseries AS "DocSeries"
                           , patient.docnumber AS "DocNumber"
                           , edt.code AS "DocType"
                           , pl.number AS "Polis"
                           , patient.snils AS "Snils"
                           , TO_CHAR(milkrecipe.begindate, 'YYYY.MM.DD') AS "BeginDate"
                           , TO_CHAR(milkrecipe.enddate, 'YYYY.MM.DD') AS "EndDate"
                           , CASE
                                 WHEN milkrecipe.category = 3 THEN 1
                                 WHEN milkrecipe.category = 5 THEN 2
                                 ELSE 0
                             END AS "Category"
                           , milkrecipe.number AS "Number"
                           , milkrecipe.seria AS "Seria"
                           , milkrecipe.mkbcode AS "MKBCode"
                           , milkrecipe.additional AS "Additional"
                           , milkrecipe.lowincomeregister AS "LowIncomeRegister"
                           , TO_CHAR(ambcardrecord.postingdate, 'YYYY.MM.DD') AS "PostingDate"
                           , CASE
                                 WHEN (EXTRACT(YEAR FROM AGE(milkrecipe.enddate, patient.birthday)) * 12 +
                                       EXTRACT(MONTH FROM AGE(milkrecipe.enddate, patient.birthday))) < 6 THEN 0
                                 WHEN (EXTRACT(YEAR FROM AGE(milkrecipe.enddate, patient.birthday)) * 12 +
                                       EXTRACT(MONTH FROM AGE(milkrecipe.enddate, patient.birthday))) >= 6
                                     AND (EXTRACT(YEAR FROM AGE(milkrecipe.enddate, patient.birthday)) * 12 +
                                          EXTRACT(MONTH FROM AGE(milkrecipe.enddate, patient.birthday))) < 12 THEN 1
                                 ELSE 2
                             END AS "Age"
                           , CASE WHEN rd.milkrecipe IS NOT NULL THEN 1 ELSE 0 END AS "RecipeDiscard"
                       ), (SELECT XMLAGG
                                  (
                                      XMLELEMENT
                                      (
                                          NAME "MILK_PRODUCT"
                                          , XMLFOREST
                                            (
                                              milkunit.milkrecipe AS "RecipeId"
                                              , milkunit.amount AS "Amount"
                                              , milkproducts.dose AS "Dose"
                                              , milkproducts.name AS "Name"
                                            )
                                      )
                                  )
                           FROM akuz.t_milk_unit     milkunit
                           JOIN akuz.t_milk_products milkproducts ON milkunit.milk = milkproducts.milkproductsid
                           WHERE milkunit.milkrecipe = milkrecipe.medicalrecordid
                          )
                   )::text
            INTO _result
            FROM akuz.t_milk_recipe         milkrecipe
            JOIN      akuz.t_medical_record ambcardrecord ON milkrecipe.medicalrecordid = ambcardrecord.medicalrecordid
            JOIN      akuz.t_ambulance_card ambcard ON ambcardrecord.ambulancecard = ambcard.ambulancecardid
            JOIN      akuz.t_patient        patient ON ambcard.patient = patient.patientid
            JOIN      vclib.t_enums         e ON e.enumid = patient.sex
            JOIN      akuz.t_lpu_main       lm ON lm.lpuid = ambcard.cardlpu
            JOIN      akuz.t_lpu_settings   ls ON ls.lpumain = lm.lpuid
            LEFT JOIN akuz.t_policy         pl ON pl.policyid = dbo.fn_getomspolicy(ambcard.patient, "DateTime")
            JOIN      vclib.t_enums         edt ON edt.enumid = patient.doctype
            LEFT JOIN akuz.t_dairy_center   dairycenter ON milkrecipe.dairycenter = dairycenter.dairycenterid
            LEFT JOIN akuz.t_recipe_discard rd ON rd.milkrecipe = milkrecipe.medicalrecordid
            WHERE milkrecipe.medicalrecordid = "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;
        
        END IF;
    END;
END;
$$;