DROP PROCEDURE IF EXISTS public.sp_getsetmedicationforms;

CREATE OR REPLACE PROCEDURE public.sp_getsetmedicationforms(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml" :: xml;
    p_isnew           boolean      := "IsNew"::boolean;
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_contentencoding varchar(255) = 'utf-8';
    v_type            varchar(255) := 'MEDICATION_FORMS';
    v_operationtype   varchar(6);
    v_ownerid         uuid         := p_id;
    v_entid           int ;
    c_insert          varchar(6)   := 'Insert';
    c_update          VARCHAR(6)   := 'Update';
    c_delete          VARCHAR(6)   := 'Delete';
    c_ftload          VARCHAR(6)   := 'FTLoad';
BEGIN
    
    IF (p_xml IS NULL) THEN
        
        IF (p_id IS NULL) THEN
            v_operationtype = c_ftload;
        ELSIF (p_isnew = TRUE) THEN
            v_operationtype = c_insert;
        ELSIF (p_isnew = FALSE AND
               EXISTS (SELECT 1 FROM pharm.t_medication_forms mf WHERE mf.medicationformsid = p_id)) THEN
            v_operationtype = c_update;
        ELSIF (p_isnew = FALSE AND
               NOT EXISTS (SELECT 1 FROM pharm.t_medication_forms mf WHERE mf.medicationformsid = p_id)) THEN
            
            v_operationtype = c_delete;
            SELECT XMLELEMENT(
                       NAME "MEDICATION_FORMS",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLFOREST
                           (
                               v_operationtype AS "TYPE",
                               p_id AS "ID"
                           )
                       )
                   )
            INTO v_result;
        
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT '<MEDICATION_FORM><header><TYPE>' || c_ftload || '</TYPE></header><body><MEDICATION_FORMS>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                ''), '')
                       ||
                   '</MEDICATION_FORMS></body></MEDICATION_FORM>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "MEDICATION_FORM",
                             XMLFOREST(
                                 mf.medicationformsid AS "ID",
                                 mf.code AS "Code",
                                 mf.active AS "Active",
                                 mf.name AS "Name",
                                 mf.formtype AS "FormType",
                                 mf.regcode AS "RegCode",
                                 mf.fedcode AS "FedCode",
                                 mf.namelat AS "NameLat",
                                 ehr.idehr AS "EhrRusVzn431",
                                 rrb.currversion AS "EhrRusVzn431Ver",
                                 mf.packagetype AS "PackageType"
                             )
                         ) AS xml_elem
                  FROM pharm.t_medication_forms     mf
                  LEFT JOIN ehr.t_ehr_rus_vzn431    ehr ON ehr.ehrrusid = mf.ehrrusvzn431
                  LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = ehr.ehrrusrefbooks
                 ) tt;
        
        ELSIF (v_operationtype <> c_delete) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "MEDICATION_FORMS",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLFOREST
                           (
                               v_operationtype AS "TYPE",
                               p_id AS "ID"
                           )
                       ),
                       XMLELEMENT
                       (
                           NAME "body",
                           XMLFOREST
                           (
                               mf.active AS "Active",
                               mf.name AS "Name",
                               mf.formtype AS "FormType",
                               mf.regcode AS "RegCode",
                               mf.fedcode AS "FedCode",
                               mf.namelat AS "NameLat",
                               mf.code AS "Code",
                               ehr.idehr AS "EhrRusVan431",
                               rrb.currversion AS "EhrRusVzn431Ver",
                               mf.packagetype AS "PackageType"
                           )
                       )
                   )
            INTO v_result
            FROM pharm.t_medication_forms     mf
            LEFT JOIN ehr.t_ehr_rus_vzn431    ehr ON ehr.ehrrusid = mf.ehrrusvzn431
            LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = ehr.ehrrusrefbooks
            WHERE mf.medicationformsid = p_id;
        
        
        END IF;
        
        INSERT INTO
            rds.t_rabbit_send_object (rabbitsendobjectid,
                                      queuename,
                                      content,
                                      messageid,
                                      headers,
                                      contentencoding,
                                      type,
                                      userid,
                                      state,
                                      createdatetime,
                                      ownerid)
        SELECT gen_random_uuid(),
               s.servername,
               public.converttexttobinaryzip(v_result, ''),
               NULL,
               NULL,
               v_contentencoding,
               v_type,
               p_user,
               0,
               p_datetime,
               v_ownerid
        FROM rds.t_sync_servers s
        WHERE (p_queuename IS NOT NULL AND s.servername = p_queuename)
           OR (p_queuename IS NULL AND s.servertype IN (0, 1));
    
    
    ELSIF (p_xml IS NOT NULL) THEN
        
        SELECT (XPATH('/MEDICATION_FORMS/header/TYPE/text()', x))[1]::varchar(6),
               (XPATH('/MEDICATION_FORMS/header/ID/text()', x))[1]::text::uuid,
               p_xml::text
        INTO v_operationtype, v_ownerid, v_result
        FROM UNNEST(XPATH('/MEDICATION_FORMS', p_xml)) x;
--
        IF (v_operationtype = c_ftload) THEN
            CREATE TEMP TABLE temp_medication_forms AS
                SELECT (XPATH('/MEDICATION_FORMS/ID/text()', x))[1]::text::uuid              AS id,
                       (XPATH('/MEDICATION_FORMS/Active/text()', x))[1]::text::boolean       AS active,
                       (XPATH('/MEDICATION_FORMS/Name/text()', x))[1]::varchar(255)          AS name,
                       (XPATH('/MEDICATION_FORMS/NameLat/text()', x))[1]::varchar(255)       AS namelat,
                       (XPATH('/MEDICATION_FORMS/FormType/text()', x))[1]::text::int         AS formtype,
                       (XPATH('/MEDICATION_FORMS/RegCode/text()', x))[1]::varchar(50)        AS regcode,
                       (XPATH('/MEDICATION_FORMS/FedCode/text()', x))[1]::varchar(50)        AS fedcode,
                       (XPATH('/MEDICATION_FORMS/Code/text()', x))[1]::text::int             AS code,
                       (XPATH('/MEDICATION_FORMS/EhrRusVzn431/text()', x))[1]::text::int     AS ehrrusvzn431,
                       (XPATH('/MEDICATION_FORMS/EhrRusVzn431Ver/text()', x))[1]::varchar(8) AS ehrrusvzn431ver,
                       (XPATH('/MEDICATION_FORMS/PackageType/text()', x))[1]::text::uuid     AS packagetype
                FROM UNNEST(XPATH('MEDICATION_FORMS/body/MEDICATION_FORMS/MEDICATION_FORM', p_xml)) x;
            
            UPDATE pharm.t_medication_forms mf
            SET active = FALSE
            WHERE NOT EXISTS
                      (SELECT 1
                       FROM temp_medication_forms nmf
                       WHERE nmf.id = mf.medicationformsid
                      );
            
            UPDATE pharm.t_medication_forms mf
            SET name         = nmf.name,
                active       = nmf.active,
                formtype     = COALESCE(nmf.formtype, 0),
                regcode      = nmf.regcode,
                fedcode      = nmf.fedcode,
                namelat      = nmf.namelat,
                code         =nmf.code,
                ehrrusvzn431 = eh1.ehrrusid,
                packagetype  = nmf.packagetype
            FROM temp_medication_forms  nmf
            LEFT JOIN LATERAL (SELECT eh.ehrrusid
                               FROM ehr.t_ehr_rus_vzn431    eh
                               JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                               WHERE eh.idehr = nmf.ehrrusvzn431
                                 AND rrb.currversion = nmf.ehrrusvzn431ver
                               LIMIT 1) eh1 ON TRUE
            WHERE nmf.id = mf.medicationformsid;
            
            INSERT INTO
                pharm.t_medication_forms (medicationformsid,
                                          name,
                                          active,
                                          formtype,
                                          regcode,
                                          fedcode,
                                          namelat,
                                          code,
                                          ehrrusvzn431,
                                          packagetype)
            SELECT nmf.id,
                   nmf.name,
                   nmf.active,
                   COALESCE(nmf.formtype, 0),
                   nmf.regcode,
                   nmf.fedcode,
                   nmf.namelat,
                   nmf.code,
                   eh1.ehrrusid,
                   nmf.packagetype
            FROM temp_medication_forms  nmf
            LEFT JOIN LATERAL (SELECT eh.ehrrusid
                               FROM ehr.t_ehr_rus_vzn431    eh
                               JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                               WHERE eh.idehr = nmf.ehrrusvzn431
                                 AND rrb.currversion = nmf.ehrrusvzn431ver
                               LIMIT 1) eh1 ON TRUE
            WHERE NOT EXISTS (SELECT 1
                              FROM pharm.t_medication_forms mf
                              WHERE mf.medicationformsid = nmf.id
                             );
        
        ELSE
            CREATE TEMP TABLE temp_medication_form AS
                SELECT (XPATH('/header/ID/text()', t.x1))[1]::text::uuid            AS id,
                       (XPATH('/body/Active/text()', t.x2))[1]::text::boolean       AS active,
                       (XPATH('/body/Name/text()', t.x2))[1]::varchar(255)          AS name,
                       (XPATH('/body/NameLat/text()', t.x2))[1]::varchar(255)       AS namelat,
                       (XPATH('/body/FormType/text()', t.x2))[1]::text::int         AS formtype,
                       (XPATH('/body/RegCode/text()', t.x2))[1]::varchar(50)        AS regcode,
                       (XPATH('/body/FedCode/text()', t.x2))[1]::varchar(50)        AS fedcode,
                       (XPATH('/body/Code/text()', t.x2))[1]::text::int             AS code,
                       (XPATH('/body/EhrRusVzn431/text()', t.x2))[1]::text::int     AS ehrrusvzn431,
                       (XPATH('/body/EhrRusVzn431Ver/text()', t.x2))[1]::varchar(8) AS ehrrusvzn431ver,
                       (XPATH('/body/PackageType/text()', t.x2))[1]::text::uuid     AS packagetype
                FROM (SELECT UNNEST(XPATH('/MEDICATION_FORMS/header', x)) AS x1
                           , UNNEST(XPATH('/MEDICATION_FORMS/body', x))   AS x2
                      FROM UNNEST(XPATH('/MEDICATION_FORMS', p_xml)) x
                     ) t;
            
            IF (v_operationtype = c_delete) THEN
                
                UPDATE pharm.t_medication_forms mf
                SET active = FALSE
                FROM temp_medication_form nmf
                WHERE nmf.id = mf.medicationformsid;
            
            ELSIF (v_operationtype = c_update) THEN
                
                UPDATE pharm.t_medication_forms mf
                SET name         = nmf.name,
                    active       = nmf.active,
                    formtype     = COALESCE(nmf.formtype, 0),
                    regcode      = nmf.regcode,
                    fedcode      = nmf.fedcode,
                    namelat      = nmf.namelat,
                    code         = nmf.code,
                    ehrrusvzn431 = ehrrusid,
                    packagetype  = nmf.packagetype
                FROM temp_medication_form   nmf
                LEFT JOIN LATERAL (SELECT eh.ehrrusid
                                   FROM ehr.t_ehr_rus_vzn431    eh
                                   JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                   WHERE eh.idehr = nmf.ehrrusvzn431
                                     AND rrb.currversion = nmf.ehrrusvzn431ver
                                   LIMIT 1) eh1 ON TRUE
                WHERE nmf.id = mf.medicationformsid;
            
            ELSIF (v_operationtype = c_insert) THEN
                
                INSERT INTO
                    pharm.t_medication_forms (medicationformsid,
                                              name,
                                              active,
                                              formtype,
                                              regcode,
                                              fedcode,
                                              namelat,
                                              code,
                                              ehrrusvzn431,
                                              packagetype)
                SELECT nmf.id,
                       nmf.name,
                       nmf.active,
                       COALESCE(nmf.formtype, 0),
                       nmf.regcode,
                       nmf.fedcode,
                       nmf.namelat,
                       nmf.code,
                       eh1.ehrrusid,
                       nmf.packagetype
                FROM temp_medication_form   nmf
                LEFT JOIN LATERAL (SELECT eh.ehrrusid
                                   FROM ehr.t_ehr_rus_vzn431    eh
                                   JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                   WHERE eh.idehr = nmf.ehrrusvzn431
                                     AND rrb.currversion = nmf.ehrrusvzn431ver
                                   LIMIT 1) eh1 ON TRUE
                WHERE NOT EXISTS (SELECT 1
                                  FROM pharm.t_medication_forms mf
                                  WHERE mf.medicationformsid = nmf.id
                                 );
            
            END IF;
            
            INSERT INTO
                rds.t_rabbit_send_object (rabbitsendobjectid,
                                          queuename,
                                          content,
                                          messageid,
                                          headers,
                                          contentencoding,
                                          type,
                                          userid,
                                          state,
                                          createdatetime,
                                          ownerid)
            SELECT gen_random_uuid(),
                   s.servername,
                   public.converttexttobinaryzip(v_result, ''),
                   NULL,
                   NULL,
                   v_contentencoding,
                   v_type,
                   p_user,
                   0,
                   p_datetime,
                   v_ownerid
            FROM rds.t_sync_servers s
            WHERE s.servername <> p_queuename;
        
        END IF;
        
        SELECT entityid
        INTO v_entid
        FROM vclib.t_entities
        WHERE entname = 'MEDICATION_FORMS';
        
        IF (EXISTS (SELECT 1 FROM vclib.t_gen_numbers WHERE entityid = v_entid AND numbername = 'PHARM.MEDICATION_FORMS'
                   )) THEN
            
            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = (SELECT MAX(code) FROM pharm.t_medication_forms)
            WHERE gn.entityid = v_entid
              AND gn.numbername = 'PHARM.MEDICATION_FORMS';
        
        ELSE
            
            INSERT INTO
                vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(),
                    v_entid,
                    'PHARM.MEDICATION_FORMS',
                    COALESCE((SELECT MAX(code) FROM pharm.t_medication_forms), 1));
        
        END IF;
    
    END IF;
END;
$$