def _list_homonyms(db, word):
res = db.query(("SELECT w.wid, w.word, wc.name FROM word w, wordclass wc " +
"WHERE w.class = wc.classid AND w.word = '%s'") \
% jotools.escape_sql_string(word))
h = u''
for r in res.getresult():
h = h + u'<a href="edit?wid=%i">%s(%s)</a> ' \
% (r[0], jotools.escape_html(unicode(r[1], 'UTF-8')),
jotools.escape_html(unicode(r[2], 'UTF-8')))
if len(h) == 0: return u''
else: return _(u'Homonyms') + u': ' + h
def add_from_db(req):
(uid, uname, editable) = jotools.get_login_user(req)
if not editable:
joheaders.error_page(req, _(u'You are not allowed to edit data'))
return '\n'
if req.method != 'GET':
joheaders.error_page(req, _(u'Only GET requests are allowed'))
return '\n'
db = jodb.connect()
words_per_page = 15
category = jotools.get_param(req, 'category', None)
if category == None: condition = ""
else: condition = "AND coalesce(info, '') = '%s'" \
% jotools.escape_sql_string(category)
results = db.query("SELECT count(*) FROM raw_word WHERE processed = FALSE %s" \
% condition)
nwords = results.getresult()[0][0]
if nwords <= words_per_page: limit = ""
else: limit = "LIMIT %i OFFSET %i" % (words_per_page,
random.randint(0, nwords - words_per_page))
results = db.query(("SELECT word, coalesce(notes, '') FROM raw_word " +
"WHERE processed = FALSE %s " +
"ORDER BY word %s") % (condition, limit))
if results.ntuples() == 0 and category == None:
joheaders.error_page(req, _(u'There are no words to be added'))
return '\n'
if results.ntuples() == 0 and category != None:
joheaders.error_page(req, _(u'There are no words to be added') + u' ' +
_(u'in category %s') % jotools.escape_html(category))
return '\n'
class_res = db.query("select classid, name from wordclass").getresult()
joheaders.page_header_navbar_level1(req, _(u"Add words"), uid, uname)
jotools.write(req, u'<form method="post" action="add">\n')
jotools.write(req, u'<table class="border">\n')
jotools.write(req, u'<tr><th>%s</th><th>%s</th><th>%s</th></tr>\n' \
% (_(u'Word'), _(u'Word class'), _(u'Notes')))
i = 0
for result in results.getresult():
word = unicode(result[0], 'UTF-8')
notes = unicode(result[1], 'UTF-8')
jotools.write(req, u'<tr><td><input type="hidden" name="origword%i" value=%s />' \
% (i, jotools.escape_form_value(word)))
jotools.write(req, u'<input type="text" name="word%i" value=%s /></td><td>' \
% (i, jotools.escape_form_value(word)))
jotools.write(req, _get_class_selector(class_res, None, i, True))
jotools.write(req, u'</td><td>')
jotools.write(req, jotools.escape_html(notes))
jotools.write(req, u'</td></tr>\n')
i = i + 1
jotools.write(req, u'</table>\n' +
u'<p><input type="submit" value="%s"></p></form>\n' % _(u"Add words"))
joheaders.page_footer_plain(req)
return '\n'
def list(req):
(uid, uname, editable) = jotools.get_login_user(req)
db = jodb.connect()
tasks = db.query("SELECT t.tid, t.descr, t.sql, COUNT(DISTINCT tw.wid) FROM task t " +
"LEFT JOIN task_word tw ON (t.tid = tw.tid) " +
"GROUP BY t.tid, t.descr, t.sql ORDER BY t.tid")
if tasks.ntuples() == 0:
joheaders.error_page(req, _(u'There are no tasks.'))
return '\n'
joheaders.page_header_navbar_level1(req, _(u"tasks"), uid, uname)
jotools.write(req, u"<p>%s:</p>\n" % _(u'Choose a task'))
jotools.write(req, (u'<table class="border"><tr><th>%s</th><th>%s</th>' +
u'<th>%s *</th><th>%s *</th></tr>\n') \
% (_(u'Task'), _(u'Total words'), _(u'Words left'), _(u'Completed')))
for task in tasks.getresult():
wordcount = db.query("SELECT COUNT(*) FROM (%s) AS q" % task[2]).getresult()[0][0]
jotools.write(req, u'<tr><td><a href="work?tid=%i">' % task[0])
jotools.write(req, u'%s</a></td>' % jotools.escape_html(unicode(task[1],'UTF-8')))
jotools.write(req, u'<td>%i</td>' % wordcount)
jotools.write(req, u'<td>%i</td>' % (wordcount - task[3]))
if wordcount == 0: pleft = u'-'
else: pleft = task[3] * 100 / wordcount
jotools.write(req, u'<td>%s %%</td></tr>\n' % pleft)
jotools.write(req, u"</table>\n")
# "Words left" is an approximation, because all of the checked words may not belong to
# this task any more. Calculating the exact numbers is too slow to do here.
jotools.write(req, u"<p>*) %s.</p>" % _(u'Number of words left is an estimate'))
joheaders.page_footer_plain(req)
return '\n'
def categories(req):
(uid, uname, editable) = jotools.get_login_user(req)
if not editable:
joheaders.error_page(req, _(u'You are not allowed to edit data'))
return '\n'
db = jodb.connect()
results = db.query("SELECT coalesce(info, ''), count(*) FROM raw_word " +
"WHERE processed = FALSE " +
"GROUP BY coalesce(info, '') " +
"ORDER BY coalesce(info, '') ")
if results.ntuples() == 0:
joheaders.error_page(req, _(u'There are no words to be added'))
return '\n'
joheaders.page_header_navbar_level1(req, _(u"Add words"), uid, uname)
jotools.write(req, u"<p>%s:</p>\n" \
% _(u'Choose a category from which you want to add words'))
jotools.write(req, u"<table><tr><th>%s</th><th>%s</th></tr>\n" \
% (_(u'Category'), _(u'Words left')))
for result in results.getresult():
cat = unicode(result[0], 'UTF-8')
if cat == u'': cats = u'(' + _(u'no category') + u')'
else: cats = cat
jotools.write(req, (u'<tr><td><a href="add_from_db?category=%s">%s</a></td>' +
u'<td>%i</td></tr>\n') \
% (jotools.escape_url(result[0]), jotools.escape_html(cats), result[1]))
jotools.write(req, u"</table>\n")
jotools.write(req, u'<p><a href="add_from_db">%s ...</a></p>\n' % _(u'All words'))
joheaders.page_footer_plain(req)
return '\n'
def _get_class_selector(classlist, cid, row, include_no_class): if cid == None and row != -1: retstr = u'' for res in classlist: retstr = retstr + (u'<label class="nobr"><input type="radio" name="class%i" ' + u'value="%i">%s</input></label>') \ % (row, res[0], jotools.escape_html(unicode(res[1], 'UTF-8'))) if include_no_class: retstr = retstr + (u'<label class="nobr"><input type="radio" name="class%i" ' + 'value="0">%s</input></label>') % (row, _(u'reject')) return retstr else: for res in classlist: if res[0] == cid: return (u'<input type="hidden" name="class%i" value="%i" />' + u'%s') % (row, cid, jotools.escape_html(unicode(res[1], 'UTF-8'))) return u' '
def _add_entry_fields(req, db, words = None, count = 1):
class_res = db.query("select classid, name from wordclass").getresult()
if words == None:
for i in range(count):
jotools.write(req, u'<tr><td><input type="text" name="word%i" /></td><td>' % i)
for res in class_res:
jotools.write(req, (u'<label><input type="radio" name="class%i" ' +
'value="%i">%s</input></label>\n') \
% (i, res[0], jotools.escape_html(unicode(res[1], 'UTF-8'))))
jotools.write(req, u'</td></tr>\n')
return
confirm_column = False
for word in words:
if word['try_again'] == True:
confirm_column = True
break
i = 0
for word in words:
jotools.write(req, u'<tr><td>')
if word['try_again']:
if word['oword'] != None:
jotools.write(req, u'<input type="hidden" name="origword%i" value=%s />' \
% (i, jotools.escape_form_value(word['oword'])))
jotools.write(req, u'<input type="hidden" name="word%i" value=%s />%s</td><td>' \
% (i, jotools.escape_form_value(word['word']),
jotools.escape_html(word['word'])))
incnocls = word['oword'] != None
jotools.write(req, _get_class_selector(class_res, word['cid'], i, incnocls))
jotools.write(req, u'<td><input type="checkbox" name="confirm%i"></td><td>' % i)
jotools.write(req, word['error'])
jotools.write(req, u'</td>')
i = i + 1
else:
jotools.write(req, jotools.escape_html(word['word']))
jotools.write(req, u'</td><td>')
jotools.write(req, _get_class_selector(class_res, word['cid'], -1, False))
jotools.write(req, u'</td><td>')
if confirm_column: jotools.write(req, u' </td><td>')
jotools.write(req, word['error'])
jotools.write(req, u'</td>')
jotools.write(req, u'</tr>\n')
def show(req):
(uid, uname, editable) = jotools.get_login_user(req)
if not editable:
joheaders.error_page(req, _(u'You are not allowed to edit data'))
return '\n'
tid = jotools.toint(jotools.get_param(req, "tid", "0"))
if tid == 0:
joheaders.error_page(req, _(u'Parameter %s is required') % u'tid')
return '\n'
words_per_page = 20
db = jodb.connect()
taskq = db.query("SELECT sql, orderby FROM task WHERE tid = %i" % tid)
if taskq.ntuples() != 1:
joheaders.error_page(req, u'Parameter %s is wrong' % u'tid')
return '\n'
tasksql = taskq.getresult()[0][0]
taskorder = taskq.getresult()[0][1]
results = db.query(("SELECT w.wid, w.word FROM word w, (%s) t " +
"WHERE t.wid = w.wid AND w.wid NOT IN " +
"(SELECT tw.wid FROM task_word tw WHERE tw.tid = %i)" +
"ORDER BY %s") % (tasksql, tid, taskorder))
joheaders.page_header_nonavbar(req, u"%s %i" % (_(u'task'), tid))
jotools.write(req, u'<form method="post" action="save">\n')
jotools.write(req, u'<table class="border">\n<tr><th>%s</th><th>%s</th></tr>\n' \
% (_(u'OK'), _(u'Word')))
firstword = random.randint(0, max(results.ntuples() - words_per_page, 0))
restuples = results.getresult()
for i in range(firstword, min(firstword + words_per_page, results.ntuples())):
word = restuples[i]
jotools.write(req, u'<tr><td><input type="checkbox" name="checked%i" /></td>' \
% word[0])
jotools.write(req, (u'<td><a href="../word/edit?wid=%i" target="right">%s' +
u'</a></td></tr>\n') \
% (word[0], jotools.escape_html(unicode(word[1], 'UTF-8'))))
jotools.write(req, u'</table>')
jotools.write(req, u'<p><input type="hidden" name="tid" value="%i" />' % tid)
jotools.write(req, u'<input type="submit" value="%s"></form></p>' % _(u'Save checked'))
jotools.write(req, u'<p><a href="../" target="_top">%s</a></p>\n' \
%_(u'Back to main page'))
joheaders.page_footer_plain(req)
return '\n'
def listchanges(req, sdate = None, edate = None):
db = jodb.connect()
(uid, uname, editable) = jotools.get_login_user(req)
joheaders.page_header_navbar_level1(req, _(u'List changes'), uid, uname)
edt = datetime.datetime.now()
sdt = edt - datetime.timedelta(days=1)
if sdate != None:
try:
stime = time.strptime(sdate, u'%Y-%m-%d')
sdt = datetime.datetime(*stime[0:5])
except:
jotools.write(req, "<p>%s</p>\n" % _("Invalid start date"))
if edate != None:
try:
etime = time.strptime(edate, u'%Y-%m-%d')
edt = datetime.datetime(*etime[0:5])
except:
jotools.write(req, "<p>%s</p>\n" % _("Invalid end date"))
sdate_s = sdt.strftime('%Y-%m-%d')
edate_s = edt.strftime('%Y-%m-%d')
jotools.write(req, u"""
<form method="get" action="listchanges">
<label>%s <input type="text" name="sdate" value="%s"/></label><br />
<label>%s <input type="text" name="edate" value="%s"/></label><br />
<input type="submit" /> <input type="reset" />
</form>
""" % (_(u'Start date'), sdate_s, _(u'End date'), edate_s))
# Increase edt by one day to make the the SQL between operator act on timestamps
# in a more intuitive way.
edt = edt + datetime.timedelta(days=1)
edate_s = edt.strftime('%Y-%m-%d')
results = db.query("""
SELECT u.uname, to_char(w.ctime, 'YYYY-MM-DD HH24:MI:SS'),
coalesce(u.firstname, ''), coalesce(u.lastname, ''),
'%s', NULL, w.wid, w.word
FROM word w, appuser u WHERE w.cuser = u.uid AND w.ctime BETWEEN '%s' AND '%s'
UNION
SELECT u.uname, to_char(e.etime, 'YYYY-MM-DD HH24:MI:SS'),
coalesce(u.firstname, ''), coalesce(u.lastname, ''),
e.message, e.comment, w.wid, w.word
FROM appuser u, event e, word w WHERE u.uid = e.euser AND e.eword = w.wid
AND e.etime BETWEEN '%s' AND '%s'
ORDER BY 2 DESC""" % (_(u'Word created').encode('UTF-8'), sdate_s, edate_s, sdate_s, edate_s));
if results.ntuples() > 1000:
jotools.write(req, u'<p>%s</p>' % _(u'Too many changes, use narrower date interval.'))
joheaders.page_footer_plain(req)
return '\n'
retstr = u''
for result in results.getresult():
wordlink = u'<a href="../word/edit?wid=%i">%s</a>' \
% (result[6], jotools.escape_html(unicode(result[7], 'UTF-8')))
date = result[1]
user = jotools.escape_html(unicode(result[2], 'UTF-8')) + u" " + \
jotools.escape_html(unicode(result[3], 'UTF-8')) + u" (" + \
jotools.escape_html(unicode(result[0], 'UTF-8')) + u")"
retstr = retstr + (u'<div class="logitem"><p class="date">%s %s %s</p>\n' \
% (wordlink, user, date))
if result[4] != None:
msg = jotools.escape_html(unicode(result[4], 'UTF-8')).strip()
msg = msg.replace(u'\n', u'<br />\n')
retstr = retstr + u'<p class="logmsg">%s</p>\n' % msg
if result[5] != None:
comment = jotools.escape_html(unicode(result[5], 'UTF-8')).strip()
comment = comment.replace(u'\n', u'<br />\n')
comment = jotools.comment_links(comment)
retstr = retstr + u'<p class="comment">%s</p>\n' % comment
retstr = retstr + u"</div>\n"
jotools.write(req, retstr)
joheaders.page_footer_plain(req)
return '\n'
def page_header_navbar_level1(req, title, uid, uname, wid = 0): title = jotools.escape_html(title) h1 = u'<a href="..">Joukahainen</a> > %s' % title _page_header_internal(req, title, h1, uid, uname, wid)
def page_header_navbar_level0(req, title, uid, uname): title = jotools.escape_html(title) h1 = title _page_header_internal(req, title, h1, uid, uname, 0)
def page_header_navbar_level2(req, title1, link1, title2, uid, uname, wid = 0): title1 = jotools.escape_html(title1) title2 = jotools.escape_html(title2) title = title1 + u' > ' + title2 h1 = u'<a href="..">Joukahainen</a> > <a href="%s">%s</a> > %s' % (link1, title1, title2) _page_header_internal(req, title, h1, uid, uname, wid)
def rwords(req, wid = None):
(uid, uname, editable) = jotools.get_login_user(req)
if not editable:
joheaders.error_page(req, _(u'You are not allowed to edit data'))
return '\n'
if wid == None:
joheaders.error_page(req, _(u'Parameter %s is required') % u'wid')
return '\n'
wid_n = jotools.toint(wid)
db = jodb.connect()
results = db.query("select word, class from word where wid = %i" % wid_n)
if results.ntuples() == 0:
joheaders.error_page(req, _(u'Word %i does not exist') % wid_n)
return '\n'
wordinfo = results.getresult()[0]
if req.method == 'GET': # show editor
word = unicode(wordinfo[0], 'UTF-8')
classid = wordinfo[1]
title1 = _(u'Word') + u': ' + word
link1 = u'edit?wid=%i' % wid_n
title2 = _(u'related words')
joheaders.page_header_navbar_level2(req, title1, link1, title2, uid, uname, wid_n)
jotools.write(req, u'<p>%s</p>\n' % joeditors.call(db, u'word_class', [classid]))
jotools.write(req, joeditors.call(db, u'rwords_edit_form', [wid_n]))
joheaders.page_footer_plain(req)
return '\n'
if req.method != 'POST':
joheaders.error_page(req, _(u'Only GET and POST requests are allowed'))
return '\n'
db.query("begin")
rword_results = db.query("SELECT rwid, related_word FROM related_word WHERE wid = %i" % wid_n)
rword_res = rword_results.getresult()
eid = db.query("select nextval('event_eid_seq')").getresult()[0][0]
event_inserted = False
messages = []
for attribute in rword_res:
html_att = 'rword%i' % attribute[0]
if jotools.get_param(req, html_att, u'') == u'on': remove = True
else: remove = False
if not remove: continue
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
db.query("delete from related_word where wid = %i and rwid = %i" \
% (wid_n, attribute[0]))
messages.append(_(u"Alternative spelling removed: '%s'") \
% jotools.escape_html(unicode(attribute[1], 'UTF-8')))
newwords = jotools.get_param(req, 'add', u'')
for word in jotools.unique(newwords.split()):
if not jotools.checkword(word): continue
already_listed = False
for attribute in rword_res:
if word == unicode(attribute[1], 'UTF-8'):
already_listed = True
break
if already_listed: continue
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
db.query("insert into related_word(wid, eevent, related_word) values(%i, %i, '%s')" \
% (wid_n, eid, jotools.escape_sql_string(word)))
messages.append(_(u"Alternative spelling added: '%s'") % jotools.escape_html(word))
comment = jotools.get_param(req, 'comment', u'')
if comment != u'':
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
db.query("update event set comment = '%s' where eid = %i" \
% (jotools.escape_sql_string(comment), eid))
if event_inserted and len(messages) > 0:
mess_str = jotools.escape_sql_string(reduce(lambda x, y: x + u"\n" + y, messages, u""))
db.query("update event set message = '%s' where eid = %i" % (mess_str, eid))
db.query("commit")
joheaders.redirect_header(req, u'edit?wid=%i' % wid_n)
return '\n'
