Biannual followup to T303689

1. 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))) GROUP BY u.userName; only to get the number of different assignees, to be used in the nagging email
2. 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
3. Convert output of second SQL query to a CSV file
4. Remove duplicate task IDs
5. Use script at https://gitlab.com/aklapper/wikimedia-maniphest-assignee-nagging to create raw email file to import into your email client
6. Send first round of emails
7. [FUTURE] Wait three weeks
8. [FUTURE] Run updated query to see which tasks are left
9. [FUTURE] Send second and last round of emails
10. [FUTURE] Wait two four weeks
11. 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 Feburary 22nd, 2023.
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 ("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!

12. Create followup task for same stuff in half a year: T332676