Grocy: Feature Request: Email Chore Notification

[skettydog]

I like to set it and forget it. If I could get a notification a set number of days before a chore is due that would be great. DW and I would not have to check Grocy every day for upcoming chores. This would streamline our chores and Grocy usage.

[berrnd]

Great idea, I think a link per chore in the mail to track the execution directly from there would be useful as well...

[jose1711]

This is a very simple implementation in Bash using REST API:

#!/bin/bash
# sends an email to a specified grocy user if a chore is due
# in less than specified number of days (default: 2)
#
# requires:
#   - jq
#   - curl
#   - api key (hardcoded below)
#   - msmtp installed and configured
#
# example:
#    grocy_chore_notifier admin [email protected]
#
api_key="APIKEYAPIKEYAPIKEY"

if [ $# -ne 2 ]
then
  echo "Run this script with two arguments: $0 GROCY_USER EMAIL"
  exit 1
fi

username=$1
email=$2
time_now=$(date +%s)
# start notifying this many days before the chore is due
days_threshold=2
seconds_threshold=$((days_threshold*86400))

function call {
curl -s "http://grocy.lan/api/$1" -H "GROCY-API-KEY:${api_key}"
}

user_id=$(call users  | jq --arg username "${username}" -r 'map(select(.username == $username))[0].id')

chores_due=$(
  call chores | \
   jq -r --arg user_id ${user_id} 'map(select(.next_execution_assigned_to_user_id == $user_id)) | map(.chore_id + ";" + .next_estimated_execution_time)' | \
    grep ';' | tr -d '",' | while IFS=';' read -r chore_id date time
      do
          time_chore=$(date +%s -d "${date} ${time}")
          if [ $((time_chore-time_now)) -le ${seconds_threshold} ]
          then
            chore_name=$(call "chores/${chore_id}" | jq -r '.chore.name')
            echo "${chore_name} (${date} ${time})"
          fi
      done
)

if [ -n "${chores_due}" ]
then
  cat <<HERE
Subject: Grocy notification for ${username} (id: ${user_id})

The following chores are due in less than ${days_threshold} days or overdue:
${chores_due}
HERE
fi | msmtp "${email}"

[jose1711]

And here's a way to do it without API access if execution (and scheduling) of scripts is possible directly on the server hosting grocy:
First an auxiliary useremail table is created containing userid -> email mapping:

$ sqlite3 /www/data/grocy.db
CREATE TABLE useremail ( userid INTEGER PRIMARY KEY UNIQUE, email TEXT);
INSERT INTO useremail VALUES(1, '[email protected]');
INSERT INTO useremail VALUES(2, '[email protected]');
INSERT INTO useremail VALUES(3, '[email protected]');

Then the following is put into script and scheduled via cron to run e. g. every morning:

#!/bin/bash
notified_users="user1 user2 user3"
days_threshold=2

chores_due=$(
cat <<HERE | sqlite3 /www/data/grocy.db
SELECT username,email,GROUP_CONCAT(STRFTIME("%d.%m.", next_estimated_execution_time) || " " || name, "*")
FROM chores_current                                                                                      
JOIN chores ON chores_current.chore_id == chores.id
JOIN users ON chores_current.next_execution_assigned_to_user_id = users.id
JOIN useremail ON users.id == useremail.userid                            
WHERE CAST(strftime('%s', next_estimated_execution_time) as decimal)-CAST(STRFTIME('%s', 'now') as decimal) < (${days_threshold} * 86400)
GROUP BY username;                                                                                                                       
HERE              
)   

if [ -n "${chores_due}" ]
then                     
  echo "${chores_due}" | while IFS='|' read -r user email chores
    do                                                          
    if ! echo "${notified_users}" | grep -qw ${user}; then continue; fi
    echo "Subject: Grocy notification for user ${user}                 

${chores}" | sed 's%\*%\n%g' | msmtp ${email}
    done                                     
fi

Msmtp is configured in /etc/msmtprc (this is just an example):

account default
host smtp.gmail.com
port 587
protocol smtp
auth on
from [email protected]
user [email protected]
password secret_password
tls on
tls_starttls on
tls_certcheck off

File mode must be 600:

chmod 600 /etc/msmtprc

[cybermcm]

I'd love if this feature gets into the docker container ;-)
Maybe it is even possible to integrate apprise?