def p_recentlyseen_term(p):
'''recentlyseen_term : OP_RECENTLYSEEN NUMBER string'''
# A gertty extension
now = datetime.datetime.utcnow()
delta = p[2]
unit = p[3]
delta = age_to_delta(delta, unit)
s = select([func.datetime(func.max(gertty.db.change_table.c.last_seen), '-%s seconds' % delta)],
correlate=False)
p[0] = gertty.db.change_table.c.last_seen >= s
def p_recentlyseen_term(p):
'''recentlyseen_term : OP_RECENTLYSEEN NUMBER string'''
# A hubtty extension
delta = p[2]
unit = p[3]
delta = age_to_delta(delta, unit)
s = select([
func.datetime(func.max(hubtty.db.pull_request_table.c.last_seen),
'-%s seconds' % delta)
],
correlate=False)
p[0] = hubtty.db.pull_request_table.c.last_seen >= s
def adv_search_custom_columns(cc, term, q):
for c in cc:
if c.datatype == "datetime":
custom_start = term.get('custom_column_' + str(c.id) + '_start')
custom_end = term.get('custom_column_' + str(c.id) + '_end')
if custom_start:
q = q.filter(
getattr(db.Books, 'custom_column_' + str(c.id)).any(
func.datetime(db.cc_classes[c.id].value) >=
func.datetime(custom_start)))
if custom_end:
q = q.filter(
getattr(db.Books, 'custom_column_' + str(c.id)).any(
func.datetime(db.cc_classes[c.id].value) <=
func.datetime(custom_end)))
else:
custom_query = term.get('custom_column_' + str(c.id))
if custom_query != '' and custom_query is not None:
if c.datatype == 'bool':
q = q.filter(
getattr(db.Books, 'custom_column_' +
str(c.id)).any(db.cc_classes[c.id].value == (
custom_query == "True")))
elif c.datatype == 'int' or c.datatype == 'float':
q = q.filter(
getattr(db.Books, 'custom_column_' + str(c.id)).any(
db.cc_classes[c.id].value == custom_query))
elif c.datatype == 'rating':
q = q.filter(
getattr(db.Books, 'custom_column_' +
str(c.id)).any(db.cc_classes[c.id].value ==
int(float(custom_query) * 2)))
else:
q = q.filter(
getattr(db.Books, 'custom_column_' + str(c.id)).any(
func.lower(
db.cc_classes[c.id].value).ilike("%" +
custom_query +
"%")))
return q
def p_recentlyseen_term(p):
'''recentlyseen_term : OP_RECENTLYSEEN NUMBER string'''
# A gertty extension
now = datetime.datetime.utcnow()
delta = p[2]
unit = p[3]
delta = age_to_delta(delta, unit)
s = select([
func.datetime(func.max(gertty.db.change_table.c.last_seen),
'-%s seconds' % delta)
],
correlate=False)
p[0] = gertty.db.change_table.c.last_seen >= s
def render_adv_search_results(term, offset=None, order=None, limit=None):
sort = order[0] if order else [db.Books.sort]
pagination = None
cc = calibre_db.get_cc_columns(config, filter_config_custom_read=True)
calibre_db.session.connection().connection.connection.create_function(
"lower", 1, db.lcase)
if not config.config_read_column:
query = (calibre_db.session.query(
db.Books, ub.ArchivedBook.is_archived,
ub.ReadBook).select_from(db.Books).outerjoin(
ub.ReadBook,
and_(db.Books.id == ub.ReadBook.book_id,
int(current_user.id) == ub.ReadBook.user_id)))
else:
try:
read_column = cc[config.config_read_column]
query = (calibre_db.session.query(
db.Books, ub.ArchivedBook.is_archived,
read_column.value).select_from(db.Books).outerjoin(
read_column, read_column.book == db.Books.id))
except (KeyError, AttributeError):
log.error(
"Custom Column No.%d is not existing in calibre database",
config.config_read_column)
# Skip linking read column
query = calibre_db.session.query(db.Books,
ub.ArchivedBook.is_archived, None)
query = query.outerjoin(
ub.ArchivedBook,
and_(db.Books.id == ub.ArchivedBook.book_id,
int(current_user.id) == ub.ArchivedBook.user_id))
q = query.outerjoin(db.books_series_link, db.Books.id == db.books_series_link.c.book)\
.outerjoin(db.Series)\
.filter(calibre_db.common_filters(True))
# parse multi selects to a complete dict
tags = dict()
elements = ['tag', 'serie', 'shelf', 'language', 'extension']
for element in elements:
tags['include_' + element] = term.get('include_' + element)
tags['exclude_' + element] = term.get('exclude_' + element)
author_name = term.get("author_name")
book_title = term.get("book_title")
publisher = term.get("publisher")
pub_start = term.get("publishstart")
pub_end = term.get("publishend")
rating_low = term.get("ratinghigh")
rating_high = term.get("ratinglow")
description = term.get("comment")
read_status = term.get("read_status")
if author_name:
author_name = author_name.strip().lower().replace(',', '|')
if book_title:
book_title = book_title.strip().lower()
if publisher:
publisher = publisher.strip().lower()
search_term = []
cc_present = False
for c in cc:
if c.datatype == "datetime":
column_start = term.get('custom_column_' + str(c.id) + '_start')
column_end = term.get('custom_column_' + str(c.id) + '_end')
if column_start:
search_term.extend([
u"{} >= {}".format(
c.name,
format_date(datetime.strptime(column_start,
"%Y-%m-%d").date(),
format='medium'))
])
cc_present = True
if column_end:
search_term.extend([
u"{} <= {}".format(
c.name,
format_date(datetime.strptime(column_end,
"%Y-%m-%d").date(),
format='medium'))
])
cc_present = True
elif term.get('custom_column_' + str(c.id)):
search_term.extend([
(u"{}: {}".format(c.name,
term.get('custom_column_' + str(c.id))))
])
cc_present = True
if any(tags.values()) or author_name or book_title or publisher or pub_start or pub_end or rating_low \
or rating_high or description or cc_present or read_status:
search_term, pub_start, pub_end = extend_search_term(
search_term, author_name, book_title, publisher, pub_start,
pub_end, tags, rating_high, rating_low, read_status)
if author_name:
q = q.filter(
db.Books.authors.any(
func.lower(db.Authors.name).ilike("%" + author_name +
"%")))
if book_title:
q = q.filter(
func.lower(db.Books.title).ilike("%" + book_title + "%"))
if pub_start:
q = q.filter(
func.datetime(db.Books.pubdate) > func.datetime(pub_start))
if pub_end:
q = q.filter(
func.datetime(db.Books.pubdate) < func.datetime(pub_end))
q = adv_search_read_status(q, read_status)
if publisher:
q = q.filter(
db.Books.publishers.any(
func.lower(db.Publishers.name).ilike("%" + publisher +
"%")))
q = adv_search_tag(q, tags['include_tag'], tags['exclude_tag'])
q = adv_search_serie(q, tags['include_serie'], tags['exclude_serie'])
q = adv_search_shelf(q, tags['include_shelf'], tags['exclude_shelf'])
q = adv_search_extension(q, tags['include_extension'],
tags['exclude_extension'])
q = adv_search_language(q, tags['include_language'],
tags['exclude_language'])
q = adv_search_ratings(q, rating_high, rating_low)
if description:
q = q.filter(
db.Books.comments.any(
func.lower(db.Comments.text).ilike("%" + description +
"%")))
# search custom columns
try:
q = adv_search_custom_columns(cc, term, q)
except AttributeError as ex:
log.debug_or_exception(ex)
flash(_(
"Error on search for custom columns, please restart Calibre-Web"
),
category="error")
q = q.order_by(*sort).all()
flask_session['query'] = json.dumps(term)
ub.store_combo_ids(q)
result_count = len(q)
if offset is not None and limit is not None:
offset = int(offset)
limit_all = offset + int(limit)
pagination = Pagination((offset / (int(limit)) + 1), limit,
result_count)
else:
offset = 0
limit_all = result_count
entries = calibre_db.order_authors(q[offset:limit_all],
list_return=True,
combined=True)
return render_title_template('search.html',
adv_searchterm=search_term,
pagination=pagination,
entries=entries,
result_count=result_count,
title=_(u"Advanced Search"),
page="advsearch",
order=order[1])