crontab

30 17 * * 1,2,3,4,5 cd /root && ./worklog_7day_statistics.sh --alert_slack 2>&1 | /usr/bin/logger -t worklog_7day_statistics

lib.sh

#!/usr/bin/env bash

_jira_docker_exec_postgres () {
ssh root@project <<SSH
  set -eu
  docker exec -i postgres_for_jira bash <<DOCKER
    set -eu
      $(cat)
DOCKER
SSH
}

_jira_su_postgres () {
_jira_docker_exec_postgres <<DOCKER
  su - postgres <<SU
    set -eu
    $(cat)
SU
DOCKER
}

exec_psql_jira () {
_jira_su_postgres <<SU | grep "^OUTPUT" | sed 's/^OUTPUT//'
  psql jira ${1:-} <<\SQL | sed 's/^/OUTPUT/'
    $(cat)
SQL
SU
}

alert_slack () {
  curl -X POST --data-urlencode "payload={\"channel\": \"$1\", \"text\": \"$(echo "$2"| sed 's/\"/ /g')\"}" https://hooks.slack.com/services/SECRET
}

worklog_7day_statistics.sh

#!/usr/bin/env bash

source lib.sh

get_data () {
exec_psql_jira <<SQL
  SELECT
    u.first_name || ' ' ||
    u.last_name as name,
    to_char(week.spent/3600, 'FM999G999G999G999D00') as total,
    to_char(today.spent/3600, 'FM999G999G999G999D00') as today,
    to_char(yesterday.spent/3600, 'FM999G999G999G999D00') as yesterday,
    to_char(day3.spent/3600, 'FM999G999G999G999D00') as day3,
    to_char(day4.spent/3600, 'FM999G999G999G999D00') as day4,
    to_char(day5.spent/3600, 'FM999G999G999G999D00') as day5,
    to_char(day6.spent/3600, 'FM999G999G999G999D00') as day6,
    to_char(day7.spent/3600, 'FM999G999G999G999D00') as day7
  FROM (SELECT DISTINCT app_user.user_key, cwd_user.first_name, cwd_user.last_name FROM app_user JOIN cwd_user ON app_user.lower_user_name=cwd_user.lower_user_name WHERE cwd_user.active=1) u
  JOIN (SELECT author, sum(timeworked) as spent FROM worklog WHERE startdate + interval '6 hours' > date_trunc('day', now()) - interval '6 days' GROUP BY author) week ON week.author = u.user_key AND week.spent > 0
  LEFT JOIN (SELECT author, sum(timeworked) as spent FROM worklog WHERE date_trunc('day', startdate + interval '6 hours') = date_trunc('day', now()) GROUP BY author) today ON today.author = u.user_key
  LEFT JOIN (SELECT author, sum(timeworked) as spent FROM worklog WHERE date_trunc('day', startdate + interval '6 hours') = date_trunc('day', now() - interval '1 days') GROUP BY author) yesterday ON yesterday.author = u.user_key
  LEFT JOIN (SELECT author, sum(timeworked) as spent FROM worklog WHERE date_trunc('day', startdate + interval '6 hours') = date_trunc('day', now() - interval '2 days') GROUP BY author) day3 ON day3.author = u.user_key
  LEFT JOIN (SELECT author, sum(timeworked) as spent FROM worklog WHERE date_trunc('day', startdate + interval '6 hours') = date_trunc('day', now() - interval '3 days') GROUP BY author) day4 ON day4.author = u.user_key
  LEFT JOIN (SELECT author, sum(timeworked) as spent FROM worklog WHERE date_trunc('day', startdate + interval '6 hours') = date_trunc('day', now() - interval '4 days') GROUP BY author) day5 ON day5.author = u.user_key
  LEFT JOIN (SELECT author, sum(timeworked) as spent FROM worklog WHERE date_trunc('day', startdate + interval '6 hours') = date_trunc('day', now() - interval '5 days') GROUP BY author) day6 ON day6.author = u.user_key
  LEFT JOIN (SELECT author, sum(timeworked) as spent FROM worklog WHERE date_trunc('day', startdate + interval '6 hours') = date_trunc('day', now() - interval '6 days') GROUP BY author) day7 ON day7.author = u.user_key
  ORDER by name;
SQL
}

report="$(whoami)@${HOSTNAME} @chanel Статистика по worklog в jira за 7 дней:\n
\`\`\`\\n
$(get_data)\n
\`\`\`\n"

printf "$report\n"

if [ "${1:-}" == "--alert_slack" ]; then
  alert_slack "random" "$report"
fi