Bash script to create a billing report task in kanbanflow

README.md

Bash script to create a billing report task in kanbanflow

Needs jq >= 1.5

Usage

curl -s https://gist.githubusercontent.com/jroehl/8ba7eb7280278518a52889613dcc02d3/raw/create-billing-report.sh | bash -s "--month=1 --year=2020"

create-billing-report.sh

#!/usr/bin/env bash

# exit when any command fails
set -e

DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" >/dev/null 2>&1 && pwd)"
[[ -f "${DIR}/.env" ]] && source "${DIR}/.env"

# ENV variables
# NEW_TASK_COLUMN_ID
# NEW_TASK_SWIMLANE_ID
# NEW_TASK_TARGET_ID
# API_TOKEN

readonly BASE_URL="https://kanbanflow.com/api/v1"
readonly TASK_PATH="tasks"
readonly BOARD_PATH="board"
readonly AUTH_HEADER="Authorization: Basic $(echo "apiToken:${API_TOKEN}" | base64)"

function first_day() {
  local month=${1}
  local date_type="+%F"
  if [ ${month} -gt 1 ]; then
    echo $(date --utc -d "$((month - 1))/1 month" "${date_type}")
  else
    echo $(date --utc -d "${month}/1 month - 1 month" "${date_type}")
  fi
}

function last_day() {
  local month=${1}
  local year=${2}
  local date_type="+%F"
  echo $(date -d "${year}/${month}/1 + 1 month - 1 day" "${date_type}")
}

function fetch_tasks_until() {
  local until="${1}"
  local swimlane_index="${2:-0}"
  tasks=$(
    curl -X GET -H "${AUTH_HEADER}" -s \
      "${BASE_URL}/${TASK_PATH}?swimlaneIndex=${swimlane_index}&columnName=Done&limit=100&startGroupingDate=${until}"
  )
  echo "${tasks}"
}

function iterate_swimlanes() {
  local start="${1}"
  board=$(
    curl -X GET -H "${AUTH_HEADER}" -s \
      "${BASE_URL}/${BOARD_PATH}"
  )
  results="[]"
  swimlanes=($(echo "${board}" | jq -r '.swimlanes[] | @base64'))
  for i in ${!swimlanes[@]}; do
    tasks=$(fetch_tasks_until ${start} ${i})
    results="$(echo "$tasks" | jq --argjson r "${results}" -r '$r + .[].tasks')"
  done
  echo $results
}

function get_time_spent() {
  local delta="${1}"
  ((h = ${delta} / 3600))
  ((m = (${delta} % 3600) / 60))
  ((s = ${delta} % 60))
  printf "%02d hours %02d minutes %02d seconds\n" $h $m $s
}

function get_customer_name() {
  local arg=${1}
  if [[ "$arg" =~ (^cx:([^,]*)) ]]; then
    echo ${BASH_REMATCH[2]}
  else
    echo "N/A"
  fi
}

function iterate_tasks() {
  local tasks="${1}"
  declare -a billable_tasks
  for row in $(echo "${1}" | jq -r '.[] | @base64'); do
    _jq() {
      echo ${row} | base64 --decode | jq -r "${1}"
    }
    local time_spent=$(get_time_spent $(_jq '.totalSecondsSpent'))
    local time_spent_seconds=$(_jq '.totalSecondsSpent')
    local task_id=$(_jq '._id')
    local name=$(_jq '.name')
    local labels=$(_jq '.labels | map(select(.name | contains("billable") | not)) | [.[].name] | join(", ")')
    local grouping_date=$(_jq '.groupingDate')
    json="
    {
      \"name\": \"${name}\",
      \"timeSpent\": \"${time_spent}\",
      \"timeSpentSeconds\": ${time_spent_seconds},
      \"labels\": \"${labels}\",
      \"customer\": \"$(get_customer_name "${labels}")\",
      \"taskId\": \"${task_id}\",
      \"groupingDate\": \"${grouping_date}\"
    }
    "
    billable_tasks+=("${json}")
    [ -z ${DRY_RUN+x} ] && add_billed_label "${task_id}"
  done
  echo ${billable_tasks[@]} | jq -s '.'
}

function add_billed_label() {
  local task_id="${1}"
  curl -X POST -H "${AUTH_HEADER}" -H "Content-type: application/json" -s \
    "${BASE_URL}/${TASK_PATH}/${task_id}/labels" -d '{ "name": "billed" }' >/dev/null
}

function add_billing_task() {
  local name="${1}"
  local description="${2}"
  local sub_tasks="${3}"
  json_body=$(
    echo "{
      \"name\": \"${name}\",
      \"columnId\": \"${NEW_TASK_COLUMN_ID}\",
      \"swimlaneId\": \"${NEW_TASK_SWIMLANE_ID}\",
      \"position\": \"top\",
      \"color\": \"yellow\",
      \"dates\": [{
        \"dueTimestamp\": \"$(date +%FT%TZ)\",
        \"targetColumnId\": \"${NEW_TASK_TARGET_ID}\"
      }],
      \"labels\": [{ \"name\": \"finance\" }],
      \"description\": ${description},
      \"subTasks\": ${sub_tasks}
    }"
  )
  task_id=$(
    curl -X POST -H "${AUTH_HEADER}" -H "Content-type: application/json" -s \
      "${BASE_URL}/${TASK_PATH}" -d "${json_body}" | jq -r '.taskId'
  )
  echo "Task created (https://kanbanflow.com/t/${task_id})"
}

function get_sub_tasks() {
  local result="${1}"
  grouped=$(echo "$result" | jq -r 'group_by(.customer) | map({"customer": .[0].customer, timeSpentSeconds: map(.timeSpentSeconds) | add})')
  declare -a sub_tasks
  for row in $(echo "${grouped}" | jq -r '.[] | @base64'); do
    _jq() {
      echo ${row} | base64 --decode | jq -r "${1}"
    }
    local time_spent=$(get_time_spent $(_jq '.timeSpentSeconds'))
    local customer=$(_jq '.customer')
    json="
    {
      \"customer\": \"${customer}\",
      \"timeSpent\": \"${time_spent}\"
    }
    "
    sub_tasks+=("${json}")
  done
  echo ${sub_tasks[@]} | jq -s -a '[.[] | { name: "Bill \(.customer?) (\(.timeSpent))" }]'
}

function main() {
  for i in "$@"; do
    case $i in
    -m=* | --month=*)
      MONTH="${i#*=}"
      shift # past argument=value
      ;;
    -y=* | --year=*)
      YEAR="${i#*=}"
      shift # past argument=value
      ;;
    --dry-run)
      DRY_RUN=YES
      shift # past argument with no value
      ;;
    *)
      # unknown option
      ;;
    esac
  done

  MONTH=${MONTH:-$(date +%m)}
  YEAR=${YEAR:-$(date +%Y)}

  first=$(first_day ${MONTH} ${YEAR})
  last=$(last_day ${MONTH} ${YEAR})

  echo "Fetching time entries starting ${first} until ${last}"

  first_epoch=$(date -d ${first} +%s)
  last_epoch=$(date -d ${last} +%s)

  tasks=$(iterate_swimlanes "${last}")

  # filter tasks having label "billable" and not "billed"
  billable_tasks=$(
    echo "${tasks}" | jq --argjson f ${first_epoch} --argjson t ${last_epoch} '
        def billable(a): [.labels?[].name?] | contains(["billable"]);
        def notbilled(a): [.labels?[].name?] | contains(["billed"]) | not;
        def inrange(a): (.groupingDate | strptime("%Y-%m-%d") | mktime) | . >= $f and . <= $t;
        [
          .[] | select(.labels? and billable(.) and notbilled(.) and inrange(.))
        ]
    '
  )

  result_json=$(iterate_tasks "${billable_tasks}")
  description="*Billing entries*"
  description+=$(
    echo $result_json | jq -r 'group_by(.customer) | map(
        "\n- *\(.[0].customer)*\n\(map(. | "  - \(.groupingDate) _\(.timeSpent)_\n \(.name?) \n(\(.labels?))") | join("\n"))"
      ) | join("\n")'
  )

  escaped_sub_tasks=$(get_sub_tasks "${result_json}")
  escaped_description=$(echo "$description" | jq -a -R -s '.')

  if [ -z ${DRY_RUN+x} ]; then
    add_billing_task "Rechnungen schreiben ${first} bis ${last}" "${escaped_description}" "${escaped_sub_tasks}"
  else
    echo "Running in dry-run mode"
    echo ">> Sanitized result"
    echo $result_json | jq

    echo ">> Subtasks"
    echo $escaped_sub_tasks | jq

    echo ">> Description"
    echo $description
  fi
}

main "$@"