#! /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()
