Biannual followup to T332676
1. Optional: Run: `SELECT u.userName, CONCAT("https://phabricator.wikimedia.org/T",t.id) FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND (e.dst = "PHID-PROJ-iuj2chpabktaluedrqsf" OR e.dst = "PHID-PROJ-hwr4habt5dzuabb5w2xz")) AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) AND t.phid IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND e.dst = "PHID-PROJ-onnxucoedheq3jevknyr") ORDER BY u.userName, t.id;` to go through open assigned tasks with #patch-for-review to manually check if they should still have that project tag
2. Run: `SELECT u.userName, ue.address FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND (e.dst = "PHID-PROJ-onnxucoedheq3jevknyr" OR e.dst = "PHID-PROJ-iuj2chpabktaluedrqsf" OR e.dst = "PHID-PROJ-hwr4habt5dzuabb5w2xz")) AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) GROUP BY u.userName;` only to get the number of different assignees, to be used in the nagging email
3. Run: `SELECT u.userName, ue.address, t.id FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND (e.dst = "PHID-PROJ-onnxucoedheq3jevknyr" OR e.dst = "PHID-PROJ-iuj2chpabktaluedrqsf" OR e.dst = "PHID-PROJ-hwr4habt5dzuabb5w2xz")) AND t.phid NOT IN (SELECT ta.objectPHID FROM phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified > (UNIX_TIMESTAMP() - 63072000))) ORDER BY u.userName, t.id;` to get actual data (which intentionally excludes tasks with #patch-for-review or #epic or #periodic-update)
4. Convert output of second SQL query to a CSV file, using the `space` character as column delimiter
5. Remove duplicate task IDs
6. Use script at https://gitlab.com/aklapper/wikimedia-maniphest-assignee-nagging to create raw email file to import into your email client
7. Send first round of emails
8. [FUTURE] Wait three weeks
9. [FUTURE] Run updated query to see which tasks are left
10. [FUTURE] Send second and last round of emails
11. [FUTURE] Wait two weeks
12. Remove assignee for tasks left and add explanatory text comment:
> Removing task assignee as this open task has been assigned for more than two years - See the email sent to task assignee on <INSERT DATE HERE>.
> Please assign this task to yourself again if you still realistically [plan to] work on this task - it would be welcome! :)
> If this task has been resolved in the meantime, or [should not be worked on by anybody](https://www.mediawiki.org/wiki/Bug_management/Bug_report_life_cycle) ("declined"), please update its task status via "Add Action… 🡒 Change Status".
> Also see https://www.mediawiki.org/wiki/Bug_management/Assignee_cleanup for tips how to best manage your individual work in Phabricator. Thanks!
Can automate that a bit by using the API, e.g. `echo '{"objectIdentifier":"TXXXXXX", "transactions": [{"type": "owner", "value":null}, {"type": "comment", "value":"@XXXXXX: Per emails from Date1 and Date2 and https://www.mediawiki.org/wiki/Bug_management/Assignee_cleanup , I am resetting the assignee of this task because there has not been progress lately (please correct me if I am wrong!). Resetting the assignee avoids the impression that somebody is already working on this task. It also allows others to potentially work towards fixing this task. Please claim this task again when you plan to work on it (via {nav name=Add Action... > Assign / Claim} in the dropdown menu) - it would be welcome. Thanks for your understanding!"}]}' | /var/www/html/phorge/arcanist/bin/arc call-conduit --conduit-uri https://phabricator.wikimedia.org --conduit-token xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx maniphest.edit --`
13. Create followup task for same stuff in half a year.