measure_todo_triggers.sql

DROP FUNCTION IF EXISTS simple_study_todo__proc() CASCADE;
DROP FUNCTION IF EXISTS condition_study_todo__proc() CASCADE;
DROP FUNCTION IF EXISTS longitudinal_study_todo__proc() CASCADE;


CREATE OR REPLACE FUNCTION longitudinal_study_todo__proc()
RETURNS trigger
LANGUAGE plpgsql
AS $$
    DECLARE
        todo__id integer;
        requirement__id integer;
        measure__id integer;
    BEGIN
        SELECT * FROM (
            INSERT INTO todo
                (individual_id,
                 requirement_id,
                 status,
                 title,
                 time_period__id,
                 expected_completion_date,
                 study_id,
                 asset_type__id,
                 num_required_entries,
                 acquire_channel__id)
                SELECT NEW.individual_id,
                       r.id,
                       'not-started',
                       r.title,
                       tp.id,
                       NEW.enrollment_date+coalesce(tp.days_from_enrollment_offset,0),
                       NEW.study_id,
                       r.asset_type__id,
                       30,
                       r.acquire_channel__id
                FROM requirement r
                    INNER JOIN requirement_list rl ON (r.requirement_list__id = rl.id)
                    INNER JOIN asset_type at ON (at.id = r.asset_type__id)
                    INNER JOIN time_period tp ON (rl.time_period__id = tp.id)
                WHERE at.code = 'measure'
                    AND rl.study_id = NEW.study_id
                    AND rl.participant_group__id = NEW.participant_group__id
                RETURNING id, requirement_id
        ) AS todos;
        INSERT INTO measure_type_x_todo (measure_type__id, todo_id)
        SELECT m.measure_type__id,
               t.id AS todo_id
            FROM todos t INNER JOIN measure_type_x_requirement m
                 ON (t.requirement_id = m.requirement_id);

        DELETE FROM study_recruitment
        WHERE study_id = NEW.study_id AND individual_id = NEW.individual_id;

        RETURN NULL;
    END;
$$;

COMMENT ON FUNCTION longitudinal_study_todo__proc()
IS 'revision: 2015-09-01';

CREATE TRIGGER longitudinal_study_todo__proc AFTER INSERT ON study_enrollment
FOR EACH ROW EXECUTE PROCEDURE longitudinal_study_todo__proc();