예제 #1
0
  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
예제 #2
0
  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 ) )
예제 #3
0
파일: webctx.py 프로젝트: wunderlins/rot
	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)
예제 #4
0
    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
예제 #5
0
  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
예제 #6
0
    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))