#! /usr/bin/env python
# Public domain; MZMcBride; 2011
import oursql
import wikitools
import settings
report_title = settings.rootpage + 'WikiLove usage'
report_template = u'''\
[[mw:Extension:WikiLove|WikiLove]] usage statistics; \
data as of <onlyinclude>~~~~~</onlyinclude>.
== Message types ==
{| class="wikitable sortable plainlinks"
|- style="white-space:nowrap;"
! No.
! Type
! Uses
|-
%s
|}
== Senders ==
{| class="wikitable sortable plainlinks"
|- style="white-space:nowrap;"
! No.
! User
! Uses
|-
%s
|}
== Custom images ==
{| class="wikitable sortable plainlinks"
|- style="white-space:nowrap;"
! No.
! Image
! Uses
|-
%s
|}
== Uses per day ==
{| class="wikitable sortable plainlinks"
|- style="white-space:nowrap;"
! Day
! Uses
|-
%s
|- class="sortbottom"
! Total
! style="text-align:left;" | %s
|}
'''
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()
types = []
i = 1
cursor.execute('''
/* wikilovestats.py SLOW_OK */
SELECT
wll_type,
COUNT(wll_type)
FROM wikilove_log
GROUP BY wll_type
ORDER BY COUNT(wll_type) DESC;
''')
for row in cursor.fetchall():
wll_type = unicode(row[0], 'utf-8')
count = row[1]
table_row = u'''\
| %d
| %s
| %s
|-''' % (i, wll_type, count)
types.append(table_row)
i += 1
senders = []
i = 1
cursor.execute('''
/* wikilovestats.py SLOW_OK */
SELECT
user_name,
COUNT(wll_sender)
FROM wikilove_log
JOIN user
ON user_id = wll_sender
GROUP BY wll_sender
HAVING COUNT(wll_sender) > 2
ORDER BY COUNT(wll_sender) DESC
LIMIT 20;
''')
for row in cursor.fetchall():
user_name = u'[[User:%s|%s]]' % (unicode(row[0], 'utf-8'), unicode(row[0], 'utf-8'))
count = row[1]
table_row = u'''\
| %d
| %s
| %s
|-''' % (i, user_name, count)
senders.append(table_row)
i += 1
custom_images = []
i = 1
cursor.execute('''
/* wikilovestats.py SLOW_OK */
SELECT
wlil_image,
COUNT(wlil_image)
FROM wikilove_image_log
GROUP BY wlil_image
HAVING COUNT(wlil_image) > 3
ORDER BY COUNT(wlil_image) DESC
LIMIT 20;
''')
for row in cursor.fetchall():
wlil_image = u'[[:%s|%s]]' % (unicode(row[0], 'utf-8'), unicode(row[0], 'utf-8').strip('File:'))
count = row[1]
table_row = u'''\
| %d
| %s
| %s
|-''' % (i, wlil_image, count)
custom_images.append(table_row)
i += 1
days = []
total = 0
cursor.execute('''
/* wikilovestats.py SLOW_OK */
SELECT
DATE(CONCAT(YEAR(wll_timestamp),"-",MONTH(wll_timestamp),"-",DAY(wll_timestamp))) AS day,
COUNT(wll_timestamp) AS uses
FROM wikilove_log
GROUP BY day
ORDER BY day ASC;
''')
for row in cursor.fetchall():
day = row[0]
uses = row[1]
total += int(uses)
table_row = u'''\
| %s
| %s
|-''' % (day, uses)
days.append(table_row)
report = wikitools.Page(wiki, report_title)
report_text = report_template % ('\n'.join(types),
'\n'.join(senders),
'\n'.join(custom_images),
'\n'.join(days),
total)
report_text = report_text.encode('utf-8')
report.edit(report_text, summary=settings.editsumm, bot=1)
cursor.close()
conn.close()
25 0 * * * PYTHONPATH=$HOME/scripts python $HOME/scripts/database-reports/wikilovestats.py > /dev/null
