~m4v/+junk/Factos

#!/usr/bin/python
# -*- Encoding: UTF-8 -*-
###
# Copyright (c) 2006,2007 Dennis Kaarsemaker
# Copyright (C) 2008 Terence Simpson <tsimpson@ubuntu.com> (stdin on irc.freenode.net)
# Copyright (C) 2010 Elián Hanisch <lambdae2@gmail.com>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of version 2 of the GNU General Public License as
# published by the Free Software Foundation.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
###

import sys, os, sqlite, urllib2, re, time
from math import ceil

from commoncgi import send_page, form, quote

### Variables
NUM_PER_PAGE=50.0
# Directory of the factoids database
datadir = '/home/m4v/ircbot/kubot/data'
#datadir = '/home/m4v/dev/ircbot/scratDev/data'
# Database name (without .db)
default_database = 'Factos.sqlite'
#default_database = 'Factos2.sqlite'

#### You shouldn't have to change anything under this line ###

# Initialize
database = default_database
order_by = 'request_count DESC'
page = 0
search = ''
facts = []
total = 0
channel = 'global'
table = 'factos'
table_dict = {}
sort_dict = {
        'created_at DESC':'Fecha -',
        'created_at ASC':'Fecha +',
        'name DESC': 'Nombre -',
        'name ASC': 'Nombre +', 
        'request_count DESC': 'Popularidad -',
        'request_count ASC':'Popularidad +',
        }

# Read POST
#if 'db' in form:
#    databases = [file for file in os.listdir(datadir)]
#    db = form['db'].value
#    if db in databases:
#        database = db
if 'page' in form:
    try: 
        page = int(form['page'].value)
    except: 
        pass
if 'order' in form:
    if form['order'].value in sort_dict:
        order_by = form['order'].value
if 'search' in form:
    search = form['search'].value

db = sqlite.connect(os.path.join(datadir, database + '.db'))
cursor = db.cursor()
        
# get tables
query_tables = "SELECT type,tbl_name FROM sqlite_master"
cursor.execute(query_tables)
for tbl in cursor.fetchall():
    if tbl[0] == 'table' and tbl[1].startswith('factos_'):
        tbl = tbl[1]
        if tbl == 'factos_#scratbot': continue # #scratbot is a testing channel
        chan = tbl[len('factos_')+1:]
        table_dict[chan] = tbl

if 'channel' in form:
    chan = form['channel'].value
    if chan in table_dict:
        channel = chan
        table = table_dict[chan]

# sort links
print '<br />Ordenar por<br />&middot;'
for sort in ('created_at DESC', 'created_at ASC', 'name DESC', 'name ASC', 'request_count DESC',
        'request_count ASC'):
    print ' <a href="factos.cgi?db=%s&search=%s&order=%s&page=0&channel=%s">%s</a> &middot;' % (database, search,
        sort, channel, sort_dict[sort])

# channel links
print '<br />Canales<br />&middot;'
print ' <a href="factos.cgi?db=%s&search=%s&order=%s&page=0&channel=%s">%s</a> &middot;' %(database,
        search, order_by, 'global', 'global')
for chan in sorted(table_dict):
    print ' <a href="factos.cgi?db=%s&search=%s&order=%s&page=0&channel=%s">#%s</a> &middot;' %(database,
            search, order_by, chan, chan)
print '<br />'

# Note: Fact modes
# 1 - disabled
# 2 - locked
# 4 - alias

# Select factoids
if search:
    keys = [urllib2.unquote(x.strip()) for x in search.split() if len(x.strip()) >=2][:5]
    if not keys:
        keys = ['']
    query = "SELECT name,data,created_by,created_at,request_count FROM '%s' "\
            "WHERE NOT (flags & 1 OR flags & 4) AND (" %table
    count = "SELECT COUNT(name) FROM '%s' WHERE NOT (flags & 1 OR flags & 4) AND " %table
    bogus = False
    for k in keys:
        k = repr('%' + k + '%')
        if bogus:
            query1 += ' OR '
            query2 += ' OR '
        query += "name LIKE %s OR data LIKE %s" % (k, k)
        count += "name LIKE %s OR data LIKE %s" % (k, k)
        bogus=True

    query += ') ORDER BY %s LIMIT %d, %d' % (order_by, NUM_PER_PAGE*page, NUM_PER_PAGE)
    cursor.execute(query)
    facts = cursor.fetchall()
    cursor.execute(count)
    total = cursor.fetchall()[0][0]
else:
    query = "SELECT name,data,created_by,created_at,request_count FROM '%s' "\
            "WHERE NOT (flags & 1 OR flags & 4) ORDER BY %s" %(table, order_by)
            # FIXME due to a limitation of how request_count is done, must fetch all fact until it's
            # fixed
            #LIMIT %d, %d" %(table,
            #        order_by, page*NUM_PER_PAGE, NUM_PER_PAGE)
    count = "SELECT COUNT(*) FROM '%s' WHERE NOT (flags & 1 OR flags & 4)" %table
    cursor.execute(query)
    facts = cursor.fetchall()
    cursor.execute(count)
    total = cursor.fetchall()[0][0]

# Pagination links
npages = int(ceil(total / float(NUM_PER_PAGE)))
print '&middot;'
for i in range(npages):
    print '<a href="factos.cgi?db=%s&search=%s&order=%s&page=%s&channel=%s">%d</a> &middot;' \
            %(database, search, order_by, i, channel, i+1)
    
print '<table cellspacing="0"><tr><th>Facto</th><th>Contenido</th><th>Autor</th></tr>'

url_re = re.compile('(?P<url>(https?://[^\s)>]+|www[^\s)>]+))')
def format_data(s):
    s = quote(s)
    s = stripcolor(s)
    return url_re.sub(link, s)

def link(match):
    url = match.group('url')
    txt = url
    if len(txt) > 30:
        txt = txt[:20] + '&hellip;' + txt[-10:]
    return '<a href="%s">%s</a>' % (url, txt)

_stripcolorre = re.compile(r'\x03(?:\d{1,2},\d{1,2}|\d{1,2}|,\d{1,2}|)')
def stripcolor(s):
    return _stripcolorre.sub('', s)

date_string = lambda t: time.strftime('%d/%m/%y %H:%M', time.localtime(int(t)))

def format_alias(L):
    if isinstance(L, list):
        return ' <br />'.join(L)
    return L

facts = [ list(f) for f in facts ]
for f in facts:
    query = "SELECT name,data,created_by,created_at,request_count FROM factos WHERE " \
            "(flags & 4) AND NOT (flags & 1) AND data='%s'" %f[0]
    cursor.execute(query)
    alias = cursor.fetchall()
    if alias:
        alias = list(alias)
        alias.append(f)
        # sort alias by count, so the most used shows first
        alias.sort(key=lambda x: x[4])
        alias.reverse()
        f[4] = sum([a[4] for a in alias]) # sum all request counts
        # make a list of all names
        f[0] = [a[0] for a in alias]

# sort (this is due to a limitation of how facts are counted in the bot)
order_by = order_by.split()
if order_by[0] == 'request_count':
    facts.sort(key=lambda x: x[4])
    if order_by[1] == 'DESC':
        facts.reverse()

if len(facts) > NUM_PER_PAGE:
    facts = facts[page*NUM_PER_PAGE:(page+1)*NUM_PER_PAGE]

i = 0
for f in facts:
    names = f[0]
    names = format_alias(names)
    data = format_data(f[1])
    author = f[2]
    query_author = "SELECT host FROM usuarios WHERE user_id='%s'" %author
    cursor.execute(query_author)
    author = cursor.fetchall()[0][0] 
    author = author[:author.find('!')]
    date = date_string(f[3])
    count = f[4]
    print '<tr',
    if i % 2: 
        print ' class="bg2"',
    i += 1
    print '><td>%s</td><td>%s</td><td><b>%s</b><br />Agregado&nbsp;el: %s' \
            '<br />Usado&nbsp;%s&nbsp;veces</td>' %(names, data, author, date, count)

print '</table>'

send_page('factoids.tmpl')

# vim:set shiftwidth=4 softtabstop=4 tabstop=4 expandtab textwidth=100: