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'