Wikipedia:Database reports/Inactive users in user groups/Configuration

inactiveuserrights.py

#! /usr/bin/env python
# Public domain; bjweeks, MZMcBride; 2008, 2016

import oursql
import wikitools

import settings

report_title = settings.rootpage + 'Inactive users in user groups'

report_template = u'''\
Users in user groups without any [[Special:Contributions|contributions]] in \
the past year; data as of <onlyinclude>~~~~~</onlyinclude>.

{| class="wikitable sortable plainlinks" style="width:100%%; margin:auto;"
|- style="white-space:nowrap;"
! No.
! User
! Last edit
! User groups
|-
%s
|}
'''

def get_latest_edit(cursor, user_id):
    cursor.execute('''
        SELECT
          IF(rev_timestamp < DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 YEAR),'%Y%m%d%H%i%s'), rev_timestamp, NULL)
        FROM revision_userindex
        WHERE rev_user = ?
        ORDER BY rev_timestamp DESC
        LIMIT 1;
    ''', (user_id,))
    result = cursor.fetchall()
    if result == []:
        return u'[no edits]'
    elif result:
        return result[0][0]
    else:
        return None

wiki = wikitools.Wiki(settings.apiurl)
wiki.login(settings.username, settings.password)

conn = oursql.connect(host=settings.host,
                      db=settings.dbname,
                      read_default_file='~/.my.cnf')
cursor = conn.cursor()

cursor.execute('''
/* inactiveuserrights.py SLOW_OK */
SELECT DISTINCT
  ug_user,
  user_name,
  GROUP_CONCAT(ug_group)
FROM user_groups
JOIN user
ON ug_user = user_id
WHERE ug_group NOT IN ('extendedconfirmed')
GROUP BY ug_user;
''')

users_in_groups = cursor.fetchall()

i = 1
output = []
for user in users_in_groups:
    user_id = int(user[0])
    user_name = u'[[User:{0}|{0}]]'.format(user[1].decode('utf-8'))
    user_groups = user[2].replace(',', ', ')
    latest_edit = get_latest_edit(cursor, user_id)
    if latest_edit == u'[no edits]':
        rev_timestamp = latest_edit
    elif latest_edit:
        rev_timestamp = u'[[Special:Contributions/{0}|{1}]]'.format(
            user[1].decode('utf-8'),
            latest_edit
        )
    else:
        continue
    table_row = u'''| %d
| %s
| %s
| %s
|-''' % (i, user_name, rev_timestamp, user_groups)
    output.append(table_row)
    i += 1

report = wikitools.Page(wiki, report_title)
report_text = report_template % ('\n'.join(output))
report_text = report_text.encode('utf-8')
report.edit(report_text, summary=settings.editsumm, bot=1)

cursor.close()
conn.close()

crontab

0 15 6,20 * * python ~/scripts/database-reports/inactiveuserrights.py > /dev/null