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])