SET ROLE :adm_ka_owner;
BEGIN;

INSERT INTO db_history (beschreibung, zeitpunkt) 
	VALUES ('01_helpers.sql', current_timestamp);

-- Author: Torsten Irlaender
-- Funktionen, die für die Anonymisierung der Fallakten genutzt werden.

CREATE OR REPLACE FUNCTION shorten_value(myvalue varchar, len integer) 
RETURNS varchar 
LANGUAGE 'plpgsql' STABLE AS 
$$
BEGIN
    return substring(myvalue, 1, len);
END;
$$;

REVOKE ALL ON FUNCTION shorten_value(varchar, integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION shorten_value(varchar, integer) TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION shorten_value(varchar, integer) TO GROUP :admin_group;

CREATE OR REPLACE FUNCTION hash_value(myvalue varchar, myvalue2 varchar) 
RETURNS varchar 
LANGUAGE 'plpgsql' STABLE AS 
$$
BEGIN
    return md5(coalesce(myvalue, '') || coalesce(myvalue2, ''));
END;
$$;

REVOKE ALL ON FUNCTION hash_value(varchar, varchar) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION hash_value(varchar, varchar) TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION hash_value(varchar, varchar) TO GROUP :admin_group;

CREATE OR REPLACE FUNCTION get_age(myvalue date) 
RETURNS date 
LANGUAGE 'plpgsql' STABLE AS 
$$
BEGIN
    return date_trunc('year', myvalue::timestamp);
END;
$$;

REVOKE ALL ON FUNCTION get_age(date) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION get_age(date) TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION get_age(date) TO GROUP :admin_group;

CREATE OR REPLACE FUNCTION check_bool(myvalue integer, mybool integer) 
RETURNS integer 
LANGUAGE 'plpgsql' STABLE AS 
$$
BEGIN
    IF mybool = 1 THEN
        return myvalue;
    ELSE
        return NULL::integer;
    END IF;
END;
$$;

REVOKE ALL ON FUNCTION check_bool(integer, integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_bool(integer, integer) TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION check_bool(integer, integer) TO GROUP :admin_group;

CREATE OR REPLACE FUNCTION check_bool(myvalue varchar, mybool integer) 
RETURNS varchar 
LANGUAGE 'plpgsql' STABLE AS 
$$
BEGIN
    IF mybool = 1 THEN
        return myvalue;
    ELSE
        return NULL::varchar;
    END IF;
END;
$$;

REVOKE ALL ON FUNCTION check_bool(varchar, integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_bool(varchar, integer) TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION check_bool(varchar, integer) TO GROUP :admin_group;

-- ----------------------------------------------------------------------------
-- 
-- Haeufige Abfragen, die sowohl von der Gruppe CM_KA, als auch Admin_KA
-- ausgefuehrt werden koennen.
-- 

--
-- gettotal()
-- Anzahl aller Faelle des Fallmanagers oder der KA (wenn Admin)
--
CREATE OR REPLACE FUNCTION gettotal() RETURNS integer
    LANGUAGE 'plpgsql' 
    AS $$
DECLARE 
    total int4;
BEGIN
    SELECT INTO total count(id) FROM master_tbl_eval_total_view;
    RETURN total;
END;
$$ ;

REVOKE ALL ON FUNCTION gettotal() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION gettotal() TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION gettotal() TO GROUP :admin_group;


--
-- gettotalcm()
-- Anzahl aller Faelle des Fallmanagers oder der KA (wenn Admin),
-- die sich im Case-Management befinden.
--
CREATE OR REPLACE FUNCTION gettotalcm() RETURNS integer
    LANGUAGE 'plpgsql' 
    AS $$
DECLARE
    total_cm int4;
BEGIN
    SELECT INTO total_cm count(id) FROM master_tbl_eval_incm_view;
    RETURN total_cm;
END;
$$ ;

REVOKE ALL ON FUNCTION gettotalcm() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION gettotalcm() TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION gettotalcm() TO GROUP :admin_group;

-- 
-- get_masterid_from_uuid(myrel varchar, uuid char) 
-- Author: Torsten Irlaender 
--
-- Gibt die master id des datensatzes mit der übergebenen uuid in der
-- angegebenen realtion wieder. Wenn ein solcher datensatz nicht existiert, wird
-- NULL zurueck gegeben.
--

CREATE OR REPLACE FUNCTION get_masterid_from_uuid(myrel varchar(128), uuid char(36))
    RETURNS integer AS
$$
DECLARE
    myid integer;
BEGIN
    IF myrel = 'master' THEN
        SELECT INTO myid id from master_tbl WHERE uuid_id = uuid;
    ELSIF myrel = 'logbuch' THEN
        SELECT INTO myid master_id from ka_logbuch_tbl WHERE uuid_id = uuid;
    ELSIF myrel = 'dokumente' THEN
        SELECT INTO myid master_id from ka_dokumente_tbl WHERE uuid_id = uuid;
    END IF;
    RETURN myid;
END;
$$ LANGUAGE 'plpgsql' VOLATILE EXTERNAL SECURITY DEFINER;
REVOKE ALL ON FUNCTION get_masterid_from_uuid(varchar, char) FROM public;
GRANT EXECUTE ON FUNCTION get_masterid_from_uuid(varchar, char)  TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION get_masterid_from_uuid(varchar, char)  TO GROUP :admin_group;

-- Funktion wird offenbar nirgens mehr aufgerufen.
-- -- Funktion get_default_value() gibt zu einer
-- -- gegebenen Relation und Spalte den Default-Wert zurueck gibt.
-- 
-- CREATE OR REPLACE FUNCTION get_default_value(relation_name text, attribute_name text)
-- RETURNS text 
-- LANGUAGE plpgsql STABLE AS
-- $$
-- DECLARE
--     has_def bool;
--     def     text;
-- BEGIN
--     SELECT INTO has_def a.atthasdef FROM pg_class c 
--     JOIN pg_attribute a ON c.oid = a.attrelid
--     WHERE c.relname = relation_name AND a.attname = attribute_name;
-- 
--     IF has_def IS NULL OR NOT has_def THEN
--         RETURN NULL;
--     END IF;
-- 
--     SELECT INTO def pg_get_expr(i.adbin, i.oid) FROM 
--     (SELECT c.oid, adnum, r.adbin
--         FROM pg_class c 
--         JOIN pg_attrdef r ON r.adrelid = c.oid
--         WHERE c.relname = relation_name) i
--         INNER JOIN pg_attribute p ON attrelid = i.oid AND i.adnum = p.attnum
--         WHERE p.attname = attribute_name;
--     RETURN def;
-- END;
-- $$;
-- 
-- REVOKE ALL ON FUNCTION get_default_value(text, text) FROM public;
-- GRANT EXECUTE ON FUNCTION get_default_value(text, text) TO GROUP :cm_group;
-- GRANT EXECUTE ON FUNCTION get_default_value(text, text) TO GROUP :admin_group;

COMMIT;
