#!/usr/bin/env python
# encoding: utf-8
"""
ForgottenPages.py
Created by Tim Sears on 2012-03-16.
Copyright (c) Tim Sears. All rights reserved.
"""
##config
from CONFIG import *
report_title='Wikipedia:Database_reports/Forgotten_articles'
report_template = u'''
List of non-[[ويكيبيديا:DAB|disambiguation]], non-[[ويكيبيديا:REDIRECT|redirect]] articles that have not been edited in 3 years. If there are more than 1000 articles, the report is limited to the oldest 10000.
Report as of <onlyinclude>~~~~~</onlyinclude>:
{| class="wikitable sortable"
|-
! Article
! Timestamp of last edit
! Number of edits to date
! Number of [[:Category:Article_message_boxes|article message boxes]] on article
|-
%s
|}
<small>(Took %.2f seconds to generate)</small>
'''
##
query= '''SELECT /* SLOW_OK */ mp.page_title AS t,
TIMESTAMP((SELECT rev_timestamp
FROM revision
WHERE rev_id = mp.page_latest)) AS ts, (SELECT COUNT(*) from revision where rev_page=mp.page_id) as ec,
(SELECT count(*) from templatelinks join page as tp on tp.page_title=tl_title and tp.page_namespace=10
join categorylinks as s on s.cl_to='Article_message_boxes' and s.cl_from=tp.page_id
where tl_namespace=10
and tl_from=mp.page_id) as tc
FROM page as mp
LEFT OUTER JOIN categorylinks as m
ON m.cl_to in ('Article_Feedback_Blacklist','All_set_index_articles','All_article_disambiguation_pages' )
AND m.cl_from = mp.page_id
WHERE mp.page_is_redirect = 0
AND mp.page_namespace = 0
AND mp.page_latest < (SELECT rev_id
FROM revision
WHERE rev_timestamp > Date_format(DATE_SUB(NOW(),
INTERVAL 3 YEAR),
'%Y%m%d%H%i%s')
LIMIT 1)
AND m.cl_to IS NULL order by ts asc LIMIT 1000;'''
db = connect(host=HOSTNAME, db=DATABSENAME, read_default_file='~/.my.cnf')
cursor=db.cursor()
t=time()
print '* Running query "Forgotten Articles"...'
cursor.execute(query)
print ':Done! Took %f seconds.'%(time()-t)
print '* Generating table...'
rows=''
for pagename,lastedit,editcount,maitnencecount in cursor.fetchall():
if maitnencecount>=2:
maitnencecount-=1
rows+="""\n|[[:%s]]
|%s
|%d
|%d
|-"""%(unicode(pagename.replace('_',' '),'utf-8'), lastedit,editcount,maitnencecount)
print ':Done!'
report_template=report_template%(rows,time()-t)
report_page=wikipedia.Page(wikipedia.getSite(),report_title)
report_page.put(report_template,comment='Updating Page...')
