calculate_time.sh

#!/usr/bin/env -S bash -e

if [[ "$1" = "" ]]; then
  echo "Usage: ./calculate_time.sh FILE.csv"
  exit 1
fi

FILE="$(realpath $1)"

if [[ ! -f "$FILE" ]]; then
  echo "File $FILE does not exist!"
  exit 1
fi

psql <<- SQL
create temporary table summary (
  client text,
  project text,
  duration interval
);

\\copy summary from '$FILE' csv header;

select
  coalesce(project_name, '總計') 專案名稱,
  sum(hours) 工時,
  case
    when project_name is null then null
    else sum(rate)
  end 時薪,
  sum(hours * rate)::int 小計
  from (
    select
      project project_name,
      round((extract(epoch from duration) / 3600), 2) hours,
      case
        when project ~* '急件' then 1600
        else 1000
      end rate
    from summary
  ) s
  group by rollup (project_name)
  order by project_name is null,
  小計 desc;
SQL