2020/06/23
Openprojectのworkpackageで完了日が設定されていないものを取得する
概要
タスク管理に使っているOpenprojectですが、完了日付がない（または過ぎている）タスクを定期的に抽出したいというニーズが湧いてきました。
Openprojectのweb UI上だとプロジェクト横断的に細かい条件を指定してフィルターするのが煩雑だったため
OpenprojectのPostgreql Databaseに直接ログインして、データの抽出を行いました。
このようなことは、OpenprojectのPluginで実装していくのが良さそうな気もしますが、今はOpenproject serverのアプリとは別のアプリを作って
Postgresql dbなどにアクセスして抽出するような感じができたら良いなと思っています。
実施事項
psqlの実行
まずはpostgresql databaseが起動しているサーバーにログインして、psqlコマンドを実行します。
workpackageのデータ確認
テーブル一覧を確認します。
work_packagesテーブルがそれっぽいです。
\dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------------------+-------+-------------
public | announcements | table | openproject
public | ar_internal_metadata | table | openproject
public | attachable_journals | table | openproject
public | attachment_journals | table | openproject
public | attachments | table | openproject
public | attribute_help_texts | table | openproject
public | auth_sources | table | openproject
public | categories | table | openproject
public | changes | table | openproject
public | changeset_journals | table | openproject
public | changesets | table | openproject
public | changesets_work_packages | table | openproject
public | colors | table | openproject
public | comments | table | openproject
public | cost_entries | table | openproject
public | cost_object_journals | table | openproject
public | cost_objects | table | openproject
public | cost_queries | table | openproject
public | cost_types | table | openproject
public | custom_actions | table | openproject
public | custom_actions_projects | table | openproject
public | custom_actions_roles | table | openproject
public | custom_actions_statuses | table | openproject
public | custom_actions_types | table | openproject
public | custom_fields | table | openproject
public | custom_fields_projects | table | openproject
public | custom_fields_types | table | openproject
public | custom_options | table | openproject
public | custom_styles | table | openproject
public | custom_values | table | openproject
public | customizable_journals | table | openproject
public | delayed_jobs | table | openproject
public | design_colors | table | openproject
public | document_journals | table | openproject
public | documents | table | openproject
public | done_statuses_for_project | table | openproject
public | enabled_modules | table | openproject
public | enterprise_tokens | table | openproject
public | enumerations | table | openproject
public | export_card_configurations | table | openproject
public | forums | table | openproject
public | grid_widgets | table | openproject
public | grids | table | openproject
public | group_users | table | openproject
public | journal_versions | table | openproject
public | journals | table | openproject
public | labor_budget_items | table | openproject
public | ldap_groups_memberships | table | openproject
public | ldap_groups_synchronized_groups | table | openproject
public | material_budget_items | table | openproject
public | meeting_content_journals | table | openproject
public | meeting_contents | table | openproject
public | meeting_journals | table | openproject
public | meeting_participants | table | openproject
public | meetings | table | openproject
public | member_roles | table | openproject
public | members | table | openproject
public | menu_items | table | openproject
public | message_journals | table | openproject
public | messages | table | openproject
public | news | table | openproject
public | news_journals | table | openproject
public | oauth_access_grants | table | openproject
public | oauth_access_tokens | table | openproject
public | oauth_applications | table | openproject
public | ordered_work_packages | table | openproject
public | plaintext_tokens | table | openproject
public | principal_roles | table | openproject
public | project_associations | table | openproject
public | project_statuses | table | openproject
public | projects | table | openproject
public | projects_types | table | openproject
public | queries | table | openproject
public | rates | table | openproject
public | recaptcha_entries | table | openproject
public | relations | table | openproject
public | repositories | table | openproject
public | role_permissions | table | openproject
public | roles | table | openproject
public | schema_migrations | table | openproject
public | sessions | table | openproject
public | settings | table | openproject
public | statuses | table | openproject
public | time_entries | table | openproject
public | time_entry_journals | table | openproject
public | tokens | table | openproject
public | two_factor_authentication_devices | table | openproject
public | types | table | openproject
public | user_passwords | table | openproject
public | user_preferences | table | openproject
public | users | table | openproject
public | version_settings | table | openproject
public | versions | table | openproject
public | watchers | table | openproject
public | webhooks_events | table | openproject
public | webhooks_logs | table | openproject
public | webhooks_projects | table | openproject
public | webhooks_webhooks | table | openproject
public | wiki_content_journals | table | openproject
public | wiki_contents | table | openproject
public | wiki_pages | table | openproject
public | wiki_redirects | table | openproject
public | wikis | table | openproject
public | work_package_journals | table | openproject
public | work_packages | table | openproject
public | workflows | table | openproject
(106 rows)
work_packagesのテーブル定義を確認します。
\d work_packages;
Table "public.work_packages"
Column | Type | Modifiers
-------------------------+-----------------------------+------------------------------------------------------------
id | integer | not null default nextval('work_packages_id_seq'::regclass)
type_id | integer | not null default 0
project_id | integer | not null default 0
subject | character varying | not null default ''::character varying
description | text |
due_date | date |
category_id | integer |
status_id | integer | not null default 0
assigned_to_id | integer |
priority_id | integer | default 0
fixed_version_id | integer |
author_id | integer | not null default 0
lock_version | integer | not null default 0
done_ratio | integer | not null default 0
estimated_hours | double precision |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
start_date | date |
responsible_id | integer |
cost_object_id | integer |
position | integer |
story_points | integer |
remaining_hours | double precision |
derived_estimated_hours | double precision |
Indexes:
"work_packages_pkey" PRIMARY KEY, btree (id)
"index_work_packages_on_assigned_to_id" btree (assigned_to_id)
"index_work_packages_on_author_id" btree (author_id)
"index_work_packages_on_category_id" btree (category_id)
"index_work_packages_on_created_at" btree (created_at)
"index_work_packages_on_fixed_version_id" btree (fixed_version_id)
"index_work_packages_on_project_id" btree (project_id)
"index_work_packages_on_project_id_and_updated_at" btree (project_id, updated_at)
"index_work_packages_on_responsible_id" btree (responsible_id)
"index_work_packages_on_status_id" btree (status_id)
"index_work_packages_on_type_id" btree (type_id)
"index_work_packages_on_updated_at" btree (updated_at)
Referenced by:
TABLE "ordered_work_packages" CONSTRAINT "fk_rails_fe038e4e03" FOREIGN KEY (work_package_id) REFERENCES work_packages(id) ON DELETE CASCADE
データ抽出
実際に、完了日が設定されていないデータを以下のように抽出しました。
あとはこのデータをtxtファイルなどで保存して、spread sheetなどに加工すれば、一覧で見れるようになります。
ポイントとしては以下です。
- work_packages.due_dateに完了日付が設定されているので、そこがnullのものを抽出しています。
- statusesというテーブルにworkpackageのステータスが定義されているので、結合して完了してあるものは除外しています。
Note
この業務が何度も実行されるべきものであれば、web viewなどを作っても良いかなと考えています。
select
w.id,
w.subject,
p.name as project_name,
s.name as status_name,
w.assigned_to_id as assigned_user_id,
w.due_date
from
work_packages as w
left join projects as p
on p.id = w.project_id
left join statuses as s
on s.id = w.status_id
where
s.name != 'Closed'
and w.due_date is null
order by
project_name,
w.id
;
