أرابيكا:تقارير قاعدة البيانات/WikiLove usage/Configuration

wikilovestats.py

#! /usr/bin/env python
# Public domain; MZMcBride; 2011

import datetime
import MySQLdb
import wikitools
import settings

report_title = settings.rootpage + 'WikiLove usage'

report_template = u'''\
[[mw:Extension:WikiLove|WikiLove]] usage statistics; \
data as of <onlyinclude>%s</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
|}
'''

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

conn = MySQLdb.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'[[مستخدم:%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

cursor.execute('''
               SELECT
                 UNIX_TIMESTAMP() - UNIX_TIMESTAMP(rc_timestamp)
               FROM recentchanges
               ORDER BY rc_timestamp DESC
               LIMIT 1;
               ''')
rep_lag = cursor.fetchone()[0]
time_diff = datetime.datetime.utcnow() - datetime.timedelta(seconds=rep_lag)
current_of = time_diff.strftime('%H:%M, %d %B %Y (UTC)')

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

cursor.close()
conn.close()

crontab

25 0 * * * PYTHONPATH=$HOME/scripts python $HOME/scripts/database-reports/wikilovestats.py > /dev/null