Redmine

CREATE EVENT prio_issues_with_due_date
ON SCHEDULE EVERY 1 DAY
STARTS '2018-06-14 05:00:00'
DO
update issues i
join
issue_statuses s ON s.id = i.status_id
set 
priority_id = 
    case 
        when datediff(date_format(now(),'%Y-%m-%d'), date_format(i.due_date,'%Y-%m-%d')) =-2 and priority_id <= 4 then 4
        when datediff(date_format(now(),'%Y-%m-%d'), date_format(i.due_date,'%Y-%m-%d')) =-1 and priority_id <= 5 then 5
        when datediff(date_format(now(),'%Y-%m-%d'), date_format(i.due_date,'%Y-%m-%d')) = 0 and priority_id <= 6 then 6
        when datediff(date_format(now(),'%Y-%m-%d'), date_format(i.due_date,'%Y-%m-%d')) > 0 and priority_id <= 7 then 7
        else priority_id
    end
where
s.is_closed = 0
and
i.due_date is not null
;

ALTER EVENT prio_issues_without_due_date
ON SCHEDULE EVERY 1 DAY
STARTS '2018-06-14 05:00:00'
DO
update issues i
join
issue_statuses s ON s.id = i.status_id
set 
priority_id = 
    case 
        when datediff(date_format(now(),'%Y-%m-%d'), date_format(i.created_on,'%Y-%m-%d')) = 1 and priority_id <= 4 then 4
        when datediff(date_format(now(),'%Y-%m-%d'), date_format(i.created_on,'%Y-%m-%d')) = 2 and priority_id <= 5 then 5
        when datediff(date_format(now(),'%Y-%m-%d'), date_format(i.created_on,'%Y-%m-%d')) = 3 and priority_id <= 6 then 6
        when datediff(date_format(now(),'%Y-%m-%d'), date_format(i.created_on,'%Y-%m-%d')) > 3 and priority_id <= 7 then 7
        else priority_id
    end
where
s.is_closed = 0
and
i.due_date is null
;