Month: February 2020

Redmine is probably the best open source ticketing application. We have been using it for some years and had no problem at all.

Though you can define your own workflows and status transitions I miss a feature: automatically closing issues that have been solved during a certain number of days.

There exists a plugin to do so, but if you don’t want to deploy third-party plugins or you just want to customize the way you close the issues, here you have a couple of MySQL Scripts to automate this task (you can program a cron entry for that).

The first script retrieves all the issues ids that have been in solved status during more than the specified number of days

CREATE DEFINER=`root`@`%` PROCEDURE `sp_close_resolved_redmine`(
IN `in_days` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    declare v_id integer;
    declare v_finished bool;
    declare res_issues cursor for
    SELECT id
    FROM redmine.issues
    WHERE status_id = 3
    AND TIMESTAMPDIFF(DAY, updated_on, CURRENT_TIMESTAMP) > in_days;
    declare continue handler for not found set v_finished = true;

    open res_issues;
    res_issues: loop
        fetch res_issues into v_id;
        if v_finished = true then
            leave res_issues;
        end if;
        call sp_cierra_issue_redmine(v_id);
    end loop res_issues;
    close res_issues;
END

The second script is invoked by the one above passing the issue id to be closed

CREATE DEFINER=`root`@`%` PROCEDURE `sp_cierra_issue_redmine`(
                IN `in_id` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    declare v_id            integer;
    declare v_author_id     integer;
    declare v_assigned_to   integer;
    declare v_status        integer;
    declare v_id_journal    integer;               

    select author_id, assigned_to_id, status_id into v_author_id, v_assigned_to, v_status
      from redmine.issues
     where id = in_id;

    #Update issue status and assign to author
    update redmine.issues
       set assigned_to_id = v_author_id,
           status_id = 5,
           updated_on = current_timestamp,
           closed_on = current_timestamp
     where id = in_id;
     
    #Create a new journal for the issue
    insert into redmine.journals (journalized_id, journalized_type, user_id, notes, created_on)
                          values (in_id, 'Issue', 1, 'Automatically closed after 7 days solved', current_timestamp);  
    
    #Get the journal id we have just inserted
    select max(id) into v_id_journal
      from redmine.journals
     where id = in_id;
    
    #Create two new entries in journal details, one for assigned_id and another one for status_id
    insert into redmine.journal_details (journal_id, property, prop_key, old_value, `value`)
                                 values (v_id_journal, 'attr', 'assigned_to_id', v_assigned_to, v_author_id);     

    insert into redmine.journal_details (journal_id, property, prop_key, old_value, `value`)
                                 values (v_id_journal, 'attr', 'status_id', v_status, 5);     
END