def pager( self, table, filter_field, filter, sort_by, descending, offset, limit, prefilter=[] ):
table = db.__dict__[table]
def build_query( query ):
for field, value in prefilter:
query = query.filter( getattr( table, field ) == value )
if filter != "":
if type( filter_field ) != list:
for f in filter.split():
query = query.filter( getattr( table, filter_field ).like( "%%%s%%" % filter ) )
else:
t = db.__dict__[filter_field[0]]
for f in filter.split():
query = query.filter( getattr( t, filter_field[1] ).like( "%%%s%%" % filter ) )
if not descending:
query = query.order_by( db.func.lower( getattr( table, sort_by ) ).asc() )
else:
query = query.order_by( db.func.lower( getattr( table, sort_by ) ).desc() )
query = query.distinct()
return query
result = build_query( db.session().query( table ) )
count = build_query( db.session().query( db.func.count( db.distinct( table.id ) ) ) ).one()[0]
result = result.limit( limit ).offset( offset ).all()
result = [
helpers.get( table ).to_dictionary( p )
for p in result
]
return result, count
def product_pager( self, filter_field, filter, sort_by, descending, page, limit, genre="", prefilter=[] ):
def build_query( query ):
if genre != "":
query = query.join( db.Product.genres )
query = query.filter( "product_genre_1.genre_id = %s" % genre )
for field, value in prefilter:
query = query.filter( getattr( db.Product, field ) == value )
if filter != "":
for f in filter.split():
query = query.filter( getattr( db.Product, filter_field ).like( "%%%s%%" % filter ) )
if not descending:
query = query.order_by( db.func.lower( getattr( db.Product, sort_by ) ).asc() )
else:
query = query.order_by( db.func.lower( getattr( db.Product, sort_by ) ).desc() )
query = query.distinct()
return query
result = build_query( db.session().query( db.Product ) )
count = build_query( db.session().query( db.func.count( db.distinct( db.Product.id ) ) ) ).one()[0]
result = result.limit( limit ).offset( page * limit ).all()
result = [
helpers.get( db.Product ).to_dictionary( p )
for p in result
]
return result, int( math.ceil( 1.0 * count / limit ) )
def person(self, pid, history):
# get latest wishes
if history == None:
try:
w = db.session.query(db.Wunsch).filter_by(pid=pid, latest=1)
except:
db.session.rollback()
else:
endd = history + datetime.timedelta(days=1)
try:
w = db.session.query(db.Wunsch).filter_by(pid=pid).filter(
and_(db.Wunsch.created >= datetime.date(history.year, history.month, history.day),\
db.Wunsch.created < datetime.date(endd.year, endd.month, endd.day))
)
except:
db.session.rollback()
#print u
try:
g = db.session.query(db.Group).order_by(db.Group.sort)
except:
db.session.rollback()
# modified dates
try:
d = db.session.query(db.distinct(db.Wunsch.created)).filter_by(pid=pid)
except:
db.session.rollback()
dates = []
for dt in d:
dates.insert(0, dt[0])
wunsch = {}
for gr in g:
for r in gr.rot:
wunsch[r.id] = {"prio": 0, "wunsch": None}
for e in w:
if e.rot_id == r.id:
#print e
wunsch[r.id] = {"prio": e.prio, "wunsch": e.janein}
break
return (g, wunsch, dates)
def pager(self,
table,
filter_field,
filter,
sort_by,
descending,
offset,
limit,
prefilter=[]):
table = db.__dict__[table]
def build_query(query):
for field, value in prefilter:
query = query.filter(getattr(table, field) == value)
if filter != "":
if type(filter_field) != list:
for f in filter.split():
query = query.filter(
getattr(table,
filter_field).like("%%%s%%" % filter))
else:
t = db.__dict__[filter_field[0]]
for f in filter.split():
query = query.filter(
getattr(t,
filter_field[1]).like("%%%s%%" % filter))
if not descending:
query = query.order_by(
db.func.lower(getattr(table, sort_by)).asc())
else:
query = query.order_by(
db.func.lower(getattr(table, sort_by)).desc())
query = query.distinct()
return query
result = build_query(db.session().query(table))
count = build_query(db.session().query(
db.func.count(db.distinct(table.id)))).one()[0]
result = result.limit(limit).offset(offset).all()
result = [helpers.get(table).to_dictionary(p) for p in result]
return result, count
def article_pager( self,
symbol = None,
filter = "",
sort_by = "date",
descending = False,
page = 0, limit = 10, date=None
):
def build_query( query, date ):
# TODO: probably extend to multiple symbols
if symbol != None:
concept = db.Concept.by_symbol( symbol )
query = query.join( db.Article.concept_info )
query = query.filter( db.ArticleConcept.concept == concept )
if filter != "":
for f in filter.split():
ac_alias = db.aliased( db.ArticleConcept )
c_alias = db.aliased( db.Concept )
query = query.join( ac_alias, db.Article.concept_info )\
.join( c_alias, ac_alias.concept )\
.filter( c_alias.text.like( "%%%s%%" % f ) )
if date != None:
if type( date ) == str:
date = datetime.datetime.strptime( date, "%d/%m/%Y" )
query = query.filter( db.Article.date == date.date() )
if not descending:
query = query.order_by( getattr( db.Article, sort_by ).asc() )
else:
query = query.order_by( getattr( db.Article, sort_by ).desc() )
query = query.distinct()
return query
result = build_query( db.session().query( db.Article ), date )
count = build_query( db.session().query( db.func.count( db.distinct( db.Article.id ) ) ), date ).one()[0]
result = result.limit( limit ).offset( page * limit ).all()
result = [
helpers.article.to_dictionary( p )
for p in result
]
return result, int( math.floor( 1.0 * count / limit ) ) + 1
def product_pager(self,
filter_field,
filter,
sort_by,
descending,
page,
limit,
genre="",
prefilter=[]):
def build_query(query):
if genre != "":
query = query.join(db.Product.genres)
query = query.filter("product_genre_1.genre_id = %s" % genre)
for field, value in prefilter:
query = query.filter(getattr(db.Product, field) == value)
if filter != "":
for f in filter.split():
query = query.filter(
getattr(db.Product,
filter_field).like("%%%s%%" % filter))
if not descending:
query = query.order_by(
db.func.lower(getattr(db.Product, sort_by)).asc())
else:
query = query.order_by(
db.func.lower(getattr(db.Product, sort_by)).desc())
query = query.distinct()
return query
result = build_query(db.session().query(db.Product))
count = build_query(db.session().query(
db.func.count(db.distinct(db.Product.id)))).one()[0]
result = result.limit(limit).offset(page * limit).all()
result = [helpers.get(db.Product).to_dictionary(p) for p in result]
return result, int(math.ceil(1.0 * count / limit))
