DROP PROCEDURE IF EXISTS sp_calculatecommentbycloseact;
DROP FUNCTION IF EXISTS sp_calculatecommentbycloseact;

CREATE PROCEDURE sp_calculatecommentbycloseact(IN "MedicalActID" uuid DEFAULT NULL::uuid)
AS $BODY$
BEGIN
    CREATE TEMP TABLE controltypesbysluch AS
    SELECT
        COALESCE(
            CASE
                WHEN et.controltype = 0 THEN 'МЭК'
                WHEN et.controltype = 1 THEN 'МЭЭ'
                WHEN et.controltype = 2 THEN 'ЭКМП'
            END || ' ', ''
        ) || COALESCE('(' || f042.n_expert || ')', '') AS controltype,
        ssout.schetsluchid
    FROM ies.t_medical_expertise_item mei
    JOIN ies.t_medical_expertise_schet mes ON mes.medicalexpertiseschetid = mei.medicalexpertiseschet
    JOIN ies.t_medical_expertise me ON me.medicalexpertiseid = mes.medicalexpertise
    JOIN ies.t_f006_control_type et ON et.f006controltypeid = me.f006controltype
    JOIN ies.t_schet_sluch_accomplished ssa ON ssa.schetsluchaccomplishedid = mei.schetsluchaccomplished
    LEFT JOIN ies.t_medical_request_item mri ON mri.schetsluchaccomplished = ssa.schetsluchaccomplishedid
    LEFT JOIN ies.t_medical_request mr ON mri.medicalrequest = mr.medicalrequestid AND mr.medicalexpertise = me.medicalexpertiseid
	JOIN ies.t_medical_act ma on ma.medicalrequest = mr.medicalrequestid
    JOIN ies.t_schet_sluch ss ON ssa.schetsluchaccomplishedid = ss.schetsluchaccomplished
    JOIN ies.t_schet_sluch ssout ON ssout.omsschetsluch = ss.schetsluchid
	left join ies.t_f042_trekmp f042 on f042.f042trekmpid = mr.f042expert
    WHERE ma.MedicalActID = "MedicalActID"
    GROUP BY et.controltype, f042.n_expert, ssout.schetsluchid;

    CREATE TEMP TABLE controltypes AS
    SELECT
        schetsluchid,
        string_agg(controltype, ',') AS comment
    FROM controltypesbysluch
    GROUP BY schetsluchid;

    UPDATE ies.t_schet_sluch ss
    SET comentsl = COALESCE(NULLIF(replace(ss.comentsl, ct.comment, ''), '') || ' ', '') || ct.comment
    FROM controltypes ct
    WHERE ct.schetsluchid = ss.schetsluchid;

    DROP TABLE IF EXISTS controltypesbysluch;
    DROP TABLE IF EXISTS controltypes;
END;
$BODY$ LANGUAGE plpgsql;
