Grocy: Weekly chores scheduled wrong when the next execution is in the same week

[shadow7412]

When you set the period type to "Weekly" the hint text reads;

This means the next execution of this chore is scheduled 1 day after the last execution, but only for the weekdays selected below

I read this to mean that if I tick, say, Thursday and Saturday then after I complete the chore on Thursday the next run should be on Saturday (and vice versa).

The current behaviour seems to be that when completing a chore on Thursday, it schedules it for next Thursday.

Period is currently set to 1 - the lowest setting that the UI allows.

How do I get a chore to behave in this way? Am I doing something wrong?

[berrnd]

Thursday and Saturday then after I complete the chore on Thursday the next run should be on Saturday (and vice versa).

Yes, that's how it should work - I can reproduce this, so is of course a bug.

[berrnd]

Unfortunately there is still a problem about this.

Noticed by @shadow7412 in _https://github.com/grocy/grocy/commit/c6e06ab07c4fa734837a95aba5ef48c35ed8003a#commitcomment-38734078_

Eg: I have a chore that recurs on Saturday and Wednesday. If I complete it on Saturday, it'll set it for next Saturday because it's the first listed in that switch statement despite the fact that Wednesday is closer...

[shadow7412]

This is what I was thinking;

SELECT
  x.chore_id,
  x.last_tracked_time,
  CASE WHEN x.rollover = 1 AND DATETIME('now', 'localtime') > x.next_estimated_execution_time THEN
    DATETIME(STRFTIME('%Y-%m-%d', DATETIME('now', 'localtime')) || ' ' || STRFTIME('%H:%M:%S', x.next_estimated_execution_time))
  ELSE
    x.next_estimated_execution_time
  END AS next_estimated_execution_time,
  x.track_date_only,
  x.next_execution_assigned_to_user_id
FROM (

SELECT
  h.id AS chore_id,
  MAX(l.tracked_time) AS last_tracked_time,
  CASE h.period_type
    WHEN 'manually' THEN '2999-12-31 23:59:59'
    WHEN 'dynamic-regular' THEN DATETIME(MAX(l.tracked_time), '+' || CAST(h.period_days AS TEXT) || ' day')
    WHEN 'daily' THEN DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '+' || CAST(h.period_interval AS TEXT) || ' day')
    WHEN 'weekly' THEN
      (
        select next
        from (
          SELECT 'saturday' as day, DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 6') as next
          UNION
          SELECT 'friday' as day, DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 5') as next
          UNION
          SELECT 'thursday' as day, DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 4') as next
          UNION
          SELECT 'wednesday' as day, DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 3') as next
          UNION
          SELECT 'tuesday' as day, DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 2') as next
          UNION
          SELECT 'monday' as day, DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 1') as next
          UNION
          SELECT 'sunday' as day, DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 0') as next
        )
        WHERE instr(period_config, day) > 0
        ORDER BY day
        LIMIT 1
      )
    WHEN 'monthly' THEN DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '+' || CAST(h.period_interval AS TEXT) || ' month', 'start of month', '+' || CAST(h.period_days - 1 AS TEXT) || ' day')
    WHEN 'yearly' THEN DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '+' || CAST(h.period_interval AS TEXT) || ' years')
  END AS next_estimated_execution_time,
  h.track_date_only,
  h.rollover,
  h.next_execution_assigned_to_user_id
FROM chores h
LEFT JOIN chores_log l
  ON h.id = l.chore_id
  AND l.undone = 0
GROUP BY h.id, h.period_days

) x

Sadly, I'm getting a "misuse of aggregate MAX" error when doing it this way. I might have another crack at it later, but I posted this in case it gave you inspiration.

[shadow7412]

Subqueries seem to do the trick 👌

I'm not liking the line lengths, nor the repeated code, but that's kinda SQL in a netshell...

SELECT
  x.chore_id,
  x.last_tracked_time,
  CASE WHEN x.rollover = 1 AND DATETIME('now', 'localtime') > x.next_estimated_execution_time THEN
    DATETIME(STRFTIME('%Y-%m-%d', DATETIME('now', 'localtime')) || ' ' || STRFTIME('%H:%M:%S', x.next_estimated_execution_time))
  ELSE
    x.next_estimated_execution_time
  END AS next_estimated_execution_time,
  x.track_date_only,
  x.next_execution_assigned_to_user_id
FROM (

SELECT
  h.id AS chore_id,
  MAX(l.tracked_time) AS last_tracked_time,
  CASE h.period_type
    WHEN 'manually' THEN '2999-12-31 23:59:59'
    WHEN 'dynamic-regular' THEN DATETIME(MAX(l.tracked_time), '+' || CAST(h.period_days AS TEXT) || ' day')
    WHEN 'daily' THEN DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '+' || CAST(h.period_interval AS TEXT) || ' day')
    WHEN 'weekly' THEN
      (
        select next
        from (
          SELECT 'saturday' as day, DATETIME(COALESCE((SELECT tracked_time FROM chores_log WHERE chore_id = h.id order by tracked_time desc limit 1), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 6') as next
          UNION
          SELECT 'friday' as day, DATETIME(COALESCE((SELECT tracked_time FROM chores_log WHERE chore_id = h.id order by tracked_time desc limit 1), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 5') as next
          UNION
          SELECT 'thursday' as day, DATETIME(COALESCE((SELECT tracked_time FROM chores_log WHERE chore_id = h.id order by tracked_time desc limit 1), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 4') as next
          UNION
          SELECT 'wednesday' as day, DATETIME(COALESCE((SELECT tracked_time FROM chores_log WHERE chore_id = h.id order by tracked_time desc limit 1), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 4') as next
          UNION
          SELECT 'tuesday' as day, DATETIME(COALESCE((SELECT tracked_time FROM chores_log WHERE chore_id = h.id order by tracked_time desc limit 1), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 3') as next
          UNION
          SELECT 'monday' as day, DATETIME(COALESCE((SELECT tracked_time FROM chores_log WHERE chore_id = h.id order by tracked_time desc limit 1), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 2') as next
          UNION
          SELECT 'sunday' as day, DATETIME(COALESCE((SELECT tracked_time FROM chores_log WHERE chore_id = h.id order by tracked_time desc limit 1), DATETIME('now', 'localtime')), '1 days', '+' || CAST((h.period_interval - 1) * 7 AS TEXT) || ' days', 'weekday 1') as next
        )
        WHERE instr(period_config, day) > 0
        ORDER BY day
        LIMIT 1
      )
    WHEN 'monthly' THEN DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '+' || CAST(h.period_interval AS TEXT) || ' month', 'start of month', '+' || CAST(h.period_days - 1 AS TEXT) || ' day')
    WHEN 'yearly' THEN DATETIME(IFNULL(MAX(l.tracked_time), DATETIME('now', 'localtime')), '+' || CAST(h.period_interval AS TEXT) || ' years')
  END AS next_estimated_execution_time,
  h.track_date_only,
  h.rollover,
  h.next_execution_assigned_to_user_id
FROM chores h
LEFT JOIN chores_log l
  ON h.id = l.chore_id
  AND l.undone = 0
GROUP BY h.id, h.period_days

) x

[shadow7412]

Ugh. This is what happens when I code too late at night.

See https://github.com/grocy/grocy/pull/802